1 package body pay_us_sqwl_archive as
2 /* $Header: pyussqwl.pkb 120.12.12010000.2 2008/08/06 08:36:01 ubhat ship $ */
3
4 /*
5 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6
7 Description : Package and procedure to build sql for payroll processes.
8
9 Change List
10 -----------
11 Date Name Vers Bug No Description
12 ----------- ---------- ----- ------- --------------------------------
13 06-Jun-2008 mikarthi 115.63 6774422 Changed _cursor c_get_latest_asg
14 for improving performance
15 14-Mar-2007 saurgupt 115.62 5152728 Changed the range_cursor and action_creation to
16 improve perf. In range_cursor, removed pay_payrolls_f.
17 07-Apr-2006 sudedas 115.60 4344959 changing preprocess_check, cursor (c_chk_asg_wp)
18 01-Feb-2006 sudedas 115.59 4890376 Removing hr_organization_information
19 from action_creation cursors (including
20 LA,CT) as the checks are there for range_cursor
21 24-JAN-2006 sackumar 115.58 4869678 Modified the c_get_latest_asg cursor in
22 archive_data procedure.removed the +0 from
23 the query to enable the indexes.
24 16-AUG-2005 sudedas 115.55 Adding some trace messages for
25 procedure archive_asg_locs.
26 10-AUG-2005 sudedas 115.54 4349864 action_creation is modified to
27 enable Range Person ID functionality
28 for LA, PR and CT (non-profit)
29 24-JUN-2005 sudedas 115.53 4310812 action_creation is modified for
30 State of Maine.
31 22-JUN-2005 sudedas 115.52 4310812 range_cursor is changed to include
32 Maine like California.
33 30-MAY-2005 sudedas 115.51 3843134 action_creation is modified for performance
34 25-MAY-2005 sudedas 115.50 4310812 action_creation and report_person_on_tape
35 is modified for Maine Sqwl.
36 24-Nov-2004 saikrish 115.48 Commented the trace.
37 22-Nov-2004 saikrish 115.47 3923296 Changed get_selection_information to check
38 SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD for Indiana
39 28-OCT-2004 saikrish 115.46 3923296 Changed get_selection_information to check
40 SUI_ER_GROSS_PER_JD_GRE_QTD for Indiana
41 22-OCT-2004 jgoswami 115.45 Fix Check Patch error
42 30-SEP-2004 jgoswami 115.44 3925772 modified archive_data, modified
43 cursor c_get_latest_asg to check for
44 all assignments for person which are
45 valid and paid in quarter.
46 01-MAR-2004 jgoswami 115.43 3416806 modified action_creation cursors to check for
47 assignment_type of Employee only.
48 Clean Package, removed unnecessary code.
49 19-FEB-2004 jgoswami 115.42 3331021 modified archive_data, remove query with RULE hint
50 and added cursor c_get_latest_asg
51 21-JAN-2004 jgoswami 115.41 3388513 Changed the criteria for picking up the emps
52 in fourth quarter.
53 check for SIT_SUBJ_WHABLE_PER_JD_GRE_YTD,
54 SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD,
55 SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD.
56 18-DEC-2003 jgoswami 115.40 3324974 comment correctly to initialize
57 l_prev_tax_unit_id to -99999999.
58 04-DEC-2003 fusman 115.39 3281209 Checked the checking criteria for NY
59 in fourth quarter.
60 30-JUL-2003 fusman 115.38 2922028 Changed the criteria for picking up the emps
61 for NY on fourth QTR.
62 07-JUL-2003 sodhingr 115.37 changed the cursor c_state_pr,c_ct_non_profit,
63 c_state,c_state_la_quality for performance.
64 Added the check for
65 asg.effective_end_date >= l_period_start
66 and asg.effective_start_date <= l_period_end
67 instead of ppa.effective_date between
68 ASG.effective_start_date and ASG.effective_end_date
69 02-Jun-2003 fusman 115.36 2965887 Checked for archive type in chk_gre_archive
70 and inserted archive_type in ff_archive_items.
71 28-MAY-2003 tmehra 115.35 2981455 Made changes to the action_creation
72 Added code to error out in case
73 if the wage plan is missing at both
74 the Asg and the GRE level for CA.
75 27-MAY-2003 tmehra 115.34 Made changes to the c_chk_asg_wp
76 cursor, The Asg's with NULL SUI
77 ID does not get falgged off now.
78 22-MAY-2003 tmehra 115.33 2707698 Replaced c_dup_orgn_info
79 cursor with a new select
80 statement due to performance
81 issues.
82 19-MAY-2003 tmehra 115.32 Made changes to the archiver
83 Pre-Process c_chk_gre_wp cursor.
84 15-MAY-2003 tmehra 115.31 Made changes to the archiver
85 Pre-Process.
86 07-MAY-2003 tmehra 115.30 Merged Single and Multi Wage Plan
87 logic for California.
88 23-APR-2003 tclewis 115.29 2924361 added a order by paf.effective_end_date
89 to the cursor c_asg_loc_end.
90 this is to return the latest
91 location id in the cursor.
92 30-MAR-2003 sodhingr 115.28 changed the cursor csr_defined_balance
93 in the function bal_db_item to join
94 with the legislation_code = 'US'
95
96 18-MAR-2003 sodhingr 115.27 changed the cursor c_state_pr, to
97 compare effective_date between
98 l_period_start and l_period_end
99 instead of comparing between l_period_start
100 and l_period start.
101 25-FEB-2003 sodhingr 115.22 2717128 Changed the cursors c_state ,
102 c_ct_non_profit,c_state_la_quality
103 for performance.
104 2809506 changed the cursor c_asg_loc_end for
105 performance, commenting the redundant
106 join with business group id
107 12-FEB-2002 sodhingr 115.21 2779152 Changed action_creation, added the
108 cursor c_state_pr, for PR.
109 11-SEP-2002 sodhingr 115.20 2549213 Changed the foloowing cursors to user
110 per_all_assignments_f instead of per_assignments_f
111 c_ct_non_profit, c_state_la_quality, c_state
112 30-MAY-2002 asasthan 115.19 2396909 For MMREF states SQWLs now
113 give warning when there is
114 no W2 Reporting Rules set up
115 for transmitter GRE.
116 Removed following procedures
117 that were earlier used by EOY
118 process and are not reqd by
119 SQWL process. These are
120
121 PROCEDURE EOY_RANGE_CURSOR
122 PROCEDURE EOY_ACTION_CREATION
123 PROCEDURE EOY_ARCHIVE_DATA
124 PROCEDURE EOY_ARCHINIT
125
126 25-MAR-2002 asasthan 115.18 Added ORDERED hint in action
127 creation cursor
128 20-MAR-2002 djoshi 115.17 Initalized l_prev_tax_unit_id
129 to -9999999;
130 21-FEB-2002 asasthan 115.16 Fix for Bug 2123699
131 Changed l_value > 0 in action
132 creation to l_value <> 0 to
133 create assignment actions for
134 -ve SUI_ER_SUBJ_WHABLE_PER_JD_GRE
135 _QTD assignments.
136 Also made similar changes
137 in residence_in_state and
138 report_person_on_tape.
139
140 05-DEC-2001 asasthan 115.15 Changed for MA SQWLs 2138109
141 18-OCT-2001 tmehra 115.14 Replaced the following cursors
142 - c_archive_wage_plan_code_rts
143 - c_archive_wage_plan_code_rtm
144 with
145 - c_archive_wage_plan_code
146 to improve performance.
147 Also modified archive_data
148 12-OCT-2001 vmehta 115.13 Modified c_state cursor for
149 improving performance.
150 Also modified archive_data
151 05-JUN-2001 tclewis 115.4 Added procedure archive_asg_locs. This
152 will archive the Assignment locations as of
153 the 12th of the month, for each month of the
154 quarter.
155
156 11/16/2000 asasthan 115.8 1494215 Added A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD
157 and A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD
158 to update_ff_archive_items.
159 22-AUG-2000 ashgupta 110.12 1382408 Changed the SQL statement of
160 c_rts_dup_wage cursor. This SQL was
161 changed due to Fidelity issue. Now
162 the cursor does not check for Multi
163 ple wage plan codes across the
164 assignments of a person. It just
165 checks that each individual assgn-
166 ment should not be having more than
167 one wage plan code. It takes care
168 of only paid assignments.
169 19-JUL-2000 ashgupta 40.14 1354144 Changed the SQL statement of
170 c_rts_dup_wage cursor. This SQL was
171 changed due to Fidelity performance
172 problem.
173 12-JUN-2000 asasthan 115.5 update till Q2 2000 changes and includes
174 the 11i fnd_date and fnd_number changes
175 22-MAY-2000 ashgupta 40.12 1237099 Added the error messages in the
176 preprocess_check function
177 02-MAR-2000 rpotnuru 40.11 1220213 Terminated Employees not showing for $th Qtr
178 NY sqwl. Range cursor date range will now the whole
179 Year for NY 4th Qtr SQWL.
180 08-FEB-2000 ashgupta 40.9 SQWL changes for city of Oakland
181 Added code in archinit
182 archive_data
183 range_cursor
184 Added a new fn preprocess_check
185 This was done for the enhancement
186 req 1063413
187 03_DEC-1999 asasthan 40.6 1093595
188 03-DEC-1999 rpotnuru 40.5 1095096 NY sqwl for 4th qtr date range is Year St to
189 1085774 Year End. so for reporting QTD balances
190 setting a flag in pay_assignment_actions
191 if the employee doesnt have balances for the QTD.
192 Added function update_ff_archive_item.
193
194 17-NOV-1999 asasthan Performance Tuning 1079787
195 27-OCT-1999 RPOTNURU 110.0 Bug fix 976472
196
197 25-oct-1999 djoshi added the A_SS_EE_WAGE_BASE and
198 A_SS_EE_WAGE rate to archive the data
199 related to bug 983094 and 101435
200
201 01-sep-1999 achauhan While archiving the employer data
202 add the context of pay_payroll_actions
203 to ff_archive_item_contexts.
204 11-aug-1999 achauhan Added the call to
205 eoy_archive_gre_data in the
206 eoy_range_cursor procedure. This is
207 being done to handle the situation
208 of archiving employer level data
209 even when there are no employees in
210 a GRE.
211 10-aug-1999 achauhan In the archive_data routine,
212 removed the use of payroll_action_id
213 >= 0.
214 04-Aug-1999 VMehta Changed eoy_archive_data to improve performance.
215 02-Jun-1999 meshah added new cursors in the range and action
216 creation cursors to check for non profit
217 gre's for the state of connecticut.
218
219 08-mar-1999 VMehta Added nvl while checking for l_1099R_ind
220 to correct the Louisiana quality jobs program
221 tape processing.
222 26-jan-1999 VMehta Modified function report_person_on_tape to
223 return false for all states except California
224 and Massachusetts.
225 24-Jan-1999 VMehta 805012 Added function report_person_on_tape to perform
226 check for retirees having SIT w/h in california.
227 06-Jan-1999 MReid Changed c_eoy_gre cursor to disable
228 business_group_id index on ppa side
229 30-dec-1998 vmehta 709641 Look at SUI_ER_SUBJ_WHABLE instead of SUI_ER_GROSS
230 for picking up people for SQWL . This makes sure
231 that only people with SUI wages are picked up.
232 27-dec-1998 vmehta Corrected the cursor in action creation to get the
233 tax_unit_name from pay_assignment_actions.
234 21-DEC-1998 achauhan Changed the cursor in action creation to get the
235 assignments from the pay_assignment_actions table.
236
237 08-DEC-1998 vmehta Removed grouping by on assignment_id while creating
238 assignment_ids
239 08-DEC-1998 nbristow Updated the c_state cursor to use
240 an exists rather than a join.
241 07-DEC-1998 nbristow Resolved some issues introduced by
242 40.13.
243 04-DEC-1998 vmehta 750802 Changed the cursors/logic to
244 pick up people who live in
245 California for the California SQWL.
246 29-NOV-1998 nbristow Changes to the SQWL code,
247 now using pay_us_asg_reporting.
248 25-Sep-1998 vmehta Changed the range cursor and
249 the assignment_action creation
250 cursors to support Louisiana
251 Quality Jobs Program Reporting.
252 08-aug-1998 achauhan Added the routines for eoy -
253 Year End Pre-Process
254 18-MAY-1998 NBRISTOW sqwl_range cursor now checks
255 the tax_unit_id etc.
256 06-MAY-1998 NBRISTOW
257
258 14-MAR-2005 sackumar 115.49 4222032 Change in the Range Cursor removing redundant
259 use of bind Variable (:payroll_action_id)
260 */
261
262
263 function chk_gre_archive (p_payroll_action_id number) return boolean;
264 procedure create_archive (p_user_entity_id in number,
265 p_context1 in number,
266 p_value in varchar2,
267 p_sequence in pay_us_sqwl_archive.number_data_type_table,
268 p_context in pay_us_sqwl_archive.char240_data_type_table,
269 p_context_id in pay_us_sqwl_archive.number_data_type_table);
270
271 sqwl_range varchar2(4000);
272
273
274
275 /* Name : bal_db_item
276 Purpose : Given the name of a balance DB item as would be seen in a fast formula
277 it returns the defined_balance_id of the balance it represents.
278 Arguments :
279 Notes : A defined balance_id is required by the PLSQL balance function.
280 */
281
282 function bal_db_item
283 (
284 p_db_item_name varchar2
285 ) return number is
286
287 /* Get the defined_balance_id for the specified balance DB item. */
288
289 cursor csr_defined_balance is
290 select fnd_number.canonical_to_number(UE.creator_id)
291 from ff_user_entities UE,
292 ff_database_items DI
293 where DI.user_name = p_db_item_name
294 and UE.user_entity_id = DI.user_entity_id
295 and Ue.creator_type = 'B'
296 and UE.legislation_code = 'US';
297
298 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
299
300 begin
301
302 open csr_defined_balance;
303 fetch csr_defined_balance into l_defined_balance_id;
304 if csr_defined_balance%notfound then
305 close csr_defined_balance;
306 raise hr_utility.hr_error;
307 else
308 close csr_defined_balance;
309 end if;
310
311 return (l_defined_balance_id);
312
313 end bal_db_item;
314
315
316 /* Name : get_dates
317 Purpose : The dates are dependent on the report being run i.e.
318 a W2 report shows information for a tax year while
319 a SQWL report shows information for a quarter within
320 a tax year.
321 Arguments :
322 Notes :
323 */
324
325 procedure get_dates
326 (
327 p_report_type in varchar2,
328 p_effective_date in date,
329 p_period_end in out nocopy date,
330 p_quarter_start in out nocopy date,
331 p_quarter_end in out nocopy date,
332 p_year_start in out nocopy date,
333 p_year_end in out nocopy date
334 ) is
335 begin
336
337
338
339 /* Report is SQWL ie. a quarterly report where the identifier indicates the
340 quarter eg. 0395
341 p_period_end 31-MAR-1995
342 p_quarter_start 01-JAN-1995
343 p_quarter_end 31-MAR-1995
344 p_year_start 01-JAN-1995
345 p_year_end 31-DEC-1995
346 */
347
348 p_quarter_start := trunc(p_effective_date, 'Q');
349 p_quarter_end := add_months(trunc(p_effective_date, 'Q'),3) - 1;
350 p_period_end := p_quarter_end;
351
352 p_year_start := trunc(p_effective_date, 'Y');
353 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
354
355 end get_dates;
356
357
358 /* Name : get_selection_information
359 Purpose : Returns information used in the selection of people to be reported on.
360 Arguments :
361
362 The following values are returned :-
363
364 p_period_start - The start of the period over which to select
365 the people.
366 p_period_end - The end of the period over which to select
367 the people.
368 p_defined_balance_id - The balance which must be non zero for each
369 person to be included in the report.
370 p_group_by_gre - should the people be grouped by GRE.
371 p_group_by_medicare - Should the people ,be grouped by medicare
372 within GRE NB. this is not currently supported.
373 p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
374 the testing of the balance.
375 p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
376 for the testing of the balance.
377
378 Notes : This routine provides a way of coding explicit rules for
379 individual reports where they are different from the
380 standard selection criteria for the report type ie. in
381 NY state the selection of people in the 4th quarter is
382 different from the first 3.
383 */
384
385 procedure get_selection_information
386 (
387
388 /* Identifies the type of report, the authority for which it is being run,
389 and the period being reported. */
390 p_report_type varchar2,
391 p_state varchar2,
392 p_quarter_start date,
393 p_quarter_end date,
394 p_year_start date,
395 p_year_end date,
396 /* Information returned is used to control the selection of people to
397 report on. */
398 p_period_start in out nocopy date,
399 p_period_end in out nocopy date,
400 p_defined_balance_id in out nocopy number,
401 p_group_by_gre in out nocopy boolean,
402 p_group_by_medicare in out nocopy boolean,
403 p_tax_unit_context in out nocopy boolean,
404 p_jurisdiction_context in out nocopy boolean
405 ) is
406
407 begin
408
409 /* Depending on the report being processed, derive all the information
410 required to be able to select the people to report on. */
411
412
413 /* State Quarterly Wage Listings. */
414
415 if p_report_type = 'SQWL' then
416
417 /* New York state settings NB. the difference is that the criteria for
418 selecting people in the 4th quarter is different to that used for the
419 first 3 quarters of the tax year. */
420
421 if p_state = 'NY' then
422
423 if instr(to_char(p_quarter_end,'MM'), '12') = 0 then
424
425 /* Period is one of the first 3 quarters of tax year. */
426
427 p_period_start := p_quarter_start;
428 p_period_end := p_quarter_end;
429 p_defined_balance_id := bal_db_item('SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD');
430
431 else
432
433 /* Period is the last quarter of the year.*/
434
435 p_period_start := p_year_start;
436 p_period_end := p_year_end;
437 --p_defined_balance_id := bal_db_item('REGULAR_EARNINGS_PER_GRE_YTD'); /*Bug:2922028*/
438 p_defined_balance_id := bal_db_item('SIT_SUBJ_WHABLE_PER_JD_GRE_YTD'); /*Bug:3388513*/
439
440 end if;
441
442 /* Values are set independent of quarter being reported on. */
443
444 p_group_by_gre := TRUE;
445 p_group_by_medicare := TRUE;
446 p_tax_unit_context := TRUE;
447 p_jurisdiction_context := TRUE;
448
449 else
450
451 /* Default settings for State Quarterly Wage Listing. */
452 hr_utility.set_location ('State',1);
453 p_period_start := p_quarter_start;
454 p_period_end := p_quarter_end;
455 p_defined_balance_id := bal_db_item('SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD');
456 p_group_by_gre := TRUE;
457 p_group_by_medicare := TRUE;
458 p_tax_unit_context := TRUE;
459 p_jurisdiction_context := TRUE;
460
461 hr_utility.set_location ('p_period_start -> '|| p_period_start,1);
462 hr_utility.set_location ('p_period_end -> '|| p_period_end,1);
463 hr_utility.set_location ('p_defined_balance -> SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD',1);
464 hr_utility.set_location ('p_defined_balance_id -> '|| p_defined_balance_id,1);
465
466 end if;
467
468 else /* An invalid report type has been passed so fail. */
469
470 raise hr_utility.hr_error;
471
472 end if;
473
474 end get_selection_information;
475
476
477 /* Name : lookup_jurisdiction_code
478 Purpose : Given a state code ie. AL it returns the jurisdiction code that
479 represents that state.
480 Arguments :
481 Notes :
482 */
483
484 function lookup_jurisdiction_code
485 (
486 p_state varchar2
487 ) return varchar2 is
488
489 /* Get the jurisdiction_code for the specified state code. */
490
491 cursor csr_jurisdiction_code is
492 select SR.jurisdiction_code
493 from pay_state_rules SR
494 where SR.state_code = p_state;
495
496 l_jurisdiction_code pay_state_rules.jurisdiction_code%type;
497
498 begin
499
500 open csr_jurisdiction_code;
501 fetch csr_jurisdiction_code into l_jurisdiction_code;
502 if csr_jurisdiction_code%notfound then
503 close csr_jurisdiction_code;
504 raise hr_utility.hr_error;
505 else
506 close csr_jurisdiction_code;
507 end if;
508
509 return (l_jurisdiction_code);
510
511 end lookup_jurisdiction_code;
512
513
514 ---------------------------------------------------------------------------
515 -- Name
516 -- check_residence_state
517 -- Purpose
518 -- This checks that the state of residence for the given assignment id
519 -- is the same as that passed in. Used
520 -- in this package to determine if a person has lived in the state of
521 -- MA. Such people need to be reported on SQWL for MA.
522 -- Arguments
523 -- Assignment Id
524 -- Period Start Date
525 -- Period End Date
526 -- State
527 ---------------------------------------------------------------------------
528 --
529 FUNCTION check_residence_state (
530 p_assignment_id NUMBER,
531 p_period_start DATE,
532 p_period_end DATE,
533 p_state VARCHAR2,
534 p_effective_end_date DATE
535 ) RETURN BOOLEAN IS
536
537 l_resides_true VARCHAR2(1);
538 BEGIN
539
540 BEGIN
541 SELECT '1'
542 INTO l_resides_true
543 FROM dual
544 WHERE EXISTS (
545 SELECT '1'
546 FROM per_assignments_f paf,
547 per_addresses pad
548 WHERE paf.assignment_id = p_assignment_id AND
549 paf.person_id = pad.person_id AND
550 pad.date_from <= p_period_end AND
551 NVL(pad.date_to ,p_period_end) >= p_period_start AND
552 pad.region_2 = p_state AND
553 pad.primary_flag = 'Y');
554 EXCEPTION when no_data_found then
555 l_resides_true := '0';
556 END;
557
558 hr_utility.trace('l_resides_true =' || l_resides_true);
559
560 IF (l_resides_true = '1' AND
561 pay_balance_pkg.get_value(bal_db_item('GROSS_EARNINGS_PER_GRE_QTD'),
562 p_assignment_id, least(p_period_end, p_effective_end_date)) <> 0) THEN
563
564 hr_utility.trace('Returning TRUE from check_residence_state');
565
566 RETURN TRUE;
567 ELSE
568 RETURN FALSE;
569 END IF;
570 END; -- check_residence_state
571
572 ---------------------------------------------------------------------------
573 -- Name
574 -- report_person_on_tape
575 -- Purpose
576 -- This checks various state specific criteria to decide whether the given
577 -- person should be reported on the tape.
578 -- Arguments
579 -- Assignment Id
580 -- Period Start Date
581 -- Period End Date
582 -- State
583 -- Effective End Date
584 -- 1099R_ind
585 ---------------------------------------------------------------------------
586 --
587 FUNCTION report_person_on_tape (
588 p_assignment_id NUMBER,
589 p_period_start DATE,
590 p_period_end DATE,
591 p_state VARCHAR2,
592 p_effective_end_date DATE,
593 p_1099R_ind VARCHAR2
594 ) RETURN BOOLEAN IS
595 l_ret_value BOOLEAN := FALSE;
596 l_resides_in_state BOOLEAN;
597 BEGIN
598 IF (p_state = 'MA' ) THEN
599
600 l_resides_in_state := check_residence_state(p_assignment_id,
601 p_period_start, p_period_end, p_state, p_effective_end_date);
602
603 l_ret_value := l_resides_in_state;
604
605
606 END IF;
607
608
609 IF (p_state = 'CA') THEN
610
611 IF (p_1099R_ind = 'Y') THEN
612
613 l_ret_value := (pay_balance_pkg.get_value(
614 bal_db_item('SIT_WITHHELD_PER_JD_GRE_QTD') , p_assignment_id,
615 least(p_period_end, p_effective_end_date)) <> 0 );
616
617
618 ELSE
619
620 l_ret_value := (pay_balance_pkg.get_value(
621 bal_db_item('SIT_GROSS_PER_JD_GRE_QTD') , p_assignment_id,
622 least(p_period_end, p_effective_end_date)) <> 0 );
623
624 END IF;
625 END IF;
626 /* Check for ME Bug# 4310812 */
627 IF (p_state = 'ME') THEN
628 IF (p_1099R_ind = 'Y') THEN
629
630 l_ret_value := (pay_balance_pkg.get_value(
631 bal_db_item('SIT_SUBJ_WHABLE_PER_JD_GRE_QTD') , p_assignment_id,
632 least(p_period_end, p_effective_end_date)) <> 0 );
633 END IF ;
634 END IF ;
635 /* Ending Check for ME Bug# 4310812 */
636
637 return l_ret_value;
638
639
640 END; --report_person_on_tape
641
642 /* Name : get_user_entity_id
643 Purpose : This gets the user_entity_id for a specific database item name.
644 Arguments : p_dbi_name -> database item name.
645 Notes :
646 */
647
648 function get_user_entity_id (p_dbi_name in varchar2)
649 return number is
650 l_user_entity_id number;
651
652 begin
653
654 select user_entity_id
655 into l_user_entity_id
656 from ff_database_items
657 where user_name = p_dbi_name;
658
659 return l_user_entity_id;
660
661 exception
662 when others then
663 hr_utility.trace('Error while getting the user_entity_id'
664 || to_char(sqlcode));
665 raise hr_utility.hr_error;
666
667 end get_user_entity_id;
668
669 /* Name : action_creation
670 Purpose : This creates the assignment actions for a specific chunk.
671 Arguments :
672 Notes :
673 */
674
675 procedure action_creation(pactid in number,
676 stperson in number,
677 endperson in number,
678 chunk in number) is
679
680
681
682 /* Variables used to hold the select columns from the SQL statement.*/
683
684 l_person_id number;
685 l_assignment_id number;
686 l_tax_unit_id number;
687 l_effective_end_date date;
688
689 /* Variables used to hold the values used as bind variables within the
690 SQL statement. */
691
692 l_bus_group_id number;
693 l_period_start date;
694 l_period_end date;
695
696 /* Variables used to hold the details of the payroll and assignment actions
697 that are created. */
698
699 l_payroll_action_created boolean := false;
700 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
701 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
702
703
704 /* Variable holding the balance to be tested. */
705
706 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
707
708 /* Indicator variables used to control how the people are grouped. */
709
710 l_group_by_gre boolean := FALSE;
711 l_group_by_medicare boolean := FALSE;
712
713 /* Indicator variables used to control which contexts are set up for
714 balance. */
715
716 l_tax_unit_context boolean := FALSE;
717 l_jurisdiction_context boolean := FALSE;
718
719 /* Indicator variable used to check if the GRE has a default wage plan */
720
721 l_gre_wage_plan_exist BOOLEAN := FALSE;
722
723 /* Variables used to hold the current values returned within the loop for
724 checking against the new values returned from within the loop on the
725 next iteration. */
726
727 l_prev_person_id per_people_f.person_id%type;
728 l_prev_asg_id per_assignments_f.assignment_id%type;
729 l_prev_tax_unit_id hr_organization_units.organization_id%type;
730
731 /* Variable to hold the jurisdiction code used as a context for state
732 reporting. */
733
734 l_jurisdiction_code varchar2(30);
735
736 /* general process variables */
737
738 l_report_type pay_payroll_actions.report_type%type;
739 l_report_cat pay_payroll_actions.report_category%type;
740 l_state pay_payroll_actions.report_qualifier%type;
741 l_report_format pay_report_format_mappings_f.report_format%type; -- Bug# 3843134
742 l_value number;
743 l_value_sit number ; --4310812
744 l_person_on boolean ; --4349864
745 l_effective_date date;
746 l_quarter_start date;
747 l_quarter_end date;
748 l_year_start date;
749 l_year_end date;
750 l_1099R_ind varchar2(2);
751 lockingactid number;
752 ----------
753 /*This select is same as cursor c_state except the check for
754 NVL(HOI.org_information16, 'No') = 'Yes'*/
755
756 CURSOR c_state_la_quality IS
757 SELECT
758 ASG.person_id person_id,
759 ASG.assignment_id assignment_id,
760 paa.tax_unit_id tax_unit_id,
761 ppa.effective_date effective_end_date
762 FROM per_all_assignments_f ASG,
763 pay_assignment_actions paa,
764 pay_payroll_actions ppa
765 WHERE ppa.effective_date between l_period_start
766 and l_period_end
767 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
768 and paa.payroll_action_id = ppa.payroll_action_id
769 and paa.assignment_id = ASG.assignment_id
770 /*added to ignore skipped assignment */
771 and paa.action_status <> 'S'
772 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
773 /* Added for Performance, 01-JUL-2003 */
774 and asg.effective_end_date >= l_period_start
775 and asg.effective_start_date <= l_period_end
776
777 and ASG.business_group_id + 0 = l_bus_group_id
778 and ASG.person_id between stperson and endperson
779 and ASG.assignment_type = 'E'
780 and ASG.business_group_id = ppa.business_group_id -- 5152728
781 and exists (select '1'
782 from pay_us_asg_reporting puar,
783 pay_state_rules SR
784 where SR.state_code = l_state
785 and substr(SR.jurisdiction_code ,1,2) =
786 substr(puar.jurisdiction_code,1,2)
787 and ASG.assignment_id = puar.assignment_id
788 )
789 ORDER BY 1, 3, 4 DESC, 2 ;
790
791 -- Added for Bug# 4349864
792 -- Used when RANGE_PERSON_ID functionality is available
793
794 CURSOR c_state_la_quality_person_on IS
795 SELECT
796 ASG.person_id person_id,
797 ASG.assignment_id assignment_id,
798 paa.tax_unit_id tax_unit_id,
799 ppa.effective_date effective_end_date
800 FROM per_all_assignments_f ASG,
801 pay_assignment_actions paa,
802 pay_payroll_actions ppa,
803 pay_population_ranges ppr
804 WHERE ppa.effective_date between l_period_start
805 and l_period_end
806 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
807 and paa.payroll_action_id = ppa.payroll_action_id
808 and paa.assignment_id = ASG.assignment_id
809 /*added to ignore skipped assignment */
810 and paa.action_status <> 'S'
811 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
812 /* Added for Performance, 01-JUL-2003 */
813 and asg.effective_end_date >= l_period_start
814 and asg.effective_start_date <= l_period_end
815
816 and ASG.business_group_id + 0 = l_bus_group_id
817 --and ASG.person_id between stperson and endperson
818 and ppr.payroll_action_id = pactid
819 and ppr.chunk_number = chunk
820 and ppr.person_id = ASG.person_id
821 and ASG.assignment_type = 'E'
822 and ASG.business_group_id = ppa.business_group_id -- 5152728
823 and exists (select '1'
824 from pay_us_asg_reporting puar,
825 pay_state_rules SR
826 where SR.state_code = l_state
827 and substr(SR.jurisdiction_code ,1,2) =
828 substr(puar.jurisdiction_code,1,2)
829 and ASG.assignment_id = puar.assignment_id
830 )
831 ORDER BY 1, 3, 4 DESC, 2 ;
832
833 CURSOR c_state IS
834 SELECT
835 ASG.person_id person_id,
836 ASG.assignment_id assignment_id,
837 paa.tax_unit_id tax_unit_id,
838 ppa.effective_date effective_end_date
839 FROM per_all_assignments_f ASG,
840 pay_assignment_actions paa,
841 pay_payroll_actions ppa
842 WHERE ppa.effective_date between l_period_start
843 and l_period_end
844 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
845 and paa.payroll_action_id = ppa.payroll_action_id
846 and paa.assignment_id = ASG.assignment_id
847 /*added to ignore skipped assignment */
848 and paa.action_status <> 'S'
849 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
850 /* Added for Performance, 01-JUL-2003 */
851 and asg.effective_end_date >= l_period_start
852 and asg.effective_start_date <= l_period_end
853
854 and ASG.business_group_id + 0 = l_bus_group_id
855 and ASG.person_id between stperson and endperson
856 and ASG.assignment_type = 'E'
857 and ASG.business_group_id = ppa.business_group_id -- 5152728
858 and exists (select '1'
859 from pay_us_asg_reporting puar,
860 pay_state_rules SR
861 where SR.state_code = l_state
862 and substr(SR.jurisdiction_code ,1,2) =
863 substr(puar.jurisdiction_code,1,2)
864 and ASG.assignment_id = puar.assignment_id
865 )
866 ORDER BY 1, 3, 4 DESC, 2 ;
867
868 -- Added for Bug# 3843134 (Performance Issue)
869 -- Used when RANGE_PERSON_ID functionality is available
870
871 CURSOR c_state_person_on IS
872 SELECT
873 ASG.person_id person_id,
874 ASG.assignment_id assignment_id,
875 paa.tax_unit_id tax_unit_id,
876 ppa.effective_date effective_end_date
877 FROM per_all_assignments_f ASG,
878 pay_assignment_actions paa,
879 pay_payroll_actions ppa,
880 pay_population_ranges ppr
881 WHERE ppa.effective_date between l_period_start
882 and l_period_end
883 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
884 and paa.payroll_action_id = ppa.payroll_action_id
885 and paa.assignment_id = ASG.assignment_id
886 /*added to ignore skipped assignment */
887 and paa.action_status <> 'S'
888 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
889 /* Added for Performance, 01-JUL-2003 */
890 and asg.effective_end_date >= l_period_start
891 and asg.effective_start_date <= l_period_end
892
893 and ASG.business_group_id + 0 = l_bus_group_id
894 -- and ASG.person_id between stperson and endperson
895 and ppr.payroll_action_id = pactid
896 and ppr.chunk_number = chunk
897 and asg.person_id = ppr.person_id
898 and ASG.assignment_type = 'E'
899 and ASG.business_group_id = ppa.business_group_id -- 5152728
900 and exists (select '1'
901 from pay_us_asg_reporting puar,
902 pay_state_rules SR
903 where SR.state_code = l_state
904 and substr(SR.jurisdiction_code ,1,2) =
905 substr(puar.jurisdiction_code,1,2)
906 and ASG.assignment_id = puar.assignment_id
907 )
908 ORDER BY 1, 3, 4 DESC, 2 ;
909
910 /*This select in c_ct_non_profit is same as cursor c_state except the check for
911 NVL(HOI.org_information20, 'No') = 'Yes'*/
912
913 CURSOR c_ct_non_profit IS
914 SELECT
915 ASG.person_id person_id,
916 ASG.assignment_id assignment_id,
917 paa.tax_unit_id tax_unit_id,
918 ppa.effective_date effective_end_date
919 FROM per_all_assignments_f ASG,
920 pay_assignment_actions paa,
921 pay_payroll_actions ppa
922 WHERE ppa.effective_date between l_period_start
923 and l_period_end
924 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
925 and paa.payroll_action_id = ppa.payroll_action_id
926 and paa.assignment_id = ASG.assignment_id
927 /*added to ignore skipped assignment */
928 and paa.action_status <> 'S'
929 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
930 /* Added for Performance, 01-JUL-2003 */
931 and asg.effective_end_date >= l_period_start
932 and asg.effective_start_date <= l_period_end
933
934 and ASG.business_group_id + 0 = l_bus_group_id
935 and ASG.person_id between stperson and endperson
936 and ASG.assignment_type = 'E'
937 and exists (select '1'
938 from pay_us_asg_reporting puar,
939 pay_state_rules SR
940 where SR.state_code = l_state
941 and substr(SR.jurisdiction_code ,1,2) =
942 substr(puar.jurisdiction_code,1,2)
943 and ASG.assignment_id = puar.assignment_id
944 )
945 ORDER BY 1, 3, 4 DESC, 2 ;
946
947 -- Added for Bug# 4349864
948 -- Used when RANGE_PERSON_ID functionality is available
949
950 CURSOR c_ct_non_profit_person_on IS
951 SELECT
952 ASG.person_id person_id,
953 ASG.assignment_id assignment_id,
954 paa.tax_unit_id tax_unit_id,
955 ppa.effective_date effective_end_date
956 FROM per_all_assignments_f ASG,
957 pay_assignment_actions paa,
958 pay_payroll_actions ppa,
959 pay_population_ranges ppr
960 WHERE ppa.effective_date between l_period_start
961 and l_period_end
962 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
963 and paa.payroll_action_id = ppa.payroll_action_id
964 and paa.assignment_id = ASG.assignment_id
965 /*added to ignore skipped assignment */
966 and paa.action_status <> 'S'
967 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
968 /* Added for Performance, 01-JUL-2003 */
969 and asg.effective_end_date >= l_period_start
970 and asg.effective_start_date <= l_period_end
971
972 and ASG.business_group_id + 0 = l_bus_group_id
973 --and ASG.person_id between stperson and endperson
974 and ppr.payroll_action_id = pactid
975 and ppr.chunk_number = chunk
976 and ppr.person_id = ASG.person_id
977 and ASG.assignment_type = 'E'
978 and exists (select '1'
979 from pay_us_asg_reporting puar,
980 pay_state_rules SR
981 where SR.state_code = l_state
982 and substr(SR.jurisdiction_code ,1,2) =
983 substr(puar.jurisdiction_code,1,2)
984 and ASG.assignment_id = puar.assignment_id
985 )
986 ORDER BY 1, 3, 4 DESC, 2 ;
987
988 -------
989
990 CURSOR c_state_pr IS
991 SELECT ASG.person_id person_id,
992 ASG.assignment_id assignment_id,
993 paa.tax_unit_id tax_unit_id,
994 ppa.effective_date effective_end_date
995 FROM per_all_assignments_f ASG,
996 pay_assignment_actions paa,
997 pay_payroll_actions ppa,
998 hr_organization_information HOI_PR
999 WHERE ppa.effective_date between l_period_start and l_period_end
1000 AND ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1001 AND paa.payroll_action_id = ppa.payroll_action_id
1002 AND hoi_pr.organization_id = paa.tax_unit_id
1003 AND HOI_pr.org_information_context = 'W2 Reporting Rules'
1004 AND NVL(HOI_pr.org_information16, 'A') = 'P'
1005 AND paa.assignment_id = ASG.assignment_id
1006 /*added to ignore skipped assignment */
1007 and paa.action_status <> 'S'
1008 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
1009 /* Added for Performance, 01-JUL-2003 */
1010 and asg.effective_end_date >= l_period_start
1011 and asg.effective_start_date <= l_period_end
1012
1013 AND ASG.business_group_id + 0 = l_bus_group_id
1014 AND ASG.person_id between stperson and endperson
1015 and ASG.assignment_type = 'E'
1016 AND EXISTS (select 'x'
1017 from pay_us_asg_reporting puar,
1018 pay_state_rules SR
1019 where substr(SR.jurisdiction_code ,1,2) =
1020 substr(puar.jurisdiction_code,1,2)
1021 and ASG.assignment_id = puar.assignment_id
1022 and puar.tax_unit_id = hoi_pr.organization_id
1023 and SR.state_code = l_state)
1024 /* there shouldn't be any dependency on state tax rules
1025 AND EXISTS (select 'x'
1026 from hr_organization_information HOI
1027 where hoi.organization_id = hoi_pr.organization_id
1028 AND HOI.org_information_context = 'State Tax Rules'
1029 AND HOI.org_information1 = l_state
1030 AND NVL(HOI.org_information16, 'No') = 'No'
1031 AND NVL(HOI.org_information20, 'No') = 'No') */
1032 ORDER BY 1, 3, 4 DESC, 2;
1033
1034 -- Added for Bug# 4349864
1035 -- Used when RANGE_PERSON_ID functionality is available
1036
1037 CURSOR c_state_pr_person_on IS
1038 SELECT ASG.person_id person_id,
1039 ASG.assignment_id assignment_id,
1040 paa.tax_unit_id tax_unit_id,
1041 ppa.effective_date effective_end_date
1042 FROM per_all_assignments_f ASG,
1043 pay_assignment_actions paa,
1044 pay_payroll_actions ppa,
1045 hr_organization_information HOI_PR,
1046 pay_population_ranges ppr
1047 WHERE ppa.effective_date between l_period_start and l_period_end
1048 AND ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1049 AND paa.payroll_action_id = ppa.payroll_action_id
1050 AND hoi_pr.organization_id = paa.tax_unit_id
1051 AND HOI_pr.org_information_context = 'W2 Reporting Rules'
1052 AND NVL(HOI_pr.org_information16, 'A') = 'P'
1053 AND paa.assignment_id = ASG.assignment_id
1054 /*added to ignore skipped assignment */
1055 and paa.action_status <> 'S'
1056 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
1057 /* Added for Performance, 01-JUL-2003 */
1058 and asg.effective_end_date >= l_period_start
1059 and asg.effective_start_date <= l_period_end
1060
1061 AND ASG.business_group_id + 0 = l_bus_group_id
1062 --AND ASG.person_id between stperson and endperson
1063 and ppr.payroll_action_id = pactid
1064 and ppr.chunk_number = chunk
1065 and ppr.person_id = ASG.person_id
1066 and ASG.assignment_type = 'E'
1067 AND EXISTS (select 'x'
1068 from pay_us_asg_reporting puar,
1069 pay_state_rules SR
1070 where substr(SR.jurisdiction_code ,1,2) =
1071 substr(puar.jurisdiction_code,1,2)
1072 and ASG.assignment_id = puar.assignment_id
1073 and puar.tax_unit_id = hoi_pr.organization_id
1074 and SR.state_code = l_state)
1075 /* there shouldn't be any dependency on state tax rules
1076 AND EXISTS (select 'x'
1077 from hr_organization_information HOI
1078 where hoi.organization_id = hoi_pr.organization_id
1079 AND HOI.org_information_context = 'State Tax Rules'
1080 AND HOI.org_information1 = l_state
1081 AND NVL(HOI.org_information16, 'No') = 'No'
1082 AND NVL(HOI.org_information20, 'No') = 'No') */
1083 ORDER BY 1, 3, 4 DESC, 2;
1084
1085 /* California Multi Wage Plan Requirement */
1086
1087 CURSOR c_chk_gre_wp (p_tax_unit_id number) IS
1088 SELECT count(*) ct
1089 FROM hr_organization_information
1090 WHERE organization_id = p_tax_unit_id
1091 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
1092 AND org_information1 = 'CA'
1093 AND org_information4 = 'Y';
1094
1095 CURSOR c_chk_asg_wp (p_assignment_id number) IS
1096 SELECT count(*) ct
1097 FROM per_assignment_extra_info paei
1098 WHERE paei.assignment_id = p_assignment_id
1099 AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
1100 AND paei.aei_information1 = 'CA'
1101 AND paei.aei_information3 IS NOT NULL;
1102
1103
1104 --
1105 begin
1106 hr_utility.trace('Entered action creation');
1107
1108 /* added initalization for l_prev_tax_unit_id */
1109
1110 l_prev_tax_unit_id := -99999999;
1111
1112 /* Return details used to control the selection of people to report on ie.
1113 the SQL statement to run, the period over which to look for the people,
1114 how to group the people, etc... */
1115
1116 select effective_date,
1117 report_type,
1118 report_qualifier,
1119 report_category,
1120 business_group_id
1121 into l_effective_date,
1122 l_report_type,
1123 l_state,
1124 l_report_cat,
1125 l_bus_group_id
1126 from pay_payroll_actions
1127 where payroll_action_id = pactid;
1128
1129
1130 hr_utility.set_location ('actio_creation',1);
1131 --
1132 get_dates(l_report_type,
1133 l_effective_date,
1134 l_period_end,
1135 l_quarter_start,
1136 l_quarter_end,
1137 l_year_start,
1138 l_year_end);
1139
1140 hr_utility.set_location ('actio_creation',2);
1141 --
1142 get_selection_information
1143 (l_report_type,
1144 l_state,
1145 l_quarter_start,
1146 l_quarter_end,
1147 l_year_start,
1148 l_year_end,
1149 l_period_start,
1150 l_period_end,
1151 l_defined_balance_id,
1152 l_group_by_gre,
1153 l_group_by_medicare,
1154 l_tax_unit_context,
1155 l_jurisdiction_context);
1156
1157 hr_utility.set_location ('actio_creation',3);
1158
1159 --
1160 -- Get the jurisdiction code for the state if appropriate.
1161 --
1162 if l_jurisdiction_context then
1163 l_jurisdiction_code := lookup_jurisdiction_code(l_state);
1164 end if;
1165 -- Check for the Range Person ID Functionality
1166
1167 /* Initializing variable */
1168 l_person_on := FALSE ; --4349864
1169
1170 Begin
1171 select report_format
1172 into l_report_format
1173 from pay_report_format_mappings_f
1174 where report_type = l_report_type
1175 and report_qualifier = l_state
1176 and report_category = l_report_cat ;
1177 Exception
1178 When Others Then
1179 l_report_format := Null ;
1180 End ;
1181
1182 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
1183 p_report_format => l_report_format,
1184 p_report_qualifier => l_state,
1185 p_report_category => l_report_cat) ;
1186
1187 --
1188 -- Open up a cursor for processing a SQL statement.
1189 --
1190 if (l_state = 'LA' and l_report_cat = 'RTLAQ') then
1191 if l_person_on then
1192 OPEN c_state_la_quality_person_on ;
1193 else
1194 OPEN c_state_la_quality;
1195 end if ;
1196 elsif (l_state = 'CT' and l_report_cat = 'RTCTN') then
1197 if l_person_on then
1198 OPEN c_ct_non_profit_person_on ;
1199 else
1200 OPEN c_ct_non_profit;
1201 end if ;
1202 elsif (l_state = 'PR') THEN
1203 if l_person_on then
1204 OPEN c_state_pr_person_on ;
1205 else
1206 OPEN c_state_pr;
1207 end if ;
1208 else
1209 if l_person_on then
1210 OPEN c_state_person_on ;
1211 else
1212 OPEN c_state;
1213 end if ;
1214 end if;
1215
1216 --
1217 -- Loop for all rows returned for SQL statement.
1218 --
1219
1220 LOOP
1221 if (l_state = 'LA' and l_report_cat = 'RTLAQ') then
1222 hr_utility.set_location ('actio_creation',4);
1223 if l_person_on then
1224 FETCH c_state_la_quality_person_on INTO l_person_id,
1225 l_assignment_id,
1226 l_tax_unit_id,
1227 l_effective_end_date;
1228 EXIT WHEN c_state_la_quality_person_on%NOTFOUND;
1229 else
1230 FETCH c_state_la_quality INTO l_person_id,
1231 l_assignment_id,
1232 l_tax_unit_id,
1233 l_effective_end_date;
1234 EXIT WHEN c_state_la_quality%NOTFOUND;
1235 end if ;
1236
1237 elsif (l_state = 'CT' and l_report_cat = 'RTCTN') then
1238 hr_utility.set_location ('actio_creation',4);
1239 if l_person_on then
1240 FETCH c_ct_non_profit_person_on INTO l_person_id,
1241 l_assignment_id,
1242 l_tax_unit_id,
1243 l_effective_end_date;
1244 EXIT WHEN c_ct_non_profit_person_on%NOTFOUND;
1245 else
1246 FETCH c_ct_non_profit INTO l_person_id,
1247 l_assignment_id,
1248 l_tax_unit_id,
1249 l_effective_end_date;
1250 EXIT WHEN c_ct_non_profit%NOTFOUND;
1251 end if ;
1252
1253 elsif (l_state = 'PR') THEN
1254 hr_utility.set_location ('actio_creation',4);
1255 if l_person_on then
1256 FETCH c_state_pr_person_on INTO l_person_id,
1257 l_assignment_id,
1258 l_tax_unit_id,
1259 l_effective_end_date;
1260 EXIT WHEN c_state_pr_person_on%NOTFOUND;
1261 else
1262 FETCH c_state_pr INTO l_person_id,
1263 l_assignment_id,
1264 l_tax_unit_id,
1265 l_effective_end_date;
1266 EXIT WHEN c_state_pr%NOTFOUND;
1267 end if ;
1268
1269 else
1270 hr_utility.set_location ('actio_creation',5);
1271 -- If it is on then fetch from c_state_person_on cursor else c_state
1272 if l_person_on then
1273 FETCH c_state_person_on INTO l_person_id,
1274 l_assignment_id,
1275 l_tax_unit_id,
1276 l_effective_end_date;
1277 EXIT WHEN c_state_person_on%NOTFOUND;
1278 else
1279 FETCH c_state INTO l_person_id,
1280 l_assignment_id,
1281 l_tax_unit_id,
1282 l_effective_end_date;
1283 --l_1099R_ind;
1284 EXIT WHEN c_state%NOTFOUND;
1285 end If ;
1286
1287 end if;
1288
1289 --
1290 -- If the new row is the same as the previous row according to the way
1291 -- the rows are grouped then discard the row ie. grouping by GRE
1292 -- requires a single row for each person / GRE combination.
1293 --
1294 if (l_group_by_gre and
1295 l_person_id = l_prev_person_id and
1296 l_tax_unit_id = l_prev_tax_unit_id
1297 ) then
1298 --
1299 -- Do nothing.
1300 --
1301 null;
1302 --
1303 -- Have a new unique row according to the way the rows are grouped.
1304 -- The inclusion of the person is dependent on having a non zero
1305 -- balance.
1306 -- If the balance is non zero then an assignment action is created to
1307 -- indicate their inclusion in the magnetic tape report.
1308 --
1309 else
1310 hr_utility.set_location ('actio_creation',6);
1311 --
1312 -- Set up contexts required to test the balance.
1313 --
1314 -- Set up TAX_UNIT_ID context if appropriate.
1315 --
1316 if l_tax_unit_context then
1317 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1318 end if;
1319 --
1320 -- Set up JURISDICTION_CODE context if appropriate.
1321 --
1322 if l_jurisdiction_context then
1323 pay_balance_pkg.set_context('JURISDICTION_CODE',l_jurisdiction_code);
1324 end if;
1325 --
1326 --
1327 -- Check the balance.
1328 --
1329 if (l_tax_unit_id <> l_prev_tax_unit_id)
1330 then
1331 begin
1332 select 'Y'
1333 into l_1099R_ind
1334 from hr_organization_information
1335 where organization_id = l_tax_unit_id
1336 and org_information_context = '1099R Magnetic Report Rules';
1337 exception
1338 when no_data_found then
1339 l_1099R_ind := null;
1340 end;
1341 end if;
1342 if (nvl(l_1099R_ind, 'N') <> 'Y') then
1343 l_value := pay_balance_pkg.get_value
1344 (l_defined_balance_id,
1345 l_assignment_id,
1346 least(l_period_end,l_effective_end_date));
1347 --4310812
1348 If l_state = 'ME' Then
1349 l_value_sit := pay_balance_pkg.get_value(bal_db_item('SIT_SUBJ_WHABLE_PER_JD_GRE_QTD') ,
1350 l_assignment_id,
1351 least(l_period_end, l_effective_end_date)) ;
1352
1353 If nvl(l_value,0) <> 0 and nvl(l_value_sit,0) <> 0 Then
1354 l_value := greatest(l_value,l_value_sit) ;
1355 Elsif nvl(l_value,0) = 0 and nvl(l_value_sit,0) <> 0 Then
1356 l_value := l_value_sit ;
1357 End If ;
1358 End If ; -- end check for ME Non 1099R GRE
1359
1360
1361 if (l_value = 0 AND l_state = 'NY') then /*Check for NY Bug:2922028*/
1362
1363 hr_utility.trace('Entered NY Checking ');
1364
1365 if instr(to_char(l_quarter_end,'MM'), '12') <> 0 then /*Check for Last Quarter*/
1366
1367 hr_utility.trace('Last Quarter.Check the values for SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD');
1368
1369 l_value := pay_balance_pkg.get_value
1370 (bal_db_item('SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD'),
1371 l_assignment_id,
1372 least(l_period_end,l_effective_end_date));
1373
1374 hr_utility.trace('Value of SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD = '||l_value);
1375
1376 if l_value = 0 then /*Check for SUBJ_WHABLE*/
1377
1378 hr_utility.trace('Value of SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD is 0');
1379
1380 If l_effective_end_date < l_quarter_start THEN /*l_effective_end_date checking */
1381 /*Bug:3281209*/
1382
1383 hr_utility.trace('l_effective_end_date < l_quarter_start');
1384 l_value := 0;
1385
1386 ELSE
1387
1388 l_value := pay_balance_pkg.get_value
1389 (bal_db_item('SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'),
1390 l_assignment_id,
1391 least(l_period_end,l_effective_end_date));
1392
1393 hr_utility.trace('Value of SUI_ER_SUBJ_WHABLE_PER = '||l_value);
1394
1395 End if; /*l_effective_end_date checking */
1396
1397 end if; /*Check for SUBJ_WHABLE*/
1398
1399 end if; /*Check for Last Quarter*/
1400
1401 end if; /*Check for NY*/
1402
1403 else
1404 l_value := 0;
1405 end if;
1406
1407 hr_utility.trace('l_value = ' || to_char(l_value));
1408 hr_utility.trace('l_assignment_id = ' || to_char(l_assignment_id));
1409 hr_utility.trace('l_period_start = ' || l_period_start);
1410 hr_utility.trace('l_quarter_start = ' || l_quarter_start);
1411 hr_utility.trace('l_period_end = ' || l_period_end);
1412 hr_utility.trace('l_state = ' || l_state);
1413 hr_utility.trace('l_effective_end_date = ' || l_effective_end_date);
1414 hr_utility.trace('l_1099R_ind = ' || l_1099R_ind);
1415
1416 if ((l_value <> 0) OR
1417 report_person_on_tape(l_assignment_id, l_period_start,
1418 l_period_end, l_state, l_effective_end_date, l_1099R_ind)) then
1419 hr_utility.set_location ('actio_creation',7);
1420 --
1421 -- Have found a person that needs to be reported in the federal W2 so
1422 -- need to create an assignment action for it.
1423
1424
1425 -- California Multi Wage Plan requirement
1426 -- Check if the state is CA and Asg has a wage plan
1427 -- defined or it can default to the Wage Plan defined
1428 -- at the GRE level. Other wise error out.
1429
1430
1431 IF l_state = 'CA' THEN
1432
1433 -- Check if the GRE has a wage Plan defined
1434
1435 l_gre_wage_plan_exist := TRUE;
1436
1437 FOR c_rec IN c_chk_gre_wp (l_tax_unit_id)
1438 LOOP
1439
1440 IF c_rec.ct = 0 THEN
1441 l_gre_wage_plan_exist := FALSE;
1442 END IF;
1443
1444 END LOOP;
1445
1446 IF l_gre_wage_plan_exist = FALSE THEN
1447
1448 FOR c_rec IN c_chk_asg_wp (l_assignment_id)
1449 LOOP
1450 IF c_rec.ct = 0 THEN
1451 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
1452 hr_utility.set_message_token('FORMAT',' wage plan not defined at GRE');
1453 hr_utility.raise_error;
1454 END IF;
1455 END LOOP;
1456
1457 END IF;
1458
1459 END IF;
1460 --
1461 -- If the payroll action has not been created yet i.e. this is the
1462 -- first assignment action then create it.
1463 --
1464 --
1465 -- Create the assignment action to represnt the person / tax unit
1466 -- combination.
1467 --
1468 select pay_assignment_actions_s.nextval
1469 into lockingactid
1470 from dual;
1471 --
1472 -- insert into pay_assignment_actions.
1473 hr_nonrun_asact.insact(lockingactid,l_assignment_id,
1474 pactid,chunk,l_tax_unit_id);
1475 hr_utility.set_location ('actio_creation',8);
1476
1477 archive_asg_locs( lockingactid, pactid, l_assignment_id);
1478
1479
1480
1481 end if;
1482 end if;
1483 --
1484 -- Record the current values for the next time around the loop.
1485 --
1486 l_prev_person_id := l_person_id;
1487 l_prev_asg_id := l_assignment_id;
1488 l_prev_tax_unit_id := l_tax_unit_id;
1489 --
1490 END LOOP;
1491 end action_creation;
1492
1493 /* Name : archinit
1494 Purpose : This performs the US specific initialisation section.
1495 Arguments :
1496 Notes :
1497 */
1498
1499
1500 procedure archinit(p_payroll_action_id in number) is
1501 jurisdiction_code pay_state_rules.jurisdiction_code%TYPE;
1502 l_state VARCHAR2(30);
1503 l_report_cat pay_report_format_mappings_f.report_category%TYPE;
1504
1505 /* Bug 773937 */
1506 l_archive boolean:= FALSE;
1507
1508 cursor c_get_min_chunk is
1509 select min(paa.chunk_number)
1510 from pay_assignment_actions paa
1511 where paa.payroll_action_id = p_payroll_action_id;
1512
1513 /* End of Bug 773937 */
1514
1515 begin
1516 hr_utility.set_location ('archinit',1);
1517
1518 -- Derive state
1519 select report_qualifier,
1520 report_category
1521 into l_state,
1522 l_report_cat
1523 from pay_payroll_actions
1524 where payroll_action_id = p_payroll_action_id;
1525
1526 /* Added the select of report_category field in the above SQL on
1527 10-FEB-2000 by Ashu Gupta (ashgupta) */
1528
1529 hr_utility.set_location ('archinit',2);
1530
1531 -- Get jurisdiction code and store as a context
1532 IF l_state <> 'FED' THEN
1533 SELECT sr.jurisdiction_code
1534 INTO jurisdiction_code
1535 FROM pay_state_rules sr
1536 WHERE sr.state_code = l_state;
1537
1538 /* Bug 976472 */
1539 g_sqwl_state := l_state;
1540 g_sqwl_jursd := jurisdiction_code;
1541 /* End Bug 976472 */
1542 g_report_cat := l_report_cat;
1543
1544 /* Added the g_report_cat variable on 10-FEB-2000 by Ashu Gupta (ashgupta).
1545 This variable will be used in archive_data procedure to decide the report
1546 category */
1547
1548 pay_balance_pkg.set_context ('JURISDICTION_CODE',jurisdiction_code);
1549 END IF;
1550 /* Bug 773937 */
1551 hr_utility.set_location ('archinit getting min chunk number',10);
1552 open c_get_min_chunk;
1553 fetch c_get_min_chunk into g_min_chunk;
1554 if c_get_min_chunk%NOTFOUND then
1555 g_min_chunk := -1;
1556 hr_utility.set_location ('archinit min chunk is -1',11);
1557 raise hr_utility.hr_error;
1558 end if;
1559 close c_get_min_chunk;
1560
1561 /* Check if GRE level data has been archived or not and set the g_archive_flag to Y or N*/
1562 l_archive := chk_gre_archive(p_payroll_action_id);
1563
1564 /* END of Bug 773937 */
1565
1566 exception
1567 when others then
1568 raise;
1569 end archinit;
1570
1571 /* Name : eoy_get_jursd_level
1572 Purpose : This returns the jurisdiction level of the non balance
1573 database items.
1574 Arguments :
1575 Notes :
1576 */
1577
1578 function eoy_get_jursd_level(p_route_id number,
1579 p_user_entity_id number) return number is
1580 l_jursd_value number:= 0;
1581
1582 begin
1583
1584 select frpv.value
1585 into l_jursd_value
1586 from ff_route_parameter_values frpv,
1587 ff_route_parameters frp
1588 where frpv.route_parameter_id = frp.route_parameter_id
1589 and frpv.user_entity_id = p_user_entity_id
1590 and frp.route_id = p_route_id
1591 and frp.parameter_name = 'Jursd. Level';
1592
1593 return(l_jursd_value);
1594
1595 exception
1596 when no_data_found then
1597 return(0);
1598 when others then
1599 hr_utility.trace('Error while getting the jursd. value ' ||
1600 to_char(sqlcode));
1601
1602 end eoy_get_jursd_level;
1603
1604
1605
1606 procedure create_archive (p_user_entity_id in number,
1607 p_context1 in number,
1608 p_value in varchar2,
1609 p_sequence in pay_us_sqwl_archive.number_data_type_table,
1610 p_context in pay_us_sqwl_archive.char240_data_type_table,
1611 p_context_id in pay_us_sqwl_archive.number_data_type_table) is
1612 l_step number := 0;
1613
1614 begin
1615
1616 l_step := 1;
1617
1618 insert into ff_archive_items
1619 (ARCHIVE_ITEM_ID,
1620 USER_ENTITY_ID,
1621 CONTEXT1,
1622 VALUE,
1623 ARCHIVE_TYPE)
1624 values
1625 (ff_archive_items_s.nextval,
1626 p_user_entity_id,
1627 p_context1,
1628 p_value,
1629 'PPA'); /* Bug:2965887 */
1630
1631 l_step := 2;
1632
1633 for i in p_sequence.first .. p_sequence.last
1634 loop
1635 insert into ff_archive_item_contexts
1636 (ARCHIVE_ITEM_ID,
1637 SEQUENCE_NO,
1638 CONTEXT,
1639 CONTEXT_ID)
1640 values
1641 (ff_archive_items_s.currval,
1642 p_sequence(i),
1643 p_context(i),
1644 p_context_id(i));
1645 end loop;
1646
1647 exception
1648 when others then
1649 if l_step = 1 then
1650 hr_utility.trace('Error while inserting into ff_archive_items'
1651 || to_char(sqlcode));
1652 raise hr_utility.hr_error;
1653
1654 elsif l_step = 2 then
1655 hr_utility.trace('Error while inserting into ff_archive_item_contexts'
1656 || to_char(sqlcode));
1657 raise hr_utility.hr_error;
1658
1659 end if;
1660
1661 end create_archive;
1662
1663 /* Bug 773937 */
1664
1665 /* Name : archive_gre_data
1666 Purpose : This performs the US specific employer data archiving.
1667 Arguments :
1668 Notes :
1669 */
1670
1671 procedure archive_gre_data(p_payroll_action_id in number,
1672 p_tax_unit_id in number)
1673 is
1674
1675 l_user_entity_id number;
1676 l_tax_context_id number;
1677 l_jursd_context_id number;
1678 l_value varchar2(240);
1679 l_seq_tab pay_us_sqwl_archive.number_data_type_table;
1680 l_context_id_tab pay_us_sqwl_archive.number_data_type_table;
1681 l_context_val_tab pay_us_sqwl_archive.char240_data_type_table;
1682 l_arch_gre_step number := 0;
1683
1684 l_state_code pay_us_states.state_code%type;
1685
1686 l_from number;
1687 l_to number;
1688 l_length number;
1689
1690 begin
1691
1692 /* Get the context_id for 'TAX_UNIT_ID' */
1693
1694 l_arch_gre_step := 10;
1695
1696 select context_id
1697 into l_tax_context_id
1698 from ff_contexts
1699 where context_name = 'TAX_UNIT_ID';
1700
1701 /* Get the context_id for 'JURISDICTION_CODE' */
1702
1703 l_arch_gre_step := 20;
1704
1705 select context_id
1706 into l_jursd_context_id
1707 from ff_contexts
1708 where context_name = 'JURISDICTION_CODE';
1709
1710
1711 /* get the state code for the state abbrev */
1712 /* Start Position of State */
1713 select INSTR(legislative_parameters,'TRANSFER_STATE=')
1714 + LENGTH('TRANSFER_STATE=')
1715 into l_from
1716 from pay_payroll_actions
1717 where payroll_action_id = p_payroll_action_id;
1718
1719
1720 /* End position of state in legislative parameters */
1721 select INSTR(legislative_parameters,'TRANSFER_REPORTING_YEAR=')
1722 into l_to
1723 from pay_payroll_actions
1724 where payroll_action_id = p_payroll_action_id;
1725
1726 l_length := l_to - l_from - 1 ;
1727
1728 Select state_code
1729 into l_state_code
1730 from pay_us_states
1731 where state_abbrev = (
1732 select substr(legislative_parameters,l_from, l_length )
1733 from pay_payroll_actions
1734 where payroll_action_id = p_payroll_action_id);
1735
1736 /* Archive the Taxable wage Base */
1737
1738 l_user_entity_id := get_user_entity_id('A_SUI_TAXABLE_WAGE_BASE');
1739
1740 l_arch_gre_step := 21;
1741
1742 begin
1743 select to_char(sti.sui_er_wage_limit)
1744 into l_value
1745 from pay_us_state_tax_info_f sti,
1746 pay_payroll_actions ppa
1747 where ppa.payroll_action_id = p_payroll_action_id
1748 and sti.state_code = l_state_code
1749 and ppa.effective_date between sti.effective_start_date
1750 and sti.effective_end_date
1751 and sti.sta_information_category = 'State tax limit rate info';
1752
1753 exception
1754 when no_data_found then
1755 l_value := null;
1756 end;
1757
1758 /* Initialise the PL/SQL tables */
1759 l_arch_gre_step := 22;
1760
1761 l_seq_tab.delete;
1762 l_context_id_tab.delete;
1763 l_context_val_tab.delete;
1764
1765 /* Assign value to PL/SQL tables */
1766
1767 l_arch_gre_step := 23;
1768
1769 l_seq_tab(1) := 1;
1770 l_context_id_tab(1) := l_tax_context_id;
1771 l_context_val_tab(1) := to_char(p_tax_unit_id);
1772 l_seq_tab(2) := 2;
1773 l_context_id_tab(2) := l_jursd_context_id;
1774 l_context_val_tab(2) := l_state_code || '-000-0000';
1775
1776 l_arch_gre_step := 24;
1777
1778 create_archive (p_user_entity_id => l_user_entity_id,
1779 p_context1 => p_payroll_action_id,
1780 p_value => l_value,
1781 p_sequence => l_seq_tab,
1782 p_context => l_context_val_tab,
1783 p_context_id => l_context_id_tab);
1784
1785 g_archive_flag := 'Y';
1786 exception
1787 when others then
1788 g_archive_flag := 'N';
1789
1790 end archive_gre_data;
1791 /* End of Bug 773937 */
1792
1793
1794 /* Name : chk_gre_archive
1795 Purpose : Function to check if the employer level data has been archived
1796 or not.
1797 Arguments :
1798 Notes :
1799 */
1800
1801 function chk_gre_archive (p_payroll_action_id number) return boolean is
1802
1803 l_flag varchar2(1);
1804
1805 cursor c_chk_payroll_action is
1806 select 'Y'
1807 from dual
1808 where exists (select null
1809 from ff_archive_items fai
1810 where fai.context1 = p_payroll_action_id
1811 and archive_type = 'PPA'); /* Bug:2965887 */
1812 begin
1813
1814 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1815
1816 if g_archive_flag = 'Y' then
1817 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1818 return (TRUE);
1819 else
1820
1821 hr_utility.trace('chk_gre_archive - opening cursor');
1822
1823 open c_chk_payroll_action;
1824 fetch c_chk_payroll_action into l_flag;
1825 if c_chk_payroll_action%FOUND then
1826 hr_utility.trace('chk_gre_archive - found in cursor');
1827 g_archive_flag := 'Y';
1828 else
1829 hr_utility.trace('chk_gre_archive - not found in cursor');
1830 g_archive_flag := 'N';
1831 end if;
1832
1833 hr_utility.trace('chk_gre_archive - closing cursor');
1834 close c_chk_payroll_action;
1835 if g_archive_flag = 'Y' then
1836 hr_utility.trace('chk_gre_archive - returning true');
1837 return (TRUE);
1838 else
1839 hr_utility.trace('chk_gre_archive - returning false');
1840 return(FALSE);
1841 end if;
1842 end if;
1843 end chk_gre_archive;
1844
1845
1846 /* Name : archive_data
1847 Purpose : This performs the US specific employee context setting for the SQWL
1848 report.
1849 Arguments :
1850 Notes :
1851 */
1852
1853 procedure archive_data(p_assactid in number, p_effective_date in date) is
1854
1855 aaid pay_assignment_actions.assignment_action_id%type;
1856 aaseq pay_assignment_actions.action_sequence%type;
1857 asgid pay_assignment_actions.assignment_id%type;
1858 date_earned date;
1859 eff_date date;
1860 l_year_start date;
1861 l_year_end date;
1862 taxunitid pay_assignment_actions.tax_unit_id%type;
1863 l_period_start date;
1864 l_period_end date;
1865
1866 /* Bug 773937 */
1867 l_chunk number;
1868 l_payroll_action_id number;
1869 /* End of Bug 773937 */
1870
1871
1872 /* The following variables were added on 08-FEB-2000 by Ashu Gupta(ashgupta) to
1873 take care of archiving of Wage Plan Codes in California */
1874
1875 l_user_entity_id NUMBER;
1876 l_context_id_assignment_id NUMBER;
1877 l_quarter_start DATE ;
1878 l_quarter_end DATE ;
1879 l_wage_plan_code per_assignment_extra_info.aei_information3%TYPE;
1880 l_assignment_id NUMBER;
1881
1882 l_wage_plan_ct NUMBER := 0; -- Added by tmehra
1883
1884 /* Bug 976472 */
1885
1886 l_jurisdiction varchar2(11);
1887 l_count number := 0;
1888 l_context_no number := 0;
1889 l_temp_var number := 0;
1890
1891 /* Get the jurisdiction code of all the cities
1892 for the person_id corresponding to the
1893 assignment_id */
1894
1895 cursor c_get_city is
1896 select distinct pcty.jurisdiction_code pcty
1897 from pay_us_emp_city_tax_rules_f pcty,
1898 per_assignments_f paf1,
1899 per_assignments_f paf
1900 where paf.assignment_id = asgid
1901 and paf.effective_end_date >= l_year_start
1902 and paf.effective_start_date <= l_year_end
1903 and paf1.person_id = paf.person_id
1904 and paf1.effective_end_date >= l_year_start
1905 and paf1.effective_start_date <= l_year_end
1906 and pcty.assignment_id = paf1.assignment_id
1907 and pcty.effective_start_date <= l_year_end
1908 and pcty.effective_end_date >= l_year_start
1909 and pcty.jurisdiction_code in ('33-005-2010',
1910 '33-047-2010',
1911 '33-061-2010',
1912 '33-081-2010',
1913 '33-085-2010',
1914 '33-119-3230');
1915 /* End Bug 976472 */
1916
1917 /* Added by Ashu on 07-FEB-2000 to archive the Wage Plan Codes.
1918 A_SCL_US_ASG_CA_WAGE_PLAN_CODE is no longer present in
1919 ICESA_SUPPLEMENTAL formula. Therefore archiver will not archive this
1920 database item. The following cursor is executed when the category is
1921 RTM in case of California. Enhancement Req 1063413 */
1922
1923
1924 -- CURSOR c_archive_wage_plan_code_rtm IS
1925 -- SELECT DISTINCT aei_information3 ,
1926 -- paf1.assignment_id
1927 -- FROM per_assignment_extra_info paei,
1928 -- pay_us_asg_reporting puar,
1929 -- pay_us_states pus ,
1930 -- per_assignments_f paf1,
1931 -- per_assignments_f paf
1932 -- WHERE paf.assignment_id = asgid
1933 -- AND date_earned BETWEEN paf.effective_start_date
1934 -- AND paf.effective_end_date
1935 -- AND paf1.person_id = paf.person_id
1936 -- AND paf1.effective_start_date <= l_quarter_end
1937 -- AND paf1.effective_end_date >= l_quarter_start
1938 -- AND pus.state_abbrev = g_sqwl_state
1939 -- AND puar.assignment_id = paf1.assignment_id
1940 -- AND puar.tax_unit_id = taxunitid
1941 -- AND substr(puar.jurisdiction_code,1,2) = pus.state_code
1942 -- AND paf1.assignment_id = paei.assignment_id
1943 -- AND paei.aei_information1 = g_sqwl_state
1944 -- AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
1945 -- AND EXISTS(
1946 -- SELECT NULL
1947 -- FROM pay_payroll_actions ppa1,
1948 -- pay_assignment_actions paa1,
1949 -- pay_us_asg_reporting puar1
1950 -- WHERE paa1.assignment_id = paf1.assignment_id
1951 -- AND ppa1.payroll_action_id = paa1.payroll_action_id
1952 -- AND puar1.assignment_id = paf1.assignment_id
1953 -- AND puar1.tax_unit_id = puar.tax_unit_id
1954 -- AND ppa1.action_type in ('R', 'Q', 'V', 'B', 'I')
1955 -- AND ppa1.effective_date BETWEEN l_quarter_start
1956 -- AND l_quarter_end
1957 -- AND ppa1.effective_date BETWEEN
1958 -- paf1.effective_start_date
1959 -- AND paf1.effective_end_date
1960 -- );
1961 --
1962 --
1963 --
1964 -- /* Added by Ashu on 10-FEB-2000 to archive the Wage Plan Codes.
1965 -- A_SCL_US_ASG_CA_WAGE_PLAN_CODE is no longer present in
1966 -- ICESA_SUPPLEMENTAL formula. Therefore archiver will not archive this
1967 -- database item. The following cursor is executed when the category is
1968 -- RTS in case of California. The need to have external join is to make
1969 -- sure that the people with No Wage Plan Code have record in
1970 -- ff_archive_tems table. This way these persons will be selected in
1971 -- sqwl_employee_s cursor . Enhancement Req 1063413 */
1972 --
1973 --
1974 -- CURSOR c_archive_wage_plan_code_rts IS
1975 -- SELECT DISTINCT aei_information3 ,
1976 -- paf1.assignment_id
1977 -- FROM per_assignment_extra_info paei,
1978 -- pay_us_asg_reporting puar,
1979 -- pay_us_states pus ,
1980 -- per_assignments_f paf1,
1981 -- per_assignments_f paf
1982 -- WHERE paf.assignment_id = asgid
1983 -- AND date_earned BETWEEN paf.effective_start_date
1984 -- AND paf.effective_end_date
1985 -- AND paf1.person_id = paf.person_id
1986 -- AND paf1.effective_start_date <= l_quarter_end
1987 -- AND paf1.effective_end_date >= l_quarter_start
1988 -- AND pus.state_abbrev = g_sqwl_state
1989 -- AND puar.assignment_id = paf1.assignment_id
1990 -- AND puar.tax_unit_id = taxunitid
1991 -- AND substr(puar.jurisdiction_code,1,2) = pus.state_code
1992 -- AND paf1.assignment_id = paei.assignment_id(+)
1993 -- AND paei.aei_information1(+) = g_sqwl_state
1994 -- AND paei.information_type(+) = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
1995 -- AND EXISTS(
1996 -- SELECT NULL
1997 -- FROM pay_payroll_actions ppa1,
1998 -- pay_assignment_actions paa1,
1999 -- pay_us_asg_reporting puar1
2000 -- WHERE paa1.assignment_id = paf1.assignment_id
2001 -- AND ppa1.payroll_action_id = paa1.payroll_action_id
2002 -- AND puar1.assignment_id = paf1.assignment_id
2003 -- AND puar1.tax_unit_id = puar.tax_unit_id
2004 -- AND ppa1.action_type in ('R', 'Q', 'V', 'B', 'I')
2005 -- AND ppa1.effective_date BETWEEN l_quarter_start
2006 -- AND l_quarter_end
2007 -- AND ppa1.effective_date BETWEEN
2008 -- paf1.effective_start_date
2009 -- AND paf1.effective_end_date
2010 -- );
2011
2012
2013
2014 /*
2015 Due to the performance issues raised by Internal/In-House the above two
2016 cursors have been replaced with the following by tmehra 18-OCT-2001
2017 */
2018
2019 CURSOR c_archive_wage_plan_code IS
2020 SELECT DISTINCT aei_information3
2021 FROM per_assignment_extra_info paei
2022 WHERE paei.assignment_id = asgid
2023 AND paei.aei_information1 = g_sqwl_state
2024 AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD';
2025
2026 -- The following cursor was added by tmehra on 07-MAY-2003
2027 -- This cursor get the default Wage Plan defined at the GRE level
2028 -- if the Asg level Wage Plan is missing.
2029
2030
2031 CURSOR c_gre_wage_plan_code IS
2032 SELECT hoi.org_information3 wage_plan
2033 FROM hr_organization_information hoi
2034 WHERE hoi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
2035 AND hoi.organization_id = taxunitid
2036 AND hoi.org_information1 = g_sqwl_state
2037 AND hoi.org_information4 = 'Y';
2038
2039 /* Get the latest assignment for the given assisignment_id ,person_id */
2040 /* Commented out and modified query for improving performance (bug 6774422)
2041 CURSOR c_get_latest_asg(p_assignment_id number ) IS
2042 select paa.assignment_action_id,
2043 ppa.effective_date
2044 from pay_assignment_actions paa,
2045 per_all_assignments_f paf,
2046 pay_payroll_actions ppa,
2047 pay_action_classifications pac,
2048 per_all_assignments_f paf1
2049 where paf1.assignment_id = p_assignment_id
2050 and paf.person_id = paf1.person_id
2051 and paa.assignment_id = paf.assignment_id
2052 and paa.tax_unit_id = taxunitid
2053 and paa.payroll_action_id = ppa.payroll_action_id
2054 and ppa.action_type = pac.action_type
2055 and pac.classification_name = 'SEQUENCED'
2056 and ppa.effective_date between paf.effective_start_date
2057 and paf.effective_end_date
2058 and ppa.effective_date between l_period_start and
2059 l_period_end
2060 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
2061 and paa.source_action_id is null)
2062 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
2063 and paa.source_action_id is not null )
2064 or (ppa.action_type = 'V' and ppa.run_type_id is null
2065 and paa.run_type_id is not null
2066 and paa.source_action_id is null))
2067 order by paa.action_sequence desc;
2068
2069 */
2070
2071 /* This is the modified new cursor (bug 6774422)**/
2072 CURSOR c_get_latest_asg(p_assignment_id number ) IS
2073 SELECT /*+ORDERED*/
2074 PAA.ASSIGNMENT_ACTION_ID,
2075 PPA.EFFECTIVE_DATE
2076 FROM PER_ALL_ASSIGNMENTS_F PAF1,
2077 PER_ALL_ASSIGNMENTS_F PAF ,
2078 PAY_ASSIGNMENT_ACTIONS PAA,
2079 PAY_PAYROLL_ACTIONS PPA ,
2080 PAY_ACTION_CLASSIFICATIONS PAC
2081 WHERE PAF1.ASSIGNMENT_ID = p_assignment_id
2082 AND PAF.PERSON_ID = PAF1.PERSON_ID
2083 AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
2084 AND PAA.TAX_UNIT_ID = taxunitid
2085 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
2086 AND PPA.ACTION_TYPE = PAC.ACTION_TYPE
2087 AND PAC.CLASSIFICATION_NAME = 'SEQUENCED'
2088 AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
2089 AND PPA.EFFECTIVE_DATE BETWEEN l_period_start AND l_period_end
2090 AND ((NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NULL
2091 AND PAA.SOURCE_ACTION_ID IS NULL)
2092 OR (NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NOT NULL
2093 AND PAA.SOURCE_ACTION_ID IS NOT NULL )
2094 OR (PPA.ACTION_TYPE = 'V'
2095 AND PPA.RUN_TYPE_ID IS NULL
2096 AND PAA.RUN_TYPE_ID IS NOT NULL
2097 AND PAA.SOURCE_ACTION_ID IS NULL))
2098 ORDER BY PAA.ACTION_SEQUENCE DESC;
2099
2100
2101 begin
2102 hr_utility.set_location ('archive_data',1);
2103
2104 SELECT aa.assignment_id,
2105 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2106 aa.tax_unit_id,
2107 aa.chunk_number, /* Bug 773937 */
2108 aa.payroll_action_id /* Bug 773937 */
2109 into asgid,
2110 date_earned,
2111 taxunitid,
2112 l_chunk, /* Bug 773937 */
2113 l_payroll_action_id /* Bug 773937 */
2114 FROM pay_assignment_actions aa
2115 WHERE aa.assignment_action_id = p_assactid;
2116
2117 /*
2118 The following code was added on 08-FEB-2000 by Ashu Gupta (ashgupta) to
2119 take care of archiving of Wage Plan Codes in California
2120 */
2121
2122 IF (g_sqwl_state = 'CA') THEN
2123
2124 l_user_entity_id :=get_user_entity_id('A_SCL_ASG_US_CA_WAGE_PLAN_CODE');
2125 l_quarter_start := TRUNC(p_effective_date, 'Q');
2126 l_quarter_end := ADD_MONTHS(TRUNC(p_effective_date, 'Q'),3) - 1;
2127
2128 SELECT context_id
2129 INTO l_context_id_assignment_id
2130 FROM ff_contexts
2131 WHERE context_name = 'ASSIGNMENT_ID';
2132
2133 /* l_user_entity_id, l_context_id_date_earned, l_context_id_assignment_id,
2134 can be declared as global variables, then there will be no need
2135 to select their values every time. This will improve performance */
2136 --
2137 -- IF (g_report_cat = 'RTM') THEN
2138 -- OPEN c_archive_wage_plan_code_rtm;
2139 -- ELSIF (g_report_cat = 'RTS') THEN
2140 -- OPEN c_archive_wage_plan_code_rts;
2141 -- END IF;
2142 --
2143 -- LOOP
2144 -- hr_utility.trace('In Archive Wage Plan Code RTM loop ');
2145 --
2146 -- IF (g_report_cat = 'RTM') THEN
2147 -- FETCH c_archive_wage_plan_code_rtm INTO l_wage_plan_code,
2148 -- l_assignment_id ;
2149 -- EXIT WHEN c_archive_wage_plan_code_rtm%NOTFOUND;
2150 -- ELSIF (g_report_cat = 'RTS') THEN
2151 -- FETCH c_archive_wage_plan_code_rts INTO l_wage_plan_code,
2152 -- l_assignment_id ;
2153 -- EXIT WHEN c_archive_wage_plan_code_rts%NOTFOUND;
2154 -- END IF;
2155 --
2156 --
2157 -- INSERT INTO ff_archive_items (archive_item_id,
2158 -- user_entity_id,
2159 -- context1,
2160 -- value)
2161 -- VALUES( ff_archive_items_s.NEXTVAL ,
2162 -- l_user_entity_id ,
2163 -- p_assactid ,
2164 -- l_wage_plan_code );
2165 --
2166 --
2167 -- INSERT INTO ff_archive_item_contexts (archive_item_id,
2168 -- sequence_no ,
2169 -- context ,
2170 -- context_id )
2171 -- VALUES (ff_archive_items_s.currval,
2172 -- 1 ,
2173 -- l_assignment_id ,
2174 -- l_context_id_assignment_id);
2175 -- END LOOP;
2176 -- IF (g_report_cat = 'RTM') THEN
2177 -- CLOSE c_archive_wage_plan_code_rtm;
2178 -- ELSIF (g_report_cat = 'RTS') THEN
2179 -- CLOSE c_archive_wage_plan_code_rts;
2180 -- END IF;
2181
2182
2183
2184 /* Due to the performance issues raised by Internal the above code has been replaced
2185 by tmehra 18-OCT-2001*/
2186
2187 l_wage_plan_ct := 0;
2188
2189 FOR c_rec in c_archive_wage_plan_code
2190 LOOP
2191
2192 hr_utility.trace('In Archive Wage Plan Code loop ');
2193
2194 l_wage_plan_code := c_rec.aei_information3;
2195
2196 INSERT INTO ff_archive_items (archive_item_id,
2197 user_entity_id,
2198 context1,
2199 value)
2200 VALUES( ff_archive_items_s.NEXTVAL ,
2201 l_user_entity_id ,
2202 p_assactid ,
2203 l_wage_plan_code );
2204
2205 INSERT INTO ff_archive_item_contexts (archive_item_id,
2206 sequence_no ,
2207 context ,
2208 context_id )
2209 VALUES (ff_archive_items_s.currval,
2210 1 ,
2211 asgid ,
2212 l_context_id_assignment_id);
2213
2214 l_wage_plan_ct := l_wage_plan_ct + 1;
2215
2216 END LOOP;
2217
2218 IF l_wage_plan_ct = 0 THEN
2219
2220 FOR c_rec in c_gre_wage_plan_code
2221 LOOP
2222
2223 hr_utility.trace('In Archive GRE Wage Plan Code loop ');
2224
2225 l_wage_plan_code := c_rec.wage_plan;
2226
2227 END LOOP;
2228
2229
2230
2231 INSERT INTO ff_archive_items (archive_item_id,
2232 user_entity_id,
2233 context1,
2234 value)
2235 VALUES( ff_archive_items_s.NEXTVAL ,
2236 l_user_entity_id ,
2237 p_assactid ,
2238 l_wage_plan_code );
2239
2240 INSERT INTO ff_archive_item_contexts (archive_item_id,
2241 sequence_no ,
2242 context ,
2243 context_id )
2244 VALUES (ff_archive_items_s.currval,
2245 1 ,
2246 asgid ,
2247 l_context_id_assignment_id);
2248
2249 END IF;
2250
2251 END IF;
2252
2253
2254
2255 hr_utility.set_location ('archive_data',2);
2256 /* Bug 773937 */
2257 /* If the chunk of the assignment is same as the minimun chunk
2258 for the payroll_action_id and the gre data has not yet been
2259 archived then archive the gre data i.e. the employer data */
2260
2261 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2262 hr_utility.trace('archive_data archiving employer data');
2263 archive_gre_data(p_payroll_action_id => l_payroll_action_id,
2264 p_tax_unit_id => taxunitid);
2265 hr_utility.trace('archive_data archiving employer data');
2266 end if;
2267 /* End of Bug 773937 */
2268
2269 /* Setup contexts */
2270
2271 pay_balance_pkg.set_context ('ASSIGNMENT_ID', asgid);
2272 pay_balance_pkg.set_context ('DATE_EARNED',fnd_date.date_to_canonical(date_earned));
2273 /* pay_balance_pkg.set_context ('DATE_EARNED',fnd_date.date_to_canonical(date_earned,'DD-MON-YYYY')); date format not required */
2274 pay_balance_pkg.set_context ('TAX_UNIT_ID', taxunitid);
2275
2276 /* Get the year begin and year end dates */
2277
2278 l_year_start := trunc(p_effective_date, 'Y');
2279 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2280
2281 /* Bug 976472 */
2282 if g_sqwl_state = 'NY' then
2283
2284 /* Initialise the global PL/SQL table */
2285
2286 for i in 1..l_context_no loop
2287
2288 pay_archive.g_context_values.name(i) := NULL;
2289 pay_archive.g_context_values.value(i) := NULL;
2290
2291 end loop;
2292
2293
2294 /* Get the New York burroughs and the Yonker City if the
2295 employee has tax records for them */
2296
2297 open c_get_city;
2298 loop
2299
2300 hr_utility.trace('In city loop ');
2301
2302 fetch c_get_city into l_jurisdiction;
2303 exit when c_get_city%NOTFOUND;
2304
2305 l_count := l_count + 1;
2306 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
2307 pay_archive.g_context_values.value(l_count) := l_jurisdiction;
2308
2309 end loop;
2310 close c_get_city;
2311
2312 If l_count = 0 then
2313 l_count := l_count + 1;
2314 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
2315 pay_archive.g_context_values.value(l_count) := g_sqwl_jursd;
2316 end if;
2317 pay_archive.g_context_values.sz := l_count;
2318
2319 end if;
2320 /* End Bug 976472 */
2321
2322 /* To get person level balances you must use the highest assignment action
2323 of the last paid assignment */
2324 /* Modifying to select effective_date from pay_payroll_actions corrsponding to
2325 the assignment action selected to solve th e new York SQWL 4th quarter problem */
2326
2327 --Bug 3331021 : Remove Query with Rule hint and added cursor c_get_latest_asg
2328
2329
2330 /* Get the effective_date and start_date of the payroll_Action_id */
2331
2332 select effective_date,
2333 start_date
2334 into l_period_end,
2335 l_period_start
2336 from pay_payroll_actions
2337 where payroll_action_id = l_payroll_action_id;
2338
2339
2340 begin
2341 open c_get_latest_asg(asgid );
2342 fetch c_get_latest_asg into aaid,eff_date;
2343 hr_utility.trace('aaid in action creation code'||to_char(aaid));
2344 close c_get_latest_asg;
2345
2346 exception
2347 when no_data_found then
2348 aaid := -9999;
2349 raise_application_error(-20001,'Balance Assignment Action does not exist for : '||to_char(asgid));
2350 end;
2351
2352 /* Updating the serial Number column of pay_assignment_actions with 1 if
2353 the effective_date of the assignment action id is lying in the
2354 fourth quarter. */
2355
2356 if g_sqwl_state = 'NY' and to_char(p_effective_date,'MM-DD') = '12-31'
2357 THEN
2358 if(eff_date < trunc(p_effective_date,'Q'))
2359 THEN
2360 update pay_assignment_actions paa
2361 set serial_number = 1
2362 where paa.assignment_action_id = p_assactid;
2363 END IF;
2364 END IF;
2365 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID',aaid);
2366 pay_archive.balance_aa := aaid;
2367
2368 end archive_data;
2369
2370 /* Name : update_ff_archive_items
2371 Purpose : Given the SQWL payroll_action_id, identifies SQWL assignment actions for which
2372 serial number is set to 1 (those employee assgnment actions who doesnt have balances
2373 in the 4th Qtr while running 4th qtr new york SQWL report ) and update QTD balances
2374 to zero for the assignment action in ff_archive_items.
2375 Arguments : SQWL Payroll Action ID
2376 */
2377
2378 /* added A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD and A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD
2379 for bug 1494215 of NY Q4 */
2380
2381 FUNCTION Update_ff_archive_items (p_payroll_action_id in VARCHAR2)
2382 return varchar is
2383 BEGIN
2384 update ff_archive_items ffai
2385 set ffai.value = 0
2386 where ffai.user_entity_id in (
2387 select user_entity_id
2388 from ff_database_items
2389 where user_name in ('A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD',
2390 'A_SUI_ER_SUBJ_NWHABLE_PER_JD_GRE_QTD',
2391 'A_SUI_ER_125_REDNS_PER_JD_GRE_QTD',
2392 'A_SUI_ER_401_REDNS_PER_JD_GRE_QTD',
2393 'A_SUI_ER_DEP_CARE_REDNS_PER_JD_GRE_QTD',
2394 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD',
2395 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_QTD',
2396 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_QTD',
2397 'A_SIT_125_REDNS_PER_JD_GRE_QTD',
2398 'A_SIT_401_REDNS_PER_JD_GRE_QTD',
2399 'A_SIT_DEP_CARE_REDNS_PER_JD_GRE_QTD',
2400 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD',
2401 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD',
2402 'A_SIT_WITHHELD_PER_JD_GRE_QTD')
2403 )
2404 and ffai.context1 in (
2405 select paa.assignment_action_id
2406 from pay_assignment_actions paa,
2407 pay_payroll_actions ppa
2408 where ppa.payroll_action_id = paa.payroll_action_id
2409 and ppa.report_type = 'SQWL'
2410 and ppa.report_qualifier = 'NY'
2411 and ppa.payroll_action_id = to_number(p_payroll_action_id)
2412 and paa.serial_number = 1
2413 );
2414 commit;
2415 return 'Y';
2416
2417 EXCEPTION
2418 when OTHERS then
2419 hr_utility.trace('Error while updating ff_archive_items ');
2420 return 'N';
2421 END Update_ff_archive_items;
2422
2423
2424 --Name
2425 -- preprocess_check
2426 --Purpose
2427 -- This function checks if
2428 -- In case of RTS : No person has got more than one wage plan code. Any
2429 -- of his/her assignments shpuld be having more than one
2430 -- wage plan code. If the two assignments for the same
2431 -- person has different wage plan codes, then also it is
2432 -- an error.
2433 -- In RTM : No person should be having a null wage plan code.
2434 -- In both the cases, only those assignments are taken
2435 -- into consideration that were paid in the period
2436 -- concerned. Added as a part of Enhancement Req 1063413
2437 ---------------------------------------------------------------------------
2438 FUNCTION preprocess_check
2439 (
2440 l_pactid NUMBER ,
2441 l_period_start DATE ,
2442 l_period_end DATE ,
2443 l_bus_group_id pay_payroll_actions.business_group_id%type,
2444 l_state VARCHAR2,
2445 l_report_cat VARCHAR2
2446 )
2447 RETURN BOOLEAN IS
2448
2449 CURSOR c_chk_asg_wp IS
2450 SELECT count(*) ct
2451 FROM per_assignments_f paf,
2452 per_assignment_extra_info paei
2453 WHERE paf.business_group_id = l_bus_group_id
2454 AND paf.effective_end_date >= l_period_start
2455 AND paf.effective_start_date <= l_period_end
2456 AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
2457 AND paei.aei_information1 = l_state /* Added for performance improvement Bug# 4344959 */
2458 AND paei.assignment_id = paf.assignment_id
2459 AND NOT EXISTS (SELECT null
2460 FROM hr_organization_information orgi,
2461 hr_soft_coding_keyflex sft
2462 WHERE orgi.organization_id = to_number(sft.segment1)
2463 AND sft.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
2464 AND orgi.org_information1 = paei.aei_information1
2465 AND (orgi.org_information2 = paei.aei_information2
2466 OR paei.aei_information2 IS NULL)
2467 AND orgi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
2468 AND orgi.org_information3 = paei.aei_information3);
2469
2470
2471
2472 CURSOR c_chk_gre_wp IS
2473 SELECT count(*) ct
2474 FROM hr_legal_entities org
2475 WHERE org.business_group_id = l_bus_group_id
2476 AND EXISTS (SELECT null
2477 FROM hr_organization_information orgi
2478 WHERE organization_id = org.organization_id
2479 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
2480 AND org_information1 = 'CA')
2481 AND NOT EXISTS ( SELECT null
2482 FROM hr_organization_information orgi
2483 WHERE organization_id = org.organization_id
2484 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
2485 AND org_information1 = 'CA'
2486 AND org_information4 = 'Y');
2487
2488 CURSOR c_dup_orgn_info IS
2489 SELECT count(*) ct
2490 FROM hr_legal_entities org,
2491 (select distinct
2492 a.organization_id,
2493 a.org_information1,
2494 a.org_information3
2495 FROM hr_organization_information a
2496 WHERE org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO') b
2497 WHERE org.business_group_id = l_bus_group_id
2498 AND b.organization_id = org.organization_id
2499 AND 1 < ( SELECT count(*)
2500 FROM hr_organization_information orgi
2501 WHERE organization_id = org.organization_id
2502 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
2503 AND org_information1 = b.org_information1
2504 AND org_information3 = b.org_information3);
2505
2506
2507 l_flag VARCHAR2(4) ;
2508 l_wage_plan_code hr_organization_information.org_information3%TYPE;
2509 l_company_sui_id hr_organization_information.org_information2%TYPE;
2510 l_counter NUMBER := 0 ;
2511 l_distinct_wage_plan_code NUMBER := 0 ;
2512
2513
2514 BEGIN
2515 hr_utility.set_location('pay_us_sqwl_archive.preprocess_check', 10);
2516
2517 IF (l_report_cat = 'RTM') THEN
2518
2519 l_counter := 0;
2520
2521 FOR c_rec IN c_dup_orgn_info
2522 LOOP
2523
2524 l_counter := c_rec.ct;
2525
2526 END LOOP;
2527
2528 IF (l_counter > 0) THEN
2529 hr_utility.set_location('pay_us_sqwl_archive.preprocess_check', 30);
2530 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
2531 hr_utility.set_message_token('FORMAT', '> 1 row in hoi for wg plcd');
2532 hr_utility.raise_error;
2533 END IF;
2534 END IF;
2535
2536 IF (l_report_cat = 'RTM') THEN
2537
2538 l_counter := 0;
2539
2540 FOR c_rec IN c_chk_gre_wp
2541 LOOP
2542
2543 l_counter := c_rec.ct;
2544
2545 END LOOP;
2546
2547 IF l_counter > 0 THEN
2548 hr_utility.set_location('pay_us_sqwl_archive.preprocess_check', 40);
2549 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
2550 hr_utility.set_message_token('FORMAT',' Default Wage Plan not marked');
2551 hr_utility.raise_error;
2552 END IF;
2553
2554 l_counter := 0;
2555
2556 FOR c_rec IN c_chk_asg_wp
2557 LOOP
2558
2559 l_counter := c_rec.ct;
2560
2561 END LOOP;
2562
2563 IF l_counter > 0 THEN
2564 hr_utility.set_location('pay_us_sqwl_archive.preprocess_check', 50);
2565 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
2566 hr_utility.set_message_token('FORMAT',' wage plan not defined at GRE');
2567 hr_utility.raise_error;
2568 END IF;
2569
2570 END IF;
2571 RETURN TRUE;
2572 END preprocess_check;
2573
2574 /* Name : archive_asg_locs
2575 Purpose : This procedure will archive the assignment locations as of the 12
2576 of each months during the sqwl quarter.
2577 Arguments :
2578 Notes :
2579 */
2580
2581 PROCEDURE archive_asg_locs( p_asg_act_id in number
2582 ,p_pay_act_id in number
2583 ,p_asg_id in number)
2584 IS
2585
2586 CURSOR c_asg_loc_mon ( p_ass_act_id number
2587 ,p_mon_of_qtr number) IS
2588 SELECT ASG.LOCATION_ID
2589 FROM per_assignments_f ASG
2590 , pay_assignment_actions ASSACT
2591 , pay_payroll_actions PACT
2592 WHERE ASSACT.assignment_action_id = p_ass_act_id
2593 AND ASSACT.payroll_action_id = PACT.payroll_action_id
2594 AND ASSACT.assignment_id = ASG.assignment_id
2595 AND add_months(trunc (PACT.effective_date, 'Q'), p_mon_of_qtr - 1) + 11
2596 BETWEEN ASG.effective_start_date
2597 AND ASG.Effective_end_date;
2598
2599 l_location_id per_all_assignments_f.location_id%type;
2600 l_user_entity_id ff_user_entities.user_entity_id%type;
2601 l_archive_item_id ff_archive_items.archive_item_id%type;
2602 l_object_version_number ff_archive_items.object_version_number%type;
2603 l_some_warning boolean;
2604
2605 l_procedure varchar2(16) := 'archive_asg_locs';
2606
2607
2608 CURSOR c_asg_loc_end (p_ass_acti_id number) IS
2609 /*Commenting for bug 2510853
2610 SELECT paf.location_id
2611 FROM per_assignments_f paf,
2612 pay_assignment_actions paa,
2613 pay_payroll_actions ppa
2614 WHERE (paa.assignment_action_id = p_ass_acti_id
2615 AND paa.payroll_action_id = ppa.payroll_action_id
2616 AND paa.assignment_id = paf.assignment_id
2617 AND ppa.business_group_id = paf.business_group_id
2618 AND ppa.effective_date BETWEEN paf.effective_start_date
2619 AND paf.effective_end_date
2620 )
2621 OR (paa.assignment_action_id = p_ass_acti_id
2622 AND paa.payroll_action_id = ppa.payroll_action_id
2623 AND paa.assignment_id = paf.assignment_id
2624 AND ppa.business_group_id = paf.business_group_id
2625 AND paf.effective_end_date =
2626 (SELECT max(paf1.effective_end_date)
2627 FROM per_assignments_f paf1
2628 WHERE paf1.assignment_id = paf.assignment_id
2629 AND paf1.effective_end_date BETWEEN ppa.start_date
2630 AND ppa.effective_date
2631 )
2632 );
2633 */
2634
2635 SELECT paf.location_id
2636 FROM per_assignments_f paf,
2637 pay_assignment_actions paa,
2638 pay_payroll_actions ppa
2639 WHERE paa.assignment_action_id = p_ass_acti_id
2640 AND paa.payroll_action_id = ppa.payroll_action_id
2641 AND paa.assignment_id = paf.assignment_id
2642 -- commenting the redundant join with business group id for bug 2809506
2643 -- AND ppa.business_group_id = paf.business_group_id
2644 AND ((ppa.effective_date BETWEEN paf.effective_start_date
2645 AND paf.effective_end_date)
2646 OR
2647 (paf.effective_end_date =
2648 (SELECT max(paf1.effective_end_date)
2649 FROM per_assignments_f paf1
2650 WHERE paf1.assignment_id = paf.assignment_id
2651 AND paf1.effective_end_date BETWEEN ppa.start_date
2652 AND ppa.effective_date)
2653 )
2654 )
2655 order by paf.effective_end_date desc;
2656
2657 BEGIN
2658
2659 hr_utility.set_location('archive_asg_locs.' || l_procedure , 10);
2660 hr_utility.trace('p_asg_act_id = '||to_char(p_asg_act_id));
2661 hr_utility.trace('p_asg_id = '||to_char(p_asg_id));
2662 hr_utility.trace('p_pay_act_id = '||to_char(p_pay_act_id));
2663
2664
2665 FOR i IN 1 .. 3 LOOP
2666 OPEN c_asg_loc_mon(p_asg_act_id,
2667 i);
2668 Fetch c_asg_loc_mon into l_location_id;
2669
2670 IF c_asg_loc_mon%NOTFOUND THEN
2671 l_location_id := Null;
2672 END IF;
2673
2674 CLOSE c_asg_loc_mon;
2675
2676 IF l_location_id is not NULL THEN
2677
2678 hr_utility.set_location('archive_asg_locs.' || l_procedure , 20);
2679
2680 -- set the correct user_entity_id for the archive call
2681 BEGIN
2682 SELECT user_entity_id
2683 INTO l_user_entity_id
2684 FROM ff_user_entities
2685 WHERE user_entity_name = 'A_SQWL_LOC_MON_' || to_char(i);
2686 EXCEPTION
2687 WHEN NO_DATA_FOUND THEN
2688 hr_utility.trace('User entities SQWL_LOC_MON_* not define contact your system administrator');
2689 raise hr_utility.hr_error;
2690 END;
2691
2692 hr_utility.set_location('archive_asg_locs.' || l_procedure , 30);
2693
2694 -- Call the create archive item api procedure
2695 ff_archive_api.create_archive_item(
2696 p_archive_item_id => l_archive_item_id
2697 ,p_user_entity_id => l_user_entity_id
2698 ,p_archive_value => l_location_id
2699 ,p_archive_type => 'AAP'
2700 ,p_action_id => p_asg_act_id
2701 ,p_legislation_code => 'US'
2702 ,p_object_version_number => l_object_version_number
2703 ,p_some_warning => l_some_warning
2704 );
2705
2706 IF l_some_warning THEN
2707 hr_utility.trace('Error occurrecd when creating archive item ');
2708 raise hr_utility.hr_error;
2709 END IF;
2710 END IF;
2711
2712 END LOOP;
2713
2714 hr_utility.set_location('archive_asg_locs.' || l_procedure , 40);
2715
2716 -- Process the location id for the end of the period.
2717 OPEN c_asg_loc_end(p_asg_act_id);
2718
2719 FETCH c_asg_loc_end INTO l_location_id;
2720
2721 IF c_asg_loc_end%NOTFOUND THEN
2722 close c_asg_loc_end;
2723 hr_utility.trace('Error occurrecd when creating archive item ');
2724 hr_utility.trace('Error occurrecd : Assignment Location not found for p_asg_act_id ='|| to_char(p_asg_act_id));
2725 raise hr_utility.hr_error;
2726 END IF;
2727 close c_asg_loc_end;
2728
2729
2730 hr_utility.set_location('archive_asg_locs.' || l_procedure , 50);
2731
2732 -- set the correct user_entity_id for the archive call
2733 BEGIN
2734 SELECT user_entity_id
2735 INTO l_user_entity_id
2736 FROM ff_user_entities
2737 WHERE user_entity_name = 'A_SQWL_LOC_QTR_END';
2738 EXCEPTION
2739 WHEN NO_DATA_FOUND THEN
2740 hr_utility.trace('User entities A_SQWL_LOC_END_QTR not define contact your system administrator');
2741 raise hr_utility.hr_error;
2742 END;
2743
2744 hr_utility.set_location('archive_asg_locs.' || l_procedure , 60);
2745
2746 -- Call the create archive item api procedure
2747 ff_archive_api.create_archive_item(
2748 p_archive_item_id => l_archive_item_id
2749 ,p_user_entity_id => l_user_entity_id
2750 ,p_archive_value => l_location_id
2751 ,p_archive_type => 'AAP'
2752 ,p_action_id => p_asg_act_id
2753 ,p_legislation_code => 'US'
2754 ,p_object_version_number => l_object_version_number
2755 ,p_some_warning => l_some_warning
2756 );
2757
2758 IF l_some_warning THEN
2759 hr_utility.trace('Error occurrecd when creating archive item ');
2760 hr_utility.trace('Error occurrecd when creating archive item for User entity A_SQWL_LOC_END_QTR');
2761 raise hr_utility.hr_error;
2762 END IF;
2763
2764 EXCEPTION
2765 WHEN OTHERS THEN
2766 hr_utility.trace('Error occurrecd when creating archive item ');
2767 hr_utility.trace('Error occurrecd when othersof archive_asg_locs ');
2768 raise hr_utility.hr_error;
2769
2770 END archive_asg_locs;
2771
2772
2773 /* Name : range_cursor
2774 Purpose : This returns the select statement that is used to created the
2775 range rows.
2776 Arguments :
2777 Notes :
2778 */
2779
2780 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
2781 l_state pay_payroll_actions.report_qualifier%type;
2782 l_report_cat pay_payroll_actions.report_category%type;
2783 l_effective_date pay_payroll_actions.effective_date%type;
2784 l_start_date pay_payroll_actions.start_date%type;
2785 l_business_group_id pay_payroll_actions.business_group_id%type;
2786
2787 /* Bug 773937 */
2788 l_tax_unit_id number;
2789 l_archive boolean:= FALSE;
2790
2791 l_from number;
2792 l_to number;
2793 l_length number;
2794 l_w2_reporting_rules_exist number;
2795
2796 /* Local variables used for checking W2 Reporting Rules */
2797 message_text VARCHAR2(32000):= null;
2798 message_preprocess VARCHAR2(2000) := null;
2799
2800
2801 /* End of Bug 773937 */
2802
2803 -- The l_preprocess_flag variable was added by Ashu Gupta (ashgupta)
2804 -- on 08-FEB-2000 to check if any person has an invalid wage plan code
2805
2806 l_preprocess_flag BOOLEAN := FALSE;
2807
2808 cursor c_reporting_rules(cp_tax_unit_id in number) is
2809 select '1' from hr_organization_information
2810 where organization_id = cp_tax_unit_id
2811 and org_information_context = 'W2 Reporting Rules';
2812 begin
2813
2814 SELECT report_qualifier,
2815 report_category,
2816 effective_date,
2817 start_date,
2818 business_group_id
2819 INTO l_state,
2820 l_report_cat,
2821 l_effective_date,
2822 l_start_date,
2823 l_business_group_id
2824 FROM pay_payroll_actions
2825 WHERE payroll_action_id = pactid;
2826
2827 hr_utility.trace('Selected from pay_payroll_actions ');
2828
2829 /* Bug 1220213 */
2830 /* If New York state and last quarter SQWL, then the date range is full year */
2831 if ( l_state = 'NY' and to_char(l_effective_date,'DD-MON') = '31-DEC' ) then
2832 l_start_date := trunc(l_start_date,'YYYY');
2833 end if;
2834 /* End of Bug 1220213 */
2835
2836 if (l_state = 'LA' and l_report_cat = 'RTLAQ') then
2837 sqwl_range := 'SELECT distinct ASG.person_id
2838 FROM hr_organization_information HOI,
2839 per_assignments_f ASG,
2840 pay_us_asg_reporting puar,
2841 pay_state_rules SR
2842 WHERE SR.state_code = ''' || l_state || '''
2843 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
2844 AND ASG.assignment_id = puar.assignment_id
2845 AND ASG.assignment_type = ''E''
2846 AND ASG.effective_start_date <= ''' || l_effective_date || '''
2847 AND ASG.effective_end_date >= ''' || l_start_date || '''
2848 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
2849 AND HOI.organization_id = puar.tax_unit_id
2850 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
2851 AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
2852 AND NVL(HOI.ORG_INFORMATION16,''No'') = ''Yes''
2853 AND not exists (select ''x''
2854 from hr_organization_information HOI2
2855 where HOI2.organization_id = puar.tax_unit_id
2856 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
2857 AND HOI2.ORG_INFORMATION2 is not null)
2858 AND ASG.payroll_id is not null
2859 AND :payroll_action_id is not null
2860 ORDER BY ASG.person_id';
2861
2862 elsif (l_state = 'CT' and l_report_cat = 'RTCTN') then
2863 sqwl_range := 'SELECT distinct ASG.person_id
2864 FROM hr_organization_information HOI,
2865 per_assignments_f ASG,
2866 pay_us_asg_reporting puar,
2867 pay_state_rules SR
2868 WHERE SR.state_code = ''' || l_state || '''
2869 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
2870 AND ASG.assignment_id = puar.assignment_id
2871 AND ASG.assignment_type = ''E''
2872 AND ASG.effective_start_date <= ''' || l_effective_date || '''
2873 AND ASG.effective_end_date >= ''' || l_start_date || '''
2874 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
2875 AND HOI.organization_id = puar.tax_unit_id
2876 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
2877 AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
2878 AND NVL(HOI.ORG_INFORMATION20,''No'') = ''Yes''
2879 AND not exists (select ''x''
2880 from hr_organization_information HOI2
2881 where HOI2.organization_id = puar.tax_unit_id
2882 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
2883 AND HOI2.ORG_INFORMATION2 is not null)
2884 AND ASG.payroll_id is not null
2885 AND :payroll_action_id is not null
2886 ORDER BY ASG.person_id';
2887 else
2888 IF (l_state = 'CA') THEN
2889 l_preprocess_flag := preprocess_check(pactid ,
2890 l_start_date ,
2891 l_effective_date ,
2892 l_business_group_id ,
2893 l_state ,
2894 l_report_cat );
2895 END IF;
2896 IF ((l_preprocess_flag = TRUE AND l_state = 'CA') OR
2897 l_state <> 'CA') THEN
2898 sqwl_range := 'SELECT distinct ASG.person_id
2899 FROM hr_organization_information HOI,
2900 per_assignments_f ASG,
2901 pay_us_asg_reporting puar,
2902 pay_state_rules SR
2903 WHERE SR.state_code = ''' || l_state || '''
2904 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
2905 AND ASG.assignment_id = puar.assignment_id
2906 AND ASG.assignment_type = ''E''
2907 AND ASG.effective_start_date <= ''' || l_effective_date || '''
2908 AND ASG.effective_end_date >= ''' || l_start_date || '''
2909 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
2910 AND ((''' || l_state || ''' IN ( ''CA'',''ME''))
2911 OR (not exists (select ''x''
2912 from hr_organization_information HOI2
2913 where HOI2.organization_id = puar.tax_unit_id
2914 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
2915 AND HOI2.ORG_INFORMATION2 is not null)))
2916 AND HOI.organization_id = puar.tax_unit_id
2917 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
2918 AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
2919 AND NVL(HOI.ORG_INFORMATION16,''No'') = ''No''
2920 AND NVL(HOI.ORG_INFORMATION20,''No'') = ''No''
2921 AND ASG.payroll_id is not null
2922 AND :payroll_action_id is not null
2923 ORDER BY ASG.person_id';
2924
2925 /* commented by saurgupt for testing
2926 sqwl_range := 'SELECT distinct ASG.person_id
2927 FROM pay_payrolls_f PPY,
2928 hr_organization_information HOI,
2929 per_assignments_f ASG,
2930 pay_us_asg_reporting puar,
2931 pay_state_rules SR
2932 WHERE SR.state_code = ''' || l_state || '''
2933 AND substr(SR.jurisdiction_code ,1,2) =
2934 substr(puar.jurisdiction_code,1,2)
2935 AND ASG.assignment_id = puar.assignment_id
2936 AND ASG.assignment_type = ''E''
2937 AND ASG.effective_start_date <= ''' || l_effective_date || '''
2938 AND ASG.effective_end_date >= ''' || l_start_date || '''
2939 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
2940 AND ((''' || l_state || ''' IN ( ''CA'',''ME''))
2941 OR (not exists (select ''x''
2942 from hr_organization_information HOI2
2943 where HOI2.organization_id = puar.tax_unit_id
2944 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
2945 AND HOI2.ORG_INFORMATION2 is not null)))
2946 AND HOI.organization_id = puar.tax_unit_id
2947 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
2948 AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
2949 AND NVL(HOI.ORG_INFORMATION16,''No'') = ''No''
2950 AND NVL(HOI.ORG_INFORMATION20,''No'') = ''No''
2951 AND PPY.payroll_id = ASG.payroll_id
2952 AND :payroll_action_id is not null
2953 ORDER BY ASG.person_id';
2954 */
2955 END IF;
2956
2957 end if;
2958
2959 hr_utility.trace('Bulit sqlstr for range ');
2960
2961 sqlstr := sqwl_range;
2962
2963 /* Bug 773937 */
2964 /* Select Tax unit Id from legislative parameters */
2965 select INSTR(legislative_parameters,'TRANSFER_TRANS_LEGAL_CO_ID=')
2966 + LENGTH('TRANSFER_TRANS_LEGAL_CO_ID=')
2967 into l_from
2968 from pay_payroll_actions
2969 where payroll_action_id = pactid;
2970
2971 hr_utility.trace('l_from is '||to_char(l_from));
2972
2973
2974 /* End position of state in legislative parameters */
2975
2976 select INSTR(legislative_parameters,'TRANSFER_DATE=')
2977 into l_to
2978 from pay_payroll_actions
2979 where payroll_action_id = pactid;
2980
2981 hr_utility.trace('l_to is '||to_char(l_to));
2982
2983 l_length := l_to - l_from - 1 ;
2984
2985 hr_utility.trace('l_length is '||to_char(l_length));
2986
2987 select fnd_number.canonical_to_number(substr(legislative_parameters, l_from , l_length ))
2988 into l_tax_unit_id
2989 from pay_payroll_actions
2990 where payroll_action_id = pactid;
2991
2992 hr_utility.trace('Transmitter GRS is '||to_char(l_tax_unit_id));
2993 hr_utility.trace('Report Category is '||l_report_cat);
2994
2995 /* Commenting this check as there's no need to define W2 reporting rules
2996 for SQWL's except for PR, which checks if a GRE is a PR GRE or not.
2997 if l_report_cat in ('RM', 'RTLAQ') then
2998
2999 open c_reporting_rules(l_tax_unit_id);
3000
3001 fetch c_reporting_rules into l_w2_reporting_rules_exist;
3002
3003 if c_reporting_rules%NOTFOUND then
3004
3005 message_preprocess := 'SQWL process - W2 Reporting Rules Missing';
3006 message_text := 'Define these for tax unit id '||to_char(l_tax_unit_id);
3007
3008
3009 hr_utility.trace('W2 Reporting rules have not been setup');
3010
3011 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
3012 pay_core_utils.push_token('record_name',message_preprocess);
3013 pay_core_utils.push_token('description',message_text);
3014
3015 end if;
3016 close c_reporting_rules;
3017
3018 end if;
3019 */
3020
3021 hr_utility.trace('Finished with W2 Reporting Rules check ');
3022
3023 l_archive := chk_gre_archive(pactid);
3024
3025 hr_utility.trace('after gre archive ');
3026
3027 if g_archive_flag = 'N' then
3028
3029 hr_utility.trace('range_cursor archiving employer data');
3030
3031 archive_gre_data(p_payroll_action_id => pactid,
3032 p_tax_unit_id => l_tax_unit_id);
3033
3034 hr_utility.trace('range_cursor archiving employer data');
3035
3036 end if;
3037 /* End of Bug 773937 */
3038
3039 end range_cursor;
3040
3041 --begin
3042
3043 --hr_utility.trace_on(null,'sqwl');
3044
3045 end pay_us_sqwl_archive;
[Home] [Help]