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