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