[Home] [Help]
PACKAGE BODY: APPS.HR_US_FF_UDF1
Source
1 PACKAGE BODY hr_us_ff_udf1 AS
2 /* $Header: pyusudf1.pkb 120.49.12010000.18 2008/09/22 11:58:43 nerao ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : hr_us_ff_udf1
11 Filename : pyusudf1.pkb
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 19-AUG-02 TCLEWIS 115.0 Created
17 10-Oct-02 EKIM 115.2 2522002 Added functions
18 neg_earning, calc_earning
19 06-Aug-03 VMEHTA 115.4 Corrected the definition
20 (parameters) for
21 get_prev_ptd_values
22
23 08-Aug-03 VMEHTA 115.5 Corrected the definition
24 (parameters) for
25 get_prev_ptd_values
26 30-APR-04 TCLEWIS 115.6 Added functions
27 get_work_jurisdictions
28 and
29 Jurisdiction_processed
30 07-JUL-04 TCLEWIS 115.9 Changed plsql tables to be
31 indexed by binary integer.
32 version 115.7 was leap
33 frogged to 115.9, so
34 implementd 115.7 change.
35 02-AUN-04 TCLEWIS 115.10 ADDED GET_JD_PERCENT
36
37 27-AUG-04 TCLEWIS 115.12 Added code to load the resident
38 jurisdiction to the Juridiction_code_tbl.
39 This is because when a person works and
40 lives in the same JD, we have to pass
41 both to vertex and vertex will determine
42 how to tax.
43 23-SEP-04 FUSMAN 115.13 3909937 Changed the second cursor in function
44 get_prev_ptd_values to use
45 effective_date instead of date_earned.
46 23-SEP-04 meshah 115.14 3909937 get_prev_ptd_values, made changes
47 24-SEP-04 vmehta 115.15 3909937 get_prev_ptd_values, made
48 change to get full wages if
49 not processing home
50 jurisdiction (state), else get
51 only aggregate wages
52 27-SEP-04 vmehta 115.16 3909937 Added a check for dates in
53 query for getting person
54 address.
55 06-OCT-04 ppanda 115.17 3915176 Function GET_PRV_PTD_VALUES changed to
56 support previously withheld City, County
57 and School Dist Taxes
58 27-DEC-04 ppanda 115.21 3861379 Rolled back the fix 3926044
59 For Georgia Supp SIT 3926044 fix cannot
60 be shipped
61 09-APR-05 PPANDA 115.22 4241122 get_work_jurisdiction function
62 modified to support new value for
63 parameter p_INITIALIZE. Changes documented
64 above to the function definition.
65
66 A New function get_jurisdiction_type added
67
68 02-MAY-05 PPANDA 115.23 4337890 Exception added to formula function
69 get_Work_jurisdiction
70 27-MAY-05 SAIKRISH 115.25 4383819 Increase l_max_jurisdictions to 200
71 01-JUN-05 PPANDA 115.26 4217503 in get_work_jurisdiction function
72 a new criteria added to handle
73 number of work jurisdiction
74 02-JUN-05 ppanda 115.27 4374308 get_work_jurisdiction function
75 was using two local variables
76 to store the zip code of live and
77 work where size was not enough
78 when zip code had extension.
79 variable sizes increased.
80
81 27-JUN-05 ppanda 115.28 4227824 get_work_jurisdiction function
82 modified to support Homeworker
83 12-JUL-05 schauhan 115.29 4194339 Added function get_executive_status
84 20-AUG-05 saikrish 115.30 4532107 Added get_it_work_jurisdictions,
85 get_jd_level_threshold,get_th_assignment for
86 Consultant Taxation.
87 24-AUG-05 saikrish 115.31 4532107 Corrected Further Payroll DFF segment
88 columns in cursor csr_period_flag,
89 get_it_work_jurisdictions.
90 31-AUG-05 saikrish 115.35 4532107 Defaulted Further Payroll DFF segment
91 Use Information Hours to P (Previous
92 Pay Period).
93 05-SEP-05 saikrish 115.36 4590974 changed csr_it_element_entries
94 to query on end_date rather than
95 date_earned.
96 08-SEP-05 saikrish 115.38 4532107 Removed to_char for character
97 trace variables.
98 09-SEP-05 saikrish 115.39 4590974 Modified get_jd_tax_balances.
99 Corrected to 'State Tax Rules 2'
100 15-SEP-05 saikrish 115.43 4532107 Replaced csr_balance_ro with
101 balance call, _ASG_JD_RTD dimension
102 16-SEP-05 saikrish 115.44 4532107 Added message calls.
103 19-SEP-05 saikrish 115.45 4532107 Restricting tokens to length of 50
104 22-SEP-05 saikrish 115.46 4626170 Checking the termination of person
105 added cursor csr_term_date.
106 23-SEP-05 saikrish 115.47 4626170 Checking the hiring of person
107 added cursor csr_eff_dates.
108 29-SEP-05 saikrish 115.48 4638194 MOdified the dimension from
109 _ASG_JD_RTD to _PER_JD_GRE_RTD
110 07-NOV-05 saikrish 115.49 4626170 Enabled checking of hire and
111 termination of asssignment.
112 29-NOV-05 saikrish 115.50 4626170 Checking the termination date
113 based on date paid.
114 01-DEC-05 ppanda 115.51 4758960 get_work_jurisdiction function
115 modified to return work jurisdiction
116 count correctly ignoring the resident
117 jurisdiction code which is always added
118 at the end when its not an work
119 jurisdiction
120 03-APR-06 PPANDA 115.53 4715851 Few session variables were defined to fix
121 the Enhanced tax interface issue on local
122 tax.
123 02-MAY-06 PPANDA 115.55 5092586 get_work_jurisdiction function changed to
124 use Date Earned instead of Date Paid while
125 fetching VERTEX element entries.
126 03-MAY-06 PPANDA 115.56 5227022 get_work_jurisdiction function changed
127 When P_initialize flag was N, for information
128 time processing code was having issue to
129 fetch next jurisdiction from the pl/sql table
130 used in enhanced tax interface.
131 31-OCT-06 ssouresr 115.58 5602889 The function get_work_jurisdiction has been
132 5515072 modified to ensure that a resident jurisdiction
133 is not returned if the employee has not worked
134 in that jurisdiction
135 21-NOV-06 ssouresr 115.59 Added extra parameter to get_tax_exists
136 23-JAN-07 saikrish 115.60 5722893 Added new function get_jit_data.
137 07-MAR-07 SAIKRISH 115.62 Added new function get_rs_jd,get_wk_jd
138 15-MAR-07 jdevasah 115.67 5648961 The function get_it_work_jurisdiction has been
139 modified to assign null to l_start_date and
140 l_end_date for the first pay period when
141 l_pay_period_flag = 'P'.
142 11-MAY-07 jdevasah 115.68 5981447 Modified the function get_it_work_jurisdiction
143 with the following changes,
144 1. changed definition of the cursor csr_term_dates
145 to fetch hired date.
146 2. Added new date variables l_ws_start_date,
147 l_ws_end_date. These two variables are start and
148 end dates to calculate scheduled working hours
149 3. logic to calculate start and end dates for capturing
150 information hours and tagged hours is modified.
151 16-MAY-07 jdevasah 115.69 5981447 Added new condition in function get_jit_data to get
152 default supplemental calc method.
153 21-SEP-07 jdevasah 115.71 6371062 refined cdefiniton of cursor csr_term_dates in
154 in order to pick only active records from period of
155 service table.
156
157 25-OCT-07 jdevasah 115.72 6524016 Added two newparameters to the cursor csr_term_dates.
158 Pay period'start and end dates are passed to this
159 cursor.
160
161 14-NOV-07 jdevasah 115.73 6598477 Added new condition in get_it_jd_percent function
162 to handle CITY.
163 03-DEC-07 ssouresr 115.74 6495410 modified logic that was summing up percentages for
164 County. This was causing problems when city tax in KY
165 eliminates county tax.
166 08-MAR-08 ssouresr 115.75 6794452 Backed out changes made in previous version of the file
167 10-MAR-08 jdevasah 115.76 2122611 Added new function get_wc_flag.
168 10-APR-08 sjawid 115.77 6899939 Added new function parameter p_get_regular_wage to
169 115.78 get_prev_ptd_values
170 09-MAY-08 pannapur 115.79 5972214 Added function get_max_perc to find the city
171 wherein more time is worked in case of multiple
172 work jurisdictions
173 115.80 5972214 Removed get_max_perc
174 12-MAY-08 jdevasah 115.81 6869097 Modified Threshold hours calculation logic in function
175 get_it_work_jurisdiction.
176 10-JUL-08 Pannapur 115.83 7238809 Modified get_prev_ptd_values
177 10-JUL-08 Pannapur 115.84 Problem with ARCS .so arcsed in once again
178 12-JUL-08 jdevasah 115.85 6957929 Modified get_it_work_jurisdiction function to make the
179 functionality in sync with non EMJT. When work and
180 resident locations are same jurisdiction_type in the
181 pl/sql table must be 'RW'.
182 14-JUL-08 pannapur 115.86 Reverted back the fix made in 115.83
183 08-Aug-08 Pannapur 115.87 7238809 Added new function parameter per_adr_geocode to
184 115.88 get_prev_ptd_values
185 115.89 get_prev_ptd_values
186 15-SEP-08 jdevasah 115.90 7114362 modified get_it_work_jurisdiction function to make
187 payroll works for terminated assignments.
188 */
189
190 FUNCTION get_tax_jurisdiction(p_assignment_id number
191 ,p_date_earned date
192 )
193 RETURN varchar2
194 IS
195
196 l_return_value varchar2(1);
197
198 BEGIN
199 hr_utility.trace('BEGIN -> hr_us_ff_udf1.get_tax_jurisdiction ');
200 select nvl(hoi.org_information16,'N')
201 into l_return_value
202 from per_assignments_f paf,
203 hr_organization_information hoi,
204 hr_soft_coding_keyflex hsk
205 where paf.assignment_id = p_assignment_id
206 and p_date_earned between paf.effective_start_date
207 and paf.effective_end_date
208 and paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
209 and hsk.segment1 = hoi.organization_id
210 and hoi.org_information_context = 'W2 Reporting Rules';
211
212 hr_utility.trace('get_tax_jurisdiction retrun_value = ' || l_return_value);
213 hr_utility.trace('END -> hr_us_ff_udf1.get_tax_jurisdiction ');
214 return (l_return_value);
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 return ('N');
218 END get_tax_jurisdiction;
219
220 --
221 --
222 FUNCTION calc_earning (p_template_earning number,
223 p_addl_asg_gre_itd number,
224 p_neg_earn_asg_gre_itd number)
225 RETURN NUMBER
226 IS
227
228 l_ret number;
229
230 BEGIN
231 hr_utility.trace('tab.count = '||to_char(hr_us_ff_udf1.l_neg_earn_tab.count));
232
233 IF hr_us_ff_udf1.l_neg_earn_tab.count <= 0 THEN
234 hr_us_ff_udf1.l_neg_earn_tab(1).temp_earn := 0;
235 hr_us_ff_udf1.l_neg_earn_tab(1).neg_earn_feed := 0;
236 hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn := p_neg_earn_asg_gre_itd;
237 END IF;
238
239 hr_us_ff_udf1.l_neg_earn_tab(1).temp_earn := p_template_earning +
240 p_addl_asg_gre_itd +
241 hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn;
242
243 hr_utility.trace('temp_earn = '||
244 to_char(hr_us_ff_udf1.l_neg_earn_tab(1).temp_earn));
245
246 IF hr_us_ff_udf1.l_neg_earn_tab(1).temp_earn < 0 THEN
247 hr_us_ff_udf1.l_neg_earn_tab(1).neg_earn_feed :=
248 hr_us_ff_udf1.l_neg_earn_tab(1).temp_earn;
249 ELSE
250 IF (hr_us_ff_udf1.l_neg_earn_tab(1).temp_earn > 0) and
251 ( hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn = 0 ) THEN
252 hr_us_ff_udf1.l_neg_earn_tab(1).neg_earn_feed := 0;
253 END IF;
254
255 IF ( hr_us_ff_udf1.l_neg_earn_tab(1).temp_earn >= 0) and
256 ( hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn < 0) THEN
257 hr_us_ff_udf1.l_neg_earn_tab(1).neg_earn_feed :=
258 ( -1 * hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn);
259 END IF;
260 END IF;
261
262 hr_utility.trace('neg_earn_feed = '||
263 to_char(hr_us_ff_udf1.l_neg_earn_tab(1).neg_earn_feed));
264
265 hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn
266 := hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn +
267 (-1 * hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn);
268
269 hr_utility.trace('reduced_neg_earn = '||
270 to_char(hr_us_ff_udf1.l_neg_earn_tab(1).reduced_neg_earn));
271
272 l_ret := hr_us_ff_udf1.l_neg_earn_tab(1).temp_earn;
273
274 return l_ret;
275 END calc_earning;
276 --
277 --
278 FUNCTION neg_earning RETURN NUMBER is
279 l_ret number;
280 BEGIN
281 l_ret := hr_us_ff_udf1.l_neg_earn_tab(1).neg_earn_feed;
282 hr_utility.trace('hr_us_ff_udf1.neg_earning : neg_earn_feed = '||
283 to_char(l_ret));
284 return l_ret;
285 END neg_earning;
286
287 /*Function added for 6899939*/
288 FUNCTION get_prev_ptd_values(
289 p_assignment_action_id number, -- Context
290 p_tax_unit_id number, -- Context
291 p_jurisdiction_code varchar2, -- Context
292 p_fed_or_state varchar2, -- Parameter
293 p_regular_aggregate number, -- Parameter
294 calc_PRV_GRS OUT nocopy number, -- Paramter
295 calc_PRV_TAX OUT nocopy number )
296
297 RETURN NUMBER IS
298 BEGIN
299 RETURN get_prev_ptd_values(
300 p_assignment_action_id
301 ,p_tax_unit_id
302 ,p_jurisdiction_code
303 ,p_fed_or_state
304 ,p_regular_aggregate
305 ,calc_PRV_GRS
306 ,calc_PRV_TAX
307 ,'N');
308 END get_prev_ptd_values;
309
310 /*Function added for 7238809*/
311 FUNCTION get_prev_ptd_values(
312 p_assignment_action_id number, -- Context
313 p_tax_unit_id number, -- Context
314 p_jurisdiction_code varchar2, -- Context
315 p_fed_or_state varchar2, -- Parameter
316 p_regular_aggregate number, -- Parameter
317 calc_PRV_GRS OUT nocopy number, -- Paramter
318 calc_PRV_TAX OUT nocopy number,
319 p_get_regular_wage varchar2 )
320
321 RETURN NUMBER IS
322
323 CURSOR csr_l_home_juris (p_assignment_action_id IN NUMBER) IS
324
325 SELECT hr_us_ff_udfs.addr_val(NVL(add_information17, region_2),
326 NVL(add_information19, region_1),
327 NVL(add_information18, town_or_city),
328 NVL(add_information20, postal_code)
329 )
330
331 FROM per_addresses pad,
332 per_assignments_f paf,
333 pay_assignment_actions paa,
334 pay_payroll_actions ppa
335
336 WHERE pad.primary_flag = 'Y'
337 AND pad.person_id = paf.person_id
338 AND ppa.date_earned BETWEEN pad.date_from
339 AND NVL(pad.date_to, TO_DATE('12/31/4712',
340 'MM/DD/YYYY'))
341 AND ppa.date_earned BETWEEN paf.effective_start_date
342 AND paf.effective_end_date
343 AND paf.assignment_id = paa.assignment_id
344 AND paa.payroll_action_id = ppa.payroll_action_id
345 AND paa.assignment_action_id = p_assignment_action_id;
346
347 l_home_juris varchar2(11);
348
349 BEGIN
350
351 OPEN csr_l_home_juris(p_assignment_action_id);
352 FETCH csr_l_home_juris INTO l_home_juris;
353 IF csr_l_home_juris%NOTFOUND THEN
354 l_home_juris := '00-000-0000';
355 END IF;
356 CLOSE csr_l_home_juris;
357
358
359 RETURN get_prev_ptd_values(
360 p_assignment_action_id
361 ,p_tax_unit_id
362 ,p_jurisdiction_code
363 ,p_fed_or_state
364 ,p_regular_aggregate
365 ,calc_PRV_GRS
366 ,calc_PRV_TAX
367 ,p_get_regular_wage
368 ,l_home_juris);
369 END get_prev_ptd_values;
370
371
372 FUNCTION get_prev_ptd_values(
373 p_assignment_action_id number, -- context
374 p_tax_unit_id number,-- context
375 p_jurisdiction_code varchar2, -- context
376 p_fed_or_state varchar2, -- parameter
377 p_regular_aggregate number,
378 calc_PRV_GRS OUT nocopy number,
379 calc_PRV_TAX OUT nocopy number,
380 p_get_regular_wage varchar2, -- Paramter /*6899939*/
381 per_adr_geocode varchar2 -- Parameter /*7238809*/
382 )
383 RETURN NUMBER IS
384
385 l_defined_balance_tab pay_balance_pkg.t_balance_value_tab;
386 l_context_tab pay_balance_pkg.t_context_tab;
387 l_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
388 l_assignment_id number;
389 l_bal_assact number;
390 l_payroll_id number;
391 l_asg_type varchar2(11) := null;
392 l_regular_aggregate number;
393 l_get_full_wage varchar2(1);
394
395
396 BEGIN
397 -- hr_utility.trace_on(NULL,'ORCL');
398 l_get_full_wage := 'Y';
399 hr_utility.trace('Entering get_prev_ptd_values' );
400 hr_utility.trace('p_regular_aggregate = ' ||
401 to_char(p_regular_aggregate) );
402
403 SELECT paa.assignment_id
404 INTO l_assignment_id
405 FROM pay_assignment_actions paa
406 where paa.assignment_action_id = p_assignment_action_id;
407
408
409 /* commented for bug 7238809 .As fetching home jurisdiction using
410 fnd sessions does not pick the correct value .
411 SELECT hr_us_ff_udfs.addr_val(NVL(add_information17, region_2),
412 NVL(add_information19, region_1),
413 NVL(add_information18, town_or_city),
414 NVL(add_information20, postal_code)
415 )
416 INTO l_home_juris
417 FROM per_addresses pad,
418 per_assignments_f paf,
419 fnd_sessions fs
420 WHERE pad.primary_flag = 'Y'
421 AND pad.person_id = paf.person_id
422 AND fs.effective_date BETWEEN pad.date_from
423 AND NVL(pad.date_to, TO_DATE('12/31/4712',
424 'MM/DD/YYYY'))
425 AND fs.effective_date BETWEEN paf.effective_start_date
426 AND paf.effective_end_date
427 AND fs.session_id = USERENV('sessionid')
428 AND paf.assignment_id = l_assignment_id; */
429
430 SELECT /*+ RULE */ paa1.assignment_action_id,
431 ppa1.payroll_id
432 INTO l_bal_assact,
433 l_payroll_id
434 FROM pay_assignment_Actions paa1,
435 pay_payroll_actions ppa1
436 WHERE paa1.assignment_id = l_assignment_id
437 AND paa1.tax_unit_id = p_tax_unit_id
438 AND paa1.action_sequence =
439 (SELECT max(paa_prev.action_sequence)
440 FROM per_time_periods ptp
441 , pay_payroll_actions ppa
442 , pay_assignment_actions paa
443 , per_time_periods ptp_prev
444 , pay_payroll_actions ppa_prev
445 , pay_assignment_actions paa_prev
446 WHERE paa.assignment_action_id = p_assignment_action_id
447 AND ppa.payroll_action_id = paa.payroll_action_id
448 -- AND ptp.time_period_id = ppa.time_period_id
449 AND ppa.effective_date between ptp.start_date /*Bug:3909937*/
450 and ptp.end_date
451 AND ptp.payroll_id = ppa.payroll_id
452 AND ptp_prev.payroll_id = ppa.payroll_id
453 AND ptp.start_date - 1 between ptp_prev.start_date
454 and ptp_prev.end_date
455 AND paa_prev.assignment_id = paa.assignment_id
456 AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
457 AND ppa_prev.action_type IN ('R', 'Q', 'B')
458 -- AND ppa_prev.time_period_id = ptp_prev.time_period_id)
459 --AND ppa_prev.date_earned between ptp_prev.start_date
460 AND ppa_prev.effective_date between ptp_prev.start_date
461 and ptp_prev.end_date)
462 AND paa1.payroll_action_id = ppa1.payroll_action_id ;
463
464 hr_utility.trace('Previous period AAID ' || to_char(l_bal_assact) );
465
466
467 IF nvl(p_regular_aggregate,0)= 0 THEN
468 hr_utility.trace('p_regular_aggregate = 0 asg_type = ASG' );
469
470 l_asg_type := 'ASG';
471 ELSE
472 l_asg_type := 'PER_PAYROLL';
473
474 hr_utility.trace('p_regular_aggregate not = 0 asg_type = PER_PAYROLL' );
475
476 pay_balance_pkg.set_context('PAYROLL_ID',l_payroll_id);
477
478 END IF;
479
480 IF p_fed_or_state = 'FED' THEN
481
482 hr_utility.trace('p_fed_or_state = FED' );
483
484 -- MAY NEED FOR AGGREGATION.
485 --pay_balance_pkg.set_context('PAYROLL_ID',l_payroll_id);
486
487 SELECT creator_id
488 INTO l_defined_balance_tab(1).defined_balance_id
489 FROM ff_user_entities
490 WHERE user_entity_name = 'REGULAR_EARNINGS_' || l_asg_type || '_GRE_PTD'
491 AND legislation_code = 'US';
492
493 l_defined_balance_tab(1).balance_value := 0;
494
495 hr_utility.trace('REGULAR_EARNINGS_' || l_asg_type || '_GRE_PTD' || ' = '
496 || to_char(l_defined_balance_tab(1).defined_balance_id));
497
498 l_context_tab(1).tax_unit_id := p_tax_unit_id;
499 l_context_tab(1).jurisdiction_code := Null;
500 l_context_tab(1).source_id := null;
501 l_context_tab(1).source_text := null;
502 l_context_tab(1).source_number := null;
503 l_context_tab(1).source_text2 := null;
504
505 SELECT creator_id
506 INTO l_defined_balance_tab(2).defined_balance_id
507 FROM ff_user_entities
508 WHERE user_entity_name = 'SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
509 AND legislation_code = 'US';
510 l_defined_balance_tab(2).balance_value := 0;
511
512 hr_utility.trace('REGULAR_EARNINGS_' || l_asg_type || '_GRE_PTD' || ' = '
513 || to_char(l_defined_balance_tab(2).defined_balance_id));
514
515 l_context_tab(2).tax_unit_id := p_tax_unit_id;
516 l_context_tab(2).jurisdiction_code := null;
517 l_context_tab(2).source_id := null;
518 l_context_tab(2).source_text := null;
519 l_context_tab(2).source_number := null;
520 l_context_tab(2).source_text2 := null;
521
522 SELECT creator_id
523 INTO l_defined_balance_tab(3).defined_balance_id
524 FROM ff_user_entities
525 WHERE user_entity_name = 'FIT_NON_AGGREGATE_RED_SUBJ_WHABLE_' || l_asg_type || '_GRE_PTD'
526 AND legislation_code = 'US';
527
528 l_defined_balance_tab(3).balance_value := 0;
529
530 hr_utility.trace('FIT_NON_AGGREGATE_RED_SUBJ_WHABLE_' || l_asg_type || '_GRE_PTD' || ' = '
531 || to_char(l_defined_balance_tab(3).defined_balance_id));
532
533
534 l_context_tab(3).tax_unit_id := p_tax_unit_id;
535 l_context_tab(3).jurisdiction_code := null;
536 l_context_tab(3).source_id := null;
537 l_context_tab(3).source_text := null;
538 l_context_tab(3).source_number := null;
539 l_context_tab(3).source_text2 := null;
540
541 SELECT creator_id
542 INTO l_defined_balance_tab(4).defined_balance_id
543 FROM ff_user_entities
544 WHERE user_entity_name = 'DEF_COMP_401K_' || l_asg_type || '_GRE_PTD'
545 AND legislation_code = 'US';
546
547 l_defined_balance_tab(4).balance_value := 0;
548
549 hr_utility.trace('DEF_COMP_401K_' || l_asg_type || '_GRE_PTD' || ' = '
550 || to_char(l_defined_balance_tab(4).defined_balance_id));
551
552 l_context_tab(4).tax_unit_id := p_tax_unit_id;
553 l_context_tab(4).jurisdiction_code := null;
554 l_context_tab(4).source_id := null;
555 l_context_tab(4).source_text := null;
556 l_context_tab(4).source_number := null;
557 l_context_tab(4).source_text2 := null;
558
559 SELECT creator_id
560 INTO l_defined_balance_tab(5).defined_balance_id
561 FROM ff_user_entities
562 WHERE user_entity_name = 'DEF_COMP_403B_' || l_asg_type || '_GRE_PTD'
563 AND legislation_code = 'US';
564
565 l_defined_balance_tab(5).balance_value := 0;
566
567 hr_utility.trace('DEF_COMP_403B_' || l_asg_type || '_GRE_PTD' || ' = '
568 || to_char(l_defined_balance_tab(5).defined_balance_id));
569
570 l_context_tab(5).tax_unit_id := p_tax_unit_id;
571 l_context_tab(5).jurisdiction_code := null;
572 l_context_tab(5).source_id := null;
573 l_context_tab(5).source_text := null;
574 l_context_tab(5).source_number := null;
575 l_context_tab(5).source_text2 := null;
576
577 SELECT creator_id
578 INTO l_defined_balance_tab(6).defined_balance_id
579 FROM ff_user_entities
580 WHERE user_entity_name = 'DEF_COMP_457_' || l_asg_type || '_GRE_PTD'
581 AND legislation_code = 'US';
582
583 l_defined_balance_tab(6).balance_value := 0;
584
585 hr_utility.trace('DEF_COMP_457_' || l_asg_type || '_GRE_PTD' || ' = '
586 || to_char(l_defined_balance_tab(6).defined_balance_id));
587
588 l_context_tab(6).tax_unit_id := p_tax_unit_id;
589 l_context_tab(6).jurisdiction_code := null;
590 l_context_tab(6).source_id := null;
591 l_context_tab(6).source_text := null;
592 l_context_tab(6).source_number := null;
593 l_context_tab(6).source_text2 := null;
594
595 SELECT creator_id
596 INTO l_defined_balance_tab(7).defined_balance_id
597 FROM ff_user_entities
598 WHERE user_entity_name = 'OTHER_PRETAX_' || l_asg_type || '_GRE_PTD'
599 AND legislation_code = 'US';
600
601 hr_utility.trace('OTHER_PRETAX_' || l_asg_type || '_GRE_PTD' || ' = '
602 || to_char(l_defined_balance_tab(7).defined_balance_id));
603
604 l_defined_balance_tab(7).balance_value := 0;
605
606 l_context_tab(7).tax_unit_id := p_tax_unit_id;
607 l_context_tab(7).jurisdiction_code := null;
608 l_context_tab(7).source_id := null;
609 l_context_tab(7).source_text := null;
610 l_context_tab(7).source_number := null;
611 l_context_tab(7).source_text2 := null;
612
613 SELECT creator_id
614 INTO l_defined_balance_tab(8).defined_balance_id
615 FROM ff_user_entities
616 WHERE user_entity_name = 'SECTION_125_' || l_asg_type || '_GRE_PTD'
617 AND legislation_code = 'US';
618
619 hr_utility.trace('SECTION_125_' || l_asg_type || '_GRE_PTD' || ' = '
620 || to_char(l_defined_balance_tab(8).defined_balance_id));
621
622 l_defined_balance_tab(8).balance_value := 0;
623
624 l_context_tab(8).tax_unit_id := p_tax_unit_id;
625 l_context_tab(8).jurisdiction_code := null;
626 l_context_tab(8).source_id := null;
627 l_context_tab(8).source_text := null;
628 l_context_tab(8).source_number := null;
629 l_context_tab(8).source_text2 := null;
630
631 SELECT creator_id
632 INTO l_defined_balance_tab(9).defined_balance_id
633 FROM ff_user_entities
634 WHERE user_entity_name = 'DEPENDENT_CARE_' || l_asg_type || '_GRE_PTD'
635 AND legislation_code = 'US';
636
637 hr_utility.trace('DEPENDENT_CARE_' || l_asg_type || '_GRE_PTD' || ' = '
638 || to_char(l_defined_balance_tab(9).defined_balance_id));
639
640 l_defined_balance_tab(9).balance_value := 0;
641
642 l_context_tab(9).tax_unit_id := p_tax_unit_id;
643 l_context_tab(9).jurisdiction_code := null;
644 l_context_tab(9).source_id := null;
645 l_context_tab(9).source_text := null;
646 l_context_tab(9).source_number := null;
647 l_context_tab(9).source_text2 := null;
648
649 SELECT creator_id
650 INTO l_defined_balance_tab(10).defined_balance_id
651 FROM ff_user_entities
652 WHERE user_entity_name = 'DEF_COMP_401K_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
653 AND legislation_code = 'US';
654
655 hr_utility.trace('DEF_COMP_401K_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD' || ' = '
656 || to_char(l_defined_balance_tab(10).defined_balance_id));
657
658
659 l_defined_balance_tab(10).balance_value := 0;
660
661 l_context_tab(10).tax_unit_id := p_tax_unit_id;
662 l_context_tab(10).jurisdiction_code := null;
663 l_context_tab(10).source_id := null;
664 l_context_tab(10).source_text := null;
665 l_context_tab(10).source_number := null;
666 l_context_tab(10).source_text2 := null;
667
668 SELECT creator_id
669 INTO l_defined_balance_tab(11).defined_balance_id
670 FROM ff_user_entities
671 WHERE user_entity_name = 'DEF_COMP_403B_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
672 AND legislation_code = 'US';
673
674 hr_utility.trace('DEF_COMP_403B_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD' || ' = '
675 || to_char(l_defined_balance_tab(11).defined_balance_id));
676
677 l_defined_balance_tab(11).balance_value := 0;
678
679 l_context_tab(11).tax_unit_id := p_tax_unit_id;
680 l_context_tab(11).jurisdiction_code := null;
681 l_context_tab(11).source_id := null;
682 l_context_tab(11).source_text := null;
683 l_context_tab(11).source_number := null;
684 l_context_tab(11).source_text2 := null;
685
686 SELECT creator_id
687 INTO l_defined_balance_tab(12).defined_balance_id
688 FROM ff_user_entities
689 WHERE user_entity_name = 'DEF_COMP_457_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
690 AND legislation_code = 'US';
691
692 hr_utility.trace('DEF_COMP_457_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD' || ' = '
693 || to_char(l_defined_balance_tab(12).defined_balance_id));
694
695 l_defined_balance_tab(12).balance_value := 0;
696
697 l_context_tab(12).tax_unit_id := p_tax_unit_id;
698 l_context_tab(12).jurisdiction_code := null;
699 l_context_tab(12).source_id := null;
700 l_context_tab(12).source_text := null;
701 l_context_tab(12).source_number := null;
702 l_context_tab(12).source_text2 := null;
703
704 SELECT creator_id
705 INTO l_defined_balance_tab(13).defined_balance_id
706 FROM ff_user_entities
707 WHERE user_entity_name = 'OTHER_PRETAX_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
708 AND legislation_code = 'US';
709
710 hr_utility.trace('OTHER_PRETAX_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD' || ' = '
711 || to_char(l_defined_balance_tab(13).defined_balance_id));
712
713 l_defined_balance_tab(13).balance_value := 0;
714
715 l_context_tab(13).tax_unit_id := p_tax_unit_id;
716 l_context_tab(13).jurisdiction_code := null;
717 l_context_tab(13).source_id := null;
718 l_context_tab(13).source_text := null;
719 l_context_tab(13).source_number := null;
720 l_context_tab(13).source_text2 := null;
721
722 SELECT creator_id
723 INTO l_defined_balance_tab(14).defined_balance_id
724 FROM ff_user_entities
725 WHERE user_entity_name = 'SECTION_125_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
726 AND legislation_code = 'US';
727
728 hr_utility.trace('SECTION_125_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD' || ' = '
729 || to_char(l_defined_balance_tab(14).defined_balance_id));
730
731 l_defined_balance_tab(14).balance_value := 0;
732
733 l_context_tab(14).tax_unit_id := p_tax_unit_id;
734 l_context_tab(14).jurisdiction_code := null;
735 l_context_tab(14).source_id := null;
736 l_context_tab(14).source_text := null;
737 l_context_tab(14).source_number := null;
738 l_context_tab(14).source_text2 := null;
739
740 SELECT creator_id
741 INTO l_defined_balance_tab(15).defined_balance_id
742 FROM ff_user_entities
743 WHERE user_entity_name = 'DEPENDENT_CARE_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
744 AND legislation_code = 'US';
745
746 hr_utility.trace('DEPENDENT_CARE_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD' || ' = '
747 || to_char(l_defined_balance_tab(15).defined_balance_id));
748
749 l_defined_balance_tab(15).balance_value := 0;
750
751 l_context_tab(15).tax_unit_id := p_tax_unit_id;
752 l_context_tab(15).jurisdiction_code := null;
753 l_context_tab(15).source_id := null;
754 l_context_tab(15).source_text := null;
755 l_context_tab(15).source_number := null;
756 l_context_tab(15).source_text2 := null;
757
758 SELECT creator_id
759 INTO l_defined_balance_tab(16).defined_balance_id
760 FROM ff_user_entities
761 WHERE user_entity_name = 'FIT_NON_W2_DEF_COMP_401_' || l_asg_type || '_GRE_PTD'
762 AND legislation_code = 'US';
763
764 hr_utility.trace('FIT_NON_W2_DEF_COMP_401_' || l_asg_type || '_GRE_PTD' || ' = '
765 || to_char(l_defined_balance_tab(16).defined_balance_id));
766
767 l_defined_balance_tab(16).balance_value := 0;
768
769 l_context_tab(16).tax_unit_id := p_tax_unit_id;
770 l_context_tab(16).jurisdiction_code := null;
771 l_context_tab(16).source_id := null;
772 l_context_tab(16).source_text := null;
773 l_context_tab(16).source_number := null;
774 l_context_tab(16).source_text2 := null;
775
776 SELECT creator_id
777 INTO l_defined_balance_tab(17).defined_balance_id
778 FROM ff_user_entities
779 WHERE user_entity_name = 'FIT_NON_W2_DEF_COMP_403_' || l_asg_type || '_GRE_PTD'
780 AND legislation_code = 'US';
781
782 hr_utility.trace('FIT_NON_W2_DEF_COMP_403_' || l_asg_type || '_GRE_PTD' || ' = '
783 || to_char(l_defined_balance_tab(17).defined_balance_id));
784
785 l_defined_balance_tab(17).balance_value := 0;
786
787 l_context_tab(17).tax_unit_id := p_tax_unit_id;
788 l_context_tab(17).jurisdiction_code := null;
789 l_context_tab(17).source_id := null;
790 l_context_tab(17).source_text := null;
791 l_context_tab(17).source_number := null;
792 l_context_tab(17).source_text2 := null;
793
794 SELECT creator_id
795 INTO l_defined_balance_tab(18).defined_balance_id
796 FROM ff_user_entities
797 WHERE user_entity_name = 'FIT_NON_W2_DEF_COMP_457_' || l_asg_type || '_GRE_PTD'
798 AND legislation_code = 'US';
799
800 hr_utility.trace('FIT_NON_W2_DEF_COMP_457_' || l_asg_type || '_GRE_PTD' || ' = '
801 || to_char(l_defined_balance_tab(18).defined_balance_id));
802
803 l_defined_balance_tab(18).balance_value := 0;
804
805 l_context_tab(18).tax_unit_id := p_tax_unit_id;
806 l_context_tab(18).jurisdiction_code := null;
807 l_context_tab(18).source_id := null;
808 l_context_tab(18).source_text := null;
809 l_context_tab(18).source_number := null;
810 l_context_tab(18).source_text2 := null;
811
812 SELECT creator_id
813 INTO l_defined_balance_tab(19).defined_balance_id
814 FROM ff_user_entities
815 WHERE user_entity_name = 'FIT_NON_W2_SECTION_125_' || l_asg_type || '_GRE_PTD'
816 AND legislation_code = 'US';
817
818 hr_utility.trace('FIT_NON_W2_SECTION_125_' || l_asg_type || '_GRE_PTD' || ' = '
819 || to_char(l_defined_balance_tab(19).defined_balance_id));
820
821 l_defined_balance_tab(19).balance_value := 0;
822
823 l_context_tab(19).tax_unit_id := p_tax_unit_id;
824 l_context_tab(19).jurisdiction_code := null;
825 l_context_tab(19).source_id := null;
826 l_context_tab(19).source_text := null;
827 l_context_tab(19).source_number := null;
828 l_context_tab(19).source_text2 := null;
829
830 SELECT creator_id
831 INTO l_defined_balance_tab(20).defined_balance_id
832 FROM ff_user_entities
833 WHERE user_entity_name = 'FIT_NON_W2_DEPENDENT_CARE_' || l_asg_type || '_GRE_PTD'
834 AND legislation_code = 'US';
835
836 hr_utility.trace('FIT_NON_W2_DEPENDENT_CARE_' || l_asg_type || '_GRE_PTD' || ' = '
837 || to_char(l_defined_balance_tab(20).defined_balance_id));
838
839 l_defined_balance_tab(20).balance_value := 0;
840
841 l_context_tab(20).tax_unit_id := p_tax_unit_id;
842 l_context_tab(20).jurisdiction_code := null;
843 l_context_tab(20).source_id := null;
844 l_context_tab(20).source_text := null;
845 l_context_tab(20).source_number := null;
846 l_context_tab(20).source_text2 := null;
847
848 SELECT creator_id
849 INTO l_defined_balance_tab(21).defined_balance_id
850 FROM ff_user_entities
851 WHERE user_entity_name = 'FIT_NON_W2_OTHER_PRETAX_' || l_asg_type || '_GRE_PTD'
852 AND legislation_code = 'US';
853
854 hr_utility.trace('FIT_NON_W2_OTHER_PRETAX_' || l_asg_type || '_GRE_PTD' || ' = '
855 || to_char(l_defined_balance_tab(21).defined_balance_id));
856
857 l_defined_balance_tab(21).balance_value := 0;
858
859 l_context_tab(21).tax_unit_id := p_tax_unit_id;
860 l_context_tab(21).jurisdiction_code := null;
861 l_context_tab(21).source_id := null;
862 l_context_tab(21).source_text := null;
863 l_context_tab(21).source_number := null;
864 l_context_tab(21).source_text2 := null;
865
866
867 SELECT creator_id
868 INTO l_defined_balance_tab(22).defined_balance_id
869 FROM ff_user_entities
870 WHERE user_entity_name = 'FIT_WITHHELD_' || l_asg_type || '_GRE_PTD'
871 AND legislation_code = 'US';
872
873 hr_utility.trace('FIT_WITHHELD_' || l_asg_type || '_GRE_PTD' || ' = '
874 || to_char(l_defined_balance_tab(22).defined_balance_id));
875
876 l_defined_balance_tab(22).balance_value := 0;
877
878 l_context_tab(22).tax_unit_id := p_tax_unit_id;
879 l_context_tab(22).jurisdiction_code := null;
880 l_context_tab(22).source_id := null;
881 l_context_tab(22).source_text := null;
882 l_context_tab(22).source_number := null;
883 l_context_tab(22).source_text2 := null;
884
885 SELECT creator_id
886 INTO l_defined_balance_tab(23).defined_balance_id
887 FROM ff_user_entities
888 WHERE user_entity_name = 'FIT_SUPP_WITHHELD_' || l_asg_type || '_GRE_PTD'
889 AND legislation_code = 'US';
890
891 hr_utility.trace('FIT_SUPP_WITHHELD_' || l_asg_type || '_GRE_PTD' || ' = '
892 || to_char(l_defined_balance_tab(23).defined_balance_id));
893
894 l_defined_balance_tab(23).balance_value := 0;
895
896 l_context_tab(23).tax_unit_id := p_tax_unit_id;
897 l_context_tab(23).jurisdiction_code := null;
898 l_context_tab(23).source_id := null;
899 l_context_tab(23).source_text := null;
900 l_context_tab(23).source_number := null;
901 l_context_tab(23).source_text2 := null;
902
903 pay_balance_pkg.get_value (p_assignment_action_id => l_bal_assact,
904 p_defined_balance_lst => l_defined_balance_tab,
905 p_context_lst => l_context_tab,
906 p_get_rr_route => FALSE,
907 p_get_rb_route => FALSE,
908 p_output_table => l_bal_out_tab);
909
910 hr_utility.trace('Return value from balance call');
911 hr_utility.trace( 'REGULAR_EARNINGS_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(1).balance_value,0)));
912 hr_utility.trace( 'SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(2).balance_value,0)));
913 hr_utility.trace( 'FIT_NON_AGGREGATE_RED_SUBJ_WHABLE_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(3).balance_value,0)));
914 hr_utility.trace( 'DEF_COMP_401K_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(4).balance_value,0)));
915 hr_utility.trace( 'DEF_COMP_403B_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(5).balance_value,0)));
916 hr_utility.trace( 'DEF_COMP_457_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(6).balance_value,0)));
917 hr_utility.trace( 'OTHER_PRETAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(7).balance_value,0)));
918 hr_utility.trace( 'SECTION_125_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(8).balance_value,0)));
919 hr_utility.trace( 'DEPENDENT_CARE_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(9).balance_value,0)));
920
921 hr_utility.trace( 'DEF_COMP_401K_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(10).balance_value,0)));
922 hr_utility.trace( 'DEF_COMP_403B_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(11).balance_value,0)));
923 hr_utility.trace( 'DEF_COMP_457_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(12).balance_value,0)));
924 hr_utility.trace( 'OTHER_PRETAX_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(13).balance_value,0)));
925 hr_utility.trace( 'SECTION_125_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(14).balance_value,0)));
926 hr_utility.trace( 'DEPENDENT_CARE_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(15).balance_value,0)));
927
928 hr_utility.trace( 'FIT_NON_W2_DEF_COMP_401_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(16).balance_value,0)));
929 hr_utility.trace( 'FIT_NON_W2_DEF_COMP_403_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(17).balance_value,0)));
930 hr_utility.trace( 'FIT_NON_W2_DEF_COMP_457_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(18).balance_value,0)));
931 hr_utility.trace( 'FIT_NON_W2_SECTION_125_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(19).balance_value,0)));
932 hr_utility.trace( 'FIT_NON_W2_DEPENDENT_CARE_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(20).balance_value,0)));
933 hr_utility.trace( 'FIT_NON_W2_OTHER_PRETAX_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(21).balance_value,0)));
934
935 hr_utility.trace( 'FIT_WITHHELD_' || l_asg_type || '_PTD = '|| to_char(nvl(l_bal_out_tab(22).balance_value,0)));
936 hr_utility.trace( 'FIT_SUPP_WITHHELD_' || l_asg_type || '_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(23).balance_value,0)));
937
938
939
940 calc_PRV_GRS := nvl(l_bal_out_tab(1).balance_value,0) -- REGULAR_EARNINGS_ASG_GRE_PTD
941 + nvl(l_bal_out_tab(2).balance_value,0) -- SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_ASG_GRE_PTD
942 - nvl(l_bal_out_tab(3).balance_value,0) -- FIT_NON_AGGREGATE_RED_SUBJ_WHABLE_ASG_GRE_PTD
943 - nvl(l_bal_out_tab(4).balance_value,0) -- DEF_COMP_401K_ASG_GRE_PTD
944 - nvl(l_bal_out_tab(5).balance_value,0) -- DEF_COMP_403B_ASG_GRE_PTD
945 - nvl(l_bal_out_tab(6).balance_value,0) -- DEF_COMP_457_ASG_GRE_PTD
946 - nvl(l_bal_out_tab(7).balance_value,0) -- OTHER_PRETAX_ASG_GRE_PTD
947 - nvl(l_bal_out_tab(8).balance_value,0) -- SECTION_125_ASG_GRE_PTD
948 - nvl(l_bal_out_tab(9).balance_value,0) -- DEPENDENT_CARE_ASG_GRE_PTD
949
950 + nvl(l_bal_out_tab(10).balance_value,0) -- 'DEF_COMP_401K_FOR_FIT_SUBJECT_TO_TAX_ASG_GRE_PTD'
951 + nvl(l_bal_out_tab(11).balance_value,0) -- 'DEF_COMP_403B_FOR_FIT_SUBJECT_TO_TAX_ASG_GRE_PTD'
952 + nvl(l_bal_out_tab(12).balance_value,0) -- 'DEF_COMP_457_FOR_FIT_SUBJECT_TO_TAX_ASG_GRE_PTD'
953 + nvl(l_bal_out_tab(13).balance_value,0) -- 'OTHER_PRETAX_FOR_FIT_SUBJECT_TO_TAX_ASG_GRE_PTD'
954 + nvl(l_bal_out_tab(14).balance_value,0) -- 'SECTION_125_FOR_FIT_SUBJECT_TO_TAX_ASG_GRE_PTD'
955 + nvl(l_bal_out_tab(15).balance_value,0) -- 'DEPENDENT_CARE_FOR_FIT_SUBJECT_TO_TAX_ASG_GRE_PTD'
956
957 + nvl(l_bal_out_tab(16).balance_value,0) -- 'FIT_NON_W2_DEF_COMP_401_ASG_GRE_PTD'
958 + nvl(l_bal_out_tab(17).balance_value,0) -- 'FIT_NON_W2_DEF_COMP_403_ASG_GRE_PTD'
959 + nvl(l_bal_out_tab(18).balance_value,0) -- 'FIT_NON_W2_DEF_COMP_457_ASG_GRE_PTD'
960 + nvl(l_bal_out_tab(19).balance_value,0) -- 'FIT_NON_W2_SECTION_125_ASG_GRE_PTD'
961 + nvl(l_bal_out_tab(20).balance_value,0) -- 'FIT_NON_W2_DEPENDENT_CARE_ASG_GRE_PTD'
962 + nvl(l_bal_out_tab(21).balance_value,0); -- 'FIT_NON_W2_OTHER_PRETAX_ASG_GRE_PTD'
963
964 hr_utility.trace( 'calc_PRV_GRS = '|| to_char(nvl(calc_PRV_GRS,0)));
965
966 calc_PRV_TAX := nvl(l_bal_out_tab(22).balance_value,0) -- FIT_WITHHELD_ASG_PTD
967 - nvl(l_bal_out_tab(23).balance_value,0); -- FIT_SUPP_WITHHELD_ASG_GRE_PTD
968
969 hr_utility.trace( 'calc_PRV_TAX = '|| to_char(nvl(calc_PRV_TAX,0)));
970
971 ELSIF p_fed_or_state = 'STATE' THEN
972
973 hr_utility.trace('p_fed_or_state = STATE');
974
975 /*IF ((SUBSTR(l_home_juris, 1, 2)
976 = SUBSTR(p_jurisdiction_code, 1, 2))
977 OR
978 p_get_regular_wage='Y') 6899939*/
979
980 /* Modifed for both 6899939 and 7238809 .Finding home jurisdiction based on sessiondate does not
981 fetch correct address in 7238809 */
982 IF ((SUBSTR(per_adr_geocode, 1, 2)
983 = SUBSTR(p_jurisdiction_code, 1, 2))
984 OR
985 p_get_regular_wage='Y')
986
987 THEN
988 /*
989 * If we are processing home jurisdiction, then return
990 * only aggregate wages, otherwise return full wages
991 */
992 l_get_full_wage := 'N';
993 END IF;
994
995 SELECT creator_id
996 INTO l_defined_balance_tab(1).defined_balance_id
997 FROM ff_user_entities
998 WHERE user_entity_name = 'SIT_SUBJ_WHABLE_' || l_asg_type || '_JD_GRE_PTD'
999 AND legislation_code = 'US';
1000
1001 hr_utility.trace('SIT_SUBJ_WHABLE_' || l_asg_type || '_JD_GRE_PTD' || ' = '
1002 || to_char(l_defined_balance_tab(1).defined_balance_id));
1003
1004 l_defined_balance_tab(1).balance_value := 0;
1005
1006 l_context_tab(1).tax_unit_id := p_tax_unit_id;
1007 l_context_tab(1).jurisdiction_code := p_jurisdiction_code;
1008 l_context_tab(1).source_id := null;
1009 l_context_tab(1).source_text := null;
1010 l_context_tab(1).source_number := null;
1011 l_context_tab(1).source_text2 := null;
1012
1013 SELECT creator_id
1014 INTO l_defined_balance_tab(2).defined_balance_id
1015 FROM ff_user_entities
1016 WHERE user_entity_name = 'SIT_NON_AGGREGATE_RED_SUBJ_WHABLE_' || l_asg_type || '_JD_GRE_PTD'
1017 AND legislation_code = 'US';
1018
1019 hr_utility.trace('SIT_NON_AGGREGATE_RED_SUBJ_WHABLE_' || l_asg_type || '_JD_GRE_PTD' || ' = '
1020 || to_char(l_defined_balance_tab(2).defined_balance_id));
1021
1022 l_defined_balance_tab(2).balance_value := 0;
1023
1024 l_context_tab(2).tax_unit_id := p_tax_unit_id;
1025 l_context_tab(2).jurisdiction_code := p_jurisdiction_code;
1026 l_context_tab(2).source_id := null;
1027 l_context_tab(2).source_text := null;
1028 l_context_tab(2).source_number := null;
1029 l_context_tab(2).source_text2 := null;
1030
1031 SELECT creator_id
1032 INTO l_defined_balance_tab(3).defined_balance_id
1033 FROM ff_user_entities
1034 WHERE user_entity_name = 'SIT_PRE_TAX_REDNS_' || l_asg_type || '_JD_GRE_PTD'
1035 AND legislation_code = 'US';
1036
1037 hr_utility.trace('SIT_PRE_TAX_REDNS_' || l_asg_type || '_JD_GRE_PTD' || ' = '
1038 || to_char(l_defined_balance_tab(3).defined_balance_id));
1039
1040 l_defined_balance_tab(3).balance_value := 0;
1041
1042 l_context_tab(3).tax_unit_id := p_tax_unit_id;
1043 l_context_tab(3).jurisdiction_code := p_jurisdiction_code;
1044 l_context_tab(3).source_id := null;
1045 l_context_tab(3).source_text := null;
1046 l_context_tab(3).source_number := null;
1047 l_context_tab(3).source_text2 := null;
1048
1049 SELECT creator_id
1050 INTO l_defined_balance_tab(4).defined_balance_id
1051 FROM ff_user_entities
1052 WHERE user_entity_name = 'SIT_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
1053 AND legislation_code = 'US';
1054
1055 hr_utility.trace('SIT_WITHHELD_' || l_asg_type || '_JD_GRE_PTD' || ' = '
1056 || to_char(l_defined_balance_tab(4).defined_balance_id));
1057
1058 l_defined_balance_tab(5).balance_value := 0;
1059
1060 l_context_tab(4).tax_unit_id := p_tax_unit_id;
1061 l_context_tab(4).jurisdiction_code := p_jurisdiction_code;
1062 l_context_tab(4).source_id := null;
1063 l_context_tab(4).source_text := null;
1064 l_context_tab(4).source_number := null;
1065 l_context_tab(4).source_text2 := null;
1066
1067 SELECT creator_id
1068 INTO l_defined_balance_tab(5).defined_balance_id
1069 FROM ff_user_entities
1070 WHERE user_entity_name = 'SIT_SUPP_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
1071 AND legislation_code = 'US';
1072
1073 hr_utility.trace('SIT_SUPP_WITHHELD_' || l_asg_type || '_JD_GRE_PTD' || ' = '
1074 || to_char(l_defined_balance_tab(5).defined_balance_id));
1075
1076 l_defined_balance_tab(5).balance_value := 0;
1077
1078 l_context_tab(5).tax_unit_id := p_tax_unit_id;
1079 l_context_tab(5).jurisdiction_code := p_jurisdiction_code;
1080 l_context_tab(5).source_id := null;
1081 l_context_tab(5).source_text := null;
1082 l_context_tab(5).source_number := null;
1083 l_context_tab(5).source_text2 := null;
1084
1085 pay_balance_pkg.get_value (p_assignment_action_id => l_bal_assact,
1086 p_defined_balance_lst => l_defined_balance_tab,
1087 p_context_lst => l_context_tab,
1088 p_get_rr_route => FALSE,
1089 p_get_rb_route => FALSE,
1090 p_output_table => l_bal_out_tab);
1091
1092 hr_utility.trace('Return value from balance call');
1093 hr_utility.trace( 'SIT_SUBJ_WHABLE_' || l_asg_type || '_JD_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(1).balance_value,0)));
1094 hr_utility.trace( 'SIT_NON_AGGREGATE_RED_SUBJ_WHABLE_' || l_asg_type || '_JD_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(2).balance_value,0)));
1095 hr_utility.trace( 'SIT_PRE_TAX_REDNS_' || l_asg_type || '_JD_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(3).balance_value,0)));
1096 hr_utility.trace( 'SIT_WITHHELD_' || l_asg_type || '_JD_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(4).balance_value,0)));
1097 hr_utility.trace( 'SIT_SUPP_WITHHELD_' || l_asg_type || '_JD_GRE_PTD = '|| to_char(nvl(l_bal_out_tab(5).balance_value,0)));
1098
1099 calc_PRV_GRS := nvl(l_bal_out_tab(1).balance_value,0) -- SIT_SUBJ_WHABLE_ASG_JD_GRE_PTD
1100 - nvl(l_bal_out_tab(3).balance_value,0); -- SIT_PRE_TAX_REDNS_ASG_JD_GRE_PTD
1101
1102
1103 calc_PRV_TAX := nvl(l_bal_out_tab(4).balance_value,0); -- SIT_WITHHELD_ASG_JD_GRE_PTD
1104
1105 IF (l_get_full_wage <> 'Y')
1106 THEN
1107 calc_PRV_GRS := calc_PRV_GRS
1108 -NVL(l_bal_out_tab(2).balance_value,0);
1109 -- SIT_NON_AGGREGATE_RED_SUBJ_WHABLE_ASG_JD_GRE_PTD
1110 calc_PRV_TAX := calc_PRV_TAX
1111 -NVL(l_bal_out_tab(5).balance_value,0);
1112 -- SIT_SUPP_WITHHELD_ASG_JD_GRE_PTD
1113 END IF;
1114
1115 hr_utility.trace( 'calc_PRV_GRS = '|| to_char(nvl(calc_PRV_GRS,0)));
1116 hr_utility.trace( 'calc_PRV_TAX = '|| to_char(nvl(calc_PRV_TAX,0)));
1117 -- End of State Level Balance Fetch
1118 --
1119 --{
1120 -- This piece of code added for determining the Previous Pay Period City Level balances
1121 -- For fixing bug # 3915176
1122 -- Only City Tax withheld is derived from this piece of code not City level Wages
1123 -- Code needs modification for deriving City level wages
1124 --
1125 ELSIF p_fed_or_state = 'CITY' THEN
1126 hr_utility.trace('p_fed_or_state = CITY');
1127 SELECT creator_id
1128 INTO l_defined_balance_tab(1).defined_balance_id
1129 FROM ff_user_entities
1130 WHERE user_entity_name = 'CITY_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
1131 AND legislation_code = 'US';
1132
1133 hr_utility.trace('CITY_WITHHELD_' || l_asg_type || '_JD_GRE_PTD' || ' = '
1134 || to_char(l_defined_balance_tab(1).defined_balance_id));
1135
1136 l_defined_balance_tab(1).balance_value := 0;
1137 l_context_tab(1).tax_unit_id := p_tax_unit_id;
1138 l_context_tab(1).jurisdiction_code := p_jurisdiction_code;
1139 l_context_tab(1).source_id := null;
1140 l_context_tab(1).source_text := null;
1141 l_context_tab(1).source_number := null;
1142 l_context_tab(1).source_text2 := null;
1143
1144 pay_balance_pkg.get_value (p_assignment_action_id => l_bal_assact,
1145 p_defined_balance_lst => l_defined_balance_tab,
1146 p_context_lst => l_context_tab,
1147 p_get_rr_route => FALSE,
1148 p_get_rb_route => FALSE,
1149 p_output_table => l_bal_out_tab);
1150 calc_PRV_GRS := 0;
1151 calc_PRV_TAX := NVL(l_bal_out_tab(1).balance_value,0);
1152
1153 hr_utility.trace( 'CITY calc_PRV_GRS = '|| to_char(nvl(calc_PRV_GRS,0)));
1154 hr_utility.trace( 'CITY calc_PRV_TAX = '|| to_char(nvl(calc_PRV_TAX,0)));
1155 --}
1156 --{
1157 -- This piece of code added for determining the Previous Pay Period County Level balances
1158 -- For fixing bug # 3915176
1159 -- Only County Tax withheld is derived from this piece of code not County level Wages
1160 -- Code needs modification for deriving County level wages
1161 --
1162 ELSIF p_fed_or_state = 'COUNTY' THEN
1163 hr_utility.trace('p_fed_or_state = COUNTY');
1164 SELECT creator_id
1165 INTO l_defined_balance_tab(1).defined_balance_id
1166 FROM ff_user_entities
1167 WHERE user_entity_name = 'COUNTY_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
1168 AND legislation_code = 'US';
1169
1170 hr_utility.trace('COUNTY_WITHHELD_' || l_asg_type || '_JD_GRE_PTD' || ' = '
1171 || to_char(l_defined_balance_tab(1).defined_balance_id));
1172
1173 l_defined_balance_tab(1).balance_value := 0;
1174 l_context_tab(1).tax_unit_id := p_tax_unit_id;
1175 l_context_tab(1).jurisdiction_code := p_jurisdiction_code;
1176 l_context_tab(1).source_id := null;
1177 l_context_tab(1).source_text := null;
1178 l_context_tab(1).source_number := null;
1179 l_context_tab(1).source_text2 := null;
1180
1181 pay_balance_pkg.get_value (p_assignment_action_id => l_bal_assact,
1182 p_defined_balance_lst => l_defined_balance_tab,
1183 p_context_lst => l_context_tab,
1184 p_get_rr_route => FALSE,
1185 p_get_rb_route => FALSE,
1186 p_output_table => l_bal_out_tab);
1187 calc_PRV_GRS := 0;
1188 calc_PRV_TAX := NVL(l_bal_out_tab(1).balance_value,0);
1189
1190 hr_utility.trace( 'COUNTY calc_PRV_GRS = '|| to_char(nvl(calc_PRV_GRS,0)));
1191 hr_utility.trace( 'COUNTY calc_PRV_TAX = '|| to_char(nvl(calc_PRV_TAX,0)));
1192 --}
1193 --{
1194 -- This piece of code added for determining the Previous Pay Period School
1195 -- District Level balances. For fixing bug # 3915176
1196 -- Only School Dist. Tax withheld is derived from this piece of code not School Dist. level Wages
1197 -- Code needs modification for deriving School Dist. level wages
1198 --
1199 ELSIF p_fed_or_state = 'SCHOOL' THEN
1200 hr_utility.trace('p_fed_or_state = SCHOOL');
1201 SELECT creator_id
1202 INTO l_defined_balance_tab(1).defined_balance_id
1203 FROM ff_user_entities
1204 WHERE user_entity_name = 'SCHOOL_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
1205 AND legislation_code = 'US';
1206
1207 hr_utility.trace('SCHOOL_WITHHELD_' || l_asg_type || '_JD_GRE_PTD' || ' = '
1208 || to_char(l_defined_balance_tab(1).defined_balance_id));
1209
1210 l_defined_balance_tab(1).balance_value := 0;
1211 l_context_tab(1).tax_unit_id := p_tax_unit_id;
1212 l_context_tab(1).jurisdiction_code := p_jurisdiction_code;
1213 l_context_tab(1).source_id := null;
1214 l_context_tab(1).source_text := null;
1215 l_context_tab(1).source_number := null;
1216 l_context_tab(1).source_text2 := null;
1217
1218 pay_balance_pkg.get_value (p_assignment_action_id => l_bal_assact,
1219 p_defined_balance_lst => l_defined_balance_tab,
1220 p_context_lst => l_context_tab,
1221 p_get_rr_route => FALSE,
1222 p_get_rb_route => FALSE,
1223 p_output_table => l_bal_out_tab);
1224 calc_PRV_GRS := 0;
1225 calc_PRV_TAX := NVL(l_bal_out_tab(1).balance_value,0);
1226
1227 hr_utility.trace( 'School Dist. calc_PRV_GRS = '|| to_char(nvl(calc_PRV_GRS,0)));
1228 hr_utility.trace( 'School Dist. calc_PRV_TAX = '|| to_char(nvl(calc_PRV_TAX,0)));
1229 --}
1230
1231 END IF;
1232
1233 hr_utility.trace('End of GET_PRV_PTD_VALUES');
1234 return 0;
1235 --
1236 EXCEPTION
1237 WHEN OTHERS THEN
1238 hr_utility.trace('Exception handler');
1239 hr_utility.trace('SQLCODE = ' || SQLCODE);
1240 hr_utility.trace('SQLERRM = ' || SUBSTR(SQLERRM,1,80));
1241 calc_PRV_GRS := 0;
1242 calc_PRV_TAX := 0;
1243 RETURN 0;
1244 END get_prev_ptd_values;
1245
1246 /* This Function used to manage pl/table for work/tagged/home jurisdictions
1247 associated with an assignment
1248
1249 Parameter Purpose
1250 --------- -------
1251 p_INITIALIZE This parmaeter determines to process the pl/table
1252 jurisdiction_codes_tbl. This parameter expects one of 3
1253 values. (Y, N, F)
1254 Y denotes Initialize and populate the pl table
1255 N denotes Fetch jurisction that is stored next to the
1256 jurisdiction assigned to p_jurisdiction_code
1257 F denotes Fecth the First jurisdiction stored in the pl
1258 table
1259
1260 This function is being called from US_TAX_VERTEX2 formula with P_INITIALIZE
1261 value as 'Y'. PL table is always initialized for each assignment.
1262
1263 This function is called from US_TAX_VERTEX_HOME2 formula with P_INITIALIZE
1264 value as 'F'.
1265
1266 This function is repeatedly called from US_TAX_VERTEX_WORK2 depending on the
1267 number of work jurisdiction stored in the pl table.For this call P_INITIALIZE
1268 value is set as 'N'.
1269 */
1270 FUNCTION get_work_jurisdictions(p_assignment_action_id number
1271 ,p_INITIALIZE in varchar2
1272 ,p_jurisdiction_code in out NOCOPY varchar2
1273 ,p_percentage out NOCOPY number
1274 )
1275 RETURN varchar2
1276 IS
1277
1278 TOO_MANY_JURISDICTIONS EXCEPTION;
1279
1280 /*************************************************************
1281 * Maximum number of Work Jurisdictions that Quantum can Handle
1282 *************************************************************/
1283 /* bug 4383819, Changed max_jurisdiction to 200*/
1284 l_max_jurisdictions number := 200;
1285 l_assignment_id number;
1286 l_date_paid date;
1287 l_date_earned date;
1288
1289 l_ee_id number;
1290 l_jurisdiction_code varchar2(11);
1291 l_res_jurisdiction_code varchar2(11);
1292 l_jd_type varchar2(2);
1293
1294 l_percentage number;
1295 p_array_count number;
1296 l_index_value number;
1297
1298 l_jd_found varchar2(1);
1299 l_return_value varchar2(28);
1300
1301 l_state varchar2(2);
1302 l_county varchar2(120);
1303 l_city varchar2(30);
1304 l_zip_code varchar2(10);
1305
1306 l_res_state varchar2(2);
1307 l_res_county varchar2(120);
1308 l_res_city varchar2(100);
1309 l_res_zip varchar2(10);
1310
1311 l_wah Varchar2(1);
1312 cnt number;
1313 l_counter INTEGER;
1314
1315 Cursor Vertex_EE_Cursor is
1316 select pev1.element_entry_id,
1317 pev1.screen_entry_value Jurisdiction_code,
1318 pev2.screen_entry_value Percentage
1319 from pay_element_entry_values_f pev1,
1320 pay_element_entry_values_f pev2,
1321 pay_element_entries_f pee,
1322 pay_element_links_f pel,
1323 pay_element_types_f pet,
1324 pay_input_values_f piv1,
1325 pay_input_values_f piv2
1326 where pee.assignment_id = l_assignment_id
1327 -- and l_date_paid between pee.effective_start_date
1328 and l_date_earned between pee.effective_start_date
1329 and pee.effective_end_date
1330 and pee.element_link_id = pel.element_link_id
1331 and pee.effective_start_date between pel.effective_start_date
1332 and pel.effective_end_date
1333 and pel.element_type_id = pet.element_type_id
1334 and pet.element_name = 'VERTEX'
1335 and pee.effective_start_date between pet.effective_start_date
1336 and pet.effective_end_date
1337 and pee.element_entry_id = pev1.element_entry_id
1338 and pee.effective_start_date between pev1.effective_start_date
1339 and pev1.effective_end_date
1340 and pev1.input_value_id = piv1.input_value_id
1341 and pee.effective_start_date between piv1.effective_start_date
1342 and piv1.effective_end_date
1343 and piv1.name = 'Jurisdiction'
1344 and pee.element_entry_id = pev2.element_entry_id
1345 and pee.effective_start_date between pev2.effective_start_date
1346 and pev2.effective_end_date
1347 and pev2.input_value_id = piv2.input_value_id
1348 and pee.effective_start_date between piv2.effective_start_date
1349 and piv2.effective_end_date
1350 and piv2.name = 'Percentage';
1351
1352 Cursor tagged_earnings_Cursor is
1353 select /*+ INDEX (paa pay_assignment_actions_n51) */ distinct
1354 peev.element_entry_id,
1355 peev.screen_entry_value
1356 from pay_element_classifications pec
1357 ,pay_element_types_f pet
1358 ,pay_element_entries_f pee
1359 ,pay_element_links_f pel
1360 ,pay_input_values_f piv
1361 ,pay_element_entry_values_f peev
1362 where pec.classification_name in
1363 ( 'Earnings', 'Supplemental Earnings','Imputed Earnings' )
1364 and pet.classification_id = pec.classification_id
1365 and pee.effective_start_date between pet.effective_start_date
1366 and pet.effective_end_date
1367 and pee.assignment_id = l_assignment_id
1368 and l_date_earned between pee.effective_start_date
1369 and pee.effective_end_date
1370 and pet.element_type_id = pel.element_type_id
1371 and pel.element_link_id = pee.element_link_id
1372 and pee.effective_start_date between pel.effective_start_date
1373 and pel.effective_end_date
1374 and pet.element_type_id = piv.element_type_id
1375 and piv.name = 'Jurisdiction'
1376 and pee.effective_start_date between piv.effective_start_date
1377 and piv.effective_end_date
1378 and pee.element_entry_id = peev.element_entry_id
1379 and peev.input_value_id = piv.input_value_id
1380 and pee.effective_start_date between peev.effective_start_date
1381 and peev.effective_end_date
1382 and peev.screen_entry_value is not null;
1383
1384 -- Get flag to determine assignment has only IT time.
1385 CURSOR csr_use_it_flag (p_assignment_action_id IN NUMBER) IS
1386 SELECT NVL(fed.fed_information1,'N'),paa.assignment_id
1387 FROM pay_us_emp_fed_tax_rules_f fed,
1388 pay_assignment_actions paa,
1389 pay_payroll_actions ppa
1390 WHERE paa.assignment_id = fed.assignment_id
1391 AND paa.assignment_action_id = p_assignment_action_id
1392 AND paa.payroll_action_id = ppa.payroll_action_id
1393 AND NVL(ppa.date_earned,ppa.effective_date)
1394 BETWEEN fed.effective_start_date AND fed.effective_end_date;
1395
1396 l_use_it_flag pay_us_emp_fed_tax_rules_f.fed_information1%TYPE;
1397 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
1398 l_time_period_id pay_payroll_actions.time_period_id%TYPE;
1399 l_return_flag VARCHAR2(2);
1400 l_business_group_id NUMBER;
1401 l_tax_unit_id NUMBER;
1402
1403 BEGIN
1404 -- hr_utility.trace_on(NULL,'SK_hr_us_ff_udf1');
1405 hr_utility.trace('Begin get_work_jurisdictions');
1406 hr_utility.trace('get_work_jurisdictions query 1');
1407 hr_utility.trace('Fetch Payroll Details ');
1408
1409 SELECT paa.assignment_id,
1410 ppa.EFFECTIVE_DATE,
1411 ppa.date_earned,
1412 ppa.time_period_id,
1413 ppa.payroll_id,
1414 ppa.business_group_id,
1415 paa.tax_unit_id
1416 INTO l_assignment_id,
1417 l_date_paid,
1418 l_date_earned,
1419 l_time_period_id,
1420 l_payroll_id,
1421 l_business_group_id,
1422 l_tax_unit_id
1423 FROM pay_assignment_actions paa,
1424 pay_payroll_actions ppa
1425 WHERE paa.assignment_action_id = p_assignment_action_id
1426 AND ppa.payroll_action_id = paa.payroll_action_id;
1427
1428 hr_utility.trace('Assignment_Action_Id :'||to_char(p_assignment_action_id));
1429 hr_utility.trace('Assignment_ID :'||to_char(l_assignment_id));
1430 hr_utility.trace('Jurisdiction Code :'||p_jurisdiction_code);
1431 hr_utility.trace('Initialize Flag :'||p_initialize);
1432 hr_utility.trace('Date Earned :'||to_char(l_date_earned,'dd-mon-yyyy'));
1433 hr_utility.trace('Date Paid :'||to_char(l_date_paid,'dd-mon-yyyy'));
1434
1435 OPEN csr_use_it_flag(p_assignment_action_id);
1436 FETCH csr_use_it_flag INTO l_use_it_flag,
1437 l_assignment_id;
1438 IF csr_use_it_flag%NOTFOUND THEN
1439 l_use_it_flag := 'N';
1440 END IF;
1441 CLOSE csr_use_it_flag;
1442
1443 hr_utility.trace('Process Information Hours : '||l_use_it_flag);
1444
1445 IF l_use_it_flag = 'Y' AND p_initialize = 'Y' THEN
1446 --{
1447 g_use_it_flag := 'Y';
1448 hr_utility.trace('EMJT: Calling get_it_work_jurisdiction to process Information Hours');
1449 l_return_flag := get_it_work_jurisdictions
1450 (p_assignment_action_id => p_assignment_action_id
1451 ,p_initialize => p_initialize
1452 ,p_jurisdiction_code => p_jurisdiction_code
1453 ,p_percentage => p_percentage
1454 ,p_assignment_id => l_assignment_id
1455 ,p_date_paid => l_date_paid
1456 ,p_date_earned => l_date_earned
1457 ,p_time_period_id => l_time_period_id
1458 ,p_payroll_id => l_payroll_id
1459 ,p_business_group_id => l_business_group_id
1460 ,p_tax_unit_id => l_tax_unit_id
1461 );
1462 p_jurisdiction_code := 'NULL';
1463 p_percentage := 0;
1464 hr_utility.trace(' Returning after call to get_it_work_jurisdiction');
1465 RETURN('0');
1466 --}
1467 END IF; -- l_use_it_flag = 'Y'
1468
1469
1470 IF l_use_it_flag = 'N' THEN
1471 --{
1472 hr_utility.trace('Taxation would use W-4% configured for the assignment ');
1473 cnt :=0;
1474 IF p_initialize = 'Y' THEN
1475 -- LOAD the pl Table and return only NULL jurisdiction
1476 --{
1477 hr_utility.trace('get_work_jurisdictions || p_initialize = Y');
1478 g_use_it_flag := 'N';
1479 --
1480 -- initialize the PL/SQL tables
1481 --
1482 jurisdiction_codes_tbl.delete;
1483 res_jurisdiction_codes_tbl.delete; -- Added for Bug # 4715851
1484 state_processed_tbl.delete;
1485 county_processed_tbl.delete;
1486 city_processed_tbl.delete;
1487
1488 hr_utility.trace('get_work_jurisdictions plsql tables cleared');
1489
1490 -- GET the RESIDENT jurisdictions and load in to the *_processed_tables
1491 --
1492 hr_utility.trace('2nd Query in get_work_jurisdictions for fetching '
1493 ||' resident address details');
1494 SELECT nvl(ADDR.add_information17,ADDR.region_2) state,
1495 nvl(ADDR.add_information19,ADDR.region_1) county,
1496 nvl(ADDR.add_information18,ADDR.town_or_city) city,
1497 nvl(ADDR.add_information20,ADDR.postal_code) zip,
1498 nvl(ASSIGN.work_at_home,'N')
1499 INTO l_res_state,
1500 l_res_county,
1501 l_res_city,
1502 l_res_zip,
1503 l_wah
1504 FROM per_addresses ADDR
1505 ,per_all_assignments_f ASSIGN
1506 WHERE l_date_earned BETWEEN ASSIGN.effective_start_date
1507 AND ASSIGN.effective_end_date
1508 and ASSIGN.assignment_id = l_assignment_id
1509 and ADDR.person_id = ASSIGN.person_id
1510 and ADDR.primary_flag = 'Y'
1511 and l_date_earned BETWEEN nvl(ADDR.date_from, l_date_earned)
1512 AND nvl(ADDR.date_to, l_date_earned);
1513
1514 hr_utility.trace('2nd query returned res address details');
1515 l_res_jurisdiction_code := hr_us_ff_udfs.addr_val(l_res_state
1516 , l_res_county
1517 , l_res_city
1518 , l_res_zip);
1519
1523 IF substr(l_res_jurisdiction_code,8,1) = 'U' THEN
1520 -- IF this is a user defined city IE: city_code = 'U***' the change
1521 -- the city code to all 0 (zeros)
1522
1524 l_res_jurisdiction_code := substr(l_res_jurisdiction_code,1,7) ||
1525 '0000' ;
1526 END IF;
1527
1528 hr_utility.trace('Resident Jurisdiction Code = ' ||
1529 l_res_jurisdiction_code);
1530 hr_utility.trace('Home Workers Flag = ' || l_wah);
1531
1532 IF l_wah = 'N' THEN
1533 --{
1534 -- Get the vertex element Entries
1535 OPEN Vertex_EE_Cursor;
1536 FETCH Vertex_EE_Cursor into
1537 l_ee_id,
1538 l_jurisdiction_code,
1539 l_percentage;
1540
1541 hr_utility.trace('open fetch vertex_ee_cursor');
1542 LOOP
1543 hr_utility.trace('Processing Tagged Jurisdiction Code'||
1544 l_jurisdiction_code);
1545 EXIT WHEN Vertex_EE_Cursor%NOTFOUND;
1546 -- IF this is a user defined city IE: city_code = 'U***' the
1547 -- change the city code to all 0 (zeros)
1548 if substr(l_jurisdiction_code,8,1) = 'U' then
1549 l_jurisdiction_code := substr(l_jurisdiction_code,1,7) ||
1550 '0000' ;
1551 end if;
1552 IF nvl(l_percentage,0) <> 0 THEN
1553 --{
1554 IF jurisdiction_codes_tbl.count >= l_max_jurisdictions THEN
1555 raise TOO_MANY_JURISDICTIONS;
1556 hr_utility.trace('too many jurisdictions');
1557 END IF;
1558 hr_utility.trace('Jurisdiction Code'|| l_jurisdiction_code ||
1559 ' loaded into pl table');
1560 jurisdiction_codes_tbl( to_number(substr(l_jurisdiction_code,
1561 1,2) ||
1562 substr(l_jurisdiction_code,4,3) ||
1563 substr(l_jurisdiction_code,8,4) )
1564 ).jurisdiction_code := l_jurisdiction_code;
1565 jurisdiction_codes_tbl( to_number(substr(l_jurisdiction_code,
1566 1,2) ||
1567 substr(l_jurisdiction_code,4,3) ||
1568 substr(l_jurisdiction_code,8,4) )
1569 ).percentage := l_percentage;
1570 jurisdiction_codes_tbl( to_number(substr(l_jurisdiction_code,
1571 1,2) ||
1572 substr(l_jurisdiction_code,4,3) ||
1573 substr(l_jurisdiction_code,8,4) )
1574 ).hours := 0;
1575 jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
1576 1,2) ||
1577 substr(l_jurisdiction_code,4,3) ||
1578 substr(l_jurisdiction_code,8,4) )
1579 ).jd_type := 'WK';
1580 --}
1581 END IF;
1582 FETCH Vertex_EE_Cursor into
1583 l_ee_id,
1584 l_jurisdiction_code,
1585 l_percentage;
1586 END LOOP;
1587 hr_utility.trace('end of loop 1 vertex_ee_cursor');
1588 CLOSE Vertex_EE_Cursor;
1589 --}
1590 END IF; -- if l_wah = 'N' then
1591
1592 -- Look for and load tagged earnings;
1593 OPEN tagged_earnings_Cursor;
1594 FETCH tagged_earnings_Cursor into
1595 l_ee_id,
1596 l_jurisdiction_code;
1597
1598 hr_utility.trace(' open fetch tagged_earnings_cursor');
1599 hr_utility.trace('loop tagged earnings cursor');
1600
1601 LOOP
1602 hr_utility.trace('Processing Tagged Jurisdiction Code'||
1603 l_jurisdiction_code);
1604 EXIT WHEN tagged_earnings_Cursor%NOTFOUND;
1605 -- IF this is a user defined city IE: city_code = 'U***' the
1606 -- change the city code to all 0 (zeros)
1607
1608 IF substr(l_jurisdiction_code,8,1) = 'U' then
1609 l_jurisdiction_code := substr(l_jurisdiction_code,1,7)
1610 || '0000' ;
1611 END IF;
1612 -- 1) see if JD exists in plsql table
1613 -- 2) if not add the JD, to the plsql table with a 0 percent.
1614 IF jurisdiction_codes_tbl.EXISTS(
1615 to_number(substr(l_jurisdiction_code,1,2) ||
1616 substr(l_jurisdiction_code,4,3) ||
1617 substr(l_jurisdiction_code,8,4) )
1618 ) THEN
1619 --{
1620 NULL;
1621 --}
1622 ELSE
1623 --{
1624 IF jurisdiction_codes_tbl.count >= l_max_jurisdictions THEN
1625 raise TOO_MANY_JURISDICTIONS;
1626 hr_utility.trace('too many jurisdictions');
1627 END IF;
1628 hr_utility.trace('Jurisdiction Code'|| l_jurisdiction_code ||
1629 ' loaded into pl table');
1630
1631 jurisdiction_codes_tbl( to_number(substr(l_jurisdiction_code,
1632 1,2) ||
1633 substr(l_jurisdiction_code,4,3) ||
1634 substr(l_jurisdiction_code,8,4) )
1635 ).jurisdiction_code := l_jurisdiction_code;
1636 jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
1637 1,2) ||
1638 substr(l_jurisdiction_code,4,3) ||
1639 substr(l_jurisdiction_code,8,4) )
1640 ).percentage := 0;
1641 jurisdiction_codes_tbl( to_number(substr(l_jurisdiction_code,
1642 1,2) ||
1643 substr(l_jurisdiction_code,4,3) ||
1644 substr(l_jurisdiction_code,8,4) )
1645 ).hours := 0;
1646
1647 --}
1648 END IF;
1649 -- This is set JD_TYPE for Tagged earnings
1650 if l_jurisdiction_code = l_res_jurisdiction_code then
1651 jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
1652 1,2) ||
1653 substr(l_jurisdiction_code,4,3) ||
1654 substr(l_jurisdiction_code,8,4) )
1655 ).jd_type := 'RT';
1656 else
1657 jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
1658 1,2) ||
1659 substr(l_jurisdiction_code,4,3) ||
1660 substr(l_jurisdiction_code,8,4) )
1661 ).jd_type := 'TG';
1662 end if;
1663
1664 FETCH tagged_earnings_Cursor into
1665 l_ee_id,
1666 l_jurisdiction_code;
1667 hr_utility.trace('fetch 2 from cursor tagged_earnings_Cursor');
1668
1669 END LOOP;
1670 hr_utility.trace('end of loop 2 tagged_earnings_Cursor');
1671 CLOSE tagged_earnings_Cursor;
1672 --
1673 -- This section is determine Primary Work Jurisdiction for the assignment
1674 --
1675 IF l_wah = 'N' THEN -- Home Workers flag is set to NO
1676 --{
1677 -- Find and load the primary Work location to the jurisdiction.
1678 SELECT nvl(HRLOC.loc_information18,HRLOC.town_or_city),
1679 nvl(HRLOC.loc_information19,HRLOC.region_1),
1680 nvl(HRLOC.loc_information17,HRLOC.region_2),
1681 substr(nvl(HRLOC.loc_information20,HRLOC.postal_code)
1682 ,1,5)
1683 INTO l_city,
1684 l_county,
1685 l_state,
1686 l_zip_code
1687 FROM hr_locations HRLOC
1688 , hr_soft_coding_keyflex HRSCKF
1689 , per_all_assignments_f ASSIGN
1690 WHERE l_date_earned BETWEEN ASSIGN.effective_start_date
1691 AND ASSIGN.effective_end_date
1692 AND ASSIGN.assignment_id = l_assignment_id
1693 AND ASSIGN.soft_coding_keyflex_id = HRSCKF.soft_coding_keyflex_id
1694 AND nvl(HRSCKF.segment18,
1695 ASSIGN.location_id) = HRLOC.location_id;
1696 l_jd_found := 'N';
1697 hr_utility.trace('Primary work location query');
1698 l_jurisdiction_code := hr_us_ff_udfs.addr_val(l_state,
1699 l_county,
1700 l_city,
1701 l_zip_code);
1702 hr_utility.trace('Primary work loc JD CODE = ' ||
1703 l_jurisdiction_code);
1704
1705 -- IF this is a user defined city IE: city_code = 'U***' the
1706 -- change the city code to all 0 (zeros)
1707 if substr(l_jurisdiction_code,8,1) = 'U' then
1708 l_jurisdiction_code := substr(l_jurisdiction_code,1,7) ||
1709 '0000' ;
1710 end if;
1711
1712 IF jurisdiction_codes_tbl.EXISTS(
1713 to_number(substr(l_jurisdiction_code,1,2) ||
1714 substr(l_jurisdiction_code,4,3) ||
1715 substr(l_jurisdiction_code,8,4) )
1716 ) THEN
1717 --{
1718 hr_utility.trace('Work Jurisdiction already loaded.'||
1719 ' Updating JD_Type');
1720 if l_jurisdiction_code = l_res_jurisdiction_code then
1721 jurisdiction_codes_tbl(to_number(substr(
1722 l_jurisdiction_code, 1,2) ||
1723 substr(l_jurisdiction_code,4,3) ||
1724 substr(l_jurisdiction_code,8,4) )
1725 ).jd_type := 'RW';
1726 else
1727 jurisdiction_codes_tbl(to_number(substr(
1728 l_jurisdiction_code, 1,2) ||
1729 substr(l_jurisdiction_code,4,3) ||
1730 substr(l_jurisdiction_code,8,4) )
1731 ).jd_type := 'WK';
1732 end if;
1733 --}
1734 ELSE
1735 --{
1736 hr_utility.trace('Jurisdiction Code'|| l_jurisdiction_code ||
1737 ' loaded into pl table');
1738 IF jurisdiction_codes_tbl.count >= l_max_jurisdictions THEN
1739 raise TOO_MANY_JURISDICTIONS;
1740 hr_utility.trace('too many jurisdictions');
1741 END IF;
1742 hr_utility.trace('Populating table with Pri work location');
1743 jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
1744 1,2) || substr(l_jurisdiction_code,4,3) ||
1745 substr(l_jurisdiction_code,8,4) )
1746 ).jurisdiction_code := l_jurisdiction_code;
1747 jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
1748 1,2) || substr(l_jurisdiction_code,4,3) ||
1749 substr(l_jurisdiction_code,8,4) )
1750 ).percentage := 0;
1751 jurisdiction_codes_tbl(to_number(substr(
1752 l_jurisdiction_code, 1,2) ||
1753 substr(l_jurisdiction_code,4,3) ||
1754 substr(l_jurisdiction_code,8,4) )
1755 ).jd_type := 'WK';
1756 jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
1757 1,2) ||
1758 substr(l_jurisdiction_code,4,3) ||
1759 substr(l_jurisdiction_code,8,4) )
1760 ).hours := 0;
1761
1762 --}
1763 END IF;
1764 --}
1765 END IF; -- if l_wah = 'N' then
1766 -- Load the resident jurisdiction if not already loaded via one of the
1767 -- other queries. Note the resident jurisdiction is querried at the
1768 -- begining of this function.
1769 hr_utility.trace('Processing Resident jurisdiction ');
1770 IF jurisdiction_codes_tbl.EXISTS(
1771 to_number(substr(l_res_jurisdiction_code,
1772 1,2) ||
1773 substr(l_res_jurisdiction_code,4,3) ||
1774 substr(l_res_jurisdiction_code,8,4) )
1775 ) THEN
1776 --{
1777 hr_utility.trace('Resident jurisdiction exist in pl table');
1778 IF (l_wah = 'N') THEN
1779 --{
1780 IF (jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code, 1,2) ||
1781 substr(l_res_jurisdiction_code, 4,3) ||
1782 substr(l_res_jurisdiction_code, 8,4) )
1783 ).jd_type <> 'RT')
1784 THEN
1785 jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code, 1,2) ||
1786 substr(l_res_jurisdiction_code, 4,3) ||
1787 substr(l_res_jurisdiction_code, 8,4) )
1788 ).jd_type := 'RW';
1789 END IF;
1790 --}
1791 ELSE
1792 --{
1793 jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code, 1,2) ||
1794 substr(l_res_jurisdiction_code, 4,3) ||
1795 substr(l_res_jurisdiction_code, 8,4) )
1796 ).jd_type := 'HW';
1797 --}
1798 END IF;
1799 --}
1800 ELSE
1801 --{
1802 -- If Residence jurisdiction does not exist in the pl/sql table
1803 IF jurisdiction_codes_tbl.count >= l_max_jurisdictions THEN
1804 raise TOO_MANY_JURISDICTIONS;
1805 hr_utility.trace('too many jurisdictions');
1806 END IF;
1807 hr_utility.trace('Populating pl table for Resident Jurisdiction');
1808 --
1809 -- For bug 4715851 res_jurisdiction_codes_tbl used
1810 -- in place of jurisdiction_codes_tbl
1811 IF l_wah = 'N' THEN
1812 --{
1813 res_jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code,1,2) ||
1814 substr(l_res_jurisdiction_code,4,3) ||
1815 substr(l_res_jurisdiction_code,8,4) )
1816 ).jurisdiction_code := l_res_jurisdiction_code;
1817
1818 res_jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code,1,2) ||
1819 substr(l_res_jurisdiction_code,4,3) ||
1820 substr(l_res_jurisdiction_code,8,4) )
1821 ).percentage := 0;
1822 res_jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code,1,2) ||
1823 substr(l_res_jurisdiction_code,4,3) ||
1824 substr(l_res_jurisdiction_code,8,4))
1825 ).jd_type := 'RS';
1826 res_jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code,1,2) ||
1827 substr(l_res_jurisdiction_code, 4,3) ||
1828 substr(l_res_jurisdiction_code, 8,4))
1829 ).hours := 0;
1830 --}
1831 ELSE
1832 --{
1833 jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code,1,2) ||
1834 substr(l_res_jurisdiction_code,4,3) ||
1835 substr(l_res_jurisdiction_code,8,4) )
1836 ).jurisdiction_code := l_res_jurisdiction_code;
1837
1838 jurisdiction_codes_tbl( to_number(substr(l_res_jurisdiction_code,
1839 1,2) ||
1840 substr(l_res_jurisdiction_code,4,3) ||
1841 substr(l_res_jurisdiction_code,8,4) )
1842 ).percentage := 100;
1843 jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code, 1,2) ||
1844 substr(l_res_jurisdiction_code, 4,3) ||
1845 substr(l_res_jurisdiction_code, 8,4) )
1846 ).jd_type := 'HW';
1847 jurisdiction_codes_tbl(to_number(substr(l_res_jurisdiction_code, 1,2) ||
1848 substr(l_res_jurisdiction_code, 4,3) ||
1849 substr(l_res_jurisdiction_code, 8,4) )
1850 ).hours := 0;
1851 --}
1852 END IF; --l_wah = 'N'
1853 --}
1854 END IF;
1855 hr_utility.trace('Jurisdiction Table count = '||
1856 to_char(jurisdiction_codes_tbl.COUNT));
1857 hr_utility.trace('Return section begin');
1858 --As Initialize section doesn't expect the return value
1859 p_jurisdiction_code := 'NULL';
1860 p_percentage := 0;
1861 RETURN('0');
1862
1863 --}End of Initialize = 'Y'
1864 END IF;
1865 --}
1866 END IF; --l_use_it_flag = 'N' THEN
1867
1868 IF p_initialize = 'F' THEN
1869 -- This is to get first jurisdiction loaed into pl table
1870 -- jurisdiction_codes_tbl
1871 -- BEGIN
1872 IF jurisdiction_codes_tbl.COUNT = 0 THEN
1873 --{
1874 hr_utility.trace('Table count = 0');
1875 p_jurisdiction_code := '';
1876 p_percentage := 0;
1877 --}
1878 ELSE
1879 --{
1880 hr_utility.trace('Table count <> 0');
1881 p_jurisdiction_code :=
1882 jurisdiction_codes_tbl(jurisdiction_codes_tbl.FIRST).jurisdiction_code;
1883 p_percentage :=
1884 jurisdiction_codes_tbl(jurisdiction_codes_tbl.FIRST).percentage;
1885 --}
1886 END IF;
1887 hr_utility.trace('p_jurisdiction_code = ' || p_jurisdiction_code);
1888 hr_utility.trace('p_percentage = ' || to_char(p_percentage));
1889 hr_utility.trace('Done with p_initialize F');
1890 --
1891 --} End of Initialize = 'F'
1892 ELSIF p_initialize = 'C' THEN
1893 -- This is to get the number of jurisdiction associated with
1894 -- a given assignment
1895 -- BEGIN
1896 hr_utility.trace('This is to count no of jurisdiction associated with');
1897 hr_utility.trace('all the element entries defined for the assignment ');
1898 cnt := 0;
1899 IF jurisdiction_codes_tbl.COUNT >= 1
1900 THEN
1901 --{
1902 cnt := jurisdiction_codes_tbl.COUNT;
1903 l_counter := NULL;
1904 l_counter := jurisdiction_codes_tbl.FIRST;
1905 WHILE l_counter IS NOT NULL
1906 LOOP
1907 if (jurisdiction_codes_tbl(l_counter).jd_type = 'RS')
1908 then
1909 cnt := cnt - 1;
1910 end if;
1911 l_counter := jurisdiction_codes_tbl.NEXT(l_counter);
1912 END LOOP;
1913 p_percentage := cnt;
1914 --}
1915 END IF;
1916 hr_utility.trace('Work Jurisdiction Table Count = '||to_char(cnt));
1917 hr_utility.trace('Done with p_initialize=C');
1918
1919 --} End of Initialize = 'C'
1920 ELSIF p_initialize = 'N' THEN
1921
1922 hr_utility.trace('Initialize = n');
1923 hr_utility.trace('Fetching Next Jurisdiction stored in PL Table');
1924 IF jurisdiction_codes_tbl.EXISTS(
1925 to_number(substr(p_jurisdiction_code,1,2) ||
1926 substr(p_jurisdiction_code,4,3) ||
1927 substr(p_jurisdiction_code,8,4) )
1928 ) THEN
1929
1930 IF jurisdiction_codes_tbl.NEXT(to_number(substr(p_jurisdiction_code,1,2) ||
1931 substr(p_jurisdiction_code,4,3) ||
1932 substr(p_jurisdiction_code,8,4) )
1933 ) is NULL THEN
1934 p_jurisdiction_code := 'NULL';
1935 p_percentage := 0;
1936 hr_utility.trace('Next jurisdiction is NULL');
1937 ELSE -- When next jurisdiction is Not Null
1938 --{
1939 -- This is to fetch next jurisdiction from the pl table
1940 -- when information time is being processed
1941 --{
1942 hr_utility.trace('Fetching Next Jurisdiction');
1943 l_index_value :=jurisdiction_codes_tbl.next (
1944 to_number(substr(p_jurisdiction_code,1,2) ||
1945 substr(p_jurisdiction_code,4,3) ||
1946 substr(p_jurisdiction_code,8,4) )
1947 );
1948 p_jurisdiction_code :=
1949 jurisdiction_codes_tbl(l_index_value).jurisdiction_code;
1950 p_percentage := jurisdiction_codes_tbl(l_index_value).percentage;
1951 --}
1952 END IF;
1953 --}
1954 ELSE -- if jurisdiction code passed does not exist in PL table
1955 --{
1956 hr_utility.trace('Jurisdiction Code '||p_jurisdiction_code||
1957 ' passed does not exist in PL table ');
1958 p_jurisdiction_code := 'NULL';
1959 p_percentage := 0;
1960 hr_utility.trace('Next jurisdiction is NULL');
1961 --}
1962 END IF;
1963 hr_utility.trace('p_jurisdiction_code = ' || p_jurisdiction_code);
1964 hr_utility.trace('p_percentage = ' || to_char(p_percentage));
1965 hr_utility.trace('End of get_work_jurisdictions for p_initialize=N');
1966 --}
1967 END IF;
1968 --
1969 -- This section is used only for debug
1970 hr_utility.trace('======================================================');
1971 IF jurisdiction_codes_tbl.COUNT > 0 THEN
1972 hr_utility.trace('Display the value of jurisdiction_codes_tbl');
1973 l_jurisdiction_code :=
1974 jurisdiction_codes_tbl(jurisdiction_codes_tbl.FIRST).jurisdiction_code;
1975 l_percentage :=
1976 jurisdiction_codes_tbl(jurisdiction_codes_tbl.FIRST).percentage;
1977 l_jd_type := jurisdiction_codes_tbl(jurisdiction_codes_tbl.FIRST).jd_type;
1978 hr_utility.trace('Jurisdiction_code 1st = '|| l_jurisdiction_code);
1979 hr_utility.trace('Percentage 1st = '|| to_char(l_percentage));
1980 hr_utility.trace('JD_Type 1st = '|| l_jd_type);
1981 l_jurisdiction_code :=
1982 jurisdiction_codes_tbl(jurisdiction_codes_tbl.LAST).jurisdiction_code;
1983 l_percentage :=
1984 jurisdiction_codes_tbl(jurisdiction_codes_tbl.LAST).percentage;
1985 l_jd_type := jurisdiction_codes_tbl(jurisdiction_codes_tbl.LAST).jd_type;
1986
1987 hr_utility.trace('Jurisdiction_code last = '|| l_jurisdiction_code);
1988 hr_utility.trace('Percentage last = '|| to_char(l_percentage));
1989 hr_utility.trace('JD_Type last = '|| l_jd_type);
1990 hr_utility.trace('======================================================');
1991 --
1992 -- End of pl/sql table debug messages
1993 --
1994 hr_utility.trace('Display the value of jurisdiction_codes_tbl');
1995 hr_utility.trace('End get_work_jurisdictions');
1996
1997 END IF; --jurisdiction_codes_tbl.COUNT > 0
1998
1999 RETURN ('0');
2000 /*EXCEPTION
2001 WHEN NO_DATA_FOUND THEN
2002 hr_utility.trace('Exception raised NO_DATA_FOUND in '||
2003 'get_work_jurisdictions');
2004 p_jurisdiction_code := 'NULL';
2005 p_percentage := 0;
2006 return ('0');
2007 WHEN TOO_MANY_JURISDICTIONS THEN
2008 hr_utility.set_message(801, 'PAY_75242_PAY_TOO_MANY_JD');
2009 hr_utility.set_message_token('MAX_WORK_JDS', l_max_jurisdictions);
2010 hr_utility.raise_error; -- create a new message--
2011 raise;
2012 WHEN OTHERS THEN
2013 hr_utility.trace('Exception raised OTHERS in '||
2014 'get_work_jurisdictions');
2015 hr_utility.trace('Mesg: '||substr(sqlerrm,1,45));
2016 p_jurisdiction_code := 'NULL';
2017 p_percentage := 0;
2018 return ('0');*/
2019
2020 -- End of Function get_work_jurisdictions
2021 END get_work_jurisdictions;
2022 --
2023
2024 FUNCTION Jurisdiction_processed( p_jurisdiction_code in varchar2
2025 ,p_jd_level in varchar
2026 )
2027 RETURN varchar2
2028 IS
2029 BEGIN
2030 IF p_jd_level = 'STATE' THEN
2031 IF state_processed_tbl.EXISTS( to_number(substr(p_jurisdiction_code,1,2))
2032 ) THEN
2033 RETURN 'Y';
2034 ELSE -- Added this state Jurisdiction to the state_processed_tbl table
2035 state_processed_tbl( to_number(substr(p_jurisdiction_code,1,2))
2036 ) := 'Y';
2037 RETURN 'N';
2038 END IF;
2039 ELSIF p_jd_level = 'COUNTY' THEN
2040 IF county_processed_tbl.EXISTS(to_number(substr(p_jurisdiction_code,1,2) ||
2041 substr(p_jurisdiction_code,4,3) )
2042 ) THEN
2043 RETURN 'Y';
2044 ELSE -- Added this state Jurisdiction to the county_processed_tbl table
2045 county_processed_tbl(to_number(substr(p_jurisdiction_code,1,2) ||
2046 substr(p_jurisdiction_code,4,3) )
2047 ) := 'Y';
2048 RETURN 'N';
2049 END IF;
2050 ELSIF p_jd_level = 'CITY' THEN
2051
2052 IF city_processed_tbl.EXISTS( to_number(substr(p_jurisdiction_code,1,2) ||
2053 substr(p_jurisdiction_code,4,3) ||
2054 substr(p_jurisdiction_code,8,4) )
2055 ) THEN
2056 RETURN 'Y';
2057 ELSE -- Added this state Jurisdiction to the city_processed_tbl table
2058 city_processed_tbl( to_number(substr(p_jurisdiction_code,1,2) ||
2059 substr(p_jurisdiction_code,4,3) ||
2060 substr(p_jurisdiction_code,8,4) )
2061 ) := 'Y';
2062 RETURN 'N';
2063 END IF;
2064
2065 ELSE
2066 return('N');
2067 END IF;
2068
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 return ('N');
2072 END Jurisdiction_processed;
2073
2074 FUNCTION get_fed_prev_ptd_values(
2075 p_assignment_action_id number, -- context
2076 p_tax_unit_id number, -- context
2077 p_fed_or_state varchar2, -- parameter
2078 p_regular_aggregate number, -- parameter
2079 calc_PRV_GRS OUT nocopy number,-- parameter
2080 calc_PRV_TAX OUT nocopy number)-- parameter
2081 RETURN NUMBER IS
2082 l_dummy_value number;
2083 BEGIN
2084 --{
2085 l_dummy_value := hr_us_ff_udf1.get_prev_ptd_values(p_assignment_action_id
2086 ,p_tax_unit_id
2087 ,'00-000-0000'
2088 ,p_fed_or_state
2089 ,p_regular_aggregate
2090 ,calc_PRV_GRS
2091 ,calc_PRV_TAX );
2092
2093 return 0;
2094 --
2095 EXCEPTION
2096 WHEN OTHERS THEN
2097 hr_utility.trace('Exception handler');
2098 hr_utility.trace('SQLCODE = ' || SQLCODE);
2099 hr_utility.trace('SQLERRM = ' || SUBSTR(SQLERRM,1,80));
2100 calc_PRV_GRS := 0;
2101 calc_PRV_TAX := 0;
2102 RETURN 0;
2103 --}
2104 END get_fed_prev_ptd_values;
2105
2106 FUNCTION get_jd_percent(p_jurisdiction_code VARCHAR2 -- Parameter
2107 ,p_jd_level VARCHAR2 -- Parameter
2108 ,p_hours_to_accumulate OUT nocopy NUMBER -- Parameter
2109 ,p_wages_to_accrue_flag OUT nocopy VARCHAR2 -- Parameter
2110 )
2111 RETURN NUMBER
2112 IS
2113 l_jd_level number;
2114 l_pad number;
2115 l_entry_jd number;
2116 l_max_jd number;
2117 l_temp_jd number;
2118 l_percentage number;
2119 l_index_value number;
2120 l_return number;
2121 begin
2122 --{
2123 hr_utility.trace('IN get_jd_percent');
2124 hr_utility.trace('get_jd_percent Use Information Hours Flag =>'||hr_us_ff_udf1.g_use_it_flag);
2125 IF hr_us_ff_udf1.g_use_it_flag = 'Y' THEN
2126 --{
2127 hr_utility.trace('get_jd_percent Calling Function get_it_jd_percent');
2128 l_percentage :=
2129 hr_us_ff_udf1.get_it_jd_percent(p_jurisdiction_code => p_jurisdiction_code
2130 ,p_jd_level => p_jd_level
2131 ,p_hours_to_accumulate => p_hours_to_accumulate
2132 ,p_wages_to_accrue_flag => p_wages_to_accrue_flag
2133 );
2134 hr_utility.trace('get_it_jd_percent Percentage Returned for '
2135 ||p_jd_level||' => '||to_char(l_percentage));
2136 -- RETURN l_percentage;
2137 --}
2138 ELSE
2139 --{
2140 hr_utility.trace('get_jd_percent p_jd_level = ' || p_jd_level) ;
2141 if p_jd_level = 'COUNTY' THEN
2142 l_jd_level := 5;
2143 else
2144 l_jd_level := 2;
2145 end if;
2146
2147 if substr(p_jurisdiction_code,1,1) = 0 then
2148 l_pad := 8;
2149 l_jd_level := l_jd_level - 1;
2150 else
2151 l_pad := 9;
2152 end if;
2153
2154 hr_utility.trace('get_jd_percent l_pad = ' || to_char(l_pad)) ;
2155 hr_utility.trace('get_jd_percent l_jd_level = ' || to_char(l_jd_level)) ;
2156
2157 l_entry_jd := to_number(substr(p_jurisdiction_code,1,2) ||
2158 substr(p_jurisdiction_code,4,3) ||
2159 substr(p_jurisdiction_code,8,4) );
2160
2161 l_temp_jd := rpad(substr(l_entry_jd,1,l_jd_level),l_pad,0);
2162 l_max_jd := rpad(substr(l_entry_jd,1,l_jd_level),l_pad,9);
2163
2164 hr_utility.trace('get_jd_percent l_temp_jd = ' || to_char(l_temp_jd)) ;
2165 hr_utility.trace('get_jd_percent l_max_jd = ' || to_char(l_max_jd)) ;
2166 hr_utility.trace('get_jd_percent next = ' || to_char(jurisdiction_codes_tbl.NEXT(l_temp_jd -1 ))) ;
2167
2168 if jurisdiction_codes_tbl.NEXT(l_temp_jd -1 ) is NULL
2169 OR jurisdiction_codes_tbl.NEXT(l_temp_jd -1 ) > l_max_jd THEN
2170 --{
2171 l_percentage := 0;
2172 --}
2173 else
2174 --{
2175 l_percentage := 0;
2176 l_index_value := jurisdiction_codes_tbl.NEXT(l_temp_jd - 1 );
2177 WHILE l_index_value is not null LOOP
2178 --{
2179 l_percentage := l_percentage + jurisdiction_codes_tbl(l_index_value).percentage;
2180
2181 IF jurisdiction_codes_tbl.NEXT(l_index_value) is NULL
2182 OR jurisdiction_codes_tbl.NEXT(l_index_value) > l_max_jd THEN
2183 --{
2184 l_index_value := NULL;
2185 --}
2186 ELSE
2187 --{
2188 l_index_value := jurisdiction_codes_tbl.NEXT(l_index_value);
2189 --}
2190 END IF;
2191 --}
2192 END LOOP;
2193 --}
2194 end if;
2195 hr_utility.trace('get_jd_percent Percentage Returned for '
2196 ||p_jd_level||' => '||to_char(l_percentage));
2197 -- RETURN l_percentage;
2198
2199 END IF;--IF g_use_it_flag = 'Y'
2200 RETURN l_percentage;
2201 EXCEPTION
2202 WHEN OTHERS THEN
2203 RETURN 0;
2204 --}
2205 end get_jd_percent;
2206 --
2207 -- This function would be used to fetch Jurisdiction type stored against a
2208 -- jurisdiction in the pl table jurisdiction_codes_tbl. This pl table is
2209 -- populated in function get_work_jurisdiction
2210 --
2211 FUNCTION get_jurisdiction_type(p_jurisdiction_code varchar2)
2212 RETURN varchar2
2213 IS
2214 l_jurisdiction_code varchar2(100);
2215 l_jd_type varchar2(100);
2216 BEGIN
2217 --{
2218 l_jurisdiction_code := p_jurisdiction_code;
2219 l_jd_type := 'NL';
2220 IF jurisdiction_codes_tbl.EXISTS(
2221 to_number(substr(l_jurisdiction_code,1,2) ||
2222 substr(l_jurisdiction_code,4,3) ||
2223 substr(l_jurisdiction_code,8,4) )
2224 ) THEN
2225 --{
2226 l_jd_type := jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
2227 1,2) ||
2228 substr(l_jurisdiction_code,4,3) ||
2229 substr(l_jurisdiction_code,8,4) )
2230 ).jd_type;
2231 --}
2232 ELSIF res_jurisdiction_codes_tbl.EXISTS(
2233 to_number(substr(l_jurisdiction_code,1,2) ||
2234 substr(l_jurisdiction_code,4,3) ||
2235 substr(l_jurisdiction_code,8,4) )
2236 ) THEN
2237 --{
2238 l_jd_type := res_jurisdiction_codes_tbl(to_number(substr(l_jurisdiction_code,
2239 1,2) ||
2240 substr(l_jurisdiction_code,4,3) ||
2241 substr(l_jurisdiction_code,8,4) )
2242 ).jd_type;
2243 --}
2244 ELSE
2245 --{
2246 l_jd_type := 'NL';
2247 --}
2248 END IF;
2249 return(l_jd_type);
2250
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 return ('NL');
2254 --}
2255 END get_jurisdiction_type;
2256
2257 --
2258 -- This function is used to fetch the status of Employee. It is used for determining
2259 -- whether executive weekly maximum should be applicable for a employee.
2260 --
2261 FUNCTION get_executive_status(p_assignment_id number,
2262 p_date_earned date,
2263 p_jurisdiction_code varchar2
2264 )
2265 RETURN varchar2
2266 IS
2267
2268 CURSOR get_executive_status
2269 IS
2270 select sta_information2
2271 from pay_us_emp_state_tax_rules_f
2272 where assignment_id = p_assignment_id
2273 and p_date_earned between effective_start_date and effective_end_date
2274 and jurisdiction_code = p_jurisdiction_code;
2275
2276 l_executive_status varchar2(1) := 'N';
2277 BEGIN
2278
2279 OPEN get_executive_status;
2280 FETCH get_executive_status INTO l_executive_status;
2281 CLOSE get_executive_status;
2282 return(nvl(l_executive_status,'N'));
2283 EXCEPTION
2284 WHEN OTHERS THEN
2285 return ('N');
2286
2287 END get_executive_status;
2288
2289 -- Bug#2122611
2290 -- This function used for determining whether Workers compensation
2291 -- amount should be deducted in the current pay period or not.
2292 --
2293
2294 FUNCTION get_wc_flag(p_assignment_id number,
2295 p_date_earned date,
2296 p_wc_flat_rate_period varchar2
2297 )
2298 RETURN varchar2
2299 IS
2300
2301 l_last_period_for_wc varchar2(1) := 'N';
2302 l_pay_period_start_date date;
2303 l_pay_period_end_date date;
2304 l_date date;
2305 l_valid number(1);
2306
2307 -- get start and end dates of the pay period
2308 CURSOR csr_get_period_dates IS
2309 select ptp.start_date,
2310 ptp.end_date
2311 from per_all_assignments_f paaf,
2312 pay_all_payrolls_f papf,
2313 per_time_periods ptp
2314 where paaf.payroll_id = papf.payroll_id
2315 and papf.payroll_id = ptp.payroll_id
2316 and assignment_id = p_assignment_id
2317 and p_date_earned between ptp.start_date
2318 and ptp.end_date
2319 and p_date_earned between papf.effective_start_date
2320 and papf.effective_end_date
2321 and p_date_earned between paaf.effective_start_date
2322 and paaf.effective_end_date;
2323
2324 -- Checks whether the assignment is valid as of last date of the pay period.
2325 CURSOR CSR_GET_VALID_ASSIGNMENT IS
2326 SELECT 1
2327 FROM per_all_assignments_f paaf
2328 WHERE l_date between paaf.effective_start_date
2329 and paaf.effective_end_date;
2330
2331 BEGIN
2332
2333 OPEN csr_get_period_dates;
2334
2335 FETCH csr_get_period_dates INTO l_pay_period_start_date,l_pay_period_end_date;
2336 IF csr_get_period_dates%NOTFOUND THEN
2337 CLOSE csr_get_period_dates;
2338 return (l_last_period_for_wc);
2339 END IF;
2340
2341 IF (p_wc_flat_rate_period = 'YEAR') THEN
2342 l_date := to_date( '31-12-' || to_char(l_pay_period_start_date,'yyyy'),'DD-MM-YYYY');
2343 -- last day of the year
2344
2345 ELSIF (p_wc_flat_rate_period = 'MONTH') THEN
2346 l_date := last_day(l_pay_period_start_date);
2347 -- last day of the month
2348
2349 ELSIF (p_wc_flat_rate_period = 'QUARTER') THEN
2350 l_date := add_months(trunc(l_pay_period_start_date,'YY'),3*to_number(to_char(l_pay_period_start_date,'Q')))-1;
2351 -- last day of the Quarter
2352 END IF;
2353
2354 if (l_date between l_pay_period_start_date and l_pay_period_end_date ) then
2355 l_last_period_for_wc := 'Y'; -- deduct WC in the current pay period.
2356 else
2357 l_last_period_for_wc := 'N';
2358 end if;
2359
2360 CLOSE csr_get_period_dates;
2361
2362 -- Check the assignment's validity as of last date of the pay period.
2363 OPEN CSR_GET_VALID_ASSIGNMENT;
2364 FETCH CSR_GET_VALID_ASSIGNMENT into l_valid;
2365
2366 IF CSR_GET_VALID_ASSIGNMENT%NOTFOUND THEN
2367 CLOSE CSR_GET_VALID_ASSIGNMENT;
2368 return ('N');
2369 END IF;
2370 CLOSE CSR_GET_VALID_ASSIGNMENT;
2371 return(l_last_period_for_wc);
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374 return ('N');
2375
2376 END get_wc_flag;
2377
2378 --
2379 -- FUNCTION get_it_work_jurisdictions
2380 --
2381 /* This Function used to manage pl/table for work/tagged/home jurisdictions
2382 associated with an assignment
2383
2384 Parameter Purpose
2385 --------- -------
2386 p_INITIALIZE This parmaeter determines to process the pl/table
2387 jurisdiction_codes_tbl. This parameter expects one of 3
2388 values. (Y, N, F)
2389 Y denotes Initialize and populate the pl table
2390 N denotes Fetch jurisction that is stored next to the
2391 jurisdiction assigned to p_jurisdiction_code
2392 F denotes Fecth the First jurisdiction stored in the pl
2393 table
2394
2395 This function is being called from US_TAX_VERTEX2 formula with P_INITIALIZE
2396 value as 'Y'. PL table is always initialized for each assignment.
2397
2398 This function is called from US_TAX_VERTEX_HOME2 formula with P_INITIALIZE
2399 value as 'F'.
2400
2401 This function is repeatedly called from US_TAX_VERTEX_WORK2 depending on the
2402 number of work jurisdiction stored in the pl table.For this call P_INITIALIZE
2403 value is set as 'N'.
2404 */
2405 FUNCTION get_it_work_jurisdictions(p_assignment_action_id IN NUMBER
2406 ,p_initialize IN VARCHAR2
2407 ,p_jurisdiction_code IN OUT NOCOPY VARCHAR2
2408 ,p_percentage OUT NOCOPY NUMBER
2409 ,p_assignment_id IN NUMBER
2410 ,p_date_paid IN DATE
2411 ,p_date_earned IN DATE
2412 ,p_time_period_id IN NUMBER
2413 ,p_payroll_id IN NUMBER
2414 ,p_business_group_id IN NUMBER
2415 ,p_tax_unit_id IN NUMBER
2416 )
2417 RETURN VARCHAR2
2418 IS
2419
2420 TOO_MANY_JURISDICTIONS EXCEPTION;
2421 l_max_jurisdictions NUMBER;
2422 l_assignment_id NUMBER;
2423 l_date_paid DATE;
2424 l_date_earned DATE;
2425
2426 l_ee_id NUMBER;
2427 l_jurisdiction_code VARCHAR2(11);
2428 l_res_jurisdiction_code VARCHAR2(11);
2429 l_work_jurisdiction_code VARCHAR2(11);
2430 l_wk_jurisdiction_code VARCHAR2(11);
2431 l_jd_type VARCHAR2(2);
2432
2433 l_percentage NUMBER;
2434 p_array_count NUMBER;
2435 l_index_value NUMBER;
2436
2437 l_jd_found VARCHAR2(1);
2438 l_return_value VARCHAR2(28);
2439
2440 l_state VARCHAR2(2);
2441 l_county VARCHAR2(120);
2442 l_city VARCHAR2(30);
2443 l_zip_code VARCHAR2(10);
2444
2445 l_res_state VARCHAR2(2);
2446 l_res_county VARCHAR2(120);
2447 l_res_city VARCHAR2(100);
2448 l_res_zip VARCHAR2(10);
2449 l_wah VARCHAR2(1);
2450
2451 cnt NUMBER;
2452
2453 -- Get Further Payroll Information , Use Informational Hours From flag.
2454 -- Current_pay_period (C) or Previous_pay_period(P)
2455 CURSOR csr_period_flag (p_assignment_action_id IN NUMBER) IS
2456 SELECT NVL(prl_information12 ,'P') --Defaulted to Previous
2457 ,NVL(prl_information13 ,'YTD') --defaulted to Tax Year
2458 FROM pay_payrolls_f payroll,
2459 pay_assignment_actions paa,
2460 pay_payroll_actions ppa
2461 WHERE ppa.payroll_id = payroll.payroll_id
2462 AND paa.payroll_action_id = ppa.payroll_action_id
2463 AND paa.assignment_action_id = p_assignment_action_id
2464 AND NVL(ppa.date_earned,ppa.effective_date)
2465 BETWEEN payroll.effective_start_date
2466 AND payroll.effective_end_date;
2467
2468 l_pay_period_flag pay_payrolls_f.prl_information11%TYPE;
2469 l_threshold_basis pay_payrolls_f.prl_information13%TYPE;
2470
2471 -- Cursor to get all the informational time element entries
2472 -- Jurisdiction Code and Hours screen entry values are retrieved
2473 -- All element entries are considered based on the start date and end date.
2474 -- Hours are summed for each jurisdiction.
2475 --
2476 CURSOR csr_it_element_entries(p_start_date IN DATE,
2477 p_end_date IN DATE,
2478 p_assignment_id IN NUMBER,
2479 p_date_earned IN DATE) IS
2480 SELECT pev1.screen_entry_value Jurisdiction,
2481 SUM(pev2.screen_entry_value) Hours
2482 FROM pay_element_entry_values_f pev1,
2483 pay_element_entry_values_f pev2,
2484 pay_element_entries_f pee,
2485 pay_element_links_f pel,
2486 pay_element_types_f pet,
2487 pay_input_values_f piv1,
2488 pay_input_values_f piv2,
2489 pay_element_type_extra_info extra
2490 WHERE extra.information_type = 'PAY_US_INFORMATION_TIME'
2491 AND extra.eei_information1 = 'Y'
2492 AND extra.element_type_id = pet.element_type_id
2493 AND pet.element_type_id = pel.element_type_id
2494 AND p_end_date BETWEEN pet.effective_start_date
2495 AND pet.effective_end_date
2496 AND pel.element_link_id = pee.element_link_id
2497 AND p_end_date BETWEEN pel.effective_start_date
2498 AND pel.effective_end_date
2499 AND pee.assignment_id = p_assignment_id
2500 AND ( (pee.effective_start_date BETWEEN p_start_date
2501 AND p_end_date)
2502 OR
2503 (pee.effective_end_date BETWEEN p_start_date
2504 AND p_end_date)
2505 )
2506 AND pee.element_entry_id = pev1.element_entry_id
2507 AND p_end_date BETWEEN pee.effective_start_date
2508 AND pee.effective_end_date
2509 AND pev1.input_value_id = piv1.input_value_id
2510 AND p_end_date BETWEEN pev1.effective_start_date
2511 AND pev1.effective_end_date
2512 AND piv1.name = 'Jurisdiction'
2513 AND p_end_date BETWEEN piv1.effective_start_date
2514 AND piv1.effective_end_date
2515 AND pee.element_entry_id = pev2.element_entry_id
2516 AND p_end_date BETWEEN pee.effective_start_date
2517 AND pee.effective_end_date
2518 AND pev2.input_value_id = piv2.input_value_id
2519 AND piv2.name = 'Hours'
2520 AND p_end_date BETWEEN piv2.effective_start_date
2521 AND piv2.effective_end_date
2522 AND pev1.screen_entry_value IS NOT NULL
2523 AND pev2.screen_entry_value IS NOT NULL
2524 GROUP BY pev1.screen_entry_value;
2525
2526 l_sum_hours NUMBER;
2527 l_scheduled_work_hours NUMBER;
2528 l_total_hours NUMBER;
2529 l_work_hours NUMBER;
2530 l_remaining_hours NUMBER;
2531 l_jd_hours NUMBER;
2532 l_end_date DATE;
2533 l_start_date DATE;
2534 l_time_period_id pay_payroll_actions.time_period_id%TYPE;
2535 l_counter INTEGER;
2536 l_tg_jurisdiction_code VARCHAR2(20);
2537 l_tg_sum_hours NUMBER;
2538 l_tg_total_hours NUMBER;
2539 l_tg_hours_fetched NUMBER;
2540 l_tg_jd_code_fetched VARCHAR2(20);
2541 l_it_hours_fetched NUMBER;
2542 l_hours_fetched NUMBER;
2543 l_tg_hours NUMBER;
2544 l_ws_name VARCHAR2(200);
2545 l_total_percent NUMBER;
2546 l_primary_work_jd_flag VARCHAR2(1);
2547 l_primary_work_jd_index_value NUMBER;
2548 l_extra_percent NUMBER;
2549 l_last_jd_index_value NUMBER;
2550 l_denominator NUMBER;
2551
2552 -- Get start_date, end_date for the given time_period_id and payroll_id
2553 --
2554 CURSOR csr_time_period(p_time_period_id IN NUMBER,
2555 p_payroll_id IN NUMBER) IS
2556 SELECT end_date,
2557 start_date
2558 FROM per_time_periods
2559 WHERE time_period_id = p_time_period_id
2560 AND payroll_id = p_payroll_id;
2561
2562
2563 -- Get start_date, end_date for the given time_period_id and payroll_id
2564 --
2565 CURSOR csr_time_period_prev(p_prev_end_date IN DATE,
2566 p_payroll_id IN NUMBER) IS
2567 SELECT end_date,
2568 start_date
2569 FROM per_time_periods
2570 WHERE end_date = p_prev_end_date
2571 AND payroll_id = p_payroll_id;
2572
2573 -- Get Work Jurisdiction for the assignment
2574 --
2575 CURSOR csr_work_jd (p_date_earned IN DATE, p_assignment_id IN NUMBER) IS
2576 SELECT NVL(hrloc.loc_information18,hrloc.town_or_city),
2577 NVL(hrloc.loc_information19,hrloc.region_1),
2578 NVL(hrloc.loc_information17,hrloc.region_2),
2579 SUBSTR(NVL(hrloc.loc_information20,hrloc.postal_code),1,5)
2580 FROM hr_locations hrloc
2581 ,hr_soft_coding_keyflex hrsckf
2582 ,per_all_assignments_f assign
2583 WHERE p_date_earned BETWEEN assign.effective_start_date
2584 AND assign.effective_end_date
2585 AND assign.assignment_id = p_assignment_id
2586 AND assign.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
2587 AND NVL(hrsckf.segment18,
2588 assign.location_id) = hrloc.location_id;
2589 /*
2590 --Get the Positive Pay entries(same as Tagged Entries)
2591 --
2592 CURSOR csr_tagged_entries(p_start_date IN DATE,
2593 p_end_date IN DATE,
2594 p_assignment_id IN NUMBER,
2595 p_date_earned IN DATE) IS
2596 SELECT pev1.screen_entry_value Jurisdiction,
2597 SUM(pev2.screen_entry_value) Hours
2598 FROM pay_element_entry_values_f pev1,
2599 pay_element_entry_values_f pev2,
2600 pay_element_entries_f pee,
2601 pay_element_links_f pel,
2602 pay_element_types_f pet,
2603 pay_input_values_f piv1,
2604 pay_input_values_f piv2,
2605 pay_element_classifications pec
2606 WHERE pec.classification_name IN ( 'Earnings', 'Supplemental Earnings','Imputed Earnings' )
2607 AND pec.legislation_code = 'US'
2608 AND pec.classification_id = pet.classification_id
2609 AND pet.element_type_id = pel.element_type_id
2610 AND p_date_earned BETWEEN pet.effective_start_date
2611 AND pet.effective_end_date
2612 AND pel.element_link_id = pee.element_link_id
2613 AND p_date_earned BETWEEN pel.effective_start_date
2614 AND pel.effective_end_date
2615 AND pee.assignment_id = p_assignment_id
2616 AND pee.effective_start_date BETWEEN p_start_date
2617 AND p_end_date
2618 AND pee.element_entry_id = pev1.element_entry_id
2619 AND p_date_earned BETWEEN pee.effective_start_date
2620 AND pee.effective_end_date
2621 AND pev1.input_value_id = piv1.input_value_id
2622 AND p_date_earned BETWEEN pev1.effective_start_date
2623 AND pev1.effective_end_date
2624 AND piv1.name = 'Jurisdiction'
2625 AND p_date_earned BETWEEN piv1.effective_start_date
2626 AND piv1.effective_end_date
2627 AND pee.element_entry_id = pev2.element_entry_id
2628 AND p_date_earned BETWEEN pee.effective_start_date
2629 AND pee.effective_end_date
2630 AND pev2.input_value_id = piv2.input_value_id
2631 AND piv2.name = 'Hours'
2632 AND p_date_earned BETWEEN piv2.effective_start_date
2633 AND piv2.effective_end_date
2634 AND pev1.screen_entry_value IS NOT NULL
2635 AND pev2.screen_entry_value IS NOT NULL
2636 GROUP BY pev1.screen_entry_value;
2637 */
2638
2639 CURSOR csr_tagged_entries(p_start_date IN DATE,
2640 p_end_date IN DATE,
2641 p_assignment_id IN NUMBER,
2642 p_date_earned IN DATE)
2643 IS
2644 SELECT /*+ INDEX (paa pay_assignment_actions_n51) */ DISTINCT
2645 peev.screen_entry_value Jurisdiction,
2646 0 Hours
2647 FROM pay_element_classifications pec
2648 ,pay_element_types_f pet
2649 ,pay_element_entries_f pee
2650 ,pay_element_links_f pel
2651 ,pay_input_values_f piv
2652 ,pay_element_entry_values_f peev
2653 WHERE pec.classification_name in
2654 ( 'Earnings', 'Supplemental Earnings','Imputed Earnings' )
2655 AND pec.legislation_code = 'US'
2656 AND pet.classification_id = pec.classification_id
2657 AND (( pee.effective_start_date BETWEEN p_start_date
2658 AND p_end_date)
2659 OR
2660 ( pee.effective_end_date BETWEEN p_start_date
2661 AND p_end_date)
2662 )
2663 AND p_end_date BETWEEN pet.effective_start_date
2664 AND pet.effective_end_date
2665 AND pee.assignment_id = p_assignment_id
2666 AND pet.element_type_id = pel.element_type_id
2667 AND pel.element_link_id = pee.element_link_id
2668 AND p_end_date BETWEEN pel.effective_start_date
2669 AND pel.effective_end_date
2670 AND pet.element_type_id = piv.element_type_id
2671 AND piv.name = 'Jurisdiction'
2672 AND pee.effective_start_date BETWEEN piv.effective_start_date
2673 AND piv.effective_end_date
2674 AND pee.element_entry_id = peev.element_entry_id
2675 AND peev.input_value_id = piv.input_value_id
2676 AND pee.effective_start_date BETWEEN peev.effective_start_date
2677 AND peev.effective_end_date
2678 AND peev.screen_entry_value IS NOT NULL;
2679
2680
2681 -- Get work schedule details
2682 --
2683 CURSOR csr_ws(p_assignment_id IN NUMBER,p_date_earned IN DATE) IS
2684 SELECT segment4
2685 FROM hr_soft_coding_keyflex target,
2686 per_all_assignments_f assign
2687 WHERE ASSIGN.assignment_id = p_assignment_id
2688 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
2689 AND target.enabled_flag = 'Y'
2690 AND p_date_earned BETWEEN assign.effective_start_date
2691 AND assign.effective_end_date;
2692
2693 CURSOR csr_resident_jd(p_assignment_id IN NUMBER,p_date_earned IN DATE) IS
2694 SELECT NVL(addr.add_information17,addr.region_2) state,
2695 NVL(addr.add_information19,addr.region_1) county,
2696 NVL(addr.add_information18,addr.town_or_city) city,
2697 NVL(addr.add_information20,addr.postal_code) zip,
2698 NVL(assign.work_at_home,'N')
2699 FROM per_addresses addr
2700 ,per_all_assignments_f assign
2701 WHERE p_date_earned BETWEEN assign.effective_start_date
2702 AND assign.effective_end_date
2703 AND assign.assignment_id = p_assignment_id
2704 AND addr.person_id = assign.person_id
2705 AND addr.primary_flag = 'Y'
2706 AND p_date_earned BETWEEN NVL(addr.date_from, p_date_earned)
2707 AND NVL(addr.date_to, p_date_earned);
2708
2709 -- Get Full Name, Assignment Number
2710 CURSOR csr_person_details(p_assignment_id IN NUMBER,p_date_paid IN DATE) IS
2711 SELECT ppf.full_name, paf.assignment_number
2712 FROM per_all_people_f ppf,
2713 per_all_assignments_f paf
2714 WHERE ppf.person_id = paf.person_id
2715 AND paf.assignment_id = p_assignment_id
2716 AND p_date_paid BETWEEN paf.effective_start_date
2717 AND paf.effective_end_date
2718 AND p_date_paid BETWEEN ppf.effective_start_date
2719 AND ppf.effective_end_date;
2720
2721 l_full_name per_all_people_f.full_name%TYPE;
2722 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
2723
2724 -- Get actual_termination_date for the person
2725 CURSOR csr_eff_dates(p_assignment_id IN NUMBER, p_date_paid IN DATE ) IS
2726 SELECT paa.effective_start_date,
2727 paa.effective_end_date
2728 FROM per_all_assignments_f paa
2729 WHERE paa.assignment_id = p_assignment_id
2730 AND p_date_paid BETWEEN paa.effective_start_date
2731 AND paa.effective_end_date;
2732
2733 l_effective_start_date DATE;
2734 l_max_start_date DATE;
2735 l_effective_end_date DATE;
2736 l_max_end_date DATE;
2737 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
2738
2739 -- Get actual_termination_date for the person
2740 CURSOR csr_term_dates(p_assignment_id IN NUMBER, p_date_earned IN DATE,p_date_start IN DATE,p_date_end IN DATE ) IS
2741 SELECT pps.actual_termination_date,pps.date_start
2742 FROM per_periods_of_service pps,
2743 per_all_assignments_f paa
2744 WHERE paa.assignment_id = p_assignment_id
2745 AND paa.person_id = pps.person_id
2746 AND p_date_earned BETWEEN paa.effective_start_date
2747 AND paa.effective_end_date
2748 AND p_date_end >= pps.date_start
2749 AND p_date_start <= NVL(pps.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'));
2750
2751
2752 -- Get
2753 CURSOR csr_person_id (p_assignment_id IN NUMBER) IS
2754 SELECT person_id
2755 FROM per_all_assignments_f
2756 WHERE assignment_id = p_assignment_id;
2757
2758 l_person_id per_all_assignments_f.person_id%TYPE;
2759
2760 l_jurisdiction VARCHAR2(20);
2761 l_calc_percent VARCHAR2(10);
2762 l_threshold_hours_state NUMBER;
2763 l_threshold_hours_county NUMBER;
2764 l_threshold_hours_city NUMBER;
2765 l_sit_withheld NUMBER;
2766 l_county_withheld NUMBER;
2767 l_city_withheld NUMBER;
2768 l_ih_excluding_pay_period NUMBER;
2769 l_ih_above_threshold NUMBER;
2770 l_ih_for_primary_wk NUMBER;
2771 l_state_ih_logged NUMBER;
2772 l_county_ih_logged NUMBER;
2773 l_city_ih_logged NUMBER;
2774 l_total_state_hours NUMBER;
2775 l_total_county_hours NUMBER;
2776 l_total_state_percent NUMBER;
2777 l_total_county_percent NUMBER;
2778 l_in_counter INTEGER;
2779 l_prev_end_date DATE;
2780 /*Bug#5981447: Variables to hold start and end dates
2781 to calculate work schduled hours */
2782 l_ws_start_date DATE;
2783 l_ws_end_date DATE;
2784 /*Bug#5981447: Ends here */
2785 l_sit_city_withheld NUMBER;
2786 l_county_city_withheld NUMBER;
2787 l_sit_county_withheld NUMBER;
2788
2789 l_spelled_jd_code VARCHAR2(200);
2790
2791 BEGIN
2792 --{
2793 l_max_jurisdictions := 200;
2794 l_total_hours := 0;
2795 l_work_hours := 0;
2796 l_remaining_hours := 0;
2797 l_jd_hours := 0;
2798 l_counter := NULL;
2799 l_tg_total_hours := 0;
2800 l_total_percent := 0;
2801 l_primary_work_jd_flag := 'N';
2802 l_extra_percent := 0;
2803 l_denominator := 0;
2804
2805 l_total_state_hours := 0;
2806 l_total_county_hours := 0;
2807 l_total_state_percent := 0;
2808 l_total_county_percent := 0;
2809
2810 l_sit_city_withheld := 0;
2811 l_county_city_withheld := 0;
2812 l_sit_county_withheld := 0;
2813
2814 hr_utility.trace('EMJT : Begin get_it_work_jurisdictions');
2815 hr_utility.trace('EMJT : p_assignment_action_id -> '
2816 ||to_char(p_assignment_action_id));
2817 hr_utility.trace('EMJT : p_assignment_id -> '||
2818 to_char(p_assignment_id));
2819 hr_utility.trace('EMJT : p_date_earned -> '||to_char(p_date_earned));
2820 hr_utility.trace('EMJT : p_date_paid -> '||to_char(p_date_paid));
2821 hr_utility.trace('EMJT : p_time_period_id -> '||
2822 to_char(p_time_period_id));
2823 hr_utility.trace('EMJT : p_payroll_id -> '||to_char(p_payroll_id));
2824 hr_utility.trace('EMJT : =====================================================');
2825
2826
2827 OPEN csr_person_id (p_assignment_id);
2828 FETCH csr_person_id INTO l_person_id;
2829 IF csr_person_id%NOTFOUND THEN
2830 hr_utility.trace('EMJT: Person Id not found');
2831 END IF;
2832
2833 CLOSE csr_person_id;
2834
2835 --
2836 -- Determine the pay period, whether it is current or previous
2837 -- This is used to fetch element entries for the user configured period
2838 --
2839 OPEN csr_period_flag(p_assignment_action_id);
2840 FETCH csr_period_flag INTO l_pay_period_flag,
2841 l_threshold_basis;
2842 CLOSE csr_period_flag;
2843 hr_utility.trace('EMJT : l_pay_period_flag -> '|| l_pay_period_flag );
2844 hr_utility.trace('EMJT : l_threshold_basis -> '|| l_threshold_basis );
2845 /* IF l_pay_period_flag = 'C' THEN --Current Pay Period
2846 OPEN csr_time_period(p_time_period_id, p_payroll_id);
2847 ELSIF l_pay_period_flag = 'P' THEN --Previous Pay Period
2848 -- l_time_period_id := p_time_period_id - 1;
2849 -- IF l_time_period_id <= 0 THEN
2850 -- l_time_period_id := p_time_period_id;
2851 -- END IF;
2852 OPEN csr_time_period(l_time_period_id,p_payroll_id);
2853 END IF; --l_pay_period_flag = 'C'
2854 */
2855 OPEN csr_time_period(p_time_period_id, p_payroll_id);
2856 FETCH csr_time_period INTO l_end_date,
2857 l_start_date;
2858 CLOSE csr_time_period;
2859 /* Assign current pay period's start and end dates */
2860 l_ws_start_date := l_start_date;
2861 l_ws_end_date := l_end_date;
2862
2863 OPEN csr_term_dates (p_assignment_id, p_date_earned,l_start_date,l_end_date);
2864 FETCH csr_term_dates INTO l_effective_end_date,l_effective_start_date;
2865 CLOSE csr_term_dates;
2866
2867 l_effective_end_date := NVL( l_effective_end_date,to_date('12/31/4712','mm/dd/yyyy'));
2868
2872 IF l_pay_period_flag = 'P' THEN
2869 hr_utility.trace('EMJT : l_effective_start_date -> '|| l_effective_start_date);
2870 hr_utility.trace('EMJT : l_effective_end_date -> '|| l_effective_end_date);
2871
2873
2874 l_prev_end_date := l_start_date - 1;
2875
2876 OPEN csr_time_period_prev(l_prev_end_date, p_payroll_id);
2877 FETCH csr_time_period_prev INTO l_end_date, l_start_date;
2878
2879 /*There is no previous pay period available.
2880 Assign null to l_start_Date and l_end_date */
2881
2882 IF csr_time_period_prev%NOTFOUND THEN
2883 l_start_date :=null;
2884 l_end_date :=null;
2885 ELSIF l_effective_start_date >=l_end_date or l_effective_end_date <= l_start_date THEN
2886 /* no previous pay period exists for the assignment */
2887 l_start_date :=null;
2888 l_end_date :=null;
2889 ELSE
2890 l_ws_start_date := l_start_date;
2891 l_ws_end_date := l_end_date;
2892 END IF;
2893
2894 CLOSE csr_time_period_prev;
2895
2896 END IF;
2897
2898 hr_utility.trace('EMJT : After csr_time_period -> ');
2899 hr_utility.trace('EMJT : l_end_date -> '||to_char(l_end_date));
2900 hr_utility.trace('EMJT : l_start_date -> '||to_char(l_start_date));
2901 hr_utility.trace('EMJT : l_ws_end_date -> '||to_char(l_ws_end_date));
2902 hr_utility.trace('EMJT : l_ws_start_date -> '||to_char(l_ws_start_date));
2903
2904 -- Need to fetch scheduled hours configured for the assignment for the
2905 -- current pay period.
2906 OPEN csr_ws(p_assignment_id,
2907 p_date_earned);
2908 FETCH csr_ws INTO l_ws_name;
2909 IF csr_ws%NOTFOUND THEN
2910 hr_utility.trace('EMJT : get_id_work_jurisdiction Work Scheduled Not Found ');
2911 END IF;
2912 CLOSE csr_ws;
2913
2914 /* Start and end dates for the work scheduled hours calculation */
2915 /*Bug#7114362: l_ws_start_date should not be NULL even when l_effective_start_date is
2916 NULL*/
2917 l_ws_start_date := greatest ( NVL(l_effective_start_date,to_date('01-01-0001','DD-MM-YYYY')), l_ws_start_date);
2918 l_ws_end_date := least ( l_effective_end_date, l_ws_end_date);
2919
2920 /* Start and end dates for fetching information and tagged hours */
2921 IF l_start_date is null and l_end_date is null THEN
2922 /* There is no Previous pay period available.
2923 No need to process information and tagged hours */
2924 l_max_start_date := null;
2925 l_max_end_Date := null;
2926 ELSE
2927 l_max_start_date := l_ws_start_date;
2928 l_max_end_Date := l_ws_end_date;
2929 END IF;
2930
2931 hr_utility.trace('EMJT : l_max_start_date -> '|| l_max_start_date);
2932 hr_utility.trace('EMJT : l_max_end_date -> '|| l_max_end_date);
2933 hr_utility.trace('EMJT : l_ws_end_date -> '||to_char(l_ws_end_date));
2934 hr_utility.trace('EMJT : l_ws_start_date -> '||to_char(l_ws_start_date));
2935
2936 /* need to use l_ws_start_date and l_ws_end_date for work scheduled hours calculation */
2937
2938 l_scheduled_work_hours :=
2939 hr_us_ff_udfs.work_schedule_total_hours(p_business_group_id,
2940 l_ws_name,
2941 l_ws_start_date,
2942 l_ws_end_date);
2943
2944 IF l_ws_start_date = l_ws_end_date AND
2945 NVL(l_scheduled_work_hours,0) <= 0 THEN
2946
2947 l_scheduled_work_hours :=
2948 hr_us_ff_udfs.work_schedule_total_hours(p_business_group_id,
2949 l_ws_name,
2950 l_ws_start_date,
2951 l_ws_end_date);
2952 IF l_scheduled_work_hours = 0 THEN
2953 hr_utility.trace('EMJT : Scheduled hours set to 8');
2954 l_scheduled_work_hours := 8; -- Defaulted to 8 hours
2955 END IF;
2956 END IF;
2957
2958
2959 hr_utility.trace('EMJT : Scheduled Hours for the assignment -> '||
2960 to_char(l_scheduled_work_hours));
2961 IF p_initialize = 'Y' THEN
2962 --{
2963 -- pl/sql table initialized for all twork location where Information Time
2964 -- and Positive Pay hours are logged. This call initiated from the
2965 -- US_TAX_VERTEX2 fast formula
2966
2967 hr_utility.trace('EMJT : get_it_work_jurisdictions || p_initialize = Y');
2968 jurisdiction_codes_tbl.delete;
2969 state_processed_tbl.delete;
2970 county_processed_tbl.delete;
2971 city_processed_tbl.delete;
2972
2973 jurisdiction_codes_tbl_stg.delete;
2974
2975 -- GET the RESIDENT jurisdictions and load in to the *_processed_tables
2976 --
2977 hr_utility.trace('EMJT : get_work_jurisdictions Get Resident Address details');
2978
2979 OPEN csr_resident_jd(p_assignment_id,p_date_earned);
2980 FETCH csr_resident_jd INTO l_res_state,
2981 l_res_county,
2982 l_res_city,
2983 l_res_zip,
2984 l_wah;
2985
2986 CLOSE csr_resident_jd;
2987
2988 hr_utility.trace('EMJT : get_it_work_jurisdictions Resident Address Fetched');
2989 l_res_jurisdiction_code := hr_us_ff_udfs.addr_val(l_res_state
2990 , l_res_county
2991 , l_res_city
2992 , l_res_zip);
2993
2994 -- IF this is a user defined city IE: city_code = 'U***' the change
2995 -- the city code to all 0 (zeros)
2996
2997 IF SUBSTR(l_res_jurisdiction_code,8,1) = 'U' THEN
2998 l_res_jurisdiction_code := SUBSTR(l_res_jurisdiction_code,1,7) ||
2999 '0000' ;
3000 END IF;
3001 hr_utility.trace('EMJT : Resident Jurisdiction Code -> ' ||
3002 l_res_jurisdiction_code);
3003 hr_utility.trace('EMJT : Home Workers Flag -> ' || l_wah);
3004
3005 --
3006 -- Determine the address components of primary work jurisdiction.
3007 --
3008 hr_utility.trace('EMJT : get_it_work_jurisdictions Fetch Primary Work Location');
3009 OPEN csr_work_jd(p_date_earned,
3010 p_assignment_id);
3011 FETCH csr_work_jd INTO l_city,
3012 l_county,
3013 l_state,
3014 l_zip_code;
3015
3016 IF csr_work_jd%NOTFOUND THEN
3017 hr_utility.trace('EMJT : Primary Work Location address componets NOT Found');
3018 END IF;
3019 CLOSE csr_work_jd;
3020 hr_utility.trace('EMJT : Determine Jurisdiction Code for Primary Work Location');
3021 l_work_jurisdiction_code := hr_us_ff_udfs.addr_val(l_state,
3022 l_county,
3023 l_city,
3024 l_zip_code);
3025
3026 hr_utility.trace('EMJT : Primary work Jursdiction CODE -> '
3027 || l_work_jurisdiction_code);
3028
3029 -- Check to see whether employee is configured as "Home Worker"
3030 -- If employee is a Home worker use resident jurisdiction as primary
3031 -- work jurisdiction code instead of actual primary work jurisdiction
3032 -- available for the assignment
3033
3034 IF l_wah = 'Y' THEN
3035 l_work_jurisdiction_code := l_res_jurisdiction_code;
3036 hr_utility.trace('EMJT : As assignment is configured as Home Worker ');
3037 hr_utility.trace('EMJT : Residence Jurisdiction overrides the Primary Work Jurisdiction');
3038 hr_utility.trace('EMJT : Primary work Jursdiction CODE -> '
3039 || l_work_jurisdiction_code);
3040 END IF;
3041
3042 -- Fetch all the informational time element entries logged for the
3043 -- Pay period being processed
3044 hr_utility.trace('EMJT : Fetching all Information Hours Element Entris for Assignment');
3045 hr_utility.trace('EMJT : For Assignment '||to_char(p_assignment_id));
3046 hr_utility.trace('EMJT : Start Date '||to_char(l_start_date,'dd-mon-yyyy'));
3047 hr_utility.trace('EMJT : End Date '||to_char(l_end_date,'dd-mon-yyyy'));
3048 BEGIN
3049 /* OPEN csr_it_element_entries(l_start_date,
3050 l_end_date,
3051 p_assignment_id,
3052 p_date_earned);
3053 */
3054
3055 OPEN csr_it_element_entries(l_max_start_date, --l_start_date,
3056 l_max_end_date, --l_end_date,
3057 p_assignment_id,
3058 p_date_earned);
3059
3060 FETCH csr_it_element_entries INTO l_jurisdiction_code,
3061 l_sum_hours;
3062 hr_utility.trace('EMJT : 1st Information Time JD Code fetched-> '||l_jurisdiction_code);
3063 hr_utility.trace('EMJT : 1st Information Time Hours -> '||to_char(l_sum_hours));
3064 LOOP
3065 --{
3066 EXIT WHEN csr_it_element_entries%NOTFOUND;
3067
3068 jurisdiction_codes_tbl_stg( TO_NUMBER(SUBSTR(l_jurisdiction_code,1,2) ||
3069 SUBSTR(l_jurisdiction_code,4,3) ||
3070 SUBSTR(l_jurisdiction_code,8,4) )
3071 ).jurisdiction_code := l_jurisdiction_code;
3072
3073 jurisdiction_codes_tbl_stg( TO_NUMBER(SUBSTR(l_jurisdiction_code,1,2) ||
3074 SUBSTR(l_jurisdiction_code,4,3) ||
3075 SUBSTR(l_jurisdiction_code,8,4) )
3076 ).hours := l_sum_hours;
3077
3078 jurisdiction_codes_tbl_stg( TO_NUMBER(SUBSTR(l_jurisdiction_code,1,2) ||
3079 SUBSTR(l_jurisdiction_code,4,3) ||
3080 SUBSTR(l_jurisdiction_code,8,4) )
3081 ).jd_type := 'IT'; --Informational Time
3082
3083 jurisdiction_codes_tbl_stg( TO_NUMBER(SUBSTR(l_jurisdiction_code,1,2) ||
3084 SUBSTR(l_jurisdiction_code,4,3) ||
3085 SUBSTR(l_jurisdiction_code,8,4) )
3086 ).tg_hours := 0; --Initialize Tagged Hours
3087
3088 hr_utility.trace('EMJT : ===============================================');
3089 hr_utility.trace('EMJT : Information Hours entry tagged to JD Code -> ' ||l_jurisdiction_code);
3090 hr_utility.trace('EMJT : Information Hours logged -> ' ||to_char(l_sum_hours));
3091 hr_utility.trace('EMJT : ===============================================');
3092 -- Add information time logged to local variable to compute the
3093 -- the total information time hours logged for the assignment
3094 l_total_hours := l_total_hours + l_sum_hours;
3095 --
3096 -- If hours entered for jurisdiction is primary work jurisdiction
3097 -- set the flag to Yes
3098 IF l_work_jurisdiction_code = l_jurisdiction_code THEN
3099 l_primary_work_jd_flag := 'Y';
3100 l_primary_work_jd_index_value :=
3101 TO_NUMBER(SUBSTR(l_jurisdiction_code,1,2) ||
3102 SUBSTR(l_jurisdiction_code,4,3) ||
3103 SUBSTR(l_jurisdiction_code,8,4));
3104 END IF;
3105 -- Fetch the next jurisdiction and hours from cursor csr_it_element_entries
3106 --
3107 FETCH csr_it_element_entries INTO l_jurisdiction_code,
3108 l_sum_hours;
3109 --}
3110 END LOOP;
3111 CLOSE csr_it_element_entries;
3112 EXCEPTION
3113 WHEN OTHERS THEN
3114 hr_utility.trace('EMJT : ERROR Encountered while processing Information Hours EE');
3115 hr_utility.trace(substr(sqlerrm,1,45));
3116 hr_utility.trace(substr(sqlerrm,46,45));
3117 END;
3118 hr_utility.trace('EMJT : Information Hours Element Entries fetched and loaded ');
3119 hr_utility.trace('EMJT : Total no. of Jurisdiction loaded in to pl/sql table ->'||
3120 to_char(jurisdiction_codes_tbl_stg.COUNT));
3121 hr_utility.trace('EMJT : Total Information Hours logged -> ' ||to_char(l_total_hours));
3122
3123 -- Determine whether total hours match against scheduled hours.
3124 -- In case total hours fall less than scheduled hours, add the
3125 -- remaining hours to primary work jurisdiction.
3126 --
3127 IF l_total_hours < l_scheduled_work_hours THEN
3128 --{
3129 l_remaining_hours := l_scheduled_work_hours - l_total_hours;
3130 hr_utility.trace('EMJT : Total Hours entered '||to_char(l_total_hours)||
3131 ' less than Scheduled Hours '||
3132 to_char(l_scheduled_work_hours));
3133 hr_utility.trace('EMJT : Entered Hours short of Sheduled Hours ' ||
3134 to_char(l_remaining_hours));
3135 -- Check whether work jurisdiction is available in pl table.
3136 -- If yes, add l_remaining_hours to the hours logged against work
3137 -- jurisdiction Else assign l_remaining_hours to the work jurisdiction.
3138 IF jurisdiction_codes_tbl_stg.EXISTS(
3139 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3140 SUBSTR(l_work_jurisdiction_code,4,3) ||
3141 SUBSTR(l_work_jurisdiction_code,8,4) )
3142 ) THEN
3143 --{
3144 l_work_hours := jurisdiction_codes_tbl_stg(
3145 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3146 SUBSTR(l_work_jurisdiction_code,4,3) ||
3147 SUBSTR(l_work_jurisdiction_code,8,4) )
3148 ).hours;
3149 jurisdiction_codes_tbl_stg(
3150 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3151 SUBSTR(l_work_jurisdiction_code,4,3) ||
3152 SUBSTR(l_work_jurisdiction_code,8,4) )
3153 ).hours := l_work_hours + l_remaining_hours;
3154 l_primary_work_jd_flag := 'Y';
3155 --
3156 l_primary_work_jd_index_value :=
3157 TO_NUMBER(SUBSTR(l_jurisdiction_code,1,2) ||
3158 SUBSTR(l_jurisdiction_code,4,3) ||
3159 SUBSTR(l_jurisdiction_code,8,4));
3160
3161 hr_utility.trace('EMJT : l_work_hours ' || to_char(l_work_hours));
3162 --}
3163 ELSE
3164 --{
3165 -- No Informational Time logged against work location.
3166 -- Insert work jurisdiction into pl table with jd_type as WK
3167 jurisdiction_codes_tbl_stg(
3168 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3169 SUBSTR(l_work_jurisdiction_code,4,3) ||
3170 SUBSTR(l_work_jurisdiction_code,8,4) )
3171 ).jurisdiction_code := l_work_jurisdiction_code;
3172 /*Bug#6957929: Jurisdiction type must be 'RW' when work and resident
3173 jurisdictions are same.*/
3174 if l_res_jurisdiction_code = l_work_jurisdiction_code then
3175 jurisdiction_codes_tbl_stg(
3176 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3177 SUBSTR(l_work_jurisdiction_code,4,3) ||
3181 jurisdiction_codes_tbl_stg(
3178 SUBSTR(l_work_jurisdiction_code,8,4) )
3179 ).jd_type := 'RW'; --Work Location
3180 else
3182 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3183 SUBSTR(l_work_jurisdiction_code,4,3) ||
3184 SUBSTR(l_work_jurisdiction_code,8,4) )
3185 ).jd_type := 'WK'; --Work Location
3186 end if;
3187 /*
3188 jurisdiction_codes_tbl_stg(
3189 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3190 SUBSTR(l_work_jurisdiction_code,4,3) ||
3191 SUBSTR(l_work_jurisdiction_code,8,4) )
3192 ).jd_type := 'WK'; --Work Location
3193 */
3194 /*Bug#6957929: Changes end here*/
3195
3196 jurisdiction_codes_tbl_stg(
3197 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3198 SUBSTR(l_work_jurisdiction_code,4,3) ||
3199 SUBSTR(l_work_jurisdiction_code,8,4) )
3200 ).hours := l_remaining_hours;
3201
3202 jurisdiction_codes_tbl_stg(
3203 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3204 SUBSTR(l_work_jurisdiction_code,4,3) ||
3205 SUBSTR(l_work_jurisdiction_code,8,4) )
3206 ).tg_hours := 0; --Initialize Tagged Hours
3207 l_primary_work_jd_flag := 'Y';
3208 l_primary_work_jd_index_value :=
3209 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
3210 SUBSTR(l_work_jurisdiction_code,4,3) ||
3211 SUBSTR(l_work_jurisdiction_code,8,4));
3212 --}
3213 END IF; -- jurisdiction_codes_tbl_stg.EXISTS
3214
3215 hr_utility.trace('EMJT : Hours logged to primary Work Jurisdiction ->'||
3216 to_char(l_work_hours));
3217 hr_utility.trace('EMJT : Total no. of Jurisdiction loaded in to pl/sql table ->'||
3218 to_char(jurisdiction_codes_tbl_stg.COUNT));
3219 --}
3220 END IF; --l_total_hours < l_scheduled_work_hours
3221
3222
3223 hr_utility.trace('EMJT : Fetching Tagged and/or Positive Pay Hours Element Entries ');
3224 --
3225 -- Processing Tagged Entries for the assignment
3226 --
3227 BEGIN
3228 -- For tagged eearnings start and date date change fir fixing issue 4626170
3229 --
3230 /* OPEN csr_tagged_entries(l_start_date,
3231 l_end_date,
3232 p_assignment_id,
3233 p_date_earned);
3234 */
3235 OPEN csr_tagged_entries(l_max_start_date,
3236 l_max_end_date,
3237 p_assignment_id,
3238 p_date_earned);
3239 --{
3240 FETCH csr_tagged_entries INTO l_tg_jurisdiction_code,
3241 l_tg_sum_hours;
3242 LOOP
3243 --{
3244 EXIT WHEN csr_tagged_entries%NOTFOUND;
3245
3246 IF jurisdiction_codes_tbl_stg.EXISTS(
3247 TO_NUMBER(SUBSTR(l_tg_jurisdiction_code,1,2) ||
3248 SUBSTR(l_tg_jurisdiction_code,4,3) ||
3249 SUBSTR(l_tg_jurisdiction_code,8,4) )
3250 ) THEN
3251 --{
3252 l_hours_fetched := jurisdiction_codes_tbl_stg(
3253 TO_NUMBER(SUBSTR(l_tg_jurisdiction_code,1,2) ||
3254 SUBSTR(l_tg_jurisdiction_code,4,3) ||
3255 SUBSTR(l_tg_jurisdiction_code,8,4) )
3256 ).hours;
3257
3258 jurisdiction_codes_tbl_stg(
3259 TO_NUMBER(SUBSTR(l_tg_jurisdiction_code,1,2) ||
3260 SUBSTR(l_tg_jurisdiction_code,4,3) ||
3261 SUBSTR(l_tg_jurisdiction_code,8,4) )
3262 ).hours := l_hours_fetched + l_tg_sum_hours;
3263
3264 jurisdiction_codes_tbl_stg(
3265 TO_NUMBER(SUBSTR(l_tg_jurisdiction_code,1,2) ||
3266 SUBSTR(l_tg_jurisdiction_code,4,3) ||
3267 SUBSTR(l_tg_jurisdiction_code,8,4) )
3268 ).tg_hours := l_tg_sum_hours;
3269 --}
3270 ELSE
3271 --{
3272 jurisdiction_codes_tbl_stg(
3273 TO_NUMBER(SUBSTR(l_tg_jurisdiction_code,1,2) ||
3274 SUBSTR(l_tg_jurisdiction_code,4,3) ||
3275 SUBSTR(l_tg_jurisdiction_code,8,4) )
3276 ).jurisdiction_code := l_tg_jurisdiction_code;
3277
3278 jurisdiction_codes_tbl_stg(
3279 TO_NUMBER(SUBSTR(l_tg_jurisdiction_code,1,2) ||
3280 SUBSTR(l_tg_jurisdiction_code,4,3) ||
3281 SUBSTR(l_tg_jurisdiction_code,8,4) )
3282 ).hours := l_tg_sum_hours;
3283
3284 jurisdiction_codes_tbl_stg(
3285 TO_NUMBER(SUBSTR(l_tg_jurisdiction_code,1,2) ||
3286 SUBSTR(l_tg_jurisdiction_code,4,3) ||
3287 SUBSTR(l_tg_jurisdiction_code,8,4) )
3288 ).tg_hours := l_tg_sum_hours;
3289
3290 jurisdiction_codes_tbl_stg(
3291 TO_NUMBER(SUBSTR(l_tg_jurisdiction_code,1,2) ||
3292 SUBSTR(l_tg_jurisdiction_code,4,3) ||
3293 SUBSTR(l_tg_jurisdiction_code,8,4) )
3294 ).jd_type := 'TG'; --Informational Time
3295
3296 --}
3297 END IF;
3298
3299 hr_utility.trace('EMJT : ===============================================');
3300 hr_utility.trace('EMJT : Tagged Jurisdiction Code -> ' ||
3301 l_tg_jurisdiction_code);
3302 hr_utility.trace('EMJT : Tagged/Positive_Pay Hours -> ' ||
3303 to_char(l_tg_sum_hours));
3304 hr_utility.trace('EMJT : ===============================================');
3305
3306 FETCH csr_tagged_entries INTO l_tg_jurisdiction_code,
3307 l_tg_sum_hours;
3308 --}
3309 END LOOP;
3310 CLOSE csr_tagged_entries;
3311 EXCEPTION
3312 WHEN OTHERS THEN
3313 hr_utility.trace('EMJT : ERROR Encountered while processing Tagged EE');
3314 hr_utility.trace(substr(sqlerrm,1,45));
3315 hr_utility.trace(substr(sqlerrm,46,45));
3316 END;
3317 hr_utility.trace('EMJT : Tagged and/or Positive Pay Hours Element Entries Fetched and Loaded');
3318
3319 --Just to print the contents of jurisdiction_codes_tbl_stg
3320 l_counter := jurisdiction_codes_tbl_stg.FIRST;
3321 l_last_jd_index_value := jurisdiction_codes_tbl_stg.LAST;
3322 WHILE l_counter IS NOT NULL LOOP
3323 hr_utility.trace('EMJT : =========================================================');
3324 hr_utility.trace('EMJT : jurisdiction_codes_tbl_stg('||to_char(l_counter)||').hours ->'
3325 || jurisdiction_codes_tbl_stg(l_counter).hours);
3326 hr_utility.trace('EMJT : jurisdiction_codes_tbl_stg('||to_char(l_counter)||').tg_hours ->'
3327 || jurisdiction_codes_tbl_stg(l_counter).tg_hours);
3328 hr_utility.trace('EMJT : jurisdiction_codes_tbl_stg('||to_char(l_counter)||').jurisdiction_code->'
3329 || jurisdiction_codes_tbl_stg(l_counter).jurisdiction_code);
3330 hr_utility.trace('EMJT : jurisdiction_codes_tbl_stg('||to_char(l_counter)||').percentage ->'
3331 || jurisdiction_codes_tbl_stg(l_counter).percentage);
3332 hr_utility.trace('EMJT : jurisdiction_codes_tbl_stg('||to_char(l_counter)||').jd_type ->'
3333 || jurisdiction_codes_tbl_stg(l_counter).jd_type);
3334 hr_utility.trace('EMJT : =========================================================');
3335 l_counter := jurisdiction_codes_tbl_stg.NEXT(l_counter);
3336 END LOOP; --WHILE l_counter
3337
3338 --============================================================================================
3339 --This part of the code is used to populate state, county and city level pl tables
3340
3341 -- State pl tables -> jd_codes_tbl_state_STG (STAGING) and jd_codes_tbl_state (MAIN)
3342 -- County pl tables -> jd_codes_tbl_county_STG (STAGING) and jd_codes_tbl_county (MAIN)
3343 -- City pl tables -> jd_codes_tbl_city_STG (STAGING) and jd_codes_tbl_city (MAIN)
3344
3345 --
3346 -- This is to upload the primary work jurisdiction entered hours into staging table
3347 -- created for threshold purpose
3348 --
3349
3350 --Initialize the state and county pl tables.
3351 --Purge all the staging pl/sql table used for Threshold
3352 hr_utility.trace('EMJT: Purge All pl/sql table used for Threshold ');
3353 jd_codes_tbl_state_stg.delete;
3354 jd_codes_tbl_state.delete;
3355 jd_codes_tbl_county_stg.delete;
3356 jd_codes_tbl_county.delete;
3357 jd_codes_tbl_city_stg.delete;
3358 hr_utility.trace('EMJT: PL/SQL tables are purged');
3359 -- Loop thru the staging table to see whether any of the jurisdiction under threshold for taxing
3360 -- Get the hours for each jurisdiction
3361
3362 --Start processing state data, populating the jd_codes_tbl_state.
3363
3364 l_counter := jurisdiction_codes_tbl_stg.FIRST;
3365 l_last_jd_index_value := jurisdiction_codes_tbl_stg.LAST;
3366 hr_utility.trace('EMJT: First JD Code '||to_char(l_counter));
3367 hr_utility.trace('EMJT: Last JD Code '||to_char(l_last_jd_index_value));
3368 WHILE l_counter IS NOT NULL LOOP
3369 BEGIN
3370 --{
3371 l_jurisdiction := jurisdiction_codes_tbl_stg(l_counter).jurisdiction_code ;
3372 l_jd_hours := jurisdiction_codes_tbl_stg(l_counter).hours ;
3373 hr_utility.trace('EMJT: l_jurisdiction '|| l_jurisdiction);
3374 hr_utility.trace('EMJT: l_counter '|| to_char(l_counter));
3375 --Processing For State
3376 IF jd_codes_tbl_state_stg.EXISTS(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||'0000000')
3377 ) THEN
3378 --{
3379 jd_codes_tbl_state_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||'0000000')).hours
3380 := jd_codes_tbl_state_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||'0000000')).hours
3381 + l_jd_hours;
3382 hr_utility.trace('EMJT: State JD Code Exists in State Stg PL table');
3383 --}
3384 ELSE
3385 --{
3386 hr_utility.trace('EMJT: State JD Code doesnot Exists in State Stage pl/sql table');
3387 jd_codes_tbl_state_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||'0000000')).jurisdiction_code
3388 := SUBSTR(l_jurisdiction,1,2)||'-000-0000';
3389 jd_codes_tbl_state_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||'0000000')).hours
3390 := l_jd_hours;
3391
3392 IF l_jd_hours > 0 THEN
3393 jd_codes_tbl_state_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||'0000000')).calc_percent
3394 := 'Y';
3395 ELSE
3396 jd_codes_tbl_state_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||'0000000')).calc_percent
3397 := 'N';
3398 END IF;
3399 hr_utility.trace('EMJT: State JD Code '|| SUBSTR(l_jurisdiction,1,2)||'-000-0000'
3400 ||' loaded to pl/sql table');
3401 --}
3402 END IF;
3403 --End of processing for state
3404
3405 hr_utility.trace('EMJT : =======================================================================');
3406 hr_utility.trace('EMJT : jd_codes_tbl_state_stg.count ->' || jd_codes_tbl_state_stg.COUNT);
3407 hr_utility.trace('EMJT : l_counter ->'||l_counter);
3408
3409 /* hr_utility.trace('EMJT : jd_codes_tbl_state_stg.jurisdiction_code->' || jd_codes_tbl_state_stg(l_counter).jurisdiction_code);
3410 hr_utility.trace('EMJT : jd_codes_tbl_state_stg('||to_char(l_counter)||').hours ->'
3411 || jd_codes_tbl_state_stg(l_counter).hours);
3412 hr_utility.trace('EMJT : jd_codes_tbl_state_stg('||to_char(l_counter)||').percentage ->'
3413 || jd_codes_tbl_state_stg(l_counter).percentage);
3414 hr_utility.trace('EMJT : jd_codes_tbl_state_stg('||to_char(l_counter)||').calc_percent ->'
3415 || jd_codes_tbl_state_stg(l_counter).calc_percent);*/
3416 hr_utility.trace('EMJT : ========================================================================');
3417
3418 --Processing For County
3419 IF jd_codes_tbl_county_stg.EXISTS(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
3420 SUBSTR(l_jurisdiction,4,3)||
3421 '0000')
3422 ) THEN
3423 --{
3424 jd_codes_tbl_county_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
3425 SUBSTR(l_jurisdiction,4,3)||'0000')).hours
3426 := jd_codes_tbl_county_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
3427 SUBSTR(l_jurisdiction,4,3)||'0000')).hours
3428 + l_jd_hours;
3429 hr_utility.trace('EMJT: County JD Code Exists in State Stage pl/sql table');
3430 --}
3431 ELSE
3432 --{
3433 hr_utility.trace('EMJT: County JD Code doesnot Exists in County Stg pl table');
3434 hr_utility.trace('EMJT: County JD Code '||SUBSTR(l_jurisdiction,1,7)||'0000');
3435 jd_codes_tbl_county_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
3436 SUBSTR(l_jurisdiction,4,3)||
3437 '0000')).jurisdiction_code
3438 := SUBSTR(l_jurisdiction,1,7)||'0000';
3439
3440 jd_codes_tbl_county_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
3441 SUBSTR(l_jurisdiction,4,3)||
3442 '0000')
3443 ).hours := l_jd_hours;
3444
3445 IF l_jd_hours > 0 THEN
3446 jd_codes_tbl_county_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
3447 SUBSTR(l_jurisdiction,4,3)||
3448 '0000')
3449 ).calc_percent := 'Y';
3450 ELSE
3451 jd_codes_tbl_county_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
3452 SUBSTR(l_jurisdiction,4,3)||
3453 '0000')
3454 ).calc_percent := 'N';
3455 END IF;
3456 hr_utility.trace('EMJT: County JD Code '|| SUBSTR(l_jurisdiction,1,7)||'0000'
3457 ||' loaded to pl/sql table');
3458
3459 --}
3460 END IF;
3461 --End of processing for county.
3462
3463 l_counter := jurisdiction_codes_tbl_stg.NEXT(l_counter);
3464 --}
3465 EXCEPTION
3466 WHEN OTHERS THEN
3467 hr_utility.trace('EMJT: ERROR in populating State/County Stg Pl Table');
3468 hr_utility.trace(substr(sqlerrm,1,45));
3469 hr_utility.trace(substr(sqlerrm,46,45));
3470 RAISE;
3471 END;
3472 END LOOP;
3473 hr_utility.trace('EMJT: Staging table jd_codes_tbl_state_stg populated sucessfully');
3474 hr_utility.trace('EMJT: Staging table jd_codes_tbl_county_stg populated sucessfully');
3475 l_counter := NULL;
3476 --
3477 --This part of the code populates the MAIN STATE PL TABLE that will be used in get_jd_percent
3478 --FROM jd_codes_tbl_state_stg INTO jd_codes_tbl_state
3479 --
3480 hr_utility.trace('EMJT STATE:==============================================================');
3481 hr_utility.trace('EMJT STATE: Main State Processing');
3482 l_counter := jd_codes_tbl_state_stg.FIRST;
3483 l_last_jd_index_value := jd_codes_tbl_state_stg.LAST;
3484 hr_utility.trace('EMJT STATE: State First JD Code '||to_char(l_counter));
3485 hr_utility.trace('EMJT STATE: State Last JD Code '||to_char(l_last_jd_index_value));
3486
3487 WHILE l_counter IS NOT NULL LOOP
3488 --{
3489 l_jurisdiction := jd_codes_tbl_state_stg(l_counter).jurisdiction_code ;
3490 l_jd_hours := jd_codes_tbl_state_stg(l_counter).hours ;
3491 l_calc_percent := jd_codes_tbl_state_stg(l_counter).calc_percent ;
3492 hr_utility.trace('EMJT STATE: State l_counter ->'|| l_counter);
3493 hr_utility.trace('EMJT STATE: State Jurisdiction Code =>'|| l_jurisdiction);
3494 hr_utility.trace('EMJT STATE: State Hours =>'|| to_char(l_jd_hours));
3495 hr_utility.trace('EMJT STATE: State Calculate Percentage =>'|| l_calc_percent);
3496 -- When Jurisdiction state is same as primary work state no thresholding
3497 IF l_calc_percent = 'N' THEN
3498 --{
3499 hr_utility.trace('EMJT STATE: Thresholding Not required load in jd_codes_tbl_state');
3500 jd_codes_tbl_state(l_counter).jurisdiction_code := l_jurisdiction;
3501 jd_codes_tbl_state(l_counter).hours := l_jd_hours;
3502 jd_codes_tbl_state(l_counter).calc_percent := l_calc_percent;
3503 --}
3504 ELSE
3505 --{
3506 IF SUBSTR(l_jurisdiction,1,2) = SUBSTR(l_work_jurisdiction_code,1,2) THEN
3507 --{
3508 hr_utility.trace('EMJT STATE: Work state is same as Primary Work State');
3509 hr_utility.trace('EMJT STATE: Threshold check not required load in jd_codes_tbl_state');
3510 IF jd_codes_tbl_state.EXISTS(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||'0000000'))
3511 THEN
3512 --{
3513 hr_utility.trace('EMJT STATE: HERE in if');
3514 hr_utility.trace('EMJT STATE: TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||''0000000'')-> '||TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||'0000000'));
3515 hr_utility.trace('EMJT STATE: l_counter '|| to_char(l_counter));
3516 jd_codes_tbl_state(l_counter).hours
3517 := NVL(jd_codes_tbl_state(l_counter).hours,0) + l_jd_hours;
3518 --}
3519 ELSE
3520 --{
3521 hr_utility.trace('EMJT STATE: IN ELSE');
3522 jd_codes_tbl_state(l_counter).jurisdiction_code := l_jurisdiction;
3523 jd_codes_tbl_state(l_counter).hours := l_jd_hours;
3524 jd_codes_tbl_state(l_counter).calc_percent := l_calc_percent;
3525 hr_utility.trace('EMJT STATE: Primary Work State JD loaded into jd_codes_tbl_state');
3526 --}
3527 END IF;
3528 --}
3529 ELSE --SUBSTR(l_jurisdiction,1,2) = SUBSTR(l_work_jurisdiction_code,1,2)
3530 --{
3531 hr_utility.trace('EMJT STATE: Work state is NOT same as Primar Work State');
3532 --Fetch State level Threshold
3533 hr_utility.trace('EMJT STATE: Fetching Threshold Hours configured for State ');
3534 hr_utility.trace('EMJT STATE: Processing State JD Code '||l_jurisdiction);
3535 l_threshold_hours_state := get_jd_level_threshold(p_tax_unit_id
3536 ,l_jurisdiction
3537 ,'STATE');
3538 hr_utility.trace('EMJT STATE: Threshold_Hours_State '|| to_char(l_threshold_hours_state));
3539 IF l_threshold_hours_state > 0 THEN
3540 --{
3541 -- Fetch the state level tax balance accrued for the person
3542 -- If Tax balance found then tax the state as per hours logged for the state
3543 -- otherwise hours will be accounted to primary work state
3544 -- for SIT Witheld and/or SIT Supp Witheld
3545 hr_utility.trace('EMJT STATE: Threshold_Hours_State > 0 so Fetch SIT Witheld for Assignment');
3546
3547
3548
3549 /* Bug 6869097:The following code checks whether SIT is withheld already for the
3550 assignment and if it finds SIT is withheld already it assumes that the
3551 assignemnt has crossed the threshold limit already and inserts the
3552 current record. But there are some situations where tax is withheld
3553 for an assignment even before the threshold limit is reached. So commented
3554 the following code so that it can go on with threshold checking irrespective
3555 of SIT Withheld balance.
3556 */
3557
3558 /* l_sit_withheld :=
3559 hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
3560 ,p_assignment_action_id => p_assignment_action_id
3561 ,p_jurisdiction_code => l_jurisdiction
3562 ,p_tax_unit_id => p_tax_unit_id
3563 ,p_jurisdiction_level => 'STATE'
3564 ,p_effective_date => p_date_paid
3565 ,p_assignment_id => p_assignment_id);
3566 hr_utility.trace('EMJT STATE: SIT Withheld for Assignment -> '|| to_char(l_sit_withheld));
3567 IF l_sit_withheld > 0 THEN
3568 --{
3569 hr_utility.trace('EMJT STATE: As Tax Withheld previously in State NO THRESHOLD CHECK');
3570 jd_codes_tbl_state(l_counter).jurisdiction_code := l_jurisdiction;
3571 jd_codes_tbl_state(l_counter).hours := l_jd_hours;
3572 jd_codes_tbl_state(l_counter).calc_percent := l_calc_percent;
3573 hr_utility.trace('EMJT STATE: State JD '||l_jurisdiction||' loaded in jd_codes_tbl_state ');
3574 --}
3575 ELSE --l_sit_withheld > 0 */
3576
3577 /*Bug#6869097: changes end here*/
3578 --{
3579 -- Fetch Information Hours logged for the person depending on the payroll effective date
3580 -- call to get_th_assignment for the STATE
3581 hr_utility.trace('EMJT STATE: Fetch Information Hours Logged for Assignment ');
3582 l_state_ih_logged
3583 := hr_us_ff_udf1.get_person_it_hours(p_person_id => l_person_id
3584 ,p_assignment_id => p_assignment_id
3585 ,p_jurisdiction_code => l_jurisdiction
3586 ,p_jd_level => 2
3587 ,p_threshold_basis => l_threshold_basis
3588 ,p_effective_date => l_max_end_date -- p_date_paid
3589 ,p_end_date => l_end_date);
3590 hr_utility.trace('EMJT STATE: Information Hours Logged for Assignment for State => '
3591 || to_char(l_state_ih_logged));
3592 IF l_state_ih_logged >= l_threshold_hours_state THEN
3593 --{
3594 hr_utility.trace('EMJT STATE: Information Hours Logged > Threshold_Hours_State ');
3595 l_ih_excluding_pay_period := l_state_ih_logged - l_jd_hours;
3596 hr_utility.trace('EMJT STATE: Information Hours Processed Prior This Pay Period -> '
3597 || to_char(l_ih_excluding_pay_period));
3598 -- if information hours processed till last payroll run is greater than the
3599 -- threshold limit configured at the State level then hours logged for the state
3600 -- would be accounted for that state
3601 --
3602 IF l_ih_excluding_pay_period >= l_threshold_hours_state THEN
3603 --{
3604 hr_utility.trace('EMJT STATE: Hours till last Pay Period > Threshold_Hours_State');
3605 jd_codes_tbl_state(l_counter).jurisdiction_code := l_jurisdiction;
3606 jd_codes_tbl_state(l_counter).hours := l_jd_hours;
3607 jd_codes_tbl_state(l_counter).calc_percent := l_calc_percent;
3608 hr_utility.trace('EMJT STATE: State JD loaded into jd_codes_tbl_state '|| l_jurisdiction);
3609 hr_utility.trace('EMJT STATE: Hours loaded into jd_codes_tbl_state '|| to_char(l_jd_hours));
3610 --}
3611 ELSE --l_ih_excluding_pay_period >= l_threshold_hours_state
3612 --{
3613 -- if information hours processed till last payroll run is less than the
3614 -- threshold limit configured at the State level
3615 -- Calculate information hours that is exceeds threshold limit
3616 --
3617 l_ih_above_threshold := l_state_ih_logged - l_threshold_hours_state;
3618 hr_utility.trace('EMJT STATE: Information Hours Above Threshold -> '
3619 || to_char(l_ih_above_threshold));
3620 --
3621 -- Calculate information hours that would be accounted to primary work location
3622 -- due to threshold
3623 hr_utility.trace('EMJT STATE: l_jurisdiction -> '|| l_jurisdiction);
3624 hr_utility.trace('EMJT STATE: l_counter -> '|| to_char(l_counter));
3625
3626 l_ih_for_primary_wk :=
3627 jd_codes_tbl_state_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
3628 '0000000')).hours - l_ih_above_threshold;
3629 hr_utility.trace('EMJT STATE: Hours Accounted for Primary Work Location '
3630 ||to_char(l_ih_for_primary_wk));
3631
3632 -- if information hours logged for the state is more than threshold
3633 -- configured for the state, only exceeded hours would be accounted for that
3634 -- state
3635 IF l_ih_above_threshold > 0 THEN
3636 IF jd_codes_tbl_state.EXISTS(
3637 TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||'0000000'))
3638 THEN
3639 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_jurisdiction_code,1,2)
3640 ||'0000000')).hours :=
3641 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3642 ||'0000000')).hours + l_ih_above_threshold ;
3643 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)
3644 ||'0000000')).calc_percent := l_calc_percent;
3645 ELSE
3646 jd_codes_tbl_state(l_counter).jurisdiction_code := l_jurisdiction;
3647 jd_codes_tbl_state(l_counter).hours := l_ih_above_threshold ;
3648 jd_codes_tbl_state(l_counter).calc_percent := l_calc_percent;
3649 END IF;
3650 END IF;
3651 -- When Total information hours logged for the person is above threshold
3652 -- but there are some information hours need to accounted to primary
3653 -- work location due to threshold limit
3654 -- This is determine if part of information hours entered for the processing pay
3655 -- period need to be accounted to primary work location due to
3656 --
3657 IF l_ih_for_primary_wk > 0 THEN
3658 --{
3659 hr_utility.trace(' EMJT STATE: l_ih_for_primary_wk -> ' || to_char(l_ih_for_primary_wk));
3660 IF jd_codes_tbl_state.EXISTS(
3661 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||'0000000'))
3662 THEN
3663 --{
3664 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3665 ||'0000000')).hours :=
3666 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3667 ||'0000000')).hours + l_ih_for_primary_wk ;
3668 hr_utility.trace('EMJT STATE: Hours accounted for Primary WK JD State in IF '
3669 || to_char(l_ih_for_primary_wk));
3670 --}
3671 ELSE
3672 --{
3673 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3674 ||'0000000')).jurisdiction_code
3675 := SUBSTR(l_work_jurisdiction_code,1,2)||'-000-0000';
3676 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3677 ||'0000000')).hours := l_ih_for_primary_wk;
3678 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3679 ||'0000000')).calc_percent := l_calc_percent;
3680 hr_utility.trace('EMJT STATE: Primary WK JD State loaded into pl table jd_codes_tbl_state');
3681 hr_utility.trace('EMJT STATE: Hours accounted for Primary WK JD State in ELSE '
3682 ||to_char(l_ih_for_primary_wk));
3683 --}
3684 END IF;
3685 --}
3686 END IF; --l_ih_for_primary_wk > 0
3687 --}
3688 END IF; --l_ih_excluding_pay_period >= l_threshold_hours_state
3689 --}
3690 ELSE
3691 --{
3692 -- If Information Hours Logged for the assignment is less than Threshold Hours
3693 -- configured for the state, information hours would be accounted to primary work
3694 -- State
3695 IF jd_codes_tbl_state.EXISTS(
3696 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||'0000000'))
3697 THEN
3698 --{
3699 hr_utility.trace('EMJT STATE: l_jd_hours -> '|| to_char(l_jd_hours) );
3700 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3701 ||'0000000')).hours :=
3702 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3703 ||'0000000')).hours + l_jd_hours ;
3704 hr_utility.trace('EMJT STATE: Hours accounted for Primary WK JD State in IF l_jd_hours'
3705 || to_char(l_jd_hours));
3706 --}
3707 ELSE
3708 --{
3709 hr_utility.trace('EMJT STATE: l_work_jurisdiction_code -> '|| l_work_jurisdiction_code);
3710 hr_utility.trace('EMJT STATE: l_counter -> '|| to_char(l_counter));
3711 hr_utility.trace('EMJT STATE: l_calc_percent -> '|| l_calc_percent);
3712 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3713 ||'0000000')).jurisdiction_code
3714 := SUBSTR(l_work_jurisdiction_code,1,2)||'-000-0000';
3715 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3716 ||'0000000')).hours := l_jd_hours;
3717 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)
3718 ||'0000000')).calc_percent := l_calc_percent;
3719 hr_utility.trace('EMJT STATE: Primary WK JD State loaded into pl table jd_codes_tbl_state');
3720 hr_utility.trace('EMJT STATE: Hours accounted for Primary WK JD State in ELSE l_jd_hours '
3721 ||to_char(l_jd_hours));
3722 --}
3723 END IF;
3724 --}
3725 END IF; --l_state_ih_logged > l_threshold_hours_state
3726 --}
3727 -- END IF;--l_sit_withheld > 0 /*6869097*/
3728 --}
3729 ELSE
3730 -- If Threshold Hours not logged for a State load Jurisdiction into jd_codes_tbl_state
3731 --
3732 --{
3733 jd_codes_tbl_state(l_counter).jurisdiction_code := l_jurisdiction;
3734 jd_codes_tbl_state(l_counter).hours := l_jd_hours;
3735 jd_codes_tbl_state(l_counter).calc_percent := l_calc_percent;
3736 hr_utility.trace('EMJT STATE: Work JD State loaded into jd_codes_tbl_state =>'
3737 ||l_jurisdiction);
3738 hr_utility.trace('EMJT STATE: Hours accounted for Primary WK JD State '
3739 ||to_char(l_ih_for_primary_wk));
3740 --}
3741 END IF;--l_threshold_hours_state > 0
3742 --}
3743 END IF; --SUBSTR(l_jurisdiction,1,2) = SUBSTR(l_work_jurisdiction_code,1,2)
3744 --}
3745 END IF; --l_calc_percent = 'N'
3746
3747 /* hr_utility.trace('EMJT:Testing =================================================');
3748 l_in_counter := NULL;
3749 l_in_counter := jd_codes_tbl_state.FIRST;
3750 WHILE l_in_counter IS NOT NULL LOOP
3751 hr_utility.trace('EMJT: jd_codes_tbl_state.jurisdiction_code -> '||jd_codes_tbl_state(l_in_counter).jurisdiction_code);
3752 hr_utility.trace('EMJT: jd_codes_tbl_state.hours -> '||jd_codes_tbl_state(l_in_counter).hours);
3753 l_in_counter := jd_codes_tbl_state.NEXT(l_in_counter);
3754 END LOOP;
3755 hr_utility.trace('EMJT:=================================================');*/
3756
3757 hr_utility.trace('EMJT STATE: Setting the Index counter to fetch next JD State ');
3758 l_counter := jd_codes_tbl_state_stg.NEXT(l_counter);
3759 hr_utility.trace('EMJT STATE: Next Index Counter Value '||to_char(l_counter));
3760 --}
3761 END LOOP;
3762 hr_utility.trace('EMJT STATE: Final jd_codes_tbl_state.count ->' || jd_codes_tbl_state.COUNT);
3763 hr_utility.trace('EMJT STATE: PL Table jd_codes_tbl_state_stg processed Successfully');
3764 --Done with populating the jd_codes_tbl_state.
3765
3766
3767 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3768 l_counter := NULL;
3769 l_counter := jd_codes_tbl_state.FIRST;
3770 l_last_jd_index_value := jd_codes_tbl_state.LAST;
3771 hr_utility.trace('EMJT STATE: jd_codes_tbl_state.FIRST->' || jd_codes_tbl_state.FIRST);
3772 hr_utility.trace('EMJT STATE: jd_codes_tbl_state.LAST->' || jd_codes_tbl_state.LAST);
3773
3774 WHILE l_counter IS NOT NULL LOOP
3775 hr_utility.trace('EMJT STATE: =========================================================');
3776 hr_utility.trace('EMJT STATE: jd_codes_tbl_state('||to_char(l_counter)||').hours ->'
3777 || jd_codes_tbl_state(l_counter).hours);
3778 hr_utility.trace('EMJT STATE: jd_codes_tbl_state('||to_char(l_counter)||').jurisdiction_code->'
3779 || jd_codes_tbl_state(l_counter).jurisdiction_code);
3780 hr_utility.trace('EMJT STATE: jd_codes_tbl_state('||to_char(l_counter)||').percentage ->'
3781 || jd_codes_tbl_state(l_counter).percentage);
3782 hr_utility.trace('EMJT STATE: =========================================================');
3783 l_total_state_hours := l_total_state_hours + NVL(jd_codes_tbl_state(l_counter).hours,0);
3784 l_counter := jd_codes_tbl_state.NEXT(l_counter);
3785 END LOOP;
3786
3787 hr_utility.trace('EMJT STATE: Final l_total_state_hours ->' || to_char(l_total_state_hours) );
3788 hr_utility.trace('EMJT STATE: Final l_scheduled_work_hours ->' || to_char(l_scheduled_work_hours) );
3789
3790 IF l_total_state_hours <= l_scheduled_work_hours THEN
3791 l_denominator := l_scheduled_work_hours;
3792 ELSIF l_total_state_hours > l_scheduled_work_hours THEN
3793 l_denominator := l_total_state_hours;
3794 END IF;
3795
3796 hr_utility.trace('EMJT STATE: Final l_denominator ->' || to_char(l_denominator) );
3797
3798 l_counter := NULL;
3799 l_counter := jd_codes_tbl_state.FIRST;
3800 l_last_jd_index_value := jd_codes_tbl_state.LAST;
3801 WHILE l_counter IS NOT NULL LOOP
3802 l_jd_hours := jd_codes_tbl_state(l_counter).hours ;
3803 jd_codes_tbl_state(l_counter).percentage :=
3804 ROUND((l_jd_hours/l_denominator) * 100);
3805 /* hr_utility.trace('EMJT: =========================================================');
3806 hr_utility.trace('EMJT: jd_codes_tbl_state('||to_char(l_counter)||').hours ->'
3807 || jd_codes_tbl_state(l_counter).hours);
3808 hr_utility.trace('EMJT: jd_codes_tbl_state('||to_char(l_counter)||').jurisdiction_code->'
3809 || jd_codes_tbl_state(l_counter).jurisdiction_code);
3810 hr_utility.trace('EMJT: jd_codes_tbl_state('||to_char(l_counter)||').percentage ->'
3811 || jd_codes_tbl_state(l_counter).percentage);
3812 hr_utility.trace('EMJT: =========================================================');*/
3813 l_total_state_percent := l_total_state_percent
3814 + jd_codes_tbl_state(l_counter).percentage;
3815 l_counter := jd_codes_tbl_state.NEXT(l_counter);
3816 END LOOP; --WHILE l_counter
3817
3818 hr_utility.trace('EMJT STATE: (TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||''0000000'')) '||
3819 TO_CHAR(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||'0000000')));
3820 IF l_total_state_percent > 100 THEN
3821 --{
3822 l_extra_percent := l_total_state_percent - 100;
3823 IF l_primary_work_jd_flag = 'Y' THEN
3824 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||'0000000')).percentage
3825 := jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||'0000000')).percentage
3826 - l_extra_percent;
3827 ELSE
3828 jd_codes_tbl_state(l_last_jd_index_value).percentage
3829 := jd_codes_tbl_state(l_last_jd_index_value).percentage
3830 - l_extra_percent;
3831 END IF;
3832 --}
3833 ELSIF l_total_state_percent < 100 THEN
3834 --{
3835 l_extra_percent := 100 - l_total_state_percent;
3836 IF l_primary_work_jd_flag = 'Y' THEN
3837 jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||'0000000')).percentage
3838 := jd_codes_tbl_state(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||'0000000')).percentage
3839 + l_extra_percent;
3840 ELSE
3841 jd_codes_tbl_state(l_last_jd_index_value).percentage
3842 := jd_codes_tbl_state(l_last_jd_index_value).percentage
3843 + l_extra_percent;
3844 END IF;
3845 --}
3846 END IF; --l_total_state_percent > 100
3847
3848
3849 l_counter := NULL;
3850 l_counter := jd_codes_tbl_state.FIRST;
3851 -- l_last_jd_index_value := jd_codes_tbl_state.LAST;
3852 hr_utility.trace('EMJT STATE: jd_codes_tbl_state.FIRST->' || jd_codes_tbl_state.FIRST);
3853 hr_utility.trace('EMJT STATE: jd_codes_tbl_state.LAST->' || jd_codes_tbl_state.LAST);
3854 WHILE l_counter IS NOT NULL LOOP
3855 hr_utility.trace('EMJT STATE: Final State Table');
3856 hr_utility.trace('EMJT STATE: =========================================================');
3857 hr_utility.trace('EMJT STATE: jd_codes_tbl_state('||to_char(l_counter)||').hours ->'
3858 || jd_codes_tbl_state(l_counter).hours);
3859 hr_utility.trace('EMJT STATE: jd_codes_tbl_state('||to_char(l_counter)||').jurisdiction_code->'
3860 || jd_codes_tbl_state(l_counter).jurisdiction_code);
3861 hr_utility.trace('EMJT STATE: jd_codes_tbl_state('||to_char(l_counter)||').percentage ->'
3862 || jd_codes_tbl_state(l_counter).percentage);
3863 hr_utility.trace('EMJT STATE: =========================================================');
3864 -- l_total_state_hours := l_total_state_hours + jd_codes_tbl_state(l_counter).hours;
3865 l_counter := jd_codes_tbl_state.NEXT(l_counter);
3866 END LOOP;
3867
3868 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3869 hr_utility.trace('EMJT COUNTY:==============================================================');
3870 hr_utility.trace('EMJT COUNTY: Main COUNTY Processing');
3871
3872 --Starting processing the county, populating jd_codes_tbl_county.
3873 --
3874 --This part of the code populates the MAIN COUNTY PL TABLE from the staging county pl table.
3875 --INTO jd_codes_tbl_county
3876 --FROM jd_codes_tbl_county_stg
3877 --
3878 l_counter := NULL;
3879 l_counter := jd_codes_tbl_county_stg.FIRST;
3880 l_last_jd_index_value := jd_codes_tbl_county_stg.LAST;
3881 hr_utility.trace('EMJT COUNTY: l_counter First JD Code -> '|| to_char(l_counter));
3882 hr_utility.trace('EMJT COUNTY: Last JD Code ->'|| to_char(l_last_jd_index_value));
3883 WHILE l_counter IS NOT NULL LOOP
3884 --{
3885 l_jurisdiction := jd_codes_tbl_county_stg(l_counter).jurisdiction_code ;
3886 l_jd_hours := jd_codes_tbl_county_stg(l_counter).hours ;
3887 l_calc_percent := jd_codes_tbl_county_stg(l_counter).calc_percent ;
3888 hr_utility.trace('EMJT COUNTY: Jurisdiction Code =>'|| l_jurisdiction);
3889 hr_utility.trace('EMJT COUNTY: l_jd_hours =>'|| to_char(l_jd_hours));
3890 hr_utility.trace('EMJT COUNTY: Calculate Percenrage =>'|| l_calc_percent);
3891
3892 -- When Jurisdiction county is same as primary work county no thresholding
3893 IF l_calc_percent = 'N' THEN
3894 --{
3895 hr_utility.trace('EMJT COUNTY: Thresholding Not required load in jd_codes_tbl_county');
3896 jd_codes_tbl_county(l_counter).jurisdiction_code := l_jurisdiction;
3897 jd_codes_tbl_county(l_counter).hours := l_jd_hours;
3898 jd_codes_tbl_county(l_counter).calc_percent := l_calc_percent;
3899 --}
3900 ELSE
3901 --{
3902 IF SUBSTR(l_jurisdiction,1,2)||SUBSTR(l_jurisdiction,4,3) =
3903 SUBSTR(l_work_jurisdiction_code,1,2)||SUBSTR(l_work_jurisdiction_code,4,3) THEN
3904 --{
3905 hr_utility.trace('EMJT COUNTY: Work County is same as Primar Work County');
3906 hr_utility.trace('EMJT COUNTY: Threshold check not required to load in jd_codes_tbl_county');
3907 IF jd_codes_tbl_county.EXISTS(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
3908 SUBSTR(l_jurisdiction,4,3) ||'0000'))
3909 THEN
3910 --{
3911 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
3912 SUBSTR(l_jurisdiction,4,3) ||
3913 '0000')).hours
3914 := jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
3915 SUBSTR(l_jurisdiction,4,3) ||
3916 '0000')).hours + l_jd_hours;
3917 --}
3918 ELSE
3919 --{
3920 jd_codes_tbl_county(l_counter).jurisdiction_code := l_jurisdiction;
3921 jd_codes_tbl_county(l_counter).hours := l_jd_hours;
3922 jd_codes_tbl_county(l_counter).calc_percent := l_calc_percent;
3923 hr_utility.trace('EMJT COUNTY: Primary Work State JD loaded into jd_codes_tbl_state');
3924 --}
3925 END IF;
3926 --}
3927 ELSE --SUBSTR(l_jurisdiction,1,2) = SUBSTR(l_work_jurisdiction_code,1,2)
3928 --{
3929 --Fetch County level threshold
3930 hr_utility.trace('EMJT COUNTY: Work County is NOT same as Primary Work County');
3931 hr_utility.trace('EMJT COUNTY: Fetching Threshold Hours configured for County ');
3932 hr_utility.trace('EMJT COUNTY: Processing County JD Code '|| l_jurisdiction);
3933
3934 l_threshold_hours_county := get_jd_level_threshold(p_tax_unit_id
3935 ,l_jurisdiction
3936 ,'COUNTY');
3937 hr_utility.trace('EMJT COUNTY: Threshold_Hours_County '|| to_char(l_threshold_hours_county));
3938 IF l_threshold_hours_county > 0 THEN
3939 --{
3940 -- Fetch the county level tax balance accrued for the person
3941 -- If Tax balance found then tax the county as per hours logged for the county
3942 -- otherwise hours will be accounted to primary work county
3943 hr_utility.trace('EMJT COUNTY: Threshold_Hours_County > 0 so Fetch County Witheld for Assignment');
3944
3945 /* Bug#6869097:The following code checks whether county tax is withheld already
3946 for the assignment and if it finds SIT is withheld already it assumes that
3947 the assignemnt has crossed the threshold limit already and inserts the
3948 current record. Commented the following code so that it can go on with
3952 /* l_county_withheld :=
3949 threshold checking irrespective of county tax Withheld balance.
3950 */
3951
3953 hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
3954 ,p_assignment_action_id=> p_assignment_action_id
3955 ,p_jurisdiction_code => l_jurisdiction
3956 ,p_tax_unit_id => p_tax_unit_id
3957 ,p_jurisdiction_level => 'COUNTY'
3958 ,p_effective_date => p_date_paid
3959 ,p_assignment_id => p_assignment_id);
3960 hr_utility.trace('EMJT COUNTY: County Withheld for Assignment '|| to_char(l_county_withheld));
3961 hr_utility.trace('EMJT COUNTY: jd_codes_tbl_county.COUNT -> '|| jd_codes_tbl_county.COUNT);
3962
3963 --=============================================================================
3964 IF l_county_withheld = 0 THEN
3965
3966 l_sit_county_withheld :=
3967 hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
3968 ,p_assignment_action_id => p_assignment_action_id
3969 ,p_jurisdiction_code => l_jurisdiction
3970 ,p_tax_unit_id => p_tax_unit_id
3971 ,p_jurisdiction_level => 'STATE'
3972 ,p_effective_date => p_date_paid
3973 ,p_assignment_id => p_assignment_id);
3974 hr_utility.trace('EMJT: l_sit_county_withheld -> '||to_char(l_sit_county_withheld));
3975 END IF;
3976 --=============================================================================
3977
3978 IF l_county_withheld > 0 THEN
3979 --{
3980 hr_utility.trace('EMJT COUNTY: As Tax Withheld previously in County NO THRESHOLD CHECK');
3981 hr_utility.trace('EMJT COUNTY: l_county_withheld > 0 ');
3982 jd_codes_tbl_county(l_counter).jurisdiction_code := l_jurisdiction;
3983 jd_codes_tbl_county(l_counter).hours := l_jd_hours;
3984 jd_codes_tbl_county(l_counter).calc_percent := l_calc_percent;
3985 hr_utility.trace('EMJT COUNTY: County JD '||l_jurisdiction||' loaded in jd_codes_tbl_county ');
3986 --}
3987 ELSIF l_county_withheld = 0 AND l_sit_county_withheld > 0 THEN
3988 --{
3989 hr_utility.trace('EMJT COUNTY: l_county_withheld = 0 AND l_sit_county_withheld > 0 ');
3990 jd_codes_tbl_county(l_counter).jurisdiction_code := l_jurisdiction;
3991 jd_codes_tbl_county(l_counter).hours := l_jd_hours;
3992 jd_codes_tbl_county(l_counter).calc_percent := l_calc_percent;
3993 hr_utility.trace('EMJT COUNTY: County JD '||l_jurisdiction||' loaded in jd_codes_tbl_county ');
3994 --}
3995 -- ELSE --l_county_withheld > 0 */
3996
3997 /*Bug#6869097: changes end here*/
3998 --{
3999 -- Fetch Information Hours logged for the person depending on the payroll effective date
4000 -- call to get_th_assignment for the COUNTY
4001 hr_utility.trace('EMJT COUNTY: Fetch Information Hours Logged for Assignment ');
4002 l_county_ih_logged
4003 := hr_us_ff_udf1.get_person_it_hours(p_person_id => l_person_id
4004 ,p_assignment_id => p_assignment_id
4005 ,p_jurisdiction_code => l_jurisdiction
4006 ,p_jd_level => 6
4007 ,p_threshold_basis => l_threshold_basis
4008 ,p_effective_date => l_max_end_date --p_date_paid
4009 ,p_end_date => l_end_date);
4010 hr_utility.trace('EMJT COUNTY: Information Hours Logged for Assignment => '|| to_char(l_county_ih_logged));
4011 IF l_county_ih_logged >= l_threshold_hours_county THEN
4012 --{
4013 hr_utility.trace('EMJT COUNTY: Information Hours Logged > Threshold_Hours_County ');
4014 l_ih_excluding_pay_period := l_county_ih_logged
4015 - l_jd_hours;
4016 hr_utility.trace('EMJT COUNTY: Information Hours Processed Prior This Pay Period '
4017 || to_char(l_ih_excluding_pay_period));
4018 -- if information hours processed till last payroll run is greater than the
4019 -- threshold limit configured at the County level then hours logged for the county
4020 -- would be accounted for that county
4021 --
4022 IF l_ih_excluding_pay_period >= l_threshold_hours_county THEN
4023 --{
4024 hr_utility.trace('EMJT COUNTY: Hours till last Pay Period > Threshold_Hours_State');
4025 jd_codes_tbl_county(l_counter).jurisdiction_code := l_jurisdiction;
4026 jd_codes_tbl_county(l_counter).hours := l_jd_hours;
4027 jd_codes_tbl_county(l_counter).calc_percent := l_calc_percent;
4028 hr_utility.trace('EMJT COUNTY: County JD loaded into jd_codes_tbl_county '|| l_jurisdiction);
4029 hr_utility.trace('EMJT COUNTY: Hours loaded into jd_codes_tbl_county '|| to_char(l_jd_hours));
4030
4031 --}
4032 ELSE --l_ih_excluding_pay_period >= l_threshold_hours_county
4036 -- Calculate information hours that is exceeds threshold limit
4033 --{
4034 -- if information hours processed till last payroll run is less than the
4035 -- threshold limit configured at the county level
4037 l_ih_above_threshold := l_county_ih_logged - l_threshold_hours_county;
4038 hr_utility.trace('EMJT COUNTY: Information Hours Above Threshold '
4039 || to_char(l_ih_above_threshold));
4040
4041 -- Calculate information hours that would be accounted to primary work location
4042 -- due to threshold
4043 l_ih_for_primary_wk := jd_codes_tbl_county_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
4044 SUBSTR(l_jurisdiction,4,3) ||
4045 '0000')
4046 ).hours - l_ih_above_threshold;
4047 hr_utility.trace('EMJT COUNTY: Hours Accounted for Primary Work Location '
4048 || to_char(l_ih_for_primary_wk));
4049
4050 -- if information hours logged for the county is more than threshold
4051 -- configured for the county, only exceeded hours would be accounted for that
4052 -- county
4053 IF l_ih_above_threshold > 0 THEN
4054 IF jd_codes_tbl_county.EXISTS(
4055 TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
4056 SUBSTR(l_jurisdiction,4,3) ||'0000'))
4057 THEN
4058 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
4059 SUBSTR(l_jurisdiction,4,3)||'0000')).hours :=
4060 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
4061 SUBSTR(l_jurisdiction,4,3)||'0000')).hours
4062 + l_ih_above_threshold ;
4063 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_jurisdiction,1,2)||
4064 SUBSTR(l_jurisdiction,4,3) ||'0000')).calc_percent := l_calc_percent;
4065 ELSE
4066 --{
4067 jd_codes_tbl_county(l_counter).jurisdiction_code := l_jurisdiction;
4068 jd_codes_tbl_county(l_counter).hours := l_ih_above_threshold ;
4069 jd_codes_tbl_county(l_counter).calc_percent := l_calc_percent;
4070 --}
4071 END IF;
4072 END IF;
4073 -- When Total information hours logged for the person is above threshold
4074 -- but there are some information hours need to accounted to primary
4075 -- work location due to threshold limit
4076 -- This is determine if part of information hours entered for the processing pay
4077 -- period need to be accounted to primary work location due to
4078 --
4079 IF l_ih_for_primary_wk > 0 THEN
4080 --{
4081 IF jd_codes_tbl_county.EXISTS(
4082 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4083 SUBSTR(l_work_jurisdiction_code,4,3) ||
4084 '0000'))
4085 THEN
4086 --{
4087 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4088 SUBSTR(l_work_jurisdiction_code,4,3) ||
4089 '0000')
4090 ).hours :=
4091 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4092 SUBSTR(l_work_jurisdiction_code,4,3) ||
4093 '0000')
4094 ).hours + l_ih_for_primary_wk ;
4095 hr_utility.trace('EMJT COUNTY: Hours accounted for Primary WK JD County '
4096 || to_char(l_ih_for_primary_wk));
4097
4098 --}
4099 ELSE
4100 --{
4101 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4102 SUBSTR(l_work_jurisdiction_code,4,3) ||
4103 '0000')).jurisdiction_code
4104 := SUBSTR(l_work_jurisdiction_code,1,7)||'0000';
4105 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4106 SUBSTR(l_work_jurisdiction_code,4,3) ||
4107 '0000')).hours
4108 := l_ih_for_primary_wk;
4109 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4110 SUBSTR(l_work_jurisdiction_code,4,3) ||
4111 '0000')).calc_percent
4112 := l_calc_percent;
4113 hr_utility.trace('EMJT COUNTY: Primary WK JD State loaded into pl table jd_codes_tbl_county');
4114 hr_utility.trace('EMJT COUNTY: Hours accounted for Primary WK JD County '
4115 ||to_char(l_ih_for_primary_wk));
4116 --}
4117 END IF;
4118 --}
4119 END IF; --l_ih_for_primary_wk > 0
4120 --}
4121 END IF; --l_ih_excluding_pay_period >= l_threshold_hours_county
4122 --}
4123 ELSE -- l_county_ih_logged >= l_threshold_hours_county
4124 --{
4125 -- If Information Hours Logged for the assignment is less than Threshold Hours
4126 -- configured for the County, information hours would be accounted to primary work
4127 -- County
4128 IF jd_codes_tbl_county.EXISTS(
4129 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4130 SUBSTR(l_work_jurisdiction_code,4,3) ||
4131 '0000'))
4132 THEN
4133 --{
4134 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4135 SUBSTR(l_work_jurisdiction_code,4,3) ||
4136 '0000')).hours :=
4137 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4138 SUBSTR(l_work_jurisdiction_code,4,3) ||
4139 '0000')).hours + l_jd_hours ;
4140 hr_utility.trace('EMJT COUNTY: Hours accounted for Primary WK JD County '
4141 ||to_char(l_jd_hours));
4142 --}
4143 ELSE
4144 --{
4145 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4146 SUBSTR(l_work_jurisdiction_code,4,3) ||
4147 '0000')).jurisdiction_code
4148 := SUBSTR(l_work_jurisdiction_code,1,7)||'0000';
4149 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4150 SUBSTR(l_work_jurisdiction_code,4,3) ||
4151 '0000')).hours := l_jd_hours;
4152 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4153 SUBSTR(l_work_jurisdiction_code,4,3) ||
4154 '0000')).calc_percent := l_calc_percent;
4155 hr_utility.trace('EMJT COUNTY: Primary WK JD County loaded into pl table jd_codes_tbl_county');
4156 hr_utility.trace('EMJT COUNTY: Hours accounted for Primary WK JD County '
4157 ||to_char(l_jd_hours));
4158 --}
4159 END IF;
4160 --}
4161 END IF;
4162 --}
4163 -- END IF;--l_county_withheld > 0 /*Bug#6869097*/
4164 --}
4165 ELSE
4166 -- If Threshold Hours not logged for the County load Jurisdiction into jd_codes_tbl_state
4167 --
4168 --{
4169 jd_codes_tbl_county(l_counter).jurisdiction_code := l_jurisdiction;
4170 jd_codes_tbl_county(l_counter).hours := l_jd_hours;
4171 jd_codes_tbl_county(l_counter).calc_percent := l_calc_percent;
4172 hr_utility.trace('EMJT COUNTY: Work JD State loaded into jd_codes_tbl_state =>'
4173 ||l_jurisdiction);
4174 hr_utility.trace('EMJT COUNTY: Hours accounted for Primary WK JD State '
4175 ||to_char(l_ih_for_primary_wk));
4176 --}
4177 END IF;--l_threshold_hours_county > 0
4178 --}
4179 END IF; --SUBSTR(l_jurisdiction,1,2) = SUBSTR(l_work_jurisdiction_code,1,2)
4180 --}
4181 END IF; --l_calc_percent = 'N'
4182 --}
4183 hr_utility.trace('EMJT COUNTY: =======================================================================');
4184 hr_utility.trace('EMJT COUNTY: jd_codes_tbl_county.COUNT -> '|| jd_codes_tbl_county.COUNT);
4185 /* hr_utility.trace('EMJT : jd_codes_tbl_county('||to_char(l_counter)||').hours ->'
4186 || jd_codes_tbl_county(l_counter).hours);
4187 hr_utility.trace('EMJT : jd_codes_tbl_county('||to_char(l_counter)||').jurisdiction_code->'
4188 || jd_codes_tbl_county(l_counter).jurisdiction_code);
4189 hr_utility.trace('EMJT : jd_codes_tbl_county('||to_char(l_counter)||').percentage ->'
4190 || jd_codes_tbl_county(l_counter).percentage);
4191 hr_utility.trace('EMJT : jd_codes_tbl_county('||to_char(l_counter)||').calc_percent ->'
4192 || jd_codes_tbl_county(l_counter).calc_percent);*/
4193 hr_utility.trace('EMJT COUNTY: ========================================================================');
4194 hr_utility.trace('EMJT COUNTY: Setting the Index counter to fetch next JD County ');
4195 l_counter := jd_codes_tbl_county_stg.NEXT(l_counter);
4196 hr_utility.trace('EMJT COUNTY: Next Index Counter Value '||to_char(l_counter));
4197 END LOOP;
4198 --Done with populating the jd_codes_tbl_county
4199 hr_utility.trace('EMJT COUNTY: PL Table jd_codes_tbl_county_stg processed Successfully');
4200 hr_utility.trace('EMJT COUNTY: PL Table jd_codes_tbl_county populated with required County details');
4201
4202
4203 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4204 l_counter := NULL;
4205 l_counter := jd_codes_tbl_county.FIRST;
4206 l_last_jd_index_value := jd_codes_tbl_county.LAST;
4207 hr_utility.trace('EMJT COUNTY: jd_codes_tbl_county.FIRST->' || jd_codes_tbl_county.FIRST);
4208 hr_utility.trace('EMJT COUNTY: jd_codes_tbl_county.LAST->' || jd_codes_tbl_county.LAST);
4209
4210 WHILE l_counter IS NOT NULL LOOP
4211 /* hr_utility.trace('EMJT: =========================================================');
4212 hr_utility.trace('EMJT: jd_codes_tbl_county('||to_char(l_counter)||').hours ->'
4213 || jd_codes_tbl_county(l_counter).hours);
4214 hr_utility.trace('EMJT: jd_codes_tbl_county('||to_char(l_counter)||').jurisdiction_code->'
4215 || jd_codes_tbl_county(l_counter).jurisdiction_code);
4216 hr_utility.trace('EMJT: jd_codes_tbl_county('||to_char(l_counter)||').percentage ->'
4217 || jd_codes_tbl_county(l_counter).percentage);
4218 hr_utility.trace('EMJT: =========================================================');*/
4219 l_total_county_hours := l_total_county_hours + NVL(jd_codes_tbl_county(l_counter).hours,0);
4220 l_counter := jd_codes_tbl_county.NEXT(l_counter);
4221 END LOOP;
4222
4223 hr_utility.trace('EMJT COUNTY: l_total_county_hours ->' || to_char(l_total_county_hours) );
4224
4225 IF l_total_county_hours <= l_scheduled_work_hours THEN
4226 l_denominator := l_scheduled_work_hours;
4227 ELSIF l_total_county_hours > l_scheduled_work_hours THEN
4228 l_denominator := l_total_county_hours;
4229 END IF;
4230
4231 l_counter := NULL;
4232 l_counter := jd_codes_tbl_county.FIRST;
4233 l_last_jd_index_value := jd_codes_tbl_county.LAST;
4234 WHILE l_counter IS NOT NULL LOOP
4235 l_jd_hours := jd_codes_tbl_county(l_counter).hours ;
4236 jd_codes_tbl_county(l_counter).percentage :=
4237 ROUND((l_jd_hours/l_denominator) * 100);
4238 /* hr_utility.trace('EMJT: =========================================================');
4239 hr_utility.trace('EMJT: jd_codes_tbl_county('||to_char(l_counter)||').hours ->'
4240 || jd_codes_tbl_county(l_counter).hours);
4241 hr_utility.trace('EMJT: jd_codes_tbl_county('||to_char(l_counter)||').jurisdiction_code->'
4242 || jd_codes_tbl_county(l_counter).jurisdiction_code);
4243 hr_utility.trace('EMJT: jd_codes_tbl_county('||to_char(l_counter)||').percentage ->'
4244 || jd_codes_tbl_county(l_counter).percentage);
4245 hr_utility.trace('EMJT: =========================================================');*/
4246 l_total_county_percent := l_total_county_percent
4247 + jd_codes_tbl_county(l_counter).percentage;
4248 l_counter := jd_codes_tbl_county.NEXT(l_counter);
4249 END LOOP; --WHILE l_counter
4250
4251 l_extra_percent := 0;
4252 IF l_total_county_percent > 100 THEN
4253 --{
4254 l_extra_percent := l_total_county_percent - 100;
4255 IF l_primary_work_jd_flag = 'Y' THEN
4256 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||
4257 SUBSTR(l_work_jurisdiction_code,4,3)||'0000')).percentage
4258 := jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||
4259 SUBSTR(l_work_jurisdiction_code,4,3)||'0000')).percentage
4260 - l_extra_percent;
4261 ELSE
4262 jd_codes_tbl_county(l_last_jd_index_value).percentage
4263 := jd_codes_tbl_county(l_last_jd_index_value).percentage
4264 - l_extra_percent;
4265 END IF;
4266 --}
4267 ELSIF l_total_county_percent < 100 THEN
4268 --{
4269 l_extra_percent := 100 - l_total_county_percent;
4270 IF l_primary_work_jd_flag = 'Y' THEN
4271 jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||
4272 SUBSTR(l_work_jurisdiction_code,4,3)||'0000')).percentage
4273 := jd_codes_tbl_county(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2)||
4274 SUBSTR(l_work_jurisdiction_code,4,3)||'0000')).percentage
4275 + l_extra_percent;
4276 ELSE
4277 jd_codes_tbl_county(l_last_jd_index_value).percentage
4278 := jd_codes_tbl_county(l_last_jd_index_value).percentage
4279 + l_extra_percent;
4280 END IF;
4281 --}
4282 END IF; --l_total_county_percent > 100
4283
4284
4285 l_counter := NULL;
4286 l_counter := jd_codes_tbl_county.FIRST;
4287 -- l_last_jd_index_value := jd_codes_tbl_county.LAST;
4288 -- hr_utility.trace('EMJT: jd_codes_tbl_county.FIRST->' || jd_codes_tbl_county.FIRST);
4289 -- hr_utility.trace('EMJT: jd_codes_tbl_county.LAST->' || jd_codes_tbl_county.LAST);
4290
4291 WHILE l_counter IS NOT NULL LOOP
4292 hr_utility.trace('EMJT COUNTY: Final County Table ');
4293 hr_utility.trace('EMJT COUNTY: =========================================================');
4294 hr_utility.trace('EMJT COUNTY: jd_codes_tbl_county('||to_char(l_counter)||').hours ->'
4295 || jd_codes_tbl_county(l_counter).hours);
4296 hr_utility.trace('EMJT COUNTY: jd_codes_tbl_county('||to_char(l_counter)||').jurisdiction_code->'
4297 || jd_codes_tbl_county(l_counter).jurisdiction_code);
4298 hr_utility.trace('EMJT COUNTY: jd_codes_tbl_county('||to_char(l_counter)||').percentage ->'
4299 || jd_codes_tbl_county(l_counter).percentage);
4300 hr_utility.trace('EMJT COUNTY: =========================================================');
4301 -- l_total_county_hours := l_total_county_hours + jd_codes_tbl_county(l_counter).hours;
4302 l_counter := jd_codes_tbl_county.NEXT(l_counter);
4303 END LOOP;
4304
4305 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4306
4307 hr_utility.trace('EMJT CITY :===========================================================');
4308 hr_utility.trace('EMJT CITY: Main City Processing');
4309
4310 --Starting processing the city, populating jurisdiction_codes_tbl.
4311 l_counter := NULL;
4312 --
4313 --This part of the code populates the MAIN COUNTY PL TABLE from the staging city pl table.
4314 --INTO jurisdiction_codes_tbl
4315 --FROM jd_codes_tbl_city_stg
4316 -- l_primary_work_jd_index_value
4317 l_counter := jurisdiction_codes_tbl_stg.FIRST;
4318 l_last_jd_index_value := jurisdiction_codes_tbl_stg.LAST;
4319 hr_utility.trace('EMJT CITY: First JD Code '||to_char(l_counter));
4320 hr_utility.trace('EMJT CITY: Last JD Code '||to_char(l_last_jd_index_value));
4321
4322 WHILE l_counter IS NOT NULL LOOP
4323 --{
4324 l_jurisdiction := jurisdiction_codes_tbl_stg(l_counter).jurisdiction_code ;
4325 l_jd_hours := jurisdiction_codes_tbl_stg(l_counter).hours ;
4326 hr_utility.trace('EMJT CITY: l_counter =>'|| to_char(l_counter));
4327 hr_utility.trace('EMJT CITY: Jurisdiction Code =>'|| l_jurisdiction);
4328 hr_utility.trace('EMJT CITY: l_jd_hours =>'|| to_char(l_jd_hours));
4329 hr_utility.trace('EMJT CITY: l_work_jurisdiction_code '|| l_work_jurisdiction_code);
4330
4331 IF SUBSTR(l_jurisdiction,1,2) ||
4332 SUBSTR(l_jurisdiction,4,3) ||
4333 SUBSTR(l_jurisdiction,8,4) =
4334 SUBSTR(l_work_jurisdiction_code,1,2) ||
4335 SUBSTR(l_work_jurisdiction_code,4,3) ||
4336 SUBSTR(l_work_jurisdiction_code,8,4)
4337 THEN
4338 --{
4339
4340 IF jd_codes_tbl_city_stg.EXISTS(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
4341 SUBSTR(l_jurisdiction,4,3) ||
4342 SUBSTR(l_jurisdiction,8,4) ))
4343 THEN
4344 --{
4345 hr_utility.trace('EMJT CITY: Primary WK JD already in jd_codes_tbl_city_stg');
4346 hr_utility.trace('EMJT CITY: Add This JD Hours to Primary Work Location');
4347 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
4348 SUBSTR(l_jurisdiction,4,3) ||
4349 SUBSTR(l_jurisdiction,8,4))).hours
4350 := jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
4351 SUBSTR(l_jurisdiction,4,3) ||
4352 SUBSTR(l_jurisdiction,8,4) )
4353 ).hours + l_jd_hours;
4354 --}
4355 ELSE
4356 --{
4357 hr_utility.trace('EMJT CITY: Primary work jurisidction loaded in jd_codes_tbl_city_stg');
4358 jd_codes_tbl_city_stg(l_counter).jurisdiction_code := l_jurisdiction;
4359 jd_codes_tbl_city_stg(l_counter).hours := l_jd_hours;
4360 jd_codes_tbl_city_stg(l_counter).jd_type :=
4361 jurisdiction_codes_tbl_stg(l_counter).jd_type;
4362 --}
4363 END IF;
4364 --}
4365 ELSE --SUBSTR(l_jurisdiction,1,2) = SUBSTR(l_work_jurisdiction_code,1,2)
4366 --{
4367 hr_utility.trace('EMJT CITY: l_counter not work_jd '|| l_work_jurisdiction_code);
4368
4369 IF l_jd_hours = 0 AND jurisdiction_codes_tbl_stg(l_counter).jd_type = 'TG' THEN
4370 --{
4371 IF jd_codes_tbl_city_stg.EXISTS(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
4372 SUBSTR(l_jurisdiction,4,3) ||
4373 SUBSTR(l_jurisdiction,8,4) ))
4374 THEN
4375 --{
4376 hr_utility.trace('EMJT CITY: This is a Tagged Jurisdiction which is already loaded');
4377 --}
4378 ELSE
4379 --{
4380 hr_utility.trace('EMJT CITY: Load Tagged Jurisdiction into jd_codes_tbl_city_stg');
4381 jd_codes_tbl_city_stg(l_counter).jurisdiction_code := l_jurisdiction;
4382 jd_codes_tbl_city_stg(l_counter).hours := l_jd_hours;
4383 jd_codes_tbl_city_stg(l_counter).jd_type :=
4384 jurisdiction_codes_tbl_stg(l_counter).jd_type;
4385 --}
4386 END IF;
4387 --}
4388 ELSE --l_jd_hours = 0 and jd_codes_tbl_city_stg(l_counter).jd_type = 'TG'
4389 --Fetch City level threshold
4390 hr_utility.trace('EMJT CITY: Fetch Threshold Hours for City');
4391 l_threshold_hours_city := get_jd_level_threshold(p_tax_unit_id
4392 ,l_jurisdiction
4393 ,'CITY');
4394 hr_utility.trace('EMJT CITY: Threshold Hours for City '||to_char(l_threshold_hours_city));
4395 IF l_threshold_hours_city > 0 THEN
4396 --{
4397 -- Fetch the city level tax balance accrued for the person
4398 -- If Tax balance found then tax the city as per hours logged for the city
4399 -- otherwise hours will be accounted to primary work city
4400 hr_utility.trace('EMJT CITY: Fetch City level Tax Withheld for Assignment');
4401
4402
4403 /* Bug#6869097:The following code checks whether city tax is withheld already
4404 for the assignment and if it finds city tax is withheld already it assumes that
4405 the assignemnt has crossed the threshold limit already and inserts the
4406 current record. Commented the following code so that it can go on with
4407 threshold checking irrespective of city tax Withheld balance.
4408 */
4409
4410 /* l_city_withheld :=
4411 hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
4412 ,p_assignment_action_id=> p_assignment_action_id
4413 ,p_jurisdiction_code => l_jurisdiction
4414 ,p_tax_unit_id => p_tax_unit_id
4415 ,p_jurisdiction_level => 'CITY'
4416 ,p_effective_date => p_date_paid
4417 ,p_assignment_id => p_assignment_id);
4418 hr_utility.trace('EMJT CITY: City level Tax Withheld for Assignment '||
4419 to_char(l_city_withheld));
4420
4421 --=============================================================================
4422 IF l_city_withheld = 0 THEN
4423
4424 l_county_city_withheld :=
4425 hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
4426 ,p_assignment_action_id=> p_assignment_action_id
4427 ,p_jurisdiction_code => l_jurisdiction
4428 ,p_tax_unit_id => p_tax_unit_id
4429 ,p_jurisdiction_level => 'COUNTY'
4430 ,p_effective_date => p_date_paid
4431 ,p_assignment_id => p_assignment_id);
4432 hr_utility.trace('EMJT: l_county_city_withheld -> '||to_char(l_county_city_withheld));
4433
4434 IF l_county_city_withheld = 0 THEN
4435
4436 l_sit_city_withheld :=
4437 hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
4438 ,p_assignment_action_id => p_assignment_action_id
4439 ,p_jurisdiction_code => l_jurisdiction
4440 ,p_tax_unit_id => p_tax_unit_id
4441 ,p_jurisdiction_level => 'STATE'
4442 ,p_effective_date => p_date_paid
4443 ,p_assignment_id => p_assignment_id);
4444 hr_utility.trace('EMJT: l_sit_city_withheld -> '||to_char(l_sit_city_withheld));
4445 END IF;
4446
4447 END IF;
4448
4449
4450 --=============================================================================
4451 IF l_city_withheld > 0 THEN
4452 --{
4453 hr_utility.trace('EMJT CITY: As City level Tax is Withheld previously ');
4454 hr_utility.trace('EMJT CITY: NO THRESHOLD Validation required for the City');
4455 jd_codes_tbl_city_stg(l_counter).jurisdiction_code := l_jurisdiction;
4456 jd_codes_tbl_city_stg(l_counter).hours := l_jd_hours;
4457 jd_codes_tbl_city_stg(l_counter).jd_type :=
4458 jurisdiction_codes_tbl_stg(l_counter).jd_type;
4459 --}
4460 ELSIF l_city_withheld = 0 AND l_county_city_withheld > 0 THEN
4461 --{
4462 hr_utility.trace('EMJT CITY: l_city_withheld = 0 AND l_county_city_withheld > 0 ');
4463 jd_codes_tbl_city_stg(l_counter).jurisdiction_code := l_jurisdiction;
4464 jd_codes_tbl_city_stg(l_counter).hours := l_jd_hours;
4465 jd_codes_tbl_city_stg(l_counter).jd_type :=
4466 jurisdiction_codes_tbl_stg(l_counter).jd_type;
4467 --}
4468 ELSIF l_city_withheld = 0 AND l_county_city_withheld = 0 AND l_sit_city_withheld > 0 THEN
4469 --{
4470 hr_utility.trace('EMJT CITY: l_city_withheld = 0 l_county_city_withheld = 0 l_sit_city_withheld > 0');
4471 jd_codes_tbl_city_stg(l_counter).jurisdiction_code := l_jurisdiction;
4472 jd_codes_tbl_city_stg(l_counter).hours := l_jd_hours;
4473 jd_codes_tbl_city_stg(l_counter).jd_type :=
4474 jurisdiction_codes_tbl_stg(l_counter).jd_type;
4475 --}
4476 ELSE --l_city_withheld > 0 */
4477 --{
4478
4479 /*Bug#6869097:Changes end here*/
4480
4481 -- Fetch Information Hours logged for the person depending on the payroll effective date
4482 -- call to get_th_assignment for the CITY
4483 hr_utility.trace('EMJT CITY: Fetch Information Hours Logged for the CITY JD');
4484 l_city_ih_logged
4485 := hr_us_ff_udf1.get_person_it_hours(p_person_id => l_person_id
4486 ,p_assignment_id => p_assignment_id
4487 ,p_jurisdiction_code => l_jurisdiction
4488 ,p_jd_level => 11
4489 ,p_threshold_basis => l_threshold_basis
4490 ,p_effective_date => l_max_end_date --p_date_paid
4491 ,p_end_date => l_end_date);
4492 hr_utility.trace('EMJT CITY: Information Hours logged for the assignment '
4493 ||to_char(l_city_ih_logged));
4494 IF l_city_ih_logged > l_threshold_hours_city THEN
4495 --{
4496 hr_utility.trace('EMJT CITY: City Information Hours logged > Threshold_Hours_City');
4497 l_ih_excluding_pay_period := l_city_ih_logged
4498 - l_jd_hours;
4499 hr_utility.trace('EMJT CITY: City Information Hours till last Pay period'
4500 ||to_char(l_ih_excluding_pay_period));
4501 -- if information hours processed till last payroll run is greater than the
4502 -- threshold limit configured at the City level then hours logged for the city
4503 -- would be accounted for that city
4504 --
4505 IF l_ih_excluding_pay_period >= l_threshold_hours_city THEN
4506 --{
4507 hr_utility.trace('EMJT CITY: Information Hours excluding Current Pay Period');
4508 hr_utility.trace('EMJT CITY: >= Threshold Hours configured for the city');
4509 --
4510 jd_codes_tbl_city_stg(l_counter).jurisdiction_code := l_jurisdiction;
4511 jd_codes_tbl_city_stg(l_counter).hours := l_jd_hours;
4512 jd_codes_tbl_city_stg(l_counter).jd_type :=
4513 jurisdiction_codes_tbl_stg(l_counter).jd_type;
4514 hr_utility.trace('EMJT CITY: Information Hours accounted to logged JD '||l_jurisdiction);
4515 --}
4516 ELSE --l_ih_excluding_pay_period >= l_threshold_hours_city
4517 --{
4518 -- if information hours processed till last payroll run is less than the
4519 -- threshold limit configured at the city level
4520 -- Calculate information hours that is exceeds threshold limit
4521 l_ih_above_threshold := l_city_ih_logged - l_threshold_hours_city;
4522 hr_utility.trace('EMJT CITY: Information Hours above City Threshold '
4523 ||to_char(l_ih_above_threshold));
4524 -- Calculate information hours that would be accounted to primary work location
4525 -- due to threshold
4526 l_ih_for_primary_wk :=
4527 jurisdiction_codes_tbl_stg(TO_NUMBER(SUBSTR(l_jurisdiction,1,2) ||
4528 SUBSTR(l_jurisdiction,4,3) ||
4529 SUBSTR(l_jurisdiction,8,4) )
4530 ).hours - l_ih_above_threshold;
4531 hr_utility.trace('EMJT CITY: Information Hours to be logged for Primar Work '
4532 ||to_char(l_ih_for_primary_wk));
4533 -- if information hours logged for the city is more than threshold
4534 -- configured for the city, only exceeded hours would be accounted for that
4535 -- city
4536 IF l_ih_above_threshold > 0 THEN
4537 jd_codes_tbl_city_stg(l_counter).jurisdiction_code := l_jurisdiction;
4538 jd_codes_tbl_city_stg(l_counter).hours := l_ih_above_threshold ;
4539 jd_codes_tbl_city_stg(l_counter).jd_type :=
4540 jurisdiction_codes_tbl_stg(l_counter).jd_type;
4541 hr_utility.trace('EMJT CITY: As Information Hours above Threshold ');
4542 hr_utility.trace('EMJT CITY: Log Hours '||to_char(l_ih_for_primary_wk)
4543 ||' to JD '||l_jurisdiction);
4544 END IF;
4545 -- When Total information hours logged for the person is above threshold
4546 -- but there are some information hours need to accounted to primary
4547 -- work location due to threshold limit
4548 -- This is determine if part of information hours entered for the processing pay
4549 -- period need to be accounted to primary work location due to
4550 --
4551 IF l_ih_for_primary_wk > 0 THEN
4552 --{
4553 IF jd_codes_tbl_city_stg.EXISTS(
4554 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4555 SUBSTR(l_work_jurisdiction_code,4,3) ||
4556 SUBSTR(l_work_jurisdiction_code,8,4) ) )
4557 THEN
4558 --{
4559 hr_utility.trace('EMJT CITY: Hours Added to Primary Work Jurisdiction '
4560 ||to_char(l_ih_for_primary_wk));
4561 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4562 SUBSTR(l_work_jurisdiction_code,4,3) ||
4563 SUBSTR(l_work_jurisdiction_code,8,4) )
4564 ).hours :=
4565 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4566 SUBSTR(l_work_jurisdiction_code,4,3) ||
4567 SUBSTR(l_work_jurisdiction_code,8,4) )
4568 ).hours + l_ih_for_primary_wk ;
4569 --}
4570 ELSE
4571 --{
4572 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4573 SUBSTR(l_work_jurisdiction_code,4,3) ||
4574 SUBSTR(l_work_jurisdiction_code,8,4) )
4575 ).jurisdiction_code
4576 := l_work_jurisdiction_code;
4577 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4578 SUBSTR(l_work_jurisdiction_code,4,3) ||
4579 SUBSTR(l_work_jurisdiction_code,8,4) )).hours
4580 := l_ih_for_primary_wk;
4581 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4582 SUBSTR(l_work_jurisdiction_code,4,3) ||
4583 SUBSTR(l_work_jurisdiction_code,8,4) )).jd_type
4584 := jurisdiction_codes_tbl_stg(l_counter).jd_type;
4585 hr_utility.trace('EMJT CITY: Hours logged for Primary Work Jurisdiction '
4586 ||to_char(l_ih_for_primary_wk));
4587
4588 --}
4589 END IF;
4590 --}
4591 END IF; --l_ih_for_primary_wk > 0
4592 --}
4593 END IF;
4594 --}
4595 ELSE --l_ih_excluding_pay_period >= l_threshold_hours_state
4596 --{
4597 -- If Information Hours Logged for the assignment is less than Threshold Hours
4598 -- configured for the City, information hours would be accounted to primary work
4599 -- State
4600 hr_utility.trace('EMJT CITY : l_work_jurisdiction_code ->'|| l_work_jurisdiction_code);
4601 hr_utility.trace('EMJT CITY : l_counter ->'|| l_counter);
4602 IF jd_codes_tbl_city_stg.EXISTS(
4603 TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4604 SUBSTR(l_work_jurisdiction_code,4,3) ||
4605 SUBSTR(l_work_jurisdiction_code,8,4) ))
4606 THEN
4607 --{
4608 hr_utility.trace('EMJT CITY : IN IF l_counter ->'|| l_counter);
4609 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4610 SUBSTR(l_work_jurisdiction_code,4,3) ||
4611 SUBSTR(l_work_jurisdiction_code,8,4) )).hours :=
4612 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4613 SUBSTR(l_work_jurisdiction_code,4,3) ||
4614 SUBSTR(l_work_jurisdiction_code,8,4) )).hours + l_jd_hours ;
4615 hr_utility.trace('EMJT CITY: Hours accounted for Primary WK JD State '
4616 ||to_char(l_jd_hours));
4617 --}
4618 ELSE
4619 --{
4620 hr_utility.trace('EMJT CITY : IN ELSE l_counter ->'|| l_counter);
4621 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4622 SUBSTR(l_work_jurisdiction_code,4,3) ||
4623 SUBSTR(l_work_jurisdiction_code,8,4) )
4624 ).jurisdiction_code
4625 := l_work_jurisdiction_code;
4626 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4627 SUBSTR(l_work_jurisdiction_code,4,3) ||
4628 SUBSTR(l_work_jurisdiction_code,8,4) )).hours
4629 := l_jd_hours;
4630 jd_codes_tbl_city_stg(TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
4631 SUBSTR(l_work_jurisdiction_code,4,3) ||
4632 SUBSTR(l_work_jurisdiction_code,8,4) )).jd_type
4633 := 'WK';
4634 hr_utility.trace('EMJT CITY: Primary WK JD City loaded into jd_codes_tbl_city_stg');
4635 hr_utility.trace('EMJT CITY: Hours accounted for Primary WK JD City '
4636 ||to_char(l_jd_hours));
4637 --}
4638 END IF;
4639 --}
4640 END IF; --l_city_ih_logged > l_threshold_hours_city
4641 --}
4642 -- END IF;--l_city_withheld > 0 /*6869097*/
4643 --}
4644 ELSE
4645 --{
4646 -- If Threshold Hours not logged for a City load Jurisdiction into jd_codes_tbl_city_stg
4647 --
4648 jd_codes_tbl_city_stg(l_counter).jurisdiction_code := l_jurisdiction;
4649 jd_codes_tbl_city_stg(l_counter).hours := l_jd_hours;
4650 jd_codes_tbl_city_stg(l_counter).jd_type :=
4651 jurisdiction_codes_tbl_stg(l_counter).jd_type;
4652 hr_utility.trace('EMJT CITY: Work City JD loaded into jd_codes_tbl_city_stg =>'
4653 ||l_jurisdiction);
4654 --}
4655 END IF;--l_threshold_hours_city > 0
4656 --}
4657 END IF; --l_jd_hours = 0 and jd_codes_tbl_city_stg(l_counter).jd_type = 'TG'
4658 --}
4659 END IF; --SUBSTR(l_jurisdiction,1,2) = SUBSTR(l_work_jurisdiction_code,1,2)
4660 --}
4661 hr_utility.trace('EMJT CITY: Setting the Index counter to fetch next JD City ');
4662 l_counter := jurisdiction_codes_tbl_stg.NEXT(l_counter);
4663 hr_utility.trace('EMJT CITY: Next Index Counter Value '||to_char(l_counter));
4664
4665 END LOOP;
4666 --Done with populating the jurisdiction_codes_tbl
4667 hr_utility.trace('EMJT CITY: PL Table jd_codes_tbl_city_stg processed Successfully');
4668
4669 --=============================================================================================
4670
4671 hr_utility.trace('EMJT CITY: Total Informational Time Hours entered -> '||to_char(l_total_hours));
4672 IF l_total_hours <= l_scheduled_work_hours THEN
4673 l_denominator := l_scheduled_work_hours;
4674 ELSIF l_total_hours > l_scheduled_work_hours THEN
4675 l_denominator := l_total_hours;
4676 END IF;
4677
4678 l_counter := NULL;
4679 l_counter := jd_codes_tbl_city_stg.FIRST;
4680 l_last_jd_index_value := jd_codes_tbl_city_stg.LAST;
4681 WHILE l_counter IS NOT NULL LOOP
4682 l_jd_hours := jd_codes_tbl_city_stg(l_counter).hours ;
4683 jd_codes_tbl_city_stg(l_counter).percentage :=
4684 ROUND((l_jd_hours/l_denominator) * 100);
4685 hr_utility.trace('EMJT CITY: =========================================================');
4686 hr_utility.trace('EMJT CITY: jd_codes_tbl_city_stg('||to_char(l_counter)||').hours ->'
4687 || jd_codes_tbl_city_stg(l_counter).hours);
4688 hr_utility.trace('EMJT CITY: jd_codes_tbl_city_stg('||to_char(l_counter)||').tg_hours ->'
4689 || jd_codes_tbl_city_stg(l_counter).tg_hours);
4690 hr_utility.trace('EMJT CITY: jd_codes_tbl_city_stg('||to_char(l_counter)||').jurisdiction_code->'
4691 || jd_codes_tbl_city_stg(l_counter).jurisdiction_code);
4695 || jd_codes_tbl_city_stg(l_counter).jd_type);
4692 hr_utility.trace('EMJT CITY: jd_codes_tbl_city_stg('||to_char(l_counter)||').percentage ->'
4693 || jd_codes_tbl_city_stg(l_counter).percentage);
4694 hr_utility.trace('EMJT CITY: jd_codes_tbl_city_stg('||to_char(l_counter)||').jd_type ->'
4696 hr_utility.trace('EMJT CITY: =========================================================');
4697 l_total_percent := l_total_percent
4698 + jd_codes_tbl_city_stg(l_counter).percentage;
4699 l_counter := jd_codes_tbl_city_stg.NEXT(l_counter);
4700 END LOOP; --WHILE l_counter
4701
4702
4703 IF l_total_percent > 100 THEN
4704 --{
4705 l_extra_percent := l_total_percent - 100;
4706 IF l_primary_work_jd_flag = 'Y' THEN
4707 jd_codes_tbl_city_stg(l_primary_work_jd_index_value).percentage
4708 := jd_codes_tbl_city_stg(l_primary_work_jd_index_value).percentage
4709 - l_extra_percent;
4710 ELSE
4711 jd_codes_tbl_city_stg(l_last_jd_index_value).percentage
4712 := jd_codes_tbl_city_stg(l_last_jd_index_value).percentage
4713 - l_extra_percent;
4714 END IF;
4715 --}
4716 ELSIF l_total_percent < 100 THEN
4717 --{
4718 l_extra_percent := 100 - l_total_percent;
4719 IF l_primary_work_jd_flag = 'Y' THEN
4720 jd_codes_tbl_city_stg(l_primary_work_jd_index_value).percentage
4721 := jd_codes_tbl_city_stg(l_primary_work_jd_index_value).percentage
4722 + l_extra_percent;
4723 ELSE
4724 jd_codes_tbl_city_stg(l_last_jd_index_value).percentage
4725 := jd_codes_tbl_city_stg(l_last_jd_index_value).percentage
4726 + l_extra_percent;
4727 END IF;
4728 --}
4729 END IF; --l_total_percent > 100
4730
4731 --========================================================================
4732 OPEN csr_person_details(p_assignment_id, p_date_paid);
4733 FETCH csr_person_details INTO l_full_name, l_assignment_number;
4734 CLOSE csr_person_details;
4735 --========================================================================
4736 hr_utility.trace('EMJT Full Name -> '|| l_full_name );
4737 hr_utility.trace('EMJT Assignment Number -> '|| l_assignment_number );
4738 --
4739 --Populate jurisdiction_codes_tbl
4740 --
4741 l_counter := jd_codes_tbl_city_stg.FIRST;
4742 WHILE l_counter IS NOT NULL LOOP
4743 jurisdiction_codes_tbl(l_counter).jurisdiction_code
4744 := jd_codes_tbl_city_stg(l_counter).jurisdiction_code;
4745 jurisdiction_codes_tbl(l_counter).percentage
4746 := jd_codes_tbl_city_stg(l_counter).percentage;
4747 jurisdiction_codes_tbl(l_counter).jd_type
4748 := jd_codes_tbl_city_stg(l_counter).jd_type;
4749 jurisdiction_codes_tbl(l_counter).hours
4750 := jd_codes_tbl_city_stg(l_counter).hours;
4751 jurisdiction_codes_tbl(l_counter).wages_to_accrue_flag
4752 := jd_codes_tbl_city_stg(l_counter).wages_to_accrue_flag;
4753 jurisdiction_codes_tbl(l_counter).tg_hours
4754 := jd_codes_tbl_city_stg(l_counter).tg_hours;
4755 jurisdiction_codes_tbl(l_counter).other_pay_hours
4756 := jd_codes_tbl_city_stg(l_counter).other_pay_hours;
4757 hr_utility.trace('EMJT CITY: ===================================================');
4758 hr_utility.trace('EMJT CITY: jurisdiction_codes_tbl('||to_char(l_counter)||').jurisdiction_code->'
4759 || jurisdiction_codes_tbl(l_counter).jurisdiction_code);
4760 hr_utility.trace('EMJT CITY: jurisdiction_codes_tbl('||to_char(l_counter)||').hours ->'
4761 || jurisdiction_codes_tbl(l_counter).hours);
4762 hr_utility.trace('EMJT CITY: jurisdiction_codes_tbl('||to_char(l_counter)||').tg_hours ->'
4763 || jurisdiction_codes_tbl(l_counter).tg_hours);
4764 hr_utility.trace('EMJT CITY: jurisdiction_codes_tbl('||to_char(l_counter)||').percentage ->'
4765 || jurisdiction_codes_tbl(l_counter).percentage);
4766 hr_utility.trace('EMJT CITY: jurisdiction_codes_tbl('||to_char(l_counter)||').jd_type ->'
4767 || jurisdiction_codes_tbl(l_counter).jd_type);
4768 hr_utility.trace('EMJT CITY: ===================================================');
4769 --================================================================================================
4770 l_spelled_jd_code :=
4771 pay_us_employee_payslip_web.get_full_jurisdiction_name(
4772 jd_codes_tbl_city_stg(l_counter).jurisdiction_code);
4773 hr_utility.trace('EMJT spelled jd code -> '|| l_spelled_jd_code );
4774
4775 pay_core_utils.push_message(801,'PAY_US_EMJT_EMPLOYEE_INFO','P');
4776 pay_core_utils.push_token('EMPLOYEE_NAME',SUBSTR(l_full_name,1,50));
4777 pay_core_utils.push_token('ASSIGNMENT_NUMBER',SUBSTR(l_assignment_number,1,50));
4778 pay_core_utils.push_token('JURISDICTION_NAME',SUBSTR(l_spelled_jd_code,1,50));
4779 pay_core_utils.push_token('HOURS',SUBSTR(jd_codes_tbl_city_stg(l_counter).hours,1,50));
4780 pay_core_utils.push_token('PERCENTAGE',SUBSTR(jd_codes_tbl_city_stg(l_counter).percentage,1,50));
4781 --================================================================================================
4782 l_counter := jd_codes_tbl_city_stg.NEXT(l_counter);
4783 END LOOP; --WHILE l_counter
4784 --}
4785 END IF; --p_initialize = 'Y'
4786 RETURN ('0');
4787 EXCEPTION
4788 WHEN NO_DATA_FOUND THEN
4789 hr_utility.trace('Exception raised NO_DATA_FOUND in '||'get_it_work_jurisdictions');
4790 p_jurisdiction_code := 'NULL';
4791 p_percentage := 0;
4792 RETURN ('0');
4793 WHEN TOO_MANY_JURISDICTIONS THEN
4794 hr_utility.set_message(801, 'PAY_75242_PAY_TOO_MANY_JD');
4795 hr_utility.set_message_token('MAX_WORK_JDS', l_max_jurisdictions);
4796 hr_utility.raise_error;
4797 RAISE;
4798 WHEN OTHERS THEN
4799 hr_utility.trace('Exception raised OTHERS in '||'get_it_work_jurisdictions');
4800 hr_utility.trace('Mesg: '||substr(sqlerrm,1,45));
4801 p_jurisdiction_code := 'NULL';
4802 p_percentage := 0;
4803 RETURN ('0');
4804 -- End of Function get_it_work_jurisdictions
4805 --}
4806 END get_it_work_jurisdictions;
4807
4808 --Function to get balance value
4809 FUNCTION get_jd_tax_balance(p_threshold_basis IN VARCHAR2
4810 ,p_assignment_action_id IN NUMBER
4811 ,p_jurisdiction_code IN VARCHAR2
4812 ,p_tax_unit_id IN NUMBER
4813 ,p_jurisdiction_level IN VARCHAR2
4814 ,p_effective_date IN DATE
4815 ,p_assignment_id IN NUMBER
4816 ) RETURN NUMBER AS
4817
4818 l_value NUMBER;
4819
4820 CURSOR csr_defined_balance_id(p_balance_name IN VARCHAR2
4821 ,p_database_item_suffix IN VARCHAR2) IS
4822 SELECT pdb.defined_balance_id, pbt.balance_type_id
4823 FROM pay_defined_balances pdb,
4824 pay_balance_types pbt,
4825 pay_balance_dimensions pbd
4826 WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
4827 AND pdb.balance_type_id = pbt.balance_type_id
4828 AND pbt.balance_name = p_balance_name
4829 AND pbd.database_item_suffix = p_database_item_suffix
4830 AND pdb.legislation_code = 'US';
4831
4832 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
4833 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
4834 l_state_flag VARCHAR2(1);
4835 l_state_tax_flag VARCHAR2(1);
4836 l_county_tax_flag VARCHAR2(1);
4837 l_city_tax_flag VARCHAR2(1);
4838
4839 l_jurisdiction_level NUMBER;
4840
4841 BEGIN
4842 hr_utility.trace('EMJT: ================================================================');
4843 hr_utility.trace('EMJT: In get_jd_tax_balance');
4844 hr_utility.trace('EMJT: p_threshold_basis -> '||p_threshold_basis);
4845 hr_utility.trace('EMJT: p_jurisdiction_level -> '||p_jurisdiction_level);
4846 hr_utility.trace('EMJT: p_jurisdiction_code -> '||p_jurisdiction_code);
4847 hr_utility.trace('EMJT: p_tax_unit_id -> '||p_tax_unit_id);
4848 hr_utility.trace('EMJT: p_assignment_action_id -> '|| p_assignment_action_id);
4849
4850 l_value := 0;
4851 l_state_tax_flag := 'N';
4852 l_county_tax_flag := 'N';
4853 l_city_tax_flag := 'N';
4854
4855 IF p_jurisdiction_level = 'STATE' THEN
4856
4857 l_state_tax_flag :=
4858 pay_get_tax_exists_pkg.get_tax_exists(p_juri_code => p_jurisdiction_code,
4859 p_date_earned => p_effective_date,
4860 p_tax_unit_id => p_tax_unit_id,
4861 p_assign_id => p_assignment_id,
4862 p_pact_id => NULL,
4863 p_type => 'SIT_WK',
4864 p_call => 'F');
4865
4866 hr_utility.trace('EMJT: l_state_tax_flag -> '|| l_state_tax_flag);
4867 ELSIF p_jurisdiction_level = 'COUNTY' THEN
4868 l_county_tax_flag :=
4869 pay_get_tax_exists_pkg.get_tax_exists(p_juri_code => p_jurisdiction_code,
4870 p_date_earned => p_effective_date,
4871 p_tax_unit_id => p_tax_unit_id,
4872 p_assign_id => p_assignment_id,
4873 p_pact_id => NULL,
4874 p_type => 'COUNTY_WK',
4875 p_call => 'F');
4876
4877 hr_utility.trace('EMJT: l_county_tax_flag -> '|| l_county_tax_flag);
4878 ELSIF p_jurisdiction_level ='CITY' THEN
4879 l_city_tax_flag :=
4880 pay_get_tax_exists_pkg.get_tax_exists(p_juri_code => p_jurisdiction_code,
4881 p_date_earned => p_effective_date,
4882 p_tax_unit_id => p_tax_unit_id,
4883 p_assign_id => p_assignment_id,
4884 p_pact_id => NULL,
4885 p_type => 'CITY_WK',
4886 p_call => 'F');
4887 hr_utility.trace('EMJT: l_city_tax_flag -> '|| l_city_tax_flag);
4888 END IF; --p_jurisdiction_level = 'STATE'
4889
4890
4891 IF l_state_tax_flag = 'Y' THEN
4892 --{
4893
4894 IF p_threshold_basis = 'YTD' THEN
4895 --{
4896 hr_utility.trace('EMJT: Getting SIT Withheld for YTD');
4897 OPEN csr_defined_balance_id('SIT Withheld','_PER_JD_GRE_YTD');
4898 hr_utility.trace('EMJT: Fetching SIT Withheld _PER_JD_GRE_YTD ');
4899 FETCH csr_defined_balance_id INTO l_defined_balance_id,l_balance_type_id;
4900 CLOSE csr_defined_balance_id;
4901
4902 hr_utility.trace('EMJT: STATE l_defined_balance_id -> '|| l_defined_balance_id);
4906 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
4903 hr_utility.trace('EMJT: STATE l_balance_type_id -> '|| l_balance_type_id);
4904
4905 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
4907 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
4908
4909 l_value := NVL(pay_balance_pkg.get_value
4910 (p_defined_balance_id => l_defined_balance_id
4911 ,p_assignment_action_id => p_assignment_action_id)
4912 ,0);
4913
4914 IF l_value = 0 THEN
4915 --{
4916 OPEN csr_defined_balance_id('SIT Supp Withheld','_PER_JD_GRE_YTD');
4917 hr_utility.trace('EMJT: Fetching SIT Supp Withheld_PER_JD_GRE_YTD for YTD ');
4918 FETCH csr_defined_balance_id INTO l_defined_balance_id,l_balance_type_id;
4919 CLOSE csr_defined_balance_id;
4920 l_value := NVL(pay_balance_pkg.get_value
4921 (p_defined_balance_id => l_defined_balance_id
4922 ,p_assignment_action_id => p_assignment_action_id)
4923 ,0);
4924 --}
4925 END IF;
4926 --}
4927 ELSIF p_threshold_basis = 'RTD' THEN
4928 --{
4929
4930 OPEN csr_defined_balance_id('SIT Withheld','_PER_JD_GRE_RTD');
4931 hr_utility.trace('EMJT: Fetching SIT Withheld _PER_JD_GRE_RTD ');
4932 FETCH csr_defined_balance_id INTO l_defined_balance_id,l_balance_type_id;
4933 CLOSE csr_defined_balance_id;
4934
4935 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
4936 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
4937 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
4938 l_value := NVL(pay_balance_pkg.get_value
4939 (p_defined_balance_id => l_defined_balance_id
4940 ,p_assignment_action_id => p_assignment_action_id)
4941 ,0);
4942
4943 IF l_value = 0 THEN
4944 --{
4945 OPEN csr_defined_balance_id('SIT Supp Withheld','_PER_JD_GRE_RTD');
4946 hr_utility.trace('EMJT: Fetching SIT Supp Withheld_PER_JD_GRE_RTD for RTD');
4947 FETCH csr_defined_balance_id INTO l_defined_balance_id,l_balance_type_id;
4948 CLOSE csr_defined_balance_id;
4949
4950 l_value := NVL(pay_balance_pkg.get_value
4951 (p_defined_balance_id => l_defined_balance_id
4952 ,p_assignment_action_id => p_assignment_action_id)
4953 ,0);
4954 --}
4955 END IF; --l_value = 0
4956 --}
4957 END IF; --p_threshold_basis = 'YTD'
4958 --}
4959 END IF; --l_state_tax_flag = 'Y'
4960
4961
4962 IF l_county_tax_flag = 'Y' THEN
4963 --{
4964
4965 IF p_threshold_basis = 'YTD' THEN
4966 --{
4967 OPEN csr_defined_balance_id('County Withheld','_PER_JD_GRE_YTD');
4968 hr_utility.trace('EMJT: Fetching County Withheld_PER_JD_GRE_YTD ');
4969 FETCH csr_defined_balance_id INTO l_defined_balance_id,l_balance_type_id;
4970 CLOSE csr_defined_balance_id;
4971
4972 hr_utility.trace('EMJT: COUNTY l_defined_balance_id -> '|| l_defined_balance_id);
4973 hr_utility.trace('EMJT: COUNTY l_balance_type_id -> '|| l_balance_type_id);
4974
4975 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
4976 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
4977 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
4978 hr_utility.trace('EMJT: p_threshold_basis = YTD');
4979 l_value := NVL(pay_balance_pkg.get_value
4980 (p_defined_balance_id => l_defined_balance_id
4981 ,p_assignment_action_id => p_assignment_action_id)
4982 ,0);
4983
4984 --}
4985 ELSIF p_threshold_basis = 'RTD' THEN
4986 --{
4987 hr_utility.trace('EMJT COUNTY: p_threshold_basis = RTD');
4988 OPEN csr_defined_balance_id('County Withheld','_PER_JD_GRE_RTD');
4989 hr_utility.trace('EMJT: Fetching County Withheld_PER_JD_GRE_RTD ');
4990 FETCH csr_defined_balance_id INTO l_defined_balance_id,l_balance_type_id;
4991 CLOSE csr_defined_balance_id;
4992
4993 hr_utility.trace('EMJT: COUNTY l_defined_balance_id -> '|| l_defined_balance_id);
4994 hr_utility.trace('EMJT: COUNTY l_balance_type_id -> '|| l_balance_type_id);
4995
4996 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
4997 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
4998 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
4999 l_value := NVL(pay_balance_pkg.get_value
5000 (p_defined_balance_id => l_defined_balance_id
5001 ,p_assignment_action_id => p_assignment_action_id)
5002 ,0);
5003 --}
5004 END IF; --p_threshold_basis = 'YTD'
5005
5006 --}
5007 END IF; --l_county_tax_flag = 'Y'
5008
5009 IF l_city_tax_flag = 'Y' THEN
5010 --{
5011
5012 IF p_threshold_basis = 'YTD' THEN
5013 --{
5014 hr_utility.trace('EMJT CITY: p_threshold_basis = YTD');
5015 OPEN csr_defined_balance_id('City Withheld','_PER_JD_GRE_YTD');
5016 hr_utility.trace('EMJT : Fetching City Withheld_PER_JD_GRE_YTD');
5017 FETCH csr_defined_balance_id INTO l_defined_balance_id,l_balance_type_id;
5018 CLOSE csr_defined_balance_id;
5019
5020 hr_utility.trace('EMJT: CITY l_defined_balance_id -> '|| l_defined_balance_id);
5021 hr_utility.trace('EMJT: CITY l_balance_type_id -> '|| l_balance_type_id);
5022
5023 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
5024 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
5025 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
5026
5027 l_value := NVL(pay_balance_pkg.get_value
5028 (p_defined_balance_id => l_defined_balance_id
5029 ,p_assignment_action_id => p_assignment_action_id)
5030 ,0);
5031
5032 --}
5033 ELSIF p_threshold_basis = 'RTD' THEN
5034 --{
5035 hr_utility.trace('EMJT CITY : p_threshold_basis = RTD');
5036
5037 OPEN csr_defined_balance_id('City Withheld','_PER_JD_GRE_RTD');
5038 hr_utility.trace('EMJT CITY: Fetching City Withheld_PER_JD_GRE_RTD');
5039 FETCH csr_defined_balance_id INTO l_defined_balance_id,l_balance_type_id;
5040 CLOSE csr_defined_balance_id;
5041
5042 hr_utility.trace('EMJT: CITY RTD l_defined_balance_id -> '|| l_defined_balance_id);
5043 hr_utility.trace('EMJT: CITY RTD l_balance_type_id -> '|| l_balance_type_id);
5044
5045 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
5046 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id);
5047 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
5048
5049 l_value := NVL(pay_balance_pkg.get_value
5050 (p_defined_balance_id => l_defined_balance_id
5051 ,p_assignment_action_id => p_assignment_action_id)
5052 ,0);
5053
5054 --}
5055 END IF; --p_threshold_basis = 'YTD'
5056 --}
5057 END IF; --l_city_tax_flag = 'Y'
5058
5059 hr_utility.trace('EMJT: l_value from get_jd_tax_balance ->'|| to_char(l_value));
5060 hr_utility.trace('EMJT: ================================================================');
5061 RETURN l_value;
5062
5063 EXCEPTION
5064 WHEN OTHERS THEN
5065 hr_utility.trace('Exception handler');
5066 hr_utility.trace('SQLCODE = ' || SQLCODE);
5067 hr_utility.trace('SQLERRM = ' || SUBSTR(SQLERRM,1,80));
5068 hr_utility.trace('EMJT: Exception OTHERS in get_jd_tax_balance, returning 0');
5069 RETURN 0;
5070 END get_jd_tax_balance;
5071
5072
5073 --Function to get IT threshold hours for a given Jurisdiction
5074 FUNCTION get_jd_level_threshold(p_tax_unit_id NUMBER
5075 ,p_jurisdiction_code VARCHAR2
5076 ,p_jd_level VARCHAR2) RETURN NUMBER AS
5077
5078 --Threshold hours at state level
5079 CURSOR csr_state_rules(p_tax_unit_id IN NUMBER
5080 ,p_state_code IN VARCHAR2) IS
5081 SELECT NVL(org_information2,0)
5082 FROM hr_organization_information hoi,
5083 pay_us_states pus
5084 WHERE hoi.organization_id = p_tax_unit_id
5085 AND hoi.org_information_context = 'State Tax Rules 2'
5086 AND hoi.org_information1 = pus.state_abbrev
5087 AND pus.state_code = p_state_code;
5088
5089 --Threshold hours at Local level
5090 CURSOR csr_local_rules(p_tax_unit_id IN NUMBER
5091 ,p_jurisdiction_code IN VARCHAR2) IS
5092 SELECT NVL(org_information4,0)
5093 FROM hr_organization_information
5094 WHERE organization_id = p_tax_unit_id
5095 AND org_information_context = 'Local Tax Rules'
5096 AND org_information1 = p_jurisdiction_code;
5097
5098 l_state_threshold VARCHAR2(10);
5099 l_local_threshold VARCHAR2(10);
5100
5101 BEGIN
5102
5103 hr_utility.trace('EMJT: ================================================================');
5104 hr_utility.trace('EMJT: In get_jd_level_threshold');
5105 IF p_jd_level = 'STATE' THEN
5106 OPEN csr_state_rules(p_tax_unit_id,
5107 SUBSTR(p_jurisdiction_code,1,2));
5108 FETCH csr_state_rules INTO l_state_threshold;
5109 CLOSE csr_state_rules;
5110 hr_utility.trace('EMJT: SUBSTR(p_jurisdiction_code,1,2) -> ' || SUBSTR(p_jurisdiction_code,1,2));
5111 hr_utility.trace('EMJT: l_state_threshold from get_jd_level_threshold -> '
5112 || l_state_threshold);
5113 hr_utility.trace('EMJT: ================================================================');
5114 RETURN TO_NUMBER(l_state_threshold);
5115
5116 ELSIF p_jd_level = 'COUNTY' THEN
5117 OPEN csr_local_rules(p_tax_unit_id,
5118 substr(p_jurisdiction_code,1,7)||'0000'
5119 );
5120 FETCH csr_local_rules INTO l_local_threshold;
5121 CLOSE csr_local_rules;
5122 hr_utility.trace('EMJT: substr(p_jurisdiction_code,1,7)||''0000'' -> ' || substr(p_jurisdiction_code,1,7)||'0000');
5123 hr_utility.trace('EMJT: l_county_threshold from get_jd_level_threshold -> '
5124 || NVL(l_local_threshold,0));
5125
5126 IF NVL(l_local_threshold,0) = 0 THEN
5127 OPEN csr_state_rules(p_tax_unit_id, SUBSTR(p_jurisdiction_code,1,2));
5128 FETCH csr_state_rules INTO l_state_threshold;
5129 CLOSE csr_state_rules;
5130 hr_utility.trace('EMJT: l_state_threshold from get_jd_level_threshold -> '
5131 || l_state_threshold);
5132 hr_utility.trace('EMJT: ================================================================');
5133 RETURN TO_NUMBER(NVL(l_state_threshold,0));
5134 ELSE
5135 RETURN TO_NUMBER(NVL(l_local_threshold,0));
5136 END IF;
5137
5138
5139 ELSIF p_jd_level = 'CITY' THEN
5140 OPEN csr_local_rules(p_tax_unit_id,
5141 p_jurisdiction_code);
5142 FETCH csr_local_rules INTO l_local_threshold;
5143 CLOSE csr_local_rules;
5144 hr_utility.trace('EMJT: p_jurisdiction_code -> ' || p_jurisdiction_code);
5145 hr_utility.trace('EMJT: l_local_threshold from get_jd_level_threshold -> '
5146 || NVL(l_local_threshold,0));
5147 hr_utility.trace('EMJT: ================================================');
5148 IF NVL(l_local_threshold,0) = 0 THEN
5149 OPEN csr_state_rules(p_tax_unit_id, SUBSTR(p_jurisdiction_code,1,2));
5150 FETCH csr_state_rules INTO l_state_threshold;
5151 CLOSE csr_state_rules;
5152 hr_utility.trace('EMJT: l_state_threshold from get_jd_level_threshold -> '
5153 || l_state_threshold);
5154 hr_utility.trace('EMJT: ================================================================');
5155 RETURN TO_NUMBER(NVL(l_state_threshold,0));
5156 ELSE
5157 RETURN TO_NUMBER(NVL(l_local_threshold,0));
5158 END IF;
5159
5160 END IF;
5161 EXCEPTION
5162 WHEN OTHERS THEN
5163 hr_utility.trace('EMJT: Exception OTHERS in get_jd_level_threshold, returning 0');
5164 RETURN 0;
5165 END get_jd_level_threshold;
5166
5167 --Function to get Informational Hours logged by the assignment for
5168 --each jurisdiction code in the pl table.
5169 FUNCTION get_person_it_hours(p_person_id IN NUMBER
5170 ,p_assignment_id IN NUMBER
5171 ,p_jurisdiction_code IN VARCHAR2
5172 ,p_jd_level IN VARCHAR2 --2,6,11
5173 ,p_threshold_basis IN VARCHAR2 --YTD,RTD
5174 ,p_effective_date IN DATE
5175 ,p_end_date IN DATE) RETURN NUMBER AS
5176
5177 -- Cursor to get all the informational time element entries
5178 -- Jurisdiction Code and Hours screen entry values are retrieved
5179 -- All element entries are considered based on the start date and end date.
5180 -- Hours are summed for each jurisdiction.
5181 -- pev1.screen_entry_value Jurisdiction,
5182 CURSOR csr_element_entries(p_start_date IN DATE,
5183 p_end_date IN DATE,
5184 p_person_id IN NUMBER,
5185 p_assignment_id IN NUMBER,
5186 p_effective_date IN DATE,
5187 p_jurisdiction_code IN VARCHAR2,
5188 p_jd_level IN NUMBER) IS
5189 SELECT SUM(pev2.screen_entry_value) Hours
5190 FROM pay_element_entry_values_f pev1,
5191 pay_element_entry_values_f pev2,
5192 pay_element_entries_f pee,
5193 pay_element_links_f pel,
5194 pay_element_types_f pet,
5195 pay_input_values_f piv1,
5196 pay_input_values_f piv2,
5197 pay_element_type_extra_info extra,
5198 per_assignments_f paf
5199 WHERE extra.information_type = 'PAY_US_INFORMATION_TIME'
5200 AND extra.eei_information1 = 'Y'
5201 AND extra.element_type_id = pet.element_type_id
5202 AND pet.element_type_id = pel.element_type_id
5203 AND pee.effective_start_date BETWEEN pet.effective_start_date
5204 AND pet.effective_end_date
5205 AND pel.element_link_id = pee.element_link_id
5206 AND pee.effective_start_date BETWEEN pel.effective_start_date
5207 AND pel.effective_end_date
5208 AND pee.effective_start_date BETWEEN p_start_date
5209 AND p_end_date
5210 AND paf.assignment_id = pee.assignment_id
5211 AND pee.effective_start_date BETWEEN paf.effective_start_date
5212 AND paf.effective_end_date
5213 AND paf.person_id = p_person_id
5214 AND pee.element_entry_id = pev1.element_entry_id
5215 AND pee.effective_start_date BETWEEN pee.effective_start_date
5216 AND pee.effective_end_date
5217 AND pev1.input_value_id = piv1.input_value_id
5218 AND pee.effective_start_date BETWEEN pev1.effective_start_date
5219 AND pev1.effective_end_date
5220 AND piv1.name = 'Jurisdiction'
5221 AND pee.effective_start_date BETWEEN piv1.effective_start_date
5222 AND piv1.effective_end_date
5223 AND pee.element_entry_id = pev2.element_entry_id
5224 AND pee.effective_start_date BETWEEN pee.effective_start_date
5225 AND pee.effective_end_date
5226 AND pev2.input_value_id = piv2.input_value_id
5227 AND piv2.name = 'Hours'
5228 AND pee.effective_start_date BETWEEN piv2.effective_start_date
5229 AND piv2.effective_end_date
5230 AND SUBSTR(pev1.screen_entry_value,1,p_jd_level)
5231 = SUBSTR(p_jurisdiction_code,1,p_jd_level);
5232
5233 l_start_date DATE;
5234 l_end_date DATE;
5235 l_hours NUMBER;
5236
5237 BEGIN
5238 hr_utility.trace('EMJT: ================================================================');
5239 hr_utility.trace('EMJT: In get_person_it_hours');
5240 hr_utility.trace('EMJT: Jurisdiction Code -> '||p_jurisdiction_code);
5241 hr_utility.trace('EMJT: Threshold Basis -> '||p_threshold_basis);
5242 hr_utility.trace('EMJT: Jurisdiction Level -> '||p_jd_level);
5243 hr_utility.trace('EMJT: Payroll Effective Date -> '||to_char(p_effective_date,'DD-MON-YYYY'));
5244 hr_utility.trace('EMJT: Payroll Period End Date -> '||to_char(p_end_date,'DD-MON-YYYY'));
5245
5246 --p_effective_date - pay_payroll_actions.effective_date;
5247 --p_date_earned - pay_payroll_actions.date_earned;
5248
5249 IF p_threshold_basis = 'YTD' THEN
5250 l_start_date := TRUNC(p_effective_date,'Y');
5251 l_end_date := p_effective_date;
5252 ELSIF p_threshold_basis = 'RTD' THEN
5253 l_start_date := ADD_MONTHS(p_end_date, -12);
5254 l_end_date := p_end_date;
5255 END IF;
5256
5257 hr_utility.trace('EMJT: p_effective_date -> '|| p_effective_date );
5258 hr_utility.trace('EMJT: l_start_date -> '|| l_start_date );
5259 hr_utility.trace('EMJT: l_end_date -> '|| l_end_date );
5260
5261 OPEN csr_element_entries(l_start_date
5262 ,l_end_date
5263 ,p_person_id
5264 ,p_assignment_id
5265 ,p_effective_date
5266 ,p_jurisdiction_code
5267 ,p_jd_level );
5268
5269 FETCH csr_element_entries INTO l_hours;
5270 CLOSE csr_element_entries;
5271
5272 hr_utility.trace('EMJT: l_hours -> '|| to_char(l_hours ) );
5273 hr_utility.trace('EMJT: ========================================================');
5274
5275 RETURN l_hours;
5276
5277 END get_person_it_hours;
5278 --
5279 -- This function would be used for fetching percentage to be used STATE and
5280 -- COUNTY level percentage to be used for distributing wages over different
5281 -- jurisdictions when assignment is configured to process information hours
5282 --
5283 FUNCTION get_it_jd_percent(p_jurisdiction_code VARCHAR2 -- parameter
5284 ,p_jd_level VARCHAR2 -- parameter
5285 ,p_hours_to_accumulate OUT nocopy NUMBER -- parameter
5286 ,p_wages_to_accrue_flag OUT nocopy VARCHAR2 -- parameter
5287 )
5288 RETURN NUMBER
5289 IS
5290 l_jd_level number;
5291 l_pad number;
5292 l_entry_jd number;
5293 l_temp_jd number;
5294 l_percentage number;
5295 l_return number;
5296 BEGIN
5297 --{
5298 hr_utility.trace('EMJT: ================================================================');
5299 hr_utility.trace('EMJT: IN get_it_jd_percent ') ;
5300 --
5301 -- This flag is set to Y when assignment is configured to process information
5302 -- hours for deriving the percentage of wages to be distributed over various
5303 -- jurisidictions person worked during the payroll period
5304 --
5305 hr_utility.trace('EMJT: get_it_jd_percent Jurisdiction Level = ' || p_jd_level) ;
5306 hr_utility.trace('EMJT: get_it_jd_percent Jurisdiction Code = ' || p_jurisdiction_code) ;
5307 IF p_jd_level = 'COUNTY' THEN
5308 l_jd_level := 5;
5309 ELSIF p_jd_level = 'STATE' THEN
5310 l_jd_level := 2;
5311 END IF;
5312
5313 IF SUBSTR(p_jurisdiction_code,1,1) = 0 THEN
5314 l_pad := 8;
5315 l_jd_level := l_jd_level - 1;
5316 ELSE
5317 l_pad := 9;
5318 END IF;
5319
5320 hr_utility.trace('EMJT: get_it_jd_percent l_pad = ' || to_char(l_pad)) ;
5321 hr_utility.trace('EMJT: get_it_jd_percent l_jd_level = ' || to_char(l_jd_level)) ;
5322
5323 l_entry_jd := TO_NUMBER(SUBSTR(p_jurisdiction_code,1,2) ||
5324 SUBSTR(p_jurisdiction_code,4,3) ||
5325 SUBSTR(p_jurisdiction_code,8,4) );
5326
5327 l_temp_jd := RPAD(SUBSTR(l_entry_jd,1,l_jd_level),l_pad,0);
5328
5329 hr_utility.trace('EMJT: get_it_jd_percent l_temp_jd = ' || to_char(l_temp_jd)) ;
5330
5331 --Fetch the percentage stored for the State
5332 --
5333 IF p_jd_level = 'STATE' THEN
5334 --{
5335 IF jd_codes_tbl_state.EXISTS(TO_NUMBER(l_temp_jd))
5336 THEN
5337 l_percentage := jd_codes_tbl_state(TO_NUMBER(l_temp_jd)).percentage;
5338 ELSE
5339 l_percentage := 0;
5340 END IF;
5341 --} end of p_jd_level = 'STATE'
5342 ELSIF p_jd_level = 'COUNTY' THEN
5343 --{
5344 IF jd_codes_tbl_county.EXISTS(TO_NUMBER(l_temp_jd))
5345 THEN
5346 l_percentage := jd_codes_tbl_county(TO_NUMBER(l_temp_jd)).percentage;
5347 ELSE
5348 l_percentage := 0;
5349 END IF;
5350 --}end of p_jd_level = 'COUNTY'
5351 /*Bug#6598477 begins*/
5352 ELSIF p_jd_level = 'CITY' THEN
5353 --{
5354 IF jurisdiction_codes_tbl.EXISTS(TO_NUMBER(l_temp_jd))
5355 THEN
5356 l_percentage := jurisdiction_codes_tbl(TO_NUMBER(l_temp_jd)).percentage;
5357 ELSE
5358 l_percentage := 0;
5359 END IF;
5360 --}end of p_jd_level = 'CITY'
5361 /*Bug#6598477 ends*/
5362 END IF;
5363 hr_utility.trace('EMJT: get_it_jd_percent Percentage Derived for Jurisdiction = '
5364 || to_char(l_percentage)) ;
5365 -- Dummy values assigned to the OUT variables
5366 p_hours_to_accumulate := 0;
5367 p_wages_to_accrue_flag := 'AIHW';
5368 --
5369 -- Percentage returned based on the jurisidction level and jurisdiction code passed
5370 --
5371 hr_utility.trace('EMJT: l_percentage from get_it_jd_percent ->'|| to_char(l_percentage));
5372 hr_utility.trace('EMJT: ================================================================');
5373 RETURN l_percentage;
5374 EXCEPTION
5375 WHEN OTHERS THEN
5376 hr_utility.trace('EMJT: Exception OTHERS in get_it_jd_percent, returning 0');
5377 RETURN 0;
5378 --}
5379 END get_it_jd_percent;
5380
5381 function across_calendar_years(p_payroll_action_id in number)
5382 return varchar2 is
5383
5384 l_date_earned date;
5385 l_date_paid date;
5386 l_check_years varchar2(1);
5387
5388 cursor csr_get_dates is
5389 select effective_date,
5390 date_earned
5391 from pay_payroll_actions
5392 where payroll_action_id = p_payroll_action_id;
5393
5394 begin
5395
5396 open csr_get_dates;
5397 fetch csr_get_dates
5398 into l_date_paid,
5399 l_date_earned;
5400 close csr_get_dates;
5401
5402 if to_char(l_date_paid,'YYYY') = to_char(l_date_earned,'YYYY') then
5403 l_check_years := 'N';
5404 else
5405 l_check_years := 'Y';
5406 end if;
5407
5408 return l_check_years;
5409
5410 end across_calendar_years;
5411
5412
5413 FUNCTION get_work_state (p_jurisdiction_code in varchar2)
5414 RETURN varchar2 IS
5415
5416 l_exists varchar2(2);
5417 i number;
5418 BEGIN
5419 l_exists := 'N';
5420
5421 IF jurisdiction_codes_tbl.COUNT > 0 THEN
5422
5423 i := jurisdiction_codes_tbl.FIRST; -- get subscript of first element
5424 WHILE i IS NOT NULL LOOP
5425
5426 hr_utility.trace('plsql table='||substr(jurisdiction_codes_tbl(i).jurisdiction_code,1,2));
5427 IF p_jurisdiction_code = substr(jurisdiction_codes_tbl(i).jurisdiction_code,1,2)
5428 THEN
5429 l_exists := 'Y';
5430 EXIT;
5431 END IF;
5432
5433 i := jurisdiction_codes_tbl.NEXT(i); -- get subscript of next element
5434 END LOOP;
5435
5436 END IF;
5437
5438 RETURN l_exists;
5439
5440 END get_work_state;
5441
5442 --Function to return the SUI Wage Limits.
5443 FUNCTION get_jit_data(p_jurisdiction_code IN VARCHAR2
5444 ,p_date_earned IN DATE
5445 ,p_jit_type IN VARCHAR2)
5446 RETURN NUMBER IS
5447
5448 CURSOR csr_sui_er_wage_limit(p_jurisdiction_code IN VARCHAR2,p_date_earned IN DATE) IS
5449 SELECT NVL(sui_er_wage_limit,0)
5450 FROM pay_us_state_tax_info_f
5451 WHERE state_code = SUBSTR(p_jurisdiction_code,1,2)
5452 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
5453
5454 l_sui_er_wage_limit pay_us_state_tax_info_f.sui_er_wage_limit%TYPE;
5455
5456 CURSOR csr_sui_ee_wage_limit(p_jurisdiction_code IN VARCHAR2,p_date_earned IN DATE) IS
5457 SELECT NVL(sui_ee_wage_limit,0)
5458 FROM pay_us_state_tax_info_f
5459 WHERE state_code = SUBSTR(p_jurisdiction_code,1,2)
5460 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
5461
5462 l_sui_ee_wage_limit pay_us_state_tax_info_f.sui_ee_wage_limit%TYPE;
5463
5464 CURSOR csr_supp_calc_method(p_jurisdiction_code IN VARCHAR2,p_date_earned IN DATE) IS
5465 SELECT NVL(sta_information18,' ')
5466 FROM pay_us_state_tax_info_f
5467 WHERE state_code = SUBSTR(p_jurisdiction_code,1,2)
5468 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
5469
5470 l_supp_calc_meth pay_us_state_tax_info_f.sta_information18%TYPE;
5471
5472 l_return_value NUMBER;
5473
5474 BEGIN
5475
5476 hr_utility.trace('hr_us_ff_udf1.get_jit_date');
5477 hr_utility.trace('p_jurisdiction_code --> '|| p_jurisdiction_code);
5478 hr_utility.trace('p_date_earned --> '|| p_date_earned);
5479 hr_utility.trace('p_jit_type --> '|| p_jit_type);
5480
5481 IF p_jit_type = 'SUI_ER_WAGE_LIMIT' THEN
5482 OPEN csr_sui_er_wage_limit(p_jurisdiction_code,p_date_earned);
5483 FETCH csr_sui_er_wage_limit INTO l_sui_er_wage_limit;
5484 CLOSE csr_sui_er_wage_limit;
5485 l_return_value := l_sui_er_wage_limit;
5486 hr_utility.trace('l_sui_er_wage_limit --> '|| l_sui_er_wage_limit);
5487
5488 END IF; /* SUI_ER_WAGE_LIMIT */
5489
5490 IF p_jit_type = 'SUI_EE_WAGE_LIMIT' THEN
5491
5492 OPEN csr_sui_ee_wage_limit(p_jurisdiction_code,p_date_earned);
5493 FETCH csr_sui_ee_wage_limit INTO l_sui_ee_wage_limit;
5494 CLOSE csr_sui_ee_wage_limit;
5495 l_return_value := l_sui_ee_wage_limit;
5496 hr_utility.trace('l_sui_ee_wage_limit --> '|| l_sui_ee_wage_limit);
5497
5498 END IF; /* SUI_EE_WAGE_LIMIT */
5499
5500 IF p_jit_type = 'DEFAULT_SUPP_CALC_METH' THEN
5501
5502 OPEN csr_supp_calc_method(p_jurisdiction_code,p_date_earned);
5503 FETCH csr_supp_calc_method INTO l_supp_calc_meth;
5504 CLOSE csr_supp_calc_method;
5505 l_return_value := l_supp_calc_meth;
5506 hr_utility.trace('DEFAULT_SUPP_CALC_METH --> '|| l_supp_calc_meth);
5507 END IF; /* DEFAULT_SUPP_CALC_METH */
5508
5509 hr_utility.trace('l_return_value --> '|| l_return_value);
5510 RETURN l_return_value;
5511
5512 END get_jit_data;
5513
5514 FUNCTION get_rs_jd (p_assignment_id IN NUMBER,
5515 p_date_earned IN DATE)
5516 RETURN VARCHAR2 IS
5517
5518 CURSOR c_override_jd (p_assignment_id IN NUMBER,p_date_earned IN DATE) IS
5519 SELECT puc.state_code || '-' || puc.county_code || '-' ||pucty.city_code
5520 FROM pay_us_counties puc,
5521 pay_us_states pus,
5522 pay_us_city_names pucty,
5523 per_addresses pa,
5524 per_assignments_f paf
5525 WHERE paf.assignment_id = p_assignment_id
5526 AND paf.person_id = pa.person_id
5527 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date
5528 AND paf.primary_flag = 'Y'
5529 AND paf.assignment_type = 'E'
5530 AND pa.primary_flag = 'Y'
5531 AND pa.country = 'US'
5532 AND pa.style = 'US'
5533 AND TO_DATE('01-01-'||TO_CHAR(p_date_earned,'YYYY'), 'DD-MM-YYYY') BETWEEN
5534 pa.date_from AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'))
5535 AND pus.state_abbrev = pa.add_information17 --override state
5536 AND puc.state_code = pus.state_code
5537 AND puc.county_name = pa.add_information19 --Override County
5538 AND pucty.state_code = pus.state_code
5539 AND pucty.county_code = puc.county_code
5540 AND pucty.city_name = pa.add_information18; -- Override City.
5541
5542
5543 CURSOR c_jd (p_assignment_id IN NUMBER,p_date_earned IN DATE) IS
5544 SELECT puc.state_code || '-' || puc.county_code || '-' ||pucty.city_code
5545 FROM pay_us_counties puc,
5546 pay_us_states pus,
5547 pay_us_city_names pucty,
5548 per_addresses pa,
5549 per_assignments_f paf
5550 WHERE paf.assignment_id = p_assignment_id
5551 AND paf.person_id = pa.person_id
5552 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date
5553 AND paf.primary_flag = 'Y'
5554 AND paf.assignment_type = 'E'
5555 AND pa.primary_flag = 'Y'
5556 AND pa.country = 'US'
5557 AND pa.style = 'US'
5558 AND TO_DATE('01-01-'||TO_CHAR(p_date_earned,'YYYY'), 'DD-MM-YYYY') BETWEEN
5559 pa.date_from AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'))
5560 AND pus.state_abbrev = pa.region_2 --Regular state
5561 AND puc.state_code = pus.state_code
5562 AND puc.county_name = pa.region_1 --Regular County
5563 AND pucty.state_code = pus.state_code
5564 AND pucty.county_code = puc.county_code
5565 AND pucty.city_name = pa.town_or_city; -- Regular City.
5566
5567
5568 l_rs_jd VARCHAR2(200);
5569
5570 BEGIN
5571
5572 OPEN c_override_jd(p_assignment_id,p_date_earned);
5573 FETCH c_override_jd INTO l_rs_jd;
5574 IF c_override_jd%NOTFOUND THEN
5575
5576 OPEN c_jd(p_assignment_id,p_date_earned);
5577 FETCH c_jd INTO l_rs_jd;
5578 IF c_jd%NOTFOUND THEN
5579 l_rs_jd := NULL;
5580 END IF;
5581 CLOSE c_jd;
5582
5583 END IF;
5584 CLOSE c_override_jd;
5585
5586 hr_utility.trace('hr_us_ff_udf1.get_rs_jd');
5587 --hr_utility.trace('p_jurisdiction_code --> '|| p_jurisdiction_code);
5588 hr_utility.trace('p_date_earned --> '|| p_date_earned);
5589 --hr_utility.trace('p_jit_type --> '|| p_jit_type);
5590
5591 hr_utility.trace('l_rs_jd --> '|| l_rs_jd );
5592 --dbms_output.put_line('l_rs_jd --> '|| l_rs_jd);
5593
5594 RETURN l_rs_jd;
5595
5596
5597 END get_rs_jd;
5598
5599 FUNCTION get_wk_jd(p_assignment_id IN NUMBER,
5600 p_date_earned IN DATE,
5601 p_jurisdiction_code IN VARCHAR2)
5602 RETURN VARCHAR2 IS
5603
5604 CURSOR c_override_wk_jd (p_assignment_id IN NUMBER,
5605 p_date_earned IN DATE,
5606 p_jurisdiction_code IN VARCHAR2) IS
5607 SELECT puc.state_code || '-' || puc.county_code || '-' ||pucty.city_code
5608 FROM pay_us_counties puc,
5609 pay_us_states pus,
5610 pay_us_city_names pucty,
5611 hr_locations hl,
5612 per_assignments_f paf
5613 WHERE paf.assignment_id = p_assignment_id
5614 AND TO_DATE('01-01-'||TO_CHAR(p_date_earned,'YYYY'), 'DD-MM-YYYY') BETWEEN
5615 paf.effective_start_date AND paf.effective_end_date
5616 AND paf.location_id = hl.location_id
5617 AND hl.loc_information17 = pus.state_abbrev --override state
5618 AND puc.state_code = pus.state_code
5619 AND hl.loc_information19 = puc.county_name --override county
5620 AND puc.state_code = SUBSTR(p_jurisdiction_code, 1, 2)
5621 AND puc.county_code = SUBSTR(p_jurisdiction_code, 4, 3)
5622 AND hl.loc_information18 = pucty.city_name --override city
5623 AND pucty.state_code = SUBSTR(p_jurisdiction_code, 1, 2)
5624 AND pucty.county_code = SUBSTR(p_jurisdiction_code, 4, 3)
5625 AND pucty.city_code = SUBSTR(p_jurisdiction_code, 8, 4);
5626
5627 CURSOR c_reg_wk_jd (p_assignment_id IN NUMBER,
5628 p_date_earned IN DATE,
5629 p_jurisdiction_code IN VARCHAR2) IS
5630 SELECT puc.state_code || '-' || puc.county_code || '-' ||pucty.city_code
5631 FROM pay_us_counties puc,
5632 pay_us_states pus,
5633 pay_us_city_names pucty,
5634 hr_locations hl,
5635 per_assignments_f paf
5636 WHERE paf.assignment_id = p_assignment_id
5637 AND TO_DATE('01-01-'||TO_CHAR(p_date_earned,'YYYY'), 'DD-MM-YYYY') BETWEEN
5638 paf.effective_start_date AND paf.effective_end_date
5639 AND paf.location_id = hl.location_id
5640 AND hl.region_2 = pus.state_abbrev --reg state
5641 AND puc.state_code = pus.state_code
5642 AND hl.region_1 = puc.county_name --reg county
5643 AND puc.state_code = SUBSTR(p_jurisdiction_code, 1, 2)
5644 AND puc.county_code = SUBSTR(p_jurisdiction_code, 4, 3)
5645 AND hl.town_or_city = pucty.city_name --reg city
5646 AND pucty.state_code = SUBSTR(p_jurisdiction_code, 1, 2)
5647 AND pucty.county_code = SUBSTR(p_jurisdiction_code, 4, 3)
5648 AND pucty.city_code = SUBSTR(p_jurisdiction_code, 8, 4);
5649
5650 l_wk_jd VARCHAR2(200);
5651
5652 BEGIN
5653
5654
5655 OPEN c_override_wk_jd (p_assignment_id,p_date_earned,p_jurisdiction_code);
5656 FETCH c_override_wk_jd INTO l_wk_jd;
5657 IF c_override_wk_jd%NOTFOUND THEN
5658 OPEN c_reg_wk_jd(p_assignment_id,p_date_earned,p_jurisdiction_code);
5659 FETCH c_reg_wk_jd INTO l_wk_jd;
5660 IF c_reg_wk_jd%NOTFOUND THEN
5661 l_wk_jd := NULL;
5662 END IF;
5663 CLOSE c_reg_wk_jd;
5664 END IF;
5665 CLOSE c_override_wk_jd;
5666
5667
5668 hr_utility.trace('hr_us_ff_udf1.get_wk_jd');
5669 hr_utility.trace('p_jurisdiction_code --> '|| p_jurisdiction_code);
5670 hr_utility.trace('p_date_earned --> '|| p_date_earned);
5671 --hr_utility.trace('p_jit_type --> '|| p_jit_type);
5672
5673 hr_utility.trace('l_wk_jd --> '|| l_wk_jd );
5674
5675 --dbms_output.put_line('l_wk_jd --> '|| l_wk_jd);
5676
5677 RETURN l_wk_jd;
5678
5679 END get_wk_jd;
5680
5681
5682
5683 END hr_us_ff_udf1;