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