[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_SOE_SS_PKG
Source
1 package body pay_mx_soe_ss_pkg as
2 /* $Header: paymxsoe.pkb 120.1 2005/08/22 11:47:41 vmehta noship $ */
3 --
4 /*
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
9 * Chertsey, England. *
10 * *
11 * All rights reserved. *
12 * *
13 * This material has been provided pursuant to an agreement *
14 * containing restrictions on its use. The material is also *
15 * protected by copyright law. No part of this material may *
16 * be copied or distributed, transmitted or transcribed, in *
17 * any form or by any means, electronic, mechanical, magnetic, *
18 * manual, or otherwise, or disclosed to third parties without *
19 * the express written permission of Oracle Corporation UK Ltd, *
20 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
21 * England. *
22 * *
23 ******************************************************************
24
25 Description: This package is used to show SS SOE for Mexico.
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ----------- ---------- ----- ------- -----------------------------------
31 11-AUG-2004 vpandya 115.0 Created.
32 20-DEC-2004 vpandya 115.1 Changed view name and added suffix
33 _V.
34 06-Jan-2005 vpandya 115.2 Added following functions:
35 - summary_balances
36 - hourly_earnings
37 - tax_balances
38 - deductions
39 - taxable_benefits
40 - other_balances
41 08-Feb-2005 vpandya 115.3 4145833 Added function setParameters
42 08-Feb-2005 vpandya 115.4 4170915 Changes summary_balances using
43 _PAYMENTS dimension for the
44 prepayment.
45 22-Aug-2005 vmehta 115.5 Changed currency code to MXN
46 instead of MXP
47 */
48 --
49
50 lv_sql long;
51 lv_currency_code varchar2(240);
52 g_debug boolean;
53 g_max_action number;
54 g_min_action number;
55
56
57 FUNCTION employee_earnings( p_assignment_action_id in NUMBER )
58 RETURN LONG IS
59 BEGIN
60
61 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.employee_earnings ');
62 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
63
64 pay_soe_util.clear;
65
66 lv_sql := 'select earn_bal_name COL01
67 ,nvl(earn_reporting_name, earn_bal_name) COL02
68 ,to_char(days_run_val
69 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
70 ,to_char(earn_run_val
71 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL17
72 from PAY_MX_EMPLOYEE_EARNINGS_V
73 where assignment_action_id :action_clause
74 and earn_run_val <> 0';
75
76 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.employee_earnings ');
77
78 return lv_sql;
79
80 END employee_earnings;
81
82 FUNCTION employee_taxes( p_assignment_action_id in NUMBER )
83 RETURN LONG IS
84 BEGIN
85
86 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.employee_taxes ');
87 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
88
89 pay_soe_util.clear;
90
91 lv_sql := 'select balance_name COL01
92 ,nvl(reporting_name, balance_name) COL02
93 ,to_char(run_val
94 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
95 from PAY_MX_EMPLOYEE_TAXES_V
96 where assignment_action_id :action_clause
97 and run_val <> 0';
98
99 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.employee_taxes ');
100
101 return lv_sql;
102
103 END employee_taxes;
104
105 FUNCTION tax_calc_details( p_assignment_action_id in NUMBER )
106 RETURN LONG IS
107 BEGIN
108
109 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.tax_calc_details ');
110 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
111
112 pay_soe_util.clear;
113
114 lv_sql := 'select balance_name COL01
115 ,nvl(reporting_name, balance_name) COL02
116 ,to_char(run_val
117 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
118 from PAY_MX_TAX_CALC_DETAILS_V
119 where assignment_action_id :action_clause
120 and run_val <> 0';
121
122 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.tax_calc_details ');
123
124 return lv_sql;
125
126 END tax_calc_details;
127
128
129 FUNCTION summary_balances( p_assignment_action_id in NUMBER )
130 RETURN LONG IS
131
132 CURSOR c_tax_unit(cp_assignment_action_id NUMBER) IS
133 select tax_unit_id, payroll_action_id
134 from pay_assignment_actions
135 where assignment_action_id = cp_assignment_action_id;
136
137 CURSOR c_action_type(cp_payroll_action_id NUMBER) IS
138 select action_type
139 from pay_payroll_actions
140 where payroll_action_id = cp_payroll_action_id;
141
142 ln_tax_unit_id number;
143 ln_pyrl_act_id number;
144 ln_cnt number;
145 ln_bal_value number;
146
147 lv_action_type varchar2(10);
148
149 lv_curr_dim varchar2(240);
150 lv_ytd_dim varchar2(240);
151
152 summary summary_bal;
153
154 BEGIN
155 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.mx_summary_balances ');
156 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
157 hr_utility.trace('lv_currency_code '||lv_currency_code);
158
159 open c_tax_unit(p_assignment_action_id);
160 fetch c_tax_unit into ln_tax_unit_id,ln_pyrl_act_id;
161 close c_tax_unit;
162
163 open c_action_type(ln_pyrl_act_id);
164 fetch c_action_type into lv_action_type;
165 close c_action_type;
166
167 pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
168
169 IF lv_action_type in ('P', 'U') THEN
170 lv_curr_dim := '_PAYMENTS';
171 ELSE
172 lv_curr_dim := '_ASG_GRE_RUN';
173 END IF;
174
175 lv_ytd_dim := '_ASG_GRE_YTD';
176
177 ln_cnt := 1;
178 summary(ln_cnt).bal_name := 'Gross Earnings';
179 summary(ln_cnt).reporting_name := 'Gross Pay';
180
181 --ln_cnt := ln_cnt + 1;
182 --summary(ln_cnt).bal_name := 'Pre Tax Deductions';
183 --summary(ln_cnt).reporting_name := 'Pre-Tax Deductions';
184
185 ln_cnt := ln_cnt + 1;
186 summary(ln_cnt).bal_name := 'Tax Deductions';
187 summary(ln_cnt).reporting_name := 'Tax Deductions';
188
189 ln_cnt := ln_cnt + 1;
190 summary(ln_cnt).bal_name := 'Deductions';
191 summary(ln_cnt).reporting_name := 'Other Deductions';
192
193 ln_cnt := ln_cnt + 1;
194 summary(ln_cnt).bal_name := 'Total Pay';
195 summary(ln_cnt).reporting_name := 'Total Pay';
196
197 pay_soe_util.clear;
198
199 for i in summary.first..summary.last loop
200
201 hr_utility.trace('i = '||i);
202 hr_utility.trace('Balance = '||summary(i).bal_name);
203
204 summary(i).curr_def_bal_id :=
205 pay_ac_utility.get_defined_balance_id
206 (p_balance_name => summary(i).bal_name
207 ,p_dimension_name => lv_curr_dim
208 ,p_bus_grp_id => NULL
209 ,p_legislation_cd => 'MX');
210
211 -- summary(i).ytd_def_bal_id :=
212 -- get_defined_balance_id(summary(i).bal_name, lv_ytd_dim);
213
214 ln_bal_value := pay_balance_pkg.get_value(summary(i).curr_def_bal_id
215 ,p_assignment_action_id);
216 summary(i).curr_val :=
217 to_char(ln_bal_value
218 ,fnd_currency.get_format_mask(lv_currency_code,40));
219
220 -- summary(i).ytd_val :=
221 -- pay_balance_pkg.get_value(summary(i).ytd_def_bal_id
222 -- ,p_assignment_action_id);
223 --
224 --
225 hr_utility.trace(' summary(i).curr_val '|| summary(i).curr_val);
226 pay_soe_util.setValue('01' ,summary(i).bal_name ,TRUE, FALSE);
227 pay_soe_util.setValue('02' ,summary(i).reporting_name ,FALSE, FALSE);
228 pay_soe_util.setValue('16' ,summary(i).curr_val, FALSE, FALSE );
229 pay_soe_util.setValue('17' ,summary(i).ytd_val, FALSE, TRUE );
230 --
231 end loop;
232
233 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.mx_summary_balances ');
234
235 return pay_soe_util.genCursor;
236
237 END summary_balances;
238
239 FUNCTION hourly_earnings( p_assignment_action_id in NUMBER )
240 RETURN LONG IS
241 BEGIN
242
243 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.hourly_earnings ');
244 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
245
246 pay_soe_util.clear;
247
248 lv_sql := 'select earn_bal_name COL01
249 ,nvl(earn_reporting_name, earn_bal_name) COL02
250 ,to_char(hours_run_val
251 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
252 ,to_char(earn_run_val
253 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL17
254 from PAY_MX_HOURLY_EARNINGS_V
255 where assignment_action_id :action_clause
256 and earn_run_val <> 0';
257
258 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.employee_earnings ');
259
260 return lv_sql;
261
262 END hourly_earnings;
263
264 FUNCTION taxable_benefits( p_assignment_action_id in NUMBER )
265 RETURN LONG IS
266 BEGIN
267
268 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.taxable_benefits ');
269 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
270
271 pay_soe_util.clear;
272
273 lv_sql := 'select balance_name COL01
274 ,nvl(reporting_name, balance_name) COL02
275 ,to_char(run_val
276 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
277 from PAY_MX_TAXABLE_BENEFITS_V
278 where assignment_action_id :action_clause
279 and run_val <> 0';
280
281 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.tax_calc_details ');
282
283 return lv_sql;
284
285 END taxable_benefits;
286
287 FUNCTION tax_balances( p_assignment_action_id in NUMBER )
288 RETURN LONG IS
289 BEGIN
290
291 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.tax_balances ');
292 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
293
294 pay_soe_util.clear;
295
296 lv_sql := 'select balance_name COL01
297 ,nvl(reporting_name, balance_name) COL02
298 ,to_char(run_val
299 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
300 from PAY_MX_TAX_BALANCES_V
301 where assignment_action_id :action_clause
302 and run_val <> 0';
303
304 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.tax_calc_details ');
305
306 return lv_sql;
307
308 END tax_balances;
309
310 FUNCTION deductions( p_assignment_action_id in NUMBER )
311 RETURN LONG IS
312 BEGIN
313
314 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.deductions ');
315 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
316
317 pay_soe_util.clear;
318
319 lv_sql := 'select balance_name COL01
320 ,nvl(reporting_name, balance_name) COL02
321 ,to_char(run_val
322 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
323 from PAY_MX_DEDUCTIONS_V
324 where assignment_action_id :action_clause
325 and run_val <> 0';
326
327 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.tax_calc_details ');
328
329 return lv_sql;
330
331 END deductions;
332
333 FUNCTION other_balances( p_assignment_action_id in NUMBER )
334 RETURN LONG IS
335 BEGIN
336
337 hr_utility.trace('Entering.. pay_mx_soe_ss_pkg.other_balances ');
338 hr_utility.trace('p_assignment_action_id '||p_assignment_action_id);
339
340 pay_soe_util.clear;
341
342 lv_sql := 'select balance_name COL01
343 ,nvl(reporting_name, balance_name) COL02
344 ,to_char(run_val
345 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
346 ,to_char(mtd_val
347 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL17
348 ,to_char(ytd_val
349 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL18
350 from PAY_MX_OTHER_BALANCES_V
351 where assignment_action_id :action_clause';
352
353 hr_utility.trace('Leaving.. pay_mx_soe_ss_pkg.tax_calc_details ');
354
355 return lv_sql;
356
357 END other_balances;
358
359 --
360 --
361 /* ---------------------------------------------------------------------
362 Function : SetParameters
363
364 Text
365 ------------------------------------------------------------------------ */
366 FUNCTION setParameters(p_assignment_action_id in number)
367 RETURN varchar2 is
368 --
369 cursor getParameters(c_assignment_action_id in number) is
370 select pa.payroll_id
371 --, to_number(to_char(pa.effective_date,'J')) effective_date
372 ,replace(substr(FND_DATE.DATE_TO_CANONICAL(pa.effective_date),1,10),'/','-') jsqldate --YYYY-MM-DD
373 ,'' || pa.effective_date || '' effective_date
374 , aa.assignment_id
375 , pa.business_group_id
376 , aa.tax_unit_id
377 ,'''' || bg.currency_code || '''' currency_code
378 ,action_type
379 ,fc.name currency_name
380 from pay_payroll_actions pa
381 , pay_assignment_actions aa
382 , per_business_groups bg
383 , fnd_currencies_vl fc
384 where aa.assignment_action_id = p_assignment_action_id
385 and aa.payroll_action_id = pa.payroll_action_id
386 and pa.business_group_id = bg.business_group_id
387 and fc.currency_code = bg.currency_code
388 and rownum = 1;
389
390 cursor getActions is
391 select assignment_action_id
392 from pay_assignment_actions
393 where level =
394 (select max(level)
395 from pay_assignment_actions
396 connect by source_action_id = prior assignment_action_id
397 start with assignment_action_id = p_assignment_action_id)
398 connect by source_action_id = prior assignment_action_id
399 start with assignment_action_id = p_assignment_action_id;
400
401 l_action_type pay_payroll_actions.action_type%type;
402
403 cursor lockedActions is
404 select locked_action_id,
405 action_sequence
406 from pay_action_interlocks,
407 pay_assignment_actions paa
408 where locking_action_id = p_assignment_action_id
409 and locked_action_id = assignment_action_id
410 and exists ( select 1 from pay_run_types_f prt
411 where prt.legislation_code = 'MX'
412 and prt.run_type_id = paa.run_type_id
413 and prt.run_method <> 'C' )
414 order by action_sequence desc;
415
416 --
417 l_parameters varchar2(2000);
418 l_action_count number;
419 l_actions varchar2(2000);
420 l_max_action number;
421 l_min_action number;
422 l_assignment_action_id number;
423 --
424 begin
425 --
426 if g_debug then
427 hr_utility.set_location('Entering pay_soe_glb.setParameters', 10);
428 end if;
429 --
430 -- Prepay change
431 select action_type
432 into l_action_type
433 from pay_payroll_actions pa
434 ,pay_assignment_actions aa
435 where aa.assignment_action_id = p_assignment_action_id
436 and aa.payroll_action_id = pa.payroll_action_id;
437
438 /* exception
439 when no_data_found then
440 */
441
442 l_action_count := 0;
443 l_max_action := 0;
444 l_min_action := 0;
445
446 if l_action_type in ('P','U') then
447 for a in lockedActions loop
448 l_action_count := l_action_count + 1;
449 l_actions := l_actions || a.locked_action_id|| ',';
450 if l_max_action = 0 then
451 l_max_action := a.locked_action_id;
452 end if;
453 l_min_action := a.locked_action_id;
454 end loop;
455 else
456 for a in getActions loop
457 l_action_count := l_action_count + 1;
458 l_actions := l_actions || a.assignment_action_id|| ',';
459 end loop;
460 end if;
461
462 l_actions := substr(l_actions,1,length(l_actions)-1);
463 --
464 if l_action_type in ( 'P','U' ) then
465 l_assignment_action_id := l_max_action; -- for Prepays, effective date is date of
466 else -- latest run action.
467 l_assignment_action_id := p_assignment_action_id;
468 end if;
469
470 for p in getParameters(l_assignment_action_id) loop
471 l_parameters := 'PAYROLL_ID:' ||p.payroll_id ||':'||
472 'JSQLDATE:' ||p.jsqldate ||':'||
473 'EFFECTIVE_DATE:' ||p.effective_date ||':'||
474 'ASSIGNMENT_ID:' ||p.assignment_id ||':'||
475 'BUSINESS_GROUP_ID:' ||p.business_group_id ||':'||
476 'TAX_UNIT_ID:' ||p.tax_unit_id ||':'||
477 'G_CURRENCY_CODE:' ||p.currency_code ||':'||
478 'PREPAY_MAX_ACTION:' ||l_max_action ||':'||
479 'PREPAY_MIN_ACTION:' ||l_min_action ||':'||
480 'CURRENCY_NAME:' ||p.currency_name ||':'||
481 'ASSIGNMENT_ACTION_ID:'||p_assignment_action_id||':';
482 if g_debug then
483 hr_utility.trace('p_payroll_id = ' || p.payroll_id);
484 hr_utility.trace('jsqldate = ' || p.jsqldate);
485 hr_utility.trace('effective_date = ' || p.effective_date);
486 hr_utility.trace('assignment_id = ' || p.assignment_id);
487 hr_utility.trace('business_group_id = ' || p.business_group_id);
488 hr_utility.trace('tax_unit_id = ' || p.tax_unit_id);
489 hr_utility.trace('g_currency_code = ' || g_currency_code);
490 hr_utility.trace('action_clause = ' || l_actions);
491 end if;
492 g_currency_code := p.currency_code;
493 l_action_type := p.action_type;
494 end loop;
495 --
496 if l_action_count = 1 then
497 l_parameters := l_parameters || 'ACTION_CLAUSE:' ||
498 ' = '||l_actions ||':';
499 else
500 l_parameters := l_parameters || 'ACTION_CLAUSE:' ||
501 ' in ('||l_actions ||')' ||':';
502 end if;
503 --
504 if g_debug then
505 hr_utility.trace('l_parameters = ' || l_parameters);
506 hr_utility.set_location('Leaving pay_soe_glb.setParameters', 20);
507 end if;
508 --
509 return l_parameters;
510 end;
511 --
512 /* ---------------------------------------------------------------------
513 Function : SetParameters
514
515 Text
516 ------------------------------------------------------------------------ */
517 FUNCTION setParameters( p_person_id in number
518 , p_assignment_id in number
519 , p_effective_date date)
520 RETURN VARCHAR2 is
521 begin
522 --
523 if g_debug then
524 hr_utility.set_location('Entering pay_soe_glb.setParameters', 10);
525 end if;
526 --
527 -- NOTE:
528 -- This overridden version of setParameters is not yet fully implemented
529 -- at GLB level.
530 --
531 -- Localizations should provide their own version of setParameters to
532 -- derive the desired assignment_action_id, and then call
533 -- pay_soe_glb.setParameters with that assignment_action_id.
534 --
535 if g_debug then
536 hr_utility.set_location('Leaving pay_soe_glb.setParameters', 20);
537 end if;
538 --
539 RETURN null;
540 --
541 END;
542
543 BEGIN
544 -- hr_utility.trace_on(NULL, 'DEBUG');
545 lv_currency_code := 'MXN';
546 END pay_mx_soe_ss_pkg;