1 PACKAGE BODY pay_ac_util AS
2 /* $Header: pyacdisc.pkb 115.1 2004/02/16 16:03:59 vpandya noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
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_ac_util
21
22 Description : Package contains functions and procedures used
23 by Discoverer
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31 27-OCT-2003 asasthan 115.0 Created
32 16-FEB-2004 vpandya 115.1 Gross to Net Adhoc, Added functions
33 get_def_bal_for_seeded_bal and
34 get_value.
35
36 ***********************************************************************/
37 g_currency_code varchar2(240) := NULL;
38
39 FUNCTION get_legis_parameter(p_parameter_name in varchar2,
40 p_parameter_list varchar2) return number
41 is
42 start_ptr number;
43 end_ptr number;
44 token_val pay_payroll_actions.legislative_parameters%type;
45 par_value pay_payroll_actions.legislative_parameters%type;
46 ln_parameter_id number :=0;
47 begin
48
49 token_val := p_parameter_name||'=';
50
51 start_ptr := instr(p_parameter_list, token_val) + length(token_val);
52 end_ptr := instr(p_parameter_list, ' ',start_ptr);
53
54
55 /* if there is no spaces use then length of the string */
56 if end_ptr = 0 then
57 end_ptr := length(p_parameter_list)+1;
58 end if;
59
60 /* Did we find the token */
61 if instr(p_parameter_list, token_val) = 0 then
62 par_value := NULL;
63 else
64 par_value := substr(p_parameter_list, start_ptr, end_ptr - start_ptr);
65 end if;
66 ln_parameter_id := to_number(par_value);
67 return ln_parameter_id;
68
69 end get_legis_parameter;
70
71
72
73 /*********************************************************************
74 Name : get_jurisdiction_name
75 Purpose : This function returns the name of the jurisdiction
76 If Jurisdiction_code is like 'XX-000-0000' then
77 it returns State Name from py_us_states
78 If Jurisdiction_code is like 'XX-XXX-0000' then
79 it returns County Name from paY_us_counties
80 If Jurisdiction_code is like 'XX-XXX-XXXX' then
81 it returns City Name from pay_us_city_name
82 If Jurisdiction_code is like 'XX-XXXXX' then
83 it returns School Name from pay_us_school_dsts
84 In case jurisdiction code could not be found relevent
85 table then NULL is returned.
86 Arguments : p_jurisdiction_code
87 Notes :
88 *********************************************************************/
89 FUNCTION get_jurisdiction_name(p_jurisdiction_code in varchar2)
90
91 RETURN VARCHAR2
92 IS
93
94 cursor c_get_state(cp_state_code in varchar2) is
95 select state_abbrev
96 from pay_us_states
97 where state_code = cp_state_code;
98
99 cursor c_get_county( cp_state_code in varchar2
100 ,cp_county_code in varchar2
101 ) is
102 select county_name
103 from pay_us_counties
104 where state_code = cp_state_code
105 and county_code = cp_county_code;
106
107 cursor c_get_city( cp_state_code in varchar2
108 ,cp_county_code in varchar2
109 ,cp_city_code in varchar2
110 ) is
111 select city_name
112 from pay_us_city_names
113 where state_code = cp_state_code
114 and county_code = cp_county_code
115 and city_code = cp_city_code
116 and primary_flag = 'Y';
117
118 lv_state_code VARCHAR2(2) := substr(p_jurisdiction_code,1,2);
119 lv_county_code VARCHAR2(3) := substr(p_jurisdiction_code,4,3);
120 lv_city_code VARCHAR2(4) := substr(p_jurisdiction_code,8,4);
121 lv_jurisdiction_name VARCHAR2(240):= null;
122
123 lv_procedure_name VARCHAR2(50) := '.get_jurisdiction_name' ;
124 BEGIN
125 if p_jurisdiction_code like '__-000-0000' then
126 open c_get_state(lv_state_code);
127 fetch c_get_state into lv_jurisdiction_name;
128 close c_get_state;
129 elsif p_jurisdiction_code like '__-___-0000' then
130 open c_get_county(lv_state_code
131 ,lv_county_code);
132 fetch c_get_county into lv_jurisdiction_name;
133 close c_get_county;
134 elsif p_jurisdiction_code like '__-___-____' then
135 open c_get_city( lv_state_code
136 ,lv_county_code
137 ,lv_city_code);
138 fetch c_get_city into lv_jurisdiction_name;
139 close c_get_city;
140 elsif p_jurisdiction_code like '__-_____' then
141 -- this is school district make a function call
142 lv_jurisdiction_name
143 := pay_us_employee_payslip_web.get_school_dsts_name(p_jurisdiction_code);
144 end if;
145
146 return (lv_jurisdiction_name);
147 END get_jurisdiction_name;
148
149
150
151 /*********************************************************************
152 Name : get_state_abbrev
153 Purpose : This function returns the state abbrev for the jurisdiction
154 Arguments : p_jurisdiction_code
155 Notes :
156 *********************************************************************/
157 FUNCTION get_state_abbrev(p_jurisdiction_code in varchar2)
158
159 RETURN VARCHAR2
160 IS
161
162 cursor c_get_state(cp_state_code in varchar2) is
163 select state_abbrev
164 from pay_us_states
165 where state_code = cp_state_code;
166
167 lv_state_code VARCHAR2(2) := substr(p_jurisdiction_code,1,2);
168 lv_state_abbrev VARCHAR2(2):= null;
169
170 BEGIN
171
172 open c_get_state(lv_state_code);
173 fetch c_get_state into lv_state_abbrev;
174 close c_get_state;
175 return (lv_state_abbrev);
176 END get_state_abbrev;
177
178 /************************************************************
179 Name : get_format_value
180 purpuse : given a value, it formats the value to a given
181 currency_code and precision.
182 arguments : p_business_group_id, p_value
183 notes :
184 *************************************************************/
185 FUNCTION get_format_value(p_business_group_id in number,
186 p_value in number)
187 RETURN varchar2 IS
188
189 lv_formatted_number varchar2(50);
190
191 CURSOR c_currency_code is
192 select hoi.org_information10
193 from hr_organization_units hou,
194 hr_organization_information hoi
195 where hou.business_group_id = p_business_group_id
196 and hou.organization_id = hoi.organization_id
197 and hoi.org_information_context = 'Business Group Information';
198
199 BEGIN
200 IF g_currency_code is null THEN
201 OPEN c_currency_code;
202 FETCH c_currency_code into g_currency_code;
203 CLOSE c_currency_code;
204 END IF;
205 IF g_currency_code is not null THEN
206 lv_formatted_number := to_char(p_value,
207 fnd_currency.get_format_mask(
208 g_currency_code,40));
209 ELSE
210 lv_formatted_number := p_value;
211 END IF;
212
213 return lv_formatted_number;
214
215 EXCEPTION
216 when others then
217 return p_value;
218 END get_format_value;
219
220 FUNCTION get_consolidation_set(p_business_group_id in number
221 ,p_consolidation_set_id in number)
222 return varchar2
223 IS
224 cursor c_consolidation_set (cp_business_group_id in number,
225 cp_consolidation_set_id in number) is
226 select consolidation_set_name
227 from pay_consolidation_sets
228 where consolidation_set_id = cp_consolidation_set_id
229 and business_group_id = p_business_group_id;
230
231 lv_consolidation_set_name varchar2(200);
232
233 BEGIN
234
235 open c_consolidation_set(p_business_group_id,
236 p_consolidation_set_id);
237 fetch c_consolidation_set into lv_consolidation_set_name;
238 close c_consolidation_set;
239
240 return lv_consolidation_set_name;
241 END;
242
243
244 FUNCTION get_payroll_name(p_business_group_id in number
245 ,p_payroll_id in number
246 ,p_effective_date in date)
247 return varchar2
248 IS
249 cursor c_payroll_name (cp_business_group_id in number,
250 cp_payroll_id in number,
251 cp_effective_date in date) is
252 select payroll_name
253 from pay_all_payrolls_f
254 where payroll_id = cp_payroll_id
255 and business_group_id = p_business_group_id
256 and p_effective_date between effective_start_date
257 and effective_end_date;
258
259 lv_payroll_name varchar2(200);
260
261 BEGIN
262
263 open c_payroll_name(p_business_group_id,
264 p_payroll_id,
265 p_effective_date);
266 fetch c_payroll_name into lv_payroll_name;
267 close c_payroll_name;
268
269 return lv_payroll_name;
270 END;
271
272
273 /************************************************************
274 Name : format_to_date
275 Purpuse : The function formats the value in date format
276 Arguments : p_value
277 Notes :
278 *************************************************************/
279 FUNCTION format_to_date(p_char_date in varchar2)
280 RETURN date IS
281
282 ld_return_date DATE;
283
284 BEGIN
285 if length(p_char_date) = 19 then
286 ld_return_date := fnd_date.canonical_to_date(p_char_date);
287 else
288 begin
289 ld_return_date := fnd_date.chardate_to_date(p_char_date);
290
291 exception
292 when others then
293 ld_return_date := null;
294 end;
295
296 end if;
297
298 return(ld_return_date);
299
300 END format_to_date;
301
302 /********************************************************************
303 ** Function : get_def_bal_for_seeded_bal
304 ** Arguments: p_balance_name
305 ** p_legislation_code
306 ** Returns : Defined Balance Id
307 ** Purpose : This function has 2 parameters as input. The function
308 ** returns defined balance id of the seeded balance. This
309 ** function also uses PL/SQL table def_bal_tbl to cache
310 ** defined balance id for seeded balanced.
311 *********************************************************************/
312 FUNCTION get_def_bal_for_seeded_bal (p_balance_name in varchar2
313 ,p_legislation_code in varchar2)
314 RETURN number
315 IS
316
317 cursor c_def_bal(cp_balance_name varchar2
318 ,cp_legislation_code varchar2) is
319 select bal.balance_name
320 , def.legislation_code
321 , def.defined_balance_id
322 , bal.balance_type_id
323 , dim.balance_dimension_id
327 where bal.legislation_code = cp_legislation_code
324 from pay_balance_types bal,
325 pay_balance_dimensions dim,
326 pay_defined_balances def
328 and bal.balance_name in ( cp_balance_name )
329 and dim.legislation_code = cp_legislation_code
330 and dim.dimension_name = 'Assignment within Government Reporting Entity Run'
331 and def.legislation_code = cp_legislation_code
332 and def.balance_type_id = bal.balance_type_id
333 and def.balance_dimension_id = dim.balance_dimension_id;
334
335 ln_defined_balance_id NUMBER := -1;
336
337 TYPE CHAR_80_TABLE IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
338
339 lv_balance_name CHAR_80_TABLE;
340
341 ln_index number;
342 ln_step number;
343 BEGIN
344
345 -- hr_utility.trace_on(null,'DEFBAL');
346 hr_utility.trace('p_balance_name : '||p_balance_name);
347 hr_utility.trace('p_legislation_code : '||p_legislation_code);
348
349 ln_index := pay_ac_util.ltr_def_bal.count;
350
351 ln_step := 1;
352
353 if ln_index = 0 then
354
355 ln_step := 2;
356
357 lv_balance_name(1) := 'Gross Earnings';
358 lv_balance_name(2) := 'Gross Pay';
359 lv_balance_name(3) := 'Regular Earnings';
360 lv_balance_name(4) := 'Supplemental Earnings';
361 lv_balance_name(5) := 'Imputed Earnings';
362 lv_balance_name(6) := 'Taxable Benefits';
363 lv_balance_name(7) := 'Non Payroll Payments';
364 lv_balance_name(8) := 'Tax Deductions';
365 lv_balance_name(9) := 'Pre Tax Deductions';
366 lv_balance_name(10) := 'Involuntary Deductions';
367 lv_balance_name(11) := 'Voluntary Deductions';
368 lv_balance_name(12) := 'Net';
369 lv_balance_name(13) := 'Payments';
370
371 for i in 1..13 loop
372
373 hr_utility.trace('lv_balance_name : '||lv_balance_name(i));
374 ln_step := 3;
375
376 for defbal in c_def_bal(lv_balance_name(i), p_legislation_code)
377 loop
378 hr_utility.trace('Balance Name : '||defbal.balance_name);
379 ln_step := 4;
380 pay_ac_util.ltr_def_bal(ln_index).balance_name
381 := defbal.balance_name;
382 pay_ac_util.ltr_def_bal(ln_index).legislation_code
383 := defbal.legislation_code;
384 pay_ac_util.ltr_def_bal(ln_index).defined_balance_id
385 := defbal.defined_balance_id;
386 pay_ac_util.ltr_def_bal(ln_index).balance_type_id
387 := defbal.balance_type_id;
388 pay_ac_util.ltr_def_bal(ln_index).balance_dimension_id
389 := defbal.balance_dimension_id;
390
391 ln_index := ln_index + 1;
392 end loop;
393 end loop;
394 end if;
395
396 ln_step := 5;
397 if ln_index > 0 then
398 for i in pay_ac_util.ltr_def_bal.first ..
399 pay_ac_util.ltr_def_bal.last
400 loop
401 ln_step := 6;
402 if pay_ac_util.ltr_def_bal(i).balance_name =
403 p_balance_name and
404 pay_ac_util.ltr_def_bal(i).legislation_code =
405 p_legislation_code
406 then
407 ln_step := 7;
408 hr_utility.trace(p_balance_name ||' ' ||
409 pay_ac_util.ltr_def_bal(i).defined_balance_id);
410 return pay_ac_util.ltr_def_bal(i).defined_balance_id;
411 end if;
412 end loop;
413 end if;
414
415 ln_step := 8;
416 for defbal in c_def_bal(p_balance_name, p_legislation_code)
417 loop
418 ln_step := 9;
419 pay_ac_util.ltr_def_bal(ln_index).balance_name
420 := defbal.balance_name;
421 pay_ac_util.ltr_def_bal(ln_index).legislation_code
422 := defbal.legislation_code;
423 pay_ac_util.ltr_def_bal(ln_index).defined_balance_id
424 := defbal.defined_balance_id;
425 pay_ac_util.ltr_def_bal(ln_index).balance_type_id
426 := defbal.balance_type_id;
427 pay_ac_util.ltr_def_bal(ln_index).balance_dimension_id
428 := defbal.balance_dimension_id;
429
430 ln_defined_balance_id := defbal.defined_balance_id;
431
432 hr_utility.trace('Balance not in PL/SQL table: '||defbal.balance_name);
433
434 end loop;
435
436 RETURN ln_defined_balance_id;
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 hr_utility.trace('Error at Step : ' || ln_step );
441 RETURN -1;
442 END;
443
444 /********************************************************************
445 ** Function : get_value
446 ** Arguments: p_assignment_action_id
447 ** p_defined_balance_id
448 ** p_tax_unit_id
449 ** Returns : Valueed Balance Id
450 ** Purpose : This function has 3 parameters as input. This function
451 ** sets the context for Tax Unit Id and then calling
452 ** pay_balance_pkg.get_value to get value for given
453 ** assignment_action id and defined balance id.
454 *********************************************************************/
455 FUNCTION get_value(p_assignment_action_id in number
456 ,p_defined_balance_id in number
457 ,p_tax_unit_id in number)
458 RETURN number IS
459 ln_value number := 0;
460 BEGIN
461
462 if gn_tax_unit_id <> p_tax_unit_id then
463 hr_utility.trace('p_tax_unit_id : '||p_tax_unit_id);
464 hr_utility.trace('gn_tax_unit_id : '||gn_tax_unit_id);
468 -- hr_utility.trace_off;
465 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
466 gn_tax_unit_id := p_tax_unit_id;
467 end if;
469
470 ln_value := nvl(pay_balance_pkg.get_value(p_defined_balance_id
471 ,p_assignment_action_id),0);
472 return ln_value;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 return 0;
477 END;
478
479 --Begin
480 --hr_utility.trace_on(null,'ACDIS');
481
482
483 end pay_ac_util;
[Home] [Help]