[Home] [Help]
PACKAGE: APPS.PAY_MX_TAX_FUNCTIONS
Source
1 PACKAGE PAY_MX_TAX_FUNCTIONS AUTHID CURRENT_USER as
2 /* $Header: pymxtxfn.pkh 120.17.12020000.2 2012/07/05 13:17:43 amnaraya 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 Modified definition of function
31 get_isr_partial_subj_earnings.
32 09-NOV-2004 sdahiya 115.2 Added GET_SS_QUOTA_INFO
33 function
34 21-FEB-2005 sdahiya 115.3 Renamed
35 get_isr_partial_subj_earnings
36 to get_partial_subj_earnings.
37 10-MAR-2005 sdahiya 115.4 Renamed GET_SS_QUOTA_INFO to
38 GET_MX_TAX_INFO.
39 12-MAR-2005 ardsouza 115.5 Added GET_MX_EE_HEAD_COUNT and
40 GET_MX_STATE_TAX_RULES
41 functions.
42 18-APR-2005 sdahiya 115.6 Modified
43 GET_PARTIAL_SUBJ_EARNINGS to
44 accept P_CTX_ELEMENT_TYPE_ID
45 as a parameter.
46 27-JUN-2005 ardsouza 115.7 4387751 Added 2 overloaded versions of
47 GET_PARTIAL_SUBJ_EARNINGS -
48 one that accepts YTD earnings
49 and one both. Added
50 GET_SUBJ_EARNINGS_FOR_PERIOD,
51 which accepts PTD earnings.
52 06-Dec-2005 vpandya 115.8 Added following functions:
53 - CALCULATE_ISR_TAX
54 10-Jan-2006 vpandya 115.9 Added following functions:
55 - CONVERT_INTO_MONTHLY_SALARY
56 29-Mar-2006 ardsouza 115.10 Added g_temp_object_actions global
57 variable.
58 14-Jul-2006 sukukuma 115.11 Added following functions:
59 - CHECK_EE_SAL_CRITERIA
60 - CHECK_EE_EMPLOYMENT_CRITERIA
61 - IS_ASG_EXEMPT_FROM_ISR
62 - IS_PER_EXEMPT_FROM_ADJ
63 14-Nov-2006 sdahiya 115.12 Added overloaded version of
64 calculate_isr_tax to support subsidy
65 calculation for Article 141.
66 15-Oct-2007 srikared 115.13 6437992 Added New functions GET_MIN_WAGE,
67 GET_MX_ECON_ZONE
68 12-Dec-2007 nragavar 115.15 6487007 Added new function
69 CONVERT_MONTHLY_TO_PERIOD and
70 CONVERT_INTO_MONTHLY_AVG_SAL
71 30-Jan-2008 nragavar 115.18 6782264 Modified get_table_value function
72
73 21-Feb-2008 sivanara 115.19 6821377 Adde new parameter p_hire_date
74 to function CONVERT_INTO_MONTHLY_AVG_SAL
75 04-Jun-2008 sivanara 115.20 6933775 Adde new parameter p_first_paid_date
76 to function CONVERT_INTO_MONTHLY_AVG_SAL
77 21-Jul-2010 vvijayku 115.22 9753792 Added the new global variables g_ptu_start_date
78 and g_ptu_end_date
79 28-May-2012 vvijayku 115.23 14094610 Added a new function get_prorated_rates.
80 *****************************************************************************/
81
82 --****************************************************************************
83 -- Name : GET_PARTIAL_SUBJ_EARNINGS
84 -- Description : This function calls another overloaded function, which returns
85 -- the portion of earnings that are partially subject to State
86 -- Tax and both fully and partially subject to ISR.
87 -- Both the YTD and PTD Earnings are defaulted to the
88 -- secondary classification earnings for the current run, which is
89 -- maintained in the PL-SQL table.
90 --****************************************************************************
91 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
92 (
93 P_CTX_EFFECTIVE_DATE date,
94 P_CTX_ASSIGNMENT_ACTION_ID number,
95 P_CTX_BUSINESS_GROUP_ID number,
96 P_CTX_JURISDICTION_CODE varchar2,
97 P_CTX_ELEMENT_TYPE_ID number,
98 P_TAX_TYPE varchar2,
99 P_EARNINGS_AMT number,
100 P_GROSS_EARNINGS number,
101 P_DAILY_SALARY number,
102 P_CLASSIFICATION_NAME varchar2
103 )RETURN NUMBER;
104
105
106 --****************************************************************************
107 -- Name : GET_PARTIAL_SUBJ_EARNINGS
108 -- Description : This function calls another overloaded function, which returns
109 -- the portion of earnings that are partially subject to State
110 -- Tax and both fully and partially subject to ISR.
111 -- The PTD Earnings are defaulted to the secondary classification
112 -- earnings for the current run, which is maintained in the PL-SQL
113 -- table.
114 --****************************************************************************
115 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
116 (
117 P_CTX_EFFECTIVE_DATE date,
118 P_CTX_ASSIGNMENT_ACTION_ID number,
119 P_CTX_BUSINESS_GROUP_ID number,
120 P_CTX_JURISDICTION_CODE varchar2,
121 P_CTX_ELEMENT_TYPE_ID number,
122 P_TAX_TYPE varchar2,
123 P_EARNINGS_AMT number,
124 P_YTD_EARNINGS_AMT number,
125 P_GROSS_EARNINGS number,
126 P_YTD_GROSS_EARNINGS number,
127 P_DAILY_SALARY number,
128 P_CLASSIFICATION_NAME varchar2
129 )RETURN NUMBER;
130
131
132 --******************************************************************************
133 -- Name : GET_SUBJ_EARNINGS_FOR_PERIOD
134 -- Description : This function calls another overloaded function, which returns
135 -- the portion of earnings that are partially subject to State
136 -- Tax and both fully and partially subject to ISR.
137 -- The YTD Earnings are defaulted to the secondary classification
138 -- earnings for the current run, which is maintained in the PL-SQL
139 -- table.
140 --******************************************************************************
141 FUNCTION GET_SUBJ_EARNINGS_FOR_PERIOD
142 (
143 P_CTX_EFFECTIVE_DATE date,
144 P_CTX_ASSIGNMENT_ACTION_ID number,
145 P_CTX_BUSINESS_GROUP_ID number,
146 P_CTX_JURISDICTION_CODE varchar2,
147 P_CTX_ELEMENT_TYPE_ID number,
148 P_TAX_TYPE varchar2,
149 P_EARNINGS_AMT number,
150 P_PTD_EARNINGS_AMT number,
151 P_GROSS_EARNINGS number,
152 P_YTD_GROSS_EARNINGS number,
153 P_DAILY_SALARY number,
154 P_CLASSIFICATION_NAME varchar2
155 )RETURN NUMBER;
156
157
158 /****************************************************************************
159 Name : GET_PARTIAL_SUBJ_EARNINGS
160 Description : This function calculates subject earnings for classifications
161 that are fully and partially subject to ISR and state taxes.
162 *****************************************************************************/
163 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
164 (
165 P_CTX_EFFECTIVE_DATE date,
166 P_CTX_ASSIGNMENT_ACTION_ID number,
167 P_CTX_BUSINESS_GROUP_ID number,
168 P_CTX_JURISDICTION_CODE varchar2,
169 P_CTX_ELEMENT_TYPE_ID number,
170 P_TAX_TYPE varchar2,
171 P_EARNINGS_AMT number,
172 P_YTD_EARNINGS_AMT number,
173 P_PTD_EARNINGS_AMT number,
174 P_GROSS_EARNINGS number,
175 P_YTD_GROSS_EARNINGS number,
176 P_DAILY_SALARY number,
177 P_CLASSIFICATION_NAME varchar2
178 )RETURN NUMBER;
179
180
181 /****************************************************************************
182 Name : GET_PREVIOUS_PERIOD_BAL
183 Description : This function returns balance values for pay period
184 immediately previous to the period in which passed
185 assignment_action_id lies.
186 *****************************************************************************/
187
188 FUNCTION GET_PREVIOUS_PERIOD_BAL
189 (
190 P_CTX_ASSIGNMENT_ID number,
191 P_CTX_ASSIGNMENT_ACTION_ID number,
192 P_MODE varchar2
193 ) RETURN NUMBER;
194
195
196 /****************************************************************************
197 Name : GET_MX_TAX_INFO
198 Description : This function returns various parameters required for social
199 security quota and state tax calculation.
200 *****************************************************************************/
201
202 FUNCTION GET_MX_TAX_INFO
203 (
204 P_CTX_BUSINESS_GROUP_ID number,
205 P_CTX_TAX_UNIT_ID number,
206 P_CTX_EFFECTIVE_DATE date,
207 P_CTX_JURISDICTION_CODE varchar2,
208 P_LEGISLATION_INFO_TYPE varchar2,
209 P_LEGISLATION_INFO1 in out nocopy varchar2,
210 P_LEGISLATION_INFO2 out nocopy varchar2,
211 P_LEGISLATION_INFO3 out nocopy varchar2,
212 P_LEGISLATION_INFO4 out nocopy varchar2,
213 P_LEGISLATION_INFO5 out nocopy varchar2,
214 P_LEGISLATION_INFO6 out nocopy varchar2
215 ) RETURN NUMBER;
216
217
218 /****************************************************************************
219 Name : GET_MX_EE_HEAD_COUNT
220 Description : This function returns the Employee Headcount used for
221 Employer State Tax rate computation.
222 *****************************************************************************/
223 FUNCTION GET_MX_EE_HEAD_COUNT
224 (
225 P_CTX_BUSINESS_GROUP_ID NUMBER,
226 P_CTX_TAX_UNIT_ID NUMBER,
227 P_CTX_EFFECTIVE_DATE DATE,
228 P_CTX_JURISDICTION_CODE VARCHAR2
229 ) RETURN NUMBER;
230
231 /****************************************************************************
232 Name : GET_MX_STATE_TAX_RULES
233 Description : This function returns the data stored at legal Employer level
234 under "State Tax Rules" Org Info type.
235 *****************************************************************************/
236
237 FUNCTION GET_MX_STATE_TAX_RULES
238 (
239 P_CTX_BUSINESS_GROUP_ID NUMBER,
240 P_CTX_TAX_UNIT_ID NUMBER,
241 P_CTX_EFFECTIVE_DATE DATE,
242 P_CTX_JURISDICTION_CODE VARCHAR2
243 ) RETURN VARCHAR2;
244
245 /****************************************************************************
246 Name : CALCULATE_ISR_TAX
247 Description : This function has
248 1. Input Parameters as Contexts:
249 - BUSINESS_GROUP_ID
250 - ASSIGNMENT_ID
251 - TAX_UNIT_ID
252 - DATE_EARNED
253 2. Input Parameters as Parameter:
254 - SUBJECT_AMOUNT
255 - ISR_RATES_TABLE
256 - SUBSIDY_TABLE
257 - CREDIT_TO_SALARY_TABLE
258 3. Returns following data for give subject amount:
259 - ISR_WITHHELD (Return Value)
260 - ISR_CALCULATED (Output Parameter)
261 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
262 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
263 - ISR_CREDIT_TO_SALARY (Output Parameter)
264 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
265 *****************************************************************************/
266
267 FUNCTION CALCULATE_ISR_TAX (
271 ,p_assignment_id NUMBER
268 P_PAYROLL_ACTION_ID NUMBER
269 ,P_ASSIGNMENT_ACTION_ID NUMBER
270 ,p_business_group_id NUMBER
272 ,p_tax_unit_id NUMBER
273 ,p_date_earned DATE
274 ,p_subject_amount NUMBER
275 ,p_isr_rates_table VARCHAR2
276 ,p_subsidy_table VARCHAR2
277 ,p_credit_to_salary_table VARCHAR2
278 ,p_isr_calculated OUT NOCOPY NUMBER
279 ,p_isr_creditable_subsidy OUT NOCOPY NUMBER
280 ,p_isr_non_creditable_subsidy OUT NOCOPY NUMBER
281 ,p_credit_to_salary OUT NOCOPY NUMBER
282 ,p_credit_to_salary_paid OUT NOCOPY NUMBER)
283 RETURN NUMBER;
284
285 /****************************************************************************
286 Name : CALCULATE_ISR_TAX
287 Description : This function has
288 1. Input Parameters as Contexts:
289 - PAYROLL_ACTION_ID
290 - ASSIGNMENT_ACTION_ID
291 - BUSINESS_GROUP_ID
292 - ASSIGNMENT_ID
293 - TAX_UNIT_ID
294 - DATE_EARNED
295 2. Input Parameters as Parameter:
296 - SUBJECT_AMOUNT
297 - ISR_RATES_TABLE
298 - SUBSIDY_TABLE
299 - CREDIT_TO_SALARY_TABLE
300 3. Returns following data for give subject amount:
301 - ISR_WITHHELD (Return Value)
302 - ISR_CALCULATED (Output Parameter)
303 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
304 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
305 - ISR_CREDIT_TO_SALARY (Output Parameter)
306 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
307 *****************************************************************************/
308
309 FUNCTION CALCULATE_ISR_TAX
310 (
311 P_PAYROLL_ACTION_ID NUMBER,
312 P_ASSIGNMENT_ACTION_ID NUMBER,
313 P_BUSINESS_GROUP_ID NUMBER,
314 P_ASSIGNMENT_ID NUMBER,
315 P_TAX_UNIT_ID NUMBER,
316 P_DATE_EARNED DATE,
317 P_CALC_MODE VARCHAR2,
318 P_SUBJECT_AMOUNT NUMBER,
319 P_ISR_RATES_TABLE VARCHAR2,
320 P_SUBSIDY_TABLE VARCHAR2,
321 P_CREDIT_TO_SALARY_TABLE VARCHAR2,
322 P_ISR_CALCULATED OUT NOCOPY NUMBER,
323 P_ISR_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
324 P_ISR_NON_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
325 P_CREDIT_TO_SALARY OUT NOCOPY NUMBER,
326 P_CREDIT_TO_SALARY_PAID OUT NOCOPY NUMBER
327 )
328 RETURN NUMBER;
329
330 /****************************************************************************
331 Name : CALCULATE_ISR_TAX
332 Description : This function has
333 1. Input Parameters as Contexts:
334 - PAYROLL_ACTION_ID
335 - ASSIGNMENT_ACTION_ID
336 - BUSINESS_GROUP_ID
337 - ASSIGNMENT_ID
338 - TAX_UNIT_ID
339 - DATE_EARNED
340 2. Input Parameters as Parameter:
341 - RUN TYPE
342 - CALCULATION MODE
343 - SUBJECT_AMOUNT
344 - ISR_RATES_TABLE
345 - SUBSIDY_TABLE
346 - CREDIT_TO_SALARY_TABLE
347 3. Returns following data for give subject amount:
348 - ISR_WITHHELD (Return Value)
349 - ISR_CALCULATED (Output Parameter)
350 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
351 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
352 - ISR_CREDIT_TO_SALARY (Output Parameter)
353 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
354 *****************************************************************************/
355
356 FUNCTION CALCULATE_ISR_TAX
357 (
358 P_PAYROLL_ACTION_ID NUMBER,
359 P_ASSIGNMENT_ACTION_ID NUMBER,
360 P_BUSINESS_GROUP_ID NUMBER,
361 P_ASSIGNMENT_ID NUMBER,
362 P_TAX_UNIT_ID NUMBER,
363 P_DATE_EARNED DATE,
364 P_RUN_TYPE VARCHAR2,
365 P_CALC_MODE VARCHAR2,
366 P_SUBJECT_AMOUNT NUMBER,
367 P_ISR_RATES_TABLE VARCHAR2,
368 P_SUBSIDY_TABLE VARCHAR2,
369 P_CREDIT_TO_SALARY_TABLE VARCHAR2,
370 P_ISR_CALCULATED OUT NOCOPY NUMBER,
371 P_ISR_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
372 P_ISR_NON_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
373 P_CREDIT_TO_SALARY OUT NOCOPY NUMBER,
374 P_CREDIT_TO_SALARY_PAID OUT NOCOPY NUMBER
375 )
376 RETURN NUMBER;
377
378 /****************************************************************************
379 Name : CONVERT_INTO_MONTHLY_SALARY
380 Description : This function has
381 1. Input Parameters as Contexts:
382 - BUSINESS_GROUP_ID
383 - TAX_UNIT_ID
384 - PAYROLL_ID
385 2. Input Parameters as Parameter:
386 - PERIODIC_EARNINGS
387 *****************************************************************************/
388
392 ,p_periodic_earnings NUMBER)
389 FUNCTION CONVERT_INTO_MONTHLY_SALARY ( p_business_group_id NUMBER
390 ,p_tax_unit_id NUMBER
391 ,p_payroll_id NUMBER
393 RETURN NUMBER;
394
395
396 /****************************************************************************
397 Name : CHECK_EE_SAL_CRITERIA
398 Description : This function returns 'Y' if employee's annual gross earning
399 is less than 300,000 MXN.
400 *****************************************************************************/
401
402 FUNCTION CHECK_EE_SAL_CRITERIA(P_CTX_ASSIGNMENT_ID NUMBER
403 ,P_CTX_DATE_EARNED DATE)
404 RETURN VARCHAR2;
405
406
407 /****************************************************************************
408 Name : CHECK_EE_EMPLOYMENT_CRITERIA
409 Description : This Function return 'Y' if employee is working continously
410 between the given start date and end date
411 *****************************************************************************/
412
413 FUNCTION CHECK_EE_EMPLOYMENT_CRITERIA(P_CTX_ASSIGNMENT_ID NUMBER
414 ,P_CTX_DATE_EARNED DATE)
415 RETURN VARCHAR2;
416
417
418 /****************************************************************************
419 Name : IS_ASG_EXEMPT_FROM_ISR
420 Description : This function returns Y if an assignment is exempted from ISR
421 calculation
422 *****************************************************************************/
423
424 FUNCTION IS_ASG_EXEMPT_FROM_ISR(P_CTX_ASSIGNMENT_ID NUMBER
425 ,P_CTX_DATE_EARNED DATE)
426 RETURN VARCHAR2;
427
428
429 /****************************************************************************
430 Name : IS_PER_EXEMPT_FROM_ADJ
431 Description : This function returns Y if an assignment is exempted from Tax
432 Adjustment
433 *****************************************************************************/
434
435 FUNCTION IS_PER_EXEMPT_FROM_ADJ(P_CTX_ASSIGNMENT_ID NUMBER
436 ,P_CTX_DATE_EARNED DATE)
437 RETURN VARCHAR2;
438
439
440 /****************************************************************************
441 Name : GET_MX_ECON_ZONE
442 Description : This function returns Economy Zone('A', 'B', 'C') for the
443 given tax_unit_id
444 *****************************************************************************/
445
446 FUNCTION GET_MX_ECON_ZONE
447 (
448 P_CTX_TAX_UNIT_ID number,
449 P_CTX_DATE_EARNED DATE
450 ) RETURN varchar2;
451
452 /****************************************************************************
453 Name : GET_MIN_WAGE
454 Description : This function returns Minimum Wage for the Economy Zone
455 *****************************************************************************/
456
457 FUNCTION GET_MIN_WAGE
458 (
459 P_CTX_DATE_EARNED DATE,
460 P_TAX_BASIS varchar2,
461 P_ECON_ZONE varchar2
462
463 ) RETURN varchar2;
464
465
466 g_temp_object_actions BOOLEAN;
467 g_ptu_start_date DATE; -- Bug:9753792
468 g_ptu_end_date DATE; -- Bug:9753792
469
470 /****************************************************************************
471 Name : CONVERT_MONTHLY_TO_PERIOD
472 Description : This function returns monthly isr table value converted to
473 pay period actual number of days value.
474 *****************************************************************************/
475
476
477 /*FUNCTION CONVERT_MONTHLY_TO_PERIOD ( p_business_group_id NUMBER
478 ,p_tax_unit_id NUMBER
479 ,p_payroll_id NUMBER
480 ,p_periodic_value NUMBER
481 ,p_date_earned DATE)
482 RETURN NUMBER; */
483
484 /****************************************************************************
485 Name : CONVERT_INTO_MONTHLY_AVG_SAL
486 Description : This function returns period isr subject value converted to
487 monthly subject.
488 *****************************************************************************/
489
490 FUNCTION CONVERT_INTO_MONTHLY_AVG_SAL ( p_business_group_id NUMBER
491 ,p_tax_unit_id NUMBER
492 ,p_payroll_id NUMBER
493 ,p_subject_earnings NUMBER
494 ,P_DATE_EARNED DATE
495 ,p_hire_date DATE
496 ,p_first_pay_date DATE
497 ,p_period_days out NOCOPY NUMBER)
498 RETURN NUMBER;
499
500 function get_prorated_rates (p_bus_grp_id in number,
501 p_table_id in number,
502 p_column_name in varchar2,
503 p_eff_date in date,
504 p_row_value in number,
505 p_period_days in number,
506 p_table_name in varchar2)
507 return varchar2;
508
509 function get_table_value (p_bus_group_id number,
510 p_table_name varchar2,
511 p_col_name varchar2,
512 p_row_value varchar2,
513 p_effective_date date default null,
514 p_period_days number,
515 p_period_number number,
516 p_period_type varchar2)
517 return varchar2;
518
519 END;
520