[Home] [Help]
PACKAGE BODY: APPS.PYW2DATA
Source
1 package body pyw2data as
2 /* $Header: pyw2data.pkb 115.10 99/07/17 06:49:13 porting ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1995 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pyw2data
10 Purpose
11 Sets up the data to provide W2 and State Quarterly Wage Reporting.
12 Notes
13 This data is US specific.
14 History
15 29-Mar-95 J.S.Hobbs 40.0 Date created.
16 14-Jun-95 allee 40.0 arcs'd in
17 15-Jun-95 allee commented out all state formats
18 16-Jun-95 allee Changed hardcoded bg_id = 63 -> 3
19 27-Jun-95 allee commented out the TIB4_Supplement
20 Changed a sequence to 2
21 14-Aug-95 allee Changed the call to lookup formula.
22 Now BG_ID = NULL and leg_code = 'US'
23 14-Aug-95 allee Changed Basic Information -> Binfo
24 Change lookup formula's cursor to
25 restrict by leg_code and not by
26 bg_id
27 15-Aug-95 allee Changed the overflow flag from
28 'R' -> 'N' (Note: This might have
29 fixed the problem concerning picking
30 up employees)
31
32 29-Sep-95 allee Changed the overflow flag back to 'R'
33
34 30-Sep-95 allee Added TIB4_DUMMY to ensure that
35 TIB4_EMPLOYEE gets called
36
37 01-Dec-95 allee Added information to handle the
38 ICESA and SSA_SQWL formats
39
40 21-Dec-95 allee Changed the Employer and Employee
41 Level Cursors.
42
43 16-Jan-96 allee Add the other formats.
44
45 23-Jan-96 allee Removed User errors. Commented
46 out all the formats dependent
47 SQWL formulas, so that we can deliver
48 a kosher Prod10
49 26-Jan-96 rquance Added a / and exit for the p10 patch
50 01-FEB-96 ramurthy Added the formula ICESA_TOTAL2 to
51 the ICESA
52 27-Feb-96 allee Added formats: IASQWL, RISQWL,
53 HISQWL. Rhode Island and
54 Hawaii used to be an SSA STATE.
55 Note: I currently set the frequency
56 to one for testing purposes.
57 29-Feb-96 allee Added Washington and Mississippi.
58 26-MAY-98 nbristow Added report_category.
59
60 15-JUL-98 vmehta Moved 'KY' from SSA format to ICESA
61 for SQWL
62 22-JUL-98 vmehta Added code to populate 'magtape resilience'
63 data in pay_report_format_mappings_f
64 Folded in changes from patch 702840.
65 New formulae for Washington SQWL
66 New Format definition for Connecticut
67 SQWL (bug 704503)
68 08-aug-1998 vmehta Added code to make MagW2 'magtape resilience'
69 compliant.
70 11-aug-1998 vmehta Changed block definitions for Ohio and
71 Indiana W2. The cursors oh_in_employee and
72 ohstw2_supp and instw2_supp fetch 8 columns each
73 as opposed to 10.
74 changed block defn. for ohstw2_supp to fetch 10
75 columns due to the added parameter
76 TRANSFER_SCHOOL_DISTRICT
77 Changed block definition for magw2_transmitter to
78 fetch 8 columns instead of 6
79 11-sep-1998 vmehta Moved Kansas(KS) to ICESA format from SSA.
80 06-oct-1998 vmehta ARCS'd in 40.41 as 40.43
81 *********************************************
82 40.42 is for release 10.6 only
83 *******************************************
84 06-oct-1998 vmehta Added the following new formats as part of SQWL
85 Q3 1998 patch.
86 South Dakota (Diskette)
87 Maryland (Diskette)
88 Illinois (Diskette)
89 Oregon (FTP)
90 North Dakota (FTP)
91 Louisiana (Quality Jobs Program)
92 11-Jan-1999 vmehta 40.45 Changed the number of columns fetched for
93 magw2_transmitter to support 2678 filing.
94 16-Jan-1999 vmehta 40.46 Changed the number of columns fetched for
95 (110.11)w2_transmitter and instw2_supp
96
97 ***************************************************************************
98 **************************************************************************
99
100 18-Feb-1999 VMehta 110.12 Removed the SQWL part from this file and moved it
101 to pysqdata.pkb because the program had become
102 too large for startup database.
103 ****************************************************************************
104 ****************************************************************************
105 ============================================================================*/
106 --
107 -- Date constants representing the start and end of time.sqlk
108 --
109 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
110 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
111 --
112 -----------------------------------------------------------------------------
113 -- Name
114 -- lookup_formula
115 -- Purpose
116 -- Given a formula name it returns its id.
117 -- Arguments
118 -- Notes
119 -----------------------------------------------------------------------------
120 --
121 function lookup_formula
122 (
123 p_session_date date,
124 p_business_group_id number,
125 p_legislation_code varchar2,
126 p_formula_name varchar2
127 ) return number is
128 --
129 -- Local variable to hold formula_id from cursor fetch.
130 --
131 formula_id number;
132 --
133 -- Get the formula id for the specified formula.
134 --
135 cursor csr_formula is
136 select FM.formula_id
137 from ff_formulas_f FM
138 where FM.legislation_code = p_legislation_code
139 and FM.formula_name = upper(p_formula_name)
140 and p_session_date between FM.effective_start_date
141 and FM.effective_end_date;
142 --
143 begin
144 --
145 open csr_formula;
146 fetch csr_formula into formula_id;
147 if csr_formula%notfound then
148 close csr_formula;
149 raise NO_DATA_FOUND;
150 end if;
151 close csr_formula;
152 --
153 return (formula_id);
154 --
155 end lookup_formula;
156 --
157 -----------------------------------------------------------------------------
158 -- Name
159 -- setup
160 -- Purpose
161 -- Sets up data to provide W2 and State Quarterly Wage reporting.
162 -- Arguments
163 -- None
164 -- Notes
165 -----------------------------------------------------------------------------
166 --
167 procedure setup is
168 --
169 -- Holds the name of the report format.
170 --
171 L_REPORT_FORMAT varchar2(30);
172 --
173 -- Holds the ID's of created blocks.
174 --
175 L_BLOCK1 number;
176 L_BLOCK2 number;
177 L_BLOCK3 number;
178 L_BLOCK4 number;
179 L_BLOCK5 number;
180 --
181 -- Holds the ID of a formula.
182 --
183 L_FORMULA_ID number;
184 --
185 begin
186 --
187 --------------------------------------------------------------------------
188 -- Federal W2 format -
189 --------------------------------------------------------------------------
190 --
191 -- Set up report format to be maintained.
192 --
193 L_REPORT_FORMAT := 'TIB4';
194 --
195 -- Clear down current format definition and its mappings.
196 --
197 delete from pay_magnetic_records MGR
198 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
199 from pay_magnetic_blocks MGB,
200 pay_report_format_mappings_f RFM
201 where MGB.report_format = RFM.report_format
202 AND RFM.report_format = L_REPORT_FORMAT
203 AND RFM.report_category = 'RT');
204 --
205 delete from pay_magnetic_blocks MGB
206 where MGB.report_format in (
207 SELECT report_format
208 FROM pay_report_format_mappings_f
209 WHERE report_format = L_REPORT_FORMAT
210 AND report_category = 'RT');
211 --
212 delete from pay_report_format_mappings_f RFM
213 where RFM.report_format = L_REPORT_FORMAT
214 and RFM.report_category = 'RT';
215 --
216 -- Report to format definitions.
217 --
218 insert into pay_report_format_mappings_f
219 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
220 RANGE_CODE,
221 ASSIGNMENT_ACTION_CODE,
222 INITIALIZATION_CODE,
223 ARCHIVE_CODE,
224 MAGNETIC_CODE,
225 REPORT_FORMAT, EFFECTIVE_START_DATE,
226 EFFECTIVE_END_DATE )
227 values ('W2' , 'FED', 'RT',
228 'pay_us_magw2_reporting.range_cursor',
229 'pay_us_magw2_reporting.create_assignment_act',
230 NULL,
231 NULL,
232 'pay_magtape_generic.new_formula',
233 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
234 --
235 -- Block definitions.
236 --
237 pay_mgb_ins.ins
238 (p_magnetic_block_id => L_BLOCK1
239 ,p_block_name => 'TRANSMITTER'
240 ,p_main_block_flag => 'Y'
241 ,p_report_format => L_REPORT_FORMAT
242 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
243 ,p_no_column_returned => 12
244 ,p_validate => false);
245 --
246 pay_mgb_ins.ins
247 (p_magnetic_block_id => L_BLOCK2
248 ,p_block_name => 'EMPLOYER'
249 ,p_main_block_flag => 'N'
250 ,p_report_format => L_REPORT_FORMAT
251 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_employer'
252 ,p_no_column_returned => 6
253 ,p_validate => false);
254 --
255 pay_mgb_ins.ins
256 (p_magnetic_block_id => L_BLOCK3
257 ,p_block_name => 'EMPLOYEE'
258 ,p_main_block_flag => 'N'
259 ,p_report_format => L_REPORT_FORMAT
260 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_employee'
261 ,p_no_column_returned => 6
262 ,p_validate => false);
263 --
264 -- Record definitions.
265 --
266
267 pay_mgr_ins.ins
268 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
269 'TIB4_TRANSMITTER')
270 ,p_magnetic_block_id => L_BLOCK1
271 ,p_next_block_id => NULL
272 ,p_last_run_executed_mode => 'N'
273 ,p_overflow_mode => 'N'
274 ,p_sequence => 1
275 ,p_frequency => NULL
276 ,p_validate => false);
277
278 --
279 pay_mgr_ins.ins
280 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
281 'TIB4_BINFO')
282 ,p_magnetic_block_id => L_BLOCK1
283 ,p_next_block_id => L_BLOCK2
284 ,p_last_run_executed_mode => 'N'
285 ,p_overflow_mode => 'N'
286 ,p_sequence => 2
287 ,p_frequency => NULL
288 ,p_validate => false);
289 --
290 pay_mgr_ins.ins
291 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
292 'TIB4_FINAL')
293 ,p_magnetic_block_id => L_BLOCK1
294 ,p_next_block_id => NULL
295 ,p_last_run_executed_mode => 'N'
296 ,p_overflow_mode => 'N'
297 ,p_sequence => 3
298 ,p_frequency => NULL
299 ,p_validate => false);
300 --
301 pay_mgr_ins.ins
302 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
303 'TIB4_EMPLOYER')
304 ,p_magnetic_block_id => L_BLOCK2
305 ,p_next_block_id => L_BLOCK3
306 ,p_last_run_executed_mode => 'N'
307 ,p_overflow_mode => 'N'
308 ,p_sequence => 1
309 ,p_frequency => NULL
310 ,p_validate => false);
311 --
312 pay_mgr_ins.ins
313 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
314 'TIB4_INTERMEDIATE_TOTAL')
315 ,p_magnetic_block_id => L_BLOCK2
316 ,p_next_block_id => NULL
317 ,p_last_run_executed_mode => 'N'
318 ,p_overflow_mode => 'N'
319 ,p_sequence => 2
320 ,p_frequency => NULL
321 ,p_validate => false);
322 --
323 pay_mgr_ins.ins
324 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
325 'TIB4_TOTAL')
326 ,p_magnetic_block_id => L_BLOCK2
327 ,p_next_block_id => NULL
328 ,p_last_run_executed_mode => 'N'
329 ,p_overflow_mode => 'N'
330 ,p_sequence => 3
331 ,p_frequency => NULL
332 ,p_validate => false);
333 --
334 pay_mgr_ins.ins
335 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
336 'TIB4_DUMMY')
337 ,p_magnetic_block_id => L_BLOCK3
338 ,p_next_block_id => NULL
339 ,p_last_run_executed_mode => 'N'
340 ,p_overflow_mode => 'N'
341 ,p_sequence => 1
342 ,p_frequency => NULL
343 ,p_validate => false);
344
345 pay_mgr_ins.ins
346 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
347 'TIB4_EMPLOYEE')
348 ,p_magnetic_block_id => L_BLOCK3
349 ,p_next_block_id => NULL
350 ,p_last_run_executed_mode => 'N'
351 ,p_overflow_mode => 'R'
352 ,p_sequence => 2
353 ,p_frequency => NULL
354 ,p_validate => false);
355 --
356 /*
357 pay_mgr_ins.ins
358 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
359 'W2_TIB4_SUPPLEMENTAL')
360 ,p_magnetic_block_id => L_BLOCK3
361 ,p_next_block_id => NULL
362 ,p_last_run_executed_mode => 'N'
363 ,p_overflow_mode => 'N'
364 ,p_sequence => 3
365 ,p_frequency => NULL
366 ,p_validate => false);
367 */
368
369 --
370 pay_mgr_ins.ins
371 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
372 'TIB4_INTERMEDIATE_TOTAL')
373 ,p_magnetic_block_id => L_BLOCK3
374 ,p_next_block_id => NULL
375 ,p_last_run_executed_mode => 'N'
376 ,p_overflow_mode => 'N'
377 ,p_sequence => 3
378 ,p_frequency => 41
379 ,p_validate => false);
380
381 --
382 --------------------------------------------------------------------------
383 -- State W2 format --
384 --------------------------------------------------------------------------
385 --
386 -- Set up report format to be maintained.
387 --
388 L_REPORT_FORMAT := 'ST_TIB4';
389 --
390 -- Clear down current format definition and its mappings.
391 --
392 delete from pay_magnetic_records MGR
393 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
394 from pay_magnetic_blocks MGB,
395 pay_report_format_mappings_f RFM
396 where MGB.report_format = RFM.report_format
397 AND RFM.report_format = L_REPORT_FORMAT
398 AND RFM.report_category = 'RT');
399 --
400 delete from pay_magnetic_blocks MGB
401 where MGB.report_format in (
402 SELECT report_format
403 FROM pay_report_format_mappings_f
404 WHERE report_format = L_REPORT_FORMAT
405 AND report_category = 'RT');
406 --
407 delete from pay_report_format_mappings_f RFM
408 where RFM.report_format = L_REPORT_FORMAT
409 and RFM.report_category = 'RT';
410 --
411 -- Report to format definitions.
412 --
413 insert into pay_report_format_mappings_f
414 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
415 RANGE_CODE,
416 ASSIGNMENT_ACTION_CODE,
417 INITIALIZATION_CODE,
418 ARCHIVE_CODE,
419 MAGNETIC_CODE,
420 REPORT_FORMAT, EFFECTIVE_START_DATE,
421 EFFECTIVE_END_DATE )
422 values ('W2' , 'AL', 'RT',
423 'pay_us_magw2_reporting.range_cursor',
424 'pay_us_magw2_reporting.create_assignment_act',
425 NULL,
426 NULL,
427 'pay_magtape_generic.new_formula',
428 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
429 insert into pay_report_format_mappings_f
430 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
431 RANGE_CODE,
432 ASSIGNMENT_ACTION_CODE,
433 INITIALIZATION_CODE,
434 ARCHIVE_CODE,
435 MAGNETIC_CODE,
436 REPORT_FORMAT, EFFECTIVE_START_DATE,
437 EFFECTIVE_END_DATE )
438 values ('W2' , 'AZ', 'RT',
439 'pay_us_magw2_reporting.range_cursor',
440 'pay_us_magw2_reporting.create_assignment_act',
441 NULL,
442 NULL,
443 'pay_magtape_generic.new_formula',
444 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
445 insert into pay_report_format_mappings_f
446 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
447 RANGE_CODE,
448 ASSIGNMENT_ACTION_CODE,
449 INITIALIZATION_CODE,
450 ARCHIVE_CODE,
451 MAGNETIC_CODE,
452 REPORT_FORMAT, EFFECTIVE_START_DATE,
453 EFFECTIVE_END_DATE )
454 values ('W2' , 'AR', 'RT',
455 'pay_us_magw2_reporting.range_cursor',
456 'pay_us_magw2_reporting.create_assignment_act',
457 NULL,
458 NULL,
459 'pay_magtape_generic.new_formula',
460 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
461 insert into pay_report_format_mappings_f
462 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
463 RANGE_CODE,
464 ASSIGNMENT_ACTION_CODE,
465 INITIALIZATION_CODE,
466 ARCHIVE_CODE,
467 MAGNETIC_CODE,
468 REPORT_FORMAT, EFFECTIVE_START_DATE,
469 EFFECTIVE_END_DATE )
470 values ('W2' , 'CO', 'RT',
471 'pay_us_magw2_reporting.range_cursor',
472 'pay_us_magw2_reporting.create_assignment_act',
473 NULL,
474 NULL,
475 'pay_magtape_generic.new_formula',
476 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
477 insert into pay_report_format_mappings_f
478 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
479 RANGE_CODE,
480 ASSIGNMENT_ACTION_CODE,
481 INITIALIZATION_CODE,
482 ARCHIVE_CODE,
483 MAGNETIC_CODE,
484 REPORT_FORMAT, EFFECTIVE_START_DATE,
485 EFFECTIVE_END_DATE )
486 values ('W2' , 'DE', 'RT',
487 'pay_us_magw2_reporting.range_cursor',
488 'pay_us_magw2_reporting.create_assignment_act',
489 NULL,
490 NULL,
491 'pay_magtape_generic.new_formula',
492 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
493 insert into pay_report_format_mappings_f
494 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
495 RANGE_CODE,
496 ASSIGNMENT_ACTION_CODE,
497 INITIALIZATION_CODE,
498 ARCHIVE_CODE,
499 MAGNETIC_CODE,
500 REPORT_FORMAT, EFFECTIVE_START_DATE,
501 EFFECTIVE_END_DATE )
502 values ('W2' , 'DC', 'RT',
503 'pay_us_magw2_reporting.range_cursor',
504 'pay_us_magw2_reporting.create_assignment_act',
505 NULL,
506 NULL,
507 'pay_magtape_generic.new_formula',
508 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
509 insert into pay_report_format_mappings_f
510 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
511 RANGE_CODE,
512 ASSIGNMENT_ACTION_CODE,
513 INITIALIZATION_CODE,
514 ARCHIVE_CODE,
515 MAGNETIC_CODE,
516 REPORT_FORMAT, EFFECTIVE_START_DATE,
517 EFFECTIVE_END_DATE )
518 values ('W2' , 'GA', 'RT',
519 'pay_us_magw2_reporting.range_cursor',
520 'pay_us_magw2_reporting.create_assignment_act',
521 NULL,
522 NULL,
523 'pay_magtape_generic.new_formula',
524 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
525 insert into pay_report_format_mappings_f
526 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
527 RANGE_CODE,
528 ASSIGNMENT_ACTION_CODE,
529 INITIALIZATION_CODE,
530 ARCHIVE_CODE,
531 MAGNETIC_CODE,
532 REPORT_FORMAT, EFFECTIVE_START_DATE,
533 EFFECTIVE_END_DATE )
534 values ('W2' , 'ID', 'RT',
535 'pay_us_magw2_reporting.range_cursor',
536 'pay_us_magw2_reporting.create_assignment_act',
537 NULL,
538 NULL,
539 'pay_magtape_generic.new_formula',
540 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
541 insert into pay_report_format_mappings_f
542 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
543 RANGE_CODE,
544 ASSIGNMENT_ACTION_CODE,
545 INITIALIZATION_CODE,
546 ARCHIVE_CODE,
547 MAGNETIC_CODE,
548 REPORT_FORMAT, EFFECTIVE_START_DATE,
549 EFFECTIVE_END_DATE )
550 values ('W2' , 'IL', 'RT',
551 'pay_us_magw2_reporting.range_cursor',
552 'pay_us_magw2_reporting.create_assignment_act',
553 NULL,
554 NULL,
555 'pay_magtape_generic.new_formula',
556 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
557 insert into pay_report_format_mappings_f
558 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
559 RANGE_CODE,
560 ASSIGNMENT_ACTION_CODE,
561 INITIALIZATION_CODE,
562 ARCHIVE_CODE,
563 MAGNETIC_CODE,
564 REPORT_FORMAT, EFFECTIVE_START_DATE,
565 EFFECTIVE_END_DATE )
566 values ('W2' , 'IA', 'RT',
567 'pay_us_magw2_reporting.range_cursor',
568 'pay_us_magw2_reporting.create_assignment_act',
569 NULL,
570 NULL,
571 'pay_magtape_generic.new_formula',
572 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
573 insert into pay_report_format_mappings_f
574 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
575 RANGE_CODE,
576 ASSIGNMENT_ACTION_CODE,
577 INITIALIZATION_CODE,
578 ARCHIVE_CODE,
579 MAGNETIC_CODE,
580 REPORT_FORMAT, EFFECTIVE_START_DATE,
581 EFFECTIVE_END_DATE )
582 values ('W2' , 'KS', 'RT',
583 'pay_us_magw2_reporting.range_cursor',
584 'pay_us_magw2_reporting.create_assignment_act',
585 NULL,
586 NULL,
587 'pay_magtape_generic.new_formula',
588 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
589 insert into pay_report_format_mappings_f
590 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
591 RANGE_CODE,
592 ASSIGNMENT_ACTION_CODE,
593 INITIALIZATION_CODE,
594 ARCHIVE_CODE,
595 MAGNETIC_CODE,
596 REPORT_FORMAT, EFFECTIVE_START_DATE,
597 EFFECTIVE_END_DATE )
598 values ('W2' , 'ME', 'RT',
599 'pay_us_magw2_reporting.range_cursor',
600 'pay_us_magw2_reporting.create_assignment_act',
601 NULL,
602 NULL,
603 'pay_magtape_generic.new_formula',
604 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
605 insert into pay_report_format_mappings_f
606 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
607 RANGE_CODE,
608 ASSIGNMENT_ACTION_CODE,
609 INITIALIZATION_CODE,
610 ARCHIVE_CODE,
611 MAGNETIC_CODE,
612 REPORT_FORMAT, EFFECTIVE_START_DATE,
613 EFFECTIVE_END_DATE )
614 values ('W2' , 'MD', 'RT',
615 'pay_us_magw2_reporting.range_cursor',
616 'pay_us_magw2_reporting.create_assignment_act',
617 NULL,
618 NULL,
619 'pay_magtape_generic.new_formula',
620 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
621 insert into pay_report_format_mappings_f
622 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
623 RANGE_CODE,
624 ASSIGNMENT_ACTION_CODE,
625 INITIALIZATION_CODE,
626 ARCHIVE_CODE,
627 MAGNETIC_CODE,
628 REPORT_FORMAT, EFFECTIVE_START_DATE,
629 EFFECTIVE_END_DATE )
630 values ('W2' , 'MA', 'RT',
631 'pay_us_magw2_reporting.range_cursor',
632 'pay_us_magw2_reporting.create_assignment_act',
633 NULL,
634 NULL,
635 'pay_magtape_generic.new_formula',
636 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
637 insert into pay_report_format_mappings_f
638 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
639 RANGE_CODE,
640 ASSIGNMENT_ACTION_CODE,
641 INITIALIZATION_CODE,
642 ARCHIVE_CODE,
643 MAGNETIC_CODE,
644 REPORT_FORMAT, EFFECTIVE_START_DATE,
645 EFFECTIVE_END_DATE )
646 values ('W2' , 'MN', 'RT',
647 'pay_us_magw2_reporting.range_cursor',
648 'pay_us_magw2_reporting.create_assignment_act',
649 NULL,
650 NULL,
651 'pay_magtape_generic.new_formula',
652 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
653 insert into pay_report_format_mappings_f
654 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
655 RANGE_CODE,
656 ASSIGNMENT_ACTION_CODE,
657 INITIALIZATION_CODE,
658 ARCHIVE_CODE,
659 MAGNETIC_CODE,
660 REPORT_FORMAT, EFFECTIVE_START_DATE,
661 EFFECTIVE_END_DATE )
662 values ('W2' , 'MO', 'RT',
663 'pay_us_magw2_reporting.range_cursor',
664 'pay_us_magw2_reporting.create_assignment_act',
665 NULL,
666 NULL,
667 'pay_magtape_generic.new_formula',
668 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
669 insert into pay_report_format_mappings_f
670 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
671 RANGE_CODE,
672 ASSIGNMENT_ACTION_CODE,
673 INITIALIZATION_CODE,
674 ARCHIVE_CODE,
675 MAGNETIC_CODE,
676 REPORT_FORMAT, EFFECTIVE_START_DATE,
677 EFFECTIVE_END_DATE )
678 values ('W2' , 'MT', 'RT',
679 'pay_us_magw2_reporting.range_cursor',
680 'pay_us_magw2_reporting.create_assignment_act',
681 NULL,
682 NULL,
683 'pay_magtape_generic.new_formula',
684 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
685 insert into pay_report_format_mappings_f
686 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
687 RANGE_CODE,
688 ASSIGNMENT_ACTION_CODE,
689 INITIALIZATION_CODE,
690 ARCHIVE_CODE,
691 MAGNETIC_CODE,
692 REPORT_FORMAT, EFFECTIVE_START_DATE,
693 EFFECTIVE_END_DATE )
694 values ('W2' , 'NE', 'RT',
695 'pay_us_magw2_reporting.range_cursor',
696 'pay_us_magw2_reporting.create_assignment_act',
697 NULL,
698 NULL,
699 'pay_magtape_generic.new_formula',
700 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
701 insert into pay_report_format_mappings_f
702 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
703 RANGE_CODE,
704 ASSIGNMENT_ACTION_CODE,
705 INITIALIZATION_CODE,
706 ARCHIVE_CODE,
707 MAGNETIC_CODE,
708 REPORT_FORMAT, EFFECTIVE_START_DATE,
709 EFFECTIVE_END_DATE )
710 values ('W2' , 'NM', 'RT',
711 'pay_us_magw2_reporting.range_cursor',
712 'pay_us_magw2_reporting.create_assignment_act',
713 NULL,
714 NULL,
715 'pay_magtape_generic.new_formula',
716 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
717 insert into pay_report_format_mappings_f
718 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
719 RANGE_CODE,
720 ASSIGNMENT_ACTION_CODE,
721 INITIALIZATION_CODE,
722 ARCHIVE_CODE,
723 MAGNETIC_CODE,
724 REPORT_FORMAT, EFFECTIVE_START_DATE,
725 EFFECTIVE_END_DATE )
726 values ('W2' , 'ND', 'RT',
727 'pay_us_magw2_reporting.range_cursor',
728 'pay_us_magw2_reporting.create_assignment_act',
729 NULL,
730 NULL,
731 'pay_magtape_generic.new_formula',
732 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
733 insert into pay_report_format_mappings_f
734 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
735 RANGE_CODE,
736 ASSIGNMENT_ACTION_CODE,
737 INITIALIZATION_CODE,
738 ARCHIVE_CODE,
739 MAGNETIC_CODE,
740 REPORT_FORMAT, EFFECTIVE_START_DATE,
741 EFFECTIVE_END_DATE )
742 values ('W2' , 'OK', 'RT',
743 'pay_us_magw2_reporting.range_cursor',
744 'pay_us_magw2_reporting.create_assignment_act',
745 NULL,
746 NULL,
747 'pay_magtape_generic.new_formula',
748 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
749 insert into pay_report_format_mappings_f
750 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
751 RANGE_CODE,
752 ASSIGNMENT_ACTION_CODE,
753 INITIALIZATION_CODE,
754 ARCHIVE_CODE,
755 MAGNETIC_CODE,
756 REPORT_FORMAT, EFFECTIVE_START_DATE,
757 EFFECTIVE_END_DATE )
758 values ('W2' , 'PA', 'RT',
759 'pay_us_magw2_reporting.range_cursor',
760 'pay_us_magw2_reporting.create_assignment_act',
761 NULL,
762 NULL,
763 'pay_magtape_generic.new_formula',
764 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
765 insert into pay_report_format_mappings_f
766 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
767 RANGE_CODE,
768 ASSIGNMENT_ACTION_CODE,
769 INITIALIZATION_CODE,
770 ARCHIVE_CODE,
771 MAGNETIC_CODE,
772 REPORT_FORMAT, EFFECTIVE_START_DATE,
773 EFFECTIVE_END_DATE )
774 values ('W2' , 'PR', 'RT',
775 'pay_us_magw2_reporting.range_cursor',
776 'pay_us_magw2_reporting.create_assignment_act',
777 NULL,
778 NULL,
779 'pay_magtape_generic.new_formula',
780 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
781 insert into pay_report_format_mappings_f
782 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
783 RANGE_CODE,
784 ASSIGNMENT_ACTION_CODE,
785 INITIALIZATION_CODE,
786 ARCHIVE_CODE,
787 MAGNETIC_CODE,
788 REPORT_FORMAT, EFFECTIVE_START_DATE,
789 EFFECTIVE_END_DATE )
790 values ('W2' , 'RI', 'RT',
791 'pay_us_magw2_reporting.range_cursor',
792 'pay_us_magw2_reporting.create_assignment_act',
793 NULL,
794 NULL,
795 'pay_magtape_generic.new_formula',
796 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
797 insert into pay_report_format_mappings_f
798 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
799 RANGE_CODE,
800 ASSIGNMENT_ACTION_CODE,
801 INITIALIZATION_CODE,
802 ARCHIVE_CODE,
803 MAGNETIC_CODE,
804 REPORT_FORMAT, EFFECTIVE_START_DATE,
805 EFFECTIVE_END_DATE )
806 values ('W2' , 'SC', 'RT',
807 'pay_us_magw2_reporting.range_cursor',
808 'pay_us_magw2_reporting.create_assignment_act',
809 NULL,
810 NULL,
811 'pay_magtape_generic.new_formula',
812 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
813 insert into pay_report_format_mappings_f
814 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
815 RANGE_CODE,
816 ASSIGNMENT_ACTION_CODE,
817 INITIALIZATION_CODE,
818 ARCHIVE_CODE,
819 MAGNETIC_CODE,
820 REPORT_FORMAT, EFFECTIVE_START_DATE,
821 EFFECTIVE_END_DATE )
822 values ('W2' , 'UT', 'RT',
823 'pay_us_magw2_reporting.range_cursor',
824 'pay_us_magw2_reporting.create_assignment_act',
825 NULL,
826 NULL,
827 'pay_magtape_generic.new_formula',
828 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
829 insert into pay_report_format_mappings_f
830 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
831 RANGE_CODE,
832 ASSIGNMENT_ACTION_CODE,
833 INITIALIZATION_CODE,
834 ARCHIVE_CODE,
835 MAGNETIC_CODE,
836 REPORT_FORMAT, EFFECTIVE_START_DATE,
837 EFFECTIVE_END_DATE )
838 values ('W2' , 'VA', 'RT',
839 'pay_us_magw2_reporting.range_cursor',
840 'pay_us_magw2_reporting.create_assignment_act',
841 NULL,
842 NULL,
843 'pay_magtape_generic.new_formula',
844 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
845 insert into pay_report_format_mappings_f
846 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
847 RANGE_CODE,
848 ASSIGNMENT_ACTION_CODE,
849 INITIALIZATION_CODE,
850 ARCHIVE_CODE,
851 MAGNETIC_CODE,
852 REPORT_FORMAT, EFFECTIVE_START_DATE,
853 EFFECTIVE_END_DATE )
854 values ('W2' , 'WI', 'RT',
855 'pay_us_magw2_reporting.range_cursor',
856 'pay_us_magw2_reporting.create_assignment_act',
857 NULL,
858 NULL,
859 'pay_magtape_generic.new_formula',
860 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
861 --
862 -- Block definitions.
863 --
864 pay_mgb_ins.ins
865 (p_magnetic_block_id => L_BLOCK1
866 ,p_block_name => 'TRANSMITTER'
867 ,p_main_block_flag => 'Y'
868 ,p_report_format => L_REPORT_FORMAT
869 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
870 ,p_no_column_returned => 12
871 ,p_validate => false);
872 --
873 pay_mgb_ins.ins
874 (p_magnetic_block_id => L_BLOCK2
875 ,p_block_name => 'EMPLOYER'
876 ,p_main_block_flag => 'N'
877 ,p_report_format => L_REPORT_FORMAT
878 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
879 ,p_no_column_returned => 12
880 ,p_validate => false);
881 --
882 pay_mgb_ins.ins
883 (p_magnetic_block_id => L_BLOCK3
884 ,p_block_name => 'EMPLOYEE'
885 ,p_main_block_flag => 'N'
886 ,p_report_format => L_REPORT_FORMAT
887 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employee'
888 ,p_no_column_returned => 8
889 ,p_validate => false);
890 --
891 pay_mgb_ins.ins
892 (p_magnetic_block_id => L_BLOCK4
893 ,p_block_name => 'HIGH_COMP'
894 ,p_main_block_flag => 'N'
895 ,p_report_format => L_REPORT_FORMAT
896 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
897 ,p_no_column_returned => 2
898 ,p_validate => false);
899 --
900 -- Record definitions.
901 --
902
903 pay_mgr_ins.ins
904 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
905 'TIB4_TRANSMITTER')
906 ,p_magnetic_block_id => L_BLOCK1
907 ,p_next_block_id => NULL
908 ,p_last_run_executed_mode => 'N'
909 ,p_overflow_mode => 'N'
910 ,p_sequence => 1
911 ,p_frequency => NULL
912 ,p_validate => false);
913
914 --
915 pay_mgr_ins.ins
916 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
917 'TIB4_BINFO')
918 ,p_magnetic_block_id => L_BLOCK1
919 ,p_next_block_id => L_BLOCK2
920 ,p_last_run_executed_mode => 'N'
921 ,p_overflow_mode => 'N'
922 ,p_sequence => 2
923 ,p_frequency => NULL
924 ,p_validate => false);
925 --
926 pay_mgr_ins.ins
927 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
928 'TIB4_FINAL')
929 ,p_magnetic_block_id => L_BLOCK1
930 ,p_next_block_id => L_BLOCK4
931 ,p_last_run_executed_mode => 'N'
932 ,p_overflow_mode => 'N'
933 ,p_sequence => 3
934 ,p_frequency => NULL
935 ,p_validate => false);
936 --
937 pay_mgr_ins.ins
938 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
939 'W2_HIGH_COMP')
940 ,p_magnetic_block_id => L_BLOCK4
941 ,p_next_block_id => NULL
942 ,p_last_run_executed_mode => 'N'
943 ,p_overflow_mode => 'N'
944 ,p_sequence => 1
945 ,p_frequency => NULL
946 ,p_validate => false);
947 --
948 pay_mgr_ins.ins
949 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
950 'TIB4_EMPLOYER')
951 ,p_magnetic_block_id => L_BLOCK2
952 ,p_next_block_id => L_BLOCK3
953 ,p_last_run_executed_mode => 'N'
954 ,p_overflow_mode => 'N'
955 ,p_sequence => 1
956 ,p_frequency => NULL
957 ,p_validate => false);
958 --
959 pay_mgr_ins.ins
960 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
961 'TIB4_INTERMEDIATE_TOTAL')
962 ,p_magnetic_block_id => L_BLOCK2
963 ,p_next_block_id => NULL
964 ,p_last_run_executed_mode => 'N'
965 ,p_overflow_mode => 'N'
966 ,p_sequence => 2
967 ,p_frequency => NULL
968 ,p_validate => false);
969 --
970 pay_mgr_ins.ins
971 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
972 'TIB4_TOTAL')
973 ,p_magnetic_block_id => L_BLOCK2
974 ,p_next_block_id => NULL
975 ,p_last_run_executed_mode => 'N'
976 ,p_overflow_mode => 'N'
977 ,p_sequence => 3
978 ,p_frequency => NULL
979 ,p_validate => false);
980 --
981 pay_mgr_ins.ins
982 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
983 'TIB4_DUMMY')
984 ,p_magnetic_block_id => L_BLOCK3
985 ,p_next_block_id => NULL
986 ,p_last_run_executed_mode => 'N'
987 ,p_overflow_mode => 'N'
988 ,p_sequence => 1
989 ,p_frequency => NULL
990 ,p_validate => false);
991 --
992 pay_mgr_ins.ins
993 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
994 'TIB4_EMPLOYEE')
995 ,p_magnetic_block_id => L_BLOCK3
996 ,p_next_block_id => NULL
997 ,p_last_run_executed_mode => 'N'
998 ,p_overflow_mode => 'R'
999 ,p_sequence => 2
1000 ,p_frequency => NULL
1001 ,p_validate => false);
1002 --
1003 pay_mgr_ins.ins
1004 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1005 'W2_TIB4_SUPPLEMENTAL')
1006 ,p_magnetic_block_id => L_BLOCK3
1007 ,p_next_block_id => NULL
1008 ,p_last_run_executed_mode => 'N'
1009 ,p_overflow_mode => 'N'
1010 ,p_sequence => 3
1011 ,p_frequency => NULL
1012 ,p_validate => false);
1013 --
1014 pay_mgr_ins.ins
1015 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1016 'TIB4_INTERMEDIATE_TOTAL')
1017 ,p_magnetic_block_id => L_BLOCK3
1018 ,p_next_block_id => NULL
1019 ,p_last_run_executed_mode => 'N'
1020 ,p_overflow_mode => 'N'
1021 ,p_sequence => 4
1022 ,p_frequency => 41
1023 ,p_validate => false);
1024
1025 --
1026 --------------------------------------------------------------------------
1027 -- New Jersey State W2 format --
1028 --------------------------------------------------------------------------
1029 --
1030 -- Set up report format to be maintained.
1031 --
1032 L_REPORT_FORMAT := 'NJ_TIB4';
1033 --
1034 -- Clear down current format definition and its mappings.
1035 --
1036 delete from pay_magnetic_records MGR
1037 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
1038 from pay_magnetic_blocks MGB,
1039 pay_report_format_mappings_f RFM
1040 where MGB.report_format = RFM.report_format
1041 AND RFM.report_format = L_REPORT_FORMAT
1042 AND RFM.report_category = 'RT');
1043 --
1044 delete from pay_magnetic_blocks MGB
1045 where MGB.report_format in (
1046 SELECT report_format
1047 FROM pay_report_format_mappings_f
1048 WHERE report_format = L_REPORT_FORMAT
1049 AND report_category = 'RT');
1050 --
1051 delete from pay_report_format_mappings_f RFM
1052 where RFM.report_format = L_REPORT_FORMAT
1053 and RFM.report_category = 'RT';
1054 --
1055 -- Report to format definitions.
1056 --
1057 insert into pay_report_format_mappings_f
1058 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
1059 RANGE_CODE,
1060 ASSIGNMENT_ACTION_CODE,
1061 INITIALIZATION_CODE,
1062 ARCHIVE_CODE,
1063 MAGNETIC_CODE,
1064 REPORT_FORMAT, EFFECTIVE_START_DATE,
1065 EFFECTIVE_END_DATE )
1066 values ('W2' , 'NJ', 'RT',
1067 'pay_us_magw2_reporting.range_cursor',
1068 'pay_us_magw2_reporting.create_assignment_act',
1069 NULL,
1070 NULL,
1071 'pay_magtape_generic.new_formula',
1072 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
1073 --
1074 -- Block definitions.
1075 --
1076 pay_mgb_ins.ins
1077 (p_magnetic_block_id => L_BLOCK1
1078 ,p_block_name => 'TRANSMITTER'
1079 ,p_main_block_flag => 'Y'
1080 ,p_report_format => L_REPORT_FORMAT
1081 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
1082 ,p_no_column_returned => 12
1083 ,p_validate => false);
1084 --
1085 pay_mgb_ins.ins
1086 (p_magnetic_block_id => L_BLOCK2
1087 ,p_block_name => 'EMPLOYER'
1088 ,p_main_block_flag => 'N'
1089 ,p_report_format => L_REPORT_FORMAT
1090 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
1091 ,p_no_column_returned => 12
1092 ,p_validate => false);
1093 --
1094 pay_mgb_ins.ins
1095 (p_magnetic_block_id => L_BLOCK3
1096 ,p_block_name => 'EMPLOYEE'
1097 ,p_main_block_flag => 'N'
1098 ,p_report_format => L_REPORT_FORMAT
1099 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employee'
1100 ,p_no_column_returned => 8
1101 ,p_validate => false);
1102 --
1103 pay_mgb_ins.ins
1104 (p_magnetic_block_id => L_BLOCK4
1105 ,p_block_name => 'HIGH_COMP'
1106 ,p_main_block_flag => 'N'
1107 ,p_report_format => L_REPORT_FORMAT
1108 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
1109 ,p_no_column_returned => 2
1110 ,p_validate => false);
1111 --
1112 -- Record definitions.
1113 --
1114
1115 pay_mgr_ins.ins
1116 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1117 'TIB4_TRANSMITTER')
1118 ,p_magnetic_block_id => L_BLOCK1
1119 ,p_next_block_id => NULL
1120 ,p_last_run_executed_mode => 'N'
1121 ,p_overflow_mode => 'N'
1122 ,p_sequence => 1
1123 ,p_frequency => NULL
1124 ,p_validate => false);
1125
1126 --
1127 pay_mgr_ins.ins
1128 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1129 'TIB4_BINFO')
1130 ,p_magnetic_block_id => L_BLOCK1
1131 ,p_next_block_id => L_BLOCK2
1132 ,p_last_run_executed_mode => 'N'
1133 ,p_overflow_mode => 'N'
1134 ,p_sequence => 2
1135 ,p_frequency => NULL
1136 ,p_validate => false);
1137 --
1138 pay_mgr_ins.ins
1139 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1140 'TIB4_FINAL')
1141 ,p_magnetic_block_id => L_BLOCK1
1142 ,p_next_block_id => L_BLOCK4
1143 ,p_last_run_executed_mode => 'N'
1144 ,p_overflow_mode => 'N'
1145 ,p_sequence => 3
1146 ,p_frequency => NULL
1147 ,p_validate => false);
1148 --
1149 pay_mgr_ins.ins
1150 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1151 'W2_HIGH_COMP')
1152 ,p_magnetic_block_id => L_BLOCK4
1153 ,p_next_block_id => NULL
1154 ,p_last_run_executed_mode => 'N'
1155 ,p_overflow_mode => 'N'
1156 ,p_sequence => 1
1157 ,p_frequency => NULL
1158 ,p_validate => false);
1159 --
1160 pay_mgr_ins.ins
1161 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1162 'TIB4_EMPLOYER')
1163 ,p_magnetic_block_id => L_BLOCK2
1164 ,p_next_block_id => L_BLOCK3
1165 ,p_last_run_executed_mode => 'N'
1166 ,p_overflow_mode => 'N'
1167 ,p_sequence => 1
1168 ,p_frequency => NULL
1169 ,p_validate => false);
1170 --
1171 pay_mgr_ins.ins
1172 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1173 'TIB4_INTERMEDIATE_TOTAL')
1174 ,p_magnetic_block_id => L_BLOCK2
1175 ,p_next_block_id => NULL
1176 ,p_last_run_executed_mode => 'N'
1177 ,p_overflow_mode => 'N'
1178 ,p_sequence => 2
1179 ,p_frequency => NULL
1180 ,p_validate => false);
1181 --
1182 pay_mgr_ins.ins
1183 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1184 'TIB4_TOTAL')
1185 ,p_magnetic_block_id => L_BLOCK2
1186 ,p_next_block_id => NULL
1187 ,p_last_run_executed_mode => 'N'
1188 ,p_overflow_mode => 'N'
1189 ,p_sequence => 3
1190 ,p_frequency => NULL
1191 ,p_validate => false);
1192 --
1193 pay_mgr_ins.ins
1194 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1195 'TIB4_DUMMY')
1196 ,p_magnetic_block_id => L_BLOCK3
1197 ,p_next_block_id => NULL
1198 ,p_last_run_executed_mode => 'N'
1199 ,p_overflow_mode => 'N'
1200 ,p_sequence => 1
1201 ,p_frequency => NULL
1202 ,p_validate => false);
1203 --
1204 pay_mgr_ins.ins
1205 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1206 'TIB4_EMPLOYEE')
1207 ,p_magnetic_block_id => L_BLOCK3
1208 ,p_next_block_id => NULL
1209 ,p_last_run_executed_mode => 'N'
1210 ,p_overflow_mode => 'R'
1211 ,p_sequence => 2
1212 ,p_frequency => NULL
1213 ,p_validate => false);
1214 --
1215 pay_mgr_ins.ins
1216 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1217 'NJTIB4_SUPPLEMENTAL')
1218 ,p_magnetic_block_id => L_BLOCK3
1219 ,p_next_block_id => NULL
1220 ,p_last_run_executed_mode => 'N'
1221 ,p_overflow_mode => 'N'
1222 ,p_sequence => 3
1223 ,p_frequency => NULL
1224 ,p_validate => false);
1225 --
1226 pay_mgr_ins.ins
1227 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1228 'TIB4_INTERMEDIATE_TOTAL')
1229 ,p_magnetic_block_id => L_BLOCK3
1230 ,p_next_block_id => NULL
1231 ,p_last_run_executed_mode => 'N'
1232 ,p_overflow_mode => 'N'
1233 ,p_sequence => 4
1234 ,p_frequency => 41
1235 ,p_validate => false);
1236
1237 --
1238 --------------------------------------------------------------------------
1239 -- West Virginia State W2 format --
1240 --------------------------------------------------------------------------
1241 --
1242 -- Set up report format to be maintained.
1243 --
1244 L_REPORT_FORMAT := 'WV_TIB4';
1245 --
1246 -- Clear down current format definition and its mappings.
1247 --
1248 delete from pay_magnetic_records MGR
1249 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
1250 from pay_magnetic_blocks MGB,
1251 pay_report_format_mappings_f RFM
1252 where MGB.report_format = RFM.report_format
1253 AND RFM.report_format = L_REPORT_FORMAT
1254 AND RFM.report_category = 'RT');
1255 --
1256 delete from pay_magnetic_blocks MGB
1257 where MGB.report_format in (
1258 SELECT report_format
1259 FROM pay_report_format_mappings_f
1260 WHERE report_format = L_REPORT_FORMAT
1261 AND report_category = 'RT');
1262 --
1263 delete from pay_report_format_mappings_f RFM
1264 where RFM.report_format = L_REPORT_FORMAT
1265 and RFM.report_category = 'RT';
1266 --
1267 -- Report to format definitions.
1268 --
1269 insert into pay_report_format_mappings_f
1270 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
1271 RANGE_CODE,
1272 ASSIGNMENT_ACTION_CODE,
1273 INITIALIZATION_CODE,
1274 ARCHIVE_CODE,
1275 MAGNETIC_CODE,
1276 REPORT_FORMAT, EFFECTIVE_START_DATE,
1277 EFFECTIVE_END_DATE )
1278 values ('W2' , 'WV', 'RT',
1279 'pay_us_magw2_reporting.range_cursor',
1280 'pay_us_magw2_reporting.create_assignment_act',
1281 NULL,
1282 NULL,
1283 'pay_magtape_generic.new_formula',
1284 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
1285 --
1286 -- Block definitions.
1287 --
1288 pay_mgb_ins.ins
1289 (p_magnetic_block_id => L_BLOCK1
1290 ,p_block_name => 'TRANSMITTER'
1291 ,p_main_block_flag => 'Y'
1292 ,p_report_format => L_REPORT_FORMAT
1293 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
1294 ,p_no_column_returned => 12
1295 ,p_validate => false);
1296 --
1297 pay_mgb_ins.ins
1298 (p_magnetic_block_id => L_BLOCK2
1299 ,p_block_name => 'EMPLOYER'
1300 ,p_main_block_flag => 'N'
1301 ,p_report_format => L_REPORT_FORMAT
1302 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
1303 ,p_no_column_returned => 12
1304 ,p_validate => false);
1305 --
1306 pay_mgb_ins.ins
1307 (p_magnetic_block_id => L_BLOCK3
1308 ,p_block_name => 'EMPLOYEE'
1309 ,p_main_block_flag => 'N'
1310 ,p_report_format => L_REPORT_FORMAT
1311 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employee'
1312 ,p_no_column_returned => 8
1313 ,p_validate => false);
1314 --
1315 pay_mgb_ins.ins
1316 (p_magnetic_block_id => L_BLOCK4
1317 ,p_block_name => 'HIGH_COMP'
1318 ,p_main_block_flag => 'N'
1319 ,p_report_format => L_REPORT_FORMAT
1320 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
1321 ,p_no_column_returned => 2
1322 ,p_validate => false);
1323 --
1324 -- Record definitions.
1325 --
1326
1327 pay_mgr_ins.ins
1328 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1329 'DUMMY_TIB4_TRANSMITTER')
1330 ,p_magnetic_block_id => L_BLOCK1
1331 ,p_next_block_id => L_BLOCK2
1332 ,p_last_run_executed_mode => 'N'
1333 ,p_overflow_mode => 'N'
1334 ,p_sequence => 1
1335 ,p_frequency => NULL
1336 ,p_validate => false);
1337
1338 --
1339 pay_mgr_ins.ins
1340 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1341 'DUMMY_TIB4_FINAL')
1342 ,p_magnetic_block_id => L_BLOCK1
1343 ,p_next_block_id => L_BLOCK4
1344 ,p_last_run_executed_mode => 'N'
1345 ,p_overflow_mode => 'N'
1346 ,p_sequence => 2
1347 ,p_frequency => NULL
1348 ,p_validate => false);
1349 --
1350 pay_mgr_ins.ins
1351 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1352 'W2_HIGH_COMP')
1353 ,p_magnetic_block_id => L_BLOCK4
1354 ,p_next_block_id => NULL
1355 ,p_last_run_executed_mode => 'N'
1356 ,p_overflow_mode => 'N'
1357 ,p_sequence => 1
1358 ,p_frequency => NULL
1359 ,p_validate => false);
1360 --
1361 pay_mgr_ins.ins
1362 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1363 'TIB4_EMPLOYER')
1364 ,p_magnetic_block_id => L_BLOCK2
1365 ,p_next_block_id => L_BLOCK3
1366 ,p_last_run_executed_mode => 'N'
1367 ,p_overflow_mode => 'N'
1368 ,p_sequence => 1
1369 ,p_frequency => NULL
1370 ,p_validate => false);
1371 --
1372 pay_mgr_ins.ins
1373 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1374 'DUMMY_TIB4_TOTAL')
1375 ,p_magnetic_block_id => L_BLOCK2
1376 ,p_next_block_id => NULL
1377 ,p_last_run_executed_mode => 'N'
1378 ,p_overflow_mode => 'N'
1379 ,p_sequence => 2
1380 ,p_frequency => NULL
1381 ,p_validate => false);
1382 --
1383 pay_mgr_ins.ins
1384 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1385 'TIB4_DUMMY')
1386 ,p_magnetic_block_id => L_BLOCK3
1387 ,p_next_block_id => NULL
1388 ,p_last_run_executed_mode => 'N'
1389 ,p_overflow_mode => 'N'
1390 ,p_sequence => 1
1391 ,p_frequency => NULL
1392 ,p_validate => false);
1393 --
1394 pay_mgr_ins.ins
1395 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1396 'TIB4_EMPLOYEE')
1397 ,p_magnetic_block_id => L_BLOCK3
1398 ,p_next_block_id => NULL
1399 ,p_last_run_executed_mode => 'N'
1400 ,p_overflow_mode => 'R'
1401 ,p_sequence => 2
1402 ,p_frequency => NULL
1403 ,p_validate => false);
1404 --
1405 pay_mgr_ins.ins
1406 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1407 'W2_TIB4_SUPPLEMENTAL')
1408 ,p_magnetic_block_id => L_BLOCK3
1409 ,p_next_block_id => NULL
1410 ,p_last_run_executed_mode => 'N'
1411 ,p_overflow_mode => 'N'
1412 ,p_sequence => 3
1413 ,p_frequency => NULL
1414 ,p_validate => false);
1415
1416 --
1417 --------------------------------------------------------------------------
1418 -- Michigan State W2 format --
1419 --------------------------------------------------------------------------
1420 --
1421 -- Set up report format to be maintained.
1422 --
1423 L_REPORT_FORMAT := 'MI_TIB4';
1424 --
1425 -- Clear down current format definition and its mappings.
1426 --
1427 delete from pay_magnetic_records MGR
1428 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
1429 from pay_magnetic_blocks MGB,
1430 pay_report_format_mappings_f RFM
1431 where MGB.report_format = RFM.report_format
1432 AND RFM.report_format = L_REPORT_FORMAT
1433 AND RFM.report_category = 'RT');
1434 --
1435 delete from pay_magnetic_blocks MGB
1436 where MGB.report_format in (
1437 SELECT report_format
1438 FROM pay_report_format_mappings_f
1439 WHERE report_format = L_REPORT_FORMAT
1440 AND report_category = 'RT');
1441 --
1442 delete from pay_report_format_mappings_f RFM
1443 where RFM.report_format = L_REPORT_FORMAT
1444 and RFM.report_category = 'RT';
1445 --
1446 -- Report to format definitions.
1447 --
1448 insert into pay_report_format_mappings_f
1449 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
1450 RANGE_CODE,
1451 ASSIGNMENT_ACTION_CODE,
1452 INITIALIZATION_CODE,
1453 ARCHIVE_CODE,
1454 MAGNETIC_CODE,
1455 REPORT_FORMAT, EFFECTIVE_START_DATE,
1456 EFFECTIVE_END_DATE )
1457 values ('W2' , 'MI', 'RT',
1458 'pay_us_magw2_reporting.range_cursor',
1459 'pay_us_magw2_reporting.create_assignment_act',
1460 NULL,
1461 NULL,
1462 'pay_magtape_generic.new_formula',
1463 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
1464 insert into pay_report_format_mappings_f
1465 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
1466 RANGE_CODE,
1467 ASSIGNMENT_ACTION_CODE,
1468 INITIALIZATION_CODE,
1469 ARCHIVE_CODE,
1470 MAGNETIC_CODE,
1471 REPORT_FORMAT, EFFECTIVE_START_DATE,
1472 EFFECTIVE_END_DATE )
1473 values ('W2' , 'LA', 'RT',
1474 'pay_us_magw2_reporting.range_cursor',
1475 'pay_us_magw2_reporting.create_assignment_act',
1476 NULL,
1477 NULL,
1478 'pay_magtape_generic.new_formula',
1479 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
1480 --
1481 -- Block definitions.
1482 --
1483 pay_mgb_ins.ins
1484 (p_magnetic_block_id => L_BLOCK1
1485 ,p_block_name => 'TRANSMITTER'
1486 ,p_main_block_flag => 'Y'
1487 ,p_report_format => L_REPORT_FORMAT
1488 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
1489 ,p_no_column_returned => 12
1490 ,p_validate => false);
1491 --
1492 pay_mgb_ins.ins
1493 (p_magnetic_block_id => L_BLOCK2
1494 ,p_block_name => 'EMPLOYER'
1495 ,p_main_block_flag => 'N'
1496 ,p_report_format => L_REPORT_FORMAT
1497 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
1498 ,p_no_column_returned => 12
1499 ,p_validate => false);
1500 --
1501 pay_mgb_ins.ins
1502 (p_magnetic_block_id => L_BLOCK3
1503 ,p_block_name => 'EMPLOYEE'
1504 ,p_main_block_flag => 'N'
1505 ,p_report_format => L_REPORT_FORMAT
1506 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employee'
1507 ,p_no_column_returned => 8
1508 ,p_validate => false);
1509 --
1510 pay_mgb_ins.ins
1511 (p_magnetic_block_id => L_BLOCK4
1512 ,p_block_name => 'HIGH_COMP'
1513 ,p_main_block_flag => 'N'
1514 ,p_report_format => L_REPORT_FORMAT
1515 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
1516 ,p_no_column_returned => 2
1517 ,p_validate => false);
1518 --
1519 -- Record definitions.
1520 --
1521
1522 pay_mgr_ins.ins
1523 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1524 'TIB4_TRANSMITTER')
1525 ,p_magnetic_block_id => L_BLOCK1
1526 ,p_next_block_id => L_BLOCK2
1527 ,p_last_run_executed_mode => 'N'
1528 ,p_overflow_mode => 'N'
1529 ,p_sequence => 1
1530 ,p_frequency => NULL
1531 ,p_validate => false);
1532
1533 --
1534 pay_mgr_ins.ins
1535 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1536 'DUMMY_TIB4_FINAL')
1537 ,p_magnetic_block_id => L_BLOCK1
1538 ,p_next_block_id => L_BLOCK4
1539 ,p_last_run_executed_mode => 'N'
1540 ,p_overflow_mode => 'N'
1541 ,p_sequence => 2
1542 ,p_frequency => NULL
1543 ,p_validate => false);
1544 --
1545 pay_mgr_ins.ins
1546 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1547 'W2_HIGH_COMP')
1548 ,p_magnetic_block_id => L_BLOCK4
1549 ,p_next_block_id => NULL
1550 ,p_last_run_executed_mode => 'N'
1551 ,p_overflow_mode => 'N'
1552 ,p_sequence => 1
1553 ,p_frequency => NULL
1554 ,p_validate => false);
1555 --
1556 pay_mgr_ins.ins
1557 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1558 'TIB4_EMPLOYER')
1559 ,p_magnetic_block_id => L_BLOCK2
1560 ,p_next_block_id => L_BLOCK3
1561 ,p_last_run_executed_mode => 'N'
1562 ,p_overflow_mode => 'N'
1563 ,p_sequence => 1
1564 ,p_frequency => NULL
1565 ,p_validate => false);
1566 --
1567 pay_mgr_ins.ins
1568 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1569 'DUMMY_TIB4_TOTAL')
1570 ,p_magnetic_block_id => L_BLOCK2
1571 ,p_next_block_id => NULL
1572 ,p_last_run_executed_mode => 'N'
1573 ,p_overflow_mode => 'N'
1574 ,p_sequence => 2
1575 ,p_frequency => NULL
1576 ,p_validate => false);
1577 --
1578 pay_mgr_ins.ins
1579 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1580 'TIB4_DUMMY')
1581 ,p_magnetic_block_id => L_BLOCK3
1582 ,p_next_block_id => NULL
1583 ,p_last_run_executed_mode => 'N'
1584 ,p_overflow_mode => 'N'
1585 ,p_sequence => 1
1586 ,p_frequency => NULL
1587 ,p_validate => false);
1588 --
1589 pay_mgr_ins.ins
1590 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1591 'TIB4_EMPLOYEE')
1592 ,p_magnetic_block_id => L_BLOCK3
1593 ,p_next_block_id => NULL
1594 ,p_last_run_executed_mode => 'N'
1595 ,p_overflow_mode => 'R'
1596 ,p_sequence => 2
1597 ,p_frequency => NULL
1598 ,p_validate => false);
1599 --
1600 pay_mgr_ins.ins
1601 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1602 'W2_TIB4_SUPPLEMENTAL')
1603 ,p_magnetic_block_id => L_BLOCK3
1604 ,p_next_block_id => NULL
1605 ,p_last_run_executed_mode => 'N'
1606 ,p_overflow_mode => 'N'
1607 ,p_sequence => 3
1608 ,p_frequency => NULL
1609 ,p_validate => false);
1610
1611 --
1612 --------------------------------------------------------------------------
1613 -- Kentucky State W2 format --
1614 --------------------------------------------------------------------------
1615 --
1616 -- Set up report format to be maintained.
1617 --
1618 L_REPORT_FORMAT := 'KY_TIB4';
1619 --
1620 -- Clear down current format definition and its mappings.
1621 --
1622 delete from pay_magnetic_records MGR
1623 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
1624 from pay_magnetic_blocks MGB,
1625 pay_report_format_mappings_f RFM
1626 where MGB.report_format = RFM.report_format
1627 AND RFM.report_format = L_REPORT_FORMAT
1628 AND RFM.report_category = 'RT');
1629 --
1630 delete from pay_magnetic_blocks MGB
1631 where MGB.report_format in (
1632 SELECT report_format
1633 FROM pay_report_format_mappings_f
1634 WHERE report_format = L_REPORT_FORMAT
1635 AND report_category = 'RT');
1636 --
1637 --
1638 delete from pay_report_format_mappings_f RFM
1639 where RFM.report_format = L_REPORT_FORMAT
1640 and RFM.report_category = 'RT';
1641 --
1642 -- Report to format definitions.
1643 --
1644 insert into pay_report_format_mappings_f
1645 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
1646 RANGE_CODE,
1647 ASSIGNMENT_ACTION_CODE,
1648 INITIALIZATION_CODE,
1649 ARCHIVE_CODE,
1650 MAGNETIC_CODE,
1651 REPORT_FORMAT, EFFECTIVE_START_DATE,
1652 EFFECTIVE_END_DATE )
1653 values ('W2' , 'KY', 'RT',
1654 'pay_us_magw2_reporting.range_cursor',
1655 'pay_us_magw2_reporting.create_assignment_act',
1656 NULL,
1657 NULL,
1658 'pay_magtape_generic.new_formula',
1659 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
1660 --
1661 -- Block definitions.
1662 --
1663 pay_mgb_ins.ins
1664 (p_magnetic_block_id => L_BLOCK1
1665 ,p_block_name => 'TRANSMITTER'
1666 ,p_main_block_flag => 'Y'
1667 ,p_report_format => L_REPORT_FORMAT
1668 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
1669 ,p_no_column_returned => 12
1670 ,p_validate => false);
1671 --
1672 pay_mgb_ins.ins
1673 (p_magnetic_block_id => L_BLOCK2
1674 ,p_block_name => 'EMPLOYER'
1675 ,p_main_block_flag => 'N'
1676 ,p_report_format => L_REPORT_FORMAT
1677 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
1678 ,p_no_column_returned => 12
1679 ,p_validate => false);
1680 --
1681 pay_mgb_ins.ins
1682 (p_magnetic_block_id => L_BLOCK3
1683 ,p_block_name => 'EMPLOYEE'
1684 ,p_main_block_flag => 'N'
1685 ,p_report_format => L_REPORT_FORMAT
1686 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employee'
1687 ,p_no_column_returned => 8
1688 ,p_validate => false);
1689 --
1690 pay_mgb_ins.ins
1691 (p_magnetic_block_id => L_BLOCK4
1692 ,p_block_name => 'HIGH_COMP'
1693 ,p_main_block_flag => 'N'
1694 ,p_report_format => L_REPORT_FORMAT
1695 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
1696 ,p_no_column_returned => 2
1697 ,p_validate => false);
1698 --
1699 -- Record definitions.
1700 --
1701
1702 pay_mgr_ins.ins
1703 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1704 'TIB4_TRANSMITTER')
1705 ,p_magnetic_block_id => L_BLOCK1
1706 ,p_next_block_id => NULL
1707 ,p_last_run_executed_mode => 'N'
1708 ,p_overflow_mode => 'N'
1709 ,p_sequence => 1
1710 ,p_frequency => NULL
1711 ,p_validate => false);
1712
1713 --
1714 pay_mgr_ins.ins
1715 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1716 'TIB4_BINFO')
1717 ,p_magnetic_block_id => L_BLOCK1
1718 ,p_next_block_id => L_BLOCK2
1719 ,p_last_run_executed_mode => 'N'
1720 ,p_overflow_mode => 'N'
1721 ,p_sequence => 2
1722 ,p_frequency => NULL
1723 ,p_validate => false);
1724 --
1725 pay_mgr_ins.ins
1726 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1727 'DUMMY_TIB4_FINAL')
1728 ,p_magnetic_block_id => L_BLOCK1
1729 ,p_next_block_id => L_BLOCK4
1730 ,p_last_run_executed_mode => 'N'
1731 ,p_overflow_mode => 'N'
1732 ,p_sequence => 3
1733 ,p_frequency => NULL
1734 ,p_validate => false);
1735 --
1736 pay_mgr_ins.ins
1737 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1738 'W2_HIGH_COMP')
1739 ,p_magnetic_block_id => L_BLOCK4
1740 ,p_next_block_id => NULL
1741 ,p_last_run_executed_mode => 'N'
1742 ,p_overflow_mode => 'N'
1743 ,p_sequence => 1
1744 ,p_frequency => NULL
1745 ,p_validate => false);
1746 --
1747 pay_mgr_ins.ins
1748 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1749 'TIB4_EMPLOYER')
1750 ,p_magnetic_block_id => L_BLOCK2
1751 ,p_next_block_id => L_BLOCK3
1752 ,p_last_run_executed_mode => 'N'
1753 ,p_overflow_mode => 'N'
1754 ,p_sequence => 1
1755 ,p_frequency => NULL
1756 ,p_validate => false);
1757 --
1758 pay_mgr_ins.ins
1759 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1760 'DUMMY_TIB4_TOTAL')
1761 ,p_magnetic_block_id => L_BLOCK2
1762 ,p_next_block_id => NULL
1763 ,p_last_run_executed_mode => 'N'
1764 ,p_overflow_mode => 'N'
1765 ,p_sequence => 2
1766 ,p_frequency => NULL
1767 ,p_validate => false);
1768 --
1769 pay_mgr_ins.ins
1770 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1771 'TIB4_DUMMY')
1772 ,p_magnetic_block_id => L_BLOCK3
1773 ,p_next_block_id => NULL
1774 ,p_last_run_executed_mode => 'N'
1775 ,p_overflow_mode => 'N'
1776 ,p_sequence => 1
1777 ,p_frequency => NULL
1778 ,p_validate => false);
1779 --
1780 pay_mgr_ins.ins
1781 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1782 'W2_TIB4_SUPPLEMENTAL')
1783 ,p_magnetic_block_id => L_BLOCK3
1784 ,p_next_block_id => NULL
1785 ,p_last_run_executed_mode => 'N'
1786 ,p_overflow_mode => 'N'
1787 ,p_sequence => 2
1788 ,p_frequency => NULL
1789 ,p_validate => false);
1790 --
1791 --------------------------------------------------------------------------
1792 -- Mississippi State W2 format --
1793 --------------------------------------------------------------------------
1794 --
1795 -- Set up report format to be maintained.
1796 --
1797 L_REPORT_FORMAT := 'MS_TIB4';
1798 --
1799 -- Clear down current format definition and its mappings.
1800 --
1801 delete from pay_magnetic_records MGR
1802 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
1803 from pay_magnetic_blocks MGB,
1804 pay_report_format_mappings_f RFM
1805 where MGB.report_format = RFM.report_format
1806 AND RFM.report_format = L_REPORT_FORMAT
1807 AND RFM.report_category = 'RT');
1808 --
1809 delete from pay_magnetic_blocks MGB
1810 where MGB.report_format in (
1811 SELECT report_format
1812 FROM pay_report_format_mappings_f
1813 WHERE report_format = L_REPORT_FORMAT
1814 AND report_category = 'RT');
1815 --
1816 delete from pay_report_format_mappings_f RFM
1817 where RFM.report_format = L_REPORT_FORMAT
1818 and RFM.report_category = 'RT';
1819 --
1820 -- Report to format definitions.
1821 --
1822 insert into pay_report_format_mappings_f
1823 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
1824 RANGE_CODE,
1825 ASSIGNMENT_ACTION_CODE,
1826 INITIALIZATION_CODE,
1827 ARCHIVE_CODE,
1828 MAGNETIC_CODE,
1829 REPORT_FORMAT, EFFECTIVE_START_DATE,
1830 EFFECTIVE_END_DATE )
1831 values ('W2' , 'MS', 'RT',
1832 'pay_us_magw2_reporting.range_cursor',
1833 'pay_us_magw2_reporting.create_assignment_act',
1834 NULL,
1835 NULL,
1836 'pay_magtape_generic.new_formula',
1837 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
1838 --
1839 -- Block definitions.
1840 --
1841 pay_mgb_ins.ins
1842 (p_magnetic_block_id => L_BLOCK1
1843 ,p_block_name => 'TRANSMITTER'
1844 ,p_main_block_flag => 'Y'
1845 ,p_report_format => L_REPORT_FORMAT
1846 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
1847 ,p_no_column_returned => 12
1848 ,p_validate => false);
1849 --
1850 pay_mgb_ins.ins
1851 (p_magnetic_block_id => L_BLOCK2
1852 ,p_block_name => 'EMPLOYER'
1853 ,p_main_block_flag => 'N'
1854 ,p_report_format => L_REPORT_FORMAT
1855 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
1856 ,p_no_column_returned => 12
1857 ,p_validate => false);
1858 --
1859 pay_mgb_ins.ins
1860 (p_magnetic_block_id => L_BLOCK3
1861 ,p_block_name => 'EMPLOYEE'
1862 ,p_main_block_flag => 'N'
1863 ,p_report_format => L_REPORT_FORMAT
1864 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employee'
1865 ,p_no_column_returned => 8
1866 ,p_validate => false);
1867 --
1868 pay_mgb_ins.ins
1869 (p_magnetic_block_id => L_BLOCK4
1870 ,p_block_name => 'HIGH_COMP'
1871 ,p_main_block_flag => 'N'
1872 ,p_report_format => L_REPORT_FORMAT
1873 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
1874 ,p_no_column_returned => 2
1875 ,p_validate => false);
1876 --
1877 -- Record definitions.
1878 --
1879
1880 pay_mgr_ins.ins
1881 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1882 'TIB4_TRANSMITTER')
1883 ,p_magnetic_block_id => L_BLOCK1
1884 ,p_next_block_id => NULL
1885 ,p_last_run_executed_mode => 'N'
1886 ,p_overflow_mode => 'N'
1887 ,p_sequence => 1
1888 ,p_frequency => NULL
1889 ,p_validate => false);
1890
1891 --
1892 pay_mgr_ins.ins
1893 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1894 'TIB4_BINFO')
1895 ,p_magnetic_block_id => L_BLOCK1
1896 ,p_next_block_id => L_BLOCK2
1897 ,p_last_run_executed_mode => 'N'
1898 ,p_overflow_mode => 'N'
1899 ,p_sequence => 2
1900 ,p_frequency => NULL
1901 ,p_validate => false);
1902 --
1903 pay_mgr_ins.ins
1904 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1905 'TIB4_FINAL')
1906 ,p_magnetic_block_id => L_BLOCK1
1907 ,p_next_block_id => L_BLOCK4
1908 ,p_last_run_executed_mode => 'N'
1909 ,p_overflow_mode => 'N'
1910 ,p_sequence => 3
1911 ,p_frequency => NULL
1912 ,p_validate => false);
1913 --
1914 pay_mgr_ins.ins
1915 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1916 'W2_HIGH_COMP')
1917 ,p_magnetic_block_id => L_BLOCK4
1918 ,p_next_block_id => NULL
1919 ,p_last_run_executed_mode => 'N'
1920 ,p_overflow_mode => 'N'
1921 ,p_sequence => 1
1922 ,p_frequency => NULL
1923 ,p_validate => false);
1924 --
1925 pay_mgr_ins.ins
1926 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1927 'TIB4_EMPLOYER')
1928 ,p_magnetic_block_id => L_BLOCK2
1929 ,p_next_block_id => L_BLOCK3
1930 ,p_last_run_executed_mode => 'N'
1931 ,p_overflow_mode => 'N'
1932 ,p_sequence => 1
1933 ,p_frequency => NULL
1934 ,p_validate => false);
1935 --
1936 pay_mgr_ins.ins
1937 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1938 'TIB4_INTERMEDIATE_TOTAL')
1939 ,p_magnetic_block_id => L_BLOCK2
1940 ,p_next_block_id => NULL
1941 ,p_last_run_executed_mode => 'N'
1942 ,p_overflow_mode => 'N'
1943 ,p_sequence => 2
1944 ,p_frequency => NULL
1945 ,p_validate => false);
1946 --
1947 pay_mgr_ins.ins
1948 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1949 'TIB4_TOTAL')
1950 ,p_magnetic_block_id => L_BLOCK2
1951 ,p_next_block_id => NULL
1952 ,p_last_run_executed_mode => 'N'
1953 ,p_overflow_mode => 'N'
1954 ,p_sequence => 3
1955 ,p_frequency => NULL
1956 ,p_validate => false);
1957 --
1958 pay_mgr_ins.ins
1959 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1960 'TIB4_DUMMY')
1961 ,p_magnetic_block_id => L_BLOCK3
1962 ,p_next_block_id => NULL
1963 ,p_last_run_executed_mode => 'N'
1964 ,p_overflow_mode => 'N'
1965 ,p_sequence => 1
1966 ,p_frequency => NULL
1967 ,p_validate => false);
1968 --
1969 pay_mgr_ins.ins
1970 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1971 'TIB4_EMPLOYEE')
1972 ,p_magnetic_block_id => L_BLOCK3
1973 ,p_next_block_id => NULL
1974 ,p_last_run_executed_mode => 'N'
1975 ,p_overflow_mode => 'R'
1976 ,p_sequence => 2
1977 ,p_frequency => NULL
1978 ,p_validate => false);
1979 --
1980 pay_mgr_ins.ins
1981 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1982 'W2_TIB4_SUPPLEMENTAL')
1983 ,p_magnetic_block_id => L_BLOCK3
1984 ,p_next_block_id => NULL
1985 ,p_last_run_executed_mode => 'N'
1986 ,p_overflow_mode => 'N'
1987 ,p_sequence => 3
1988 ,p_frequency => NULL
1989 ,p_validate => false);
1990 --
1991 pay_mgr_ins.ins
1992 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
1993 'TIB4_INTERMEDIATE_TOTAL')
1994 ,p_magnetic_block_id => L_BLOCK3
1995 ,p_next_block_id => NULL
1996 ,p_last_run_executed_mode => 'N'
1997 ,p_overflow_mode => 'N'
1998 ,p_sequence => 4
1999 ,p_frequency => 41
2000 ,p_validate => false);
2001
2002 --
2003 --------------------------------------------------------------------------
2004 -- Indiana State W2 format --
2005 --------------------------------------------------------------------------
2006 --
2007 -- Set up report format to be maintained.
2008 --
2009 L_REPORT_FORMAT := 'IN_TIB4';
2010 --
2011 -- Clear down current format definition and its mappings.
2012 --
2013 delete from pay_magnetic_records MGR
2014 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
2015 from pay_magnetic_blocks MGB,
2016 pay_report_format_mappings_f RFM
2017 where MGB.report_format = RFM.report_format
2018 AND RFM.report_format = L_REPORT_FORMAT
2019 AND RFM.report_category = 'RT');
2020 --
2021 delete from pay_magnetic_blocks MGB
2022 where MGB.report_format in (
2023 SELECT report_format
2024 FROM pay_report_format_mappings_f
2025 WHERE report_format = L_REPORT_FORMAT
2026 AND report_category = 'RT');
2027 --
2028 delete from pay_report_format_mappings_f RFM
2029 where RFM.report_format = L_REPORT_FORMAT
2030 and RFM.report_category = 'RT';
2031 --
2032 -- Report to format definitions.
2033 --
2034 insert into pay_report_format_mappings_f
2035 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
2036 RANGE_CODE,
2037 ASSIGNMENT_ACTION_CODE,
2038 INITIALIZATION_CODE,
2039 ARCHIVE_CODE,
2040 MAGNETIC_CODE,
2041 REPORT_FORMAT, EFFECTIVE_START_DATE,
2042 EFFECTIVE_END_DATE )
2043 values ('W2' , 'IN', 'RT',
2044 'pay_us_magw2_reporting.range_cursor',
2045 'pay_us_magw2_reporting.create_assignment_act',
2046 NULL,
2047 NULL,
2048 'pay_magtape_generic.new_formula',
2049 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
2050 --
2051 -- Block definitions.
2052 --
2053 pay_mgb_ins.ins
2054 (p_magnetic_block_id => L_BLOCK1
2055 ,p_block_name => 'TRANSMITTER'
2056 ,p_main_block_flag => 'Y'
2057 ,p_report_format => L_REPORT_FORMAT
2058 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
2059 ,p_no_column_returned => 12
2060 ,p_validate => false);
2061 --
2062 pay_mgb_ins.ins
2063 (p_magnetic_block_id => L_BLOCK2
2064 ,p_block_name => 'EMPLOYER'
2065 ,p_main_block_flag => 'N'
2066 ,p_report_format => L_REPORT_FORMAT
2067 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
2068 ,p_no_column_returned => 12
2069 ,p_validate => false);
2070 --
2071 pay_mgb_ins.ins
2072 (p_magnetic_block_id => L_BLOCK3
2073 ,p_block_name => 'EMPLOYEE'
2074 ,p_main_block_flag => 'N'
2075 ,p_report_format => L_REPORT_FORMAT
2076 ,p_cursor_name => 'pay_us_magw2_reporting.oh_in_employee'
2077 ,p_no_column_returned => 8
2078 ,p_validate => false);
2079
2080 -- Add
2081 pay_mgb_ins.ins
2082 (p_magnetic_block_id => L_BLOCK4
2083 ,p_block_name => 'IN_SUPP'
2084 ,p_main_block_flag => 'N'
2085 ,p_report_format => L_REPORT_FORMAT
2086 ,p_cursor_name => 'pay_us_magw2_reporting.instw2_supp'
2087 ,p_no_column_returned => 12
2088 ,p_validate => false);
2089 -- Add
2090 --
2091 pay_mgb_ins.ins
2092 (p_magnetic_block_id => L_BLOCK5
2093 ,p_block_name => 'HIGH_COMP'
2094 ,p_main_block_flag => 'N'
2095 ,p_report_format => L_REPORT_FORMAT
2096 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
2097 ,p_no_column_returned => 2
2098 ,p_validate => false);
2099
2100 --
2101 -- Record definitions.
2102 --
2103
2104 pay_mgr_ins.ins
2105 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2106 'TIB4_TRANSMITTER')
2107 ,p_magnetic_block_id => L_BLOCK1
2108 ,p_next_block_id => NULL
2109 ,p_last_run_executed_mode => 'N'
2110 ,p_overflow_mode => 'N'
2111 ,p_sequence => 1
2112 ,p_frequency => NULL
2113 ,p_validate => false);
2114
2115 --
2116 pay_mgr_ins.ins
2117 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2118 'TIB4_BINFO')
2119 ,p_magnetic_block_id => L_BLOCK1
2120 ,p_next_block_id => L_BLOCK2
2121 ,p_last_run_executed_mode => 'N'
2122 ,p_overflow_mode => 'N'
2123 ,p_sequence => 2
2124 ,p_frequency => NULL
2125 ,p_validate => false);
2126 --
2127 pay_mgr_ins.ins
2128 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2129 'TIB4_FINAL')
2130 ,p_magnetic_block_id => L_BLOCK1
2131 ,p_next_block_id => L_BLOCK5
2132 ,p_last_run_executed_mode => 'N'
2133 ,p_overflow_mode => 'N'
2134 ,p_sequence => 3
2135 ,p_frequency => NULL
2136 ,p_validate => false);
2137 --
2138 pay_mgr_ins.ins
2139 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2140 'W2_HIGH_COMP')
2141 ,p_magnetic_block_id => L_BLOCK5
2142 ,p_next_block_id => NULL
2143 ,p_last_run_executed_mode => 'N'
2144 ,p_overflow_mode => 'N'
2145 ,p_sequence => 1
2146 ,p_frequency => NULL
2147 ,p_validate => false);
2148 --
2149 pay_mgr_ins.ins
2150 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2151 'TIB4_EMPLOYER')
2152 ,p_magnetic_block_id => L_BLOCK2
2153 ,p_next_block_id => L_BLOCK3
2154 ,p_last_run_executed_mode => 'N'
2155 ,p_overflow_mode => 'N'
2156 ,p_sequence => 1
2157 ,p_frequency => NULL
2158 ,p_validate => false);
2159 --
2160 pay_mgr_ins.ins
2161 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2162 'TIB4_INTERMEDIATE_TOTAL')
2163 ,p_magnetic_block_id => L_BLOCK2
2164 ,p_next_block_id => NULL
2165 ,p_last_run_executed_mode => 'N'
2166 ,p_overflow_mode => 'N'
2167 ,p_sequence => 2
2168 ,p_frequency => NULL
2169 ,p_validate => false);
2170 --
2171 pay_mgr_ins.ins
2172 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2173 'TIB4_TOTAL')
2174 ,p_magnetic_block_id => L_BLOCK2
2175 ,p_next_block_id => NULL
2176 ,p_last_run_executed_mode => 'N'
2177 ,p_overflow_mode => 'N'
2178 ,p_sequence => 3
2179 ,p_frequency => NULL
2180 ,p_validate => false);
2181 --
2182 pay_mgr_ins.ins
2183 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2184 'TIB4_DUMMY')
2185 ,p_magnetic_block_id => L_BLOCK3
2186 ,p_next_block_id => NULL
2187 ,p_last_run_executed_mode => 'N'
2188 ,p_overflow_mode => 'N'
2189 ,p_sequence => 1
2190 ,p_frequency => NULL
2191 ,p_validate => false);
2192 --
2193 pay_mgr_ins.ins
2194 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2195 'TIB4_EMPLOYEE')
2196 ,p_magnetic_block_id => L_BLOCK3
2197 ,p_next_block_id => L_BLOCK4
2198 ,p_last_run_executed_mode => 'N'
2199 ,p_overflow_mode => 'N'
2200 ,p_sequence => 2
2201 ,p_frequency => NULL
2202 ,p_validate => false);
2203 --
2204 pay_mgr_ins.ins
2205 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2206 'W2_TIB4_SUPPLEMENTAL')
2207 ,p_magnetic_block_id => L_BLOCK4
2208 ,p_next_block_id => NULL
2209 ,p_last_run_executed_mode => 'N'
2210 ,p_overflow_mode => 'N'
2211 ,p_sequence => 1
2212 ,p_frequency => NULL
2213 ,p_validate => false);
2214 --
2215 pay_mgr_ins.ins
2216 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2217 'TIB4_INTERMEDIATE_TOTAL')
2218 ,p_magnetic_block_id => L_BLOCK3
2219 ,p_next_block_id => NULL
2220 ,p_last_run_executed_mode => 'N'
2221 ,p_overflow_mode => 'N'
2222 ,p_sequence => 3
2223 ,p_frequency => 41
2224 ,p_validate => false);
2225
2226 --
2227 --------------------------------------------------------------------------
2228 -- Connecticut State W2 format --
2229 --------------------------------------------------------------------------
2230 --
2231 -- Set up report format to be maintained.
2232 --
2233 L_REPORT_FORMAT := 'CT_TIB4';
2234 --
2235 -- Clear down current format definition and its mappings.
2236 --
2237 delete from pay_magnetic_records MGR
2238 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
2239 from pay_magnetic_blocks MGB,
2240 pay_report_format_mappings_f RFM
2241 where MGB.report_format = RFM.report_format
2242 AND RFM.report_format = L_REPORT_FORMAT
2243 AND RFM.report_category = 'RT');
2244 --
2245 delete from pay_magnetic_blocks MGB
2246 where MGB.report_format in (
2247 SELECT report_format
2248 FROM pay_report_format_mappings_f
2249 WHERE report_format = L_REPORT_FORMAT
2250 AND report_category = 'RT');
2251 --
2252 delete from pay_report_format_mappings_f RFM
2253 where RFM.report_format = L_REPORT_FORMAT
2254 and RFM.report_category = 'RT';
2255 --
2256 -- Report to format definitions.
2257 --
2258 insert into pay_report_format_mappings_f
2259 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
2260 RANGE_CODE,
2261 ASSIGNMENT_ACTION_CODE,
2262 INITIALIZATION_CODE,
2263 ARCHIVE_CODE,
2264 MAGNETIC_CODE,
2265 REPORT_FORMAT, EFFECTIVE_START_DATE,
2266 EFFECTIVE_END_DATE )
2267 values ('W2' , 'CT', 'RT',
2268 'pay_us_magw2_reporting.range_cursor',
2269 'pay_us_magw2_reporting.create_assignment_act',
2270 NULL,
2271 NULL,
2272 'pay_magtape_generic.new_formula',
2273 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
2274
2275 --
2276 -- Block definitions.
2277 --
2278 pay_mgb_ins.ins
2279 (p_magnetic_block_id => L_BLOCK1
2280 ,p_block_name => 'TRANSMITTER'
2281 ,p_main_block_flag => 'Y'
2282 ,p_report_format => L_REPORT_FORMAT
2283 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
2284 ,p_no_column_returned => 12
2285 ,p_validate => false);
2286 --
2287 pay_mgb_ins.ins
2288 (p_magnetic_block_id => L_BLOCK2
2289 ,p_block_name => 'EMPLOYER'
2290 ,p_main_block_flag => 'N'
2291 ,p_report_format => L_REPORT_FORMAT
2292 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
2293 ,p_no_column_returned => 12
2294 ,p_validate => false);
2295 --
2296 pay_mgb_ins.ins
2297 (p_magnetic_block_id => L_BLOCK3
2298 ,p_block_name => 'EMPLOYEE'
2299 ,p_main_block_flag => 'N'
2300 ,p_report_format => L_REPORT_FORMAT
2301 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employee'
2302 ,p_no_column_returned => 8
2303 ,p_validate => false);
2304 --
2305 pay_mgb_ins.ins
2306 (p_magnetic_block_id => L_BLOCK4
2307 ,p_block_name => 'HIGH_COMP'
2308 ,p_main_block_flag => 'N'
2309 ,p_report_format => L_REPORT_FORMAT
2310 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
2311 ,p_no_column_returned => 2
2312 ,p_validate => false);
2313 --
2314 -- Record definitions.
2315 --
2316
2317 pay_mgr_ins.ins
2318 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2319 'TIB4_TRANSMITTER')
2320 ,p_magnetic_block_id => L_BLOCK1
2321 ,p_next_block_id => NULL
2322 ,p_last_run_executed_mode => 'N'
2323 ,p_overflow_mode => 'N'
2324 ,p_sequence => 1
2325 ,p_frequency => NULL
2326 ,p_validate => false);
2327
2328 --
2329 pay_mgr_ins.ins
2330 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2331 'TIB4_BINFO')
2332 ,p_magnetic_block_id => L_BLOCK1
2333 ,p_next_block_id => L_BLOCK2
2334 ,p_last_run_executed_mode => 'N'
2335 ,p_overflow_mode => 'N'
2336 ,p_sequence => 2
2337 ,p_frequency => NULL
2338 ,p_validate => false);
2339 --
2340 pay_mgr_ins.ins
2341 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2342 'TIB4_FINAL')
2343 ,p_magnetic_block_id => L_BLOCK1
2344 ,p_next_block_id => L_BLOCK4
2345 ,p_last_run_executed_mode => 'N'
2346 ,p_overflow_mode => 'N'
2347 ,p_sequence => 3
2348 ,p_frequency => NULL
2349 ,p_validate => false);
2350 --
2351 pay_mgr_ins.ins
2352 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2353 'W2_HIGH_COMP')
2354 ,p_magnetic_block_id => L_BLOCK4
2355 ,p_next_block_id => NULL
2356 ,p_last_run_executed_mode => 'N'
2357 ,p_overflow_mode => 'N'
2358 ,p_sequence => 1
2359 ,p_frequency => NULL
2360 ,p_validate => false);
2361 --
2362 pay_mgr_ins.ins
2363 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2364 'TIB4_EMPLOYER')
2365 ,p_magnetic_block_id => L_BLOCK2
2366 ,p_next_block_id => L_BLOCK3
2367 ,p_last_run_executed_mode => 'N'
2368 ,p_overflow_mode => 'N'
2369 ,p_sequence => 1
2370 ,p_frequency => NULL
2371 ,p_validate => false);
2372 --
2373 pay_mgr_ins.ins
2374 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2375 'TIB4_TOTAL')
2376 ,p_magnetic_block_id => L_BLOCK2
2377 ,p_next_block_id => NULL
2378 ,p_last_run_executed_mode => 'N'
2379 ,p_overflow_mode => 'N'
2380 ,p_sequence => 2
2381 ,p_frequency => NULL
2382 ,p_validate => false);
2383 --
2384 pay_mgr_ins.ins
2385 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2386 'TIB4_DUMMY')
2387 ,p_magnetic_block_id => L_BLOCK3
2388 ,p_next_block_id => NULL
2389 ,p_last_run_executed_mode => 'N'
2390 ,p_overflow_mode => 'N'
2391 ,p_sequence => 1
2392 ,p_frequency => NULL
2393 ,p_validate => false);
2394 --
2395 pay_mgr_ins.ins
2396 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2397 'TIB4_EMPLOYEE')
2398 ,p_magnetic_block_id => L_BLOCK3
2399 ,p_next_block_id => NULL
2400 ,p_last_run_executed_mode => 'N'
2401 ,p_overflow_mode => 'R'
2402 ,p_sequence => 2
2403 ,p_frequency => NULL
2404 ,p_validate => false);
2405 --
2406 pay_mgr_ins.ins
2407 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2408 'W2_TIB4_SUPPLEMENTAL')
2409 ,p_magnetic_block_id => L_BLOCK3
2410 ,p_next_block_id => NULL
2411 ,p_last_run_executed_mode => 'N'
2412 ,p_overflow_mode => 'N'
2413 ,p_sequence => 3
2414 ,p_frequency => NULL
2415 ,p_validate => false);
2416
2417 --
2418 --------------------------------------------------------------------------
2419 -- Ohio State W2 format --
2420 --------------------------------------------------------------------------
2421 --
2422 -- Set up report format to be maintained.
2423 --
2424 L_REPORT_FORMAT := 'OH_TIB4';
2425 --
2426 -- Clear down current format definition and its mappings.
2427 --
2428 delete from pay_magnetic_records MGR
2429 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
2430 from pay_magnetic_blocks MGB,
2431 pay_report_format_mappings_f RFM
2432 where MGB.report_format = RFM.report_format
2433 AND RFM.report_format = L_REPORT_FORMAT
2434 AND RFM.report_category = 'RT');
2435 --
2436 delete from pay_magnetic_blocks MGB
2437 where MGB.report_format in (
2438 SELECT report_format
2439 FROM pay_report_format_mappings_f
2440 WHERE report_format = L_REPORT_FORMAT
2441 AND report_category = 'RT');
2442 --
2443 delete from pay_report_format_mappings_f RFM
2444 where RFM.report_format = L_REPORT_FORMAT
2445 and RFM.report_category = 'RT';
2446 --
2447 -- Report to format definitions.
2448 --
2449 insert into pay_report_format_mappings_f
2450 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
2451 RANGE_CODE,
2452 ASSIGNMENT_ACTION_CODE,
2453 INITIALIZATION_CODE,
2454 ARCHIVE_CODE,
2455 MAGNETIC_CODE,
2456 REPORT_FORMAT, EFFECTIVE_START_DATE,
2457 EFFECTIVE_END_DATE )
2458 values ('W2' , 'OH', 'RT',
2459 'pay_us_magw2_reporting.range_cursor',
2460 'pay_us_magw2_reporting.create_assignment_act',
2461 NULL,
2462 NULL,
2463 'pay_magtape_generic.new_formula',
2464 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
2465
2466 --
2467 -- Block definitions.
2468 --
2469 pay_mgb_ins.ins
2470 (p_magnetic_block_id => L_BLOCK1
2471 ,p_block_name => 'TRANSMITTER'
2472 ,p_main_block_flag => 'Y'
2473 ,p_report_format => L_REPORT_FORMAT
2474 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
2475 ,p_no_column_returned => 12
2476 ,p_validate => false);
2477 --
2478 pay_mgb_ins.ins
2479 (p_magnetic_block_id => L_BLOCK2
2480 ,p_block_name => 'EMPLOYER'
2481 ,p_main_block_flag => 'N'
2482 ,p_report_format => L_REPORT_FORMAT
2483 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
2484 ,p_no_column_returned => 12
2485 ,p_validate => false);
2486 --
2487 pay_mgb_ins.ins
2488 (p_magnetic_block_id => L_BLOCK3
2489 ,p_block_name => 'EMPLOYEE'
2490 ,p_main_block_flag => 'N'
2491 ,p_report_format => L_REPORT_FORMAT
2492 ,p_cursor_name => 'pay_us_magw2_reporting.oh_in_employee'
2493 ,p_no_column_returned => 8
2494 ,p_validate => false);
2495
2496 -- Add
2497 pay_mgb_ins.ins
2498 (p_magnetic_block_id => L_BLOCK4
2499 ,p_block_name => 'OHIO_SUPP'
2500 ,p_main_block_flag => 'N'
2501 ,p_report_format => L_REPORT_FORMAT
2502 ,p_cursor_name => 'pay_us_magw2_reporting.ohstw2_supp'
2503 ,p_no_column_returned => 10
2504 ,p_validate => false);
2505 -- Add
2506 --
2507 pay_mgb_ins.ins
2508 (p_magnetic_block_id => L_BLOCK5
2509 ,p_block_name => 'HIGH_COMP'
2510 ,p_main_block_flag => 'N'
2511 ,p_report_format => L_REPORT_FORMAT
2512 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
2513 ,p_no_column_returned => 2
2514 ,p_validate => false);
2515
2516 --
2517 -- Record definitions.
2518 --
2519
2520 pay_mgr_ins.ins
2521 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2522 'TIB4_TRANSMITTER')
2523 ,p_magnetic_block_id => L_BLOCK1
2524 ,p_next_block_id => NULL
2525 ,p_last_run_executed_mode => 'N'
2526 ,p_overflow_mode => 'N'
2527 ,p_sequence => 1
2528 ,p_frequency => NULL
2529 ,p_validate => false);
2530
2531 --
2532 pay_mgr_ins.ins
2533 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2534 'TIB4_BINFO')
2535 ,p_magnetic_block_id => L_BLOCK1
2536 ,p_next_block_id => L_BLOCK2
2537 ,p_last_run_executed_mode => 'N'
2538 ,p_overflow_mode => 'N'
2539 ,p_sequence => 2
2540 ,p_frequency => NULL
2541 ,p_validate => false);
2542 --
2543 pay_mgr_ins.ins
2544 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2545 'TIB4_FINAL')
2546 ,p_magnetic_block_id => L_BLOCK1
2547 ,p_next_block_id => L_BLOCK5
2548 ,p_last_run_executed_mode => 'N'
2549 ,p_overflow_mode => 'N'
2550 ,p_sequence => 3
2551 ,p_frequency => NULL
2552 ,p_validate => false);
2553 --
2554 pay_mgr_ins.ins
2555 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2556 'W2_HIGH_COMP')
2557 ,p_magnetic_block_id => L_BLOCK5
2558 ,p_next_block_id => NULL
2559 ,p_last_run_executed_mode => 'N'
2560 ,p_overflow_mode => 'N'
2561 ,p_sequence => 1
2562 ,p_frequency => NULL
2563 ,p_validate => false);
2564 --
2565 pay_mgr_ins.ins
2566 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2567 'TIB4_EMPLOYER')
2568 ,p_magnetic_block_id => L_BLOCK2
2569 ,p_next_block_id => L_BLOCK3
2570 ,p_last_run_executed_mode => 'N'
2571 ,p_overflow_mode => 'N'
2572 ,p_sequence => 1
2573 ,p_frequency => NULL
2574 ,p_validate => false);
2575 --
2576 pay_mgr_ins.ins
2577 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2578 'TIB4_TOTAL')
2579 ,p_magnetic_block_id => L_BLOCK2
2580 ,p_next_block_id => NULL
2581 ,p_last_run_executed_mode => 'N'
2582 ,p_overflow_mode => 'N'
2583 ,p_sequence => 2
2584 ,p_frequency => NULL
2585 ,p_validate => false);
2586 --
2587 pay_mgr_ins.ins
2588 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2589 'TIB4_DUMMY')
2590 ,p_magnetic_block_id => L_BLOCK3
2591 ,p_next_block_id => NULL
2592 ,p_last_run_executed_mode => 'N'
2593 ,p_overflow_mode => 'N'
2594 ,p_sequence => 1
2595 ,p_frequency => NULL
2596 ,p_validate => false);
2597 --
2598 -- Add
2599 pay_mgr_ins.ins
2600 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2601 'TIB4_EMPLOYEE')
2602 ,p_magnetic_block_id => L_BLOCK3
2603 ,p_next_block_id => L_BLOCK4
2604 ,p_last_run_executed_mode => 'N'
2605 ,p_overflow_mode => 'N'
2606 ,p_sequence => 2
2607 ,p_frequency => NULL
2608 ,p_validate => false);
2609 -- Add
2610
2611 pay_mgr_ins.ins
2612 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2613 'W2_TIB4_SUPPLEMENTAL')
2614 ,p_magnetic_block_id => L_BLOCK4
2615 ,p_next_block_id => NULL
2616 ,p_last_run_executed_mode => 'N'
2617 ,p_overflow_mode => 'N'
2618 ,p_sequence => 1
2619 ,p_frequency => NULL
2620 ,p_validate => false);
2621
2622 --
2623 --------------------------------------------------------------------------
2624 -- North Carolina State W2 format --
2625 --------------------------------------------------------------------------
2626 --
2627 -- Set up report format to be maintained.
2628 --
2629 L_REPORT_FORMAT := 'NC_TIB4';
2630 --
2631 -- Clear down current format definition and its mappings.
2632 --
2633 delete from pay_magnetic_records MGR
2634 where MGR.magnetic_block_id in (select MGB.magnetic_block_id
2635 from pay_magnetic_blocks MGB,
2636 pay_report_format_mappings_f RFM
2637 where MGB.report_format = RFM.report_format
2638 AND RFM.report_format = L_REPORT_FORMAT
2639 AND RFM.report_category = 'RT');
2640 --
2641 delete from pay_magnetic_blocks MGB
2642 where MGB.report_format in (
2643 SELECT report_format
2644 FROM pay_report_format_mappings_f
2645 WHERE report_format = L_REPORT_FORMAT
2646 AND report_category = 'RT');
2647 --
2648 delete from pay_report_format_mappings_f RFM
2649 where RFM.report_format = L_REPORT_FORMAT
2650 and RFM.report_category = 'RT';
2651 --
2652 -- Report to format definitions.
2653 --
2654 insert into pay_report_format_mappings_f
2655 ( REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY,
2656 RANGE_CODE,
2657 ASSIGNMENT_ACTION_CODE,
2658 INITIALIZATION_CODE,
2659 ARCHIVE_CODE,
2660 MAGNETIC_CODE,
2661 REPORT_FORMAT, EFFECTIVE_START_DATE,
2662 EFFECTIVE_END_DATE )
2663 values ('W2' , 'NC', 'RT',
2664 'pay_us_magw2_reporting.range_cursor',
2665 'pay_us_magw2_reporting.create_assignment_act',
2666 NULL,
2667 NULL,
2668 'pay_magtape_generic.new_formula',
2669 L_REPORT_FORMAT, START_OF_TIME, END_OF_TIME);
2670
2671 --
2672 -- Block definitions.
2673 --
2674 pay_mgb_ins.ins
2675 (p_magnetic_block_id => L_BLOCK1
2676 ,p_block_name => 'TRANSMITTER'
2677 ,p_main_block_flag => 'Y'
2678 ,p_report_format => L_REPORT_FORMAT
2679 ,p_cursor_name => 'pay_us_magw2_reporting.magw2_transmitter'
2680 ,p_no_column_returned => 12
2681 ,p_validate => false);
2682 --
2683 pay_mgb_ins.ins
2684 (p_magnetic_block_id => L_BLOCK2
2685 ,p_block_name => 'EMPLOYER'
2686 ,p_main_block_flag => 'N'
2687 ,p_report_format => L_REPORT_FORMAT
2688 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employer'
2689 ,p_no_column_returned => 12
2690 ,p_validate => false);
2691 --
2692 pay_mgb_ins.ins
2693 (p_magnetic_block_id => L_BLOCK3
2694 ,p_block_name => 'EMPLOYEE'
2695 ,p_main_block_flag => 'N'
2696 ,p_report_format => L_REPORT_FORMAT
2697 ,p_cursor_name => 'pay_us_magw2_reporting.st_magw2_employee'
2698 ,p_no_column_returned => 8
2699 ,p_validate => false);
2700 --
2701 pay_mgb_ins.ins
2702 (p_magnetic_block_id => L_BLOCK4
2703 ,p_block_name => 'HIGH_COMP'
2704 ,p_main_block_flag => 'N'
2705 ,p_report_format => L_REPORT_FORMAT
2706 ,p_cursor_name => 'pay_us_magw2_reporting.w2_high_comp'
2707 ,p_no_column_returned => 2
2708 ,p_validate => false);
2709 --
2710 -- Record definitions.
2711 --
2712
2713 pay_mgr_ins.ins
2714 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2715 'TIB4_TRANSMITTER')
2716 ,p_magnetic_block_id => L_BLOCK1
2717 ,p_next_block_id => L_BLOCK2
2718 ,p_last_run_executed_mode => 'N'
2719 ,p_overflow_mode => 'N'
2720 ,p_sequence => 1
2721 ,p_frequency => NULL
2722 ,p_validate => false);
2723
2724 --
2725 pay_mgr_ins.ins
2726 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2727 'TIB4_FINAL')
2728 ,p_magnetic_block_id => L_BLOCK1
2729 ,p_next_block_id => L_BLOCK4
2730 ,p_last_run_executed_mode => 'N'
2731 ,p_overflow_mode => 'N'
2732 ,p_sequence => 2
2733 ,p_frequency => NULL
2734 ,p_validate => false);
2735 --
2736 pay_mgr_ins.ins
2737 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2738 'W2_HIGH_COMP')
2739 ,p_magnetic_block_id => L_BLOCK4
2740 ,p_next_block_id => NULL
2741 ,p_last_run_executed_mode => 'N'
2742 ,p_overflow_mode => 'N'
2743 ,p_sequence => 1
2744 ,p_frequency => NULL
2745 ,p_validate => false);
2746 --
2747 pay_mgr_ins.ins
2748 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2749 'TIB4_EMPLOYER')
2750 ,p_magnetic_block_id => L_BLOCK2
2751 ,p_next_block_id => L_BLOCK3
2752 ,p_last_run_executed_mode => 'N'
2753 ,p_overflow_mode => 'N'
2754 ,p_sequence => 1
2755 ,p_frequency => NULL
2756 ,p_validate => false);
2757 --
2758 pay_mgr_ins.ins
2759 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2760 'TIB4_INTERMEDIATE_TOTAL')
2761 ,p_magnetic_block_id => L_BLOCK2
2762 ,p_next_block_id => NULL
2763 ,p_last_run_executed_mode => 'N'
2764 ,p_overflow_mode => 'N'
2765 ,p_sequence => 2
2766 ,p_frequency => NULL
2767 ,p_validate => false);
2768 --
2769 pay_mgr_ins.ins
2770 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2771 'TIB4_TOTAL')
2772 ,p_magnetic_block_id => L_BLOCK2
2773 ,p_next_block_id => NULL
2774 ,p_last_run_executed_mode => 'N'
2775 ,p_overflow_mode => 'N'
2776 ,p_sequence => 3
2777 ,p_frequency => NULL
2778 ,p_validate => false);
2779 --
2780 pay_mgr_ins.ins
2781 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2782 'TIB4_DUMMY')
2783 ,p_magnetic_block_id => L_BLOCK3
2784 ,p_next_block_id => NULL
2785 ,p_last_run_executed_mode => 'N'
2786 ,p_overflow_mode => 'N'
2787 ,p_sequence => 1
2788 ,p_frequency => NULL
2789 ,p_validate => false);
2790 --
2791 pay_mgr_ins.ins
2792 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2793 'NCTIB4_SUPPLEMENTAL')
2794 ,p_magnetic_block_id => L_BLOCK3
2795 ,p_next_block_id => NULL
2796 ,p_last_run_executed_mode => 'N'
2797 ,p_overflow_mode => 'N'
2798 ,p_sequence => 2
2799 ,p_frequency => NULL
2800 ,p_validate => false);
2801 --
2802 pay_mgr_ins.ins
2803 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2804 'TIB4_EMPLOYEE')
2805 ,p_magnetic_block_id => L_BLOCK3
2806 ,p_next_block_id => NULL
2807 ,p_last_run_executed_mode => 'N'
2808 ,p_overflow_mode => 'R'
2809 ,p_sequence => 3
2810 ,p_frequency => NULL
2811 ,p_validate => false);
2812 --
2813 pay_mgr_ins.ins
2814 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2815 'W2_TIB4_SUPPLEMENTAL')
2816 ,p_magnetic_block_id => L_BLOCK3
2817 ,p_next_block_id => NULL
2818 ,p_last_run_executed_mode => 'N'
2819 ,p_overflow_mode => 'N'
2820 ,p_sequence => 4
2821 ,p_frequency => NULL
2822 ,p_validate => false);
2823 --
2824 pay_mgr_ins.ins
2825 (p_formula_id => lookup_formula(START_OF_TIME, NULL, 'US',
2826 'TIB4_INTERMEDIATE_TOTAL')
2827 ,p_magnetic_block_id => L_BLOCK3
2828 ,p_next_block_id => NULL
2829 ,p_last_run_executed_mode => 'N'
2830 ,p_overflow_mode => 'N'
2831 ,p_sequence => 5
2832 ,p_frequency => 41
2833 ,p_validate => false);
2834
2835
2836 --
2837 -- Make the data permanent.
2838 --
2839 commit;
2840 --
2841 end setup;
2842 --
2843 end pyw2data;
2844