1 package body pay_mws_magtape_reporting as
2 /* $Header: pymwsrep.pkb 120.1 2005/10/05 03:51:18 sackumar noship $ */
3 g_message_text varchar2(240);
4
5 /* table Variables used for calculating the wages of the assignment */
6 g_asg_tab numeric_data_table;
7 g_asg_end_dt_tab character_data_table;
8 g_tax_unit_id_tab numeric_data_table;
9 g_wages_tab numeric_data_table;
10 g_asg_wages_tab numeric_data_table;
11 g_ctr number := 0;
12 g_reg_earn_bal_id pay_defined_balances.defined_balance_id%type;
13 g_supp_earn_bal_id pay_defined_balances.defined_balance_id%type;
14 g_def_gre_bal_id pay_defined_balances.defined_balance_id%type;
15 g_sec_gre_bal_id pay_defined_balances.defined_balance_id%type;
16 g_dep_gre_bal_id pay_defined_balances.defined_balance_id%type;
17 g_supp_nwfit_bal_id pay_defined_balances.defined_balance_id%type;
18
19 /* added by skutteti for the pre tax enhancement */
20 g_pre_tax_bal_id pay_defined_balances.defined_balance_id%type;
21
22 procedure get_balance_id is
23 begin
24 g_reg_earn_bal_id := Pay_Mag_Utils.Bal_Db_Item(
25 'REGULAR_EARNINGS_PER_GRE_QTD');
26 g_supp_earn_bal_id := Pay_Mag_Utils.Bal_Db_Item(
27 'SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_PER_GRE_QTD');
28 --
29 -- the following has been commented for the pre-tax enhancements
30 -- by skutteti. Individual pre-tax balances has been replaced
31 -- by one balance g_pre_tax_bal_id
32 --
33 --g_def_gre_bal_id := Pay_Mag_Utils.Bal_Db_Item(
34 -- 'DEF_COMP_401K_PER_GRE_QTD');
35 --g_sec_gre_bal_id := Pay_Mag_Utils.Bal_Db_Item(
36 -- 'SECTION_125_PER_GRE_QTD');
37 --g_dep_gre_bal_id := Pay_Mag_Utils.Bal_Db_Item(
38 -- 'DEPENDENT_CARE_PER_GRE_QTD');
39 --
40 g_pre_tax_bal_id := Pay_Mag_Utils.Bal_Db_Item(
41 'PRE_TAX_DEDUCTIONS_PER_GRE_QTD');
42
43 g_supp_nwfit_bal_id := Pay_Mag_Utils.Bal_Db_Item(
44 'SUPPLEMENTAL_EARNINGS_FOR_NWFIT_SUBJECT_TO_TAX_PER_GRE_QTD');
45
46 end get_balance_id;
47
48 function get_wages(p_assignment_id in number,
49 p_tax_unit_id in number,
50 p_effective_end_date in date)
51 return number is
52 l_total_wages number;
53 l_reg_earn_wages number;
54 l_supp_earn_wages number;
55 l_def_gre_wages number;
56 l_sec_gre_wages number;
57 l_dep_gre_wages number;
58 l_supp_nwfit_wages number;
59 l_pre_gre_wages number; /* skutteti */
60
61 begin
62 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
63
64 l_reg_earn_wages := pay_balance_pkg.get_value (g_reg_earn_bal_id,
65 p_assignment_id,
66 p_effective_end_date);
67
68 l_supp_earn_wages := pay_balance_pkg.get_value (g_supp_earn_bal_id,
69 p_assignment_id,
70 p_effective_end_date);
71 --
72 -- Pre-tax enhancements by skutteti on 10-jul-1999
73 -- Removed individual pre-tax categories and replaced by one generic
74 -- Pre-tax deduction component.
75 --
76 -- l_def_gre_wages := pay_balance_pkg.get_value (g_def_gre_bal_id,
77 -- p_assignment_id,
78 -- p_effective_end_date);
79 --
80 -- l_sec_gre_wages := pay_balance_pkg.get_value (g_sec_gre_bal_id,
81 -- p_assignment_id,
82 -- p_effective_end_date);
83 --
84 --
85 -- l_dep_gre_wages := pay_balance_pkg.get_value (g_dep_gre_bal_id,
86 -- p_assignment_id,
87 -- p_effective_end_date);
88 --
89 -- replace the above by the following
90 --
91 l_pre_gre_wages := pay_balance_pkg.get_value (g_pre_tax_bal_id,
92 p_assignment_id,
93 p_effective_end_date);
94
95 l_supp_nwfit_wages := pay_balance_pkg.get_value (g_supp_nwfit_bal_id,
96 p_assignment_id,
97 p_effective_end_date);
98
99
100 --
101 -- commented the following and replaced with new code by skutteti
102 --
103 -- l_total_wages := (l_reg_earn_wages + l_supp_earn_wages -
104 -- l_def_gre_wages - l_sec_gre_wages - l_dep_gre_wages )
105 -- + l_supp_nwfit_wages;
106 --
107 l_total_wages := l_reg_Earn_wages + l_supp_earn_wages -
108 l_pre_gre_wages + l_supp_nwfit_wages;
109
110
111 return (round(l_total_wages));
112
113 end get_wages;
114
115
116
117 /* Name : Create_Assignment_Action
118 Purpose : Create an assignment action for each person to be
119 reported on within the magnetic tape report identified by
120 the parent payroll action.
121 */
122
123 function Create_Assignment_Action ( p_payroll_action_id in number,
124 p_assignment_id in number,
125 p_tax_unit_id in number,
126 p_asg_wages in number,
127 p_asg_end_dt in varchar)
128 return number is
129
130 /* Cursor to fetch the newly created assignment_action_id. There could
131 be several assignment actions for the same assignment and the only way
132 to find the newly created one is to fetch the one that has not had the
133 tax_unit_id updated yet. */
134
135 CURSOR csr_assignment_action IS
136 SELECT aa.assignment_action_id
137 FROM pay_assignment_actions aa
138 WHERE aa.payroll_action_id = p_payroll_action_id
139 AND aa.assignment_id = p_assignment_id
140 AND aa.tax_unit_id IS NULL;
141
142 /* Local variables. */
143
144 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
145 l_serial_no varchar2(30);
146
147 begin
148
149 hr_utility.set_location('pay_mag_utils.create_assignment_action',1);
150
151 /* Create assignment action to identify a specific person's inclusion in the
152 magnetic tape report identified by the parent payroll action. The
153 assignment action has to be sequenced within the other assignment actions
154 according to the date of the payroll action so that the derivation of
155 any balances based on the assignment action is correct. */
156
157 /* First Round up the wages to the nearest dollar and convert it to char
158 so that it can be stored in the serial number coulmn of the
159 PAY_ASSIGNMENT_ACTIONS table We will also store the effective end date
160 of the assignment along with the wages for the assignment because we
161 may have a person having 2 assignments for the same organization and
162 and GRE with different effective start and end dates (falling within
163 the same quarter e.g. one having start dt = 01-jan-1990
164 end dt = 07-mar-1990 and another having start dt = 08-mar-1990
165 end dt = 29-aug-1997. Now, the first record may have non zero wages
166 but the second may not have non zero wages. So, in order to decide that
167 which record to pick up we need to have the end date of the assignment
168 taged to the wages so that we can specifically pick up the assignment
169 corresponding to the wages stored in the pay_assignmnet_actions table.
170 The first 20 positions will be for the wages and the last 10 positions
171 for the assignment end date
172 */
173
174 l_serial_no := lpad((to_char(p_asg_wages)),20) || p_asg_end_dt;
175
176 hrassact.inassact(p_payroll_action_id, p_assignment_id);
177
178
179 /* Get the assignment_action_id of the newly created assignment action. */
180
181 hr_utility.set_location('pay_mag_utils.create_assignment_action',2);
182
183 open csr_assignment_action;
184 fetch csr_assignment_action INTO l_assignment_action_id;
185 close csr_assignment_action;
186
187 update pay_assignment_actions aa
188 set aa.tax_unit_id = p_tax_unit_id,
189 aa.serial_number = l_serial_no
190 where aa.assignment_action_id = l_assignment_action_id;
191
192 hr_utility.set_location('pay_mag_utils.create_assignment_action',3);
193
194 /* Return id of new row. */
195
196 return (l_assignment_action_id);
197
198 end Create_Assignment_Action;
199
200
201 procedure do_asg_break_processing(p_payroll_action_id in number,
202 p_ctr in out nocopy number,
203 end_of_cursor in boolean) is
204 l_asg_action_id pay_assignment_actions.assignment_action_id%type;
205 begin
206
207 if (p_ctr = 4) then /* We have 3 records for the assg */
208
209 /* Check if the assignment has remained in the same GRE */
210 if ((g_tax_unit_id_tab(1) = g_tax_unit_id_tab(2)) and
211 (g_tax_unit_id_tab(1) = g_tax_unit_id_tab(3))) then
212 g_asg_wages_tab(3) := g_wages_tab(3) - g_wages_tab(2);
213 g_asg_wages_tab(2) := g_wages_tab(2) - g_wages_tab(1);
214 g_asg_wages_tab(1) := g_wages_tab(1);
215
216 /* Assignment changed GRE in the 3rd month */
217 elsif ((g_tax_unit_id_tab(1) = g_tax_unit_id_tab(2)) and
218 (g_tax_unit_id_tab(1) <> g_tax_unit_id_tab(3))) then
219 g_asg_wages_tab(3) := g_wages_tab(3);
220 g_asg_wages_tab(2) := g_wages_tab(2) - g_wages_tab(1);
221 g_asg_wages_tab(1) := g_wages_tab(1);
222
223 /* Assignment changed GRE in the 2nd month but came back to the 1st
224 GRE in the 3rd month */
225 elsif ((g_tax_unit_id_tab(1) <> g_tax_unit_id_tab(2)) and
226 (g_tax_unit_id_tab(1) = g_tax_unit_id_tab(3))) then
227 g_asg_wages_tab(3) := g_wages_tab(3) - g_wages_tab(1);
228 g_asg_wages_tab(2) := g_wages_tab(2);
229 g_asg_wages_tab(1) := g_wages_tab(1);
230
231 /* Assignment changed GRE in all the months */
232 else
233 g_asg_wages_tab(3) := g_wages_tab(3);
234 g_asg_wages_tab(2) := g_wages_tab(2);
235 g_asg_wages_tab(1) := g_wages_tab(1);
236 end if;
237
238 elsif (p_ctr = 3) then /* 2 records for the assignment */
239
240 /* Check if the assignment has remained in the same GRE */
241 if (g_tax_unit_id_tab(1) = g_tax_unit_id_tab(2)) then
242 g_asg_wages_tab(2) := g_wages_tab(2) - g_wages_tab(1);
243 g_asg_wages_tab(1) := g_wages_tab(1);
244
245 /* Assignment has changed GREs */
246 else
247 g_asg_wages_tab(1) := g_wages_tab(1);
248 g_asg_wages_tab(2) := g_wages_tab(2);
249 end if;
250
251 elsif (p_ctr = 2) then /* 1 records for the assignment */
252 /* There is only one GRE since there is only one record. */
253 g_asg_wages_tab(1) := g_wages_tab(1);
254 end if;
255
256 /* Create the assignment action for all the applicable records */
257 for j in 1..p_ctr - 1 loop
258
259 l_asg_action_id := Create_Assignment_Action
260 (p_payroll_action_id,
261 g_asg_tab(j),
262 g_tax_unit_id_tab(j),
263 g_asg_wages_tab(j),
264 g_asg_end_dt_tab(j));
265
266 end loop;
267
268 /* Now is the time for initialization */
269 if not end_of_cursor then
270 g_asg_tab(1) := g_asg_tab(p_ctr);
271 g_asg_end_dt_tab(1) := g_asg_end_dt_tab(p_ctr);
272 g_wages_tab(1) := g_wages_tab(p_ctr);
273 g_tax_unit_id_tab(1) := g_tax_unit_id_tab(p_ctr);
274 p_ctr := 1;
275 end if;
276
277 return;
278
279 end do_asg_break_processing;
280
281
282 /*
283 Name : generate_people_list
284 Purpose : Creates a payroll action and a list of assignment actions
285 detailing the date of the magnetic tape report along with
286 the list of people to report on.
287 Arguments :
288 Notes : The criteria for selecting the people cannot be done
289 simply using SQL.It is done by first using a PLSQL cursor
290 which makes an educated guess about the people to include NB.
291 It will always include all the correct people even though
292 some may not be valid. The second step is to further check
293 each person found and apply further checks. If these are
294 passed then they are added to the list (create an assignment
295 action) otherwise they are discarded.
296 */
297
298 function generate_people_list
299 (
300 p_report_type varchar2,
301 p_state varchar2,
302 p_trans_legal_co_id varchar2,
303 p_business_group_id number,
304 p_period_end date,
305 p_quarter_start date,
306 p_quarter_end date
307 ) return number is
308
309 l_person_id number;
310 l_assignment_id number;
311 l_tax_unit_id number;
312 l_effective_start_date date;
313 l_effective_end_date date;
314 l_bus_group_id number;
315 l_state varchar2(30);
316 l_end_date1 date;
317 l_end_date2 date;
318 l_end_date3 date;
319 l_payroll_action_created boolean := false;
320 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
321 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
322
323
324 /* Variable holding the balance to be tested. */
325 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
326
327 cnt number;
328 l_chunk_size number;
329
330 cursor c_people is
331 select paf.person_id person_id,
332 paf.assignment_id assignment_id,
333 fnd_number.canonical_to_number(scl.segment1) tax_unit_id,
334 paf.effective_start_date effective_start_date,
335 paf.effective_end_date effective_end_date
336 from per_assignments_f paf,
337 hr_soft_coding_keyflex scl
338 where paf.business_group_id = l_bus_group_id
339 and paf.assignment_type = 'E'
340 and paf.primary_flag = 'Y'
341 and paf.payroll_id is not null
342 and paf.effective_start_date <= l_end_date3
343 and (paf.effective_end_date >= l_end_date1
344 or paf.effective_end_date >= l_end_date2
345 or paf.effective_end_date >= l_end_date3)
346 and scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
347 and exists ( select null
348 from hr_organization_information hoi2
349 where hoi2.organization_id = paf.organization_id
350 and hoi2.org_information_context = 'CLASS'
351 and hoi2.org_information1 = 'HR_ESTAB'
352 and hoi2.org_information2 = 'Y')
353 and exists(
354 select null
355 from hr_organization_information hoi
356 where hoi.organization_id = paf.organization_id
357 and hoi.org_information_context =
358 'Worksite Filing')
359 group by paf.person_id,
360 paf.assignment_id,
361 fnd_number.canonical_to_number(scl.segment1),
362 paf.effective_start_date,
363 paf.effective_end_date
364 order by 1, 2, 4 asc, 5, 3;
365
366 begin
367
368 /* Assign values to the variables used in the cursor for querying
369 purpose */
370 l_bus_group_id := p_business_group_id;
371 l_state := p_state;
372 l_end_date1 := to_date('12-'|| to_char(p_quarter_start,
373 'MM-YYYY'), 'DD-MM-YYYY');
374 l_end_date2 := to_date('12-'|| (to_char(
375 add_months(p_quarter_start,1), 'MM-YYYY')),
376 'DD-MM-YYYY');
377 l_end_date3 := to_date('12-'|| to_char(p_quarter_end,
378 'MM-YYYY'), 'DD-MM-YYYY');
379
380 /* Get the balance id for the Gross Quarterly wages of a person */
381 l_defined_balance_id := Pay_Mag_Utils.Bal_Db_Item(
382 'GROSS_EARNINGS_PER_GRE_QTD');
383
384 /* Get the remaining balance ids */
385
386 get_balance_id;
387
388 /* Get CHUNK_SIZE or default to 20 if CHUNK_SIZE does not exist */
389 begin
390 select parameter_value
391 into l_chunk_size
392 from pay_action_parameters
393 where parameter_name = 'CHUNK_SIZE';
394 exception
395 when no_data_found then
396 l_chunk_size := 20;
397 end;
398
399 /* Initialize counter. */
400
401 cnt := 0;
402
403 /* Open the cursor and get the people */
404 open c_people;
405
406 /* Loop for all rows returned for SQL statement. */
407
408 loop
409
410 /* Commit if l_chunk_size number of assignments have been processed.*/
411
412 if cnt = l_chunk_size then
413 cnt := 0;
414 commit;
415 hr_utility.trace('COMMITTED');
416 end if;
417
418 cnt := cnt + 1;
419 hr_utility.trace('CNT:::: '||cnt||'CHUNK SIZE::: '||l_chunk_size);
420
421 /* Fetch a row from the cursor. */
422
423 fetch c_people into l_person_id,
424 l_assignment_id,
425 l_tax_unit_id,
426 l_effective_start_date,
427 l_effective_end_date;
428
429 if c_people%NOTFOUND then
430 if (g_ctr = 0) then
431 /* Nothing to do so get outof the loop */
432 close c_people;
433 exit;
434 else
435 /* I have atleast one assignment to process. So, I'll make
436 a dummy increment of the g_ctr variable by 1 and then call
437 the break routine. The increment is done to ensure
438 compatibility with the break routine */
439
440 g_ctr := g_ctr + 1;
441 do_asg_break_processing(l_payroll_action_id, g_ctr, TRUE);
442 close c_people;
443 exit;
444 end if;
445 end if;
446
447 /* Check to see that the gross quarterly balance is nonzero. */
448
449 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
450
451 if pay_balance_pkg.get_value
452 (l_defined_balance_id,
453 l_assignment_id,
454 least(p_period_end,l_effective_end_date)) > 0 then
455
456 if not l_payroll_action_created then
457
458 /* Create payroll action for the magnetic tape report. */
459
460 l_payroll_action_id := Pay_Mag_Utils.Create_Payroll_Action
461 (p_report_type,
462 p_state,
463 p_trans_legal_co_id,
464 p_business_group_id,
465 p_period_end);
466
467 /* Set the flag to true to indicate that the payroll action id
468 has been created */
469
470 l_payroll_action_created := true;
471
472 end if;
473
474 /* Adding code for the caluculation of wages of the assignments */
475
476 if (l_end_date1 >= l_effective_start_date and
477 l_end_date1 <= l_effective_end_date) OR
478 (l_end_date2 >= l_effective_start_date and
479 l_end_date2 <= l_effective_end_date) OR
480 (l_end_date3 >= l_effective_start_date and
481 l_end_date3 <= l_effective_end_date) then
482
483 g_ctr := g_ctr + 1;
484 g_asg_tab(g_ctr) := l_assignment_id;
485 g_asg_end_dt_tab(g_ctr) := to_char(l_effective_end_date,
486 'DD-MM-YYYY');
487 g_wages_tab(g_ctr) := get_wages(l_assignment_id, l_tax_unit_id,
488 least(p_period_end,l_effective_end_date));
489 g_tax_unit_id_tab(g_ctr) := l_tax_unit_id;
490
491 end if;
492
493 /* If there is a break in the assignment number then call the
494 break processing routine to create the assignment action id
495 for the applicable assignments */
496
497 if (g_ctr > 1 and g_asg_tab(g_ctr) <> g_asg_tab(g_ctr - 1)) then
498
499 do_asg_break_processing(l_payroll_action_id, g_ctr, FALSE);
500
501 end if;
502 end if;
503 end loop;
504 commit;
505 if c_people%ISOPEN then
506 close c_people;
507 end if;
508
509 /* A payroll action has been created. So, update the status to created */
510
511 if l_payroll_action_created then
512
513 /* Update the population status of the payroll action to indicate that all
514 the assignment actions have been created for it. */
515
516 update pay_payroll_actions ppa
517 set ppa.action_population_status = 'C'
518 where ppa.payroll_action_id = l_payroll_action_id;
519
520 commit;
521
522 end if;
523
524 return (l_payroll_action_id);
525
526 end generate_people_list;
527
528 /*
529 Name : Run_Magtape
530 Purpose : Submits the magnetic tape process to be run by the concurrent
531 manager. We also define the name of the output and the format
532 here.
533 Arguments : p_effective_date Effective Date of the report.
534 p_report_type Report Type for MWS.
535 p_payroll_action_id Payroll Action Id assigned to the report.
536 p_state The Report Qualifier.
537 p_reporting_year The year for which the report is being
538 generated.
539 p_reporting_quarter The quarter for which the report is being
540 generated.
541 p_trans_legal_co_id The transmitter tax_unit_id.
542 p_quarter_start The start of the quarter
543 p_quarter_end The end of the quarter
544 p_business_group_id The business group
545 */
546
547 procedure Run_Magtape
548 (
549 p_effective_date date,
550 p_report_type varchar2,
551 p_payroll_action_id varchar2,
552 p_state varchar2,
553 p_reporting_year varchar2,
554 p_reporting_quarter varchar2,
555 p_trans_legal_co_id varchar2,
556 p_quarter_start date,
557 p_quarter_end date,
558 p_business_group_id varchar2
559 ) is
560
561 l_format varchar2(30);
562 l_magfilename varchar2(15); /* Magnetic File Name */
563 l_repfilename varchar2(15); /* Report File Name */
564 l_request_id number; /* Request Id */
565 l_tape_creation_date varchar2(6);
566 l_month1 varchar2(10);
567 l_month2 varchar2(10);
568 l_month3 varchar2(10);
569
570 begin
571
572 /* Get the sysdate and assign it as the tape creation date */
573 select (to_char(sysdate,'YYMMDD'))
574 into l_tape_creation_date
575 from sys.dual;
576
577 /* Get the format to be used to produce the report. */
578
579 l_format := Pay_Mag_Utils.Lookup_Format(p_effective_date,
580 p_report_type,
581 p_state);
582
583 /* Assign the name of the output filename */
584
585 l_magfilename := p_state || p_report_type || '_' ||
586 substr(to_char(p_effective_date,'YY'),1,2);
587 l_repfilename := l_magfilename;
588
589 /* Form the end date of the 1st, 2nd and 3rd month for the quarter */
590
591 l_month1 := to_char(to_date('12-'|| to_char(p_quarter_start,
592 'MM-YYYY'), 'DD-MM-YYYY'),'DD-MM-YYYY');
593 l_month2 := to_char(to_date('12-'|| (to_char(
594 add_months(p_quarter_start,1), 'MM-YYYY')), 'DD-MM-YYYY'),
595 'DD-MM-YYYY');
596 l_month3 := to_char(to_date('12-'|| to_char(p_quarter_end,
597 'MM-YYYY'), 'DD-MM-YYYY'),'DD-MM-YYYY');
598
599 /* Start the generic magnetic tape process using the concurrent manager NB.
600 the process is registered with SRS. This process is run as a sub request
601 of the process running this PLSQL. This should result in the PLSQL
602 process being paused while the magnetic tape process runs.
603 */
604
605 l_request_id :=
606 fnd_request.submit_request
607 ('PAY',
608 program => 'PYUMAG',
609 description => null,
610 start_time => null,
611 sub_request => FALSE, -- TRUE
612 argument1 => 'pay_magtape_generic.new_formula',
613 argument2 => l_magfilename,
614 argument3 => l_repfilename,
615 argument4 => to_char(p_effective_date,'DD-MON-YYYY'),
616 argument5 => 'MAGTAPE_REPORT_ID=' || l_format,
617 argument6 => 'TRANSFER_PAYROLL_ACTION_ID=' || p_payroll_action_id,
618 argument7 => 'TRANSFER_STATE=' || p_state,
619 argument8 => 'TRANSFER_REPORTING_YEAR=' || p_reporting_year,
620 argument9 => 'TRANSFER_REPORTING_QUARTER=' || p_reporting_quarter,
621 argument10 => 'TRANSFER_TRANS_LEGAL_CO_ID=' || p_trans_legal_co_id,
622 argument11 => 'TRANSFER_FILE_NAME=' || l_magfilename,
623 argument12 => 'TRANSFER_CREATION_DATE=' || l_tape_creation_date,
624 argument13 => 'TRANSFER_MONTH1=' || l_month1,
625 argument14 => 'TRANSFER_MONTH2=' || l_month2,
626 argument15 => 'TRANSFER_MONTH3=' || l_month3,
627 argument16 => 'TRANSFER_BUSINESS_GROUP=' || p_business_group_id);
628
629 /* Detect if the request was really submitted. If it has not then handle
630 the error. */
631
632 if l_request_id = 0 then
633
634 g_message_text := 'Failed to submit concurrent request';
635 raise hr_utility.hr_error;
636 end if;
637
638 /* Request has been accepted so update payroll action with the
639 request details. */
640
641 update pay_payroll_actions ppa
642 set ppa.request_id = l_request_id
643 where ppa.payroll_action_id = p_payroll_action_id;
644
645 /* Issue a commit to synchronise the concurrent manager. */
646
647 commit;
648
649 end Run_Magtape;
650
651
652 /*
653 Name : get_reporting_dates
654 Purpose : This procedure will return the quarter start date and the
655 quarter end date of the report
656 Arguments : p_quarter It will be 03 for 1st quarter, 06 for
657 2nd quarter, 09 for the 3rd quarter and
658 12 for the 4th quarter. thus it is
659 essentially the last month number for a
660 given quarter.
661 p_year The year of reporting.
662 p_effective_date The effective date of the report.
663 p_quarter_start The start date of the quarter
664 p_quarter_end The end date of the quarter.
665 p_reporting_quarter Will be set to 1 for the first quarter,
666 2 for the 2nd quarter, 3 for the 3rd
667 quarter and 4 for the 4th quarter.
668 p_reporting_year Will be same as p_year
669 */
670
671 procedure get_reporting_dates
672 (
673 p_quarter varchar2,
674 p_year varchar2,
675 p_effective_date in out nocopy varchar2,
676 p_quarter_start in out nocopy date,
677 p_quarter_end in out nocopy date,
678 p_reporting_quarter in out nocopy varchar2,
679 p_reporting_year in out nocopy varchar2
680 ) is
681
682 begin
683
684 /* It is a Federal report which will be sent to the BLS quarterly. If the
685 report is being generated for the third quarter of 1997 then we'll get
686 p_quarter -> 09
687 p_year -> 1997
688 Hence, the values of the p_quarter_start, p_quarter_end,
689 p_reporting_quarter and p_reporting_year will be as follows :
690 p_quarter_start 01-07-1997
691 p_quarter_end 31-09-1997
692 p_reporting_quarter 3
693 p_reporting_year 1997
694 p_reporting_quarter 3
695 p_effective_date 31-09-1997
696 Note : The effective date of a report is essentially the last date of the
697 reporting period, for the report. If the report is being generated
698 for the 3rd quarter then the last date of the reporting period i.e.
699 the reporting quarter is 31-SEP-1997. So, this will be the effective
700 date. For a yearly report the effective date would be 31-DEC-1997 for
701 the report of 1997.
702 */
703
704 p_quarter_end := last_day(to_date(p_quarter || p_year,'MMYYYY'));
705 p_quarter_start := add_months(p_quarter_end, -3) + 1;
706 p_reporting_year := p_year;
707 p_reporting_quarter := to_char(fnd_number.canonical_to_number(p_quarter)/3);
708 p_effective_date := p_quarter_end;
709
710 end get_reporting_dates;
711
712 /*
713 Name : redo
714 Purpose : Calls the procedure Run_Magtape directly from SRS. This
715 procedure handles the error buffer and return code interface
716 with SRS.
717 We are going to derive all the parameters from the vi
718 Arguments : errbuf To store the message for SRS
719 retcode Return code to SRS
720 p_payroll_action_id The Payroll Action Id
721 Notes :
722 */
723
724 procedure redo
725 (
726 errbuf out nocopy varchar2,
727 retcode out nocopy number,
728 p_payroll_action_id in varchar2
729 ) is
730
731 l_effective_date date;
732 l_report_type varchar2(10);
733 l_state varchar2(10);
734 l_reporting_year varchar2(10);
735 l_reporting_quarter varchar2(10);
736 l_trans_legal_co_id varchar2(10);
737 l_quarter varchar2(10);
738 l_period_end varchar2(10);
739 l_quarter_start varchar2(10);
740 l_quarter_end varchar2(10);
741 l_report_quarter varchar2(10);
742 l_report_year varchar2(10);
743 l_business_group_id varchar2(15);
744 begin
745
746 /* Derive the rest of the parameters from the payroll_action_id */
747
748 select PA.effective_date,
749 ltrim(substr(PA.legislative_parameters, 11,5)),
750 ltrim(substr(PA.legislative_parameters, 17,5)),
751 to_char(PA.effective_date,'YYYY'),
752 to_char(fnd_number.canonical_to_number(to_char(PA.effective_date,'MM'))/3),
753 ltrim(substr(PA.legislative_parameters, 23,5)) ,
754 to_char(business_group_id)
755 into l_effective_date,
756 l_report_type,
757 l_state,
758 l_reporting_year,
759 l_reporting_quarter,
760 l_trans_legal_co_id,
761 l_business_group_id
762 from pay_payroll_actions PA
763 where PA.payroll_action_id = p_payroll_action_id;
764
765
766 update pay_payroll_actions pa
767 set PA.action_status = 'M'
768 where PA.payroll_action_id = p_payroll_action_id;
769
770 update pay_assignment_actions AA
771 set AA.action_status = 'M'
772 where AA.payroll_action_id = p_payroll_action_id;
773
774 commit;
775
776 /* Derive the start and end dates of the period being reported on. */
777
778 l_quarter := to_char((fnd_number.canonical_to_number(l_reporting_quarter) * 3),'00');
779 get_reporting_dates( l_quarter,
780 l_reporting_year,
781 l_period_end,
782 l_quarter_start,
783 l_quarter_end,
784 l_report_quarter,
785 l_report_year);
786
787 /* Start the generic magnetic tape process. */
788
789 Run_Magtape(l_effective_date,
790 l_report_type,
791 p_payroll_action_id,
792 l_state,
793 l_reporting_year,
794 l_reporting_quarter,
795 l_trans_legal_co_id,
796 l_quarter_start,
797 l_quarter_end,
798 l_business_group_id);
799
800 update pay_assignment_actions AA
801 set AA.action_status = 'C'
802 where AA.payroll_action_id = p_payroll_action_id;
803
804 commit;
805
806 /* Set up success return code. */
807
808 retcode := 0;
809
810 /* Traps all exceptions raised within the procedure, extracts the message
811 text associated with the exception and sets this up for SRS to read. */
812
813 exception
814 when hr_utility.hr_error then
815
816 /* If a payroll action exists then error it. */
817 if p_payroll_action_id is not null then
818 Pay_Mag_Utils.Error_Payroll_Action(p_payroll_action_id);
819 end if;
820
821 /* Set up error message and error return code. */
822 if g_message_text is not null
823 then
824 errbuf := g_message_text;
825 else
826 errbuf := hr_utility.get_message;
827 end if;
828 retcode := 2;
829
830 when others then
831 /* If a payroll action exists then error it. */
832 if p_payroll_action_id is not null then
833 Pay_Mag_Utils.Error_Payroll_Action(p_payroll_action_id);
834 end if;
835
836 /* Set up error message and error return code. */
837 errbuf := sqlerrm;
838 retcode := 2;
839
840 end redo;
841
842
843 /*
844 Name : run
845 Purpose : This is the main procedure responsible for generating the
846 list of assignment actions and then submitting the request to
847 produce the magnetic tape report.
848 Arguments : errbuf Error message string passed back to SRS.
849 : retcode Error code passed back to SRS ie.
850 0 - Success
851 1 - Warning
852 2 - Error
853 p_business_group_id Business group the user is running
854 under when the report is generated.
855 p_report_type 'MWSMR'
856 p_state 'FED'
857 p_quarter Identifies the quarter being reported
858 eg. 03 is the 1st quarter.
859 p_year Identifies the year being reported on.
860 p_trans_legal_co_id Identifies the Transmitter Tax Unit.
861
862 Notes : This procedure is invoked from the SRS screens.
863 */
864
865 procedure run
866 (
867 errbuf out nocopy varchar2,
868 retcode out nocopy number,
869 p_business_group_id in number,
870 p_report_type in varchar2,
871 p_quarter in varchar2,
872 p_year in varchar2,
873 p_trans_legal_co_id in number
874 ) is
875 --
876
877 c_period_end date;
878 c_quarter_start date;
879 c_quarter_end date;
880 c_reporting_year varchar2(4);
881 c_reporting_quarter varchar2(4);
882 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
883 l_trans_legal_co_id number;
884 l_request_id number;
885 l_format varchar2(30);
886 l_report_type varchar2(5);
887 l_state varchar2(5);
888 l_context varchar2(240);
889 l_legislative_code varchar2(4);
890 l_err_code number(5);
891 l_err_text varchar2(240);
892
893 begin
894
895 g_ctr := 0;
896 /* Assign the Report Type and the Report Qualifier */
897 l_report_type := p_report_type;
898 l_state := 'FED';
899
900 /* Check if the environment for the report has been properly set */
901
902 /* First check for the context of Multiple Worksite Reporting */
903
904 l_context := 'Multiple Worksite Reporting';
905 l_legislative_code := 'US';
906 l_err_code := 0;
907 l_err_text := null;
908 pay_us_validate_info.validate(p_business_group_id, l_context,
909 l_legislative_code, l_err_code, l_err_text);
910 if l_err_code <> 0
911 then
912 /* Set up error message and error return code. */
913 errbuf := l_err_text;
914 retcode := l_err_code;
915 g_message_text := l_err_text;
916 hr_utility.raise_error;
917 end if;
918
919 /* Now its time to check for the context of Worksite Filing */
920
921 l_context := 'Worksite Filing';
922 l_legislative_code := 'US';
923 l_err_code := 0;
924 l_err_text := null;
925 pay_us_validate_info .validate(p_business_group_id, l_context,
926 l_legislative_code, l_err_code, l_err_text);
927 if l_err_code <> 0
928 then
929 /* Set up error message and error return code. */
930 errbuf := l_err_text;
931 retcode := l_err_code;
932 g_message_text := l_err_text;
933 hr_utility.raise_error;
934 end if;
935
936 /* Derive the start and end dates of the period being reported on. */
937
938 get_reporting_dates( p_quarter,
939 p_year,
940 c_period_end,
941 c_quarter_start,
942 c_quarter_end,
943 c_reporting_quarter,
944 c_reporting_year);
945
946 /* Check for the uniqueness of the report */
947
948 Pay_Mag_Utils.Check_Report_Unique(p_business_group_id,
949 c_period_end,
950 l_report_type,
951 l_state);
952
953 /* Get the format to be used to produce the report. */
954
955 l_format := Pay_Mag_Utils.Lookup_Format(c_period_end,
956 l_report_type,
957 l_state);
958
959 /* See if a transmitter legal company was specified NB. it is not
960 possible to pass NULL parameters to the process so a value has to be
961 set ie. '-1'. */
962
963 l_trans_legal_co_id := nvl(p_trans_legal_co_id, -1);
964
965 /* Generate payroll action and assignment actions for all the people to be
966 reported on NB. The list of people is dependent on the report being
967 run. If there are no people to report on then there is no need to
968 submit the process to produce the report. The variable
969 l_payroll_action_id holds the ID of the created payroll action. */
970
971 l_payroll_action_id := generate_people_list(l_report_type,
972 l_state,
973 l_trans_legal_co_id,
974 p_business_group_id,
975 c_period_end,
976 c_quarter_start,
977 c_quarter_end);
978
979 /* A payroll action has been created which means that at least one
980 assignment action has been created so the magnetic tape report has to
981 be run. Since we are not going to do any archiving for MWS, the call to
982 the archiver has been removed */
983
984 if l_payroll_action_id is not null then
985
986 /* Start the generic magnetic tape process. */
987
988 Run_Magtape(c_period_end,
989 l_report_type,
990 l_payroll_action_id,
991 l_state,
992 c_reporting_year,
993 c_reporting_quarter,
994 l_trans_legal_co_id,
995 c_quarter_start,
996 c_quarter_end,
997 p_business_group_id);
998
999 else
1000
1001 /* A payroll action has not been created so there are no people to report
1002 on. Set up message explaining why report was not produced. */
1003
1004 g_message_text := 'There are no employees that match ' ||
1005 'the criteria for the report';
1006 hr_utility.raise_error;
1007
1008 end if;
1009
1010 /* Process completed successfully. Update the status of the payroll and
1011 assignments actions. */
1012
1013 Pay_Mag_Utils.Update_Action_Status(l_payroll_action_id);
1014
1015 /* Set up success return code. */
1016
1017 retcode := 0;
1018
1019 /* Trap all exceptions raised within the procedure, extract the message
1020 text associated with the exception and set this up for SRS to read. */
1021
1022 exception
1023 when hr_utility.hr_error then
1024 /* If a payroll action exists then error it. */
1025 if l_payroll_action_id is not null then
1026 Pay_Mag_Utils.Error_Payroll_Action(l_payroll_action_id);
1027 end if;
1028
1029 /* Set up error message and error return code. */
1030 if g_message_text is not null
1031 then
1032 errbuf := g_message_text;
1033 else
1034 errbuf := hr_utility.get_message;
1035 end if;
1036 retcode := 2;
1037
1038 when others then
1039
1040 /* If a payroll action exists then error it. */
1041 if l_payroll_action_id is not null then
1042 Pay_Mag_Utils.Error_Payroll_Action(l_payroll_action_id);
1043 end if;
1044
1045 /* Set up error message and error return code. */
1046 errbuf := sqlerrm;
1047 retcode := sqlcode;
1048
1049 end run;
1050
1051 end pay_mws_magtape_reporting;
[Home] [Help]