1 PACKAGE pay_us_mmref_reporting AS
2 /* $Header: pyusmmye.pkh 120.5.12010000.1 2008/07/27 23:53:31 appldev 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 =============================================================================*/
85 -- 'level_cnt' will allow the cursors to select function results,
86 -- whether it is a standard fuction such as to_char or a function
87 -- defined in a package (with the correct pragma restriction).
88 level_cnt NUMBER;
89
90 --
91 -- Sets up the tax unit context for the Submitter
92 --
93 /* Transmitter for the State MMREF tape */
94 CURSOR state_mmrf_submitter IS
95 SELECT 'TAX_UNIT_ID=C' , HOI.organization_id,
96 'JURISDICTION_CODE=C', SR.jurisdiction_code,
97 'TRANSFER_JD=P', SR.jurisdiction_code,
98 'ASSIGNMENT_ID=C' , '-1',
99 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
100 'TRANSFER_HIGH_COUNT=P', '0',
101 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
102 'TRANSFER_COUNTY=P', '-1',
103 'TRANSFER_2678_FILER=P', HOI.org_information8,
104 'PAYROLL_ACTION_ID=C', PPA.payroll_action_id,
105 'BUSINESS_GROUP_ID=C',PPA.business_group_id,
106 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
107 FROM pay_state_rules SR,
108 hr_organization_information HOI,
109 pay_payroll_actions PPA,
110 pay_payroll_actions PPA1
111 WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
112 ('TRANSFER_PAYROLL_ACTION_ID')
113 AND ppa1.effective_date = ppa.effective_date
114 AND ppa1.report_qualifier = sr.state_code
115 AND HOI.organization_id =
116 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
117 AND SR.state_code =
118 pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
119 AND HOI.org_information_context = 'W2 Reporting Rules'
120 AND PPA.report_type = 'YREND'
121 AND HOI.ORGANIZATION_ID
122 = substr(PPA.legislative_parameters,
123 instr(PPA.legislative_parameters,
124 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
125 AND to_char(PPA.effective_date,'YYYY')
126 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
127 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
128
129 /* Context and Parameter Set in the cursor are
130 Context :
131 TAX_UNIT_ID - Submitter's Tax Unit ID
132 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
133 ASSIGNMENT_ID - Required for call to function - context not used
134 in the for Submitter
135 Date Earned - Always set to Effective date ie. in this case
136 for Mag tapes to 31-DEC-YYYY, in case of SQWL
137 this will be diffrent.
138 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
139
140 Parameters :
141 Transfer_HIGH_COUNT
142 TRANSFER_SCHOOL_DISTRICT
143 TRANSFER_COUNTY
144 TRANSFER_2678_FILER
145 TRANSFER_LOCALITY_CODE (Added for Local Mag Tape changes)
146 */
147 CURSOR fed_mmrf_submitter IS
148 SELECT 'TAX_UNIT_ID=C', HOI.organization_id,
149 'JURISDICTION_CODE=C', 'DUMMY_VALUE',
150 'TRANSFER_JD=P', 'DUMMY_VALUE',
151 'ASSIGNMENT_ID=C' , '-1',
152 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
153 'TRANSFER_HIGH_COUNT=P', '0',
154 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
155 'TRANSFER_COUNTY=P', '-1',
156 'TRANSFER_2678_FILER=P', HOI.org_information8,
157 'PAYROLL_ACTION_ID=C',ppa.payroll_action_id, -- payroll_action_id of YREND
158 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
159 FROM hr_organization_information HOI,
160 pay_payroll_actions PPA
161 WHERE HOI.organization_id =
162 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
163 AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
164 AND HOI.org_information_context = 'W2 Reporting Rules'
165 AND PPA.report_type = 'YREND'
166 AND HOI.ORGANIZATION_ID
167 = substr(PPA.legislative_parameters,
168 instr(PPA.legislative_parameters,
169 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
170 AND to_char(PPA.effective_date,'YYYY')
171 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
172 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
173
174 --
175 -- Sets up the tax unit context for each employer to be reported on NB. sets
176 -- up a parameter holding the tax unit identifier which can then be used by
177 -- subsequent cursors to restrict to employees within the employer.
178 --
179 --
180 /* Context and Parameter in the cursor are
181 Payroll_action_id table looks for value related to Year End pre-
182 processor while the pay_assignment_actions looks for
183 assignment actions of Mag. tapes
184 Context :
185 TAX_UNIT_ID - Submitter's Tax Unit ID
186 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
187 ASSIGNMENT_ID - Required for call to function - context not used
188 in the for Submitter
189 Date Earned - Always set to Effective date ie. in this case
190 for Mag tapes to 31-DEC-YYYY, in case of SQWL
191 this will be diffrent.
192 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
193
194 Parameters :
195 TAX_UNIT_ID - To be used in subsequent cusrsor
196 */
197 CURSOR mmrf_employer IS
198 SELECT DISTINCT
199 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
200 'TAX_UNIT_ID=C' , AA.tax_unit_id,
201 'TAX_UNIT_ID=P' , AA.tax_unit_id,
202 'TAX_UNIT_NAME=P' , hou.name,
203 'TRANSFER_EMP_CODE=P', 'R'
204 FROM hr_all_organization_units hou,
205 pay_payroll_actions ppa,
206 pay_assignment_actions AA
207 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
208 ('TRANSFER_PAYROLL_ACTION_ID')
209 AND ppa.report_type = 'YREND'
210 AND to_char(ppa.effective_date,'YYYY')
211 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
212 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
213 AND AA.tax_unit_id
214 = substr(ppa.legislative_parameters,
215 instr(ppa.legislative_parameters,
216 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
217 AND hou.organization_id = AA.tax_unit_id
218 order by hou.name;
219
220
221 CURSOR govt_mmrf_employer IS
222 SELECT DISTINCT
223 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
224 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
225 'TAX_UNIT_ID=C' , AA.tax_unit_id,
226 'TAX_UNIT_ID=P' , AA.tax_unit_id,
227 'TAX_UNIT_NAME=P', hou.name,
228 'TRANSFER_EMP_CODE=P' , 'R'
229 FROM hr_all_organization_units hou,
230 pay_payroll_actions ppa,
231 pay_assignment_actions AA
232 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
233 ('TRANSFER_PAYROLL_ACTION_ID')
234 AND ppa.report_type = 'YREND'
235 AND to_char(ppa.effective_date,'YYYY')
236 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
237 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
238 AND AA.tax_unit_id
239 = substr(ppa.legislative_parameters,
240 instr(ppa.legislative_parameters,
241 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
242 AND hou.organization_id = AA.tax_unit_id
243 UNION ALL
244 SELECT DISTINCT
245 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
246 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
247 'TAX_UNIT_ID=C' , AA.tax_unit_id,
248 'TAX_UNIT_ID=P' , AA.tax_unit_id,
249 'TAX_UNIT_NAME=P', hou.name,
250 'TRANSFER_EMP_CODE=P' , 'Q'
251 FROM hr_all_organization_units hou,
252 pay_payroll_actions ppa,
253 pay_assignment_actions AA
254 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
255 ('TRANSFER_PAYROLL_ACTION_ID')
256 AND ppa.report_type = 'YREND'
257 AND to_char(ppa.effective_date,'YYYY')
258 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
259 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
260 AND AA.tax_unit_id
261 = substr(ppa.legislative_parameters,
262 instr(ppa.legislative_parameters,
263 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
264 AND hou.organization_id = AA.tax_unit_id
265 order by 8;
266
267
268 --
272 -- assignment. This ensures that personal information ie. name etc... is
269 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
270 -- for an employee. The date_earned context is set to be the least of the
271 -- end of the period being reported and the maximum end date of the
273 -- current relative to the period being reported on.
274 --
275 CURSOR mmrf_employee IS
276 SELECT /*+ ORDERED */ 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
277 'ASSIGNMENT_ID=C', AA.assignment_id,
278 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
279 (PA.effective_date, AA.assignment_id)),
280 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
281 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
282 FROM pay_payroll_actions PA,
283 pay_assignment_actions AA,
284 pay_action_interlocks AI,
285 per_all_assignments_f SS,
286 per_all_people_f PE
287 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
288 ('TRANSFER_PAYROLL_ACTION_ID')
289 AND AA.payroll_action_id = PA.payroll_action_id
290 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
291 ('TAX_UNIT_ID')
292 AND AI.locking_action_id = AA.assignment_action_id
293 AND SS.assignment_id = AA.assignment_id
294 AND PE.person_id = SS.person_id
295 AND SS.effective_start_date =
296 (select max(paf2.effective_start_date)
297 from per_all_assignments_f paf2
298 where paf2.assignment_id = SS.assignment_id
299 and paf2.effective_start_date <= PA.effective_date
300 and paf2.assignment_type = 'E')
301 AND SS.effective_end_date >= PA.start_date
302 AND SS.assignment_type = 'E'
303 AND LEAST(SS.effective_end_date, PA.effective_date)
304 between PE.effective_start_date and PE.effective_end_date
305 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
306
307
308 CURSOR govt_mmrf_employee IS
309 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
310 'ASSIGNMENT_ID=C', AA.assignment_id,
311 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
312 (PA.effective_date, AA.assignment_id)),
313 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
314 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
315 FROM per_all_people_f PE,
316 per_all_assignments_f SS,
317 pay_action_interlocks AI,
318 pay_assignment_actions AA,
319 pay_assignment_actions paa,
320 pay_payroll_actions PA,
321 ff_archive_items arch,
322 ff_user_entities fue
323 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
324 ('TRANSFER_PAYROLL_ACTION_ID')
325 AND AA.payroll_action_id = PA.payroll_action_id
326 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
327 ('TAX_UNIT_ID')
328 AND AI.locking_action_id = AA.assignment_action_id
329 AND SS.assignment_id = AA.assignment_id
330 AND PE.person_id = SS.person_id
331 AND SS.effective_start_date =
332 (select max(paf2.effective_start_date)
333 from per_all_assignments_f paf2
334 where paf2.assignment_id = SS.assignment_id
335 and paf2.effective_start_date <= PA.effective_date
336 and paf2.assignment_type = 'E')
337 AND SS.effective_end_date >= PA.start_date
338 AND SS.assignment_type = 'E'
339 AND LEAST(SS.effective_end_date, PA.effective_date)
340 between PE.effective_start_date and PE.effective_end_date
341 AND paa.payroll_action_id = pay_magtape_generic.get_parameter_value
342 ('PAYROLL_ACTION_ID')
343 AND paa.assignment_id = AA.assignment_id
344 AND arch.context1 = paa.assignment_action_id
345 AND arch.user_entity_id = fue.user_entity_id
346 AND fue.user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE'
347 AND arch.value = pay_magtape_generic.get_parameter_value
348 ('TRANSFER_EMP_CODE')
349 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
350
351
352 /* This Cursor Added to fix Bug # 2736928
353 Additional Sort Parameter Person_ID added to the Employee Cursor
354 This change made to generate serial number */
355
356 CURSOR mmrf_pr_employee IS
357 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
358 'ASSIGNMENT_ID=C', AA.assignment_id,
359 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
360 (PA.effective_date, AA.assignment_id)),
361 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
362 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
363 FROM per_all_people_f PE,
364 per_all_assignments_f SS,
365 pay_action_interlocks AI,
366 pay_assignment_actions AA,
370 AND AA.payroll_action_id = PA.payroll_action_id
367 pay_payroll_actions PA
368 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
369 ('TRANSFER_PAYROLL_ACTION_ID')
371 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
372 ('TAX_UNIT_ID')
373 AND AI.locking_action_id = AA.assignment_action_id
374 AND SS.assignment_id = AA.assignment_id
375 AND PE.person_id = SS.person_id
376 AND SS.effective_start_date =
377 (select max(paf2.effective_start_date)
378 from per_all_assignments_f paf2
379 where paf2.assignment_id = SS.assignment_id
380 and paf2.effective_start_date <= PA.effective_date
381 and paf2.assignment_type = 'E')
382 AND SS.effective_end_date >= PA.start_date
383 AND SS.assignment_type = 'E'
384 AND LEAST(SS.effective_end_date, PA.effective_date)
385 between PE.effective_start_date and PE.effective_end_date
386 ORDER BY PE.last_name, PE.first_name, PE.middle_names,PE.person_id;
387
388
389 /* Indiana has multiple RS record. This RS record will Report Locality
390 Wages for Employee. We are currently interested in getting only
391 the JD code for all Indiana County.
392 */
393 CURSOR IN_LOCAL_MMRF_EMPLOYEE IS
394 SELECT 'JURISDICTION_CODE=C', rtrim(ltrim(faic.context)),
395 'TRANSFER_YE_JURISDICTION_CODE=P', ltrim(rtrim(faic.context))
396 from ff_archive_items fai,
397 ff_contexts fc, -- JD
398 ff_database_items fdi,
399 ff_archive_item_contexts faic, -- JD
400 pay_assignment_actions paa
401 where paa.assignment_action_id
402 = pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
403 and paa.assignment_action_id = fai.context1
404 and fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
405 and fdi.user_entity_id = fai.user_entity_id
406 and faic.archive_item_id = fai.archive_item_id
407 and fc.context_name = 'JURISDICTION_CODE'
408 and faic.context_id = fc.context_id
409 and value <> '0'
410 and faic.context like '15%'
411 order by faic.context;
412
413
414 /* Ohio Cursor */
415 CURSOR OH_LOCAL_MMRF_EMPLOYEE IS
416 SELECT 'JURISDICTION_CODE=C',ltrim(rtrim(faic.context)),
417 'TRANSFER_YE_JURISDICTION_CODE=P',ltrim(rtrim(faic.context))
418 from ff_archive_items fai,
419 ff_contexts fc, -- JD
420 ff_database_items fdi,
421 ff_archive_item_contexts faic, -- JD
422 pay_assignment_actions paa
423 where paa.assignment_action_id =
424 pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
425 and paa.assignment_action_id = fai.context1
426 and (fdi.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD' OR
427 fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD' OR
428 fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD')
429 and fdi.user_entity_id = fai.user_entity_id
430 and faic.archive_item_id = fai.archive_item_id
431 and fc.context_name = 'JURISDICTION_CODE'
432 and faic.context_id = fc.context_id
433 and value <> '0'
434 and faic.context like '36%'
435 order by faic.context ;
436
437
438 FUNCTION bal_db_item(p_db_item_name VARCHAR2)
439 RETURN NUMBER;
440
441 PROCEDURE get_report_parameters(
442 p_pactid IN NUMBER,
443 p_year_start IN OUT nocopy DATE,
444 p_year_end IN OUT nocopy DATE,
445 p_state_abbrev IN OUT nocopy VARCHAR2,
446 p_state_code IN OUT nocopy VARCHAR2,
447 p_report_type IN OUT nocopy VARCHAR2,
448 p_business_group_id IN OUT nocopy NUMBER);
449
450 FUNCTION get_balance_value(
451 p_balance_name VARCHAR2,
452 p_tax_unit_id NUMBER,
453 p_state_abbrev VARCHAR2,
454 p_assignment_id NUMBER,
455 p_effective_date DATE)
456 RETURN NUMBER;
457
458 FUNCTION preprocess_check(
459 p_pactid NUMBER,
460 p_year_start DATE,
461 p_year_end DATE,
462 p_business_group_id NUMBER,
463 p_state_abbrev VARCHAR2,
464 p_state_code VARCHAR2,
465 p_report_type VARCHAR2)
466 RETURN BOOLEAN;
467
468 PROCEDURE range_cursor(
469 p_pactid IN NUMBER,
470 p_sqlstr OUT nocopy VARCHAR2);
471
472 PROCEDURE create_assignment_act(
473 p_pactid IN NUMBER,
474 p_stperson IN NUMBER,
475 p_endperson IN NUMBER,
476 p_chunk IN NUMBER );
477
478 FUNCTION check_er_data(
479 p_pactid NUMBER,
480 p_ein_user_id NUMBER)
481 RETURN varchar2;
482
483
484 FUNCTION check_state_er_data(
485 p_pactid NUMBER,
486 p_tax_unit NUMBER,
487 p_jurisdictions varchar2)
488 RETURN varchar2;
489
490
491 PROCEDURE archive_eoy_data(
492 p_pactid IN NUMBER,
493 p_tax_id IN NUMBER );
494
495 PROCEDURE archive_state_eoy_data(
496 p_pactid IN NUMBER,
497 p_tax_id IN NUMBER,
501 p_payroll_action_id NUMBER,
498 p_state_code IN VARCHAR2);
499
500 FUNCTION check_state_data(
502 p_transfer_state varchar2)
503 RETURN varchar2;
504
505 FUNCTION get_report_category(p_business_group_id number,
506 p_effective_date date)
507 RETURN varchar2;
508
509 FUNCTION get_report_category_mt(p_business_group_id number,
510 p_effective_date date)
511 RETURN varchar2;
512
513 TYPE er_sum_rec IS RECORD
514 ( bal_name varchar2(60)
515 , value varchar2(20)
516 );
517 TYPE er_sum_table IS TABLE OF
518 er_sum_rec
519 INDEX BY BINARY_INTEGER;
520
521 FUNCTION get_w2_er_arch_bal(
522 w2_balance_name in varchar2,
523 w2_tax_unit_id in varchar2,
524 w2_jurisdiction_code in varchar2,
525 w2_jurisdiction_level in varchar2,
526 w2_year varchar2,
527 a1 OUT NOCOPY varchar2,
528 a2 OUT NOCOPY varchar2,
529 a3 OUT NOCOPY varchar2,
530 a4 OUT NOCOPY varchar2,
531 a5 OUT NOCOPY varchar2,
532 a6 OUT NOCOPY varchar2,
533 a7 OUT NOCOPY varchar2,
534 a8 OUT NOCOPY varchar2,
535 a9 OUT NOCOPY varchar2,
536 a10 OUT NOCOPY varchar2,
537 a11 OUT NOCOPY varchar2,
538 a12 OUT NOCOPY varchar2,
539 a13 OUT NOCOPY varchar2,
540 a14 OUT NOCOPY varchar2,
541 a15 OUT NOCOPY varchar2,
542 a16 OUT NOCOPY varchar2,
543 a17 OUT NOCOPY varchar2,
544 a18 OUT NOCOPY varchar2,
545 a19 OUT NOCOPY varchar2,
546 a20 OUT NOCOPY varchar2,
547 a21 OUT NOCOPY varchar2,
548 a22 OUT NOCOPY varchar2,
549 a23 OUT NOCOPY varchar2,
550 a24 OUT NOCOPY varchar2,
551 a25 OUT NOCOPY varchar2,
552 a26 OUT NOCOPY varchar2,
553 a27 OUT NOCOPY varchar2,
554 a28 OUT NOCOPY varchar2,
555 a29 OUT NOCOPY varchar2,
556 a30 OUT NOCOPY varchar2,
557 a31 OUT NOCOPY varchar2,
558 a32 OUT NOCOPY varchar2,
559 a33 OUT NOCOPY varchar2,
560 a34 OUT NOCOPY varchar2,
561 a35 OUT NOCOPY varchar2,
562 a36 OUT NOCOPY varchar2,
563 a37 OUT NOCOPY varchar2,
564 a38 OUT NOCOPY varchar2,
565 a39 OUT NOCOPY varchar2,
566 a40 OUT NOCOPY varchar2,
567 a41 OUT NOCOPY varchar2,
568 a42 OUT NOCOPY varchar2,
569 a43 OUT NOCOPY varchar2,
570 a44 OUT NOCOPY varchar2,
571 a45 OUT NOCOPY varchar2,
572 a46 OUT NOCOPY varchar2,
573 a47 OUT NOCOPY varchar2
574 )
575 RETURN varchar2;
576
577 FUNCTION set_application_error(p_state varchar2,
578 p_error varchar2,
579 p_assignment_action_id number
580 )
581 RETURN varchar2;
582
583 FUNCTION get_tax_unit_info (tax_unit_id IN NUMBER -- Context
584 , assignment_action_id IN NUMBER -- Context
585 ,p_tax_year IN NUMBER -- Parameter
586 ,p_federal_ein OUT NOCOPY VARCHAR2 -- Parameter
587 ,p_tax_jd_code OUT NOCOPY VARCHAR2 --Parameter
588 ,p_tax_unit_info1 OUT NOCOPY VARCHAR2 --Parameter
589 ,p_tax_unit_info2 OUT NOCOPY VARCHAR2 --Parameter
590 )
591 RETURN varchar2;
592
593 END pay_us_mmref_reporting;
[Home] [Help]