[Home] [Help]
PACKAGE BODY: APPS.HR_PAY_BASIS
Source
1 PACKAGE BODY hr_pay_basis AS
2 /* $Header: pepbasis.pkb 120.1 2006/01/06 10:06:26 rthiagar noship $ */
3 /*
4 ************************************************************************
5 * *
6 *Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved*
7 ************************************************************************ */
8 /*
9 Name : hr_pay_basis (BODY)
10
11 Description : This package declares procedures required to
12 INSERT, UPDATE and DELETE pay bases:
13
14 PER_PAY_BASES
15 Change List
16 -----------
17
18 Version Date Author ER/CR No. Description of Change
19 ------------------------------------------------------------
20 80.0 11-NOV-1993 msingh Date Created
21 80.1 20-DEC-1993 msingh G311 chk_duplicate_element and
22 chk_input_val_rate_uk take into
23 account template elements
24 spanning business groups
25 70.1 23-NOV-1993 rfine Suppressed index on business_group_id
26 70.2 01-MAR-1994 gpaytonm Removed reference to bg_id in
27 chk_input_val_rate_uk
28 70.4 20-NOV-1996 fshojaas The bg_id was added to the
29 chk_input_val_rate_uk.
30 This change was done to fix bug #412780.
31 115.2 16-Sep-2000 mmillmor 1385192 Added element_type_id output and
32 translated element and input value
33 115.3 09-Dec-2002 pkakar Added nocopy to parameters
34 115.4 05-Jan-2006 rthiagar 4894015 Changed the use of per_assignments_f
35 to per_all_assignments_f in
36 chk_basis_assignment.
37 --------------------------------------------------------------- */
38 --
39 FUNCTION generate_unique_id RETURN NUMBER IS
40 --
41 v_pay_basis_id NUMBER;
42 --
43 Begin
44 hr_utility.set_location('hr_pay_basis.generate_unique_id',1);
45 Begin
46 select per_pay_bases_s.nextval
47 into v_pay_basis_id
48 from sys.dual;
49 --
50 exception
51 when NO_DATA_FOUND then
52 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
53 hr_utility.set_message_token('PROCEDURE','hr_salary_data',1 );
54 hr_utility.set_message_token('STEP','1');
55 hr_utility.raise_error;
56 End;
57 --
58 --
59 return v_pay_basis_id;
60 --
61 end generate_unique_id;
62 --
63 -----------------------------------------------------------------------
64 PROCEDURE insert_row (p_pay_basis_id IN OUT NOCOPY NUMBER,
65 p_business_group_id NUMBER,
66 p_name VARCHAR2,
67 p_pay_basis VARCHAR2,
68 p_input_value_id NUMBER,
69 p_rate_id NUMBER,
70 p_rate_basis VARCHAR2) IS
71 --
72 --
73 Begin
74 --
75 hr_utility.set_location ('hr_salary_date.insert_pay_basis',1);
76 --
77 Begin
78 --
79 p_pay_basis_id := generate_unique_id;
80 --
81 -- insert row
82 --
83 INSERT INTO PER_PAY_BASES(pay_basis_id,
84 business_group_id,
85 name,
86 pay_basis,
87 input_value_id,
88 rate_id,
89 rate_basis,
90 last_update_date,
91 last_updated_by,
92 last_update_login,
93 created_by,
94 creation_date)
95 VALUES (p_pay_basis_id ,
96 p_business_group_id,
97 p_name,
98 p_pay_basis,
99 p_input_value_id,
100 p_rate_id,
101 p_rate_basis,
102 trunc(sysdate),
103 -1,
104 -1,
105 -1,
106 trunc(sysdate));
107 --
108 exception
109 when NO_DATA_FOUND then
110 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
111 hr_utility.set_message_token('PROCEDURE','hr_pay_basis.insert_row',1 );
112 hr_utility.set_message_token('STEP','1');
113 hr_utility.raise_error;
114 End;
115 End insert_row;
116 --
117 -------------------------------------------------------------------
118 PROCEDURE chk_name_uniqueness
119 (p_business_group_id IN NUMBER
120 ,p_name IN VARCHAR2
121 ,p_row_id IN VARCHAR2 DEFAULT NULL
122 ) IS
123 -----------------------------------------------------------
124 -- DECLARE THE LOCAL VARIABLES
125 -----------------------------------------------------------
126 duplicate VARCHAR2(1) := 'N';
127 --
128 BEGIN
129 --
130 -- hr_utility.set_location('hr_pay_basis.chk_name_uniqueness',1);
131 --
132 BEGIN
133 --
134 SELECT 'Y'
135 INTO duplicate
136 FROM sys.dual
137 WHERE EXISTS
138 (select 'Y'
139 from per_pay_bases
140 where upper(p_name) = upper(name)
141 and business_group_id + 0 = p_business_group_id
142 and (p_row_id <> rowid
143 or p_row_id is null)
144 );
145 --
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN NULL;
148 --
149 END;
150 if duplicate = 'Y' then
151 hr_utility.set_message(801 ,'HR_13017_SAL_BASIS_DUP_NAME');
152 hr_utility.raise_error;
153 end if;
154 --
155 END chk_name_uniqueness;
156 --
157 -------------------------------------------------------------------------------
158 --
159 PROCEDURE chk_input_val_rate_uk
160 --
161 (
162 p_input_value_id IN NUMBER
163 ,p_rate_id IN NUMBER DEFAULT NULL
164 ,p_row_id IN VARCHAR2 DEFAULT NULL
165 ,p_business_group_id IN NUMBER
166 )
167 IS
168 --
169 duplicate VARCHAR2(1) := 'N';
170 --
171 BEGIN
172 --
173 -- hr_utility.set_location('hr_pay_basis.chk_input_val_rate_uk',1);
174 --
175 BEGIN
176 --
177 SELECT 'Y'
178 INTO duplicate
179 FROM sys.dual
180 WHERE EXISTS
181 (select 'Y'
182 from per_pay_bases
183 where input_value_id = p_input_value_id
184 and nvl(p_rate_id,-1) = nvl(rate_id,-1)
185 and p_business_group_id = business_group_id
186 and (p_row_id <> rowid
187 or p_row_id IS NULL)
188 );
189 --
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN NULL;
192 END;
193 --
194 if duplicate = 'Y' then
195 hr_utility.set_message(801,'HR_13018_SAL_IV_RATE_DUP');
196 hr_utility.raise_error;
197 end if;
198 --
199 END chk_input_val_rate_uk;
200 --
201 ----------------------------------------------------------------------------
202 --
203 FUNCTION chk_duplicate_element
204 (
205 p_element_type_id IN NUMBER
206 ,p_row_id IN VARCHAR2
207 ,p_business_group_id IN NUMBER
208 ) RETURN BOOLEAN IS
209 -----------------------------------------------------------
210 -- DECLARE THE LOCAL VARIABLES
211 -----------------------------------------------------------
212 --
213 v_validation_chk VARCHAR2(1);
214 --
215 BEGIN
216 --
217 v_validation_chk := 'N';
218 -- hr_utility.set_location('hr_pay_basis.chk_duplicate_element',1);
219 --
220 BEGIN
221 --
222 SELECT 'Y'
223 INTO v_validation_chk
224 FROM sys.dual
225 WHERE EXISTS
226 (select 'Y'
227 from per_pay_bases ppb,
228 pay_input_values_f piv
229 where piv.element_type_id = p_element_type_id
230 and ppb.input_value_id = piv.input_value_id
231 and (p_row_id <> ppb.rowid
232 or p_row_id is null)
233 and ppb.business_group_id + 0 = p_business_group_id
234 );
235 --
236 EXCEPTION
237 WHEN NO_DATA_FOUND THEN NULL;
238 END;
239 --
240 RETURN (v_validation_chk = 'N');
241 --
242 END chk_duplicate_element;
243 --
244 -----------------------------------------------------------------------------
245 --
246 PROCEDURE chk_element_entry(
247 p_input_value_id IN NUMBER
248 ) IS
249 -----------------------------------------------------------
250 -- DECLARE THE LOCAL VARIABLES
251 -----------------------------------------------------------
252 --
253 v_validation_chk VARCHAR2(1);
254 --
255 BEGIN
256 --
257 v_validation_chk := 'N';
258 -- hr_utility.set_location('hr_pay_basis.chk_element_entry',1);
259 --
260 --
261 BEGIN
262 --
263 SELECT 'Y'
264 INTO v_validation_chk
265 FROM sys.dual
266 WHERE EXISTS
267 (select 'Y'
268 from pay_element_entry_values_f pev
269 where pev.input_value_id = p_input_value_id
270 );
271 --
272 EXCEPTION
273 --
274 WHEN NO_DATA_FOUND THEN NULL;
275 --
276 END;
277 --
278 --
279 if v_validation_chk = 'Y'
280 then
281 hr_utility.set_message(801,'HR_13019_SAL_ENTRY_EXISTS');
282 hr_utility.raise_error;
283 end if;
284 --
285 END chk_element_entry;
286 --
287 -----------------------------------------------------------------------------
288 Procedure chk_basis_assignment
289 (
290 p_pay_basis_id IN NUMBER
291 ) IS
292 -----------------------------------------------------------
293 -- DECLARE THE LOCAL VARIABLES
294 -----------------------------------------------------------
295 --
296 v_validation_chk VARCHAR2(1);
297 --
298 BEGIN
299 --
300 v_validation_chk := 'N';
301 hr_utility.set_location('hr_pay_basis.chk_basis_assignment',1);
302 --
303 --
304 BEGIN
305 --
306 SELECT 'Y'
307 INTO v_validation_chk
308 FROM sys.dual
309 WHERE EXISTS
310 (select 'Y'
311 from per_all_assignments_f ass
312 where ass.pay_basis_id = p_pay_basis_id
313 );
314 --
315 EXCEPTION
316 --
317 WHEN NO_DATA_FOUND THEN NULL;
318 --
319 END;
320 --
321 --
322 if v_validation_chk = 'Y'
323 then
324 hr_utility.set_message(801,'HR_13020_SAL_ASG_EXISTS');
325 hr_utility.raise_error;
326 end if;
327 --
328 END chk_basis_assignment;
329 --
330 ----------------------------------------------------------------------------
331 --
332 Function populate_basis (p_basis_code IN VARCHAR2)
333 return VARCHAR2 IS
334 --
335 v_basis_meaning VARCHAR2(80);
336 --
337 Begin
338 --
339 hr_utility.set_location('hr_pay_basis.populate_basis',1);
340 --
341 Begin
342 select hlu.meaning into v_basis_meaning
343 from hr_lookups hlu
344 where hlu.lookup_code = p_basis_code
345 and hlu.lookup_type = 'PAY_BASIS';
346 --
347 EXCEPTION
348 --
349 WHEN NO_DATA_FOUND THEN
350 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
351 hr_utility.set_message_token('PROCEDURE',
352 'hr_pay.basis.populate_basis');
353 hr_utility.set_message_token('STEP','1');
354 hr_utility.raise_error;
355 end;
356 --
357 RETURN v_basis_meaning;
358 --
359 End populate_basis;
360 -----------------------------------------------------------------------------
361 Procedure populate_element_iv_name (p_input_value_id IN NUMBER,
362 p_session_date IN DATE,
363 p_iv_name OUT NOCOPY VARCHAR2,
364 p_element_type_id OUT NOCOPY VARCHAR2,
365 p_element_name OUT NOCOPY VARCHAR2)
366 IS
367 --
368 --
369 Begin
370 --
371 hr_utility.set_location ('hr_pay_basis.populate_element_iv_name',1);
372 --
373 Begin
374 --
375 select pivtl.name,
376 pettl.element_name,
377 pet.element_type_id
378 into p_iv_name,
379 p_element_name,
380 p_element_type_id
381 from pay_input_values_f piv,
382 pay_input_values_f_tl pivtl,
383 pay_element_types_f pet,
384 pay_element_types_f_tl pettl
385 where pet.element_type_id = piv.element_type_id
386 and pet.element_type_id = pettl.element_type_id
387 and p_session_date between pet.effective_start_date
388 and pet.effective_end_date
389 and piv.input_value_id = p_input_value_id
390 and pivtl.input_value_id = p_input_value_id
391 and p_session_date between piv.effective_start_date
392 and piv.effective_end_date
393 and pivtl.language=userenv('LANG')
394 and pettl.language=userenv('LANG');
395 --
396 EXCEPTION
397 --
398 WHEN NO_DATA_FOUND THEN
399 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
400 hr_utility.set_message_token('PROCEDURE',
401 'hr_pay.basis.populate_iv_name');
402 hr_utility.set_message_token('STEP','1');
403 hr_utility.raise_error;
404 end;
405 --
406 End populate_element_iv_name;
407 --
408 -----------------------------------------------------------------------
409 Function populate_rate_name (p_rate_id IN NUMBER)
410 RETURN VARCHAR2 Is
411 --
412 v_rate_name VARCHAR2(80);
413 --
414 Begin
415 --
416 hr_utility.set_location('hr_pay_basis.populate_rate_name',1);
417 Begin
418 --
419 select name into v_rate_name
420 from pay_rates
421 where rate_id = p_rate_id;
422 --
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
426 hr_utility.set_message_token('PROCEDURE',
427 'hr_pay.basis.populate_rate_name');
428 hr_utility.set_message_token('STEP','1');
429 hr_utility.raise_error;
430 end;
431 --
432 RETURN v_rate_name;
433 --
434 end populate_rate_name;
435 --
436 ------------------------------------------------------------------------
437 --
438 Procedure populate_iv_valid_dates (
439 p_input_value_id IN NUMBER,
440 p_start_date OUT NOCOPY DATE,
441 p_end_date OUT NOCOPY DATE) IS
442 --
443 Begin
444 --
445 hr_utility.set_location ('hr_pay_basis.populate_valid_dates',1);
446 --
447 Begin
448 --
449 select min(effective_start_date),
450 max(effective_end_date)
451 into p_start_date,
452 p_end_date
453 from pay_input_values_f
454 where input_value_id = p_input_value_id;
455 --
456 exception
457 when NO_DATA_FOUND
458 then
459 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
460 hr_utility.set_message_token('PROCEDURE',
461 'hr_pay.basis.populate_rate_name');
462 hr_utility.set_message_token('STEP','1');
463 hr_utility.raise_error;
464 end;
465 --
466 end populate_iv_valid_dates;
467 --
468 --------------------------------------------------------------------
469 Procedure retreive_fields ( p_session_date IN DATE,
470 p_basis_code IN VARCHAR2,
471 p_basis OUT NOCOPY VARCHAR2,
472 p_element_type_id OUT NOCOPY NUMBER,
473 p_element_name OUT NOCOPY VARCHAR2,
474 p_input_value_id IN NUMBER,
475 p_iv_name OUT NOCOPY VARCHAR2,
476 p_rate_id IN NUMBER,
477 p_rate_name OUT NOCOPY VARCHAR2,
478 p_rate_basis_code IN VARCHAR2,
479 p_rate_basis OUT NOCOPY VARCHAR2,
480 p_start_date OUT NOCOPY DATE,
481 p_end_date OUT NOCOPY DATE) IS
482 --
483 v_valid VARCHAR2(1);
484 --
485 Begin
486 --
487 hr_utility.set_location ('hr_pay_basis.retreive_fields',1);
488 --
489 Begin
490 -- check to see if salary basis reteived is valid for the given session
491 -- date
492 select 'V'
493 into v_valid
494 from sys.dual
495 where exists
496 (select 1
497 from pay_input_values_f
498 where input_value_id = p_input_value_id
499 and p_session_date between effective_start_date
500 and effective_end_date);
501 --
502 exception
503 when no_data_found then
504 hr_utility.set_message(801, 'HR_13027_SAL_BAS_DATE_INVALID');
505 hr_utility.raise_error;
506 end;
507
508 p_basis := populate_basis (p_basis_code);
509 --
510 populate_element_iv_name (p_input_value_id,
511 p_session_date,
512 p_iv_name,
513 p_element_type_id,
514 p_element_name);
515 --
516 populate_iv_valid_dates (p_input_value_id,
517 p_start_date,
518 p_end_date);
519 --
520 if (p_rate_id is not null)
521 then
522 p_rate_name := populate_rate_name (p_rate_id);
523 p_rate_basis := populate_basis (p_rate_basis_code);
524 end if;
525 --
526 End retreive_fields;
527 --
528 ------------------------------------------------------------------------
529 --
530 Procedure validate_insert (p_business_group_id NUMBER,
531 p_row_id VARCHAR2,
532 p_name VARCHAR2,
533 p_input_value_id NUMBER,
534 p_rate_id NUMBER,
535 p_pay_basis_id IN OUT NOCOPY NUMBER) IS
536 --
537 Begin
538 --
539 chk_name_uniqueness (p_business_group_id,
540 p_name,
541 p_row_id);
542 --
543 chk_input_val_rate_uk (p_input_value_id,
544 p_rate_id,
545 p_row_id,
546 p_business_group_id);
547 --
548 p_pay_basis_id := generate_unique_id;
549 --
550 End validate_insert;
551 --
552 -------------------------------------------------------------------------
553 --
554 Procedure validate_update (p_row_id VARCHAR2,
555 p_input_value_id NUMBER,
556 p_pay_basis VARCHAR2) IS
557 --
558 v_pay_basis VARCHAR2 (30);
559 v_input_value_id NUMBER;
560 --
561 CURSOR C IS SELECT pay_basis, input_value_id
562 from per_pay_bases
563 where rowid = p_row_id;
564
565 --
566 Begin
567 hr_utility.set_location ('hr_pay_basis.validate_update',1);
568 --
569 OPEN C;
570 Fetch C into v_pay_basis, v_input_value_id;
571 if (C%NOTFOUND)
572 then
573 close C;
574 raise NO_DATA_FOUND;
575 end if;
576 --
577 close C;
578 --
579 if (v_pay_basis <> p_pay_basis)
580 or (v_input_value_id <> p_input_value_id)
581 then
582 chk_element_entry(v_input_value_id);
583 end if;
584 --
585 exception
586 when no_data_found
587 then
588 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
589 hr_utility.set_message_token('PROCEDURE',
590 'hr_pay_basis.validate_update');
591 hr_utility.set_message_token('STEP','1');
592 hr_utility.raise_error;
593 end;
594
595
596 END hr_pay_basis;