[Home] [Help]
PACKAGE BODY: APPS.PAY_ARCHIVER_REPORT_PKG
Source
1 PACKAGE BODY pay_archiver_report_pkg AS
2 /* $Header: pyempdtl.pkb 120.1 2005/08/23 21:42:31 sackumar noship $ */
3 --
4 /*
5 +========================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | All rights reserved. |
8 +========================================================================+
9
10 Name : pay_archive_report_pkg
11
12 Description : Package for Employee Periodic Detail Report which drives data
13 from External Process Archive. Output file is in the format
14 specificed by the user.
15 The current formats supported are
16 - HTML
17 - CSV
18
19 Change List
20 -----------
21 Date Name Vers Bug No Description
22 ---- ---- ------ ------- -----------
23 24-JAN-2002 ekim 115.0 Created.
24 17-MAR-2002 ekim 115.1 Made changes for Summary.
25 19-MAR-2002 ekim 115.2 Added tax_unit_id check for summary.
26 19-MAR-2002 ekim 115.3 Uncommented exit
27 24-MAR-2002 ahanda 115.4 Fixed GSCC warnings.
28 29-Mar-2002 ekim 115.5 1)Added emp_sum_static_header
29 procedure. emp_sum_static_data
30 2)Changed emp_static_header to
31 emp_detail_static_header.
32 2294299 3)Added EIC_ADVANCE for federal
33 balance.
34 4)Removed p_is_city_mandatory
35 parameter from archiver_extract
36 procedure.
37 5)Re-ordered balance in the following
38 order:
39 Gross, Subject, Reduced Subject,
40 Taxable, Withheld, Liability
41 (EE and then ER)
42 2294432 6)Changed the cursor to supress all
43 zero balances.
44 02-Apr-2002 ekim 115.6 2294135 Formatted data returned to
45 999999990.00.
46 04-Apr-2002 ekim 115.7 Added null value check.
47 Commented c_employee_count.
48 Added sum by jurisdiction_code in case
49 of multiple assignments, only one row
50 will be displayed summing up the
51 totals for the person for the
52 jurisdiction.
53 04-Apr-2002 ekim 115.8 Added nvl for all balances retrieved.
54 08-Apr-2002 ekim 115.9 Added jurisdiction check on top of 0
55 and null check. Added nvl to convert
56 null values to 0.
57 10-Apr-2002 ekim 115.10 Removed lv_school_id.
58 15-Apr-2002 ekim 115.11 Removed p_is_county_mandatory, changed
59 c_city_balance to accomodate null
60 county_id.
61 15-Apr-2002 ekim 115.12 Changed all cursors to check for the
62 jurisdiction code for 115.11 change.
63 13-Jun-2003 ekim 115.13 2974109 Removed setting of ln_prev_person
64 to ln_person_id that is done before
65 the end of employee fetch loop
66 13-Jun-2003 ekim 115.14 Made GSCC warning change.
67 30-Oct-2003 115.15 3217369 Modified the c_state_balances cursor
68 to use sum(nvl(field,0)) and removed
69 subquerychecking for null or zero.
70 07-Nov-2003 115.16 3217369 removed sub-queries from
71 c_federal_balances, c_county_balances,
72 c_city_balances, c_school_balances
73 cursors
74 23-Jan-2004 schauhan 115.17 3369315 Changed the query for the cursors
75 c_state_balances,c_school_balances,
76 c_city_balances and
77 forced the index pay_action_information_n2
78 to avoid FTS on pay_action_information.
79 23-Jan-2004 schauhan 115.18 3395312 Changed the type for the local variable
80 lv_address_line to the same type as column
81 address_line of pay_us_employee_action_info_v
82 The cursor c_employee populates this variable
83 12-APR-2005 ahanda 115.19 4294918 Changed local variable length to make sure
84 it matches the db coulmn length.
85 23-aug-2005 sackumar 115.20 4559897 Introduce the missing "if lv_found = 'N' then"
86 condition for federal balance to show the multiple
87 GREs in summary report.
88 */
89
90 /***********************************************************
91 ** Local Package Variables
92 ************************************************************/
93 gv_title VARCHAR2(100) := ' Employee Periodic Details Report ';
94 gc_csv_delimiter VARCHAR2(1) := ',';
95 gc_csv_data_delimiter VARCHAR2(1) := '"';
96
97 gv_html_start_data VARCHAR2(5) := '<td>' ;
98 gv_html_end_data VARCHAR2(5) := '</td>' ;
99
100 gv_package_name VARCHAR2(50) := 'pay_archive_report_pkg';
101
102 /******************************************************************
103 ** Function Returns the formated input string based on the
104 ** Output format. If the format is CSV then the values are returned
105 ** seperated by comma (,). If the format is HTML then the returned
106 ** string as the HTML tags. The parameter p_bold only works for
107 ** the HTML format.
108 ******************************************************************/
109 FUNCTION data_string
110 (p_input_string in varchar2
111 ,p_format in varchar2 default 'N'
112 ,p_output_file_type in varchar2
113 ,p_bold in varchar2 default 'N'
114 )
115 RETURN VARCHAR2
116 IS
117
118 lv_format varchar2(5000);
119 p_display_string varchar2(100);
120
121 BEGIN
122
123 if p_format = 'Y' then
124 p_display_string := ltrim(to_char(to_number(p_input_string),'999999990.00'));
125 else
126 p_display_string := p_input_string;
127 end if;
128
129 if p_output_file_type = 'CSV' then
130 lv_format := gc_csv_data_delimiter || p_display_string ||
131 gc_csv_data_delimiter || gc_csv_delimiter;
132 elsif p_output_file_type = 'HTML' then
133 if p_display_string is null then
134 lv_format := gv_html_start_data || ' ' || gv_html_end_data;
135 else
136 if p_bold = 'Y' then
137 lv_format := gv_html_start_data || '<b> ' || p_display_string
138 || '</b>' || gv_html_end_data;
139 else
140 lv_format := gv_html_start_data || p_display_string || gv_html_end_data;
141 end if;
142 end if;
143 end if;
144 return lv_format;
145 END data_string;
146
147
148 /************************************************************
149 ** Function returns the string with the HTML Header tags
150 ************************************************************/
151 FUNCTION title_string
152 (p_input_string in varchar2
153 ,p_output_file_type in varchar2
154 )
155 RETURN VARCHAR2
156 IS
157
158 lv_format varchar2(1000);
159
160 BEGIN
161 if p_output_file_type = 'CSV' then
162 lv_format := p_input_string;
163 elsif p_output_file_type = 'HTML' then
164 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
165 '</B></H1></CENTER></HEAD>';
166 end if;
167
168 return lv_format;
169
170 END title_string;
171
172 FUNCTION f_state_ein
173 (pv_tax_unit_id in number
174 ,pv_state_id in varchar2)
175 RETURN VARCHAR2
176 IS
177
178 l_state_ein hr_organization_information.org_information3%type;
179
180 BEGIN
181
182 SELECT hoi.org_information3
183 INTO l_state_ein
184 FROM hr_organization_information hoi,
185 pay_us_states pus
186 WHERE pus.state_code = pv_state_id
187 and hoi.organization_id = pv_tax_unit_id
188 and hoi.org_information_context = 'State Tax Rules'
189 and hoi.org_information1 = pus.state_abbrev;
190
191 return l_state_ein;
192
193 exception
194 when no_data_found then
195 return l_state_ein;
196
197 when others then
198 raise;
199
200 END f_state_ein;
201
202
203 PROCEDURE emp_detail_static_header(
204 p_output_file_type in varchar2
205 ,p_emp_static_label out nocopy varchar2
206 )
207 IS
208 l_emp_detail_format varchar2(32000);
209 BEGIN
210
211 l_emp_detail_format :=
212 data_string (p_input_string => 'Last Name'
213 ,p_bold => 'Y'
214 ,p_output_file_type => p_output_file_type) ||
215 data_string (p_input_string => 'First Name'
216 ,p_bold => 'Y'
217 ,p_output_file_type => p_output_file_type) ||
218 data_string (p_input_string => 'Middle Name'
219 ,p_bold => 'Y'
220 ,p_output_file_type => p_output_file_type) ||
221 data_string (p_input_string => 'Employee Number'
222 ,p_bold => 'Y'
223 ,p_output_file_type => p_output_file_type) ||
224 data_string (p_input_string => 'Assignment Number'
225 ,p_bold => 'Y'
226 ,p_output_file_type => p_output_file_type) ||
227 data_string (p_input_string => 'SSN'
228 ,p_bold => 'Y'
229 ,p_output_file_type => p_output_file_type) ||
230 data_string (p_input_string => 'Address'
231 ,p_bold => 'Y'
232 ,p_output_file_type => p_output_file_type) ||
233 data_string (p_input_string => 'City'
234 ,p_bold => 'Y'
235 ,p_output_file_type => p_output_file_type) ||
236 data_string (p_input_string => 'County'
237 ,p_bold => 'Y'
238 ,p_output_file_type => p_output_file_type) ||
239 data_string (p_input_string => 'State'
240 ,p_bold => 'Y'
241 ,p_output_file_type => p_output_file_type) ||
242 data_string (p_input_string => 'Zip'
243 ,p_bold => 'Y'
244 ,p_output_file_type => p_output_file_type) ||
245 data_string (p_input_string => 'Country'
246 ,p_bold => 'Y'
247 ,p_output_file_type => p_output_file_type) ||
248 data_string (p_input_string => 'Fed EIN'
249 ,p_bold => 'Y'
250 ,p_output_file_type => p_output_file_type) ||
251 data_string (p_input_string => 'Organization'
252 ,p_bold => 'Y'
253 ,p_output_file_type => p_output_file_type) ||
254 data_string (p_input_string => 'Location'
255 ,p_bold => 'Y'
256 ,p_output_file_type => p_output_file_type) ||
257 data_string (p_input_string => 'Action Type'
258 ,p_bold => 'Y'
259 ,p_output_file_type => p_output_file_type) ||
260 data_string (p_input_string => 'Effective Date'
261 ,p_bold => 'Y'
262 ,p_output_file_type => p_output_file_type)
263 ;
264
265 p_emp_static_label := l_emp_detail_format;
266 hr_utility.set_location(gv_package_name||'.emp_detail_static_header',20);
267 END;
268
269 PROCEDURE emp_sum_static_header(
270 p_output_file_type in varchar2
271 ,p_emp_static_label out nocopy varchar2
272 )
273 IS
274 l_emp_sum_format varchar2(32000);
275 BEGIN
276
277 l_emp_sum_format :=
278 data_string (p_input_string => 'Last Name'
279 ,p_bold => 'Y'
280 ,p_output_file_type => p_output_file_type) ||
281 data_string (p_input_string => 'First Name'
282 ,p_bold => 'Y'
283 ,p_output_file_type => p_output_file_type) ||
284 data_string (p_input_string => 'Middle Name'
285 ,p_bold => 'Y'
286 ,p_output_file_type => p_output_file_type) ||
287 data_string (p_input_string => 'Employee Number'
288 ,p_bold => 'Y'
289 ,p_output_file_type => p_output_file_type) ||
290 data_string (p_input_string => 'SSN'
291 ,p_bold => 'Y'
292 ,p_output_file_type => p_output_file_type) ||
293 data_string (p_input_string => 'Fed EIN'
294 ,p_bold => 'Y'
295 ,p_output_file_type => p_output_file_type)
296 ;
297
298 p_emp_static_label := l_emp_sum_format;
299 hr_utility.set_location(gv_package_name||'.emp_sum_static_header',20);
300 END;
301
302 PROCEDURE fed_static_header(
303 p_output_file_type in varchar2
304 ,p_fed_static_label out nocopy varchar2
305 )
306 IS
307 l_fed_format varchar2(32000);
308 BEGIN
309
310 -- hr_utility.set_location(gv_package_name || '.fed_static_header', 10);
311 l_fed_format :=
312 data_string (p_input_string => 'GRE Name'
313 ,p_bold => 'Y'
314 ,p_output_file_type => p_output_file_type) ||
315 data_string (p_input_string => 'FIT Gross'
316 ,p_bold => 'Y'
317 ,p_output_file_type => p_output_file_type) ||
318 data_string (p_input_string => 'FIT Wages'
319 ,p_bold => 'Y'
320 ,p_output_file_type => p_output_file_type) ||
321 data_string (p_input_string => 'FIT Withheld'
322 ,p_bold => 'Y'
323 ,p_output_file_type => p_output_file_type) ||
324 data_string (p_input_string => 'FUTA Taxable'
325 ,p_bold => 'Y'
326 ,p_output_file_type => p_output_file_type) ||
327 data_string (p_input_string => 'FUTA Liability'
328 ,p_bold => 'Y'
329 ,p_output_file_type => p_output_file_type) ||
330 data_string (p_input_string => 'SS EE Taxable'
331 ,p_bold => 'Y'
332 ,p_output_file_type => p_output_file_type) ||
333 data_string (p_input_string => 'SS EE Withheld'
334 ,p_bold => 'Y'
335 ,p_output_file_type => p_output_file_type) ||
336 data_string (p_input_string => 'SS ER Taxable'
337 ,p_bold => 'Y'
338 ,p_output_file_type => p_output_file_type) ||
339 data_string (p_input_string => 'SS ER Liability'
340 ,p_bold => 'Y'
341 ,p_output_file_type => p_output_file_type) ||
342 data_string (p_input_string => 'Med EE Taxable'
343 ,p_bold => 'Y'
344 ,p_output_file_type => p_output_file_type) ||
345 data_string (p_input_string => 'Med EE Withheld'
346 ,p_bold => 'Y'
347 ,p_output_file_type => p_output_file_type) ||
348 data_string (p_input_string => 'Med ER Taxable'
349 ,p_bold => 'Y'
350 ,p_output_file_type => p_output_file_type) ||
351 data_string (p_input_string => 'Med ER Liability'
352 ,p_bold => 'Y'
353 ,p_output_file_type => p_output_file_type) ||
354 data_string (p_input_string => 'Advance EIC'
355 ,p_bold => 'Y'
356 ,p_output_file_type => p_output_file_type)
357 ;
358
359 p_fed_static_label := l_fed_format;
360 hr_utility.set_location(gv_package_name||'.fed_static_header',20);
361
362 END;
363
364 PROCEDURE state_static_header ( p_output_file_type in varchar2
365 ,p_state_static_label out nocopy varchar2)
366 IS
367 l_state_format varchar2(32000);
368 BEGIN
369 -- hr_utility.set_location(gv_package_name||'.state_static_header',10);
370
371 l_state_format :=
372 data_string (p_input_string => 'GRE Name'
373 ,p_bold => 'Y'
374 ,p_output_file_type => p_output_file_type) ||
375 data_string (p_input_string => 'State EIN'
376 ,p_bold => 'Y'
377 ,p_output_file_type => p_output_file_type) ||
378 data_string (p_input_string => 'Jurisdiction'
379 ,p_bold => 'Y'
380 ,p_output_file_type => p_output_file_type) ||
381 data_string (p_input_string => 'State'
382 ,p_bold => 'Y'
383 ,p_output_file_type => p_output_file_type) ||
384 data_string (p_input_string => 'SIT Gross'
385 ,p_bold => 'Y'
386 ,p_output_file_type => p_output_file_type) ||
387 data_string (p_input_string => 'SIT wages'
388 ,p_bold => 'Y'
389 ,p_output_file_type => p_output_file_type) ||
390 data_string (p_input_string => 'SIT Withheld'
391 ,p_bold => 'Y'
392 ,p_output_file_type => p_output_file_type) ||
393 data_string (p_input_string => 'SUI EE Taxable'
394 ,p_bold => 'Y'
395 ,p_output_file_type => p_output_file_type) ||
396 data_string (p_input_string => 'SUI EE Withheld'
397 ,p_bold => 'Y'
398 ,p_output_file_type => p_output_file_type) ||
399 data_string (p_input_string => 'SUI ER Taxable'
400 ,p_bold => 'Y'
401 ,p_output_file_type => p_output_file_type) ||
402 data_string (p_input_string => 'SUI ER Liability'
403 ,p_bold => 'Y'
404 ,p_output_file_type => p_output_file_type) ||
405 data_string (p_input_string => 'SDI EE Taxable'
406 ,p_bold => 'Y'
407 ,p_output_file_type => p_output_file_type) ||
408 data_string (p_input_string => 'SDI EE Withheld'
409 ,p_bold => 'Y'
410 ,p_output_file_type => p_output_file_type) ||
411 data_string (p_input_string => 'SDI ER Taxable'
412 ,p_bold => 'Y'
416 ,p_output_file_type => p_output_file_type) ||
413 ,p_output_file_type => p_output_file_type) ||
414 data_string (p_input_string => 'SDI ER Liability'
415 ,p_bold => 'Y'
417 data_string (p_input_string => 'Workers Comp Withheld'
418 ,p_bold => 'Y'
419 ,p_output_file_type => p_output_file_type) ||
420 data_string (p_input_string => 'Workers Comp2 Withheld'
421 ,p_bold => 'Y'
422 ,p_output_file_type => p_output_file_type)
423 ;
424
425 p_state_static_label := l_state_format;
426 hr_utility.set_location(gv_package_name||'.state_static_header',20);
427
428 END;
429
430 PROCEDURE county_static_header ( p_output_file_type in varchar2
431 ,p_county_static_label out nocopy varchar2)
432 IS
433 l_county_format varchar2(32000);
434 BEGIN
435 -- hr_utility.set_location(gv_package_name||'.county_static_header',10);
436
437 l_county_format :=
438 data_string (p_input_string => 'GRE Name'
439 ,p_bold => 'Y'
440 ,p_output_file_type => p_output_file_type) ||
441 data_string (p_input_string => 'Jurisdiction'
442 ,p_bold => 'Y'
443 ,p_output_file_type => p_output_file_type) ||
444 data_string (p_input_string => 'County Name'
445 ,p_bold => 'Y'
446 ,p_output_file_type => p_output_file_type) ||
447 data_string (p_input_string => 'County Gross'
448 ,p_bold => 'Y'
449 ,p_output_file_type => p_output_file_type) ||
450 data_string (p_input_string => 'County Wage'
451 ,p_bold => 'Y'
452 ,p_output_file_type => p_output_file_type) ||
453 data_string (p_input_string => 'County Withheld'
454 ,p_bold => 'Y'
455 ,p_output_file_type => p_output_file_type) ||
456 data_string (p_input_string => 'County Head Tax Withheld'
457 ,p_bold => 'Y'
458 ,p_output_file_type => p_output_file_type)||
459 data_string (p_input_string => 'Non Resident Flag'
460 ,p_bold => 'Y'
461 ,p_output_file_type => p_output_file_type)
462 ;
463
464 p_county_static_label := l_county_format;
465 --hr_utility.trace('Static County label = '||l_county_format);
466 hr_utility.set_location(gv_package_name||'.county_static_header',20);
467
468 END;
469
470 PROCEDURE city_static_header ( p_output_file_type in varchar2
471 ,p_city_static_label out nocopy varchar2)
472 IS
473 l_city_format varchar2(32000);
474 BEGIN
475 -- hr_utility.set_location(gv_package_name||'.city_static_header',10);
476
477 l_city_format :=
478 data_string (p_input_string => 'GRE Name'
479 ,p_bold => 'Y'
480 ,p_output_file_type => p_output_file_type) ||
481 data_string (p_input_string => 'Jurisdiction'
482 ,p_bold => 'Y'
483 ,p_output_file_type => p_output_file_type) ||
484 data_string (p_input_string => 'City Name'
485 ,p_bold => 'Y'
486 ,p_output_file_type => p_output_file_type) ||
487 data_string (p_input_string => 'City Gross'
488 ,p_bold => 'Y'
489 ,p_output_file_type => p_output_file_type) ||
490 data_string (p_input_string => 'City Wages'
491 ,p_bold => 'Y'
492 ,p_output_file_type => p_output_file_type) ||
493 data_string (p_input_string => 'City Withheld'
494 ,p_bold => 'Y'
495 ,p_output_file_type => p_output_file_type) ||
496 data_string (p_input_string => 'City Head Tax Withheld'
497 ,p_bold => 'Y'
498 ,p_output_file_type => p_output_file_type)||
499 data_string (p_input_string => 'Non Resident flag'
500 ,p_bold => 'Y'
501 ,p_output_file_type => p_output_file_type)
502 ;
503
504 p_city_static_label := l_city_format;
505 --hr_utility.trace('Static City label = '||l_city_format);
506 hr_utility.set_location(gv_package_name||'.city_static_header',20);
507 END;
508
509 PROCEDURE school_static_header ( p_output_file_type in varchar2
510 ,p_school_static_label out nocopy varchar2)
511 IS
512 l_school_format varchar2(32000);
513 BEGIN
517 data_string (p_input_string => 'GRE Name'
514 -- hr_utility.set_location(gv_package_name||'.school_static_header',10);
515
516 l_school_format :=
518 ,p_bold => 'Y'
519 ,p_output_file_type => p_output_file_type) ||
520 data_string (p_input_string => 'Jurisdiction'
521 ,p_bold => 'Y'
522 ,p_output_file_type => p_output_file_type) ||
523 data_string (p_input_string => 'School Dist Name'
524 ,p_bold => 'Y'
525 ,p_output_file_type => p_output_file_type) ||
526 data_string (p_input_string => 'School Gross'
527 ,p_bold => 'Y'
528 ,p_output_file_type => p_output_file_type) ||
529 data_string (p_input_string => 'School Wages'
530 ,p_bold => 'Y'
531 ,p_output_file_type => p_output_file_type) ||
532 data_string (p_input_string => 'School Withheld'
533 ,p_bold => 'Y'
534 ,p_output_file_type => p_output_file_type)
535 ;
536
537 p_school_static_label := l_school_format;
538 --hr_utility.trace('Static school label = '||l_school_format);
539 hr_utility.set_location(gv_package_name||'.school_static_header',20);
540 END;
541
542 /******* Done with creating Static Header *****/
543
544 /* Create format for data */
545
546 PROCEDURE emp_static_data (
547 p_last_name in varchar2
548 ,p_first_name in varchar2
549 ,p_middle_name in varchar2
550 ,p_employee_number in varchar2
551 ,p_assignment_number in varchar2
552 ,p_ssn in varchar2
553 ,p_address in varchar2
554 ,p_city in varchar2
555 ,p_county in varchar2
556 ,p_state in varchar2
557 ,p_zip in varchar2
558 ,p_country in varchar2
559 ,p_fed_ein in varchar2
560 ,p_organization in varchar2
561 ,p_location in varchar2
562 ,p_action_type in varchar2
563 ,p_effective_date in varchar2
564 ,p_output_file_type in varchar2
565 ,p_employee_data out nocopy varchar2
566 )
567 IS
568
569 l_emp_static_data VARCHAR2(32000);
570
571 BEGIN
572
573 hr_utility.set_location(gv_package_name || '.emp_static_data', 10);
574 l_emp_static_data :=
575 data_string (p_input_string => p_last_name
576 ,p_output_file_type => p_output_file_type) ||
577 data_string (p_input_string => p_first_name
578 ,p_output_file_type => p_output_file_type) ||
579 data_string (p_input_string => p_middle_name
580 ,p_output_file_type => p_output_file_type) ||
581 data_string (p_input_string => p_employee_number
582 ,p_output_file_type => p_output_file_type) ||
583 data_string (p_input_string => p_assignment_number
584 ,p_output_file_type => p_output_file_type) ||
585 data_string (p_input_string => p_ssn
586 ,p_output_file_type => p_output_file_type) ||
587 data_string (p_input_string => p_address
588 ,p_output_file_type => p_output_file_type) ||
589 data_string (p_input_string => p_city
590 ,p_output_file_type => p_output_file_type) ||
591 data_string (p_input_string => p_county
592 ,p_output_file_type => p_output_file_type) ||
593 data_string (p_input_string => p_state
594 ,p_output_file_type => p_output_file_type) ||
595 data_string (p_input_string => p_zip
596 ,p_output_file_type => p_output_file_type) ||
597 data_string (p_input_string => p_country
598 ,p_output_file_type => p_output_file_type) ||
599 data_string (p_input_string => p_fed_ein
600 ,p_output_file_type => p_output_file_type) ||
601 data_string (p_input_string => p_organization
602 ,p_output_file_type => p_output_file_type) ||
603 data_string (p_input_string => p_location
604 ,p_output_file_type => p_output_file_type) ||
605 data_string (p_input_string => p_action_type
606 ,p_output_file_type => p_output_file_type) ||
607 data_string (p_input_string => p_effective_date
608 ,p_output_file_type => p_output_file_type)
609 ;
610
611 p_employee_data := l_emp_static_data;
612
613 --hr_utility.trace('Employee Header = '||l_emp_static_data);
614 hr_utility.set_location(gv_package_name || '.emp_static_data', 20);
615 END;
616
620 ,p_middle_name in varchar2
617 PROCEDURE emp_sum_static_data (
618 p_last_name in varchar2
619 ,p_first_name in varchar2
621 ,p_employee_number in varchar2
622 ,p_ssn in varchar2
623 ,p_fed_ein in varchar2
624 ,p_output_file_type in varchar2
625 ,p_employee_data out nocopy varchar2
626 )
627 IS
628
629 l_emp_sum_static_data VARCHAR2(32000);
630
631 BEGIN
632
633 hr_utility.set_location(gv_package_name || '.emp_static_data', 10);
634 l_emp_sum_static_data :=
635 data_string (p_input_string => p_last_name
636 ,p_output_file_type => p_output_file_type) ||
637 data_string (p_input_string => p_first_name
638 ,p_output_file_type => p_output_file_type) ||
639 data_string (p_input_string => p_middle_name
640 ,p_output_file_type => p_output_file_type) ||
641 data_string (p_input_string => p_employee_number
642 ,p_output_file_type => p_output_file_type) ||
643 data_string (p_input_string => p_ssn
644 ,p_output_file_type => p_output_file_type) ||
645 data_string (p_input_string => p_fed_ein
646 ,p_output_file_type => p_output_file_type)
647 ;
648
649 p_employee_data := l_emp_sum_static_data;
650
651 --hr_utility.trace('Employee Header = '||l_emp_static_data);
652 hr_utility.set_location(gv_package_name || '.emp_static_data', 20);
653 END;
654
655 PROCEDURE fed_static_data (
656 p_gre_name in varchar2
657 ,p_fit_gross in number
658 ,p_fit_reduced_subject in number
659 ,p_fit_withheld in number
660 ,p_futa_taxable in number
661 ,p_futa_liability in number
662 ,p_ss_ee_taxable in number
663 ,p_ss_ee_withheld in number
664 ,p_ss_er_taxable in number
665 ,p_ss_er_liability in number
666 ,p_med_ee_taxable in number
667 ,p_med_ee_withheld in number
668 ,p_med_er_taxable in number
669 ,p_med_er_liability in number
670 ,p_eic_advance in number
671 ,p_output_file_type in varchar2
672 ,p_federal_data out nocopy varchar2
673 )
674 IS
675 l_fed_static_data varchar2(32000);
676
677 BEGIN
678 --hr_utility.set_location(gv_package_name || '.fit_static_data', 10);
679 l_fed_static_data :=
680 data_string (p_input_string => p_gre_name
681 ,p_output_file_type => p_output_file_type) ||
682 data_string (p_input_string => p_fit_gross
683 ,p_format => 'Y'
684 ,p_output_file_type => p_output_file_type) ||
685 data_string (p_input_string => p_fit_reduced_subject
686 ,p_format => 'Y'
687 ,p_output_file_type => p_output_file_type) ||
688 data_string (p_input_string => p_fit_withheld
689 ,p_format => 'Y'
690 ,p_output_file_type => p_output_file_type) ||
691 data_string (p_input_string => p_futa_taxable
692 ,p_format => 'Y'
693 ,p_output_file_type => p_output_file_type) ||
694 data_string (p_input_string => p_futa_liability
695 ,p_format => 'Y'
696 ,p_output_file_type => p_output_file_type) ||
697 data_string (p_input_string => p_ss_ee_taxable
698 ,p_format => 'Y'
699 ,p_output_file_type => p_output_file_type) ||
700 data_string (p_input_string => p_ss_ee_withheld
701 ,p_format => 'Y'
702 ,p_output_file_type => p_output_file_type) ||
703 data_string (p_input_string => p_ss_er_taxable
704 ,p_format => 'Y'
705 ,p_output_file_type => p_output_file_type) ||
706 data_string (p_input_string => p_ss_er_liability
707 ,p_format => 'Y'
708 ,p_output_file_type => p_output_file_type) ||
709 data_string (p_input_string => p_med_ee_taxable
710 ,p_format => 'Y'
711 ,p_output_file_type => p_output_file_type) ||
712 data_string (p_input_string => p_med_ee_withheld
713 ,p_format => 'Y'
714 ,p_output_file_type => p_output_file_type) ||
715 data_string (p_input_string => p_med_er_taxable
716 ,p_format => 'Y'
717 ,p_output_file_type => p_output_file_type) ||
718 data_string (p_input_string => p_med_er_liability
719 ,p_format => 'Y'
723 ,p_output_file_type => p_output_file_type)
720 ,p_output_file_type => p_output_file_type) ||
721 data_string (p_input_string => p_eic_advance
722 ,p_format => 'Y'
724 ;
725
726
727 p_federal_data := l_fed_static_data;
728 -- hr_utility.trace('Federal Data = '||p_federal_data);
729 hr_utility.set_location(gv_package_name || '.fit_static_data', 20);
730 END;
731
732 PROCEDURE state_static_data(
733 p_gre_name in varchar2
734 ,p_state_ein in varchar2
735 ,p_jurisdiction in varchar2
736 ,p_state in varchar2
737 ,p_sit_gross in number
738 ,p_sit_wages in number
739 ,p_sit_withheld in number
740 ,p_sui_ee_taxable in number
741 ,p_sui_ee_withheld in number
742 ,p_sui_er_taxable in number
743 ,p_sui_er_liability in number
744 ,p_sdi_ee_taxable in number
745 ,p_sdi_ee_withheld in number
746 ,p_sdi_er_taxable in number
747 ,p_sdi_er_liability in number
748 ,p_workers_comp_wh in number
749 ,p_workers_comp2_wh in number
750 ,p_output_file_type in varchar2
751 ,p_state_data out nocopy varchar2
752 )
753 IS
754 l_state_static_data varchar2(32000);
755
756 BEGIN
757 --hr_utility.set_location(gv_package_name || '.state_static_data', 10);
758 l_state_static_data :=
759 data_string (p_input_string => p_gre_name
760 ,p_output_file_type => p_output_file_type) ||
761 data_string (p_input_string => p_state_ein
762 ,p_output_file_type => p_output_file_type) ||
763 data_string (p_input_string => p_jurisdiction
764 ,p_output_file_type => p_output_file_type) ||
765 data_string (p_input_string => p_state
766 ,p_output_file_type => p_output_file_type) ||
767 data_string (p_input_string => p_sit_gross
768 ,p_format => 'Y'
769 ,p_output_file_type => p_output_file_type) ||
770 data_string (p_input_string => p_sit_wages
771 ,p_format => 'Y'
772 ,p_output_file_type => p_output_file_type) ||
773 data_string (p_input_string => p_sit_withheld
774 ,p_format => 'Y'
775 ,p_output_file_type => p_output_file_type) ||
776 data_string (p_input_string => p_sui_ee_taxable
777 ,p_format => 'Y'
778 ,p_output_file_type => p_output_file_type) ||
779 data_string (p_input_string => p_sui_ee_withheld
780 ,p_format => 'Y'
781 ,p_output_file_type => p_output_file_type) ||
782 data_string (p_input_string => p_sui_er_taxable
783 ,p_format => 'Y'
784 ,p_output_file_type => p_output_file_type) ||
785 data_string (p_input_string => p_sui_er_liability
786 ,p_format => 'Y'
787 ,p_output_file_type => p_output_file_type) ||
788 data_string (p_input_string => p_sdi_ee_taxable
789 ,p_format => 'Y'
790 ,p_output_file_type => p_output_file_type) ||
791 data_string (p_input_string => p_sdi_ee_withheld
792 ,p_format => 'Y'
793 ,p_output_file_type => p_output_file_type) ||
794 data_string (p_input_string => p_sdi_er_taxable
795 ,p_format => 'Y'
796 ,p_output_file_type => p_output_file_type) ||
797 data_string (p_input_string => p_sdi_er_liability
798 ,p_format => 'Y'
799 ,p_output_file_type => p_output_file_type) ||
800 data_string (p_input_string => p_workers_comp_wh
801 ,p_format => 'Y'
802 ,p_output_file_type => p_output_file_type) ||
803 data_string (p_input_string => p_workers_comp2_wh
804 ,p_format => 'Y'
805 ,p_output_file_type => p_output_file_type)
806 ;
807
808 p_state_data := l_state_static_data;
809
810 -- hr_utility.trace('State data = '||p_state_data);
811 hr_utility.set_location(gv_package_name || '.state__static_data', 20);
812 END;
813
814
815 PROCEDURE county_static_data(
816 p_gre_name in varchar2
817 ,p_jurisdiction in varchar2
818 ,p_county_name in varchar2
819 ,p_county_gross in number
820 ,p_county_wage in number
821 ,p_county_withheld in number
822 ,p_co_head_tax_wh in number
823 ,p_non_resident_flag in varchar2
824 ,p_output_file_type in varchar2
825 ,p_county_data out nocopy varchar2
826 )
827 IS
831 -- hr_utility.set_location(gv_package_name || '.county_static_data', 10);
828 l_county_static_data varchar2(32000);
829
830 BEGIN
832 l_county_static_data :=
833 data_string (p_input_string => p_gre_name
834 ,p_output_file_type => p_output_file_type) ||
835 data_string (p_input_string => p_jurisdiction
836 ,p_output_file_type => p_output_file_type) ||
837 data_string (p_input_string => p_county_name
838 ,p_output_file_type => p_output_file_type) ||
839 data_string (p_input_string => p_county_gross
840 ,p_format => 'Y'
841 ,p_output_file_type => p_output_file_type) ||
842 data_string (p_input_string => p_county_wage
843 ,p_format => 'Y'
844 ,p_output_file_type => p_output_file_type) ||
845 data_string (p_input_string => p_county_withheld
846 ,p_format => 'Y'
847 ,p_output_file_type => p_output_file_type) ||
848 data_string (p_input_string => p_co_head_tax_wh
849 ,p_format => 'Y'
850 ,p_output_file_type => p_output_file_type) ||
851 data_string (p_input_string => p_non_resident_flag
852 ,p_output_file_type => p_output_file_type)
853 ;
854 p_county_data := l_county_static_data;
855 -- hr_utility.trace('County data = '||p_county_data);
856 hr_utility.set_location(gv_package_name || '.county_static_data', 20);
857 END;
858
859
860 PROCEDURE city_static_data(
861 p_gre_name in varchar2
862 ,p_jurisdiction in varchar2
863 ,p_city_name in varchar2
864 ,p_city_gross in number
865 ,p_city_wage in number
866 ,p_city_withheld in number
867 ,p_cty_head_tax_wh in number
868 ,p_non_resident_flag in varchar2
869 ,p_output_file_type in varchar2
870 ,p_city_data out nocopy varchar2
871 )
872 IS
873 l_city_static_data varchar2(32000);
874 BEGIN
875 --hr_utility.set_location(gv_package_name || '.city_static_data', 10);
876 l_city_static_data :=
877 data_string (p_input_string => p_gre_name
878 ,p_output_file_type => p_output_file_type) ||
879 data_string (p_input_string => p_jurisdiction
880 ,p_output_file_type => p_output_file_type) ||
881 data_string (p_input_string => p_city_name
882 ,p_output_file_type => p_output_file_type) ||
883 data_string (p_input_string => p_city_gross
884 ,p_format => 'Y'
885 ,p_output_file_type => p_output_file_type) ||
886 data_string (p_input_string => p_city_wage
887 ,p_format => 'Y'
888 ,p_output_file_type => p_output_file_type) ||
889 data_string (p_input_string => p_city_withheld
890 ,p_format => 'Y'
891 ,p_output_file_type => p_output_file_type) ||
892 data_string (p_input_string => p_cty_head_tax_wh
893 ,p_format => 'Y'
894 ,p_output_file_type => p_output_file_type) ||
895 data_string (p_input_string => p_non_resident_flag
896 ,p_output_file_type => p_output_file_type)
897 ;
898
899 p_city_data := l_city_static_data;
900 -- hr_utility.trace('City data = '||p_city_data);
901 hr_utility.set_location(gv_package_name || '.city_static_data', 20);
902 END;
903
904 PROCEDURE school_static_data(
905 p_gre_name in varchar2
906 ,p_jurisdiction in varchar2
907 ,p_school_dist_name in varchar2
908 ,p_school_gross in number
909 ,p_school_reduced_subject in number
910 ,p_school_withheld in number
911 ,p_output_file_type in varchar2
912 ,p_school_data out nocopy varchar2
913 )
914 IS
915 l_school_static_data varchar2(32000);
916
917 BEGIN
918 -- hr_utility.set_location(gv_package_name || '.school_static_data', 10);
919 l_school_static_data :=
920 data_string (p_input_string => p_gre_name
921 ,p_output_file_type => p_output_file_type) ||
922 data_string (p_input_string => p_jurisdiction
923 ,p_output_file_type => p_output_file_type) ||
924 data_string (p_input_string => p_school_dist_name
925 ,p_output_file_type => p_output_file_type) ||
926 data_string (p_input_string => p_school_gross
927 ,p_format => 'Y'
928 ,p_output_file_type => p_output_file_type) ||
929 data_string (p_input_string => p_school_reduced_subject
930 ,p_format => 'Y'
931 ,p_output_file_type => p_output_file_type) ||
932 data_string (p_input_string => p_school_withheld
933 ,p_format => 'Y'
937 p_school_data := l_school_static_data;
934 ,p_output_file_type => p_output_file_type)
935 ;
936
938 -- hr_utility.trace('School data = '||p_school_data);
939 hr_utility.set_location(gv_package_name || '.school_static_data', 20);
940 END;
941
942
943 /*****************************************************************
944 ** This is the main procedure which is called from the Concurrent
945 ** Request. All the paramaters are passed based on which it will
946 ** either print a CSV format or an HTML format file.
947 *****************************************************************/
948
949 PROCEDURE archiver_extract
950 (errbuf out nocopy varchar2
951 ,retcode out nocopy number
952 ,p_business_group_id in number
953 ,p_beginning_date in varchar2
954 ,p_end_date in varchar2
955 ,p_jurisdiction_level in varchar2
956 ,p_detail_level in varchar2
957 ,p_is_byRun in varchar2
958 ,p_organization_id in number
959 ,p_location_id in number
960 ,p_is_summary in varchar2
961 ,p_is_state in varchar2
962 ,p_state_id in varchar2
963 ,p_is_county in varchar2
964 ,p_is_state_mandatory in varchar2
965 ,p_county_id in varchar2
966 ,p_is_city in varchar2
967 ,p_city_id in varchar2
968 ,p_is_school in varchar2
969 ,p_school_id in varchar2
970 ,p_payroll_id in number
971 ,p_consolidation_set_id in number
972 ,p_tax_unit_id in varchar2
973 ,p_assignment_set_id in number
974 ,p_output_file_type in varchar2
975 )
976 IS
977
978 /************************************************************
979 ** Cursor to get all the employee and assignment data.
980 ** This cursor will return one row for each tax type
981 ** for the Selection parameter entered by the user in the SRS.
982 ** the action_context_id returned by this cursor is used to
983 ** retreive the jurisdiction specific level tax information.
984 ************************************************************/
985
986 cursor c_employee (
987 cp_beginning_date in date
988 ,cp_end_date in date
989 ,cp_payroll_id in number
990 ,cp_consolidation_set_id in number
991 ,cp_organization_id in number
992 ,cp_tax_unit_id in number
993 ,cp_location_id in number
994 ,cp_business_group_id in number
995 ) is
996 SELECT action_number, last_name, first_name, middle_names,
997 employee_number,
998 assignment_number,
999 assignment_id,
1000 national_identifier,
1001 address_line, town_or_city, county, state,
1002 postal_code,country,
1003 tax_unit_id, gre_name, fed_ein, org_name, location_code,
1004 action_type, person_id, effective_date
1005 FROM pay_us_employee_action_info_v peav
1006 WHERE peav.effective_date between cp_beginning_date and cp_end_date
1007 and nvl(cp_business_group_id,peav.business_group_id)
1008 = peav.business_group_id
1009 and nvl(cp_location_id,peav.location_id) = peav.location_id
1010 and nvl(cp_organization_id, peav.organization_id)
1011 = peav.organization_id
1012 and nvl(cp_payroll_id, peav.payroll_id) = peav.payroll_id
1013 and nvl(cp_tax_unit_id, peav.tax_unit_id) = peav.tax_unit_id
1014 and nvl(cp_consolidation_set_id, peav.consolidation_set_id)
1015 = peav.consolidation_set_id
1016 order by person_id, effective_date asc;
1017
1018 cursor c_employee_count (
1019 cp_beginning_date in date
1020 ,cp_end_date in date
1021 ,cp_payroll_id in number
1022 ,cp_consolidation_set_id in number
1023 ,cp_organization_id in number
1024 ,cp_tax_unit_id in number
1025 ,cp_location_id in number
1026 ,cp_business_group_id in number
1027 ) is
1028 SELECT person_id, last_name, action_number
1029 FROM pay_us_employee_action_info_v peav
1030 WHERE peav.effective_date between cp_beginning_date and cp_end_date
1031 and nvl(cp_business_group_id,peav.business_group_id)
1032 = peav.business_group_id
1033 and nvl(cp_location_id,peav.location_id) = peav.location_id
1034 and nvl(cp_organization_id, peav.organization_id)
1035 = peav.organization_id
1036 and nvl(cp_payroll_id, peav.payroll_id) = peav.payroll_id
1037 and nvl(cp_tax_unit_id, peav.tax_unit_id) = peav.tax_unit_id
1038 and nvl(cp_consolidation_set_id, peav.consolidation_set_id)
1042
1039 = peav.consolidation_set_id;
1040
1041
1043 /****************************************************************
1044 ** This cursor returns Federal Level Balances for the selected **
1045 ** assignments from the archiver. **
1046 ****************************************************************/
1047 CURSOR c_federal_balances(cp_action_number in number) is
1048 select action_number, sum(fit_gross),
1049 sum(fit_reduced_subject),
1050 sum(fit_withheld),
1051 sum(futa_taxable),
1052 sum(futa_liability),
1053 sum(ss_ee_taxable),
1054 sum(ss_ee_withheld),
1055 sum(ss_er_taxable),
1056 sum(ss_er_liability),
1057 sum(medicare_ee_taxable),
1058 sum(medicare_ee_withheld),
1059 sum(medicare_er_taxable),
1060 sum(medicare_er_liability),
1061 sum(eic_advance)
1062 FROM pay_us_federal_action_info_v fed
1063 WHERE fed.action_number = cp_action_number
1064 /* and cp_action_number not in
1065 (select fed2.action_number
1066 from pay_us_federal_action_info_v fed2
1067 where (fed2.fit_gross = 0
1068 or fed2.fit_gross is null)
1069 and (fed2.fit_withheld = 0
1070 or fed2.fit_withheld is null)
1071 and (fed2.fit_reduced_subject = 0
1072 or fed2.fit_reduced_subject is null)
1073 and (fed2.futa_liability = 0
1074 or fed2.futa_liability is null)
1075 and (fed2.futa_taxable = 0
1076 or fed2.futa_taxable is null)
1077 and (fed2.ss_ee_withheld = 0
1078 or fed2.ss_ee_withheld is null)
1079 and (fed2.ss_ee_taxable = 0
1080 or fed2.ss_ee_taxable is null)
1081 and (fed2.ss_er_liability = 0
1082 or fed2.ss_er_liability is null)
1083 and (fed2.ss_er_taxable = 0
1084 or fed2.ss_er_taxable is null)
1085 and (fed2.medicare_ee_withheld = 0
1086 or fed2.medicare_ee_withheld is null)
1087 and (fed2.medicare_ee_taxable = 0
1088 or fed2.medicare_ee_taxable is null)
1089 and (fed2.medicare_er_taxable = 0
1090 or fed2.medicare_er_taxable is null)
1091 and (fed2.medicare_er_liability = 0
1092 or fed2.medicare_er_liability is null)
1093 and (fed2.eic_advance = 0
1094 or fed2.eic_advance is null)) */
1095 GROUP BY action_number;
1096
1097 --Bug3369315 --Changed the cursor query for c_state_balances and forced the index pay_action_information_n2
1098 -- to remove FTS from pay_action_information on HRPPG2.
1099
1100 CURSOR c_state_balances(cp_action_number in number,
1101 cp_state_id in varchar2) IS
1102 select /*+ index(state.pai pay_action_information_n2) */ jurisdiction_code,
1103 jurisdiction_name,
1104 sum(nvl(sit_gross,0)),
1105 sum(nvl(sit_reduced_subject,0)),
1106 sum(nvl(sit_withheld,0)),
1107 sum(nvl(sui_ee_taxable,0)),
1108 sum(nvl(sui_ee_withheld,0)),
1109 sum(nvl(sui_er_taxable,0)),
1110 sum(nvl(sui_er_liability,0)),
1111 sum(nvl(sdi_ee_taxable,0)),
1112 sum(nvl(sdi_ee_withheld,0)),
1113 sum(nvl(sdi_er_taxable,0)),
1114 sum(nvl(sdi_er_liability,0)),
1115 sum(nvl(workers_comp_withheld,0)),
1116 sum(nvl(workers_comp2_withheld,0))
1117 from pay_us_state_action_info_v state
1118 where state.action_number = cp_action_number
1119 and state.jurisdiction_code like nvl(cp_state_id,'%')||'-000-0000'
1120 group by jurisdiction_code, jurisdiction_name;
1121
1122
1123 CURSOR c_county_balances(cp_action_number in number,
1124 cp_state_id in varchar2,
1125 cp_county_id in varchar2) IS
1126 select jurisdiction_code,
1127 jurisdiction_name,
1128 sum(county_gross),
1129 sum(county_reduced_subject),
1130 sum(county_withheld),
1131 sum(head_tax_withheld),
1132 decode(non_resident_flag,'NR','WK','R','RS',non_resident_flag)
1133 from pay_us_county_action_info_v county
1134 where county.action_number = cp_action_number
1135 and county.jurisdiction_code
1136 like cp_state_id||'-'||nvl(cp_county_id,'%')||'-0000'
1137 /* and cp_action_number not in (select county2.action_number
1138 from pay_us_county_action_info_v county2
1139 where (county2.county_gross = 0
1140 or county2.county_gross is null)
1141 and (county2.county_reduced_subject = 0
1142 or county2.county_reduced_subject is null)
1143 and (county2.county_withheld = 0
1147 and county2.jurisdiction_code
1144 or county2.county_withheld is null)
1145 and (county2.head_tax_withheld = 0
1146 or county2.head_tax_withheld is null)
1148 = county.jurisdiction_code) */
1149 GROUP BY jurisdiction_code, jurisdiction_name,
1150 decode(non_resident_flag,'NR','WK','R','RS',non_resident_flag);
1151
1152
1153 --Bug3369315 --Changed the cursor query for c_city_balances and forced the index pay_action_information_n2
1154 -- to remove FTS from pay_action_information on HRPPG2.
1155
1156 CURSOR c_city_balances(cp_action_number in number,
1157 cp_state_id in varchar2,
1158 cp_county_id in varchar2,
1159 cp_city_id in varchar2) IS
1160 select /*+ index(city.pai pay_action_information_n2) */ jurisdiction_code,
1161 jurisdiction_name,
1162 sum(city_gross),
1163 sum(city_reduced_subject),
1164 sum(city_withheld),
1165 sum(head_tax_withheld),
1166 decode(non_resident_flag,'NR','WK','R','RS',non_resident_flag)
1167 from pay_us_city_action_info_v city
1168 where city.action_number = cp_action_number
1169 and city.jurisdiction_code
1170 like cp_state_id||'-'||nvl(cp_county_id,'%')||'-'||nvl(cp_city_id,'%')
1171 /* and cp_action_number not in (select city2.action_number
1172 from pay_us_city_action_info_v city2
1173 where (city2.city_gross = 0
1174 or city2.city_gross is null)
1175 and (city2.city_reduced_subject = 0
1176 or city2.city_reduced_subject is null)
1177 and (city2.city_withheld = 0
1178 or city2.city_withheld is null)
1179 and (city2.head_tax_withheld = 0
1180 or city2.head_tax_withheld is null)
1181 and city2.jurisdiction_code
1182 = city.jurisdiction_code) */
1183 GROUP BY jurisdiction_code, jurisdiction_name,
1184 decode(non_resident_flag,'NR','WK','R','RS',non_resident_flag);
1185
1186 --Bug3369315 ---Changed the cursor query for c_school_balances and forced the index pay_action_information_n2
1187 -- to remove FTS from pay_action_information on HRPPG2.
1188
1189 CURSOR c_school_balances(cp_action_number in number,
1190 cp_state_id in varchar2,
1191 cp_school_id in varchar2) IS
1192 select /*+ index(school.pai pay_action_information_n2) */ jurisdiction_code,
1193 jurisdiction_name,
1194 sum(school_gross),
1195 sum(school_reduced_subject),
1196 sum(School_Withheld)
1197 from pay_us_school_action_info_v school
1198 where school.action_number = cp_action_number
1199 and school.jurisdiction_code like
1200 cp_state_id||'-'||nvl(cp_school_id,'%')
1201 /* and cp_action_number not in (select school2.action_number
1202 from pay_us_school_action_info_v school2
1203 where (school2.school_gross = 0
1204 or school2. school_gross is null)
1205 and (school2.school_reduced_subject = 0
1206 or school2.school_reduced_subject is null)
1207 and (school2.School_Withheld = 0
1208 or school2.School_Withheld is null)
1209 and school2.jurisdiction_code
1210 = school.jurisdiction_code)*/
1211 GROUP BY jurisdiction_code, jurisdiction_name;
1212
1213 /*************************************************************
1214 ** Local Variables
1215 *************************************************************/
1216
1217 ln_person_id NUMBER;
1218 ln_assignment_id NUMBER;
1219 ln_prev_person NUMBER;
1220 ln_prev_gre NUMBER;
1221 lv_found VARCHAR2(1);
1222 ln_next_tab NUMBER;
1223 lv_county_id VARCHAR2(4);
1224 lv_city_id VARCHAR2(4);
1225
1226 ld_beginning_date DATE;
1227 ld_end_date DATE;
1228 ld_effective_date DATE;
1229
1230 ln_action_number NUMBER;
1231 ln_tax_unit_id NUMBER;
1232 lv_last_name per_all_people_f.last_name%type;
1233 lv_first_name per_all_people_f.first_name%type;
1234 lv_middle_name per_all_people_f.middle_names%type;
1235 lv_employee_number per_all_people_f.employee_number%type;
1236 lv_assignment_number per_all_assignments_f.assignment_number%type;
1237 lv_national_identifier per_all_people_f.national_identifier%type;
1238 lv_address_line pay_us_employee_action_info_v.address_line%type;
1239
1240 lv_town_or_city varchar2(150);
1241 lv_county varchar2(150);
1242 lv_state varchar2(150);
1243 lv_postal_code varchar2(150);
1244 lv_country varchar2(150);
1245 lv_gre_name hr_all_organization_units.name%type;
1249 lv_action_type varchar2(150);
1246 lv_fed_ein hr_organization_information.org_information2%type;
1247 lv_org_name hr_all_organization_units.name%type;
1248 lv_location_code hr_locations_all.location_code%type;
1250
1251 ln_fit_gross number;
1252 ln_fit_withheld number;
1253 ln_fit_reduced_subject number;
1254 ln_futa_liability number;
1255 ln_futa_taxable number;
1256 ln_ss_ee_withheld number;
1257 ln_ss_ee_taxable number;
1258 ln_ss_er_liability number;
1259 ln_ss_er_taxable number;
1260 ln_medicare_ee_withheld number;
1261 ln_medicare_ee_taxable number;
1262 ln_medicare_er_taxable number;
1263 ln_medicare_er_liability number;
1264 ln_eic_advance number;
1265
1266 ln_fit_gross_sum number := 0 ;
1267 ln_fit_withheld_sum number := 0 ;
1268 ln_fit_reduced_subject_sum number := 0 ;
1269 ln_futa_liability_sum number := 0 ;
1270 ln_futa_taxable_sum number := 0 ;
1271 ln_ss_ee_withheld_sum number := 0 ;
1272 ln_ss_ee_taxable_sum number := 0 ;
1273 ln_ss_er_liability_sum number := 0 ;
1274 ln_ss_er_taxable_sum number := 0 ;
1275 ln_medicare_ee_withheld_sum number := 0 ;
1276 ln_medicare_ee_taxable_sum number := 0 ;
1277 ln_medicare_er_taxable_sum number := 0 ;
1278 ln_medicare_er_liability_sum number := 0 ;
1279
1280 lv_jurisdiction varchar2(15);
1281 lv_jurisdiction_name varchar2(150);
1282
1283 lv_state_id varchar2(150);
1284 lv_state_ein varchar2(150);
1285 ln_sit_gross number;
1286 ln_sit_reduced_subject number;
1287 ln_sit_withheld number;
1288 ln_sui_ee_taxable number;
1289 ln_sui_ee_withheld number;
1290 ln_sui_er_taxable number;
1291 ln_sui_er_liability number;
1292 ln_sdi_ee_taxable number;
1293 ln_sdi_ee_withheld number;
1294 ln_sdi_er_taxable number;
1295 ln_sdi_er_liability number;
1296 ln_workers_comp_withheld number;
1297 ln_workers_comp2_withheld number;
1298
1299 ln_county_gross number;
1300 ln_county_reduced_subject number;
1301 ln_county_withheld number;
1302 ln_head_tax_withheld number;
1303 lv_non_resident_flag varchar2(5);
1304
1305 ln_city_gross number;
1306 ln_city_reduced_subject number;
1307 ln_city_withheld number;
1308 ln_city_gross_sum number;
1309 ln_city_reduced_subject_sum number;
1310 ln_city_withheld_sum number;
1311
1312 ln_school_withheld number;
1313 ln_school_gross number;
1314 ln_school_reduced_subject number;
1315
1316 lv_header_label VARCHAR2(32000);
1317 lv_emp_detail_header VARCHAR2(32000);
1318 lv_emp_sum_header VARCHAR2(32000);
1319 lv_fed_header VARCHAR2(32000);
1320 lv_state_header VARCHAR2(32000);
1321 lv_county_header VARCHAR2(32000);
1322 lv_city_header VARCHAR2(32000);
1323 lv_school_header VARCHAR2(32000);
1324
1325 lv_employee_data varchar2(32000);
1326 lv_federal_data varchar2(32000);
1327 lv_state_data varchar2(32000);
1328 lv_county_data varchar2(32000);
1329 lv_city_data varchar2(32000);
1330 lv_school_data varchar2(32000);
1331 lv_data_row varchar2(32000) := '';
1332 lv_prev_emp_data_row varchar2(32000) := '';
1333 lv_emp_data_row varchar2(32000) := '';
1334
1335 lv_federal_data_sum varchar2(32000);
1336 lv_state_data_sum varchar2(32000);
1337 lv_county_data_sum varchar2(32000);
1338 lv_city_data_sum varchar2(32000);
1339 lv_school_data_sum varchar2(32000);
1340
1341 type federal_rec IS RECORD
1342 (tax_unit_id number,
1343 gre_name varchar2(240),
1344 fit_gross number,
1345 fit_reduced_subject number,
1346 fit_withheld number,
1347 futa_taxable number,
1348 futa_liability number,
1349 ss_ee_taxable number,
1350 ss_ee_withheld number,
1351 ss_er_taxable number,
1352 ss_er_liability number,
1353 medicare_ee_taxable number,
1354 medicare_ee_withheld number,
1355 medicare_er_taxable number,
1356 medicare_er_liability number,
1357 eic_advance number);
1358
1359 type state_rec IS RECORD
1360 (tax_unit_id number,
1361 gre_name varchar2(240),
1362 state_ein varchar2(150),
1363 jurisdiction_code varchar2(150),
1364 jurisdiction_name varchar2(150),
1365 sit_gross number,
1366 sit_reduced_subject number,
1370 sui_er_taxable number,
1367 sit_withheld number,
1368 sui_ee_taxable number,
1369 sui_ee_withheld number,
1371 sui_er_liability number,
1372 sdi_ee_taxable number,
1373 sdi_ee_withheld number,
1374 sdi_er_taxable number,
1375 sdi_er_liability number,
1376 workers_comp_withheld number,
1377 workers_comp2_withheld number);
1378
1379 type county_rec IS RECORD
1380 (tax_unit_id number,
1381 gre_name varchar2(240),
1382 jurisdiction_code varchar2(150),
1383 jurisdiction_name varchar2(150),
1384 county_gross number,
1385 county_reduced_subject number,
1386 county_withheld number,
1387 county_head_tax_withheld number );
1388
1389 type city_rec IS RECORD
1390 (tax_unit_id number,
1391 gre_name varchar2(240),
1392 jurisdiction_code varchar2(150),
1393 jurisdiction_name varchar2(150),
1394 city_gross number,
1395 city_reduced_subject number,
1396 city_withheld number,
1397 head_tax_withheld number);
1398
1399 type school_rec IS RECORD
1400 (tax_unit_id number,
1401 gre_name varchar2(240),
1402 jurisdiction_code varchar2(150),
1403 jurisdiction_name varchar2(150),
1404 school_gross number,
1405 school_reduced_subject number,
1406 school_withheld number);
1407
1408 type federal_tab is table of federal_rec index by binary_integer;
1409 type state_tab is table of state_rec index by binary_integer;
1410 type county_tab is table of county_rec index by binary_integer;
1411 type city_tab is table of city_rec index by binary_integer;
1412 type school_tab is table of school_rec index by binary_integer;
1413
1414 federal_bal federal_tab;
1415 state_bal state_tab;
1416 county_bal county_tab;
1417 city_bal city_tab;
1418 school_bal school_tab;
1419
1420 BEGIN
1421 hr_utility.set_location(gv_package_name || '.archiver_extract', 10);
1422 ld_beginning_date := fnd_date.canonical_to_date(p_beginning_date);
1423 ld_end_date := fnd_date.canonical_to_date(p_end_date);
1424
1425 /* Create Headers for each column */
1426
1427 emp_detail_static_header( p_output_file_type ,lv_emp_detail_header);
1428 emp_sum_static_header( p_output_file_type ,lv_emp_sum_header);
1429 fed_static_header(p_output_file_type, lv_fed_header);
1430 state_static_header(p_output_file_type, lv_state_header);
1431 county_static_header(p_output_file_type, lv_county_header);
1432 city_static_header(p_output_file_type, lv_city_header);
1433 school_static_header(p_output_file_type, lv_school_header);
1434
1435 hr_utility.trace('----------Done with Static Header Lables ------------');
1436
1437 IF p_detail_level = '01' THEN
1438 if p_jurisdiction_level = '01' -- Federal
1439 then
1440 lv_header_label := lv_emp_detail_header||lv_fed_header;
1441 elsif p_jurisdiction_level = '02' -- State
1442 then
1443 lv_header_label := lv_emp_detail_header||lv_state_header;
1444 elsif p_jurisdiction_level = '03' -- County
1445 then
1446 lv_header_label := lv_emp_detail_header||lv_county_header;
1447 elsif p_jurisdiction_level = '04' -- City
1448 then
1449 lv_header_label := lv_emp_detail_header||lv_city_header;
1450 elsif p_jurisdiction_level = '05' -- school
1451 then
1452 lv_header_label := lv_emp_detail_header||lv_school_header;
1453 end if;
1454 ELSIF p_detail_level = '02' THEN
1455 if p_jurisdiction_level = '01' -- Federal
1456 then
1457 lv_header_label := lv_emp_sum_header||lv_fed_header;
1458 elsif p_jurisdiction_level = '02' -- State
1459 then
1460 lv_header_label := lv_emp_sum_header||lv_state_header;
1461 elsif p_jurisdiction_level = '03' -- County
1462 then
1463 lv_header_label := lv_emp_sum_header||lv_county_header;
1464 elsif p_jurisdiction_level = '04' -- City
1465 then
1466 lv_header_label := lv_emp_sum_header||lv_city_header;
1467 elsif p_jurisdiction_level = '05' -- school
1468 then
1469 lv_header_label := lv_emp_sum_header||lv_school_header;
1470 end if;
1471 END IF;
1472
1473 hr_utility.set_location(gv_package_name || '.archiver_extract', 70);
1474
1475 /* write the title of the report based on the output file type */
1476
1477 fnd_file.put_line(fnd_file.output,
1478 title_string( gv_title
1479 ,p_output_file_type));
1480
1481 hr_utility.set_location(gv_package_name || '.archiver_extract', 90);
1482 /****************************************************************
1483 ** Print the Header Information. If the format is HTML then open
1484 ** the body and table before printing the header info, otherwise
1485 ** just print the header information.
1486 ****************************************************************/
1487 hr_utility.trace('Output File Type = '||p_output_file_type);
1488
1489 if p_output_file_type ='HTML' then
1490 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1495 fnd_file.put_line(fnd_file.output, lv_header_label);
1492 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1493 end if;
1494
1496
1497 if p_output_file_type ='HTML' then
1498 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1499 end if;
1500
1501 hr_utility.set_location(gv_package_name || '.archiver_extract', 100);
1502
1503 /*****************************************************
1504 ** Start of the Data Section of the Report
1505 *****************************************************/
1506 hr_utility.trace('Assignment Set ID = ' || p_assignment_set_id);
1507 hr_utility.trace('Beginning Date = '||ld_beginning_date);
1508 hr_utility.trace('Ending Date = '||ld_end_date);
1509 hr_utility.trace('Payroll id = '||p_payroll_id);
1510 hr_utility.trace('Consolidation Set id = '||p_consolidation_set_id);
1511 hr_utility.trace('Organization id = '||p_organization_id);
1512 hr_utility.trace('Tax Unit Id = '||p_tax_unit_id);
1513 hr_utility.trace('Location id = '||p_location_id);
1514 hr_utility.trace('Business Group id = '||p_business_group_id);
1515 hr_utility.trace('State Id = '||p_state_id);
1516 hr_utility.trace('County Id = '||p_county_id);
1517 hr_utility.trace('City Id = '||p_city_id);
1518 hr_utility.trace('School Id = '||p_school_id);
1519
1520 ln_prev_person := -1;
1521 ln_prev_gre := -1;
1522 /*
1523 open c_employee_count( ld_beginning_date
1524 ,ld_end_date
1525 ,p_payroll_id
1526 ,p_consolidation_set_id
1527 ,p_organization_id
1528 ,p_tax_unit_id
1529 ,p_location_id
1530 ,p_business_group_id);
1531
1532 LOOP
1533 fetch c_employee_count into ln_person_id, lv_last_name, ln_action_number;
1534 hr_utility.trace('ln_person_id = '||ln_person_id);
1535 hr_utility.trace('lv_last_name = '||lv_last_name);
1536 hr_utility.trace('ln_action_number = '||ln_action_number);
1537 exit when c_employee_count%NOTFOUND;
1538 END LOOP;
1539 close c_employee_count;
1540 */
1541
1542 open c_employee( ld_beginning_date
1543 ,ld_end_date
1544 ,p_payroll_id
1545 ,p_consolidation_set_id
1546 ,p_organization_id
1547 ,p_tax_unit_id
1548 ,p_location_id
1549 ,p_business_group_id);
1550
1551 LOOP
1552 fetch c_employee into ln_action_number,
1553 lv_last_name,
1554 lv_first_name,
1555 lv_middle_name,
1556 lv_employee_number,
1557 lv_assignment_number,
1558 ln_assignment_id,
1559 lv_national_identifier,
1560 lv_address_line,
1561 lv_town_or_city,
1562 lv_county,
1563 lv_state,
1564 lv_postal_code,
1565 lv_country,
1566 ln_tax_unit_id,
1567 lv_gre_name,
1568 lv_fed_ein,
1569 lv_org_name,
1570 lv_location_code,
1571 lv_action_type,
1572 ln_person_id,
1573 ld_effective_date;
1574 EXIT WHEN c_employee%NOTFOUND;
1575 hr_utility.trace('-----------------------------------------------');
1576 hr_utility.trace('C_EMPLOYEE CURSOR');
1577 hr_utility.trace('ln_action_number = '||ln_action_number);
1578 hr_utility.trace('lv_last_name = '||lv_last_name);
1579 hr_utility.trace('ln_person_id = '||ln_person_id);
1580 hr_utility.trace('-----------------------------------------------');
1581
1582 if ln_prev_person = -1 then
1583 ln_prev_person := ln_person_id;
1584 end if;
1585 if ln_prev_gre = -1 then
1586 ln_prev_gre := ln_tax_unit_id;
1587 end if;
1588
1589 if c_employee%notfound then
1590 hr_utility.trace('EMPLOYEE NOT FOUND');
1591 hr_utility.set_location(gv_package_name || '.archiver_extract', 105);
1592 exit;
1593 else
1594 hr_utility.trace('EMPLOYEE FOUND');
1595 -- hr_utility.trace('Employee action_number = '||ln_action_number);
1596 -- hr_utility.trace('Employee Last Name = '||lv_last_name);
1597 -- hr_utility.trace('Employee First Name = '||lv_first_name);
1598 end if;
1599
1600 /*----------------------------------------------------------
1601 -- If Assignment Set is used, pick up only those employee
1602 -- assignments which are part of the Assignment Set
1603 -----------------------------------------------------------*/
1604 hr_utility.set_location(gv_package_name || '.archiver_extract', 110);
1605
1606 if hr_assignment_set.assignment_in_set(p_assignment_set_id
1607 ,ln_assignment_id) = 'Y' then
1608
1609 hr_utility.set_location(gv_package_name || '.archiver_extract', 120);
1610
1611 /************************************/
1612 /*** FEDERAL balances ***/
1613 /************************************/
1614
1615 IF p_jurisdiction_level = '01' THEN
1616 open c_federal_balances( ln_action_number );
1620 ln_fit_gross,
1617 LOOP
1618
1619 fetch c_federal_balances into ln_action_number,
1621 ln_fit_reduced_subject,
1622 ln_fit_withheld,
1623 ln_futa_taxable,
1624 ln_futa_liability,
1625 ln_ss_ee_taxable,
1626 ln_ss_ee_withheld,
1627 ln_ss_er_taxable,
1628 ln_ss_er_liability,
1629 ln_medicare_ee_taxable,
1630 ln_medicare_ee_withheld,
1631 ln_medicare_er_taxable,
1632 ln_medicare_er_liability,
1633 ln_eic_advance;
1634 EXIT WHEN c_federal_balances%NOTFOUND;
1635
1636 hr_utility.trace('Fetched FEDERAL balance ---------------');
1637 hr_utility.trace('Detail Level in FED = '||p_detail_level);
1638 hr_utility.trace('ln_action_number = '||ln_action_number);
1639 hr_utility.trace('ln_fit_gross = '||ln_fit_gross);
1640 hr_utility.trace('ln_fit_withheld = '||ln_fit_withheld);
1641
1642 IF p_detail_level = '01' THEN -- By Run
1643 emp_static_data(
1644 lv_last_name,
1645 lv_first_name,
1646 lv_middle_name,
1647 lv_employee_number,
1648 lv_assignment_number,
1649 lv_national_identifier,
1650 lv_address_line,
1651 lv_town_or_city,
1652 lv_county,
1653 lv_state,
1654 lv_postal_code,
1655 lv_country,
1656 lv_fed_ein,
1657 lv_org_name,
1658 lv_location_code,
1659 lv_action_type,
1660 ld_effective_date,
1661 p_output_file_type,
1662 lv_employee_data);
1663
1664 lv_data_row := lv_employee_data;
1665
1666 fed_static_data( lv_gre_name,
1667 nvl(ln_fit_gross,0),
1668 nvl(ln_fit_reduced_subject,0),
1669 nvl(ln_fit_withheld,0),
1670 nvl(ln_futa_taxable,0),
1671 nvl(ln_futa_liability,0),
1672 nvl(ln_ss_ee_taxable,0),
1673 nvl(ln_ss_ee_withheld,0),
1674 nvl(ln_ss_er_taxable,0),
1675 nvl(ln_ss_er_liability,0),
1676 nvl(ln_medicare_ee_taxable,0),
1677 nvl(ln_medicare_ee_withheld,0),
1678 nvl(ln_medicare_er_taxable,0),
1679 nvl(ln_medicare_er_liability,0),
1680 nvl(ln_eic_advance,0),
1681 p_output_file_type,
1682 lv_federal_data);
1683 lv_data_row := lv_data_row||lv_federal_data;
1684 --hr_utility.trace('FED static data = '||lv_federal_data);
1685 --hr_utility.trace('Data Row = '||lv_data_row);
1686
1687 if p_output_file_type = 'HTML' then
1688 lv_data_row := '<tr>'||lv_data_row||'</tr>';
1689 end if;
1690
1691 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
1692 lv_data_row := lv_employee_data;
1693 END IF;
1694
1695 /******* Federal Summary Level ********/
1696
1697 IF p_detail_level = '02' THEN
1698 hr_utility.trace('------------------------------------------');
1699 hr_utility.trace('Federal by summary.......................');
1700 hr_utility.trace('ln_prev_person = '||ln_prev_person);
1701 hr_utility.trace('ln_person_id = '||ln_person_id);
1702
1703 IF ln_prev_person = ln_person_id then
1704 emp_sum_static_data( lv_last_name,
1705 lv_first_name,
1706 lv_middle_name,
1707 lv_employee_number,
1708 lv_national_identifier,
1709 lv_fed_ein,
1710 p_output_file_type,
1711 lv_employee_data);
1712
1713 lv_data_row := lv_employee_data;
1714
1715 lv_found := 'N';
1716
1717 hr_utility.trace('ln_fit_gross = '||ln_fit_gross);
1718 hr_utility.trace('ln_fit_gross_sum BEFORE adding = '||
1719 ln_fit_gross_sum);
1720 hr_utility.trace('ln_prev_gre = '||ln_prev_gre);
1721 hr_utility.trace('ln_tax_unit_id = '||ln_tax_unit_id);
1722
1723 IF federal_bal.count > 0 THEN
1724 FOR k in federal_bal.first..federal_bal.last LOOP
1725 IF federal_bal(k).tax_unit_id = ln_tax_unit_id THEN
1729 federal_bal(k).fit_gross := federal_bal(k).fit_gross +
1726 lv_found := 'Y';
1727 federal_bal(k).tax_unit_id := ln_tax_unit_id;
1728 federal_bal(k).gre_name := lv_gre_name;
1730 nvl(ln_fit_gross,0);
1731 federal_bal(k).fit_reduced_subject := nvl(ln_fit_reduced_subject,0)+
1732 federal_bal(k).fit_reduced_subject;
1733 federal_bal(k).fit_withheld := nvl(ln_fit_withheld,0) +
1734 federal_bal(k).fit_withheld;
1735 federal_bal(k).futa_taxable := nvl(ln_futa_taxable,0) +
1736 federal_bal(k).futa_taxable;
1737 federal_bal(k).futa_liability := nvl(ln_futa_liability,0) +
1738 federal_bal(k).futa_liability;
1739 federal_bal(k).ss_ee_taxable := nvl(ln_ss_ee_taxable,0) +
1740 federal_bal(k).ss_ee_taxable;
1741 federal_bal(k).ss_ee_withheld := nvl(ln_ss_ee_withheld,0) +
1742 federal_bal(k).ss_ee_withheld;
1743 federal_bal(k).ss_er_taxable := nvl(ln_ss_er_taxable,0) +
1744 federal_bal(k).ss_er_taxable;
1745 federal_bal(k).ss_er_liability := nvl(ln_ss_er_liability,0) +
1746 federal_bal(k).ss_er_liability;
1747 federal_bal(k).medicare_ee_taxable := nvl(ln_medicare_ee_taxable,0) +
1748 federal_bal(k).medicare_ee_taxable;
1749 federal_bal(k).medicare_ee_withheld :=
1750 nvl(ln_medicare_ee_withheld,0) +
1751 federal_bal(k).medicare_ee_withheld;
1752 federal_bal(k).medicare_er_taxable := nvl(ln_medicare_er_taxable,0) +
1753 federal_bal(k).medicare_er_taxable;
1754 federal_bal(k).medicare_er_liability :=
1755 nvl(ln_medicare_er_liability,0) +
1756 federal_bal(k).medicare_er_liability;
1757 federal_bal(k).eic_advance := nvl(ln_eic_advance,0) +
1758 federal_bal(k).eic_advance;
1759 END IF;
1760 END LOOP;
1761 /* sackumar(Bug 4559897) for multiple GREs */
1762 if lv_found = 'N' then
1763 ln_next_tab := federal_bal.count + 1;
1764 federal_bal(ln_next_tab).tax_unit_id := ln_tax_unit_id;
1765 federal_bal(ln_next_tab).gre_name := lv_gre_name;
1766 federal_bal(ln_next_tab).fit_gross := nvl(ln_fit_gross,0);
1767 federal_bal(ln_next_tab).fit_reduced_subject := nvl(ln_fit_reduced_subject,0);
1768 federal_bal(ln_next_tab).fit_withheld := nvl(ln_fit_withheld,0) ;
1769 federal_bal(ln_next_tab).futa_taxable := nvl(ln_futa_taxable,0) ;
1770 federal_bal(ln_next_tab).futa_liability := nvl(ln_futa_liability,0) ;
1771 federal_bal(ln_next_tab).ss_ee_taxable := nvl(ln_ss_ee_taxable,0) ;
1772 federal_bal(ln_next_tab).ss_ee_withheld := nvl(ln_ss_ee_withheld,0) ;
1773 federal_bal(ln_next_tab).ss_er_taxable := nvl(ln_ss_er_taxable,0) ;
1774 federal_bal(ln_next_tab).ss_er_liability := nvl(ln_ss_er_liability,0) ;
1775 federal_bal(ln_next_tab).medicare_ee_taxable := nvl(ln_medicare_ee_taxable,0) ;
1776 federal_bal(ln_next_tab).medicare_ee_withheld := nvl(ln_medicare_ee_withheld,0);
1777 federal_bal(ln_next_tab).medicare_er_taxable := nvl(ln_medicare_er_taxable,0) ;
1778 federal_bal(ln_next_tab).medicare_er_liability := nvl(ln_medicare_er_liability,0) ;
1779 federal_bal(ln_next_tab).eic_advance := nvl(ln_eic_advance,0);
1780 end if;
1781 ELSE /* Federal_bal.count = 0, first fetch */
1782 federal_bal(1).tax_unit_id := ln_tax_unit_id;
1783 federal_bal(1).gre_name := lv_gre_name;
1784 federal_bal(1).fit_gross := nvl(ln_fit_gross,0);
1785 federal_bal(1).fit_reduced_subject := nvl(ln_fit_reduced_subject,0);
1786 federal_bal(1).fit_withheld := nvl(ln_fit_withheld,0) ;
1787 federal_bal(1).futa_taxable := nvl(ln_futa_taxable,0) ;
1788 federal_bal(1).futa_liability := nvl(ln_futa_liability,0) ;
1789 federal_bal(1).ss_ee_taxable := nvl(ln_ss_ee_taxable,0) ;
1790 federal_bal(1).ss_ee_withheld := nvl(ln_ss_ee_withheld,0) ;
1791 federal_bal(1).ss_er_taxable := nvl(ln_ss_er_taxable,0) ;
1792 federal_bal(1).ss_er_liability := nvl(ln_ss_er_liability,0) ;
1793 federal_bal(1).medicare_ee_taxable := nvl(ln_medicare_ee_taxable,0) ;
1794 federal_bal(1).medicare_ee_withheld := nvl(ln_medicare_ee_withheld,0);
1795 federal_bal(1).medicare_er_taxable := nvl(ln_medicare_er_taxable,0) ;
1796 federal_bal(1).medicare_er_liability := nvl(ln_medicare_er_liability,0) ;
1797 federal_bal(1).eic_advance := nvl(ln_eic_advance,0);
1798 END IF; /* federal_bal.count check */
1799
1800 ELSE /* New person fetched */
1804 lv_prev_emp_data_row := lv_data_row;
1801 hr_utility.trace('Inside FED ELSE...........');
1802 hr_utility.trace('New Person fetched ..........');
1803
1805 IF federal_bal.count>0 THEN
1806 FOR k in federal_bal.first..federal_bal.last LOOP
1807
1808 fed_static_data(federal_bal(k).gre_name,
1809 federal_bal(k).fit_gross,
1810 federal_bal(k).fit_reduced_subject,
1811 federal_bal(k).fit_withheld,
1812 federal_bal(k).futa_taxable,
1813 federal_bal(k).futa_liability,
1814 federal_bal(k).ss_ee_taxable,
1815 federal_bal(k).ss_ee_withheld,
1816 federal_bal(k).ss_er_taxable,
1817 federal_bal(k).ss_er_liability,
1818 federal_bal(k).medicare_ee_taxable,
1819 federal_bal(k).medicare_ee_withheld,
1820 federal_bal(k).medicare_er_taxable,
1821 federal_bal(k).medicare_er_liability,
1822 federal_bal(k).eic_advance,
1823 p_output_file_type,
1824 lv_federal_data_sum);
1825
1826 lv_data_row := lv_prev_emp_data_row||lv_federal_data_sum;
1827
1828 if p_output_file_type = 'HTML' then
1829 lv_data_row := '<tr>'||lv_data_row||'</tr>';
1830 end if;
1831 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
1832 END LOOP;
1833 END IF; -- federal_bal.count
1834 /* Now Build employee static header with the new fetched person */
1835 emp_sum_static_data( lv_last_name,
1836 lv_first_name,
1837 lv_middle_name,
1838 lv_employee_number,
1839 lv_national_identifier,
1840 lv_fed_ein,
1841 p_output_file_type,
1842 lv_employee_data);
1843 lv_data_row := lv_employee_data;
1844 ln_prev_person := ln_person_id;
1845
1846 /******* rest sum to currently fetched person *****/
1847
1848 hr_utility.trace('resetting summary for Federal ...........');
1849 IF federal_bal.count > 0 THEN
1850 FOR k in federal_bal.first..federal_bal.last LOOP
1851 federal_bal(k).tax_unit_id := -1;
1852 federal_bal(k).gre_name := '';
1853 federal_bal(k).fit_gross := 0;
1854 federal_bal(k).fit_reduced_subject := 0;
1855 federal_bal(k).fit_withheld := 0;
1856 federal_bal(k).futa_taxable := 0;
1857 federal_bal(k).futa_liability := 0;
1858 federal_bal(k).ss_ee_taxable := 0;
1859 federal_bal(k).ss_ee_withheld := 0;
1860 federal_bal(k).ss_er_taxable := 0;
1861 federal_bal(k).ss_er_liability := 0;
1862 federal_bal(k).medicare_ee_taxable := 0;
1863 federal_bal(k).medicare_ee_withheld := 0;
1864 federal_bal(k).medicare_er_taxable := 0;
1865 federal_bal(k).medicare_er_liability := 0;
1866 federal_bal(k).eic_advance := 0;
1867 END LOOP;
1868 END IF;
1869 federal_bal.delete;
1870
1871 federal_bal(1).tax_unit_id := ln_tax_unit_id;
1872 federal_bal(1).gre_name := lv_gre_name;
1873 federal_bal(1).fit_gross := nvl(ln_fit_gross,0);
1874 federal_bal(1).fit_reduced_subject := nvl(ln_fit_reduced_subject,0);
1875 federal_bal(1).fit_withheld := nvl(ln_fit_withheld,0) ;
1876 federal_bal(1).futa_taxable := nvl(ln_futa_taxable,0) ;
1877 federal_bal(1).futa_liability := nvl(ln_futa_liability,0) ;
1878 federal_bal(1).ss_ee_taxable := nvl(ln_ss_ee_taxable,0) ;
1879 federal_bal(1).ss_ee_withheld := nvl(ln_ss_ee_withheld,0) ;
1880 federal_bal(1).ss_er_taxable := nvl(ln_ss_er_taxable,0) ;
1881 federal_bal(1).ss_er_liability := nvl(ln_ss_er_liability,0) ;
1882 federal_bal(1).medicare_ee_taxable := nvl(ln_medicare_ee_taxable,0) ;
1883 federal_bal(1).medicare_ee_withheld := nvl(ln_medicare_ee_withheld,0);
1884 federal_bal(1).medicare_er_taxable := nvl(ln_medicare_er_taxable,0) ;
1885 federal_bal(1).medicare_er_liability := nvl(ln_medicare_er_liability,0) ;
1886 federal_bal(1).eic_advance := nvl(ln_eic_advance,0);
1887
1888 END IF; /* Person Check */
1889 END IF; /* Detail Level check */
1890 END LOOP; -- Federal balance loop
1891 CLOSE c_federal_balances;
1892 END IF; -- jurisdiction level check
1893
1894
1895
1896 /****************************************/
1897 /*** STATE balances ***/
1898 /****************************************/
1899
1903 hr_utility.trace('ln_tax_unit_id = '||ln_tax_unit_id);
1900 IF p_jurisdiction_level = '02' THEN
1901 hr_utility.trace('-------------------------------------');
1902 hr_utility.trace('.......... In STATE..................');
1904 hr_utility.trace('p_state_id = '||p_state_id);
1905 hr_utility.trace('ln_action_number = '||ln_action_number);
1906 hr_utility.trace('-------------------------------------');
1907
1908 open c_state_balances(ln_action_number,
1909 p_state_id);
1910 hr_utility.trace('State first cursor OPEN ');
1911
1912 LOOP
1913
1914 hr_utility.trace('Fetching STATE balance ...............');
1915
1916 fetch c_state_balances into lv_jurisdiction,
1917 lv_jurisdiction_name,
1918 ln_sit_gross,
1919 ln_sit_reduced_subject,
1920 ln_sit_withheld,
1921 ln_sui_ee_taxable,
1922 ln_sui_ee_withheld,
1923 ln_sui_er_taxable,
1924 ln_sui_er_liability,
1925 ln_sdi_ee_taxable,
1926 ln_sdi_ee_withheld,
1927 ln_sdi_er_taxable,
1928 ln_sdi_er_liability,
1929 ln_workers_comp_withheld,
1930 ln_workers_comp2_withheld;
1931
1932 EXIT WHEN c_state_balances%NOTFOUND;
1933
1934 lv_state_ein := f_state_ein(ln_tax_unit_id,
1935 substr(lv_jurisdiction,1,2));
1936
1937 hr_utility.trace('----------------------------------------');
1938 hr_utility.trace('Fetched STATE record...............');
1939 hr_utility.trace('ln_sit_gross = '||ln_sit_gross);
1940 hr_utility.trace('ln_sit_withheld = '||ln_sit_withheld);
1941 hr_utility.trace('----------------------------------------');
1942
1943 /******* State Balance By Run *****/
1944
1945 IF p_detail_level = '01' THEN
1946
1947 hr_utility.trace('----------------------------------------');
1948 hr_utility.trace('State by run out put....................');
1949 hr_utility.trace('ln_action_number = '||ln_action_number);
1950 hr_utility.trace('----------------------------------------');
1951
1952 emp_static_data(
1953 lv_last_name,
1954 lv_first_name,
1955 lv_middle_name,
1956 lv_employee_number,
1957 lv_assignment_number,
1958 lv_national_identifier,
1959 lv_address_line,
1960 lv_town_or_city,
1961 lv_county,
1962 lv_state,
1963 lv_postal_code,
1964 lv_country,
1965 lv_fed_ein,
1966 lv_org_name,
1967 lv_location_code,
1968 lv_action_type,
1969 ld_effective_date,
1970 p_output_file_type,
1971 lv_employee_data);
1972
1973 lv_data_row := lv_employee_data;
1974
1975 state_static_data(lv_gre_name,
1976 lv_state_ein,
1977 lv_jurisdiction,
1978 lv_jurisdiction_name,
1979 nvl(ln_sit_gross,0),
1980 nvl(ln_sit_reduced_subject,0),
1981 nvl(ln_sit_withheld,0),
1982 nvl(ln_sui_ee_taxable,0),
1983 nvl(ln_sui_ee_withheld,0),
1984 nvl(ln_sui_er_taxable,0),
1985 nvl(ln_sui_er_liability,0),
1986 nvl(ln_sdi_ee_taxable,0),
1987 nvl(ln_sdi_ee_withheld,0),
1988 nvl(ln_sdi_er_taxable,0),
1989 nvl(ln_sdi_er_liability,0),
1990 nvl(ln_workers_comp_withheld,0),
1991 nvl(ln_workers_comp2_withheld,0),
1992 p_output_file_type,
1993 lv_state_data);
1994 lv_data_row := lv_data_row||lv_state_data;
1995
1996 if p_output_file_type = 'HTML' then
1997 lv_data_row := '<tr>'||lv_data_row||'</tr>';
1998 end if;
1999 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
2000 END IF;
2001
2002
2003 /********* State Balance by Summary(by jurisdiction level) *****/
2004
2005 hr_utility.trace('---------State by Summary --------------');
2006 hr_utility.trace('ln_prev_person = '||ln_prev_person);
2007 hr_utility.trace('ln_person_id = '||ln_person_id);
2008 hr_utility.trace('----------------------------------------');
2009
2010 IF p_detail_level = '02' then
2011
2012 IF ln_prev_person = ln_person_id Then
2013 emp_sum_static_data( lv_last_name,
2014 lv_first_name,
2018 lv_fed_ein,
2015 lv_middle_name,
2016 lv_employee_number,
2017 lv_national_identifier,
2019 p_output_file_type,
2020 lv_employee_data);
2021 lv_data_row := lv_employee_data;
2022
2023 IF p_state_id is null THEN
2024 lv_state_id := substr(lv_jurisdiction,1,2);
2025 ELSE
2026 lv_state_id := p_state_id;
2027 END IF;
2028
2029 hr_utility.trace('lv_state_id = '||lv_state_id);
2030
2031 lv_found := 'N';
2032 hr_utility.trace('state_bal.count = '||state_bal.count);
2033 hr_utility.trace('lv_jurisdiction = '||lv_jurisdiction);
2034 IF state_bal.count > 0 THEN
2035 FOR k in state_bal.first..state_bal.last LOOP
2036 IF (state_bal(k).jurisdiction_code = lv_jurisdiction
2037 AND
2038 state_bal(k).tax_unit_id = ln_tax_unit_id) THEN
2039 lv_found := 'Y';
2040 state_bal(k).tax_unit_id := ln_tax_unit_id;
2041 state_bal(k).gre_name := lv_gre_name;
2042 state_bal(k).state_ein := lv_state_ein;
2043 state_bal(k).sit_gross := nvl(ln_sit_gross,0) +
2044 state_bal(k).sit_gross;
2045 state_bal(k).sit_reduced_subject :=
2046 nvl(ln_sit_reduced_subject,0) +
2047 state_bal(k).sit_reduced_subject;
2048 state_bal(k).sit_withheld := nvl(ln_sit_withheld,0) +
2049 state_bal(k).sit_withheld;
2050 state_bal(k).sui_ee_taxable := nvl(ln_sui_ee_taxable,0) +
2051 state_bal(k).sui_ee_taxable;
2052 state_bal(k).sui_ee_withheld := nvl(ln_sui_ee_withheld,0) +
2053 state_bal(k).sui_ee_withheld ;
2054 state_bal(k).sui_er_taxable := nvl(ln_sui_er_taxable,0) +
2055 state_bal(k).sui_er_taxable ;
2056 state_bal(k).sui_er_liability := nvl(ln_sui_er_liability,0) +
2057 state_bal(k).sui_er_liability;
2058 state_bal(k).sdi_ee_taxable := nvl(ln_sdi_ee_taxable,0) +
2059 state_bal(k).sdi_ee_taxable;
2060 state_bal(k).sdi_ee_withheld := nvl(ln_sdi_ee_withheld,0) +
2061 state_bal(k).sdi_ee_withheld;
2062 state_bal(k).sdi_er_taxable := nvl(ln_sdi_er_taxable,0) +
2063 state_bal(k).sdi_er_taxable;
2064 state_bal(k).sdi_er_liability := nvl(ln_sdi_er_liability,0) +
2065 state_bal(k).sdi_er_liability ;
2066 state_bal(k).workers_comp_withheld :=
2067 nvl(ln_workers_comp_withheld,0) +
2068 state_bal(k).workers_comp_withheld ;
2069 state_bal(k).workers_comp2_withheld:=
2070 nvl(ln_workers_comp2_withheld ,0)+
2071 state_bal(k).workers_comp2_withheld;
2072 END IF;
2073 END LOOP;
2074 hr_utility.trace('end of Loop');
2075 hr_utility.trace('lv_found := '||lv_found);
2076 IF lv_found = 'N' THEN
2077 ln_next_tab := state_bal.count + 1;
2078 state_bal(ln_next_tab).tax_unit_id := ln_tax_unit_id;
2079 state_bal(ln_next_tab).gre_name := lv_gre_name;
2080 state_bal(ln_next_tab).state_ein := lv_state_ein;
2081 state_bal(ln_next_tab).jurisdiction_code :=
2082 lv_jurisdiction;
2083 state_bal(ln_next_tab).jurisdiction_name :=
2084 lv_jurisdiction_name;
2085 state_bal(ln_next_tab).sit_gross := nvl(ln_sit_gross,0) ;
2086 state_bal(ln_next_tab).sit_reduced_subject :=
2087 nvl(ln_sit_reduced_subject,0) ;
2088 state_bal(ln_next_tab).sit_withheld := nvl(ln_sit_withheld,0) ;
2089 state_bal(ln_next_tab).sui_ee_taxable :=
2090 nvl(ln_sui_ee_taxable,0) ;
2091 state_bal(ln_next_tab).sui_ee_withheld :=
2092 nvl(ln_sui_ee_withheld,0) ;
2093 state_bal(ln_next_tab).sui_er_taxable :=
2094 nvl(ln_sui_er_taxable,0) ;
2095 state_bal(ln_next_tab).sui_er_liability :=
2096 nvl(ln_sui_er_liability,0) ;
2097 state_bal(ln_next_tab).sdi_ee_taxable :=
2098 nvl(ln_sdi_ee_taxable,0) ;
2099 state_bal(ln_next_tab).sdi_ee_withheld :=
2100 nvl(ln_sdi_ee_withheld,0) ;
2101 state_bal(ln_next_tab).sdi_er_taxable :=
2105 state_bal(ln_next_tab).workers_comp_withheld :=
2102 nvl(ln_sdi_er_taxable,0) ;
2103 state_bal(ln_next_tab).sdi_er_liability :=
2104 nvl(ln_sdi_er_liability,0) ;
2106 nvl(ln_workers_comp_withheld,0) ;
2107 state_bal(ln_next_tab).workers_comp2_withheld:=
2108 nvl(ln_workers_comp2_withheld,0) ;
2109 END IF;
2110
2111 ELSE /* state_bal.count = 0, first fetch **/
2112 hr_utility.trace('STATE first fetch .................');
2113 state_bal(1).tax_unit_id := ln_tax_unit_id;
2114 state_bal(1).gre_name := lv_gre_name;
2115 state_bal(1).state_ein := lv_state_ein;
2116 state_bal(1).jurisdiction_code := lv_jurisdiction;
2117 state_bal(1).jurisdiction_name := lv_jurisdiction_name;
2118 state_bal(1).sit_gross := nvl(ln_sit_gross,0) ;
2119 state_bal(1).sit_reduced_subject := nvl(ln_sit_reduced_subject,0) ;
2120 state_bal(1).sit_withheld := nvl(ln_sit_withheld,0) ;
2121 state_bal(1).sui_ee_taxable := nvl(ln_sui_ee_taxable,0) ;
2122 state_bal(1).sui_ee_withheld := nvl(ln_sui_ee_withheld,0) ;
2123 state_bal(1).sui_er_taxable := nvl(ln_sui_er_taxable,0) ;
2124 state_bal(1).sui_er_liability := nvl(ln_sui_er_liability,0) ;
2125 state_bal(1).sdi_ee_taxable := nvl(ln_sdi_ee_taxable,0) ;
2126 state_bal(1).sdi_ee_withheld := nvl(ln_sdi_ee_withheld,0) ;
2127 state_bal(1).sdi_er_taxable := nvl(ln_sdi_er_taxable,0) ;
2128 state_bal(1).sdi_er_liability := nvl(ln_sdi_er_liability,0) ;
2129 state_bal(1).workers_comp_withheld :=
2130 nvl(ln_workers_comp_withheld,0) ;
2131 state_bal(1).workers_comp2_withheld:=
2132 nvl(ln_workers_comp2_withheld,0);
2133 END IF;
2134 ELSE /** New Person Fetched, write out prev person **/
2135 hr_utility.trace('.....State ELSE New Person Fetched .......');
2136 hr_utility.trace('Write out the previous person.............');
2137
2138 lv_prev_emp_data_row := lv_data_row;
2139
2140 IF state_bal.count > 0 THEN
2141 FOR i in state_bal.first..state_bal.last LOOP
2142 state_static_data( state_bal(i).gre_name
2143 ,state_bal(i).state_ein
2144 ,state_bal(i).jurisdiction_code
2145 ,state_bal(i).jurisdiction_name
2146 ,state_bal(i).sit_gross
2147 ,state_bal(i).sit_reduced_subject
2148 ,state_bal(i).sit_withheld
2149 ,state_bal(i).sui_ee_taxable
2150 ,state_bal(i).sui_ee_withheld
2151 ,state_bal(i).sui_er_taxable
2152 ,state_bal(i).sui_er_liability
2153 ,state_bal(i).sdi_ee_taxable
2154 ,state_bal(i).sdi_ee_withheld
2155 ,state_bal(i).sdi_er_taxable
2156 ,state_bal(i).sdi_er_liability
2157 ,state_bal(i).workers_comp_withheld
2158 ,state_bal(i).workers_comp2_withheld
2159 ,p_output_file_type
2160 ,lv_state_data_sum);
2161
2162 lv_data_row := lv_prev_emp_data_row||lv_state_data_sum;
2163
2164 if p_output_file_type = 'HTML' then
2165 lv_data_row := '<tr>'||lv_data_row||'</tr>';
2166 end if;
2167 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
2168 END LOOP;
2169 END IF; /* state_bal.count */
2170 hr_utility.trace('Before building new emp header');
2171
2172 /** Now, build employee static header with the new
2173 fetched record **/
2174
2175 emp_sum_static_data( lv_last_name,
2176 lv_first_name,
2177 lv_middle_name,
2178 lv_employee_number,
2179 lv_national_identifier,
2180 lv_fed_ein,
2181 p_output_file_type,
2182 lv_employee_data);
2183 lv_data_row := lv_employee_data;
2184 /* Set the prev person to this fetched person */
2185 ln_prev_person := ln_person_id;
2186
2187 /******** reset Sum to currently fetched record *******/
2188
2189 hr_utility.trace('Resetting Summary for State .............');
2190 IF state_bal.count > 0 THEN
2191 FOR i in state_bal.first..state_bal.last LOOP
2192 state_bal(i).tax_unit_id := -1;
2193 state_bal(i).gre_name := null;
2194 state_bal(i).state_ein := null;
2198 state_bal(i).sit_reduced_subject := 0;
2195 state_bal(i).jurisdiction_code := null;
2196 state_bal(i).jurisdiction_name := null;
2197 state_bal(i).sit_gross := 0;
2199 state_bal(i).sit_withheld := 0;
2200 state_bal(i).sui_ee_taxable := 0;
2201 state_bal(i).sui_ee_withheld := 0;
2202 state_bal(i).sui_er_taxable := 0;
2203 state_bal(i).sui_er_liability := 0;
2204 state_bal(i).sdi_ee_taxable := 0;
2205 state_bal(i).sdi_ee_withheld := 0;
2206 state_bal(i).sdi_er_taxable := 0;
2207 state_bal(i).sdi_er_liability := 0;
2208 state_bal(i).workers_comp_withheld := 0;
2209 state_bal(i).workers_comp2_withheld := 0;
2210 END LOOP;
2211 END IF;
2212 state_bal.delete;
2213
2214 state_bal(1).tax_unit_id := ln_tax_unit_id;
2215 state_bal(1).gre_name := lv_gre_name;
2216 state_bal(1).state_ein := lv_state_ein;
2217 state_bal(1).jurisdiction_code := lv_jurisdiction;
2218 state_bal(1).jurisdiction_name := lv_jurisdiction_name;
2219 state_bal(1).sit_gross := nvl(ln_sit_gross,0) ;
2220 state_bal(1).sit_reduced_subject := nvl(ln_sit_reduced_subject,0) ;
2221 state_bal(1).sit_withheld := nvl(ln_sit_withheld,0) ;
2222 state_bal(1).sui_ee_taxable := nvl(ln_sui_ee_taxable,0) ;
2223 state_bal(1).sui_ee_withheld := nvl(ln_sui_ee_withheld,0) ;
2224 state_bal(1).sui_er_taxable := nvl(ln_sui_er_taxable,0) ;
2225 state_bal(1).sui_er_liability := nvl(ln_sui_er_liability,0) ;
2226 state_bal(1).sdi_ee_taxable := nvl(ln_sdi_ee_taxable,0) ;
2227 state_bal(1).sdi_ee_withheld := nvl(ln_sdi_ee_withheld,0) ;
2228 state_bal(1).sdi_er_taxable := nvl(ln_sdi_er_taxable,0) ;
2229 state_bal(1).sdi_er_liability := nvl(ln_sdi_er_liability,0) ;
2230 state_bal(1).workers_comp_withheld :=
2231 nvl(ln_workers_comp_withheld,0) ;
2232 state_bal(1).workers_comp2_withheld:=
2233 nvl(ln_workers_comp2_withheld,0);
2234
2235 hr_utility.trace('New state summary for '||
2236 ln_person_id|| ' sit_gross = '||
2237 state_bal(1).sit_gross);
2238 END IF; /* Person Check */
2239 END IF; /* detail level check */
2240
2241 END LOOP;
2242 close c_state_balances;
2243 END IF; /* Jurisdiction Level Check */
2244
2245 /******************************************/
2246 /*** County Balances ***/
2247 /******************************************/
2248
2249 IF p_jurisdiction_level = '03' THEN
2250
2251 hr_utility.trace('----------------------------------------');
2252 hr_utility.trace('.............In COUNTY..................');
2253 hr_utility.trace('p_state_id = '||p_state_id);
2254 hr_utility.trace('p_county_id = '||p_county_id);
2255 hr_utility.trace('ln_action_number = '||ln_action_number);
2256 hr_utility.trace('jurisdiction = '||p_state_id||'-'||
2257 nvl(p_county_id,'%')||'-0000');
2258 hr_utility.trace('----------------------------------------');
2259
2260 OPEN c_county_balances(ln_action_number, p_state_id, p_county_id);
2261 LOOP
2262 hr_utility.trace('Fetching COUNTY records .................');
2263 fetch c_county_balances into lv_jurisdiction,
2264 lv_jurisdiction_name,
2265 ln_county_gross,
2266 ln_county_reduced_subject,
2267 ln_county_withheld,
2268 ln_head_tax_withheld,
2269 lv_non_resident_flag;
2270
2271 EXIT WHEN c_county_balances%NOTFOUND;
2272
2273 hr_utility.trace('Fetched records are .................');
2274 hr_utility.trace(' ');
2275 hr_utility.trace('lv_jurisdiction = '||lv_jurisdiction);
2276 hr_utility.trace('lv_jurisdiction_name = '||lv_jurisdiction_name);
2277 hr_utility.trace('ln_county_gross = '||ln_county_gross);
2278 hr_utility.trace('ln_county_reduced_subject = '||
2279 ln_county_reduced_subject);
2280 hr_utility.trace('ln_county_withheld = '||ln_county_withheld);
2281 hr_utility.trace('ln_head_tax_withheld = '||ln_head_tax_withheld);
2282 hr_utility.trace('----------------------------------------');
2283
2284 /******** County Balances by run *******/
2285
2286 IF p_detail_level = '01' THEN
2287
2288 hr_utility.trace('----------------------------------------');
2289 hr_utility.trace('County by run output....................');
2290 hr_utility.trace('ln_action_number = '||ln_action_number);
2291 hr_utility.trace('----------------------------------------');
2292
2293 emp_static_data(
2297 lv_employee_number,
2294 lv_last_name,
2295 lv_first_name,
2296 lv_middle_name,
2298 lv_assignment_number,
2299 lv_national_identifier,
2300 lv_address_line,
2301 lv_town_or_city,
2302 lv_county,
2303 lv_state,
2304 lv_postal_code,
2305 lv_country,
2306 lv_fed_ein,
2307 lv_org_name,
2308 lv_location_code,
2309 lv_action_type,
2310 ld_effective_date,
2311 p_output_file_type,
2312 lv_employee_data);
2313
2314 lv_data_row := lv_employee_data;
2315
2316 county_static_data( lv_gre_name,
2317 lv_jurisdiction,
2318 lv_jurisdiction_name,
2319 nvl(ln_county_gross,0),
2320 nvl(ln_county_reduced_subject,0),
2321 nvl(ln_county_withheld,0),
2322 nvl(ln_head_tax_withheld,0),
2323 lv_non_resident_flag,
2324 p_output_file_type,
2325 lv_county_data);
2326
2327 lv_data_row := lv_data_row||lv_county_data;
2328
2329 if p_output_file_type = 'HTML' then
2330 lv_data_row := '<tr>'||lv_data_row||'</tr>';
2331 end if;
2332 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
2333 END IF;
2334
2335 /******* County Balances by Summary (by Jurisdiction) ******/
2336
2337 IF p_detail_level = '02' THEN
2338
2339 hr_utility.trace('----------------------------------------');
2340 hr_utility.trace('County by Summary....................');
2341 hr_utility.trace('ln_action_number = '||ln_action_number);
2342 hr_utility.trace('ln_prev_person = '||ln_prev_person);
2343 hr_utility.trace('ln_person_id = '||ln_person_id);
2344 hr_utility.trace('----------------------------------------');
2345
2346 IF ln_prev_person = ln_person_id THEN
2347 emp_sum_static_data( lv_last_name,
2348 lv_first_name,
2349 lv_middle_name,
2350 lv_employee_number,
2351 lv_national_identifier,
2352 lv_fed_ein,
2353 p_output_file_type,
2354 lv_employee_data);
2355 lv_data_row := lv_employee_data;
2356
2357 IF p_county_id is null THEN
2358 lv_county_id := substr(lv_jurisdiction,4,3);
2359 ELSE
2360 lv_county_id := p_county_id;
2361 END IF;
2362
2363 hr_utility.trace('p_state_id = '||p_state_id);
2364 hr_utility.trace('lv_county_id = '||lv_county_id);
2365 hr_utility.trace('county_bal.count = '||county_bal.count);
2366
2367 lv_found := 'N';
2368
2369 IF county_bal.count > 0 THEN
2370 FOR k in county_bal.first..county_bal.last LOOP
2371 IF (county_bal(k).jurisdiction_code = lv_jurisdiction
2372 AND
2373 county_bal(k).tax_unit_id = ln_tax_unit_id ) THEN
2374 lv_found := 'Y';
2375 county_bal(k).county_gross := county_bal(k).county_gross +
2376 nvl(ln_county_gross,0);
2377 county_bal(k).county_reduced_subject :=
2378 nvl(ln_county_reduced_subject,0) +
2379 county_bal(k).county_reduced_subject;
2380 county_bal(k).county_withheld := nvl(ln_county_withheld,0) +
2381 county_bal(k).county_withheld;
2382 county_bal(k).county_head_tax_withheld :=
2383 nvl(ln_head_tax_withheld,0) +
2384 county_bal(k).county_head_tax_withheld;
2385 END IF;
2386 END LOOP;
2387 hr_utility.trace('lv_found := '||lv_found);
2388 IF lv_found = 'N' THEN
2389 ln_next_tab := county_bal.count +1;
2390 county_bal(ln_next_tab).tax_unit_id := ln_tax_unit_id;
2391 county_bal(ln_next_tab).gre_name := lv_gre_name;
2392 county_bal(ln_next_tab).jurisdiction_code :=
2393 lv_jurisdiction;
2394 county_bal(ln_next_tab).jurisdiction_name :=
2395 lv_jurisdiction_name;
2396 county_bal(ln_next_tab).county_gross :=
2397 nvl(ln_county_gross,0);
2398 county_bal(ln_next_tab).county_reduced_subject :=
2399 nvl(ln_county_reduced_subject,0);
2400 county_bal(ln_next_tab).county_withheld :=
2401 nvl(ln_county_withheld,0);
2405
2402 county_bal(ln_next_tab).county_head_tax_withheld :=
2403 nvl(ln_head_tax_withheld,0);
2404 END IF;
2406
2407 ELSE /** county_bal = 0, first fetch **/
2408 hr_utility.trace('COUNTY first fetch ..........');
2409 county_bal(1).tax_unit_id := ln_tax_unit_id;
2410 county_bal(1).gre_name := lv_gre_name;
2411 county_bal(1).jurisdiction_code :=
2412 lv_jurisdiction;
2413 county_bal(1).jurisdiction_name :=
2414 lv_jurisdiction_name;
2415 county_bal(1).county_gross :=
2416 nvl(ln_county_gross,0);
2417 county_bal(1).county_reduced_subject :=
2418 nvl(ln_county_reduced_subject,0);
2419 county_bal(1).county_withheld :=
2420 nvl(ln_county_withheld,0);
2421 county_bal(1).county_head_tax_withheld :=
2422 nvl(ln_head_tax_withheld,0);
2423
2424 END IF;
2425
2426 ELSE /** New Person Fetched, write out prev person **/
2427 hr_utility.trace('.......County ELSE New Person Fetched .......');
2428 hr_utility.trace('Write out the previous person................');
2429
2430 lv_prev_emp_data_row := lv_data_row;
2431
2432 IF county_bal.count>0 THEN
2433 FOR k in county_bal.first..county_bal.last LOOP
2434 county_static_data( county_bal(k).gre_name,
2435 county_bal(k).jurisdiction_code,
2436 county_bal(k).jurisdiction_name,
2437 county_bal(k).county_gross,
2438 county_bal(k).county_reduced_subject,
2439 county_bal(k).county_withheld,
2440 county_bal(k).county_head_tax_withheld,
2441 '', --Non resident flag
2442 p_output_file_type,
2443 lv_county_data_sum);
2444 lv_data_row := lv_prev_emp_data_row||lv_county_data_sum;
2445 if p_output_file_type = 'HTML' then
2446 lv_data_row := '<tr>'||lv_data_row||'</tr>';
2447 end if;
2448 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
2449 END LOOP;
2450 END IF; --county_bal.count
2451 hr_utility.trace('End of printing COUNTY sum.....');
2452
2453 /**** Now, build employee static header with the new fetched ***/
2454 /**** Person record ***/
2455
2456 emp_sum_static_data( lv_last_name,
2457 lv_first_name,
2458 lv_middle_name,
2459 lv_employee_number,
2460 lv_national_identifier,
2461 lv_fed_ein,
2462 p_output_file_type,
2463 lv_employee_data);
2464
2465 lv_data_row := lv_employee_data;
2466 /* Set the prev person to this fetched person */
2467 ln_prev_person := ln_person_id;
2468
2469 /*** Reset sum to currently Fetched Record ***/
2470
2471 hr_utility.trace('Resetting Summary for county................');
2472 IF county_bal.count > 0 THEN
2473 FOR k in county_bal.first..county_bal.last LOOP
2474 county_bal(k).tax_unit_id := -1;
2475 county_bal(k).gre_name := '';
2476 county_bal(k).jurisdiction_code:='';
2477 county_bal(k).jurisdiction_name:='';
2478 county_bal(k).county_gross:=0;
2479 county_bal(k).county_reduced_subject:=0;
2480 county_bal(k).county_withheld:=0;
2481 county_bal(k).county_head_tax_withheld:=0;
2482 END LOOP;
2483 END IF;
2484 county_bal.delete;
2485
2486 county_bal(1).tax_unit_id := ln_tax_unit_id;
2487 county_bal(1).gre_name := lv_gre_name;
2488 county_bal(1).jurisdiction_code := lv_jurisdiction;
2489 county_bal(1).jurisdiction_name := lv_jurisdiction_name;
2490 county_bal(1).county_gross := nvl(ln_county_gross,0);
2491 county_bal(1).county_reduced_subject := nvl(ln_county_reduced_subject,0);
2492 county_bal(1).county_withheld := nvl(ln_county_withheld,0);
2493 county_bal(1).county_head_tax_withheld := nvl(ln_head_tax_withheld,0);
2494
2495 hr_utility.trace('New County summary for '||
2496 ln_person_id||' county_gross = '||
2497 county_bal(1).county_gross);
2498
2499 END IF; /** Person Check **/
2500 END IF; /** Detail Level Check **/
2501
2502 END LOOP;
2503 close c_county_balances;
2504 END IF; /* Jurisdiction Level Check for County */
2505
2506 /*************************************************/
2507 /*** City Balances ***/
2508 /*************************************************/
2509
2510 IF p_jurisdiction_level = '04' THEN
2511
2515 hr_utility.trace('Jurisdiction Code = '||
2512 hr_utility.trace('-----------------------------------------');
2513 hr_utility.trace('.........In CITY balances ...............');
2514 hr_utility.trace('ln_action_number = '||ln_action_number);
2516 p_state_id||'-'||p_county_id||'-'||nvl(p_city_id,'%'));
2517 hr_utility.trace('-----------------------------------------');
2518
2519 open c_city_balances(ln_action_number,
2520 p_state_id,
2521 p_county_id,
2522 p_city_id);
2523 LOOP
2524 hr_utility.trace('Fetching CITY balance.............');
2525 fetch c_city_balances into lv_jurisdiction,
2526 lv_jurisdiction_name,
2527 ln_city_gross,
2528 ln_city_reduced_subject,
2529 ln_city_withheld,
2530 ln_head_tax_withheld,
2531 lv_non_resident_flag;
2532
2533 EXIT WHEN c_city_balances%NOTFOUND;
2534
2535 hr_utility.trace('-----------------------------------------');
2536 hr_utility.trace('Fetched CITY balance.............');
2537 hr_utility.trace('ln_city_gross = '||ln_city_gross);
2538 hr_utility.trace('ln_city_reduced_subject = '||ln_city_reduced_subject);
2539 hr_utility.trace('ln_city_withheld = '||ln_city_withheld);
2540 hr_utility.trace('ln_head_tax_withheld = '||ln_head_tax_withheld);
2541 hr_utility.trace('-----------------------------------------');
2542
2543 /****** City Balances By Run ********/
2544
2545 IF p_detail_level = '01' THEN
2546
2547 hr_utility.trace('-----------------------------------------');
2548 hr_utility.trace('City by Run output.......................');
2549 hr_utility.trace('ln_action_number = '||ln_action_number);
2550 hr_utility.trace('-----------------------------------------');
2551
2552 emp_static_data( lv_last_name,
2553 lv_first_name,
2554 lv_middle_name,
2555 lv_employee_number,
2556 lv_assignment_number,
2557 lv_national_identifier,
2558 lv_address_line,
2559 lv_town_or_city,
2560 lv_county,
2561 lv_state,
2562 lv_postal_code,
2563 lv_country,
2564 lv_fed_ein,
2565 lv_org_name,
2566 lv_location_code,
2567 lv_action_type,
2568 ld_effective_date,
2569 p_output_file_type,
2570 lv_employee_data);
2571
2572 lv_data_row := lv_employee_data;
2573
2574 city_static_data(lv_gre_name,
2575 lv_jurisdiction,
2576 lv_jurisdiction_name,
2577 nvl(ln_city_gross,0),
2578 nvl(ln_city_reduced_subject,0),
2579 nvl(ln_city_withheld,0),
2580 nvl(ln_head_tax_withheld,0),
2581 lv_non_resident_flag,
2582 p_output_file_type,
2583 lv_city_data);
2584
2585 lv_data_row := lv_data_row||lv_city_data;
2586
2587 if p_output_file_type = 'HTML' then
2588 lv_data_row := '<tr>'||lv_data_row||'</tr>';
2589 end if;
2590 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
2591 END IF;
2592
2593 /********* City Balances by Summary (by Jurisdiction) *******/
2594
2595 IF p_detail_level = '02' THEN
2596
2597 hr_utility.trace('----------------------------------------');
2598 hr_utility.trace('City by Summary....................');
2599 hr_utility.trace('ln_action_number = '||ln_action_number);
2600 hr_utility.trace('ln_prev_person = '||ln_prev_person);
2601 hr_utility.trace('ln_person_id = '||ln_person_id);
2602 hr_utility.trace('----------------------------------------');
2603
2604 IF ln_prev_person = ln_person_id THEN
2605 emp_sum_static_data( lv_last_name,
2606 lv_first_name,
2607 lv_middle_name,
2608 lv_employee_number,
2609 lv_national_identifier,
2610 lv_fed_ein,
2611 p_output_file_type,
2612 lv_employee_data);
2613
2614 lv_data_row := lv_employee_data;
2615
2616 IF p_city_id is null THEN
2617 lv_city_id := substr(lv_jurisdiction,8,4);
2618 ELSE
2619 lv_city_id := p_city_id;
2620 END IF;
2621 hr_utility.trace('city_bal.count = '||city_bal.count);
2622
2623 lv_found := 'N';
2624 IF city_bal.count > 0 THEN
2625 FOR k in city_bal.first..city_bal.last LOOP
2626 IF (city_bal(k).jurisdiction_code = lv_jurisdiction
2627 AND
2631 nvl(ln_city_gross,0);
2628 city_bal(k).tax_unit_id = ln_tax_unit_id) THEN
2629 lv_found := 'Y';
2630 city_bal(k).city_gross := city_bal(k).city_gross +
2632 city_bal(k).city_reduced_subject :=
2633 nvl(ln_city_reduced_subject,0) +
2634 city_bal(k).city_reduced_subject;
2635 city_bal(k).city_withheld := nvl(ln_city_withheld,0) +
2636 city_bal(k).city_withheld;
2637 city_bal(k).head_tax_withheld :=
2638 nvl(ln_head_tax_withheld,0) +
2639 city_bal(k).head_tax_withheld;
2640 END IF;
2641 END LOOP;
2642 hr_utility.trace('lv_found := '||lv_found);
2643 IF lv_found = 'N' THEN
2644 ln_next_tab := city_bal.count +1;
2645 city_bal(ln_next_tab).tax_unit_id := ln_tax_unit_id;
2646 city_bal(ln_next_tab).gre_name := lv_gre_name;
2647 city_bal(ln_next_tab).jurisdiction_code :=
2648 lv_jurisdiction;
2649 city_bal(ln_next_tab).jurisdiction_name :=
2650 lv_jurisdiction_name;
2651 city_bal(ln_next_tab).city_gross := nvl(ln_city_gross,0);
2652 city_bal(ln_next_tab).city_reduced_subject :=
2653 nvl(ln_city_reduced_subject,0);
2654 city_bal(ln_next_tab).city_withheld := nvl(ln_city_withheld,0);
2655 city_bal(ln_next_tab).head_tax_withheld :=
2656 nvl(ln_head_tax_withheld,0);
2657 END IF;
2658
2659 ELSE /** city_bal = 0, first fetch **/
2660 hr_utility.trace('CITY first fetch ..........');
2661 city_bal(1).tax_unit_id := ln_tax_unit_id;
2662 city_bal(1).gre_name := lv_gre_name;
2663 city_bal(1).jurisdiction_code := lv_jurisdiction;
2664 city_bal(1).jurisdiction_name := lv_jurisdiction_name;
2665 city_bal(1).city_gross := nvl(ln_city_gross,0);
2666 city_bal(1).city_reduced_subject := nvl(ln_city_reduced_subject,0);
2667 city_bal(1).city_withheld := nvl(ln_city_withheld,0);
2668 city_bal(1).head_tax_withheld := nvl(ln_head_tax_withheld,0);
2669 END IF;
2670 ELSE /*** New person fetched, write out prev person **/
2671 hr_utility.trace('.......City ELSE New Person Fetched .......');
2672 hr_utility.trace('Write out the previous person................');
2673
2674 lv_prev_emp_data_row := lv_data_row;
2675
2676 IF city_bal.count>0 THEN
2677 FOR k in city_bal.first..city_bal.last LOOP
2678 city_static_data( city_bal(k).gre_name,
2679 city_bal(k).jurisdiction_code,
2680 city_bal(k).jurisdiction_name,
2681 city_bal(k).city_gross,
2682 city_bal(k).city_reduced_subject,
2683 city_bal(k).city_withheld,
2684 city_bal(k).head_tax_withheld,
2685 '', --Non resident flag
2686 p_output_file_type,
2687 lv_city_data_sum);
2688 lv_data_row := lv_prev_emp_data_row||lv_city_data_sum;
2689 if p_output_file_type = 'HTML' then
2690 lv_data_row := '<tr>'||lv_data_row||'</tr>';
2691 end if;
2692 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
2693 END LOOP;
2694 END IF; -- city_bal.count
2695 hr_utility.trace('End of printing COUNTY sum.....');
2696
2697 /**** Now, build employee static header with the new fetched ***/
2698 /**** Person record ***/
2699
2700 emp_sum_static_data( lv_last_name,
2701 lv_first_name,
2702 lv_middle_name,
2703 lv_employee_number,
2704 lv_national_identifier,
2705 lv_fed_ein,
2706 p_output_file_type,
2707 lv_employee_data);
2708 lv_data_row := lv_employee_data;
2709 /* Set the prev person to this fetched person */
2710 ln_prev_person := ln_person_id;
2711
2712 /*** Reset sum to currently Fetched Record ***/
2713
2714 hr_utility.trace('Resetting Summary for city................');
2715 IF city_bal.count > 0 THEN
2716 FOR k in city_bal.first..city_bal.last LOOP
2717 city_bal(k).tax_unit_id := -1;
2718 city_bal(k).gre_name := '';
2719 city_bal(k).jurisdiction_code:='';
2720 city_bal(k).jurisdiction_name:='';
2721 city_bal(k).city_gross:=0;
2722 city_bal(k).city_reduced_subject:=0;
2723 city_bal(k).city_withheld:=0;
2724 city_bal(k).head_tax_withheld:=0;
2725 END LOOP;
2729 city_bal(1).tax_unit_id := ln_tax_unit_id;
2726 END IF;
2727 city_bal.delete;
2728
2730 city_bal(1).gre_name := lv_gre_name;
2731 city_bal(1).jurisdiction_code := lv_jurisdiction;
2732 city_bal(1).jurisdiction_name := lv_jurisdiction_name;
2733 city_bal(1).city_gross := nvl(ln_city_gross,0);
2734 city_bal(1).city_reduced_subject := nvl(ln_city_reduced_subject,0);
2735 city_bal(1).city_withheld := nvl(ln_city_withheld,0);
2736 city_bal(1).head_tax_withheld := nvl(ln_head_tax_withheld,0);
2737
2738 hr_utility.trace('New City summary for '||
2739 ln_person_id||' city_gross = '||
2740 city_bal(1).city_gross);
2741
2742 END IF; /** Person Check **/
2743 END IF; /** Detail Level Check **/
2744
2745 END LOOP;
2746 close c_city_balances;
2747 END IF; -- Jurisdiction Level Check for city
2748
2749 /*********************************************/
2750 /*** School District Balances ****/
2751 /*********************************************/
2752
2753 IF p_jurisdiction_level = '05' THEN
2754
2755 hr_utility.trace('-------------------------------------');
2756 hr_utility.trace('............In SCHOOL DIST...........');
2757 hr_utility.trace('p_city_id = '||p_city_id);
2758 hr_utility.trace('Jurisdiction Code = '||
2759 p_state_id||'-'||nvl(p_school_id,'%'));
2760
2761 open c_school_balances(ln_action_number,
2762 p_state_id,
2763 p_school_id);
2764 LOOP
2765
2766 hr_utility.trace('Fetching SCHOOL balance ................');
2767 fetch c_school_balances into lv_jurisdiction,
2768 lv_jurisdiction_name,
2769 ln_school_gross,
2770 ln_school_reduced_subject,
2771 ln_school_withheld;
2772
2773 EXIT WHEN c_school_balances%NOTFOUND;
2774
2775 hr_utility.trace('Fetched SCHOOL balance.............');
2776 hr_utility.trace('----------------------------------------');
2777 hr_utility.trace('ln_school_gross = '||ln_school_gross);
2778 hr_utility.trace('ln_school_withheld = '||ln_school_withheld);
2779 hr_utility.trace('----------------------------------------');
2780
2781 /******* School Balance By Run *****/
2782
2783 IF p_detail_level = '01' THEN
2784
2785 hr_utility.trace('-----------------------------------');
2786 hr_utility.trace('School Dist by Run output..........');
2787 hr_utility.trace('ln_action_number = '||ln_action_number);
2788 hr_utility.trace('-----------------------------------');
2789
2790 emp_static_data( lv_last_name,
2791 lv_first_name,
2792 lv_middle_name,
2793 lv_employee_number,
2794 lv_assignment_number,
2795 lv_national_identifier,
2796 lv_address_line,
2797 lv_town_or_city,
2798 lv_county,
2799 lv_state,
2800 lv_postal_code,
2801 lv_country,
2802 lv_fed_ein,
2803 lv_org_name,
2804 lv_location_code,
2805 lv_action_type,
2806 ld_effective_date,
2807 p_output_file_type,
2808 lv_employee_data);
2809
2810 lv_data_row := lv_employee_data;
2811
2812 school_static_data(lv_gre_name,
2813 lv_jurisdiction,
2814 lv_jurisdiction_name,
2815 nvl(ln_school_gross,0),
2816 nvl(ln_school_reduced_subject,0),
2817 nvl(ln_school_withheld,0),
2818 p_output_file_type,
2819 lv_school_data);
2820
2821 lv_data_row := lv_data_row||lv_school_data;
2822
2823 if p_output_file_type = 'HTML' then
2824 lv_data_row := '<tr>'||lv_data_row||'</tr>';
2825 end if;
2826 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
2827 END IF;
2828
2829 /***** School balance by Summary (by jurisdiction) *****/
2830
2831 IF p_detail_level = '02' THEN
2832 hr_utility.trace('----------------------------------------');
2833 hr_utility.trace('City by Summary....................');
2834 hr_utility.trace('ln_action_number = '||ln_action_number);
2835 hr_utility.trace('ln_prev_person = '||ln_prev_person);
2836 hr_utility.trace('ln_person_id = '||ln_person_id);
2837 hr_utility.trace('----------------------------------------');
2841 lv_middle_name,
2838 IF ln_prev_person = ln_person_id THEN
2839 emp_sum_static_data( lv_last_name,
2840 lv_first_name,
2842 lv_employee_number,
2843 lv_national_identifier,
2844 lv_fed_ein,
2845 p_output_file_type,
2846 lv_employee_data);
2847
2848 lv_data_row := lv_employee_data;
2849 hr_utility.trace('p_state_id = '||p_state_id);
2850 hr_utility.trace('p_county_id = '||p_county_id);
2851 hr_utility.trace('p_city_id = '||p_city_id);
2852 hr_utility.trace('school_bal.count = '||school_bal.count);
2853
2854 lv_found := 'N';
2855 IF school_bal.count > 0 THEN
2856 FOR k in school_bal.first..school_bal.last LOOP
2857 IF (school_bal(k).jurisdiction_code = lv_jurisdiction
2858 AND
2859 school_bal(k).tax_unit_id = ln_tax_unit_id) THEN
2860 lv_found := 'Y';
2861 school_bal(k).school_gross := nvl(ln_school_gross,0) +
2862 school_bal(k).school_gross;
2863 school_bal(k).school_reduced_subject := nvl(ln_school_reduced_subject,0)
2864 + school_bal(k).school_reduced_subject;
2865 school_bal(k).school_withheld := nvl(ln_school_withheld,0) +
2866 school_bal(k).school_withheld;
2867 END IF;
2868 END LOOP;
2869
2870 hr_utility.trace('lv_found := '||lv_found);
2871 IF lv_found = 'N' THEN
2872 ln_next_tab := school_bal.count +1;
2873 school_bal(ln_next_tab).tax_unit_id := ln_tax_unit_id;
2874 school_bal(ln_next_tab).gre_name := lv_gre_name;
2875 school_bal(ln_next_tab).jurisdiction_code := lv_jurisdiction;
2876 school_bal(ln_next_tab).jurisdiction_name :=
2877 lv_jurisdiction_name;
2878 school_bal(ln_next_tab).school_gross := nvl(ln_school_gross,0);
2879 school_bal(ln_next_tab).school_reduced_subject :=
2880 nvl(ln_school_reduced_subject,0);
2881 school_bal(ln_next_tab).school_withheld := nvl(ln_school_withheld,0);
2882 END IF;
2883
2884 ELSE /** city_bal = 0, first fetch **/
2885 hr_utility.trace('SCHOOL first fetch ..........');
2886 school_bal(1).tax_unit_id := ln_tax_unit_id;
2887 school_bal(1).gre_name := lv_gre_name;
2888 school_bal(1).jurisdiction_code := lv_jurisdiction;
2889 school_bal(1).jurisdiction_name := lv_jurisdiction_name;
2890 school_bal(1).school_gross := nvl(ln_school_gross,0);
2891 school_bal(1).school_reduced_subject :=
2892 nvl(ln_school_reduced_subject,0);
2893 school_bal(1).school_withheld := nvl(ln_school_withheld,0);
2894 END IF;
2895 ELSE /*** New person fetched, write out prev person **/
2896 hr_utility.trace('.......School ELSE New Person Fetched .......');
2897 hr_utility.trace('Write out the previous person................');
2898
2899 lv_prev_emp_data_row := lv_data_row;
2900
2901 IF school_bal.count>0 THEN
2902 FOR k in school_bal.first..school_bal.last LOOP
2903 school_static_data( school_bal(k).gre_name,
2904 school_bal(k).jurisdiction_code,
2905 school_bal(k).jurisdiction_name,
2906 school_bal(k).school_gross,
2907 school_bal(k).school_reduced_subject,
2908 school_bal(k).school_withheld,
2909 p_output_file_type,
2910 lv_school_data_sum);
2911 lv_data_row := lv_prev_emp_data_row||lv_school_data_sum;
2912 if p_output_file_type = 'HTML' then
2913 lv_data_row := '<tr>'||lv_data_row||'</tr>';
2914 end if;
2915 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
2916 END LOOP;
2917 END IF; -- school_bal.count
2918 hr_utility.trace('End of printing SCHOOL sum.....');
2919 /**** Now, build employee static header with the new fetched ***/
2920 /**** Person record ***/
2921
2922 emp_sum_static_data( lv_last_name,
2923 lv_first_name,
2924 lv_middle_name,
2925 lv_employee_number,
2926 lv_national_identifier,
2927 lv_fed_ein,
2928 p_output_file_type,
2929 lv_employee_data);
2930 lv_data_row := lv_employee_data;
2931 /* Set the prev person to this fetched person */
2932 ln_prev_person := ln_person_id;
2933 /*** Reset sum to currently Fetched Record ***/
2934
2938 school_bal(k).tax_unit_id := -1;
2935 hr_utility.trace('Resetting Summary for school................');
2936 IF school_bal.count > 0 THEN
2937 FOR k in school_bal.first..school_bal.last LOOP
2939 school_bal(k).gre_name := '';
2940 school_bal(k).jurisdiction_code:='';
2941 school_bal(k).jurisdiction_name:='';
2942 school_bal(k).school_gross := 0;
2943 school_bal(k).school_reduced_subject := 0;
2944 school_bal(k).school_withheld:=0;
2945 END LOOP;
2946 END IF;
2947 school_bal.delete;
2948
2949 school_bal(1).tax_unit_id := ln_tax_unit_id;
2950 school_bal(1).gre_name := lv_gre_name;
2954 school_bal(1).school_reduced_subject := nvl(ln_school_reduced_subject,0);
2951 school_bal(1).jurisdiction_code := lv_jurisdiction;
2952 school_bal(1).jurisdiction_name := lv_jurisdiction_name;
2953 school_bal(1).school_gross := nvl(ln_school_gross,0);
2955 school_bal(1).school_withheld := nvl(ln_school_withheld,0);
2956
2957 hr_utility.trace('New School summary for '||
2958 ln_person_id||' school_withheld = '||
2959 school_bal(1).school_withheld);
2960
2961 END IF; /** Person Check **/
2962 END IF; /** Detail Level Check **/
2963
2964 END LOOP;
2965 close c_school_balances;
2966 END IF; /** School Jurisdiction Level check */
2967 END IF; /**** End of Assignment Set ****/
2968
2969 /** Reset the previous person to the current person
2970 before fetching another person **/
2971 hr_utility.trace('Getting Next employee ........ ');
2972
2973 --ln_prev_person := ln_person_id; /* 2974109 fix */
2974
2975 END LOOP; /** End of Employee Loop */
2976 CLOSE c_employee;
2977
2978 /**** Write out the last fetched record ****/
2979
2980 IF (p_jurisdiction_level = '01' and p_detail_level = '02') then
2981 hr_utility.trace('Inside FINAL write out for FED');
2982
2983 lv_emp_data_row := lv_data_row;
2984 IF federal_bal.count > 0 THEN
2985 FOR k in federal_bal.first..federal_bal.last LOOP
2986 fed_static_data( federal_bal(k).gre_name,
2987 federal_bal(k).fit_gross ,
2988 federal_bal(k).fit_reduced_subject,
2989 federal_bal(k).fit_withheld,
2990 federal_bal(k).futa_taxable ,
2991 federal_bal(k).futa_liability ,
2992 federal_bal(k).ss_ee_taxable ,
2993 federal_bal(k).ss_ee_withheld,
2994 federal_bal(k).ss_er_taxable,
2995 federal_bal(k).ss_er_liability ,
2996 federal_bal(k).medicare_ee_taxable,
2997 federal_bal(k).medicare_ee_withheld,
2998 federal_bal(k).medicare_er_taxable ,
2999 federal_bal(k).medicare_er_liability,
3000 federal_bal(k).eic_advance,
3001 p_output_file_type,
3002 lv_federal_data_sum);
3003
3004 lv_data_row := lv_emp_data_row||lv_federal_data_sum;
3005
3006 if p_output_file_type = 'HTML' then
3007 lv_data_row := '<tr>'||lv_data_row||'</tr>';
3008 end if;
3009
3010 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
3011 END LOOP;
3012 END IF;
3013 END IF;
3014
3015 IF (p_jurisdiction_level = '02' and p_detail_level = '02') then
3016 hr_utility.trace('Inside FINAL write out for STATE');
3017
3018 lv_emp_data_row := lv_data_row;
3019
3020 IF state_bal.count > 0 THEN
3021 FOR i in state_bal.first..state_bal.last LOOP
3022 state_static_data(state_bal(i).gre_name
3023 ,state_bal(i).state_ein
3024 ,state_bal(i).jurisdiction_code
3025 ,state_bal(i).jurisdiction_name
3026 ,state_bal(i).sit_gross
3027 ,state_bal(i).sit_reduced_subject
3028 ,state_bal(i).sit_withheld
3029 ,state_bal(i).sui_ee_taxable
3030 ,state_bal(i).sui_ee_withheld
3031 ,state_bal(i).sui_er_taxable
3032 ,state_bal(i).sui_er_liability
3033 ,state_bal(i).sdi_ee_taxable
3034 ,state_bal(i).sdi_ee_withheld
3035 ,state_bal(i).sdi_er_taxable
3036 ,state_bal(i).sdi_er_liability
3037 ,state_bal(i).workers_comp_withheld
3038 ,state_bal(i).workers_comp2_withheld
3039 ,p_output_file_type
3040 ,lv_state_data_sum);
3041
3042 lv_data_row := lv_emp_data_row||lv_state_data_sum;
3043
3044 if p_output_file_type = 'HTML' then
3045 lv_data_row := '<tr>'||lv_data_row||'</tr>';
3046 end if;
3047 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
3048 END LOOP;
3049 END IF;
3050
3051 END IF;
3052
3053 IF (p_jurisdiction_level = '03' and p_detail_level = '02') then
3054 hr_utility.trace('Inside FINAL write out for COUNTY');
3055 lv_emp_data_row := lv_data_row;
3056
3057 IF county_bal.count>0 THEN
3058 FOR j in county_bal.first..county_bal.last LOOP
3059 county_static_data(county_bal(j).gre_name,
3060 county_bal(j).jurisdiction_code,
3061 county_bal(j).jurisdiction_name,
3062 county_bal(j).county_gross,
3063 county_bal(j).county_reduced_subject,
3064 county_bal(j).county_withheld,
3065 county_bal(j).county_head_tax_withheld,
3066 '',
3067 p_output_file_type,
3068 lv_county_data_sum);
3069
3073 lv_data_row := '<tr>'||lv_data_row||'</tr>';
3070 lv_data_row := lv_emp_data_row||lv_county_data_sum;
3071
3072 if p_output_file_type = 'HTML' then
3074 end if;
3075 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
3076 END LOOP;
3077 END IF; -- county_bal.count
3078 END IF;
3079
3080 IF (p_jurisdiction_level = '04' and p_detail_level = '02') then
3081 hr_utility.trace('Inside FINAL write out for CITY');
3082 lv_emp_data_row := lv_data_row;
3083
3084 IF city_bal.count>0 THEN
3085 FOR j in city_bal.first..city_bal.last LOOP
3086 city_static_data(city_bal(j).gre_name,
3087 city_bal(j).jurisdiction_code,
3088 city_bal(j).jurisdiction_name,
3089 city_bal(j).city_gross,
3090 city_bal(j).city_reduced_subject,
3091 city_bal(j).city_withheld,
3092 city_bal(j).head_tax_withheld,
3093 '',
3094 p_output_file_type,
3095 lv_city_data_sum);
3096
3097 lv_data_row := lv_emp_data_row||lv_city_data_sum;
3098
3099 if p_output_file_type = 'HTML' then
3100 lv_data_row := '<tr>'||lv_data_row||'</tr>';
3101 end if;
3102 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
3103 END LOOP;
3104 END IF; -- city_bal.count
3105 END IF;
3106
3107 IF (p_jurisdiction_level = '05' and p_detail_level = '02') then
3108 hr_utility.trace('Inside FINAL write out for SCHOOL');
3109 lv_emp_data_row := lv_data_row;
3110
3111 IF school_bal.count>0 THEN
3112 FOR j in school_bal.first..school_bal.last LOOP
3113 school_static_data(school_bal(j).gre_name,
3114 school_bal(j).jurisdiction_code,
3115 school_bal(j).jurisdiction_name,
3116 school_bal(j).school_gross,
3117 school_bal(j).school_reduced_subject,
3118 school_bal(j).school_withheld,
3119 p_output_file_type,
3120 lv_school_data_sum);
3121
3122 lv_data_row := lv_emp_data_row||lv_school_data_sum;
3123
3124 if p_output_file_type = 'HTML' then
3125 lv_data_row := '<tr>'||lv_data_row||'</tr>';
3126 end if;
3127 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lv_data_row);
3128 END LOOP;
3129 END IF; -- school_bal.count
3130 END IF;
3131
3132 if p_output_file_type ='HTML' then
3133 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
3134 end if;
3135 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
3136
3137 /**********************************************************
3138 ** Not Required as the output file type is HTML by default
3139 ***********************************************************/
3140
3141 if p_output_file_type ='HTML' then
3142 update fnd_concurrent_requests
3143 set output_file_type = 'HTML'
3144 where request_id = FND_GLOBAL.CONC_REQUEST_ID ;
3145
3146 commit;
3147 end if;
3148
3149 END ;
3150
3151 --BEGIN
3152 -- hr_utility.trace_on('Y', 'EMPPDTL');
3153 END pay_archiver_report_pkg;