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