1 PACKAGE BODY pay_1099R_formula_driver AS
2 /* $Header: py1099fd.pkb 115.5 99/07/17 05:40:44 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_formula_driver
22
23 Description : Allows the creation of formulas which are neccessary
24 for 1099R reporting on a federal level. This set of
25 formulas includes those for US,STATE,WV,IN,NY
26
27 Uses : For any 1099R installation.
28
29 Change List
30 -----------
31 Date Name Vers Bug No Description
32 ---- ---- ---- ------ -----------
33 05-NOV-96 GPERRY 40.0 Created.
34 18-NOV-96 HEKIM 40.1 Added State specific formulas.
35 05-MAR-96 HEKIM 40.2 Added WV,IN, NY formulas
36 05-OCT-98 AHANDA 40.4 Added formulas for New Federal Format
37 20-OCT-98 AHANDA 40.5 Added formulas for Old State Format
38 14-DEC-98 AHANDA 40.6/110.2 Changed formula name for Non Federal States.
39 26-JAN-99 AHANDA 40.7/110.3 Changed script to delete only formulas which
40 it is creating. Moved delete statement
41 inside the loop.
42 16-jun-99 achauhan 110.5 Changed dbms_output to
43 hr_utility.trace
44 */
45 --
46 /* */
47 ----------------------------------------------------------------------------------------
48 -- Name
49 -- setup
50 -- Purpose
51 -- Seeds formula data in ff_formulas_f table for 1099R federal formulas.
52 -- Arguments
53 -- None
54 -- Notes
55 ----------------------------------------------------------------------------------------
56 --
57 PROCEDURE Setup IS
58 --
59 -- Define table structures to hold parameter details.
60 --
61 -- note: we are only inserting into the ff_formulas_f table.
62 -- The only fields that we have to cater for are the following :
63 -- LEGISLATION_CODE
64 -- FORMULA_TYPE_ID
65 -- FORMULA_NAME
66 -- DESCRIPTION
67 --
68 l_formula_name_table char80_data_table;
69 l_description_table char240_data_table;
70 --
71 l_formula_id number;
72 l_legislation_code varchar2(30) := 'US';
73 l_formula_type_id number;
74 --
75 -- The l_case_count variable is used to keep count of the number of
76 -- formulas we are seeding. NOTE - this must be changed if formulae
77 -- are to be added or removed from the seeding process.
78 --
79 l_case_count number := 44;
80 --
81 -- Note that l_message is used throughout this module to hold the
82 -- message which will be displayed if an exception is raised.
83 --
84 l_message VARCHAR2(200);
85 --
86 cursor c_formula_type_id is
87 select formula_type_id
88 from ff_formula_types fft
89 where fft.formula_type_name = 'Oracle Payroll';
90 --
91 BEGIN
92 -- **************************************************************************
93 -- PL/SQL TABLE SEEDING
94 -- **************************************************************************
95 --
96 -- This part of the procedure is where we seed all of the tables that we
97 -- use to insert into the FF_FORMULAS_F table. This will allow the insertion
98 -- of further formulas to be that much more simpler.
99 --
100 -- First we seed the formulas table, these are the names of all the formulas
101 -- that we plan to seed.
102 --
103 hr_utility.trace('Seeding formula name table');
104 --
105 l_message := 'Seeding formula name table';
106 --
107 l_formula_name_table(1) := 'US_1099R_FILE_TOTALS';
108 l_formula_name_table(2) := 'US_1099R_PAYEES';
109 l_formula_name_table(3) := 'US_1099R_PAYER';
110 l_formula_name_table(4) := 'US_1099R_PAYER_TOTALS';
111 l_formula_name_table(5) := 'US_1099R_TRANSMITTER';
112 l_formula_name_table(6) := 'US_1099R_STATE_TOTALS';
113 l_formula_name_table(7) := 'STATE_1099R_PAYEES';
114 l_formula_name_table(8) := 'STATE_1099R_PAYER';
115 l_formula_name_table(9) := 'WV_1099R_PAYEES';
116 l_formula_name_table(10) := 'WV_1099R_PAYER';
117 l_formula_name_table(11) := 'IN_1099R_EMPLOYER';
118 l_formula_name_table(12) := 'IN_1099R_SUPPLEMENTAL';
119 l_formula_name_table(13) := 'IN_1099R_FINAL';
120 l_formula_name_table(14) := 'IN_1099R_TRANSMITTER';
121 l_formula_name_table(15) := 'IN_1099R_TOTAL';
122 l_formula_name_table(16) := 'NY_1099R_TRANSMITTER';
123 l_formula_name_table(17) := 'NY_1099R_EMPLOYER';
124 l_formula_name_table(18) := 'NY_1099R_EMPLOYEE';
125 l_formula_name_table(19) := 'NY_1099R_TOTAL';
126 l_formula_name_table(20) := 'NY_1099R_FINAL';
127
128 l_formula_name_table(21) := 'US_1099R_NFED_TRANSMITTER';
129 l_formula_name_table(22) := 'US_1099R_NFED_STATE_TOTALS';
130 l_formula_name_table(23) := 'US_1099R_NFED_PAYER_TOTALS';
131 l_formula_name_table(24) := 'US_1099R_NFED_FILE_TOTALS';
132
133 /* Formula for Old Magnetic Reports(Retry) */
134 l_formula_name_table(25) := 'US_OLD_1099R_FILE_TOTALS';
135 l_formula_name_table(26) := 'US_OLD_1099R_PAYEES';
136 l_formula_name_table(27) := 'US_OLD_1099R_PAYER';
137 l_formula_name_table(28) := 'US_OLD_1099R_PAYER_TOTALS';
138 l_formula_name_table(29) := 'US_OLD_1099R_TRANSMITTER';
139 l_formula_name_table(30) := 'US_OLD_1099R_STATE_TOTALS';
140 l_formula_name_table(31) := 'STATE_OLD_1099R_PAYEES';
141 l_formula_name_table(32) := 'STATE_OLD_1099R_PAYER';
142 l_formula_name_table(33) := 'WV_OLD_1099R_PAYEES';
143 l_formula_name_table(34) := 'WV_OLD_1099R_PAYER';
144 l_formula_name_table(35) := 'IN_OLD_1099R_EMPLOYER';
145 l_formula_name_table(36) := 'IN_OLD_1099R_SUPPLEMENTAL';
146 l_formula_name_table(37) := 'IN_OLD_1099R_FINAL';
147 l_formula_name_table(38) := 'IN_OLD_1099R_TRANSMITTER';
148 l_formula_name_table(39) := 'IN_OLD_1099R_TOTAL';
149 l_formula_name_table(40) := 'NY_OLD_1099R_TRANSMITTER';
150 l_formula_name_table(41) := 'NY_OLD_1099R_EMPLOYER';
151 l_formula_name_table(42) := 'NY_OLD_1099R_EMPLOYEE';
152 l_formula_name_table(43) := 'NY_OLD_1099R_TOTAL';
153 l_formula_name_table(44) := 'NY_OLD_1099R_FINAL';
154
155 --
156 --
157 -- Now we seed the formula descriptions of the formulas that we plan to seed.
158 --
159 hr_utility.trace('Seeding formula description table');
160 --
161 l_message := 'Seeding formula table descriptions';
162 --
163 l_description_table(1) := '1099R File totals formula for retirement processing';
164 l_description_table(2) := '1099R Payees information formula';
165 l_description_table(3) := '1099R Payer information formula';
166 l_description_table(4) := '1099R Payer totals formula';
167 l_description_table(5) := '1099R Transmitter formula';
168 l_description_table(6) := '1099R State totals process formula';
169 l_description_table(7) := 'State 1099R Payees information formula';
170 l_description_table(8) := 'State 1099R Payer information formula';
171 l_description_table(9) := 'West Virginia 1099R Payees information formula';
172 l_description_table(10) := 'West Virginia 1099R Payer information formula';
173 l_description_table(11) := 'Indiana 1099R Employer Record formula';
174 l_description_table(12) := 'Indiana 1099R Supplemental Record formula';
175 l_description_table(13) := 'Indiana 1099R Final Record formula ';
176 l_description_table(14) := 'Indiana 1099R Transmitter Record formula ';
177 l_description_table(15) := 'Indiana 1099R Total Record formula ';
178 l_description_table(16) := 'NY 1099R Transmitter formula';
179 l_description_table(17) := 'NY 1099R Employer Record formula';
180 l_description_table(18) := 'NY 1099R Employee Record formula';
181 l_description_table(19) := 'NY 1099R Total Record formula ';
182 l_description_table(20) := 'NY 1099R Final Record formula ';
183
184 l_description_table(21) := '1099R Transmitter formula for Fed Report (New Format)';
185 l_description_table(22) := '1099R State totals process formula for Fed Report (New Format)';
186 l_description_table(23) := '1099R Payer totals formula for Fed Report (New Format)';
187 l_description_table(24) := '1099R File totals formula for Ferdal Report (New Format)';
188
189 l_description_table(25) := 'Old 1099R File totals formula for retirement processing';
190 l_description_table(26) := 'Old 1099R Payees information formula';
191 l_description_table(27) := 'Old 1099R Payer information formula';
192 l_description_table(28) := 'Old 1099R Payer totals formula';
193 l_description_table(29) := 'Old 1099R Transmitter formula';
194 l_description_table(30) := 'Old 1099R State totals process formula';
195 l_description_table(31) := 'Old State 1099R Payees information formula';
196 l_description_table(32) := 'Old State 1099R Payer information formula';
197 l_description_table(33) := 'Old West Virginia 1099R Payees information formula';
198 l_description_table(34) := 'Old West Virginia 1099R Payer information formula';
199 l_description_table(35) := 'Old Indiana 1099R Employer Record formula';
200 l_description_table(36) := 'Old Indiana 1099R Supplemental Record formula';
201 l_description_table(37) := 'Old Indiana 1099R Final Record formula ';
202 l_description_table(38) := 'Old Indiana 1099R Transmitter Record formula ';
203 l_description_table(39) := 'Old Indiana 1099R Total Record formula ';
204 l_description_table(40) := 'Old NY 1099R Transmitter formula';
205 l_description_table(41) := 'Old NY 1099R Employer Record formula';
206 l_description_table(42) := 'Old NY 1099R Employee Record formula';
207 l_description_table(43) := 'Old NY 1099R Total Record formula ';
208 l_description_table(44) := 'Old NY 1099R Final Record formula ';
209
210 --
211 --
212 -- **************************************************************************
213 -- SET FORMULA_TYPE_ID
214 -- **************************************************************************
215 --
216 hr_utility.trace('Setting formula type id');
217 --
218 l_message := 'Setting formula type id';
219 --
220 -- Steps to set this variable are as follows :
221 -- 1) Open previously defined cursor
222 -- 2) Attempt to fetch row
223 -- 3) If row not found then raise error
224 -- 4) If not then continue processing
225 --
226 open c_formula_type_id;
227 --
228 fetch c_formula_type_id into l_formula_type_id;
229 --
230 if c_formula_type_id%notfound then
231 --
232 -- Raise error as formula id can not be found
233 --
234 raise NO_DATA_FOUND;
235 --
236 end if;
237 --
238 close c_formula_type_id;
239 --
240 -- *************************************************************************
241 -- SEED FF_FORMULAS_F TABLE
242 -- *************************************************************************
243 --
244 hr_utility.trace('Seeding ff_formulas_f table');
245 --
246 l_message := 'Seeding ff_formulas_f table';
247 --
248 FOR l_count in 1..l_case_count LOOP
249
250 -- **************************************************************************
251 -- DELETION STEPS
255 -- FF_FORMULAS_F, FF_FDI_USAGES_F and FF_COMPILED_INFO_F tables.
252 -- **************************************************************************
253 --
254 -- This part of the procedure deletes all previous definitions from the
256 --
257 l_message := 'Attempting to delete previous definitions of 1099R federal
258 formulas from FF_COMPILED_INFO_F for formula - ' ||
259 l_formula_name_table(l_count);
260 --
261 -- Delete all cases where 1099R Federal Formulas have been compiled
262 -- from FF_COMPILED_INFO_F
263 --
264 delete from ff_compiled_info_f fci
265 where fci.formula_id in (select ff.formula_id
266 from ff_formulas_f ff
267 where formula_name = l_formula_name_table(l_count));
268 --
269 l_message := 'Attempting to delete previous definitions of 1099R federal
270 formulas from FF_FDI_USAGES_F for formula - ' ||
271 l_formula_name_table(l_count);
272 --
273 -- Delete all cases where 1099R Federal Formulas have been compiled
274 -- from FF_FDI_USAGES_F
275 --
276 delete from ff_fdi_usages_f
277 where formula_id in (select formula_id
278 from ff_formulas_f
279 where formula_name = l_formula_name_table(l_count));
280 --
281 hr_utility.trace('Attempting to delete previous definitions of 1099R federal
282 formulas from FF_FORMULAS_F');
283 --
284 l_message := 'Attempting to delete previous definitions of 1099R federal
285 formulas from FF_FORMULAS_F for formula name - '||
286 l_formula_name_table(l_count);
287
288 --
289 -- Delete all cases where 1099R Federal Formulas have been seeded previously
290 -- in the FF_FORMULAS_F table.
291 --
292 delete from ff_formulas_f ff
293 where ff.formula_name = l_formula_name_table(l_count);
294 --
295 --
296 --
297 hr_utility.trace('Getting sequence for next record to seed in
298 ff_formulas_f');
299 --
300 l_message := 'Getting sequence for next record to seed in ff_formulas_f';
301 --
302 select ff_formulas_s.nextval
303 into l_formula_id
304 from sys.dual;
305 --
306 hr_utility.trace('Seeding formula : '||l_formula_name_table(l_count));
307 --
308 l_message := 'Seeding formula : '||l_formula_name_table(l_count);
309 --
310 insert into ff_formulas_f
311 (FORMULA_ID,
312 EFFECTIVE_START_DATE,
313 EFFECTIVE_END_DATE,
314 BUSINESS_GROUP_ID,
315 LEGISLATION_CODE,
316 FORMULA_TYPE_ID,
317 FORMULA_NAME,
318 DESCRIPTION,
319 FORMULA_TEXT,
320 STICKY_FLAG,
321 LAST_UPDATE_DATE,
322 LAST_UPDATED_BY,
323 LAST_UPDATE_LOGIN,
324 CREATED_BY,
325 CREATION_DATE)
326 values
327 (l_formula_id,
328 pay_1099R_formula_driver.c_start_of_time,
329 pay_1099R_formula_driver.c_end_of_time,
330 null,
331 l_legislation_code,
332 l_formula_type_id,
333 l_formula_name_table(l_count),
334 l_description_table(l_count),
335 null,
336 null,
337 null,
338 null,
339 null,
340 null,
341 null);
342 --
343 hr_utility.trace('Seeding '||l_formula_name_table(l_count));
344 --
345 end loop;
346 --
347 hr_utility.trace('Successful');
348 --
349 COMMIT;
350 --
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN
353 --
354 hr_utility.trace('ERROR : Cannot get formula type id for Application');
355 --
356 WHEN OTHERS THEN
357 --
358 hr_utility.trace('ERROR : ' ||l_message||' - ORA '||to_char(SQLCODE));
359 hr_utility.trace('ERROR : ' ||l_message||' - ORA '||to_char(SQLCODE));
360 --
361 END setup;
362 --
363 END pay_1099R_formula_driver;
[Home] [Help]