1 PACKAGE BODY pay_us_1099r_mag_reporting AS
2 /* $Header: pyyep99r.pkb 120.3 2006/03/16 22:38:40 pragupta noship $ */
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');
315 close c_yrend_ppa;
312 hr_utility.raise_error;
313 end if;
314
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.
439 --Arguments
436 -- b) If NO, then we check and see if the GRE should have been
437 -- archived.
438 --
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
546
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
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
669 exit;
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);
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
784 lv_state_abbrev varchar2(30);
781 ld_year_start date;
782 ld_year_end date;
783 lv_state_code varchar2(10);
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 decode(psv.state_abbrev, ''NY'', psv.w2_state_income_tax,
881 ''WV'', psv.w2_state_income_tax,
882 ''IN'', psv.w2_state_income_tax,
883 ''CT'', psv.w2_state_income_tax,
884 ''SC'', psv.w2_state_income_tax,
885 ''AZ'', psv.w2_state_income_tax,
886 psv.w2_state_wages) >= 0 -- 4350849
887 and psv.state_abbrev = ppa.report_qualifier
888 and psv.assignment_id = paf.assignment_id
889 and paf.assignment_type = ''E''
890 and paf.effective_start_date <= ppa.effective_date
891 and paf.effective_end_date >= ppa.start_date
892 and paf.business_group_id + 0 = ppa.business_group_id
893 --and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
894 --and hsck.segment1 = psv.tax_unit_id
895 --and hsck.segment1 in
896 and psv.tax_unit_id in
900 order by paf.person_id';
897 (select hoi.organization_id
898 from hr_organization_information hoi
899 where hoi.org_information_context = ''1099R Magnetic Report Rules'')
901
902 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor', 80);
903
904 end if;
905
906 hr_utility.set_location('pay_us_1099r_mag_reporting.range_cursor',90);
907
908 p_sql_string := lv_sql_string;
909
910 end if;
911
912 hr_utility.trace('Exiting pay_us_1099r_mag_reporting.range_cursor');
913
914 end range_cursor;
915
916
917
918 -----------------------------------------------------------------------------
919 --Name
920 -- mag_1099r_action_creation
921 --Purpose
922 -- Creates assignment actions for the payroll action associated with the
923 -- report and only for the assignments on the particular Transmitter
924 --
925 --Arguments
926 -- p_payroll_action_id payroll action for the report
927 -- p_start_person starting person id for the chunk
928 -- p_end_person last person id for the chunk
929 -- p_chunk size of the chunk
930 --Note
931 -- The procedure processes assignments in 'chunks' to facilitate
932 -- multi-threaded operation. The chunk is defined by the size and the
933 -- starting and ending person id. An interlock is also created against the
934 -- pre-processor assignment action to prevent rolling back of the archiver.
935 -- Now the year end pre processor is archiving all assignments and all
936 -- balances so we must check to see if the Federal Gross is greater than
937 -- zero.
938 ----------------------------------------------------------------------------
939 --
940
941 Procedure mag_1099r_action_creation
942 (p_payroll_action_id in number,
943 p_start_person in number,
944 p_end_person in number,
945 p_chunk in number)
946
947 is
948
949
950 -- Cursor to get the assignments for federal 1099R. Includes only 1099R GREs.
951 -- Removed the exists clause as the YREND archiver does the same logic
952 -- so it is safe to assume that the assignment action id we pick up should
953 -- satisfy the exists clause.
954 -- Now when choosing tax_unit_id we need to roll up to the TCC from the archiver.
955
956 cursor c_federal(cp_payroll_action_id number,
957 cp_start_person number,
958 cp_end_person number,
959 cp_yrend_ppa_id number) is
960 select paf.person_id,
961 paa.tax_unit_id,
962 paf.effective_end_date,
963 paf.assignment_id,
964 --pww.wages_tips_compensation
965 pww.gross_1099r
966 from pay_payroll_actions ppa,
967 pay_payroll_actions ppa1,
968 pay_us_wages_1099r_v pww,
969 per_all_assignments_f paf,
970 pay_assignment_actions paa
971 where ppa1.payroll_action_id = cp_payroll_action_id
972 and pww.year = to_number(to_char(ppa.effective_date, 'YYYY'))
973 and pww.assignment_id = paf.assignment_id
974 and pww.tax_unit_id = paa.tax_unit_id
975 and ppa.report_type = 'YREND'
976 and ppa.business_group_id + 0 = ppa1.business_group_id + 0
977 and ppa.effective_date = ppa1.effective_date
978 and ppa.start_date = ppa1.start_date
979 and paa.payroll_action_id = ppa.payroll_action_id
980 and paf.assignment_id = paa.assignment_id
981 and paf.person_id BETWEEN cp_start_person and cp_end_person
982 and paf.assignment_type = 'E'
983 and paf.effective_start_date <= ppa.effective_date
984 and paf.effective_end_date >= ppa.start_date
985 and to_char(paa.tax_unit_id) in (
986 select ffaic2.context
987 from ff_contexts ffc,
988 ff_user_entities ffue,
989 ff_archive_items ffai,
990 ff_archive_items ffai2,
991 ff_archive_item_contexts ffaic,
992 ff_archive_item_contexts ffaic2,
993 ff_contexts ffc2
994 where ffai.context1 = cp_yrend_ppa_id
995 and ffue.user_entity_id = ffai.user_entity_id
996 and ffue.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
997 and ffai.archive_item_id = ffaic.archive_item_id
998 and ffaic.context_id = ffc.context_id
999 and ffc.context_name = 'TAX_UNIT_ID'
1000 and ffai2.user_entity_id = ffai.user_entity_id
1001 and ffai2.value = ffai.value
1002 and ffai2.context1 in (select payroll_action_id
1003 from pay_payroll_actions
1004 where report_type = 'YREND'
1005 and effective_date = ppa.effective_date)
1006 and ffai2.archive_item_id = ffaic2.archive_item_id
1007 and ffaic2.context_id = ffc2.context_id
1008 and ffc2.context_name = 'TAX_UNIT_ID')
1009 order by 1, 2, 3 desc, 4;
1010
1011
1012 -- Cursor to get the assignments for state 1099R. Gets only those employees
1013 -- which have wages for the specified state.This cursor only includes the
1014 -- 1099R GREs.
1015 -- Removed the exists clause as the YREND archiver does the same logic
1016 -- so it is safe to assume that the assignment action id we pick up should
1017 -- satisfy the exists clause.
1021 cp_start_person number,
1018 -- Now when choosing tax_unit_id we need to roll up to the TCC in the archiver.
1019
1020 cursor c_state(cp_payroll_action_id number,
1022 cp_end_person number,
1023 cp_yrend_ppa_id number) is
1024 select paf.person_id,
1025 psv.tax_unit_id, --to_number(hsck.segment1),
1026 paf.effective_end_date,
1027 paf.assignment_id,
1028 psv.w2_state_wages,
1029 psv.w2_state_income_tax
1030 from per_all_assignments_f paf,
1031 pay_us_state_w2_v psv,
1032 pay_payroll_actions ppa
1033 where ppa.payroll_action_id = cp_payroll_action_id
1034 and psv.year = to_number(to_char(ppa.effective_date, 'YYYY'))
1035 and psv.state_abbrev = ppa.report_qualifier
1036 and psv.assignment_id = paf.assignment_id
1037 and paf.assignment_type = 'E'
1038 and paf.person_id between cp_start_person and cp_end_person
1039 and paf.effective_start_date <= ppa.effective_date
1040 and paf.effective_end_date >= ppa.start_date
1041 and paf.business_group_id + 0 = ppa.business_group_id + 0
1042 and to_char(psv.tax_unit_id) in
1043 (select ffaic2.context
1044 from ff_contexts ffc,
1045 ff_user_entities ffue,
1046 ff_archive_items ffai,
1047 ff_archive_items ffai2,
1048 ff_archive_item_contexts ffaic,
1049 ff_archive_item_contexts ffaic2,
1050 ff_contexts ffc2
1051 where ffai.context1 = cp_yrend_ppa_id
1052 and ffue.user_entity_id = ffai.user_entity_id
1053 and ffue.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
1054 and ffai.archive_item_id = ffaic.archive_item_id
1055 and ffaic.context_id = ffc.context_id
1056 and ffc.context_name = 'TAX_UNIT_ID'
1057 and ffai2.user_entity_id = ffai.user_entity_id
1058 and ffai2.value = ffai.value
1059 and ffai2.context1 in (select payroll_action_id
1060 from pay_payroll_actions
1061 where report_type = 'YREND'
1062 and effective_date = ppa.effective_date)
1063 and ffai2.archive_item_id = ffaic2.archive_item_id
1064 and ffaic2.context_id = ffc2.context_id
1065 and ffc2.context_name = 'TAX_UNIT_ID')
1066 order by 1, 2, 3 desc, 4;
1067
1068
1069 --local variables
1070 ld_effective_end_date date;
1071
1072 ln_person_id number;
1073 ln_prev_person_id number;
1074
1075 ln_assignment_id number;
1076 ln_tax_unit_id number;
1077 ln_prev_tax_unit_id number;
1078 ln_lockingactid number;
1079 ln_lockedactid number;
1080 ln_balance_value number;
1081
1082 ld_year_start date;
1083 ld_year_end date;
1084 lv_state_code varchar2(10);
1085 lv_state_abbrev varchar2(30);
1086 lv_report_type varchar2(30);
1087 ln_business_group_id number;
1088 lv_trans_cont_code varchar2(30);
1089 ln_yrend_ppa_id number;
1090
1091 ln_box_17 number;
1092 ln_box_18 number;
1093 ln_box1 number;
1094 ln_create_assignment number;
1095
1096 begin
1097
1098 hr_utility.trace('Entering pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1099
1100 -- Get the report parameters. These define the report being run.
1101 hr_utility.set_location(
1102 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',10);
1103
1104 get_selection_information (
1105 p_payroll_action_id,
1106 ld_year_start,
1107 ld_year_end,
1108 lv_state_code,
1109 lv_state_abbrev,
1110 lv_report_type,
1111 ln_business_group_id,
1112 ln_tax_unit_id,
1113 lv_trans_cont_code,
1114 ln_yrend_ppa_id);
1115
1116
1117
1118 --Open the appropriate cursor
1119
1120 hr_utility.set_location(
1121 'pay_us_1099r_mag_reporting.mag_1099r_action_creation',20);
1122
1123 if lv_report_type = '1099R_FED' then
1124 open c_federal(p_payroll_action_id,
1125 p_start_person,
1126 p_end_person,
1127 ln_yrend_ppa_id);
1128 elsif lv_report_type = '1099R_STATE' then
1129 open c_state(p_payroll_action_id,
1130 p_start_person,
1131 p_end_person,
1132 ln_yrend_ppa_id);
1133 end if;
1134
1135 loop
1136 if lv_report_type = '1099R_FED' then
1137 fetch c_federal into ln_person_id,
1138 ln_tax_unit_id,
1139 ld_effective_end_date,
1140 ln_assignment_id,
1141 ln_box1;
1142
1143 hr_utility.set_location(
1144 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 30);
1145
1146 if c_federal%notfound then
1147 exit;
1148 end if;
1149
1150 elsif lv_report_type = '1099R_STATE' then
1151 fetch c_state into ln_person_id,
1152 ln_tax_unit_id,
1153 ld_effective_end_date,
1157
1154 ln_assignment_id,
1155 ln_box_17,
1156 ln_box_18;
1158 hr_utility.set_location(
1159 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 40);
1160
1161 if c_state%notfound then
1162 exit;
1163 end if;
1164
1165 end if;
1166
1167 --Based on the groupin criteria, check if the record is the same
1168 --as the previous record. If the records are not same then we
1169 --create the assignment actions and do the interlocking else
1170 --we do nothing.
1171 --Grouping by GRE requires a unique person/GRE combination for
1172 --each record.Only one of the condition will always be satisfied
1173 --if group by gre is TRUE then first condition will hold true
1174 --otherwise the second.
1175 if (ln_person_id = nvl(ln_prev_person_id, -1) and
1176 ln_tax_unit_id <> nvl(ln_prev_tax_unit_id, -1))
1177 or
1178 (ln_person_id <> nvl(ln_prev_person_id, -1)) then
1179
1180 --Create the assignment action for the record
1181 hr_utility.trace('Assignment Fetched - ');
1182 hr_utility.trace('Assignment Id : '|| to_char(ln_assignment_id));
1183 hr_utility.trace('Person Id : '|| to_char(ln_person_id));
1184 hr_utility.trace('tax unit id : '|| to_char(ln_tax_unit_id));
1185 hr_utility.trace('Effective End Date : '|| to_char(ld_effective_end_date));
1186
1187 if lv_report_type = '1099R_FED' then
1188 if ln_box1 > 0 then
1189 ln_create_assignment := 1;
1190 else
1191 ln_create_assignment := 0;
1192 end if;
1193
1194 elsif lv_report_type = '1099R_STATE' then
1195
1196 /* Bug 3209884 Changed boundry conditions for employees to be reported on tape
1197 (creating assignemnt_action_id) for 1099R_STATE for folowing States
1198 'AR' if SIT > 0 or State_wages > 2500 changed to
1199 'AR' if SIT > 0 or State_wages > 0
1200 'KS' was SIT > 0 must be paper so we have 'KS' if SIT = 0 changed to
1201 'KS' if SIT > 0 or State_wages > 0
1202 'MT' if SIT > 0 must be Paper so we check SIT = 0 only then report on tape
1203 */
1204 /* Bug 3349571 Reverted change for KS as if there is KS SIT,
1205 the 1099-R cannot be included on the magnetic tape */
1206
1207 if lv_state_abbrev in ('AR','CT','IN','WV','NY','AZ','SC','KS','MT') then
1208 if lv_state_abbrev = 'AR' then
1209 if ln_box_18 > 0 or ln_box_17 > 0 then
1210 ln_create_assignment := 1;
1211 else
1212 ln_create_assignment := 0;
1213 end if;
1214 elsif lv_state_abbrev = 'KS' OR lv_state_abbrev = 'MT' then
1215 if ln_box_18 = 0 then
1216 ln_create_assignment := 1;
1217 else
1218 ln_create_assignment := 0;
1219 end if;
1220 else
1221 if ln_box_18 > 0 then
1222 ln_create_assignment := 1;
1223 else
1224 ln_create_assignment := 0;
1225 end if;
1226 end if;
1227 else
1228 if ln_box_17 >= 0 then -- 4350849
1229 ln_create_assignment := 1;
1230 else
1231 ln_create_assignment := 0;
1232 end if;
1233 end if;
1234 end if;
1235
1236 if ln_create_assignment = 1 then
1237
1238 select pay_assignment_actions_s.nextval
1239 into ln_lockingactid from dual;
1240
1241 hr_utility.set_location(
1242 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 60);
1243
1244 hr_nonrun_asact.insact(ln_lockingactid, ln_assignment_id, p_payroll_action_id,
1245 p_chunk, ln_tax_unit_id);
1246
1247 hr_utility.set_location(
1248 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 70);
1249
1250 --Create Interlock
1251 select assignment_action_id into ln_lockedactid
1252 from pay_assignment_actions paa,
1253 per_all_assignments_f paf,
1254 pay_payroll_actions ppa
1255 where paa.payroll_action_id = ppa.payroll_action_id
1256 and paa.assignment_id = paf.assignment_id
1257 and paa.tax_unit_id = ln_tax_unit_id
1258 and ppa.report_type = 'YREND'
1259 and substr(legislative_parameters,
1260 instr(legislative_parameters, 'TRANSFER_GRE=') +
1261 length('TRANSFER_GRE=')) = to_char(ln_tax_unit_id)
1262 and ppa.effective_date = ld_year_end
1263 and ppa.start_date = ld_year_start
1264 and paf.effective_end_date = ld_effective_end_date
1265 and paf.assignment_id = ln_assignment_id
1266 and paf.effective_start_date <= ppa.effective_date; -- 4583577 Perf Change 2.
1267
1268 --insert into pay_action_interlocks
1269 hr_nonrun_asact.insint(ln_lockingactid, ln_lockedactid);
1270
1271 hr_utility.set_location(
1272 'pay_us_1099r_mag_reporting.mag_1099r_action_creation', 90);
1273 hr_utility.trace('Interlock Created - ');
1277 --Store the current person/GRE for comparision during the
1274 hr_utility.trace('Locking Action : '|| to_char(ln_lockingactid));
1275 hr_utility.trace('Locked Action : '|| to_char(ln_lockedactid));
1276
1278 --next iteration.
1279 ln_prev_person_id := ln_person_id;
1280 ln_prev_tax_unit_id := ln_tax_unit_id;
1281
1282 end if;
1283 end if;
1284
1285 ln_create_assignment := 0;
1286
1287 end loop;
1288
1289 if lv_report_type = '1099R_FED' then
1290 close c_federal;
1291 elsif lv_report_type = '1099R_STATE' then
1292 close c_state;
1293 end if;
1294
1295 hr_utility.trace('Exiting pay_us_1099r_mag_reporting.mag_1099r_action_creation');
1296
1297 end mag_1099r_action_creation;
1298
1299
1300 end pay_us_1099r_mag_reporting;