1 PACKAGE BODY pay_us_w2c_reporting_utils AS
2 /* $Header: payusw2creputils.pkb 120.1.12020000.3 2013/04/12 05:34:48 skchalla ship $ */
3 /*
4 =========================================================================+
5 Copyright (c) 1993 Oracle Corporation
6 Redwood Shores, California, USA
7 All rights reserved.
8 +=========================================================================+
9 Name
10 pay_us_w2c_reporting_utils
11
12 File Name
13 payusw2creputils.pkb
14
15 Purpose
16 The purpose of this package is to support the generation of magnetic
17 tape in MMREF - 2 Format. This magnetic tapes are for US legilsative
18 requirements.
19
20 Notes
21 The generation of each Federal W-2c magnetic tape report is a two stage
22 process i.e.
23
24 1. Check if the "Employee W-2c Report" is not run for a
25 "W-2c Pre-Process". If not, then error out without processing further.
26
27 2. Create a payroll action for the report. Identify all the assignments
28 to be reported and record an assignment action against the payroll
29 action for each one of them.
30
31 3. Run the generic magnetic tape process which will drive off the data
32 created in stage two. This will result in the production of a
33 structured ascii file which can be transferred to magnetic tape and
34 sent to the relevant authority.
35
36 History
37 Date Author Verion Bug Details
38 -------------------------------------------------------------------------
39 22-OCT-03 ppanda 115.0 Created
40 02-DEC-03 ppanda 115.3 3284445 Value reported for Originally reported
41 using wrong employee
42 3275145 Federal W-2c Mag is not logging any message
43 when no W-2c paper is picked up by process.
44
45 11-DEC-03 ppanda 115.4 3313954 Originally reported value is not correct
46 when W-2c mag is run after first correction
47 reported in W-2c mag or Paper
48 31-dec-03 jgoswami 115.6 commented show_error for gscc failure.
49 14-MAR-2005 sackumar 115.9 4222032 Change in the Range Cursor removing redundant
50 use of bind Variable (:payroll_action_id)
51 29-MAR-2005 sackumar 115.10 4222032 Removing GSCC Errors
52 07-Nov-2008 asgugupt 115.11 7504239 Have put Order by in action creation
53 and distinct clause in cursor
54 c_w2c_paper_not_locked
55 20-APR-2012 skchalla 115.12 13961934 Added an order by clause to the cursor get_w2c_mag_assignments,
56 to avoid muliple actions created for a single Person ID.
57 09-Apr-2013 skchalla 115.13 16617759 Modified the code to pickup the actions for W-2c PDF
58 along with the W-2c Paper
59 =========================================================================
60 */
61
62 /******************************************************************
63 ** Package Local Variables
64 ******************************************************************/
65
66 gv_package varchar2(50) := 'pay_us_w2c_reporting_utils';
67
68 ---------------------------------------------------------------------------
69 -- Name : bal_db_item
70 -- Purpose : Given the name of a balance DB item as would be seen in a
71 -- fast formula it returns the defined_balance_id of the
72 -- balance it represents.
73 -- Arguments
74 -- INPUT : p_db_item_name
75 -- returns : l_defined_balance_id
76 --
77 -- Notes
78 -- A defined_balance_id is required by the PLSQL balance function.
79 -----------------------------------------------------------------------------
80 FUNCTION bal_db_item ( p_db_item_name IN VARCHAR2
81 ) RETURN NUMBER IS
82 -- Get the defined_balance_id for the specified balance DB item.
83 CURSOR csr_defined_balance IS
84 SELECT TO_NUMBER(UE.creator_id)
85 FROM ff_database_items DI,
86 ff_user_entities UE
87 WHERE DI.user_name = p_db_item_name
88 AND UE.user_entity_id = DI.user_entity_id
89 AND UE.creator_type = 'B'
90 AND UE.legislation_code = 'US';
91 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
92 BEGIN
93 hr_utility.set_location
94 (gv_package || '.bal_db_item - opening cursor', 10);
95 -- Open the cursor
96 OPEN csr_defined_balance;
97 -- Fetch the value
98 FETCH csr_defined_balance
99 INTO l_defined_balance_id;
100 IF csr_defined_balance%NOTFOUND THEN
101 CLOSE csr_defined_balance;
102 hr_utility.set_location
103 (gv_package || '.bal_db_item - no rows found ', 20);
104 hr_utility.raise_error;
105 ELSE
106 hr_utility.set_location
107 (gv_package || '.bal_db_item - Row fetched from cursor', 30);
108 CLOSE csr_defined_balance;
109 END IF;
110 /* Return the value to the call */
111 RETURN (l_defined_balance_id);
112 END bal_db_item;
113
114
115 /*******************************************************************
116 ** Procedure return the values for the Payroll Action of
117 ** the "Mark Paper W-2c and Exclude From Future Tapes" process.
118 ** This is used in Range Code and Action Creation.
119 ******************************************************************/
120
121 PROCEDURE get_payroll_action_info
122 (
123 p_payroll_action_id in number,
124 p_start_date in out nocopy date,
125 p_end_date in out nocopy date,
126 p_report_type in out nocopy varchar2,
127 p_report_qualifier in out nocopy varchar2,
128 p_business_group_id in out nocopy number
129 )
130 IS
131 cursor c_payroll_action(cp_payroll_action_id in number) is
132 select ppa.start_date
133 ,ppa.effective_date
134 ,ppa.report_type
135 ,ppa.report_qualifier
136 ,ppa.business_group_id
137 from pay_payroll_actions ppa
138 where payroll_action_id = cp_payroll_action_id;
139
140 ld_start_date date;
141 ld_end_date date;
142 lv_report_type varchar2(50);
143 lv_report_qualifier varchar2(50);
144 ln_business_group_id number;
145
146 BEGIN
147 hr_utility.set_location(gv_package || '.get_payroll_action_info', 10);
148
149 open c_payroll_action(p_payroll_action_id);
150 fetch c_payroll_action into
151 ld_start_date,
152 ld_end_date,
153 lv_report_type,
154 lv_report_qualifier,
155 ln_business_group_id;
156 if c_payroll_action%notfound then
157 hr_utility.set_location( gv_package || '.get_payroll_action_info',20);
158 hr_utility.trace('Payroll Action '||to_char(p_payroll_action_id)||' Not found');
159 hr_utility.raise_error;
160 end if;
161 close c_payroll_action;
162 hr_utility.set_location(gv_package || '.get_payroll_action_info', 30);
163 p_start_date := ld_start_date;
164 p_end_date := ld_end_date;
165 p_report_type := lv_report_type;
166 p_report_qualifier := lv_report_qualifier;
167 p_business_group_id := ln_business_group_id;
168 hr_utility.set_location(gv_package || '.get_payroll_action_info', 40);
169 END get_payroll_action_info;
170
171 -------------------------------------------------------------------------
172 -- Name : get_balance_value
173 --
174 -- Purpose
175 -- Get the value of the specified balance item
176 -- Arguments
177 -- p_balance_name Name of the balnce
178 -- p_tax_unit_id GRE name for the context
179 -- p_state_code State for context
180 -- p_assignment_id Assignment for whom the balance is to be
181 -- retrieved
182 -- p_effective_date effective_date
183 --
184 -- Note
185 -- This procedure set is a wrapper for setting the GRE/Jurisdiction context
186 -- needed by the pay_balance_pkg.get_value to get the actual balance
187 -------------------------------------------------------------------------
188 FUNCTION get_balance_value (p_balance_name VARCHAR2,
189 p_tax_unit_id NUMBER,
190 p_state_abbrev VARCHAR2,
191 p_assignment_id NUMBER,
192 p_effective_date DATE
193 ) RETURN NUMBER IS
194 l_jurisdiction_code VARCHAR2(20);
195 BEGIN
196 hr_utility.set_location(gv_package || '.get_balance_value', 10);
197 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
198 IF p_state_abbrev <> 'FED' THEN
199 SELECT jurisdiction_code
200 INTO l_jurisdiction_code
201 FROM pay_state_rules
202 WHERE state_code = p_state_abbrev;
203 hr_utility.set_location(gv_package || '.get_balance_value', 20);
204 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction_code);
205 END IF;
206 hr_utility.trace('Balance Name : '||p_balance_name);
207 hr_utility.trace('Context');
208 hr_utility.trace(' Tax Unit Id : '|| p_tax_unit_id);
209 hr_utility.trace(' Jurisdiction: '|| l_jurisdiction_code);
210 hr_utility.set_location('pay_us_mmref_reporting.get_balance_value', 30);
211 RETURN pay_balance_pkg.get_value(bal_db_item(p_balance_name),
212 p_assignment_id,
213 p_effective_date);
214 END get_balance_value;
215
216 --------------------------------------------------------------------------
217 --Name
218 -- preprocess_check
219 -- Purpose
220 -- This function checks whether W-2c paper genrated and waiting for W-2c mag
221 -- to pick up for processing. If it doesn't find even a single W-2c paper
222 -- assignment action, it logs a message for user
223 -- Arguments
224 -- p_pactid payroll_action_id for the report
225 -- p_year_start start date of the period for which the report
226 -- has been requested
227 -- p_year_end end date of the period
228 -- p_business_group_id business group for which the report is being run
229 --
230 --Notes
231 --
232 --
233 --
234 -----------------------------------------------------------------------------
235 FUNCTION preprocess_check
236 (
237 p_pactid IN NUMBER,
238 p_year_start IN DATE,
239 p_year_end IN DATE,
240 p_business_group_id IN NUMBER
241 )
242 RETURN BOOLEAN IS
243 lb_return_value BOOLEAN := TRUE;
244 ln_count_of_w2c_paper number := 0;
245 lv_message_text varchar2(200) := '';
246 lv_message_preprocess varchar2(200) := '';
247
248 CURSOR c_w2c_paper_exist (cpn_business_group_id number,
249 cpd_start_date date,
250 cpd_end_date date)
251 IS
252 select paa.assignment_action_id
253 from pay_assignment_actions paa,
254 per_all_assignments_f paf,
255 pay_payroll_actions ppa
256 where ppa.business_group_id = cpn_business_group_id
257 and ppa.effective_date between cpd_start_date and cpd_end_date
258 and ppa.action_type = 'X'
259 and ppa.report_type IN ('W-2C PAPER','W2C_XML')
260 and ppa.action_status = 'C'
261 and ppa.payroll_action_id = paa.payroll_action_id
262 and paf.assignment_id = paa.assignment_id
263 and paf.effective_start_date <= ppa.effective_date
264 and paf.effective_end_date >= ppa.start_date
265 and paf.assignment_type = 'E'
266 and not exists
267 (select 'x'
268 from pay_Action_interlocks pai,
269 pay_assignment_actions paa1,
270 pay_payroll_actions ppa1
271 where pai.locked_action_id = paa.assignment_action_id
272 and paa1.assignment_action_id = pai.locking_action_id
273 and ppa1.payroll_action_id = paa1.payroll_action_id
274 and ppa1.effective_date between cpd_start_date and cpd_end_date
275 and ppa1.action_type = 'X'
276 and ppa1.report_type = 'MARK_W2C_PAPER'
277 and ppa1.action_status = 'C')
278 and not exists
279 (select 'x'
280 from pay_Action_interlocks pai,
281 pay_assignment_actions paa1,
282 pay_payroll_actions ppa1
283 where pai.locked_action_id = paa.assignment_action_id
284 and paa1.assignment_action_id = pai.locking_action_id
285 and ppa1.payroll_action_id = paa1.payroll_action_id
286 and ppa1.effective_date between cpd_start_date and cpd_end_date
287 and ppa1.action_type = 'X'
288 and ppa1.report_type = 'W2C'
289 and ppa1.report_qualifier = 'FED'
290 and ppa1.action_status = 'C');
291 BEGIN
292 hr_utility.set_location(gv_package || '.preprocess_check', 10);
293 lv_message_preprocess := 'Pre-Process check';
294 --
295 -- Determine whether any W-2c paper assignment action exist for W-2c mag
296 -- pick up. If not log a message for user
297 --
298 OPEN c_w2c_paper_exist(p_business_group_id,
299 p_year_start,
300 p_year_end);
301 FETCH c_w2c_paper_exist INTO ln_count_of_w2c_paper;
302 if c_w2c_paper_exist%ROWCOUNT = 0 or c_w2c_paper_exist%NOTFOUND
303 then
304 hr_utility.set_location(gv_package || '.preprocess_check', 20);
305 /* message to user -- unable to find any W-2c Paper report for
306 genrating W-2c Mag */
307 lv_message_text := 'No W-2c paper printed for processing W-2c Mag Tape';
308 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
309 pay_core_utils.push_token('record_name',lv_message_preprocess);
310 pay_core_utils.push_token('description',lv_message_text);
311 lb_return_value := FALSE;
312 CLOSE c_w2c_paper_exist;
313 hr_utility.set_location(gv_package || '.preprocess_check', 30);
314 else
315 CLOSE c_w2c_paper_exist;
316 lb_return_value := TRUE;
317 hr_utility.set_location(gv_package || '.preprocess_check', 30);
318 end if;
319 hr_utility.set_location(gv_package || '.preprocess_check', 40);
320 return lb_return_value;
321 END preprocess_check;
322 -- End of Function Preprocess_Check
323
324 /*****************************************************************************
325 Name : get_eoy_action_info
326 Purpose : This returns the Payroll Action level
327 information for YREND Archiver.
328 Arguments : p_w2c_eff_date - End date of W2C Mag Process
329 p_w2c_tax_unit_id - Tax Unit Id
330 p_payroll_action_id - Payroll_Action_id of EOY
331
332 ******************************************************************************/
333 PROCEDURE get_eoy_action_info(p_eoy_effective_date in date
334 ,p_eoy_tax_unit_id in number
335 ,p_assignment_id in number
336 ,p_eoy_pactid out nocopy number
337 ,p_eoy_asg_actid out nocopy number
338 )
339 IS
340 CURSOR get_eoy_info(cp_w2c_eff_date in date
341 ,cp_w2c_tax_unit_id in number
342 ,cp_assignment_id in number) is
343 select ppa.payroll_action_id,
344 paa.assignment_action_id
345 from pay_assignment_actions paa,
346 pay_payroll_actions ppa
347 where ppa.payroll_action_id = paa.payroll_action_id
348 and ppa.report_type = 'YREND'
349 and ppa.effective_date = cp_w2c_eff_date
350 and paa.assignment_id = cp_assignment_id
351 and pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(
352 ppa.payroll_action_id,
353 'TRANSFER_GRE') = cp_w2c_tax_unit_id;
354 ln_eoy_pactid number :=0;
355 ln_eoy_asg_actid number :=0;
356 BEGIN
357 hr_utility.set_location(gv_package || '.get_eoy_action_info', 10);
358 hr_utility.trace('Effective Date '||to_char(p_eoy_effective_date,'dd-mon-yyyy') );
359 hr_utility.trace('Tax Unit Id '||to_char(p_eoy_tax_unit_id));
360 hr_utility.trace('Entered get_eoy_action_info');
361 open get_eoy_info(p_eoy_effective_date
362 ,p_eoy_tax_unit_id
363 ,p_assignment_id
364 );
365 hr_utility.set_location(gv_package || '.get_eoy_action_info', 20);
366 hr_utility.trace('Opened get_eoy_info');
367
368 fetch get_eoy_info into ln_eoy_pactid,
369 ln_eoy_asg_actid;
370 hr_utility.trace('Fetched get_eoy_info ');
371 close get_eoy_info;
372
373 hr_utility.trace('Closed get_eoy_info ');
374 p_eoy_pactid := ln_eoy_pactid;
375 p_eoy_asg_actid := ln_eoy_asg_actid;
376 hr_utility.trace('ln_eoy_pactid = ' || to_char(ln_eoy_pactid));
377 hr_utility.trace('ln_eoy_asg_actid = ' || to_char(ln_eoy_asg_actid));
378 hr_utility.set_location(gv_package || '.get_eoy_action_info', 30);
379 hr_utility.trace('Leaving get_eoy_action_info');
380 EXCEPTION
381 when others then
382 hr_utility.trace('Error in ' || gv_package || '.get_eoy_action_info' ||
383 to_char(sqlcode) || '-' || sqlerrm);
384 hr_utility.set_location(gv_package || '.get_eoy_action_info', 40);
385 raise hr_utility.hr_error;
386 END get_eoy_action_info;
387
388 /******************************************************************
389 ** Range Code to pick all the distinct assignment_ids
390 ** that need to be marked as submitted to governement.
391 *******************************************************************/
392 PROCEDURE w2c_mag_range_cursor( p_payroll_action_id in number
393 ,p_sqlstr out nocopy varchar2)
394 IS
395
396 ld_start_date date;
397 ld_end_date date;
398 lv_report_type varchar2(30);
399 lv_report_qualifier varchar2(30);
400 ln_business_group_id number;
401 lv_sql_string varchar2(10000);
402 BEGIN
403 hr_utility.set_location(gv_package || '.w2c_mag_range_cursor', 10);
404 get_payroll_action_info ( p_payroll_action_id
405 ,ld_start_date
406 ,ld_end_date
407 ,lv_report_type
408 ,lv_report_qualifier
409 ,ln_business_group_id
410 );
411 hr_utility.trace('ld_start_date = ' || ld_start_date);
412 hr_utility.trace('ld_end_date = ' || ld_end_date);
413 hr_utility.trace('lv_report_type = ' || lv_report_type);
414 hr_utility.trace('lv_report_qualifier = ' || lv_report_qualifier);
415 hr_utility.trace('ln_business_group_id = ' || ln_business_group_id);
416
417 hr_utility.set_location(gv_package || '.w2c_mag_range_cursor', 20);
418
419 if preprocess_check ( p_payroll_action_id
420 ,ld_start_date
421 ,ld_end_date
422 ,ln_business_group_id
423 )
424 then
425 hr_utility.trace('W-2c Assignment picked up for processing W-2c Mag' );
426 else
427 hr_utility.trace('No W-2c Assignment picked up for processing W-2c Mag');
428 end if;
429 --{
430
431 hr_utility.set_location(gv_package || '.w2c_mag_range_cursor', 30);
432 if (lv_report_type = 'W2C' and lv_report_qualifier = 'FED') then
433 hr_utility.set_location(gv_package || '.w2c_mag_range_cursor', 40);
434 lv_sql_string :=
435 'select distinct paf.person_id
436 from pay_assignment_actions paa,
437 per_all_assignments_f paf,
438 pay_payroll_actions ppa
439 where ppa.business_group_id = '|| ln_business_group_id || '
440 and ppa.effective_date between to_date(''' ||
441 to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
442 and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
443 and ppa.action_type = ''X''
444 and ppa.report_type IN (''W-2C PAPER'',''W2C_XML'')
445 and ppa.action_status = ''C''
446 and ppa.payroll_action_id = paa.payroll_action_id
447 and paf.assignment_id = paa.assignment_id
448 and paf.effective_start_date <= ppa.effective_date
449 and paf.effective_end_date >= ppa.start_date
450 and paf.assignment_type = ''E''
451 and :payroll_action_id is not null
452 and not exists
453 (select ''x'' from pay_Action_interlocks pai,
454 pay_assignment_actions paa1,
455 pay_payroll_actions ppa1
456 where pai.locked_action_id = paa.assignment_action_id
457 and paa1.assignment_action_id = pai.locking_action_id
458 and ppa1.payroll_action_id = paa1.payroll_action_id
459 and ppa1.effective_date between to_date(''' ||
460 to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
461 and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
462 and ppa1.action_type = ''X''
463 and ppa1.report_type = ''MARK_W2C_PAPER''
464 and ppa1.action_status = ''C'')
465 and not exists
466 (select ''x'' from pay_Action_interlocks pai,
467 pay_assignment_actions paa1,
468 pay_payroll_actions ppa1
469 where pai.locked_action_id = paa.assignment_action_id
470 and paa1.assignment_action_id = pai.locking_action_id
471 and ppa1.payroll_action_id = paa1.payroll_action_id
472 and ppa1.effective_date between to_date(''' ||
473 to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
474 and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
475 and ppa1.action_type = ''X''
476 and ppa1.report_type = ''W2C''
477 and ppa1.report_qualifier = ''FED''
478 and ppa1.action_status = ''C'')
479 order by paf.person_id';
480 p_sqlstr := lv_sql_string;
481 hr_utility.set_location(gv_package || '.w2c_mag_range_cursor', 50);
482 --hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
483 hr_utility.trace('length of p_sqlstr <' || to_char(length(p_sqlstr))||'>' );
484 hr_utility.trace('Procedure w2c_mag_range_cursor completed successfully');
485 else
486 hr_utility.trace('Procedure w2c_mag_range_cursor Unsucessful ... ');
487 end if;
488 --}
489
490 end w2c_mag_range_cursor;
491
492 /*******************************************************************
493 ** Action Creation Code to create assignment actions for all the
494 ** the assignment_ids that are corrected and not yet reported to
495 ** governement
496 *******************************************************************/
497 PROCEDURE w2c_mag_action_creation( p_payroll_action_id in number
498 ,p_start_person_id in number
499 ,p_end_person_id in number
500 ,p_chunk in number)
501 IS
502 -- This cursor would be used to determine whether a future correction is already
503 -- reported to govt
504 cursor w2c_future_correction_reported(cp_business_group_id in number
505 ,cp_start_date in date
506 ,cp_end_date in date
507 ,cp_start_person_id in number
508 ,cp_end_person_id in number
509 )
510 IS
511 select distinct paa.assignment_id,
512 paf.person_id
513 from pay_assignment_actions paa,
514 per_all_assignments_f paf,
515 pay_payroll_actions ppa
516 where ppa.business_group_id = cp_business_group_id
517 and ppa.effective_date between cp_start_date and cp_end_date
518 and ppa.action_type = 'X'
519 and ppa.report_type IN ('W-2C PAPER','W2C_XML')
520 and ppa.action_status = 'C'
521 and ppa.payroll_action_id = paa.payroll_action_id
522 and paf.assignment_id = paa.assignment_id
523 and paf.effective_start_date <= ppa.effective_date
524 and paf.effective_end_date >= ppa.start_date
525 and paf.assignment_type = 'E'
526 and paf.person_id between cp_start_person_id
527 and cp_end_person_id
528 and not exists
529 (select 'x' from pay_Action_interlocks pai,
530 pay_assignment_actions paa1,
531 pay_payroll_actions ppa1
532 where pai.locked_action_id = paa.assignment_action_id
533 and paa1.assignment_action_id = pai.locking_action_id
534 and ppa1.payroll_action_id = paa1.payroll_action_id
535 and ppa1.effective_date between cp_start_date and cp_end_date
536 and ppa1.action_type = 'X'
537 and ppa1.report_type = 'MARK_W2C_PAPER'
538 and ppa1.report_category = 'RT'
539 and ppa1.action_status = 'C')
540 and not exists
541 (select 'x' from pay_Action_interlocks pai,
542 pay_assignment_actions paa1,
543 pay_payroll_actions ppa1
544 where pai.locked_action_id = paa.assignment_action_id
545 and paa1.assignment_action_id = pai.locking_action_id
546 and ppa1.payroll_action_id = paa1.payroll_action_id
547 and ppa1.effective_date between cp_start_date and cp_end_date
548 and ppa1.action_type = 'X'
549 and ppa1.report_type = 'W2C'
550 and ppa1.report_qualifier = 'FED'
551 and ppa1.report_category = 'RM'
552 and ppa1.action_status = 'C'
553 )
554 and exists
555 (select 'x'
556 from pay_Action_interlocks pai,
557 pay_assignment_actions paa1,
558 pay_assignment_actions paa2,
559 pay_payroll_actions ppa1,
560 pay_payroll_actions ppa2
561 where paa2.assignment_Action_id = pai.locked_action_id
562 and paa1.assignment_action_id = pai.locking_action_id
563 and ppa1.payroll_action_id = paa1.payroll_action_id
564 and ppa1.effective_date between cp_start_date and cp_end_date
565 and ppa1.action_type = 'X'
566 and ppa1.report_type = 'W2C'
567 and ppa1.report_qualifier = 'FED'
568 and ppa1.report_category = 'RM'
569 and ppa1.action_status = 'C'
570 and paa2.assignment_id = paa.assignment_id
571 and ppa2.action_type = 'X'
572 and ppa2.report_type IN ('W-2C PAPER','W2C_XML')
573 and ppa2.action_status = 'C'
574 and ppa2.payroll_action_id = paa2.payroll_action_id
575 and paa2.assignment_Action_id > paa.assignment_Action_id
576 and ppa2.effective_date between cp_start_date and cp_end_date
577 );
578
579 cursor get_w2c_mag_assignments (cp_business_group_id in number
580 ,cp_start_date in date
581 ,cp_end_date in date
582 ,cp_start_person_id in number
583 ,cp_end_person_id in number)
584 IS
585 select paa.assignment_id,
586 paa.tax_unit_id,
587 paf.person_id,
588 paa.assignment_Action_id, -- Maximum Assignment Action_ID
589 to_number(substr(paa.serial_number,1,15)) w2c_pp_asg_actid,
590 to_number(substr(paa.serial_number,16,30)) w2c_pp_locked_actid
591 from pay_assignment_actions paa,
592 per_all_assignments_f paf,
593 pay_payroll_actions ppa
594 where ppa.business_group_id = cp_business_group_id
595 and ppa.effective_date between cp_start_date and cp_end_date
596 and ppa.action_type = 'X'
597 and ppa.report_type IN ('W-2C PAPER','W2C_XML')
598 and ppa.action_status = 'C'
599 and ppa.payroll_action_id = paa.payroll_action_id
600 and paf.assignment_id = paa.assignment_id
601 and paf.effective_start_date <= ppa.effective_date
602 and paf.effective_end_date >= ppa.start_date
603 and paf.assignment_type = 'E'
604 and paf.person_id between cp_start_person_id
605 and cp_end_person_id
606 and not exists
607 (select 'x' from pay_Action_interlocks pai,
608 pay_assignment_actions paa1,
609 pay_payroll_actions ppa1
610 where pai.locked_action_id = paa.assignment_action_id
611 and paa1.assignment_action_id = pai.locking_action_id
612 and ppa1.payroll_action_id = paa1.payroll_action_id
613 and ppa1.effective_date between cp_start_date
614 and cp_end_date
615 and ppa1.action_type = 'X'
616 and ppa1.report_type = 'MARK_W2C_PAPER'
617 and ppa1.report_category = 'RT'
618 and ppa1.action_status = 'C')
619 and not exists
620 (select 'x' from pay_Action_interlocks pai,
621 pay_assignment_actions paa1,
622 pay_payroll_actions ppa1
623 where pai.locked_action_id = paa.assignment_action_id
624 and paa1.assignment_action_id = pai.locking_action_id
625 and ppa1.payroll_action_id = paa1.payroll_action_id
626 and ppa1.effective_date between cp_start_date and cp_end_date
627 and ppa1.action_type = 'X'
628 and ppa1.report_type = 'W2C'
629 and ppa1.report_qualifier = 'FED'
630 and ppa1.report_category = 'RM'
631 and ppa1.action_status = 'C'
632 )
633 and paa.assignment_Action_id =
634 ( SELECT max(paa1.assignment_action_id)
635 FROM pay_payroll_actions ppa1,
636 pay_assignment_actions paa1
637 WHERE ppa1.payroll_action_id = paa1.payroll_Action_id
638 and ppa1.report_type IN ('W-2C PAPER','W2C_XML')
639 and ppa1.action_status = 'C'
640 and ppa1.effective_date between cp_start_date and cp_end_date
641 and paa1.assignment_id = paa.assignment_id
642 and ppa1.business_group_id = cp_business_group_id
643 )
644 ORDER BY paf.person_id;
645
646
647 --
648 -- This cursor will fetch all W-2c paper action that are not locked by Fed W-2c
649 -- process
650 -- Input
651 -- cpn_max_asgn_action_id - LAst W-2c Paper action Id
652 --
653 CURSOR c_w2c_paper_not_locked (cpn_business_group_id number,
654 cpd_start_date date,
655 cpd_end_date date,
656 cpn_assignment_id number,
657 cpn_max_asgn_action_id number)
658 IS
659 --bug 7504239
660 select distinct paa.assignment_action_id
661 --bug 7504239
662 from pay_assignment_actions paa,
663 per_all_assignments_f paf,
664 pay_payroll_actions ppa
665 where ppa.business_group_id = cpn_business_group_id
666 and ppa.effective_date between cpd_start_date and cpd_end_date
667 and ppa.action_type = 'X'
668 and ppa.report_type IN ('W-2C PAPER','W2C_XML')
669 and ppa.action_status = 'C'
670 and ppa.payroll_action_id = paa.payroll_action_id
671 and paf.assignment_id = paa.assignment_id
672 and paa.assignment_id = cpn_assignment_id
673 and paa.assignment_action_id <> cpn_max_asgn_action_id
674 and paf.effective_start_date <= ppa.effective_date
675 and paf.effective_end_date >= ppa.start_date
676 and paf.assignment_type = 'E'
677 and not exists
678 (select 'x'
679 from pay_Action_interlocks pai,
680 pay_assignment_actions paa1,
681 pay_payroll_actions ppa1
682 where pai.locked_action_id = paa.assignment_action_id
683 and paa1.assignment_action_id = pai.locking_action_id
684 and ppa1.payroll_action_id = paa1.payroll_action_id
685 and ppa1.effective_date between cpd_start_date and cpd_end_date
686 and ppa1.action_type = 'X'
687 and ppa1.report_type = 'MARK_W2C_PAPER'
688 and ppa1.action_status = 'C')
689 and not exists
690 (select 'x'
691 from pay_Action_interlocks pai,
692 pay_assignment_actions paa1,
693 pay_payroll_actions ppa1
694 where pai.locked_action_id = paa.assignment_action_id
695 and paa1.assignment_action_id = pai.locking_action_id
696 and ppa1.payroll_action_id = paa1.payroll_action_id
697 and ppa1.effective_date between cpd_start_date and cpd_end_date
698 and ppa1.action_type = 'X'
699 and ppa1.report_type = 'W2C'
700 and ppa1.report_qualifier = 'FED'
701 and ppa1.action_status = 'C');
702
703
704
705
706 -- Fetch Last W-2c Paper Action that is already reported to Govt
707 -- If this fetches one record that would be considered as Originally reported
708 -- Action and W-2c Pre-process associated with this action would be
709 -- considered as original archived value
710 --
711 -- If this cursor fetches multiple record action which is greated among would
712 -- would be considered as last reported Action
713 --
714 -- If no-record found from this cursor, Federal W-2 Mag would be considered
715 -- as last reported W-2 and YE archived value would be considered as
716 -- originally reported archived value
717 --
718 -- Arguments
719 -- W-2c Paper Assignment Action Id selected by Range Cursor
720 --
721 cursor get_last_reported_action (cp_business_group_id in number
722 ,cp_start_date in date
723 ,cp_end_date in date
724 ,cp_person_id in number
725 ,cp_w2c_paper_action_id in number)
726 IS
727 select --paa.assignment_id,
728 --paa.tax_unit_id,
729 --paf.person_id,
730 --ppa.report_type,
731 paa.assignment_Action_id,
732 ppa.payroll_action_id,
733 to_number(substr(paa.serial_number,1,15)) w2c_pp_asg_actid
734 from pay_assignment_actions paa,
735 per_all_assignments_f paf,
736 pay_payroll_actions ppa
737 where ppa.business_group_id = cp_business_group_id
738 and ppa.effective_date between cp_start_date and cp_end_date
739 and ppa.action_type = 'X'
740 and ppa.report_type IN ('W-2C PAPER','W2C_XML')
741 and ppa.action_status = 'C'
742 and ppa.payroll_action_id = paa.payroll_action_id
743 and paf.assignment_id = paa.assignment_id
744 and paf.effective_start_date <= ppa.effective_date
745 and paf.effective_end_date >= ppa.start_date
746 and paf.assignment_type = 'E'
747 and paf.person_id = cp_person_id
748 and paa.assignment_Action_id < cp_w2c_paper_action_id
749 and exists ((select 'x'
750 from pay_Action_interlocks pai,
751 pay_assignment_actions paa1,
752 pay_payroll_actions ppa1
753 where pai.locked_action_id = paa.assignment_action_id
754 and paa1.assignment_action_id = pai.locking_action_id
755 and ppa1.payroll_action_id = paa1.payroll_action_id
756 and ppa1.effective_date between cp_start_date
757 and cp_end_date
758 and ppa1.action_type = 'X'
759 and ppa1.report_type = 'MARK_W2C_PAPER'
760 and ppa1.report_category = 'RT'
761 and ppa1.action_status = 'C')
762 UNION ALL
763 (select 'x'
764 from pay_Action_interlocks pai,
765 pay_assignment_actions paa1,
766 pay_payroll_actions ppa1
767 where pai.locked_action_id = paa.assignment_action_id
768 and paa1.assignment_action_id = pai.locking_action_id
769 and ppa1.payroll_action_id = paa1.payroll_action_id
770 and ppa1.effective_date between cp_start_date
771 and cp_end_date
772 and ppa1.action_type = 'X'
773 and ppa1.report_type = 'W2C'
774 and ppa1.report_qualifier = 'FED'
775 and ppa1.report_category = 'RM'
776 and ppa1.action_status = 'C'))
777 order by paa.assignment_action_id DESC;
778
779 --
780 -- Fetch The W-2c Pre-Process Action ID which archived the changes reported
781 -- on W-2c Paper Report
782 --
783 CURSOR get_interlocked_action(cp_locking_action in number)
784 is
785 select ppa.report_type locked_report_type,
786 ppa.payroll_action_id locked_paction_id,
787 paa.assignment_action_id locked_action_id,
788 paa.serial_number serial_number
789 from pay_payroll_actions ppa,
790 pay_assignment_actions paa,
791 pay_action_interlocks pai
792 where pai.locking_action_id = cp_locking_action
793 and paa.assignment_action_id = pai.locked_action_id
794 and ppa.payroll_action_id = paa.payroll_action_id;
795 --
796 -- This cursor would be used to fetch the person details
797 -- for loging WARNING/ERROR messages
798 --
799 CURSOR get_warning_dtls_for_ee(cp_person_id in number)
800 is
801 select substr(full_name,1,48), employee_number
802 from per_all_people_f
803 where person_id = cp_person_id
804 order by effective_end_date desc;
805
806 ld_start_date DATE;
807 ld_end_date DATE;
808 lv_report_type VARCHAR2(30);
809 lv_report_qualifier VARCHAR2(30);
810 ln_business_group_id NUMBER;
811
812 /* Assignment Record Local Variables */
813 ln_assignment_id number;
814 ln_emp_tax_unit_id number;
815 ln_person_id number;
816 ln_assignment_action_id number;
817 ln_w2c_pp_asg_actid number;
818 ln_w2c_pp_locked_actid number;
819
820 lv_national_identifier per_all_people_f.national_identifier%type;
821 lv_message varchar2(50):= null;
822 lv_full_name per_all_people_f.full_name%type;
823 lv_name varchar2(50);
824 lv_record_name varchar2(50);
825 ln_prev_person_id number;
826
827 PROCEDURE action_creation (lp_person_id IN number,
828 lp_assignment_id IN number,
829 lp_assignment_action_id IN number,
830 lp_tax_unit_id IN number,
831 lp_start_date IN date,
832 lp_effective_date IN date,
833 lp_business_group_id IN number,
834 lp_w2c_pp_asg_action_id IN number,
835 lp_w2c_pp_locked_actid IN number
836 )
837 IS
838
839 ln_w2c_asg_action NUMBER := 0;
840 ln_corrected_asg_action NUMBER := 0;
841 ln_orig_reported_asg_action NUMBER := 0;
842
843 lv_ilocked_report_type VARCHAR2(30);
844 ln_ilocked_action_id NUMBER;
845 ln_ilocked_paction_id NUMBER;
846 ln_ilocked_serial_number VARCHAR2(30);
847 ln_eoy_payroll_action_id NUMBER;
848 ln_eoy_assignment_action_id NUMBER;
849
850 ln_last_w2cp_pactid NUMBER;
851 ln_last_w2cp_asg_actid NUMBER;
852 ln_last_w2cpp_asg_action_id NUMBER;
853
854 ln_serial_number pay_assignment_actions.serial_number%TYPE;
855 ln_notlocked_asgn_actid number;
856
857 BEGIN
858 hr_utility.set_location(gv_package || '.action_creation', 10);
859 -- Corrected Assignment Action would be the W-2c Pre-Process Action_ID
860 --
861 if lp_w2c_pp_asg_action_id > 0 then
862 hr_utility.set_location(gv_package || '.action_creation', 20);
863 ln_corrected_asg_action := lp_w2c_pp_asg_action_id;
864 end if;
865 hr_utility.set_location(gv_package || '.action_creation', 30);
866 --
867 -- Determine the EOY Action_ID
868 --
869 get_eoy_action_info(lp_effective_date
870 ,lp_tax_unit_id
871 ,lp_assignment_id
872 ,ln_eoy_payroll_action_id
873 ,ln_eoy_assignment_action_id);
874 hr_utility.set_location(gv_package || '.action_creation', 40);
875 -- Ideally if one correction made to W-2c and Previously reported archived
876 -- values will be from YE archive. Check whether W-2c Pre-process locks
877 -- the YE Pre-process. If yes then previously reported values can be
878 -- derived from YE pre-process
879
880 if lp_w2c_pp_locked_actid = ln_eoy_assignment_action_id
881 then
882 hr_utility.set_location(gv_package || '.action_creation', 50);
883 ln_orig_reported_asg_action := lp_w2c_pp_locked_actid;
884 else
885 --{
886 hr_utility.set_location(gv_package || '.action_creation', 60);
887 -- This indicates there is multiple W-2c or a Mark W-2c paper process ran
888 -- or a Federal W-2c Magnetic Process ran and reported for a set of W-2c.
889 -- Determine the last W-2c paper reported
890 OPEN get_last_reported_action(lp_business_group_id
891 ,lp_start_date
892 ,lp_effective_date
893 ,lp_person_id
894 ,lp_assignment_action_id);
895 -- ,ln_corrected_asg_action);
896 hr_utility.set_location(gv_package || '.action_creation', 70);
897 LOOP
898 FETCH get_last_reported_action INTO ln_last_w2cp_pactid,
899 ln_last_w2cp_asg_actid,
900 ln_last_w2cpp_asg_action_id;
901
902 hr_utility.set_location(gv_package || '.action_creation', 80);
903 if get_last_reported_action%ROWCOUNT = 0 then
904 hr_utility.set_location(gv_package || '.action_creation', 90);
905 hr_utility.trace('There is No other W-2c submitted to govt');
906 -- This condition will hold good if multiple W-2c paper printed
907 -- for employee but not reported to Govt. For this scenario
908 -- YE Pre-process would be considered as previously reported
909 -- action.
910 ln_orig_reported_asg_action := ln_eoy_assignment_action_id;
911 exit;
912 end if;
913 EXIT WHEN get_last_reported_action%NOTFOUND;
914 if ln_last_w2cp_asg_actid IS NOT NULL then
915 hr_utility.set_location(gv_package || '.action_creation', 100);
916 -- This scenario would exist if a W-2 correction is reported
917 -- either in paper form or in Magnetic Media
918 ln_orig_reported_asg_action := ln_last_w2cpp_asg_action_id;
919 exit;
920 end if;
921 END LOOP;
922 CLOSE get_last_reported_action;
923 --}
924 end if;
925 --
926 hr_utility.set_location(gv_package || '.action_creation', 110);
927 hr_utility.trace('Corrected Assignment_Action_ID '||
928 to_char(ln_corrected_asg_action));
929 hr_utility.trace('Originally Reported Assignment_Action_ID '||
930 to_char(ln_orig_reported_asg_action));
931
932 /* Create an assignment action for this person */
933 select pay_assignment_actions_s.nextval
934 into ln_w2c_asg_action
935 from dual;
936 hr_utility.set_location(gv_package || '.action_creation', 120);
937 hr_utility.trace('New w2c Action = ' || to_char(ln_w2c_asg_action));
938
939 /* Insert into pay_assignment_actions. */
940 hr_utility.trace('Creating Assignment Action');
941
942 hr_nonrun_asact.insact(ln_w2c_asg_action
943 ,lp_assignment_id
944 ,p_payroll_action_id
945 ,p_chunk
946 ,lp_tax_unit_id);
947
948 /* Update the serial number column with the person id
949 so that the W2C report will not have
950 to do an additional checking against the assignment
951 table */
952
953 hr_utility.set_location(gv_package || '.action_creation', 130);
954 hr_utility.trace('updating asg action');
955 /*************************************************************
956 ** Update the serial number column with the assignment action
957 ** of the last two archive processes
958 *************************************************************/
959 ln_serial_number := lpad(ln_corrected_asg_action,15,0)||
960 lpad(ln_orig_reported_asg_action,15,0);
961 update pay_assignment_actions aa
962 set aa.serial_number = ln_serial_number
963 where aa.assignment_action_id = ln_w2c_asg_action;
964 hr_utility.set_location(gv_package || '.action_creation', 140);
965
966 /* Interlock the w2c report action with current w2c Mag action */
967 hr_utility.trace('Locking Action = ' || ln_w2c_asg_action);
968 hr_utility.trace('Locked Action = ' || lp_assignment_action_id);
969 hr_nonrun_asact.insint(ln_w2c_asg_action
970 ,lp_assignment_action_id);
971 /*
972 Lock all other W-2c paper action that are not yet locked by Fed W-2c
973 mag action. This scenario could exist when there are multiple W-2c paper
974 for which there would be one W-2c Mag action.
975 */
976 OPEN c_w2c_paper_not_locked(lp_business_group_id
977 ,lp_start_date
978 ,lp_effective_date
979 ,lp_assignment_id
980 ,lp_assignment_action_id
981 );
982 hr_utility.set_location(gv_package || '.action_creation', 150);
983 LOOP
984 FETCH c_w2c_paper_not_locked INTO ln_notlocked_asgn_actid;
985
986 hr_utility.set_location(gv_package || '.action_creation', 160);
987 EXIT WHEN c_w2c_paper_not_locked%NOTFOUND;
988 if c_w2c_paper_not_locked%ROWCOUNT = 0 then
989 exit;
990 else
991 hr_utility.trace('Locking Action = ' || ln_w2c_asg_action);
992 hr_utility.trace('Locked Action = ' || ln_notlocked_asgn_actid);
993 hr_nonrun_asact.insint(ln_w2c_asg_action
994 ,ln_notlocked_asgn_actid);
995 end if;
996 END LOOP;
997 CLOSE c_w2c_paper_not_locked;
998 hr_utility.set_location(gv_package || '.action_creation', 170);
999 end action_creation; -- End of Local function Action_Creation
1000 --
1001 -- Action Creation Main Logic
1002 --
1003 begin
1004 --{
1005 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 10);
1006 hr_utility.trace('Entered Mark_W2c_action_creation ');
1007 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
1008 hr_utility.trace('p_start_person_id = '|| to_char(p_start_person_id));
1009 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
1010 hr_utility.trace('p_chunk = '|| to_char(p_chunk));
1011
1012 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 20);
1013 get_payroll_action_info(p_payroll_action_id
1014 ,ld_start_date
1015 ,ld_end_date
1016 ,lv_report_type
1017 ,lv_report_qualifier
1018 ,ln_business_group_id);
1019
1020 hr_utility.trace('ld_start_date = ' || ld_start_date);
1021 hr_utility.trace('ld_end_date = ' || ld_end_date);
1022 hr_utility.trace('lv_report_type = ' || lv_report_type);
1023 hr_utility.trace('lv_report_qualifier = ' || lv_report_qualifier);
1024 hr_utility.trace('ln_business_group_id = ' || ln_business_group_id);
1025
1026 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 22);
1027 open w2c_future_correction_reported(ln_business_group_id
1028 ,ld_start_date
1029 ,ld_end_date
1030 ,p_start_person_id
1031 ,p_end_person_id
1032 );
1033 loop
1034 --{
1035 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 23);
1036 fetch w2c_future_correction_reported into ln_assignment_id,
1037 ln_person_id;
1038 if w2c_future_correction_reported%ROWCOUNT = 0 then
1039 hr_utility.set_location(gv_package ||'.w2c_mag_action_creation', 24);
1040 hr_utility.trace('No Person found for whose future correction is already reported');
1041 end if;
1042 EXIT WHEN w2c_future_correction_reported%NOTFOUND;
1043 --
1044 -- If an employees future correction is already reported log a Warning to alert
1045 -- that Federal W-2c Magnetic Media will not create action for this employee
1046 --
1047 if ln_person_id is not null then
1048 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 25);
1049 open get_warning_dtls_for_ee(ln_person_id);
1050 fetch get_warning_dtls_for_ee into lv_full_name
1051 ,lv_national_identifier;
1052 close get_warning_dtls_for_ee;
1053 hr_utility.trace('WARNING: Employee '||lv_full_name ||' Reported Future corrections');
1054 hr_utility.trace(' SSN = '||lv_national_identifier);
1055 /* message to user -- This employees future correction is already Reported
1056 genrating W-2c Mag */
1057 lv_record_name := 'Action_Creation';
1058 lv_message := 'Future Correction reported for this employee';
1059 lv_name := lv_full_name || ', SSN '||lv_national_identifier;
1060 /* push message into pay_message_lines */
1061 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
1062 pay_core_utils.push_token('record_name', lv_record_name);
1063 pay_core_utils.push_token('name_or_number',lv_name);
1064 pay_core_utils.push_token('description', lv_message);
1065 end if;
1066 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 27);
1067 --}
1068 end loop;
1069 close w2c_future_correction_reported;
1070 --
1071 -- Fetch Employees for creating Action for Federal W-2c Magnetic Media
1072 --
1073 open get_w2c_mag_assignments (ln_business_group_id
1074 ,ld_start_date
1075 ,ld_end_date
1076 ,p_start_person_id
1077 ,p_end_person_id
1078 );
1079 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 30);
1080 loop
1081 --{
1082 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 40);
1083 fetch get_w2c_mag_assignments into ln_assignment_id,
1084 ln_emp_tax_unit_id,
1085 ln_person_id,
1086 ln_assignment_action_id,
1087 ln_w2c_pp_asg_actid,
1088 ln_w2c_pp_locked_actid;
1089
1090 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 50);
1091
1092 if get_w2c_mag_assignments%ROWCOUNT = 0 then
1093 hr_utility.set_location(gv_package ||'.w2c_mag_action_creation', 60);
1094 hr_utility.trace('No Person found for reporting in this chunk');
1095 end if;
1096
1097 EXIT WHEN get_w2c_mag_assignments%NOTFOUND;
1098
1099 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 70);
1100 hr_utility.trace('ln_assignment_id =' ||ln_assignment_id);
1101 hr_utility.trace('ln_emp_tax_unit_id =' ||ln_emp_tax_unit_id);
1102 hr_utility.trace('ln_person_id =' ||ln_person_id);
1103 hr_utility.trace('ln_assignment_action_id =' ||ln_assignment_action_id);
1104 hr_utility.trace('Corrected assignment_action_id =' ||ln_w2c_pp_asg_actid);
1105 hr_utility.trace('action locked by Corrected assignment_action_id =' ||ln_w2c_pp_locked_actid);
1106
1107 if ln_person_id is not null then
1108 hr_utility.set_location(gv_package ||'.w2c_mag_action_creation', 80);
1109 -- This check is performed to ignore duplicate assignment when a
1110 -- person is having an update on assignment during the tax year.
1111 -- multiple assignment dur to update on assignment was causing
1112 -- duplicate RCW record.
1113 if (nvl(ln_prev_person_id,0) <> ln_person_id) then
1114 action_creation(ln_person_id,
1115 ln_assignment_id,
1116 ln_assignment_action_id,
1117 ln_emp_tax_unit_id,
1118 ld_start_date,
1119 ld_end_date,
1120 ln_business_group_id,
1121 ln_w2c_pp_asg_actid,
1122 ln_w2c_pp_locked_actid
1123 );
1124 hr_utility.set_location(gv_package ||'.w2c_mag_action_creation', 90);
1125 end if;
1126 ln_prev_person_id := ln_person_id;
1127 end if;
1128 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 100);
1129 --}
1130 end loop;
1131 close get_w2c_mag_assignments;
1132 hr_utility.trace('Action Creation for W2c_Magnetic_Media completed Successfully');
1133 hr_utility.set_location(gv_package || '.w2c_mag_action_creation', 110);
1134 --}
1135 end w2c_mag_action_creation;
1136 -- End of Procedure mar_w2c_action_creation
1137 --
1138 --Begin
1139 --hr_utility.trace_on(null,'W2CMAG');
1140 end pay_us_w2c_reporting_utils;