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