[Home] [Help]
PACKAGE BODY: APPS.PAY_US_REPORTING_UTILS_PKG
Source
1 PACKAGE BODY pay_us_reporting_utils_pkg AS
2 /* $Header: pyusmref.pkb 120.10.12010000.2 2008/08/06 08:34:30 ubhat ship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 pay_us_reporting_utils_pkg
11
12 Purpose
13 The purpose of this package is to support the generation of magnetic tape W2
14 reports for US legilsative requirements incorporating magtape resilience
15 and the new end-of-year design. New Functions will support the Year end
16 reporting in MMREF format initially and will be extended to have more
17 format.
18
19 Notes
20
21 Parameters: The following 7 parameters are used in all the functions.
22
23 p_effective_date -
24 This parameter indicates the year for the function.
25 p_item_name -
26 This parameter indicates the name of the item
27 to be calculated.
28 'EE_ADDRESS' - Employee address
29 'ER_ADDRESS' - Employer Address
30 p_report_type -
31 This parameter will have the type of the report.
32 eg: 'W2'
33 p_format -
34 This parameter will have the format to be printed
35 on W2. eg:'MMREF'
36 p_record_name -
37 This parameter will have the particular
38 record name. eg: RA,RF,RE,RT,RSSUMM etc.
39 p_validate -
40 This parameter will check whether it wants to
41 validate the error condition or override the checking.
42 'N'- Override
43 'Y'- Check
44 p_exclude_from_output -
45 This out parameter gives the information on
46 whether the record has to be printed or not.
47 'Y'- Do not print.
48 'N'- Print.
49
50
51 Change List
52 -----------
53 Date Name Vers Bug No Description
54 ----------- -------- ------ ------- --------------------------
55 24-JUL-2001 fusman 40.0 created
56 06-Sep-2001 fusman 40.1 Made changes to the function get_item_data.
57 08-spe-2001 djoshi 40.2 Program modified by Mehul and Fouzia
58 for Value exceed allowable lenth in
59 RO and RU reocrd. Arcs done by dipen in
60 their absence.
61 10-Sep-2001 fusman 40.3 changed the output to upper case.
62 18-Sep-2001 fusman 40.4 Changed the message PAY_INVALID_ER_DATA and
63 the tokens.
64 19-Sep-2001 fusman 40.5 Changed the to_number conversion of the
65 p_tax_unit_id.
66 19-Sep-2001 fusman 40.6 Changed the date format.
67 20-Sep-2001 fusman 40.7 Added an exit statement for too many rows.
68 21-Sep-2001 fusman 40.8 Changed the cursor get_ss_limit to fetch as
69 dollar and cents.
70 28-Sep-2001 fusman 40.9 Removed commas from the names, checked
71 the terminated business indicator,
72 padded with blanks for spouse's social
73 security number in RO record.
74 02-Nov-2001 fusman 40.10 Added two new functions and created RS rec
75 03-Nov-2001 fusman 40.11 Added two dbi item to Get_territory_values
76 Added extra checking to SSN.
77 05-Nov-2001 fusman 40.12 Added a blank space if value not found for
78 dbis - A_CON_NATIONAL_IDENTIFIER and
79 - A_PER_MARITAL_STATUS
80 05-Nov-2001 fusman 40.13 Avoided NEG_CHECK for
81 - A_CON_NATIONAL_IDENTIFIER and
82 - A_PER_MARITAL_STATUS.
83 13-Nov-2001 fusman 40.14 2102205 Alabama Location address should be null
84 in RE record.
85 2105936 Suffix mismatch.
86 2109184
87 2108966 Incorrect State code in RS.
88 15-Nov-2001 40.15 Added a blank call for RS record.Spaces
89 has been removed from EIN.
90 16-Nov-2001 40.16 2110762 Removed hyphens and spaces on State
91 control number and state id number.
92 19-Nov-2001 40.17 2109279
93 2114444 RF incorrect for PA and CT.
94 20-nov-2001 40.18 Date for. changed for get_employer_address
95 20-nov-2001 40.19 2116807 RE rec for IN pos 221.
96 26-Nov-2001 115.10 Added dbdrv command.
97 27-Nov-2001 115.11 2124630 RE record for NC .
98 03-Dec-2001 115.12 2128995 County code not shown for IN in RS.
99 04-Dec-2001 115.13 2133985 Foreign State/province in foreign address.
100 05-Dec-2001 115.14 2132773 Agent EIN for PA,NC, AL,CT.
101 10-Dec-2001 115.16 2128533 Added checkfile stmt.
102 11-Dec-2001 115.17 2145032 CT resub indicator is not blanked.
103 20-Dec-2001 115.18 2159881 / removed in EIN.
104 2150138 Period,comma not removed in the names.
105 2157065 Non-global locations are not fetched.
106 21-Dec-2001 115.19 Modified checkfile to meet GSCC Standards.
107 21-Dec-2001 115.20 2146475 Marital status value for PR emp.
108 21-Dec-2001 115.21 Removed - from SSN.
109 02-Jan-2002 115.22 2159881 / removed in state emp a/c number.
110 03-Jan-2002 115.23 Removed / and - in RE record for PA emp num.
111 23-Jan-2002 115.24 Added format changes for OH RITA.
112 24-Jan-2002 115.25 Removed RA preparer code default value for
113 OH RITA.
114 24-Jan-2002 115.26 Removed the zero fill for the state code in
115 RS record for OH RITA.
116 24-Jan-2002 115.27 Changed the city to upper case.
117 04-Mar-2002 115.28 Added SQWL changes to RA,RE and RS records.
118 13-Mar-2002 115.29 2259849 Bug 2259849 and fix for SC,AZ,MN SQWL
119 on formatting issues replacing blanks
120 for zeroes and viceversa.
121 14-Mar-2002 115.32 Changed for UTF8-
122 14-Mar-2002 115.33 Fix for state code in RS record.
123 20-Mar-2002 115.34 2274381 RT record.
124 22-MAR-2002 115.36 Added count to SC_SQWL
125 25-MAR-2002 djoshi 115.37 2281801
126 25-MAR-2002 djoshi 115.38 Fix the SUI Account No for SC,MD,MO
127 05-MAY-2002 djoshi 115.39 2351936 Fix for SUI Account No. for OR.
128 16-MAY-2002 fusman 115.40 Fix for SUI Account No on all SQWL states and
129 for bug 2337613,2334393,2324869,2309772,2286329.
130 17-MAY-2002 fusman 115.41 Fixed LA_SQWL headers and passed SSN as output to print
131 in a03 file.
132 23-MAY-2002 fusman 115.42 EIN is not checked for SQWL.
133 24-MAY-2002 fusman 115.43 p_input_2 is send for GA_SQWL for position 18.
134 10-aug-2002 ppanda 115.45 2173795 For Puerto Rico Tax Jurisdiction Civil Status
135 value would be either M or S
136 2183859 MMREF-1 doesn't currently Pick up employees
137 with SSN beginning W/9
138 Validation for SSN modified to log a
139 warning for SSN beginning with 9
140 A warning is also logged for person having
141 no or blank SSN For above two cases person
142 record appears in .a03 and .mf file
143 2503639 JD context removed from Puerto Rico Balance
144
145 2198547 Pos:119-140. Delivery Address shoul not be
146 blanked out for NC. This is to revert the chenges
147 made to fix the bug 2124630.
148 11-Jun-2002 fusman 115.46 2409031 Removed '-' from EIN for LA_SQWL.
149 13-Jun-2002 fusman 115.47 Hyphens in EIN is removed first and then
150 9 digits is taken.
151
152 14-AUG-2002 sodhingr 115.48 Changed the format_record function to support
153 MMREF format for CA
154 22-AUG-2002 rpinjala 115.49 Changed the print_record_header for CA RTM report
155 and p_input_1 has a value of RTM passed from the
156 MMRF_SUBMITTER_DUMMY_SQWL
157 05-SEP-2002 sodhingr 115.50 2550189 Changed the formating for RST record to fill with
158 zero when null and right justify and zero fill for
159 all the numeric values.
160 29-SEP-2002 ahanda 115.55 Changed Get_Territory_Values to call JD specific
161 archive value for JD specific DBIs
162 23-OCT-2002 ppanda 115.56 2510920 Column heading which used to be 'Resub TLCN' now
163 its going to be 'Resub WFID'
164 2640074 For state of CT RF record changed
165 State Taxable Wages using position 12-27 instead of 13-28
166 SIT Withheld using the position 28-43 instead of 30-43
167 2627606 For state of MO, OH and PA column position 203-226
168 is now filled with zeros
169 2644092 For state of KS column position 3-4 State code
170 and 274-275 State code are now blank filled
171 2640052 For state of CT, on RT record SIT w/h used to be in
172 positions 26-40.Now it is in 25-39. On RT Pos 40-512
173 will be blank
174 2297587 On RW record pos 342-352 is blank filled and associated
175 validation is commented out
176 On RT record pos 220-334 is blank filled and associated
177 validation is commented out
178 2645739 On RW record local variables were formatted and used
179 for Flat record instead of input parameters
180 03-NOV-2002 2622709 Changed the 'RST' record for CA, to report no.of employees
181 from Pos 4-10
182 04-NOV-2002 ppanda 115.59 2297587 Column heading also to be blanked out
183 along with the values
184 10-NOV-2002 sodhingr 115.60 Changed get_item_data and format_record procedure for NY MMREF
185 11-NOV-2002 ppanda 115.61 2180659 Philadelphia, PA locality changes made as per MMREF specs
186 2182946 St. Louis, MO locality changes made as per MMREF specs
187 2420001 Daton, OH locality changes made as per MMREF Specs
188 14-NOV-2002 sodhingr 115.62 Changed format_record function for NY SQWL to print SIT wages
189 and tax withheld
190 15-NOV-2002 ppanda 115.63 2668099 On RS record pos 203-226 filled with zeros
191 2668250 On RS record for Arkansa pos 309-330 filled with blank
192 19-NOV-2002 ppanda 115.66 2673502 trace_on command commented
193 A change made for RO record when type is BLANK
194 RO Balnk record was shifting the RS record by one column right
195 because of an unwanted coma
196 20-NOV-2002 sodhingr 115.67 Changed format_record function for NY MMREF to use
197 p_input_23 instead of local variable r_input_23
198 which was printing blank for Quarter on
199 RE record pos 174-178.
200 Changed the function get_file_name to allow only numbers and
201 characters in the magnetic and report file name
202 24-NOV-2002 ppanda 115.68 2680070 On RA record for St Louis pos 95-512 is blank filled
203 On RS record Pos 341-351 filled with zeros
204 2680189 On RA record Pos 12-217 and 499-500 filled with Blank
205 On RO record Pos 275-351 and 363-384 filled with zeros
206 On RT record Pos 3-512 filled with blank
207 On RF record pos 8-16 filled with number of RW records and
208 pos 17-512 filled with blank
209 2682414 On RS record positions 338-349 right justified with wages and filled with zeros
210 positions 413-424 right justified with withheld and filled with zeros
211 2682446 On RS local record Tax Entity code is null filled which was causing wraping problem
212 for MF file.
213 02-Dec-2002 ppanda 115.70 Nocopy hint added to OUT and IN OUT parameters
214 06-Dec-2002 ppanda 115.71 2682428 On RA record posistion 499 is hard coded with 3 for Dayton Local mag
215 20-Jan-2003 ppanda 115.72 2742008 For Massachussets RS and RT record formatted for 2 new fields
216 2736928 For PuertoRico RA, RE, RO, RS and RT record formatted as new requirement
217 23-Jan-2003 ppanda 115.76 2767254 On RS record position 342-352, 364-374 and 386-407 filled with zeros
218 instead of blanks
219 11-Feb-2003 fusman 115.77 2789523 MN wants "X" in position 512 of rs record.
220 10-FEB-2003 sodhingr 115.77 2778338, 2788155 changed format_record and data_validation for FL_SQWL and VA_SQWL
221 14-FEB-2003 sodhingr 115.78 2802928 changed the format_record to print Agent id number for FL_SQWL
222 23-feb-2003 djoshi 115.79 djoshi Added the RITA CCA split
223 04-mar-2003 fusman 115.80 2426228 Changed the formats of phone numbers to match the doc.
224 10-MAR-2003 sodhingr 115.81 Changed the procedure data_validation to remove the formating for
225 Total tax due on FL's RT record.
226 19-MAR-2003 sodhingr 115.82 2856632 modified format_record funtion for 'RS' record Pos 5-9 , which is blank
227 for FL as apps doesn't support Unit code
228 Also, change the Agent Id number for 'FL' on 'RA' rec to print 8 chars
229 instead of 7 chars and changed 'RT' record to print 11 chars for tax
230 due.
231 modified data_validation procedure to restrict the length of tax due
232 to 11 chars instead of 10 chars
233 19-MAR-2003 sodhingr 115.83 2859806 Added company name for VA SQWL, RA rec pos38-94
234 21-MAR-2003 sodhingr 115.86 2856632 FL Unit code shouldn't be blank, we do support Unit code in apps.
235 08-APR-2003 sodhingr 115.87 2892148, 2892354 changed the function format_record to add CR/LF for FL SQWL
236 and for AZ SQWL added blanks instead of zeros from pos 274-337
237 15-APR-2003 fusman 115.88 2901849 NH SQWL State Account number is changed to be right
238 justified with leading zeros.
239 01-JUN-2003 fusman 115.89 2349576 NC SQWL MMREF Changes.
240 2787646 WA SQWL New format Changes.
241 2796947 NH's required field changes.
242 06-JUN-2003 fusman 115.90 NC SQWL delivery address blanked out.
243 18-JUN-2003 fusman 115.91 3011801 MN hours worked right justified and zero filled.
244 15-AUG-2003 ppanda 115.92 This package redesined for modularising complex functions
245 Modification resultted into few packages
246 07-Nov-2003 ppanda 2587381 Get_File_Name function changed to support Federal W-2c Magnetic Media
247 07-Nov-2003 fusman 115.95 3233249 Added balance feed check for performance issue.
248 07-Nov-2003 ppanda 2587381 W-2c report type added
249 01-MAR-2004 JGoswami 115.98 3334497 Modified data_validation added AK_SQWL check.
250 Added support to CUSTOM report format for AK_SQWL
251 28-AUG-2004 JGoswami 115.99 3830050 Modified data_validation added RSSUMM check for NV SQWL.
252 10-NOV-2004 meshah 115.100 added function get_ff_archive_value
253 15-NOV-2004 djoshi 115.101 changed l_jurisdiciton_code to varchar2(11) in
254 function get_ff_archive_value
255 07-Nov-2005 sudedas 115.102 4391218 Updated Format_Record function , updated with 2 new input params ,
256 pay_us_mmrf_w2_format_record.format_W2_RW_record
257 17-Feb-2006 sudedas 115.103 4425800 Added Functions Get_Employee_Count and Get_Total_Wages
258 30-May-2006 sackumar 115.104 5089997 Added Functions Get_Employee_Count_Monthwise and Get_Wages.
259 14-Jun-2006 sackumar 115.105 5089997 Modified get_wages to get the workers comp values..
260 17-Aug-2006 sudedas 115.106 5256745 Updated Format_Record Function with 2 Optional Input
261 Parameters p_input_43 and p_input_44 and
262 Updated pay_us_mmrf_w2_format_record.format_W2_RW_record.
263 22-Nov-2006 sudedas 115.107 5640748 Enhanced Get_Total_Wages Function.
264 01-Dec-2006 sudedas 115.108 Changed Cursors cur_employee_count and cur_summed_balance
265 in Function Get_Total_Wages (Added Jurisdiction Check)
266 25-Jun-2007 sjawid 115.109 5621099 Added function GET_SUI_WAGES.
267 07-Dec-2007 vmkulkar 115.110 6644795 Added call to format_W2_RV_record
268 6648007 get_item_data can now handle 'CS_PERSON' used
269 for capturing contact persons title.
270 18-Feb-2008 sjawid 115.111 6677736 Modified function GET_SUI_WAGES to ignore
271 -ve Wages employees for Florida SQWL xml format.
272 ============================================================================*/
273 -- Global Variable
274
275 g_number NUMBER;
276 l_return varchar2(100);
277 end_date date := to_date('31/12/4712','DD/MM/YYYY');
278
279 -- Used by Magnetic W2 (MMREF format).
280 /* ============================================================================ */
281 /* Function Name : calculate_balance
282 Purpose : Purpose of this function is is to provide calculation
283 of Derived balnces that are used in the formula
284 Error checking
285
286 Special Note :
287 */
288
289 FUNCTION calculate_balance(
290 p_effective_date IN varchar2,
291 p_balance_name IN varchar2,
292 p_report_type IN varchar2,
293 p_format IN varchar2,
294 p_report_qualifier IN varchar2,
295 p_record_name IN varchar2,
296 p_input_1 IN varchar2,
297 p_input_2 IN varchar2,
298 p_input_3 IN varchar2,
299 p_input_4 IN varchar2,
300 p_input_5 IN varchar2,
301 p_input_6 IN varchar2,
302 p_input_7 IN varchar2,
303 p_input_8 IN varchar2,
304 p_input_9 IN varchar2,
305 p_input_10 IN varchar2,
306 p_input_11 IN varchar2,
307 p_input_12 IN varchar2,
308 p_input_13 IN varchar2,
309 p_input_14 IN varchar2,
310 p_input_15 IN varchar2,
311 p_validate IN varchar2,
312 p_exclude_from_output out nocopy varchar2,
313 sp_out_1 out nocopy varchar2,
314 sp_out_2 out nocopy varchar2,
315 sp_out_3 out nocopy varchar2,
316 sp_out_4 out nocopy varchar2,
317 sp_out_5 out nocopy varchar2
318 ) RETURN number IS
319 /* LOCAL Varialbe Declaration */
320
321 l_calculated_value number ;
322 Begin
323 null;
324 return 0;
325
326 End calculate_balance;
327
328 /* Function Name : calculate_wages
329 Purpose : Purpose of this function is is to provide calcula
330 tion
331 of wages that are used in the formula
332 Error checking
333
334 Special Note :
335
336 */
337
338 FUNCTION calculate_wages(
339 p_effective_date IN varchar2,
340 p_wage_name IN varchar2,
341 p_report_type IN varchar2,
342 p_format IN varchar2,
343 p_report_qualifier IN varchar2,
344 p_record_name IN varchar2,
345 p_input_1 IN varchar2,
346 p_gross IN varchar2,
347 p_subject IN varchar2,
348 p_subject_nw IN varchar2,
349 p_pretax_redns IN varchar2,
350 p_taxable IN varchar2,
351 p_validate IN varchar2,
352 p_exclude_from_output out nocopy varchar2,
353 sp_exempt out nocopy varchar2,
354 sp_reduced_sub out nocopy varchar2,
355 sp_excess out nocopy varchar2,
356 sp_reduced_sub_wh out nocopy varchar2,
357 sp_out_1 out nocopy varchar2
358 )
359 RETURN number IS
360
361 return_wage number(10):=100;
362 l_error boolean;
363 l_message varchar2(300);
364 l_description varchar2(50);
365
366 BEGIN
367
368 null;
369
370 RETURN '0';
371
372 END;
373
374
375
376
377
378 /* --------------------------------------------------------------
379 Name : get_item_data
380
381 Purpose : Purpose of this function is to get live
382 data from the System.This can be replace
383 Call to live database items where error
384 checking is required.
385
386 Parameters :
387 p_effective_date -
388 This parameter indicates the year for the function.
389 p_item_name -
390 This parameter indicates the name of the item
391 to be calculated.
392 'EE_ADDRESS' - Employee address
393 'ER_ADDRESS' - Employer Address
394 'CR_ADDRESS' - Company's Address
395 'CR_PERSON' -Contact Persons details in the Submitter record.
396 'CS_PERSON' - Contact person details title. -vmkulkar
397 p_report_type -
398 This parameter will have the type of the report.
399 eg: 'W2'
400 p_format -
401 This parameter will have the format to be printed
402 on W2. eg:'MMREF'
403 p_record_name -
404 This parameter will have the particular
405 record name. eg: RA,RF,RE,RT,RSSUMM etc.
406 p_validate -
407 This parameter will check whether it wants to
408 validate the error condition or override the checking.
409 'N'- Override
410 'Y'- Check
411 p_exclude_from_output -
412 This parameter gives the information on
413 whether the record has to be printed or not.
414 'Y'- Do not print.
415 'N'- Print.
416 sp_out_1 -
417 This out parameter returns Location address.
418 sp_out_2 -
419 This out parameter returns Delivery address.
420 sp_out_3 -
421 This out parameter returns Town_or_city.
422 sp_out_4 -
423 This out parameter returns State abbreviation.
424 sp_out_5 -
425 This out parameter returns Postal_code.
426 sp_out_6 -
427 This out parameter returns zip code extension.
428 sp_out_7 -
429 This out parameter returns foreign state/province.
430 sp_out_8 -
431 This out parameter returns foreign postal_code.
432 sp_out_9 -
433 This out parameter returns Country_code.
434 sp_out_10 -
435 This parameter is returns the organization name or employee number.
436
437
438
439 Error checking
440
441 Special Note :
442
443
444 ---------------------------------------------------------------- */
445
446 FUNCTION get_item_data(
447 p_assignment_id number, --context
448 p_date_earned date, --context
449 p_tax_unit_id number, --context
450 p_effective_date IN varchar2,
451 p_item_name IN varchar2,
452 p_report_type IN varchar2,
453 p_format IN varchar2,
454 p_report_qualifier IN varchar2,
455 p_record_name IN varchar2,
456 p_input_1 IN varchar2,
457 p_input_2 IN varchar2,
458 p_input_3 IN varchar2,
459 p_input_4 IN varchar2,
460 p_input_5 IN varchar2,
461 p_validate IN varchar2,
462 p_exclude_from_output OUT nocopy varchar2,
463 sp_out_1 OUT nocopy varchar2,
464 sp_out_2 OUT nocopy varchar2,
465 sp_out_3 OUT nocopy varchar2,
466 sp_out_4 OUT nocopy varchar2,
467 sp_out_5 OUT nocopy varchar2,
468 sp_out_6 OUT nocopy varchar2,
469 sp_out_7 OUT nocopy varchar2,
470 sp_out_8 OUT nocopy varchar2,
471 sp_out_9 OUT nocopy varchar2,
472 sp_out_10 OUT nocopy varchar2
473 ) RETURN VARCHAR2 IS
474 -- Declaration of Local Variables
475 --
476 l_input_1 varchar2(50);
477 l_valid_address boolean;
478 c_item_name varchar2(40);
479 c_tax_unit_id hr_all_organization_units.organization_id%TYPE;
480
481 BEGIN
482 hr_utility.trace('Start of GET_ITEM_DATA ');
483 hr_utility.trace('Parameter Input Values ...');
484 hr_utility.trace('Ccontext value p_assignemnt_id = '
485 || to_char(p_assignment_id));
486 hr_utility.trace(' p_date_earned = '
487 ||p_date_earned);
488 hr_utility.trace(' p_tax_unit_id is = '
489 || to_char(p_tax_unit_id));
490 hr_utility.trace('p_effective_date is : '|| p_effective_date);
491 hr_utility.trace('p_item_name is : '|| p_item_name);
492 hr_utility.trace('p_report_type is : '|| p_report_type);
493 hr_utility.trace('p_format is : '|| p_format);
494 hr_utility.trace('p_report_qualifier is : '|| p_report_qualifier );
495 hr_utility.trace('p_record_name is : '|| p_record_name );
496 hr_utility.trace('p_input_1 is : '|| p_input_1 );
497 hr_utility.trace('p_input_2 is : '|| p_input_2 );
498 hr_utility.trace('p_input_3 is : '|| p_input_3 );
499 hr_utility.trace('p_input_4 is : '|| p_input_4 );
500 hr_utility.trace('p_input_5 is : '|| p_input_5 );
501 hr_utility.trace('p_validate is : '|| p_validate );
502
503 -- p_item_name parameter is checked to decide which
504 -- procedure to call for Address or contact info
505 --
506 IF p_item_name = 'CR_ADDRESS' THEN --p_item_name
507 l_input_1:=replace(p_input_1,' ');
508 IF l_input_1 IS NULL THEN
509 hr_utility.trace('p_input_1 is null');
510 c_item_name := NULL;
511 l_valid_address := FALSE;
512 ELSE -- l_input_1 IS NOT NULL THEN
513 hr_utility.trace('p_input_1 is not null');
514 c_tax_unit_id := to_number(p_input_1);
515 c_item_name := 'ER_ADDRESS';
516 END IF;
517 ELSIF p_item_name = 'ER_ADDRESS' THEN
518 hr_utility.trace(p_item_name);
519 c_tax_unit_id := p_tax_unit_id;
520 c_item_name := 'ER_ADDRESS';
521 hr_utility.trace('c_tax_unit_id = '||to_char(c_tax_unit_id));
522 ELSIF p_item_name = 'EE_ADDRESS' THEN
523 c_item_name:='EE_ADDRESS';
524 ELSIF p_item_name = 'CR_PERSON' THEN
525 c_item_name:='CR_PERSON';
526 l_valid_address:=FALSE;
527 ELSIF p_item_name = 'CS_PERSON' THEN
528 c_item_name:='CS_PERSON';
529 l_valid_address:=FALSE;
530 END IF; --p_item_name
531
532 IF c_item_name = 'CR_PERSON' THEN --c_item_name
533 --
534 -- Following function is called to fetch Contact Person info
535 --
536 hr_utility.trace('Calling get_cr_person_info');
537 sp_out_1 := pay_us_get_item_data_pkg.get_contact_person_info(
538 p_assignment_id,
539 p_date_earned,
540 p_tax_unit_id,
541 p_effective_date,
542 c_item_name,
543 p_report_type,
544 p_format,
545 p_report_qualifier,
546 p_record_name,
547 p_input_1,
548 p_validate,
549 p_exclude_from_output,
550 sp_out_1,
551 sp_out_2,
552 sp_out_3,
553 sp_out_4,
554 sp_out_5,
555 sp_out_6,
556 sp_out_7,
557 sp_out_8
558 );
559
560 ELSIF c_item_name = 'CS_PERSON' THEN
561 -- vmkulkar
562 -- Following function is called to fetch Contact Person Title
563 --
564 -- vmkulkar - Contact person Title should we displayed in the MD RV Record.
565 -- So using p_contact_prsn_email(out4) for passing TITLE back to the formula.
566 -- ITEM NAME used is 'CS_PERSON'
567
568 sp_out_1 := pay_us_get_item_data_pkg.get_contact_person_info(
569 p_assignment_id,
570 p_date_earned,
571 p_tax_unit_id,
572 p_effective_date,
573 c_item_name,
574 p_report_type,
575 p_format,
576 p_report_qualifier,
577 p_record_name,
578 p_input_1,
579 p_validate,
580 p_exclude_from_output,
581 sp_out_1,
582 sp_out_2,
583 sp_out_3,
584 sp_out_4,
585 sp_out_5,
586 sp_out_6,
587 sp_out_7,
588 sp_out_8
589 );
590
591 ELSIF c_item_name = 'EE_ADDRESS' THEN
592 --
593 -- Following function is called to fetch Employee Address
594 --
595 hr_utility.trace('EE_ADDRESS Calling get_mmref_employee_address');
596 sp_out_1 := pay_us_get_item_data_pkg.get_mmref_employee_address(
597 p_assignment_id,
598 p_date_earned,
599 p_tax_unit_id,
600 p_effective_date,
601 p_item_name,
602 p_report_type,
603 p_format,
604 p_report_qualifier,
605 p_record_name,
606 p_input_1,
607 p_input_2,
608 p_input_3,
609 p_input_4,
610 p_input_5,
611 p_validate,
612 p_exclude_from_output,
613 sp_out_1,
614 sp_out_2,
615 sp_out_3,
616 sp_out_4,
617 sp_out_5,
618 sp_out_6,
619 sp_out_7,
620 sp_out_8,
621 sp_out_9,
622 sp_out_10
623 );
624 hr_utility.trace('EE_ADDRESS get_mmref_employee_address completed sucessfully');
625 ELSIF c_item_name = 'ER_ADDRESS' THEN
626 --
627 -- Following function is called to fetch Employer Address
628 --
629 hr_utility.trace('ER_ADDRESS Calling get_mmref_employer_address');
630 sp_out_1 := pay_us_get_item_data_pkg.get_mmref_employer_address(
631 p_assignment_id,
632 p_date_earned,
633 p_tax_unit_id,
634 p_effective_date,
635 p_item_name,
636 p_report_type,
637 p_format,
638 p_report_qualifier,
639 p_record_name,
640 p_input_1,
641 p_input_2,
642 p_input_3,
643 p_input_4,
644 p_input_5,
645 p_validate,
646 p_exclude_from_output,
647 sp_out_1,
648 sp_out_2,
649 sp_out_3,
650 sp_out_4,
651 sp_out_5,
652 sp_out_6,
653 sp_out_7,
654 sp_out_8,
655 sp_out_9,
656 sp_out_10
657 );
658 hr_utility.trace('ER_ADDRESS get_mmref_employer_address completed sucessfully');
659 END IF; --c_item_name
660
661 IF p_exclude_from_output IS NULL THEN
662 p_exclude_from_output := 'N';
663 END IF;
664 --
665 -- Following checks are made to eliminate unwanted chanracters
666 -- from the output values
667
668 sp_out_1 := Character_check(sp_out_1);
669 sp_out_2 := Character_check(sp_out_2);
670 sp_out_3 := Character_check(sp_out_3);
671 sp_out_5 := Character_check(sp_out_5);
672 sp_out_6 := Character_check(sp_out_6);
673 sp_out_7 := Character_check(sp_out_7);
674 sp_out_8 := Character_check(sp_out_8);
675 sp_out_9 := Character_check(sp_out_9);
676 /* Email address should not under go Character checking.*/
677 IF p_item_name <> 'CR_PERSON' THEN
678 sp_out_4 := Character_check(sp_out_4);
679 END IF;
680
681 hr_utility.trace('Return Values of Get_Item_Data ..');
682 hr_utility.trace('Value of sp_out_1 = '||sp_out_1);
683 hr_utility.trace('Value of sp_out_2 = '||sp_out_2);
684 hr_utility.trace('Value of sp_out_3 = '||sp_out_3);
685 hr_utility.trace('Value of sp_out_4 = '||sp_out_4);
686 hr_utility.trace('Value of sp_out_5 = '||sp_out_5);
687 hr_utility.trace('Value of sp_out_6 = '||sp_out_6);
688 hr_utility.trace('Value of sp_out_7 = '||sp_out_7);
689 hr_utility.trace('Value of sp_out_8 = '||sp_out_8);
690 hr_utility.trace('Value of sp_out_9 = '||sp_out_9);
691 hr_utility.trace('Value of sp_out_10 = '||sp_out_10);
692
693 hr_utility.trace('Befor the final return. Length of the fields. ');
694 hr_utility.trace('length of sp_out_1 = '||to_char(length(sp_out_1)));
695 hr_utility.trace('length of sp_out_2 = '||to_char(length(sp_out_2)));
696 hr_utility.trace('length of sp_out_3 = '||to_char(length(sp_out_3)));
697 hr_utility.trace('length of sp_out_4 = '||to_char(length(sp_out_4)));
698 hr_utility.trace('length of sp_out_5 = '||to_char(length(sp_out_5)));
699 hr_utility.trace('length of sp_out_6 = '||to_char(length(sp_out_6)));
700 hr_utility.trace('length of sp_out_7 = '||to_char(length(sp_out_7)));
701 hr_utility.trace('length of sp_out_8 = '||to_char(length(sp_out_8)));
702 hr_utility.trace('length of sp_out_9 = '||to_char(length(sp_out_9)));
703 hr_utility.trace('length of sp_out_10 = '||to_char(length(sp_out_10)));
704
705 RETURN sp_out_1;
706 END; -- End of function GET_ITEM_DATA
707
708
709 /* -------------------------------------------------------------
710 Function Name : print_record_header
711 Purpose : Function will return the String for header
712 or title line for the Table or table heading
713 related to record for printing in audit files
714
715 Error checking
716
717 Special Note :
718
719 -------------------------------------------------------------- */
720
721 FUNCTION print_record_header(
722 p_effective_date IN varchar2,
723 p_report_type IN varchar2,
724 p_format IN varchar2,
725 p_report_qualifier IN varchar2,
726 p_record_name IN varchar2,
727 p_input_1 IN varchar2,
728 p_input_2 IN varchar2,
729 p_input_3 IN varchar2,
730 p_input_4 IN varchar2,
731 p_input_5 IN varchar2,
732 p_validate IN varchar2,
733 p_exclude_from_output OUT nocopy varchar2,
734 sp_out_1 OUT nocopy varchar2,
735 sp_out_2 OUT nocopy varchar2,
736 sp_out_3 OUT nocopy varchar2,
737 sp_out_4 OUT nocopy varchar2,
738 sp_out_5 OUT nocopy varchar2,
739 sp_out_6 OUT nocopy varchar2,
740 sp_out_7 OUT nocopy varchar2,
741 sp_out_8 OUT nocopy varchar2,
742 sp_out_9 OUT nocopy varchar2,
743 sp_out_10 OUT nocopy varchar2
744 ) RETURN VARCHAR2
745 IS
746
747 header_string varchar2(3000);
748 return_header_string varchar2(2000);
749
750 l_header_2 varchar2(900);
751 l_header_3 varchar2(900);
752 l_header_4 varchar2(900);
753 l_header_5 varchar2(900);
754 l_header_8 varchar2(900);
755 l_header_9 varchar2(900);
756
757 l_header_20 varchar2(900);
758 l_header_21 varchar2(900);
759 l_report_format varchar2(15);
760 l_header_29 varchar2(900);
761 l_header_34 varchar2(900);
762 l_name_header varchar2(900);
763 l_records varchar2(900);
764
765
766 BEGIN
767 l_report_format := p_input_1;
768 hr_utility.trace('Begin Checking ');
769 IF p_format = 'MMREF' THEN
770 IF p_record_name = 'RA' THEN
771 hr_utility.trace('RA record');
772 header_string :=
773 pay_us_mmrf_print_rec_header.mmrf_format_ra_record_header(
774 p_report_type,
775 p_format,
776 p_report_qualifier,
777 p_record_name
778 );
779 ELSIF p_record_name = 'RE' THEN --1028 length
780 hr_utility.trace('RE record');
781 header_string :=
782 pay_us_mmrf_print_rec_header.mmrf_format_re_record_header(
783 p_report_type,
784 p_format,
785 p_report_qualifier,
786 p_record_name
787 );
788 ELSIF p_record_name = 'RW' THEN
789 hr_utility.trace('RW record'); --2189 length
790 header_string :=
791 pay_us_mmrf_print_rec_header.mmrf_format_rw_record_header(
792 p_report_type,
793 p_format,
794 p_report_qualifier,
795 p_record_name
796 );
797 ELSIF p_record_name = 'RO' THEN
798 hr_utility.trace('RO record'); --1398 length
799 header_string:=
800 pay_us_mmrf_print_rec_header.mmrf_format_ro_record_header(
801 p_report_type,
802 p_format,
803 p_report_qualifier,
804 p_record_name
805 );
806 ELSIF p_record_name = 'RS' THEN
807 hr_utility.trace('RS record'); -- 1715 length
808 IF p_report_type = 'W2' THEN
809 header_string:=
810 pay_us_mmrf_print_rec_header.mmrf_format_w2_rs_rec_header(
811 p_report_type,
812 p_format,
813 p_report_qualifier,
814 p_record_name,
815 p_input_1
816 );
817 ELSIF p_report_type = 'SQWL' THEN
818 header_string:=
819 pay_us_mmrf_print_rec_header.mmrf_format_sqwl_rs_rec_header(
820 p_report_type,
821 p_format,
822 p_report_qualifier,
823 p_record_name,
824 p_input_1
825 );
826 END IF;
827 ELSIF p_record_name = 'RSSUMM' THEN
828 hr_utility.trace('RSSUMM record'); -- 1503 length
829 IF p_report_type = 'SQWL' THEN
830 header_string:=
831 pay_us_mmrf_print_rec_header.mmrf_format_rssumm_rec_header(
832 p_report_type,
833 p_format,
834 p_report_qualifier,
835 p_record_name
836 );
837 END IF;
838 ELSIF p_record_name = 'RT' THEN
839 hr_utility.trace('RT record'); -- 1503 length
840 header_string:=
841 pay_us_mmrf_print_rec_header.mmrf_format_rt_record_header(
842 p_report_type,
843 p_format,
844 p_report_qualifier,
845 p_record_name
846 );
847 ELSIF p_record_name = 'RU' THEN
848 hr_utility.trace('RU record'); -- 1295 length
849 header_string:=
850 pay_us_mmrf_print_rec_header.mmrf_format_rt_record_header(
851 p_report_type,
852 p_format,
853 p_report_qualifier,
854 p_record_name
855 );
856 ELSIF p_record_name = 'RF' THEN
857 hr_utility.trace('RF record');
858 header_string:=
859 pay_us_mmrf_print_rec_header.mmrf_format_rt_record_header(
860 p_report_type,
861 p_format,
862 p_report_qualifier,
863 p_record_name
864 );
865 END IF; /* p_record_name */
866 ELSIF p_format = 'CUSTOM' THEN
867
868 IF p_record_name = 'A' or
869 p_record_name = 'H' or
870 p_record_name = 'D'
871 THEN
872 hr_utility.trace(p_record_name||' record'); -- 117 length
873 IF p_report_type = 'SQWL' THEN
874 header_string:=
875 pay_us_mmrf_print_rec_header.mmrf_format_sqwl_rs_rec_header(
876 p_report_type,
877 p_format,
878 p_report_qualifier,
879 p_record_name,
880 p_input_1
881 );
882 END IF;
883 END IF;
884 END IF; /* p_format */
885 hr_utility.trace('splitting the header string ');
886 return_header_string := substr(header_string,1,200);
887 sp_out_1:=substr(header_string,201,200);
888 sp_out_2:=substr(header_string,401,200);
889 sp_out_3:=substr(header_string,601,200);
890 sp_out_4:=substr(header_string,801,200);
891 sp_out_5:=substr(header_string,1001,200);
892 sp_out_6:=substr(header_string,1201,200);
893 sp_out_7:=substr(header_string,1401,200);
894 sp_out_8:=substr(header_string,1601,200);
895 sp_out_9:=substr(header_string,1801,200);
896 sp_out_10:=substr(header_string,2001,300);
897 p_exclude_from_output:='N';
898 hr_utility.trace('return_header_string = '||return_header_string);
899 hr_utility.trace('sp_out_1:='||sp_out_1);
900 hr_utility.trace('sp_out_2:='||sp_out_2);
901 hr_utility.trace('sp_out_3:='||sp_out_3);
902 hr_utility.trace('sp_out_4:='||sp_out_4);
903 hr_utility.trace('sp_out_5:='||sp_out_5);
904 hr_utility.trace('sp_out_6:='||sp_out_6);
905 hr_utility.trace('sp_out_7:='||sp_out_7);
906 hr_utility.trace('sp_out_8:='||sp_out_8);
907 hr_utility.trace('sp_out_9:='||sp_out_9);
908 hr_utility.trace('sp_out_10:='||sp_out_10);
909
910 RETURN return_header_string;
911 END;
912
913
914
915 /*
916 Name : data_validation
917 : Function will validate data for any
918 database items or can change the data
919 based on the parameters. It should
920 be capable of having special data
921 validation and change function.
922
923 Error checking
924
925 Special Note :
926
927 */
928
929 FUNCTION data_validation(
930 p_effective_date IN varchar2,
931 p_report_type IN varchar2,
932 p_format IN varchar2,
933 p_report_qualifier IN varchar2,
934 p_record_name IN varchar2,
935 p_input_1 IN varchar2,
936 p_input_2 IN varchar2,
937 p_input_3 IN varchar2,
938 p_input_4 IN varchar2,
939 p_input_5 IN varchar2,
940 p_validate IN varchar2,
941 p_exclude_from_output OUT nocopy varchar2,
942 sp_out_1 OUT nocopy varchar2,
943 sp_out_2 OUT nocopy varchar2
944 )
945 return varchar2 IS
946 l_err boolean;
947 return_value varchar2(100);
948 l_length number(10);
949 l_message varchar2(2000);
950 l_number_length number(10);
951 l_description varchar2(50);
952 l_input_2 varchar2(100);
953 l_ssn varchar2(50);
954
955 TYPE special_numbers is record(
956 p_number_set varchar2(50));
957
958 special_number_record special_numbers;
959
960 TYPE ssn_special_number_rec is table
961 of special_number_record%type INDEX BY binary_integer;
962 ssn_check ssn_special_number_rec;
963 BEGIN
964 l_err := FALSE;
965 IF p_input_1 = 'EIN' THEN
966 IF p_report_type = 'SQWL' THEN -- SQWL EIN check
967 return_value :=
968 pay_us_report_data_validation.validate_SQWL_EIN( p_report_qualifier,
969 p_record_name,
970 p_input_2,
971 p_input_4,
972 p_validate,
973 l_err
974 );
975 ELSIF p_report_type IN ('W2','W2C') THEN -- W2 or W2c EIN check
976 return_value :=
977 pay_us_report_data_validation.validate_W2_EIN( p_report_qualifier,
978 p_record_name,
979 p_input_2,
980 p_input_4,
981 p_validate,
982 l_err
983 );
984 END IF; /* SQWL or W2 EIN check */
985 ELSIF p_input_1 = 'SSN' THEN
986 IF p_report_type = 'SQWL' THEN /*SQWL SSN check*/
987 return_value :=
988 pay_us_report_data_validation.validate_SQWL_SSN(p_effective_date,
989 p_report_type,
990 p_format,
991 p_report_qualifier,
992 p_record_name,
993 p_input_1,
994 p_input_2,
995 p_input_3,
996 p_input_4,
997 p_input_5,
998 p_validate,
999 l_err
1000 );
1001 ELSIF p_report_type IN ('W2','W2C') THEN /* W2 or W2c SSN check*/
1002 return_value :=
1003 pay_us_report_data_validation.validate_W2_SSN(p_effective_date,
1004 p_report_type,
1005 p_format,
1006 p_report_qualifier,
1007 p_record_name,
1008 p_input_1,
1009 p_input_2,
1010 p_input_3,
1011 p_input_4,
1012 p_input_5,
1013 p_validate,
1014 l_err
1015 );
1016 END IF; -- SQLWL or W2 SSN check
1017 ELSIF p_input_1 = 'NEG_CHECK' THEN
1018 l_input_2 := replace(p_input_2,' ');
1019 IF p_report_type IN ('W2','W2C') THEN
1020 l_length := pay_us_mmrf_w2_format_record.set_req_field_length(
1021 p_report_type,
1022 p_format,
1023 p_report_qualifier,
1024 p_record_name,
1025 p_input_1,
1026 p_input_2,
1027 p_input_3,
1028 p_input_4,
1029 p_input_5
1030 );
1031 ELSIF p_report_type = 'SQWL' THEN
1032 l_length := pay_us_mmrf_sqwl_format_record.set_req_field_length(
1033 p_report_type,
1034 p_format,
1035 p_report_qualifier,
1036 p_record_name,
1037 p_input_1,
1038 p_input_2,
1039 p_input_3,
1040 p_input_4,
1041 p_input_5
1042 );
1043 END IF;
1044 l_number_length:=length(l_input_2);
1045 IF l_number_length > l_length THEN
1046 IF (p_record_name in ('RO','RW','RS','RCW','RCO','RSSUMM') OR
1047 (p_record_name = 'D' AND p_report_qualifier = 'AK_SQWL')) THEN
1048 l_description:=' The number is bigger than the given length '
1049 ||l_length;
1050 pay_core_utils.push_message(801,'PAY_INVALID_EE_DATA','A');
1051 pay_core_utils.push_token('record_name',substr(p_record_name,1,50));
1052 pay_core_utils.push_token('name_or_number',substr(p_input_4,1,50));
1053 pay_core_utils.push_token('field_name',substr(p_input_3,1,50));
1054 pay_core_utils.push_token('description',substr(l_description,1,50));
1055 /* Error in RW record for employee 1234 in Medicare wages.
1056 The number is bigger than the given length 11 */
1057 hr_utility.trace(p_input_3 ||' '||l_description);
1058 END IF;
1059 l_err:=TRUE;
1060 END IF;
1061
1062 hr_utility.trace('l_input_2 = '|| l_input_2);
1063 hr_utility.trace('l_input_2 in number = '|| to_char(to_number(l_input_2)));
1064 hr_utility.trace('l_length = '|| l_length);
1065 hr_utility.trace('After Number length check');
1066 IF to_number(l_input_2) < 0 THEN
1067 return_value:='-'||lpad(nvl(replace(l_input_2,'-'),0),l_length-1,0);
1068 IF( p_record_name in ('RO','RW','RS','RCW','RCO','D','RSSUMM') OR
1069 (p_record_name = 'D' AND p_report_qualifier = 'AK_SQWL')) THEN
1070 l_description:=' The value is negative '||substr(l_input_2,1,l_length);
1071 pay_core_utils.push_message(801,'PAY_INVALID_EE_DATA','A');
1072 pay_core_utils.push_token('record_name',substr(p_record_name,1,50));
1073 pay_core_utils.push_token('name_or_number',substr(p_input_4,1,50));
1074 pay_core_utils.push_token('field_name',substr(p_input_3,1,50));
1075 pay_core_utils.push_token('description',substr(l_description,1,50));
1076
1077 /* sample mesg*/
1078 /* Error in RW record for employee 1234 in Medicare wages.
1079 The value is negative -2345 */
1080 hr_utility.trace(l_description);
1081 END IF;
1082 l_err:=TRUE;
1083 ELSE
1084 hr_utility.trace('l_input_2 = '|| l_input_2);
1085 hr_utility.trace('l_length = '|| l_length);
1086 return_value:=lpad(nvl(replace(l_input_2,'-'),0),l_length,0);
1087 hr_utility.trace('return_value = '|| return_value);
1088 END IF; /* to_number(l_input_2) */
1089 END IF; /* p_input_1 */
1090
1091 hr_utility.trace('Before returning the value = '||return_value);
1092 hr_utility.trace('p_validate = '||p_validate);
1093
1094 IF p_validate= 'Y' THEN
1095 IF l_err THEN
1096 p_exclude_from_output:='Y';
1097 ELSE
1098 p_exclude_from_output:='N';
1099 END IF;
1100 END IF;
1101 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1102 RETURN return_value;
1103
1104 END DATA_VALIDATION;
1105
1106
1107 /* ---------------------------------------------------------------
1108 Function Name : format_record
1109 Purpose : Function will return formating of the record
1110 there will be one function per record
1111 Error checking
1112
1113 Special Note :
1114
1115 parameters : p_input_1
1116 This parameter will have the value for
1117 transfer employee for
1118 'FED' in jurisdiction
1119 'W2' in p_report_type
1120
1121
1122
1123 -------------------------------------------------------------------- */
1124
1125 --
1126
1127 FUNCTION format_record(
1128 p_effective_date IN varchar2,
1129 p_report_type IN varchar2,
1130 p_format IN varchar2,
1131 p_report_qualifier IN varchar2,
1132 p_record_name IN varchar2,
1133 p_input_1 IN varchar2,
1134 p_input_2 IN varchar2,
1135 p_input_3 IN varchar2,
1136 p_input_4 IN varchar2,
1137 p_input_5 IN varchar2,
1138 p_input_6 IN varchar2,
1139 p_input_7 IN varchar2,
1140 p_input_8 IN varchar2,
1141 p_input_9 IN varchar2,
1142 p_input_10 IN varchar2,
1143 p_input_11 IN varchar2,
1144 p_input_12 IN varchar2,
1145 p_input_13 IN varchar2,
1146 p_input_14 IN varchar2,
1147 p_input_15 IN varchar2,
1148 p_input_16 IN varchar2,
1149 p_input_17 IN varchar2,
1150 p_input_18 IN varchar2,
1151 p_input_19 IN varchar2,
1152 p_input_20 IN varchar2,
1153 p_input_21 IN varchar2,
1154 p_input_22 IN varchar2,
1155 p_input_23 IN varchar2,
1156 p_input_24 IN varchar2,
1157 p_input_25 IN varchar2,
1158 p_input_26 IN varchar2,
1159 p_input_27 IN varchar2,
1160 p_input_28 IN varchar2,
1161 p_input_29 IN varchar2,
1162 p_input_30 IN varchar2,
1163 p_input_31 IN varchar2,
1164 p_input_32 IN varchar2,
1165 p_input_33 IN varchar2,
1166 p_input_34 IN varchar2,
1167 p_input_35 IN varchar2,
1168 p_input_36 IN varchar2,
1169 p_input_37 IN varchar2,
1170 p_input_38 IN varchar2,
1171 p_input_39 IN varchar2,
1172 p_input_40 IN varchar2,
1173 p_validate IN varchar2,
1174 p_exclude_from_output OUT nocopy varchar2,
1175 sp_out_1 OUT nocopy varchar2,
1176 sp_out_2 OUT nocopy varchar2,
1177 sp_out_3 OUT nocopy varchar2,
1178 sp_out_4 OUT nocopy varchar2,
1179 sp_out_5 OUT nocopy varchar2,
1180 ret_str_len OUT nocopy number,
1181 p_input_41 IN varchar2 default null,
1182 p_input_42 IN varchar2 default null,
1183 p_input_43 IN varchar2 default null,
1184 p_input_44 IN varchar2 default null
1185 ) RETURN VARCHAR2
1186 IS
1187
1188 return_value varchar2(32767);
1189 l_exclude_from_output_chk boolean;
1190 main_return_string varchar2(300);
1191 BEGIN
1192
1193 hr_utility.trace(' p_report_qualifier = '||p_report_qualifier);
1194 hr_utility.trace(' p_record_name = ' ||p_record_name);
1195 hr_utility.trace(' p_input_2 = ' ||p_input_2);
1196 hr_utility.trace(' p_input_2 = ' ||p_input_2);
1197 hr_utility.trace(' p_input_3 = ' ||p_input_3);
1198 hr_utility.trace(' p_input_4 = ' ||p_input_4);
1199 hr_utility.trace(' p_input_5 = ' ||p_input_5);
1200 hr_utility.trace(' p_input_6 = ' ||p_input_6);
1201 hr_utility.trace(' p_input_7 = ' ||p_input_7);
1202 hr_utility.trace(' p_input_8 = ' ||p_input_8);
1203 hr_utility.trace(' p_input_9 = ' ||p_input_9);
1204 hr_utility.trace(' p_input_10 = ' ||p_input_10);
1205 hr_utility.trace(' p_input_11 = ' ||p_input_11);
1206 hr_utility.trace(' p_input_12 = ' ||p_input_12);
1207 hr_utility.trace(' p_input_13 = ' ||p_input_13);
1208 hr_utility.trace(' p_input_14 = ' ||p_input_14);
1209 hr_utility.trace(' p_input_15 = ' ||p_input_15);
1210 hr_utility.trace(' p_input_16 = ' ||p_input_16);
1211 hr_utility.trace(' p_input_17 = ' ||p_input_17);
1212 hr_utility.trace(' p_input_18 = ' ||p_input_18);
1213 hr_utility.trace(' p_input_19 = ' ||p_input_19);
1214 hr_utility.trace(' p_input_20 = ' ||p_input_20);
1215 hr_utility.trace(' p_input_21 = ' ||p_input_21);
1216 hr_utility.trace(' p_input_22 = ' ||p_input_22);
1217 hr_utility.trace(' p_input_23 = ' ||p_input_23);
1218 hr_utility.trace(' p_input_24 = ' ||p_input_24);
1219 hr_utility.trace(' p_input_25 = ' ||p_input_25);
1220 hr_utility.trace(' p_input_26 = ' ||p_input_26);
1221 hr_utility.trace(' p_input_27 = ' ||p_input_27);
1222 hr_utility.trace(' p_input_28 = ' ||p_input_28);
1223 hr_utility.trace(' p_input_29 = ' ||p_input_29);
1224 hr_utility.trace(' p_input_30 = ' ||p_input_30);
1225 hr_utility.trace(' p_input_31 = ' ||p_input_31);
1226 hr_utility.trace(' p_input_32 = ' ||p_input_32);
1227 hr_utility.trace(' p_input_33 = ' ||p_input_33);
1228 hr_utility.trace(' p_input_34 = ' ||p_input_34);
1229 hr_utility.trace(' p_input_35 = ' ||p_input_35);
1230 hr_utility.trace(' p_input_36 = ' ||p_input_36);
1231 hr_utility.trace(' p_input_37 = ' ||p_input_37);
1232 hr_utility.trace(' p_input_38 = ' ||p_input_38);
1233 hr_utility.trace(' p_input_39 = ' ||p_input_39);
1234 hr_utility.trace(' p_input_40 = ' ||p_input_40);
1235 hr_utility.trace(' p_input_41 = ' ||p_input_41);
1236 hr_utility.trace(' p_input_42 = ' ||p_input_42);
1237 hr_utility.trace(' p_input_43 = ' ||p_input_43);
1238 hr_utility.trace(' p_input_44 = ' ||p_input_44);
1239
1240 IF p_format = 'MMREF' THEN -- p_format
1241 --{
1242 IF (p_report_type = 'W2') THEN
1243 --{
1244 IF p_record_name = 'RA' THEN -- p_record_name
1245 return_value := pay_us_mmrf_w2_format_record.format_W2_RA_record(
1246 p_effective_date,
1247 p_report_type,
1248 p_format,
1249 p_report_qualifier,
1250 p_record_name,
1251 p_input_1,
1252 p_input_2,
1253 p_input_3,
1254 p_input_4,
1255 p_input_5,
1256 p_input_6,
1257 p_input_7,
1258 p_input_8,
1259 p_input_9 ,
1260 p_input_10,
1261 p_input_11,
1262 p_input_12,
1263 p_input_13,
1264 p_input_14,
1265 p_input_15,
1266 p_input_16,
1267 p_input_17,
1268 p_input_18,
1269 p_input_19,
1270 p_input_20,
1271 p_input_21,
1272 p_input_22,
1273 p_input_23,
1274 p_input_24,
1275 p_input_25,
1276 p_input_26,
1277 p_input_27,
1278 p_input_28,
1279 p_input_29,
1280 p_input_30,
1281 p_input_31,
1282 p_input_32,
1283 p_input_33,
1284 p_input_34,
1285 p_input_35,
1286 p_input_36,
1287 p_input_37,
1288 p_input_38,
1289 p_input_39,
1290 p_input_40,
1291 p_validate,
1292 p_exclude_from_output,
1293 sp_out_1,
1294 sp_out_2,
1295 sp_out_3,
1296 sp_out_4,
1297 sp_out_5,
1298 ret_str_len,
1299 l_exclude_from_output_chk
1300 );
1301 ELSIF p_record_name = 'RE' THEN
1302 return_value :=
1303 pay_us_mmrf_w2_format_record.format_W2_RE_record(
1304 p_effective_date,
1305 p_report_type,
1306 p_format,
1307 p_report_qualifier,
1308 p_record_name,
1309 p_input_1,
1310 p_input_2,
1311 p_input_3,
1312 p_input_4,
1313 p_input_5,
1314 p_input_6,
1315 p_input_7,
1316 p_input_8,
1317 p_input_9 ,
1318 p_input_10,
1319 p_input_11,
1320 p_input_12,
1321 p_input_13,
1322 p_input_14,
1323 p_input_15,
1324 p_input_16,
1325 p_input_17,
1326 p_input_18,
1327 p_input_19,
1328 p_input_20,
1329 p_input_21,
1330 p_input_22,
1331 p_input_23,
1332 p_input_24,
1333 p_input_25,
1334 p_input_26,
1335 p_input_27,
1336 p_input_28,
1337 p_input_29,
1338 p_input_30,
1339 p_input_31,
1340 p_input_32,
1341 p_input_33,
1342 p_input_34,
1343 p_input_35,
1344 p_input_36,
1345 p_input_37,
1346 p_input_38,
1347 p_input_39,
1348 p_input_40,
1349 p_validate,
1350 p_exclude_from_output,
1351 sp_out_1,
1352 sp_out_2,
1353 sp_out_3,
1354 sp_out_4,
1355 sp_out_5,
1356 ret_str_len,
1357 l_exclude_from_output_chk
1358 );
1359 ELSIF p_record_name = 'RW' THEN
1360 return_value :=
1361 pay_us_mmrf_w2_format_record.format_W2_RW_record(
1362 p_effective_date,
1363 p_report_type,
1364 p_format,
1365 p_report_qualifier,
1366 p_record_name,
1367 p_input_1,
1368 p_input_2,
1369 p_input_3,
1370 p_input_4,
1371 p_input_5,
1372 p_input_6,
1373 p_input_7,
1374 p_input_8,
1375 p_input_9 ,
1376 p_input_10,
1377 p_input_11,
1378 p_input_12,
1379 p_input_13,
1380 p_input_14,
1381 p_input_15,
1382 p_input_16,
1383 p_input_17,
1384 p_input_18,
1385 p_input_19,
1386 p_input_20,
1387 p_input_21,
1388 p_input_22,
1389 p_input_23,
1390 p_input_24,
1391 p_input_25,
1392 p_input_26,
1393 p_input_27,
1394 p_input_28,
1395 p_input_29,
1396 p_input_30,
1397 p_input_31,
1398 p_input_32,
1399 p_input_33,
1400 p_input_34,
1401 p_input_35,
1402 p_input_36,
1403 p_input_37,
1404 p_input_38,
1405 p_input_39,
1406 p_input_40,
1407 p_validate,
1408 p_exclude_from_output,
1409 sp_out_1,
1410 sp_out_2,
1411 sp_out_3,
1412 sp_out_4,
1413 sp_out_5,
1414 ret_str_len,
1415 l_exclude_from_output_chk,
1416 p_input_41,
1417 p_input_42,
1418 p_input_43,
1419 p_input_44
1420 );
1421
1422 ELSIF p_record_name = 'RO' THEN
1423 return_value :=
1424 pay_us_mmrf_w2_format_record.format_W2_RO_record(
1425 p_effective_date,
1426 p_report_type,
1427 p_format,
1428 p_report_qualifier,
1429 p_record_name,
1430 p_input_1,
1431 p_input_2,
1432 p_input_3,
1433 p_input_4,
1434 p_input_5,
1435 p_input_6,
1436 p_input_7,
1437 p_input_8,
1438 p_input_9 ,
1439 p_input_10,
1440 p_input_11,
1441 p_input_12,
1442 p_input_13,
1443 p_input_14,
1444 p_input_15,
1445 p_input_16,
1446 p_input_17,
1447 p_input_18,
1448 p_input_19,
1449 p_input_20,
1450 p_input_21,
1451 p_input_22,
1452 p_input_23,
1453 p_input_24,
1454 p_input_25,
1455 p_input_26,
1456 p_input_27,
1457 p_input_28,
1458 p_input_29,
1459 p_input_30,
1460 p_input_31,
1461 p_input_32,
1462 p_input_33,
1463 p_input_34,
1464 p_input_35,
1465 p_input_36,
1466 p_input_37,
1467 p_input_38,
1468 p_input_39,
1469 p_input_40,
1470 p_validate,
1471 p_exclude_from_output,
1472 sp_out_1,
1473 sp_out_2,
1474 sp_out_3,
1475 sp_out_4,
1476 sp_out_5,
1477 ret_str_len,
1478 l_exclude_from_output_chk
1479 );
1480
1481 ELSIF p_record_name = 'RS' THEN
1482 return_value :=
1483 pay_us_mmrf_w2_format_record.format_W2_RS_record(
1484 p_effective_date,
1485 p_report_type,
1486 p_format,
1487 p_report_qualifier,
1488 p_record_name,
1489 p_input_1,
1490 p_input_2,
1491 p_input_3,
1492 p_input_4,
1493 p_input_5,
1494 p_input_6,
1495 p_input_7,
1496 p_input_8,
1497 p_input_9 ,
1498 p_input_10,
1499 p_input_11,
1500 p_input_12,
1501 p_input_13,
1502 p_input_14,
1503 p_input_15,
1504 p_input_16,
1505 p_input_17,
1506 p_input_18,
1507 p_input_19,
1508 p_input_20,
1509 p_input_21,
1510 p_input_22,
1511 p_input_23,
1512 p_input_24,
1513 p_input_25,
1514 p_input_26,
1515 p_input_27,
1516 p_input_28,
1517 p_input_29,
1518 p_input_30,
1519 p_input_31,
1520 p_input_32,
1521 p_input_33,
1522 p_input_34,
1523 p_input_35,
1524 p_input_36,
1525 p_input_37,
1526 p_input_38,
1527 p_input_39,
1528 p_input_40,
1529 p_validate,
1530 p_exclude_from_output,
1531 sp_out_1,
1532 sp_out_2,
1533 sp_out_3,
1534 sp_out_4,
1535 sp_out_5,
1536 ret_str_len,
1537 l_exclude_from_output_chk
1538 );
1539 ELSIF p_record_name = 'RT' THEN
1540 return_value :=
1541 pay_us_mmrf_w2_format_record.format_W2_RT_record(
1542 p_effective_date,
1543 p_report_type,
1544 p_format,
1545 p_report_qualifier,
1546 p_record_name,
1547 p_input_1,
1548 p_input_2,
1549 p_input_3,
1550 p_input_4,
1551 p_input_5,
1552 p_input_6,
1553 p_input_7,
1554 p_input_8,
1555 p_input_9 ,
1556 p_input_10,
1557 p_input_11,
1558 p_input_12,
1559 p_input_13,
1560 p_input_14,
1561 p_input_15,
1562 p_input_16,
1563 p_input_17,
1564 p_input_18,
1565 p_input_19,
1566 p_input_20,
1567 p_input_21,
1568 p_input_22,
1569 p_input_23,
1570 p_input_24,
1571 p_input_25,
1572 p_input_26,
1573 p_input_27,
1574 p_input_28,
1575 p_input_29,
1576 p_input_30,
1577 p_input_31,
1578 p_input_32,
1579 p_input_33,
1580 p_input_34,
1581 p_input_35,
1582 p_input_36,
1583 p_input_37,
1584 p_input_38,
1585 p_input_39,
1586 p_input_40,
1587 p_validate,
1588 p_exclude_from_output,
1589 sp_out_1,
1590 sp_out_2,
1591 sp_out_3,
1592 sp_out_4,
1593 sp_out_5,
1594 ret_str_len,
1595 l_exclude_from_output_chk
1596 );
1597 ELSIF p_record_name = 'RU' THEN
1598 return_value :=
1599 pay_us_mmrf_w2_format_record.format_W2_RU_record(
1600 p_effective_date,
1601 p_report_type,
1602 p_format,
1603 p_report_qualifier,
1604 p_record_name,
1605 p_input_1,
1606 p_input_2,
1607 p_input_3,
1608 p_input_4,
1609 p_input_5,
1610 p_input_6,
1611 p_input_7,
1612 p_input_8,
1613 p_input_9 ,
1614 p_input_10,
1615 p_input_11,
1616 p_input_12,
1617 p_input_13,
1618 p_input_14,
1619 p_input_15,
1620 p_input_16,
1621 p_input_17,
1622 p_input_18,
1623 p_input_19,
1624 p_input_20,
1625 p_input_21,
1626 p_input_22,
1627 p_input_23,
1628 p_input_24,
1629 p_input_25,
1630 p_input_26,
1631 p_input_27,
1632 p_input_28,
1633 p_input_29,
1634 p_input_30,
1635 p_input_31,
1636 p_input_32,
1637 p_input_33,
1638 p_input_34,
1639 p_input_35,
1640 p_input_36,
1641 p_input_37,
1642 p_input_38,
1643 p_input_39,
1644 p_input_40,
1645 p_validate,
1646 p_exclude_from_output,
1647 sp_out_1,
1648 sp_out_2,
1649 sp_out_3,
1650 sp_out_4,
1651 sp_out_5,
1652 ret_str_len,
1653 l_exclude_from_output_chk
1654 );
1655
1656 -- RV Record formatting vmkulkar
1657 ELSIF p_record_name = 'RV' THEN -- p_record_name
1658 return_value := pay_us_mmrf_w2_format_record.format_W2_RV_record(
1659 p_effective_date,
1660 p_report_type,
1661 p_format,
1662 p_report_qualifier,
1663 p_record_name,
1664 p_input_1,
1665 p_input_2,
1666 p_input_3,
1667 p_input_4,
1668 p_input_5,
1669 p_input_6,
1670 p_input_7,
1671 p_input_8,
1672 p_input_9 ,
1673 p_input_10,
1674 p_input_11,
1675 p_input_12,
1676 p_input_13,
1677 p_input_14,
1678 p_input_15,
1679 p_input_16,
1680 p_input_17,
1681 p_input_18,
1682 p_input_19,
1683 p_input_20,
1684 p_input_21,
1685 p_input_22,
1686 p_input_23,
1687 p_input_24,
1688 p_input_25,
1689 p_input_26,
1690 p_input_27,
1691 p_input_28,
1692 p_input_29,
1693 p_input_30,
1694 p_input_31,
1695 p_input_32,
1696 p_input_33,
1697 p_input_34,
1698 p_input_35,
1699 p_input_36,
1700 p_input_37,
1701 p_input_38,
1702 p_input_39,
1703 p_input_40,
1704 p_validate,
1705 p_exclude_from_output,
1706 sp_out_1,
1707 sp_out_2,
1708 sp_out_3,
1709 sp_out_4,
1710 sp_out_5,
1711 ret_str_len,
1712 l_exclude_from_output_chk
1713 );
1714 ELSIF p_record_name = 'RF' THEN
1715 return_value :=
1716 pay_us_mmrf_w2_format_record.format_W2_RF_record(
1717 p_effective_date,
1718 p_report_type,
1719 p_format,
1720 p_report_qualifier,
1721 p_record_name,
1722 p_input_1,
1723 p_input_2,
1724 p_input_3,
1725 p_input_4,
1726 p_input_40,
1727 p_validate,
1728 p_exclude_from_output,
1729 ret_str_len,
1730 l_exclude_from_output_chk
1731 );
1732 END IF; --p_record_name
1733 --}
1734 ELSIF (p_report_type = 'SQWL') THEN --p_report_type
1735 --{
1736 IF p_record_name = 'RA' THEN -- p_record_name
1737 return_value := pay_us_mmrf_sqwl_format_record.format_SQWL_RA_record(
1738 p_effective_date,
1739 p_report_type,
1740 p_format,
1741 p_report_qualifier,
1742 p_record_name,
1743 p_input_1,
1744 p_input_2,
1745 p_input_3,
1746 p_input_4,
1747 p_input_5,
1748 p_input_6,
1749 p_input_7,
1750 p_input_8,
1751 p_input_9 ,
1752 p_input_10,
1753 p_input_11,
1754 p_input_12,
1755 p_input_13,
1756 p_input_14,
1757 p_input_15,
1758 p_input_16,
1759 p_input_17,
1760 p_input_18,
1761 p_input_19,
1762 p_input_20,
1763 p_input_21,
1764 p_input_22,
1765 p_input_23,
1766 p_input_24,
1767 p_input_25,
1768 p_input_26,
1769 p_input_27,
1770 p_input_28,
1771 p_input_29,
1772 p_input_30,
1773 p_input_31,
1774 p_input_32,
1775 p_input_33,
1776 p_input_34,
1777 p_input_35,
1778 p_input_36,
1779 p_input_37,
1780 p_input_38,
1781 p_input_39,
1782 p_input_40,
1783 p_validate,
1784 p_exclude_from_output,
1785 sp_out_1,
1786 sp_out_2,
1787 sp_out_3,
1788 sp_out_4,
1789 sp_out_5,
1790 ret_str_len,
1791 l_exclude_from_output_chk
1792 );
1793 ELSIF p_record_name = 'RE' THEN
1794 return_value :=
1795 pay_us_mmrf_sqwl_format_record.format_SQWL_RE_record(
1796 p_effective_date,
1797 p_report_type,
1798 p_format,
1799 p_report_qualifier,
1800 p_record_name,
1801 p_input_1,
1802 p_input_2,
1803 p_input_3,
1804 p_input_4,
1805 p_input_5,
1806 p_input_6,
1807 p_input_7,
1808 p_input_8,
1809 p_input_9 ,
1810 p_input_10,
1811 p_input_11,
1812 p_input_12,
1813 p_input_13,
1814 p_input_14,
1815 p_input_15,
1816 p_input_16,
1817 p_input_17,
1818 p_input_18,
1819 p_input_19,
1820 p_input_20,
1821 p_input_21,
1822 p_input_22,
1823 p_input_23,
1824 p_input_24,
1825 p_input_25,
1826 p_input_26,
1827 p_input_27,
1828 p_input_28,
1829 p_input_29,
1830 p_input_30,
1831 p_input_31,
1832 p_input_32,
1833 p_input_33,
1834 p_input_34,
1835 p_input_35,
1836 p_input_36,
1837 p_input_37,
1838 p_input_38,
1839 p_input_39,
1840 p_input_40,
1841 p_validate,
1842 p_exclude_from_output,
1843 sp_out_1,
1844 sp_out_2,
1845 sp_out_3,
1846 sp_out_4,
1847 sp_out_5,
1848 ret_str_len,
1849 l_exclude_from_output_chk
1850 );
1851
1852 ELSIF p_record_name = 'RS' THEN
1853 return_value :=
1854 pay_us_mmrf_sqwl_format_record.format_SQWL_RS_record(
1855 p_effective_date,
1856 p_report_type,
1857 p_format,
1858 p_report_qualifier,
1859 p_record_name,
1860 p_input_1,
1861 p_input_2,
1862 p_input_3,
1863 p_input_4,
1864 p_input_5,
1865 p_input_6,
1866 p_input_7,
1867 p_input_8,
1868 p_input_9 ,
1869 p_input_10,
1870 p_input_11,
1871 p_input_12,
1872 p_input_13,
1873 p_input_14,
1874 p_input_15,
1875 p_input_16,
1876 p_input_17,
1877 p_input_18,
1878 p_input_19,
1879 p_input_20,
1880 p_input_21,
1881 p_input_22,
1882 p_input_23,
1883 p_input_24,
1884 p_input_25,
1885 p_input_26,
1886 p_input_27,
1887 p_input_28,
1888 p_input_29,
1889 p_input_30,
1890 p_input_31,
1891 p_input_32,
1892 p_input_33,
1893 p_input_34,
1894 p_input_35,
1895 p_input_36,
1896 p_input_37,
1897 p_input_38,
1898 p_input_39,
1899 p_input_40,
1900 p_validate,
1901 p_exclude_from_output,
1902 sp_out_1,
1903 sp_out_2,
1904 sp_out_3,
1905 sp_out_4,
1906 sp_out_5,
1907 ret_str_len,
1908 l_exclude_from_output_chk
1909 );
1910 ELSIF p_record_name = 'RT' THEN
1911 return_value :=
1912 pay_us_mmrf_sqwl_format_record.format_SQWL_RT_record(
1913 p_effective_date,
1914 p_report_type,
1915 p_format,
1916 p_report_qualifier,
1917 p_record_name,
1918 p_input_1,
1919 p_input_2,
1920 p_input_3,
1921 p_input_4,
1922 p_input_5,
1923 p_input_6,
1924 p_input_7,
1925 p_input_8,
1926 p_input_9 ,
1927 p_input_10,
1928 p_input_11,
1929 p_input_12,
1930 p_input_13,
1931 p_input_14,
1932 p_input_15,
1933 p_input_16,
1934 p_input_17,
1935 p_input_18,
1936 p_input_19,
1937 p_input_20,
1938 p_input_21,
1939 p_input_22,
1940 p_input_23,
1941 p_input_24,
1942 p_input_25,
1943 p_input_26,
1944 p_input_27,
1945 p_input_28,
1946 p_input_29,
1947 p_input_30,
1948 p_input_31,
1949 p_input_32,
1950 p_input_33,
1951 p_input_34,
1952 p_input_35,
1953 p_input_36,
1954 p_input_37,
1955 p_input_38,
1956 p_input_39,
1957 p_input_40,
1958 p_validate,
1959 p_exclude_from_output,
1960 sp_out_1,
1961 sp_out_2,
1962 sp_out_3,
1963 sp_out_4,
1964 sp_out_5,
1965 ret_str_len,
1966 l_exclude_from_output_chk
1967 );
1968 ELSIF p_record_name = 'RSSUMM' THEN
1969 return_value :=
1970 pay_us_mmrf_sqwl_format_record.format_SQWL_RSSUMM_record(
1971 p_effective_date,
1972 p_report_type,
1973 p_format,
1974 p_report_qualifier,
1975 p_record_name,
1976 p_input_1,
1977 p_input_2,
1978 p_input_3,
1979 p_input_4,
1980 p_input_5,
1981 p_input_6,
1982 p_input_7,
1983 p_input_8,
1984 p_input_9 ,
1985 p_input_10,
1986 p_input_11,
1987 p_input_12,
1988 p_input_13,
1989 p_input_14,
1990 p_input_15,
1991 p_input_16,
1992 p_input_17,
1993 p_input_18,
1994 p_input_19,
1995 p_input_20,
1996 p_input_21,
1997 p_input_22,
1998 p_input_23,
1999 p_input_24,
2000 p_input_25,
2001 p_input_26,
2002 p_input_27,
2003 p_input_28,
2004 p_input_29,
2005 p_input_30,
2006 p_input_31,
2007 p_input_32,
2008 p_input_33,
2009 p_input_34,
2010 p_input_35,
2011 p_input_36,
2012 p_input_37,
2013 p_input_38,
2014 p_input_39,
2015 p_input_40,
2016 p_validate,
2017 p_exclude_from_output,
2018 sp_out_1,
2019 sp_out_2,
2020 sp_out_3,
2021 sp_out_4,
2022 sp_out_5,
2023 ret_str_len,
2024 l_exclude_from_output_chk
2025 );
2026 ELSIF p_record_name = 'RST' THEN
2027 return_value :=
2028 pay_us_mmrf_sqwl_format_record.format_SQWL_RST_record(
2029 p_effective_date,
2030 p_report_type,
2031 p_format,
2032 p_report_qualifier,
2033 p_record_name,
2034 p_input_1,
2035 p_input_2,
2036 p_input_3,
2037 p_input_4,
2038 p_input_5,
2039 p_input_6,
2040 p_input_7,
2041 p_input_8,
2042 p_input_9 ,
2043 p_input_40,
2044 p_validate,
2045 p_exclude_from_output,
2046 ret_str_len
2047 );
2048 ELSIF p_record_name = 'RU' THEN
2049 return_value :=
2050 pay_us_mmrf_sqwl_format_record.format_SQWL_RU_record(
2051 p_effective_date,
2052 p_report_type,
2053 p_format,
2054 p_report_qualifier,
2055 p_record_name,
2056 p_input_1,
2057 p_input_2,
2058 p_input_40,
2059 p_exclude_from_output,
2060 ret_str_len
2061 );
2062 ELSIF p_record_name = 'RF' THEN
2063 return_value :=
2064 pay_us_mmrf_sqwl_format_record.format_SQWL_RF_record(
2065 p_effective_date,
2066 p_report_type,
2067 p_format,
2068 p_report_qualifier,
2069 p_record_name,
2070 p_input_1,
2071 p_input_2,
2072 p_input_3,
2073 p_input_4,
2074 p_input_40,
2075 p_validate,
2076 p_exclude_from_output,
2077 ret_str_len
2078 );
2079 END IF; --p_record_name
2080 --}
2081 END IF; --p_report_type
2082 --}
2083 ELSIF p_format = 'CUSTOM' THEN
2084 --{
2085 hr_utility.trace('In pay_us_reporting_utils_pkg.format_record ' || p_format);
2086
2087 IF p_record_name = 'H' THEN -- p_record_name
2088 hr_utility.trace('In pay_us_reporting_utils_pkg.format_record ' || p_record_name);
2089 return_value :=
2090 pay_us_custom_sqwl_format_rec.format_SQWL_CUSTOM_EMPLOYER(
2091 p_effective_date,
2092 p_report_type,
2093 p_format,
2094 p_report_qualifier,
2095 p_record_name,
2096 p_input_1,
2097 p_input_2,
2098 p_input_3,
2099 p_input_4,
2100 p_input_5,
2101 p_input_6,
2102 p_input_7,
2103 p_input_8,
2104 p_input_9 ,
2105 p_input_10,
2106 p_input_11,
2107 p_input_12,
2108 p_input_13,
2109 p_input_14,
2110 p_input_15,
2111 p_input_16,
2112 p_input_17,
2113 p_input_18,
2114 p_input_19,
2115 p_input_20,
2116 p_input_21,
2117 p_input_22,
2118 p_input_23,
2119 p_input_24,
2120 p_input_25,
2121 p_input_26,
2122 p_input_27,
2123 p_input_28,
2124 p_input_29,
2125 p_input_30,
2126 p_input_31,
2127 p_input_32,
2128 p_input_33,
2129 p_input_34,
2130 p_input_35,
2131 p_input_36,
2132 p_input_37,
2133 p_input_38,
2134 p_input_39,
2135 p_input_40,
2136 p_validate,
2137 p_exclude_from_output,
2138 sp_out_1,
2139 sp_out_2,
2140 sp_out_3,
2141 sp_out_4,
2142 sp_out_5,
2143 ret_str_len,
2144 l_exclude_from_output_chk
2145 );
2146
2147 ELSIF p_record_name = 'D' THEN
2148 hr_utility.trace('In pay_us_reporting_utils_pkg.format_record ' || p_record_name);
2149 return_value :=
2150 pay_us_custom_sqwl_format_rec.format_SQWL_CUSTOM_EMPLOYEE(
2151 p_effective_date,
2152 p_report_type,
2153 p_format,
2154 p_report_qualifier,
2155 p_record_name,
2156 p_input_1,
2157 p_input_2,
2158 p_input_3,
2159 p_input_4,
2160 p_input_5,
2161 p_input_6,
2162 p_input_7,
2163 p_input_8,
2164 p_input_9 ,
2165 p_input_10,
2166 p_input_11,
2167 p_input_12,
2168 p_input_13,
2169 p_input_14,
2170 p_input_15,
2171 p_input_16,
2172 p_input_17,
2173 p_input_18,
2174 p_input_19,
2175 p_input_20,
2176 p_input_21,
2177 p_input_22,
2178 p_input_23,
2179 p_input_24,
2180 p_input_25,
2181 p_input_26,
2182 p_input_27,
2183 p_input_28,
2184 p_input_29,
2185 p_input_30,
2186 p_input_31,
2187 p_input_32,
2188 p_input_33,
2189 p_input_34,
2190 p_input_35,
2191 p_input_36,
2192 p_input_37,
2193 p_input_38,
2194 p_input_39,
2195 p_input_40,
2196 p_validate,
2197 p_exclude_from_output,
2198 sp_out_1,
2199 sp_out_2,
2200 sp_out_3,
2201 sp_out_4,
2202 sp_out_5,
2203 ret_str_len,
2204 l_exclude_from_output_chk
2205 );
2206 END IF; --p_report_type
2207 --}
2208 END IF; -- p_format
2209 return_value:=upper(return_value);
2210
2211 main_return_string := substr(return_value,1,200);
2212 sp_out_1:=substr(return_value,201,200);
2213 sp_out_2:=substr(return_value,401,200);
2214 sp_out_3:=substr(return_value,601,200);
2215 sp_out_4:=substr(return_value,801,200);
2216
2217 IF (((p_record_name = 'RS') OR (p_record_name = 'D')) AND
2218 (p_report_type = 'SQWL') AND
2219 (p_input_40 = 'FLAT') ) THEN
2220 NULL; -- sp_out_5 is initialized with ssn in format_SQWL_RS_record
2221 ELSE
2222 sp_out_5:=substr(return_value,1001,200);
2223 END IF;
2224 IF l_exclude_from_output_chk THEN
2225 p_exclude_from_output := 'Y';
2226 ELSE
2227 p_exclude_from_output := 'N';
2228 END IF;
2229 hr_utility.trace('main_return_string = '||main_return_string);
2230 hr_utility.trace(' length of main_return_string = '||to_char(length(main_return_string)));
2231 hr_utility.trace('sp_out_1 = '||sp_out_1);
2232 hr_utility.trace(' length of sp_out_1 = '||to_char(length(sp_out_1)));
2233 hr_utility.trace('sp_out_2 = '||sp_out_2);
2234 hr_utility.trace(' length of sp_out_2 = '||to_char(length(sp_out_2)));
2235 hr_utility.trace('sp_out_3 = '||sp_out_3);
2236 hr_utility.trace(' length of sp_out_3 = '||to_char(length(sp_out_3)));
2237 hr_utility.trace('sp_out_4 = '||sp_out_4);
2238 hr_utility.trace(' length of sp_out_4 = '||to_char(length(sp_out_4)));
2239 hr_utility.trace('sp_out_5 = '||sp_out_5);
2240 hr_utility.trace(' length of sp_out_5 = '||to_char(length(sp_out_5)));
2241 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
2242
2243 RETURN main_return_string;
2244 END Format_Record;
2245
2246 /* Function Name : Get_Territory_Values
2247 Purpose : Purpose of this function is to fetch the balances as well
2248 as the data related to territory.
2249 Error checking
2250 Special Note :
2251 */
2252
2253 FUNCTION Get_Territory_Values(
2254 p_assignment_action_id number, -- context
2255 p_tax_unit_id number,-- context
2256 p_effective_date IN varchar2,
2257 p_report_type IN varchar2,
2258 p_format IN varchar2,
2259 p_report_qualifier IN varchar2,
2260 p_record_name IN varchar2,
2261 p_input_1 IN varchar2,
2262 p_input_2 IN varchar2,
2263 p_input_3 IN varchar2,
2264 p_input_4 IN varchar2,
2265 p_input_5 IN varchar2,
2266 p_validate IN varchar2,
2267 p_exclude_from_output OUT nocopy varchar2,
2268 sp_out_1 OUT nocopy varchar2,
2269 sp_out_2 OUT nocopy varchar2,
2270 sp_out_3 OUT nocopy varchar2,
2271 sp_out_4 OUT nocopy varchar2,
2272 sp_out_5 OUT nocopy varchar2,
2273 sp_out_6 OUT nocopy varchar2,
2274 sp_out_7 OUT nocopy varchar2,
2275 sp_out_8 OUT nocopy varchar2,
2276 sp_out_9 OUT nocopy varchar2,
2277 sp_out_10 OUT nocopy varchar2)
2278 return varchar2 IS
2279
2280 l_err boolean;
2281 l_entity_id ff_database_items.user_entity_id%type;
2282 l_archived_value ff_archive_items.value%type;
2283 l_jurisdiction_code varchar2(25);
2284 l_message varchar2(1000);
2285 l_state_wage varchar2(100);
2286 l_main_return varchar2(100);
2287
2288 TYPE dbi_columns IS RECORD(
2289 p_user_name ff_database_items.user_name%type,
2290 p_archived_value ff_archive_items.value%type);
2291
2292 dbi_rec dbi_columns;
2293
2294 TYPE dbi_infm IS TABLE OF dbi_rec%TYPE
2295 INDEX BY BINARY_INTEGER;
2296
2297 dbi_table dbi_infm;
2298
2299 CURSOR get_user_entity_id
2300 (c_user_name ff_database_items.user_name%type) IS
2301 SELECT fdi.user_entity_id
2302 FROM ff_database_items fdi,
2303 ff_user_entities fue
2304 WHERE fue.legislation_code = 'US'
2305 AND fue.user_entity_id = fdi.user_entity_id
2306 AND fdi.user_name = c_user_name;
2307
2308 CURSOR get_archived_values(
2309 c_user_entity_id ff_database_items.user_entity_id%type,
2310 c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
2311 c_tax_unit_id hr_organization_units.organization_id%type)
2312 IS
2313 SELECT target.value
2314 FROM ff_archive_item_contexts con2,
2315 ff_contexts fc2,
2316 ff_archive_items target
2317 WHERE target.user_entity_id = c_user_entity_id
2318 AND target.context1 = to_char(c_assignment_action_id)
2319 /* context assignment action id */
2320 AND fc2.context_name = 'TAX_UNIT_ID'
2321 and con2.archive_item_id = target.archive_item_id
2322 and con2.context_id = fc2.context_id
2323 and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id);
2324 /*context of tax_unit_id */
2325
2326 CURSOR get_jd_archived_values(
2327 c_user_entity_id ff_database_items.user_entity_id%type,
2328 c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
2329 c_tax_unit_id hr_organization_units.organization_id%type,
2330 c_jurisdiction_code varchar2)
2331 IS
2332 SELECT target.value
2333 FROM ff_archive_item_contexts con2,
2334 ff_archive_item_contexts con3,
2335 ff_contexts fc2,
2336 ff_contexts fc3,
2337 ff_archive_items target
2338 WHERE target.user_entity_id = c_user_entity_id
2339 AND target.context1 = to_char(c_assignment_action_id)
2340 /* context assignment action id */
2341 AND fc2.context_name = 'TAX_UNIT_ID'
2342 and con2.archive_item_id = target.archive_item_id
2343 and con2.context_id = fc2.context_id
2344 and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id)
2345 /*context of tax_unit_id */
2346 and fc3.context_name = 'JURISDICTION_CODE'
2347 and con3.archive_item_id = target.archive_item_id
2348 and con3.context_id = fc3.context_id
2349 and substr(con3.context,1,2) = substr(c_jurisdiction_code,1,2);
2350 /* 3rd context of state jurisdiction_code*/
2351
2352 BEGIN
2353
2354 hr_utility.trace('p_assignment_action_id = '||to_char(p_assignment_action_id));
2355 hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
2356 dbi_table(1).p_user_name:='A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD';
2357 dbi_table(2).p_user_name:='A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD';
2358 dbi_table(3).p_user_name:='A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD';
2359 dbi_table(4).p_user_name:='A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD';
2360 dbi_table(5).p_user_name:='A_SIT_WITHHELD_PER_JD_GRE_YTD';
2361 dbi_table(6).p_user_name:='A_PER_MARITAL_STATUS';
2362 dbi_table(7).p_user_name:='A_CON_NATIONAL_IDENTIFIER';
2363
2364 l_jurisdiction_code:='72-000-0000';
2365 hr_utility.trace('Get PR Values');
2366
2367 FOR i in dbi_table.first .. dbi_table.last loop
2368
2369 OPEN get_user_entity_id(dbi_table(i).p_user_name);
2370 FETCH get_user_entity_id INTO l_entity_id;
2371 IF get_user_entity_id%NOTFOUND THEN
2372
2373 l_message:='Error in '||p_record_name||'. User_Entity_Id not found for user name '
2374 ||dbi_table(i).p_user_name;
2375 dbi_table(i).p_archived_value:='00000000000';
2376
2377 ELSIF get_user_entity_id%FOUND THEN
2378
2379 hr_utility.trace('get_user_entity_id = '||to_char(l_entity_id));
2380 hr_utility.trace('p_assignment_action_id = '||to_char(p_assignment_action_id));
2381 hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
2382
2383 IF dbi_table(i).p_user_name like '%PER_JD_GRE_YTD' THEN
2384 open get_jd_archived_values(l_entity_id,
2385 p_assignment_action_id,
2386 p_tax_unit_id,
2387 l_jurisdiction_code);
2388 FETCH get_jd_archived_values INTO l_archived_value;
2389 IF get_jd_archived_values%NOTFOUND THEN
2390 dbi_table(i).p_archived_value:='00000000000';
2391 ELSE
2392 dbi_table(i).p_archived_value:= data_validation
2393 ( p_effective_date,
2394 p_report_type,
2395 p_format,
2396 p_report_qualifier,
2397 p_record_name,
2398 'NEG_CHECK',
2399 l_archived_value,
2400 dbi_table(i).p_user_name,
2401 p_input_1,
2402 null,
2403 p_validate,
2404 p_exclude_from_output,
2405 sp_out_1,
2406 sp_out_2);
2407
2408 hr_utility.trace('Archived_value = '||dbi_table(i).p_archived_value);
2409
2410 IF p_exclude_from_output = 'Y' THEN
2411 l_err:=TRUE;
2412 END IF;
2413 END IF;
2414 CLOSE get_jd_archived_values;
2415
2416 ELSE -- Non JD specific balances
2417 OPEN get_archived_values(l_entity_id,
2418 p_assignment_action_id,
2419 p_tax_unit_id);
2420 FETCH get_archived_values INTO l_archived_value;
2421
2422 IF get_archived_values%NOTFOUND THEN
2423 IF ( (dbi_table(i).p_user_name = 'A_PER_MARITAL_STATUS') OR
2424 (dbi_table(i).p_user_name = 'A_CON_NATIONAL_IDENTIFIER') ) THEN
2425 dbi_table(i).p_archived_value:= ' ';
2426 ELSE
2427 dbi_table(i).p_archived_value:='00000000000';
2428 END IF;
2429
2430 hr_utility.trace('Archived_values not found for user name ' ||dbi_table(i).p_user_name);
2431 ELSIF get_archived_values%FOUND THEN
2432
2433 hr_utility.trace('Archived_values found for user name ' ||dbi_table(i).p_user_name);
2434 hr_utility.trace('Archived_value before neg check= '||l_archived_value);
2435
2436 IF ((dbi_table(i).p_user_name = 'A_PER_MARITAL_STATUS') OR
2437 (dbi_table(i).p_user_name = 'A_CON_NATIONAL_IDENTIFIER')) THEN
2438 dbi_table(i).p_archived_value := l_archived_value;
2439
2440 IF dbi_table(i).p_user_name = 'A_PER_MARITAL_STATUS' THEN
2441
2442 l_archived_value := replace(l_archived_value,' ');
2443 -- Bug # 2173795
2444 -- For Portorico Tax Jurisdiction Civil Status value would either M or S
2445 -- IF l_archived_value is other than M it should be defaulted to S
2446 IF l_archived_value = 'M' THEN
2447 dbi_table(i).p_archived_value := 'M';
2448 ELSE
2449 dbi_table(i).p_archived_value := 'S';
2450 END IF;
2451 END IF;
2452
2453 ELSE
2454
2455 dbi_table(i).p_archived_value:= data_validation
2456 ( p_effective_date,
2457 p_report_type,
2458 p_format,
2459 p_report_qualifier,
2460 p_record_name,
2461 'NEG_CHECK',
2462 l_archived_value,
2463 dbi_table(i).p_user_name,
2464 p_input_1,
2465 null,
2466 p_validate,
2467 p_exclude_from_output,
2468 sp_out_1,
2469 sp_out_2);
2470
2471 hr_utility.trace('Archived_value = '||dbi_table(i).p_archived_value);
2472
2473 IF p_exclude_from_output = 'Y' THEN
2474 l_err:=TRUE;
2475 END IF;
2476 END IF;
2477 END IF;
2478 CLOSE get_archived_values;
2479 END IF;
2480 END IF;
2481 CLOSE get_user_entity_id;
2482
2483 END LOOP;
2484
2485
2486 hr_utility.trace('before Call to retrieve state_wages');
2487
2488 l_state_wage := HR_US_W2_REP.GET_W2_ARCH_BAL
2489 (p_assignment_action_id,'A_W2_STATE_WAGES',
2490 p_tax_unit_id,l_jurisdiction_code,2);
2491
2492 hr_utility.trace('l_state_wage = '||l_state_wage);
2493 sp_out_5:= data_validation ( p_effective_date,
2494 p_report_type,
2495 p_format,
2496 p_report_qualifier,
2497 p_record_name,
2498 'NEG_CHECK',
2499 l_state_wage,
2500 'State_wage',
2501 p_input_1,
2502 null,
2503 p_validate,
2504 p_exclude_from_output,
2505 sp_out_1,
2506 sp_out_2);
2507
2508 IF p_exclude_from_output='Y' THEN
2509 l_err:=TRUE;
2510 END IF;
2511
2512
2513 l_main_return:=dbi_table(1).p_archived_value;
2514 sp_out_1:=dbi_table(2).p_archived_value;
2515 sp_out_2:=dbi_table(3).p_archived_value;
2516 sp_out_3:=dbi_table(4).p_archived_value;
2517 sp_out_4:=dbi_table(5).p_archived_value;
2518 sp_out_6:=dbi_table(6).p_archived_value;
2519 sp_out_7:=dbi_table(7).p_archived_value;
2520
2521
2522 hr_utility.trace('l_main_return = '||l_main_return);
2523 hr_utility.trace('sp_out_1 = '||sp_out_1);
2524 hr_utility.trace('sp_out_2 = '||sp_out_2);
2525 hr_utility.trace('sp_out_3 = '||sp_out_3);
2526 hr_utility.trace('sp_out_4 = '||sp_out_4);
2527 hr_utility.trace('sp_out_5 = '||sp_out_5);
2528 hr_utility.trace('sp_out_6 = '||sp_out_6);
2529 hr_utility.trace('sp_out_7 = '||sp_out_7);
2530
2531
2532 IF p_validate = 'Y' THEN
2533 IF l_err THEN
2534 p_exclude_from_output:='Y';
2535 END IF;
2536 END IF;
2537
2538 IF p_exclude_from_output IS NULL THEN
2539 p_exclude_from_output:='N';
2540 END IF;
2541
2542 RETURN l_main_return;
2543
2544 END Get_Territory_Values;
2545
2546 FUNCTION Character_check(p_value IN varchar2)
2547 RETURN VARCHAR2
2548
2549 IS
2550
2551 TYPE special_characters is record(
2552 p_character varchar2(100));
2553 character_val_record special_characters;
2554 type character_val_rec IS table of character_val_record%type
2555 INDEX BY BINARY_INTEGER;
2556 character_rec character_val_rec;
2557 l_stripped_value varchar2(100);
2558 l_param_length number(20);
2559
2560 Begin
2561
2562 character_rec(1).p_character :='<';
2563 character_rec(2).p_character :='>';
2564 character_rec(3).p_character :='(';
2565 character_rec(4).p_character :=')';
2566 character_rec(5).p_character :='_';
2567 character_rec(6).p_character :='*';
2568 character_rec(7).p_character :='&';
2569 character_rec(8).p_character :='^';
2570 character_rec(9).p_character :='%';
2571 character_rec(10).p_character :='$';
2572 character_rec(11).p_character :='#';
2573 character_rec(12).p_character :='@';
2574 character_rec(13).p_character :='!';
2575 character_rec(14).p_character :='~';
2576 character_rec(15).p_character :='+';
2577 character_rec(16).p_character :='=';
2578 character_rec(17).p_character :='?';
2579 character_rec(18).p_character :='/';
2580 character_rec(19).p_character :=','; /* Bug:2150138*/
2581
2582 l_stripped_value := p_value;
2583
2584 FOR i in 1 .. 19 LOOP
2585
2586 l_stripped_value := replace(l_stripped_value,character_rec(i).p_character,' ');
2587
2588 END LOOP;
2589
2590 return l_stripped_value;
2591
2592 END;
2593
2594 FUNCTION Formula_Check(p_report_format IN VARCHAR2,
2595 p_formula_name IN VARCHAR2)
2596 RETURN VARCHAR2 IS
2597
2598 l_value varchar2(1);
2599
2600 CURSOR formula_exist(c_report_format pay_magnetic_blocks.report_format%TYPE,
2601 c_formula_name ff_formulas_f.formula_name%TYPE)
2602 IS
2603 SELECT 'Y'
2604 FROM ff_formulas_f ff,
2605 pay_magnetic_blocks pmb,
2606 pay_magnetic_records pmr
2607 WHERE pmb.report_format = c_report_format
2608 AND pmr.magnetic_block_id = pmb.magnetic_block_id
2609 AND pmr.formula_id = ff.formula_id
2610 AND ff.formula_name = c_formula_name;
2611
2612 Begin
2613
2614 l_value := 'N';
2615
2616 hr_utility.trace('Formula_Check');
2617 hr_utility.trace('p_report_format = '||p_report_format);
2618 hr_utility.trace('p_formula_name = '||p_formula_name);
2619
2620
2621 OPEN formula_exist(p_report_format,
2622 p_formula_name);
2623
2624 FETCH formula_exist INTO l_value;
2625
2626 CLOSE formula_exist;
2627
2628 hr_utility.trace('l_value = '||l_value);
2629
2630 RETURN l_value;
2631
2632 END;
2633
2634 FUNCTION get_file_name ( p_bus_group_id IN Number, -- Business Group Id
2635 p_report_type IN Varchar2, -- W2, W2C, SQWL, RL (Rita/CCA or Local City)
2636 p_state_code IN Varchar2, -- FED or State Code
2637 p_mag_effective_date IN Varchar2, -- This would be used to derive period
2638 p_format_type IN Varchar2 -- Only for SQWL (I=ICESA, M=MMREF, T=TIB4, S=State)
2639 ) RETURN varchar2
2640 IS
2641
2642 --
2643 -- Purpose: Procedure to derive the Mag file Name for following Magnetic media
2644 -- processes.
2645 -- 1. Federal W-2 Magnetic Media
2646 -- 2. State W-2 Magnetic Media
2647 -- 3. State Quarterly Wage Listing
2648 -- 4. Local W-2 Magnetic Media
2649 -- 5. Federal W-2c Magnetic Medica
2650 --
2651 -- Declaration of Local program variables
2652 --
2653 l_mag_file_name Varchar2(80);
2654 --
2655 -- This Cursor fetches Tax info for the given jurisdiction and effective date
2656 --
2657 -- Federal Mag filenames will have following format.
2658 -- 1. Max of 6 characters Business Group Short Name (Embedded blanks will be ignored)
2659 -- 2. FED or State Abbreviation
2660 -- 3. Format specifier (W2)
2661 -- 4. Last two digits of the year
2662
2663 cursor c_fed_w2_Cursor(c_bus_group_id Number,
2664 c_report_type Varchar2,
2665 c_mag_effective_date Varchar2)
2666 is
2667 select substr(translate(upper(o.short_name),
2668 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
2669 {["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
2670 ---substr(replace(replace(replace(upper(o.short_name),'_'),' '),'-'),1,6)||
2671 'FED'||c_report_type||
2672 substr(to_char(add_months(fnd_date.canonical_TO_DATE(c_mag_effective_date), 12) -1, 'YYYY'),3,4)
2673 from per_business_groups o where o.organization_id = c_bus_group_id;
2674
2675 -- State W-2 Mag filenames will have following format.
2676 -- 1. Max of 6 characters Business Group Short Name (Embedded blanks will be ignored)
2677 -- 2. State Abbreviation
2678 -- 3. Report Type
2679 -- 4. Last two digits of the year
2680
2681 cursor c_State_W2_Cursor (c_bus_group_id Number,
2682 c_report_type Varchar2,
2683 c_state_code Varchar2,
2684 c_mag_effective_date Varchar2)
2685 is
2686 select substr(translate(upper(o.short_name),
2687 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
2688 {["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
2689 -- substr(replace(replace(replace(upper(o.short_name),'_'),' '),'-'),1,6)||
2690 psr.state_code ||
2691 c_report_type||
2692 substr(to_char(add_months(fnd_date.canonical_to_date(c_mag_effective_date ), 12) -1, 'YYYY'),3,4)
2693 from per_business_groups o, pay_state_rules psr
2694 where o.organization_id = c_bus_group_id
2695 and psr.state_code = c_state_code;
2696
2697 -- The SQWL (State Quarterly Wage Listing) filenames will follow the naming convention as
2698 -- 1. Max of 6 characters Business Group Short Name (Embedded blanks will be ignored)
2699 -- 2. State Abbreviation
2700 -- 3. Period in MMYY format
2701 -- 4. Format Type (i.e. I=ICESA, M=MMREF, T=TIB4, S=State)
2702
2703 cursor c_sqwl_Cursor (c_bus_group_id Number,
2704 c_state_code Varchar2,
2705 c_mag_effective_date Varchar2,
2706 c_format_type Varchar2)
2707 is
2708 select substr(translate(upper(o.short_name),
2709 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
2710 {["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
2711 -- substr(replace(replace(replace(upper(o.short_name),'_'),' '),'-'),1,6)||
2712 c_state_code ||
2713 substr(to_char(fnd_date.canonical_to_date(c_mag_effective_date ), 'MMYY'),1,4) ||
2714 c_format_type
2715 from per_business_groups o, pay_state_rules psr
2716 where o.organization_id = c_bus_group_id
2717 and psr.state_code = c_state_code;
2718
2719 --
2720 -- Local Mag filenames will have following format.
2721 -- 1. Max of 6 characters Business Group Short Name (Embedded blanks will be ignored)
2722 -- 2. City or Agency Code
2723 -- 3. Format specifier (W2)
2724 -- 4. Last two digits of the year
2725 --
2726 cursor c_local_cursor(c_bus_group_id Number,
2727 c_city_or_agency_code Varchar2,
2728 c_mag_effective_date Varchar2)
2729 is
2730 select substr(translate(upper(o.short_name),
2731 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
2732 {["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
2733 -- substr(replace(replace(replace(upper(o.short_name),'_'),' '),'-'),1,6)||
2734 decode(c_city_or_agency_code,'RTCCA','RITA','CCAAA','CCA',c_city_or_agency_code) ||
2735 'W2'||
2736 substr(to_char(add_months(fnd_date.canonical_TO_DATE(c_mag_effective_date), 12) -1, 'YYYY'),3,4)
2737 from per_business_groups o where o.organization_id = c_bus_group_id;
2738
2739 BEGIN
2740 l_mag_file_name := '';
2741
2742 if p_report_type in ('W2', 'W2C') then
2743 if p_state_code = 'FED' then
2744 open c_fed_w2_cursor(p_bus_group_id, p_report_type, p_mag_effective_date );
2745 fetch c_fed_w2_cursor into l_mag_file_name;
2746 close c_fed_w2_cursor;
2747 else
2748 open c_state_w2_cursor(p_bus_group_id, p_report_type, p_state_code, p_mag_effective_date );
2749 fetch c_state_w2_cursor into l_mag_file_name;
2750 close c_state_w2_cursor ;
2751 end if;
2752
2753 elsif p_report_type = 'SQWL' then
2754 open c_sqwl_cursor(p_bus_group_id, p_state_code, p_mag_effective_date, p_format_type );
2755 fetch c_sqwl_cursor into l_mag_file_name;
2756 close c_sqwl_cursor;
2757 elsif p_report_type = 'RL' then
2758 open c_local_cursor(p_bus_group_id, p_state_code, p_mag_effective_date );
2759 fetch c_local_cursor into l_mag_file_name;
2760 close c_local_cursor;
2761 else
2762 l_mag_file_name := 'ERRORMAGFILE';
2763 end if;
2764 return (l_mag_file_name);
2765 END get_file_name; -- End of Function get_file_name
2766
2767
2768 -- This function derives balance_ID for a given Balance
2769 --
2770 FUNCTION bal_db_item ( p_db_item_name varchar2)
2771 RETURN number
2772 IS
2773
2774 /* Get the defined_balance_id for the specified balance DB item. */
2775
2776 CURSOR csr_defined_balance is
2777 SELECT to_number(UE.creator_id)
2778 FROM ff_user_entities UE,
2779 ff_database_items DI
2780 WHERE DI.user_name = p_db_item_name
2781 AND UE.user_entity_id = DI.user_entity_id
2782 AND Ue.creator_type = 'B'
2783 AND UE.legislation_code = 'US';
2784
2785 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2786
2787
2788 BEGIN
2789
2790 --hr_utility.trace('p_db_item_name is '||p_db_item_name);
2791
2792 OPEN csr_defined_balance;
2793 FETCH csr_defined_balance INTO l_defined_balance_id;
2794 IF csr_defined_balance%notfound THEN
2795 CLOSE csr_defined_balance;
2796 RAISE hr_utility.hr_error;
2797 ELSE
2798 CLOSE csr_defined_balance;
2799 END IF;
2800
2801 --hr_utility.trace('l_defined_balance_id is '||to_char(l_defined_balance_id));
2802 RETURN (l_defined_balance_id);
2803
2804 END bal_db_item;
2805
2806 -- Derives live Balance for W2_GOVE_EE_CONTRIB
2807 --
2808 FUNCTION get_live_ee_contrib( p_assignment_action_id number, --context
2809 p_tax_unit_id number --context
2810 )
2811 RETURN VARCHAR2
2812 IS
2813
2814 lv_bal_amt number := 0;
2815 l_bal_id number := 0;
2816 l_get boolean := TRUE ;
2817 l_balance_name pay_balance_types.balance_name%TYPE;
2818 l_effective_start_date pay_payroll_actions.effective_date%TYPE;
2819 l_effective_end_date pay_payroll_actions.effective_date%TYPE;
2820 l_exists varchar2(1);
2821
2822 CURSOR get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
2823 IS
2824 select add_months(effective_date,-12)+1,effective_date
2825 from pay_payroll_actions ppa,
2826 pay_assignment_actions paa
2827 where ppa.payroll_action_id = paa.payroll_action_id
2828 and paa.assignment_action_id = c_assignment_action_id;
2829
2830 /*Bug:3233249*/
2831 CURSOR get_balance_feed_exist(c_balance_name pay_balance_types.balance_name%TYPE,
2832 c_start_date pay_payroll_actions.effective_date%TYPE,
2833 c_end_date pay_payroll_actions.effective_date%TYPE)
2834 IS
2835 select '1' from pay_balance_feeds_f pbf,
2836 pay_balance_types pbt
2837 where pbf.balance_type_id = pbt.balance_type_id
2838 and pbt.balance_name = c_balance_name
2839 and pbf.effective_start_date <= c_end_date
2840 and pbf.effective_end_date >= c_start_date;
2841
2842
2843 BEGIN
2844 if p_tax_unit_id is not null then
2845 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
2846 end if;
2847 l_balance_name := 'W2 Govt EE Contrib';
2848
2849 OPEN get_effective_date(p_assignment_action_id);
2850 FETCH get_effective_date INTO l_effective_start_date
2851 ,l_effective_end_date;
2852
2853 hr_utility.trace('l_effective_start_date = '||l_effective_start_date);
2854 hr_utility.trace('l_effective_end_date = '||l_effective_end_date);
2855
2856 IF get_effective_date%NOTFOUND THEN
2857
2858 hr_utility.trace('Effective Date not found for given
2859 assignment_action_id ='||to_char(p_assignment_action_id));
2860
2861 END IF;
2862
2863 CLOSE get_effective_date;
2864
2865 OPEN get_balance_feed_exist(l_balance_name,
2866 l_effective_start_date,
2867 l_effective_end_date);
2868
2869 FETCH get_balance_feed_exist INTO l_exists;
2870
2871
2872 If get_balance_feed_exist%NOTFOUND THEN
2873
2874 hr_utility.trace('get_balance_feed_exist%NOTFOUND');
2875 return 0;
2876
2877 ELSE
2878
2879 /* Live Balance Call Procedure */
2880 l_bal_id := bal_db_item('W2_GOVT_EE_CONTRIB_PER_GRE_YTD');
2881 hr_utility.trace('get_balance_feed_exist FOUND');
2882 lv_bal_amt := nvl(pay_balance_pkg.get_value
2883 (p_defined_balance_id => l_bal_id,
2884 p_assignment_action_id => p_assignment_action_id),0);
2885 return (to_char(lv_bal_amt));
2886
2887 END IF;
2888
2889 END get_live_ee_contrib;
2890
2891 /*********************************************************************
2892 Name : get_ff_archive_value
2893
2894 Description : Definition for formula function GET_ARCHIVE_VALUE.
2895 Calls the get_archive_value function to fetch the
2896 archived value for an user entity
2897 ********************************************************************/
2898
2899 FUNCTION get_ff_archive_value (
2900 p_action_id NUMBER, -- context
2901 p_jurisdiction_code VARCHAR2, -- context
2902 p_tax_unit_id NUMBER, -- context
2903 p_data_type VARCHAR2
2904 )
2905 RETURN NUMBER IS
2906
2907 lv_return_value NUMBER;
2908 lv_data_type VARCHAR2(100);
2909 /*bug 4011829 */
2910 l_jurisdiction_code VARCHAR(11);
2911
2912
2913 BEGIN
2914 lv_return_value := 0;
2915 hr_utility.trace('In pay_us_reporting_utils_pkg.get_ff_archive_value');
2916
2917 hr_utility.trace('p_tax_unit_id = '||p_tax_unit_id);
2918 hr_utility.trace('p_assignment_action_id = '||p_action_id);
2919
2920 /*
2921
2922 -- remarked by tmehra, as discussed with Dipen and Mehul
2923 -- The whole if block would be removed after Mehul clarifies the
2924 -- 'A_W2_HSA_PER_GRE_YTD' usage.
2925
2926 IF p_data_type = 'ER_HSA' THEN
2927 lv_data_type := 'A_W2_HSA_PER_GRE_YTD';
2928 ELSIF p_data_type = 'IN_STATE_ADV_EIC' THEN
2929 lv_data_type := 'A_STEIC_ADVANCE_PER_JD_GRE_YTD';
2930 ELSIF p_data_type = 'IN_FED_ADV_EIC' THEN
2931 lv_data_type := 'A_EIC_ADVANCE_PER_GRE_YTD';
2932 ELSE lv_data_type := NULL;
2933 END IF;
2934 */
2935 if p_data_type like '%_JD_%' THEN
2936
2937 l_jurisdiction_code := substr(p_jurisdiction_code,1,2)||'-000-0000';
2938
2939 else
2940
2941 l_jurisdiction_code := NULL;
2942
2943 end if;
2944
2945 lv_return_value := to_number(pay_us_archive_util.get_archive_value(
2946 p_action_id,
2947 p_data_type,
2948 p_tax_unit_id,
2949 l_jurisdiction_code
2950 ));
2951
2952 hr_utility.trace('lv_return_value = ' || lv_return_value);
2953
2954
2955 RETURN nvl(lv_return_value,0);
2956
2957 END get_ff_archive_value;
2958
2959
2960 FUNCTION get_employee_count( p_payroll_action_id number, --context
2961 p_tax_unit_id number, --context
2962 p_state varchar2 default null
2963 ) RETURN number IS
2964 lv_employee_count number ;
2965 BEGIN
2966 hr_utility.trace('Inside pay_us_reporting_utils_pkg.get_employee_count') ;
2967 hr_utility.trace('p_payroll_action_id := '||p_payroll_action_id) ;
2968 hr_utility.trace('p_tax_unit_id := ' || p_tax_unit_id) ;
2969
2970 select count(*)
2971 into lv_employee_count
2972 from pay_payroll_actions ppa,
2973 pay_assignment_actions paa
2974 where ppa.payroll_action_id = p_payroll_action_id
2975 and paa.tax_unit_id = p_tax_unit_id
2976 and ppa.payroll_action_id = paa.payroll_action_id ;
2977
2978 hr_utility.trace('lv_employee_count := '||lv_employee_count) ;
2979 RETURN lv_employee_count ;
2980
2981 END get_employee_count ;
2982
2983
2984 FUNCTION get_employee_count_monthwise( p_payroll_action_id number, --context
2985 p_tax_unit_id number, --context
2986 p_database_item_name varchar2
2987 ) RETURN number is
2988 lv_employee_count number ;
2989 temp_month_count number;
2990 return_month_count number;
2991
2992 cursor get_all_assignment (cur_payroll_action_id number, cur_tax_unit_id number)
2993 is
2994 Select paa.assignment_action_id
2995 from pay_assignment_actions paa
2996 where payroll_action_id = cur_payroll_action_id
2997 and tax_unit_id = cur_tax_unit_id;
2998
2999 BEGIN
3000
3001 return_month_count := 0;
3002
3003 FOR i in get_all_assignment (p_payroll_action_id, p_tax_unit_id) LOOP
3004
3005 Select fai.value
3006 into lv_employee_count
3007 from ff_archive_items fai,
3008 ff_database_items fdi
3009 where fdi.user_name = p_database_item_name /*eg A_SQWL_MONTH1_COUNT */
3010 and fai.user_entity_id = fdi.user_entity_id
3011 and fai.context1 = i.assignment_action_id ;
3012
3013 IF lv_employee_count is null THEN
3014 temp_month_count := pay_us_sqwl_misc.get_Old_Month1_Count( i.assignment_action_id);
3015 ELSE
3016 temp_month_count := lv_employee_count ;
3017 END IF;
3018
3019 IF (temp_month_count <> 0 ) THEN
3020 return_month_count := return_month_count + 1;
3021 END IF;
3022
3023 END LOOP;
3024
3025 RETURN return_month_count ;
3026
3027 END get_employee_count_monthwise;
3028
3029 FUNCTION get_total_wages( p_payroll_action_id number, --context
3030 p_tax_unit_id number, --context
3031 p_state varchar2,
3032 p_report_type varchar2 default 'SQWL',
3033 p_balance_name varchar2 default null
3034 ) RETURN number IS
3035 lv_total_wages number ;
3036
3037 CURSOR cur_employee_count(p_payroll_action_id number,
3038 p_tax_unit_id number,
3039 p_state varchar2) IS
3040 SELECT count(*)
3041 FROM ff_archive_item_contexts faic,
3042 ff_archive_items fai,
3043 ff_database_items fdi,
3044 pay_assignment_actions paa,
3045 pay_payroll_actions ppa
3046 WHERE
3047 ppa.payroll_action_id = p_payroll_action_id
3048 and ppa.payroll_action_id = paa.payroll_action_id
3049 and fdi.user_name = 'A_STATE_ABBREV'
3050 and fdi.user_entity_id = fai.user_entity_id
3051 and fai.archive_item_id = faic.archive_item_id
3052 and fai.context1 = paa.assignment_action_id
3053 and fai.value = p_state
3054 and paa.tax_unit_id = p_tax_unit_id
3055 and paa.action_status = 'C'
3056 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
3057 'A_W2_STATE_WAGES',
3058 paa.tax_unit_id,
3059 faic.context , 2),0) > 0
3060 and not exists
3061 (
3062 select 'x'
3063 from hr_organization_information hoi
3064 WHERE hoi.organization_id = paa.tax_unit_id
3065 and hoi.org_information_context ='1099R Magnetic Report Rules'
3066 ) ;
3067
3068 lv_employee_count NUMBER ;
3069
3070 CURSOR cur_summed_balance(p_payroll_action_id number,
3071 p_tax_unit_id number,
3072 p_state varchar2) IS
3073 SELECT fdi1.user_name,
3074 sum(to_number(nvl(fai1.value,'0')))
3075 FROM ff_archive_item_contexts faic,
3076 ff_archive_items fai,
3077 ff_database_items fdi,
3078 pay_assignment_actions paa,
3079 pay_payroll_actions ppa,
3080 ff_archive_items fai1,
3081 ff_database_items fdi1,
3082 ff_archive_item_contexts faic1,
3083 ff_contexts fc,
3084 pay_us_states pus
3085 WHERE
3086 ppa.payroll_action_id = p_payroll_action_id
3087 and ppa.payroll_action_id = paa.payroll_action_id
3088 and fdi.user_name = 'A_STATE_ABBREV'
3089 and fdi.user_entity_id = fai.user_entity_id
3090 and fai.archive_item_id = faic.archive_item_id
3091 and fai.context1 = paa.assignment_action_id
3092 and fai.value = p_state
3093 and paa.tax_unit_id = p_tax_unit_id
3094 and paa.action_status = 'C'
3095 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
3096 'A_W2_STATE_WAGES',
3097 paa.tax_unit_id,
3098 faic.context , 2),0) > 0
3099 and not exists
3100 (
3101 select 'x'
3102 from hr_organization_information hoi
3103 WHERE hoi.organization_id = paa.tax_unit_id
3104 and hoi.org_information_context ='1099R Magnetic Report Rules'
3105 )
3106
3107 and fdi1.user_name in (
3108 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
3109 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
3110 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
3111 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD',
3112 'A_W2_STATE_PICKUP_PER_GRE_YTD')
3113 and fdi1.user_entity_id = fai1.user_entity_id
3114 and fai1.context1 = paa.assignment_action_id
3115 and fai1.archive_item_id = faic1.archive_item_id
3116 and (
3117 (
3118 faic1.context_id = fc.context_id
3119 and fc.context_name = 'JURISDICTION_CODE'
3120 and substr(faic1.context,1,2) = pus.state_code
3121 and pus.state_abbrev = p_state
3122 )
3123 or
3124 not exists (select 'x'
3125 from ff_archive_items fai2,
3126 ff_archive_item_contexts faic2,
3127 ff_contexts fc2
3128 where fai2.user_entity_id = fdi1.user_entity_id
3129 and fai2.context1 = fai1.context1
3130 and fai2.archive_item_id = fai1.archive_item_id
3131 and fai2.archive_item_id = faic2.archive_item_id
3132 and faic2.context_id = fc2.context_id
3133 and fc2.context_name = 'JURISDICTION_CODE')
3134 )
3135 group by fdi1.user_name ;
3136
3137 TYPE balance_rec IS RECORD ( dbi_name VARCHAR2(200),
3138 summed_balance_value NUMBER ) ;
3139 TYPE w2_bal_tab IS TABLE OF balance_rec INDEX BY BINARY_INTEGER ;
3140 lv_tot_wage_tab w2_bal_tab ;
3141 i NUMBER ;
3142
3143 BEGIN
3144
3145 hr_utility.trace('Inside pay_us_reporting_utils_pkg.get_total_wages') ;
3146 hr_utility.trace('p_payroll_action_id := '||p_payroll_action_id) ;
3147 hr_utility.trace('p_tax_unit_id := ' || p_tax_unit_id) ;
3148 hr_utility.trace('p_state := ' || p_state) ;
3149 hr_utility.trace('p_report_type := ' || p_report_type) ;
3150 hr_utility.trace('p_balance_name := ' || p_balance_name) ;
3151
3152 lv_total_wages := 0 ;
3153 lv_employee_count := 0 ;
3154
3155 IF p_report_type = 'SQWL' THEN
3156
3157 IF p_state = 'MI' or p_state='NM' THEN
3158
3159 select sum(to_number(nvl(fai.value, '0')) - to_number(nvl(fai1.value, '0')))
3160 into lv_total_wages
3161 from pay_payroll_actions ppa,
3162 pay_assignment_actions paa,
3163 ff_archive_items fai,
3164 ff_archive_items fai1,
3165 ff_database_items fdi,
3166 ff_database_items fdi1
3167 where ppa.payroll_action_id = p_payroll_action_id
3168 and ppa.payroll_action_id = paa.payroll_action_id
3169 and paa.tax_unit_id = p_tax_unit_id
3170 and fai.context1 = paa.assignment_action_id
3171 and fai.user_entity_id = fdi.user_entity_id
3172 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
3173 and fai1.context1 = fai.context1
3174 and fai1.user_entity_id = fdi1.user_entity_id
3175 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD' ;
3176
3177 RETURN lv_total_wages ;
3178 END IF ;
3179
3180 ELSIF p_report_type = 'W2' THEN
3181 IF p_state = 'MD' THEN
3182 OPEN cur_summed_balance(p_payroll_action_id,
3183 p_tax_unit_id,
3184 p_state) ;
3185 i := 0 ;
3186 LOOP
3187 i := i + 1 ;
3188 FETCH cur_summed_balance INTO lv_tot_wage_tab(i).dbi_name,
3189 lv_tot_wage_tab(i).summed_balance_value;
3190
3191 EXIT WHEN cur_summed_balance%NOTFOUND ;
3192
3193 END LOOP ;
3194 CLOSE cur_summed_balance ;
3195
3196 IF p_balance_name = 'SIT Withheld' THEN
3197 FOR j IN 1..(i - 1)
3198 LOOP
3199 IF lv_tot_wage_tab(j).dbi_name = 'A_SIT_WITHHELD_PER_JD_GRE_YTD' THEN
3200 lv_total_wages := lv_tot_wage_tab(j).summed_balance_value ;
3201 EXIT ;
3202 END IF ;
3203 END LOOP ;
3204 hr_utility.trace('p_balance_name := '||p_balance_name) ;
3205 hr_utility.trace('lv_total_wages := '||lv_total_wages) ;
3206
3207 RETURN lv_total_wages ;
3208
3209 ELSIF p_balance_name = 'State taxable Wages' THEN
3210 FOR j IN 1..(i - 1)
3211 LOOP
3212 IF lv_tot_wage_tab(j).dbi_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' THEN
3213 lv_total_wages := lv_total_wages + lv_tot_wage_tab(j).summed_balance_value ;
3214 ELSIF lv_tot_wage_tab(j).dbi_name = 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD' THEN
3215 lv_total_wages := lv_total_wages + lv_tot_wage_tab(j).summed_balance_value ;
3216 ELSIF lv_tot_wage_tab(j).dbi_name = 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD' THEN
3217 lv_total_wages := lv_total_wages - lv_tot_wage_tab(j).summed_balance_value ;
3218 END IF ;
3219 END LOOP ;
3220 hr_utility.trace('p_balance_name := '||p_balance_name) ;
3221 hr_utility.trace('lv_total_wages := '||lv_total_wages) ;
3222
3223 RETURN lv_total_wages ;
3224
3225 ELSIF p_balance_name = 'Other State Data' THEN
3226 FOR j IN 1..(i - 1)
3227 LOOP
3228 IF lv_tot_wage_tab(j).dbi_name = 'A_W2_STATE_PICKUP_PER_GRE_YTD' THEN
3229 lv_total_wages := lv_tot_wage_tab(j).summed_balance_value ;
3230 EXIT ;
3231 END IF ;
3232 END LOOP ;
3233 hr_utility.trace('p_balance_name := '||p_balance_name) ;
3234 hr_utility.trace('lv_total_wages := '||lv_total_wages) ;
3235
3236 RETURN lv_total_wages ;
3237 END IF ; -- p_balance_name
3238 END IF ; -- p_state
3239
3240 IF p_balance_name = 'Employee Count' THEN
3241 IF p_state = 'MD' THEN
3242 OPEN cur_employee_count(p_payroll_action_id,
3243 p_tax_unit_id,
3244 p_state) ;
3245 FETCH cur_employee_count INTO lv_employee_count ;
3246 CLOSE cur_employee_count ;
3247
3248 RETURN lv_employee_count ;
3249 END IF ;
3250 END IF ;
3251 END IF ; -- p_report_type
3252
3253 END get_total_wages ;
3254
3255 /* sackumar */
3256
3257 function get_wages(p_payroll_action_id number, --context
3258 p_tax_unit_id number, --context
3259 p_state varchar2,
3260 p_excess_wages out nocopy number,
3261 p_withholding out nocopy number,
3262 p_workerscomp out nocopy number
3263 ) return number IS
3264 BEGIN
3265 select nvl(sum(to_number(nvl(fai.value, '0'))
3266 - to_number(nvl(fai1.value, '0'))
3267 - to_number(nvl(fai2.value, '0'))
3268 ),0)
3269 into p_excess_wages
3270 from pay_payroll_actions ppa,
3271 pay_assignment_actions paa,
3272 ff_archive_items fai,
3273 ff_archive_items fai1,
3274 ff_archive_items fai2,
3275 ff_database_items fdi,
3276 ff_database_items fdi1,
3277 ff_database_items fdi2
3278 where ppa.payroll_action_id = p_payroll_action_id
3279 and ppa.payroll_action_id = paa.payroll_action_id
3280 and paa.tax_unit_id = p_tax_unit_id
3281 and fai.context1 = paa.assignment_action_id
3282 and fai.user_entity_id = fdi.user_entity_id
3283 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
3284 and fai1.context1 = fai.context1
3285 and fai1.user_entity_id = fdi1.user_entity_id
3286 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
3287 and fai2.context1 = fai1.context1
3288 and fai2.user_entity_id = fdi2.user_entity_id
3289 and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD' ;
3290
3291 select nvl(sum(to_number(nvl(fai.value, '0'))),0)
3292 into p_withholding
3293 from pay_payroll_actions ppa,
3294 pay_assignment_actions paa,
3295 ff_archive_items fai,
3296 ff_database_items fdi
3297 where ppa.payroll_action_id = p_payroll_action_id
3298 and ppa.payroll_action_id = paa.payroll_action_id
3299 and paa.tax_unit_id = p_tax_unit_id
3300 and fai.context1 = paa.assignment_action_id
3301 and fai.user_entity_id = fdi.user_entity_id
3302 and fdi.user_name = 'A_SIT_WITHHELD_PER_JD_GRE_QTD';
3303
3304 select nvl(sum(to_number(nvl(fai.value, '0'))
3305 + to_number(nvl(fai1.value, '0'))
3306 + to_number(nvl(fai2.value, '0'))
3307 ),0)
3308 into p_workerscomp
3309 from pay_payroll_actions ppa,
3310 pay_assignment_actions paa,
3311 ff_archive_items fai,
3312 ff_archive_items fai1,
3313 ff_archive_items fai2,
3314 ff_database_items fdi,
3315 ff_database_items fdi1,
3316 ff_database_items fdi2
3317 where ppa.payroll_action_id = p_payroll_action_id
3318 and ppa.payroll_action_id = paa.payroll_action_id
3319 and paa.tax_unit_id = p_tax_unit_id
3320 and fai.context1 = paa.assignment_action_id
3321 and fai.user_entity_id = fdi.user_entity_id
3322 and fdi.user_name = 'A_WORKERS_COMPENSATION2_ER_PER_JD_GRE_QTD'
3323 and fai1.context1 = fai.context1
3324 and fai1.user_entity_id = fdi1.user_entity_id
3325 and fdi1.user_name = 'A_WORKERS_COMP2_WITHHELD_PER_JD_GRE_QTD'
3326 and fai2.context1 = fai1.context1
3327 and fai2.user_entity_id = fdi2.user_entity_id
3328 and fdi2.user_name = 'A_WORKERS_COMP_WITHHELD_PER_JD_GRE_QTD' ;
3329
3330 return(0);
3331 END get_wages ;
3332
3333 FUNCTION GET_SUI_WAGES(p_payroll_action_id number, --context
3334 p_tax_unit_id number, --context
3335 p_state varchar2,
3336 p_sui_gross out nocopy number,
3337 p_sui_subj out nocopy number,
3338 p_sui_pre_tax out nocopy number,
3339 p_sui_taxable out nocopy number
3340 )return number is
3341 BEGIN
3342 select nvl(sum(to_number(nvl(fai.value, '0'))),0),
3343 nvl(sum(to_number(nvl(fai1.value, '0'))),0),
3344 nvl(sum(to_number(nvl(fai2.value, '0'))),0),
3345 nvl(sum(to_number(nvl(fai3.value, '0'))),0)
3346 into p_sui_subj,
3347 p_sui_pre_tax,
3348 p_sui_taxable,
3349 p_sui_gross
3350 from pay_payroll_actions ppa,
3351 pay_assignment_actions paa,
3352 ff_archive_items fai,
3353 ff_archive_items fai1,
3354 ff_archive_items fai2,
3355 ff_archive_items fai3,
3356 ff_database_items fdi,
3357 ff_database_items fdi1,
3358 ff_database_items fdi2,
3359 ff_database_items fdi3
3360 where ppa.payroll_action_id = p_payroll_action_id
3361 and ppa.payroll_action_id = paa.payroll_action_id
3362 and paa.tax_unit_id = p_tax_unit_id
3363 and fai.context1 = paa.assignment_action_id
3364 and fai.user_entity_id = fdi.user_entity_id
3365 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
3366 and fai1.context1 = fai.context1
3367 and fai1.user_entity_id = fdi1.user_entity_id
3368 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
3369 and fai2.context1 = fai1.context1
3370 and fai2.user_entity_id = fdi2.user_entity_id
3371 and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD'
3372 and fai3.context1 = fai1.context1
3373 and fai3.user_entity_id = fdi3.user_entity_id
3374 and fdi3.user_name = 'A_SUI_ER_GROSS_PER_JD_GRE_QTD'
3375 and length(translate(trim(fai.value),' .0123456789',' ')) is null
3376 and length(translate(trim(fai2.value),' .0123456789',' ')) is null ;
3377 return(0);
3378
3379 END GET_SUI_WAGES;
3380 --BEGIN
3381 --hr_utility.trace_on(null,'MMREF');
3382
3383 END pay_us_reporting_utils_pkg;