1 PACKAGE PAY_MX_TAX_FUNCTIONS as
2 /* $Header: pymxtxfn.pkh 120.12.12010000.3 2008/08/06 07:54:43 ubhat 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.35 6821377 Adde new parameter p_hire_date
74 to function CONVERT_INTO_MONTHLY_AVG_SAL
75 04-Jun-2008 sivanara 115.36 6933775 Adde new parameter p_first_paid_date
76 to function CONVERT_INTO_MONTHLY_AVG_SAL
77 *****************************************************************************/
78
79 --****************************************************************************
80 -- Name : GET_PARTIAL_SUBJ_EARNINGS
81 -- Description : This function calls another overloaded function, which returns
82 -- the portion of earnings that are partially subject to State
83 -- Tax and both fully and partially subject to ISR.
84 -- Both the YTD and PTD Earnings are defaulted to the
85 -- secondary classification earnings for the current run, which is
86 -- maintained in the PL-SQL table.
87 --****************************************************************************
88 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
89 (
90 P_CTX_EFFECTIVE_DATE date,
91 P_CTX_ASSIGNMENT_ACTION_ID number,
92 P_CTX_BUSINESS_GROUP_ID number,
93 P_CTX_JURISDICTION_CODE varchar2,
94 P_CTX_ELEMENT_TYPE_ID number,
95 P_TAX_TYPE varchar2,
96 P_EARNINGS_AMT number,
97 P_GROSS_EARNINGS number,
98 P_DAILY_SALARY number,
99 P_CLASSIFICATION_NAME varchar2
100 )RETURN NUMBER;
101
102
103 --****************************************************************************
104 -- Name : GET_PARTIAL_SUBJ_EARNINGS
105 -- Description : This function calls another overloaded function, which returns
106 -- the portion of earnings that are partially subject to State
107 -- Tax and both fully and partially subject to ISR.
108 -- The PTD Earnings are defaulted to the secondary classification
109 -- earnings for the current run, which is maintained in the PL-SQL
110 -- table.
111 --****************************************************************************
112 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
113 (
114 P_CTX_EFFECTIVE_DATE date,
115 P_CTX_ASSIGNMENT_ACTION_ID number,
116 P_CTX_BUSINESS_GROUP_ID number,
117 P_CTX_JURISDICTION_CODE varchar2,
118 P_CTX_ELEMENT_TYPE_ID number,
119 P_TAX_TYPE varchar2,
120 P_EARNINGS_AMT number,
121 P_YTD_EARNINGS_AMT number,
122 P_GROSS_EARNINGS number,
123 P_YTD_GROSS_EARNINGS number,
124 P_DAILY_SALARY number,
125 P_CLASSIFICATION_NAME varchar2
126 )RETURN NUMBER;
127
128
129 --******************************************************************************
130 -- Name : GET_SUBJ_EARNINGS_FOR_PERIOD
131 -- Description : This function calls another overloaded function, which returns
132 -- the portion of earnings that are partially subject to State
133 -- Tax and both fully and partially subject to ISR.
134 -- The YTD Earnings are defaulted to the secondary classification
135 -- earnings for the current run, which is maintained in the PL-SQL
136 -- table.
137 --******************************************************************************
138 FUNCTION GET_SUBJ_EARNINGS_FOR_PERIOD
139 (
140 P_CTX_EFFECTIVE_DATE date,
141 P_CTX_ASSIGNMENT_ACTION_ID number,
142 P_CTX_BUSINESS_GROUP_ID number,
143 P_CTX_JURISDICTION_CODE varchar2,
144 P_CTX_ELEMENT_TYPE_ID number,
145 P_TAX_TYPE varchar2,
146 P_EARNINGS_AMT number,
147 P_PTD_EARNINGS_AMT number,
148 P_GROSS_EARNINGS number,
149 P_YTD_GROSS_EARNINGS number,
150 P_DAILY_SALARY number,
151 P_CLASSIFICATION_NAME varchar2
152 )RETURN NUMBER;
153
154
155 /****************************************************************************
156 Name : GET_PARTIAL_SUBJ_EARNINGS
157 Description : This function calculates subject earnings for classifications
158 that are fully and partially subject to ISR and state taxes.
159 *****************************************************************************/
160 FUNCTION GET_PARTIAL_SUBJ_EARNINGS
161 (
162 P_CTX_EFFECTIVE_DATE date,
163 P_CTX_ASSIGNMENT_ACTION_ID number,
164 P_CTX_BUSINESS_GROUP_ID number,
165 P_CTX_JURISDICTION_CODE varchar2,
166 P_CTX_ELEMENT_TYPE_ID number,
167 P_TAX_TYPE varchar2,
168 P_EARNINGS_AMT number,
169 P_YTD_EARNINGS_AMT number,
170 P_PTD_EARNINGS_AMT number,
171 P_GROSS_EARNINGS number,
172 P_YTD_GROSS_EARNINGS number,
173 P_DAILY_SALARY number,
174 P_CLASSIFICATION_NAME varchar2
175 )RETURN NUMBER;
176
177
178 /****************************************************************************
179 Name : GET_PREVIOUS_PERIOD_BAL
180 Description : This function returns balance values for pay period
181 immediately previous to the period in which passed
182 assignment_action_id lies.
183 *****************************************************************************/
184
185 FUNCTION GET_PREVIOUS_PERIOD_BAL
186 (
187 P_CTX_ASSIGNMENT_ID number,
188 P_CTX_ASSIGNMENT_ACTION_ID number,
189 P_MODE varchar2
190 ) RETURN NUMBER;
191
192
193 /****************************************************************************
194 Name : GET_MX_TAX_INFO
195 Description : This function returns various parameters required for social
196 security quota and state tax calculation.
197 *****************************************************************************/
198
199 FUNCTION GET_MX_TAX_INFO
200 (
201 P_CTX_BUSINESS_GROUP_ID number,
202 P_CTX_TAX_UNIT_ID number,
203 P_CTX_EFFECTIVE_DATE date,
204 P_CTX_JURISDICTION_CODE varchar2,
205 P_LEGISLATION_INFO_TYPE varchar2,
206 P_LEGISLATION_INFO1 in out nocopy varchar2,
207 P_LEGISLATION_INFO2 out nocopy varchar2,
208 P_LEGISLATION_INFO3 out nocopy varchar2,
209 P_LEGISLATION_INFO4 out nocopy varchar2,
210 P_LEGISLATION_INFO5 out nocopy varchar2,
211 P_LEGISLATION_INFO6 out nocopy varchar2
212 ) RETURN NUMBER;
213
214
215 /****************************************************************************
216 Name : GET_MX_EE_HEAD_COUNT
217 Description : This function returns the Employee Headcount used for
218 Employer State Tax rate computation.
219 *****************************************************************************/
220 FUNCTION GET_MX_EE_HEAD_COUNT
221 (
222 P_CTX_BUSINESS_GROUP_ID NUMBER,
223 P_CTX_TAX_UNIT_ID NUMBER,
224 P_CTX_EFFECTIVE_DATE DATE,
225 P_CTX_JURISDICTION_CODE VARCHAR2
226 ) RETURN NUMBER;
227
228 /****************************************************************************
229 Name : GET_MX_STATE_TAX_RULES
230 Description : This function returns the data stored at legal Employer level
231 under "State Tax Rules" Org Info type.
232 *****************************************************************************/
233
234 FUNCTION GET_MX_STATE_TAX_RULES
235 (
236 P_CTX_BUSINESS_GROUP_ID NUMBER,
237 P_CTX_TAX_UNIT_ID NUMBER,
238 P_CTX_EFFECTIVE_DATE DATE,
239 P_CTX_JURISDICTION_CODE VARCHAR2
240 ) RETURN VARCHAR2;
241
242 /****************************************************************************
243 Name : CALCULATE_ISR_TAX
244 Description : This function has
245 1. Input Parameters as Contexts:
246 - BUSINESS_GROUP_ID
247 - ASSIGNMENT_ID
248 - TAX_UNIT_ID
249 - DATE_EARNED
250 2. Input Parameters as Parameter:
251 - SUBJECT_AMOUNT
252 - ISR_RATES_TABLE
253 - SUBSIDY_TABLE
254 - CREDIT_TO_SALARY_TABLE
255 3. Returns following data for give subject amount:
256 - ISR_WITHHELD (Return Value)
257 - ISR_CALCULATED (Output Parameter)
258 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
259 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
260 - ISR_CREDIT_TO_SALARY (Output Parameter)
261 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
262 *****************************************************************************/
263
264 FUNCTION CALCULATE_ISR_TAX (
265 P_PAYROLL_ACTION_ID NUMBER
266 ,P_ASSIGNMENT_ACTION_ID NUMBER
267 ,p_business_group_id NUMBER
268 ,p_assignment_id NUMBER
269 ,p_tax_unit_id NUMBER
270 ,p_date_earned DATE
271 ,p_subject_amount NUMBER
272 ,p_isr_rates_table VARCHAR2
273 ,p_subsidy_table VARCHAR2
274 ,p_credit_to_salary_table VARCHAR2
275 ,p_isr_calculated OUT NOCOPY NUMBER
276 ,p_isr_creditable_subsidy OUT NOCOPY NUMBER
277 ,p_isr_non_creditable_subsidy OUT NOCOPY NUMBER
278 ,p_credit_to_salary OUT NOCOPY NUMBER
279 ,p_credit_to_salary_paid OUT NOCOPY NUMBER)
280 RETURN NUMBER;
281
282 /****************************************************************************
283 Name : CALCULATE_ISR_TAX
284 Description : This function has
285 1. Input Parameters as Contexts:
286 - PAYROLL_ACTION_ID
287 - ASSIGNMENT_ACTION_ID
288 - BUSINESS_GROUP_ID
289 - ASSIGNMENT_ID
290 - TAX_UNIT_ID
291 - DATE_EARNED
292 2. Input Parameters as Parameter:
293 - SUBJECT_AMOUNT
294 - ISR_RATES_TABLE
295 - SUBSIDY_TABLE
296 - CREDIT_TO_SALARY_TABLE
297 3. Returns following data for give subject amount:
298 - ISR_WITHHELD (Return Value)
299 - ISR_CALCULATED (Output Parameter)
300 - ISR_CREDITABLE_SUBSIDY (Output Parameter)
301 - ISR_NON_CREDITABLE_SUBSIDY (Output Parameter)
302 - ISR_CREDIT_TO_SALARY (Output Parameter)
303 - ISR_CREDIT_TO_SALARY_PAID (Output Parameter)
304 *****************************************************************************/
305
306 FUNCTION CALCULATE_ISR_TAX
307 (
308 P_PAYROLL_ACTION_ID NUMBER,
309 P_ASSIGNMENT_ACTION_ID NUMBER,
310 P_BUSINESS_GROUP_ID NUMBER,
311 P_ASSIGNMENT_ID NUMBER,
315 P_SUBJECT_AMOUNT NUMBER,
312 P_TAX_UNIT_ID NUMBER,
313 P_DATE_EARNED DATE,
314 P_CALC_MODE VARCHAR2,
316 P_ISR_RATES_TABLE VARCHAR2,
317 P_SUBSIDY_TABLE VARCHAR2,
318 P_CREDIT_TO_SALARY_TABLE VARCHAR2,
319 P_ISR_CALCULATED OUT NOCOPY NUMBER,
320 P_ISR_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
321 P_ISR_NON_CREDITABLE_SUBSIDY OUT NOCOPY NUMBER,
322 P_CREDIT_TO_SALARY OUT NOCOPY NUMBER,
323 P_CREDIT_TO_SALARY_PAID OUT NOCOPY NUMBER
324 )
325 RETURN NUMBER;
326
327 /****************************************************************************
328 Name : CONVERT_INTO_MONTHLY_SALARY
329 Description : This function has
330 1. Input Parameters as Contexts:
331 - BUSINESS_GROUP_ID
332 - TAX_UNIT_ID
333 - PAYROLL_ID
334 2. Input Parameters as Parameter:
335 - PERIODIC_EARNINGS
336 *****************************************************************************/
337
338 FUNCTION CONVERT_INTO_MONTHLY_SALARY ( p_business_group_id NUMBER
339 ,p_tax_unit_id NUMBER
340 ,p_payroll_id NUMBER
341 ,p_periodic_earnings NUMBER)
342 RETURN NUMBER;
343
344
345 /****************************************************************************
346 Name : CHECK_EE_SAL_CRITERIA
347 Description : This function returns 'Y' if employee's annual gross earning
348 is less than 300,000 MXN.
349 *****************************************************************************/
350
351 FUNCTION CHECK_EE_SAL_CRITERIA(P_CTX_ASSIGNMENT_ID NUMBER
352 ,P_CTX_DATE_EARNED DATE)
353 RETURN VARCHAR2;
354
355
356 /****************************************************************************
357 Name : CHECK_EE_EMPLOYMENT_CRITERIA
358 Description : This Function return 'Y' if employee is working continously
359 between the given start date and end date
360 *****************************************************************************/
361
362 FUNCTION CHECK_EE_EMPLOYMENT_CRITERIA(P_CTX_ASSIGNMENT_ID NUMBER
363 ,P_CTX_DATE_EARNED DATE)
364 RETURN VARCHAR2;
365
366
367 /****************************************************************************
368 Name : IS_ASG_EXEMPT_FROM_ISR
369 Description : This function returns Y if an assignment is exempted from ISR
370 calculation
371 *****************************************************************************/
372
373 FUNCTION IS_ASG_EXEMPT_FROM_ISR(P_CTX_ASSIGNMENT_ID NUMBER
374 ,P_CTX_DATE_EARNED DATE)
375 RETURN VARCHAR2;
376
377
378 /****************************************************************************
379 Name : IS_PER_EXEMPT_FROM_ADJ
380 Description : This function returns Y if an assignment is exempted from Tax
381 Adjustment
382 *****************************************************************************/
383
384 FUNCTION IS_PER_EXEMPT_FROM_ADJ(P_CTX_ASSIGNMENT_ID NUMBER
385 ,P_CTX_DATE_EARNED DATE)
386 RETURN VARCHAR2;
387
388
389 /****************************************************************************
390 Name : GET_MX_ECON_ZONE
391 Description : This function returns Economy Zone('A', 'B', 'C') for the
392 given tax_unit_id
393 *****************************************************************************/
394
395 FUNCTION GET_MX_ECON_ZONE
396 (
397 P_CTX_TAX_UNIT_ID number,
398 P_CTX_DATE_EARNED DATE
399 ) RETURN varchar2;
400
401 /****************************************************************************
402 Name : GET_MIN_WAGE
403 Description : This function returns Minimum Wage for the Economy Zone
404 *****************************************************************************/
405
406 FUNCTION GET_MIN_WAGE
407 (
408 P_CTX_DATE_EARNED DATE,
409 P_TAX_BASIS varchar2,
410 P_ECON_ZONE varchar2
411
412 ) RETURN varchar2;
413
414
415 g_temp_object_actions BOOLEAN;
416
417 /****************************************************************************
418 Name : CONVERT_MONTHLY_TO_PERIOD
419 Description : This function returns monthly isr table value converted to
420 pay period actual number of days value.
421 *****************************************************************************/
422
423
424 /*FUNCTION CONVERT_MONTHLY_TO_PERIOD ( p_business_group_id NUMBER
425 ,p_tax_unit_id NUMBER
426 ,p_payroll_id NUMBER
427 ,p_periodic_value NUMBER
428 ,p_date_earned DATE)
429 RETURN NUMBER; */
430
431 /****************************************************************************
432 Name : CONVERT_INTO_MONTHLY_AVG_SAL
433 Description : This function returns period isr subject value converted to
434 monthly subject.
435 *****************************************************************************/
436
437 FUNCTION CONVERT_INTO_MONTHLY_AVG_SAL ( p_business_group_id NUMBER
438 ,p_tax_unit_id NUMBER
439 ,p_payroll_id NUMBER
440 ,p_subject_earnings NUMBER
441 ,P_DATE_EARNED DATE
442 ,p_hire_date DATE
443 ,p_first_pay_date DATE
444 ,p_period_days out NOCOPY NUMBER)
445 RETURN NUMBER;
446
447 function get_table_value (p_bus_group_id number,
448 p_table_name varchar2,
449 p_col_name varchar2,
450 p_row_value varchar2,
451 p_effective_date date default null,
452 p_period_days number,
453 p_period_number number,
454 p_period_type varchar2)
455 return varchar2;
456
457 END;
458
[Home] [Help]