[Home] [Help]
PACKAGE BODY: APPS.PAY_US_SQWL_ERROR
Source
1 package body pay_us_sqwl_error as
2 /* $Header: pyusngpk.pkb 120.5.12020000.2 2013/01/25 10:56:40 schowl ship $*/
3
4 /* Rmonge 30-NOV-2001 */
5
6
7 /************************************************************
8 ** Local Package Variables
9 ************************************************************/
10 gv_title VARCHAR2(100) := ' State Quaterly Error Report ';
11 gc_csv_delimiter VARCHAR2(1) := ',';
12 gc_csv_data_delimiter VARCHAR2(1) := '"';
13
14 gv_package_name VARCHAR2(50) := 'pay_us_sqwl_error';
15
16 gv_report_type pay_payroll_actions.report_type%TYPE := 'SQWL'; /* Added for Bug 14456648 */
17
18
19 /******************************************************************
20 ** Function Returns the formated input string based on the
21 ** Output format. If the format is CSV then the values are returned
22 ** seperated by comma (,).
23 ******************************************************************/
24
25 FUNCTION formated_data_string
26 (p_input_string in varchar2
27 ,p_output_file_type in varchar2
28 ,p_bold in varchar2 default 'N'
29 )
30 RETURN VARCHAR2
31 IS
32
33 lv_format varchar2(32000);
34
35 BEGIN
36 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
37 if p_output_file_type = 'CSV' then
38 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
39
40 lv_format := gc_csv_data_delimiter || p_input_string ||
41 gc_csv_data_delimiter || gc_csv_delimiter;
42
43 end if;
44
45 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
46 return lv_format;
47
48 END formated_data_string;
49
50 /*****************************************************************
51 ** This procudure returns the Mandatory Static Labels and the
52 ** Other Additional Static columns.
53 *****************************************************************/
54
55 FUNCTION formated_static_header(
56 p_output_file_type in varchar2
57 )
58 RETURN VARCHAR2
59 IS
60
61 lv_format1 varchar2(32000);
62 lv_format2 varchar2(32000);
63
64 BEGIN
65
66 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
67 IF gv_report_type = 'SMWL' THEN /* Condition added for Bug 14456648 */
68
69 lv_format1 :=
70 formated_data_string (p_input_string => 'GRE'
71 ,p_bold => 'Y'
72 ,p_output_file_type => p_output_file_type) ||
73 formated_data_string (p_input_string => 'Organization'
74 ,p_bold => 'Y'
75 ,p_output_file_type => p_output_file_type) ||
76 formated_data_string (p_input_string => 'Month Name'
77 ,p_bold => 'Y'
78 ,p_output_file_type => p_output_file_type) ||
79 formated_data_string (p_input_string => 'State Code'
80 ,p_bold => 'Y'
81 ,p_output_file_type => p_output_file_type) ||
82 formated_data_string (p_input_string => 'Assignment Number'
83 ,p_bold => 'Y'
84 ,p_output_file_type => p_output_file_type) ||
85 formated_data_string (p_input_string => 'Full Name'
86 ,p_bold => 'Y'
87 ,p_output_file_type => p_output_file_type) ||
88 formated_data_string (p_input_string => 'SUI Month Wages'
89 ,p_bold => 'Y'
90 ,p_output_file_type => p_output_file_type) ||
91 formated_data_string (p_input_string => 'SUI ER Taxable'
92 ,p_bold => 'Y'
93 ,p_output_file_type => p_output_file_type) ||
94 formated_data_string (p_input_string => 'SUI Excess '
95 ,p_bold => 'Y'
96 ,p_output_file_type => p_output_file_type) ||
97 formated_data_string (p_input_string => 'SUI ER Subj Whable'
98 ,p_bold => 'Y'
99 ,p_output_file_type => p_output_file_type) ||
100 formated_data_string (p_input_string => 'SUI ER Pre Tax Redns'
101 ,p_bold => 'Y'
102 ,p_output_file_type => p_output_file_type) ||
103 formated_data_string (p_input_string => 'SIT Month Wages'
104 ,p_bold => 'Y'
105 ,p_output_file_type => p_output_file_type) ||
106 formated_data_string (p_input_string => 'SIT Withheld'
107 ,p_bold => 'Y'
108 ,p_output_file_type => p_output_file_type) ||
109 formated_data_string (p_input_string => 'SIT Gross'
110 ,p_bold => 'Y'
111 ,p_output_file_type => p_output_file_type) ||
112 formated_data_string (p_input_string => 'SIT Subj Whable'
113 ,p_bold => 'Y'
114 ,p_output_file_type => p_output_file_type) ||
115 formated_data_string (p_input_string => 'SIT Subj Nwhable'
116 ,p_bold => 'Y'
117 ,p_output_file_type => p_output_file_type) ||
118 formated_data_string (p_input_string => 'SIT Pre Tax Redns'
119 ,p_bold => 'Y'
120 ,p_output_file_type => p_output_file_type) ||
121 formated_data_string (p_input_string => 'Hours Worked'
122 ,p_bold => 'Y'
123 ,p_output_file_type => p_output_file_type) ||
124 formated_data_string (p_input_string => 'Weeks Worked'
125 ,p_bold => 'Y'
126 ,p_output_file_type => p_output_file_type) ;
127
128 ELSE
129
130 lv_format1 :=
131 formated_data_string (p_input_string => 'GRE'
132 ,p_bold => 'Y'
133 ,p_output_file_type => p_output_file_type) ||
134 formated_data_string (p_input_string => 'Organization'
135 ,p_bold => 'Y'
136 ,p_output_file_type => p_output_file_type) ||
137 formated_data_string (p_input_string => 'QTR Name'
138 ,p_bold => 'Y'
139 ,p_output_file_type => p_output_file_type) ||
140 formated_data_string (p_input_string => 'State Code'
141 ,p_bold => 'Y'
142 ,p_output_file_type => p_output_file_type) ||
143 formated_data_string (p_input_string => 'Assignment Number'
144 ,p_bold => 'Y'
145 ,p_output_file_type => p_output_file_type) ||
146 formated_data_string (p_input_string => 'Full Name'
147 ,p_bold => 'Y'
148 ,p_output_file_type => p_output_file_type) ||
149 formated_data_string (p_input_string => 'SUI Qtr Wages'
150 ,p_bold => 'Y'
151 ,p_output_file_type => p_output_file_type) ||
152 formated_data_string (p_input_string => 'SUI ER Taxable'
153 ,p_bold => 'Y'
154 ,p_output_file_type => p_output_file_type) ||
155 formated_data_string (p_input_string => 'SUI Excess '
156 ,p_bold => 'Y'
157 ,p_output_file_type => p_output_file_type) ||
158 formated_data_string (p_input_string => 'SUI ER Subj Whable'
159 ,p_bold => 'Y'
160 ,p_output_file_type => p_output_file_type) ||
161 formated_data_string (p_input_string => 'SUI ER Pre Tax Redns'
162 ,p_bold => 'Y'
163 ,p_output_file_type => p_output_file_type) ||
164 formated_data_string (p_input_string => 'SIT Qtr Wages'
165 ,p_bold => 'Y'
166 ,p_output_file_type => p_output_file_type) ||
167 formated_data_string (p_input_string => 'SIT Withheld'
168 ,p_bold => 'Y'
169 ,p_output_file_type => p_output_file_type) ||
170 formated_data_string (p_input_string => 'SIT Gross'
171 ,p_bold => 'Y'
172 ,p_output_file_type => p_output_file_type) ||
173 formated_data_string (p_input_string => 'SIT Subj Whable'
174 ,p_bold => 'Y'
175 ,p_output_file_type => p_output_file_type) ||
176 formated_data_string (p_input_string => 'SIT Subj Nwhable'
177 ,p_bold => 'Y'
178 ,p_output_file_type => p_output_file_type) ||
179 formated_data_string (p_input_string => 'SIT Pre Tax Redns'
180 ,p_bold => 'Y'
181 ,p_output_file_type => p_output_file_type) ||
182 formated_data_string (p_input_string => 'Hours Worked'
183 ,p_bold => 'Y'
184 ,p_output_file_type => p_output_file_type) ||
185 formated_data_string (p_input_string => 'Weeks Worked'
186 ,p_bold => 'Y'
187 ,p_output_file_type => p_output_file_type) ;
188
189 END IF;
190
191 RETURN (lv_format1);
192 END;
193
194 /* Bug # 5333916 */
195 FUNCTION formated_static_header_NM(
196 p_output_file_type in varchar2
197 )
198 RETURN VARCHAR2
199 IS
200
201 lv_format1 varchar2(32000);
202 lv_format2 varchar2(32000);
203
204 BEGIN
205
206 hr_utility.set_location(gv_package_name || '.formated_static_header_NM', 10);
207 lv_format1 :=
208 formated_data_string (p_input_string => 'GRE'
209 ,p_bold => 'Y'
210 ,p_output_file_type => p_output_file_type) ||
211 formated_data_string (p_input_string => 'Organization'
212 ,p_bold => 'Y'
213 ,p_output_file_type => p_output_file_type) ||
214 formated_data_string (p_input_string => 'QTR Name'
215 ,p_bold => 'Y'
216 ,p_output_file_type => p_output_file_type) ||
217 formated_data_string (p_input_string => 'State Code'
218 ,p_bold => 'Y'
219 ,p_output_file_type => p_output_file_type) ||
220 formated_data_string (p_input_string => 'Assignment Number'
221 ,p_bold => 'Y'
222 ,p_output_file_type => p_output_file_type) ||
223 formated_data_string (p_input_string => 'Full Name'
224 ,p_bold => 'Y'
225 ,p_output_file_type => p_output_file_type) ||
226 formated_data_string (p_input_string => 'SUI Qtr Wages'
227 ,p_bold => 'Y'
228 ,p_output_file_type => p_output_file_type) ||
229 formated_data_string (p_input_string => 'SUI ER Taxable'
230 ,p_bold => 'Y'
231 ,p_output_file_type => p_output_file_type) ||
232 formated_data_string (p_input_string => 'SUI Excess '
233 ,p_bold => 'Y'
234 ,p_output_file_type => p_output_file_type) ||
235 formated_data_string (p_input_string => 'SUI ER Subj Whable'
236 ,p_bold => 'Y'
237 ,p_output_file_type => p_output_file_type) ||
238 formated_data_string (p_input_string => 'SUI ER Pre Tax Redns'
239 ,p_bold => 'Y'
240 ,p_output_file_type => p_output_file_type) ||
241 formated_data_string (p_input_string => 'SIT Qtr Wages'
242 ,p_bold => 'Y'
243 ,p_output_file_type => p_output_file_type) ||
244 formated_data_string (p_input_string => 'SIT Withheld'
245 ,p_bold => 'Y'
246 ,p_output_file_type => p_output_file_type) ||
247 formated_data_string (p_input_string => 'SIT Gross'
248 ,p_bold => 'Y'
249 ,p_output_file_type => p_output_file_type) ||
250 formated_data_string (p_input_string => 'SIT Subj Whable'
251 ,p_bold => 'Y'
252 ,p_output_file_type => p_output_file_type) ||
253 formated_data_string (p_input_string => 'SIT Subj Nwhable'
254 ,p_bold => 'Y'
255 ,p_output_file_type => p_output_file_type) ||
256 formated_data_string (p_input_string => 'SIT Pre Tax Redns'
257 ,p_bold => 'Y'
258 ,p_output_file_type => p_output_file_type) ||
259 formated_data_string (p_input_string => 'Hours Worked'
260 ,p_bold => 'Y'
261 ,p_output_file_type => p_output_file_type) ||
262 formated_data_string (p_input_string => 'Weeks Worked'
263 ,p_bold => 'Y'
264 ,p_output_file_type => p_output_file_type) ||
265 formated_data_string (p_input_string => 'Workers Compensation 2 ER'
266 ,p_bold => 'Y'
267 ,p_output_file_type => p_output_file_type) ||
268 formated_data_string (p_input_string => 'Workers Compensation 2 EE'
269 ,p_bold => 'Y'
270 ,p_output_file_type => p_output_file_type) ||
271 formated_data_string (p_input_string => 'Workers Compensation'
272 ,p_bold => 'Y'
273 ,p_output_file_type => p_output_file_type) ;
274
275 RETURN (lv_format1);
276 END;
277
278 /* This function is used to write the headers for the State of California */
279
280 FUNCTION formated_static_header_CA(
281 p_output_file_type in varchar2
282 )
283 RETURN VARCHAR2
284 IS
285
286 lv_format1 varchar2(32000);
287 lv_format2 varchar2(32000);
288
289 BEGIN
290
291 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
292 lv_format1 :=
293 formated_data_string (p_input_string => 'GRE'
294 ,p_bold => 'Y'
295 ,p_output_file_type => p_output_file_type) ||
296 formated_data_string (p_input_string => 'Organization'
297 ,p_bold => 'Y'
298 ,p_output_file_type => p_output_file_type) ||
299 formated_data_string (p_input_string => 'QTR Name'
300 ,p_bold => 'Y'
301 ,p_output_file_type => p_output_file_type) ||
302 formated_data_string (p_input_string => 'State Code'
303 ,p_bold => 'Y'
304 ,p_output_file_type => p_output_file_type) ||
305 formated_data_string (p_input_string => 'Assignment Number'
306 ,p_bold => 'Y'
307 ,p_output_file_type => p_output_file_type) ||
308 formated_data_string (p_input_string => 'Full Name'
309 ,p_bold => 'Y'
310 ,p_output_file_type => p_output_file_type) ||
311 formated_data_string (p_input_string => 'SUI Qtr Wages'
312 ,p_bold => 'Y'
313 ,p_output_file_type => p_output_file_type) ||
314 formated_data_string (p_input_string => 'SUI ER Taxable'
315 ,p_bold => 'Y'
316 ,p_output_file_type => p_output_file_type) ||
317 formated_data_string (p_input_string => 'SUI Excess '
318 ,p_bold => 'Y'
319 ,p_output_file_type => p_output_file_type) ||
320 formated_data_string (p_input_string => 'SUI ER Subj Whable'
321 ,p_bold => 'Y'
322 ,p_output_file_type => p_output_file_type) ||
323 formated_data_string (p_input_string => 'SUI ER Pre Tax Redns'
324 ,p_bold => 'Y'
325 ,p_output_file_type => p_output_file_type) ||
326 formated_data_string (p_input_string => 'SIT Qtr Wages'
327 ,p_bold => 'Y'
328 ,p_output_file_type => p_output_file_type) ||
329 formated_data_string (p_input_string => 'SIT Withheld'
330 ,p_bold => 'Y'
331 ,p_output_file_type => p_output_file_type) ||
332 formated_data_string (p_input_string => 'SIT Gross'
333 ,p_bold => 'Y'
334 ,p_output_file_type => p_output_file_type) ||
335 formated_data_string (p_input_string => 'SIT Subj Whable'
336 ,p_bold => 'Y'
337 ,p_output_file_type => p_output_file_type) ||
338 formated_data_string (p_input_string => 'SIT Subj Nwhable'
339 ,p_bold => 'Y'
340 ,p_output_file_type => p_output_file_type) ||
341 formated_data_string (p_input_string => 'SIT Pre Tax Redns'
342 ,p_bold => 'Y'
343 ,p_output_file_type => p_output_file_type) ||
344 formated_data_string (p_input_string => 'SDI EE QTR Wages'
345 ,p_bold => 'Y'
346 ,p_output_file_type => p_output_file_type) ||
347 formated_data_string (p_input_string => 'SDI EE Taxable'
348 ,p_bold => 'Y'
349 ,p_output_file_type => p_output_file_type) ||
350 formated_data_string (p_input_string => 'SDI EE Excess'
351 ,p_bold => 'Y'
352 ,p_output_file_type => p_output_file_type) ||
353 formated_data_string (p_input_string => 'SDI EE Subj Whable'
354 ,p_bold => 'Y'
355 ,p_output_file_type => p_output_file_type) ||
356 formated_data_string (p_input_string => 'SDI Pre Tax Redns'
357 ,p_bold => 'Y'
358 ,p_output_file_type => p_output_file_type) ||
359 formated_data_string (p_input_string => 'Hours Worked'
360 ,p_bold => 'Y'
361 ,p_output_file_type => p_output_file_type) ;
362
363 RETURN (lv_format1);
364 END;
365
366
367
368 /* This function is used to write the headers for the State of New York */
369
370 FUNCTION formated_static_header_NY(
371 p_output_file_type in varchar2
372 )
373 RETURN VARCHAR2
374 IS
375
376 lv_format1 varchar2(32000);
377 lv_format2 varchar2(32000);
378
379 BEGIN
380
381 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
382 lv_format1 :=
383 formated_data_string (p_input_string => 'GRE'
384 ,p_bold => 'Y'
385 ,p_output_file_type => p_output_file_type) ||
386 formated_data_string (p_input_string => 'Organization'
387 ,p_bold => 'Y'
388 ,p_output_file_type => p_output_file_type) ||
389 formated_data_string (p_input_string => 'QTR Name'
390 ,p_bold => 'Y'
391 ,p_output_file_type => p_output_file_type) ||
392 formated_data_string (p_input_string => 'State Code'
393 ,p_bold => 'Y'
394 ,p_output_file_type => p_output_file_type) ||
395 formated_data_string (p_input_string => 'Assignment Number'
396 ,p_bold => 'Y'
397 ,p_output_file_type => p_output_file_type) ||
398 formated_data_string (p_input_string => 'Full Name'
399 ,p_bold => 'Y'
400 ,p_output_file_type => p_output_file_type) ||
401 formated_data_string (p_input_string => 'SUI Qtr Wages'
402 ,p_bold => 'Y'
403 ,p_output_file_type => p_output_file_type) ||
404 formated_data_string (p_input_string => 'SUI ER Taxable'
405 ,p_bold => 'Y'
406 ,p_output_file_type => p_output_file_type) ||
407 formated_data_string (p_input_string => 'SUI Excess '
408 ,p_bold => 'Y'
409 ,p_output_file_type => p_output_file_type) ||
410 formated_data_string (p_input_string => 'SUI ER Subj Whable'
411 ,p_bold => 'Y'
412 ,p_output_file_type => p_output_file_type) ||
413 formated_data_string (p_input_string => 'SUI ER Pre Tax Redns'
414 ,p_bold => 'Y'
415 ,p_output_file_type => p_output_file_type) ||
416 formated_data_string (p_input_string => 'SIT Qtr Wages'
417 ,p_bold => 'Y'
418 ,p_output_file_type => p_output_file_type) ||
419 formated_data_string (p_input_string => 'SIT Withheld'
420 ,p_bold => 'Y'
421 ,p_output_file_type => p_output_file_type) ||
422 formated_data_string (p_input_string => 'SIT Gross'
423 ,p_bold => 'Y'
424 ,p_output_file_type => p_output_file_type) ||
425 formated_data_string (p_input_string => 'SIT Subj Whable'
426 ,p_bold => 'Y'
427 ,p_output_file_type => p_output_file_type) ||
428 formated_data_string (p_input_string => 'SIT Subj Nwhable'
429 ,p_bold => 'Y'
430 ,p_output_file_type => p_output_file_type) ||
431 formated_data_string (p_input_string => 'SIT Pre Tax Redns'
432 ,p_bold => 'Y'
433 ,p_output_file_type => p_output_file_type) ||
434 formated_data_string (p_input_string => 'Annual Gross Wages'
435 ,p_bold => 'Y'
436 ,p_output_file_type => p_output_file_type) ||
437 formated_data_string (p_input_string => 'Regular Earnings'
438 ,p_bold => 'Y'
439 ,p_output_file_type => p_output_file_type) ||
440 formated_data_string (p_input_string => 'Supp Earnings(FIT)'
441 ,p_bold => 'Y'
442 ,p_output_file_type => p_output_file_type) ||
443 formated_data_string (p_input_string => 'Supp Earnings(NWFIT)'
444 ,p_bold => 'Y'
445 ,p_output_file_type => p_output_file_type) ||
446 formated_data_string (p_input_string => 'Fit NW2 Pre Tax Redns'
447 ,p_bold => 'Y'
448 ,p_output_file_type => p_output_file_type) ||
449 formated_data_string (p_input_string => 'Fit Pre Tax Ded Sbj Tax'
450 ,p_bold => 'Y'
451 ,p_output_file_type => p_output_file_type) ||
452 formated_data_string (p_input_string => 'Pre Tax Deds GRE'
453 ,p_bold => 'Y'
454 ,p_output_file_type => p_output_file_type) ;
455
456 RETURN (lv_format1);
457 END;
458
459
460
461 FUNCTION formated_static_data (
462 p_tax_unit_name in varchar2
463 ,p_organization_name in varchar2
464 ,p_qtrname in varchar2
465 ,p_state_abbrev in varchar2
466 ,p_assignment_number in varchar2
467 ,p_full_name in varchar2
468 ,p_sui_qtr_wages in varchar2
469 ,p_sui_er_taxable in varchar2
470 ,p_sui_excess in varchar2
471 ,p_sui_er_subjwh in varchar2
472 ,p_sui_er_pre_tax_redns in varchar2
473 ,p_sit_qtr_wages in varchar2
474 ,p_sit_withheld in varchar2
475 ,p_sit_gross in varchar2
476 ,p_sit_subjwh in varchar2
477 ,p_sit_subjnwh in varchar2
478 ,p_sit_pre_tax_redns in varchar2
479 ,p_hours_worked in varchar2
480 ,p_weeks_worked in varchar2
481 ,p_output_file_type in varchar2
482 )
483 RETURN VARCHAR2
484 IS
485
486 lv_format1 VARCHAR2(32000);
487 lv_format2 VARCHAR2(32000);
488
489
490 BEGIN
491
492 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
493 lv_format1 :=
494 formated_data_string (p_input_string => p_tax_unit_name
495 ,p_output_file_type => p_output_file_type)||
496 formated_data_string (p_input_string => p_organization_name
497 ,p_output_file_type => p_output_file_type)||
498 formated_data_string (p_input_string => p_qtrname
499 ,p_output_file_type => p_output_file_type) ||
500 formated_data_string (p_input_string => p_state_abbrev
501 ,p_output_file_type => p_output_file_type)||
502 formated_data_string (p_input_string => p_assignment_number
503 ,p_output_file_type => p_output_file_type) ||
504 formated_data_string (p_input_string => p_full_name
505 ,p_output_file_type => p_output_file_type) ||
506 formated_data_string (p_input_string => p_sui_qtr_wages
507 ,p_output_file_type => p_output_file_type) ||
508 formated_data_string (p_input_string => p_sui_er_taxable
509 ,p_output_file_type => p_output_file_type) ||
510 formated_data_string (p_input_string => p_sui_excess
511 ,p_output_file_type => p_output_file_type) ||
512 formated_data_string (p_input_string => p_sui_er_subjwh
513 ,p_output_file_type => p_output_file_type) ||
514 formated_data_string (p_input_string => p_sui_er_pre_tax_redns
515 ,p_output_file_type => p_output_file_type) ||
516 formated_data_string (p_input_string => p_sit_qtr_wages
517 ,p_output_file_type => p_output_file_type) ||
518 formated_data_string (p_input_string => p_sit_withheld
519 ,p_output_file_type => p_output_file_type) ||
520 formated_data_string (p_input_string => p_sit_gross
521 ,p_output_file_type => p_output_file_type) ||
522 formated_data_string (p_input_string => p_sit_subjwh
523 ,p_output_file_type => p_output_file_type)||
524 formated_data_string (p_input_string => p_sit_subjnwh
525 ,p_output_file_type => p_output_file_type) ||
526 formated_data_string (p_input_string => p_sit_pre_tax_redns
527 ,p_output_file_type => p_output_file_type) ||
528 formated_data_string (p_input_string => p_hours_worked
529 ,p_output_file_type => p_output_file_type) ||
530 formated_data_string (p_input_string => p_weeks_worked
531 ,p_output_file_type => p_output_file_type) ;
532
533 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
534
535
536 -- p_static_data1 := lv_format1;
537 -- p_static_data2 := lv_format2;
538 hr_utility.trace('Static Data1 = ' || lv_format1);
539 hr_utility.trace('Static Data2 = ' || lv_format2);
540 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
541
542 return (lv_format1);
543 END;
544
545
546
547 FUNCTION formated_static_data_CA (
548 p_tax_unit_name in varchar2
549 ,p_organization_name in varchar2
550 ,p_qtrname in varchar2
551 ,p_state_abbrev in varchar2
552 ,p_assignment_number in varchar2
553 ,p_full_name in varchar2
554 ,p_sui_qtr_wages in varchar2
555 ,p_sui_er_taxable in varchar2
556 ,p_sui_excess in varchar2
557 ,p_sui_er_subjwh in varchar2
558 ,p_sui_er_pre_tax_redns in varchar2
559 ,p_sit_qtr_wages in varchar2
560 ,p_sit_withheld in varchar2
561 ,p_sit_gross in varchar2
562 ,p_sit_subjwh in varchar2
563 ,p_sit_subjnwh in varchar2
564 ,p_sit_pre_tax_redns in varchar2
565 ,p_sdi_qtr_wages in varchar2
566 ,p_sdi_taxable in varchar2
567 ,p_sdi_excess in varchar2
568 ,p_sdi_subjwh in varchar2
569 ,p_sdi_pre_tax_redns in varchar2
570 ,p_hours_worked in varchar2
571 ,p_output_file_type in varchar2
572 )
573 RETURN VARCHAR2
574 IS
575
576 lv_format1 VARCHAR2(32000);
577 lv_format2 VARCHAR2(32000);
578
579
580 BEGIN
581
582 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
583 lv_format1 :=
584 formated_data_string (p_input_string => p_tax_unit_name
585 ,p_output_file_type => p_output_file_type)||
586 formated_data_string (p_input_string => p_organization_name
587 ,p_output_file_type => p_output_file_type)||
588 formated_data_string (p_input_string => p_qtrname
589 ,p_output_file_type => p_output_file_type) ||
590 formated_data_string (p_input_string => p_state_abbrev
591 ,p_output_file_type => p_output_file_type)||
592 formated_data_string (p_input_string => p_assignment_number
593 ,p_output_file_type => p_output_file_type) ||
594 formated_data_string (p_input_string => p_full_name
595 ,p_output_file_type => p_output_file_type) ||
596 formated_data_string (p_input_string => p_sui_qtr_wages
597 ,p_output_file_type => p_output_file_type) ||
598 formated_data_string (p_input_string => p_sui_er_taxable
599 ,p_output_file_type => p_output_file_type) ||
600 formated_data_string (p_input_string => p_sui_excess
601 ,p_output_file_type => p_output_file_type) ||
602 formated_data_string (p_input_string => p_sui_er_subjwh
603 ,p_output_file_type => p_output_file_type) ||
604 formated_data_string (p_input_string => p_sui_er_pre_tax_redns
605 ,p_output_file_type => p_output_file_type) ||
606 formated_data_string (p_input_string => p_sit_qtr_wages
607 ,p_output_file_type => p_output_file_type) ||
608 formated_data_string (p_input_string => p_sit_withheld
609 ,p_output_file_type => p_output_file_type) ||
610 formated_data_string (p_input_string => p_sit_gross
611 ,p_output_file_type => p_output_file_type) ||
612 formated_data_string (p_input_string => p_sit_subjwh
613 ,p_output_file_type => p_output_file_type)||
614 formated_data_string (p_input_string => p_sit_subjnwh
615 ,p_output_file_type => p_output_file_type) ||
616 formated_data_string (p_input_string => p_sit_pre_tax_redns
617 ,p_output_file_type => p_output_file_type) ||
618 formated_data_string (p_input_string => p_sdi_qtr_wages
619 ,p_output_file_type => p_output_file_type) ||
620 formated_data_string (p_input_string => p_sdi_taxable
621 ,p_output_file_type => p_output_file_type) ||
622 formated_data_string (p_input_string => p_sdi_excess
623 ,p_output_file_type => p_output_file_type) ||
624 formated_data_string (p_input_string => p_sdi_subjwh
625 ,p_output_file_type => p_output_file_type) ||
626 formated_data_string (p_input_string => p_sdi_pre_tax_redns
627 ,p_output_file_type => p_output_file_type) ||
628 formated_data_string (p_input_string => p_hours_worked
629 ,p_output_file_type => p_output_file_type) ;
630
631 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
632
633
634 hr_utility.trace('Static Data1 = ' || lv_format1);
635 hr_utility.trace('Static Data2 = ' || lv_format2);
636 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
637
638 return (lv_format1);
639 END;
640
641 FUNCTION formated_static_data_NM (
642 p_tax_unit_name in varchar2
643 ,p_organization_name in varchar2
644 ,p_qtrname in varchar2
645 ,p_state_abbrev in varchar2
646 ,p_assignment_number in varchar2
647 ,p_full_name in varchar2
648 ,p_sui_qtr_wages in varchar2
649 ,p_sui_er_taxable in varchar2
650 ,p_sui_excess in varchar2
651 ,p_sui_er_subjwh in varchar2
652 ,p_sui_er_pre_tax_redns in varchar2
653 ,p_sit_qtr_wages in varchar2
654 ,p_sit_withheld in varchar2
655 ,p_sit_gross in varchar2
656 ,p_sit_subjwh in varchar2
657 ,p_sit_subjnwh in varchar2
658 ,p_sit_pre_tax_redns in varchar2
659 ,p_sdi_qtr_wages in varchar2
660 ,p_sdi_taxable in varchar2
661 ,p_sdi_excess in varchar2
662 ,p_sdi_subjwh in varchar2
663 ,p_sdi_pre_tax_redns in varchar2
664 ,p_hours_worked in varchar2
665 ,p_weeks_worked in varchar2
666 ,p_workers_compensation2_er in varchar2
667 ,p_workers_comp2_withheld in varchar2
668 ,p_workers_comp_withheld in varchar2
669 ,p_output_file_type in varchar2
670 )
671 RETURN VARCHAR2
672 IS
673
674 lv_format1 VARCHAR2(32000);
675 lv_format2 VARCHAR2(32000);
676
677
678 BEGIN
679
680 hr_utility.set_location(gv_package_name || '.formated_static_data_NM', 10);
681 lv_format1 :=
682 formated_data_string (p_input_string => p_tax_unit_name
683 ,p_output_file_type => p_output_file_type)||
684 formated_data_string (p_input_string => p_organization_name
685 ,p_output_file_type => p_output_file_type)||
686 formated_data_string (p_input_string => p_qtrname
687 ,p_output_file_type => p_output_file_type) ||
688 formated_data_string (p_input_string => p_state_abbrev
689 ,p_output_file_type => p_output_file_type)||
690 formated_data_string (p_input_string => p_assignment_number
691 ,p_output_file_type => p_output_file_type) ||
692 formated_data_string (p_input_string => p_full_name
693 ,p_output_file_type => p_output_file_type) ||
694 formated_data_string (p_input_string => p_sui_qtr_wages
695 ,p_output_file_type => p_output_file_type) ||
696 formated_data_string (p_input_string => p_sui_er_taxable
697 ,p_output_file_type => p_output_file_type) ||
698 formated_data_string (p_input_string => p_sui_excess
699 ,p_output_file_type => p_output_file_type) ||
700 formated_data_string (p_input_string => p_sui_er_subjwh
701 ,p_output_file_type => p_output_file_type) ||
702 formated_data_string (p_input_string => p_sui_er_pre_tax_redns
703 ,p_output_file_type => p_output_file_type) ||
704 formated_data_string (p_input_string => p_sit_qtr_wages
705 ,p_output_file_type => p_output_file_type) ||
706 formated_data_string (p_input_string => p_sit_withheld
707 ,p_output_file_type => p_output_file_type) ||
708 formated_data_string (p_input_string => p_sit_gross
709 ,p_output_file_type => p_output_file_type) ||
710 formated_data_string (p_input_string => p_sit_subjwh
711 ,p_output_file_type => p_output_file_type)||
712 formated_data_string (p_input_string => p_sit_subjnwh
713 ,p_output_file_type => p_output_file_type) ||
714 formated_data_string (p_input_string => p_sit_pre_tax_redns
715 ,p_output_file_type => p_output_file_type) ||
716 formated_data_string (p_input_string => p_sdi_qtr_wages
717 ,p_output_file_type => p_output_file_type) ||
718 formated_data_string (p_input_string => p_sdi_taxable
719 ,p_output_file_type => p_output_file_type) ||
720 formated_data_string (p_input_string => p_sdi_excess
721 ,p_output_file_type => p_output_file_type) ||
722 formated_data_string (p_input_string => p_sdi_subjwh
723 ,p_output_file_type => p_output_file_type) ||
724 formated_data_string (p_input_string => p_sdi_pre_tax_redns
725 ,p_output_file_type => p_output_file_type) ||
726 formated_data_string (p_input_string => p_hours_worked
727 ,p_output_file_type => p_output_file_type)||
728 formated_data_string (p_input_string => p_weeks_worked
729 ,p_output_file_type => p_output_file_type)||
730 formated_data_string (p_input_string => p_workers_compensation2_er
731 ,p_output_file_type => p_output_file_type)||
732 formated_data_string (p_input_string => p_workers_comp2_withheld
733 ,p_output_file_type => p_output_file_type)||
734 formated_data_string (p_input_string => p_workers_comp_withheld
735 ,p_output_file_type => p_output_file_type) ;
736
737 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
738
739 hr_utility.trace('Static Data1 = ' || lv_format1);
740 hr_utility.trace('Static Data2 = ' || lv_format2);
741 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
742
743 return (lv_format1);
744 END;
745
746
747 FUNCTION formated_static_data_NY (
748 p_tax_unit_name in varchar2
749 ,p_organization_name in varchar2
750 ,p_qtrname in varchar2
751 ,p_state_abbrev in varchar2
752 ,p_assignment_number in varchar2
753 ,p_full_name in varchar2
754 ,p_sui_qtr_wages in varchar2
755 ,p_sui_er_taxable in varchar2
756 ,p_sui_excess in varchar2
757 ,p_sui_er_subjwh in varchar2
758 ,p_sui_er_pre_tax_redns in varchar2
759 ,p_sit_qtr_wages in varchar2
760 ,p_sit_withheld in varchar2
761 ,p_sit_gross in varchar2
762 ,p_sit_subjwh in varchar2
763 ,p_sit_subjnwh in varchar2
764 ,p_sit_pre_tax_redns in varchar2
765 ,p_annual_gross_wages in varchar2
766 ,p_regular_earnings_pgy in varchar2
767 ,p_supp_fit_stt_pgy in varchar2
768 ,p_supp_nwfit_stt_pgy in varchar2
769 ,p_fit_non_w2_pre_tax_pgy in varchar2
770 ,p_pre_tax_dedns_fit_stt_pgy in varchar2
771 ,p_pre_tax_dedns_pgy in varchar2
772 ,p_output_file_type in varchar2
773 )
774 RETURN VARCHAR2
775 IS
776
777 lv_format1 VARCHAR2(32000);
778 lv_format2 VARCHAR2(32000);
779
780
781 BEGIN
782
783 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
784 lv_format1 :=
785 formated_data_string (p_input_string => p_tax_unit_name
786 ,p_output_file_type => p_output_file_type)||
787 formated_data_string (p_input_string => p_organization_name
788 ,p_output_file_type => p_output_file_type)||
789 formated_data_string (p_input_string => p_qtrname
790 ,p_output_file_type => p_output_file_type) ||
791 formated_data_string (p_input_string => p_state_abbrev
792 ,p_output_file_type => p_output_file_type)||
793 formated_data_string (p_input_string => p_assignment_number
794 ,p_output_file_type => p_output_file_type) ||
795 formated_data_string (p_input_string => p_full_name
796 ,p_output_file_type => p_output_file_type) ||
797 formated_data_string (p_input_string => p_sui_qtr_wages
798 ,p_output_file_type => p_output_file_type) ||
799 formated_data_string (p_input_string => p_sui_er_taxable
800 ,p_output_file_type => p_output_file_type) ||
801 formated_data_string (p_input_string => p_sui_excess
802 ,p_output_file_type => p_output_file_type) ||
803 formated_data_string (p_input_string => p_sui_er_subjwh
804 ,p_output_file_type => p_output_file_type) ||
805 formated_data_string (p_input_string => p_sui_er_pre_tax_redns
806 ,p_output_file_type => p_output_file_type) ||
807 formated_data_string (p_input_string => p_sit_qtr_wages
808 ,p_output_file_type => p_output_file_type) ||
809 formated_data_string (p_input_string => p_sit_withheld
810 ,p_output_file_type => p_output_file_type) ||
811 formated_data_string (p_input_string => p_sit_gross
812 ,p_output_file_type => p_output_file_type) ||
813 formated_data_string (p_input_string => p_sit_subjwh
814 ,p_output_file_type => p_output_file_type)||
815 formated_data_string (p_input_string => p_sit_subjnwh
816 ,p_output_file_type => p_output_file_type) ||
817 formated_data_string (p_input_string => p_sit_pre_tax_redns
818 ,p_output_file_type => p_output_file_type) ||
819 formated_data_string (p_input_string => p_annual_gross_wages
820 ,p_output_file_type => p_output_file_type) ||
821 formated_data_string (p_input_string => p_regular_earnings_pgy
822 ,p_output_file_type => p_output_file_type) ||
823 formated_data_string (p_input_string => p_supp_fit_stt_pgy
824 ,p_output_file_type => p_output_file_type) ||
825 formated_data_string (p_input_string => p_supp_nwfit_stt_pgy
826 ,p_output_file_type => p_output_file_type) ||
827 formated_data_string (p_input_string => p_fit_non_w2_pre_tax_pgy
828 ,p_output_file_type => p_output_file_type)||
829 formated_data_string (p_input_string =>p_pre_tax_dedns_fit_stt_pgy
830 ,p_output_file_type => p_output_file_type)||
831 formated_data_string (p_input_string => p_pre_tax_dedns_pgy
832 ,p_output_file_type => p_output_file_type);
833
834
835 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
836
837
838 hr_utility.trace('Static Data1 = ' || lv_format1);
839 hr_utility.trace('Static Data2 = ' || lv_format2);
840 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
841
842 return (lv_format1);
843 END;
844
845
846
847 PROCEDURE insert_error(errbuf OUT nocopy VARCHAR2,
848 retcode OUT nocopy NUMBER,
849 p_payroll_action_id IN NUMBER,
850 p_qtrname IN VARCHAR2) is
851
852 cursor c_assignments (cp_payroll_action_id in number) is
853 SELECT hou.name,
854 full_name,
855 paa.assignment_action_id,
856 paa.assignment_id,
857 paa.tax_unit_id,
858 state_code,
859 paf.assignment_number,
860 hou1.name,
861 pus.state_abbrev,
862 ppa.report_category
863 FROM pay_us_states pus,
864 hr_organization_units hou,
865 hr_organization_units hou1,
866 per_people_f ppf,
867 per_assignments_f paf,
868 pay_assignment_actions paa,
869 pay_payroll_actions ppa
870 WHERE ppa.payroll_action_id = p_payroll_action_id
871 AND ppa.payroll_action_id = paa.payroll_action_id
872 AND paa.assignment_id = paf.assignment_id
873 /* Added for bug 2506588 */
874 AND paf.effective_start_date =
875 (select max(paf2.effective_start_date)
876 from per_assignments_f paf2
877 where paf2.assignment_id = paf.assignment_id
878 and paf2.effective_start_date <= ppa.effective_date)
879 AND paf.effective_end_date >= ppa.start_date
880 AND paf.assignment_type = 'E'
881 /* Commented for bug 2506588
882 AND ppa.effective_date between paf.effective_start_date
883 and paf.effective_end_date*/
884 AND paf.person_id = ppf.person_id
885 AND ppa.effective_date between ppf.effective_start_date
886 and ppf.effective_end_date
887 AND paa.tax_unit_id = hou.organization_id
888 AND report_qualifier = pus.state_abbrev
889 and paf.organization_id = hou1.organization_id;
890 /*sackumar*/
891 cursor c_archive_dtls(cp_asg_act_id in number,
892 cp_tax_unit_id in number,
893 cp_state_code in varchar2) is
894 select fdi.user_name,fai.value
895 from ff_database_items fdi,
896 ff_archive_items fai,
897 ff_archive_item_contexts fac,
898 ff_archive_item_contexts fac1,
899 ff_contexts fc,
900 ff_contexts fc1
901 where fai.user_entity_id = fdi.user_entity_id
902 and fai.context1 = to_char(cp_asg_act_id)
903 /* context assignment action id */
904 and fac.archive_item_id = fai.archive_item_id
905 and fc.context_name = 'TAX_UNIT_ID'
906 and fc.context_id = fac.context_id
907 and ltrim(rtrim(fac.context)) = to_char(cp_tax_unit_id)
908 /* 2nd context of tax_unit_id */
909 and fac1.archive_item_id = fai.archive_item_id
910 and fc1.context_name = 'JURISDICTION_CODE'
911 and fc1.context_id = fac1.context_id
912 and substr(fac1.context,1,2) = substr(cp_state_code,1,2) ;
913
914 cursor c_archive_dtls_fed(cp_asg_act_id in number,
915 cp_tax_unit_id in number) is
916 select fdi.user_name,fai.value
917 from ff_database_items fdi,
918 ff_archive_items fai,
919 ff_archive_item_contexts fac,
920 ff_contexts fc
921 where fai.user_entity_id = fdi.user_entity_id
922 and fai.context1 = to_char(cp_asg_act_id)
923 /* context assignment action id */
924 and fc.context_name = 'TAX_UNIT_ID'
925 and fc.context_id = fac.context_id
926 and fac.archive_item_id = fai.archive_item_id
927 and ltrim(rtrim(fac.context)) = to_char(cp_tax_unit_id);
928 /* 2nd context of tax_unit_id */
929
930 cursor c_state (cp_payroll_action_id in number) is
931 select report_qualifier,business_group_id
932 from pay_payroll_actions
933 where payroll_action_id = cp_payroll_action_id;
934
935 cursor c_get_asg_details(p_asg_action_id in number,
936 p_asg_id in number) is
937 select fdi.user_name,fai.value
938 from ff_database_items fdi,
939 ff_archive_items fai,
940 ff_archive_item_contexts fac,
941 ff_contexts fc
942 where fai.user_entity_id = fdi.user_entity_id
943 and fai.context1 = to_char(p_asg_action_id)
944 /* context assignment action id */
945 and fc.context_name = 'ASSIGNMENT_ID'
946 and fc.context_id = fac.context_id
947 and fac.archive_item_id = fai.archive_item_id
948 and ltrim(rtrim(fac.context)) = to_char(p_asg_id)
949 and user_name in ('A_SCL_ASG_US_WORK_SCHEDULE','A_ASG_HOURS','A_ASG_FREQ');
950
951 /* Bug:3044939 */
952 cursor c_get_hour_calc_method(p_tax_unit_id in number) is
953 select org_information14
954 from hr_organization_information hoi
955 where org_information_context like 'SQWL GN Transmitter Rules%'
956 and hoi.organization_id = p_tax_unit_id;
957
958 /* Bug:14456648 */
959 cursor c_get_report_type(c_payroll_action_id number) is
960 select report_type
961 from pay_payroll_actions
962 where payroll_action_id = c_payroll_action_id;
963
964
965 TYPE numeric_table IS TABLE OF number(20,2)
966 INDEX BY BINARY_INTEGER;
967
968 TYPE text_table IS TABLE OF varchar2(2000)
969 INDEX BY BINARY_INTEGER;
970
971 lv_tax_unit_name varchar2(240);
972 lv_full_name varchar2(240);
973 lv_full_name_wc varchar2(240);
974 ln_asg_act_id number;
975 ln_asg_id number;
976 ln_tax_unit_id number;
977 lv_state_code varchar2(2);
978 lv_user_name varchar2(80);
979 lv_value varchar2(240) ;
980 lv_gross_earnings varchar2(240) ;
981 lv_sui_er_gross varchar2(240) ;
982 lv_sui_er_125 varchar2(240) ;
983 lv_sui_er_401 varchar2(240) ;
984 lv_sui_er_depcare varchar2(240) ;
985 lv_sui_er_subjwh varchar2(240) ;
986 lv_sui_er_taxable varchar2(240) ;
987 lv_sui_ee_withheld varchar2(240) ;
988 lv_sui_qtr_wages varchar2(240) ;
989 lv_sui_excess varchar2(240) ;
990 lv_sit_gross varchar2(240) ;
991 lv_sit_subjwh varchar2(240) ;
992 lv_sit_subjnwh varchar2(240) ;
993 lv_sit_125 varchar2(240) ;
994 lv_sit_401 varchar2(240) ;
995 lv_sit_depcare varchar2(240) ;
996 lv_sit_withheld varchar2(240) ;
997 lv_sit_qtr_wages varchar2(240) ;
998 lv_sdi_qtr_wages varchar2(240) ;
999 lv_sdi_taxable varchar2(240) ;
1000 lv_sdi_excess varchar2(240) ;
1001 lv_sdi_subjwh varchar2(240) ;
1002 lv_sdi_125 varchar2(240) ;
1003 lv_sdi_401 varchar2(240) ;
1004 lv_sdi_depcare varchar2(240);
1005 lv_annual_gross_wages varchar2(240) ;
1006 lv_regular_earnings_pgy varchar2(240) ;
1007 lv_supp_fit_stt_pgy varchar2(240) ;
1008 lv_supp_nwfit_stt_pgy varchar2(240) ;
1009 lv_def_comp_401_pry varchar2(240) ;
1010 lv_sec_125_pgy varchar2(240) ;
1011 lv_sit_withheld_pjgy varchar2(240) ;
1012 lv_dep_care_pgy varchar2(240) ;
1013 lv_hours_worked varchar2(240) ;
1014 lv_regular_hrs_pgq varchar2(240) ;
1015 lv_organization_name varchar2(240):=null ;
1016 lv_assignment_number varchar2(240):=null ;
1017 ln_count_rpt_total number:= 0;
1018 lv_state_abbrev varchar2(2) :=null;
1019 lv_report_category varchar2(30):=null;
1020 lv_neg_asg_count number :=0 ;
1021
1022 lv_sui_er_pre_tax_redns varchar2(240);
1023 lv_sit_pre_tax_redns varchar2(240);
1024 lv_sdi_pre_tax_redns varchar2(240);
1025
1026
1027 lv_pre_tax_dedns_fit_stt_pgy varchar2(240);
1028 lv_fit_non_w2_pre_tax_pgy varchar2(240);
1029 lv_pre_tax_dedns_pgy varchar2(240);
1030
1031 /*Bug# 5333916*/
1032 lv_workers_compensation2_er varchar2(240);
1033 lv_workers_comp2_withheld varchar2(240);
1034 lv_workers_comp_withheld varchar2(240);
1035
1036 lv_char_value number;
1037 lv_hours_worked_calc_method varchar2(240);
1038 lv_sui_sick_hours varchar2(240);
1039 lv_sui_vacation_hours varchar2(240);
1040 lv_sui_regular_hours varchar2(240);
1041 lv_sui_overtime_hours varchar2(240);
1042 lv_work_schedule varchar2(240) := NULL;
1043 lv_hours_per_week NUMBER := 40;
1044 lv_weeks_worked NUMBER := 0;
1045 lv_asg_freq varchar2(240) := 'Week';
1046 lv_asg_hours varchar2(240) := NULL;
1047 lv_bgrp_id NUMBER;
1048 lv_add_days DATE;
1049
1050
1051
1052 BEGIN
1053
1054 --hr_utility.trace_on (null, 'SQWLNEG');
1055 hr_utility.trace ('Entered Main package');
1056 hr_utility.trace ('Payroll Action Id = '||to_char(p_payroll_action_id));
1057 hr_utility.trace ('Qtrname = '||p_qtrname);
1058
1059 /* Change for Bug 14456648 starts */
1060 open c_get_report_type(p_payroll_action_id);
1061 fetch c_get_report_type into gv_report_type;
1062 IF c_get_report_type%NOTFOUND THEN
1063 gv_report_type :='SQWL';
1064 END IF;
1065 close c_get_report_type;
1066 /* Change for Bug 14456648 starts */
1067
1068
1069
1070 open c_assignments(p_payroll_action_id);
1071 hr_utility.trace ('Opened c_assignment cursor');
1072 loop
1073
1074 /* Initialixe variables */
1075
1076 lv_user_name := null;
1077 lv_value := null;
1078 lv_gross_earnings := null ;
1079 lv_sui_er_gross := null ;
1080 lv_sui_er_subjwh := null ;
1081 lv_sui_er_taxable := null ;
1082 lv_sui_ee_withheld := null ;
1083 lv_sui_qtr_wages := null ;
1084 lv_sui_excess := null ;
1085 lv_sit_gross := null ;
1086 lv_sit_subjwh := null ;
1087 lv_sit_subjnwh := null ;
1088 lv_sit_125 := null ;
1089 lv_sit_401 := null ;
1090 lv_sit_depcare := null ;
1091 lv_sit_withheld := null ;
1092 lv_sit_qtr_wages := null ;
1093 lv_sdi_qtr_wages := null ;
1094 lv_sdi_taxable := null ;
1095 lv_sdi_excess := null ;
1096 lv_annual_gross_wages := null ;
1097 lv_regular_earnings_pgy := null ;
1098 lv_supp_fit_stt_pgy := null ;
1099 lv_supp_nwfit_stt_pgy := null ;
1100 lv_def_comp_401_pry := null ;
1101 lv_sec_125_pgy := null ;
1102 lv_sit_withheld_pjgy := null ;
1103 lv_dep_care_pgy := null ;
1104 lv_hours_worked := null ;
1105 lv_regular_hrs_pgq := null ;
1106
1107 lv_sdi_pre_tax_redns := null;
1108 lv_sui_er_pre_tax_redns := null;
1109 lv_sit_pre_tax_redns := null;
1110
1111 lv_pre_tax_dedns_fit_stt_pgy := null;
1112 lv_fit_non_w2_pre_tax_pgy := null;
1113 lv_pre_tax_dedns_pgy := null;
1114
1115 lv_workers_compensation2_er := null ;
1116 lv_workers_comp2_withheld := null ;
1117 lv_workers_comp_withheld := null ;
1118
1119 lv_char_value := 0;
1120
1121
1122 /* rosie monge added the following */
1123
1124 lv_hours_worked_calc_method := 'W';
1125
1126 lv_sui_sick_hours := null;
1127 lv_sui_vacation_hours := null;
1128 lv_sui_regular_hours := null;
1129 lv_sui_overtime_hours := null;
1130
1131 /* end of code rosie monge added */
1132
1133 fetch c_assignments into lv_tax_unit_name,
1134 lv_full_name_wc,
1135 ln_asg_act_id,
1136 ln_asg_id,
1137 ln_tax_unit_id,
1138 lv_state_code,
1139 lv_assignment_number,
1140 lv_organization_name,
1141 lv_state_abbrev,
1142 lv_report_category;
1143
1144 if c_assignments%NOTFOUND then
1145 if c_assignments%ROWCOUNT = 0 then
1146 hr_utility.trace('Into Row Count = 0 ');
1147 lv_state_abbrev := null;
1148
1149 open c_state(p_payroll_action_id);
1150 fetch c_state into lv_state_abbrev,lv_bgrp_id;
1151 if c_state%notfound then
1152 exit;
1153 end if;
1154 close c_state;
1155
1156 if lv_state_abbrev = 'CA' then
1157
1158 insert into pay_us_rpt_totals (gre_name,
1159 organization_name,
1160 attribute2,
1161 attribute3,
1162 attribute30)
1163 values(p_qtrname,lv_state_abbrev,
1164 'No assignment actions have been created by the State quarterly Wage listing process.State Tax Rules may not have been defined properly','For California there could a setup issue related with Single/Multiple Wage plan',
1165 'STATE_QUARTERLY_ERROR_REPORT');
1166 else
1167
1168 insert into pay_us_rpt_totals (gre_name,
1169 organization_name,
1170 attribute2,
1171 attribute30)
1172 values(p_qtrname,lv_state_abbrev,
1173 'No assignment actions have been created by the State quarterly Wage listing process.State Tax Rules may not have been defined properly',
1174 'STATE_QUARTERLY_ERROR_REPORT');
1175
1176 end if;
1177
1178 end if;
1179 exit;
1180 end if;
1181 lv_full_name := replace(lv_full_name_wc,',');
1182
1183 hr_utility.trace('ln_asg_id ='||to_char(ln_asg_id));
1184 hr_utility.trace('ln_asg_act_id ='||to_char(ln_asg_act_id));
1185 hr_utility.trace('ln_tax_unit_id ='||to_char(ln_tax_unit_id));
1186 hr_utility.trace('lv_full_name ='||lv_full_name);
1187 hr_utility.trace('lv_full_name_wc ='||lv_full_name_wc);
1188 hr_utility.trace('lv_state_code ='||lv_state_code);
1189 hr_utility.trace('lv_report_category ='||lv_report_category);
1190
1191 open c_archive_dtls(ln_asg_act_id,
1192 ln_tax_unit_id,
1193 lv_state_code);
1194 hr_utility.trace('Opened c_archive_dtls');
1195 loop
1196 fetch c_archive_dtls into lv_user_name
1197 ,lv_value;
1198
1199 if c_archive_dtls%NOTFOUND then
1200 exit;
1201 end if ;
1202 if gv_report_type = 'SMWL' then /* Condition added for Bug 14456648 */
1203
1204 if lv_user_name = 'A_SUI_ER_GROSS_PER_JD_GRE_MONTH' then
1205 lv_sui_er_gross := lv_value;
1206 elsif lv_user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_MONTH' then
1207 lv_sui_er_subjwh := lv_value;
1208 elsif lv_user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_MONTH' then
1209 lv_sui_er_taxable := lv_value;
1210 elsif lv_user_name = 'A_SUI_EE_WITHHELD_PER_JD_GRE_MONTH' then
1211 lv_sui_ee_withheld := lv_value;
1212 elsif lv_user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_MONTH' then
1213 lv_sui_er_pre_tax_redns := lv_value;
1214 elsif lv_user_name = 'A_SIT_GROSS_PER_JD_GRE_MONTH' then
1215 lv_sit_gross := lv_value;
1216 elsif lv_user_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_MONTH' then
1217 lv_sit_subjwh := lv_value;
1218 elsif lv_user_name = 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_MONTH' then
1219 lv_sit_subjnwh := lv_value;
1220
1221 elsif lv_user_name = 'A_SIT_WITHHELD_PER_JD_GRE_MONTH' then
1222 lv_sit_withheld := lv_value;
1223 elsif lv_user_name = 'A_SIT_WITHHELD_PER_JD_GRE_YTD' then
1224 lv_sit_withheld_pjgy := lv_value;
1225
1226 elsif lv_user_name = 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_MONTH' then
1227 lv_sit_pre_tax_redns := lv_value;
1228
1229 elsif lv_user_name = 'A_SDI_EE_PRE_TAX_REDNS_PER_JD_GRE_MONTH' then
1230 lv_sdi_pre_tax_redns := lv_value;
1231
1232 elsif lv_user_name = 'A_SDI_EE_SUBJ_WHABLE_PER_JD_GRE_MONTH' then
1233 lv_sdi_subjwh := lv_value;
1234
1235 elsif lv_user_name = 'A_SDI_EE_TAXABLE_PER_JD_GRE_MONTH' then
1236 lv_sdi_taxable := lv_value;
1237
1238
1239 elsif lv_user_name = 'A_SUI_SICK_HOURS_BY_STATE_PER_JD_GRE_MONTH' then
1240 lv_sui_sick_hours := lv_value;
1241
1242 elsif lv_user_name ='A_SUI_VACATION_HOURS_BY_STATE_PER_JD_GRE_MONTH' then
1243 lv_sui_vacation_hours := lv_value;
1244
1245 elsif lv_user_name ='A_SUI_REGULAR_HOURS_BY_STATE_PER_JD_GRE_MONTH' then
1246 lv_sui_regular_hours := lv_value;
1247
1248 elsif lv_user_name ='A_SUI_OVERTIME_HOURS_BY_STATE_PER_JD_GRE_MONTH' then
1249 lv_sui_overtime_hours := lv_value;
1250
1251 elsif lv_user_name ='A_WORKERS_COMPENSATION2_ER_PER_JD_GRE_MONTH' then
1252 lv_workers_compensation2_er := lv_value;
1253
1254 elsif lv_user_name ='A_WORKERS_COMP2_WITHHELD_PER_JD_GRE_MONTH' then
1255 lv_workers_comp2_withheld := lv_value;
1256
1257 elsif lv_user_name ='A_WORKERS_COMP_WITHHELD_PER_JD_GRE_MONTH' then
1258 lv_workers_comp_withheld := lv_value;
1259
1260 end if;
1261 else
1262 if lv_user_name = 'A_SUI_ER_GROSS_PER_JD_GRE_QTD' then
1263 lv_sui_er_gross := lv_value;
1264 elsif lv_user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD' then
1265 lv_sui_er_subjwh := lv_value;
1266 elsif lv_user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD' then
1267 lv_sui_er_taxable := lv_value;
1268 elsif lv_user_name = 'A_SUI_EE_WITHHELD_PER_JD_GRE_QTD' then
1269 lv_sui_ee_withheld := lv_value;
1270 elsif lv_user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD' then
1271 lv_sui_er_pre_tax_redns := lv_value;
1272 elsif lv_user_name = 'A_SIT_GROSS_PER_JD_GRE_QTD' then
1273 lv_sit_gross := lv_value;
1274 elsif lv_user_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_QTD' then
1275 lv_sit_subjwh := lv_value;
1276 elsif lv_user_name = 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_QTD' then
1277 lv_sit_subjnwh := lv_value;
1278
1279 elsif lv_user_name = 'A_SIT_WITHHELD_PER_JD_GRE_QTD' then
1280 lv_sit_withheld := lv_value;
1281 elsif lv_user_name = 'A_SIT_WITHHELD_PER_JD_GRE_YTD' then
1282 lv_sit_withheld_pjgy := lv_value;
1283
1284 elsif lv_user_name = 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD' then
1285 lv_sit_pre_tax_redns := lv_value;
1286
1287 elsif lv_user_name = 'A_SDI_EE_PRE_TAX_REDNS_PER_JD_GRE_QTD' then
1288 lv_sdi_pre_tax_redns := lv_value;
1289
1290 elsif lv_user_name = 'A_SDI_EE_SUBJ_WHABLE_PER_JD_GRE_QTD' then
1291 lv_sdi_subjwh := lv_value;
1292
1293 elsif lv_user_name = 'A_SDI_EE_TAXABLE_PER_JD_GRE_QTD' then
1294 lv_sdi_taxable := lv_value;
1295
1296 /* rosie monge added the following code */
1297
1298 elsif lv_user_name = 'A_SUI_SICK_HOURS_BY_STATE_PER_JD_GRE_QTD' then
1299 lv_sui_sick_hours := lv_value;
1300
1301 elsif lv_user_name ='A_SUI_VACATION_HOURS_BY_STATE_PER_JD_GRE_QTD' then
1302 lv_sui_vacation_hours := lv_value;
1303
1304 elsif lv_user_name ='A_SUI_REGULAR_HOURS_BY_STATE_PER_JD_GRE_QTD' then
1305 lv_sui_regular_hours := lv_value;
1306
1307 elsif lv_user_name ='A_SUI_OVERTIME_HOURS_BY_STATE_PER_JD_GRE_QTD' then
1308 lv_sui_overtime_hours := lv_value;
1309
1310 elsif lv_user_name ='A_WORKERS_COMPENSATION2_ER_PER_JD_GRE_QTD' then
1311 lv_workers_compensation2_er := lv_value;
1312
1313 elsif lv_user_name ='A_WORKERS_COMP2_WITHHELD_PER_JD_GRE_QTD' then
1314 lv_workers_comp2_withheld := lv_value;
1315
1316 elsif lv_user_name ='A_WORKERS_COMP_WITHHELD_PER_JD_GRE_QTD' then
1317 lv_workers_comp_withheld := lv_value;
1318
1319 end if;
1320 end if;
1321
1322 end loop;
1323 close c_archive_dtls;
1324
1325
1326 open c_archive_dtls_fed(ln_asg_act_id,
1327 ln_tax_unit_id);
1328 hr_utility.trace('Opened c_archive_dtls_fed');
1329 loop
1330 fetch c_archive_dtls_fed into lv_user_name
1331 ,lv_value;
1332
1333 if c_archive_dtls_fed%NOTFOUND then
1334 exit;
1335 end if ;
1336
1337 if lv_user_name = 'A_REGULAR_EARNINGS_PER_GRE_YTD' then
1338 lv_regular_earnings_pgy := lv_value;
1339 elsif lv_user_name = 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' then
1340 lv_supp_fit_stt_pgy := lv_value;
1341 elsif lv_user_name = 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' then
1342 lv_supp_nwfit_stt_pgy := lv_value;
1343 elsif lv_user_name = 'A_DEF_COMP_401K_PER_GRE_YTD' then
1344 lv_def_comp_401_pry := lv_value;
1345 elsif lv_user_name = 'A_SECTION_125_PER_GRE_YTD' then
1346 lv_sec_125_pgy := lv_value;
1347 elsif lv_user_name = 'A_DEPENDENT_CARE_PER_GRE_YTD' then
1348 lv_dep_care_pgy := lv_value;
1349 elsif lv_user_name = 'A_REGULAR_HOURS_WORKED_PER_GRE_QTD' then
1350 lv_regular_hrs_pgq := lv_value;
1351 elsif lv_user_name = 'A_GROSS_EARNINGS_PER_GRE_QTD' then
1352 lv_gross_earnings := lv_value;
1353 elsif lv_user_name = 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' then
1354 lv_pre_tax_dedns_fit_stt_pgy := lv_value;
1355 elsif lv_user_name = 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' then
1356 lv_fit_non_w2_pre_tax_pgy := lv_value;
1357 elsif lv_user_name = 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' then
1358 lv_pre_tax_dedns_pgy := lv_value;
1359 end if;
1360
1361 end loop;
1362 close c_archive_dtls_fed;
1363
1364 FOR I in c_get_asg_details(ln_asg_act_id, ln_asg_id)
1365 LOOP
1366 IF I.user_name = 'A_SCL_ASG_US_WORK_SCHEDULE' THEN
1367 hr_utility.trace('Getting A_SCL_ASG_US_WORK_SCHEDULE ');
1368 lv_work_schedule := I.value;
1369 ELSIF I.user_name = 'A_ASG_HOURS' THEN
1370 lv_asg_hours := I.value;
1371 ELSIF I.user_name = 'A_ASG_FREQ' THEN
1372 lv_asg_freq := I.value;
1373 END IF;
1374 END LOOP;
1375
1376 hr_utility.trace('A_SCL_ASG_US_WORK_SCHEDULE ' ||lv_work_schedule);
1377 hr_utility.trace('A_ASG_HOURS ' ||lv_asg_hours);
1378 hr_utility.trace('A_ASG_FREQ ' ||lv_asg_freq);
1379
1380 /* Need business group id for calculating weeks worked */
1381
1382 OPEN c_state(p_payroll_action_id);
1383 FETCH c_state INTO lv_state_abbrev,lv_bgrp_id;
1384 IF c_state%NOTFOUND THEN
1385 EXIT;
1386 END IF;
1387 CLOSE c_state;
1388
1389 /* calculate the derived balances here */
1390 hr_utility.trace('Going to calculate derived balances');
1391
1392 lv_sui_qtr_wages := lv_sui_er_subjwh - lv_sui_er_pre_tax_redns;
1393 lv_sui_excess := lv_sui_qtr_wages - lv_sui_er_taxable;
1394 lv_sit_qtr_wages := lv_sit_subjwh + lv_sit_subjnwh - lv_sit_pre_tax_redns;
1395 lv_sdi_qtr_wages := lv_sdi_subjwh - lv_sdi_pre_tax_redns;
1396 lv_sdi_excess := lv_sdi_subjwh - lv_sdi_pre_tax_redns - lv_sdi_taxable;
1397
1398 hr_utility.trace('lv_tax_unit_name ='||lv_tax_unit_name);
1399 hr_utility.trace('lv_organization_name ='||lv_organization_name);
1400 hr_utility.trace('lv_state_abbrev ='||lv_state_abbrev);
1401 hr_utility.trace('Qtrname ='||p_qtrname);
1402 hr_utility.trace('lv_assignment_number ='||lv_assignment_number);
1403 hr_utility.trace('lv_full_name ='||lv_full_name);
1404 hr_utility.trace('lv_gross_earnings ='||lv_gross_earnings);
1405 hr_utility.trace('lv_sui_er_gross ='||lv_sui_er_gross);
1406 hr_utility.trace('lv_sui_er_125 ='||lv_sui_er_125);
1407 hr_utility.trace('lv_sui_er_401 ='||lv_sui_er_401);
1408 hr_utility.trace('lv_sui_er_depcare ='||lv_sui_er_depcare);
1409 hr_utility.trace('lv_sui_er_subjwh ='||lv_sui_er_subjwh);
1410 hr_utility.trace('lv_sui_er_taxable ='||lv_sui_er_taxable);
1411 hr_utility.trace('lv_sui_qtr_wages ='||lv_sui_qtr_wages);
1412 hr_utility.trace('lv_sui_excess ='||lv_sui_excess);
1413 hr_utility.trace('lv_sit_gross ='||lv_sit_gross);
1414 hr_utility.trace('lv_sit_subjwh ='||lv_sit_subjwh);
1415 hr_utility.trace('lv_sit_subjnwh ='||lv_sit_subjnwh);
1416 hr_utility.trace('lv_sit_125 ='||lv_sit_125);
1417 hr_utility.trace('lv_sit_401 ='||lv_sit_401);
1418 hr_utility.trace('lv_sit_depcare ='||lv_sit_depcare);
1419 hr_utility.trace('lv_sit_withheld ='||lv_sit_withheld);
1420 hr_utility.trace('lv_sit_qtr_wages ='||lv_sit_qtr_wages);
1421 hr_utility.trace('lv_hours_worked ='||lv_hours_worked);
1422 hr_utility.trace('lv_annual_gross_wages ='||lv_annual_gross_wages);
1423 hr_utility.trace('lv_regular_earngs_pgy='||lv_regular_earnings_pgy);
1424 hr_utility.trace('lv_supp_fit_stt_pgy ='||lv_supp_fit_stt_pgy);
1425 hr_utility.trace('lv_supp_fit_stt_pgy ='||lv_supp_fit_stt_pgy);
1426 hr_utility.trace('lv_supp_nwfit_stt_pgy ='||lv_supp_nwfit_stt_pgy);
1427 hr_utility.trace('lv_def_comp_401_pry ='||lv_def_comp_401_pry);
1428 hr_utility.trace('lv_sec_125_pgy ='||lv_sec_125_pgy);
1429 hr_utility.trace('lv_sit_withheld_pjgy ='||lv_sit_withheld_pjgy);
1430 hr_utility.trace('lv_dep_care_pgy ='||lv_dep_care_pgy);
1431 hr_utility.trace('lv_hours_worked_calculation_method='|| lv_hours_worked_calc_method );
1432 hr_utility.trace('lv_sui_sick_hours_by_state_per_jd_gre_qtd ='|| lv_sui_sick_hours);
1433 hr_utility.trace('lv_sui_vacation_hours_by_state_per_jd_gre_qtd =' || lv_sui_vacation_hours);
1434 hr_utility.trace('lv_sui_regular_hours_by_state_per_jd_gre_qtd =' || lv_sui_regular_hours);
1435 hr_utility.trace('lv_sui_overtime_hours_by_state_per_jd_gre_qtd =' || lv_sui_overtime_hours);
1436 hr_utility.trace('lv_work_schedule ='||lv_work_schedule);
1437
1438
1439 /* regular hours */
1440
1441 IF lv_gross_earnings <> '0' then
1442
1443 /* This query selects the value for the Flag Hours Worked Calculation Method */
1444
1445 OPEN c_get_hour_calc_method(ln_tax_unit_id);
1446 FETCH c_get_hour_calc_method INTO lv_Hours_Worked_Calc_Method;
1447
1448 /* Bug:3044939 */
1449 IF c_get_hour_calc_method%NOTFOUND THEN
1450
1451 lv_Hours_Worked_Calc_Method := 'W'; --Default Value
1452 hr_utility.trace('NO Data found. So setting c_get_hour_calc_method to default value');
1453
1454 END IF;
1455
1456 CLOSE c_get_hour_calc_method;
1457
1458 hr_utility.trace('lv_hours_worked_calc_method '||lv_hours_worked_calc_method);
1459
1460 /* We have the Calculation Method. Lets calculate the hours and weeks worked */
1461
1462 IF nvl(lv_Hours_Worked_Calc_Method,'W') = 'W' then
1463
1464 lv_hours_worked :=
1465 (lv_regular_hrs_pgq *
1466 lv_sui_er_gross/lv_gross_earnings);
1467
1468 ELSIF lv_Hours_Worked_Calc_Method ='B' then
1469
1470 lv_hours_worked := nvl(to_number(lv_sui_sick_hours),0)
1471 + nvl(to_number(lv_sui_vacation_hours),0)
1472 + nvl(to_number(lv_sui_regular_hours),0)
1473 + nvl(to_number(lv_sui_overtime_hours),0);
1474
1475 END IF;
1476
1477 hr_utility.trace('lv_hours_worked =' || lv_hours_worked);
1478 hr_utility.trace('lv_work_schedule =' || lv_work_schedule);
1479 hr_utility.trace('lv_asg_freq =' || lv_asg_freq);
1480 hr_utility.trace('lv_asg_hours =' || lv_asg_hours);
1481
1482
1483 IF (lv_work_schedule is null ) then
1484 lv_add_days := fffunc.add_days(sysdate,6);
1485 lv_hours_per_week := hr_us_ff_udfs.standard_hours_worked( lv_asg_hours,
1486 sysdate,
1487 lv_add_days,
1488 lv_asg_freq);
1489 hr_utility.trace('lv_add_days = '||lv_add_days);
1490 hr_utility.trace('lv_hours_per_week = '||to_char(lv_hours_per_week));
1491 ELSE
1492 lv_hours_per_week := hr_us_ff_udfs.work_schedule_total_hours(lv_bgrp_id,
1493 lv_work_schedule,
1494 null,
1495 null);
1496 hr_utility.trace('Work Schedule found = '||lv_work_schedule);
1497 hr_utility.trace('lv_hours_per_week = '||to_char(lv_hours_per_week));
1498 END IF;
1499
1500 /* calculating the Weeks Worked */
1501 IF nvl(lv_hours_per_week,0) <> 0 THEN
1502 hr_utility.trace('lv_hours_per_week <> 0 ');
1503 lv_weeks_worked := round(lv_hours_worked/lv_hours_per_week);
1504 hr_utility.trace('lv_weeks_worked = '||to_char(lv_weeks_worked));
1505
1506 IF lv_weeks_worked > 14 THEN
1507 lv_weeks_worked := 14;
1508 END IF;
1509
1510 END IF;
1511 END IF;
1512
1513 /* main */
1514
1515 if (lv_state_abbrev in ( 'AL' ,'AZ' ,'CO' ,'IA' ,'IL' ,'IN'
1516 ,'KS' ,'KY' ,'MA' ,'ME' ,'MN' ,'MO'
1517 ,'MT' ,'NC' ,'NV' ,'OH' ,'OK' ,'SC'
1518 ,'TN' ,'TX' ,'VT' ,'WV' ,'MS' ,'ND'
1519 ,'AR' ,'DE' ,'LA' ,'NE' ,'NH' ,'OR'
1520 ,'UT' ,'VA' ,'WI' ,'WY' ,'HI' ,'MI'
1521 ,'RI' ,'CT'
1522 )
1523 or (lv_state_abbrev = 'MD' and lv_report_category ='RT')
1524 or (lv_state_abbrev = 'SD' and lv_report_category ='RT')) then
1525
1526 BEGIN
1527
1528 lv_char_value := to_number(lv_sui_qtr_wages);
1529 lv_char_value := to_number(lv_sui_excess);
1530 lv_char_value := to_number(lv_sui_er_taxable);
1531 lv_char_value := to_number(lv_sit_qtr_wages);
1532 lv_char_value := to_number(lv_sit_gross);
1533 lv_char_value := to_number(lv_sit_withheld);
1534 lv_char_value := to_number(lv_hours_worked);
1535
1536 if (lv_sui_qtr_wages < 0 or
1537 lv_sui_excess < 0 or
1538 lv_sui_er_taxable < 0 or
1539 lv_sit_qtr_wages < 0 or
1540 lv_sit_gross < 0 or
1541 lv_sit_withheld < 0 or
1542 (lv_state_abbrev in ('MN' ,'VT' ,'OR' ,'WA' ,'WY' ) and lv_hours_worked < 0) or
1543 (lv_state_abbrev in ('MA','DE','OH') and lv_weeks_worked < 0)
1544 ) THEN
1545
1546 hr_utility.trace('Entered negative reporting loop');
1547
1548 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1549
1550 if lv_neg_asg_count = 1 then
1551
1552 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
1553
1554 end if;
1555
1556
1557 fnd_file.put_line(fnd_file.output,formated_static_data (
1558 lv_tax_unit_name,
1559 lv_organization_name,
1560 p_qtrname,
1561 lv_state_abbrev,
1562 lv_assignment_number,
1563 lv_full_name,
1564 lv_sui_qtr_wages,
1565 lv_sui_er_taxable,
1566 lv_sui_excess,
1567 lv_sui_er_subjwh,
1568 lv_sui_er_pre_tax_redns,
1569 lv_sit_qtr_wages,
1570 lv_sit_withheld,
1571 lv_sit_gross,
1572 lv_sit_subjwh,
1573 lv_sit_subjnwh,
1574 lv_sit_pre_tax_redns,
1575 lv_hours_worked,
1576 lv_weeks_worked,
1577 'CSV'));
1578
1579 end if; /* if negative then */
1580
1581 EXCEPTION
1582 when value_error then
1583
1584 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1585
1586 if lv_neg_asg_count = 1 then
1587 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
1588 end if;
1589
1590 fnd_file.put_line(fnd_file.output,formated_static_data (
1591 lv_tax_unit_name,
1592 lv_organization_name,
1593 p_qtrname,
1594 lv_state_abbrev,
1595 lv_assignment_number,
1596 lv_full_name,
1597 lv_sui_qtr_wages,
1598 lv_sui_er_taxable,
1599 lv_sui_excess,
1600 lv_sui_er_subjwh,
1601 lv_sui_er_pre_tax_redns,
1602 lv_sit_qtr_wages,
1603 lv_sit_withheld,
1604 lv_sit_gross,
1605 lv_sit_subjwh,
1606 lv_sit_subjnwh,
1607 lv_sit_pre_tax_redns,
1608 lv_hours_worked,
1609 lv_weeks_worked,
1610 'CSV'));
1611
1612 END;
1613
1614 end if; /* ICESA or SSA states */
1615
1616
1617 if lv_state_abbrev = 'PA' then
1618
1619 BEGIN
1620 lv_char_value := to_number(lv_sui_qtr_wages);
1621 lv_char_value := to_number(lv_sui_er_taxable);
1622 lv_char_value := to_number(lv_sui_excess);
1623 lv_char_value := to_number(lv_sit_qtr_wages);
1624 lv_char_value := to_number(lv_sit_gross);
1625 lv_char_value := to_number(lv_sit_withheld);
1626 lv_char_value := to_number(lv_hours_worked);
1627
1628
1629 if (lv_sui_qtr_wages < 0 or
1630 lv_sui_er_taxable < 0 or
1631 lv_sui_excess < 0 or
1632 lv_sit_qtr_wages < 0 or
1633 lv_sit_gross < 0 or
1634 lv_sit_withheld < 0 or
1635 lv_weeks_worked < 0) then
1636
1637 hr_utility.trace('Entered negative reporting loop');
1638
1639 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1640 if lv_neg_asg_count = 1 then
1641 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
1642 end if;
1643
1644 fnd_file.put_line(fnd_file.output,formated_static_data (
1645 lv_tax_unit_name,
1646 lv_organization_name,
1647 p_qtrname,
1648 lv_state_abbrev,
1649 lv_assignment_number,
1650 lv_full_name,
1651 lv_sui_qtr_wages,
1652 lv_sui_er_taxable,
1653 lv_sui_excess,
1654 lv_sui_er_subjwh,
1655 lv_sui_er_pre_tax_redns,
1656 lv_sit_qtr_wages,
1657 lv_sit_withheld,
1658 lv_sit_gross,
1659 lv_sit_subjwh,
1660 lv_sit_subjnwh,
1661 lv_sit_pre_tax_redns,
1662 lv_hours_worked,
1663 lv_weeks_worked,
1664 'CSV'));
1665
1666 end if; /* if negative then */
1667
1668 EXCEPTION
1669
1670 when value_error then
1671
1672 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1673
1674 if lv_neg_asg_count = 1 then
1675
1676 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
1677
1678 end if;
1679
1680
1681 fnd_file.put_line(fnd_file.output,formated_static_data (
1682 lv_tax_unit_name,
1683 lv_organization_name,
1684 p_qtrname,
1685 lv_state_abbrev,
1686 lv_assignment_number,
1687 lv_full_name,
1688 lv_sui_qtr_wages,
1689 lv_sui_er_taxable,
1690 lv_sui_excess,
1691 lv_sui_er_subjwh,
1692 lv_sui_er_pre_tax_redns,
1693 lv_sit_qtr_wages,
1694 lv_sit_withheld,
1695 lv_sit_gross,
1696 lv_sit_subjwh,
1697 lv_sit_subjnwh,
1698 lv_sit_pre_tax_redns,
1699 lv_hours_worked,
1700 lv_weeks_worked,
1701 'CSV'));
1702
1703 END;
1704
1705 end if; /* PA */
1706
1707 if lv_state_abbrev in ( 'AK','DC','GA','PR','ID') then --Custom
1708
1709 BEGIN
1710 lv_char_value := to_number(lv_sui_qtr_wages);
1711 lv_char_value := to_number(lv_sui_er_taxable);
1712 lv_char_value := to_number(lv_sui_excess);
1713 lv_char_value := to_number(lv_sit_qtr_wages);
1714 lv_char_value := to_number(lv_sit_withheld);
1715
1716 if (lv_sui_qtr_wages < 0 or
1717 lv_sui_er_taxable < 0 or
1718 lv_sui_excess < 0 or
1719 lv_sit_qtr_wages < 0 or
1720 lv_sit_withheld < 0 ) then
1721
1722 hr_utility.trace('Entered negative reporting loop');
1723
1724 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1725
1726 if lv_neg_asg_count = 1 then
1727
1728 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
1729
1730 end if;
1731
1732 fnd_file.put_line(fnd_file.output,formated_static_data (
1733 lv_tax_unit_name,
1734 lv_organization_name,
1735 p_qtrname,
1736 lv_state_abbrev,
1737 lv_assignment_number,
1738 lv_full_name,
1739 lv_sui_qtr_wages,
1740 lv_sui_er_taxable,
1741 lv_sui_excess,
1742 lv_sui_er_subjwh,
1743 lv_sui_er_pre_tax_redns,
1744 lv_sit_qtr_wages,
1745 lv_sit_withheld,
1746 lv_sit_gross,
1747 lv_sit_subjwh,
1748 lv_sit_subjnwh,
1749 lv_sit_pre_tax_redns,
1750 lv_hours_worked,
1751 lv_weeks_worked,
1752 'CSV'));
1753 END IF;
1754
1755 EXCEPTION
1756
1757 when value_error then
1758
1759 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1760
1761 if lv_neg_asg_count = 1 then
1762 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
1763 end if;
1764
1765 fnd_file.put_line(fnd_file.output,formated_static_data (
1766 lv_tax_unit_name,
1767 lv_organization_name,
1768 p_qtrname,
1769 lv_state_abbrev,
1770 lv_assignment_number,
1771 lv_full_name,
1772 lv_sui_qtr_wages,
1773 lv_sui_er_taxable,
1774 lv_sui_excess,
1775 lv_sui_er_subjwh,
1776 lv_sui_er_pre_tax_redns,
1777 lv_sit_qtr_wages,
1778 lv_sit_withheld,
1779 lv_sit_gross,
1780 lv_sit_subjwh,
1781 lv_sit_subjnwh,
1782 lv_sit_pre_tax_redns,
1783 lv_hours_worked,
1784 lv_weeks_worked,
1785 'CSV'));
1786 END;
1787
1788 end if; /* Custom */
1789
1790 if lv_state_abbrev ='NM' then /*Bug # 5333916 */
1791
1792 BEGIN
1793 lv_char_value := to_number(lv_sui_qtr_wages);
1794 lv_char_value := to_number(lv_sui_er_taxable);
1795 lv_char_value := to_number(lv_sui_excess);
1796 lv_char_value := to_number(lv_sit_qtr_wages);
1797 lv_char_value := to_number(lv_sit_withheld);
1798 lv_char_value := to_number(lv_workers_compensation2_er);
1799 lv_char_value := to_number(lv_workers_comp2_withheld);
1800 lv_char_value := to_number(lv_workers_comp_withheld);
1801
1802 if ( lv_sui_qtr_wages < 0 or
1803 lv_sui_er_taxable < 0 or
1804 lv_sui_excess < 0 or
1805 lv_sit_qtr_wages < 0 or
1806 lv_sit_withheld < 0 or
1807 lv_workers_compensation2_er < 0 or
1808 lv_workers_comp2_withheld < 0 or
1809 lv_workers_comp_withheld < 0
1810 ) then
1811
1812 hr_utility.trace('Entered negative reporting loop');
1813
1814 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1815
1816 if lv_neg_asg_count = 1 then
1817
1818 fnd_file.put_line(fnd_file.output,formated_Static_header_NM('CSV'));
1819
1820 end if;
1821
1822 fnd_file.put_line(fnd_file.output,formated_static_data_NM (
1823 lv_tax_unit_name,
1824 lv_organization_name,
1825 p_qtrname,
1826 lv_state_abbrev,
1827 lv_assignment_number,
1828 lv_full_name,
1829 lv_sui_qtr_wages,
1830 lv_sui_er_taxable,
1831 lv_sui_excess,
1832 lv_sui_er_subjwh,
1833 lv_sui_er_pre_tax_redns,
1834 lv_sit_qtr_wages,
1835 lv_sit_withheld,
1836 lv_sit_gross,
1837 lv_sit_subjwh,
1838 lv_sit_subjnwh,
1839 lv_sit_pre_tax_redns,
1840 lv_sdi_qtr_wages,
1841 lv_sdi_taxable,
1842 lv_sdi_excess,
1843 lv_sdi_subjwh,
1844 lv_sdi_pre_tax_redns,
1845 lv_hours_worked,
1846 lv_weeks_worked,
1847 lv_workers_compensation2_er,
1848 lv_workers_comp2_withheld,
1849 lv_workers_comp_withheld,
1850 'CSV'));
1851
1852 END IF;
1853
1854 EXCEPTION
1855
1856 when value_error then
1857
1858 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1859
1860 if lv_neg_asg_count = 1 then
1861 fnd_file.put_line(fnd_file.output,formated_Static_header_NM('CSV'));
1862 end if;
1863
1864 fnd_file.put_line(fnd_file.output,formated_static_data_NM (
1865 lv_tax_unit_name,
1866 lv_organization_name,
1867 p_qtrname,
1868 lv_state_abbrev,
1869 lv_assignment_number,
1870 lv_full_name,
1871 lv_sui_qtr_wages,
1872 lv_sui_er_taxable,
1873 lv_sui_excess,
1874 lv_sui_er_subjwh,
1875 lv_sui_er_pre_tax_redns,
1876 lv_sit_qtr_wages,
1877 lv_sit_withheld,
1878 lv_sit_gross,
1879 lv_sit_subjwh,
1880 lv_sit_subjnwh,
1881 lv_sit_pre_tax_redns,
1882 lv_sdi_qtr_wages,
1883 lv_sdi_taxable,
1884 lv_sdi_excess,
1885 lv_sdi_subjwh,
1886 lv_sdi_pre_tax_redns,
1887 lv_hours_worked,
1888 lv_weeks_worked,
1889 lv_workers_compensation2_er,
1890 lv_workers_comp2_withheld,
1891 lv_workers_comp_withheld,
1892 'CSV'));
1893
1894 END;
1895
1896 end if; /* NM */
1897
1898 if lv_state_abbrev = 'FL' then
1899
1900 BEGIN
1901 lv_char_value := to_number(lv_sui_qtr_wages);
1902 lv_char_value := to_number(lv_sui_er_taxable);
1903 lv_char_value := to_number(lv_sui_excess);
1904 lv_char_value := to_number(lv_sit_qtr_wages);
1905 lv_char_value := to_number(lv_sit_withheld);
1906 lv_char_value := to_number(lv_hours_worked);
1907
1908
1909 if (lv_sui_qtr_wages < 0 or
1910 lv_sui_er_taxable < 0 or
1911 lv_sui_excess < 0 or
1912 lv_sit_qtr_wages < 0 or
1913 lv_sit_withheld < 0 ) then
1914
1915 hr_utility.trace('Entered negative reporting loop');
1916
1917 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1918
1919 if lv_neg_asg_count = 1 then
1920
1921 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
1922
1923 end if;
1924
1925 fnd_file.put_line(fnd_file.output,formated_static_data (
1926 lv_tax_unit_name,
1927 lv_organization_name,
1928 p_qtrname,
1929 lv_state_abbrev,
1930 lv_assignment_number,
1931 lv_full_name,
1932 lv_sui_qtr_wages,
1933 lv_sui_er_taxable,
1934 lv_sui_excess,
1935 lv_sui_er_subjwh,
1936 lv_sui_er_pre_tax_redns,
1937 lv_sit_qtr_wages,
1938 lv_sit_withheld,
1939 lv_sit_gross,
1940 lv_sit_subjwh,
1941 lv_sit_subjnwh,
1942 lv_sit_pre_tax_redns,
1943 lv_hours_worked,
1944 lv_weeks_worked,
1945 'CSV'));
1946
1947 end if; /* if negative then */
1948
1949 EXCEPTION
1950
1951 when value_error then
1952
1953 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
1954
1955 if lv_neg_asg_count = 1 then
1956
1957 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
1958
1959 end if;
1960
1961
1962 fnd_file.put_line(fnd_file.output,formated_static_data (
1963 lv_tax_unit_name,
1964 lv_organization_name,
1965 p_qtrname,
1966 lv_state_abbrev,
1967 lv_assignment_number,
1968 lv_full_name,
1969 lv_sui_qtr_wages,
1970 lv_sui_er_taxable,
1971 lv_sui_excess,
1972 lv_sui_er_subjwh,
1973 lv_sui_er_pre_tax_redns,
1974 lv_sit_qtr_wages,
1975 lv_sit_withheld,
1976 lv_sit_gross,
1977 lv_sit_subjwh,
1978 lv_sit_subjnwh,
1979 lv_sit_pre_tax_redns,
1980 lv_hours_worked,
1981 lv_weeks_worked,
1982 'CSV'));
1983
1984 END;
1985
1986 end if; /* FL */
1987
1988
1989 if lv_state_abbrev = 'NJ' then
1990
1991 BEGIN
1992
1993 lv_char_value := to_number(lv_sui_qtr_wages);
1994 lv_char_value := to_number(lv_sui_excess);
1995 lv_char_value := to_number(lv_sui_er_taxable);
1996 lv_char_value := to_number(lv_sit_qtr_wages);
1997 lv_char_value := to_number(lv_sit_withheld);
1998 lv_char_value := to_number(lv_hours_worked);
1999
2000 if (lv_sui_qtr_wages < 0 or
2001 lv_sui_excess < 0 or
2002 lv_sui_er_taxable < 0 or
2003 lv_sit_qtr_wages < 0 or
2004 lv_sit_withheld < 0 or
2005 lv_weeks_worked < 0) then
2006
2007 hr_utility.trace('Entered negative reporting loop');
2008
2009 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2010
2011 if lv_neg_asg_count = 1 then
2012
2013 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
2014
2015 end if;
2016
2017
2018
2019 fnd_file.put_line(fnd_file.output,formated_static_data (
2020 lv_tax_unit_name,
2021 lv_organization_name,
2022 p_qtrname,
2023 lv_state_abbrev,
2024 lv_assignment_number,
2025 lv_full_name,
2026 lv_sui_qtr_wages,
2027 lv_sui_er_taxable,
2028 lv_sui_excess,
2029 lv_sui_er_subjwh,
2030 lv_sui_er_pre_tax_redns,
2031 lv_sit_qtr_wages,
2032 lv_sit_withheld,
2033 lv_sit_gross,
2034 lv_sit_subjwh,
2035 lv_sit_subjnwh,
2036 lv_sit_pre_tax_redns,
2037 lv_hours_worked,
2038 lv_weeks_worked,
2039 'CSV'));
2040
2041 end if; /* if negative then */
2042
2043 EXCEPTION
2044
2045 when value_error then
2046
2047 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2048
2049 if lv_neg_asg_count = 1 then
2050
2051 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
2052
2053 end if;
2054
2055
2056
2057 fnd_file.put_line(fnd_file.output,formated_static_data (
2058 lv_tax_unit_name,
2059 lv_organization_name,
2060 p_qtrname,
2061 lv_state_abbrev,
2062 lv_assignment_number,
2063 lv_full_name,
2064 lv_sui_qtr_wages,
2065 lv_sui_er_taxable,
2066 lv_sui_excess,
2067 lv_sui_er_subjwh,
2068 lv_sui_er_pre_tax_redns,
2069 lv_sit_qtr_wages,
2070 lv_sit_withheld,
2071 lv_sit_gross,
2072 lv_sit_subjwh,
2073 lv_sit_subjnwh,
2074 lv_sit_pre_tax_redns,
2075 lv_hours_worked,
2076 lv_weeks_worked,
2077 'CSV'));
2078
2079 END;
2080 end if; /* NJ */
2081
2082 if (lv_state_abbrev = 'SD' and lv_report_category ='PD') then
2083
2084 BEGIN
2085
2086 lv_char_value := to_number(lv_sui_qtr_wages);
2087 lv_char_value := to_number(lv_sui_excess);
2088 lv_char_value := to_number(lv_sui_er_taxable);
2089 lv_char_value := to_number(lv_sit_qtr_wages);
2090 lv_char_value := to_number(lv_sit_gross);
2091
2092 if (lv_sui_qtr_wages < 0 or
2093 lv_sui_excess < 0 or
2094 lv_sui_er_taxable < 0 or
2095 lv_sit_qtr_wages < 0 or
2096 lv_sit_gross < 0 ) then
2097
2098 hr_utility.trace('Entered negative reporting loop');
2099
2100 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2101
2102 if lv_neg_asg_count = 1 then
2103
2104 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
2105
2106 end if;
2107
2108
2109 fnd_file.put_line(fnd_file.output,formated_static_data (
2110 lv_tax_unit_name,
2111 lv_organization_name,
2112 p_qtrname,
2113 lv_state_abbrev,
2114 lv_assignment_number,
2115 lv_full_name,
2116 lv_sui_qtr_wages,
2117 lv_sui_er_taxable,
2118 lv_sui_excess,
2119 lv_sui_er_subjwh,
2120 lv_sui_er_pre_tax_redns,
2121 lv_sit_qtr_wages,
2122 lv_sit_withheld,
2123 lv_sit_gross,
2124 lv_sit_subjwh,
2125 lv_sit_subjnwh,
2126 lv_sit_pre_tax_redns,
2127 lv_hours_worked,
2128 lv_weeks_worked,
2129 'CSV'));
2130
2131
2132 end if; /* if negative then */
2133
2134 EXCEPTION
2135
2136 when value_error then
2137
2138 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2139
2140 if lv_neg_asg_count = 1 then
2141
2142 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
2143
2144 end if;
2145
2146
2147 fnd_file.put_line(fnd_file.output,formated_static_data (
2148 lv_tax_unit_name,
2149 lv_organization_name,
2150 p_qtrname,
2151 lv_state_abbrev,
2152 lv_assignment_number,
2153 lv_full_name,
2154 lv_sui_qtr_wages,
2155 lv_sui_er_taxable,
2156 lv_sui_excess,
2157 lv_sui_er_subjwh,
2158 lv_sui_er_pre_tax_redns,
2159 lv_sit_qtr_wages,
2160 lv_sit_withheld,
2161 lv_sit_gross,
2162 lv_sit_subjwh,
2163 lv_sit_subjnwh,
2164 lv_sit_pre_tax_redns,
2165 lv_hours_worked,
2166 lv_weeks_worked,
2167 'CSV'));
2168
2169 END;
2170 end if; /* South Dakota Doskette */
2171
2172
2173 if (lv_state_abbrev = 'WA') then
2174
2175 BEGIN
2176
2177 lv_char_value := to_number(lv_sui_qtr_wages);
2178 lv_char_value := to_number(lv_sui_excess);
2179 lv_char_value := to_number(lv_sui_er_taxable);
2180 lv_char_value := to_number(lv_sit_qtr_wages);
2181 lv_char_value := to_number(lv_sit_withheld);
2182 lv_char_value := to_number(lv_hours_worked);
2183
2184
2185 if (lv_sui_qtr_wages < 0 or
2186 lv_sui_excess < 0 or
2187 lv_sui_er_taxable < 0 or
2188 lv_sit_qtr_wages < 0 or
2189 lv_sit_withheld < 0 or
2190 lv_hours_worked < 0 ) then
2191
2192 hr_utility.trace('Entered negative reporting loop');
2193
2194 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2195
2196 if lv_neg_asg_count = 1 then
2197
2198 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
2199
2200 hr_utility.trace('Inserted header in pay_us_rpt_totals');
2201
2202 end if;
2203
2204
2205 fnd_file.put_line(fnd_file.output,formated_static_data (
2206 lv_tax_unit_name,
2207 lv_organization_name,
2208 p_qtrname,
2209 lv_state_abbrev,
2210 lv_assignment_number,
2211 lv_full_name,
2212 lv_sui_qtr_wages,
2213 lv_sui_er_taxable,
2214 lv_sui_excess,
2215 lv_sui_er_subjwh,
2216 lv_sui_er_pre_tax_redns,
2217 lv_sit_qtr_wages,
2218 lv_sit_withheld,
2219 lv_sit_gross,
2220 lv_sit_subjwh,
2221 lv_sit_subjnwh,
2222 lv_sit_pre_tax_redns,
2223 lv_hours_worked,
2224 lv_weeks_worked,
2225 'CSV'));
2226
2227
2228 end if; /* if negative then */
2229
2230 EXCEPTION
2231
2232 when value_error then
2233
2234 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2235
2236 if lv_neg_asg_count = 1 then
2237
2238 fnd_file.put_line(fnd_file.output,formated_Static_header('CSV'));
2239
2240 hr_utility.trace('Inserted header in pay_us_rpt_totals');
2241
2242 end if;
2243
2244
2245 fnd_file.put_line(fnd_file.output,formated_static_data (
2246 lv_tax_unit_name,
2247 lv_organization_name,
2248 p_qtrname,
2249 lv_state_abbrev,
2250 lv_assignment_number,
2251 lv_full_name,
2252 lv_sui_qtr_wages,
2253 lv_sui_er_taxable,
2254 lv_sui_excess,
2255 lv_sui_er_subjwh,
2256 lv_sui_er_pre_tax_redns,
2257 lv_sit_qtr_wages,
2258 lv_sit_withheld,
2259 lv_sit_gross,
2260 lv_sit_subjwh,
2261 lv_sit_subjnwh,
2262 lv_sit_pre_tax_redns,
2263 lv_hours_worked,
2264 lv_weeks_worked,
2265 'CSV'));
2266
2267 END;
2268
2269 end if; /* WA */
2270
2271 if (lv_state_abbrev = 'CA') then
2272
2273 BEGIN
2274
2275 lv_char_value := to_number(lv_sui_qtr_wages);
2276 lv_char_value := to_number(lv_sui_excess);
2277 lv_char_value := to_number(lv_sui_er_taxable);
2278 lv_char_value := to_number(lv_sit_qtr_wages);
2279 lv_char_value := to_number(lv_sit_gross);
2280 lv_char_value := to_number(lv_sit_withheld);
2281 lv_char_value := to_number(lv_hours_worked);
2282 lv_char_value := to_number(lv_sdi_qtr_wages);
2283 lv_char_value := to_number(lv_sdi_taxable);
2284 lv_char_value := to_number(lv_sdi_excess);
2285
2286 if (lv_sui_qtr_wages < 0 or
2287 lv_sui_excess < 0 or
2288 lv_sui_er_taxable < 0 or
2289 lv_sit_qtr_wages < 0 or
2290 lv_sit_gross < 0 or
2291 lv_sit_withheld < 0 or
2292 lv_hours_worked < 0 or
2293 lv_sdi_qtr_wages < 0 or
2294 lv_sdi_taxable < 0 or
2295 lv_sdi_excess < 0 ) then
2296
2297 hr_utility.trace('Entered negative reporting loop');
2298
2299 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2300
2301 if lv_neg_asg_count = 1 then
2302
2303 fnd_file.put_line(fnd_file.output,formated_Static_header_CA('CSV'));
2304
2305 end if;
2306
2307
2308 fnd_file.put_line(fnd_file.output,formated_static_data_CA (
2309 lv_tax_unit_name,
2310 lv_organization_name,
2311 p_qtrname,
2312 lv_state_abbrev,
2313 lv_assignment_number,
2314 lv_full_name,
2315 lv_sui_qtr_wages,
2316 lv_sui_er_taxable,
2317 lv_sui_excess,
2318 lv_sui_er_subjwh,
2319 lv_sui_er_pre_tax_redns,
2320 lv_sit_qtr_wages,
2321 lv_sit_withheld,
2322 lv_sit_gross,
2323 lv_sit_subjwh,
2324 lv_sit_subjnwh,
2325 lv_sit_pre_tax_redns,
2326 lv_sdi_qtr_wages,
2327 lv_sdi_taxable,
2328 lv_sdi_excess,
2329 lv_sdi_subjwh,
2330 lv_sdi_pre_tax_redns,
2331 lv_hours_worked ,
2332 'CSV'));
2333
2334
2335 end if; /* if negative then */
2336
2337 EXCEPTION
2338
2339 when value_error then
2340
2341 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2342
2343 if lv_neg_asg_count = 1 then
2344
2345 fnd_file.put_line(fnd_file.output,formated_Static_header_CA('CSV'));
2346
2347 end if;
2348
2349
2350 fnd_file.put_line(fnd_file.output,formated_static_data_CA (
2351 lv_tax_unit_name,
2352 lv_organization_name,
2353 p_qtrname,
2354 lv_state_abbrev,
2355 lv_assignment_number,
2356 lv_full_name,
2357 lv_sui_qtr_wages,
2358 lv_sui_er_taxable,
2359 lv_sui_excess,
2360 lv_sui_er_subjwh,
2361 lv_sui_er_pre_tax_redns,
2362 lv_sit_qtr_wages,
2363 lv_sit_withheld,
2364 lv_sit_gross,
2365 lv_sit_subjwh,
2366 lv_sit_subjnwh,
2367 lv_sit_pre_tax_redns,
2368 lv_sdi_qtr_wages,
2369 lv_sdi_taxable,
2370 lv_sdi_excess,
2371 lv_sdi_subjwh,
2372 lv_sdi_pre_tax_redns,
2373 lv_hours_worked,
2374 'CSV'));
2375 END;
2376
2377 end if; /* CA */
2378
2379 if (lv_state_abbrev = 'NY') then
2380
2381 BEGIN
2382
2383 lv_annual_gross_wages := lv_regular_earnings_pgy +
2384 lv_supp_fit_stt_pgy +
2385 lv_supp_nwfit_stt_pgy +
2386 lv_fit_non_w2_pre_tax_pgy +
2387 lv_pre_tax_dedns_fit_stt_pgy -
2388 lv_pre_tax_dedns_pgy;
2389
2390
2391 lv_char_value := to_number(lv_sui_qtr_wages);
2392 lv_char_value := to_number(lv_sui_excess);
2393 lv_char_value := to_number(lv_sui_er_taxable);
2394 lv_char_value := to_number(lv_sit_qtr_wages);
2395 lv_char_value := to_number(lv_sit_withheld);
2396 lv_char_value := to_number(lv_sit_withheld_pjgy);
2397 lv_char_value := to_number(lv_annual_gross_wages);
2398
2399
2400 hr_utility.trace('lv_annual_gross_wages as cal = '||lv_annual_gross_wages);
2401
2402 if (lv_sui_qtr_wages < 0 or
2403 lv_sui_excess < 0 or
2404 lv_sui_er_taxable < 0 or
2405 lv_sit_qtr_wages < 0 or
2406 lv_sit_withheld < 0 or
2407 lv_sit_withheld_pjgy < 0 or
2408 lv_annual_gross_wages < 0 ) then
2409
2410 hr_utility.trace('Entered negative reporting loop');
2411
2412
2413 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2414
2415 if lv_neg_asg_count = 1 then
2416
2417
2418 fnd_file.put_line(fnd_file.output,formated_Static_header_NY('CSV'));
2419
2420
2421 end if;
2422
2423
2424 fnd_file.put_line(fnd_file.output,formated_static_data_NY (
2425 lv_tax_unit_name,
2426 lv_organization_name,
2427 p_qtrname,
2428 lv_state_abbrev,
2429 lv_assignment_number,
2430 lv_full_name,
2431 lv_sui_qtr_wages,
2432 lv_sui_er_taxable,
2433 lv_sui_excess,
2434 lv_sui_er_subjwh,
2435 lv_sui_er_pre_tax_redns,
2436 lv_sit_qtr_wages,
2437 lv_sit_withheld,
2438 lv_sit_gross,
2439 lv_sit_subjwh,
2440 lv_sit_subjnwh,
2441 lv_sit_pre_tax_redns,
2442 lv_annual_gross_wages,
2443 lv_regular_earnings_pgy,
2444 lv_supp_fit_stt_pgy,
2445 lv_supp_nwfit_stt_pgy,
2446 lv_fit_non_w2_pre_tax_pgy,
2447 lv_pre_tax_dedns_fit_stt_pgy,
2448 lv_pre_tax_dedns_pgy,
2449 'CSV'));
2450
2451
2452
2453 end if; /* if negative then */
2454 EXCEPTION
2455
2456 when value_error then
2457
2458 lv_neg_asg_count :=lv_neg_asg_count + 1 ;
2459
2460 if lv_neg_asg_count = 1 then
2461
2462
2463 fnd_file.put_line(fnd_file.output,formated_Static_header_NY('CSV'));
2464
2465
2466 end if;
2467
2468
2469 fnd_file.put_line(fnd_file.output,formated_static_data_NY (
2470 lv_tax_unit_name,
2471 lv_organization_name,
2472 p_qtrname,
2473 lv_state_abbrev,
2474 lv_assignment_number,
2475 lv_full_name,
2476 lv_sui_qtr_wages,
2477 lv_sui_er_taxable,
2478 lv_sui_excess,
2479 lv_sui_er_subjwh,
2480 lv_sui_er_pre_tax_redns,
2481 lv_sit_qtr_wages,
2482 lv_sit_withheld,
2483 lv_sit_gross,
2484 lv_sit_subjwh,
2485 lv_sit_subjnwh,
2486 lv_sit_pre_tax_redns,
2487 lv_annual_gross_wages,
2488 lv_regular_earnings_pgy,
2489 lv_supp_fit_stt_pgy,
2490 lv_supp_nwfit_stt_pgy,
2491 lv_fit_non_w2_pre_tax_pgy,
2492 lv_pre_tax_dedns_fit_stt_pgy,
2493 lv_pre_tax_dedns_pgy,
2494 'CSV'));
2495
2496 END;
2497
2498 end if; /* NY */
2499 end loop;
2500
2501 close c_assignments;
2502
2503 end;
2504 end pay_us_sqwl_error;