[Home] [Help]
PACKAGE BODY: APPS.PAY_1099R_DATA
Source
1 PACKAGE BODY pay_1099R_data AS
2 /* $Header: py1099rd.pkb 115.7 99/07/17 05:40:51 porting ship $ */
3 --
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_1099R_data
22
23 Description : Sets up the data to provide 1099R reporting.
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31 07-AUG-96 ATAYLOR 40.0 Created.
32 28-SEP-96 HEKIM 40.1 All format definitions stored in plsql
33 tables to be called in a loop.
34 8-OCT-96 HEKIM 40.2 Added block definition for states.
35 12-NOV-96 HEKIM 40.3 Cleaned up loop, and took out state blocks
36 which use k-record.
37 13-NOV-96 GPERRY 40.4 Added Exit Statement.
38 20-DEC-96 HEKIM 40.5 Added NY and MI formats.
39 31-JAN-97 HEKIM 40.6 Added transmitter info for WV
40 26-FEB-97 HEKIM 40.7 Changed state blocks to follow state cursor
41 06-MAR-97 HEKIM 40.8 NY now follows 1099R modified SQWL formula.
42 20-MAR-97 HEKIM 40.9 Changed K record structure for 1099R_FED
43 04-FEB-98 EKIM 40.10 Added Georgia for '1099R_State'report format.
44 26-MAY-98 NBRISTOW 40.14 Added report_category to
45 pay_report_format_mappings.
46 05-OCT-98 AHANDA 40.15 765557 Added code to make the 1099R
47 mag run on the Archiver Process
48 and NC format.
49 20-NOV-98 AHANDA 40.19 755093 Changed Report Definition for the EOY-III
50 patch.
51 04-DEC-98 AHANDA 40.20 Changed Report Definition for Kansas and Iowa
52 07-DEC-98 AHANDA 40.21 Changed script to create the 1099R_STATE report
53 defn. first and then create the 1099R_STATE_NFED.
54 07-DEC-98 AHANDA 40.22/110.4 Changed the frequency for
55 TIB4_INTERMEDIATE_TOTALS for North Carolina
56 17-DEC-98 AHANDA 40.23/110.5 Changed the format for South Carolina as
57 they added a K record.
58 16-JAN-98 AHANDA 40.25/110.6 Changed no no of columns returned
59 by magw2_transmitter to 12
60 27-JAN-99 AHANDA 40.26/110.7 808958 Modified the Report format for 1099R_STATE to
61 include NM, AR. Also changed the script to first
62 delete all the report formats and then insert.
63 --
64 08-MAR-99 MREID 110.8 845184 Changed dbms_output calls to use
65 hr_utility
66 /* */
67 ----------------------------------------------------------------------------------------
68 -- Name
69 -- setup
70 -- Purpose
71 -- Sets up structure of 1099R Federal and State reports for the generic
72 -- magnetic tape harness.
73 -- Arguments
74 -- None
75 -- Notes:
76 -- This file contains the following report definitions:
77 -- 1099R_FED : Federal 1099R format
78 -- 1099R_STATE : Federal 1099R format with variations, omits k-records
79 -- 1099R_WV : Custom 1099R format for West Virginia
80 -- 1099R_IND : 1099R format for Indiana
81 -- 1099R_MI : 1099R format for Michigan
82 -- 1099R_NY : 1099R format for New York
83 -- 1099R_NC : 1099R format for North Carolina
84 ----------------------------------------------------------------------------------------
85 /* */
86 --
87 PROCEDURE Setup IS
88 --
89 -- Define table structures to hold parameter details.
90 --
91 -- note: the parameter prefix 'lt' denotes a local table
92 -- 'li' denotes a local table index
93 --Report Definitions
94 lt_report_format char30_data_table;
95 lt_report_qualifier char30_data_table;
96 lt_desc char250_data_table;
97 --
98 -- Tables to account for multiple report qualifiers for each report format
99 lt_rptq_first numeric_data_table; --start index into lt_report_qualifier
100 lt_rptq_last numeric_data_table; --end index into lt_report_qualifier
101 --
102 --Block Definitions
103 --
104 lt_B_mag_block_id numeric_data_table;
105 -- Note: lt_B_mag_block_id holds the id's for the current format being processed
106 --
107 lt_B_block_name char30_data_table;
108 lt_B_cursor_name char250_data_table;
109 lt_B_no_column_ret numeric_data_table;
110 lt_B_validate boolean_data_table;
111 --
112 --Formula Definitions
113 --
114 lt_F_formula_name char250_data_table;
115 --
116 lt_F_mag_block_id numeric_data_table;
117 -- used to index into lt_B_mag_block_id
118 --
119 lt_F_next_block_id numeric_data_table;
120 lt_F_last_run_exec_mode char30_data_table;
121 lt_F_overflow_mode char30_data_table;
122 lt_F_sequence numeric_data_table;
123 lt_F_frequency numeric_data_table;
124 lt_F_validate boolean_data_table;
125 --
126 --
127 lt_F_total numeric_data_table; --# of record defs for a format
128 lt_B_total numeric_data_table; --# of block defs for a format
129 --
130 l_report_type VARCHAR2(10) := '1099R';
131 l_formula_id number; -- Holds the id of a formula.
132 l_main_block_flag VARCHAR2(10);
133 --
134
135 --
136 l_case_count number := 9; --number of formats
137 li_case number;
138 li_btab number := 1;
139 li_ftab number := 1;
140 l_f_id number;
141 l_mag_block_id number;
142 l_next_block_id number;
143 --
144 -- Note that l_message is used throughout this module to hold the
145 -- message which will be displayed if an exception is raised.
146 --
147 l_message VARCHAR2(200);
148 --
149 BEGIN
150 ----------------------------------------------------------------------------------------
151 --
152 --Note: Block 1 is always the starting block, and is the only block
153 -- with p_main_block_flag set to 'Y'.
154 -- Block 1 has information on one Payer that is nominated as the
155 -- "Transmitter" for each report. This block will also be used to maintain
156 -- report level totals defined in the "F" record.
157 --
158 /* */
159 ----------------------------------------------------------------------------------------
160 -- Federal 1099R format definition
161 ----------------------------------------------------------------------------------------
162 --
163 lt_B_total(1) := 4;
164 lt_F_total(1) := 6;
165 lt_report_format(1) := '1099R_FED';
166 lt_desc(1) :='1099R Federal ';
167 --
168 lt_rptq_first(1) := 1;
169 lt_rptq_last(1) := 1;
170 lt_report_qualifier(1) :='FED'; --Federal
171 --
172 -- 1099R_FED Block 1: Payer that is nominated as the "Transmitter"
173 --
174 lt_B_block_name(li_btab) := 'US_1099R_TRANSMITTER';
175 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_transmitter';
176 lt_B_no_column_ret(li_btab) := 10;
177 lt_B_validate(li_btab) := false;
178 --
179 -- 1099R_FED Block 2: Payer "A" Record.
180 --
181 li_btab := li_btab + 1;
182 lt_B_block_name(li_btab) := 'US_1099R_PAYER';
183 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.US_1099r_payer';
184 lt_B_no_column_ret(li_btab) := 6;
185 lt_B_validate(li_btab) := false;
186 --
187 -- 1099R_FED Block 3: Payee "B" Record.
188 --
189 li_btab := li_btab + 1;
190
191 lt_B_block_name(li_btab) := 'US_1099R_PAYEE';
192 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_payee';
193 lt_B_no_column_ret(li_btab) := 16;
194 lt_B_validate(li_btab) := false;
195 --
196 -- 1099R_FED Block 4: State Process "K" Record.
197 --
198 li_btab := li_btab + 1;
199
200 lt_B_block_name(li_btab) := 'US_1099R_STATE_PROCESS';
201 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_state_process';
202 lt_B_no_column_ret(li_btab) := 2;
203 lt_B_validate(li_btab) := false;
204 --
205 /* */
206 --
207 -- Record definitions. Describe sequence of records, hierarchy and the
208 -- structure of each record ( by formula ).
209 -- 1099R_FED Formula
210 --
211 --
212 -- 1099R_FED Formula to write "T" Record
213 --
214 lt_F_formula_name(li_ftab) := 'US_1099R_TRANSMITTER';
215 lt_F_mag_block_id(li_ftab) := 1; --block 1
216 lt_F_next_block_id(li_ftab) := 2; --block 2
217 lt_F_last_run_exec_mode(li_ftab) := 'N';
218 lt_F_overflow_mode(li_ftab) := 'N';
219 lt_F_sequence(li_ftab) := 1;
220 lt_F_frequency(li_ftab) := NULL;
221 lt_F_validate(li_ftab) := false;
222 --
223 -- 1099R_FED Formula to write "F" Record - End of transmission.
224 --
225 li_ftab := li_ftab + 1;
226 lt_F_formula_name(li_ftab) := 'US_1099R_FILE_TOTALS';
227 lt_F_mag_block_id(li_ftab) := 1; --block 1
228 lt_F_next_block_id(li_ftab) := NULL;
229 lt_F_last_run_exec_mode(li_ftab) := 'N';
230 lt_F_overflow_mode(li_ftab) := 'N';
231 lt_F_sequence(li_ftab) := 2;
232 lt_F_frequency(li_ftab) := NULL;
233 lt_F_validate(li_ftab) := false;
234 --
235 -- 1099R_FED Formula to write "A" Record.
236 --
237 li_ftab := li_ftab + 1;
238 lt_F_formula_name(li_ftab) := 'US_1099R_PAYER';
239 lt_F_mag_block_id(li_ftab) := 2; --block 2
240 lt_F_next_block_id(li_ftab) := 3; --block 3
241 lt_F_last_run_exec_mode(li_ftab) := 'N';
242 lt_F_overflow_mode(li_ftab) := 'N';
243 lt_F_sequence(li_ftab) := 1;
244 lt_F_frequency(li_ftab) := NULL;
245 lt_F_validate(li_ftab) := false;
246 --
247 -- 1099R_FED Formula to write "C" Record. End of payer control totals.
248 --
249 li_ftab := li_ftab + 1;
250 lt_F_formula_name(li_ftab) := 'US_1099R_PAYER_TOTALS';
251 lt_F_mag_block_id(li_ftab) := 2; --block 2
252 lt_F_next_block_id(li_ftab) := 4; --block 4
253 lt_F_last_run_exec_mode(li_ftab) := 'N';
254 lt_F_overflow_mode(li_ftab) := 'N';
255 lt_F_sequence(li_ftab) := 2;
256 lt_F_frequency(li_ftab) := NULL;
257 lt_F_validate(li_ftab) := false;
258 --
259 /* */
260 --
261 -- 1099R_FED Formula to write "B" Records. Payees.
262 --
263 li_ftab := li_ftab + 1;
264 lt_F_formula_name(li_ftab) := 'US_1099R_PAYEES';
265 lt_F_mag_block_id(li_ftab) := 3; --block 3
266 lt_F_next_block_id(li_ftab) := NULL;
267 lt_F_last_run_exec_mode(li_ftab) := 'N';
268 lt_F_overflow_mode(li_ftab) := 'N';
269 lt_F_sequence(li_ftab) := 1;
270 lt_F_frequency(li_ftab) := NULL;
271 lt_F_validate(li_ftab) := false;
272 --
273 -- 1099R_FED Formula to write "K" Record. State Totals
274 --
275 li_ftab := li_ftab + 1;
276 lt_F_formula_name(li_ftab) := 'US_1099R_STATE_TOTALS';
277 lt_F_mag_block_id(li_ftab) := 4; --block 4
278 lt_F_next_block_id(li_ftab) := NULL;
279 lt_F_last_run_exec_mode(li_ftab) := 'N';
280 lt_F_overflow_mode(li_ftab) := 'N';
281 lt_F_sequence(li_ftab) := 1;
282 lt_F_frequency(li_ftab) := NULL;
283 lt_F_validate(li_ftab) := false;
284
285 /* */
286 -- ---------------------------------------------------------------------
287 -- States which use New 1220, without slight modifications
288 -- ---------------------------------------------------------------------
289 lt_report_format(2) := '1099R_STATE';
290 lt_B_total(2) := 3;
291 lt_F_total(2) := 5;
292 lt_desc(2) :='--States which use 1220, with or without modifications';
293 lt_rptq_first(2) := 2;
294 lt_rptq_last(2) := 24;
295 --
296 lt_report_qualifier(2) := 'KS'; --Kansas
297 lt_report_qualifier(3) := 'IA'; --Iowa
298 lt_report_qualifier(4) := 'CT'; --Connecticut
299 lt_report_qualifier(5) := 'AZ'; --Arizona
300 lt_report_qualifier(6) := 'ME'; --Maine
301 lt_report_qualifier(7) := 'NE'; --Nebraska
302 lt_report_qualifier(8) := 'CA'; --California
303 lt_report_qualifier(9) := 'MD'; --Maryland
304 lt_report_qualifier(10) := 'DC'; --District of Columbia
308 lt_report_qualifier(14) := 'DE'; --Delaware
305 lt_report_qualifier(11) := 'MA'; --Massachusettes
306 lt_report_qualifier(12) := 'MS'; --Mississippi
307 lt_report_qualifier(13) := 'ID'; --Idaho
309 lt_report_qualifier(15) := 'MO'; --Missouri
310 lt_report_qualifier(16) := 'NJ'; --New Jersey
311 lt_report_qualifier(17) := 'ND'; --North Dakota
312 lt_report_qualifier(18) := 'OK'; --Oklahoma
313 lt_report_qualifier(19) := 'PA'; --Pennsylvania
314 lt_report_qualifier(20) := 'MN'; --Minnesota
315 lt_report_qualifier(21) := 'WI'; --Wisconsin
316 lt_report_qualifier(22) := 'GA'; --Georgia
317 lt_report_qualifier(23) := 'NM'; --New Mexico
318 lt_report_qualifier(24) := 'AR'; --Arkansas
319
320 --
321 -- 1099R_STATE Block 1: Payer that is nominated as the "Transmitter"
322 --
323 li_btab := li_btab + 1;
324 lt_B_block_name(li_btab) := 'US_1099R_TRANSMITTER';
325 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_transmitter';
326 lt_B_no_column_ret(li_btab) := 10;
327 lt_B_validate(li_btab) := false;
328 --
329 -- 1099R_STATE Block 2: Payer/Transmitter "A" Record.
330 --
331 li_btab := li_btab + 1;
332 lt_B_block_name(li_btab) := 'US_1099R_PAYER';
333 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_payer';
334 lt_B_no_column_ret(li_btab) := 6;
335 lt_B_validate(li_btab) := false;
336 --
337 -- 1099R_STATE Block 3: Payee "B" Record.
338 --
339 li_btab := li_btab + 1;
340 lt_B_block_name(li_btab) := 'US_1099R_PAYEE';
341 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.state_1099r_payee';
342 lt_B_no_column_ret(li_btab) := 16;
343 lt_B_validate(li_btab) := false;
344 --
345 -- Record definitions. Describe sequence of records, hierarchy and the
346 -- structure of each record ( by formula ).
347 -- 1099R_STATE Formula.
348 --
349 li_ftab := li_ftab + 1;
350 lt_F_formula_name(li_ftab) := 'US_1099R_TRANSMITTER';
351 lt_F_mag_block_id(li_ftab) := 1; --block 1
352 lt_F_next_block_id(li_ftab) := 2; --block 2
353 lt_F_last_run_exec_mode(li_ftab) := 'N';
354 lt_F_overflow_mode(li_ftab) := 'N';
355 lt_F_sequence(li_ftab) := 1;
356 lt_F_frequency(li_ftab) := NULL;
357 lt_F_validate(li_ftab) := false;
358 --
359 -- 1099R_STATE Formula to write "F" Record - End of transmission.
360 --
361 li_ftab := li_ftab + 1;
362 lt_F_formula_name(li_ftab) := 'US_1099R_FILE_TOTALS';
363 lt_F_mag_block_id(li_ftab) := 1; --block 1
364 lt_F_next_block_id(li_ftab) := NULL;
365 lt_F_last_run_exec_mode(li_ftab) := 'N';
366 lt_F_overflow_mode(li_ftab) := 'N';
367 lt_F_sequence(li_ftab) := 2;
368 lt_F_frequency(li_ftab) := NULL;
369 lt_F_validate(li_ftab) := false;
370 --
371 -- 1099R_STATE Formula to write "A" Record.
372 --
373 li_ftab := li_ftab + 1;
374 lt_F_formula_name(li_ftab) := 'US_1099R_PAYER';
375 lt_F_mag_block_id(li_ftab) := 2; --block 2
376 lt_F_next_block_id(li_ftab) := 3; --block 3
377 lt_F_last_run_exec_mode(li_ftab) := 'N';
378 lt_F_overflow_mode(li_ftab) := 'N';
379 lt_F_sequence(li_ftab) := 1;
380 lt_F_frequency(li_ftab) := NULL;
381 lt_F_validate(li_ftab) := false;
382 --
383 -- 1099R_STATE Formula to write "C" Record. End of payer control totals.
384 --
385 li_ftab := li_ftab + 1;
386 lt_F_formula_name(li_ftab) := 'US_1099R_PAYER_TOTALS';
387 lt_F_mag_block_id(li_ftab) := 2; --block 2
388 lt_F_next_block_id(li_ftab) := NULL;
389 lt_F_last_run_exec_mode(li_ftab) := 'N';
390 lt_F_overflow_mode(li_ftab) := 'N';
391 lt_F_sequence(li_ftab) := 2;
392 lt_F_frequency(li_ftab) := NULL;
393 lt_F_validate(li_ftab) := false;
394 --
395 -- 1099R_STATE Formula to write "B" Records. Payees---------------------------------------
396 --
397 li_ftab := li_ftab + 1;
398 lt_F_formula_name(li_ftab) := 'US_1099R_PAYEES';
399 lt_F_mag_block_id(li_ftab) := 3; --block 3
400 lt_F_next_block_id(li_ftab) := NULL;
401 lt_F_last_run_exec_mode(li_ftab) := 'N';
402 lt_F_overflow_mode(li_ftab) := 'N';
403 lt_F_sequence(li_ftab) := 1;
404 lt_F_frequency(li_ftab) := NULL;
405 lt_F_validate(li_ftab) := false;
406
407 /* */
408 -- ---------------------------------------------------------------------
409 -- South Carolina use 1220 with or without slight modifications
410 -- but they have a K record if SIT > 0
411 -- ---------------------------------------------------------------------
412 lt_report_format(3) := '1099R_SC';
413 lt_B_total(3) := 4;
414 lt_F_total(3) := 6;
415 lt_desc(3) :='--South Carolina uses 1220, with modifications';
416 lt_rptq_first(3) := 25;
417 lt_rptq_last(3) := 25;
418 lt_report_qualifier(25) := 'SC'; --South Carolina
419 --
420 -- 1099R_SC Block 1: Payer that is nominated as the "Transmitter"
421 --
422 li_btab := li_btab + 1;
423 lt_B_block_name(li_btab) := 'US_1099R_TRANSMITTER';
424 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_transmitter';
425 lt_B_no_column_ret(li_btab) := 10;
426 lt_B_validate(li_btab) := false;
427 --
428 -- 1099R_SC Block 2: Payer/Transmitter "A" Record.
429 --
430 li_btab := li_btab + 1;
431 lt_B_block_name(li_btab) := 'US_1099R_PAYER';
432 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_payer';
433 lt_B_no_column_ret(li_btab) := 6;
434 lt_B_validate(li_btab) := false;
435 --
436 -- 1099R_SC Block 3: Payee "B" Record.
437 --
441 lt_B_no_column_ret(li_btab) := 16;
438 li_btab := li_btab + 1;
439 lt_B_block_name(li_btab) := 'US_1099R_PAYEE';
440 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.state_1099r_payee';
442 lt_B_validate(li_btab) := false;
443 --
444 -- 1099R_SC Block 4: State Process "K" Record.
445 --
446 li_btab := li_btab + 1;
447
448 lt_B_block_name(li_btab) := 'US_1099R_STATE_PROCESS';
449 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_state_process';
450 lt_B_no_column_ret(li_btab) := 2;
451 lt_B_validate(li_btab) := false;
452
453 --
454 -- Record definitions. Describe sequence of records, hierarchy and the
455 -- structure of each record ( by formula ).
456 -- 1099R_SC Formula.
457 --
458 li_ftab := li_ftab + 1;
459 lt_F_formula_name(li_ftab) := 'US_1099R_TRANSMITTER';
460 lt_F_mag_block_id(li_ftab) := 1; --block 1
461 lt_F_next_block_id(li_ftab) := 2; --block 2
462 lt_F_last_run_exec_mode(li_ftab) := 'N';
463 lt_F_overflow_mode(li_ftab) := 'N';
464 lt_F_sequence(li_ftab) := 1;
465 lt_F_frequency(li_ftab) := NULL;
466 lt_F_validate(li_ftab) := false;
467 --
468 -- 1099R_SC Formula to write "F" Record - End of transmission.
469 --
470 li_ftab := li_ftab + 1;
471 lt_F_formula_name(li_ftab) := 'US_1099R_FILE_TOTALS';
472 lt_F_mag_block_id(li_ftab) := 1; --block 1
473 lt_F_next_block_id(li_ftab) := NULL;
474 lt_F_last_run_exec_mode(li_ftab) := 'N';
475 lt_F_overflow_mode(li_ftab) := 'N';
476 lt_F_sequence(li_ftab) := 2;
477 lt_F_frequency(li_ftab) := NULL;
478 lt_F_validate(li_ftab) := false;
479 --
480 -- 1099R_SC Formula to write "A" Record.
481 --
482 li_ftab := li_ftab + 1;
483 lt_F_formula_name(li_ftab) := 'US_1099R_PAYER';
484 lt_F_mag_block_id(li_ftab) := 2; --block 2
485 lt_F_next_block_id(li_ftab) := 3; --block 3
486 lt_F_last_run_exec_mode(li_ftab) := 'N';
487 lt_F_overflow_mode(li_ftab) := 'N';
488 lt_F_sequence(li_ftab) := 1;
489 lt_F_frequency(li_ftab) := NULL;
490 lt_F_validate(li_ftab) := false;
491 --
492 -- 1099R_SC Formula to write "C" Record. End of payer control totals.
493 --
494 li_ftab := li_ftab + 1;
495 lt_F_formula_name(li_ftab) := 'US_1099R_PAYER_TOTALS';
496 lt_F_mag_block_id(li_ftab) := 2; --block 2
497 lt_F_next_block_id(li_ftab) := 4;
498 lt_F_last_run_exec_mode(li_ftab) := 'N';
499 lt_F_overflow_mode(li_ftab) := 'N';
500 lt_F_sequence(li_ftab) := 2;
501 lt_F_frequency(li_ftab) := NULL;
502 lt_F_validate(li_ftab) := false;
503 --
504 -- 1099R_SC Formula to write "B" Records. Payees---------------------------------------
505 --
506 li_ftab := li_ftab + 1;
507 lt_F_formula_name(li_ftab) := 'US_1099R_PAYEES';
508 lt_F_mag_block_id(li_ftab) := 3; --block 3
509 lt_F_next_block_id(li_ftab) := NULL;
510 lt_F_last_run_exec_mode(li_ftab) := 'N';
511 lt_F_overflow_mode(li_ftab) := 'N';
512 lt_F_sequence(li_ftab) := 1;
513 lt_F_frequency(li_ftab) := NULL;
514 lt_F_validate(li_ftab) := false;
515
516 --
517 -- 1099R_SC Formula to write "K" Record. State Totals
518 --
519 li_ftab := li_ftab + 1;
520 lt_F_formula_name(li_ftab) := 'US_1099R_STATE_TOTALS';
521 lt_F_mag_block_id(li_ftab) := 4; --block 4
522 lt_F_next_block_id(li_ftab) := NULL;
523 lt_F_last_run_exec_mode(li_ftab) := 'N';
524 lt_F_overflow_mode(li_ftab) := 'N';
525 lt_F_sequence(li_ftab) := 1;
526 lt_F_frequency(li_ftab) := NULL;
527 lt_F_validate(li_ftab) := false;
528
529 /* */
530 -- ---------------------------------------------------------------------
531 -- States which use 1220, with or without slight modifications
532 -- ---------------------------------------------------------------------
533 lt_report_format(4) := '1099R_STATE_NFED';
534 lt_B_total(4) := 3;
535 lt_F_total(4) := 5;
536 lt_desc(4) :='--States which do not use 1220';
537 lt_rptq_first(4) := 26;
538 lt_rptq_last(4) := 26;
539 --
540 lt_report_qualifier(26) := 'MT'; --Montana
541
542 -- 1099R_STATE_NFED Block 1: Payer that is nominated as the "Transmitter"
543 --
544 li_btab := li_btab + 1;
545 lt_B_block_name(li_btab) := 'US_1099R_NFED_TRANSMITTER';
546 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_transmitter';
547 lt_B_no_column_ret(li_btab) := 10;
548 lt_B_validate(li_btab) := false;
549 --
550 -- 1099R_STATE_NFED Block 2: Payer/Transmitter "A" Record.
551 --
552 li_btab := li_btab + 1;
553 lt_B_block_name(li_btab) := 'US_1099R_NFED_PAYER';
554 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_payer';
555 lt_B_no_column_ret(li_btab) := 6;
556 lt_B_validate(li_btab) := false;
557 --
558 -- 1099R_STATE_NFED Block 3: Payee "B" Record.
559 --
560 li_btab := li_btab + 1;
561 lt_B_block_name(li_btab) := 'US_1099R_NFED_PAYEE';
562 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.state_1099r_payee';
563 lt_B_no_column_ret(li_btab) := 16;
564 lt_B_validate(li_btab) := false;
565 --
566 -- Record definitions. Describe sequence of records, hierarchy and the
567 -- structure of each record ( by formula ).
568 -- 1099R_STATE_NFED Formula.
569 --
570 li_ftab := li_ftab + 1;
571 lt_F_formula_name(li_ftab) := 'US_1099R_NFED_TRANSMITTER';
572 lt_F_mag_block_id(li_ftab) := 1; --block 1
573 lt_F_next_block_id(li_ftab) := 2; --block 2
577 lt_F_frequency(li_ftab) := NULL;
574 lt_F_last_run_exec_mode(li_ftab) := 'N';
575 lt_F_overflow_mode(li_ftab) := 'N';
576 lt_F_sequence(li_ftab) := 1;
578 lt_F_validate(li_ftab) := false;
579 --
580 -- 1099R_STATE_NFED Formula to write "F" Record - End of transmission.
581 --
582 li_ftab := li_ftab + 1;
583 lt_F_formula_name(li_ftab) := 'US_1099R_NFED_FILE_TOTALS';
584 lt_F_mag_block_id(li_ftab) := 1; --block 1
585 lt_F_next_block_id(li_ftab) := NULL;
586 lt_F_last_run_exec_mode(li_ftab) := 'N';
587 lt_F_overflow_mode(li_ftab) := 'N';
588 lt_F_sequence(li_ftab) := 2;
589 lt_F_frequency(li_ftab) := NULL;
590 lt_F_validate(li_ftab) := false;
591 --
592 -- 1099R_STATE_NFED Formula to write "A" Record.
593 --
594 li_ftab := li_ftab + 1;
595 lt_F_formula_name(li_ftab) := 'STATE_1099R_PAYER';
596 lt_F_mag_block_id(li_ftab) := 2; --block 2
597 lt_F_next_block_id(li_ftab) := 3; --block 3
598 lt_F_last_run_exec_mode(li_ftab) := 'N';
599 lt_F_overflow_mode(li_ftab) := 'N';
600 lt_F_sequence(li_ftab) := 1;
601 lt_F_frequency(li_ftab) := NULL;
602 lt_F_validate(li_ftab) := false;
603 --
604 -- 1099R_STATE_NFED Formula to write "C" Record. End of payer control totals.
605 --
606 li_ftab := li_ftab + 1;
607 lt_F_formula_name(li_ftab) := 'US_1099R_NFED_PAYER_TOTALS';
608 lt_F_mag_block_id(li_ftab) := 2; --block 2
609 lt_F_next_block_id(li_ftab) := NULL;
610 lt_F_last_run_exec_mode(li_ftab) := 'N';
611 lt_F_overflow_mode(li_ftab) := 'N';
612 lt_F_sequence(li_ftab) := 2;
613 lt_F_frequency(li_ftab) := NULL;
614 lt_F_validate(li_ftab) := false;
615 --
616 -- 1099R_STATE_NFED Formula to write "B" Records. Payees---------------------------------------
617 --
618 li_ftab := li_ftab + 1;
619 lt_F_formula_name(li_ftab) := 'STATE_1099R_PAYEES';
620 lt_F_mag_block_id(li_ftab) := 3; --block 3
621 lt_F_next_block_id(li_ftab) := NULL;
622 lt_F_last_run_exec_mode(li_ftab) := 'N';
623 lt_F_overflow_mode(li_ftab) := 'N';
624 lt_F_sequence(li_ftab) := 1;
625 lt_F_frequency(li_ftab) := NULL;
626 lt_F_validate(li_ftab) := false;
627 /* */
628 ----------------------------------------------------------------------------------------
629 --1099R custom format for West Virginia
630 ----------------------------------------------------------------------------------------
631 lt_report_format(5) := '1099R_WV';
632 lt_B_total(5) := 3;
633 lt_F_total(5) := 5;
634 lt_desc(5) :='West Virginia 1099R';
635
636 lt_rptq_first(5) := 27;
637 lt_rptq_last(5) := 27;
638 --
639 lt_report_qualifier(27) := 'WV'; --West Virginia
640 ---------------------------------------------------------------------------------------
641 --
642 -- 1099R_WV Block 1: Payer that is nominated as the "Transmitter"--------------------
643 ---
644 li_btab := li_btab + 1;
645 lt_B_block_name(li_btab) := 'US_1099R_NFED_TRANSMITTER';
646 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.us_1099r_transmitter';
647 lt_B_no_column_ret(li_btab) := 10;
648 lt_B_validate(li_btab) := false;
649 --
650 -- 1099R_WV Block 1: Payer/Transmitter "A" Record.----------------------------------
651 --
652 li_btab := li_btab + 1;
653 lt_B_block_name(li_btab) := 'US_1099R_NFED_PAYER';
654 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.US_1099r_payer';
655 lt_B_no_column_ret(li_btab) := 6;
656 lt_B_validate(li_btab) := false;
657 --
658 -- 1099R_WV Block 2: Payee "B" Record.----------------------------------------------
659 --
660 li_btab := li_btab + 1;
661 lt_B_block_name(li_btab) := 'US_1099R_NFED_PAYEE';
662 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.state_1099r_payee';
663 lt_B_no_column_ret(li_btab) := 16;
664 lt_B_validate(li_btab) := false;
665 --
666 ----------------------------------------------------------------------------------------
667 -- Record definitions. Describe sequence of records, hierarchy and the
668 -- structure of each record ( by formula ).
669 --1099R_WV Formula---------------------------------------------------------------------------
670 li_ftab := li_ftab + 1;
671 lt_F_formula_name(li_ftab) := 'US_1099R_NFED_TRANSMITTER';
672 lt_F_mag_block_id(li_ftab) := 1; --block 1
673 lt_F_next_block_id(li_ftab) := 2; --block 2
674 lt_F_last_run_exec_mode(li_ftab) := 'N';
675 lt_F_overflow_mode(li_ftab) := 'N';
676 lt_F_sequence(li_ftab) := 1;
677 lt_F_frequency(li_ftab) := NULL;
678 lt_F_validate(li_ftab) := false;
679 ---
680 -- 1099R_WV Formula to write transmitter details.
681 li_ftab := li_ftab + 1;
682 lt_F_formula_name(li_ftab) := 'US_1099R_NFED_FILE_TOTALS';
683 lt_F_mag_block_id(li_ftab) := 1; --block 1
684 lt_F_next_block_id(li_ftab) := NULL;
685 lt_F_last_run_exec_mode(li_ftab) := 'N';
686 lt_F_overflow_mode(li_ftab) := 'N';
687 lt_F_sequence(li_ftab) := 2;
688 lt_F_frequency(li_ftab) := NULL;
689 lt_F_validate(li_ftab) := false;
690 --
691 -- 1099R_WV Formula to write "E" Record-----------------------------------------------
692 --
693 li_ftab := li_ftab + 1;
694 lt_F_formula_name(li_ftab) := 'WV_1099R_PAYER';
695 lt_F_mag_block_id(li_ftab) := 2; --block 2
696 lt_F_next_block_id(li_ftab) := 3; --block 3
697 lt_F_last_run_exec_mode(li_ftab) := 'N';
698 lt_F_overflow_mode(li_ftab) := 'N';
699 lt_F_sequence(li_ftab) := 1;
700 lt_F_frequency(li_ftab) := NULL;
704 --
701 lt_F_validate(li_ftab) := false;
702 --
703 -- 1099R_WV Formula to write transmitter file details
705 li_ftab := li_ftab + 1;
706 lt_F_formula_name(li_ftab) := 'US_1099R_NFED_PAYER_TOTALS';
707 lt_F_mag_block_id(li_ftab) := 2; --block 2
708 lt_F_next_block_id(li_ftab) := NULL;
709 lt_F_last_run_exec_mode(li_ftab) := 'N';
710 lt_F_overflow_mode(li_ftab) := 'N';
711 lt_F_sequence(li_ftab) := 2;
712 lt_F_frequency(li_ftab) := NULL;
713 lt_F_validate(li_ftab) := false;
714 --
715 --
716 -- 1099R_WV Formula to write "W" Records. Payees---------------------------------------
717 --
718 li_ftab := li_ftab + 1;
719 lt_F_formula_name(li_ftab) := 'WV_1099R_PAYEES';
720 lt_F_mag_block_id(li_ftab) := 3 ; --block 3
721 lt_F_next_block_id(li_ftab) := NULL;
722 lt_F_last_run_exec_mode(li_ftab) := 'N';
723 lt_F_overflow_mode(li_ftab) := 'N';
724 lt_F_sequence(li_ftab) := 1;
725 lt_F_frequency(li_ftab) := NULL;
726 lt_F_validate(li_ftab) := false;
727
728 ------------------------------------------------------------------------------------------
729 --1099R custom format for Indiana
730 -- Indiana requires the TIB4 format for 1099R reporting
731 -- identical structure is taken from the W2 Reporting.
732 ----------------------------------------------------------------------------------------
733 lt_report_format(6) := '1099R_IND';
734 lt_B_total(6) := 4;
735 lt_F_total(6) := 7;
736 lt_desc(6) :='Indiana 1099R';
737 --
738 lt_rptq_first(6) := 28;
739 lt_rptq_last(6) := 28;
740 --
741 lt_report_qualifier(28) := 'IN'; --IN
742 ----------------------------------------------------------------------------------------
743 --
744 -- 1099R_IND Block 1: Payer that is nominated as the "Transmitter"--------------------
745 --
746 li_btab := li_btab + 1;
747 lt_B_block_name(li_btab) := 'IN_1099R_TRANSMITTER';
748 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.magw2_transmitter';
749 lt_B_no_column_ret(li_btab) := 12;
750 lt_B_validate(li_btab) := false;
751 --
752 -- 1099R_IND Block 2: EMPLOYER --------------------
753 --
754 li_btab := li_btab + 1;
755 lt_B_block_name(li_btab) := 'IN_1099R_EMPLOYER';
756 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.st_magw2_employer';
757 lt_B_no_column_ret(li_btab) := 12;
758 lt_B_validate(li_btab) := false;
759 --
760 -- 1099R_IND Block 3: EMPLOYEE --------------------
761 --
762 li_btab := li_btab + 1;
763 lt_B_block_name(li_btab) := 'IN_1099R_EMPLOYEE';
764 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.oh_in_employee';
765 lt_B_no_column_ret(li_btab) := 8;
766 lt_B_validate(li_btab) := false;
767 --
768 -- 1099R_IND Block 4: SUPPLEMENTAL --------------------
769 --
770 li_btab := li_btab + 1;
771 lt_B_block_name(li_btab) := 'IN_1099R_SUPP';
772 lt_B_cursor_name(li_btab) := 'pay_us_1099r_mag_reporting.state_1099r_payee';
773 lt_B_no_column_ret(li_btab) := 16;
774 lt_B_validate(li_btab) := false;
775
776 ----------------------------------------------------------------------------------------
777 -- Record definitions. Describe sequence of records, hierarchy and the
778 -- structure of each record ( by formula ).
779 --1099R_IND Formula-------------------------------------------------------------------
780 --
781 li_ftab := li_ftab + 1;
782 lt_F_formula_name(li_ftab) := 'IN_1099R_TRANSMITTER';
783 lt_F_mag_block_id(li_ftab) := 1; --block 1
784 lt_F_next_block_id(li_ftab) := 2;
785 lt_F_last_run_exec_mode(li_ftab) := 'N';
786 lt_F_overflow_mode(li_ftab) := 'N';
787 lt_F_sequence(li_ftab) := 1;
788 lt_F_frequency(li_ftab) := NULL;
789 lt_F_validate(li_ftab) := false;
790
791 -- 1099R_IND Formula to write "x" Record-----------------------------------------------
792 --
793 li_ftab := li_ftab + 1;
794 lt_F_formula_name(li_ftab) := 'IN_1099R_FINAL'; --TIB4_FINAL
795 lt_F_mag_block_id(li_ftab) := 1; --block 1
796 lt_F_next_block_id(li_ftab) := NULL;
797 lt_F_last_run_exec_mode(li_ftab) := 'N';
798 lt_F_overflow_mode(li_ftab) := 'N';
799 lt_F_sequence(li_ftab) := 2;
800 lt_F_frequency(li_ftab) := NULL;
801 lt_F_validate(li_ftab) := false;
802
803 -- 1099R_IND Formula to write "x" Record-----------------------------------------------
804 li_ftab := li_ftab + 1;
805 lt_F_formula_name(li_ftab) := 'IN_1099R_EMPLOYER'; -- TIB4_EMPLOYER
806 lt_F_mag_block_id(li_ftab) := 2;
807 lt_F_next_block_id(li_ftab) := 3;
808 lt_F_last_run_exec_mode(li_ftab) := 'N';
809 lt_F_overflow_mode(li_ftab) := 'N';
810 lt_F_sequence(li_ftab) := 1;
811 lt_F_frequency(li_ftab) := NULL;
812 lt_F_validate(li_ftab) := false;
813
814 -- 1099R_IND Formula to write "x" Record-----------------------------------------------
815 li_ftab := li_ftab + 1;
816 lt_F_formula_name(li_ftab) := 'IN_1099R_TOTAL';
817 lt_F_mag_block_id(li_ftab) := 2;
818 lt_F_next_block_id(li_ftab) := NULL;
819 lt_F_last_run_exec_mode(li_ftab) := 'N';
820 lt_F_overflow_mode(li_ftab) := 'N';
821 lt_F_sequence(li_ftab) := 2;
822 lt_F_frequency(li_ftab) := NULL;
823 lt_F_validate(li_ftab) := false;
824
825 -- 1099R_IND Formula to write "x" Record-----------------------------------------------
826 li_ftab := li_ftab + 1;
830 lt_F_last_run_exec_mode(li_ftab) := 'N';
827 lt_F_formula_name(li_ftab) := 'TIB4_DUMMY';
828 lt_F_mag_block_id(li_ftab) := 3;
829 lt_F_next_block_id(li_ftab) := NULL;
831 lt_F_overflow_mode(li_ftab) := 'N';
832 lt_F_sequence(li_ftab) := 1;
833 lt_F_frequency(li_ftab) := NULL;
834 lt_F_validate(li_ftab) := false;
835 --
836 -- 1099R_IND Formula to write "E" Record-----------------------------------------------
837 --
838 li_ftab := li_ftab + 1;
839 lt_F_formula_name(li_ftab) := 'TIB4_EMPLOYEE';
840 lt_F_mag_block_id(li_ftab) := 3;
841 lt_F_next_block_id(li_ftab) := 4;
842 lt_F_last_run_exec_mode(li_ftab) := 'N';
843 lt_F_overflow_mode(li_ftab) := 'N';
844 lt_F_sequence(li_ftab) := 2;
845 lt_F_frequency(li_ftab) := NULL;
846 lt_F_validate(li_ftab) := false;
847 --
848 -- 1099R_IND Formula to write "S" Record-----------------------------------------------
849 --
850 li_ftab := li_ftab + 1;
851 lt_F_formula_name(li_ftab) := 'IN_1099R_SUPPLEMENTAL'; -- TIB4_SUPPLEMENTAL
852 lt_F_mag_block_id(li_ftab) := 4;
853 lt_F_next_block_id(li_ftab) := NULL;
854 lt_F_last_run_exec_mode(li_ftab) := 'N';
855 lt_F_overflow_mode(li_ftab) := 'N';
856 lt_F_sequence(li_ftab) := 1;
857 lt_F_frequency(li_ftab) := NULL;
858 lt_F_validate(li_ftab) := false;
859
860
861 /* */
862 ------------------------------------------------------------------------------------------
863 --1099R custom format for Michigan
864 -- Michigan requires the TIB4 format for 1099R reporting
865 -- identical structure is taken from the W2 Reporting.
866 ----------------------------------------------------------------------------------------
867 lt_report_format(7) := '1099R_MI';
868 lt_B_total(7) := 3;
869 lt_F_total(7) := 7;
870 lt_desc(7) :='Michigan 1099R';
871 --
872 lt_rptq_first(7) := 29;
873 lt_rptq_last(7) := 29;
874 --
875 lt_report_qualifier(29):= 'MI';
876 ----------------------------------------------------------------------------------------
877 --
878 -- 1099R_MI Block 1: Payer that is nominated as the "Transmitter"--------------------
879 --
880 li_btab := li_btab + 1;
881 lt_B_block_name(li_btab) := 'MI_1099R_TRANSMITTER';
882 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.magw2_transmitter';
883 lt_B_no_column_ret(li_btab) := 12;
884 lt_B_validate(li_btab) := false;
885 --
886 -- 1099R_MI Block 2: EMPLOYER --------------------
887 --
888 li_btab := li_btab + 1;
889 lt_B_block_name(li_btab) := 'MI_1099R_EMPLOYER';
890 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.st_magw2_employer';
891 lt_B_no_column_ret(li_btab) := 12;
892 lt_B_validate(li_btab) := false;
893 --
894 -- 1099R_MI Block 3: EMPLOYEE --------------------
895 --
896 li_btab := li_btab + 1;
897 lt_B_block_name(li_btab) := 'MI_1099R_EMPLOYEE';
898 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.st_magw2_employee';
899 lt_B_no_column_ret(li_btab) := 8;
900 lt_B_validate(li_btab) := false;
901
902 ----------------------------------------------------------------------------------------
903 -- Record definitions. Describe sequence of records, hierarchy and the
904 -- structure of each record ( by formula ).
905 --1099R_MI Formula-------------------------------------------------------------------
906 --
907 li_ftab := li_ftab + 1;
908 lt_F_formula_name(li_ftab) := 'IN_1099R_TRANSMITTER';
909 lt_F_mag_block_id(li_ftab) := 1; --block 1
910 lt_F_next_block_id(li_ftab) := 2;
911 lt_F_last_run_exec_mode(li_ftab) := 'N';
912 lt_F_overflow_mode(li_ftab) := 'N';
913 lt_F_sequence(li_ftab) := 1;
914 lt_F_frequency(li_ftab) := NULL;
915 lt_F_validate(li_ftab) := false;
916
917 -- 1099R_MI Formula to write "x" Record-----------------------------------------------
918 li_ftab := li_ftab + 1;
919 lt_F_formula_name(li_ftab) := 'IN_1099R_FINAL'; --DUMMY_TIB4_FINAL
920 lt_F_mag_block_id(li_ftab) := 1; --block 1
921 lt_F_next_block_id(li_ftab) := NULL;
922 lt_F_last_run_exec_mode(li_ftab) := 'N';
923 lt_F_overflow_mode(li_ftab) := 'N';
924 lt_F_sequence(li_ftab) := 2;
925 lt_F_frequency(li_ftab) := NULL;
926 lt_F_validate(li_ftab) := false;
927 -- 1099R_MI Formula ----------------------------------------------------------------
928 --
929 li_ftab := li_ftab + 1;
930 lt_F_formula_name(li_ftab) := 'IN_1099R_EMPLOYER'; --TIB4_EMPLOYER
931 lt_F_mag_block_id(li_ftab) := 2; --block 2
932 lt_F_next_block_id(li_ftab) := 3; --block 3
933 lt_F_last_run_exec_mode(li_ftab) := 'N';
934 lt_F_overflow_mode(li_ftab) := 'N';
935 lt_F_sequence(li_ftab) := 1;
936 lt_F_frequency(li_ftab) := NULL;
937 lt_F_validate(li_ftab) := false;
938
939 -- 1099R_MI Formula to write "x" Record-----------------------------------------------
940 --
941 li_ftab := li_ftab + 1;
942 lt_F_formula_name(li_ftab) := 'IN_1099R_TOTAL';--DUMMY_TIB4_TOTAL
943 lt_F_mag_block_id(li_ftab) := 2; --block 2
944 lt_F_next_block_id(li_ftab) := NULL;
945 lt_F_last_run_exec_mode(li_ftab) := 'N';
946 lt_F_overflow_mode(li_ftab) := 'N';
947 lt_F_sequence(li_ftab) := 2;
948 lt_F_frequency(li_ftab) := NULL;
949 lt_F_validate(li_ftab) := false;
950
951 -- 1099R_MI Formula to write "x" Record-----------------------------------------------
952 li_ftab := li_ftab + 1;
956 lt_F_last_run_exec_mode(li_ftab) := 'N';
953 lt_F_formula_name(li_ftab) := 'TIB4_DUMMY';
954 lt_F_mag_block_id(li_ftab) := 3;
955 lt_F_next_block_id(li_ftab) := NULL;
957 lt_F_overflow_mode(li_ftab) := 'N';
958 lt_F_sequence(li_ftab) := 1;
959 lt_F_frequency(li_ftab) := NULL;
960 lt_F_validate(li_ftab) := false;
961
962 -- 1099R_MI Formula to write "E" Record-----------------------------------------------
963 li_ftab := li_ftab + 1;
964 lt_F_formula_name(li_ftab) := 'TIB4_EMPLOYEE'; --required
965 lt_F_mag_block_id(li_ftab) := 3;
966 lt_F_next_block_id(li_ftab) := NULL;
967 lt_F_last_run_exec_mode(li_ftab) := 'N';
968 lt_F_overflow_mode(li_ftab) := 'R';
969 lt_F_sequence(li_ftab) := 2;
970 lt_F_frequency(li_ftab) := NULL;
971 lt_F_validate(li_ftab) := false;
972
973 -- 1099R_MI Formula to write "S" Record-----------------------------------------------
974 li_ftab := li_ftab + 1;
975 lt_F_formula_name(li_ftab) := 'TIB4_SUPPLEMENTAL'; --required
976 lt_F_mag_block_id(li_ftab) := 3;
977 lt_F_next_block_id(li_ftab) := NULL;
978 lt_F_last_run_exec_mode(li_ftab) := 'N';
979 lt_F_overflow_mode(li_ftab) := 'N';
980 lt_F_sequence(li_ftab) := 3;
981 lt_F_frequency(li_ftab) := NULL;
982 lt_F_validate(li_ftab) := false;
983 --
984 -------------------------------------------------------------------------------------------
985 /* */
986 ------------------------------------------------------------------------------------------
987 --1099R custom format for New York
988 -- New York requires the SQWL format for 1099R reporting
989 -- identical structure is taken from pyw2data
990 ----------------------------------------------------------------------------------------
991 lt_report_format(8) := '1099R_NY';
992 lt_B_total(8) := 3;
993 lt_F_total(8) := 5;
994 lt_desc(8) :='New York 1099R';
995 --
996 lt_rptq_first(8) := 30;
997 lt_rptq_last(8) := 30;
998 --
999 lt_report_qualifier(30):= 'NY';
1000 ----------------------------------------------------------------------------------------
1001 --
1002 -- 1099R_NY Block 1: Payer that is nominated as the "Transmitter"--------------------
1003 --
1004 li_btab := li_btab + 1;
1005 lt_B_block_name(li_btab) := 'NY_1099R_TRANSMITTER';
1006 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.magw2_transmitter';
1007 lt_B_no_column_ret(li_btab) := 12;
1008 lt_B_validate(li_btab) := false;
1009 --
1010 -- 1099R_NY Block 2: EMPLOYER --------------------
1011 --
1012 li_btab := li_btab + 1;
1013 lt_B_block_name(li_btab) := 'NY_1099R_EMPLOYER';
1014 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.st_magw2_employer';
1015 lt_B_no_column_ret(li_btab) := 12;
1016 lt_B_validate(li_btab) := false;
1017 --
1018 -- 1099R_NY Block 3: EMPLOYEE --------------------
1019 --
1020 li_btab := li_btab + 1;
1021 lt_B_block_name(li_btab) := 'NY_1099R_EMPLOYEE';
1022 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.st_magw2_employee';
1023 lt_B_no_column_ret(li_btab) := 8;
1024 lt_B_validate(li_btab) := false;
1025
1026 ----------------------------------------------------------------------------------------
1027 -- Record definitions. Describe sequence of records, hierarchy and the
1028 -- structure of each record ( by formula ).
1029 --1099R_NY Formula-------------------------------------------------------------------
1030 --
1031 li_ftab := li_ftab + 1;
1032 lt_F_formula_name(li_ftab) := 'NY_1099R_TRANSMITTER';
1033 lt_F_mag_block_id(li_ftab) := 1; --block 1
1034 lt_F_next_block_id(li_ftab) := 2; --block 2
1035 lt_F_last_run_exec_mode(li_ftab) := 'N';
1036 lt_F_overflow_mode(li_ftab) := 'N';
1037 lt_F_sequence(li_ftab) := 1;
1038 lt_F_frequency(li_ftab) := NULL;
1039 lt_F_validate(li_ftab) := false;
1040
1041 -- 1099R_NY Formula to write "x" Record-----------------------------------------------
1042 li_ftab := li_ftab + 1;
1043 lt_F_formula_name(li_ftab) := 'NY_1099R_FINAL';
1044 lt_F_mag_block_id(li_ftab) := 1; --block 1
1045 lt_F_next_block_id(li_ftab) := NULL;
1046 lt_F_last_run_exec_mode(li_ftab) := 'N';
1047 lt_F_overflow_mode(li_ftab) := 'N';
1048 lt_F_sequence(li_ftab) := 2;
1049 lt_F_frequency(li_ftab) := NULL;
1050 lt_F_validate(li_ftab) := false;
1051 -- 1099R_NY Formula ----------------------------------------------------------------
1052 --
1053 li_ftab := li_ftab + 1;
1054 lt_F_formula_name(li_ftab) := 'NY_1099R_EMPLOYER';
1055 lt_F_mag_block_id(li_ftab) := 2; --block 2
1056 lt_F_next_block_id(li_ftab) := 3; --block 3
1057 lt_F_last_run_exec_mode(li_ftab) := 'N';
1058 lt_F_overflow_mode(li_ftab) := 'N';
1059 lt_F_sequence(li_ftab) := 1;
1060 lt_F_frequency(li_ftab) := NULL;
1061 lt_F_validate(li_ftab) := false;
1062
1063 -- 1099R_NY Formula to write "x" Record-----------------------------------------------
1064 --
1065 li_ftab := li_ftab + 1;
1066 lt_F_formula_name(li_ftab) := 'NY_1099R_TOTAL';
1067 lt_F_mag_block_id(li_ftab) := 2; --block 2
1068 lt_F_next_block_id(li_ftab) := NULL;
1069 lt_F_last_run_exec_mode(li_ftab) := 'N';
1070 lt_F_overflow_mode(li_ftab) := 'N';
1071 lt_F_sequence(li_ftab) := 2;
1072 lt_F_frequency(li_ftab) := NULL;
1073 lt_F_validate(li_ftab) := false;
1074
1075 -- 1099R_NY Formula to write "x" Record-----------------------------------------------
1076 li_ftab := li_ftab + 1;
1077 lt_F_formula_name(li_ftab) := 'NY_1099R_EMPLOYEE';
1081 lt_F_overflow_mode(li_ftab) := 'N';
1078 lt_F_mag_block_id(li_ftab) := 3;
1079 lt_F_next_block_id(li_ftab) := NULL;
1080 lt_F_last_run_exec_mode(li_ftab) := 'N';
1082 lt_F_sequence(li_ftab) := 1;
1083 lt_F_frequency(li_ftab) := NULL;
1084 lt_F_validate(li_ftab) := false;
1085
1086 -------------------------------------------------------------------------------------------
1087 /* */
1088 ------------------------------------------------------------------------------------------
1089 --1099R custom format for North Carolina
1090 -- North Carolina requires the W2 format for 1099R reporting
1091 -- identical structure is taken from pyw2data
1092 ----------------------------------------------------------------------------------------
1093 lt_report_format(9) := '1099R_NC';
1094 lt_B_total(9) := 3;
1095 lt_F_total(9) := 10;
1096 lt_desc(9) :='North Carolina 1099R';
1097 --
1098 lt_rptq_first(9) := 31;
1099 lt_rptq_last(9) := 31;
1100 --
1101 lt_report_qualifier(31):= 'NC';
1102 ----------------------------------------------------------------------------------------
1103 --
1104 -- 1099R_NC Block 1: Payer that is nominated as the "Transmitter"--------------------
1105 --
1106 li_btab := li_btab + 1;
1107 lt_B_block_name(li_btab) := 'NC_1099R_TRANSMITTER';
1108 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.magw2_transmitter';
1109 lt_B_no_column_ret(li_btab) := 12;
1110 lt_B_validate(li_btab) := false;
1111 --
1112 -- 1099R_NC Block 2: EMPLOYER --------------------
1113 --
1114 li_btab := li_btab + 1;
1115 lt_B_block_name(li_btab) := 'NC_1099R_EMPLOYER';
1116 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.st_magw2_employer';
1117 lt_B_no_column_ret(li_btab) := 12;
1118 lt_B_validate(li_btab) := false;
1119 --
1120 -- 1099R_NC Block 3: EMPLOYEE --------------------
1121 --
1122 li_btab := li_btab + 1;
1123 lt_B_block_name(li_btab) := 'NC_1099R_EMPLOYEE';
1124 lt_B_cursor_name(li_btab) := 'pay_us_magw2_reporting.st_magw2_employee';
1125 lt_B_no_column_ret(li_btab) := 8;
1126 lt_B_validate(li_btab) := false;
1127
1128 ----------------------------------------------------------------------------------------
1129 -- Record definitions. Describe sequence of records, hierarchy and the
1130 -- structure of each record ( by formula ).
1131 --1099R_NC Formula-------------------------------------------------------------------
1132 --
1133 li_ftab := li_ftab + 1;
1134 lt_F_formula_name(li_ftab) := 'TIB4_TRANSMITTER';
1135 lt_F_mag_block_id(li_ftab) := 1; --block 1
1136 lt_F_next_block_id(li_ftab) := 2; --block 2
1137 lt_F_last_run_exec_mode(li_ftab) := 'N';
1138 lt_F_overflow_mode(li_ftab) := 'N';
1139 lt_F_sequence(li_ftab) := 1;
1140 lt_F_frequency(li_ftab) := NULL;
1141 lt_F_validate(li_ftab) := false;
1142
1143 -- 1099R_NC Formula to write "x" Record-----------------------------------------------
1144 li_ftab := li_ftab + 1;
1145 lt_F_formula_name(li_ftab) := 'TIB4_FINAL';
1146 lt_F_mag_block_id(li_ftab) := 1; --block 1
1147 lt_F_next_block_id(li_ftab) := Null;
1148 lt_F_last_run_exec_mode(li_ftab) := 'N';
1149 lt_F_overflow_mode(li_ftab) := 'N';
1150 lt_F_sequence(li_ftab) := 2;
1151 lt_F_frequency(li_ftab) := NULL;
1152 lt_F_validate(li_ftab) := false;
1153
1154 -- 1099R_NC Formula ----------------------------------------------------------------
1155 --
1156 li_ftab := li_ftab + 1;
1157 lt_F_formula_name(li_ftab) := 'TIB4_EMPLOYER';
1158 lt_F_mag_block_id(li_ftab) := 2; --block 2
1159 lt_F_next_block_id(li_ftab) := 3; --block 3
1160 lt_F_last_run_exec_mode(li_ftab) := 'N';
1161 lt_F_overflow_mode(li_ftab) := 'N';
1162 lt_F_sequence(li_ftab) := 1;
1163 lt_F_frequency(li_ftab) := NULL;
1164 lt_F_validate(li_ftab) := false;
1165
1166 -- 1099R_NC Formula to write "x" Record-----------------------------------------------
1167 --
1168 li_ftab := li_ftab + 1;
1169 lt_F_formula_name(li_ftab) := 'TIB4_INTERMEDIATE_TOTAL';
1170 lt_F_mag_block_id(li_ftab) := 2; --block 2
1171 lt_F_next_block_id(li_ftab) := NULL;
1172 lt_F_last_run_exec_mode(li_ftab) := 'N';
1173 lt_F_overflow_mode(li_ftab) := 'N';
1174 lt_F_sequence(li_ftab) := 2;
1175 lt_F_frequency(li_ftab) := NULL;
1176 lt_F_validate(li_ftab) := false;
1177
1178 -- 1099R_NC Formula to write "x" Record-----------------------------------------------
1179 --
1180 li_ftab := li_ftab + 1;
1181 lt_F_formula_name(li_ftab) := 'TIB4_TOTAL';
1182 lt_F_mag_block_id(li_ftab) := 2; --block 2
1183 lt_F_next_block_id(li_ftab) := NULL;
1184 lt_F_last_run_exec_mode(li_ftab) := 'N';
1185 lt_F_overflow_mode(li_ftab) := 'N';
1186 lt_F_sequence(li_ftab) := 3;
1187 lt_F_frequency(li_ftab) := NULL;
1188 lt_F_validate(li_ftab) := false;
1189
1190 -- 1099R_NC Formula to write "x" Record-----------------------------------------------
1191 --
1192 li_ftab := li_ftab + 1;
1193 lt_F_formula_name(li_ftab) := 'TIB4_DUMMY';
1194 lt_F_mag_block_id(li_ftab) := 3;
1195 lt_F_next_block_id(li_ftab) := NULL;
1196 lt_F_last_run_exec_mode(li_ftab) := 'N';
1197 lt_F_overflow_mode(li_ftab) := 'N';
1198 lt_F_sequence(li_ftab) := 1;
1199 lt_F_frequency(li_ftab) := NULL;
1200 lt_F_validate(li_ftab) := false;
1201
1202 -- 1099R_NC Formula to write "x" Record-----------------------------------------------
1203 --
1204 li_ftab := li_ftab + 1;
1205 lt_F_formula_name(li_ftab) := 'NCTIB4_SUPPLEMENTAL';
1209 lt_F_overflow_mode(li_ftab) := 'N';
1206 lt_F_mag_block_id(li_ftab) := 3;
1207 lt_F_next_block_id(li_ftab) := NULL;
1208 lt_F_last_run_exec_mode(li_ftab) := 'N';
1210 lt_F_sequence(li_ftab) := 2;
1211 lt_F_frequency(li_ftab) := NULL;
1212 lt_F_validate(li_ftab) := false;
1213
1214 -- 1099R_NC Formula to write "x" Record-----------------------------------------------
1215 --
1216 li_ftab := li_ftab + 1;
1217 lt_F_formula_name(li_ftab) := 'TIB4_EMPLOYEE';
1218 lt_F_mag_block_id(li_ftab) := 3;
1219 lt_F_next_block_id(li_ftab) := NULL;
1220 lt_F_last_run_exec_mode(li_ftab) := 'N';
1221 lt_F_overflow_mode(li_ftab) := 'N';
1222 lt_F_sequence(li_ftab) := 3;
1223 lt_F_frequency(li_ftab) := NULL;
1224 lt_F_validate(li_ftab) := false;
1225
1226 -- 1099R_NC Formula to write "x" Record-----------------------------------------------
1227 --
1228 li_ftab := li_ftab + 1;
1229 lt_F_formula_name(li_ftab) := 'W2_TIB4_SUPPLEMENTAL';
1230 lt_F_mag_block_id(li_ftab) := 3;
1231 lt_F_next_block_id(li_ftab) := NULL;
1232 lt_F_last_run_exec_mode(li_ftab) := 'N';
1233 lt_F_overflow_mode(li_ftab) := 'N';
1234 lt_F_sequence(li_ftab) := 4;
1235 lt_F_frequency(li_ftab) := NULL;
1236 lt_F_validate(li_ftab) := false;
1237
1238 -- 1099R_NC Formula to write "x" Record-----------------------------------------------
1239 --
1240 li_ftab := li_ftab + 1;
1241 lt_F_formula_name(li_ftab) := 'TIB4_INTERMEDIATE_TOTAL';
1242 lt_F_mag_block_id(li_ftab) := 3;
1243 lt_F_next_block_id(li_ftab) := NULL;
1244 lt_F_last_run_exec_mode(li_ftab) := 'N';
1245 lt_F_overflow_mode(li_ftab) := 'N';
1246 lt_F_sequence(li_ftab) := 5;
1247 lt_F_frequency(li_ftab) := 41;
1248 lt_F_validate(li_ftab) := false;
1249
1250 ----------------------------------------------------------------------------------------
1251 --
1252 hr_utility.trace('BEGIN 1099R REPORT DEFINITION PROCESS------------------------');
1253 li_btab := 1;
1254 li_ftab := 1;
1255 --
1256 FOR l_ccount IN 1..l_case_count LOOP
1257 ------------------------------------------------------
1258 --Clear existing format definitions and mappings
1259 ------------------------------------------------------
1260 hr_utility.trace('Insert definition for ' ||
1261 lt_report_format(l_ccount));
1262 hr_utility.trace(lt_desc(l_ccount));
1263 hr_utility.trace(' Deleting magnetic records...');
1264 DELETE FROM pay_magnetic_records
1265 WHERE magnetic_block_id IN
1266 (SELECT pmb.magnetic_block_id
1267 FROM pay_magnetic_blocks pmb,
1268 pay_report_format_mappings_f pfm
1269 WHERE pmb.report_format = pfm.report_format
1270 AND pmb.report_format = lt_report_format(l_ccount)
1271 AND pfm.report_category = 'RT');
1272 --
1273 hr_utility.trace(' Deleting blocks...');
1274 DELETE FROM pay_magnetic_blocks pmb
1275 WHERE pmb.report_format in
1276 (SELECT pfm.report_format
1277 FROM pay_report_format_mappings_f pfm
1278 WHERE pfm.report_format = lt_report_format(l_ccount)
1279 AND pfm.report_category = 'RT');
1280 --
1281 hr_utility.trace(' Deleting report format mappings...');
1282 DELETE FROM pay_report_format_mappings_f rfm
1283 WHERE rfm.report_format = lt_report_format(l_ccount)
1284 AND report_category = 'RT';
1285
1286 END LOOP;
1287
1288 FOR l_ccount IN 1..l_case_count LOOP
1289 --
1290 -------------------------------------------------------
1291 --Insert report format mappings
1292 -------------------------------------------------------
1293 --
1294 FOR l_st_count IN lt_rptq_first(l_ccount)..lt_rptq_last(l_ccount) LOOP
1295 hr_utility.trace(' --Inserting '|| lt_report_qualifier(l_st_count)
1296 || ' report qualifier...');
1297 INSERT INTO pay_report_format_mappings_f
1298 ( report_type,
1299 report_qualifier,
1300 report_format,
1301 report_category,
1302 range_code,
1303 assignment_action_code,
1304 initialization_code,
1305 archive_code,
1306 magnetic_code,
1307 effective_start_date,
1308 effective_end_date )
1309 VALUES ( l_report_type,
1310 lt_report_qualifier(l_st_count),
1311 lt_report_format(l_ccount),
1312 'RT',
1313 'pay_us_1099r_mag_reporting.range_cursor',
1314 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',
1315 null,
1316 null,
1317 'pay_magtape_generic.new_formula',
1318 c_start_of_time,
1319 c_end_of_time );
1320 END LOOP;
1321 --
1322 -------------------------------------------------------
1323 --Insert Blocks
1324 -------------------------------------------------------
1325 FOR l_bcount IN 1..lt_B_total(l_ccount) LOOP
1326 hr_utility.trace(' ');
1327 hr_utility.trace('Inserting block ' || lt_B_block_name(li_btab));
1328 l_message := 'Error inserting block ' || lt_B_block_name(li_btab);
1329 --
1330 IF l_bcount = 1 THEN -- only the first block should be starting block
1331 l_main_block_flag := 'Y';
1332 ELSE l_main_block_flag := 'N';
1333 END IF;
1334 --
1335 Pay_Mgb_Ins.Ins
1336 ( p_magnetic_block_id => lt_B_mag_block_id(l_bcount),
1340 p_cursor_name => lt_B_cursor_name(li_btab),
1337 p_block_name => lt_B_block_name(li_btab),
1338 p_main_block_flag => l_main_block_flag,
1339 p_report_format => lt_report_format(l_ccount),
1341 p_no_column_returned => lt_B_no_column_ret(li_btab),
1342 p_validate => lt_B_validate(li_btab));
1343 li_btab := li_btab + 1;
1344 END LOOP;
1345 ----------------------------------------------------------
1346 --Insert Formulas
1350 hr_utility.trace('Inserting record def with formula '
1347 ----------------------------------------------------------
1348 FOR l_fcount IN 1..lt_F_total(l_ccount) LOOP
1349 hr_utility.trace('-------------');
1351 || lt_F_formula_name(li_ftab));
1352 --
1353 l_message:='Error inserting record def with formula '
1354 || lt_F_formula_name(li_ftab);
1355 --
1356 l_f_id := Pay_Mag_Utils.Lookup_Formula
1357 ( p_session_date => c_start_of_time,
1358 p_business_group_id => NULL,
1359 p_legislation_code =>'US',
1360 p_formula_name => lt_F_formula_name(li_ftab));
1361 --
1362 IF l_f_id IS NULL THEN
1363 hr_utility.trace('Could not find formula id');
1364 ELSE hr_utility.trace('Successfully found formula id');
1365 END IF;
1366 --
1367 -- Since lt_F_mag_block_id is used to index into lt_B_mag_block_id,
1368 -- take care of NULL values
1369 --
1370 IF lt_F_mag_block_id(li_ftab) IS NULL THEN
1371 hr_utility.trace( '--ERROR:NULL block id not allowed');
1372 ELSE l_mag_block_id := lt_B_mag_block_id(lt_F_mag_block_id(li_ftab));
1373 END IF;
1374 --
1375 IF lt_F_next_block_id(li_ftab) IS NULL THEN
1376 l_next_block_id := NULL;
1377 ELSE l_next_block_id := lt_B_mag_block_id(lt_F_next_block_id(li_ftab));
1378 END IF;
1379 --
1380 hr_utility.trace( ' formula_id = '|| l_f_id);
1381 hr_utility.trace( ' magnetic_block_id = '|| l_mag_block_id);
1382 hr_utility.trace( ' next_block_id = '|| l_next_block_id);
1383 hr_utility.trace( ' last_run_exec_mode = '|| lt_F_last_run_exec_mode(li_ftab));
1384 hr_utility.trace( ' overflow_mode = '|| lt_F_overflow_mode(li_ftab));
1385 hr_utility.trace( ' sequence = '|| lt_F_sequence(li_ftab));
1386 hr_utility.trace( ' frequency = '|| lt_F_frequency(li_ftab));
1387 --
1388 --
1389 Pay_Mgr_Ins.Ins
1390 ( p_formula_id => l_f_id,
1391 p_magnetic_block_id => l_mag_block_id,
1392 p_next_block_id => l_next_block_id,
1393 p_last_run_executed_mode => lt_F_last_run_exec_mode(li_ftab),
1394 p_overflow_mode => lt_F_overflow_mode(li_ftab),
1395 p_sequence => lt_F_sequence(li_ftab),
1396 p_frequency => lt_F_frequency(li_ftab),
1397 p_validate => lt_F_validate(li_ftab));
1398 --
1399 li_ftab := li_ftab + 1;
1400 END LOOP;
1401 --
1402 hr_utility.trace('Successfully created '|| lt_report_format(l_ccount)
1403 || ' format mapping..');
1404 --
1405 END LOOP; --for each case
1406 --
1407 ------------------------------------------------------------------------------
1408 --
1409 -- If no exceptions raised during formatting, commit new structures.
1410 --
1411 hr_utility.trace('Commiting structures...');
1412 COMMIT;
1413 --
1414 EXCEPTION --Andy Taylor's Generic Exception Handler
1415 WHEN OTHERS THEN
1416 --
1417 hr_utility.trace( l_message||' - ORA '||to_char(SQLCODE));
1418 hr_utility.trace(fnd_flex_val_api.message);
1419 --
1420 --
1421 --
1422 hr_utility.trace('END REPORT DEFINITION PROCESS----------------------------------');
1423 end setup;
1424 --
1425 end pay_1099R_data;