1 PACKAGE BODY pay_us_magw2_reporting AS
2 /* $Header: pyyepmw2.pkb 115.15 2002/12/03 03:02:16 ppanda ship $ */
3 -----------------------------------------------------------------------------
4 -- Name : bal_db_item
5 -- Purpose : Given the name of a balance DB item as would be seen in a
6 -- fast formula it returns the defined_balance_id of the
7 -- balance it represents.
8 -- Arguments
9 -- INPUT : p_db_item_name
10 -- returns : l_defined_balance_id
11 -- Notes
12 -- A defined_balance_id is required by the PLSQL balance function.
13 -----------------------------------------------------------------------------
14 FUNCTION bal_db_item
15 ( p_db_item_name VARCHAR2
16 ) RETURN NUMBER IS
17 -- Get the defined_balance_id for the specified balance DB item.
18 CURSOR csr_defined_balance IS
19 SELECT TO_NUMBER(UE.creator_id)
20 FROM ff_database_items DI,
21 ff_user_entities UE
22 WHERE DI.user_name = p_db_item_name
23 AND UE.user_entity_id = DI.user_entity_id
24 AND UE.creator_type = 'B'
25 AND UE.legislation_code = 'US'; /* Bug: 2296797 */
26 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
27 BEGIN
28 hr_utility.set_location
29 ('pay_us_magw2_reporting.bal_db_item - opening cursor', 10);
30 -- Open the cursor
31 OPEN csr_defined_balance;
32 -- Fetch the value
33 FETCH csr_defined_balance
34 INTO l_defined_balance_id;
35 IF csr_defined_balance%NOTFOUND THEN
36 CLOSE csr_defined_balance;
37 hr_utility.set_location
38 ('pay_us_magw2_reporting.bal_db_item - no rows found from cursor', 20);
39 hr_utility.raise_error;
40 ELSE
41 hr_utility.set_location
42 ('pay_us_magw2_reporting.bal_db_item - fetched from cursor', 30);
43 CLOSE csr_defined_balance;
44 END IF;
45 /* Return the value to the call */
46 RETURN (l_defined_balance_id);
47 END bal_db_item;
48 -----------------------------------------------------------------------------
49 -- Name : :get_report_parameters
50 --
51 -- Purpose
52 -- The procedure gets the 'parameter' for which the report is being
53 -- run i.e., the period, state and business organization.
54 --
55 -- Arguments
56 -- p_year_start Start Date of the period for which the report
57 -- has been requested
58 -- p_year_end End date of the period
59 -- p_business_group_id Business group for which the report is being run
60 -- p_state_abbrev Two digit state abbreviation (or 'FED' for federal
61 -- report)
62 -- p_state_code State code (NULL for federal)
63 -- p_report_type Type of report being run (FEDW2, STW2, 1099R ...)
64 --
65 -- Notes
66 ----------------------------------------------------------------------------
67 PROCEDURE get_report_parameters
68 ( p_pactid IN NUMBER,
69 p_year_start IN OUT nocopy DATE,
70 p_year_end IN OUT nocopy DATE,
71 p_state_abbrev IN OUT nocopy VARCHAR2,
72 p_state_code IN OUT nocopy VARCHAR2,
73 p_report_type IN OUT nocopy VARCHAR2,
74 p_business_group_id IN OUT nocopy NUMBER
75 ) IS
76 BEGIN
77 hr_utility.set_location
78 ('pay_us_magw2_reporting.get_report_parameters', 10);
79 SELECT ppa.start_date,
80 ppa.effective_date,
81 ppa.business_group_id,
82 ppa.report_qualifier,
83 ppa.report_type
84 INTO p_year_start,
85 p_year_end,
86 p_business_group_id,
87 p_state_abbrev,
88 p_report_type
89 FROM pay_payroll_actions ppa
90 WHERE payroll_action_id = p_pactid;
91 IF p_state_abbrev <> 'FED' THEN
92 SELECT state_code
93 INTO p_state_code
94 FROM pay_us_states
95 WHERE state_abbrev = p_state_abbrev;
96 hr_utility.set_location
97 ('pay_us_magw2_reporting.get_report_parameters', 20);
98 ELSE
99 p_state_code := '';
100 hr_utility.set_location
101 ('pay_us_magw2_reporting.get_report_parameters', 30);
102 END IF;
103 IF p_state_abbrev = 'FED' AND p_report_type = 'W2' THEN
104 p_report_type := 'FEDW2';
105 ELSIF p_report_type = 'W2' THEN
106 p_report_type := 'STW2';
107 END IF;
108 hr_utility.set_location
109 ('pay_us_magw2_reporting.get_report_parameters', 40);
110 END get_report_parameters;
111 -------------------------------------------------------------------------
112 -- Name : get_balance_value
113 --
114 --Purpose
115 -- Get the value of the specified balance item
116 --Arguments
117 -- p_balance_name Name of the balnce
118 -- p_tax_unit_id GRE name for the context
119 -- p_state_code State for context
120 -- p_assignment_id Assignment for whom the balance is to be
121 -- retrieved
122 -- p_effective_date effective_date
123 --Note
124 -- This procedure set is a wrapper for setting the GRE/Jurisdiction context
125 -- needed by the pay_balance_pkg.get_value to get the actual balance
126 -------------------------------------------------------------------------
127 FUNCTION get_balance_value (
128 p_balance_name VARCHAR2,
129 p_tax_unit_id NUMBER,
130 p_state_abbrev VARCHAR2,
131 p_assignment_id NUMBER,
132 p_effective_date DATE
133 ) RETURN NUMBER IS
134 l_jurisdiction_code VARCHAR2(20);
135 BEGIN
136 hr_utility.set_location
137 ('pay_us_magw2_reporting.get_balance_value', 10);
138 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
139 IF p_state_abbrev <> 'FED' THEN
140 SELECT jurisdiction_code
141 INTO l_jurisdiction_code
142 FROM pay_state_rules
143 WHERE state_code = p_state_abbrev;
144 hr_utility.set_location
145 ('pay_us_magw2_reporting.get_balance_value', 15);
146 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction_code);
147 END IF;
148 hr_utility.trace(p_balance_name);
149 hr_utility.trace('Context');
150 hr_utility.trace('Tax Unit Id: '|| p_tax_unit_id);
151 hr_utility.trace('Jurisdiction: '|| l_jurisdiction_code);
152 hr_utility.set_location
153 ('pay_us_magw2_reporting.get_balance_value', 20);
154 RETURN pay_balance_pkg.get_value(bal_db_item(p_balance_name),
155 p_assignment_id, p_effective_date);
156 END get_balance_value;
157 --------------------------------------------------------------------------
158 --Name
159 -- preprocess_check
160 --Purpose
161 -- This function checks if the year end preprocessor has been run for the
162 -- GREs involved in the W2 report. It also checks if any of the assignments
163 -- have errored out or have been marked for retry.
164 --
165 --Arguments
166 -- p_pactid payroll_action_id for the report
167 -- p_year_start start date of the period for which the report
168 -- has been requested
169 -- p_year_end end date of the period
170 -- p_business_group_id business group for which the report is being run
171 -- p_state_abbrev two digit state abbreviation (or 'FED' for federal
172 -- report)
173 -- p_state_code state code (NULL for federal)
174 -- p_report_type type of report being run (W2, 1099R ...)
175 --
176 --Notes
177 -- The check for 'errored'/'marked for retry'assignments can be bypassed by
178 -- setting the parameter 'FORCE_MAG_REPORT' to 'E' and 'M' respectively. In
179 -- such cases the report will ignore the assignments in question.
180 -----------------------------------------------------------------------------
181 FUNCTION preprocess_check
182 (
183 p_pactid NUMBER,
184 p_year_start DATE,
185 p_year_end DATE,
186 p_business_group_id NUMBER,
187 p_state_abbrev VARCHAR2,
188 p_state_code VARCHAR2,
189 p_report_type VARCHAR2
190 )
191 RETURN BOOLEAN IS
192 -- Cursor to get all the GREs belonging to the given business group
193 CURSOR c_get_gre IS
194 SELECT hou.organization_id gre
195 FROM hr_organization_information hoi,
196 hr_organization_units hou
197 WHERE hou.business_group_id+0 = p_business_group_id AND
198 hoi.organization_id = hou.organization_id AND
199 hoi.org_information_context = 'CLASS' AND
200 hoi.org_information1 = 'HR_LEGAL' AND
201 NOT EXISTS (
202 SELECT 'Y'
203 FROM hr_organization_information
204 WHERE organization_id = hou.organization_id
205 AND org_information_context = '1099R Magnetic Report Rules');
206 -- Check if the GRE needs to be archived.
207 -- Cursor to fetch people in a given GRE with earnings in the given state to
208
209 CURSOR c_gre_state (cp_tax_unit_id NUMBER)IS
210 SELECT paf.person_id,
211 paf.assignment_id,
212 paf.effective_end_date
213 FROM per_assignments_f paf
214 WHERE exists
215 (SELECT 'x'
216 FROM pay_us_emp_state_tax_rules_f pest
217 WHERE pest.state_code = p_state_code
218 AND pest.business_group_id + 0 = p_business_group_id
219 AND pest.effective_start_date <= p_year_end
220 AND pest.effective_end_date >= p_year_start
221 AND pest.assignment_id = paf.assignment_id
222 )
223 AND paf.effective_start_date <= p_year_end
224 AND paf.effective_end_date >= p_year_start
225 AND paf.business_group_id+0 = p_business_group_id
226 AND paf.assignment_type = 'E'
227 AND EXISTS
228 (SELECT 'x'
229 FROM pay_assignment_actions paa_act,
230 pay_payroll_actions ppa_act
231 WHERE paa_act.assignment_id = paf.assignment_id
232 AND paa_act.tax_unit_id = cp_tax_unit_id
233 AND ppa_act.payroll_action_id = paa_act.payroll_action_id
234 AND ppa_act.action_type IN ('R', 'Q', 'B', 'I', 'V')
235 AND ppa_act.effective_date BETWEEN p_year_start
236 AND p_year_end
237 AND ppa_act.date_earned BETWEEN paf.effective_start_date
238 AND paf.effective_end_date
239 AND ppa_act.action_status = 'C' )
240 ORDER BY 1, 3 DESC, 2;
241
242 -- Cursor to fetch people from the GRE belonging to the business group
243
244 CURSOR c_gre_fed (cp_tax_unit_id NUMBER) IS
245 SELECT paf.person_id,
246 paf.assignment_id,
247 paf.effective_end_date
248 FROM per_assignments_f paf
249 WHERE paf.business_group_id+0 = p_business_group_id
250 -- In order to avoid full table scan on per_assignment_f
251 -- added assignmet_id
252 AND paf.assignment_id >= 0
253 AND paf.effective_start_date <= p_year_end
254 AND paf.effective_end_date >= p_year_start
255 AND paf.assignment_type = 'E'
256 AND EXISTS (
257 SELECT 'x'
258 FROM pay_payroll_actions ppa_act,
259 pay_assignment_actions paa_act
260 WHERE paa_act.assignment_id = paf.assignment_id
261 AND paa_act.tax_unit_id = cp_tax_unit_id
262 AND ppa_act.payroll_action_id = paa_act.payroll_action_id
263 AND ppa_act.action_type IN ('R', 'Q', 'B', 'I', 'V')
264 AND ppa_act.effective_date
265 BETWEEN p_year_start AND p_year_end
266 AND ppa_act.date_earned
267 BETWEEN paf.effective_start_date AND paf.effective_end_date
268 AND ppa_act.action_STATUS = 'C' -- ADDED BY Djoshi
269 )
270 ORDER BY 1, 3 DESC, 2;
271
272 -- Cursor to get payroll_action_ids of the pre-process for the given GRE.
273 -- This will also serve as a check to make sure that all GREs have been
274 -- archived
275 CURSOR c_gre_payroll_action (cp_gre NUMBER) IS
276 SELECT payroll_action_id
277 FROM pay_payroll_actions
278 WHERE report_type = 'YREND'
279 AND effective_date = p_year_end
280 AND start_date = p_year_start
281 AND business_group_id+0 = p_business_group_id
282 AND SUBSTR(legislative_parameters,
283 INSTR(legislative_parameters, 'TRANSFER_GRE=') +
284 LENGTH('TRANSFER_GRE=')) = TO_CHAR(cp_gre)
285 -- ADDED FOLLOWING CHECK CONDITION
286 AND action_status = 'C';
287
288 --Cursor for checking if any of the the archiver has errored for
289 --any of the assignments for federal W2
290 CURSOR c_arch_errored_asg (cp_payroll_action_id NUMBER) IS
291 SELECT '1'
292 FROM dual
293 WHERE EXISTS (SELECT '1'
294 FROM pay_assignment_actions paa
295 WHERE paa.payroll_action_id = cp_payroll_action_id
296 AND paa.action_status = 'E'
297 )
298 AND NOT EXISTS ( SELECT '1'
299 FROM pay_action_parameters
300 WHERE parameter_name = 'FORCE_MAG_REPORT'
301 AND INSTR(parameter_value, 'E') > 0
302 );
303
304 --Cursor for checking if any of the assignments have been marked for retry
305
306 CURSOR c_arch_retry_pending (cp_payroll_action_id NUMBER) IS
307 SELECT '1'
308 FROM dual
309 WHERE EXISTS (SELECT '1'
310 FROM pay_assignment_actions paa
311 WHERE paa.payroll_action_id = cp_payroll_action_id
312 AND paa.action_status = 'M')
313 AND NOT EXISTS (SELECT '1'
314 FROM pay_action_parameters
315 WHERE parameter_name = 'FORCE_MAG_REPORT'
316 AND INSTR(parameter_value, 'R') > 0
317 );
318
319 /* cursor to get user_entity_id */
320
321 CURSOR c_user_entity_id_of_bal
322 IS
323 SELECT user_entity_id
324 FROM ff_database_items fdi
325 WHERE user_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD' ;
326
327
328 /* cursor to get_context_of_tax_unit_id */
329
330 CURSOR c_context_tax_unit_id
331 IS
332 SELECT context_id
333 FROM ff_contexts
334 WHERE context_name = 'TAX_UNIT_ID';
335
336
337 /* cursor to get context of jurisdiction */
338
339 CURSOR c_context_jurisdiction
340 IS
341 SELECT context_id
342 FROM ff_contexts
343 WHERE context_name = 'JURISDICTION_CODE';
344
345
346 /* cursor to determine if archive gre has any person in State */
347
348 CURSOR c_person_in_state( cp_payroll_action_id number,
349 cp_user_entity_id number,
350 cp_context_tax_unit number,
351 cp_tax_unit_id varchar2,
352 cp_context_jursidiction number)
353 IS
354 SELECT 'Y'
355 FROM
356 ff_archive_items fai,
357 pay_assignment_actions paa,
358 pay_payroll_actions ppa
359 WHERE ppa.PAYROLL_ACTION_ID = CP_PAYROLL_ACTION_ID
360 AND ppa.payroll_action_id = paa.payroll_action_id
361 AND paa.assignment_action_id = fai.context1
362 AND fai.user_entity_id = cp_user_entity_id
363 AND fai.value > 0
364 AND EXISTS
365 ( SELECT 'Y'
366 FROM ff_archive_item_contexts faic1
367 WHERE faic1.archive_item_id = fai.archive_item_id
368 AND faic1.context_id = cp_context_tax_unit
369 AND rtrim(ltrim(faic1.context)) = cp_tax_unit_id
370 )
371 AND EXISTS
372 ( SELECT 'Y'
373 FROM ff_archive_item_contexts faic2
374 WHERE faic2.archive_item_id = fai.archive_item_id
375 AND faic2.context_id = cp_context_jursidiction
376 AND rtrim(ltrim(substr(faic2.context,1,2))) = p_state_code
377 );
378
379 /* cursor to check if the state tax Rules have been added or Not. */
380
381
382 CURSOR c_chk_archive_state_code(cp_tax_unit_id number,cp_payroll_action_id number)
383 IS
384 SELECT 'Y'
385 FROM
386 ff_archive_item_contexts con3,
387 ff_archive_item_contexts con2,
388 ff_contexts fc3,
389 ff_contexts fc2,
390 ff_archive_items target,
391 ff_database_items fdi
392 WHERE target.context1 = cp_payroll_action_id
393 /* context of payroll_action_id */
394 and fdi.user_name = 'A_FIPS_CODE_JD'
395 and target.user_entity_id = fdi.user_entity_id
396 and fc2.context_name = 'TAX_UNIT_ID'
397 and con2.archive_item_id = target.archive_item_id
398 and con2.context_id = fc2.context_id
399 and ltrim(rtrim(con2.context)) = to_char(cp_tax_unit_id)
400 and fc3.context_name = 'JURISDICTION_CODE'
401 and con3.archive_item_id = target.archive_item_id
402 and con3.context_id = fc3.context_id
403 and substr(ltrim(rtrim(con3.context)),1,2) = p_state_code;
404 /* jurisdiction code of the state */
405
406 /* cursor to get if transmitter has been been archived */
407
408 CURSOR c_transmitter IS
409 SELECT SUBSTR(legislative_parameters,INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
410 + LENGTH('TRANSFER_TRANS_LEGAL_CO_ID='),
411 (INSTR(legislative_parameters, 'TRANSFER_DATE=')
412 - INSTR(legislative_parameters, 'TRANSFER_TRANS_LEGAL_CO_ID=')
413 - LENGTH('TRANSFER_TRANS_LEGAL_CO_ID=')-1 ))
414 FROM pay_payroll_actions
415 WHERE report_type = 'W2'
416 AND effective_date = p_year_end
417 AND report_qualifier = p_state_abbrev
418 AND business_group_id = p_business_group_id
419 AND report_category = 'RT' ;
420
421
422 /* Local variables used for processing */
423 message_text VARCHAR2(32000);
424 l_gre NUMBER(15);
425 l_person NUMBER(15);
426 l_assignment NUMBER(15);
427 l_asg_effective_dt DATE;
428 l_payroll_action_id NUMBER(15);
429 l_asg_errored VARCHAR2(1);
430 l_asg_retry_pend VARCHAR2(1);
431 l_balance_exists NUMBER(1) := 0;
432 l_no_of_gres_picked NUMBER(15) := 0;
433 l_transmitter NUMBER(15) :=0;
434 l_state_tax_rules_exist CHAR(1);
435 l_person_in_state CHAR(1);
436 l_user_entity_id number;
437 l_context_jursidiction number;
438 l_context_tax_unit_id number; --ff_contexts.context_id%type;
439 l_package_error_status char(1) := 'N';
440 BEGIN
441 /* GET the context and user entity id */
442
443 OPEN c_user_entity_id_of_bal;
444 FETCH c_user_entity_id_of_bal INTO l_user_entity_id;
445 IF c_user_entity_id_of_bal%NOTFOUND THEN
446 CLOSE c_user_entity_id_of_bal;
447 l_package_error_status := 'Y';
448
449 /* message to user - Database item missing */
450 hr_utility.trace('Database item for balacne missing ');
451 message_text := 'Database item missing ';
452 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
453 hr_utility.set_message_token('MESSAGE', message_text);
454 hr_utility.raise_error;
455
456 ELSE
457 CLOSE c_user_entity_id_of_bal;
458 END IF;
459
460 OPEN c_context_tax_unit_id;
461 FETCH c_context_tax_unit_id INTO l_context_tax_unit_id;
462 IF c_context_tax_unit_id%NOTFOUND THEN
463 CLOSE c_context_tax_unit_id;
464 /* message to user -- unable to find the context_id for tax_unit_id */
465 message_text := 'Context_id value for tax_unit_id missing';
466 hr_utility.trace('Contxt_id value for tax unit id missing');
467 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
468 hr_utility.set_message_token('MESSAGE', message_text);
469 hr_utility.raise_error;
470 ELSE
471 CLOSE c_context_tax_unit_id;
472 END IF;
473
474 OPEN c_context_jurisdiction;
475 FETCH c_context_jurisdiction INTO l_context_jursidiction;
476 IF c_context_jurisdiction%NOTFOUND THEN
477 CLOSE c_context_jurisdiction;
478 /* message to User -- Unable to find to context_id for jurisdiction */
479 message_text := 'Context_id value for jurisdiction missing';
480 hr_utility.trace('Contxt_id value for jurisdction_id missing');
481 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
482 hr_utility.set_message_token('MESSAGE', message_text);
483 hr_utility.raise_error;
484
485 ELSE
486 CLOSE c_context_jurisdiction;
487 END IF;
488
489
490 /* Get the Tranmitter id of the Current Mag. W2. and check if it has
491 archived or Not for the year End process
492
493 Get the transmitter for the Mag. W2. Process. */
494
495 OPEN c_transmitter;
496 FETCH c_transmitter INTO l_transmitter;
497 IF c_transmitter%NOTFOUND THEN
498 CLOSE c_transmitter;
499 /* message to user -- transmitter has not been defined for the gre */
500 message_text := 'Transmitter Not denfined';
501 hr_utility.trace('Transmitter Not defined ');
502 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
503 hr_utility.set_message_token('MESSAGE', message_text);
504 -- hr_utility.raise_error;
505 ELSE
506 CLOSE c_transmitter;
507 END IF;
508
509 hr_utility.trace('Transmetter Setting is ' || to_char(l_transmitter));
510 hr_utility.trace('Start date ' || to_char(p_year_start));
511 hr_utility.trace('End date ' || to_char(p_year_end));
512 hr_utility.trace('Bussiness_group id ' || to_char(p_business_group_id));
513
514
515 /* Check if Archiver has been run for Transmitter */
516
517 OPEN c_gre_payroll_action (l_transmitter);
518 FETCH c_gre_payroll_action INTO l_payroll_action_id;
519
520 IF c_gre_payroll_action%NOTFOUND THEN
521 hr_utility.trace('Transmitter has not been Archvied ');
522 CLOSE c_gre_payroll_action;
523 /* message to user -- Transmitter has not been archived */
524 message_text := 'Transmitter has not been archived';
525 hr_utility.trace('Transmitter has not been archived');
526 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
527 hr_utility.set_message_token('MESSAGE', message_text);
528 -- hr_utility.raise_error;
529
530 END IF;
531 CLOSE c_gre_payroll_action;
532
533 /* end of Transmitter Checking */
534
535
536 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 10);
537
538 FOR gre_rec IN c_get_gre LOOP
539 /* set l_gre to gre Fethched */
540 l_gre := gre_rec.gre;
541 /* Get the payroll_action_id of the archvier for given GRe */
542 OPEN c_gre_payroll_action (l_gre);
543 FETCH c_gre_payroll_action INTO l_payroll_action_id;
544 IF c_gre_payroll_action%FOUND THEN
545 /* Check if any of the payroll_action_id has errored out or Not */
546 OPEN c_arch_errored_asg (l_payroll_action_id);
547 FETCH c_arch_errored_asg
548 INTO l_asg_errored;
549 IF c_arch_errored_asg%FOUND THEN
550 --Some of the assignments have not been archived
551
552
553 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 70);
554 hr_utility.set_message(801, 'PAY_72729_ASG_NOT_ARCH');
555 l_package_error_status := 'Y';
556 /* message to user -- assignment has errored out */
557 -- hr_utility.raise_error;
558 END IF;
559 CLOSE c_arch_errored_asg;
560 OPEN c_arch_retry_pending (l_payroll_action_id);
561 FETCH c_arch_retry_pending INTO l_asg_retry_pend;
562 IF c_arch_retry_pending%FOUND THEN
563 --Some of the assignments have been marked for retry
564 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 80);
565 hr_utility.set_message(801, 'PAY_72730_ASG_MARKED_FOR_RETRY');
566 l_package_error_status := 'Y';
567 -- hr_utility.raise_error;
568 END IF;
569 CLOSE c_arch_retry_pending;
570
571 /* CHECK IF THERE IS NEED TO DO STATE TAX_RULES CHECKING */
572 IF p_report_type = 'STW2' THEN
573 OPEN c_person_in_state(l_payroll_action_id ,
574 l_user_entity_id ,
575 l_context_tax_unit_id ,
576 l_gre ,
577 l_context_jursidiction );
578 FETCH c_person_in_state into l_person_in_state;
579 hr_utility.trace( to_char(l_gre) || ' GRE-ID has atleast one person in the state ' || p_state_abbrev);
580 IF c_person_in_state%FOUND THEN
581 /* Check to set if state tax rules have been defined */
582
583 OPEN c_chk_archive_state_code (l_gre,l_payroll_action_id);
584 FETCH c_chk_archive_state_code INTO l_state_tax_rules_exist;
585 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
586 hr_utility.trace('payroll_action_id - '|| to_char(l_payroll_action_id));
587 IF c_chk_archive_state_code%NOTFOUND THEN
588 --State Tax rules have not been defined
589 /* message to user -- State Tax rules not defined for the state ') */
590 message_text := 'State Tax Rules not Defind for GRE ' || to_char(l_gre) || ' for ' || P_state_abbrev;
591 insert into pay_message_lines (
592 line_sequence,
593 payroll_id,
594 message_level,
595 source_id,
596 source_type,
597 line_text)
598 values (pay_message_lines_s.nextval,
599 NULL,
600 'F', -- it's a fatal message.
601 p_pactid,
602 'P', -- payroll action level.
603 message_text);
604 commit;
605 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
606 hr_utility.set_message_token('MESSAGE', message_text);
607 l_package_error_status := 'Y';
608 --hr_utility.raise_error;
609 END IF;
610 CLOSE c_chk_archive_state_code;
611 CLOSE c_person_in_state;
612 ELSE
613 CLOSE c_person_in_state;
614 END IF; -- END OF C_person_in state not found if
615 END IF; -- REPORT TYPE = STATE
616
617 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
618 hr_utility.trace('payroll_action_id - '|| to_char(l_payroll_action_id));
619 hr_utility.trace('No. of GREs picked so far - '|| to_char(l_no_of_gres_picked));
620 l_no_of_gres_picked := l_no_of_gres_picked + 1;
621 ELSE
622 /* The GRE has not been archived so check for valid Persons in the GRE
623 who have been paid for the run YEAR
624
625 Open Cursor as per your Report type to check if GRE needs to be archived
626 or Not */
627 IF p_report_type = 'FEDW2' THEN --federal W2
628 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 99);
629 OPEN c_gre_fed(gre_rec.gre);
630 ELSIF p_report_type = 'STW2' THEN --state W2
631 OPEN c_gre_state(gre_rec.gre);
632 END IF;
633
634 LOOP --Main Loop
635 IF p_report_type = 'FEDW2' THEN
636 FETCH c_gre_fed INTO l_person
637 ,l_assignment
638 ,l_asg_effective_dt;
639 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check',20);
640 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
641 hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
642 hr_utility.trace('Person ID:' || TO_CHAR(l_person));
643 hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
644 IF c_gre_fed%NOTFOUND THEN
645 EXIT;
646 END IF;
647 ELSIF p_report_type = 'STW2' THEN
648 FETCH c_gre_state INTO l_person
649 ,l_assignment
650 ,l_asg_effective_dt;
651 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 40);
652 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
653 hr_utility.trace('Assignment ID:' || TO_CHAR(l_assignment));
654 hr_utility.trace('Person ID:' || TO_CHAR(l_person));
655 hr_utility.trace('Effective Date:' || TO_CHAR(l_asg_effective_dt));
656 IF c_gre_state%NOTFOUND THEN
657 EXIT;
658 END IF;
659 END IF; /* report type = 'STW2' and etc */
660 hr_utility.trace('pay_us_magw2_reporting.preprocess_check');
661 hr_utility.trace('GRE - '||to_char(l_gre));
662 IF p_report_type = 'FEDW2' THEN
663 IF get_balance_value('GROSS_EARNINGS_PER_GRE_YTD',
664 l_gre, p_state_abbrev, l_assignment,
665 LEAST(p_year_end, l_asg_effective_dt)) > 0 THEN
666 l_balance_exists := 1;
667 END IF;
668 ELSIF p_report_type = 'STW2' THEN
669 IF get_balance_value('GROSS_EARNINGS_PER_GRE_YTD',
670 l_gre, p_state_abbrev, l_assignment,
671 LEAST(p_year_end, l_asg_effective_dt)) > 0 AND
672 get_balance_value('SIT_GROSS_PER_JD_GRE_YTD',
673 l_gre, p_state_abbrev, l_assignment,
674 LEAST(p_year_end, l_asg_effective_dt)) > 0 THEN
675 l_balance_exists := 1;
676 END IF;
677 END IF;
678 if l_balance_exists = 1 then
679 --It means that no archived GRE was
680 --found for the Organization. This is an error.
681 if p_report_type = 'FEDW2' THEN
682 close c_gre_fed;
683 else
684 close c_gre_state;
685 end if;
686 hr_utility.set_location(
687 'pay_us_magw2_reporting.preprocess_check', 12);
688 hr_utility.set_message(801, 'PAY_72728_ARCH_GRE_NOT_FOUND');
689 /* Check for state tax rules for the gre */
690 OPEN c_chk_archive_state_code (l_gre,l_payroll_action_id);
691 FETCH c_chk_archive_state_code INTO l_state_tax_rules_exist;
692 hr_utility.trace('GRE:' || TO_CHAR(l_gre));
693 hr_utility.trace('payroll_action_id - '|| to_char(l_payroll_action_id));
694 IF c_chk_archive_state_code%NOTFOUND THEN
695 --State Tax rules have not been defined
696 /* message to user -- State Tax rules not defined for the state ') */
697 message_text := 'GRE_id ' || to_char(l_gre) || 'not archived- STR State ' || P_state_abbrev;
698 insert into pay_message_lines (
699 line_sequence,
700 payroll_id,
701 message_level,
702 source_id,
703 source_type,
704 line_text)
705 values (pay_message_lines_s.nextval,
706 NULL,
707 'F', -- it's a fatal message.
708 p_pactid,
709 'P', -- payroll action level.
710 message_text);
711 commit;
712 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
713 hr_utility.set_message_token('MESSAGE', message_text);
714 l_package_error_status := 'Y';
715 hr_utility.raise_error;
716 END IF;
717 CLOSE c_chk_archive_state_code;
718 --hr_utility.raise_error;
719 end if; /* balance exists */
720 l_no_of_gres_picked := l_no_of_gres_picked + 1;
721 l_balance_exists := 0;
722 END LOOP; --Main Loop
723 if p_report_type = 'FEDW2' THEN
724 close c_gre_fed;
725 else
726 close c_gre_state;
727 end if;
728 END IF; --end if for checking of person balance if the GRE has
729 --not been archived.
730 CLOSE c_gre_payroll_action;
731 END LOOP; /* end of for statement */
732
733 IF l_package_error_status = 'Y' THEN
734 message_text := 'Package error - Message lines have detail';
735 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
736 hr_utility.set_message_token('MESSAGE', message_text);
737 hr_utility.raise_error;
738 END IF;
739
740 IF l_no_of_gres_picked = 0 THEN
741 --It means that no archived GRE was
742 --found for the Organization. This is an error.
743 hr_utility.set_location('pay_us_magw2_reporting.preprocess_check', 110);
744 hr_utility.set_message(801, 'PAY_72728_ARCH_GRE_NOT_FOUND');
745 /* message to User -- No Gre Found for the archive */
746 hr_utility.raise_error;
747 END IF;
748 hr_utility.set_location( 'pay_us_magw2_reporting.preprocess_check', 120);
749 RETURN TRUE;
750 exception
751 when others then
752 -- add message for this
753 message_text := message_text || '+ Exception';
754 hr_utility.set_message(801, 'HR_7998_ALL_EXEMACRO_MESSAGE');
755 hr_utility.set_message_token('MESSAGE', message_text);
756 hr_utility.raise_error;
757
758 END preprocess_check;
759 --
760 ----------------------------------------------------------------------------
761 --Name
762 -- range_cursor
763 --Purpose
764 -- This procedure calls a function to check if the pre-processor has been
765 -- run for all the GREs and assignments. It then defines a SQL statement
766 -- to fetch all the people to be included in the report. This SQL statement
767 -- is used to define the 'chunks' for multi-threaded operation
768 --Arguments
769 -- p_pactid payroll action id for the report
770 -- p_sqlstr the SQL statement to fetch the people
771 ------------------------------------------------------------------------------
772 PROCEDURE range_cursor (
773 p_pactid IN NUMBER,
774 p_sqlstr OUT nocopy VARCHAR2
775 )
776 IS
777 p_year_start DATE;
778 p_year_end DATE;
779 p_business_group_id NUMBER;
780 p_state_abbrev VARCHAR2(3);
781 p_state_code VARCHAR2(2);
782 p_report_type VARCHAR2(30);
783 BEGIN
784 hr_utility.set_location( 'pay_us_magw2_reporting.range_cursor', 10);
785 get_report_parameters(
786 p_pactid,
787 p_year_start,
788 p_year_end,
789 p_state_abbrev,
790 p_state_code,
791 p_report_type,
792 p_business_group_id
793 );
794 hr_utility.set_location( 'pay_us_magw2_reporting.range_cursor', 20);
795 IF preprocess_check(p_pactid,
796 p_year_start,
797 p_year_end,
798 p_business_group_id,
799 p_state_abbrev,
800 p_state_code,
801 p_report_type
802 ) THEN
803 IF p_report_type = 'FEDW2' THEN
804 p_sqlstr := '
805 SELECT DISTINCT paf.person_id
806 FROM per_assignments_f paf,
807 pay_assignment_actions paa,
808 pay_payroll_actions ppa,
809 pay_payroll_actions ppa1
810 WHERE ppa1.payroll_action_id = :payroll_action_id
811 AND ppa.report_type = ''YREND''
812 AND ppa.business_group_id+0 = ppa1.business_group_id
813 AND ppa.effective_date = ppa1.effective_date
814 AND ppa.start_date = ppa1.start_date
815 AND paa.payroll_action_id = ppa.payroll_action_id
816 AND paa.action_status = ''C''
817 AND paf.assignment_id = paa.assignment_id
818 AND paf.effective_start_date <= ppa.effective_date
819 AND paf.effective_end_date >= ppa.start_date
820 AND paf.assignment_type = ''E''
821 AND not exists (
822 SELECT ''x''
823 FROM hr_organization_information hoi
824 WHERE hoi.organization_id = paa.tax_unit_id
825 and hoi.org_information_context =
826 ''1099R Magnetic Report Rules'')
827 ORDER BY paf.person_id
828 ';
829 hr_utility.set_location( 'pay_us_magw2_reporting.range_cursor',
830 30);
831 ELSIF p_report_type = 'STW2' THEN
832 p_sqlstr := '
833 SELECT DISTINCT
834 to_number(paa.serial_number)
835 FROM ff_archive_item_contexts faic,
836 ff_archive_items fai,
837 ff_database_items fdi,
838 pay_assignment_actions paa,
839 pay_payroll_actions ppa,
840 per_assignments_f paf,
841 pay_payroll_actions ppa1
842 WHERE
843 ppa1.payroll_action_id = :payroll_action_id
844 AND ppa.business_group_id+0 = ppa1.business_group_id
845 AND ppa1.effective_date = ppa.effective_date
846 AND ppa.report_type = ''YREND''
847 AND ppa.payroll_action_id = paa.payroll_action_id
848 and paf.assignment_id = paa.assignment_id
849 AND paf.assignment_type = ''E''
850 AND fdi.user_name = ''A_STATE_ABBREV''
851 AND fdi.user_entity_id = fai.user_entity_id
852 AND fai.archive_item_id = faic.archive_item_id
853 AND fai.context1 = paa.assignment_action_id
854 AND fai.value = ppa1.report_qualifier
855 AND paf.effective_start_date <= ppa.effective_date
856 AND paf.effective_end_date >= ppa.start_date
857 AND paa.action_status = ''C''
858 AND nvl(hr_us_w2_rep.get_w2_arch_bal(
859 paa.assignment_action_id,
860 ''A_W2_STATE_WAGES'',
861 paa.tax_unit_id,
862 faic.context , 2),0) > 0
863 AND EXISTS ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
864 select ''x''
865 from pay_us_state_tax_info_f pustif
866 where substr(faic.context,1,2) = pustif.state_code
867 and ppa.effective_date between pustif.effective_start_date
868 and pustif.effective_end_date
869 and pustif.sit_exists = ''Y'')
870 AND not exists (
871 SELECT ''x''
872 FROM hr_organization_information hoi
873 WHERE hoi.organization_id = paa.tax_unit_id
874 and hoi.org_information_context =
875 ''1099R Magnetic Report Rules''
876 )
877 order by to_number(paa.serial_number)';
878 hr_utility.set_location( 'pay_us_magw2_reporting.range_cursor',
879 40);
880 END IF;
881 END IF;
882 END range_cursor;
883 --
884 -----------------------------------------------------------------------------
885 --Name
886 -- create_assignment_act
887 --Purpose
888 -- Creates assignment actions for the payroll action associated with the
889 -- report
890 --Arguments
891 -- p_pactid payroll action for the report
892 -- p_stperson starting person id for the chunk
893 -- p_endperson last person id for the chunk
894 -- p_chunk size of the chunk
895 --Note
896 -- The procedure processes assignments in 'chunks' to facilitate
897 -- multi-threaded operation. The chunk is defined by the size and the
898 -- starting and ending person id. An interlock is also created against the
899 -- pre-processor assignment action to prevent rolling back of the archiver.
900 ----------------------------------------------------------------------------
901 --
902 PROCEDURE create_assignment_act(
903 p_pactid IN NUMBER,
904 p_stperson IN NUMBER,
905 p_endperson IN NUMBER,
906 p_chunk IN NUMBER )
907 IS
908 -- Cursor to get the assignments for state W2. Gets only those employees
909 -- which have wages for the specified state.This cursor excludes the
910 -- 1099R GREs.
911 CURSOR c_state IS
912 SELECT
913 to_number(paa.serial_number),
914 paf.assignment_id,
915 paa.tax_unit_id,
916 paf.effective_end_date,
917 paa.assignment_action_id,
918 nvl(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
919 'A_W2_STATE_WAGES',
920 paa.tax_unit_id,
921 faic.context , 2),0)
922 FROM ff_archive_item_contexts faic,
923 ff_archive_items fai,
924 ff_database_items fdi,
925 pay_assignment_actions paa,
926 pay_payroll_actions ppa,
927 per_assignments_f paf,
928 pay_payroll_actions ppa1
929 WHERE
930 ppa1.payroll_action_id = p_pactid
931 and ppa.business_group_id+0 = ppa1.business_group_id
932 and ppa1.effective_date = ppa.effective_date
933 and ppa.report_type = 'YREND'
934 and ppa.payroll_action_id = paa.payroll_action_id
935 and paf.assignment_id = paa.assignment_id
936 and paf.assignment_type = 'E'
937 and fdi.user_name = 'A_STATE_ABBREV'
938 and fdi.user_entity_id = fai.user_entity_id
939 and fai.archive_item_id = faic.archive_item_id
940 and fai.context1 = paa.assignment_action_id
941 and fai.value = ppa1.report_qualifier
942 and paf.effective_start_date <= ppa.effective_date
943 and paf.effective_end_date >= ppa.start_date
944 and paa.action_status = 'C'
945 and paf.person_id BETWEEN p_stperson AND p_endperson
946 and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
947 'A_W2_STATE_WAGES',
948 paa.tax_unit_id,
949 faic.context , 2),0) > 0
950 and exists ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
951 select 'x'
952 from pay_us_state_tax_info_f pustif
953 where substr(faic.context,1,2) = pustif.state_code
954 and ppa.effective_date between pustif.effective_start_date
955 and pustif.effective_end_date
956 and pustif.sit_exists = 'Y'
957 )
958 and not exists
959 (
960 select 'x'
961 from hr_organization_information hoi
962 WHERE hoi.organization_id = paa.tax_unit_id
963 and hoi.org_information_context ='1099R Magnetic Report Rules'
964 )
965 ORDER BY 1, 3, 4 DESC, 2
966 FOR UPDATE OF paf.assignment_id;
967 -- Cursor to get the assignments for federal W2. Excludes 1099R GREs.
968 CURSOR c_federal IS
969 SELECT paf.person_id,
970 paf.assignment_id,
971 Paa.tax_unit_id, --TO_NUMBER(hsck.segment1),
972 paf.effective_end_date,
973 paa.assignment_action_id
974 FROM pay_payroll_actions ppa,
975 pay_assignment_actions paa,
976 --hr_soft_coding_keyflex hsck,
977 per_assignments_f paf,
978 pay_payroll_actions ppa1
979 WHERE ppa1.payroll_action_id = p_pactid
980 AND ppa.report_type = 'YREND'
981 AND ppa.business_group_id+0 = ppa1.business_group_id
982 AND ppa.effective_date = ppa1.effective_date
983 AND ppa.start_date = ppa1.start_date
984 AND paa.payroll_action_id = ppa.payroll_action_id
985 AND paa.action_status = 'C'
986 AND paf.assignment_id = paa.assignment_id
987 AND paf.person_id BETWEEN p_stperson AND p_endperson
988 AND paf.assignment_type = 'E'
989 AND paf.effective_start_date <= ppa.effective_date
990 AND paf.effective_end_date >= ppa.start_date
991 --AND hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
992 --AND hsck.segment1 = paa.tax_unit_id
993 --AND hsck.segment1 NOT IN (
994 AND not exists (
995 SELECT 'x'
996 FROM hr_organization_information hoi
997 WHERE hoi.organization_id = paa.tax_unit_id
998 and hoi.org_information_context = '1099R Magnetic Report Rules')
999 ORDER BY 1, 3, 4 DESC, 2
1000 FOR UPDATE OF paf.assignment_id;
1001 cursor csr_get_fed_wages(p_assignment_action_id number,
1002 p_tax_unit_id number) is
1003 select to_number(fai.value)
1004 from ff_archive_item_contexts faic,
1005 ff_archive_items fai,
1006 ff_contexts fc,
1007 ff_database_items fdi
1008 where fdi.user_name = 'A_GROSS_EARNINGS_PER_GRE_YTD'
1009 and fc.context_name = 'TAX_UNIT_ID'
1010 and fai.context1 = p_assignment_action_id
1011 and fai.user_entity_id = fdi.user_entity_id
1012 and faic.archive_item_id = fai.archive_item_id
1013 and faic.context_id = fc.context_id
1014 and faic.context = p_tax_unit_id
1015 and faic.sequence_no = 1;
1016 --local variables
1017 l_year_start DATE;
1018 l_year_end DATE;
1019 l_effective_end_date DATE;
1020 l_state_abbrev VARCHAR2(3);
1021 l_state_code VARCHAR2(2);
1022 l_report_type VARCHAR2(30);
1023 l_business_group_id NUMBER;
1024 l_person_id NUMBER;
1025 l_prev_person_id NUMBER;
1026 l_assignment_id NUMBER;
1027 l_assignment_action_id NUMBER;
1028 l_value NUMBER;
1029 l_tax_unit_id NUMBER;
1030 l_prev_tax_unit_id NUMBER;
1031 lockingactid NUMBER;
1032 l_group_by_gre BOOLEAN;
1033 l_w2_box17 NUMBER; --SIT Wages
1034 BEGIN
1035 -- Get the report parameters. These define the report being run.
1036 hr_utility.set_location( 'pay_us_magw2_reporting.create_assignement_act',
1037 10);
1038 get_report_parameters(
1039 p_pactid,
1040 l_year_start,
1041 l_year_end,
1042 l_state_abbrev,
1043 l_state_code,
1044 l_report_type,
1045 l_business_group_id
1046 );
1047 --Currently all reports group by GRE
1048 l_group_by_gre := TRUE;
1049 --Open the appropriate cursor
1050 hr_utility.set_location( 'pay_us_magw2_reporting.create_assignement_act',
1051 20);
1052 IF l_report_type = 'FEDW2' THEN
1053 OPEN c_federal;
1054 ELSIF l_report_type = 'STW2' THEN
1055 OPEN c_state;
1056 END IF;
1057 LOOP
1058 IF l_report_type = 'FEDW2' THEN
1059 FETCH c_federal INTO l_person_id,
1060 l_assignment_id,
1061 l_tax_unit_id,
1062 l_effective_end_date,
1063 l_assignment_action_id;
1064 hr_utility.set_location(
1065 'pay_us_magw2_reporting.create_assignement_act', 30);
1066 EXIT WHEN c_federal%NOTFOUND;
1067 ELSIF l_report_type = 'STW2' THEN
1068 FETCH c_state INTO l_person_id,
1069 l_assignment_id,
1070 l_tax_unit_id,
1071 l_effective_end_date,
1072 l_assignment_action_id,
1073 l_w2_box17;
1074 hr_utility.set_location(
1075 'pay_us_magw2_reporting.create_assignement_act', 40);
1076 EXIT WHEN c_state%NOTFOUND;
1077 END IF;
1078 --Based on the groupin criteria, check if the record is the same
1079 --as the previous record.
1080 --Grouping by GRE requires a unique person/GRE combination for
1081 --each record.
1082 IF ((l_group_by_gre AND
1083 l_person_id = l_prev_person_id AND
1084 l_tax_unit_id = l_prev_tax_unit_id) OR
1085 (NOT l_group_by_gre AND
1086 l_person_id = l_prev_person_id)) THEN
1087 --Do Nothing
1088 hr_utility.set_location(
1089 'pay_us_magw2_reporting.create_assignement_act', 50);
1090 NULL;
1091 ELSE
1092 --Create the assignment action for the record
1093 hr_utility.trace('Assignment Fetched - ');
1094 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
1095 hr_utility.trace('Person Id : '|| to_char(l_person_id));
1096 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
1097 hr_utility.trace('Effective End Date : '||
1098 to_char(l_effective_end_date));
1099 IF l_report_type = 'FEDW2' then
1100 open csr_get_fed_wages(l_assignment_action_id, l_tax_unit_id);
1101 fetch csr_get_fed_wages into l_value;
1102 if csr_get_fed_wages%NOTFOUND then
1103 l_value := 0;
1104 end if;
1105 close csr_get_fed_wages;
1106 END IF;
1107 IF (l_report_type = 'FEDW2' and l_value > 0) OR
1108 (l_report_type = 'STW2') then
1109 SELECT pay_assignment_actions_s.nextval
1110 INTO lockingactid
1111 FROM dual;
1112 hr_utility.set_location(
1113 'pay_us_magw2_reporting.create_assignement_act', 60);
1114 hr_nonrun_asact.insact(lockingactid, l_assignment_id, p_pactid,
1115 p_chunk, l_tax_unit_id);
1116 hr_utility.set_location(
1117 'pay_us_magw2_reporting.create_assignement_act', 70);
1118 --update serial number for highly compensated people for the
1119 --state W2.
1120 IF l_report_type = 'STW2' THEN
1121 hr_utility.set_location(
1122 'pay_us_magw2_reporting.create_assignement_act', 80);
1123 IF l_w2_box17 > 9999999.99 THEN
1124 UPDATE pay_assignment_actions
1125 SET serial_number = 999999
1126 WHERE assignment_action_id = lockingactid;
1127 END IF;
1128 END IF;
1129 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
1130 hr_utility.set_location(
1131 'pay_us_magw2_reporting.create_assignement_act', 90);
1132 hr_utility.trace('Interlock Created - ');
1133 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
1134 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
1135 --Store the current person/GRE for comparision during the
1136 --next iteration.
1137 l_prev_person_id := l_person_id;
1138 l_prev_tax_unit_id := l_tax_unit_id;
1139 END IF;
1140 END IF;
1141 END LOOP;
1142 IF l_report_type = 'FEDW2' THEN
1143 CLOSE c_federal;
1144 ELSIF l_report_type = 'STW2' THEN
1145 CLOSE c_state;
1146 END IF;
1147 END create_assignment_act;
1148
1149 --begin
1150 -- hr_utility.trace_on(NULL, 'VIP');
1151
1152 END pay_us_magw2_reporting;
[Home] [Help]