1 PACKAGE BODY pay_us_mmref_local AS
2 /* $Header: pyusmmle.pkb 120.3 2006/12/07 01:24:11 ppanda noship $ */
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.9 2296797 Added legislation Code
30 REM ppanda 04-Nov-2002 115.10 changes made to generalise assignment creation
31 REM for any given locality
32 REM asasthan 15-Nov-2002 115.2 2586041 Added Index hint
33 REM on pay_us_state_tax_info_f
34 REM ppanda 02-Dec-02 115.3 Nocopy hint added to OUT and IN OUT parameters
35 REM ppanda 12-Dec-03 115.6 3313007 LOCAL W2 MAG FOR CCA INCLUDES STATE RW RECORD
36 REM ppanda 09-FEB-03 115.9 Federal/State W-2 Mag tapes should issue warning if GRE
37 REM is not archived. This issue was fixed for Federal and State
38 REM W-2. However This is causing a performance issue on Local W-2
39 REM so to be consistent on W-2 Magnetic Media reporting and
40 REM performance reason code changed.
41 REM asasthan 30-JUL02004 115.10 3319904 cursors c_gre_fed, c_gre_state
42 REM were not being used. removed
43 REM the cursors and removed +0
44 REM from c_get_gre.
45 REM pragupta 22-JUL-2005 115.11 4344862 cursor c_person_in_state removed. It was a redundant
46 REM cursor and was not used any where in the package.
47 REM djoshi 14-feb-2005 115.12 5009863 Changed state and federal Cursor
48 REM to join paa.serial_number
49 REM ppanda 06-DEC-2006 115.13 4490252 Commented out the code for checking highly compensated
50 REM person in the create_assignment_act procedure.
51 REM
52 REM ========================================================================
53 REM
54
55 -----------------------------------------------------------------------------
56 -- Name : bal_db_item
57 -- Purpose : Given the name of a balance DB item as would be seen in a
58 -- fast formula it returns the defined_balance_id of the
59 -- balance it represents.
60 -- Arguments
61 -- INPUT : p_db_item_name
62 -- returns : l_defined_balance_id
63 -- Notes
64 -- A defined_balance_id is required by the PLSQL balance function.
65 -----------------------------------------------------------------------------
66 */
67 FUNCTION bal_db_item
68 ( p_db_item_name VARCHAR2
69 ) RETURN NUMBER IS
70 -- Get the defined_balance_id for the specified balance DB item.
71 CURSOR csr_defined_balance IS
72 SELECT TO_NUMBER(UE.creator_id)
73 FROM ff_database_items DI,
74 ff_user_entities UE
75 WHERE DI.user_name = p_db_item_name
76 AND UE.user_entity_id = DI.user_entity_id
77 AND UE.creator_type = 'B'
78 AND UE.legislation_code = 'US'; /* Bug:2296797 */
79 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
80 BEGIN
81 hr_utility.set_location
82 ('pay_us_mmref_reporting.bal_db_item - opening cursor', 10);
83 -- Open the cursor
84 OPEN csr_defined_balance;
85 -- Fetch the value
86 FETCH csr_defined_balance
87 INTO l_defined_balance_id;
88 IF csr_defined_balance%NOTFOUND THEN
89 CLOSE csr_defined_balance;
90 hr_utility.set_location
91 ('pay_us_mmref_reporting.bal_db_item - no rows found from cursor', 20);
92 hr_utility.raise_error;
93 ELSE
94 hr_utility.set_location
95 ('pay_us_mmref_reporting.bal_db_item - fetched from cursor', 30);
96 CLOSE csr_defined_balance;
97 END IF;
98 /* Return the value to the call */
99 RETURN (l_defined_balance_id);
100 END bal_db_item;
101 -----------------------------------------------------------------------------
102 -- Name : :get_report_parameters
103 --
104 -- Purpose
105 -- The procedure gets the 'parameter' for which the report is being
106 -- run i.e., the period, state and business organization.
107 --
108 -- Arguments
109 -- p_year_start Start Date of the period for which the report
110 -- has been requested
111 -- p_year_end End date of the period
112 -- p_business_group_id Business group for which the report is being run
113 -- p_state_abbrev Two digit state abbreviation (or 'FED' for federal
114 -- report)
115 -- p_state_code State code (NULL for federal)
116 -- p_report_type Type of report being run (FEDW2, STW2, 1099R ...)
117 --
118 -- p_locality_code Locality Code like DAYTO, PHILA, STLOU and RTCCA
119 --
120 -- Notes
121 ----------------------------------------------------------------------------
122
123 PROCEDURE get_report_parameters
124 ( p_pactid IN NUMBER,
125 p_year_start IN OUT nocopy DATE,
126 p_year_end IN OUT nocopy DATE,
127 p_state_abbrev IN OUT nocopy VARCHAR2,
128 p_state_code IN OUT nocopy VARCHAR2,
129 p_report_type IN OUT nocopy VARCHAR2,
130 p_business_group_id IN OUT nocopy NUMBER,
131 p_locality_code IN OUT nocopy VARCHAR2
132 ) IS
133 BEGIN
134 hr_utility.set_location
135 ('pay_us_mmref_reporting.get_report_parameters', 10);
136 SELECT ppa.start_date,
137 ppa.effective_date,
138 ppa.business_group_id,
139 --CPE
140 --ppa.report_qualifier,
141 --CPE
142 substr(ppa.report_qualifier,1,2),
143 ppa.report_type,
144 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
145 'LC')
146 -- substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,
147 -- 'LOCALITY_CODE=') + length('LOCALITY_CODE='))
148 INTO p_year_start,
149 p_year_end,
150 p_business_group_id,
151 p_state_abbrev,
152 p_report_type,
153 p_locality_code
154 FROM pay_payroll_actions ppa
155 WHERE payroll_action_id = p_pactid;
156 IF p_state_abbrev <> 'FED' THEN
157 SELECT state_code
158 INTO p_state_code
159 FROM pay_us_states
160 WHERE state_abbrev = p_state_abbrev;
161 hr_utility.set_location
162 ('pay_us_mmref_reporting.get_report_parameters', 20);
163 ELSE
164 p_state_code := '';
165 hr_utility.set_location
166 ('pay_us_mmref_reporting.get_report_parameters', 30);
167 END IF;
168 IF p_state_abbrev = 'FED' AND p_report_type = 'W2' THEN
169 p_report_type := 'FEDW2';
170 ELSIF p_report_type = 'W2' THEN
171 p_report_type := 'STW2';
172 END IF;
173 hr_utility.set_location
174 ('pay_us_mmref_reporting.get_report_parameters', 40);
175 END get_report_parameters;
176 -------------------------------------------------------------------------
177 -- Name : get_balance_value
178 --
179 --Purpose
180 -- Get the value of the specified balance item
181 --Arguments
182 -- p_balance_name Name of the balnce
183 -- p_tax_unit_id GRE name for the context
184 -- p_state_code State for context
185 -- p_assignment_id Assignment for whom the balance is to be
186 -- retrieved
187 -- p_effective_date effective_date
188 --Note
189 -- This procedure set is a wrapper for setting the GRE/Jurisdiction context
190 -- needed by the pay_balance_pkg.get_value to get the actual balance
191 -------------------------------------------------------------------------
192 FUNCTION get_balance_value (
193 p_balance_name VARCHAR2,
194 p_tax_unit_id NUMBER,
195 p_state_abbrev VARCHAR2,
196 p_assignment_id NUMBER,
197 p_effective_date DATE
198 ) RETURN NUMBER IS
199 l_jurisdiction_code VARCHAR2(20);
200 BEGIN
201 hr_utility.set_location
202 ('pay_us_mmref_reporting.get_balance_value', 10);
203 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
204 IF p_state_abbrev <> 'FED' THEN
205 SELECT jurisdiction_code
206 INTO l_jurisdiction_code
207 FROM pay_state_rules
208 WHERE state_code = p_state_abbrev;
209 hr_utility.set_location
210 ('pay_us_mmref_reporting.get_balance_value', 15);
211 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction_code);
212 END IF;
213 hr_utility.trace(p_balance_name);
214 hr_utility.trace('Context');
215 hr_utility.trace('Tax Unit Id: '|| p_tax_unit_id);
216 hr_utility.trace('Jurisdiction: '|| l_jurisdiction_code);
217 hr_utility.set_location
218 ('pay_us_mmref_reporting.get_balance_value', 20);
219 RETURN pay_balance_pkg.get_value(bal_db_item(p_balance_name),
220 p_assignment_id, p_effective_date);
221 END get_balance_value;
222 --------------------------------------------------------------------------
223 --Name
224 -- preprocess_check
225 --Purpose
226 -- This function checks if the year end preprocessor has been run for the
227 -- GREs involved in the W2 report. It also checks if any of the assignments
228 -- have errored out or have been marked for retry.
229 --
230 --Arguments
231 -- p_pactid payroll_action_id for the report
232 -- p_year_start start date of the period for which the report
233 -- has been requested
234 -- p_year_end end date of the period
235 -- p_business_group_id business group for which the report is being run
236 -- p_state_abbrev two digit state abbreviation (or 'FED' for federal
237 -- report)
238 -- p_state_code state code (NULL for federal)
239 -- p_report_type type of report being run (W2, 1099R ...)
240 --
241 --Notes
242 -- The check for 'errored'/'marked for retry'assignments can be bypassed by
243 -- setting the parameter 'FORCE_MAG_REPORT' to 'E' and 'M' respectively. In
244 -- such cases the report will ignore the assignments in question.
245 -----------------------------------------------------------------------------
246 FUNCTION preprocess_check
247 (
248 p_pactid NUMBER,
249 p_year_start DATE,
250 p_year_end DATE,
251 p_business_group_id NUMBER,
252 p_state_abbrev VARCHAR2,
253 p_state_code VARCHAR2,
254 p_report_type VARCHAR2
255 )
256 RETURN BOOLEAN IS
257 -- Cursor to get all the GREs belonging to the given business group
258 CURSOR c_get_gre IS
259 SELECT hou.organization_id gre
260 FROM hr_organization_information hoi,
261 hr_all_organization_units hou
262 WHERE hou.business_group_id = p_business_group_id AND
263 hoi.organization_id = hou.organization_id AND
264 hoi.org_information_context = 'CLASS' AND
265 hoi.org_information1 = 'HR_LEGAL' AND
266 NOT EXISTS (
267 SELECT 'Y'
268 FROM hr_organization_information
269 WHERE organization_id = hou.organization_id
270 AND org_information_context = '1099R Magnetic Report Rules');
271
272 -- Check if the GRE needs to be archived.
273 -- Cursor to fetch people in a given GRE with earnings in the given state to
274
275 CURSOR c_tax_ein
276 IS
277 SELECT user_entity_id from ff_user_entities
278 WHERE user_entity_name = 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER';
279
280
281
282 -- Cursor to fetch people from the GRE belonging to the business group
283
284
285 -- Cursor to get payroll_action_ids of the pre-process for the given GRE.
286 -- This will also serve as a check to make sure that all GREs have been
287 -- archived
288 CURSOR c_gre_payroll_action (cp_gre NUMBER)
289 IS
290 SELECT payroll_action_id
291 FROM pay_payroll_actions
292 WHERE report_type = 'YREND'
293 AND effective_date = p_year_end
294 AND start_date = p_year_start
295 AND business_group_id+0 = p_business_group_id
296 AND SUBSTR(legislative_parameters,
297 INSTR(legislative_parameters, 'TRANSFER_GRE=') +
298 LENGTH('TRANSFER_GRE=')) = TO_CHAR(cp_gre)
299 -- ADDED FOLLOWING CHECK CONDITION
300 AND action_status = 'C';
301
302 --Cursor for checking if any of the the archiver has errored for
303 --any of the assignments for federal W
304
305 CURSOR c_arch_errored_asg (cp_payroll_action_id NUMBER) IS
306 SELECT '1'
307 FROM dual
308 WHERE EXISTS (SELECT '1'
309 FROM pay_assignment_actions paa
310 WHERE paa.payroll_action_id = cp_payroll_action_id
311 AND paa.action_status = 'E'
312 )
313 AND NOT EXISTS ( SELECT '1'
314 FROM pay_action_parameters
315 WHERE parameter_name = 'FORCE_MAG_REPORT'
316 AND INSTR(parameter_value, 'E') > 0
317 );
318 --Cursor for checking if any of the assignments have been marked for retry
319
320 CURSOR c_arch_retry_pending (cp_payroll_action_id NUMBER) IS
321 SELECT '1'
322 FROM dual
323 WHERE EXISTS (SELECT '1'
324 FROM pay_assignment_actions paa
325 WHERE paa.payroll_action_id = cp_payroll_action_id
326 AND paa.action_status = 'M')
327 AND NOT EXISTS (SELECT '1'
328 FROM pay_action_parameters
329 WHERE parameter_name = 'FORCE_MAG_REPORT'
330 AND INSTR(parameter_value, 'R') > 0
331 );
332
333 /* cursor to get user_entity_id */
334 CURSOR c_user_entity_id_of_bal
335 IS
336 SELECT user_entity_id
337 FROM ff_database_items fdi
338 WHERE user_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' ;
339
340
341 /* cursor to get_context_of_tax_unit_id */
342 CURSOR c_context_tax_unit_id
343 IS
344 SELECT context_id
345 FROM ff_contexts
346 WHERE context_name = 'TAX_UNIT_ID';
347
348 /* cursor to get context of jurisdiction */
349 CURSOR c_context_jurisdiction
350 IS
351 SELECT context_id
352 FROM ff_contexts
353 WHERE context_name = 'JURISDICTION_CODE';
354
355 /* cursor to check if the state tax Rules have been added or Not. */
356 CURSOR c_chk_archive_state_code(cp_tax_unit_id number,cp_payroll_action_id number)
357 IS
358 SELECT 'Y'
359 FROM ff_archive_item_contexts con3,
360 ff_archive_item_contexts con2,
361 ff_contexts fc3,
362 ff_contexts fc2,
363 ff_archive_items target,
364 ff_database_items fdi
365 WHERE target.context1 = to_char(cp_payroll_action_id)
366 /* context of payroll_action_id */
367 AND fdi.user_name = 'A_FIPS_CODE_JD'
368 AND target.user_entity_id = fdi.user_entity_id
369 AND fc2.context_name = 'TAX_UNIT_ID'
370 AND con2.archive_item_id = target.archive_item_id
371 AND con2.context_id = fc2.context_id
372 AND ltrim(rtrim(con2.context)) = to_char(cp_tax_unit_id)
373 AND fc3.context_name = 'JURISDICTION_CODE'
374 AND con3.archive_item_id = target.archive_item_id
375 AND con3.context_id = fc3.context_id
376 AND substr(ltrim(rtrim(con3.context)),1,2) = p_state_code;
377 /* jurisdiction code of the state */
378
379 /* cursor to get if transmitter has been been archived */
380
381 CURSOR c_transmitter IS
382 SELECT SUBSTR(legislative_parameters,INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
383 + LENGTH('TRANSFER_TRANS_LEGAL_CO_ID='),
384 (INSTR(legislative_parameters, 'TRANSFER_DATE=')
385 - INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
386 - LENGTH('TRANSFER_TRANS_LEGAL_CO_ID=')-1 ))
387 FROM pay_payroll_actions
388 WHERE report_type = 'W2'
389 AND effective_date = p_year_end
390 --CPE
391 --AND report_qualifier = p_state_abbrev
392 --CPE
393 AND substr(report_qualifier,1,2) = p_state_abbrev
394 AND business_group_id = p_business_group_id
395 --AND report_category = 'RL' ;
396 AND report_category = 'LC_'||
397 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(payroll_action_id,
398 'LC');
399 --substr(legislative_parameters,instr(legislative_parameters,'LOCALITY_CODE=')
400 -- + length('LOCALITY_CODE='));
401
402 /* LoCal variables used for processing */
403 message_text VARCHAR2(32000);
404 message_preprocess varchar2(2000);
405 l_gre NUMBER(15);
406 l_person NUMBER(15);
407 l_assignment NUMBER(15);
408 l_asg_effective_dt DATE;
409 l_payroll_action_id NUMBER(15);
410 l_asg_errored VARCHAR2(1);
411 l_asg_retry_pend VARCHAR2(1);
412 l_balance_exists NUMBER(1) := 0;
413 l_no_of_gres_picked NUMBER(15) := 0;
414 l_transmitter NUMBER(15) :=0;
415 l_state_tax_rules_exist CHAR(1);
416 l_person_in_state CHAR(1);
417 l_user_entity_id number;
418 l_context_jursidiction number;
419 l_context_tax_unit_id number; --ff_contexts.context_id%type;
420 l_package_error_status char(1) := 'N';
421 l_ein number;
422 l_ein_result varchar2(30);
423 BEGIN
424
425 /* One Time Setting of Pre-Process Message */
426 message_preprocess := 'Pre-Process check';
427
428 /* GET the Employer EIN */
429 OPEN c_tax_ein;
430 FETCH c_tax_ein INTO l_ein;
431 IF c_tax_ein%NOTFOUND THEN
432 CLOSE c_tax_ein;
433 l_package_error_status := 'Y';
434 hr_utility.trace('A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER missing ');
435 message_text := 'EIN ID missing ';
436 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
437 pay_core_utils.push_token('record_name',message_preprocess);
438 pay_core_utils.push_token('description',message_text);
439 hr_utility.raise_error;
440 ELSE
441 CLOSE c_tax_ein;
442 END IF;
443
444
445 /* GET the context and user entity id */
446 OPEN c_user_entity_id_of_bal;
447 FETCH c_user_entity_id_of_bal INTO l_user_entity_id;
448 IF c_user_entity_id_of_bal%NOTFOUND THEN
449 CLOSE c_user_entity_id_of_bal;
450 l_package_error_status := 'Y';
451 /* message to user - Database item missing */
452 hr_utility.trace('Database item for balacne missing ');
453 message_text := '-Database item missing ';
454 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
455 pay_core_utils.push_token('record_name',message_preprocess);
456 pay_core_utils.push_token('description',message_text);
457 hr_utility.raise_error;
458 ELSE
459 CLOSE c_user_entity_id_of_bal;
460 END IF;
461
462 OPEN c_context_tax_unit_id;
463 FETCH c_context_tax_unit_id INTO l_context_tax_unit_id;
464 IF c_context_tax_unit_id%NOTFOUND THEN
465 CLOSE c_context_tax_unit_id;
466 /* message to user -- unable to find the context_id for tax_unit_id */
467 message_text := 'Context_id value for tax_unit_id missing';
468 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
469 pay_core_utils.push_token('record_name',message_preprocess);
470 pay_core_utils.push_token('description',message_text);
471 hr_utility.raise_error;
472 ELSE
473 CLOSE c_context_tax_unit_id;
474 END IF;
475
476 OPEN c_context_jurisdiction;
477 FETCH c_context_jurisdiction INTO l_context_jursidiction;
478 IF c_context_jurisdiction%NOTFOUND THEN
479 CLOSE c_context_jurisdiction;
480 /* message to User -- Unable to find to context_id for jurisdiction */
481
482 message_text := 'Context_id value for jurisdiction missing';
483 hr_utility.trace('Contxt_id value for jurisdction_id missing');
484 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
485 pay_core_utils.push_token('record_name',message_preprocess);
486 pay_core_utils.push_token('description',message_text);
487 hr_utility.raise_error;
488 ELSE
489 CLOSE c_context_jurisdiction;
490 END IF;
491 /* Get the Tranmitter id of the Current Mag. W2. and check if it has
492 archived or Not for the year End process
493 Get the transmitter for the Mag. W2. Process. */
494 OPEN c_transmitter;
495 FETCH c_transmitter INTO l_transmitter;
496 IF c_transmitter%NOTFOUND THEN
497 CLOSE c_transmitter;
498 /* message to user -- transmitter has not been defined for the gre */
499 message_text := 'Transmitter Not denfined';
500 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
501 pay_core_utils.push_token('record_name',message_preprocess);
502 pay_core_utils.push_token('description',message_text);
503 hr_utility.raise_error;
504 ELSE
505 CLOSE c_transmitter;
506 END IF;
507
508 hr_utility.trace('Transmetter Setting is ' || to_char(l_transmitter));
509 hr_utility.trace('Start date ' || to_char(p_year_start));
510 hr_utility.trace('End date ' || to_char(p_year_end));
511 hr_utility.trace('Bussiness_group id ' || to_char(p_business_group_id));
512
513
514 /* Check if Archiver has been run for Transmitter */
515 OPEN c_gre_payroll_action (l_transmitter);
516 FETCH c_gre_payroll_action INTO l_payroll_action_id;
517
518 IF c_gre_payroll_action%NOTFOUND THEN
519 hr_utility.trace('Transmitter not Archvied ');
520 CLOSE c_gre_payroll_action;
521 /* message to user -- Transmitter has not been archived */
522 message_text := 'Transmitter not Archived';
523 hr_utility.trace('Transmitter has not been archived');
524 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
525 pay_core_utils.push_token('record_name',message_preprocess);
526 pay_core_utils.push_token('description',message_text);
527 -- hr_utility.raise_error;
528 END IF;
529 CLOSE c_gre_payroll_action;
530
531 /* end of Transmitter Checking */
532
533 hr_utility.set_location('pay_us_mmref_reporting.preprocess_check', 10);
534
535 FOR gre_rec IN c_get_gre LOOP
536 /* set l_gre to gre Fethched */
537
538 l_gre := gre_rec.gre;
539
540 /* Get the payroll_action_id of the archvier for given GRE */
541
542 OPEN c_gre_payroll_action (l_gre);
543 FETCH c_gre_payroll_action INTO l_payroll_action_id;
544
545 /* Check for the Gre That have been Archived */
546
547 IF c_gre_payroll_action%FOUND THEN
548
549 /* Check if any of the payroll_action_id has errored out or Not */
550
551 OPEN c_arch_errored_asg (l_payroll_action_id);
552 FETCH c_arch_errored_asg
553 INTO l_asg_errored;
554
555 IF c_arch_errored_asg%FOUND THEN
556 message_text := 'Assignment in Error Conditon for GRE:= ' || to_char(l_gre);
557 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
558 pay_core_utils.push_token('record_name',message_preprocess);
559 pay_core_utils.push_token('description',message_text);
560 --Some of the assignments have Errored
561 l_package_error_status := 'Y';
562 /* message to user -- assignment has errored out */
563 --
564 -- hr_utility.raise_error;
565 END IF;
566 CLOSE c_arch_errored_asg;
567
568 /* Checking for Retry */
569
570 OPEN c_arch_retry_pending (l_payroll_action_id);
571 FETCH c_arch_retry_pending INTO l_asg_retry_pend;
572 IF c_arch_retry_pending%FOUND THEN
573 --Some of the assignments have been marked for retry
574 message_text := 'Assignment Marked for Retry: GRE_ID := ' || to_char(l_gre);
575 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
576 pay_core_utils.push_token('record_name',message_preprocess);
577 pay_core_utils.push_token('description',message_text);
578 l_package_error_status := 'Y';
579 -- hr_utility.raise_error;
580
581 END IF;
582
583 CLOSE c_arch_retry_pending;
584
585 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
586 hr_utility.trace('Payroll_action_id - '|| to_char(l_payroll_action_id));
587 hr_utility.trace('No. of GREs picked so far - '|| to_char(l_no_of_gres_picked));
588
589 l_no_of_gres_picked := l_no_of_gres_picked + 1;
590 /* All the condition have been met so it is safe to make Arhive
591 Call
592 */
593
594 IF p_report_type = 'FEDW2' THEN --federal W2
595 archive_eoy_data(l_payroll_action_id,l_gre);
596 ELSE
597 hr_utility.trace('Federal smart archive call');
598 archive_eoy_data(l_payroll_action_id,l_gre);
599 hr_utility.trace('State Code :- ' || p_state_code);
600 hr_utility.trace('GRE - ' || to_char(l_gre));
601 hr_utility.trace('Before calling Smart State Archive');
602 archive_state_eoy_data(l_payroll_action_id,l_gre,p_state_code);
603 hr_utility.trace('After call to state Archive');
604 END IF;
605
606 hr_utility.trace('After Call to smart Archive ');
607
608 /* Check EIN for the Employee */
609
610 IF p_report_type = 'FEDW2' THEN --federal W2
611 /* Check for Federal Data */
612 l_ein_result := check_er_data(l_payroll_action_id,l_gre);
613
614 IF l_ein_result = 'N'THEN
615 message_text := 'EIN for GRE:= ' || to_char(l_gre) || 'Not Set';
616 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
617 pay_core_utils.push_token('record_name',message_preprocess);
618 pay_core_utils.push_token('description',message_text);
619 l_package_error_status := 'Y';
620 END IF;
621 ELSE
622 /* If Report type is not Fed so state */
623 /* Check for State */
624 -- OPEN c_chk_archive_state_code (l_gre,l_payroll_action_id);
625 -- FETCH c_chk_archive_state_code INTO l_state_tax_rules_exist;
626 -- IF c_chk_archive_state_code%NOTFOUND THEN
627 -- hr_utility.trace('State Tax Rules not Found ');
628 -- /* state Tax Rules have not been Defined */
629 -- message_text := 'State Tax Rules not Defind for GRE '
630 -- || to_char(l_gre) || ' for ' || P_state_abbrev;
631 -- pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
632 -- pay_core_utils.push_token('record_name',message_preprocess);
633 -- pay_core_utils.push_token('description',message_text);
634 -- l_package_error_status := 'Y';
635 -- END IF; /* Missing State Tax Rules */
636 --
637 -- close c_chk_archive_state_code;
638
639 /* Do check_er_data only if Record exist */
640 /* Add those checks for current Year */
641 hr_utility.trace('Check the State ER data ');
642 -- l_ein_result := check_state_er_data(l_payroll_action_id,l_gre,p_state_code);
643 -- hr_utility.trace('return value for check_state_er_data ' || l_ein_result );
644 /* EIN check Failed */
645 -- IF l_ein_result = 'N'THEN
646 -- hr_utility.trace('ID missing in State Tax Rules' || to_char(l_gre));
647 -- message_text := 'Missing ID in State Tax Rules for GRE:= '|| to_char(l_gre);
648 -- pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
649 -- pay_core_utils.push_token('record_name',message_preprocess);
650 -- pay_core_utils.push_token('description',message_text);
651 -- l_package_error_status := 'Y';
652 -- END IF;
653 END IF;
654
655 ELSE
656 /* The GRE has not been archived so check for valid Persons in the GRE
657 who have been paid for the run YEAR
658 Open Cursor as per your Report type to check if GRE needs to be archived
659 or Not */
660 /* Federal/State W2 Mag tapes should issue warning if GRE is not archived
661 Bug # 3069840
662 This is No loger an Pre-precess check Error. Pre-process check shuld log a
663 Warning an proceed to genrate W2 Mag Tape
664 */
665 /*
666 IF p_report_type = 'FEDW2' THEN --federal W2
667 hr_utility.set_location('pay_us_mmref_reporting.preprocess_check', 99);
668 OPEN c_gre_fed(gre_rec.gre);
669 ELSIF p_report_type = 'STW2' THEN --state W2
670 OPEN c_gre_state(gre_rec.gre);
671 END IF;
672
673 -- For GRE Find_out if any person has balance greater then Zero
674
675 LOOP --Main Loop
676 IF p_report_type = 'FEDW2' THEN
677 -- Start feching Persons for GRE
678 FETCH c_gre_fed INTO l_person
679 ,l_assignment
680 ,l_asg_effective_dt;
681 hr_utility.set_location('pay_us_mmref_reporting.preprocess_check',20);
682 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
683 hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
684 hr_utility.trace('Person ID:' || TO_CHAR(l_person));
685 hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
686 IF c_gre_fed%NOTFOUND THEN
687 -- get out of the Main Loop if You have reached No person Found
688 EXIT;
689 END IF;
690 END IF; -- report type = 'FEDW2'
691
692 IF p_report_type = 'STW2' THEN
693 FETCH c_gre_state INTO l_person
694 ,l_assignment
695 ,l_asg_effective_dt;
696
697 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 40);
698 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
699 hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
700 hr_utility.trace('Person ID:' || TO_CHAR(l_person));
701 hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
702 -- No Person Was found for the State So Exit
703 IF c_gre_state%NOTFOUND THEN
704 EXIT;
705 END IF;
706 END IF; -- report type = 'STW2' and etc
707
708
709 hr_utility.trace('pay_us_mmref_reporting.preprocess_check');
710 hr_utility.trace('GRE - '||to_char(l_gre));
711
712 -- get the balance for person
713
714 IF p_report_type = 'FEDW2' THEN
715 IF get_balance_value('GROSS_EARNINGS_PER_GRE_YTD',
716 l_gre, p_state_abbrev, l_assignment,
717 LEAST(p_year_end, l_asg_effective_dt)) > 0 THEN
718 l_balance_exists := 1;
719 END IF;
720 END IF; -- End of report_type 'FEDW2'
721
722
723 IF p_report_type = 'STW2' THEN
724 IF get_balance_value('GROSS_EARNINGS_PER_GRE_YTD',
725 l_gre, p_state_abbrev, l_assignment,
726 LEAST(p_year_end, l_asg_effective_dt)) > 0 AND
727 get_balance_value('SIT_GROSS_PER_JD_GRE_YTD',
728 l_gre, p_state_abbrev, l_assignment,
729 LEAST(p_year_end, l_asg_effective_dt)) > 0 THEN
730 l_balance_exists := 1;
731 END IF; -- balance Greater then Zero Exist
732 END IF;
733
734
735 IF l_balance_exists = 1 then
736 --It means that no archived GRE was
737 --found for the GRE. This is an error.
738 IF p_report_type = 'FEDW2' THEN
739
740 close c_gre_fed;
741 ELSE
742 close c_gre_state;
743 END IF; -- End Of Report_type 'FEDW2'
744 hr_utility.trace('Archive_Gre ' || to_char(l_gre));
745 -- Person Found with Balance for given GRE
746 message_text := 'GRE_ID := ' || to_char(l_gre)
747 || ' has People with Balnace > 0';
748 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
749 pay_core_utils.push_token('record_name',message_preprocess);
750 pay_core_utils.push_token('description',message_text);
751
752 message_text := 'Please Archive GRE With ID := ' || to_char(l_gre) ;
753 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
754 pay_core_utils.push_token('record_name',message_preprocess);
755 l_package_error_status := 'Y';
756 END IF;
757 l_balance_exists := 0;
758 END LOOP; --Main Loop
759 -- You have checked that We dont have any person with
760 -- balance greater then Zero
761
762 IF p_report_type = 'FEDW2' THEN
763 close c_gre_fed;
764 ELSE
765 close c_gre_state;
766 END if;
767 -- End of Comment for Bug # 3069840
768 --
769 */
770 -- End of Code comment for Bug # 3069840
771 --
772 /* A warning is logged if GRE is not archived Buug # 3069840 */
773 l_package_error_status := 'N';
774 message_text := 'Please Archive GRE With ID := ' || to_char(l_gre) ;
775 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
776 pay_core_utils.push_token('record_name',message_preprocess);
777 pay_core_utils.push_token('description',message_text);
778
779 END IF; --end if for checking of person balance if the GRE has
780 --not been archived.
781 CLOSE c_gre_payroll_action;
782
783 END LOOP; /* end of for statement */
784
785 IF l_package_error_status = 'Y' THEN
786 hr_utility.trace('Error Condition Found');
787 message_text := 'Error Condition detected ' ;
788 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
789 pay_core_utils.push_token('record_name',message_preprocess);
790 pay_core_utils.push_token('description',message_text);
791
792 message_text := 'Pay Message line and log have more Details' ;
793 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
794 pay_core_utils.push_token('record_name',message_preprocess);
795 pay_core_utils.push_token('description',message_text);
796 hr_utility.raise_error;
797 END IF;
798
799 IF l_no_of_gres_picked = 0 THEN
800 --It means that no archived GRE was
801 --found for the Organization. This is an error.
802
803 message_text := 'No GRE was picked for Magnetic Tape';
804 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
805 pay_core_utils.push_token('record_name',message_preprocess);
806 pay_core_utils.push_token('description',message_text);
807 hr_utility.raise_error;
808 END IF;
809
810 RETURN TRUE;
811 hr_utility.trace('Succesful - Return True ');
812 exception
813 when others then
814 -- add message for this
815 message_text := message_text || '+ Exception';
816 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
817 hr_utility.set_message_token('MESSAGE', message_text);
818 hr_utility.raise_error;
819
820 END preprocess_check;
821 --
822 ----------------------------------------------------------------------------
823 --Name
824 -- range_cursor
825 --Purpose
826 -- This procedure calls a function to check if the pre-processor has been
827 -- run for all the GREs and assignments. It then defines a SQL statement
828 -- to fetch all the people to be included in the report. This SQL statement
829 -- is used to define the 'chunks' for multi-threaded operation
830 --Arguments
831 -- p_pactid payroll action id for the report
832 -- p_sqlstr the SQL statement to fetch the people
833 ------------------------------------------------------------------------------
834 PROCEDURE range_cursor (
835 p_pactid IN NUMBER,
836 p_sqlstr OUT nocopy VARCHAR2
837 )
838 IS
839 p_year_start DATE;
840 p_year_end DATE;
841 p_business_group_id NUMBER;
842 p_state_abbrev VARCHAR2(3);
843 p_state_code VARCHAR2(2);
844 p_report_type VARCHAR2(30);
845 p_locality_code VARCHAR2(10);
846 BEGIN
847 --hr_utility.trace_on(null,'oracle');
848 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor', 10);
849 get_report_parameters(
850 p_pactid,
851 p_year_start,
852 p_year_end,
853 p_state_abbrev,
854 p_state_code,
855 p_report_type,
856 p_business_group_id,
857 p_locality_code
858 );
859 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor', 20);
860 IF preprocess_check(p_pactid,
861 p_year_start,
862 p_year_end,
863 p_business_group_id,
864 p_state_abbrev,
865 p_state_code,
866 p_report_type
867 ) THEN
868 IF p_report_type = 'FEDW2' THEN
869 p_sqlstr := '
870 SELECT DISTINCT paf.person_id
871 FROM per_all_assignments_f paf,
872 pay_assignment_actions paa,
873 pay_payroll_actions ppa,
874 pay_payroll_actions ppa1
875 WHERE ppa1.payroll_action_id = :payroll_action_id
876 AND ppa.report_type = ''YREND''
877 AND ppa.business_group_id+0 = ppa1.business_group_id
878 AND ppa.effective_date = ppa1.effective_date
879 AND ppa.start_date = ppa1.start_date
880 AND paa.payroll_action_id = ppa.payroll_action_id
881 AND paa.action_status = ''C''
882 AND paf.assignment_id = paa.assignment_id
883 AND paf.effective_start_date <= ppa.effective_date
884 AND paf.effective_end_date >= ppa.start_date
885 AND paf.assignment_type = ''E''
886 AND not exists (
887 SELECT ''x''
888 FROM hr_organization_information hoi
889 WHERE hoi.organization_id = paa.tax_unit_id
890 and hoi.org_information_context =
891 ''1099R Magnetic Report Rules'')
892 ORDER BY paf.person_id
893 ';
894 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor',
895 30);
896 ELSIF p_report_type = 'STW2' THEN
897 p_sqlstr := '
898 SELECT DISTINCT
899 to_number(paa.serial_number)
900 FROM ff_archive_item_contexts faic,
901 ff_archive_items fai,
902 ff_database_items fdi,
903 pay_assignment_actions paa,
904 pay_payroll_actions ppa,
905 per_all_assignments_f paf,
906 pay_payroll_actions ppa1
907 WHERE
908 ppa1.payroll_action_id = :payroll_action_id
909 AND ppa.business_group_id+0 = ppa1.business_group_id
910 AND ppa1.effective_date = ppa.effective_date
911 AND ppa.report_type = ''YREND''
912 AND ppa.payroll_action_id = paa.payroll_action_id
913 and paf.assignment_id = paa.assignment_id
914 AND paf.assignment_type = ''E''
915 AND fdi.user_name = ''A_STATE_ABBREV''
916 AND fdi.user_entity_id = fai.user_entity_id
917 AND fai.archive_item_id = faic.archive_item_id
918 AND fai.context1 = paa.assignment_action_id
919 --CPE
920 --AND fai.value = ppa1.report_qualifier
921 --CPE
922 AND fai.value = substr(ppa1.report_qualifier,1,2)
923 AND paf.effective_start_date <= ppa.effective_date
924 AND paf.effective_end_date >= ppa.start_date
925 AND paa.action_status = ''C''
926 AND nvl(hr_us_w2_rep.get_w2_arch_bal(
927 paa.assignment_action_id,
928 ''A_W2_STATE_WAGES'',
929 paa.tax_unit_id,
930 faic.context , 2),0) > 0
931 AND EXISTS ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
932 select ''x''
933 from pay_us_state_tax_info_f pustif
934 where substr(faic.context,1,2) = pustif.state_code
935 and ppa.effective_date between pustif.effective_start_date
936 and pustif.effective_end_date
937 and pustif.sit_exists = ''Y'')
938 AND not exists (
939 SELECT ''x''
940 FROM hr_organization_information hoi
941 WHERE hoi.organization_id = paa.tax_unit_id
942 and hoi.org_information_context =
943 ''1099R Magnetic Report Rules''
944 )
945 order by to_number(paa.serial_number)';
946 hr_utility.set_location( 'pay_us_mmref_reporting.range_cursor',
947 40);
948 END IF;
949 END IF;
950 END range_cursor;
951 --
952 -----------------------------------------------------------------------------
953 --Name
954 -- create_assignment_act
955 --Purpose
956 -- Creates assignment actions for the payroll action associated with the
957 -- report
958 --Arguments
959 -- p_pactid payroll action for the report
960 -- p_stperson starting person id for the chunk
961 -- p_endperson last person id for the chunk
962 -- p_chunk size of the chunk
963 --Note
964 -- The procedure processes assignments in 'chunks' to facilitate
965 -- multi-threaded operation. The chunk is defined by the size and the
966 -- starting and ending person id. An interlock is also created against the
967 -- pre-processor assignment action to prevent rolling back of the archiver.
968 ----------------------------------------------------------------------------
969 --
970 PROCEDURE create_assignment_act(
971 p_pactid IN NUMBER,
972 p_stperson IN NUMBER,
973 p_endperson IN NUMBER,
974 p_chunk IN NUMBER )
975 IS
976 -- Cursor to get the assignments for state W2. Gets only those employees
977 -- which have wages for the specified state.This cursor excludes the
978 -- 1099R GREs.
979 CURSOR c_state (c_state_code varchar2,
980 c_locality_code varchar2)
981 IS
982 SELECT
983 to_number(paa.serial_number),
984 paf.assignment_id,
985 paa.tax_unit_id,
986 paf.effective_end_date,
987 paa.assignment_action_id,
988 fai1.value
989
990 FROM
991 pay_assignment_actions paa, /* YREND PAA */
992 pay_payroll_actions ppa, /* YREND PPA */
993 per_all_assignments_f paf,
994 pay_payroll_actions ppa1,
995 ff_contexts fc1 , --for city context
996 ff_archive_items fai1, -- city
997 ff_archive_item_contexts faic1, -- city_context
998 ff_database_items fdi1 --database_items for City_withheld
999 --,pay_us_city_tax_info_f puctif
1000 WHERE
1001 ppa1.payroll_action_id = p_pactid
1002 and ppa.business_group_id+0 = ppa1.business_group_id
1003 and ppa1.effective_date = ppa.effective_date
1004 and ppa.report_type = 'YREND'
1005 and ppa.payroll_action_id = paa.payroll_action_id
1006 and paf.assignment_id = paa.assignment_id
1007 and paf.assignment_type = 'E'
1008 and fc1.context_name = 'JURISDICTION_CODE'
1009 and faic1.context_id = fc1.context_id
1010 and fdi1.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
1011 and fdi1.user_entity_id = fai1.user_entity_id
1012 and fai1.context1 = paa.assignment_action_id
1013 and fai1.archive_item_id = faic1.archive_item_id
1014
1015 -- Commneted to generalise stater
1016 --and ltrim(rtrim(faic1.context)) like '36%'
1017 -- Commented for to generalise locality or ageny code
1018 --and (puctif.city_information1 like 'RITA%'
1019 -- or puctif.city_information1 like 'CCCA%')
1020 --
1021 and exists ( select 'x' from pay_us_city_tax_info_f puctif
1022 where puctif.jurisdiction_code = ltrim(rtrim(faic1.context))
1023 and ltrim(rtrim(faic1.context)) like c_state_code||'%'
1024 and puctif.city_information1 like c_locality_code||'%'
1025 and puctif.effective_start_date < ppa.effective_date
1026 and puctif.effective_end_date >= ppa.effective_date
1027 )
1028 and paf.effective_start_date <= ppa.effective_date
1029 and paf.effective_end_date >= ppa.start_date
1030 and paa.action_status = 'C'
1031 and paa.serial_number between p_stperson and p_endperson
1032 and paf.person_id between p_stperson AND p_endperson
1033 and not exists
1034 (
1035 select 'x'
1036 from hr_organization_information hoi
1037 WHERE hoi.organization_id = paa.tax_unit_id
1038 and hoi.org_information_context ='1099R Magnetic Report Rules'
1039 )
1040 and rtrim(ltrim(fai1.value)) <> '0'
1041 ORDER BY 1, 3, 4 DESC, 2;
1042
1043
1044 -- Cursor to get the assignments for federal W2. Excludes 1099R GREs.
1045 CURSOR c_federal IS
1046 SELECT paf.person_id,
1047 paf.assignment_id,
1048 Paa.tax_unit_id, --TO_NUMBER(hsck.segment1),
1049 paf.effective_end_date,
1050 paa.assignment_action_id
1051 FROM pay_payroll_actions ppa,
1052 pay_assignment_actions paa,
1053 per_all_assignments_f paf,
1054 pay_payroll_actions ppa1
1055 WHERE ppa1.payroll_action_id = p_pactid
1056 AND ppa.report_type = 'YREND'
1057 AND ppa.business_group_id+0 = ppa1.business_group_id
1058 AND ppa.effective_date = ppa1.effective_date
1059 AND ppa.start_date = ppa1.start_date
1060 AND paa.payroll_action_id = ppa.payroll_action_id
1061 AND paa.action_status = 'C'
1062 AND paf.assignment_id = paa.assignment_id
1063 AND paa.serial_number BETWEEN p_stperson AND p_endperson
1064 AND paf.person_id BETWEEN p_stperson AND p_endperson
1065 AND paf.assignment_type = 'E'
1066 AND paf.effective_start_date <= ppa.effective_date
1067 AND paf.effective_end_date >= ppa.start_date
1068 AND not exists (
1069 SELECT 'x'
1070 FROM hr_organization_information hoi
1071 WHERE hoi.organization_id = paa.tax_unit_id
1072 and hoi.org_information_context = '1099R Magnetic Report Rules')
1073 ORDER BY 1, 3, 4 DESC, 2
1074 FOR UPDATE OF paf.assignment_id;
1075 cursor csr_get_fed_wages(p_assignment_action_id number,
1076 p_tax_unit_id number) is
1077 select to_number(fai.value)
1078 from ff_archive_item_contexts faic,
1079 ff_archive_items fai,
1080 ff_contexts fc,
1081 ff_database_items fdi
1082 where fdi.user_name = 'A_GROSS_EARNINGS_PER_GRE_YTD'
1083 and fc.context_name = 'TAX_UNIT_ID'
1084 and fai.context1 = to_char(p_assignment_action_id)
1085 and fai.user_entity_id = fdi.user_entity_id
1086 and faic.archive_item_id = fai.archive_item_id
1087 and faic.context_id = fc.context_id
1088 and faic.context = to_char(p_tax_unit_id)
1089 and faic.sequence_no = 1;
1090 --local variables
1091 l_year_start DATE;
1092 l_year_end DATE;
1093 l_effective_end_date DATE;
1094 l_state_abbrev VARCHAR2(3);
1095 l_state_code VARCHAR2(2);
1096 l_report_type VARCHAR2(30);
1097 l_business_group_id NUMBER;
1098 l_person_id NUMBER;
1099 l_prev_person_id NUMBER;
1100 l_assignment_id NUMBER;
1101 l_assignment_action_id NUMBER;
1102 l_value NUMBER;
1103 l_tax_unit_id NUMBER;
1104 l_prev_tax_unit_id NUMBER;
1105 lockingactid NUMBER;
1106 l_group_by_gre BOOLEAN;
1107 l_w2_box17 NUMBER; --SIT Wages
1108 l_gre_id NUMBER;
1109 l_error_flag VARCHAR2(10);
1110 l_locality_code VARCHAR2(10);
1111 BEGIN
1112 -- Set the local variable to correct Value
1113
1114 l_gre_id := -1;
1115 l_error_flag := 'N';
1116
1117 -- Get the report parameters. These define the report being run.
1118 hr_utility.set_location( 'pay_us_mmref_reporting.create_assignement_act',
1119 10);
1120 get_report_parameters(
1121 p_pactid,
1122 l_year_start,
1123 l_year_end,
1124 l_state_abbrev,
1125 l_state_code,
1126 l_report_type,
1127 l_business_group_id,
1128 l_locality_code
1129 );
1130 --Currently all reports group by GRE
1131 l_group_by_gre := TRUE;
1132 --Open the appropriate cursor
1133 hr_utility.set_location( 'pay_us_mmref_reporting.create_assignement_act',
1134 20);
1135 hr_utility.trace('LOCALITY_CODE : '|| l_locality_code);
1136 IF l_report_type = 'FEDW2' THEN
1137 OPEN c_federal;
1138 ELSIF l_report_type = 'STW2' THEN
1139 OPEN c_state(l_state_code,
1140 l_locality_code);
1141 END IF;
1142 LOOP
1143 IF l_report_type = 'FEDW2' THEN
1144 FETCH c_federal INTO l_person_id,
1145 l_assignment_id,
1146 l_tax_unit_id,
1147 l_effective_end_date,
1148 l_assignment_action_id;
1149 hr_utility.set_location(
1150 'pay_us_mmref_reporting.create_assignement_act', 30);
1151 EXIT WHEN c_federal%NOTFOUND;
1152 ELSIF l_report_type = 'STW2' THEN
1153 FETCH c_state
1154 INTO l_person_id,
1155 l_assignment_id,
1156 l_tax_unit_id,
1157 l_effective_end_date,
1158 l_assignment_action_id,
1159 l_w2_box17;
1160 hr_utility.set_location(
1161 'pay_us_mmref_reporting.create_assignement_act', 40);
1162 EXIT WHEN c_state%NOTFOUND;
1163 -- Check the state Tax rules if new gre
1164 -- Set the Error Flag to Y so that Action Creation will Error
1165 -- At the End
1166
1167 IF l_gre_id = l_tax_unit_id THEN
1168 hr_utility.trace('Same GRE ');
1169 ELSE
1170 IF check_state_er_data(p_pactid,l_tax_unit_id,'A') = 'N' THEN
1171 hr_utility.trace('State Tax Rules Missing in GRE');
1172 l_gre_id := l_tax_unit_id;
1173 l_error_flag := 'Y';
1174 ELSE
1175 l_gre_id := l_tax_unit_id;
1176 END if ; --check ER
1177 END IF;
1178
1179 END IF;
1180 --Based on the groupin criteria, check if the record is the same
1181 --as the previous record.
1182 --Grouping by GRE requires a unique person/GRE combination for
1183 --each record.
1184 IF ((l_group_by_gre AND
1185 l_person_id = l_prev_person_id AND
1186 l_tax_unit_id = l_prev_tax_unit_id) OR
1187 (NOT l_group_by_gre AND
1188 l_person_id = l_prev_person_id)) THEN
1189 --Do Nothing
1190 hr_utility.set_location(
1191 'pay_us_mmref_reporting.create_assignement_act', 50);
1192 NULL;
1193 ELSE
1194 --Create the assignment action for the record
1195 hr_utility.trace('Assignment Fetched - ');
1196 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
1197 hr_utility.trace('Person Id : '|| to_char(l_person_id));
1198 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
1199 hr_utility.trace('Effective End Date : '||
1200 to_char(l_effective_end_date));
1201 IF l_report_type = 'FEDW2' then
1202 open csr_get_fed_wages(l_assignment_action_id, l_tax_unit_id);
1203 fetch csr_get_fed_wages into l_value;
1204 if csr_get_fed_wages%NOTFOUND then
1205 l_value := 0;
1206 end if;
1207 close csr_get_fed_wages;
1208 END IF;
1209 IF (l_report_type = 'FEDW2' and l_value > 0) OR
1210 (l_report_type = 'STW2') then
1211 SELECT pay_assignment_actions_s.nextval
1212 INTO lockingactid
1213 FROM dual;
1214 hr_utility.set_location(
1215 'pay_us_mmref_reporting.create_assignement_act', 60);
1216 hr_nonrun_asact.insact(lockingactid, l_assignment_id, p_pactid,
1217 p_chunk, l_tax_unit_id);
1218 hr_utility.set_location(
1219 'pay_us_mmref_reporting.create_assignement_act', 70);
1220 /*
1221 --update serial number for highly compensated people for the
1222 --state W2.
1223 IF l_report_type = 'STW2' THEN
1224 hr_utility.set_location(
1225 'pay_us_mmref_reporting.create_assignement_act', 80);
1226 IF l_w2_box17 > 999999999.99 THEN
1227 UPDATE pay_assignment_actions
1228 SET serial_number = 9999999
1229 WHERE assignment_action_id = lockingactid;
1230 END IF;
1231 END IF;
1232 */ -- Bug 4490252
1233 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
1234 hr_utility.set_location(
1235 'pay_us_mmref_reporting.create_assignement_act', 90);
1236 hr_utility.trace('Interlock Created - ');
1237 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
1238 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
1239 --Store the current person/GRE for comparision during the
1240 --next iteration.
1241 l_prev_person_id := l_person_id;
1242 l_prev_tax_unit_id := l_tax_unit_id;
1243 END IF;
1244 END IF;
1245 END LOOP;
1246 IF l_report_type = 'FEDW2' THEN
1247 CLOSE c_federal;
1248 ELSIF l_report_type = 'STW2' THEN
1249 CLOSE c_state;
1250 END IF;
1251
1252 IF l_error_flag = 'Y' THEN
1253 hr_utility.trace('Error Flag was set to Y');
1254 hr_utility.raise_error;
1255 END IF;
1256
1257 END create_assignment_act;
1258
1259 FUNCTION check_er_data (
1260 p_pactid IN NUMBER,
1261 p_ein_user_id IN NUMBER )
1262 RETURN varchar2
1263 IS
1264
1265 l_ein_val varchar2(80);
1266 l_ein_status varchar2(80);
1267 l_add_status varchar2(80);
1268 l_gre number;
1269
1270
1271 message_preprocess varchar2(80);
1272 message_text varchar2(80);
1273
1274 CURSOR c_get_user_entity_id
1275 IS
1276 SELECT user_entity_id
1277 FROM ff_database_items
1278 WHERE user_name in ( 'A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER',
1279 'A_TAX_UNIT_NAME');
1280
1281 CURSOR c_get_address_entity_id
1282 IS
1283 SELECT user_entity_id
1284 FROM ff_database_items
1285 WHERE user_name = 'TAX_UNIT_ADDRESS_LINE_1';
1286
1287
1288
1289 BEGIN
1290 l_ein_status := 'Y';
1291 l_gre := p_ein_user_id;
1292 FOR c_id IN c_get_user_entity_id LOOP
1293
1294 SELECT value
1295 INTO l_ein_val
1296 FROM ff_archive_items fai,
1297 ff_contexts fc,
1298 ff_archive_item_contexts faic
1299 WHERE fai.context1 = to_char(p_pactid)
1300 AND user_entity_id = c_id.user_entity_id
1301 AND faic.archive_item_id = fai.archive_item_id
1302 AND faic.context = to_char(l_gre)
1303 AND fc.context_name = 'TAX_UNIT_ID'
1304 AND fc.context_id = faic.context_id ;
1305
1306 IF l_ein_val IS NULL OR l_ein_status = 'N' THEN
1307 l_ein_status := 'N' ;
1308 END IF;
1309
1310 END LOOP;
1311
1312 IF l_ein_status = 'N' THEN
1313 message_preprocess := 'Pre-Process Check-';
1314 message_text := 'EIN or Tax Unit Name Missing ';
1315 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1316 pay_core_utils.push_token('record_name',message_preprocess);
1317 pay_core_utils.push_token('description',message_text);
1318 END IF;
1319
1320 return l_ein_status ;
1321
1322 exception WHEN OTHERS THEN
1323 return 'N';
1324 END check_er_data;
1325
1326 /* check the data for only one state EIN */
1327
1328 FUNCTION check_state_er_data (
1329 p_pactid IN NUMBER,
1330 p_tax_unit IN NUMBER,
1331 p_jurisdictions IN varchar2 )
1332 RETURN varchar2
1333 IS
1334 l_state_ein_val varchar2(80);
1335 l_sit_state_id varchar2(80);
1336 l_jurisdiction varchar2(80);
1337
1338 CURSOR c_state_sit IS
1339 SELECT user_entity_id
1340 FROM ff_user_entities
1341 WHERE user_entity_name = 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID';
1342
1343 CURSOR c_get_state_id
1344 IS
1345 SELECT value
1346 FROM ff_archive_items fai,
1347 ff_archive_item_contexts faic,
1348 ff_archive_item_contexts faic1
1349 WHERE context1 = to_char(p_pactid)
1350 AND user_entity_id = l_sit_state_id
1351 AND faic.archive_item_id = fai.archive_item_id
1352 AND faic1.archive_item_id = fai.archive_item_id
1353 AND faic.context = to_char(p_tax_unit)
1354 and faic1.context = p_jurisdictions || '-000-0000';
1355
1356
1357 BEGIN
1358
1359 OPEN c_state_sit;
1360 FETCH c_state_sit INTO l_sit_state_id;
1361 CLOSE c_state_sit;
1362
1363
1364 OPEN c_get_state_id;
1365 FETCH c_get_state_id INTO l_state_ein_val;
1366
1367 CLOSE c_get_state_id;
1368 IF l_state_ein_val is NULL THEN
1369 return 'Y';
1370 ELSE
1371 return 'Y';
1372 END IF;
1373
1374 exception WHEN OTHERS THEN
1375 return 'Y';
1376 END check_state_er_data;
1377
1378
1379 PROCEDURE ARCHIVE_EOY_DATA (
1380 p_pactid IN NUMBER,
1381 p_tax_id IN NUMBER )
1382
1383 IS
1384 BEGIN
1385 /* get the Parameter Setting */
1386 pay_us_archive.eoy_archive_gre_data(p_pactid,p_tax_id,'FED W2 REPORTING RULES','ALL');
1387 pay_us_archive.eoy_archive_gre_data(p_pactid,p_tax_id,'FED TAX UNIT INFORMATION','ALL');
1388 pay_us_archive.eoy_archive_gre_data(p_pactid,p_tax_id,'FEDERAL TAX RULES','ALL');
1389
1390 END ARCHIVE_EOY_DATA;
1391
1392 /* Note: There is no way of limiting the archiving data related to single state
1393 */
1394
1395 PROCEDURE ARCHIVE_STATE_EOY_DATA (
1396 p_pactid IN NUMBER,
1397 p_tax_id IN NUMBER,
1398 p_state_code IN VARCHAR2 )
1399
1400 IS
1401 BEGIN
1402 /* get the Parameter Setting */
1403
1404 hr_utility.trace('Calling the state archiving ');
1405 hr_utility.trace('Pactid ' || to_char(p_pactid));
1406 hr_utility.trace('tax id ' || to_char(p_tax_id));
1407 hr_utility.trace('Pactid ' || p_state_code);
1408
1409 pay_us_archive.eoy_archive_gre_data(p_pactid,p_tax_id,'STATE TAX RULES',p_state_code);
1410
1411 END ARCHIVE_STATE_EOY_DATA;
1412
1413 FUNCTION check_state_data
1414 ( p_payroll_action_id number,
1415 p_transfer_state varchar2
1416 ) RETURN varchar2
1417 IS
1418 /* get the state Code */
1419 CURSOR c_state_code(cp_state varchar2 )
1420 IS select state_code
1421 from pay_us_states
1422 WHERE state_abbrev = cp_state;
1423
1424 CURSOR c_yep_tax_unit_ppa_id(cp_payroll_action_id number )
1425 IS
1426 SELECT DISTINCT paa.tax_unit_id unit_id,ppa1.payroll_action_Id payroll_action,name
1427 FROM pay_assignment_actions paa
1428 ,pay_payroll_actions ppa1 /* year End Pre-process for GRE */
1429 ,pay_payroll_actions ppa /* Year End Pre-process for W-2 */
1430 ,hr_organization_units hou
1431 WHERE
1432 ppa.payroll_action_id = cp_payroll_action_id /* W2 payroll_action_id */
1433 and ppa.payroll_action_id = paa.payroll_action_id
1434 and ppa1.legislative_parameters like ltrim(rtrim(to_char(paa.tax_unit_id))) || ' TRANS%'
1435 and ppa1.effective_date = ppa.effective_date
1436 and ppa1.report_type = 'YREND'
1437 and ppa1.report_qualifier = 'FED'
1438 and hou.organization_id = paa.tax_unit_id;
1439
1440 CURSOR c_sit_check (cp_payroll_action_id number,
1441 cp_tax_unit_id number,
1442 cp_state_Code varchar2 )
1443 IS
1444 SELECT target.value
1445 FROM
1446 ff_archive_item_contexts con3,
1447 ff_archive_item_contexts con2,
1448 ff_contexts fc3,
1449 ff_contexts fc2,
1450 ff_archive_items target,
1451 ff_database_items fdi
1452 WHERE target.context1 = to_char(cp_payroll_action_id)
1453 /* context of payroll_action_id */
1454 and fdi.user_name = 'A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID'
1455 and target.user_entity_id = fdi.user_entity_id
1456 and fc2.context_name = 'TAX_UNIT_ID'
1457 and con2.archive_item_id = target.archive_item_id
1458 and con2.context_id = fc2.context_id
1459 and ltrim(rtrim(con2.context)) = ltrim(rtrim(to_char(cp_tax_unit_id)))
1460 and fc3.context_name = 'JURISDICTION_CODE'
1461 and con3.archive_item_id = target.archive_item_id
1462 and con3.context_id = fc3.context_id
1463 and substr(ltrim(rtrim(con3.context)),1,2) = ltrim(rtrim(cp_state_code));
1464 /* jurisdiction code of the state */
1465 /* local Variables */
1466 l_w2_state pay_us_states.state_code%type;
1467 l_tax_unit_id number;
1468 l_payroll_action_id number;
1469 l_state_code varchar2(2);
1470 l_info varchar2(80);
1471 l_flag varchar2(2) := 'Y';
1472 l_gre hr_organization_units.name%type;
1473 l_message_preprocess varchar2(80);
1474 l_message_text varchar2(200);
1475 u_message_text varchar2(200);
1476 BEGIN
1477 IF p_transfer_state = 'FED' THEN
1478 return 'Y';
1479 end if;
1480 /* Get the state Code for the W2 Tape */
1481 OPEN C_STATE_CODE(p_transfer_state);
1482 FETCH c_state_code into l_w2_state;
1483 CLOSE C_STATE_CODE;
1484 FOR c1 IN c_yep_tax_unit_ppa_id(p_payroll_action_id ) LOOP
1485 open c_sit_check(C1.payroll_action,c1.unit_id,l_w2_state);
1486 l_message_preprocess := 'GRE: ' ||c1.name || ' has ' ;
1487 fetch c_sit_check INTO l_info ;
1488 l_message_text := to_char(c1.unit_id) || 'Payroll_action_id ' || to_char(c1.payroll_action);
1489 hr_utility.trace(l_message_preprocess || ' ' || l_message_text) ;
1490 if c_sit_check%notfound then
1491 l_message_text := ' :Missing State Tax Rules For State of :' || p_transfer_state;
1492 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1493 pay_core_utils.push_token('record_name',l_message_preprocess);
1494 pay_core_utils.push_token('description',l_message_text);
1495 hr_utility.trace(l_message_preprocess || ' ' || l_message_text );
1496 hr_utility.trace(l_message_preprocess || ' ' || l_message_text );
1497 l_flag := 'N';
1498 close c_sit_check;
1499 else
1500 if l_info IS NULL THEN
1501 l_flag := 'N';
1502 l_message_text := ' NULL EIN For State of :' || p_transfer_state;
1503 hr_utility.trace(l_message_preprocess || ' ' || l_message_text );
1504 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1505 pay_core_utils.push_token('record_name',l_message_preprocess);
1506 pay_core_utils.push_token('description',l_message_text);
1507 end if ;
1508 close c_sit_check;
1509 end if;
1510 END loop;
1511 IF l_flag = 'N' THEN
1512 l_message_text := 'Flag N Set for Tax Rule Check ';
1513 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1514 pay_core_utils.push_token('record_name',l_message_preprocess);
1515 pay_core_utils.push_token('description',l_message_text);
1516 u_message_text := 'Error in EIN or State Tax Rule Missing ';
1517 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
1518 hr_utility.set_message_token('MESSAGE', u_message_text);
1519 hr_utility.raise_error;
1520 return 'N';
1521
1522 END IF;
1523 return 'Y';
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 l_message_preprocess := 'Exception ';
1527 l_message_text := 'In State Tax Rules Or EIN Checking ';
1528 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1529 pay_core_utils.push_token('record_name',l_message_preprocess);
1530 pay_core_utils.push_token('description',l_message_text);
1531 u_message_text := 'Exception : State Tax Rules Or EIN Checking ';
1532 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
1533 hr_utility.set_message_token('MESSAGE', u_message_text);
1534 hr_utility.raise_error;
1535 return 'Y';
1536 END check_state_data;
1537
1538
1539
1540
1541 END pay_us_mmref_local;
[Home] [Help]