1 package pay_us_magw2_reporting AUTHID CURRENT_USER as
2 /* $Header: pyyepmw2.pkh 115.13 2002/12/03 03:01:44 ppanda ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_magw2_reporting
10
11 Purpose
12 The purpose of this package is to support the generation of magnetic tape W2
13 reports for US legilsative requirements incorporating magtape resilience
14 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 25-Jun-98 Vipin Mehta 40.0 Date created.
30
31 13-Aug-98 Vipin Mehta 40.1 changed ohstw2_supp and instw2_supp
32 to pick up assignment_action_id
33 from pay_action_interlocks
34 changed ohstw2_supp and
35 magw2_transmitter to set up an
36 additional parameter
37 (TRANSFER_SCHOOL_DISTRICT) to pass
38 the school district code to
39 W2_TIB4_SUPPLEMENTAL
40 11-Jan-99 Vipin Mehta 40.4 Added new parameters to
41 magw2_transmitter cursor to support
42 2678 Filing.
43 16-jan-99 Vipin Mehta 40.5 Modified magw2_reporting, instw2_supp and
44 ohstw2_supp to fix indiana and ohio
45
46 26-jan-99 VMehta 40.6 Modified w2_high_comp to change
47 24-aug-99 djoshi 40.7 Modified ohstw2 to change the table
48 pay_us_arch_mag_county_v to pay_us_acrh_county_sd_v
49 pay_us_arch_mag_city_v to pay_us_arch_mag_city_sd_v
50 and also changed the state abbv in query from IN
51 to OH. This is changed for bug 969567
52 24-oct-99 djoshi 40.8 changed the oh_in_employee cursor and
53 ohstw2_supp cursor to make them performant.
54 22-NOV-99 ahanda 115.5 Took the 110.6 of r11 and made the fnd_date changes.
55
56 24-nov-00 djoshi 115.8 Modified the file for Indiana 'S' record ref. bug
57 1230231.
58 10-sep-00 djoshi 115.9 Modified the file for Indiana 'S' record
59 for persormace
60
61 20-oct-00 djoshi 115.11 Reveted to 115.09
62 15-NOV-02 asasthan 115.12 Made file gscc compliant
63 02-DEC-02 ppanda 115.13 Made file gscc compliant for nocopy
64
65 ============================================================================*/
66
67 -- 'level_cnt' will allow the cursors to select function results,
68 -- whether it is a standard fuction such as to_char or a function
69 -- defined in a package (with the correct pragma restriction).
70
71 level_cnt NUMBER;
72
73 -- Used by Magnetic W2 (TIB4 format).
74 --
75 -- Sets up the tax unit context for the transmitter
76 --
77 --
78
79 CURSOR magw2_transmitter IS
80 SELECT 'TAX_UNIT_ID=C' , HOI.organization_id,
81 'JURISDICTION_CODE=C', SR.jurisdiction_code,
82 'TRANSFER_HIGH_COUNT=P', '0',
83 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
84 'TRANSFER_COUNTY=P', '-1',
85 'TRANSFER_2678_FILER=P', 'N',
86 'PAYROLL_ACTION_ID=C', PPA.payroll_action_id
87 FROM pay_state_rules SR,
88 hr_organization_information HOI,
89 pay_payroll_actions PPA
90 WHERE HOI.organization_id =
91 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
92 AND SR.state_code =
93 pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
94 AND HOI.org_information_context = 'W2 Reporting Rules'
95 AND PPA.report_type = 'YREND'
96 AND HOI.ORGANIZATION_ID = substr(PPA.legislative_parameters,instr(PPA.legislative_parameters,'TRANSFER_GRE=') + length('TRANSFER_GRE='))
97 AND to_char(PPA.effective_date,'YYYY') =
98 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
99 AND to_char(PPA.effective_date,'DD-MM') = '31-12'
100 UNION ALL
101 SELECT 'TAX_UNIT_ID=C', HOI.organization_id,
102 'JURISDICTION_CODE=C', 'DUMMY_VALUE',
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 FROM hr_organization_information HOI,
109 pay_payroll_actions PPA
110 WHERE HOI.organization_id =
111 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
112 AND pay_magtape_generic.get_parameter_value('TRANSFER_STATE') = 'FED'
113 AND HOI.org_information_context = 'W2 Reporting Rules'
114 AND PPA.report_type = 'YREND'
115 AND HOI.ORGANIZATION_ID = substr(PPA.legislative_parameters,instr(PPA.legislative_parameters,'TRANSFER_GRE=') + length('TRANSFER_GRE='))
116 AND to_char(PPA.effective_date,'YYYY') =
117 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
118 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
119
120 --
121 -- Used by Magnetic W2 (TIB4 format).
122 --
123 -- Sets up the tax unit context for each employer to be reported on NB. sets
124 -- up a parameter holding the tax unit identifier which can then be used by
125 -- subsequent cursors to restrict to employees within the employer.
126 --
127 --
128
129 CURSOR magw2_employer IS
130 SELECT DISTINCT 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
131 'TAX_UNIT_ID=C' , AA.tax_unit_id,
132 'TAX_UNIT_ID=P' , AA.tax_unit_id,
133 'TAX_UNIT_NAME=P' , fai.value
134 FROM ff_archive_item_contexts faic,
135 ff_archive_items fai,
136 ff_contexts ffc,
137 ff_database_items fdi,
138 pay_payroll_actions ppa,
139 pay_assignment_actions AA
140 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
141 ('TRANSFER_PAYROLL_ACTION_ID')
142 AND ppa.report_type = 'YREND'
143 AND to_char(ppa.effective_date,'YYYY') =
144 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
145 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
146 AND AA.tax_unit_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=') + length('TRANSFER_GRE='))
147 AND fdi.user_name = 'A_TAX_UNIT_NAME'
148 AND ffc.context_name = 'TAX_UNIT_ID'
149 AND ppa.payroll_action_id = fai.context1
150 AND fdi.user_entity_id = fai.user_entity_id
151 AND fai.archive_item_id = faic.archive_item_id
152 AND faic.context_id = ffc.context_id
153 AND faic.context = AA.tax_unit_id
154 order by fai.value;
155 --
156 -- Used by Magnetic W2 (TIB4 format).
157 --
158 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
159 -- for an employee. The date_earned context is set to be the least of the
160 -- end of the period being reported and the maximum end date of the
161 -- assignment. This ensures that personal information ie. name etc... is
162 -- current relative to the period being reported on.
163 --
164
165 CURSOR magw2_employee IS
166 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
167 'ASSIGNMENT_ID=C', AA.assignment_id,
168 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
169 (PA.effective_date, AA.assignment_id))
170 FROM per_people_f PE,
171 per_assignments_f SS,
172 pay_action_interlocks AI,
173 pay_assignment_actions AA,
174 pay_payroll_actions PA
175 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
176 ('TRANSFER_PAYROLL_ACTION_ID') AND
177 AA.payroll_action_id = PA.payroll_action_id AND
178 AA.tax_unit_id = pay_magtape_generic.get_parameter_value
179 ('TAX_UNIT_ID') AND
180 AI.locking_action_id = AA.assignment_action_id AND
181 SS.assignment_id = AA.assignment_id AND
182 PE.person_id = SS.person_id AND
183 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
184 SS.effective_start_date and SS.effective_end_date AND
185 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
186 PE.effective_start_date and PE.effective_end_date
187 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
188
189 --
190 -- Used by most states for State W2.
191 --
192 -- Sets up the tax unit context for each employer to be reported on NB. sets
193 -- up a parameter holding the tax unit identifier which can then be used by
194 -- subsequent cursors to restrict to employees within the employer. The
195 -- payroll action id context is used for the Archive DB Items.
196 -- The Date_Earned Context is used for balances with dimensions of
197 -- "GRE_JD_QTD" -- Notably Pennsylvania SUI_EE_GROSS. Added join to payroll
198 -- action table.
199
200 CURSOR st_magw2_employer IS
201 SELECT DISTINCT 'PAYROLL_ACTION_ID=C', PA.payroll_action_id,
202 'TAX_UNIT_ID=C' , AA.tax_unit_id,
203 'TAX_UNIT_ID=P' , AA.tax_unit_id,
204 'JURISDICTION_CODE=C', SR.jurisdiction_code,
205 'DATE_EARNED=C' , fnd_date.date_to_canonical(PA.effective_date),
206 'BUSINESS_GROUP_ID=C', PA.business_group_id
207 FROM pay_state_rules SR,
208 pay_payroll_actions PA,
209 pay_assignment_actions AA,
210 pay_payroll_actions PA1
211 WHERE PA1.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
212 AND AA.payroll_action_id = pay_magtape_generic.get_parameter_value
213 ('TRANSFER_PAYROLL_ACTION_ID')
214 AND AA.serial_number IS NULL
215 AND PA.report_type = 'YREND'
216 AND AA.tax_unit_id = substr(PA.legislative_parameters,instr(PA.legislative_parameters,'TRANSFER_GRE=') + length('TRANSFER_GRE='))
217 AND to_char(PA.effective_date,'YYYY') =
218 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
219 AND to_char(PA.effective_date,'DD-MM') = '31-12'
220 AND SR.state_code = ltrim(rtrim(PA1.report_qualifier));
221
222 --
223 -- Used by Magnetic W2 (TIB4 format).
224 --
225
226 CURSOR st_magw2_employee IS
227 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id,
228 'ASSIGNMENT_ID=C', AA.assignment_id,
229 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
230 (PA.effective_date, AA.assignment_id)),
231 'JURISDICTION_CODE=C', SR.jurisdiction_code
232 FROM per_people_f PE,
233 per_assignments_f SS,
234 pay_state_rules SR,
235 pay_action_interlocks AI,
236 pay_assignment_actions AA,
237 pay_payroll_actions PA
238 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
239 ('TRANSFER_PAYROLL_ACTION_ID') AND
240 AA.payroll_action_id = PA.payroll_action_id AND
241 AA.tax_unit_id = pay_magtape_generic.get_parameter_value
242 ('TAX_UNIT_ID') AND
243 AI.locking_action_id = AA.assignment_action_id AND
244 AA.serial_number IS NULL AND
245 SR.state_code = ltrim(rtrim(PA.report_qualifier)) AND
246 SS.assignment_id = AA.assignment_id AND
247 PE.person_id = SS.person_id AND
248 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
249 SS.effective_start_date AND SS.effective_end_date AND
250 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
251 PE.effective_start_date AND PE.effective_end_date
252 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
253
254 --
255
256 CURSOR oh_in_employee IS
257 SELECT 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id,
258 'ASSIGNMENT_ACTION_ID=P', AI.locking_action_id,
259 'ASSIGNMENT_ID=C', AA.assignment_id,
260 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
261 (PA.effective_date, AA.assignment_id)),
262 'YREND_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id,
263 'YREND_ASSIGNMENT_ID=P',AA.assignment_id,
264 'DATE_EARNED=P', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
265 (PA.effective_date, AA.assignment_id))
266 FROM per_people_f PE,
267 per_assignments_f SS,
268 pay_action_interlocks AI,
269 pay_assignment_actions AA,
270 pay_payroll_actions PA
271 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
272 ('TRANSFER_PAYROLL_ACTION_ID') AND
273 AA.payroll_action_id = PA.payroll_action_id AND
274 AA.tax_unit_id = pay_magtape_generic.get_parameter_value
275 ('TAX_UNIT_ID') AND
276 AI.locking_action_id = AA.assignment_action_id AND
277 AA.serial_number IS NULL AND
278 SS.assignment_id = AA.assignment_id AND
279 PE.person_id = SS.person_id AND
280 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
281 SS.effective_start_date AND SS.effective_end_date AND
282 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
283 PE.effective_start_date AND PE.effective_end_date
284 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
285
286
287
288 CURSOR instw2_supp
289 IS
290 SELECT 'TRANSFER_ASS_ACTION_ID=C', pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID'),
291 'ASSIGNMENT_ACTION_ID=C', pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID'),
292 'ASSIGNMENT_ID=C', pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ID'),
293 'DATE_EARNED=C', pay_magtape_generic.get_parameter_value('DATE_EARNED'),
294 'TRANSFER_COUNTY=P', substr(PLV.jurisdiction,4,3),
295 'JURISDICTION_CODE=C', PLV.jurisdiction
296 FROM pay_us_w2_locality_v plv
297 where plv.assignment_action_id = pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID')
298 and plv.TAX_UNIT_ID = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
299 and plv.tax_type = 'COUNTY'
300 and plv.state_abbrev = 'IN'
301 and W2_BOX_21 > 0
302
303 UNION ALL
304
305 SELECT 'TRANSFER_ASS_ACTION_ID=C',pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID'),
306 'ASSIGNMENT_ACTION_ID=C', pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID'),
307 'ASSIGNMENT_ID=C', pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ID'),
308 'DATE_EARNED=C',pay_magtape_generic.get_parameter_value('DATE_EARNED'),
309 'TRANSFER_COUNTY=P', '-1',
310 'JURISDICTION_CODE=C',psv.jurisdiction_code
311 FROM pay_us_arch_mag_state_v PSV
312 WHERE
313 PSV.assignment_action_id = pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID') AND
314 PSV.state_abbrev = 'IN' AND
315 ( NOT EXISTS
316 (
317 SELECT 'y'
318 FROM pay_us_arch_mag_county_v pcv
319 WHERE substr(pcv.jurisdiction_code,1,2) = substr(psv.jurisdiction_code,1,2) AND
320 pcv.assignment_action_id = pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID')
321 )
322 OR (0 = (SELECT NVL(sum(W2_BOX_21),0)
323 FROM pay_us_w2_locality_v plv
324 WHERE plv.assignment_action_id = pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID')
325 AND plv.TAX_UNIT_ID = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
326 AND plv.tax_type = 'COUNTY'
327 AND plv.state_abbrev = 'IN'
328 AND substr(plv.jurisdiction,1,2) = substr(PSV.jurisdiction_code,1,2)
329 )
330 )
331 );
332 --ORDER BY 12;
333
334
335 --
336
337 CURSOR ohstw2_supp IS
338 SELECT 'ASSIGNMENT_ACTION_ID=C', AA.assignment_action_id,
339 'ASSIGNMENT_ID=C', AA.assignment_id,
340 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
341 (PA.effective_date, AA.assignment_id)),
342 'TRANSFER_SCHOOL_DISTRICT=P',substr(PMV.jurisdiction_code,4,5),
343 'JURISDICTION_CODE=C', PMV.jurisdiction_code
344 FROM
345 pay_us_states PUS,
346 pay_us_arch_mag_county_sd_v PMV,
347 pay_payroll_actions PA,
348 pay_assignment_actions AA
349
350 WHERE
351 AA.assignment_action_id = pay_magtape_generic.get_parameter_value('YREND_ASSIGNMENT_ACTION_ID') AND
352 AA.payroll_action_id = PA.payroll_action_id AND
353 AA.tax_unit_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID') AND
354 PMV.assignment_action_id = AA.assignment_action_id AND
355 substr(PMV.jurisdiction_code,1,2) = pus.state_code AND
356 pus.state_abbrev = 'OH'
357 AND (EXISTS(
358 SELECT 'y'
359 FROM dual
360 where hr_us_w2_rep.get_w2_arch_bal(aa.assignment_action_id,'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD',
361 aa.tax_unit_id,pmv.jurisdiction_code,8) > 0))
362 UNION ALL
363 SELECT 'ASSIGNMENT_ACTION_ID=C', AA.assignment_action_id,
364 'ASSIGNMENT_ID=C', AA.assignment_id,
365 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
366 (PA.effective_date, AA.assignment_id)),
367 'TRANSFER_SCHOOL_DISTRICT=P', substr(PMC.jurisdiction_code,4,5),
368 'JURISDICTION_CODE=C', PMC.jurisdiction_code
369 FROM
370 pay_us_states PUS,
371 pay_us_arch_mag_city_sd_v PMC,
372 pay_assignment_actions AA,
373 pay_payroll_actions PA
374 WHERE
375
376 AA.assignment_action_id = pay_magtape_generic.get_parameter_value
377 ('YREND_ASSIGNMENT_ACTION_ID') AND
378 AA.payroll_action_id = PA.payroll_action_id AND
379 AA.tax_unit_id = pay_magtape_generic.get_parameter_value ('TAX_UNIT_ID') AND
380 PMC.assignment_action_id = AA.assignment_action_id AND
381 substr(PMC.jurisdiction_code,1,2) = PUS.state_code AND
382 PUS.state_abbrev = 'OH'
383 AND (EXISTS(
384 SELECT 'y'
385 FROM dual
386 where hr_us_w2_rep.get_w2_arch_bal(aa.assignment_action_id,'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD',
387 aa.tax_unit_id,pmc.jurisdiction_code,8) > 0))
388 UNION ALL
389 SELECT 'ASSIGNMENT_ACTION_ID=C', AA.assignment_action_id,
390 'ASSIGNMENT_ID=C', AA.assignment_id,
391 'DATE_EARNED=C', fnd_date.date_to_canonical(pay_magtape_generic.date_earned
392 (PA.effective_date, AA.assignment_id)),
393 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
394 'JURISDICTION_CODE=C', PSV.jurisdiction_code
395 FROM
396 pay_us_states PUS,
397 pay_us_arch_mag_state_v PSV,
398 pay_assignment_actions AA,
399 pay_payroll_actions PA
400 WHERE
401 AA.assignment_action_id = pay_magtape_generic.get_parameter_value
402 ('YREND_ASSIGNMENT_ACTION_ID') AND
403 AA.payroll_action_id = PA.payroll_action_id AND
404 AA.tax_unit_id = pay_magtape_generic.get_parameter_value ('TAX_UNIT_ID') AND
405 PSV.assignment_action_id = AA.assignment_action_id AND
406 substr(PSV.jurisdiction_code,1,2) = PUS.state_code AND
407 PUS.state_abbrev = 'OH'
408 AND (
409 ( 0 =
410 (SELECT nvl(sum(W2_BOX_21),0)
411 FROM pay_us_w2_locality_v plv
412 where plv.assignment_action_id = AA.assignment_action_id
413 and plv.TAX_UNIT_ID = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
414 and plv.tax_type = 'COUNTY SCHOOL'
415 and plv.state_abbrev = 'OH'
416 and substr(plv.jurisdiction,1,2) = substr(PSV.jurisdiction_code,1,2)))
417 or ( 0 =
418 (SELECT nvl(sum(W2_BOX_21),0)
419 FROM pay_us_w2_locality_v plv
420 where plv.assignment_action_id = AA.assignment_action_id
421 and plv.TAX_UNIT_ID = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
422 and plv.tax_type = 'CITY SCHOOL'
423 and plv.state_abbrev = 'OH'
424 and substr(plv.jurisdiction,1,2) = substr(PSV.jurisdiction_code,1,2)))
425 )
426 ORDER BY 10;
427
428 --
429 -- Cursor for handling Highly Compensated People
430 --
431
432 CURSOR w2_high_comp IS
433 SELECT 'TRANSFER_MESSAGE=P',
434 tuv.name || '-' || ppf.full_name||'('||ppf.employee_number||')'||fnd_global.local_chr(10)
435 FROM per_people_f ppf,
436 per_assignments_f paf,
437 pay_assignment_actions paa,
438 hr_tax_units_v tuv,
439 pay_payroll_actions ppa
440 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value
441 ('TRANSFER_PAYROLL_ACTION_ID') AND
442 paa.payroll_action_id = ppa.payroll_action_id AND
443 paa.tax_unit_id = tuv.tax_unit_id and
444 paa.assignment_id = paf.assignment_id AND
445 paf.person_id = ppf.person_id AND
446 ppa.effective_date BETWEEN
447 paf.effective_start_date AND paf.effective_end_date AND
448 ppa.effective_date BETWEEN
449 ppf.effective_start_date AND ppf.effective_end_date AND
450 paa.serial_number IS NOT NULL
451 order by tuv.name;
452
453 --
454
455 FUNCTION bal_db_item
456 (
457 p_db_item_name VARCHAR2
458 ) RETURN NUMBER;
459
460 PROCEDURE get_report_parameters
461 (
462 p_pactid IN NUMBER,
463 p_year_start IN OUT nocopy DATE,
464 p_year_end IN OUT nocopy DATE,
465 p_state_abbrev IN OUT nocopy VARCHAR2,
466 p_state_code IN OUT nocopy VARCHAR2,
467 p_report_type IN OUT nocopy VARCHAR2,
468 p_business_group_id IN OUT nocopy NUMBER
469 );
470
471 FUNCTION get_balance_value (
472 p_balance_name VARCHAR2,
473 p_tax_unit_id NUMBER,
474 p_state_abbrev VARCHAR2,
475 p_assignment_id NUMBER,
476 p_effective_date DATE
477 ) RETURN NUMBER;
478
479 FUNCTION preprocess_check
480 (
481 p_pactid NUMBER,
482 p_year_start DATE,
483 p_year_end DATE,
484 p_business_group_id NUMBER,
485 p_state_abbrev VARCHAR2,
486 p_state_code VARCHAR2,
487 p_report_type VARCHAR2
488 ) RETURN BOOLEAN;
489
490 PROCEDURE range_cursor (
491 p_pactid IN NUMBER,
492 p_sqlstr OUT nocopy VARCHAR2
493 );
494
495 PROCEDURE create_assignment_act(
496 p_pactid IN NUMBER,
497 p_stperson IN NUMBER,
498 p_endperson IN NUMBER,
499 p_chunk IN NUMBER );
500
501 END pay_us_magw2_reporting;
[Home] [Help]