[Home] [Help]
PACKAGE BODY: APPS.PER_PSV_BUS
Source
1 Package Body per_psv_bus as
2 /* $Header: pepsvrhi.pkb 115.7 2002/12/05 09:55:22 eumenyio noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_psv_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_pos_structure_version_id number default null;
15
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< get_business_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure get_business_group_id
22 (p_position_structure_id in number
23 ,p_business_group_id out nocopy number
24 ) is
25 l_proc varchar2(72) := g_package||'set_security_group_id';
26 --
27 begin
28 --
29 hr_utility.set_location('Entering:'|| l_proc, 10);
30 --
31 select business_group_id
32 into p_business_group_id
33 from per_position_structures
34 where p_position_structure_id = position_structure_id;
35
36 --
37 hr_utility.set_location(' Leaving:'|| l_proc, 20);
38 --
39 exception
40 when no_data_found then
41 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
42 fnd_message.raise_error;
43 end get_business_group_id;
44
45 --
46 -- ---------------------------------------------------------------------------
47 -- |----------------------< set_security_group_id >--------------------------|
48 -- ---------------------------------------------------------------------------
49 --
50 Procedure set_security_group_id
51 (p_pos_structure_version_id in number
52 ) is
53 --
54 -- Declare cursor
55 --
56 cursor csr_sec_grp is
57 select pbg.security_group_id
58 from per_business_groups pbg
59 , per_pos_structure_versions psv
60 where psv.pos_structure_version_id = p_pos_structure_version_id
61 and pbg.business_group_id = psv.business_group_id;
62 --
63 -- Declare local variables
64 --
65 l_security_group_id number;
66 l_proc varchar2(72) := g_package||'set_security_group_id';
67 --
68 begin
69 --
70 hr_utility.set_location('Entering:'|| l_proc, 10);
71 --
72 -- Ensure that all the mandatory parameter are not null
73 --
74 hr_api.mandatory_arg_error
75 (p_api_name => l_proc
76 ,p_argument => 'pos_structure_version_id'
77 ,p_argument_value => p_pos_structure_version_id
78 );
79 --
80 open csr_sec_grp;
81 fetch csr_sec_grp into l_security_group_id;
82 --
83 if csr_sec_grp%notfound then
84 --
85 close csr_sec_grp;
86 --
87 -- The primary key is invalid therefore we must error
88 --
89 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
90 fnd_message.raise_error;
91 --
92 end if;
93 close csr_sec_grp;
94 --
95 -- Set the security_group_id in CLIENT_INFO
96 --
97 hr_api.set_security_group_id
98 (p_security_group_id => l_security_group_id
99 );
100 --
101 hr_utility.set_location(' Leaving:'|| l_proc, 20);
102 --
103 end set_security_group_id;
104 --
105 -- ---------------------------------------------------------------------------
106 -- |---------------------< return_legislation_code >-------------------------|
107 -- ---------------------------------------------------------------------------
108 --
109 Function return_legislation_code
110 (p_pos_structure_version_id in number
111 )
112 Return Varchar2 Is
113 --
114 -- Declare cursor
115 --
116 cursor csr_leg_code is
117 select pbg.legislation_code
118 from per_business_groups pbg
119 , per_pos_structure_versions psv
120 where psv.pos_structure_version_id = p_pos_structure_version_id
121 and pbg.business_group_id = psv.business_group_id;
122 --
123 -- Declare local variables
124 --
125 l_legislation_code varchar2(150);
126 l_proc varchar2(72) := g_package||'return_legislation_code';
127 --
128 Begin
129 --
130 hr_utility.set_location('Entering:'|| l_proc, 10);
131 --
132 -- Ensure that all the mandatory parameter are not null
133 --
134 hr_api.mandatory_arg_error
135 (p_api_name => l_proc
136 ,p_argument => 'pos_structure_version_id'
137 ,p_argument_value => p_pos_structure_version_id
138 );
139 --
140 if ( nvl(per_psv_bus.g_pos_structure_version_id, hr_api.g_number)
141 = p_pos_structure_version_id) then
142 --
143 -- The legislation code has already been found with a previous
144 -- call to this function. Just return the value in the global
145 -- variable.
146 --
147 l_legislation_code := per_psv_bus.g_legislation_code;
148 hr_utility.set_location(l_proc, 20);
149 else
150 --
151 -- The ID is different to the last call to this function
152 -- or this is the first call to this function.
153 --
154 open csr_leg_code;
155 fetch csr_leg_code into l_legislation_code;
156 --
157 if csr_leg_code%notfound then
158 --
159 -- The primary key is invalid therefore we must error
160 --
161 close csr_leg_code;
162 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
163 fnd_message.raise_error;
164 end if;
165 hr_utility.set_location(l_proc,30);
166 --
167 -- Set the global variables so the values are
168 -- available for the next call to this function.
169 --
170 close csr_leg_code;
171 per_psv_bus.g_pos_structure_version_id := p_pos_structure_version_id;
172 per_psv_bus.g_legislation_code := l_legislation_code;
173 end if;
174 hr_utility.set_location(' Leaving:'|| l_proc, 40);
175 return l_legislation_code;
176 end return_legislation_code;
177
178 -- ---------------------------------------------------------------------------
179 -- |---------------------------< copy_hierachy >-----------------------------|
180 -- ---------------------------------------------------------------------------
181 --
182 Procedure copy_hierachy
183 ( p_src_psv_id in number,
184 p_dest_psv_id in number,
185 p_pos_structure_version_id in number
186 ) is
187 l_proc VARCHAR2(72) := g_package||'copy_hierachy';
188 --
189 -- Define cursor for inserts
190 --
191 cursor struct_element is select *
192 from per_pos_structure_elements ose
193 where ose.pos_structure_version_id = p_src_psv_id;
194 --
195 -- Local Variable
196 --
197 ele_record struct_element%ROWTYPE;
198 l_rowid VARCHAR2(20);
199 l_count number(2);
200 begin
201 --
202 hr_utility.set_location('Entering:'|| l_proc, 10);
203 --
204 --
205 hr_api.mandatory_arg_error
206 (p_api_name => l_proc
207 ,p_argument => 'p_src_psv_id'
208 ,p_argument_value => p_src_psv_id
209 );
210 hr_api.mandatory_arg_error
211 (p_api_name => l_proc
212 ,p_argument => 'p_dest_psv_id'
213 ,p_argument_value => p_dest_psv_id
214 );
215
216 select count(*)
217 into l_count
218 from per_pos_structure_versions
219 where pos_structure_version_id in (p_src_psv_id,
220 p_dest_psv_id);
221 if l_count <> 2 then
222 null;
223
224 end if;
225 open struct_element;
226 fetch struct_element into ele_record;
227 loop
228 exit when struct_element%NOTFOUND;
229 INSERT INTO PER_POS_STRUCTURE_ELEMENTS(
230 pos_structure_element_id,
231 business_group_id,
232 parent_position_id,
233 pos_structure_version_id
234 ) VALUES (
235 PER_POS_STRUCTURE_ELEMENTS_S.NEXTVAL,
236 ele_record.Business_Group_Id,
237 ele_record.parent_position_Id,
238 p_pos_structure_version_id
239 );
240 --
241 fetch struct_element into ele_record;
242 end loop;
243 close struct_element;
244 end copy_hierachy;
245
246 -- ---------------------------------------------------------------------------
247 -- |-----------------------------< chk_dates >-------------------------------|
248 -- ---------------------------------------------------------------------------
249 --
250 Procedure chk_dates
251 (p_pos_structure_version_id in number,
252 p_date_from in date,
253 p_date_to in date,
254 p_position_structure_id in number,
255 p_gap_warning out nocopy boolean
256 ) is
257 l_dummy VARCHAR2(5);
258 l_max_end_date DATE;
259 l_min_start_date DATE;
260 l_proc VARCHAR2(72) := g_package||'chk_dates';
261 begin
262 --
263 hr_utility.set_location('Entering:'|| l_proc, 10);
264 --
265 --
266
267 --
268 --
269 hr_utility.set_location(l_proc, 20);
270 --
271 -- Checks that date from is earlier than date to and neither
272 -- are before or after beginning/end of time
273 --
274
275 if p_date_from > p_date_to
276 or p_date_from < hr_api.g_sot
277 or p_date_from > hr_api.g_eot then
278 hr_utility.set_message('801','HR_6021_ALL_START_END_DATE');
279 hr_utility.raise_error;
280 end if;
281
282 -- The code below checks for a gap between different versions
283 --
284 --
285 --
286 p_gap_warning := FALSE;
287 select max(osv.date_to)
288 into l_max_end_date
289 from per_pos_structure_versions osv
290 where osv.date_from < p_Date_From
291 and osv.position_structure_id = p_position_structure_id
292 and osv.pos_structure_Version_id = nvl(p_pos_structure_version_id,-1);
293 --
294 if (l_max_end_date is not null and p_Date_from = (l_max_end_date +1)
295 or (l_max_end_date is null)) then
296 select min(osv.date_from)
297 into l_min_start_date
298 from per_pos_structure_versions osv
299 where osv.position_structure_id = p_position_structure_id
300 and osv.date_from > p_Date_To
301 and osv.pos_structure_Version_id = nvl(p_pos_structure_version_id,-1);
302 --
303 if l_min_start_date is not null and (p_Date_To +1) <> l_min_start_date then
304 p_gap_warning :=TRUE;
305 end if;
306 else
307 p_gap_warning := TRUE;
308 end if;
309 begin
310 --
311 -- Test for overlapping rows
312 --
313 select null
314 into l_dummy
315 from dual
316 where exists
317 (select 1
318 from per_pos_structure_versions osv
319 where osv.date_from <= nvl(p_Date_To, hr_api.g_eot)
320 and nvl(osv.date_to,hr_api.g_eot) >= p_Date_From
321 and osv.position_structure_id = p_position_structure_id
322 and osv.pos_structure_version_id
323 <> nvl(p_pos_structure_version_id,-1));
324 --
325
326 --
327 --
328 hr_utility.set_message('801','HR_6076_PO_POS_OVERLAP');
329 hr_utility.raise_error;
330 --
331
332 end;
333 exception
334 when no_data_found then
335 null;
336 --
337 hr_utility.set_location('Leaving:'||l_proc, 30);
338 --
339
340 end chk_dates;
341 --
342
343 --
344 -- ---------------------------------------------------------------------------
345 -- |------------------------< chk_version_number >---------------------------|
346 -- ---------------------------------------------------------------------------
347 --
348 Procedure chk_version_number
349 (p_pos_structure_version_id in number,
353 l_proc VARCHAR2(72) := g_package||'chk_version_number';
350 p_version_number in number,
351 p_position_structure_id in number
352 ) is
354 --
355 -- Declare cursor
356 --
357 cursor csr_pos_version is
358 select pos_structure_version_id,business_group_id
359 from per_pos_structure_versions
360 where version_number = p_version_number
361 and nvl(p_pos_structure_version_id,-1) <> pos_structure_version_id
362 and p_position_structure_id = position_structure_id;
363 begin
364 --
365 hr_utility.set_location('Entering:'|| l_proc, 10);
366 --
367 --
368 --
369 --
370 hr_utility.set_location(l_proc, 20);
371 --
372 --
373 for Crec in csr_pos_version loop
374 hr_utility.set_message(800, 'PER_7901_SYS_DUPLICATE_RECORDS');
375 hr_utility.raise_error;
376 end loop;
377 --
378 hr_utility.set_location('Leaving:'||l_proc, 30);
379 --
380 end chk_version_number;
381
382 --
383 -- ---------------------------------------------------------------------------
384 -- |----------------------< chk_position_structure_id >----------------------|
385 -- ---------------------------------------------------------------------------
386 --
387 Procedure chk_position_structure_id
388 (p_position_structure_id IN number
389 ) IS
390 l_proc VARCHAR2(72) := g_package||'chk_position_structure_id';
391 cursor csr_pos_str IS
392 select 'N' from dual
393 where not exists (select 'Y' from per_position_structures where position_structure_id = p_position_structure_id);
394 begin
395 --
396 hr_utility.set_location('Entering:'|| l_proc, 10);
397 --
398 --
399 hr_api.mandatory_arg_error
400 (p_api_name => l_proc
401 ,p_argument => 'p_position_structure_id'
402 ,p_argument_value => p_position_structure_id);
403
404 --
405 hr_utility.set_location(l_proc, 20);
406 --
407 --
408 for c1 in csr_pos_Str loop
409 hr_utility.set_message(800, 'HR_51022_HR_INV_PRIMARY_KEY');
410 hr_utility.raise_error;
411 end loop;
412
413 --
414 --
415 hr_utility.set_location('Leaving:'||l_proc, 30);
416 --
417 end chk_position_structure_id;
418
419
420
421 --
422 -- ----------------------------------------------------------------------------
423 -- |-----------------------< chk_non_updateable_args >------------------------|
424 -- ----------------------------------------------------------------------------
425 -- {Start Of Comments}
426 --
427 -- Description:
428 -- This procedure is used to ensure that non updateable attributes have
429 -- not been updated. If an attribute has been updated an error is generated.
430 --
431 -- Pre Conditions:
432 -- g_old_rec has been populated with details of the values currently in
433 -- the database.
434 --
435 -- In Arguments:
436 -- p_rec has been populated with the updated values the user would like the
437 -- record set to.
438 --
439 -- Post Success:
440 -- Processing continues if all the non updateable attributes have not
441 -- changed.
442 --
443 -- Post Failure:
444 -- An application error is raised if any of the non updatable attributes
445 -- have been altered.
446 --
447 -- {End Of Comments}
448 -- ----------------------------------------------------------------------------
449 Procedure chk_non_updateable_args
450 (p_effective_date in date
451 ,p_rec in per_psv_shd.g_rec_type
452 ) IS
453 --
454 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
455 l_error EXCEPTION;
456 l_argument varchar2(30);
457 --
458 Begin
459 --
460 -- Only proceed with the validation if a row exists for the current
461 -- record in the HR Schema.
462 --
463 IF NOT per_psv_shd.api_updating
464 (p_pos_structure_version_id => p_rec.pos_structure_version_id
465 ,p_object_version_number => p_rec.object_version_number
466 ) THEN
467 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
468 fnd_message.set_token('PROCEDURE ', l_proc);
469 fnd_message.set_token('STEP ', '5');
470 fnd_message.raise_error;
471 END IF;
472 --
473 -- EDIT_HERE: Add checks to ensure non-updateable args have
474 -- not been updated.
475 --
476 EXCEPTION
477 WHEN l_error THEN
478 hr_api.argument_changed_error
479 (p_api_name => l_proc
480 ,p_argument => l_argument);
481 WHEN OTHERS THEN
482 RAISE;
483 End chk_non_updateable_args;
484 --
485 -- ----------------------------------------------------------------------------
486 -- |---------------------------< insert_validate >----------------------------|
487 -- ----------------------------------------------------------------------------
488 Procedure insert_validate
489 (p_effective_date in date
490 ,p_rec in per_psv_shd.g_rec_type
491 ,p_gap_warning out nocopy boolean
492 ) is
493 --
494 l_proc varchar2(72) := g_package||'insert_validate';
495 --
496 Begin
500 --
497 hr_utility.set_location('Entering:'||l_proc, 5);
498 --
499 -- Call all supporting business operations
501 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
502
503 chk_position_Structure_id
504 (p_position_structure_id => p_rec.position_structure_id);
505
506 chk_version_number
507 (p_pos_structure_version_id => p_rec.pos_structure_version_id,
508 p_version_number => p_rec.version_number,
509 p_position_structure_id => p_rec.position_structure_id
510 );
511
512 chk_dates
513 (p_pos_structure_version_id => p_rec.pos_structure_version_id,
514 p_date_from => p_rec.date_from,
515 p_date_to => p_rec.date_to,
516 p_position_structure_id => p_rec.position_structure_id,
517 p_gap_warning => p_gap_warning);
518
519 --
520 --
521 hr_utility.set_location(' Leaving:'||l_proc, 10);
522 End insert_validate;
523 --
524 -- ----------------------------------------------------------------------------
525 -- |---------------------------< update_validate >----------------------------|
526 -- ----------------------------------------------------------------------------
527 Procedure update_validate
528 (p_effective_date in date
529 ,p_rec in per_psv_shd.g_rec_type
530 ,p_gap_warning out nocopy boolean ) is
531
532 --
533 l_proc varchar2(72) := g_package||'update_validate';
534 --
535 Begin
536 hr_utility.set_location('Entering:'||l_proc, 5);
537 --
538 -- Call all supporting business operations
539 --
540 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
541 --
542 chk_non_updateable_args
543 (p_effective_date => p_effective_date
544 ,p_rec => p_rec
545 );
546 --
547
548 chk_version_number
549 (p_pos_structure_version_id => p_rec.pos_structure_version_id,
550 p_version_number => p_rec.version_number,
551 p_position_structure_id => p_rec.position_structure_id);
552
553 chk_dates
554 (p_pos_structure_version_id => p_rec.pos_structure_version_id,
555 p_date_from => p_rec.date_from,
556 p_date_to => p_rec.date_to,
557 p_position_structure_id => p_rec.position_structure_id,
558 p_gap_warning => p_gap_warning);
559
560 --
561 hr_utility.set_location(' Leaving:'||l_proc, 10);
562 End update_validate;
563 --
564 -- ----------------------------------------------------------------------------
565 -- |---------------------------< delete_validate >----------------------------|
566 -- ----------------------------------------------------------------------------
567 Procedure delete_validate
568 (p_rec in per_psv_shd.g_rec_type
569 ) is
570 --
571 l_proc varchar2(72) := g_package||'delete_validate';
572 --
573 Begin
574 hr_utility.set_location('Entering:'||l_proc, 5);
575 --
576 -- Call all supporting business operations
577 --
578 hr_utility.set_location(' Leaving:'||l_proc, 10);
579 End delete_validate;
580 --
581 end per_psv_bus;