1 package body pay_us_magtape_reporting as
2 /* $Header: pyusmrep.pkb 115.8 2002/12/02 21:44:07 sodhingr ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_magtape_reporting
10 Purpose
11 The purpose of this package is to support the generation of magnetic tape
12 reports for US legilsative requirements. Specifically this covers federal
13 and state W2's and also State Quarterly Wage Listing's.
14 Notes
15 The generation of each magnetic tape report is a two stage process i.e.
16
17 1. Create a payroll action identifying the magnetic tape report being
18 generated. Populate a set of assignment actions with each one
19 identifying a person to be included in the report.
20
21 2. Submit a request to run the generic magnetic tape process which will
22 drive off the data created in stage one. This will result in the
23 production of a structured ascii file which can be transferred to
24 magnetic tape and sent to the relevant authority.
25 History
26 10-Feb-95 J.S.Hobbs 40.0 Date created.
27
28 ============================================================================*/
29 --
30 g_message_text varchar2(240);
31 type g_people_type is table of VARCHAR(80)
32 index by binary_integer;
33 g_people_text g_people_type;
34 g_num_peo number;
35 --
36 -----------------------------------------------------------------------------
37 -- Name
38 -- bal_db_item
39 -- Purpose
40 -- Given the name of a balance DB item as would be seen in a fast formula
41 -- it returns the defined_balance_id of the balance it represents.
42 -- Arguments
43 -- Notes
44 -- A defined +balance_id is required by the PLSQL balance function.
45 -----------------------------------------------------------------------------
46 --
47 function bal_db_item
48 (
49 p_db_item_name varchar2
50 ) return number is
51 --
52 -- Get the defined_balance_id for the specified balance DB item.
53 --
54 cursor csr_defined_balance is
55 select fnd_number.canonical_to_number(UE.creator_id)
56 from ff_database_items DI,
57 ff_user_entities UE
58 where DI.user_name = p_db_item_name
59 and UE.user_entity_id = DI.user_entity_id
60 and UE.creator_type = 'B'
61 and UE.legislation_code = 'US'; /* Bug: 2296797 */
62 --
63 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
64 --
65 begin
66 --
67 hr_utility.set_location('pay_us_magtape_reporting.bal_db_item - opening cursor', 1);
68 open csr_defined_balance;
69 fetch csr_defined_balance into l_defined_balance_id;
70 if csr_defined_balance%notfound then
71 close csr_defined_balance;
72 g_message_text := 'Balance DB item does not exist';
73 raise hr_utility.hr_error;
74 else
75 hr_utility.set_location('pay_us_magtape_reporting.bal_db_item - fetched from cursor', 2);
76 close csr_defined_balance;
77 end if;
78 --
79 return (l_defined_balance_id);
80 --
81 end bal_db_item;
82 --
83 -----------------------------------------------------------------------------
84 -- Name
85 -- lookup_jurisdiction_code
86 -- Purpose
87 -- Given a state code ie. AL it returns the jurisdiction code that
88 -- represents that state.
89 -- Arguments
90 -- Notes
91 -----------------------------------------------------------------------------
92 --
93 function lookup_jurisdiction_code
94 (
95 p_state varchar2
96 ) return varchar2 is
97 --
98 -- Get the jurisdiction_code for the specified state code.
99 --
100 cursor csr_jurisdiction_code is
101 select SR.jurisdiction_code
102 from pay_state_rules SR
103 where SR.state_code = p_state;
104 --
105 l_jurisdiction_code pay_state_rules.jurisdiction_code%type;
106 --
107 begin
108 --
109 hr_utility.set_location('pay_us_magtape_reporting.lookup_jurisdiction_code - opening cursor', 1);
110 open csr_jurisdiction_code;
111 fetch csr_jurisdiction_code into l_jurisdiction_code;
112 if csr_jurisdiction_code%notfound then
113 close csr_jurisdiction_code;
114 g_message_text := 'Cannot find jurisdiction code';
115 raise hr_utility.hr_error;
116 else
117 hr_utility.set_location('pay_us_magtape_reporting.lookup_jurisdiction_code - fetched from cursor', 2);
118 close csr_jurisdiction_code;
119 end if;
120 --
121 return (l_jurisdiction_code);
122 --
123 end lookup_jurisdiction_code;
124 --
125 -----------------------------------------------------------------------------
126 -- Name
127 -- error_payroll_action
128 -- Purpose
129 -- Sets the status of a payroll action to 'E'rror.
130 -- Arguments
131 -- Notes
132 -- This should only be used when the magnetic report has failed.
133 -----------------------------------------------------------------------------
134 --
135 procedure error_payroll_action
136 (
137 p_payroll_action_id number
138 ) is
139 begin
140 --
141 -- Sets the payroll action to a status of 'E'rror.
142 --
143 hr_utility.set_location('pay_us_magtape_reporting.error_payroll_action - updating pay_ payrol_actions', 1);
144 update pay_payroll_actions PA
145 set PA.action_status = 'E'
146 where PA.payroll_action_id = p_payroll_action_id;
147 --
148 hr_utility.set_location('pay_us_magtape_reporting.error_payroll_action - updated pay_ payrol_actions', 2);
149 commit;
150 --
151 end error_payroll_action;
152 --
153 -----------------------------------------------------------------------------
154 -- Name
155 -- update_action_statuses
156 -- Purpose
157 -- Sets the payroll action to 'C'omplete. Sets all successful assignment
158 -- actions to 'C'omplete.
159 -- Arguments
160 -- Notes
161 -- This should only be used when the magnetic report has successfully run.
162 -- All the assignment actions are set to 'U'nprocessed before processing
163 -- starts. If an error occurs with an assignment action then it is set to
164 -- 'E'rror by the magnetic tape process. Having finished processing, all
165 -- assignment actions left with a status of 'U'nprocessed are assumed to
166 -- be successful and therefore set to 'C'omplete.
167 -----------------------------------------------------------------------------
168 --
169 procedure update_action_statuses
170 (
171 p_payroll_action_id number
172 ) is
173 begin
174 --
175 -- Sets the payroll action to a status of 'C'omplete.
176 --
177 hr_utility.set_location('pay_us_magtape_reporting.update_action_statuses - updating pay_ payrol_actions', 1);
178 update pay_payroll_actions PA
179 set PA.action_status = 'C'
180 where PA.payroll_action_id = p_payroll_action_id;
181 --
182 -- Sets all successfully processed assignment actions to 'C'omplete.
183 --
184 hr_utility.set_location('pay_us_magtape_reporting.update_action_statuses - updating pay_ assignment_actions', 2);
185 update pay_assignment_actions AA
186 set AA.action_status = 'C'
187 where AA.payroll_action_id = p_payroll_action_id
188 and AA.action_status = 'U';
189 --
190 hr_utility.set_location('pay_us_magtape_reporting.update_action_statuses - commiting', 3);
191 commit;
192 --
193 end update_action_statuses;
194 --
195 -----------------------------------------------------------------------------
196 -- Name
197 -- get_selection_information
198 -- Purpose
199 -- Returns information used in the selection of people to be reported on.
200 -- Arguments
201 -- The following values are returned :-
202 --
203 -- p_sql_statement - the SQL to be run to select the people.
204 -- p_period_start - the start of the period over which to select
205 -- the people.
206 -- p_period_end - the end of the period over which to select
207 -- the people.
208 -- p_defined_balance_id - the balance which must be non zero for each
209 -- person to be included in the report.
210 -- p_group_by_gre - should the people be grouped by GRE.
211 -- p_group_by_medicare - should the people ,be grouped by medicare
212 -- within GRE NB. this is not currently supported.
213 -- p_tax_unit_context - should the TAX_UNIT_ID context be set up for
214 -- the testing of the balance.
215 -- p_jurisdiction_context - should the JURISDICTION_CODE context be set up
216 -- for the testing of the balance.
217 -- Notes
218 -- This routine provides a way of coding explicit rules for individual
219 -- reports where they are different from the standard selection criteria
220 -- for the report type ie. in NY state the selection of people in the 4th
221 -- quarter is different from the first 3.
222 -----------------------------------------------------------------------------
223 --
224 procedure get_selection_information
225 (
226 --
227 -- Identifies the type of report, the authority for which it is being run,
228 -- and the period being reported.
229 --
230 p_report_type varchar2,
231 p_state varchar2,
232
233 --
234 -- Quarter and year start and end dates for the period being reported on.
235 --
236 p_quarter_start date,
237 p_quarter_end date,
238 p_year_start date,
239 p_year_end date,
240 --
241 -- Information returned is used to control the selection of people to
242 -- report on.
243 --
244 p_period_start in out nocopy date,
245 p_period_end in out nocopy date,
246 p_defined_balance_id in out nocopy number,
247 p_group_by_gre in out nocopy boolean,
248 p_group_by_medicare in out nocopy boolean,
249 p_tax_unit_context in out nocopy boolean,
250 p_jurisdiction_context in out nocopy boolean
251 ) is
252 --
253 --
254 begin
255 --
256 -- Depending on the report being processed, derive all the information
257 -- required to be able to select the people to report on.
258 --
259 -- Federal W2.
260 --
261 if p_report_type = 'W2' and p_state = 'FED' then
262 --
263 -- Default settings for Federal W2.
264 --
265 hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - default settings for Federal W2', 1);
266 p_period_start := p_year_start;
267 p_period_end := p_year_end;
268 p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD');
269 p_group_by_gre := TRUE;
270 p_group_by_medicare := TRUE;
271 p_tax_unit_context := TRUE;
272 p_jurisdiction_context := FALSE;
273 --
274 -- State W2's.
275 --
276 elsif p_report_type = 'W2' and p_state <> 'FED' then
277 --
278 -- Default settings for State W2.
279 --
280 hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - default settings for State W2', 2);
281 p_period_start := p_year_start;
282 p_period_end := p_year_end;
283 p_defined_balance_id := bal_db_item('SIT_GROSS_PER_JD_GRE_YTD');
284 p_group_by_gre := TRUE;
285 p_group_by_medicare := TRUE;
286 p_tax_unit_context := TRUE;
287 p_jurisdiction_context := TRUE;
288 --
289 -- State Quarterly Wage Listings.
290 --
291 elsif p_report_type = 'SQWL' then
292 --
293 -- New York state settings NB. the difference is that the criteria for
294 -- selecting people in the 4th quarter is different to that used for the
295 -- furst 3 quarters of the tax year.
296 --
297 if p_state = 'NY' then
298 --
299 -- Period is one of the first 3 quarters of tax year.
300 --
301 if instr(to_char(p_quarter_end,'MM'), '12') = 0 then
302 --
303 hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - NY last quarter', 3);
304 p_period_start := p_quarter_start;
305 p_period_end := p_quarter_end;
306 p_defined_balance_id := bal_db_item('SUI_ER_GROSS_PER_JD_GRE_QTD');
307 --
308 -- Period is the last quarter of the year.
309 --
310 else
311 --
312 hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - in NY ', 3);
313 p_period_start := p_year_start;
314 p_period_end := p_year_end;
315 p_defined_balance_id := bal_db_item('SIT_GROSS_PER_JD_GRE_YTD');
316 --
317 end if;
318 --
319 -- Values are set independent of quarter being reported on.
320 --
321 p_group_by_gre := TRUE;
322 p_group_by_medicare := TRUE;
323 p_tax_unit_context := TRUE;
324 p_jurisdiction_context := TRUE;
325 --
326 -- Default settings for State Quarterly Wage Listing.
327 --
328 else
329 --
330 hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - defalut setting for SQWL ', 4);
331 p_period_start := p_quarter_start;
332 p_period_end := p_quarter_end;
333 p_defined_balance_id := bal_db_item('SUI_ER_GROSS_PER_JD_GRE_QTD');
334 p_group_by_gre := TRUE;
335 p_group_by_medicare := TRUE;
336 p_tax_unit_context := TRUE;
337 p_jurisdiction_context := TRUE;
338 --
339 end if;
340 --
341 -- An invalid report type has been passed so fail.
342 --
343 else
344 --
345 hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - invalid report ', 4);
346 raise hr_utility.hr_error;
347 --
348 end if;
349 --
350 end get_selection_information;
351 --
352 -----------------------------------------------------------------------------
353 -- Name
354 -- create_payroll_action
355 -- Purpose
356 -- Creates a payroll action identifying the production of a particular
357 -- magnetic tape report i.e. federal W2, etc... The list of people to be
358 -- reported on is created as assignment actions for the payroll action.
359 -- Arguments
360 -- Notes
361 -- The effective_date of the payroll action identifies the end of the
362 -- period being reported i.e. end of tax year or end of a quarter. The
363 -- legislative parameter is used to uniquely identify the report.
364 --
365 -- SQWLD - add p_media_type parameter
366 -----------------------------------------------------------------------------
367 --
368 function create_payroll_action
369 (
370 p_report_type in varchar2,
371 p_state in varchar2,
372 p_trans_legal_co_id in varchar2,
373 p_business_group_id in number,
374 p_period_end in date,
375 p_media_type in varchar2
376 ) return number is
377 --
378 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
379 --
380 begin
381 --
382 -- Get the next payroll_action_id value from the sequence.
383 hr_utility.set_location('pay_us_magtape_reporting.create_payroll_action - getting nextval', 1);
384 --
385 select pay_payroll_actions_s.nextval
386 into l_payroll_action_id
387 from sys.dual;
388 --
389 -- Create a payroll action dated as of the end of the period being reported
390 -- on. Populate the legislative parameter to identify the report being run
391 -- NB. the combination of this value and the effective date should uniquely
392 -- identify each report e.g. FED-W2 on 31-DEC-1995.
393 --
394
395 -- SQWLD - append p_media_type to parameter string, so redo can detect it
396 hr_utility.set_location('pay_us_magtape_reporting.create_payroll_action - creating payroll action', 2);
397 insert into pay_payroll_actions
398 (payroll_action_id
399 ,action_type
400 ,business_group_id
401 ,action_population_status
402 ,action_status
403 ,effective_date
404 ,date_earned
405 ,legislative_parameters
406 ,object_version_number)
407 values
408 (l_payroll_action_id
409 ,'X' -- (X) -> Magnetic Report
410 ,p_business_group_id
411 ,'U' -- (U)npopulated
412 ,'U' -- (U)nprocessed
413 ,p_period_end
414 ,p_period_end
415 ,'USMAGTAPE' || '-' ||
416 lpad(p_report_type, 5) || '-' ||
417 lpad(p_state , 5) || '-' ||
418 lpad(p_trans_legal_co_id, 5) || '-' ||
419 lpad(nvl(p_media_type, 'RT'), 5) -- SQWLD - save media value, 'PD' for PC Diskette
420 ,1);
421 --
422 -- Return id of new row.
423 --
424 return (l_payroll_action_id);
425 --
426 end create_payroll_action;
427 --
428 -----------------------------------------------------------------------------
429 -- Name
430 -- create_assignment_action
431 -- Purpose
432 -- Create an assignment action for each person to be reported on within the
433 -- magnetic tape report identified by the parent payroll action.
434 -- Arguments
435 -- Notes
436 -----------------------------------------------------------------------------
437 --
438 function create_assignment_action
439 (
440 p_payroll_action_id in number,
441 p_assignment_id in number,
442 p_tax_unit_id in number
443 ) return number is
444 --
445 -- Cursor to fetch the newly created assignment_action_id NB. there could
446 -- be several assignment actions for the same assignment and the only way
447 -- to find the newly created one is to fetch the one that has not had the
448 -- tax_unit_id updated yet.
449 --
450 cursor csr_assignment_action is
451 select AA.assignment_action_id
452 from pay_assignment_actions AA
453 where AA.payroll_action_id = p_payroll_action_id
454 and AA.assignment_id = p_assignment_id
455 and AA.tax_unit_id is null;
456 --
457 -- Local variables.
458 --
459 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
460 --
461 begin
462 --
463 -- Create assignment action to identify a specific person's inclusion in the
464 -- magnetic tape report identified by the parent payroll action. The
465 -- assignment action has to be sequenced within the other assignment actions
466 -- according to the date of the payroll action so that the derivation of
467 -- any balances based on the assignment action is correct.
468 --
469 hr_utility.set_location('pay_us_magtape_reporting.create_assignment_action - creating assignment action', 1);
470 hrassact.inassact(p_payroll_action_id, p_assignment_id);
471 --
472 -- Get the assignment_action_id of the newly created assignment action.
473 --
474 hr_utility.set_location('pay_us_magtape_reporting.create_assignment_action - opening csr_assignment_action', 2);
475 open csr_assignment_action;
476 fetch csr_assignment_action into l_assignment_action_id;
477 close csr_assignment_action;
478 --
479 --
480 hr_utility.set_location('pay_us_magtape_reporting.create_assignment_action - updating pay_assignment_actions', 3);
481 update pay_assignment_actions AA
482 set AA.tax_unit_id = p_tax_unit_id
483 where AA.assignment_action_id = l_assignment_action_id;
484 --
485 -- Return id of new row.
486 --
487 hr_utility.set_location('pay_us_magtape_reporting.create_assignment_action - updated pay_assignment_actions', 4);
488 return (l_assignment_action_id);
489 --
490 end create_assignment_action;
491 --
492 procedure get_person_name(p_person_id in number,
493 p_full_name in out nocopy varchar2,
494 p_emp_number in out nocopy varchar2) is
495
496 l_name varchar2(240);
497 l_number varchar2(60);
498
499 cursor csr_get_info is
500 select full_name, employee_number
501 from per_people_f
502 where person_id = p_person_id
503 and rownum = 1;
504
505 begin
506 open csr_get_info;
507 fetch csr_get_info into l_name, l_number;
508 close csr_get_info;
509
510 --dbms_output.put_line('l_name is '||l_name||' and l_number is '||l_number);
511
512 p_full_name := l_name;
513 p_emp_number := l_number;
514
515 end get_person_name;
516
517 -----------------------------------------------------------------------------
518 -- Name
519 -- generate_people_list
520 -- Purpose
521 -- Creates a payroll action and a list of assignment actions detailing the
522 -- date of the magnetic tape report along with the list of people to
523 -- report on.
524 -- Arguments
525 -- Notes
526 -- The criteria for selecting the people cannot be done simply using SQL.
527 -- It is done by first using a PLSQL cursor which makes an educated guess
528 -- about the people to include NB. it will always include all the correct
529 -- people even though some may not be valid. The second step is to
530 -- further check each person found and apply further checks. If these are
531 -- passed then they are added to the list (create an assignment action)
532 -- otherwise they are discarded.
533 --
534 -- SQWLD - add p_media_type parameter
535 -----------------------------------------------------------------------------
536 --
537 function generate_people_list
538 (
539 p_report_type varchar2,
540 p_state varchar2,
541 p_trans_legal_co_id varchar2,
542 p_business_group_id number,
543 p_period_end date,
544 p_quarter_start date,
545 p_quarter_end date,
546 p_year_start date,
547 p_year_end date,
548 p_media_type varchar2
549 ) return number is
550 --
551 --
552 -- Variables used to hold the select columns from the SQL statement.
553 --
554 l_person_id number;
555 l_assignment_id number;
556 l_tax_unit_id number;
557 l_effective_end_date date;
558 --
559 -- Variables used to hold the values used as bind variables within the
560 -- SQL statement.
561 --
562 l_bus_group_id number := p_business_group_id;
563 l_state varchar2(30) := p_state;
564 l_period_start date;
565 l_period_end date;
566 --
567 -- Variables used to hold the details of the payroll and assignment actions
568 -- that are created.
569 --
570 l_payroll_action_created boolean := false;
571 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
572 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
573 --
574 -- Variable holding the balance to be tested.
575 --
576 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
577 --
578 -- Indicator variables used to control how the people are grouped.
579 --
580 l_group_by_gre boolean := FALSE;
581 l_group_by_medicare boolean := FALSE;
582 --
583 -- Indicator variables used to control which contexts are set up for
584 -- balance.
585 --
586 l_tax_unit_context boolean := FALSE;
587 l_jurisdiction_context boolean := FALSE;
588 --
589 -- Variables used to hold the current values returned within the loop for
590 -- checking against the new values returned from within the loop on the
591 -- next iteration.
592 --
593 l_prev_person_id per_people_f.person_id%type;
594 l_prev_tax_unit_id hr_organization_units.organization_id%type;
595 --
596 -- Variable to hold the jurisdiction code used as a context for state
597 -- reporting.
598 --
599 l_jurisdiction_code varchar2(30);
600 --
601 -- Flag to indicate whether assignment is exempt from SUI wages.
602 -- Not Needed
603 --
604 -- l_exempt varchar2(150) := 'N';
605 --
606 -- Variable used to commit after every chunk_size of assignment actions
607 -- or after 20, if no chunk size is specified.
608 --
609 cnt number;
610 l_chunk_size number;
611
612 l_value number;
613 --
614 -- People list for Fed W2 - Federal grouped within GRE.
615 --
616 CURSOR c_federal IS
617 SELECT ASG.person_id person_id,
618 ASG.assignment_id assignment_id,
619 fnd_number.canonical_to_number(SCL.segment1) tax_unit_id,
620 max(ASG.effective_end_date) effective_end_date
621 FROM per_assignments_f ASG,
622 hr_soft_coding_keyflex SCL,
623 hr_tax_units_v TUV,
624 pay_payrolls_f PPY
625 WHERE ASG.business_group_id = l_bus_group_id
626 AND ASG.assignment_type = 'E'
627 AND ASG.effective_start_date <= l_period_end
628 AND ASG.effective_end_date >= l_period_start
629 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
630 AND fnd_number.canonical_to_number(SCL.segment1) = TUV.tax_unit_id
631 AND TUV.US_1099R_TRANSMITTER_CODE IS NULL
632 AND PPY.payroll_id = ASG.payroll_id
633 AND l_state = l_state
634 GROUP BY ASG.person_id,
635 ASG.assignment_id,
636 fnd_number.canonical_to_number(SCL.segment1)
637 ORDER BY 1, 3, 4 DESC, 2;
638 --
639 -- People list for State W2 - State grouped within GRE.
640 --
641 CURSOR c_state IS
642 SELECT ASG.person_id person_id,
643 ASG.assignment_id assignment_id,
644 fnd_number.canonical_to_number(SCL.segment1) tax_unit_id,
645 max(ASG.effective_end_date) effective_end_date
646 FROM per_assignments_f ASG,
647 hr_soft_coding_keyflex SCL,
648 hr_tax_units_v TUV,
649 pay_payrolls_f PPY,
650 pay_state_rules SR,
651 pay_element_types_f ET,
652 pay_input_values_f IV,
653 pay_element_links_f EL
654 WHERE ASG.business_group_id + 0 = l_bus_group_id
655 AND ASG.assignment_type = 'E'
656 AND ASG.effective_start_date <= l_period_end
657 AND ASG.effective_end_date >= l_period_start
658 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
659 AND fnd_number.canonical_to_number(SCL.segment1) = TUV.tax_unit_id
660 AND TUV.US_1099R_TRANSMITTER_CODE IS NULL
661 AND PPY.payroll_id = ASG.payroll_id
662 AND SR.state_code = l_state
663 AND ET.element_name = 'VERTEX'
664 AND IV.element_type_id = ET.element_type_id
665 AND upper(IV.name) = 'JURISDICTION'
666 AND EL.element_type_id = ET.element_type_id
667 AND EL.business_group_id + 0 = ASG.business_group_id + 0
668 AND EXISTS (SELECT ''
669 FROM
670 pay_element_entries_f EE,
671 pay_element_entry_values_f EEV
672 WHERE EE.assignment_id = ASG.assignment_id
673 AND EE.element_link_id = EL.element_link_id
674 AND EEV.element_entry_id = EE.element_entry_id
675 AND EEV.input_value_id + 0 = IV.input_value_id
676 AND substr(SR.jurisdiction_code ,1,2) =
677 substr(EEV.screen_entry_value,1,2)
678 AND EE.effective_start_date <= l_period_end
679 AND EE.effective_end_date >= l_period_start)
680 GROUP BY ASG.person_id,
681 ASG.assignment_id,
682 fnd_number.canonical_to_number(SCL.segment1)
683 ORDER BY 1, 3, 4 DESC, 2;
684 --
685 --
686 --
687 begin
688 --
689 -- Return details used to control the selection of people to report on ie.
690 -- the SQL statement to run, the period over which to look for the people,
691 -- how to group the people, etc...
692 --
693 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - get selection_information', 1);
694 get_selection_information
695 (p_report_type,
696 p_state,
697 p_quarter_start,
698 p_quarter_end,
699 p_year_start,
700 p_year_end,
701 l_period_start,
702 l_period_end,
703 l_defined_balance_id,
704 l_group_by_gre,
705 l_group_by_medicare,
706 l_tax_unit_context,
707 l_jurisdiction_context);
708 --
709 -- Get the jurisdiction code for the state if appropriate.
710 --
711 if l_jurisdiction_context then
712 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - get jurisdiction code', 2);
713 l_jurisdiction_code := lookup_jurisdiction_code(p_state);
714 end if;
715 --
716 -- Open up a cursor for processing a SQL statement.
717 --
718 IF l_state = 'FED' THEN
719 --
720 OPEN c_federal;
721 --
722 ELSE
723 --
724 OPEN c_state;
725 --
726 END IF;
727 --
728 --------------------------------------------------------------------
729 -- Get CHUNK_SIZE or default to 20 if CHUNK_SIZE does not exist
730 --------------------------------------------------------------------
731 BEGIN
732 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - get chunk size', 3);
733 SELECT parameter_value
734 INTO l_chunk_size
735 FROM pay_action_parameters
736 WHERE parameter_name = 'CHUNK_SIZE';
737 EXCEPTION
738 WHEN no_data_found THEN
739 l_chunk_size := 20;
740 END;
741 --
742 -- Initialize counter.
743 --
744 cnt := 0;
745 --
746 -- Loop for all rows returned for SQL statement.
747 --
748 LOOP
749 --
750 -- Commit if l_chunk_size number of assignments have been processed.
751 --
752 if cnt = l_chunk_size then
753 cnt := 0;
754 commit;
755 hr_utility.trace('COMMITTED');
756 end if;
757 --
758 cnt := cnt + 1;
759 hr_utility.trace('CNT:::: '||cnt||'CHUNK SIZE::: '||l_chunk_size);
760 --
761 -- Fetch next row from the appropriate cursor.
762 --
763 --
764 IF l_state = 'FED' THEN
765 --
766 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - fetching from c_federal', 4);
767 FETCH c_federal INTO l_person_id,
768 l_assignment_id,
769 l_tax_unit_id,
770 l_effective_end_date;
771 EXIT WHEN c_federal%NOTFOUND;
772 --
773 ELSE
774 --
775 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - fetching from c_state', 4);
776 FETCH c_state INTO l_person_id,
777 l_assignment_id,
778 l_tax_unit_id,
779 l_effective_end_date;
780 EXIT WHEN c_state%NOTFOUND;
781 --
782 END IF;
783 --
784 -- If the new row is the same as the previous row according to the way
785 -- the rows are grouped then discard the row ie. grouping by GRE
786 -- requires a single row for each person / GRE combination.
787 --
788 if ((l_group_by_gre and
789 l_person_id = l_prev_person_id and
790 l_tax_unit_id = l_prev_tax_unit_id) or
791 (not l_group_by_gre and
792 l_person_id = l_prev_person_id)) then
793 --
794 -- Do nothing.
795 --
796 null;
797 --
798 -- Have a new unique row according to the way the rows are grouped.
799 -- The inclusion of the person is dependent on having a non zero
800 -- balance.
801 -- If the balance is non zero then an assignment action is created to
802 -- indicate their inclusion in the magnetic tape report.
803 --
804 else
805 --
806 -- Set up contexts required to test the balance.
807 --
808 -- Set up TAX_UNIT_ID context if appropriate.
809 --
810 if l_tax_unit_context then
811 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - setting tax_unit_id context', 5);
812 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
813 end if;
814 --
815 -- Set up JURISDICTION_CODE context if appropriate.
816 --
817 if l_jurisdiction_context then
818 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - setting jurisdiction_code context', 6);
819 pay_balance_pkg.set_context('JURISDICTION_CODE',l_jurisdiction_code);
820 end if;
821 --
822 --
823 -- Check the balance.
824 --
825 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - check balance', 7);
826
827 l_value := pay_balance_pkg.get_value
828 (l_defined_balance_id,
829 l_assignment_id,
830 least(p_period_end,l_effective_end_date));
831
832 if (l_value > 0) then
833 --
834 -- Have found a person that needs to be reported in the federal W2 so
835 -- need to create an assignment action for it.
836 --
837 -- If the payroll action has not been created yet i.e. this is the
838 -- first assignment action then create it.
839 --
840 if not l_payroll_action_created then
841 --
842 -- Create payroll action for the magnetic tape report.
843 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - creating payroll action id', 8);
844 --
845 -- SQWLD - add p_media_type parameter
846 l_payroll_action_id := create_payroll_action
847 (p_report_type,
848 p_state,
849 p_trans_legal_co_id,
850 p_business_group_id,
851 p_period_end,
852 p_media_type);
853 --
854 -- Flag the creation of the payroll action.
855 --
856 l_payroll_action_created := true;
857 --
858 end if;
859 --
860 -- Create the assignment action to represnt the person / tax unit
861 -- combination.
862 --
863 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - creating assignment action id', 9);
864 l_assignment_action_id := create_assignment_action
865 (l_payroll_action_id,
866 l_assignment_id,
867 l_tax_unit_id);
868
869 if (p_report_type = 'W2' and p_state <> 'FED' and
870 l_value > 9999999.99) then
871
872 update pay_assignment_actions aa
873 set aa.serial_number = 999999
874 where aa.assignment_action_id = l_assignment_action_id;
875
876 end if;
877
878 end if;
879
880 end if;
881 --
882 -- Record the current values for the next time around the loop.
883 --
884 l_prev_person_id := l_person_id;
885 l_prev_tax_unit_id := l_tax_unit_id;
886 --
887 END LOOP;
888 COMMIT;
889 --
890 -- Close cursor used for processing SQL statement.
891 --
892 IF l_state = 'FED' THEN
893 --
894 CLOSE c_federal;
895 --
896 ELSE
897 --
898 CLOSE c_state;
899 --
900 END IF;
901
902 --
903 -- A payroll action has been created.
904 --
905 if l_payroll_action_created then
906 --
907 -- Update the population status of the payroll action to indicate that all
908 -- the assignment actions have been created for it.
909 --
910 hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - updating pay_payroll_actions', 10);
911 update pay_payroll_actions PPA
912 set PPA.action_population_status = 'C'
913 where PPA.payroll_action_id = l_payroll_action_id;
914 --
915 -- Make the changes permanent.
916 --
917 commit;
918 --
919 end if;
920 --
921 return (l_payroll_action_id);
922 --
923 end generate_people_list;
924 --
925 -----------------------------------------------------------------------------
926 -- Name
927 -- redo
928 -- Purpose
929 -- Calls the procedure run_magtape directly from SRS. This procedure
930 -- handles the error buffer and return code interface with SRS.
931 -- We are going to derive all the parameters from the vi
932 -- Arguments
933 -- Notes
934 -- SQWLD - add support for media type by parsing leg params for media value 'D' or 'M'
935 -----------------------------------------------------------------------------
936 procedure redo
937 (
938 errbuf out nocopy varchar2,
939 retcode out nocopy number,
940 p_payroll_action_id in varchar2
941 ) is
942 --
943 l_effective_date date;
944 l_report_type varchar2(10);
945 l_state varchar2(10);
946 l_reporting_year varchar2(10);
947 l_reporting_quarter varchar2(10);
948 l_trans_legal_co_id varchar2(10);
949 l_media_type varchar2(32);
950
951 begin
952 --
953 -- Derive the rest of the parameters from the payroll_action_id
954 --
955 hr_utility.set_location('pay_us_magtape_reporting.redo - get parameters', 1);
956 select PA.effective_date,
957 ltrim(substr(PA.legislative_parameters, 11,5)),
958 ltrim(substr(PA.legislative_parameters, 17,5)),
959 to_char(PA.effective_date,'YYYY'),
960 decode(ltrim(substr(PA.legislative_parameters, 11,5)),
961 'W2' , to_char(PA.effective_date,'YYYY'),
962 'SQWL', to_char(PA.effective_date,'MMYY')),
963 ltrim(substr(PA.legislative_parameters, 23,5)),
964 ltrim(substr(PA.legislative_parameters, 29,5))
965 into l_effective_date,
966 l_report_type,
967 l_state,
968 l_reporting_year,
969 l_reporting_quarter,
970 l_trans_legal_co_id,
971 l_media_type
972 from pay_payroll_actions PA
973 where PA.payroll_action_id = p_payroll_action_id;
974
975 --
976 hr_utility.set_location('pay_us_magtape_reporting.redo - update pay_payroll_actions', 2);
977 update pay_payroll_actions pa
978 set PA.action_status = 'M'
979 where PA.payroll_action_id = p_payroll_action_id;
980
981 hr_utility.set_location('pay_us_magtape_reporting.redo - update pay_assignment_actions', 3);
982 update pay_assignment_actions AA
983 set AA.action_status = 'M'
984 where AA.payroll_action_id = p_payroll_action_id;
985
986 commit;
987
988 FOR i in 1..70 LOOP
989 g_people_text(i) := ' ';
990 END LOOP;
991
992 -- Start the generic magnetic tape process.
993 --
994 hr_utility.set_location('pay_us_magtape_reporting.redo - run_magtape', 4);
995 run_magtape(l_effective_date,
996 l_report_type,
997 p_payroll_action_id,
998 l_state,
999 l_reporting_year,
1000 l_reporting_quarter,
1001 l_trans_legal_co_id,
1002 l_media_type);
1003
1004 --
1005 hr_utility.set_location('pay_us_magtape_reporting.redo - update pay_assignment_actions', 5);
1006 update pay_assignment_actions AA
1007 set AA.action_status = 'C'
1008 where AA.payroll_action_id = p_payroll_action_id;
1009
1010 commit;
1011
1012 -- Set up success return code.
1013 --
1014 retcode := 0;
1015 --
1016 -- Traps all exceptions raised within the procedure, extracts the message
1017 -- text associated with the exception and sets this up for SRS to read.
1018 --
1019 exception
1020 when hr_utility.hr_error then
1021 --
1022 -- If a payroll action exists then error it.
1023 --
1024 if p_payroll_action_id is not null then
1025 error_payroll_action(p_payroll_action_id);
1026 end if;
1027 --
1028 -- Set up error message and error return code.
1029 --
1030 errbuf := g_message_text;
1031 retcode := 2;
1032 --
1033 when others then
1034 --
1035 -- If a payroll action exists then error it.
1036 --
1037 if p_payroll_action_id is not null then
1038 error_payroll_action(p_payroll_action_id);
1039 end if;
1040 --
1041 -- Set up error message and error return code.
1042 --
1043 errbuf := sqlerrm;
1044 retcode := 2;
1045 --
1046 end redo;
1047
1048 -----------------------------------------------------------------------------
1049 -- Name
1050 -- run_magtape
1051 -- Purpose
1052 -- Submits the magnetic tape process to be run by the concurrent manager.
1053 -- We also define the name of the output and the format here
1054 -- Arguments
1055 -- Notes
1056 -- SQWLD - add p_media_type parameter
1057 -----------------------------------------------------------------------------
1058 --
1059 procedure run_magtape
1060 (
1061 p_effective_date date,
1062 p_report_type varchar2,
1063 p_payroll_action_id varchar2,
1064 p_state varchar2,
1065 p_reporting_year varchar2,
1066 p_reporting_quarter varchar2,
1067 p_trans_legal_co_id varchar2,
1068 p_media_type varchar2
1069 ) is
1070
1071 l_format varchar2(30);
1072 --
1073 -- Filenames of the output stuff
1074 --
1075 l_magfilename varchar2(8);
1076 l_sumfilename varchar2(8);
1077 --
1078 -- Request id of the magnetic tape process.
1079 --
1080 l_request_id number;
1081 --
1082 -- Variable to hold the result of waiting for the magnetic tape process
1083 -- to finish.
1084 --
1085 l_wait boolean;
1086 --
1087 -- Status information returned from the processing of the magnetic tape
1088 -- process.
1089 --
1090 l_phase varchar2(30);
1091 l_status varchar2(30);
1092 l_dev_phase varchar2(30);
1093 l_dev_status varchar2(30);
1094 l_date varchar2(30);
1095 l_message varchar2(255);
1096 --
1097 l_name varchar2(240);
1098 l_employee_number varchar2(30);
1099 l_people_cnt number;
1100 l_person_id number;
1101
1102 cursor get_highly_comp is
1103 select a.person_id
1104 from pay_assignment_actions aa, per_assignments_f a
1105 where aa.payroll_action_id = p_payroll_action_id
1106 and aa.assignment_id = a.assignment_id
1107 and aa.serial_number is NOT NULL;
1108
1109 begin
1110
1111 --
1112 -- Get the format to be used to produce the report.
1113 --
1114 hr_utility.set_location('pay_us_magtape_reporting.run_magtape - get report format', 1);
1115 -- SQWLD - pass p_media_type parameter
1116 l_format := lookup_format(p_effective_date,
1117 p_report_type,
1118 p_state,
1119 p_media_type);
1120 --
1121 -- Determine the name of the output filename
1122 --
1123 hr_utility.set_location('pay_us_magtape_reporting.run_magtape - determine filename', 2);
1124
1125 if p_report_type = 'W2' and p_state = 'FED' then
1126
1127 l_magfilename := p_state || p_report_type || '_' ||
1128 substr(to_char(p_effective_date,'YY'),1,2);
1129 l_sumfilename := '6559_' || substr(to_char(p_effective_date,'YY'),1,2);
1130
1131 elsif p_report_type = 'W2' and p_state <> 'FED' then
1132
1133 l_magfilename := p_state || p_report_type || '_' ||
1134 substr(to_char(p_effective_date,'YY'),1,2);
1135 l_sumfilename := l_magfilename;
1136
1137 elsif p_report_type = 'SQWL' then
1138
1139 l_magfilename := p_state || '_' ||
1140 substr(to_char(p_effective_date,'MMYY'),1,4);
1141 l_sumfilename := l_magfilename;
1142 else
1143
1144 l_magfilename := p_report_type;
1145 l_sumfilename := p_report_type;
1146
1147 end if;
1148
1149 hr_utility.set_location('pay_us_magtape_reporting.run_magtape - determine reporting quarter', 3);
1150 if substr(p_reporting_quarter, 1, 2) = '03' then
1151 l_date := 'March 31';
1152 elsif substr(p_reporting_quarter, 1, 2) = '06' then
1153 l_date := 'June 30';
1154 elsif substr(p_reporting_quarter, 1, 2) = '09' then
1155 l_date := 'September 30';
1156 else
1157 l_date := 'December 31';
1158 end if;
1159
1160 l_people_cnt := 0;
1161
1162 FOR i in 1..70 LOOP
1163 g_people_text(i) := ' ';
1164 END LOOP;
1165
1166 open get_highly_comp;
1167 loop
1168 fetch get_highly_comp into l_person_id;
1169 EXIT WHEN get_highly_comp%NOTFOUND;
1170
1171 get_person_name(l_person_id, l_name, l_employee_number);
1172
1173 l_people_cnt := l_people_cnt + 1;
1174
1175 g_people_text(l_people_cnt) := fnd_global.local_chr(10) || l_name || ' (' || l_employee_number || ')';
1176
1177 end loop;
1178
1179 close get_highly_comp;
1180
1181 g_num_peo := l_people_cnt;
1182
1183 -- Start the generic magnetic tape process using the concurrent manager NB.
1184 -- the process is registered with SRS. This process is run as a sub request
1185 -- of the process running this PLSQL. This should result in the PLSQL
1186 -- process being paused while the magnetic tape process runs.
1187 --
1188 hr_utility.set_location('pay_us_magtape_reporting.run_magtape - submit request', 4);
1189 l_request_id :=
1190 fnd_request.submit_request
1191 ('PAY'
1192 ,program => 'PYUMAG'
1193 ,description => null
1194 ,start_time => null
1195 ,sub_request => FALSE -- TRUE
1196 ,argument1 => 'pay_magtape_generic.new_formula'
1197 ,argument2 => l_magfilename
1198 ,argument3 => l_sumfilename
1199 ,argument4 => fnd_date.date_to_canonical(p_effective_date)
1200 ,argument5 => 'MAGTAPE_REPORT_ID=' || l_format
1201 ,argument6 => 'TRANSFER_PAYROLL_ACTION_ID=' || p_payroll_action_id
1202 ,argument7 => 'TRANSFER_STATE=' || p_state
1203 ,argument8 => 'TRANSFER_REPORTING_YEAR=' || p_reporting_year
1204 ,argument9 => 'TRANSFER_REPORTING_QUARTER=' || p_reporting_quarter
1205 ,argument10 => 'TRANSFER_TRANS_LEGAL_CO_ID=' || p_trans_legal_co_id
1206 ,argument11 => 'TRANSFER_DATE=' || l_date
1207 ,argument12 => 'TRANSFER_COUNT=' || to_char(g_num_peo)
1208 ,argument13 => 'TRANSFER_MESSAGE_1=' || g_people_text(1)
1209 ,argument14 => 'TRANSFER_MESSAGE_2=' || g_people_text(2)
1210 ,argument15 => 'TRANSFER_MESSAGE_3=' || g_people_text(3)
1211 ,argument16 => 'TRANSFER_MESSAGE_4=' || g_people_text(4)
1212 ,argument17 => 'TRANSFER_MESSAGE_5=' || g_people_text(5)
1213 ,argument18 => 'TRANSFER_MESSAGE_6=' || g_people_text(6)
1214 ,argument19 => 'TRANSFER_MESSAGE_7=' || g_people_text(7)
1215 ,argument20 => 'TRANSFER_MESSAGE_8=' || g_people_text(8)
1216 ,argument21 => 'TRANSFER_MESSAGE_9=' || g_people_text(9)
1217 ,argument22 => 'TRANSFER_MESSAGE_10=' || g_people_text(10)
1218 ,argument23 => 'TRANSFER_MESSAGE_11=' || g_people_text(11)
1219 ,argument24 => 'TRANSFER_MESSAGE_12=' || g_people_text(12)
1220 ,argument25 => 'TRANSFER_MESSAGE_13=' || g_people_text(13)
1221 ,argument26 => 'TRANSFER_MESSAGE_14=' || g_people_text(14)
1222 ,argument27 => 'TRANSFER_MESSAGE_15=' || g_people_text(15)
1223 ,argument28 => 'TRANSFER_MESSAGE_16=' || g_people_text(16)
1224 ,argument29 => 'TRANSFER_MESSAGE_17=' || g_people_text(17)
1225 ,argument30 => 'TRANSFER_MESSAGE_18=' || g_people_text(18)
1226 ,argument31 => 'TRANSFER_MESSAGE_19=' || g_people_text(19)
1227 ,argument32 => 'TRANSFER_MESSAGE_20=' || g_people_text(20)
1228 ,argument33 => 'TRANSFER_MESSAGE_21=' || g_people_text(21)
1229 ,argument34 => 'TRANSFER_MESSAGE_22=' || g_people_text(22)
1230 ,argument35 => 'TRANSFER_MESSAGE_23=' || g_people_text(23)
1231 ,argument36 => 'TRANSFER_MESSAGE_24=' || g_people_text(24)
1232 ,argument37 => 'TRANSFER_MESSAGE_25=' || g_people_text(25)
1233 ,argument38 => 'TRANSFER_MESSAGE_26=' || g_people_text(26)
1234 ,argument39 => 'TRANSFER_MESSAGE_27=' || g_people_text(27)
1235 ,argument40 => 'TRANSFER_MESSAGE_28=' || g_people_text(28)
1236 ,argument41 => 'TRANSFER_MESSAGE_29=' || g_people_text(29)
1237 ,argument42 => 'TRANSFER_MESSAGE_30=' || g_people_text(30)
1238 ,argument43 => 'TRANSFER_MESSAGE_31=' || g_people_text(31)
1239 ,argument44 => 'TRANSFER_MESSAGE_32=' || g_people_text(32)
1240 ,argument45 => 'TRANSFER_MESSAGE_33=' || g_people_text(33)
1241 ,argument46 => 'TRANSFER_MESSAGE_34=' || g_people_text(34)
1242 ,argument47 => 'TRANSFER_MESSAGE_35=' || g_people_text(35)
1243 ,argument48 => 'TRANSFER_MESSAGE_36=' || g_people_text(36)
1244 ,argument49 => 'TRANSFER_MESSAGE_37=' || g_people_text(37)
1245 ,argument50 => 'TRANSFER_MESSAGE_38=' || g_people_text(38)
1246 ,argument51 => 'TRANSFER_MESSAGE_39=' || g_people_text(39)
1247 ,argument52 => 'TRANSFER_MESSAGE_40=' || g_people_text(40)
1248 ,argument53 => 'TRANSFER_MESSAGE_41=' || g_people_text(41)
1249 ,argument54 => 'TRANSFER_MESSAGE_42=' || g_people_text(42)
1250 ,argument55 => 'TRANSFER_MESSAGE_43=' || g_people_text(43)
1251 ,argument56 => 'TRANSFER_MESSAGE_44=' || g_people_text(44)
1252 ,argument57 => 'TRANSFER_MESSAGE_45=' || g_people_text(45)
1253 ,argument58 => 'TRANSFER_MESSAGE_46=' || g_people_text(46)
1254 ,argument59 => 'TRANSFER_MESSAGE_47=' || g_people_text(47)
1255 ,argument60 => 'TRANSFER_MESSAGE_48=' || g_people_text(48)
1256 ,argument61 => 'TRANSFER_MESSAGE_49=' || g_people_text(49)
1257 ,argument62 => 'TRANSFER_MESSAGE_50=' || g_people_text(50)
1258 ,argument63 => 'TRANSFER_MESSAGE_51=' || g_people_text(51)
1259 ,argument64 => 'TRANSFER_MESSAGE_52=' || g_people_text(52)
1260 ,argument65 => 'TRANSFER_MESSAGE_53=' || g_people_text(53)
1261 ,argument66 => 'TRANSFER_MESSAGE_54=' || g_people_text(54)
1262 ,argument67 => 'TRANSFER_MESSAGE_55=' || g_people_text(55)
1263 ,argument68 => 'TRANSFER_MESSAGE_56=' || g_people_text(56)
1264 ,argument69 => 'TRANSFER_MESSAGE_57=' || g_people_text(57)
1265 ,argument70 => 'TRANSFER_MESSAGE_58=' || g_people_text(58)
1266 ,argument71 => 'TRANSFER_MESSAGE_59=' || g_people_text(59)
1267 ,argument72 => 'TRANSFER_MESSAGE_60=' || g_people_text(60)
1268 ,argument73 => 'TRANSFER_MESSAGE_61=' || g_people_text(61)
1269 ,argument74 => 'TRANSFER_MESSAGE_62=' || g_people_text(62)
1270 ,argument75 => 'TRANSFER_MESSAGE_63=' || g_people_text(63)
1271 ,argument76 => 'TRANSFER_MESSAGE_64=' || g_people_text(64)
1272 ,argument77 => 'TRANSFER_MESSAGE_65=' || g_people_text(65)
1273 ,argument78 => 'TRANSFER_MESSAGE_66=' || g_people_text(66)
1274 ,argument79 => 'TRANSFER_MESSAGE_67=' || g_people_text(67)
1275 ,argument80 => 'TRANSFER_MESSAGE_68=' || g_people_text(68)
1276 ,argument81 => 'TRANSFER_MESSAGE_69=' || g_people_text(69)
1277 ,argument82 => 'TRANSFER_MESSAGE_70=' || g_people_text(70)
1278
1279 );
1280 --
1281 -- Detect if the request was really submitted.
1282 -- If it has not then handle the error.
1283 --
1284 if l_request_id = 0 then
1285 g_message_text := 'Failed to submit concurrent request';
1286 raise hr_utility.hr_error;
1287 end if;
1288 --
1289 -- Request has been accepted so update payroll action with the
1290 -- request details.
1291 --
1292 hr_utility.set_location('pay_us_magtape_reporting.run_magtape - update pay_payroll_actions', 5);
1293 update pay_payroll_actions PPA
1294 set PPA.request_id = l_request_id
1295 where PPA.payroll_action_id = p_payroll_action_id;
1296 --
1297 -- Issue a commit to synchronise the concurrent manager.
1298 --
1299 commit;
1300 --
1301 -- Wait for process to finish and get its status..
1302 --
1303 hr_utility.set_location('pay_us_magtape_reporting.run_magtape - wait for process to finish', 6);
1304 -- l_wait := fnd_concurrent.wait_for_request
1305 -- (request_id => l_request_id
1306 -- ,interval => 5
1307 -- ,max_wait => 9999999 /* until child finishes */
1308 -- ,phase => l_phase
1309 -- ,status => l_status
1310 -- ,dev_phase => l_dev_phase
1311 -- ,dev_status => l_dev_status
1312 -- ,message => l_message);
1313 --
1314 -- Process has failed.
1315 --
1316 -- if not (l_dev_phase = 'COMPLETE' and l_dev_status = 'NORMAL') then
1317 -- g_message_text := 'Magnetic tape process has failed';
1318 -- raise hr_utility.hr_error;
1319 -- end if;
1320 --
1321 end run_magtape;
1322 --
1323 -----------------------------------------------------------------------------
1324 -- Name
1325 -- get_dates
1326 -- Purpose
1327 -- The dates
1328 -- are dependent on the report being run i.e. a W2 report shows information
1329 -- for a tax year while a SQWL report shows information for a quarter
1330 -- within a tax year.
1331 -- Arguments
1332 -- Notes
1333 -----------------------------------------------------------------------------
1334 --
1335 procedure get_dates
1336 (
1337 p_report_type varchar2,
1338 p_quarter varchar2,
1339 p_year varchar2,
1340 p_period_end in out nocopy date,
1341 p_quarter_start in out nocopy date,
1342 p_quarter_end in out nocopy date,
1343 p_year_start in out nocopy date,
1344 p_year_end in out nocopy date,
1345 p_rep_year in out nocopy varchar2,
1346 p_rep_quarter in out nocopy varchar2
1347 ) is
1348
1349 l_rep_quarter varchar2(6);
1350
1351 begin
1352 --
1353 -- Report is W2 ie. a yearly report where the identifier indicates the year
1354 -- eg. 1995. The expected values for the example should be
1355 --
1356 -- p_period_end 31-DEC-1995
1357 -- p_quarter_start 01-OCT-1995
1358 -- p_quarter_end 31-DEC-1995
1359 -- p_year_start 01-JAN-1995
1360 -- p_year_end 31-DEC-1995
1361 -- p_reporting_year 1995
1362 -- p_reporting_quarter 1295
1363 --
1364 if p_report_type = 'W2' then
1365 p_rep_year := p_year;
1366 p_rep_quarter := '12' || to_char(to_date(p_year, 'YYYY'),'YY');
1367 p_period_end := to_date('31-12-'||p_rep_year, 'DD-MM-YYYY');
1368 p_quarter_start := to_date('01-10-'||p_rep_year, 'DD-MM-YYYY');
1369 p_quarter_end := to_date('31-12-'||p_rep_year, 'DD-MM-YYYY');
1370 --
1371 -- Report is SQWL ie. a quarterly report where the identifier indicates the
1372 -- quarter eg. 0395.
1373 --
1374 -- p_period_end 31-MAR-1995
1375 -- p_quarter_start 01-JAN-1995
1376 -- p_quarter_end 31-MAR-1995
1377 -- p_year_start 01-JAN-1995
1378 -- p_year_end 31-DEC-1995
1379 -- p_reporting_year 1995
1380 -- p_reporting_quarter 0395
1381 --
1382 elsif p_report_type = 'SQWL' then
1383 p_rep_year := p_year;
1384 p_rep_quarter := p_quarter || to_char(to_date(p_year, 'YYYY'),'YY');
1385 hr_utility.set_location('pay_us_magtape_reporting.get dates', 1);
1386 l_rep_quarter := p_quarter || to_char(to_date(p_year, 'YYYY'),'YYYY');
1387 hr_utility.set_location('pay_us_magtape_reporting.get dates', 2);
1388 hr_utility.trace('l_rep_quarter'||l_rep_quarter);
1389 p_period_end := last_day(to_date(l_rep_quarter, 'MMYYYY'));
1390 hr_utility.set_location('pay_us_magtape_reporting.get dates', 3);
1391 p_quarter_start := add_months(p_period_end, -3) + 1;
1392 hr_utility.set_location('pay_us_magtape_reporting.get dates', 4);
1393 p_quarter_end := last_day(to_date(l_rep_quarter, 'MMYYYY'));
1394 hr_utility.set_location('pay_us_magtape_reporting.get dates', 5);
1395 end if;
1396 --
1397 p_year_start := to_date('01-01-'||p_rep_year, 'DD-MM-YYYY');
1398 p_year_end := to_date('31-12-'||p_rep_year, 'DD-MM-YYYY');
1399 --
1400 end get_dates;
1401 --
1402 -----------------------------------------------------------------------------
1403 -- Name
1404 -- check_report_unique
1405 -- Purpose
1406 -- Makes sure that a report has not already been run which overlaps with
1407 -- the report being started.
1408 -- Arguments
1409 -- Notes
1410 -- Each report is uniquely defined by the EFFECTIVE_DATE and the
1411 -- LEGISLATIVE_PARAMETERS of the payroll action. The LEGISLATIVE_PARAMETERS
1412 -- is set to report_type || '-' || p_state. In order to resubmit this report
1413 -- we need to add transmitter legal company id onto the LEGISLATIVE PARAMETERS.
1414 -- To ensure that a report with a for the same state and same period is not run
1415 -- for different transmitters. I added the '%' to where clause.
1416 -----------------------------------------------------------------------------
1417 --
1418 procedure check_report_unique
1419 (
1420 p_business_group_id in number,
1421 p_period_end in date,
1422 p_report_type in varchar2,
1423 p_state in varchar2
1424 ) is
1425 --
1426 -- Select all payroll actions used to report W2 and SQWLs that have an
1427 -- EFFECTIVE_DATE that matches that of the report being run and have the
1428 -- same LEGISLATIVE_PARAMETERS value. If a payroll action is found then
1429 -- the report has already been run.
1430 --
1431 cursor csr_payroll_action is
1432 select PA.payroll_action_id
1433 from pay_payroll_actions PA
1434 where PA.business_group_id = p_business_group_id
1435 and PA.effective_date = p_period_end
1436 and PA.legislative_parameters like 'USMAGTAPE' || '-' ||
1437 lpad(p_report_type, 5) || '-' ||
1438 lpad(p_state , 5) || '%';
1439 --
1440 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
1441 --
1442 begin
1443 --
1444 -- Check report has not already been run.
1445 --
1446 hr_utility.set_location('pay_us_magtape_reporting.check_report_unique -opening cursor', 1);
1447 open csr_payroll_action;
1448 hr_utility.set_location('pay_us_magtape_reporting.check_report_unique -fetching cursor', 2);
1449 fetch csr_payroll_action into l_payroll_action_id;
1450 if csr_payroll_action%found then
1451 close csr_payroll_action;
1452 g_message_text := 'Report has already been run';
1453 raise hr_utility.hr_error;
1454 else
1455 hr_utility.set_location('pay_us_magtape_reporting.check_report_unique - report unique', 3);
1456 close csr_payroll_action;
1457 end if;
1458 --
1459 end check_report_unique;
1460 --
1461 -----------------------------------------------------------------------------
1462 -- Name
1463 -- lookup_format
1464 -- Purpose
1465 -- Find the format to be applied when generating the report.
1466 -- Arguments
1467 -- Notes
1468 -- SQWLD - p_media_type param
1469 -----------------------------------------------------------------------------
1470 --
1471 function lookup_format
1472 (
1473 p_period_end in date,
1474 p_report_type in varchar2,
1475 p_state in varchar2,
1476 p_media_type in varchar2
1477 ) return varchar2 is
1478 --
1479 -- Find the format to be used by the report.
1480 --
1481 -- SQWLD - cursor for tape format expects *no* 'D' at end of format name
1482 cursor csr_tape_format is
1483 select RM.report_format
1484 from pay_report_format_mappings_f RM
1485 where RM.report_type = p_report_type
1486 and RM.report_qualifier = p_state
1487 and RM.report_format not like '%D'
1488 and p_period_end between RM.effective_start_date
1489 and RM.effective_end_date;
1490 --
1491 -- SQWLD - cursor for disk format expects 'D' at end of format name
1492 cursor csr_disk_format is
1493 select RM.report_format
1494 from pay_report_format_mappings_f RM
1495 where RM.report_type = p_report_type
1496 and RM.report_qualifier = p_state
1497 and RM.report_format like '%D'
1498 and p_period_end between RM.effective_start_date
1499 and RM.effective_end_date;
1500 --
1501 l_format varchar2(30);
1502 --
1503 begin
1504 --
1505
1506 hr_utility.trace('lookup_format, p_media_type: <' || p_media_type || '>');
1507
1508 -- BHOMAN _ hard-coded support for SD diskette format
1509 -- if p_report_type = 'SQWL' AND p_state in ('SD') AND p_media_type = 'PD' then
1510 -- return 'SDSQWLD';
1511 -- end if;
1512
1513 -- SQLWLD use different cursor depending on p_report_type and p_media_type
1514 if p_report_type = 'SQWL' AND p_media_type = 'PD' then
1515 -- Get the diskette format.
1516 --
1517 --
1518 open csr_disk_format;
1519 hr_utility.set_location('pay_us_magtape_reporting.lookup_format - get format', 1);
1520 fetch csr_disk_format into l_format;
1521 if csr_disk_format%notfound then
1522 close csr_disk_format;
1523 g_message_text := 'Cannot find format for report';
1524 raise hr_utility.hr_error;
1525 else
1526 hr_utility.set_location('pay_us_magtape_reporting.lookup_format - found format', 2);
1527 close csr_disk_format;
1528 end if;
1529 else -- not SQWLD and diskette
1530 -- Get the tape format.
1531 --
1532 open csr_tape_format;
1533 hr_utility.set_location('pay_us_magtape_reporting.lookup_format - get format', 1);
1534 fetch csr_tape_format into l_format;
1535 if csr_tape_format%notfound then
1536 close csr_tape_format;
1537 g_message_text := 'Cannot find format for report';
1538 raise hr_utility.hr_error;
1539 else
1540 hr_utility.set_location('pay_us_magtape_reporting.lookup_format - found format', 2);
1541 close csr_tape_format;
1542 end if;
1543 end if;
1544 --
1545 return (l_format);
1546 --
1547 end lookup_format;
1548 --
1549 -----------------------------------------------------------------------------
1550 -- Name
1551 -- run
1552 -- Purpose
1553 -- This is the main procedure responsible for generating the list of
1554 -- assignment actions and then submitting the request to produce the
1555 -- magnetic tape report.
1556 -- Arguments
1557 -- errbuf - error message string passed back to SRS.
1558 -- retcode - error code passed back to SRS ie.
1559 -- 0 - Success
1560 -- 1 - Warning
1561 -- 2 - Error
1562 -- p_business_group_id - business group the user is running under when the
1563 -- report is generated.
1564 -- p_report_type - either 'W2' or 'SQWL'
1565 -- p_state - either 'FED' for federal or the state code of a
1566 -- state eg. PA for Pennsylvania
1567 -- p_quarter - identifies the quarter being reported eg. 03 is
1568 -- the 1st quarter. This is defaulted to '12' for
1569 -- the W2 Report
1570 -- p_year - identifies the year being reported on.
1571 -- p_trans_legal_co_id - identifies the Transmitter Tax Unit.
1572
1573 -- Notes
1574 -- This procedure is invoked from the SRS screens.
1575 -----------------------------------------------------------------------------
1576 --
1577 procedure run
1578 (
1579 errbuf out nocopy varchar2,
1580 retcode out nocopy number,
1581 p_business_group_id in number,
1582 p_report_type in varchar2,
1583 p_state in varchar2,
1584 p_quarter in varchar2,
1585 p_year in varchar2,
1586 p_trans_legal_co_id in number,
1587 p_media_type in varchar2
1588 ) is
1589 --
1590
1591 c_period_end date;
1592 c_quarter_start date;
1593 c_quarter_end date;
1594 c_year_start date;
1595 c_year_end date;
1596 c_reporting_year varchar2(4);
1597 c_reporting_quarter varchar2(4);
1598 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
1599 l_trans_legal_co_id number;
1600 l_request_id number;
1601 l_format varchar2(30);
1602 --
1603 begin
1604 --
1605 -- Derive the start and end dates of the period being reported on.
1606 --
1607 hr_utility.set_location('pay_us_magtape_reporting.run - get dates', 1);
1608 get_dates(p_report_type,
1609 p_quarter,
1610 p_year,
1611 c_period_end,
1612 c_quarter_start,
1613 c_quarter_end,
1614 c_year_start,
1615 c_year_end,
1616 c_reporting_year,
1617 c_reporting_quarter);
1618 --
1619 -- Make sure the report has not already been run.
1620 --
1621 hr_utility.set_location('pay_us_magtape_reporting.run - check report unique', 2);
1622 check_report_unique(p_business_group_id,
1623 c_period_end,
1624 p_report_type,
1625 p_state);
1626
1627
1628 --
1629 -- Get the format to be used to produce the report.
1630 --
1631 hr_utility.set_location('pay_us_magtape_reporting.run - get report format', 3);
1632 l_format := lookup_format(c_period_end,
1633 p_report_type,
1634 p_state,
1635 p_media_type);
1636
1637
1638 --
1639 -- See if a transmitter legal company was specified NB. it is not
1640 -- possible to pass NULL parameters to the process so a value has to be
1641 -- set ie. '-1'.
1642 --
1643 l_trans_legal_co_id := nvl(p_trans_legal_co_id, -1);
1644
1645
1646 --
1647 -- Generate payroll action and assignment actions for all the people to be
1648 -- reported on NB. the list of people is dependent on the report being
1649 -- run. If there are no people to report on then there is no need to
1650 -- submit the process to produce the report. The variable
1651 -- l_payroll_action_id holds the ID of the created payroll action.
1652 --
1653 hr_utility.set_location('pay_us_magtape_reporting.run - generate people list', 4);
1654 l_payroll_action_id := generate_people_list(p_report_type,
1655 p_state,
1656 l_trans_legal_co_id,
1657 p_business_group_id,
1658 c_period_end,
1659 c_quarter_start,
1660 c_quarter_end,
1661 c_year_start,
1662 c_year_end,
1663 p_media_type);
1664
1665
1666 --
1667 -- A payroll action has been created which means that at least one
1668 -- assignment action has been created so the magnetic tape report has to
1669 -- be run. Before we run the magnetic tape proces we will archive
1670 -- certain DBitems
1671 --
1672 if l_payroll_action_id is not null then
1673 --
1674 -- Initiate archiving
1675 --
1676 hr_utility.set_location('pay_us_magtape_reporting.run - initiate archiving', 5);
1677 pay_magtape_extract.arch_main('S',
1678 l_payroll_action_id);
1679 --
1680 -- Start the generic magnetic tape process.
1681 --
1682 hr_utility.set_location('pay_us_magtape_reporting.run - run magtape', 6);
1683 run_magtape(c_period_end,
1684 p_report_type,
1685 l_payroll_action_id,
1686 p_state,
1687 c_reporting_year,
1688 c_reporting_quarter,
1689 l_trans_legal_co_id,
1690 p_media_type);
1691 --
1692 -- A payroll action has not been created so there are no people to report
1693 -- on.
1694 --
1695 else
1696 --
1697 -- Set up message explaining why report was not produced.
1698 --
1699 g_message_text := 'There are no employees that match ' ||
1700 'the criteria for the report';
1701 hr_utility.raise_error;
1702 --
1703 end if;
1704 --
1705 -- Process completed successfully.
1706 --
1707 -- Update the status of the payroll and assignments actions.
1708 --
1709 hr_utility.set_location('pay_us_magtape_reporting.run - update action status', 6);
1710 update_action_statuses(l_payroll_action_id);
1711 --
1712 -- Set up success return code.
1713 --
1714 retcode := 0;
1715 --
1716 -- Traps all exceptions raised within the procedure, extracts the message
1717 -- text associated with the exception and sets this up for SRS to read.
1718 --
1719 exception
1720 when hr_utility.hr_error then
1721 --
1722 -- If a payroll action exists then error it.
1723 --
1724 if l_payroll_action_id is not null then
1725 error_payroll_action(l_payroll_action_id);
1726 end if;
1727 --
1728 -- Set up error message and error return code.
1729 --
1730 errbuf := g_message_text;
1731 retcode := 2;
1732 --
1733 when others then
1734 --
1735 -- If a payroll action exists then error it.
1736 --
1737 if l_payroll_action_id is not null then
1738 error_payroll_action(l_payroll_action_id);
1739 end if;
1740 --
1741 -- Set up error message and error return code.
1742 --
1743 errbuf := sqlerrm;
1744 retcode := 2;
1745 --
1746 end run;
1747 --
1748 end pay_us_magtape_reporting;
[Home] [Help]