1 PACKAGE pay_mx_utility AUTHID CURRENT_USER as
2 /* $Header: pymxutil.pkh 120.3.12010000.1 2008/07/27 23:10:48 appldev ship $ */
3
4
5 /**********************************************************************
6 ** Name : get_days_bal_type_id
7 ** Purpose : This function returns Balance Type ID of Days Balance
8 ** for Mexico.
9 ** Arguments : IN Parameters
10 ** p_balance_type_id -> Balance Type ID of Primary Balance
11 ** Notes :
12 **********************************************************************/
13
14 FUNCTION get_days_bal_type_id (p_balance_type_id IN NUMBER)
15 RETURN NUMBER;
16
17 /************************************************************************
18 ** Type : PL/SQL Table Structure
19 ** Purpose : Stores following values
20 ** bal_type_id -> Balance Type ID
21 ** days_bal_type_id -> Balance Type ID of Days Balance
22 ** bal_uom -> Unit of Measure of Balance
23 ** Notes :
24 ************************************************************************/
25
26 TYPE days_balance IS RECORD ( bal_type_id number(15),
27 days_bal_type_id number(15),
28 days_bal_uom varchar2(240));
29
30 TYPE days_balance_tbl IS TABLE OF days_balance INDEX BY BINARY_INTEGER;
31
32 days_bal_tbl days_balance_tbl;
33
34
35 /**********************************************************************
36 ** Name : get_hours_bal_type_id
37 ** Purpose : This function returns Balance Type ID of Hours Balance
38 ** for Mexico.
39 ** Arguments : IN Parameters
40 ** p_balance_type_id -> Balance Type ID of Primary Balance
41 ** Notes :
42 **********************************************************************/
43
44 FUNCTION get_hours_bal_type_id (p_balance_type_id IN NUMBER)
45 RETURN NUMBER;
46
47 /************************************************************************
48 ** Type : PL/SQL Table Structure
49 ** Purpose : Stores following values
50 ** bal_type_id -> Balance Type ID
51 ** hours_bal_type_id -> Balance Type ID of Hours Balance
52 ** bal_uom -> Unit of Measure of Balance
53 ** Notes :
54 ************************************************************************/
55
56 TYPE hours_balance IS RECORD ( bal_type_id number(15),
57 hours_bal_type_id number(15),
58 hours_bal_uom varchar2(240));
59
60 TYPE hours_balance_tbl IS TABLE OF hours_balance INDEX BY BINARY_INTEGER;
61
62 hours_bal_tbl hours_balance_tbl;
63
64
65 /************************************************************************
66 ** Type : PL/SQL Table Structure
67 ** Purpose : Stores following values
68 ** name -> Period Type of The Payroll
69 ** days -> No. of days for the payroll
70 ** frequency in a year.
71 **
72 ** Notes :
73 ** Below Table contains period type of Weekly, Bi-Weekly, Monthly and
74 ** Semi-Monthly payroll and its number of days for the current year
75 ** based on value entered in pay_mx_legislation_info_f for
76 ** Legislation Type 'MX Annualization Factor'.
77 ************************************************************************/
78
79 TYPE payroll_period_type IS RECORD ( name VARCHAR2(150)
80 , days NUMBER);
81
82 TYPE g_period_type IS TABLE OF payroll_period_type INDEX BY BINARY_INTEGER;
83
84 py_prd_tp g_period_type;
85
86 /**********************************************************************
87 ** Type : Procedure
88 ** Name : get_days_mth_yr_for_pay_period
89 ** Purpose : This procedure popuate payroll_period_type PL/SQL table
90 ** for the period type of the payroll and its number of
91 ** days. (PL/SQL table structure mentioned above)
92 **
93 ** Arguments : IN Parameters
94 ** p_payroll_id -> Payroll ID
95 **
96 ** OUT Parameters
97 ** p_period_type -> Period Type of the payroll
98 ** p_days_year -> No. of Days in Year for the payroll
99 **
100 ** Notes :
101 **********************************************************************/
102
103 PROCEDURE get_days_yr_for_pay_period( p_payroll_id IN NUMBER
104 ,p_period_type OUT NOCOPY VARCHAR2
105 ,p_days_year OUT NOCOPY NUMBER);
106
107 /************************************************************************
108 ** Type : PL/SQL Table Structure
109 ** Purpose : Stores following values
110 ** Tax Unit ID -> Tax Unit ID
111 ** days_month -> No. of days per Month
112 ** days_year -> No. of days per Year
113 **
114 ** Notes :
115 ** Below Table contains Number of Days per Month and
116 ** Number of Days per Year entered at GRE Level. If not found at GRE
117 ** Level then get the value from Legal Employer Level.
118 ** Two PL/SQL tables are used to hold the values entered at the GRE
119 ** and legal employer level. Each table is indexed on organization_id
120 ************************************************************************/
121
122 TYPE number_of_days IS RECORD ( days_month NUMBER
123 , days_year NUMBER);
124
125 TYPE g_no_of_days IS TABLE OF number_of_days INDEX BY BINARY_INTEGER;
126
127 gre_no_of_days g_no_of_days;
128 le_no_of_days g_no_of_days;
129
130 /**********************************************************************
131 ** Type : Procedure
132 ** Name : get_no_of_days_for_org
133 ** Purpose : This procedure populate number_of_days PL/SQL table
134 ** for the Month and the Year for GRE or Legal Employer.
135 ** (PL/SQL table structure mentioned above)
136 **
137 ** Arguments : IN Parameters
138 ** p_business_group_id -> Business Group ID
139 ** p_tax_unit_id -> Tax Unit ID
140 **
141 ** OUT Parameters
142 ** p_days_month -> No. of Days in Month
143 ** p_days_year -> No. of Days in Year
144 **
145 ** Notes :
146 **********************************************************************/
147
148 PROCEDURE get_no_of_days_for_org( p_business_group_id IN NUMBER
149 ,p_org_id IN NUMBER
150 ,p_gre_or_le IN VARCHAR2
151 ,p_days_month OUT NOCOPY NUMBER
152 ,p_days_year OUT NOCOPY NUMBER);
153
154 /**********************************************************************
155 ** Type : Function
156 ** Name : get_days_month_year
157 ** Purpose : This function returns number of days based on p_mode.
158 ** If p_mode is 'MONTH', this function returns no of days
159 ** in month and if it is 'YEAR', this function return
160 ** returns no of days in year.
161 **
162 ** Arguments : IN Parameters
163 ** p_business_group_id -> Business Group ID
164 ** p_tax_unit_id -> Tax Unit ID
165 ** p_payroll_id -> Payroll ID
166 ** p_mode -> 'MONTH' or 'YEAR'
167 **
168 ** Notes :
169 **********************************************************************/
170 FUNCTION get_days_month_year( p_business_group_id IN NUMBER
171 ,p_tax_unit_id IN NUMBER
172 ,p_payroll_id IN NUMBER
173 ,p_mode IN VARCHAR2 )
174 RETURN NUMBER;
175
176 /**********************************************************************
177 ** Type : Function
178 ** Name : get_days_in_year
179 ** Purpose : This function returns number of days based in year.
180 ** This function calls get_days_month_year function
181 ** with p_mode 'YEAR'.
182 **
183 ** Arguments : IN Parameters
184 ** p_business_group_id -> Business Group ID
185 ** p_tax_unit_id -> Tax Unit ID
186 ** p_payroll_id -> Payroll ID
187 **
188 ** Notes :
189 **********************************************************************/
190 FUNCTION get_days_in_year( p_business_group_id IN NUMBER
191 ,p_tax_unit_id IN NUMBER
192 ,p_payroll_id IN NUMBER)
193 RETURN NUMBER;
194
195 /**********************************************************************
196 ** Type : Function
197 ** Name : get_days_in_month
198 ** Purpose : This function returns number of days based in month.
199 ** This function calls get_days_month_year function
200 ** with p_mode 'MONTH'.
201 **
202 ** Arguments : IN Parameters
203 ** p_business_group_id -> Business Group ID
204 ** p_tax_unit_id -> Tax Unit ID
205 ** p_payroll_id -> Payroll ID
206 **
207 ** Notes :
208 **********************************************************************/
209 FUNCTION get_days_in_month( p_business_group_id IN NUMBER
210 ,p_tax_unit_id IN NUMBER
211 ,p_payroll_id IN NUMBER)
212 RETURN NUMBER;
213
214 /**********************************************************************
215 ** Type : Function
216 ** Name : get_days_in_pay_period
217 ** Purpose : This function returns number of days based on payroll
218 ** frequency.
219 ** Week -> 7 Days
220 ** Bi-Week -> 14 Days
221 ** Month -> Getting no of days using get_days_in_month
222 ** Semi-Month -> Month Days (above) / 2
223 **
224 ** Arguments : IN Parameters
225 ** p_business_group_id -> Business Group ID
226 ** p_tax_unit_id -> Tax Unit ID
227 ** p_payroll_id -> Payroll ID
228 **
229 ** Notes :
230 **********************************************************************/
231 FUNCTION get_days_in_pay_period( p_business_group_id IN NUMBER
232 ,p_tax_unit_id IN NUMBER
233 ,p_payroll_id IN NUMBER)
234 RETURN NUMBER;
235
236
237 /**********************************************************************
238 ** Type : Function
239 ** Name : get_days_in_bimonth
240 ** Purpose : This function returns number of days for current and
241 ** previous month.
242 ** If payroll processsing is on 15-APR-2005 then this function
243 ** will return 30 (for april 2005) + 31 (for mar 2005) = 61
244 ** days.
245 ** Notes :
246 **********************************************************************/
247 FUNCTION get_days_in_bimonth
248 RETURN NUMBER ;
249
250
251 /**********************************************************************
252 ** Type : Function
253 ** Name : get_classification_id
254 ** Purpose : This function returns classification_id for Mexico.
255 **
256 ** Arguments : IN Parameters
257 ** p_classification_name -> Classification Name.
258 ** Notes :
259 **********************************************************************/
260 FUNCTION get_classification_id( p_classification_name IN VARCHAR2 )
261 RETURN NUMBER;
262
263 /**********************************************************************
264 ** Type : Procedure
265 ** Name : create_ele_tmplt_class_usg
266 ** Purpose : This procedure creates records for
267 ** PAY_ELE_TMPLT_CLASS_USAGES table.
268 **
269 ** Arguments : IN Parameters
270 ** p_classification_id -> Classification ID
271 ** p_template_id -> Template ID
272 ** p_display_process_mode -> Display Process Mode
273 ** p_display_arrearage -> Display Arrearage
274 ** Notes :
275 **********************************************************************/
276 PROCEDURE create_ele_tmplt_class_usg( p_classification_id IN NUMBER
277 ,p_template_id IN NUMBER
278 ,p_display_process_mode IN VARCHAR2
279 ,p_display_arrearage IN VARCHAR2 );
280
281 /**********************************************************************
282 ** Type : Procedure
283 ** Name : create_template_classification
284 ** Purpose : This procedure is getting called from the template
285 ** with Template ID and Classification Type and will
286 ** decides how many record to be created for
287 ** PAY_ELE_TMPLT_CLASS_USAGES table.
288 **
289 ** Arguments : IN Parameters
290 ** p_template_id -> Template ID
291 ** p_classification_type -> Display Process Mode
292 ** Notes :
293 **********************************************************************/
294 PROCEDURE create_template_classification( p_template_id IN NUMBER
295 ,p_classification_type IN VARCHAR2);
296
297 /**********************************************************************
298 ** Type : Function
299 ** Name : get_default_imp_date
300 ** Purpose : This function is returning Implementation Date.
301 ** Using in Social Security Archiver.
302 **
303 ** Arguments :
304 ** Notes :
305 **********************************************************************/
306 FUNCTION get_default_imp_date RETURN VARCHAR2;
307
308 /**********************************************************************
309 ** Type : Function
310 ** Name : get_legi_param_val
311 ** Purpose : This function gets Paramter Value from
312 ** legislation_parameters column of pay_payroll_actions
313 ** WHENEVER TWO PARAMETERS ARE SEPARATED BY A PIPE (|)
314 **
315 ** WARNING : IF THERE IS A SPACE IN THE VALUE
316 ** THEN DONOT USE THIS FUNCTION
317 **
318 ** Arguments :
319 ** Notes :
320 **********************************************************************/
321 FUNCTION get_legi_param_val(name IN VARCHAR2,
322 parameter_list IN VARCHAR2) RETURN VARCHAR2;
323
324 /*************************************************************************
325 ** Type : Function
326 ** Name : get_legi_param_val
327 ** Purpose : This is an overloaded function that gets paramter Value
328 ** from legislation_parameters column of pay_payroll_actions
329 ** WHENEVER TWO PARAMETERS ARE SEPARATED BY EITHER A PIPE (|)
330 ** OR A SPACE.
331 **
332 ** Arguments :
333 ** Notes :
334 **********************************************************************/
335 FUNCTION get_legi_param_val(name IN VARCHAR2,
336 parameter_list IN VARCHAR2,
337 tag IN VARCHAR2) RETURN VARCHAR2;
338
339 /**********************************************************************
340 ** Type : Function
341 ** Name : get_parameter
342 ** Purpose : This function gets Paramter Value from
343 ** legislation_parameters column of pay_payroll_actions
344 ** WHENEVER TWO PARAMETERS ARE SEPARATED BY A SPACE
345 **
346 ** WARNING : IF THERE IS A PIPE (OTHER THAN A SPACE)IN THE VALUE
347 ** THEN DONOT USE THIS FUNCTION
348 **
349 ** Arguments :
350 ** Notes :
351 **********************************************************************/
352 FUNCTION get_parameter(name IN VARCHAR2,
353 parameter_list IN VARCHAR2) RETURN VARCHAR2;
354
355 /**********************************************************************
356 ** Type : Function
357 ** Name : get_process_parameters
358 ** Purpose : Returns Legislative parameters for specified payroll
359 ** action
360 **********************************************************************/
361 FUNCTION get_process_parameters(p_cntx_payroll_action_id IN NUMBER,
362 p_parameter_name IN VARCHAR2)
363 RETURN VARCHAR2;
364
365 /**************************************************************************
366 ** Name : GET_MX_ECON_ZONE
367 ** Description : This function returns Economy Zone('A', 'B', 'C') for the
368 ** given tax_unit_id
369 ***************************************************************************/
370
371 FUNCTION GET_MX_ECON_ZONE
372 (
373 P_CTX_TAX_UNIT_ID number,
374 P_CTX_DATE_EARNED DATE
375 ) RETURN varchar2;
376
377 /**************************************************************************
378 ** Name : GET_MIN_WAGE
379 ** Description : This function returns Minimum Wage for the Economy Zone
380 ***************************************************************************/
381
382 FUNCTION GET_MIN_WAGE
383 (
384 P_CTX_DATE_EARNED DATE,
385 P_TAX_BASIS varchar2,
386 P_ECON_ZONE varchar2
387
388 ) RETURN varchar2;
389
390
391 END pay_mx_utility;
[Home] [Help]