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