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