1 PACKAGE pay_us_mmref_reporting AUTHID CURRENT_USER AS
2 /* $Header: pyusmmye.pkh 120.8.12020000.2 2012/10/30 14:41:18 pkoduri ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_mmref_reporting
10
11 Purpose
12 The purpose of this package is to support the generation of magnetic tape
13 in MMREF - 1 Format. This magnetic tapes are for US legilsative requirements
14 incorporating magtape resilience and the new end-of-year design.
15
16 Notes
17 The generation of each magnetic tape report is a two stage process i.e.
18 1. Check if the year end pre-processor has been run for all the GREs
19 and the assignments. If not, then error out without processing further.
20 2. Create a payroll action for the report. Identify all the assignments
21 to be reported and record an assignment action against the payroll action
22 for each one of them.
23 3. Run the generic magnetic tape process which will
24 drive off the data created in stage two. This will result in the
25 production of a structured ascii file which can be transferred to
26 magnetic tape and sent to the relevant authority.
27
28 History
29 Date Author Verion Bug Details
30 ----------- -------- ------- -------- -------------------------------------
31 14-sep-2001 djoshi 115.0 Created
32 18-sep-2001 djoshi 115.1 Added the changes for Smart
33 Archive calls
34 20-sep-2001 djoshi 115.2 Added function check_gre_data
35 and removed all ref. to all tables
36 16-nov-2001 djoshi 115.3 Added the changes for State
37 Magnetic Tapes
38 03-dec-2001 djoshi 115.6 Changed the file for dbdrv postion
39 and employee cursor
40 05-dec-2001 djoshi 115.7 Added function to chech state tax rules
41 22-jan-2002 djoshi 115.8 Added check_file for GSCC
42 14-Nov-2002 ppanda 115.9 Added transfer_locality_code to driving
43 cursor for Fed and State submitter
44 The new column added for Local W-2 Mag
45 changes
46 02-Dec-2002 ppanda 115.10 Nocopy hint added to OUT and IN OUT
47 parameters
48 20-Jan-2003 ppanda 115.11 2736928 For PuertoRico a new Employee Cursor
49 created to have sorting order as Last Name,
50 First Name, Middle Name, Person_ID
51 This new sorting order is used due to
52 generation of Control number for each
53 employee depending on the starting Control
54 Number defined at the GRE level.
55 15-Nov-2003 tmehra 115.12 2219097 Made changes to mmrf_employer
56 and mmrf_employee cursor for
57 FED W2 employment_code requirement
58 20-Nov-2003 tmehra 115.13 Changed the parameter name from
59 EMP_CODE to TRANSFER_EMP_CODE
60 26-Nov-2003 tmehra 115.14 2219097 Added a new function for Govt
61 Employer W2 changes
62 - get_report_category
63 26-Nov-2003 tmehra 115.15 Added two new cursors
64 - govt_mmrf_employer
65 - govt_mmrf_employee
66 02-Dec-2003 tmehra 115.16 Modified govt_mmrf_employee cursor.
67 03-Dec-2003 tmehra 115.17 Modified govt_mmrf_employer cursor.
68 28-DEC-2004 ahanda 115.19 Changed employee cursor for performance
69 04-JAN-2004 ahanda 115.20 Changed per_assignments_f to
70 per_all_assignments_f
71 18-NOV-2005 saurgupt 115.21 4644692 Added ORDERED hint in cursor mmrf_employee.
72 04-AUG-2006 ppanda 115.22 Added following new function to support multithread architecture
73 in Federal W-2 Magnetic Media
74 - get_report_category_mt
75 28-AUG-2006 ppanda 115.31 Three New formula function added to the package
76 for Federal W-2 Magnetic Media MultiThread process
77 13-DEC-2006 ppanda 115.36 Function modified b adding additional parameters
78 assignment_action_id, p_tax_jd_code,
79 p_tax_unit_info1 and , p_tax_unit_info2
80 This is to fix Bug # 5709609
81
82 10-JAN-2007 ppanda 115.37 Function set_application_error modified by adding a
83 new parameter assignment_Action_id
84 19-AUG-2010 asgugupt 115.38 Added global variable for bug 9467090
85 14-12-2011 SKchalla 115.55 13497022 Added two parameters to the formula function
86 get_w2_er_arch_bal for W2 Fedaral Mag in Multi mode
87 30-OCT-2012 pkoduri 115.56 14286448 Corrections for GRE name length issue.
88
89 =============================================================================*/
90 -- 'level_cnt' will allow the cursors to select function results,
91 -- whether it is a standard fuction such as to_char or a function
92 -- defined in a package (with the correct pragma restriction).
93 level_cnt NUMBER;
94 --bug 9467090
95 g_action_param_val varchar2(30);
96 --bug 9467090
97 --
98 -- Sets up the tax unit context for the Submitter
99 --
100 /* Transmitter for the State MMREF tape */
101 CURSOR state_mmrf_submitter IS
102 SELECT 'TAX_UNIT_ID=C' , HOI.organization_id,
103 'JURISDICTION_CODE=C', SR.jurisdiction_code,
104 'TRANSFER_JD=P', SR.jurisdiction_code,
105 'ASSIGNMENT_ID=C' , '-1',
106 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
107 'TRANSFER_HIGH_COUNT=P', '0',
108 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
109 'TRANSFER_COUNTY=P', '-1',
110 'TRANSFER_2678_FILER=P', HOI.org_information8,
111 'PAYROLL_ACTION_ID=C', PPA.payroll_action_id,
112 'BUSINESS_GROUP_ID=C',PPA.business_group_id,
113 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
114 FROM pay_state_rules SR,
115 hr_organization_information HOI,
116 pay_payroll_actions PPA,
117 pay_payroll_actions PPA1
118 WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
119 ('TRANSFER_PAYROLL_ACTION_ID')
120 AND ppa1.effective_date = ppa.effective_date
121 AND ppa1.report_qualifier = sr.state_code
122 AND HOI.organization_id =
123 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
124 AND SR.state_code =
125 pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
126 AND HOI.org_information_context = 'W2 Reporting Rules'
127 AND PPA.report_type = 'YREND'
128 AND HOI.ORGANIZATION_ID
129 = substr(PPA.legislative_parameters,
130 instr(PPA.legislative_parameters,
131 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
132 AND to_char(PPA.effective_date,'YYYY')
133 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
134 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
135
136 /* Context and Parameter Set in the cursor are
137 Context :
138 TAX_UNIT_ID - Submitter's Tax Unit ID
139 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
140 ASSIGNMENT_ID - Required for call to function - context not used
141 in the for Submitter
142 Date Earned - Always set to Effective date ie. in this case
143 for Mag tapes to 31-DEC-YYYY, in case of SQWL
144 this will be diffrent.
145 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
146
147 Parameters :
148 Transfer_HIGH_COUNT
149 TRANSFER_SCHOOL_DISTRICT
150 TRANSFER_COUNTY
151 TRANSFER_2678_FILER
152 TRANSFER_LOCALITY_CODE (Added for Local Mag Tape changes)
153 */
154 CURSOR fed_mmrf_submitter IS
155 SELECT 'TAX_UNIT_ID=C', HOI.organization_id,
156 'JURISDICTION_CODE=C', 'DUMMY_VALUE',
157 'TRANSFER_JD=P', 'DUMMY_VALUE',
158 'ASSIGNMENT_ID=C' , '-1',
159 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
160 'TRANSFER_HIGH_COUNT=P', '0',
161 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
162 'TRANSFER_COUNTY=P', '-1',
163 'TRANSFER_2678_FILER=P', HOI.org_information8,
164 'PAYROLL_ACTION_ID=C',ppa.payroll_action_id, -- payroll_action_id of YREND
165 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
166 FROM hr_organization_information HOI,
167 pay_payroll_actions PPA
168 WHERE HOI.organization_id =
169 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
170 AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
171 AND HOI.org_information_context = 'W2 Reporting Rules'
172 AND PPA.report_type = 'YREND'
173 AND HOI.ORGANIZATION_ID
174 = substr(PPA.legislative_parameters,
175 instr(PPA.legislative_parameters,
176 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
177 AND to_char(PPA.effective_date,'YYYY')
178 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
179 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
180
181 --
182 -- Sets up the tax unit context for each employer to be reported on NB. sets
183 -- up a parameter holding the tax unit identifier which can then be used by
184 -- subsequent cursors to restrict to employees within the employer.
185 --
186 --
187 /* Context and Parameter in the cursor are
188 Payroll_action_id table looks for value related to Year End pre-
189 processor while the pay_assignment_actions looks for
190 assignment actions of Mag. tapes
191 Context :
192 TAX_UNIT_ID - Submitter's Tax Unit ID
193 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
194 ASSIGNMENT_ID - Required for call to function - context not used
195 in the for Submitter
196 Date Earned - Always set to Effective date ie. in this case
197 for Mag tapes to 31-DEC-YYYY, in case of SQWL
198 this will be diffrent.
199 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
200
201 Parameters :
202 TAX_UNIT_ID - To be used in subsequent cusrsor
203 */
204 CURSOR mmrf_employer IS
205 SELECT DISTINCT
206 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
207 'TAX_UNIT_ID=C' , AA.tax_unit_id,
208 'TAX_UNIT_ID=P' , AA.tax_unit_id,
209 'TAX_UNIT_NAME=P' , substr(hou.name,1,80), -- Bug# 14286448 Gre length issue
210 'TRANSFER_EMP_CODE=P', 'R'
211 FROM hr_all_organization_units hou,
212 pay_payroll_actions ppa,
213 pay_assignment_actions AA
214 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
215 ('TRANSFER_PAYROLL_ACTION_ID')
216 AND ppa.report_type = 'YREND'
217 AND to_char(ppa.effective_date,'YYYY')
218 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
219 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
220 AND AA.tax_unit_id
221 = substr(ppa.legislative_parameters,
222 instr(ppa.legislative_parameters,
223 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
224 AND hou.organization_id = AA.tax_unit_id
225 order by substr(hou.name,1,80); -- Bug# 14286448 Gre length issue
226
227
228 CURSOR govt_mmrf_employer IS
229 SELECT DISTINCT
230 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
231 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
232 'TAX_UNIT_ID=C' , AA.tax_unit_id,
233 'TAX_UNIT_ID=P' , AA.tax_unit_id,
234 'TAX_UNIT_NAME=P', substr(hou.name,1,80), -- Bug# 14286448 Gre length issue
235 'TRANSFER_EMP_CODE=P' , 'R'
236 FROM hr_all_organization_units hou,
237 pay_payroll_actions ppa,
238 pay_assignment_actions AA
239 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
240 ('TRANSFER_PAYROLL_ACTION_ID')
241 AND ppa.report_type = 'YREND'
242 AND to_char(ppa.effective_date,'YYYY')
243 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
244 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
245 AND AA.tax_unit_id
246 = substr(ppa.legislative_parameters,
247 instr(ppa.legislative_parameters,
248 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
249 AND hou.organization_id = AA.tax_unit_id
250 UNION ALL
251 SELECT DISTINCT
252 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
253 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
254 'TAX_UNIT_ID=C' , AA.tax_unit_id,
255 'TAX_UNIT_ID=P' , AA.tax_unit_id,
256 'TAX_UNIT_NAME=P', substr(hou.name,1,80), -- Bug# 14286448 Gre length issue
257 'TRANSFER_EMP_CODE=P' , 'Q'
258 FROM hr_all_organization_units hou,
259 pay_payroll_actions ppa,
260 pay_assignment_actions AA
261 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
262 ('TRANSFER_PAYROLL_ACTION_ID')
263 AND ppa.report_type = 'YREND'
264 AND to_char(ppa.effective_date,'YYYY')
265 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
266 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
267 AND AA.tax_unit_id
268 = substr(ppa.legislative_parameters,
269 instr(ppa.legislative_parameters,
270 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
271 AND hou.organization_id = AA.tax_unit_id
272 and nvl(pay_us_archive_util.get_archive_value(ppa.payroll_action_id,
273 'A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER',
274 AA.tax_unit_id),'N') = 'Y'
275 order by 8; /* Bug # 8851771 */
276
277
278 --
279 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
280 -- for an employee. The date_earned context is set to be the least of the
281 -- end of the period being reported and the maximum end date of the
282 -- assignment. This ensures that personal information ie. name etc... is
283 -- current relative to the period being reported on.
284 --
285 CURSOR mmrf_employee IS
286 SELECT /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
287 'ASSIGNMENT_ID=C', AA.assignment_id,
288 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
289 (PA.effective_date, AA.assignment_id)),
290 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
291 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
292 FROM pay_payroll_actions PA,
293 pay_assignment_actions AA,
294 pay_action_interlocks AI,
295 per_all_assignments_f SS,
296 per_all_people_f PE
297 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
298 ('TRANSFER_PAYROLL_ACTION_ID')
299 AND AA.payroll_action_id = PA.payroll_action_id
300 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
301 ('TAX_UNIT_ID')
302 AND AI.locking_action_id = AA.assignment_action_id
303 AND SS.assignment_id = AA.assignment_id
304 AND PE.person_id = SS.person_id
305 AND SS.effective_start_date =
306 (select max(paf2.effective_start_date)
307 from per_all_assignments_f paf2
308 where paf2.assignment_id = SS.assignment_id
309 and paf2.effective_start_date <= PA.effective_date
310 and paf2.assignment_type = 'E')
311 AND SS.effective_end_date >= PA.start_date
312 AND SS.assignment_type = 'E'
313 AND LEAST(SS.effective_end_date, PA.effective_date)
314 between PE.effective_start_date and PE.effective_end_date
315 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
316
317
318 CURSOR govt_mmrf_employee IS
319 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
320 'ASSIGNMENT_ID=C', AA.assignment_id,
321 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
322 (PA.effective_date, AA.assignment_id)),
323 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
324 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
325 FROM per_all_people_f PE,
326 per_all_assignments_f SS,
327 pay_action_interlocks AI,
328 pay_assignment_actions AA,
329 pay_assignment_actions paa,
330 pay_payroll_actions PA,
331 ff_archive_items arch,
332 ff_user_entities fue
333 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
334 ('TRANSFER_PAYROLL_ACTION_ID')
335 AND AA.payroll_action_id = PA.payroll_action_id
336 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
337 ('TAX_UNIT_ID')
338 AND AI.locking_action_id = AA.assignment_action_id
339 AND SS.assignment_id = AA.assignment_id
340 AND PE.person_id = SS.person_id
341 AND SS.effective_start_date =
342 (select max(paf2.effective_start_date)
343 from per_all_assignments_f paf2
344 where paf2.assignment_id = SS.assignment_id
345 and paf2.effective_start_date <= PA.effective_date
346 and paf2.assignment_type = 'E')
347 AND SS.effective_end_date >= PA.start_date
348 AND SS.assignment_type = 'E'
349 AND LEAST(SS.effective_end_date, PA.effective_date)
350 between PE.effective_start_date and PE.effective_end_date
351 AND paa.payroll_action_id = pay_magtape_generic.get_parameter_value
352 ('PAYROLL_ACTION_ID')
353 AND paa.assignment_id = AA.assignment_id
354 AND arch.context1 = paa.assignment_action_id
355 AND arch.user_entity_id = fue.user_entity_id
356 AND fue.user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE'
357 AND arch.value = pay_magtape_generic.get_parameter_value
358 ('TRANSFER_EMP_CODE')
359 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
360
361
362 /* This Cursor Added to fix Bug # 2736928
363 Additional Sort Parameter Person_ID added to the Employee Cursor
364 This change made to generate serial number */
365
366 CURSOR mmrf_pr_employee IS
367 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
368 'ASSIGNMENT_ID=C', AA.assignment_id,
369 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
370 (PA.effective_date, AA.assignment_id)),
371 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
372 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
373 FROM per_all_people_f PE,
374 per_all_assignments_f SS,
375 pay_action_interlocks AI,
376 pay_assignment_actions AA,
377 pay_payroll_actions PA
378 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
379 ('TRANSFER_PAYROLL_ACTION_ID')
380 AND AA.payroll_action_id = PA.payroll_action_id
381 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
382 ('TAX_UNIT_ID')
383 AND AI.locking_action_id = AA.assignment_action_id
384 AND SS.assignment_id = AA.assignment_id
385 AND PE.person_id = SS.person_id
386 AND SS.effective_start_date =
387 (select max(paf2.effective_start_date)
388 from per_all_assignments_f paf2
389 where paf2.assignment_id = SS.assignment_id
390 and paf2.effective_start_date <= PA.effective_date
391 and paf2.assignment_type = 'E')
392 AND SS.effective_end_date >= PA.start_date
393 AND SS.assignment_type = 'E'
394 AND LEAST(SS.effective_end_date, PA.effective_date)
395 between PE.effective_start_date and PE.effective_end_date
396 ORDER BY PE.last_name, PE.first_name, PE.middle_names,PE.person_id;
397
398
399 /* Indiana has multiple RS record. This RS record will Report Locality
400 Wages for Employee. We are currently interested in getting only
401 the JD code for all Indiana County.
402 */
403 CURSOR IN_LOCAL_MMRF_EMPLOYEE IS
404 SELECT 'JURISDICTION_CODE=C', rtrim(ltrim(faic.context)),
405 'TRANSFER_YE_JURISDICTION_CODE=P', ltrim(rtrim(faic.context))
406 from ff_archive_items fai,
407 ff_contexts fc, -- JD
408 ff_database_items fdi,
409 ff_archive_item_contexts faic, -- JD
410 pay_assignment_actions paa
411 where paa.assignment_action_id
412 = pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
413 and paa.assignment_action_id = fai.context1
414 and fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
415 and fdi.user_entity_id = fai.user_entity_id
416 and faic.archive_item_id = fai.archive_item_id
417 and fc.context_name = 'JURISDICTION_CODE'
418 and faic.context_id = fc.context_id
419 and value <> '0'
420 and faic.context like '15%'
421 order by faic.context;
422
423
424 /* Ohio Cursor */
425 CURSOR OH_LOCAL_MMRF_EMPLOYEE IS
426 SELECT 'JURISDICTION_CODE=C',ltrim(rtrim(faic.context)),
427 'TRANSFER_YE_JURISDICTION_CODE=P',ltrim(rtrim(faic.context))
428 from ff_archive_items fai,
429 ff_contexts fc, -- JD
430 ff_database_items fdi,
431 ff_archive_item_contexts faic, -- JD
432 pay_assignment_actions paa
433 where paa.assignment_action_id =
434 pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
435 and paa.assignment_action_id = fai.context1
436 and (fdi.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD' OR
437 fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD' OR
438 fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD')
439 and fdi.user_entity_id = fai.user_entity_id
440 and faic.archive_item_id = fai.archive_item_id
441 and fc.context_name = 'JURISDICTION_CODE'
442 and faic.context_id = fc.context_id
443 and value <> '0'
444 and faic.context like '36%'
445 order by faic.context ;
446
447
448 FUNCTION bal_db_item(p_db_item_name VARCHAR2)
449 RETURN NUMBER;
450
451 PROCEDURE get_report_parameters(
452 p_pactid IN NUMBER,
453 p_year_start IN OUT nocopy DATE,
454 p_year_end IN OUT nocopy DATE,
455 p_state_abbrev IN OUT nocopy VARCHAR2,
456 p_state_code IN OUT nocopy VARCHAR2,
457 p_report_type IN OUT nocopy VARCHAR2,
458 p_business_group_id IN OUT nocopy NUMBER);
459
460 FUNCTION get_balance_value(
461 p_balance_name VARCHAR2,
462 p_tax_unit_id NUMBER,
463 p_state_abbrev VARCHAR2,
464 p_assignment_id NUMBER,
465 p_effective_date DATE)
466 RETURN NUMBER;
467
468 FUNCTION preprocess_check(
469 p_pactid NUMBER,
470 p_year_start DATE,
471 p_year_end DATE,
472 p_business_group_id NUMBER,
473 p_state_abbrev VARCHAR2,
474 p_state_code VARCHAR2,
475 p_report_type VARCHAR2)
476 RETURN BOOLEAN;
477
478 PROCEDURE range_cursor(
479 p_pactid IN NUMBER,
480 p_sqlstr OUT nocopy VARCHAR2);
481
482 PROCEDURE create_assignment_act(
483 p_pactid IN NUMBER,
484 p_stperson IN NUMBER,
485 p_endperson IN NUMBER,
486 p_chunk IN NUMBER );
487
488 FUNCTION check_er_data(
489 p_pactid NUMBER,
490 p_ein_user_id NUMBER)
491 RETURN varchar2;
492
493
494 FUNCTION check_state_er_data(
495 p_pactid NUMBER,
496 p_tax_unit NUMBER,
497 p_jurisdictions varchar2)
498 RETURN varchar2;
499
500
501 PROCEDURE archive_eoy_data(
502 p_pactid IN NUMBER,
503 p_tax_id IN NUMBER );
504
505 PROCEDURE archive_state_eoy_data(
506 p_pactid IN NUMBER,
507 p_tax_id IN NUMBER,
508 p_state_code IN VARCHAR2);
509
510 FUNCTION check_state_data(
511 p_payroll_action_id NUMBER,
512 p_transfer_state varchar2)
513 RETURN varchar2;
514
515 FUNCTION get_report_category(p_business_group_id number,
516 p_effective_date date)
517 RETURN varchar2;
518
519 FUNCTION get_report_category_mt(p_business_group_id number,
520 p_effective_date date)
521 RETURN varchar2;
522
523 TYPE er_sum_rec IS RECORD
524 ( bal_name varchar2(60)
525 , value varchar2(20)
526 );
527 TYPE er_sum_table IS TABLE OF
528 er_sum_rec
529 INDEX BY BINARY_INTEGER;
530
531 FUNCTION get_w2_er_arch_bal(
532 w2_balance_name in varchar2,
533 w2_tax_unit_id in varchar2,
534 w2_jurisdiction_code in varchar2,
535 w2_jurisdiction_level in varchar2,
536 w2_year varchar2,
537 a1 OUT NOCOPY varchar2,
538 a2 OUT NOCOPY varchar2,
539 a3 OUT NOCOPY varchar2,
540 a4 OUT NOCOPY varchar2,
541 a5 OUT NOCOPY varchar2,
542 a6 OUT NOCOPY varchar2,
543 a7 OUT NOCOPY varchar2,
544 a8 OUT NOCOPY varchar2,
545 a9 OUT NOCOPY varchar2,
546 a10 OUT NOCOPY varchar2,
547 a11 OUT NOCOPY varchar2,
548 a12 OUT NOCOPY varchar2,
549 a13 OUT NOCOPY varchar2,
550 a14 OUT NOCOPY varchar2,
551 a15 OUT NOCOPY varchar2,
552 a16 OUT NOCOPY varchar2,
553 a17 OUT NOCOPY varchar2,
554 a18 OUT NOCOPY varchar2,
555 a19 OUT NOCOPY varchar2,
556 a20 OUT NOCOPY varchar2,
557 a21 OUT NOCOPY varchar2,
558 a22 OUT NOCOPY varchar2,
559 a23 OUT NOCOPY varchar2,
560 a24 OUT NOCOPY varchar2,
561 a25 OUT NOCOPY varchar2,
562 a26 OUT NOCOPY varchar2,
563 a27 OUT NOCOPY varchar2,
564 a28 OUT NOCOPY varchar2,
565 a29 OUT NOCOPY varchar2,
566 a30 OUT NOCOPY varchar2,
567 a31 OUT NOCOPY varchar2,
568 a32 OUT NOCOPY varchar2,
569 a33 OUT NOCOPY varchar2,
570 a34 OUT NOCOPY varchar2,
571 a35 OUT NOCOPY varchar2,
572 a36 OUT NOCOPY varchar2,
573 a37 OUT NOCOPY varchar2,
574 a38 OUT NOCOPY varchar2,
575 a39 OUT NOCOPY varchar2,
576 a40 OUT NOCOPY varchar2,
577 a41 OUT NOCOPY varchar2,
578 a42 OUT NOCOPY varchar2,
579 a43 OUT NOCOPY varchar2,
580 a44 OUT NOCOPY varchar2,
581 a45 OUT NOCOPY varchar2,
582 a46 OUT NOCOPY varchar2,
583 a47 OUT NOCOPY varchar2,
584 a48 OUT NOCOPY varchar2, -- Bug 13497022
585 a49 OUT NOCOPY varchar2 -- Bug 13497022
586 )
587 RETURN varchar2;
588
589 FUNCTION set_application_error(p_state varchar2,
590 p_error varchar2,
591 p_assignment_action_id number
592 )
593 RETURN varchar2;
594
595 FUNCTION get_tax_unit_info (tax_unit_id IN NUMBER -- Context
596 , assignment_action_id IN NUMBER -- Context
597 ,p_tax_year IN NUMBER -- Parameter
598 ,p_federal_ein OUT NOCOPY VARCHAR2 -- Parameter
599 ,p_tax_jd_code OUT NOCOPY VARCHAR2 --Parameter
600 ,p_tax_unit_info1 OUT NOCOPY VARCHAR2 --Parameter
601 ,p_tax_unit_info2 OUT NOCOPY VARCHAR2 --Parameter
602 )
603 RETURN varchar2;
604
605 END pay_us_mmref_reporting;
[Home] [Help]