1 package body pay_seed_utl AS
2 /* $Header: pyseedutl.pkb 120.1.12020000.2 2012/11/16 10:28:24 asnell ship $ */
3 -- PLOG logging message cover for hr_utility --
4 procedure PLOG ( p_message IN varchar2 ) is
5 -- output a message to the process log file
6 begin
7 IF hr_utility.debug_enabled then
8 hr_utility.trace('pyseedutl '||p_message);
9 END IF;
10 end plog;
11
12
13 FUNCTION get_creator_id (
14 p_creator_type in VARCHAR2
15 ,p_creator_name1 in VARCHAR2
16 ,p_creator_name2 in VARCHAR2
17 ,p_legislation_code in VARCHAR2 )
18 return NUMBER
19 IS
20 -- Cursor to retrieve element_type_id given name - as name doesnt change date effectivly
21 CURSOR c_element_name_id(p_element_name VARCHAR2,
22 p_legislation_code VARCHAR2) IS
23 select element_type_id
24 from pay_element_types_f
25 where element_name = p_element_name
26 and legislation_code = p_legislation_code
27 and business_group_id IS NULL;
28
29 -- Cursor to retrieve input_value_id given name - as name doesnt change date effectivly
30 CURSOR c_input_name_id(p_element_name VARCHAR2,
31 p_input_value_name VARCHAR2,
32 p_legislation_code VARCHAR2) IS
33 select i.input_value_id
34 from pay_element_types_f e, pay_input_values_f i
35 where e.element_name = p_element_name
36 and i.name = p_input_value_name
37 and i.element_type_id = e.element_type_id
38 and i.legislation_code = p_legislation_code
39 and i.business_group_id IS NULL
40 and e.legislation_code = p_legislation_code
41 and e.business_group_id IS NULL;
42
43 -- Cursor to retrieve defined_balance_id given balance name and dimension name
44 CURSOR c_defined_balance_id(p_balance_name VARCHAR2,
45 p_dimension VARCHAR2,
46 p_legislation_code VARCHAR2) IS
47 select db.defined_balance_id
48 from pay_balance_types b, pay_balance_dimensions d, pay_defined_balances db
49 where b.balance_name = p_balance_name
50 and d.dimension_name = p_dimension
51 and db.balance_type_id = b.balance_type_id
52 and db.balance_dimension_id = d.balance_dimension_id
53 and db.legislation_code = p_legislation_code
54 and db.business_group_id IS NULL
55 and b.legislation_code = p_legislation_code
56 and b.business_group_id IS NULL
57 and d.legislation_code = p_legislation_code
58 and d.business_group_id IS NULL;
59
60 -- Cursor to retrieve absence_type_id given name
61 CURSOR c_absence_type_id(p_absence_type_name VARCHAR2) IS
62 select ABSENCE_ATTENDANCE_TYPE_ID
63 from per_absence_attendance_types
64 where name = p_absence_type_name
65 and business_group_id IS NULL;
66
67 -- Cursor to retrieve balance_type_id given name
68 CURSOR c_grade_name_id(p_grade_name VARCHAR2) IS
69 select GRADE_ID
70 from per_grades
71 where name = p_grade_name
72 and business_group_id IS NULL;
73
74 -- Cursor to retrieve balance_type_id given name
75 CURSOR c_global_name_id(p_global_name VARCHAR2,
76 p_legislation_code VARCHAR2) IS
77 select global_id
78 from ff_globals_f
79 where global_name = p_global_name
80 and legislation_code = p_legislation_code
81 and business_group_id IS NULL;
82
83 l_creator_id NUMBER;
84 g_proc_name VARCHAR2(50);
85
86 begin
87
88 g_proc_name := 'PAY_SEED_UTL';
89
90 l_creator_id := null;
91
92 If p_creator_type = 'E' then --{
93 OPEN c_element_name_id(p_creator_name1, p_legislation_code );
94 FETCH c_element_name_id INTO l_creator_id;
95 IF c_element_name_id%NOTFOUND THEN --{
96 CLOSE c_element_name_id;
97 plog(' ERROR ELEMENT_NAME:'||p_creator_name1||' not found ');
98 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
99 hr_utility.set_message_token('PROCEDURE','USER_ENTITY_CREATOR_ID');
100 hr_utility.set_message_token('STEP','FindElementName');
101 hr_utility.raise_error;
102 END IF; --}
103 CLOSE c_element_name_id;
104 END IF; --}
105
106 IF p_creator_type = 'I' then --{
107 OPEN c_input_name_id(p_creator_name1, p_creator_name2, p_legislation_code );
108 FETCH c_input_name_id INTO l_creator_id;
109 IF c_input_name_id%NOTFOUND THEN --{
110 CLOSE c_input_name_id;
111 plog(' ERROR Element Input:'||p_creator_name1||'.'||p_creator_name2||' not found ');
112 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
113 hr_utility.set_message_token('PROCEDURE','USER_ENTITY_CREATOR_ID');
114 hr_utility.set_message_token('STEP','FindElementInput');
115 hr_utility.raise_error;
116 END IF; --}
117 CLOSE c_input_name_id;
118 END IF; --}
119
120 IF p_creator_type = 'B' then --{
121 OPEN c_defined_balance_id(p_creator_name1, p_creator_name2, p_legislation_code );
122 FETCH c_defined_balance_id INTO l_creator_id;
123 IF c_defined_balance_id%NOTFOUND THEN --{
124 CLOSE c_defined_balance_id;
125 plog(' ERROR Balance Dimension :'||p_creator_name1||'.'||p_creator_name2||' not found ');
126 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
127 hr_utility.set_message_token('PROCEDURE','USER_ENTITY_CREATOR_ID');
128 hr_utility.set_message_token('STEP','FindDefinedBalance');
129 hr_utility.raise_error;
130 END IF; --}
131 CLOSE c_defined_balance_id;
132 END IF; --}
133
134 IF p_creator_type = 'RB' then --{
135 OPEN c_defined_balance_id(p_creator_name1, p_creator_name2, p_legislation_code );
136 FETCH c_defined_balance_id INTO l_creator_id;
137 IF c_defined_balance_id%NOTFOUND THEN --{
138 CLOSE c_defined_balance_id;
139 plog(' ERROR Balance Dimension :'||p_creator_name1||'.'||p_creator_name2||' not found ');
140 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
141 hr_utility.set_message_token('PROCEDURE','USER_ENTITY_CREATOR_ID');
142 hr_utility.set_message_token('STEP','FindBalanceDimension');
143 hr_utility.raise_error;
144 END IF; --}
145 CLOSE c_defined_balance_id;
146 END IF; --}
147
148 IF p_creator_type = 'A' then --{
149 OPEN c_absence_type_id(p_creator_name1);
150 FETCH c_absence_type_id INTO l_creator_id;
151 IF c_absence_type_id%NOTFOUND THEN --{
152 CLOSE c_absence_type_id;
153 plog(' ERROR Absence Type:'||p_creator_name1||' not found ');
154 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
155 hr_utility.set_message_token('PROCEDURE','USER_ENTITY_CREATOR_ID');
156 hr_utility.set_message_token('STEP','FindAbsenceType');
157 hr_utility.raise_error;
158 END IF; --}
159 CLOSE c_absence_type_id;
160 END IF; --}
161
162 IF p_creator_type = 'G' then --{
163 OPEN c_grade_name_id(p_creator_name1);
164 FETCH c_grade_name_id INTO l_creator_id;
165 IF c_grade_name_id%NOTFOUND THEN --{
166 CLOSE c_grade_name_id;
167 plog(' ERROR Grade:'||p_creator_name1||' not found ');
168 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
169 hr_utility.set_message_token('PROCEDURE','USER_ENTITY_CREATOR_ID');
170 hr_utility.set_message_token('STEP','FindGrade ');
171 hr_utility.raise_error;
172 END IF; --}
173 CLOSE c_grade_name_id;
174 END IF; --}
175
176 IF p_creator_type = 'S' then --{
177 OPEN c_global_name_id(p_creator_name1, p_legislation_code );
178 FETCH c_global_name_id INTO l_creator_id;
179 IF c_global_name_id%NOTFOUND THEN --{
180 CLOSE c_global_name_id;
181 plog(' ERROR Global Name:'||p_creator_name1||' not found ');
182 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
183 hr_utility.set_message_token('PROCEDURE','USER_ENTITY_CREATOR_ID');
184 hr_utility.set_message_token('STEP','FindFFGlobal');
185 hr_utility.raise_error;
186 END IF; --}
187 CLOSE c_global_name_id;
188 END IF; --}
189
190
191 return l_creator_id;
192
193 END GET_CREATOR_ID;
194
195 FUNCTION get_parameter_value (
196 p_parameter_name in VARCHAR2
197 ,p_value_name1 in VARCHAR2
198 ,p_value_name2 in VARCHAR2
199 ,p_legislation_code in VARCHAR2 )
200 return VARCHAR2
201 IS
202 -- Cursor to convert parameter name into type
203 CURSOR c_parm_type(p_parameter_name VARCHAR2) IS
204 select decode(upper(translate(rtrim(p_PARAMETER_NAME),' ','_'))
205 ,'ABSENCE_TYPE_ID','A'
206 ,'BALANCE_DIMENSION_ID','BD'
207 ,'BALANCE_TYPE_ID','BT'
208 ,'DEFINED_BALANCE_ID','B'
209 ,'ELEMENT_TYPE_ID','E'
210 ,'ID_FLEX_NUMBER','KF'
211 ,'INPUT_VALUE_ID','I'
212 ,'PAYMENT_TYPE_ID','P'
213 ,'USER_ENTITY_ID','UE'
214 , 'OTHER') Creator_type
215 from dual;
216
217 -- Cursor to retrieve element_type_id given name - as name doesnt change date effectivly
218 CURSOR c_element_name_id(p_element_name VARCHAR2,
219 p_legislation_code VARCHAR2) IS
220 select element_type_id
221 from pay_element_types_f
222 where element_name = p_element_name
223 and legislation_code = p_legislation_code
224 and business_group_id IS NULL;
225
226 -- Cursor to retrieve balance_type_id given name
227 CURSOR c_balance_name_id(p_balance_name VARCHAR2,
228 p_legislation_code VARCHAR2) IS
229 select balance_type_id
230 from pay_balance_types
231 where balance_name = p_balance_name
232 and legislation_code = p_legislation_code
233 and business_group_id IS NULL;
234
235 -- Cursor to retrieve balance_dimension_id given name
236 CURSOR c_dimension_name_id(p_dimension_name VARCHAR2,
237 p_legislation_code VARCHAR2) IS
238 select balance_dimension_id
239 from pay_balance_dimensions
240 where dimension_name = p_dimension_name
241 and (legislation_code = p_legislation_code or
242 ( legislation_code is null and p_legislation_code is null ))
243 and business_group_id IS NULL;
244
245 -- Cursor to retrieve input_value_id given name - as name doesnt change date effectivly
246 CURSOR c_input_name_id(p_element_name VARCHAR2,
247 p_input_value_name VARCHAR2,
251 where e.element_name = p_element_name
248 p_legislation_code VARCHAR2) IS
249 select i.input_value_id
250 from pay_element_types_f e, pay_input_values_f i
252 and i.name = p_input_value_name
253 and i.element_type_id = e.element_type_id
254 and i.legislation_code = p_legislation_code
255 and i.business_group_id IS NULL
256 and e.legislation_code = p_legislation_code
257 and e.business_group_id IS NULL;
258
259 -- Cursor to retrieve defined_balance_id given balance name and dimension name
260 CURSOR c_defined_balance_id(p_balance_name VARCHAR2,
261 p_dimension VARCHAR2,
262 p_legislation_code VARCHAR2) IS
263 select db.defined_balance_id
264 from pay_balance_types b, pay_balance_dimensions d, pay_defined_balances db
265 where b.balance_name = p_balance_name
266 and d.dimension_name = p_dimension
267 and db.balance_type_id = b.balance_type_id
268 and db.balance_dimension_id = d.balance_dimension_id
269 and db.legislation_code = p_legislation_code
270 and db.business_group_id IS NULL
271 and b.legislation_code = p_legislation_code
272 and b.business_group_id IS NULL
273 and d.legislation_code = p_legislation_code
274 and d.business_group_id IS NULL;
275
276 -- Cursor to retrieve absence_type_id given name
277 CURSOR c_absence_type_id(p_absence_type_name VARCHAR2) IS
278 select ABSENCE_ATTENDANCE_TYPE_ID
279 from per_absence_attendance_types
280 where name = p_absence_type_name
281 and business_group_id IS NULL;
282
283 -- Cursor to retrieve balance_type_id given name
284 CURSOR c_grade_name_id(p_grade_name VARCHAR2) IS
285 select GRADE_ID
286 from per_grades
287 where name = p_grade_name
288 and business_group_id IS NULL;
289
290 -- Cursor to retrieve payment_type_id given name
291 CURSOR c_payment_name_id(p_payment_type_name VARCHAR2,
292 p_legislation_code VARCHAR2) IS
293 select payment_type_id
294 from pay_payment_types
295 where payment_type_name = p_payment_type_name
296 and (territory_code = p_legislation_code or
297 ( territory_code is null and p_legislation_code is null ));
298
299 -- Cursor to retrieve user_entity_id given name
300 CURSOR c_user_entity_name_id(p_user_entity_name VARCHAR2,
301 p_legislation_code VARCHAR2) IS
302 select user_entity_id
303 from ff_user_entities
304 where user_entity_name = p_user_entity_name
305 and (legislation_code = p_legislation_code or
306 ( legislation_code is null and p_legislation_code is null ))
307 and business_group_id IS NULL;
308
309 l_parameter_value NUMBER;
310 l_parameter_value_char VARCHAR2(80);
311 l_parm_type VARCHAR2(5);
312 g_proc_name VARCHAR2(50);
313
314 begin
315
316 g_proc_name := 'PAY_SEED_UTL';
317
318 l_parameter_value := null;
319
320 OPEN c_parm_type(p_parameter_name);
321 FETCH c_parm_type into l_parm_type;
322 CLOSE c_parm_type;
323
324 If l_parm_type = 'E' then --{
325 OPEN c_element_name_id(p_value_name1, p_legislation_code );
326 FETCH c_element_name_id INTO l_parameter_value;
327 IF c_element_name_id%NOTFOUND THEN --{
328 CLOSE c_element_name_id;
329 plog(' ERROR ELEMENT_NAME:'||p_value_name1||' not found ');
330 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
331 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
332 hr_utility.set_message_token('STEP','FindElementName');
333 hr_utility.raise_error;
334 END IF; --}
335 CLOSE c_element_name_id;
336 END IF; --}
337
338 IF l_parm_type = 'I' then --{
339 OPEN c_input_name_id(p_value_name1, p_value_name2, p_legislation_code );
340 FETCH c_input_name_id INTO l_parameter_value;
341 IF c_input_name_id%NOTFOUND THEN --{
342 CLOSE c_input_name_id;
343 plog(' ERROR Element Input:'||p_value_name1||'.'||p_value_name2||' not found ');
344 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
345 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
346 hr_utility.set_message_token('STEP','FindElementInput');
347 hr_utility.raise_error;
348 END IF; --}
349 CLOSE c_input_name_id;
350 END IF; --}
351
352 IF l_parm_type = 'B' then --{
353 OPEN c_defined_balance_id(p_value_name1, p_value_name2, p_legislation_code );
354 FETCH c_defined_balance_id INTO l_parameter_value;
355 IF c_defined_balance_id%NOTFOUND THEN --{
356 CLOSE c_defined_balance_id;
357 plog(' ERROR Balance Dimension :'||p_value_name1||'.'||p_value_name2||' not found ');
358 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
359 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
360 hr_utility.set_message_token('STEP','FindDefinedBalance');
361 hr_utility.raise_error;
362 END IF; --}
363 CLOSE c_defined_balance_id;
364 END IF; --}
365
366 IF l_parm_type = 'A' then --{
367 OPEN c_absence_type_id(p_value_name1);
368 FETCH c_absence_type_id INTO l_parameter_value;
369 IF c_absence_type_id%NOTFOUND THEN --{
370 CLOSE c_absence_type_id;
371 plog(' ERROR Absence Type:'||p_value_name1||' not found ');
375 hr_utility.raise_error;
372 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
373 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
374 hr_utility.set_message_token('STEP','FindAbsenceType');
376 END IF; --}
377 CLOSE c_absence_type_id;
378 END IF; --}
379
380 IF l_parm_type = 'G' then --{
381 OPEN c_grade_name_id(p_value_name1);
382 FETCH c_grade_name_id INTO l_parameter_value;
383 IF c_grade_name_id%NOTFOUND THEN --{
384 CLOSE c_grade_name_id;
385 plog(' ERROR Grade:'||p_value_name1||' not found ');
386 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
387 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
388 hr_utility.set_message_token('STEP','FindGrade ');
389 hr_utility.raise_error;
390 END IF; --}
391 CLOSE c_grade_name_id;
392 END IF; --}
393
394 IF l_parm_type = 'BD' then --{
395 OPEN c_dimension_name_id(p_value_name1,p_legislation_code);
396 FETCH c_dimension_name_id INTO l_parameter_value;
397 IF c_dimension_name_id%NOTFOUND THEN --{
398 CLOSE c_dimension_name_id;
399 plog(' ERROR dimension:'||p_value_name1||' not found ');
400 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
401 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
402 hr_utility.set_message_token('STEP','FindDimension ');
403 hr_utility.raise_error;
404 END IF; --}
405 CLOSE c_dimension_name_id;
406 END IF; --}
407
408 IF l_parm_type = 'BT' then --{
409 OPEN c_balance_name_id(p_value_name1,p_legislation_code);
410 FETCH c_balance_name_id INTO l_parameter_value;
411 IF c_balance_name_id%NOTFOUND THEN --{
412 CLOSE c_balance_name_id;
413 plog(' ERROR balance:'||p_value_name1||' not found ');
414 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
415 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
416 hr_utility.set_message_token('STEP','FindBalance ');
417 hr_utility.raise_error;
418 END IF; --}
419 CLOSE c_balance_name_id;
420 END IF; --}
421
422 IF l_parm_type = 'P' then --{
423 OPEN c_payment_name_id(p_value_name1,p_legislation_code);
424 FETCH c_payment_name_id INTO l_parameter_value;
425 IF c_payment_name_id%NOTFOUND THEN --{
426 CLOSE c_payment_name_id;
427 plog(' ERROR payment type:'||p_value_name1||' not found ');
428 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
429 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
430 hr_utility.set_message_token('STEP','FindPaymentType ');
431 hr_utility.raise_error;
432 END IF; --}
433 CLOSE c_payment_name_id;
434 END IF; --}
435
436 IF l_parm_type = 'UE' then --{
437 OPEN c_user_entity_name_id(p_value_name1,p_legislation_code);
438 FETCH c_user_entity_name_id INTO l_parameter_value;
439 IF c_user_entity_name_id%NOTFOUND THEN --{
440 CLOSE c_user_entity_name_id;
441 plog(' ERROR user_entity:'||p_value_name1||' not found ');
442 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
443 hr_utility.set_message_token('PROCEDURE','PARAMETER_VALUE');
444 hr_utility.set_message_token('STEP','FindUserEntity ');
445 hr_utility.raise_error;
446 END IF; --}
447 CLOSE c_user_entity_name_id;
448 END IF; --}
449
450 IF l_parm_type in ('KF','OTHER') then --{
451 l_parameter_value_char := p_value_name1;
452 ELSE l_parameter_value_char := to_char(l_parameter_value);
453 END IF; --}
454
455 PLOG(' return parameter_value:'||l_parameter_value_char);
456
457 return l_parameter_value_char;
458
459 END GET_PARAMETER_VALUE;
460
461 FUNCTION lookup_balance_name ( p_balance_type_id in number) return varchar2 IS
462 CURSOR csr_balance_name(p_balance_type_id NUMBER) IS
463
464 SELECT balance_name
465 FROM pay_balance_types
466 WHERE balance_type_id = p_balance_type_id;
467
468 l_return VARCHAR2(80);
469
470 begin
471 OPEN csr_balance_name(p_balance_type_id);
472 FETCH csr_balance_name INTO l_return;
473 IF csr_balance_name%NOTFOUND THEN
474 l_return := NULL;
475 hr_utility.trace('PAY_SEED_UTILS.ID_TO_NAME. p_balance_type_id:'||p_balance_type_id||' not found');
476 END IF;
477 close csr_balance_name;
478 return l_return;
479 end lookup_balance_name;
480
481 FUNCTION lookup_balance_id ( p_balance_name in varchar2 , p_legislation_code in varchar2, p_ignore_missing_balance in varchar2 )
482 return varchar2 IS
483 CURSOR csr_balance_id(p_balance_name VARCHAR2,
484 p_legislation_code VARCHAR2) IS
485 SELECT balance_type_id
486 FROM pay_balance_types
487 WHERE balance_name = p_balance_name
488 AND legislation_code = p_legislation_code;
489
490 l_return VARCHAR2(80);
491
492 begin
493 OPEN csr_balance_id(p_balance_name, p_legislation_code);
494 FETCH csr_balance_id INTO l_return;
495 IF csr_balance_id%NOTFOUND THEN
496 l_return := 'BalNotFound';
497 hr_utility.trace('PAY_SEED_UTILS.NAME_TO_ID. p_balance_name:'||p_balance_name||' not found');
498 IF nvl(p_ignore_missing_balance,'N') <> 'Y' then
502 hr_utility.raise_error;
499 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
500 hr_utility.set_message_token('PROCEDURE','BALANCE_NAME');
501 hr_utility.set_message_token('STEP','Find balance:'||p_balance_name);
503 END IF;
504 END IF;
505 close csr_balance_id;
506 return l_return;
507 end lookup_balance_id;
508
509 FUNCTION id_to_name (
510 p_legislation_code in VARCHAR2
511 ,p_context in VARCHAR2
512 ,p_column in VARCHAR2
513 ,p_column_value in VARCHAR2 )
514 return VARCHAR2
515 IS
516 CURSOR csr_element_name(p_element_type_id NUMBER) IS
517 SELECT distinct element_name
518 FROM pay_element_types_f
519 WHERE element_type_id = p_element_type_id;
520
521 CURSOR csr_element_id(p_element_name VARCHAR2,
522 p_legislation_code VARCHAR2) IS
523 SELECT element_type_id
524 FROM pay_element_types_f
525 WHERE element_type_id = p_column_value
526 AND legislation_code = p_legislation_code;
527
528 CURSOR csr_balance_name(p_balance_type_id NUMBER) IS
529 SELECT balance_name
530 FROM pay_balance_types
531 WHERE balance_type_id = p_balance_type_id;
532
533 CURSOR csr_balance_id(p_balance_name VARCHAR2,
534 p_legislation_code VARCHAR2) IS
535 SELECT balance_name
536 FROM pay_balance_types
537 WHERE balance_name = p_balance_name
538 AND legislation_code = p_legislation_code;
539
540 l_return VARCHAR2(80);
541
542
543 BEGIN
544 l_return := p_column_value; -- defualt return the segment value.
545
546 IF p_column_value is not null then --{
547 -- for specific context and columns swap an id for a name
548 IF p_legislation_code = 'US' then -- {
549
550 IF p_context = 'US_EARNINGS' then -- {
551 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
552 l_return := lookup_balance_name(to_number(p_column_value));
553 END IF; --}
554
555 ELSIF p_context = 'US_IMPUTED EARNINGS' then
556 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
557 l_return := lookup_balance_name(to_number(p_column_value));
558 END IF; --}
559
560 ELSIF p_context = 'US_INVOLUNTARY DEDUCTIONS' then
561 IF p_column in ('ELEMENT_INFORMATION8','ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12',
562 'ELEMENT_INFORMATION13',
563 'ELEMENT_INFORMATION14','ELEMENT_INFORMATION15','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
564 l_return := lookup_balance_name(to_number(p_column_value));
565 END IF; --}
566
567 ELSIF p_context = 'US_NON-PAYROLL PAYMENTS' then
568 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
569 l_return := lookup_balance_name(to_number(p_column_value));
570 END IF; --}
571
572 ELSIF p_context = 'US_PRE-TAX DEDUCTIONS' then
573 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13',
574 'ELEMENT_INFORMATION14','ELEMENT_INFORMATION15','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
575 l_return := lookup_balance_name(to_number(p_column_value));
576 END IF; --}
577
578 ELSIF p_context = 'US_PTO ACCRUALS' then
579 IF p_column in ('ELEMENT_INFORMATION10') then -- {
580 l_return := lookup_balance_name(to_number(p_column_value));
581 END IF; --}
582
583 ELSIF p_context = 'US_SUPPLEMENTAL EARNINGS' then
584 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
585 l_return := lookup_balance_name(to_number(p_column_value));
586 END IF; --}
587
588
589 ELSIF p_context = 'US_TAX DEDUCTIONS' then
590 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13',
591 'ELEMENT_INFORMATION14','ELEMENT_INFORMATION15','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17',
592 'ELEMENT_INFORMATION18','ELEMENT_INFORMATION19') then -- {
593 l_return := lookup_balance_name(to_number(p_column_value));
594 END IF; --}
595
596 ELSIF p_context = 'US_VOLUNTARY DEDUCTIONS' then
597 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13',
598 'ELEMENT_INFORMATION14','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
599 l_return := lookup_balance_name(to_number(p_column_value));
600 END IF; --}
601
602
603 END IF; --}
604
605 END IF; --} US
606 IF p_legislation_code = 'CA' then -- {
607
608 IF p_context = 'CA_EARNINGS' then -- {
609 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12') then -- {
610 l_return := lookup_balance_name(to_number(p_column_value));
611 END IF; --}
612
613 ELSIF p_context = 'CA_TAXABLE BENEFITS' then
614 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12') then -- {
615 l_return := lookup_balance_name(to_number(p_column_value));
616 END IF; --}
617
621 l_return := lookup_balance_name(to_number(p_column_value));
618 ELSIF p_context = 'CA_INVOLUNTARY DEDUCTIONS' then
619 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13',
620 'ELEMENT_INFORMATION15','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
622 END IF; --}
623
624 ELSIF p_context = 'CA_NON-PAYROLL PAYMENTS' then
625 IF p_column in ('ELEMENT_INFORMATION10') then -- {
626 l_return := lookup_balance_name(to_number(p_column_value));
627 END IF; --}
628
629 ELSIF p_context = 'CA_PRE-TAX DEDUCTIONS' then
630 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13') then -- {
631 l_return := lookup_balance_name(to_number(p_column_value));
632 END IF; --}
633
634 ELSIF p_context = 'CA_SUPPLEMENTAL EARNINGS' then
635 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12') then -- {
636 l_return := lookup_balance_name(to_number(p_column_value));
637 END IF; --}
638
639 ELSIF p_context = 'CA_VOLUNTARY DEDUCTIONS' then
640 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13') then -- {
641 l_return := lookup_balance_name(to_number(p_column_value));
642 END IF; --}
643
644 END IF; --}
645 END IF; --} CA
646
647 END IF; --}
648 return l_return;
649
650 END ID_TO_NAME;
651
652 FUNCTION name_to_id (
653 p_legislation_code in VARCHAR2
654 ,p_context in VARCHAR2
655 ,p_column in VARCHAR2
656 ,p_column_value in VARCHAR2
657 ,p_ignore_missing_balance in VARCHAR2 default 'N')
658 return VARCHAR2
659 IS
660 CURSOR csr_element_name(p_element_type_id NUMBER) IS
661 SELECT distinct element_name
662 FROM pay_element_types_f
663 WHERE element_type_id = p_element_type_id;
664
665 CURSOR csr_element_id(p_element_name VARCHAR2,
666 p_legislation_code VARCHAR2) IS
667 SELECT element_type_id
668 FROM pay_element_types_f
669 WHERE element_type_id = p_column_value
670 AND legislation_code = p_legislation_code;
671
672 CURSOR csr_balance_name(p_balance_type_id NUMBER) IS
673 SELECT balance_name
674 FROM pay_balance_types
675 WHERE balance_type_id = p_balance_type_id;
676
677 CURSOR csr_balance_id(p_balance_name VARCHAR2,
678 p_legislation_code VARCHAR2) IS
679 SELECT balance_name
680 FROM pay_balance_types
681 WHERE balance_name = p_balance_name
682 AND legislation_code = p_legislation_code;
683
684 l_return VARCHAR2(80);
685
686
687 BEGIN
688 l_return := p_column_value; -- defualt return the segment value.
689
690 IF p_column_value is not null then --{
691 -- for specific context and columns swap an id for a name
692 IF p_legislation_code = 'US' then -- {
693
694 IF p_context = 'US_EARNINGS' then -- {
695 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
696 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
697 END IF; --}
698
699 ELSIF p_context = 'US_IMPUTED EARNINGS' then
700 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
701 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
702 END IF; --}
703
704 ELSIF p_context = 'US_INVOLUNTARY DEDUCTIONS' then
705 IF p_column in ('ELEMENT_INFORMATION8','ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12',
706 'ELEMENT_INFORMATION13',
707 'ELEMENT_INFORMATION14','ELEMENT_INFORMATION15','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
708 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
709 END IF; --}
710
711 ELSIF p_context = 'US_NON-PAYROLL PAYMENTS' then
712 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
713 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
714 END IF; --}
715
716 ELSIF p_context = 'US_PRE-TAX DEDUCTIONS' then
717 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13',
718 'ELEMENT_INFORMATION14','ELEMENT_INFORMATION15','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
719 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
720 END IF; --}
721
722 ELSIF p_context = 'US_PTO ACCRUALS' then
723 IF p_column in ('ELEMENT_INFORMATION10') then -- {
724 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
725 END IF; --}
726
727 ELSIF p_context = 'US_SUPPLEMENTAL EARNINGS' then
728 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
729 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
730 END IF; --}
731
732
733 ELSIF p_context = 'US_TAX DEDUCTIONS' then
737 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
734 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13',
735 'ELEMENT_INFORMATION14','ELEMENT_INFORMATION15','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17',
736 'ELEMENT_INFORMATION18','ELEMENT_INFORMATION19') then -- {
738 END IF; --}
739
740 ELSIF p_context = 'US_VOLUNTARY DEDUCTIONS' then
741 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13',
742 'ELEMENT_INFORMATION14','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
743 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
744 END IF; --}
745
746
747 END IF; --}
748
749 END IF; --} US
750 IF p_legislation_code = 'CA' then -- {
751
752 IF p_context = 'CA_EARNINGS' then -- {
753 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12') then -- {
754 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
755 END IF; --}
756
757 ELSIF p_context = 'CA_TAXABLE BENEFITS' then
758 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12') then -- {
759 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
760 END IF; --}
761
762 ELSIF p_context = 'CA_INVOLUNTARY DEDUCTIONS' then
763 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13',
764 'ELEMENT_INFORMATION15','ELEMENT_INFORMATION16','ELEMENT_INFORMATION17') then -- {
765 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
766 END IF; --}
767
768 ELSIF p_context = 'CA_NON-PAYROLL PAYMENTS' then
769 IF p_column in ('ELEMENT_INFORMATION10') then -- {
770 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
771 END IF; --}
772
773 ELSIF p_context = 'CA_PRE-TAX DEDUCTIONS' then
774 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13') then -- {
775 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
776 END IF; --}
777
778 ELSIF p_context = 'CA_SUPPLEMENTAL EARNINGS' then
779 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION12') then -- {
780 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
781 END IF; --}
782
783 ELSIF p_context = 'CA_VOLUNTARY DEDUCTIONS' then
784 IF p_column in ('ELEMENT_INFORMATION10','ELEMENT_INFORMATION11','ELEMENT_INFORMATION12','ELEMENT_INFORMATION13') then -- {
785 l_return := lookup_balance_id(p_column_value,p_legislation_code,p_ignore_missing_balance);
786 END IF; --}
787 END IF; --}
788 END IF; --} CA
789 END IF; --}
790 return l_return;
791
792 END NAME_TO_ID;
793
794 PROCEDURE UNCOMPILE_FORMULAS ( P_ROUTE_ID in number default null
795 ,P_USER_NAME in VARCHAR2 default null
796 ,P_LEGISLATION_CODE in VARCHAR2 default null
797 ,P_FORMULA_ID in NUMBER default null) is
798 -- need to uncompile formula when a update is made to a route, dbitem or formula
799
800 -- given a route id find formulas using that route and delete compiled info and
801 -- fdi usages for that. Used when updating a route.
802 CURSOR c_formula_id ( p_route_id number) is
803 select /*+ ORDERED
804 INDEX(C FF_USER_ENTITIES_FK1)
805 INDEX(B FF_DATABASE_ITEMS_FK1)
806 INDEX(A FF_FDI_USAGES_F_N50) */
807 distinct a.formula_id
808 from ff_user_entities c,
809 ff_database_items b,
810 ff_fdi_usages_f a
811 where a.item_name = b.user_name
812 and a.usage = 'D'
813 and b.user_entity_id = c.user_entity_id
814 and c.route_id = p_route_id;
815
816
817 cursor c_formulas_using_dbi ( p_user_name varchar2, p_legislation_code varchar2)
818 is
819 -- dbitem can be global or legislation (not user) so checks are limitted to
820 -- seed database items. However user formulas can see core and owning legislation
821 -- code dbitems.
822 select distinct fdi.formula_id from
823 ff_fdi_usages_f fdi
824 ,ff_database_items dbi
825 ,ff_user_entities ue
826 ,ff_formulas_f f
827 where fdi.ITEM_NAME = p_user_name
828 and fdi.USAGE = 'D'
829 and ue.USER_ENTITY_ID = dbi.USER_ENTITY_ID
830 and dbi.USER_NAME = p_user_name
831 and nvl(ue.legislation_code,'z') = nvl(p_legislation_code,'z')
832 and f.formula_id = fdi.formula_id
833 and (( f.legislation_code = p_legislation_code )
834 or ( f.business_group_id in ( select business_group_id from
835 per_business_groups bg
836 where bg.legislation_code = p_legislation_code )));
837
838 BEGIN
839 IF p_route_id is not null then
840 for getrec in c_formula_id(p_route_id ) loop
841 delete from ff_fdi_usages_f where formula_id = getrec.formula_id;
842 delete from ff_compiled_info_f where formula_id = getrec.formula_id;
843 end loop;
844 END IF;
845
846 IF p_user_name is not null then
847 for getrec in c_formulas_using_dbi(p_user_name, p_legislation_code ) loop
848 delete from ff_fdi_usages_f where formula_id = getrec.formula_id;
849 delete from ff_compiled_info_f where formula_id = getrec.formula_id;
850 end loop;
851 END IF;
852
853 IF p_formula_id is not null then
854 delete from ff_fdi_usages_f where formula_id = p_formula_id;
855 delete from ff_compiled_info_f where formula_id = p_formula_id;
856 END IF;
857
858 END UNCOMPILE_FORMULAS;
859
860 END PAY_SEED_UTL;
[Home] [Help]