[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ACTION_ARCH
Source
1 PACKAGE BODY pay_us_action_arch AS
2 /* $Header: pyusxfrp.pkb 120.15.12010000.4 2008/09/27 09:13:06 sudedas ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_action_arch
21
22 Description : This procedure is used by the External Process
23 Archive process to archive the Employee and
24 Employer Balances in pay_action_information table.
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- --------- ------ ------- --------------------------
30 27-SEP-2008 sudedas 115.114 7418142 Changed cursor c_get_unproc_asg
31 05-SEP-2008 asgugupt 115.113 7379102 Changed c_time_period
32 03-MAR-2008 sudedas 115.112 6827021 Changed action_archive_data
33 For Multi Assignment Enabled
34 Payroll.
35 13-09-2007 sausingh 115.111 5635335 Added nvl condition in procedure
36 update_ytd_withheld for action_info8
37 and actioninfo9
38 21-08-2007 sausingh 115.110 Added Cursor get_display_name
39 to proc update_ytd_withheld
40 26-JUN-2007 asasthan 115.109 5868672 Head Tax info retrieved from
41 JIT table.
42 28-NOV-2006 saikrish 115.108 5683349 Corrected the signature of
43 pay_get_tax_exists_pkg.
44 15-NOV-2006 ahanda 115.107 5264527 Changed select for action_creation
45 code to add hints.
46 Also, changed sql statement to
47 use base table instead secure
48 views.
49 13-APR-2006 ahanda 115.106 Changed HBR code to use amount
50 from pay_hours_by_rate_v.
51 24-MAR-2006 ahanda 115.105 4924263 Changed archive code to not check
52 if tax exists in a JD for
53 Bal Adj action types.
54 27-FEB-2006 ahanda 115.104 5058507 Changed HBR code to store the
55 pay value as amount as for Retro,
56 Hours or Rate could be null.
57 27-FEB-2006 ahanda 115.103 5058507 Changed HBR code to check for
58 Zero Retro Hours.
59 12-FEB-2006 ahanda 115.102 5003054 Changed code to archive multiple
60 retro entries for same element type
61 03-FEB-2006 ahanda 115.101 5003054 Added logic to retropay and hrsXrate
62 07-OCT-2005 ahanda 115.100 4642121 Changed the logic to archive element
63 even if Gross and Net Run are zero
64 4552807 Added call to process_baladj_elements
65 to archive baladj elements.
66 4640155 Changed cursor to get distinct JD
67 20-JUL-2005 ahanda 115.99 4500097 Added Ordered hint for subquery
68 28-JUN-2005 ahanda 115.98 4449712 Changed call to get_last_xfr_info
69 to pass EMPLOYEE DETAILS.
70 Changed cursor c_get_jd to not
71 pick up Federal JD.
72 06-JUN-2005 ahanda 115.97 Changed populate_emp_hours_by_rate
73 Storing Hours and Rate in canonical
74 format.
75 14-MAR-2005 sackumar 115.96 4222032 Change in the Range Cursor removing
76 use of bind Variable -
77 :payroll_action_id
78 17-MAR-2005 ahanda 115.94 4247361 Changed cursor c_payment_info
79 to add a distinct to ensure
80 and assignment is returned
81 only once.
82 4212744 Changed cursor c_get_baladj_jd
83 to remove join to
84 pay_run_result_values and other
85 tables.
86 05-NOV-2004 ahanda 115.94 3995766 Archiver not archiving
87 Balance Adjustment and Reversal
88 03-NOV-2004 ahanda 115.93 3979501 Added support for RANGE_PERSON_ID
89 06-OCT-2004 ahanda 115.92 3940380 Added parameter p_xfr_action_id
90 to get_last_xfr_info
91 06-AUG-2004 ahanda 115.91 3814488 Added populate_emp_hours_by_rate
92 to archive all elements returned
93 by pay_hours_by_rate view.
94 05-AUG-2004 ahanda 115.90 3814488 Added logic for Hours By Rate
95 20-JUL-2004 ahanda 115.89 3780771 Changed order by in the action
96 creation code
97 23-JUN-2004 ahanda 115.88 3711280 Changed the logic for NR/R
98 Also, balance call for Medicare ER
99 and SS ER Liability
100 14-MAY-2004 rsethupa 115.87 3231253 Added Comments
101 13-MAY-2004 rsethupa 115.86 3231253 Added code to archive STEIC Advance
102 balance in the categories
103 'AC DEDUCTIONS' and 'US STATE'
104 30-APR-2004 saurgupt 115.85 3561821 Modified the procedure
105 update_ytd_withheld
106 to check for current values for
107 the balances along with the YTD
108 values before archiving.
109 16-APR-2004 rsethupa 115.84 3311866 US SS Payslip currency Format Enh.
110 Changed code to archive currency
111 data in canonical format for the
112 action info categories 'AC
113 DEDUCTIONS'and 'US WITHHOLDINGS'.
114 12-MAR-2004 rsethupa 115.83 3452149 Modified procedures process_actions
115 and action_archive_data to
116 archive Employee and W4 Information
117 for primary assignment in case
118 multiple assignments flag is checked
119 and any assignment other than the
120 primary is paid.
121 16-JAN-2004 kvsankar 115.82 3349855 Modified query for performance
122 enhancement
123 02-JAN-2004 ahanda 115.81 Changed cursor c_get_baladj_jd
124 to pick up distinct jurisdictions
125 04-DEC-2003 vpandya 115.80 Added logic to call
126 first_time_process
127 even though the Ext Proc Arch
128 has already been run for the
129 current year before.
130 25-NOV-2003 vpandya 115.79 3280589 Changed action_archive_data:
131 populating gv_multi_payroll_pymt.
132 Changed condition for Termi Asg.
133 Removed create_child_actions proc.
134 17-Nov-2003 vpandya 115.78 3262057 Changed populate_puv_tax_balances:
135 Added condition in the cursor
136 c_get_jd in the second select
137 clause.
138 07-Nov-2003 vpandya 115.77 3243503 Changed action_archive_data:
139 selecting assignment_action_id
140 in c_payment_info cursor to
141 get master pp asg act id again in
142 ln_asg_action_id variable.
143 06-Nov-2003 vpandya 115.76 3231337 Changed populate_puv_tax_balances:
144 Removed cursors c_get_sp_rr_jd and
145 c_get_rr_jd. Added cursor c_get_jd.
146 Added function check_tax_exists.
147 05-Nov-2003 vpandya 115.75 3237538 Changed action_archive_data:
148 added cursor c_all_runs.
149 31-Oct-2003 vpandya 115.74 3225286 Changed process_actions
150 Added cursor c_chk_act_type to check
151 if previous archiver is of balance
152 adjustement then call first_time..
153 procedure instead of calling
154 get_current_elements.
155 08-Oct-2003 ahanda 115.73 3181365 Changed -
156 populate_federal_tax_balances
157 and update_ytd_withheld
158 18-Sep-2003 vpandya 115.72 Changed range cursor to fix gscc
159 error on date conversion. Using
160 fnd_date.date_to_canonical instead
161 to_char and canonical_to_date
162 instead of to_date.
163 10-Sep-2003 ekim 115.71 3119792 Added check for whether the archiver
164 2880047 is run for a given payroll
165 in action_archive_data procedure.
166 This sets variable g_xfr_run_exists.
167 Added call to
168 process_additional_elements
169 Added procedure
170 change_processing_priority
171 and called this procedure before
172 insert_rows_thro_api_process.
173 Terminated Assignment check:
174 c_get_term_asg
175 06-Aug-2003 vpandya 115.70 3050620 Changed action_action_creation to
176 create assignment action for
177 zero net pay using view
178 pay_payment_information.
179 28-Jul-2003 vpandya 115.69 3053917 Passing parameter
180 p_ytd_balcall_aaid to
181 get_personal_information.
182 13-Jun-2003 ekim 115.68 3005678 Removed call update_federal_values
183 in between if..else statements
184 in populate_federal_tax_balances
185 and left one call at the end of the
186 procedure.
187 02-Apr-2003 ekim 115.67 Removed ppa.action_status='C'
188 for all queries as paa.action_status
189 ='C' is the only required.
190 28-Mar-2003 ekim 115.66 2875350 Made performance fix on
191 c_get_rr_jd, c_get_sp_rr_jd,
192 c_get_baladj_jd
193 - Added parameter
194 cp_run_effective_date in cursor.
195 2874412 Changed c_get_employee_info to
196 add pre_name_adjunt and suffix.
197 18-Mar-2003 ekim 115.65 2855261 Changed default processing
198 priority in update_ytd_withheld
199 to 10.
200 Changed processing priority
201 for all Tax Deductions.
202 06-Feb-2003 ekim 115.64 2315822 Added additional parameter
203 p_sepchk_flag,p_assignment_id
204 in get_xfr_elements procedure call.
205 31-Jan-2003 ekim 115.63 2752134 Added YTD balance to be archived
206 for EIC Advance
207 02-DEC-2002 ahanda 115.62 Changed package to fix GSCC warnings
208 25-NOV-2002 ahanda 115.61 2658611 Changed update_employee_information
209 to pass assignment_id.
210 19-NOV-2002 vpandya 115.60 Calling set_error_message function
211 of pay_emp_action_arch from all
212 exceptions to get error message
213 Remote Procedure Calls(RPC or Sub
214 program)
215 17-NOV-2002 ahanda 115.59 Added function get_balance_value
216 Balance call done only if def bal id
217 is not null.
218 01-NOV-2002 ahanda 115.58 Changed error handling.
219 14-OCT-2002 ahanda 115.57 2500413 Changed calls to update_ytd_withheld
220 to populate processing_prioirty for
221 Tax Deductions
222 2500381 Changed Code to update Employee Name
223 2562608 Changed range and action creation
224 cursor to pick up reversals.
225 15-OCT-2002 tmehra 115.56 Added code to archive the PQP
226 (Alien) balances.
227 14-OCT-2002 ahanda 115.55 Changed update_ytd_withheld to
228 populate processing priority.
229 23-SEP-2002 ahanda 115.54 2498029 Changed populate_school_tax_balances
230 2532436 and populate_state_tax_balances
231 06-SEP-2002 ahanda 115.53 Fixed GSCC Warnings.
232 17-JUN-2002 ahanda 115.52 2447717 Changed package to populate tax
233 2365908 deductions if location has changed.
234 14-MAY-2002 ahanda 115.51 Moved procedures
235 - get_last_xfr_info
236 - get_last_pymt_info
237 to pay_ac_action_arch
238 24-APR-2002 ahanda 115.50 Changed c_get_rr_jd for performance.
239 18-MAR-2002 ahanda 115.49 2204512 Changed the way we populate NR/R.
240 Fixed archiving for Bal Adj for
241 which Pre Pay flag is checked.
242 18-FEB-2002 ahanda 115.48 2200748 Changed W4 to archive the Work and
243 Resident JDs. Changed Adj Bal proc
244 to pass the bal adj action_id.
245 14-FEB-2002 ahanda 115.47 2189810 Changed c_time_period to get the
246 time_period_id from per_time_periods
247 14-FEB-2002 ahanda 115.46 Changed archinit to check for the
248 new dimension only if multi asgn
249 is enabled. This removed dependency
250 on HRGLOBAL for one off patch.
251 11-FEB-2002 ahanda 115.45 Changed fetch for cursor
252 c_get_states_jit in archinit
253 05-FEB-2002 ahanda 115.44 Changed package for Bal Adjustments.
254 26-JAN-2002 ahanda 115.43 Added dbdrv commands.
255 22-JAN-2002 ahanda 115.42 Changed package to take care
256 of Multi Assignment Processing.
257 ****************************************************************************
258 25-JAN-2001 asasthan 115.0 Created.
259
260 ******************************************************************************/
261
262 /******************************************************************************
263 ** Package Local Variables
264 ******************************************************************************/
265 gv_package VARCHAR2(100) := 'pay_us_action_arch';
266 gn_gross_earn_def_bal_id number := 0;
267 gn_payments_def_bal_id number := 0;
268
269 /***************************************************************************
270 Name : change_processing_priority
271 Purpose : Reset the processing priority from the element processing
272 priority to the archiver processing priority for Tax Deductions
273 **************************************************************************/
274 PROCEDURE change_processing_priority IS
275 BEGIN
276 IF pay_ac_action_arch.lrr_act_tab.count > 0 THEN
277 for i in pay_ac_action_arch.lrr_act_tab.first ..
278 pay_ac_action_arch.lrr_act_tab.last loop
279 if pay_ac_action_arch.lrr_act_tab(i).action_info_category
280 = 'AC DEDUCTIONS' then
281 if pay_ac_action_arch.lrr_act_tab(i).act_info10
282 = 'FIT Withheld' then
283 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '1';
284 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
285 = 'SS EE Withheld' then
286 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '2';
287 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
288 = 'SS Withheld' then
289 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '2';
290 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
291 = 'Medicare EE Withheld' then
292 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '3';
293 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
294 = 'EIC Advance' then
295 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '4';
296 if pay_ac_action_arch.lrr_act_tab(i).act_info9 > 0 then
297 pay_ac_action_arch.lrr_act_tab(i).act_info9 :=
298 (pay_ac_action_arch.lrr_act_tab(i).act_info9 * -1);
299 end if;
300 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
301 = 'SIT Withheld' then
302 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '5';
303 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
304 = 'County Withheld' then
305 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '6';
306 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
307 = 'SDI Withheld' then
308 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
309 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
310 = 'SDI EE Withheld' then
311 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
312 pay_ac_action_arch.lrr_act_tab(i).act_info10 := 'SDI Withheld';
313 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
314 = 'Non W2 FIT Withheld' then
315 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
316 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
317 = 'SUI Withheld' then
318 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
319 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
320 = 'SUI EE Withheld' then
321 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
322 pay_ac_action_arch.lrr_act_tab(i).act_info10 := 'SUI Withheld';
323 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
324 = 'WC Withheld' then
325 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
326 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
327 = 'Workers Comp Withheld' then
328 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
329 pay_ac_action_arch.lrr_act_tab(i).act_info10 := 'WC Withheld';
330 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
331 = 'WC2 Withheld' then
332 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
333 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
334 = 'Workers Comp2 Withheld' then
335 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
336 pay_ac_action_arch.lrr_act_tab(i).act_info10 := 'WC2 Withheld';
337 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
338 = 'STEIC Advance' then
339 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10'; /*Bug 3231253*/
340 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
341 = 'Head Tax Withheld' then
342 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
343 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
344 = 'City Withheld' then
345 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
346 elsif pay_ac_action_arch.lrr_act_tab(i).act_info10
347 = 'School Withheld' then
348 pay_ac_action_arch.lrr_act_tab(i).act_info7 := '10';
349 end if;
350 end if;
351 end loop;
352 end if;
353 END change_processing_priority;
354
355 /******************************************************************************
356 Name : get_payroll_action_info
357 Purpose : This returns the Payroll Action level
358 information for Tax Filing (FLS)/Payslip Archiver.
359 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
360 p_start_date - Start date of Archiver
361 p_end_date - End date of Archiver
362 p_business_group_id - Business Group ID
363 p_cons_set_id - Consolidation Set when submitting Archiver
364 p_payroll_id - Payroll ID when submitting Archiver
365 ******************************************************************************/
366 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
367 ,p_end_date out nocopy date
368 ,p_start_date out nocopy date
369 ,p_business_group_id out nocopy number
370 ,p_cons_set_id out nocopy number
371 ,p_payroll_id out nocopy number
372 )
373 IS
374 cursor c_payroll_Action_info
375 (cp_payroll_action_id in number) is
376 select effective_date,
377 start_date,
378 business_group_id,
379 to_number(substr(legislative_parameters,
380 instr(legislative_parameters,
381 'TRANSFER_CONSOLIDATION_SET_ID=')
382 + length('TRANSFER_CONSOLIDATION_SET_ID='))),
383 to_number(ltrim(rtrim(substr(legislative_parameters,
384 instr(legislative_parameters,
385 'TRANSFER_PAYROLL_ID=')
386 + length('TRANSFER_PAYROLL_ID='),
387 (instr(legislative_parameters,
388 'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
389 - (instr(legislative_parameters,
390 'TRANSFER_PAYROLL_ID=')
391 + length('TRANSFER_PAYROLL_ID='))))))
392 from pay_payroll_actions
393 where payroll_action_id = cp_payroll_action_id;
394
395 ld_end_date DATE;
396 ld_start_date DATE;
397 ln_business_group_id NUMBER;
398 ln_cons_set_id NUMBER;
399 ln_payroll_id NUMBER;
400 lv_procedure_name VARCHAR2(100) := '.get_payroll_action_info';
401
402 lv_error_message VARCHAR2(200);
403 ln_step NUMBER;
404
405 BEGIN
406 hr_utility.set_location(gv_package || lv_procedure_name, 10);
407 ln_step := 1;
408 open c_payroll_action_info(p_payroll_action_id);
409 fetch c_payroll_action_info into ld_end_date,
410 ld_start_date,
411 ln_business_group_id,
412 ln_cons_set_id,
413 ln_payroll_id;
414 close c_payroll_action_info;
415
416 hr_utility.set_location(gv_package || lv_procedure_name, 30);
417 p_end_date := ld_end_date;
418 p_start_date := ld_start_date;
419 p_business_group_id := ln_business_group_id;
420 p_cons_set_id := ln_cons_set_id;
421 p_payroll_id := ln_payroll_id;
422 hr_utility.set_location(gv_package || lv_procedure_name, 50);
423 ln_step := 2;
424
425 EXCEPTION
426 when others then
427 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
428 gv_package || lv_procedure_name;
429
430 hr_utility.trace(lv_error_message || '-' || sqlerrm);
431
432 lv_error_message :=
433 pay_emp_action_arch.set_error_message(lv_error_message);
434
435 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
436 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
437 hr_utility.raise_error;
438
439 END get_payroll_action_info;
440
441
442 /*********************************************************************
443 Name : get_defined_balance_id
444 Purpose : This function returns the defined_balance_id for a given
445 Balance Name and Dimension.
446 The function is used to get the defined_balance_id
447 of the Balance Names stored in the Action Information DF
448 flexfield for US Federal, State, County, City and School
449 Districts.
450 Arguments :
451 Notes :
452 *********************************************************************/
453 FUNCTION get_defined_balance_id(
454 p_business_group_id in number
455 ,p_balance_name in varchar2
456 ,p_balance_dimension in varchar2)
457 RETURN NUMBER
458 IS
459
460 cursor c_get_defined_balance_id (
461 cp_business_group_id in number,
462 cp_balance_name in varchar2,
463 cp_balance_dimension in varchar2 ) is
464 select pdb.defined_balance_id
465 from pay_defined_balances pdb,
466 pay_balance_dimensions pbd,
467 pay_balance_types pbt
468 where pbt.balance_name = cp_balance_name
469 and pbd.database_item_suffix= cp_balance_dimension
470 and pbt.balance_type_id = pdb.balance_type_id
471 and pbd.balance_dimension_id = pdb.balance_dimension_id
472 and ((pbt.legislation_code = 'US' and
473 pbt.business_group_id is null)
474 or (pbt.legislation_code is null and
475 pbt.business_group_id = cp_business_group_id))
476 and ((pdb.legislation_code ='US' and
477 pdb.business_group_id is null)
478 or (pdb.legislation_code is null and
479 pdb.business_group_id = cp_business_group_id));
480
481 ln_defined_balance_id NUMBER;
482
483 BEGIN
484 hr_utility.trace('opened c_get_defined_balance');
485 open c_get_defined_balance_id(p_business_group_id,
486 p_balance_name,
487 p_balance_dimension);
488
489 fetch c_get_defined_balance_id into ln_defined_balance_id;
490 if c_get_defined_balance_id%notfound then
491 hr_utility.trace('Defined balance Id not found');
492 -- Do not error out if the defined_balance_id does not exist
493 -- Pass Null instead.
494 end if;
495 close c_get_defined_balance_id;
496 hr_utility.trace('ln_defined_balance_id = ' ||
497 to_char(ln_defined_balance_id));
498
499 return (ln_defined_balance_id);
500
501 END get_defined_balance_id;
502
503
504 /******************************************************************
505 Name : action_range_cursor
506 Purpose : This returns the select statement that is
507 used to created the range rows for the
508 Tax Filing (FLS)/Payslip Archiver.
509 Arguments :
510 Notes : Calls procedure - get_payroll_action_info
511 ******************************************************************/
512 PROCEDURE action_range_cursor(
513 p_payroll_action_id in number
514 ,p_sqlstr out nocopy varchar2)
515 IS
516
517 ld_end_date DATE;
518 ld_start_date DATE;
519 ln_business_group_id NUMBER;
520 ln_cons_set_id NUMBER;
521 ln_payroll_id NUMBER;
522
523 lv_sql_string VARCHAR2(32000);
524 lv_procedure_name VARCHAR2(100) := '.action_range_cursor';
525
526 BEGIN
527 hr_utility.set_location(gv_package || lv_procedure_name, 10);
528 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
529 ,p_start_date => ld_start_date
530 ,p_end_date => ld_end_date
531 ,p_business_group_id => ln_business_group_id
532 ,p_cons_set_id => ln_cons_set_id
533 ,p_payroll_id => ln_payroll_id);
534 hr_utility.set_location(gv_package || lv_procedure_name, 20);
535
536 lv_sql_string :=
537 'select distinct paf.person_id
538 from pay_assignment_actions paa,
539 pay_payroll_actions ppa,
540 per_assignments_f paf
541 where ppa.business_group_id = ''' || ln_business_group_id || '''
542 and ppa.effective_date between fnd_date.canonical_to_date(''' ||
543 fnd_date.date_to_canonical(ld_start_date) || ''')
544 and fnd_date.canonical_to_date(''' ||
545 fnd_date.date_to_canonical(ld_end_date) || ''')
546 and ppa.action_type in (''U'',''P'',''B'',''V'')
547 and decode(ppa.action_type,
548 ''B'', nvl(ppa.future_process_mode, ''Y''),
549 ''N'') = ''N''
550 and ppa.consolidation_set_id = ''' || ln_cons_set_id || '''
551 and ppa.payroll_id = ''' || ln_payroll_id || '''
552 and ppa.payroll_action_id = paa.payroll_action_id
553 and paa.action_status = ''C''
554 and paa.source_action_id is null
555 and paf.assignment_id = paa.assignment_id
556 and ppa.effective_date between paf.effective_start_date
557 and paf.effective_end_date
558 and not exists
559 (select /*+ ORDERED */
560 1
561 from pay_action_interlocks pai,
562 pay_assignment_actions paa1,
563 pay_payroll_actions ppa1
564 where pai.locked_action_id = paa.assignment_action_id
565 and paa1.assignment_action_id = pai.locking_action_id
566 and ppa1.payroll_action_id = paa1.payroll_action_id
567 and ppa1.action_type =''X''
568 and ppa1.report_type = ''XFR_INTERFACE'')
569 and :payroll_action_id is not null
570 order by paf.person_id';
571
572 hr_utility.set_location(gv_package || lv_procedure_name, 30);
573 p_sqlstr := lv_sql_string;
574 hr_utility.set_location(gv_package || lv_procedure_name, 50);
575
576 END action_range_cursor;
577
578
579 /************************************************************
580 Name : action_action_creation
581 Purpose : This creates the assignment actions for
582 a specific chunk of people to be archived
583 by the Archiver process.
584 Arguments :
585 Notes : Calls procedure - get_payroll_action_info
586 ************************************************************/
587 PROCEDURE action_action_creation(
588 p_payroll_action_id in number
589 ,p_start_person_id in number
590 ,p_end_person_id in number
591 ,p_chunk in number)
592 IS
593
594 cursor c_get_xfr_emp( cp_start_person_id in number
595 ,cp_end_person_id in number
596 ,cp_cons_set_id in number
597 ,cp_payroll_id in number
598 ,cp_business_group_id in number
599 ,cp_start_date in date
600 ,cp_end_date in date
601 ) is
602 select /*+ INDEX(PAF PER_ASSIGNMENTS_F_N12)
603 INDEX(PPA PAY_PAYROLL_ACTIONS_N50)
604 INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) */
605 paa.assignment_id,
606 paa.tax_unit_id,
607 ppa.effective_date,
608 ppa.date_earned,
609 ppa.action_type,
610 paa.assignment_action_id,
611 paa.payroll_action_id
612 from pay_payroll_actions ppa,
613 pay_assignment_actions paa,
614 per_assignments_f paf
615 where paf.person_id between cp_start_person_id
616 and cp_end_person_id
617 and paa.assignment_id = paf.assignment_id
618 and ppa.effective_date between paf.effective_start_date
619 and paf.effective_end_date
620 and ppa.consolidation_set_id = cp_cons_set_id
621 and paa.action_status = 'C'
622 and ppa.payroll_id = cp_payroll_id
623 and ppa.payroll_action_id = paa.payroll_action_id
624 and ppa.business_group_id = cp_business_group_id
625 and ppa.effective_date between cp_start_date
626 and cp_end_date
627 and ppa.action_type in ('U','P','B','V')
628 and decode(ppa.action_type,
629 'B', nvl(ppa.future_process_mode, 'Y'),
630 'N') = 'N'
631 and paa.source_action_id is null
632 and not exists
633 (select 'x'
634 from pay_action_interlocks pai1,
635 pay_assignment_actions paa1,
636 pay_payroll_actions ppa1
637 where pai1.locked_action_id = paa.assignment_action_id
638 and paa1.assignment_action_id = pai1.locking_action_id
639 and ppa1.payroll_action_id = paa1.payroll_action_id
640 and ppa1.action_type ='X'
641 and ppa1.report_type = 'XFR_INTERFACE')
642 order by 1,2,3,5,6;
643
644 cursor c_get_xfr_range_emp(
645 cp_payroll_action_id in number
646 ,cp_chunk_number in number
647 ,cp_cons_set_id in number
648 ,cp_payroll_id in number
649 ,cp_business_group_id in number
650 ,cp_start_date in date
651 ,cp_end_date in date
652 ) is
653 select /*+ INDEX(PPR PAY_POPULATION_RANGES_N4)
654 INDEX(PAF PER_ASSIGNMENTS_F_N12)
655 INDEX(PPA PAY_PAYROLL_ACTIONS_N50)
656 INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) */
657 paa.assignment_id,
658 paa.tax_unit_id,
659 ppa.effective_date,
660 ppa.date_earned,
661 ppa.action_type,
662 paa.assignment_action_id,
663 paa.payroll_action_id
664 from pay_payroll_actions ppa,
665 pay_assignment_actions paa,
666 per_assignments_f paf,
667 pay_population_ranges ppr
668 where ppr.payroll_action_id = cp_payroll_action_id
669 and ppr.chunk_number = cp_chunk_number
670 and paf.person_id = ppr.person_id
671 and ppa.effective_date between paf.effective_start_date
672 and paf.effective_end_date
673 and paa.assignment_id = paf.assignment_id
674 and ppa.consolidation_set_id = cp_cons_set_id
675 and paa.action_status = 'C'
676 and ppa.payroll_id = cp_payroll_id
677 and ppa.payroll_action_id = paa.payroll_action_id
678 and ppa.business_group_id = cp_business_group_id
679 and ppa.effective_date between cp_start_date
680 and cp_end_date
681 and ppa.action_type in ('U','P','B','V')
682 and decode(ppa.action_type,
683 'B', nvl(ppa.future_process_mode, 'Y'),
684 'N') = 'N'
685 and paa.source_action_id is null
686 and not exists
687 (select 'x'
688 from pay_action_interlocks pai1,
689 pay_assignment_actions paa1,
690 pay_payroll_actions ppa1
691 where pai1.locked_action_id = paa.assignment_action_id
692 and paa1.assignment_action_id = pai1.locking_action_id
693 and ppa1.payroll_action_id = paa1.payroll_action_id
694 and ppa1.action_type ='X'
695 and ppa1.report_type = 'XFR_INTERFACE')
696 order by 1,2,3,5,6;
697
698 cursor c_master_action(cp_prepayment_action_id number) is
699 select max(paa.assignment_action_id)
700 from pay_payroll_actions ppa,
701 pay_assignment_actions paa,
702 pay_action_interlocks pai
703 where pai.locking_action_Id = cp_prepayment_action_id
704 and paa.assignment_action_id = pai.locked_action_id
705 and paa.source_action_id is null
706 and ppa.payroll_action_id = paa.payroll_action_id
707 and ppa.action_type in ('R', 'Q');
708
709 ln_assignment_id NUMBER := 0;
710 ln_tax_unit_id NUMBER := 0;
711 ld_effective_date DATE := to_date('1900/12/31','YYYY/MM/DD');
712 ld_date_earned DATE;
713 lv_action_type VARCHAR2(10);
714 ln_asg_action_id NUMBER := 0;
715 ln_payroll_action_id NUMBER := 0;
716
717 ln_master_action_id NUMBER := 0;
718
719 ld_end_date DATE;
720 ld_start_date DATE;
721 ln_business_group_id NUMBER;
722 ln_cons_set_id NUMBER;
723 ln_payroll_id NUMBER;
724
725 ln_prev_asg_action_id NUMBER := 0;
726 ln_prev_assignment_id NUMBER := 0;
727 ln_prev_tax_unit_id NUMBER := 0;
728 ld_prev_effective_date DATE := to_date('1800/12/31','YYYY/MM/DD');
729
730 ln_xfr_action_id NUMBER;
731
732 lv_serial_number VARCHAR2(30);
733 lv_procedure_name VARCHAR2(100) := '.action_action_creation';
734 lv_error_message VARCHAR2(200);
735 ln_step NUMBER;
736
737 lb_range_person BOOLEAN;
738
739 begin
740 ln_step := 1;
741 pay_emp_action_arch.gv_error_message := NULL;
742 hr_utility.set_location(gv_package || lv_procedure_name, 10);
743
744 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
745 ,p_start_date => ld_start_date
746 ,p_end_date => ld_end_date
747 ,p_business_group_id => ln_business_group_id
748 ,p_cons_set_id => ln_cons_set_id
749 ,p_payroll_id => ln_payroll_id);
750 hr_utility.set_location(gv_package || lv_procedure_name, 20);
751
752 lb_range_person := pay_ac_utility.range_person_on(
753 p_report_type => 'XFR_INTERFACE'
754 ,p_report_format => 'TAXARCH'
755 ,p_report_qualifier => 'FED'
756 ,p_report_category => 'RT');
757
758 ln_step := 2;
759 if lb_range_person then
760 open c_get_xfr_range_emp(p_payroll_action_id
761 ,p_chunk
762 ,ln_cons_set_id
763 ,ln_payroll_id
764 ,ln_business_group_id
765 ,ld_start_date
766 ,ld_end_date);
767 else
768 open c_get_xfr_emp( p_start_person_id
769 ,p_end_person_id
770 ,ln_cons_set_id
771 ,ln_payroll_id
772 ,ln_business_group_id
773 ,ld_start_date
774 ,ld_end_date);
775 end if;
776
777 -- Loop for all rows returned for SQL statement.
778 hr_utility.set_location(gv_package || lv_procedure_name, 30);
779 loop
780 if lb_range_person then
781 fetch c_get_xfr_range_emp into ln_assignment_id,
782 ln_tax_unit_id,
783 ld_effective_date,
784 ld_date_earned,
785 lv_action_type,
786 ln_asg_action_id,
787 ln_payroll_action_id;
788 exit when c_get_xfr_range_emp%notfound;
789 else
790
791 fetch c_get_xfr_emp into ln_assignment_id,
792 ln_tax_unit_id,
793 ld_effective_date,
794 ld_date_earned,
795 lv_action_type,
796 ln_asg_action_id,
797 ln_payroll_action_id;
798
799 exit when c_get_xfr_emp%notfound;
800 end if;
801
802 hr_utility.set_location(gv_package || lv_procedure_name, 40);
803 hr_utility.trace('ln_assignment_id = ' ||
804 to_char(ln_assignment_id));
805
806 /********************************************************
807 ** If Balance Adjustment, only create one assignment
808 ** action record. As there could be multiple assignment
809 ** actions for Balance Adjustment, we lock all the
810 ** balance adj record.
811 ** First time the else portion will be executed which
812 ** creates the assignment action. If the Assignment ID,
813 ** Tax Unit ID and Effective Date is same and Action
814 ** Type is Balance Adj only lock the record
815 ********************************************************/
816 if ln_assignment_id = ln_prev_assignment_id and
817 ln_tax_unit_id = ln_prev_tax_unit_id and
818 ld_effective_date = ld_prev_effective_date and
819 lv_action_type = 'B' and
820 ln_asg_action_id <> ln_prev_asg_action_id then
821
822 hr_utility.set_location(gv_package || lv_procedure_name, 50);
823 hr_utility.trace('Locking Action = ' || ln_xfr_action_id);
824 hr_utility.trace('Locked Action = ' || ln_asg_action_id);
825 hr_nonrun_asact.insint(ln_xfr_action_id
826 ,ln_asg_action_id);
827 else
828 hr_utility.set_location(gv_package || lv_procedure_name, 60);
829 hr_utility.trace('Action_type = '||lv_action_type );
830
831 select pay_assignment_actions_s.nextval
832 into ln_xfr_action_id
833 from dual;
834
835 -- insert into pay_assignment_actions.
836 hr_nonrun_asact.insact(ln_xfr_action_id,
837 ln_assignment_id,
838 p_payroll_action_id,
839 p_chunk,
840 ln_tax_unit_id,
841 null,
842 'U',
843 null);
844 hr_utility.set_location(gv_package || lv_procedure_name, 70);
845 hr_utility.trace('ln_asg_action_id = ' || ln_asg_action_id);
846 hr_utility.trace('ln_xfr_action_id = ' || ln_xfr_action_id);
847 hr_utility.trace('p_payroll_action_id = ' || p_payroll_action_id);
848 hr_utility.trace('ln_tax_unit_id = ' || ln_tax_unit_id);
849 hr_utility.set_location(gv_package || lv_procedure_name, 80);
850
851 -- insert an interlock to this action
852 hr_utility.trace('Locking Action = ' || ln_xfr_action_id);
853 hr_utility.trace('Locked Action = ' || ln_asg_action_id);
854 hr_nonrun_asact.insint(ln_xfr_action_id,
855 ln_asg_action_id);
856
857 hr_utility.set_location(gv_package || lv_procedure_name, 90);
858
859 /********************************************************
860 ** For Balance Adj we put only the first assignment action
861 ********************************************************/
862 lv_serial_number := lv_action_type || 'N' ||
863 ln_asg_action_id;
864
865 update pay_assignment_actions
866 set serial_number = lv_serial_number
867 where assignment_action_id = ln_xfr_action_id;
868
869 hr_utility.set_location(gv_package || lv_procedure_name, 100);
870
871 end if ; --ln_assignment_id ...
872
873 ln_prev_tax_unit_id := ln_tax_unit_id;
874 ld_prev_effective_date := ld_effective_date;
875 ln_prev_assignment_id := ln_assignment_id;
876 ln_prev_asg_action_id := ln_asg_action_id;
877
878 end loop;
879 if lb_range_person then
880 close c_get_xfr_range_emp;
881 else
882 close c_get_xfr_emp;
883 end if;
884
885 ln_step := 5;
886
887 EXCEPTION
888 when others then
889 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
890 gv_package || lv_procedure_name;
891
892 hr_utility.trace(lv_error_message || '-' || sqlerrm);
893
894 lv_error_message :=
895 pay_emp_action_arch.set_error_message(lv_error_message);
896
897 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
898 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
899 hr_utility.raise_error;
900
901 END action_action_creation;
902
903
904 /************************************************************
905 Name : action_archinit
906 Purpose : This performs the context initialization.
907 Arguments :
908 Notes :
909 ************************************************************/
910 PROCEDURE action_archinit(
911 p_payroll_action_id in number) is
912
913 lv_state_code VARCHAR2(2);
914 lv_sit_exists VARCHAR2(1);
915 lv_sdi_ee_exists VARCHAR2(1);
916 lv_sdi_er_exists VARCHAR2(1);
917 lv_sui_ee_exists VARCHAR2(1);
918 lv_sui_er_exists VARCHAR2(1);
919 ln_index NUMBER;
920
921 lv_jurisdiction_code VARCHAR2(11);
922 lv_county_tax_exists VARCHAR2(1);
923 lv_county_sd_tax_exists VARCHAR2(1);
924 lv_county_head_tax_exists VARCHAR2(1);
925
926 ln_fed_count NUMBER := 0;
927 ln_state_count NUMBER := 0;
928 ln_county_count NUMBER := 0;
929 ln_city_count NUMBER := 0;
930 ln_schdist_count NUMBER := 0;
931
932 lv_balance_name VARCHAR2(80);
933 ln_balance_type_id NUMBER;
934 lv_balance_dimension VARCHAR2(80);
935
936 ld_effective_date DATE;
937
938 lv_pymt_dimension VARCHAR2(50);
939 lv_jd_pymt_dimension VARCHAR2(50);
940 lv_subj_pymt_dimension VARCHAR2(50);
941
942 ld_end_date DATE;
943 ld_start_date DATE;
944 ln_business_group_id NUMBER;
945 ln_cons_set_id NUMBER;
946 ln_payroll_id NUMBER;
947
948 lv_error_message VARCHAR2(500);
949 lv_procedure_name VARCHAR2(100) := '.action_archinit';
950 ln_step NUMBER;
951
952 cursor c_asg_actions (cp_payroll_action_id in number) is
953 select ppa.effective_date
954 from pay_payroll_actions ppa
955 where ppa.payroll_action_id = cp_payroll_action_id;
956
957 cursor c_get_balance_type_id (cp_balance_name in varchar2) is
958 select balance_type_id
959 from pay_balance_types
960 where balance_name = cp_balance_name
961 and legislation_code = 'US';
962
963 cursor c_get_balances (cp_action_context in varchar2) is
964 select fdu.form_left_prompt, pbt.balance_type_id
965 from fnd_descr_flex_col_usage_tl fdu,
966 pay_balance_types pbt,
967 fnd_application fa
968 where fdu.descriptive_flexfield_name = 'Action Information DF'
969 and fdu.language = 'US'
970 and pbt.balance_name = fdu.form_left_prompt
971 and pbt.legislation_code = 'US'
972 and fdu.descriptive_flex_context_code = cp_action_context
973 and fdu.form_left_prompt <> 'Resident/Non-Resident Flag'
974 and fdu.form_left_prompt <> 'Resident Jurisdiction'
975 and fa.application_id = fdu.application_id
976 and fa.application_short_name = 'PAY'
977 order by fdu.descriptive_flex_context_code, fdu.form_left_prompt;
978
979 cursor c_get_states_jit (cp_effective_date in date) is
980 select state_code,
981 sit_exists,
982 decode(sui_ee_wage_limit, null, 'N', 'Y'),
983 decode(sui_er_wage_limit, null, 'N', 'Y'),
984 decode(sdi_ee_wage_limit, null, 'N', 'Y'),
985 decode(sdi_er_wage_limit, null, 'N', 'Y')
986 from pay_us_state_tax_info_f
987 where cp_effective_date between effective_start_date
988 and effective_end_date
989 and sta_information_category = 'State tax limit rate info'
990 order by 1 ;
991
992 cursor c_get_county_jit (cp_effective_date in date) is
993 select jurisdiction_code,
994 county_tax,
995 head_tax,
996 school_tax
997 from pay_us_county_tax_info_f
998 where cp_effective_date between effective_start_date
999 and effective_end_date
1000 and cnty_information_category = 'County tax status info'
1001 order by 1 ;
1002
1003 cursor c_get_act_param is
1004 select parameter_value
1005 from pay_action_parameters
1006 where parameter_name = 'INIT_PAY_ARCHIVE';
1007
1008 BEGIN
1009 ln_step := 1;
1010 hr_utility.set_location(gv_package || lv_procedure_name, 1);
1011 pay_emp_action_arch.gv_error_message := NULL;
1012
1013 hr_utility.set_location(gv_package || lv_procedure_name, 2);
1014 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1015 ,p_start_date => ld_start_date
1016 ,p_end_date => ld_end_date
1017 ,p_business_group_id => ln_business_group_id
1018 ,p_cons_set_id => ln_cons_set_id
1019 ,p_payroll_id => ln_payroll_id);
1020 hr_utility.set_location(gv_package || lv_procedure_name, 3);
1021
1022 /*********************************************************************
1023 ** This cursor is used to call first_time_process from process_actions
1024 ** whenever it is set to either Y or end date of External Process
1025 ** Archive in YYYY/MM/DD format (canonical format).
1026 ** In other words, by setting this, it will behave like external
1027 ** process archiver is being run first time.
1028 *********************************************************************/
1029
1030 gv_act_param_val := NULL;
1031
1032 open c_get_act_param;
1033 fetch c_get_act_param into gv_act_param_val;
1034 close c_get_act_param;
1035
1036 ln_step := 2;
1037
1038 if pay_emp_action_arch.gv_multi_leg_rule is null then
1039 pay_emp_action_arch.gv_multi_leg_rule
1040 := pay_emp_action_arch.get_multi_legislative_rule('US');
1041 end if;
1042
1043 if pay_emp_action_arch.gv_multi_leg_rule = 'Y' then
1044 lv_pymt_dimension := '_ASG_PAYMENTS';
1045 lv_jd_pymt_dimension := '_ASG_PAYMENTS_JD';
1046 lv_subj_pymt_dimension := '_SUBJECT_TO_TAX_ASG_PAYMENTS';
1047 else
1048 lv_pymt_dimension := '_PAYMENTS';
1049 lv_jd_pymt_dimension := '_PAYMENTS_JD';
1050 lv_subj_pymt_dimension := '_SUBJECT_TO_TAX_PAYMENTS';
1051 end if;
1052 hr_utility.trace('pay_emp_action_arch.gv_multi_leg_rule = ' ||
1053 pay_emp_action_arch.gv_multi_leg_rule);
1054 hr_utility.trace('lv_pymt_dimension = ' || lv_pymt_dimension);
1055 hr_utility.trace('lv_jd_pymt_dimension = ' || lv_jd_pymt_dimension);
1056 hr_utility.trace('lv_subj_pymt_dimension = ' || lv_subj_pymt_dimension);
1057
1058 ln_step := 5;
1059 open c_asg_actions(p_payroll_action_id);
1060 fetch c_asg_actions into ld_effective_date;
1061 if c_asg_actions%notfound then
1062 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1063 lv_error_message := 'No Assignment Actions were picked by ' ||
1064 'External Archive Process.';
1065
1066 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1067 hr_utility.set_message_token('FORMULA_TEXT',lv_error_message);
1068 --hr_utility.raise_error;
1069 end if;
1070 close c_asg_actions;
1071
1072 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1073
1074 /* Get Federal Balances */
1075 ln_step := 10;
1076 open c_get_balances('US FEDERAL');
1077 loop
1078 fetch c_get_balances into lv_balance_name, ln_balance_type_id;
1079 if c_get_balances%NOTFOUND then
1080 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1081 exit;
1082 end if;
1083 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1084 hr_utility.trace('lv_balance_name = ' || lv_balance_name);
1085
1086 ln_fed_count := ln_fed_count + 1;
1087 hr_utility.trace('ln_fed_count = '||ln_fed_count);
1088
1089 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).action_info_category
1090 := 'US FEDERAL';
1091 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).balance_name
1092 := lv_balance_name;
1093 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).balance_type_id
1094 := ln_balance_type_id;
1095
1096 if lv_balance_name in ('Supplemental Earnings for FIT',
1097 'Supplemental Earnings for NWFIT',
1098 'Pre Tax Deductions for FIT',
1099 'Supplemental Earnings for SS',
1100 'Pre Tax Deductions for SS',
1101 'Supplemental Earnings for Medicare',
1102 'Pre Tax Deductions for Medicare',
1103 'Supplemental Earnings for FUTA',
1104 'Pre Tax Deductions for FUTA',
1105 'Supplemental Earnings for EIC',
1106 'Pre Tax Deductions for EIC') then
1107
1108 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1109 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).payment_def_bal_id
1110 := get_defined_balance_id(ln_business_group_id,
1111 lv_balance_name,
1112 lv_subj_pymt_dimension);
1113
1114 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).asg_run_def_bal_id
1115 := get_defined_balance_id(ln_business_group_id,
1116 lv_balance_name,
1117 '_SUBJECT_TO_TAX_ASG_GRE_RUN');
1118
1119 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).ytd_def_bal_id
1120 := get_defined_balance_id(ln_business_group_id,
1121 lv_balance_name,
1122 '_SUBJECT_TO_TAX_ASG_GRE_YTD');
1123
1124 else
1125 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1126 hr_utility.trace('lv_pymt_dimension = '||lv_pymt_dimension);
1127 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).payment_def_bal_id
1128 := get_defined_balance_id(ln_business_group_id,
1129 lv_balance_name,
1130 lv_pymt_dimension);
1131
1132 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).asg_run_def_bal_id
1133 := get_defined_balance_id(ln_business_group_id,
1134 lv_balance_name,
1135 '_ASG_GRE_RUN');
1136
1137 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).ytd_def_bal_id
1138 := get_defined_balance_id(ln_business_group_id,
1139 lv_balance_name,
1140 '_ASG_GRE_YTD');
1141
1142 end if;
1143 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1144 end loop;
1145
1146 close c_get_balances;
1147
1148
1149 /****************************************************************
1150 Public Sector Payroll changes. The following code has
1151 been added to display and archive the FIT Alien Balances
1152 *****************************************************************/
1153 ln_step := 15;
1154 hr_utility.set_location(gv_package || lv_procedure_name, 75);
1155
1156 ln_fed_count := ln_fed_count + 1;
1157
1158 for c_rec in c_get_balance_type_id ('Non W2 FIT Withheld')
1159 loop
1160 ln_balance_type_id := c_rec.balance_type_id;
1161 end loop;
1162
1163 hr_utility.trace('ln_fed_count = ' || to_char(ln_fed_count));
1164 hr_utility.trace('ln_blance_type_id = ' || to_char(ln_balance_type_id));
1165
1166 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).action_info_category
1167 := 'US FEDERAL';
1168 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).balance_name
1169 := 'Non W2 FIT Withheld';
1170 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).balance_type_id
1171 := ln_balance_type_id;
1172
1173 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).payment_def_bal_id
1174 := get_defined_balance_id(ln_business_group_id,
1175 'Non W2 FIT Withheld',
1176 lv_pymt_dimension);
1177
1178 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).asg_run_def_bal_id
1179 := get_defined_balance_id(ln_business_group_id,
1180 'Non W2 FIT Withheld',
1181 '_ASG_GRE_RUN');
1182
1183 pay_us_action_arch.ltr_fed_tax_bal(ln_fed_count).ytd_def_bal_id
1184 := get_defined_balance_id(ln_business_group_id,
1185 'Non W2 FIT Withheld',
1186 '_ASG_GRE_YTD');
1187
1188 /********************************************************
1189 ** Getting Defined Balance IDs for
1190 ** Gross Earnings (which is used for all normal earning
1191 ** elements) and Payments for Non Payroll Payment element.
1192 ** In archive_data, the value for an assignment action
1193 ** with these defined balances are non zero then call
1194 ** process action.
1195 ***********************************************************/
1196 gn_gross_earn_def_bal_id := nvl(get_defined_balance_id(
1197 ln_business_group_id,
1198 'Gross Earnings',
1199 '_ASG_RUN'),-1);
1200 gn_payments_def_bal_id := nvl(get_defined_balance_id(
1201 ln_business_group_id,
1202 'Payments',
1203 '_ASG_RUN'),-1);
1204
1205
1206 /****************************************************************
1207 End Public Sector Payroll changes.
1208 *****************************************************************/
1209
1210 hr_utility.set_location(gv_package || lv_procedure_name, 80);
1211
1212
1213 /* Get State Balances */
1214 ln_step := 20;
1215 open c_get_balances('US STATE');
1216 loop
1217 fetch c_get_balances into lv_balance_name, ln_balance_type_id;
1218 if c_get_balances%NOTFOUND then
1219 hr_utility.set_location(gv_package || lv_procedure_name, 90);
1220 exit;
1221 end if;
1222 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1223 hr_utility.trace('lv_balance_name is '||lv_balance_name);
1224
1225 ln_state_count := ln_state_count + 1;
1226
1227 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).action_info_category
1228 := 'US STATE';
1229 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).balance_name
1230 := lv_balance_name;
1231 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).balance_type_id
1232 := ln_balance_type_id;
1233
1234 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).payment_def_bal_id
1235 := get_defined_balance_id(ln_business_group_id,
1236 lv_balance_name,
1237 lv_jd_pymt_dimension);
1238
1239 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).asg_run_def_bal_id
1240 := get_defined_balance_id(ln_business_group_id,
1241 lv_balance_name,
1242 '_ASG_JD_GRE_RUN');
1243
1244 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).ytd_def_bal_id
1245 := get_defined_balance_id(ln_business_group_id,
1246 lv_balance_name,
1247 '_ASG_JD_GRE_YTD');
1248
1249 hr_utility.set_location(gv_package || lv_procedure_name, 110);
1250 end loop;
1251 close c_get_balances;
1252
1253 hr_utility.set_location(gv_package || lv_procedure_name, 120);
1254
1255 /****************************************************************
1256 Public Sector Payroll changes. The following code has
1257 been added to display and archive the SIT Alien Balances
1258 *****************************************************************/
1259 ln_step := 25;
1260 hr_utility.set_location(gv_package || lv_procedure_name, 125);
1261
1262 ln_state_count := ln_state_count + 1;
1263
1264 for c_rec in c_get_balance_type_id ('SIT Alien Withheld')
1265 loop
1266 ln_balance_type_id := c_rec.balance_type_id;
1267 end loop;
1268
1269 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).action_info_category
1270 := 'US STATE';
1271 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).balance_name
1272 := 'SIT Alien Withheld';
1273 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).balance_type_id
1274 := ln_balance_type_id;
1275
1276 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).payment_def_bal_id
1277 := get_defined_balance_id(ln_business_group_id,
1278 'SIT Alien Withheld',
1279 lv_jd_pymt_dimension);
1280
1281 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).asg_run_def_bal_id
1282 := get_defined_balance_id(ln_business_group_id,
1283 'SIT Alien Withheld',
1284 '_ASG_JD_GRE_RUN');
1285
1286 pay_us_action_arch.ltr_state_tax_bal(ln_state_count).ytd_def_bal_id
1287 := get_defined_balance_id(ln_business_group_id,
1288 'SIT Alien Withheld',
1289 '_ASG_JD_GRE_YTD');
1290
1291 /****************************************************************
1292 End Public Sector Payroll changes.
1293 *****************************************************************/
1294 hr_utility.set_location(gv_package || lv_procedure_name, 126);
1295
1296
1297 /* Get County Balances */
1298 ln_step := 30;
1299 open c_get_balances('US COUNTY');
1300 loop
1301 fetch c_get_balances into lv_balance_name, ln_balance_type_id;
1302 if c_get_balances%NOTFOUND then
1303 hr_utility.set_location(gv_package || lv_procedure_name, 130);
1304 exit;
1305 end if;
1306 hr_utility.set_location(gv_package || lv_procedure_name, 140);
1307 hr_utility.trace('lv_balance_name is '||lv_balance_name);
1308
1309 ln_county_count := ln_county_count + 1;
1310
1311 pay_us_action_arch.ltr_county_tax_bal(ln_county_count).action_info_category
1312 := 'US COUNTY';
1313 pay_us_action_arch.ltr_county_tax_bal(ln_county_count).balance_name
1314 := lv_balance_name;
1315 pay_us_action_arch.ltr_county_tax_bal(ln_county_count).balance_type_id
1316 := ln_balance_type_id;
1317
1318 pay_us_action_arch.ltr_county_tax_bal(ln_county_count).payment_def_bal_id
1319 := get_defined_balance_id(ln_business_group_id,
1320 lv_balance_name,
1321 lv_jd_pymt_dimension);
1322 pay_us_action_arch.ltr_county_tax_bal(ln_county_count).asg_run_def_bal_id
1323 := get_defined_balance_id(ln_business_group_id,
1324 lv_balance_name,
1325 '_ASG_JD_GRE_RUN');
1326 pay_us_action_arch.ltr_county_tax_bal(ln_county_count).ytd_def_bal_id
1327 := get_defined_balance_id(ln_business_group_id,
1328 lv_balance_name,
1329 '_ASG_JD_GRE_YTD');
1330
1331 hr_utility.set_location(gv_package || lv_procedure_name, 150);
1332 end loop;
1333 close c_get_balances;
1334 --
1335 hr_utility.trace('Entering County Loop ' );
1336 ln_step := 35;
1337 for i in pay_us_action_arch.ltr_county_tax_bal.first ..
1338 pay_us_action_arch.ltr_county_tax_bal.last loop
1339
1340 hr_utility.trace('Count = ' || to_char(i));
1341 hr_utility.trace('Category = ' ||
1342 pay_us_action_arch.ltr_county_tax_bal(i).action_info_category);
1343 hr_utility.trace('Balance Name = ' ||
1344 pay_us_action_arch.ltr_county_tax_bal(i).balance_name);
1345 hr_utility.trace('pay_def_bal_id ' ||
1346 pay_us_action_arch.ltr_county_tax_bal(i).payment_def_bal_id);
1347 hr_utility.trace('asg_run_def_bal_id = ' ||
1348 pay_us_action_arch.ltr_county_tax_bal(i).asg_run_def_bal_id);
1349 hr_utility.trace('ytd_def_bal_id = ' ||
1350 pay_us_action_arch.ltr_county_tax_bal(i).ytd_def_bal_id);
1351 end loop;
1352 hr_utility.trace('Leaving County Loop ' );
1353 --
1354
1355 hr_utility.set_location(gv_package || lv_procedure_name, 160);
1356 /* Get City Balances */
1357 ln_step := 40;
1358 open c_get_balances('US CITY');
1359 loop
1360 fetch c_get_balances into lv_balance_name, ln_balance_type_id;
1361 if c_get_balances%notfound then
1362 hr_utility.set_location(gv_package || lv_procedure_name, 170);
1363 exit;
1364 end if;
1365 hr_utility.set_location(gv_package || lv_procedure_name, 180);
1366 hr_utility.trace('lv_balance_name is '||lv_balance_name);
1367
1368 ln_city_count := ln_city_count + 1;
1369
1370 pay_us_action_arch.ltr_city_tax_bal(ln_city_count).action_info_category
1371 := 'US CITY';
1372 pay_us_action_arch.ltr_city_tax_bal(ln_city_count).balance_name
1373 := lv_balance_name;
1374 pay_us_action_arch.ltr_city_tax_bal(ln_city_count).balance_type_id
1375 := ln_balance_type_id;
1376
1377 pay_us_action_arch.ltr_city_tax_bal(ln_city_count).payment_def_bal_id
1378 := get_defined_balance_id(ln_business_group_id,
1379 lv_balance_name,
1380 lv_jd_pymt_dimension);
1381 pay_us_action_arch.ltr_city_tax_bal(ln_city_count).asg_run_def_bal_id
1382 := get_defined_balance_id(ln_business_group_id,
1383 lv_balance_name,
1384 '_ASG_JD_GRE_RUN');
1385 pay_us_action_arch.ltr_city_tax_bal(ln_city_count).ytd_def_bal_id
1386 := get_defined_balance_id(ln_business_group_id,
1387 lv_balance_name,
1388 '_ASG_JD_GRE_YTD');
1389
1390 hr_utility.set_location(gv_package || lv_procedure_name, 190);
1391 end loop;
1392 close c_get_balances;
1393
1394 hr_utility.set_location(gv_package || lv_procedure_name, 200);
1395 /* Get School District Balances */
1396 ln_step := 45;
1397 open c_get_balances('US SCHOOL DISTRICT');
1398 loop
1399 fetch c_get_balances into lv_balance_name, ln_balance_type_id;
1400 if c_get_balances%notfound then
1401 hr_utility.set_location(gv_package || lv_procedure_name, 210);
1402 exit;
1403 end if;
1404 hr_utility.set_location(gv_package || lv_procedure_name, 220);
1405 hr_utility.trace('lv_balance_name is '||lv_balance_name);
1406
1407 ln_schdist_count := ln_schdist_count + 1;
1408
1409 pay_us_action_arch.ltr_schdist_tax_bal(ln_schdist_count).action_info_category
1410 := 'US SCHOOL DISTRICT';
1411 pay_us_action_arch.ltr_schdist_tax_bal(ln_schdist_count).balance_name
1412 := lv_balance_name;
1413 pay_us_action_arch.ltr_schdist_tax_bal(ln_schdist_count).balance_type_id
1414 := ln_balance_type_id;
1415
1416 pay_us_action_arch.ltr_schdist_tax_bal(ln_schdist_count).payment_def_bal_id
1417 := get_defined_balance_id(ln_business_group_id,
1418 lv_balance_name,
1419 lv_jd_pymt_dimension);
1420 pay_us_action_arch.ltr_schdist_tax_bal(ln_schdist_count).asg_run_def_bal_id
1421 := get_defined_balance_id(ln_business_group_id,
1422 lv_balance_name,
1423 '_ASG_JD_GRE_RUN');
1424 pay_us_action_arch.ltr_schdist_tax_bal(ln_schdist_count).ytd_def_bal_id
1425 := get_defined_balance_id(ln_business_group_id,
1426 lv_balance_name,
1427 '_ASG_JD_GRE_YTD');
1428
1429 hr_utility.set_location(gv_package || lv_procedure_name, 230);
1430 end loop;
1431 close c_get_balances;
1432
1433 hr_utility.set_location(gv_package || lv_procedure_name, 240);
1434 hr_utility.trace('Fed Balance Loop Count = ' ||
1435 pay_us_action_arch.ltr_fed_tax_bal.count);
1436 hr_utility.trace('State Balance Loop Count = ' ||
1437 pay_us_action_arch.ltr_state_tax_bal.count);
1438 hr_utility.trace('County Balance Loop Count = ' ||
1439 pay_us_action_arch.ltr_county_tax_bal.count);
1440 hr_utility.trace('City Balance Loop Count = ' ||
1441 pay_us_action_arch.ltr_city_tax_bal.count);
1442 hr_utility.trace('School Dsts Balance Loop Count = ' ||
1443 pay_us_action_arch.ltr_schdist_tax_bal.count);
1444 hr_utility.set_location(gv_package || lv_procedure_name, 250);
1445
1446
1447 /****************************************************
1448 ** Build a PL/SQL table which has state tax info
1449 ** for all states
1450 ****************************************************/
1451 hr_utility.set_location(gv_package || lv_procedure_name, 300);
1452 ln_step := 50;
1453 open c_get_states_jit(ld_effective_date);
1454 loop
1455 fetch c_get_states_jit into lv_state_code, lv_sit_exists,
1456 lv_sui_ee_exists, lv_sui_er_exists,
1457 lv_sdi_ee_exists, lv_sdi_er_exists;
1458 if c_get_states_jit%notfound then
1459 hr_utility.set_location(gv_package || lv_procedure_name, 310);
1460 exit;
1461 end if;
1462 hr_utility.set_location(gv_package || lv_procedure_name, 320);
1463 hr_utility.trace('lv_state_code = ' || lv_state_code);
1464 hr_utility.trace('lv_sit_exists = ' || lv_sit_exists);
1465 hr_utility.trace('lv_sui_ee_exists = ' || lv_sui_ee_exists);
1466 hr_utility.trace('lv_sui_er_exists = ' || lv_sui_er_exists);
1467 hr_utility.trace('lv_sdi_ee_exists = ' || lv_sdi_ee_exists);
1468 hr_utility.trace('lv_sdi_er_exists = ' || lv_sdi_er_exists);
1469
1470 pay_us_action_arch.ltr_state_tax_info(lv_state_code).sit_exists
1471 := lv_sit_exists;
1472 pay_us_action_arch.ltr_state_tax_info(lv_state_code).sui_ee_exists
1473 := lv_sui_ee_exists;
1474 pay_us_action_arch.ltr_state_tax_info(lv_state_code).sui_er_exists
1475 := lv_sui_er_exists;
1476 pay_us_action_arch.ltr_state_tax_info(lv_state_code).sdi_ee_exists
1477 := lv_sdi_ee_exists;
1478 pay_us_action_arch.ltr_state_tax_info(lv_state_code).sdi_er_exists
1479 := lv_sdi_er_exists;
1480
1481 end loop;
1482 close c_get_states_jit;
1483
1484 hr_utility.set_location(gv_package || lv_procedure_name, 350);
1485 ln_step := 55;
1486 open c_get_county_jit(ld_effective_date);
1487 loop
1488 fetch c_get_county_jit into lv_jurisdiction_code,
1489 lv_county_tax_exists,
1490 lv_county_sd_tax_exists,
1491 lv_county_head_tax_exists;
1492 if c_get_county_jit%notfound then
1493 hr_utility.set_location(gv_package || lv_procedure_name, 360);
1494 exit;
1495 end if;
1496 hr_utility.set_location(gv_package || lv_procedure_name, 370);
1497 hr_utility.trace('lv_jurisdiction_code = ' || lv_jurisdiction_code);
1498
1499 ln_index := pay_us_action_arch.ltr_county_tax_info.count;
1500
1501 pay_us_action_arch.ltr_county_tax_info(ln_index).jurisdiction_code
1502 := lv_jurisdiction_code;
1503 pay_us_action_arch.ltr_county_tax_info(ln_index).cnty_tax_exists
1504 := lv_county_tax_exists;
1505 pay_us_action_arch.ltr_county_tax_info(ln_index).cnty_head_tax_exists
1506 := lv_county_sd_tax_exists;
1507 pay_us_action_arch.ltr_county_tax_info(ln_index).cnty_sd_tax_exists
1508 := lv_county_head_tax_exists;
1509
1510 end loop;
1511 close c_get_county_jit;
1512
1513 hr_utility.set_location(gv_package || lv_procedure_name, 400);
1514 ln_step := 60;
1515
1516 exception
1517 when others then
1518 hr_utility.set_location(gv_package || lv_procedure_name, 500);
1519 lv_error_message := 'Error at step ' || ln_step ||
1520 ' in ' || gv_package || lv_procedure_name;
1521 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1522
1523 lv_error_message :=
1524 pay_emp_action_arch.set_error_message(lv_error_message);
1525
1526 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1527 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1528 hr_utility.raise_error;
1529
1530 END action_archinit;
1531
1532
1533 /******************************************************************
1534 Name : populate_emp_hours_by_rate
1535 Purpose : The procedure set the Federal level balance value
1536 in the PL/SQL table.
1537 Arguments :
1538 Notes :
1539 ******************************************************************/
1540 PROCEDURE populate_emp_hours_by_rate(p_action_context_id in number
1541 ,p_assignment_id in number
1542 ,p_run_action_id in number)
1543 IS
1544
1545 cursor c_hbr(cp_run_action_id in number) is
1546 select hours.element_type_id,
1547 hours.element_name,
1548 hours.processing_priority,
1549 hours.rate,
1550 nvl(hours.multiple,1),
1551 hours.hours,
1552 hours.amount
1553 from pay_hours_by_rate_v hours
1554 where hours.assignment_action_id = cp_run_action_id
1555 and legislation_code = 'US'
1556 and hours.element_type_id >= 0 -- Bug 3370112
1557 order by hours.processing_priority,hours.element_type_id;
1558
1559 cursor c_retro(cp_run_action_id in number
1560 ,cp_element_type_id in number) is
1561 select pepd.element_entry_id,
1562 sum(decode(piv.name, 'Pay Value', prrv.result_value)),
1563 sum(decode(piv.name, 'Hours', prrv.result_value)),
1564 nvl(sum(decode(piv.name, 'Multiple', prrv.result_value)),1),
1565 sum(decode(piv.name, 'Rate', prrv.result_value))
1566 from pay_run_results prr,
1567 pay_run_result_values prrv,
1568 pay_input_values_f piv,
1569 pay_entry_process_details pepd
1570 where piv.input_value_id = prrv.input_value_id
1571 and prr.element_type_id = cp_element_type_id
1572 and prr.run_result_id = prrv.run_result_id
1573 and prr.assignment_action_id = cp_run_action_id
1574 and prr.source_type = 'E'
1575 and pepd.element_entry_id = prr.source_id
1576 and pepd.source_asg_action_id is not null
1577 and result_value is not null
1578 group by pepd.element_entry_id;
1579
1580 ln_element_type_id NUMBER;
1581 lv_element_name VARCHAR2(100);
1582 lv_processing_priority VARCHAR2(10);
1583 ln_rate NUMBER(15,5);
1584 ln_multiple NUMBER(15,5);
1585 ln_hours NUMBER(15,5);
1586 ln_amount NUMBER(15,5);
1587 ln_index NUMBER;
1588
1589 lv_procedure_name VARCHAR2(100);
1590 lv_error_message VARCHAR2(200);
1591
1592 ln_hrs_index NUMBER;
1593 ltr_hours_x_rate pay_ac_action_arch.hbr_table;
1594
1595 ln_retro_rate NUMBER(15,5);
1596 ln_retro_multiple NUMBER(15,5);
1597 ln_retro_hours NUMBER(15,5);
1598 ln_retro_payvalue NUMBER(15,5);
1599 ln_retro_element_entry NUMBER;
1600
1601 BEGIN
1602 --hr_utility.trace_on(null, 'HBR');
1603 lv_procedure_name := '.populate_emp_hours_by_rate';
1604 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1605 ln_rate := 0;
1606 ln_hours := 0;
1607 ln_multiple := 1;
1608
1609 open c_hbr(p_run_action_id);
1610 loop
1611 fetch c_hbr into ln_element_type_id
1612 ,lv_element_name
1613 ,lv_processing_priority
1614 ,ln_rate
1615 ,ln_multiple
1616 ,ln_hours
1617 ,ln_amount;
1618 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1619 if c_hbr%notfound then
1620 hr_utility.set_location(gv_package || lv_procedure_name, 25);
1621 exit;
1622 end if;
1623
1624 if c_hbr%found then
1625 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1626 ln_index := pay_ac_action_arch.lrr_act_tab.count;
1627 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
1628 := 'EMPLOYEE HOURS BY RATE';
1629 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1630 := '00-000-0000';
1631 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
1632 := p_action_context_id;
1633 pay_ac_action_arch.lrr_act_tab(ln_index).assignment_id
1634 := p_assignment_id;
1635 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
1636 := ln_element_type_id;
1637 pay_ac_action_arch.lrr_act_tab(ln_index).act_info4
1638 := lv_element_name;
1639 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5
1640 := fnd_number.number_to_canonical(ln_rate);
1641 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
1642 := fnd_number.number_to_canonical(ln_hours);
1643 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
1644 := lv_processing_priority;
1645 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
1646 := fnd_number.number_to_canonical(ln_multiple);
1647 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1648 := fnd_number.number_to_canonical(ln_amount);
1649
1650 /******************************************************
1651 ** Insert into seperate table
1652 ******************************************************/
1653 ln_hrs_index := ltr_hours_x_rate.count;
1654 if ltr_hours_x_rate.count > 0 then
1655 for z in ltr_hours_x_rate.first .. ltr_hours_x_rate.last loop
1656 if ltr_hours_x_rate(z).element_type_id
1657 = ln_element_type_id then
1658 ln_hrs_index := z;
1659 exit;
1660 end if;
1661 end loop;
1662 end if;
1663
1664 ltr_hours_x_rate(ln_hrs_index).element_type_id := ln_element_type_id;
1665 ltr_hours_x_rate(ln_hrs_index).element_name := lv_element_name;
1666 ltr_hours_x_rate(ln_hrs_index).processing_priority := lv_processing_priority;
1667 ltr_hours_x_rate(ln_hrs_index).rate := ln_rate;
1668 ltr_hours_x_rate(ln_hrs_index).hours
1669 := nvl(ltr_hours_x_rate(ln_hrs_index).hours,0) + ln_hours;
1670 ltr_hours_x_rate(ln_hrs_index).amount
1671 := nvl(ltr_hours_x_rate(ln_hrs_index).amount,0) +
1672 ln_amount;
1673
1674 end if;
1675 end loop;
1676 close c_hbr;
1677
1678 if ltr_hours_x_rate.count > 0 then
1679 for z in ltr_hours_x_rate.first .. ltr_hours_x_rate.last loop
1680 hr_utility.trace('*******Element in Hours By Rate *************');
1681 hr_utility.trace('HBR element name = ' || ltr_hours_x_rate(z).element_name);
1682 hr_utility.trace('HBR element hour = ' || ltr_hours_x_rate(z).hours);
1683 hr_utility.trace('HBR element rate = ' || ltr_hours_x_rate(z).rate);
1684 hr_utility.trace('HBR element payvalue = ' || ltr_hours_x_rate(z).amount);
1685 end loop;
1686 end if;
1687
1688 if ltr_hours_x_rate.count > 0 then
1689 for z in ltr_hours_x_rate.first .. ltr_hours_x_rate.last loop
1690 if pay_ac_action_arch.lrr_act_tab.count > 0 then
1691 for i in pay_ac_action_arch.lrr_act_tab.first..
1692 pay_ac_action_arch.lrr_act_tab.last loop
1693 if pay_ac_action_arch.lrr_act_tab(i).action_info_category
1694 = 'AC EARNINGS' and
1695 pay_ac_action_arch.lrr_act_tab(i).action_context_id
1696 = p_action_context_id and
1697 pay_ac_action_arch.lrr_act_tab(i).act_info2
1698 = ltr_hours_x_rate(z).element_type_id then
1699 if ((ltr_hours_x_rate(z).hours <>
1700 pay_ac_action_arch.lrr_act_tab(i).act_info11) OR
1701 (ltr_hours_x_rate(z).amount <>
1702 pay_ac_action_arch.lrr_act_tab(i).act_info8)) then
1703 --call function to get the retro data
1704 hr_utility.trace('HBR diff ' || ltr_hours_x_rate(z).element_name ||
1705 ' Element ID=' || ltr_hours_x_rate(z).element_type_id ||
1706 ' AC HBR=' ||
1707 pay_ac_action_arch.lrr_act_tab(i).act_info11 ||
1708 ' HBR Hours=' || ltr_hours_x_rate(z).hours ||
1709 ' p_run_action_id='||p_run_action_id);
1710 open c_retro(p_run_action_id, ltr_hours_x_rate(z).element_type_id);
1711 loop
1712 hr_utility.set_location(gv_package || lv_procedure_name, 57);
1713 fetch c_retro into ln_retro_element_entry
1714 ,ln_retro_payvalue
1715 ,ln_retro_hours
1716 ,ln_retro_multiple
1717 ,ln_retro_rate;
1718 if c_retro%notfound then
1719 exit;
1720 end if;
1721
1722 hr_utility.trace('HBR Retro Values');
1723 hr_utility.trace('Pay Value='|| ln_retro_payvalue);
1724 hr_utility.trace('Hours ='|| ln_retro_hours);
1725 hr_utility.trace('Rate ='|| ln_retro_rate);
1726 hr_utility.trace('Multiple ='|| ln_retro_multiple);
1727 if ln_retro_multiple = 0 then
1728 ln_retro_multiple := 1;
1729 end if;
1730
1731 ln_index := pay_ac_action_arch.lrr_act_tab.count;
1732 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
1733 := 'EMPLOYEE HOURS BY RATE';
1734 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1735 := '00-000-0000';
1736 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
1737 := p_action_context_id;
1738 pay_ac_action_arch.lrr_act_tab(ln_index).assignment_id
1739 := p_assignment_id;
1740 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
1741 := ltr_hours_x_rate(z).element_type_id;
1742 pay_ac_action_arch.lrr_act_tab(ln_index).act_info4
1743 := ltr_hours_x_rate(z).element_name;
1744 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5
1745 := fnd_number.number_to_canonical(ln_retro_rate);
1746 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
1747 := fnd_number.number_to_canonical(ln_retro_hours);
1748 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
1749 := ltr_hours_x_rate(z).processing_priority;
1750 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
1751 := fnd_number.number_to_canonical(nvl(ln_retro_multiple,1));
1752 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1753 := fnd_number.number_to_canonical(ln_retro_payvalue);
1754 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1755 end loop;
1756 close c_retro;
1757 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1758 end if;
1759 exit;
1760 end if;
1761 end loop;
1762 end if;
1763 end loop;
1764 end if;
1765
1766 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1767 --hr_utility.trace_off;
1768
1769 EXCEPTION
1770 when others then
1771 lv_error_message := 'Error in ' ||
1772 gv_package || lv_procedure_name;
1773
1774 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1775
1776 lv_error_message :=
1777 pay_emp_action_arch.set_error_message(lv_error_message);
1778
1779 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1780 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1781 hr_utility.raise_error;
1782
1783 END populate_emp_hours_by_rate;
1784
1785
1786 /**************************************************************
1787 Name : update_federal_values
1788 Purpose : The procedure set the Federal level balance value
1789 in the PL/SQL table.
1790 Arguments :
1791 Notes :
1792 **************************************************************/
1793 PROCEDURE update_federal_values(p_balance in varchar2
1794 ,p_bal_value in number
1795 ,p_index in number
1796 ,p_category in varchar2
1797 )
1798 IS
1799 lv_procedure_name VARCHAR2(100) := '.update_federal_values';
1800 lv_error_message VARCHAR2(200);
1801
1802 BEGIN
1803 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1804 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
1805 hr_utility.trace('p_balance = '|| p_balance);
1806 hr_utility.trace('ln_bal_value = ' || p_bal_value);
1807
1808 pay_ac_action_arch.lrr_act_tab(p_index).action_info_category
1809 := p_category;
1810 pay_ac_action_arch.lrr_act_tab(p_index).jurisdiction_code
1811 := '00-000-0000';
1812
1813 if p_balance = 'FIT Withheld' then
1814 pay_ac_action_arch.lrr_act_tab(p_index).act_info1 :=
1815 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info1,0)
1816 + p_bal_value ;
1817 elsif p_balance = 'Regular Earnings' then
1818 pay_ac_action_arch.lrr_act_tab(p_index).act_info2 :=
1819 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info2,0)
1820 + p_bal_value;
1821 elsif p_balance = 'Supplemental Earnings for FIT' then
1822 pay_ac_action_arch.lrr_act_tab(p_index).act_info3 :=
1823 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info3,0)
1824 + p_bal_value;
1825 elsif p_balance = 'Supplemental Earnings for NWFIT' then
1826 pay_ac_action_arch.lrr_act_tab(p_index).act_info4 :=
1827 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info4,0)
1828 + p_bal_value;
1829 elsif p_balance = 'Pre Tax Deductions' then
1830 pay_ac_action_arch.lrr_act_tab(p_index).act_info5 :=
1831 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info5,0)
1832 + p_bal_value;
1833 elsif p_balance = 'Pre Tax Deductions for FIT' then
1834 pay_ac_action_arch.lrr_act_tab(p_index).act_info6 :=
1835 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info6,0)
1836 + p_bal_value;
1837 elsif p_balance = 'SS EE Taxable' then
1838 pay_ac_action_arch.lrr_act_tab(p_index).act_info7 :=
1839 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info7,0)
1840 + p_bal_value;
1841 elsif p_balance = 'SS EE Withheld' then
1842 pay_ac_action_arch.lrr_act_tab(p_index).act_info8 :=
1843 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info8,0)
1844 + p_bal_value;
1845 elsif p_balance = 'SS ER Liability' then
1846 pay_ac_action_arch.lrr_act_tab(p_index).act_info9 :=
1847 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info9,0)
1848 + p_bal_value ;
1849 elsif p_balance = 'SS ER Taxable' then
1850 pay_ac_action_arch.lrr_act_tab(p_index).act_info10 :=
1851 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info10,0)
1852 + p_bal_value ;
1853 elsif p_balance = 'Supplemental Earnings for SS' then
1854 pay_ac_action_arch.lrr_act_tab(p_index).act_info11 :=
1855 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info11,0)
1856 + p_bal_value;
1857 elsif p_balance = 'Pre Tax Deductions for SS' then
1858 pay_ac_action_arch.lrr_act_tab(p_index).act_info12 :=
1859 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info12,0)
1860 + p_bal_value;
1861 elsif p_balance = 'Medicare EE Taxable' then
1862 pay_ac_action_arch.lrr_act_tab(p_index).act_info13 :=
1863 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info13,0)
1864 + p_bal_value;
1865 elsif p_balance = 'Medicare EE Withheld' then
1866 pay_ac_action_arch.lrr_act_tab(p_index).act_info14 :=
1867 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info14,0)
1868 + p_bal_value;
1869 elsif p_balance = 'Medicare ER Liability' then
1870 pay_ac_action_arch.lrr_act_tab(p_index).act_info15 :=
1871 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info15,0)
1872 + p_bal_value ;
1873 elsif p_balance = 'Medicare ER Taxable' then
1874 pay_ac_action_arch.lrr_act_tab(p_index).act_info16 :=
1875 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info16,0)
1876 + p_bal_value;
1877 elsif p_balance = 'Supplemental Earnings for Medicare' then
1878 pay_ac_action_arch.lrr_act_tab(p_index).act_info17 :=
1879 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info17,0)
1880 + p_bal_value;
1881 elsif p_balance = 'Pre Tax Deductions for Medicare' then
1882 pay_ac_action_arch.lrr_act_tab(p_index).act_info18 :=
1883 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info18,0)
1884 + p_bal_value;
1885 elsif p_balance = 'Supplemental Earnings for FUTA' then
1886 pay_ac_action_arch.lrr_act_tab(p_index).act_info19 :=
1887 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info19,0)
1888 + p_bal_value;
1889 elsif p_balance = 'Pre Tax Deductions for FUTA' then
1890 pay_ac_action_arch.lrr_act_tab(p_index).act_info20 :=
1891 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info20,0)
1892 + p_bal_value;
1893 elsif p_balance = 'FUTA Taxable' then
1894 pay_ac_action_arch.lrr_act_tab(p_index).act_info21 :=
1895 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info21,0)
1896 + p_bal_value;
1897 elsif p_balance = 'FUTA Liability' then
1898 pay_ac_action_arch.lrr_act_tab(p_index).act_info22 :=
1899 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info22,0)
1900 + p_bal_value;
1901 elsif p_balance = 'Gross Earnings' then
1902 pay_ac_action_arch.lrr_act_tab(p_index).act_info23 :=
1903 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info23,0)
1904 + p_bal_value;
1905 elsif p_balance = 'Pre Tax Deductions for EIC' then
1906 pay_ac_action_arch.lrr_act_tab(p_index).act_info24 :=
1907 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info24,0)
1908 + p_bal_value;
1909 elsif p_balance = 'Supplemental Earnings for EIC' then
1910 pay_ac_action_arch.lrr_act_tab(p_index).act_info25 :=
1911 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info25,0)
1912 + p_bal_value;
1913 elsif p_balance = 'EIC Advance' then
1914 pay_ac_action_arch.lrr_act_tab(p_index).act_info26 :=
1915 nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info26,0)
1916 + p_bal_value;
1917 end if;
1918
1919 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1920
1921 EXCEPTION
1922 when others then
1923 lv_error_message := 'Error in ' ||
1924 gv_package || lv_procedure_name;
1925
1926 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1927
1928 lv_error_message :=
1929 pay_emp_action_arch.set_error_message(lv_error_message);
1930
1931 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1932 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1933 hr_utility.raise_error;
1934
1935 END update_federal_values;
1936
1937
1938 /**************************************************************
1939 Name : update_sit_values
1940 Purpose : The procedure set the SIT level balance value
1941 in the PL/SQL table.
1942 Arguments :
1943 Notes :
1944 **************************************************************/
1945 PROCEDURE update_sit_values(p_balance in varchar2
1946 ,p_bal_value in number
1947 ,p_index in number
1948 )
1949 IS
1950 lv_procedure_name VARCHAR2(100) := '.update_sit_values';
1951 lv_error_message VARCHAR2(200);
1952
1953 BEGIN
1954 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1955 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
1956 hr_utility.trace('p_balance = '|| p_balance);
1957 hr_utility.trace('p_bal_value = ' || p_bal_value);
1958
1959 if p_balance = 'SIT Withheld' then
1960 pay_ac_action_arch.lrr_act_tab(p_index).act_info1
1961 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info1,0) +
1962 p_bal_value ;
1963 elsif p_balance = 'SIT Subj Whable' then
1964 pay_ac_action_arch.lrr_act_tab(p_index).act_info2
1965 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info2,0) +
1966 p_bal_value;
1967 elsif p_balance = 'SIT Subj NWhable' then
1968 pay_ac_action_arch.lrr_act_tab(p_index).act_info3
1969 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info3,0) +
1970 p_bal_value;
1971 elsif p_balance = 'SIT Pre Tax Redns' then
1972 pay_ac_action_arch.lrr_act_tab(p_index).act_info4
1973 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info4,0) +
1974 p_bal_value;
1975 elsif p_balance = 'SIT Gross' then
1976 pay_ac_action_arch.lrr_act_tab(p_index).act_info17
1977 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info17,0) +
1978 p_bal_value ;
1979 end if;
1980
1981 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1982
1983 EXCEPTION
1984 when others then
1985 lv_error_message := 'Error in ' ||
1986 gv_package || lv_procedure_name;
1987
1988 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1989
1990 lv_error_message :=
1991 pay_emp_action_arch.set_error_message(lv_error_message);
1992
1993 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1994 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1995 hr_utility.raise_error;
1996
1997 END update_sit_values;
1998
1999
2000 /**************************************************************
2001 Name : update_sdi_ee_values
2002 Purpose : The procedure set the SDI EE level balance value
2003 in the PL/SQL table.
2004 Arguments :
2005 Notes :
2006 **************************************************************/
2007 PROCEDURE update_sdi_ee_values(p_balance in varchar2
2008 ,p_bal_value in number
2009 ,p_index in number
2010 )
2011 IS
2012 lv_procedure_name VARCHAR2(100) := '.update_sdi_ee_values';
2013 lv_error_message VARCHAR2(200);
2014
2015 BEGIN
2016 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2017 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2018 hr_utility.trace('p_balance = '|| p_balance);
2019 hr_utility.trace('p_bal_value = ' || p_bal_value);
2020
2021 if p_balance = 'SDI EE Withheld' then
2022 pay_ac_action_arch.lrr_act_tab(p_index).act_info5
2023 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info5,0) +
2024 p_bal_value ;
2025 elsif p_balance = 'SDI EE Taxable' then
2026 pay_ac_action_arch.lrr_act_tab(p_index).act_info6
2027 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info6,0) +
2028 p_bal_value;
2029 elsif p_balance = 'SDI EE Subj Whable' then
2030 pay_ac_action_arch.lrr_act_tab(p_index).act_info7
2031 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info7,0) +
2032 p_bal_value;
2033 elsif p_balance = 'SDI EE Pre Tax Redns' then
2034 pay_ac_action_arch.lrr_act_tab(p_index).act_info8
2035 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info8,0) +
2036 p_bal_value;
2037 elsif p_balance = 'SDI EE Gross' then
2038 pay_ac_action_arch.lrr_act_tab(p_index).act_info26
2039 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info26,0) +
2040 p_bal_value ;
2041 end if;
2042
2043 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2044
2045 EXCEPTION
2046 when others then
2047 lv_error_message := 'Error in ' ||
2048 gv_package || lv_procedure_name;
2049
2050 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2051
2052 lv_error_message :=
2053 pay_emp_action_arch.set_error_message(lv_error_message);
2054
2055 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2056 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2057 hr_utility.raise_error;
2058
2059
2060 END update_sdi_ee_values;
2061
2062
2063
2064 /**************************************************************
2065 Name : update_sdi_er_values
2066 Purpose : The procedure set the SDI ER level balance value
2067 in the PL/SQL table.
2068 Arguments :
2069 Notes :
2070 **************************************************************/
2071 PROCEDURE update_sdi_er_values(p_balance in varchar2
2072 ,p_bal_value in number
2073 ,p_index in number
2074 )
2075 IS
2076 lv_procedure_name VARCHAR2(100) := '.update_sdi_ee_values';
2077 lv_error_message VARCHAR2(200);
2078
2079 BEGIN
2080 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2081 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2082 hr_utility.trace('p_balance = '|| p_balance);
2083 hr_utility.trace('p_bal_value = ' || p_bal_value);
2084
2085 if p_balance = 'SDI ER Liability' then
2086 pay_ac_action_arch.lrr_act_tab(p_index).act_info9
2087 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info9,0) +
2088 p_bal_value;
2089 elsif p_balance = 'SDI ER Taxable' then
2090 pay_ac_action_arch.lrr_act_tab(p_index).act_info10
2091 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info10,0) +
2092 p_bal_value;
2093 elsif p_balance = 'SDI ER Subj Whable' then
2094 pay_ac_action_arch.lrr_act_tab(p_index).act_info11
2095 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info11,0) +
2096 p_bal_value;
2097 elsif p_balance = 'SDI ER Pre Tax Redns' then
2098 pay_ac_action_arch.lrr_act_tab(p_index).act_info12
2099 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info12,0) +
2100 p_bal_value ;
2101 elsif p_balance = 'SDI ER Gross' then
2102 pay_ac_action_arch.lrr_act_tab(p_index).act_info27
2103 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info27,0) +
2104 p_bal_value;
2105 end if;
2106
2107 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2108
2109 EXCEPTION
2110 when others then
2111 lv_error_message := 'Error in ' ||
2112 gv_package || lv_procedure_name;
2113
2114 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2115
2116 lv_error_message :=
2117 pay_emp_action_arch.set_error_message(lv_error_message);
2118
2119 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2120 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2121 hr_utility.raise_error;
2122
2123 END update_sdi_er_values;
2124
2125
2126 /**************************************************************
2127 Name : update_sui_ee_values
2128 Purpose : The procedure set the SUI EE level balance value
2129 in the PL/SQL table.
2130 Arguments :
2131 Notes :
2132 **************************************************************/
2133 PROCEDURE update_sui_ee_values(p_balance in varchar2
2134 ,p_bal_value in number
2135 ,p_index in number
2136 )
2137 IS
2138 lv_procedure_name VARCHAR2(100) := '.update_sui_ee_values';
2139 lv_error_message VARCHAR2(200);
2140
2141 BEGIN
2142 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2143 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2144 hr_utility.trace('p_balance = '|| p_balance);
2145 hr_utility.trace('p_bal_value = ' || p_bal_value);
2146
2147 if p_balance = 'SUI EE Withheld' then
2148 pay_ac_action_arch.lrr_act_tab(p_index).act_info13
2149 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info13,0) +
2150 p_bal_value;
2151 elsif p_balance = 'SUI EE Taxable' then
2152 pay_ac_action_arch.lrr_act_tab(p_index).act_info14
2153 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info14,0) +
2154 p_bal_value;
2155 elsif p_balance = 'SUI EE Subj Whable' then
2156 pay_ac_action_arch.lrr_act_tab(p_index).act_info15
2157 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info15,0) +
2158 p_bal_value;
2159 elsif p_balance = 'SUI EE Pre Tax Redns' then
2160 pay_ac_action_arch.lrr_act_tab(p_index).act_info16
2161 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info16,0) +
2162 p_bal_value;
2163 elsif p_balance = 'SUI EE Gross' then
2164 pay_ac_action_arch.lrr_act_tab(p_index).act_info28
2165 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info28,0) +
2166 p_bal_value;
2167 end if;
2168 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2169
2170 EXCEPTION
2171 when others then
2172 lv_error_message := 'Error in ' ||
2173 gv_package || lv_procedure_name;
2174
2175 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2176
2177 lv_error_message :=
2178 pay_emp_action_arch.set_error_message(lv_error_message);
2179
2180 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2181 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2182 hr_utility.raise_error;
2183
2184 END update_sui_ee_values;
2185
2186
2187 /**************************************************************
2188 Name : update_sui_er_values
2189 Purpose : The procedure set the SUI ER level balance value
2190 in the PL/SQL table.
2191 Arguments :
2192 Notes :
2193 **************************************************************/
2194 PROCEDURE update_sui_er_values(p_balance in varchar2
2195 ,p_bal_value in number
2196 ,p_index in number
2197 )
2198 IS
2199 lv_procedure_name VARCHAR2(100) := '.update_sui_er_values';
2200 lv_error_message VARCHAR2(200);
2201
2202 BEGIN
2203 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2204 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2205 hr_utility.trace('p_balance = '|| p_balance);
2206 hr_utility.trace('p_bal_value = ' || p_bal_value);
2207
2208 if p_balance = 'SUI ER Taxable' then
2209 pay_ac_action_arch.lrr_act_tab(p_index).act_info18
2210 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info18,0)
2211 + p_bal_value;
2212 elsif p_balance = 'SUI ER Subj Whable' then
2213 pay_ac_action_arch.lrr_act_tab(p_index).act_info19
2214 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info19,0)
2215 + p_bal_value;
2216 elsif p_balance = 'SUI ER Pre Tax Redns' then
2217 pay_ac_action_arch.lrr_act_tab(p_index).act_info20
2218 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info20,0)
2219 + p_bal_value;
2220 elsif p_balance = 'SUI ER Liability' then
2221 pay_ac_action_arch.lrr_act_tab(p_index).act_info21
2222 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info21,0)
2223 + p_bal_value;
2224 elsif p_balance = 'SUI ER Gross' then
2225 pay_ac_action_arch.lrr_act_tab(p_index).act_info29
2226 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info29,0)
2227 + p_bal_value;
2228 end if;
2229 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2230
2231 EXCEPTION
2232 when others then
2233 lv_error_message := 'Error in ' ||
2234 gv_package || lv_procedure_name;
2235
2236 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2237
2238 lv_error_message :=
2239 pay_emp_action_arch.set_error_message(lv_error_message);
2240
2241 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2242 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2243 hr_utility.raise_error;
2244
2245 END update_sui_er_values;
2246
2247
2248 /**************************************************************
2249 Name : update_work_comp_values
2250 Purpose : The procedure set the Worker's Comp level balance
2251 value in the PL/SQL table.
2252 Arguments :
2253 Notes :
2254 **************************************************************/
2255 PROCEDURE update_work_comp_values(p_balance in varchar2
2256 ,p_bal_value in number
2257 ,p_index in number
2258 )
2259 IS
2260 lv_procedure_name VARCHAR2(100) := '.update_work_comp_values';
2261 lv_error_message VARCHAR2(200);
2262
2263 BEGIN
2264 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2265 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2266 hr_utility.trace('p_balance = '|| p_balance);
2267 hr_utility.trace('p_bal_value = ' || p_bal_value);
2268
2269 if p_balance = 'Workers Comp Withheld' then
2270 pay_ac_action_arch.lrr_act_tab(p_index).act_info22
2271 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info22,0) +
2272 p_bal_value ;
2273 elsif p_balance = 'Workers Comp2 Withheld' then
2274 pay_ac_action_arch.lrr_act_tab(p_index).act_info23
2275 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info23,0) +
2276 p_bal_value;
2277 elsif p_balance = 'Workers Compensation2 ER' then
2278 pay_ac_action_arch.lrr_act_tab(p_index).act_info24
2279 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info24,0) +
2280 p_bal_value;
2281 elsif p_balance = 'Workers Compensation3 ER' then
2282 pay_ac_action_arch.lrr_act_tab(p_index).act_info25
2283 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info25,0) +
2284 p_bal_value;
2285 end if;
2286 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2287
2288 EXCEPTION
2289 when others then
2290 lv_error_message := 'Error in ' ||
2291 gv_package || lv_procedure_name;
2292
2293 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2294
2295 lv_error_message :=
2296 pay_emp_action_arch.set_error_message(lv_error_message);
2297
2298 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2299 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2300 hr_utility.raise_error;
2301
2302 END update_work_comp_values;
2303
2304 /*Bug 3231253*/
2305 /**************************************************************
2306 Name : update_steic_values
2307 Purpose : The procedure sets the STEIC Advance balance value
2308 in the PL/SQL table.
2309 Arguments :
2310 Notes :
2311 **************************************************************/
2312 PROCEDURE update_steic_values(p_balance in varchar2
2313 ,p_bal_value in number
2314 ,p_index in number
2315 )
2316 IS
2317 lv_procedure_name VARCHAR2(100) := '.update_steic_values';
2318 lv_error_message VARCHAR2(200);
2319
2320 BEGIN
2321 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2322 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2323 hr_utility.trace('p_balance = '|| p_balance);
2324 hr_utility.trace('p_bal_value = ' || p_bal_value);
2325
2326 if p_balance = 'STEIC Advance' then
2327 pay_ac_action_arch.lrr_act_tab(p_index).act_info30
2328 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info30,0) +
2329 p_bal_value ;
2330 end if;
2331
2332 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2333
2334 EXCEPTION
2335 when others then
2336 lv_error_message := 'Error in ' ||
2337 gv_package || lv_procedure_name;
2338
2339 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2340
2341 lv_error_message :=
2342 pay_emp_action_arch.set_error_message(lv_error_message);
2343
2344 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2345 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2346 hr_utility.raise_error;
2347
2348 END update_steic_values;
2349
2350
2351 /**************************************************************
2352 Name : update_county_values
2353 Purpose : The procedure set the County level balance value
2354 in the PL/SQL table.
2355 Arguments :
2356 Notes :
2357 **************************************************************/
2358 PROCEDURE update_county_values(p_balance in varchar2
2359 ,p_bal_value in number
2360 ,p_index in number
2361 )
2362 IS
2363 lv_procedure_name VARCHAR2(100) := '.update_county_values';
2364 lv_error_message VARCHAR2(200);
2365
2366 BEGIN
2367 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2368 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2369 hr_utility.trace('p_balance = '|| p_balance);
2370 hr_utility.trace('p_bal_value = ' || p_bal_value);
2371
2372 if p_balance = 'County Withheld' then
2373 pay_ac_action_arch.lrr_act_tab(p_index ).act_info1
2374 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info1,0) +
2375 p_bal_value ;
2376 elsif p_balance = 'County Subj Whable' then
2377 pay_ac_action_arch.lrr_act_tab(p_index).act_info2
2378 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info2,0) +
2379 p_bal_value;
2380 elsif p_balance = 'County Subj NWhable' then
2381 pay_ac_action_arch.lrr_act_tab(p_index).act_info3
2382 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info3,0) +
2383 p_bal_value;
2384 elsif p_balance = 'County Pre Tax Redns' then
2385 pay_ac_action_arch.lrr_act_tab(p_index).act_info4
2386 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info4,0) +
2387 p_bal_value;
2388 elsif p_balance = 'County Gross' then
2389 pay_ac_action_arch.lrr_act_tab(p_index).act_info7
2390 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info7,0) +
2391 p_bal_value ;
2392 end if;
2393 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2394
2395 EXCEPTION
2396 when others then
2397 lv_error_message := 'Error in ' ||
2398 gv_package || lv_procedure_name;
2399
2400 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2401
2402 lv_error_message :=
2403 pay_emp_action_arch.set_error_message(lv_error_message);
2404
2405 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2406 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2407 hr_utility.raise_error;
2408
2409 END update_county_values;
2410
2411
2412 /**************************************************************
2413 Name : update_county_head_values
2414 Purpose : The procedure set the County Head level balance
2415 values in the PL/SQL table.
2416 Arguments :
2417 Notes :
2418 **************************************************************/
2419 PROCEDURE update_county_head_values(p_balance in varchar2
2420 ,p_bal_value in number
2421 ,p_index in number
2422 )
2423 IS
2424 lv_procedure_name VARCHAR2(100) := '.update_county_head_values';
2425 lv_error_message VARCHAR2(200);
2426
2427 BEGIN
2428 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2429 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2430 hr_utility.trace('p_balance = '|| p_balance);
2431 hr_utility.trace('p_bal_value = ' || p_bal_value);
2432
2433 if p_balance = 'Head Tax Liability' then
2434 pay_ac_action_arch.lrr_act_tab(p_index).act_info5
2435 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info5,0) +
2436 p_bal_value;
2437 elsif p_balance = 'Head Tax Withheld' then
2438 pay_ac_action_arch.lrr_act_tab(p_index).act_info6
2439 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info6,0) +
2440 p_bal_value;
2441 end if;
2442 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2443
2444 EXCEPTION
2445 when others then
2446 lv_error_message := 'Error in ' ||
2447 gv_package || lv_procedure_name;
2448
2449 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2450
2451 lv_error_message :=
2452 pay_emp_action_arch.set_error_message(lv_error_message);
2453
2454 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2455 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2456 hr_utility.raise_error;
2457
2458 END update_county_head_values;
2459
2460
2461 /**************************************************************
2462 Name : update_city_values
2463 Purpose : The procedure set the City level balance value
2464 in the PL/SQL table.
2465 Arguments :
2466 Notes :
2467 **************************************************************/
2468 PROCEDURE update_city_values(p_balance in varchar2
2469 ,p_bal_value in number
2470 ,p_index in number
2471 )
2472 IS
2473 lv_procedure_name VARCHAR2(100) := '.update_city_values';
2474 lv_error_message VARCHAR2(200);
2475
2476 BEGIN
2477 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2478 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2479 hr_utility.trace('p_balance = '|| p_balance);
2480 hr_utility.trace('p_bal_value = ' || p_bal_value);
2481
2482 if p_balance = 'City Withheld' then
2483 pay_ac_action_arch.lrr_act_tab(p_index ).act_info1
2484 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info1,0) +
2485 p_bal_value;
2486 elsif p_balance = 'City Subj Whable' then
2487 pay_ac_action_arch.lrr_act_tab(p_index).act_info2
2488 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info2,0) +
2489 p_bal_value;
2490 elsif p_balance = 'City Subj NWhable' then
2491 pay_ac_action_arch.lrr_act_tab(p_index).act_info3
2492 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info3,0) +
2493 p_bal_value;
2494 elsif p_balance = 'City Pre Tax Redns' then
2495 pay_ac_action_arch.lrr_act_tab(p_index).act_info4
2496 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info4,0) +
2497 p_bal_value;
2498 elsif p_balance = 'City Gross' then
2499 pay_ac_action_arch.lrr_act_tab(p_index ).act_info7
2500 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info7,0) +
2501 p_bal_value;
2502 end if;
2503 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2504
2505 EXCEPTION
2506 when others then
2507 lv_error_message := 'Error in ' ||
2508 gv_package || lv_procedure_name;
2509
2510 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2511
2512 lv_error_message :=
2513 pay_emp_action_arch.set_error_message(lv_error_message);
2514
2515 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2516 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2517 hr_utility.raise_error;
2518
2519 END update_city_values;
2520
2521
2522 /**************************************************************
2523 Name : update_city_head_values
2524 Purpose : The procedure set the City Head level balance value
2525 in the PL/SQL table.
2526 Arguments :
2527 Notes :
2528 **************************************************************/
2529 PROCEDURE update_city_head_values(p_balance in varchar2
2530 ,p_bal_value in number
2531 ,p_index in number
2532 )
2533 IS
2534 lv_procedure_name VARCHAR2(100) := '.update_city_head_values';
2535 lv_error_message VARCHAR2(200);
2536
2537 BEGIN
2538 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2539 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2540 hr_utility.trace('p_balance = '|| p_balance);
2541 hr_utility.trace('p_bal_value = ' || p_bal_value);
2542
2543 if p_balance = 'Head Tax Liability' then
2544 pay_ac_action_arch.lrr_act_tab(p_index).act_info5
2545 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info5,0) +
2546 p_bal_value;
2547 elsif p_balance = 'Head Tax Withheld' then
2548 pay_ac_action_arch.lrr_act_tab(p_index).act_info6
2549 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info6,0) +
2550 p_bal_value;
2551 end if;
2552 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2553
2554 EXCEPTION
2555 when others then
2556 lv_error_message := 'Error in ' ||
2557 gv_package || lv_procedure_name;
2558
2559 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2560
2561 lv_error_message :=
2562 pay_emp_action_arch.set_error_message(lv_error_message);
2563
2564 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2565 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2566 hr_utility.raise_error;
2567
2568 END update_city_head_values;
2569
2570
2571 /**************************************************************
2572 Name : update_school_values
2573 Purpose : The procedure set the School level balance value
2574 in the PL/SQL table.
2575 Arguments :
2576 Notes :
2577 **************************************************************/
2578 PROCEDURE update_school_values(p_balance in varchar2
2579 ,p_bal_value in number
2580 ,p_index in number
2581 )
2582 IS
2583 lv_procedure_name VARCHAR2(100) := '.update_school_values';
2584 lv_error_message VARCHAR2(200);
2585
2586 BEGIN
2587 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2588 hr_utility.trace('p_index = '|| pay_ac_action_arch.lrr_act_tab.count);
2589 hr_utility.trace('p_balance = '|| p_balance);
2590 hr_utility.trace('p_bal_value = ' || p_bal_value);
2591
2592 if p_balance = 'School Withheld' then
2593 pay_ac_action_arch.lrr_act_tab(p_index ).act_info1
2594 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info1,0) +
2595 p_bal_value ;
2596 elsif p_balance = 'School Subj Whable' then
2597 pay_ac_action_arch.lrr_act_tab(p_index).act_info2
2598 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info2,0) +
2599 p_bal_value;
2600 elsif p_balance = 'School Subj NWhable' then
2601 pay_ac_action_arch.lrr_act_tab(p_index).act_info3
2602 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info3,0) +
2603 + p_bal_value;
2604 elsif p_balance = 'School Pre Tax Redns' then
2605 pay_ac_action_arch.lrr_act_tab(p_index).act_info4
2606 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info4,0) +
2607 p_bal_value;
2608 elsif p_balance = 'School Gross' then
2609 pay_ac_action_arch.lrr_act_tab(p_index).act_info5
2610 := nvl(pay_ac_action_arch.lrr_act_tab(p_index).act_info5,0) +
2611 p_bal_value;
2612 end if;
2613 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2614
2615 EXCEPTION
2616 when others then
2617 lv_error_message := 'Error in ' ||
2618 gv_package || lv_procedure_name;
2619
2620 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2621
2622 lv_error_message :=
2623 pay_emp_action_arch.set_error_message(lv_error_message);
2624
2625 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2626 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2627 hr_utility.raise_error;
2628
2629 END update_school_values;
2630
2631
2632 /**************************************************************
2633 Name : get_city_tax_info
2634 Purpose : The procedure gets the City JIT Information
2635 Arguments :
2636 Notes :
2637 **************************************************************/
2638 PROCEDURE get_city_tax_info( p_effective_date in date
2639 ,p_emp_city_jurisdiction in varchar2
2640 ,p_city_tax_exists out nocopy varchar2
2641 ,p_city_head_tax_exists out nocopy varchar2
2642 )
2643 IS
2644 lv_city_tax_exists VARCHAR2(1);
2645 lv_city_head_tax_exists VARCHAR2(1);
2646 lv_procedure_name VARCHAR2(100) := '.get_city_tax_info';
2647 lv_error_message VARCHAR2(200);
2648
2649 BEGIN
2650 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2651 lv_city_tax_exists := pay_us_payroll_utils.get_tax_exists(
2652 p_tax_type => 'CITY'
2653 ,p_jurisdiction_code => p_emp_city_jurisdiction
2654 ,p_effective_date => p_effective_date) ;
2655
2656 lv_city_head_tax_exists := pay_us_payroll_utils.get_tax_exists(
2657 p_tax_type => 'HT'
2658 ,p_jurisdiction_code => p_emp_city_jurisdiction
2659 ,p_effective_date => p_effective_date) ;
2660
2661 p_city_tax_exists := lv_city_tax_exists ;
2662 p_city_head_tax_exists := lv_city_head_tax_exists;
2663 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2664
2665 EXCEPTION
2666 when others then
2667 lv_error_message := 'Error in ' ||
2668 gv_package || lv_procedure_name;
2669
2670 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2671
2672 lv_error_message :=
2673 pay_emp_action_arch.set_error_message(lv_error_message);
2674
2675 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2676 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2677 hr_utility.raise_error;
2678
2679 END get_city_tax_info;
2680
2681
2682 /******************************************************************
2683 Name : get_school_parent_jd
2684 Purpose : The procedure gets the City/County Jurisdiction for a
2685 School District Jurisdiction. This information is
2686 archived as a school distrinct can comparise of multiple
2687 Cities or Counties.
2688 Arguments :
2689 Notes :
2690 ******************************************************************/
2691 FUNCTION get_school_parent_jd(p_assignment_id in number
2692 ,p_school_jurisdiction in varchar2
2693 ,p_end_date in date
2694 ,p_start_date in date
2695 )
2696 RETURN VARCHAR2
2697 IS
2698 cursor c_get_county_school_district is
2699 select pcnt.jurisdiction_code
2700 from pay_us_emp_county_tax_rules_f pcnt
2701 where pcnt.assignment_id = p_assignment_id
2702 and pcnt.school_district_code = substr(p_school_jurisdiction,4,5)
2703 and pcnt.state_code = substr(p_school_jurisdiction,1,2)
2704 and pcnt.effective_start_date <= p_end_date
2705 and pcnt.effective_end_date >= p_start_date;
2706
2707 cursor c_get_city_school_district is
2708 select pcty.jurisdiction_code
2709 from pay_us_emp_city_tax_rules_f pcty
2710 where pcty.assignment_id = p_assignment_id
2711 and pcty.school_district_code = substr(p_school_jurisdiction,4,5)
2712 and pcty.state_code = substr(p_school_jurisdiction,1,2)
2713 and pcty.effective_start_date <= p_end_date
2714 and pcty.effective_end_date >= p_start_date;
2715
2716 lv_parent_jurisdiction_code VARCHAR2(11);
2717 lv_procedure_name VARCHAR2(100) := '.get_school_parent_jd';
2718 lv_error_message VARCHAR2(200);
2719
2720 BEGIN
2721 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2722 open c_get_city_school_district;
2723 fetch c_get_city_school_district into lv_parent_jurisdiction_code ;
2724 if c_get_city_school_district%notfound then
2725 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2726 open c_get_county_school_district;
2727 fetch c_get_county_school_district into lv_parent_jurisdiction_code ;
2728 close c_get_county_school_district;
2729 end if;
2730 close c_get_city_school_district;
2731
2732 hr_utility.trace('lv_parent_jurisdiction_code = '||
2733 lv_parent_jurisdiction_code);
2734 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2735
2736 return(lv_parent_jurisdiction_code);
2737 EXCEPTION
2738 when others then
2739 lv_error_message := 'Error in ' ||
2740 gv_package || lv_procedure_name;
2741
2742 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2743
2744 lv_error_message :=
2745 pay_emp_action_arch.set_error_message(lv_error_message);
2746
2747 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2748 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2749 hr_utility.raise_error;
2750
2751 END get_school_parent_jd;
2752
2753
2754 /******************************************************************
2755 Name : get_emp_residence
2756 Purpose : The procedure gets the Employee Resident JD
2757 Arguments :
2758 Notes :
2759 ******************************************************************/
2760 PROCEDURE get_emp_residence(p_assignment_id in number
2761 ,p_end_date in date
2762 ,p_run_effective_date in date
2763 ,p_resident_state_jd out nocopy varchar2
2764 ,p_resident_county_jd out nocopy varchar2
2765 ,p_resident_city_jd out nocopy varchar2
2766 )
2767
2768 IS
2769
2770 cursor c_get_emp_res_dtls(cp_assignment_id in number
2771 ,cp_run_effective_date in date) is
2772 select nvl(ADDR.add_information19,ADDR.region_1),
2773 nvl(ADDR.add_information17,ADDR.region_2),
2774 nvl(addr.add_information18,addr.town_or_city)
2775 from per_addresses addr
2776 ,per_all_assignments_f assign
2777 where cp_run_effective_date between assign.effective_start_date
2778 and assign.effective_end_date
2779 and assign.assignment_id = cp_assignment_id
2780 and addr.person_id = assign.person_id
2781 and addr.primary_flag = 'Y'
2782 and cp_run_effective_date between addr.date_from
2783 and nvl(addr.date_to,
2784 to_date('31/12/4712', 'DD/MM/YYYY'));
2785
2786 cursor c_get_emp_res_jd(cp_state_abbrev in varchar2
2787 ,cp_county_name in varchar2
2788 ,cp_city_name in varchar2) is
2789 select pcn.state_code, pcn.county_code, pcn.city_code
2790 from pay_us_states pus,
2791 pay_us_counties puc,
2792 pay_us_city_names pcn
2793 where pus.state_abbrev = cp_state_abbrev
2794 and puc.state_code = pus.state_code
2795 and puc.county_name = cp_county_name
2796 and pcn.state_code = puc.state_code
2797 and pcn.county_code = puc.county_code
2798 and pcn.city_name = cp_city_name ;
2799
2800 lv_resident_city_jd VARCHAR2(11);
2801 lv_resident_cnty_jd VARCHAR2(11);
2802 lv_resident_state_jd VARCHAR2(11);
2803
2804 lv_resident_city VARCHAR2(120);
2805 lv_resident_county VARCHAR2(120);
2806 lv_resident_state VARCHAR2(120);
2807
2808 lv_procedure_name VARCHAR2(100) := '.get_emp_residence';
2809 lv_error_message VARCHAR2(200);
2810
2811 BEGIN
2812 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2813 /* get the assignments resident city,county,state jurisdictions */
2814 open c_get_emp_res_dtls(p_assignment_id
2815 ,p_run_effective_date);
2816 fetch c_get_emp_res_dtls into lv_resident_county,
2817 lv_resident_state,
2818 lv_resident_city;
2819 close c_get_emp_res_dtls;
2820
2821 open c_get_emp_res_jd(lv_resident_state,
2822 lv_resident_county,
2823 lv_resident_city);
2824 fetch c_get_emp_res_jd into lv_resident_state_jd,
2825 lv_resident_cnty_jd,
2826 lv_resident_city_jd;
2827 close c_get_emp_res_jd;
2828
2829 p_resident_state_jd := lv_resident_state_jd;
2830 p_resident_county_jd := lv_resident_cnty_jd;
2831 p_resident_city_jd := lv_resident_city_jd;
2832 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2833
2834 EXCEPTION
2835 when others then
2836 lv_error_message := 'Error in ' ||
2837 gv_package || lv_procedure_name;
2838
2839 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2840
2841 lv_error_message :=
2842 pay_emp_action_arch.set_error_message(lv_error_message);
2843
2844 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2845 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2846 hr_utility.raise_error;
2847
2848 END get_emp_residence;
2849
2850
2851 /******************************************************************
2852 Name : update_ytd_withheld
2853 Purpose : This procedure inserts the Witheld Current and YTD
2854 amounts into the PL/SQL table for Employee Taxes
2855 Arguments :
2856 Notes :
2857 ******************************************************************/
2858 PROCEDURE update_ytd_withheld(p_xfr_action_id in number
2859 ,p_balance_name in varchar2
2860 ,p_balance_type_id in varchar2
2861 ,p_processing_priority in varchar2 default 10
2862 ,p_jurisdiction in varchar2
2863 ,p_curr_withheld in number
2864 ,p_ytd_withheld in number
2865 )
2866 IS
2867
2868 CURSOR get_display_name ( cp_reporting_name in varchar2 ,
2869 cp_jurisdiction_code in varchar2) IS
2870
2871 select decode(length(cp_jurisdiction_code),
2872 11, decode(cp_jurisdiction_code,
2873 '00-000-0000', null,
2874 decode(cp_reporting_name,
2875 'Head Tax Withheld', null,
2876 pay_us_employee_payslip_web.get_jurisdiction_name(
2877 cp_jurisdiction_code) || ' ')),
2878 8, pay_us_employee_payslip_web.get_jurisdiction_name(
2879 substr(cp_jurisdiction_code,1,2)||'-000-0000') || ' ') ||
2880 decode(fl.description, '', null,
2881 nvl(fl.description, cp_reporting_name)) || ' ' ||
2882 decode(length(cp_jurisdiction_code),
2883 8, decode(substr(cp_jurisdiction_code,1,2), '36', substr(cp_jurisdiction_code, 4),
2884 pay_us_employee_payslip_web.get_jurisdiction_name(cp_jurisdiction_code)),
2885 11, decode(cp_reporting_name,
2886 'Head Tax Withheld', pay_us_employee_payslip_web.get_jurisdiction_name(
2887 cp_jurisdiction_code)))display_name
2888 from fnd_common_lookups fl
2889 where fl.lookup_type(+) = 'PAY_US_LABELS'
2890 and upper(cp_reporting_name) = fl.lookup_code(+) ;
2891
2892 lv_display_name VARCHAR2(50);
2893 ln_index NUMBER;
2894 ln_element_index NUMBER;
2895 lv_procedure_name VARCHAR2(100) := '.update_ytd_withheld';
2896 lv_error_message VARCHAR2(200);
2897
2898 BEGIN
2899 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2900 if p_curr_withheld <> 0 or p_ytd_withheld <> 0 then -- Bug 3561821
2901
2902 ln_index := pay_ac_action_arch.lrr_act_tab.count;
2903 hr_utility.trace('ln_index = ' || ln_index);
2904
2905 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
2906 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_classfn
2907 := 'Tax Deductions';
2908 pay_ac_action_arch.emp_elements_tab(ln_element_index).jurisdiction_code
2909 := p_jurisdiction;
2910 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
2911 := p_balance_name;
2912 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
2913 := p_balance_type_id;
2914 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_processing_priority
2915 := p_processing_priority;
2916
2917 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
2918 := 'AC DEDUCTIONS';
2919 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
2920 := p_jurisdiction;
2921 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
2922 := p_xfr_action_id;
2923 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
2924 := 'Tax Deductions';
2925 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
2926 := p_balance_type_id ;
2927 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
2928 := p_processing_priority;
2929 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
2930 := fnd_number.number_to_canonical(nvl(p_curr_withheld,0));
2931 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
2932 := fnd_number.number_to_canonical(nvl(p_ytd_withheld,0));
2933 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
2934 := p_balance_name ;
2935 OPEN get_display_name( p_balance_name ,p_jurisdiction ) ;
2936 FETCH get_display_name INTO lv_display_name ;
2937 IF get_display_name%FOUND THEN
2938 IF substr(lv_display_name , -7,5) = 'BLANK' THEN
2939 lv_display_name := substr(lv_display_name , 1, length(lv_display_name)-8);
2940 hr_utility.trace('get_display_name inside if' || lv_display_name);
2941 END IF;
2942 ELSE
2943 lv_display_name := ' ';
2944 END IF;
2945 IF get_display_name%ISOPEN THEN
2946 close get_display_name;
2947 END IF;
2948 pay_ac_action_arch.lrr_act_tab(ln_index).act_info24
2949 := lv_display_name ;
2950 end if;
2951 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2952
2953 EXCEPTION
2954 when others then
2955 lv_error_message := 'Error in ' ||
2956 gv_package || lv_procedure_name;
2957
2958 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2959
2960 lv_error_message :=
2961 pay_emp_action_arch.set_error_message(lv_error_message);
2962
2963 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2964 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2965 hr_utility.raise_error;
2966
2967 END update_ytd_withheld;
2968
2969
2970 /******************************************************************
2971 Name : get_table_index
2972 Purpose : This function gets the index for the PL/SQL table
2973 for US Tax Balances for a given JD.
2974 For Bal Adjustments the function will return the same
2975 index for a given JD as we only archive one record for
2976 all Bal Adj. done on a given date.
2977 Arguments :
2978 Notes :
2979 ******************************************************************/
2980 FUNCTION get_table_index (p_jurisdiction_code in varchar2)
2981 RETURN NUMBER
2982 IS
2983 ln_table_index NUMBER;
2984 lv_index_flag VARCHAR2(1) := 'N';
2985
2986 BEGIN
2987 if pay_ac_action_arch.lrr_act_tab.count > 0 then
2988 for j in pay_ac_action_arch.lrr_act_tab.first ..
2989 pay_ac_action_arch.lrr_act_tab.last loop
2990
2991 if pay_ac_action_arch.lrr_act_tab(j).jurisdiction_code
2992 = p_jurisdiction_code and
2993 pay_ac_action_arch.lrr_act_tab(j).action_info_category
2994 in ('US FEDERAL', 'US STATE',
2995 'US COUNTY', 'US CITY',
2996 'US SCHOOL DISTRICT') then
2997 ln_table_index := j;
2998 lv_index_flag := 'Y';
2999 exit;
3000 end if;
3001 end loop;
3002 end if;
3003
3004 if lv_index_flag <> 'Y' then
3005 ln_table_index := pay_ac_action_arch.lrr_act_tab.count;
3006 end if;
3007 hr_utility.trace('ln_table_index = '|| ln_table_index);
3008
3009 return(ln_table_index);
3010
3011 END get_table_index;
3012
3013
3014 /******************************************************************
3015 Name : get_balance_value
3016 Purpose : This procedure calls the get_value function for
3017 balance calls.
3018 Arguments :
3019 Notes :
3020 ******************************************************************/
3021 FUNCTION get_balance_value(
3022 p_defined_balance_id in number
3023 ,p_balcall_aaid in number)
3024 RETURN NUMBER
3025 IS
3026 lv_error_message VARCHAR2(200);
3027 ln_bal_value NUMBER;
3028
3029 BEGIN
3030
3031 if p_defined_balance_id is not null then
3032 ln_bal_value := fnd_number.number_to_canonical(
3033 nvl(pay_balance_pkg.get_value(
3034 p_defined_balance_id,
3035 p_balcall_aaid),0));
3036 end if;
3037
3038 return (ln_bal_value);
3039
3040 EXCEPTION
3041 when others then
3042 return (null);
3043
3044 END get_balance_value;
3045
3046
3047
3048 /******************************************************************
3049 Name : populate_federal_tax_balances
3050 Purpose : This procedure gets all the federal level tax balances
3051 and populates the PL/SQL table.
3052 Arguments :
3053 Notes :
3054 ******************************************************************/
3055 PROCEDURE populate_federal_tax_balances(
3056 p_xfr_action_id in number
3057 ,p_pymt_balcall_aaid in number default null
3058 ,p_ytd_balcall_aaid in number default null
3059 ,p_rqp_action_id in number
3060 ,p_action_type in varchar2)
3061 IS
3062 ln_index NUMBER;
3063 lv_balance_name VARCHAR2(80);
3064 ln_balance_type_id NUMBER;
3065 ln_pymt_def_bal_id NUMBER;
3066 ln_ytd_def_bal_id NUMBER;
3067 ln_run_def_bal_id NUMBER;
3068
3069 ln_bal_value NUMBER(15,2);
3070
3071 ln_curr_withheld NUMBER(15,2):=0;
3072 ln_ytd_withheld NUMBER(15,2):=0;
3073 lv_procedure_name VARCHAR2(100) := '.populate_federal_tax_balances';
3074
3075 lv_error_message VARCHAR2(200);
3076 ln_step NUMBER;
3077
3078 BEGIN
3079 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3080 ln_step := 1;
3081
3082 ln_index := get_table_index('00-000-0000');
3083
3084 hr_utility.trace('Fed Loop Count = ' || ln_index);
3085 for i in pay_us_action_arch.ltr_fed_tax_bal.first..
3086 pay_us_action_arch.ltr_fed_tax_bal.last LOOP
3087
3088 lv_balance_name
3089 := pay_us_action_arch.ltr_fed_tax_bal(i).balance_name;
3090 ln_balance_type_id
3091 := pay_us_action_arch.ltr_fed_tax_bal(i).balance_type_id;
3092 ln_pymt_def_bal_id
3093 := pay_us_action_arch.ltr_fed_tax_bal(i).payment_def_bal_id;
3094 ln_ytd_def_bal_id
3095 := pay_us_action_arch.ltr_fed_tax_bal(i).ytd_def_bal_id;
3096 ln_run_def_bal_id
3097 := pay_us_action_arch.ltr_fed_tax_bal(i).asg_run_def_bal_id;
3098
3099 hr_utility.trace('lv_balance_name = ' || lv_balance_name);
3100 hr_utility.trace('ln_pymt_def_bal_id = ' || ln_pymt_def_bal_id);
3101 hr_utility.trace('ln_ytd_def_bal_id = ' || ln_ytd_def_bal_id);
3102 hr_utility.trace('ln_run_def_bal_id = ' || ln_run_def_bal_id);
3103
3104 ln_step := 5;
3105 if p_action_type in ( 'U', 'P') then
3106 if lv_balance_name not in ('SS ER Taxable',
3107 /*'SS ER Liability',
3108 'Medicare ER Liability',*/
3109 'Medicare ER Taxable',
3110 'Non W2 FIT Withheld') then
3111 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3112
3113 ln_step := 6;
3114 ln_bal_value := get_balance_value(
3115 p_defined_balance_id => ln_pymt_def_bal_id
3116 ,p_balcall_aaid => p_pymt_balcall_aaid);
3117 if lv_balance_name = 'EIC Advance' then
3118 ln_bal_value := -1 * ln_bal_value;
3119 end if;
3120
3121 elsif lv_balance_name = 'SS ER Taxable' then
3122 ln_bal_value
3123 := pay_ac_action_arch.lrr_act_tab(ln_index).act_info7;
3124 -- elsif lv_balance_name = 'SS ER Liability' then
3125 -- ln_bal_value
3126 -- := pay_ac_action_arch.lrr_act_tab(ln_index).act_info8;
3127 elsif lv_balance_name = 'Medicare ER Taxable' then
3128 ln_bal_value
3129 := pay_ac_action_arch.lrr_act_tab(ln_index).act_info13;
3130 -- elsif lv_balance_name = 'Medicare ER Liability' then
3131 -- ln_bal_value
3132 -- := pay_ac_action_arch.lrr_act_tab(ln_index).act_info14;
3133 end if;
3134
3135 else
3136 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3137 ln_step := 10;
3138 ln_bal_value := get_balance_value(
3139 p_defined_balance_id => ln_run_def_bal_id
3140 ,p_balcall_aaid => p_rqp_action_id);
3141 end if;
3142
3143 hr_utility.trace('ln_bal_value is'||to_char(ln_bal_value));
3144 update_federal_values(p_balance => lv_balance_name
3145 ,p_bal_value => ln_bal_value
3146 ,p_index => ln_index
3147 ,p_category => 'US FEDERAL');
3148
3149 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3150
3151 /*****************************************************************
3152 ** Insert data for Payslip
3153 ******************************************************************/
3154 if lv_balance_name = 'FIT Withheld' then
3155 ln_step := 12;
3156 ln_curr_withheld := ln_bal_value;
3157 ln_ytd_withheld := get_balance_value(
3158 p_defined_balance_id => ln_ytd_def_bal_id
3159 ,p_balcall_aaid => p_ytd_balcall_aaid);
3160 update_ytd_withheld(
3161 p_xfr_action_id => p_xfr_action_id
3162 ,p_balance_name => lv_balance_name
3163 ,p_balance_type_id => ln_balance_type_id
3164 ,p_processing_priority => 1
3165 ,p_jurisdiction => '00-000-0000'
3166 ,p_curr_withheld => ln_curr_withheld
3167 ,p_ytd_withheld => ln_ytd_withheld);
3168 elsif lv_balance_name = 'EIC Advance' then
3169 ln_step := 13;
3170 ln_curr_withheld := ln_bal_value;
3171 ln_ytd_withheld := get_balance_value(
3172 p_defined_balance_id => ln_ytd_def_bal_id
3173 ,p_balcall_aaid => p_ytd_balcall_aaid);
3174 ln_ytd_withheld := -1 * ln_ytd_withheld;
3175 update_ytd_withheld(
3176 p_xfr_action_id => p_xfr_action_id
3177 ,p_balance_name => lv_balance_name
3178 ,p_balance_type_id => ln_balance_type_id
3179 ,p_processing_priority => 4
3180 ,p_jurisdiction => '00-000-0000'
3181 ,p_curr_withheld => ln_curr_withheld
3182 ,p_ytd_withheld => ln_ytd_withheld);
3183 elsif lv_balance_name = 'Medicare EE Withheld' then
3184 ln_step := 14;
3185 ln_curr_withheld := ln_bal_value ;
3186 ln_ytd_withheld := get_balance_value(
3187 p_defined_balance_id => ln_ytd_def_bal_id
3188 ,p_balcall_aaid => p_ytd_balcall_aaid);
3189 update_ytd_withheld(
3190 p_xfr_action_id => p_xfr_action_id
3191 ,p_balance_name => lv_balance_name
3192 ,p_balance_type_id => ln_balance_type_id
3193 ,p_processing_priority => 3
3194 ,p_jurisdiction => '00-000-0000'
3195 ,p_curr_withheld => ln_curr_withheld
3196 ,p_ytd_withheld => ln_ytd_withheld);
3197 elsif lv_balance_name = 'SS EE Withheld' then
3198 ln_step := 16;
3199 ln_curr_withheld := ln_bal_value;
3200 ln_ytd_withheld := get_balance_value(
3201 p_defined_balance_id => ln_ytd_def_bal_id
3202 ,p_balcall_aaid => p_ytd_balcall_aaid);
3203 update_ytd_withheld(
3204 p_xfr_action_id => p_xfr_action_id
3205 ,p_balance_name => lv_balance_name
3206 ,p_balance_type_id => ln_balance_type_id
3207 ,p_processing_priority => 2
3208 ,p_jurisdiction => '00-000-0000'
3209 ,p_curr_withheld => ln_curr_withheld
3210 ,p_ytd_withheld => ln_ytd_withheld);
3211 elsif lv_balance_name = 'Non W2 FIT Withheld' then
3212
3213 ln_step := 18;
3214 if check_alien(p_xfr_action_id) = 'TRUE' then
3215 hr_utility.set_location(gv_package || lv_procedure_name||
3216 ' Chk Alien ', 20);
3217
3218 ln_bal_value := get_balance_value(
3219 p_defined_balance_id => ln_pymt_def_bal_id
3220 ,p_balcall_aaid => p_pymt_balcall_aaid);
3221
3222 ln_curr_withheld := ln_bal_value;
3223
3224 hr_utility.trace('NonW2FIT Pymt def balid = ' ||
3225 to_char(ln_pymt_def_bal_id));
3226 hr_utility.trace('NonW2FIT Cur = ' ||
3227 to_char(ln_curr_withheld));
3228
3229 ln_ytd_withheld := get_balance_value(
3230 p_defined_balance_id => ln_ytd_def_bal_id
3231 ,p_balcall_aaid => p_ytd_balcall_aaid);
3232
3233 hr_utility.trace('NonW2FIT YTD def balid = ' ||
3234 to_char(ln_ytd_def_bal_id));
3235 hr_utility.trace('NonW2FIT YTD = ' ||
3236 to_char(ln_ytd_withheld));
3237
3238 update_ytd_withheld(
3239 p_xfr_action_id => p_xfr_action_id
3240 ,p_balance_name => lv_balance_name
3241 ,p_balance_type_id => ln_balance_type_id
3242 ,p_jurisdiction => '00-000-0000'
3243 ,p_curr_withheld => ln_curr_withheld
3244 ,p_ytd_withheld => ln_ytd_withheld);
3245 end if;
3246 end if;
3247
3248 end loop;
3249
3250 hr_utility.set_location(gv_package || lv_procedure_name, 100);
3251 ln_step := 20;
3252
3253 EXCEPTION
3254 when others then
3255 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3256 gv_package || lv_procedure_name;
3257
3258 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3259
3260 lv_error_message :=
3261 pay_emp_action_arch.set_error_message(lv_error_message);
3262
3263 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3264 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3265 hr_utility.raise_error;
3266
3267 END populate_federal_tax_balances;
3268
3269
3270 /******************************************************************
3271 Name : populate_state_tax_balances
3272 Purpose : This procedure gets all the state level tax balances
3273 and populates the PL/SQL table.
3274 Arguments :
3275 Notes :
3276 ******************************************************************/
3277 PROCEDURE populate_state_tax_balances(
3278 p_xfr_action_id in number
3279 ,p_pymt_balcall_aaid in number default null
3280 ,p_ytd_balcall_aaid in number default null
3281 ,p_rqp_action_id in number
3282 ,p_action_type in varchar2
3283 ,p_jurisdiction_tab in pay_ac_action_arch.emp_jd_rec_table)
3284 IS
3285 ln_index NUMBER ;
3286 lv_balance_name VARCHAR2(80);
3287 ln_balance_type_id NUMBER;
3288 ln_pymt_def_bal_id NUMBER;
3289 ln_ytd_def_bal_id NUMBER;
3290 ln_run_def_bal_id NUMBER;
3291
3292 lv_state_code VARCHAR2(10);
3293 lv_sit_exists VARCHAR2(1);
3294 lv_sdi_ee_exists VARCHAR2(1);
3295 lv_sdi_er_exists VARCHAR2(1);
3296 lv_sui_ee_exists VARCHAR2(1);
3297 lv_sui_er_exists VARCHAR2(1);
3298
3299 ln_bal_value NUMBER(15,2);
3300
3301 ln_curr_withheld NUMBER(15,2):=0;
3302 ln_ytd_withheld NUMBER(15,2):=0;
3303 lv_procedure_name VARCHAR2(100) := '.populate_state_tax_balances';
3304 lv_error_message VARCHAR2(200);
3305 ln_step NUMBER;
3306
3307
3308 BEGIN
3309 ln_step := 1;
3310 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3311
3312 hr_utility.trace('State Balance Loop Count = ' ||
3313 p_jurisdiction_tab.count);
3314 if p_jurisdiction_tab.count > 0 then
3315 /*(*/
3316 for i in p_jurisdiction_tab.first..
3317 p_jurisdiction_tab.last loop
3318 if p_action_type = 'B' then
3319 lv_state_code := 'Y';
3320 lv_sit_exists := 'Y';
3321 lv_sui_ee_exists := 'Y';
3322 lv_sui_er_exists := 'Y';
3323 lv_sdi_ee_exists := 'Y';
3324 lv_sdi_er_exists := 'Y';
3325 else
3326 lv_state_code := substr(p_jurisdiction_tab(i).emp_jd,1,2);
3327 lv_sit_exists
3328 := pay_us_action_arch.ltr_state_tax_info(lv_state_code).sit_exists;
3329 lv_sui_ee_exists
3330 := pay_us_action_arch.ltr_state_tax_info(lv_state_code).sui_ee_exists;
3331 lv_sui_er_exists
3332 := pay_us_action_arch.ltr_state_tax_info(lv_state_code).sui_er_exists;
3333 lv_sdi_ee_exists
3334 := pay_us_action_arch.ltr_state_tax_info(lv_state_code).sdi_ee_exists;
3335 lv_sdi_er_exists
3336 := pay_us_action_arch.ltr_state_tax_info(lv_state_code).sdi_er_exists;
3337 end if;
3338
3339 hr_utility.trace('p_action_type = ' || p_action_type);
3340 hr_utility.trace('lv_sit_exists = ' || lv_sit_exists);
3341 hr_utility.trace('lv_sui_ee_exists = ' || lv_sui_ee_exists);
3342 hr_utility.trace('lv_sui_er_exists = ' || lv_sui_er_exists);
3343 hr_utility.trace('lv_sdi_ee_exists = ' || lv_sdi_ee_exists);
3344 hr_utility.trace('lv_sdi_er_exists = ' || lv_sdi_er_exists);
3345 hr_utility.trace('Archiving for Jurisdiction = ' ||
3346 p_jurisdiction_tab(i).emp_jd);
3347
3348 pay_balance_pkg.set_context('JURISDICTION_CODE',
3349 p_jurisdiction_tab(i).emp_jd);
3350
3351 ln_bal_value := 0;
3352 ln_curr_withheld := 0;
3353 ln_ytd_withheld := 0;
3354
3355 ln_step := 2;
3356 ln_index := get_table_index(p_jurisdiction_tab(i).emp_jd);
3357 hr_utility.trace('ln_index for state is '||to_char(ln_index));
3358
3359 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3360 := 'US STATE';
3361 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
3362 := p_jurisdiction_tab(i).emp_jd;
3363
3364 if pay_us_action_arch.ltr_state_tax_bal.count > 0 then
3365 --(
3366 for j in pay_us_action_arch.ltr_state_tax_bal.first..
3367 pay_us_action_arch.ltr_state_tax_bal.last loop
3368 lv_balance_name := pay_us_action_arch.ltr_state_tax_bal(j).balance_name;
3369 ln_balance_type_id := pay_us_action_arch.ltr_state_tax_bal(j).balance_type_id;
3370 ln_pymt_def_bal_id := pay_us_action_arch.ltr_state_tax_bal(j).payment_def_bal_id;
3371 ln_ytd_def_bal_id := pay_us_action_arch.ltr_state_tax_bal(j).ytd_def_bal_id;
3372 ln_run_def_bal_id := pay_us_action_arch.ltr_state_tax_bal(j).asg_run_def_bal_id;
3373
3374 hr_utility.trace('lv_balance_name =' || lv_balance_name);
3375 hr_utility.trace('ln_pymt_def_bal_id=' || ln_pymt_def_bal_id);
3376 hr_utility.trace('ln_ytd_def_bal_id =' || ln_ytd_def_bal_id);
3377 hr_utility.trace('ln_run_def_bal_id =' || ln_run_def_bal_id);
3378
3379 --SIT
3380 ln_step := 3;
3381 if substr(lv_balance_name, 1,3) = 'SIT' then
3382 if lv_sit_exists = 'Y' then
3383 hr_utility.set_location(gv_package || lv_procedure_name,
3384 110);
3385 if p_action_type in ('U', 'P') then
3386 -- SIT Alien
3387 if lv_balance_name = 'SIT Alien Withheld' then
3388 ln_step := 4;
3389 -- Alien
3390 if check_alien (p_xfr_action_id) = 'TRUE' then
3391 ln_bal_value := get_balance_value(
3392 p_defined_balance_id => ln_pymt_def_bal_id
3393 ,p_balcall_aaid => p_pymt_balcall_aaid);
3394
3395 ln_curr_withheld := ln_bal_value;
3396 hr_utility.trace('NonW2SIT Cur = ' || ln_curr_withheld);
3397
3398 ln_ytd_withheld := get_balance_value(
3399 p_defined_balance_id => ln_ytd_def_bal_id
3400 ,p_balcall_aaid => p_ytd_balcall_aaid);
3401 hr_utility.trace('NonW2SIT YTD = ' || ln_ytd_withheld);
3402
3403 update_ytd_withheld(
3404 p_xfr_action_id => p_xfr_action_id
3405 ,p_balance_name => lv_balance_name
3406 ,p_balance_type_id => ln_balance_type_id
3407 ,p_jurisdiction =>
3408 p_jurisdiction_tab(i).emp_jd
3409 ,p_curr_withheld => ln_curr_withheld
3410 ,p_ytd_withheld => ln_ytd_withheld);
3411 end if;
3412
3413 else -- SIT Alien
3414 ln_step := 5;
3415 ln_bal_value := get_balance_value(
3416 p_defined_balance_id => ln_pymt_def_bal_id
3417 ,p_balcall_aaid => p_pymt_balcall_aaid);
3418
3419 if lv_balance_name = 'SIT Withheld' then
3420 ln_step := 6;
3421 ln_curr_withheld := ln_bal_value;
3422 ln_ytd_withheld := get_balance_value(
3423 p_defined_balance_id => ln_ytd_def_bal_id
3424 ,p_balcall_aaid => p_ytd_balcall_aaid);
3425 update_ytd_withheld(
3426 p_xfr_action_id => p_xfr_action_id
3427 ,p_balance_name => lv_balance_name
3428 ,p_balance_type_id => ln_balance_type_id
3429 ,p_processing_priority => 5
3430 ,p_jurisdiction =>
3431 p_jurisdiction_tab(i).emp_jd
3432 ,p_curr_withheld => ln_curr_withheld
3433 ,p_ytd_withheld => ln_ytd_withheld);
3434 end if;
3435 end if; -- SIT Alien
3436 else
3437 ln_step := 7;
3438 ln_bal_value := get_balance_value(
3439 p_defined_balance_id => ln_run_def_bal_id
3440 ,p_balcall_aaid => p_rqp_action_id);
3441 end if;
3442
3443 hr_utility.trace('ln_bal_value = ' || ln_bal_value);
3444 ln_step := 8;
3445 update_sit_values(p_balance => lv_balance_name
3446 ,p_bal_value => ln_bal_value
3447 ,p_index => ln_index);
3448
3449 end if; -- sit exists
3450 end if; -- taxtype is SIT
3451
3452 --SDI
3453 ln_step := 11;
3454 if substr(lv_balance_name, 1,3) = 'SDI' then
3455 if substr(lv_balance_name, 5, 2) = 'EE' then
3456 if lv_sdi_ee_exists = 'Y' then
3457 if p_action_type in ( 'U', 'P') then
3458 ln_bal_value := get_balance_value(
3459 p_defined_balance_id => ln_pymt_def_bal_id
3460 ,p_balcall_aaid => p_pymt_balcall_aaid);
3461
3462 if lv_balance_name = 'SDI EE Withheld' then
3463 ln_curr_withheld := ln_bal_value;
3464 ln_ytd_withheld := get_balance_value(
3465 p_defined_balance_id => ln_ytd_def_bal_id
3466 ,p_balcall_aaid => p_ytd_balcall_aaid);
3467 update_ytd_withheld(
3468 p_xfr_action_id => p_xfr_action_id
3469 ,p_balance_name => 'SDI Withheld'
3470 ,p_balance_type_id => ln_balance_type_id
3471 ,p_jurisdiction =>
3472 p_jurisdiction_tab(i).emp_jd
3473 ,p_curr_withheld => ln_curr_withheld
3474 ,p_ytd_withheld => ln_ytd_withheld);
3475
3476 end if;
3477 else
3478 ln_bal_value := get_balance_value(
3479 p_defined_balance_id => ln_run_def_bal_id
3480 ,p_balcall_aaid => p_rqp_action_id);
3481 end if;
3482
3483 update_sdi_ee_values(p_balance => lv_balance_name
3484 ,p_bal_value => ln_bal_value
3485 ,p_index => ln_index);
3486
3487 end if;
3488 elsif substr(lv_balance_name,5,2) = 'ER' then
3489 if lv_sdi_er_exists = 'Y' then
3490 if p_action_type in ( 'U', 'P') then
3491 ln_bal_value := get_balance_value(
3492 p_defined_balance_id => ln_pymt_def_bal_id
3493 ,p_balcall_aaid => p_pymt_balcall_aaid);
3494 else
3495 ln_bal_value := get_balance_value(
3496 p_defined_balance_id => ln_run_def_bal_id
3497 ,p_balcall_aaid => p_rqp_action_id);
3498 end if;
3499 update_sdi_er_values(p_balance => lv_balance_name
3500 ,p_bal_value => ln_bal_value
3501 ,p_index => ln_index);
3502
3503 end if; -- if SDI ER exists
3504 end if; -- if type EE or ER
3505 end if; --if taxtype is SDI
3506
3507 --SUI
3508 ln_step := 15;
3509 if substr(lv_balance_name, 1, 3) = 'SUI' then
3510 if substr(lv_balance_name, 5, 2) = 'EE' then
3511 if lv_sui_ee_exists = 'Y' then
3512 if p_action_type in ( 'U', 'P') then
3513 ln_bal_value := get_balance_value(
3514 p_defined_balance_id => ln_pymt_def_bal_id
3515 ,p_balcall_aaid => p_pymt_balcall_aaid);
3516 if lv_balance_name = 'SUI EE Withheld' then
3517 ln_curr_withheld := ln_bal_value;
3518 ln_ytd_withheld := get_balance_value(
3519 p_defined_balance_id => ln_ytd_def_bal_id
3520 ,p_balcall_aaid => p_ytd_balcall_aaid);
3521 update_ytd_withheld(
3522 p_xfr_action_id => p_xfr_action_id
3523 ,p_balance_name => 'SUI Withheld'
3524 ,p_balance_type_id => ln_balance_type_id
3525 ,p_jurisdiction =>
3526 p_jurisdiction_tab(i).emp_jd
3527 ,p_curr_withheld => ln_curr_withheld
3528 ,p_ytd_withheld => ln_ytd_withheld);
3529 end if;
3530 else
3531 ln_bal_value := get_balance_value(
3532 p_defined_balance_id => ln_run_def_bal_id
3533 ,p_balcall_aaid => p_rqp_action_id);
3534 end if;
3535
3536 update_sui_ee_values(p_balance => lv_balance_name
3537 ,p_bal_value => ln_bal_value
3538 ,p_index => ln_index);
3539
3540 end if; -- SUI EE exists
3541
3542 elsif substr(lv_balance_name,5,2) = 'ER' then
3543 if lv_sui_er_exists = 'Y' then
3544 if p_action_type in ( 'U', 'P') then
3545 ln_bal_value := get_balance_value(
3546 p_defined_balance_id => ln_pymt_def_bal_id
3547 ,p_balcall_aaid => p_pymt_balcall_aaid);
3548 else
3549 ln_bal_value := get_balance_value(
3550 p_defined_balance_id => ln_run_def_bal_id
3551 ,p_balcall_aaid => p_rqp_action_id);
3552 end if;
3553
3554 update_sui_er_values(p_balance => lv_balance_name
3555 ,p_bal_value => ln_bal_value
3556 ,p_index => ln_index);
3557 end if;
3558 end if;
3559 end if; --if taxtype is SUI
3560
3561 ln_step := 20;
3562 if substr(lv_balance_name, 1, 4) = 'Work' then
3563 if p_action_type in ( 'U', 'P') then
3564 ln_bal_value := get_balance_value(
3565 p_defined_balance_id => ln_pymt_def_bal_id
3566 ,p_balcall_aaid => p_pymt_balcall_aaid);
3567 if lv_balance_name = 'Workers Comp Withheld' then
3568 ln_curr_withheld := ln_bal_value;
3569 ln_ytd_withheld := get_balance_value(
3570 p_defined_balance_id => ln_ytd_def_bal_id
3571 ,p_balcall_aaid => p_ytd_balcall_aaid);
3572 update_ytd_withheld(
3573 p_xfr_action_id => p_xfr_action_id
3574 ,p_balance_name => 'WC Withheld'
3575 ,p_balance_type_id => ln_balance_type_id
3576 ,p_jurisdiction =>
3577 p_jurisdiction_tab(i).emp_jd
3578 ,p_processing_priority => 10
3579 ,p_curr_withheld => ln_curr_withheld
3580 ,p_ytd_withheld => ln_ytd_withheld);
3581 elsif lv_balance_name = 'Workers Comp2 Withheld' then
3582 ln_curr_withheld := ln_bal_value;
3583 ln_ytd_withheld := get_balance_value(
3584 p_defined_balance_id => ln_ytd_def_bal_id
3585 ,p_balcall_aaid => p_ytd_balcall_aaid);
3586 update_ytd_withheld(
3587 p_xfr_action_id => p_xfr_action_id
3588 ,p_balance_name => 'WC2 Withheld'
3589 ,p_balance_type_id => ln_balance_type_id
3590 ,p_jurisdiction =>
3591 p_jurisdiction_tab(i).emp_jd
3592 ,p_processing_priority =>10
3593 ,p_curr_withheld => ln_curr_withheld
3594 ,p_ytd_withheld => ln_ytd_withheld);
3595 end if;
3596
3597 else
3598 ln_bal_value := get_balance_value(
3599 p_defined_balance_id => ln_run_def_bal_id
3600 ,p_balcall_aaid => p_rqp_action_id);
3601 end if;
3602 hr_utility.trace('State Balance value is '|| ln_bal_value);
3603
3604 update_work_comp_values(p_balance => lv_balance_name
3605 ,p_bal_value => ln_bal_value
3606 ,p_index => ln_index);
3607 end if; -- taxtype is Workerscomp
3608
3609 --STEIC /*Bug 3231253*/
3610 if substr(lv_balance_name, 1, 5) = 'STEIC' then
3611 if p_action_type in ( 'U', 'P') then
3612 ln_bal_value := get_balance_value(
3613 p_defined_balance_id => ln_pymt_def_bal_id
3614 ,p_balcall_aaid => p_pymt_balcall_aaid);
3615 if lv_balance_name = 'STEIC Advance' then
3616 ln_bal_value := -1 * ln_bal_value;
3617 ln_curr_withheld := ln_bal_value;
3618 ln_ytd_withheld := get_balance_value(
3619 p_defined_balance_id => ln_ytd_def_bal_id
3620 ,p_balcall_aaid => p_ytd_balcall_aaid);
3621
3622 ln_ytd_withheld := -1 * ln_ytd_withheld ;
3623 update_ytd_withheld(
3624 p_xfr_action_id => p_xfr_action_id
3625 ,p_balance_name => 'STEIC Advance'
3626 ,p_balance_type_id => ln_balance_type_id
3627 ,p_jurisdiction =>
3628 p_jurisdiction_tab(i).emp_jd
3629 ,p_processing_priority => 10
3630 ,p_curr_withheld => ln_curr_withheld
3631 ,p_ytd_withheld => ln_ytd_withheld);
3632
3633 end if;
3634
3635 else
3636 ln_bal_value := get_balance_value(
3637 p_defined_balance_id => ln_run_def_bal_id
3638 ,p_balcall_aaid => p_rqp_action_id);
3639 end if;
3640 hr_utility.trace('STEIC State Balance value is '|| ln_bal_value);
3641
3642 update_steic_values(p_balance => lv_balance_name
3643 ,p_bal_value => ln_bal_value
3644 ,p_index => ln_index);
3645 end if; -- taxtype is STEIC Advance
3646
3647 ln_bal_value := 0;
3648 ln_curr_withheld := 0;
3649 ln_ytd_withheld := 0;
3650 end loop; -- of ltr_state_tax_bal.taxtype)
3651 end if; -- of ltr_state_tax_bal.taxtype
3652 end loop; -- )state jurisdiction loop
3653 end if;
3654
3655 hr_utility.set_location(gv_package || lv_procedure_name, 150);
3656
3657 EXCEPTION
3658 when others then
3659 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3660 gv_package || lv_procedure_name;
3661
3662 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3663
3664 lv_error_message :=
3665 pay_emp_action_arch.set_error_message(lv_error_message);
3666
3667 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3668 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3669 hr_utility.raise_error;
3670
3671 END populate_state_tax_balances;
3672
3673
3674 /******************************************************************
3675 Name : populate_county_tax_balances
3676 Purpose : This procedure gets all the County level tax balances
3677 and populates the PL/SQL table.
3678 Arguments :
3679 Notes :
3680 ******************************************************************/
3681 PROCEDURE populate_county_tax_balances(
3682 p_xfr_action_id in number
3683 ,p_pymt_balcall_aaid in number default null
3684 ,p_ytd_balcall_aaid in number default null
3685 ,p_rqp_action_id in number
3686 ,p_action_type in varchar2
3687 ,p_resident_state in varchar2
3688 ,p_resident_county in varchar2
3689 ,p_resident_city in varchar2
3690 ,p_jurisdiction_tab in pay_ac_action_arch.emp_jd_rec_table)
3691 IS
3692 ln_index NUMBER ;
3693 lv_balance_name VARCHAR2(80);
3694 ln_balance_type_id NUMBER;
3695 ln_pymt_def_bal_id NUMBER;
3696 ln_ytd_def_bal_id NUMBER;
3697 ln_run_def_bal_id NUMBER;
3698
3699 lv_county_tax_exists VARCHAR2(1);
3700 lv_county_head_tax_exists VARCHAR2(1);
3701
3702 ln_bal_value NUMBER(15,2) := 0;
3703 ln_curr_withheld NUMBER(15,2) := 0;
3704 ln_ytd_withheld NUMBER(15,2) := 0;
3705
3706 lv_procedure_name VARCHAR2(100) := '.populate_county_tax_balances';
3707 lv_error_message VARCHAR2(200);
3708 ln_step NUMBER;
3709
3710
3711 BEGIN
3712 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3713 hr_utility.trace('County Balance Loop Count = ' ||
3714 pay_us_action_arch.ltr_county_tax_bal.count);
3715
3716 if p_jurisdiction_tab.count > 0 then
3717 for i in p_jurisdiction_tab.first..
3718 p_jurisdiction_tab.last loop
3719
3720 hr_utility.set_location(gv_package || lv_procedure_name, 160);
3721 if pay_us_action_arch.ltr_county_tax_info.count > 0 then
3722 if p_action_type = 'B' then
3723 lv_county_tax_exists := 'Y';
3724 lv_county_head_tax_exists := 'Y';
3725 else
3726 for j in pay_us_action_arch.ltr_county_tax_info.first ..
3727 pay_us_action_arch.ltr_county_tax_info.last loop
3728 if pay_us_action_arch.ltr_county_tax_info(j).jurisdiction_code
3729 = p_jurisdiction_tab(i).emp_jd then
3730 lv_county_tax_exists
3731 := pay_us_action_arch.ltr_county_tax_info(j).cnty_tax_exists;
3732 lv_county_head_tax_exists
3733 := pay_us_action_arch.ltr_county_tax_info(j).cnty_head_tax_exists;
3734 exit;
3735 end if;
3736 end loop;
3737 end if;
3738 end if;
3739
3740 hr_utility.trace('COUNTY lv_county_tax_exists = ' ||
3741 lv_county_tax_exists);
3742 hr_utility.trace('COUNTY lv_county_head_tax_exists = ' ||
3743 lv_county_head_tax_exists);
3744 hr_utility.trace('COUNTY Archiving for Jurisdiction = ' ||
3745 p_jurisdiction_tab(i).emp_jd);
3746
3747 pay_balance_pkg.set_context('JURISDICTION_CODE',
3748 p_jurisdiction_tab(i).emp_jd);
3749
3750 ln_index := get_table_index(p_jurisdiction_tab(i).emp_jd);
3751
3752 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
3753 := p_jurisdiction_tab(i).emp_jd;
3754 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3755 := 'US COUNTY';
3756
3757 if p_jurisdiction_tab(i).emp_jd
3758 = p_resident_state||'-'||p_resident_county|| '-0000' then
3759 pay_ac_action_arch.lrr_act_tab(ln_index).act_info30 := 'R';
3760 else
3761 pay_ac_action_arch.lrr_act_tab(ln_index).act_info30 := 'NR';
3762 end if;
3763
3764 if pay_us_action_arch.ltr_county_tax_bal.count > 0 then
3765 for k in pay_us_action_arch.ltr_county_tax_bal.first..
3766 pay_us_action_arch.ltr_county_tax_bal.last loop
3767 lv_balance_name := pay_us_action_arch.ltr_county_tax_bal(k).balance_name;
3768 ln_balance_type_id := pay_us_action_arch.ltr_county_tax_bal(k).balance_type_id;
3769 ln_pymt_def_bal_id := pay_us_action_arch.ltr_county_tax_bal(k).payment_def_bal_id;
3770 ln_ytd_def_bal_id := pay_us_action_arch.ltr_county_tax_bal(k).ytd_def_bal_id;
3771 ln_run_def_bal_id := pay_us_action_arch.ltr_county_tax_bal(k).asg_run_def_bal_id;
3772
3773 hr_utility.trace('lv_balance_name = '||lv_balance_name);
3774 hr_utility.trace('ln_pymt_def_bal_id = '||ln_pymt_def_bal_id);
3775 hr_utility.trace('ln_ytd_def_bal_id = '||ln_ytd_def_bal_id);
3776 hr_utility.trace('ln_run_def_bal_id = '||ln_run_def_bal_id);
3777
3778 if substr(lv_balance_name, 1, 6) = 'County' then
3779 if lv_county_tax_exists = 'Y' then
3780 if p_action_type in ( 'U', 'P') then
3781 ln_bal_value := get_balance_value(
3782 p_defined_balance_id => ln_pymt_def_bal_id
3783 ,p_balcall_aaid => p_pymt_balcall_aaid);
3784
3785 if lv_balance_name = 'County Withheld' then
3786 ln_curr_withheld := ln_bal_value;
3787 ln_ytd_withheld := get_balance_value(
3788 p_defined_balance_id => ln_ytd_def_bal_id
3789 ,p_balcall_aaid => p_ytd_balcall_aaid);
3790 update_ytd_withheld(
3791 p_xfr_action_id => p_xfr_action_id
3792 ,p_balance_name => 'County Withheld'
3793 ,p_balance_type_id => ln_balance_type_id
3794 ,p_processing_priority => 6
3795 ,p_jurisdiction =>
3796 p_jurisdiction_tab(i).emp_jd
3797 ,p_curr_withheld => ln_curr_withheld
3798 ,p_ytd_withheld => ln_ytd_withheld);
3799 end if;
3800 else
3801 ln_bal_value := get_balance_value(
3802 p_defined_balance_id => ln_run_def_bal_id
3803 ,p_balcall_aaid => p_rqp_action_id);
3804 end if;
3805
3806 update_county_values(p_balance => lv_balance_name
3807 ,p_bal_value => ln_bal_value
3808 ,p_index => ln_index);
3809 end if; --county tax exists
3810 end if; --substr is County
3811
3812 if substr(lv_balance_name, 1, 4) = 'Head' then
3813 if lv_county_head_tax_exists = 'Y' then
3814 if p_action_type in ( 'U', 'P') then
3815 ln_bal_value := get_balance_value(
3816 p_defined_balance_id => ln_pymt_def_bal_id
3817 ,p_balcall_aaid => p_pymt_balcall_aaid);
3818 if lv_balance_name = 'Head Tax Withheld' then
3819 ln_curr_withheld := ln_bal_value;
3820 ln_ytd_withheld := get_balance_value(
3821 p_defined_balance_id => ln_ytd_def_bal_id
3822 ,p_balcall_aaid => p_ytd_balcall_aaid);
3823
3824 update_ytd_withheld(
3825 p_xfr_action_id => p_xfr_action_id
3826 ,p_balance_name => 'Head Tax Withheld'
3827 ,p_balance_type_id => ln_balance_type_id
3828 ,p_jurisdiction =>
3829 p_jurisdiction_tab(i).emp_jd
3830 ,p_curr_withheld => ln_curr_withheld
3831 ,p_ytd_withheld => ln_ytd_withheld);
3832
3833 end if;
3834 else
3835 ln_bal_value := get_balance_value(
3836 p_defined_balance_id => ln_run_def_bal_id
3837 ,p_balcall_aaid => p_rqp_action_id);
3838 end if;
3839
3840 update_county_head_values(
3841 p_balance => lv_balance_name
3842 ,p_bal_value => ln_bal_value
3843 ,p_index => ln_index);
3844 end if; --head tax exists
3845 end if; --substr is Head
3846
3847 ln_bal_value := 0;
3848 ln_curr_withheld := 0;
3849 ln_ytd_withheld := 0;
3850
3851 end loop; -- ltr_county_tax_bal
3852 end if; -- ltr_county_tax_bal
3853
3854 end loop; -- of jurisdiction)
3855 end if; -- of jurisdiction)
3856 hr_utility.set_location(gv_package || lv_procedure_name, 100);
3857
3858 EXCEPTION
3859 when others then
3860 lv_error_message := 'Error in ' ||
3861 gv_package || lv_procedure_name;
3862
3863 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3864
3865 lv_error_message :=
3866 pay_emp_action_arch.set_error_message(lv_error_message);
3867
3868 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3869 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3870 hr_utility.raise_error;
3871 END populate_county_tax_balances;
3872
3873
3874 /******************************************************************
3875 Name : populate_city_tax_balances
3876 Purpose : This procedure gets all the City level tax balances
3877 and populates the PL/SQL table.
3878 Arguments :
3879 Notes :
3880 ******************************************************************/
3881 PROCEDURE populate_city_tax_balances(
3882 p_xfr_action_id in number
3883 ,p_pymt_balcall_aaid in number default null
3884 ,p_ytd_balcall_aaid in number default null
3885 ,p_rqp_action_id in number
3886 ,p_action_type in varchar2
3887 ,p_resident_state in varchar2
3888 ,p_resident_county in varchar2
3889 ,p_resident_city in varchar2
3890 ,p_effective_date in date
3891 ,p_jurisdiction_tab in pay_ac_action_arch.emp_jd_rec_table)
3892 IS
3893 ln_index NUMBER ;
3894 lv_balance_name VARCHAR2(80);
3895 ln_balance_type_id NUMBER;
3896 ln_pymt_def_bal_id NUMBER;
3897 ln_ytd_def_bal_id NUMBER;
3898 ln_run_def_bal_id NUMBER;
3899
3900 lv_city_tax_exists VARCHAR2(1);
3901 lv_city_head_tax_exists VARCHAR2(1);
3902
3903 ln_bal_value NUMBER(15,2) := 0;
3904 ln_curr_withheld NUMBER(15,2) := 0;
3905 ln_ytd_withheld NUMBER(15,2) := 0;
3906
3907 lv_procedure_name VARCHAR2(100) := '.populate_city_tax_balances';
3908 lv_error_message VARCHAR2(200);
3909 ln_step NUMBER;
3910
3911
3912 BEGIN
3913 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3914
3915 if p_jurisdiction_tab.count > 0 then
3916 for i in p_jurisdiction_tab.first..
3917 p_jurisdiction_tab.last loop
3918 if p_action_type = 'B' then
3919 lv_city_tax_exists := 'Y';
3920 lv_city_head_tax_exists := 'Y';
3921 else
3922 get_city_tax_info(p_effective_date,
3923 p_jurisdiction_tab(i).emp_jd,
3924 lv_city_tax_exists,
3925 lv_city_head_tax_exists);
3926 end if;
3927
3928 hr_utility.trace('CITY Archiving for Jurisdiction = ' ||
3929 p_jurisdiction_tab(i).emp_jd);
3930
3931 pay_balance_pkg.set_context('JURISDICTION_CODE',
3932 p_jurisdiction_tab(i).emp_jd);
3933
3934 ln_index := get_table_index(p_jurisdiction_tab(i).emp_jd);
3935 hr_utility.trace('Index = '|| ln_index);
3936
3937 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
3938 := p_jurisdiction_tab(i).emp_jd;
3939 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3940 := 'US CITY';
3941
3942 /************************************************************
3943 ** When comparing the City Jurisdiction for
3944 ** Resident/Non Resident flag, only check the State and City
3945 ** Codes i.e. do not check county codes.
3946 ** This is because a City could span multiple counties.
3947 ***********************************************************/
3948 if substr(p_jurisdiction_tab(i).emp_jd,1,2)
3949 || '-000-'
3950 || substr(p_jurisdiction_tab(i).emp_jd,8,4)
3951 = p_resident_state || '-000-'
3952 || p_resident_city then
3953 pay_ac_action_arch.lrr_act_tab(ln_index).act_info30 := 'R';
3954 else
3955 pay_ac_action_arch.lrr_act_tab(ln_index).act_info30 := 'NR';
3956 end if;
3957
3958 if pay_us_action_arch.ltr_city_tax_bal.count > 0 then
3959 for k in pay_us_action_arch.ltr_city_tax_bal.first..
3960 pay_us_action_arch.ltr_city_tax_bal.last loop
3961
3962 lv_balance_name :=
3963 pay_us_action_arch.ltr_city_tax_bal(k).balance_name;
3964 ln_balance_type_id :=
3965 pay_us_action_arch.ltr_city_tax_bal(k).balance_type_id;
3966 ln_pymt_def_bal_id :=
3967 pay_us_action_arch.ltr_city_tax_bal(k).payment_def_bal_id;
3968 ln_ytd_def_bal_id :=
3969 pay_us_action_arch.ltr_city_tax_bal(k).ytd_def_bal_id;
3970 ln_run_def_bal_id :=
3971 pay_us_action_arch.ltr_city_tax_bal(k).asg_run_def_bal_id;
3972
3973 hr_utility.trace('lv_balance_name ='||lv_balance_name);
3974 hr_utility.trace('ln_pymt_def_bal_id='||ln_pymt_def_bal_id);
3975 hr_utility.trace('ln_ytd_def_bal_id ='||ln_ytd_def_bal_id);
3976 hr_utility.trace('ln_run_def_bal_id ='||ln_run_def_bal_id);
3977
3978 if substr(lv_balance_name,1,4) = 'City' then
3979 if lv_city_tax_exists = 'Y' then
3980 if p_action_type in ('U', 'P') then
3981 ln_bal_value := get_balance_value(
3982 p_defined_balance_id => ln_pymt_def_bal_id
3983 ,p_balcall_aaid => p_pymt_balcall_aaid);
3984 if lv_balance_name = 'City Withheld' then
3985 ln_curr_withheld := ln_bal_value;
3986 ln_ytd_withheld := get_balance_value(
3987 p_defined_balance_id => ln_ytd_def_bal_id
3988 ,p_balcall_aaid => p_ytd_balcall_aaid);
3989
3990 update_ytd_withheld(
3991 p_xfr_action_id => p_xfr_action_id
3992 ,p_balance_name => 'City Withheld'
3993 ,p_balance_type_id => ln_balance_type_id
3994 ,p_jurisdiction =>
3995 p_jurisdiction_tab(i).emp_jd
3996 ,p_curr_withheld => ln_curr_withheld
3997 ,p_ytd_withheld => ln_ytd_withheld);
3998 end if;
3999 else
4000 ln_bal_value := get_balance_value(
4001 p_defined_balance_id => ln_run_def_bal_id
4002 ,p_balcall_aaid => p_rqp_action_id);
4003 end if;
4004 hr_utility.trace('ln_bal_value = '|| ln_bal_value);
4005
4006 update_city_values(
4007 p_balance => lv_balance_name
4008 ,p_bal_value => ln_bal_value
4009 ,p_index => ln_index);
4010
4011 end if; --city tax exists
4012 end if; --substr is City
4013
4014 if substr(lv_balance_name,1,4) = 'Head' then
4015 if lv_city_head_tax_exists = 'Y' then
4016 if p_action_type in ( 'U', 'P') then
4017 ln_bal_value := get_balance_value(
4018 p_defined_balance_id => ln_pymt_def_bal_id
4019 ,p_balcall_aaid => p_pymt_balcall_aaid);
4020 if lv_balance_name = 'Head Tax Withheld' then
4021 ln_curr_withheld := ln_bal_value;
4022 ln_ytd_withheld := get_balance_value(
4023 p_defined_balance_id => ln_ytd_def_bal_id
4024 ,p_balcall_aaid => p_ytd_balcall_aaid);
4025 update_ytd_withheld(
4026 p_xfr_action_id => p_xfr_action_id
4027 ,p_balance_name => 'Head Tax Withheld'
4028 ,p_balance_type_id => ln_balance_type_id
4029 ,p_jurisdiction =>
4030 p_jurisdiction_tab(i).emp_jd
4031 ,p_curr_withheld => ln_curr_withheld
4032 ,p_ytd_withheld => ln_ytd_withheld);
4033 end if;
4034 else
4035 ln_bal_value := get_balance_value(
4036 p_defined_balance_id => ln_run_def_bal_id
4037 ,p_balcall_aaid => p_rqp_action_id);
4038 end if;
4039 hr_utility.trace('ln_bal_value = '|| ln_bal_value);
4040
4041 update_city_head_values(
4042 p_balance => lv_balance_name
4043 ,p_bal_value => ln_bal_value
4044 ,p_index => ln_index);
4045 end if; -- city head tax exists
4046 end if; --substr is Head
4047
4048 ln_bal_value := 0;
4049 ln_curr_withheld := 0;
4050 ln_ytd_withheld := 0;
4051
4052 end loop; -- ltr_city_tax_bal
4053 end if; -- ltr_city_tax_bal
4054 end loop; -- of city jurisdiction
4055 end if; -- of city jurisdiction
4056
4057 hr_utility.set_location(gv_package || lv_procedure_name, 100);
4058
4059 EXCEPTION
4060 when others then
4061 lv_error_message := 'Error in ' ||
4062 gv_package || lv_procedure_name;
4063
4064 hr_utility.trace(lv_error_message || '-' || sqlerrm);
4065
4066 lv_error_message :=
4067 pay_emp_action_arch.set_error_message(lv_error_message);
4068
4069 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4070 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4071 hr_utility.raise_error;
4072 END populate_city_tax_balances;
4073
4074 /******************************************************************
4075 Name : populate_school_tax_balances
4076 Purpose : This procedure gets all the School level tax balances
4077 and populates the PL/SQL table.
4078 Arguments :
4079 Notes :
4080 ******************************************************************/
4081 PROCEDURE populate_school_tax_balances(
4082 p_xfr_action_id in number
4083 ,p_pymt_balcall_aaid in number default null
4084 ,p_ytd_balcall_aaid in number default null
4085 ,p_rqp_action_id in number
4086 ,p_action_type in varchar2
4087 ,p_jurisdiction_tab in pay_ac_action_arch.emp_rec_table)
4088 IS
4089 ln_index NUMBER ;
4090 lv_balance_name VARCHAR2(80);
4091 ln_balance_type_id NUMBER;
4092 ln_pymt_def_bal_id NUMBER;
4093 ln_ytd_def_bal_id NUMBER;
4094 ln_run_def_bal_id NUMBER;
4095
4096 lv_emp_school_jd VARCHAR2(15);
4097
4098 ln_bal_value NUMBER(15,2) := 0;
4099 ln_curr_withheld NUMBER(15,2) := 0;
4100 ln_ytd_withheld NUMBER(15,2) := 0;
4101
4102 lv_procedure_name VARCHAR2(100) := '.populate_school_tax_balances';
4103 lv_error_message VARCHAR2(200);
4104
4105 BEGIN
4106 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4107
4108 if p_jurisdiction_tab.count > 0 then
4109 for j in p_jurisdiction_tab.first..
4110 p_jurisdiction_tab.last loop
4111
4112 hr_utility.trace(' Archiving for School Dist = ' ||
4113 p_jurisdiction_tab(j).emp_jd);
4114 hr_utility.trace(' Parent JD for School Dist = ' ||
4115 p_jurisdiction_tab(j).emp_parent_jd);
4116
4117 pay_balance_pkg.set_context('JURISDICTION_CODE',
4118 p_jurisdiction_tab(j).emp_jd);
4119
4120 lv_emp_school_jd := p_jurisdiction_tab(j).emp_jd;
4121 ln_index := get_table_index(p_jurisdiction_tab(j).emp_jd);
4122
4123 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
4124 := p_jurisdiction_tab(j).emp_jd;
4125 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
4126 := 'US SCHOOL DISTRICT';
4127 pay_ac_action_arch.lrr_act_tab(ln_index).act_info30
4128 := p_jurisdiction_tab(j).emp_parent_jd;
4129
4130 if pay_us_action_arch.ltr_schdist_tax_bal.count > 0 then
4131 for k in pay_us_action_arch.ltr_schdist_tax_bal.first..
4132 pay_us_action_arch.ltr_schdist_tax_bal.last loop
4133
4134 lv_balance_name
4135 := pay_us_action_arch.ltr_schdist_tax_bal(k).balance_name;
4136 ln_balance_type_id
4137 := pay_us_action_arch.ltr_schdist_tax_bal(k).balance_type_id;
4138 ln_pymt_def_bal_id
4139 := pay_us_action_arch.ltr_schdist_tax_bal(k).payment_def_bal_id;
4140 ln_ytd_def_bal_id
4141 := pay_us_action_arch.ltr_schdist_tax_bal(k).ytd_def_bal_id;
4142 ln_run_def_bal_id
4143 := pay_us_action_arch.ltr_schdist_tax_bal(k).asg_run_def_bal_id;
4144
4145 hr_utility.trace('lv_balance_name = '||lv_balance_name);
4146 hr_utility.trace('ln_pymt_def_bal_id= '||ln_pymt_def_bal_id);
4147 hr_utility.trace('ln_ytd_def_bal_id = '||ln_ytd_def_bal_id);
4148 hr_utility.trace('ln_run_def_bal_id = '||ln_run_def_bal_id);
4149
4150 if p_action_type in ( 'U', 'P') then
4151 ln_bal_value := get_balance_value(
4152 p_defined_balance_id => ln_pymt_def_bal_id
4153 ,p_balcall_aaid => p_pymt_balcall_aaid);
4154
4155 if lv_balance_name = 'School Withheld' then
4156 ln_curr_withheld := ln_bal_value;
4157 ln_ytd_withheld := get_balance_value(
4158 p_defined_balance_id => ln_ytd_def_bal_id
4159 ,p_balcall_aaid => p_ytd_balcall_aaid);
4160 update_ytd_withheld(
4161 p_xfr_action_id => p_xfr_action_id
4162 ,p_balance_name => 'School Withheld'
4163 ,p_balance_type_id => ln_balance_type_id
4164 ,p_jurisdiction => lv_emp_school_jd
4165 ,p_curr_withheld => ln_curr_withheld
4166 ,p_ytd_withheld => ln_ytd_withheld);
4167 end if;
4168 else
4169 ln_bal_value := get_balance_value(
4170 p_defined_balance_id => ln_run_def_bal_id
4171 ,p_balcall_aaid => p_rqp_action_id);
4172 end if;
4173
4174 hr_utility.trace('ln_bal_value for school dist is '||
4175 ln_bal_value);
4176
4177 update_school_values(
4178 p_balance => lv_balance_name
4179 ,p_bal_value => ln_bal_value
4180 ,p_index => ln_index);
4181
4182 ln_bal_value := 0;
4183 ln_curr_withheld := 0;
4184 ln_ytd_withheld := 0;
4185
4186 end loop; -- ltr_schdist_tax_bal
4187 end if; -- ltr_schdist_tax_bal
4188 end loop; -- of sd jurisdiction
4189 end if; -- of sd jurisdiction
4190 hr_utility.set_location(gv_package || lv_procedure_name, 100);
4191
4192 EXCEPTION
4193 when others then
4194 lv_error_message := 'Error in ' ||
4195 gv_package || lv_procedure_name;
4196
4197 hr_utility.trace(lv_error_message || '-' || sqlerrm);
4198
4199 lv_error_message :=
4200 pay_emp_action_arch.set_error_message(lv_error_message);
4201
4202 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4203 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4204 hr_utility.raise_error;
4205 END populate_school_tax_balances;
4206
4207 /*********************************************************************
4208 Name : check_tax_exists
4209 Purpose : This function checks whether does tax exist for given
4210 assignment_id, tax_unit_id, effective_date, jurisdiction
4211 for State, County and City.
4212 This function calls another function
4213 pay_get_tax_exists_pkg.get_tax_exists
4214 This function returns a 'Y' or 'N'.
4215 Arguments : IN
4216 p_assignment_id number;
4217 p_tax_unit_id number;
4218 p_run_effective_date date;
4219 p_jurisdiction_code varchar2;
4220 Notes :
4221 *********************************************************************/
4222 FUNCTION check_tax_exists(
4223 p_assignment_id number,
4224 p_tax_unit_id number,
4225 p_run_effective_date date,
4226 p_jurisdiction_code varchar2)
4227 RETURN VARCHAR2
4228 IS
4229 cursor c_get_head_tax (cp_jd varchar2
4230 ,cp_date date) is
4231 select nvl(head_tax,'N')
4232 from pay_us_city_tax_info_f
4233 where jurisdiction_code = cp_jd
4234 and cp_date between effective_start_date and effective_end_date;
4235
4236
4237 lv_tax_exists varchar2(80) := 'N';
4238
4239 lv_procedure_name VARCHAR2(100) := '.check_tax_exists';
4240 lv_error_message VARCHAR2(200);
4241 ln_step NUMBER;
4242
4243 TYPE char_tabtype IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
4244 lv_tax_type char_tabtype;
4245
4246 BEGIN
4247
4248 ln_step := 1;
4249 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4250
4251 if length(p_jurisdiction_code) > 8 then
4252 if length(p_jurisdiction_code) = 11 and
4253 substr(p_jurisdiction_code, 4) = '000-0000' then
4254
4255 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4256 ln_step := 2;
4257 lv_tax_type(1) := 'SIT_WK';
4258 lv_tax_type(2) := 'SIT_RS';
4259 lv_tax_type(3) := 'SUI';
4260 lv_tax_type(4) := 'SDI_EE';
4261
4262 for i in 1..4 loop
4263
4264 ln_step := 3;
4265 lv_tax_exists :=
4266 pay_get_tax_exists_pkg.get_tax_exists(p_juri_code => p_jurisdiction_code
4267 ,p_date_earned => p_run_effective_date
4268 ,p_tax_unit_id => p_tax_unit_id
4269 ,p_assign_id => p_assignment_id
4270 ,p_pact_id => NULL /** 5683349*/
4271 ,p_type => lv_tax_type(i)
4272 ,p_call => 'P');
4273 if lv_tax_exists = 'Y' then
4274 exit;
4275 end if;
4276
4277 end loop;
4278
4279 ln_step := 4;
4280 return(lv_tax_exists);
4281
4282 elsif length(p_jurisdiction_code) = 11 and
4283 substr(p_jurisdiction_code,8) = '0000' and
4284 substr(p_jurisdiction_code,4,3) <> '000' then
4285
4286 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4287 ln_step := 5;
4288 lv_tax_type(1) := 'COUNTY_WK';
4289 lv_tax_type(2) := 'COUNTY_RS';
4290
4291 for i in 1..2 loop
4292
4293 ln_step := 6;
4294 lv_tax_exists :=
4295 pay_get_tax_exists_pkg.get_tax_exists(p_juri_code => p_jurisdiction_code
4296 ,p_date_earned => p_run_effective_date
4297 ,p_tax_unit_id => p_tax_unit_id
4298 ,p_assign_id => p_assignment_id
4299 ,p_pact_id => NULL /** 5683349*/
4300 ,p_type => lv_tax_type(i)
4301 ,p_call => 'P');
4302
4303 if lv_tax_exists = 'Y' then
4304 exit;
4305 end if;
4306
4307 end loop;
4308
4309 ln_step := 7;
4310 return(lv_tax_exists);
4311
4312 elsif length(p_jurisdiction_code) = 11 and
4313 substr(p_jurisdiction_code,8) <> '0000' then
4314
4315 hr_utility.set_location(gv_package || lv_procedure_name, 40);
4316 ln_step := 8;
4317 lv_tax_type(1) := 'CITY_WK';
4318 lv_tax_type(2) := 'CITY_RS';
4319 lv_tax_type(3) := 'HT_WK';
4320
4321 for i in 1..3 loop
4322
4323 ln_step := 9;
4324 if i in (1,2) then
4325 lv_tax_exists :=
4326 pay_get_tax_exists_pkg.get_tax_exists(p_juri_code => p_jurisdiction_code
4327 ,p_date_earned => p_run_effective_date
4328 ,p_tax_unit_id => p_tax_unit_id
4329 ,p_assign_id => p_assignment_id
4330 ,p_pact_id => NULL /** 5683349*/
4331 ,p_type => lv_tax_type(i)
4332 ,p_call => 'P');
4333
4334 elsif i = 3 then
4335 open c_get_head_tax(p_jurisdiction_code,p_run_effective_date);
4336 lv_tax_exists := ' ';
4337 fetch c_get_head_tax into lv_tax_exists;
4338 if c_get_head_tax%notfound then
4339 hr_utility.set_location(gv_package||lv_procedure_name,10);
4340 lv_error_message := 'No row in JIT Tables ' ||
4341 'for this Jurisdiction ';
4342 lv_tax_exists := 'N';
4343 --hr_utility.raise_error;
4344 end if;
4345 close c_get_head_tax;
4346
4347 end if;
4348
4349 if lv_tax_exists = 'Y' then
4350 exit;
4351 end if;
4352
4353 end loop;
4354
4355 ln_step := 10;
4356 return(lv_tax_exists);
4357
4358 end if;
4359
4360 elsif length(p_jurisdiction_code) = 8 then
4361
4362 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4363 ln_step := 11;
4364 lv_tax_exists := 'Y';
4365 return(lv_tax_exists);
4366
4367 end if;
4368
4369 hr_utility.set_location(gv_package || lv_procedure_name, 60);
4370 return(lv_tax_exists);
4371
4372 EXCEPTION
4373 when others then
4374 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
4375 gv_package || lv_procedure_name;
4376
4377 hr_utility.trace(lv_error_message || '-' || sqlerrm);
4378
4379 lv_error_message :=
4380 pay_emp_action_arch.set_error_message(lv_error_message);
4381
4382 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4383 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4384 hr_utility.raise_error;
4385
4386 END check_tax_exists;
4387
4388 /******************************************************************
4389 Name : populate_puv_tax_balances
4390 Purpose : This is the procedure which is called for Quick/
4391 Pre-Payments and Reversals.
4392 Arguments :
4393 Notes :
4394 ******************************************************************/
4395 PROCEDURE populate_puv_tax_balances(
4396 p_xfr_action_id in number
4397 ,p_assignment_id in number
4398 ,p_tax_unit_id in number
4399 ,p_pymt_balcall_aaid in number
4400 ,p_ytd_balcall_aaid in number
4401 ,p_rqp_action_id in number
4402 ,p_action_type in varchar2
4403 ,p_start_date in date
4404 ,p_end_date in date
4405 ,p_run_effective_date in date
4406 ,p_sepchk_run_type_id in number default null
4407 ,p_sepchk_flag in varchar2 default null
4408 ,p_resident_jurisdiction out nocopy varchar2
4409 )
4410
4411 IS
4412
4413 cursor c_get_jd (cp_assignment_id number
4414 ,cp_tax_unit_id number) is
4415 select substr(puar.jurisdiction_code,1,2)||'-000-0000'
4416 from pay_us_asg_reporting puar
4417 where puar.assignment_id = cp_assignment_id
4418 and puar.tax_unit_id = cp_tax_unit_id
4419 union
4420 select substr(puar.jurisdiction_code,1,6)||'-0000'
4421 from pay_us_asg_reporting puar
4422 where puar.assignment_id = cp_assignment_id
4423 and puar.tax_unit_id = cp_tax_unit_id
4424 and length(puar.jurisdiction_code) <> 8
4425 union
4426 select puar.jurisdiction_code
4427 from pay_us_asg_reporting puar
4428 where puar.assignment_id = cp_assignment_id
4429 and puar.tax_unit_id = cp_tax_unit_id;
4430
4431 ln_index NUMBER ;
4432
4433 lv_rr_jurisdiction_code VARCHAR2(11);
4434 lv_rr_sd_parent_jd VARCHAR2(11);
4435 lv_state_code VARCHAR2(11);
4436
4437 lv_resident_state VARCHAR2(11);
4438 lv_resident_county VARCHAR2(11);
4439 lv_resident_city VARCHAR2(11);
4440
4441 lv_procedure_name VARCHAR2(100) := '.populate_puv_tax_balances';
4442 lv_error_message VARCHAR2(200);
4443
4444 BEGIN
4445 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4446
4447 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
4448
4449 /*****************************************************************
4450 ** Get Jurisdictions from pay_run_results for all locked actions
4451 ** actions of the U,P,V case and then populating different PLSQL
4452 ** tables for State, County, City, School Dist
4453 *****************************************************************/
4454 hr_utility.trace('c_get_rr_jd parameters are---------');
4455 hr_utility.trace('p_rqp_action_id = '||p_rqp_action_id);
4456 hr_utility.trace('p_assignment_id = '||p_assignment_id);
4457 hr_utility.trace('p_sepchk_run_type_id = '||p_sepchk_run_type_id);
4458 hr_utility.trace('p_run_effective_date = '||p_run_effective_date);
4459 hr_utility.trace('-----------------------------------------');
4460
4461 open c_get_jd( p_assignment_id
4462 ,p_tax_unit_id );
4463
4464 loop
4465 fetch c_get_jd into lv_rr_jurisdiction_code;
4466 exit when c_get_jd%notfound;
4467
4468 if check_tax_exists(p_assignment_id, p_tax_unit_id,
4469 p_run_effective_date, lv_rr_jurisdiction_code) = 'Y'
4470 then
4471
4472 hr_utility.trace('lv_rr_jurisdiction_code = '||lv_rr_jurisdiction_code);
4473
4474 /*************************************************************
4475 ** Populate the PLSQL table emp_state_jd with this jd
4476 *************************************************************/
4477 if length(lv_rr_jurisdiction_code) = 11 and
4478 lv_rr_jurisdiction_code = '00-000-0000' then
4479 -- don't do anything as this should never happen. Added this as
4480 -- a safety check. We don't care about Federal JD
4481 null;
4482 elsif length(lv_rr_jurisdiction_code) = 11 and
4483 substr(lv_rr_jurisdiction_code, 4) = '000-0000' then
4484 ln_index := pay_ac_action_arch.emp_state_jd.count;
4485 pay_ac_action_arch.emp_state_jd(ln_index).emp_jd
4486 := lv_rr_jurisdiction_code;
4487 /*************************************************************
4488 ** Populate the PLSQL table emp_county_jd with this jd
4489 *************************************************************/
4490 elsif length(lv_rr_jurisdiction_code) = 11 and
4491 substr(lv_rr_jurisdiction_code,8) = '0000' and
4492 substr(lv_rr_jurisdiction_code,4,3) <> '000' then
4493 ln_index := pay_ac_action_arch.emp_county_jd.count;
4494 pay_ac_action_arch.emp_county_jd(ln_index).emp_jd
4495 := lv_rr_jurisdiction_code;
4496 /*************************************************************
4497 ** Populate the PLSQL table emp_city_jd with this jd
4498 *************************************************************/
4499 elsif length(lv_rr_jurisdiction_code) = 11 and
4500 substr(lv_rr_jurisdiction_code,8) <> '0000' then
4501 ln_index := pay_ac_action_arch.emp_city_jd.count;
4502 pay_ac_action_arch.emp_city_jd(ln_index).emp_jd := lv_rr_jurisdiction_code;
4503 /*************************************************************
4504 ** Populate the PLSQL table emp_school_jd with this jd
4505 *************************************************************/
4506 elsif length(lv_rr_jurisdiction_code) = 8 then
4507 if substr(lv_rr_jurisdiction_code,1,2) = '39' then
4508 lv_rr_sd_parent_jd
4509 := get_school_parent_jd(
4510 p_assignment_id => p_assignment_id
4511 ,p_school_jurisdiction => lv_rr_jurisdiction_code
4512 ,p_start_date => p_start_date
4513 ,p_end_date => p_end_date);
4514 end if;
4515
4516 ln_index := pay_ac_action_arch.emp_school_jd.count;
4517 pay_ac_action_arch.emp_school_jd(ln_index).emp_jd
4518 := lv_rr_jurisdiction_code;
4519 pay_ac_action_arch.emp_school_jd(ln_index).emp_parent_jd
4520 := lv_rr_sd_parent_jd;
4521 end if;
4522 end if;
4523 end loop;
4524 close c_get_jd;
4525
4526 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4527
4528
4529 /*****************************************************************
4530 ** Get Employee Resident Jurisdiction
4531 *****************************************************************/
4532 get_emp_residence(p_assignment_id => p_assignment_id
4533 ,p_end_date => p_end_date
4534 ,p_run_effective_date => p_run_effective_date
4535 ,p_resident_state_jd => lv_resident_state
4536 ,p_resident_county_jd => lv_resident_county
4537 ,p_resident_city_jd => lv_resident_city);
4538
4539 p_resident_jurisdiction := lv_resident_state || '-' ||
4540 lv_resident_county || '-' ||
4541 lv_resident_city;
4542 /*****************************************************************
4543 ** Federal Information Archiving
4544 *****************************************************************/
4545 populate_federal_tax_balances(p_xfr_action_id => p_xfr_action_id
4546 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
4547 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
4548 ,p_rqp_action_id => p_rqp_action_id
4549 ,p_action_type => p_action_type);
4550 hr_utility.set_location(gv_package || lv_procedure_name, 60);
4551
4552 /*****************************************************************
4553 ** State Information Archiving
4554 *****************************************************************/
4555 populate_state_tax_balances(
4556 p_xfr_action_id => p_xfr_action_id
4557 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
4558 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
4559 ,p_rqp_action_id => p_rqp_action_id
4560 ,p_action_type => p_action_type
4561 ,p_jurisdiction_tab => pay_ac_action_arch.emp_state_jd);
4562 hr_utility.set_location(gv_package || lv_procedure_name, 70);
4563
4564 /*****************************************************************
4565 ** County Information Archiving
4566 *****************************************************************/
4567 populate_county_tax_balances(
4568 p_xfr_action_id => p_xfr_action_id
4569 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
4570 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
4571 ,p_rqp_action_id => p_rqp_action_id
4572 ,p_resident_state => lv_resident_state
4573 ,p_resident_county => lv_resident_county
4574 ,p_resident_city => lv_resident_city
4575 ,p_action_type => p_action_type
4576 ,p_jurisdiction_tab => pay_ac_action_arch.emp_county_jd);
4577
4578 /*****************************************************************
4579 ** City Information Archiving
4580 *****************************************************************/
4581 populate_city_tax_balances(
4582 p_xfr_action_id => p_xfr_action_id
4583 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
4584 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
4585 ,p_rqp_action_id => p_rqp_action_id
4586 ,p_action_type => p_action_type
4587 ,p_resident_state => lv_resident_state
4588 ,p_resident_county => lv_resident_county
4589 ,p_resident_city => lv_resident_city
4590 ,p_effective_date => p_end_date
4591 ,p_jurisdiction_tab => pay_ac_action_arch.emp_city_jd);
4592
4593 /*****************************************************************
4594 ** School District Information Archiving
4595 *****************************************************************/
4596 populate_school_tax_balances(
4597 p_xfr_action_id => p_xfr_action_id
4598 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
4599 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
4600 ,p_rqp_action_id => p_rqp_action_id
4601 ,p_action_type => p_action_type
4602 ,p_jurisdiction_tab => pay_ac_action_arch.emp_school_jd);
4603
4604
4605 EXCEPTION
4606 when others then
4607 lv_error_message := 'Error in ' ||
4608 gv_package || lv_procedure_name;
4609
4610 hr_utility.trace(lv_error_message || '-' || sqlerrm);
4611
4612 lv_error_message :=
4613 pay_emp_action_arch.set_error_message(lv_error_message);
4614
4615 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4616 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4617 hr_utility.raise_error;
4618
4619 END populate_puv_tax_balances;
4620
4621
4622
4623 /******************************************************************
4624 Name : populate_adj_tax_balances
4625 Purpose : This is the procedure which is called for Balance
4626 Adjustments.
4627 Arguments :
4628 Notes : When archiving a balance adjustment, we should not
4629 check if a tax exists but do the balance calls and
4630 archive for a non-zero balance value.
4631 ******************************************************************/
4632 PROCEDURE populate_adj_tax_balances( p_xfr_action_id in number
4633 ,p_assignment_id in number
4634 ,p_tax_unit_id in number
4635 ,p_action_type in varchar2
4636 ,p_start_date in date
4637 ,p_end_date in date
4638 ,p_run_effective_date in date
4639 )
4640 IS
4641
4642 cursor c_get_emp_adjbal(cp_xfr_action_id number) IS
4643 select locked_action_id
4644 from pay_action_interlocks
4645 where locking_action_id = cp_xfr_action_id;
4646
4647 cursor c_get_baladj_jd(cp_baladj_action_id number
4648 ,cp_run_effective_date in date) is
4649 select distinct prr.jurisdiction_code
4650 from pay_run_results prr
4651 where prr.assignment_action_id = cp_baladj_action_id
4652 and length(prr.jurisdiction_code) >= 8
4653 and substr(prr.jurisdiction_code,8,1) <> 'U'
4654 order by prr.jurisdiction_code;
4655
4656 ln_index NUMBER;
4657 ln_baladj_action_id NUMBER;
4658
4659 lv_baladj_jurisdiction_code VARCHAR2(30);
4660
4661 lv_resident_city VARCHAR2(30);
4662 lv_resident_county VARCHAR2(30);
4663 lv_resident_state VARCHAR2(30);
4664
4665 lv_rr_sd_parent_jd VARCHAR2(30);
4666 lv_procedure_name VARCHAR2(100) := '.populate_adj_tax_balances';
4667 lv_error_message VARCHAR2(200);
4668
4669 BEGIN
4670 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4671
4672 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
4673
4674 open c_get_emp_adjbal(p_xfr_action_id);
4675 loop
4676 fetch c_get_emp_adjbal into ln_baladj_action_id;
4677 if c_get_emp_adjbal%notfound then
4678 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4679 exit;
4680 end if;
4681 hr_utility.trace('ln_baladj_action_id = '|| ln_baladj_action_id);
4682
4683 open c_get_baladj_jd(ln_baladj_action_id
4684 ,p_run_effective_date);
4685 loop
4686 fetch c_get_baladj_jd into lv_baladj_jurisdiction_code;
4687 if c_get_baladj_jd%notfound or lv_baladj_jurisdiction_code is null then
4688 hr_utility.set_location(gv_package || '.archive_date', 222);
4689 exit;
4690 end if;
4691 hr_utility.trace('lv_baladj_jurisdiction_code = '||
4692 lv_baladj_jurisdiction_code);
4693
4694 /*************************************************************
4695 ** Populate the PLSQL table emp_state_jd with this jd
4696 *************************************************************/
4697 if length(lv_baladj_jurisdiction_code) = 11 and
4698 substr(lv_baladj_jurisdiction_code, 4) = '000-0000' then
4699 ln_index := pay_ac_action_arch.emp_state_jd.count;
4700 pay_ac_action_arch.emp_state_jd(ln_index).emp_jd
4701 := lv_baladj_jurisdiction_code;
4702 /*************************************************************
4703 ** Populate the PLSQL table emp_county_jd with this jd
4704 *************************************************************/
4705 elsif length(lv_baladj_jurisdiction_code) = 11 and
4706 substr(lv_baladj_jurisdiction_code,8) = '0000' and
4707 substr(lv_baladj_jurisdiction_code,4,3) <> '000' then
4708 ln_index := pay_ac_action_arch.emp_county_jd.count;
4709 pay_ac_action_arch.emp_county_jd(ln_index).emp_jd
4710 := lv_baladj_jurisdiction_code;
4711 /*************************************************************
4712 ** Populate the PLSQL table emp_city_jd with this jd
4713 *************************************************************/
4714 elsif length(lv_baladj_jurisdiction_code) = 11 and
4715 substr(lv_baladj_jurisdiction_code,8) <> '0000' then
4716 ln_index := pay_ac_action_arch.emp_city_jd.count;
4717 pay_ac_action_arch.emp_city_jd(ln_index).emp_jd := lv_baladj_jurisdiction_code;
4718 /*************************************************************
4719 ** Populate the PLSQL table emp_school_jd with this jd
4720 *************************************************************/
4721 elsif length(lv_baladj_jurisdiction_code) = 8 then
4722 if substr(lv_baladj_jurisdiction_code,1,2) = '39' then
4723 lv_rr_sd_parent_jd := get_school_parent_jd(
4724 p_assignment_id => p_assignment_id
4725 ,p_school_jurisdiction => lv_baladj_jurisdiction_code
4726 ,p_start_date => p_start_date
4727 ,p_end_date => p_end_date);
4728 end if;
4729
4730 ln_index := pay_ac_action_arch.emp_school_jd.count;
4731 pay_ac_action_arch.emp_school_jd(ln_index).emp_jd := lv_baladj_jurisdiction_code;
4732 pay_ac_action_arch.emp_school_jd(ln_index).emp_parent_jd := lv_rr_sd_parent_jd;
4733 end if;
4734 end loop;
4735 close c_get_baladj_jd;
4736 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4737
4738 /*****************************************************************
4739 ** Get Employee Resident Jurisdiction
4740 *****************************************************************/
4741 get_emp_residence(p_assignment_id => p_assignment_id
4742 ,p_end_date => p_end_date
4743 ,p_run_effective_date => p_run_effective_date
4744 ,p_resident_state_jd => lv_resident_state
4745 ,p_resident_county_jd => lv_resident_county
4746 ,p_resident_city_jd => lv_resident_city);
4747 hr_utility.set_location(gv_package || lv_procedure_name, 60);
4748 hr_utility.trace('lv_resident_state = ' || lv_resident_state);
4749 hr_utility.trace('lv_resident_county = ' || lv_resident_county);
4750 hr_utility.trace('lv_resident_city = ' || lv_resident_city);
4751
4752 /*****************************************************************
4753 ** Federal Information Archiving
4754 *****************************************************************/
4755 populate_federal_tax_balances(p_xfr_action_id => p_xfr_action_id
4756 ,p_rqp_action_id => ln_baladj_action_id
4757 ,p_action_type => p_action_type);
4758 hr_utility.set_location(gv_package || lv_procedure_name, 70);
4759
4760 /*****************************************************************
4761 ** State Information Archiving
4762 *****************************************************************/
4763 populate_state_tax_balances(
4764 p_xfr_action_id => p_xfr_action_id
4765 ,p_rqp_action_id => ln_baladj_action_id
4766 ,p_action_type => p_action_type
4767 ,p_jurisdiction_tab => pay_ac_action_arch.emp_state_jd);
4768 hr_utility.set_location(gv_package || lv_procedure_name, 80);
4769
4770 /*****************************************************************
4771 ** County Information Archiving
4772 *****************************************************************/
4773 populate_county_tax_balances(
4774 p_xfr_action_id => p_xfr_action_id
4775 ,p_rqp_action_id => ln_baladj_action_id
4776 ,p_resident_state => lv_resident_state
4777 ,p_resident_county => lv_resident_county
4778 ,p_resident_city => lv_resident_city
4779 ,p_action_type => p_action_type
4780 ,p_jurisdiction_tab => pay_ac_action_arch.emp_county_jd);
4781 hr_utility.set_location(gv_package || lv_procedure_name, 90);
4782
4783 /*****************************************************************
4784 ** City Information Archiving
4785 *****************************************************************/
4786 populate_city_tax_balances(
4787 p_xfr_action_id => p_xfr_action_id
4788 ,p_rqp_action_id => ln_baladj_action_id
4789 ,p_action_type => p_action_type
4790 ,p_resident_state => lv_resident_state
4791 ,p_resident_county => lv_resident_county
4792 ,p_resident_city => lv_resident_city
4793 ,p_effective_date => p_end_date
4794 ,p_jurisdiction_tab => pay_ac_action_arch.emp_city_jd);
4795 hr_utility.set_location(gv_package || lv_procedure_name, 100);
4796
4797 /*****************************************************************
4798 ** School District Information Archiving
4799 *****************************************************************/
4800 populate_school_tax_balances(
4801 p_xfr_action_id => p_xfr_action_id
4802 ,p_rqp_action_id => ln_baladj_action_id
4803 ,p_action_type => p_action_type
4804 ,p_jurisdiction_tab => pay_ac_action_arch.emp_school_jd);
4805 hr_utility.set_location(gv_package || lv_procedure_name, 110);
4806
4807 /*****************************************************************
4808 ** Initialize the PL/SQL tables for State, County, City and School
4809 *****************************************************************/
4810 pay_ac_action_arch.emp_state_jd.delete;
4811 pay_ac_action_arch.emp_city_jd.delete;
4812 pay_ac_action_arch.emp_county_jd.delete;
4813 pay_ac_action_arch.emp_school_jd.delete;
4814 hr_utility.set_location(gv_package || lv_procedure_name, 120);
4815
4816 end loop;
4817 close c_get_emp_adjbal;
4818 hr_utility.set_location(gv_package || lv_procedure_name, 130);
4819
4820 hr_utility.trace('Leaving populate_adj_tax_balances');
4821
4822 EXCEPTION
4823 when others then
4824 lv_error_message := 'Error in ' ||
4825 gv_package || lv_procedure_name;
4826
4827 hr_utility.trace(lv_error_message || '-' || sqlerrm);
4828
4829 lv_error_message :=
4830 pay_emp_action_arch.set_error_message(lv_error_message);
4831
4832 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4833 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4834 hr_utility.raise_error;
4835
4836 END populate_adj_tax_balances;
4837
4838
4839 /*********************************************************************
4840 Name : update_employee_information
4841 Purpose : This function updates the Employee Information which is
4842 archived by the global archive procedure.
4843 The only thing which is updated is employee name. The
4844 Global package archvies the full name for the employee.
4845 This procedure will update the name to
4846 First Name[space]Middle Initial[.][space]Last Name
4847 Arguments : IN
4848 p_assignment_action_id number;
4849 Notes :
4850 *********************************************************************/
4851 PROCEDURE update_employee_information(
4852 p_action_context_id in number
4853 ,p_assignment_id in number)
4854 IS
4855 cursor c_get_archive_info(cp_action_context_id in number
4856 ,cp_assignment_id in number) is
4857 select action_information_id, effective_date,
4858 object_version_number
4859 from pay_action_information
4860 where action_context_id = cp_action_context_id
4861 and action_context_type = 'AAP'
4862 and assignment_id = cp_assignment_id
4863 and action_information_category = 'EMPLOYEE DETAILS';
4864
4865 cursor c_get_employee_info(cp_assignment_id in number
4866 ,cp_effective_date in date) is
4867 select ltrim(rtrim(
4868 first_name || ' ' ||
4869 decode(nvl(length(ltrim(rtrim(middle_names))),0), 0, null,
4870 upper(substr(middle_names,1,1)) || '. ' ) ||
4871 pre_name_adjunct || last_name || ' '|| suffix))
4872 from per_all_people_f ppf
4873 where ppf.person_id =
4874 (select person_id from per_all_assignments_f paf
4875 where assignment_id = cp_assignment_id
4876 and cp_effective_date between paf.effective_start_date
4877 and paf.effective_end_date)
4878 and cp_effective_date between ppf.effective_start_date
4879 and ppf.effective_end_date;
4880
4881 ln_action_information_id NUMBER;
4882 ld_effective_date DATE;
4883
4884 lv_employee_name VARCHAR2(300);
4885
4886 ln_ovn NUMBER;
4887 lv_procedure_name VARCHAR2(200) := '.update_employee_information';
4888 lv_error_message VARCHAR2(200);
4889
4890
4891 BEGIN
4892 hr_utility.trace('Action_Context_ID = ' || p_action_context_id);
4893 hr_utility.trace('Asg ID = ' || p_assignment_id);
4894 open c_get_archive_info(p_action_context_id, p_assignment_id);
4895 loop
4896 fetch c_get_archive_info into ln_action_information_id,
4897 ld_effective_date,
4898 ln_ovn;
4899 if c_get_archive_info%notfound then
4900 exit;
4901 end if;
4902
4903 hr_utility.trace('Action_info_id = ' || ln_action_information_id);
4904 hr_utility.trace('ld_eff_date = ' || to_char(ld_effective_date, 'dd-mon-yyyy'));
4905
4906 open c_get_employee_info(p_assignment_id, ld_effective_date);
4907 fetch c_get_employee_info into lv_employee_name;
4908 close c_get_employee_info;
4909
4910 hr_utility.trace('lv_employee_name = *' || lv_employee_name ||'*');
4911
4912 pay_action_information_api.update_action_information
4913 (p_action_information_id => ln_action_information_id
4914 ,p_object_version_number => ln_ovn
4915 ,p_action_information1 => lv_employee_name
4916 );
4917
4918 end loop;
4919 close c_get_archive_info;
4920
4921 EXCEPTION
4922 when others then
4923 lv_error_message := 'Error in ' ||
4924 gv_package || lv_procedure_name;
4925
4926 hr_utility.trace(lv_error_message || '-' || sqlerrm);
4927
4928 lv_error_message :=
4929 pay_emp_action_arch.set_error_message(lv_error_message);
4930
4931 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4932 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4933 hr_utility.raise_error;
4934
4935 END update_employee_information;
4936
4937
4938 /************************************************************
4939 Name : get_employee_withholding_info
4940 Purpose :
4941 Arguments :
4942 Notes :
4943 ************************************************************/
4944 PROCEDURE get_employee_withholding_info(
4945 p_assignment_id in number
4946 ,p_run_effective_date in date
4947 ,p_resident_jurisdiction in varchar2
4948 )
4949 IS
4950 cursor c_emp_fed_info(cp_assignment_id in number
4951 ,cp_run_effective_date in date) is
4952 select fed.filing_status_code,
4953 hl.meaning,
4954 fed.withholding_allowances,
4955 fed.fit_additional_tax,
4956 fed.fit_override_amount,
4957 fed.fit_override_rate
4958 from pay_us_emp_fed_tax_rules_f fed,
4959 hr_lookups hl
4960 where fed.assignment_id = cp_assignment_id
4961 and hl.lookup_code = fed.filing_status_code
4962 and hl.lookup_type = 'US_FIT_FILING_STATUS'
4963 and cp_run_effective_date between effective_start_date
4964 and effective_end_date;
4965
4966 cursor c_get_asg_work_at_home (cp_assignment_id in number
4967 ,p_run_effective_date in date) is
4968 select nvl(paf.work_at_home, 'N')
4969 from per_all_assignments_f paf
4970 where paf.assignment_id = cp_assignment_id
4971 and p_run_effective_date between paf.effective_start_date
4972 and paf.effective_end_date;
4973
4974 cursor c_emp_state_info(cp_assignment_id in number
4975 ,cp_jurisdiction_code in varchar2) is
4976 select pts.time_in_state,
4977 pts.state_name,
4978 pts.jurisdiction_code,
4979 pts.filing_status_code,
4980 pts.meaning,
4981 pts.withholding_allowances,
4982 pts.sit_additional_tax,
4983 pts.sit_override_amount,
4984 pts.sit_override_rate
4985 from pay_us_emp_time_in_state_v pts
4986 where pts.assignment_id = cp_assignment_id
4987 and pts.jurisdiction_code like cp_jurisdiction_code;
4988
4989 cursor c_fnd_session is
4990 select effective_date
4991 from fnd_sessions fs
4992 where session_id = userenv('sessionid');
4993
4994 lv_fit_filing_status_code VARCHAR2(30);
4995 lv_fit_filing_status VARCHAR2(80);
4996 ln_fit_withholding_allowances NUMBER(3);
4997 ln_fit_additional_wa_amount NUMBER(11,2);
4998 ln_fit_override_amount NUMBER(11,2);
4999 ln_fit_override_rate NUMBER(6,3);
5000
5001 lv_asg_work_at_home VARCHAR2(10);
5002
5003 lv_time_in_state VARCHAR2(50);
5004 lv_state_name VARCHAR2(50);
5005 lv_jurisdiction_code VARCHAR2(11);
5006 lv_sit_filing_status_code VARCHAR2(30);
5007 lv_sit_filing_status VARCHAR2(80);
5008 ln_sit_withholding_allowances NUMBER(3);
5009 ln_sit_additional_wa_amount NUMBER(11,2);
5010 ln_sit_override_amount NUMBER(11,2);
5011 ln_sit_override_rate NUMBER(6,3);
5012
5013
5014 ld_session_date DATE;
5015 ln_index NUMBER;
5016
5017 lv_procedure_name VARCHAR2(100) := '.get_employee_withholding_info';
5018 lv_error_message VARCHAR2(200);
5019
5020
5021 BEGIN
5022 hr_utility.set_location(gv_package || lv_procedure_name, 10);
5023 open c_emp_fed_info(p_assignment_id,
5024 p_run_effective_date);
5025 hr_utility.trace('Opened c_emp_fed_info cursor of get_withholding_info');
5026 fetch c_emp_fed_info into lv_fit_filing_status_code,
5027 lv_fit_filing_status,
5028 ln_fit_withholding_allowances,
5029 ln_fit_additional_wa_amount,
5030 ln_fit_override_amount,
5031 ln_fit_override_rate;
5032 if c_emp_fed_info%found then
5033 hr_utility.trace('Going to write get_withholding_info record for fed');
5034
5035 ln_index := pay_ac_action_arch.lrr_act_tab.count;
5036
5037 hr_utility.trace('ln_index in get_withholding_info proc is '
5038 || pay_ac_action_arch.lrr_act_tab.count);
5039
5040 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
5041 := 'US WITHHOLDINGS';
5042 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
5043 := '00-000-0000';
5044 pay_ac_action_arch.lrr_act_tab(ln_index).act_info4
5045 := 'Federal';
5046 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5
5047 := lv_fit_filing_status;
5048 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
5049 := ln_fit_withholding_allowances;
5050 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
5051 := fnd_number.number_to_canonical(
5052 ln_fit_additional_wa_amount);
5053 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
5054 := fnd_number.number_to_canonical(
5055 ln_fit_override_amount);
5056 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
5057 := ln_fit_override_rate;
5058 end if;
5059 close c_emp_fed_info;
5060 hr_utility.set_location(gv_package || lv_procedure_name, 30);
5061
5062
5063 open c_fnd_session;
5064 fetch c_fnd_session into ld_session_date;
5065 if c_fnd_session%notfound then
5066 insert into fnd_sessions (session_id, effective_date) values
5067 (userenv('sessionid'), p_run_effective_date);
5068 else
5069 if ld_session_date <> p_run_effective_date then
5070 update fnd_sessions
5071 set effective_date = p_run_effective_date
5072 where session_id = userenv('sessionid');
5073 end if;
5074 end if;
5075 close c_fnd_session;
5076
5077 open c_get_asg_work_at_home(p_assignment_id
5078 ,p_run_effective_date);
5079 fetch c_get_asg_work_at_home into lv_asg_work_at_home;
5080 close c_get_asg_work_at_home;
5081
5082 hr_utility.set_location(gv_package || lv_procedure_name, 30);
5083 if lv_asg_work_at_home = 'Y' then
5084 open c_emp_state_info(p_assignment_id,
5085 substr(p_resident_jurisdiction,1,2) || '-000-0000');
5086 else
5087 open c_emp_state_info(p_assignment_id, '%');
5088 end if;
5089
5090 loop
5091 fetch c_emp_state_info into lv_time_in_state,
5092 lv_state_name,
5093 lv_jurisdiction_code,
5094 lv_sit_filing_status_code,
5095 lv_sit_filing_status,
5096 ln_sit_withholding_allowances,
5097 ln_sit_additional_wa_amount,
5098 ln_sit_override_amount,
5099 ln_sit_override_rate;
5100
5101 if c_emp_state_info%notfound then
5102 hr_utility.set_location(gv_package || lv_procedure_name, 40);
5103 exit;
5104 end if;
5105
5106 hr_utility.set_location(gv_package || lv_procedure_name, 50);
5107 if ((lv_time_in_state > 0) or
5108 (lv_time_in_state = 0 and
5109 substr(lv_jurisdiction_code, 1,2) = substr(p_resident_jurisdiction,1,2))) then
5110
5111 ln_index := pay_ac_action_arch.lrr_act_tab.count;
5112 hr_utility.trace('ln_index = ' || ln_index);
5113
5114 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
5115 := 'US WITHHOLDINGS';
5116 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
5117 := lv_jurisdiction_code;
5118 pay_ac_action_arch.lrr_act_tab(ln_index).act_info4
5119 := lv_state_name ;
5120 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5
5121 := lv_sit_filing_status;
5122 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
5123 := ln_sit_withholding_allowances;
5124 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
5125 := fnd_number.number_to_canonical(
5126 ln_sit_additional_wa_amount);
5127 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
5128 := fnd_number.number_to_canonical(ln_sit_override_amount);
5129 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
5130 := ln_sit_override_rate;
5131 end if;
5132 end loop;
5133 close c_emp_state_info;
5134
5135 hr_utility.set_location(gv_package || lv_procedure_name, 100);
5136
5137 EXCEPTION
5138 when others then
5139 lv_error_message := 'Error in ' ||
5140 gv_package || lv_procedure_name;
5141
5142 hr_utility.trace(lv_error_message || '-' || sqlerrm);
5143
5144 lv_error_message :=
5145 pay_emp_action_arch.set_error_message(lv_error_message);
5146
5147 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5148 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5149 hr_utility.raise_error;
5150
5151 END get_employee_withholding_info;
5152
5153
5154 /************************************************************
5155 Name : process_actions
5156 Purpose :
5157 Arguments : p_rqp_action_id - For Child actions we pass the
5158 Action ID of Run/Quick Pay
5159 - For Master we pass the Action ID
5160 of Pre Payment Process.
5161 Notes :
5162 ************************************************************/
5163 PROCEDURE process_actions( p_xfr_payroll_action_id in number
5164 ,p_xfr_action_id in number
5165 ,p_pre_pay_action_id in number
5166 ,p_payment_action_id in number
5167 ,p_rqp_action_id in number
5168 ,p_seperate_check_flag in varchar2 default 'N'
5169 ,p_sepcheck_run_type_id in number
5170 ,p_action_type in varchar2
5171 ,p_legislation_code in varchar2
5172 ,p_assignment_id in number
5173 ,p_payroll_id in number
5174 ,p_consolidation_set_id in number
5175 ,p_tax_unit_id in number
5176 ,p_curr_pymt_eff_date in date
5177 ,p_xfr_start_date in date
5178 ,p_xfr_end_date in date
5179 ,p_ppp_source_action_id in number default null
5180 ,p_archive_balance_info in varchar2 default 'Y'
5181 ,p_last_xfr_eff_date out nocopy date
5182 ,p_last_xfr_action_id out nocopy number
5183 )
5184 IS
5185
5186 cursor c_ytd_aaid(cp_prepayment_action_id in number
5187 ,cp_assignment_id in number
5188 ,cp_sepchk_run_type in number) is
5189 select paa.assignment_action_id
5190 from pay_assignment_actions paa,
5191 pay_action_interlocks pai,
5192 pay_payroll_actions ppa
5193 where pai.locking_action_id = cp_prepayment_action_id
5194 and paa.assignment_action_id = pai.locked_action_id
5195 and paa.assignment_id = cp_assignment_id
5196 and ppa.payroll_action_id = paa.payroll_action_id
5197 and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
5198 order by paa.assignment_action_id desc;
5199
5200 cursor c_time_period(cp_run_assignment_action in number) is
5201 select ptp.time_period_id,
5202 --bug 7379102
5203 -- ppa.date_earned,
5204 nvl(ppa.date_earned,ppa.effective_date),
5205 --bug 7379102
5206 ppa.effective_date
5207 from pay_assignment_actions paa,
5208 pay_payroll_actions ppa,
5209 per_time_periods ptp
5210 where paa.assignment_action_id = cp_run_assignment_action
5211 and ppa.payroll_action_id = paa.payroll_action_id
5212 and ptp.payroll_id = ppa.payroll_id
5213 --bug 7379102
5214 -- and ppa.date_earned between ptp.start_date and ptp.end_date;
5215 and nvl(ppa.date_earned,ppa.effective_date) between ptp.start_date and ptp.end_date;
5216 --bug 7379102
5217
5218 cursor c_chk_act_type(cp_last_xfr_act_id number) is
5219 select substr(serial_number,1,1)
5220 from pay_assignment_actions paa
5221 where paa.assignment_action_id = cp_last_xfr_act_id;
5222
5223 lv_pre_xfr_act_type VARCHAR2(80);
5224
5225 ln_run_action_id NUMBER;
5226 ln_ytd_balcall_aaid NUMBER;
5227 ld_run_date_earned DATE;
5228 ld_run_effective_date DATE;
5229
5230 ld_last_xfr_eff_date DATE;
5231 ln_last_xfr_action_id NUMBER;
5232 ld_last_pymt_eff_date DATE;
5233 ln_last_pymt_action_id NUMBER;
5234
5235 ln_time_period_id NUMBER;
5236 lv_resident_jurisdiction VARCHAR2(15);
5237
5238 lv_resident_city VARCHAR2(30); -- Bug 3452149
5239 lv_resident_county VARCHAR2(30);
5240 lv_resident_state VARCHAR2(30);
5241
5242 lv_procedure_name VARCHAR2(100) := '.process_actions';
5243 lv_error_message VARCHAR2(200);
5244
5245 BEGIN
5246 hr_utility.set_location(gv_package || lv_procedure_name, 10);
5247 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
5248 /****************************************************************
5249 ** For Seperate Check we do the YTD balance calls with the Run
5250 ** Action ID. So, we do not need to get the max. action which is
5251 ** not seperate Check.
5252 ** Also, p_ppp_source_action_id is set to null as we want to get
5253 ** all records from pay_pre_payments where source_action_id is
5254 ** is null.
5255 ****************************************************************/
5256 ln_ytd_balcall_aaid := p_payment_action_id;
5257 if p_seperate_check_flag = 'N' and
5258 p_action_type in ('U', 'P') then
5259 hr_utility.set_location(gv_package || lv_procedure_name, 40);
5260 open c_ytd_aaid(p_rqp_action_id,
5261 p_assignment_id,
5262 p_sepcheck_run_type_id);
5263 fetch c_ytd_aaid into ln_ytd_balcall_aaid;
5264 if c_ytd_aaid%notfound then
5265 hr_utility.set_location(gv_package || lv_procedure_name, 50);
5266 hr_utility.raise_error;
5267 end if;
5268 close c_ytd_aaid;
5269 end if;
5270
5271 hr_utility.set_location(gv_package || lv_procedure_name, 60);
5272
5273 open c_time_period(p_payment_action_id);
5274 fetch c_time_period into ln_time_period_id,
5275 ld_run_date_earned,
5276 ld_run_effective_date;
5277 close c_time_period;
5278
5279 hr_utility.set_location(gv_package || lv_procedure_name, 70);
5280 pay_ac_action_arch.get_last_xfr_info(
5281 p_assignment_id => p_assignment_id
5282 ,p_curr_effective_date => p_xfr_end_date
5283 ,p_action_info_category => 'EMPLOYEE DETAILS'
5284 ,p_xfr_action_id => p_xfr_action_id
5285 ,p_sepchk_flag => p_seperate_check_flag
5286 ,p_last_xfr_eff_date => ld_last_xfr_eff_date
5287 ,p_last_xfr_action_id => ln_last_xfr_action_id
5288 );
5289
5290 if ld_last_xfr_eff_date is not null then
5291 if gv_act_param_val is not null then
5292 if gv_act_param_val = 'Y'
5293 then
5294 ld_last_xfr_eff_date := NULL;
5295 elsif fnd_date.canonical_to_date(gv_act_param_val) = p_xfr_end_date
5296 then
5297 ld_last_xfr_eff_date := NULL;
5298 end if;
5299 end if;
5300 end if;
5301
5302 -- This is no longer going to be called as ln_last_xfr_action_id
5303 -- will never be a archive for balance adjustment
5304 if ld_last_xfr_eff_date is not null then
5305 open c_chk_act_type(ln_last_xfr_action_id);
5306 fetch c_chk_act_type into lv_pre_xfr_act_type;
5307 close c_chk_act_type;
5308
5309 if lv_pre_xfr_act_type = 'B' then
5310 ld_last_xfr_eff_date := NULL;
5311 end if;
5312 end if;
5313
5314 p_last_xfr_eff_date := ld_last_xfr_eff_date;
5315 p_last_xfr_action_id := ln_last_xfr_action_id;
5316
5317 hr_utility.trace('p_xfr_payroll_action_id= '|| p_xfr_payroll_action_id);
5318 hr_utility.trace('p_xfr_action_id = ' || p_xfr_action_id);
5319 hr_utility.trace('p_seperate_check_flag = ' || p_seperate_check_flag);
5320 hr_utility.trace('p_action_type = ' || p_action_type);
5321 hr_utility.trace('p_pre_pay_action_id = ' || p_pre_pay_action_id);
5322 hr_utility.trace('p_payment_action_id = ' || p_payment_action_id);
5323 hr_utility.trace('p_rqp_action_id = ' || p_rqp_action_id);
5324 hr_utility.trace('p_sepcheck_run_type_id = '|| p_sepcheck_run_type_id);
5325 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
5326 hr_utility.trace('p_xfr_start_date = ' || p_xfr_start_date );
5327 hr_utility.trace('p_xfr_end_date = ' || p_xfr_end_date );
5328 hr_utility.trace('p_curr_pymt_eff_date = ' || p_curr_pymt_eff_date);
5329 hr_utility.trace('ld_run_effective_date = ' || ld_run_effective_date);
5330 hr_utility.trace('ln_ytd_balcall_aaid = ' || ln_ytd_balcall_aaid);
5331 hr_utility.trace('p_ppp_source_action_id = '|| p_ppp_source_action_id);
5332 hr_utility.trace('ld_run_date_earned = ' || ld_run_date_earned);
5333 hr_utility.trace('ld_last_xfr_eff_date = ' || ld_last_xfr_eff_date);
5334 hr_utility.trace('ln_last_xfr_action_id = ' || ln_last_xfr_action_id);
5335
5336 pay_ac_action_arch.initialization_process;
5337
5338 /*********************************************************************
5339 ** If p_archive_balance_info is not Y then it mean that the assignment
5340 ** does not have any Gross or Payments in the Run. In this case, we
5341 ** only archive the employee level information and also move forward
5342 ** elements from previous archive.
5343 ** The only issue with the approach is if the first archiver run
5344 ** for the employee has no Gross or Payments Balance. In this case
5345 ** any non-recurring processed from 1st Jan till date willl not be
5346 ** archived.
5347 *********************************************************************/
5348 if p_archive_balance_info = 'Y' then
5349 populate_puv_tax_balances(
5350 p_xfr_action_id => p_xfr_action_id
5351 ,p_assignment_id => p_assignment_id
5352 ,p_pymt_balcall_aaid => p_payment_action_id
5353 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
5354 ,p_tax_unit_id => p_tax_unit_id
5355 ,p_action_type => p_action_type
5356 ,p_rqp_action_id => p_rqp_action_id
5357 ,p_start_date => p_xfr_start_date
5358 ,p_end_date => p_xfr_end_date
5359 ,p_run_effective_date => ld_run_effective_date
5360 ,p_sepchk_run_type_id => p_sepcheck_run_type_id
5361 ,p_sepchk_flag => p_seperate_check_flag
5362 ,p_resident_jurisdiction => lv_resident_jurisdiction
5363 );
5364 hr_utility.set_location(gv_package || lv_procedure_name, 90);
5365
5366 /******************************************************************
5367 ** For seperate check cases, the ld_last_xfr_eff_date is never null
5368 ** as the master is always processed before the child actions. The
5369 ** master data is already in the archive table and as it is in the
5370 ** same session the process will always go to the else statement
5371 ******************************************************************/
5372 if ld_last_xfr_eff_date is null then
5373 hr_utility.set_location(gv_package || lv_procedure_name, 100);
5374 pay_ac_action_arch.first_time_process(
5375 p_xfr_action_id => p_xfr_action_id
5376 ,p_assignment_id => p_assignment_id
5377 ,p_curr_pymt_action_id => p_rqp_action_id
5378 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
5379 ,p_curr_eff_date => p_xfr_end_date
5380 ,p_tax_unit_id => p_tax_unit_id
5381 ,p_pymt_balcall_aaid => p_payment_action_id
5382 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
5383 ,p_sepchk_run_type_id => p_sepcheck_run_type_id
5384 ,p_sepchk_flag => p_seperate_check_flag
5385 ,p_legislation_code => p_legislation_code
5386 );
5387
5388 else
5389 hr_utility.set_location(gv_package || lv_procedure_name, 110);
5390 pay_ac_action_arch.get_current_elements(
5391 p_xfr_action_id => p_xfr_action_id
5392 ,p_curr_pymt_action_id => p_rqp_action_id
5393 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
5394 ,p_assignment_id => p_assignment_id
5395 ,p_tax_unit_id => p_tax_unit_id
5396 ,p_pymt_balcall_aaid => p_payment_action_id
5397 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
5398 ,p_sepchk_run_type_id => p_sepcheck_run_type_id
5399 ,p_sepchk_flag => p_seperate_check_flag
5400 ,p_legislation_code => p_legislation_code);
5401
5402 end if;
5403
5404 hr_utility.set_location(gv_package || lv_procedure_name, 120);
5405
5406 else
5407
5408 /*****************************************************************
5409 ** Get Employee Resident Jurisdiction seperately as we do not
5410 ** call populate_puv_tax_balances which returns
5411 ** lv_resident_jurisdiction. This value is used when archiving
5412 ** W4 Information.
5413 *****************************************************************/
5414 hr_utility.set_location(gv_package || lv_procedure_name, 130);
5415 get_emp_residence(p_assignment_id => p_assignment_id
5416 ,p_end_date => p_xfr_end_date
5417 ,p_run_effective_date => ld_run_effective_date
5418 ,p_resident_state_jd => lv_resident_state
5419 ,p_resident_county_jd => lv_resident_county
5420 ,p_resident_city_jd => lv_resident_city);
5421
5422 lv_resident_jurisdiction := lv_resident_state || '-' ||
5423 lv_resident_county || '-' ||
5424 lv_resident_city;
5425
5426 end if; /* p_archive_balance_info = 'Y' */
5427
5428 hr_utility.set_location(gv_package || lv_procedure_name, 135);
5429 pay_ac_action_arch.get_xfr_elements(
5430 p_xfr_action_id => p_xfr_action_id
5431 ,p_last_xfr_action_id => ln_last_xfr_action_id
5432 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
5433 ,p_pymt_eff_date => p_curr_pymt_eff_date
5434 ,p_legislation_code => p_legislation_code
5435 ,p_sepchk_flag => p_seperate_check_flag
5436 ,p_assignment_id => p_assignment_id);
5437
5438 hr_utility.set_location(gv_package || lv_procedure_name, 140);
5439 pay_ac_action_arch.get_last_pymt_info(
5440 p_assignment_id => p_assignment_id
5441 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
5442 ,p_last_pymt_eff_date => ld_last_pymt_eff_date
5443 ,p_last_pymt_action_id => ln_last_pymt_action_id);
5444
5445 if ld_last_xfr_eff_date <> ld_last_pymt_eff_date then
5446 hr_utility.set_location(gv_package || lv_procedure_name, 145);
5447 pay_ac_action_arch.get_missing_xfr_info(
5448 p_xfr_action_id => p_xfr_action_id
5449 ,p_tax_unit_id => p_tax_unit_id
5450 ,p_assignment_id => p_assignment_id
5451 ,p_last_pymt_action_id => ln_last_pymt_action_id
5452 ,p_last_pymt_eff_date => ld_last_pymt_eff_date
5453 ,p_last_xfr_eff_date => ld_last_xfr_eff_date
5454 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
5455 ,p_pymt_eff_date => p_curr_pymt_eff_date
5456 ,p_legislation_code => p_legislation_code);
5457 end if;
5458
5459
5460 hr_utility.set_location(gv_package || lv_procedure_name, 155);
5461 pay_emp_action_arch.get_personal_information(
5462 p_payroll_action_id => p_xfr_payroll_action_id
5463 ,p_assactid => p_xfr_action_id
5464 ,p_assignment_id => p_assignment_id
5465 ,p_curr_pymt_ass_act_id => p_pre_pay_action_id
5466 ,p_curr_eff_date => p_xfr_end_date
5467 ,p_date_earned => ld_run_date_earned
5468 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
5469 ,p_tax_unit_id => p_tax_unit_id
5470 ,p_time_period_id => ln_time_period_id
5471 ,p_ppp_source_action_id => p_ppp_source_action_id
5472 ,p_run_action_id => p_payment_action_id
5473 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
5474 );
5475
5476 hr_utility.set_location(gv_package || lv_procedure_name, 160);
5477 get_employee_withholding_info(
5478 p_assignment_id => p_assignment_id
5479 ,p_run_effective_date => ld_run_effective_date
5480 ,p_resident_jurisdiction => lv_resident_jurisdiction);
5481
5482 if p_seperate_check_flag = 'N' then
5483 hr_utility.set_location(gv_package || lv_procedure_name, 170);
5484 -- Archive element processed in balance adjustment. This only
5485 -- needs to be done for master action as once the element is
5486 -- in archive, it will be carried forward.
5487 pay_ac_action_arch.process_baladj_elements(
5488 p_assignment_id => p_assignment_id
5489 ,p_xfr_action_id => p_xfr_action_id
5490 ,p_last_xfr_action_id => ln_last_xfr_action_id
5491 ,p_curr_pymt_action_id => p_rqp_action_id
5492 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
5493 ,p_ytd_balcall_aaid => ln_ytd_balcall_aaid
5494 ,p_sepchk_flag => p_seperate_check_flag
5495 ,p_sepchk_run_type_id => p_sepcheck_run_type_id
5496 ,p_payroll_id => p_payroll_id
5497 ,p_consolidation_set_id => p_consolidation_set_id
5498 ,p_legislation_code => p_legislation_code
5499 ,p_tax_unit_id => p_tax_unit_id);
5500
5501 hr_utility.set_location(gv_package || lv_procedure_name, 175);
5502
5503 open c_ytd_aaid(p_rqp_action_id,
5504 p_assignment_id,
5505 p_sepcheck_run_type_id);
5506 loop
5507 fetch c_ytd_aaid into ln_run_action_id;
5508 if c_ytd_aaid%notfound then
5509 hr_utility.set_location(gv_package || lv_procedure_name, 180);
5510 exit;
5511 end if;
5512
5513 hr_utility.set_location(gv_package || lv_procedure_name, 190);
5514 populate_emp_hours_by_rate(
5515 p_action_context_id => p_xfr_action_id
5516 ,p_assignment_id => p_assignment_id
5517 ,p_run_action_id => ln_run_action_id);
5518
5519 end loop;
5520 close c_ytd_aaid;
5521 else
5522 hr_utility.set_location(gv_package || lv_procedure_name, 200);
5523 populate_emp_hours_by_rate(
5524 p_action_context_id => p_xfr_action_id
5525 ,p_assignment_id => p_assignment_id
5526 ,p_run_action_id => p_payment_action_id);
5527 end if;
5528
5529 hr_utility.set_location(gv_package || lv_procedure_name, 205);
5530 pay_ac_action_arch.populate_summary(
5531 p_xfr_action_id => p_xfr_action_id);
5532 change_processing_priority;
5533
5534 hr_utility.set_location(gv_package || lv_procedure_name, 210);
5535 pay_emp_action_arch.insert_rows_thro_api_process(
5536 p_action_context_id => p_xfr_action_id
5537 ,p_action_context_type=> 'AAP'
5538 ,p_assignment_id => p_assignment_id
5539 ,p_tax_unit_id => p_tax_unit_id
5540 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
5541 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
5542 );
5543
5544 hr_utility.set_location(gv_package || lv_procedure_name, 220);
5545 update_employee_information(
5546 p_action_context_id => p_xfr_action_id
5547 ,p_assignment_id => p_assignment_id);
5548
5549 hr_utility.set_location(gv_package || lv_procedure_name, 250);
5550
5551 EXCEPTION
5552 when others then
5553 lv_error_message := 'Error in ' ||
5554 gv_package || lv_procedure_name;
5555
5556 hr_utility.trace(lv_error_message || '-' || sqlerrm);
5557
5558 lv_error_message :=
5559 pay_emp_action_arch.set_error_message(lv_error_message);
5560
5561 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
5562 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
5563 hr_utility.raise_error;
5564
5565 END process_actions;
5566
5567 /************************************************************
5568 Name : action_archive_data
5569 Purpose : This procedure Archives data which are used in
5570 Tax Remittance Archiver, Payslip, Check Writer,
5571 Deposit Advice modules.
5572 Arguments :
5573 Notes :
5574 ************************************************************/
5575 PROCEDURE action_archive_data(p_xfr_action_id in number
5576 ,p_effective_date in date)
5577 IS
5578
5579 cursor c_xfr_info (cp_assignment_action in number) is
5580 select paa.payroll_action_id,
5581 paa.assignment_action_id,
5582 paa.assignment_id,
5583 paa.tax_unit_id,
5584 paa.serial_number,
5585 paa.chunk_number
5586 from pay_assignment_actions paa
5587 where paa.assignment_action_id = cp_assignment_action;
5588
5589 cursor c_legislation (cp_business_group in number) is
5590 select org_information9
5591 from hr_organization_information
5592 where org_information_context = 'Business Group Information'
5593 and organization_id = cp_business_group;
5594
5595 cursor c_sepchk_ryn_type is
5596 select prt.run_type_id
5597 from pay_run_types_f prt
5598 where prt.shortname = 'SEPCHECK'
5599 and prt.legislation_code = 'US';
5600
5601 cursor c_assignment_run (cp_prepayment_action_id in number) is
5602 select distinct paa.assignment_id
5603 from pay_action_interlocks pai,
5604 pay_assignment_actions paa,
5605 pay_payroll_actions ppa
5606 where pai.locking_action_id = cp_prepayment_action_id
5607 and paa.assignment_action_id = pai.locked_action_id
5608 and ppa.payroll_action_id = paa.payroll_action_id
5609 and ppa.action_type in ('R', 'Q', 'B')
5610 and ((ppa.run_type_id is null and paa.source_action_id is null) or
5611 (ppa.run_type_id is not null and paa.source_action_id is not null))
5612 and paa.action_status = 'C';
5613
5614 cursor c_master_run_action(
5615 cp_prepayment_action_id in number,
5616 cp_assignment_id in number) is
5617 select paa.assignment_action_id, paa.payroll_action_id,
5618 ppa.action_type
5619 from pay_payroll_actions ppa,
5620 pay_assignment_actions paa,
5621 pay_action_interlocks pai
5622 where pai.locking_action_id = cp_prepayment_action_id
5623 and pai.locked_action_id = paa.assignment_action_id
5624 and paa.assignment_id = cp_assignment_id
5625 and paa.source_action_id is null
5626 and ppa.payroll_action_id = paa.payroll_action_id
5627 order by paa.assignment_action_id desc;
5628
5629 cursor c_pymt_eff_date(cp_prepayment_action_id in number) is
5630 select ppa.effective_date
5631 from pay_payroll_actions ppa,
5632 pay_assignment_actions paa
5633 where ppa.payroll_action_id = paa.payroll_action_id
5634 and paa.assignment_action_id = cp_prepayment_action_id;
5635
5636 cursor c_check_pay_action( cp_payroll_action_id in number) is
5637 select count(*)
5638 from pay_action_information
5639 where action_context_id = cp_payroll_action_id
5640 and action_context_type = 'PA';
5641
5642 cursor c_payment_info(cp_prepay_action_id number) is
5643 select distinct
5644 assignment_id
5645 ,tax_unit_id
5646 ,nvl(source_action_id,-999)
5647 ,assignment_action_id
5648 from pay_payment_information_v
5649 where assignment_action_id = cp_prepay_action_id
5650 order by 3,1,2;
5651
5652 cursor c_run_aa_id(cp_pp_asg_act_id number
5653 ,cp_assignment_id number
5654 ,cp_tax_unit_id number) is
5655 select paa.assignment_action_id
5656 ,paa.source_action_id
5657 from pay_assignment_actions paa
5658 ,pay_action_interlocks pai
5659 where pai.locking_action_id = cp_pp_asg_act_id
5660 and paa.assignment_action_id = pai.locked_action_id
5661 and paa.assignment_id = cp_assignment_id
5662 and paa.tax_unit_id = cp_tax_unit_id
5663 and paa.source_action_id is not null
5664 and not exists ( select 1
5665 from pay_run_types_f prt
5666 where prt.legislation_code = 'US'
5667 and prt.run_type_id = paa.run_type_id
5668 and prt.run_method in ('C', 'S'))
5669 order by paa.action_sequence desc;
5670
5671 cursor c_get_prepay_aaid_for_sepchk( cp_asg_act_id number,
5672 cp_source_act_id number ) is
5673 select ppp.assignment_action_id
5674 from pay_assignment_actions paa
5675 ,pay_pre_payments ppp
5676 where ( paa.assignment_action_id = cp_asg_act_id OR
5677 paa.source_action_id = cp_asg_act_id )
5678 and ppp.assignment_action_id = paa.assignment_action_id
5679 and ppp.source_action_id = cp_source_act_id;
5680
5681
5682 /* Following cursor is changed for performance issue Bug# 7418142 */
5683
5684 cursor c_get_unproc_asg(cp_assignment_id in number,
5685 cp_effective_date in date,
5686 cp_payroll_id in number,
5687 cp_xfr_action_id in number,
5688 cp_prepay_action_id in number) is
5689 select /*+ ORDERED */
5690 paf1.assignment_id,
5691 paa.assignment_action_id
5692 from per_all_assignments_f paf1
5693 ,pay_assignment_actions paa
5694 ,pay_payroll_actions ppa
5695 where paf1.person_id = (select /*+ PUSH_SUBQ */ person_id
5696 from per_all_assignments_f START_ASS
5697 where START_ASS.assignment_id = cp_assignment_id
5698 and rownum = 1)
5699 and paf1.effective_end_date >= trunc(cp_effective_date,'Y')
5700 and paf1.effective_start_date <= cp_effective_date
5701 and paa.assignment_id = paf1.assignment_id
5702 and paa.payroll_action_id = ppa.payroll_action_id
5703 and ppa.payroll_id = cp_payroll_id
5704 and ppa.action_type in ('Q', 'R', 'I','B')
5705 and not exists (select 'x'
5706 from pay_action_information pai
5707 where pai.action_context_id = cp_xfr_action_id
5708 and pai.assignment_id = paf1.assignment_id)
5709 and not exists (select 1
5710 from pay_payment_information_v ppi
5711 where ppi.assignment_action_id = cp_prepay_action_id
5712 and ppi.assignment_id = paf1.assignment_id
5713 and ppi.source_action_id is null)
5714 order by paf1.assignment_id,
5715 paa.action_sequence desc;
5716
5717
5718
5719 cursor c_prev_run_information(cp_assignment_id in number
5720 ,cp_xfr_action_id in number
5721 ,cp_effective_date in date) is
5722 select max(pai.effective_date)
5723 from pay_action_information pai
5724 where pai.action_context_type = 'AAP'
5725 and pai.assignment_id = cp_assignment_id
5726 and pai.action_information_category in ('AC EARNINGS', 'AC DEDUCTIONS')
5727 and pai.action_context_id <> cp_xfr_action_id
5728 and pai.effective_date <= cp_effective_date;
5729
5730 cursor c_run_aa_id_bal_adj(cp_pp_asg_act_id number
5731 ,cp_assignment_id number
5732 ,cp_tax_unit_id number) is
5733 select paa.assignment_action_id
5734 ,paa.source_action_id
5735 from pay_assignment_actions paa
5736 ,pay_action_interlocks pai
5737 where pai.locking_action_id = cp_pp_asg_act_id
5738 and paa.assignment_action_id = pai.locked_action_id
5739 and paa.assignment_id = cp_assignment_id
5740 and paa.tax_unit_id = cp_tax_unit_id
5741 order by paa.action_sequence desc;
5742
5743 cursor c_all_runs(cp_pp_asg_act_id in number
5744 ,cp_assignment_id in number
5745 ,cp_tax_unit_id in number
5746 ,cp_sepchk_run_type in number) is
5747 select paa.assignment_action_id
5748 from pay_assignment_actions paa,
5749 pay_action_interlocks pai
5750 where pai.locking_action_id = cp_pp_asg_act_id
5751 and paa.assignment_action_id = pai.locked_action_id
5752 and paa.assignment_id = cp_assignment_id
5753 and paa.tax_unit_id = cp_tax_unit_id
5754 and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
5755 and not exists ( select 1
5756 from pay_run_types_f prt
5757 where prt.legislation_code = 'US'
5758 and prt.run_type_id = nvl(paa.run_type_id,0)
5759 and prt.run_method = 'C' );
5760
5761 ld_curr_pymt_eff_date DATE;
5762 ln_sepchk_run_type_id NUMBER;
5763 lv_legislation_code VARCHAR2(2);
5764
5765 ln_xfr_master_action_id NUMBER;
5766
5767 ln_tax_unit_id NUMBER;
5768 ln_xfr_payroll_action_id NUMBER; /* of current xfr */
5769 ln_xfr_assignment_id NUMBER; -- Bug 3452149
5770 ln_assignment_id NUMBER;
5771 ln_chunk_number NUMBER;
5772
5773 lv_xfr_master_serial_number VARCHAR2(30);
5774 lv_master_action_type VARCHAR2(1);
5775 lv_master_sepcheck_flag VARCHAR2(1);
5776 ln_asg_action_id NUMBER;
5777
5778 ln_master_run_action_id NUMBER;
5779 ln_master_run_pact_id NUMBER;
5780 lv_master_run_action_type VARCHAR2(1);
5781
5782 ln_pymt_balcall_aaid NUMBER;
5783 ln_pay_action_count NUMBER;
5784
5785 ld_start_date DATE;
5786 ld_end_date DATE;
5787 ln_business_group_id NUMBER;
5788 ln_cons_set_id NUMBER;
5789 ln_payroll_id NUMBER;
5790
5791 lv_resident_jurisdiction VARCHAR2(30);
5792
5793 lv_procedure_name VARCHAR2(100) := '.action_archive_data';
5794 lv_error_message VARCHAR2(200);
5795 ln_step NUMBER;
5796
5797 ln_np_asg_id NUMBER;
5798 ln_np_asg_action_id NUMBER;
5799 ln_np_prev_asg_id NUMBER := '-1';
5800 ld_np_last_xfr_eff_date DATE;
5801
5802 ln_source_action_id NUMBER;
5803 ln_child_xfr_action_id NUMBER;
5804 ln_run_aa_id NUMBER;
5805 ln_run_source_action_id NUMBER;
5806 ln_rqp_action_id NUMBER;
5807 ln_ppp_source_action_id NUMBER;
5808 ln_master_run_aa_id NUMBER;
5809 ln_earnings NUMBER;
5810 lv_serial_number VARCHAR2(30);
5811
5812 ln_run_qp_found NUMBER;
5813 ln_all_run_asg_act_id NUMBER;
5814
5815 lv_archive_balance_info VARCHAR2(1) := 'Y'; -- Bug 3452149
5816 ld_last_xfr_eff_date DATE;
5817 ln_last_xfr_action_id NUMBER;
5818
5819 BEGIN
5820 pay_emp_action_arch.gv_error_message := NULL;
5821 hr_utility.set_location(gv_package || lv_procedure_name, 10);
5822 ln_step := 1;
5823 open c_xfr_info (p_xfr_action_id);
5824 fetch c_xfr_info into ln_xfr_payroll_action_id,
5825 ln_xfr_master_action_id,
5826 ln_xfr_assignment_id,
5827 ln_tax_unit_id,
5828 lv_xfr_master_serial_number,
5829 ln_chunk_number;
5830 close c_xfr_info;
5831
5832 ln_step := 2;
5833 get_payroll_action_info(p_payroll_action_id => ln_xfr_payroll_action_id
5834 ,p_start_date => ld_start_date
5835 ,p_end_date => ld_end_date
5836 ,p_business_group_id => ln_business_group_id
5837 ,p_cons_set_id => ln_cons_set_id
5838 ,p_payroll_id => ln_payroll_id);
5839
5840 hr_utility.set_location(gv_package || lv_procedure_name, 15);
5841
5842 ln_step := 205;
5843 pay_emp_action_arch.gv_multi_payroll_pymt
5844 := pay_emp_action_arch.get_multi_assignment_flag(
5845 p_payroll_id => ln_payroll_id
5846 ,p_effective_date => ld_end_date);
5847
5848 hr_utility.trace('pay_emp_action_arch.gv_multi_payroll_pymt = ' ||
5849 pay_emp_action_arch.gv_multi_payroll_pymt);
5850
5851 ln_step := 3;
5852 open c_legislation (ln_business_group_id);
5853 fetch c_legislation into lv_legislation_code ;
5854 if c_legislation%notfound then
5855 hr_utility.trace('Business Group for Interface Process Not Found');
5856 hr_utility.raise_error;
5857 end if;
5858 close c_legislation;
5859 hr_utility.trace('lv_legislation_code '||lv_legislation_code);
5860
5861 ln_step := 4;
5862 open c_sepchk_ryn_type;
5863 fetch c_sepchk_ryn_type into ln_sepchk_run_type_id;
5864 if c_sepchk_ryn_type%notfound then
5865 hr_utility.set_location(gv_package || lv_procedure_name, 20);
5866 hr_utility.raise_error;
5867 end if;
5868 close c_sepchk_ryn_type;
5869
5870 -- process the master_action
5871 lv_master_action_type := substr(lv_xfr_master_serial_number,1,1);
5872 -- Always N for Master Assignment Action
5873 lv_master_sepcheck_flag := substr(lv_xfr_master_serial_number,2,1);
5874 -- Assignment Action of Quick Pay Pre Payment, Pre Payment, Reversal
5875 ln_asg_action_id := substr(lv_xfr_master_serial_number,3);
5876
5877 ln_step := 5;
5878 open c_pymt_eff_date(ln_asg_action_id);
5879 fetch c_pymt_eff_date into ld_curr_pymt_eff_date;
5880 if c_pymt_eff_date%notfound then
5881 hr_utility.trace('PayrollAction for InterfaceProcess NotFound');
5882 hr_utility.raise_error;
5883 end if;
5884 close c_pymt_eff_date;
5885
5886 hr_utility.trace('End Date=' || to_char(ld_end_date, 'dd-mon-yyyy'));
5887 hr_utility.trace('Start Date='||to_char(ld_start_date, 'dd-mon-yyyy'));
5888 hr_utility.trace('Business Group Id='||to_char(ln_business_group_id));
5889 hr_utility.trace('Serial Number='||lv_xfr_master_serial_number);
5890 hr_utility.trace('ln_xfr_payroll_action_id ='||to_char(ln_xfr_payroll_action_id));
5891
5892 ln_step := 6;
5893 if lv_master_action_type in ( 'P','U') then
5894 /************************************************************
5895 ** For Master Pre Payment Action get the distinct
5896 ** Assignment_ID's and archive the data seperately for
5897 ** all the assigments.
5898 *************************************************************/
5899 ln_step := 7;
5900 open c_payment_info(ln_asg_action_id);
5901 loop
5902
5903 fetch c_payment_info into ln_assignment_id
5904 ,ln_tax_unit_id
5905 ,ln_source_action_id
5906 ,ln_asg_action_id;
5907 exit when c_payment_info%notfound;
5908
5909 hr_utility.trace('archive_data:payment_info:ln_asg_action_id = ' ||
5910 ln_asg_action_id );
5911 hr_utility.trace('archive_data:payment_info:ln_assignment_id = ' ||
5912 ln_assignment_id );
5913 hr_utility.trace('archive_data:payment_info:ln_tax_unit_id = ' ||
5914 ln_tax_unit_id );
5915 hr_utility.trace('archive_data:payment_info:ln_source_action_id = ' ||
5916 ln_source_action_id );
5917
5918 ln_step := 8;
5919
5920 if ln_source_action_id = -999 then
5921 ln_step := 9;
5922 lv_master_sepcheck_flag := 'N';
5923 ln_master_run_aa_id := NULL;
5924 ln_master_run_aa_id := NULL;
5925 ln_run_qp_found := 0;
5926
5927
5928 /********************************************************
5929 ** Getting Run Assignment Action Id for normal cheque.
5930 ********************************************************/
5931 open c_run_aa_id(ln_asg_action_id
5932 ,ln_assignment_id
5933 ,ln_tax_unit_id);
5934 fetch c_run_aa_id into ln_run_aa_id, ln_run_source_action_id;
5935 if c_run_aa_id%found then
5936 ln_run_qp_found := 1;
5937 end if;
5938 close c_run_aa_id;
5939
5940 ln_step := 10;
5941 hr_utility.trace('GRE ln_run_aa_id = ' || ln_run_aa_id);
5942
5943 if ln_run_source_action_id is not null then
5944 ln_master_run_aa_id := ln_run_source_action_id; -- Normal Chk
5945 else
5946 if ln_run_qp_found = 0 then
5947 /* Balance Adjustment or Reversal */
5948 open c_run_aa_id_bal_adj(ln_asg_action_id
5949 ,ln_assignment_id
5950 ,ln_tax_unit_id);
5951 fetch c_run_aa_id_bal_adj into ln_run_aa_id,
5952 ln_run_source_action_id;
5953 close c_run_aa_id_bal_adj;
5954 ln_master_run_aa_id := ln_asg_action_id;
5955 else
5956 ln_master_run_aa_id := ln_run_aa_id; -- Normal Chk
5957 end if;
5958 end if;
5959
5960 ln_rqp_action_id := ln_asg_action_id;
5961 ln_ppp_source_action_id := NULL;
5962
5963 else
5964
5965 ln_step := 11;
5966 lv_master_sepcheck_flag := 'Y';
5967 ln_master_run_aa_id := ln_source_action_id; -- Sep Chk
5968 ln_rqp_action_id := ln_source_action_id; -- Sep Chk
5969 ln_ppp_source_action_id := ln_source_action_id; -- Sep Chk
5970 ln_run_aa_id := ln_source_action_id; -- Sep Chk
5971
5972 end if;
5973
5974 if ln_source_action_id <> -999 then
5975
5976 open c_get_prepay_aaid_for_sepchk(ln_asg_action_id
5977 ,ln_source_action_id);
5978 fetch c_get_prepay_aaid_for_sepchk into ln_asg_action_id;
5979 close c_get_prepay_aaid_for_sepchk;
5980
5981 ln_step := 12;
5982 select pay_assignment_actions_s.nextval
5983 into ln_child_xfr_action_id
5984 from dual;
5985
5986 hr_utility.set_location(gv_package || lv_procedure_name, 30);
5987
5988 -- insert into pay_assignment_actions.
5989 ln_step := 13;
5990 hr_nonrun_asact.insact(ln_child_xfr_action_id,
5991 ln_assignment_id,
5992 ln_xfr_payroll_action_id,
5993 ln_chunk_number,
5994 ln_tax_unit_id,
5995 null,
5996 'C',
5997 p_xfr_action_id);
5998
5999 hr_utility.set_location(gv_package || lv_procedure_name, 40);
6000
6001 hr_utility.trace('GRE Locking Action = ' ||ln_child_xfr_action_id);
6002 hr_utility.trace('GRE Locked Action = ' ||ln_asg_action_id);
6003
6004 -- insert an interlock to this action
6005 ln_step := 14;
6006 hr_nonrun_asact.insint(ln_child_xfr_action_id,
6007 ln_asg_action_id);
6008
6009 ln_step := 15;
6010
6011 lv_serial_number := lv_master_action_type ||
6012 lv_master_sepcheck_flag || ln_source_action_id;
6013
6014 ln_step := 16;
6015
6016 update pay_assignment_actions
6017 set serial_number = lv_serial_number
6018 where assignment_action_id = ln_child_xfr_action_id;
6019
6020 hr_utility.trace('Processing Child action ' ||
6021 p_xfr_action_id);
6022
6023 else
6024 ln_step := 17;
6025 ln_child_xfr_action_id := p_xfr_action_id;
6026 end if;
6027
6028 ln_earnings := 0;
6029 ln_step := 18;
6030
6031 if gn_gross_earn_def_bal_id + gn_payments_def_bal_id <> 0 then
6032
6033 if ln_source_action_id = -999 then
6034
6035 ln_step := 19;
6036
6037 open c_all_runs(ln_asg_action_id,
6038 ln_assignment_id,
6039 ln_tax_unit_id,
6040 ln_sepchk_run_type_id);
6041 loop
6042 fetch c_all_runs into ln_all_run_asg_act_id;
6043 if c_all_runs%notfound then
6044 exit;
6045 end if;
6046
6047 ln_earnings := nvl(pay_balance_pkg.get_value(
6048 gn_gross_earn_def_bal_id,
6049 ln_all_run_asg_act_id),0);
6050
6051 /**************************************************
6052 ** For Non-payroll Payments element is processed
6053 ** alone, the gross earning balance returns zero.
6054 ** In this case check payment.
6055 **************************************************/
6056
6057 if ln_earnings = 0 then
6058
6059 ln_step := 20;
6060 ln_earnings := nvl(pay_balance_pkg.get_value(
6061 gn_payments_def_bal_id,
6062 ln_all_run_asg_act_id),0);
6063
6064 end if;
6065
6066 if ln_earnings <> 0 then
6067 exit;
6068 end if;
6069
6070 end loop;
6071 close c_all_runs;
6072 else
6073 ln_earnings := 1; -- For Separate Check
6074 end if;
6075
6076 end if;
6077
6078
6079 ln_step := 21;
6080 /* Bug 3452149 */
6081 if ln_earnings = 0 and
6082 pay_emp_action_arch.gv_multi_payroll_pymt = 'Y' then
6083 ln_earnings := 1;
6084 lv_archive_balance_info := 'N';
6085 else
6086 lv_archive_balance_info := 'Y';
6087 end if;
6088
6089 /* Bug 3452149 */
6090 hr_utility.trace('archive_data:payment_info: ln_earnings = ' ||
6091 ln_earnings);
6092 hr_utility.trace('archive_data:payment_info: lv_archive_balance_info = ' ||
6093 lv_archive_balance_info);
6094 if ln_earnings <> 0 then
6095 process_actions(p_xfr_payroll_action_id => ln_xfr_payroll_action_id
6096 ,p_xfr_action_id => ln_child_xfr_action_id
6097 ,p_pre_pay_action_id => ln_asg_action_id
6098 ,p_payment_action_id => ln_master_run_aa_id
6099 ,p_rqp_action_id => ln_rqp_action_id
6100 ,p_seperate_check_flag => lv_master_sepcheck_flag
6101 ,p_sepcheck_run_type_id => ln_sepchk_run_type_id
6102 ,p_action_type => lv_master_action_type
6103 ,p_legislation_code => lv_legislation_code
6104 ,p_assignment_id => ln_assignment_id
6105 ,p_payroll_id => ln_payroll_id
6106 ,p_consolidation_set_id => ln_cons_set_id
6107 ,p_tax_unit_id => ln_tax_unit_id
6108 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
6109 ,p_xfr_start_date => ld_start_date
6110 ,p_xfr_end_date => ld_end_date
6111 ,p_ppp_source_action_id => ln_ppp_source_action_id
6112 ,p_archive_balance_info => lv_archive_balance_info
6113 ,p_last_xfr_eff_date => ld_last_xfr_eff_date
6114 ,p_last_xfr_action_id => ln_last_xfr_action_id
6115 );
6116
6117 if pay_emp_action_arch.gv_multi_payroll_pymt = 'Y' and
6118 nvl(lv_master_sepcheck_flag, 'N') = 'N' and
6119 ld_last_xfr_eff_date is not null then
6120 hr_utility.trace('---------Check for un-processed asignments --------');
6121 hr_utility.trace('ln_assignment_id = '||ln_assignment_id);
6122 hr_utility.trace('ld_curr_pymt_eff_date = '||ld_curr_pymt_eff_date);
6123 hr_utility.trace('ln_payroll_id = '||ln_payroll_id);
6124 hr_utility.trace('p_xfr_action_id = '||p_xfr_action_id);
6125
6126 /***************************************************************
6127 Find out if any assignments have been un-processed. If so,
6128 archive elements processed in the un-processed assignment
6129 ***************************************************************/
6130 open c_get_unproc_asg(ln_assignment_id,
6131 ld_curr_pymt_eff_date,
6132 ln_payroll_id,
6133 p_xfr_action_id,
6134 ln_asg_action_id);
6135 loop
6136 fetch c_get_unproc_asg into ln_np_asg_id, ln_np_asg_action_id;
6137 hr_utility.trace('ln_np_asg_id ='||ln_np_asg_id);
6138 hr_utility.trace('ln_np_asg_action_id ='||ln_np_asg_action_id);
6139
6140 exit when c_get_unproc_asg%NOTFOUND;
6141
6142 -- An assignment only needs to be processed once as that will
6143 -- move all elements.
6144 if ln_np_asg_id <> ln_np_prev_asg_id then
6145 pay_ac_action_arch.emp_elements_tab.delete;
6146 pay_ac_action_arch.lrr_act_tab.delete;
6147
6148 -- Check if the date of assignment process is the same as
6149 -- last archive date. If they are the same, the element just
6150 -- needs to be moved forward otherwise we need to get the
6151 -- data from run results
6152
6153 hr_utility.trace('PrevRun ln_np_asg_id := ' || ln_np_asg_id);
6154 hr_utility.trace('PrevRun ln_child_xfr_action_id := ' || ln_child_xfr_action_id);
6155 hr_utility.trace('PrevRun ld_curr_pymt_eff_date := ' || ld_curr_pymt_eff_date);
6156
6157 open c_prev_run_information(ln_np_asg_id
6158 ,ln_child_xfr_action_id
6159 ,ld_curr_pymt_eff_date);
6160 fetch c_prev_run_information into ld_np_last_xfr_eff_date;
6161 close c_prev_run_information;
6162
6163 hr_utility.trace('ld_np_last_xfr_eff_date='||ld_np_last_xfr_eff_date);
6164 hr_utility.trace('ld_last_xfr_eff_date ='||ld_last_xfr_eff_date);
6165
6166 if ld_np_last_xfr_eff_date >= ld_last_xfr_eff_date then
6167 -- To be Changed
6168 pay_ac_action_arch.get_xfr_elements(
6169 p_xfr_action_id => ln_child_xfr_action_id
6170 ,p_last_xfr_action_id => ln_last_xfr_action_id
6171 ,p_ytd_balcall_aaid => ln_np_asg_action_id
6172 ,p_pymt_eff_date => ld_curr_pymt_eff_date
6173 ,p_legislation_code => lv_legislation_code
6174 ,p_sepchk_flag => lv_master_sepcheck_flag
6175 ,p_assignment_id => ln_np_asg_id);
6176 else
6177 pay_ac_action_arch.process_additional_elements
6178 (p_assignment_id => ln_np_asg_id,
6179 p_assignment_action_id => ln_np_asg_action_id,
6180 p_curr_eff_date => ld_curr_pymt_eff_date,
6181 p_xfr_action_id => ln_child_xfr_action_id,
6182 p_legislation_code => lv_legislation_code,
6183 p_tax_unit_id => ln_tax_unit_id);
6184
6185 change_processing_priority;
6186 end if;
6187
6188 pay_ac_action_arch.populate_summary(
6189 p_xfr_action_id => p_xfr_action_id);
6190
6191 pay_emp_action_arch.insert_rows_thro_api_process(
6192 p_action_context_id => ln_child_xfr_action_id
6193 ,p_action_context_type=> 'AAP'
6194 ,p_assignment_id => ln_np_asg_id
6195 ,p_tax_unit_id => ln_tax_unit_id
6196 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
6197 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
6198 );
6199
6200 end if;
6201 ln_np_prev_asg_id := ln_np_asg_id;
6202
6203 end loop;
6204 close c_get_unproc_asg;
6205 end if;
6206
6207 end if; -- ln_earnings
6208
6209 end loop; -- c_payment_info
6210 close c_payment_info;
6211 hr_utility.trace('archive_data:payment_info:DONE');
6212
6213 end if; /* P,U */
6214
6215
6216 ln_step := 11;
6217 if lv_master_action_type = 'V' then
6218 ln_pymt_balcall_aaid := ln_asg_action_id ;
6219 hr_utility.trace('Reversal ln_pymt_balcall_aaid'
6220 ||to_char(ln_pymt_balcall_aaid));
6221 ln_step := 12;
6222 pay_ac_action_arch.initialization_process;
6223 ln_step := 13;
6224 populate_puv_tax_balances(
6225 p_xfr_action_id => p_xfr_action_id
6226 ,p_assignment_id => ln_xfr_assignment_id
6227 ,p_pymt_balcall_aaid => ln_pymt_balcall_aaid
6228 ,p_ytd_balcall_aaid => null
6229 ,p_tax_unit_id => ln_tax_unit_id
6230 ,p_action_type => lv_master_action_type
6231 ,p_rqp_action_id => ln_asg_action_id
6232 ,p_start_date => ld_start_date
6233 ,p_end_date => ld_end_date
6234 ,p_run_effective_date => ld_curr_pymt_eff_date
6235 ,p_resident_jurisdiction => lv_resident_jurisdiction
6236 );
6237
6238 ln_step := 14;
6239 change_processing_priority;
6240 pay_emp_action_arch.insert_rows_thro_api_process(
6241 p_action_context_id => p_xfr_action_id
6242 ,p_action_context_type=> 'AAP'
6243 ,p_assignment_id => ln_xfr_assignment_id
6244 ,p_tax_unit_id => ln_tax_unit_id
6245 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
6246 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
6247 );
6248
6249 end if;
6250
6251 ln_step := 15;
6252 if lv_master_action_type = 'B' then
6253 hr_utility.trace('Reversal ln_pymt_balcall_aaid'
6254 ||to_char(ln_pymt_balcall_aaid));
6255 pay_ac_action_arch.initialization_process;
6256 ln_step := 16;
6257 populate_adj_tax_balances(
6258 p_xfr_action_id => p_xfr_action_id
6259 ,p_assignment_id => ln_xfr_assignment_id
6260 ,p_tax_unit_id => ln_tax_unit_id
6261 ,p_action_type => lv_master_action_type
6262 ,p_start_date => ld_start_date
6263 ,p_end_date => ld_end_date
6264 ,p_run_effective_date => ld_curr_pymt_eff_date
6265 );
6266
6267 ln_step := 17;
6268 change_processing_priority;
6269 pay_emp_action_arch.insert_rows_thro_api_process(
6270 p_action_context_id => p_xfr_action_id
6271 ,p_action_context_type=> 'AAP'
6272 ,p_assignment_id => ln_xfr_assignment_id
6273 ,p_tax_unit_id => ln_tax_unit_id
6274 ,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
6275 ,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
6276 );
6277
6278 end if;
6279
6280 /****************************************************************
6281 ** Archive all the payroll action level data once only when
6282 ** chunk number is 1. Also check if this has not been archived
6283 ** earlier
6284 *****************************************************************/
6285 hr_utility.set_location(gv_package || lv_procedure_name,210);
6286 ln_step := 20;
6287 open c_check_pay_action(ln_xfr_payroll_action_id);
6288 fetch c_check_pay_action into ln_pay_action_count;
6289 close c_check_pay_action;
6290 if ln_pay_action_count = 0 then
6291 hr_utility.set_location(gv_package || lv_procedure_name,210);
6292 if ln_chunk_number = 1 then
6293 ln_step := 25;
6294 pay_emp_action_arch.arch_pay_action_level_data(
6295 p_payroll_action_id => ln_xfr_payroll_action_id
6296 ,p_payroll_id => ln_payroll_id
6297 ,p_effective_Date => ld_end_date
6298 );
6299 end if;
6300
6301 end if;
6302
6303 EXCEPTION
6304 when others then
6305 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
6306 gv_package || lv_procedure_name;
6307
6308 hr_utility.trace(lv_error_message || '-' || sqlerrm);
6309
6310 lv_error_message :=
6311 pay_emp_action_arch.set_error_message(lv_error_message);
6312
6313 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
6314 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
6315 hr_utility.raise_error;
6316
6317 end action_archive_data;
6318
6319
6320 /*********************************************************************
6321 Name : check_alien
6322 Purpose : This function checks if the given assignemnt is ALIEN
6323 it returns a 'TRUE' or 'FALSE'.
6324 Arguments : IN
6325 p_assignment_action_id number;
6326 Notes :
6327 *********************************************************************/
6328 FUNCTION check_alien(
6329 p_assignment_action_id in number)
6330 RETURN VARCHAR2
6331 IS
6332
6333 ln_assignment_id number;
6334 lv_error_message VARCHAR2(200);
6335 lv_procedure_name VARCHAR2(100) := '.check_alien';
6336
6337 cursor c_get_assignment_id (cp_assignment_action_id in number) is
6338 select assignment_id
6339 from pay_assignment_actions
6340 where assignment_action_id = cp_assignment_action_id;
6341
6342 BEGIN
6343 hr_utility.trace('opened c_get_assignment_id');
6344
6345 open c_get_assignment_id(p_assignment_action_id);
6346 fetch c_get_assignment_id into ln_assignment_id;
6347 close c_get_assignment_id;
6348
6349 hr_utility.trace('ln_assignment_id = ' ||
6350 to_char(ln_assignment_id));
6351
6352 return pqp_us_ff_functions.is_windstar(p_assignment_id => ln_assignment_id);
6353
6354 EXCEPTION
6355 when others then
6356 lv_error_message := 'Error in ' ||
6357 gv_package || lv_procedure_name;
6358
6359 hr_utility.trace(lv_error_message || '-' || sqlerrm);
6360
6361 lv_error_message :=
6362 pay_emp_action_arch.set_error_message(lv_error_message);
6363
6364 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
6365 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
6366 hr_utility.raise_error;
6367 END check_alien;
6368
6369 --begin
6370 --hr_utility.trace_on (null, 'XFR');
6371
6372 end pay_us_action_arch;