[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_MX_TAX_FUNCTIONS
Source
1 PACKAGE BODY PAY_MX_TAX_FUNCTIONS as
2 /* $Header: pymxtxfn.pkb 120.56.12020000.7 2013/02/01 06:11:57 jeisaac 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 02-Dec-2008 sjawid 115.53 7677805 Fixed the issue of incorrect isr tax on weekly
154 payroll. Modified Subsidy calculation logic.
155 27-Jan-2009 vvijayku 115.54 6785206 Periodic tax adjustment changes
156 added new overloaded funtion isr_tax_calculation
157 20-Feb-2009 vvijayku 115.55 8271515 Changed the code so that the Non Working days is
158 considered correctly while doing Tax Adjustment.
159 25-Feb-2009 vvijayku 115.56 8286044 Changed the balance dimensions for the Balances
160 called for Tax Adjustment from ASG_GRE_YTD/MTD to
161 PER_YTD/MTD.
162 8283620 Modified the ISR Subject balance call for Tax
163 Adjustment
164 26-Feb-2009 vvijayku 115.57 8286044 Added a new cursor to take the first hire date of
165 the employee so that the number of worked days is
166 calculated correctly.
167 30-Apr-2009 vvijayku 15.58 8291738 Changed the balance calls from PER_YTD AND PER_MTD
168 to ASG_MTD and ASG_YTD respectively for the Tax
169 Adjustment process and ASG_GRE_MTD to ASG_MTD for
170 subsidy calculation.
171 27-Oct-2009 sjawid 15.59 8438155 Added new cursor csr_get_compute_subsidy_flag in
172 function CALCULATE_ISR_TAX and added logic to
173 skip the subsidy for employment calculation when
174 user has a value 'No' in 'Compute Subsidy for Employment'
175 at 'MX Statutory Info' in assignment screen.
176 27-Oct-2009 sjawid 15.60 8438155 Added nvl function to the cursor
177 csr_get_compute_subsidy_flag in function
178 CALCULATE_ISR_TAX.
179 29-Oct-2009 sjawid 15.61 8438155 Multiple style comments found on one line
180 are removed to remove check_patch errors.
181 01-Nov-2009 sjawid 15.62 8932102 The cursor csr_get_payroll_id in Calculate_isr_tax function
182 has been modified to fetch only payroll_id and
183 removed hire_date reference and a new cursor
184 csr_get_hire_date has been added to fetch hire date.
185 21-Jul-2010 vvijayku 115.63 9753792 Added new code in the function GET_RANGE_BASIS_VALUE and
186 GET_PARTIAL_SUBJ_EARNINGS for handling the PTU calculation.
187 06-Jun-2011 vvijayku 115.64 12553943 Added fnd_number.canonical_to_number in the cursor
188 csr_get_table_value.
189 23-Apr-2012 sjawid 115.65 13905014 Modified ISR tax and Subsidy for employement calculation
190 for Monthly and Periodic Tax adjustments.
191 11-May-2012 jeisaac 115.67 13998818 Modified ISR Calculated for Monthly and Periodic Tax Adj.
192 28-May-2012 vvijayku 115.68 14094610 Added a new function get_prorated_rates to correctly fetch
193 and use the prorated table rates for the user table
194 ISR Subsidy for Empl_Month.
195 27-Dec-2012 rmugloo 115.69 16032260 Modified ISR calculated for Monthly and Periodic Tax
196 adjustments to take care of non periodic runs.
197 01-Feb-2012 jeisaac 115.70 15976084 Min Wage amount while calculating ISR Exempt for sub classification
198 'Overtime and Holidays Worked' should be taken as weekly limit.
199 *****************************************************************************/
200
201 TYPE g_leg_record IS RECORD (
202 effective_start_date pay_mx_legislation_info_f.effective_start_date%TYPE,
203 effective_end_date pay_mx_legislation_info_f.effective_end_date%TYPE,
204 jurisdiction_code pay_mx_legislation_info_f.jurisdiction_code%TYPE,
205 legislation_info_type pay_mx_legislation_info_f.legislation_info_type%TYPE,
206 legislation_info1 pay_mx_legislation_info_f.legislation_info1%TYPE,
207 legislation_info2 pay_mx_legislation_info_f.legislation_info2%TYPE,
208 legislation_info3 pay_mx_legislation_info_f.legislation_info3%TYPE,
209 legislation_info4 pay_mx_legislation_info_f.legislation_info4%TYPE,
210 legislation_info5 pay_mx_legislation_info_f.legislation_info5%TYPE,
211 legislation_info6 pay_mx_legislation_info_f.legislation_info6%TYPE);
212
213 TYPE g_isr_balances_rec IS RECORD (
214 assignment_action_id pay_assignment_actions.assignment_action_id%TYPE,
215 earnings_amt NUMBER,
216 exempt_amt NUMBER);
217
218 TYPE g_leg_table IS TABLE OF g_leg_record INDEX BY BINARY_INTEGER;
219 TYPE g_isr_balances_table IS TABLE OF g_isr_balances_rec INDEX BY BINARY_INTEGER;
220
221 g_proc_name VARCHAR2(50);
222 g_debug BOOLEAN;
223 g_isr_balances g_isr_balances_table;
224 g_pay_mx_legislation_info_f g_leg_table;
225
226 g_ptu_process BOOLEAN; -- Bug:9753792
227
228 /****************************************************************************
229 Name : HR_UTILITY_TRACE
230 Description : This function prints debug messages during diagnostics mode.
231 *****************************************************************************/
232
233 PROCEDURE HR_UTILITY_TRACE(trc_data varchar2) IS
234 BEGIN
235 IF g_debug THEN
236 hr_utility.trace(trc_data);
237 END IF;
238 END HR_UTILITY_TRACE;
239
240
241 /****************************************************************************
242 Name : GET_DEF_BAL_ID
243 Description : Function to get the defined_balance_id from a DBI name.
244 *****************************************************************************/
245
246 FUNCTION GET_DEF_BAL_ID
247 (
248 P_ENTITY_NAME VARCHAR2
249 ) RETURN NUMBER AS
250 l_defined_balance_id NUMBER;
251 l_proc_name VARCHAR2(100);
252 BEGIN
253 l_proc_name := g_proc_name ||'GET_DEF_BAL_ID';
254 hr_utility_trace('Entering '||l_proc_name);
255 SELECT creator_id
256 INTO l_defined_balance_id
257 FROM ff_user_entities
258 WHERE user_entity_name = p_entity_name
259 AND legislation_code = 'MX'
260 AND creator_type = 'B';
261
262 RETURN (l_defined_balance_id);
263 END GET_DEF_BAL_ID;
264
265
266 /****************************************************************************
267 Name : GET_RANGE_BASIS_VALUE
268 Description : This function takes MW/GMW/SAL as parameter and returns the
269 numerical values associated.
270 *****************************************************************************/
271 FUNCTION GET_RANGE_BASIS_VALUE
272 (
273 P_RANGE_BASIS VARCHAR2,
274 P_DAILY_SALARY NUMBER,
275 P_ASACT_ID NUMBER
276 ) RETURN NUMBER AS
277
278 CURSOR csr_get_asg_info IS
279 SELECT paf.soft_coding_keyflex_id,
280 paf.location_id,
281 ppa.effective_date,
282 paf.business_group_id
283 FROM per_assignments_f paf,
284 pay_assignment_actions paa,
285 pay_payroll_actions ppa
286 WHERE paf.assignment_id = paa.assignment_id
287 AND paa.payroll_action_id = ppa.payroll_action_id
288 AND paa.assignment_action_id = p_asact_id
289 AND ppa.effective_date BETWEEN paf.effective_start_date
290 AND paf.effective_end_date;
291
292 CURSOR csr_get_asg_info_temp IS
293 SELECT paf.soft_coding_keyflex_id,
294 paf.location_id,
295 ppa.effective_date,
296 paf.business_group_id
297 FROM per_assignments_f paf,
298 pay_temp_object_actions ptoa,
299 pay_payroll_actions ppa
300 WHERE paf.assignment_id = ptoa.object_id
301 AND ptoa.payroll_action_id = ppa.payroll_action_id
302 AND ptoa.object_action_id = p_asact_id
303 AND ptoa.object_type = 'ASG'
304 AND ppa.effective_date BETWEEN paf.effective_start_date
305 AND paf.effective_end_date;
306
307 CURSOR csr_get_asg_info_temp_ptu IS -- Bug:9753792
308 SELECT paf.soft_coding_keyflex_id,
309 paf.location_id,
310 ppa.effective_date,
311 paf.business_group_id
312 FROM per_assignments_f paf,
313 pay_temp_object_actions ptoa,
314 pay_payroll_actions ppa
315 WHERE paf.assignment_id = ptoa.object_id
316 AND ptoa.payroll_action_id = ppa.payroll_action_id
317 AND ptoa.object_action_id = p_asact_id
318 AND ptoa.object_type = 'ASG'
319 AND paf.effective_start_date <= g_ptu_end_date
320 AND paf.effective_end_date >= g_ptu_start_date;
321
322 CURSOR csr_get_min_wage (p_gre_id NUMBER, p_effective_date DATE) IS
323 SELECT fnd_number.canonical_to_number(plif.legislation_info2)
324 FROM pay_mx_legislation_info_f plif,
325 hr_organization_units hou,
326 hr_organization_information hoi
327 WHERE hou.organization_id = hoi.organization_id
328 AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
329 AND (DECODE (UPPER(p_range_basis),
330 'MW', 'MW'||hoi.org_information7) = plif.legislation_info1 OR
331 p_range_basis = plif.legislation_info1)
332 AND hou.organization_id = p_gre_id
333 AND plif.legislation_info_type = 'MX Minimum Wage Information'
334 AND p_effective_date BETWEEN hou.date_from
335 AND NVL(hou.date_to, hr_general.end_of_time)
336 AND p_effective_date BETWEEN plif.effective_start_date
337 AND plif.effective_end_date;
338
339 l_min_wage NUMBER;
340 l_scl_id NUMBER;
341 l_gre_id hr_organization_units.organization_id%type;
342 l_location_id hr_locations.location_id%type;
343 l_business_group_id NUMBER;
344 l_effective_date DATE;
345 l_is_ambiguous BOOLEAN;
346 l_missing_gre BOOLEAN;
347 l_proc_name VARCHAR2(100);
348
349 BEGIN
350 l_proc_name := g_proc_name ||'GET_RANGE_BASIS_VALUE';
351 hr_utility_trace('Entering '||l_proc_name);
352
353 IF p_range_basis = 'SAL' THEN
354
355 RETURN (p_daily_salary);
356
357 END IF;
358
359 IF g_temp_object_actions THEN
360 IF g_ptu_process THEN -- Bug:9753792
361 OPEN csr_get_asg_info_temp_ptu;
362 FETCH csr_get_asg_info_temp_ptu INTO l_scl_id,
363 l_location_id,
364 l_effective_date,
365 l_business_group_id;
366 CLOSE csr_get_asg_info_temp_ptu;
367 ELSE
368 OPEN csr_get_asg_info_temp;
369 FETCH csr_get_asg_info_temp INTO l_scl_id,
370 l_location_id,
371 l_effective_date,
372 l_business_group_id;
373 CLOSE csr_get_asg_info_temp;
374 END IF;
375 ELSE
376 OPEN csr_get_asg_info;
377 FETCH csr_get_asg_info INTO l_scl_id,
378 l_location_id,
379 l_effective_date,
380 l_business_group_id;
381 CLOSE csr_get_asg_info;
382
383 END IF;
384
385 l_gre_id := hr_mx_utility.get_gre_from_scl (l_scl_id);
386
387 hr_utility_trace('GRE obtained from SCL = "'||l_gre_id||'"');
388
389 IF l_gre_id is null THEN
390 l_gre_id := hr_mx_utility.get_gre_from_location(
391 l_location_id,
392 l_business_group_id, -- Bug 4129001
393 l_effective_date,
394 l_is_ambiguous,
395 l_missing_gre);
396 hr_utility_trace('GRE obtained from location = "'||l_gre_id||'"');
397 END IF;
398 /* Obtain (General) Minimum Wage */
399 OPEN csr_get_min_wage (l_gre_id, l_effective_date);
400 FETCH csr_get_min_wage INTO l_min_wage;
401 CLOSE csr_get_min_wage;
402 hr_utility_trace('(General) Minimum wage = '||l_min_wage);
403
404 hr_utility_trace('Leaving '||l_proc_name);
405 RETURN (l_min_wage);
406
407 END GET_RANGE_BASIS_VALUE;
408
409
410 --****************************************************************************
411 -- Name : GET_PARTIAL_SUBJ_EARNINGS
412 -- Description : This function calls another overloaded function, which returns
413 -- the portion of earnings that are partially subject to State
414 -- Tax and both fully and partially subject to ISR.
415 -- Both the YTD and PTD Earnings are defaulted to the
416 -- secondary classification earnings for the current run, which is
417 -- maintained in the PL-SQL table.
418 --****************************************************************************
419 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
420 (
421 P_CTX_EFFECTIVE_DATE DATE,
422 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
423 P_CTX_BUSINESS_GROUP_ID NUMBER,
424 P_CTX_JURISDICTION_CODE VARCHAR2,
425 P_CTX_ELEMENT_TYPE_ID NUMBER,
426 P_TAX_TYPE VARCHAR2,
427 P_EARNINGS_AMT NUMBER,
428 P_GROSS_EARNINGS NUMBER,
429 P_DAILY_SALARY NUMBER,
430 P_CLASSIFICATION_NAME VARCHAR2
431 )RETURN NUMBER AS
432
433 CURSOR get_ele_class IS
434 SELECT pec1.classification_id,
435 pec1.classification_name
436 FROM pay_element_classifications pec,
437 pay_element_classifications pec1, -- Secondary classification
438 pay_element_types_f pet,
439 pay_sub_classification_rules_f psr
440 WHERE pet.classification_id = pec.classification_id
441 AND pec.classification_id = pec1.parent_classification_id
442 AND pet.element_type_id = psr.element_type_id
443 AND psr.classification_id = pec1.classification_id
444 AND p_ctx_effective_date BETWEEN pet.effective_start_date
445 AND pet.effective_end_date
446 AND p_ctx_effective_date BETWEEN psr.effective_start_date
447 AND psr.effective_end_date
448 AND pet.element_type_id = p_ctx_element_type_id
449 AND pec.legislation_code = 'MX'
450 AND pec.classification_name <> 'Employer Liabilities'
451 AND pec.business_group_id IS NULL
452 AND pec1.legislation_code = 'MX'
453 AND pec1.business_group_id IS NULL
454 AND p_tax_type = 'ISR'
455 UNION
456 SELECT pec.classification_id,
457 pec.classification_name
458 FROM pay_element_classifications pec
459 WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
460 AND pec.legislation_code = 'MX'
461 AND pec.business_group_id IS NULL
462 AND pec.parent_classification_id IS NOT NULL
463 AND p_tax_type = 'STATE';
464
465 l_calc_rule pay_mx_earn_exemption_rules_f.calc_rule%TYPE;
466 l_low_exempt_factor pay_mx_earn_exemption_rules_f.low_exempt_factor%TYPE;
467 l_low_range_factor pay_mx_earn_exemption_rules_f.low_range_factor%TYPE;
468 l_low_range_basis pay_mx_earn_exemption_rules_f.low_range_basis%TYPE;
469 l_high_exempt_factor pay_mx_earn_exemption_rules_f.high_exempt_factor%TYPE;
470 l_high_range_factor pay_mx_earn_exemption_rules_f.high_range_factor%TYPE;
471 l_high_range_basis pay_mx_earn_exemption_rules_f.high_range_basis%TYPE;
472 l_classification_name pay_element_classifications.classification_name%TYPE;
473 l_classification_id NUMBER;
474 l_counter NUMBER;
475 l_proc_name VARCHAR2(100);
476 l_index NUMBER;
477 l_return_value NUMBER;
478 l_default_value NUMBER;
479 l_dummy VARCHAR2(1);
480
481 BEGIN
482
483 l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
484 hr_utility_trace('Entering '||l_proc_name);
485
486 l_counter := 0;
487 l_return_value := 0;
488
489 IF p_earnings_amt > 0 THEN
490
491 /* Perform exempt calculation only if earnings amount is greater
492 than zero. */
493 OPEN get_ele_class;
494 LOOP
495 FETCH get_ele_class INTO l_classification_id,
496 l_classification_name;
497 EXIT WHEN get_ele_class%NOTFOUND;
498
499 hr_utility_trace('Element classification name is '
500 ||l_classification_name);
501
502 IF l_classification_name LIKE '%:Subject to ISR' THEN
503 RETURN (P_EARNINGS_AMT);
504 END IF;
505
506 IF g_isr_balances.count() > 0 THEN
507 IF g_isr_balances(g_isr_balances.first()).assignment_action_id <>
508 p_ctx_assignment_action_id THEN
509 g_isr_balances.delete();
510 END IF;
511 END IF;
512
513 l_counter := 0;
514
515 BEGIN
516 -- Query to check if the classification is partially subject.
517 --
518 SELECT ''
519 INTO l_dummy
520 FROM pay_mx_earn_exemption_rules_f pmex
521 WHERE pmex.tax_type = p_tax_type
522 AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
523 pmex.state_code) = p_ctx_jurisdiction_code
524 AND pmex.element_classification_id = l_classification_id
525 AND p_ctx_effective_date BETWEEN pmex.effective_start_date
526 AND pmex.effective_end_date;
527
528 l_index := l_classification_id;
529
530 IF l_classification_name IN
531 ('Supplemental Earnings:Social Foresight Earnings',
532 'Imputed Earnings:Social Foresight Earnings') THEN
533 l_index := 0;
534 END IF;
535
536 BEGIN
537 l_default_value := nvl(g_isr_balances(l_index).earnings_amt, 0)
538 + P_EARNINGS_AMT;
539 EXCEPTION
540 WHEN NO_DATA_FOUND THEN
541 l_default_value := P_EARNINGS_AMT;
542 END;
543
544 l_return_value := get_partial_subj_earnings(
545 p_ctx_effective_date => P_CTX_EFFECTIVE_DATE,
546 p_ctx_assignment_action_id => P_CTX_ASSIGNMENT_ACTION_ID,
547 p_ctx_business_group_id => P_CTX_BUSINESS_GROUP_ID,
548 p_ctx_jurisdiction_code => P_CTX_JURISDICTION_CODE,
549 p_ctx_element_type_id => P_CTX_ELEMENT_TYPE_ID,
550 p_tax_type => P_TAX_TYPE,
551 p_earnings_amt => P_EARNINGS_AMT,
552 p_ytd_earnings_amt => l_default_value,
553 p_ptd_earnings_amt => l_default_value,
554 p_gross_earnings => P_GROSS_EARNINGS,
555 p_ytd_gross_earnings => P_GROSS_EARNINGS,
556 p_daily_salary => P_DAILY_SALARY,
557 p_classification_name => l_classification_name);
558
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 null;
562 END;
563
564 END LOOP;
565 CLOSE get_ele_class;
566 END IF;
567
568 RETURN (l_return_value);
569
570 END GET_PARTIAL_SUBJ_EARNINGS;
571
572
573 --****************************************************************************
574 -- Name : GET_PARTIAL_SUBJ_EARNINGS
575 -- Description : This function calls another overloaded function, which returns
576 -- the portion of earnings that are partially subject to State
577 -- Tax and both fully and partially subject to ISR.
578 -- The PTD Earnings are defaulted to the secondary classification
579 -- earnings for the current run, which is maintained in the PL-SQL
580 -- table.
581 --****************************************************************************
582 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
583 (
584 P_CTX_EFFECTIVE_DATE DATE,
585 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
586 P_CTX_BUSINESS_GROUP_ID NUMBER,
587 P_CTX_JURISDICTION_CODE VARCHAR2,
588 P_CTX_ELEMENT_TYPE_ID NUMBER,
589 P_TAX_TYPE VARCHAR2,
590 P_EARNINGS_AMT NUMBER,
591 P_YTD_EARNINGS_AMT NUMBER,
592 P_GROSS_EARNINGS NUMBER,
593 P_YTD_GROSS_EARNINGS NUMBER,
594 P_DAILY_SALARY NUMBER,
595 P_CLASSIFICATION_NAME VARCHAR2
596 ) RETURN NUMBER AS
597
598 CURSOR get_ele_class IS
599 SELECT pec1.classification_id,
600 pec1.classification_name
601 FROM pay_element_classifications pec,
602 pay_element_classifications pec1, -- Secondary classification
603 pay_element_types_f pet,
604 pay_sub_classification_rules_f psr
605 WHERE pet.classification_id = pec.classification_id
606 AND pec.classification_id = pec1.parent_classification_id
607 AND pet.element_type_id = psr.element_type_id
608 AND psr.classification_id = pec1.classification_id
609 AND p_ctx_effective_date BETWEEN pet.effective_start_date
610 AND pet.effective_end_date
611 AND p_ctx_effective_date BETWEEN psr.effective_start_date
612 AND psr.effective_end_date
613 AND pet.element_type_id = p_ctx_element_type_id
614 AND pec.legislation_code = 'MX'
615 AND pec.classification_name <> 'Employer Liabilities'
616 AND pec.business_group_id IS NULL
617 AND pec1.legislation_code = 'MX'
618 AND pec1.business_group_id IS NULL
619 AND p_tax_type = 'ISR'
620 UNION
621 SELECT pec.classification_id,
622 pec.classification_name
623 FROM pay_element_classifications pec
624 WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
625 AND pec.legislation_code = 'MX'
626 AND pec.business_group_id IS NULL
627 AND pec.parent_classification_id IS NOT NULL
628 AND p_tax_type = 'STATE';
629
630 l_calc_rule pay_mx_earn_exemption_rules_f.calc_rule%type;
631 l_low_exempt_factor pay_mx_earn_exemption_rules_f.low_exempt_factor%type;
632 l_low_range_factor pay_mx_earn_exemption_rules_f.low_range_factor%type;
633 l_low_range_basis pay_mx_earn_exemption_rules_f.low_range_basis%type;
634 l_high_exempt_factor pay_mx_earn_exemption_rules_f.high_exempt_factor%type;
635 l_high_range_factor pay_mx_earn_exemption_rules_f.high_range_factor%type;
636 l_high_range_basis pay_mx_earn_exemption_rules_f.high_range_basis%type;
637 l_classification_name pay_element_classifications.classification_name%type;
638 l_classification_id NUMBER;
639 l_counter NUMBER;
640 l_proc_name VARCHAR2(100);
641 l_index NUMBER;
642 l_return_value NUMBER;
643 l_default_value NUMBER;
644 l_dummy VARCHAR2(1);
645
646 BEGIN
647
648 l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
649 hr_utility_trace('Entering '||l_proc_name);
650
651 l_counter := 0;
652 l_return_value := 0;
653
654 IF p_earnings_amt > 0 THEN
655
656 /* Perform exempt calculation only if earnings amount is greater
657 than zero. */
658 OPEN get_ele_class;
659 LOOP
660 FETCH get_ele_class INTO l_classification_id,
661 l_classification_name;
662 EXIT WHEN get_ele_class%NOTFOUND;
663
664 hr_utility_trace('Element classification name is '
665 ||l_classification_name);
666
667 IF l_classification_name LIKE '%:Subject to ISR' THEN
668 RETURN (P_EARNINGS_AMT);
669 END IF;
670
671 IF g_isr_balances.count() > 0 THEN
672 IF ((g_isr_balances(g_isr_balances.first()).assignment_action_id <>
673 p_ctx_assignment_action_id) OR p_tax_type <> 'ISR') THEN
674 g_isr_balances.delete();
675 END IF;
676 END IF;
677
678 l_counter := 0;
679
680 BEGIN
681 -- Query to check if the classification is partially subject.
682 --
683 SELECT ''
684 INTO l_dummy
685 FROM pay_mx_earn_exemption_rules_f pmex
686 WHERE pmex.tax_type = p_tax_type
687 AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
688 pmex.state_code) = p_ctx_jurisdiction_code
689 AND pmex.element_classification_id = l_classification_id
690 AND p_ctx_effective_date BETWEEN pmex.effective_start_date
691 AND pmex.effective_end_date;
692
693 l_index := l_classification_id;
694
695 IF l_classification_name IN
696 ('Supplemental Earnings:Social Foresight Earnings',
697 'Imputed Earnings:Social Foresight Earnings') THEN
698 l_index := 0;
699 END IF;
700
701 BEGIN
702 l_default_value := nvl(g_isr_balances(l_index).earnings_amt, 0)
703 + P_EARNINGS_AMT;
704 EXCEPTION
705 WHEN NO_DATA_FOUND THEN
706 l_default_value := P_EARNINGS_AMT;
707 END;
708
709 l_return_value := get_partial_subj_earnings(
710 p_ctx_effective_date => P_CTX_EFFECTIVE_DATE,
711 p_ctx_assignment_action_id => P_CTX_ASSIGNMENT_ACTION_ID,
712 p_ctx_business_group_id => P_CTX_BUSINESS_GROUP_ID,
713 p_ctx_jurisdiction_code => P_CTX_JURISDICTION_CODE,
714 p_ctx_element_type_id => P_CTX_ELEMENT_TYPE_ID,
715 p_tax_type => P_TAX_TYPE,
716 p_earnings_amt => P_EARNINGS_AMT,
717 p_ytd_earnings_amt => P_YTD_EARNINGS_AMT,
718 p_ptd_earnings_amt => l_default_value,
719 p_gross_earnings => P_GROSS_EARNINGS,
720 p_ytd_gross_earnings => P_YTD_GROSS_EARNINGS,
721 p_daily_salary => P_DAILY_SALARY,
722 p_classification_name => l_classification_name);
723
724 EXCEPTION
725 WHEN NO_DATA_FOUND THEN
726 null;
727 END;
728
729 END LOOP;
730 CLOSE get_ele_class;
731 END IF;
732
733 RETURN (l_return_value);
734
735 END GET_PARTIAL_SUBJ_EARNINGS;
736
737
738 --******************************************************************************
739 -- Name : GET_SUBJ_EARNINGS_FOR_PERIOD
740 -- Description : This function calls another overloaded function, which returns
741 -- the portion of earnings that are partially subject to State
742 -- Tax and both fully and partially subject to ISR.
743 -- The YTD Earnings are defaulted to the secondary classification
744 -- earnings for the current run, which is maintained in the PL-SQL
745 -- table.
746 --******************************************************************************
747 FUNCTION GET_SUBJ_EARNINGS_FOR_PERIOD
748 (
749 P_CTX_EFFECTIVE_DATE DATE,
750 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
751 P_CTX_BUSINESS_GROUP_ID NUMBER,
752 P_CTX_JURISDICTION_CODE VARCHAR2,
753 P_CTX_ELEMENT_TYPE_ID NUMBER,
754 P_TAX_TYPE VARCHAR2,
755 P_EARNINGS_AMT NUMBER,
756 P_PTD_EARNINGS_AMT NUMBER,
757 P_GROSS_EARNINGS NUMBER,
758 P_YTD_GROSS_EARNINGS NUMBER,
759 P_DAILY_SALARY NUMBER,
760 P_CLASSIFICATION_NAME VARCHAR2
761 ) RETURN NUMBER AS
762
763 CURSOR get_ele_class IS
764 SELECT pec1.classification_id,
765 pec1.classification_name
766 FROM pay_element_classifications pec,
767 pay_element_classifications pec1, -- Secondary classification
768 pay_element_types_f pet,
769 pay_sub_classification_rules_f psr
770 WHERE pet.classification_id = pec.classification_id
771 AND pec.classification_id = pec1.parent_classification_id
772 AND pet.element_type_id = psr.element_type_id
773 AND psr.classification_id = pec1.classification_id
774 AND p_ctx_effective_date BETWEEN pet.effective_start_date
775 AND pet.effective_end_date
776 AND p_ctx_effective_date BETWEEN psr.effective_start_date
777 AND psr.effective_end_date
778 AND pet.element_type_id = p_ctx_element_type_id
779 AND pec.legislation_code = 'MX'
780 AND pec.classification_name <> 'Employer Liabilities'
781 AND pec.business_group_id IS NULL
782 AND pec1.legislation_code = 'MX'
783 AND pec1.business_group_id IS NULL
784 AND p_tax_type = 'ISR'
785 UNION
786 SELECT pec.classification_id,
787 pec.classification_name
788 FROM pay_element_classifications pec
789 WHERE UPPER(pec.classification_name) = UPPER(p_classification_name)
790 AND pec.legislation_code = 'MX'
791 AND pec.business_group_id IS NULL
792 AND pec.parent_classification_id IS NOT NULL
793 AND p_tax_type = 'STATE';
794
795 l_calc_rule pay_mx_earn_exemption_rules_f.calc_rule%TYPE;
796 l_low_exempt_factor pay_mx_earn_exemption_rules_f.low_exempt_factor%TYPE;
797 l_low_range_factor pay_mx_earn_exemption_rules_f.low_range_factor%TYPE;
798 l_low_range_basis pay_mx_earn_exemption_rules_f.low_range_basis%TYPE;
799 l_high_exempt_factor pay_mx_earn_exemption_rules_f.high_exempt_factor%TYPE;
800 l_high_range_factor pay_mx_earn_exemption_rules_f.high_range_factor%TYPE;
801 l_high_range_basis pay_mx_earn_exemption_rules_f.high_range_basis%TYPE;
802 l_classification_name pay_element_classifications.classification_name%TYPE;
803 l_classification_id NUMBER;
804 l_counter NUMBER;
805 l_proc_name VARCHAR2(100);
806 l_index NUMBER;
807 l_return_value NUMBER;
808 l_default_value NUMBER;
809 l_dummy VARCHAR2(1);
810
811 BEGIN
812
813 l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
814 hr_utility_trace('Entering '||l_proc_name);
815
816 l_counter := 0;
817 l_return_value := 0;
818
819 IF p_earnings_amt > 0 THEN
820
821 /* Perform exempt calculation only if earnings amount is greater
822 than zero. */
823 OPEN get_ele_class;
824 LOOP
825 FETCH get_ele_class INTO l_classification_id,
826 l_classification_name;
827 EXIT WHEN get_ele_class%NOTFOUND;
828
829 hr_utility_trace('Element classification name is '
830 ||l_classification_name);
831
832 IF l_classification_name LIKE '%:Subject to ISR' THEN
833 RETURN (P_EARNINGS_AMT);
834 END IF;
835
836 IF g_isr_balances.count() > 0 THEN
837 IF g_isr_balances(g_isr_balances.first()).assignment_action_id <>
838 p_ctx_assignment_action_id THEN
839 g_isr_balances.delete();
840 END IF;
841 END IF;
842
843 l_counter := 0;
844
845 BEGIN
846 -- Query to check if the classification is partially subject.
847 --
848 SELECT ''
849 INTO l_dummy
850 FROM pay_mx_earn_exemption_rules_f pmex
851 WHERE pmex.tax_type = p_tax_type
852 AND DECODE(p_tax_type, 'ISR', p_ctx_jurisdiction_code,
853 pmex.state_code) = p_ctx_jurisdiction_code
854 AND pmex.element_classification_id = l_classification_id
855 AND p_ctx_effective_date BETWEEN pmex.effective_start_date
856 AND pmex.effective_end_date;
857
858 l_index := l_classification_id;
859
860 IF l_classification_name IN
861 ('Supplemental Earnings:Social Foresight Earnings',
862 'Imputed Earnings:Social Foresight Earnings') THEN
863 l_index := 0;
864 END IF;
865
866 BEGIN
867 l_default_value := nvl(g_isr_balances(l_index).earnings_amt, 0)
868 + P_EARNINGS_AMT;
869 EXCEPTION
870 WHEN NO_DATA_FOUND THEN
871 l_default_value := P_EARNINGS_AMT;
872 END;
873
874 l_return_value := get_partial_subj_earnings(
875 p_ctx_effective_date => P_CTX_EFFECTIVE_DATE,
876 p_ctx_assignment_action_id => P_CTX_ASSIGNMENT_ACTION_ID,
877 p_ctx_business_group_id => P_CTX_BUSINESS_GROUP_ID,
878 p_ctx_jurisdiction_code => P_CTX_JURISDICTION_CODE,
879 p_ctx_element_type_id => P_CTX_ELEMENT_TYPE_ID,
880 p_tax_type => P_TAX_TYPE,
881 p_earnings_amt => P_EARNINGS_AMT,
882 p_ytd_earnings_amt => l_default_value,
883 p_ptd_earnings_amt => P_PTD_EARNINGS_AMT,
884 p_gross_earnings => P_GROSS_EARNINGS,
885 p_ytd_gross_earnings => P_YTD_GROSS_EARNINGS,
886 p_daily_salary => P_DAILY_SALARY,
887 p_classification_name => l_classification_name);
888
889 EXCEPTION
890 WHEN NO_DATA_FOUND THEN
891 null;
892 END;
893
894 END LOOP;
895 CLOSE get_ele_class;
896 END IF;
897
898 RETURN (l_return_value);
899
900 END GET_SUBJ_EARNINGS_FOR_PERIOD;
901
902
903
904 /****************************************************************************
905 Name : GET_PARTIAL_SUBJ_EARNINGS
906 Description : This function calculates subject earnings for classifications
907 that are fully and partially subject to ISR and state taxes.
908 *****************************************************************************/
909 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
910 (
911 P_CTX_EFFECTIVE_DATE DATE,
912 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
913 P_CTX_BUSINESS_GROUP_ID NUMBER,
914 P_CTX_JURISDICTION_CODE VARCHAR2,
915 P_CTX_ELEMENT_TYPE_ID NUMBER,
916 P_TAX_TYPE VARCHAR2,
917 P_EARNINGS_AMT NUMBER,
918 P_YTD_EARNINGS_AMT NUMBER,
919 P_PTD_EARNINGS_AMT NUMBER,
920 P_GROSS_EARNINGS NUMBER,
921 P_YTD_GROSS_EARNINGS NUMBER,
922 P_DAILY_SALARY NUMBER,
923 P_CLASSIFICATION_NAME VARCHAR2
924 )RETURN NUMBER AS
925
926 CURSOR get_exempt_info (p_classification_id number) IS
927 SELECT pmex.calc_rule,
928 pmex.low_exempt_factor,
929 pmex.low_range_factor,
930 pmex.low_range_basis,
931 pmex.high_exempt_factor,
932 pmex.high_range_factor,
933 pmex.high_range_basis
934 FROM pay_mx_earn_exemption_rules_f pmex
935 WHERE pmex.tax_type = p_tax_type
936 AND DECODE(p_tax_type, 'ISR',
937 p_ctx_jurisdiction_code,
938 pmex.state_code) = p_ctx_jurisdiction_code
939 AND pmex.element_classification_id = p_classification_id
940 AND p_ctx_effective_date BETWEEN pmex.effective_start_date
941 AND pmex.effective_end_date;
942
943 CURSOR get_days_per_period IS
944 SELECT TRUNC(ptp.end_date - ptp.start_date) + 1 days,
945 ppf.payroll_id,
946 paa.tax_unit_id,
947 paa.assignment_id
948 FROM pay_payrolls_f ppf,
949 per_time_periods ptp,
950 pay_assignment_actions paa,
951 pay_payroll_actions ppa
952 WHERE ptp.payroll_id = ppf.payroll_id
953 AND ppf.payroll_id = ppa.payroll_id
954 AND ppa.payroll_action_id = paa.payroll_action_id
955 AND paa.assignment_action_id = p_ctx_assignment_action_id
956 AND ppa.effective_date BETWEEN ptp.start_date
957 AND ptp.end_date
958 AND ppa.effective_date BETWEEN ppf.effective_start_date
959 AND ppf.effective_end_date;
960
961 CURSOR get_days_per_period_temp IS
962 SELECT TRUNC(ptp.end_date - ptp.start_date) + 1 days,
963 paf.payroll_id,
964 -- paa.tax_unit_id,
965 paf.assignment_id
966 FROM per_assignments_f paf,
967 per_time_periods ptp,
968 pay_temp_object_actions ptoa,
969 pay_payroll_actions ppa
970 WHERE ptp.payroll_id = paf.payroll_id
971 AND ppa.payroll_action_id = ptoa.payroll_action_id
972 AND ptoa.object_id = paf.assignment_id
973 AND ptoa.object_type = 'ASG'
974 AND ptoa.object_action_id = p_ctx_assignment_action_id
975 AND ppa.effective_date BETWEEN ptp.start_date
976 AND ptp.end_date
977 AND ppa.effective_date BETWEEN paf.effective_start_date
978 AND paf.effective_end_date;
979
980 CURSOR get_days_per_period_temp_ptu (cp_start_date DATE,
981 cp_end_date DATE) IS -- Bug:9753792
982 SELECT TRUNC(ptp.end_date - ptp.start_date) + 1 days,
983 paf.payroll_id,
984 -- paa.tax_unit_id,
985 paf.assignment_id
986 FROM per_assignments_f paf,
987 per_time_periods ptp,
988 pay_temp_object_actions ptoa,
989 pay_payroll_actions ppa
990 WHERE ptp.payroll_id = paf.payroll_id
991 AND ppa.payroll_action_id = ptoa.payroll_action_id
992 AND ptoa.object_id = paf.assignment_id
993 AND ptoa.object_type = 'ASG'
994 AND ptoa.object_action_id = p_ctx_assignment_action_id
995 AND ppa.effective_date BETWEEN ptp.start_date
996 AND ptp.end_date
997 AND paf.effective_start_date <= cp_end_date
998 AND paf.effective_end_date >= cp_start_date;
999
1000 CURSOR get_person_id ( cp_assignment_id NUMBER
1001 ,cp_effective_date DATE) IS
1002 SELECT person_id
1003 FROM per_assignments_f paf
1004 WHERE paf.assignment_id = cp_assignment_id
1005 AND cp_effective_date BETWEEN paf.effective_start_date
1006 AND paf.effective_end_date;
1007
1008
1009
1010 l_calc_rule pay_mx_earn_exemption_rules_f.calc_rule%TYPE;
1011 l_low_exempt_factor pay_mx_earn_exemption_rules_f.low_exempt_factor%TYPE;
1012 l_low_range_factor pay_mx_earn_exemption_rules_f.low_range_factor%TYPE;
1013 l_low_range_basis pay_mx_earn_exemption_rules_f.low_range_basis%TYPE;
1014 l_high_exempt_factor pay_mx_earn_exemption_rules_f.high_exempt_factor%TYPE;
1015 l_high_range_factor pay_mx_earn_exemption_rules_f.high_range_factor%TYPE;
1016 l_high_range_basis pay_mx_earn_exemption_rules_f.high_range_basis%TYPE;
1017 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
1018 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
1019 l_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1020 ln_assignment_id pay_assignment_actions.assignment_id%TYPE;
1021 ln_person_id per_all_people_f.person_id%TYPE;
1022 l_classification_id NUMBER;
1023 l_hire_date DATE;
1024 l_exempt_amount NUMBER;
1025 l_counter NUMBER;
1026 l_sf_earnings NUMBER;
1027 l_total_earnings NUMBER;
1028 gmwa_1 NUMBER;
1029 gmwa_7 NUMBER;
1030 x NUMBER;
1031 l_middle NUMBER;
1032 l_proc_name VARCHAR2(100);
1033 l_days NUMBER;
1034 l_days_in_month NUMBER;
1035 l_days_in_year NUMBER;
1036 l_service_years NUMBER;
1037 l_index NUMBER;
1038 l_earnings_amt NUMBER;
1039 l_low_exempt_limit NUMBER;
1040 l_high_exempt_limit NUMBER;
1041 l_ytd_excl_current NUMBER;
1042 l_ptd_excl_current NUMBER;
1043
1044 BEGIN
1045
1046 l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
1047 hr_utility_trace('Entering '||l_proc_name);
1048
1049 hr_utility_trace('Parameters ....');
1050 hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
1051 hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1052 hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053 hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054 hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055 hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056 hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057 hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058 hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059 hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1060 hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
1061 hr_utility_trace('P_DAILY_SALARY = '|| P_DAILY_SALARY);
1062 hr_utility_trace('P_CLASSIFICATION_NAME = '|| P_CLASSIFICATION_NAME);
1063
1064
1065 l_exempt_amount := 0;
1066 l_counter := 0;
1067
1068 IF p_classification_name = 'Profit Sharing' THEN -- Bug:9753792
1069
1070 g_ptu_process := TRUE;
1071
1072 END IF;
1073
1074 IF p_earnings_amt > 0 THEN
1075
1076 l_counter := 0;
1077
1078 l_classification_id :=
1079 pay_mx_utility.get_classification_id(p_classification_name);
1080
1081 OPEN get_exempt_info(l_classification_id);
1082 LOOP
1083 FETCH get_exempt_info INTO l_calc_rule,
1084 l_low_exempt_factor,
1085 l_low_range_factor,
1086 l_low_range_basis,
1087 l_high_exempt_factor,
1088 l_high_range_factor,
1089 l_high_range_basis;
1090 EXIT WHEN get_exempt_info%NOTFOUND;
1091 l_counter := l_counter + 1;
1092 IF l_counter > 1 THEN
1093 hr_utility.set_message(801, 'PAY_MX_MULTI_TAX_SEC_CLASS');
1094 hr_utility.set_message_token(801,
1095 'ELEMENT_TYPE_ID',
1096 p_ctx_element_type_id);
1097 hr_utility.raise_error;
1098 END IF;
1099
1100 hr_utility_trace('l_calc_rule = '|| l_calc_rule);
1101 hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1102 hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1103 hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1104 hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1105 hr_utility_trace('l_high_range_factor = '|| l_high_range_factor);
1106 hr_utility_trace('l_high_range_basis = '|| l_high_range_basis);
1107
1108 l_index := l_classification_id;
1109
1110 IF p_classification_name IN
1111 ('Supplemental Earnings:Social Foresight Earnings',
1112 'Imputed Earnings:Social Foresight Earnings') THEN
1113 l_index := 0;
1114 END IF;
1115 --
1116 BEGIN
1117 g_isr_balances(l_index).earnings_amt := p_earnings_amt +
1118 g_isr_balances(l_index).earnings_amt;
1119 g_isr_balances(l_index).assignment_action_id :=
1120 p_ctx_assignment_action_id;
1121 --
1122 EXCEPTION
1123 WHEN NO_DATA_FOUND THEN
1124 g_isr_balances(l_index).earnings_amt := p_earnings_amt;
1125 g_isr_balances(l_index).assignment_action_id :=
1126 p_ctx_assignment_action_id;
1127 --
1128 END;
1129
1130 IF g_temp_object_actions THEN
1131
1132 IF g_ptu_process THEN -- Bug:9753792
1133
1134 OPEN get_days_per_period_temp_ptu (g_ptu_start_date, g_ptu_end_date);
1135 FETCH get_days_per_period_temp_ptu INTO l_days,
1136 l_payroll_id,
1137 -- l_tax_unit_id,
1138 ln_assignment_id;
1139 CLOSE get_days_per_period_temp_ptu;
1140
1141 pay_mx_rules.g_ptu_start_date := g_ptu_start_date; -- Bug:9753792
1142 pay_mx_rules.g_ptu_end_date := g_ptu_end_date; -- Bug:9753792
1143 pay_mx_rules.g_ptu_process := TRUE; -- Bug:9753792
1144
1145 pay_mx_rules.get_main_tax_unit_id(ln_assignment_id,
1146 p_ctx_effective_date,
1147 l_tax_unit_id);
1148 ELSE
1149
1150 OPEN get_days_per_period_temp;
1151 FETCH get_days_per_period_temp INTO l_days,
1152 l_payroll_id,
1153 -- l_tax_unit_id,
1154 ln_assignment_id;
1155 CLOSE get_days_per_period_temp;
1156
1157 pay_mx_rules.get_main_tax_unit_id(ln_assignment_id,
1158 p_ctx_effective_date,
1159 l_tax_unit_id);
1160 END IF;
1161
1162 ELSE
1163 OPEN get_days_per_period;
1164 FETCH get_days_per_period INTO l_days,
1165 l_payroll_id,
1166 l_tax_unit_id,
1167 ln_assignment_id;
1168 CLOSE get_days_per_period;
1169
1170 END IF;
1171
1172 hr_utility_trace('Days per period of payroll = '||l_days);
1173
1174 l_earnings_amt := g_isr_balances(l_index).earnings_amt;
1175
1176 OPEN get_person_id( ln_assignment_id, p_ctx_effective_date);
1177 FETCH get_person_id INTO ln_person_id;
1178 CLOSE get_person_id;
1179
1180 -- This represents the Exemption limit applicable on the
1181 -- Lower Range
1182 --
1183 l_low_exempt_limit := l_low_range_factor * get_range_basis_value(
1184 l_low_range_basis,
1185 p_daily_salary,
1186 p_ctx_assignment_action_id);
1187
1188 l_high_exempt_limit := l_high_range_factor * get_range_basis_value(
1189 l_high_range_basis,
1190 p_daily_salary,
1191 p_ctx_assignment_action_id);
1192
1193 l_ytd_excl_current := p_ytd_earnings_amt - p_earnings_amt;
1194
1195 l_ptd_excl_current := p_ptd_earnings_amt - p_earnings_amt;
1196
1197 -- Get number of days in year
1198 l_days_in_year := pay_mx_utility.get_days_in_year
1199 (p_ctx_business_group_id,
1200 l_tax_unit_id,
1201 l_payroll_id);
1202 -- Get number of days in month
1203 l_days_in_month := pay_mx_utility.get_days_in_month
1204 (p_ctx_business_group_id,
1205 l_tax_unit_id,
1206 l_payroll_id);
1207
1208 IF l_calc_rule = 'SINGLE_RANGE_DAILY' THEN
1209
1210 l_exempt_amount := LEAST(l_low_exempt_factor * p_earnings_amt,
1211 l_low_exempt_limit);
1212
1213 l_exempt_amount := l_exempt_amount -
1214 NVL(g_isr_balances(l_index).exempt_amt, 0);
1215
1216 ELSIF l_calc_rule = 'SINGLE_RANGE' THEN
1217
1218 l_low_exempt_limit := l_low_exempt_limit * l_days;
1219
1220 l_exempt_amount :=
1221 LEAST(l_low_exempt_factor * p_earnings_amt,
1222 l_low_exempt_limit - LEAST(
1223 l_low_exempt_limit,
1224 l_low_exempt_factor * l_ptd_excl_current
1225 )
1226 );
1227
1228 ELSIF l_calc_rule = 'SINGLE_RANGE_MONTH' THEN /*7604285*/
1229
1230 l_low_exempt_limit := l_low_exempt_limit * l_days_in_month;
1231
1232 l_exempt_amount :=
1233 LEAST(l_low_exempt_factor * p_earnings_amt,
1234 l_low_exempt_limit - LEAST(
1235 l_low_exempt_limit,
1236 l_low_exempt_factor * l_ptd_excl_current
1237 )
1238 );
1239
1240 ELSIF l_calc_rule = 'SINGLE_RANGE_ANNUAL' THEN
1241
1242 l_exempt_amount :=
1243 LEAST(l_low_exempt_factor * p_earnings_amt,
1244 l_low_exempt_limit - LEAST(
1245 l_low_exempt_limit,
1246 l_low_exempt_factor * l_ytd_excl_current
1247 )
1248 );
1249
1250 ELSIF l_calc_rule = 'SINGLE_RANGE_SENIORITY_ANNUAL' THEN
1251
1252 l_service_years := hr_mx_utility.get_seniority(
1253 p_business_group_id => p_ctx_business_group_id
1254 ,p_tax_unit_id => l_tax_unit_id
1255 ,p_payroll_id => l_payroll_id
1256 ,p_person_id => ln_person_id
1257 ,p_effective_date => p_ctx_effective_date);
1258
1259 -- Bug 4950628 - Corrected the calculation.
1260 --
1261 l_low_exempt_limit := l_low_exempt_limit * l_service_years;
1262 l_exempt_amount :=
1263 LEAST(l_low_exempt_factor * p_earnings_amt,
1264 l_low_exempt_limit - LEAST(
1265 l_low_exempt_limit,
1266 l_low_exempt_factor * l_ytd_excl_current
1267 )
1268 );
1269
1270 ELSIF l_calc_rule = 'MIN_OF_RANGES' THEN
1271
1272 -- For 'MIN_OF_RANGES', we assume that the low_exempt_factor
1273 -- equals the high_exempt_factor.
1274 --
1275 l_low_exempt_limit := l_low_exempt_limit * l_days;
1276 l_high_exempt_limit := l_high_exempt_limit * l_days;
1277
1278 IF l_low_exempt_limit < l_high_exempt_limit THEN
1279
1280 l_exempt_amount :=
1281 LEAST(l_low_exempt_factor * p_earnings_amt,
1282 l_low_exempt_limit - LEAST(
1283 l_low_exempt_limit,
1284 l_low_exempt_factor * l_ptd_excl_current
1285 )
1286 );
1287 ELSE
1288
1289 l_exempt_amount :=
1290 LEAST(l_high_exempt_factor * p_earnings_amt,
1291 l_high_exempt_limit - LEAST(
1292 l_high_exempt_limit,
1293 l_high_exempt_factor * l_ptd_excl_current
1294 )
1295 );
1296
1297 END IF;
1298
1299 ELSIF l_calc_rule = 'DOUBLE_RANGE_TOTAL_EARNINGS' THEN
1300
1301 IF p_daily_salary <= l_low_exempt_limit THEN
1302
1303 l_exempt_amount := l_low_exempt_factor * p_earnings_amt;
1304
1305 ELSE
1306
1307 --l_high_exempt_limit := l_high_exempt_limit * l_days; /*Bug 15976084*/
1308
1309 l_exempt_amount :=
1310 LEAST(l_high_exempt_factor * p_earnings_amt,
1311 l_high_exempt_limit - LEAST(
1312 l_high_exempt_limit,
1313 l_high_exempt_factor * l_ptd_excl_current
1314 )
1315 );
1316
1317 END IF;
1318
1319 ELSIF l_calc_rule = 'INCOME_PLUS_EXEMPT_LIMIT' THEN
1320
1321 ------------------------------------------------------------
1322 -- Algorithm for SF Exempt earnings:
1323 -- ---------------------------------
1324 -- 1. Obtain YTD social foresight earnings (SFE).
1325 -- 2. Compute X = 7 * GMWA - YTD gross earnings.
1326 --
1327 -- The Table showing the Exempt Portion is as follows:
1328 -- ==================================================
1329 -- | Case | Exempt Portion |
1330 -- ==================================================
1331 -- | X < GMWA < SFE | GMWA |
1332 -- --------------------------------------------------
1333 -- | X < SFE < GMWA | SFE |
1334 -- --------------------------------------------------
1335 -- | GMWA < X < SFE | X |
1336 -- --------------------------------------------------
1337 -- | GMWA < SFE < X | SFE |
1338 -- --------------------------------------------------
1339 -- | SFE < GMWA < X | SFE |
1340 -- --------------------------------------------------
1341 -- | SFE < X < GMWA | SFE |
1342 -- --------------------------------------------------
1343 --
1344 -- 3. Amount exempt from ISR =
1345 -- Min ( SFE, Median(x, SFE, 1GMWA) )
1346 ------------------------------------------------------------
1347
1348 -- Step 1
1349 l_sf_earnings := p_ytd_earnings_amt;
1350
1351 -- Step 2
1352 -- Add Gross Earnings to total SF earnings.
1353 l_total_earnings := l_sf_earnings +
1354 p_ytd_gross_earnings;
1355
1356 -- Step 3
1357 gmwa_1 := l_days_in_year * get_range_basis_value(
1358 'GMW',
1359 p_daily_salary,
1360 p_ctx_assignment_action_id);
1361 gmwa_7 := gmwa_1 * 7;
1362
1363 x := gmwa_7 - p_ytd_gross_earnings;
1364
1365 -- Now find the second largest number among
1366 -- x, l_sf_earnings and gmwa_1
1367
1368 l_middle := least (greatest(x, l_sf_earnings),
1369 greatest(l_sf_earnings, gmwa_1),
1370 greatest(gmwa_1, x)
1371 );
1372
1373 l_exempt_amount :=
1374 least (l_sf_earnings, l_middle) * l_days / l_days_in_year;
1375
1376 l_exempt_amount := l_exempt_amount -
1377 nvl(g_isr_balances(l_index).exempt_amt, 0);
1378
1379 g_isr_balances(l_index).exempt_amt :=
1380 nvl(g_isr_balances(l_index).exempt_amt, 0) + l_exempt_amount;
1381
1382 hr_utility_trace('Subject amount for ' ||
1383 p_classification_name || ' = ' ||
1384 to_char(l_sf_earnings - l_exempt_amount)
1385 );
1386
1387 hr_utility_trace('Leaving '||l_proc_name);
1388
1389 -- Return the subject amount.
1390 RETURN (l_sf_earnings - l_exempt_amount);
1391
1392 END IF;
1393
1394 END LOOP;
1395
1396 CLOSE get_exempt_info;
1397
1398 END IF;
1399 --
1400 IF g_isr_balances.EXISTS(l_index) THEN
1401
1402 g_isr_balances(l_index).exempt_amt :=
1403 NVL(g_isr_balances(l_index).exempt_amt, 0) + l_exempt_amount;
1404
1405 END IF;
1406 --
1407 hr_utility_trace('Subject amount for ' ||
1408 p_classification_name || ' = ' ||
1409 TO_CHAR(p_earnings_amt - l_exempt_amount)
1410 );
1411
1412 hr_utility_trace('Leaving '||l_proc_name);
1413 --
1414 IF p_earnings_amt >= l_exempt_amount THEN
1415
1416 RETURN (p_earnings_amt - l_exempt_amount);
1417
1418 ELSE
1419
1420 RETURN (0);
1421
1422 END IF;
1423 --
1424 END GET_PARTIAL_SUBJ_EARNINGS;
1425
1426 /****************************************************************************
1427 Name : GET_PREVIOUS_PERIOD_BAL
1428 Description : This function returns balance values for pay period
1429 immediately previous to the period in which passed
1430 assignment_action_id lies.
1431 *****************************************************************************/
1432
1433 FUNCTION GET_PREVIOUS_PERIOD_BAL
1434 (
1435 P_CTX_ASSIGNMENT_ID NUMBER,
1436 P_CTX_ASSIGNMENT_ACTION_ID NUMBER,
1437 P_MODE VARCHAR2
1438 ) RETURN NUMBER AS
1439
1440 CURSOR get_previous_assact IS
1441 SELECT paa.assignment_action_id
1442 FROM pay_assignment_actions paa,
1443 pay_payroll_actions ppa
1444 WHERE paa.assignment_id = p_ctx_assignment_id
1445 AND paa.payroll_action_id = ppa.payroll_action_id
1446 AND paa.action_sequence =
1447 (SELECT max(paa_prev.action_sequence)
1448 FROM per_time_periods ptp
1449 , pay_payroll_actions ppa1
1450 , pay_assignment_actions paa1
1451 , per_time_periods ptp_prev
1452 , pay_payroll_actions ppa_prev
1453 , pay_assignment_actions paa_prev
1454 WHERE paa1.assignment_action_id = p_ctx_assignment_action_id
1455 AND ppa1.payroll_action_id = paa1.payroll_action_id
1456 AND ppa1.effective_date BETWEEN ptp.start_date
1457 AND ptp.end_date
1458 AND ptp.payroll_id = ppa1.payroll_id
1459 AND ptp_prev.payroll_id = ppa1.payroll_id
1460 AND (ptp.start_date - 1) BETWEEN ptp_prev.start_date
1461 AND ptp_prev.end_date
1462 AND paa_prev.assignment_id = paa1.assignment_id
1463 AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
1464 AND ppa_prev.action_type IN ('R', 'Q', 'B')
1465 AND ppa_prev.effective_date BETWEEN ptp_prev.start_date
1466 AND ptp_prev.end_date);
1467
1468 l_prev_assact pay_assignment_actions.assignment_action_id%TYPE;
1469 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
1470 l_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1471 l_counter NUMBER;
1472 l_balance_value NUMBER;
1473 l_proc_name VARCHAR2(100);
1474
1475 BEGIN
1476
1477 l_proc_name := g_proc_name ||'GET_PREVIOUS_PERIOD_BAL';
1478 hr_utility_trace('Entering '||l_proc_name);
1479
1480 l_balance_value := 0;
1481 OPEN get_previous_assact;
1482 FETCH get_previous_assact INTO l_prev_assact;
1483 CLOSE get_previous_assact;
1484
1485 hr_utility_trace('Previous assignment action id = '||l_prev_assact);
1486
1487 IF l_prev_assact IS NOT NULL THEN /* Bug 4283490 */
1488 l_counter := 1;
1489
1490 l_balance_value_tab(l_counter).defined_balance_id := get_def_bal_id (p_mode);
1491 l_balance_value_tab(l_counter).balance_value := 0;
1492 pay_balance_pkg.get_value (
1493 p_assignment_action_id => l_prev_assact,
1494 p_defined_balance_lst => l_balance_value_tab,
1495 p_get_rr_route => FALSE,
1496 p_get_rb_route => FALSE);
1497 l_balance_value := l_balance_value_tab(1).balance_value;
1498 END IF;
1499
1500 hr_utility_trace('Return value = '||l_balance_value);
1501 hr_utility_trace('Leaving '||l_proc_name);
1502
1503 RETURN (l_balance_value);
1504
1505 END GET_PREVIOUS_PERIOD_BAL;
1506
1507
1508 /****************************************************************************
1509 Name : GET_MX_TAX_INFO
1510 Description : This function returns various parameters required for social
1511 security quota and state tax calculation.
1512 *****************************************************************************/
1513
1514 FUNCTION GET_MX_TAX_INFO
1515 (
1516 P_CTX_BUSINESS_GROUP_ID NUMBER,
1517 P_CTX_TAX_UNIT_ID NUMBER,
1518 P_CTX_EFFECTIVE_DATE DATE,
1519 P_CTX_JURISDICTION_CODE VARCHAR2,
1520 P_LEGISLATION_INFO_TYPE VARCHAR2,
1521 P_LEGISLATION_INFO1 IN OUT NOCOPY VARCHAR2,
1522 P_LEGISLATION_INFO2 OUT NOCOPY VARCHAR2,
1523 P_LEGISLATION_INFO3 OUT NOCOPY VARCHAR2,
1524 P_LEGISLATION_INFO4 OUT NOCOPY VARCHAR2,
1525 P_LEGISLATION_INFO5 OUT NOCOPY VARCHAR2,
1526 P_LEGISLATION_INFO6 OUT NOCOPY VARCHAR2
1527 ) RETURN NUMBER AS
1528
1529 CURSOR csr_get_ss_info IS
1530 SELECT effective_start_date,
1531 effective_end_date,
1532 jurisdiction_code,
1533 legislation_info_type,
1534 legislation_info1,
1535 legislation_info2,
1536 legislation_info3,
1537 legislation_info4,
1538 legislation_info5,
1539 legislation_info6
1540 FROM pay_mx_legislation_info_f
1541 WHERE legislation_info_type = p_legislation_info_type
1542 AND DECODE(p_legislation_info1,
1543 '$Sys_Def$', legislation_info1,
1544 p_legislation_info1) = legislation_info1
1545 AND NVL(jurisdiction_code,
1546 p_ctx_jurisdiction_code) = p_ctx_jurisdiction_code
1547 AND p_ctx_effective_date BETWEEN effective_start_date
1548 AND effective_end_date;
1549
1550 l_proc_name VARCHAR2(100);
1551 l_exists BOOLEAN;
1552 cntr NUMBER;
1553 ld_start_date DATE;
1554 ld_end_date DATE;
1555 lv_jurisdiction pay_mx_legislation_info_f.jurisdiction_code%type;
1556 lv_legislation_info_type pay_mx_legislation_info_f.legislation_info_type%type;
1557 BEGIN
1558 l_proc_name := g_proc_name ||'GET_MX_TAX_INFO';
1559 hr_utility_trace('Entering '||l_proc_name);
1560 l_exists := FALSE;
1561 cntr := g_pay_mx_legislation_info_f.count();
1562 hr_utility_trace('Number of cached legislative tax info records = '||cntr);
1563
1564 IF cntr > 0 THEN /* Check if legislation info exists in cache. */
1565 FOR cntr IN g_pay_mx_legislation_info_f.first()..g_pay_mx_legislation_info_f.last()
1566 LOOP
1567 IF g_pay_mx_legislation_info_f(cntr).legislation_info_type
1568 = p_legislation_info_type AND
1569 NVL(g_pay_mx_legislation_info_f(cntr).jurisdiction_code,
1570 p_ctx_jurisdiction_code)= p_ctx_jurisdiction_code AND
1571 (p_legislation_info1 = '$Sys_Def$' OR
1572 g_pay_mx_legislation_info_f(cntr).legislation_info1
1573 = p_legislation_info1) AND
1574 p_ctx_effective_date BETWEEN
1575 g_pay_mx_legislation_info_f(cntr).effective_start_date AND
1576 g_pay_mx_legislation_info_f(cntr).effective_end_date
1577
1578 THEN
1579
1580 l_exists := TRUE;
1581 hr_utility_trace ('Retrieving legislative tax info from cache.');
1582 /* Copy cache onto out parameters */
1583 p_legislation_info1 := g_pay_mx_legislation_info_f(cntr).legislation_info1;
1584 p_legislation_info2 := g_pay_mx_legislation_info_f(cntr).legislation_info2;
1585 p_legislation_info3 := g_pay_mx_legislation_info_f(cntr).legislation_info3;
1586 p_legislation_info4 := g_pay_mx_legislation_info_f(cntr).legislation_info4;
1587 p_legislation_info5 := g_pay_mx_legislation_info_f(cntr).legislation_info5;
1588 p_legislation_info6 := g_pay_mx_legislation_info_f(cntr).legislation_info6;
1589
1590 EXIT;
1591 END IF;
1592 END LOOP;
1593 END IF;
1594
1595 IF cntr = 0 OR (NOT l_exists) THEN
1596 IF cntr > 0 THEN
1597 cntr := g_pay_mx_legislation_info_f.last() + 1;
1598 ELSE
1599 cntr := 1;
1600 END IF;
1601 hr_utility_trace ('Legislative tax info not found in cache. Hitting database now.');
1602
1603 OPEN csr_get_ss_info;
1604 FETCH csr_get_ss_info
1605 INTO g_pay_mx_legislation_info_f(cntr).effective_start_date,
1606 g_pay_mx_legislation_info_f(cntr).effective_end_date,
1607 g_pay_mx_legislation_info_f(cntr).jurisdiction_code,
1608 g_pay_mx_legislation_info_f(cntr).legislation_info_type,
1609 g_pay_mx_legislation_info_f(cntr).legislation_info1,
1610 g_pay_mx_legislation_info_f(cntr).legislation_info2,
1611 g_pay_mx_legislation_info_f(cntr).legislation_info3,
1612 g_pay_mx_legislation_info_f(cntr).legislation_info4,
1613 g_pay_mx_legislation_info_f(cntr).legislation_info5,
1614 g_pay_mx_legislation_info_f(cntr).legislation_info6;
1615 CLOSE csr_get_ss_info;
1616
1617 /* Override values fetched by this cursor
1618 IF p_legislation_info_type = 'MX Social Security Information' THEN
1619 IF p_legislation_info1 = 'WRI' THEN
1620 g_pay_mx_legislation_info_f(cntr).legislation_info5 :=
1621 hr_mx_utility.get_wrip (
1622 p_business_group_id => p_ctx_business_group_id,
1623 p_tax_unit_id => p_ctx_tax_unit_id);
1624 END IF;
1625 END IF;*/
1626
1627 /* Copy cache onto out parameters */
1628 p_legislation_info1 := g_pay_mx_legislation_info_f(cntr).legislation_info1;
1629 p_legislation_info2 := g_pay_mx_legislation_info_f(cntr).legislation_info2;
1630 p_legislation_info3 := g_pay_mx_legislation_info_f(cntr).legislation_info3;
1631 p_legislation_info4 := g_pay_mx_legislation_info_f(cntr).legislation_info4;
1632 p_legislation_info5 := g_pay_mx_legislation_info_f(cntr).legislation_info5;
1633 p_legislation_info6 := g_pay_mx_legislation_info_f(cntr).legislation_info6;
1634 END IF;
1635
1636 -- Bug 4656174
1637 IF p_legislation_info_type = 'MX Social Security Information' THEN
1638 IF p_legislation_info1 = 'WRI' THEN
1639 hr_utility_trace('Hitting database for WRIP');
1640 OPEN csr_get_ss_info;
1641 FETCH csr_get_ss_info
1642 INTO ld_start_date,
1643 ld_end_date,
1644 lv_jurisdiction,
1645 lv_legislation_info_type,
1646 p_legislation_info1,
1647 p_legislation_info2,
1648 p_legislation_info3,
1649 p_legislation_info4,
1650 p_legislation_info5,
1651 p_legislation_info6;
1652 CLOSE csr_get_ss_info;
1653 p_legislation_info5 :=
1654 hr_mx_utility.get_wrip (
1655 p_business_group_id => p_ctx_business_group_id,
1656 p_tax_unit_id => p_ctx_tax_unit_id);
1657 END IF;
1658 END IF;
1659
1660 hr_utility_trace('p_legislation_info_type = '||p_legislation_info_type);
1661 hr_utility_trace('p_legislation_info1 = ' ||p_legislation_info1);
1662 hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1663 hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1664 hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1665 hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1666 hr_utility_trace('p_legislation_info6 = ' ||p_legislation_info6);
1667
1668 hr_utility_trace('Leaving '||l_proc_name);
1669 RETURN(0);
1670 EXCEPTION WHEN OTHERS THEN
1671 g_pay_mx_legislation_info_f.DELETE();
1672 RAISE;
1673 END GET_MX_TAX_INFO;
1674
1675 /****************************************************************************
1676 Name : GET_MX_EE_HEAD_COUNT
1677 Description : This function returns the Employee Headcount used for
1678 Employer State Tax rate computation.
1679 *****************************************************************************/
1680 FUNCTION GET_MX_EE_HEAD_COUNT
1681 (
1682 P_CTX_BUSINESS_GROUP_ID NUMBER,
1683 P_CTX_TAX_UNIT_ID NUMBER,
1684 P_CTX_EFFECTIVE_DATE DATE,
1685 P_CTX_JURISDICTION_CODE VARCHAR2
1686 ) RETURN NUMBER AS
1687
1688 l_proc_name VARCHAR2(100);
1689 l_row_name VARCHAR2(300);
1690 l_legal_employer_name hr_organization_units.name%TYPE;
1691 l_head_count NUMBER;
1692 BEGIN
1693
1694 l_proc_name := g_proc_name || 'GET_MX_EE_HEAD_COUNT';
1695
1696 hr_utility_trace('Entering ' || l_proc_name);
1697
1698 l_legal_employer_name := hr_general.decode_organization(
1699 hr_mx_utility.get_legal_employer(
1700 p_ctx_business_group_id,
1701 p_ctx_tax_unit_id)
1702 );
1703
1704 hr_utility_trace('Legal Employer Name: ' || l_legal_employer_name);
1705
1706 l_row_name := l_legal_employer_name || ' (' ||
1707 p_ctx_jurisdiction_code || ')';
1708
1709
1710 l_head_count := hruserdt.get_table_value(p_ctx_business_group_id,
1711 'Employee Head Count',
1712 'Number of Employees',
1713 l_row_name,
1714 p_ctx_effective_date);
1715
1716 RETURN (l_head_count);
1717
1718 END GET_MX_EE_HEAD_COUNT;
1719
1720 /****************************************************************************
1721 Name : GET_MX_STATE_TAX_RULES
1722 Description : This function returns the data stored at Legal Employer level
1723 under "State Tax Rules" Org Info type.
1724 *****************************************************************************/
1725 FUNCTION GET_MX_STATE_TAX_RULES
1726 (
1727 P_CTX_BUSINESS_GROUP_ID NUMBER,
1728 P_CTX_TAX_UNIT_ID NUMBER,
1729 P_CTX_EFFECTIVE_DATE DATE,
1730 P_CTX_JURISDICTION_CODE VARCHAR2
1731 ) RETURN VARCHAR2 AS
1732
1733 -- Get data from hr_organization_information for the given Legal Employer
1734 --
1735 CURSOR c_get_rate_data(cp_legal_er_id NUMBER) IS
1736 SELECT DECODE(pml.legislation_info1,
1737 'RANGE', org_information5,
1738 'FLAT_RATE', org_information3)
1739 FROM hr_organization_information hoi,
1740 pay_mx_legislation_info_f pml
1741 WHERE hoi.organization_id = cp_legal_er_id
1742 AND hoi.org_information_context = 'MX_STATE_TAX_RULES'
1743 AND hoi.org_information1 = p_ctx_jurisdiction_code
1744 AND pml.jurisdiction_code = hoi.org_information1
1745 AND pml.legislation_info_type = 'MX State Tax Rate'
1746 AND p_ctx_effective_date BETWEEN pml.effective_start_date
1747 AND pml.effective_end_date
1748 AND DECODE(pml.legislation_info1,
1749 'RANGE', org_information5,
1750 'FLAT_RATE', org_information3) IS NOT NULL;
1751
1752 l_proc_name VARCHAR2(100);
1753 l_legal_er_id NUMBER;
1754 l_return_value VARCHAR2(100);
1755 BEGIN
1756
1757 l_proc_name := g_proc_name || 'GET_MX_STATE_TAX_RULES';
1758
1759 hr_utility_trace('Entering ' || l_proc_name);
1760
1761 l_legal_er_id := hr_mx_utility.get_legal_employer(p_ctx_business_group_id,
1762 p_ctx_tax_unit_id);
1763
1764 OPEN c_get_rate_data(l_legal_er_id);
1765 FETCH c_get_rate_data INTO l_return_value;
1766 CLOSE c_get_rate_data;
1767
1768 hr_utility_trace('Leaving ' || l_proc_name);
1769
1770 RETURN (l_return_value);
1771
1772 END GET_MX_STATE_TAX_RULES;
1773
1774 /****************************************************************************
1775 Name : CALCULATE_ISR_TAX
1776 Description : This function has
1777 1. Input Parameters as Contexts:
1778 - BUSINESS_GROUP_ID
1779 - ASSIGNMENT_ID
1780 - TAX_UNIT_ID
1781 - DATE_EARNED
1782 2. Input Parameters as Parameter:
1783 - SUBJECT_AMOUNT
1784 - ISR_RATES_TABLE
1785 - SUBSIDY_TABLE
1786 - CREDIT_TO_SALARY_TABLE
1787 3. Returns following data for give subject amount:
1788 - ISR_WITHHELD (Return Value)
1789 - ISR_CALCULATED (Output Parameter)
1790 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
1791 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
1792 - ISR_CREDIT_TO_SALARY (Output Parameter)
1793 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
1794 *****************************************************************************/
1795
1796 FUNCTION CALCULATE_ISR_TAX
1797 (
1798 P_PAYROLL_ACTION_ID NUMBER,
1799 P_ASSIGNMENT_ACTION_ID NUMBER,
1800 P_BUSINESS_GROUP_ID NUMBER,
1801 P_ASSIGNMENT_ID NUMBER,
1802 P_TAX_UNIT_ID NUMBER,
1803 P_DATE_EARNED DATE,
1804 P_SUBJECT_AMOUNT NUMBER,
1805 P_ISR_RATES_TABLE VARCHAR2,
1806 P_SUBSIDY_TABLE VARCHAR2,
1807 P_CREDIT_TO_SALARY_TABLE VARCHAR2,
1808 P_ISR_CALCULATED OUT NOCOPY NUMBER,
1809 P_ISR_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
1810 P_ISR_NON_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
1811 P_CREDIT_TO_SALARY OUT NOCOPY NUMBER,
1812 P_CREDIT_TO_SALARY_PAID OUT NOCOPY NUMBER
1813 )
1814 RETURN NUMBER IS
1815 l_proc_name VARCHAR2(100);
1816 ln_isr NUMBER;
1817 BEGIN
1818 l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX-2';
1819 hr_utility_trace('Entering '||l_proc_name);
1820
1821 ln_isr := pay_mx_tax_functions.calculate_isr_tax(
1822 p_payroll_action_id => P_PAYROLL_ACTION_ID,
1823 p_assignment_action_id => P_ASSIGNMENT_ACTION_ID,
1824 p_business_group_id => p_business_group_id,
1825 p_assignment_id => p_assignment_id,
1826 p_tax_unit_id => p_tax_unit_id,
1827 p_date_earned => p_date_earned,
1828 p_calc_mode => NULL,
1829 p_subject_amount => p_subject_amount,
1830 p_isr_rates_table => p_isr_rates_table,
1831 p_subsidy_table => p_subsidy_table,
1832 p_credit_to_salary_table => p_credit_to_salary_table,
1833 p_isr_calculated => p_isr_calculated,
1834 p_isr_creditable_subsidy => p_isr_creditable_subsidy,
1835 p_isr_non_creditable_subsidy => p_isr_non_creditable_subsidy,
1836 p_credit_to_salary => p_credit_to_salary,
1837 p_credit_to_salary_paid => p_credit_to_salary_paid);
1838
1839 hr_utility_trace('ISR = ' || ln_isr);
1840 hr_utility_trace('Leaving '||l_proc_name);
1841 RETURN (ln_isr);
1842 END CALCULATE_ISR_TAX;
1843
1844
1845 /****************************************************************************
1846 Name : CALCULATE_ISR_TAX
1847 Description : This function has
1848 1. Input Parameters as Contexts:
1849 - PAYROLL_ACTION_ID
1850 - ASSIGNMENT_ACTION_ID
1851 - BUSINESS_GROUP_ID
1852 - ASSIGNMENT_ID
1853 - TAX_UNIT_ID
1854 - DATE_EARNED
1855 2. Input Parameters as Parameter:
1856 - SUBJECT_AMOUNT
1857 - ISR_RATES_TABLE
1858 - SUBSIDY_TABLE
1859 - CREDIT_TO_SALARY_TABLE
1860 3. Returns following data for give subject amount:
1861 - ISR_WITHHELD (Return Value)
1862 - ISR_CALCULATED (Output Parameter)
1863 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
1864 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
1865 - ISR_CREDIT_TO_SALARY (Output Parameter)
1866 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
1867 4. ISR changes 2008
1868 - ISR Credit to Salary has been used as
1869 - ISR Subsidy for Employment
1870 *****************************************************************************/
1871
1872 FUNCTION CALCULATE_ISR_TAX
1873 (
1874 P_PAYROLL_ACTION_ID NUMBER,
1875 P_ASSIGNMENT_ACTION_ID NUMBER,
1876 P_BUSINESS_GROUP_ID NUMBER,
1877 P_ASSIGNMENT_ID NUMBER,
1878 P_TAX_UNIT_ID NUMBER,
1879 P_DATE_EARNED DATE,
1880 P_CALC_MODE VARCHAR2,
1881 P_SUBJECT_AMOUNT NUMBER,
1882 P_ISR_RATES_TABLE VARCHAR2,
1883 P_SUBSIDY_TABLE VARCHAR2,
1884 P_CREDIT_TO_SALARY_TABLE VARCHAR2,
1885 P_ISR_CALCULATED OUT NOCOPY NUMBER,
1886 P_ISR_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
1887 P_ISR_NON_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
1888 P_CREDIT_TO_SALARY OUT NOCOPY NUMBER,
1889 P_CREDIT_TO_SALARY_PAID OUT NOCOPY NUMBER
1890 )
1891 RETURN NUMBER IS
1892
1893 l_proc_name VARCHAR2(100);
1894 ln_fixed_rate NUMBER;
1895 ln_marginal_rate NUMBER;
1896 ln_lower_limit NUMBER;
1897 ln_marginal_tax NUMBER;
1898
1899 ln_fixed_subsidy NUMBER;
1900 ln_marginal_subsidy NUMBER;
1901 ln_subsidy_lower_limit NUMBER;
1902 ln_tax_subsidy_percentage NUMBER;
1903 ln_total_subsidy NUMBER;
1904 ln_payroll_id NUMBER;
1905
1906 ln_isr_withheld NUMBER;
1907 l_credit_to_salary_table varchar2(100) := P_CREDIT_TO_SALARY_TABLE;
1908 ln_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
1909 ln_bal_amt NUMBER;
1910 ln_bal_sub NUMBER;
1911 ln_bal_sub_paid NUMBER;
1912 ln_isr_subject_mtd NUMBER;
1913 l_date_earned DATE;
1914 l_date_paid DATE;
1915 ln_credit_to_salary_tot NUMBER;
1916 ln_credit_to_salary_max NUMBER;
1917 ln_credit_to_salary_curr NUMBER;
1918 ln_mult_num NUMBER;
1919 ln_period_end_mtd DATE;
1920 ln_period_start_mtd DATE;
1921 ln_max_row NUMBER;
1922 lv_period_type per_time_periods.period_type%TYPE;
1923 ln_days_in_a_period NUMBER;
1924 ln_days_in_period_sub_empl NUMBER;
1925 ln_period_number NUMBER;
1926 ln_pre_date_paid NUMBER := 0;
1927 ld_hire_date DATE; --added for fix 6821377
1928 ld_first_pay_date DATE; --added for fix 6933775
1929 ln_ISR_subj_adj NUMBER;
1930 --ln_pay_period_days NUMBER;
1931 ln_worked_days NUMBER;
1932 ln_bal_ISR_non_wrkd_days NUMBER;
1933 ln_ISR_table_factor NUMBER;
1934 ln_ISR_proj_subject NUMBER;
1935 ln_le_days_month NUMBER;
1936 ln_le_days_year NUMBER;
1937 ln_le_id hr_all_organization_units.organization_id%TYPE;
1938 ln_isr_prop_fact NUMBER;
1939 ln_le_worked_days NUMBER;
1940 l_compute_subsidy_flag CHAR(1); /*bug#8438155*/
1941 /* lv_calc_mode VARCHAR2(20);
1942 lv_process VARCHAR2(20);
1943 lv_action_type pay_payroll_actions.action_type%type;
1944
1945 CURSOR csr_get_process_type IS
1946 SELECT action_type,
1947 pay_mx_utility.get_legi_param_val('CALC_MODE',
1948 legislative_parameters),
1949 pay_mx_utility.get_legi_param_val('PROCESS',
1950 legislative_parameters)
1951 FROM pay_payroll_actions
1952 WHERE payroll_action_id = p_payroll_action_id;*/
1953
1954 CURSOR csr_get_payroll_id IS /* bug#8932102 */
1955 SELECT payroll_id
1956 FROM per_assignments
1957 WHERE assignment_id = P_ASSIGNMENT_ID;
1958
1959 CURSOR csr_get_hire_date IS /* bug#8932102 */
1960 SELECT min(effective_start_date)
1961 FROM per_all_assignments_f
1962 WHERE assignment_id = P_ASSIGNMENT_ID
1963 AND assignment_type='E';
1964
1965 CURSOR csr_def_bal_id (p_balance_name varchar2
1966 ,p_db_item_suffix varchar2) IS
1967 SELECT pdb.defined_balance_id
1968 FROM pay_defined_balances pdb,
1969 pay_balance_dimensions pbd,
1970 pay_balance_types pbt
1971 WHERE pbd.balance_dimension_id = pdb.balance_dimension_id
1972 AND pbt.balance_type_id = pdb.balance_type_id
1973 AND pbd.database_item_suffix = p_db_item_suffix -- '_ASG_GRE_MTD'
1974 AND pbt.balance_name = p_balance_name; --'ISR Subsidy for Employment'
1975
1976 CURSOR csr_date_earned IS
1977 SELECT ppa.effective_date l_date_paid,
1978 ptp.end_date l_date_earned
1979 FROM per_time_periods ptp,
1980 pay_payroll_actions ppa
1981 WHERE ppa.payroll_action_id = p_payroll_action_id
1982 AND ppa.time_period_id = ptp.time_period_id;
1983
1984 --Added for fix 6933775.
1985 /*Cursor to get the first pay period start date for the assignment*/
1986 CURSOR csr_get_first_pay_date IS
1987 SELECT MIN(ptp.start_date)
1988 FROM pay_assignment_actions paa,
1989 pay_payroll_actions ppa,
1990 per_time_periods ptp
1991 WHERE paa.assignment_id = p_assignment_id
1992 AND paa.payroll_action_id = ppa.payroll_action_id
1993 AND ptp.time_period_id = ppa.time_period_id
1994 AND ppa.action_type IN ('R', 'Q', 'B')
1995 AND (to_char(l_date_paid,'yyyymm') = to_char(ptp.end_date,'yyyymm')
1996 OR
1997 l_date_paid BETWEEN ptp.start_date AND ptp.end_date
1998 );
1999
2000 CURSOR csr_get_period_count_in_month IS
2001 SELECT ROWNUM mult_num
2002 ,end_date period_end_mtd
2003 ,start_date period_start_mtd
2004 ,period_type
2005 FROM PER_TIME_PERIODS ptp
2006 WHERE payroll_id = ln_payroll_id
2007 AND (TO_CHAR(l_date_paid,'yyyymm') = TO_CHAR(end_date,'yyyymm')
2008 OR
2009 l_date_paid BETWEEN start_date AND end_date
2010 )
2011 AND ld_hire_date <= end_date
2012 AND start_date >= NVL(ld_first_pay_date,start_date)
2013 ORDER BY end_date;
2014
2015 CURSOR csr_get_no_of_days_in_period(p_payroll_id number) IS
2016 SELECT end_date - start_date +1 period_days
2017 FROM PER_TIME_PERIODS ptp
2018 WHERE payroll_id = p_payroll_id
2019 AND TO_CHAR(l_date_earned,'yyyymmdd') = TO_CHAR(end_date,'yyyymmdd');
2020
2021 CURSOR csr_get_compute_subsidy_flag IS /*bug#8438155*/
2022 SELECT nvl(hsck.SEGMENT11,'Y')
2023 FROM per_assignments_f paf,
2024 pay_assignment_actions paa,
2025 pay_payroll_actions ppa,
2026 hr_soft_coding_keyflex hsck
2027 WHERE paf.assignment_id = paa.assignment_id
2028 AND paa.payroll_action_id = ppa.payroll_action_id
2029 AND paa.assignment_action_id = p_assignment_action_id
2030 AND hsck.soft_coding_keyflex_id= paf.soft_coding_keyflex_id
2031 AND ppa.effective_date BETWEEN paf.effective_start_date
2032 AND paf.effective_end_date;
2033
2034 BEGIN
2035 l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2036 hr_utility_trace('Entering '||l_proc_name);
2037 hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2038 hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2039 hr_utility_trace('p_business_group_id: '||p_business_group_id);
2040 hr_utility_trace('p_assignment_id: '||p_assignment_id);
2041 hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2042 hr_utility_trace('p_date_earned: '||
2043 fnd_date.date_to_canonical(p_date_earned));
2044 hr_utility_trace('p_calc_mode: '||p_calc_mode);
2045 hr_utility_trace('p_subject_amount: '||p_subject_amount);
2046 hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2047 hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2048 hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2049 hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2050
2051 ln_ISR_proj_subject := p_subject_amount;
2052
2053 OPEN csr_date_earned;
2054 FETCH csr_date_earned INTO l_date_paid, l_date_earned;
2055 CLOSE csr_date_earned;
2056
2057 hr_utility_trace('l_date_earned: '||l_date_earned);
2058 hr_utility_trace('l_date_paid: '||l_date_paid);
2059
2060 --Start of Bug Fix 6852627
2061 /*Calculation for Proration*/
2062
2063 /*bug#8932102:
2064 1. Seperated ld_hire_date from cursor csr_get_payroll_id
2065 and created seperate cursor to fetch ld_hire_date, this is because
2066 ld_hire_date will be wrong when the per_all_people_f table has
2067 multiple date effective entries
2068 2. Table reference per_assignments_f has been changed to per_assignments in
2069 cursor csr_get_payroll_id to get latest payroll_id for the assignment*/
2070
2071 OPEN csr_get_payroll_id; /*bug#8932102 */
2072 FETCH csr_get_payroll_id INTO ln_payroll_id;
2073 CLOSE csr_get_payroll_id;
2074
2075 OPEN csr_get_hire_date;
2076 FETCH csr_get_hire_date INTO ld_hire_date;
2077 CLOSE csr_get_hire_date;
2078
2079 hr_utility_trace('payroll_id '||ln_payroll_id);
2080 hr_utility_trace('ld_hire_date '||ld_hire_date);
2081 hr_utility_trace('Getting the actual number of days in pay period ...');
2082
2083 OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2084 FETCH csr_get_no_of_days_in_period INTO ln_days_in_a_period;
2085 CLOSE csr_get_no_of_days_in_period;
2086
2087 hr_utility_trace('Number of days in the pay period : '||ln_days_in_a_period);
2088
2089 hr_utility_trace('Getting the total number of days in pay period as deifned in GRE/LE...');
2090
2091 ln_le_id := hr_mx_utility.get_legal_employer(
2092 p_business_group_id => p_business_group_id
2093 ,p_tax_unit_id => p_tax_unit_id);
2094
2095 pay_mx_utility.get_no_of_days_for_org( p_business_group_id => p_business_group_id
2096 ,p_org_id => ln_le_id
2097 ,p_gre_or_le => 'LE'
2098 ,p_days_month => ln_le_days_month
2099 ,p_days_year => ln_le_days_year);
2100
2101 IF (ln_le_days_month IS NULL OR ln_le_days_month = -999) THEN
2102 ln_le_days_month := ln_days_in_a_period ;
2103 END IF;
2104
2105 /*ln_pay_period_days := pay_mx_utility.get_days_in_pay_period( p_business_group_id
2106 ,p_tax_unit_id
2107 ,ln_payroll_id);*/
2108 hr_utility_trace('Average days in the month at LE :'||to_char(ln_le_days_month));
2109
2110 OPEN csr_def_bal_id ('ISR Non Working Days','_ASG_GRE_RUN');
2111 FETCH csr_def_bal_id INTO ln_def_bal_id;
2112 CLOSE csr_def_bal_id;
2113
2114 hr_utility_trace('ISR Non Working Days def bal id '||to_char(ln_def_bal_id));
2115 ln_bal_ISR_non_wrkd_days := pay_balance_pkg.get_value(ln_def_bal_id,
2116 p_assignment_action_id,
2117 p_tax_unit_id,
2118 NULL,
2119 NULL,
2120 NULL,
2121 NULL,
2122 NULL,
2123 NULL,
2124 'TRUE');
2125 hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
2126
2127 hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2128 ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2129 hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2130
2131 hr_utility_trace('getting worked days.. ');
2132 ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
2133
2134 ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
2135
2136 hr_utility_trace('Actual worked days in the period for ISR Tax '||to_char(ln_worked_days));
2137 hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
2138 --End of Bug Fix 6852627
2139 IF to_char(l_date_paid,'yyyymmdd')
2140 >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2141 hr_utility_trace('ISr Proration in 2008');
2142 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
2143
2144 ln_ISR_proj_subject := ROUND(((ln_ISR_proj_subject * ln_le_days_month) / ln_le_worked_days),2);
2145
2146 hr_utility_trace('ISR Projected value in the pay period '||to_char(ln_ISR_proj_subject));
2147 END IF;
2148
2149 END IF;
2150
2151 hr_utility_trace('Final Subject amount after proration: '||ln_ISR_proj_subject);
2152 /* Article 113, 114 and 115 */
2153
2154 ln_fixed_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2155 p_business_group_id
2156 ,p_isr_rates_table
2157 ,'Fixed Rate'
2158 ,TO_CHAR(ln_ISR_proj_subject)));
2159
2160 hr_utility_trace('ln_fixed_rate: '||ln_fixed_rate);
2161
2162 ln_marginal_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2163 p_business_group_id
2164 ,p_isr_rates_table
2165 ,'Marginal Rate'
2166 ,TO_CHAR(ln_ISR_proj_subject)));
2167
2168 hr_utility_trace('ln_marginal_rate: '||ln_marginal_rate);
2169
2170 ln_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2171 p_business_group_id
2172 ,p_isr_rates_table
2173 ,'Lower Bound'
2174 ,TO_CHAR(ln_ISR_proj_subject)));
2175
2176 hr_utility_trace('ln_lower_limit: '||ln_lower_limit);
2177
2178 ln_marginal_tax := (ln_marginal_rate/100) * (ln_ISR_proj_subject -
2179 ln_lower_limit);
2180
2181 hr_utility_trace('ln_marginal_tax: '||ln_marginal_tax);
2182
2183 p_isr_calculated := ln_fixed_rate + ln_marginal_tax;
2184
2185 hr_utility_trace('p_isr_calculated: '||p_isr_calculated);
2186
2187 IF TO_CHAR(l_date_paid,'yyyymmdd')
2188 < TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2189
2190 /* Subsidy Calculation */
2191 ln_fixed_subsidy := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2192 p_business_group_id
2193 ,p_subsidy_table
2194 ,'Fixed Rate'
2195 ,TO_CHAR(ln_ISR_proj_subject)));
2196
2197 hr_utility_trace('ln_fixed_subsidy: '||ln_fixed_subsidy);
2198
2199 ln_marginal_subsidy := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2200 p_business_group_id
2201 ,p_subsidy_table
2202 ,'Marginal Rate'
2203 ,TO_CHAR(ln_ISR_proj_subject)));
2204
2205 hr_utility_trace('ln_marginal_subsidy: '||ln_marginal_subsidy);
2206
2207 ln_subsidy_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2208 p_business_group_id
2209 ,p_subsidy_table
2210 ,'Lower Bound'
2211 ,TO_CHAR(ln_ISR_proj_subject)));
2212
2213
2214 hr_utility_trace('ln_subsidy_lower_limit: '||ln_subsidy_lower_limit);
2215
2216 ln_tax_subsidy_percentage := hr_mx_utility.get_tax_subsidy_percent(
2217 p_business_group_id
2218 ,p_tax_unit_id);
2219
2220 hr_utility_trace('ln_tax_subsidy_percentage: '||ln_tax_subsidy_percentage);
2221
2222 /*OPEN csr_get_process_type;
2223 FETCH csr_get_process_type INTO lv_action_type,
2224 lv_calc_mode,
2225 lv_process;
2226 CLOSE csr_get_process_type;*/
2227
2228 IF (p_calc_mode = 'BEST' OR p_calc_mode = 'ARTICLE141') THEN
2229 -- Calculate subsidy for Article 141
2230 hr_utility_trace('Calculating subsidy for Article 141 ...');
2231 ln_total_subsidy := ln_fixed_rate * (ln_fixed_subsidy/100) +
2232 (ln_marginal_subsidy/100) *
2233 (ln_marginal_rate/100) *
2234 (ln_ISR_proj_subject - ln_subsidy_lower_limit);
2235 ELSE
2236 ln_total_subsidy := ln_fixed_subsidy +
2237 (ln_marginal_subsidy/100) *
2238 (ln_marginal_rate/100) *
2239 (ln_ISR_proj_subject - ln_subsidy_lower_limit);
2240 END IF;
2241 hr_utility_trace('ln_total_subsidy: '||ln_total_subsidy);
2242
2243 p_isr_creditable_subsidy := (ln_tax_subsidy_percentage/100) *
2244 ln_total_subsidy;
2245
2246 hr_utility_trace('p_isr_creditable_subsidy: '||p_isr_creditable_subsidy);
2247
2248 p_isr_non_creditable_subsidy := ln_total_subsidy - p_isr_creditable_subsidy;
2249
2250 hr_utility_trace('p_isr_non_creditable_subsidy: '||
2251 p_isr_non_creditable_subsidy);
2252
2253 ELSE
2254 p_isr_creditable_subsidy := 0;
2255 p_isr_non_creditable_subsidy := 0;
2256 END IF; -- end of subsidy calc
2257
2258 /* Credit To Salary Calculation */
2259
2260 /* Bug#8438155: created new cursor csr_get_compute_subsidy_flag to fetch
2261 the value 'Compute Subsidy for Employment' from 'MX Statutory Info'
2262 If this value is 'N' then below subsidy calculation will be skipped */
2263
2264 OPEN csr_get_compute_subsidy_flag;
2265 FETCH csr_get_compute_subsidy_flag into l_compute_subsidy_flag;
2266 CLOSE csr_get_compute_subsidy_flag;
2267
2268 hr_utility_trace('l_compute_subsidy_flag '||l_compute_subsidy_flag);
2269 /*bug7445486 bug#8438155*/
2270 IF ( p_credit_to_salary_table <> 'NONE' and P_CALC_MODE <>'ARTICLE142' AND l_compute_subsidy_flag <> 'N') THEN
2271
2272 hr_utility_trace('inside subsidy '||p_credit_to_salary_table|| ' --- '||TO_CHAR(ln_ISR_proj_subject));
2273 p_credit_to_salary := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2274 p_business_group_id
2275 ,p_credit_to_salary_table
2276 ,'Amount'
2277 ,TO_CHAR(ln_ISR_proj_subject)));
2278
2279 P_CREDIT_TO_SALARY := nvl(P_CREDIT_TO_SALARY,0);
2280
2281 IF TO_CHAR(l_date_paid,'yyyymmdd')
2282 >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2283
2284 /*bug#8932102:
2285 1. Seperated ld_hire_date from cursor csr_get_payroll_id
2286 and created seperate cursor to fetch ld_hire_date, this is because
2287 ld_hire_date will be wrong when the per_all_people_f table has
2288 multiple date effective entries
2289 2. Table reference per_assignments_f has been changed to per_assignments in
2290 cursor csr_get_payroll_id*/
2291
2292
2293 OPEN csr_get_payroll_id; /*bug#8932102 */
2294 FETCH csr_get_payroll_id INTO ln_payroll_id;
2295 CLOSE csr_get_payroll_id;
2296
2297 OPEN csr_get_hire_date;
2298 FETCH csr_get_hire_date INTO ld_hire_date;
2299 CLOSE csr_get_hire_date;
2300
2301 hr_utility_trace('payroll_id 2'||ln_payroll_id);
2302 hr_utility_trace('ld_hire_date 2'||ld_hire_date);
2303
2304 -- get ISR Subject balance for month
2305
2306 OPEN csr_def_bal_id ('ISR Subject','_ASG_MTD');
2307 FETCH csr_def_bal_id INTO ln_def_bal_id;
2308 CLOSE csr_def_bal_id;
2309
2310 hr_utility_trace('ISR Subject def bal id '||ln_def_bal_id);
2311
2312 ln_bal_amt := pay_balance_pkg.get_value(ln_def_bal_id,
2313 P_ASSIGNMENT_ACTION_ID,
2314 p_tax_unit_id,
2315 NULL,
2316 NULL,
2317 NULL,
2318 NULL,
2319 NULL,
2320 NULL,
2321 'TRUE');
2322
2323 hr_utility_trace('MTD Subject Amt - ln_bal_amt '||to_char(ln_bal_amt));
2324 /*Get the previous ISR Subject adjusted amount due to proration
2325 this will have impact only in semi-monthly and weekely payroll*/
2326 OPEN csr_def_bal_id ('ISR Subject Adjusted','_ASG_MTD');
2327 FETCH csr_def_bal_id INTO ln_def_bal_id;
2328 CLOSE csr_def_bal_id;
2329
2330 hr_utility_trace('ISR Subject Adjusted def bal id '||ln_def_bal_id);
2331 ln_ISR_subj_adj := pay_balance_pkg.get_value(ln_def_bal_id,
2332 P_ASSIGNMENT_ACTION_ID,
2333 p_tax_unit_id,
2334 null,
2335 null,
2336 null,
2337 null,
2338 null,
2339 null,
2340 'TRUE');
2341 hr_utility_trace('ISR Subject Adjusted Amount MTD - ln_ISR_subj_adj'||ln_ISR_subj_adj);
2342 -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
2343 -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
2344 -- get ISR Subsidy for Employement balance for month
2345 ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
2346 hr_utility_trace('Actual subject value is ln_ISR_subj_adj + ln_ISR_proj_subject '||ln_ISR_subj_adj);
2347
2348 OPEN csr_def_bal_id ('ISR Subsidy for Employment','_ASG_MTD');
2349 FETCH csr_def_bal_id INTO ln_def_bal_id;
2350 CLOSE csr_def_bal_id;
2351
2352 hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
2353
2354 ln_bal_sub := pay_balance_pkg.get_value(ln_def_bal_id,
2355 P_ASSIGNMENT_ACTION_ID,
2356 p_tax_unit_id,
2357 NULL,
2358 NULL,
2359 NULL,
2360 NULL,
2361 NULL,
2362 NULL,
2363 'TRUE');
2364
2365 hr_utility_trace('ISR Sub for Empl Bal Sub '||to_char(ln_bal_sub));
2366
2367 OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid','_ASG_MTD');
2368 FETCH csr_def_bal_id INTO ln_def_bal_id;
2369 CLOSE csr_def_bal_id;
2370
2371 hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
2372
2373 ln_bal_sub_paid := pay_balance_pkg.get_value(ln_def_bal_id,
2374 P_ASSIGNMENT_ACTION_ID,
2375 p_tax_unit_id,
2376 NULL,
2377 NULL,
2378 NULL,
2379 NULL,
2380 NULL,
2381 NULL,
2382 'TRUE');
2383
2384 hr_utility_trace('ISR Sub for Empl Bal Sub Paid '||to_char(ln_bal_sub_paid));
2385
2386 if ln_payroll_id is not null then
2387 select min(period_type)
2388 into lv_period_type
2389 from pay_payrolls_f
2390 where payroll_id = ln_payroll_id;
2391 end if;
2392
2393 OPEN csr_get_first_pay_date;
2394 FETCH csr_get_first_pay_date INTO ld_first_pay_date;
2395 CLOSE csr_get_first_pay_date;
2396
2397 ln_isr_subject_mtd := CONVERT_INTO_MONTHLY_AVG_SAL (p_business_group_id
2398 ,p_tax_unit_id
2399 ,ln_payroll_id
2400 ,ln_ISR_proj_subject
2401 ,l_date_paid
2402 ,ld_hire_date
2403 ,ld_first_pay_date
2404 ,ln_period_number)
2405 + (ln_ISR_subj_adj - ln_ISR_proj_subject);
2406
2407 hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
2408 hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
2409 hr_utility_trace('ln_period_number : '||ln_period_number);
2410
2411 open csr_get_period_count_in_month;
2412 loop
2413 fetch csr_get_period_count_in_month into ln_mult_num,
2414 ln_period_end_mtd,
2415 ln_period_start_mtd,
2416 lv_period_type;
2417 exit when csr_get_period_count_in_month%NOTFOUND;
2418
2419 select least(count(*),1)
2420 into ln_pre_date_paid
2421 from pay_payroll_actions ppa,
2422 pay_assignment_actions paa,
2423 per_time_periods ptp
2424 where ppa.payroll_action_id = paa.payroll_action_id
2425 and ptp.time_period_id = ppa.time_period_id
2426 and paa.assignment_id = p_assignment_id
2427 and to_char(ptp.end_date,'yyyymm') = to_char(l_date_paid,'yyyymm')
2428 and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
2429 AND ld_hire_date <= ptp.end_date;
2430
2431 hr_utility_trace('l_date_paid '||to_char(l_date_paid,'yyyymm'));
2432 hr_utility_trace('ln_pre_date_paid '||to_char(ln_pre_date_paid));
2433
2434 IF l_date_paid >= ln_period_start_mtd and
2435 l_date_paid <= ln_period_end_mtd then
2436 ln_period_number := ln_period_number - ln_pre_date_paid;
2437 hr_utility_trace('Actual ln_period_number '||to_char(ln_period_number));
2438 end if;
2439
2440 end loop;
2441 close csr_get_period_count_in_month;
2442
2443
2444 IF lv_period_type = 'Semi-Month' THEN
2445 ln_days_in_period_sub_empl := 15;
2446 ELSIF lv_period_type = 'Ten Days' then
2447 ln_days_in_period_sub_empl := 10;
2448 ELSE
2449 ln_days_in_period_sub_empl := ln_days_in_a_period; /*bug 7677805*/
2450 END IF ;
2451 p_credit_to_salary := FND_NUMBER.canonical_to_number(get_table_value (
2452 p_business_group_id
2453 ,p_credit_to_salary_table
2454 ,'Amount'
2455 ,TO_CHAR(ln_ISR_subj_adj)
2456 ,l_date_paid
2457 ,ln_days_in_a_period
2458 ,ln_period_number
2459 ,lv_period_type));
2460
2461 hr_utility_trace('Total Subsidy for Empl '||to_char(p_credit_to_salary));
2462
2463 ln_credit_to_salary_max := p_credit_to_salary;
2464
2465 P_CREDIT_TO_SALARY := (ln_credit_to_salary_max/30.4) * ln_days_in_period_sub_empl;
2466
2467 ln_credit_to_salary_curr := P_CREDIT_TO_SALARY;
2468
2469 hr_utility_trace('Current Period Subsidy for Empl '||to_char(p_credit_to_salary));
2470
2471 select count(*) max_row
2472 into ln_max_row
2473 from PER_TIME_PERIODS ptp1
2474 where payroll_id = ln_payroll_id
2475 and to_char(l_date_paid,'yyyymm') = to_char(end_date,'yyyymm');
2476
2477 open csr_get_period_count_in_month;
2478 loop
2479 fetch csr_get_period_count_in_month into ln_mult_num,
2480 ln_period_end_mtd,
2481 ln_period_start_mtd,
2482 lv_period_type;
2483 exit when csr_get_period_count_in_month%NOTFOUND;
2484 if ln_period_end_mtd = l_date_earned then
2485
2486 if to_char(ln_period_end_mtd,'yyyymm') = to_char(l_date_paid,'yyyymm') and
2487 to_char(ln_period_end_mtd,'yyyymmdd') >= to_char(last_day(l_date_paid),'yyyymmdd') then
2488
2489 P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
2490 P_CREDIT_TO_SALARY * ln_mult_num );
2491 hr_utility_trace('outside/last day of month '||to_char(p_credit_to_salary));
2492 else
2493 P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
2494 P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
2495 hr_utility_trace('With in month '||to_char(p_credit_to_salary));
2496 end if;
2497
2498 hr_utility_trace('max allowd Subsidy for Empl '||to_char(p_credit_to_salary));
2499 IF l_date_paid >= ln_period_start_mtd AND
2500 l_date_paid <= ln_period_end_mtd AND
2501 (lv_period_type = 'Calendar Month' OR (lv_period_type = 'Semi-Month' AND ln_period_number = 2)
2502 OR (lv_period_type = 'Ten Days' AND ln_period_number = 3))
2503 THEN
2504 P_CREDIT_TO_SALARY := ln_credit_to_salary_max;
2505 hr_utility_trace('Final period Subsidy for Empl '||to_char(p_credit_to_salary));
2506 end if;
2507 P_CREDIT_TO_SALARY := P_CREDIT_TO_SALARY - ln_bal_sub;
2508 hr_utility_trace('Subsidy for Empl '||to_char(p_credit_to_salary));
2509 end if;
2510 end loop;
2511 close csr_get_period_count_in_month;
2512 END IF;
2513 ELSE
2514 p_credit_to_salary := 0;
2515 END IF;
2516
2517 P_CREDIT_TO_SALARY := nvl(P_CREDIT_TO_SALARY,0);
2518 hr_utility_trace('p_credit_to_salary: '||p_credit_to_salary);
2519
2520 /* ISR Withheld Calculation */
2521
2522 ln_isr_withheld := p_isr_calculated -
2523 p_isr_creditable_subsidy -
2524 p_credit_to_salary;
2525
2526
2527 hr_utility_trace('ln_isr_withheld B4: '||ln_isr_withheld);
2528
2529 /*Incuded the proration logic based on balance ISR Non working days*/
2530 --Start of Bug fix 6852627
2531 IF to_char(l_date_paid,'yyyymmdd')
2532 >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2533 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
2534
2535 hr_utility_trace('-- Non workings days are feeded, need to proproate ISR Tax --');
2536
2537 hr_utility_trace('Getting the table factor value for ISR Tax calculation');
2538 ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
2539
2540 hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
2541
2542 hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
2543
2544 ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
2545
2546 hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
2547 hr_utility_trace('Start of ISR Subject proration ');
2548
2549 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
2550 hr_utility_trace('ISR Non Worked Days value is invalid ');
2551 hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
2552 hr_utility.raise_error;
2553
2554 ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
2555 ln_isr_withheld := 0;
2556 p_credit_to_salary_paid := 0;
2557 p_isr_calculated := 0;
2558 p_credit_to_salary := 0;
2559
2560 END IF;
2561 END IF;
2562 --End of Bug Fix 6852627
2563
2564 IF ln_isr_withheld < 0 THEN
2565 p_credit_to_salary_paid := ABS(ln_isr_withheld);
2566 ln_isr_withheld := 0;
2567 ELSE
2568 p_credit_to_salary_paid := 0;
2569 END IF;
2570 /*To feed ISR Subject Adjusted Balance*/
2571 IF TO_CHAR(l_date_paid,'yyyymmdd')
2572 >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2573 p_isr_creditable_subsidy := ln_ISR_proj_subject;
2574 hr_utility_trace(' Feeding ISR Subject Adjusted Balance value in 2008 = p_isr_creditable_subsidy'||p_isr_creditable_subsidy);
2575 END IF;
2576 hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
2577 hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
2578 hr_utility_trace('Leaving '||l_proc_name);
2579
2580 RETURN ln_isr_withheld;
2581
2582 EXCEPTION
2583 WHEN OTHERS THEN
2584 hr_utility_trace('Exception in '||l_proc_name||': '||SQLERRM);
2585 RAISE;
2586 END CALCULATE_ISR_TAX;
2587
2588
2589 /****************************************************************************
2590 Name : CALCULATE_ISR_TAX
2591 Description : This function has
2592 1. Input Parameters as Contexts:
2593 - PAYROLL_ACTION_ID
2594 - ASSIGNMENT_ACTION_ID
2595 - BUSINESS_GROUP_ID
2596 - ASSIGNMENT_ID
2597 - TAX_UNIT_ID
2598 - DATE_EARNED
2599 2. Input Parameters as Parameter:
2600 - RUN TYPE
2601 - CALCULATION MODE
2602 - SUBJECT_AMOUNT
2603 - ISR_RATES_TABLE
2604 - SUBSIDY_TABLE
2605 - CREDIT_TO_SALARY_TABLE
2606 3. Returns following data for give subject amount:
2607 - ISR_WITHHELD (Return Value)
2608 - ISR_CALCULATED (Output Parameter)
2609 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
2610 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
2611 - ISR_CREDIT_TO_SALARY (Output Parameter)
2612 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
2613 4. ISR changes 2008
2614 - ISR Credit to Salary has been used as
2615 - ISR Subsidy for Employment
2616 *****************************************************************************/
2617
2618 FUNCTION CALCULATE_ISR_TAX
2619 (
2620 P_PAYROLL_ACTION_ID NUMBER,
2621 P_ASSIGNMENT_ACTION_ID NUMBER,
2622 P_BUSINESS_GROUP_ID NUMBER,
2623 P_ASSIGNMENT_ID NUMBER,
2624 P_TAX_UNIT_ID NUMBER,
2625 P_DATE_EARNED DATE,
2626 P_RUN_TYPE VARCHAR2,
2627 P_CALC_MODE VARCHAR2,
2628 P_SUBJECT_AMOUNT NUMBER,
2629 P_ISR_RATES_TABLE VARCHAR2,
2630 P_SUBSIDY_TABLE VARCHAR2,
2631 P_CREDIT_TO_SALARY_TABLE VARCHAR2,
2632 P_ISR_CALCULATED OUT NOCOPY NUMBER,
2633 P_ISR_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
2634 P_ISR_NON_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
2635 P_CREDIT_TO_SALARY OUT NOCOPY NUMBER,
2636 P_CREDIT_TO_SALARY_PAID OUT NOCOPY NUMBER
2637 )
2638 RETURN NUMBER IS
2639
2640 l_proc_name VARCHAR2(100);
2641 lv_dimension_type VARCHAR2 (50);
2642 lv_proration_table VARCHAR2 (50);
2643 ln_fixed_rate NUMBER;
2644 ln_marginal_rate NUMBER;
2645 ln_lower_limit NUMBER;
2646 ln_marginal_tax NUMBER;
2647
2648 ln_fixed_subsidy NUMBER;
2649 ln_marginal_subsidy NUMBER;
2650 ln_subsidy_lower_limit NUMBER;
2651 ln_tax_subsidy_percentage NUMBER;
2652 ln_total_subsidy NUMBER;
2653 ln_payroll_id NUMBER;
2654
2655 ln_isr_withheld NUMBER;
2656 l_credit_to_salary_table varchar2(100) := P_CREDIT_TO_SALARY_TABLE;
2657 ln_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
2658 ln_bal_amt NUMBER;
2659 ln_bal_sub NUMBER;
2660 ln_bal_sub_paid NUMBER;
2661 tot_subsidy_empl NUMBER;
2662 ln_isr_subject_mtd NUMBER;
2663 l_date_earned DATE;
2664 l_date_paid DATE;
2665 ln_credit_to_salary_tot NUMBER;
2666 ln_credit_to_salary_max NUMBER;
2667 ln_credit_to_salary_curr NUMBER;
2668 ln_mult_num NUMBER;
2669 ln_period_end_mtd DATE;
2670 ln_period_start_mtd DATE;
2671 ln_max_row NUMBER;
2672 LN_ISR_SUBSIDY_EMPL_YTD NUMBER;
2673 lv_period_type per_time_periods.period_type%TYPE;
2674 ln_days_in_a_period NUMBER;
2675 ln_days_in_period_sub_empl NUMBER;
2676 ln_period_number NUMBER;
2677 ln_pre_date_paid NUMBER := 0;
2678 ld_hire_date DATE; --added for fix 6821377
2679 ld_first_pay_date DATE; --added for fix 6933775
2680 ln_ISR_subj_adj NUMBER;
2681 ln_ISR_subj_adj_ytd NUMBER;
2682 ln_ISR_tax_to_charge_ytd NUMBER;
2683 ln_ISR_withheld_ytd NUMBER;
2684 --ln_pay_period_days NUMBER;
2685 ln_worked_days NUMBER;
2686 ln_bal_ISR_non_wrkd_days NUMBER;
2687 ln_bal_ISR_non_wrkd_days_ytd NUMBER;
2688 tot_ISR_non_wrkd_days NUMBER;
2689 ln_ISR_table_factor NUMBER;
2690 ln_ISR_proj_subject NUMBER;
2691 ln_le_days_month NUMBER;
2692 ln_le_days_year NUMBER;
2693 ln_le_id hr_all_organization_units.organization_id%TYPE;
2694 ln_isr_prop_fact NUMBER;
2695 ln_le_worked_days NUMBER;
2696 ln_days_in_ytd NUMBER;
2697 ln_wrkd_days_in_adj_period NUMBER;
2698 ln_proration_fac NUMBER;
2699 tot_sub_basis NUMBER;
2700 ln_table_id NUMBER;
2701 ln_fixed_rate_ytd NUMBER;
2702 ln_lower_bound_ytd NUMBER;
2703 ln_marginal_rate_ytd NUMBER;
2704 ln_taxable_subject_ytd NUMBER;
2705 ln_marginal_tax_ytd NUMBER;
2706 ln_total_tax_ytd NUMBER;
2707 ln_net_tax_ytd NUMBER;
2708 ln_adjusted_tax_ytd NUMBER;
2709 ln_ISR_subsidy_paid_ytd NUMBER;
2710 ln_no_days_in_month NUMBER;
2711 ld_period_start_date DATE;
2712 ld_period_end_date DATE;
2713 --ln_pro_sub_emp NUMBER;
2714 ld_act_hire_date DATE;
2715 l_compute_subsidy_flag CHAR(1); /*bug#8438155*/
2716 ln_ISR_calculated_ytd NUMBER;
2717
2718 /* lv_calc_mode VARCHAR2(20);
2719 lv_process VARCHAR2(20);
2720 lv_action_type pay_payroll_actions.action_type%type;
2721
2722 CURSOR csr_get_process_type IS
2723 SELECT action_type,
2724 pay_mx_utility.get_legi_param_val('CALC_MODE',
2725 legislative_parameters),
2726 pay_mx_utility.get_legi_param_val('PROCESS',
2727 legislative_parameters)
2728 FROM pay_payroll_actions
2729 WHERE payroll_action_id = p_payroll_action_id;*/
2730
2731 CURSOR csr_get_payroll_id IS /* bug#8932102 */
2732 SELECT payroll_id
2733 FROM per_assignments
2734 WHERE assignment_id = P_ASSIGNMENT_ID;
2735
2736 CURSOR csr_get_hire_date IS /* bug#8932102 */
2737 SELECT min(effective_start_date)
2738 FROM per_all_assignments_f
2739 WHERE assignment_id = P_ASSIGNMENT_ID
2740 AND assignment_type='E';
2741
2742 CURSOR csr_def_bal_id (p_balance_name varchar2
2743 ,p_db_item_suffix varchar2) IS
2744 SELECT pdb.defined_balance_id
2745 FROM pay_defined_balances pdb,
2746 pay_balance_dimensions pbd,
2747 pay_balance_types pbt
2748 WHERE pbd.balance_dimension_id = pdb.balance_dimension_id
2749 AND pbt.balance_type_id = pdb.balance_type_id
2750 AND pbd.database_item_suffix = p_db_item_suffix -- '_ASG_GRE_MTD'
2751 AND pbt.balance_name = p_balance_name; --'ISR Subsidy for Employment'
2752
2753 CURSOR csr_date_earned IS
2754 SELECT ppa.effective_date l_date_paid,
2755 ptp.end_date l_date_earned
2756 FROM per_time_periods ptp,
2757 pay_payroll_actions ppa
2758 WHERE ppa.payroll_action_id = p_payroll_action_id
2759 AND ppa.time_period_id = ptp.time_period_id;
2760
2761 --Added for fix 6933775.
2762 /*Cursor to get the first pay period start date for the assignment*/
2763 CURSOR csr_get_first_pay_date IS
2764 SELECT MIN(ptp.start_date)
2765 FROM pay_assignment_actions paa,
2766 pay_payroll_actions ppa,
2767 per_time_periods ptp
2768 WHERE paa.assignment_id = p_assignment_id
2769 AND paa.payroll_action_id = ppa.payroll_action_id
2770 AND ptp.time_period_id = ppa.time_period_id
2771 AND ppa.action_type IN ('R', 'Q', 'B')
2772 AND (to_char(l_date_paid,'yyyymm') = to_char(ptp.end_date,'yyyymm')
2773 OR
2774 l_date_paid BETWEEN ptp.start_date AND ptp.end_date
2775 );
2776
2777 CURSOR csr_get_period_count_in_month IS
2778 SELECT ROWNUM mult_num
2779 ,end_date period_end_mtd
2780 ,start_date period_start_mtd
2781 ,period_type
2782 FROM PER_TIME_PERIODS ptp
2783 WHERE payroll_id = ln_payroll_id
2784 AND (TO_CHAR(l_date_paid,'yyyymm') = TO_CHAR(end_date,'yyyymm')
2785 OR
2786 l_date_paid BETWEEN start_date AND end_date
2787 )
2788 AND ld_hire_date <= end_date
2789 AND start_date >= NVL(ld_first_pay_date,start_date)
2790 ORDER BY end_date;
2791
2792 CURSOR csr_get_no_of_days_in_period(p_payroll_id NUMBER) IS
2793 SELECT end_date - start_date +1 period_days
2794 FROM PER_TIME_PERIODS ptp
2795 WHERE payroll_id = p_payroll_id
2796 AND TO_CHAR(l_date_earned,'yyyymmdd') = TO_CHAR(end_date,'yyyymmdd');
2797
2798 CURSOR csr_get_no_of_days_in_ytd(p_payroll_id NUMBER) IS
2799 SELECT ptp1.end_date-TRUNC(to_date(ptp.start_date),'YEAR') + 1 period_days , TRUNC(to_date(ptp.start_date),'YEAR'), ptp1.end_date
2800 FROM PER_TIME_PERIODS ptp, PER_TIME_PERIODS ptp1
2801 WHERE ptp.period_num = '1'
2802 AND l_date_earned BETWEEN ptp1.start_date AND ptp1.end_date
2803 AND ptp.payroll_id = p_payroll_id
2804 AND ptp1.payroll_id = p_payroll_id
2805 AND (ptp1.end_date-TRUNC(to_date(ptp.start_date),'YEAR') + 1) BETWEEN '1' AND '379';
2806
2807 CURSOR csr_get_table_id(p_table_name VARCHAR2) IS
2808 SELECT user_table_id
2809 FROM pay_user_tables
2810 WHERE upper(user_table_name) = upper(p_table_name)
2811 AND nvl (business_group_id,
2812 p_business_group_id) = p_business_group_id
2813 AND nvl(legislation_code, 'MX') = 'MX';
2814
2815 CURSOR csr_get_table_value(p_table_id NUMBER, p_proration_fac NUMBER, p_col_name VARCHAR2, p_row_value NUMBER) IS
2816 SELECT fnd_number.canonical_to_number(CINST.value)* fnd_number.canonical_to_number(p_proration_fac)
2817 FROM pay_user_tables TAB
2818 , pay_user_columns C
2819 , pay_user_rows_f R
2820 , pay_user_column_instances_f CINST
2821 WHERE TAB.user_table_id = p_table_id
2822 AND C.user_table_id = TAB.user_table_id
2823 AND nvl (C.business_group_id,
2824 p_business_group_id) = p_business_group_id
2825 AND nvl (C.legislation_code,
2826 'MX') = 'MX'
2827 AND upper (C.user_column_name) = upper (p_col_name)
2828 AND CINST.user_column_id = C.user_column_id
2829 AND R.user_table_id = TAB.user_table_id
2830 AND p_date_earned BETWEEN R.effective_start_date
2831 AND R.effective_end_date
2832 AND nvl (R.business_group_id,
2833 p_business_group_id) = p_business_group_id
2834 AND nvl (R.legislation_code,
2835 'MX') = 'MX'
2836 AND fnd_number.canonical_to_number (p_row_value)
2837 BETWEEN fnd_number.canonical_to_number (R.row_low_range_or_name)* fnd_number.canonical_to_number(p_proration_fac)
2838 AND fnd_number.canonical_to_number (R.row_high_range)* fnd_number.canonical_to_number(p_proration_fac)
2839 AND TAB.user_key_units = 'N'
2840 AND CINST.user_row_id = R.user_row_id
2841 AND p_date_earned BETWEEN CINST.effective_start_date
2842 AND CINST.effective_end_date
2843 AND nvl (CINST.business_group_id,
2844 p_business_group_id) = p_business_group_id
2845 AND nvl (CINST.legislation_code,
2846 'MX') = 'MX';
2847
2848 CURSOR csr_get_no_of_days_in_mtd (p_payroll_id NUMBER) IS
2849 SELECT end_date-TRUNC(start_date, 'MONTH')+1, end_date, TRUNC(start_date, 'MONTH')
2850 FROM per_time_periods
2851 WHERE payroll_id = p_payroll_id
2852 AND l_date_paid BETWEEN start_date AND end_date;
2853
2854 CURSOR csr_get_act_hire_date IS
2855 SELECT MIN(pps.date_start)
2856 FROM per_periods_of_service pps , per_assignments_f paf
2857 WHERE paf.assignment_id = P_ASSIGNMENT_ID
2858 AND pps.person_id = paf.person_id;
2859
2860 CURSOR csr_get_compute_subsidy_flag IS /*bug#8438155*/
2861 SELECT nvl(hsck.SEGMENT11,'Y')
2862 FROM per_assignments_f paf,
2863 pay_assignment_actions paa,
2864 pay_payroll_actions ppa,
2865 hr_soft_coding_keyflex hsck
2866 WHERE paf.assignment_id = paa.assignment_id
2867 AND paa.payroll_action_id = ppa.payroll_action_id
2868 AND paa.assignment_action_id = p_assignment_action_id
2869 AND hsck.soft_coding_keyflex_id= paf.soft_coding_keyflex_id
2870 AND ppa.effective_date BETWEEN paf.effective_start_date
2871 AND paf.effective_end_date;
2872
2873 BEGIN
2874 l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2875 hr_utility_trace('Entering '||l_proc_name);
2876 hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2877 hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2878 hr_utility_trace('p_business_group_id: '||p_business_group_id);
2879 hr_utility_trace('p_assignment_id: '||p_assignment_id);
2880 hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2881 hr_utility_trace('p_date_earned: '||
2882 fnd_date.date_to_canonical(p_date_earned));
2883 hr_utility_trace('p_run_type: '||p_run_type);
2884 hr_utility_trace('p_calc_mode: '||p_calc_mode);
2885 hr_utility_trace('p_subject_amount: '||p_subject_amount);
2886 hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2887 hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2888 hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2889 hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2890
2891 ln_ISR_proj_subject := p_subject_amount;
2892
2893 OPEN csr_date_earned;
2894 FETCH csr_date_earned INTO l_date_paid, l_date_earned;
2895 CLOSE csr_date_earned;
2896
2897 hr_utility_trace('l_date_earned: '||l_date_earned);
2898 hr_utility_trace('l_date_paid: '||l_date_paid);
2899
2900 --Start of Bug Fix 6852627
2901 /*Calculation for Proration*/
2902 OPEN csr_get_payroll_id; /*bug#8932102 */
2903 FETCH csr_get_payroll_id INTO ln_payroll_id;
2904 CLOSE csr_get_payroll_id;
2905
2906 OPEN csr_get_hire_date;
2907 FETCH csr_get_hire_date INTO ld_hire_date;
2908 CLOSE csr_get_hire_date;
2909
2910 hr_utility_trace('payroll_id '||ln_payroll_id);
2911 hr_utility_trace('ld_hire_date '||ld_hire_date);
2912
2913 hr_utility_trace('Getting the actual number of days in pay period ...');
2914
2915 OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2916 FETCH csr_get_no_of_days_in_period INTO ln_days_in_a_period;
2917 CLOSE csr_get_no_of_days_in_period;
2918
2919 hr_utility_trace('Number of days in the pay period : '||ln_days_in_a_period);
2920
2921 hr_utility_trace('Getting the total number of days in pay period as deifned in GRE/LE...');
2922
2923 ln_le_id := hr_mx_utility.get_legal_employer(
2924 p_business_group_id => p_business_group_id
2925 ,p_tax_unit_id => p_tax_unit_id);
2926
2927 pay_mx_utility.get_no_of_days_for_org( p_business_group_id => p_business_group_id
2928 ,p_org_id => ln_le_id
2929 ,p_gre_or_le => 'LE'
2930 ,p_days_month => ln_le_days_month
2931 ,p_days_year => ln_le_days_year);
2932
2933 IF (ln_le_days_month IS NULL OR ln_le_days_month = -999) THEN
2934 ln_le_days_month := ln_days_in_a_period ;
2935 END IF;
2936
2937 /*ln_pay_period_days := pay_mx_utility.get_days_in_pay_period( p_business_group_id
2938 ,p_tax_unit_id
2939 ,ln_payroll_id);*/
2940 hr_utility_trace('Average days in the month at LE :'||to_char(ln_le_days_month));
2941
2942 OPEN csr_def_bal_id ('ISR Non Working Days','_ASG_GRE_RUN');
2943 FETCH csr_def_bal_id INTO ln_def_bal_id;
2944 CLOSE csr_def_bal_id;
2945
2946 hr_utility_trace('ISR Non Working Days def bal id '||to_char(ln_def_bal_id));
2947 ln_bal_ISR_non_wrkd_days := pay_balance_pkg.get_value(ln_def_bal_id,
2948 p_assignment_action_id,
2949 p_tax_unit_id,
2950 NULL,
2951 NULL,
2952 NULL,
2953 NULL,
2954 NULL,
2955 NULL,
2956 'TRUE');
2957 hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
2958
2959 hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2960 ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2961 hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2962
2963 hr_utility_trace('getting worked days.. ');
2964 ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
2965
2966 ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
2967
2968 hr_utility_trace('Actual worked days in the period for ISR Tax '||to_char(ln_worked_days));
2969 hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
2970 --End of Bug Fix 6852627
2971 IF to_char(l_date_paid,'yyyymmdd')
2972 >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2973 hr_utility_trace('ISr Proration in 2008');
2974 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
2975
2976 ln_ISR_proj_subject := ROUND(((ln_ISR_proj_subject * ln_le_days_month) / ln_le_worked_days),2);
2977
2978 hr_utility_trace('ISR Projected value in the pay period '||to_char(ln_ISR_proj_subject));
2979 END IF;
2980
2981 END IF;
2982
2983 hr_utility_trace('Final Subject amount after proration: '||ln_ISR_proj_subject);
2984 /* Article 113, 114 and 115 */
2985
2986 ln_fixed_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2987 p_business_group_id
2988 ,p_isr_rates_table
2989 ,'Fixed Rate'
2990 ,TO_CHAR(ln_ISR_proj_subject)));
2991
2992 hr_utility_trace('ln_fixed_rate: '||ln_fixed_rate);
2993
2994 ln_marginal_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2995 p_business_group_id
2996 ,p_isr_rates_table
2997 ,'Marginal Rate'
2998 ,TO_CHAR(ln_ISR_proj_subject)));
2999
3000 hr_utility_trace('ln_marginal_rate: '||ln_marginal_rate);
3001
3002 ln_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3003 p_business_group_id
3004 ,p_isr_rates_table
3005 ,'Lower Bound'
3006 ,TO_CHAR(ln_ISR_proj_subject)));
3007
3008 hr_utility_trace('ln_lower_limit: '||ln_lower_limit);
3009
3010 ln_marginal_tax := (ln_marginal_rate/100) * (ln_ISR_proj_subject -
3011 ln_lower_limit);
3012
3013 hr_utility_trace('ln_marginal_tax: '||ln_marginal_tax);
3014
3015 p_isr_calculated := ln_fixed_rate + ln_marginal_tax;
3016
3017 hr_utility_trace('p_isr_calculated: '||p_isr_calculated);
3018
3019 IF TO_CHAR(l_date_paid,'yyyymmdd')
3020 < TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
3021
3022 /* Subsidy Calculation */
3023 ln_fixed_subsidy := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3024 p_business_group_id
3025 ,p_subsidy_table
3026 ,'Fixed Rate'
3027 ,TO_CHAR(ln_ISR_proj_subject)));
3028
3029 hr_utility_trace('ln_fixed_subsidy: '||ln_fixed_subsidy);
3030
3031 ln_marginal_subsidy := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3032 p_business_group_id
3033 ,p_subsidy_table
3034 ,'Marginal Rate'
3035 ,TO_CHAR(ln_ISR_proj_subject)));
3036
3037 hr_utility_trace('ln_marginal_subsidy: '||ln_marginal_subsidy);
3038
3039 ln_subsidy_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3040 p_business_group_id
3041 ,p_subsidy_table
3042 ,'Lower Bound'
3043 ,TO_CHAR(ln_ISR_proj_subject)));
3044
3045
3046 hr_utility_trace('ln_subsidy_lower_limit: '||ln_subsidy_lower_limit);
3047
3048 ln_tax_subsidy_percentage := hr_mx_utility.get_tax_subsidy_percent(
3049 p_business_group_id
3050 ,p_tax_unit_id);
3051
3052 hr_utility_trace('ln_tax_subsidy_percentage: '||ln_tax_subsidy_percentage);
3053
3054 /*OPEN csr_get_process_type;
3055 FETCH csr_get_process_type INTO lv_action_type,
3056 lv_calc_mode,
3057 lv_process;
3058 CLOSE csr_get_process_type;*/
3059
3060 IF (p_calc_mode = 'BEST' OR p_calc_mode = 'ARTICLE141') THEN
3061 -- Calculate subsidy for Article 141
3062 hr_utility_trace('Calculating subsidy for Article 141 ...');
3063 ln_total_subsidy := ln_fixed_rate * (ln_fixed_subsidy/100) +
3064 (ln_marginal_subsidy/100) *
3065 (ln_marginal_rate/100) *
3066 (ln_ISR_proj_subject - ln_subsidy_lower_limit);
3067 ELSE
3068 ln_total_subsidy := ln_fixed_subsidy +
3069 (ln_marginal_subsidy/100) *
3070 (ln_marginal_rate/100) *
3071 (ln_ISR_proj_subject - ln_subsidy_lower_limit);
3072 END IF;
3073 hr_utility_trace('ln_total_subsidy: '||ln_total_subsidy);
3074
3075 p_isr_creditable_subsidy := (ln_tax_subsidy_percentage/100) *
3076 ln_total_subsidy;
3077
3078 hr_utility_trace('p_isr_creditable_subsidy: '||p_isr_creditable_subsidy);
3079
3080 p_isr_non_creditable_subsidy := ln_total_subsidy - p_isr_creditable_subsidy;
3081
3082 hr_utility_trace('p_isr_non_creditable_subsidy: '||
3083 p_isr_non_creditable_subsidy);
3084
3085 ELSE
3086 p_isr_creditable_subsidy := 0;
3087 p_isr_non_creditable_subsidy := 0;
3088 END IF; -- end of subsidy calc
3089
3090 /* Credit To Salary Calculation */
3091
3092 /* Bug#8438155: created new cursor csr_get_compute_subsidy_flag to fetch
3093 the value 'Compute Subsidy for Employment' from 'MX Statutory Info'
3094 If this value is 'N' then below subsidy calculation will be skipped */
3095
3096 OPEN csr_get_compute_subsidy_flag;
3097 FETCH csr_get_compute_subsidy_flag into l_compute_subsidy_flag;
3098 CLOSE csr_get_compute_subsidy_flag;
3099
3100 hr_utility_trace('l_compute_subsidy_flag '||l_compute_subsidy_flag);
3101 /*bug7445486 bug#8438155*/
3102 IF ( p_credit_to_salary_table <> 'NONE' and P_CALC_MODE <>'ARTICLE142' AND l_compute_subsidy_flag <> 'N') THEN
3103
3104 hr_utility_trace('inside subsidy '||p_credit_to_salary_table|| ' --- '||TO_CHAR(ln_ISR_proj_subject));
3105 p_credit_to_salary := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3106 p_business_group_id
3107 ,p_credit_to_salary_table
3108 ,'Amount'
3109 ,TO_CHAR(ln_ISR_proj_subject)));
3110
3111 P_CREDIT_TO_SALARY := nvl(P_CREDIT_TO_SALARY,0);
3112
3113 IF TO_CHAR(l_date_paid,'yyyymmdd')
3114 >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
3115
3116 OPEN csr_get_payroll_id; /*bug#8932102 */
3117 FETCH csr_get_payroll_id INTO ln_payroll_id;
3118 CLOSE csr_get_payroll_id;
3119
3120 OPEN csr_get_hire_date;
3121 FETCH csr_get_hire_date INTO ld_hire_date;
3122 CLOSE csr_get_hire_date;
3123
3124 hr_utility_trace('payroll_id '||ln_payroll_id);
3125 hr_utility_trace('ld_hire_date '||ld_hire_date);
3126
3127 -- get ISR Subject balance for month
3128
3129 OPEN csr_def_bal_id ('ISR Subject','_ASG_MTD');
3130 FETCH csr_def_bal_id INTO ln_def_bal_id;
3131 CLOSE csr_def_bal_id;
3132
3133 hr_utility_trace('ISR Subject def bal id '||ln_def_bal_id);
3134
3135 ln_bal_amt := pay_balance_pkg.get_value(ln_def_bal_id,
3136 P_ASSIGNMENT_ACTION_ID,
3137 p_tax_unit_id,
3138 NULL,
3139 NULL,
3140 NULL,
3141 NULL,
3142 NULL,
3143 NULL,
3144 'TRUE');
3145
3146 hr_utility_trace('MTD Subject Amt - ln_bal_amt '||to_char(ln_bal_amt));
3147 /*Get the previous ISR Subject adjusted amount due to proration
3148 this will have impact only in semi-monthly and weekely payroll*/
3149 OPEN csr_def_bal_id ('ISR Subject Adjusted','_ASG_MTD');
3150 FETCH csr_def_bal_id INTO ln_def_bal_id;
3151 CLOSE csr_def_bal_id;
3152
3153 hr_utility_trace('ISR Subject Adjusted def bal id '||ln_def_bal_id);
3154 ln_ISR_subj_adj := pay_balance_pkg.get_value(ln_def_bal_id,
3155 P_ASSIGNMENT_ACTION_ID,
3156 p_tax_unit_id,
3157 null,
3158 null,
3159 null,
3160 null,
3161 null,
3162 null,
3163 'TRUE');
3164 hr_utility_trace('ISR Subject Adjusted Amount MTD - ln_ISR_subj_adj'||ln_ISR_subj_adj);
3165 -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
3166 -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
3167 -- get ISR Subsidy for Employement balance for month
3168 ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
3169 hr_utility_trace('Actual subject value is ln_ISR_subj_adj + ln_ISR_proj_subject '||ln_ISR_subj_adj);
3170
3171 OPEN csr_def_bal_id ('ISR Subsidy for Employment','_ASG_MTD');
3172 FETCH csr_def_bal_id INTO ln_def_bal_id;
3173 CLOSE csr_def_bal_id;
3174
3175 hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
3176
3177 ln_bal_sub := pay_balance_pkg.get_value(ln_def_bal_id,
3178 P_ASSIGNMENT_ACTION_ID,
3179 p_tax_unit_id,
3180 NULL,
3181 NULL,
3182 NULL,
3183 NULL,
3184 NULL,
3185 NULL,
3186 'TRUE');
3187
3188 hr_utility_trace('ISR Sub for Empl Bal Sub '||to_char(ln_bal_sub));
3189
3190 OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid','_ASG_MTD');
3191 FETCH csr_def_bal_id INTO ln_def_bal_id;
3192 CLOSE csr_def_bal_id;
3193
3194 hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
3195
3196 ln_bal_sub_paid := pay_balance_pkg.get_value(ln_def_bal_id,
3197 P_ASSIGNMENT_ACTION_ID,
3198 p_tax_unit_id,
3199 NULL,
3200 NULL,
3201 NULL,
3202 NULL,
3203 NULL,
3204 NULL,
3205 'TRUE');
3206
3207 hr_utility_trace('ISR Sub for Empl Bal Sub Paid '||to_char(ln_bal_sub_paid));
3208
3209 if ln_payroll_id is not null then
3210 select min(period_type)
3211 into lv_period_type
3212 from pay_payrolls_f
3213 where payroll_id = ln_payroll_id;
3214 end if;
3215
3216 OPEN csr_get_first_pay_date;
3217 FETCH csr_get_first_pay_date INTO ld_first_pay_date;
3218 CLOSE csr_get_first_pay_date;
3219
3220 ln_isr_subject_mtd := CONVERT_INTO_MONTHLY_AVG_SAL (p_business_group_id
3221 ,p_tax_unit_id
3222 ,ln_payroll_id
3223 ,ln_ISR_proj_subject
3224 ,l_date_paid
3225 ,ld_hire_date
3226 ,ld_first_pay_date
3227 ,ln_period_number)
3228 + (ln_ISR_subj_adj - ln_ISR_proj_subject);
3229
3230 hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
3231 hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
3232 hr_utility_trace('ln_period_number : '||ln_period_number);
3233
3234 open csr_get_period_count_in_month;
3235 loop
3236 fetch csr_get_period_count_in_month into ln_mult_num,
3237 ln_period_end_mtd,
3238 ln_period_start_mtd,
3239 lv_period_type;
3240 exit when csr_get_period_count_in_month%NOTFOUND;
3241
3242 select least(count(*),1)
3243 into ln_pre_date_paid
3244 from pay_payroll_actions ppa,
3245 pay_assignment_actions paa,
3246 per_time_periods ptp
3247 where ppa.payroll_action_id = paa.payroll_action_id
3248 and ptp.time_period_id = ppa.time_period_id
3249 and paa.assignment_id = p_assignment_id
3250 and to_char(ptp.end_date,'yyyymm') = to_char(l_date_paid,'yyyymm')
3251 and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
3252 AND ld_hire_date <= ptp.end_date;
3253
3254 hr_utility_trace('l_date_paid '||to_char(l_date_paid,'yyyymm'));
3255 hr_utility_trace('ln_pre_date_paid '||to_char(ln_pre_date_paid));
3256
3257 IF l_date_paid >= ln_period_start_mtd and
3258 l_date_paid <= ln_period_end_mtd then
3259 ln_period_number := ln_period_number - ln_pre_date_paid;
3260 hr_utility_trace('Actual ln_period_number '||to_char(ln_period_number));
3261 end if;
3262
3263 end loop;
3264 close csr_get_period_count_in_month;
3265
3266
3267 IF lv_period_type = 'Semi-Month' THEN
3268 ln_days_in_period_sub_empl := 15;
3269 ELSIF lv_period_type = 'Ten Days' then
3270 ln_days_in_period_sub_empl := 10;
3271 ELSE
3272 ln_days_in_period_sub_empl := ln_days_in_a_period; /*bug 7677805*/
3273 END IF ;
3274 p_credit_to_salary := FND_NUMBER.canonical_to_number(get_table_value (
3275 p_business_group_id
3276 ,p_credit_to_salary_table
3277 ,'Amount'
3278 ,TO_CHAR(ln_ISR_subj_adj)
3279 ,l_date_paid
3280 ,ln_days_in_a_period
3281 ,ln_period_number
3282 ,lv_period_type));
3283
3284 hr_utility_trace('Total Subsidy for Empl '||to_char(p_credit_to_salary));
3285
3286 ln_credit_to_salary_max := p_credit_to_salary;
3287
3288 P_CREDIT_TO_SALARY := (ln_credit_to_salary_max/30.4) * ln_days_in_period_sub_empl;
3289
3290 ln_credit_to_salary_curr := P_CREDIT_TO_SALARY;
3291
3292 hr_utility_trace('Current Period Subsidy for Empl '||to_char(p_credit_to_salary));
3293
3294 select count(*) max_row
3295 into ln_max_row
3296 from PER_TIME_PERIODS ptp1
3297 where payroll_id = ln_payroll_id
3298 and to_char(l_date_paid,'yyyymm') = to_char(end_date,'yyyymm');
3299
3300 open csr_get_period_count_in_month;
3301 loop
3302 fetch csr_get_period_count_in_month into ln_mult_num,
3303 ln_period_end_mtd,
3304 ln_period_start_mtd,
3305 lv_period_type;
3306 exit when csr_get_period_count_in_month%NOTFOUND;
3307 if ln_period_end_mtd = l_date_earned then
3308
3309 if to_char(ln_period_end_mtd,'yyyymm') = to_char(l_date_paid,'yyyymm') and
3310 to_char(ln_period_end_mtd,'yyyymmdd') >= to_char(last_day(l_date_paid),'yyyymmdd') then
3311
3312 P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
3313 P_CREDIT_TO_SALARY * ln_mult_num );
3314 hr_utility_trace('outside/last day of month '||to_char(p_credit_to_salary));
3315 else
3316 P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
3317 P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
3318 hr_utility_trace('With in month '||to_char(p_credit_to_salary));
3319 end if;
3320
3321 hr_utility_trace('max allowd Subsidy for Empl '||to_char(p_credit_to_salary));
3322 IF l_date_paid >= ln_period_start_mtd AND
3323 l_date_paid <= ln_period_end_mtd AND
3324 (lv_period_type = 'Calendar Month' OR (lv_period_type = 'Semi-Month' AND ln_period_number = 2)
3325 OR (lv_period_type = 'Ten Days' AND ln_period_number = 3))
3326 THEN
3327 P_CREDIT_TO_SALARY := ln_credit_to_salary_max;
3328 hr_utility_trace('Final period Subsidy for Empl '||to_char(p_credit_to_salary));
3329 end if;
3330 P_CREDIT_TO_SALARY := P_CREDIT_TO_SALARY - ln_bal_sub;
3331 hr_utility_trace('Subsidy for Empl '||to_char(p_credit_to_salary));
3332 end if;
3333 end loop;
3334 close csr_get_period_count_in_month;
3335 END IF;
3336 ELSE
3337 p_credit_to_salary := 0;
3338 END IF;
3339
3340 P_CREDIT_TO_SALARY := nvl(P_CREDIT_TO_SALARY,0);
3341 hr_utility_trace('p_credit_to_salary: '||p_credit_to_salary);
3342
3343 /* ISR Withheld Calculation */
3344
3345 ln_isr_withheld := p_isr_calculated -
3346 p_isr_creditable_subsidy -
3347 p_credit_to_salary;
3348
3349
3350 hr_utility_trace('ln_isr_withheld B4: '||ln_isr_withheld);
3351
3352 /*Incuded the proration logic based on balance ISR Non working days*/
3353 --Start of Bug fix 6852627
3354 IF to_char(l_date_paid,'yyyymmdd')
3355 >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
3356 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
3357
3358 hr_utility_trace('-- Non workings days are feeded, need to proproate ISR Tax --');
3359
3360 hr_utility_trace('Getting the table factor value for ISR Tax calculation');
3361 ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
3362
3363 hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
3364
3365 hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
3366
3367 /*ln_pro_sub_emp := (p_credit_to_salary/ln_ISR_table_factor)* ln_worked_days ;
3368
3369 hr_utility_trace('Prorated Subsidy For Employment is '||TO_CHAR(ln_pro_sub_emp));*/
3370
3371 ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
3372
3373 hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
3374 hr_utility_trace('Start of ISR Subject proration ');
3375
3376 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
3377 hr_utility_trace('ISR Non Worked Days value is invalid ');
3378 hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
3379 hr_utility.raise_error;
3380
3381 ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
3382 ln_isr_withheld := 0;
3383 p_credit_to_salary_paid := 0;
3384 p_isr_calculated := 0;
3385 p_credit_to_salary := 0;
3386
3387 END IF;
3388 END IF;
3389 --End of Bug Fix 6852627
3390
3391 IF ln_isr_withheld < 0 THEN
3392 p_credit_to_salary_paid := ABS(ln_isr_withheld);
3393 ln_isr_withheld := 0;
3394 ELSE
3395 p_credit_to_salary_paid := 0;
3396 END IF;
3397 /*To feed ISR Subject Adjusted Balance*/
3398 IF TO_CHAR(l_date_paid,'yyyymmdd')
3399 >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
3400 p_isr_creditable_subsidy := ln_ISR_proj_subject;
3401 hr_utility_trace(' Feeding ISR Subject Adjusted Balance value in 2008 = p_isr_creditable_subsidy'||p_isr_creditable_subsidy);
3402 END IF;
3403 IF p_run_type = 'ADJTAX' OR p_run_type = 'MTDTAXADJ' THEN
3404
3405 IF p_run_type = 'ADJTAX' THEN
3406 lv_dimension_type := '_ASG_YTD';
3407 lv_proration_table := 'isr rates_annual';
3408 hr_utility_trace('The Tax Adjustment type is Periodic Tax Adjustment ');
3409 ELSIF p_run_type = 'MTDTAXADJ' THEN
3410 lv_dimension_type := '_ASG_MTD';
3411 lv_proration_table := 'isr rates_month';
3412 hr_utility_trace('The Tax Adjustment type is Monthly Tax Adjustment ');
3413 END IF;
3414
3415 OPEN csr_def_bal_id ('ISR Subject',lv_dimension_type);
3416 FETCH csr_def_bal_id INTO ln_def_bal_id;
3417 CLOSE csr_def_bal_id;
3418
3419 hr_utility_trace('ISR Subject def bal id '||ln_def_bal_id);
3420 ln_ISR_subj_adj_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3421 P_ASSIGNMENT_ACTION_ID,
3422 p_tax_unit_id,
3423 null,
3424 null,
3425 null,
3426 null,
3427 null,
3428 null,
3429 'TRUE');
3430 hr_utility_trace('YTD ISR Subject def bal is '||ln_ISR_subj_adj_ytd);
3431
3432 tot_sub_basis := ln_ISR_subj_adj_ytd;
3433
3434 hr_utility_trace('Total Subject Basis for tax adjustment is '||tot_sub_basis);
3435
3436 OPEN csr_def_bal_id ('ISR Calculated',lv_dimension_type);
3437 FETCH csr_def_bal_id INTO ln_def_bal_id;
3438 CLOSE csr_def_bal_id;
3439
3440 hr_utility_trace('ISR Calculated def bal id '||ln_def_bal_id);
3441 ln_ISR_calculated_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3442 P_ASSIGNMENT_ACTION_ID,
3443 p_tax_unit_id,
3444 null,
3445 null,
3446 null,
3447 null,
3448 null,
3449 null,
3450 'TRUE');
3451 hr_utility_trace('YTD ISR Calculated Value is '||ln_ISR_calculated_ytd);
3452
3453
3454 OPEN csr_def_bal_id ('ISR Withheld',lv_dimension_type);
3455 FETCH csr_def_bal_id INTO ln_def_bal_id;
3456 CLOSE csr_def_bal_id;
3457
3458 hr_utility_trace('ISR Withheld def bal id '||to_char(ln_def_bal_id));
3459
3460 ln_ISR_withheld_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3461 P_ASSIGNMENT_ACTION_ID,
3462 p_tax_unit_id,
3463 NULL,
3464 NULL,
3465 NULL,
3466 NULL,
3467 NULL,
3468 NULL,
3469 'TRUE');
3470
3471 hr_utility_trace('ISR withheld is '||ln_ISR_withheld_ytd);
3472
3473 OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid',lv_dimension_type);
3474 FETCH csr_def_bal_id INTO ln_def_bal_id;
3475 CLOSE csr_def_bal_id;
3476
3477 hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
3478
3479 ln_ISR_subsidy_paid_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3480 P_ASSIGNMENT_ACTION_ID,
3481 p_tax_unit_id,
3482 NULL,
3483 NULL,
3484 NULL,
3485 NULL,
3486 NULL,
3487 NULL,
3488 'TRUE');
3489
3490 hr_utility_trace('ISR subsidy for employment paid is '||ln_ISR_subsidy_paid_ytd);
3491
3492 ln_ISR_tax_to_charge_ytd := ln_ISR_withheld_ytd - ln_ISR_subsidy_paid_ytd;
3493 hr_utility_trace('YTD ISR Sub for Empl Bal Sub Paid '||to_char(ln_ISR_tax_to_charge_ytd));
3494 OPEN csr_def_bal_id ('ISR Subsidy for Employment',lv_dimension_type);
3495 FETCH csr_def_bal_id INTO ln_def_bal_id;
3496 CLOSE csr_def_bal_id;
3497
3498 hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
3499
3500 ln_ISR_subsidy_empl_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3501 P_ASSIGNMENT_ACTION_ID,
3502 p_tax_unit_id,
3503 NULL,
3504 NULL,
3505 NULL,
3506 NULL,
3507 NULL,
3508 NULL,
3509 'TRUE');
3510 hr_utility_trace('YTD ISR Sub for Empl Bal Sub '||to_char(ln_ISR_subsidy_empl_ytd));
3511 /*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
3512 tot_subsidy_empl := ln_ISR_subsidy_empl_ytd + ln_pro_sub_emp;
3513 ELSE*/
3514 tot_subsidy_empl := ln_ISR_subsidy_empl_ytd + p_credit_to_salary;
3515 --END IF;
3516 hr_utility_trace('Total Subsidy for Employment '||to_char(tot_subsidy_empl));
3517 OPEN csr_def_bal_id ('ISR Non Working Days',lv_dimension_type);
3518 FETCH csr_def_bal_id INTO ln_def_bal_id;
3519 CLOSE csr_def_bal_id;
3520
3521 hr_utility_trace('ISR Non Working Days def bal id '||to_char(ln_def_bal_id));
3522 ln_bal_ISR_non_wrkd_days_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3523 p_assignment_action_id,
3524 p_tax_unit_id,
3525 NULL,
3526 NULL,
3527 NULL,
3528 NULL,
3529 NULL,
3530 NULL,
3531 'TRUE');
3532 hr_utility_trace('YTD ISR Non Working Days Bal '||to_char(ln_bal_ISR_non_wrkd_days_ytd));
3533 tot_ISR_non_wrkd_days := ln_bal_ISR_non_wrkd_days_ytd;
3534 hr_utility_trace('Total Non Worked days '||to_char(tot_ISR_non_wrkd_days));
3535
3536 IF p_run_type = 'ADJTAX' THEN
3537 OPEN csr_get_no_of_days_in_ytd(ln_payroll_id);
3538 FETCH csr_get_no_of_days_in_ytd INTO ln_days_in_ytd, ld_period_start_date, ld_period_end_date;
3539 CLOSE csr_get_no_of_days_in_ytd;
3540 hr_utility_trace('Period Start date is'||to_char(ld_period_start_date));
3541 hr_utility_trace('Period End date is'||to_char(ld_period_end_date));
3542
3543 OPEN csr_get_act_hire_date;
3544 FETCH csr_get_act_hire_date INTO ld_act_hire_date;
3545 CLOSE csr_get_act_hire_date;
3546
3547 hr_utility_trace('The actual Hire Date is'||to_char(ld_act_hire_date));
3548
3549 IF ld_act_hire_date >ld_period_start_date THEN
3550 ln_days_in_ytd := ld_period_end_date - ld_act_hire_date +1;
3551 hr_utility_trace('Total days in YTD'||to_char(ln_days_in_ytd));
3552 END IF;
3553
3554 IF ln_days_in_ytd > 365 THEN
3555 ln_days_in_ytd := 365;
3556 END IF;
3557
3558 ELSIF p_run_type = 'MTDTAXADJ' THEN
3559 OPEN csr_get_no_of_days_in_mtd (ln_payroll_id);
3560 FETCH csr_get_no_of_days_in_mtd INTO ln_days_in_ytd , ld_period_end_date, ld_period_start_date;
3561 CLOSE csr_get_no_of_days_in_mtd;
3562 hr_utility_trace('Period Start date is'||to_char(ld_period_start_date));
3563 hr_utility_trace('Period End date is'||to_char(ld_period_end_date));
3564
3565 OPEN csr_get_act_hire_date;
3566 FETCH csr_get_act_hire_date INTO ld_act_hire_date;
3567 CLOSE csr_get_act_hire_date;
3568
3569 hr_utility_trace('The actual Hire Date is'||to_char(ld_act_hire_date));
3570
3571 IF ld_act_hire_date > ld_period_start_date THEN
3572 ln_days_in_ytd := ld_period_end_date - ld_act_hire_date +1;
3573 hr_utility_trace('Total days in YTD'||to_char(ln_days_in_ytd));
3574 END IF;
3575 END IF;
3576
3577 ln_wrkd_days_in_adj_period := ln_days_in_ytd - tot_ISR_non_wrkd_days;
3578 hr_utility_trace('Total Worked days YTD '||to_char(ln_wrkd_days_in_adj_period));
3579
3580 IF p_run_type = 'ADJTAX' THEN
3581 ln_proration_fac := ln_wrkd_days_in_adj_period/365;
3582 hr_utility_trace('Proration factor for YTD adj'||to_char(ln_proration_fac));
3583
3584 ELSIF p_run_type = 'MTDTAXADJ' THEN
3585 ln_no_days_in_month := LAST_DAY(ld_period_end_date) - TRUNC(ld_period_end_date, 'MONTH') +1;
3586 hr_utility_trace('The total number of days in the adjustment month'||to_char(ln_no_days_in_month));
3587 ln_proration_fac := (ln_wrkd_days_in_adj_period * (30.4/ln_no_days_in_month))/30.4;
3588 hr_utility_trace('Proration factor for MTD adj'||to_char(ln_proration_fac));
3589 END IF;
3590
3591 OPEN csr_get_table_id (lv_proration_table);
3592 FETCH csr_get_table_id INTO ln_table_id;
3593 CLOSE csr_get_table_id;
3594
3595 hr_utility_trace('Table Id is'||to_char(ln_table_id));
3596
3597 OPEN csr_get_table_value (ln_table_id, ln_proration_fac, 'FIXED RATE', tot_sub_basis);
3598 FETCH csr_get_table_value INTO ln_fixed_rate_ytd;
3599 CLOSE csr_get_table_value;
3600 hr_utility_trace('Fixed Rate is '||to_char(ln_fixed_rate_ytd));
3601
3602 OPEN csr_get_table_value (ln_table_id, ln_proration_fac, 'LOWER BOUND', tot_sub_basis);
3603 FETCH csr_get_table_value INTO ln_lower_bound_ytd;
3604 CLOSE csr_get_table_value;
3605 hr_utility_trace('Lower Bound value is '||to_char(ln_lower_bound_ytd));
3606
3607 OPEN csr_get_table_value (ln_table_id, ln_proration_fac, 'MARGINAL RATE', tot_sub_basis);
3608 FETCH csr_get_table_value INTO ln_marginal_rate_ytd;
3609 CLOSE csr_get_table_value;
3610
3611 ln_marginal_rate_ytd := ln_marginal_rate_ytd/ln_proration_fac;
3612 hr_utility_trace('Marginal Rate is '||to_char(ln_marginal_rate_ytd));
3613
3614 ln_taxable_subject_ytd := tot_sub_basis-ln_lower_bound_ytd;
3615 hr_utility_trace('The Difference amount is '||to_char(ln_taxable_subject_ytd));
3616
3617 ln_marginal_tax_ytd := ln_taxable_subject_ytd * (ln_marginal_rate_ytd/100);
3618 hr_utility_trace('The Marginal Tax '||to_char(ln_marginal_tax_ytd));
3619
3620 ln_total_tax_ytd := ln_marginal_tax_ytd + ln_fixed_rate_ytd;
3621 hr_utility_trace('The Total Tax is '||to_char(ln_total_tax_ytd));
3622
3623 p_isr_calculated := ln_total_tax_ytd - ln_ISR_calculated_ytd;
3624
3625 ln_net_tax_ytd := ln_total_tax_ytd - tot_subsidy_empl;
3626 hr_utility_trace('The Net Tax is '||to_char(ln_net_tax_ytd));
3627
3628 ln_adjusted_tax_ytd := ln_net_tax_ytd - ln_ISR_tax_to_charge_ytd;
3629 hr_utility_trace('The Adjusted Tax is '||to_char(ln_adjusted_tax_ytd));
3630
3631 --ln_isr_withheld := ROUND(ln_adjusted_tax_ytd,2);
3632
3633
3634 /*
3635 Bug:13905014 The adjustment amount must be computed and shared between tax and Subsidy for
3636 Employment depending on the balance paid or withheld and the subject amount
3637 in the last pay period of the month.
3638 */
3639
3640 /*IF ln_isr_withheld < 0 THEN
3641 p_credit_to_salary_paid := ABS(ln_isr_withheld);
3642 ln_isr_withheld := 0;
3643 ELSE
3644 p_credit_to_salary_paid := 0;
3645 END IF;*/
3646
3647 IF ln_net_tax_ytd < 0 THEN
3648 IF ln_ISR_tax_to_charge_ytd < 0 THEN
3649 /* Start of Bug 16032260 fix*/
3650 /* Check if there was tax withheld caused by a non-periodic payment */
3651 IF ln_ISR_withheld_ytd > 0 THEN
3652 /* non periodic wuthheld tax is reimbursed*/
3653 ln_isr_withheld := ln_ISR_withheld_ytd * -1;
3654 p_credit_to_salary_paid := (ROUND(ln_adjusted_tax_ytd,2) * -1)-(ln_ISR_withheld_ytd);
3655 ELSE
3656 ln_isr_withheld := 0;
3657 p_credit_to_salary_paid := ROUND(ln_adjusted_tax_ytd,2) * -1;
3658 /* End of Bug 16032260 fix*/
3659 END IF;
3660 ELSE
3661 ln_isr_withheld := ln_ISR_tax_to_charge_ytd * -1;
3662 p_credit_to_salary_paid := ln_net_tax_ytd * -1;
3663 END IF;
3664 ELSE
3665 IF ln_ISR_tax_to_charge_ytd < 0 THEN
3666 ln_isr_withheld := ln_net_tax_ytd;
3667 p_credit_to_salary_paid := ln_ISR_tax_to_charge_ytd;
3668 ELSE
3669 ln_isr_withheld := ROUND(ln_adjusted_tax_ytd,2);
3670 p_credit_to_salary_paid := 0;
3671 END IF;
3672 END IF;
3673
3674 -- End Fix for Bug 13905014
3675
3676 END IF;
3677
3678 hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
3679 hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
3680 hr_utility_trace('Leaving '||l_proc_name);
3681
3682 RETURN ln_isr_withheld;
3683
3684 EXCEPTION
3685 WHEN OTHERS THEN
3686 hr_utility_trace('Exception in '||l_proc_name||': '||SQLERRM);
3687 RAISE;
3688 END CALCULATE_ISR_TAX;
3689
3690 /****************************************************************************
3691 Name : CONVERT_MONTHLY_TO_PERIOD
3692 Description : This function has
3693 1. Input Parameters as Contexts:
3694 - BUSINESS_GROUP_ID
3695 - TAX_UNIT_ID
3696 - PAYROLL_ID
3697 2. Input Parameters as Parameter:
3698 - PERIODIC_EARNINGS
3699 *****************************************************************************/
3700
3701 /*FUNCTION CONVERT_MONTHLY_TO_PERIOD ( p_business_group_id NUMBER
3702 ,p_tax_unit_id NUMBER
3703 ,p_payroll_id NUMBER
3704 ,p_periodic_value NUMBER
3705 ,P_date_earned DATE)
3706 RETURN NUMBER IS
3707
3708 ln_periodic_value NUMBER;
3709 ln_days_in_a_period NUMBER;
3710
3711 CURSOR csr_get_period_count_in_month IS
3712 select end_date - start_date +1 period_days
3713 from PER_TIME_PERIODS ptp
3714 where payroll_id = p_payroll_id
3715 and to_char(p_date_earned,'yyyymmdd') = to_char(end_date,'yyyymmdd');
3716
3717 BEGIN
3718
3719 hr_utility_trace('Entering ..CONVERT_MONTHLY_TO_PERIOD');
3720 hr_utility_trace('p_periodic_value: ' ||p_periodic_value);
3721
3722 open csr_get_period_count_in_month;
3723 fetch csr_get_period_count_in_month into ln_days_in_a_period;
3724 close csr_get_period_count_in_month;
3725
3726 ln_periodic_value := (p_periodic_value/30.4) * ln_days_in_a_period;
3727
3728 hr_utility_trace('ln_periodic_value: ' ||ln_periodic_value);
3729 hr_utility_trace('Leaving ..CONVERT_MONTHLY_TO_PERIOD');
3730
3731 RETURN ln_periodic_value;
3732
3733 END CONVERT_MONTHLY_TO_PERIOD; /*
3734
3735 /****************************************************************************
3736 Name : CONVERT_INTO_MONTHLY_SALARY
3737 Description : This function has
3738 1. Input Parameters as Contexts:
3739 - BUSINESS_GROUP_ID
3740 - TAX_UNIT_ID
3741 - PAYROLL_ID
3742 2. Input Parameters as Parameter:
3743 - PERIODIC_EARNINGS
3744 *****************************************************************************/
3745
3746 FUNCTION CONVERT_INTO_MONTHLY_SALARY ( p_business_group_id NUMBER
3747 ,p_tax_unit_id NUMBER
3748 ,p_payroll_id NUMBER
3749 ,p_periodic_earnings NUMBER)
3750 RETURN NUMBER IS
3751
3752 ln_periodic_earnings NUMBER;
3753 ln_days_in_a_month NUMBER;
3754 lv_period_type pay_all_payrolls_f.period_type%TYPE;
3755
3756 BEGIN
3757
3758 hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_SALARY');
3759 hr_utility_trace('p_periodic_earnings: ' ||p_periodic_earnings);
3760
3761 SELECT period_type
3762 INTO lv_period_type
3763 FROM pay_all_payrolls_f ppf,
3764 fnd_sessions fs
3765 WHERE payroll_id = p_payroll_id
3766 AND fs.effective_date BETWEEN ppf.effective_start_date
3767 AND ppf.effective_end_date
3768 AND fs.session_id = USERENV('sessionid');
3769
3770 ln_days_in_a_month := pay_mx_utility.get_days_in_month(
3771 p_business_group_id => p_business_group_id
3772 ,p_tax_unit_id => p_tax_unit_id
3773 ,p_payroll_id => p_payroll_id);
3774
3775
3776 IF lv_period_type = 'Calendar Month' THEN
3777
3778 ln_periodic_earnings := p_periodic_earnings;
3779
3780 ELSIF lv_period_type = 'Semi-Month' THEN
3781
3782 ln_periodic_earnings := (p_periodic_earnings / 15) * ln_days_in_a_month;
3783
3784 ELSIF lv_period_type = 'Week' THEN
3785
3786 ln_periodic_earnings := (p_periodic_earnings / 7) * ln_days_in_a_month;
3787
3788 ELSIF lv_period_type = 'Bi-Week' THEN
3789
3790 ln_periodic_earnings := (p_periodic_earnings / 14) * ln_days_in_a_month;
3791
3792 ELSIF lv_period_type = 'Ten Days' THEN
3793
3794 ln_periodic_earnings := (p_periodic_earnings / 10) * ln_days_in_a_month;
3795
3796
3797 END IF;
3798
3799 hr_utility_trace('ln_periodic_earnings: ' ||ln_periodic_earnings);
3800 hr_utility_trace('Leaving ..CONVERT_INTO_MONTHLY_SALARY');
3801
3802 RETURN ln_periodic_earnings;
3803
3804 END CONVERT_INTO_MONTHLY_SALARY;
3805
3806
3807 /****************************************************************************
3808 Name : CHECK_EE_SAL_CRITERIA
3809 Description : This function returns 'Y' if employee's annual gross earning
3810 is less than 300,000 MXN.
3811 *****************************************************************************/
3812
3813 FUNCTION CHECK_EE_SAL_CRITERIA
3814 (
3815 P_CTX_ASSIGNMENT_ID NUMBER
3816 ,P_CTX_DATE_EARNED DATE
3817 ) RETURN VARCHAR2 AS
3818
3819 CURSOR c_defined_balance_id(p_balance_name VARCHAR2
3820 ,p_dimension VARCHAR2)
3821 IS
3822 SELECT pdb.defined_balance_id
3823 FROM pay_balance_types pbt
3824 ,pay_balance_dimensions pbd
3825 ,pay_defined_balances pdb
3826 WHERE pbt.balance_name=p_balance_name
3827 AND pbd.database_item_suffix =p_dimension
3828 AND pbt.legislation_code = 'MX'
3829 AND pbd.legislation_code = 'MX'
3830 AND pbt.balance_type_id = pdb.balance_type_id
3831 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
3832
3833
3834 CURSOR c_assignment_action_id
3835 IS
3836 SELECT MAX(paa.assignment_action_id)
3837 FROM pay_assignment_actions paa
3838 ,pay_payroll_actions ppa
3839 WHERE paa.assignment_id =P_CTX_ASSIGNMENT_ID
3840 AND paa.payroll_action_id=ppa.payroll_action_id
3841 AND ppa.action_type in ('R','Q','I')
3842 AND ppa.date_earned <=P_CTX_DATE_EARNED;
3843
3844 l_flag VARCHAR2(4);
3845 l_capping_value VARCHAR(20);
3846 l_ignore NUMBER;
3847 l_pkg_value NUMBER;
3848 l_gross_earning NUMBER;
3849 l_bal_defined_id NUMBER;
3850 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
3851 BEGIN
3852 l_flag :='N';
3853 l_ignore:=0;
3854 l_gross_earning:=0;
3855 l_capping_value:=0;
3856 l_capping_value:='$Sys_Def$';
3857
3858 hr_utility_trace('Entering ..CHECK_EE_SAL_CRITERIA');
3859 OPEN c_defined_balance_id('Gross Earnings','_PER_YTD');
3860 FETCH c_defined_balance_id INTO l_bal_defined_id;
3861 CLOSE c_defined_balance_id;
3862
3863 OPEN c_assignment_action_id;
3864 FETCH c_assignment_action_id INTO l_assignment_action_id;
3865 CLOSE c_assignment_action_id;
3866
3867 IF(l_assignment_action_id IS NULL) THEN
3868 hr_utility_trace('Leaving ..CHECK_EE_SAL_CRITERIA');
3869 RETURN 'N';
3870 END IF;
3871
3872 l_pkg_value:=pay_mx_tax_functions.get_mx_tax_info
3873 ( P_CTX_BUSINESS_GROUP_ID => NULL,
3874 P_CTX_TAX_UNIT_ID => NULL,
3875 P_CTX_EFFECTIVE_DATE => P_CTX_DATE_EARNED,
3876 P_CTX_JURISDICTION_CODE => l_ignore,
3877 P_LEGISLATION_INFO_TYPE => 'MX Tax Adjustment Parameters',
3878 P_LEGISLATION_INFO1 => l_capping_value,
3879 P_LEGISLATION_INFO2 => l_ignore,
3880 P_LEGISLATION_INFO3 => l_ignore,
3881 P_LEGISLATION_INFO4 => l_ignore,
3882 P_LEGISLATION_INFO5 => l_ignore,
3883 P_LEGISLATION_INFO6 => l_ignore
3884 );
3885
3886 l_gross_earning:=pay_balance_pkg.get_value
3887 (p_defined_balance_id =>l_bal_defined_id,
3888 p_assignment_action_id =>l_assignment_action_id,
3889 p_tax_unit_id => NULL,
3890 p_jurisdiction_code => NULL,
3891 p_source_id => NULL,
3892 p_tax_group => NULL,
3893 p_date_earned => NULL);
3894
3895 IF(nvl(l_gross_earning,-1)>l_capping_value) THEN
3896 l_flag :='N';
3897 ELSIF (l_gross_earning <> 0) THEN
3898 l_flag :='Y';
3899 END IF;
3900 hr_utility_trace('l_flag: ' ||l_flag);
3901 hr_utility_trace('Leaving ..CHECK_EE_SAL_CRITERIA');
3902 RETURN l_flag;
3903
3904 END CHECK_EE_SAL_CRITERIA;
3905
3906
3907 /****************************************************************************
3908 Name : CHECK_EE_EMPLOYMENT_CRITERIA
3909 Description : This Function return 'Y' if employee is working continously
3910 between the given start date and end date
3911 *****************************************************************************/
3912
3913 FUNCTION CHECK_EE_EMPLOYMENT_CRITERIA
3914 (
3915 P_CTX_ASSIGNMENT_ID NUMBER,
3916 P_CTX_DATE_EARNED DATE
3917 ) RETURN VARCHAR2 AS
3918
3919 CURSOR csr_get_dates IS
3920 SELECT fnd_date.canonical_to_date(TO_CHAR(P_CTX_DATE_EARNED,'YYYY')
3921 ||'/'||hoi.org_information7),
3922 fnd_date.canonical_to_date(TO_CHAR(P_CTX_DATE_EARNED,'YYYY')
3923 ||'/'||hoi.org_information8),
3924 paf.person_id
3925 FROM hr_organization_information hoi
3926 ,per_assignments_f paf
3927 WHERE hoi.organization_id =
3928 hr_mx_utility.get_legal_employer(paf.business_group_id,
3929 per_mx_ssaffl_archive.derive_gre_from_loc_scl(
3930 paf.location_id
3931 ,paf.business_group_id
3932 ,paf.soft_coding_keyflex_id
3933 ,p_ctx_date_earned),
3934 p_ctx_date_earned)
3935 AND hoi.org_information_context = 'MX_TAX_REGISTRATION'
3936 AND paf.assignment_id = P_CTX_ASSIGNMENT_ID
3937 AND P_CTX_DATE_EARNED BETWEEN paf.effective_start_date
3938 AND paf.effective_end_date;
3939
3940 CURSOR csr_get_form37_status (p_start_date date,p_end_date date) IS
3941 SELECT 'N'
3942 FROM pay_assignment_actions paa
3943 ,pay_payroll_actions ppa
3944 WHERE paa.payroll_action_id=ppa.payroll_action_id
3945 AND paa.assignment_id =P_CTX_ASSIGNMENT_ID
3946 AND ppa.report_type='ISR_TAX_FORMAT37'
3947 AND ppa.date_earned BETWEEN p_start_date AND p_end_date;
3948
3949 CURSOR c_get_hire_date ( cp_person_id NUMBER
3950 ,cp_effective_date DATE ) IS
3951 SELECT MAX (pps.date_start), MAX(nvl(actual_termination_date,
3952 fnd_date.canonical_to_date('4712/12/31')))
3953 FROM per_periods_of_service pps
3954 WHERE pps.person_id = cp_person_id
3955 AND pps.date_start <= cp_effective_date;
3956 --bug 7604298
3957 CURSOR c_get_prev_term_date ( cp_person_id NUMBER
3958 ,cp_effective_date DATE ) IS
3959 SELECT MAX(actual_termination_date)
3960 FROM per_periods_of_service pps
3961 WHERE pps.person_id = cp_person_id
3962 AND pps.date_start <= cp_effective_date
3963 AND pps.actual_termination_date IS NOT NULL;
3964
3965
3966 l_proc_name VARCHAR2(100);
3967 l_flag VARCHAR2(4);
3968 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
3969 l_end_date DATE;
3970 l_start_date DATE;
3971 l_hire_date DATE;
3972 l_termination_date DATE;
3973 ln_person_id NUMBER;
3974 l_prev_term_date DATE;
3975
3976 BEGIN
3977 l_proc_name := g_proc_name ||'CHECK_EE_EMPLOYMENT_CRITERIA';
3978 hr_utility_trace('Entering '||l_proc_name);
3979 hr_utility_trace('P_CTX_ASSIGNMENT_ID = ' || p_ctx_assignment_id);
3980
3981 l_flag :='N';
3982 l_start_date := NULL;
3983
3984 OPEN csr_get_dates;
3985 FETCH csr_get_dates INTO l_start_date,
3986 l_end_date,
3987 ln_person_id;
3988 CLOSE csr_get_dates;
3989
3990
3991 HR_UTILITY.trace('l_start_date: '||l_start_date);
3992 HR_UTILITY.trace('l_end_date: '||l_end_date);
3993
3994 OPEN c_get_hire_date(ln_person_id,P_CTX_DATE_EARNED);
3995 FETCH c_get_hire_date INTO l_hire_date, l_termination_date;
3996 CLOSE c_get_hire_date;
3997
3998 HR_UTILITY.trace('l_hire_date: '||l_hire_date);
3999 HR_UTILITY.trace('l_termination_date: '||l_termination_date);
4000
4001 IF l_hire_date > l_start_date AND l_termination_date >= l_end_date THEN
4002 /*Added for bug 7604298 to pick up the re-hire employee when re-hire happens in next day*/
4003 HR_UTILITY.trace('About to check the re-hire condition ');
4004 OPEN c_get_prev_term_date(ln_person_id,P_CTX_DATE_EARNED);
4005 FETCH c_get_prev_term_date INTO l_prev_term_date;
4006 CLOSE c_get_prev_term_date;
4007 HR_UTILITY.trace('l_prev_term_date: '||l_prev_term_date);
4008 IF (l_prev_term_date IS NOT NULL) AND ((l_prev_term_date +1) = l_hire_date) THEN
4009 hr_utility_trace('Person re-hired in the next day ');
4010 RETURN ('Y');
4011 END IF;
4012
4013 hr_utility_trace('Person hired after ' || l_start_date);
4014 hr_utility_trace('Leaving '||l_proc_name);
4015 RETURN ('N');
4016 END IF;
4017
4018 IF l_termination_date < l_end_date THEN
4019 hr_utility_trace('Person terminated before ' || l_end_date );
4020 hr_utility_trace('Leaving '||l_proc_name);
4021 RETURN ('N');
4022 END IF;
4023
4024 IF l_start_date IS NOT NULL THEN
4025 OPEN csr_get_form37_status(l_start_date,l_end_date);
4026 FETCH csr_get_form37_status INTO l_flag;
4027 IF csr_get_form37_status%NOTFOUND THEN
4028 CLOSE csr_get_form37_status;
4029 hr_utility_trace('Leaving '||l_proc_name);
4030 RETURN 'Y';
4031 END IF;
4032 CLOSE csr_get_form37_status;
4033 END IF;
4034
4035 hr_utility_trace('l_flag: ' ||l_flag);
4036 hr_utility_trace('Leaving '||l_proc_name);
4037
4038 RETURN (l_flag);
4039
4040 END CHECK_EE_EMPLOYMENT_CRITERIA;
4041
4042
4043
4044 /****************************************************************************
4045 Name : IS_ASG_EXEMPT_FROM_ISR
4046 Description : This function returns Y if an assignment is exempted from ISR
4047 calculation
4048 *****************************************************************************/
4049
4050 FUNCTION IS_ASG_EXEMPT_FROM_ISR
4051 (
4052 P_CTX_ASSIGNMENT_ID NUMBER
4053 ,P_CTX_DATE_EARNED DATE
4054 ) RETURN VARCHAR2 AS
4055
4056 CURSOR get_isr_entry
4057 IS
4058 SELECT 'Y'
4059 FROM pay_element_entries_f pee
4060 ,pay_element_types_f pet
4061 ,pay_element_entry_values_f pev
4062 ,pay_input_values_f piv
4063 WHERE assignment_id=P_CTX_ASSIGNMENT_ID
4064 AND pee.element_type_id=pet.element_type_id
4065 AND pev.element_entry_id=pee.element_entry_id
4066 AND piv.input_value_id = pev.input_value_id
4067 AND pet.element_name = 'Mexico Tax'
4068 AND piv.name ='Exempt ISR Tax'
4069 AND pev.screen_entry_value='Y'
4070 AND P_CTX_DATE_EARNED BETWEEN pee.effective_start_date AND pee.effective_end_date
4071 AND P_CTX_DATE_EARNED BETWEEN pev.effective_start_date AND pev.effective_end_date;
4072
4073
4074
4075 l_flag VARCHAR2(4);
4076 l_value NUMBER;
4077
4078 BEGIN
4079
4080 l_flag :='N';
4081 hr_utility_trace('Entering ..IS_ASG_EXEMPT_FROM_ISR');
4082 OPEN get_isr_entry;
4083 FETCH get_isr_entry INTO l_flag;
4084 IF get_isr_entry%NOTFOUND THEN
4085 CLOSE get_isr_entry;
4086 hr_utility_trace('Leaving ..IS_ASG_EXEMPT_FROM_ISR');
4087 RETURN 'N';
4088 END IF;
4089 CLOSE get_isr_entry;
4090
4091 hr_utility_trace('l_flag: ' ||l_flag);
4092 hr_utility_trace('Leaving ..IS_ASG_EXEMPT_FROM_ISR');
4093
4094 RETURN (l_flag);
4095
4096 END IS_ASG_EXEMPT_FROM_ISR;
4097
4098
4099 /****************************************************************************
4100 Name : IS_PER_EXEMPT_FROM_ADJ
4101 Description : This function returns Y if an assignment is exempted from Tax
4102 Adjustment
4103 *****************************************************************************/
4104
4105 FUNCTION IS_PER_EXEMPT_FROM_ADJ
4106 (
4107 P_CTX_ASSIGNMENT_ID NUMBER
4108 ,P_CTX_DATE_EARNED DATE
4109 ) RETURN VARCHAR2 AS
4110
4111 CURSOR get_person_id
4112 IS
4113 SELECT person_id
4114 FROM per_all_assignments_f
4115 WHERE assignment_id=P_CTX_ASSIGNMENT_ID
4116 AND P_CTX_DATE_EARNED BETWEEN effective_start_date AND effective_end_date;
4117
4118 CURSOR get_exempt_adjustment(p_person_id per_all_people_f.person_id%TYPE)
4119 IS
4120 SELECT pei_information1
4121 FROM per_people_extra_info
4122 WHERE person_id = p_person_id
4123 AND information_type='MX_EMP_TAX_SIGNUP'
4124 AND P_CTX_DATE_EARNED BETWEEN fnd_date.canonical_to_date(pei_information2)
4125 AND fnd_date.canonical_to_date(pei_information3);
4126
4127 l_flag VARCHAR2(4);
4128 l_person_id per_all_people_f.person_id%TYPE;
4129 l_exempt_adj per_people_extra_info.pei_information1%TYPE;
4130
4131 BEGIN
4132
4133 l_flag:='N';
4134 hr_utility_trace('Entering ..IS_PER_EXEMPT_FROM_ADJ');
4135 OPEN get_person_id;
4136 FETCH get_person_id INTO l_person_id;
4137 CLOSE get_person_id;
4138
4139 OPEN get_exempt_adjustment(l_person_id);
4140 FETCH get_exempt_adjustment INTO l_exempt_adj;
4141 IF get_exempt_adjustment%NOTFOUND THEN
4142 CLOSE get_exempt_adjustment;
4143 hr_utility_trace('Leaving ..IS_PER_EXEMPT_FROM_ADJ');
4144 RETURN 'N';
4145 END IF;
4146 CLOSE get_exempt_adjustment;
4147
4148 IF l_exempt_adj ='Y' THEN
4149 l_flag:='Y';
4150 ELSE
4151 l_flag:='N';
4152 END IF;
4153
4154 hr_utility_trace('l_flag: ' ||l_flag);
4155 hr_utility_trace('Leaving ..IS_PER_EXEMPT_FROM_ADJ');
4156
4157 RETURN (l_flag);
4158
4159 END IS_PER_EXEMPT_FROM_ADJ;
4160
4161 /****************************************************************************
4162 Name : GET_MX_ECON_ZONE
4163 Description : This function returns Economy Zone('A', 'B', 'C') for the
4164 given tax_unit_id
4165 *****************************************************************************/
4166
4167 FUNCTION GET_MX_ECON_ZONE
4168 (
4169 P_CTX_TAX_UNIT_ID number,
4170 P_CTX_DATE_EARNED DATE
4171 ) RETURN varchar2 AS
4172
4173 CURSOR get_econ_zone
4174 IS
4175 SELECT hoi.org_information7
4176 FROM hr_organization_units hou,
4177 hr_organization_information hoi
4178 WHERE hou.organization_id = hoi.organization_id
4179 AND hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
4180 AND hou.organization_id = P_CTX_TAX_UNIT_ID
4181 AND P_CTX_DATE_EARNED BETWEEN hou.date_from
4182 AND NVL(hou.date_to, hr_general.end_of_time);
4183
4184 l_econ_zone varchar2(2);
4185
4186 BEGIN
4187
4188
4189 OPEN get_econ_zone;
4190 FETCH get_econ_zone INTO l_econ_zone;
4191 CLOSE get_econ_zone;
4192
4193 RETURN (l_econ_zone);
4194 END GET_MX_ECON_ZONE;
4195
4196 /****************************************************************************
4197 Name : GET_MIN_WAGE
4198 Description : This function returns Minimum Wage for the Economy Zone
4199 *****************************************************************************/
4200
4201 FUNCTION GET_MIN_WAGE
4202 (
4203 P_CTX_DATE_EARNED DATE,
4204 P_TAX_BASIS varchar2,
4205 P_ECON_ZONE varchar2
4206
4207 ) RETURN varchar2 AS
4208
4209 CURSOR get_min_wage
4210 IS
4211 SELECT legislation_info2 FROM PAY_MX_LEGISLATION_INFO_F WHERE
4212 legislation_info1=
4213 DECODE(P_ECON_ZONE,'NONE','GMW','MW'||P_ECON_ZONE) AND
4214 legislation_info_type = 'MX Minimum Wage Information'
4215 AND P_CTX_DATE_EARNED BETWEEN effective_start_date AND effective_end_date;
4216
4217 l_min_wage number;
4218
4219 BEGIN
4220
4221 hr_utility_trace('Economy Zone '||P_ECON_ZONE);
4222 OPEN get_min_wage;
4223 FETCH get_min_wage INTO l_min_wage;
4224 CLOSE get_min_wage;
4225
4226
4227 RETURN (l_min_wage);
4228
4229 END GET_MIN_WAGE;
4230
4231 /****************************************************************************
4232 Name : CONVERT_INTO_MONTHLY_AVG_SAL
4233 Description : This function has
4234 1. Input Parameters as Contexts:
4235 - BUSINESS_GROUP_ID
4236 - TAX_UNIT_ID
4237 - PAYROLL_ID
4238 2. Input Parameters as Parameter:
4239 - SUBJECT_EARNINGS
4240 - DATE_EARNED
4241 *****************************************************************************/
4242
4243 FUNCTION CONVERT_INTO_MONTHLY_AVG_SAL ( p_business_group_id IN NUMBER
4244 ,p_tax_unit_id IN NUMBER
4245 ,p_payroll_id IN NUMBER
4246 ,p_subject_earnings IN NUMBER
4247 ,P_DATE_EARNED IN DATE
4248 ,p_hire_date IN DATE
4249 ,p_first_pay_date IN DATE
4250 ,p_period_days OUT NOCOPY NUMBER)
4251 RETURN NUMBER IS
4252
4253 ln_subject_earnings NUMBER;
4254 ln_days_in_a_month NUMBER;
4255 lv_period_type pay_all_payrolls_f.period_type%TYPE;
4256 ln_row_count NUMBER;
4257 lv_end_date varchar2(24);
4258 lv_start_date varchar2(24);
4259 ln_time_period_id number;
4260
4261 CURSOR csr_get_period_count_in_month IS
4262 select rownum mult_num,
4263 to_char(end_date,'yyyymmdd') period_end_mtd,
4264 to_char(start_date,'yyyymmdd') period_start_mtd,
4265 time_period_id
4266 from PER_TIME_PERIODS ptp
4267 where payroll_id = p_payroll_id
4268 and (to_char(p_date_earned,'yyyymm') = to_char(end_date,'yyyymm')
4269 or
4270 p_date_earned between start_date and end_date
4271 )
4272 AND p_hire_date <= end_date
4273 AND start_date >= NVL(p_first_pay_date,start_date)
4274 order by end_date;
4275
4276 BEGIN
4277
4278 hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_AVG_SAL');
4279 hr_utility_trace('p_subject_earnings: ' ||p_subject_earnings);
4280 hr_utility_trace('p_first_pay_date: ' ||to_char(p_first_pay_date));
4281 hr_utility_trace('p_hire_date: ' ||to_char(p_hire_date));
4282 hr_utility_trace('p_date_earned: ' ||to_char(P_DATE_EARNED));
4283 hr_utility_trace('p_payroll_id: ' ||p_payroll_id);
4284 open csr_get_period_count_in_month;
4285 loop
4286 fetch csr_get_period_count_in_month into ln_row_count,
4287 lv_end_date,
4288 lv_start_date,
4289 ln_time_period_id;
4290 exit when csr_get_period_count_in_month%NOTFOUND;
4291
4292 hr_utility_trace('ln_row_count: ld_end_date' ||
4293 to_char(ln_row_count) ||' -- '||lv_end_date);
4294
4295 if to_char(P_DATE_EARNED,'yyyymmdd') <= lv_end_date and
4296 to_char(P_DATE_EARNED,'yyyymmdd') >= lv_start_date then
4297 ln_subject_earnings := p_subject_earnings * ln_row_count;
4298 p_period_days := ln_row_count;
4299 hr_utility_trace('p_period_days: ' ||ln_row_count);
4300 end if;
4301
4302 end loop;
4303 close csr_get_period_count_in_month;
4304
4305 hr_utility_trace('ln_subject_earnings: ' ||ln_subject_earnings);
4306 hr_utility_trace('Leaving ..CONVERT_INTO_MONTHLY_AVG_SAL');
4307
4308 RETURN ln_subject_earnings;
4309
4310 END CONVERT_INTO_MONTHLY_AVG_SAL;
4311
4312 /*Bug 14094610 - Begin
4313 This function has been written to fetch the prorated user table
4314 low range and high range values along with the table rate
4315 pertaining to each of the ranges. These user table values
4316 will be stored in a temporary pl/sql table t_pro_table_values.
4317 The prorated lower range values in the pl/sql table is updated as
4318 prorated high range values of the immediately previous range + 0.01.
4319 This is done to ensure there are no gaps between the prorated high range
4320 values and the succeeding prorated low range values. Once the table rates
4321 are corrected then table rate pertaining to the range in which p_row_value
4322 is identified and returned back.*/
4323 function get_prorated_rates (p_bus_grp_id in number,
4324 p_table_id in number,
4325 p_column_name in varchar2,
4326 p_eff_date in date,
4327 p_row_value in number,
4328 p_period_days in number,
4329 p_table_name in varchar2)
4330 return varchar2 is
4331
4332 CURSOR csr_get_pro_table_val IS
4333 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
4334 INDEX(R PAY_USER_ROWS_F_FK1)
4335 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
4336 ORDERED */
4337 round((fnd_number.canonical_to_number (R.row_low_range_or_name)/30.4) * p_period_days,2),
4338 round((fnd_number.canonical_to_number (R.row_high_range)/30.4) * p_period_days,2),
4339 CINST.value
4340 from pay_user_tables TAB
4341 , pay_user_columns C
4342 , pay_user_rows_f R
4343 , pay_user_column_instances_f CINST
4344 where TAB.user_table_id = p_table_id
4345 and C.user_table_id = TAB.user_table_id
4346 and nvl (C.business_group_id,
4347 p_bus_grp_id) = p_bus_grp_id
4348 and nvl (C.legislation_code,
4349 'MX') = 'MX'
4350 and upper (C.user_column_name) = upper (p_column_name)
4351 and CINST.user_column_id = C.user_column_id
4352 and R.user_table_id = TAB.user_table_id
4353 and p_eff_date between R.effective_start_date
4354 and R.effective_end_date
4355 and nvl (R.business_group_id,
4356 p_bus_grp_id) = p_bus_grp_id
4357 and nvl (R.legislation_code,
4358 'MX') = 'MX'
4359 and TAB.user_key_units = 'N'
4360 and CINST.user_row_id = R.user_row_id
4361 and p_eff_date between CINST.effective_start_date
4362 and CINST.effective_end_date
4363 and nvl (CINST.business_group_id,
4364 p_bus_grp_id) = p_bus_grp_id
4365 and nvl (CINST.legislation_code,
4366 'MX') = 'MX'
4367 order by R.display_sequence asc;
4368
4369 l_table_value pay_user_column_instances_f.value%type;
4370 i number;
4371 table_count number;
4372
4373 -- Record to store prorated table data for Bug 14094610
4374 TYPE pro_table_rec IS RECORD
4375 (low_range number
4376 ,high_range number
4377 ,row_value pay_user_column_instances_f.value%type);
4378
4379 -- Declare table for the record pro_table_rec for Bug 14094610
4380 TYPE pro_table_val IS TABLE OF pro_table_rec INDEX BY BINARY_INTEGER;
4381
4382 t_pro_table_values pro_table_val; -- Bug 14094610
4383
4384 BEGIN
4385 hr_utility_trace('p_bus_grp_id: ' ||p_bus_grp_id);
4386 hr_utility_trace('p_table_id: ' ||to_char(p_table_id));
4387 hr_utility_trace('p_column_name: ' ||p_column_name);
4388 hr_utility_trace('p_eff_date: ' ||p_eff_date);
4389 hr_utility_trace('p_row_value: ' ||to_char(p_row_value));
4390 hr_utility_trace('p_period_days: ' ||to_char(p_period_days));
4391
4392 t_pro_table_values.delete;
4393 i := 0;
4394
4395 hr_utility.trace('The code is here in the function - 10');
4396
4397 /*The following cursor will fetch the prorated user table
4398 values along with the table rate pertaining to each of
4399 the ranges. This information will be stored in the
4400 pl/sql table t_pro_table_values.*/
4401 OPEN csr_get_pro_table_val;
4402 LOOP
4403 FETCH csr_get_pro_table_val INTO
4404 t_pro_table_values(i).low_range,
4405 t_pro_table_values(i).high_range,
4406 t_pro_table_values(i).row_value;
4407 EXIT WHEN csr_get_pro_table_val%NOTFOUND;
4408 i:=i+1;
4409 END LOOP;
4410 CLOSE csr_get_pro_table_val;
4411
4412 table_count := t_pro_table_values.count; -- The total count of the records in t_pro_table_values is being calculated.
4413
4414 /*If the count of the number of records in the table is 0 then raise
4415 the error saying that the Subsidy cannot be calculated correctly.*/
4416 IF table_count = 0 THEN
4417 hr_utility.trace ('The table returned no rows. Please set the: '||upper(p_table_name) ||' user table correctly.');
4418 hr_utility.set_message(800, 'HR_MX_NO_SUBSIDY');
4419 hr_utility.raise_error;
4420 END IF;
4421 hr_utility.trace('The code is here in the function - 20');
4422
4423 /*The following code will set the prorated low range
4424 values as prorated high range value of previous range
4425 + 0.01. Also it checks in which range does p_row_value
4426 falls in.*/
4427 FOR i in 0..t_pro_table_values.count-1
4428 LOOP
4429 IF t_pro_table_values.exists(i) THEN
4430 IF i = 0 AND (t_pro_table_values(i).low_range = 0)THEN
4431 t_pro_table_values(i).low_range := 0.01;
4432 ELSIF i = table_count-1 THEN
4433 t_pro_table_values(i).high_range := 9999999999.99;
4434 END IF;
4435
4436 hr_utility.trace('The code is here in the function - 30');
4437
4438 IF t_pro_table_values.exists(i+1) THEN
4439 t_pro_table_values(i+1).low_range := t_pro_table_values(i).high_range + 0.01;
4440 END IF;
4441
4442 hr_utility.trace('The code is here in the function - 40');
4443
4444 hr_utility.trace('Low Range is: '||to_char(t_pro_table_values(i).low_range));
4445 hr_utility.trace('High Range is: '||to_char(t_pro_table_values(i).high_range));
4446
4447 IF p_row_value BETWEEN t_pro_table_values(i).low_range AND t_pro_table_values(i).high_range THEN
4448 l_table_value := t_pro_table_values(i).row_value;
4449 hr_utility.trace('The table value is: '||to_char(l_table_value));
4450 EXIT;
4451 END IF;
4452
4453 END IF;
4454 END LOOP;
4455
4456 RETURN l_table_value;
4457
4458 END get_prorated_rates;
4459
4460 /*Bug 14094610 - End*/
4461
4462 function get_table_value (p_bus_group_id in number,
4463 p_table_name in varchar2,
4464 p_col_name in varchar2,
4465 p_row_value in varchar2,
4466 p_effective_date in date default null,
4467 p_period_days in number,
4468 p_period_number in number,
4469 p_period_type in varchar2)
4470 return varchar2 is
4471 l_effective_date date;
4472 l_range_or_match pay_user_tables.range_or_match%type;
4473 l_table_id pay_user_tables.user_table_id%type;
4474 l_value pay_user_column_instances_f.value%type;
4475 ln_period_days number;
4476 begin
4477 hr_utility_trace('p_bus_group_id: ' ||p_bus_group_id);
4478 hr_utility_trace('p_table_name: ' ||p_table_name);
4479 hr_utility_trace('p_col_name: ' ||p_col_name);
4480 hr_utility_trace('p_row_value: ' ||p_row_value);
4481 hr_utility_trace('p_effective_date: ' ||p_effective_date);
4482 hr_utility_trace('p_period_days: ' ||p_period_days);
4483 hr_utility_trace('p_period_number: ' ||p_period_number);
4484 hr_utility_trace('p_period_type: ' ||p_period_type);
4485 --
4486 -- Use either the supplied date, or the date from fnd_sessions
4487 --
4488 if (p_effective_date is not null) then
4489 l_effective_date := p_effective_date;
4490 end if;
4491 --
4492 -- get the type of query to be performed, either range or match
4493 --
4494 select range_or_match, user_table_id
4495 into l_range_or_match, l_table_id
4496 from pay_user_tables
4497 where upper(user_table_name) = upper(p_table_name)
4498 and nvl (business_group_id,
4499 p_bus_group_id) = p_bus_group_id
4500 and nvl(legislation_code, 'MX') = 'MX';
4501 --
4502 hr_utility_trace('l_range_or_match: ' ||l_range_or_match);
4503 hr_utility_trace('l_table_id: ' ||l_table_id);
4504 --
4505 if p_period_type in ('Calendar Month') or
4506 (p_period_type in ('Semi-Month') and p_period_number = 2) or
4507 (p_period_type = 'Ten Days' AND p_period_number = 3) then
4508 ln_period_days := 30.4;
4509 else
4510 ln_period_days := least(p_period_days * p_period_number,30.4);
4511 end if; --
4512 hr_utility_trace('ln_period_days: ' ||ln_period_days);
4513
4514 /*Bug 14094610 - Begin
4515 Calling the following function to correctly identify which table range
4516 does the value p_row_value falls in, and to fetch the corresponding table
4517 rate value pertaining to that range. Currently this code has been added only
4518 for the user table 'ISR Subsidy for Empl_Month', as the need arises this code
4519 can be extended to other user tables as well.*/
4520
4521 if (upper(p_table_name)= upper('ISR Subsidy for Empl_Month') and l_range_or_match <> 'M') then
4522
4523 l_value := get_prorated_rates(p_bus_group_id,
4524 l_table_id,
4525 p_col_name,
4526 p_effective_date,
4527 fnd_number.canonical_to_number(p_row_value),
4528 ln_period_days,
4529 p_table_name);
4530
4531 hr_utility_trace('l_table_value: ' ||l_value);
4532 return l_value;
4533
4534 /*Bug 14094610 - End*/
4535
4536 elsif (l_range_or_match <> 'M') then -- matched
4537 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
4538 INDEX(R PAY_USER_ROWS_F_FK1)
4539 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
4540 ORDERED */
4541 CINST.value
4542 into l_value
4543 from pay_user_tables TAB
4544 , pay_user_columns C
4545 , pay_user_rows_f R
4546 , pay_user_column_instances_f CINST
4547 where TAB.user_table_id = l_table_id
4548 and C.user_table_id = TAB.user_table_id
4549 and nvl (C.business_group_id,
4550 p_bus_group_id) = p_bus_group_id
4551 and nvl (C.legislation_code,
4552 'MX') = 'MX'
4553 and upper (C.user_column_name) = upper (p_col_name)
4554 and CINST.user_column_id = C.user_column_id
4555 and R.user_table_id = TAB.user_table_id
4556 and l_effective_date between R.effective_start_date
4557 and R.effective_end_date
4558 and nvl (R.business_group_id,
4559 p_bus_group_id) = p_bus_group_id
4560 and nvl (R.legislation_code,
4561 'MX') = 'MX'
4562 and fnd_number.canonical_to_number (p_row_value)
4563 between (fnd_number.canonical_to_number (R.row_low_range_or_name)/30.4) * ln_period_days
4564 and (fnd_number.canonical_to_number (R.row_high_range)/30.4) * ln_period_days
4565 and TAB.user_key_units = 'N'
4566 and CINST.user_row_id = R.user_row_id
4567 and l_effective_date between CINST.effective_start_date
4568 and CINST.effective_end_date
4569 and nvl (CINST.business_group_id,
4570 p_bus_group_id) = p_bus_group_id
4571 and nvl (CINST.legislation_code,
4572 'MX') = 'MX';
4573 --
4574 hr_utility_trace('l_value: ' ||l_value);
4575 return l_value;
4576
4577 end if;
4578
4579 end get_table_value;
4580
4581 BEGIN
4582 --hr_utility.trace_on (null, 'MX_IDC');
4583 g_proc_name := 'PAY_MX_TAX_FUNCTIONS.';
4584 g_debug := hr_utility.debug_enabled;
4585
4586 END PAY_MX_TAX_FUNCTIONS;