1 PACKAGE pay_us_1099r_mag_reporting AUTHID CURRENT_USER AS
2 /* $Header: pyyep99r.pkh 120.3.12020000.2 2012/10/30 14:23:56 pkoduri ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_1099r_mag_reporting
21
22 Description : Generate 1099R end of year magnetic reports according to
23 US legislative requirements.
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31 01-OCT-98 AHANDA 40.0 Created.
32 20-DEC-98 AHANDA 40.4 Changed procedure
33 US_1099R_State_Process for Changed
34 to SC format. They have a K
35 record if SIT > 0
36 18-FEB-99 AHANDA 40.5 Changed the order by clause for
37 the cursor us_1099r_payee and
38 state_1099r_payee
39 from 10, 12, 14 to 12, 14, 16
40 18-FEB-99 AHANDA 40.6 Added national_identifier to the
41 cursor us_1099r_payee and
42 state_1099r_payee
43 and added it in the order by clause
44 28-MAY-99 rthakur 40.7 Added a check for the existence of
45 a run result
46 in the same jurisdiction code in the
47 US_1099R_Payee and STATE_1099R_Payee
48 cursors. In the 'T' Record the
49 transmitter information is now
50 mandatory so removed the nvl.
51 27-JUN-99 rthakur 40.8 Changed the check of the run result
52 code to match the check in the view
53 PAY_US_EARNINGS_AMOUNTS_V.
54 29-JUN-99 rthakur 40.10 Removed the check for the existence
55 of a run result
56 in the US_1099R_Payee and
57 STATE_1099R_Payee cursors as the year
58 end archiver takes care of this logic
59 01-JUL-99 rthakur 40.11 Modified the driving cursors to
60 utilize the archived jurisdictions,
61 through the pay_us_arch_mag_xxxx_v
62 views.
63 20-JUL-99 rthakur 40.13 Made major changes to the driving
64 cursors to report off of the archive
65 items.
66 17-AUG-99 rthakur 40.14 Added the check for assignment
67 actions in the Payer cursor.
68 26-AUG-99 rthakur 40.15 Modified the Payer cursor, previously
69 we would check the assign actions
70 only for the Transmitter, we need to
71 check for all.
72 30-AUG-99 rthakur 40.16/17 Fixed the Payer cursor it would not
73 pick up non-TCC GRE's.
74 21-SEP-99 rthakur 115.1 Took the 110.7 of r11 and made the
75 fnd_date changes.
76 01-dec-00 djoshi 115.2 Modified state_1099r_payee
77 cursor modified for indiana
78 27-AUG-01 ekim 40.20 Added Vendor Information parameters
79 in us_1099r_transmitter cursor.
80 14-NOV-01 jgoswami 115.4 Added MMREF Cursors for State 1099r
81 Submitter, Payer and Payee.
82 30-NOV-01 jgoswami 115.5 Added dbdrv command
83 07-DEC-01 jgoswami 115.6 Added fnd_date.date_to_canonical to
84 MMREF Cursors.
85 15-AUG-2002 ahanda 115.7 Changed transmitter cursor to remove
86 cartesian join
87 11-nov-2002 djoshi 115.8 Added parameter to get contact Email
88 Address to transmitter Cursor
89 'US_1099R_TRANSMITTER'
90 02-dec-2002 djoshi 115.9 Added nvl to the paramter.
91 02-dec-2002 djoshi 115.10 Added nocopy to the file
92 05-dec-2002 djoshi 115.11 Added combined filer for ID and NE
93 07-DEC-2002 ahanda 115.12 Changed from clause to join to main
94 table instead of secure views.
95 30-OCT-2003 jgoswami 115.13 3057115 Added parameter to get payroll_action_id
96 of 1099r mag to transmitter Cursor
97 'US_1099R_TRANSMITTER'.It is used in the
98 get_cprog_parameter_value formula function
99 04-NOV-2003 jgoswami 115.14 3113962 added transfer_assignment_action_id parameter
100 to the cursor US_1099R_Payee, STATE_1099R_Payee
101 09-DEC-2003 jgoswami 115.15 Modified state_1099r_mmref_payer cursor
102 added transfer_emp_code parameter as the
103 common formula used for 1099r and W2
104 MMRF_EMPLOYER_RECORD was modified for W2
105 18-MAR-2011 asgugupt 115.16 Modified us_1099r_transmitter cursor
106 12-JAN-2012 pkoduri 115.17 13408243 Modified state_1099r_mmref_transmitter cursor
107 so that it only gets the state code out of the
108 transfer_state parameter.
109 Added transfer code as PA_1099R for this bug.
110 30-OCT-2012 pkoduri 115.18 14286448 Corrections for GRE name length issue.
111 ***************************************************************************
112 Cursor for the Fast Formulas are defined below
113
114 US_1099R_TRANSMITTER - For Transmitter Block
115 US_1099R_Payer - For Payer Block
116 US_1099R_Payee - For Payee Block (for Fedral 1099R)
117 STATE_1099R_Payee - For Payee Block (for State 1099R)
118 US_1099R_State_Process - Allow generation of state totals
119
120 ***************************************************************************/
121
122
123 -- 'level_cnt' will allow the cursors to select function results,
124 -- whether it is a standard fuction such as to_char or a function
125 -- defined in a package (with the correct pragma restriction).
126
127 level_cnt NUMBER;
128
129
130 -- Cursor to set up tax unit and jurisdiction contexts for each transmitter rec.
131 -- When we run for a State the Jurisdiction is set for the context otherwise it is set
132 -- to a dummy value for federal.
133
134 Cursor us_1099r_transmitter is
135 select 'TAX_UNIT_ID=C', ffaic.context, -- hoi.organization_id,
136 'JURISDICTION_CODE=C', 'NOT_USED_FOR_FED',
137 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id, -- YREND ARCHIVER
138 'TRANSFER_TRANSMITTER_CONTROL_CODE=P', fai2.value, -- hoi.org_information2,
139 'TRANSFER_CONTACT_NAME=P', hoi.org_information9, -- nvl(hoi.org_information9, '$$'),
140 'TRANSFER_CONTACT_NO=P', hoi.org_information10, -- nvl(hoi.org_information10, '$$')
141 'TRANSFER_CONTACT_EMAIL=P', nvl(hoi.org_information20,' '),
142 'TRANSFER_YREND_PAY_ACT_ID=P', ppa.payroll_action_id, -- YREND ARCHIVER
143 'TRANSFER_PAYROLL_ACTION_ID=P', ppa2.payroll_action_id, -- 1099R Payroll Action Id
144 'VENDOR_INDICATOR=P', hoi.org_information11,
145 'VENDOR_NAME=P', nvl(hoi.org_information12,' '),
146 'VENDOR_ADDRESS=P', nvl(hoi.org_information13,' '),
147 'VENDOR_CITY=P', nvl(hoi.org_information14,' '),
148 'VENDOR_STATE=P', nvl(hoi.org_information15,' '),
149 'VENDOR_ZIP=P', nvl(hoi.org_information16,' '),
150 'VENDOR_CONTACT_NAME=P', nvl(hoi.org_information17,' '),
151 'VENDOR_CONTACT_PHONE=P', nvl(hoi.org_information18,' '),
152 'VENDOR_CONTACT_EMAIL=P', nvl(hoi.org_information19,' ')
153 from hr_organization_information hoi,
154 ff_contexts ffc,
155 ff_user_entities fue, -- A_US_1099R_TRANSMITTER_INDICATOR
156 ff_user_entities fue2, -- A_US_1099R_TRANSMITTER_CODE
157 ff_archive_item_contexts ffaic,
158 pay_payroll_actions ppa, -- YREND Preprocessor
159 pay_payroll_actions ppa2, -- 1099R Payroll Action Id
160 ff_archive_items fai, -- Transmitter Indicator
161 ff_archive_items fai2 -- Transmitter code
162 where ppa2.payroll_action_id
163 = Pay_Magtape_Generic.Get_Parameter_Value('TRANSFER_PAYROLL_ACTION_ID')
164 and ppa.report_type = 'YREND'
165 and ppa.effective_date = ppa2.effective_date
166 and ppa.business_group_id + 0 = ppa2.business_group_id
167 and ppa.action_status = 'C'
168 and rtrim(ltrim(Pay_Mag_Utils.Get_Parameter('TRANSFER_TRANS_LEGAL_CO_ID','TRANSFER_ALL_PAYERS',ppa2.legislative_parameters))) =
169 ffaic.context
170 and ppa.payroll_action_id = fai.context1
171 and fai.context1 = fai2.context1
172 and fue2.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
173 and fue2.user_entity_id = fai2.user_entity_id
174 and fue.user_entity_name = 'A_US_1099R_TRANSMITTER_INDICATOR'
175 and fue.user_entity_id = fai.user_entity_id
176 and fai.value ='Y'
177 and ffc.context_name = 'TAX_UNIT_ID'
178 and ffc.context_id = ffaic.context_id
179 and ffaic.archive_item_id = fai.archive_item_id
180 /* changed back to original due to CBO issue */
181 /* and hoi.organization_id = ffaic.context */
182 /* Commented to avoid cartesian join issue */
183 /* and hoi.organization_id
184 = Pay_Magtape_Generic.Get_Parameter_Value('TRANSFER_TRANS_LEGAL_CO_ID') */
185 and hoi.organization_id = rtrim(ltrim(Pay_Mag_Utils.Get_Parameter(
186 'TRANSFER_TRANS_LEGAL_CO_ID',
187 'TRANSFER_ALL_PAYERS',
188 ppa2.legislative_parameters)))
189 and hoi.org_information_context = '1099R Magnetic Report Rules'
190 -- and hoi.org_information1 = 'Y'
191 and Pay_Magtape_Generic.Get_Parameter_Value ('TRANSFER_STATE') ='FED'
192 UNION
193 select 'TAX_UNIT_ID=C', ffaic.context, -- hoi.organization_id,
194 'JURISDICTION_CODE=C', psr.jurisdiction_code,
195 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id, -- YREND ARCHIVER
196 'TRANSFER_TRANSMITTER_CONTROL_CODE=P', fai2.value, -- hoi.org_information2,
197 'TRANSFER_CONTACT_NAME=P', hoi.org_information9, -- nvl(hoi.org_information9,'$$'),
198 'TRANSFER_CONTACT_NO=P', hoi.org_information10, -- nvl(hoi.org_information10,'$$')
199 'TRANSFER_CONTACT_EMAIL=P', nvl(hoi.org_information20,' '),
200 'TRANSFER_YREND_PAY_ACT_ID=P', ppa.payroll_action_id, -- YREND ARCHIVER
201 'TRANSFER_PAYROLL_ACTION_ID=P', ppa2.payroll_action_id, -- 1099R Payroll Action Id
202 'VENDOR_INDICATOR=P', hoi.org_information11,
203 'VENDOR_NAME=P', nvl(hoi.org_information12,' '),
204 'VENDOR_ADDRESS=P', nvl(hoi.org_information13,' '),
205 'VENDOR_CITY=P', nvl(hoi.org_information14,' '),
206 'VENDOR_STATE=P', nvl(hoi.org_information15,' '),
207 'VENDOR_ZIP=P', nvl(hoi.org_information16,' '),
208 'VENDOR_CONTACT_NAME=P', nvl(hoi.org_information17,' '),
209 'VENDOR_CONTACT_PHONE=P', nvl(hoi.org_information18, ' '),
210 'VENDOR_CONTACT_EMAIL=P', nvl(hoi.org_information19,' ')
211 from hr_organization_information hoi,
212 pay_state_rules psr,
213 ff_contexts ffc,
214 ff_user_entities fue, -- A_US_1099R_TRANSMITTER_INDICATOR
215 ff_user_entities fue2, -- A_US_1099R_TRANSMITTER_CODE
216 ff_archive_item_contexts ffaic,
217 pay_payroll_actions ppa, -- YREND Preprocessor
218 pay_payroll_actions ppa2, -- 1099R Payroll Action Id
219 ff_archive_items fai, -- INDICATOR
220 ff_archive_items fai2 -- Transmitter code
221 where ppa2.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value('TRANSFER_PAYROLL_ACTION_ID')
222 and ppa.report_type = 'YREND'
223 and ppa.effective_date = ppa2.effective_date
224 and ppa.business_group_id + 0 = ppa2.business_group_id
225 and ppa.action_status = 'C'
226 and rtrim(ltrim(Pay_Mag_Utils.Get_Parameter('TRANSFER_TRANS_LEGAL_CO_ID','TRANSFER_ALL_PAYERS',ppa2.legislative_parameters))) =
227 ffaic.context
228 and ppa.payroll_action_id = fai.context1
229 and fai.context1 = fai2.context1
230 and fue2.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
231 and fue2.user_entity_id = fai2.user_entity_id
232 and fue.user_entity_name = 'A_US_1099R_TRANSMITTER_INDICATOR'
233 and fue.user_entity_id = fai.user_entity_id
234 and fai.value ='Y'
235 and ffc.context_name = 'TAX_UNIT_ID'
236 and ffc.context_id = ffaic.context_id
237 and ffaic.archive_item_id = fai.archive_item_id
238 /* changed back to original due to CBO issue */
239 /* and hoi.organization_id = ffaic.context */
240 /* Commented to avoid cartesian join issue */
241 /* and hoi.organization_id
242 = Pay_Magtape_Generic.Get_Parameter_Value('TRANSFER_TRANS_LEGAL_CO_ID') */
243 and hoi.organization_id = rtrim(ltrim(Pay_Mag_Utils.Get_Parameter(
244 'TRANSFER_TRANS_LEGAL_CO_ID',
245 'TRANSFER_ALL_PAYERS',
246 ppa2.legislative_parameters)))
247 and hoi.org_information_context = '1099R Magnetic Report Rules'
248 -- and hoi.org_information1 = 'Y'
249 and psr.state_code = Pay_Magtape_Generic.Get_Parameter_Value('TRANSFER_STATE');
250
251
252 -- Cursor to set up the tax unit context for each employer being reported. Sets
253 -- up a parameter holding the tax unit identifier which can then be used by
254 -- subsequent cursors to restrict to employees within the employer.
255 -- Added an exists clause to check for the existance of an assignment action for
256 -- the Payer GRE. A GRE can be the transmitter without any employees. In that case
257 -- we don't want to print any payer records.
258 -- We have to have all the joins to ppa and ppa2 otherwise we will pick up duplicate rows.
259 -- When we pull from ff_archive_items we have to make sure the context1 is the YREND archiver
260 -- for the GRE othwerise we get duplicates.
261
262 Cursor US_1099R_Payer is
263
264 select 'TAX_UNIT_ID=C' , ffaic2.context, -- hoi.organization_id,
265 'PAYROLL_ACTION_ID=C' , ffai2.context1,
266 'TAX_UNIT_ID=P' , ffaic2.context, -- hoi.organization_id,
267 'TRANSFER_TAX_UNIT_NAME=P', substr(ffai3.value,1,80) -- hou.name Bug# 14286448 Gre length issue
268 from -- hr_organization_information hoi,
269 -- hr_organization_units hou,
270 ff_contexts ffc,
271 ff_user_entities ffue,
272 ff_user_entities ffue2,
273 ff_archive_items ffai, -- TCC
274 ff_archive_items ffai2, -- Tax Unit Id
275 ff_archive_items ffai3, -- Tax Unit Name
276 ff_archive_item_contexts ffaic,
277 ff_archive_item_contexts ffaic2,
278 pay_payroll_actions ppa,
279 pay_payroll_actions ppa2
280 where ffai.context1 = Pay_Magtape_Generic.Get_Parameter_value('TRANSFER_YREND_PAY_ACT_ID')
281 and ffai.archive_item_id = ffaic.archive_item_id
282 and ffue.user_entity_id = ffai.user_entity_id
283 and ffue.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
284 and ffc.context_name = 'TAX_UNIT_ID'
285 and ffaic.context_id = ffc.context_id
286 and ffai2.user_entity_id = ffai.user_entity_id
287 and ffai2.value = ffai.value
288 and ffai2.archive_item_id = ffaic2.archive_item_id
289 and ffai2.context1 = ppa.payroll_action_id
290 and ppa2.payroll_action_id = ffai.context1
291 and ppa.report_type = 'YREND'
292 and ppa2.business_group_id + 0 = ppa.business_group_id + 0
293 and ppa2.effective_date = ppa.effective_date
294 and ffue2.user_entity_name = 'A_TAX_UNIT_NAME'
295 and ffue2.user_entity_id = ffai3.user_entity_id
296 and ffai3.context1 = ffai2.context1
297 and ffaic2.context_id = ffc.context_id
298 and (Pay_Magtape_Generic.Get_Parameter_value('TRANSFER_ALL_PAYERS') = 'Y'
299 OR ffaic2.context = Pay_Magtape_Generic.Get_Parameter_Value('TRANSFER_TRANS_LEGAL_CO_ID'))
300 and exists (select 'Y' from pay_assignment_actions paa
301 where paa.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value('TRANSFER_PAYROLL_ACTION_ID')
302 and paa.tax_unit_id = ffaic2.context)
303 order by ffai3.value;
304
305
306 --Cursor to set up the assignment_action_id, assignment_id, and date_earned
307 --and Jurisdiction contexts for an employee. The date_earned context is set
308 --to be the least of the end of the period being reported and the maximum
309 --end date of the assignment.
310 --This ensures that personal information is current relative to the period
311 --being reported on.
312
313 Cursor us_1099r_payee is
314 select 'ASSIGNMENT_ACTION_ID=C' , pai.locked_action_id, -- YREND Pre-Processor aaid
315 'ASSIGNMENT_ID=C' , paa.assignment_id,
316 'JURISDICTION_CODE=C' , pec.jurisdiction_code,
317 'TRANSFER_JURISDICTION_CODE=P' , pec.jurisdiction_code,
318 'DATE_EARNED=C' , fnd_date.date_to_canonical(Pay_Magtape_Generic.Date_Earned
319 (ppa.effective_date,
320 paa.assignment_id)),
321 'TRANSFER_LAST_NAME=P' , pay_mag_utils.get_parameter('TRANSFER_LN','TRANSFER_FN', ffai.value),
322 'TRANSFER_FIRST_NAME=P' , pay_mag_utils.get_parameter('TRANSFER_FN','TRANSFER_MN', ffai.value),
323 'TRANSFER_MIDDLE_NAMES=P' , pay_mag_utils.get_parameter('TRANSFER_MN','TRANSFER_SSN', ffai.value),
324 'TRANSFER_NATIONAL_IDENTIFIER=P' , pay_mag_utils.get_parameter('TRANSFER_SSN', '', ffai.value),
325 'TRANSFER_ASSIGNMENT_ACTION_ID=P' , pai.locked_action_id -- YREND Pre-Processor aaid
326 from pay_us_arch_mag_county_v pec, -- pay_us_emp_county_tax_rules_f pec,
327 per_all_people_f ppf,
328 ff_user_entities ffue,
329 ff_archive_items ffai,
330 pay_action_interlocks pai,
331 per_all_assignments_f paf,
332 pay_assignment_actions paa,
333 pay_payroll_actions ppa
334 where pai.locking_action_id = paa.assignment_action_id
335 and ppa.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value
336 ('TRANSFER_PAYROLL_ACTION_ID')
337 and ppa.payroll_action_id = paa.payroll_action_id
338 and paf.assignment_id = paa.assignment_id
339 and paa.tax_unit_id = Pay_Magtape_Generic.Get_Parameter_Value
340 ('TAX_UNIT_ID')
341 and ffue.user_entity_name = 'A_PER_1099R_NAME'
342 and ffue.user_entity_id = ffai.user_entity_id
343 and ffai.context1 = pai.locked_action_id -- YREND pre-process aaid
344 -- and paa.assignment_action_id = pai.locking_action_id /* duplicate of the first where statement */
345 and ppf.person_id = paf.person_id
346 -- and pec.assignment_id = paa.assignment_id
347 and pec.assignment_action_id = pai.locked_action_id
348 -- and pay_magtape_generic.date_earned(ppa.effective_date, paa.assignment_id) between
349 -- pec.effective_start_date AND pec.effective_end_date
350 and Pay_Mag_Utils.Date_Earned(ppa.effective_date,
351 paa.assignment_id,
352 paf.effective_start_date,
353 paf.effective_end_date,
354 ppf.effective_start_date,
355 ppf.effective_end_date) = 1
356 UNION ALL
357 select 'ASSIGNMENT_ACTION_ID=C' , pai.locked_action_id,
358 'ASSIGNMENT_ID=C' , paa.assignment_id,
359 'JURISDICTION_CODE=C' , pes.jurisdiction_code,
360 'TRANSFER_JURISDICTION_CODE=P' , pes.jurisdiction_code,
361 'DATE_EARNED=C' , fnd_date.date_to_canonical(Pay_Magtape_Generic.Date_Earned
362 (ppa.effective_date,
363 paa.assignment_id)),
364 'TRANSFER_LAST_NAME=P' , pay_mag_utils.get_parameter('TRANSFER_LN','TRANSFER_FN', ffai.value),
365 'TRANSFER_FIRST_NAME=P' , pay_mag_utils.get_parameter('TRANSFER_FN','TRANSFER_MN', ffai.value),
366 'TRANSFER_MIDDLE_NAMES=P' , pay_mag_utils.get_parameter('TRANSFER_MN','TRANSFER_SSN', ffai.value),
367 'TRANSFER_NATIONAL_IDENTIFIER=P' , pay_mag_utils.get_parameter('TRANSFER_SSN', '', ffai.value),
368 'TRANSFER_ASSIGNMENT_ACTION_ID=P' , pai.locked_action_id -- YREND Pre-Processor aaid
369 from pay_us_arch_mag_state_v pes, -- pay_us_emp_state_tax_rules_f pes,
370 per_all_people_f ppf,
371 ff_user_entities ffue,
372 ff_archive_items ffai,
373 pay_action_interlocks pai,
374 per_all_assignments_f paf,
375 pay_assignment_actions paa,
376 pay_payroll_actions ppa
377 where pai.locking_action_id = paa.assignment_action_id
378 and ppa.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value
379 ('TRANSFER_PAYROLL_ACTION_ID')
380 and ppa.payroll_action_id = paa.payroll_action_id
381 and paf.assignment_id = paa.assignment_id
382 and paa.tax_unit_id = Pay_Magtape_Generic.Get_Parameter_Value
383 ('TAX_UNIT_ID')
384 and ffue.user_entity_name = 'A_PER_1099R_NAME'
385 and ffue.user_entity_id = ffai.user_entity_id
386 and ffai.context1 = pai.locked_action_id -- YREND pre-process aaid
387 -- and paa.assignment_action_id = pai.locking_action_id
388 and ppf.person_id = paf.person_id
389 -- and pes.assignment_id = paa.assignment_id
390 and pes.assignment_action_id = pai.locked_action_id
391 and not exists (select null from pay_us_arch_mag_county_v -- pay_us_emp_county_tax_rules_f
392 -- where state_code = pes.state_code
393 where substr(jurisdiction_code,1,2) = substr(pes.jurisdiction_code,1,2)
394 and assignment_action_id = pes.assignment_action_id )
395 -- and pay_magtape_generic.date_earned(ppa.effective_date, paa.assignment_id) between
396 -- pes.effective_start_date AND pes.effective_end_date
397 and Pay_Mag_Utils.Date_Earned(ppa.effective_date,
398 paa.assignment_id,
399 paf.effective_start_date,
400 paf.effective_end_date,
401 ppf.effective_start_date,
402 ppf.effective_end_date) = 1
403 ORDER BY 12, 14, 16, 18 ; --last_name, first_name, middle_names, national_identifier;
404
405
406
407 -- This is the state specific version of US_1099R_Payee
408 cursor state_1099r_payee is
409 select 'ASSIGNMENT_ACTION_ID=C' , pai.locked_action_id,
410 'ASSIGNMENT_ID=C' , paa.assignment_id,
411 'JURISDICTION_CODE=C' , pec.jurisdiction_code,
412 'TRANSFER_JURISDICTION_CODE=P' , pec.jurisdiction_code,
413 'DATE_EARNED=C' , fnd_date.date_to_canonical(Pay_Magtape_Generic.Date_Earned
414 (ppa.effective_date,
415 paa.assignment_id)),
416 'TRANSFER_LAST_NAME=P' , pay_mag_utils.get_parameter('TRANSFER_LN','TRANSFER_FN', ffai.value),
417 'TRANSFER_FIRST_NAME=P' , pay_mag_utils.get_parameter('TRANSFER_FN','TRANSFER_MN', ffai.value),
418 'TRANSFER_MIDDLE_NAMES=P' , pay_mag_utils.get_parameter('TRANSFER_MN','TRANSFER_SSN', ffai.value),
419 'TRANSFER_NATIONAL_IDENTIFIER=P' , pay_mag_utils.get_parameter('TRANSFER_SSN', '', ffai.value),
420 'TRANSFER_ASSIGNMENT_ACTION_ID=P' , pai.locked_action_id -- YREND Pre-Processor aaid
421 from pay_us_arch_mag_county_v pec, -- pay_us_emp_county_tax_rules_f pec,
422 per_all_people_f ppf,
423 ff_user_entities ffue,
424 ff_archive_items ffai,
425 pay_action_interlocks pai,
426 per_all_assignments_f paf,
427 pay_assignment_actions paa,
428 pay_payroll_actions ppa,
429 pay_us_states pus
430 where ppa.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value
431 ('TRANSFER_PAYROLL_ACTION_ID')
432 and ppa.payroll_action_id = paa.payroll_action_id
433 and paf.assignment_id = paa.assignment_id
434 and ppf.person_id = paf.person_id
435 and paa.assignment_action_id = pai.locking_action_id
436 -- and pec.assignment_id = paa.assignment_id
437 and pec.assignment_action_id = pai.locked_action_id
438 -- and pec.state_code = pus.state_code
439 and ffue.user_entity_name = 'A_PER_1099R_NAME'
440 and ffue.user_entity_id = ffai.user_entity_id
441 and ffai.context1 = pai.locked_action_id -- YREND pre-process aaid
442 and substr(pec.jurisdiction_code,1,2) = pus.state_code
443 and paa.tax_unit_id = Pay_Magtape_Generic.Get_Parameter_Value
444 ('TAX_UNIT_ID')
445 and pus.state_abbrev= Pay_Magtape_Generic.Get_Parameter_Value
446 ('TRANSFER_STATE')
447 -- and pay_magtape_generic.date_earned(ppa.effective_date, paa.assignment_id) between
448 -- pec.effective_start_date AND pec.effective_end_date
449 and Pay_Mag_Utils.Date_Earned(ppa.effective_date,
450 paa.assignment_id,
451 paf.effective_start_date,
452 paf.effective_end_date,
453 ppf.effective_start_date,
454 ppf.effective_end_date) = 1
455 /* if the state is Indiana we use this cursor for getting the Supplemental Records */
456 UNION ALL
457 select 'ASSIGNMENT_ACTION_ID=C' , pai.locked_action_id,
458 'ASSIGNMENT_ID=C' , paa.assignment_id,
459 'JURISDICTION_CODE=C' , pes.jurisdiction_code,
460 'TRANSFER_JURISDICTION_CODE=P' , pes.jurisdiction_code,
461 'DATE_EARNED=C' , fnd_date.date_to_canonical(Pay_Magtape_Generic.Date_Earned
462 (ppa.effective_date,
463 paa.assignment_id)),
464 'TRANSFER_LAST_NAME=P' , pay_mag_utils.get_parameter('TRANSFER_LN','TRANSFER_FN', ffai.value),
465 'TRANSFER_FIRST_NAME=P' , pay_mag_utils.get_parameter('TRANSFER_FN','TRANSFER_MN', ffai.value),
466 'TRANSFER_MIDDLE_NAMES=P' , pay_mag_utils.get_parameter('TRANSFER_MN','TRANSFER_SSN', ffai.value),
467 'TRANSFER_NATIONAL_IDENTIFIER=P' , pay_mag_utils.get_parameter('TRANSFER_SSN', '', ffai.value),
468 'TRANSFER_ASSIGNMENT_ACTION_ID=P' , pai.locked_action_id -- YREND Pre-Processor aaid
469 from pay_us_arch_mag_state_v pes, -- pay_us_emp_state_tax_rules_f pes,
470 per_all_people_f ppf,
471 ff_user_entities ffue,
472 ff_archive_items ffai,
473 pay_action_interlocks pai,
474 per_all_assignments_f paf,
475 pay_assignment_actions paa,
476 pay_payroll_actions ppa,
477 pay_us_states pus
478 where ppa.payroll_action_id = Pay_Magtape_Generic.Get_Parameter_Value
479 ('TRANSFER_PAYROLL_ACTION_ID')
480 and ppa.payroll_action_id = paa.payroll_action_id
481 and paf.assignment_id = paa.assignment_id
482 and ppf.person_id = paf.person_id
483 and paa.assignment_action_id = pai.locking_action_id
484 -- and pes.assignment_id = paa.assignment_id
485 and pes.assignment_action_id = pai.locked_action_id
486 -- and pes.state_code = pus.state_code
487 and substr(pes.jurisdiction_code,1,2) = pus.state_code
488 and ffue.user_entity_name = 'A_PER_1099R_NAME'
489 and ffue.user_entity_id = ffai.user_entity_id
490 and ffai.context1 = pai.locked_action_id -- YREND pre-process aaid
491 and not exists (select null from pay_us_arch_mag_county_v -- pay_us_emp_county_tax_rules_f
492 -- where state_code = pes.state_code
493 where substr(jurisdiction_code,1,2) = substr(pes.jurisdiction_code,1,2)
494 -- and assignment_id = pes.assignment_id
495 and assignment_action_id = pes.assignment_action_id)
496 -- and pay_magtape_generic.date_earned(ppa.effective_date, paa.assignment_id) between
497 -- pes.effective_start_date AND pes.effective_end_date
498 and paa.tax_unit_id = Pay_Magtape_Generic.Get_Parameter_Value
499 ('TAX_UNIT_ID')
500 and pus.state_abbrev= Pay_Magtape_Generic.Get_Parameter_Value
501 ('TRANSFER_STATE')
502 and Pay_Mag_Utils.Date_Earned(ppa.effective_date,
503 paa.assignment_id,
504 paf.effective_start_date,
505 paf.effective_end_date,
506 ppf.effective_start_date,
507 ppf.effective_end_date) = 1
508 /* if the state is Indiana we use this cursor for getting the Supplemental Records */
509 ORDER BY 12, 14, 16, 18 ; --last_name, first_name, middle_names, national_identifier;
510
511
512 -- Cursor to allow generation of state totals by assignment
513 -- for payers participating in Combined Filing.
514 Cursor US_1099R_State_Process IS
515 select distinct 'TRANSFER_STATE_NAME=P', hlk.lookup_code
516 from fnd_common_lookups hlk,
517 hr_organization_information hoi
518 where hoi.organization_id = Pay_Magtape_Generic.Get_Parameter_Value
519 ('TRANSFER_TRANS_LEGAL_CO_ID')
520 and hoi.org_information4 = 'Y'
521 and hoi.org_information_context = '1099R Magnetic Report Rules'
522 and hlk.lookup_type = '1099R_US_COMBINED_FILER_STATES'
523 and Pay_Magtape_Generic.Get_Parameter_Value
524 ('TRANSFER_STATE') = 'FED'
525 UNION ALL
526 select 'TRANSFER_STATE_NAME=P', 'SC'
527 from dual
528 where Pay_Magtape_Generic.Get_Parameter_Value
529 ('TRANSFER_STATE') = 'SC'
530 UNION ALL
531 select 'TRANSFER_STATE_NAME=P', 'ID'
532 from dual
533 where Pay_Magtape_Generic.Get_Parameter_Value
534 ('TRANSFER_STATE') = 'ID'
535 UNION ALL
536 select 'TRANSFER_STATE_NAME=P', 'NE'
537 from dual
538 where Pay_Magtape_Generic.Get_Parameter_Value
539 ('TRANSFER_STATE') = 'NE'
540 UNION ALL
541 select 'TRANSFER_STATE_NAME=P', 'VT'
542 from dual
543 where Pay_Magtape_Generic.Get_Parameter_Value
544 ('TRANSFER_STATE') = 'VT'
545 ;
546
547 --MMREF Cursors
548 -- Cursor to set up tax unit and jurisdiction contexts for each transmitter rec.
549 -- When we run for a State the Jurisdiction is set for the context otherwise it is set
550 -- to a dummy value for federal.
551 CURSOR state_1099r_mmref_transmitter IS
552 SELECT 'TAX_UNIT_ID=C' , HOI.organization_id,
553 'JURISDICTION_CODE=C', SR.jurisdiction_code,
554 'TRANSFER_JD=P', SR.jurisdiction_code,
555 'ASSIGNMENT_ID=C' , '-1',
556 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
557 'TRANSFER_HIGH_COUNT=P', '0',
558 'TRANSFER_SCHOOL_DISTRICT=P', '-1',
559 'TRANSFER_COUNTY=P', '-1',
560 'TRANSFER_2678_FILER=P', 'N',
561 'PAYROLL_ACTION_ID=C', PPA.payroll_action_id,
562 'TRANSFER_LOCALITY_CODE=P', 'DUMMY',
563 'BUSINESS_GROUP_ID=C',PPA.business_group_id
564 FROM pay_state_rules SR,
565 hr_organization_information HOI,
566 pay_payroll_actions PPA,
567 pay_payroll_actions PPA1
568 WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
569 ('TRANSFER_PAYROLL_ACTION_ID')
570 AND ppa1.effective_date = ppa.effective_date
571 AND ppa1.report_qualifier = sr.state_code
572 AND HOI.organization_id =
573 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
574 AND SR.state_code =
575 substr(pay_magtape_generic.get_parameter_value('TRANSFER_STATE'),1,2) /* 13408243 */
576 AND HOI.org_information_context = '1099R Magnetic Report Rules'
577 AND PPA.report_type = 'YREND'
578 AND HOI.ORGANIZATION_ID =
579 substr(PPA.legislative_parameters,instr(PPA.legislative_parameters,'TRANSFER_GRE=')+ length('TRANSFER_GRE='))
580 AND to_char(PPA.effective_date,'YYYY') =
581 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
582 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
583
584 --
585 -- Sets up the tax unit context for each employer to be reported on NB. sets
586 -- up a parameter holding the tax unit identifier which can then be used by
587 -- subsequent cursors to restrict to employees within the employer.
588 --
589 --
590 /* Context and Parameter in the cursor are
591 Payroll_action_id table looks for value related to Year End pre-
592 processor while the pay_assignment_actions looks for
593 assignment actions of Mag. tapes
594 Context :
595 TAX_UNIT_ID - Submitter's Tax Unit ID
596 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
597 ASSIGNMENT_ID - Required for call to function - context not used
598 in the for Submitter
599 Date Earned - Always set to Effective date ie. in this case
600 for Mag tapes to 31-DEC-YYYY, in case of SQWL
601 this will be diffrent.
602 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
603
604 Parameters :
605 TAX_UNIT_ID - To be used in subsequent cusrsor
606 */
607
608
609
610 CURSOR state_1099r_mmref_payer IS
611 SELECT DISTINCT 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
612 'TAX_UNIT_ID=C' , AA.tax_unit_id,
613 'TAX_UNIT_ID=P' , AA.tax_unit_id,
614 'TAX_UNIT_NAME=P' ,substr(hou.name,1,80), --Bug# 14286448 Gre length issue
615 'TRANSFER_EMP_CODE=P', 'R'
616 FROM
617 hr_all_organization_units hou,
618 pay_payroll_actions ppa,
619 pay_assignment_actions AA
620 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
621 ('TRANSFER_PAYROLL_ACTION_ID')
622 AND ppa.report_type = 'YREND'
623 AND to_char(ppa.effective_date,'YYYY') =
624 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
625 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
626 AND AA.tax_unit_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=') + length('TRANSFER_GRE='))
627 AND hou.organization_id = AA.tax_unit_id
628 order by substr(hou.name,1,80);
629
630
631 --Sets up the assignment_action_id, assignment_id, and date_earned contexts
632 -- for an employee. The date_earned context is set to be the least of the
633 -- end of the period being reported and the maximum end date of the
634 -- assignment. This ensures that personal information ie. name etc... is
635 -- current relative to the period being reported on.
636 --
637 CURSOR state_1099r_mmref_payee IS
638 SELECT
639 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
640 'ASSIGNMENT_ID=C', AA.assignment_id,
641 'DATE_EARNED=C', fnd_date.date_to_canonical( pay_magtape_generic.date_earned (PA.effective_date, AA.assignment_id)),
642 'JURISDICTION_CODE=C',pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
643 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
644 FROM per_all_people_f PE,
645 per_all_assignments_f SS,
646 pay_action_interlocks AI,
647 pay_assignment_actions AA,
648 pay_payroll_actions PA
649 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
650 ('TRANSFER_PAYROLL_ACTION_ID') AND
651 AA.payroll_action_id = PA.payroll_action_id AND
652 AA.tax_unit_id = pay_magtape_generic.get_parameter_value
653 ('TAX_UNIT_ID') AND
654 AI.locking_action_id = AA.assignment_action_id AND
655 SS.assignment_id = AA.assignment_id AND
656 PE.person_id = SS.person_id AND
657 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
658 SS.effective_start_date and SS.effective_end_date AND
659 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
660 PE.effective_start_date and PE.effective_end_date
661 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
662
663
664 /****************************************************************************
665 Function and Procedures used in the PAY_REPORT_FORMAT_MAPPINGS_F
666 are defined below.
667
668 Range_cursor - Picks the valid 1099R persons
669 mag_1099r_action_creation - Creates Assignment Action id for the Valid
670 person who need to be reported in the 1099R
671
672 ****************************************************************************/
673
674 Procedure get_selection_information (
675 p_payroll_action_id in number,
676 p_year_start out nocopy date,
677 p_year_end out nocopy date,
678 p_state_code out nocopy varchar2,
679 p_state_abbrev out nocopy varchar2,
680 p_report_type out nocopy varchar2,
681 p_business_group_id out nocopy number,
682 p_tax_unit_id out nocopy number,
683 p_trans_cont_code out nocopy varchar2,
684 p_yrend_ppa_id out nocopy number);
685
686 Function get_balance_value (
687 p_balance_name in varchar2,
688 p_tax_unit_id in number,
689 p_state_abbrev in varchar2,
690 p_assignment_id in number,
691 p_effective_date in date) RETURN NUMBER;
692
693 Function preprocess_check (
694 p_payroll_action_id in number,
695 p_year_start in date,
696 p_year_end in date,
697 p_business_group_id in number,
698 p_state_abbrev in varchar2,
699 p_state_code in varchar2,
700 p_report_type in varchar2,
701 p_tax_unit_id in number,
702 p_trans_cont_code in varchar2) RETURN BOOLEAN;
703
704 Procedure range_cursor (
705 p_payroll_action_id in number,
706 p_sql_string out nocopy varchar2);
707
708 Procedure mag_1099r_action_creation (
709 p_payroll_action_id in number,
710 p_start_person in number,
711 p_end_person in number,
712 p_chunk in number);
713
714
715 end pay_us_1099r_mag_reporting;
[Home] [Help]