1 PACKAGE BODY pay_us_mmref_reporting AS
2 /* $Header: pyusmmye.pkb 120.28.12020000.5 2013/01/11 14:24:40 skchalla ship $ */
3 /*REM +======================================================================+
4 REM | Copyright (c) 1997 Oracle Corporation |
5 REM | Redwood Shores, California, USA |
6 REM | All rights reserved. |
7 REM +======================================================================+
8 REM Package Body Name : pay_us_mmref_reporting
9 REM Package File Name : pay_us_mmref_reporting.pkb
10 REM Description : This package declares functions and procedures to support
11 REM the genration of magnetic W2 reports for US legislative requirements
12 REM incorporating magtape resilience and the new end-of-year processing.
13 REM
14 REM Change List:
15 REM ------------
16 REM
17 REM Name Date Version Bug Text
18 REM --------- ---------- ------- ----------- ------------------------------
19 REM djoshi 24-aug-2001 40.01 Created
20 REM djoshi 20-sep-2001 40.02 Modified the files to
21 REM use Core Message Functions
22 REM djoshi 16-nov-2001 115.3 Made changes for YE 2001
23 REM djoshi Phase II
24 REM djoshi 03-dec-2001 115.5 changed to code for dbdrv
25 REM djoshi 05-dec-2001 115.6 Added the Check for State
26 REM tax rules Checking
27 REM djoshi 17-jan-2002 115.7 2190825 Changed the code for bug
28 REM djoshi 21-jan-2002 115.8 Added checkfile:
29 REM fusman 02-jul-2002 115.6 2296797 Added legislation Code
30 REM asasthan 11-NOV-2002 115.12 2586041 Added Index hint
31 REM INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1)
32 REM djoshi 13-nov-2002 115.13 Changed the local variable to be char
33 REM to overcome convertion problems in
34 REM 9i database.
35 REM ppanda 02-DEC-2002 115.14 Nocopy hint added to OUT and IN OUT parameters
36 REM ppanda 20-Jan-2003 115.15 2736928 For PuertoRico a validation added in Preprocess to check
37 REM whether Control Number defined or not. If Control number
38 REM not defined process will Error out
39 REM ppanda 22-Jan-2003 115.16 For PuertoRico a separate cursor added to create assignment
40 REM ppanda 20-Oct-2003 115.18 3069840 Federal/State W-2 Mag tapes should issue warning if GRE
41 REM is not archived
42 REM tmehra 10-Nov-2003 115.19 2778457 Federal Mag not picking up
43 REM the employees with 0 gorss.
44 REM Added check for SS Medicare
45 REM and FIT balances.
46 REM tmehra 26-Nov-2003 115.20 2219097 Added a new function for Govt
47 REM Employer W2 changes
48 REM - get_report_category
49 REM tmehra 01-Dec-2003 115.21 2219097 Added the new category 'RG'
50 REM to the cursor c_transmitter
51 REM ppanda 14-DEC-2003 115.22 2778457 A_GROSS_EARNINGS_PER_GRE_YTD was added
52 REM to csr_get_fed_Wages cursor as fix for this
53 REM bug was breaking FED W-2. This cursor is used for
54 REM checking balances for creating assignment for FED W-2
55 REM tmehra 29-DEC-2003 115.23 2778457 Added FIT Subject balance
56 REM check and removed the
57 REM Gross Earnings check from
58 REM Fed Mag Tape.
59 REM asasthan 30-JUL-2004 115.25 3343633 Part of preprocess check was
60 REM removed earlier but the cursors
61 REM c_gre_fed, c_gre_state were
62 REM not removed and resulted
63 REM in 11510 bug on performance.
64 REM These cursors are now being
65 REM removed.
66 REM asasthan 30-JUL-2004 115.26 3343633 Removed +0 from c_get_gre(bgid)
67 REM pragupta 22-JUL-2005 115.27 4344872 cursor c_person_in_state removed. It was a redundant
68 REM cursor and was not used any where in the package.
69 REM pragupta 36-OCT-2005 115.28 4490252 Commented out the code for checking highly paid
70 REM person in the create_assignment_act procedure.
71 REM
72 REM djoshi 14-feb-2005 115.29 5009863 Changed state and federal Cursor
73 REM to join paa.serial_number
74 REM ppanda 31-JUL-2006 115.30 Federal W-2 Mag tapes to support multi thread architecture
75 REM A new function get_report_category_multi_thread would be
76 REM used to derive the report category for the new concurrent
77 REM program.
78 REM ppanda 28-AUG-2006 115.31 Three New formula function added to the package
79 REM for Federal W-2 Magnetic Media MultiThread process
80 REM
81 REM sudedas 08-NOV-2006 115.32 5099892 range_cursor and create_assignment_act
82 REM has been changed for State of Indiana
83 REM so that EE is eligible to be included
84 REM in Tape when County Withheld is non-zero
85 REM even when State Wage is zero.
86 REM 5630156 Removed the check of State Code
87 REM in pay_us_state_tax_info_f within
88 REM cursor c_state of create_assignment_act
89 REM due to Performance Issue.
90 REM sudedas 13-NOV-2006 115.33 5648738 range_cursor and create_assignment_act
91 REM has been changed for State of Ohio
92 REM to include EE with non-zero School Withheld.
93 REM Also corrected jurisdiction_level for IN
94 REM ppanda 12-DEC-2006 115.35 Federal W-2 Multithread ver was resulting
95 REM decimal values in RT due to rounding issues.
96 REM function get_w2_er_arch_bal modified for rounding
97 REM Bug # 5711922 fixed to resolve the RT record
98 REM ppanda 13-DEC-2006 115.36 Function modified b adding additional parameters
99 REM assignment_action_id, p_tax_jd_code, p_tax_unit_info1 and , p_tax_unit_info2
100 REM This is to fix Bug # 5709609
101 REM ppanda 14-DEC-2006 115.38 Function get_w2_er_arch_bal changed for rounding the values
102 REM ppanda 14-DEC-2006 115.39 Function get_w2_er_arch_bal modified to count Error out RW record
103 REM
104 REM ppanda 15-DEC-2006 115.40 Function get_w2_er_arch_bal modified for SS Wages
105 REM and Non-qualified Plan Not Section 457 Distributions
106 REM sudedas 02-Jan-2007 115.41 5739737 Changed range_cursor and create_assignment_act
107 REM for Indiana and Ohio to pass correct Jurisdiction
108 REM Context.
109 REM ppanda 11-JAN-2007 115.42 For erroring out employee and moving employee details to a02
110 REM a new parameter added to set_application_error
111 REM The new parameter is Assingnment_action_id
112 REM tclewis 14-dec-2007 115.43 6695132 Type cast the numeric varisables p_stperson and p_endperson
113 REM in the cursors create_assignment_act procedure.
114 REM svannian 02-jan-2008 115.44 6712859 Remove the Type cast since Federal W2 Magnetic Media
115 REM did not have correct income numbers.
116 REM svannian 20-feb-2008 115.45 6809739 state w2 to pick up employees when
117 REM either sit wages or sit tax is greater than zero.
118 REM svannian 22-mar-2008 115.46 6868340 federal w2 to pick up employees when
119 REM either fit wages or fit tax is greater than zero.
120 REM svannian 29-may-2008 114.47 7109106 PR GTL amount should not be included in RT record.
121 REM asgugupt 18-Aug-2010 114.48 9467090 Enabled Range Person Id for improving performance.
122 REM asgugupt 14-Dec-2010 114.49 10390984 Modified get_w2_er_arch_bal for hire act changes
123 REM sgotlasw 21-Oct-2011 115.53 11907144 Added additional condition to pick the employee in
124 REM Federal W2 Mag Media Report, if there are 'SS Wages',
125 REM 'Medicare Wages' for the employee.
126 REM emunisek 17-Nov-2011 115.54 13326069 Made changes to Function get_w2_er_arch_bal to calculate
127 REM Federal Wages based on Direct US Federal Balances Profile
128 REM option
129 REM skchalla 14-12-2011 115.55 13497022 Added two parameters to the formula function
130 REM get_w2_er_arch_bal for W2 Fedaral Mag in Multi mode
131 REM emunisek 25-Jan-2012 115.57 13614766 Added change to check the Data Archived before using
132 REM Direct Balance approach
133 REM pkoduri 21-NOV-12 115.58 14286448 Corrections for GRE name length issue.
134 REM skchalla 23-NOV-12 115.59 14286448 Corrections for GRE name length issue.
135 REM skchalla 11-JAN-13 115.60 14223392 Corrections for GRE name length issue.
136 REM ========================================================================
137 REM
138
139 -----------------------------------------------------------------------------
140 -- Name : bal_db_item
141 -- Purpose : Given the name of a balance DB item as would be seen in a
142 -- fast formula it returns the defined_balance_id of the
143 -- balance it represents.
144 -- Arguments
145 -- INPUT : p_db_item_name
146 -- returns : l_defined_balance_id
147 -- Notes
148 -- A defined_balance_id is required by the PLSQL balance function.
149 -----------------------------------------------------------------------------
150 */
151 FUNCTION bal_db_item
152 ( p_db_item_name VARCHAR2
153 ) RETURN NUMBER IS
154 -- Get the defined_balance_id for the specified balance DB item.
155 CURSOR csr_defined_balance IS
156 SELECT TO_NUMBER(UE.creator_id)
157 FROM ff_database_items DI,
158 ff_user_entities UE
159 WHERE DI.user_name = p_db_item_name
160 AND UE.user_entity_id = DI.user_entity_id
161 AND UE.creator_type = 'B'
162 AND UE.legislation_code = 'US'; /* Bug:2296797 */
163 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
164 BEGIN
165 hr_utility.set_location
166 ('pay_us_mmref_reporting.bal_db_item - opening cursor', 10);
167 -- Open the cursor
168 OPEN csr_defined_balance;
169 -- Fetch the value
170 FETCH csr_defined_balance
171 INTO l_defined_balance_id;
172 IF csr_defined_balance%NOTFOUND THEN
173 CLOSE csr_defined_balance;
174 hr_utility.set_location
175 ('pay_us_mmref_reporting.bal_db_item - no rows found from cursor', 20);
176 hr_utility.raise_error;
177 ELSE
178 hr_utility.set_location
179 ('pay_us_mmref_reporting.bal_db_item - fetched from cursor', 30);
180 CLOSE csr_defined_balance;
181 END IF;
182 /* Return the value to the call */
183 RETURN (l_defined_balance_id);
184 END bal_db_item;
185 -----------------------------------------------------------------------------
186 -- Name : :get_report_parameters
187 --
188 -- Purpose
189 -- The procedure gets the 'parameter' for which the report is being
190 -- run i.e., the period, state and business organization.
191 --
192 -- Arguments
193 -- p_year_start Start Date of the period for which the report
194 -- has been requested
195 -- p_year_end End date of the period
196 -- p_business_group_id Business group for which the report is being run
197 -- p_state_abbrev Two digit state abbreviation (or 'FED' for federal
198 -- report)
199 -- p_state_code State code (NULL for federal)
200 -- p_report_type Type of report being run (FEDW2, STW2, 1099R ...)
201 --
202 -- Notes
203 ----------------------------------------------------------------------------
204
205 PROCEDURE get_report_parameters
206 ( p_pactid IN NUMBER,
207 p_year_start IN OUT nocopy DATE,
208 p_year_end IN OUT nocopy DATE,
209 p_state_abbrev IN OUT nocopy VARCHAR2,
210 p_state_code IN OUT nocopy VARCHAR2,
211 p_report_type IN OUT nocopy VARCHAR2,
212 p_business_group_id IN OUT nocopy NUMBER
213 ) IS
214 BEGIN
215 hr_utility.set_location
216 ('pay_us_mmref_reporting.get_report_parameters', 10);
217 SELECT ppa.start_date,
218 ppa.effective_date,
219 ppa.business_group_id,
220 ppa.report_qualifier,
221 ppa.report_type
222 INTO p_year_start,
223 p_year_end,
224 p_business_group_id,
225 p_state_abbrev,
226 p_report_type
227 FROM pay_payroll_actions ppa
228 WHERE payroll_action_id = p_pactid;
229 IF p_state_abbrev <> 'FED' THEN
230 SELECT state_code
231 INTO p_state_code
232 FROM pay_us_states
233 WHERE state_abbrev = p_state_abbrev;
234 hr_utility.set_location
235 ('pay_us_mmref_reporting.get_report_parameters', 20);
236 ELSE
237 p_state_code := '';
238 hr_utility.set_location
239 ('pay_us_mmref_reporting.get_report_parameters', 30);
240 END IF;
241 IF p_state_abbrev = 'FED' AND p_report_type = 'W2' THEN
242 p_report_type := 'FEDW2';
243 ELSIF p_report_type = 'W2' THEN
244 p_report_type := 'STW2';
245 END IF;
246 hr_utility.set_location
247 ('pay_us_mmref_reporting.get_report_parameters', 40);
248 END get_report_parameters;
249 -------------------------------------------------------------------------
250 -- Name : get_balance_value
251 --
252 --Purpose
253 -- Get the value of the specified balance item
254 --Arguments
255 -- p_balance_name Name of the balnce
256 -- p_tax_unit_id GRE name for the context
257 -- p_state_code State for context
258 -- p_assignment_id Assignment for whom the balance is to be
259 -- retrieved
260 -- p_effective_date effective_date
261 --Note
262 -- This procedure set is a wrapper for setting the GRE/Jurisdiction context
263 -- needed by the pay_balance_pkg.get_value to get the actual balance
264 -------------------------------------------------------------------------
265 FUNCTION get_balance_value (
266 p_balance_name VARCHAR2,
267 p_tax_unit_id NUMBER,
268 p_state_abbrev VARCHAR2,
269 p_assignment_id NUMBER,
270 p_effective_date DATE
271 ) RETURN NUMBER IS
272 l_jurisdiction_code VARCHAR2(20);
273 BEGIN
274 hr_utility.set_location
275 ('pay_us_mmref_reporting.get_balance_value', 10);
276 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
277 IF p_state_abbrev <> 'FED' THEN
278 SELECT jurisdiction_code
279 INTO l_jurisdiction_code
280 FROM pay_state_rules
281 WHERE state_code = p_state_abbrev;
282 hr_utility.set_location
283 ('pay_us_mmref_reporting.get_balance_value', 15);
284 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction_code);
285 END IF;
286 hr_utility.trace(p_balance_name);
287 hr_utility.trace('Context');
288 hr_utility.trace('Tax Unit Id: '|| p_tax_unit_id);
289 hr_utility.trace('Jurisdiction: '|| l_jurisdiction_code);
290 hr_utility.set_location
291 ('pay_us_mmref_reporting.get_balance_value', 20);
292 RETURN pay_balance_pkg.get_value(bal_db_item(p_balance_name),
293 p_assignment_id, p_effective_date);
294 END get_balance_value;
295 --------------------------------------------------------------------------
296 --Name
297 -- preprocess_check
298 --Purpose
299 -- This function checks if the year end preprocessor has been run for the
300 -- GREs involved in the W2 report. It also checks if any of the assignments
301 -- have errored out or have been marked for retry.
302 --
303 --Arguments
304 -- p_pactid payroll_action_id for the report
305 -- p_year_start start date of the period for which the report
306 -- has been requested
307 -- p_year_end end date of the period
308 -- p_business_group_id business group for which the report is being run
309 -- p_state_abbrev two digit state abbreviation (or 'FED' for federal
310 -- report)
311 -- p_state_code state code (NULL for federal)
312 -- p_report_type type of report being run (W2, 1099R ...)
313 --
314 --Notes
315 -- The check for 'errored'/'marked for retry'assignments can be bypassed by
316 -- setting the parameter 'FORCE_MAG_REPORT' to 'E' and 'M' respectively. In
317 -- such cases the report will ignore the assignments in question.
318 -----------------------------------------------------------------------------
319 FUNCTION preprocess_check
320 (
321 p_pactid NUMBER,
322 p_year_start DATE,
323 p_year_end DATE,
324 p_business_group_id NUMBER,
325 p_state_abbrev VARCHAR2,
326 p_state_code VARCHAR2,
327 p_report_type VARCHAR2
328 )
329 RETURN BOOLEAN IS
330 -- Cursor to get all the GREs belonging to the given business group
331 CURSOR c_get_gre IS
332 SELECT hou.organization_id gre
333 FROM hr_organization_information hoi,
334 hr_all_organization_units hou
335 WHERE hou.business_group_id = p_business_group_id AND
336 hoi.organization_id = hou.organization_id AND
337 hoi.org_information_context = 'CLASS' AND
338 hoi.org_information1 = 'HR_LEGAL' AND
339 NOT EXISTS (
340 SELECT 'Y'
341 FROM hr_organization_information
342 WHERE organization_id = hou.organization_id
343 AND org_information_context = '1099R Magnetic Report Rules');
344
345 -- Check if the GRE needs to be archived.
346 -- Cursor to fetch people in a given GRE with earnings in the given state to
347
348 CURSOR c_tax_ein
349 IS
350 SELECT user_entity_id from ff_user_entities
351 WHERE user_entity_name = 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER';
352
353
354
355 -- Cursor to fetch people from the GRE belonging to the business group
356
357
358
359 -- Cursor to get payroll_action_ids of the pre-process for the given GRE.
360 -- This will also serve as a check to make sure that all GREs have been
361 -- archived
362 CURSOR c_gre_payroll_action (cp_gre NUMBER)
363 IS
364 SELECT payroll_action_id
365 FROM pay_payroll_actions
366 WHERE report_type = 'YREND'
367 AND effective_date = p_year_end
368 AND start_date = p_year_start
369 AND business_group_id+0 = p_business_group_id
370 AND SUBSTR(legislative_parameters,
371 INSTR(legislative_parameters, 'TRANSFER_GRE=') +
372 LENGTH('TRANSFER_GRE=')) = TO_CHAR(cp_gre)
373 -- ADDED FOLLOWING CHECK CONDITION
374 AND action_status = 'C';
375
376 --Cursor for checking if any of the the archiver has errored for
377 --any of the assignments for federal W
378
379 CURSOR c_arch_errored_asg (cp_payroll_action_id NUMBER) IS
380 SELECT '1'
381 FROM dual
382 WHERE EXISTS (SELECT '1'
383 FROM pay_assignment_actions paa
384 WHERE paa.payroll_action_id = cp_payroll_action_id
385 AND paa.action_status = 'E'
386 )
387 AND NOT EXISTS ( SELECT '1'
388 FROM pay_action_parameters
389 WHERE parameter_name = 'FORCE_MAG_REPORT'
390 AND INSTR(parameter_value, 'E') > 0
391 );
392 --Cursor for checking if any of the assignments have been marked for retry
393
394 CURSOR c_arch_retry_pending (cp_payroll_action_id NUMBER) IS
395 SELECT '1'
396 FROM dual
397 WHERE EXISTS (SELECT '1'
398 FROM pay_assignment_actions paa
399 WHERE paa.payroll_action_id = cp_payroll_action_id
400 AND paa.action_status = 'M')
401 AND NOT EXISTS (SELECT '1'
402 FROM pay_action_parameters
403 WHERE parameter_name = 'FORCE_MAG_REPORT'
404 AND INSTR(parameter_value, 'R') > 0
405 );
406
407 /* cursor to get user_entity_id */
408 CURSOR c_user_entity_id_of_bal
409 IS
410 SELECT user_entity_id
411 FROM ff_database_items fdi
412 WHERE user_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' ;
413
414
415 /* cursor to get_context_of_tax_unit_id */
416 CURSOR c_context_tax_unit_id
417 IS
418 SELECT context_id
419 FROM ff_contexts
420 WHERE context_name = 'TAX_UNIT_ID';
421
422 /* cursor to get context of jurisdiction */
423 CURSOR c_context_jurisdiction
424 IS
425 SELECT context_id
426 FROM ff_contexts
427 WHERE context_name = 'JURISDICTION_CODE';
428
429 /* cursor to check if the state tax Rules have been added or Not. */
430 CURSOR c_chk_archive_state_code(cp_tax_unit_id number,cp_payroll_action_id number)
431 IS
432 SELECT 'Y'
433 FROM ff_archive_item_contexts con3,
434 ff_archive_item_contexts con2,
435 ff_contexts fc3,
436 ff_contexts fc2,
437 ff_archive_items target,
438 ff_database_items fdi
439 WHERE target.context1 = to_char(cp_payroll_action_id)
440 /* context of payroll_action_id */
441 AND fdi.user_name = 'A_FIPS_CODE_JD'
442 AND target.user_entity_id = fdi.user_entity_id
443 AND fc2.context_name = 'TAX_UNIT_ID'
444 AND con2.archive_item_id = target.archive_item_id
445 AND con2.context_id = fc2.context_id
446 AND ltrim(rtrim(con2.context)) = to_char(cp_tax_unit_id)
447 AND fc3.context_name = 'JURISDICTION_CODE'
448 AND con3.archive_item_id = target.archive_item_id
449 AND con3.context_id = fc3.context_id
450 AND substr(ltrim(rtrim(con3.context)),1,2) = p_state_code;
451 /* jurisdiction code of the state */
452
453 /* cursor to get if transmitter has been been archived */
454
455 CURSOR c_transmitter IS
456 SELECT SUBSTR(legislative_parameters,INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
457 + LENGTH('TRANSFER_TRANS_LEGAL_CO_ID='),
458 (INSTR(legislative_parameters, 'TRANSFER_DATE=')
459 - INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
460 - LENGTH('TRANSFER_TRANS_LEGAL_CO_ID=')-1 ))
461 FROM pay_payroll_actions
462 WHERE report_type = 'W2'
463 AND effective_date = p_year_end
464 AND report_qualifier = p_state_abbrev
465 AND business_group_id = p_business_group_id
466 AND report_category IN ('RG', 'RM', 'MT') ;
467
468
469 /* LoCal variables used for processing */
470 message_text VARCHAR2(32000);
471 message_preprocess varchar2(2000);
472 l_gre NUMBER(15);
473 l_person NUMBER(15);
474 l_assignment NUMBER(15);
475 l_asg_effective_dt DATE;
476 l_payroll_action_id NUMBER(15);
477 l_asg_errored VARCHAR2(1);
478 l_asg_retry_pend VARCHAR2(1);
479 l_balance_exists NUMBER(1) := 0;
480 l_no_of_gres_picked NUMBER(15) := 0;
481 l_transmitter NUMBER(15) :=0;
482 l_state_tax_rules_exist CHAR(1);
483 l_person_in_state CHAR(1);
484 l_user_entity_id number;
485 l_context_jursidiction number;
486 l_context_tax_unit_id number; --ff_contexts.context_id%type;
487 l_package_error_status char(1) := 'N';
488 l_ein number;
489 l_ein_result varchar2(30);
490 BEGIN
491
492 /* One Time Setting of Pre-Process Message */
493 message_preprocess := 'Pre-Process check';
494
495 /* GET the Employer EIN */
496 OPEN c_tax_ein;
497 FETCH c_tax_ein INTO l_ein;
498 IF c_tax_ein%NOTFOUND THEN
499 CLOSE c_tax_ein;
500 l_package_error_status := 'Y';
501 hr_utility.trace('A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER missing ');
502 message_text := 'EIN ID missing ';
503 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
504 pay_core_utils.push_token('record_name',message_preprocess);
505 pay_core_utils.push_token('description',message_text);
506 hr_utility.raise_error;
507 ELSE
508 CLOSE c_tax_ein;
509 END IF;
510
511
512 /* GET the context and user entity id */
513 OPEN c_user_entity_id_of_bal;
514 FETCH c_user_entity_id_of_bal INTO l_user_entity_id;
515 IF c_user_entity_id_of_bal%NOTFOUND THEN
516 CLOSE c_user_entity_id_of_bal;
517 l_package_error_status := 'Y';
518 /* message to user - Database item missing */
519 hr_utility.trace('Database item for balacne missing ');
520 message_text := '-Database item missing ';
521 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
522 pay_core_utils.push_token('record_name',message_preprocess);
523 pay_core_utils.push_token('description',message_text);
524 hr_utility.raise_error;
525 ELSE
526 CLOSE c_user_entity_id_of_bal;
527 END IF;
528
529 OPEN c_context_tax_unit_id;
530 FETCH c_context_tax_unit_id INTO l_context_tax_unit_id;
531 IF c_context_tax_unit_id%NOTFOUND THEN
532 CLOSE c_context_tax_unit_id;
533 /* message to user -- unable to find the context_id for tax_unit_id */
534 message_text := 'Context_id value for tax_unit_id missing';
535 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
536 pay_core_utils.push_token('record_name',message_preprocess);
537 pay_core_utils.push_token('description',message_text);
538 hr_utility.raise_error;
539 ELSE
540 CLOSE c_context_tax_unit_id;
541 END IF;
542
543 OPEN c_context_jurisdiction;
544 FETCH c_context_jurisdiction INTO l_context_jursidiction;
545 IF c_context_jurisdiction%NOTFOUND THEN
546 CLOSE c_context_jurisdiction;
547 /* message to User -- Unable to find to context_id for jurisdiction */
548
549 message_text := 'Context_id value for jurisdiction missing';
550 hr_utility.trace('Contxt_id value for jurisdction_id missing');
551 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
552 pay_core_utils.push_token('record_name',message_preprocess);
553 pay_core_utils.push_token('description',message_text);
554 hr_utility.raise_error;
555 ELSE
556 CLOSE c_context_jurisdiction;
557 eND IF;
558 /* Get the Tranmitter id of the Current Mag. W2. and check if it has
559 archived or Not for the year End process
560 Get the transmitter for the Mag. W2. Process. */
561 OPEN c_transmitter;
562 FETCH c_transmitter INTO l_transmitter;
563 IF c_transmitter%NOTFOUND THEN
564 CLOSE c_transmitter;
565 /* message to user -- transmitter has not been defined for the gre */
566 message_text := 'Transmitter Not denfined';
567 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
568 pay_core_utils.push_token('record_name',message_preprocess);
569 pay_core_utils.push_token('description',message_text);
570 hr_utility.raise_error;
571 ELSE
572 CLOSE c_transmitter;
573 END IF;
574
575 hr_utility.trace('Transmetter Setting is ' || to_char(l_transmitter));
576 hr_utility.trace('Start date ' || to_char(p_year_start));
577 hr_utility.trace('End date ' || to_char(p_year_end));
578 hr_utility.trace('Bussiness_group id ' || to_char(p_business_group_id));
579
580
581 /* Check if Archiver has been run for Transmitter */
582 OPEN c_gre_payroll_action (l_transmitter);
583 FETCH c_gre_payroll_action INTO l_payroll_action_id;
584
585 IF c_gre_payroll_action%NOTFOUND THEN
586 hr_utility.trace('Transmitter not Archvied ');
587 CLOSE c_gre_payroll_action;
588 /* message to user -- Transmitter has not been archived */
589 message_text := 'Transmitter not Archived';
590 hr_utility.trace('Transmitter has not been archived');
591 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
592 pay_core_utils.push_token('record_name',message_preprocess);
593 pay_core_utils.push_token('description',message_text);
594 -- hr_utility.raise_error;
595 END IF;
596 CLOSE c_gre_payroll_action;
597
598 /* end of Transmitter Checking */
599
600 hr_utility.set_location('pay_us_mmref_reporting.preprocess_check', 10);
601
602 FOR gre_rec IN c_get_gre LOOP
603 /* set l_gre to gre Fethched */
604
605 l_gre := gre_rec.gre;
606
607 /* Get the payroll_action_id of the archvier for given GRE */
608
609 OPEN c_gre_payroll_action (l_gre);
610 FETCH c_gre_payroll_action INTO l_payroll_action_id;
611
612 /* Check for the Gre That have been Archived */
613
614 IF c_gre_payroll_action%FOUND THEN
615
616 /* Check if any of the payroll_action_id has errored out or Not */
617
618 OPEN c_arch_errored_asg (l_payroll_action_id);
619 FETCH c_arch_errored_asg
620 INTO l_asg_errored;
621
622 IF c_arch_errored_asg%FOUND THEN
623 message_text := 'Assignment in Error Conditon for GRE:= ' || to_char(l_gre);
624 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
625 pay_core_utils.push_token('record_name',message_preprocess);
626 pay_core_utils.push_token('description',message_text);
627 --Some of the assignments have Errored
628 l_package_error_status := 'Y';
629 /* message to user -- assignment has errored out */
630 --
631 -- hr_utility.raise_error;
632 END IF;
633 CLOSE c_arch_errored_asg;
634
635 /* Checking for Retry */
636
637 OPEN c_arch_retry_pending (l_payroll_action_id);
638 FETCH c_arch_retry_pending INTO l_asg_retry_pend;
639 IF c_arch_retry_pending%FOUND THEN
640 --Some of the assignments have been marked for retry
641 message_text := 'Assignment Marked for Retry: GRE_ID := ' || to_char(l_gre);
642 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
643 pay_core_utils.push_token('record_name',message_preprocess);
644 pay_core_utils.push_token('description',message_text);
645 l_package_error_status := 'Y';
646 -- hr_utility.raise_error;
647
648 END IF;
649
650 CLOSE c_arch_retry_pending;
651
652 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
653 hr_utility.trace('Payroll_action_id - '|| to_char(l_payroll_action_id));
654 hr_utility.trace('No. of GREs picked so far - '|| to_char(l_no_of_gres_picked));
655
656 l_no_of_gres_picked := l_no_of_gres_picked + 1;
657 /* All the condition have been met so it is safe to make Arhive
658 Call
659 */
660
661 IF p_report_type = 'FEDW2' THEN --federal W2
662 archive_eoy_data(l_payroll_action_id,l_gre);
663 ELSE
664 hr_utility.trace('Federal smart archive call');
665 archive_eoy_data(l_payroll_action_id,l_gre);
666 hr_utility.trace('State Code :- ' || p_state_code);
667 hr_utility.trace('GRE - ' || to_char(l_gre));
668 hr_utility.trace('Before calling Smart State Archive');
669 archive_state_eoy_data(l_payroll_action_id,l_gre,p_state_code);
670 hr_utility.trace('After call to state Archive');
671 END IF;
672
673 hr_utility.trace('After Call to smart Archive ');
674
675 /* Check EIN for the Employee */
676
677 IF p_report_type = 'FEDW2' THEN --federal W2
678 /* Check for Federal Data */
679 l_ein_result := check_er_data(l_payroll_action_id,l_gre);
680
681 IF l_ein_result = 'N'THEN
682 message_text := 'EIN for GRE:= ' || to_char(l_gre) || 'Not Set';
683 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
684 pay_core_utils.push_token('record_name',message_preprocess);
685 pay_core_utils.push_token('description',message_text);
686 l_package_error_status := 'Y';
687 END IF;
688 ELSE
689 /* If Report type is not Fed so state */
690 /* Check for State */
691 -- OPEN c_chk_archive_state_code (l_gre,l_payroll_action_id);
692 -- FETCH c_chk_archive_state_code INTO l_state_tax_rules_exist;
693 -- IF c_chk_archive_state_code%NOTFOUND THEN
694 -- hr_utility.trace('State Tax Rules not Found ');
695 -- /* state Tax Rules have not been Defined */
696 -- message_text := 'State Tax Rules not Defind for GRE '
697 -- || to_char(l_gre) || ' for ' || P_state_abbrev;
698 -- pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
699 -- pay_core_utils.push_token('record_name',message_preprocess);
700 -- pay_core_utils.push_token('description',message_text);
701 -- l_package_error_status := 'Y';
702 -- END IF; /* Missing State Tax Rules */
703 --
704 -- close c_chk_archive_state_code;
705
706 /* Do check_er_data only if Record exist */
707 /* Add those checks for current Year */
708 hr_utility.trace('Check the State ER data ');
709 -- l_ein_result := check_state_er_data(l_payroll_action_id,l_gre,p_state_code);
710 -- hr_utility.trace('return value for check_state_er_data ' || l_ein_result );
711 /* EIN check Failed */
712 -- IF l_ein_result = 'N'THEN
713 -- hr_utility.trace('ID missing in State Tax Rules' || to_char(l_gre));
714 -- message_text := 'Missing ID in State Tax Rules for GRE:= '|| to_char(l_gre);
715 -- pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
716 -- pay_core_utils.push_token('record_name',message_preprocess);
717 -- pay_core_utils.push_token('description',message_text);
718 -- l_package_error_status := 'Y';
719 -- END IF;
720 END IF;
721
722 ELSE
723 /* The GRE has not been archived so check for valid Persons in the GRE
724 who have been paid for the run YEAR
725 Open Cursor as per your Report type to check if GRE needs to be archived
726 or Not */
727 /* Federal/State W2 Mag tapes should issue warning if GRE is not archived
728 Bug # 3069840
729 This is No loger an Pre-precess check Error. Pre-process check shuld log a
730 Warning an proceed to genrate W2 Mag Tape
731 */
732
733
734 /* Code commented to fix Bug # 3069840
735
736 IF p_report_type = 'FEDW2' THEN --federal W2
737 hr_utility.set_location('pay_us_mmref_reporting.preprocess_check', 99);
738 OPEN c_gre_fed(gre_rec.gre);
739 ELSIF p_report_type = 'STW2' THEN --state W2
740 OPEN c_gre_state(gre_rec.gre);
741 END IF;
742
743 -- For GRE Find_out if any person has balance greater then Zero
744
745 LOOP --Main Loop
746 IF p_report_type = 'FEDW2' THEN
747 -- Start feching Persons for GRE
748 FETCH c_gre_fed INTO l_person
749 ,l_assignment
750 ,l_asg_effective_dt;
751 hr_utility.set_location('pay_us_mmref_reporting.preprocess_check',20);
752 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
753 hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
754 hr_utility.trace('Person ID:' || TO_CHAR(l_person));
755 hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
756 IF c_gre_fed%NOTFOUND THEN
757 -- get out of the Main Loop if You have reached No person Found
758 EXIT;
759 END IF;
760 END IF; -- report type = 'FEDW2'
761
762 IF p_report_type = 'STW2' THEN
763 FETCH c_gre_state INTO l_person
764 ,l_assignment
765 ,l_asg_effective_dt;
766
767 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 40);
768 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
769 hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
770 hr_utility.trace('Person ID:' || TO_CHAR(l_person));
771 hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
772 -- No Person Was found for the State So Exit
773 IF c_gre_state%NOTFOUND THEN
774 EXIT;
775 END IF;
776 END IF; -- report type = 'STW2' and etc
777
778
779 hr_utility.trace('pay_us_mmref_reporting.preprocess_check');
780 hr_utility.trace('GRE - '||to_char(l_gre));
781
782 -- get the balance for person
783
784 IF p_report_type = 'FEDW2' THEN
785 IF get_balance_value('GROSS_EARNINGS_PER_GRE_YTD',
786 l_gre, p_state_abbrev, l_assignment,
787 LEAST(p_year_end, l_asg_effective_dt)) > 0 THEN
788 l_balance_exists := 1;
789 END IF;
790 END IF; -- End of report_type 'FEDW2'
791
792
793 IF p_report_type = 'STW2' THEN
794 IF get_balance_value('GROSS_EARNINGS_PER_GRE_YTD',
795 l_gre, p_state_abbrev, l_assignment,
796 LEAST(p_year_end, l_asg_effective_dt)) > 0 AND
797 get_balance_value('SIT_GROSS_PER_JD_GRE_YTD',
798 l_gre, p_state_abbrev, l_assignment,
799 LEAST(p_year_end, l_asg_effective_dt)) > 0 THEN
800 l_balance_exists := 1;
801 END IF; -- balance Greater then Zero Exist
802 END IF;
803
804
805 IF l_balance_exists = 1 then
806 --It means that no archived GRE was
807 --found for the GRE. This is an error.
808 IF p_report_type = 'FEDW2' THEN
809
810 close c_gre_fed;
811 ELSE
812 close c_gre_state;
813 END IF; -- End Of Report_type 'FEDW2'
814 hr_utility.trace('Archive_Gre ' || to_char(l_gre));
815 -- Person Found with Balance for given GRE
816 message_text := 'GRE_ID := ' || to_char(l_gre)
817 || ' has People with Balnace > 0';
818 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
819 pay_core_utils.push_token('record_name',message_preprocess);
820 pay_core_utils.push_token('description',message_text);
821
822 message_text := 'Please Archive GRE With ID := ' || to_char(l_gre) ;
823 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
824 pay_core_utils.push_token('record_name',message_preprocess);
825 l_package_error_status := 'Y';
826 END IF;
827 l_balance_exists := 0;
828
829 END LOOP; --Main Loop
830 -- You have checked that We dont have any person with
831 -- balance greater then Zero
832 --
833 IF p_report_type = 'FEDW2' THEN
834 close c_gre_fed;
835 ELSE
836 close c_gre_state;
837 END if;
838 -- End of Comment for Bug # 3069840
839 */
840
841 /* A warning is logged if GRE is not archived Buug # 3069840 */
842 l_package_error_status := 'N';
843 message_text := 'Please Archive GRE With ID := ' || to_char(l_gre) ;
844 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
845 pay_core_utils.push_token('record_name',message_preprocess);
846 pay_core_utils.push_token('description',message_text);
847
848 END IF; --end if for checking of person balance if the GRE has
849 --not been archived.
850
851 CLOSE c_gre_payroll_action;
852
853 END LOOP; /* end of for statement */
854
855 IF l_package_error_status = 'Y' THEN
856 hr_utility.trace('Error Condition Found');
857 message_text := 'Error Condition detected ' ;
858 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
859 pay_core_utils.push_token('record_name',message_preprocess);
860 pay_core_utils.push_token('description',message_text);
861
862 message_text := 'Pay Message line and log have more Details' ;
863 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
864 pay_core_utils.push_token('record_name',message_preprocess);
865 pay_core_utils.push_token('description',message_text);
866 hr_utility.raise_error;
867 END IF;
868
869 IF l_no_of_gres_picked = 0 THEN
870 --It means that no archived GRE was
871 --found for the Organization. This is an error.
872
873 message_text := 'No GRE was picked for Magnetic Tape';
874 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
875 pay_core_utils.push_token('record_name',message_preprocess);
876 pay_core_utils.push_token('description',message_text);
877 hr_utility.raise_error;
878 END IF;
879
880 RETURN TRUE;
881 hr_utility.trace('Succesful - Return True ');
882 exception
883 when others then
884 -- add message for this
885 message_text := message_text || '+ Exception';
886 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
887 hr_utility.set_message_token('MESSAGE', message_text);
888 hr_utility.raise_error;
889
890 END preprocess_check;
891 --
892 ----------------------------------------------------------------------------
893 --Name
894 -- range_cursor
895 --Purpose
896 -- This procedure calls a function to check if the pre-processor has been
897 -- run for all the GREs and assignments. It then defines a SQL statement
898 -- to fetch all the people to be included in the report. This SQL statement
899 -- is used to define the 'chunks' for multi-threaded operation
900 --Arguments
901 -- p_pactid payroll action id for the report
902 -- p_sqlstr the SQL statement to fetch the people
903 ------------------------------------------------------------------------------
904 PROCEDURE range_cursor (
905 p_pactid IN NUMBER,
906 p_sqlstr OUT nocopy VARCHAR2
907 )
908 IS
909 --Changes for bug 9467090
910 CURSOR csr_action_parameter is
911 select parameter_value
912 from pay_action_parameters
913 where parameter_name = 'RANGE_PERSON_ID';
914 --Changes for bug 9467090
915 p_year_start DATE;
916 p_year_end DATE;
917 p_business_group_id NUMBER;
918 p_state_abbrev VARCHAR2(3);
919 p_state_code VARCHAR2(2);
920 p_report_type VARCHAR2(30);
921 BEGIN
922
923 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor', 10);
924 get_report_parameters(
925 p_pactid,
926 p_year_start,
927 p_year_end,
928 p_state_abbrev,
929 p_state_code,
930 p_report_type,
931 p_business_group_id
932 );
933 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor', 20);
934 IF preprocess_check(p_pactid,
935 p_year_start,
936 p_year_end,
937 p_business_group_id,
938 p_state_abbrev,
939 p_state_code,
940 p_report_type
941 ) THEN
942
943 open csr_action_parameter;
944 fetch csr_action_parameter into g_action_param_val;
945 close csr_action_parameter;
946 IF g_action_param_val = 'Y' or p_report_type = 'FEDW2' THEN
947 p_sqlstr := '
948 SELECT DISTINCT paf.person_id
949 FROM per_all_assignments_f paf,
950 pay_assignment_actions paa,
951 pay_payroll_actions ppa,
952 pay_payroll_actions ppa1
953 WHERE ppa1.payroll_action_id = :payroll_action_id
954 AND ppa.report_type = ''YREND''
955 AND ppa.business_group_id+0 = ppa1.business_group_id
956 AND ppa.effective_date = ppa1.effective_date
957 AND ppa.start_date = ppa1.start_date
958 AND paa.payroll_action_id = ppa.payroll_action_id
959 AND paa.action_status = ''C''
960 AND paf.assignment_id = paa.assignment_id
961 AND paf.effective_start_date <= ppa.effective_date
962 AND paf.effective_end_date >= ppa.start_date
963 AND paf.assignment_type = ''E''
964 AND not exists (
965 SELECT ''x''
966 FROM hr_organization_information hoi
967 WHERE hoi.organization_id = paa.tax_unit_id
968 and hoi.org_information_context =
969 ''1099R Magnetic Report Rules'')
970 ORDER BY paf.person_id
971 ';
972 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor',
973 30);
974 ELSIF p_report_type = 'STW2' and p_state_abbrev = 'IN' THEN
975 p_sqlstr := 'SELECT DISTINCT '||
976 'to_number(paa.serial_number) '||
977 'FROM ff_archive_item_contexts faic, '||
978 'ff_archive_items fai, '||
979 'ff_database_items fdi, '||
980 'pay_assignment_actions paa, '||
981 'pay_payroll_actions ppa, '||
982 'per_all_assignments_f paf, '||
983 'pay_payroll_actions ppa1 '||
984 'WHERE ppa1.payroll_action_id = :payroll_action_id '||
985 'AND ppa.business_group_id+0 = ppa1.business_group_id '||
986 'AND ppa1.effective_date = ppa.effective_date '||
987 'AND ppa.report_type = ''YREND'' '||
988 'AND ppa.payroll_action_id = paa.payroll_action_id '||
989 'AND paf.assignment_id = paa.assignment_id '||
990 'AND paf.assignment_type = ''E'' '||
991 'AND fdi.user_name = ''A_STATE_ABBREV'' '||
992 'AND fdi.user_entity_id = fai.user_entity_id '||
993 'AND fai.archive_item_id = faic.archive_item_id '||
994 'AND fai.context1 = paa.assignment_action_id '||
995 'AND fai.value = ppa1.report_qualifier '||
996 'AND paf.effective_start_date <= ppa.effective_date '||
997 'AND paf.effective_end_date >= ppa.start_date '||
998 'AND paa.action_status = ''C'' '||
999 'AND ( '||
1000 'nvl(hr_us_w2_rep.get_w2_arch_bal( '||
1001 'paa.assignment_action_id, '||
1002 '''A_W2_STATE_WAGES'', '||
1003 'paa.tax_unit_id, '||
1004 'faic.context , 2),0) > 0 '||
1005 'OR '||
1006 'exists (select ''x'' '||
1007 'from ff_contexts fc1, '||
1008 'ff_archive_items fai1, '||
1009 'ff_archive_item_contexts faic1, '||
1010 'ff_database_items fdi1 '||
1011 'where fc1.context_name = ''JURISDICTION_CODE'' '||
1012 'and fc1.context_id = faic1.context_id '||
1013 'and fdi1.user_name = ''A_COUNTY_WITHHELD_PER_JD_GRE_YTD'' '||
1014 'and fdi1.user_entity_id = fai1.user_entity_id '||
1015 'and fai1.context1 = paa.assignment_action_id '||
1016 'and fai1.archive_item_id = faic1.archive_item_id '||
1017 'and substr(faic1.context,1,2) = substr(faic.context,1,2) '||
1018 'and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, '||
1019 '''A_COUNTY_WITHHELD_PER_JD_GRE_YTD'', '||
1020 'paa.tax_unit_id, '||
1021 'faic1.context , 6),0) > 0) '||
1022 ') '||
1023
1024 'AND EXISTS ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */ '||
1025 'select ''x'' '||
1026 'from pay_us_state_tax_info_f pustif '||
1027 'where substr(faic.context,1,2) = pustif.state_code '||
1028 'and ppa.effective_date between pustif.effective_start_date '||
1029 'and pustif.effective_end_date '||
1030 'and pustif.sit_exists = ''Y'') '||
1031
1032 'AND not exists ( '||
1033 'SELECT ''x'' '||
1034 'FROM hr_organization_information hoi '||
1035 'WHERE hoi.organization_id = paa.tax_unit_id '||
1036 'and hoi.org_information_context = '||
1037 '''1099R Magnetic Report Rules'' '||
1038 ') '||
1039 'order by to_number(paa.serial_number)';
1040 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor',
1041 40);
1042 ELSIF p_report_type = 'STW2' and p_state_abbrev = 'OH' THEN
1043 p_sqlstr := 'SELECT DISTINCT '||
1044 'to_number(paa.serial_number) '||
1045 'FROM ff_archive_item_contexts faic, '||
1046 'ff_archive_items fai, '||
1047 'ff_database_items fdi, '||
1048 'pay_assignment_actions paa, '||
1049 'pay_payroll_actions ppa, '||
1050 'per_all_assignments_f paf, '||
1051 'pay_payroll_actions ppa1 '||
1052 'WHERE ppa1.payroll_action_id = :payroll_action_id '||
1053 'AND ppa.business_group_id+0 = ppa1.business_group_id '||
1054 'AND ppa1.effective_date = ppa.effective_date '||
1055 'AND ppa.report_type = ''YREND'' '||
1056 'AND ppa.payroll_action_id = paa.payroll_action_id '||
1057 'AND paf.assignment_id = paa.assignment_id '||
1058 'AND paf.assignment_type = ''E'' '||
1059 'AND fdi.user_name = ''A_STATE_ABBREV'' '||
1060 'AND fdi.user_entity_id = fai.user_entity_id '||
1061 'AND fai.archive_item_id = faic.archive_item_id '||
1062 'AND fai.context1 = paa.assignment_action_id '||
1063 'AND fai.value = ppa1.report_qualifier '||
1064 'AND paf.effective_start_date <= ppa.effective_date '||
1065 'AND paf.effective_end_date >= ppa.start_date '||
1066 'AND paa.action_status = ''C'' '||
1067 'AND ( '||
1068 'nvl(hr_us_w2_rep.get_w2_arch_bal( '||
1069 'paa.assignment_action_id, '||
1070 '''A_W2_STATE_WAGES'', '||
1071 'paa.tax_unit_id, '||
1072 'faic.context , 2),0) > 0 '||
1073 'OR '||
1074 'exists (select ''x'' '||
1075 'from ff_contexts fc1, '||
1076 'ff_archive_items fai1, '||
1077 'ff_archive_item_contexts faic1, '||
1078 'ff_database_items fdi1 '||
1079 'where fc1.context_name = ''JURISDICTION_CODE'' '||
1080 'and fc1.context_id = faic1.context_id '||
1081 'and fdi1.user_name = ''A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'' '||
1082 'and fdi1.user_entity_id = fai1.user_entity_id '||
1083 'and fai1.context1 = paa.assignment_action_id '||
1084 'and fai1.archive_item_id = faic1.archive_item_id '||
1085 'and substr(faic1.context,1,2) = substr(faic.context,1,2) '||
1086 'and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, '||
1087 '''A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'', '||
1088 'paa.tax_unit_id, '||
1089 'faic1.context , 8),0) > 0) '||
1090 ') '||
1091
1092 'AND EXISTS ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */ '||
1093 'select ''x'' '||
1094 'from pay_us_state_tax_info_f pustif '||
1095 'where substr(faic.context,1,2) = pustif.state_code '||
1096 'and ppa.effective_date between pustif.effective_start_date '||
1097 'and pustif.effective_end_date '||
1098 'and pustif.sit_exists = ''Y'') '||
1099
1100 'AND not exists ( '||
1101 'SELECT ''x'' '||
1102 'FROM hr_organization_information hoi '||
1103 'WHERE hoi.organization_id = paa.tax_unit_id '||
1104 'and hoi.org_information_context = '||
1105 '''1099R Magnetic Report Rules'' '||
1106 ') '||
1107 'order by to_number(paa.serial_number)';
1108 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor',
1109 40);
1110
1111 ELSIF p_report_type = 'STW2' THEN
1112 p_sqlstr := '
1113 SELECT DISTINCT
1114 to_number(paa.serial_number)
1115 FROM ff_archive_item_contexts faic,
1116 ff_archive_items fai,
1117 ff_database_items fdi,
1118 pay_assignment_actions paa,
1119 pay_payroll_actions ppa,
1120 per_all_assignments_f paf,
1121 pay_payroll_actions ppa1
1122 WHERE
1123 ppa1.payroll_action_id = :payroll_action_id
1124 AND ppa.business_group_id+0 = ppa1.business_group_id
1125 AND ppa1.effective_date = ppa.effective_date
1126 AND ppa.report_type = ''YREND''
1127 AND ppa.payroll_action_id = paa.payroll_action_id
1128 and paf.assignment_id = paa.assignment_id
1129 AND paf.assignment_type = ''E''
1130 AND fdi.user_name = ''A_STATE_ABBREV''
1131 AND fdi.user_entity_id = fai.user_entity_id
1132 AND fai.archive_item_id = faic.archive_item_id
1133 AND fai.context1 = paa.assignment_action_id
1134 AND fai.value = ppa1.report_qualifier
1135 AND paf.effective_start_date <= ppa.effective_date
1136 AND paf.effective_end_date >= ppa.start_date
1137 AND paa.action_status = ''C''
1138 AND nvl(hr_us_w2_rep.get_w2_arch_bal(
1139 paa.assignment_action_id,
1140 ''A_W2_STATE_WAGES'',
1141 paa.tax_unit_id,
1142 faic.context , 2),0) > 0
1143 AND EXISTS ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
1144 select ''x''
1145 from pay_us_state_tax_info_f pustif
1146 where substr(faic.context,1,2) = pustif.state_code
1147 and ppa.effective_date between pustif.effective_start_date
1148 and pustif.effective_end_date
1149 and pustif.sit_exists = ''Y'')
1150 AND not exists (
1151 SELECT ''x''
1152 FROM hr_organization_information hoi
1153 WHERE hoi.organization_id = paa.tax_unit_id
1154 and hoi.org_information_context =
1155 ''1099R Magnetic Report Rules''
1156 )
1157 order by to_number(paa.serial_number)';
1158 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor',
1159 40);
1160 END IF;
1161 END IF;
1162 END range_cursor;
1163 --
1164 -----------------------------------------------------------------------------
1165 --Name
1166 -- create_assignment_act
1167 --Purpose
1168 -- Creates assignment actions for the payroll action associated with the
1169 -- report
1170 --Arguments
1171 -- p_pactid payroll action for the report
1172 -- p_stperson starting person id for the chunk
1173 -- p_endperson last person id for the chunk
1174 -- p_chunk size of the chunk
1175 --Note
1176 -- The procedure processes assignments in 'chunks' to facilitate
1177 -- multi-threaded operation. The chunk is defined by the size and the
1178 -- starting and ending person id. An interlock is also created against the
1179 -- pre-processor assignment action to prevent rolling back of the archiver.
1180 ----------------------------------------------------------------------------
1181 --
1182 PROCEDURE create_assignment_act(
1183 p_pactid IN NUMBER,
1184 p_stperson IN NUMBER,
1185 p_endperson IN NUMBER,
1186 p_chunk IN NUMBER )
1187 IS
1188 -- This Cursor is Speific to PuertoRico State
1189 -- Cursor to get the assignments for state W2. Gets only those employees
1190 -- which have wages for the specified state.This cursor excludes the
1191 -- 1099R GREs.
1192 CURSOR c_pr_state IS
1193 SELECT
1194 to_number(paa.serial_number),
1195 paf.assignment_id,
1196 paa.tax_unit_id,
1197 paf.effective_end_date,
1198 paa.assignment_action_id,
1199 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1200 'A_W2_STATE_WAGES',
1201 paa.tax_unit_id,
1202 faic.context , 2),0)
1203 FROM ff_archive_item_contexts faic,
1204 ff_archive_items fai,
1205 ff_database_items fdi,
1206 pay_assignment_actions paa,
1207 pay_payroll_actions ppa,
1208 per_all_assignments_f paf,
1209 pay_payroll_actions ppa1
1210 WHERE
1214 and ppa.report_type = 'YREND'
1211 ppa1.payroll_action_id = p_pactid
1212 and ppa.business_group_id+0 = ppa1.business_group_id
1213 and ppa1.effective_date = ppa.effective_date
1215 and ppa.payroll_action_id = paa.payroll_action_id
1216 and paf.assignment_id = paa.assignment_id
1217 and paf.assignment_type = 'E'
1218 and fdi.user_name = 'A_STATE_ABBREV'
1219 and fdi.user_entity_id = fai.user_entity_id
1220 and fai.archive_item_id = faic.archive_item_id
1221 and fai.context1 = paa.assignment_action_id
1222 and fai.value = ppa1.report_qualifier
1223 and paf.effective_start_date <= ppa.effective_date
1224 and paf.effective_end_date >= ppa.start_date
1225 and paa.action_status = 'C'
1226 --and paa.serial_number BETWEEN to_char(p_stperson) AND to_char(p_endperson)
1227 and to_number(paa.serial_number) BETWEEN p_stperson AND p_endperson /* 6712859 */
1228 and paf.person_id BETWEEN p_stperson AND p_endperson
1229 and ( ( nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1230 'A_W2_STATE_WAGES',
1231 paa.tax_unit_id,
1232 faic.context , 2),0) > 0 )
1233 or
1234 ( nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1235 'A_SIT_WITHHELD_PER_JD_GRE_YTD', /* 6809739 */
1236 paa.tax_unit_id,
1237 faic.context , 2),0) > 0) )
1238
1239 and exists ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
1240 select 'x'
1241 from pay_us_state_tax_info_f pustif
1242 where substr(faic.context,1,2) = pustif.state_code
1243 and ppa.effective_date between pustif.effective_start_date
1244 and pustif.effective_end_date
1245 and pustif.sit_exists = 'Y'
1246 )
1247 and exists (select 'x'
1248 from hr_organization_information hou
1249 where hou.organization_id = paa.tax_unit_id
1250 and hou.org_information16 = 'P'
1251 and hou.org_information_context = 'W2 Reporting Rules')
1252 and not exists
1253 (
1254 select 'x'
1255 from hr_organization_information hoi
1256 where hoi.organization_id = paa.tax_unit_id
1257 and hoi.org_information_context ='1099R Magnetic Report Rules'
1258 )
1259 ORDER BY 1, 3, 4 DESC, 2
1260 FOR UPDATE OF paf.assignment_id;
1261
1262 CURSOR c_pr_state_range IS
1263 SELECT /*+ index(ppa pay_payroll_actions_N52) */
1264 to_number(paa.serial_number),
1265 paf.assignment_id,
1266 paa.tax_unit_id,
1267 paf.effective_end_date,
1268 paa.assignment_action_id,
1269 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1270 'A_W2_STATE_WAGES',
1271 paa.tax_unit_id,
1272 faic.context , 2),0)
1273 FROM ff_archive_item_contexts faic,
1274 ff_archive_items fai,
1275 ff_database_items fdi,
1276 pay_assignment_actions paa,
1277 pay_payroll_actions ppa,
1278 per_all_assignments_f paf,
1279 pay_payroll_actions ppa1,
1280 pay_population_ranges ppr
1281 WHERE
1282 ppa1.payroll_action_id = p_pactid
1283 and ppa1.payroll_action_id=ppr.payroll_action_id
1284 AND ppr.chunk_number = p_chunk
1285 and ppa.business_group_id+0 = ppa1.business_group_id
1286 and ppa1.effective_date = ppa.effective_date
1287 and ppa.report_type = 'YREND'
1288 and ppa.payroll_action_id = paa.payroll_action_id
1289 and paf.assignment_id = paa.assignment_id
1290 and paf.assignment_type = 'E'
1291 and fdi.user_name = 'A_STATE_ABBREV'
1292 and fdi.user_entity_id = fai.user_entity_id
1293 and fai.archive_item_id = faic.archive_item_id
1294 and fai.context1 = paa.assignment_action_id
1295 and fai.value = ppa1.report_qualifier
1296 and paf.effective_start_date <= ppa.effective_date
1297 and paf.effective_end_date >= ppa.start_date
1298 and paa.action_status = 'C'
1299 --and paa.serial_number BETWEEN to_char(p_stperson) AND to_char(p_endperson)
1300 -- and to_number(paa.serial_number) BETWEEN p_stperson AND p_endperson /* 6712859 */
1301 AND to_number(paa.serial_number) =ppr.person_id
1302 and paf.person_id BETWEEN p_stperson AND p_endperson
1303 and ( ( nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1304 'A_W2_STATE_WAGES',
1305 paa.tax_unit_id,
1306 faic.context , 2),0) > 0 )
1307 or
1308 ( nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1309 'A_SIT_WITHHELD_PER_JD_GRE_YTD', /* 6809739 */
1310 paa.tax_unit_id,
1311 faic.context , 2),0) > 0) )
1312
1313 and exists ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
1314 select 'x'
1315 from pay_us_state_tax_info_f pustif
1316 where substr(faic.context,1,2) = pustif.state_code
1317 and ppa.effective_date between pustif.effective_start_date
1318 and pustif.effective_end_date
1319 and pustif.sit_exists = 'Y'
1320 )
1321 and exists (select 'x'
1322 from hr_organization_information hou
1323 where hou.organization_id = paa.tax_unit_id
1324 and hou.org_information16 = 'P'
1325 and hou.org_information_context = 'W2 Reporting Rules')
1326 and not exists
1327 (
1328 select 'x'
1329 from hr_organization_information hoi
1330 where hoi.organization_id = paa.tax_unit_id
1331 and hoi.org_information_context ='1099R Magnetic Report Rules'
1332 )
1333 ORDER BY 1, 3, 4 DESC, 2
1334 FOR UPDATE OF paf.assignment_id;
1335
1336 -- Cursor to get the assignments for state W2. Gets only those employees
1337 -- which have wages for the specified state.This cursor excludes the
1338 -- 1099R GREs.
1339 CURSOR c_state IS
1340 SELECT
1341 to_number(paa.serial_number),
1342 paf.assignment_id,
1343 paa.tax_unit_id,
1344 paf.effective_end_date,
1345 paa.assignment_action_id,
1346 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1347 'A_W2_STATE_WAGES',
1348 paa.tax_unit_id,
1349 faic.context , 2),0)
1350 FROM ff_archive_item_contexts faic,
1351 ff_archive_items fai,
1352 ff_database_items fdi,
1353 pay_assignment_actions paa,
1354 pay_payroll_actions ppa,
1355 per_all_assignments_f paf,
1356 pay_payroll_actions ppa1
1357 WHERE
1358 ppa1.payroll_action_id = p_pactid
1359 and ppa.business_group_id+0 = ppa1.business_group_id
1360 and ppa1.effective_date = ppa.effective_date
1361 and ppa.report_type = 'YREND'
1362 and ppa.payroll_action_id = paa.payroll_action_id
1363 and paf.assignment_id = paa.assignment_id
1364 and paf.assignment_type = 'E'
1365 and fdi.user_name = 'A_STATE_ABBREV'
1366 and fdi.user_entity_id = fai.user_entity_id
1367 and fai.archive_item_id = faic.archive_item_id
1368 and fai.context1 = paa.assignment_action_id
1369 and fai.value = ppa1.report_qualifier
1370 and paf.effective_start_date <= ppa.effective_date
1371 and paf.effective_end_date >= ppa.start_date
1372 and paa.action_status = 'C'
1373 and paf.person_id BETWEEN p_stperson AND p_endperson
1374 and (( nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1375 'A_W2_STATE_WAGES',
1376 paa.tax_unit_id,
1377 faic.context , 2),0) > 0 )
1378 or
1379 (nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
1380 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
1381 paa.tax_unit_id,
1382 faic.context , 2),0) > 0))
1383 /* Commenting it due to Performance Issue Bug# 5630156 */
1384 -- and exists ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
1385 /* select 'x'
1386 from pay_us_state_tax_info_f pustif
1387 where substr(faic.context,1,2) = pustif.state_code
1388 and ppa.effective_date between pustif.effective_start_date
1389 and pustif.effective_end_date
1390 and pustif.sit_exists = 'Y'
1391 )
1392 */
1393 and not exists
1394 (
1395 select 'x'
1396 from hr_organization_information hoi
1397 WHERE hoi.organization_id = paa.tax_unit_id
1398 and hoi.org_information_context ='1099R Magnetic Report Rules'
1399 )
1400 ORDER BY 1, 3, 4 DESC, 2 ;
1401 /* Commenting for Performance Issue Bug# 5630156
1402 FOR UPDATE OF paf.assignment_id; */
1403
1404 CURSOR c_state_range IS
1405 SELECT /*+ index(ppa pay_payroll_actions_N52) */
1406 to_number(paa.serial_number),
1407 paf.assignment_id,
1408 paa.tax_unit_id,
1409 paf.effective_end_date,
1410 paa.assignment_action_id,
1411 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1412 'A_W2_STATE_WAGES',
1413 paa.tax_unit_id,
1414 faic.context , 2),0)
1415 FROM ff_archive_item_contexts faic,
1416 ff_archive_items fai,
1417 ff_database_items fdi,
1418 pay_assignment_actions paa,
1419 pay_payroll_actions ppa,
1420 per_all_assignments_f paf,
1421 pay_payroll_actions ppa1,
1422 pay_population_ranges ppr
1423 WHERE
1424 ppa1.payroll_action_id = p_pactid
1425 and ppa1.payroll_action_id=ppr.payroll_action_id
1426 AND ppr.chunk_number = p_chunk
1427 and ppa.business_group_id+0 = ppa1.business_group_id
1428 and ppa1.effective_date = ppa.effective_date
1429 and ppa.report_type = 'YREND'
1430 and ppa.payroll_action_id = paa.payroll_action_id
1431 and paf.assignment_id = paa.assignment_id
1432 and paf.assignment_type = 'E'
1433 and fdi.user_name = 'A_STATE_ABBREV'
1434 and fdi.user_entity_id = fai.user_entity_id
1435 and fai.archive_item_id = faic.archive_item_id
1436 and fai.context1 = paa.assignment_action_id
1437 and fai.value = ppa1.report_qualifier
1438 and paf.effective_start_date <= ppa.effective_date
1439 and paf.effective_end_date >= ppa.start_date
1440 and paa.action_status = 'C'
1441 and paf.person_id =ppr.person_id
1442 and (( nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1443 'A_W2_STATE_WAGES',
1444 paa.tax_unit_id,
1445 faic.context , 2),0) > 0 )
1446 or
1447 (nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
1448 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
1449 paa.tax_unit_id,
1450 faic.context , 2),0) > 0))
1451 /* Commenting it due to Performance Issue Bug# 5630156 */
1452 -- and exists ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
1453 /* select 'x'
1454 from pay_us_state_tax_info_f pustif
1455 where substr(faic.context,1,2) = pustif.state_code
1456 and ppa.effective_date between pustif.effective_start_date
1457 and pustif.effective_end_date
1458 and pustif.sit_exists = 'Y'
1459 )
1460 */
1461 and not exists
1462 (
1463 select 'x'
1464 from hr_organization_information hoi
1465 WHERE hoi.organization_id = paa.tax_unit_id
1466 and hoi.org_information_context ='1099R Magnetic Report Rules'
1467 )
1468 ORDER BY 1, 3, 4 DESC, 2 ;
1469 /* Commenting for Performance Issue Bug# 5630156
1470 FOR UPDATE OF paf.assignment_id; */
1471
1472 -- Introduced this cursor for State of Indiana for Bug# 5099892
1473 -- In case of Indiana, Assignment Actions need to created
1474 -- [ Employee to be included in Tape ] if non-zero County Withheld is
1475 -- there evenif the State Wages is zero.
1476
1477 CURSOR c_state_indiana IS
1478 SELECT
1479 to_number(paa.serial_number),
1480 paf.assignment_id,
1481 paa.tax_unit_id,
1482 paf.effective_end_date,
1483 paa.assignment_action_id,
1484 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1485 'A_W2_STATE_WAGES',
1486 paa.tax_unit_id,
1487 faic.context , 2),0)
1488 FROM ff_archive_item_contexts faic,
1489 ff_archive_items fai,
1490 ff_database_items fdi,
1491 pay_assignment_actions paa,
1492 pay_payroll_actions ppa,
1493 per_all_assignments_f paf,
1494 pay_payroll_actions ppa1
1495 WHERE
1496 ppa1.payroll_action_id = p_pactid
1497 and ppa.business_group_id+0 = ppa1.business_group_id
1498 and ppa1.effective_date = ppa.effective_date
1499 and ppa.report_type = 'YREND'
1500 and ppa.payroll_action_id = paa.payroll_action_id
1501 and paf.assignment_id = paa.assignment_id
1502 and paf.assignment_type = 'E'
1503 and fdi.user_name = 'A_STATE_ABBREV'
1504 and fdi.user_entity_id = fai.user_entity_id
1505 and fai.archive_item_id = faic.archive_item_id
1506 and fai.context1 = paa.assignment_action_id
1507 and fai.value = ppa1.report_qualifier
1508 and paf.effective_start_date <= ppa.effective_date
1509 and paf.effective_end_date >= ppa.start_date
1510 and paa.action_status = 'C'
1511 and paf.person_id BETWEEN p_stperson AND p_endperson
1512 and ( ((
1513 nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1514 'A_W2_STATE_WAGES',
1515 paa.tax_unit_id,
1516 faic.context , 2),0) > 0 )
1517
1518 or
1519 (
1520 nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
1521 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
1522 paa.tax_unit_id,
1523 faic.context , 2),0) > 0))
1524 OR
1525 exists (select 'x'
1526 from ff_contexts fc1,
1527 ff_archive_items fai1,
1528 ff_archive_item_contexts faic1,
1529 ff_database_items fdi1
1530 where fc1.context_name = 'JURISDICTION_CODE'
1531 and fc1.context_id = faic1.context_id
1532 and fdi1.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
1533 and fdi1.user_entity_id = fai1.user_entity_id
1534 and fai1.context1 = paa.assignment_action_id
1535 and fai1.archive_item_id = faic1.archive_item_id
1536 and substr(faic1.context,1,2) = substr(faic.context,1,2)
1537 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1538 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD',
1539 paa.tax_unit_id,
1540 faic1.context , 6),0) > 0)
1541 )
1542 and not exists
1543 (
1544 select 'x'
1545 from hr_organization_information hoi
1546 WHERE hoi.organization_id = paa.tax_unit_id
1547 and hoi.org_information_context ='1099R Magnetic Report Rules'
1548 )
1549 ORDER BY 1, 3, 4 DESC, 2 ;
1550
1551
1552 CURSOR c_state_indiana_range IS
1553 SELECT /*+ index(ppa pay_payroll_actions_N52) */
1554 to_number(paa.serial_number),
1555 paf.assignment_id,
1556 paa.tax_unit_id,
1557 paf.effective_end_date,
1558 paa.assignment_action_id,
1559 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1560 'A_W2_STATE_WAGES',
1561 paa.tax_unit_id,
1562 faic.context , 2),0)
1563 FROM ff_archive_item_contexts faic,
1564 ff_archive_items fai,
1565 ff_database_items fdi,
1566 pay_assignment_actions paa,
1567 pay_payroll_actions ppa,
1568 per_all_assignments_f paf,
1569 pay_payroll_actions ppa1,
1570 pay_population_ranges ppr
1571 WHERE
1572 ppa1.payroll_action_id = p_pactid
1573 and ppa1.payroll_action_id=ppr.payroll_action_id
1574 AND ppr.chunk_number = p_chunk
1575 and ppa.business_group_id+0 = ppa1.business_group_id
1576 and ppa1.effective_date = ppa.effective_date
1577 and ppa.report_type = 'YREND'
1578 and ppa.payroll_action_id = paa.payroll_action_id
1579 and paf.assignment_id = paa.assignment_id
1580 and paf.assignment_type = 'E'
1581 and fdi.user_name = 'A_STATE_ABBREV'
1582 and fdi.user_entity_id = fai.user_entity_id
1583 and fai.archive_item_id = faic.archive_item_id
1584 and fai.context1 = paa.assignment_action_id
1585 and fai.value = ppa1.report_qualifier
1586 and paf.effective_start_date <= ppa.effective_date
1587 and paf.effective_end_date >= ppa.start_date
1588 and paa.action_status = 'C'
1589 and paf.person_id =ppr.person_id
1590 and ( ((
1591 nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1592 'A_W2_STATE_WAGES',
1593 paa.tax_unit_id,
1594 faic.context , 2),0) > 0 )
1595
1596 or
1597 (
1598 nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
1599 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
1600 paa.tax_unit_id,
1601 faic.context , 2),0) > 0))
1602 OR
1603 exists (select 'x'
1604 from ff_contexts fc1,
1605 ff_archive_items fai1,
1606 ff_archive_item_contexts faic1,
1607 ff_database_items fdi1
1608 where fc1.context_name = 'JURISDICTION_CODE'
1609 and fc1.context_id = faic1.context_id
1610 and fdi1.user_name = 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
1611 and fdi1.user_entity_id = fai1.user_entity_id
1612 and fai1.context1 = paa.assignment_action_id
1613 and fai1.archive_item_id = faic1.archive_item_id
1614 and substr(faic1.context,1,2) = substr(faic.context,1,2)
1615 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1616 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD',
1617 paa.tax_unit_id,
1618 faic1.context , 6),0) > 0)
1619 )
1620 and not exists
1621 (
1622 select 'x'
1623 from hr_organization_information hoi
1624 WHERE hoi.organization_id = paa.tax_unit_id
1625 and hoi.org_information_context ='1099R Magnetic Report Rules'
1626 )
1627 ORDER BY 1, 3, 4 DESC, 2 ;
1628
1629 -- Introduced this cursor for State of Ohio for Bug# 5648738
1630 -- In case of Ohio, Assignment Actions need to created
1631 -- [ Employee to be included in Tape ] in presence of non-zero School Withheld
1632
1633 CURSOR c_state_ohio IS
1634 SELECT
1635 to_number(paa.serial_number),
1636 paf.assignment_id,
1637 paa.tax_unit_id,
1638 paf.effective_end_date,
1639 paa.assignment_action_id,
1640 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1641 'A_W2_STATE_WAGES',
1642 paa.tax_unit_id,
1643 faic.context , 2),0)
1644 FROM ff_archive_item_contexts faic,
1645 ff_archive_items fai,
1646 ff_database_items fdi,
1647 pay_assignment_actions paa,
1648 pay_payroll_actions ppa,
1649 per_all_assignments_f paf,
1650 pay_payroll_actions ppa1
1651 WHERE
1652 ppa1.payroll_action_id = p_pactid
1653 and ppa.business_group_id+0 = ppa1.business_group_id
1654 and ppa1.effective_date = ppa.effective_date
1655 and ppa.report_type = 'YREND'
1656 and ppa.payroll_action_id = paa.payroll_action_id
1657 and paf.assignment_id = paa.assignment_id
1658 and paf.assignment_type = 'E'
1659 and fdi.user_name = 'A_STATE_ABBREV'
1660 and fdi.user_entity_id = fai.user_entity_id
1661 and fai.archive_item_id = faic.archive_item_id
1662 and fai.context1 = paa.assignment_action_id
1663 and fai.value = ppa1.report_qualifier
1664 and paf.effective_start_date <= ppa.effective_date
1665 and paf.effective_end_date >= ppa.start_date
1666 and paa.action_status = 'C'
1667 and paf.person_id BETWEEN p_stperson AND p_endperson
1668 and ( ((
1669 nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1670 'A_W2_STATE_WAGES',
1671 paa.tax_unit_id,
1672 faic.context , 2),0) > 0 )
1673 or
1674 (
1675 nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
1676 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
1677 paa.tax_unit_id,
1678 faic.context , 2),0) > 0) )
1679 OR
1680 exists (select 'x'
1681 from ff_contexts fc1,
1682 ff_archive_items fai1,
1683 ff_archive_item_contexts faic1,
1684 ff_database_items fdi1
1685 where fc1.context_name = 'JURISDICTION_CODE'
1686 and fc1.context_id = faic1.context_id
1687 and fdi1.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
1688 and fdi1.user_entity_id = fai1.user_entity_id
1689 and fai1.context1 = paa.assignment_action_id
1690 and fai1.archive_item_id = faic1.archive_item_id
1691 and substr(faic1.context,1,2) = substr(faic.context,1,2)
1692 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1693 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD',
1694 paa.tax_unit_id,
1695 faic1.context , 8),0) > 0)
1696 )
1697 and not exists
1698 (
1699 select 'x'
1700 from hr_organization_information hoi
1701 WHERE hoi.organization_id = paa.tax_unit_id
1702 and hoi.org_information_context ='1099R Magnetic Report Rules'
1703 )
1704 ORDER BY 1, 3, 4 DESC, 2 ;
1705
1706
1707 CURSOR c_state_ohio_range IS
1708 SELECT /*+ index(ppa pay_payroll_actions_N52) */
1709 to_number(paa.serial_number),
1710 paf.assignment_id,
1711 paa.tax_unit_id,
1712 paf.effective_end_date,
1713 paa.assignment_action_id,
1714 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1715 'A_W2_STATE_WAGES',
1716 paa.tax_unit_id,
1717 faic.context , 2),0)
1718 FROM ff_archive_item_contexts faic,
1719 ff_archive_items fai,
1720 ff_database_items fdi,
1721 pay_assignment_actions paa,
1722 pay_payroll_actions ppa,
1723 per_all_assignments_f paf,
1724 pay_payroll_actions ppa1,
1725 pay_population_ranges ppr
1726 WHERE
1727 ppa1.payroll_action_id = p_pactid
1728 and ppa1.payroll_action_id=ppr.payroll_action_id
1729 AND ppr.chunk_number = p_chunk
1730 and ppa.business_group_id+0 = ppa1.business_group_id
1731 and ppa1.effective_date = ppa.effective_date
1732 and ppa.report_type = 'YREND'
1733 and ppa.payroll_action_id = paa.payroll_action_id
1734 and paf.assignment_id = paa.assignment_id
1735 and paf.assignment_type = 'E'
1736 and fdi.user_name = 'A_STATE_ABBREV'
1737 and fdi.user_entity_id = fai.user_entity_id
1738 and fai.archive_item_id = faic.archive_item_id
1739 and fai.context1 = paa.assignment_action_id
1740 and fai.value = ppa1.report_qualifier
1741 and paf.effective_start_date <= ppa.effective_date
1742 and paf.effective_end_date >= ppa.start_date
1743 and paa.action_status = 'C'
1744 and paf.person_id =ppr.person_id
1745 and ( ((
1746 nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1747 'A_W2_STATE_WAGES',
1748 paa.tax_unit_id,
1749 faic.context , 2),0) > 0 )
1750 or
1751 (
1752 nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id, /* 6809739 */
1753 'A_SIT_WITHHELD_PER_JD_GRE_YTD',
1754 paa.tax_unit_id,
1755 faic.context , 2),0) > 0) )
1756 OR
1757 exists (select 'x'
1758 from ff_contexts fc1,
1759 ff_archive_items fai1,
1760 ff_archive_item_contexts faic1,
1761 ff_database_items fdi1
1762 where fc1.context_name = 'JURISDICTION_CODE'
1763 and fc1.context_id = faic1.context_id
1764 and fdi1.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
1765 and fdi1.user_entity_id = fai1.user_entity_id
1766 and fai1.context1 = paa.assignment_action_id
1767 and fai1.archive_item_id = faic1.archive_item_id
1768 and substr(faic1.context,1,2) = substr(faic.context,1,2)
1769 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
1770 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD',
1771 paa.tax_unit_id,
1772 faic1.context , 8),0) > 0)
1773 )
1774 and not exists
1775 (
1776 select 'x'
1777 from hr_organization_information hoi
1778 WHERE hoi.organization_id = paa.tax_unit_id
1779 and hoi.org_information_context ='1099R Magnetic Report Rules'
1780 )
1781 ORDER BY 1, 3, 4 DESC, 2 ;
1782
1783 -- Cursor to get the assignments for federal W2. Excludes 1099R GREs.
1784 CURSOR c_federal IS
1785 SELECT paf.person_id,
1786 paf.assignment_id,
1787 Paa.tax_unit_id, --TO_NUMBER(hsck.segment1),
1788 paf.effective_end_date,
1789 paa.assignment_action_id
1790 FROM pay_payroll_actions ppa,
1791 pay_assignment_actions paa,
1792 per_all_assignments_f paf,
1793 pay_payroll_actions ppa1
1794 WHERE ppa1.payroll_action_id = p_pactid
1795 AND ppa.report_type = 'YREND'
1796 AND ppa.business_group_id+0 = ppa1.business_group_id
1797 AND ppa.effective_date = ppa1.effective_date
1798 AND ppa.start_date = ppa1.start_date
1799 AND paa.payroll_action_id = ppa.payroll_action_id
1800 AND paa.action_status = 'C'
1801 AND paf.assignment_id = paa.assignment_id
1802 --AND paa.serial_number between to_char(p_stperson) AND to_char(p_endperson)
1803 AND to_number(paa.serial_number) BETWEEN p_stperson AND p_endperson /* 6712859 */
1804 AND paf.person_id BETWEEN p_stperson AND p_endperson
1805 AND paf.assignment_type = 'E'
1806 AND paf.effective_start_date <= ppa.effective_date
1807 AND paf.effective_end_date >= ppa.start_date
1808 AND not exists (
1809 SELECT 'x'
1810 FROM hr_organization_information hoi
1811 WHERE hoi.organization_id = paa.tax_unit_id
1812 and hoi.org_information_context = '1099R Magnetic Report Rules')
1813 ORDER BY 1, 3, 4 DESC, 2
1814 FOR UPDATE OF paf.assignment_id;
1815
1816 CURSOR c_federal_range IS
1817 SELECT /*+ index(ppa pay_payroll_actions_N52) */ paf.person_id,
1818 paf.assignment_id,
1819 Paa.tax_unit_id, --TO_NUMBER(hsck.segment1),
1820 paf.effective_end_date,
1821 paa.assignment_action_id
1822 FROM pay_payroll_actions ppa,
1823 pay_assignment_actions paa,
1824 per_all_assignments_f paf,
1825 pay_payroll_actions ppa1,
1826 pay_population_ranges ppr
1827 WHERE ppa1.payroll_action_id = p_pactid
1828 and ppa1.payroll_action_id=ppr.payroll_action_id
1829 AND ppr.chunk_number = p_chunk
1830 AND ppa.report_type = 'YREND'
1831 AND ppa.business_group_id+0 = ppa1.business_group_id
1832 AND ppa.effective_date = ppa1.effective_date
1833 AND ppa.start_date = ppa1.start_date
1834 AND paa.payroll_action_id = ppa.payroll_action_id
1835 AND paa.action_status = 'C'
1836 AND paf.assignment_id = paa.assignment_id
1837 --AND paa.serial_number between to_char(p_stperson) AND to_char(p_endperson)
1838 AND to_number(paa.serial_number) =ppr.person_id
1839 AND paf.person_id =ppr.person_id
1840 AND paf.assignment_type = 'E'
1841 AND paf.effective_start_date <= ppa.effective_date
1842 AND paf.effective_end_date >= ppa.start_date
1843 AND not exists (
1844 SELECT 'x'
1845 FROM hr_organization_information hoi
1846 WHERE hoi.organization_id = paa.tax_unit_id
1847 and hoi.org_information_context = '1099R Magnetic Report Rules')
1848 ORDER BY 1, 3, 4 DESC, 2
1849 FOR UPDATE OF paf.assignment_id;
1850
1851 cursor csr_get_fed_wages(p_user_name varchar2,
1852 p_assignment_action_id number,
1853 p_tax_unit_id number) is
1854 select to_number(fai.value) value
1855 from ff_archive_item_contexts faic,
1856 ff_archive_items fai,
1857 ff_contexts fc,
1858 ff_database_items fdi
1859 where fdi.user_name = p_user_name
1860 and fc.context_name = 'TAX_UNIT_ID'
1861 and fai.context1 = to_char(p_assignment_action_id)
1862 and fai.user_entity_id = fdi.user_entity_id
1863 and faic.archive_item_id = fai.archive_item_id
1864 and faic.context_id = fc.context_id
1865 and faic.context = to_char(p_tax_unit_id)
1866 and faic.sequence_no = 1;
1867
1868 cursor csr_get_fit_sub_wages(p_assignment_action_id number,
1869 p_tax_unit_id number) is
1870 select to_number(fai.value) value
1871 from ff_archive_item_contexts faic,
1872 ff_archive_items fai,
1873 ff_contexts fc,
1874 ff_database_items fdi
1875 where fdi.user_name IN ('A_REGULAR_EARNINGS_PER_GRE_YTD',
1876 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD',
1877 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD',
1878 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD',
1879 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD')
1880 and fc.context_name = 'TAX_UNIT_ID'
1881 and fai.context1 = to_char(p_assignment_action_id)
1882 and fai.user_entity_id = fdi.user_entity_id
1883 and faic.archive_item_id = fai.archive_item_id
1884 and faic.context_id = fc.context_id
1885 and faic.context = to_char(p_tax_unit_id)
1886 and faic.sequence_no = 1;
1887
1888 --Changes for bug 9467090
1889 CURSOR csr_action_parameter is
1890 select parameter_value
1891 from pay_action_parameters
1892 where parameter_name = 'RANGE_PERSON_ID';
1893 --Changes for bug 9467090
1894 --local variables
1895 l_year_start DATE;
1896 l_year_end DATE;
1897 l_effective_end_date DATE;
1898 l_state_abbrev VARCHAR2(3);
1899 l_state_code VARCHAR2(2);
1900 l_report_type VARCHAR2(30);
1901 l_business_group_id NUMBER;
1902 l_person_id NUMBER;
1903 l_prev_person_id NUMBER;
1904 l_assignment_id NUMBER;
1905 l_assignment_action_id NUMBER;
1906 l_value NUMBER;
1907 l_tax_unit_id NUMBER;
1908 l_prev_tax_unit_id NUMBER;
1909 lockingactid NUMBER;
1910 l_group_by_gre BOOLEAN;
1911 l_w2_box17 NUMBER; --SIT Wages
1912 l_gre_id NUMBER;
1913 l_error_flag VARCHAR2(10);
1914 BEGIN
1915 -- Set the local variable to correct Value
1916
1917 l_gre_id := -1;
1918 l_error_flag := 'N';
1919
1920 -- Get the report parameters. These define the report being run.
1921 hr_utility.set_location( 'pay_us_mmref_reporting.create_assignement_act',
1922 10);
1923 get_report_parameters(
1924 p_pactid,
1925 l_year_start,
1926 l_year_end,
1927 l_state_abbrev,
1928 l_state_code,
1929 l_report_type,
1930 l_business_group_id
1931 );
1932 open csr_action_parameter;
1933 fetch csr_action_parameter into g_action_param_val;
1934 close csr_action_parameter;
1935 --Currently all reports group by GRE
1936 l_group_by_gre := TRUE;
1937 --Open the appropriate cursor
1938 hr_utility.set_location( 'pay_us_mmref_reporting.create_assignement_act',
1939 20);
1940 IF l_report_type = 'FEDW2' THEN
1941 if g_action_param_val='Y' then
1942 OPEN c_federal_range;
1943 else
1944 OPEN c_federal;
1945 end if;
1946 --
1947 -- This was added specifically to have PuertoRico GRE employees for assignment creation
1948 --
1949 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'PR' THEN
1950 if g_action_param_val='Y' then
1951 OPEN c_pr_state_range;
1952 else
1953 OPEN c_pr_state;
1954 end if;
1955 /* Added for Bug# 5099892 */
1956 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'IN' THEN
1957 if g_action_param_val='Y' then
1958 OPEN c_state_indiana_range ;
1959 else
1960 OPEN c_state_indiana ;
1961 end if;
1962 /* Added for Bug# 5648738 */
1963 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'OH' THEN
1964 if g_action_param_val='Y' then
1965 OPEN c_state_ohio_range ;
1966 else
1967 OPEN c_state_ohio ;
1968 end if;
1969 ELSIF l_report_type = 'STW2' THEN
1970 if g_action_param_val='Y' then
1971 OPEN c_state_range;
1972 else
1973 OPEN c_state;
1974 end if;
1975 END IF;
1976 LOOP
1977 IF l_report_type = 'FEDW2' THEN
1978 if g_action_param_val='Y' then
1979 FETCH c_federal_range INTO l_person_id,
1980 l_assignment_id,
1981 l_tax_unit_id,
1982 l_effective_end_date,
1983 l_assignment_action_id;
1984 else
1985 FETCH c_federal INTO l_person_id,
1986 l_assignment_id,
1987 l_tax_unit_id,
1988 l_effective_end_date,
1989 l_assignment_action_id;
1990 end if;
1991 hr_utility.set_location(
1992 'pay_us_mmref_reporting.create_assignement_act', 30);
1993 if g_action_param_val='Y' then
1994 EXIT WHEN c_federal_range%NOTFOUND;
1995 else
1996 EXIT WHEN c_federal%NOTFOUND;
1997 end if;
1998 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'PR' THEN
1999 if g_action_param_val='Y' then
2000 FETCH c_pr_state_range INTO l_person_id,
2001 l_assignment_id,
2002 l_tax_unit_id,
2003 l_effective_end_date,
2004 l_assignment_action_id,
2005 l_w2_box17;
2006 else
2007 FETCH c_pr_state INTO l_person_id,
2008 l_assignment_id,
2009 l_tax_unit_id,
2010 l_effective_end_date,
2011 l_assignment_action_id,
2012 l_w2_box17;
2013 end if;
2014 hr_utility.set_location(
2015 'pay_us_mmref_reporting.create_assignement_act', 40);
2016 if g_action_param_val='Y' then
2017 EXIT WHEN c_pr_state_range%NOTFOUND;
2018 else
2019 EXIT WHEN c_pr_state%NOTFOUND;
2020 end if;
2021 -- Check the state Tax rules if new gre
2022 -- Set the Error Flag to Y so that Action Creation will Error
2023 -- At the End
2024
2025 IF l_gre_id = l_tax_unit_id THEN
2026 hr_utility.trace('Same GRE ');
2027 ELSE
2028 IF check_state_er_data(p_pactid,l_tax_unit_id,'A') = 'N' THEN
2029 hr_utility.trace('State Tax Rules Missing in GRE');
2030 l_gre_id := l_tax_unit_id;
2031 l_error_flag := 'Y';
2032 ELSE
2033 l_gre_id := l_tax_unit_id;
2034 END if ; --check ER
2035 END IF;
2036 -- Added for Bug# 5099892
2037 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'IN' THEN
2038 if g_action_param_val='Y' then
2039 FETCH c_state_indiana_range INTO l_person_id,
2040 l_assignment_id,
2041 l_tax_unit_id,
2042 l_effective_end_date,
2043 l_assignment_action_id,
2044 l_w2_box17;
2045 else
2046 FETCH c_state_indiana INTO l_person_id,
2047 l_assignment_id,
2048 l_tax_unit_id,
2049 l_effective_end_date,
2050 l_assignment_action_id,
2051 l_w2_box17;
2052 end if;
2053 hr_utility.set_location(
2054 'pay_us_mmref_reporting.create_assignement_act', 40);
2055 if g_action_param_val='Y' then
2056 EXIT WHEN c_state_indiana_range%NOTFOUND;
2057 else
2058 EXIT WHEN c_state_indiana%NOTFOUND;
2059 end if;
2060 -- Check the state Tax rules if new gre
2061 -- Set the Error Flag to Y so that Action Creation will Error
2062 -- At the End
2063
2064 IF l_gre_id = l_tax_unit_id THEN
2065 hr_utility.trace('Same GRE ');
2066 ELSE
2067 IF check_state_er_data(p_pactid,l_tax_unit_id,'A') = 'N' THEN
2068 hr_utility.trace('State Tax Rules Missing in GRE');
2069 l_gre_id := l_tax_unit_id;
2070 l_error_flag := 'Y';
2071 ELSE
2072 l_gre_id := l_tax_unit_id;
2073 END if ; --check ER
2074 END IF;
2075
2076 -- Added for Bug# 5648738
2077 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'OH' THEN
2078 if g_action_param_val='Y' then
2079 FETCH c_state_ohio_range INTO l_person_id,
2080 l_assignment_id,
2081 l_tax_unit_id,
2082 l_effective_end_date,
2083 l_assignment_action_id,
2084 l_w2_box17;
2085 else
2086 FETCH c_state_ohio INTO l_person_id,
2087 l_assignment_id,
2088 l_tax_unit_id,
2089 l_effective_end_date,
2090 l_assignment_action_id,
2091 l_w2_box17;
2092 end if;
2093 hr_utility.set_location(
2094 'pay_us_mmref_reporting.create_assignement_act', 40);
2095 if g_action_param_val='Y' then
2096 EXIT WHEN c_state_ohio_range%NOTFOUND;
2097 else
2098 EXIT WHEN c_state_ohio%NOTFOUND;
2099 end if;
2100 -- Check the state Tax rules if new gre
2101 -- Set the Error Flag to Y so that Action Creation will Error
2102 -- At the End
2103
2104 IF l_gre_id = l_tax_unit_id THEN
2105 hr_utility.trace('Same GRE ');
2106 ELSE
2107 IF check_state_er_data(p_pactid,l_tax_unit_id,'A') = 'N' THEN
2108 hr_utility.trace('State Tax Rules Missing in GRE');
2109 l_gre_id := l_tax_unit_id;
2110 l_error_flag := 'Y';
2111 ELSE
2112 l_gre_id := l_tax_unit_id;
2113 END if ; --check ER
2114 END IF;
2115
2116 ELSIF l_report_type = 'STW2' THEN
2117 if g_action_param_val='Y' then
2118 FETCH c_state_range INTO l_person_id,
2119 l_assignment_id,
2120 l_tax_unit_id,
2121 l_effective_end_date,
2122 l_assignment_action_id,
2123 l_w2_box17;
2124 else
2125 FETCH c_state INTO l_person_id,
2126 l_assignment_id,
2127 l_tax_unit_id,
2128 l_effective_end_date,
2129 l_assignment_action_id,
2130 l_w2_box17;
2131 end if;
2132 hr_utility.set_location(
2133 'pay_us_mmref_reporting.create_assignement_act', 40);
2134 if g_action_param_val='Y' then
2135 EXIT WHEN c_state_range%NOTFOUND;
2136 else
2137 EXIT WHEN c_state%NOTFOUND;
2138 end if;
2139 -- Check the state Tax rules if new gre
2140 -- Set the Error Flag to Y so that Action Creation will Error
2141 -- At the End
2142
2143 IF l_gre_id = l_tax_unit_id THEN
2144 hr_utility.trace('Same GRE ');
2145 ELSE
2146 IF check_state_er_data(p_pactid,l_tax_unit_id,'A') = 'N' THEN
2147 hr_utility.trace('State Tax Rules Missing in GRE');
2148 l_gre_id := l_tax_unit_id;
2149 l_error_flag := 'Y';
2150 ELSE
2151 l_gre_id := l_tax_unit_id;
2152 END if ; --check ER
2153 END IF;
2154
2155
2156 END IF;
2157 --Based on the groupin criteria, check if the record is the same
2158 --as the previous record.
2159 --Grouping by GRE requires a unique person/GRE combination for
2160 --each record.
2161 IF ((l_group_by_gre AND
2162 l_person_id = l_prev_person_id AND
2163 l_tax_unit_id = l_prev_tax_unit_id) OR
2164 (NOT l_group_by_gre AND
2165 l_person_id = l_prev_person_id)) THEN
2166 --Do Nothing
2167 hr_utility.set_location(
2168 'pay_us_mmref_reporting.create_assignement_act', 50);
2169 NULL;
2170 ELSE
2171 --Create the assignment action for the record
2172 hr_utility.trace('Assignment Fetched - ');
2173 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
2174 hr_utility.trace('Person Id : '|| to_char(l_person_id));
2175 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
2176 hr_utility.trace('Effective End Date : '||
2177 to_char(l_effective_end_date));
2178
2179 IF l_report_type = 'FEDW2' then
2180
2181 l_value := 0;
2182 /* Bug 11907144 - Added following 2 checks.
2183 If Employee has any of 'SS Wages', 'Medicare Wages', then employee is
2184 picked in the 'Federal W2 Mag Media' Report */
2185
2186 FOR c_rec IN csr_get_fed_wages('A_SS_EE_TAXABLE_PER_GRE_YTD',
2187 l_assignment_action_id,
2188 l_tax_unit_id)
2189 LOOP
2190 l_value := c_rec.value;
2191 END LOOP;
2192
2193 IF l_value = 0 THEN
2194 FOR c_rec IN csr_get_fed_wages('A_MEDICARE_EE_TAXABLE_PER_GRE_YTD',
2195 l_assignment_action_id,
2196 l_tax_unit_id)
2197 LOOP
2198 l_value := c_rec.value;
2199 END LOOP;
2200 END IF;
2201
2202 -- Check FOR SS Withheld, added by tmehra
2203
2204 IF l_value = 0 THEN
2205 FOR c_rec IN csr_get_fed_wages('A_SS_EE_WITHHELD_PER_GRE_YTD',
2206 l_assignment_action_id,
2207 l_tax_unit_id)
2208 LOOP
2209 l_value := c_rec.value;
2210 END LOOP;
2211 END IF;
2212
2213 -- Check for Medicare balance if SS is zero -- tmehra
2214
2215 IF l_value = 0 THEN
2216
2217 FOR c_rec IN csr_get_fed_wages
2218 ('A_MEDICARE_EE_WITHHELD_PER_GRE_YTD',
2219 l_assignment_action_id,
2220 l_tax_unit_id)
2221 LOOP
2222 l_value := c_rec.value;
2223 END LOOP;
2224
2225 END IF;
2226
2227 -- Check for FIT Subject balance if Medicare is also zero
2228 -- Since FIT Subject is a derieved balance we add the
2229 -- following balances
2230 -- - 'A_REGULAR_EARNINGS_PER_GRE_YTD'
2231 -- - 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'
2232 -- - 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD'
2233 -- - 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD'
2234 -- - 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD'
2235 -- added by tmehra
2236
2237 IF l_value = 0 THEN
2238
2239 FOR c_rec IN csr_get_fit_sub_wages( l_assignment_action_id,
2240 l_tax_unit_id)
2241 LOOP
2242 l_value := l_value + c_rec.value;
2243 END LOOP;
2244
2245 END IF;
2246
2247 If l_value = 0 THEN /* 6868340 */
2248 FOR c_rec IN csr_get_fed_wages
2249 ('A_FIT_WITHHELD_PER_GRE_YTD',
2250 l_assignment_action_id,
2251 l_tax_unit_id)
2252 LOOP
2253 l_value := c_rec.value;
2254 END LOOP;
2255 end if ;
2256
2257 END IF;
2258 IF (l_report_type = 'FEDW2' and l_value <> 0 ) OR
2259 (l_report_type = 'STW2') then
2260 SELECT pay_assignment_actions_s.nextval
2261 INTO lockingactid
2262 FROM dual;
2263 hr_utility.set_location(
2264 'pay_us_mmref_reporting.create_assignement_act', 60);
2265 hr_nonrun_asact.insact(lockingactid, l_assignment_id, p_pactid,
2266 p_chunk, l_tax_unit_id);
2267 hr_utility.set_location(
2268 'pay_us_mmref_reporting.create_assignement_act', 70);
2269 --update serial number for highly compensated people for the
2270 --state W2.
2271 /*IF l_report_type = 'STW2' THEN
2272 hr_utility.set_location(
2273 'pay_us_mmref_reporting.create_assignement_act', 80);
2274 IF l_w2_box17 > 9999999.99 THEN
2275 UPDATE pay_assignment_actions
2276 SET serial_number = 999999
2277 WHERE assignment_action_id = lockingactid;
2278 END IF;
2279 END IF;*/ -- 4490252
2280 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
2281 hr_utility.set_location(
2282 'pay_us_mmref_reporting.create_assignement_act', 90);
2283 hr_utility.trace('Interlock Created - ');
2284 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
2285 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
2286 --Store the current person/GRE for comparision during the
2287 --next iteration.
2288 l_prev_person_id := l_person_id;
2289 l_prev_tax_unit_id := l_tax_unit_id;
2290 END IF;
2291 END IF;
2292 END LOOP;
2293 IF l_report_type = 'FEDW2' THEN
2294 if g_action_param_val='Y' then
2295 CLOSE c_federal_range;
2296 else
2297 CLOSE c_federal;
2298 end if;
2299 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'PR' THEN
2300 if g_action_param_val='Y' then
2301 CLOSE c_pr_state_range;
2302 else
2303 CLOSE c_pr_state;
2304 end if;
2305 /* Added for Bug# 5099892 */
2306 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'IN' THEN
2307 if g_action_param_val='Y' then
2308 CLOSE c_state_indiana_range ;
2309 else
2310 CLOSE c_state_indiana ;
2311 end if;
2312 ELSIF l_report_type = 'STW2' and l_state_abbrev = 'OH' THEN
2313 if g_action_param_val='Y' then
2314 CLOSE c_state_ohio_range ;
2315 else
2316 CLOSE c_state_ohio ;
2317 end if;
2318 ELSIF l_report_type = 'STW2' THEN
2319 if g_action_param_val='Y' then
2320 CLOSE c_state_range;
2321 else
2322 CLOSE c_state;
2323 end if;
2324 END IF;
2325
2326 IF l_error_flag = 'Y' THEN
2327 hr_utility.trace('Error Flag was set to Y');
2328 hr_utility.raise_error;
2329 END IF;
2330
2331 END create_assignment_act;
2332
2333 FUNCTION check_er_data (
2334 p_pactid IN NUMBER,
2335 p_ein_user_id IN NUMBER )
2336 RETURN varchar2
2337 IS
2338
2339 l_ein_val varchar2(240); -- Bug# 14286448 Gre length issue
2340 l_ein_status varchar2(80);
2341 l_add_status varchar2(80);
2342 l_gre number;
2343
2344
2345 message_preprocess varchar2(80);
2346 message_text varchar2(80);
2347
2348 CURSOR c_get_user_entity_id
2349 IS
2350 SELECT user_entity_id
2351 FROM ff_database_items
2352 WHERE user_name in ( 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER',
2353 'A_TAX_UNIT_NAME');
2354
2355 CURSOR c_get_address_entity_id
2356 IS
2357 SELECT user_entity_id
2358 FROM ff_database_items
2359 WHERE user_name = 'TAX_UNIT_ADDRESS_LINE_1';
2360
2361
2362
2363 BEGIN
2364 l_ein_status := 'Y';
2365 l_gre := p_ein_user_id;
2366 FOR c_id IN c_get_user_entity_id LOOP
2367
2368 SELECT value
2369 INTO l_ein_val
2370 FROM ff_archive_items fai,
2371 ff_contexts fc,
2372 ff_archive_item_contexts faic
2373 WHERE fai.context1 = to_char(p_pactid)
2374 AND user_entity_id = c_id.user_entity_id
2375 AND faic.archive_item_id = fai.archive_item_id
2376 AND faic.context = to_char(l_gre)
2377 AND fc.context_name = 'TAX_UNIT_ID'
2378 AND fc.context_id = faic.context_id ;
2379
2380 IF l_ein_val IS NULL OR l_ein_status = 'N' THEN
2381 l_ein_status := 'N' ;
2382 END IF;
2383
2384 END LOOP;
2385
2386 IF l_ein_status = 'N' THEN
2387 message_preprocess := 'Pre-Process Check-';
2388 message_text := 'EIN or Tax Unit Name Missing ';
2389 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
2390 pay_core_utils.push_token('record_name',message_preprocess);
2391 pay_core_utils.push_token('description',message_text);
2392 END IF;
2393
2394 return l_ein_status ;
2395
2396 exception WHEN OTHERS THEN
2397 return 'N';
2398 END check_er_data;
2399
2400 /* check the data for only one state EIN */
2401
2402 FUNCTION check_state_er_data (
2403 p_pactid IN NUMBER,
2404 p_tax_unit IN NUMBER,
2405 p_jurisdictions IN varchar2 )
2406 RETURN varchar2
2407 IS
2408 l_state_ein_val varchar2(80);
2409 l_sit_state_id varchar2(80);
2410 l_jurisdiction varchar2(80);
2411
2412 CURSOR c_state_sit IS
2413 SELECT user_entity_id
2414 FROM ff_user_entities
2415 WHERE user_entity_name = 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID';
2416
2417 CURSOR c_get_state_id
2418 IS
2419 SELECT value
2420 FROM ff_archive_items fai,
2421 ff_archive_item_contexts faic,
2422 ff_archive_item_contexts faic1
2423 WHERE context1 = to_char(p_pactid)
2424 AND user_entity_id = l_sit_state_id
2425 AND faic.archive_item_id = fai.archive_item_id
2426 AND faic1.archive_item_id = fai.archive_item_id
2427 AND faic.context = to_char(p_tax_unit)
2428 and faic1.context = p_jurisdictions || '-000-0000';
2429
2430
2431 BEGIN
2432
2433 OPEN c_state_sit;
2434 FETCH c_state_sit INTO l_sit_state_id;
2435 CLOSE c_state_sit;
2436
2437
2438 OPEN c_get_state_id;
2439 FETCH c_get_state_id INTO l_state_ein_val;
2440
2441 CLOSE c_get_state_id;
2442 IF l_state_ein_val is NULL THEN
2443 return 'Y';
2444 ELSE
2445 return 'Y';
2446 END IF;
2447
2448 exception WHEN OTHERS THEN
2449 return 'Y';
2450 END check_state_er_data;
2451
2452
2453 PROCEDURE ARCHIVE_EOY_DATA (
2454 p_pactid IN NUMBER,
2455 p_tax_id IN NUMBER )
2456
2457 IS
2458 BEGIN
2459 /* get the Parameter Setting */
2460 pay_us_archive.eoy_archive_gre_data(p_pactid,p_tax_id,'FED W2 REPORTING RULES','ALL');
2461 pay_us_archive.eoy_archive_gre_data(p_pactid,p_tax_id,'FED TAX UNIT INFORMATION','ALL');
2462 pay_us_archive.eoy_archive_gre_data(p_pactid,p_tax_id,'FEDERAL TAX RULES','ALL');
2463
2464 END ARCHIVE_EOY_DATA;
2465
2466 /* Note: There is no way of limiting the archiving data related to single state
2467 */
2468
2469 PROCEDURE ARCHIVE_STATE_EOY_DATA (
2470 p_pactid IN NUMBER,
2471 p_tax_id IN NUMBER,
2472 p_state_code IN VARCHAR2 )
2473
2474 IS
2475 BEGIN
2476 /* get the Parameter Setting */
2477
2478 hr_utility.trace('Calling the state archiving ');
2479 hr_utility.trace('Pactid ' || to_char(p_pactid));
2480 hr_utility.trace('tax id ' || to_char(p_tax_id));
2481 hr_utility.trace('Pactid ' || p_state_code);
2482
2483 pay_us_archive.eoy_archive_gre_data(p_pactid,p_tax_id,'STATE TAX RULES',p_state_code);
2484
2485 END ARCHIVE_STATE_EOY_DATA;
2486
2487 FUNCTION check_state_data
2488 ( p_payroll_action_id number,
2489 p_transfer_state varchar2
2490 ) RETURN varchar2
2491 IS
2492 /* get the state Code */
2493 CURSOR c_state_code(cp_state varchar2 )
2494 IS select state_code
2495 from pay_us_states
2496 WHERE state_abbrev = cp_state;
2497
2498 CURSOR c_yep_tax_unit_ppa_id(cp_payroll_action_id number )
2499 IS
2500 SELECT DISTINCT paa.tax_unit_id unit_id,ppa1.payroll_action_Id payroll_action,name
2501 FROM pay_assignment_actions paa
2502 ,pay_payroll_actions ppa1 /* year End Pre-process for GRE */
2503 ,pay_payroll_actions ppa /* Year End Pre-process for W-2 */
2504 ,hr_organization_units hou
2505 WHERE
2506 ppa.payroll_action_id = cp_payroll_action_id /* W2 payroll_action_id */
2507 and ppa.payroll_action_id = paa.payroll_action_id
2508 and ppa1.legislative_parameters like ltrim(rtrim(to_char(paa.tax_unit_id))) || ' TRANS%'
2509 and ppa1.effective_date = ppa.effective_date
2510 and ppa1.report_type = 'YREND'
2511 and ppa1.report_qualifier = 'FED'
2512 and hou.organization_id = paa.tax_unit_id;
2513
2514 CURSOR c_get_pr_control_num ( cp_tax_unit_id number)
2515 IS
2516 SELECT org_information17 from hr_organization_information
2517 WHERE org_information_context = 'W2 Reporting Rules'
2518 AND organization_id = cp_tax_unit_id;
2519
2520 CURSOR c_sit_check (cp_payroll_action_id number,
2521 cp_tax_unit_id number,
2522 cp_state_Code varchar2 )
2523 IS
2524 SELECT target.value
2525 FROM
2526 ff_archive_item_contexts con3,
2527 ff_archive_item_contexts con2,
2528 ff_contexts fc3,
2529 ff_contexts fc2,
2530 ff_archive_items target,
2531 ff_database_items fdi
2532 WHERE target.context1 = to_char(cp_payroll_action_id)
2533 /* context of payroll_action_id */
2534 and fdi.user_name = 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID'
2535 and target.user_entity_id = fdi.user_entity_id
2536 and fc2.context_name = 'TAX_UNIT_ID'
2537 and con2.archive_item_id = target.archive_item_id
2538 and con2.context_id = fc2.context_id
2539 and ltrim(rtrim(con2.context)) = ltrim(rtrim(to_char(cp_tax_unit_id)))
2540 and fc3.context_name = 'JURISDICTION_CODE'
2541 and con3.archive_item_id = target.archive_item_id
2542 and con3.context_id = fc3.context_id
2543 and substr(ltrim(rtrim(con3.context)),1,2) = ltrim(rtrim(cp_state_code));
2544 /* jurisdiction code of the state */
2545 /* local Variables */
2546 l_w2_state pay_us_states.state_code%type;
2547 l_tax_unit_id number;
2548 l_payroll_action_id number;
2549 l_state_code varchar2(2);
2550 l_info varchar2(80);
2551 l_flag varchar2(2) := 'Y';
2552 l_control_num_flag varchar2(2) := 'Y';
2553 l_gre hr_organization_units.name%type;
2554 l_message_preprocess varchar2(300); -- Bug# 14286448 Gre length issue
2555 l_message_text varchar2(200);
2556 u_message_text varchar2(200);
2557 l_control_number number;
2558 BEGIN
2559 IF p_transfer_state = 'FED' THEN
2560 return 'Y';
2561 end if;
2562 /* Get the state Code for the W2 Tape */
2563 OPEN C_STATE_CODE(p_transfer_state);
2564 FETCH c_state_code into l_w2_state;
2565 CLOSE C_STATE_CODE;
2566 FOR c1 IN c_yep_tax_unit_ppa_id(p_payroll_action_id ) LOOP
2567
2568 IF p_transfer_state = 'PR' THEN
2569 open c_get_pr_control_num(C1.unit_id);
2570 fetch c_get_pr_control_num INTO l_control_number;
2571 if c_get_pr_control_num%notfound then
2572 l_message_text := 'ERROR: PR 499R Starting Control Number not defined';
2573 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
2574 pay_core_utils.push_token('record_name',l_message_preprocess);
2575 pay_core_utils.push_token('description',l_message_text);
2576 hr_utility.trace(l_message_preprocess || ' ' || l_message_text );
2577 l_control_num_flag := 'N';
2578 else
2579 if (l_control_number is NULL or l_control_number = 0) then
2580 l_message_text := 'ERROR:PR 499R Starting control Number is NULL';
2581 hr_utility.trace(l_message_preprocess || ' ' || l_message_text );
2582 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
2583 pay_core_utils.push_token('record_name',l_message_preprocess);
2584 pay_core_utils.push_token('description',l_message_text);
2585 hr_utility.trace(l_message_preprocess || ' ' || l_message_text );
2586 l_control_num_flag := 'N';
2587 end if;
2588 end if;
2589 close c_get_pr_control_num;
2590 END IF;
2591 open c_sit_check(C1.payroll_action,c1.unit_id,l_w2_state);
2592 l_message_preprocess := 'GRE: ' ||substr(c1.name,1,220) || ' has ' ; --Modified for the Bug 14223392
2593 fetch c_sit_check INTO l_info ;
2594 l_message_text := to_char(c1.unit_id) || 'Payroll_action_id ' || to_char(c1.payroll_action);
2595 hr_utility.trace(l_message_preprocess || ' ' || l_message_text) ;
2596 if c_sit_check%notfound then
2597 l_message_text := 'ERROR: Missing State Tax Rules For State of :' || p_transfer_state;
2598 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
2599 pay_core_utils.push_token('record_name',l_message_preprocess);
2600 pay_core_utils.push_token('description',l_message_text);
2601 hr_utility.trace(l_message_preprocess || ' ' || l_message_text );
2602 l_flag := 'N';
2603 close c_sit_check;
2604 else
2605 if l_info IS NULL THEN
2606 l_flag := 'N';
2607 l_message_text := 'ERROR: NULL EIN For State of ' || p_transfer_state;
2608 hr_utility.trace(l_message_preprocess || ' ' || l_message_text );
2609 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
2610 pay_core_utils.push_token('record_name',l_message_preprocess);
2611 pay_core_utils.push_token('description',l_message_text);
2612 end if ;
2613 close c_sit_check;
2614 end if;
2615 END loop;
2616 IF l_flag = 'N' OR l_control_num_flag = 'N' THEN
2617 l_message_text := 'Set your W2 Reporting Rules or State Tax Rules';
2618 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
2619 pay_core_utils.push_token('record_name',l_message_preprocess);
2620 pay_core_utils.push_token('description',l_message_text);
2621 hr_utility.raise_error;
2622 return 'N';
2623 END IF;
2624 return 'Y';
2625 EXCEPTION
2626 WHEN OTHERS THEN
2627 l_message_preprocess := 'Exception ';
2628 l_message_text := 'Set your W2 Reporting Rules or State Tax Rules';
2629 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
2630 pay_core_utils.push_token('record_name',l_message_preprocess);
2631 pay_core_utils.push_token('description',l_message_text);
2632 u_message_text := 'Set your W2 Reporting Rules or State Tax Rules';
2633 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
2634 hr_utility.set_message_token('MESSAGE', u_message_text);
2635 hr_utility.raise_error;
2636 return 'Y';
2637 END check_state_data;
2638
2639 -- ----------------------------------------------------------------------------
2640 -- -----------------------< get_report_category >-----------------------------
2641 -- ----------------------------------------------------------------------------
2642 -- Description:
2643 -- Returns the 'RM' or 'RG' depending upon the archived value for the Govt
2644 -- employer.
2645 -- Added for US Payroll Govt Employer W2 specific situations.
2646 --
2647 -- Pre Conditions:
2648 -- If no archived value is found, default value of 'RM' is returned. This is
2649 -- done to support the employees whose data was archived before these
2650 -- changes.
2651 --
2652 --
2653 -- In Parameters:
2654 -- p_business_group_id
2655 -- p_effective_date
2656 --
2657 -- Post Success:
2658 -- Returns 'RG' or 'RM'
2659 --
2660 -- Added by tmehra
2661 -- ----------------------------------------------------------------------------
2662 FUNCTION get_report_category ( p_business_group_id number,
2663 p_effective_date date
2664 ) RETURN varchar2 IS
2665
2666 --
2667 l_proc varchar2(100) := 'pay_us_mmref_reporting.get_report_category';
2668 l_code varchar2(2);
2669 l_ue_id NUMBER;
2670 --
2671 --
2672 CURSOR c_get_user_entity_id IS
2673 SELECT user_entity_id
2674 FROM ff_user_entities
2675 WHERE user_entity_name = 'A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER';
2676
2677
2678 CURSOR c_chk_for_govt_employer (p_user_entity_id NUMBER) IS
2679 SELECT 'RG'
2680 FROM DUAL
2681 WHERE EXISTS
2682 (SELECT NULL
2683 FROM pay_payroll_actions ppa,
2684 ff_archive_items fai
2685 WHERE ppa.report_type = 'YREND'
2686 AND ppa.report_qualifier = 'FED'
2687 AND ppa.report_category = 'RT'
2688 AND ppa.effective_date = p_effective_date
2689 AND ppa.business_group_id = p_business_group_id
2690 AND fai.context1 = ppa.payroll_action_id
2691 AND fai.user_entity_id = p_user_entity_id
2692 AND fai.value = 'Y'
2693 );
2694
2695 BEGIN
2696 --
2697 hr_utility.set_location('Entering:'||l_proc, 10);
2698 --
2699
2700
2701 -- Get the user entity id for A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER
2702
2703 FOR c_rec IN c_get_user_entity_id
2704 LOOP
2705
2706 l_ue_id := c_rec.user_entity_id;
2707
2708 END LOOP;
2709
2710
2711 -- Decide upon the report Category based on the archived emp type
2712
2713 l_code := 'RM';
2714
2715 FOR c_rec IN c_chk_for_govt_employer (l_ue_id)
2716 LOOP
2717
2718 l_code := 'RG';
2719
2720 END LOOP;
2721
2722
2723 RETURN l_code;
2724
2725 END get_report_category;
2726
2727
2728 --
2729 --
2730 --
2731 -- ----------------------------------------------------------------------------
2732 -- -----------------------< get_report_category_new >-----------------------------
2733 -- ----------------------------------------------------------------------------
2734 -- Description:
2735 -- Returns the 'MT' or 'RG' depending upon the archived value for the Govt employer.
2736 -- Added for US Payroll Govt Employer W2 specific situations.
2737 --
2738 -- Pre Conditions:
2739 -- If no archived value is found, default value of 'MT' is returned. This is done to
2740 -- support the employees whose data was archived before these changes.
2741 --
2742 -- In Parameters:
2743 -- p_business_group_id
2744 -- p_effective_date
2745 --
2746 -- Post Success:
2747 -- Returns 'RG' or 'MT'
2748 --
2749 -- Added by Pradeep
2750 -- ----------------------------------------------------------------------------
2751 FUNCTION get_report_category_mt ( p_business_group_id number,
2752 p_effective_date date
2753 ) RETURN varchar2 IS
2754 --
2755 l_proc varchar2(100) := 'pay_us_mmref_reporting.get_report_category';
2756 l_code varchar2(2);
2757 l_ue_id NUMBER;
2758 --
2759 CURSOR c_get_user_entity_id IS
2760 SELECT user_entity_id
2761 FROM ff_user_entities
2762 WHERE user_entity_name = 'A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER';
2763
2764 CURSOR c_chk_for_govt_employer (p_user_entity_id NUMBER) IS
2765 SELECT 'RG'
2766 FROM DUAL
2767 WHERE EXISTS
2768 (SELECT NULL
2769 FROM pay_payroll_actions ppa,
2770 ff_archive_items fai
2771 WHERE ppa.report_type = 'YREND'
2772 AND ppa.report_qualifier = 'FED'
2773 AND ppa.report_category = 'RT'
2774 AND ppa.effective_date = p_effective_date
2775 AND ppa.business_group_id = p_business_group_id
2776 AND fai.context1 = ppa.payroll_action_id
2777 AND fai.user_entity_id = p_user_entity_id
2778 AND fai.value = 'Y'
2779 );
2780 BEGIN
2781 --
2782 hr_utility.set_location('Entering: ' || l_proc, 10);
2783 --
2784 -- Get the user entity id for A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER
2785
2786 FOR c_rec IN c_get_user_entity_id
2787 LOOP
2788 l_ue_id := c_rec.user_entity_id;
2789 END LOOP;
2790
2791 -- Decide upon the report Category based on the archived emp type
2792
2793 l_code := 'MT';
2794 FOR c_rec IN c_chk_for_govt_employer (l_ue_id)
2795 LOOP
2796 l_code := 'RG';
2797 END LOOP;
2798 RETURN l_code;
2799 END get_report_category_mt;
2800
2801
2802 FUNCTION set_application_error(p_state varchar2,
2803 p_error varchar2,
2804 p_assignment_action_id number
2805 )
2806 RETURN varchar2 IS
2807 BEGIN
2808 IF p_state = 'FED' and p_error = 'Y'
2809 THEN
2810 --raise hr_utility.hr_error;
2811 update pay_assignment_actions
2812 set SERIAL_NUMBER = 'E999999999'
2813 where assignment_action_id = p_assignment_action_id;
2814 return 'Y';
2815 ELSE
2816 return 'N';
2817 END IF;
2818 END set_application_error;
2819 --
2820 -- This function is used by formula MT_MMRF_EW_WAGE_RECORD and
2821 -- MT_MMRF_EO_WAGE_RECORD
2822 --
2823 FUNCTION get_tax_unit_info (tax_unit_id IN NUMBER -- Context when from formula
2824 , assignment_action_id IN NUMBER -- Context
2825 ,p_tax_year IN NUMBER -- Parameter
2826 ,p_federal_ein OUT NOCOPY VARCHAR2 -- Parameter
2827 ,p_tax_jd_code OUT NOCOPY VARCHAR2 --Parameter
2828 ,p_tax_unit_info1 OUT NOCOPY VARCHAR2 --Parameter
2829 ,p_tax_unit_info2 OUT NOCOPY VARCHAR2 --Parameter
2830 )
2831 RETURN varchar2
2832 IS
2833 CURSOR c_get_tax_unit_info (c_tax_unit_id NUMBER
2834 ,c_tax_year NUMBER)
2835 IS
2836 SELECT federal_ein
2837 ,tax_unit_name
2838 FROM pay_us_w2_tax_unit_v put
2839 WHERE tax_unit_id = c_tax_unit_id
2840 AND year = c_tax_year;
2841
2842 CURSOR c_get_ye_payroll_action (c_tax_unit_id NUMBER
2843 ,c_assignment_action_id NUMBER)
2844 IS
2845 SELECT payroll_action_id
2846 FROM pay_assignment_actions
2847 WHERE tax_unit_id = c_tax_unit_id
2848 AND assignment_action_id = c_assignment_action_id;
2849
2850 l_tax_unit_name VARCHAR2(2000);
2851 l_federal_EIN VARCHAR2(200);
2852 l_payroll_action_id NUMBER;
2853 l_tax_jurisdiction_code VARCHAR2(200);
2854
2855 BEGIN
2856 hr_utility.trace('In Procedure PAY_US_MMREF_REPORTING.GET_TAX_UNIT_INFO' );
2857 OPEN c_get_tax_unit_info(tax_unit_id,
2858 p_tax_year);
2859 FETCH c_get_tax_unit_info INTO l_federal_EIN,
2860 l_tax_unit_name;
2861 CLOSE c_get_tax_unit_info;
2862 p_Federal_EIN := l_federal_EIN;
2863 -- Extra set of information accessed for Multi thread Federal W-2 RW and RO record
2864 l_payroll_action_id := 0;
2865 BEGIN
2866 l_tax_jurisdiction_code := ' ';
2867 OPEN c_get_ye_payroll_action(tax_unit_id,
2868 assignment_action_id);
2869 FETCH c_get_ye_payroll_action INTO l_payroll_action_id;
2870 CLOSE c_get_ye_payroll_action;
2871 IF l_payroll_action_id <> 0 THEN
2872 l_tax_jurisdiction_code :=
2873 hr_us_w2_rep.get_w2_tax_unit_item (tax_unit_id,
2874 l_payroll_action_id,
2875 'A_LC_W2_REPORTING_RULES_ORG_TAX_JURISDICTION');
2876 END IF;
2877 p_tax_jd_code := l_tax_jurisdiction_code;
2878 END;
2879 hr_utility.trace('Federal EIN '||l_federal_EIN );
2880 hr_utility.trace('Tax Unit Name '|| l_tax_unit_name);
2881 hr_utility.trace('Tax Jurisdiction Code '|| l_tax_jurisdiction_code);
2882 return l_tax_unit_name;
2883 EXCEPTION
2884 WHEN OTHERS THEN
2885 l_tax_unit_name := ' ';
2886 p_Federal_EIN := ' ';
2887 return l_tax_unit_name;
2888 END get_tax_unit_info;
2889
2890 FUNCTION get_w2_er_arch_bal(
2891 w2_balance_name in varchar2,
2892 w2_tax_unit_id in varchar2,
2893 w2_jurisdiction_code in varchar2,
2894 w2_jurisdiction_level in varchar2,
2895 w2_year in varchar2,
2896 a1 OUT NOCOPY varchar2,
2897 a2 OUT NOCOPY varchar2,
2898 a3 OUT NOCOPY varchar2,
2899 a4 OUT NOCOPY varchar2,
2900 a5 OUT NOCOPY varchar2,
2901 a6 OUT NOCOPY varchar2,
2902 a7 OUT NOCOPY varchar2,
2903 a8 OUT NOCOPY varchar2,
2904 a9 OUT NOCOPY varchar2,
2905 a10 OUT NOCOPY varchar2,
2906 a11 OUT NOCOPY varchar2,
2907 a12 OUT NOCOPY varchar2,
2908 a13 OUT NOCOPY varchar2,
2909 a14 OUT NOCOPY varchar2,
2910 a15 OUT NOCOPY varchar2,
2911 a16 OUT NOCOPY varchar2,
2912 a17 OUT NOCOPY varchar2,
2913 a18 OUT NOCOPY varchar2,
2914 a19 OUT NOCOPY varchar2,
2915 a20 OUT NOCOPY varchar2,
2916 a21 OUT NOCOPY varchar2,
2917 a22 OUT NOCOPY varchar2,
2918 a23 OUT NOCOPY varchar2,
2919 a24 OUT NOCOPY varchar2,
2920 a25 OUT NOCOPY varchar2,
2921 a26 OUT NOCOPY varchar2,
2922 a27 OUT NOCOPY varchar2,
2923 a28 OUT NOCOPY varchar2,
2924 a29 OUT NOCOPY varchar2,
2925 a30 OUT NOCOPY varchar2,
2926 a31 OUT NOCOPY varchar2,
2927 a32 OUT NOCOPY varchar2,
2928 a33 OUT NOCOPY varchar2,
2929 a34 OUT NOCOPY varchar2,
2930 a35 OUT NOCOPY varchar2,
2931 a36 OUT NOCOPY varchar2,
2932 a37 OUT NOCOPY varchar2,
2933 a38 OUT NOCOPY varchar2,
2934 a39 OUT NOCOPY varchar2,
2935 a40 OUT NOCOPY varchar2,
2936 a41 OUT NOCOPY varchar2,
2937 a42 OUT NOCOPY varchar2,
2938 a43 OUT NOCOPY varchar2,
2939 a44 OUT NOCOPY varchar2,
2940 a45 OUT NOCOPY varchar2,
2941 a46 OUT NOCOPY varchar2,
2942 a47 OUT NOCOPY varchar2,
2943 a48 OUT NOCOPY varchar2, -- Bug 13497022
2944 a49 OUT NOCOPY varchar2 -- Bug 13497022
2945 )
2946 RETURN varchar2 IS
2947
2948 CURSOR C_EMP_count(cp_tax_unit_id number
2949 ,cp_tax_year varchar2) IS
2950 select count(*)
2951 from pay_payroll_actions ppa,
2952 pay_assignment_actions paa
2953 where ppa.report_type = 'W2'
2954 and ppa.report_qualifier = 'FED'
2955 and ppa.report_category = 'MT'
2956 and effective_date = to_date('31/12/'|| cp_tax_year, 'dd/mm/yyyy')
2957 and ppa.payroll_action_id = paa.payroll_action_id
2958 and paa.action_status = 'C'
2959 and NVL(paa.serial_number, 'S') <> 'E999999999'
2960 and paa.tax_unit_id = cp_tax_unit_id;
2961
2962 l_rw_error_count NUMBER := 0;
2963
2964 CURSOR c_rw_error_count ( cp_tax_unit_id number
2965 ,cp_tax_year varchar2) IS
2966 select count(*)
2967 from pay_payroll_actions ppa
2968 ,pay_assignment_actions paa
2969 where ppa.report_type = 'W2'
2970 and ppa.report_qualifier = 'FED'
2971 and ppa.report_category = 'MT'
2972 and ppa.effective_date = to_date('31/12/'|| cp_tax_year,'dd/mm/yyyy')
2973 and ppa.payroll_action_id = paa.payroll_action_id
2974 and NVL(paa.serial_number,'S') = 'E999999999'
2975 and paa.action_status = 'C'
2976 and paa.tax_unit_id = cp_tax_unit_id;
2977
2978 CURSOR C_ER_SUM ( P_TAX_UNIT_ID number) IS
2979 SELECT user_entity_name,
2980 DECODE(fue.user_entity_name,
2981 'A_REGULAR_EARNINGS_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
2982 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' , nvl(sum(round(to_number(value),2)),0) ,
2983 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' , nvl(sum(round(to_number(value),2)),0) ,
2984 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
2985 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' , nvl(sum(round(to_number(value),2)),0) ,
2986 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' , nvl(sum(round(to_number(value),2)),0) ,
2987 'A_FIT_SUBJ_WHABLE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2988 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2989 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2990 'A_FIT_WITHHELD_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2991 'A_SS_EE_TAXABLE_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
2992 'A_SS_EE_WITHHELD_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2993 'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2994 'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2995 'A_W2_BOX_7_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
2996 'A_EIC_ADVANCE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2997 'A_W2_DEPENDENT_CARE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2998 'A_W2_401K_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
2999 'A_W2_403B_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3000 'A_W2_408K_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3001 'A_W2_457_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3002 'A_W2_501C_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3003 'A_W2_MILITARY_HOUSING_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3004 'A_W2_NONQUAL_PLAN_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3005 'A_W2_NONQUAL_457_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3006 'A_W2_BOX_11_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3007 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3008 'A_FIT_3RD_PARTY_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3009 'A_W2_NONQUAL_STOCK_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3010 'A_W2_HSA_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3011 'A_W2_NONTAX_COMBAT_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3012 'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3013 'A_W2_BOX_8_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3014 'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3015 'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3016 'A_W2_MSA_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3017 'A_W2_408P_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3018 'A_W2_ADOPTION_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3019 'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3020 'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3021 'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3022 'A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3023 'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3024 'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3025 'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3026 'A_W2_ROTH_401K_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3027 'A_W2_ROTH_403B_PER_GRE_YTD', nvl(sum(to_number(value)),0) ,
3028 'A_SS_ER_W11_TAXABLE_PER_GRE_YTD', nvl(sum(to_number(value)),0),
3029 'A_W2_HEALTH_COVERAGE_PER_GRE_YTD', nvl(sum(to_number(value)),0), --Bug 13497022
3030 'A_W2_ROTH_457B_PER_GRE_YTD', nvl(sum(to_number(value)),0) --Bug 13497022
3031 ) val
3032 FROM ff_archive_items fai,
3033 pay_action_interlocks pai,
3034 pay_payroll_actions ppa,
3035 pay_assignment_actions paa,
3036 ff_user_entities fue
3037 where ppa.report_type = 'W2'
3041 and ppa.payroll_action_id = paa.payroll_action_id
3038 and ppa.report_qualifier = 'FED'
3039 and ppa.report_category = 'MT'
3040 and effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
3042 and paa.tax_unit_id = p_tax_unit_id
3043 and paa.action_status = 'C'
3044 and NVL(paa.serial_number, 'S') <> 'E999999999'
3045 and paa.assignment_action_id = pai.locking_action_id
3046 and fai.context1 = pai.locked_action_id
3047 and fai.user_entity_id = fue.user_entity_id
3048 and fue.user_entity_name IN
3049 (
3050 'A_REGULAR_EARNINGS_PER_GRE_YTD' ,
3051 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,
3052 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,
3053 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' ,
3054 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' ,
3055 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' ,
3056 'A_FIT_SUBJ_WHABLE_PER_GRE_YTD',
3057 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD',
3058 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD',
3059 'A_FIT_WITHHELD_PER_GRE_YTD',
3060 'A_SS_EE_TAXABLE_PER_GRE_YTD',
3061 'A_SS_EE_WITHHELD_PER_GRE_YTD',
3062 'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD',
3063 'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD',
3064 'A_W2_BOX_7_PER_GRE_YTD',
3065 'A_EIC_ADVANCE_PER_GRE_YTD',
3066 'A_W2_DEPENDENT_CARE_PER_GRE_YTD',
3067 'A_W2_401K_PER_GRE_YTD',
3068 'A_W2_403B_PER_GRE_YTD',
3069 'A_W2_408K_PER_GRE_YTD',
3070 'A_W2_457_PER_GRE_YTD',
3071 'A_W2_501C_PER_GRE_YTD',
3072 'A_W2_MILITARY_HOUSING_PER_GRE_YTD',
3073 'A_W2_NONQUAL_PLAN_PER_GRE_YTD',
3074 'A_W2_NONQUAL_457_PER_GRE_YTD',
3075 'A_W2_BOX_11_PER_GRE_YTD',
3076 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD',
3077 'A_FIT_3RD_PARTY_PER_GRE_YTD',
3078 'A_W2_NONQUAL_STOCK_PER_GRE_YTD',
3079 'A_W2_HSA_PER_GRE_YTD',
3080 'A_W2_NONTAX_COMBAT_PER_GRE_YTD',
3081 'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD',
3082 'A_W2_BOX_8_PER_GRE_YTD',
3083 /* Sum of */
3084 'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD',
3085 'A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD',
3086 'A_W2_MSA_PER_GRE_YTD',
3087 'A_W2_408P_PER_GRE_YTD',
3088 'A_W2_ADOPTION_PER_GRE_YTD',
3089 'A_W2_UNCOLL_SS_GTL_PER_GRE_YTD',
3090 'A_W2_UNCOLL_MED_GTL_PER_GRE_YTD',
3091 'A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD',
3092 'A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD',
3093 'A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD',
3094 'A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD',
3095 'A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD'
3096 , 'A_W2_ROTH_401K_PER_GRE_YTD'
3097 , 'A_W2_ROTH_403B_PER_GRE_YTD'
3098 , 'A_SS_ER_W11_TAXABLE_PER_GRE_YTD'
3099 , 'A_W2_HEALTH_COVERAGE_PER_GRE_YTD' --Bug 13497022
3100 , 'A_W2_ROTH_457B_PER_GRE_YTD' --Bug 13497022
3101 )
3102 group by fue.user_entity_name;
3103
3104
3105 CURSOR c_ter(cp_tax_unit_id number) IS
3106 SELECT
3107 fue.user_entity_name,decode(fue.user_entity_name,
3108 'A_SIT_WITHHELD_PER_JD_GRE_YTD' ,nvl(sum(to_number(value)),0) ,
3109 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3110 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3111 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0)
3112 ) val
3113 FROM ff_archive_item_contexts faic
3114 ,ff_archive_items fai
3115 ,pay_assignment_actions paa
3116 ,pay_payroll_actions ppa
3117 ,pay_action_interlocks pai
3118 ,ff_user_entities fue
3119 WHERE
3120 ppa.report_type = 'W2'
3121 and ppa.report_qualifier = 'FED'
3122 and ppa.report_category = 'MT'
3123 and ppa.effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
3124 and paa.payroll_action_id = ppa.payroll_action_id
3125 and paa.assignment_action_id = pai.locking_action_id
3126 and fai.context1 = pai.locked_action_id
3127 and context = '72-000-0000'
3128 and fai.archive_item_id = faic.archive_item_id
3129 and fai.user_entity_id = fue.user_entity_id
3130 and paa.tax_unit_id = cp_tax_unit_id
3131 and fue.user_entity_name in ('A_SIT_WITHHELD_PER_JD_GRE_YTD',
3132 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
3133 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
3134 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD')
3135 and paa.action_status = 'C'
3136 group by fue.user_entity_name;
3137 /* 7109106 */
3138 /* CURSOR c_ter(cp_tax_unit_id number) IS
3139 SELECT
3140 fue.user_entity_name,decode(fue.user_entity_name,
3141 'A_SIT_WITHHELD_PER_JD_GRE_YTD' ,nvl(sum(to_number(value)),0) ,
3142 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3143 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0) ,
3144 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD', nvl(sum(round(to_number(value),2)),0),
3145 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD', nvl(sum(round(to_number(value),2)),0)) val
3146 FROM ff_archive_item_contexts faic
3147 ,ff_archive_items fai
3148 ,pay_assignment_actions paa
3149 ,pay_payroll_actions ppa
3150 ,pay_action_interlocks pai
3151 ,ff_user_entities fue
3152 WHERE
3153 ppa.report_type = 'W2'
3154 and ppa.report_qualifier = 'FED'
3155 and ppa.report_category = 'MT'
3156 and ppa.effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
3157 and paa.payroll_action_id = ppa.payroll_action_id
3158 and paa.assignment_action_id = pai.locking_action_id
3159 and fai.context1 = pai.locked_action_id
3160 and fai.archive_item_id = faic.archive_item_id
3161 and fai.user_entity_id = fue.user_entity_id
3162 and paa.tax_unit_id = cp_tax_unit_id
3163 and case when fue.user_entity_name = 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD' then to_char(cp_tax_unit_id)
3164 else '72-000-0000' end = context
3165 and fue.user_entity_name in ('A_SIT_WITHHELD_PER_JD_GRE_YTD',
3166 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
3167 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
3168 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD' ,
3169 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD' )
3170 and paa.action_status = 'C'
3171 and exists (select 'Y'
3172 from ff_user_entities fai1, ff_archive_items fue1
3173 where fai1.user_entity_name = 'A_STATE_ABBREV'
3174 and fai1.user_entity_id = fue1.user_entity_id
3175 and fue1.value = 'PR'
3176 and fai1.user_entity_id = fue1.user_entity_id
3177 and fue1.context1 = fai.context1 )
3178 group by fue.user_entity_name; */
3179
3180 CURSOR c_ro_count ( cp_tax_unit_id number) IS
3181 select count(*)
3182 from pay_payroll_actions ppa
3183 ,pay_assignment_actions paa
3184 ,ff_archive_items fai
3185 where ppa.report_type = 'W2'
3186 and ppa.report_qualifier = 'FED'
3187 and ppa.report_category = 'MT'
3188 and effective_date = to_date('31/12/'||w2_year,'dd/mm/yyyy')
3189 and ppa.payroll_action_id = paa.payroll_action_id
3190 and paa.assignment_action_id = fai.context1
3191 and name is not null
3192 and name like 'TRANSFER_RO_TOTAL'
3193 and paa.tax_unit_id = cp_tax_unit_id
3194 group by tax_unit_id;
3195
3196 l_er_sum er_sum_table;
3197 l_date date;
3198 l_tax_unit_id varchar2(10);
3199 l_fit_with varchar2(20);
3200 l_ss_ee_taxable varchar2(20);
3201 l_total_emp number := 0;
3202 l_ro_count number := 0;
3203 l_a2 number := 0;
3204 l_a20 number := 0;
3205 l_a27 number := 0;
3206 l_a35 number := 0;
3207 l_a36 number := 0;
3208 l_a37 number := 0;
3209 l_a39 number := 0;
3210 l_a18 number := 0;
3211 l_a4 number := 0;
3212 l_a8 number := 0;
3213
3214 l_a70 number := 0;
3215 l_a71 number := 0;
3216 l_a72 number := 0;
3217 l_a73 number := 0;
3218 --l_a22 number := 0; /* 7109106 */
3219 l_direct_fed_bal_call varchar2(2) := 'N';
3220 BEGIN
3221 -- hr_utility.trace_on(NULL,'FEDW2MT');
3222 hr_utility.trace('In Procedure GET_W2_ER_ARCH_BAL' );
3223
3224 a1 := '0';
3225 a2 := '0';
3226 a3 := '0';
3227 a4 := '0';
3228 a5 := '0';
3229 a6 := '0';
3230 a7 := '0';
3231 a8 := '0';
3232 a9 := '0';
3233 a10:= '0';
3234 a11 := '0';
3235 a12 := '0';
3236 a13 := '0';
3237 a14 := '0';
3238 a15 := '0';
3239 a16 := '0';
3240 a17 := '0';
3241 a18 := '0';
3242 a19 := '0';
3243 a20 := '0';
3244 a21 := '0';
3245 a22 := '0';
3246 a23 := '0';
3247 a24 := '0';
3248 a25 := '0';
3249 a26 := '0';
3250 a27 := '0';
3251 a28 := '0';
3252 a29 := '0';
3253 a30 := '0';
3254 a31 := '0';
3255 a32 := '0';
3256 a33 := '0';
3257 a34 := '0';
3258 a35 := '0';
3259 a36 := '0';
3260 a37 := '0';
3261 a38 := '0';
3262 a39 := '0';
3263 a40 := '0';
3264 a41 := '0';
3265 a42 := '0';
3266 a43 := '0';
3267 a44 := '0';
3268 a45 := '0';
3269 a46 := '0';
3270 a47 := '0';
3271 a48 := '0'; --Bug 13497022
3272 a49 := '0'; --Bug 13497022
3273
3274 OPEN C_EMP_COUNT(to_number(W2_tax_unit_id),
3275 w2_year);
3276 FETCH C_EMP_COUNT INTO a1;
3277 IF C_EMP_COUNT%NOTFOUND THEN
3278 a1 := 0;
3279 END IF;
3280 CLOSE C_EMP_COUNT;
3281
3282 l_rw_error_count := 0;
3283 OPEN c_rw_error_count(to_number(w2_tax_unit_id),
3284 w2_year);
3285 FETCH c_rw_error_count INTO l_rw_error_count;
3286 CLOSE c_rw_error_count;
3287
3288 a1 := a1 || '.'||to_char(l_rw_error_count);
3289
3290 l_a2 := 0;
3291 l_a20 := 0;
3292 l_a27 := 0;
3293 l_a39 := 0;
3294 l_a35 := 0;
3295 l_a36 := 0;
3296 l_a37 := 0;
3297
3298 l_direct_fed_bal_call := nvl(fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES'),'N'); /*Added for Bug#13326069*/
3299
3300 /*Added for Bug#13614766*/
3301 IF l_direct_fed_bal_call = 'Y' THEN
3302
3303 l_direct_fed_bal_call := hr_us_ff_udfs.direct_fed_data_archived(w2_year);
3304
3305 END IF;
3306
3307 /*For Bug#13326069, Made changes to calculate Federal Gross Wages based on Direct US Federal Balances Profile Option*/
3308
3309 FOR I IN C_ER_SUM(to_number(W2_TAX_UNIT_ID)) LOOP
3310
3311 if I.user_entity_name = 'A_REGULAR_EARNINGS_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
3312 l_a2 := l_a2 + i.val;
3313 ELSIF I.user_entity_name = 'A_SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
3314 l_a2 := l_a2 + i.val;
3315 ELSIF I.user_entity_name = 'A_SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
3316 l_a2 := l_a2 + i.val;
3317 ELSIF I.user_entity_name = 'A_PRE_TAX_DEDUCTIONS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
3318 l_a2 := l_a2 + i.val;
3319 ELSIF I.user_entity_name = 'A_FIT_NON_W2_PRE_TAX_DEDNS_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
3320 l_a2 := l_a2 + i.val;
3321 ELSIF I.user_entity_name = 'A_PRE_TAX_DEDUCTIONS_PER_GRE_YTD' AND l_direct_fed_bal_call='N' THEN
3322 l_a2 := l_a2 - i.val;
3323 ELSIF I.user_entity_name = 'A_FIT_SUBJ_WHABLE_PER_GRE_YTD' AND l_direct_fed_bal_call='Y' THEN
3324 l_a2 := l_a2 + i.val;
3325 ELSIF I.user_entity_name = 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD' AND l_direct_fed_bal_call='Y' THEN
3326 l_a2 := l_a2 + i.val;
3327 ELSIF I.user_entity_name = 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD' AND l_direct_fed_bal_call='Y' THEN
3328 l_a2 := l_a2 - i.val;
3329 ELSIF I.user_entity_name = 'A_FIT_WITHHELD_PER_GRE_YTD' THEN
3330 a3 := to_char(trunc(i.val * 100));
3331 ELSIF i.user_entity_name = 'A_SS_EE_TAXABLE_PER_GRE_YTD' THEN
3332 l_a4 := i.val;
3333 ELSIF i.user_entity_name ='A_SS_EE_WITHHELD_PER_GRE_YTD' THEN
3334 a5 := to_char(trunc(i.val * 100));
3335 ELSIF i.user_entity_name = 'A_MEDICARE_EE_TAXABLE_PER_GRE_YTD' THEN
3336 a6 := to_char(trunc(i.val * 100));
3337 ELSIF i.user_entity_name = 'A_MEDICARE_EE_WITHHELD_PER_GRE_YTD' THEN
3338 a7 := to_char(trunc(i.val * 100));
3339 ELSIF i.user_entity_name = 'A_W2_BOX_7_PER_GRE_YTD' THEN
3340 l_a8 := i.val;
3341 ELSIF i.user_entity_name = 'A_EIC_ADVANCE_PER_GRE_YTD' THEN
3342 a9 := to_char(trunc(i.val * 100));
3343 ELSIF i.user_entity_name = 'A_W2_DEPENDENT_CARE_PER_GRE_YTD' THEN
3344 a10 := to_char(trunc(i.val * 100));
3345 ELSIF i.user_entity_name = 'A_W2_401K_PER_GRE_YTD' THEN
3346 a11 := to_char(trunc(i.val * 100));
3347 ELSIF i.user_entity_name = 'A_W2_403B_PER_GRE_YTD' THEN
3348 a12 := to_char(trunc(i.val * 100));
3349 ELSIF i.user_entity_name = 'A_W2_408K_PER_GRE_YTD' THEN
3350 a13 := to_char(trunc(i.val * 100));
3351 ELSIF i.user_entity_name = 'A_W2_457_PER_GRE_YTD' THEN
3352 a14 := to_char(trunc(i.val * 100));
3353 ELSIF i.user_entity_name = 'A_W2_501C_PER_GRE_YTD' THEN
3354 a15 := to_char(trunc(i.val * 100));
3355 ELSIF i.user_entity_name = 'A_W2_MILITARY_HOUSING_PER_GRE_YTD' THEN
3356 a16 := to_char(trunc(i.val * 100));
3357 ELSIF i.user_entity_name = 'A_W2_NONQUAL_457_PER_GRE_YTD' THEN
3358 a17:= to_char(trunc(i.val * 100));
3359 ELSIF i.user_entity_name = 'A_W2_BOX_11_PER_GRE_YTD' THEN
3360 a18 := to_char(trunc(i.val * 100));
3361 ELSIF i.user_entity_name = 'A_W2_HSA_PER_GRE_YTD' THEN
3362 a19 := to_char(trunc(i.val * 100));
3363 ELSIF i.user_entity_name = 'A_W2_NONQUAL_PLAN_PER_GRE_YTD' THEN
3364 l_a20 := i.val;
3365 ELSIF i.user_entity_name = 'A_W2_NONTAX_COMBAT_PER_GRE_YTD' THEN
3366 a21 := to_char(trunc(i.val * 100));
3367 ELSIF i.user_entity_name = 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD' THEN
3368 a22:= to_char(trunc(i.val * 100));
3369 ELSIF i.user_entity_name = 'A_FIT_3RD_PARTY_PER_GRE_YTD' THEN
3370 a23 := to_char(trunc(i.val * 100));
3371 ELSIF i.user_entity_name = 'A_W2_NONQUAL_STOCK_PER_GRE_YTD' THEN
3372 a24 := to_char(trunc(i.val * 100));
3373 ELSIF i.user_entity_name = 'A_W2_NONQUAL_DEF_COMP_PER_GRE_YTD' THEN
3374 a25 := to_char(trunc(i.val * 100));
3375 ELSIF i.user_entity_name ='A_W2_BOX_8_PER_GRE_YTD' THEN
3376 a26 := to_char(trunc(i.val * 100));
3377 ELSIF i.user_entity_name = 'A_W2_UNCOLL_SS_TAX_TIPS_PER_GRE_YTD' THEN
3378 l_a27 := i.val;
3379 ELSIF i.user_entity_name ='A_W2_UNCOLL_MED_TIPS_PER_GRE_YTD' THEN
3380 l_a27 := l_a27 + i.val;
3381 ELSIF i.user_entity_name ='A_W2_MSA_PER_GRE_YTD' THEN
3382 a28 := to_char(trunc(i.val * 100));
3383 ELSIF i.user_entity_name ='A_W2_408P_PER_GRE_YTD' THEN
3384 a29 := to_char(trunc(i.val * 100));
3385 ELSIF i.user_entity_name ='A_W2_ADOPTION_PER_GRE_YTD' THEN
3386 a30 := to_char(trunc(i.val * 100));
3387 ELSIF i.user_entity_name ='A_W2_UNCOLL_SS_GTL_PER_GRE_YTD' THEN
3388 a31 := to_char(trunc(i.val * 100));
3389 ELSIF i.user_entity_name ='A_W2_UNCOLL_MED_GTL_PER_GRE_YTD' THEN
3390 a32 := to_char(trunc(i.val * 100));
3391 ELSIF i.user_entity_name ='A_W2_409A_NONQUAL_INCOME_PER_GRE_YTD' THEN
3392 a33 := to_char(trunc(i.val * 100));
3393 ELSIF i.user_entity_name ='A_TERRITORY_RETIRE_CONTRIB_PER_GRE_YTD' THEN
3394 a34 := to_char(trunc(i.val * 100));
3395 ELSIF i.user_entity_name ='A_TERRITORY_TAXABLE_ALLOW_PER_GRE_YTD' THEN
3396 l_a35 := i.val;
3397 ELSIF i.user_entity_name ='A_TERRITORY_TAXABLE_COMM_PER_GRE_YTD' THEN
3398 l_a36 := i.val;
3399 ELSIF i.user_entity_name ='A_TERRITORY_TAXABLE_TIPS_PER_GRE_YTD' THEN
3400 l_a37 := i.val;
3401 ELSIF i.user_entity_name ='A_SS_ER_W11_TAXABLE_PER_GRE_YTD' THEN
3402 a45 := to_char(trunc(i.val * 100));
3403 ELSIF i.user_entity_name ='A_W2_ROTH_401K_PER_GRE_YTD' THEN
3404 a46 := to_char(trunc(i.val * 100));
3405 ELSIF i.user_entity_name ='A_W2_ROTH_403B_PER_GRE_YTD' THEN
3406 a47 := to_char(trunc(i.val * 100));
3407 ELSIF i.user_entity_name ='A_W2_HEALTH_COVERAGE_PER_GRE_YTD' THEN
3408 a48 := to_char(trunc(i.val * 100)); -- Bug 13497022
3409 ELSIF i.user_entity_name ='A_W2_ROTH_457B_PER_GRE_YTD' THEN
3410 a49 := to_char(trunc(i.val * 100)); -- Bug 13497022
3411
3412 END IF;
3413
3414 IF i.user_entity_name = 'A_W2_NONQUAL_457_PER_GRE_YTD' THEN
3415 l_a70 := i.val;
3416 END IF;
3417
3418 hr_utility.trace('UE Name : '|| i.user_entity_name || ' SumValue '|| to_char( i.val));
3419 END LOOP;
3420 /*
3421 a2 := to_char(round(l_a2, 2) * 100);
3422 a20 := to_char(round(l_a20, 2) * 100);
3423 a27 := to_char(round(l_a27, 2) * 100);
3424 a35 := to_char(round(l_a35, 2) * 100);
3425 a36 := to_char(round(l_a36, 2) * 100);
3426 a37 := to_char(round(l_a37, 2) * 100);
3427 */
3428 l_a20 := l_a20 - l_a70;
3429
3430 a2 := to_char(trunc(l_a2 * 100));
3431 a20 := to_char(trunc(l_a20 * 100));
3432 a27 := to_char(trunc(l_a27 * 100));
3433 a35 := to_char(trunc(l_a35 * 100));
3434 a36 := to_char(trunc(l_a36 * 100));
3435 a37 := to_char(trunc(l_a37 * 100));
3436 l_a4 := l_a4 - l_a8;
3437 a4 := to_char(trunc(l_a4 * 100));
3438 a8 := to_char(trunc(l_a8 * 100));
3439
3440 OPEN c_ro_count(to_number(W2_TAX_UNIT_ID));
3441 FETCH c_ro_count INTO l_ro_count;
3442 CLOSE c_ro_count;
3443
3444 a38 := to_char(nvl(l_ro_count,0));
3445
3446 IF l_ro_count > 0 THEN
3447 FOR J IN c_ter(to_number(W2_TAX_UNIT_ID)) LOOP
3448 if J.user_entity_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' THEN
3449 l_a71 := J.val;
3450 ELSIF J.user_entity_name = 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD' THEN
3451 l_a72 := J.val;
3452 ELSIF J.user_entity_name = 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD' THEN
3453 l_a73 := J.val;
3454 /* ELSIF J.user_entity_name = 'A_W2_GROUP_TERM_LIFE_PER_GRE_YTD' THEN
3455 l_a22 := to_char(trunc(J.val * 100 )); */
3456 /* 7109106 */
3457 ELSIF J.user_entity_name = 'A_SIT_WITHHELD_PER_JD_GRE_YTD' THEN
3458 a40 := to_char(trunc(J.val * 100));
3459 END IF;
3460 END LOOP;
3461 END IF ;
3462
3463 l_a39 := (l_a71 + l_a72) - l_a73;
3464 -- a22 := a22 - l_a22 ; /* 7109106 */
3465 a39 := to_char(round(l_a39, 2) * 100 ) ;
3466 a41 := to_char(trunc( (l_a39 - l_a37 - l_a35 - l_a36 ) * 100));
3467
3468 hr_utility.trace('Value of a1 : '||a1 );
3469 hr_utility.trace('Value of a2 : '||a2);
3470 hr_utility.trace('Value of a3 : '||a3 );
3471 hr_utility.trace('Value of a4 : '||a4 );
3472 hr_utility.trace('Value of a5 : '||a5 );
3473 hr_utility.trace('Value of a6 : '||a6 );
3474 hr_utility.trace('Value of a7 : '||a7 );
3475 hr_utility.trace('Value of a8 : '||a8 );
3476 hr_utility.trace('Value of a9 : '||a9 );
3477 hr_utility.trace('Value of a10 : '||a10 );
3478 hr_utility.trace('Value of a11 : '||a11 );
3479 hr_utility.trace('Value of a12 : '||a12 );
3480 hr_utility.trace('Value of a13 : '||a13 );
3481 hr_utility.trace('Value of a14 : '||a14 );
3482 hr_utility.trace('Value of a15 : '||a15 );
3483 hr_utility.trace('Value of a16 : '||a16 );
3484 hr_utility.trace('Value of a17 : '||a17 );
3485 hr_utility.trace('Value of a18 : '||a18 );
3486 hr_utility.trace('Value of a19 : '||a19 );
3487 hr_utility.trace('Value of a20 : '||a20 );
3488 hr_utility.trace('Value of a21 : '||a21 );
3489 hr_utility.trace('Value of a22 : '||a22 );
3490 hr_utility.trace('Value of a23 : '||a23 );
3491 hr_utility.trace('Value of a24 : '||a24 );
3492 hr_utility.trace('Value of a25 : '||a25 );
3493 hr_utility.trace('Value of a26 : '||a26 );
3494 hr_utility.trace('Value of a27 : '||a27 );
3495 hr_utility.trace('Value of a28 : '||a28 );
3496 hr_utility.trace('Value of a29 : '||a29 );
3497 hr_utility.trace('Value of a30 : '||a30 );
3498 hr_utility.trace('Value of a31 : '||a31 );
3499 hr_utility.trace('Value of a32 : '||a32 );
3500 hr_utility.trace('Value of a33 : '||a33 );
3501 hr_utility.trace('Value of a34 : '||a34 );
3502 hr_utility.trace('Value of a35 : '||a35 );
3503 hr_utility.trace('Value of a36 : '||a36 );
3504 hr_utility.trace('Value of a37 : '||a37 );
3505 hr_utility.trace('Value of a38 : '||a38 );
3506 hr_utility.trace('Value of a39 : '||a39 );
3507 hr_utility.trace('Value of a40 : '||a40 );
3508 hr_utility.trace('Value of a41 : '||a41 );
3509 hr_utility.trace('Value of a42 : '||a42 );
3510 hr_utility.trace('Value of a43 : '||a43 );
3511 hr_utility.trace('Value of a44 : '||a44 );
3512 hr_utility.trace('Value of a45 : '||a45 );
3513 hr_utility.trace('Value of a46 : '||a46 );
3514 hr_utility.trace('Value of a47 : '||a47 );
3515 hr_utility.trace('Value of a48 : '||a48 ); --Bug 13497022
3516 hr_utility.trace('Value of a49 : '||a49 ); --Bug 13497022
3517 hr_utility.trace('Value of a71 : '||to_char(l_a71));
3518 hr_utility.trace('Value of a72 : '||to_char(l_a72));
3519 hr_utility.trace('Value of a73 : '||to_char(l_a73));
3520
3521 return '0' ;
3522 END get_w2_er_arch_bal;
3523
3524 --BEGIN
3525 -- hr_utility.trace_on(NULL,'FEDW2MT');
3526 END pay_us_mmref_reporting;
[Home] [Help]