1 PACKAGE BODY pay_ac_utility as
2 /* $Header: pyacutil.pkb 120.2 2005/12/01 08:45 sdahiya noship $ */
3
4 /*********************************************************************
5 ** Name : get_defined_balance_id
6 ** Purpose : This function returns the defined_balance_id for a
7 ** given Balance Name and Dimension for Mexico.
8 ** Arguments : IN Parameters
9 ** p_balance_type_id -> Balance Type ID
10 ** p_balance_name -> Balance Name
11 ** p_dimension_name -> Dimension Name or
12 ** database_item_suffix
13 ** p_bus_grp_id -> Business Group ID
14 ** p_legislation_cd -> Legislation Code
15 **
16 ** Notes : The combination of Business Group ID and
17 ** Legislation Code would be 'Not NULL / NULL' or
18 ** 'NULL / Not NULL'.
19 **
20 ** When first character of p_dimension_name is
21 ** underscore, then it is considered as
22 ** database_item_suffix.
23 *********************************************************************/
24
25 FUNCTION get_defined_balance_id (p_balance_type_id IN NUMBER
26 ,p_dimension_name IN VARCHAR2
27 ,p_bus_grp_id IN NUMBER
28 ,p_legislation_cd IN VARCHAR2)
29 RETURN NUMBER IS
30
31 cursor get_legislation (cp_bus_grp_id NUMBER) is
32 select org_information9
33 from hr_organization_information
34 where org_information_context = 'Business Group Information'
35 and organization_id = cp_bus_grp_id;
36
37 cursor get_bal_dim_id (cp_dimension_name VARCHAR2
38 ,cp_legislation_cd VARCHAR2) IS
39 select balance_dimension_id
40 from pay_balance_dimensions
41 where legislation_code = cp_legislation_cd
42 and dimension_name = cp_dimension_name;
43
44 cursor get_baldim_id (cp_database_item_suffix VARCHAR2
45 ,cp_legislation_cd VARCHAR2) IS
46 select balance_dimension_id
47 from pay_balance_dimensions
48 where legislation_code = cp_legislation_cd
49 and database_item_suffix = cp_database_item_suffix;
50
51 cursor get_def_bal_id (cp_bal_typ_id NUMBER
52 ,cp_bal_dim_id NUMBER) IS
53 select defined_balance_id
54 from pay_defined_balances
55 where balance_type_id = cp_bal_typ_id
56 and balance_dimension_id = cp_bal_dim_id;
57
58 ln_legislation_cd VARCHAR2(240);
59 ln_bal_dim_id NUMBER;
60 ln_defined_balance_id NUMBER;
61 BEGIN
62
63 if p_bus_grp_id is not null and p_legislation_cd is null then
64 open get_legislation(p_bus_grp_id);
65 fetch get_legislation into ln_legislation_cd;
66 close get_legislation;
67 else
68 ln_legislation_cd := p_legislation_cd;
69 end if;
70
71 if substr(p_dimension_name, 1, 1) = '_' then
72 open get_baldim_id(p_dimension_name, ln_legislation_cd);
73 fetch get_baldim_id into ln_bal_dim_id;
74 close get_baldim_id;
75 else
76 open get_bal_dim_id(p_dimension_name, ln_legislation_cd);
77 fetch get_bal_dim_id into ln_bal_dim_id;
78 close get_bal_dim_id;
79 end if;
80
81 ln_defined_balance_id := 0;
82
83 open get_def_bal_id(p_balance_type_id, ln_bal_dim_id);
84 fetch get_def_bal_id into ln_defined_balance_id;
85 close get_def_bal_id;
86
87 return (ln_defined_balance_id);
88 END get_defined_balance_id;
89
90 FUNCTION get_defined_balance_id (p_balance_name IN VARCHAR2
91 ,p_dimension_name IN VARCHAR2
92 ,p_bus_grp_id IN NUMBER
93 ,p_legislation_cd IN VARCHAR2)
94 RETURN NUMBER IS
95
96 ln_balance_type_id NUMBER;
97 ln_defined_balance_id NUMBER;
98
99 BEGIN
100
101 ln_balance_type_id := get_balance_type_id ( p_balance_name
102 , p_bus_grp_id
103 , p_legislation_cd);
104
105 ln_defined_balance_id := get_defined_balance_id (ln_balance_type_id
106 ,p_dimension_name
107 ,p_bus_grp_id
108 ,p_legislation_cd);
109
110 return (ln_defined_balance_id);
111
112 END get_defined_balance_id;
113
114 /**********************************************************************
115 ** Name : get_balance_type_id
116 ** Purpose : This function returns balance type ID of given Balance
117 ** Name, Business Group ID and Legislation Code.
118 ** Arguments : IN Parameters
119 ** p_balance_name -> Balance Name
120 ** p_bus_grp_id -> Business Group ID
121 ** p_legislation_cd -> Legislation Code
122 ** Notes :
123 **********************************************************************/
124
125 FUNCTION get_balance_type_id ( p_balance_name IN VARCHAR2
126 , p_bus_grp_id IN NUMBER
127 , p_legislation_cd IN VARCHAR2)
128 RETURN NUMBER IS
129
130 cursor get_bal_tp_id ( cp_balance_name VARCHAR2
131 , cp_bus_grp_id NUMBER
132 , cp_legislation_cd VARCHAR2 )is
133 select balance_type_id
134 from pay_balance_types
135 where balance_name = p_balance_name
136 and (( business_group_id = cp_bus_grp_id and
137 cp_legislation_cd is null ) or
138 ( legislation_code = cp_legislation_cd and
139 cp_bus_grp_id is null ) );
140
141 ln_balance_type_id NUMBER;
142
143 BEGIN
144
145 open get_bal_tp_id ( p_balance_name
146 , p_bus_grp_id
147 , p_legislation_cd);
148 fetch get_bal_tp_id into ln_balance_type_id;
149 close get_bal_tp_id;
150
151 return ln_balance_type_id;
152
153 END get_balance_type_id;
154
155 /*********************************************************************
156 ** Name : get_bal_or_rep_name
157 ** Purpose : This function returns translated value of either the
158 ** balance name or reporting name of the balance based
159 ** on p_desc_type. The p_desc_type could be either 'B'
160 ** for Balance or 'R' for Reporting name.
161 **
162 ** Arguments : IN Parameters
163 ** p_balance_type_id -> Balance Type ID
164 ** p_desc_type -> 'B' or 'R'
165 ** Notes :
166 *********************************************************************/
167
168 FUNCTION get_bal_or_rep_name (p_balance_type_id IN NUMBER
169 ,p_desc_type IN VARCHAR2)
170 RETURN VARCHAR2 IS
171
172 cursor csr_balance (cp_balance_type_id number) is
173 select balance_name, reporting_name
174 from pay_balance_types_tl pbt
175 where balance_type_id = cp_balance_type_id
176 and language = USERENV('LANG') ;
177 --
178 lv_balance_name VARCHAR2(240);
179 lv_reporting_name VARCHAR2(240);
180 --
181 ln_found NUMBER;
182 ln_index NUMBER;
183 BEGIN
184
185 ln_found := 0;
186
187 if pay_ac_utility.bal_tbl.count > 0 then
188
189 for i in pay_ac_utility.bal_tbl.first .. pay_ac_utility.bal_tbl.last
190 loop
191
192 if pay_ac_utility.bal_tbl(i).bal_type_id = p_balance_type_id then
193
194 lv_balance_name := pay_ac_utility.bal_tbl(i).bal_name;
195 lv_reporting_name := pay_ac_utility.bal_tbl(i).bal_rep_name;
196 ln_found := 1;
197
198 end if;
199
200 end loop;
201
202 end if;
203
204 if ln_found = 0 then
205
206 open csr_balance(p_balance_type_id);
207 fetch csr_balance into lv_balance_name, lv_reporting_name;
208 close csr_balance;
209
210 ln_index := pay_ac_utility.bal_tbl.count;
211
212 pay_ac_utility.bal_tbl(ln_index).bal_type_id := p_balance_type_id;
213 pay_ac_utility.bal_tbl(ln_index).bal_name := lv_balance_name;
214 pay_ac_utility.bal_tbl(ln_index).bal_rep_name := lv_reporting_name;
215
216 end if;
217
218 if p_desc_type = 'R' then
219 return lv_reporting_name;
220 else
221 return lv_balance_name;
222 end if;
223
224 END get_bal_or_rep_name;
225
226 /*********************************************************************
227 ** Name : get_balance_name
228 ** Purpose : This function returns translated value of the balance
229 ** name.
230 ** Arguments : IN Parameters
231 ** p_balance_type_id -> Balance Type ID
232 ** Notes :
233 *********************************************************************/
234
235 FUNCTION get_balance_name (p_balance_type_id IN NUMBER)
236 RETURN VARCHAR2 IS
237 BEGIN
238 return get_bal_or_rep_name(p_balance_type_id,'B');
239 END get_balance_name;
240
241 /**********************************************************************
242 ** Name : get_bal_reporting_name
243 ** Purpose : This function returns translated value of reporting
244 ** name of the balance.
245 ** Arguments : IN Parameters
246 ** p_balance_type_id -> Balance Type ID
247 ** Notes :
248 **********************************************************************/
249
250 FUNCTION get_bal_reporting_name (p_balance_type_id IN NUMBER)
251 RETURN VARCHAR2 IS
252 BEGIN
253 return get_bal_or_rep_name(p_balance_type_id,'R');
254 END get_bal_reporting_name;
255
256 /**********************************************************************
257 ** Name : get_value
258 ** Purpose : This function returns balance value
259 **
260 ** Arguments : IN Parameters
261 ** p_balance_type_id -> Balance Type ID
262 ** p_dimension_name -> Dimension Name or
263 ** database_item_suffix
264 ** p_bus_grp_id -> Business Group ID
265 ** p_legislation_cd -> Legislation Code
266 ** p_asg_act_id -> Assignment Action ID
267 ** p_tax_unit_id -> Tax Unit ID
268 ** p_date_paid -> Date Paid
269 ** Notes :
270 **********************************************************************/
271
272 FUNCTION get_value (p_balance_type_id IN NUMBER
273 ,p_dimension_name IN VARCHAR2
274 ,p_bus_grp_id IN NUMBER
275 ,p_legislation_cd IN VARCHAR2
276 ,p_asg_act_id IN NUMBER
277 ,p_tax_unit_id IN NUMBER
278 ,p_date_paid IN DATE)
279 RETURN NUMBER IS
280
281 ln_defined_balance_id NUMBER;
282 ln_value NUMBER;
283
284 BEGIN
285
286 hr_utility.trace('Entering pay_ac_utility.get_value with Bal Type ID');
287 hr_utility.trace('p_balance_type_id: '||p_balance_type_id);
288 hr_utility.trace('p_dimension_name: '||p_dimension_name);
289 hr_utility.trace('p_bus_grp_id: '||p_bus_grp_id);
290 hr_utility.trace('p_legislation_cd: '||p_legislation_cd);
291 hr_utility.trace('p_tax_unit_id: '||p_tax_unit_id);
292 hr_utility.trace('p_date_paid: '||p_date_paid);
293
294 ln_defined_balance_id := get_defined_balance_id (p_balance_type_id
295 ,p_dimension_name
296 ,p_bus_grp_id
297 ,p_legislation_cd);
298
299 hr_utility.trace('ln_defined_balance_id: '||ln_defined_balance_id);
300
301 if ln_defined_balance_id <> 0 then
302 ln_value := pay_balance_pkg.get_value
303 (p_defined_balance_id => ln_defined_balance_id
304 ,p_assignment_action_id => p_asg_act_id
305 ,p_tax_unit_id => p_tax_unit_id
306 ,p_jurisdiction_code => NULL
307 ,p_source_id => NULL
308 ,p_tax_group => NULL
309 ,p_date_earned => p_date_paid);
310 else
311 ln_value := NULL;
312 end if;
313
314 hr_utility.trace('ln_value: '||ln_value);
315 hr_utility.trace('Leaving pay_ac_utility.get_value with Bal Type ID');
316
317 return ln_value;
318
319 END get_value;
320
321 FUNCTION get_value (p_balance_name IN VARCHAR2
322 ,p_dimension_name IN VARCHAR2
323 ,p_bus_grp_id IN NUMBER
324 ,p_legislation_cd IN VARCHAR2
325 ,p_asg_act_id IN NUMBER
326 ,p_tax_unit_id IN NUMBER
327 ,p_date_paid IN DATE)
328 RETURN NUMBER IS
329
330 ln_balance_type_id NUMBER;
331 ln_value NUMBER;
332 BEGIN
333
334 hr_utility.trace('Entering pay_ac_utility.get_value with Bal Type Name');
335 hr_utility.trace('p_balance_name: '||p_balance_name);
336 hr_utility.trace('p_dimension_name: '||p_dimension_name);
337 hr_utility.trace('p_bus_grp_id: '||p_bus_grp_id);
338 hr_utility.trace('p_legislation_cd: '||p_legislation_cd);
339 hr_utility.trace('p_tax_unit_id: '||p_tax_unit_id);
340 hr_utility.trace('p_date_paid: '||p_date_paid);
341
342 ln_balance_type_id := get_balance_type_id ( p_balance_name
343 , p_bus_grp_id
344 , p_legislation_cd);
345
346 hr_utility.trace('ln_balance_type_id: '||ln_balance_type_id);
347
348 ln_value := get_value(p_balance_type_id => ln_balance_type_id
349 ,p_dimension_name => p_dimension_name
350 ,p_bus_grp_id => p_bus_grp_id
351 ,p_legislation_cd => p_legislation_cd
352 ,p_asg_act_id => p_asg_act_id
353 ,p_tax_unit_id => p_tax_unit_id
354 ,p_date_paid => p_date_paid);
355
356 hr_utility.trace('ln_value: '||ln_value);
357 hr_utility.trace('Leaving pay_ac_utility.get_value with Bal Type Name');
358
359 return ln_value;
360
361 END get_value;
362
363 /**************************************************************************
364 ** Function : range_person_on
365 ** Arguments: p_report_type
366 ** p_report_format
367 ** p_report_qualifier
368 ** p_report_category
369 ** Returns : Returns true if the range_person performance enhancement is
370 ** enabled for the process.
371 **************************************************************************/
372 FUNCTION range_person_on(p_report_type in varchar2
373 ,p_report_format in varchar2
374 ,p_report_qualifier in varchar2
375 ,p_report_category in varchar2) RETURN BOOLEAN
376 IS
377
378 CURSOR csr_action_parameter is
379 select parameter_value
380 from pay_action_parameters
381 where parameter_name = 'RANGE_PERSON_ID';
382
383 CURSOR csr_range_format_param is
384 select par.parameter_value
385 from pay_report_format_parameters par,
386 pay_report_format_mappings_f map
387 where map.report_format_mapping_id = par.report_format_mapping_id
388 and map.report_type = p_report_type
389 and map.report_format = p_report_format
390 and map.report_qualifier = p_report_qualifier
391 and map.report_category = p_report_category
392 and par.parameter_name = 'RANGE_PERSON_ID';
393
394 lb_return boolean;
395 lv_action_param_val varchar2(30);
396 lv_report_param_val varchar2(30);
397
398 BEGIN
399 hr_utility.set_location('range_person_on',10);
400
401 open csr_action_parameter;
402 fetch csr_action_parameter into lv_action_param_val;
403 close csr_action_parameter;
404
405 hr_utility.set_location('range_person_on',20);
406 open csr_range_format_param;
410 hr_utility.set_location('range_person_on',30);
407 fetch csr_range_format_param into lv_report_param_val;
408 close csr_range_format_param;
409
411
412 IF nvl(lv_action_param_val,'N') = 'Y' AND
413 nvl(lv_report_param_val,'N') = 'Y' THEN
414 lb_return := TRUE;
415 hr_utility.trace('Range Person = True');
416 ELSE
417 lb_return := FALSE;
418 END IF;
419
420 RETURN lb_return;
421
422 END range_person_on;
423
424 /**************************************************************************
425 ** Function : get_geocode
426 ** Arguments: p_state_abbrev
427 ** p_county_name
428 ** p_city_name
429 ** p_zip_code
430 ** Returns : Returns Vertex geocode. The function will currently return
431 ** 00-000-0000 for Canadian Cities
432 **************************************************************************/
433 FUNCTION get_geocode(p_state_abbrev in VARCHAR2
434 ,p_county_name in VARCHAR2
435 ,p_city_name in VARCHAR2
436 ,p_zip_code in VARCHAR2)
437 RETURN VARCHAR2
438 IS
439 cursor c_state_code(cp_state_abbrev in varchar2) is
440 select state_code || '-000-0000'
441 from pay_us_states
442 where state_abbrev = cp_state_abbrev;
443
444 cursor c_county_code(cp_state_abbrev in varchar2
445 ,cp_county_name in varchar2) is
446 select puc.state_code || '-' || puc.county_code || '-0000'
447 from pay_us_states pus,
448 pay_us_counties puc
449 where pus.state_abbrev = cp_state_abbrev
450 and puc.state_code = pus.state_code
451 and puc.county_name = cp_county_name;
452
453 lv_geocode VARCHAR2(11);
454 lv_sql_geocode VARCHAR2(11);
455 BEGIN
456 lv_geocode := '00-000-0000';
457
458 if p_state_abbrev is not null and
459 p_state_abbrev <> 'CN' and
460 p_county_name is null and
461 p_city_name is null and
462 p_zip_code is null then
463 open c_state_code(p_state_abbrev);
464 fetch c_state_code into lv_sql_geocode;
465 close c_state_code;
466
467 lv_geocode := nvl(lv_sql_geocode, lv_geocode);
468
469 elsif p_state_abbrev is not null and
470 p_state_abbrev <> 'CN' and
471 p_county_name is not null and
472 p_city_name is null and
473 p_zip_code is null then
474 open c_county_code(p_state_abbrev
475 ,p_county_name);
476 fetch c_county_code into lv_sql_geocode;
477 close c_county_code;
478
479 lv_geocode := nvl(lv_sql_geocode, lv_geocode);
480 else
481 lv_geocode := hr_us_ff_udfs.addr_val(p_state_abbrev => p_state_abbrev
482 ,p_county_name => p_county_name
483 ,p_city_name => p_city_name
484 ,p_zip_code => p_zip_code);
485
486 end if;
487
488 return (lv_geocode);
489 END get_geocode;
490
491 /****************************************************************************
492 Name : print_lob
493 Description : This procedure prints contents of LOB passed as parameter.
494 *****************************************************************************/
495
496 PROCEDURE print_lob(p_blob BLOB) IS
497 ln_offset number;
498 ln_amount number;
499 lr_buf RAW(2000);
500 BEGIN
501 ln_offset := 1;
502 ln_amount := 2000;
503 hr_utility.trace('BLOB contents: -');
504 LOOP
505 dbms_lob.read(
506 p_blob,
507 ln_amount,
508 ln_offset,
509 lr_buf);
510 ln_amount := 2000;
511 ln_offset := ln_offset + ln_amount;
512 hr_utility.trace(utl_raw.cast_to_varchar2(lr_buf));
513 END LOOP;
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516 hr_utility.trace('BLOB contents end.');
517 END print_lob;
518
519 end pay_ac_utility;
[Home] [Help]