1 package body pay_us_sqwl_archive as
2 /* $Header: pyussqwl.pkb 120.27.12020000.11 2013/02/25 09:05:52 nvelaga 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 22-Feb-2013 nvelaga 115.87 16236978 Modified the logic to get the effective
14 date for Mulitple Assignments.
15 30-Jan-2013 sjawid 115.86 14456648 Modified get_selection_information
16 25-Jan-2013 sjawid 115.85 14456648 Modified Action Creation code to implement
17 SMWL 'IL' state legislative requirement to
18 report the Employer(GRE) with zero wages but
19 not display employees with zero wages.
20
21 Added code to update Serial Number column
22 of pay_assignment_actions to 'X' when the
23 SUI wages value is zero for the employee.
24
25 The assignments which are marked as 'X' in the Serial_number
26 columns will be excluded from the flat file. The cursor
27 smwl_employee at package specifications handles this case.
28
29 22-Jan-2013 schowl 115.84 14456648 Modified procedures 'get_dates' and
30 'get_selection_information' for SMWL report type
31 26-Aug-2012 nvelaga 115.83 14541245 Modified the calculation of Out of State Wages
32 for AR SQWL.
33 26-Jul-2012 nvelaga 115.82 14371049 Modified the cursors to handle Multi-Assignments.
34 23-Jul-2012 nvelaga 115.81 14358354 Modified the AR SUI Limit reached check to be
35 based on Reduced Subject AR Wages reported in
36 previous Quarters.
37 16-Jul-2012 nvelaga 115.80 14097843 Modified the calculation of AR Out of State Wages,
38 based on Reduced Subject AR Wages.
39 18-Jun-2012 nvelaga 115.79 14097843 Replaced != with <> for GSCC failure.
40 15-Jun-2012 nvelaga 115.78 14097843 Modified archive_data procedure to calculate
41 the AR Out of State Wages for reporting in AR SQWL.
42 3-May-2011 rosuri 115.77 12322280 Added code to archive Ohio SQWL related data
43 to procedure archive_data.
44 14-Mar-2011 sjawid 115.72-115.76 10649380 Modified the package to add necessary changes
45 for SQWL GRE wise enhancement.
46 1. Range Cursor Modified
47 2. Raising Error if Customer use
48 old sqwl process, Customer should always
49 use new conc program , "SQWL (Enhanced)".
50 3. Modified Action_creation code to skip
51 other GREs when Customer selects
52 Report Output option as "Single GRE".
53 13-Apr-2010 emunisek 115.72 9561700 Added date check condition in
54 cursor get_previous_fl_taxable
55 12-Apr-2010 emunisek 115.71 9561700 Made changes to use the maximum
56 effective date of Assignment's
57 payroll actions in Balance Call
58 if the assignment ends in between
59 the Quarter for FL SQWL.
60 30-Mar-2010 emunisek 115.68 9356178 Modified to fetch the balances in archive_data
61 for Florida SQWL based on virtual date
62 24-Mar-2010 emunisek 115.67 9356178 Reorganized the code as per the suggestions
63 made in codereview.
64 23-Mar-2010 emunisek 115.66 9356178 Made changes to make file GSCC Compliant
65 23-Mar-2010 emunisek 115.65 9356178 Added code to archive Florida SQWL
66 related data to procedure archive_data
67 06-Jun-2008 mikarthi 115.63 6774422 Changed _cursor c_get_latest_asg
68 for improving performance
69 14-Mar-2007 saurgupt 115.62 5152728 Changed the range_cursor and action_creation to
70 improve perf. In range_cursor, removed pay_payrolls_f.
71 07-Apr-2006 sudedas 115.60 4344959 changing preprocess_check, cursor (c_chk_asg_wp)
72 01-Feb-2006 sudedas 115.59 4890376 Removing hr_organization_information
73 from action_creation cursors (including
74 LA,CT) as the checks are there for range_cursor
75 24-JAN-2006 sackumar 115.58 4869678 Modified the c_get_latest_asg cursor in
76 archive_data procedure.removed the +0 from
77 the query to enable the indexes.
78 16-AUG-2005 sudedas 115.55 Adding some trace messages for
79 procedure archive_asg_locs.
80 10-AUG-2005 sudedas 115.54 4349864 action_creation is modified to
81 enable Range Person ID functionality
82 for LA, PR and CT (non-profit)
83 24-JUN-2005 sudedas 115.53 4310812 action_creation is modified for
84 State of Maine.
85 22-JUN-2005 sudedas 115.52 4310812 range_cursor is changed to include
86 Maine like California.
87 30-MAY-2005 sudedas 115.51 3843134 action_creation is modified for performance
88 25-MAY-2005 sudedas 115.50 4310812 action_creation and report_person_on_tape
89 is modified for Maine Sqwl.
90 24-Nov-2004 saikrish 115.48 Commented the trace.
91 22-Nov-2004 saikrish 115.47 3923296 Changed get_selection_information to check
92 SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD for Indiana
93 28-OCT-2004 saikrish 115.46 3923296 Changed get_selection_information to check
94 SUI_ER_GROSS_PER_JD_GRE_QTD for Indiana
95 22-OCT-2004 jgoswami 115.45 Fix Check Patch error
96 30-SEP-2004 jgoswami 115.44 3925772 modified archive_data, modified
97 cursor c_get_latest_asg to check for
98 all assignments for person which are
99 valid and paid in quarter.
100 01-MAR-2004 jgoswami 115.43 3416806 modified action_creation cursors to check for
101 assignment_type of Employee only.
102 Clean Package, removed unnecessary code.
103 19-FEB-2004 jgoswami 115.42 3331021 modified archive_data, remove query with RULE hint
104 and added cursor c_get_latest_asg
105 21-JAN-2004 jgoswami 115.41 3388513 Changed the criteria for picking up the emps
106 in fourth quarter.
107 check for SIT_SUBJ_WHABLE_PER_JD_GRE_YTD,
108 SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD,
109 SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD.
110 18-DEC-2003 jgoswami 115.40 3324974 comment correctly to initialize
111 l_prev_tax_unit_id to -99999999.
112 04-DEC-2003 fusman 115.39 3281209 Checked the checking criteria for NY
113 in fourth quarter.
114 30-JUL-2003 fusman 115.38 2922028 Changed the criteria for picking up the emps
115 for NY on fourth QTR.
116 07-JUL-2003 sodhingr 115.37 changed the cursor c_state_pr,c_ct_non_profit,
117 c_state,c_state_la_quality for performance.
118 Added the check for
119 asg.effective_end_date >= l_period_start
120 and asg.effective_start_date <= l_period_end
121 instead of ppa.effective_date between
122 ASG.effective_start_date and ASG.effective_end_date
123 02-Jun-2003 fusman 115.36 2965887 Checked for archive type in chk_gre_archive
124 and inserted archive_type in ff_archive_items.
125 28-MAY-2003 tmehra 115.35 2981455 Made changes to the action_creation
126 Added code to error out in case
127 if the wage plan is missing at both
128 the Asg and the GRE level for CA.
129 27-MAY-2003 tmehra 115.34 Made changes to the c_chk_asg_wp
130 cursor, The Asg's with NULL SUI
131 ID does not get falgged off now.
132 22-MAY-2003 tmehra 115.33 2707698 Replaced c_dup_orgn_info
133 cursor with a new select
134 statement due to performance
135 issues.
136 19-MAY-2003 tmehra 115.32 Made changes to the archiver
137 Pre-Process c_chk_gre_wp cursor.
138 15-MAY-2003 tmehra 115.31 Made changes to the archiver
139 Pre-Process.
140 07-MAY-2003 tmehra 115.30 Merged Single and Multi Wage Plan
141 logic for California.
142 23-APR-2003 tclewis 115.29 2924361 added a order by paf.effective_end_date
143 to the cursor c_asg_loc_end.
144 this is to return the latest
145 location id in the cursor.
146 30-MAR-2003 sodhingr 115.28 changed the cursor csr_defined_balance
147 in the function bal_db_item to join
148 with the legislation_code = 'US'
149
150 18-MAR-2003 sodhingr 115.27 changed the cursor c_state_pr, to
151 compare effective_date between
152 l_period_start and l_period_end
153 instead of comparing between l_period_start
154 and l_period start.
155 25-FEB-2003 sodhingr 115.22 2717128 Changed the cursors c_state ,
156 c_ct_non_profit,c_state_la_quality
157 for performance.
158 2809506 changed the cursor c_asg_loc_end for
159 performance, commenting the redundant
160 join with business group id
161 12-FEB-2002 sodhingr 115.21 2779152 Changed action_creation, added the
162 cursor c_state_pr, for PR.
163 11-SEP-2002 sodhingr 115.20 2549213 Changed the foloowing cursors to user
164 per_all_assignments_f instead of per_assignments_f
165 c_ct_non_profit, c_state_la_quality, c_state
166 30-MAY-2002 asasthan 115.19 2396909 For MMREF states SQWLs now
167 give warning when there is
168 no W2 Reporting Rules set up
169 for transmitter GRE.
170 Removed following procedures
171 that were earlier used by EOY
172 process and are not reqd by
173 SQWL process. These are
174
175 PROCEDURE EOY_RANGE_CURSOR
176 PROCEDURE EOY_ACTION_CREATION
177 PROCEDURE EOY_ARCHIVE_DATA
178 PROCEDURE EOY_ARCHINIT
179
180 25-MAR-2002 asasthan 115.18 Added ORDERED hint in action
181 creation cursor
182 20-MAR-2002 djoshi 115.17 Initalized l_prev_tax_unit_id
183 to -9999999;
184 21-FEB-2002 asasthan 115.16 Fix for Bug 2123699
185 Changed l_value > 0 in action
186 creation to l_value <> 0 to
187 create assignment actions for
188 -ve SUI_ER_SUBJ_WHABLE_PER_JD_GRE
189 _QTD assignments.
190 Also made similar changes
191 in residence_in_state and
192 report_person_on_tape.
193
194 05-DEC-2001 asasthan 115.15 Changed for MA SQWLs 2138109
195 18-OCT-2001 tmehra 115.14 Replaced the following cursors
196 - c_archive_wage_plan_code_rts
197 - c_archive_wage_plan_code_rtm
198 with
199 - c_archive_wage_plan_code
200 to improve performance.
201 Also modified archive_data
202 12-OCT-2001 vmehta 115.13 Modified c_state cursor for
203 improving performance.
204 Also modified archive_data
205 05-JUN-2001 tclewis 115.4 Added procedure archive_asg_locs. This
206 will archive the Assignment locations as of
207 the 12th of the month, for each month of the
208 quarter.
209
210 11/16/2000 asasthan 115.8 1494215 Added A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD
211 and A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD
212 to update_ff_archive_items.
213 22-AUG-2000 ashgupta 110.12 1382408 Changed the SQL statement of
214 c_rts_dup_wage cursor. This SQL was
215 changed due to Fidelity issue. Now
216 the cursor does not check for Multi
217 ple wage plan codes across the
218 assignments of a person. It just
219 checks that each individual assgn-
220 ment should not be having more than
221 one wage plan code. It takes care
222 of only paid assignments.
223 19-JUL-2000 ashgupta 40.14 1354144 Changed the SQL statement of
224 c_rts_dup_wage cursor. This SQL was
225 changed due to Fidelity performance
226 problem.
227 12-JUN-2000 asasthan 115.5 update till Q2 2000 changes and includes
228 the 11i fnd_date and fnd_number changes
229 22-MAY-2000 ashgupta 40.12 1237099 Added the error messages in the
230 preprocess_check function
231 02-MAR-2000 rpotnuru 40.11 1220213 Terminated Employees not showing for $th Qtr
232 NY sqwl. Range cursor date range will now the whole
233 Year for NY 4th Qtr SQWL.
234 08-FEB-2000 ashgupta 40.9 SQWL changes for city of Oakland
235 Added code in archinit
236 archive_data
237 range_cursor
238 Added a new fn preprocess_check
239 This was done for the enhancement
240 req 1063413
241 03_DEC-1999 asasthan 40.6 1093595
242 03-DEC-1999 rpotnuru 40.5 1095096 NY sqwl for 4th qtr date range is Year St to
243 1085774 Year End. so for reporting QTD balances
244 setting a flag in pay_assignment_actions
245 if the employee doesnt have balances for the QTD.
246 Added function update_ff_archive_item.
247
248 17-NOV-1999 asasthan Performance Tuning 1079787
249 27-OCT-1999 RPOTNURU 110.0 Bug fix 976472
250
251 25-oct-1999 djoshi added the A_SS_EE_WAGE_BASE and
252 A_SS_EE_WAGE rate to archive the data
253 related to bug 983094 and 101435
254
255 01-sep-1999 achauhan While archiving the employer data
256 add the context of pay_payroll_actions
257 to ff_archive_item_contexts.
258 11-aug-1999 achauhan Added the call to
259 eoy_archive_gre_data in the
260 eoy_range_cursor procedure. This is
261 being done to handle the situation
262 of archiving employer level data
263 even when there are no employees in
264 a GRE.
265 10-aug-1999 achauhan In the archive_data routine,
266 removed the use of payroll_action_id
267 >= 0.
268 04-Aug-1999 VMehta Changed eoy_archive_data to improve performance.
269 02-Jun-1999 meshah added new cursors in the range and action
270 creation cursors to check for non profit
271 gre's for the state of connecticut.
272
273 08-mar-1999 VMehta Added nvl while checking for l_1099R_ind
274 to correct the Louisiana quality jobs program
275 tape processing.
276 26-jan-1999 VMehta Modified function report_person_on_tape to
277 return false for all states except California
278 and Massachusetts.
279 24-Jan-1999 VMehta 805012 Added function report_person_on_tape to perform
280 check for retirees having SIT w/h in california.
281 06-Jan-1999 MReid Changed c_eoy_gre cursor to disable
282 business_group_id index on ppa side
283 30-dec-1998 vmehta 709641 Look at SUI_ER_SUBJ_WHABLE instead of SUI_ER_GROSS
284 for picking up people for SQWL . This makes sure
285 that only people with SUI wages are picked up.
286 27-dec-1998 vmehta Corrected the cursor in action creation to get the
287 tax_unit_name from pay_assignment_actions.
288 21-DEC-1998 achauhan Changed the cursor in action creation to get the
289 assignments from the pay_assignment_actions table.
290
291 08-DEC-1998 vmehta Removed grouping by on assignment_id while creating
292 assignment_ids
293 08-DEC-1998 nbristow Updated the c_state cursor to use
294 an exists rather than a join.
295 07-DEC-1998 nbristow Resolved some issues introduced by
296 40.13.
297 04-DEC-1998 vmehta 750802 Changed the cursors/logic to
298 pick up people who live in
299 California for the California SQWL.
300 29-NOV-1998 nbristow Changes to the SQWL code,
301 now using pay_us_asg_reporting.
302 25-Sep-1998 vmehta Changed the range cursor and
303 the assignment_action creation
304 cursors to support Louisiana
305 Quality Jobs Program Reporting.
306 08-aug-1998 achauhan Added the routines for eoy -
307 Year End Pre-Process
308 18-MAY-1998 NBRISTOW sqwl_range cursor now checks
309 the tax_unit_id etc.
310 06-MAY-1998 NBRISTOW
311
312 14-MAR-2005 sackumar 115.49 4222032 Change in the Range Cursor removing redundant
313 use of bind Variable (:payroll_action_id)
314
315 */
316
317
318 function chk_gre_archive (p_payroll_action_id number) return boolean;
319 procedure create_archive (p_user_entity_id in number,
320 p_context1 in number,
321 p_value in varchar2,
322 p_sequence in pay_us_sqwl_archive.number_data_type_table,
323 p_context in pay_us_sqwl_archive.char240_data_type_table,
324 p_context_id in pay_us_sqwl_archive.number_data_type_table);
325
326 sqwl_range varchar2(4000);
327
328
329
330 /* Name : bal_db_item
331 Purpose : Given the name of a balance DB item as would be seen in a fast formula
332 it returns the defined_balance_id of the balance it represents.
333 Arguments :
334 Notes : A defined balance_id is required by the PLSQL balance function.
335 */
336
337 function bal_db_item
338 (
339 p_db_item_name varchar2
340 ) return number is
341
342 /* Get the defined_balance_id for the specified balance DB item. */
343
344 cursor csr_defined_balance is
345 select fnd_number.canonical_to_number(UE.creator_id)
346 from ff_user_entities UE,
347 ff_database_items DI
348 where DI.user_name = p_db_item_name
349 and UE.user_entity_id = DI.user_entity_id
350 and Ue.creator_type = 'B'
351 and UE.legislation_code = 'US';
352
353 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
354
355 begin
356
357 open csr_defined_balance;
358 fetch csr_defined_balance into l_defined_balance_id;
359 if csr_defined_balance%notfound then
360 close csr_defined_balance;
361 raise hr_utility.hr_error;
362 else
363 close csr_defined_balance;
364 end if;
365
366 return (l_defined_balance_id);
367
368 end bal_db_item;
369
370
371 /* Name : get_dates
372 Purpose : The dates are dependent on the report being run i.e.
373 a W2 report shows information for a tax year while
374 a SQWL report shows information for a quarter within
375 a tax year.
376 Arguments :
377 Notes :
378 */
379
380 procedure get_dates
381 (
382 p_report_type in varchar2,
383 p_effective_date in date,
384 p_period_end in out nocopy date,
385 p_quarter_start in out nocopy date,
386 p_quarter_end in out nocopy date,
387 p_year_start in out nocopy date,
388 p_year_end in out nocopy date
389 ) is
390 begin
391
392
393
394 /* Report is SQWL ie. a quarterly report where the identifier indicates the
395 quarter eg. 0395
396 p_period_end 31-MAR-1995
397 p_quarter_start 01-JAN-1995
398 p_quarter_end 31-MAR-1995
399 p_year_start 01-JAN-1995
400 p_year_end 31-DEC-1995
401 */
402
403 p_quarter_start := trunc(p_effective_date, 'Q');
404 p_quarter_end := add_months(trunc(p_effective_date, 'Q'),3) - 1;
405 p_period_end := p_quarter_end;
406
407 if p_report_type = 'SMWL' then /* Added for Bug 14456648 */
408 p_quarter_start := trunc(p_effective_date, 'MM');
409 p_quarter_end := p_effective_date;
410 end if;
411
412 p_year_start := trunc(p_effective_date, 'Y');
413 p_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
414
415 end get_dates;
416
417
418 /* Name : get_selection_information
419 Purpose : Returns information used in the selection of people to be reported on.
420 Arguments :
421
422 The following values are returned :-
423
424 p_period_start - The start of the period over which to select
425 the people.
426 p_period_end - The end of the period over which to select
427 the people.
428 p_defined_balance_id - The balance which must be non zero for each
429 person to be included in the report.
430 p_group_by_gre - should the people be grouped by GRE.
431 p_group_by_medicare - Should the people ,be grouped by medicare
432 within GRE NB. this is not currently supported.
433 p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
434 the testing of the balance.
435 p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
436 for the testing of the balance.
437
438 Notes : This routine provides a way of coding explicit rules for
439 individual reports where they are different from the
440 standard selection criteria for the report type ie. in
441 NY state the selection of people in the 4th quarter is
442 different from the first 3.
443 */
444
445 procedure get_selection_information
446 (
447
448 /* Identifies the type of report, the authority for which it is being run,
449 and the period being reported. */
450 p_report_type varchar2,
451 p_state varchar2,
452 p_quarter_start date,
453 p_quarter_end date,
454 p_year_start date,
455 p_year_end date,
456 /* Information returned is used to control the selection of people to
457 report on. */
458 p_period_start in out nocopy date,
459 p_period_end in out nocopy date,
460 p_defined_balance_id in out nocopy number,
461 p_group_by_gre in out nocopy boolean,
462 p_group_by_medicare in out nocopy boolean,
463 p_tax_unit_context in out nocopy boolean,
464 p_jurisdiction_context in out nocopy boolean
465 ) is
466
467 begin
468
469 /* Depending on the report being processed, derive all the information
470 required to be able to select the people to report on. */
471
472
473 /* State Quarterly Wage Listings. */
474
475 if p_report_type = 'SQWL' or p_report_type = 'SMWL' then /* Modified for Bug 14456648 */
476
477 /* New York state settings NB. the difference is that the criteria for
478 selecting people in the 4th quarter is different to that used for the
479 first 3 quarters of the tax year. */
480
481 if p_state = 'NY' then
482
483 if instr(to_char(p_quarter_end,'MM'), '12') = 0 then
484
485 /* Period is one of the first 3 quarters of tax year. */
486
487 p_period_start := p_quarter_start;
488 p_period_end := p_quarter_end;
489 p_defined_balance_id := bal_db_item('SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD');
490
491 else
492
493 /* Period is the last quarter of the year.*/
494
495 p_period_start := p_year_start;
496 p_period_end := p_year_end;
497 --p_defined_balance_id := bal_db_item('REGULAR_EARNINGS_PER_GRE_YTD'); /*Bug:2922028*/
498 p_defined_balance_id := bal_db_item('SIT_SUBJ_WHABLE_PER_JD_GRE_YTD'); /*Bug:3388513*/
499
500 end if;
501
502 /* Values are set independent of quarter being reported on. */
503
504 p_group_by_gre := TRUE;
505 p_group_by_medicare := TRUE;
506 p_tax_unit_context := TRUE;
507 p_jurisdiction_context := TRUE;
508
509 else
510
511 /* Default settings for State Quarterly Wage Listing. */
512 hr_utility.set_location ('State',1);
513 p_period_start := p_quarter_start;
514 p_period_end := p_quarter_end;
515 IF p_report_type = 'SMWL' THEN /*bug 14456648*/
516 p_defined_balance_id := bal_db_item('SUI_ER_SUBJ_WHABLE_PER_JD_GRE_MONTH');
517 ELSE
518 p_defined_balance_id := bal_db_item('SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD');
519 END IF;
520 p_group_by_gre := TRUE;
521 p_group_by_medicare := TRUE;
522 p_tax_unit_context := TRUE;
523 p_jurisdiction_context := TRUE;
524
525 hr_utility.set_location ('p_period_start -> '|| p_period_start,1);
526 hr_utility.set_location ('p_period_end -> '|| p_period_end,1);
527 hr_utility.set_location ('p_defined_balance -> SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD',1);
528 hr_utility.set_location ('p_defined_balance_id -> '|| p_defined_balance_id,1);
529
530 end if;
531
532 else /* An invalid report type has been passed so fail. */
533
534 raise hr_utility.hr_error;
535
536 end if;
537
538 end get_selection_information;
539
540
541 /* Name : lookup_jurisdiction_code
542 Purpose : Given a state code ie. AL it returns the jurisdiction code that
543 represents that state.
544 Arguments :
545 Notes :
546 */
547
548 function lookup_jurisdiction_code
549 (
550 p_state varchar2
551 ) return varchar2 is
552
553 /* Get the jurisdiction_code for the specified state code. */
554
555 cursor csr_jurisdiction_code is
556 select SR.jurisdiction_code
557 from pay_state_rules SR
558 where SR.state_code = p_state;
559
560 l_jurisdiction_code pay_state_rules.jurisdiction_code%type;
561
562 begin
563
564 open csr_jurisdiction_code;
565 fetch csr_jurisdiction_code into l_jurisdiction_code;
566 if csr_jurisdiction_code%notfound then
567 close csr_jurisdiction_code;
568 raise hr_utility.hr_error;
569 else
570 close csr_jurisdiction_code;
571 end if;
572
573 return (l_jurisdiction_code);
574
575 end lookup_jurisdiction_code;
576
577
578 ---------------------------------------------------------------------------
579 -- Name
580 -- check_residence_state
581 -- Purpose
582 -- This checks that the state of residence for the given assignment id
583 -- is the same as that passed in. Used
584 -- in this package to determine if a person has lived in the state of
585 -- MA. Such people need to be reported on SQWL for MA.
586 -- Arguments
587 -- Assignment Id
588 -- Period Start Date
589 -- Period End Date
590 -- State
591 ---------------------------------------------------------------------------
592 --
593 FUNCTION check_residence_state (
594 p_assignment_id NUMBER,
595 p_period_start DATE,
596 p_period_end DATE,
597 p_state VARCHAR2,
598 p_effective_end_date DATE
599 ) RETURN BOOLEAN IS
600
601 l_resides_true VARCHAR2(1);
602 BEGIN
603
604 BEGIN
605 SELECT '1'
606 INTO l_resides_true
607 FROM dual
608 WHERE EXISTS (
609 SELECT '1'
610 FROM per_assignments_f paf,
611 per_addresses pad
612 WHERE paf.assignment_id = p_assignment_id AND
613 paf.person_id = pad.person_id AND
614 pad.date_from <= p_period_end AND
615 NVL(pad.date_to ,p_period_end) >= p_period_start AND
616 pad.region_2 = p_state AND
617 pad.primary_flag = 'Y');
618 EXCEPTION when no_data_found then
619 l_resides_true := '0';
620 END;
621
622 hr_utility.trace('l_resides_true =' || l_resides_true);
623
624 IF (l_resides_true = '1' AND
625 pay_balance_pkg.get_value(bal_db_item('GROSS_EARNINGS_PER_GRE_QTD'),
626 p_assignment_id, least(p_period_end, p_effective_end_date)) <> 0) THEN
627
628 hr_utility.trace('Returning TRUE from check_residence_state');
629
630 RETURN TRUE;
631 ELSE
632 RETURN FALSE;
633 END IF;
634 END; -- check_residence_state
635
636 ---------------------------------------------------------------------------
637 -- Name
638 -- report_person_on_tape
639 -- Purpose
640 -- This checks various state specific criteria to decide whether the given
641 -- person should be reported on the tape.
642 -- Arguments
643 -- Assignment Id
644 -- Period Start Date
645 -- Period End Date
646 -- State
647 -- Effective End Date
648 -- 1099R_ind
649 ---------------------------------------------------------------------------
650 --
651 FUNCTION report_person_on_tape (
652 p_assignment_id NUMBER,
653 p_period_start DATE,
654 p_period_end DATE,
655 p_state VARCHAR2,
656 p_effective_end_date DATE,
657 p_1099R_ind VARCHAR2,
658 p_report_type VARCHAR2
659 ) RETURN BOOLEAN IS
660 l_ret_value BOOLEAN := FALSE;
661 l_resides_in_state BOOLEAN;
662 BEGIN
663 IF (p_state = 'MA' ) THEN
664
665 l_resides_in_state := check_residence_state(p_assignment_id,
666 p_period_start, p_period_end, p_state, p_effective_end_date);
667
668 l_ret_value := l_resides_in_state;
669
670
671 END IF;
672
673
674 IF (p_state = 'CA') THEN
675
676 IF (p_1099R_ind = 'Y') THEN
677
678 l_ret_value := (pay_balance_pkg.get_value(
679 bal_db_item('SIT_WITHHELD_PER_JD_GRE_QTD') , p_assignment_id,
680 least(p_period_end, p_effective_end_date)) <> 0 );
681
682
683 ELSE
684
685 l_ret_value := (pay_balance_pkg.get_value(
686 bal_db_item('SIT_GROSS_PER_JD_GRE_QTD') , p_assignment_id,
687 least(p_period_end, p_effective_end_date)) <> 0 );
688
689 END IF;
690 END IF;
691 /* Check for ME Bug# 4310812 */
692 IF (p_state = 'ME') THEN
693 IF (p_1099R_ind = 'Y') THEN
694
695 l_ret_value := (pay_balance_pkg.get_value(
696 bal_db_item('SIT_SUBJ_WHABLE_PER_JD_GRE_QTD') , p_assignment_id,
697 least(p_period_end, p_effective_end_date)) <> 0 );
698 END IF ;
699 END IF ;
700 /* Ending Check for ME Bug# 4310812 */
701
702 IF p_state = 'IL' AND p_report_type = 'SMWL' THEN /* Bug 16202645 */
703 l_ret_value := TRUE;
704 END IF;
705
706 return l_ret_value;
707
708
709 END; --report_person_on_tape
710
711 /* Name : get_user_entity_id
712 Purpose : This gets the user_entity_id for a specific database item name.
713 Arguments : p_dbi_name -> database item name.
714 Notes :
715 */
716
717 function get_user_entity_id (p_dbi_name in varchar2)
718 return number is
719 l_user_entity_id number;
720
721 begin
722
723 select user_entity_id
724 into l_user_entity_id
725 from ff_database_items
726 where user_name = p_dbi_name;
727
728 return l_user_entity_id;
729
730 exception
731 when others then
732 hr_utility.trace('Error while getting the user_entity_id'
733 || to_char(sqlcode));
734 raise hr_utility.hr_error;
735
736 end get_user_entity_id;
737
738 /* Name : action_creation
739 Purpose : This creates the assignment actions for a specific chunk.
740 Arguments :
741 Notes :
742 */
743
744 procedure action_creation(pactid in number,
745 stperson in number,
746 endperson in number,
747 chunk in number) is
748
749
750
751 /* Variables used to hold the select columns from the SQL statement.*/
752
753 l_person_id number;
754 l_assignment_id number;
755 l_tax_unit_id number;
756 l_effective_end_date date;
757
758 /* Variables used to hold the values used as bind variables within the
759 SQL statement. */
760
761 l_bus_group_id number;
762 l_period_start date;
763 l_period_end date;
764
765 /* Variables used to hold the details of the payroll and assignment actions
766 that are created. */
767
768 l_payroll_action_created boolean := false;
769 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
770 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
771
772
773 /* Variable holding the balance to be tested. */
774
775 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
776
777 /* Indicator variables used to control how the people are grouped. */
778
779 l_group_by_gre boolean := FALSE;
780 l_group_by_medicare boolean := FALSE;
781
782 /* Indicator variables used to control which contexts are set up for
783 balance. */
784
785 l_tax_unit_context boolean := FALSE;
786 l_jurisdiction_context boolean := FALSE;
787
788 /* Indicator variable used to check if the GRE has a default wage plan */
789
790 l_gre_wage_plan_exist BOOLEAN := FALSE;
791
792 /* Variables used to hold the current values returned within the loop for
793 checking against the new values returned from within the loop on the
794 next iteration. */
795
796 l_prev_person_id per_people_f.person_id%type;
797 l_prev_asg_id per_assignments_f.assignment_id%type;
798 l_prev_tax_unit_id hr_organization_units.organization_id%type;
799
800 /* Variable to hold the jurisdiction code used as a context for state
801 reporting. */
802
803 l_jurisdiction_code varchar2(30);
804
805 /* general process variables */
806
807 l_report_type pay_payroll_actions.report_type%type;
808 l_report_cat pay_payroll_actions.report_category%type;
809 l_state pay_payroll_actions.report_qualifier%type;
810 l_report_format pay_report_format_mappings_f.report_format%type; -- Bug# 3843134
811 l_value number;
812 l_value_sit number ; --4310812
813 l_person_on boolean ; --4349864
814 l_effective_date date;
815 l_quarter_start date;
816 l_quarter_end date;
817 l_year_start date;
818 l_year_end date;
819 l_1099R_ind varchar2(2);
820 lockingactid number;
821 l_transfer_gre_id number;
822 ----------
823 /*This select is same as cursor c_state except the check for
824 NVL(HOI.org_information16, 'No') = 'Yes'*/
825
826 CURSOR c_state_la_quality IS
827 SELECT
828 ASG.person_id person_id,
829 ASG.assignment_id assignment_id,
830 paa.tax_unit_id tax_unit_id,
831 ppa.effective_date effective_end_date
832 FROM per_all_assignments_f ASG,
833 pay_assignment_actions paa,
834 pay_payroll_actions ppa
835 WHERE ppa.effective_date between l_period_start
836 and l_period_end
837 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
838 and paa.payroll_action_id = ppa.payroll_action_id
839 and paa.assignment_id = ASG.assignment_id
840 /*added to ignore skipped assignment */
841 and paa.action_status <> 'S'
842 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
843 /* Added for Performance, 01-JUL-2003 */
844 and asg.effective_end_date >= l_period_start
845 and asg.effective_start_date <= l_period_end
846
847 and ASG.business_group_id + 0 = l_bus_group_id
848 and ASG.person_id between stperson and endperson
849 and ASG.assignment_type = 'E'
850 and ASG.business_group_id = ppa.business_group_id -- 5152728
851 and exists (select '1'
852 from pay_us_asg_reporting puar,
853 pay_state_rules SR
854 where SR.state_code = l_state
855 and substr(SR.jurisdiction_code ,1,2) =
856 substr(puar.jurisdiction_code,1,2)
857 and ASG.assignment_id = puar.assignment_id
858 )
859 ORDER BY 1, 3, 4 DESC, 2 ;
860
861 -- Added for Bug# 4349864
862 -- Used when RANGE_PERSON_ID functionality is available
863
864 CURSOR c_state_la_quality_person_on IS
865 SELECT
866 ASG.person_id person_id,
867 ASG.assignment_id assignment_id,
868 paa.tax_unit_id tax_unit_id,
869 ppa.effective_date effective_end_date
870 FROM per_all_assignments_f ASG,
871 pay_assignment_actions paa,
872 pay_payroll_actions ppa,
873 pay_population_ranges ppr
874 WHERE ppa.effective_date between l_period_start
875 and l_period_end
876 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
877 and paa.payroll_action_id = ppa.payroll_action_id
878 and paa.assignment_id = ASG.assignment_id
879 /*added to ignore skipped assignment */
880 and paa.action_status <> 'S'
881 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
882 /* Added for Performance, 01-JUL-2003 */
883 and asg.effective_end_date >= l_period_start
884 and asg.effective_start_date <= l_period_end
885
886 and ASG.business_group_id + 0 = l_bus_group_id
887 --and ASG.person_id between stperson and endperson
888 and ppr.payroll_action_id = pactid
889 and ppr.chunk_number = chunk
890 and ppr.person_id = ASG.person_id
891 and ASG.assignment_type = 'E'
892 and ASG.business_group_id = ppa.business_group_id -- 5152728
893 and exists (select '1'
894 from pay_us_asg_reporting puar,
895 pay_state_rules SR
896 where SR.state_code = l_state
897 and substr(SR.jurisdiction_code ,1,2) =
898 substr(puar.jurisdiction_code,1,2)
899 and ASG.assignment_id = puar.assignment_id
900 )
901 ORDER BY 1, 3, 4 DESC, 2 ;
902
903 CURSOR c_state IS
904 SELECT
905 ASG.person_id person_id,
906 ASG.assignment_id assignment_id,
907 paa.tax_unit_id tax_unit_id,
908 ppa.effective_date effective_end_date
909 FROM per_all_assignments_f ASG,
910 pay_assignment_actions paa,
911 pay_payroll_actions ppa
912 WHERE ppa.effective_date between l_period_start
913 and l_period_end
914 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
915 and paa.payroll_action_id = ppa.payroll_action_id
916 and paa.assignment_id = ASG.assignment_id
917 /*added to ignore skipped assignment */
918 and paa.action_status <> 'S'
919 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
920 /* Added for Performance, 01-JUL-2003 */
921 and asg.effective_end_date >= l_period_start
922 and asg.effective_start_date <= l_period_end
923
924 and ASG.business_group_id + 0 = l_bus_group_id
925 and ASG.person_id between stperson and endperson
926 and ASG.assignment_type = 'E'
927 and ASG.business_group_id = ppa.business_group_id -- 5152728
928 and exists (select '1'
929 from pay_us_asg_reporting puar,
930 pay_state_rules SR
931 where SR.state_code = l_state
932 and substr(SR.jurisdiction_code ,1,2) =
933 substr(puar.jurisdiction_code,1,2)
934 and ASG.assignment_id = puar.assignment_id
935 )
936 ORDER BY 1, 3, 4 DESC, 2 ;
937
938 -- Added for Bug# 3843134 (Performance Issue)
939 -- Used when RANGE_PERSON_ID functionality is available
940
941 CURSOR c_state_person_on IS
942 SELECT
943 ASG.person_id person_id,
944 ASG.assignment_id assignment_id,
945 paa.tax_unit_id tax_unit_id,
946 ppa.effective_date effective_end_date
947 FROM per_all_assignments_f ASG,
948 pay_assignment_actions paa,
949 pay_payroll_actions ppa,
950 pay_population_ranges ppr
951 WHERE ppa.effective_date between l_period_start
952 and l_period_end
953 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
954 and paa.payroll_action_id = ppa.payroll_action_id
955 and paa.assignment_id = ASG.assignment_id
956 /*added to ignore skipped assignment */
957 and paa.action_status <> 'S'
958 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
959 /* Added for Performance, 01-JUL-2003 */
960 and asg.effective_end_date >= l_period_start
961 and asg.effective_start_date <= l_period_end
962
963 and ASG.business_group_id + 0 = l_bus_group_id
964 -- and ASG.person_id between stperson and endperson
965 and ppr.payroll_action_id = pactid
966 and ppr.chunk_number = chunk
967 and asg.person_id = ppr.person_id
968 and ASG.assignment_type = 'E'
969 and ASG.business_group_id = ppa.business_group_id -- 5152728
970 and exists (select '1'
971 from pay_us_asg_reporting puar,
972 pay_state_rules SR
973 where SR.state_code = l_state
974 and substr(SR.jurisdiction_code ,1,2) =
975 substr(puar.jurisdiction_code,1,2)
976 and ASG.assignment_id = puar.assignment_id
977 )
978 ORDER BY 1, 3, 4 DESC, 2 ;
979
980 /*This select in c_ct_non_profit is same as cursor c_state except the check for
981 NVL(HOI.org_information20, 'No') = 'Yes'*/
982
983 CURSOR c_ct_non_profit IS
984 SELECT
985 ASG.person_id person_id,
986 ASG.assignment_id assignment_id,
987 paa.tax_unit_id tax_unit_id,
988 ppa.effective_date effective_end_date
989 FROM per_all_assignments_f ASG,
990 pay_assignment_actions paa,
991 pay_payroll_actions ppa
992 WHERE ppa.effective_date between l_period_start
993 and l_period_end
994 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
995 and paa.payroll_action_id = ppa.payroll_action_id
996 and paa.assignment_id = ASG.assignment_id
997 /*added to ignore skipped assignment */
998 and paa.action_status <> 'S'
999 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
1000 /* Added for Performance, 01-JUL-2003 */
1001 and asg.effective_end_date >= l_period_start
1002 and asg.effective_start_date <= l_period_end
1003
1004 and ASG.business_group_id + 0 = l_bus_group_id
1005 and ASG.person_id between stperson and endperson
1006 and ASG.assignment_type = 'E'
1007 and exists (select '1'
1008 from pay_us_asg_reporting puar,
1009 pay_state_rules SR
1010 where SR.state_code = l_state
1011 and substr(SR.jurisdiction_code ,1,2) =
1012 substr(puar.jurisdiction_code,1,2)
1013 and ASG.assignment_id = puar.assignment_id
1014 )
1015 ORDER BY 1, 3, 4 DESC, 2 ;
1016
1017 -- Added for Bug# 4349864
1018 -- Used when RANGE_PERSON_ID functionality is available
1019
1020 CURSOR c_ct_non_profit_person_on IS
1021 SELECT
1022 ASG.person_id person_id,
1023 ASG.assignment_id assignment_id,
1024 paa.tax_unit_id tax_unit_id,
1025 ppa.effective_date effective_end_date
1026 FROM per_all_assignments_f ASG,
1027 pay_assignment_actions paa,
1028 pay_payroll_actions ppa,
1029 pay_population_ranges ppr
1030 WHERE ppa.effective_date between l_period_start
1031 and l_period_end
1032 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1033 and paa.payroll_action_id = ppa.payroll_action_id
1034 and paa.assignment_id = ASG.assignment_id
1035 /*added to ignore skipped assignment */
1036 and paa.action_status <> 'S'
1037 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
1038 /* Added for Performance, 01-JUL-2003 */
1039 and asg.effective_end_date >= l_period_start
1040 and asg.effective_start_date <= l_period_end
1041
1042 and ASG.business_group_id + 0 = l_bus_group_id
1043 --and ASG.person_id between stperson and endperson
1044 and ppr.payroll_action_id = pactid
1045 and ppr.chunk_number = chunk
1046 and ppr.person_id = ASG.person_id
1047 and ASG.assignment_type = 'E'
1048 and exists (select '1'
1049 from pay_us_asg_reporting puar,
1050 pay_state_rules SR
1051 where SR.state_code = l_state
1052 and substr(SR.jurisdiction_code ,1,2) =
1053 substr(puar.jurisdiction_code,1,2)
1054 and ASG.assignment_id = puar.assignment_id
1055 )
1056 ORDER BY 1, 3, 4 DESC, 2 ;
1057
1058 -------
1059
1060 CURSOR c_state_pr IS
1061 SELECT ASG.person_id person_id,
1062 ASG.assignment_id assignment_id,
1063 paa.tax_unit_id tax_unit_id,
1064 ppa.effective_date effective_end_date
1065 FROM per_all_assignments_f ASG,
1066 pay_assignment_actions paa,
1067 pay_payroll_actions ppa,
1068 hr_organization_information HOI_PR
1069 WHERE ppa.effective_date between l_period_start and l_period_end
1070 AND ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1071 AND paa.payroll_action_id = ppa.payroll_action_id
1072 AND hoi_pr.organization_id = paa.tax_unit_id
1073 AND HOI_pr.org_information_context = 'W2 Reporting Rules'
1074 AND NVL(HOI_pr.org_information16, 'A') = 'P'
1075 AND paa.assignment_id = ASG.assignment_id
1076 /*added to ignore skipped assignment */
1077 and paa.action_status <> 'S'
1078 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
1079 /* Added for Performance, 01-JUL-2003 */
1080 and asg.effective_end_date >= l_period_start
1081 and asg.effective_start_date <= l_period_end
1082
1083 AND ASG.business_group_id + 0 = l_bus_group_id
1084 AND ASG.person_id between stperson and endperson
1085 and ASG.assignment_type = 'E'
1086 AND EXISTS (select 'x'
1087 from pay_us_asg_reporting puar,
1088 pay_state_rules SR
1089 where substr(SR.jurisdiction_code ,1,2) =
1090 substr(puar.jurisdiction_code,1,2)
1091 and ASG.assignment_id = puar.assignment_id
1092 and puar.tax_unit_id = hoi_pr.organization_id
1093 and SR.state_code = l_state)
1094 /* there shouldn't be any dependency on state tax rules
1095 AND EXISTS (select 'x'
1096 from hr_organization_information HOI
1097 where hoi.organization_id = hoi_pr.organization_id
1098 AND HOI.org_information_context = 'State Tax Rules'
1099 AND HOI.org_information1 = l_state
1100 AND NVL(HOI.org_information16, 'No') = 'No'
1101 AND NVL(HOI.org_information20, 'No') = 'No') */
1102 ORDER BY 1, 3, 4 DESC, 2;
1103
1104 -- Added for Bug# 4349864
1105 -- Used when RANGE_PERSON_ID functionality is available
1106
1107 CURSOR c_state_pr_person_on IS
1108 SELECT ASG.person_id person_id,
1109 ASG.assignment_id assignment_id,
1110 paa.tax_unit_id tax_unit_id,
1111 ppa.effective_date effective_end_date
1112 FROM per_all_assignments_f ASG,
1113 pay_assignment_actions paa,
1114 pay_payroll_actions ppa,
1115 hr_organization_information HOI_PR,
1116 pay_population_ranges ppr
1117 WHERE ppa.effective_date between l_period_start and l_period_end
1118 AND ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1119 AND paa.payroll_action_id = ppa.payroll_action_id
1120 AND hoi_pr.organization_id = paa.tax_unit_id
1121 AND HOI_pr.org_information_context = 'W2 Reporting Rules'
1122 AND NVL(HOI_pr.org_information16, 'A') = 'P'
1123 AND paa.assignment_id = ASG.assignment_id
1124 /*added to ignore skipped assignment */
1125 and paa.action_status <> 'S'
1126 -- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
1127 /* Added for Performance, 01-JUL-2003 */
1128 and asg.effective_end_date >= l_period_start
1129 and asg.effective_start_date <= l_period_end
1130
1131 AND ASG.business_group_id + 0 = l_bus_group_id
1132 --AND ASG.person_id between stperson and endperson
1133 and ppr.payroll_action_id = pactid
1134 and ppr.chunk_number = chunk
1135 and ppr.person_id = ASG.person_id
1136 and ASG.assignment_type = 'E'
1137 AND EXISTS (select 'x'
1138 from pay_us_asg_reporting puar,
1139 pay_state_rules SR
1140 where substr(SR.jurisdiction_code ,1,2) =
1141 substr(puar.jurisdiction_code,1,2)
1142 and ASG.assignment_id = puar.assignment_id
1143 and puar.tax_unit_id = hoi_pr.organization_id
1144 and SR.state_code = l_state)
1145 /* there shouldn't be any dependency on state tax rules
1146 AND EXISTS (select 'x'
1147 from hr_organization_information HOI
1148 where hoi.organization_id = hoi_pr.organization_id
1149 AND HOI.org_information_context = 'State Tax Rules'
1150 AND HOI.org_information1 = l_state
1151 AND NVL(HOI.org_information16, 'No') = 'No'
1152 AND NVL(HOI.org_information20, 'No') = 'No') */
1153 ORDER BY 1, 3, 4 DESC, 2;
1154
1155 /* California Multi Wage Plan Requirement */
1156
1157 CURSOR c_chk_gre_wp (p_tax_unit_id number) IS
1158 SELECT count(*) ct
1159 FROM hr_organization_information
1160 WHERE organization_id = p_tax_unit_id
1161 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
1162 AND org_information1 = 'CA'
1163 AND org_information4 = 'Y';
1164
1165 CURSOR c_chk_asg_wp (p_assignment_id number) IS
1166 SELECT count(*) ct
1167 FROM per_assignment_extra_info paei
1168 WHERE paei.assignment_id = p_assignment_id
1169 AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
1170 AND paei.aei_information1 = 'CA'
1171 AND paei.aei_information3 IS NOT NULL;
1172
1173
1174 --
1175 begin
1176 hr_utility.trace('Entered action creation');
1177
1178 /* added initalization for l_prev_tax_unit_id */
1179
1180 l_prev_tax_unit_id := -99999999;
1181
1182 /* Return details used to control the selection of people to report on ie.
1183 the SQL statement to run, the period over which to look for the people,
1184 how to group the people, etc... */
1185
1186 select effective_date,
1187 report_type,
1188 report_qualifier,
1189 report_category,
1190 business_group_id
1191 into l_effective_date,
1192 l_report_type,
1193 l_state,
1194 l_report_cat,
1195 l_bus_group_id
1196 from pay_payroll_actions
1197 where payroll_action_id = pactid;
1198
1199
1200 hr_utility.set_location ('actio_creation',1);
1201 --
1202 get_dates(l_report_type,
1203 l_effective_date,
1204 l_period_end,
1205 l_quarter_start,
1206 l_quarter_end,
1207 l_year_start,
1208 l_year_end);
1209
1210 hr_utility.set_location ('actio_creation',2);
1211 --
1212 get_selection_information
1213 (l_report_type,
1214 l_state,
1215 l_quarter_start,
1216 l_quarter_end,
1217 l_year_start,
1218 l_year_end,
1219 l_period_start,
1220 l_period_end,
1221 l_defined_balance_id,
1222 l_group_by_gre,
1223 l_group_by_medicare,
1224 l_tax_unit_context,
1225 l_jurisdiction_context);
1226
1227 hr_utility.set_location ('actio_creation',3);
1228
1229 --
1230 -- Get the jurisdiction code for the state if appropriate.
1231 --
1232 if l_jurisdiction_context then
1233 l_jurisdiction_code := lookup_jurisdiction_code(l_state);
1234 end if;
1235 -- Check for the Range Person ID Functionality
1236
1237 /* Initializing variable */
1238 l_person_on := FALSE ; --4349864
1239
1240 Begin
1241 select report_format
1242 into l_report_format
1243 from pay_report_format_mappings_f
1244 where report_type = l_report_type
1245 and report_qualifier = l_state
1246 and report_category = l_report_cat ;
1247 Exception
1248 When Others Then
1249 l_report_format := Null ;
1250 End ;
1251
1252 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
1253 p_report_format => l_report_format,
1254 p_report_qualifier => l_state,
1255 p_report_category => l_report_cat) ;
1256
1257 --
1258 -- Open up a cursor for processing a SQL statement.
1259 --
1260 if (l_state = 'LA' and l_report_cat = 'RTLAQ') then
1261 if l_person_on then
1262 OPEN c_state_la_quality_person_on ;
1263 else
1264 OPEN c_state_la_quality;
1265 end if ;
1266 elsif (l_state = 'CT' and l_report_cat = 'RTCTN') then
1267 if l_person_on then
1268 OPEN c_ct_non_profit_person_on ;
1269 else
1270 OPEN c_ct_non_profit;
1271 end if ;
1272 elsif (l_state = 'PR') THEN
1273 if l_person_on then
1274 OPEN c_state_pr_person_on ;
1275 else
1276 OPEN c_state_pr;
1277 end if ;
1278 else
1279 if l_person_on then
1280 OPEN c_state_person_on ;
1281 else
1282 OPEN c_state;
1283 end if ;
1284 end if;
1285
1286 --
1287 -- Loop for all rows returned for SQL statement.
1288 --
1289
1290 LOOP
1291 if (l_state = 'LA' and l_report_cat = 'RTLAQ') then
1292 hr_utility.set_location ('actio_creation',4);
1293 if l_person_on then
1294 FETCH c_state_la_quality_person_on INTO l_person_id,
1295 l_assignment_id,
1296 l_tax_unit_id,
1297 l_effective_end_date;
1298 EXIT WHEN c_state_la_quality_person_on%NOTFOUND;
1299 else
1300 FETCH c_state_la_quality INTO l_person_id,
1301 l_assignment_id,
1302 l_tax_unit_id,
1303 l_effective_end_date;
1304 EXIT WHEN c_state_la_quality%NOTFOUND;
1305 end if ;
1306
1307 elsif (l_state = 'CT' and l_report_cat = 'RTCTN') then
1308 hr_utility.set_location ('actio_creation',4);
1309 if l_person_on then
1310 FETCH c_ct_non_profit_person_on INTO l_person_id,
1311 l_assignment_id,
1312 l_tax_unit_id,
1313 l_effective_end_date;
1314 EXIT WHEN c_ct_non_profit_person_on%NOTFOUND;
1315 else
1316 FETCH c_ct_non_profit INTO l_person_id,
1317 l_assignment_id,
1318 l_tax_unit_id,
1319 l_effective_end_date;
1320 EXIT WHEN c_ct_non_profit%NOTFOUND;
1321 end if ;
1322
1323 elsif (l_state = 'PR') THEN
1324 hr_utility.set_location ('actio_creation',4);
1325 if l_person_on then
1326 FETCH c_state_pr_person_on INTO l_person_id,
1327 l_assignment_id,
1328 l_tax_unit_id,
1329 l_effective_end_date;
1330 EXIT WHEN c_state_pr_person_on%NOTFOUND;
1331 else
1332 FETCH c_state_pr INTO l_person_id,
1333 l_assignment_id,
1334 l_tax_unit_id,
1335 l_effective_end_date;
1336 EXIT WHEN c_state_pr%NOTFOUND;
1337 end if ;
1338
1339 else
1340 hr_utility.set_location ('actio_creation',5);
1341 -- If it is on then fetch from c_state_person_on cursor else c_state
1342 if l_person_on then
1343 FETCH c_state_person_on INTO l_person_id,
1344 l_assignment_id,
1345 l_tax_unit_id,
1346 l_effective_end_date;
1347 EXIT WHEN c_state_person_on%NOTFOUND;
1348 else
1349 FETCH c_state INTO l_person_id,
1350 l_assignment_id,
1351 l_tax_unit_id,
1352 l_effective_end_date;
1353 --l_1099R_ind;
1354 EXIT WHEN c_state%NOTFOUND;
1355 end If ;
1356
1357 end if;
1358
1359 --
1360 -- If the new row is the same as the previous row according to the way
1361 -- the rows are grouped then discard the row ie. grouping by GRE
1362 -- requires a single row for each person / GRE combination.
1363 --
1364 if (l_group_by_gre and
1365 l_person_id = l_prev_person_id and
1366 l_tax_unit_id = l_prev_tax_unit_id
1367 ) then
1368 --
1369 -- Do nothing.
1370 --
1371 null;
1372 --
1373 -- Have a new unique row according to the way the rows are grouped.
1374 -- The inclusion of the person is dependent on having a non zero
1375 -- balance.
1376 -- If the balance is non zero then an assignment action is created to
1377 -- indicate their inclusion in the magnetic tape report.
1378 --
1379 else
1380 hr_utility.set_location ('actio_creation',6);
1381 --
1382 -- Set up contexts required to test the balance.
1383 --
1384 -- Set up TAX_UNIT_ID context if appropriate.
1385 --
1386 if l_tax_unit_context then
1387 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1388 end if;
1389 --
1390 -- Set up JURISDICTION_CODE context if appropriate.
1391 --
1392 if l_jurisdiction_context then
1393 pay_balance_pkg.set_context('JURISDICTION_CODE',l_jurisdiction_code);
1394 end if;
1395 --
1396 --
1397 -- Check the balance.
1398 --
1399
1400 BEGIN
1401 SELECT pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
1402 INTO l_transfer_gre_id
1403 FROM pay_payroll_actions
1404 WHERE payroll_action_id = pactid;
1405 EXCEPTION
1406 WHEN no_data_found then
1407 l_transfer_gre_id := NULL;
1408 END;
1409
1410 if (l_tax_unit_id <> l_prev_tax_unit_id)
1411 then
1412 begin
1413 select 'Y'
1414 into l_1099R_ind
1415 from hr_organization_information
1416 where organization_id = l_tax_unit_id
1417 and org_information_context = '1099R Magnetic Report Rules';
1418 exception
1419 when no_data_found then
1420 l_1099R_ind := null;
1421 end;
1422 end if;
1423 if (nvl(l_1099R_ind, 'N') <> 'Y') and ((l_tax_unit_id = l_transfer_gre_id) or (l_transfer_gre_id is null)) then
1424 l_value := pay_balance_pkg.get_value
1425 (l_defined_balance_id,
1426 l_assignment_id,
1427 least(l_period_end,l_effective_end_date));
1428 --4310812
1429 If l_state = 'ME' Then
1430 l_value_sit := pay_balance_pkg.get_value(bal_db_item('SIT_SUBJ_WHABLE_PER_JD_GRE_QTD') ,
1431 l_assignment_id,
1432 least(l_period_end, l_effective_end_date)) ;
1433
1434 If nvl(l_value,0) <> 0 and nvl(l_value_sit,0) <> 0 Then
1435 l_value := greatest(l_value,l_value_sit) ;
1436 Elsif nvl(l_value,0) = 0 and nvl(l_value_sit,0) <> 0 Then
1437 l_value := l_value_sit ;
1438 End If ;
1439 End If ; -- end check for ME Non 1099R GRE
1440
1441
1442 if (l_value = 0 AND l_state = 'NY') then /*Check for NY Bug:2922028*/
1443
1444 hr_utility.trace('Entered NY Checking ');
1445
1446 if instr(to_char(l_quarter_end,'MM'), '12') <> 0 then /*Check for Last Quarter*/
1447
1448 hr_utility.trace('Last Quarter.Check the values for SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD');
1449
1450 l_value := pay_balance_pkg.get_value
1451 (bal_db_item('SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD'),
1452 l_assignment_id,
1453 least(l_period_end,l_effective_end_date));
1454
1455 hr_utility.trace('Value of SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD = '||l_value);
1456
1457 if l_value = 0 then /*Check for SUBJ_WHABLE*/
1458
1459 hr_utility.trace('Value of SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD is 0');
1460
1461 If l_effective_end_date < l_quarter_start THEN /*l_effective_end_date checking */
1462 /*Bug:3281209*/
1463
1464 hr_utility.trace('l_effective_end_date < l_quarter_start');
1465 l_value := 0;
1466
1467 ELSE
1468
1469 l_value := pay_balance_pkg.get_value
1470 (bal_db_item('SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'),
1471 l_assignment_id,
1472 least(l_period_end,l_effective_end_date));
1473
1474 hr_utility.trace('Value of SUI_ER_SUBJ_WHABLE_PER = '||l_value);
1475
1476 End if; /*l_effective_end_date checking */
1477
1478 end if; /*Check for SUBJ_WHABLE*/
1479
1480 end if; /*Check for Last Quarter*/
1481
1482 end if; /*Check for NY*/
1483
1484 else
1485 l_value := 0;
1486 end if;
1487
1488 hr_utility.trace('l_value = ' || to_char(l_value));
1489 hr_utility.trace('l_assignment_id = ' || to_char(l_assignment_id));
1490 hr_utility.trace('l_period_start = ' || l_period_start);
1491 hr_utility.trace('l_quarter_start = ' || l_quarter_start);
1492 hr_utility.trace('l_period_end = ' || l_period_end);
1493 hr_utility.trace('l_state = ' || l_state);
1494 hr_utility.trace('l_effective_end_date = ' || l_effective_end_date);
1495 hr_utility.trace('l_1099R_ind = ' || l_1099R_ind);
1496
1497 if ((l_value <> 0) OR
1498 report_person_on_tape(l_assignment_id, l_period_start,
1499 l_period_end, l_state, l_effective_end_date, l_1099R_ind, l_report_type)) then
1500 hr_utility.set_location ('actio_creation',7);
1501 --
1502 -- Have found a person that needs to be reported in the federal W2 so
1503 -- need to create an assignment action for it.
1504
1505
1506 -- California Multi Wage Plan requirement
1507 -- Check if the state is CA and Asg has a wage plan
1508 -- defined or it can default to the Wage Plan defined
1509 -- at the GRE level. Other wise error out.
1510
1511
1512 IF l_state = 'CA' AND ((l_tax_unit_id = l_transfer_gre_id) OR (l_transfer_gre_id is null)) THEN
1513
1514 -- Check if the GRE has a wage Plan defined
1515
1516 l_gre_wage_plan_exist := TRUE;
1517
1518 FOR c_rec IN c_chk_gre_wp (l_tax_unit_id)
1519 LOOP
1520
1521 IF c_rec.ct = 0 THEN
1522 l_gre_wage_plan_exist := FALSE;
1523 END IF;
1524
1525 END LOOP;
1526
1527 IF l_gre_wage_plan_exist = FALSE THEN
1528
1529 FOR c_rec IN c_chk_asg_wp (l_assignment_id)
1530 LOOP
1531 IF c_rec.ct = 0 THEN
1532 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
1533 hr_utility.set_message_token('FORMAT',' wage plan not defined at GRE');
1534 hr_utility.raise_error;
1535 END IF;
1536 END LOOP;
1537
1538 END IF;
1539
1540 END IF;
1541 --
1542 -- If the payroll action has not been created yet i.e. this is the
1543 -- first assignment action then create it.
1544 --
1545 --
1546 -- Create the assignment action to represnt the person / tax unit
1547 -- combination.
1548 --
1549 select pay_assignment_actions_s.nextval
1550 into lockingactid
1551 from dual;
1552 --
1553
1554 SELECT pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
1555 INTO l_transfer_gre_id
1556 FROM pay_payroll_actions
1557 WHERE payroll_action_id = pactid;
1558
1559 hr_utility.set_location ('actio_creation',8);
1560 -- insert into pay_assignment_actions.
1561 /* Bug: 10649380 : Create Assignment action only if GRE is equal to the
1562 Tranfer GRE parameter which is getting passed through the new SQWL process
1563 State Quarterly Wage Listing (Enhanced) */
1564 IF l_transfer_gre_id is null OR l_transfer_gre_id = l_tax_unit_id THEN
1565
1566 hr_utility.set_location ('actio_creation',9);
1567 hr_nonrun_asact.insact(lockingactid,l_assignment_id,
1568 pactid,chunk,l_tax_unit_id);
1569 hr_utility.set_location ('actio_creation',10);
1570 IF l_value= 0 THEN /*bug 14456648 */
1571 UPDATE pay_assignment_actions
1572 SET SERIAL_NUMBER = 'X'
1573 WHERE assignment_action_id = lockingactid;
1574 END IF;
1575
1576 archive_asg_locs( lockingactid, pactid, l_assignment_id);
1577 END IF;
1578
1579
1580 end if;
1581 end if;
1582 --
1583 -- Record the current values for the next time around the loop.
1584 --
1585 l_prev_person_id := l_person_id;
1586 l_prev_asg_id := l_assignment_id;
1587 l_prev_tax_unit_id := l_tax_unit_id;
1588 --
1589 END LOOP;
1590 end action_creation;
1591
1592 /* Name : archinit
1593 Purpose : This performs the US specific initialisation section.
1594 Arguments :
1595 Notes :
1596 */
1597
1598
1599 procedure archinit(p_payroll_action_id in number) is
1600 jurisdiction_code pay_state_rules.jurisdiction_code%TYPE;
1601 l_state VARCHAR2(30);
1602 l_report_cat pay_report_format_mappings_f.report_category%TYPE;
1603
1604 /* Bug 773937 */
1605 l_archive boolean:= FALSE;
1606
1607 cursor c_get_min_chunk is
1608 select min(paa.chunk_number)
1609 from pay_assignment_actions paa
1610 where paa.payroll_action_id = p_payroll_action_id;
1611
1612 /* End of Bug 773937 */
1613
1614 begin
1615 hr_utility.set_location ('archinit',1);
1616
1617 -- Derive state
1618 select report_qualifier,
1619 report_category
1620 into l_state,
1621 l_report_cat
1622 from pay_payroll_actions
1623 where payroll_action_id = p_payroll_action_id;
1624
1625 /* Added the select of report_category field in the above SQL on
1626 10-FEB-2000 by Ashu Gupta (ashgupta) */
1627
1628 hr_utility.set_location ('archinit',2);
1629
1630 -- Get jurisdiction code and store as a context
1631 IF l_state <> 'FED' THEN
1632 SELECT sr.jurisdiction_code
1633 INTO jurisdiction_code
1634 FROM pay_state_rules sr
1635 WHERE sr.state_code = l_state;
1636
1637 /* Bug 976472 */
1638 g_sqwl_state := l_state;
1639 g_sqwl_jursd := jurisdiction_code;
1640 /* End Bug 976472 */
1641 g_report_cat := l_report_cat;
1642
1643 /* Added the g_report_cat variable on 10-FEB-2000 by Ashu Gupta (ashgupta).
1644 This variable will be used in archive_data procedure to decide the report
1645 category */
1646
1647 pay_balance_pkg.set_context ('JURISDICTION_CODE',jurisdiction_code);
1648 END IF;
1649 /* Bug 773937 */
1650 hr_utility.set_location ('archinit getting min chunk number',10);
1651 open c_get_min_chunk;
1652 fetch c_get_min_chunk into g_min_chunk;
1653 if c_get_min_chunk%NOTFOUND then
1654 g_min_chunk := -1;
1655 hr_utility.set_location ('archinit min chunk is -1',11);
1656 raise hr_utility.hr_error;
1657 end if;
1658 close c_get_min_chunk;
1659
1660 /* Check if GRE level data has been archived or not and set the g_archive_flag to Y or N*/
1661 l_archive := chk_gre_archive(p_payroll_action_id);
1662
1663 /* END of Bug 773937 */
1664
1665 exception
1666 when others then
1667 raise;
1668 end archinit;
1669
1670 /* Name : eoy_get_jursd_level
1671 Purpose : This returns the jurisdiction level of the non balance
1672 database items.
1673 Arguments :
1674 Notes :
1675 */
1676
1677 function eoy_get_jursd_level(p_route_id number,
1678 p_user_entity_id number) return number is
1679 l_jursd_value number:= 0;
1680
1681 begin
1682
1683 select frpv.value
1684 into l_jursd_value
1685 from ff_route_parameter_values frpv,
1686 ff_route_parameters frp
1687 where frpv.route_parameter_id = frp.route_parameter_id
1688 and frpv.user_entity_id = p_user_entity_id
1689 and frp.route_id = p_route_id
1690 and frp.parameter_name = 'Jursd. Level';
1691
1692 return(l_jursd_value);
1693
1694 exception
1695 when no_data_found then
1696 return(0);
1697 when others then
1698 hr_utility.trace('Error while getting the jursd. value ' ||
1699 to_char(sqlcode));
1700
1701 end eoy_get_jursd_level;
1702
1703
1704
1705 procedure create_archive (p_user_entity_id in number,
1706 p_context1 in number,
1707 p_value in varchar2,
1708 p_sequence in pay_us_sqwl_archive.number_data_type_table,
1709 p_context in pay_us_sqwl_archive.char240_data_type_table,
1710 p_context_id in pay_us_sqwl_archive.number_data_type_table) is
1711 l_step number := 0;
1712
1713 begin
1714
1715 l_step := 1;
1716
1717 insert into ff_archive_items
1718 (ARCHIVE_ITEM_ID,
1719 USER_ENTITY_ID,
1720 CONTEXT1,
1721 VALUE,
1722 ARCHIVE_TYPE)
1723 values
1724 (ff_archive_items_s.nextval,
1725 p_user_entity_id,
1726 p_context1,
1727 p_value,
1728 'PPA'); /* Bug:2965887 */
1729
1730 l_step := 2;
1731
1732 for i in p_sequence.first .. p_sequence.last
1733 loop
1734 insert into ff_archive_item_contexts
1735 (ARCHIVE_ITEM_ID,
1736 SEQUENCE_NO,
1737 CONTEXT,
1738 CONTEXT_ID)
1739 values
1740 (ff_archive_items_s.currval,
1741 p_sequence(i),
1742 p_context(i),
1743 p_context_id(i));
1744 end loop;
1745
1746 exception
1747 when others then
1748 if l_step = 1 then
1749 hr_utility.trace('Error while inserting into ff_archive_items'
1750 || to_char(sqlcode));
1751 raise hr_utility.hr_error;
1752
1753 elsif l_step = 2 then
1754 hr_utility.trace('Error while inserting into ff_archive_item_contexts'
1755 || to_char(sqlcode));
1756 raise hr_utility.hr_error;
1757
1758 end if;
1759
1760 end create_archive;
1761
1762 /* Bug 773937 */
1763
1764 /* Name : archive_gre_data
1765 Purpose : This performs the US specific employer data archiving.
1766 Arguments :
1767 Notes :
1768 */
1769
1770 procedure archive_gre_data(p_payroll_action_id in number,
1771 p_tax_unit_id in number)
1772 is
1773
1774 l_user_entity_id number;
1775 l_tax_context_id number;
1776 l_jursd_context_id number;
1777 l_value varchar2(240);
1778 l_seq_tab pay_us_sqwl_archive.number_data_type_table;
1779 l_context_id_tab pay_us_sqwl_archive.number_data_type_table;
1780 l_context_val_tab pay_us_sqwl_archive.char240_data_type_table;
1781 l_arch_gre_step number := 0;
1782
1783 l_state_code pay_us_states.state_code%type;
1784
1785 l_from number;
1786 l_to number;
1787 l_length number;
1788
1789 begin
1790
1791 /* Get the context_id for 'TAX_UNIT_ID' */
1792
1793 l_arch_gre_step := 10;
1794
1795 select context_id
1796 into l_tax_context_id
1797 from ff_contexts
1798 where context_name = 'TAX_UNIT_ID';
1799
1800 /* Get the context_id for 'JURISDICTION_CODE' */
1801
1802 l_arch_gre_step := 20;
1803
1804 select context_id
1805 into l_jursd_context_id
1806 from ff_contexts
1807 where context_name = 'JURISDICTION_CODE';
1808
1809
1810 /* get the state code for the state abbrev */
1811 /* Start Position of State */
1812 select INSTR(legislative_parameters,'TRANSFER_STATE=')
1813 + LENGTH('TRANSFER_STATE=')
1814 into l_from
1815 from pay_payroll_actions
1816 where payroll_action_id = p_payroll_action_id;
1817
1818
1819 /* End position of state in legislative parameters */
1820 select INSTR(legislative_parameters,'TRANSFER_REPORTING_YEAR=')
1821 into l_to
1822 from pay_payroll_actions
1823 where payroll_action_id = p_payroll_action_id;
1824
1825 l_length := l_to - l_from - 1 ;
1826
1827 Select state_code
1828 into l_state_code
1829 from pay_us_states
1830 where state_abbrev = (
1831 select substr(legislative_parameters,l_from, l_length )
1832 from pay_payroll_actions
1833 where payroll_action_id = p_payroll_action_id);
1834
1835 /* Archive the Taxable wage Base */
1836
1837 l_user_entity_id := get_user_entity_id('A_SUI_TAXABLE_WAGE_BASE');
1838
1839 l_arch_gre_step := 21;
1840
1841 begin
1842 select to_char(sti.sui_er_wage_limit)
1843 into l_value
1844 from pay_us_state_tax_info_f sti,
1845 pay_payroll_actions ppa
1846 where ppa.payroll_action_id = p_payroll_action_id
1847 and sti.state_code = l_state_code
1848 and ppa.effective_date between sti.effective_start_date
1849 and sti.effective_end_date
1850 and sti.sta_information_category = 'State tax limit rate info';
1851
1852 exception
1853 when no_data_found then
1854 l_value := null;
1855 end;
1856
1857 /* Initialise the PL/SQL tables */
1858 l_arch_gre_step := 22;
1859
1860 l_seq_tab.delete;
1861 l_context_id_tab.delete;
1862 l_context_val_tab.delete;
1863
1864 /* Assign value to PL/SQL tables */
1865
1866 l_arch_gre_step := 23;
1867
1868 l_seq_tab(1) := 1;
1869 l_context_id_tab(1) := l_tax_context_id;
1870 l_context_val_tab(1) := to_char(p_tax_unit_id);
1871 l_seq_tab(2) := 2;
1872 l_context_id_tab(2) := l_jursd_context_id;
1873 l_context_val_tab(2) := l_state_code || '-000-0000';
1874
1875 l_arch_gre_step := 24;
1876
1877 create_archive (p_user_entity_id => l_user_entity_id,
1878 p_context1 => p_payroll_action_id,
1879 p_value => l_value,
1880 p_sequence => l_seq_tab,
1881 p_context => l_context_val_tab,
1882 p_context_id => l_context_id_tab);
1883
1884 g_archive_flag := 'Y';
1885 exception
1886 when others then
1887 g_archive_flag := 'N';
1888
1889 end archive_gre_data;
1890 /* End of Bug 773937 */
1891
1892
1893 /* Name : chk_gre_archive
1894 Purpose : Function to check if the employer level data has been archived
1895 or not.
1896 Arguments :
1897 Notes :
1898 */
1899
1900 function chk_gre_archive (p_payroll_action_id number) return boolean is
1901
1902 l_flag varchar2(1);
1903
1904 cursor c_chk_payroll_action is
1905 select 'Y'
1906 from dual
1907 where exists (select null
1908 from ff_archive_items fai
1909 where fai.context1 = p_payroll_action_id
1910 and archive_type = 'PPA'); /* Bug:2965887 */
1911 begin
1912
1913 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1914
1915 if g_archive_flag = 'Y' then
1916 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1917 return (TRUE);
1918 else
1919
1920 hr_utility.trace('chk_gre_archive - opening cursor');
1921
1922 open c_chk_payroll_action;
1923 fetch c_chk_payroll_action into l_flag;
1924 if c_chk_payroll_action%FOUND then
1925 hr_utility.trace('chk_gre_archive - found in cursor');
1926 g_archive_flag := 'Y';
1927 else
1928 hr_utility.trace('chk_gre_archive - not found in cursor');
1929 g_archive_flag := 'N';
1930 end if;
1931
1932 hr_utility.trace('chk_gre_archive - closing cursor');
1933 close c_chk_payroll_action;
1934 if g_archive_flag = 'Y' then
1935 hr_utility.trace('chk_gre_archive - returning true');
1936 return (TRUE);
1937 else
1938 hr_utility.trace('chk_gre_archive - returning false');
1939 return(FALSE);
1940 end if;
1941 end if;
1942 end chk_gre_archive;
1943
1944
1945 /* Name : archive_data
1946 Purpose : This performs the US specific employee context setting for the SQWL
1947 report.
1948 Arguments :
1949 Notes :
1950 */
1951
1952 procedure archive_data(p_assactid in number, p_effective_date in date) is
1953
1954 aaid pay_assignment_actions.assignment_action_id%type;
1955 aaseq pay_assignment_actions.action_sequence%type;
1956 asgid pay_assignment_actions.assignment_id%type;
1957 date_earned date;
1958 eff_date date;
1959 l_year_start date;
1960 l_year_end date;
1961 taxunitid pay_assignment_actions.tax_unit_id%type;
1962 l_period_start date;
1963 l_period_end date;
1964
1965 /* Bug 773937 */
1966 l_chunk number;
1967 l_payroll_action_id number;
1968 /* End of Bug 773937 */
1969
1970 /* The following variables were added on 08-FEB-2000 by Ashu Gupta(ashgupta) to
1971 take care of archiving of Wage Plan Codes in California */
1972
1973 l_user_entity_id NUMBER;
1974 l_context_id_assignment_id NUMBER;
1975 l_quarter_start DATE ;
1976 l_quarter_end DATE ;
1977 l_wage_plan_code per_assignment_extra_info.aei_information3%TYPE;
1978 l_assignment_id NUMBER;
1979
1980 l_wage_plan_ct NUMBER := 0; -- Added by tmehra
1981
1982 /* Bug 976472 */
1983
1984 l_jurisdiction varchar2(11);
1985 l_count number := 0;
1986 l_context_no number := 0;
1987 l_temp_var number := 0;
1988
1989 /* Get the jurisdiction code of all the cities
1990 for the person_id corresponding to the
1991 assignment_id */
1992
1993 cursor c_get_city is
1994 select distinct pcty.jurisdiction_code pcty
1995 from pay_us_emp_city_tax_rules_f pcty,
1996 per_assignments_f paf1,
1997 per_assignments_f paf
1998 where paf.assignment_id = asgid
1999 and paf.effective_end_date >= l_year_start
2000 and paf.effective_start_date <= l_year_end
2001 and paf1.person_id = paf.person_id
2002 and paf1.effective_end_date >= l_year_start
2003 and paf1.effective_start_date <= l_year_end
2004 and pcty.assignment_id = paf1.assignment_id
2005 and pcty.effective_start_date <= l_year_end
2006 and pcty.effective_end_date >= l_year_start
2007 and pcty.jurisdiction_code in ('33-005-2010',
2008 '33-047-2010',
2009 '33-061-2010',
2010 '33-081-2010',
2011 '33-085-2010',
2012 '33-119-3230');
2013 /* End Bug 976472 */
2014
2015 /* Added by Ashu on 07-FEB-2000 to archive the Wage Plan Codes.
2016 A_SCL_US_ASG_CA_WAGE_PLAN_CODE is no longer present in
2017 ICESA_SUPPLEMENTAL formula. Therefore archiver will not archive this
2018 database item. The following cursor is executed when the category is
2019 RTM in case of California. Enhancement Req 1063413 */
2020
2021
2022 -- CURSOR c_archive_wage_plan_code_rtm IS
2023 -- SELECT DISTINCT aei_information3 ,
2024 -- paf1.assignment_id
2025 -- FROM per_assignment_extra_info paei,
2026 -- pay_us_asg_reporting puar,
2027 -- pay_us_states pus ,
2028 -- per_assignments_f paf1,
2029 -- per_assignments_f paf
2030 -- WHERE paf.assignment_id = asgid
2031 -- AND date_earned BETWEEN paf.effective_start_date
2032 -- AND paf.effective_end_date
2033 -- AND paf1.person_id = paf.person_id
2034 -- AND paf1.effective_start_date <= l_quarter_end
2035 -- AND paf1.effective_end_date >= l_quarter_start
2036 -- AND pus.state_abbrev = g_sqwl_state
2037 -- AND puar.assignment_id = paf1.assignment_id
2038 -- AND puar.tax_unit_id = taxunitid
2039 -- AND substr(puar.jurisdiction_code,1,2) = pus.state_code
2040 -- AND paf1.assignment_id = paei.assignment_id
2041 -- AND paei.aei_information1 = g_sqwl_state
2042 -- AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
2043 -- AND EXISTS(
2044 -- SELECT NULL
2045 -- FROM pay_payroll_actions ppa1,
2046 -- pay_assignment_actions paa1,
2047 -- pay_us_asg_reporting puar1
2048 -- WHERE paa1.assignment_id = paf1.assignment_id
2049 -- AND ppa1.payroll_action_id = paa1.payroll_action_id
2050 -- AND puar1.assignment_id = paf1.assignment_id
2051 -- AND puar1.tax_unit_id = puar.tax_unit_id
2052 -- AND ppa1.action_type in ('R', 'Q', 'V', 'B', 'I')
2053 -- AND ppa1.effective_date BETWEEN l_quarter_start
2054 -- AND l_quarter_end
2055 -- AND ppa1.effective_date BETWEEN
2056 -- paf1.effective_start_date
2057 -- AND paf1.effective_end_date
2058 -- );
2059 --
2060 --
2061 --
2062 -- /* Added by Ashu on 10-FEB-2000 to archive the Wage Plan Codes.
2063 -- A_SCL_US_ASG_CA_WAGE_PLAN_CODE is no longer present in
2064 -- ICESA_SUPPLEMENTAL formula. Therefore archiver will not archive this
2065 -- database item. The following cursor is executed when the category is
2066 -- RTS in case of California. The need to have external join is to make
2067 -- sure that the people with No Wage Plan Code have record in
2068 -- ff_archive_tems table. This way these persons will be selected in
2069 -- sqwl_employee_s cursor . Enhancement Req 1063413 */
2070 --
2071 --
2072 -- CURSOR c_archive_wage_plan_code_rts IS
2073 -- SELECT DISTINCT aei_information3 ,
2074 -- paf1.assignment_id
2075 -- FROM per_assignment_extra_info paei,
2076 -- pay_us_asg_reporting puar,
2077 -- pay_us_states pus ,
2078 -- per_assignments_f paf1,
2079 -- per_assignments_f paf
2080 -- WHERE paf.assignment_id = asgid
2081 -- AND date_earned BETWEEN paf.effective_start_date
2082 -- AND paf.effective_end_date
2083 -- AND paf1.person_id = paf.person_id
2084 -- AND paf1.effective_start_date <= l_quarter_end
2085 -- AND paf1.effective_end_date >= l_quarter_start
2086 -- AND pus.state_abbrev = g_sqwl_state
2087 -- AND puar.assignment_id = paf1.assignment_id
2088 -- AND puar.tax_unit_id = taxunitid
2089 -- AND substr(puar.jurisdiction_code,1,2) = pus.state_code
2090 -- AND paf1.assignment_id = paei.assignment_id(+)
2091 -- AND paei.aei_information1(+) = g_sqwl_state
2092 -- AND paei.information_type(+) = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
2093 -- AND EXISTS(
2094 -- SELECT NULL
2095 -- FROM pay_payroll_actions ppa1,
2096 -- pay_assignment_actions paa1,
2097 -- pay_us_asg_reporting puar1
2098 -- WHERE paa1.assignment_id = paf1.assignment_id
2099 -- AND ppa1.payroll_action_id = paa1.payroll_action_id
2100 -- AND puar1.assignment_id = paf1.assignment_id
2101 -- AND puar1.tax_unit_id = puar.tax_unit_id
2102 -- AND ppa1.action_type in ('R', 'Q', 'V', 'B', 'I')
2103 -- AND ppa1.effective_date BETWEEN l_quarter_start
2104 -- AND l_quarter_end
2105 -- AND ppa1.effective_date BETWEEN
2106 -- paf1.effective_start_date
2107 -- AND paf1.effective_end_date
2108 -- );
2109
2110
2111
2112 /*
2113 Due to the performance issues raised by Internal/In-House the above two
2114 cursors have been replaced with the following by tmehra 18-OCT-2001
2115 */
2116
2117 CURSOR c_archive_wage_plan_code IS
2118 SELECT DISTINCT aei_information3
2119 FROM per_assignment_extra_info paei
2120 WHERE paei.assignment_id = asgid
2121 AND paei.aei_information1 = g_sqwl_state
2122 AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD';
2123
2124 -- The following cursor was added by tmehra on 07-MAY-2003
2125 -- This cursor get the default Wage Plan defined at the GRE level
2126 -- if the Asg level Wage Plan is missing.
2127
2128
2129 CURSOR c_gre_wage_plan_code IS
2130 SELECT hoi.org_information3 wage_plan
2131 FROM hr_organization_information hoi
2132 WHERE hoi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
2133 AND hoi.organization_id = taxunitid
2134 AND hoi.org_information1 = g_sqwl_state
2135 AND hoi.org_information4 = 'Y';
2136
2137 /* Get the latest assignment for the given assisignment_id ,person_id */
2138 /* Commented out and modified query for improving performance (bug 6774422)
2139 CURSOR c_get_latest_asg(p_assignment_id number ) IS
2140 select paa.assignment_action_id,
2141 ppa.effective_date
2142 from pay_assignment_actions paa,
2143 per_all_assignments_f paf,
2144 pay_payroll_actions ppa,
2145 pay_action_classifications pac,
2146 per_all_assignments_f paf1
2147 where paf1.assignment_id = p_assignment_id
2148 and paf.person_id = paf1.person_id
2149 and paa.assignment_id = paf.assignment_id
2150 and paa.tax_unit_id = taxunitid
2151 and paa.payroll_action_id = ppa.payroll_action_id
2152 and ppa.action_type = pac.action_type
2153 and pac.classification_name = 'SEQUENCED'
2154 and ppa.effective_date between paf.effective_start_date
2155 and paf.effective_end_date
2156 and ppa.effective_date between l_period_start and
2157 l_period_end
2158 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
2159 and paa.source_action_id is null)
2160 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
2161 and paa.source_action_id is not null )
2162 or (ppa.action_type = 'V' and ppa.run_type_id is null
2163 and paa.run_type_id is not null
2164 and paa.source_action_id is null))
2165 order by paa.action_sequence desc;
2166
2167 */
2168
2169 /* This is the modified new cursor (bug 6774422)**/
2170 CURSOR c_get_latest_asg(p_assignment_id number ) IS
2171 SELECT /*+ORDERED*/
2172 PAA.ASSIGNMENT_ACTION_ID,
2173 PPA.EFFECTIVE_DATE
2174 FROM PER_ALL_ASSIGNMENTS_F PAF1,
2175 PER_ALL_ASSIGNMENTS_F PAF ,
2176 PAY_ASSIGNMENT_ACTIONS PAA,
2177 PAY_PAYROLL_ACTIONS PPA ,
2178 PAY_ACTION_CLASSIFICATIONS PAC
2179 WHERE PAF1.ASSIGNMENT_ID = p_assignment_id
2180 AND PAF.PERSON_ID = PAF1.PERSON_ID
2181 AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
2182 AND PAA.TAX_UNIT_ID = taxunitid
2183 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
2184 AND PPA.ACTION_TYPE = PAC.ACTION_TYPE
2185 AND PAC.CLASSIFICATION_NAME = 'SEQUENCED'
2186 AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
2187 AND PPA.EFFECTIVE_DATE BETWEEN l_period_start AND l_period_end
2188 AND ((NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NULL
2189 AND PAA.SOURCE_ACTION_ID IS NULL)
2190 OR (NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NOT NULL
2191 AND PAA.SOURCE_ACTION_ID IS NOT NULL )
2192 OR (PPA.ACTION_TYPE = 'V'
2193 AND PPA.RUN_TYPE_ID IS NULL
2194 AND PAA.RUN_TYPE_ID IS NOT NULL
2195 AND PAA.SOURCE_ACTION_ID IS NULL))
2196 ORDER BY PAA.ACTION_SEQUENCE DESC;
2197
2198 /*Modified for Florida SQWL Bug#9356178*/
2199 /*For Florida SQWL starting from Q1 2010, the filing authority is validating the employee
2200 balances and is expecting the credit to be given for Out of State SUI taxable wages of the
2201 employee in Florida Taxable.But since the same requirement is not mandatory in taxation,
2202 the Florida Taxable is adjusted and the value is archived with the dbi
2203 SUI_ER_FL_ADJ_TAXABLE_PER_JD_GRE_QTD and this new value is used in SQWL reporting*/
2204
2205 CURSOR get_defined_balance_id IS
2206 select pdb.defined_balance_id,pbd.dimension_name
2207 from pay_balance_types pbt,
2208 pay_balance_dimensions pbd,
2209 pay_defined_balances pdb
2210 where pbt.legislation_code = 'US'
2211 and pbt.balance_name = 'SUI ER Taxable'
2212 and pbd.dimension_name in ('Person in JD within GRE Quarter to Date',
2213 'Person in JD within GRE Year to Date',
2214 'Person within Government Reporting Entity Year to Date')
2215 and pbd.legislation_code = 'US'
2216 and pdb.balance_type_id = pbt.balance_type_id
2217 and pdb.balance_dimension_id = pbd.balance_dimension_id;
2218
2219 CURSOR c_get_defined_balance_id_oh IS
2220 select pdb.defined_balance_id,pbd.dimension_name
2221 from pay_balance_types pbt,
2222 pay_balance_dimensions pbd,
2223 pay_defined_balances pdb
2224 where pbt.legislation_code = 'US'
2225 and pbt.balance_name = 'SUI ER Taxable'
2226 and pbd.dimension_name in ('Person in JD within GRE Quarter to Date',
2227 'Person within Government Reporting Entity Quarter to Date')
2228 and pbd.legislation_code = 'US'
2229 and pdb.balance_type_id = pbt.balance_type_id
2230 and pdb.balance_dimension_id = pbd.balance_dimension_id;
2231
2232 /*Modified to ensure correct behavior for all kinds of
2233 assignments.The earlier cursor had date checks which were
2234 not correct and also it gave duplicate results which can
2235 lead to wrong sum being calculated Correct this in below cursor*/
2236
2237 CURSOR get_previous_fl_taxable IS
2238 select sum(to_number(nvl(value,'0')))
2239 from ff_archive_items ffai,
2240 pay_assignment_actions paa,
2241 pay_payroll_actions ppa
2242 where ffai.user_entity_id = l_user_entity_id
2243 and ffai.context1=to_char(paa.assignment_action_id)
2244 and paa.tax_unit_id = taxunitid
2245 and paa.payroll_action_id = ppa.payroll_action_id
2246 and ppa.report_type = 'SQWL'
2247 and ppa.report_qualifier = 'FL'
2248 and ppa.action_type = 'X'
2249 and trunc(ppa.effective_date,'YEAR') = trunc(p_effective_date,'YEAR')
2250 and ppa.effective_date < p_effective_date
2251 and paa.assignment_id in
2252 (select distinct paaf2.assignment_id
2253 from per_all_assignments_f paaf1,
2254 per_all_assignments_f paaf2
2255 where paaf1.assignment_id = asgid
2256 and paaf1.person_id = paaf2.person_id
2257 and paaf2.effective_start_date <= p_effective_date);
2258
2259 /* Added for Bug#9561700*/
2260
2261 /*Since we are using the Date Based approach to fetch the Balances
2262 of the assignment, we need to ensure that on the Date we pass for the
2263 assignment, the Assignment record is present.Incase, the employee
2264 is terminated, we need to pass the last effective date applicable to the
2265 assignment to fetch the balances.This we do by referring to the pay_payroll_actions
2266 table to find the maximum effective_date of this person in this Quarter.*/
2267
2268 CURSOR get_effective_date (p_quarter_start_date DATE,
2269 p_quarter_end_date DATE) IS
2270 select max(ppa.effective_date)
2271 from per_all_assignments_f asg,
2272 pay_assignment_actions paa,
2273 pay_payroll_actions ppa
2274 where ppa.effective_date between p_quarter_start_date
2275 and p_quarter_end_date
2276 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
2277 and paa.payroll_action_id = ppa.payroll_action_id
2278 and paa.assignment_id = asg.assignment_id
2279 and paa.action_status <> 'S'
2280 and asg.effective_end_date >= p_quarter_start_date
2281 and asg.effective_start_date <= p_quarter_end_date
2282 and asg.business_group_id = ppa.business_group_id
2283 and paa.tax_unit_id = taxunitid
2284 and asg.assignment_id = asgid;
2285 /* End Bug#9561700*/
2286
2287 /* Added for Bug#14097843 starts here */
2288
2289 -- Cursor to get the Defined Balance IDs
2290 CURSOR c_get_defined_balance_id_ar IS
2291 select pdb.defined_balance_id, pbt.balance_name, pbd.dimension_name
2292 from pay_balance_types pbt,
2293 pay_balance_dimensions pbd,
2294 pay_defined_balances pdb
2295 where pbt.legislation_code = 'US'
2296 and (( pbt.balance_name = 'SUI ER Taxable'
2297 and pbd.dimension_name in ('Person in JD within GRE Year to Date',
2298 'Person within Government Reporting Entity Year to Date'))
2299 or ( pbt.balance_name in ('SUI ER Subj Whable', 'SUI ER Pre Tax Redns')
2300 and pbd.dimension_name in ('Person in JD within GRE Quarter to Date',
2301 'Person in JD within GRE Year to Date'))
2302 )
2303 and pbd.legislation_code = 'US'
2304 and pdb.balance_type_id = pbt.balance_type_id
2305 and pdb.balance_dimension_id = pbd.balance_dimension_id;
2306
2307 -- Cursor to get the sum of archived values in the Year for AR
2308 CURSOR get_previous_ar_value(l_user_entity_id IN NUMBER) IS
2309 select sum(to_number(nvl(value,'0')))
2310 from ff_archive_items ffai,
2311 pay_assignment_actions paa,
2312 pay_payroll_actions ppa
2313 where ffai.user_entity_id = l_user_entity_id
2314 and ffai.context1=to_char(paa.assignment_action_id)
2315 and paa.tax_unit_id = taxunitid
2316 and paa.payroll_action_id = ppa.payroll_action_id
2317 and ppa.report_type = 'SQWL'
2318 and ppa.report_qualifier = 'AR'
2319 and ppa.action_type = 'X'
2320 and trunc(ppa.effective_date,'YEAR') = trunc(p_effective_date,'YEAR')
2321 and ppa.effective_date < p_effective_date
2322 and paa.assignment_id in
2323 (select distinct paaf2.assignment_id
2324 from per_all_assignments_f paaf1,
2325 per_all_assignments_f paaf2
2326 where paaf1.assignment_id = asgid
2327 and paaf1.person_id = paaf2.person_id
2328 and paaf2.effective_start_date <= p_effective_date);
2329
2330 -- Cursor to get the SUI State Codes from fed_tax_rules
2331 /* Modified for Bug#14371049 */
2332 CURSOR get_ar_oos_state_code (l_assignment_id IN NUMBER,
2333 l_eff_start_date IN DATE,
2334 l_eff_end_date IN DATE,
2335 l_ar_state_abbrev IN VARCHAR2)
2336 IS
2337 SELECT pus.state_abbrev
2338 FROM pay_us_emp_fed_tax_rules_f pef,
2339 pay_us_states pus
2340 WHERE pef.sui_state_code = pus.state_code
2341 AND pus.state_abbrev <> l_ar_state_abbrev
2342 AND pef.effective_start_date <= l_eff_end_date
2343 AND pef.effective_end_date >= l_eff_start_date
2344 AND pef.assignment_id IN (SELECT paa2.assignment_id
2345 FROM per_all_assignments_f paa1,
2346 per_all_assignments_f paa2
2347 WHERE paa1.assignment_id = l_assignment_id
2348 AND paa1.person_id = paa2.person_id
2349 AND paa1.assignment_type = 'E'
2350 AND paa1.effective_start_date <= l_eff_end_date
2351 AND paa1.effective_end_date >= l_eff_start_date
2352 AND paa2.assignment_type = 'E'
2353 AND paa2.effective_start_date <= l_eff_end_date
2354 AND paa2.effective_end_date >= l_eff_start_date
2355 )
2356 ORDER BY pef.assignment_id, pef.effective_start_date;
2357
2358 -- Cursor to get the State Codes from state_tax_rules
2359 /* Modified for Bug#14371049 */
2360 CURSOR get_ar_oos_state_code_st (l_assignment_id IN NUMBER,
2361 l_eff_start_date IN DATE,
2362 l_eff_end_date IN DATE,
2363 l_ar_state_abbrev IN VARCHAR2)
2364 IS
2365 SELECT pus.state_abbrev
2366 FROM pay_us_emp_state_tax_rules_f pes,
2367 pay_us_states pus
2368 WHERE pes.state_code = pus.state_code
2369 AND pus.state_abbrev <> l_ar_state_abbrev
2370 AND pes.effective_start_date <= l_eff_end_date
2371 AND pes.effective_end_date >= l_eff_start_date
2372 AND pes.assignment_id IN (SELECT paa2.assignment_id
2373 FROM per_all_assignments_f paa1,
2374 per_all_assignments_f paa2
2375 WHERE paa1.assignment_id = l_assignment_id
2376 AND paa1.person_id = paa2.person_id
2377 AND paa1.assignment_type = 'E'
2378 AND paa1.effective_start_date <= l_eff_end_date
2379 AND paa1.effective_end_date >= l_eff_start_date
2380 AND paa2.assignment_type = 'E'
2381 AND paa2.effective_start_date <= l_eff_end_date
2382 AND paa2.effective_end_date >= l_eff_start_date
2383 )
2384 ORDER BY pes.assignment_id, pes.effective_start_date, pes.state_code;
2385
2386 /* Added for Bug#14097843 ends here */
2387
2388 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
2389 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
2390
2391 l_sui_fl_taxable_qtd number;
2392 l_sui_fl_taxable_ytd number;
2393 l_sui_taxable_ytd number;
2394 l_outstate_sui_taxable_ytd number;
2395 l_previous_fl_taxable number;
2396 l_fl_sui_er_limit number;
2397 l_context_id_tax_unit_id number;
2398 l_context_id_jurisdiction_code number;
2399 l_archive_item_id number;
2400 fl_jurisdiction_code varchar2(11);
2401 l_effective_date date;
2402 l_effective_end_date date;
2403 l_oh_jurisdiction_code varchar2(11);
2404 l_oh_sui_er_limit number;
2405 l_sui_total_taxable_qtd number;
2406 p_out_of_state_taxable number;
2407 l_sui_oh_taxable_qtd number;
2408
2409 /* End Bug#9356178*/
2410
2411 /* Added for Bug#14097843 starts here */
2412 l_balance_name pay_balance_types.balance_name%TYPE;
2413
2414 l_ar_jurisdiction_code VARCHAR2(11);
2415 l_ar_state_abbrev VARCHAR2(2);
2416 l_sui_ar_taxable_ytd NUMBER;
2417 l_sui_ar_total_taxable_ytd NUMBER;
2418 l_sui_ar_adj_taxable_qtd NUMBER;
2419 l_sui_ar_subj_whable_qtd NUMBER;
2420 l_sui_ar_subj_whable_ytd NUMBER;
2421 l_sui_ar_pre_tax_redn_qtd NUMBER;
2422 l_sui_ar_pre_tax_redn_ytd NUMBER;
2423 l_sui_ar_redsubj_whable_qtd NUMBER;
2424 l_sui_ar_redsubj_whable_ytd NUMBER;
2425 l_sui_ar_oos_rpt_qtd NUMBER;
2426 l_ar_sui_er_limit NUMBER;
2427 l_sui_ar_tax_user_ent_id NUMBER;
2428 l_sui_ar_oos_rpt_user_ent_id NUMBER;
2429 l_sui_ar_oos_stcd_user_ent_id NUMBER;
2430 l_prev_ar_oos_rpt NUMBER;
2431 l_sui_ar_oos_ytd NUMBER;
2432 l_total_oos_rpt_remaining NUMBER;
2433 l_sui_ar_taxable_qtd_rem NUMBER;
2434 l_amt_req_to_reach_ar_lmt NUMBER;
2435 l_sui_ar_oos_state_code VARCHAR2(2);
2436 /* Added for Bug#14097843 ends here */
2437
2438 begin
2439 hr_utility.set_location ('archive_data',1);
2440
2441 SELECT aa.assignment_id,
2442 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2443 aa.tax_unit_id,
2444 aa.chunk_number, /* Bug 773937 */
2445 aa.payroll_action_id /* Bug 773937 */
2446 into asgid,
2447 date_earned,
2448 taxunitid,
2449 l_chunk, /* Bug 773937 */
2450 l_payroll_action_id /* Bug 773937 */
2451 FROM pay_assignment_actions aa
2452 WHERE aa.assignment_action_id = p_assactid;
2453
2454 /*
2455 BUG# 12322280
2456 This archives the taxable wages for ohio after deducting
2457 the Out of State wages for each employee.
2458 The summed up value is used in the SQWL report for Ohio
2459 T55-68 record.
2460 */
2461
2462 IF (g_sqwl_state = 'OH') then
2463
2464 hr_utility.trace('Adjusting the Ohio Taxable');
2465 l_user_entity_id :=get_user_entity_id('A_SUI_OH_ER_ADJ_TAXABLE_ASG_GRE_QTD');
2466 l_oh_jurisdiction_code := '36-000-0000';
2467
2468 pay_balance_pkg.set_context('TAX_UNIT_ID',taxunitid);
2469 pay_balance_pkg.set_context('JURISDICTION_CODE',l_oh_jurisdiction_code);
2470
2471 SELECT least(max(effective_end_date),p_effective_date)
2472 INTO l_effective_end_date
2473 FROM per_all_assignments_f
2474 WHERE assignment_id = asgid
2475 AND assignment_type = 'E'
2476 AND effective_end_date >= add_months(last_day(p_effective_date),-3)+1 ; /*Quarter Start Date */
2477
2478 IF l_effective_end_date < p_effective_date
2479
2480 THEN
2481
2482 open get_effective_date(add_months(last_day(p_effective_date),-3)+1,last_day(p_effective_date));
2483 fetch get_effective_date into l_effective_date;
2484 close get_effective_date;
2485
2486 hr_utility.trace('Modified l_effective_date'||to_char(l_effective_date));
2487
2488 ELSE
2489
2490 l_effective_date := p_effective_date;
2491
2492 hr_utility.trace('Use original l_effective_date'||to_char(l_effective_date));
2493
2494 END IF;
2495
2496 open c_get_defined_balance_id_oh;
2497 fetch c_get_defined_balance_id_oh into l_def_bal_id,l_dimension_name;
2498
2499 while c_get_defined_balance_id_oh%FOUND
2500 loop
2501
2502 if l_dimension_name = 'Person in JD within GRE Quarter to Date' then
2503
2504
2505 l_sui_oh_taxable_qtd := pay_balance_pkg.get_value( l_def_bal_id,
2506 asgid,
2507 l_effective_date);
2508
2509 hr_utility.trace('l_sui_oh_taxable_qtd '||l_sui_oh_taxable_qtd);
2510 else
2511
2512
2513 l_sui_total_taxable_qtd :=pay_balance_pkg.get_value( l_def_bal_id,
2514 asgid,
2515 l_effective_date);
2516 hr_utility.trace('l_sui_total_taxable_qtd '||l_sui_total_taxable_qtd);
2517
2518 end if;
2519
2520 fetch c_get_defined_balance_id_oh into l_def_bal_id,l_dimension_name;
2521
2522 end loop;
2523 close c_get_defined_balance_id_oh;
2524
2525 p_out_of_state_taxable := l_sui_total_taxable_qtd - l_sui_oh_taxable_qtd;
2526 -- p_out_of_state_taxable will be equal to Out of State taxable
2527 l_oh_sui_er_limit := hr_us_ff_udf1.get_jit_data ( l_oh_jurisdiction_code,p_effective_date,'SUI_ER_WAGE_LIMIT');
2528
2529 p_out_of_state_taxable :=l_oh_sui_er_limit - least(p_out_of_state_taxable,l_oh_sui_er_limit);
2530
2531
2532 l_sui_oh_taxable_qtd := least(p_out_of_state_taxable,l_sui_oh_taxable_qtd);
2533
2534
2535 INSERT INTO ff_archive_items (archive_item_id,
2536 user_entity_id,
2537 context1,
2538 value)
2539 VALUES( ff_archive_items_s.NEXTVAL ,
2540 l_user_entity_id ,
2541 p_assactid ,
2542 l_sui_oh_taxable_qtd );
2543
2544 hr_utility.trace('Archived the adjusted OH Taxable');
2545
2546 SELECT context_id
2547 INTO l_context_id_tax_unit_id
2548 FROM ff_contexts
2549 WHERE context_name = 'TAX_UNIT_ID';
2550
2551 INSERT INTO ff_archive_item_contexts
2552 (archive_item_id,sequence_no,context,context_id)
2553 VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
2554
2555 SELECT context_id
2556 INTO l_context_id_jurisdiction_code
2557 FROM ff_contexts
2558 WHERE context_name = 'JURISDICTION_CODE';
2559
2560 INSERT INTO ff_archive_item_contexts
2561 (archive_item_id,sequence_no,context,context_id)
2562 VALUES (ff_archive_items_s.CURRVAL,1,l_oh_jurisdiction_code,l_context_id_jurisdiction_code);
2563
2564 /* Added for Bug# 14097843 Starts here */
2565 ELSIF (g_sqwl_state = 'AR') THEN
2566
2567 l_prev_ar_oos_rpt := 0;
2568 l_sui_ar_taxable_ytd := 0;
2569 l_sui_ar_total_taxable_ytd := 0;
2570 l_sui_ar_adj_taxable_qtd := 0;
2571 l_sui_ar_subj_whable_qtd := 0;
2572 l_sui_ar_subj_whable_ytd := 0;
2573 l_sui_ar_pre_tax_redn_qtd := 0;
2574 l_sui_ar_pre_tax_redn_ytd := 0;
2575 l_sui_ar_redsubj_whable_qtd := 0;
2576 l_sui_ar_redsubj_whable_ytd := 0;
2577 l_sui_ar_oos_rpt_qtd := 0;
2578 l_sui_ar_oos_ytd := 0;
2579 l_total_oos_rpt_remaining := 0;
2580 l_sui_ar_taxable_qtd_rem := 0;
2581 l_amt_req_to_reach_ar_lmt := 0;
2582 l_ar_state_abbrev := 'AR';
2583 l_ar_jurisdiction_code := '04-000-0000';
2584 l_sui_ar_oos_state_code := NULL;
2585
2586 hr_utility.trace('Adjusting the Arkansas Taxable and Out of State Wages');
2587
2588 l_sui_ar_tax_user_ent_id := get_user_entity_id('SUI_ER_AR_ADJ_TAXABLE_PER_JD_GRE_QTD');
2589 l_sui_ar_oos_rpt_user_ent_id := get_user_entity_id('SUI_ER_AR_ADJ_OOSW_RPT_PER_JD_GRE_QTD');
2590 l_sui_ar_oos_stcd_user_ent_id := get_user_entity_id('SUI_ER_AR_OOSW_STATE_CODE');
2591
2592 hr_utility.trace('l_sui_ar_tax_user_ent_id: ' || TO_CHAR(l_sui_ar_tax_user_ent_id));
2593 hr_utility.trace('l_sui_ar_oos_rpt_user_ent_id: ' || TO_CHAR(l_sui_ar_oos_rpt_user_ent_id));
2594 hr_utility.trace('l_sui_ar_oos_stcd_user_ent_id: ' || TO_CHAR(l_sui_ar_oos_stcd_user_ent_id));
2595
2596 pay_balance_pkg.set_context('TAX_UNIT_ID',taxunitid);
2597 pay_balance_pkg.set_context('JURISDICTION_CODE',l_ar_jurisdiction_code);
2598
2599 /* This query is to get the Last Effective Day of Employee in Arkansas in the Quarter */
2600 /* Modified for Bug#14371049 */
2601 /* Modified for Bug#16236978 */
2602
2603 SELECT LEAST(MAX(pef.effective_end_date), p_effective_date)
2604 INTO l_effective_date
2605 FROM per_all_assignments_f paa,
2606 pay_us_emp_fed_tax_rules_f pef,
2607 pay_us_states pus
2608 WHERE paa.assignment_id = pef.assignment_id
2609 AND paa.assignment_id = asgid
2610 AND paa.assignment_type = 'E'
2611 AND pef.sui_state_code = pus.state_code
2612 AND pus.state_abbrev = g_sqwl_state
2613 AND paa.effective_end_date >= ADD_MONTHS(LAST_DAY(p_effective_date),-3)+1 -- Quarter Start Date
2614 AND paa.effective_start_date <= p_effective_date
2615 AND pef.effective_end_date >= ADD_MONTHS(LAST_DAY(p_effective_date),-3)+1 -- Quarter Start Date
2616 AND pef.effective_start_date <= p_effective_date;
2617
2618 IF l_effective_date IS NULL THEN
2619 OPEN get_effective_date(ADD_MONTHS(LAST_DAY(p_effective_date),-3)+1, LAST_DAY(p_effective_date));
2620 FETCH get_effective_date INTO l_effective_date;
2621 CLOSE get_effective_date;
2622 hr_utility.trace('Modified l_effective_date: '||to_char(l_effective_date));
2623 END IF;
2624
2625 hr_utility.trace('asgid: '||TO_CHAR(asgid));
2626 hr_utility.trace('g_sqwl_state: '||TO_CHAR(g_sqwl_state));
2627 hr_utility.trace('Original p_effective_date: '||TO_CHAR(p_effective_date));
2628 hr_utility.trace('Modified l_effective_date: '||TO_CHAR(l_effective_date));
2629
2630 /* Previous Taxable Out of State Wages in the Year */
2631 OPEN get_previous_ar_value(l_sui_ar_oos_rpt_user_ent_id);
2632 FETCH get_previous_ar_value into l_prev_ar_oos_rpt;
2633 IF l_prev_ar_oos_rpt IS NULL THEN
2634 l_prev_ar_oos_rpt := 0;
2635 END IF;
2636 CLOSE get_previous_ar_value;
2637
2638 hr_utility.trace('l_prev_ar_oos_rpt: '||TO_CHAR(l_prev_ar_oos_rpt));
2639
2640 /* AR SUI Wage Limit */
2641 l_ar_sui_er_limit := hr_us_ff_udf1.get_jit_data (l_ar_jurisdiction_code,
2642 p_effective_date,
2643 'SUI_ER_WAGE_LIMIT');
2644
2645 hr_utility.trace('l_ar_sui_er_limit: '||TO_CHAR(l_ar_sui_er_limit));
2646
2647 /* Get the Defined balance ids for
2648 - SUI ER Arkansas Taxable Wages in Year
2649 - SUI ER Total Taxable Wages in Year - This includes all states
2650 - SUI ER Arkansas Subj Whable Wages in Quarter
2651 - SUI ER Arkansas Subj Whable Wages in Year
2652 - SUI ER Arkansas Pre Tax Redn Wages in Quarter
2653 - SUI ER Arkansas Pre Tax Redn Wages in Year */
2654 OPEN c_get_defined_balance_id_ar;
2655 FETCH c_get_defined_balance_id_ar INTO l_def_bal_id, l_balance_name, l_dimension_name;
2656
2657 WHILE c_get_defined_balance_id_ar%FOUND
2658 LOOP
2659
2660 IF l_dimension_name = 'Person in JD within GRE Quarter to Date' THEN
2661
2662 IF l_balance_name = 'SUI ER Subj Whable' THEN
2663 /* AR Subj Whable Wages till Last Effective Date of Employee in AR in Quarter */
2664 l_sui_ar_subj_whable_qtd := pay_balance_pkg.get_value(l_def_bal_id,
2665 asgid,
2666 l_effective_date);
2667
2668 hr_utility.trace('l_sui_ar_subj_whable_qtd: '|| TO_CHAR(l_sui_ar_subj_whable_qtd));
2669
2670 ELSIF l_balance_name = 'SUI ER Pre Tax Redns' THEN
2671 /* AR Pre Tax Redn Wages till Last Effective Date of Employee in AR in Quarter */
2672 l_sui_ar_pre_tax_redn_qtd := pay_balance_pkg.get_value(l_def_bal_id,
2673 asgid,
2674 l_effective_date);
2675
2676 hr_utility.trace('l_sui_ar_pre_tax_redn_qtd: '|| TO_CHAR(l_sui_ar_pre_tax_redn_qtd));
2677 END IF;
2678
2679 ELSIF l_dimension_name = 'Person in JD within GRE Year to Date' THEN
2680
2681 IF l_balance_name = 'SUI ER Taxable' THEN
2682 /* AR Taxable Wages till Last Effective Date of Employee in AR in Year */
2683 l_sui_ar_taxable_ytd := pay_balance_pkg.get_value(l_def_bal_id,
2684 asgid,
2685 l_effective_date);
2686
2687 hr_utility.trace('l_sui_ar_taxable_ytd: '|| TO_CHAR(l_sui_ar_taxable_ytd));
2688
2689 ELSIF l_balance_name = 'SUI ER Subj Whable' THEN
2690 /* AR Subj Whable Wages till Last Effective Date of Employee in AR in Year */
2691 l_sui_ar_subj_whable_ytd := pay_balance_pkg.get_value(l_def_bal_id,
2692 asgid,
2693 l_effective_date);
2694
2695 hr_utility.trace('l_sui_ar_subj_whable_ytd: '|| TO_CHAR(l_sui_ar_subj_whable_ytd));
2696
2697 ELSIF l_balance_name = 'SUI ER Pre Tax Redns' THEN
2698 /* AR Pre Tax Redn Wages till Last Effective Date of Employee in AR in Year */
2699 l_sui_ar_pre_tax_redn_ytd := pay_balance_pkg.get_value(l_def_bal_id,
2700 asgid,
2701 l_effective_date);
2702
2703 hr_utility.trace('l_sui_ar_pre_tax_redn_ytd: '|| TO_CHAR(l_sui_ar_pre_tax_redn_ytd));
2704 END IF;
2705
2706 ELSIF l_dimension_name = 'Person within Government Reporting Entity Year to Date' THEN
2707
2708 /* Total Wages till Last Effective Date of Employee in AR in Year */
2709 l_sui_ar_total_taxable_ytd := pay_balance_pkg.get_value(l_def_bal_id,
2710 asgid,
2711 l_effective_date);
2712
2713 hr_utility.trace('l_sui_ar_total_taxable_ytd '|| TO_CHAR(l_sui_ar_total_taxable_ytd));
2714 END IF;
2715
2716 FETCH c_get_defined_balance_id_ar INTO l_def_bal_id, l_balance_name, l_dimension_name;
2717
2718 END LOOP;
2719 CLOSE c_get_defined_balance_id_ar;
2720
2721 /* Total Out of State Wages in Year till Last Date in AR = Total Wages in Year till Last Date in AR
2722 - AR Wages in Year */
2723 l_sui_ar_oos_ytd := l_sui_ar_total_taxable_ytd - l_sui_ar_taxable_ytd;
2724 hr_utility.trace('l_sui_ar_oos_ytd '|| TO_CHAR(l_sui_ar_oos_ytd));
2725
2726 /* Total Reduced Subject Wages in Year till Last Date in AR */
2727 l_sui_ar_redsubj_whable_ytd := l_sui_ar_subj_whable_ytd - l_sui_ar_pre_tax_redn_ytd;
2728 hr_utility.trace('l_sui_ar_redsubj_whable_ytd '|| TO_CHAR(l_sui_ar_redsubj_whable_ytd));
2729
2730 /* Total Reduced Subject Wages in Quarter till Last Date in AR */
2731 l_sui_ar_redsubj_whable_qtd := l_sui_ar_subj_whable_qtd - l_sui_ar_pre_tax_redn_qtd;
2732 hr_utility.trace('l_sui_ar_redsubj_whable_qtd '|| TO_CHAR(l_sui_ar_redsubj_whable_qtd));
2733
2734 IF (l_sui_ar_redsubj_whable_ytd - l_sui_ar_redsubj_whable_qtd) < l_ar_sui_er_limit THEN
2735 /* Reduced Subject Withholdable Wages reported in Previous Quarters does not reach the AR limit */
2736
2737 hr_utility.set_location('From archive_data AR', 10);
2738
2739 /* Total Out of State Wages not reported in Year = Total Out of State Wages in Year till Last Date in AR
2740 - Out of State Wages previously reported */
2741 l_total_oos_rpt_remaining := l_sui_ar_oos_ytd - l_prev_ar_oos_rpt;
2742 hr_utility.trace('l_total_oos_rpt_remaining '|| TO_CHAR(l_total_oos_rpt_remaining));
2743
2744 /* Amount required to reach Arkansas Wage Limit */
2745 l_amt_req_to_reach_ar_lmt := l_ar_sui_er_limit - (l_sui_ar_redsubj_whable_ytd - l_sui_ar_redsubj_whable_qtd);
2746 hr_utility.trace('l_amt_req_to_reach_ar_lmt '|| TO_CHAR(l_amt_req_to_reach_ar_lmt));
2747
2748 /* Out of State Wages reported is the Least of Amount required to reach AR Limit,
2749 AR Quarterly Reduced Subj Whable Wages and remaining Out of State yearly Wages */
2750 l_sui_ar_oos_rpt_qtd := LEAST(l_amt_req_to_reach_ar_lmt,
2751 l_sui_ar_redsubj_whable_qtd,
2752 l_total_oos_rpt_remaining);
2753 hr_utility.trace('l_sui_ar_oos_rpt_qtd '|| TO_CHAR(l_sui_ar_oos_rpt_qtd));
2754
2755 /* Remaining AR Taxable Wages = AR Reduced Subj Whable Wages - Out of State Wages reported */
2756 l_sui_ar_taxable_qtd_rem := l_sui_ar_redsubj_whable_qtd - l_sui_ar_oos_rpt_qtd;
2757 hr_utility.trace('l_sui_ar_taxable_qtd_rem '|| TO_CHAR(l_sui_ar_taxable_qtd_rem));
2758
2759 /* Amount required to reach Arkansas Wage Limit */
2760 l_amt_req_to_reach_ar_lmt := l_ar_sui_er_limit - (l_sui_ar_redsubj_whable_ytd - l_sui_ar_redsubj_whable_qtd)
2761 - l_sui_ar_oos_rpt_qtd;
2762 hr_utility.trace('l_amt_req_to_reach_ar_lmt '|| TO_CHAR(l_amt_req_to_reach_ar_lmt));
2763
2764 /* Adjusted AR Taxable Wages is least of Amount required to reach AR Wage Limit and Remaining AR Taxable Wages */
2765 l_sui_ar_adj_taxable_qtd := LEAST(l_amt_req_to_reach_ar_lmt, l_sui_ar_taxable_qtd_rem);
2766 hr_utility.trace('l_sui_ar_adj_taxable_qtd '|| TO_CHAR(l_sui_ar_adj_taxable_qtd));
2767
2768 ELSE
2769 /* Previous Adjusted AR Taxable + Out of State Wages has reached the AR limit */
2770 l_sui_ar_oos_rpt_qtd := 0;
2771 l_sui_ar_adj_taxable_qtd := 0;
2772 hr_utility.set_location('From archive_data AR', 60);
2773 hr_utility.trace('l_sui_ar_oos_rpt_qtd '|| TO_CHAR(l_sui_ar_oos_rpt_qtd));
2774 hr_utility.trace('l_sui_ar_adj_taxable_qtd '|| TO_CHAR(l_sui_ar_adj_taxable_qtd));
2775 END IF;
2776
2777 /* Report Out of State Wages State Code only if Out of State Wages are reported */
2778 IF l_sui_ar_oos_rpt_qtd <> 0 THEN
2779
2780 /* Search for SUI State Codes from pay_us_emp_fed_tax_rules in Quarter */
2781 OPEN get_ar_oos_state_code(asgid,
2782 ADD_MONTHS(LAST_DAY(p_effective_date),-3) +1,
2783 l_effective_date,
2784 l_ar_state_abbrev);
2785
2786 FETCH get_ar_oos_state_code INTO l_sui_ar_oos_state_code;
2787
2788 IF get_ar_oos_state_code%NOTFOUND THEN
2789 CLOSE get_ar_oos_state_code;
2790
2791 /* Search for SUI State Codes from pay_us_emp_fed_tax_rules in year */
2792 OPEN get_ar_oos_state_code(asgid,
2793 TRUNC(p_effective_date, 'YEAR'),
2794 l_effective_date,
2795 l_ar_state_abbrev);
2796
2797 FETCH get_ar_oos_state_code INTO l_sui_ar_oos_state_code;
2798
2799 IF get_ar_oos_state_code%NOTFOUND THEN
2800 CLOSE get_ar_oos_state_code;
2801 /* Search for State Codes from pay_us_emp_state_tax_rules in Year */
2802 OPEN get_ar_oos_state_code_st(asgid,
2803 TRUNC(p_effective_date, 'YEAR'),
2804 l_effective_date,
2805 l_ar_state_abbrev);
2806
2807 FETCH get_ar_oos_state_code_st INTO l_sui_ar_oos_state_code;
2808
2809 IF get_ar_oos_state_code_st%NOTFOUND THEN
2810 l_sui_ar_oos_state_code := 'XX';
2811 END IF;
2812
2813 CLOSE get_ar_oos_state_code_st;
2814 ELSE
2815 CLOSE get_ar_oos_state_code;
2816 END IF;
2817 ELSE
2818 CLOSE get_ar_oos_state_code;
2819 END IF;
2820 END IF;
2821
2822 /* Archive SUI Adjusted Taxable Wages */
2823 INSERT INTO ff_archive_items (archive_item_id,
2824 user_entity_id,
2825 context1,
2826 value)
2827 VALUES(ff_archive_items_s.NEXTVAL ,
2828 l_sui_ar_tax_user_ent_id ,
2829 p_assactid ,
2830 l_sui_ar_adj_taxable_qtd );
2831
2832 hr_utility.trace('Archived the adjusted AR Taxable');
2833
2834 SELECT context_id
2835 INTO l_context_id_tax_unit_id
2836 FROM ff_contexts
2837 WHERE context_name = 'TAX_UNIT_ID';
2838
2839 INSERT INTO ff_archive_item_contexts
2840 (archive_item_id,sequence_no,context,context_id)
2841 VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
2842
2843 SELECT context_id
2844 INTO l_context_id_jurisdiction_code
2845 FROM ff_contexts
2846 WHERE context_name = 'JURISDICTION_CODE';
2847
2848 INSERT INTO ff_archive_item_contexts
2849 (archive_item_id,sequence_no,context,context_id)
2850 VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
2851
2852 /* Archive Out of State Wages */
2853 INSERT INTO ff_archive_items (archive_item_id,
2854 user_entity_id,
2855 context1,
2856 value)
2857 VALUES(ff_archive_items_s.NEXTVAL ,
2858 l_sui_ar_oos_rpt_user_ent_id,
2859 p_assactid ,
2860 l_sui_ar_oos_rpt_qtd );
2861
2862 hr_utility.trace('Archived the AR Out of State Wages');
2863
2864 INSERT INTO ff_archive_item_contexts
2865 (archive_item_id,sequence_no,context,context_id)
2866 VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
2867
2868 INSERT INTO ff_archive_item_contexts
2869 (archive_item_id,sequence_no,context,context_id)
2870 VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
2871
2872 /* Archive Out of State Wages State Code */
2873 INSERT INTO ff_archive_items (archive_item_id,
2874 user_entity_id,
2875 context1,
2876 value)
2877 VALUES(ff_archive_items_s.NEXTVAL ,
2878 l_sui_ar_oos_stcd_user_ent_id,
2879 p_assactid ,
2880 l_sui_ar_oos_state_code );
2881
2882 hr_utility.trace('Archived the Out of State Wages State Code');
2883
2884 /* Added for Bug#14097843 Ends here */
2885
2886 /*
2887 The following code was added on 08-FEB-2000 by Ashu Gupta (ashgupta) to
2888 take care of archiving of Wage Plan Codes in California
2889 */
2890 ELSIF (g_sqwl_state = 'FL') THEN
2891
2892 hr_utility.trace('Adjusting the Florida Taxable');
2893
2894 l_user_entity_id :=get_user_entity_id('SUI_ER_FL_ADJ_TAXABLE_PER_JD_GRE_QTD');
2895
2896 fl_jurisdiction_code := '10-000-0000';
2897
2898 pay_balance_pkg.set_context('TAX_UNIT_ID',taxunitid);
2899
2900 pay_balance_pkg.set_context('JURISDICTION_CODE',fl_jurisdiction_code);
2901
2902 /* Modified for Bug#9561700*/
2903 /* First find out if the Assignment record is ending in between the Quarter.If
2904 it is not, then call the balance procedure with Quarter End Date.If the Assignment
2905 record ends in between the Quarter, we need to use find the maximum effective
2906 date for the assignment from payroll actions and use it in balance calls.*/
2907
2908 SELECT least(max(effective_end_date),p_effective_date)
2909 INTO l_effective_end_date
2910 FROM per_all_assignments_f
2911 WHERE assignment_id = asgid
2912 AND assignment_type = 'E'
2913 AND effective_end_date >= add_months(last_day(p_effective_date),-3)+1 ; /*Quarter Start Date */
2914
2915 IF l_effective_end_date < p_effective_date
2916
2917 THEN
2918
2919 open get_effective_date(add_months(last_day(p_effective_date),-3)+1,last_day(p_effective_date));
2920 fetch get_effective_date into l_effective_date;
2921 close get_effective_date;
2922
2923 hr_utility.trace('Modified l_effective_date'||to_char(l_effective_date));
2924
2925 ELSE
2926
2927 l_effective_date := p_effective_date;
2928
2929 hr_utility.trace('Use original l_effective_date'||to_char(l_effective_date));
2930
2931 END IF;
2932
2933 /* End Bug#9561700*/
2934
2935 open get_defined_balance_id;
2936 fetch get_defined_balance_id into l_def_bal_id,l_dimension_name;
2937
2938 while get_defined_balance_id%FOUND
2939 loop
2940
2941 if l_dimension_name = 'Person in JD within GRE Quarter to Date' then
2942
2943
2944
2945 l_sui_fl_taxable_qtd := pay_balance_pkg.get_value( l_def_bal_id,
2946 asgid,
2947 l_effective_date);
2948
2949 hr_utility.trace('l_sui_fl_taxable_qtd'||l_sui_fl_taxable_qtd);
2950
2951
2952 elsif l_dimension_name = 'Person in JD within GRE Year to Date' then
2953
2954
2955 l_sui_fl_taxable_ytd := pay_balance_pkg.get_value( l_def_bal_id,
2956 asgid,
2957 l_effective_date);
2958
2959 hr_utility.trace('l_sui_fl_taxable_ytd'||l_sui_fl_taxable_ytd);
2960
2961
2962 else
2963
2964 l_sui_taxable_ytd := pay_balance_pkg.get_value( l_def_bal_id,
2965 asgid,
2966 l_effective_date);
2967
2968 hr_utility.trace('l_sui_taxable_ytd'||l_sui_taxable_ytd);
2969
2970 end if;
2971
2972 fetch get_defined_balance_id into l_def_bal_id,l_dimension_name;
2973
2974 end loop;
2975
2976 close get_defined_balance_id;
2977
2978 l_outstate_sui_taxable_ytd := l_sui_taxable_ytd - l_sui_fl_taxable_ytd;
2979
2980 hr_utility.trace('l_outstate_sui_taxable_ytd'||l_outstate_sui_taxable_ytd);
2981
2982 l_fl_sui_er_limit := hr_us_ff_udf1.get_jit_data ( fl_jurisdiction_code,p_effective_date,'SUI_ER_WAGE_LIMIT');
2983
2984 hr_utility.trace('l_fl_sui_er_limit'||l_fl_sui_er_limit);
2985
2986 if l_outstate_sui_taxable_ytd >= l_fl_sui_er_limit
2987 then
2988
2989 l_sui_fl_taxable_qtd := 0;
2990
2991 hr_utility.trace('l_outstate_sui_taxable_ytd greater than l_fl_sui_er_limit');
2992
2993 else
2994
2995
2996 open get_previous_fl_taxable;
2997
2998 fetch get_previous_fl_taxable into l_previous_fl_taxable;
2999
3000 if l_previous_fl_taxable is NULL then
3001
3002 l_previous_fl_taxable := 0;
3003
3004 end if;
3005
3006 close get_previous_fl_taxable;
3007
3008 if l_outstate_sui_taxable_ytd + l_previous_fl_taxable >= l_fl_sui_er_limit
3009 then
3010
3011 l_sui_fl_taxable_qtd := 0;
3012
3013 hr_utility.trace('l_outstate_sui_taxable_ytd and l_previous_fl_taxable greater than l_fl_sui_er_limit');
3014
3015 else
3016
3017 l_sui_fl_taxable_qtd := least(l_sui_fl_taxable_qtd,l_fl_sui_er_limit - l_outstate_sui_taxable_ytd - l_previous_fl_taxable);
3018
3019 end if;
3020
3021 end if;
3022
3023
3024 hr_utility.trace('l_sui_fl_taxable_qtd after adjustment is'||l_sui_fl_taxable_qtd);
3025
3026 INSERT INTO ff_archive_items (archive_item_id,
3027 user_entity_id,
3028 context1,
3029 value)
3030 VALUES( ff_archive_items_s.NEXTVAL ,
3031 l_user_entity_id ,
3032 p_assactid ,
3033 l_sui_fl_taxable_qtd );
3034
3035 hr_utility.trace('Archived the adjusted FL Taxable');
3036
3037 SELECT context_id
3038 INTO l_context_id_tax_unit_id
3039 FROM ff_contexts
3040 WHERE context_name = 'TAX_UNIT_ID';
3041
3042 INSERT INTO ff_archive_item_contexts
3043 (archive_item_id,sequence_no,context,context_id)
3044 VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
3045
3046 SELECT context_id
3047 INTO l_context_id_jurisdiction_code
3048 FROM ff_contexts
3049 WHERE context_name = 'JURISDICTION_CODE';
3050
3051 INSERT INTO ff_archive_item_contexts
3052 (archive_item_id,sequence_no,context,context_id)
3053 VALUES (ff_archive_items_s.CURRVAL,1,fl_jurisdiction_code,l_context_id_jurisdiction_code);
3054
3055 ELSIF (g_sqwl_state = 'CA') THEN
3056
3057 l_user_entity_id :=get_user_entity_id('A_SCL_ASG_US_CA_WAGE_PLAN_CODE');
3058 l_quarter_start := TRUNC(p_effective_date, 'Q');
3059 l_quarter_end := ADD_MONTHS(TRUNC(p_effective_date, 'Q'),3) - 1;
3060
3061 SELECT context_id
3062 INTO l_context_id_assignment_id
3063 FROM ff_contexts
3064 WHERE context_name = 'ASSIGNMENT_ID';
3065
3066 /* l_user_entity_id, l_context_id_date_earned, l_context_id_assignment_id,
3067 can be declared as global variables, then there will be no need
3068 to select their values every time. This will improve performance */
3069 --
3070 -- IF (g_report_cat = 'RTM') THEN
3071 -- OPEN c_archive_wage_plan_code_rtm;
3072 -- ELSIF (g_report_cat = 'RTS') THEN
3073 -- OPEN c_archive_wage_plan_code_rts;
3074 -- END IF;
3075 --
3076 -- LOOP
3077 -- hr_utility.trace('In Archive Wage Plan Code RTM loop ');
3078 --
3079 -- IF (g_report_cat = 'RTM') THEN
3080 -- FETCH c_archive_wage_plan_code_rtm INTO l_wage_plan_code,
3081 -- l_assignment_id ;
3082 -- EXIT WHEN c_archive_wage_plan_code_rtm%NOTFOUND;
3083 -- ELSIF (g_report_cat = 'RTS') THEN
3084 -- FETCH c_archive_wage_plan_code_rts INTO l_wage_plan_code,
3085 -- l_assignment_id ;
3086 -- EXIT WHEN c_archive_wage_plan_code_rts%NOTFOUND;
3087 -- END IF;
3088 --
3089 --
3090 -- INSERT INTO ff_archive_items (archive_item_id,
3091 -- user_entity_id,
3092 -- context1,
3093 -- value)
3094 -- VALUES( ff_archive_items_s.NEXTVAL ,
3095 -- l_user_entity_id ,
3096 -- p_assactid ,
3097 -- l_wage_plan_code );
3098 --
3099 --
3100 -- INSERT INTO ff_archive_item_contexts (archive_item_id,
3101 -- sequence_no ,
3102 -- context ,
3103 -- context_id )
3104 -- VALUES (ff_archive_items_s.currval,
3105 -- 1 ,
3106 -- l_assignment_id ,
3107 -- l_context_id_assignment_id);
3108 -- END LOOP;
3109 -- IF (g_report_cat = 'RTM') THEN
3110 -- CLOSE c_archive_wage_plan_code_rtm;
3111 -- ELSIF (g_report_cat = 'RTS') THEN
3112 -- CLOSE c_archive_wage_plan_code_rts;
3113 -- END IF;
3114
3115
3116
3117 /* Due to the performance issues raised by Internal the above code has been replaced
3118 by tmehra 18-OCT-2001*/
3119
3120 l_wage_plan_ct := 0;
3121
3122 FOR c_rec in c_archive_wage_plan_code
3123 LOOP
3124
3125 hr_utility.trace('In Archive Wage Plan Code loop ');
3126
3127 l_wage_plan_code := c_rec.aei_information3;
3128
3129 INSERT INTO ff_archive_items (archive_item_id,
3130 user_entity_id,
3131 context1,
3132 value)
3133 VALUES( ff_archive_items_s.NEXTVAL ,
3134 l_user_entity_id ,
3135 p_assactid ,
3136 l_wage_plan_code );
3137
3138 INSERT INTO ff_archive_item_contexts (archive_item_id,
3139 sequence_no ,
3140 context ,
3141 context_id )
3142 VALUES (ff_archive_items_s.currval,
3143 1 ,
3144 asgid ,
3145 l_context_id_assignment_id);
3146
3147 l_wage_plan_ct := l_wage_plan_ct + 1;
3148
3149 END LOOP;
3150
3151 IF l_wage_plan_ct = 0 THEN
3152
3153 FOR c_rec in c_gre_wage_plan_code
3154 LOOP
3155
3156 hr_utility.trace('In Archive GRE Wage Plan Code loop ');
3157
3158 l_wage_plan_code := c_rec.wage_plan;
3159
3160 END LOOP;
3161
3162
3163
3164 INSERT INTO ff_archive_items (archive_item_id,
3165 user_entity_id,
3166 context1,
3167 value)
3168 VALUES( ff_archive_items_s.NEXTVAL ,
3169 l_user_entity_id ,
3170 p_assactid ,
3171 l_wage_plan_code );
3172
3173 INSERT INTO ff_archive_item_contexts (archive_item_id,
3174 sequence_no ,
3175 context ,
3176 context_id )
3177 VALUES (ff_archive_items_s.currval,
3178 1 ,
3179 asgid ,
3180 l_context_id_assignment_id);
3181
3182 END IF;
3183
3184 END IF;
3185
3186
3187
3188 hr_utility.set_location ('archive_data',2);
3189 /* Bug 773937 */
3190 /* If the chunk of the assignment is same as the minimun chunk
3191 for the payroll_action_id and the gre data has not yet been
3192 archived then archive the gre data i.e. the employer data */
3193
3194 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
3195 hr_utility.trace('archive_data archiving employer data');
3196 archive_gre_data(p_payroll_action_id => l_payroll_action_id,
3197 p_tax_unit_id => taxunitid);
3198 hr_utility.trace('archive_data archiving employer data');
3199 end if;
3200 /* End of Bug 773937 */
3201
3202 /* Setup contexts */
3203
3204 pay_balance_pkg.set_context ('ASSIGNMENT_ID', asgid);
3205 pay_balance_pkg.set_context ('DATE_EARNED',fnd_date.date_to_canonical(date_earned));
3206 /* pay_balance_pkg.set_context ('DATE_EARNED',fnd_date.date_to_canonical(date_earned,'DD-MON-YYYY')); date format not required */
3207 pay_balance_pkg.set_context ('TAX_UNIT_ID', taxunitid);
3208
3209 /* Get the year begin and year end dates */
3210
3211 l_year_start := trunc(p_effective_date, 'Y');
3212 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
3213
3214 /* Bug 976472 */
3215 if g_sqwl_state = 'NY' then
3216
3217 /* Initialise the global PL/SQL table */
3218
3219 for i in 1..l_context_no loop
3220
3221 pay_archive.g_context_values.name(i) := NULL;
3222 pay_archive.g_context_values.value(i) := NULL;
3223
3224 end loop;
3225
3226
3227 /* Get the New York burroughs and the Yonker City if the
3228 employee has tax records for them */
3229
3230 open c_get_city;
3231 loop
3232
3233 hr_utility.trace('In city loop ');
3234
3235 fetch c_get_city into l_jurisdiction;
3236 exit when c_get_city%NOTFOUND;
3237
3238 l_count := l_count + 1;
3239 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
3240 pay_archive.g_context_values.value(l_count) := l_jurisdiction;
3241
3242 end loop;
3243 close c_get_city;
3244
3245 If l_count = 0 then
3246 l_count := l_count + 1;
3247 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
3248 pay_archive.g_context_values.value(l_count) := g_sqwl_jursd;
3249 end if;
3250 pay_archive.g_context_values.sz := l_count;
3251
3252 end if;
3253 /* End Bug 976472 */
3254
3255 /* To get person level balances you must use the highest assignment action
3256 of the last paid assignment */
3257 /* Modifying to select effective_date from pay_payroll_actions corrsponding to
3258 the assignment action selected to solve th e new York SQWL 4th quarter problem */
3259
3260 --Bug 3331021 : Remove Query with Rule hint and added cursor c_get_latest_asg
3261
3262
3263 /* Get the effective_date and start_date of the payroll_Action_id */
3264
3265 select effective_date,
3266 start_date
3267 into l_period_end,
3268 l_period_start
3269 from pay_payroll_actions
3270 where payroll_action_id = l_payroll_action_id;
3271
3272
3273 begin
3274 open c_get_latest_asg(asgid );
3275 fetch c_get_latest_asg into aaid,eff_date;
3276 hr_utility.trace('aaid in action creation code'||to_char(aaid));
3277 close c_get_latest_asg;
3278
3279 exception
3280 when no_data_found then
3281 aaid := -9999;
3282 raise_application_error(-20001,'Balance Assignment Action does not exist for : '||to_char(asgid));
3283 end;
3284
3285 /* Updating the serial Number column of pay_assignment_actions with 1 if
3286 the effective_date of the assignment action id is lying in the
3287 fourth quarter. */
3288
3289 if g_sqwl_state = 'NY' and to_char(p_effective_date,'MM-DD') = '12-31'
3290 THEN
3291 if(eff_date < trunc(p_effective_date,'Q'))
3292 THEN
3293 update pay_assignment_actions paa
3294 set serial_number = 1
3295 where paa.assignment_action_id = p_assactid;
3296 END IF;
3297 END IF;
3298 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID',aaid);
3299 pay_archive.balance_aa := aaid;
3300
3301 exception
3302 when others then
3303 hr_utility.trace('Problem in archive_data');
3304
3305 end archive_data;
3306
3307 /* Name : update_ff_archive_items
3308 Purpose : Given the SQWL payroll_action_id, identifies SQWL assignment actions for which
3309 serial number is set to 1 (those employee assgnment actions who doesnt have balances
3310 in the 4th Qtr while running 4th qtr new york SQWL report ) and update QTD balances
3311 to zero for the assignment action in ff_archive_items.
3312 Arguments : SQWL Payroll Action ID
3313 */
3314
3315 /* added A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD and A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD
3316 for bug 1494215 of NY Q4 */
3317
3318 FUNCTION Update_ff_archive_items (p_payroll_action_id in VARCHAR2)
3319 return varchar is
3320 BEGIN
3321 update ff_archive_items ffai
3322 set ffai.value = 0
3323 where ffai.user_entity_id in (
3324 select user_entity_id
3325 from ff_database_items
3326 where user_name in ('A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD',
3327 'A_SUI_ER_SUBJ_NWHABLE_PER_JD_GRE_QTD',
3328 'A_SUI_ER_125_REDNS_PER_JD_GRE_QTD',
3329 'A_SUI_ER_401_REDNS_PER_JD_GRE_QTD',
3330 'A_SUI_ER_DEP_CARE_REDNS_PER_JD_GRE_QTD',
3331 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD',
3332 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_QTD',
3333 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_QTD',
3334 'A_SIT_125_REDNS_PER_JD_GRE_QTD',
3335 'A_SIT_401_REDNS_PER_JD_GRE_QTD',
3336 'A_SIT_DEP_CARE_REDNS_PER_JD_GRE_QTD',
3337 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD',
3338 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD',
3339 'A_SIT_WITHHELD_PER_JD_GRE_QTD')
3340 )
3341 and ffai.context1 in (
3342 select paa.assignment_action_id
3343 from pay_assignment_actions paa,
3344 pay_payroll_actions ppa
3345 where ppa.payroll_action_id = paa.payroll_action_id
3346 and ppa.report_type = 'SQWL'
3347 and ppa.report_qualifier = 'NY'
3348 and ppa.payroll_action_id = to_number(p_payroll_action_id)
3349 and paa.serial_number = 1
3350 );
3351 commit;
3352 return 'Y';
3353
3354 EXCEPTION
3355 when OTHERS then
3356 hr_utility.trace('Error while updating ff_archive_items ');
3357 return 'N';
3358 END Update_ff_archive_items;
3359
3360
3361 --Name
3362 -- preprocess_check
3363 --Purpose
3364 -- This function checks if
3365 -- In case of RTS : No person has got more than one wage plan code. Any
3366 -- of his/her assignments shpuld be having more than one
3367 -- wage plan code. If the two assignments for the same
3368 -- person has different wage plan codes, then also it is
3369 -- an error.
3370 -- In RTM : No person should be having a null wage plan code.
3371 -- In both the cases, only those assignments are taken
3372 -- into consideration that were paid in the period
3373 -- concerned. Added as a part of Enhancement Req 1063413
3374 ---------------------------------------------------------------------------
3375 FUNCTION preprocess_check
3376 (
3377 l_pactid NUMBER ,
3378 l_period_start DATE ,
3379 l_period_end DATE ,
3380 l_bus_group_id pay_payroll_actions.business_group_id%type,
3381 l_state VARCHAR2,
3382 l_report_cat VARCHAR2
3383 )
3384 RETURN BOOLEAN IS
3385
3386 CURSOR c_chk_asg_wp IS
3387 SELECT count(*) ct
3388 FROM per_assignments_f paf,
3389 per_assignment_extra_info paei
3390 WHERE paf.business_group_id = l_bus_group_id
3391 AND paf.effective_end_date >= l_period_start
3392 AND paf.effective_start_date <= l_period_end
3393 AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
3394 AND paei.aei_information1 = l_state /* Added for performance improvement Bug# 4344959 */
3395 AND paei.assignment_id = paf.assignment_id
3396 AND NOT EXISTS (SELECT null
3397 FROM hr_organization_information orgi,
3398 hr_soft_coding_keyflex sft
3399 WHERE orgi.organization_id = to_number(sft.segment1)
3400 AND sft.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
3401 AND orgi.org_information1 = paei.aei_information1
3402 AND (orgi.org_information2 = paei.aei_information2
3403 OR paei.aei_information2 IS NULL)
3404 AND orgi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
3405 AND orgi.org_information3 = paei.aei_information3);
3406
3407
3408
3409 CURSOR c_chk_gre_wp IS
3410 SELECT count(*) ct
3411 FROM hr_legal_entities org
3412 WHERE org.business_group_id = l_bus_group_id
3413 AND EXISTS (SELECT null
3414 FROM hr_organization_information orgi
3415 WHERE organization_id = org.organization_id
3416 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
3417 AND org_information1 = 'CA')
3418 AND NOT EXISTS ( SELECT null
3419 FROM hr_organization_information orgi
3420 WHERE organization_id = org.organization_id
3421 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
3422 AND org_information1 = 'CA'
3423 AND org_information4 = 'Y');
3424
3425 CURSOR c_dup_orgn_info IS
3426 SELECT count(*) ct
3427 FROM hr_legal_entities org,
3428 (select distinct
3429 a.organization_id,
3430 a.org_information1,
3431 a.org_information3
3432 FROM hr_organization_information a
3433 WHERE org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO') b
3434 WHERE org.business_group_id = l_bus_group_id
3435 AND b.organization_id = org.organization_id
3436 AND 1 < ( SELECT count(*)
3437 FROM hr_organization_information orgi
3438 WHERE organization_id = org.organization_id
3439 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
3440 AND org_information1 = b.org_information1
3441 AND org_information3 = b.org_information3);
3442
3443
3444 l_flag VARCHAR2(4) ;
3445 l_wage_plan_code hr_organization_information.org_information3%TYPE;
3446 l_company_sui_id hr_organization_information.org_information2%TYPE;
3447 l_counter NUMBER := 0 ;
3448 l_distinct_wage_plan_code NUMBER := 0 ;
3449
3450
3451 BEGIN
3452 hr_utility.set_location('pay_us_sqwl_archive.preprocess_check', 10);
3453
3454 IF (l_report_cat = 'RTM') THEN
3455
3456 l_counter := 0;
3457
3458 FOR c_rec IN c_dup_orgn_info
3459 LOOP
3460
3461 l_counter := c_rec.ct;
3462
3463 END LOOP;
3464
3465 IF (l_counter > 0) THEN
3466 hr_utility.set_location('pay_us_sqwl_archive.preprocess_check', 30);
3467 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
3468 hr_utility.set_message_token('FORMAT', '> 1 row in hoi for wg plcd');
3469 hr_utility.raise_error;
3470 END IF;
3471 END IF;
3472
3473 IF (l_report_cat = 'RTM') THEN
3474
3475 l_counter := 0;
3476
3477 FOR c_rec IN c_chk_gre_wp
3478 LOOP
3479
3480 l_counter := c_rec.ct;
3481
3482 END LOOP;
3483
3484 IF l_counter > 0 THEN
3485 hr_utility.set_location('pay_us_sqwl_archive.preprocess_check', 40);
3486 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
3487 hr_utility.set_message_token('FORMAT',' Default Wage Plan not marked');
3488 hr_utility.raise_error;
3489 END IF;
3490
3491 l_counter := 0;
3492
3493 FOR c_rec IN c_chk_asg_wp
3494 LOOP
3495
3496 l_counter := c_rec.ct;
3497
3498 END LOOP;
3499
3500 IF l_counter > 0 THEN
3501 hr_utility.set_location('pay_us_sqwl_archive.preprocess_check', 50);
3502 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
3503 hr_utility.set_message_token('FORMAT',' wage plan not defined at GRE');
3504 hr_utility.raise_error;
3505 END IF;
3506
3507 END IF;
3508 RETURN TRUE;
3509 END preprocess_check;
3510
3511 /* Name : archive_asg_locs
3512 Purpose : This procedure will archive the assignment locations as of the 12
3513 of each months during the sqwl quarter.
3514 Arguments :
3515 Notes :
3516 */
3517
3518 PROCEDURE archive_asg_locs( p_asg_act_id in number
3519 ,p_pay_act_id in number
3520 ,p_asg_id in number)
3521 IS
3522
3523 CURSOR c_asg_loc_mon ( p_ass_act_id number
3524 ,p_mon_of_qtr number) IS
3525 SELECT ASG.LOCATION_ID
3526 FROM per_assignments_f ASG
3527 , pay_assignment_actions ASSACT
3528 , pay_payroll_actions PACT
3529 WHERE ASSACT.assignment_action_id = p_ass_act_id
3530 AND ASSACT.payroll_action_id = PACT.payroll_action_id
3531 AND ASSACT.assignment_id = ASG.assignment_id
3532 AND add_months(trunc (PACT.effective_date, 'Q'), p_mon_of_qtr - 1) + 11
3533 BETWEEN ASG.effective_start_date
3534 AND ASG.Effective_end_date;
3535
3536 CURSOR c_asg_loc_mon2 ( p_ass_act_id number
3537 ,p_mon_of_qtr number) IS
3538 SELECT ASG.LOCATION_ID
3539 FROM per_assignments_f ASG
3540 , pay_assignment_actions ASSACT
3541 , pay_payroll_actions PACT
3542 WHERE ASSACT.assignment_action_id = p_ass_act_id
3543 AND ASSACT.payroll_action_id = PACT.payroll_action_id
3544 AND ASSACT.assignment_id = ASG.assignment_id
3545 AND ( add_months(trunc (PACT.effective_date, 'Q'), p_mon_of_qtr - 1)
3546 BETWEEN ASG.effective_start_date
3547 AND ASG.Effective_end_date
3548 OR last_day(add_months(trunc (PACT.effective_date, 'Q'), p_mon_of_qtr - 1))
3549 BETWEEN ASG.effective_start_date
3550 AND ASG.Effective_end_date)
3551 ORDER BY ASG.effective_start_date desc ;
3552
3553 l_location_id per_all_assignments_f.location_id%type;
3554 l_user_entity_id ff_user_entities.user_entity_id%type;
3555 l_archive_item_id ff_archive_items.archive_item_id%type;
3556 l_object_version_number ff_archive_items.object_version_number%type;
3557 l_some_warning boolean;
3558
3559 l_procedure varchar2(16) := 'archive_asg_locs';
3560
3561
3562 CURSOR c_asg_loc_end (p_ass_acti_id number) IS
3563 /*Commenting for bug 2510853
3564 SELECT paf.location_id
3565 FROM per_assignments_f paf,
3566 pay_assignment_actions paa,
3567 pay_payroll_actions ppa
3568 WHERE (paa.assignment_action_id = p_ass_acti_id
3569 AND paa.payroll_action_id = ppa.payroll_action_id
3570 AND paa.assignment_id = paf.assignment_id
3571 AND ppa.business_group_id = paf.business_group_id
3572 AND ppa.effective_date BETWEEN paf.effective_start_date
3573 AND paf.effective_end_date
3574 )
3575 OR (paa.assignment_action_id = p_ass_acti_id
3576 AND paa.payroll_action_id = ppa.payroll_action_id
3577 AND paa.assignment_id = paf.assignment_id
3578 AND ppa.business_group_id = paf.business_group_id
3579 AND paf.effective_end_date =
3580 (SELECT max(paf1.effective_end_date)
3581 FROM per_assignments_f paf1
3582 WHERE paf1.assignment_id = paf.assignment_id
3583 AND paf1.effective_end_date BETWEEN ppa.start_date
3584 AND ppa.effective_date
3585 )
3586 );
3587 */
3588
3589 SELECT paf.location_id
3590 FROM per_assignments_f paf,
3591 pay_assignment_actions paa,
3592 pay_payroll_actions ppa
3593 WHERE paa.assignment_action_id = p_ass_acti_id
3594 AND paa.payroll_action_id = ppa.payroll_action_id
3595 AND paa.assignment_id = paf.assignment_id
3596 -- commenting the redundant join with business group id for bug 2809506
3597 -- AND ppa.business_group_id = paf.business_group_id
3598 AND ((ppa.effective_date BETWEEN paf.effective_start_date
3599 AND paf.effective_end_date)
3600 OR
3601 (paf.effective_end_date =
3602 (SELECT max(paf1.effective_end_date)
3603 FROM per_assignments_f paf1
3604 WHERE paf1.assignment_id = paf.assignment_id
3605 AND paf1.effective_end_date BETWEEN ppa.start_date
3606 AND ppa.effective_date)
3607 )
3608 )
3609 order by paf.effective_end_date desc;
3610
3611 BEGIN
3612
3613 hr_utility.set_location('archive_asg_locs.' || l_procedure , 10);
3614 hr_utility.trace('p_asg_act_id = '||to_char(p_asg_act_id));
3615 hr_utility.trace('p_asg_id = '||to_char(p_asg_id));
3616 hr_utility.trace('p_pay_act_id = '||to_char(p_pay_act_id));
3617
3618
3619 FOR i IN 1 .. 3 LOOP
3620 OPEN c_asg_loc_mon(p_asg_act_id,
3621 i);
3622 Fetch c_asg_loc_mon into l_location_id;
3623
3624 IF c_asg_loc_mon%NOTFOUND THEN /*7429594 */
3625 -- l_location_id := Null;
3626 OPEN c_asg_loc_mon2(p_asg_act_id,
3627 i);
3628 Fetch c_asg_loc_mon2 into l_location_id;
3629 hr_utility.trace('Entered into c_asg_loc_mon2 cursor assignment id'||to_char(p_asg_act_id));
3630 hr_utility.trace('Entered into c_asg_loc_mon2 cursor location_id '||to_char(l_location_id));
3631 IF c_asg_loc_mon2%NOTFOUND THEN
3632 l_location_id := Null;
3633 END IF;
3634
3635 CLOSE c_asg_loc_mon2;
3636 END IF;
3637
3638 CLOSE c_asg_loc_mon;
3639
3640 IF l_location_id is not NULL THEN
3641
3642 hr_utility.set_location('archive_asg_locs.' || l_procedure , 20);
3643
3644 -- set the correct user_entity_id for the archive call
3645 BEGIN
3646 SELECT user_entity_id
3647 INTO l_user_entity_id
3648 FROM ff_user_entities
3649 WHERE user_entity_name = 'A_SQWL_LOC_MON_' || to_char(i);
3650 EXCEPTION
3651 WHEN NO_DATA_FOUND THEN
3652 hr_utility.trace('User entities SQWL_LOC_MON_* not define contact your system administrator');
3653 raise hr_utility.hr_error;
3654 END;
3655
3656 hr_utility.set_location('archive_asg_locs.' || l_procedure , 30);
3657
3658 -- Call the create archive item api procedure
3659 ff_archive_api.create_archive_item(
3660 p_archive_item_id => l_archive_item_id
3661 ,p_user_entity_id => l_user_entity_id
3662 ,p_archive_value => l_location_id
3663 ,p_archive_type => 'AAP'
3664 ,p_action_id => p_asg_act_id
3665 ,p_legislation_code => 'US'
3666 ,p_object_version_number => l_object_version_number
3667 ,p_some_warning => l_some_warning
3668 );
3669
3670 IF l_some_warning THEN
3671 hr_utility.trace('Error occurrecd when creating archive item ');
3672 raise hr_utility.hr_error;
3673 END IF;
3674 END IF;
3675
3676 END LOOP;
3677
3678 hr_utility.set_location('archive_asg_locs.' || l_procedure , 40);
3679
3680 -- Process the location id for the end of the period.
3681 OPEN c_asg_loc_end(p_asg_act_id);
3682
3683 FETCH c_asg_loc_end INTO l_location_id;
3684
3685 IF c_asg_loc_end%NOTFOUND THEN
3686 close c_asg_loc_end;
3687 hr_utility.trace('Error occurrecd when creating archive item ');
3688 hr_utility.trace('Error occurrecd : Assignment Location not found for p_asg_act_id ='|| to_char(p_asg_act_id));
3689 raise hr_utility.hr_error;
3690 END IF;
3691 close c_asg_loc_end;
3692
3693
3694 hr_utility.set_location('archive_asg_locs.' || l_procedure , 50);
3695
3696 -- set the correct user_entity_id for the archive call
3697 BEGIN
3698 SELECT user_entity_id
3699 INTO l_user_entity_id
3700 FROM ff_user_entities
3701 WHERE user_entity_name = 'A_SQWL_LOC_QTR_END';
3702 EXCEPTION
3703 WHEN NO_DATA_FOUND THEN
3704 hr_utility.trace('User entities A_SQWL_LOC_END_QTR not define contact your system administrator');
3705 raise hr_utility.hr_error;
3706 END;
3707
3708 hr_utility.set_location('archive_asg_locs.' || l_procedure , 60);
3709
3710 -- Call the create archive item api procedure
3711 ff_archive_api.create_archive_item(
3712 p_archive_item_id => l_archive_item_id
3713 ,p_user_entity_id => l_user_entity_id
3714 ,p_archive_value => l_location_id
3715 ,p_archive_type => 'AAP'
3716 ,p_action_id => p_asg_act_id
3717 ,p_legislation_code => 'US'
3718 ,p_object_version_number => l_object_version_number
3719 ,p_some_warning => l_some_warning
3720 );
3721
3722 IF l_some_warning THEN
3723 hr_utility.trace('Error occurrecd when creating archive item ');
3724 hr_utility.trace('Error occurrecd when creating archive item for User entity A_SQWL_LOC_END_QTR');
3725 raise hr_utility.hr_error;
3726 END IF;
3727
3728 EXCEPTION
3729 WHEN OTHERS THEN
3730 hr_utility.trace('Error occurrecd when creating archive item ');
3731 hr_utility.trace('Error occurrecd when othersof archive_asg_locs ');
3732 raise hr_utility.hr_error;
3733
3734 END archive_asg_locs;
3735
3736
3737 /* Name : range_cursor
3738 Purpose : This returns the select statement that is used to created the
3739 range rows.
3740 Arguments :
3741 Notes :
3742 */
3743
3744 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
3745 l_state pay_payroll_actions.report_qualifier%type;
3746 l_report_cat pay_payroll_actions.report_category%type;
3747 l_effective_date pay_payroll_actions.effective_date%type;
3748 l_start_date pay_payroll_actions.start_date%type;
3749 l_business_group_id pay_payroll_actions.business_group_id%type;
3750
3751 /* Bug 773937 */
3752 l_tax_unit_id number;
3753 l_gre number;
3754 l_archive boolean:= FALSE;
3755
3756 l_from number;
3757 l_to number;
3758 l_length number;
3759 l_w2_reporting_rules_exist number;
3760
3761 /* Local variables used for checking W2 Reporting Rules */
3762 message_text VARCHAR2(32000):= null;
3763 message_preprocess VARCHAR2(2000) := null;
3764
3765
3766 /* End of Bug 773937 */
3767
3768 -- The l_preprocess_flag variable was added by Ashu Gupta (ashgupta)
3769 -- on 08-FEB-2000 to check if any person has an invalid wage plan code
3770
3771 l_preprocess_flag BOOLEAN := FALSE;
3772 l_parent_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
3773
3774 cursor c_reporting_rules(cp_tax_unit_id in number) is
3775 select '1' from hr_organization_information
3776 where organization_id = cp_tax_unit_id
3777 and org_information_context = 'W2 Reporting Rules';
3778 begin
3779
3780 SELECT report_qualifier,
3781 report_category,
3782 effective_date,
3783 start_date,
3784 business_group_id
3785 INTO l_state,
3786 l_report_cat,
3787 l_effective_date,
3788 l_start_date,
3789 l_business_group_id
3790 FROM pay_payroll_actions
3791 WHERE payroll_action_id = pactid;
3792
3793 hr_utility.trace('Selected from pay_payroll_actions ');
3794
3795 /* bug: 10649380 : code to check parent payroll action, this code has been added to stop the use
3796 of old sqwl process "State Quarterly Wage Listing", customer need use
3797 new enhanced SQWL Process.
3798 */
3799
3800 SELECT pay_us_payroll_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',legislative_parameters)
3801 INTO l_parent_payroll_action_id
3802 FROM pay_payroll_actions
3803 WHERE payroll_action_id = pactid;
3804 hr_utility.trace('l_parent_payroll_action_id '||l_parent_payroll_action_id);
3805
3806 IF l_parent_payroll_action_id is NULL THEN
3807 hr_utility.trace('No parent payroll action');
3808 PY_ROLLBACK_PKG.rollback_payroll_action(pactid);
3809 commit;
3810 hr_utility.set_message(801, 'PAY_75270_SQWL_NO_PARENT_EXIST');
3811 hr_utility.raise_error;
3812 END IF;
3813
3814 SELECT pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
3815 INTO l_gre
3816 FROM pay_payroll_actions
3817 WHERE payroll_action_id = pactid;
3818
3819 hr_utility.trace('Transferr GRE is '||to_char(l_gre));
3820 hr_utility.trace('Report Category is '||l_report_cat);
3821
3822 /* Bug 1220213 */
3823 /* If New York state and last quarter SQWL, then the date range is full year */
3824 if ( l_state = 'NY' and to_char(l_effective_date,'DD-MON') = '31-DEC' ) then
3825 l_start_date := trunc(l_start_date,'YYYY');
3826 end if;
3827 /* End of Bug 1220213 */
3828
3829 if (l_state = 'LA' and l_report_cat = 'RTLAQ') then
3830 sqwl_range := 'SELECT distinct ASG.person_id
3831 FROM hr_organization_information HOI,
3832 per_assignments_f ASG,
3833 pay_us_asg_reporting puar,
3834 pay_state_rules SR
3835 WHERE SR.state_code = ''' || l_state || '''
3836 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
3837 AND ASG.assignment_id = puar.assignment_id
3838 AND ASG.assignment_type = ''E''
3839 AND ASG.effective_start_date <= ''' || l_effective_date || '''
3840 AND ASG.effective_end_date >= ''' || l_start_date || '''
3841 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
3842 AND HOI.organization_id = puar.tax_unit_id
3843 AND puar.tax_unit_id = nvl(''' || l_gre || ''',puar.tax_unit_id) /* bug 10649380 */
3844 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
3845 AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
3846 AND NVL(HOI.ORG_INFORMATION16,''No'') = ''Yes''
3847 AND not exists (select ''x''
3848 from hr_organization_information HOI2
3849 where HOI2.organization_id = puar.tax_unit_id
3850 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
3851 AND HOI2.ORG_INFORMATION2 is not null)
3852 AND ASG.payroll_id is not null
3853 AND :payroll_action_id is not null
3854 ORDER BY ASG.person_id';
3855
3856 elsif (l_state = 'CT' and l_report_cat = 'RTCTN') then
3857 sqwl_range := 'SELECT distinct ASG.person_id
3858 FROM hr_organization_information HOI,
3859 per_assignments_f ASG,
3860 pay_us_asg_reporting puar,
3861 pay_state_rules SR
3862 WHERE SR.state_code = ''' || l_state || '''
3863 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
3864 AND ASG.assignment_id = puar.assignment_id
3865 AND ASG.assignment_type = ''E''
3866 AND ASG.effective_start_date <= ''' || l_effective_date || '''
3867 AND ASG.effective_end_date >= ''' || l_start_date || '''
3868 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
3869 AND HOI.organization_id = puar.tax_unit_id
3870 AND puar.tax_unit_id = nvl(''' || l_gre || ''',puar.tax_unit_id) /* bug 10649380 */
3871 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
3872 AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
3873 AND NVL(HOI.ORG_INFORMATION20,''No'') = ''Yes''
3874 AND not exists (select ''x''
3875 from hr_organization_information HOI2
3876 where HOI2.organization_id = puar.tax_unit_id
3877 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
3878 AND HOI2.ORG_INFORMATION2 is not null)
3879 AND ASG.payroll_id is not null
3880 AND :payroll_action_id is not null
3881 ORDER BY ASG.person_id';
3882 else
3883 IF (l_state = 'CA') THEN
3884 l_preprocess_flag := preprocess_check(pactid ,
3885 l_start_date ,
3886 l_effective_date ,
3887 l_business_group_id ,
3888 l_state ,
3889 l_report_cat );
3890 END IF;
3891 IF ((l_preprocess_flag = TRUE AND l_state = 'CA') OR
3892 l_state <> 'CA') THEN
3893 sqwl_range := 'SELECT distinct ASG.person_id
3894 FROM hr_organization_information HOI,
3895 per_assignments_f ASG,
3896 pay_us_asg_reporting puar,
3897 pay_state_rules SR
3898 WHERE SR.state_code = ''' || l_state || '''
3899 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
3900 AND ASG.assignment_id = puar.assignment_id
3901 AND ASG.assignment_type = ''E''
3902 AND ASG.effective_start_date <= ''' || l_effective_date || '''
3903 AND ASG.effective_end_date >= ''' || l_start_date || '''
3904 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
3905 AND ((''' || l_state || ''' IN ( ''CA'',''ME''))
3906 OR (not exists (select ''x''
3907 from hr_organization_information HOI2
3908 where HOI2.organization_id = puar.tax_unit_id
3909 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
3910 AND HOI2.ORG_INFORMATION2 is not null)))
3911 AND HOI.organization_id = puar.tax_unit_id
3912 AND puar.tax_unit_id = nvl(''' || l_gre || ''',puar.tax_unit_id) /* bug 10649380 */
3913 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
3914 AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
3915 AND NVL(HOI.ORG_INFORMATION16,''No'') = ''No''
3916 AND NVL(HOI.ORG_INFORMATION20,''No'') = ''No''
3917 AND ASG.payroll_id is not null
3918 AND :payroll_action_id is not null
3919 ORDER BY ASG.person_id';
3920
3921 /* commented by saurgupt for testing
3922 sqwl_range := 'SELECT distinct ASG.person_id
3923 FROM pay_payrolls_f PPY,
3924 hr_organization_information HOI,
3925 per_assignments_f ASG,
3926 pay_us_asg_reporting puar,
3927 pay_state_rules SR
3928 WHERE SR.state_code = ''' || l_state || '''
3929 AND substr(SR.jurisdiction_code ,1,2) =
3930 substr(puar.jurisdiction_code,1,2)
3931 AND ASG.assignment_id = puar.assignment_id
3932 AND ASG.assignment_type = ''E''
3933 AND ASG.effective_start_date <= ''' || l_effective_date || '''
3934 AND ASG.effective_end_date >= ''' || l_start_date || '''
3935 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
3936 AND ((''' || l_state || ''' IN ( ''CA'',''ME''))
3937 OR (not exists (select ''x''
3938 from hr_organization_information HOI2
3939 where HOI2.organization_id = puar.tax_unit_id
3940 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
3941 AND HOI2.ORG_INFORMATION2 is not null)))
3942 AND HOI.organization_id = puar.tax_unit_id
3943 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
3944 AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
3945 AND NVL(HOI.ORG_INFORMATION16,''No'') = ''No''
3946 AND NVL(HOI.ORG_INFORMATION20,''No'') = ''No''
3947 AND PPY.payroll_id = ASG.payroll_id
3948 AND :payroll_action_id is not null
3949 ORDER BY ASG.person_id';
3950 */
3951 END IF;
3952
3953 end if;
3954
3955 hr_utility.trace('Bulit sqlstr for range ');
3956
3957 sqlstr := sqwl_range;
3958
3959 /* Bug 773937 */
3960 /* Select Tax unit Id from legislative parameters */
3961 select INSTR(legislative_parameters,'TRANSFER_TRANS_LEGAL_CO_ID=')
3962 + LENGTH('TRANSFER_TRANS_LEGAL_CO_ID=')
3963 into l_from
3964 from pay_payroll_actions
3965 where payroll_action_id = pactid;
3966
3967 hr_utility.trace('l_from is '||to_char(l_from));
3968
3969
3970 /* End position of state in legislative parameters */
3971
3972 select INSTR(legislative_parameters,'TRANSFER_DATE=')
3973 into l_to
3974 from pay_payroll_actions
3975 where payroll_action_id = pactid;
3976
3977 hr_utility.trace('l_to is '||to_char(l_to));
3978
3979 l_length := l_to - l_from - 1 ;
3980
3981 hr_utility.trace('l_length is '||to_char(l_length));
3982
3983 select fnd_number.canonical_to_number(substr(legislative_parameters, l_from , l_length ))
3984 into l_tax_unit_id
3985 from pay_payroll_actions
3986 where payroll_action_id = pactid;
3987
3988 hr_utility.trace('Transmitter GRS is '||to_char(l_tax_unit_id));
3989 hr_utility.trace('Report Category is '||l_report_cat);
3990
3991 /* Commenting this check as there's no need to define W2 reporting rules
3992 for SQWL's except for PR, which checks if a GRE is a PR GRE or not.
3993 if l_report_cat in ('RM', 'RTLAQ') then
3994
3995 open c_reporting_rules(l_tax_unit_id);
3996
3997 fetch c_reporting_rules into l_w2_reporting_rules_exist;
3998
3999 if c_reporting_rules%NOTFOUND then
4000
4001 message_preprocess := 'SQWL process - W2 Reporting Rules Missing';
4002 message_text := 'Define these for tax unit id '||to_char(l_tax_unit_id);
4003
4004
4005 hr_utility.trace('W2 Reporting rules have not been setup');
4006
4007 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
4008 pay_core_utils.push_token('record_name',message_preprocess);
4009 pay_core_utils.push_token('description',message_text);
4010
4011 end if;
4012 close c_reporting_rules;
4013
4014 end if;
4015 */
4016
4017 hr_utility.trace('Finished with W2 Reporting Rules check ');
4018
4019 l_archive := chk_gre_archive(pactid);
4020
4021 hr_utility.trace('after gre archive ');
4022
4023 if g_archive_flag = 'N' then
4024
4025 hr_utility.trace('range_cursor archiving employer data');
4026
4027 archive_gre_data(p_payroll_action_id => pactid,
4028 p_tax_unit_id => l_tax_unit_id);
4029
4030 hr_utility.trace('range_cursor archiving employer data');
4031
4032 end if;
4033 /* End of Bug 773937 */
4034
4035 end range_cursor;
4036
4037 --begin
4038
4039 --hr_utility.trace_on(null,'sqwl');
4040
4041 end pay_us_sqwl_archive;
[Home] [Help]