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