1 PACKAGE pay_us_mmref_reporting_mt AS
2 /* $Header: payusmmrfrecmt.pkh 120.0.12000000.1 2007/02/23 10:16:39 sackumar noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_mmref_reporting_mt
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 01-aug-2006 ppanda 115.0 Enhanced Multi Threading architecture for Mag Media
32 report. This is the initial version created.
33 =============================================================================*/
34
35 -- 'level_cnt' will allow the cursors to select function results,
36 -- whether it is a standard fuction such as to_char or a function
37 -- defined in a package (with the correct pragma restriction).
38 level_cnt NUMBER;
39
40 --
41 -- Sets up the tax unit context for the Submitter
42 --
43 /* Transmitter for the State MMREF tape */
44 CURSOR state_mmrf_submitter IS
45 SELECT 'TAX_UNIT_ID=C' , HOI.organization_id,
46 'JURISDICTION_CODE=C', SR.jurisdiction_code,
47 'TRANSFER_JD=P', SR.jurisdiction_code,
48 'ASSIGNMENT_ID=C' , '-1',
49 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
50 'TRANSFER_HIGH_COUNT=P', '0',
51 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
52 'TRANSFER_COUNTY=P', '-1',
53 'TRANSFER_2678_FILER=P', HOI.org_information8,
54 'PAYROLL_ACTION_ID=C', PPA.payroll_action_id,
55 'BUSINESS_GROUP_ID=C',PPA.business_group_id,
56 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
57 FROM pay_state_rules SR,
58 hr_organization_information HOI,
59 pay_payroll_actions PPA,
60 pay_payroll_actions PPA1
61 WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
62 ('TRANSFER_PAYROLL_ACTION_ID')
63 AND ppa1.effective_date = ppa.effective_date
64 AND ppa1.report_qualifier = sr.state_code
65 AND HOI.organization_id =
66 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
67 AND SR.state_code =
68 pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
69 AND HOI.org_information_context = 'W2 Reporting Rules'
70 AND PPA.report_type = 'YREND'
71 AND HOI.ORGANIZATION_ID
72 = substr(PPA.legislative_parameters,
73 instr(PPA.legislative_parameters,
74 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
75 AND to_char(PPA.effective_date,'YYYY')
76 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
77 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
78
79 /* Context and Parameter Set in the cursor are
80 Context :
81 TAX_UNIT_ID - Submitter's Tax Unit ID
82 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
83 ASSIGNMENT_ID - Required for call to function - context not used
84 in the for Submitter
85 Date Earned - Always set to Effective date ie. in this case
86 for Mag tapes to 31-DEC-YYYY, in case of SQWL
87 this will be diffrent.
88 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
89
90 Parameters :
91 Transfer_HIGH_COUNT
92 TRANSFER_SCHOOL_DISTRICT
93 TRANSFER_COUNTY
94 TRANSFER_2678_FILER
95 TRANSFER_LOCALITY_CODE (Added for Local Mag Tape changes)
96 */
97 CURSOR fed_mmrf_submitter IS
98 SELECT 'TAX_UNIT_ID=C', HOI.organization_id,
99 'JURISDICTION_CODE=C', 'DUMMY_VALUE',
100 'TRANSFER_JD=P', 'DUMMY_VALUE',
101 'ASSIGNMENT_ID=C' , '-1',
102 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
103 'TRANSFER_HIGH_COUNT=P', '0',
104 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
105 'TRANSFER_COUNTY=P', '-1',
106 'TRANSFER_2678_FILER=P', HOI.org_information8,
107 'PAYROLL_ACTION_ID=C',ppa.payroll_action_id, -- payroll_action_id of YREND
108 'TRANSFER_LOCALITY_CODE=P', 'DUMMY'
109 FROM hr_organization_information HOI,
110 pay_payroll_actions PPA
111 WHERE HOI.organization_id =
112 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
113 AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
114 AND HOI.org_information_context = 'W2 Reporting Rules'
115 AND PPA.report_type = 'YREND'
116 AND HOI.ORGANIZATION_ID
117 = substr(PPA.legislative_parameters,
118 instr(PPA.legislative_parameters,
119 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
120 AND to_char(PPA.effective_date,'YYYY')
121 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
122 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
123
124 --
125 -- Sets up the tax unit context for each employer to be reported on NB. sets
126 -- up a parameter holding the tax unit identifier which can then be used by
127 -- subsequent cursors to restrict to employees within the employer.
128 --
129 --
130 /* Context and Parameter in the cursor are
131 Payroll_action_id table looks for value related to Year End pre-
132 processor while the pay_assignment_actions looks for
133 assignment actions of Mag. tapes
134 Context :
135 TAX_UNIT_ID - Submitter's Tax Unit ID
136 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
137 ASSIGNMENT_ID - Required for call to function - context not used
138 in the for Submitter
139 Date Earned - Always set to Effective date ie. in this case
140 for Mag tapes to 31-DEC-YYYY, in case of SQWL
141 this will be diffrent.
142 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
143
144 Parameters :
145 TAX_UNIT_ID - To be used in subsequent cusrsor
146 */
147 CURSOR mmrf_employer IS
148 SELECT DISTINCT
149 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
150 'TAX_UNIT_ID=C' , AA.tax_unit_id,
151 'TAX_UNIT_ID=P' , AA.tax_unit_id,
152 'TAX_UNIT_NAME=P' , hou.name,
153 'TRANSFER_EMP_CODE=P', 'R'
154 FROM hr_all_organization_units hou,
155 pay_payroll_actions ppa,
156 pay_assignment_actions AA
157 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
158 ('TRANSFER_PAYROLL_ACTION_ID')
159 AND ppa.report_type = 'YREND'
160 AND to_char(ppa.effective_date,'YYYY')
161 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
162 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
163 AND AA.tax_unit_id
164 = substr(ppa.legislative_parameters,
165 instr(ppa.legislative_parameters,
166 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
167 AND hou.organization_id = AA.tax_unit_id
168 order by hou.name;
169
170 CURSOR mmrf_employer_multi IS
171 SELECT DISTINCT
172 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
173 'TAX_UNIT_ID=C' , AA.tax_unit_id,
174 'TAX_UNIT_ID=P' , AA.tax_unit_id,
175 'TAX_UNIT_NAME=P' , hou.name,
176 'TRANSFER_EMP_CODE=P', 'R',
177 'TRANSFER_TAX_UNIT_ID=P', AA.tax_unit_id
178 FROM hr_all_organization_units hou,
179 pay_payroll_actions ppa,
180 pay_assignment_actions AA
181 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
182 ('TRANSFER_PAYROLL_ACTION_ID')
183 AND ppa.report_type = 'YREND'
184 AND to_char(ppa.effective_date,'YYYY')
185 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
186 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
187 AND AA.tax_unit_id
188 = substr(ppa.legislative_parameters,
189 instr(ppa.legislative_parameters,
190 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
191 AND hou.organization_id = AA.tax_unit_id
192 order by hou.name;
193
194
195 CURSOR govt_mmrf_employer IS
196 SELECT DISTINCT
197 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
198 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
199 'TAX_UNIT_ID=C' , AA.tax_unit_id,
200 'TAX_UNIT_ID=P' , AA.tax_unit_id,
201 'TAX_UNIT_NAME=P', hou.name,
202 'TRANSFER_EMP_CODE=P' , 'R'
203 FROM hr_all_organization_units hou,
204 pay_payroll_actions ppa,
205 pay_assignment_actions AA
206 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
207 ('TRANSFER_PAYROLL_ACTION_ID')
208 AND ppa.report_type = 'YREND'
209 AND to_char(ppa.effective_date,'YYYY')
210 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
211 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
212 AND AA.tax_unit_id
213 = substr(ppa.legislative_parameters,
214 instr(ppa.legislative_parameters,
215 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
216 AND hou.organization_id = AA.tax_unit_id
217 UNION ALL
218 SELECT DISTINCT
219 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
220 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
221 'TAX_UNIT_ID=C' , AA.tax_unit_id,
222 'TAX_UNIT_ID=P' , AA.tax_unit_id,
223 'TAX_UNIT_NAME=P', hou.name,
224 'TRANSFER_EMP_CODE=P' , 'Q'
225 FROM hr_all_organization_units hou,
226 pay_payroll_actions ppa,
227 pay_assignment_actions AA
228 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
229 ('TRANSFER_PAYROLL_ACTION_ID')
230 AND ppa.report_type = 'YREND'
231 AND to_char(ppa.effective_date,'YYYY')
232 = pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
233 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
234 AND AA.tax_unit_id
235 = substr(ppa.legislative_parameters,
236 instr(ppa.legislative_parameters,
237 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
238 AND hou.organization_id = AA.tax_unit_id
239 order by 8;
240
241
242 --
243 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
244 -- for an employee. The date_earned context is set to be the least of the
245 -- end of the period being reported and the maximum end date of the
246 -- assignment. This ensures that personal information ie. name etc... is
247 -- current relative to the period being reported on.
248 --
249
250 CURSOR mmrf_employee_main IS
251 /* SELECT 'TRANSFER_ACT_ID=P',
252 pay_magtape_generic.get_parameter_value(
253 'TRANSFER_ACT_ID')
254 FROM DUAL;
255 */
256 SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
257 FROM pay_assignment_actions paa
258 WHERE paa.payroll_action_id =
259 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
260 AND paa.tax_unit_id =
261 pay_magtape_generic.get_parameter_value('TRANSFER_TAX_UNIT_ID');
262
263
264
265 CURSOR mmrf_employee IS
266 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
267 'ASSIGNMENT_ID=C', AA.assignment_id,
268 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
269 (PA.effective_date, AA.assignment_id)),
270 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
271 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
272 FROM per_all_people_f PE,
273 per_all_assignments_f SS,
274 pay_action_interlocks AI,
275 pay_assignment_actions AA,
276 pay_payroll_actions PA
277 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
278 ('TRANSFER_PAYROLL_ACTION_ID')
279 AND AA.payroll_action_id = PA.payroll_action_id
280 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
281 ('TAX_UNIT_ID')
282 AND AI.locking_action_id = AA.assignment_action_id
283 AND SS.assignment_id = AA.assignment_id
284 AND PE.person_id = SS.person_id
285 AND SS.effective_start_date = (select max(paf2.effective_start_date)
286 from per_all_assignments_f paf2
287 where paf2.assignment_id = SS.assignment_id
288 and paf2.effective_start_date <= PA.effective_date
289 and paf2.assignment_type = 'E')
290 AND SS.effective_end_date >= PA.start_date
291 AND SS.assignment_type = 'E'
292 AND LEAST(SS.effective_end_date, PA.effective_date)
293 between PE.effective_start_date and PE.effective_end_date
294 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
295
296
297 CURSOR govt_mmrf_employee IS
298 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
299 'ASSIGNMENT_ID=C', AA.assignment_id,
300 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
301 (PA.effective_date, AA.assignment_id)),
302 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
303 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
304 FROM per_all_people_f PE,
305 per_all_assignments_f SS,
306 pay_action_interlocks AI,
307 pay_assignment_actions AA,
308 pay_assignment_actions paa,
309 pay_payroll_actions PA,
310 ff_archive_items arch,
311 ff_user_entities fue
312 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
313 ('TRANSFER_PAYROLL_ACTION_ID')
314 AND AA.payroll_action_id = PA.payroll_action_id
315 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
316 ('TAX_UNIT_ID')
317 AND AI.locking_action_id = AA.assignment_action_id
318 AND SS.assignment_id = AA.assignment_id
319 AND PE.person_id = SS.person_id
320 AND SS.effective_start_date =
321 (select max(paf2.effective_start_date)
322 from per_all_assignments_f paf2
323 where paf2.assignment_id = SS.assignment_id
324 and paf2.effective_start_date <= PA.effective_date
325 and paf2.assignment_type = 'E')
326 AND SS.effective_end_date >= PA.start_date
327 AND SS.assignment_type = 'E'
328 AND LEAST(SS.effective_end_date, PA.effective_date)
329 between PE.effective_start_date and PE.effective_end_date
330 AND paa.payroll_action_id = pay_magtape_generic.get_parameter_value
331 ('PAYROLL_ACTION_ID')
332 AND paa.assignment_id = AA.assignment_id
333 AND arch.context1 = paa.assignment_action_id
334 AND arch.user_entity_id = fue.user_entity_id
335 AND fue.user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE'
336 AND arch.value = pay_magtape_generic.get_parameter_value
337 ('TRANSFER_EMP_CODE')
338 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
339
340
341 /* This Cursor Added to fix Bug # 2736928
342 Additional Sort Parameter Person_ID added to the Employee Cursor
343 This change made to generate serial number */
344
345 CURSOR mmrf_pr_employee IS
346 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
347 'ASSIGNMENT_ID=C', AA.assignment_id,
348 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
349 (PA.effective_date, AA.assignment_id)),
350 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
351 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
352 FROM per_all_people_f PE,
353 per_all_assignments_f SS,
354 pay_action_interlocks AI,
355 pay_assignment_actions AA,
356 pay_payroll_actions PA
357 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
358 ('TRANSFER_PAYROLL_ACTION_ID')
359 AND AA.payroll_action_id = PA.payroll_action_id
360 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
361 ('TAX_UNIT_ID')
362 AND AI.locking_action_id = AA.assignment_action_id
363 AND SS.assignment_id = AA.assignment_id
364 AND PE.person_id = SS.person_id
365 AND SS.effective_start_date =
366 (select max(paf2.effective_start_date)
367 from per_all_assignments_f paf2
368 where paf2.assignment_id = SS.assignment_id
369 and paf2.effective_start_date <= PA.effective_date
370 and paf2.assignment_type = 'E')
371 AND SS.effective_end_date >= PA.start_date
372 AND SS.assignment_type = 'E'
373 AND LEAST(SS.effective_end_date, PA.effective_date)
374 between PE.effective_start_date and PE.effective_end_date
375 ORDER BY PE.last_name, PE.first_name, PE.middle_names,PE.person_id;
376
377
378 /* Indiana has multiple RS record. This RS record will Report Locality
379 Wages for Employee. We are currently interested in getting only
380 the JD code for all Indiana County.
381 */
382 CURSOR IN_LOCAL_MMRF_EMPLOYEE IS
383 SELECT 'JURISDICTION_CODE=C', rtrim(ltrim(faic.context)),
384 'TRANSFER_YE_JURISDICTION_CODE=P', ltrim(rtrim(faic.context))
385 from ff_archive_items fai,
386 ff_contexts fc, -- JD
387 ff_database_items fdi,
388 ff_archive_item_contexts faic, -- JD
389 pay_assignment_actions paa
390 where paa.assignment_action_id
391 = pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
392 and paa.assignment_action_id = fai.context1
393 and fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
394 and fdi.user_entity_id = fai.user_entity_id
395 and faic.archive_item_id = fai.archive_item_id
396 and fc.context_name = 'JURISDICTION_CODE'
397 and faic.context_id = fc.context_id
398 and value <> '0'
399 and faic.context like '15%'
400 order by faic.context;
401
402
403 /* Ohio Cursor */
404 CURSOR OH_LOCAL_MMRF_EMPLOYEE IS
405 SELECT 'JURISDICTION_CODE=C',ltrim(rtrim(faic.context)),
406 'TRANSFER_YE_JURISDICTION_CODE=P',ltrim(rtrim(faic.context))
407 from ff_archive_items fai,
408 ff_contexts fc, -- JD
409 ff_database_items fdi,
410 ff_archive_item_contexts faic, -- JD
411 pay_assignment_actions paa
412 where paa.assignment_action_id =
413 pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
414 and paa.assignment_action_id = fai.context1
415 and (fdi.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD' OR
416 fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD' OR
417 fdi.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD')
418 and fdi.user_entity_id = fai.user_entity_id
419 and faic.archive_item_id = fai.archive_item_id
420 and fc.context_name = 'JURISDICTION_CODE'
421 and faic.context_id = fc.context_id
422 and value <> '0'
423 and faic.context like '36%'
424 order by faic.context ;
425
426
427
428 CURSOR mmrf_employee_act IS
429 SELECT 'TRANSFER_ACT_ID=P',
430 pay_magtape_generic.get_parameter_value(
431 'TRANSFER_ACT_ID'),
432 'PAYROLL_ACTION_ID=C', pay_magtape_generic.get_parameter_value
433 ('TRANSFER_PAYROLL_ACTION_ID'),
434 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
435 'ASSIGNMENT_ID=C', AA.assignment_id,
436 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
437 (PA.effective_date, AA.assignment_id)),
438 'JURISDICTION_CODE=C', pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
439 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id,
440 'TAX_UNIT_ID=C',AA.TAX_UNIT_ID
441 FROM per_all_people_f PE,
442 per_all_assignments_f SS,
443 pay_action_interlocks AI,
444 pay_assignment_actions AA,
445 pay_payroll_actions PA
446 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
447 ('TRANSFER_PAYROLL_ACTION_ID')
448 AND aa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
449 AND AA.payroll_action_id = PA.payroll_action_id
450 AND AI.locking_action_id = AA.assignment_action_id
451 AND SS.assignment_id = AA.assignment_id
452 AND PE.person_id = SS.person_id
453 AND SS.effective_start_date = (select max(paf2.effective_start_date)
454 from per_all_assignments_f paf2
455 where paf2.assignment_id = SS.assignment_id
456 and paf2.effective_start_date <= PA.effective_date
457 and paf2.assignment_type = 'E')
458 AND SS.effective_end_date >= PA.start_date
459 AND SS.assignment_type = 'E'
460 AND LEAST(SS.effective_end_date, PA.effective_date)
461 between PE.effective_start_date and PE.effective_end_date
462 ORDER BY aa.tax_unit_id, PE.last_name, PE.first_name, PE.middle_names;
463
464
465
466
467 FUNCTION bal_db_item(p_db_item_name VARCHAR2)
468 RETURN NUMBER;
469
470 PROCEDURE get_report_parameters(
471 p_pactid IN NUMBER,
472 p_year_start IN OUT nocopy DATE,
473 p_year_end IN OUT nocopy DATE,
474 p_state_abbrev IN OUT nocopy VARCHAR2,
475 p_state_code IN OUT nocopy VARCHAR2,
476 p_report_type IN OUT nocopy VARCHAR2,
477 p_business_group_id IN OUT nocopy NUMBER);
478
479 FUNCTION get_balance_value(
480 p_balance_name VARCHAR2,
481 p_tax_unit_id NUMBER,
482 p_state_abbrev VARCHAR2,
483 p_assignment_id NUMBER,
484 p_effective_date DATE)
485 RETURN NUMBER;
486
487 FUNCTION preprocess_check(
488 p_pactid NUMBER,
489 p_year_start DATE,
490 p_year_end DATE,
491 p_business_group_id NUMBER,
492 p_state_abbrev VARCHAR2,
493 p_state_code VARCHAR2,
494 p_report_type VARCHAR2)
495 RETURN BOOLEAN;
496
500
497 PROCEDURE range_cursor(
498 p_pactid IN NUMBER,
499 p_sqlstr OUT nocopy VARCHAR2);
501 PROCEDURE create_assignment_act(
502 p_pactid IN NUMBER,
503 p_stperson IN NUMBER,
504 p_endperson IN NUMBER,
505 p_chunk IN NUMBER );
506
507 FUNCTION check_er_data(
508 p_pactid NUMBER,
509 p_ein_user_id NUMBER)
510 RETURN varchar2;
511
512
513 FUNCTION check_state_er_data(
514 p_pactid NUMBER,
515 p_tax_unit NUMBER,
516 p_jurisdictions varchar2)
517 RETURN varchar2;
518
519
520 PROCEDURE archive_eoy_data(
521 p_pactid IN NUMBER,
522 p_tax_id IN NUMBER );
523
524 PROCEDURE archive_state_eoy_data(
525 p_pactid IN NUMBER,
526 p_tax_id IN NUMBER,
527 p_state_code IN VARCHAR2);
528
529 FUNCTION check_state_data(
530 p_payroll_action_id NUMBER,
531 p_transfer_state varchar2)
532 RETURN varchar2;
533
534 FUNCTION get_report_category(p_business_group_id number,
535 p_effective_date date)
536 RETURN varchar2;
537
538 FUNCTION set_application_error(p_state varchar2,
539 p_error varchar2
540 )
541 RETURN varchar2;
542
543 FUNCTION get_tax_unit_info (tax_unit_id IN NUMBER -- Context
544 ,p_tax_year IN NUMBER -- Parameter
545 ,p_federal_ein OUT NOCOPY VARCHAR2 -- Parameter
546 )
547 RETURN varchar2;
548 END pay_us_mmref_reporting_mt;
[Home] [Help]