1 PACKAGE BODY pay_us_1099r_mag_reporting AS
2 /* $Header: pyyep99r.pkb 120.4.12020000.1 2012/06/28 19:40:13 appldev 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 11-JAN-99 AHANDA 40.5 Tuned the Queries.
33 22-JAN-99 AHANDA 40.6 Changed the cursor for range_cursor,
34 assignment_action_creation and
35 preprocess_check to check for
36 assignment action in that year.
37 28-MAY-99 rthakur 40.7 875113 Arizona no longer requires the 1099R
38 for employees with no SIT withheld.
39 Changed the logic in the
40 preprocess_check function.
41 In the action creation cursor, changed
42 the logic to only look for assignments
43 that fall underneath the TCC.
44 27-JUN-99 rthakur 40.8 Commented out the call to trace_on.
45 27-JUN-99 rthakur 40.9 Fixed order by clause on c_gre_federal.
46 Commented out exists clause in assign.
47 action creation cursors because the
48 logic is being duplicated by the year
49 end pre-processor.
50 02-JUL-99 rthakur 40.10 Added some more debugging information.
51 02-JUL-99 rthakur 40.11 Changed c_chk_cntc_info to take into
52 account different character TCC's.
53 24-JUL-99 rthakur 40.12 Modified the preprocess check to take
54 into account Transmitter GREs not
55 being archived.
56 30-AUG-99 rthakur 40.13/14 Modified the action creation code to
57 look at archived TCC instead of live.
58 21-SEP-99 rthakur 115.2 Arcsd in 110.8 of r11.
59 04-MAR-01 mreid 115.4 Corrected error message number
60 02-AUG-01 ekim 115.5 Added cursor c_chk_vnd_info
61 to check for vendor information
62 Bug 1811755.
63 06-SEP-01 ekim 115.6 Changed message to
64 PAY_34980_TRSMTR_VND_NOT_FOUND
65 for missing vendor information error.
66 07-SEP-01 ekim 115.7 Added space in
67 pay_mag_utils.get_parameter call.
68 16-NOV-01 jgoswami 115.8 Added South Carolina to check if SIT>0.
69 30-NOV-2001 jgoswami 115.9 Added dbdrv command
70 08-AUG-2002 ahanda 115.10 Changed the following cursors for perf:
71 - fed and state action creation
72 - c_1099_gre_state
73 03-dec-2002 djoshi 115.12 Added KS and MT logic record created
74 only if sit = 0
75 03-dec-2002 djoshi 115.13 Corrected typo
76 07-DEC-2002 ahanda 115.14 Changed from clause to join to main
77 table instead of secure views.
78 08-DEC-2002 ahanda 115.15 Changed view to use pay_us_state_w2_v
79 instead of pay_us_w2_state_v.
80 19-MAY-2003 ahanda 115.16 2955696 Changed federal and state action_creation
81 cursor to add ff_contexts and get only
82 context of TAX_UNIT_ID.
83 19-JUN-2003 ahanda 115.17 3013521 Changed federal and state action_creation
84 cursor to add to_char for tax_unit_id.
85 There will be no perf degradation as there
86 is no index on tax_unit_id.
87 30-OCT-2003 jgoswami 115.18 3209884 Modified mag_1099r_action_creation procedure
88 Changed boundry conditions for employees to be reported on tape
89 (creating assignemnt_action_id) for 1099R_STATE for folowing States
90 'AR' if SIT > 0 or State_wages > 2500 changed to
91 'AR' if SIT > 0 or State_wages > 0
92 'KS' was SIT > 0 must be paper so we have 'KS' if SIT = 0 changed to
93 'KS' if SIT > 0 or State_wages > 0
94 'MT' if SIT > 0 must be Paper so we check SIT = 0 only, report on tape
95 21-NOV-2003 jgoswami 115.19 Added check for 'KS' as mentioned in comments.
96 02-JAN-2004 jgoswami 115.20 3349571 Reverted changes for 'KS' .
97 27-JAN-2003 jgoswami 115.21 3381162 Check for gorss (box1) instead of
98 checking taxable (Box 2a) in
99 action_creation.
100 11-NOV-2004 asasthan 115.22 2694998 Changed c_chk_cntc_info
101 Should make tape error
102 out if after replacing EXT etc
103 the net result is NULL;
104 It should be noted that in
105 the US_1099R_TRANSMITTER
106 in fields like vendorcontact phone
107 if the data is simply a () and
108 after replacement the net value is
109 null then the tape will not fold
110 properly but be short by that
111 many characters as the length of the
112 field.
113
114 15-NOV-2004 asasthan 115.23 2694998 Added 'E' to strip
115 12-AUG-2005 kvsankar 115.24 4347429 Modified the Pre Process
116 4344915 procedure to not to check
117 for each and every assignment
118 in the current GRE.
119 The procedure now just
120 checks for whether a GRE is archived
121 or not. If not a warning will be
122 given to Customer for archiving the
123 same.
124 14-MOV-2005 pragupta 115.25 4350849 Changed the condition w2_state_wages
125 > 0 to >=0 in the range_cursor. Also
126 changed the condition ln_box_17 > 0
127 to >= 0 in the action_creation.
128 17-MAR-2006 pragupta 115.26 4583577 Performace changes to remove merge
129 join cartesian. Exists clause added
130 in the cursor tcc_1099R_cur. Added
131 date condition in Create Interlock
132 query in mag_1099r_action_creation.
133 *********************************************************************/
134
135 Function get_parameter(name in varchar2,
136 parameter_list varchar2) return varchar2
137 is
138 start_ptr number;
139 end_ptr number;
140 token_val ff_archive_items.value%type;
141 par_value ff_archive_items.value%type;
142 begin
143 --
144 token_val := name||'=';
145 --
146 start_ptr := instr(parameter_list, token_val) + length(token_val);
147 end_ptr := instr(parameter_list, ' ',start_ptr);
148 --
149 /* if there is no spaces use then length of the string */
150 if end_ptr = 0 then
151 end_ptr := length(parameter_list)+1;
152 end if;
153 --
154 /* Did we find the token */
155 if instr(parameter_list, token_val) = 0 then
156 par_value := NULL;
157 else
158 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
159 end if;
160 --
161 return par_value;
162 --
163 end get_parameter;
164
165
166 Procedure get_selection_information (
167 p_payroll_action_id in number,
168 p_year_start out nocopy date,
169 p_year_end out nocopy date,
170 p_state_code out nocopy varchar2,
171 p_state_abbrev out nocopy varchar2,
172 p_report_type out nocopy varchar2,
173 p_business_group_id out nocopy number,
174 p_tax_unit_id out nocopy number,
175 p_trans_cont_code out nocopy varchar2,
176 p_yrend_ppa_id out nocopy number)
177 is
178
179
180 -- Cursor to fetch the 1099R Transmitter Control Code for a particular gre
181
182 cursor tcc_1099R_cur(p_tax_unit_id NUMBER, p_business_group_id NUMBER) is
183 /*4583577 Perf change 1 start*/
184 select hoi2.org_information2
185 from hr_all_organization_units hou,
186 hr_organization_information hoi2 -- 1099R transmitter
187 where hou.business_group_id + 0 = p_business_group_id
188 and hou.organization_id = p_tax_unit_id
189 and hoi2.organization_id = hou.organization_id
190 and hoi2.org_information_context = '1099R Magnetic Report Rules'
191 and exists
192 (select 'Y'
193 from hr_all_organization_units hou1, hr_organization_information hoi
194 where hou1.business_group_id + 0 = p_business_group_id
195 and hou1.organization_id = p_tax_unit_id
196 and hou1.organization_id = hoi.organization_id
197 and hoi.org_information_context = 'CLASS'
198 and hoi.org_information1 = 'HR_LEGAL');
199 /*4583577 Perf change 1 end*/
200
201 cursor c_sel is
202 select ppa.start_date,
203 ppa.effective_date,
204 ppa.business_group_id,
205 ppa.report_qualifier,
206 ppa.report_type
207 FROM pay_payroll_actions ppa
208 WHERE payroll_action_id = p_payroll_action_id;
209
210 -- Cursor to fetch the YREND ARCHIVER Payroll action id for TCC Gre
211 cursor c_yrend_ppa(p_tax_unit_id NUMBER, p_payroll_action_id NUMBER) is
212 select ppa.payroll_action_id
213 from pay_payroll_actions ppa, -- YREND
214 pay_payroll_actions ppa1 -- 1099R
215 where ppa1.payroll_action_id = p_payroll_action_id -- 1099R
216 and ppa.report_type = 'YREND'
217 and ppa.effective_date = ppa1.effective_date
218 and ppa.business_group_id + 0 = ppa1.business_group_id
219 and ppa.action_status = 'C'
220 and rtrim(ltrim(Pay_Mag_Utils.Get_Parameter('TRANSFER_GRE',' ',ppa.legislative_parameters))) = p_tax_unit_id;
221
222
223 ln_business_group_id number;
224 lv_report_qualifier varchar2(30);
225 lv_report_type varchar2(30);
226 ld_year_start date;
227 ld_year_end date;
228 lv_state_code varchar2(10);
229 lv_tax_unit_id varchar2(30);
230 lv_trans_cont_code varchar2(30);
231 ln_yrend_ppa_id number;
232 lv_leg_param pay_payroll_actions.legislative_parameters%type;
233
234 begin
235
236 hr_utility.trace('Entering pay_us_1099r_mag_reporting.get_selection_information');
237
238 open c_sel;
239 fetch c_sel into ld_year_start, ld_year_end, ln_business_group_id,
240 lv_report_qualifier, lv_report_type;
241
242 hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',10);
243
244 if c_sel%notfound then
245
246 hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',20);
247 hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
248 hr_utility.raise_error;
249
250 end if;
251
252 close c_sel;
253
254 hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',30);
255
256 if lv_report_qualifier = 'FED' then
257
258 lv_state_code := ' ';
259 lv_report_type := '1099R_FED';
260
261 else
262 select state_code into lv_state_code
263 from pay_us_states
264 where state_abbrev = lv_report_qualifier;
265
266 lv_report_type := '1099R_STATE';
267
268 end if;
269
270 hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',40);
271
272 -- To get the tax unit id from legislative parameters
273 -- Note: The below get_parameter_value returns varchar2 so when we assign lv_tax_unit_id
274 -- to p_tax_unit_id we convert it to a number below.
275 -- We had to create a function to read from legislative parameters to get the tax_unit_id
276 -- of the transmitter GRE
277
278
279 select legislative_parameters
280 into lv_leg_param
281 from pay_payroll_actions ppa
282 where ppa.payroll_action_id = p_payroll_action_id;
283
284 lv_tax_unit_id := get_parameter('TRANSFER_TRANS_LEGAL_CO_ID', lv_leg_param);
285
286 hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',50);
287 hr_utility.trace('The tax unit id is : '||lv_tax_unit_id);
288
289 -- To get the transmitter control code for the specific tax unit id
290
291 open tcc_1099R_cur(lv_tax_unit_id, ln_business_group_id);
292 fetch tcc_1099R_cur into lv_trans_cont_code;
293 if tcc_1099R_cur%notfound then
294
295 hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',60);
296 hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
297 hr_utility.raise_error;
298 end if;
299 close tcc_1099R_cur;
300
301 -- To get the YREND ppa ID
302
303 open c_yrend_ppa(lv_tax_unit_id, p_payroll_action_id);
304 fetch c_yrend_ppa into ln_yrend_ppa_id;
305
306 if c_yrend_ppa %notfound then
307
308 hr_utility.trace('Payroll action id: '||to_char(p_payroll_action_id));
309 hr_utility.trace('Tax unit id: '||lv_tax_unit_id);
310 hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',65);
311 hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
312 hr_utility.raise_error;
313 end if;
314
315 close c_yrend_ppa;
316
317 p_year_start := ld_year_start;
318 p_year_end := ld_year_end;
319 p_state_code := lv_state_code;
320 p_state_abbrev := lv_report_qualifier;
321 p_report_type := lv_report_type;
322 p_business_group_id := ln_business_group_id;
323 p_tax_unit_id := to_number(lv_tax_unit_id);
324 p_trans_cont_code := lv_trans_cont_code;
325 p_yrend_ppa_id := ln_yrend_ppa_id;
326
327 hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',70);
328 hr_utility.trace('The year start from get_selection_information is: '||to_char(ld_year_start));
329 hr_utility.trace('The year end from get_selection_information is: '||to_char(ld_year_end));
330 hr_utility.trace('The state code from get_selection_information is: '||lv_state_code);
331 hr_utility.trace('The state abbrev from get_selection_information is: '||lv_report_qualifier);
332 hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
333 hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
334 hr_utility.trace('The tax unit id from get_selection_information is: '||lv_tax_unit_id);
335 hr_utility.trace('The transmitter control code from get_selection_information is: '||lv_trans_cont_code);
336 hr_utility.trace('The year end payroll action id from get_selection_information is: '||to_char(ln_yrend_ppa_id));
337
338 hr_utility.trace('Exiting pay_us_1099r_mag_reporting.get_selection_information');
339
340 end get_selection_information;
341
342
343 Function get_balance_value (
344 p_balance_name in VARCHAR2,
345 p_tax_unit_id in NUMBER,
346 p_state_abbrev in VARCHAR2,
347 p_assignment_id in NUMBER,
348 p_effective_date in DATE) RETURN NUMBER
349 is
350
351 lv_jurisdiction_code varchar2(20);
352 ln_defined_balance_id number;
353 ln_balance_value number;
354
355 cursor c_jurisdiction (cp_state_abbrev varchar2) is
356 select jurisdiction_code
357 from pay_state_rules
358 where state_code = cp_state_abbrev;
359
360 cursor c_defined_balance (cp_database_item varchar2)IS
361 select to_number(ue.creator_id)
362 from ff_database_items fdi,
363 ff_user_entities ue
364 where fdi.user_name = cp_database_item
365 and ue.user_entity_id = fdi.user_entity_id
366 and ue.creator_type = 'B';
367
368 begin
369
370 hr_utility.trace('Entering pay_us_1099r_mag_reporting.get_balance_value');
371
372 hr_utility.set_location ('pay_us_1099r_mag_reporting.get_balance_value', 10);
373
374 open c_defined_balance(p_balance_name);
375 fetch c_defined_balance into ln_defined_balance_id;
376 close c_defined_balance;
377
378 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
379
380 if p_state_abbrev <> 'FED' THEN
381 open c_jurisdiction(p_state_abbrev);
382 fetch c_jurisdiction into lv_jurisdiction_code;
383 close c_jurisdiction;
384
385 hr_utility.set_location
386 ('pay_us_1099r_mag_reporting.get_balance_value', 15);
387
388 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
389 end if;
390
391 hr_utility.trace(p_balance_name);
392 hr_utility.trace('Context');
393 hr_utility.trace('Tax Unit Id: '|| p_tax_unit_id);
394 hr_utility.trace('Jurisdiction: '|| lv_jurisdiction_code);
395 hr_utility.set_location
396 ('pay_us_1099r_mag_reporting.get_balance_value', 20);
397
398 ln_balance_value := pay_balance_pkg.get_value
399 (ln_defined_balance_id,
400 p_assignment_id, p_effective_date);
401
402 hr_utility.trace('Exiting pay_us_1099r_mag_reporting.get_balance_value');
403
404 return (ln_balance_value);
405
406
407 end get_balance_value;
408
409
410
411 --------------------------------------------------------------------------
412 --Name
413 -- preprocess_check
414 --Purpose
415 -- This function checks if the year end preprocessor has been run for the
416 -- GREs involved in the 1099R report. It also checks if any of the assignments
417 -- have errored out or have been marked for retry. The checking is done based
418 -- on the narrow scope of the parameters that the user has entered.
419 -- The logic in how we check for the existence of and archived GRE depends on
420 -- whether we are running the Federal or State 1099R.
421 --
422 -- We will always error out the 1099R Mag if the Transmitter GRE is not archived.
423 --
424 -- FEDERAL:
425 -- 1) Check for all 1099R GRE's within the transmitter control code.
426 -- 2) See if all the said GRE's have been archived.
427 -- a) If YES, have then check for existence of errored or retried
428 -- assignment actions.
429 -- b) If NO, then we check and see if the GRE should have been
430 -- STATE:
431 -- 1) Check for all the 1099R GRE's which have assignments in the
432 -- particular state and transmitter control code.
433 -- 2) See if the said GRE's have been archived.
434 -- a) If YES, have then check for existence of errored or retried
435 -- assignment actions.
436 -- b) If NO, then we check and see if the GRE should have been
437 -- archived.
438 --
439 --Arguments
440 -- p_payroll_action_id Payroll_action_id for the report
441 -- p_year_start Start date of the period for which the report
442 -- has been requested
443 -- p_year_end End date of the period
444 -- p_business_group_id Business group for which the report is being run
445 -- p_state_abbrev Two digit state abbreviation (or 'FED' for federal
446 -- report)
447 -- p_state_code State code (NULL for federal)
448 -- p_report_type Type of report being run (W2, 1099R ...)
449 -- p_tax_unit_id The GRE that was entered through SRS,
450 -- determined by the procedure get_selection_information
451 -- p_trans_cont_code The Transmitter Control Code of the GRE
452 -- determined by the procedure get_selection_information
453 --Note:
454 -- The check for errored/marked for retry assignments can be bypassed by
455 -- setting the parameter 'FORCE_MAG_REPORT' to 'E' and 'R' respectively. In
456 -- such cases the report will ignore the assignments in question.
457 --Note2:
458 -- Our cursors here are going against the live data to verify that the
459 -- pre-process check has run correctly and is returning the correct data.
460 -----------------------------------------------------------------------------
461 --
462
463 Function preprocess_check (
464 p_payroll_action_id in number,
465 p_year_start in date,
466 p_year_end in date,
467 p_business_group_id in number,
468 p_state_abbrev in varchar2,
469 p_state_code in varchar2,
470 p_report_type in varchar2,
471 p_tax_unit_id in number,
472 p_trans_cont_code in varchar2) RETURN BOOLEAN
473 is
474
475 -- Cursor to fetch all 1099R GREs belonging to the transmitter control code
476 -- This is ordering by the Transmitter indicator to always make the Transmitter
477 -- GRE pop to the top to make that one process first.
478
479 Cursor c_1099_fed_gre(p_trans_cont_code VARCHAR2) is
480 select hou.organization_id, hoi2.org_information1
481 from hr_all_organization_units hou,
482 hr_organization_information hoi,
483 hr_organization_information hoi2
484 where hou.business_group_id + 0 = p_business_group_id
485 and hou.organization_id = hoi.organization_id
486 and hoi.org_information_context = 'CLASS'
487 and hoi.org_information1 = 'HR_LEGAL'
488 and hoi.organization_id = hoi2.organization_id
489 and hoi2.org_information_context = '1099R Magnetic Report Rules'
490 and hou.organization_id in (
491 select organization_id
492 from hr_organization_information
493 where org_information_context = '1099R Magnetic Report Rules'
494 and org_information2 = p_trans_cont_code)
495 order by 2 desc;
496
497 -- Cursor to get payroll_action_ids of the pre-process for the given GRE.
498 -- This will also serve as a check to make sure that all GREs have been
499 -- archived
500 Cursor c_payroll_action (cp_tax_unit_id varchar2,
501 cp_year_start date,
502 cp_year_end date,
503 cp_business_group_id number) is
504 select payroll_action_id
505 from pay_payroll_actions
506 where report_type = 'YREND'
507 and effective_date = cp_year_end
508 and start_date = cp_year_start
509 and business_group_id + 0 = cp_business_group_id
510 and substr(legislative_parameters,
511 (instr(legislative_parameters, 'TRANSFER_GRE=') +
512 length('TRANSFER_GRE='))) = cp_tax_unit_id;
513
514 --Cursor for checking if any of the the archiver has errored for
515 --any of the assignments or any assignment is pending (Marked for Retry)
516 Cursor c_check_asg (cp_payroll_action_id number,
517 cp_status_type varchar2) IS
518 select '1'
519 from dual
520 where exists (
521 select '1'
522 from pay_assignment_actions paa
523 where paa.payroll_action_id = cp_payroll_action_id
524 and paa.action_status = decode(cp_status_type,'R','M', --If R is passed we compare for retry
525 cp_status_type))
526 and not exists (
527 select '1'
528 from pay_action_parameters
529 where parameter_name = 'FORCE_MAG_REPORT'
530 and instr(parameter_value, cp_status_type) > 0);
531
532 -- Cursor to check Transmitter Contact Information for the 'T' record
533
534 Cursor c_chk_cntc_info (cp_trans_control_code varchar2, cp_tax_unit_id number) IS
535 select 'Y'
536 from hr_organization_information hoi
537 where hoi.organization_id = cp_tax_unit_id
538 and hoi.org_information_context = '1099R Magnetic Report Rules'
539 and hoi.org_information1 = 'Y'
540 and hoi.org_information2 = cp_trans_control_code
541 and replace(substr(hoi.org_information9,1,40),',') is not null
542 and replace(replace(replace(replace(replace(replace(replace(replace
543 (upper(substr(hoi.org_information10,1,15)),'-'),'.'),'('),')'),'E'),'X'), 'T'),' ') is not null;
544
545 -- Cursor to check Transmitter Vendor Type for the 'T' record
546
547 Cursor c_chk_vnd_type (cp_trans_control_code varchar2,
548 cp_tax_unit_id number) IS
549 select hoi.org_information11
550 from hr_organization_information hoi
551 where hoi.organization_id = cp_tax_unit_id
552 and hoi.org_information_context = '1099R Magnetic Report Rules'
553 and hoi.org_information1 = 'Y'
554 and hoi.org_information2 = cp_trans_control_code;
555
556
557 --
558 -- Cursor to check Transmitter Vendor Information for the 'T' record
559 --
560 Cursor c_chk_vnd_info (cp_trans_control_code varchar2,
561 cp_tax_unit_id number) IS
562 select 'Y' from hr_organization_information hoi
563 where hoi.organization_id = cp_tax_unit_id
564 and hoi.org_information_context = '1099R Magnetic Report Rules'
565 and hoi.org_information1 = 'Y'
566 and hoi.org_information2 = cp_trans_control_code
567 and hoi.org_information11 is not null
568 and hoi.org_information12 is not null
569 and hoi.org_information13 is not null
570 and hoi.org_information14 is not null
571 and hoi.org_information15 is not null
572 and hoi.org_information16 is not null
573 and hoi.org_information17 is not null
574 and hoi.org_information18 is not null
575 and hoi.org_information19 is not null ;
576
577
578 --local variables used for processing
579 ln_picked_gre number(1) := 0;
580
581 ln_curr_gre number(15);
582 ln_curr_person number(15);
583 ln_prev_person number(15);
584 ln_assignment number(15);
585 ld_asg_effective_dt date;
586 ln_payroll_action_id number(15);
587 lc_asgn_retry varchar2(2) := 'N';
588 lc_asgn_error varchar2(2) := 'N';
589 ln_archived_gre_found number := 0;
590 ln_balance_exists number := 0;
591 ln_no_of_gres_picked number := 0;
592 l_trans_cont_code varchar2(30);
593 l_gre_tcc varchar2(30);
594 ln_balance_value number := 0;
595 ln_balance_value_ar number := 0;
596
597 l_fed_gre_check number(15);
598 lv_contact_chk varchar2(2);
599 lv_gre_archive varchar2(2);
600 lv_chk_state_balances varchar2(2) := 'Y';
601 lv_transmitter_flag varchar2(2);
602 lv_vendor_chk varchar2(2);
603 lv_vendor_type varchar2(1);
604 lv_contact_name varchar2(150);
605 lv_contact_number varchar2(150);
606 lv_message_preprocess varchar2(2000);
607 lv_message_text VARCHAR2(32000);
608
609 begin
610
611 /* First check if the transmitter contact information is there for the 'T' record */
612 -- hr_utility.trace_on(NULL, 'MAGR');
613 hr_utility.trace('Entering the pay_us_1099r_mag_reporting.preprocess_check');
614
615 -- Initialization
616 ln_curr_gre := -9999;
617 lv_message_preprocess := 'Pre-Process check';
618
619 open c_chk_cntc_info(p_trans_cont_code, p_tax_unit_id);
620 fetch c_chk_cntc_info into lv_contact_chk;
621 if c_chk_cntc_info%NOTFOUND then
622 hr_utility.set_location( 'pay_us_1099r_mag_reporting.preprocess_check', 20);
623 hr_utility.set_message(801, 'PAY_72837_TRSMTR_CNT_NOT_FND');
624 close c_chk_cntc_info;
625 hr_utility.raise_error;
626 end if;
627 close c_chk_cntc_info; -- The 'T' record contact information is
628 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',10);
629
630
631 /* Check if the transmitter vendor Information exists for 'T' record */
632 open c_chk_vnd_type(p_trans_cont_code, p_tax_unit_id);
633 fetch c_chk_vnd_type into lv_vendor_type;
634 if lv_vendor_type = 'Y' THEN
635 open c_chk_vnd_info(p_trans_cont_code, p_tax_unit_id);
636 fetch c_chk_vnd_info into lv_vendor_chk;
637 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',23);
638 if c_chk_vnd_info%NOTFOUND then
639 hr_utility.set_location( 'pay_us_1099r_mag_reporting.preprocess_check', 24);
640 hr_utility.set_message(801, 'PAY_34980_TRSMTR_VND_NOT_FOUND');
641 CLOSE c_chk_vnd_info;
642 close c_chk_vnd_type;
643 hr_utility.raise_error;
644 end if;
645 close c_chk_vnd_info; -- The 'T' record Vendor information exists.
646 end if;
647 close c_chk_vnd_type;
648
649 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',30);
650
651 -- Performance Changes
652 -- Check and see if we have any GREs(within the tcc) which have not been processed
653 -- Open the cursor which will give us all 1099R GREs for a particular
654 -- transmitter control code FED,
655 -- We do not distinguish between State and Federal level reports in
656 -- Pre-process
657 open c_1099_fed_gre(p_trans_cont_code);
658
659 loop -- Main Loop
660 hr_utility.trace('The previous GRE was : '||to_char(ln_curr_gre));
661 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',70);
662 fetch c_1099_fed_gre into ln_curr_gre, lv_transmitter_flag;
663 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',80);
664 hr_utility.trace('The GRE being checked is: '||to_char(ln_curr_gre));
665 if c_1099_fed_gre%NOTFOUND THEN
666 -- This means that the there are no more rows in the cursor
667 -- So lets get out of the loop and continue
668 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',81);
669 exit;
670 end if; -- if c_1099_fed_gre%NOTFOUND
671
672 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',120);
673
674 -- At this point we have the GREs for both Federal and State,
675 -- we do not know if they have been
676 -- archived or not yet. All we know is that they exist.
677 -- Lets see if the GRE has been archived we will set a flag to
678 -- determine if it has been archived or not
679 open c_payroll_action (ln_curr_gre, p_year_start, p_year_end, p_business_group_id);
680 fetch c_payroll_action into ln_payroll_action_id;
681 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',130);
682 if c_payroll_action%notfound then
683 lv_gre_archive := 'N'; -- it has not been archived
684 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',140);
685 else
686 lv_gre_archive := 'Y'; -- it has been archived
687 ln_no_of_gres_picked := ln_no_of_gres_picked + 1;
688 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',150);
689 end if;
690 close c_payroll_action;
691
692 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',190);
693
694 -- Now if the gre has been archived lets verify that none of the assignments
695 -- have errored and none are marked for retry.
696 -- If they have been marked for error or retry then set a flag
697 -- lc_asgn_retry or lc_asgn_error.
698
699 IF lv_gre_archive = 'Y' THEN
700
701 open c_check_asg(ln_payroll_action_id, 'R');
702 fetch c_check_asg into lc_asgn_retry;
703 if c_check_asg%found then
704 lc_asgn_retry := 'Y';
705 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',200);
706 end if;
707 close c_check_asg;
708
709 open c_check_asg(ln_payroll_action_id, 'E');
710 fetch c_check_asg into lc_asgn_error;
711 if c_check_asg%found then
712 lc_asgn_error := 'Y';
713 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',210);
714 end if;
715 close c_check_asg;
716
717 -- If the flag for retry and or error is set to 'Y' then we must
718 -- close the cursor and get out
719 if lc_asgn_error = 'Y' then
720 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',220);
721 close c_1099_fed_gre;
722 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check', 230);
723 hr_utility.set_message(801, 'PAY_72729_ASG_NOT_ARCH');
724 hr_utility.raise_error;
725 elsif lc_asgn_retry = 'Y' then
726 close c_1099_fed_gre;
727 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check', 240);
728 hr_utility.set_message(801, 'PAY_72730_ASG_MARKED_FOR_RETRY');
729 hr_utility.raise_error;
730 end if; -- if lc_asgn_error = 'Y'
731 ELSE
732 hr_utility.trace(ln_curr_gre || 'GRE not archived');
733 lv_message_text := 'Please Archive GRE With ID := ' || to_char(ln_curr_gre) ;
734 /*
735 * Commenting the code
736 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
737 pay_core_utils.push_token('record_name',lv_message_preprocess);
738 pay_core_utils.push_token('description',lv_message_text);
739 */
740 END IF; -- IF lv_gre_archive = 'Y'
741
742 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',410);
743 END LOOP; --Main Loop
744
745 -- This is in the scenario if a customer runs the 1099R mag for a/or
746 -- many GREs that are not archived nor should they be archived, but since
747 -- their is no one to pick up, we need to error out the report.
748 -- The variable ln_no_of_gres_picked will be greater than zero if there is
749 -- a payroll action id of the YREND archiver for the specific GRE.
750 -- Otherwise it will never be set.
751
752 if ln_no_of_gres_picked = 0 then
753 hr_utility.set_location( 'pay_us_mag_1099r_reporting.preprocess_check', 415);
754 hr_utility.set_message(801, 'PAY_ARCH_GRE_NOT_FOUND');
755 hr_utility.raise_error;
756 else
757 if p_report_type = '1099R_FED' then
758 hr_utility.trace('The number of GREs that have been archived ' ||
759 'for the federal mag are: '||to_char(ln_no_of_gres_picked));
760 else
761 hr_utility.trace('The number of GREs that have been archived ' ||
762 'for the state mag are: ' ||to_char(ln_no_of_gres_picked));
763 end if;
764 end if;
765
766 close c_1099_fed_gre;
767
768 hr_utility.set_location('pay_us_1099r_mag_reporting.preprocess_check',420);
769 hr_utility.trace('Exiting the pay_us_1099r_mag_reporting.preprocess_check');
770 return(TRUE);
771
772 end preprocess_check;
773
774 Procedure range_cursor (
775 p_payroll_action_id in number,
776 p_sql_string out nocopy varchar2)
777 is
778
779 lv_sql_string varchar2(2000);
780
781 ld_year_start date;
782 ld_year_end date;
783 lv_state_code varchar2(10);
784 lv_state_abbrev varchar2(30);
785 lv_report_type varchar2(30);
786 ln_business_group_id number;
787 ln_tax_unit_id number;
788 lv_trans_cont_code varchar2(30);
789 ln_yrend_ppa_id number;
790
791 lb_pre_process boolean;
792
793 begin
794 -- hr_utility.trace_on(NULL,'oracle');
795 hr_utility.trace('Entering pay_us_1099r_mag_reporting.range_cursor');
796 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',10);
797
798 get_selection_information (
799 p_payroll_action_id,
800 ld_year_start,
801 ld_year_end,
802 lv_state_code,
803 lv_state_abbrev,
804 lv_report_type,
805 ln_business_group_id,
806 ln_tax_unit_id,
807 lv_trans_cont_code,
808 ln_yrend_ppa_id);
809
810 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',20);
811
812 lb_pre_process := preprocess_check (
813 p_payroll_action_id,
814 ld_year_start,
815 ld_year_end,
816 ln_business_group_id,
817 lv_state_abbrev,
818 lv_state_code,
819 lv_report_type,
820 ln_tax_unit_id,
821 lv_trans_cont_code);
822
823 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',30);
824
825 if lb_pre_process then
826
827 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',40);
828
829 if lv_report_type = '1099R_FED' then
830
831 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',50);
832 hr_utility.trace('The transmitter control code is: '||lv_trans_cont_code);
833
834 lv_sql_string :=
835 'select distinct paf.person_id
836 from --hr_soft_coding_keyflex hsck,
837 per_all_assignments_f paf,
838 pay_assignment_actions paa,
839 pay_payroll_actions ppa1,
840 pay_payroll_actions ppa
841 where ppa1.payroll_action_id = :p_payroll_action_id
842 and ppa.report_type = ''YREND''
843 and ppa.business_group_id + 0 = ppa1.business_group_id
844 and ppa.effective_date = ppa1.effective_date
845 and ppa.start_date = ppa1.start_date
846 and ppa.payroll_action_id = paa.payroll_action_id
847 and paa.action_status = ''C''
848 and paa.assignment_id = paf.assignment_id
849 and paf.assignment_type = ''E''
850 and paf.effective_start_date <= ppa.effective_date
851 and paf.effective_end_date >= ppa.start_date
852 and paf.business_group_id + 0 = ppa.business_group_id
853 --and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
854 --and hsck.segment1 = paa.tax_unit_id
855 --and hsck.segment1 in
856 and paa.tax_unit_id in
857 (select hoi.organization_id
858 from hr_organization_information hoi
859 where hoi.org_information_context = ''1099R Magnetic Report Rules'')
860 order by paf.person_id';
861
862 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 60);
863
864 elsif lv_report_type = '1099R_STATE' then
865
866 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',70);
867
868 lv_sql_string :=
869 'select distinct paf.person_id
870 from --hr_soft_coding_keyflex hsck,
871 hr_organization_units hou,
872 per_all_assignments_f paf,
873 pay_us_state_w2_v psv,
874 pay_payroll_actions ppa
875 where ppa.payroll_action_id = :p_payroll_action_id
876 and hou.business_group_id + 0 = ppa.business_group_id + 0
877 and psv.tax_unit_id = hou.organization_id
878 and psv.action_status = ''C''
879 and psv.year = to_number(to_char(ppa.effective_date, ''YYYY''))
880 and ( psv.state_ein <> ''FLI P.P. #'' and
881 decode(psv.state_abbrev, ''NY'', psv.w2_state_income_tax,
882 ''WV'', psv.w2_state_income_tax,
883 ''IN'', psv.w2_state_income_tax,
884 ''CT'', psv.w2_state_income_tax,
885 ''SC'', psv.w2_state_income_tax,
886 ''AZ'', psv.w2_state_income_tax,
887 psv.w2_state_wages) >= 0 ) -- 4350849
888 and psv.state_abbrev = ppa.report_qualifier
889 and psv.assignment_id = paf.assignment_id
890 and paf.assignment_type = ''E''
891 and paf.effective_start_date <= ppa.effective_date
892 and paf.effective_end_date >= ppa.start_date
893 and paf.business_group_id + 0 = ppa.business_group_id
894 --and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
895 --and hsck.segment1 = psv.tax_unit_id
896 --and hsck.segment1 in
897 and psv.tax_unit_id in
898 (select hoi.organization_id
899 from hr_organization_information hoi
900 where hoi.org_information_context = ''1099R Magnetic Report Rules'')
901 order by paf.person_id';
902
903 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 80);
904
905 end if;
906
907 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',90);
908
909 p_sql_string := lv_sql_string;
910
911 end if;
912
913 hr_utility.trace('Exiting pay_us_1099r_mag_reporting.range_cursor');
914
915 end range_cursor;
916
917
918
919 -----------------------------------------------------------------------------
920 --Name
921 -- mag_1099r_action_creation
922 --Purpose
923 -- Creates assignment actions for the payroll action associated with the
924 -- report and only for the assignments on the particular Transmitter
925 --
926 --Arguments
927 -- p_payroll_action_id payroll action for the report
928 -- p_start_person starting person id for the chunk
929 -- p_end_person last person id for the chunk
930 -- p_chunk size of the chunk
931 --Note
932 -- The procedure processes assignments in 'chunks' to facilitate
933 -- multi-threaded operation. The chunk is defined by the size and the
934 -- starting and ending person id. An interlock is also created against the
935 -- pre-processor assignment action to prevent rolling back of the archiver.
936 -- Now the year end pre processor is archiving all assignments and all
937 -- balances so we must check to see if the Federal Gross is greater than
938 -- zero.
939 ----------------------------------------------------------------------------
940 --
941
942 Procedure mag_1099r_action_creation
943 (p_payroll_action_id in number,
944 p_start_person in number,
945 p_end_person in number,
946 p_chunk in number)
947
948 is
949
950
951 -- Cursor to get the assignments for federal 1099R. Includes only 1099R GREs.
952 -- Removed the exists clause as the YREND archiver does the same logic
953 -- so it is safe to assume that the assignment action id we pick up should
954 -- satisfy the exists clause.
955 -- Now when choosing tax_unit_id we need to roll up to the TCC from the archiver.
956
957 cursor c_federal(cp_payroll_action_id number,
958 cp_start_person number,
959 cp_end_person number,
960 cp_yrend_ppa_id number) is
961 select paf.person_id,
962 paa.tax_unit_id,
963 paf.effective_end_date,
964 paf.assignment_id,
965 --pww.wages_tips_compensation
966 pww.gross_1099r
967 from pay_payroll_actions ppa,
968 pay_payroll_actions ppa1,
969 pay_us_wages_1099r_v pww,
970 per_all_assignments_f paf,
971 pay_assignment_actions paa
972 where ppa1.payroll_action_id = cp_payroll_action_id
973 and pww.year = to_number(to_char(ppa.effective_date, 'YYYY'))
974 and pww.assignment_id = paf.assignment_id
975 and pww.tax_unit_id = paa.tax_unit_id
976 and ppa.report_type = 'YREND'
977 and ppa.business_group_id + 0 = ppa1.business_group_id + 0
978 and ppa.effective_date = ppa1.effective_date
979 and ppa.start_date = ppa1.start_date
980 and paa.payroll_action_id = ppa.payroll_action_id
981 and paf.assignment_id = paa.assignment_id
982 and paf.person_id BETWEEN cp_start_person and cp_end_person
983 and paf.assignment_type = 'E'
984 and paf.effective_start_date <= ppa.effective_date
985 and paf.effective_end_date >= ppa.start_date
986 and to_char(paa.tax_unit_id) in (
987 select ffaic2.context
988 from ff_contexts ffc,
989 ff_user_entities ffue,
990 ff_archive_items ffai,
991 ff_archive_items ffai2,
992 ff_archive_item_contexts ffaic,
993 ff_archive_item_contexts ffaic2,
994 ff_contexts ffc2
995 where ffai.context1 = cp_yrend_ppa_id
996 and ffue.user_entity_id = ffai.user_entity_id
997 and ffue.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
998 and ffai.archive_item_id = ffaic.archive_item_id
999 and ffaic.context_id = ffc.context_id
1000 and ffc.context_name = 'TAX_UNIT_ID'
1001 and ffai2.user_entity_id = ffai.user_entity_id
1002 and ffai2.value = ffai.value
1003 and ffai2.context1 in (select payroll_action_id
1004 from pay_payroll_actions
1005 where report_type = 'YREND'
1006 and effective_date = ppa.effective_date)
1007 and ffai2.archive_item_id = ffaic2.archive_item_id
1008 and ffaic2.context_id = ffc2.context_id
1009 and ffc2.context_name = 'TAX_UNIT_ID')
1010 order by 1, 2, 3 desc, 4;
1011
1012
1013 -- Cursor to get the assignments for state 1099R. Gets only those employees
1014 -- which have wages for the specified state.This cursor only includes the
1015 -- 1099R GREs.
1016 -- Removed the exists clause as the YREND archiver does the same logic
1017 -- so it is safe to assume that the assignment action id we pick up should
1018 -- satisfy the exists clause.
1019 -- Now when choosing tax_unit_id we need to roll up to the TCC in the archiver.
1020
1021 cursor c_state(cp_payroll_action_id number,
1022 cp_start_person number,
1023 cp_end_person number,
1024 cp_yrend_ppa_id number) is
1025 select paf.person_id,
1026 psv.tax_unit_id, --to_number(hsck.segment1),
1027 paf.effective_end_date,
1028 paf.assignment_id,
1029 psv.w2_state_wages,
1030 psv.w2_state_income_tax
1031 from per_all_assignments_f paf,
1032 pay_us_state_w2_v psv,
1033 pay_payroll_actions ppa
1034 where ppa.payroll_action_id = cp_payroll_action_id
1035 and psv.year = to_number(to_char(ppa.effective_date, 'YYYY'))
1036 and psv.state_abbrev = ppa.report_qualifier
1037 and psv.assignment_id = paf.assignment_id
1038 and psv.state_ein <> 'FLI P.P. #' /* 9205571 */
1039 and paf.assignment_type = 'E'
1040 and paf.person_id between cp_start_person and cp_end_person
1041 and paf.effective_start_date <= ppa.effective_date
1042 and paf.effective_end_date >= ppa.start_date
1043 and paf.business_group_id + 0 = ppa.business_group_id + 0
1044 and to_char(psv.tax_unit_id) in
1045 (select ffaic2.context
1046 from ff_contexts ffc,
1047 ff_user_entities ffue,
1048 ff_archive_items ffai,
1049 ff_archive_items ffai2,
1050 ff_archive_item_contexts ffaic,
1051 ff_archive_item_contexts ffaic2,
1052 ff_contexts ffc2
1053 where ffai.context1 = cp_yrend_ppa_id
1054 and ffue.user_entity_id = ffai.user_entity_id
1055 and ffue.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
1056 and ffai.archive_item_id = ffaic.archive_item_id
1057 and ffaic.context_id = ffc.context_id
1058 and ffc.context_name = 'TAX_UNIT_ID'
1059 and ffai2.user_entity_id = ffai.user_entity_id
1060 and ffai2.value = ffai.value
1061 and ffai2.context1 in (select payroll_action_id
1062 from pay_payroll_actions
1063 where report_type = 'YREND'
1064 and effective_date = ppa.effective_date)
1065 and ffai2.archive_item_id = ffaic2.archive_item_id
1066 and ffaic2.context_id = ffc2.context_id
1067 and ffc2.context_name = 'TAX_UNIT_ID')
1068 order by 1, 2, 3 desc, 4;
1069
1070
1071 --local variables
1072 ld_effective_end_date date;
1073
1074 ln_person_id number;
1075 ln_prev_person_id number;
1076
1077 ln_assignment_id number;
1078 ln_tax_unit_id number;
1079 ln_prev_tax_unit_id number;
1080 ln_lockingactid number;
1081 ln_lockedactid number;
1082 ln_balance_value number;
1083
1084 ld_year_start date;
1085 ld_year_end date;
1086 lv_state_code varchar2(10);
1087 lv_state_abbrev varchar2(30);
1088 lv_report_type varchar2(30);
1089 ln_business_group_id number;
1090 lv_trans_cont_code varchar2(30);
1091 ln_yrend_ppa_id number;
1092
1093 ln_box_17 number;
1094 ln_box_18 number;
1095 ln_box1 number;
1096 ln_create_assignment number;
1097
1098 begin
1099
1100 hr_utility.trace('Entering pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1101
1102 -- Get the report parameters. These define the report being run.
1103 hr_utility.set_location(
1104 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',10);
1105
1106 get_selection_information (
1107 p_payroll_action_id,
1108 ld_year_start,
1109 ld_year_end,
1110 lv_state_code,
1111 lv_state_abbrev,
1112 lv_report_type,
1113 ln_business_group_id,
1114 ln_tax_unit_id,
1115 lv_trans_cont_code,
1116 ln_yrend_ppa_id);
1117
1118
1119
1120 --Open the appropriate cursor
1121
1122 hr_utility.set_location(
1123 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',20);
1124
1125 if lv_report_type = '1099R_FED' then
1126 open c_federal(p_payroll_action_id,
1127 p_start_person,
1128 p_end_person,
1129 ln_yrend_ppa_id);
1130 elsif lv_report_type = '1099R_STATE' then
1131 open c_state(p_payroll_action_id,
1132 p_start_person,
1133 p_end_person,
1134 ln_yrend_ppa_id);
1135 end if;
1136
1137 loop
1138 if lv_report_type = '1099R_FED' then
1139 fetch c_federal into ln_person_id,
1140 ln_tax_unit_id,
1141 ld_effective_end_date,
1142 ln_assignment_id,
1143 ln_box1;
1144
1145 hr_utility.set_location(
1146 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 30);
1147
1148 if c_federal%notfound then
1149 exit;
1150 end if;
1151
1152 elsif lv_report_type = '1099R_STATE' then
1153 fetch c_state into ln_person_id,
1154 ln_tax_unit_id,
1155 ld_effective_end_date,
1156 ln_assignment_id,
1157 ln_box_17,
1158 ln_box_18;
1159
1160 hr_utility.set_location(
1161 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 40);
1162
1163 if c_state%notfound then
1164 exit;
1165 end if;
1166
1167 end if;
1168
1169 --Based on the groupin criteria, check if the record is the same
1170 --as the previous record. If the records are not same then we
1171 --create the assignment actions and do the interlocking else
1172 --we do nothing.
1173 --Grouping by GRE requires a unique person/GRE combination for
1174 --each record.Only one of the condition will always be satisfied
1175 --if group by gre is TRUE then first condition will hold true
1176 --otherwise the second.
1177 if (ln_person_id = nvl(ln_prev_person_id, -1) and
1178 ln_tax_unit_id <> nvl(ln_prev_tax_unit_id, -1))
1179 or
1180 (ln_person_id <> nvl(ln_prev_person_id, -1)) then
1181
1182 --Create the assignment action for the record
1183 hr_utility.trace('Assignment Fetched - ');
1184 hr_utility.trace('Assignment Id : '|| to_char(ln_assignment_id));
1185 hr_utility.trace('Person Id : '|| to_char(ln_person_id));
1186 hr_utility.trace('tax unit id : '|| to_char(ln_tax_unit_id));
1187 hr_utility.trace('Effective End Date : '|| to_char(ld_effective_end_date));
1188
1189 if lv_report_type = '1099R_FED' then
1190 if ln_box1 > 0 then
1191 ln_create_assignment := 1;
1192 else
1193 ln_create_assignment := 0;
1194 end if;
1195
1196 elsif lv_report_type = '1099R_STATE' then
1197
1198 /* Bug 3209884 Changed boundry conditions for employees to be reported on tape
1199 (creating assignemnt_action_id) for 1099R_STATE for folowing States
1200 'AR' if SIT > 0 or State_wages > 2500 changed to
1201 'AR' if SIT > 0 or State_wages > 0
1202 'KS' was SIT > 0 must be paper so we have 'KS' if SIT = 0 changed to
1203 'KS' if SIT > 0 or State_wages > 0
1204 'MT' if SIT > 0 must be Paper so we check SIT = 0 only then report on tape
1205 */
1206 /* Bug 3349571 Reverted change for KS as if there is KS SIT,
1207 the 1099-R cannot be included on the magnetic tape */
1208
1209 if lv_state_abbrev in ('AR','CT','IN','WV','NY','AZ','SC','KS','MT') then
1210 if lv_state_abbrev = 'AR' then
1211 if ln_box_18 > 0 or ln_box_17 > 0 then
1212 ln_create_assignment := 1;
1213 else
1214 ln_create_assignment := 0;
1215 end if;
1216 elsif lv_state_abbrev = 'KS' OR lv_state_abbrev = 'MT' then
1217 if ln_box_18 = 0 then
1218 ln_create_assignment := 1;
1219 else
1220 ln_create_assignment := 0;
1221 end if;
1222 else
1223 if ln_box_18 > 0 then
1224 ln_create_assignment := 1;
1225 else
1226 ln_create_assignment := 0;
1227 end if;
1228 end if;
1229 else
1230 if ln_box_17 >= 0 then -- 4350849
1231 ln_create_assignment := 1;
1232 else
1233 ln_create_assignment := 0;
1234 end if;
1235 end if;
1236 end if;
1237
1238 if ln_create_assignment = 1 then
1239
1240 select pay_assignment_actions_s.nextval
1241 into ln_lockingactid from dual;
1242
1243 hr_utility.set_location(
1244 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 60);
1245
1246 hr_nonrun_asact.insact(ln_lockingactid, ln_assignment_id, p_payroll_action_id,
1247 p_chunk, ln_tax_unit_id);
1248
1249 hr_utility.set_location(
1250 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 70);
1251
1252 --Create Interlock
1253 select assignment_action_id into ln_lockedactid
1254 from pay_assignment_actions paa,
1255 per_all_assignments_f paf,
1256 pay_payroll_actions ppa
1257 where paa.payroll_action_id = ppa.payroll_action_id
1258 and paa.assignment_id = paf.assignment_id
1259 and paa.tax_unit_id = ln_tax_unit_id
1260 and ppa.report_type = 'YREND'
1261 and substr(legislative_parameters,
1262 instr(legislative_parameters, 'TRANSFER_GRE=') +
1263 length('TRANSFER_GRE=')) = to_char(ln_tax_unit_id)
1264 and ppa.effective_date = ld_year_end
1265 and ppa.start_date = ld_year_start
1266 and paf.effective_end_date = ld_effective_end_date
1267 and paf.assignment_id = ln_assignment_id
1268 and paf.effective_start_date <= ppa.effective_date; -- 4583577 Perf Change 2.
1269
1270 --insert into pay_action_interlocks
1271 hr_nonrun_asact.insint(ln_lockingactid, ln_lockedactid);
1272
1273 hr_utility.set_location(
1274 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 90);
1275 hr_utility.trace('Interlock Created - ');
1276 hr_utility.trace('Locking Action : '|| to_char(ln_lockingactid));
1277 hr_utility.trace('Locked Action : '|| to_char(ln_lockedactid));
1278
1279 --Store the current person/GRE for comparision during the
1280 --next iteration.
1281 ln_prev_person_id := ln_person_id;
1282 ln_prev_tax_unit_id := ln_tax_unit_id;
1283
1284 end if;
1285 end if;
1286
1287 ln_create_assignment := 0;
1288
1289 end loop;
1290
1291 if lv_report_type = '1099R_FED' then
1292 close c_federal;
1293 elsif lv_report_type = '1099R_STATE' then
1294 close c_state;
1295 end if;
1296
1297 hr_utility.trace('Exiting pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1298
1299 end mag_1099r_action_creation;
1300
1301
1302 end pay_us_1099r_mag_reporting;
[Home] [Help]