[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_TAX_FUNCTIONS
Source
1 PACKAGE BODY PAY_MX_TAX_FUNCTIONS as
2 /* $Header: pymxtxfn.pkb 120.26.12010000.16 2008/12/02 16:21:26 sivanara ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2004, Oracle India Pvt. Ltd., Hyderabad *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_mx_tax_functions
21
22 Description : This package contains various formula function definitions
23 for Mexican tax calculation.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- --------- ------ ------- -------------------------------
29 23-SEP-2004 sdahiya 115.0 Created.
30 29-OCT-2004 sdahiya 115.1 Extended support for partially
31 subject earnings.
32 09-NOV-2004 sdahiya 115.2 Added GET_SS_QUOTA_INFO function
33 21-JAN-2005 ardsouza 115.3 4129001 hr_mx_utility.get_gre_from_location
34 call modified to pass BG.
35 21-FEB-2005 sdahiya 115.4 - Modified get_previous_period_bal
36 to fetch values for any defined
37 balance passed.
38 - Renamed
39 get_isr_partial_subj_earnings to
40 get_partial_subj_earnings and
41 modified it to fetch subject wages
42 for state tax in addition to ISR.
43 02-MAR-2005 sdahiya 115.5 Fixed GSCC warning.
44 10-MAR-2005 sdahiya 115.6 Renamed GET_SS_QUOTA_INFO to
45 GET_MX_TAX_INFO.
46 12-MAR-2005 ardsouza 115.7 Added GET_MX_EE_HEAD_COUNT and
47 GET_MX_STATE_TAX_RULES functions.
48 18-APR-2005 sdahiya 115.8 Modified GET_PARTIAL_SUBJ_EARNINGS
49 to accept P_CTX_ELEMENT_TYPE_ID as a
50 parameter.
51
52 4283490 Modified function
53 GET_PREVIOUS_PERIOD_BAL so that it
54 returns zero if there is no payroll
55 action in previous period.
56 26-APR-2005 sdahiya 115.7 Created global cache g_isr_balances.
57 27-JUN-2005 ardsouza 115.10 4387751 Added 2 overloaded versions of
58 GET_PARTIAL_SUBJ_EARNINGS - one that
59 accepts YTD earnings and one both.
60 Added GET_SUBJ_EARNINGS_FOR_PERIOD,
61 which accepts PTD earnings.
62 26-JUL-2005 ardsouza 115.11 4510115 Handled NO_DATA_FOUND exception if
63 cache is empty, while defaulting.
64 25-OCT-2005 sdahiya 115.12 4656174 Work risk insurance premium should
65 be fetched from database even
66 if it is already cached.
67 06-DEC-2005 vpandya 115.13 Added following functions:
68 - CALCULATE_ISR_TAX
69 06-JAN-2006 vpandya 115.14 Using get_seniority function for
70 tax calculation for Amends. Also
71 added convert_into_monthly_salary.
72 13-JAN-2006 ardsouza 115.15 4950628 Corrected Amends exempt amount
73 calculation.
74 29-MAR-2006 ardsouza 115.16 Modified to handle cases where
75 P_CTX_ASSIGNMENT_ACTION_ID is a
76 temporary action.
77 03-Jul-2006 vpandya 115.17 5360802 Modified CALCULATE_ISR_TAX,
78 added condition p_credit_to_salary
79 is zero when Tax Adjustment is run.
80 14-Jul-2006 sukukuma 115.18 Added following functions:
81 - CHECK_EE_SAL_CRITERIA
82 - CHECK_EE_EMPLOYMENT_CRITERIA
83 - IS_ASG_EXEMPT_FROM_ISR
84 - IS_PER_EXEMPT_FROM_ADJ
85 20-Jul-2006 sukukuma 115.19 Modified following functions:
86 - CHECK_EE_SAL_CRITERIA
87 14-Nov-2006 sdahiya 115.20 Added overloaded version of
88 calculate_isr_tax to support subsidy
89 calculation for Article 141.
90 12-Jan-2007 sdahiya 115.21 5757873 Adjustment start and end dates
91 should be fetched from legal
92 employer instead of GRE.
93 12-Jan-2007 vpandya 115.22 5757873 Changed CHECK_EE_EMPLOYMENT_CRITERIA
94 getting latest hire date.
95 15-Jan-2007 vpandya 115.23 5762654 Changed CHECK_EE_EMPLOYMENT_CRITERIA
96 getting actual_termination_date and
97 returning N if it is less then
98 adjustment end date.
99 15-Oct-2007 srikared 115.24 6437992 Added New functions GET_MIN_WAGE,
100 GET_MX_ECON_ZONE
101
102 25-Oct-2007 vmehta 115.27 6519803 Delete table for balances in
103 GET_PARTIAL_SUBJ_EARNINGS before
104 calculating taxes other than ISR
105 21-Nov-2007 prechand 115.28 6606767 Changed the function to_number
106 in the function CALCULATE_ISR_TAX to
107 Fnd_Number.Canonical_to_number
108 12-Dec-2007 nragavar 115.29 6487007 ISR 2008 changes
109 29-Jan-2008 nragavar 115.33 6779706 Subsidy for Empl paid was getting added
110 where, not required.
111 30-Jan-2008 nragavar 115.34 6782264 changes to get_table_value
112 21-Feb-2008 sivanara 115.35 6821377 Changes made to calculte_ISR_TAX to
113 considered if emp is hired in mid of
114 pay period. Also changes to function
115 CONVERT_INTO_MONTHLY_AVG_SAL
116 24-Mar-2008 sivanara 115.36 6852627 Included ISR proration logic
117 03-Apr-2008 sivanara 115.38 6926777 Included error message
118 PAY_MX_INVALID_ISR_NON_WRK_DAY for
119 ISR proration.
120 05-May-2008 sivanara 115.39 7027010 Incldued logic for ISR Subject proration
121 06-May-2008 sivanara 115.40 7116850 Revert the proration logic as we get
122 the prorated subject amount for p_subject_amount.
123 6933775 Included logic for projection of prorated
124 ISR subject amount
125 Added code in procedure CALCULATE_ISR_TAX
126 to consider the first paid period to the
127 employee
128 13-Jun-2008 nragavar 115.42 7047220 7047357- leapfroged from 115.40 to 115.42.
129 this includes changes in 115.41. changes
130 to cursor csr_get_min_wage.
131 03-Jul-2008 sivanara 115.43 7208623 leapfroged again 115.36 to115.43.
132 For this version the package header
133 version is pymxtxfn.pkh 115.19.
134 This version does not include any
135 part of isr proration fixes.
136 Version 115.42 to 115.44(whih has
137 ISR proration fix) arcsed
138 on top of this will be done.
139 03-Jul-2008 sivanara 115.44 leapfroged from Version 115.42 to 115.44
140 which has the ISR proration fix that was
141 included in version 115.43
142 15-jul-2008 sivanara 115.45 7260970 For ISR Proration added logic to consider the
143 day factor for calculating the total subject
144 amount from the given prorated amount.
145 115.46 7242481 ISR proration should be considered only for
146 ARTICLE 113 calculation method.
147 04-Aug-2008 nragavar 115.47 7042174 Done changes as part of 10 day payroll frequency.
148 04-Aug-2008 sjawid 115.50 7445486 No need to calculate 'credit to salary
149 for ISR Tax calculation as per Article142.
150 02-Dec-2008 sivanara 115.51 7602236 Added logic to CHECK_EE_EMPLOYMENT_CRITERIA
151 7604298 to consider test case for RE-HIREed employee
152 in the next day immediately after termination.
153
154 *****************************************************************************/
155
156 TYPE g_leg_record IS RECORD (
157 effective_start_date pay_mx_legislation_info_f.effective_start_date%TYPE,
158 effective_end_date pay_mx_legislation_info_f.effective_end_date%TYPE,
159 jurisdiction_code pay_mx_legislation_info_f.jurisdiction_code%TYPE,
160 legislation_info_type pay_mx_legislation_info_f.legislation_info_type%TYPE,
161 legislation_info1 pay_mx_legislation_info_f.legislation_info1%TYPE,
162 legislation_info2 pay_mx_legislation_info_f.legislation_info2%TYPE,
163 legislation_info3 pay_mx_legislation_info_f.legislation_info3%TYPE,
164 legislation_info4 pay_mx_legislation_info_f.legislation_info4%TYPE,
165 legislation_info5 pay_mx_legislation_info_f.legislation_info5%TYPE,
166 legislation_info6 pay_mx_legislation_info_f.legislation_info6%TYPE);
167
168 TYPE g_isr_balances_rec IS RECORD (
169 assignment_action_id pay_assignment_actions.assignment_action_id%TYPE,
170 earnings_amt NUMBER,
171 exempt_amt NUMBER);
172
173 TYPE g_leg_table IS TABLE OF g_leg_record INDEX BY BINARY_INTEGER;
174 TYPE g_isr_balances_table IS TABLE OF g_isr_balances_rec INDEX BY BINARY_INTEGER;
175
176 g_proc_name VARCHAR2(50);
177 g_debug BOOLEAN;
178 g_isr_balances g_isr_balances_table;
179 g_pay_mx_legislation_info_f g_leg_table;
180
181
182 /****************************************************************************
183 Name : HR_UTILITY_TRACE
184 Description : This function prints debug messages during diagnostics mode.
185 *****************************************************************************/
186
187 PROCEDURE HR_UTILITY_TRACE(trc_data varchar2) IS
188 BEGIN
189 IF g_debug THEN
190 hr_utility.trace(trc_data);
191 END IF;
192 END HR_UTILITY_TRACE;
193
194
195 /****************************************************************************
196 Name : GET_DEF_BAL_ID
197 Description : Function to get the defined_balance_id from a DBI name.
198 *****************************************************************************/
199
200 FUNCTION GET_DEF_BAL_ID
201 (
202 P_ENTITY_NAME VARCHAR2
203 ) RETURN NUMBER AS
204 l_defined_balance_id NUMBER;
205 l_proc_name VARCHAR2(100);
206 BEGIN
207 l_proc_name := g_proc_name ||'GET_DEF_BAL_ID';
208 hr_utility_trace('Entering '||l_proc_name);
209 SELECT creator_id
210 INTO l_defined_balance_id
211 FROM ff_user_entities
212 WHERE user_entity_name = p_entity_name
213 AND legislation_code = 'MX'
214 AND creator_type = 'B';
215
216 RETURN (l_defined_balance_id);
217 END GET_DEF_BAL_ID;
218
219
220 /****************************************************************************
221 Name : GET_RANGE_BASIS_VALUE
222 Description : This function takes MW/GMW/SAL as parameter and returns the
223 numerical values associated.
224 *****************************************************************************/
225 FUNCTION GET_RANGE_BASIS_VALUE
226 (
227 P_RANGE_BASIS VARCHAR2,
228 P_DAILY_SALARY NUMBER,
229 P_ASACT_ID NUMBER
230 ) RETURN NUMBER AS
231
232 CURSOR csr_get_asg_info IS
233 SELECT paf.soft_coding_keyflex_id,
234 paf.location_id,
235 ppa.effective_date,
236 paf.business_group_id
237 FROM per_assignments_f paf,
238 pay_assignment_actions paa,
239 pay_payroll_actions ppa
240 WHERE paf.assignment_id = paa.assignment_id
241 AND paa.payroll_action_id = ppa.payroll_action_id
242 AND paa.assignment_action_id = p_asact_id
243 AND ppa.effective_date BETWEEN paf.effective_start_date
244 AND paf.effective_end_date;
245
246 CURSOR csr_get_asg_info_temp IS
247 SELECT paf.soft_coding_keyflex_id,
248 paf.location_id,
249 ppa.effective_date,
250 paf.business_group_id
251 FROM per_assignments_f paf,
252 pay_temp_object_actions ptoa,
253 pay_payroll_actions ppa
254 WHERE paf.assignment_id = ptoa.object_id
255 AND ptoa.payroll_action_id = ppa.payroll_action_id
256 AND ptoa.object_action_id = p_asact_id
257 AND ptoa.object_type = 'ASG'
258 AND ppa.effective_date BETWEEN paf.effective_start_date
259 AND paf.effective_end_date;
260
261 CURSOR csr_get_min_wage (p_gre_id NUMBER, p_effective_date DATE) IS
262 SELECT fnd_number.canonical_to_number(plif.legislation_info2)
263 FROM pay_mx_legislation_info_f plif,
264 hr_organization_units hou,
265 hr_organization_information hoi
266 WHERE hou.organization_id = hoi.organization_id
267 AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
268 AND (DECODE (UPPER(p_range_basis),
269 'MW', 'MW'||hoi.org_information7) = plif.legislation_info1 OR
270 p_range_basis = plif.legislation_info1)
271 AND hou.organization_id = p_gre_id
272 AND plif.legislation_info_type = 'MX Minimum Wage Information'
273 AND p_effective_date BETWEEN hou.date_from
274 AND NVL(hou.date_to, hr_general.end_of_time)
275 AND p_effective_date BETWEEN plif.effective_start_date
276 AND plif.effective_end_date;
277
278 l_min_wage NUMBER;
279 l_scl_id NUMBER;
280 l_gre_id hr_organization_units.organization_id%type;
281 l_location_id hr_locations.location_id%type;
282 l_business_group_id NUMBER;
283 l_effective_date DATE;
284 l_is_ambiguous BOOLEAN;
285 l_missing_gre BOOLEAN;
286 l_proc_name VARCHAR2(100);
287
288 BEGIN
289 l_proc_name := g_proc_name ||'GET_RANGE_BASIS_VALUE';
290 hr_utility_trace('Entering '||l_proc_name);
291
292 IF p_range_basis = 'SAL' THEN
293
294 RETURN (p_daily_salary);
295
296 END IF;
297
298 IF g_temp_object_actions THEN
299 OPEN csr_get_asg_info_temp;
300 FETCH csr_get_asg_info_temp INTO l_scl_id,
301 l_location_id,
302 l_effective_date,
303 l_business_group_id;
304 CLOSE csr_get_asg_info_temp;
305
306 ELSE
307 OPEN csr_get_asg_info;
308 FETCH csr_get_asg_info INTO l_scl_id,
309 l_location_id,
310 l_effective_date,
311 l_business_group_id;
312 CLOSE csr_get_asg_info;
313
314 END IF;
315
316 l_gre_id := hr_mx_utility.get_gre_from_scl (l_scl_id);
317
318 hr_utility_trace('GRE obtained from SCL = "'||l_gre_id||'"');
319
320 IF l_gre_id is null THEN
321 l_gre_id := hr_mx_utility.get_gre_from_location(
322 l_location_id,
323 l_business_group_id, -- Bug 4129001
324 l_effective_date,
325 l_is_ambiguous,
326 l_missing_gre);
327 hr_utility_trace('GRE obtained from location = "'||l_gre_id||'"');
328 END IF;
329 /* Obtain (General) Minimum Wage */
330 OPEN csr_get_min_wage (l_gre_id, l_effective_date);
331 FETCH csr_get_min_wage INTO l_min_wage;
332 CLOSE csr_get_min_wage;
333 hr_utility_trace('(General) Minimum wage = '||l_min_wage);
334
335 hr_utility_trace('Leaving '||l_proc_name);
336 RETURN (l_min_wage);
337
338 END GET_RANGE_BASIS_VALUE;
339
340
341 --****************************************************************************
342 -- Name : GET_PARTIAL_SUBJ_EARNINGS
343 -- Description : This function calls another overloaded function, which returns
344 -- the portion of earnings that are partially subject to State
345 -- Tax and both fully and partially subject to ISR.
346 -- Both the YTD and PTD Earnings are defaulted to the
347 -- secondary classification earnings for the current run, which is
348 -- maintained in the PL-SQL table.
349 --****************************************************************************
350 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
351 (
352 P_CTX_EFFECTIVE_DATE DATE,
353 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
354 P_CTX_BUSINESS_GROUP_ID NUMBER,
355 P_CTX_JURISDICTION_CODE VARCHAR2,
356 P_CTX_ELEMENT_TYPE_ID NUMBER,
357 P_TAX_TYPE VARCHAR2,
358 P_EARNINGS_AMT NUMBER,
359 P_GROSS_EARNINGS NUMBER,
360 P_DAILY_SALARY NUMBER,
361 P_CLASSIFICATION_NAME VARCHAR2
362 )RETURN NUMBER AS
363
364 CURSOR get_ele_class IS
365 SELECT pec1.classification_id,
366 pec1.classification_name
367 FROM pay_element_classifications pec,
368 pay_element_classifications pec1, -- Secondary classification
369 pay_element_types_f pet,
370 pay_sub_classification_rules_f psr
371 WHERE pet.classification_id = pec.classification_id
372 AND pec.classification_id = pec1.parent_classification_id
373 AND pet.element_type_id = psr.element_type_id
374 AND psr.classification_id = pec1.classification_id
375 AND p_ctx_effective_date BETWEEN pet.effective_start_date
376 AND pet.effective_end_date
377 AND p_ctx_effective_date BETWEEN psr.effective_start_date
378 AND psr.effective_end_date
379 AND pet.element_type_id = p_ctx_element_type_id
380 AND pec.legislation_code = 'MX'
381 AND pec.classification_name <> 'Employer Liabilities'
382 AND pec.business_group_id IS NULL
383 AND pec1.legislation_code = 'MX'
384 AND pec1.business_group_id IS NULL
385 AND p_tax_type = 'ISR'
386 UNION
387 SELECT pec.classification_id,
388 pec.classification_name
389 FROM pay_element_classifications pec
390 WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
391 AND pec.legislation_code = 'MX'
392 AND pec.business_group_id IS NULL
393 AND pec.parent_classification_id IS NOT NULL
394 AND p_tax_type = 'STATE';
395
396 l_calc_rule pay_mx_earn_exemption_rules_f.calc_rule%TYPE;
397 l_low_exempt_factor pay_mx_earn_exemption_rules_f.low_exempt_factor%TYPE;
398 l_low_range_factor pay_mx_earn_exemption_rules_f.low_range_factor%TYPE;
399 l_low_range_basis pay_mx_earn_exemption_rules_f.low_range_basis%TYPE;
400 l_high_exempt_factor pay_mx_earn_exemption_rules_f.high_exempt_factor%TYPE;
401 l_high_range_factor pay_mx_earn_exemption_rules_f.high_range_factor%TYPE;
402 l_high_range_basis pay_mx_earn_exemption_rules_f.high_range_basis%TYPE;
403 l_classification_name pay_element_classifications.classification_name%TYPE;
404 l_classification_id NUMBER;
405 l_counter NUMBER;
406 l_proc_name VARCHAR2(100);
407 l_index NUMBER;
408 l_return_value NUMBER;
409 l_default_value NUMBER;
410 l_dummy VARCHAR2(1);
411
412 BEGIN
413
414 l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
415 hr_utility_trace('Entering '||l_proc_name);
416
417 l_counter := 0;
418 l_return_value := 0;
419
420 IF p_earnings_amt > 0 THEN
421
422 /* Perform exempt calculation only if earnings amount is greater
423 than zero. */
424 OPEN get_ele_class;
425 LOOP
426 FETCH get_ele_class INTO l_classification_id,
427 l_classification_name;
428 EXIT WHEN get_ele_class%NOTFOUND;
429
430 hr_utility_trace('Element classification name is '
431 ||l_classification_name);
432
433 IF l_classification_name LIKE '%:Subject to ISR' THEN
434 RETURN (P_EARNINGS_AMT);
435 END IF;
436
437 IF g_isr_balances.count() > 0 THEN
438 IF g_isr_balances(g_isr_balances.first()).assignment_action_id <>
439 p_ctx_assignment_action_id THEN
440 g_isr_balances.delete();
441 END IF;
442 END IF;
443
444 l_counter := 0;
445
446 BEGIN
447 -- Query to check if the classification is partially subject.
448 --
449 SELECT ''
450 INTO l_dummy
451 FROM pay_mx_earn_exemption_rules_f pmex
452 WHERE pmex.tax_type = p_tax_type
453 AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
454 pmex.state_code) = p_ctx_jurisdiction_code
455 AND pmex.element_classification_id = l_classification_id
456 AND p_ctx_effective_date BETWEEN pmex.effective_start_date
457 AND pmex.effective_end_date;
458
459 l_index := l_classification_id;
460
461 IF l_classification_name IN
462 ('Supplemental Earnings:Social Foresight Earnings',
463 'Imputed Earnings:Social Foresight Earnings') THEN
464 l_index := 0;
465 END IF;
466
467 BEGIN
468 l_default_value := nvl(g_isr_balances(l_index).earnings_amt, 0)
469 + P_EARNINGS_AMT;
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 l_default_value := P_EARNINGS_AMT;
473 END;
474
475 l_return_value := get_partial_subj_earnings(
476 p_ctx_effective_date => P_CTX_EFFECTIVE_DATE,
477 p_ctx_assignment_action_id => P_CTX_ASSIGNMENT_ACTION_ID,
478 p_ctx_business_group_id => P_CTX_BUSINESS_GROUP_ID,
479 p_ctx_jurisdiction_code => P_CTX_JURISDICTION_CODE,
480 p_ctx_element_type_id => P_CTX_ELEMENT_TYPE_ID,
481 p_tax_type => P_TAX_TYPE,
482 p_earnings_amt => P_EARNINGS_AMT,
483 p_ytd_earnings_amt => l_default_value,
484 p_ptd_earnings_amt => l_default_value,
485 p_gross_earnings => P_GROSS_EARNINGS,
486 p_ytd_gross_earnings => P_GROSS_EARNINGS,
487 p_daily_salary => P_DAILY_SALARY,
488 p_classification_name => l_classification_name);
489
490 EXCEPTION
491 WHEN NO_DATA_FOUND THEN
492 null;
493 END;
494
495 END LOOP;
496 CLOSE get_ele_class;
497 END IF;
498
499 RETURN (l_return_value);
500
501 END GET_PARTIAL_SUBJ_EARNINGS;
502
503
504 --****************************************************************************
505 -- Name : GET_PARTIAL_SUBJ_EARNINGS
506 -- Description : This function calls another overloaded function, which returns
507 -- the portion of earnings that are partially subject to State
508 -- Tax and both fully and partially subject to ISR.
509 -- The PTD Earnings are defaulted to the secondary classification
510 -- earnings for the current run, which is maintained in the PL-SQL
511 -- table.
512 --****************************************************************************
513 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
514 (
515 P_CTX_EFFECTIVE_DATE DATE,
516 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
517 P_CTX_BUSINESS_GROUP_ID NUMBER,
518 P_CTX_JURISDICTION_CODE VARCHAR2,
519 P_CTX_ELEMENT_TYPE_ID NUMBER,
520 P_TAX_TYPE VARCHAR2,
521 P_EARNINGS_AMT NUMBER,
522 P_YTD_EARNINGS_AMT NUMBER,
523 P_GROSS_EARNINGS NUMBER,
524 P_YTD_GROSS_EARNINGS NUMBER,
525 P_DAILY_SALARY NUMBER,
526 P_CLASSIFICATION_NAME VARCHAR2
527 ) RETURN NUMBER AS
528
529 CURSOR get_ele_class IS
530 SELECT pec1.classification_id,
531 pec1.classification_name
532 FROM pay_element_classifications pec,
533 pay_element_classifications pec1, -- Secondary classification
534 pay_element_types_f pet,
535 pay_sub_classification_rules_f psr
536 WHERE pet.classification_id = pec.classification_id
537 AND pec.classification_id = pec1.parent_classification_id
538 AND pet.element_type_id = psr.element_type_id
539 AND psr.classification_id = pec1.classification_id
540 AND p_ctx_effective_date BETWEEN pet.effective_start_date
541 AND pet.effective_end_date
542 AND p_ctx_effective_date BETWEEN psr.effective_start_date
543 AND psr.effective_end_date
544 AND pet.element_type_id = p_ctx_element_type_id
545 AND pec.legislation_code = 'MX'
546 AND pec.classification_name <> 'Employer Liabilities'
547 AND pec.business_group_id IS NULL
548 AND pec1.legislation_code = 'MX'
549 AND pec1.business_group_id IS NULL
550 AND p_tax_type = 'ISR'
551 UNION
552 SELECT pec.classification_id,
553 pec.classification_name
554 FROM pay_element_classifications pec
555 WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
556 AND pec.legislation_code = 'MX'
557 AND pec.business_group_id IS NULL
558 AND pec.parent_classification_id IS NOT NULL
559 AND p_tax_type = 'STATE';
560
561 l_calc_rule pay_mx_earn_exemption_rules_f.calc_rule%type;
562 l_low_exempt_factor pay_mx_earn_exemption_rules_f.low_exempt_factor%type;
563 l_low_range_factor pay_mx_earn_exemption_rules_f.low_range_factor%type;
564 l_low_range_basis pay_mx_earn_exemption_rules_f.low_range_basis%type;
565 l_high_exempt_factor pay_mx_earn_exemption_rules_f.high_exempt_factor%type;
566 l_high_range_factor pay_mx_earn_exemption_rules_f.high_range_factor%type;
567 l_high_range_basis pay_mx_earn_exemption_rules_f.high_range_basis%type;
568 l_classification_name pay_element_classifications.classification_name%type;
569 l_classification_id NUMBER;
570 l_counter NUMBER;
571 l_proc_name VARCHAR2(100);
572 l_index NUMBER;
573 l_return_value NUMBER;
574 l_default_value NUMBER;
575 l_dummy VARCHAR2(1);
576
577 BEGIN
578
579 l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
580 hr_utility_trace('Entering '||l_proc_name);
581
582 l_counter := 0;
583 l_return_value := 0;
584
585 IF p_earnings_amt > 0 THEN
586
587 /* Perform exempt calculation only if earnings amount is greater
588 than zero. */
589 OPEN get_ele_class;
590 LOOP
591 FETCH get_ele_class INTO l_classification_id,
592 l_classification_name;
593 EXIT WHEN get_ele_class%NOTFOUND;
594
595 hr_utility_trace('Element classification name is '
596 ||l_classification_name);
597
598 IF l_classification_name LIKE '%:Subject to ISR' THEN
599 RETURN (P_EARNINGS_AMT);
600 END IF;
601
602 IF g_isr_balances.count() > 0 THEN
603 IF ((g_isr_balances(g_isr_balances.first()).assignment_action_id <>
604 p_ctx_assignment_action_id) OR p_tax_type <> 'ISR') THEN
605 g_isr_balances.delete();
606 END IF;
607 END IF;
608
609 l_counter := 0;
610
611 BEGIN
612 -- Query to check if the classification is partially subject.
613 --
614 SELECT ''
615 INTO l_dummy
616 FROM pay_mx_earn_exemption_rules_f pmex
617 WHERE pmex.tax_type = p_tax_type
618 AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
619 pmex.state_code) = p_ctx_jurisdiction_code
620 AND pmex.element_classification_id = l_classification_id
621 AND p_ctx_effective_date BETWEEN pmex.effective_start_date
622 AND pmex.effective_end_date;
623
624 l_index := l_classification_id;
625
626 IF l_classification_name IN
627 ('Supplemental Earnings:Social Foresight Earnings',
628 'Imputed Earnings:Social Foresight Earnings') THEN
629 l_index := 0;
630 END IF;
631
632 BEGIN
633 l_default_value := nvl(g_isr_balances(l_index).earnings_amt, 0)
634 + P_EARNINGS_AMT;
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 l_default_value := P_EARNINGS_AMT;
638 END;
639
640 l_return_value := get_partial_subj_earnings(
641 p_ctx_effective_date => P_CTX_EFFECTIVE_DATE,
642 p_ctx_assignment_action_id => P_CTX_ASSIGNMENT_ACTION_ID,
643 p_ctx_business_group_id => P_CTX_BUSINESS_GROUP_ID,
644 p_ctx_jurisdiction_code => P_CTX_JURISDICTION_CODE,
645 p_ctx_element_type_id => P_CTX_ELEMENT_TYPE_ID,
646 p_tax_type => P_TAX_TYPE,
647 p_earnings_amt => P_EARNINGS_AMT,
648 p_ytd_earnings_amt => P_YTD_EARNINGS_AMT,
649 p_ptd_earnings_amt => l_default_value,
650 p_gross_earnings => P_GROSS_EARNINGS,
651 p_ytd_gross_earnings => P_YTD_GROSS_EARNINGS,
652 p_daily_salary => P_DAILY_SALARY,
653 p_classification_name => l_classification_name);
654
655 EXCEPTION
656 WHEN NO_DATA_FOUND THEN
657 null;
658 END;
659
660 END LOOP;
661 CLOSE get_ele_class;
662 END IF;
663
664 RETURN (l_return_value);
665
666 END GET_PARTIAL_SUBJ_EARNINGS;
667
668
669 --******************************************************************************
670 -- Name : GET_SUBJ_EARNINGS_FOR_PERIOD
671 -- Description : This function calls another overloaded function, which returns
672 -- the portion of earnings that are partially subject to State
673 -- Tax and both fully and partially subject to ISR.
674 -- The YTD Earnings are defaulted to the secondary classification
675 -- earnings for the current run, which is maintained in the PL-SQL
676 -- table.
677 --******************************************************************************
678 FUNCTION GET_SUBJ_EARNINGS_FOR_PERIOD
679 (
680 P_CTX_EFFECTIVE_DATE DATE,
681 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
682 P_CTX_BUSINESS_GROUP_ID NUMBER,
683 P_CTX_JURISDICTION_CODE VARCHAR2,
684 P_CTX_ELEMENT_TYPE_ID NUMBER,
685 P_TAX_TYPE VARCHAR2,
686 P_EARNINGS_AMT NUMBER,
687 P_PTD_EARNINGS_AMT NUMBER,
688 P_GROSS_EARNINGS NUMBER,
689 P_YTD_GROSS_EARNINGS NUMBER,
690 P_DAILY_SALARY NUMBER,
691 P_CLASSIFICATION_NAME VARCHAR2
692 ) RETURN NUMBER AS
693
694 CURSOR get_ele_class IS
695 SELECT pec1.classification_id,
696 pec1.classification_name
697 FROM pay_element_classifications pec,
698 pay_element_classifications pec1, -- Secondary classification
699 pay_element_types_f pet,
700 pay_sub_classification_rules_f psr
701 WHERE pet.classification_id = pec.classification_id
702 AND pec.classification_id = pec1.parent_classification_id
703 AND pet.element_type_id = psr.element_type_id
704 AND psr.classification_id = pec1.classification_id
705 AND p_ctx_effective_date BETWEEN pet.effective_start_date
706 AND pet.effective_end_date
707 AND p_ctx_effective_date BETWEEN psr.effective_start_date
708 AND psr.effective_end_date
709 AND pet.element_type_id = p_ctx_element_type_id
710 AND pec.legislation_code = 'MX'
711 AND pec.classification_name <> 'Employer Liabilities'
712 AND pec.business_group_id IS NULL
713 AND pec1.legislation_code = 'MX'
714 AND pec1.business_group_id IS NULL
715 AND p_tax_type = 'ISR'
716 UNION
717 SELECT pec.classification_id,
718 pec.classification_name
719 FROM pay_element_classifications pec
720 WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
721 AND pec.legislation_code = 'MX'
722 AND pec.business_group_id IS NULL
723 AND pec.parent_classification_id IS NOT NULL
724 AND p_tax_type = 'STATE';
725
726 l_calc_rule pay_mx_earn_exemption_rules_f.calc_rule%TYPE;
727 l_low_exempt_factor pay_mx_earn_exemption_rules_f.low_exempt_factor%TYPE;
728 l_low_range_factor pay_mx_earn_exemption_rules_f.low_range_factor%TYPE;
729 l_low_range_basis pay_mx_earn_exemption_rules_f.low_range_basis%TYPE;
730 l_high_exempt_factor pay_mx_earn_exemption_rules_f.high_exempt_factor%TYPE;
731 l_high_range_factor pay_mx_earn_exemption_rules_f.high_range_factor%TYPE;
732 l_high_range_basis pay_mx_earn_exemption_rules_f.high_range_basis%TYPE;
733 l_classification_name pay_element_classifications.classification_name%TYPE;
734 l_classification_id NUMBER;
735 l_counter NUMBER;
736 l_proc_name VARCHAR2(100);
737 l_index NUMBER;
738 l_return_value NUMBER;
739 l_default_value NUMBER;
740 l_dummy VARCHAR2(1);
741
742 BEGIN
743
744 l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
745 hr_utility_trace('Entering '||l_proc_name);
746
747 l_counter := 0;
748 l_return_value := 0;
749
750 IF p_earnings_amt > 0 THEN
751
752 /* Perform exempt calculation only if earnings amount is greater
753 than zero. */
754 OPEN get_ele_class;
755 LOOP
756 FETCH get_ele_class INTO l_classification_id,
757 l_classification_name;
758 EXIT WHEN get_ele_class%NOTFOUND;
759
760 hr_utility_trace('Element classification name is '
761 ||l_classification_name);
762
763 IF l_classification_name LIKE '%:Subject to ISR' THEN
764 RETURN (P_EARNINGS_AMT);
765 END IF;
766
767 IF g_isr_balances.count() > 0 THEN
768 IF g_isr_balances(g_isr_balances.first()).assignment_action_id <>
769 p_ctx_assignment_action_id THEN
770 g_isr_balances.delete();
771 END IF;
772 END IF;
773
774 l_counter := 0;
775
776 BEGIN
777 -- Query to check if the classification is partially subject.
778 --
779 SELECT ''
780 INTO l_dummy
781 FROM pay_mx_earn_exemption_rules_f pmex
782 WHERE pmex.tax_type = p_tax_type
783 AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
784 pmex.state_code) = p_ctx_jurisdiction_code
785 AND pmex.element_classification_id = l_classification_id
786 AND p_ctx_effective_date BETWEEN pmex.effective_start_date
787 AND pmex.effective_end_date;
788
789 l_index := l_classification_id;
790
791 IF l_classification_name IN
792 ('Supplemental Earnings:Social Foresight Earnings',
793 'Imputed Earnings:Social Foresight Earnings') THEN
794 l_index := 0;
795 END IF;
796
797 BEGIN
798 l_default_value := nvl(g_isr_balances(l_index).earnings_amt, 0)
799 + P_EARNINGS_AMT;
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 l_default_value := P_EARNINGS_AMT;
803 END;
804
805 l_return_value := get_partial_subj_earnings(
806 p_ctx_effective_date => P_CTX_EFFECTIVE_DATE,
807 p_ctx_assignment_action_id => P_CTX_ASSIGNMENT_ACTION_ID,
808 p_ctx_business_group_id => P_CTX_BUSINESS_GROUP_ID,
809 p_ctx_jurisdiction_code => P_CTX_JURISDICTION_CODE,
810 p_ctx_element_type_id => P_CTX_ELEMENT_TYPE_ID,
811 p_tax_type => P_TAX_TYPE,
812 p_earnings_amt => P_EARNINGS_AMT,
813 p_ytd_earnings_amt => l_default_value,
814 p_ptd_earnings_amt => P_PTD_EARNINGS_AMT,
815 p_gross_earnings => P_GROSS_EARNINGS,
816 p_ytd_gross_earnings => P_YTD_GROSS_EARNINGS,
817 p_daily_salary => P_DAILY_SALARY,
818 p_classification_name => l_classification_name);
819
820 EXCEPTION
821 WHEN NO_DATA_FOUND THEN
822 null;
823 END;
824
825 END LOOP;
826 CLOSE get_ele_class;
827 END IF;
828
829 RETURN (l_return_value);
830
831 END GET_SUBJ_EARNINGS_FOR_PERIOD;
832
833
834
835 /****************************************************************************
836 Name : GET_PARTIAL_SUBJ_EARNINGS
837 Description : This function calculates subject earnings for classifications
838 that are fully and partially subject to ISR and state taxes.
839 *****************************************************************************/
840 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
841 (
842 P_CTX_EFFECTIVE_DATE DATE,
843 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
844 P_CTX_BUSINESS_GROUP_ID NUMBER,
845 P_CTX_JURISDICTION_CODE VARCHAR2,
846 P_CTX_ELEMENT_TYPE_ID NUMBER,
847 P_TAX_TYPE VARCHAR2,
848 P_EARNINGS_AMT NUMBER,
849 P_YTD_EARNINGS_AMT NUMBER,
850 P_PTD_EARNINGS_AMT NUMBER,
851 P_GROSS_EARNINGS NUMBER,
852 P_YTD_GROSS_EARNINGS NUMBER,
853 P_DAILY_SALARY NUMBER,
854 P_CLASSIFICATION_NAME VARCHAR2
855 )RETURN NUMBER AS
856
857 CURSOR get_exempt_info (p_classification_id number) IS
858 SELECT pmex.calc_rule,
859 pmex.low_exempt_factor,
860 pmex.low_range_factor,
861 pmex.low_range_basis,
862 pmex.high_exempt_factor,
863 pmex.high_range_factor,
864 pmex.high_range_basis
865 FROM pay_mx_earn_exemption_rules_f pmex
866 WHERE pmex.tax_type = p_tax_type
867 AND DECODE(p_tax_type, 'ISR',
868 p_ctx_jurisdiction_code,
869 pmex.state_code) = p_ctx_jurisdiction_code
870 AND pmex.element_classification_id = p_classification_id
871 AND p_ctx_effective_date BETWEEN pmex.effective_start_date
872 AND pmex.effective_end_date;
873
874 CURSOR get_days_per_period IS
875 SELECT TRUNC(ptp.end_date - ptp.start_date) + 1 days,
876 ppf.payroll_id,
877 paa.tax_unit_id,
878 paa.assignment_id
879 FROM pay_payrolls_f ppf,
880 per_time_periods ptp,
881 pay_assignment_actions paa,
882 pay_payroll_actions ppa
883 WHERE ptp.payroll_id = ppf.payroll_id
884 AND ppf.payroll_id = ppa.payroll_id
885 AND ppa.payroll_action_id = paa.payroll_action_id
886 AND paa.assignment_action_id = p_ctx_assignment_action_id
887 AND ppa.effective_date BETWEEN ptp.start_date
888 AND ptp.end_date
889 AND ppa.effective_date BETWEEN ppf.effective_start_date
890 AND ppf.effective_end_date;
891
892 CURSOR get_days_per_period_temp IS
893 SELECT TRUNC(ptp.end_date - ptp.start_date) + 1 days,
894 paf.payroll_id,
895 -- paa.tax_unit_id,
896 paf.assignment_id
897 FROM per_assignments_f paf,
898 per_time_periods ptp,
899 pay_temp_object_actions ptoa,
900 pay_payroll_actions ppa
901 WHERE ptp.payroll_id = paf.payroll_id
902 AND ppa.payroll_action_id = ptoa.payroll_action_id
903 AND ptoa.object_id = paf.assignment_id
904 AND ptoa.object_type = 'ASG'
905 AND ptoa.object_action_id = p_ctx_assignment_action_id
906 AND ppa.effective_date BETWEEN ptp.start_date
907 AND ptp.end_date
908 AND ppa.effective_date BETWEEN paf.effective_start_date
909 AND paf.effective_end_date;
910
911 CURSOR get_person_id ( cp_assignment_id NUMBER
912 ,cp_effective_date DATE) IS
913 SELECT person_id
914 FROM per_assignments_f paf
915 WHERE paf.assignment_id = cp_assignment_id
916 AND cp_effective_date BETWEEN paf.effective_start_date
917 AND paf.effective_end_date;
918
919 l_calc_rule pay_mx_earn_exemption_rules_f.calc_rule%TYPE;
920 l_low_exempt_factor pay_mx_earn_exemption_rules_f.low_exempt_factor%TYPE;
921 l_low_range_factor pay_mx_earn_exemption_rules_f.low_range_factor%TYPE;
922 l_low_range_basis pay_mx_earn_exemption_rules_f.low_range_basis%TYPE;
923 l_high_exempt_factor pay_mx_earn_exemption_rules_f.high_exempt_factor%TYPE;
924 l_high_range_factor pay_mx_earn_exemption_rules_f.high_range_factor%TYPE;
925 l_high_range_basis pay_mx_earn_exemption_rules_f.high_range_basis%TYPE;
926 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
927 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
928 l_balance_value_tab pay_balance_pkg.t_balance_value_tab;
929 ln_assignment_id pay_assignment_actions.assignment_id%TYPE;
930 ln_person_id per_all_people_f.person_id%TYPE;
931 l_classification_id NUMBER;
932 l_hire_date DATE;
933 l_exempt_amount NUMBER;
934 l_counter NUMBER;
935 l_sf_earnings NUMBER;
936 l_total_earnings NUMBER;
937 gmwa_1 NUMBER;
938 gmwa_7 NUMBER;
939 x NUMBER;
940 l_middle NUMBER;
941 l_proc_name VARCHAR2(100);
942 l_days NUMBER;
943 l_days_in_year NUMBER;
944 l_service_years NUMBER;
945 l_index NUMBER;
946 l_earnings_amt NUMBER;
947 l_low_exempt_limit NUMBER;
948 l_high_exempt_limit NUMBER;
949 l_ytd_excl_current NUMBER;
950 l_ptd_excl_current NUMBER;
951
952 BEGIN
953
954 l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
955 hr_utility_trace('Entering '||l_proc_name);
956
957 hr_utility_trace('Parameters ....');
958 hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
959 hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
960 hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
961 hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
962 hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
963 hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
964 hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
965 hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
966 hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
967 hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
968 hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
969 hr_utility_trace('P_DAILY_SALARY = '|| P_DAILY_SALARY);
970 hr_utility_trace('P_CLASSIFICATION_NAME = '|| P_CLASSIFICATION_NAME);
971
972
973 l_exempt_amount := 0;
974 l_counter := 0;
975
976 IF p_earnings_amt > 0 THEN
977
978 l_counter := 0;
979
980 l_classification_id :=
981 pay_mx_utility.get_classification_id(p_classification_name);
982
983 OPEN get_exempt_info(l_classification_id);
984 LOOP
985 FETCH get_exempt_info INTO l_calc_rule,
986 l_low_exempt_factor,
987 l_low_range_factor,
988 l_low_range_basis,
989 l_high_exempt_factor,
990 l_high_range_factor,
991 l_high_range_basis;
992 EXIT WHEN get_exempt_info%NOTFOUND;
993 l_counter := l_counter + 1;
994 IF l_counter > 1 THEN
995 hr_utility.set_message(801, 'PAY_MX_MULTI_TAX_SEC_CLASS');
996 hr_utility.set_message_token(801,
997 'ELEMENT_TYPE_ID',
998 p_ctx_element_type_id);
999 hr_utility.raise_error;
1000 END IF;
1001
1002 hr_utility_trace('l_calc_rule = '|| l_calc_rule);
1003 hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1004 hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1005 hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1006 hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1007 hr_utility_trace('l_high_range_factor = '|| l_high_range_factor);
1008 hr_utility_trace('l_high_range_basis = '|| l_high_range_basis);
1009
1010 l_index := l_classification_id;
1011
1012 IF p_classification_name IN
1013 ('Supplemental Earnings:Social Foresight Earnings',
1014 'Imputed Earnings:Social Foresight Earnings') THEN
1015 l_index := 0;
1016 END IF;
1017 --
1018 BEGIN
1019 g_isr_balances(l_index).earnings_amt := p_earnings_amt +
1020 g_isr_balances(l_index).earnings_amt;
1021 g_isr_balances(l_index).assignment_action_id :=
1022 p_ctx_assignment_action_id;
1023 --
1024 EXCEPTION
1025 WHEN NO_DATA_FOUND THEN
1026 g_isr_balances(l_index).earnings_amt := p_earnings_amt;
1027 g_isr_balances(l_index).assignment_action_id :=
1028 p_ctx_assignment_action_id;
1029 --
1030 END;
1031
1032 IF g_temp_object_actions THEN
1033
1034 OPEN get_days_per_period_temp;
1035 FETCH get_days_per_period_temp INTO l_days,
1036 l_payroll_id,
1037 -- l_tax_unit_id,
1038 ln_assignment_id;
1039 CLOSE get_days_per_period_temp;
1040
1041 pay_mx_rules.get_main_tax_unit_id(ln_assignment_id,
1042 p_ctx_effective_date,
1043 l_tax_unit_id);
1044
1045 ELSE
1046 OPEN get_days_per_period;
1047 FETCH get_days_per_period INTO l_days,
1048 l_payroll_id,
1049 l_tax_unit_id,
1050 ln_assignment_id;
1051 CLOSE get_days_per_period;
1052
1053 END IF;
1054
1055 hr_utility_trace('Days per period of payroll = '||l_days);
1056
1057 l_earnings_amt := g_isr_balances(l_index).earnings_amt;
1058
1059 OPEN get_person_id( ln_assignment_id, p_ctx_effective_date);
1060 FETCH get_person_id INTO ln_person_id;
1061 CLOSE get_person_id;
1062
1063 -- This represents the Exemption limit applicable on the
1064 -- Lower Range
1065 --
1066 l_low_exempt_limit := l_low_range_factor * get_range_basis_value(
1067 l_low_range_basis,
1068 p_daily_salary,
1069 p_ctx_assignment_action_id);
1070
1071 l_high_exempt_limit := l_high_range_factor * get_range_basis_value(
1072 l_high_range_basis,
1073 p_daily_salary,
1074 p_ctx_assignment_action_id);
1075
1076 l_ytd_excl_current := p_ytd_earnings_amt - p_earnings_amt;
1077
1078 l_ptd_excl_current := p_ptd_earnings_amt - p_earnings_amt;
1079
1080 -- Get number of days in year
1081 l_days_in_year := pay_mx_utility.get_days_in_year
1082 (p_ctx_business_group_id,
1083 l_tax_unit_id,
1084 l_payroll_id);
1085
1086 IF l_calc_rule = 'SINGLE_RANGE_DAILY' THEN
1087
1088 l_exempt_amount := LEAST(l_low_exempt_factor * p_earnings_amt,
1089 l_low_exempt_limit);
1090
1091 l_exempt_amount := l_exempt_amount -
1092 NVL(g_isr_balances(l_index).exempt_amt, 0);
1093
1094 ELSIF l_calc_rule = 'SINGLE_RANGE' THEN
1095
1096 l_low_exempt_limit := l_low_exempt_limit * l_days;
1097
1098 l_exempt_amount :=
1099 LEAST(l_low_exempt_factor * p_earnings_amt,
1100 l_low_exempt_limit - LEAST(
1101 l_low_exempt_limit,
1102 l_low_exempt_factor * l_ptd_excl_current
1103 )
1104 );
1105
1106 ELSIF l_calc_rule = 'SINGLE_RANGE_ANNUAL' THEN
1107
1108 l_exempt_amount :=
1109 LEAST(l_low_exempt_factor * p_earnings_amt,
1110 l_low_exempt_limit - LEAST(
1111 l_low_exempt_limit,
1112 l_low_exempt_factor * l_ytd_excl_current
1113 )
1114 );
1115
1116 ELSIF l_calc_rule = 'SINGLE_RANGE_SENIORITY_ANNUAL' THEN
1117
1118 l_service_years := hr_mx_utility.get_seniority(
1119 p_business_group_id => p_ctx_business_group_id
1120 ,p_tax_unit_id => l_tax_unit_id
1121 ,p_payroll_id => l_payroll_id
1122 ,p_person_id => ln_person_id
1123 ,p_effective_date => p_ctx_effective_date);
1124
1125 -- Bug 4950628 - Corrected the calculation.
1126 --
1127 l_low_exempt_limit := l_low_exempt_limit * l_service_years;
1128 l_exempt_amount :=
1129 LEAST(l_low_exempt_factor * p_earnings_amt,
1130 l_low_exempt_limit - LEAST(
1131 l_low_exempt_limit,
1132 l_low_exempt_factor * l_ytd_excl_current
1133 )
1134 );
1135
1136 ELSIF l_calc_rule = 'MIN_OF_RANGES' THEN
1137
1138 -- For 'MIN_OF_RANGES', we assume that the low_exempt_factor
1139 -- equals the high_exempt_factor.
1140 --
1141 l_low_exempt_limit := l_low_exempt_limit * l_days;
1142 l_high_exempt_limit := l_high_exempt_limit * l_days;
1143
1144 IF l_low_exempt_limit < l_high_exempt_limit THEN
1145
1146 l_exempt_amount :=
1147 LEAST(l_low_exempt_factor * p_earnings_amt,
1148 l_low_exempt_limit - LEAST(
1149 l_low_exempt_limit,
1150 l_low_exempt_factor * l_ptd_excl_current
1151 )
1152 );
1153 ELSE
1154
1155 l_exempt_amount :=
1156 LEAST(l_high_exempt_factor * p_earnings_amt,
1157 l_high_exempt_limit - LEAST(
1158 l_high_exempt_limit,
1159 l_high_exempt_factor * l_ptd_excl_current
1160 )
1161 );
1162
1163 END IF;
1164
1165 ELSIF l_calc_rule = 'DOUBLE_RANGE_TOTAL_EARNINGS' THEN
1166
1167 IF p_daily_salary <= l_low_exempt_limit THEN
1168
1169 l_exempt_amount := l_low_exempt_factor * p_earnings_amt;
1170
1171 ELSE
1172
1173 l_high_exempt_limit := l_high_exempt_limit * l_days;
1174
1175 l_exempt_amount :=
1176 LEAST(l_high_exempt_factor * p_earnings_amt,
1177 l_high_exempt_limit - LEAST(
1178 l_high_exempt_limit,
1179 l_high_exempt_factor * l_ptd_excl_current
1180 )
1181 );
1182
1183 END IF;
1184
1185 ELSIF l_calc_rule = 'INCOME_PLUS_EXEMPT_LIMIT' THEN
1186
1187 ------------------------------------------------------------
1188 -- Algorithm for SF Exempt earnings:
1189 -- ---------------------------------
1190 -- 1. Obtain YTD social foresight earnings (SFE).
1191 -- 2. Compute X = 7 * GMWA - YTD gross earnings.
1192 --
1193 -- The Table showing the Exempt Portion is as follows:
1194 -- ==================================================
1195 -- | Case | Exempt Portion |
1196 -- ==================================================
1197 -- | X < GMWA < SFE | GMWA |
1198 -- --------------------------------------------------
1199 -- | X < SFE < GMWA | SFE |
1200 -- --------------------------------------------------
1201 -- | GMWA < X < SFE | X |
1202 -- --------------------------------------------------
1203 -- | GMWA < SFE < X | SFE |
1204 -- --------------------------------------------------
1205 -- | SFE < GMWA < X | SFE |
1206 -- --------------------------------------------------
1207 -- | SFE < X < GMWA | SFE |
1208 -- --------------------------------------------------
1209 --
1210 -- 3. Amount exempt from ISR =
1211 -- Min ( SFE, Median(x, SFE, 1GMWA) )
1212 ------------------------------------------------------------
1213
1214 -- Step 1
1215 l_sf_earnings := p_ytd_earnings_amt;
1216
1217 -- Step 2
1218 -- Add Gross Earnings to total SF earnings.
1219 l_total_earnings := l_sf_earnings +
1220 p_ytd_gross_earnings;
1221
1222 -- Step 3
1223 gmwa_1 := l_days_in_year * get_range_basis_value(
1224 'GMW',
1225 p_daily_salary,
1226 p_ctx_assignment_action_id);
1227 gmwa_7 := gmwa_1 * 7;
1228
1229 x := gmwa_7 - p_ytd_gross_earnings;
1230
1231 -- Now find the second largest number among
1232 -- x, l_sf_earnings and gmwa_1
1233
1234 l_middle := least (greatest(x, l_sf_earnings),
1235 greatest(l_sf_earnings, gmwa_1),
1236 greatest(gmwa_1, x)
1237 );
1238
1239 l_exempt_amount :=
1240 least (l_sf_earnings, l_middle) * l_days / l_days_in_year;
1241
1242 l_exempt_amount := l_exempt_amount -
1243 nvl(g_isr_balances(l_index).exempt_amt, 0);
1244
1245 g_isr_balances(l_index).exempt_amt :=
1246 nvl(g_isr_balances(l_index).exempt_amt, 0) + l_exempt_amount;
1247
1248 hr_utility_trace('Subject amount for ' ||
1249 p_classification_name || ' = ' ||
1250 to_char(l_sf_earnings - l_exempt_amount)
1251 );
1252
1253 hr_utility_trace('Leaving '||l_proc_name);
1254
1255 -- Return the subject amount.
1256 RETURN (l_sf_earnings - l_exempt_amount);
1257
1258 END IF;
1259
1260 END LOOP;
1261
1262 CLOSE get_exempt_info;
1263
1264 END IF;
1265 --
1266 IF g_isr_balances.EXISTS(l_index) THEN
1267
1268 g_isr_balances(l_index).exempt_amt :=
1269 NVL(g_isr_balances(l_index).exempt_amt, 0) + l_exempt_amount;
1270
1271 END IF;
1272 --
1273 hr_utility_trace('Subject amount for ' ||
1274 p_classification_name || ' = ' ||
1275 TO_CHAR(p_earnings_amt - l_exempt_amount)
1276 );
1277
1278 hr_utility_trace('Leaving '||l_proc_name);
1279 --
1280 IF p_earnings_amt >= l_exempt_amount THEN
1281
1282 RETURN (p_earnings_amt - l_exempt_amount);
1283
1284 ELSE
1285
1286 RETURN (0);
1287
1288 END IF;
1289 --
1290 END GET_PARTIAL_SUBJ_EARNINGS;
1291
1292 /****************************************************************************
1293 Name : GET_PREVIOUS_PERIOD_BAL
1294 Description : This function returns balance values for pay period
1295 immediately previous to the period in which passed
1296 assignment_action_id lies.
1297 *****************************************************************************/
1298
1299 FUNCTION GET_PREVIOUS_PERIOD_BAL
1300 (
1301 P_CTX_ASSIGNMENT_ID NUMBER,
1302 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
1303 P_MODE VARCHAR2
1304 ) RETURN NUMBER AS
1305
1306 CURSOR get_previous_assact IS
1307 SELECT paa.assignment_action_id
1308 FROM pay_assignment_actions paa,
1309 pay_payroll_actions ppa
1310 WHERE paa.assignment_id = p_ctx_assignment_id
1311 AND paa.payroll_action_id = ppa.payroll_action_id
1312 AND paa.action_sequence =
1313 (SELECT max(paa_prev.action_sequence)
1314 FROM per_time_periods ptp
1315 , pay_payroll_actions ppa1
1316 , pay_assignment_actions paa1
1317 , per_time_periods ptp_prev
1318 , pay_payroll_actions ppa_prev
1319 , pay_assignment_actions paa_prev
1320 WHERE paa1.assignment_action_id = p_ctx_assignment_action_id
1321 AND ppa1.payroll_action_id = paa1.payroll_action_id
1322 AND ppa1.effective_date BETWEEN ptp.start_date
1323 AND ptp.end_date
1324 AND ptp.payroll_id = ppa1.payroll_id
1325 AND ptp_prev.payroll_id = ppa1.payroll_id
1326 AND (ptp.start_date - 1) BETWEEN ptp_prev.start_date
1327 AND ptp_prev.end_date
1328 AND paa_prev.assignment_id = paa1.assignment_id
1329 AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
1330 AND ppa_prev.action_type IN ('R', 'Q', 'B')
1331 AND ppa_prev.effective_date BETWEEN ptp_prev.start_date
1332 AND ptp_prev.end_date);
1333
1334 l_prev_assact pay_assignment_actions.assignment_action_id%TYPE;
1335 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
1336 l_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1337 l_counter NUMBER;
1338 l_balance_value NUMBER;
1339 l_proc_name VARCHAR2(100);
1340
1341 BEGIN
1342
1343 l_proc_name := g_proc_name ||'GET_PREVIOUS_PERIOD_BAL';
1344 hr_utility_trace('Entering '||l_proc_name);
1345
1346 l_balance_value := 0;
1347 OPEN get_previous_assact;
1348 FETCH get_previous_assact INTO l_prev_assact;
1349 CLOSE get_previous_assact;
1350
1351 hr_utility_trace('Previous assignment action id = '||l_prev_assact);
1352
1353 IF l_prev_assact IS NOT NULL THEN /* Bug 4283490 */
1354 l_counter := 1;
1355
1356 l_balance_value_tab(l_counter).defined_balance_id := get_def_bal_id (p_mode);
1357 l_balance_value_tab(l_counter).balance_value := 0;
1358 pay_balance_pkg.get_value (
1359 p_assignment_action_id => l_prev_assact,
1360 p_defined_balance_lst => l_balance_value_tab,
1361 p_get_rr_route => FALSE,
1362 p_get_rb_route => FALSE);
1363 l_balance_value := l_balance_value_tab(1).balance_value;
1364 END IF;
1365
1366 hr_utility_trace('Return value = '||l_balance_value);
1367 hr_utility_trace('Leaving '||l_proc_name);
1368
1369 RETURN (l_balance_value);
1370
1371 END GET_PREVIOUS_PERIOD_BAL;
1372
1373
1374 /****************************************************************************
1375 Name : GET_MX_TAX_INFO
1376 Description : This function returns various parameters required for social
1377 security quota and state tax calculation.
1378 *****************************************************************************/
1379
1380 FUNCTION GET_MX_TAX_INFO
1381 (
1382 P_CTX_BUSINESS_GROUP_ID NUMBER,
1383 P_CTX_TAX_UNIT_ID NUMBER,
1384 P_CTX_EFFECTIVE_DATE DATE,
1385 P_CTX_JURISDICTION_CODE VARCHAR2,
1386 P_LEGISLATION_INFO_TYPE VARCHAR2,
1387 P_LEGISLATION_INFO1 IN OUT NOCOPY VARCHAR2,
1388 P_LEGISLATION_INFO2 OUT NOCOPY VARCHAR2,
1389 P_LEGISLATION_INFO3 OUT NOCOPY VARCHAR2,
1390 P_LEGISLATION_INFO4 OUT NOCOPY VARCHAR2,
1391 P_LEGISLATION_INFO5 OUT NOCOPY VARCHAR2,
1392 P_LEGISLATION_INFO6 OUT NOCOPY VARCHAR2
1393 ) RETURN NUMBER AS
1394
1395 CURSOR csr_get_ss_info IS
1396 SELECT effective_start_date,
1397 effective_end_date,
1398 jurisdiction_code,
1399 legislation_info_type,
1400 legislation_info1,
1401 legislation_info2,
1402 legislation_info3,
1403 legislation_info4,
1404 legislation_info5,
1405 legislation_info6
1406 FROM pay_mx_legislation_info_f
1407 WHERE legislation_info_type = p_legislation_info_type
1408 AND DECODE(p_legislation_info1,
1409 '$Sys_Def$', legislation_info1,
1410 p_legislation_info1) = legislation_info1
1411 AND NVL(jurisdiction_code,
1412 p_ctx_jurisdiction_code) = p_ctx_jurisdiction_code
1413 AND p_ctx_effective_date BETWEEN effective_start_date
1414 AND effective_end_date;
1415
1416 l_proc_name VARCHAR2(100);
1417 l_exists BOOLEAN;
1418 cntr NUMBER;
1419 ld_start_date DATE;
1420 ld_end_date DATE;
1421 lv_jurisdiction pay_mx_legislation_info_f.jurisdiction_code%type;
1422 lv_legislation_info_type pay_mx_legislation_info_f.legislation_info_type%type;
1423 BEGIN
1424 l_proc_name := g_proc_name ||'GET_MX_TAX_INFO';
1425 hr_utility_trace('Entering '||l_proc_name);
1426 l_exists := FALSE;
1427 cntr := g_pay_mx_legislation_info_f.count();
1428 hr_utility_trace('Number of cached legislative tax info records = '||cntr);
1429
1430 IF cntr > 0 THEN /* Check if legislation info exists in cache. */
1431 FOR cntr IN g_pay_mx_legislation_info_f.first()..g_pay_mx_legislation_info_f.last()
1432 LOOP
1433 IF g_pay_mx_legislation_info_f(cntr).legislation_info_type
1434 = p_legislation_info_type AND
1435 NVL(g_pay_mx_legislation_info_f(cntr).jurisdiction_code,
1436 p_ctx_jurisdiction_code)= p_ctx_jurisdiction_code AND
1437 (p_legislation_info1 = '$Sys_Def$' OR
1438 g_pay_mx_legislation_info_f(cntr).legislation_info1
1439 = p_legislation_info1) AND
1440 p_ctx_effective_date BETWEEN
1441 g_pay_mx_legislation_info_f(cntr).effective_start_date AND
1442 g_pay_mx_legislation_info_f(cntr).effective_end_date
1443
1444 THEN
1445
1446 l_exists := TRUE;
1447 hr_utility_trace ('Retrieving legislative tax info from cache.');
1448 /* Copy cache onto out parameters */
1449 p_legislation_info1 := g_pay_mx_legislation_info_f(cntr).legislation_info1;
1450 p_legislation_info2 := g_pay_mx_legislation_info_f(cntr).legislation_info2;
1451 p_legislation_info3 := g_pay_mx_legislation_info_f(cntr).legislation_info3;
1452 p_legislation_info4 := g_pay_mx_legislation_info_f(cntr).legislation_info4;
1453 p_legislation_info5 := g_pay_mx_legislation_info_f(cntr).legislation_info5;
1454 p_legislation_info6 := g_pay_mx_legislation_info_f(cntr).legislation_info6;
1455
1456 EXIT;
1457 END IF;
1458 END LOOP;
1459 END IF;
1460
1461 IF cntr = 0 OR (NOT l_exists) THEN
1462 IF cntr > 0 THEN
1463 cntr := g_pay_mx_legislation_info_f.last() + 1;
1464 ELSE
1465 cntr := 1;
1466 END IF;
1467 hr_utility_trace ('Legislative tax info not found in cache. Hitting database now.');
1468
1469 OPEN csr_get_ss_info;
1470 FETCH csr_get_ss_info
1471 INTO g_pay_mx_legislation_info_f(cntr).effective_start_date,
1472 g_pay_mx_legislation_info_f(cntr).effective_end_date,
1473 g_pay_mx_legislation_info_f(cntr).jurisdiction_code,
1474 g_pay_mx_legislation_info_f(cntr).legislation_info_type,
1475 g_pay_mx_legislation_info_f(cntr).legislation_info1,
1476 g_pay_mx_legislation_info_f(cntr).legislation_info2,
1477 g_pay_mx_legislation_info_f(cntr).legislation_info3,
1478 g_pay_mx_legislation_info_f(cntr).legislation_info4,
1479 g_pay_mx_legislation_info_f(cntr).legislation_info5,
1480 g_pay_mx_legislation_info_f(cntr).legislation_info6;
1481 CLOSE csr_get_ss_info;
1482
1483 /* Override values fetched by this cursor
1484 IF p_legislation_info_type = 'MX Social Security Information' THEN
1485 IF p_legislation_info1 = 'WRI' THEN
1486 g_pay_mx_legislation_info_f(cntr).legislation_info5 :=
1487 hr_mx_utility.get_wrip (
1488 p_business_group_id => p_ctx_business_group_id,
1489 p_tax_unit_id => p_ctx_tax_unit_id);
1490 END IF;
1491 END IF;*/
1492
1493 /* Copy cache onto out parameters */
1494 p_legislation_info1 := g_pay_mx_legislation_info_f(cntr).legislation_info1;
1495 p_legislation_info2 := g_pay_mx_legislation_info_f(cntr).legislation_info2;
1496 p_legislation_info3 := g_pay_mx_legislation_info_f(cntr).legislation_info3;
1497 p_legislation_info4 := g_pay_mx_legislation_info_f(cntr).legislation_info4;
1498 p_legislation_info5 := g_pay_mx_legislation_info_f(cntr).legislation_info5;
1499 p_legislation_info6 := g_pay_mx_legislation_info_f(cntr).legislation_info6;
1500 END IF;
1501
1502 -- Bug 4656174
1503 IF p_legislation_info_type = 'MX Social Security Information' THEN
1504 IF p_legislation_info1 = 'WRI' THEN
1505 hr_utility_trace('Hitting database for WRIP');
1506 OPEN csr_get_ss_info;
1507 FETCH csr_get_ss_info
1508 INTO ld_start_date,
1509 ld_end_date,
1510 lv_jurisdiction,
1511 lv_legislation_info_type,
1512 p_legislation_info1,
1513 p_legislation_info2,
1514 p_legislation_info3,
1515 p_legislation_info4,
1516 p_legislation_info5,
1517 p_legislation_info6;
1518 CLOSE csr_get_ss_info;
1519 p_legislation_info5 :=
1520 hr_mx_utility.get_wrip (
1521 p_business_group_id => p_ctx_business_group_id,
1522 p_tax_unit_id => p_ctx_tax_unit_id);
1523 END IF;
1524 END IF;
1525
1526 hr_utility_trace('p_legislation_info_type = '||p_legislation_info_type);
1527 hr_utility_trace('p_legislation_info1 = ' ||p_legislation_info1);
1528 hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1529 hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1530 hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1531 hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1532 hr_utility_trace('p_legislation_info6 = ' ||p_legislation_info6);
1533
1534 hr_utility_trace('Leaving '||l_proc_name);
1535 RETURN(0);
1536 EXCEPTION WHEN OTHERS THEN
1537 g_pay_mx_legislation_info_f.DELETE();
1538 RAISE;
1539 END GET_MX_TAX_INFO;
1540
1541 /****************************************************************************
1542 Name : GET_MX_EE_HEAD_COUNT
1543 Description : This function returns the Employee Headcount used for
1544 Employer State Tax rate computation.
1545 *****************************************************************************/
1546 FUNCTION GET_MX_EE_HEAD_COUNT
1547 (
1548 P_CTX_BUSINESS_GROUP_ID NUMBER,
1549 P_CTX_TAX_UNIT_ID NUMBER,
1550 P_CTX_EFFECTIVE_DATE DATE,
1551 P_CTX_JURISDICTION_CODE VARCHAR2
1552 ) RETURN NUMBER AS
1553
1554 l_proc_name VARCHAR2(100);
1555 l_row_name VARCHAR2(300);
1556 l_legal_employer_name hr_organization_units.name%TYPE;
1557 l_head_count NUMBER;
1558 BEGIN
1559
1560 l_proc_name := g_proc_name || 'GET_MX_EE_HEAD_COUNT';
1561
1562 hr_utility_trace('Entering ' || l_proc_name);
1563
1564 l_legal_employer_name := hr_general.decode_organization(
1565 hr_mx_utility.get_legal_employer(
1566 p_ctx_business_group_id,
1567 p_ctx_tax_unit_id)
1568 );
1569
1570 hr_utility_trace('Legal Employer Name: ' || l_legal_employer_name);
1571
1572 l_row_name := l_legal_employer_name || ' (' ||
1573 p_ctx_jurisdiction_code || ')';
1574
1575
1576 l_head_count := hruserdt.get_table_value(p_ctx_business_group_id,
1577 'Employee Head Count',
1578 'Number of Employees',
1579 l_row_name,
1580 p_ctx_effective_date);
1581
1582 RETURN (l_head_count);
1583
1584 END GET_MX_EE_HEAD_COUNT;
1585
1586 /****************************************************************************
1587 Name : GET_MX_STATE_TAX_RULES
1588 Description : This function returns the data stored at Legal Employer level
1589 under "State Tax Rules" Org Info type.
1590 *****************************************************************************/
1591 FUNCTION GET_MX_STATE_TAX_RULES
1592 (
1593 P_CTX_BUSINESS_GROUP_ID NUMBER,
1594 P_CTX_TAX_UNIT_ID NUMBER,
1595 P_CTX_EFFECTIVE_DATE DATE,
1596 P_CTX_JURISDICTION_CODE VARCHAR2
1597 ) RETURN VARCHAR2 AS
1598
1599 -- Get data from hr_organization_information for the given Legal Employer
1600 --
1601 CURSOR c_get_rate_data(cp_legal_er_id NUMBER) IS
1602 SELECT DECODE(pml.legislation_info1,
1603 'RANGE', org_information5,
1604 'FLAT_RATE', org_information3)
1605 FROM hr_organization_information hoi,
1606 pay_mx_legislation_info_f pml
1607 WHERE hoi.organization_id = cp_legal_er_id
1608 AND hoi.org_information_context = 'MX_STATE_TAX_RULES'
1609 AND hoi.org_information1 = p_ctx_jurisdiction_code
1610 AND pml.jurisdiction_code = hoi.org_information1
1611 AND pml.legislation_info_type = 'MX State Tax Rate'
1612 AND p_ctx_effective_date BETWEEN pml.effective_start_date
1613 AND pml.effective_end_date
1614 AND DECODE(pml.legislation_info1,
1615 'RANGE', org_information5,
1616 'FLAT_RATE', org_information3) IS NOT NULL;
1617
1618 l_proc_name VARCHAR2(100);
1619 l_legal_er_id NUMBER;
1620 l_return_value VARCHAR2(100);
1621 BEGIN
1622
1623 l_proc_name := g_proc_name || 'GET_MX_STATE_TAX_RULES';
1624
1625 hr_utility_trace('Entering ' || l_proc_name);
1626
1627 l_legal_er_id := hr_mx_utility.get_legal_employer(p_ctx_business_group_id,
1628 p_ctx_tax_unit_id);
1629
1630 OPEN c_get_rate_data(l_legal_er_id);
1631 FETCH c_get_rate_data INTO l_return_value;
1632 CLOSE c_get_rate_data;
1633
1634 hr_utility_trace('Leaving ' || l_proc_name);
1635
1636 RETURN (l_return_value);
1637
1638 END GET_MX_STATE_TAX_RULES;
1639
1640 /****************************************************************************
1641 Name : CALCULATE_ISR_TAX
1642 Description : This function has
1643 1. Input Parameters as Contexts:
1644 - BUSINESS_GROUP_ID
1645 - ASSIGNMENT_ID
1646 - TAX_UNIT_ID
1647 - DATE_EARNED
1648 2. Input Parameters as Parameter:
1649 - SUBJECT_AMOUNT
1650 - ISR_RATES_TABLE
1651 - SUBSIDY_TABLE
1652 - CREDIT_TO_SALARY_TABLE
1653 3. Returns following data for give subject amount:
1654 - ISR_WITHHELD (Return Value)
1655 - ISR_CALCULATED (Output Parameter)
1656 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
1657 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
1658 - ISR_CREDIT_TO_SALARY (Output Parameter)
1659 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
1660 *****************************************************************************/
1661
1662 FUNCTION CALCULATE_ISR_TAX
1663 (
1664 P_PAYROLL_ACTION_ID NUMBER,
1665 P_ASSIGNMENT_ACTION_ID NUMBER,
1666 P_BUSINESS_GROUP_ID NUMBER,
1667 P_ASSIGNMENT_ID NUMBER,
1668 P_TAX_UNIT_ID NUMBER,
1669 P_DATE_EARNED DATE,
1670 P_SUBJECT_AMOUNT NUMBER,
1671 P_ISR_RATES_TABLE VARCHAR2,
1672 P_SUBSIDY_TABLE VARCHAR2,
1673 P_CREDIT_TO_SALARY_TABLE VARCHAR2,
1674 P_ISR_CALCULATED OUT NOCOPY NUMBER,
1675 P_ISR_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
1676 P_ISR_NON_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
1677 P_CREDIT_TO_SALARY OUT NOCOPY NUMBER,
1678 P_CREDIT_TO_SALARY_PAID OUT NOCOPY NUMBER
1679 )
1680 RETURN NUMBER IS
1681 l_proc_name VARCHAR2(100);
1682 ln_isr NUMBER;
1683 BEGIN
1684 l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX-2';
1685 hr_utility_trace('Entering '||l_proc_name);
1686
1687 ln_isr := pay_mx_tax_functions.calculate_isr_tax(
1688 p_payroll_action_id => P_PAYROLL_ACTION_ID,
1689 p_assignment_action_id => P_ASSIGNMENT_ACTION_ID,
1690 p_business_group_id => p_business_group_id,
1691 p_assignment_id => p_assignment_id,
1692 p_tax_unit_id => p_tax_unit_id,
1693 p_date_earned => p_date_earned,
1694 p_calc_mode => NULL,
1695 p_subject_amount => p_subject_amount,
1696 p_isr_rates_table => p_isr_rates_table,
1697 p_subsidy_table => p_subsidy_table,
1698 p_credit_to_salary_table => p_credit_to_salary_table,
1699 p_isr_calculated => p_isr_calculated,
1700 p_isr_creditable_subsidy => p_isr_creditable_subsidy,
1701 p_isr_non_creditable_subsidy => p_isr_non_creditable_subsidy,
1702 p_credit_to_salary => p_credit_to_salary,
1703 p_credit_to_salary_paid => p_credit_to_salary_paid);
1704
1705 hr_utility_trace('ISR = ' || ln_isr);
1706 hr_utility_trace('Leaving '||l_proc_name);
1707 RETURN (ln_isr);
1708 END CALCULATE_ISR_TAX;
1709
1710
1711 /****************************************************************************
1712 Name : CALCULATE_ISR_TAX
1713 Description : This function has
1714 1. Input Parameters as Contexts:
1715 - PAYROLL_ACTION_ID
1716 - ASSIGNMENT_ACTION_ID
1717 - BUSINESS_GROUP_ID
1718 - ASSIGNMENT_ID
1719 - TAX_UNIT_ID
1720 - DATE_EARNED
1721 2. Input Parameters as Parameter:
1722 - SUBJECT_AMOUNT
1723 - ISR_RATES_TABLE
1724 - SUBSIDY_TABLE
1725 - CREDIT_TO_SALARY_TABLE
1726 3. Returns following data for give subject amount:
1727 - ISR_WITHHELD (Return Value)
1728 - ISR_CALCULATED (Output Parameter)
1729 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
1730 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
1731 - ISR_CREDIT_TO_SALARY (Output Parameter)
1732 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
1733 4. ISR changes 2008
1734 - ISR Credit to Salary has been used as
1735 - ISR Subsidy for Employment
1736 *****************************************************************************/
1737
1738 FUNCTION CALCULATE_ISR_TAX
1739 (
1740 P_PAYROLL_ACTION_ID NUMBER,
1741 P_ASSIGNMENT_ACTION_ID NUMBER,
1742 P_BUSINESS_GROUP_ID NUMBER,
1743 P_ASSIGNMENT_ID NUMBER,
1744 P_TAX_UNIT_ID NUMBER,
1745 P_DATE_EARNED DATE,
1746 P_CALC_MODE VARCHAR2,
1747 P_SUBJECT_AMOUNT NUMBER,
1748 P_ISR_RATES_TABLE VARCHAR2,
1749 P_SUBSIDY_TABLE VARCHAR2,
1750 P_CREDIT_TO_SALARY_TABLE VARCHAR2,
1751 P_ISR_CALCULATED OUT NOCOPY NUMBER,
1752 P_ISR_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
1753 P_ISR_NON_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
1754 P_CREDIT_TO_SALARY OUT NOCOPY NUMBER,
1755 P_CREDIT_TO_SALARY_PAID OUT NOCOPY NUMBER
1756 )
1757 RETURN NUMBER IS
1758
1759 l_proc_name VARCHAR2(100);
1760 ln_fixed_rate NUMBER;
1761 ln_marginal_rate NUMBER;
1762 ln_lower_limit NUMBER;
1763 ln_marginal_tax NUMBER;
1764
1765 ln_fixed_subsidy NUMBER;
1766 ln_marginal_subsidy NUMBER;
1767 ln_subsidy_lower_limit NUMBER;
1768 ln_tax_subsidy_percentage NUMBER;
1769 ln_total_subsidy NUMBER;
1770 ln_payroll_id NUMBER;
1771
1772 ln_isr_withheld NUMBER;
1773 l_credit_to_salary_table varchar2(100) := P_CREDIT_TO_SALARY_TABLE;
1774 ln_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
1775 ln_bal_amt NUMBER;
1776 ln_bal_sub NUMBER;
1777 ln_bal_sub_paid NUMBER;
1778 ln_isr_subject_mtd NUMBER;
1779 l_date_earned DATE;
1780 l_date_paid DATE;
1781 ln_credit_to_salary_tot NUMBER;
1782 ln_credit_to_salary_max NUMBER;
1783 ln_credit_to_salary_curr NUMBER;
1784 ln_mult_num NUMBER;
1785 ln_period_end_mtd DATE;
1786 ln_period_start_mtd DATE;
1787 ln_max_row NUMBER;
1788 lv_period_type per_time_periods.period_type%TYPE;
1789 ln_days_in_a_period NUMBER;
1790 ln_days_in_period_sub_empl NUMBER;
1791 ln_period_number NUMBER;
1792 ln_pre_date_paid NUMBER := 0;
1793 ld_hire_date DATE; --added for fix 6821377
1794 ld_first_pay_date DATE; --added for fix 6933775
1795 ln_ISR_subj_adj NUMBER;
1796 --ln_pay_period_days NUMBER;
1797 ln_worked_days NUMBER;
1798 ln_bal_ISR_non_wrkd_days NUMBER;
1799 ln_ISR_table_factor NUMBER;
1800 ln_ISR_proj_subject NUMBER;
1801 ln_le_days_month NUMBER;
1802 ln_le_days_year NUMBER;
1803 ln_le_id hr_all_organization_units.organization_id%TYPE;
1804 ln_isr_prop_fact NUMBER;
1805 ln_le_worked_days NUMBER;
1806 /* lv_calc_mode VARCHAR2(20);
1807 lv_process VARCHAR2(20);
1808 lv_action_type pay_payroll_actions.action_type%type;
1809
1810 CURSOR csr_get_process_type IS
1811 SELECT action_type,
1812 pay_mx_utility.get_legi_param_val('CALC_MODE',
1813 legislative_parameters),
1814 pay_mx_utility.get_legi_param_val('PROCESS',
1815 legislative_parameters)
1816 FROM pay_payroll_actions
1817 WHERE payroll_action_id = p_payroll_action_id;*/
1818
1819 CURSOR csr_get_payroll_id IS
1820 SELECT payroll_id,effective_start_date
1821 FROM per_assignments
1822 WHERE assignment_id = P_ASSIGNMENT_ID;
1823
1824 CURSOR csr_def_bal_id (p_balance_name varchar2
1825 ,p_db_item_suffix varchar2) IS
1826 SELECT pdb.defined_balance_id
1827 FROM pay_defined_balances pdb,
1828 pay_balance_dimensions pbd,
1829 pay_balance_types pbt
1830 WHERE pbd.balance_dimension_id = pdb.balance_dimension_id
1831 AND pbt.balance_type_id = pdb.balance_type_id
1832 AND pbd.database_item_suffix = p_db_item_suffix -- '_ASG_GRE_MTD'
1833 AND pbt.balance_name = p_balance_name; --'ISR Subsidy for Employment'
1834
1835 CURSOR csr_date_earned IS
1836 SELECT ppa.effective_date l_date_paid,
1837 ptp.end_date l_date_earned
1838 FROM per_time_periods ptp,
1839 pay_payroll_actions ppa
1840 WHERE ppa.payroll_action_id = p_payroll_action_id
1841 AND ppa.time_period_id = ptp.time_period_id;
1842
1843 --Added for fix 6933775.
1844 /*Cursor to get the first pay period start date for the assignment*/
1845 CURSOR csr_get_first_pay_date IS
1846 SELECT MIN(ptp.start_date)
1847 FROM pay_assignment_actions paa,
1848 pay_payroll_actions ppa,
1849 per_time_periods ptp
1850 WHERE paa.assignment_id = p_assignment_id
1851 AND paa.payroll_action_id = ppa.payroll_action_id
1852 AND ptp.time_period_id = ppa.time_period_id
1853 AND ppa.action_type IN ('R', 'Q', 'B')
1854 AND (to_char(l_date_paid,'yyyymm') = to_char(ptp.end_date,'yyyymm')
1855 OR
1856 l_date_paid BETWEEN ptp.start_date AND ptp.end_date
1857 );
1858
1859 CURSOR csr_get_period_count_in_month IS
1860 SELECT ROWNUM mult_num
1861 ,end_date period_end_mtd
1862 ,start_date period_start_mtd
1863 ,period_type
1864 FROM PER_TIME_PERIODS ptp
1865 WHERE payroll_id = ln_payroll_id
1866 AND (TO_CHAR(l_date_paid,'yyyymm') = TO_CHAR(end_date,'yyyymm')
1867 OR
1868 l_date_paid BETWEEN start_date AND end_date
1869 )
1870 AND ld_hire_date <= end_date
1871 AND start_date >= NVL(ld_first_pay_date,start_date)
1872 ORDER BY end_date;
1873
1874 CURSOR csr_get_no_of_days_in_period(p_payroll_id number) IS
1875 SELECT end_date - start_date +1 period_days
1876 FROM PER_TIME_PERIODS ptp
1877 WHERE payroll_id = p_payroll_id
1878 AND TO_CHAR(l_date_earned,'yyyymmdd') = TO_CHAR(end_date,'yyyymmdd');
1879
1880
1881 BEGIN
1882 l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
1883 hr_utility_trace('Entering '||l_proc_name);
1884 hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
1885 hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
1886 hr_utility_trace('p_business_group_id: '||p_business_group_id);
1887 hr_utility_trace('p_assignment_id: '||p_assignment_id);
1888 hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
1889 hr_utility_trace('p_date_earned: '||
1890 fnd_date.date_to_canonical(p_date_earned));
1891 hr_utility_trace('p_calc_mode: '||p_calc_mode);
1892 hr_utility_trace('p_subject_amount: '||p_subject_amount);
1893 hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
1894 hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
1895 hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
1896 hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
1897
1898 ln_ISR_proj_subject := p_subject_amount;
1899
1900 OPEN csr_date_earned;
1901 FETCH csr_date_earned INTO l_date_paid, l_date_earned;
1902 CLOSE csr_date_earned;
1903
1904 hr_utility_trace('l_date_earned: '||l_date_earned);
1905 hr_utility_trace('l_date_paid: '||l_date_paid);
1906
1907 --Start of Bug Fix 6852627
1908 /*Calculation for Proration*/
1909 OPEN csr_get_payroll_id;
1910 FETCH csr_get_payroll_id INTO ln_payroll_id,ld_hire_date;
1911 CLOSE csr_get_payroll_id;
1912 hr_utility_trace('payroll_id '||ln_payroll_id);
1913 hr_utility_trace('Getting the actual number of days in pay period ...');
1914
1915 OPEN csr_get_no_of_days_in_period(ln_payroll_id);
1916 FETCH csr_get_no_of_days_in_period INTO ln_days_in_a_period;
1917 CLOSE csr_get_no_of_days_in_period;
1918
1919 hr_utility_trace('Number of days in the pay period : '||ln_days_in_a_period);
1920
1921 hr_utility_trace('Getting the total number of days in pay period as deifned in GRE/LE...');
1922
1923 ln_le_id := hr_mx_utility.get_legal_employer(
1924 p_business_group_id => p_business_group_id
1925 ,p_tax_unit_id => p_tax_unit_id);
1926
1927 pay_mx_utility.get_no_of_days_for_org( p_business_group_id => p_business_group_id
1928 ,p_org_id => ln_le_id
1929 ,p_gre_or_le => 'LE'
1930 ,p_days_month => ln_le_days_month
1931 ,p_days_year => ln_le_days_year);
1932
1933 IF (ln_le_days_month IS NULL OR ln_le_days_month = -999) THEN
1934 ln_le_days_month := ln_days_in_a_period ;
1935 END IF;
1936
1937 /*ln_pay_period_days := pay_mx_utility.get_days_in_pay_period( p_business_group_id
1938 ,p_tax_unit_id
1939 ,ln_payroll_id);*/
1940 hr_utility_trace('Average days in the month at LE :'||to_char(ln_le_days_month));
1941
1942 OPEN csr_def_bal_id ('ISR Non Working Days','_ASG_GRE_RUN');
1943 FETCH csr_def_bal_id INTO ln_def_bal_id;
1944 CLOSE csr_def_bal_id;
1945
1946 hr_utility_trace('ISR Non Working Days def bal id '||to_char(ln_def_bal_id));
1947 ln_bal_ISR_non_wrkd_days := pay_balance_pkg.get_value(ln_def_bal_id,
1948 p_assignment_action_id,
1949 p_tax_unit_id,
1950 NULL,
1951 NULL,
1952 NULL,
1953 NULL,
1954 NULL,
1955 NULL,
1956 'TRUE');
1957 hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
1958
1959 hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
1960 ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
1961 hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
1962
1963 hr_utility_trace('getting worked days.. ');
1964 ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
1965
1966 ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
1967
1968 hr_utility_trace('Actual worked days in the period for ISR Tax '||to_char(ln_worked_days));
1969 hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
1970 --End of Bug Fix 6852627
1971 IF to_char(l_date_paid,'yyyymmdd')
1972 >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
1973 hr_utility_trace('ISr Proration in 2008');
1974 IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
1975
1976 ln_ISR_proj_subject := ROUND(((ln_ISR_proj_subject * ln_le_days_month) / ln_le_worked_days),2);
1977
1978 hr_utility_trace('ISR Projected value in the pay period '||to_char(ln_ISR_proj_subject));
1979 END IF;
1980
1981 END IF;
1982
1983 hr_utility_trace('Final Subject amount after proration: '||ln_ISR_proj_subject);
1984 /* Article 113, 114 and 115 */
1985
1986 ln_fixed_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
1987 p_business_group_id
1988 ,p_isr_rates_table
1989 ,'Fixed Rate'
1990 ,TO_CHAR(ln_ISR_proj_subject)));
1991
1992 hr_utility_trace('ln_fixed_rate: '||ln_fixed_rate);
1993
1994 ln_marginal_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
1995 p_business_group_id
1996 ,p_isr_rates_table
1997 ,'Marginal Rate'
1998 ,TO_CHAR(ln_ISR_proj_subject)));
1999
2000 hr_utility_trace('ln_marginal_rate: '||ln_marginal_rate);
2001
2002 ln_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2003 p_business_group_id
2004 ,p_isr_rates_table
2005 ,'Lower Bound'
2006 ,TO_CHAR(ln_ISR_proj_subject)));
2007
2008 hr_utility_trace('ln_lower_limit: '||ln_lower_limit);
2009
2010 ln_marginal_tax := (ln_marginal_rate/100) * (ln_ISR_proj_subject -
2011 ln_lower_limit);
2012
2013 hr_utility_trace('ln_marginal_tax: '||ln_marginal_tax);
2014
2015 p_isr_calculated := ln_fixed_rate + ln_marginal_tax;
2016
2017 hr_utility_trace('p_isr_calculated: '||p_isr_calculated);
2018
2019 IF TO_CHAR(l_date_paid,'yyyymmdd')
2020 < TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2021
2022 /* Subsidy Calculation */
2023 ln_fixed_subsidy := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2024 p_business_group_id
2025 ,p_subsidy_table
2026 ,'Fixed Rate'
2027 ,TO_CHAR(ln_ISR_proj_subject)));
2028
2029 hr_utility_trace('ln_fixed_subsidy: '||ln_fixed_subsidy);
2030
2031 ln_marginal_subsidy := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2032 p_business_group_id
2033 ,p_subsidy_table
2034 ,'Marginal Rate'
2035 ,TO_CHAR(ln_ISR_proj_subject)));
2036
2037 hr_utility_trace('ln_marginal_subsidy: '||ln_marginal_subsidy);
2038
2039 ln_subsidy_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2040 p_business_group_id
2041 ,p_subsidy_table
2042 ,'Lower Bound'
2043 ,TO_CHAR(ln_ISR_proj_subject)));
2044
2045
2046 hr_utility_trace('ln_subsidy_lower_limit: '||ln_subsidy_lower_limit);
2047
2048 ln_tax_subsidy_percentage := hr_mx_utility.get_tax_subsidy_percent(
2049 p_business_group_id
2050 ,p_tax_unit_id);
2051
2052 hr_utility_trace('ln_tax_subsidy_percentage: '||ln_tax_subsidy_percentage);
2053
2054 /*OPEN csr_get_process_type;
2055 FETCH csr_get_process_type INTO lv_action_type,
2056 lv_calc_mode,
2057 lv_process;
2058 CLOSE csr_get_process_type;*/
2059
2060 IF (p_calc_mode = 'BEST' OR p_calc_mode = 'ARTICLE141') THEN
2061 -- Calculate subsidy for Article 141
2062 hr_utility_trace('Calculating subsidy for Article 141 ...');
2063 ln_total_subsidy := ln_fixed_rate * (ln_fixed_subsidy/100) +
2064 (ln_marginal_subsidy/100) *
2065 (ln_marginal_rate/100) *
2066 (ln_ISR_proj_subject - ln_subsidy_lower_limit);
2067 ELSE
2068 ln_total_subsidy := ln_fixed_subsidy +
2069 (ln_marginal_subsidy/100) *
2070 (ln_marginal_rate/100) *
2071 (ln_ISR_proj_subject - ln_subsidy_lower_limit);
2072 END IF;
2073 hr_utility_trace('ln_total_subsidy: '||ln_total_subsidy);
2074
2075 p_isr_creditable_subsidy := (ln_tax_subsidy_percentage/100) *
2076 ln_total_subsidy;
2077
2078 hr_utility_trace('p_isr_creditable_subsidy: '||p_isr_creditable_subsidy);
2079
2080 p_isr_non_creditable_subsidy := ln_total_subsidy - p_isr_creditable_subsidy;
2081
2082 hr_utility_trace('p_isr_non_creditable_subsidy: '||
2083 p_isr_non_creditable_subsidy);
2084
2085 ELSE
2086 p_isr_creditable_subsidy := 0;
2087 p_isr_non_creditable_subsidy := 0;
2088 END IF; -- end of subsidy calc
2089
2090 /* Credit To Salary Calculation */
2091
2092 IF ( p_credit_to_salary_table <> 'NONE' and P_CALC_MODE <>'ARTICLE142') THEN /*bug7445486*/
2093
2094 hr_utility_trace('inside subsidy '||p_credit_to_salary_table|| ' --- '||TO_CHAR(ln_ISR_proj_subject));
2095 p_credit_to_salary := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2096 p_business_group_id
2097 ,p_credit_to_salary_table
2098 ,'Amount'
2099 ,TO_CHAR(ln_ISR_proj_subject)));
2100
2101 P_CREDIT_TO_SALARY := nvl(P_CREDIT_TO_SALARY,0);
2102
2103 IF TO_CHAR(l_date_paid,'yyyymmdd')
2104 >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2105
2106 OPEN csr_get_payroll_id;
2107 FETCH csr_get_payroll_id INTO ln_payroll_id,ld_hire_date;
2108 CLOSE csr_get_payroll_id;
2109
2110 -- get ISR Subject balance for month
2111
2112 OPEN csr_def_bal_id ('ISR Subject','_ASG_GRE_MTD');
2113 FETCH csr_def_bal_id INTO ln_def_bal_id;
2114 CLOSE csr_def_bal_id;
2115
2116 hr_utility_trace('ISR Subject def bal id '||ln_def_bal_id);
2117
2118 ln_bal_amt := pay_balance_pkg.get_value(ln_def_bal_id,
2119 P_ASSIGNMENT_ACTION_ID,
2120 p_tax_unit_id,
2121 NULL,
2122 NULL,
2123 NULL,
2124 NULL,
2125 NULL,
2126 NULL,
2127 'TRUE');
2128
2129 hr_utility_trace('MTD Subject Amt - ln_bal_amt '||to_char(ln_bal_amt));
2130 /*Get the previous ISR Subject adjusted amount due to proration
2131 this will have impact only in semi-monthly and weekely payroll*/
2132 OPEN csr_def_bal_id ('ISR Subject Adjusted','_ASG_GRE_MTD');
2133 FETCH csr_def_bal_id INTO ln_def_bal_id;
2134 CLOSE csr_def_bal_id;
2135
2136 hr_utility_trace('ISR Subject Adjusted def bal id '||ln_def_bal_id);
2137 ln_ISR_subj_adj := pay_balance_pkg.get_value(ln_def_bal_id,
2138 P_ASSIGNMENT_ACTION_ID,
2139 p_tax_unit_id,
2140 null,
2141 null,
2142 null,
2143 null,
2144 null,
2145 null,
2146 'TRUE');
2147 hr_utility_trace('ISR Subject Adjusted Amount MTD - ln_ISR_subj_adj'||ln_ISR_subj_adj);
2148 -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
2149 -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
2150 -- get ISR Subsidy for Employement balance for month
2151 ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
2152 hr_utility_trace('Actual subject value is ln_ISR_subj_adj + ln_ISR_proj_subject '||ln_ISR_subj_adj);
2153
2154 OPEN csr_def_bal_id ('ISR Subsidy for Employment','_ASG_GRE_MTD');
2155 FETCH csr_def_bal_id INTO ln_def_bal_id;
2156 CLOSE csr_def_bal_id;
2157
2158 hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
2159
2160 ln_bal_sub := pay_balance_pkg.get_value(ln_def_bal_id,
2161 P_ASSIGNMENT_ACTION_ID,
2162 p_tax_unit_id,
2163 NULL,
2164 NULL,
2165 NULL,
2166 NULL,
2167 NULL,
2168 NULL,
2169 'TRUE');
2170
2171 hr_utility_trace('ISR Sub for Empl Bal Sub '||to_char(ln_bal_sub));
2172
2173 OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid','_ASG_GRE_MTD');
2174 FETCH csr_def_bal_id INTO ln_def_bal_id;
2175 CLOSE csr_def_bal_id;
2176
2177 hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
2178
2179 ln_bal_sub_paid := pay_balance_pkg.get_value(ln_def_bal_id,
2180 P_ASSIGNMENT_ACTION_ID,
2181 p_tax_unit_id,
2182 NULL,
2183 NULL,
2184 NULL,
2185 NULL,
2186 NULL,
2187 NULL,
2188 'TRUE');
2189
2190 hr_utility_trace('ISR Sub for Empl Bal Sub Paid '||to_char(ln_bal_sub));
2191
2192 if ln_payroll_id is not null then
2193 select min(period_type)
2194 into lv_period_type
2195 from pay_payrolls_f
2196 where payroll_id = ln_payroll_id;
2197 end if;
2198
2199 OPEN csr_get_first_pay_date;
2200 FETCH csr_get_first_pay_date INTO ld_first_pay_date;
2201 CLOSE csr_get_first_pay_date;
2202
2203 ln_isr_subject_mtd := CONVERT_INTO_MONTHLY_AVG_SAL (p_business_group_id
2204 ,p_tax_unit_id
2205 ,ln_payroll_id
2206 ,ln_ISR_proj_subject
2207 ,l_date_paid
2208 ,ld_hire_date
2209 ,ld_first_pay_date
2210 ,ln_period_number)
2211 + (ln_ISR_subj_adj - ln_ISR_proj_subject);
2212
2213 hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
2214 hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
2215 hr_utility_trace('ln_period_number : '||ln_period_number);
2216
2217 open csr_get_period_count_in_month;
2218 loop
2219 fetch csr_get_period_count_in_month into ln_mult_num,
2220 ln_period_end_mtd,
2221 ln_period_start_mtd,
2222 lv_period_type;
2223 exit when csr_get_period_count_in_month%NOTFOUND;
2224
2225 select least(count(*),1)
2226 into ln_pre_date_paid
2227 from pay_payroll_actions ppa,
2228 pay_assignment_actions paa,
2229 per_time_periods ptp
2230 where ppa.payroll_action_id = paa.payroll_action_id
2231 and ptp.time_period_id = ppa.time_period_id
2232 and paa.assignment_id = p_assignment_id
2233 and to_char(ptp.end_date,'yyyymm') = to_char(l_date_paid,'yyyymm')
2234 and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
2235 AND ld_hire_date <= ptp.end_date;
2236
2237 hr_utility_trace('l_date_paid '||to_char(l_date_paid,'yyyymm'));
2238 hr_utility_trace('ln_pre_date_paid '||to_char(ln_pre_date_paid));
2239
2240 IF l_date_paid >= ln_period_start_mtd and
2241 l_date_paid <= ln_period_end_mtd then
2242 ln_period_number := ln_period_number - ln_pre_date_paid;
2243 hr_utility_trace('Actual ln_period_number '||to_char(ln_period_number));
2244 end if;
2245
2246 end loop;
2247 close csr_get_period_count_in_month;
2248
2249
2250 IF lv_period_type = 'Semi-Month' THEN
2251 ln_days_in_period_sub_empl := 15;
2252 elsif lv_period_type = 'Ten Days' then
2253 ln_days_in_period_sub_empl := 10;
2254 END IF ;
2255 p_credit_to_salary := FND_NUMBER.canonical_to_number(get_table_value (
2256 p_business_group_id
2257 ,p_credit_to_salary_table
2258 ,'Amount'
2259 ,TO_CHAR(ln_ISR_subj_adj)
2260 ,l_date_paid
2261 ,ln_days_in_a_period
2262 ,ln_period_number
2263 ,lv_period_type));
2264
2265 hr_utility_trace('Total Subsidy for Empl '||to_char(p_credit_to_salary));
2266
2267 ln_credit_to_salary_max := p_credit_to_salary;
2268
2269 P_CREDIT_TO_SALARY := (ln_credit_to_salary_max/30.4) * ln_days_in_period_sub_empl;
2270
2271 ln_credit_to_salary_curr := P_CREDIT_TO_SALARY;
2272
2273 hr_utility_trace('Current Period Subsidy for Empl '||to_char(p_credit_to_salary));
2274
2275 select count(*) max_row
2276 into ln_max_row
2277 from PER_TIME_PERIODS ptp1
2278 where payroll_id = ln_payroll_id
2279 and to_char(l_date_paid,'yyyymm') = to_char(end_date,'yyyymm');
2280
2281 open csr_get_period_count_in_month;
2282 loop
2283 fetch csr_get_period_count_in_month into ln_mult_num,
2284 ln_period_end_mtd,
2285 ln_period_start_mtd,
2286 lv_period_type;
2287 exit when csr_get_period_count_in_month%NOTFOUND;
2288 if ln_period_end_mtd = l_date_earned then
2289
2290 if to_char(ln_period_end_mtd,'yyyymm') = to_char(l_date_paid,'yyyymm') and
2291 to_char(ln_period_end_mtd,'yyyymmdd') >= to_char(last_day(l_date_paid),'yyyymmdd') then
2292
2293 P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
2294 P_CREDIT_TO_SALARY * ln_mult_num );
2295 hr_utility_trace('outside/last day of month '||to_char(p_credit_to_salary));
2296 else
2297 P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
2298 P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
2299 hr_utility_trace('With in month '||to_char(p_credit_to_salary));
2300 end if;
2301
2302 hr_utility_trace('max allowd Subsidy for Empl '||to_char(p_credit_to_salary));
2303 IF l_date_paid >= ln_period_start_mtd AND
2304 l_date_paid <= ln_period_end_mtd AND
2305 (lv_period_type = 'Calendar Month' OR (lv_period_type = 'Semi-Month' AND ln_period_number = 2)
2306 OR (lv_period_type = 'Ten Days' AND ln_period_number = 3))
2307 THEN
2308 P_CREDIT_TO_SALARY := ln_credit_to_salary_max;
2309 hr_utility_trace('Final period Subsidy for Empl '||to_char(p_credit_to_salary));
2310 end if;
2311 P_CREDIT_TO_SALARY := P_CREDIT_TO_SALARY - ln_bal_sub;
2312 hr_utility_trace('Subsidy for Empl '||to_char(p_credit_to_salary));
2313 end if;
2314 end loop;
2315 close csr_get_period_count_in_month;
2316 END IF;
2317 ELSE
2318 p_credit_to_salary := 0;
2319 END IF;
2320
2321 P_CREDIT_TO_SALARY := nvl(P_CREDIT_TO_SALARY,0);
2322 hr_utility_trace('p_credit_to_salary: '||p_credit_to_salary);
2323
2324 /* ISR Withheld Calculation */
2325
2326 ln_isr_withheld := p_isr_calculated -
2327 p_isr_creditable_subsidy -
2328 p_credit_to_salary;
2329
2330
2331 hr_utility_trace('ln_isr_withheld B4: '||ln_isr_withheld);
2332
2333 /*Incuded the proration logic based on balance ISR Non working days*/
2334 --Start of Bug fix 6852627
2335 IF to_char(l_date_paid,'yyyymmdd')
2336 >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2337 IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
2338
2339 hr_utility_trace('-- Non workings days are feeded, need to proproate ISR Tax --');
2340
2341 hr_utility_trace('Getting the table factor value for ISR Tax calculation');
2342 ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
2343
2344 hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
2345
2346 hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
2347
2348 ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
2349
2350 hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
2351 hr_utility_trace('Start of ISR Subject proration ');
2352
2353 ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
2354 hr_utility_trace('ISR Non Worked Days value is invalid ');
2355 hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
2356 hr_utility.raise_error;
2357
2358 ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
2359 ln_isr_withheld := 0;
2360 p_credit_to_salary_paid := 0;
2361 p_isr_calculated := 0;
2362 p_credit_to_salary := 0;
2363
2364 END IF;
2365 END IF;
2366 --End of Bug Fix 6852627
2367
2368 IF ln_isr_withheld < 0 THEN
2369 p_credit_to_salary_paid := ABS(ln_isr_withheld);
2370 ln_isr_withheld := 0;
2371 ELSE
2372 p_credit_to_salary_paid := 0;
2373 END IF;
2374 /*To feed ISR Subject Adjusted Balance*/
2375 IF TO_CHAR(l_date_paid,'yyyymmdd')
2376 >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2377 p_isr_creditable_subsidy := ln_ISR_proj_subject;
2378 hr_utility_trace(' Feeding ISR Subject Adjusted Balance value in 2008 = p_isr_creditable_subsidy'||p_isr_creditable_subsidy);
2379 END IF;
2380 hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
2381 hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
2382 hr_utility_trace('Leaving '||l_proc_name);
2383
2384 RETURN ln_isr_withheld;
2385
2386 EXCEPTION
2387 WHEN OTHERS THEN
2388 hr_utility_trace('Exception in '||l_proc_name||': '||SQLERRM);
2389 RAISE;
2390 END CALCULATE_ISR_TAX;
2391
2392 /****************************************************************************
2393 Name : CONVERT_MONTHLY_TO_PERIOD
2394 Description : This function has
2395 1. Input Parameters as Contexts:
2396 - BUSINESS_GROUP_ID
2397 - TAX_UNIT_ID
2398 - PAYROLL_ID
2399 2. Input Parameters as Parameter:
2400 - PERIODIC_EARNINGS
2401 *****************************************************************************/
2402
2403 /*FUNCTION CONVERT_MONTHLY_TO_PERIOD ( p_business_group_id NUMBER
2404 ,p_tax_unit_id NUMBER
2405 ,p_payroll_id NUMBER
2406 ,p_periodic_value NUMBER
2407 ,P_date_earned DATE)
2408 RETURN NUMBER IS
2409
2410 ln_periodic_value NUMBER;
2411 ln_days_in_a_period NUMBER;
2412
2413 CURSOR csr_get_period_count_in_month IS
2414 select end_date - start_date +1 period_days
2415 from PER_TIME_PERIODS ptp
2416 where payroll_id = p_payroll_id
2417 and to_char(p_date_earned,'yyyymmdd') = to_char(end_date,'yyyymmdd');
2418
2419 BEGIN
2420
2421 hr_utility_trace('Entering ..CONVERT_MONTHLY_TO_PERIOD');
2422 hr_utility_trace('p_periodic_value: ' ||p_periodic_value);
2423
2424 open csr_get_period_count_in_month;
2425 fetch csr_get_period_count_in_month into ln_days_in_a_period;
2426 close csr_get_period_count_in_month;
2427
2428 ln_periodic_value := (p_periodic_value/30.4) * ln_days_in_a_period;
2429
2430 hr_utility_trace('ln_periodic_value: ' ||ln_periodic_value);
2431 hr_utility_trace('Leaving ..CONVERT_MONTHLY_TO_PERIOD');
2432
2433 RETURN ln_periodic_value;
2434
2435 END CONVERT_MONTHLY_TO_PERIOD; /*
2436
2437 /****************************************************************************
2438 Name : CONVERT_INTO_MONTHLY_SALARY
2439 Description : This function has
2440 1. Input Parameters as Contexts:
2441 - BUSINESS_GROUP_ID
2442 - TAX_UNIT_ID
2443 - PAYROLL_ID
2444 2. Input Parameters as Parameter:
2445 - PERIODIC_EARNINGS
2446 *****************************************************************************/
2447
2448 FUNCTION CONVERT_INTO_MONTHLY_SALARY ( p_business_group_id NUMBER
2449 ,p_tax_unit_id NUMBER
2450 ,p_payroll_id NUMBER
2451 ,p_periodic_earnings NUMBER)
2452 RETURN NUMBER IS
2453
2454 ln_periodic_earnings NUMBER;
2455 ln_days_in_a_month NUMBER;
2456 lv_period_type pay_all_payrolls_f.period_type%TYPE;
2457
2458 BEGIN
2459
2460 hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_SALARY');
2461 hr_utility_trace('p_periodic_earnings: ' ||p_periodic_earnings);
2462
2463 SELECT period_type
2464 INTO lv_period_type
2465 FROM pay_all_payrolls_f ppf,
2466 fnd_sessions fs
2467 WHERE payroll_id = p_payroll_id
2468 AND fs.effective_date BETWEEN ppf.effective_start_date
2469 AND ppf.effective_end_date
2470 AND fs.session_id = USERENV('sessionid');
2471
2472 ln_days_in_a_month := pay_mx_utility.get_days_in_month(
2473 p_business_group_id => p_business_group_id
2474 ,p_tax_unit_id => p_tax_unit_id
2475 ,p_payroll_id => p_payroll_id);
2476
2477
2478 IF lv_period_type = 'Calendar Month' THEN
2479
2480 ln_periodic_earnings := p_periodic_earnings;
2481
2482 ELSIF lv_period_type = 'Semi-Month' THEN
2483
2484 ln_periodic_earnings := (p_periodic_earnings / 15) * ln_days_in_a_month;
2485
2486 ELSIF lv_period_type = 'Week' THEN
2487
2488 ln_periodic_earnings := (p_periodic_earnings / 7) * ln_days_in_a_month;
2489
2490 ELSIF lv_period_type = 'Bi-Week' THEN
2491
2492 ln_periodic_earnings := (p_periodic_earnings / 14) * ln_days_in_a_month;
2493
2494 ELSIF lv_period_type = 'Ten Days' THEN
2495
2496 ln_periodic_earnings := (p_periodic_earnings / 10) * ln_days_in_a_month;
2497
2498
2499 END IF;
2500
2501 hr_utility_trace('ln_periodic_earnings: ' ||ln_periodic_earnings);
2502 hr_utility_trace('Leaving ..CONVERT_INTO_MONTHLY_SALARY');
2503
2504 RETURN ln_periodic_earnings;
2505
2506 END CONVERT_INTO_MONTHLY_SALARY;
2507
2508
2509 /****************************************************************************
2510 Name : CHECK_EE_SAL_CRITERIA
2511 Description : This function returns 'Y' if employee's annual gross earning
2512 is less than 300,000 MXN.
2513 *****************************************************************************/
2514
2515 FUNCTION CHECK_EE_SAL_CRITERIA
2516 (
2517 P_CTX_ASSIGNMENT_ID NUMBER
2518 ,P_CTX_DATE_EARNED DATE
2519 ) RETURN VARCHAR2 AS
2520
2521 CURSOR c_defined_balance_id(p_balance_name VARCHAR2
2522 ,p_dimension VARCHAR2)
2523 IS
2524 SELECT pdb.defined_balance_id
2525 FROM pay_balance_types pbt
2526 ,pay_balance_dimensions pbd
2527 ,pay_defined_balances pdb
2528 WHERE pbt.balance_name=p_balance_name
2529 AND pbd.database_item_suffix =p_dimension
2530 AND pbt.legislation_code = 'MX'
2531 AND pbd.legislation_code = 'MX'
2532 AND pbt.balance_type_id = pdb.balance_type_id
2533 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
2534
2535
2536 CURSOR c_assignment_action_id
2537 IS
2538 SELECT MAX(paa.assignment_action_id)
2539 FROM pay_assignment_actions paa
2540 ,pay_payroll_actions ppa
2541 WHERE paa.assignment_id =P_CTX_ASSIGNMENT_ID
2542 AND paa.payroll_action_id=ppa.payroll_action_id
2543 AND ppa.action_type in ('R','Q','I')
2544 AND ppa.date_earned <=P_CTX_DATE_EARNED;
2545
2546 l_flag VARCHAR2(4);
2547 l_capping_value VARCHAR(20);
2548 l_ignore NUMBER;
2549 l_pkg_value NUMBER;
2550 l_gross_earning NUMBER;
2551 l_bal_defined_id NUMBER;
2552 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
2553 BEGIN
2554 l_flag :='N';
2555 l_ignore:=0;
2556 l_gross_earning:=0;
2557 l_capping_value:=0;
2558 l_capping_value:='$Sys_Def$';
2559
2560 hr_utility_trace('Entering ..CHECK_EE_SAL_CRITERIA');
2561 OPEN c_defined_balance_id('Gross Earnings','_PER_YTD');
2562 FETCH c_defined_balance_id INTO l_bal_defined_id;
2563 CLOSE c_defined_balance_id;
2564
2565 OPEN c_assignment_action_id;
2566 FETCH c_assignment_action_id INTO l_assignment_action_id;
2567 CLOSE c_assignment_action_id;
2568
2569 IF(l_assignment_action_id IS NULL) THEN
2570 hr_utility_trace('Leaving ..CHECK_EE_SAL_CRITERIA');
2571 RETURN 'N';
2572 END IF;
2573
2574 l_pkg_value:=pay_mx_tax_functions.get_mx_tax_info
2575 ( P_CTX_BUSINESS_GROUP_ID => NULL,
2576 P_CTX_TAX_UNIT_ID => NULL,
2577 P_CTX_EFFECTIVE_DATE => P_CTX_DATE_EARNED,
2578 P_CTX_JURISDICTION_CODE => l_ignore,
2579 P_LEGISLATION_INFO_TYPE => 'MX Tax Adjustment Parameters',
2580 P_LEGISLATION_INFO1 => l_capping_value,
2581 P_LEGISLATION_INFO2 => l_ignore,
2582 P_LEGISLATION_INFO3 => l_ignore,
2583 P_LEGISLATION_INFO4 => l_ignore,
2584 P_LEGISLATION_INFO5 => l_ignore,
2585 P_LEGISLATION_INFO6 => l_ignore
2586 );
2587
2588 l_gross_earning:=pay_balance_pkg.get_value
2589 (p_defined_balance_id =>l_bal_defined_id,
2590 p_assignment_action_id =>l_assignment_action_id,
2591 p_tax_unit_id => NULL,
2592 p_jurisdiction_code => NULL,
2593 p_source_id => NULL,
2594 p_tax_group => NULL,
2595 p_date_earned => NULL);
2596
2597 IF(nvl(l_gross_earning,-1)>l_capping_value) THEN
2598 l_flag :='N';
2599 ELSIF (l_gross_earning <> 0) THEN
2600 l_flag :='Y';
2601 END IF;
2602 hr_utility_trace('l_flag: ' ||l_flag);
2603 hr_utility_trace('Leaving ..CHECK_EE_SAL_CRITERIA');
2604 RETURN l_flag;
2605
2606 END CHECK_EE_SAL_CRITERIA;
2607
2608
2609 /****************************************************************************
2610 Name : CHECK_EE_EMPLOYMENT_CRITERIA
2611 Description : This Function return 'Y' if employee is working continously
2612 between the given start date and end date
2613 *****************************************************************************/
2614
2615 FUNCTION CHECK_EE_EMPLOYMENT_CRITERIA
2616 (
2617 P_CTX_ASSIGNMENT_ID NUMBER,
2618 P_CTX_DATE_EARNED DATE
2619 ) RETURN VARCHAR2 AS
2620
2621 CURSOR csr_get_dates IS
2622 SELECT fnd_date.canonical_to_date(TO_CHAR(P_CTX_DATE_EARNED,'YYYY')
2623 ||'/'||hoi.org_information7),
2624 fnd_date.canonical_to_date(TO_CHAR(P_CTX_DATE_EARNED,'YYYY')
2625 ||'/'||hoi.org_information8),
2626 paf.person_id
2627 FROM hr_organization_information hoi
2628 ,per_assignments_f paf
2629 WHERE hoi.organization_id =
2630 hr_mx_utility.get_legal_employer(paf.business_group_id,
2631 per_mx_ssaffl_archive.derive_gre_from_loc_scl(
2632 paf.location_id
2633 ,paf.business_group_id
2634 ,paf.soft_coding_keyflex_id
2635 ,p_ctx_date_earned),
2636 p_ctx_date_earned)
2637 AND hoi.org_information_context = 'MX_TAX_REGISTRATION'
2638 AND paf.assignment_id = P_CTX_ASSIGNMENT_ID
2639 AND P_CTX_DATE_EARNED BETWEEN paf.effective_start_date
2640 AND paf.effective_end_date;
2641
2642 CURSOR csr_get_form37_status (p_start_date date,p_end_date date) IS
2643 SELECT 'N'
2644 FROM pay_assignment_actions paa
2645 ,pay_payroll_actions ppa
2646 WHERE paa.payroll_action_id=ppa.payroll_action_id
2647 AND paa.assignment_id =P_CTX_ASSIGNMENT_ID
2648 AND ppa.report_type='ISR_TAX_FORMAT37'
2649 AND ppa.date_earned BETWEEN p_start_date AND p_end_date;
2650
2651 CURSOR c_get_hire_date ( cp_person_id NUMBER
2652 ,cp_effective_date DATE ) IS
2653 SELECT MAX (pps.date_start), MAX(nvl(actual_termination_date,
2654 fnd_date.canonical_to_date('4712/12/31')))
2655 FROM per_periods_of_service pps
2656 WHERE pps.person_id = cp_person_id
2657 AND pps.date_start <= cp_effective_date;
2658 --bug 7604298
2659 CURSOR c_get_prev_term_date ( cp_person_id NUMBER
2660 ,cp_effective_date DATE ) IS
2661 SELECT MAX(actual_termination_date)
2662 FROM per_periods_of_service pps
2663 WHERE pps.person_id = cp_person_id
2664 AND pps.date_start <= cp_effective_date
2665 AND pps.actual_termination_date IS NOT NULL;
2666
2667
2668 l_proc_name VARCHAR2(100);
2669 l_flag VARCHAR2(4);
2670 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
2671 l_end_date DATE;
2672 l_start_date DATE;
2673 l_hire_date DATE;
2674 l_termination_date DATE;
2675 ln_person_id NUMBER;
2676 l_prev_term_date DATE;
2677
2678 BEGIN
2679 l_proc_name := g_proc_name ||'CHECK_EE_EMPLOYMENT_CRITERIA';
2680 hr_utility_trace('Entering '||l_proc_name);
2681 hr_utility_trace('P_CTX_ASSIGNMENT_ID = ' || p_ctx_assignment_id);
2682
2683 l_flag :='N';
2684 l_start_date := NULL;
2685
2686 OPEN csr_get_dates;
2687 FETCH csr_get_dates INTO l_start_date,
2688 l_end_date,
2689 ln_person_id;
2690 CLOSE csr_get_dates;
2691
2692
2693 HR_UTILITY.trace('l_start_date: '||l_start_date);
2694 HR_UTILITY.trace('l_end_date: '||l_end_date);
2695
2696 OPEN c_get_hire_date(ln_person_id,P_CTX_DATE_EARNED);
2697 FETCH c_get_hire_date INTO l_hire_date, l_termination_date;
2698 CLOSE c_get_hire_date;
2699
2700 HR_UTILITY.trace('l_hire_date: '||l_hire_date);
2701 HR_UTILITY.trace('l_termination_date: '||l_termination_date);
2702
2703 IF l_hire_date > l_start_date AND l_termination_date >= l_end_date THEN
2704 /*Added for bug 7604298 to pick up the re-hire employee when re-hire happens in next day*/
2705 HR_UTILITY.trace('About to check the re-hire condition ');
2706 OPEN c_get_prev_term_date(ln_person_id,P_CTX_DATE_EARNED);
2707 FETCH c_get_prev_term_date INTO l_prev_term_date;
2708 CLOSE c_get_prev_term_date;
2709 HR_UTILITY.trace('l_prev_term_date: '||l_prev_term_date);
2710 IF (l_prev_term_date IS NOT NULL) AND ((l_prev_term_date +1) = l_hire_date) THEN
2711 hr_utility_trace('Person re-hired in the next day ');
2712 RETURN ('Y');
2713 END IF;
2714
2715 hr_utility_trace('Person hired after ' || l_start_date);
2716 hr_utility_trace('Leaving '||l_proc_name);
2717 RETURN ('N');
2718 END IF;
2719
2720 IF l_termination_date < l_end_date THEN
2721 hr_utility_trace('Person terminated before ' || l_end_date );
2722 hr_utility_trace('Leaving '||l_proc_name);
2723 RETURN ('N');
2724 END IF;
2725
2726 IF l_start_date IS NOT NULL THEN
2727 OPEN csr_get_form37_status(l_start_date,l_end_date);
2728 FETCH csr_get_form37_status INTO l_flag;
2729 IF csr_get_form37_status%NOTFOUND THEN
2730 CLOSE csr_get_form37_status;
2731 hr_utility_trace('Leaving '||l_proc_name);
2732 RETURN 'Y';
2733 END IF;
2734 CLOSE csr_get_form37_status;
2735 END IF;
2736
2737 hr_utility_trace('l_flag: ' ||l_flag);
2738 hr_utility_trace('Leaving '||l_proc_name);
2739
2740 RETURN (l_flag);
2741
2742 END CHECK_EE_EMPLOYMENT_CRITERIA;
2743
2744
2745
2746 /****************************************************************************
2747 Name : IS_ASG_EXEMPT_FROM_ISR
2748 Description : This function returns Y if an assignment is exempted from ISR
2749 calculation
2750 *****************************************************************************/
2751
2752 FUNCTION IS_ASG_EXEMPT_FROM_ISR
2753 (
2754 P_CTX_ASSIGNMENT_ID NUMBER
2755 ,P_CTX_DATE_EARNED DATE
2756 ) RETURN VARCHAR2 AS
2757
2758 CURSOR get_isr_entry
2759 IS
2760 SELECT 'Y'
2761 FROM pay_element_entries_f pee
2762 ,pay_element_types_f pet
2763 ,pay_element_entry_values_f pev
2764 ,pay_input_values_f piv
2765 WHERE assignment_id=P_CTX_ASSIGNMENT_ID
2766 AND pee.element_type_id=pet.element_type_id
2767 AND pev.element_entry_id=pee.element_entry_id
2768 AND piv.input_value_id = pev.input_value_id
2769 AND pet.element_name = 'Mexico Tax'
2770 AND piv.name ='Exempt ISR Tax'
2771 AND pev.screen_entry_value='Y'
2772 AND P_CTX_DATE_EARNED BETWEEN pee.effective_start_date AND pee.effective_end_date
2773 AND P_CTX_DATE_EARNED BETWEEN pev.effective_start_date AND pev.effective_end_date;
2774
2775
2776
2777 l_flag VARCHAR2(4);
2778 l_value NUMBER;
2779
2780 BEGIN
2781
2782 l_flag :='N';
2783 hr_utility_trace('Entering ..IS_ASG_EXEMPT_FROM_ISR');
2784 OPEN get_isr_entry;
2785 FETCH get_isr_entry INTO l_flag;
2786 IF get_isr_entry%NOTFOUND THEN
2787 CLOSE get_isr_entry;
2788 hr_utility_trace('Leaving ..IS_ASG_EXEMPT_FROM_ISR');
2789 RETURN 'N';
2790 END IF;
2791 CLOSE get_isr_entry;
2792
2793 hr_utility_trace('l_flag: ' ||l_flag);
2794 hr_utility_trace('Leaving ..IS_ASG_EXEMPT_FROM_ISR');
2795
2796 RETURN (l_flag);
2797
2798 END IS_ASG_EXEMPT_FROM_ISR;
2799
2800
2801 /****************************************************************************
2802 Name : IS_PER_EXEMPT_FROM_ADJ
2803 Description : This function returns Y if an assignment is exempted from Tax
2804 Adjustment
2805 *****************************************************************************/
2806
2807 FUNCTION IS_PER_EXEMPT_FROM_ADJ
2808 (
2809 P_CTX_ASSIGNMENT_ID NUMBER
2810 ,P_CTX_DATE_EARNED DATE
2811 ) RETURN VARCHAR2 AS
2812
2813 CURSOR get_person_id
2814 IS
2815 SELECT person_id
2816 FROM per_all_assignments_f
2817 WHERE assignment_id=P_CTX_ASSIGNMENT_ID
2818 AND P_CTX_DATE_EARNED BETWEEN effective_start_date AND effective_end_date;
2819
2820 CURSOR get_exempt_adjustment(p_person_id per_all_people_f.person_id%TYPE)
2821 IS
2822 SELECT pei_information1
2823 FROM per_people_extra_info
2824 WHERE person_id = p_person_id
2825 AND information_type='MX_EMP_TAX_SIGNUP'
2826 AND P_CTX_DATE_EARNED BETWEEN fnd_date.canonical_to_date(pei_information2)
2827 AND fnd_date.canonical_to_date(pei_information3);
2828
2829 l_flag VARCHAR2(4);
2830 l_person_id per_all_people_f.person_id%TYPE;
2831 l_exempt_adj per_people_extra_info.pei_information1%TYPE;
2832
2833 BEGIN
2834
2835 l_flag:='N';
2836 hr_utility_trace('Entering ..IS_PER_EXEMPT_FROM_ADJ');
2837 OPEN get_person_id;
2838 FETCH get_person_id INTO l_person_id;
2839 CLOSE get_person_id;
2840
2841 OPEN get_exempt_adjustment(l_person_id);
2842 FETCH get_exempt_adjustment INTO l_exempt_adj;
2843 IF get_exempt_adjustment%NOTFOUND THEN
2844 CLOSE get_exempt_adjustment;
2845 hr_utility_trace('Leaving ..IS_PER_EXEMPT_FROM_ADJ');
2846 RETURN 'N';
2847 END IF;
2848 CLOSE get_exempt_adjustment;
2849
2850 IF l_exempt_adj ='Y' THEN
2851 l_flag:='Y';
2852 ELSE
2853 l_flag:='N';
2854 END IF;
2855
2856 hr_utility_trace('l_flag: ' ||l_flag);
2857 hr_utility_trace('Leaving ..IS_PER_EXEMPT_FROM_ADJ');
2858
2859 RETURN (l_flag);
2860
2861 END IS_PER_EXEMPT_FROM_ADJ;
2862
2863 /****************************************************************************
2864 Name : GET_MX_ECON_ZONE
2865 Description : This function returns Economy Zone('A', 'B', 'C') for the
2866 given tax_unit_id
2867 *****************************************************************************/
2868
2869 FUNCTION GET_MX_ECON_ZONE
2870 (
2871 P_CTX_TAX_UNIT_ID number,
2872 P_CTX_DATE_EARNED DATE
2873 ) RETURN varchar2 AS
2874
2875 CURSOR get_econ_zone
2876 IS
2877 SELECT hoi.org_information7
2878 FROM hr_organization_units hou,
2879 hr_organization_information hoi
2880 WHERE hou.organization_id = hoi.organization_id
2881 AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
2882 AND hou.organization_id = P_CTX_TAX_UNIT_ID
2883 AND P_CTX_DATE_EARNED BETWEEN hou.date_from
2884 AND NVL(hou.date_to, hr_general.end_of_time);
2885
2886 l_econ_zone varchar2(2);
2887
2888 BEGIN
2889
2890
2891 OPEN get_econ_zone;
2892 FETCH get_econ_zone INTO l_econ_zone;
2893 CLOSE get_econ_zone;
2894
2895 RETURN (l_econ_zone);
2896 END GET_MX_ECON_ZONE;
2897
2898 /****************************************************************************
2899 Name : GET_MIN_WAGE
2900 Description : This function returns Minimum Wage for the Economy Zone
2901 *****************************************************************************/
2902
2903 FUNCTION GET_MIN_WAGE
2904 (
2905 P_CTX_DATE_EARNED DATE,
2906 P_TAX_BASIS varchar2,
2907 P_ECON_ZONE varchar2
2908
2909 ) RETURN varchar2 AS
2910
2911 CURSOR get_min_wage
2912 IS
2913 SELECT legislation_info2 FROM PAY_MX_LEGISLATION_INFO_F WHERE
2914 legislation_info1=
2915 DECODE(P_ECON_ZONE,'NONE','GMW','MW'||P_ECON_ZONE) AND
2916 legislation_info_type = 'MX Minimum Wage Information'
2917 AND P_CTX_DATE_EARNED BETWEEN effective_start_date AND effective_end_date;
2918
2919 l_min_wage number;
2920
2921 BEGIN
2922
2923 hr_utility_trace('Economy Zone '||P_ECON_ZONE);
2924 OPEN get_min_wage;
2925 FETCH get_min_wage INTO l_min_wage;
2926 CLOSE get_min_wage;
2927
2928
2929 RETURN (l_min_wage);
2930
2931 END GET_MIN_WAGE;
2932
2933 /****************************************************************************
2934 Name : CONVERT_INTO_MONTHLY_AVG_SAL
2935 Description : This function has
2936 1. Input Parameters as Contexts:
2937 - BUSINESS_GROUP_ID
2938 - TAX_UNIT_ID
2939 - PAYROLL_ID
2940 2. Input Parameters as Parameter:
2941 - SUBJECT_EARNINGS
2942 - DATE_EARNED
2943 *****************************************************************************/
2944
2945 FUNCTION CONVERT_INTO_MONTHLY_AVG_SAL ( p_business_group_id IN NUMBER
2946 ,p_tax_unit_id IN NUMBER
2947 ,p_payroll_id IN NUMBER
2948 ,p_subject_earnings IN NUMBER
2949 ,P_DATE_EARNED IN DATE
2950 ,p_hire_date IN DATE
2951 ,p_first_pay_date IN DATE
2952 ,p_period_days OUT NOCOPY NUMBER)
2953 RETURN NUMBER IS
2954
2955 ln_subject_earnings NUMBER;
2956 ln_days_in_a_month NUMBER;
2957 lv_period_type pay_all_payrolls_f.period_type%TYPE;
2958 ln_row_count NUMBER;
2959 lv_end_date varchar2(24);
2960 lv_start_date varchar2(24);
2961 ln_time_period_id number;
2962
2963 CURSOR csr_get_period_count_in_month IS
2964 select rownum mult_num,
2965 to_char(end_date,'yyyymmdd') period_end_mtd,
2966 to_char(start_date,'yyyymmdd') period_start_mtd,
2967 time_period_id
2968 from PER_TIME_PERIODS ptp
2969 where payroll_id = p_payroll_id
2970 and (to_char(p_date_earned,'yyyymm') = to_char(end_date,'yyyymm')
2971 or
2972 p_date_earned between start_date and end_date
2973 )
2974 AND p_hire_date <= end_date
2975 AND start_date >= NVL(p_first_pay_date,start_date)
2976 order by end_date;
2977
2978 BEGIN
2979
2980 hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_AVG_SAL');
2981 hr_utility_trace('p_subject_earnings: ' ||p_subject_earnings);
2982 hr_utility_trace('p_first_pay_date: ' ||to_char(p_first_pay_date));
2983 open csr_get_period_count_in_month;
2984 loop
2985 fetch csr_get_period_count_in_month into ln_row_count,
2986 lv_end_date,
2987 lv_start_date,
2988 ln_time_period_id;
2989 exit when csr_get_period_count_in_month%NOTFOUND;
2990
2991 hr_utility_trace('ln_row_count: ld_end_date' ||
2992 to_char(ln_row_count) ||' -- '||lv_end_date);
2993
2994 if to_char(P_DATE_EARNED,'yyyymmdd') <= lv_end_date and
2995 to_char(P_DATE_EARNED,'yyyymmdd') >= lv_start_date then
2996 ln_subject_earnings := p_subject_earnings * ln_row_count;
2997 p_period_days := ln_row_count;
2998 end if;
2999
3000 end loop;
3001 close csr_get_period_count_in_month;
3002
3003 hr_utility_trace('ln_subject_earnings: ' ||ln_subject_earnings);
3004 hr_utility_trace('Leaving ..CONVERT_INTO_MONTHLY_AVG_SAL');
3005
3006 RETURN ln_subject_earnings;
3007
3008 END CONVERT_INTO_MONTHLY_AVG_SAL;
3009
3010 function get_table_value (p_bus_group_id in number,
3011 p_table_name in varchar2,
3012 p_col_name in varchar2,
3013 p_row_value in varchar2,
3014 p_effective_date in date default null,
3015 p_period_days in number,
3016 p_period_number in number,
3017 p_period_type in varchar2)
3018 return varchar2 is
3019 l_effective_date date;
3020 l_range_or_match pay_user_tables.range_or_match%type;
3021 l_table_id pay_user_tables.user_table_id%type;
3022 l_value pay_user_column_instances_f.value%type;
3023 ln_period_days number;
3024 begin
3025 hr_utility_trace('p_bus_group_id: ' ||p_bus_group_id);
3026 hr_utility_trace('p_table_name: ' ||p_table_name);
3027 hr_utility_trace('p_col_name: ' ||p_col_name);
3028 hr_utility_trace('p_row_value: ' ||p_row_value);
3029 hr_utility_trace('p_effective_date: ' ||p_effective_date);
3030 hr_utility_trace('p_period_days: ' ||p_period_days);
3031 hr_utility_trace('p_period_number: ' ||p_period_number);
3032 hr_utility_trace('p_period_type: ' ||p_period_type);
3033 --
3034 -- Use either the supplied date, or the date from fnd_sessions
3035 --
3036 if (p_effective_date is not null) then
3037 l_effective_date := p_effective_date;
3038 end if;
3039 --
3040 -- get the type of query to be performed, either range or match
3041 --
3042 select range_or_match, user_table_id
3043 into l_range_or_match, l_table_id
3044 from pay_user_tables
3045 where upper(user_table_name) = upper(p_table_name)
3046 and nvl (business_group_id,
3047 p_bus_group_id) = p_bus_group_id
3048 and nvl(legislation_code, 'MX') = 'MX';
3049 --
3050 hr_utility_trace('l_range_or_match: ' ||l_range_or_match);
3051 hr_utility_trace('l_table_id: ' ||l_table_id);
3052 --
3053 if p_period_type in ('Calendar Month') or
3054 (p_period_type in ('Semi-Month') and p_period_number = 2) or
3055 (p_period_type = 'Ten Days' AND p_period_number = 3) then
3056 ln_period_days := 30.4;
3057 else
3058 ln_period_days := least(p_period_days * p_period_number,30.4);
3059 end if; --
3060 if (l_range_or_match <> 'M') then -- matched
3061 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
3062 INDEX(R PAY_USER_ROWS_F_FK1)
3063 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
3064 ORDERED */
3065 CINST.value
3066 into l_value
3067 from pay_user_tables TAB
3068 , pay_user_columns C
3069 , pay_user_rows_f R
3070 , pay_user_column_instances_f CINST
3071 where TAB.user_table_id = l_table_id
3072 and C.user_table_id = TAB.user_table_id
3073 and nvl (C.business_group_id,
3074 p_bus_group_id) = p_bus_group_id
3075 and nvl (C.legislation_code,
3076 'MX') = 'MX'
3077 and upper (C.user_column_name) = upper (p_col_name)
3078 and CINST.user_column_id = C.user_column_id
3079 and R.user_table_id = TAB.user_table_id
3080 and l_effective_date between R.effective_start_date
3081 and R.effective_end_date
3082 and nvl (R.business_group_id,
3083 p_bus_group_id) = p_bus_group_id
3084 and nvl (R.legislation_code,
3085 'MX') = 'MX'
3086 and fnd_number.canonical_to_number (p_row_value)
3087 between (fnd_number.canonical_to_number (R.row_low_range_or_name)/30.4) * ln_period_days
3088 and (fnd_number.canonical_to_number (R.row_high_range)/30.4) * ln_period_days
3089 and TAB.user_key_units = 'N'
3090 and CINST.user_row_id = R.user_row_id
3091 and l_effective_date between CINST.effective_start_date
3092 and CINST.effective_end_date
3093 and nvl (CINST.business_group_id,
3094 p_bus_group_id) = p_bus_group_id
3095 and nvl (CINST.legislation_code,
3096 'MX') = 'MX';
3097 --
3098 hr_utility_trace('l_value: ' ||l_value);
3099 return l_value;
3100
3101 end if;
3102
3103 end get_table_value;
3104
3105 BEGIN
3106 --hr_utility.trace_on (null, 'MX_IDC');
3107 g_proc_name := 'PAY_MX_TAX_FUNCTIONS.';
3108 g_debug := hr_utility.debug_enabled;
3109
3110 END PAY_MX_TAX_FUNCTIONS;