[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.18.12020000.3 2012/07/05 03:26:07 amnaraya 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 23-Mar-2010 emunisek 115.113 9356178 Modified GET_SUI_WAGES function to adjust
273 the Florida Taxable Wages as per the new filing
274 requirement.
275 25-Mar-2011 sjawid 115.115 10649380 Modified the cursor c_sqwl_Cursor to add GRE id/or BG id to
276 to SQWL file names.
277 3-May-2011 rosuri 115.117 12322280 Modified GET_SUI_WAGES function to adjust
278 the Ohio Taxable Wages as per the new filing
279 requirement.
280 29-Jun-2011 rosuri 115.118 12322280 Rounded p_sui_taxable to 2 decimal places
281 and multiplied by 100 in GET_SUI_WAGES function
282 10-Nov-2011 sgotlasw 115.119 13351713 Updated Format_Record Function with an Optional Input
283 Parameter p_input_45 for 'A_W2_HEALTH_COVERAGE_PER_GRE_YTD'
284 08-Feb-2012 skchalla 115.120 13693872 Modified the Cursor cur_summed_balance, to eliminated the duplicates.
285 08-Feb-2012 skchalla 115.121 13693872 Modified the logic specifically for MD state pick up and removed the distinct
286 ============================================================================*/
287 -- Global Variable
288
289 g_number NUMBER;
290 l_return varchar2(100);
291 end_date date := to_date('31/12/4712','DD/MM/YYYY');
292
293 -- Used by Magnetic W2 (MMREF format).
294 /* ============================================================================ */
295 /* Function Name : calculate_balance
296 Purpose : Purpose of this function is is to provide calculation
297 of Derived balnces that are used in the formula
298 Error checking
299
300 Special Note :
301 */
302
303 FUNCTION calculate_balance(
304 p_effective_date IN varchar2,
305 p_balance_name IN varchar2,
306 p_report_type IN varchar2,
307 p_format IN varchar2,
308 p_report_qualifier IN varchar2,
309 p_record_name IN varchar2,
310 p_input_1 IN varchar2,
311 p_input_2 IN varchar2,
312 p_input_3 IN varchar2,
313 p_input_4 IN varchar2,
314 p_input_5 IN varchar2,
315 p_input_6 IN varchar2,
316 p_input_7 IN varchar2,
317 p_input_8 IN varchar2,
318 p_input_9 IN varchar2,
319 p_input_10 IN varchar2,
320 p_input_11 IN varchar2,
321 p_input_12 IN varchar2,
322 p_input_13 IN varchar2,
323 p_input_14 IN varchar2,
324 p_input_15 IN varchar2,
325 p_validate IN varchar2,
326 p_exclude_from_output out nocopy varchar2,
327 sp_out_1 out nocopy varchar2,
328 sp_out_2 out nocopy varchar2,
329 sp_out_3 out nocopy varchar2,
330 sp_out_4 out nocopy varchar2,
331 sp_out_5 out nocopy varchar2
332 ) RETURN number IS
333 /* LOCAL Varialbe Declaration */
334
335 l_calculated_value number ;
336 Begin
337 null;
338 return 0;
339
340 End calculate_balance;
341
342 /* Function Name : calculate_wages
343 Purpose : Purpose of this function is is to provide calcula
344 tion
345 of wages that are used in the formula
346 Error checking
347
348 Special Note :
349
350 */
351
352 FUNCTION calculate_wages(
353 p_effective_date IN varchar2,
354 p_wage_name IN varchar2,
355 p_report_type IN varchar2,
356 p_format IN varchar2,
357 p_report_qualifier IN varchar2,
358 p_record_name IN varchar2,
359 p_input_1 IN varchar2,
360 p_gross IN varchar2,
361 p_subject IN varchar2,
362 p_subject_nw IN varchar2,
363 p_pretax_redns IN varchar2,
364 p_taxable IN varchar2,
365 p_validate IN varchar2,
366 p_exclude_from_output out nocopy varchar2,
367 sp_exempt out nocopy varchar2,
368 sp_reduced_sub out nocopy varchar2,
369 sp_excess out nocopy varchar2,
370 sp_reduced_sub_wh out nocopy varchar2,
371 sp_out_1 out nocopy varchar2
372 )
373 RETURN number IS
374
375 return_wage number(10):=100;
376 l_error boolean;
377 l_message varchar2(300);
378 l_description varchar2(50);
379
380 BEGIN
381
382 null;
383
384 RETURN '0';
385
386 END;
387
388
389
390
391
392 /* --------------------------------------------------------------
393 Name : get_item_data
394
395 Purpose : Purpose of this function is to get live
396 data from the System.This can be replace
397 Call to live database items where error
398 checking is required.
399
400 Parameters :
401 p_effective_date -
402 This parameter indicates the year for the function.
403 p_item_name -
404 This parameter indicates the name of the item
405 to be calculated.
406 'EE_ADDRESS' - Employee address
407 'ER_ADDRESS' - Employer Address
408 'CR_ADDRESS' - Company's Address
409 'CR_PERSON' -Contact Persons details in the Submitter record.
410 'CS_PERSON' - Contact person details title. -vmkulkar
411 p_report_type -
412 This parameter will have the type of the report.
413 eg: 'W2'
414 p_format -
415 This parameter will have the format to be printed
416 on W2. eg:'MMREF'
417 p_record_name -
418 This parameter will have the particular
419 record name. eg: RA,RF,RE,RT,RSSUMM etc.
420 p_validate -
421 This parameter will check whether it wants to
422 validate the error condition or override the checking.
423 'N'- Override
424 'Y'- Check
425 p_exclude_from_output -
426 This parameter gives the information on
427 whether the record has to be printed or not.
428 'Y'- Do not print.
429 'N'- Print.
430 sp_out_1 -
431 This out parameter returns Location address.
432 sp_out_2 -
433 This out parameter returns Delivery address.
434 sp_out_3 -
435 This out parameter returns Town_or_city.
436 sp_out_4 -
437 This out parameter returns State abbreviation.
438 sp_out_5 -
439 This out parameter returns Postal_code.
440 sp_out_6 -
441 This out parameter returns zip code extension.
442 sp_out_7 -
443 This out parameter returns foreign state/province.
444 sp_out_8 -
445 This out parameter returns foreign postal_code.
446 sp_out_9 -
447 This out parameter returns Country_code.
448 sp_out_10 -
449 This parameter is returns the organization name or employee number.
450
451
452
453 Error checking
454
455 Special Note :
456
457
458 ---------------------------------------------------------------- */
459
460 FUNCTION get_item_data(
461 p_assignment_id number, --context
462 p_date_earned date, --context
463 p_tax_unit_id number, --context
464 p_effective_date IN varchar2,
465 p_item_name IN varchar2,
466 p_report_type IN varchar2,
467 p_format IN varchar2,
468 p_report_qualifier IN varchar2,
469 p_record_name IN varchar2,
470 p_input_1 IN varchar2,
471 p_input_2 IN varchar2,
472 p_input_3 IN varchar2,
473 p_input_4 IN varchar2,
474 p_input_5 IN varchar2,
475 p_validate IN varchar2,
476 p_exclude_from_output OUT nocopy varchar2,
477 sp_out_1 OUT nocopy varchar2,
478 sp_out_2 OUT nocopy varchar2,
479 sp_out_3 OUT nocopy varchar2,
480 sp_out_4 OUT nocopy varchar2,
481 sp_out_5 OUT nocopy varchar2,
482 sp_out_6 OUT nocopy varchar2,
483 sp_out_7 OUT nocopy varchar2,
484 sp_out_8 OUT nocopy varchar2,
485 sp_out_9 OUT nocopy varchar2,
486 sp_out_10 OUT nocopy varchar2
487 ) RETURN VARCHAR2 IS
488 -- Declaration of Local Variables
489 --
490 l_input_1 varchar2(50);
491 l_valid_address boolean;
492 c_item_name varchar2(40);
493 c_tax_unit_id hr_all_organization_units.organization_id%TYPE;
494
495 BEGIN
496 hr_utility.trace('Start of GET_ITEM_DATA ');
497 hr_utility.trace('Parameter Input Values ...');
498 hr_utility.trace('Ccontext value p_assignemnt_id = '
499 || to_char(p_assignment_id));
500 hr_utility.trace(' p_date_earned = '
501 ||p_date_earned);
502 hr_utility.trace(' p_tax_unit_id is = '
503 || to_char(p_tax_unit_id));
504 hr_utility.trace('p_effective_date is : '|| p_effective_date);
505 hr_utility.trace('p_item_name is : '|| p_item_name);
506 hr_utility.trace('p_report_type is : '|| p_report_type);
507 hr_utility.trace('p_format is : '|| p_format);
508 hr_utility.trace('p_report_qualifier is : '|| p_report_qualifier );
509 hr_utility.trace('p_record_name is : '|| p_record_name );
510 hr_utility.trace('p_input_1 is : '|| p_input_1 );
511 hr_utility.trace('p_input_2 is : '|| p_input_2 );
512 hr_utility.trace('p_input_3 is : '|| p_input_3 );
513 hr_utility.trace('p_input_4 is : '|| p_input_4 );
514 hr_utility.trace('p_input_5 is : '|| p_input_5 );
515 hr_utility.trace('p_validate is : '|| p_validate );
516
517 -- p_item_name parameter is checked to decide which
518 -- procedure to call for Address or contact info
519 --
520 IF p_item_name = 'CR_ADDRESS' THEN --p_item_name
521 l_input_1:=replace(p_input_1,' ');
522 IF l_input_1 IS NULL THEN
523 hr_utility.trace('p_input_1 is null');
524 c_item_name := NULL;
525 l_valid_address := FALSE;
526 ELSE -- l_input_1 IS NOT NULL THEN
527 hr_utility.trace('p_input_1 is not null');
528 c_tax_unit_id := to_number(p_input_1);
529 c_item_name := 'ER_ADDRESS';
530 END IF;
531 ELSIF p_item_name = 'ER_ADDRESS' THEN
532 hr_utility.trace(p_item_name);
533 c_tax_unit_id := p_tax_unit_id;
534 c_item_name := 'ER_ADDRESS';
535 hr_utility.trace('c_tax_unit_id = '||to_char(c_tax_unit_id));
536 ELSIF p_item_name = 'EE_ADDRESS' THEN
537 c_item_name:='EE_ADDRESS';
538 ELSIF p_item_name = 'CR_PERSON' THEN
539 c_item_name:='CR_PERSON';
540 l_valid_address:=FALSE;
541 ELSIF p_item_name = 'CS_PERSON' THEN
542 c_item_name:='CS_PERSON';
543 l_valid_address:=FALSE;
544 END IF; --p_item_name
545
546 IF c_item_name = 'CR_PERSON' THEN --c_item_name
547 --
548 -- Following function is called to fetch Contact Person info
549 --
550 hr_utility.trace('Calling get_cr_person_info');
551 sp_out_1 := pay_us_get_item_data_pkg.get_contact_person_info(
552 p_assignment_id,
553 p_date_earned,
554 p_tax_unit_id,
555 p_effective_date,
556 c_item_name,
557 p_report_type,
558 p_format,
559 p_report_qualifier,
560 p_record_name,
561 p_input_1,
562 p_validate,
563 p_exclude_from_output,
564 sp_out_1,
565 sp_out_2,
566 sp_out_3,
567 sp_out_4,
568 sp_out_5,
569 sp_out_6,
570 sp_out_7,
571 sp_out_8
572 );
573
574 ELSIF c_item_name = 'CS_PERSON' THEN
575 -- vmkulkar
576 -- Following function is called to fetch Contact Person Title
577 --
578 -- vmkulkar - Contact person Title should we displayed in the MD RV Record.
579 -- So using p_contact_prsn_email(out4) for passing TITLE back to the formula.
580 -- ITEM NAME used is 'CS_PERSON'
581
582 sp_out_1 := pay_us_get_item_data_pkg.get_contact_person_info(
583 p_assignment_id,
584 p_date_earned,
585 p_tax_unit_id,
586 p_effective_date,
587 c_item_name,
588 p_report_type,
589 p_format,
590 p_report_qualifier,
591 p_record_name,
592 p_input_1,
593 p_validate,
594 p_exclude_from_output,
595 sp_out_1,
596 sp_out_2,
597 sp_out_3,
598 sp_out_4,
599 sp_out_5,
600 sp_out_6,
601 sp_out_7,
602 sp_out_8
603 );
604
605 ELSIF c_item_name = 'EE_ADDRESS' THEN
606 --
607 -- Following function is called to fetch Employee Address
608 --
609 hr_utility.trace('EE_ADDRESS Calling get_mmref_employee_address');
610 sp_out_1 := pay_us_get_item_data_pkg.get_mmref_employee_address(
611 p_assignment_id,
612 p_date_earned,
613 p_tax_unit_id,
614 p_effective_date,
615 p_item_name,
616 p_report_type,
617 p_format,
618 p_report_qualifier,
619 p_record_name,
620 p_input_1,
621 p_input_2,
622 p_input_3,
623 p_input_4,
624 p_input_5,
625 p_validate,
626 p_exclude_from_output,
627 sp_out_1,
628 sp_out_2,
629 sp_out_3,
630 sp_out_4,
631 sp_out_5,
632 sp_out_6,
633 sp_out_7,
634 sp_out_8,
635 sp_out_9,
636 sp_out_10
637 );
638 hr_utility.trace('EE_ADDRESS get_mmref_employee_address completed sucessfully');
639 ELSIF c_item_name = 'ER_ADDRESS' THEN
640 --
641 -- Following function is called to fetch Employer Address
642 --
643 hr_utility.trace('ER_ADDRESS Calling get_mmref_employer_address');
644 sp_out_1 := pay_us_get_item_data_pkg.get_mmref_employer_address(
645 p_assignment_id,
646 p_date_earned,
647 p_tax_unit_id,
648 p_effective_date,
649 p_item_name,
650 p_report_type,
651 p_format,
652 p_report_qualifier,
653 p_record_name,
654 p_input_1,
655 p_input_2,
656 p_input_3,
657 p_input_4,
658 p_input_5,
659 p_validate,
660 p_exclude_from_output,
661 sp_out_1,
662 sp_out_2,
663 sp_out_3,
664 sp_out_4,
665 sp_out_5,
666 sp_out_6,
667 sp_out_7,
668 sp_out_8,
669 sp_out_9,
670 sp_out_10
671 );
672 hr_utility.trace('ER_ADDRESS get_mmref_employer_address completed sucessfully');
673 END IF; --c_item_name
674
675 IF p_exclude_from_output IS NULL THEN
676 p_exclude_from_output := 'N';
677 END IF;
678 --
679 -- Following checks are made to eliminate unwanted chanracters
680 -- from the output values
681
682 sp_out_1 := Character_check(sp_out_1);
683 sp_out_2 := Character_check(sp_out_2);
684 sp_out_3 := Character_check(sp_out_3);
685 sp_out_5 := Character_check(sp_out_5);
686 sp_out_6 := Character_check(sp_out_6);
687 sp_out_7 := Character_check(sp_out_7);
688 sp_out_8 := Character_check(sp_out_8);
689 sp_out_9 := Character_check(sp_out_9);
690 /* Email address should not under go Character checking.*/
691 IF p_item_name <> 'CR_PERSON' THEN
692 sp_out_4 := Character_check(sp_out_4);
693 END IF;
694
695 hr_utility.trace('Return Values of Get_Item_Data ..');
696 hr_utility.trace('Value of sp_out_1 = '||sp_out_1);
697 hr_utility.trace('Value of sp_out_2 = '||sp_out_2);
698 hr_utility.trace('Value of sp_out_3 = '||sp_out_3);
699 hr_utility.trace('Value of sp_out_4 = '||sp_out_4);
700 hr_utility.trace('Value of sp_out_5 = '||sp_out_5);
701 hr_utility.trace('Value of sp_out_6 = '||sp_out_6);
702 hr_utility.trace('Value of sp_out_7 = '||sp_out_7);
703 hr_utility.trace('Value of sp_out_8 = '||sp_out_8);
704 hr_utility.trace('Value of sp_out_9 = '||sp_out_9);
705 hr_utility.trace('Value of sp_out_10 = '||sp_out_10);
706
707 hr_utility.trace('Befor the final return. Length of the fields. ');
708 hr_utility.trace('length of sp_out_1 = '||to_char(length(sp_out_1)));
709 hr_utility.trace('length of sp_out_2 = '||to_char(length(sp_out_2)));
710 hr_utility.trace('length of sp_out_3 = '||to_char(length(sp_out_3)));
711 hr_utility.trace('length of sp_out_4 = '||to_char(length(sp_out_4)));
712 hr_utility.trace('length of sp_out_5 = '||to_char(length(sp_out_5)));
713 hr_utility.trace('length of sp_out_6 = '||to_char(length(sp_out_6)));
714 hr_utility.trace('length of sp_out_7 = '||to_char(length(sp_out_7)));
715 hr_utility.trace('length of sp_out_8 = '||to_char(length(sp_out_8)));
716 hr_utility.trace('length of sp_out_9 = '||to_char(length(sp_out_9)));
717 hr_utility.trace('length of sp_out_10 = '||to_char(length(sp_out_10)));
718
719 RETURN sp_out_1;
720 END; -- End of function GET_ITEM_DATA
721
722
723 /* -------------------------------------------------------------
724 Function Name : print_record_header
725 Purpose : Function will return the String for header
726 or title line for the Table or table heading
727 related to record for printing in audit files
728
729 Error checking
730
731 Special Note :
732
733 -------------------------------------------------------------- */
734
735 FUNCTION print_record_header(
736 p_effective_date IN varchar2,
737 p_report_type IN varchar2,
738 p_format IN varchar2,
739 p_report_qualifier IN varchar2,
740 p_record_name IN varchar2,
741 p_input_1 IN varchar2,
742 p_input_2 IN varchar2,
743 p_input_3 IN varchar2,
744 p_input_4 IN varchar2,
745 p_input_5 IN varchar2,
746 p_validate IN varchar2,
747 p_exclude_from_output OUT nocopy varchar2,
748 sp_out_1 OUT nocopy varchar2,
749 sp_out_2 OUT nocopy varchar2,
750 sp_out_3 OUT nocopy varchar2,
751 sp_out_4 OUT nocopy varchar2,
752 sp_out_5 OUT nocopy varchar2,
753 sp_out_6 OUT nocopy varchar2,
754 sp_out_7 OUT nocopy varchar2,
755 sp_out_8 OUT nocopy varchar2,
756 sp_out_9 OUT nocopy varchar2,
757 sp_out_10 OUT nocopy varchar2
758 ) RETURN VARCHAR2
759 IS
760
761 header_string varchar2(3000);
762 return_header_string varchar2(2000);
763
764 l_header_2 varchar2(900);
765 l_header_3 varchar2(900);
766 l_header_4 varchar2(900);
767 l_header_5 varchar2(900);
768 l_header_8 varchar2(900);
769 l_header_9 varchar2(900);
770
771 l_header_20 varchar2(900);
772 l_header_21 varchar2(900);
773 l_report_format varchar2(15);
774 l_header_29 varchar2(900);
775 l_header_34 varchar2(900);
776 l_name_header varchar2(900);
777 l_records varchar2(900);
778
779
780 BEGIN
781 l_report_format := p_input_1;
782 hr_utility.trace('Begin Checking ');
783 IF p_format = 'MMREF' THEN
784 IF p_record_name = 'RA' THEN
785 hr_utility.trace('RA record');
786 header_string :=
787 pay_us_mmrf_print_rec_header.mmrf_format_ra_record_header(
788 p_report_type,
789 p_format,
790 p_report_qualifier,
791 p_record_name
792 );
793 ELSIF p_record_name = 'RE' THEN --1028 length
794 hr_utility.trace('RE record');
795 header_string :=
796 pay_us_mmrf_print_rec_header.mmrf_format_re_record_header(
797 p_report_type,
798 p_format,
799 p_report_qualifier,
800 p_record_name
801 );
802 ELSIF p_record_name = 'RW' THEN
803 hr_utility.trace('RW record'); --2189 length
804 header_string :=
805 pay_us_mmrf_print_rec_header.mmrf_format_rw_record_header(
806 p_report_type,
807 p_format,
808 p_report_qualifier,
809 p_record_name
810 );
811 ELSIF p_record_name = 'RO' THEN
812 hr_utility.trace('RO record'); --1398 length
813 header_string:=
814 pay_us_mmrf_print_rec_header.mmrf_format_ro_record_header(
815 p_report_type,
816 p_format,
817 p_report_qualifier,
818 p_record_name
819 );
820 ELSIF p_record_name = 'RS' THEN
821 hr_utility.trace('RS record'); -- 1715 length
822 IF p_report_type = 'W2' THEN
823 header_string:=
824 pay_us_mmrf_print_rec_header.mmrf_format_w2_rs_rec_header(
825 p_report_type,
826 p_format,
827 p_report_qualifier,
828 p_record_name,
829 p_input_1
830 );
831 ELSIF p_report_type = 'SQWL' THEN
832 header_string:=
833 pay_us_mmrf_print_rec_header.mmrf_format_sqwl_rs_rec_header(
834 p_report_type,
835 p_format,
836 p_report_qualifier,
837 p_record_name,
838 p_input_1
839 );
840 END IF;
841 ELSIF p_record_name = 'RSSUMM' THEN
842 hr_utility.trace('RSSUMM record'); -- 1503 length
843 IF p_report_type = 'SQWL' THEN
844 header_string:=
845 pay_us_mmrf_print_rec_header.mmrf_format_rssumm_rec_header(
846 p_report_type,
847 p_format,
848 p_report_qualifier,
849 p_record_name
850 );
851 END IF;
852 ELSIF p_record_name = 'RT' THEN
853 hr_utility.trace('RT record'); -- 1503 length
854 header_string:=
855 pay_us_mmrf_print_rec_header.mmrf_format_rt_record_header(
856 p_report_type,
857 p_format,
858 p_report_qualifier,
859 p_record_name
860 );
861 ELSIF p_record_name = 'RU' THEN
862 hr_utility.trace('RU record'); -- 1295 length
863 header_string:=
864 pay_us_mmrf_print_rec_header.mmrf_format_rt_record_header(
865 p_report_type,
866 p_format,
867 p_report_qualifier,
868 p_record_name
869 );
870 ELSIF p_record_name = 'RF' THEN
871 hr_utility.trace('RF record');
872 header_string:=
873 pay_us_mmrf_print_rec_header.mmrf_format_rt_record_header(
874 p_report_type,
875 p_format,
876 p_report_qualifier,
877 p_record_name
878 );
879 END IF; /* p_record_name */
880 ELSIF p_format = 'CUSTOM' THEN
881
882 IF p_record_name = 'A' or
883 p_record_name = 'H' or
884 p_record_name = 'D'
885 THEN
886 hr_utility.trace(p_record_name||' record'); -- 117 length
887 IF p_report_type = 'SQWL' THEN
888 header_string:=
889 pay_us_mmrf_print_rec_header.mmrf_format_sqwl_rs_rec_header(
890 p_report_type,
891 p_format,
892 p_report_qualifier,
893 p_record_name,
894 p_input_1
895 );
896 END IF;
897 END IF;
898 END IF; /* p_format */
899 hr_utility.trace('splitting the header string ');
900 return_header_string := substr(header_string,1,200);
901 sp_out_1:=substr(header_string,201,200);
902 sp_out_2:=substr(header_string,401,200);
903 sp_out_3:=substr(header_string,601,200);
904 sp_out_4:=substr(header_string,801,200);
905 sp_out_5:=substr(header_string,1001,200);
906 sp_out_6:=substr(header_string,1201,200);
907 sp_out_7:=substr(header_string,1401,200);
908 sp_out_8:=substr(header_string,1601,200);
909 sp_out_9:=substr(header_string,1801,200);
910 sp_out_10:=substr(header_string,2001,300);
911 p_exclude_from_output:='N';
912 hr_utility.trace('return_header_string = '||return_header_string);
913 hr_utility.trace('sp_out_1:='||sp_out_1);
914 hr_utility.trace('sp_out_2:='||sp_out_2);
915 hr_utility.trace('sp_out_3:='||sp_out_3);
916 hr_utility.trace('sp_out_4:='||sp_out_4);
917 hr_utility.trace('sp_out_5:='||sp_out_5);
918 hr_utility.trace('sp_out_6:='||sp_out_6);
919 hr_utility.trace('sp_out_7:='||sp_out_7);
920 hr_utility.trace('sp_out_8:='||sp_out_8);
921 hr_utility.trace('sp_out_9:='||sp_out_9);
922 hr_utility.trace('sp_out_10:='||sp_out_10);
923
924 RETURN return_header_string;
925 END;
926
927
928
929 /*
930 Name : data_validation
931 : Function will validate data for any
932 database items or can change the data
933 based on the parameters. It should
934 be capable of having special data
935 validation and change function.
936
937 Error checking
938
939 Special Note :
940
941 */
942
943 FUNCTION data_validation(
944 p_effective_date IN varchar2,
945 p_report_type IN varchar2,
946 p_format IN varchar2,
947 p_report_qualifier IN varchar2,
948 p_record_name IN varchar2,
949 p_input_1 IN varchar2,
950 p_input_2 IN varchar2,
951 p_input_3 IN varchar2,
952 p_input_4 IN varchar2,
953 p_input_5 IN varchar2,
954 p_validate IN varchar2,
955 p_exclude_from_output OUT nocopy varchar2,
956 sp_out_1 OUT nocopy varchar2,
957 sp_out_2 OUT nocopy varchar2
958 )
959 return varchar2 IS
960 l_err boolean;
961 return_value varchar2(100);
962 l_length number(10);
963 l_message varchar2(2000);
964 l_number_length number(10);
965 l_description varchar2(50);
966 l_input_2 varchar2(100);
967 l_ssn varchar2(50);
968
969 TYPE special_numbers is record(
970 p_number_set varchar2(50));
971
972 special_number_record special_numbers;
973
974 TYPE ssn_special_number_rec is table
975 of special_number_record%type INDEX BY binary_integer;
976 ssn_check ssn_special_number_rec;
977 BEGIN
978 l_err := FALSE;
979 IF p_input_1 = 'EIN' THEN
980 IF p_report_type = 'SQWL' THEN -- SQWL EIN check
981 return_value :=
982 pay_us_report_data_validation.validate_SQWL_EIN( p_report_qualifier,
983 p_record_name,
984 p_input_2,
985 p_input_4,
986 p_validate,
987 l_err
988 );
989 ELSIF p_report_type IN ('W2','W2C') THEN -- W2 or W2c EIN check
990 return_value :=
991 pay_us_report_data_validation.validate_W2_EIN( p_report_qualifier,
992 p_record_name,
993 p_input_2,
994 p_input_4,
995 p_validate,
996 l_err
997 );
998 END IF; /* SQWL or W2 EIN check */
999 ELSIF p_input_1 = 'SSN' THEN
1000 IF p_report_type = 'SQWL' THEN /*SQWL SSN check*/
1001 return_value :=
1002 pay_us_report_data_validation.validate_SQWL_SSN(p_effective_date,
1003 p_report_type,
1004 p_format,
1005 p_report_qualifier,
1006 p_record_name,
1007 p_input_1,
1008 p_input_2,
1009 p_input_3,
1010 p_input_4,
1011 p_input_5,
1012 p_validate,
1013 l_err
1014 );
1015 ELSIF p_report_type IN ('W2','W2C') THEN /* W2 or W2c SSN check*/
1016 return_value :=
1017 pay_us_report_data_validation.validate_W2_SSN(p_effective_date,
1018 p_report_type,
1019 p_format,
1020 p_report_qualifier,
1021 p_record_name,
1022 p_input_1,
1023 p_input_2,
1024 p_input_3,
1025 p_input_4,
1026 p_input_5,
1027 p_validate,
1028 l_err
1029 );
1030 END IF; -- SQLWL or W2 SSN check
1031 ELSIF p_input_1 = 'NEG_CHECK' THEN
1032 l_input_2 := replace(p_input_2,' ');
1033 IF p_report_type IN ('W2','W2C') THEN
1034 l_length := pay_us_mmrf_w2_format_record.set_req_field_length(
1035 p_report_type,
1036 p_format,
1037 p_report_qualifier,
1038 p_record_name,
1039 p_input_1,
1040 p_input_2,
1041 p_input_3,
1042 p_input_4,
1043 p_input_5
1044 );
1045 ELSIF p_report_type = 'SQWL' THEN
1046 l_length := pay_us_mmrf_sqwl_format_record.set_req_field_length(
1047 p_report_type,
1048 p_format,
1049 p_report_qualifier,
1050 p_record_name,
1051 p_input_1,
1052 p_input_2,
1053 p_input_3,
1054 p_input_4,
1055 p_input_5
1056 );
1057 END IF;
1058 l_number_length:=length(l_input_2);
1059 IF l_number_length > l_length THEN
1060 IF (p_record_name in ('RO','RW','RS','RCW','RCO','RSSUMM') OR
1061 (p_record_name = 'D' AND p_report_qualifier = 'AK_SQWL')) THEN
1062 l_description:=' The number is bigger than the given length '
1063 ||l_length;
1064 pay_core_utils.push_message(801,'PAY_INVALID_EE_DATA','A');
1065 pay_core_utils.push_token('record_name',substr(p_record_name,1,50));
1066 pay_core_utils.push_token('name_or_number',substr(p_input_4,1,50));
1067 pay_core_utils.push_token('field_name',substr(p_input_3,1,50));
1068 pay_core_utils.push_token('description',substr(l_description,1,50));
1069 /* Error in RW record for employee 1234 in Medicare wages.
1070 The number is bigger than the given length 11 */
1071 hr_utility.trace(p_input_3 ||' '||l_description);
1072 END IF;
1073 l_err:=TRUE;
1074 END IF;
1075
1076 hr_utility.trace('l_input_2 = '|| l_input_2);
1077 hr_utility.trace('l_input_2 in number = '|| to_char(to_number(l_input_2)));
1078 hr_utility.trace('l_length = '|| l_length);
1079 hr_utility.trace('After Number length check');
1080 IF to_number(l_input_2) < 0 THEN
1081 return_value:='-'||lpad(nvl(replace(l_input_2,'-'),0),l_length-1,0);
1082 IF( p_record_name in ('RO','RW','RS','RCW','RCO','D','RSSUMM') OR
1083 (p_record_name = 'D' AND p_report_qualifier = 'AK_SQWL')) THEN
1084 l_description:=' The value is negative '||substr(l_input_2,1,l_length);
1085 pay_core_utils.push_message(801,'PAY_INVALID_EE_DATA','A');
1086 pay_core_utils.push_token('record_name',substr(p_record_name,1,50));
1087 pay_core_utils.push_token('name_or_number',substr(p_input_4,1,50));
1088 pay_core_utils.push_token('field_name',substr(p_input_3,1,50));
1089 pay_core_utils.push_token('description',substr(l_description,1,50));
1090
1091 /* sample mesg*/
1092 /* Error in RW record for employee 1234 in Medicare wages.
1093 The value is negative -2345 */
1094 hr_utility.trace(l_description);
1095 END IF;
1096 l_err:=TRUE;
1097 ELSE
1098 hr_utility.trace('l_input_2 = '|| l_input_2);
1099 hr_utility.trace('l_length = '|| l_length);
1100 return_value:=lpad(nvl(replace(l_input_2,'-'),0),l_length,0);
1101 hr_utility.trace('return_value = '|| return_value);
1102 END IF; /* to_number(l_input_2) */
1103 END IF; /* p_input_1 */
1104
1105 hr_utility.trace('Before returning the value = '||return_value);
1106 hr_utility.trace('p_validate = '||p_validate);
1107
1108 IF p_validate= 'Y' THEN
1109 IF l_err THEN
1110 p_exclude_from_output:='Y';
1111 ELSE
1112 p_exclude_from_output:='N';
1113 END IF;
1114 END IF;
1115 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
1116 RETURN return_value;
1117
1118 END DATA_VALIDATION;
1119
1120
1121 /* ---------------------------------------------------------------
1122 Function Name : format_record
1123 Purpose : Function will return formating of the record
1124 there will be one function per record
1125 Error checking
1126
1127 Special Note :
1128
1129 parameters : p_input_1
1130 This parameter will have the value for
1131 transfer employee for
1132 'FED' in jurisdiction
1133 'W2' in p_report_type
1134
1135
1136
1137 -------------------------------------------------------------------- */
1138
1139 --
1140
1141 FUNCTION format_record(
1142 p_effective_date IN varchar2,
1143 p_report_type IN varchar2,
1144 p_format IN varchar2,
1145 p_report_qualifier IN varchar2,
1146 p_record_name IN varchar2,
1147 p_input_1 IN varchar2,
1148 p_input_2 IN varchar2,
1149 p_input_3 IN varchar2,
1150 p_input_4 IN varchar2,
1151 p_input_5 IN varchar2,
1152 p_input_6 IN varchar2,
1153 p_input_7 IN varchar2,
1154 p_input_8 IN varchar2,
1155 p_input_9 IN varchar2,
1156 p_input_10 IN varchar2,
1157 p_input_11 IN varchar2,
1158 p_input_12 IN varchar2,
1159 p_input_13 IN varchar2,
1160 p_input_14 IN varchar2,
1161 p_input_15 IN varchar2,
1162 p_input_16 IN varchar2,
1163 p_input_17 IN varchar2,
1164 p_input_18 IN varchar2,
1165 p_input_19 IN varchar2,
1166 p_input_20 IN varchar2,
1167 p_input_21 IN varchar2,
1168 p_input_22 IN varchar2,
1169 p_input_23 IN varchar2,
1170 p_input_24 IN varchar2,
1171 p_input_25 IN varchar2,
1172 p_input_26 IN varchar2,
1173 p_input_27 IN varchar2,
1174 p_input_28 IN varchar2,
1175 p_input_29 IN varchar2,
1176 p_input_30 IN varchar2,
1177 p_input_31 IN varchar2,
1178 p_input_32 IN varchar2,
1179 p_input_33 IN varchar2,
1180 p_input_34 IN varchar2,
1181 p_input_35 IN varchar2,
1182 p_input_36 IN varchar2,
1183 p_input_37 IN varchar2,
1184 p_input_38 IN varchar2,
1185 p_input_39 IN varchar2,
1186 p_input_40 IN varchar2,
1187 p_validate IN varchar2,
1188 p_exclude_from_output OUT nocopy varchar2,
1189 sp_out_1 OUT nocopy varchar2,
1190 sp_out_2 OUT nocopy varchar2,
1191 sp_out_3 OUT nocopy varchar2,
1192 sp_out_4 OUT nocopy varchar2,
1193 sp_out_5 OUT nocopy varchar2,
1194 ret_str_len OUT nocopy number,
1195 p_input_41 IN varchar2 default null,
1196 p_input_42 IN varchar2 default null,
1197 p_input_43 IN varchar2 default null,
1198 p_input_44 IN varchar2 default null,
1199 p_input_45 IN varchar2 default null /* Bug 13351713 */
1200 ) RETURN VARCHAR2
1201 IS
1202
1203 return_value varchar2(32767);
1204 l_exclude_from_output_chk boolean;
1205 main_return_string varchar2(300);
1206 BEGIN
1207
1208 hr_utility.trace(' p_report_qualifier = '||p_report_qualifier);
1209 hr_utility.trace(' p_record_name = ' ||p_record_name);
1210 hr_utility.trace(' p_input_2 = ' ||p_input_2);
1211 hr_utility.trace(' p_input_2 = ' ||p_input_2);
1212 hr_utility.trace(' p_input_3 = ' ||p_input_3);
1213 hr_utility.trace(' p_input_4 = ' ||p_input_4);
1214 hr_utility.trace(' p_input_5 = ' ||p_input_5);
1215 hr_utility.trace(' p_input_6 = ' ||p_input_6);
1216 hr_utility.trace(' p_input_7 = ' ||p_input_7);
1217 hr_utility.trace(' p_input_8 = ' ||p_input_8);
1218 hr_utility.trace(' p_input_9 = ' ||p_input_9);
1219 hr_utility.trace(' p_input_10 = ' ||p_input_10);
1220 hr_utility.trace(' p_input_11 = ' ||p_input_11);
1221 hr_utility.trace(' p_input_12 = ' ||p_input_12);
1222 hr_utility.trace(' p_input_13 = ' ||p_input_13);
1223 hr_utility.trace(' p_input_14 = ' ||p_input_14);
1224 hr_utility.trace(' p_input_15 = ' ||p_input_15);
1225 hr_utility.trace(' p_input_16 = ' ||p_input_16);
1226 hr_utility.trace(' p_input_17 = ' ||p_input_17);
1227 hr_utility.trace(' p_input_18 = ' ||p_input_18);
1228 hr_utility.trace(' p_input_19 = ' ||p_input_19);
1229 hr_utility.trace(' p_input_20 = ' ||p_input_20);
1230 hr_utility.trace(' p_input_21 = ' ||p_input_21);
1231 hr_utility.trace(' p_input_22 = ' ||p_input_22);
1232 hr_utility.trace(' p_input_23 = ' ||p_input_23);
1233 hr_utility.trace(' p_input_24 = ' ||p_input_24);
1234 hr_utility.trace(' p_input_25 = ' ||p_input_25);
1235 hr_utility.trace(' p_input_26 = ' ||p_input_26);
1236 hr_utility.trace(' p_input_27 = ' ||p_input_27);
1237 hr_utility.trace(' p_input_28 = ' ||p_input_28);
1238 hr_utility.trace(' p_input_29 = ' ||p_input_29);
1239 hr_utility.trace(' p_input_30 = ' ||p_input_30);
1240 hr_utility.trace(' p_input_31 = ' ||p_input_31);
1241 hr_utility.trace(' p_input_32 = ' ||p_input_32);
1242 hr_utility.trace(' p_input_33 = ' ||p_input_33);
1243 hr_utility.trace(' p_input_34 = ' ||p_input_34);
1244 hr_utility.trace(' p_input_35 = ' ||p_input_35);
1245 hr_utility.trace(' p_input_36 = ' ||p_input_36);
1246 hr_utility.trace(' p_input_37 = ' ||p_input_37);
1247 hr_utility.trace(' p_input_38 = ' ||p_input_38);
1248 hr_utility.trace(' p_input_39 = ' ||p_input_39);
1249 hr_utility.trace(' p_input_40 = ' ||p_input_40);
1250 hr_utility.trace(' p_input_41 = ' ||p_input_41);
1251 hr_utility.trace(' p_input_42 = ' ||p_input_42);
1252 hr_utility.trace(' p_input_43 = ' ||p_input_43);
1253 hr_utility.trace(' p_input_44 = ' ||p_input_44);
1254
1255 IF p_format = 'MMREF' THEN -- p_format
1256 --{
1257 IF (p_report_type = 'W2') THEN
1258 --{
1259 IF p_record_name = 'RA' THEN -- p_record_name
1260 return_value := pay_us_mmrf_w2_format_record.format_W2_RA_record(
1261 p_effective_date,
1262 p_report_type,
1263 p_format,
1264 p_report_qualifier,
1265 p_record_name,
1266 p_input_1,
1267 p_input_2,
1268 p_input_3,
1269 p_input_4,
1270 p_input_5,
1271 p_input_6,
1272 p_input_7,
1273 p_input_8,
1274 p_input_9 ,
1275 p_input_10,
1276 p_input_11,
1277 p_input_12,
1278 p_input_13,
1279 p_input_14,
1280 p_input_15,
1281 p_input_16,
1282 p_input_17,
1283 p_input_18,
1284 p_input_19,
1285 p_input_20,
1286 p_input_21,
1287 p_input_22,
1288 p_input_23,
1289 p_input_24,
1290 p_input_25,
1291 p_input_26,
1292 p_input_27,
1293 p_input_28,
1294 p_input_29,
1295 p_input_30,
1296 p_input_31,
1297 p_input_32,
1298 p_input_33,
1299 p_input_34,
1300 p_input_35,
1301 p_input_36,
1302 p_input_37,
1303 p_input_38,
1304 p_input_39,
1305 p_input_40,
1306 p_validate,
1307 p_exclude_from_output,
1308 sp_out_1,
1309 sp_out_2,
1310 sp_out_3,
1311 sp_out_4,
1312 sp_out_5,
1313 ret_str_len,
1314 l_exclude_from_output_chk
1315 );
1316 ELSIF p_record_name = 'RE' THEN
1317 return_value :=
1318 pay_us_mmrf_w2_format_record.format_W2_RE_record(
1319 p_effective_date,
1320 p_report_type,
1321 p_format,
1322 p_report_qualifier,
1323 p_record_name,
1324 p_input_1,
1325 p_input_2,
1326 p_input_3,
1327 p_input_4,
1328 p_input_5,
1329 p_input_6,
1330 p_input_7,
1331 p_input_8,
1332 p_input_9 ,
1333 p_input_10,
1334 p_input_11,
1335 p_input_12,
1336 p_input_13,
1337 p_input_14,
1338 p_input_15,
1339 p_input_16,
1340 p_input_17,
1341 p_input_18,
1342 p_input_19,
1343 p_input_20,
1344 p_input_21,
1345 p_input_22,
1346 p_input_23,
1347 p_input_24,
1348 p_input_25,
1349 p_input_26,
1350 p_input_27,
1351 p_input_28,
1352 p_input_29,
1353 p_input_30,
1354 p_input_31,
1355 p_input_32,
1356 p_input_33,
1357 p_input_34,
1358 p_input_35,
1359 p_input_36,
1360 p_input_37,
1361 p_input_38,
1362 p_input_39,
1363 p_input_40,
1364 p_validate,
1365 p_exclude_from_output,
1366 sp_out_1,
1367 sp_out_2,
1368 sp_out_3,
1369 sp_out_4,
1370 sp_out_5,
1371 ret_str_len,
1372 l_exclude_from_output_chk
1373 );
1374 ELSIF p_record_name = 'RW' THEN
1375 return_value :=
1376 pay_us_mmrf_w2_format_record.format_W2_RW_record(
1377 p_effective_date,
1378 p_report_type,
1379 p_format,
1380 p_report_qualifier,
1381 p_record_name,
1382 p_input_1,
1383 p_input_2,
1384 p_input_3,
1385 p_input_4,
1386 p_input_5,
1387 p_input_6,
1388 p_input_7,
1389 p_input_8,
1390 p_input_9 ,
1391 p_input_10,
1392 p_input_11,
1393 p_input_12,
1394 p_input_13,
1395 p_input_14,
1396 p_input_15,
1397 p_input_16,
1398 p_input_17,
1399 p_input_18,
1400 p_input_19,
1401 p_input_20,
1402 p_input_21,
1403 p_input_22,
1404 p_input_23,
1405 p_input_24,
1406 p_input_25,
1407 p_input_26,
1408 p_input_27,
1409 p_input_28,
1410 p_input_29,
1411 p_input_30,
1412 p_input_31,
1413 p_input_32,
1414 p_input_33,
1415 p_input_34,
1416 p_input_35,
1417 p_input_36,
1418 p_input_37,
1419 p_input_38,
1420 p_input_39,
1421 p_input_40,
1422 p_validate,
1423 p_exclude_from_output,
1424 sp_out_1,
1425 sp_out_2,
1426 sp_out_3,
1427 sp_out_4,
1428 sp_out_5,
1429 ret_str_len,
1430 l_exclude_from_output_chk,
1431 p_input_41,
1432 p_input_42,
1433 p_input_43,
1434 p_input_44,
1435 p_input_45 /* Bug 13351713 */
1436 );
1437
1438 ELSIF p_record_name = 'RO' THEN
1439 return_value :=
1440 pay_us_mmrf_w2_format_record.format_W2_RO_record(
1441 p_effective_date,
1442 p_report_type,
1443 p_format,
1444 p_report_qualifier,
1445 p_record_name,
1446 p_input_1,
1447 p_input_2,
1448 p_input_3,
1449 p_input_4,
1450 p_input_5,
1451 p_input_6,
1452 p_input_7,
1453 p_input_8,
1454 p_input_9 ,
1455 p_input_10,
1456 p_input_11,
1457 p_input_12,
1458 p_input_13,
1459 p_input_14,
1460 p_input_15,
1461 p_input_16,
1462 p_input_17,
1463 p_input_18,
1464 p_input_19,
1465 p_input_20,
1466 p_input_21,
1467 p_input_22,
1468 p_input_23,
1469 p_input_24,
1470 p_input_25,
1471 p_input_26,
1472 p_input_27,
1473 p_input_28,
1474 p_input_29,
1475 p_input_30,
1476 p_input_31,
1477 p_input_32,
1478 p_input_33,
1479 p_input_34,
1480 p_input_35,
1481 p_input_36,
1482 p_input_37,
1483 p_input_38,
1484 p_input_39,
1485 p_input_40,
1486 p_validate,
1487 p_exclude_from_output,
1488 sp_out_1,
1489 sp_out_2,
1490 sp_out_3,
1491 sp_out_4,
1492 sp_out_5,
1493 ret_str_len,
1494 l_exclude_from_output_chk
1495 );
1496
1497 ELSIF p_record_name = 'RS' THEN
1498 return_value :=
1499 pay_us_mmrf_w2_format_record.format_W2_RS_record(
1500 p_effective_date,
1501 p_report_type,
1502 p_format,
1503 p_report_qualifier,
1504 p_record_name,
1505 p_input_1,
1506 p_input_2,
1507 p_input_3,
1508 p_input_4,
1509 p_input_5,
1510 p_input_6,
1511 p_input_7,
1512 p_input_8,
1513 p_input_9 ,
1514 p_input_10,
1515 p_input_11,
1516 p_input_12,
1517 p_input_13,
1518 p_input_14,
1519 p_input_15,
1520 p_input_16,
1521 p_input_17,
1522 p_input_18,
1523 p_input_19,
1524 p_input_20,
1525 p_input_21,
1526 p_input_22,
1527 p_input_23,
1528 p_input_24,
1529 p_input_25,
1530 p_input_26,
1531 p_input_27,
1532 p_input_28,
1533 p_input_29,
1534 p_input_30,
1535 p_input_31,
1536 p_input_32,
1537 p_input_33,
1538 p_input_34,
1539 p_input_35,
1540 p_input_36,
1541 p_input_37,
1542 p_input_38,
1543 p_input_39,
1544 p_input_40,
1545 p_validate,
1546 p_exclude_from_output,
1547 sp_out_1,
1548 sp_out_2,
1549 sp_out_3,
1550 sp_out_4,
1551 sp_out_5,
1552 ret_str_len,
1553 l_exclude_from_output_chk
1554 );
1555 ELSIF p_record_name = 'RT' THEN
1556 return_value :=
1557 pay_us_mmrf_w2_format_record.format_W2_RT_record(
1558 p_effective_date,
1559 p_report_type,
1560 p_format,
1561 p_report_qualifier,
1562 p_record_name,
1563 p_input_1,
1564 p_input_2,
1565 p_input_3,
1566 p_input_4,
1567 p_input_5,
1568 p_input_6,
1569 p_input_7,
1570 p_input_8,
1571 p_input_9 ,
1572 p_input_10,
1573 p_input_11,
1574 p_input_12,
1575 p_input_13,
1576 p_input_14,
1577 p_input_15,
1578 p_input_16,
1579 p_input_17,
1580 p_input_18,
1581 p_input_19,
1582 p_input_20,
1583 p_input_21,
1584 p_input_22,
1585 p_input_23,
1586 p_input_24,
1587 p_input_25,
1588 p_input_26,
1589 p_input_27,
1590 p_input_28,
1591 p_input_29,
1592 p_input_30,
1593 p_input_31,
1594 p_input_32,
1595 p_input_33,
1596 p_input_34,
1597 p_input_35,
1598 p_input_36,
1599 p_input_37,
1600 p_input_38,
1601 p_input_39,
1602 p_input_40,
1603 p_validate,
1604 p_exclude_from_output,
1605 sp_out_1,
1606 sp_out_2,
1607 sp_out_3,
1608 sp_out_4,
1609 sp_out_5,
1610 ret_str_len,
1611 l_exclude_from_output_chk,
1612 p_input_41 /* Bug 13351713 */
1613 );
1614 ELSIF p_record_name = 'RU' THEN
1615 return_value :=
1616 pay_us_mmrf_w2_format_record.format_W2_RU_record(
1617 p_effective_date,
1618 p_report_type,
1619 p_format,
1620 p_report_qualifier,
1621 p_record_name,
1622 p_input_1,
1623 p_input_2,
1624 p_input_3,
1625 p_input_4,
1626 p_input_5,
1627 p_input_6,
1628 p_input_7,
1629 p_input_8,
1630 p_input_9 ,
1631 p_input_10,
1632 p_input_11,
1633 p_input_12,
1634 p_input_13,
1635 p_input_14,
1636 p_input_15,
1637 p_input_16,
1638 p_input_17,
1639 p_input_18,
1640 p_input_19,
1641 p_input_20,
1642 p_input_21,
1643 p_input_22,
1644 p_input_23,
1645 p_input_24,
1646 p_input_25,
1647 p_input_26,
1648 p_input_27,
1649 p_input_28,
1650 p_input_29,
1651 p_input_30,
1652 p_input_31,
1653 p_input_32,
1654 p_input_33,
1655 p_input_34,
1656 p_input_35,
1657 p_input_36,
1658 p_input_37,
1659 p_input_38,
1660 p_input_39,
1661 p_input_40,
1662 p_validate,
1663 p_exclude_from_output,
1664 sp_out_1,
1665 sp_out_2,
1666 sp_out_3,
1667 sp_out_4,
1668 sp_out_5,
1669 ret_str_len,
1670 l_exclude_from_output_chk
1671 );
1672
1673 -- RV Record formatting vmkulkar
1674 ELSIF p_record_name = 'RV' THEN -- p_record_name
1675 return_value := pay_us_mmrf_w2_format_record.format_W2_RV_record(
1676 p_effective_date,
1677 p_report_type,
1678 p_format,
1679 p_report_qualifier,
1680 p_record_name,
1681 p_input_1,
1682 p_input_2,
1683 p_input_3,
1684 p_input_4,
1685 p_input_5,
1686 p_input_6,
1687 p_input_7,
1688 p_input_8,
1689 p_input_9 ,
1690 p_input_10,
1691 p_input_11,
1692 p_input_12,
1693 p_input_13,
1694 p_input_14,
1695 p_input_15,
1696 p_input_16,
1697 p_input_17,
1698 p_input_18,
1699 p_input_19,
1700 p_input_20,
1701 p_input_21,
1702 p_input_22,
1703 p_input_23,
1704 p_input_24,
1705 p_input_25,
1706 p_input_26,
1707 p_input_27,
1708 p_input_28,
1709 p_input_29,
1710 p_input_30,
1711 p_input_31,
1712 p_input_32,
1713 p_input_33,
1714 p_input_34,
1715 p_input_35,
1716 p_input_36,
1717 p_input_37,
1718 p_input_38,
1719 p_input_39,
1720 p_input_40,
1721 p_validate,
1722 p_exclude_from_output,
1723 sp_out_1,
1724 sp_out_2,
1725 sp_out_3,
1726 sp_out_4,
1727 sp_out_5,
1728 ret_str_len,
1729 l_exclude_from_output_chk
1730 );
1731 ELSIF p_record_name = 'RF' THEN
1732 return_value :=
1733 pay_us_mmrf_w2_format_record.format_W2_RF_record(
1734 p_effective_date,
1735 p_report_type,
1736 p_format,
1737 p_report_qualifier,
1738 p_record_name,
1739 p_input_1,
1740 p_input_2,
1741 p_input_3,
1742 p_input_4,
1743 p_input_40,
1744 p_validate,
1745 p_exclude_from_output,
1746 ret_str_len,
1747 l_exclude_from_output_chk
1748 );
1749 END IF; --p_record_name
1750 --}
1751 ELSIF (p_report_type = 'SQWL') THEN --p_report_type
1752 --{
1753 IF p_record_name = 'RA' THEN -- p_record_name
1754 return_value := pay_us_mmrf_sqwl_format_record.format_SQWL_RA_record(
1755 p_effective_date,
1756 p_report_type,
1757 p_format,
1758 p_report_qualifier,
1759 p_record_name,
1760 p_input_1,
1761 p_input_2,
1762 p_input_3,
1763 p_input_4,
1764 p_input_5,
1765 p_input_6,
1766 p_input_7,
1767 p_input_8,
1768 p_input_9 ,
1769 p_input_10,
1770 p_input_11,
1771 p_input_12,
1772 p_input_13,
1773 p_input_14,
1774 p_input_15,
1775 p_input_16,
1776 p_input_17,
1777 p_input_18,
1778 p_input_19,
1779 p_input_20,
1780 p_input_21,
1781 p_input_22,
1782 p_input_23,
1783 p_input_24,
1784 p_input_25,
1785 p_input_26,
1786 p_input_27,
1787 p_input_28,
1788 p_input_29,
1789 p_input_30,
1790 p_input_31,
1791 p_input_32,
1792 p_input_33,
1793 p_input_34,
1794 p_input_35,
1795 p_input_36,
1796 p_input_37,
1797 p_input_38,
1798 p_input_39,
1799 p_input_40,
1800 p_validate,
1801 p_exclude_from_output,
1802 sp_out_1,
1803 sp_out_2,
1804 sp_out_3,
1805 sp_out_4,
1806 sp_out_5,
1807 ret_str_len,
1808 l_exclude_from_output_chk
1809 );
1810 ELSIF p_record_name = 'RE' THEN
1811 return_value :=
1812 pay_us_mmrf_sqwl_format_record.format_SQWL_RE_record(
1813 p_effective_date,
1814 p_report_type,
1815 p_format,
1816 p_report_qualifier,
1817 p_record_name,
1818 p_input_1,
1819 p_input_2,
1820 p_input_3,
1821 p_input_4,
1822 p_input_5,
1823 p_input_6,
1824 p_input_7,
1825 p_input_8,
1826 p_input_9 ,
1827 p_input_10,
1828 p_input_11,
1829 p_input_12,
1830 p_input_13,
1831 p_input_14,
1832 p_input_15,
1833 p_input_16,
1834 p_input_17,
1835 p_input_18,
1836 p_input_19,
1837 p_input_20,
1838 p_input_21,
1839 p_input_22,
1840 p_input_23,
1841 p_input_24,
1842 p_input_25,
1843 p_input_26,
1844 p_input_27,
1845 p_input_28,
1846 p_input_29,
1847 p_input_30,
1848 p_input_31,
1849 p_input_32,
1850 p_input_33,
1851 p_input_34,
1852 p_input_35,
1853 p_input_36,
1854 p_input_37,
1855 p_input_38,
1856 p_input_39,
1857 p_input_40,
1858 p_validate,
1859 p_exclude_from_output,
1860 sp_out_1,
1861 sp_out_2,
1862 sp_out_3,
1863 sp_out_4,
1864 sp_out_5,
1865 ret_str_len,
1866 l_exclude_from_output_chk
1867 );
1868
1869 ELSIF p_record_name = 'RS' THEN
1870 return_value :=
1871 pay_us_mmrf_sqwl_format_record.format_SQWL_RS_record(
1872 p_effective_date,
1873 p_report_type,
1874 p_format,
1875 p_report_qualifier,
1876 p_record_name,
1877 p_input_1,
1878 p_input_2,
1879 p_input_3,
1880 p_input_4,
1881 p_input_5,
1882 p_input_6,
1883 p_input_7,
1884 p_input_8,
1885 p_input_9 ,
1886 p_input_10,
1887 p_input_11,
1888 p_input_12,
1889 p_input_13,
1890 p_input_14,
1891 p_input_15,
1892 p_input_16,
1893 p_input_17,
1894 p_input_18,
1895 p_input_19,
1896 p_input_20,
1897 p_input_21,
1898 p_input_22,
1899 p_input_23,
1900 p_input_24,
1901 p_input_25,
1902 p_input_26,
1903 p_input_27,
1904 p_input_28,
1905 p_input_29,
1906 p_input_30,
1907 p_input_31,
1908 p_input_32,
1909 p_input_33,
1910 p_input_34,
1911 p_input_35,
1912 p_input_36,
1913 p_input_37,
1914 p_input_38,
1915 p_input_39,
1916 p_input_40,
1917 p_validate,
1918 p_exclude_from_output,
1919 sp_out_1,
1920 sp_out_2,
1921 sp_out_3,
1922 sp_out_4,
1923 sp_out_5,
1924 ret_str_len,
1925 l_exclude_from_output_chk
1926 );
1927 ELSIF p_record_name = 'RT' THEN
1928 return_value :=
1929 pay_us_mmrf_sqwl_format_record.format_SQWL_RT_record(
1930 p_effective_date,
1931 p_report_type,
1932 p_format,
1933 p_report_qualifier,
1934 p_record_name,
1935 p_input_1,
1936 p_input_2,
1937 p_input_3,
1938 p_input_4,
1939 p_input_5,
1940 p_input_6,
1941 p_input_7,
1942 p_input_8,
1943 p_input_9 ,
1944 p_input_10,
1945 p_input_11,
1946 p_input_12,
1947 p_input_13,
1948 p_input_14,
1949 p_input_15,
1950 p_input_16,
1951 p_input_17,
1952 p_input_18,
1953 p_input_19,
1954 p_input_20,
1955 p_input_21,
1956 p_input_22,
1957 p_input_23,
1958 p_input_24,
1959 p_input_25,
1960 p_input_26,
1961 p_input_27,
1962 p_input_28,
1963 p_input_29,
1964 p_input_30,
1965 p_input_31,
1966 p_input_32,
1967 p_input_33,
1968 p_input_34,
1969 p_input_35,
1970 p_input_36,
1971 p_input_37,
1972 p_input_38,
1973 p_input_39,
1974 p_input_40,
1975 p_validate,
1976 p_exclude_from_output,
1977 sp_out_1,
1978 sp_out_2,
1979 sp_out_3,
1980 sp_out_4,
1981 sp_out_5,
1982 ret_str_len,
1983 l_exclude_from_output_chk
1984 );
1985 ELSIF p_record_name = 'RSSUMM' THEN
1986 return_value :=
1987 pay_us_mmrf_sqwl_format_record.format_SQWL_RSSUMM_record(
1988 p_effective_date,
1989 p_report_type,
1990 p_format,
1991 p_report_qualifier,
1992 p_record_name,
1993 p_input_1,
1994 p_input_2,
1995 p_input_3,
1996 p_input_4,
1997 p_input_5,
1998 p_input_6,
1999 p_input_7,
2000 p_input_8,
2001 p_input_9 ,
2002 p_input_10,
2003 p_input_11,
2004 p_input_12,
2005 p_input_13,
2006 p_input_14,
2007 p_input_15,
2008 p_input_16,
2009 p_input_17,
2010 p_input_18,
2011 p_input_19,
2012 p_input_20,
2013 p_input_21,
2014 p_input_22,
2015 p_input_23,
2016 p_input_24,
2017 p_input_25,
2018 p_input_26,
2019 p_input_27,
2020 p_input_28,
2021 p_input_29,
2022 p_input_30,
2023 p_input_31,
2024 p_input_32,
2025 p_input_33,
2026 p_input_34,
2027 p_input_35,
2028 p_input_36,
2029 p_input_37,
2030 p_input_38,
2031 p_input_39,
2032 p_input_40,
2033 p_validate,
2034 p_exclude_from_output,
2035 sp_out_1,
2036 sp_out_2,
2037 sp_out_3,
2038 sp_out_4,
2039 sp_out_5,
2040 ret_str_len,
2041 l_exclude_from_output_chk
2042 );
2043 ELSIF p_record_name = 'RST' THEN
2044 return_value :=
2045 pay_us_mmrf_sqwl_format_record.format_SQWL_RST_record(
2046 p_effective_date,
2047 p_report_type,
2048 p_format,
2049 p_report_qualifier,
2050 p_record_name,
2051 p_input_1,
2052 p_input_2,
2053 p_input_3,
2054 p_input_4,
2055 p_input_5,
2056 p_input_6,
2057 p_input_7,
2058 p_input_8,
2059 p_input_9 ,
2060 p_input_40,
2061 p_validate,
2062 p_exclude_from_output,
2063 ret_str_len
2064 );
2065 ELSIF p_record_name = 'RU' THEN
2066 return_value :=
2067 pay_us_mmrf_sqwl_format_record.format_SQWL_RU_record(
2068 p_effective_date,
2069 p_report_type,
2070 p_format,
2071 p_report_qualifier,
2072 p_record_name,
2073 p_input_1,
2074 p_input_2,
2075 p_input_40,
2076 p_exclude_from_output,
2077 ret_str_len
2078 );
2079 ELSIF p_record_name = 'RF' THEN
2080 return_value :=
2081 pay_us_mmrf_sqwl_format_record.format_SQWL_RF_record(
2082 p_effective_date,
2083 p_report_type,
2084 p_format,
2085 p_report_qualifier,
2086 p_record_name,
2087 p_input_1,
2088 p_input_2,
2089 p_input_3,
2090 p_input_4,
2091 p_input_40,
2092 p_validate,
2093 p_exclude_from_output,
2094 ret_str_len
2095 );
2096 END IF; --p_record_name
2097 --}
2098 END IF; --p_report_type
2099 --}
2100 ELSIF p_format = 'CUSTOM' THEN
2101 --{
2102 hr_utility.trace('In pay_us_reporting_utils_pkg.format_record ' || p_format);
2103
2104 IF p_record_name = 'H' THEN -- p_record_name
2105 hr_utility.trace('In pay_us_reporting_utils_pkg.format_record ' || p_record_name);
2106 return_value :=
2107 pay_us_custom_sqwl_format_rec.format_SQWL_CUSTOM_EMPLOYER(
2108 p_effective_date,
2109 p_report_type,
2110 p_format,
2111 p_report_qualifier,
2112 p_record_name,
2113 p_input_1,
2114 p_input_2,
2115 p_input_3,
2116 p_input_4,
2117 p_input_5,
2118 p_input_6,
2119 p_input_7,
2120 p_input_8,
2121 p_input_9 ,
2122 p_input_10,
2123 p_input_11,
2124 p_input_12,
2125 p_input_13,
2126 p_input_14,
2127 p_input_15,
2128 p_input_16,
2129 p_input_17,
2130 p_input_18,
2131 p_input_19,
2132 p_input_20,
2133 p_input_21,
2134 p_input_22,
2135 p_input_23,
2136 p_input_24,
2137 p_input_25,
2138 p_input_26,
2139 p_input_27,
2140 p_input_28,
2141 p_input_29,
2142 p_input_30,
2143 p_input_31,
2144 p_input_32,
2145 p_input_33,
2146 p_input_34,
2147 p_input_35,
2148 p_input_36,
2149 p_input_37,
2150 p_input_38,
2151 p_input_39,
2152 p_input_40,
2153 p_validate,
2154 p_exclude_from_output,
2155 sp_out_1,
2156 sp_out_2,
2157 sp_out_3,
2158 sp_out_4,
2159 sp_out_5,
2160 ret_str_len,
2161 l_exclude_from_output_chk
2162 );
2163
2164 ELSIF p_record_name = 'D' THEN
2165 hr_utility.trace('In pay_us_reporting_utils_pkg.format_record ' || p_record_name);
2166 return_value :=
2167 pay_us_custom_sqwl_format_rec.format_SQWL_CUSTOM_EMPLOYEE(
2168 p_effective_date,
2169 p_report_type,
2170 p_format,
2171 p_report_qualifier,
2172 p_record_name,
2173 p_input_1,
2174 p_input_2,
2175 p_input_3,
2176 p_input_4,
2177 p_input_5,
2178 p_input_6,
2179 p_input_7,
2180 p_input_8,
2181 p_input_9 ,
2182 p_input_10,
2183 p_input_11,
2184 p_input_12,
2185 p_input_13,
2186 p_input_14,
2187 p_input_15,
2188 p_input_16,
2189 p_input_17,
2190 p_input_18,
2191 p_input_19,
2192 p_input_20,
2193 p_input_21,
2194 p_input_22,
2195 p_input_23,
2196 p_input_24,
2197 p_input_25,
2198 p_input_26,
2199 p_input_27,
2200 p_input_28,
2201 p_input_29,
2202 p_input_30,
2203 p_input_31,
2204 p_input_32,
2205 p_input_33,
2206 p_input_34,
2207 p_input_35,
2208 p_input_36,
2209 p_input_37,
2210 p_input_38,
2211 p_input_39,
2212 p_input_40,
2213 p_validate,
2214 p_exclude_from_output,
2215 sp_out_1,
2216 sp_out_2,
2217 sp_out_3,
2218 sp_out_4,
2219 sp_out_5,
2220 ret_str_len,
2221 l_exclude_from_output_chk
2222 );
2223 END IF; --p_report_type
2224 --}
2225 END IF; -- p_format
2226 return_value:=upper(return_value);
2227
2228 main_return_string := substr(return_value,1,200);
2229 sp_out_1:=substr(return_value,201,200);
2230 sp_out_2:=substr(return_value,401,200);
2231 sp_out_3:=substr(return_value,601,200);
2232 sp_out_4:=substr(return_value,801,200);
2233
2234 IF (((p_record_name = 'RS') OR (p_record_name = 'D')) AND
2235 (p_report_type = 'SQWL') AND
2236 (p_input_40 = 'FLAT') ) THEN
2237 NULL; -- sp_out_5 is initialized with ssn in format_SQWL_RS_record
2238 ELSE
2239 sp_out_5:=substr(return_value,1001,200);
2240 END IF;
2241 IF l_exclude_from_output_chk THEN
2242 p_exclude_from_output := 'Y';
2243 ELSE
2244 p_exclude_from_output := 'N';
2245 END IF;
2246 hr_utility.trace('main_return_string = '||main_return_string);
2247 hr_utility.trace(' length of main_return_string = '||to_char(length(main_return_string)));
2248 hr_utility.trace('sp_out_1 = '||sp_out_1);
2249 hr_utility.trace(' length of sp_out_1 = '||to_char(length(sp_out_1)));
2250 hr_utility.trace('sp_out_2 = '||sp_out_2);
2251 hr_utility.trace(' length of sp_out_2 = '||to_char(length(sp_out_2)));
2252 hr_utility.trace('sp_out_3 = '||sp_out_3);
2253 hr_utility.trace(' length of sp_out_3 = '||to_char(length(sp_out_3)));
2254 hr_utility.trace('sp_out_4 = '||sp_out_4);
2255 hr_utility.trace(' length of sp_out_4 = '||to_char(length(sp_out_4)));
2256 hr_utility.trace('sp_out_5 = '||sp_out_5);
2257 hr_utility.trace(' length of sp_out_5 = '||to_char(length(sp_out_5)));
2258 hr_utility.trace('p_exclude_from_output = '||p_exclude_from_output);
2259
2260 RETURN main_return_string;
2261 END Format_Record;
2262
2263 /* Function Name : Get_Territory_Values
2264 Purpose : Purpose of this function is to fetch the balances as well
2265 as the data related to territory.
2266 Error checking
2267 Special Note :
2268 */
2269
2270 FUNCTION Get_Territory_Values(
2271 p_assignment_action_id number, -- context
2272 p_tax_unit_id number,-- context
2273 p_effective_date IN varchar2,
2274 p_report_type IN varchar2,
2275 p_format IN varchar2,
2276 p_report_qualifier IN varchar2,
2277 p_record_name IN varchar2,
2278 p_input_1 IN varchar2,
2279 p_input_2 IN varchar2,
2280 p_input_3 IN varchar2,
2281 p_input_4 IN varchar2,
2282 p_input_5 IN varchar2,
2283 p_validate IN varchar2,
2284 p_exclude_from_output OUT nocopy varchar2,
2285 sp_out_1 OUT nocopy varchar2,
2286 sp_out_2 OUT nocopy varchar2,
2287 sp_out_3 OUT nocopy varchar2,
2288 sp_out_4 OUT nocopy varchar2,
2289 sp_out_5 OUT nocopy varchar2,
2290 sp_out_6 OUT nocopy varchar2,
2291 sp_out_7 OUT nocopy varchar2,
2292 sp_out_8 OUT nocopy varchar2,
2293 sp_out_9 OUT nocopy varchar2,
2294 sp_out_10 OUT nocopy varchar2)
2295 return varchar2 IS
2296
2297 l_err boolean;
2298 l_entity_id ff_database_items.user_entity_id%type;
2299 l_archived_value ff_archive_items.value%type;
2300 l_jurisdiction_code varchar2(25);
2301 l_message varchar2(1000);
2302 l_state_wage varchar2(100);
2303 l_main_return varchar2(100);
2304
2305 TYPE dbi_columns IS RECORD(
2306 p_user_name ff_database_items.user_name%type,
2307 p_archived_value ff_archive_items.value%type);
2308
2309 dbi_rec dbi_columns;
2310
2311 TYPE dbi_infm IS TABLE OF dbi_rec%TYPE
2312 INDEX BY BINARY_INTEGER;
2313
2314 dbi_table dbi_infm;
2315
2316 CURSOR get_user_entity_id
2317 (c_user_name ff_database_items.user_name%type) IS
2318 SELECT fdi.user_entity_id
2319 FROM ff_database_items fdi,
2320 ff_user_entities fue
2321 WHERE fue.legislation_code = 'US'
2322 AND fue.user_entity_id = fdi.user_entity_id
2323 AND fdi.user_name = c_user_name;
2324
2325 CURSOR get_archived_values(
2326 c_user_entity_id ff_database_items.user_entity_id%type,
2327 c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
2328 c_tax_unit_id hr_organization_units.organization_id%type)
2329 IS
2330 SELECT target.value
2331 FROM ff_archive_item_contexts con2,
2332 ff_contexts fc2,
2333 ff_archive_items target
2334 WHERE target.user_entity_id = c_user_entity_id
2335 AND target.context1 = to_char(c_assignment_action_id)
2336 /* context assignment action id */
2337 AND fc2.context_name = 'TAX_UNIT_ID'
2338 and con2.archive_item_id = target.archive_item_id
2339 and con2.context_id = fc2.context_id
2340 and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id);
2341 /*context of tax_unit_id */
2342
2343 CURSOR get_jd_archived_values(
2344 c_user_entity_id ff_database_items.user_entity_id%type,
2345 c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
2346 c_tax_unit_id hr_organization_units.organization_id%type,
2347 c_jurisdiction_code varchar2)
2348 IS
2349 SELECT target.value
2350 FROM ff_archive_item_contexts con2,
2351 ff_archive_item_contexts con3,
2352 ff_contexts fc2,
2353 ff_contexts fc3,
2354 ff_archive_items target
2355 WHERE target.user_entity_id = c_user_entity_id
2356 AND target.context1 = to_char(c_assignment_action_id)
2357 /* context assignment action id */
2358 AND fc2.context_name = 'TAX_UNIT_ID'
2359 and con2.archive_item_id = target.archive_item_id
2360 and con2.context_id = fc2.context_id
2361 and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id)
2362 /*context of tax_unit_id */
2363 and fc3.context_name = 'JURISDICTION_CODE'
2364 and con3.archive_item_id = target.archive_item_id
2365 and con3.context_id = fc3.context_id
2366 and substr(con3.context,1,2) = substr(c_jurisdiction_code,1,2);
2367 /* 3rd context of state jurisdiction_code*/
2368
2369 BEGIN
2370
2371 hr_utility.trace('p_assignment_action_id = '||to_char(p_assignment_action_id));
2372 hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
2373 dbi_table(1).p_user_name:='A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD';
2374 dbi_table(2).p_user_name:='A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD';
2375 dbi_table(3).p_user_name:='A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD';
2376 dbi_table(4).p_user_name:='A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD';
2377 dbi_table(5).p_user_name:='A_SIT_WITHHELD_PER_JD_GRE_YTD';
2378 dbi_table(6).p_user_name:='A_PER_MARITAL_STATUS';
2379 dbi_table(7).p_user_name:='A_CON_NATIONAL_IDENTIFIER';
2380
2381 l_jurisdiction_code:='72-000-0000';
2382 hr_utility.trace('Get PR Values');
2383
2384 FOR i in dbi_table.first .. dbi_table.last loop
2385
2386 OPEN get_user_entity_id(dbi_table(i).p_user_name);
2387 FETCH get_user_entity_id INTO l_entity_id;
2388 IF get_user_entity_id%NOTFOUND THEN
2389
2390 l_message:='Error in '||p_record_name||'. User_Entity_Id not found for user name '
2391 ||dbi_table(i).p_user_name;
2392 dbi_table(i).p_archived_value:='00000000000';
2393
2394 ELSIF get_user_entity_id%FOUND THEN
2395
2396 hr_utility.trace('get_user_entity_id = '||to_char(l_entity_id));
2397 hr_utility.trace('p_assignment_action_id = '||to_char(p_assignment_action_id));
2398 hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
2399
2400 IF dbi_table(i).p_user_name like '%PER_JD_GRE_YTD' THEN
2401 open get_jd_archived_values(l_entity_id,
2402 p_assignment_action_id,
2403 p_tax_unit_id,
2404 l_jurisdiction_code);
2405 FETCH get_jd_archived_values INTO l_archived_value;
2406 IF get_jd_archived_values%NOTFOUND THEN
2407 dbi_table(i).p_archived_value:='00000000000';
2408 ELSE
2409 dbi_table(i).p_archived_value:= data_validation
2410 ( p_effective_date,
2411 p_report_type,
2412 p_format,
2413 p_report_qualifier,
2414 p_record_name,
2415 'NEG_CHECK',
2416 l_archived_value,
2417 dbi_table(i).p_user_name,
2418 p_input_1,
2419 null,
2420 p_validate,
2421 p_exclude_from_output,
2422 sp_out_1,
2423 sp_out_2);
2424
2425 hr_utility.trace('Archived_value = '||dbi_table(i).p_archived_value);
2426
2427 IF p_exclude_from_output = 'Y' THEN
2428 l_err:=TRUE;
2429 END IF;
2430 END IF;
2431 CLOSE get_jd_archived_values;
2432
2433 ELSE -- Non JD specific balances
2434 OPEN get_archived_values(l_entity_id,
2435 p_assignment_action_id,
2436 p_tax_unit_id);
2437 FETCH get_archived_values INTO l_archived_value;
2438
2439 IF get_archived_values%NOTFOUND THEN
2440 IF ( (dbi_table(i).p_user_name = 'A_PER_MARITAL_STATUS') OR
2441 (dbi_table(i).p_user_name = 'A_CON_NATIONAL_IDENTIFIER') ) THEN
2442 dbi_table(i).p_archived_value:= ' ';
2443 ELSE
2444 dbi_table(i).p_archived_value:='00000000000';
2445 END IF;
2446
2447 hr_utility.trace('Archived_values not found for user name ' ||dbi_table(i).p_user_name);
2448 ELSIF get_archived_values%FOUND THEN
2449
2450 hr_utility.trace('Archived_values found for user name ' ||dbi_table(i).p_user_name);
2451 hr_utility.trace('Archived_value before neg check= '||l_archived_value);
2452
2453 IF ((dbi_table(i).p_user_name = 'A_PER_MARITAL_STATUS') OR
2454 (dbi_table(i).p_user_name = 'A_CON_NATIONAL_IDENTIFIER')) THEN
2455 dbi_table(i).p_archived_value := l_archived_value;
2456
2457 IF dbi_table(i).p_user_name = 'A_PER_MARITAL_STATUS' THEN
2458
2459 l_archived_value := replace(l_archived_value,' ');
2460 -- Bug # 2173795
2461 -- For Portorico Tax Jurisdiction Civil Status value would either M or S
2462 -- IF l_archived_value is other than M it should be defaulted to S
2463 IF l_archived_value = 'M' THEN
2464 dbi_table(i).p_archived_value := 'M';
2465 ELSE
2466 dbi_table(i).p_archived_value := 'S';
2467 END IF;
2468 END IF;
2469
2470 ELSE
2471
2472 dbi_table(i).p_archived_value:= data_validation
2473 ( p_effective_date,
2474 p_report_type,
2475 p_format,
2476 p_report_qualifier,
2477 p_record_name,
2478 'NEG_CHECK',
2479 l_archived_value,
2480 dbi_table(i).p_user_name,
2481 p_input_1,
2482 null,
2483 p_validate,
2484 p_exclude_from_output,
2485 sp_out_1,
2486 sp_out_2);
2487
2488 hr_utility.trace('Archived_value = '||dbi_table(i).p_archived_value);
2489
2490 IF p_exclude_from_output = 'Y' THEN
2491 l_err:=TRUE;
2492 END IF;
2493 END IF;
2494 END IF;
2495 CLOSE get_archived_values;
2496 END IF;
2497 END IF;
2498 CLOSE get_user_entity_id;
2499
2500 END LOOP;
2501
2502
2503 hr_utility.trace('before Call to retrieve state_wages');
2504
2505 l_state_wage := HR_US_W2_REP.GET_W2_ARCH_BAL
2506 (p_assignment_action_id,'A_W2_STATE_WAGES',
2507 p_tax_unit_id,l_jurisdiction_code,2);
2508
2509 hr_utility.trace('l_state_wage = '||l_state_wage);
2510 sp_out_5:= data_validation ( p_effective_date,
2511 p_report_type,
2512 p_format,
2513 p_report_qualifier,
2514 p_record_name,
2515 'NEG_CHECK',
2516 l_state_wage,
2517 'State_wage',
2518 p_input_1,
2519 null,
2520 p_validate,
2521 p_exclude_from_output,
2522 sp_out_1,
2523 sp_out_2);
2524
2525 IF p_exclude_from_output='Y' THEN
2526 l_err:=TRUE;
2527 END IF;
2528
2529
2530 l_main_return:=dbi_table(1).p_archived_value;
2531 sp_out_1:=dbi_table(2).p_archived_value;
2532 sp_out_2:=dbi_table(3).p_archived_value;
2533 sp_out_3:=dbi_table(4).p_archived_value;
2534 sp_out_4:=dbi_table(5).p_archived_value;
2535 sp_out_6:=dbi_table(6).p_archived_value;
2536 sp_out_7:=dbi_table(7).p_archived_value;
2537
2538
2539 hr_utility.trace('l_main_return = '||l_main_return);
2540 hr_utility.trace('sp_out_1 = '||sp_out_1);
2541 hr_utility.trace('sp_out_2 = '||sp_out_2);
2542 hr_utility.trace('sp_out_3 = '||sp_out_3);
2543 hr_utility.trace('sp_out_4 = '||sp_out_4);
2544 hr_utility.trace('sp_out_5 = '||sp_out_5);
2545 hr_utility.trace('sp_out_6 = '||sp_out_6);
2546 hr_utility.trace('sp_out_7 = '||sp_out_7);
2547
2548
2549 IF p_validate = 'Y' THEN
2550 IF l_err THEN
2551 p_exclude_from_output:='Y';
2552 END IF;
2553 END IF;
2554
2555 IF p_exclude_from_output IS NULL THEN
2556 p_exclude_from_output:='N';
2557 END IF;
2558
2559 RETURN l_main_return;
2560
2561 END Get_Territory_Values;
2562
2563 FUNCTION Character_check(p_value IN varchar2)
2564 RETURN VARCHAR2
2565
2566 IS
2567
2568 TYPE special_characters is record(
2569 p_character varchar2(100));
2570 character_val_record special_characters;
2571 type character_val_rec IS table of character_val_record%type
2572 INDEX BY BINARY_INTEGER;
2573 character_rec character_val_rec;
2574 l_stripped_value varchar2(100);
2575 l_param_length number(20);
2576
2577 Begin
2578
2579 character_rec(1).p_character :='<';
2580 character_rec(2).p_character :='>';
2581 character_rec(3).p_character :='(';
2582 character_rec(4).p_character :=')';
2583 character_rec(5).p_character :='_';
2584 character_rec(6).p_character :='*';
2585 character_rec(7).p_character :='&';
2586 character_rec(8).p_character :='^';
2587 character_rec(9).p_character :='%';
2588 character_rec(10).p_character :='$';
2589 character_rec(11).p_character :='#';
2590 character_rec(12).p_character :='@';
2591 character_rec(13).p_character :='!';
2592 character_rec(14).p_character :='~';
2593 character_rec(15).p_character :='+';
2594 character_rec(16).p_character :='=';
2595 character_rec(17).p_character :='?';
2596 character_rec(18).p_character :='/';
2597 character_rec(19).p_character :=','; /* Bug:2150138*/
2598
2599 l_stripped_value := p_value;
2600
2601 FOR i in 1 .. 19 LOOP
2602
2603 l_stripped_value := replace(l_stripped_value,character_rec(i).p_character,' ');
2604
2605 END LOOP;
2606
2607 return l_stripped_value;
2608
2609 END;
2610
2611 FUNCTION Formula_Check(p_report_format IN VARCHAR2,
2612 p_formula_name IN VARCHAR2)
2613 RETURN VARCHAR2 IS
2614
2615 l_value varchar2(1);
2616
2617 CURSOR formula_exist(c_report_format pay_magnetic_blocks.report_format%TYPE,
2618 c_formula_name ff_formulas_f.formula_name%TYPE)
2619 IS
2620 SELECT 'Y'
2621 FROM ff_formulas_f ff,
2622 pay_magnetic_blocks pmb,
2623 pay_magnetic_records pmr
2624 WHERE pmb.report_format = c_report_format
2625 AND pmr.magnetic_block_id = pmb.magnetic_block_id
2626 AND pmr.formula_id = ff.formula_id
2627 AND ff.formula_name = c_formula_name;
2628
2629 Begin
2630
2631 l_value := 'N';
2632
2633 hr_utility.trace('Formula_Check');
2634 hr_utility.trace('p_report_format = '||p_report_format);
2635 hr_utility.trace('p_formula_name = '||p_formula_name);
2636
2637
2638 OPEN formula_exist(p_report_format,
2639 p_formula_name);
2640
2641 FETCH formula_exist INTO l_value;
2642
2643 CLOSE formula_exist;
2644
2645 hr_utility.trace('l_value = '||l_value);
2646
2647 RETURN l_value;
2648
2649 END;
2650
2651 FUNCTION get_file_name ( p_bus_group_id IN Number, -- Business Group Id
2652 p_report_type IN Varchar2, -- W2, W2C, SQWL, RL (Rita/CCA or Local City)
2653 p_state_code IN Varchar2, -- FED or State Code
2654 p_mag_effective_date IN Varchar2, -- This would be used to derive period
2655 p_format_type IN Varchar2 -- Only for SQWL (I=ICESA, M=MMREF, T=TIB4, S=State)
2656 ) RETURN varchar2
2657 IS
2658
2659 --
2660 -- Purpose: Procedure to derive the Mag file Name for following Magnetic media
2661 -- processes.
2662 -- 1. Federal W-2 Magnetic Media
2663 -- 2. State W-2 Magnetic Media
2664 -- 3. State Quarterly Wage Listing
2665 -- 4. Local W-2 Magnetic Media
2666 -- 5. Federal W-2c Magnetic Medica
2667 --
2668 -- Declaration of Local program variables
2669 --
2670 l_mag_file_name Varchar2(80);
2671 --
2672 -- This Cursor fetches Tax info for the given jurisdiction and effective date
2673 --
2674 -- Federal Mag filenames will have following format.
2675 -- 1. Max of 6 characters Business Group Short Name (Embedded blanks will be ignored)
2676 -- 2. FED or State Abbreviation
2677 -- 3. Format specifier (W2)
2678 -- 4. Last two digits of the year
2679
2680 cursor c_fed_w2_Cursor(c_bus_group_id Number,
2681 c_report_type Varchar2,
2682 c_mag_effective_date Varchar2)
2683 is
2684 select substr(translate(upper(o.short_name),
2685 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
2686 {["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
2687 ---substr(replace(replace(replace(upper(o.short_name),'_'),' '),'-'),1,6)||
2688 'FED'||c_report_type||
2689 substr(to_char(add_months(fnd_date.canonical_TO_DATE(c_mag_effective_date), 12) -1, 'YYYY'),3,4)
2690 from per_business_groups o where o.organization_id = c_bus_group_id;
2691
2692 -- State W-2 Mag filenames will have following format.
2693 -- 1. Max of 6 characters Business Group Short Name (Embedded blanks will be ignored)
2694 -- 2. State Abbreviation
2695 -- 3. Report Type
2696 -- 4. Last two digits of the year
2697
2698 cursor c_State_W2_Cursor (c_bus_group_id Number,
2699 c_report_type Varchar2,
2700 c_state_code Varchar2,
2701 c_mag_effective_date Varchar2)
2702 is
2703 select substr(translate(upper(o.short_name),
2704 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
2705 {["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
2706 -- substr(replace(replace(replace(upper(o.short_name),'_'),' '),'-'),1,6)||
2707 psr.state_code ||
2708 c_report_type||
2709 substr(to_char(add_months(fnd_date.canonical_to_date(c_mag_effective_date ), 12) -1, 'YYYY'),3,4)
2710 from per_business_groups o, pay_state_rules psr
2711 where o.organization_id = c_bus_group_id
2712 and psr.state_code = c_state_code;
2713
2714 -- The SQWL (State Quarterly Wage Listing) filenames will follow the naming convention as
2715 -- 1. Max of 6 characters Business Group Short Name (Embedded blanks will be ignored)
2716 -- 2. State Abbreviation
2717 -- 3. Period in MMYY format
2718 -- 4. Format Type (i.e. I=ICESA, M=MMREF, T=TIB4, S=State)
2719 /* Bug:10649380 : 1st 6 character of filename modified to 1st 6 chars of GRE Name or Business Group Name
2720 depends on the report option of new enhanced SQWL program.*/
2721
2722 cursor c_sqwl_Cursor (c_bus_group_id Number,
2723 c_state_code Varchar2,
2724 c_mag_effective_date Varchar2,
2725 c_format_type Varchar2)
2726 is
2727 select substr(translate(upper(o.name),
2728 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
2729 {["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||to_char(c_bus_group_id)||
2730 -- substr(replace(replace(replace(upper(o.short_name),'_'),' '),'-'),1,6)||
2731 c_state_code ||
2732 substr(to_char(fnd_date.canonical_to_date(c_mag_effective_date ), 'MMYY'),1,4) ||
2733 c_format_type
2734 from hr_organization_units o, pay_state_rules psr
2735 where o.organization_id = c_bus_group_id
2736 and psr.state_code = c_state_code;
2737
2738 --
2739 -- Local Mag filenames will have following format.
2740 -- 1. Max of 6 characters Business Group Short Name (Embedded blanks will be ignored)
2741 -- 2. City or Agency Code
2742 -- 3. Format specifier (W2)
2743 -- 4. Last two digits of the year
2744 --
2745 cursor c_local_cursor(c_bus_group_id Number,
2746 c_city_or_agency_code Varchar2,
2747 c_mag_effective_date Varchar2)
2748 is
2749 select substr(translate(upper(o.short_name),
2750 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
2751 {["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
2752 -- substr(replace(replace(replace(upper(o.short_name),'_'),' '),'-'),1,6)||
2753 decode(c_city_or_agency_code,'RTCCA','RITA','CCAAA','CCA',c_city_or_agency_code) ||
2754 'W2'||
2755 substr(to_char(add_months(fnd_date.canonical_TO_DATE(c_mag_effective_date), 12) -1, 'YYYY'),3,4)
2756 from per_business_groups o where o.organization_id = c_bus_group_id;
2757
2758 BEGIN
2759 l_mag_file_name := '';
2760
2761 if p_report_type in ('W2', 'W2C') then
2762 if p_state_code = 'FED' then
2763 open c_fed_w2_cursor(p_bus_group_id, p_report_type, p_mag_effective_date );
2764 fetch c_fed_w2_cursor into l_mag_file_name;
2765 close c_fed_w2_cursor;
2766 else
2767 open c_state_w2_cursor(p_bus_group_id, p_report_type, p_state_code, p_mag_effective_date );
2768 fetch c_state_w2_cursor into l_mag_file_name;
2769 close c_state_w2_cursor ;
2770 end if;
2771
2772 elsif p_report_type = 'SQWL' then
2773 open c_sqwl_cursor(p_bus_group_id, p_state_code, p_mag_effective_date, p_format_type );
2774 fetch c_sqwl_cursor into l_mag_file_name;
2775 close c_sqwl_cursor;
2776 elsif p_report_type = 'RL' then
2777 open c_local_cursor(p_bus_group_id, p_state_code, p_mag_effective_date );
2778 fetch c_local_cursor into l_mag_file_name;
2779 close c_local_cursor;
2780 else
2781 l_mag_file_name := 'ERRORMAGFILE';
2782 end if;
2783 return (l_mag_file_name);
2784 END get_file_name; -- End of Function get_file_name
2785
2786
2787 -- This function derives balance_ID for a given Balance
2788 --
2789 FUNCTION bal_db_item ( p_db_item_name varchar2)
2790 RETURN number
2791 IS
2792
2793 /* Get the defined_balance_id for the specified balance DB item. */
2794
2795 CURSOR csr_defined_balance is
2796 SELECT to_number(UE.creator_id)
2797 FROM ff_user_entities UE,
2798 ff_database_items DI
2799 WHERE DI.user_name = p_db_item_name
2800 AND UE.user_entity_id = DI.user_entity_id
2801 AND Ue.creator_type = 'B'
2802 AND UE.legislation_code = 'US';
2803
2804 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2805
2806
2807 BEGIN
2808
2809 --hr_utility.trace('p_db_item_name is '||p_db_item_name);
2810
2811 OPEN csr_defined_balance;
2812 FETCH csr_defined_balance INTO l_defined_balance_id;
2813 IF csr_defined_balance%notfound THEN
2814 CLOSE csr_defined_balance;
2815 RAISE hr_utility.hr_error;
2816 ELSE
2817 CLOSE csr_defined_balance;
2818 END IF;
2819
2820 --hr_utility.trace('l_defined_balance_id is '||to_char(l_defined_balance_id));
2821 RETURN (l_defined_balance_id);
2822
2823 END bal_db_item;
2824
2825 -- Derives live Balance for W2_GOVE_EE_CONTRIB
2826 --
2827 FUNCTION get_live_ee_contrib( p_assignment_action_id number, --context
2828 p_tax_unit_id number --context
2829 )
2830 RETURN VARCHAR2
2831 IS
2832
2833 lv_bal_amt number := 0;
2834 l_bal_id number := 0;
2835 l_get boolean := TRUE ;
2836 l_balance_name pay_balance_types.balance_name%TYPE;
2837 l_effective_start_date pay_payroll_actions.effective_date%TYPE;
2838 l_effective_end_date pay_payroll_actions.effective_date%TYPE;
2839 l_exists varchar2(1);
2840
2841 CURSOR get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
2842 IS
2843 select add_months(effective_date,-12)+1,effective_date
2844 from pay_payroll_actions ppa,
2845 pay_assignment_actions paa
2846 where ppa.payroll_action_id = paa.payroll_action_id
2847 and paa.assignment_action_id = c_assignment_action_id;
2848
2849 /*Bug:3233249*/
2850 CURSOR get_balance_feed_exist(c_balance_name pay_balance_types.balance_name%TYPE,
2851 c_start_date pay_payroll_actions.effective_date%TYPE,
2852 c_end_date pay_payroll_actions.effective_date%TYPE)
2853 IS
2854 select '1' from pay_balance_feeds_f pbf,
2855 pay_balance_types pbt
2856 where pbf.balance_type_id = pbt.balance_type_id
2857 and pbt.balance_name = c_balance_name
2858 and pbf.effective_start_date <= c_end_date
2859 and pbf.effective_end_date >= c_start_date;
2860
2861
2862 BEGIN
2863 if p_tax_unit_id is not null then
2864 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
2865 end if;
2866 l_balance_name := 'W2 Govt EE Contrib';
2867
2868 OPEN get_effective_date(p_assignment_action_id);
2869 FETCH get_effective_date INTO l_effective_start_date
2870 ,l_effective_end_date;
2871
2872 hr_utility.trace('l_effective_start_date = '||l_effective_start_date);
2873 hr_utility.trace('l_effective_end_date = '||l_effective_end_date);
2874
2875 IF get_effective_date%NOTFOUND THEN
2876
2877 hr_utility.trace('Effective Date not found for given
2878 assignment_action_id ='||to_char(p_assignment_action_id));
2879
2880 END IF;
2881
2882 CLOSE get_effective_date;
2883
2884 OPEN get_balance_feed_exist(l_balance_name,
2885 l_effective_start_date,
2886 l_effective_end_date);
2887
2888 FETCH get_balance_feed_exist INTO l_exists;
2889
2890
2891 If get_balance_feed_exist%NOTFOUND THEN
2892
2893 hr_utility.trace('get_balance_feed_exist%NOTFOUND');
2894 return 0;
2895
2896 ELSE
2897
2898 /* Live Balance Call Procedure */
2899 l_bal_id := bal_db_item('W2_GOVT_EE_CONTRIB_PER_GRE_YTD');
2900 hr_utility.trace('get_balance_feed_exist FOUND');
2901 lv_bal_amt := nvl(pay_balance_pkg.get_value
2902 (p_defined_balance_id => l_bal_id,
2903 p_assignment_action_id => p_assignment_action_id),0);
2904 return (to_char(lv_bal_amt));
2905
2906 END IF;
2907
2908 END get_live_ee_contrib;
2909
2910 /*********************************************************************
2911 Name : get_ff_archive_value
2912
2913 Description : Definition for formula function GET_ARCHIVE_VALUE.
2914 Calls the get_archive_value function to fetch the
2915 archived value for an user entity
2916 ********************************************************************/
2917
2918 FUNCTION get_ff_archive_value (
2919 p_action_id NUMBER, -- context
2920 p_jurisdiction_code VARCHAR2, -- context
2921 p_tax_unit_id NUMBER, -- context
2922 p_data_type VARCHAR2
2923 )
2924 RETURN NUMBER IS
2925
2926 lv_return_value NUMBER;
2927 lv_data_type VARCHAR2(100);
2928 /*bug 4011829 */
2929 l_jurisdiction_code VARCHAR(11);
2930
2931
2932 BEGIN
2933 lv_return_value := 0;
2934 hr_utility.trace('In pay_us_reporting_utils_pkg.get_ff_archive_value');
2935
2936 hr_utility.trace('p_tax_unit_id = '||p_tax_unit_id);
2937 hr_utility.trace('p_assignment_action_id = '||p_action_id);
2938
2939 /*
2940
2941 -- remarked by tmehra, as discussed with Dipen and Mehul
2942 -- The whole if block would be removed after Mehul clarifies the
2943 -- 'A_W2_HSA_PER_GRE_YTD' usage.
2944
2945 IF p_data_type = 'ER_HSA' THEN
2946 lv_data_type := 'A_W2_HSA_PER_GRE_YTD';
2947 ELSIF p_data_type = 'IN_STATE_ADV_EIC' THEN
2948 lv_data_type := 'A_STEIC_ADVANCE_PER_JD_GRE_YTD';
2949 ELSIF p_data_type = 'IN_FED_ADV_EIC' THEN
2950 lv_data_type := 'A_EIC_ADVANCE_PER_GRE_YTD';
2951 ELSE lv_data_type := NULL;
2952 END IF;
2953 */
2954 if p_data_type like '%_JD_%' THEN
2955
2956 l_jurisdiction_code := substr(p_jurisdiction_code,1,2)||'-000-0000';
2957
2958 else
2959
2960 l_jurisdiction_code := NULL;
2961
2962 end if;
2963
2964 lv_return_value := to_number(pay_us_archive_util.get_archive_value(
2965 p_action_id,
2966 p_data_type,
2967 p_tax_unit_id,
2968 l_jurisdiction_code
2969 ));
2970
2971 hr_utility.trace('lv_return_value = ' || lv_return_value);
2972
2973
2974 RETURN nvl(lv_return_value,0);
2975
2976 END get_ff_archive_value;
2977
2978
2979 FUNCTION get_employee_count( p_payroll_action_id number, --context
2980 p_tax_unit_id number, --context
2981 p_state varchar2 default null
2982 ) RETURN number IS
2983 lv_employee_count number ;
2984 BEGIN
2985 hr_utility.trace('Inside pay_us_reporting_utils_pkg.get_employee_count') ;
2986 hr_utility.trace('p_payroll_action_id := '||p_payroll_action_id) ;
2987 hr_utility.trace('p_tax_unit_id := ' || p_tax_unit_id) ;
2988
2989 select count(*)
2990 into lv_employee_count
2991 from pay_payroll_actions ppa,
2992 pay_assignment_actions paa
2993 where ppa.payroll_action_id = p_payroll_action_id
2994 and paa.tax_unit_id = p_tax_unit_id
2995 and ppa.payroll_action_id = paa.payroll_action_id ;
2996
2997 hr_utility.trace('lv_employee_count := '||lv_employee_count) ;
2998 RETURN lv_employee_count ;
2999
3000 END get_employee_count ;
3001
3002
3003 FUNCTION get_employee_count_monthwise( p_payroll_action_id number, --context
3004 p_tax_unit_id number, --context
3005 p_database_item_name varchar2
3006 ) RETURN number is
3007 lv_employee_count number ;
3008 temp_month_count number;
3009 return_month_count number;
3010
3011 cursor get_all_assignment (cur_payroll_action_id number, cur_tax_unit_id number)
3012 is
3013 Select paa.assignment_action_id
3014 from pay_assignment_actions paa
3015 where payroll_action_id = cur_payroll_action_id
3016 and tax_unit_id = cur_tax_unit_id;
3017
3018 BEGIN
3019
3020 return_month_count := 0;
3021
3022 FOR i in get_all_assignment (p_payroll_action_id, p_tax_unit_id) LOOP
3023
3024 Select fai.value
3025 into lv_employee_count
3026 from ff_archive_items fai,
3027 ff_database_items fdi
3028 where fdi.user_name = p_database_item_name /*eg A_SQWL_MONTH1_COUNT */
3029 and fai.user_entity_id = fdi.user_entity_id
3030 and fai.context1 = i.assignment_action_id ;
3031
3032 IF lv_employee_count is null THEN
3033 temp_month_count := pay_us_sqwl_misc.get_Old_Month1_Count( i.assignment_action_id);
3034 ELSE
3035 temp_month_count := lv_employee_count ;
3036 END IF;
3037
3038 IF (temp_month_count <> 0 ) THEN
3039 return_month_count := return_month_count + 1;
3040 END IF;
3041
3042 END LOOP;
3043
3044 RETURN return_month_count ;
3045
3046 END get_employee_count_monthwise;
3047
3048 FUNCTION get_total_wages( p_payroll_action_id number, --context
3049 p_tax_unit_id number, --context
3050 p_state varchar2,
3051 p_report_type varchar2 default 'SQWL',
3052 p_balance_name varchar2 default null
3053 ) RETURN number IS
3054 lv_total_wages number ;
3055
3056 CURSOR cur_employee_count(p_payroll_action_id number,
3057 p_tax_unit_id number,
3058 p_state varchar2) IS
3059 SELECT count(*)
3060 FROM ff_archive_item_contexts faic,
3061 ff_archive_items fai,
3062 ff_database_items fdi,
3063 pay_assignment_actions paa,
3064 pay_payroll_actions ppa
3065 WHERE
3066 ppa.payroll_action_id = p_payroll_action_id
3067 and ppa.payroll_action_id = paa.payroll_action_id
3068 and fdi.user_name = 'A_STATE_ABBREV'
3069 and fdi.user_entity_id = fai.user_entity_id
3070 and fai.archive_item_id = faic.archive_item_id
3071 and fai.context1 = paa.assignment_action_id
3072 and fai.value = p_state
3073 and paa.tax_unit_id = p_tax_unit_id
3074 and paa.action_status = 'C'
3075 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
3076 'A_W2_STATE_WAGES',
3077 paa.tax_unit_id,
3078 faic.context , 2),0) > 0
3079 and not exists
3080 (
3081 select 'x'
3082 from hr_organization_information hoi
3083 WHERE hoi.organization_id = paa.tax_unit_id
3084 and hoi.org_information_context ='1099R Magnetic Report Rules'
3085 ) ;
3086
3087 lv_employee_count NUMBER ;
3088
3089 --Modified the cursor for the Bug 13693872 to eliminate the duplicates.
3090 -- Commenting the fix done for the Bug 13693872
3091 --Achiving the same thing by using the different cursor for MD State Pickup because
3092 --if we use DISTINCT some balnces could be elimenated if two different persons have the same amount.
3093
3094 /*
3095 CURSOR cur_summed_balance(p_payroll_action_id number,
3096 p_tax_unit_id number,
3097 p_state varchar2) IS
3098 SELECT t1.user_name,sum(to_number(nvl(t1.value,'0')))
3099 FROM (
3100 SELECT distinct fdi1.user_name,fai1.value
3101 --sum(to_number(nvl( fai1.value,'0')))
3102 FROM ff_archive_item_contexts faic,
3103 ff_archive_items fai,
3104 ff_database_items fdi,
3105 pay_assignment_actions paa,
3106 pay_payroll_actions ppa,
3107 ff_archive_items fai1,
3108 ff_database_items fdi1,
3109 ff_archive_item_contexts faic1,
3110 ff_contexts fc,
3111 pay_us_states pus
3112 WHERE
3113 ppa.payroll_action_id = p_payroll_action_id
3114 and ppa.payroll_action_id = paa.payroll_action_id
3115 and fdi.user_name = 'A_STATE_ABBREV'
3116 and fdi.user_entity_id = fai.user_entity_id
3117 and fai.archive_item_id = faic.archive_item_id
3118 and fai.context1 = paa.assignment_action_id
3119 and fai.value = p_state
3120 and paa.tax_unit_id = p_tax_unit_id
3121 and paa.action_status = 'C'
3122 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
3123 'A_W2_STATE_WAGES',
3124 paa.tax_unit_id,
3125 faic.context , 2),0) > 0
3126 and not exists
3127 (
3128 select 'x'
3129 from hr_organization_information hoi
3130 WHERE hoi.organization_id = paa.tax_unit_id
3131 and hoi.org_information_context ='1099R Magnetic Report Rules'
3132 )
3133
3134 and fdi1.user_name in (
3135 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
3136 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
3137 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
3138 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD',
3139 'A_W2_STATE_PICKUP_PER_GRE_YTD')
3140 and fdi1.user_entity_id = fai1.user_entity_id
3141 and fai1.context1 = paa.assignment_action_id
3142 and fai1.archive_item_id = faic1.archive_item_id
3143 and (
3144 (
3145 faic1.context_id = fc.context_id
3146 and fc.context_name = 'JURISDICTION_CODE'
3147 and substr(faic1.context,1,2) = pus.state_code
3148 and pus.state_abbrev = p_state
3149 )
3150 or
3151 not exists (select 'x'
3152 from ff_archive_items fai2,
3153 ff_archive_item_contexts faic2,
3154 ff_contexts fc2
3155 where fai2.user_entity_id = fdi1.user_entity_id
3156 and fai2.context1 = fai1.context1
3157 and fai2.archive_item_id = fai1.archive_item_id
3158 and fai2.archive_item_id = faic2.archive_item_id
3159 and faic2.context_id = fc2.context_id
3160 and fc2.context_name = 'JURISDICTION_CODE')
3161 )) t1
3162 group by t1.user_name ;
3163 */
3164
3165 CURSOR cur_summed_balance(p_payroll_action_id number,
3166 p_tax_unit_id number,
3167 p_state varchar2) IS
3168 SELECT fdi1.user_name,
3169 sum(to_number(nvl(fai1.value,'0')))
3170 FROM ff_archive_item_contexts faic,
3171 ff_archive_items fai,
3172 ff_database_items fdi,
3173 pay_assignment_actions paa,
3174 pay_payroll_actions ppa,
3175 ff_archive_items fai1,
3176 ff_database_items fdi1,
3177 ff_archive_item_contexts faic1,
3178 ff_contexts fc,
3179 pay_us_states pus
3180 WHERE
3181 ppa.payroll_action_id = p_payroll_action_id
3182 and ppa.payroll_action_id = paa.payroll_action_id
3183 and fdi.user_name = 'A_STATE_ABBREV'
3184 and fdi.user_entity_id = fai.user_entity_id
3185 and fai.archive_item_id = faic.archive_item_id
3186 and fai.context1 = paa.assignment_action_id
3187 and fai.value = p_state
3188 and paa.tax_unit_id = p_tax_unit_id
3189 and paa.action_status = 'C'
3190 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
3191 'A_W2_STATE_WAGES',
3192 paa.tax_unit_id,
3193 faic.context , 2),0) > 0
3194 and not exists
3195 (
3196 select 'x'
3197 from hr_organization_information hoi
3198 WHERE hoi.organization_id = paa.tax_unit_id
3199 and hoi.org_information_context ='1099R Magnetic Report Rules'
3200 )
3201
3202 and fdi1.user_name in (
3203 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
3204 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
3205 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
3206 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD'
3207 --'A_W2_STATE_PICKUP_PER_GRE_YTD'
3208 )
3209 and fdi1.user_entity_id = fai1.user_entity_id
3210 and fai1.context1 = paa.assignment_action_id
3211 and fai1.archive_item_id = faic1.archive_item_id
3212 and (
3213 (
3214 faic1.context_id = fc.context_id
3215 and fc.context_name = 'JURISDICTION_CODE'
3216 and substr(faic1.context,1,2) = pus.state_code
3217 and pus.state_abbrev = p_state
3218 )--Commented the below clause as it is not required for SIT balances
3219 /* or
3220 not exists (select 'x'
3221 from ff_archive_items fai2,
3222 ff_archive_item_contexts faic2,
3223 ff_contexts fc2
3224 where fai2.user_entity_id = fdi1.user_entity_id
3225 and fai2.context1 = fai1.context1
3226 and fai2.archive_item_id = fai1.archive_item_id
3227 and fai2.archive_item_id = faic2.archive_item_id
3228 and faic2.context_id = fc2.context_id
3229 and fc2.context_name = 'JURISDICTION_CODE') */
3230 )
3231 group by fdi1.user_name ;
3232
3233 --New cursor for MD State Pickup, Bug #13693872
3234
3235 CURSOR cur_summed_balance_md(p_payroll_action_id number,
3236 p_tax_unit_id number,
3237 p_state varchar2) IS
3238 SELECT fdi1.user_name,
3239 sum(to_number(nvl(fai1.value,'0')))
3240 FROM ff_archive_item_contexts faic,
3241 ff_archive_items fai,
3242 ff_database_items fdi,
3243 pay_assignment_actions paa,
3244 pay_payroll_actions ppa,
3245 ff_archive_items fai1,
3246 ff_database_items fdi1
3247 --ff_archive_item_contexts faic1,
3248 --ff_contexts fc,
3249 --pay_us_states pus
3250 WHERE
3251 ppa.payroll_action_id = p_payroll_action_id
3252 and ppa.payroll_action_id = paa.payroll_action_id
3253 and fdi.user_name = 'A_STATE_ABBREV'
3254 and fdi.user_entity_id = fai.user_entity_id
3255 and fai.archive_item_id = faic.archive_item_id
3256 and fai.context1 = paa.assignment_action_id
3257 and fai.value = p_state
3258 and paa.tax_unit_id = p_tax_unit_id
3259 and paa.action_status = 'C'
3260 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
3261 'A_W2_STATE_WAGES',
3262 paa.tax_unit_id,
3263 faic.context , 2),0) > 0
3264 and not exists
3265 (
3266 select 'x'
3267 from hr_organization_information hoi
3268 WHERE hoi.organization_id = paa.tax_unit_id
3269 and hoi.org_information_context ='1099R Magnetic Report Rules'
3270 )
3271
3272 and fdi1.user_name = 'A_W2_STATE_PICKUP_PER_GRE_YTD'
3273 and fdi1.user_entity_id = fai1.user_entity_id
3274 and fai1.context1 = paa.assignment_action_id
3275 /*and fai1.archive_item_id = faic1.archive_item_id
3276 and (
3277 (
3278 faic1.context_id = fc.context_id
3279 and fc.context_name = 'JURISDICTION_CODE'
3280 and substr(faic1.context,1,2) = pus.state_code
3281 and pus.state_abbrev = p_state
3282 )
3283 or
3284 not exists (select 'x'
3285 from ff_archive_items fai2,
3286 ff_archive_item_contexts faic2,
3287 ff_contexts fc2
3288 where fai2.user_entity_id = fdi1.user_entity_id
3289 and fai2.context1 = fai1.context1
3290 and fai2.archive_item_id = fai1.archive_item_id
3291 and fai2.archive_item_id = faic2.archive_item_id
3292 and faic2.context_id = fc2.context_id
3293 and fc2.context_name = 'JURISDICTION_CODE')
3294 ) */
3295 group by fdi1.user_name ;
3296
3297 TYPE balance_rec IS RECORD ( dbi_name VARCHAR2(200),
3298 summed_balance_value NUMBER ) ;
3299 TYPE w2_bal_tab IS TABLE OF balance_rec INDEX BY BINARY_INTEGER ;
3300 lv_tot_wage_tab w2_bal_tab ;
3301 i NUMBER ;
3302
3303 BEGIN
3304
3305 hr_utility.trace('Inside pay_us_reporting_utils_pkg.get_total_wages') ;
3306 hr_utility.trace('p_payroll_action_id := '||p_payroll_action_id) ;
3307 hr_utility.trace('p_tax_unit_id := ' || p_tax_unit_id) ;
3308 hr_utility.trace('p_state := ' || p_state) ;
3309 hr_utility.trace('p_report_type := ' || p_report_type) ;
3310 hr_utility.trace('p_balance_name := ' || p_balance_name) ;
3311
3312 lv_total_wages := 0 ;
3313 lv_employee_count := 0 ;
3314
3315 IF p_report_type = 'SQWL' THEN
3316
3317 IF p_state = 'MI' or p_state='NM' THEN
3318
3319 select sum(to_number(nvl(fai.value, '0')) - to_number(nvl(fai1.value, '0')))
3320 into lv_total_wages
3321 from pay_payroll_actions ppa,
3322 pay_assignment_actions paa,
3323 ff_archive_items fai,
3324 ff_archive_items fai1,
3325 ff_database_items fdi,
3326 ff_database_items fdi1
3327 where ppa.payroll_action_id = p_payroll_action_id
3328 and ppa.payroll_action_id = paa.payroll_action_id
3329 and paa.tax_unit_id = p_tax_unit_id
3330 and fai.context1 = paa.assignment_action_id
3331 and fai.user_entity_id = fdi.user_entity_id
3332 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
3333 and fai1.context1 = fai.context1
3334 and fai1.user_entity_id = fdi1.user_entity_id
3335 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD' ;
3336
3337 RETURN lv_total_wages ;
3338 END IF ;
3339
3340 ELSIF p_report_type = 'W2' THEN
3341 IF p_state = 'MD' THEN
3342 IF p_balance_name <> 'Other State Data' THEN--Added for the Bug 13693872
3343
3344 OPEN cur_summed_balance(p_payroll_action_id,
3345 p_tax_unit_id,
3346 p_state) ;
3347 i := 0 ;
3348 LOOP
3349 i := i + 1 ;
3350 FETCH cur_summed_balance INTO lv_tot_wage_tab(i).dbi_name,
3351 lv_tot_wage_tab(i).summed_balance_value;
3352
3353 EXIT WHEN cur_summed_balance%NOTFOUND ;
3354
3355 END LOOP ;
3356 CLOSE cur_summed_balance ;
3357
3358 ELSE
3359
3360 OPEN cur_summed_balance_md(p_payroll_action_id,
3361 p_tax_unit_id,
3362 p_state) ;
3363 i := 0 ;
3364 LOOP
3365 i := i + 1 ;
3366 FETCH cur_summed_balance_md INTO lv_tot_wage_tab(i).dbi_name,
3367 lv_tot_wage_tab(i).summed_balance_value;
3368
3369 EXIT WHEN cur_summed_balance_md%NOTFOUND ;
3370
3371 END LOOP ;
3372
3373 CLOSE cur_summed_balance_md ;
3374
3375 END IF;--p_balance_name
3376
3377 IF p_balance_name = 'SIT Withheld' THEN
3378 FOR j IN 1..(i - 1)
3379 LOOP
3380 IF lv_tot_wage_tab(j).dbi_name = 'A_SIT_WITHHELD_PER_JD_GRE_YTD' THEN
3381 lv_total_wages := lv_tot_wage_tab(j).summed_balance_value ;
3382 EXIT ;
3383 END IF ;
3384 END LOOP ;
3385 hr_utility.trace('p_balance_name := '||p_balance_name) ;
3386 hr_utility.trace('lv_total_wages := '||lv_total_wages) ;
3387
3388 RETURN lv_total_wages ;
3389
3390 ELSIF p_balance_name = 'State taxable Wages' THEN
3391 FOR j IN 1..(i - 1)
3392 LOOP
3393 IF lv_tot_wage_tab(j).dbi_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' THEN
3394 lv_total_wages := lv_total_wages + lv_tot_wage_tab(j).summed_balance_value ;
3395 ELSIF lv_tot_wage_tab(j).dbi_name = 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD' THEN
3396 lv_total_wages := lv_total_wages + lv_tot_wage_tab(j).summed_balance_value ;
3397 ELSIF lv_tot_wage_tab(j).dbi_name = 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD' THEN
3398 lv_total_wages := lv_total_wages - lv_tot_wage_tab(j).summed_balance_value ;
3399 END IF ;
3400 END LOOP ;
3401 hr_utility.trace('p_balance_name := '||p_balance_name) ;
3402 hr_utility.trace('lv_total_wages := '||lv_total_wages) ;
3403
3404 RETURN lv_total_wages ;
3405
3406 ELSIF p_balance_name = 'Other State Data' THEN
3407 FOR j IN 1..(i - 1)
3408 LOOP
3409 IF lv_tot_wage_tab(j).dbi_name = 'A_W2_STATE_PICKUP_PER_GRE_YTD' THEN
3410 lv_total_wages := lv_tot_wage_tab(j).summed_balance_value ;
3411 EXIT ;
3412 END IF ;
3413 END LOOP ;
3414 hr_utility.trace('p_balance_name := '||p_balance_name) ;
3415 hr_utility.trace('lv_total_wages := '||lv_total_wages) ;
3416
3417 RETURN lv_total_wages ;
3418 END IF ; -- p_balance_name
3419 END IF ; -- p_state
3420
3421 IF p_balance_name = 'Employee Count' THEN
3422 IF p_state = 'MD' THEN
3423 OPEN cur_employee_count(p_payroll_action_id,
3424 p_tax_unit_id,
3425 p_state) ;
3426 FETCH cur_employee_count INTO lv_employee_count ;
3427 CLOSE cur_employee_count ;
3428
3429 RETURN lv_employee_count ;
3430 END IF ;
3431 END IF ;
3432 END IF ; -- p_report_type
3433
3434 END get_total_wages ;
3435
3436 /* sackumar */
3437
3438 function get_wages(p_payroll_action_id number, --context
3439 p_tax_unit_id number, --context
3440 p_state varchar2,
3441 p_excess_wages out nocopy number,
3442 p_withholding out nocopy number,
3443 p_workerscomp out nocopy number
3444 ) return number IS
3445 BEGIN
3446 select nvl(sum(to_number(nvl(fai.value, '0'))
3447 - to_number(nvl(fai1.value, '0'))
3448 - to_number(nvl(fai2.value, '0'))
3449 ),0)
3450 into p_excess_wages
3451 from pay_payroll_actions ppa,
3452 pay_assignment_actions paa,
3453 ff_archive_items fai,
3454 ff_archive_items fai1,
3455 ff_archive_items fai2,
3456 ff_database_items fdi,
3457 ff_database_items fdi1,
3458 ff_database_items fdi2
3459 where ppa.payroll_action_id = p_payroll_action_id
3460 and ppa.payroll_action_id = paa.payroll_action_id
3461 and paa.tax_unit_id = p_tax_unit_id
3462 and fai.context1 = paa.assignment_action_id
3463 and fai.user_entity_id = fdi.user_entity_id
3464 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
3465 and fai1.context1 = fai.context1
3466 and fai1.user_entity_id = fdi1.user_entity_id
3467 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
3468 and fai2.context1 = fai1.context1
3469 and fai2.user_entity_id = fdi2.user_entity_id
3470 and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD' ;
3471
3472 select nvl(sum(to_number(nvl(fai.value, '0'))),0)
3473 into p_withholding
3474 from pay_payroll_actions ppa,
3475 pay_assignment_actions paa,
3476 ff_archive_items fai,
3477 ff_database_items fdi
3478 where ppa.payroll_action_id = p_payroll_action_id
3479 and ppa.payroll_action_id = paa.payroll_action_id
3480 and paa.tax_unit_id = p_tax_unit_id
3481 and fai.context1 = paa.assignment_action_id
3482 and fai.user_entity_id = fdi.user_entity_id
3483 and fdi.user_name = 'A_SIT_WITHHELD_PER_JD_GRE_QTD';
3484
3485 select nvl(sum(to_number(nvl(fai.value, '0'))
3486 + to_number(nvl(fai1.value, '0'))
3487 + to_number(nvl(fai2.value, '0'))
3488 ),0)
3489 into p_workerscomp
3490 from pay_payroll_actions ppa,
3491 pay_assignment_actions paa,
3492 ff_archive_items fai,
3493 ff_archive_items fai1,
3494 ff_archive_items fai2,
3495 ff_database_items fdi,
3496 ff_database_items fdi1,
3497 ff_database_items fdi2
3498 where ppa.payroll_action_id = p_payroll_action_id
3499 and ppa.payroll_action_id = paa.payroll_action_id
3500 and paa.tax_unit_id = p_tax_unit_id
3501 and fai.context1 = paa.assignment_action_id
3502 and fai.user_entity_id = fdi.user_entity_id
3503 and fdi.user_name = 'A_WORKERS_COMPENSATION2_ER_PER_JD_GRE_QTD'
3504 and fai1.context1 = fai.context1
3505 and fai1.user_entity_id = fdi1.user_entity_id
3506 and fdi1.user_name = 'A_WORKERS_COMP2_WITHHELD_PER_JD_GRE_QTD'
3507 and fai2.context1 = fai1.context1
3508 and fai2.user_entity_id = fdi2.user_entity_id
3509 and fdi2.user_name = 'A_WORKERS_COMP_WITHHELD_PER_JD_GRE_QTD' ;
3510
3511 return(0);
3512 END get_wages ;
3513
3514
3515 FUNCTION GET_SUI_WAGES(p_payroll_action_id number, --context
3516 p_tax_unit_id number, --context
3517 p_state varchar2,
3518 p_sui_gross out nocopy number,
3519 p_sui_subj out nocopy number,
3520 p_sui_pre_tax out nocopy number,
3521 p_sui_taxable out nocopy number
3522 )return number is
3523
3524 CURSOR cur_fl_sqwl IS
3525 select 'Y' from pay_payroll_actions
3526 where payroll_action_id=p_payroll_action_id
3527 and report_type='SQWL'
3528 and report_qualifier='FL';
3529
3530 CURSOR cur_oh_sqwl IS
3531 select 'Y' from pay_payroll_actions
3532 where payroll_action_id=p_payroll_action_id
3533 and report_type='SQWL'
3534 and report_qualifier='OH';
3535
3536 l_fl_sqwl VARCHAR2(2);
3537 l_oh_sqwl VARCHAR2(2);
3538 l_sui_adj_taxable number;
3539
3540
3541 BEGIN
3542
3543
3544 select nvl(sum(to_number(nvl(fai.value, '0'))),0),
3545 nvl(sum(to_number(nvl(fai1.value, '0'))),0),
3546 nvl(sum(to_number(nvl(fai2.value, '0'))),0),
3547 nvl(sum(to_number(nvl(fai3.value, '0'))),0)
3548 into p_sui_subj,
3549 p_sui_pre_tax,
3550 p_sui_taxable,
3551 p_sui_gross
3552 from pay_payroll_actions ppa,
3553 pay_assignment_actions paa,
3554 ff_archive_items fai,
3555 ff_archive_items fai1,
3556 ff_archive_items fai2,
3557 ff_archive_items fai3,
3558 ff_database_items fdi,
3559 ff_database_items fdi1,
3560 ff_database_items fdi2,
3561 ff_database_items fdi3
3562 where ppa.payroll_action_id = p_payroll_action_id
3563 and ppa.payroll_action_id = paa.payroll_action_id
3564 and paa.tax_unit_id = p_tax_unit_id
3565 and fai.context1 = paa.assignment_action_id
3566 and fai.user_entity_id = fdi.user_entity_id
3567 and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
3568 and fai1.context1 = fai.context1
3569 and fai1.user_entity_id = fdi1.user_entity_id
3570 and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
3571 and fai2.context1 = fai1.context1
3572 and fai2.user_entity_id = fdi2.user_entity_id
3573 and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD'
3574 and fai3.context1 = fai1.context1
3575 and fai3.user_entity_id = fdi3.user_entity_id
3576 and fdi3.user_name = 'A_SUI_ER_GROSS_PER_JD_GRE_QTD'
3577 and length(translate(trim(fai.value),' .0123456789',' ')) is null
3578 and length(translate(trim(fai2.value),' .0123456789',' ')) is null ;
3579
3580 hr_utility.trace('Actual SUI Taxable Amount'||p_sui_taxable);
3581
3582 open cur_fl_sqwl;
3583 fetch cur_fl_sqwl into l_fl_sqwl;
3584 if l_fl_sqwl = 'Y'
3585 then
3586 hr_utility.trace('Getting the Adjusted Taxable for Florida');
3587
3588 select nvl(sum(to_number(nvl(fai.value, '0'))),0)
3589 into p_sui_taxable
3590 from pay_payroll_actions ppa,
3591 pay_assignment_actions paa,
3592 ff_archive_items fai,
3593 ff_database_items fdi
3594 where ppa.payroll_action_id = p_payroll_action_id
3595 and ppa.payroll_action_id = paa.payroll_action_id
3596 and paa.tax_unit_id = p_tax_unit_id
3597 and fai.context1 = paa.assignment_action_id
3598 and fai.user_entity_id = fdi.user_entity_id
3599 and fdi.user_name = 'SUI_ER_FL_ADJ_TAXABLE_PER_JD_GRE_QTD'
3600 and length(translate(trim(fai.value),' .0123456789',' ')) is null;
3601
3602
3603 hr_utility.trace('Adjusted SUI Taxable Amount for Florida'||p_sui_taxable);
3604
3605 end if;
3606 close cur_fl_sqwl;
3607
3608 open cur_oh_sqwl;
3609 fetch cur_oh_sqwl into l_oh_sqwl;
3610
3611 if l_oh_sqwl = 'Y'
3612 then
3613
3614 hr_utility.trace('Getting the Out of State Taxable for Ohio');
3615
3616 select nvl(sum(to_number(nvl(fai.value, '0'))),0)
3617 into p_sui_taxable
3618 from pay_payroll_actions ppa,
3619 pay_assignment_actions paa,
3620 ff_archive_items fai,
3621 ff_database_items fdi
3622 where ppa.payroll_action_id = p_payroll_action_id
3623 and ppa.payroll_action_id = paa.payroll_action_id
3624 and paa.tax_unit_id = p_tax_unit_id
3625 and fai.context1 = paa.assignment_action_id
3626 and fai.user_entity_id = fdi.user_entity_id
3627 and fdi.user_name = 'A_SUI_OH_ER_ADJ_TAXABLE_ASG_GRE_QTD'
3628 and length(translate(trim(fai.value),' .0123456789',' ')) is null;
3629 -- This will find out the required data for T55-68 record in magnetic file
3630 -- of SQWL report of Ohio
3631
3632 p_sui_taxable := ROUND(p_sui_taxable,2) * 100;
3633 /* This value goes into T record of SQWL Report */
3634 end if;
3635 close cur_oh_sqwl;
3636
3637 return(0);
3638
3639 END GET_SUI_WAGES;
3640 --BEGIN
3641 --hr_utility.trace_on(null,'MMREF');
3642
3643 END pay_us_reporting_utils_pkg;