[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ARCHIVE
Source
1 package body pay_us_archive as
2 /* $Header: pyusarch.pkb 120.4.12000000.6 2007/09/18 09:18:02 sudedas noship $ */
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 18-Sep-2007 5517938 Archived First Yr Roth Contrib
14 from Person EIT.
15 18-Sep-2007 Reverting Back the Changes done
16 in 115.100
17 06-SEP-2007 sudedas 115.100 5517938 Archiving Year of Prior Deferral
18 For Roth 401(k)/403(b)
19 28-AUG-2007 vaprakas 115.99 Fix of bug 5065406
20 5744676 l_jd_done_tab, l_jd_name_done_tab
21 need to be Cleared for each Employee.
22 03-AUG-2007 sudedas 115.98 3973766 Job Development Fee (AL) need not
23 to be Archived.
24 05-JAN-2007 sackumar 115.97 5745950 Modified cursor c_get_disability_plan_scl_info
25 13-DEC-2006 kvsankar 115.96 5696031 Modified cursor c_get_latest_asg
26 to remove the order by clause.
27 13-NOV-2006 sodhingr 115.95 5656018 removed fnd_date.canonical_to_date
28 for A_ONLINE_W2
29 30-AUG-2006 sodhingr 115.94 5499805 Added a check to archive null if
30 if View Online W2 profile option
31 is blank
32 29-AUG-2006 sodhingr 115.93 3829668 Employees added to archive will have
33 A_W2_CORRECTED = 'N',checking the
34 value of A_ADD_ARCHIVE to decide if
35 A_W2_CORRECTED should be N. Also,
36 changed eoy_archive_gre_data to
37 archive A_VIEW_ONLINE_W2 with the
38 archive level of ER REARCH
39
40 28-AUG-2006 sodhingr 115.92 4947859 Changed eoy_archive_data to archive
41 A_W2_CORRECTED and eoy_archive_gre_data
42 to archive, A_VIEW_ONLINE_W2
43 11-AUG_2006 saurgupt 115.91 4544792 Replace DBI
44 A_EXTRA_ASSIGNMENT_INFORMATION_PAY_US_DISABILITY_PLAN_INFO_DF_PLAN_ID
45 with A_SCL_ASG_US_NJ_PLAN_ID. Removed the cursor
46 c_get_disability_plan_eit_info with c_get_disability_plan_scl_info.
47 29-AUG-2005 rsethupa 115.90 4163949 Modified cursor c_get_asg_id to
48 pick only the Primary Assignment
49 10-AUG-2005 rsethupa 115.89 Added code to enter message into
50 PAY_MESSAGE_LINES
51 08-AUG-2005 rsethupa 115.88 4137906 Suppressed Time Info in
52 A_ARCHIVE_DATE
53 17-NOV-2004 ahanda 115.87 Added support to RANGE_PERSON_ID
54 18-AUG-2004 meshah 115.86 storing city jd in plsql table
55 was missing. added back again
56 18-AUG-2004 meshah 115.85 now deleting l_jd_done_tab per
57 employee. Refer to note where we
58 are deleting the table.
59 Still getting the city, county
60 and state from plsql table.
61 11-AUG-2004 meshah 115.84 Archiving the sysdate for each
62 employee. This will be used to mark
63 W-2s as reissued or ammended.
64 06-AUG-2004 meshah 115.83 2149544 Added a check in range_cursor to
65 check for multiple submissions.
66 05-AUG-2004 meshah 115.82 moved plsql tables l_jd_done_tab
67 and l_jd_name_done_tab from body
68 to the header.
69 Added new procedure deinit.
70 04-AUG-2004 meshah 115.81 Fixed gscc error. File Sql 6
71 04-AUG-2004 meshah 115.80 moved variable eoy_gre_range
72 within the range cursor.
73 removed archiving of data that will
74 not be used from 2004 onwards.
75 removed extra code from
76 eoy_action_creation.
77 Changed eoy_archinit, now getting
78 payroll action data irrespective of
79 report type.
80 eoy)archive_data - now saving the
81 city, county and state abbrev in a
82 plsql table. Not deleting the
83 l_jd_done_tab table per employee.
84 changed c_get_city and c_get_county
85 cursor to check for tax exists
86 within the cursor.
87 Moved all the code of getting the
88 user_entity_id into archinit, so
89 that it is executed only once.
90 23-JAN-2004 ahanda 115.79 Modifed select stmt to get the
91 Spouse SSN for PR GRE to get the
92 last row valid for the year.
93 04-DEC-2003 sodhingr 115.75 Correct the values being passed to
94 pay_us_sqwl_udf.get_employment_code
95 26-NOV-2003 sodhingr 115.74 2219097 Changed package eoy_archive_data
96 and eoy_archive_gre_data to archive
97 government_employer flag and
98 changed logic to archive
99 employement code for all employees
100 even if the GRE is non-govement.
101 06-NOV-2003 sodhingr 115.73 2084862 Archiving Disability plan code
102 required for NJ magnetic tape
103 3234690 Archiving 1099R distribution code
104 24-OCT-2003 sodhingr 115.72 3207279 Added the check for language='US'
105 in the cursor csr_defined_balance
106 23-SEP-2003 sodhingr 115.71 3155042 Changed the cursor c_balance to
107 get meaning from fnd_lokkup_values
108 instead of fnd_common_lookups to fix
109 performance issue
110 04-SEP-2003 sodhingr 115.70 2219097 Changed procedure eoy_archive_data
111 to archive medicare, SS and thei
112 employement code
113 05-AUG-2003 sodhingr 115.68 2901349 Commented the cursor c_eoy_all and
114 eoy_all_range as GRE is manadatory
115 parameter for year end process so
116 these cursors will never be used.
117 Also, changed cursor eoy_gre_range
118 to join with pay_us_asg_reporting
119 instead of hr_soft_coding_keyflex.
120 This will ensure that assignments
121 are picked up year end preprocess
122 even if GRE is no longer valid for
123 that assignment.
124 05-AUG-2003 sodhingr 115.68 2753184 Change the logic to archive school
125 districtonly once. If the residence
126 address is changed and the school
127 district remains the same then
128 archiver
129 was archiving it twice, one when
130 archiving the city school district
131 and other when archiving the county
132 school district
133 18-JUN-2003 sodhingr 115.67 3011003 Commented the cursors
134 c_get_defined_balance_id,
135 c_get_puerto_rico_bal,
136 c_get_1099r_bal and using
137 pay_us_payroll_utils.
138 c_get_defined_balance_id
139 18-jun-2003 sodhingr 115.66 3011003 Changed the cursors
140 c_get_defined_balance_id,
141 c_get_puerto_rico_bal,
142 c_get_1099r_bal to add
143 join with creator_type = 'B'
144 27-DEC-2002 asasthan 115.65 2727539 changes to c_get_latest_asg cursor
145 to also pick reversal actions
146 24-DEC-2002 asasthan 115.65 changes to c_get_latest_asg cursor
147 to pick the correct action for
148 balance call
149 02-DEC-2002 asasthan 115.64 nocopy changes for gscc comp
150 08-NOV-2002 asasthan 115.63 2589239 Suppressed effective_date index in
151 archive_data procedure for
152 pay_payroll_actions in
153 c_get_latest_asg cursor
154 31-OCT-2002 asasthan 115.62 2589239 Suppressed effective_date index of
155 pay_payroll_actions in
156 c_get_latest_asg cursor
157 23-SEP-2002 asasthan 115.61 2590094 Archiving of BOX 12 cursor change
158 18-SEP-2002 fusman 115.60 Updated re-archiving changes.
159 17-SEP-2002 asasthan 115.59 Added archiving of W2 Transmitter
160 13-SEP-2002 fusamn 115.58 Added update if null so that mags
161 will not be affected.
162 06-SEP-2002 asasthan 115.57 Moved trace_on within range_code
163 06-SEP-2002 asasthan 115.56 To correct Employer Rearch print
164 process.
165 Changes for 1099 Magnetic rules
166 to be included in Emp REarch.
167 06-SEP-2002 asasthan 115.55 Employer Rearch was inserting rows
168 into ff_archive_items instead of
169 updating values. l_old_value made
170 null instead of 'Null'.
171 Also added more contexts for
172 Employer Rearch process to
173 handle
174 FEDERAL TAX RULES
175 FED TAX UNIT INFORMATION
176 04-SEP-2002 asasthan 115.54 Modified local variables l_old_value ,l_rowid_found ,l_fed_state_value to 240 instead of 100 varchar2
177 29-AUG-2002 fusman 115.53 Added new value in the State Re-archive process.
178 29-AUG-2002 fusman 115.52 Added a null check for the archived value.
179 29-AUG-2002 asasthan 115.51 Further changes for 1099 balances
180 28-AUG-2002 asasthan 115.49 Changed Names of 1099 balances
181 to Other EE Annuity Contract Amt
182 and Unrealized Net ER Sec Apprec.
183 Used plsql table for 1099R
184 balance feed checking
185 Reverted to old range code
186 that uses
187 hr_soft_coding_keyflex
188 Balance calls for PR use plsql tab
189 28-AUG-2002 fusman 115.48 Added changes for employer re-archive process.
190 27-AUG-2002 asasthan 115.47 Added function get_parameter
191 27-AUG-2002 asasthan 115.46 Added function get_report_type
192 so as to suppress the
193 call for eoy_archive_gre_data
194 for W2C_PRE_PROCESS.
195 23-AUG-2002 asasthan 115.45 Added global_variable for report_type
196 23-AUG-2002 asasthan 115.44 Changed names for 2 1099R balances
197 22-AUG-2002 asasthan 115.43 Checking for feeds for 1099R GREs
198 and cached user entities for
199 1099 and PR balances
200 GREs.
201 19-AUG-2002 asasthan 115.42 2491268 Changes for Puerto Rico and 1099R
202 19-AUG-2002 asasthan 115.41 2245457 Changes to archive W2 BOX 12
203 information thro' the
204 package and not thro' the formula.
205 15-AUG-2002 asasthan 115.40 2200920 Changed Range Cursor to go off
206 tax_unit_id of
207 pay_assignment_actions and not
208 hr_soft_coding_keyflex
209 2503639 Archiving Territory Balances
210 with Dimension of PER_GRE_YTD
211 and not PER_JD_GRE_YTD.
212 18-JUN-2002 ahanda 115.39 2412644 Correct Hint Syntax.
213 01-APR-2002 asasthan 115.38 2249870 modified Index Hint addded in
214 115.36 to use
215 PAY_ASSIGNMENT_ACTIONS_N51 instead
216 of PAY_ASSIGNMENT_ACTIONS_N1
217 22-JAN-2002 jgoswami 115.37 added checkfile command
218 28-DEC-2001 jgoswami 115.36 2161771 Added Index Hint in exist part of
219 the sql statement for c_eoy_gre in
220 eoy_action_creation procedure.
221 04-DEC-2001 jgoswami 115.35 Added Data related to Puerto Rico
222 A_MARITAL_STATUS,
223 A_CON_NATIONAL_IDENTIFIER
224 30-NOV-2001 jgoswami 115.34 added dbdrv command
225 09-NOV-2001 jgoswami 115.33 Added archive_type to ff_archive_items
226 insert for Payroll Action level.
227 15-OCT-2001 jgoswami 115.32 Added cursor c_get_latest_asg in
228 eoy_action_creation and eoy_archive_data
229 for improving performance and removed the
230 expensive query statement.
231 Remove code for SQWL and W2.
232 02-SEP-2001 ssarma 40.57 modified error handling to take care
233 of exceptions other than no_data_found.
234 28-AUG-2001 ssarma 40.55 TERRITORY DBI. name change.
235 28-AUG-2001 ssarma 40.54 TERRITORY.DBIs.should include JD
236 as a context.
237 27-AUG-2001 ssarma 40.52 TERRITORY_TAXABLE_ALLOWANCE_PER_GRE_YTD
238 instead of
239 TERRITORY_TAXABLE_ALLOWANCES_PER_GRE_YTD
240 23-AUG-2001 djoshi 40.49 removed comment as per sanjay
241 22-AUG-2001 ssarma 40.48 Revamp of create_archive,
242 eoy_archive_gre_data and
243 eoy_archive_data procedures
244 for employer level re-archive.
245 Tables used instead of variables
246 for user_entity_id and value in
247 create_archive.
248
249 14-aug-2001 djoshi 40.47 Changed the Database item name
250 A_TERRITORY_TAXABLE_RETIREMENT_CONTRIBUTION_PER_GRE_YTD to
251 A_TERRITORY_RETIREMENT_CONTRIB_PER_GRE_YTD
252
253 14-AUG-2001 SSarma 40.46 EOY 2001: Changes for security.
254 per_all_assignments_f instead of
255 per_assignmentes_f.
256 New items archived for Employer.
257 Legislation code checks for
258 ff_user_entities join.
259 Specific archiving for Puerto Rico.
260
261 03-Aug-2000 ssarma 40.43 EOY 2000: Changes to city, county cursors
262 Checks for formula compilation.
263 Check to see if jurisdiction has been
264 archived - city, county, state.
265 Change to eoy action creation cursor.
266 Change to select which gets latest assignment
267 action.
268 Filter for selecting employees bases on 5
269 balances.
270 20-JAN-2000 ahanda 40.42 Changed the c_eoy_gre cursor
271 to go of the per_assignments_f
272 as a driving table instead of
273 pay_payroll_actions.
274 12-dec-1999 ahanda 40.41 Added check in c_get_county and
275 c_get_state cursor to bypass the
276 picking up of user defined city tax
277 records.
278 10-dec-1999 achauhan 40.40 In c_get_city cursor added a check
279 to bypass the picking up of user
280 defined city tax records.Since we do
281 not withhold taxes for user defined
282 cities, we do not need to archive them.
283 27-oct-1999 djoshi 40.39 Modified the file to have the
284 fed_informaiton_context = '401K LIMITS'
285 added to the A_SS_EE_wage_BASE and
286 A_SS_EE_WAGE_RATE.
287 25-oct-1999 djoshi 40.37 added the A_SS_EE_WAGE_BASE and
288 A_SS_EE_WAGE rate to archive the data
289 related to bug 983094 and 101435
290 01-sep-1999 achauhan 40.33 While archiving the employer data
291 add the context of pay_payroll_actions
292 to ff_archive_item_contexts.
293 11-aug-1999 achauhan 40.32 Added the call to
294 eoy_archive_gre_data in the
295 eoy_range_cursor procedure. This is
296 being done to handle the situation
297 of archiving employer level data
298 even when there are no employees in
299 a GRE.
300 10-aug-1999 achauhan 40.31 In the archive_data routine,
301 removed the use of payroll_action_id
302 >= 0.
303 04-Aug-1999 VMehta 40.30 Changed eoy_archive_data to improve
304 performance.
305 02-Jun-1999 meshah 40.25 added new cursors in the range and action
306 creation cursors to check for non profit
307 gre's for the state of connecticut.
308
309 08-mar-1999 VMehta 40.24 Added nvl while checking for l_1099R_ind
310 to correct the Louisiana quality jobs program
311 tape processing.
312 26-jan-1999 VMehta 40.23 Modified function report_person_on_tape to
313 return false for all states except California
314 and Massachusetts.
315 24-Jan-1999 VMehta 40.22 805012 Added function report_person_on_tape to perform
316 check for retirees having SIT w/h in california.
317 06-Jan-1999 MReid 40.21 Changed c_eoy_gre cursor to disable
318 business_group_id index on ppa side
319 30-dec-1998 vmehta 40.20 709641 Look at SUI_ER_SUBJ_WHABLE instead of SUI_ER_GROSS
320 for picking up people for SQWL . This makes sure
321 that only people with SUI wages are picked up.
322 27-dec-1998 vmehta 40.19 Corrected the cursor in action creation to get the
323 tax_unit_name from pay_assignment_actions.
324 21-DEC-1998 achauhan 40.18 Changed the cursor in action creation to get the
325 assignments from the pay_assignment_actions table.
326
327 08-DEC-1998 vmehta 40.17 Removed grouping by on assignment_id while creating
328 assignment_ids
329 08-DEC-1998 nbristow 40.16 Updated the c_state cursor to use
330 an exists rather than a join.
331 07-DEC-1998 nbristow 40.15 Resolved some issues introduced by
332 40.13.
333 04-DEC-1998 vmehta 40.14 750802 Changed the cursors/logic to
334 pick up people who live in
335 California for the California SQWL.
336 29-NOV-1998 nbristow 40.13 Changes to the SQWL code,
337 now using pay_us_asg_reporting.
338 25-Sep-1998 vmehta 40.5 Changed the range cursor and
339 the assignment_action creation
340 cursors to support Louisiana
341 Quality Jobs Program Reporting.
342 08-aug-1998 achauhan 40.2 Added the routines for eoy -
343 Year End Pre-Process
344 18-MAY-1998 NBRISTOW 40.1 sqwl_range cursor now checks
345 the tax_unit_id etc.
346 06-MAY-1998 NBRISTOW 40.0 Created.
347 27-OCT-1999 RPOTNURU 110.16 Bug fix 976472
348
349
350 */
351 eoy_gre_range varchar2(4000);
352 eoy_all_range varchar2(4000);
353 g_pact_creation_date Date;
354
355 /* Name : bal_db_item
356 Purpose : Given the name of a balance DB item as would be seen in a fast formula
357 it returns the defined_balance_id of the balance it represents.
358 Arguments :
359 Notes : A defined balance_id is required by the PLSQL balance function.
360 */
361
362 FUNCTION bal_db_item ( p_db_item_name varchar2)
363 return number
364 IS
365
366 /* Get the defined_balance_id for the specified balance DB item. */
367
368 cursor csr_defined_balance is
369 select to_number(UE.creator_id)
370 from ff_user_entities UE,
371 ff_database_items DI
372 where DI.user_name = p_db_item_name
373 and UE.user_entity_id = DI.user_entity_id
374 and Ue.creator_type = 'B'
375 and UE.legislation_code = 'US';
376
377 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
378
379 BEGIN
380 --hr_utility.trace('p_db_item_name is '||p_db_item_name);
381
382 open csr_defined_balance;
383 fetch csr_defined_balance into l_defined_balance_id;
384 if csr_defined_balance%notfound then
385 close csr_defined_balance;
386 raise hr_utility.hr_error;
387 else
388 close csr_defined_balance;
389 end if;
390
391 --hr_utility.trace('l_defined_balance_id is '||to_char(l_defined_balance_id));
392 return (l_defined_balance_id);
393
394 END bal_db_item;
395
396
397 /*****************************************************************************
398 Name : get_payroll_action_info
399 Purpose : This returns the Payroll Action level
400 information for W-2C Archiver.
401 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
402 p_start_date - Start date of Archiver
403 p_end_date - End date of Archiver
404 p_business_group_id - Business Group ID
405 ******************************************************************************/
406 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
407 ,p_end_date out nocopy date
408 ,p_start_date out nocopy date
409 ,p_business_group_id out nocopy number
410 ,p_tax_unit_id out nocopy number
411 ,p_person_id out nocopy number
412 ,p_ssn out nocopy varchar2
413 ,p_asg_set out nocopy number
414 ,p_year out nocopy number
415 ,p_creation_date out nocopy date
416 )
417 IS
418 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
419 select
420 to_number(pay_us_payroll_utils.get_parameter('TRANSFER_GRE',
421 ppa.legislative_parameters)),
422 to_number(pay_us_payroll_utils.get_parameter('PER_ID',ppa.legislative_parameters)),
423 pay_us_payroll_utils.get_parameter('SSN',ppa.legislative_parameters),
424 to_number(pay_us_payroll_utils.get_parameter('ASG_SET',ppa.legislative_parameters)),
425 to_number(pay_us_payroll_utils.get_parameter('YEAR',ppa.legislative_parameters)),
426 effective_date,
427 start_date,
428 business_group_id,
429 creation_date
430 from pay_payroll_actions ppa
431 where ppa.payroll_action_id = cp_payroll_action_id;
432
433 ld_end_date DATE;
434 ld_start_date DATE;
435 ln_business_group_id NUMBER;
436 ln_tax_unit_id NUMBER := 0;
437 ln_person_id NUMBER := 0;
438 ln_asg_set NUMBER := 0;
439 ln_ssn NUMBER;
440 ln_year NUMBER := 0;
441 ln_creation_date DATE;
442
443 BEGIN
444 hr_utility.trace('Entered get_payroll_action_info');
445 open c_payroll_action_info(p_payroll_action_id);
446 hr_utility.trace('Opened c_payroll_action_info ');
447
448 fetch c_payroll_action_info into ln_tax_unit_id,
449 ln_person_id,
450 ln_ssn,
451 ln_asg_set,
452 ln_year,
453 ld_end_date,
454 ld_start_date,
455 ln_business_group_id,
456 ln_creation_date;
457 hr_utility.trace('Fetched c_payroll_action_info ');
458
459 close c_payroll_action_info;
460
461 hr_utility.trace('Closed c_payroll_action_info ');
462 p_end_date := ld_end_date;
463 p_start_date := ld_start_date;
464 p_business_group_id := ln_business_group_id;
465 p_tax_unit_id := ln_tax_unit_id;
466 p_person_id := ln_person_id;
467 p_ssn := ln_ssn;
468 p_asg_set := ln_asg_set;
469 p_year := ln_year;
470 p_creation_date := fnd_date.canonical_to_date(
471 substr(fnd_date.date_to_canonical(ln_creation_date),1,10));
472
473 hr_utility.trace('ld_end_date = ' ||
474 to_char(ld_end_date));
475 hr_utility.trace('ld_start_date = ' ||
476 to_char(ld_start_date));
477 hr_utility.trace('ln_tax_unit_id = ' ||
478 to_char(ln_tax_unit_id));
479 hr_utility.trace('ln_person_id = ' ||
480 to_char(ln_person_id));
481 hr_utility.trace('ln_ssn = ' ||
482 ln_ssn);
483 hr_utility.trace('ln_asg_set = ' ||
484 to_char(ln_asg_set));
485 hr_utility.trace('ln_year = ' ||
486 to_char(ln_year));
487 hr_utility.trace('ln_creation_date = ' ||
488 to_char(ln_creation_date));
489
490 hr_utility.trace('Leaving get_payroll_action_info');
491 EXCEPTION
492 when others then
493 hr_utility.trace('Error in ' ||
494 to_char(sqlcode) || '-' || sqlerrm);
495 raise hr_utility.hr_error;
496
497 END get_payroll_action_info;
498
499 /*********************************************************************
500 Name : get_report_type
501 Purpose : This function returns the report_type
502 ( eg W2C_PRE_PROCESS , YREND) of the archive process.
503 Arguments :
504 Notes :
505 *********************************************************************/
506 FUNCTION get_report_type( p_payroll_action_id in number)
507
508 RETURN VARCHAR2
509
510 IS
511
512 cursor c_get_report_type is
513 select report_type
514 from pay_payroll_actions ppa
515 where ppa.payroll_action_id = p_payroll_action_id;
516
517
518 BEGIN
519
520 hr_utility.trace('g_report_type before call ='||g_report_type);
521
522 open c_get_report_type;
523 fetch c_get_report_type into g_report_type;
524
525 if c_get_report_type%NOTFOUND then
526 raise_application_error(-20001,'get_report_type: Payroll Action data not found');
527 end if;
528 close c_get_report_type;
529 hr_utility.trace('g_report_type after call ='||g_report_type);
530
531 RETURN (g_report_type);
532
533 END get_report_type;
534
535 /*********************************************************************
536 Name : get_puerto_rico_info
537 Purpose : This function returns Y if the GRE for the archive
538 process is a Puerto Rico GRE.
539 It also builds the plsql tale with defined balance
540 id of the Puerto Rico balances.
541 Arguments :
542 Notes :
543 *********************************************************************/
544 FUNCTION get_puerto_rico_info(
545 p_tax_unit_id in number)
546 RETURN VARCHAR2
547 IS
548
549
550 lv_puerto_rico_flag varchar2(1) := 'N';
551 l_step number;
552 ln_count number := 0;
553 lv_balance_name VARCHAR2(80) := '';
554 lv_balance_dimension VARCHAR2(80) := '';
555 lv_user_entity_name ff_user_entities.user_entity_name%TYPE;
556 lv_arch_user_entity_name ff_user_entities.user_entity_name%TYPE;
557 ln_arch_user_entity_id NUMBER;
558 ln_defined_balance_id number := 0;
559 ln_user_entity_id number := 0;
560
561 cursor c_puerto_rico_gre_info (cp_tax_unit_id in number) is
562 select 'Y'
563 from hr_organization_information
564 where organization_id = cp_tax_unit_id
565 and org_information16 = 'P'
566 and org_information_context = 'W2 Reporting Rules';
567
568 /* cursor c_get_puerto_rico_bal is
569 select pbt.balance_name,pdb.defined_balance_id,fue.user_entity_name
570 from ff_user_entities fue,
571 pay_defined_balances pdb,
572 pay_balance_dimensions pbd,
573 pay_balance_types pbt
574 where pbt.balance_name in (
575 'Territory Pension Annuity',
576 'Territory Reimb Expenses',
577 'Territory Taxable Comm',
578 'Territory Taxable Allow',
579 'Territory Taxable TIPS',
580 'Territory Retire Contrib'
581 )
582 and pbd.database_item_suffix= '_PER_GRE_YTD'
583 and pbt.balance_type_id = pdb.balance_type_id
584 and pbd.balance_dimension_id = pdb.balance_dimension_id
585 and fue.creator_id = pdb.defined_balance_id
586 and fue.creator_type = 'B'
587 and ((pbt.legislation_code = 'US' and
588 pbt.business_group_id is null)
589 or (pbt.legislation_code is null and
590 pbt.business_group_id is not null))
591 and ((pbd.legislation_code ='US' and
592 pbd.business_group_id is null)
593 or (pbd.legislation_code is null and
594 pbd.business_group_id is not null)) ;
595 */
596
597 cursor c_get_arch_user_entity (cp_live_database_item in varchar2) is
598 select fue.user_entity_id
599 from ff_user_entities fue
600 where fue.user_entity_name = cp_live_database_item
601 and ((fue.legislation_code = 'US' and
602 fue.business_group_id is null)
603 or (fue.legislation_code is null and
604 fue.business_group_id is not null)) ;
605
606 BEGIN
607 pay_us_archive.ltr_pr_balances.delete;
608
609 BEGIN
610
611 l_step := 19;
612 hr_utility.trace('Checking for Puerto Rico GRE');
613
614 open c_puerto_rico_gre_info(p_tax_unit_id);
615 fetch c_puerto_rico_gre_info into g_puerto_rico_gre;
616 hr_utility.trace('g_puerto_rico_gre = '||g_puerto_rico_gre);
617
618 if c_puerto_rico_gre_info%NOTFOUND then
619 g_puerto_rico_gre := 'N';
620 else
621 /* build the user_entity_id in plsql tables for Puerto Rico */
622
623 BEGIN
624
625 /* Init variables */
626
627 lv_arch_user_entity_name := '';
628 lv_user_entity_name := '';
629 lv_balance_name := '';
630 ln_count := 0 ;
631
632 lv_balance_dimension := '_PER_GRE_YTD';
633
634 pay_us_archive.ltr_pr_balances(1).balance_name := 'Territory Pension Annuity' ;
635 pay_us_archive.ltr_pr_balances(2).balance_name := 'Territory Reimb Expenses' ;
636 pay_us_archive.ltr_pr_balances(3).balance_name := 'Territory Taxable Comm' ;
637 pay_us_archive.ltr_pr_balances(4).balance_name := 'Territory Taxable Allow' ;
638 pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
639 pay_us_archive.ltr_pr_balances(6).balance_name := 'Territory Retire Contrib' ;
640
641 hr_utility.trace('Opening pay_us_payroll_utils.c_get_defined_balance_id');
642
643 loop
644 ln_count := ln_count + 1;
645 IF ln_count > 6 THEN
646 exit;
647 END IF;
648 open pay_us_payroll_utils.c_get_defined_balance_id(pay_us_archive.ltr_pr_balances(ln_count).balance_name,
649 lv_balance_dimension,
650 NULL);
651 -- open c_get_puerto_rico_bal;
652
653 lv_arch_user_entity_name := '';
654 lv_user_entity_name := '';
655 lv_balance_name := '';
656 ln_defined_balance_id := '';
657
658 fetch pay_us_payroll_utils.c_get_defined_balance_id
659 into ln_defined_balance_id,
660 lv_user_entity_name;
661
662 hr_utility.trace('Fetched pay_us_payroll_utils.c_get_defined_balance_id '
663 ||lv_balance_name);
664
665 if pay_us_payroll_utils.c_get_defined_balance_id%NOTFOUND then
666 hr_utility.trace('Going to exit' );
667 exit;
668 end if;
669
670 l_step := 19.1;
671
672 lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
673
674 hr_utility.trace('lv_arch_user_entity_name = '
675 ||lv_arch_user_entity_name);
676 l_step := 19.2;
677 open c_get_arch_user_entity(lv_arch_user_entity_name);
678
679 fetch c_get_arch_user_entity into ln_arch_user_entity_id;
680
681 if c_get_arch_user_entity%notfound then
682 hr_utility.trace('Archived user_entity_id not found');
683 hr_utility.raise_error;
684 end if;
685 close c_get_arch_user_entity;
686 hr_utility.trace('ln_arch_user_entity_id = ' ||
687 to_char(ln_arch_user_entity_id));
688 hr_utility.trace('ln_defined_balance_id = ' ||
689 to_char(ln_defined_balance_id));
690
691 l_step := 19.3;
692
693 -- pay_us_archive.ltr_pr_balances(ln_count).balance_name := lv_balance_name ;
694 pay_us_archive.ltr_pr_balances(ln_count).defined_balance := ln_defined_balance_id ;
695 pay_us_archive.ltr_pr_balances(ln_count).user_entity_id := ln_arch_user_entity_id ;
696 close pay_us_payroll_utils.c_get_defined_balance_id;
697 end loop;
698
699 hr_utility.trace('Closed cursor');
700
701 l_step := 19.4;
702 END; /* Building Puerto Rico user entities */
703
704 end if;
705 close c_puerto_rico_gre_info;
706
707 l_step := 20;
708 END; /* Puerto Rico Info */
709
710 return (g_puerto_rico_gre);
711
712 END get_puerto_rico_info;
713
714 /*********************************************************************
715 Name : get_1099r_info
716 Purpose : This function returns Y if the GRE for the archive
717 process is a 1099R GRE.
718 It also builds the plsql tale with defined balance
719 id of the 1099R balances.
720 Arguments :
721 Notes :
722 *********************************************************************/
723 FUNCTION get_1099r_info(
724 p_tax_unit_id in number)
725 RETURN VARCHAR2
726 IS
727
728 l_step number;
729 ln_count number := 0;
730 lv_balance_name VARCHAR2(500) := '';
731 lv_balance_dimension VARCHAR2(80) := '';
732 lv_user_entity_name ff_user_entities.user_entity_name%TYPE;
733 lv_arch_user_entity_name ff_user_entities.user_entity_name%TYPE;
734 ln_arch_user_entity_id NUMBER;
735 ln_defined_balance_id number := 0;
736 ln_user_entity_id number := 0;
737 lv_1099r_flag varchar2(5) := null;
738 lv_feed_flag varchar2(1) := 'N';
739 lv_all_1099_balances VARCHAR2(500);
740
741 cursor c_1099_gre_info(cp_tax_unit_id in number) is
742 select hoi.org_information2
743 from hr_organization_information hoi
744 where hoi.organization_id = cp_tax_unit_id
745 and hoi.org_information_context = '1099R Magnetic Report Rules';
746
747 cursor c_balance_feed_info(cp_balance_name in varchar2) is
748 select 'Y' from pay_balance_types pbt
749 where pbt.balance_name = cp_balance_name
750 and((pbt.legislation_code = 'US' and
751 pbt.business_group_id is null)
752 or(pbt.legislation_code is null and
753 pbt.business_group_id is not null))
754 and exists (
755 select balance_feed_id from pay_balance_feeds_f feed
756 where feed.balance_type_id = pbt.balance_type_id
757 and((feed.legislation_code = 'US' and
758 feed.business_group_id is null)
759 or(feed.legislation_code is null and
760 feed.business_group_id is not null))
761 );
762
763 /* cursor c_get_1099r_bal is
764 select pbt.balance_name,pdb.defined_balance_id,fue.user_entity_name
765 from ff_user_entities fue,
766 pay_defined_balances pdb,
767 pay_balance_dimensions pbd,
768 pay_balance_types pbt
769 where pbt.balance_name in (
770 'Capital Gain',
771 'EE Contributions Or Premiums',
772 'Other EE Annuity Contract Amt',
773 'Total EE Contributions',
774 'Unrealized Net ER Sec Apprec'
775 )
776 and pbd.database_item_suffix= '_PER_GRE_YTD'
777 and pbt.balance_type_id = pdb.balance_type_id
778 and pbd.balance_dimension_id = pdb.balance_dimension_id
779 and fue.creator_id = pdb.defined_balance_id
780 and fue.creator_type = 'B'
781 and ((pbt.legislation_code = 'US' and
782 pbt.business_group_id is null)
783 or (pbt.legislation_code is null and
784 pbt.business_group_id is not null))
785 and ((pbd.legislation_code ='US' and
786 pbd.business_group_id is null)
787 or (pbd.legislation_code is null and
788 pbd.business_group_id is not null)) ;
789 */
790
791 cursor c_get_arch_user_entity (cp_live_database_item in varchar2) is
792 select fue.user_entity_id
793 from ff_user_entities fue
794 where fue.user_entity_name = cp_live_database_item
795 and ((fue.legislation_code = 'US' and
796 fue.business_group_id is null)
797 or (fue.legislation_code is null and
798 fue.business_group_id is not null)) ;
799
800 BEGIN
801
802 BEGIN
803
804 pay_us_archive.ltr_1099_bal.delete;
805 l_step := 21;
806 hr_utility.trace('Checking for 1099R GRE');
807
808 open c_1099_gre_info(p_tax_unit_id);
809
810 fetch c_1099_gre_info into g_1099R_transmitter_code;
811
812 hr_utility.trace('g_1099R_transmitter_code = '||g_1099R_transmitter_code);
813
814 if c_1099_gre_info%NOTFOUND then
815 g_1099R_transmitter_code := null;
816 end if;
817
818 if g_1099R_transmitter_code is not null then
819
820 /* build the user_entity_id in plsql tables for 1099R GRE */
821
822 BEGIN
823
824 /* Init variables */
825
826 lv_arch_user_entity_name := '';
827 lv_user_entity_name := '';
828 lv_balance_name := '';
829 ln_count := 0 ;
830 ln_defined_balance_id := 0 ;
831
832 lv_balance_dimension := '_PER_GRE_YTD';
833
834 hr_utility.trace('Opening pay_us_payroll_utils.c_get_defined_balance_id');
835
836 pay_us_archive.ltr_1099_bal(1).balance_name := 'Capital Gain' ;
837 pay_us_archive.ltr_1099_bal(2).balance_name := 'EE Contributions Or Premiums' ;
838 pay_us_archive.ltr_1099_bal(3).balance_name := 'Other EE Annuity Contract Amt' ;
839 pay_us_archive.ltr_1099_bal(4).balance_name := 'Total EE Contributions' ;
840 pay_us_archive.ltr_1099_bal(5).balance_name := 'Unrealized Net ER Sec Apprec' ;
841
842
843 loop
844 ln_count := ln_count + 1;
845 IF ln_count > 5 THEN
846 exit;
847 END IF;
848 open pay_us_payroll_utils.c_get_defined_balance_id(pay_us_archive.ltr_1099_bal(ln_count).balance_name,
849 lv_balance_dimension,
850 NULL);
851 -- open c_get_1099r_bal;
852
853
854 lv_arch_user_entity_name := '';
855 lv_user_entity_name := '';
856 lv_balance_name := '';
857 ln_defined_balance_id := 0;
858
859 fetch pay_us_payroll_utils.c_get_defined_balance_id
860 into ln_defined_balance_id
861 ,lv_user_entity_name;
862
863 hr_utility.trace('Fetched pay_us_payroll_utils.c_get_defined_balance_id '
864 ||lv_balance_name);
865
866 if pay_us_payroll_utils.c_get_defined_balance_id%NOTFOUND then
867 exit;
868 end if;
869
870 l_step := 21.1;
871
872 lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
873
874 hr_utility.trace('lv_arch_user_entity_name = '
875 ||lv_arch_user_entity_name);
876 l_step := 21.2;
877 open c_get_arch_user_entity(lv_arch_user_entity_name);
878
879 fetch c_get_arch_user_entity into ln_arch_user_entity_id;
880
881 if c_get_arch_user_entity%notfound then
882 hr_utility.trace('Archived user_entity_id not found');
883 hr_utility.raise_error;
884 end if;
885 close c_get_arch_user_entity;
886 hr_utility.trace('ln_arch_user_entity_id = ' ||
887 to_char(ln_arch_user_entity_id));
888 hr_utility.trace('ln_defined_balance_id = ' ||
889 to_char(ln_defined_balance_id));
890
891 l_step := 21.3;
892
893 -- pay_us_archive.ltr_1099_bal(ln_count).balance_name := lv_balance_name ;
894 pay_us_archive.ltr_1099_bal(ln_count).defined_balance := ln_defined_balance_id ;
895 pay_us_archive.ltr_1099_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
896 close pay_us_payroll_utils.c_get_defined_balance_id;
897
898 end loop;
899 hr_utility.trace('Closed cursor');
900
901 l_step := 21.4;
902
903 END; /* Building 1099R user entities */
904
905
906 /* check whether these balances have been fed or not */
907
908 for j in pay_us_archive.ltr_1099_bal.first ..
909 pay_us_archive.ltr_1099_bal.last loop
910
911 lv_feed_flag := 'N';
912
913 l_step := 21.5;
914 open c_balance_feed_info(pay_us_archive.ltr_1099_bal(j).balance_name);
915
916 fetch c_balance_feed_info into lv_feed_flag;
917
918 l_step := 21.6;
919 hr_utility.trace('lv_feed_flag = '||lv_feed_flag);
920
921 if c_balance_feed_info%NOTFOUND then
922 lv_feed_flag := 'N';
923 l_step := 21.7;
924 pay_us_archive.ltr_1099_bal(j).feed_info := 'N';
925
926 else
927
928 l_step := 21.8;
929 pay_us_archive.ltr_1099_bal(j).feed_info := lv_feed_flag;
930 end if;
931 close c_balance_feed_info ;
932
933 l_step := 21.9;
934
935 end loop;
936
937 end if; /*g_1099R_transmitter_code */
938
939 close c_1099_gre_info;
940
941 l_step := 22;
942
943 END; /* 1099R GRE Info */
944
945 return (g_1099R_transmitter_code);
946
947 END get_1099r_info;
948
949 /*********************************************************************
950 Name : get_pre_tax_info
951 Builds the plsql table with box 12 info
952 Arguments :
953 Notes :
954 *********************************************************************/
955 FUNCTION get_pre_tax_info(
956 p_tax_unit_id in number,
957 p_business_group_id in number)
958 RETURN VARCHAR2
959 IS
960
961 l_step number;
962 ln_count number := 0;
963 lv_balance_name VARCHAR2(80) := '';
964 lv_balance_dimension VARCHAR2(80) := '';
965 lv_user_entity_name ff_user_entities.user_entity_name%TYPE;
966 lv_arch_user_entity_name ff_user_entities.user_entity_name%TYPE;
967 ln_arch_user_entity_id NUMBER;
968 ln_defined_balance_id number := 0;
969 ln_user_entity_id number := 0;
970
971 cursor c_balance is
972 /*
973 select meaning
974 from fnd_common_lookups
975 where application_id = 801
976 and lookup_type = 'W2 BOX 12'
977 and enabled_flag = 'Y'
978 */
979 select meaning
980 from fnd_lookup_values flv,
981 fnd_lookup_types flt
982 where flv.lookup_type = flt.lookup_type
983 and application_id = 801
984 and flt.lookup_type = 'W2 BOX 12'
985 and enabled_flag = 'Y'
986 and language = 'US';
987
988 /* cursor c_get_defined_balance_id (
989 cp_balance_name in varchar2,
990 cp_balance_dimension in varchar2,
991 cp_business_group_id in number ) is
992 select pdb.defined_balance_id,fue.user_entity_name
993 from ff_user_entities fue,
994 pay_defined_balances pdb,
995 pay_balance_dimensions pbd,
996 pay_balance_types pbt
997 where pbt.balance_name = cp_balance_name
998 and pbd.database_item_suffix= cp_balance_dimension
999 and pbt.balance_type_id = pdb.balance_type_id
1000 and pbd.balance_dimension_id = pdb.balance_dimension_id
1001 and fue.creator_id = pdb.defined_balance_id
1002 and fue.creator_type = 'B'
1003 and ((pbt.legislation_code = 'US' and
1004 pbt.business_group_id is null)
1005 or (pbt.legislation_code is null and
1006 pbt.business_group_id = cp_business_group_id))
1007 and ((pbd.legislation_code ='US' and
1008 pbd.business_group_id is null)
1009 or (pbd.legislation_code is null and
1010 pbd.business_group_id = cp_business_group_id)) ;
1011 */
1012 cursor c_get_arch_user_entity (cp_live_database_item in varchar2) is
1013 select fue.user_entity_id
1014 from ff_user_entities fue
1015 where fue.user_entity_name = cp_live_database_item
1016 and ((fue.legislation_code = 'US' and
1017 fue.business_group_id is null)
1018 or (fue.legislation_code is null and
1019 fue.business_group_id is not null)) ;
1020
1021
1022
1023 BEGIN
1024 pay_us_archive.ltr_pre_tax_bal.delete;
1025
1026 hr_utility.trace('p_business_group_id = '||to_char(p_business_group_id));
1027 hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
1028 BEGIN
1029
1030 l_step := 14;
1031
1032 hr_utility.trace('Opening c_balance cursor to get lookup codes');
1033
1034 lv_balance_dimension := '_PER_GRE_YTD';
1035 g_pre_tax_info := 'Y' ;
1036 open c_balance;
1037
1038 loop
1039 lv_arch_user_entity_name := '';
1040 lv_user_entity_name := '';
1041 ln_defined_balance_id := 0 ;
1042
1043 fetch c_balance into lv_balance_name ;
1044 hr_utility.trace('Fetched c_balance '||lv_balance_name);
1045 if c_balance%NOTFOUND then
1046 hr_utility.trace('Going to exit' );
1047 exit;
1048 end if;
1049
1050 l_step := 15;
1051 open pay_us_payroll_utils.c_get_defined_balance_id(lv_balance_name,
1052 lv_balance_dimension,
1053 p_business_group_id);
1054
1055 fetch pay_us_payroll_utils.c_get_defined_balance_id
1056 into ln_defined_balance_id,
1057 lv_user_entity_name;
1058
1059 lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
1060
1061 hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1062
1063 if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1064
1065 hr_utility.trace('Into found loop of Box 12 ');
1066 hr_utility.trace('ln_defined_balance_id = '||to_char(ln_defined_balance_id));
1067
1068 l_step := 16;
1069 open c_get_arch_user_entity(lv_arch_user_entity_name);
1070
1071 fetch c_get_arch_user_entity into ln_arch_user_entity_id;
1072
1073 if c_get_arch_user_entity%notfound then
1074 hr_utility.trace('Archived user_entity_id not found');
1075 hr_utility.raise_error;
1076 end if;
1077 close c_get_arch_user_entity;
1078 hr_utility.trace('ln_arch_user_entity_id = ' ||
1079 to_char(ln_arch_user_entity_id));
1080 hr_utility.trace('ln_defined_balance_id = ' ||
1081 to_char(ln_defined_balance_id));
1082
1083 l_step := 17;
1084 ln_count := ln_count + 1;
1085
1086
1087 pay_us_archive.ltr_pre_tax_bal(ln_count).balance_name := lv_balance_name ;
1088 pay_us_archive.ltr_pre_tax_bal(ln_count).defined_balance := ln_defined_balance_id ;
1089 pay_us_archive.ltr_pre_tax_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
1090
1091 end if;
1092 close pay_us_payroll_utils.c_get_defined_balance_id;
1093
1094 end loop;
1095 close c_balance;
1096
1097 hr_utility.trace('Closed cursor');
1098
1099 l_step := 18;
1100 END; /* Box 12 Info */
1101
1102 return (g_pre_tax_info);
1103
1104 END get_pre_tax_info;
1105
1106
1107 ------------------------------------------------------------------------
1108 /* Name : eoy_action_creation
1109 Purpose : This creates the assignment actions for a specific chunk
1110 of people to be archived by the year end pre-process.
1111 Arguments :
1112 Notes :
1113 */
1114 ------------------------------------------------------------------------
1115 PROCEDURE eoy_action_creation(pactid in number,
1116 stperson in number,
1117 endperson in number,
1118 chunk in number)
1119 IS
1120
1121 /* Variables used to hold the select columns from the SQL statement.*/
1122 l_person_id number;
1123 l_tax_unit_id number;
1124
1125 l_eoy_tax_unit_id number;
1126 l_effective_date date;
1127 l_bus_group_id number;
1128
1129 l_primary_asg pay_assignment_actions.assignment_id%type;
1130 l_bal_aaid pay_assignment_actions.assignment_action_id%type;
1131
1132 /* Variables used to check if RANGE_PERSON_ID is enabled */
1133 l_range_person BOOLEAN;
1134
1135 /* Variables used to hold the current values returned within the loop for
1136 checking against the new values returned from within the loop on the
1137 next iteration. */
1138 l_prev_person_id per_people_f.person_id%type;
1139 l_prev_tax_unit_id hr_organization_units.organization_id%type;
1140
1141 /* Variable to hold the jurisdiction code used as a context for state
1142 reporting. */
1143 l_jurisdiction_code varchar2(30);
1144
1145 /* general process variables */
1146 l_value number;
1147 l_year_start date;
1148 l_year_end date;
1149 lockingactid number;
1150 /* message variables */
1151 l_mesg varchar2(100);
1152 l_record_name varchar2(100);
1153
1154 /* For Year End Preprocess we have to archive the assignments
1155 belonging to a GRE including the 1099R GRE */
1156 CURSOR c_eoy_gre(cp_period_start in date
1157 ,cp_period_end in date
1158 ,cp_tax_unit_id in number
1159 ,cp_business_group_id in number
1160 ,cp_start_person_id in number
1161 ,cp_end_person_id in number) is
1162 SELECT DISTINCT
1163 ASG.person_id person_id
1164 FROM per_all_assignments_f ASG,
1165 pay_all_payrolls_f PPY
1166 WHERE exists
1167 (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
1168 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
1169 'x'
1170 from pay_payroll_actions ppa,
1171 pay_assignment_actions paa
1172 where ppa.effective_date between cp_period_start
1173 and cp_period_end
1174 and ppa.action_type in ('R','Q','V','B','I')
1175 and ppa.action_status = 'C'
1176 and ppa.business_group_id + 0 = cp_business_group_id
1177 and ppa.payroll_action_id = paa.payroll_action_id
1178 and paa.tax_unit_id = cp_tax_unit_id
1179 and paa.action_status = 'C'
1180 and paa.assignment_id = ASG.assignment_id
1181 and ppa.business_group_id = ASG.business_group_id +0
1182 and ppa.effective_date between ASG.effective_start_date
1183 and ASG.effective_end_date)
1184 AND ASG.person_id between cp_start_person_id and cp_end_person_id
1185 AND ASG.assignment_type = 'E'
1186 AND PPY.payroll_id = ASG.payroll_id;
1187
1188 CURSOR c_eoy_gre_person_on(cp_period_start in date
1189 ,cp_period_end in date
1190 ,cp_tax_unit_id in number
1191 ,cp_business_group_id in number
1192 ,cp_payroll_Action_id in number
1193 ,cp_chunk_number in number) is
1194 select DISTINCT
1195 asg.person_id person_id
1196 from pay_population_ranges ppr,
1197 per_all_assignments_f asg,
1198 pay_all_payrolls_f ppy
1199 where exists
1200 (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
1201 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
1202 'x'
1203 from pay_payroll_actions ppa,
1204 pay_assignment_actions paa
1205 where ppa.effective_date between cp_period_start
1206 and cp_period_end
1207 and ppa.action_type in ('R','Q','V','B','I')
1208 and ppa.action_status = 'C'
1209 and ppa.business_group_id + 0 = cp_business_group_id
1210 and ppa.payroll_action_id = paa.payroll_action_id
1211 and paa.tax_unit_id = cp_tax_unit_id
1212 and paa.action_status = 'C'
1213 and paa.assignment_id = asg.assignment_id
1214 and ppa.business_group_id = asg.business_group_id +0
1215 and ppa.effective_date between asg.effective_start_date
1216 and asg.effective_end_date)
1217 and asg.person_id = ppr.person_id
1218 and ppr.payroll_Action_id = cp_payroll_Action_id
1219 and ppr.chunk_number = cp_chunk_number
1220 and asg.assignment_type = 'E'
1221 and ppy.payroll_id = asg.payroll_id;
1222
1223 /* Get the primary assignment for the given person_id */
1224 CURSOR c_get_asg_id(cp_person_id in number
1225 ,cp_period_start in date
1226 ,cp_period_end in date) IS
1227 SELECT assignment_id
1228 from per_all_assignments_f paf
1229 where person_id = cp_person_id
1230 and primary_flag = 'Y'
1231 and assignment_type = 'E'
1232 and paf.effective_start_date = (select max(paf2.effective_start_date)
1233 from per_all_assignments_f paf2
1234 where paf2.primary_flag = 'Y'
1235 and paf2.assignment_type = 'E'
1236 and paf2.effective_start_date <= cp_period_end
1237 and paf2.effective_end_date >= cp_period_start
1238 and paf2.person_id = paf.person_id
1239 ) /* Bug 4163949 - Added above sub query */
1240 ORDER BY assignment_id desc;
1241
1242 -- Bug 5696031
1243 -- Modified the cursor to remove the order by clause that was there before.
1244 -- The select clause has been modified to get the Assignment Action ID
1245 -- associated with Maximum Action Sequence
1246 /* Get the latest assignment for the given person_id */
1247 CURSOR c_get_latest_asg(cp_person_id number
1248 ,cp_tax_unit_id in number
1249 ,cp_period_start in date
1250 ,cp_period_end in date) IS
1251 select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')
1252 ||lpad(paa.assignment_action_id,15,'0')),16))
1253 from pay_assignment_actions paa,
1254 per_all_assignments_f paf,
1255 pay_payroll_actions ppa,
1256 pay_action_classifications pac
1257 where paf.person_id = cp_person_id
1258 and paa.assignment_id = paf.assignment_id
1259 and paa.tax_unit_id = cp_tax_unit_id
1260 and paa.payroll_action_id = ppa.payroll_action_id
1261 and ppa.action_type = pac.action_type
1262 and pac.classification_name = 'SEQUENCED'
1263 and ppa.effective_date +0 between paf.effective_start_date
1264 and paf.effective_end_date
1265 and ppa.effective_date +0 between cp_period_start
1266 and cp_period_end
1267 and ((nvl(paa.run_type_id, ppa.run_type_id) is null and
1268 paa.source_action_id is null)
1269 or (nvl(paa.run_type_id, ppa.run_type_id) is not null and
1270 paa.source_action_id is not null )
1271 or (ppa.action_type = 'V' and ppa.run_type_id is null and
1272 paa.run_type_id is not null and
1273 paa.source_action_id is null));
1274
1275 BEGIN
1276
1277 /* Get the report type, report qualifier, business group id and the
1278 gre for which the archiving has to be done */
1279 -- hr_utility.trace_on(null,'yepp');
1280
1281 hr_utility.trace('In eoy_action_creation');
1282 hr_utility.trace('getting payroll action data');
1283
1284 select effective_date,
1285 business_group_id,
1286 to_number(substr(legislative_parameters,
1287 instr(legislative_parameters,'TRANSFER_GRE=')
1288 + length('TRANSFER_GRE=')))
1289 into l_effective_date,
1290 l_bus_group_id,
1291 l_eoy_tax_unit_id
1292 from pay_payroll_actions
1293 where payroll_action_id = pactid;
1294
1295 l_year_start := trunc(l_effective_date, 'Y');
1296 l_year_end := add_months(trunc(l_effective_date, 'Y'),12) -1;
1297 hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1298 hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1299
1300 l_range_person := pay_ac_utility.range_person_on(
1301 p_report_type => 'YREND'
1302 ,p_report_format => 'YEPARCH'
1303 ,p_report_qualifier => 'FED'
1304 ,p_report_category => 'RT');
1305
1306 if l_eoy_tax_unit_id <> 99999 then
1307 if l_range_person then
1308 open c_eoy_gre_person_on(l_year_start
1309 ,l_year_end
1310 ,l_eoy_tax_unit_id
1311 ,l_bus_group_id
1312 ,pactid
1313 ,chunk);
1314 else
1315 open c_eoy_gre(l_year_start
1316 ,l_year_end
1317 ,l_eoy_tax_unit_id
1318 ,l_bus_group_id
1319 ,stperson
1320 ,endperson);
1321 end if;
1322
1323 loop
1324 if l_range_person then
1325 fetch c_eoy_gre_person_on into l_person_id;
1326 hr_utility.trace('Person ID = '|| to_char(l_person_id));
1327 exit when c_eoy_gre_person_on%NOTFOUND;
1328 else
1329 fetch c_eoy_gre into l_person_id;
1330 hr_utility.trace('Person ID = '|| to_char(l_person_id));
1331 exit when c_eoy_gre%NOTFOUND;
1332 end if;
1333
1334 l_tax_unit_id := l_eoy_tax_unit_id;
1335
1336 /* If the new row is the same as the previous row according to the way
1337 the rows are grouped then discard the row ie. grouping by GRE
1338 requires a single row for each person / GRE combination. */
1339 hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
1340 hr_utility.trace('previous tax unit id is '||
1341 to_char(l_prev_tax_unit_id));
1342
1343 if (l_person_id = l_prev_person_id and
1344 l_tax_unit_id = l_prev_tax_unit_id) then
1345 null;
1346 else
1347 hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1348 hr_utility.trace('person is '|| to_char(l_person_id));
1349
1350 /* Have a new unique row according to the way the rows are grouped.
1351 The inclusion of the person is dependent on having a non zero
1352 balance.
1353 If the balance is non zero then an assignment action is created to
1354 indicate their inclusion in the magnetic tape report. */
1355
1356 open c_get_latest_asg(l_person_id
1357 ,l_tax_unit_id
1358 ,l_year_start
1359 ,l_year_end);
1360 fetch c_get_latest_asg into l_bal_aaid;
1361 if c_get_latest_asg%notfound then
1362 l_bal_aaid := -9999;
1363 close c_get_latest_asg;
1364 raise_application_error(-20001,'Balance Assignment Action ' ||
1365 'does not exist for : ' ||
1366 to_char(l_person_id));
1367 end if;
1368 hr_utility.trace('l_bal_aaid in action creation code'||to_char(l_bal_aaid));
1369 if c_get_latest_asg%ISOPEN then
1370 close c_get_latest_asg;
1371 end if;
1372
1373 if l_bal_aaid <> -9999 then /* Assignment action in year */
1374 /* Set up the context of tax unit id */
1375 hr_utility.trace('Setting context');
1376 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1377
1378 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1379 hr_utility.trace('defined_balance_id = '||
1380 to_char(bal_db_item('GROSS_EARNINGS_PER_GRE_YTD')));
1381
1382 l_value := nvl(pay_balance_pkg.get_value
1383 (p_defined_balance_id
1384 => bal_db_item('GROSS_EARNINGS_PER_GRE_YTD'),
1385 p_assignment_action_id => l_bal_aaid),0);
1386
1387 if l_value = 0 then
1388 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1389 hr_utility.trace('defined_balance_id = '||
1390 to_char(bal_db_item('W2_NONTAX_SICK_PER_GRE_YTD')));
1391
1392 l_value := nvl(pay_balance_pkg.get_value
1393 (p_defined_balance_id
1394 => bal_db_item('W2_NONTAX_SICK_PER_GRE_YTD'),
1395 p_assignment_action_id => l_bal_aaid),0);
1396
1397 if l_value = 0 then
1398 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1399 hr_utility.trace('defined_balance_id = '||
1400 to_char(bal_db_item('W2_EXPENSE_REIMB_PER_GRE_YTD')));
1401
1402 l_value := nvl(pay_balance_pkg.get_value
1403 (p_defined_balance_id
1404 => bal_db_item('W2_EXPENSE_REIMB_PER_GRE_YTD'),
1405 p_assignment_action_id => l_bal_aaid),0);
1406
1407 if l_value = 0 then
1408 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1409 hr_utility.trace('defined_balance_id = '||
1410 to_char(bal_db_item('W2_QUAL_MOVE_PER_GRE_YTD')));
1411
1412 l_value := nvl(pay_balance_pkg.get_value
1413 (p_defined_balance_id
1414 => bal_db_item('W2_QUAL_MOVE_PER_GRE_YTD'),
1415 p_assignment_action_id => l_bal_aaid),0);
1416
1417 if l_value = 0 then
1418 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1419 hr_utility.trace('defined_balance_id = '||
1420 to_char(bal_db_item('W2_NO_GROSS_EARNINGS_PER_GRE_YTD')));
1421
1422 l_value := nvl(pay_balance_pkg.get_value
1423 (p_defined_balance_id
1424 => bal_db_item('W2_NO_GROSS_EARNINGS_PER_GRE_YTD'),
1425 p_assignment_action_id => l_bal_aaid),0);
1426
1427 end if; /* W2_NO_GROSS_EARNINGS_PER_GRE_YTD */
1428 end if; /* W2_QUAL_MOVE_PER_GRE_YTD */
1429 end if; /* W2_EXPENSE_REIMB_PER_GRE_YTD */
1430 end if; /* W2_NONTAX_SICK_PER_GRE_YTD */
1431
1432 if l_value <> 0 then
1433 /* Get the primary assignment */
1434 open c_get_asg_id(l_person_id
1435 ,l_year_start
1436 ,l_year_end);
1437 fetch c_get_asg_id into l_primary_asg;
1438 if c_get_asg_id%NOTFOUND then
1439 close c_get_asg_id;
1440 /* Added to show message in PAY_MESSAGE_LINES */
1441 l_mesg := 'Primary Assignment Not Found for Person '|| to_char(l_person_id);
1442 l_record_name := 'Person '|| to_char(l_person_id);
1443 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','A');
1444 pay_core_utils.push_token('record_name',l_record_name);
1445 pay_core_utils.push_token('description',l_mesg);
1446 raise_application_error(-20001,'Primary Assignment Not Found ' ||
1447 'for person ' || to_char(l_person_id));
1448 end if;
1449 if c_get_asg_id%ISOPEN then
1450 close c_get_asg_id;
1451 end if;
1452
1453 /* Create the assignment action to represnt the person / tax unit
1454 combination. */
1455 select pay_assignment_actions_s.nextval
1456 into lockingactid
1457 from dual;
1458
1459 /* Insert into pay_assignment_actions. */
1460 hr_utility.trace('creating asg action');
1461
1462 hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1463 pactid,chunk,l_tax_unit_id);
1464
1465 /* Update the serial number column with the person id
1466 so that the mag routine and the W2 view will not have
1467 to do an additional checking against the assignment
1468 table */
1469 hr_utility.trace('updating asg action');
1470
1471 update pay_assignment_actions aa
1472 set aa.serial_number = to_char(l_person_id)
1473 where aa.assignment_action_id = lockingactid;
1474
1475 end if; /* l_value <> 0 */
1476 end if; /* l_bal_aaid <> -9999 */
1477 end if; /* l_person and l_tax_unit are different */
1478
1479 /* Record the current values for the next time around the loop. */
1480 l_prev_person_id := l_person_id;
1481 l_prev_tax_unit_id := l_tax_unit_id;
1482 end loop;
1483 if l_range_person then
1484 close c_eoy_gre_person_on;
1485 else
1486 close c_eoy_gre;
1487 end if;
1488 end if;
1489
1490 END eoy_action_creation;
1491
1492
1493 /* Name : get_user_entity_id
1494 Purpose : This gets the user_entity_id for a specific database item name.
1495 Arguments : p_dbi_name -> database item name.
1496 Notes :
1497 */
1498 FUNCTION get_user_entity_id (p_dbi_name in varchar2)
1499 return number is
1500 l_user_entity_id number;
1501
1502 begin
1503 --hr_utility.trace('p_dbi_name is '||p_dbi_name);
1504
1505 select fdi.user_entity_id
1506 into l_user_entity_id
1507 from ff_database_items fdi,
1508 ff_user_entities fue
1509 where fdi.user_name = p_dbi_name
1510 and fue.user_entity_id = fdi.user_entity_id
1511 and fue.legislation_code = 'US';
1512
1513 --hr_utility.trace('user_entity_id is '||to_char(l_user_entity_id));
1514 return l_user_entity_id;
1515
1516 exception
1517 when others then
1518 raise_application_error(-20001,'Error getting user_entity_id for DBI : '
1519 ||p_dbi_name||' - '||to_char(sqlcode) || '-' || sqlerrm);
1520 end get_user_entity_id;
1521
1522 procedure create_archive (p_user_entity_id in pay_us_archive.number_data_type_table,
1523 p_context1 in number,
1524 p_value in pay_us_archive.char240_data_type_table,
1525 p_sequence in pay_us_archive.number_data_type_table,
1526 p_context in pay_us_archive.char240_data_type_table,
1527 p_context_id in pay_us_archive.number_data_type_table,
1528 p_archive_level in varchar2 default 'EE') is
1529
1530 l_step number := 0;
1531 l_tax_context_id number := 0;
1532 l_jursd_context_id number := 0;
1533 l_jd varchar2(11) := null;
1534 l_tuid number := 0;
1535 l_rowid_found varchar2(240);
1536 l_archive_type ff_archive_items.archive_type%type;
1537 l_rearch boolean :=FALSE;
1538 l_fed_state_value varchar2(240);
1539 l_old_value varchar2(240):= null;
1540 l_new_value varchar2(240):= null;
1541
1542 begin
1543
1544 l_step := 1;
1545
1546 if p_archive_level in('ER','ER REARCH') then /* Employer Level Archive */
1547
1548 if p_archive_level = 'ER REARCH' THEN
1549
1550 l_rearch := TRUE;
1551
1552 end if;
1553
1554 l_archive_type := 'PA';
1555 select context_id
1556 into l_tax_context_id
1557 from ff_contexts
1558 where context_name = 'TAX_UNIT_ID';
1559
1560 l_step := 2;
1561
1562 select context_id
1563 into l_jursd_context_id
1564 from ff_contexts
1565 where context_name = 'JURISDICTION_CODE';
1566
1567 l_step := 3;
1568 for i in p_sequence.first .. p_sequence.last
1569 loop
1570 if p_context_id(i) = l_jursd_context_id then
1571 l_jd := p_context(i);
1572 elsif p_context_id(i) = l_tax_context_id then
1573 l_tuid := p_context(i);
1574 end if;
1575 end loop;
1576
1577
1578 if l_jd is null then /* Federal Level Archive */
1579
1580 l_fed_state_value := 'Federal';
1581
1582 l_step := 4;
1583 for j in p_user_entity_id.first .. p_user_entity_id.last
1584 loop
1585 begin
1586 select rowid,fai.value into l_rowid_found,l_old_value
1587 from ff_archive_items fai
1588 where user_entity_id = p_user_entity_id(j)
1589 and context1 = p_context1
1590 and exists (select 'x' from ff_archive_item_contexts faic
1591 where fai.archive_item_id = faic.archive_item_id
1592 and faic.context_id = l_tax_context_id
1593 and faic.context = l_tuid );
1594 exception when no_data_found then
1595 l_rowid_found := null;
1596 l_old_value := Null;
1597 end;
1598
1599 hr_utility.trace('l_old_value = '||l_old_value);
1600
1601 IF l_rowid_found IS NOT NULL THEN
1602
1603 IF ( l_rearch
1604 AND (nvl( p_value(j),'-*9999999') <> nvl(l_old_value ,'-*9999999')
1605 )) THEN
1606
1607 BEGIN
1608 hr_utility.trace('B4 update of value ');
1609 update ff_archive_items
1610 set value = p_value(j)
1611 where rowid = l_rowid_found;
1612
1613 EXCEPTION WHEN OTHERS THEN
1614 hr_utility.trace('In others error for update -200 ');
1615 END;
1616
1617 l_new_value := p_value(j);
1618 hr_utility.trace('Updating Non null value in re-arch with new value = '
1619 ||p_value(j));
1620
1621 /* calling the print procedure only if we have not null update */
1622
1623 pay_us_er_rearch.print_er_rearch_data( p_user_entity_id(j),
1624 l_fed_state_value,
1625 l_old_value,
1626 l_new_value);
1627
1628
1629 ELSE
1630
1631 /* Smart archive call from any other Solution */
1632 /* here requirement is that update only if null */
1633
1634 IF (l_old_value is NULL
1635 AND p_value(j) is not NULL
1636 AND (not l_rearch) ) THEN
1637
1638 BEGIN
1639 update ff_archive_items
1640 set value = p_value(j)
1641 where rowid = l_rowid_found;
1642 EXCEPTION WHEN OTHERS THEN
1643 hr_utility.trace('In others error for update -210 ');
1644 END;
1645
1646 l_new_value := p_value(j);
1647 hr_utility.trace('Updating for other process new value = '||p_value(j));
1648
1649 END IF; /* smart archive call */
1650
1651 END IF; /* End l_rearch */
1652
1653 ELSE /* Archive row does not exist */
1654
1655 hr_utility.trace('No rowid found ');
1656 insert into ff_archive_items
1657 (ARCHIVE_ITEM_ID,
1658 USER_ENTITY_ID,
1659 CONTEXT1,
1660 VALUE,
1661 ARCHIVE_TYPE)
1662 values
1663 (ff_archive_items_s.nextval,
1664 p_user_entity_id(j),
1665 p_context1,
1666 p_value(j),
1667 l_archive_type);
1668
1669 l_step := 8;
1670
1671 l_new_value := p_value(j);
1672
1673 for i in p_sequence.first .. p_sequence.last
1674 loop
1675 insert into ff_archive_item_contexts
1676 (ARCHIVE_ITEM_ID,
1677 SEQUENCE_NO,
1678 CONTEXT,
1679 CONTEXT_ID)
1680 values
1681 (ff_archive_items_s.currval,
1682 p_sequence(i),
1683 p_context(i),
1684 p_context_id(i));
1685 end loop;
1686 END IF; /* End if of if l_rowid_found is not null */
1687
1688 /* Re-intializing value to null */
1689
1690 l_old_value := null;
1691 l_new_value := null;
1692 hr_utility.trace('assigned null values before loop end');
1693
1694 END LOOP; /* for j in p_user_entity_id.firs */
1695
1696 ELSE /* State Level Employer Archive */
1697
1698 l_step := 5;
1699 l_fed_state_value := 'State';
1700 for j in p_user_entity_id.first .. p_user_entity_id.last
1701 loop
1702 begin
1703 select rowid,fai.value into l_rowid_found,l_old_value
1704 from ff_archive_items fai
1705 where user_entity_id = p_user_entity_id(j)
1706 and context1 = p_context1
1707 and exists (select 'x' from ff_archive_item_contexts faic
1708 where fai.archive_item_id = faic.archive_item_id
1709 and faic.context_id = l_tax_context_id
1710 and faic.context = l_tuid )
1711 and exists (select 'x' from ff_archive_item_contexts faic
1712 where fai.archive_item_id = faic.archive_item_id
1713 and faic.context_id = l_jursd_context_id
1714 and faic.context = l_jd );
1715 exception when no_data_found then
1716 l_rowid_found := null;
1717 l_old_value := Null;
1718 end;
1719
1720
1721 if l_rowid_found is not null then
1722
1723 if l_old_value is null then
1724
1725 update ff_archive_items fai
1726 set value = p_value(j)
1727 where rowid = l_rowid_found;
1728 l_new_value := p_value(j);
1729
1730 else
1731
1732 if l_rearch then
1733
1734 update ff_archive_items fai
1735 set value = p_value(j)
1736 where rowid = l_rowid_found;
1737
1738 l_new_value := p_value(j);
1739
1740 end if;
1741
1742 end if;
1743
1744
1745 else
1746 insert into ff_archive_items
1747 (ARCHIVE_ITEM_ID,
1748 USER_ENTITY_ID,
1749 CONTEXT1,
1750 VALUE,
1751 ARCHIVE_TYPE)
1752 values
1753 (ff_archive_items_s.nextval,
1754 p_user_entity_id(j),
1755 p_context1,
1756 p_value(j),
1757 l_archive_type);
1758
1759 l_step := 8;
1760
1761 l_new_value := p_value(j);
1762
1763 for i in p_sequence.first .. p_sequence.last
1764 loop
1765 insert into ff_archive_item_contexts
1766 (ARCHIVE_ITEM_ID,
1767 SEQUENCE_NO,
1768 CONTEXT,
1769 CONTEXT_ID)
1770 values
1771 (ff_archive_items_s.currval,
1772 p_sequence(i),
1773 p_context(i),
1774 p_context_id(i));
1775 end loop;
1776 end if;
1777
1778 if l_rearch then
1779
1780 if (l_old_value is null and l_new_value is not null)
1781 or (nvl(l_old_value,'-*9999999') <> nvl(l_new_value,'-*9999999') ) then
1782
1783 pay_us_er_rearch.print_er_rearch_data( p_user_entity_id(j),
1784 l_fed_state_value,
1785 l_old_value,
1786 l_new_value);
1787
1788 end if;
1789
1790 end if;
1791
1792 end loop;
1793 end if;
1794 else /* EE Archive */
1795 l_step := 9;
1796
1797 for j in p_user_entity_id.first .. p_user_entity_id.last
1798 loop
1799 insert into ff_archive_items
1800 (ARCHIVE_ITEM_ID,
1801 USER_ENTITY_ID,
1802 CONTEXT1,
1803 VALUE)
1804 values
1805 (ff_archive_items_s.nextval,
1806 p_user_entity_id(j),
1807 p_context1,
1808 p_value(j));
1809
1810 l_step := 10;
1811
1812 for i in p_sequence.first .. p_sequence.last
1813 loop
1814 insert into ff_archive_item_contexts
1815 (ARCHIVE_ITEM_ID,
1816 SEQUENCE_NO,
1817 CONTEXT,
1818 CONTEXT_ID)
1819 values
1820 (ff_archive_items_s.currval,
1821 p_sequence(i),
1822 p_context(i),
1823 p_context_id(i));
1824 end loop;
1825 end loop;
1826 end if; /* p_archive_level is EE */
1827 exception
1828 when others then
1829 hr_utility.trace('Error in create archive at step '||to_char(l_step)||' - '
1830 || to_char(sqlcode));
1831 raise_application_error(-20001,'Error in create archive at step '
1832 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
1833 end create_archive;
1834
1835 /* Name : eoy_archive_gre_data
1836 Purpose : This performs the US specific employer data archiving.
1837 Arguments :
1838 Notes :
1839 */
1840
1841 procedure eoy_archive_gre_data(p_payroll_action_id in number,
1842 p_tax_unit_id in number,
1843 p_jd_type in varchar2 default 'ALL',
1844 p_state_code in varchar2 default 'ALL')
1845 is
1846
1847 l_user_entity_id_tab pay_us_archive.number_data_type_table;
1848 l_tax_context_id number;
1849 l_jursd_context_id number;
1850 l_value1 varchar2(240);
1851 l_value2 varchar2(240);
1852 l_value3 varchar2(240);
1853 l_value4 varchar2(240);
1854 l_value5 varchar2(240);
1855 l_value6 varchar2(240);
1856 l_value7 varchar2(240);
1857 l_value8 varchar2(240);
1858 l_value9 varchar2(240);
1859 l_value10 varchar2(240);
1860 l_value11 varchar2(240);
1861 l_value12 varchar2(240);
1862 l_value13 varchar2(240);
1863 l_value14 varchar2(240);
1864 l_value15 varchar2(240);
1865 l_value16 varchar2(240);
1866 l_value17 varchar2(240);
1867 l_value18 varchar2(240);
1868 l_value19 varchar2(240);
1869 l_value20 varchar2(240);
1870 l_value_tab pay_us_archive.char240_data_type_table;
1871 l_sit_uid number;
1872 l_sui_uid number;
1873 l_fips_uid number;
1874 l_seq_tab pay_us_archive.number_data_type_table;
1875 l_context_id_tab pay_us_archive.number_data_type_table;
1876 l_context_val_tab pay_us_archive.char240_data_type_table;
1877 l_arch_gre_step number := 0;
1878 l_archive_level varchar2(240);
1879
1880 ld_end_date DATE;
1881 ld_start_date DATE;
1882 ln_business_group_id NUMBER;
1883 ln_person_id NUMBER := 0;
1884 ln_asg_set NUMBER := 0;
1885 ln_ssn NUMBER;
1886 ln_year NUMBER := 0;
1887 l_tax_unit_id NUMBER;
1888 l_w2_profile_option VARCHAR2(10);
1889
1890 cursor c_get_state_code is
1891 select state_code
1892 from pay_us_states pus,
1893 hr_organization_information hoi
1894 where hoi.organization_id = p_tax_unit_id
1895 and hoi.org_information_context || '' = 'State Tax Rules'
1896 and pus.state_abbrev = hoi.org_information1
1897 and pus.state_code = decode(p_state_code,'ALL',pus.state_code,p_state_code);
1898
1899 begin
1900
1901 l_arch_gre_step := 10;
1902 /* Get the context_id for 'TAX_UNIT_ID' */
1903 select context_id
1904 into l_tax_context_id
1905 from ff_contexts
1906 where context_name = 'TAX_UNIT_ID';
1907
1908 l_arch_gre_step := 20;
1909 /* Get the context_id for 'JURISDICTION_CODE' */
1910
1911 select context_id
1912 into l_jursd_context_id
1913 from ff_contexts
1914 where context_name = 'JURISDICTION_CODE';
1915
1916 l_arch_gre_step := 30;
1917
1918
1919 IF p_jd_type in ('ALL','View Online W2 Profile') then -- bug 4947859
1920
1921 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1922 ,p_start_date => ld_start_date
1923 ,p_end_date => ld_end_date
1924 ,p_business_group_id => ln_business_group_id
1925 ,p_tax_unit_id => l_tax_unit_id
1926 ,p_person_id => ln_person_id
1927 ,p_ssn => ln_ssn
1928 ,p_asg_set => ln_asg_set
1929 ,p_year => ln_year
1930 ,p_creation_date => g_pact_creation_date);
1931
1932
1933 hr_utility.trace('ln_year '||to_char(ld_end_date,'YYYY'));
1934
1935 l_user_entity_id_tab(1) := get_user_entity_id('A_VIEW_ONLINE_W2');
1936 l_w2_profile_option := fnd_profile.value('HR_VIEW_ONLINE_W2');
1937 IF l_w2_profile_option IS NOT NULL THEN
1938 l_value_tab(1) := to_char(ld_end_date, 'YYYY')+1||'/'
1939 ||l_w2_profile_option;
1940 ELSE
1941 l_value_tab(1) := null;
1942 END IF;
1943
1944 l_seq_tab(1) := 1;
1945 l_context_id_tab(1) := l_tax_context_id;
1946 l_context_val_tab(1) := p_tax_unit_id;
1947
1948
1949 l_arch_gre_step := 35;
1950
1951 create_archive (p_user_entity_id => l_user_entity_id_tab,
1952 p_context1 => p_payroll_action_id,
1953 p_value => l_value_tab,
1954 p_sequence => l_seq_tab,
1955 p_context => l_context_val_tab,
1956 p_context_id => l_context_id_tab,
1957 p_archive_level => 'ER REARCH');
1958
1959 END IF;
1960
1961 IF p_jd_type in ('ALL','FED 401K LIMITS') then
1962 /* Archive the SS EE wage Base */
1963 /* Archive the SS EE wage rate */
1964
1965 l_arch_gre_step := 40;
1966 begin
1967 select ss_ee_wage_limit,
1968 ss_ee_rate
1969 into l_value1,l_value2
1970 from pay_us_federal_tax_info_f puftif,
1971 pay_payroll_actions ppa
1972 where ppa.payroll_action_id = p_payroll_action_id
1973 and ppa.effective_date between puftif.effective_start_date and effective_end_date
1974 and puftif.fed_information_category = '401K LIMITS';
1975 exception
1976 when no_data_found then
1977 l_value1 := null;
1978 l_value2 := null;
1979 hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
1980 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1981 end;
1982
1983 /* Initialise the PL/SQL tables */
1984
1985 l_user_entity_id_tab.delete;
1986 l_value_tab.delete;
1987 l_seq_tab.delete;
1988 l_context_id_tab.delete;
1989 l_context_val_tab.delete;
1990
1991 /* Assign value to PL/SQL tables */
1992
1993 l_arch_gre_step := 50;
1994
1995 l_user_entity_id_tab(1) := get_user_entity_id('A_SS_EE_WAGE_BASE');
1996 l_user_entity_id_tab(2) := get_user_entity_id('A_SS_EE_WAGE_RATE');
1997 l_value_tab(1) := l_value1;
1998 l_value_tab(2) := l_value2;
1999 l_seq_tab(1) := 1;
2000 l_context_id_tab(1) := l_tax_context_id;
2001 l_context_val_tab(1) := p_tax_unit_id;
2002
2003 l_arch_gre_step := 60;
2004
2005 create_archive (p_user_entity_id => l_user_entity_id_tab,
2006 p_context1 => p_payroll_action_id,
2007 p_value => l_value_tab,
2008 p_sequence => l_seq_tab,
2009 p_context => l_context_val_tab,
2010 p_context_id => l_context_id_tab,
2011 p_archive_level => 'ER');
2012 end if;
2013
2014 IF p_jd_type in ('ALL','FED TAX UNIT INFORMATION'
2015 , 'FED TAX UNIT INFORMATION REARCH'
2016 ,'FED 1099R MAGNETIC REPORT RULES REARCH') then /*bug 5065406 */
2017 /* Archive the Employer country code */
2018
2019 l_arch_gre_step := 70;
2020
2021 IF p_jd_type = 'FED TAX UNIT INFORMATION REARCH' THEN
2022
2023 l_archive_level := 'ER REARCH';
2024
2025 ELSE
2026
2027 l_archive_level := 'ER';
2028
2029 END IF;
2030
2031
2032 begin
2033 select hrl.country,
2034 substr(hou.name,1,240),
2035 substr(hoi.org_information1,1,240)
2036 into l_value1,
2037 l_value2,
2038 l_value3
2039 from hr_locations hrl,
2040 hr_all_organization_units hou,
2041 hr_organization_information hoi
2042 where hou.organization_id = p_tax_unit_id
2043 and hoi.organization_id = hou.organization_id
2044 and hoi.org_information_context||'' = 'Employer Identification'
2045 and hrl.location_id = hou.location_id;
2046
2047 exception
2048 when no_data_found then
2049 l_value1 := null;
2050 l_value2 := null;
2051 l_value3 := null;
2052 hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2053 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2054 end;
2055
2056 /* Initialise the PL/SQL tables */
2057
2058 l_user_entity_id_tab.delete;
2059 l_value_tab.delete;
2060 l_seq_tab.delete;
2061 l_context_id_tab.delete;
2062 l_context_val_tab.delete;
2063
2064 /* Assign values to the PL/SQL tables */
2065
2066 l_arch_gre_step := 80;
2067
2068 l_user_entity_id_tab(1) := get_user_entity_id('A_TAX_UNIT_COUNTRY_CODE');
2069 l_user_entity_id_tab(2) := get_user_entity_id('A_TAX_UNIT_NAME');
2070 l_user_entity_id_tab(3) := get_user_entity_id('A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER');
2071 l_value_tab(1) := l_value1;
2072 l_value_tab(2) := l_value2;
2073 l_value_tab(3) := l_value3;
2074 l_seq_tab(1) := 1;
2075 l_context_id_tab(1) := l_tax_context_id;
2076 l_context_val_tab(1) := p_tax_unit_id;
2077
2078 l_arch_gre_step := 90;
2079
2080 create_archive (p_user_entity_id => l_user_entity_id_tab,
2081 p_context1 => p_payroll_action_id,
2082 p_value => l_value_tab,
2083 p_sequence => l_seq_tab,
2084 p_context => l_context_val_tab,
2085 p_context_id => l_context_id_tab,
2086 p_archive_level => l_archive_level );
2087
2088 end if;
2089
2090 IF p_jd_type in ('ALL','FED 1099R MAGNETIC REPORT RULES',
2091 'FED 1099R MAGNETIC REPORT RULES REARCH') then
2092 /* Archive the 1099R transmitter reporting rules */
2093 l_arch_gre_step := 100;
2094
2095 IF p_jd_type = 'FED 1099R MAGNETIC REPORT RULES REARCH' THEN
2096
2097 l_archive_level := 'ER REARCH';
2098
2099 ELSE
2100
2101 l_archive_level := 'ER';
2102
2103 END IF;
2104
2105 begin
2106 select substr(hoi.org_information2,1,240),
2107 substr(hoi.org_information1,1,240)
2108 into l_value1,
2109 l_value2
2110 from hr_organization_information hoi
2111 where hoi.organization_id = p_tax_unit_id
2112 and hoi.org_information_context || '' = '1099R Magnetic Report Rules';
2113 exception
2114 when no_data_found then
2115 l_value1 := null;
2116 l_value2 := null;
2117 hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2118 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2119 end;
2120
2121 /* Initialise the PL/SQL tables */
2122
2123 l_user_entity_id_tab.delete;
2124 l_value_tab.delete;
2125 l_seq_tab.delete;
2126 l_context_id_tab.delete;
2127 l_context_val_tab.delete;
2128
2129 /* Assign values to the PL/SQL tables */
2130 l_arch_gre_step := 110;
2131 l_user_entity_id_tab(1) := get_user_entity_id('A_US_1099R_TRANSMITTER_CODE');
2132 l_user_entity_id_tab(2) := get_user_entity_id('A_US_1099R_TRANSMITTER_INDICATOR');
2133 l_value_tab(1) := l_value1;
2134 l_value_tab(2) := l_value2;
2135 l_seq_tab(1) := 1;
2136 l_context_id_tab(1) := l_tax_context_id;
2137 l_context_val_tab(1) := p_tax_unit_id;
2138
2139 l_arch_gre_step := 120;
2140
2141 hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2142 hr_utility.trace('value = '||l_value1);
2143 hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2144 hr_utility.trace('value = '||l_value2);
2145
2146 create_archive (p_user_entity_id => l_user_entity_id_tab,
2147 p_context1 => p_payroll_action_id,
2148 p_value => l_value_tab,
2149 p_sequence => l_seq_tab,
2150 p_context => l_context_val_tab,
2151 p_context_id => l_context_id_tab,
2152 p_archive_level => l_archive_level);
2153
2154 l_arch_gre_step := 130;
2155
2156 begin
2157
2158 select substr(hoi2.org_information3,1,240),
2159 substr(hoi2.org_information4,1,240)
2160 into l_value1, l_value2
2161 from hr_organization_information hoi2,
2162 hr_organization_information hoi
2163 where hoi.organization_id = p_tax_unit_id
2164 and hoi.org_information_context||'' = '1099R Magnetic Report Rules'
2165 and hoi.org_information_context = hoi2.org_information_context
2166 and hoi.org_information2 = hoi2.org_information2
2167 and hoi2.org_information1 = 'Y';
2168
2169 exception
2170 when no_data_found then
2171 l_value1 := null;
2172 l_value2 := null;
2173 hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2174 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2175 when too_many_rows then
2176 l_value1 := null;
2177 l_value2 := null;
2178 raise_application_error(-20001,'Error getting US_1099R_BUREAU_INDICATOR at step : '
2179 ||to_char(l_arch_gre_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2180 end;
2181 /* Initialise the PL/SQL tables */
2182
2183 l_user_entity_id_tab.delete;
2184 l_value_tab.delete;
2185 l_seq_tab.delete;
2186 l_context_id_tab.delete;
2187 l_context_val_tab.delete;
2188
2189 /* Assign values to the PL/SQL tables */
2190
2191 l_arch_gre_step := 140;
2192 l_user_entity_id_tab(1) := get_user_entity_id('A_US_1099R_BUREAU_INDICATOR');
2193 l_user_entity_id_tab(2) := get_user_entity_id('A_US_1099R_COMBINED_FED_STATE_FILER');
2194 l_value_tab(1) := l_value1;
2195 l_value_tab(2) := l_value2;
2196 l_seq_tab(1) := 1;
2197 l_context_id_tab(1) := l_tax_context_id;
2198 l_context_val_tab(1) := p_tax_unit_id;
2199
2200 l_arch_gre_step := 150;
2201
2202 hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2203 hr_utility.trace('value = '||l_value1);
2204 hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2205 hr_utility.trace('value = '||l_value2);
2206
2207 create_archive (p_user_entity_id => l_user_entity_id_tab,
2208 p_context1 => p_payroll_action_id,
2209 p_value => l_value_tab,
2210 p_sequence => l_seq_tab,
2211 p_context => l_context_val_tab,
2212 p_context_id => l_context_id_tab,
2213 p_archive_level => l_archive_level);
2214
2215 end if; /* 1099R Archive only */
2216
2217 IF p_jd_type in ('ALL','FED W2 REPORTING RULES',
2218 'FED W2 REPORTING RULES REARCH') then
2219
2220 /* Archive the W2 Reporting Rules data */
2221
2222 l_arch_gre_step := 160;
2223
2224 IF p_jd_type = 'FED W2 REPORTING RULES REARCH' THEN
2225
2226 l_archive_level := 'ER REARCH';
2227
2228 ELSE
2229
2230 l_archive_level := 'ER';
2231
2232 END IF;
2233
2234 begin
2235 select
2236 --hoi.org_information6 value1,
2237 hoi.org_information8 value2,
2238 hoi.org_information9 value3,
2239 hoi.org_information10 value4,
2240 hoi.org_information11 value5,
2241 hoi.org_information12 value6,
2242 hoi.org_information13 value7,
2243 hoi.org_information14 value8,
2244 hoi.org_information15 value9,
2245 hoi.org_information16 value10,
2246 --hoi.org_information2 value11,
2247 --hoi.org_information3 value12,
2248 --hoi.org_information4 value13,
2249 --hoi.org_information5 value14,
2250 --hoi.org_information7 value15, /* Job Development Fee (AL) */
2251 hoi.org_information1 value16
2252 into
2253 --l_value1,
2254 l_value2,
2255 l_value3,
2256 l_value4,
2257 l_value5,
2258 l_value6,
2259 l_value7,
2260 l_value8,
2261 l_value9,
2262 l_value10,
2263 --l_value11,
2264 --l_value12,
2265 --l_value13,
2266 --l_value14,
2267 --l_value15, /* Job Development Fee (AL) */
2268 l_value16
2269 from hr_organization_information hoi
2270 where hoi.organization_id = p_tax_unit_id
2271 and hoi.org_information_context || '' = 'W2 Reporting Rules';
2272 exception
2273 when no_data_found then
2274 --l_value1 := null;
2275 l_value2 := null;
2276 l_value3 := null;
2277 l_value4 := null;
2278 l_value5 := null;
2279 l_value6 := null;
2280 l_value7 := null;
2281 l_value8 := null;
2282 l_value9 := null;
2283 l_value10 := null;
2284 --l_value11 := null;
2285 --l_value12 := null;
2286 --l_value13 := null;
2287 --l_value14 := null;
2288 --l_value15 := null; /* Job Development Fee (AL) */
2289 l_value16 := null;
2290 hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2291 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2292 end;
2293
2294
2295 /* Initialise the PL/SQL tables */
2296
2297 l_user_entity_id_tab.delete;
2298 l_value_tab.delete;
2299 l_seq_tab.delete;
2300 l_context_id_tab.delete;
2301 l_context_val_tab.delete;
2302
2303 /* Assign values to the PL/SQL tables */
2304
2305 l_arch_gre_step := 170;
2306 /*
2307 l_user_entity_id_tab(1) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_BLOCKING_FACTOR');
2308 l_user_entity_id_tab(2) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_W2_2678_FILER');
2309 l_user_entity_id_tab(3) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_COMPANY_NAME');
2310 l_user_entity_id_tab(4) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_CONTACT_NAME');
2311 l_user_entity_id_tab(5) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_NOTIFICATION_METHOD');
2312 l_user_entity_id_tab(6) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_PREPARER');
2313 l_user_entity_id_tab(7) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_TERMINATED_GRE_INDICATOR');
2314 l_user_entity_id_tab(8) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_THIRD_PARTY_SICK_PAY');
2315 l_user_entity_id_tab(9) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_OTHER_EIN');
2316 l_user_entity_id_tab(10) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_TAX_JURISDICTION');
2317
2318 l_user_entity_id_tab(11) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_COMPUTER');
2319 l_user_entity_id_tab(12) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_INTERNAL_LABELLING');
2320 l_user_entity_id_tab(13) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_DENSITY');
2321 l_user_entity_id_tab(14) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_RECORDING_CODE');
2322 l_user_entity_id_tab(15) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_AL_JOB_DEVELOPMENT_FEE');
2323 l_user_entity_id_tab(16) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_W_2_TRANSMITTER');
2324
2325 l_value_tab(1) := l_value1;
2326 l_value_tab(2) := l_value2;
2327 l_value_tab(3) := l_value3;
2328 l_value_tab(4) := l_value4;
2329 l_value_tab(5) := l_value5;
2330 l_value_tab(6) := l_value6;
2331 l_value_tab(7) := l_value7;
2332 l_value_tab(8) := l_value8;
2333 l_value_tab(9) := l_value9;
2334 l_value_tab(10) := l_value10;
2335 l_value_tab(11) := l_value11;
2336 l_value_tab(12) := l_value12;
2337 l_value_tab(13) := l_value13;
2338 l_value_tab(14) := l_value14;
2339 l_value_tab(15) := l_value15;
2340 l_value_tab(16) := l_value16;
2341 */
2342 l_user_entity_id_tab(1) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_W2_2678_FILER');
2343 l_user_entity_id_tab(2) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_COMPANY_NAME');
2344 l_user_entity_id_tab(3) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_CONTACT_NAME');
2345 l_user_entity_id_tab(4) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_NOTIFICATION_METHOD');
2346 l_user_entity_id_tab(5) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_PREPARER');
2347 l_user_entity_id_tab(6) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_TERMINATED_GRE_INDICATOR');
2348 l_user_entity_id_tab(7) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_THIRD_PARTY_SICK_PAY');
2349 l_user_entity_id_tab(8) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_OTHER_EIN');
2350 l_user_entity_id_tab(9) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_TAX_JURISDICTION');
2351 --l_user_entity_id_tab(10) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_AL_JOB_DEVELOPMENT_FEE');
2352 l_user_entity_id_tab(10) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_W_2_TRANSMITTER');
2353
2354 l_value_tab(1) := l_value2;
2355 l_value_tab(2) := l_value3;
2356 l_value_tab(3) := l_value4;
2357 l_value_tab(4) := l_value5;
2358 l_value_tab(5) := l_value6;
2359 l_value_tab(6) := l_value7;
2360 l_value_tab(7) := l_value8;
2361 l_value_tab(8) := l_value9;
2362 l_value_tab(9) := l_value10;
2363 --l_value_tab(10) := l_value15; /* Job Development Fee (AL) */
2364 l_value_tab(10) := l_value16;
2365
2366 l_seq_tab(1) := 1;
2367 l_context_id_tab(1) := l_tax_context_id;
2368 l_context_val_tab(1) := p_tax_unit_id;
2369
2370 l_arch_gre_step := 180;
2371
2372 create_archive (p_user_entity_id => l_user_entity_id_tab,
2373 p_context1 => p_payroll_action_id,
2374 p_value => l_value_tab,
2375 p_sequence => l_seq_tab,
2376 p_context => l_context_val_tab,
2377 p_context_id => l_context_id_tab,
2378 p_archive_level => l_archive_level);
2379
2380 end if; /* W2 Reporting Rules */
2381
2382 IF p_jd_type in ('ALL','FEDERAL TAX RULES'
2383 ,'FEDERAL TAX RULES REARCH') then
2384 l_arch_gre_step := 190;
2385
2386 IF p_jd_type = 'FEDERAL TAX RULES REARCH' THEN
2387
2388 l_archive_level := 'ER REARCH';
2389
2390 ELSE
2391
2392 l_archive_level := 'ER';
2393
2394 END IF;
2395 l_arch_gre_step := 191;
2396
2397 begin
2398 select hoi.org_information4 value1,
2399 hoi.org_information8 value2
2400 into l_value1,
2401 l_value2
2402 from hr_organization_information hoi
2403 where hoi.organization_id = p_tax_unit_id
2404 and hoi.org_information_context || '' = 'Federal Tax Rules';
2405 exception
2406 when no_data_found then
2407 l_value1 := null;
2408 l_value2 := null;
2409 hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2410 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2411 end;
2412
2413 l_user_entity_id_tab.delete;
2414 l_value_tab.delete;
2415 l_seq_tab.delete;
2416 l_context_id_tab.delete;
2417 l_context_val_tab.delete;
2418
2419 /* Assign values to the PL/SQL tables */
2420
2421 l_arch_gre_step := 200;
2422 l_user_entity_id_tab(1) := get_user_entity_id('A_LC_FEDERAL_TAX_RULES_ORG_TYPE_OF_EMPLOYMENT');
2423 l_value_tab(1) := l_value1;
2424 l_seq_tab(1) := 1;
2425 l_context_id_tab(1) := l_tax_context_id;
2426 l_context_val_tab(1) := p_tax_unit_id;
2427
2428 l_arch_gre_step := 201;
2429 l_user_entity_id_tab(2) := get_user_entity_id('A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER');
2430 l_value_tab(2) := l_value2;
2431
2432 hr_utility.trace('A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER '||l_user_entity_id_tab(2));
2433 hr_utility.trace('Value for A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER '||l_value_tab(2));
2434
2435 l_arch_gre_step := 210;
2436 create_archive (p_user_entity_id => l_user_entity_id_tab,
2437 p_context1 => p_payroll_action_id,
2438 p_value => l_value_tab,
2439 p_sequence => l_seq_tab,
2440 p_context => l_context_val_tab,
2441 p_context_id => l_context_id_tab,
2442 p_archive_level => l_archive_level);
2443 end if;
2444
2445 IF p_jd_type in ('ALL','STATE TAX RULES','STATE TAX RULES REARCH') then
2446
2447 /* Archive the state information for all the states
2448 in the GRE, for which the state tax rules have been
2449 maintained under the 'State Tax Rules' */
2450
2451 l_arch_gre_step := 220;
2452
2453 IF p_jd_type = 'STATE TAX RULES REARCH' THEN
2454
2455 l_archive_level := 'ER REARCH';
2456
2457 ELSE
2458
2459 l_archive_level := 'ER';
2460
2461 END IF;
2462
2463
2464 l_fips_uid := get_user_entity_id('A_FIPS_CODE_JD');
2465 l_sit_uid := get_user_entity_id('A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID');
2466 l_sui_uid := get_user_entity_id('A_STATE_TAX_RULES_ORG_SUI_COMPANY_STATE_ID');
2467
2468 /* Initialise the PL/SQL tables */
2469
2470 l_user_entity_id_tab.delete;
2471 l_value_tab.delete;
2472 l_seq_tab.delete;
2473 l_context_id_tab.delete;
2474 l_context_val_tab.delete;
2475
2476 l_arch_gre_step := 230;
2477
2478 for c_state in c_get_state_code
2479 loop
2480
2481 l_arch_gre_step := 240;
2482 /* Archive the FIPS Code for a state code */
2483 /* Archive the company SIT state id */
2484 /* Archive the company SUI state id */
2485
2486 begin
2487 select to_char(rules.fips_code) value1,
2488 ltrim(rtrim(target.org_information3)) value2,
2489 ltrim(rtrim(target.org_information2)) value3
2490 into l_value1,
2491 l_value2,
2492 l_value3
2493 from pay_state_rules rules,
2494 pay_us_states pus,
2495 hr_organization_information target
2496 where substr(rules.jurisdiction_code, 1, 2) = c_state.state_code
2497 and target.organization_id = p_tax_unit_id
2498 and target.org_information_context || '' = 'State Tax Rules'
2499 and target.org_information1 = pus.state_abbrev
2500 and pus.state_code = c_state.state_code;
2501 exception
2502 when no_data_found then
2503 l_value1 := null;
2504 l_value2 := null;
2505 l_value3 := null;
2506 hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2507 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2508 end;
2509
2510 l_user_entity_id_tab.delete;
2511 l_value_tab.delete;
2512 l_seq_tab.delete;
2513 l_context_id_tab.delete;
2514 l_context_val_tab.delete;
2515
2516 /* Assign values to the PL/SQL tables */
2517
2518 l_user_entity_id_tab(1) := l_fips_uid;
2519 l_user_entity_id_tab(2) := l_sit_uid;
2520 l_user_entity_id_tab(3) := l_sui_uid;
2521 l_value_tab(1) := l_value1;
2522 l_value_tab(2) := l_value2;
2523 l_value_tab(3) := l_value3;
2524 l_seq_tab(1) := 1;
2525 l_context_id_tab(1) := l_tax_context_id;
2526 l_context_val_tab(1) := p_tax_unit_id;
2527 l_seq_tab(2) := 2;
2528 l_context_id_tab(2) := l_jursd_context_id;
2529 l_context_val_tab(2) := c_state.state_code || '-000-0000';
2530
2531 l_arch_gre_step := 250;
2532
2533 create_archive (p_user_entity_id => l_user_entity_id_tab,
2534 p_context1 => p_payroll_action_id,
2535 p_value => l_value_tab,
2536 p_sequence => l_seq_tab,
2537 p_context => l_context_val_tab,
2538 p_context_id => l_context_id_tab,
2539 p_archive_level => l_archive_level);
2540 end loop;
2541 END IF; /* State Archive */
2542
2543 g_archive_flag := 'Y';
2544
2545 exception
2546 when others then
2547 g_archive_flag := 'N';
2548 Raise_application_error(-20001,'Error in eoy_archive_gre_data after step : ' ||
2549 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2550 end eoy_archive_gre_data;
2551
2552 /* Name : chk_gre_archive
2553 Purpose : Function to check if the employer level data has been archived
2554 or not.
2555 Arguments :
2556 Notes :
2557 */
2558
2559 function chk_gre_archive (p_payroll_action_id number) return boolean is
2560
2561 l_flag varchar2(1);
2562
2563 cursor c_chk_payroll_action is
2564 select 'Y'
2565 from dual
2566 where exists (select null
2567 from ff_archive_items fai
2568 where fai.context1 = p_payroll_action_id);
2569 begin
2570
2571 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2572
2573 if g_archive_flag = 'Y' then
2574 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2575 return (TRUE);
2576 else
2577
2578 hr_utility.trace('chk_gre_archive - opening cursor');
2579
2580 open c_chk_payroll_action;
2581 fetch c_chk_payroll_action into l_flag;
2582 if c_chk_payroll_action%FOUND then
2583 hr_utility.trace('chk_gre_archive - found in cursor');
2584 g_archive_flag := 'Y';
2585 else
2586 hr_utility.trace('chk_gre_archive - not found in cursor');
2587 g_archive_flag := 'N';
2588 end if;
2589
2590 hr_utility.trace('chk_gre_archive - closing cursor');
2591 close c_chk_payroll_action;
2592 if g_archive_flag = 'Y' then
2593 hr_utility.trace('chk_gre_archive - returning true');
2594 return (TRUE);
2595 else
2596 hr_utility.trace('chk_gre_archive - returning false');
2597 return(FALSE);
2598 end if;
2599 end if;
2600 end chk_gre_archive;
2601
2602 /* Name : eoy_archinit
2603 Purpose : This performs the context initialization for the year end
2604 pre-process.
2605 Arguments :
2606 Notes :
2607 */
2608
2609 procedure eoy_archinit(p_payroll_action_id in number) is
2610
2611 l_jurisdiction_code VARCHAR2(30);
2612 l_tax_unit_id NUMBER(15);
2613 l_archive boolean:= FALSE;
2614 l_formula_name ff_formulas_f.formula_name%TYPE;
2615 l_step number;
2616 ld_end_date DATE;
2617 ld_start_date DATE;
2618 ln_business_group_id NUMBER;
2619 ln_person_id NUMBER := 0;
2620 ln_asg_set NUMBER := 0;
2621 ln_ssn NUMBER;
2622 ln_year NUMBER := 0;
2623
2624 cursor c_get_min_chunk is
2625 select min(paa.chunk_number)
2626 from pay_assignment_actions paa
2627 where paa.payroll_action_id = p_payroll_action_id;
2628
2629
2630 begin
2631
2632 --hr_utility.trace_on(null,'PYUSARCH_DBG');
2633
2634 hr_utility.set_location ('eoy_archinit',1);
2635
2636
2637 l_jurisdiction_code := '99-999-9999';
2638
2639 /* Check to see if all the relevant formulas have been compiled */
2640 l_step := 1;
2641
2642
2643 begin
2644 select ff.formula_name
2645 into l_formula_name
2646 from ff_formulas_f ff,
2647 ff_compiled_info_f fci
2648 where ff.formula_name = 'US_YEP_BOX_12'
2649 and fci.formula_id = ff.formula_id;
2650 exception
2651 when no_data_found then
2652 raise_application_error(-20001,'eoy_archinit:US_YEP_BOX_12 formula not compiled');
2653 end;
2654
2655
2656 l_step := 2;
2657 begin
2658 select ff.formula_name
2659 into l_formula_name
2660 from ff_formulas_f ff,
2661 ff_compiled_info_f fci
2662 where ff.formula_name = 'US_YEP_BOX_14'
2663 and fci.formula_id = ff.formula_id;
2664 exception
2665 when no_data_found then
2666 raise_application_error(-20001,'eoy_archinit:US_YEP_BOX_14 formula not compiled');
2667 end;
2668
2669 l_step := 3;
2670 begin
2671 select ff.formula_name
2672 into l_formula_name
2673 from ff_formulas_f ff,
2674 ff_compiled_info_f fci
2675 where ff.formula_name = 'US_YEP_FEDERAL'
2676 and fci.formula_id = ff.formula_id;
2677 exception
2678 when no_data_found then
2679 raise_application_error(-20001,'eoy_archinit:US_YEP_FEDERAL formula not compiled');
2680 end;
2681
2682 l_step := 4;
2683 begin
2684 select ff.formula_name
2685 into l_formula_name
2686 from ff_formulas_f ff,
2687 ff_compiled_info_f fci
2688 where ff.formula_name = 'US_YEP_LOCALITY'
2689 and fci.formula_id = ff.formula_id;
2690 exception
2691 when no_data_found then
2692 raise_application_error(-20001,'eoy_archinit:US_YEP_LOCALITY formula not compiled');
2693 end;
2694
2695 l_step := 5;
2696 begin
2697 select ff.formula_name
2698 into l_formula_name
2699 from ff_formulas_f ff,
2700 ff_compiled_info_f fci
2701 where ff.formula_name = 'US_YEP_STATE'
2702 and fci.formula_id = ff.formula_id;
2703 exception
2704 when no_data_found then
2705 raise_application_error(-20001,'eoy_archinit:US_YEP_STATE formula not compiled');
2706 end;
2707
2708 l_step := 6;
2709 pay_balance_pkg.set_context ('JURISDICTION_CODE',l_jurisdiction_code);
2710
2711 hr_utility.set_location ('eoy_archinit',2);
2712
2713 /* Get the tax unit id and set it up as the context */
2714 l_step := 7;
2715 /*
2716 pay_us_archive.g_report_type := pay_us_archive.get_report_type(p_payroll_action_id);
2717 if g_report_type <> 'W2C_PRE_PROCESS' then
2718
2719 select to_number(substr(legislative_parameters,
2720 instr(legislative_parameters,'TRANSFER_GRE=')+ length('TRANSFER_GRE='))),
2721 business_group_id
2722 into l_tax_unit_id,
2723 ln_business_group_id
2724 from pay_payroll_actions
2725 where payroll_action_id = p_payroll_action_id;
2726
2727 else
2728
2729 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
2730 ,p_start_date => ld_start_date
2731 ,p_end_date => ld_end_date
2732 ,p_business_group_id => ln_business_group_id
2733 ,p_tax_unit_id => l_tax_unit_id
2734 ,p_person_id => ln_person_id
2735 ,p_ssn => ln_ssn
2736 ,p_asg_set => ln_asg_set
2737 ,p_year => ln_year
2738 ,p_creation_date => g_pact_creation_date);
2739
2740
2741 end if;
2742 */
2743
2744 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
2745 ,p_start_date => ld_start_date
2746 ,p_end_date => ld_end_date
2747 ,p_business_group_id => ln_business_group_id
2748 ,p_tax_unit_id => l_tax_unit_id
2749 ,p_person_id => ln_person_id
2750 ,p_ssn => ln_ssn
2751 ,p_asg_set => ln_asg_set
2752 ,p_year => ln_year
2753 ,p_creation_date => g_pact_creation_date);
2754
2755
2756 hr_utility.set_location ('eoy_archinit',3);
2757
2758 l_step := 8;
2759 pay_balance_pkg.set_context ('TAX_UNIT_ID',l_tax_unit_id);
2760
2761 l_step := 9;
2762 hr_utility.set_location ('eoy_archinit getting min chunk number',10);
2763
2764 open c_get_min_chunk;
2765 fetch c_get_min_chunk into g_min_chunk;
2766 l_step := 10;
2767 if c_get_min_chunk%NOTFOUND then
2768 g_min_chunk := -1;
2769 raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
2770 end if;
2771 close c_get_min_chunk;
2772
2773 hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
2774 l_step := 11;
2775 l_archive := chk_gre_archive(p_payroll_action_id);
2776
2777 l_step := 12;
2778 hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
2779
2780 pay_us_archive.g_report_type := pay_us_archive.get_report_type(p_payroll_action_id);
2781 pay_us_archive.g_puerto_rico_gre := pay_us_archive.get_puerto_rico_info(l_tax_unit_id) ;
2782 pay_us_archive.g_1099R_transmitter_code := pay_us_archive.get_1099r_info(l_tax_unit_id) ;
2783 pay_us_archive.g_pre_tax_info := pay_us_archive.get_pre_tax_info(l_tax_unit_id,
2784 ln_business_group_id) ;
2785 if pay_us_sqwl_udf.chk_govt_employer(p_tax_unit_id =>l_tax_unit_id) then
2786 pay_us_archive.g_govt_employer := 'Y';
2787 else
2788 pay_us_archive.g_govt_employer := 'N';
2789 end if;
2790
2791
2792 l_step := 13;
2793
2794 select context_id
2795 into g_jursd_context_id
2796 from ff_contexts
2797 where context_name = 'JURISDICTION_CODE';
2798
2799 select context_id
2800 into g_tax_unit_context_id
2801 from ff_contexts
2802 where context_name = 'TAX_UNIT_ID';
2803
2804 /* get the user_entity_id of the dbis A_STATE_ABBREV, A_COUNTY_NAME,
2805 A_CITY_NAME, A_COUNTY_SD_NAME and A_CITY_SD_NAME */
2806
2807 l_step := 14;
2808
2809 g_state_uei := get_user_entity_id('A_STATE_ABBREV');
2810
2811 l_step := 15;
2812
2813 g_county_uei := get_user_entity_id('A_COUNTY_NAME');
2814
2815 l_step := 16;
2816
2817 g_city_uei := get_user_entity_id('A_CITY_NAME');
2818
2819 l_step := 17;
2820
2821 g_county_sd_uei := get_user_entity_id('A_COUNTY_SD_NAME');
2822
2823 l_step := 18;
2824
2825 g_city_sd_uei := get_user_entity_id('A_CITY_SD_NAME');
2826
2827 l_step := 19;
2828
2829 g_per_marital_status := get_user_entity_id('A_PER_MARITAL_STATUS');
2830
2831 l_step := 20;
2832
2833 g_con_national_identifier := get_user_entity_id('A_CON_NATIONAL_IDENTIFIER');
2834
2835 l_step := 21;
2836
2837 g_taxable_amount_unknown := get_user_entity_id('A_TAXABLE_AMOUNT_UNKNOWN');
2838
2839 l_step := 22;
2840
2841 g_total_distributions := get_user_entity_id('A_TOTAL_DISTRIBUTIONS');
2842
2843 l_step := 23;
2844
2845 g_emp_distribution_percent := get_user_entity_id('A_EMPLOYEE_DISTRIBUTION_PERCENT');
2846
2847 l_step := 24;
2848
2849 g_total_distribution_percent := get_user_entity_id('A_TOTAL_DISTRIBUTION_PERCENT');
2850
2851 l_step := 25;
2852
2853 g_distribution_code_for_1099r := get_user_entity_id('A_DISTRIBUTION_CODE_FOR_1099R');
2854
2855 l_step := 26;
2856 -- Added For bug# 5517938
2857 g_first_yr_roth_contrib := get_user_entity_id('A_FIRST_YEAR_ROTH_CONTRIB');
2858
2859 -- Bug 4544792
2860 -- g_disability_plan_id := get_user_entity_id('A_EXTRA_ASSIGNMENT_INFORMATION_PAY_US_DISABILITY_PLAN_INFO_DF_PLAN_ID');
2861
2862 g_disability_plan_id := get_user_entity_id('A_SCL_ASG_US_NJ_PLAN_ID');
2863
2864 l_step := 27;
2865
2866 g_archive_date := get_user_entity_id('A_ARCHIVE_DATE');
2867
2868 l_step := 28;
2869
2870 g_w2_corrected := get_user_entity_id('A_W2_CORRECTED');
2871
2872 l_step := 29;
2873
2874 g_view_online_w2 := get_user_entity_id('A_VIEW_ONLINE_W2');
2875
2876
2877 exception
2878 when others then
2879 raise_application_error(-20001,'eoy_archinit at '
2880 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2881 end eoy_archinit;
2882
2883 /* Name : delete_ff_archive
2884 Purpose : Delete from ff_archive and context
2885 Arguments :
2886 Notes :
2887 */
2888
2889 PROCEDURE delete_ff_archive (p_context number,
2890 p_archive_name IN VARCHAR2) IS
2891
2892 CURSOR c_get_ff_arch IS
2893 select fai.archive_item_id
2894 from ff_archive_items fai,
2895 ff_user_entities fue
2896 where context1 =p_context
2897 and fai.user_entity_id = fue.user_entity_id
2898 and user_entity_name = p_archive_name ;
2899
2900 l_archive_itemid NUMBER;
2901
2902 BEGIN
2903 hr_utility.trace('p_archive_name '||p_archive_name);
2904 hr_utility.trace('context1 '||p_context);
2905
2906 OPEN c_get_ff_arch;
2907 FETCH c_get_ff_arch INTO l_archive_itemid;
2908 CLOSE c_get_ff_arch;
2909
2910
2911 hr_utility.trace('l_archive_itemid '||l_archive_itemid);
2912
2913 delete from ff_archive_item_contexts
2914 where archive_item_id = l_archive_itemid;
2915
2916 delete from ff_archive_items
2917 where archive_item_id = l_archive_itemid;
2918
2919 END;
2920
2921 /* Name : print_w2_corrected
2922 Purpose : Returns if 'corrected; should be printed on W-2
2923 Arguments :
2924 Notes :
2925 */
2926 Function print_w2_corrected (l_payroll_action_id IN number
2927 ,p_assactid IN NUMBER
2928 ,l_taxunitid IN NUMBER)
2929 RETURN VARCHAR2 IS
2930 l_corrected_date VARCHAR2(20);
2931 l_profile_date VARCHAR2(20);
2932 l_add_archive VARCHAR2(10);
2933 BEGIN
2934
2935 l_corrected_date := fnd_date.canonical_to_date(
2936 substr(fnd_date.date_to_canonical(sysdate),1,10));
2937 hr_utility.trace('Archive Date : ' || l_corrected_date);
2938
2939 l_profile_date := fnd_date.canonical_to_date(
2940 pay_us_archive_util.get_archive_value(l_payroll_action_id,
2941 'A_VIEW_ONLINE_W2',
2942 l_taxunitid));
2943
2944
2945 l_add_archive := pay_us_archive_util.get_archive_value(p_assactid,
2946 'A_ADD_ARCHIVE',
2947 l_taxunitid);
2948
2949
2950 hr_utility.trace('View Online W2 Profile date'||l_profile_date);
2951 hr_utility.trace('l_add_archive '||l_add_archive);
2952
2953 IF nvl(l_add_archive,'N') = 'Y' THEN
2954 delete_ff_archive(p_assactid,'A_ADD_ARCHIVE');
2955 return 'N';
2956 ELSIF g_pact_creation_date = l_corrected_date THEN
2957 return '';
2958 ELSIF l_corrected_date > l_profile_date THEN
2959 return 'Y';
2960 END IF;
2961
2962 RETURN '';
2963 END;
2964
2965 /* Name : eoy_archive_data
2966 Purpose : This performs the US specific employee context setting for the
2967 Year End PreProcess.
2968 Arguments :
2969 Notes :
2970 */
2971
2972 procedure eoy_archive_data(p_assactid in number, p_effective_date in date) is
2973
2974 l_aaid pay_assignment_actions.assignment_action_id%type;
2975 l_aaseq pay_assignment_actions.action_sequence%type;
2976 l_asgid pay_assignment_actions.assignment_id%type;
2977 l_date_earned date;
2978 l_taxunitid pay_assignment_actions.tax_unit_id%type;
2979 l_year_start date;
2980 l_year_end date;
2981 l_context_no number := 60;
2982 l_count number := 0;
2983 l_jurisdiction varchar2(11);
2984 l_state_uei ff_user_entities.user_entity_id%type;
2985 l_county_uei ff_user_entities.user_entity_id%type;
2986 l_city_uei ff_user_entities.user_entity_id%type;
2987 l_county_sd_uei ff_user_entities.user_entity_id%type;
2988 l_city_sd_uei ff_user_entities.user_entity_id%type;
2989 l_state_abbrev pay_us_states.state_abbrev%type;
2990 l_county_name pay_us_counties.county_name%type;
2991 l_city_name pay_us_city_names.city_name%type;
2992 l_cnt_sd_name pay_us_county_school_dsts.school_dst_name%type;
2993 l_cty_sd_name pay_us_city_school_dsts.school_dst_name%type;
2994 l_step number := 0;
2995 l_county_code varchar2(3);
2996 l_city_code varchar2(4);
2997 l_person_id per_people_f.person_id%type;
2998 l_jursd_context_id ff_contexts.context_id%type;
2999 l_user_entity_id_tab pay_us_archive.number_data_type_table;
3000 l_user_entity_tab pay_us_archive.char240_data_type_table;
3001 l_defined_balance_id_tab pay_us_archive.number_data_type_table;
3002 l_value_tab pay_us_archive.char240_data_type_table;
3003 l_balance_feed_tab pay_us_archive.char240_data_type_table;
3004 l_seq_tab pay_us_archive.number_data_type_table;
3005 l_context_id_tab pay_us_archive.number_data_type_table;
3006 l_context_val_tab pay_us_archive.char240_data_type_table;
3007 --l_jd_done_tab pay_us_archive.char240_data_type_table;
3008 --l_jd_name_done_tab pay_us_archive.char240_data_type_table;
3009 l_chunk number;
3010 l_payroll_action_id number;
3011 l_chk_state_archive varchar2(1);
3012 l_chk_county_archive varchar2(1);
3013 l_chk_cnt_sd_archive varchar2(1);
3014 l_chk_city_sd_archive varchar2(1);
3015 l_true varchar2(1);
3016 l_marital_status per_people_f.marital_status%type;
3017 l_con_national_identifier per_people_f.national_identifier%type;
3018 l_archive_item_id ff_archive_items.archive_item_id%type;
3019 l_object_version_number number(9);
3020 l_some_warning boolean;
3021 lv_value ff_archive_items.value%type := null;
3022 l_taxable_amount_unknown varchar(150) := null;
3023 l_total_distributions varchar(150) := null;
3024 l_ee_distribution_percent varchar(150) := null;
3025 l_total_distribution_percent varchar(150) := null;
3026 l_index number := 0;
3027 lv_medicare_withheld number;
3028 lv_ss_withheld number;
3029 l_tax_unit_context_id number;
3030 l_disability_plan_id varchar2(150) := null;
3031 l_distribution_code varchar2(150) := '7'; /* Default it to 7,Normaldistribution code */
3032 l_first_yr_roth_contrib varchar2(10); -- Bug# 5517938
3033 l_mesg varchar(50);
3034
3035 l_jd_index number := 0;
3036 l_add_archive varchar2(10);
3037 --
3038 -- Following variables Added For Bug# 5517938
3039 -- reverting back changes for Bug# 5517938
3040
3041 /* Get the jurisdiction code of all the cities
3042 for the person_id corresponding to the
3043 assignment_id */
3044
3045
3046 cursor c_get_city is
3047 select distinct pcty.jurisdiction_code pcty
3048 from pay_us_city_tax_info_f cti,
3049 pay_us_emp_city_tax_rules_f pcty,
3050 per_all_assignments_f paf1
3051 where paf1.person_id = l_person_id
3052 and paf1.effective_end_date >= l_year_start
3053 and paf1.effective_start_date <= l_year_end
3054 and pcty.assignment_id = paf1.assignment_id
3055 and pcty.effective_start_date <= l_year_end
3056 and pcty.effective_end_date >= l_year_start
3057 and substr(pcty.city_code,1,1) <> 'U'
3058 and pcty.jurisdiction_code = cti.jurisdiction_code
3059 and cti.city_tax = 'Y'
3060 and cti.effective_start_date <= l_year_end
3061 and cti.effective_end_date >= l_year_start;
3062
3063 /* Get the jurisdiction code of all the counties
3064 for the person_id corresponding to the assignment_id */
3065 /*
3066 cursor c_get_county is
3067 select distinct pcnt.jurisdiction_code
3068 from pay_us_emp_county_tax_rules_f pcnt,
3069 per_all_assignments_f paf1
3070 where paf1.person_id = l_person_id
3071 and paf1.effective_end_date >= l_year_start
3072 and paf1.effective_start_date <= l_year_end
3073 and pcnt.assignment_id = paf1.assignment_id
3074 and pcnt.effective_start_date <= l_year_end
3075 and pcnt.effective_end_date >= l_year_start;
3076 */
3077
3078 cursor c_get_county is
3079 select distinct pcnt.jurisdiction_code
3080 from pay_us_county_tax_info_f cnti,
3081 pay_us_emp_county_tax_rules_f pcnt,
3082 per_all_assignments_f paf1
3083 where paf1.person_id = l_person_id
3084 and paf1.effective_end_date >= l_year_start
3085 and paf1.effective_start_date <= l_year_end
3086 and pcnt.assignment_id = paf1.assignment_id
3087 and pcnt.effective_start_date <= l_year_end
3088 and pcnt.effective_end_date >= l_year_start
3089 and pcnt.jurisdiction_code = cnti.jurisdiction_code
3090 and cnti.county_tax = 'Y'
3091 and cnti.effective_start_date <= l_year_end
3092 and cnti.effective_end_date >= l_year_start;
3093
3094
3095 /* Get the jurisdiction code of all the states
3096 for the person_id corresponding to the assignment_id */
3097 /*
3098 cursor c_get_state is
3099 select distinct pst.jurisdiction_code
3100 from pay_us_state_tax_info_f sti,
3101 pay_us_emp_state_tax_rules_f pst,
3102 per_all_assignments_f paf1
3103 where paf1.person_id = l_person_id
3104 and paf1.effective_end_date >= l_year_start
3105 and paf1.effective_start_date <= l_year_end
3106 and paf1.assignment_id = pst.assignment_id
3107 and pst.effective_start_date <= l_year_end
3108 and pst.effective_end_date >= l_year_start
3109 and sti.state_code = pst.state_code
3110 and sti.sit_exists = 'Y'
3111 and sti.effective_start_date <= l_year_end
3112 and sti.effective_end_date >= l_year_start;
3113 */
3114
3115 cursor c_get_state is
3116 select distinct pst.jurisdiction_code
3117 from pay_us_emp_state_tax_rules_f pst,
3118 per_all_assignments_f paf1
3119 where paf1.person_id = l_person_id
3120 and paf1.effective_end_date >= l_year_start
3121 and paf1.effective_start_date <= l_year_end
3122 and paf1.assignment_id = pst.assignment_id
3123 and pst.effective_start_date <= l_year_end
3124 and pst.effective_end_date >= l_year_start;
3125
3126 cursor c_get_cnt_sd is
3127 select distinct pcnt.state_code || '-'|| pcnt.school_district_code,
3128 pcnt.county_code
3129 from pay_us_emp_county_tax_rules_f pcnt,
3130 per_all_assignments_f paf1
3131 where paf1.person_id = l_person_id
3132 and paf1.effective_end_date >= l_year_start
3133 and paf1.effective_start_date <= l_year_end
3134 and paf1.assignment_id = pcnt.assignment_id
3135 and pcnt.school_district_code is not null
3136 and pcnt.effective_start_date <= l_year_end
3137 and pcnt.effective_end_date >= l_year_start;
3138
3139 cursor c_get_cty_sd is
3140 select distinct pcty.state_code || '-'|| pcty.school_district_code,
3141 county_code,
3142 city_code
3143 from pay_us_emp_city_tax_rules_f pcty,
3144 per_all_assignments_f paf1
3145 where paf1.person_id = l_person_id
3146 and paf1.effective_end_date >= l_year_start
3147 and paf1.effective_start_date <= l_year_end
3148 and pcty.assignment_id = paf1.assignment_id
3149 and pcty.school_district_code is not null
3150 and pcty.effective_start_date <= l_year_end
3151 and pcty.effective_end_date >= l_year_start;
3152
3153 -- Bug 5696031
3154 -- Modified the cursor to remove the order by clause that was there before.
3155 -- The select clause has been modified to get the Assignment Action ID
3156 -- associated with Maximum Action Sequence
3157 CURSOR c_get_latest_asg(p_person_id number ) IS
3158 select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')
3159 ||lpad(paa.assignment_action_id,15,'0')),16))
3160 from pay_assignment_actions paa,
3161 per_all_assignments_f paf,
3162 pay_payroll_actions ppa,
3163 pay_action_classifications pac
3164 where paf.person_id = p_person_id
3165 and paa.assignment_id = paf.assignment_id
3166 and paa.tax_unit_id = l_taxunitid
3167 and paa.payroll_action_id = ppa.payroll_action_id
3168 and ppa.action_type = pac.action_type
3169 and pac.classification_name = 'SEQUENCED'
3170 and ppa.effective_date +0 between paf.effective_start_date
3171 and paf.effective_end_date
3172 and ppa.effective_date +0 between l_year_start and
3173 l_year_end
3174 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
3175 and paa.source_action_id is null)
3176 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
3177 and paa.source_action_id is not null )
3178 or (ppa.action_type = 'V' and ppa.run_type_id is null
3179 and paa.run_type_id is not null
3180 and paa.source_action_id is null));
3181
3182 CURSOR c_get_1099_eit_info(cp_assignment_id in number ) IS
3183 select aei_information1,
3184 aei_information2,
3185 aei_information3,
3186 aei_information4
3187 from per_assignment_extra_info
3188 where information_type = 'PAY_US_PENSION_REPORTING'
3189 and assignment_id = cp_assignment_id;
3190
3191 CURSOR c_get_1099_distribution_info(cp_person_id in number,
3192 cp_tax_unit_id in number) IS
3193 select pei_information2
3194 from per_people_extra_info target
3195 where person_id = cp_person_id
3196 and target.pei_information1 = cp_tax_unit_id
3197 and information_type= 'PAY_US_PENSION_REPORTING';
3198
3199 --
3200 --
3201 CURSOR c_get_first_yr_roth_contrib(cp_person_id in number,
3202 cp_tax_unit_id in number) IS
3203 select pei_information3
3204 from per_people_extra_info target
3205 where person_id = cp_person_id
3206 and target.pei_information1 = cp_tax_unit_id
3207 and information_type= 'PAY_US_PENSION_REPORTING';
3208
3209 CURSOR c_get_disability_plan_scl_info(cp_assignment_id in number , cp_tax_unit_id in number) IS
3210 select hsck.segment19
3211 from per_all_assignments_f paf , hr_soft_coding_keyflex hsck
3212 where assignment_id = cp_assignment_id and
3213 paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id and
3214 hsck.segment1 = to_char(cp_tax_unit_id) and
3215 paf.effective_end_date = (select max(effective_end_date)
3216 from per_all_assignments_f paf1 , hr_soft_coding_keyflex hsck1
3217 where paf1.assignment_id = paf.assignment_id and
3218 paf1.soft_coding_keyflex_id = hsck1.soft_coding_keyflex_id and
3219 hsck1.segment1 = hsck.segment1);
3220
3221 /* This cursor is removed because now the NJ Disablily Plan ID will be stored in segment19 of
3222 Soft Coded KFF.
3223 CURSOR c_get_disability_plan_eit_info(cp_assignment_id in number) IS
3224 select aei_information1
3225 from per_assignment_extra_info
3226 where information_type = 'PAY_US_DISABILITY_PLAN_INFO'
3227 and assignment_id = cp_assignment_id;
3228 */
3229
3230 -- Adding the Following Cursor to Archive
3231 -- Year of Designated Roth Contribution
3232
3233 CURSOR c_prior_def_yr_roth(cp_asg_act_id IN NUMBER
3234 ,cp_asg_id IN NUMBER
3235 ,cp_ele_info1 IN VARCHAR2) IS
3236 SELECT TARGET.result_value
3237 FROM pay_assignment_actions BAL_ASSACT
3238 , pay_payroll_actions BACT
3239 , per_all_assignments_f ASS
3240 , pay_assignment_actions ASSACT
3241 , pay_payroll_actions PACT
3242 , pay_run_results RR
3243 , pay_run_result_values TARGET
3244 , pay_input_values_f PIV
3245 , pay_element_entries_f peef
3246 , pay_element_types_f petf
3247 where BAL_ASSACT.assignment_action_id = cp_asg_act_id
3248 and ASS.assignment_id = cp_asg_id
3249 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
3250 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
3251 and ASSACT.assignment_id = ASS.assignment_id
3252 and ASSACT.payroll_action_id = PACT.payroll_action_id
3253 and RR.assignment_action_id = ASSACT.assignment_action_id
3254 and TARGET.run_result_id = RR.run_result_id
3255 and TARGET.input_value_id = PIV.input_value_id
3256 and PIV.NAME = 'Year of Prior Deferral'
3257 and RR.element_entry_id = peef.element_entry_id
3258 and RR.entry_type = 'E'
3259 and peef.element_type_id = petf.element_type_id
3260 and petf.element_information_category = 'US_VOLUNTARY DEDUCTIONS'
3261 and petf.element_information1 = cp_ele_info1
3262 and PACT.effective_date between PIV.effective_start_date
3263 and PIV.effective_end_date
3264 and PACT.effective_date between peef.effective_start_date
3265 and peef.effective_end_date
3266 and PACT.effective_date between petf.effective_start_date
3267 and petf.effective_end_date
3268 and RR.status in ('P','PA')
3269 and ASSACT.assignment_id = ASS.assignment_id
3270 and ASS.person_id = (select person_id from per_all_assignments_f START_ASS
3271 where START_ASS.assignment_id = BAL_ASSACT.assignment_id
3272 and rownum = 1)
3273 and PACT.effective_date between ASS.effective_start_date
3274 and ASS.effective_end_date;
3275
3276 begin
3277
3278 --hr_utility.trace_on(null,'yepp');
3279
3280 hr_utility.trace('.....AAID is ' || to_char(p_assactid));
3281
3282 hr_utility.trace('Archive Data');
3283 hr_utility.set_location ('archive_data',1);
3284 hr_utility.trace('getting assignment');
3285
3286 l_step := 1;
3287
3288 SELECT aa.assignment_id,
3289 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
3290 aa.tax_unit_id,
3291 aa.chunk_number,
3292 aa.payroll_action_id,
3293 to_number(aa.serial_number)
3294 into l_asgid,
3295 l_date_earned,
3296 l_taxunitid,
3297 l_chunk,
3298 l_payroll_action_id,
3299 l_person_id
3300 FROM pay_assignment_actions aa
3301 WHERE aa.assignment_action_id = p_assactid;
3302
3303 /* If the chunk of the assignment is same as the minimun chunk
3304 for the payroll_action_id and the gre data has not yet been
3305 archived then archive the gre data i.e. the employer data */
3306
3307 l_step := 2;
3308
3309 hr_utility.trace('Chunk Number is : ' || to_char(l_chunk));
3310
3311 if l_chunk = g_min_chunk and g_archive_flag = 'N' then
3312
3313 l_step := 3;
3314 hr_utility.trace('eoy_archive_data archiving employer data');
3315
3316 if g_report_type <> 'W2C_PRE_PROCESS' then
3317
3318 eoy_archive_gre_data(
3319 p_payroll_action_id => l_payroll_action_id,
3320 p_tax_unit_id => l_taxunitid,
3321 p_jd_type => 'ALL',
3322 p_state_code => 'ALL');
3323 else
3324 g_archive_flag := 'Y';
3325 end if ;
3326
3327 l_step := 4;
3328 hr_utility.trace('eoy_archive_data archived employer data');
3329 end if;
3330
3331 hr_utility.set_location ('archive_data',2);
3332
3333 hr_utility.trace('assignment '|| to_char(l_asgid));
3334 hr_utility.trace('person id '|| to_char(l_person_id));
3335 hr_utility.trace('date_earned '|| to_char(l_date_earned));
3336 hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
3337
3338 /* Derive the beginning and end of the effective year */
3339
3340 hr_utility.trace('getting begin and end dates');
3341
3342 l_step := 5;
3343
3344 l_year_start := trunc(p_effective_date, 'Y');
3345 l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
3346
3347 hr_utility.trace('year start '|| to_char(l_year_start));
3348 hr_utility.trace('year end '|| to_char(l_year_end));
3349
3350 open c_get_latest_asg(l_person_id );
3351 fetch c_get_latest_asg into l_aaid;
3352 hr_utility.trace('l_aaid in archive code '||to_char(l_aaid));
3353 close c_get_latest_asg;
3354
3355
3356 /* Initialise the PL/SQL table before populating it */
3357
3358 hr_utility.trace('Initialising Pl/SQL table');
3359
3360 l_step := 6;
3361
3362 for i in 1..l_context_no loop
3363
3364 pay_archive.g_context_values.name(i) := NULL;
3365 pay_archive.g_context_values.value(i) := NULL;
3366
3367 end loop;
3368
3369 pay_archive.g_context_values.sz := 0;
3370
3371 /* Set up the assignment id, date earned and tax unit id contexts */
3372
3373 l_step := 7;
3374
3375 l_count := l_count + 1;
3376 pay_archive.g_context_values.name(l_count) := 'ASSIGNMENT_ID';
3377 pay_archive.g_context_values.value(l_count) := l_asgid;
3378 l_count := l_count + 1;
3379 pay_archive.g_context_values.name(l_count) := 'DATE_EARNED';
3380 pay_archive.g_context_values.value(l_count) := fnd_date.date_to_canonical(l_date_earned);
3381 l_count := l_count + 1;
3382 pay_archive.g_context_values.name(l_count) := 'TAX_UNIT_ID';
3383 pay_archive.g_context_values.value(l_count) := l_taxunitid;
3384
3385 hr_utility.trace('Initialised Pl/SQL table');
3386
3387 /* Get the context_id for 'Jurisdiction' from ff_contexts */
3388
3389 l_step := 8;
3390 /*
3391 select context_id
3392 into l_jursd_context_id
3393 from ff_contexts
3394 where context_name = 'JURISDICTION_CODE';
3395
3396 select context_id
3397 into l_tax_unit_context_id
3398 from ff_contexts
3399 where context_name = 'TAX_UNIT_ID';
3400 */
3401
3402 /* get the user_entity_id of the dbis A_STATE_ABBREV, A_COUNTY_NAME,
3403 A_CITY_NAME, A_COUNTY_SD_NAME and A_CITY_SD_NAME */
3404 /*
3405 l_step := 9;
3406
3407 l_state_uei := get_user_entity_id('A_STATE_ABBREV');
3408
3409 l_step := 10;
3410
3411 l_county_uei := get_user_entity_id('A_COUNTY_NAME');
3412
3413 l_step := 11;
3414
3415 l_city_uei := get_user_entity_id('A_CITY_NAME');
3416
3417 l_step := 12;
3418
3419 l_county_sd_uei := get_user_entity_id('A_COUNTY_SD_NAME');
3420
3421 l_step := 13;
3422
3423 l_city_sd_uei := get_user_entity_id('A_CITY_SD_NAME');
3424 */
3425
3426 l_step := 14;
3427 /* Now, set up the jurisdiction context for the db items that
3428 need the jurisdiction as a context */
3429
3430 l_true := 'N';
3431 open c_get_city;
3432 loop
3433
3434 hr_utility.trace('In city loop ');
3435
3436 l_step := 15;
3437
3438 fetch c_get_city into l_jurisdiction;
3439 exit when c_get_city%NOTFOUND;
3440
3441 hr_utility.trace('assignment '|| to_char(l_asgid));
3442 hr_utility.trace('City JD is ' || l_jurisdiction);
3443
3444 l_step := 16;
3445
3446 l_count := l_count + 1;
3447 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
3448 pay_archive.g_context_values.value(l_count) := l_jurisdiction;
3449
3450 /* Insert rows into ff_archive_items and ff_archive_item_contexts
3451 for the city, county and state */
3452
3453 l_step := 17;
3454
3455 l_jd_index := replace(l_jurisdiction,'-');
3456
3457 if l_jd_name_done_tab.exists(l_jd_index) then
3458
3459 l_city_name := l_jd_name_done_tab(l_jd_index).jd_name;
3460 hr_utility.trace('Getting City Name from cache '|| l_city_name);
3461
3462 else
3463
3464 hr_utility.trace('Getting City Name from DB');
3465
3466 select city_name
3467 into l_city_name
3468 from pay_us_city_names pcn
3469 where pcn.state_code = substr(l_jurisdiction,1,2)
3470 and pcn.county_code = substr(l_jurisdiction,4,3)
3471 and pcn.city_code = substr(l_jurisdiction,8,4)
3472 and pcn.primary_flag = 'Y';
3473
3474 l_jd_name_done_tab(l_jd_index).jd_name := l_city_name;
3475
3476 end if;
3477
3478 hr_utility.trace('Archiving the city ' || l_jurisdiction);
3479
3480 l_balance_feed_tab.delete;
3481 l_defined_balance_id_tab.delete;
3482 l_user_entity_id_tab.delete;
3483 l_value_tab.delete;
3484 l_seq_tab.delete;
3485 l_context_id_tab.delete;
3486 l_context_val_tab.delete;
3487 l_index := 0;
3488
3489 /* Assign values to the PL/SQL tables */
3490
3491 l_step := 18;
3492 l_user_entity_id_tab(1) := g_city_uei;
3493 l_value_tab(1) := l_city_name;
3494 l_seq_tab(1) := 1;
3495 l_context_id_tab(1) := g_jursd_context_id;
3496 l_context_val_tab(1) := l_jurisdiction;
3497
3498
3499 create_archive (p_user_entity_id => l_user_entity_id_tab,
3500 p_context1 => p_assactid,
3501 p_value => l_value_tab,
3502 p_sequence => l_seq_tab,
3503 p_context => l_context_val_tab,
3504 p_context_id => l_context_id_tab);
3505
3506 l_jd_done_tab(nvl(l_jd_done_tab.last+1,1)) := l_context_val_tab(1);
3507
3508 end loop;
3509 close c_get_city;
3510
3511 hr_utility.trace('Out of city loop ');
3512
3513 l_step := 19;
3514 open c_get_county;
3515 loop
3516
3517 hr_utility.trace('In county loop ');
3518
3519 l_step := 20;
3520
3521 fetch c_get_county into l_jurisdiction;
3522 exit when c_get_county%NOTFOUND;
3523
3524 hr_utility.trace('assignment '|| to_char(l_asgid));
3525 hr_utility.trace('County JD is ' || l_jurisdiction);
3526
3527 l_jd_index := replace(l_jurisdiction,'-');
3528
3529 l_step := 21;
3530 l_true := 'N';
3531 l_chk_county_archive := 'N';
3532
3533 if l_jd_done_tab.last is not null then
3534
3535 for i in 1..l_jd_done_tab.last LOOP
3536
3537 if substr(l_jd_done_tab(i),1,7) = substr(l_jurisdiction,1,7) then
3538 l_true := 'Y';
3539 end if;
3540
3541 if l_jd_done_tab(i) = l_jurisdiction then
3542 l_chk_county_archive := 'Y';
3543 exit;
3544 end if;
3545
3546 end loop;
3547
3548 end if;
3549
3550 if l_true = 'N' then
3551 l_count := l_count + 1;
3552 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
3553 pay_archive.g_context_values.value(l_count) := l_jurisdiction;
3554 end if;
3555
3556 /* Now archive the county */
3557
3558 if l_chk_county_archive = 'N' then
3559
3560 l_step := 22;
3561
3562 if l_jd_name_done_tab.exists(l_jd_index) then
3563
3564 l_county_name := l_jd_name_done_tab(l_jd_index).jd_name;
3565 hr_utility.trace('Getting County Name from cache '|| l_county_name);
3566
3567 else
3568
3569 hr_utility.trace('Getting County Name from DB');
3570
3571 select county_name
3572 into l_county_name
3573 from pay_us_counties puc
3574 where puc.state_code = substr(l_jurisdiction,1,2)
3575 and puc.county_code = substr(l_jurisdiction,4,3);
3576
3577 l_jd_name_done_tab(l_jd_index).jd_name := l_county_name;
3578
3579 end if; /* l_jd_name_done_tab.exists(l_jd_index) */
3580
3581 end if; /* l_chk_county_archive = 'N' */
3582
3583
3584 l_step := 23;
3585
3586 hr_utility.trace('Archive county '||substr(l_jurisdiction,1,7)||'0000');
3587
3588 l_user_entity_id_tab.delete;
3589 l_value_tab.delete;
3590 l_seq_tab.delete;
3591 l_context_id_tab.delete;
3592 l_context_val_tab.delete;
3593
3594 /* Assign values to the PL/SQL tables */
3595
3596 l_step := 24;
3597
3598 l_user_entity_id_tab(1) := g_county_uei;
3599 l_value_tab(1) := l_county_name;
3600 l_seq_tab(1) := 1;
3601 l_context_id_tab(1) := g_jursd_context_id;
3602 l_context_val_tab(1) := substr(l_jurisdiction,1,7) || '0000';
3603
3604 create_archive (p_user_entity_id => l_user_entity_id_tab,
3605 p_context1 => p_assactid,
3606 p_value => l_value_tab,
3607 p_sequence => l_seq_tab,
3608 p_context => l_context_val_tab,
3609 p_context_id => l_context_id_tab);
3610
3611 l_jd_done_tab(nvl(l_jd_done_tab.last+1,1)) := l_context_val_tab(1);
3612
3613 end loop;
3614 close c_get_county;
3615
3616 hr_utility.trace('Out of county loop ');
3617
3618 l_step := 25;
3619
3620 open c_get_state;
3621 loop
3622
3623 hr_utility.trace('In state loop ');
3624 fetch c_get_state into l_jurisdiction;
3625 exit when c_get_state%NOTFOUND;
3626
3627 hr_utility.trace('assignment '|| to_char(l_asgid));
3628 hr_utility.trace('State JD is ' || l_jurisdiction);
3629
3630 l_jd_index := replace(l_jurisdiction,'-');
3631
3632 l_true := 'N';
3633 l_chk_state_archive := 'N';
3634
3635 if l_jd_done_tab.last is not null then
3636
3637 for i in 1..l_jd_done_tab.last LOOP
3638
3639 if substr(l_jd_done_tab(i),1,2) = substr(l_jurisdiction,1,2) then
3640 l_true := 'Y';
3641 end if;
3642
3643 if l_jd_done_tab(i) = l_jurisdiction then
3644 l_chk_state_archive := 'Y';
3645 exit;
3646 end if;
3647
3648 end loop;
3649
3650 end if; /* l_jd_done_tab.last is not null */
3651
3652
3653 if l_true = 'N' then
3654 l_count := l_count + 1;
3655 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
3656 pay_archive.g_context_values.value(l_count) := l_jurisdiction;
3657 end if;
3658
3659 if l_chk_state_archive = 'N' then
3660
3661 if l_jd_name_done_tab.exists(l_jd_index) then
3662
3663 l_state_abbrev := l_jd_name_done_tab(l_jd_index).jd_name;
3664 hr_utility.trace('Getting State Abbrev from cache '|| l_state_abbrev);
3665
3666 else
3667
3668 hr_utility.trace('Getting State Abbrev from DB');
3669
3670 l_step := 26;
3671
3672 select state_abbrev
3673 into l_state_abbrev
3674 from pay_us_states pus
3675 where pus.state_code = substr(l_jurisdiction,1,2);
3676
3677 l_jd_name_done_tab(l_jd_index).jd_name := l_state_abbrev;
3678
3679 end if; /* l_jd_name_done_tab.exists(l_jd_index) */
3680
3681 end if; /* l_chk_state_archive = 'N' */
3682
3683 l_step := 27;
3684
3685 hr_utility.trace('Archive state' ||l_jurisdiction);
3686
3687 l_user_entity_id_tab.delete;
3688 l_value_tab.delete;
3689 l_seq_tab.delete;
3690 l_context_id_tab.delete;
3691 l_context_val_tab.delete;
3692
3693 /* Assign values to the PL/SQL tables */
3694
3695 l_step := 28;
3696
3697 hr_utility.trace('Value of g_state_uei is : ' || to_char(g_state_uei));
3698
3699 l_user_entity_id_tab(1) := g_state_uei;
3700 l_value_tab(1) := l_state_abbrev;
3701 l_seq_tab(1) := 1;
3702 l_context_id_tab(1) := g_jursd_context_id;
3703 l_context_val_tab(1) := substr(l_jurisdiction,1,3) || '000-0000';
3704
3705 create_archive (p_user_entity_id => l_user_entity_id_tab,
3706 p_context1 => p_assactid,
3707 p_value => l_value_tab,
3708 p_sequence => l_seq_tab,
3709 p_context => l_context_val_tab,
3710 p_context_id => l_context_id_tab);
3711
3712 l_jd_done_tab(nvl(l_jd_done_tab.last+1,1)) := l_context_val_tab(1);
3713
3714 end loop;
3715 close c_get_state;
3716
3717 hr_utility.trace('Out of state loop ');
3718
3719 l_step := 39;
3720
3721 open c_get_cnt_sd;
3722 loop
3723 l_step := 40;
3724 hr_utility.trace('In sd loop ');
3725 fetch c_get_cnt_sd into l_jurisdiction,l_county_code;
3726 exit when c_get_cnt_sd%NOTFOUND;
3727
3728 l_step := 41;
3729 l_true := 'N';
3730 l_chk_cnt_sd_archive := 'N';
3731
3732 if l_jd_done_tab.last is not null then
3733 for i in 1..l_jd_done_tab.last LOOP
3734 if substr(l_jd_done_tab(i),1,8) = substr(l_jurisdiction,1,8) then
3735 l_true := 'Y';
3736 end if;
3737 if l_jd_done_tab(i) = l_jurisdiction then
3738 l_chk_cnt_sd_archive := 'Y';
3739 end if;
3740 end loop;
3741 end if;
3742
3743 if l_true = 'N' then
3744 l_count := l_count + 1;
3745 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
3746 pay_archive.g_context_values.value(l_count) := substr(l_jurisdiction,1,8);
3747 end if;
3748 l_step := 42;
3749
3750 if l_chk_cnt_sd_archive = 'N' then
3751 select school_dst_name
3752 into l_cnt_sd_name
3753 from pay_us_county_school_dsts pcs
3754 where pcs.state_code = substr(l_jurisdiction,1,2)
3755 and pcs.county_code = l_county_code
3756 and school_dst_code = substr(l_jurisdiction,4,5);
3757
3758 l_step := 43;
3759
3760 l_user_entity_id_tab.delete;
3761 l_value_tab.delete;
3762 l_seq_tab.delete;
3763 l_context_id_tab.delete;
3764 l_context_val_tab.delete;
3765
3766 /* Assign values to the PL/SQL tables */
3767
3768 l_step := 44;
3769
3770 --l_user_entity_id_tab(1) := l_county_sd_uei;
3771 l_user_entity_id_tab(1) := g_county_sd_uei;
3772 l_value_tab(1) := l_cnt_sd_name;
3773 l_seq_tab(1) := 1;
3774 --l_context_id_tab(1) := l_jursd_context_id;
3775 l_context_id_tab(1) := g_jursd_context_id;
3776 l_context_val_tab(1) := substr(l_jurisdiction,1,8);
3777
3778 create_archive (p_user_entity_id => l_user_entity_id_tab,
3779 p_context1 => p_assactid,
3780 p_value => l_value_tab,
3781 p_sequence => l_seq_tab,
3782 p_context => l_context_val_tab,
3783 p_context_id => l_context_id_tab);
3784
3785 l_jd_done_tab(nvl(l_jd_done_tab.last+1,1)) := l_context_val_tab(1);
3786 else
3787 NULL; /* County school district already archived */
3788 end if;
3789
3790 end loop;
3791 close c_get_cnt_sd;
3792
3793 hr_utility.trace('Out of cnt_sd loop ');
3794
3795 l_step := 45;
3796
3797 open c_get_cty_sd;
3798 loop
3799
3800 hr_utility.trace('In cty_sd loop ');
3801
3802 l_step := 46;
3803
3804 fetch c_get_cty_sd into l_jurisdiction,l_county_code, l_city_code;
3805 exit when c_get_cty_sd%NOTFOUND;
3806
3807 l_step := 47;
3808 l_true := 'N';
3809 l_chk_city_sd_archive := 'N';
3810
3811 if l_jd_done_tab.last is not null then
3812 for i in 1..l_jd_done_tab.last LOOP
3813 if substr(l_jd_done_tab(i),1,8) = substr(l_jurisdiction,1,8) then
3814 l_true := 'Y';
3815 end if;
3816 if l_jd_done_tab(i) = l_jurisdiction then
3817 l_chk_city_sd_archive := 'Y';
3818 end if;
3819 end loop;
3820 end if;
3821
3822 if l_true = 'N' then
3823
3824 l_count := l_count + 1;
3825 pay_archive.g_context_values.name(l_count) := 'JURISDICTION_CODE';
3826 pay_archive.g_context_values.value(l_count) := substr(l_jurisdiction,1,8);
3827 end if;
3828 l_step := 48;
3829
3830 if l_chk_city_sd_archive = 'N' then
3831
3832 select school_dst_name
3833 into l_cty_sd_name
3834 from pay_us_city_school_dsts pcs
3835 where pcs.state_code = substr(l_jurisdiction,1,2)
3836 and pcs.county_code = l_county_code
3837 and pcs.city_code = l_city_code
3838 and school_dst_code = substr(l_jurisdiction,4,5);
3839
3840 l_step := 49;
3841
3842 l_user_entity_id_tab.delete;
3843 l_value_tab.delete;
3844 l_seq_tab.delete;
3845 l_context_id_tab.delete;
3846 l_context_val_tab.delete;
3847
3848 /* Assign values to the PL/SQL tables */
3849
3850 l_step := 50;
3851
3852 --l_user_entity_id_tab(1) := l_city_sd_uei;
3853 l_user_entity_id_tab(1) := g_city_sd_uei;
3854 l_value_tab(1) := l_cty_sd_name;
3855 l_seq_tab(1) := 1;
3856 --l_context_id_tab(1) := l_jursd_context_id;
3857 l_context_id_tab(1) := g_jursd_context_id;
3858 l_context_val_tab(1) := substr(l_jurisdiction,1,8);
3859
3860 create_archive (p_user_entity_id => l_user_entity_id_tab,
3861 p_context1 => p_assactid,
3862 p_value => l_value_tab,
3863 p_sequence => l_seq_tab,
3864 p_context => l_context_val_tab,
3865 p_context_id => l_context_id_tab);
3866
3867 l_jd_done_tab(nvl(l_jd_done_tab.last+1,1)) := l_context_val_tab(1);
3868 else
3869 NULL; /* City school district already archived */
3870 end if;
3871 end loop;
3872 close c_get_cty_sd;
3873
3874 hr_utility.trace('Out of cty_sd loop ');
3875
3876 l_step := 51;
3877
3878 /* Set the no. of contexts */
3879 pay_archive.g_context_values.sz := l_count;
3880
3881 if l_count = 1 then
3882 -- pay_balance_pkg.set_context ('JURISDICTION_CODE',lt_jursd_context(1));
3883 -- lt_jursd_context(1) := NULL;
3884 hr_utility.trace('One context only name : ' || pay_archive.g_context_values.name(1));
3885 hr_utility.trace('One context only value : ' || pay_archive.g_context_values.value(1));
3886 else
3887 for i in 1..l_count loop
3888 hr_utility.trace('Multiple context name : ' || pay_archive.g_context_values.name(i));
3889 hr_utility.trace('Multiple context value : ' || pay_archive.g_context_values.value(i));
3890 -- hr_utility.trace('Multiple context ('|| to_char(i)||') : ' || lt_jursd_context(i));
3891 end loop;
3892 end if;
3893
3894 hr_utility.trace('g_context_values.sz : ' || pay_archive.g_context_values.sz);
3895
3896 /* Flush all jurisdiction contexts */
3897
3898 hr_utility.trace('l_jd_done_tab....first : '|| l_jd_done_tab.first);
3899 hr_utility.trace('l_jd_done_tab....last : '|| l_jd_done_tab.last);
3900
3901 if l_jd_done_tab.count > 0 then
3902
3903 for i in l_jd_done_tab.first .. l_jd_done_tab.last loop
3904
3905 hr_utility.trace('l_jd_done_tab.... value of : '|| i ||' is '|| l_jd_done_tab(i) );
3906
3907 end loop;
3908
3909 end if;
3910
3911 /* We need to clear out the table per employee. There is some relation
3912 with the jd stored in this table and the jd context that is stored
3913 when making call to JD specific balances. However we are chaching
3914 the names of the JD to avoid the DB calls */
3915
3916 l_jd_done_tab.delete;
3917
3918 l_step := 52;
3919
3920 for ln_count in pay_us_archive.ltr_pre_tax_bal.first ..
3921 pay_us_archive.ltr_pre_tax_bal.last loop
3922
3923 l_step := 53;
3924
3925 lv_value := nvl(pay_balance_pkg.get_value
3926 (p_defined_balance_id => pay_us_archive.ltr_pre_tax_bal(ln_count).defined_balance,
3927 p_assignment_action_id => l_aaid),0);
3928
3929 l_step := 54;
3930 hr_utility.trace('lv_value is '||lv_value);
3931
3932 ff_archive_api.create_archive_item(
3933 p_archive_item_id => l_archive_item_id,
3934 p_user_entity_id => pay_us_archive.ltr_pre_tax_bal(ln_count).user_entity_id,
3935 p_archive_value => lv_value,
3936 p_archive_type => '',
3937 p_action_id => p_assactid,
3938 p_legislation_code => 'US',
3939 p_object_version_number => l_object_version_number,
3940 p_some_warning => l_some_warning,
3941 p_context_name1 => 'TAX_UNIT_ID',
3942 p_context1 => l_taxunitid);
3943
3944 l_step := 55;
3945
3946 end loop;
3947
3948
3949 l_step := 56;
3950
3951
3952 /* Puerto Rico Specific Archive */
3953
3954 if g_puerto_rico_gre = 'Y' then
3955
3956 hr_utility.trace('Entered Puerto Rico GRE ');
3957
3958 l_step := 57;
3959 l_user_entity_id_tab.delete;
3960 l_value_tab.delete;
3961 l_seq_tab.delete;
3962 l_context_id_tab.delete;
3963 l_context_val_tab.delete;
3964 l_defined_balance_id_tab.delete;
3965 l_balance_feed_tab.delete;
3966 l_index := 0;
3967
3968 begin
3969
3970 select ppf.marital_status
3971 into l_marital_status
3972 from per_people_f ppf
3973 where ppf.person_id = l_person_id
3974 and l_date_earned between ppf.effective_start_date
3975 and ppf.effective_end_date;
3976
3977
3978 exception when no_data_found then
3979 l_marital_status := null;
3980 end;
3981
3982 begin
3983
3984 select ppf.national_identifier
3985 into l_con_national_identifier
3986 from per_people_f ppf,
3987 per_contact_relationships ctr
3988 where ctr.person_id = ppf.person_id
3989 and ctr.contact_person_id = l_person_id
3990 /* and ctr.personal_flag = 'Y'*/
3991 and ctr.contact_type = 'S'
3992 and l_date_earned between ppf.effective_start_date
3993 and ppf.effective_end_date
3994 and ctr.date_start =
3995 (select max(ctr1.date_start)
3996 from per_contact_relationships ctr1
3997 where ctr1.person_id = l_person_id
3998 and ctr1.date_start <= l_year_end
3999 and nvl(ctr1.date_end,
4000 fnd_date.canonical_to_date('4712/12/31 00:00:00'))
4001 >= l_year_start);
4002
4003
4004 exception when no_data_found then
4005 l_con_national_identifier := null;
4006 end;
4007
4008 hr_utility.trace('Maritial Status = '||l_marital_status);
4009 hr_utility.trace('Contact National Identifier = '||l_con_national_identifier);
4010
4011 pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4012
4013 l_step := 58;
4014
4015 for k in pay_us_archive.ltr_pr_balances.first ..
4016 pay_us_archive.ltr_pr_balances.last loop
4017
4018 l_index := l_user_entity_id_tab.count + 1;
4019
4020 l_user_entity_id_tab(l_index) := pay_us_archive.ltr_pr_balances(k).user_entity_id;
4021 l_defined_balance_id_tab(l_index) := pay_us_archive.ltr_pr_balances(k).defined_balance;
4022 l_value_tab(l_index) := nvl(pay_balance_pkg.get_value
4023 (p_defined_balance_id =>l_defined_balance_id_tab(l_index) ,
4024 p_assignment_action_id => l_aaid),0);
4025
4026 end loop;
4027
4028
4029 l_step := 64;
4030 --l_user_entity_id_tab(7) := get_user_entity_id('A_PER_MARITAL_STATUS');
4031 l_user_entity_id_tab(7) := g_per_marital_status;
4032
4033 l_step := 65;
4034 --l_user_entity_id_tab(8) := get_user_entity_id('A_CON_NATIONAL_IDENTIFIER');
4035 l_user_entity_id_tab(8) := g_con_national_identifier;
4036
4037 l_step := 66;
4038
4039 l_step := 72;
4040 l_value_tab(7) := l_marital_status;
4041
4042 l_step := 73;
4043 l_value_tab(8) := l_con_national_identifier;
4044
4045 l_step := 74;
4046
4047 l_seq_tab(1) := 1;
4048 --l_context_id_tab(1) := l_tax_unit_context_id;
4049 l_context_id_tab(1) := g_tax_unit_context_id;
4050 l_context_val_tab(1) := l_taxunitid;
4051
4052 create_archive (p_user_entity_id => l_user_entity_id_tab,
4053 p_context1 => p_assactid,
4054 p_value => l_value_tab,
4055 p_sequence => l_seq_tab,
4056 p_context => l_context_val_tab,
4057 p_context_id => l_context_id_tab);
4058
4059 end if; /* Special archiving for Puerto Rico */
4060
4061 l_step := 75;
4062
4063 /* 1099R 2002 */
4064
4065
4066 if g_1099R_transmitter_code is not null then
4067 hr_utility.trace('Into g_1099r_transmitter_code not equal to null');
4068 l_step := 76;
4069
4070 l_user_entity_id_tab.delete;
4071 l_defined_balance_id_tab.delete;
4072 l_balance_feed_tab.delete;
4073 l_value_tab.delete;
4074 l_seq_tab.delete;
4075 l_context_id_tab.delete;
4076 l_context_val_tab.delete;
4077 l_index := 0;
4078
4079 hr_utility.trace('Deleted plsql tables ');
4080
4081 begin
4082
4083 open c_get_1099_eit_info(l_asgid);
4084 hr_utility.trace('Opened c_get_1099_eit_info ');
4085 fetch c_get_1099_eit_info into l_taxable_amount_unknown
4086 ,l_total_distributions
4087 ,l_ee_distribution_percent
4088 ,l_total_distribution_percent;
4089
4090 if c_get_1099_eit_info%NOTFOUND then
4091 l_taxable_amount_unknown := null ;
4092 l_total_distributions := null ;
4093 l_ee_distribution_percent := null ;
4094 l_total_distribution_percent := null ;
4095 end if;
4096 close c_get_1099_eit_info;
4097
4098
4099
4100 exception when no_data_found then
4101 l_marital_status := null;
4102 end;
4103
4104 hr_utility.trace('l_taxable_amount_unknown = '||l_taxable_amount_unknown);
4105 hr_utility.trace('l_total_distributions = '||l_total_distributions);
4106 hr_utility.trace('l_ee_distribution_percent='||l_ee_distribution_percent);
4107 hr_utility.trace('l_total_distribution_percent = '||l_total_distribution_percent);
4108
4109
4110 pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4111
4112 l_step := 77;
4113
4114 for m in pay_us_archive.ltr_1099_bal.first ..
4115 pay_us_archive.ltr_1099_bal.last loop
4116
4117 l_index := l_user_entity_id_tab.count + 1;
4118
4119 l_user_entity_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).user_entity_id;
4120 l_defined_balance_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).defined_balance;
4121 l_balance_feed_tab(l_index) := pay_us_archive.ltr_1099_bal(m).feed_info;
4122
4123 if l_balance_feed_tab(l_index) = 'Y' then
4124
4125 l_value_tab(l_index) := nvl(pay_balance_pkg.get_value
4126 (p_defined_balance_id =>l_defined_balance_id_tab(l_index) ,
4127 p_assignment_action_id => l_aaid),0);
4128 else
4129 l_value_tab(l_index) := 0;
4130 end if;
4131
4132 end loop;
4133
4134 l_step := 78;
4135
4136 open c_get_1099_distribution_info(l_person_id,l_taxunitid);
4137 hr_utility.trace('Opened c_get_1099_distribution_info ');
4138
4139 l_step := 79;
4140
4141 fetch c_get_1099_distribution_info into l_distribution_code;
4142
4143 if c_get_1099_distribution_info%NOTFOUND then
4144 l_distribution_code := '7' ;
4145 elsif c_get_1099_distribution_info%ROWCOUNT > 1 then
4146 l_mesg :='Person id '||to_char(l_person_id)||' has multiple distribution code for one GRE';
4147 pay_core_utils.push_message(801,'PAY_EXCEPTION','A');
4148 pay_core_utils.push_token('description',substr(l_mesg,1,50));
4149 hr_utility.raise_error;
4150 end if;
4151
4152 close c_get_1099_distribution_info;
4153 --
4154 --
4155 open c_get_first_yr_roth_contrib(l_person_id,l_taxunitid);
4156 fetch c_get_first_yr_roth_contrib into l_first_yr_roth_contrib;
4157 if c_get_first_yr_roth_contrib%NOTFOUND then
4158 l_first_yr_roth_contrib := NULL;
4159 end if;
4160
4161 l_step := 83;
4162 --l_user_entity_id_tab(6) := get_user_entity_id('A_TAXABLE_AMOUNT_UNKNOWN');
4163 l_user_entity_id_tab(6) := g_taxable_amount_unknown;
4164
4165 l_step := 84;
4166 --l_user_entity_id_tab(7) := get_user_entity_id('A_TOTAL_DISTRIBUTIONS');
4167 l_user_entity_id_tab(7) := g_total_distributions;
4168
4169 l_step := 85;
4170 --l_user_entity_id_tab(8) := get_user_entity_id('A_EMPLOYEE_DISTRIBUTION_PERCENT');
4171 l_user_entity_id_tab(8) := g_emp_distribution_percent;
4172
4173 l_step := 86;
4174 --l_user_entity_id_tab(9) := get_user_entity_id('A_TOTAL_DISTRIBUTION_PERCENT');
4175 l_user_entity_id_tab(9) := g_total_distribution_percent;
4176
4177 l_step := 87;
4178 --l_user_entity_id_tab(10) := get_user_entity_id('A_DISTRIBUTION_CODE_FOR_1099R');
4179 l_user_entity_id_tab(10) := g_distribution_code_for_1099r;
4180 --
4181 -- Added For bug# 5517938
4182 l_user_entity_id_tab(11) := g_first_yr_roth_contrib;
4183
4184 l_step := 88;
4185
4186 l_value_tab(6) := l_taxable_amount_unknown;
4187
4188 l_step := 89;
4189 l_value_tab(7) := l_total_distributions;
4190
4191 l_step := 90;
4192 l_value_tab(8) := l_ee_distribution_percent;
4193
4194 l_step := 91;
4195 l_value_tab(9) := l_total_distribution_percent;
4196
4197 l_step := 92;
4198 l_value_tab(10) := l_distribution_code;
4199
4200 l_step := 93;
4201 l_value_tab(11) := l_first_yr_roth_contrib;
4202
4203 l_seq_tab(1) := 1;
4204 --l_context_id_tab(1) := l_tax_unit_context_id;
4205 l_context_id_tab(1) := g_tax_unit_context_id;
4206 l_context_val_tab(1) := l_taxunitid;
4207
4208 create_archive (p_user_entity_id => l_user_entity_id_tab,
4209 p_context1 => p_assactid,
4210 p_value => l_value_tab,
4211 p_sequence => l_seq_tab,
4212 p_context => l_context_val_tab,
4213 p_context_id => l_context_id_tab);
4214
4215 end if; /* Special archiving for 1099R GRE */
4216
4217 l_step := 94;
4218
4219 hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
4220 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
4221 pay_archive.balance_aa := l_aaid;
4222
4223 l_step := 95;
4224 pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4225
4226 l_user_entity_id_tab.delete;
4227 l_user_entity_tab.delete;
4228 l_value_tab.delete;
4229
4230 l_user_entity_tab(1) := 'SS_EE_TAXABLE_PER_GRE_YTD';
4231 l_user_entity_tab(2) := 'SS_EE_WITHHELD_PER_GRE_YTD';
4232 l_user_entity_tab(3) := 'MEDICARE_EE_TAXABLE_PER_GRE_YTD';
4233 l_user_entity_tab(4) := 'MEDICARE_EE_WITHHELD_PER_GRE_YTD';
4234
4235 l_user_entity_id_tab(1) := get_user_entity_id('A_'||l_user_entity_tab(1));
4236 l_user_entity_id_tab(2) := get_user_entity_id('A_'||l_user_entity_tab(2));
4237 l_user_entity_id_tab(3) := get_user_entity_id('A_'||l_user_entity_tab(3));
4238 l_user_entity_id_tab(4) := get_user_entity_id('A_'||l_user_entity_tab(4));
4239
4240
4241 for ln_count in 1..4 loop
4242
4243 l_step := 96;
4244
4245 hr_utility.trace('l_user_entity_tab('||ln_count||')is '||l_user_entity_tab(ln_count));
4246 hr_utility.trace('l_user_entity_id_tab('||ln_count||')is '||l_user_entity_id_tab(ln_count));
4247
4248 l_value_tab(ln_count) := nvl(pay_balance_pkg.get_value
4249 (p_defined_balance_id =>bal_db_item(l_user_entity_tab(ln_count)),
4250 p_assignment_action_id => l_aaid),0);
4251
4252 l_step := 97;
4253 hr_utility.trace('lv_value is '||l_value_tab(ln_count));
4254
4255 ff_archive_api.create_archive_item(
4256 p_archive_item_id => l_archive_item_id,
4257 p_user_entity_id => l_user_entity_id_tab(ln_count),
4258 p_archive_value => l_value_tab(ln_count),
4259 p_archive_type => '',
4260 p_action_id => p_assactid,
4261 p_legislation_code => 'US',
4262 p_object_version_number => l_object_version_number,
4263 p_some_warning => l_some_warning,
4264 p_context_name1 => 'TAX_UNIT_ID',
4265 p_context1 => l_taxunitid);
4266
4267 hr_utility.trace('l_archive_item_id is '||to_char(l_archive_item_id));
4268
4269 l_step := 98;
4270
4271 end loop;
4272
4273 l_step := 99;
4274
4275
4276 lv_medicare_withheld := l_value_tab(4) ;
4277 hr_utility.trace('lv_medicare_withheld is '||lv_medicare_withheld);
4278
4279 l_step := 100;
4280
4281 lv_ss_withheld := l_value_tab(2) ;
4282 hr_utility.trace('lv_ss_withheld is '||lv_ss_withheld);
4283
4284 l_step := 101;
4285
4286 l_user_entity_id_tab.delete;
4287 l_value_tab.delete;
4288 l_seq_tab.delete;
4289 l_context_id_tab.delete;
4290 l_context_val_tab.delete;
4291
4292 if pay_us_archive.g_govt_employer = 'Y' then
4293
4294 hr_utility.trace('Goverment employer is ');
4295 l_step := 102;
4296 l_value_tab(1) := pay_us_sqwl_udf.get_employment_code(
4297 p_medicare_wh => lv_medicare_withheld,
4298 p_ss_wh => lv_ss_withheld);
4299 hr_utility.trace('lv_value is '||l_value_tab(1));
4300
4301 l_step := 103;
4302 else
4303 l_step := 103.1;
4304 l_value_tab(1) := 'R';
4305 end if;
4306
4307 l_user_entity_id_tab(1) := get_user_entity_id('A_ASG_GRE_EMPLOYMENT_TYPE_CODE');
4308
4309 l_step := 104;
4310 hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
4311 l_seq_tab(1) := 1;
4312 --l_context_id_tab(1) := l_tax_unit_context_id;
4313 l_context_id_tab(1) := g_tax_unit_context_id;
4314 l_context_val_tab(1) := l_taxunitid;
4315
4316 l_step := 105;
4317 create_archive (p_user_entity_id => l_user_entity_id_tab,
4318 p_context1 => p_assactid,
4319 p_value => l_value_tab,
4320 p_sequence => l_seq_tab,
4321 p_context => l_context_val_tab,
4322 p_context_id => l_context_id_tab);
4323
4324 l_step := 106;
4325
4326 l_user_entity_id_tab.delete;
4327 l_value_tab.delete;
4328 l_seq_tab.delete;
4329 l_context_id_tab.delete;
4330 l_context_val_tab.delete;
4331
4332 l_step := 107;
4333
4334 -- Bug 4544792 : Removed the cursor c_get_disability_plan_eit_info
4335 open c_get_disability_plan_scl_info(l_asgid,l_taxunitid);
4336 hr_utility.trace('Opened c_get_disability_plan_scl_info ');
4337 fetch c_get_disability_plan_scl_info
4338 into l_disability_plan_id;
4339
4340 /*
4341 open c_get_disability_plan_eit_info(l_asgid);
4342 hr_utility.trace('Opened c_get_disability_plan_eit_info ');
4343 fetch c_get_disability_plan_eit_info
4344 into l_disability_plan_id;
4345 */
4346 if c_get_disability_plan_scl_info%NOTFOUND then
4347 l_disability_plan_id := null;
4348 end if;
4349 close c_get_disability_plan_scl_info;
4350
4351 hr_utility.trace('l_disability_plan_id = '||l_disability_plan_id);
4352 l_user_entity_id_tab(1) := g_disability_plan_id;
4353 l_value_tab(1) := l_disability_plan_id;
4354
4355 l_step := 108;
4356 hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
4357 hr_utility.trace(' l_value_tab is '||l_value_tab(1));
4358 l_seq_tab(1) := 1;
4359 --l_context_id_tab(1) := l_tax_unit_context_id;
4360 l_context_id_tab(1) := g_tax_unit_context_id;
4361 l_context_val_tab(1) := l_taxunitid;
4362
4363 create_archive (p_user_entity_id => l_user_entity_id_tab,
4364 p_context1 => p_assactid,
4365 p_value => l_value_tab,
4366 p_sequence => l_seq_tab,
4367 p_context => l_context_val_tab,
4368 p_context_id => l_context_id_tab);
4369
4370 l_step := 108;
4371
4372 -- A_ARCHIVE_DATE
4373
4374 l_user_entity_id_tab.delete;
4375 l_value_tab.delete;
4376 l_seq_tab.delete;
4377 l_context_id_tab.delete;
4378 l_context_val_tab.delete;
4379
4380 l_step := 109;
4381
4382 l_user_entity_id_tab(1) := g_archive_date;
4383 /* Bug# 4137906 - Time Info not required for the Date. So suppressing it */
4384 l_value_tab(1) := substr(fnd_date.date_to_canonical(sysdate),1,10);
4385
4386 l_step := 110;
4387 hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
4388 hr_utility.trace(' l_value_tab is '||l_value_tab(1));
4389 l_seq_tab(1) := 1;
4390 --l_context_id_tab(1) := l_tax_unit_context_id;
4391 l_context_id_tab(1) := g_tax_unit_context_id;
4392 l_context_val_tab(1) := l_taxunitid;
4393
4394
4395 create_archive (p_user_entity_id => l_user_entity_id_tab,
4396 p_context1 => p_assactid,
4397 p_value => l_value_tab,
4398 p_sequence => l_seq_tab,
4399 p_context => l_context_val_tab,
4400 p_context_id => l_context_id_tab);
4401
4402 l_step := 111;
4403
4404 l_user_entity_id_tab(1) := g_w2_corrected;
4405 l_add_archive :=pay_us_archive_util.get_archive_value(p_assactid,
4406 'A_ADD_ARCHIVE',
4407 l_taxunitid);
4408
4409 IF l_add_archive = 'Y' THEN
4410 l_value_tab(1) := 'N';
4411 ELSE
4412 l_value_tab(1) := print_w2_corrected(l_payroll_action_id,
4413 p_assactid,
4414 l_taxunitid);
4415 END IF;
4416
4417 l_step := 112;
4418 hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
4419 hr_utility.trace(' l_value_tab is '||l_value_tab(1));
4420 l_seq_tab(1) := 1;
4421 l_context_id_tab(1) := g_tax_unit_context_id;
4422 l_context_val_tab(1) := l_taxunitid;
4423
4424 create_archive (p_user_entity_id => l_user_entity_id_tab,
4425 p_context1 => p_assactid,
4426 p_value => l_value_tab,
4427 p_sequence => l_seq_tab,
4428 p_context => l_context_val_tab,
4429 p_context_id => l_context_id_tab);
4430
4431 l_step := 113;
4432
4433 l_user_entity_id_tab.delete;
4434 l_value_tab.delete;
4435 l_seq_tab.delete;
4436 l_context_id_tab.delete;
4437 l_context_val_tab.delete;
4438
4439 -- Starting From Year 2007 we will archive Year of Designated Roth
4440 -- Contribution for 401(k) and 403(b) [Bug# 5517938]
4441 -- Reverting back changes as not Needed.
4442 -- End of Change For [Bug# 5517938]
4443
4444 -- We have to clear the l_jd_done_tab and
4445 -- l_jd_name_done_tab before processing the next_employee
4446 -- Reference Bug# 5744676
4447
4448 l_jd_done_tab.delete;
4449 l_jd_name_done_tab.delete;
4450
4451
4452 hr_utility.trace_off;
4453
4454 exception when others then
4455
4456 raise_application_error(-20001,'Error in eoy_archive_data at step : '
4457 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
4458
4459
4460 END eoy_archive_data;
4461
4462
4463 /* Name : eoy_range_cursor
4464 Purpose : This returns the select statement that is used to created the
4465 range rows for the Year End Pre-Process.
4466 Arguments :
4467 Notes :
4468 */
4469 PROCEDURE eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
4470 l_eoy_tax_unit_id number;
4471 l_archive boolean:= FALSE;
4472 l_step number;
4473
4474 l_eoy_bg_id pay_payroll_actions.business_group_id%TYPE;
4475 l_start_date pay_payroll_actions.start_date%TYPE;
4476
4477 l_processed varchar2(20);
4478 l_mesg varchar2(100);
4479
4480 l_gre_name hr_organization_units.name%TYPE;
4481
4482 BEGIN
4483
4484 l_step := 1;
4485 hr_utility.trace('In eoy_range_cursor');
4486
4487 eoy_gre_range := 'SELECT distinct ASG.person_id
4488 FROM per_all_assignments_f ASG,
4489 pay_us_asg_reporting puar,
4490 pay_payroll_actions PPA
4491 WHERE PPA.payroll_action_id = :payroll_action_id
4492 AND puar.tax_unit_id = substr(legislative_parameters,
4493 instr(legislative_parameters,''TRANSFER_GRE='')+ length(''TRANSFER_GRE=''))
4494 AND asg.assignment_id = puar.assignment_id
4495 AND ASG.business_group_id + 0 = PPA.business_group_id
4496 AND ASG.assignment_type = ''E''
4497 AND ASG.effective_start_date <= PPA.effective_date
4498 AND ASG.effective_end_date >= PPA.start_date
4499 AND ASG.payroll_id is not null
4500 ORDER BY ASG.person_id';
4501
4502 select to_number(substr(legislative_parameters,INSTR(legislative_parameters,
4503 'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='),15)), business_group_id, start_date
4504 into l_eoy_tax_unit_id, l_eoy_bg_id, l_start_date
4505 from pay_payroll_actions
4506 where payroll_action_id = pactid;
4507
4508 hr_utility.trace('TUI is : ' || to_char(l_eoy_tax_unit_id));
4509 hr_utility.trace('BG is : ' || to_char(l_eoy_bg_id));
4510 hr_utility.trace('Start Date is : ' || to_char(l_start_date));
4511
4512 /* for bug 2149544 */
4513 begin
4514 l_processed := 'Z';
4515
4516 select hou.name into l_gre_name
4517 from hr_organization_information hoi,
4518 hr_organization_units hou
4519 where hoi.org_information_context = 'CLASS'
4520 and hoi.org_information1 = 'HR_LEGAL'
4521 and hoi.organization_id = hou.organization_id
4522 and hou.business_group_id = l_eoy_bg_id
4523 and hou.organization_id = l_eoy_tax_unit_id;
4524
4525 select 'X' into l_processed
4526 from pay_payroll_actions ppa1
4527 where ppa1.report_type = 'YREND'
4528 AND ppa1.business_group_id + 0 = l_eoy_bg_id
4529 AND ppa1.start_date = l_start_date
4530 AND ppa1.payroll_action_id <> pactid
4531 AND to_char(l_eoy_tax_unit_id) =
4532 substr(ltrim(rtrim( ppa1.legislative_parameters)),
4533 instr(ppa1.legislative_parameters,'TRANSFER_GRE=')+ length('TRANSFER_GRE='));
4534
4535 hr_utility.trace('Value of l_processed is : ' || l_processed);
4536
4537
4538 if l_processed = 'X' then
4539 hr_utility.trace('Value of l_processed is : ' || l_processed);
4540 l_mesg :='Error : GRE '||''''|| l_gre_name|| ''''||' has already been archived';
4541 pay_core_utils.push_message(801,'PAY_EXCEPTION_ERROR','P');
4542 pay_core_utils.push_token('description',l_mesg);
4543 hr_utility.raise_error;
4544 end if;
4545
4546 exception
4547 when no_data_found then
4548 null; /* meaning this is the only run */
4549
4550 when too_many_rows then
4551 l_mesg :='Error : GRE '||''''|| l_gre_name|| ''''||' has already been archived';
4552 pay_core_utils.push_message(801,'PAY_EXCEPTION_ERROR','P');
4553 pay_core_utils.push_token('description',l_mesg);
4554 hr_utility.raise_error;
4555
4556 end;
4557
4558
4559 l_step := 2;
4560 if l_eoy_tax_unit_id <> -99999 then
4561 l_step := 3;
4562 sqlstr := eoy_gre_range;
4563 l_step := 4;
4564 l_archive := chk_gre_archive(pactid);
4565 l_step := 5;
4566
4567 if g_archive_flag = 'N' then
4568 l_step := 6;
4569 hr_utility.trace('eoy_range_cursor archiving employer data');
4570 eoy_archive_gre_data(p_payroll_action_id => pactid,
4571 p_tax_unit_id => l_eoy_tax_unit_id,
4572 p_jd_type => 'ALL',
4573 p_state_code => 'ALL');
4574 l_step := 7;
4575 hr_utility.trace('eoy_range_cursor archiving employer data');
4576 end if;
4577 else
4578 l_step := 8;
4579 sqlstr := eoy_all_range;
4580 l_step := 9;
4581 end if;
4582
4583 exception when others then
4584 hr_utility.trace('eoy_range_cursor at : '
4585 ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
4586 END eoy_range_cursor;
4587
4588
4589 PROCEDURE eoy_deinit( p_payroll_action_id in number)
4590 IS
4591
4592 BEGIN
4593
4594 /* Clear of the plsql table we have been maintaining to store
4595 the jurisdiction code and name */
4596 l_jd_done_tab.delete;
4597 l_jd_name_done_tab.delete;
4598
4599 END eoy_deinit;
4600 --begin
4601
4602 --hr_utility.trace_on(null,'pyusarch');
4603
4604 END pay_us_archive;