[Home] [Help]
PACKAGE BODY: APPS.PAY_US_EMP_BALADJ_CLEANUP
Source
1 PACKAGE BODY pay_us_emp_baladj_cleanup AS
2 /* $Header: payusbaladjclean.pkb 120.0 2005/05/29 11:52 appldev noship $ */
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 :
21
22 Description :
23
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 10-JUL-2004 ahanda 115.0 Created.
30 13-JUL-2004 ahanda 115.1 Changed SIT*_RS to SIT*_WK
31 14-JUL-2004 jgoswami 115.3 Added code for MA
32 15-JUL-2004 jgoswami 115.4 Added code for Courtsey
33 Withholding
34 14-MAR-2005 sackumar 115.7 4222032 Change in the Range Cursor removing redundant
35 use of bind Variable (:payroll_action_id)
36 ********************************************************************/
37
38 /********************************************************************
39 ** Package Local Variables
40 *********************************************************************/
41 gv_package VARCHAR2(100);
42
43 /********************************************************************
44 Name : get_payroll_action_info
45 Purpose : This returns the Payroll Action level
46 Input : p_payroll_action_id - Payroll_Action_id
47 Returns : p_start_date - Start date
48 p_end_date - End date
49 p_business_group_id - Business Group ID
50 p_cons_set_id - Consolidation Set
51 p_payroll_id - Payroll ID
52 ********************************************************************/
53 PROCEDURE get_payroll_action_info(
54 p_payroll_action_id in number
55 ,p_end_date out nocopy date
56 ,p_start_date out nocopy date
57 ,p_business_group_id out nocopy number
58 ,p_state_abbrev out nocopy varchar2
59 ,p_cons_set_id out nocopy number
60 ,p_payroll_id out nocopy number
61 )
62 IS
63 cursor c_payroll_Action_info
64 (cp_payroll_action_id in number) is
65 select effective_date,
66 start_date,
67 business_group_id,
68 pay_us_payroll_utils.get_parameter(
69 'TRANSFER_STATE',
70 legislative_parameters) state_abbrev,
71 to_number(substr(legislative_parameters,
72 instr(legislative_parameters,
73 'TRANSFER_CONSOLIDATION_SET_ID=')
74 + length('TRANSFER_CONSOLIDATION_SET_ID='))),
75 to_number(ltrim(rtrim(substr(legislative_parameters,
76 instr(legislative_parameters,
77 'TRANSFER_PAYROLL_ID=')
78 + length('TRANSFER_PAYROLL_ID='),
79 (instr(legislative_parameters,
80 'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
81 - (instr(legislative_parameters,
82 'TRANSFER_PAYROLL_ID=')
83 + length('TRANSFER_PAYROLL_ID='))))))
84 from pay_payroll_actions
85 where payroll_action_id = cp_payroll_action_id;
86
87 ld_end_date DATE;
88 ld_start_date DATE;
89 ln_business_group_id NUMBER;
90 ln_cons_set_id NUMBER;
91 ln_payroll_id NUMBER;
92 lv_state_abbrev VARCHAR2(10);
93 lv_procedure_name VARCHAR2(100);
94
95 lv_error_message VARCHAR2(200);
96 ln_step NUMBER;
97
98 BEGIN
99 lv_procedure_name := '.get_payroll_action_info';
100 hr_utility.set_location(gv_package || lv_procedure_name, 10);
101 ln_step := 1;
102 open c_payroll_action_info(p_payroll_action_id);
103 fetch c_payroll_action_info into ld_end_date,
104 ld_start_date,
105 ln_business_group_id,
106 lv_state_abbrev,
107 ln_cons_set_id,
108 ln_payroll_id;
109 close c_payroll_action_info;
110
111 hr_utility.set_location(gv_package || lv_procedure_name, 30);
112 p_end_date := ld_end_date;
113 p_start_date := ld_start_date;
114 p_business_group_id := ln_business_group_id;
115 p_cons_set_id := ln_cons_set_id;
116 p_state_abbrev := lv_state_abbrev;
117 p_payroll_id := ln_payroll_id;
118 hr_utility.set_location(gv_package || lv_procedure_name, 50);
119 ln_step := 2;
120
121 EXCEPTION
122 when others then
123 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
124 gv_package || lv_procedure_name;
125
126 hr_utility.trace(lv_error_message || '-' || sqlerrm);
127
128 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
129 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
130 hr_utility.raise_error;
131
132 END get_payroll_action_info;
133
134
135 FUNCTION get_input_value_id(p_element_type_id in number
136 ,p_input_value_name in varchar2
137 ,p_effective_date in date)
138 RETURN NUMBER
139 IS
140 ln_input_value_id NUMBER;
141
142 cursor c_get_input_id (cp_element_type_id in number
143 ,cp_input_value_name in varchar2
144 ,cp_effective_date in date) is
145 select input_value_id
146 from pay_input_values_f piv
147 where piv.element_type_id = cp_element_type_id
148 and piv.legislation_code = 'US'
149 and piv.name = cp_input_value_name
150 and cp_effective_date between piv.effective_start_date
151 and piv.effective_end_date;
152
153 BEGIN
154 open c_get_input_id(p_element_type_id,
155 p_input_value_name,
156 p_effective_date);
157 fetch c_get_input_id into ln_input_value_id;
158 close c_get_input_id;
159
160 return(ln_input_value_id);
161
162 END get_input_value_id;
163
164
165 /******************************************************************
166 Name : get_balance_value
167 Purpose : This procedure calls the get_value function for
168 balance calls.
169 Arguments :
170 Notes :
171 ******************************************************************/
172 FUNCTION get_balance_value(
173 p_defined_balance_id in number
174 ,p_balcall_aaid in number)
175 RETURN NUMBER
176 IS
177 lv_error_message VARCHAR2(200);
178 ln_bal_value NUMBER;
179
180 BEGIN
181
182 if p_defined_balance_id is not null then
183 ln_bal_value := nvl(pay_balance_pkg.get_value(
184 p_defined_balance_id,
185 p_balcall_aaid),0);
186 end if;
187
188 return (ln_bal_value);
189
190 EXCEPTION
191 when others then
192 return (null);
193
194 END get_balance_value;
195
196 PROCEDURE initialize_plsql_table
197 (p_effective_date in date
198 ,p_business_group_id in number)
199 IS
200 i NUMBER;
201 j NUMBER;
202
203 FUNCTION get_element_category(p_business_group_id in number
204 ,p_element_category in varchar2)
205 RETURN BOOLEAN
206 IS
207 lv_exists VARCHAR2(1);
208
209 cursor c_element_category(cp_business_group_id in number
210 ,cp_element_category in varchar2) is
211 select 'Y' from dual
212 where exists (select 1 from pay_element_types_f pet,
213 pay_element_classifications pec
214 where pet.classification_id = pec.classification_id
215 and pet.business_group_id = cp_business_group_id
216 and pet.element_information1 = cp_element_category
217 and pec.classification_name = 'Pre-Tax Deductions'
218 and pec.legislation_code = 'US');
219 BEGIN
220 lv_exists := 'N';
221
222 open c_element_category(p_business_group_id, p_element_category);
223 fetch c_element_category into lv_exists;
224 if c_element_category%notfound then
225 lv_exists := 'N';
226 end if;
227 close c_element_category;
228
229 if lv_exists = 'Y' then
230 return(TRUE);
231 else
232 return(FALSE);
233 end if;
234
235 END get_element_category;
236
237 FUNCTION get_element_type_id(p_elment_name in varchar2)
238 RETURN NUMBER
239 IS
240 ln_element_type_id NUMBER;
241
242 cursor get_element_type_id (cp_elment_name in varchar2) is
243 select element_type_id from pay_element_types_f
244 where element_name = cp_elment_name
245 and legislation_code = 'US';
246
247 BEGIN
248 open get_element_type_id(p_elment_name);
249 fetch get_element_type_id into ln_element_type_id;
250 close get_element_type_id;
251
252 return(ln_element_type_id);
253
254 END get_element_type_id;
255
256 FUNCTION get_defined_id(p_user_entity_name in varchar2)
257 RETURN NUMBER
258 IS
259 ln_defined_id NUMBER;
260
261 cursor c_get_defined_id (cp_user_entity_name in varchar2) is
262 select creator_id from ff_user_entities
263 where user_entity_name = cp_user_entity_name;
264
265 BEGIN
266 open c_get_defined_id(p_user_entity_name);
267 fetch c_get_defined_id into ln_defined_id;
268 close c_get_defined_id;
269
270 return(ln_defined_id);
271
272 END get_defined_id;
273
274 BEGIN
275
276 /************************************************************
277 ** Initialize SIT Balances
278 *************************************************************/
279 i := 0;
280 --SIT and SDI ER Gross
281 ltr_sit_tax_bal(i).balance_name
282 := 'SIT_GROSS_ASG_JD_GRE_YTD';
283 ltr_sit_tax_bal(i).ytd_def_bal_id
284 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
285 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT_WK';
286 ltr_sit_tax_bal(i).element_type_id
287 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
288 ltr_sit_tax_bal(i).input_name := 'Gross';
289 ltr_sit_tax_bal(i).input_value_id
290 := get_input_value_id(
291 p_element_type_id => ltr_sit_tax_bal(i).element_type_id
292 ,p_input_value_name => ltr_sit_tax_bal(i).input_name
293 ,p_effective_date => p_effective_date);
294
295 ltr_sdi_er_tax_bal(i).balance_name
296 := 'SDI_ER_GROSS_ASG_JD_GRE_YTD';
297 ltr_sdi_er_tax_bal(i).ytd_def_bal_id
298 := get_defined_id(ltr_sdi_er_tax_bal(i).balance_name);
299 ltr_sdi_er_tax_bal(i).element_name := 'SDI_SUBJECT_ER';
300 ltr_sdi_er_tax_bal(i).element_type_id
301 := get_element_type_id(ltr_sdi_er_tax_bal(i).element_name);
302 ltr_sdi_er_tax_bal(i).input_name := 'Gross';
303 ltr_sdi_er_tax_bal(i).input_value_id
304 := get_input_value_id(
305 p_element_type_id => ltr_sdi_er_tax_bal(i).element_type_id
306 ,p_input_value_name => ltr_sdi_er_tax_bal(i).input_name
307 ,p_effective_date => p_effective_date);
308
309 --SIT and SDI ER Whable
310 i := i + 1;
311 ltr_sit_tax_bal(i).balance_name
312 := 'SIT_SUBJ_WHABLE_ASG_JD_GRE_YTD';
313 ltr_sit_tax_bal(i).ytd_def_bal_id
314 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
315 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT_WK';
316 ltr_sit_tax_bal(i).element_type_id
317 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
318 ltr_sit_tax_bal(i).input_name := 'Subj Whable';
319 ltr_sit_tax_bal(i).input_value_id
320 := get_input_value_id(
321 p_element_type_id => ltr_sit_tax_bal(i).element_type_id
322 ,p_input_value_name => ltr_sit_tax_bal(i).input_name
323 ,p_effective_date => p_effective_date);
324
325 ltr_sdi_er_tax_bal(i).balance_name
326 := 'SDI_ER_SUBJ_WHABLE_ASG_JD_GRE_YTD';
327 ltr_sdi_er_tax_bal(i).ytd_def_bal_id
328 := get_defined_id(ltr_sdi_er_tax_bal(i).balance_name);
329 ltr_sdi_er_tax_bal(i).element_name := 'SDI_SUBJECT_ER';
330 ltr_sdi_er_tax_bal(i).element_type_id
331 := get_element_type_id(ltr_sdi_er_tax_bal(i).element_name);
332 ltr_sdi_er_tax_bal(i).input_name := 'Subj Whable';
333 ltr_sdi_er_tax_bal(i).input_value_id
334 := get_input_value_id(
335 p_element_type_id => ltr_sdi_er_tax_bal(i).element_type_id
336 ,p_input_value_name => ltr_sdi_er_tax_bal(i).input_name
337 ,p_effective_date => p_effective_date);
338
339
340 --SIT and SDI ER Other Pre Tax
341 i := i + 1;
342 ltr_sit_tax_bal(i).balance_name
343 := 'SIT_OTHER_PRETAX_REDNS_ASG_JD_GRE_YTD';
344 ltr_sit_tax_bal(i).ytd_def_bal_id
345 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
346 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT2_WK';
347 ltr_sit_tax_bal(i).element_type_id
348 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
349 ltr_sit_tax_bal(i).input_name := 'Other Pretax Redns';
350 ltr_sit_tax_bal(i).input_value_id
351 := get_input_value_id(
352 p_element_type_id => ltr_sit_tax_bal(i).element_type_id
353 ,p_input_value_name => ltr_sit_tax_bal(i).input_name
354 ,p_effective_date => p_effective_date);
355
356 ltr_sdi_er_tax_bal(i).balance_name
357 := 'SDI_ER_OTHER_PRETAX_REDNS_ASG_JD_GRE_YTD';
358 ltr_sdi_er_tax_bal(i).ytd_def_bal_id
359 := get_defined_id(ltr_sdi_er_tax_bal(i).balance_name);
360 ltr_sdi_er_tax_bal(i).element_name := 'SDI_SUBJECT2_ER';
361 ltr_sdi_er_tax_bal(i).element_type_id
362 := get_element_type_id(ltr_sdi_er_tax_bal(i).element_name);
363 ltr_sdi_er_tax_bal(i).input_name := 'Other Pretax Redns';
364 ltr_sdi_er_tax_bal(i).input_value_id
365 := get_input_value_id(
366 p_element_type_id => ltr_sdi_er_tax_bal(i).element_type_id
367 ,p_input_value_name => ltr_sdi_er_tax_bal(i).input_name
368 ,p_effective_date => p_effective_date);
369
370
371 if get_element_category(p_business_group_id, 'D') then
372 i := i + 1;
373 ltr_sit_tax_bal(i).balance_name
374 := 'SIT_401_REDNS_ASG_JD_GRE_YTD';
375 ltr_sit_tax_bal(i).ytd_def_bal_id
376 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
377 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT_WK';
378 ltr_sit_tax_bal(i).element_type_id
379 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
380 ltr_sit_tax_bal(i).input_name := 'DC 401 Redns';
381 ltr_sit_tax_bal(i).input_value_id
382 := get_input_value_id(
383 p_element_type_id =>ltr_sit_tax_bal(i).element_type_id
384 ,p_input_value_name=>ltr_sit_tax_bal(i).input_name
385 ,p_effective_date =>p_effective_date);
386
387 ltr_sdi_er_tax_bal(i).balance_name
388 := 'SDI_ER_401_REDNS_ASG_JD_GRE_YTD';
389 ltr_sdi_er_tax_bal(i).ytd_def_bal_id
390 := get_defined_id(ltr_sdi_er_tax_bal(i).balance_name);
391 ltr_sdi_er_tax_bal(i).element_name := 'SDI_SUBJECT_ER';
392 ltr_sdi_er_tax_bal(i).element_type_id
393 := get_element_type_id(ltr_sdi_er_tax_bal(i).element_name);
394 ltr_sdi_er_tax_bal(i).input_name := 'DC 401 Redns';
395 ltr_sdi_er_tax_bal(i).input_value_id
396 := get_input_value_id(
397 p_element_type_id => ltr_sdi_er_tax_bal(i).element_type_id
398 ,p_input_value_name => ltr_sdi_er_tax_bal(i).input_name
399 ,p_effective_date => p_effective_date);
400 end if;
401
402 if get_element_category(p_business_group_id, 'H') then
403 i := i + 1;
404 ltr_sit_tax_bal(i).balance_name
405 := 'SIT_125_REDNS_ASG_JD_GRE_YTD';
406 ltr_sit_tax_bal(i).ytd_def_bal_id
407 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
408 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT_WK';
409 ltr_sit_tax_bal(i).element_type_id
410 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
411 ltr_sit_tax_bal(i).input_name := 'S 125 Redns';
412 ltr_sit_tax_bal(i).input_value_id
413 := get_input_value_id(
414 p_element_type_id => ltr_sit_tax_bal(i).element_type_id
415 ,p_input_value_name => ltr_sit_tax_bal(i).input_name
416 ,p_effective_date => p_effective_date);
417
418 ltr_sdi_er_tax_bal(i).balance_name
419 := 'SDI_ER_125_REDNS_ASG_JD_GRE_YTD';
420 ltr_sdi_er_tax_bal(i).ytd_def_bal_id
421 := get_defined_id(ltr_sdi_er_tax_bal(i).balance_name);
422 ltr_sdi_er_tax_bal(i).element_name := 'SDI_SUBJECT_ER';
423 ltr_sdi_er_tax_bal(i).element_type_id
424 := get_element_type_id(ltr_sdi_er_tax_bal(i).element_name);
425 ltr_sdi_er_tax_bal(i).input_name := 'S 125 Redns';
426 ltr_sdi_er_tax_bal(i).input_value_id
427 := get_input_value_id(
428 p_element_type_id => ltr_sdi_er_tax_bal(i).element_type_id
429 ,p_input_value_name => ltr_sdi_er_tax_bal(i).input_name
430 ,p_effective_date => p_effective_date);
431 end if;
432
433 if get_element_category(p_business_group_id, 'S') then
434 i := i + 1;
435 ltr_sit_tax_bal(i).balance_name
436 := 'SIT_DEP_CARE_REDNS_ASG_JD_GRE_YTD';
437 ltr_sit_tax_bal(i).ytd_def_bal_id
438 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
439 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT_WK';
440 ltr_sit_tax_bal(i).element_type_id
441 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
442 ltr_sit_tax_bal(i).input_name := 'Dep Care Redns';
443 ltr_sit_tax_bal(i).input_value_id
444 := get_input_value_id(
445 p_element_type_id => ltr_sit_tax_bal(i).element_type_id
446 ,p_input_value_name => ltr_sit_tax_bal(i).input_name
447 ,p_effective_date => p_effective_date);
448
449 ltr_sdi_er_tax_bal(i).balance_name
450 := 'SDI_ER_DEP_CARE_REDNS_ASG_JD_GRE_YTD';
451 ltr_sdi_er_tax_bal(i).ytd_def_bal_id
452 := get_defined_id(ltr_sdi_er_tax_bal(i).balance_name);
453 ltr_sdi_er_tax_bal(i).element_name := 'SDI_SUBJECT_ER';
454 ltr_sdi_er_tax_bal(i).element_type_id
455 := get_element_type_id(ltr_sdi_er_tax_bal(i).element_name);
456 ltr_sdi_er_tax_bal(i).input_name := 'Dep Care Redns';
457 ltr_sdi_er_tax_bal(i).input_value_id
458 := get_input_value_id(
459 p_element_type_id => ltr_sdi_er_tax_bal(i).element_type_id
460 ,p_input_value_name => ltr_sdi_er_tax_bal(i).input_name
461 ,p_effective_date => p_effective_date);
462 end if;
463
464
465 if get_element_category(p_business_group_id, 'E') then
466 i := i + 1;
467 ltr_sit_tax_bal(i).balance_name
468 := 'SIT_403_REDNS_ASG_JD_GRE_YTD';
469 ltr_sit_tax_bal(i).ytd_def_bal_id
470 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
471 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT2_WK';
472 ltr_sit_tax_bal(i).element_type_id
473 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
474 ltr_sit_tax_bal(i).input_name := 'DC 403 Redns';
475 ltr_sit_tax_bal(i).input_value_id
476 := get_input_value_id(
477 p_element_type_id => ltr_sit_tax_bal(i).element_type_id
478 ,p_input_value_name => ltr_sit_tax_bal(i).input_name
479 ,p_effective_date => p_effective_date);
480
481 ltr_sdi_er_tax_bal(i).balance_name
482 := 'SDI_ER_403_REDNS_ASG_JD_GRE_YTD';
483 ltr_sdi_er_tax_bal(i).ytd_def_bal_id
484 := get_defined_id(ltr_sdi_er_tax_bal(i).balance_name);
485 ltr_sdi_er_tax_bal(i).element_name := 'SDI_SUBJECT2_ER';
486 ltr_sdi_er_tax_bal(i).element_type_id
487 := get_element_type_id(ltr_sdi_er_tax_bal(i).element_name);
488 ltr_sdi_er_tax_bal(i).input_name := 'DC 403 Redns';
489 ltr_sdi_er_tax_bal(i).input_value_id
490 := get_input_value_id(
491 p_element_type_id => ltr_sdi_er_tax_bal(i).element_type_id
492 ,p_input_value_name => ltr_sdi_er_tax_bal(i).input_name
493 ,p_effective_date => p_effective_date);
494 end if;
495
496 if get_element_category(p_business_group_id, 'G') then
497 i := i + 1;
498 ltr_sit_tax_bal(i).balance_name
499 := 'SIT_457_REDNS_ASG_JD_GRE_YTD';
500 ltr_sit_tax_bal(i).ytd_def_bal_id
501 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
502 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT2_WK';
503 ltr_sit_tax_bal(i).element_type_id
504 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
505 ltr_sit_tax_bal(i).input_name := 'DC 457 Redns';
506 ltr_sit_tax_bal(i).input_value_id
507 := get_input_value_id(
508 p_element_type_id => ltr_sit_tax_bal(i).element_type_id
509 ,p_input_value_name => ltr_sit_tax_bal(i).input_name
510 ,p_effective_date => p_effective_date);
511
512 ltr_sdi_er_tax_bal(i).balance_name
513 := 'SDI_ER_457_REDNS_ASG_JD_GRE_YTD';
514 ltr_sdi_er_tax_bal(i).ytd_def_bal_id
515 := get_defined_id(ltr_sdi_er_tax_bal(i).balance_name);
516 ltr_sdi_er_tax_bal(i).element_name := 'SDI_SUBJECT2_ER';
517 ltr_sdi_er_tax_bal(i).element_type_id
518 := get_element_type_id(ltr_sdi_er_tax_bal(i).element_name);
519 ltr_sdi_er_tax_bal(i).input_name := 'DC 457 Redns';
520 ltr_sdi_er_tax_bal(i).input_value_id
521 := get_input_value_id(
522 p_element_type_id => ltr_sdi_er_tax_bal(i).element_type_id
523 ,p_input_value_name => ltr_sdi_er_tax_bal(i).input_name
524 ,p_effective_date => p_effective_date);
525
526 end if;
527
528
529 --SIT NWhable
530 i := i + 1;
531 ltr_sit_tax_bal(i).balance_name
532 := 'SIT_SUBJ_NWHABLE_ASG_JD_GRE_YTD';
533 ltr_sit_tax_bal(i).ytd_def_bal_id
534 := get_defined_id(ltr_sit_tax_bal(i).balance_name);
535 ltr_sit_tax_bal(i).element_name := 'SIT_SUBJECT_WK';
536 ltr_sit_tax_bal(i).element_type_id
537 := get_element_type_id(ltr_sit_tax_bal(i).element_name);
538 ltr_sit_tax_bal(i).input_name := 'Subj NWhable';
539 ltr_sit_tax_bal(i).input_value_id
540 := get_input_value_id(
541 p_element_type_id => ltr_sit_tax_bal(i).element_type_id
542 ,p_input_value_name => ltr_sit_tax_bal(i).input_name
543 ,p_effective_date => p_effective_date);
544
545 if ltr_sit_tax_bal.count > 0 then
546 for i in ltr_sit_tax_bal.first .. ltr_sit_tax_bal.last loop
547 hr_utility.trace('balance name='||ltr_sit_tax_bal(i).balance_name);
548 hr_utility.trace('input name='||ltr_sit_tax_bal(i).input_value_id);
549 hr_utility.trace('YTD Def Bal ='||ltr_sit_tax_bal(i).ytd_def_bal_id);
550 if ltr_sit_tax_bal(i).ytd_def_bal_id is null then
551 hr_utility.raise_error;
552 end if;
553 end loop;
554 end if;
555 if ltr_sdi_er_tax_bal.count > 0 then
556 for i in ltr_sdi_er_tax_bal.first .. ltr_sdi_er_tax_bal.last loop
557 hr_utility.trace('balance name='||ltr_sdi_er_tax_bal(i).balance_name);
558 hr_utility.trace('input name='||ltr_sdi_er_tax_bal(i).input_value_id);
559 hr_utility.trace('YTD Def Bal ='||ltr_sdi_er_tax_bal(i).ytd_def_bal_id);
560 if ltr_sit_tax_bal(i).ytd_def_bal_id is null then
561 hr_utility.raise_error;
562 end if;
563 end loop;
564 end if;
565
566
567 j := 0;
568 ltr_misc_er_tax_bal(j).balance_name
569 := 'SDI_ER_SUBJ_WHABLE_PER_JD_GRE_YTD';
570 ltr_misc_er_tax_bal(j).ytd_def_bal_id
571 := get_defined_id(ltr_misc_er_tax_bal(j).balance_name);
572 ltr_misc_er_tax_bal(j).element_name := 'SDI_SUBJECT_ER';
573 ltr_misc_er_tax_bal(j).element_type_id
574 := get_element_type_id(ltr_misc_er_tax_bal(j).element_name);
575 ltr_misc_er_tax_bal(j).input_name := 'Subj Whable';
576 ltr_misc_er_tax_bal(j).input_value_id
577 := get_input_value_id(
578 p_element_type_id => ltr_misc_er_tax_bal(j).element_type_id
579 ,p_input_value_name => ltr_misc_er_tax_bal(j).input_name
580 ,p_effective_date => p_effective_date);
581
582 j := j + 1;
583 ltr_misc_er_tax_bal(j).balance_name
584 := 'SDI_ER_PRE_TAX_REDNS_PER_JD_GRE_YTD';
585 ltr_misc_er_tax_bal(j).ytd_def_bal_id
586 := get_defined_id(ltr_misc_er_tax_bal(j).balance_name);
587 ltr_misc_er_tax_bal(j).element_name := 'SDI_SUBJECT_ER';
588 ltr_misc_er_tax_bal(j).element_type_id
589 := get_element_type_id(ltr_misc_er_tax_bal(j).element_name);
590 ltr_misc_er_tax_bal(j).input_name := 'Pre Tax Redns';
591 ltr_misc_er_tax_bal(j).input_value_id
592 := get_input_value_id(
593 p_element_type_id => ltr_misc_er_tax_bal(j).element_type_id
594 ,p_input_value_name => ltr_misc_er_tax_bal(j).input_name
595 ,p_effective_date => p_effective_date);
596
597 j := j + 1;
598 ltr_misc_er_tax_bal(j).balance_name
599 := 'SDI_ER_TAXABLE_PER_JD_GRE_YTD';
600 ltr_misc_er_tax_bal(j).ytd_def_bal_id
601 := get_defined_id(ltr_misc_er_tax_bal(j).balance_name);
602 ltr_misc_er_tax_bal(j).element_name := 'MISC1_STATE_TAX_ER';
603 ltr_misc_er_tax_bal(j).element_type_id
604 := get_element_type_id(ltr_misc_er_tax_bal(j).element_name);
605 ltr_misc_er_tax_bal(j).input_name := 'Taxable';
606 ltr_misc_er_tax_bal(j).input_value_id
607 := get_input_value_id(
608 p_element_type_id => ltr_misc_er_tax_bal(j).element_type_id
609 ,p_input_value_name => ltr_misc_er_tax_bal(j).input_name
610 ,p_effective_date => p_effective_date);
611
612 END initialize_plsql_table;
613
614
615 /********************************************************************
616 Name : range_cursor
617 Purpose : This returns the select statement that is
618 used to created the range rows
619 Arguments :
620 Notes : Calls procedure - get_payroll_action_info
621 ********************************************************************/
622 PROCEDURE range_cursor(
623 p_payroll_action_id in number
624 ,p_sqlstr out nocopy varchar2)
625 IS
626
627 ld_end_date DATE;
628 ld_start_date DATE;
629 ln_business_group_id NUMBER;
630 lv_cons_set_id VARCHAR2(50);
631 lv_payroll_id VARCHAR2(50);
632 lv_state_abbrev VARCHAR2(10);
633 lv_date VARCHAR2(50);
634
635 lv_sql_string VARCHAR2(32000);
636 lv_procedure_name VARCHAR2(100);
637
638 BEGIN
639 lv_procedure_name := '.range_cursor';
640 hr_utility.set_location(gv_package || lv_procedure_name, 10);
641 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
642 ,p_start_date => ld_start_date
643 ,p_end_date => ld_end_date
644 ,p_business_group_id => ln_business_group_id
645 ,p_state_Abbrev => lv_state_abbrev
646 ,p_cons_set_id => lv_cons_set_id
647 ,p_payroll_id => lv_payroll_id);
648 hr_utility.set_location(gv_package || lv_procedure_name, 20);
649 if lv_cons_set_id is null then
650 lv_cons_set_id := '%';
651 end if;
652
653 if lv_payroll_id is null then
654 lv_payroll_id := '%';
655 end if;
656
657 lv_date := fnd_date.date_to_canonical(
658 greatest(ld_start_date,
659 fnd_date.canonical_to_date('2004/01/01 00:00:00')));
660
661
662 insert into pay_action_information
663 (ACTION_INFORMATION_ID,
664 ACTION_CONTEXT_ID,
665 ACTION_CONTEXT_TYPE,
666 ACTION_INFORMATION_CATEGORY,
667 ACTION_INFORMATION1
668 )
669 select pay_action_information_s.nextval,
670 p_payroll_action_id,
671 'PPA',
672 'GAGA_STATUS',
673 'U'
674 from dual;
675
676 lv_sql_string :=
677 'select distinct paf.person_id
678 from per_assignments_f paf,
679 pay_assignment_actions paa,
680 pay_payroll_actions ppa
681 where ppa.business_group_id = ''' || ln_business_group_id || '''
682 and paf.assignment_id = paa.assignment_id
683 and ppa.effective_date between paf.effective_start_date
684 and paf.effective_end_date
685 and ppa.effective_date
686 between fnd_date.canonical_to_date(''' ||
687 fnd_date.date_to_canonical(ld_start_date-10) || ''')
688 and fnd_date.canonical_to_date(''' ||
689 fnd_date.date_to_canonical(ld_end_date+30) || ''')
690 and ppa.action_type in (''R'',''Q'')
691 and ppa.last_update_date >= fnd_date.canonical_to_date(''' ||
692 lv_date || ''')
693 and ppa.consolidation_set_id like ''' || lv_cons_set_id || '''
694 and ppa.payroll_id like ''' || lv_payroll_id || '''
695 and ppa.payroll_action_id = paa.payroll_action_id
696 and paa.action_status = ''C''
697 and paa.source_action_id is null
698 and :payroll_action_id is not null
699 order by paf.person_id';
700
701 hr_utility.set_location(gv_package || lv_procedure_name, 30);
702 p_sqlstr := lv_sql_string;
703 hr_utility.set_location(gv_package || lv_procedure_name, 50);
704
705 END range_cursor;
706
707
708 /*******************************************************************
709 Name : action_creation
710 Purpose : This creates the assignment actions for
711 a specific chunk of people to be archived
712 by the Archiver process.
713 Arguments :
714 Notes : Calls procedure - get_payroll_action_info
715 *******************************************************************/
716 PROCEDURE action_creation(
717 p_payroll_action_id in number
718 ,p_start_person_id in number
719 ,p_end_person_id in number
720 ,p_chunk in number)
721 IS
722
723 cursor c_get_emp(cp_start_person_id in number
724 ,cp_end_person_id in number
725 ,cp_cons_set_id in varchar2
726 ,cp_payroll_id in varchar2
727 ,cp_business_group_id in number
728 ,cp_start_date in date
729 ,cp_end_date in date
730 ) is
731 select distinct
732 paa.tax_unit_id,
733 paa.assignment_id,
734 ppa.effective_date
735 from per_assignments_f paf,
736 pay_assignment_actions paa,
737 pay_payroll_actions ppa
738 where paf.person_id between cp_start_person_id
739 and cp_end_person_id
740 and ppa.effective_date between paf.effective_start_date
741 and paf.effective_end_date
742 and paa.assignment_id = paf.assignment_id
743 and ppa.business_group_id = cp_business_group_id
744 and ppa.effective_date between cp_start_date - 10
745 and cp_end_date + 30
746 and ppa.action_type in ('R','Q')
747 and ppa.last_update_date >=
748 greatest(cp_start_date,
749 fnd_date.canonical_to_date('2004/07/01 00:00:00'))
750 and ppa.consolidation_set_id like cp_cons_set_id
751 and ppa.payroll_id like cp_payroll_id
752 and ppa.payroll_action_id = paa.payroll_action_id
753 and paa.source_action_id is not null
754 and paa.action_status = 'C'
755 and not exists
756 (select 1
757 from pay_action_interlocks pai,
758 pay_assignment_actions paa1,
759 pay_payroll_actions ppa1
760 where pai.locked_action_id = paa.assignment_action_id
761 and paa1.assignment_action_id = pai.locking_action_id
762 and ppa1.payroll_action_id = paa1.payroll_action_id
763 and ppa1.action_type = 'V'
764 )
765 order by 1, 2;
766
767 cursor c_get_emp_state(cp_start_person_id in number
768 ,cp_end_person_id in number
769 ,cp_cons_set_id in varchar2
770 ,cp_payroll_id in varchar2
771 ,cp_business_group_id in number
772 ,cp_start_date in date
773 ,cp_end_date in date
774 ,cp_state_code in varchar2
775 ) is
776 select distinct
777 paa.tax_unit_id,
778 paa.assignment_id,
779 ppa.effective_date
780 from per_assignments_f paf,
781 pay_us_emp_state_tax_rules_f pest,
782 pay_assignment_actions paa,
783 pay_payroll_actions ppa
784 where paf.person_id between cp_start_person_id
785 and cp_end_person_id
786 and ppa.effective_date between paf.effective_start_date
787 and paf.effective_end_date
788 and pest.assignment_id = paf.assignment_id
789 and ppa.effective_date between pest.effective_start_date
790 and pest.effective_end_date
791 and pest.state_code = cp_state_code
792 and paa.assignment_id = paf.assignment_id
793 and ppa.business_group_id = cp_business_group_id
794 and ppa.effective_date between cp_start_date - 10
795 and cp_end_date + 30
796 and ppa.action_type in ('R','Q')
797 and ppa.last_update_date >=
798 greatest(cp_start_date,
799 fnd_date.canonical_to_date('2004/07/01 00:00:00'))
800 and ppa.consolidation_set_id like cp_cons_set_id
801 and ppa.payroll_id like cp_payroll_id
802 and ppa.payroll_action_id = paa.payroll_action_id
803 and paa.source_action_id is not null
804 and paa.action_status = 'C'
805 and not exists
806 (select 1
807 from pay_action_interlocks pai,
808 pay_assignment_actions paa1,
809 pay_payroll_actions ppa1
810 where pai.locked_action_id = paa.assignment_action_id
811 and paa1.assignment_action_id = pai.locking_action_id
812 and ppa1.payroll_action_id = paa1.payroll_action_id
813 and ppa1.action_type = 'V'
814 )
815 order by 1, 2;
816
817 cursor c_get_jurisduction_code(cp_assignment_id in number
818 ,cp_effective_date in date) is
819 select distinct state_code
820 from pay_us_emp_state_tax_rules_f pest
821 where pest.assignment_id = cp_assignment_id
822 and cp_effective_date between pest.effective_Start_date
823 and pest.effective_end_Date;
824
825 cursor c_get_latest_action(cp_assignment_id number
826 ,cp_tax_unit_id number) is
827 select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
828 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
829 paa.assignment_action_id, ppa.effective_date,
830 ppa.payroll_id, ppa.consolidation_set_id
831 from pay_assignment_actions paa,
832 pay_payroll_actions ppa
833 where paa.assignment_id = cp_assignment_id
834 and paa.tax_unit_id = cp_tax_unit_id
835 and paa.payroll_action_id = ppa.payroll_action_id
836 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
837 and ppa.effective_date between to_date('2004/01/01', 'yyyy/mm/dd')
838 and to_date('2004/12/31', 'yyyy/mm/dd')
839 order by paa.action_sequence desc;
840
841 cursor c_get_state_code(cp_state_abbrev varchar2) is
842 select state_code from pay_us_states
843 where state_Abbrev = cp_state_abbrev;
844
845
846 ld_adj_end_date DATE;
847 ld_adj_start_date DATE;
848 ln_adj_business_group_id NUMBER;
849 lv_adj_cons_set_id VARCHAR2(50);
850 lv_adj_payroll_id VARCHAR2(50);
851 lv_state_abbrev VARCHAR2(10);
852 ln_adj_action_id NUMBER;
853
854 ln_run_assignment_id NUMBER;
855 ln_run_tax_unit_id NUMBER;
856 ld_run_effective_date DATE;
857 ln_run_payroll_id NUMBER;
858 ln_run_consolidation_id NUMBER;
859 ln_run_action_id NUMBER;
860
861 ln_run_prv_assignment_id NUMBER;
862 ln_run_prv_tax_unit_id NUMBER;
863
864 lv_state_code VARCHAR2(10);
865 lv_adj_flag VARCHAR2(1);
866
867 ln_max_run_action_id NUMBER;
868
869 lv_serial_number VARCHAR2(30);
870 lv_procedure_name VARCHAR2(100);
871 lv_error_message VARCHAR2(200);
872 ln_step NUMBER;
873
874 BEGIN
875 lv_procedure_name := '.action_creation';
876 ln_run_prv_assignment_id := -1;
877 ln_run_prv_tax_unit_id := -1;
878 lv_adj_flag := 'N';
879
880 ln_step := 1;
881 hr_utility.set_location(gv_package || lv_procedure_name, 10);
882 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
883 ,p_start_date => ld_adj_start_date
884 ,p_end_date => ld_adj_end_date
885 ,p_business_group_id => ln_adj_business_group_id
886 ,p_state_abbrev => lv_state_abbrev
887 ,p_cons_set_id => lv_adj_cons_set_id
888 ,p_payroll_id => lv_adj_payroll_id);
889 hr_utility.set_location(gv_package || lv_procedure_name, 20);
890
891 if lv_adj_cons_set_id is null then
892 lv_adj_cons_set_id := '%';
893 end if;
894
895 if lv_adj_payroll_id is null then
896 lv_adj_payroll_id := '%';
897 end if;
898
899
900 pay_us_payroll_utils.populate_jit_information(
901 p_effective_date => ld_adj_start_date
902 ,p_get_state => 'Y');
903
904 ln_step := 2;
905 if lv_state_abbrev is null then
906 open c_get_emp( p_start_person_id
907 ,p_end_person_id
908 ,lv_adj_cons_set_id
909 ,lv_adj_payroll_id
910 ,ln_adj_business_group_id
911 ,ld_adj_start_date
912 ,ld_adj_end_date);
913 else
914 open c_get_state_code(lv_state_abbrev);
915 fetch c_get_state_code into lv_state_code;
916 close c_get_state_code;
917
918 open c_get_emp_state(p_start_person_id
919 ,p_end_person_id
920 ,lv_adj_cons_set_id
921 ,lv_adj_payroll_id
922 ,ln_adj_business_group_id
923 ,ld_adj_start_date
924 ,ld_adj_end_date
925 ,lv_state_code);
926 end if;
927
928
929 -- Loop for all rows returned for SQL statement.
930 hr_utility.set_location(gv_package || lv_procedure_name, 30);
931 loop
932 if lv_state_abbrev is null then
933 fetch c_get_emp into ln_run_tax_unit_id,
934 ln_run_assignment_id,
935 ld_run_effective_date;
936
937 exit when c_get_emp%notfound;
938 else
939 fetch c_get_emp_state into ln_run_tax_unit_id,
940 ln_run_assignment_id,
941 ld_run_effective_date;
942
943 exit when c_get_emp_state%notfound;
944 end if;
945
946 hr_utility.set_location(gv_package || lv_procedure_name, 40);
947 hr_utility.trace('Adj Flag = ' || lv_adj_flag);
948 hr_utility.trace('AsgID = ' ||
949 ln_run_assignment_id||'/'||ln_run_prv_assignment_id);
950 hr_utility.trace('Tax Unit ID = ' ||
951 ln_run_tax_unit_id||'/'||ln_run_prv_tax_unit_id);
952 hr_utility.trace('Payroll ID = ' || ln_run_payroll_id);
953
954 if (ln_run_assignment_id = ln_run_prv_assignment_id and
955 ln_run_tax_unit_id = ln_run_prv_tax_unit_id) then
956
957 hr_utility.set_location(gv_package || lv_procedure_name, 41);
958
959 else
960
961 hr_utility.set_location(gv_package || lv_procedure_name, 45);
962 lv_adj_flag := 'N';
963 ln_run_prv_assignment_id := ln_run_assignment_id;
964 ln_run_prv_tax_unit_id := ln_run_tax_unit_id;
965
966 if lv_state_abbrev is null then
967 open c_get_jurisduction_code(ln_run_assignment_id
968 ,ld_run_effective_date);
969 loop
970 fetch c_get_jurisduction_code into lv_state_code;
971 if c_get_jurisduction_code%notfound then
972 exit;
973 end if;
974
975 hr_utility.trace('SIT Exists = ' ||
976 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sit_exists);
977 hr_utility.trace('SDI ER Exists = ' ||
978 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit);
979 /* Create an action if the employee is in a state which does not
980 have SIT or SDI Er taxes or if the employee is in MA */
981 if pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sit_exists
982 = 'N' or
983 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit
984 is null or
985 lv_state_code = '22' then
986 hr_utility.set_location(gv_package || lv_procedure_name, 50);
987 lv_adj_flag := 'Y';
988 exit;
989 end if;
990 end loop;
991 close c_get_jurisduction_code;
992 else
993 lv_adj_flag := 'Y';
994 end if;
995
996 hr_utility.trace('Adj Flag = '||lv_adj_flag );
997 if lv_adj_flag = 'Y' then
998 hr_utility.set_location(gv_package || lv_procedure_name, 60);
999
1000 select pay_assignment_actions_s.nextval
1001 into ln_adj_action_id
1002 from dual;
1003
1004 -- insert into pay_assignment_actions.
1005 hr_nonrun_asact.insact(ln_adj_action_id,
1006 ln_run_assignment_id,
1007 p_payroll_action_id,
1008 p_chunk,
1009 ln_run_tax_unit_id,
1010 null,
1011 'U',
1012 null);
1013 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1014 hr_utility.trace('ln_run_action_id = ' || ln_run_action_id);
1015 hr_utility.trace('ln_adj_action_id = ' || ln_adj_action_id);
1016 hr_utility.trace('p_payroll_action_id = ' || p_payroll_action_id);
1017 hr_utility.trace('ln_run_tax_unit_id = ' || ln_run_tax_unit_id);
1018 hr_utility.set_location(gv_package || lv_procedure_name, 80);
1019
1020 open c_get_latest_action(ln_run_assignment_id
1021 ,ln_run_tax_unit_id);
1022 fetch c_get_latest_action into ln_max_run_action_id
1023 ,ld_run_effective_date
1024 ,ln_run_payroll_id
1025 ,ln_run_consolidation_id;
1026 close c_get_latest_action;
1027
1028 lv_serial_number := to_char(ld_run_effective_Date,'ddmmyyyy') ||
1029 ln_max_run_action_id;
1030
1031 hr_utility.trace('Update Serail Number = ' || lv_serial_number);
1032 update pay_assignment_actions
1033 set serial_number = lv_serial_number
1034 where assignment_action_id = ln_adj_action_id;
1035
1036 hr_utility.trace('Insert into temp table ');
1037 insert into pay_us_rpt_totals
1038 (location_id, organization_id, tax_unit_id,
1039 value1, value3)
1040 select
1041 p_payroll_action_id,
1042 to_char(ld_run_effective_date, 'ddmmyyyy'),
1043 ln_run_payroll_id,
1044 ln_run_consolidation_id,
1045 ln_adj_business_group_id
1046 from dual
1047 where not exists
1048 (select 1 from pay_us_rpt_totals
1049 where location_id = p_payroll_action_id
1050 and tax_unit_id = ln_run_payroll_id
1051 and value1 = ln_run_consolidation_id
1052 and organization_id
1053 = to_char(ld_run_effective_date, 'ddmmyyyy'));
1054
1055 end if;
1056
1057 end if;
1058
1059 end loop;
1060 if lv_state_abbrev is null then
1061 close c_get_emp;
1062 else
1063 close c_get_emp_state;
1064 end if;
1065
1066 ln_step := 5;
1067 hr_utility.set_location(gv_package || lv_procedure_name, 300);
1068
1069 EXCEPTION
1070 when others then
1071 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1072 gv_package || lv_procedure_name;
1073
1074 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1075
1076 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1077 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1078 hr_utility.raise_error;
1079
1080 END action_creation;
1081
1082
1083 PROCEDURE process_min_chunk(p_payroll_action_id in number
1084 ,p_chunk_number in number
1085 )
1086 IS
1087 --
1088 lr_rowid ROWID;
1089
1090 ld_run_effective_date DATE;
1091 ln_run_payroll_id NUMBER;
1092 ln_run_consolidation_id NUMBER;
1093
1094 ld_run_prv_effective_date DATE;
1095 ln_run_prv_payroll_id NUMBER;
1096 ln_run_prv_consolidation_id NUMBER;
1097
1098 ln_payroll_action_id NUMBER;
1099
1100 ln_exists NUMBER;
1101 lv_error_message VARCHAR2(500);
1102 lv_procedure_name VARCHAR2(100);
1103 ln_step NUMBER;
1104 --
1105 cursor c_get_adj_dates (cp_payroll_action_id in number) is
1106 select prt.rowid,
1107 to_date(lpad(to_char(prt.organization_id),8,'0'),'ddmmyyyy'),
1108 tax_unit_id,
1109 value1
1110 from pay_us_rpt_totals prt
1111 where prt.location_id = cp_payroll_action_id
1112 and prt.value2 is null
1113 order by to_date(lpad(to_char(prt.organization_id),8,'0'),'ddmmyyyy'),
1114 tax_unit_id, value1;
1115
1116 cursor c_get_action_info (cp_payroll_action_id in number) is
1117 select 1
1118 from pay_action_information
1119 where action_information1 = 'C'
1120 and action_context_id = cp_payroll_action_id
1121 and action_context_type = 'PPA';
1122 --
1123 BEGIN
1124 --
1125 hr_utility.set_location(gv_package || lv_procedure_name, 1);
1126 lv_procedure_name := '.process_min_chunk';
1127 ld_run_prv_effective_date := to_date('1800/01/01', 'yyyy/mm/dd');
1128 ln_run_prv_payroll_id := -1;
1129 ln_run_prv_consolidation_id := -1;
1130
1131 /* Check the pay_action_infor table to see if the pay_us_rpt_totals
1132 ** has been cleaned up and payroll actions for them started.
1133 ** If the status is not 'C' then it could mean -
1134 ** 1) this is the first time it has been called
1135 ** 2) the first assignment action for the chunk has errored
1136 ** so everything is rolled back.
1137 ** Need to do the cleanup and opening of balance adjustment payroll
1138 ** action if we hit the latter case. */
1139 open c_get_action_info(p_payroll_action_id);
1140 fetch c_get_action_info into ln_exists;
1141 if c_get_action_info%notfound then
1142 g_proc_init := FALSE;
1143 end if;
1144 close c_get_action_info;
1145
1146 if (g_proc_init = FALSE) then
1147
1148 if p_chunk_number = g_min_chunk then
1149 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1150 open c_get_adj_dates(p_payroll_action_id);
1151 loop
1152 fetch c_get_adj_dates into lr_rowid,
1153 ld_run_effective_date,
1154 ln_run_payroll_id,
1155 ln_run_consolidation_id;
1156 if c_get_adj_dates%notfound then
1157 exit;
1158 end if;
1159
1160 hr_utility.trace('Effective Date = ' ||
1161 ld_run_effective_date||'/'||ld_run_prv_effective_date);
1162 hr_utility.trace('Payroll ID = ' ||
1163 ln_run_payroll_id||'/'||ln_run_prv_payroll_id);
1164 hr_utility.trace('Consolidation Set ID = ' ||
1165 ln_run_consolidation_id||'/'||ln_run_prv_consolidation_id);
1166 if (ld_run_effective_date <> ld_run_prv_effective_date or
1167 ln_run_prv_payroll_id <> ln_run_payroll_id or
1168 ln_run_prv_consolidation_id <> ln_run_consolidation_id) then
1169
1170 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1171 ld_run_prv_effective_date := ld_run_effective_date;
1172 ln_run_prv_payroll_id := ln_run_payroll_id;
1173 ln_run_prv_consolidation_id := ln_run_consolidation_id;
1174
1175 ln_payroll_action_id
1176 := pay_bal_adjust.init_batch(
1177 p_payroll_id => ln_run_payroll_id,
1178 p_batch_mode => 'STANDARD',
1179 p_effective_date => ld_run_effective_date,
1180 p_consolidation_set_id => ln_run_consolidation_id,
1181 p_prepay_flag => 'N');
1182 update pay_us_rpt_totals
1183 set value2 = ln_payroll_action_id
1184 where rowid = lr_rowid;
1185 else
1186 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1187 delete from pay_us_rpt_totals
1188 where rowid = lr_rowid;
1189 end if;
1190 end loop;
1191 close c_get_adj_dates;
1192
1193 update pay_action_information
1194 set ACTION_INFORMATION1 = 'C'
1195 where ACTION_CONTEXT_ID = p_payroll_action_id
1196 and ACTION_CONTEXT_TYPE = 'PPA';
1197
1198 --
1199 else
1200 --
1201 -- OK, we're not chunk 1 we need to wait.
1202 --
1203 declare
1204 complete_status boolean;
1205 status pay_action_information.action_information1%type;
1206 begin
1207 --
1208 complete_status := FALSE;
1209 while (complete_status = FALSE) loop
1210 --
1211 select ACTION_INFORMATION1
1212 into status
1213 from pay_action_information
1214 where ACTION_CONTEXT_ID = p_payroll_action_id
1215 and ACTION_CONTEXT_TYPE = 'PPA';
1216 --
1217 if (status = 'C') then
1218 complete_status := TRUE;
1219 else
1220 dbms_lock.sleep(5);
1221 end if;
1222 --
1223 end loop;
1224 --
1225 end;
1226 --
1227 end if;
1228 --
1229 g_proc_init := TRUE;
1230 --
1231 end if;
1232 --
1233 END process_min_chunk;
1234
1235 /******************************************************************
1236 Name : initialize
1237 Purpose : This performs the context initialization.
1238 Arguments :
1239 Notes :
1240 *******************************************************************/
1241 PROCEDURE initialize(
1242 p_payroll_action_id in number) is
1243
1244 ld_adj_start_date DATE;
1245 ld_adj_end_date DATE;
1246 ln_adj_business_group_id NUMBER;
1247 lv_adj_cons_set_id NUMBER;
1248 lv_adj_payroll_id NUMBER;
1249 lv_adj_State_abbrev VARCHAR2(10);
1250
1251 lv_error_message VARCHAR2(500);
1252 lv_procedure_name VARCHAR2(100);
1253 ln_step NUMBER;
1254
1255 cursor c_get_state_code(cp_state_abbrev varchar2) is
1256 select state_code from pay_us_states
1257 where state_Abbrev = cp_state_abbrev;
1258
1259 cursor c_get_chunk_date(cp_payroll_action_id in number) is
1260 select min(paa.chunk_number)
1261 from pay_assignment_actions paa,
1262 pay_payroll_actions ppa
1263 where ppa.payroll_action_id = paa.payroll_Action_id
1264 and ppa.payroll_action_id = cp_payroll_action_id;
1265
1266 BEGIN
1267 hr_utility.set_location(gv_package || lv_procedure_name, 1);
1268 lv_procedure_name := '.initialize';
1269 ln_step := 1;
1270
1271 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1272 ,p_start_date => ld_adj_start_date
1273 ,p_end_date => ld_adj_end_date
1274 ,p_business_group_id => ln_adj_business_group_id
1275 ,p_state_abbrev => lv_adj_State_abbrev
1276 ,p_cons_set_id => lv_adj_cons_set_id
1277 ,p_payroll_id => lv_adj_payroll_id);
1278
1279 open c_get_state_code(lv_adj_State_abbrev);
1280 fetch c_get_state_code into g_adj_state_code;
1281 close c_get_state_code;
1282
1283 open c_get_chunk_date(p_payroll_action_id);
1284 fetch c_get_chunk_date into g_min_chunk;
1285 ln_step := 2;
1286 if c_get_chunk_date%notfound then
1287 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1288 lv_error_message := 'No Assignment Actions were picked by ' ||
1289 'the Process.';
1290
1291 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1292 hr_utility.set_message_token('FORMULA_TEXT',lv_error_message);
1293 end if;
1294 close c_get_chunk_date;
1295
1296 -- initialize pl/sql table
1297 initialize_plsql_table(ld_adj_start_date,
1298 ln_adj_business_group_id);
1299
1300 /* Populate JIT information which is used when doing
1301 balance adjustment */
1302 ln_step := 5;
1303 hr_utility.set_location(gv_package || lv_procedure_name, 15);
1304 pay_us_payroll_utils.populate_jit_information(
1305 p_effective_date => ld_adj_start_date
1306 ,p_get_state => 'Y');
1307
1308 ln_step := 8;
1309
1310 exception
1311 when others then
1312 hr_utility.set_location(gv_package || lv_procedure_name, 500);
1313 lv_error_message := 'Error at step ' || ln_step ||
1314 ' in ' || gv_package || lv_procedure_name;
1315 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1316
1317 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1318 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1319 hr_utility.raise_error;
1320
1321 END initialize;
1322
1323
1324 /******************************************************************
1325 Name : deinitialize
1326 Purpose : This is the last procedure to be called by PYUGEN.
1327 Arguments :
1328 Notes :
1329 *******************************************************************/
1330 PROCEDURE deinitialize(
1331 p_payroll_action_id in number)
1332 IS
1333
1334 ln_count_incomplete_actions NUMBER;
1335 ln_badj_payroll_Action_id NUMBER;
1336
1337 cursor c_get_badj_payroll_action (cp_payroll_action_id in number) is
1338 select prt.value2
1339 from pay_us_rpt_totals prt
1340 where prt.location_id = cp_payroll_action_id;
1341
1342 BEGIN
1343
1344 select count(*)
1345 into ln_count_incomplete_actions
1346 from pay_assignment_actions
1347 where payroll_action_id = p_payroll_action_id
1348 and action_status <> 'C';
1349
1350 if ln_count_incomplete_actions = 0 then
1351
1352 open c_get_badj_payroll_action(p_payroll_action_id);
1353 loop
1354 fetch c_get_badj_payroll_action into ln_badj_payroll_Action_id;
1355 if c_get_badj_payroll_action%notfound then
1356 exit;
1357 end if;
1358
1359 if ln_badj_payroll_Action_id is not null then
1360 pay_bal_adjust.process_batch(ln_badj_payroll_Action_id);
1361 end if;
1362
1363 end loop;
1364 close c_get_badj_payroll_action;
1365
1366
1367 delete from pay_us_rpt_totals
1368 where location_id = p_payroll_action_id;
1369
1370 delete from pay_action_information
1371 where ACTION_CONTEXT_ID = p_payroll_action_id
1372 and ACTION_CONTEXT_TYPE = 'PPA';
1373
1374 pay_archive.remove_report_actions(p_payroll_action_id);
1375
1376 end if;
1377
1378 END deinitialize;
1379
1380
1381 PROCEDURE run_preprocess(
1382 p_assignment_action_id number
1383 ,p_effective_date in date)
1384 IS
1385 l_payroll_action_id pay_assignment_actions.payroll_action_id%type;
1386 l_chunk_number pay_assignment_actions.chunk_number%type;
1387
1388 cursor cur_assignment_action_info
1389 (cp_assignment_action_id in number) is
1390 select
1391 to_date(substr(paa.serial_number,1,8),'ddmmyyyy') sort_date,
1392 paa.assignment_id,
1393 paa.tax_unit_id,
1394 paa.payroll_action_id,
1395 to_number(substr(paa.serial_number,9)) bal_asg_action_id
1396 from pay_assignment_actions paa
1397 where paa.assignment_action_id = cp_assignment_action_id;
1398
1399 cursor get_asg_run_info(cp_run_action_id in number) is
1400 select payroll_id, consolidation_set_id
1401 from pay_payroll_actions ppa
1402 ,pay_assignment_actions paa
1403 where ppa.payroll_Action_id = paa.payroll_action_id
1404 and paa.assignment_action_id = cp_run_action_id;
1405
1406 cursor csr_chk_state(cp_assignment_id in number
1407 ,cp_effective_date in date
1408 ,cp_where_state_code in varchar2) IS
1409 select st.state_code, st.jurisdiction_code
1410 from pay_us_emp_state_tax_rules_f st
1411 where st.assignment_id = cp_assignment_id
1412 and st.state_code like cp_where_state_code
1413 and cp_effective_date between st.effective_start_date
1414 and st.effective_end_date;
1415
1416 cursor csr_get_badj_action(cp_payroll_action_id in number
1417 ,cp_badj_effective_Date in date
1418 ,cp_run_payroll_id in number
1419 ,cp_consolidation_id in number) IS
1420 select value2 badj_payroll_Action,
1421 value3 business_group_id
1422 from pay_us_rpt_totals prt
1423 where prt.location_id = cp_payroll_action_id
1424 and to_date(lpad(to_char(prt.organization_id),8,'0'),'ddmmyyyy')
1425 = cp_badj_effective_date
1426 and tax_unit_id = cp_run_payroll_id
1427 and value1 = cp_consolidation_id;
1428
1429 cursor c_get_per_latest_action(cp_assignment_id number
1430 ,cp_tax_unit_id number) is
1431 select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
1432 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
1433 paa.assignment_action_id
1434 from pay_assignment_actions paa,
1435 per_all_assignments_f paf,
1436 per_all_assignments_f paf1,
1437 pay_payroll_actions ppa
1438 where paf1.assignment_id = cp_assignment_id
1439 and paf.person_id = paf1.person_id
1440 and paa.assignment_id = paf.assignment_id
1441 and paa.tax_unit_id = cp_tax_unit_id
1442 and paa.payroll_action_id = ppa.payroll_action_id
1443 and ppa.action_type in ('R', 'Q', 'B', 'V', 'I')
1444 and ppa.effective_date between paf.effective_start_date
1445 and paf.effective_end_date
1446 and ppa.effective_date between to_date('2004/01/01', 'yyyy/mm/dd')
1447 and to_date('2004/12/31', 'yyyy/mm/dd')
1448 order by paa.action_sequence desc;
1449
1450 ld_run_effective_date DATE;
1451 ln_assignment_id NUMBER;
1452 ln_tax_unit_id NUMBER;
1453 ln_bal_asg_action_id NUMBER;
1454 ln_adj_payroll_action_id NUMBER;
1455 lv_adj_state_abbrev VARCHAR2(2);
1456
1457 ln_run_payroll_id NUMBER;
1458 ln_run_consolidation_id NUMBER;
1459
1460
1461 ln_business_group_id NUMBER;
1462 ln_badj_payroll_action_id NUMBER;
1463
1464 ln_per_max_run_action_id NUMBER;
1465
1466
1467 lv_where_state_code VARCHAR2(2);
1468 lv_state_code VARCHAR2(2);
1469 lv_jurisdiction VARCHAR2(11);
1470 lv_sit_exists VARCHAR2(2);
1471
1472 lv_balance_name VARCHAR2(80);
1473 lv_element_name VARCHAR2(80);
1474 ln_element_type_id NUMBER;
1475 lv_input_name VARCHAR2(80);
1476 ln_ytd_def_bal_id NUMBER;
1477 ln_input_value_id NUMBER;
1478
1479
1480 lv_procedure_name VARCHAR2(100);
1481 lv_error_message VARCHAR2(200);
1482 ln_step NUMBER;
1483
1484 ln_bal_value NUMBER;
1485 ln_badj_bal_value NUMBER;
1486 ln_taxable_bal_value NUMBER;
1487 ln_redu_subj_bal_value NUMBER;
1488 ln_subj_bal_value NUMBER;
1489 ln_pre_tax_bal_value NUMBER;
1490 ln_sdi_er_wage_limit NUMBER;
1491 lv_sit_adj_flag VARCHAR2(1);
1492 lv_sdi_adj_flag VARCHAR2(1);
1493
1494 ln_sub_ele_link_id NUMBER;
1495 ln_sub2_ele_link_id NUMBER;
1496 ln_misc_ele_link_id NUMBER;
1497
1498 --type inp_val_table IS TABLE of pay_input_values.input_value_id%type
1499 --index by binary_integer;
1500
1501 --type entry_val_table is table of varchar2(80)
1502 -- index by binary_integer;
1503
1504 sub_input_value_table hr_entry.number_table;
1505 sub2_input_value_table hr_entry.number_table;
1506 misc_input_value_table hr_entry.number_table;
1507
1508 sub_entry_value_table hr_entry.varchar2_table;
1509 sub2_entry_value_table hr_entry.varchar2_table;
1510 misc_entry_value_table hr_entry.varchar2_table;
1511
1512 ln_count1 NUMBER;
1513 ln_count2 NUMBER;
1514 ln_count3 NUMBER;
1515 lv_sub2_jd_flag VARCHAR2(1) ;
1516 lv_sub_jd_flag VARCHAR2(1) ;
1517 lv_misc_jd_flag VARCHAR2(1) ;
1518 BEGIN
1519 hr_utility.set_location(gv_package || lv_procedure_name, 5);
1520 lv_procedure_name := '.preprocess_run';
1521 ln_bal_value := 0;
1522 ln_badj_bal_value := 0;
1523
1524 ln_assignment_id := -1;
1525 ln_tax_unit_id := -1;
1526 ln_adj_payroll_action_id := -1;
1527 ln_bal_asg_action_id := -1;
1528
1529 ln_ytd_def_bal_id := -1;
1530 ln_input_value_id := -1;
1531
1532 select payroll_action_id,
1533 chunk_number
1534 into l_payroll_action_id,
1535 l_chunk_number
1536 from pay_assignment_actions
1537 where assignment_action_id = p_assignment_action_id;
1538 --
1539 process_min_chunk(p_payroll_action_id => l_payroll_action_id,
1540 p_chunk_number => l_chunk_number);
1541 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1542
1543 --
1544 -- The data in pay_us_rpt_totals has been cleanup, so start
1545 -- the balance adjustment process
1546 lv_sit_adj_flag := 'N';
1547 lv_sdi_adj_flag := 'N';
1548 ln_step := 1;
1549
1550 open cur_assignment_action_info(p_assignment_action_id);
1551 fetch cur_assignment_action_info into
1552 ld_run_effective_date,
1553 ln_assignment_id,
1554 ln_tax_unit_id,
1555 ln_adj_payroll_action_id,
1556 ln_bal_asg_action_id;
1557 close cur_assignment_action_info;
1558
1559 open get_asg_run_info(ln_bal_asg_action_id);
1560 fetch get_asg_run_info into ln_run_payroll_id, ln_run_consolidation_id;
1561 if get_asg_run_info%notfound then
1562 hr_utility.trace('Payroll and Consolidation Set Not Found for Run');
1563 hr_utility.raise_error;
1564 end if;
1565 close get_asg_run_info;
1566
1567 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1568 hr_utility.trace('ln_assignment_id = ' || ln_assignment_id);
1569 hr_utility.trace('ln_run_payroll_id = '|| ln_run_payroll_id);
1570 hr_utility.trace('ln_run_consolidation_id = '
1571 || ln_run_consolidation_id);
1572
1573 -- get New Payroll Action ID and Business Group ID
1574 -- for the given sort_date, Payroll and Consolidation Set
1575 open csr_get_badj_action(ln_adj_payroll_action_id
1576 ,ld_run_effective_date
1577 ,ln_run_payroll_id
1578 ,ln_run_consolidation_id);
1579 fetch csr_get_badj_action into ln_badj_payroll_action_id,
1580 ln_business_group_id;
1581 if csr_get_badj_action%notfound then
1582 hr_utility.trace('ERROR:No Payrol_action_ID found for Sort_Date');
1583 hr_utility.raise_error;
1584 end if;
1585 close csr_get_badj_action;
1586
1587 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1588 hr_utility.trace('ln_badj_payroll_action_id = ' ||
1589 ln_badj_payroll_action_id);
1590 hr_utility.trace('ln_business_group_id = ' ||
1591 ln_business_group_id);
1592
1593 -- get assignment derived jurisdiction for state
1594 if g_adj_state_code is null then
1595 lv_where_state_code := '%';
1596 else
1597 lv_where_state_code := g_adj_state_code;
1598 end if;
1599 hr_utility.set_location(gv_package || lv_procedure_name, 51);
1600 open csr_chk_state(ln_assignment_id,
1601 ld_run_effective_date,
1602 lv_where_state_code);
1603 loop
1604 hr_utility.set_location(gv_package || lv_procedure_name, 52);
1605 fetch csr_chk_state into lv_state_code,
1606 lv_jurisdiction;
1607
1608 exit when csr_chk_state%NOTFOUND;
1609
1610 ln_bal_value := 0;
1611 ln_badj_bal_value := 0;
1612 lv_misc_jd_flag := 'N';
1613 lv_sub2_jd_flag := 'N';
1614 lv_sub_jd_flag := 'N';
1615
1616 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1617 hr_utility.trace('lv_state_code = ' || lv_state_code);
1618 hr_utility.trace('Jurisdiction = ' || lv_jurisdiction);
1619
1620 -- Set Context : TAX_UNIT_ID
1621 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
1622 -- Set Context : JURISDICTION_CODE
1623 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction);
1624
1625
1626 if g_adj_state_code is null then
1627 lv_sit_exists
1628 := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sit_exists;
1629 else
1630 lv_sit_exists
1631 := pay_get_tax_exists_pkg.get_tax_exists (
1632 p_juri_code => lv_state_code
1633 ,p_date_earned => ld_run_effective_date
1634 ,p_tax_unit_id => ln_tax_unit_id
1635 ,p_assign_id => ln_assignment_id
1636 ,p_type => 'SIT_RS');
1637 end if;
1638 hr_utility.trace('SIT_Exists = ' || lv_sit_exists);
1639
1640 if lv_sit_exists = 'N' then
1641 if pay_us_emp_baladj_cleanup.ltr_sit_tax_bal.count > 0 then
1642 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1643 lv_balance_name
1644 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(0).balance_name;
1645 ln_ytd_def_bal_id
1646 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(0).ytd_def_bal_id;
1647 lv_element_name
1648 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(0).element_name;
1649 ln_element_type_id
1650 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(0).element_type_id;
1651 lv_input_name
1652 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(0).input_name;
1653 ln_input_value_id
1654 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(0).input_value_id;
1655 hr_utility.trace('lv_balance_name =' || lv_balance_name);
1656 hr_utility.trace('ln_ytd_def_bal_id =' || ln_ytd_def_bal_id);
1657 hr_utility.trace('lv_element_name=' || lv_element_name);
1658 hr_utility.trace('lv_input_name=' || lv_input_name);
1659 hr_utility.trace('ln_input_value_id =' || ln_input_value_id);
1660
1661 ln_bal_value := 0;
1662 ln_bal_value := get_balance_value(
1663 p_defined_balance_id => ln_ytd_def_bal_id
1664 ,p_balcall_aaid => ln_bal_asg_action_id);
1665
1666 IF ln_bal_value <> 0 THEN
1667 for j in pay_us_emp_baladj_cleanup.ltr_sit_tax_bal.first..
1668 pay_us_emp_baladj_cleanup.ltr_sit_tax_bal.last loop
1669
1670 if j > 0 then
1671 lv_balance_name
1672 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(j).balance_name;
1673 ln_ytd_def_bal_id
1674 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(j).ytd_def_bal_id;
1675 lv_element_name
1676 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(j).element_name;
1677 ln_element_type_id
1678 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(j).element_type_id;
1679 lv_input_name
1680 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(j).input_name;
1681 ln_input_value_id
1682 := pay_us_emp_baladj_cleanup.ltr_sit_tax_bal(j).input_value_id;
1683
1684 hr_utility.trace('lv_balance_name =' || lv_balance_name);
1685 hr_utility.trace('ln_ytd_def_bal_id =' || ln_ytd_def_bal_id);
1686 hr_utility.trace('lv_element_name=' || lv_element_name);
1687 hr_utility.trace('lv_input_name=' || lv_input_name);
1688 hr_utility.trace('ln_input_value_id =' || ln_input_value_id);
1689
1690 ln_bal_value := 0;
1691 ln_bal_value := get_balance_value(
1692 p_defined_balance_id => ln_ytd_def_bal_id
1693 ,p_balcall_aaid => ln_bal_asg_action_id);
1694 end if;
1695 --****************************************************
1696 -- Keeping this outside the above if statement as the
1697 -- call for
1698 -- Gross is done outside the pl/sql table loop but process
1699 -- elements needs to be called for Gross as well as other
1700 -- balances
1701 --****************************************************
1702 ln_badj_bal_value := ln_bal_value * (-1);
1703
1704 if substr(lv_element_name,1,12) = 'SIT_SUBJECT2' then
1705 if lv_sub2_jd_flag = 'N' then
1706 ln_count2 := sub2_input_value_table.count+1;
1707 --Set Input Value of Jurisdiction
1708 sub2_input_value_table(ln_count2)
1709 := pay_us_emp_baladj_cleanup.get_input_value_id(
1710 p_element_type_id => ln_element_type_id
1711 ,p_input_value_name => 'Jurisdiction'
1712 ,p_effective_date => ld_run_effective_date);
1713 sub2_entry_value_table(ln_count2) := lv_jurisdiction;
1714 lv_sub2_jd_flag := 'Y';
1715 end if;
1716
1717 ln_count2 := sub2_input_value_table.count+1;
1718 sub2_input_value_table(ln_count2) := ln_input_value_id;
1719 sub2_entry_value_table(ln_count2) := ln_badj_bal_value;
1720
1721 hr_utility.set_location(gv_package || lv_procedure_name, 90);
1722 ln_sub2_ele_link_id := hr_entry_api.get_link(
1723 p_assignment_id => ln_assignment_id,
1724 p_element_type_id => ln_element_type_id,
1725 p_session_date => ld_run_effective_date);
1726 hr_utility.trace('Link SIT SUBJ 2 ='||ln_sub2_ele_link_id);
1727
1728 IF (ln_sub2_ele_link_id IS NULL) THEN
1729 hr_utility.set_location(gv_package||lv_procedure_name, 110);
1730 hr_utility.set_message(801, 'PY_51132_TXADJ_LINK_MISSING');
1731 hr_utility.set_message_token ('ELEMENT', lv_element_name);
1732 hr_utility.raise_error;
1733 END IF;
1734
1735
1736 else -- SIT_SUBJECT
1737 hr_utility.set_location(gv_package || lv_procedure_name, 120);
1738 if lv_sub_jd_flag = 'N' then
1739 ln_count1 := sub_input_value_table.count+1;
1740 --Set Input Value of Jurisdiction
1741 sub_input_value_table(ln_count1)
1742 := pay_us_emp_baladj_cleanup.get_input_value_id(
1743 p_element_type_id => ln_element_type_id
1744 ,p_input_value_name => 'Jurisdiction'
1745 ,p_effective_date => ld_run_effective_date);
1746 sub_entry_value_table(ln_count1) := lv_jurisdiction;
1747 lv_sub_jd_flag := 'Y';
1748 end if;
1749 ln_count1 := sub_input_value_table.count+1;
1750 sub_input_value_table(ln_count1) := ln_input_value_id;
1751 sub_entry_value_table(ln_count1) := ln_badj_bal_value;
1752
1753 hr_utility.set_location(gv_package ||
1754 lv_procedure_name, 130);
1755 ln_sub_ele_link_id
1756 := hr_entry_api.get_link(
1757 p_assignment_id => ln_assignment_id,
1758 p_element_type_id => ln_element_type_id,
1759 p_session_date => ld_run_effective_date);
1760 hr_utility.trace('Link SIT SUBJ ='||
1761 ln_sub_ele_link_id);
1762 IF ln_sub_ele_link_id IS NULL THEN
1763 hr_utility.set_location(gv_package||
1764 lv_procedure_name,140);
1765 hr_utility.set_message(
1766 801, 'PY_51132_TXADJ_LINK_MISSING');
1767 hr_utility.set_message_token('ELEMENT',
1768 lv_element_name);
1769 hr_utility.raise_error;
1770 END IF;
1771
1772 end if;
1773
1774 end loop; -- of ltr_sit_tax_bal)
1775 END IF; -- (ln_gross <> 0)
1776 end if;
1777
1778
1779 hr_utility.set_location(gv_package||lv_procedure_name, 150);
1780 if sub2_entry_value_table.count > 0 then
1781 for i in sub2_entry_value_table.first ..
1782 sub2_entry_value_table.last loop
1783 hr_utility.trace('SIT2 entry' ||i||' = '||
1784 sub2_entry_value_table(i));
1785 end loop;
1786 for i in sub2_input_value_table.first ..
1787 sub2_input_value_table.last loop
1788 hr_utility.trace('SIT2 input'||i||' = '||
1789 sub2_input_value_table(i));
1790 end loop;
1791
1792 hr_utility.set_location(gv_package ||
1793 lv_procedure_name, 160);
1794 pay_bal_adjust.adjust_balance(
1795 p_batch_id => ln_badj_payroll_action_id,
1796 p_assignment_id => ln_assignment_id,
1797 p_element_link_id => ln_sub2_ele_link_id,
1798 p_num_entry_values => sub2_entry_value_table.count,
1799 p_entry_value_tbl => sub2_entry_value_table,
1800 p_input_value_id_tbl => sub2_input_value_table,
1801 p_balance_adj_cost_flag => 'N');
1802 end if;
1803 if sub_input_value_table.count > 0 then
1804 for i in sub_entry_value_table.first ..
1805 sub_entry_value_table.last loop
1806 hr_utility.trace('SIT entry' ||i||' = '||
1807 sub_entry_value_table(i));
1808 end loop;
1809 for i in sub_input_value_table.first ..
1810 sub_input_value_table.last loop
1811 hr_utility.trace('SIT input'||i||' = '||
1812 sub_input_value_table(i));
1813 end loop;
1814 hr_utility.set_location(gv_package ||
1815 lv_procedure_name, 170);
1816 pay_bal_adjust.adjust_balance(
1817 p_batch_id => ln_badj_payroll_action_id,
1818 p_assignment_id => ln_assignment_id,
1819 p_element_link_id => ln_sub_ele_link_id,
1820 p_num_entry_values => sub_entry_value_table.count,
1821 p_entry_value_tbl => sub_entry_value_table,
1822 p_input_value_id_tbl => sub_input_value_table,
1823 p_balance_adj_cost_flag => 'N');
1824 end if;
1825
1826 hr_utility.set_location(gv_package || lv_procedure_name, 200);
1827
1828 -- initialize tables
1829 sub_input_value_table.delete;
1830 sub2_input_value_table.delete;
1831 sub_entry_value_table.delete;
1832 sub2_entry_value_table.delete;
1833
1834 end if; -- sit exists
1835
1836 --------------------------------------------------------------
1837 --State is Not 'MA'
1838 --
1839 if lv_state_code <> '22' then
1840 --
1841 -- SDI ER
1842 --
1843 ln_bal_value := 0;
1844 ln_badj_bal_value := 0;
1845 lv_sub_jd_flag := 'N';
1846 lv_sub2_jd_flag := 'N';
1847 if pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit
1848 is null then
1849 hr_utility.set_location(gv_package || lv_procedure_name, 210);
1850 hr_utility.trace('Jurisdiction = ' || lv_jurisdiction);
1851 if pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal.count > 0 then
1852 lv_balance_name
1853 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(0).balance_name;
1854 ln_ytd_def_bal_id
1855 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(0).ytd_def_bal_id;
1856 lv_element_name
1857 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(0).element_name;
1858 ln_element_type_id
1859 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(0).element_type_id;
1860 lv_input_name
1861 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(0).input_name;
1862 ln_input_value_id
1863 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(0).input_value_id;
1864
1865 hr_utility.trace('lv_balance_name =' || lv_balance_name);
1866 hr_utility.trace('ln_ytd_def_bal_id =' || ln_ytd_def_bal_id);
1867 hr_utility.trace('lv_element_name=' || lv_element_name);
1868 hr_utility.trace('lv_input_name=' || lv_input_name);
1869 hr_utility.trace('ln_input_value_id =' || ln_input_value_id);
1870
1871 ln_bal_value := 0;
1872 ln_bal_value := get_balance_value(
1873 p_defined_balance_id => ln_ytd_def_bal_id
1874 ,p_balcall_aaid => ln_bal_asg_action_id);
1875
1876 IF ln_bal_value <> 0 THEN
1877 for k in pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal.first..
1878 pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal.last loop
1879 if k > 0 then
1880 lv_balance_name
1881 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(k).balance_name;
1882 ln_ytd_def_bal_id
1883 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(k).ytd_def_bal_id;
1884 lv_element_name
1885 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(k).element_name;
1886 ln_element_type_id
1887 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(k).element_type_id;
1888 lv_input_name
1889 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(k).input_name;
1890 ln_input_value_id
1891 := pay_us_emp_baladj_cleanup.ltr_sdi_er_tax_bal(k).input_value_id;
1892
1893 hr_utility.trace('lv_balance_name =' || lv_balance_name);
1894 hr_utility.trace('ln_ytd_def_bal_id =' || ln_ytd_def_bal_id);
1895 hr_utility.trace('lv_element_name=' || lv_element_name);
1896 hr_utility.trace('lv_input_name=' || lv_input_name);
1897 hr_utility.trace('ln_input_value_id =' || ln_input_value_id);
1898
1899 ln_bal_value := 0;
1900 ln_bal_value := get_balance_value(
1901 p_defined_balance_id => ln_ytd_def_bal_id
1902 ,p_balcall_aaid => ln_bal_asg_action_id);
1903 end if;
1904 ln_badj_bal_value := -1 * ln_bal_value;
1905
1906 if substr(lv_element_name,1,12) = 'SDI_SUBJECT2' then
1907
1908 if lv_sub2_jd_flag = 'N' then
1909 ln_count2 := sub2_input_value_table.count+1;
1910 --Set Input Value of Jurisdiction
1911 sub2_input_value_table(ln_count2)
1912 := pay_us_emp_baladj_cleanup.get_input_value_id(
1913 p_element_type_id => ln_element_type_id
1914 ,p_input_value_name => 'Jurisdiction'
1915 ,p_effective_date => ld_run_effective_date);
1916 sub2_entry_value_table(ln_count2) := lv_jurisdiction;
1917 lv_sub2_jd_flag := 'Y';
1918 end if;
1919
1920 ln_count2 := sub2_input_value_table.count+1;
1921 sub2_input_value_table(ln_count2) := ln_input_value_id;
1922 sub2_entry_value_table(ln_count2) := ln_badj_bal_value;
1923
1924 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1925
1926 ln_sub2_ele_link_id := hr_entry_api.get_link(
1927 p_assignment_id => ln_assignment_id,
1928 p_element_type_id => ln_element_type_id,
1929 p_session_date => ld_run_effective_date);
1930 hr_utility.trace('Link SDI ER SUBJ 2 ='||ln_sub2_ele_link_id);
1931
1932 IF (ln_sub2_ele_link_id IS NULL) THEN
1933 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1934 hr_utility.set_message(801, 'PY_51132_TXADJ_LINK_MISSING');
1935 hr_utility.set_message_token ('ELEMENT', lv_element_name);
1936 hr_utility.raise_error;
1937 END IF;
1938
1939
1940 else
1941 if lv_sub_jd_flag = 'N' then
1942 ln_count1 := sub_input_value_table.count+1;
1943 --Set Input Value of Jurisdiction
1944 sub_input_value_table(ln_count1)
1945 := pay_us_emp_baladj_cleanup.get_input_value_id(
1946 p_element_type_id => ln_element_type_id
1947 ,p_input_value_name => 'Jurisdiction'
1948 ,p_effective_date => ld_run_effective_date);
1949 sub_entry_value_table(ln_count1) := lv_jurisdiction;
1950 lv_sub_jd_flag := 'Y';
1951 end if;
1952 ln_count1 := sub_input_value_table.count+1;
1953 sub_input_value_table(ln_count1) := ln_input_value_id;
1954 sub_entry_value_table(ln_count1) := ln_badj_bal_value;
1955
1956 hr_utility.set_location(gv_package || lv_procedure_name, 250);
1957 ln_sub_ele_link_id := hr_entry_api.get_link(
1958 p_assignment_id => ln_assignment_id,
1959 p_element_type_id => ln_element_type_id,
1960 p_session_date => ld_run_effective_date);
1961 hr_utility.trace('Link SDI ER SUBJ ='||ln_sub_ele_link_id);
1962 IF (ln_sub_ele_link_id IS NULL) THEN
1963 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1964 hr_utility.set_message(801, 'PY_51132_TXADJ_LINK_MISSING');
1965 hr_utility.set_message_token ('ELEMENT', lv_element_name);
1966 hr_utility.raise_error;
1967 END IF;
1968
1969 end if;
1970
1971 end loop; -- of ltr_sdi_er_tax_bal)
1972 END IF; -- (ln_gross <> 0)
1973 end if;
1974
1975 hr_utility.set_location(gv_package||lv_procedure_name, 240);
1976 if sub2_entry_value_table.count > 0 then
1977 for i in sub2_entry_value_table.first ..
1978 sub2_entry_value_table.last loop
1979 hr_utility.trace('SDI2 entry' ||i||'='||
1980 sub2_entry_value_table(i));
1981 end loop;
1982 for i in sub2_input_value_table.first ..
1983 sub2_input_value_table.last loop
1984 hr_utility.trace('SDI2 input'||i||'='||
1985 sub2_input_value_table(i));
1986 end loop;
1987 hr_utility.set_location(gv_package||lv_procedure_name,250);
1988
1989 pay_bal_adjust.adjust_balance(
1990 p_batch_id => ln_badj_payroll_action_id,
1991 p_assignment_id => ln_assignment_id,
1992 p_element_link_id => ln_sub2_ele_link_id,
1993 p_num_entry_values => sub2_entry_value_table.count,
1994 p_entry_value_tbl => sub2_entry_value_table,
1995 p_input_value_id_tbl => sub2_input_value_table,
1996 p_balance_adj_cost_flag => 'N');
1997 end if;
1998
1999 if sub_input_value_table.count > 0 then
2000 for i in sub_entry_value_table.first ..
2001 sub_entry_value_table.last loop
2002 hr_utility.trace('SDI entry'||i||'='||
2003 sub_entry_value_table(i));
2004 end loop;
2005 for i in sub_input_value_table.first ..
2006 sub_input_value_table.last loop
2007 hr_utility.trace('SDI input'||i||'='||
2008 sub_input_value_table(i));
2009 end loop;
2010 hr_utility.set_location(gv_package||lv_procedure_name,260);
2011 pay_bal_adjust.adjust_balance(
2012 p_batch_id => ln_badj_payroll_action_id,
2013 p_assignment_id => ln_assignment_id,
2014 p_element_link_id => ln_sub_ele_link_id,
2015 p_num_entry_values => sub_entry_value_table.count,
2016 p_entry_value_tbl => sub_entry_value_table,
2017 p_input_value_id_tbl => sub_input_value_table,
2018 p_balance_adj_cost_flag => 'N');
2019 end if;
2020
2021
2022 hr_utility.set_location(gv_package || lv_procedure_name, 270);
2023
2024
2025 -- initialize tables
2026 sub_input_value_table.delete;
2027 sub2_input_value_table.delete;
2028
2029 sub_entry_value_table.delete;
2030 sub2_entry_value_table.delete;
2031 end if; -- sdi er exists
2032 hr_utility.set_location(gv_package || lv_procedure_name, 300);
2033
2034 else
2035 hr_utility.trace('Jurisdiction = ' || lv_jurisdiction);
2036 -- For 'MA'
2037 -- Compare Work Location Jurisdiction to SUI Jurisdiction
2038 -- IF equal then do balance call and adjust the balance as follow
2039 -- If not then push message into message lines.
2040 -- Balance : SDI ER Taxable
2041 -- Element : MISC1_STATE_TAX_ER
2042 -- Input Value : Taxable
2043 -- Input Value : Jurisdiction
2044 -- Get least of Reduced Subh WH , SDI ER LIMIT
2045 -- Difference of SDI ER Taxable and least of Reduced Subh WH or SDI ER LIMIT
2046
2047 lv_sub_jd_flag := 'N';
2048 ln_badj_bal_value := 0;
2049 ln_redu_subj_bal_value := 0;
2050 ln_sdi_er_wage_limit := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit;
2051 hr_utility.trace('SDI ER Wage Limit = ' || ln_sdi_er_wage_limit);
2052
2053 open c_get_per_latest_action(ln_assignment_id
2054 ,ln_tax_unit_id);
2055 fetch c_get_per_latest_action into ln_per_max_run_action_id;
2056 close c_get_per_latest_action;
2057 hr_utility.trace('ln_per_max_run_action_id = ' || ln_per_max_run_action_id);
2058
2059 if pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit
2060 is not null then
2061 hr_utility.set_location(gv_package || lv_procedure_name, 170);
2062 hr_utility.trace('Jurisdiction = ' || lv_jurisdiction);
2063 if pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal.count > 0 then
2064 lv_balance_name
2065 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(0).balance_name;
2066 ln_ytd_def_bal_id
2067 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(0).ytd_def_bal_id;
2068 lv_element_name
2069 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(0).element_name;
2070 ln_element_type_id
2071 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(0).element_type_id;
2072 lv_input_name
2073 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(0).input_name;
2074 ln_input_value_id
2075 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(0).input_value_id;
2076
2077 hr_utility.trace('lv_balance_name =' || lv_balance_name);
2078 hr_utility.trace('ln_ytd_def_bal_id =' || ln_ytd_def_bal_id);
2079 hr_utility.trace('lv_element_name=' || lv_element_name);
2080 hr_utility.trace('lv_input_name=' || lv_input_name);
2081 hr_utility.trace('ln_input_value_id =' || ln_input_value_id);
2082
2083 ln_subj_bal_value := 0;
2084 ln_subj_bal_value := get_balance_value(
2085 p_defined_balance_id => ln_ytd_def_bal_id
2086 ,p_balcall_aaid => ln_per_max_run_action_id);
2087 hr_utility.trace('ln_subj_bal_value =' || ln_subj_bal_value);
2088
2089 IF ln_subj_bal_value <> 0 THEN
2090 lv_balance_name
2091 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(1).balance_name;
2092 ln_ytd_def_bal_id
2093 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(1).ytd_def_bal_id;
2094 lv_element_name
2095 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(1).element_name;
2096 ln_element_type_id
2097 :=pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(1).element_type_id;
2098 lv_input_name
2099 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(1).input_name;
2100 ln_input_value_id
2101 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(1).input_value_id;
2102
2103 hr_utility.trace('lv_balance_name =' || lv_balance_name);
2104 hr_utility.trace('ln_ytd_def_bal_id =' || ln_ytd_def_bal_id);
2105 hr_utility.trace('lv_element_name=' || lv_element_name);
2106 hr_utility.trace('lv_input_name=' || lv_input_name);
2107 hr_utility.trace('ln_input_value_id =' || ln_input_value_id);
2108
2109 ln_pre_tax_bal_value := 0;
2110 ln_pre_tax_bal_value := get_balance_value(
2111 p_defined_balance_id => ln_ytd_def_bal_id
2112 ,p_balcall_aaid => ln_per_max_run_action_id);
2113 hr_utility.trace('ln_pre_tax_bal_value =' || ln_pre_tax_bal_value);
2114
2115 -- Reduced Subject
2116 ln_redu_subj_bal_value := ln_subj_bal_value - ln_pre_tax_bal_value;
2117 hr_utility.trace('ln_redu_subj_bal_value ='||ln_redu_subj_bal_value);
2118 lv_balance_name
2119 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(2).balance_name;
2120 ln_ytd_def_bal_id
2121 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(2).ytd_def_bal_id;
2122 lv_element_name
2123 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(2).element_name;
2124 ln_element_type_id
2125 :=pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(2).element_type_id;
2126 lv_input_name
2127 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(2).input_name;
2128 ln_input_value_id
2129 := pay_us_emp_baladj_cleanup.ltr_misc_er_tax_bal(2).input_value_id;
2130
2131 hr_utility.trace('lv_balance_name =' || lv_balance_name);
2132 hr_utility.trace('ln_ytd_def_bal_id =' || ln_ytd_def_bal_id);
2133 hr_utility.trace('lv_element_name=' || lv_element_name);
2134 hr_utility.trace('lv_input_name=' || lv_input_name);
2135 hr_utility.trace('ln_input_value_id =' || ln_input_value_id);
2136
2137 ln_taxable_bal_value := 0;
2138 ln_taxable_bal_value := get_balance_value(
2139 p_defined_balance_id => ln_ytd_def_bal_id
2140 ,p_balcall_aaid => ln_per_max_run_action_id);
2141 hr_utility.trace('ln_taxable_bal_value =' || ln_taxable_bal_value);
2142 END IF; -- (ln_sub whable <> 0)
2143 end if; --count > 0
2144
2145
2146 hr_utility.trace('TAXXABLE = ' || ln_taxable_bal_value);
2147 hr_utility.trace('WAGE LIMIT = ' || ln_sdi_er_wage_limit);
2148 hr_utility.trace('RED SUBJ = ' || ln_redu_subj_bal_value);
2149 if ln_taxable_bal_value < ln_sdi_er_wage_limit then
2150 ln_badj_bal_value := (least(ln_sdi_er_wage_limit,
2151 ln_redu_subj_bal_value)
2152 - ln_taxable_bal_value);
2153
2154 hr_utility.trace('taxable<SDI ER ln_badj_bal_value =' ||
2155 ln_badj_bal_value);
2156
2157 if substr(lv_element_name,1,18) = 'MISC1_STATE_TAX_ER' then
2158 if lv_misc_jd_flag = 'N' then
2159 ln_count3 := misc_input_value_table.count+1;
2160 --Set Input Value of Jurisdiction
2161 misc_input_value_table(ln_count3)
2162 := pay_us_emp_baladj_cleanup.get_input_value_id(
2163 p_element_type_id => ln_element_type_id
2164 ,p_input_value_name => 'Jurisdiction'
2165 ,p_effective_date => ld_run_effective_date);
2166 misc_entry_value_table(ln_count3) := lv_jurisdiction;
2167 lv_misc_jd_flag := 'Y';
2168 end if;
2169
2170 ln_count3 := misc_input_value_table.count+1;
2171 misc_input_value_table(ln_count3) := ln_input_value_id;
2172 misc_entry_value_table(ln_count3) := ln_badj_bal_value;
2173
2174 hr_utility.set_location(gv_package||lv_procedure_name,180);
2175
2176 ln_misc_ele_link_id := hr_entry_api.get_link(
2177 p_assignment_id => ln_assignment_id,
2178 p_element_type_id => ln_element_type_id,
2179 p_session_date => ld_run_effective_date);
2180 hr_utility.trace('Link MISC1 ER ='||ln_misc_ele_link_id);
2181
2182 IF (ln_misc_ele_link_id IS NULL) THEN
2183 hr_utility.set_location(gv_package ||
2184 lv_procedure_name, 40);
2185 hr_utility.set_message(801,
2186 'PY_51132_TXADJ_LINK_MISSING');
2187 hr_utility.set_message_token ('ELEMENT',
2188 lv_element_name);
2189 hr_utility.raise_error;
2190 END IF;
2191
2192 end if;
2193
2194 hr_utility.set_location(gv_package||lv_procedure_name, 150);
2195 if misc_entry_value_table.count > 0 then
2196 for i in misc_entry_value_table.first ..
2197 misc_entry_value_table.last loop
2198 hr_utility.trace('SDI entry' ||i||'='||
2199 misc_entry_value_table(i));
2200 end loop;
2201 end if;
2202 if misc_input_value_table.count > 0 then
2203 for i in misc_input_value_table.first ..
2204 misc_input_value_table.last loop
2205 hr_utility.trace('SDI input'||i||'='|| misc_input_value_table(i));
2206 end loop;
2207 end if;
2208
2209 if ln_badj_bal_value <> 0 then
2210 pay_bal_adjust.adjust_balance(
2211 p_batch_id => ln_badj_payroll_action_id,
2212 p_assignment_id => ln_assignment_id,
2213 p_element_link_id => ln_misc_ele_link_id,
2214 p_num_entry_values => misc_entry_value_table.count,
2215 p_entry_value_tbl => misc_entry_value_table,
2216 p_input_value_id_tbl => misc_input_value_table,
2217 p_balance_adj_cost_flag => 'N');
2218
2219 hr_utility.set_location(gv_package || lv_procedure_name, 200);
2220 end if;
2221 end if;
2222 end if; --misc1 tax er
2223 end if; -- end of State if
2224
2225
2226 end loop; --) state jurisdiction loop
2227 close csr_chk_state;
2228 hr_utility.set_location(gv_package || lv_procedure_name, 300);
2229
2230 ln_step := 5;
2231
2232 EXCEPTION
2233 when others then
2234 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
2235 gv_package || lv_procedure_name;
2236
2237 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2238
2239 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2240 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2241 hr_utility.raise_error;
2242
2243 END run_preprocess;
2244
2245 begin
2246 --hr_utility.trace_on (null, 'BAL');
2247
2248 gv_package := 'pay_us_emp_baladj_cleanup';
2249 g_proc_init := FALSE;
2250 g_min_chunk := -1;
2251 end pay_us_emp_baladj_cleanup;