1 Package Body per_pse_bus as
2 /* $Header: pepserhi.pkb 120.1 2008/07/22 09:16:58 rnemani noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pse_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_element_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_pos_structure_element_id in number
25 --
22 ) is
23 --
24 -- Declare cursor
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , per_pos_structure_elements pse
30 where pse.pos_structure_element_id = p_pos_structure_element_id
31 and pbg.business_group_id = pse.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'pos_structure_element_id'
47 ,p_argument_value => p_pos_structure_element_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_pos_structure_element_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , per_pos_structure_elements pse
90 where pse.pos_structure_element_id = p_pos_structure_element_id
91 and pbg.business_group_id = pse.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'pos_structure_element_id'
107 ,p_argument_value => p_pos_structure_element_id
108 );
109 --
110 if ( nvl(per_pse_bus.g_pos_structure_element_id, hr_api.g_number)
111 = p_pos_structure_element_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := per_pse_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 per_pse_bus.g_pos_structure_element_id:= p_pos_structure_element_id;
142 per_pse_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
177 ) IS
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_rec in per_pse_shd.g_rec_type
178 --
179 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
180 l_error EXCEPTION;
181 l_argument varchar2(30);
182 --
183 Begin
184 --
185 -- Only proceed with the validation if a row exists for the current
186 -- record in the HR Schema.
187 --
188 IF NOT per_pse_shd.api_updating
189 (p_pos_structure_element_id => p_rec.pos_structure_element_id
190 ,p_object_version_number => p_rec.object_version_number
191 ) THEN
192 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
193 fnd_message.set_token('PROCEDURE ', l_proc);
194 fnd_message.set_token('STEP ', '5');
195 fnd_message.raise_error;
196 END IF;
197 --
198 -- EDIT_HERE: Add checks to ensure non-updateable args have
199 -- not been updated.
200 --
201 if nvl(p_rec.business_group_id, hr_api.g_number) <>
202 nvl(per_pse_shd.g_old_rec.business_group_id
203 ,hr_api.g_number
204 ) then
205 l_argument := 'business_group_id';
206 raise l_error;
207 end if;
208 --
209 if nvl(p_rec.pos_structure_version_id, hr_api.g_number) <>
210 nvl(per_pse_shd.g_old_rec.pos_structure_version_id
211 ,hr_api.g_number
212 ) then
213 l_argument := 'pos_structure_version_id';
214 raise l_error;
215 end if;
216 --
217 /* bug no 3888749 starts here
218
219 Removing following check on Position hierarchy so that updation of
220 Position nodes can be done in it. We don't need to put extra check
221 for ensuring not having cycle into position hierarcy because this
222 task is achieved by a constraint PER_POS_STRUCTURE_ELEMENTS_UK2 according
223 to which POS_STRUCTURE_VERSION_ID and SUBORDINATE_POSITION_ID is the unique
224 combination in table PER_POS_STRUCTURE_ELEMENTS.
225
226 if nvl(p_rec.parent_position_id, hr_api.g_number) <>
227 nvl(per_pse_shd.g_old_rec.parent_position_id
228 ,hr_api.g_number
229 ) then
230 l_argument := 'parent_position_id';
231 raise l_error;
232 end if;
233
234 bug no 3888749 ends here */
235 --
236 if nvl(p_rec.subordinate_position_id, hr_api.g_number) <>
237 nvl(per_pse_shd.g_old_rec.subordinate_position_id
238 ,hr_api.g_number
239 ) then
240 l_argument := 'subordinate_position_id';
241 raise l_error;
242 end if;
243 --
244 EXCEPTION
245 WHEN l_error THEN
246 hr_api.argument_changed_error
247 (p_api_name => l_proc
248 ,p_argument => l_argument);
249 WHEN OTHERS THEN
250 RAISE;
251 End chk_non_updateable_args;
252 --
253 -- -------------------------------------------------------------------------------
254 -- |---------------------------< chk_children_exist >----------------------------|
255 -- -------------------------------------------------------------------------------
256 PROCEDURE chk_children_exist
257 (p_pos_structure_version_id in
258 per_pos_structure_elements.pos_structure_version_id%TYPE
259 ,p_Subordinate_position_Id in
260 per_pos_structure_elements.Subordinate_position_Id%TYPE
261 ) is
262 --
263 l_temp VARCHAR2(1);
264 --
265 begin
266 --
267 -- Pre-delete checks for subordinate
268 -- positions in the hierarchy.
269 --
270 select null
271 into l_temp
272 from sys.dual
273 where exists(select 1
274 from per_pos_structure_elements pse
275 where pse.parent_position_id = p_Subordinate_position_Id
276 and pse.pos_structure_version_id = p_Pos_Structure_version_Id);
277 --
278 hr_utility.set_message('801','HR_6915_POS_DEL_FIRST');
279 hr_utility.raise_error;
280 --
281 exception
282 when no_data_found then
283 null;
284 end chk_children_exist;
285 --
286 -- -----------------------------------------------------------------------------
287 -- |---------------------------< chk_security_pos >----------------------------|
288 -- -----------------------------------------------------------------------------
289 PROCEDURE chk_security_pos(p_Subordinate_position_Id NUMBER
290 ,p_Business_Group_Id NUMBER
291 ,p_Pos_Structure_version_Id NUMBER) IS
292 l_dummy VARCHAR2(1);
293 begin
294 select null
295 into l_dummy
296 from sys.dual
297 where exists(select 1
298 from per_security_profiles psp
299 where psp.business_group_id + 0 = p_Business_Group_Id
300 and psp.position_id = p_Subordinate_position_Id
301 and psp.position_structure_id = (select psv.position_structure_id
302 from per_pos_structure_versions psv
303 where psv.Pos_Structure_version_Id
304 = p_Pos_Structure_version_Id)
305 );
306 --
307 hr_utility.set_message(801,'PAY_7694_PER_NO_DEL_STRUCTURE');
308 hr_utility.raise_error;
309 --
310 exception
311 when no_data_found then
312 null;
313 end chk_security_pos;
314
315
316 --
320 -- Description:
317 -- -----------------------------------------------------------------------------
318 -- |--------------------< chk_pos_structure_version_id >------------------------|
319 -- -----------------------------------------------------------------------------
321 -- Validates that the mandatory structure version id is supplied and that it
322 -- exists in per_pos_structure_versions for the business group on the
323 -- effective date (Insert Only).
324 --
325 -- Pre-conditions:
326 -- Business group is valid
327 --
328 -- In Arguments:
329 -- p_pos_structure_element_id
330 -- p_business_group_id
331 -- p_pos_structure_version_id
332 -- p_effective_date
333 --
334 -- Post Success:
335 -- If a row does exist in per_pos_structure_versions for the given id and
336 -- business group then processing continues.
337 --
338 -- Post Failure:
339 -- Processing stops and an error is raised.
340 --
341 -- Access Status:
342 -- Internal Table Handler Use Only.
343 --
344 PROCEDURE chk_pos_structure_version_id(p_pos_structure_element_id in NUMBER
345 ,p_business_group_id in NUMBER
346 ,p_pos_structure_version_id in NUMBER
347 ,p_effective_date in DATE) IS
348
349 CURSOR csr_pos_structure_versions IS
350 SELECT 'X'
351 from per_pos_structure_versions psv
352 where psv.pos_structure_version_id = p_pos_structure_version_id
353 and psv.business_group_id = p_business_group_id
354 and p_effective_date between psv.DATE_FROM
355 and nvl(psv.DATE_TO,hr_general.end_of_time);
356
357 l_dummy varchar2(10);
358 l_proc varchar2(100) := g_package||'chk_pos_structure_version_id';
359
360 begin
361 hr_utility.set_location('Entering:'||l_proc, 1);
362 --
363 -- Check mandatory parameters have been set
364 --
365 hr_api.mandatory_arg_error
366 (p_api_name => l_proc
367 ,p_argument => 'business_group_id'
368 ,p_argument_value => p_business_group_id
369 );
370 hr_api.mandatory_arg_error
371 (p_api_name => l_proc
372 ,p_argument => 'p_effective_date'
373 ,p_argument_value => p_effective_date
374 );
375 --
376 --
377 if p_pos_structure_version_id is null then
378 hr_utility.set_message(800,'HR_289723_POS_VER_NULL');
379 hr_utility.raise_error;
380 else
381 --
382 hr_utility.set_location(l_proc, 5);
383 --
384 -- Only proceed with validation if :
385 -- a) Inserting (pos_structure_version_id is non updateable)
386 --
387 if (p_pos_structure_element_id is null) then
388
389 open csr_pos_structure_versions;
390 fetch csr_pos_structure_versions into l_dummy;
391 if csr_pos_structure_versions%notfound then
392 close csr_pos_structure_versions;
393 hr_utility.set_message(800,'HR_289724_POS_VER_INV');
394 hr_utility.raise_error;
395 end if;
396 close csr_pos_structure_versions;
397
398 end if;
399 end if;
400 hr_utility.set_location('Leaving:'||l_proc, 10);
401
402 end chk_pos_structure_version_id;
403
404 --
405 -- -----------------------------------------------------------------------------
406 -- |-------------------< chk_parent_child_position_id >------------------------|
407 -- -----------------------------------------------------------------------------
408 -- Description:
409 -- Validates that both the parent and subordinate positions are supplied and
410 -- exist in hr_all_positions_f for the business group on the effective_date.
411 -- Also checks that the subordinate position is unique within the structure
412 -- version, to prevent circular hierarchies. (Insert Only).
413 --
414 -- Pre-conditions:
415 -- Business group is valid
416 -- Structure version is valid.
417 --
418 -- In Arguments:
419 -- p_pos_structure_element_id
420 -- p_business_group_id
421 -- p_effective_date
422 -- p_pos_structure_version_id
423 -- p_parent_position_id
424 -- p_subordinate_position_id
425 --
426 -- Post Success:
427 -- Processing continues
428 --
429 -- Post Failure:
430 -- Processing stops and an error is raised.
431 --
432 -- Access Status:
433 -- Internal Table Handler Use Only.
434 --
435 PROCEDURE chk_parent_child_position_id(p_pos_structure_element_id in NUMBER
436 ,p_business_group_id in NUMBER
437 ,p_effective_date in DATE
438 ,p_pos_structure_version_id in NUMBER
439 ,p_parent_position_id in NUMBER
440 ,p_subordinate_position_id in NUMBER) IS
441
442 CURSOR csr_pos (p_pos_id in NUMBER) IS
443 SELECT 'X'
444 from hr_all_positions_f pos
445 where pos.POSITION_ID = p_pos_id
446 and pos.business_group_id = p_business_group_id
447 and p_effective_date between pos.EFFECTIVE_START_DATE
448 and pos.EFFECTIVE_END_DATE;
449
450 CURSOR csr_ele IS
451 SELECT 'X'
452 from per_pos_structure_elements ele
453 where ele.pos_structure_version_id = p_pos_structure_version_id
454 and ele.subordinate_position_id = p_subordinate_position_id;
455
456 l_dummy varchar2(1);
457 l_proc varchar2(100) := g_package||'chk_parent_child_position_id';
458
459 begin
460 hr_utility.set_location('Entering:'||l_proc, 1);
461 --
465 (p_api_name => l_proc
462 -- Check mandatory parameters have been set
463 --
464 hr_api.mandatory_arg_error
466 ,p_argument => 'business_group_id'
467 ,p_argument_value => p_business_group_id
468 );
469 hr_api.mandatory_arg_error
470 (p_api_name => l_proc
471 ,p_argument => 'p_effective_date'
472 ,p_argument_value => p_effective_date
473 );
474 --
475 --
476 if p_parent_position_id is null or p_subordinate_position_id is null then
477 hr_utility.set_message(800,'HR_289725_POS_NULL');
478 hr_utility.raise_error;
479 else
480 --
481 hr_utility.set_location(l_proc, 5);
482 --
483 -- Only proceed with validation if :
484 -- a) Inserting (parent or child position_id are non-updateable)
485 --
486 if (p_pos_structure_element_id is null) then
487 -- check parent position_id exists
488 open csr_pos(p_pos_id => p_parent_position_id);
489 fetch csr_pos into l_dummy;
490 if csr_pos%notfound then
491 close csr_pos;
492 hr_utility.set_message(800,'HR_289726_PARENT_POS_INV');
493 hr_utility.raise_error;
494 end if;
495 close csr_pos;
496
497 hr_utility.set_location(l_proc, 20);
498 -- check child position_id exists
499 open csr_pos(p_subordinate_position_id);
500 fetch csr_pos into l_dummy;
501 if csr_pos%notfound then
502 close csr_pos;
503 hr_utility.set_message(800,'HR_289727_CHILD_POS_INV');
504 hr_utility.raise_error;
505 end if;
506 close csr_pos;
507
508 hr_utility.set_location(l_proc, 30);
509 -- check if child is the same as parent
510 if p_subordinate_position_id=p_parent_position_id then
511 hr_utility.set_message(800,'HR_289481_PARENT_CHILD_EQUAL');
512 hr_utility.raise_error;
513 end if;
514 -- check child is unique within the structure
515 open csr_ele;
516 fetch csr_ele into l_dummy;
517 if csr_ele%found then
518 close csr_ele;
519 hr_utility.set_message(800,'HR_289728_CHILD_POS_EXISTS');
520 hr_utility.raise_error;
521 end if;
522 close csr_ele;
523
524 end if;
525 end if;
526
527 hr_utility.set_location('Leaving:'||l_proc, 50);
528 --
529 end chk_parent_child_position_id;
530 --
531 --
532 -- ----------------------------------------------------------------------------
533 -- |---------------------------< insert_validate >----------------------------|
534 -- ----------------------------------------------------------------------------
535 Procedure insert_validate
536 (p_rec in per_pse_shd.g_rec_type
537 ,p_effective_date in date
538 ) is
539 --
540 l_proc varchar2(72) := g_package||'insert_validate';
541 --
542 Begin
543 hr_utility.set_location('Entering:'||l_proc, 5);
544 --
545 -- Call all supporting business operations
546 --
547 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
548
549 --
550 -- validate structure_version_id
551 --
552 per_pse_bus.chk_pos_structure_version_id(p_pos_structure_element_id => p_rec.pos_structure_version_id
553 ,p_business_group_id => p_rec.business_group_id
554 ,p_pos_structure_version_id => p_rec.pos_structure_version_id
555 ,p_effective_date => p_effective_date);
556
557 --
558 -- validate parent and subordinate position_id
559 --
560 per_pse_bus.chk_parent_child_position_id(p_pos_structure_element_id => p_rec.pos_structure_element_id
561 ,p_business_group_id => p_rec.business_group_id
562 ,p_effective_date => p_effective_date
563 ,p_pos_structure_version_id => p_rec.pos_structure_version_id
564 ,p_parent_position_id => p_rec.parent_position_id
565 ,p_subordinate_position_id => p_rec.subordinate_position_id);
566 --
567 --
568 hr_utility.set_location(' Leaving:'||l_proc, 10);
569 End insert_validate;
570 --
571 -- ----------------------------------------------------------------------------
572 -- |---------------------------< update_validate >----------------------------|
573 -- ----------------------------------------------------------------------------
574 Procedure update_validate
575 (p_rec in per_pse_shd.g_rec_type
576 ,p_effective_date in date
577 ) is
578 --
579 l_proc varchar2(72) := g_package||'update_validate';
580 --
581 Begin
582 hr_utility.set_location('Entering:'||l_proc, 5);
583 --
584 -- Call all supporting business operations
585 --
586 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
587 --
588 chk_non_updateable_args
589 (p_rec => p_rec
590 );
591 --
592 --
593 hr_utility.set_location(' Leaving:'||l_proc, 10);
594 End update_validate;
595 --
596 -- ----------------------------------------------------------------------------
597 -- |---------------------------< delete_validate >----------------------------|
598 -- ----------------------------------------------------------------------------
599 Procedure delete_validate
600 (p_rec in per_pse_shd.g_rec_type
601 ,p_hr_installed in VARCHAR2
602 ,p_chk_children in VARCHAR2
603 ) is
604 --
605 l_proc varchar2(72) := g_package||'delete_validate';
606 --
607 Begin
608 hr_utility.set_location('Entering:'||l_proc, 5);
609 --
610 -- Call all supporting business operations
611 --
612 if p_chk_children = 'Y' then
613 per_pse_bus.chk_children_exist
614 (p_pos_structure_version_id => p_rec.pos_structure_version_id
615 ,p_subordinate_position_id => p_rec.subordinate_position_id
616 );
617 end if;
618 --
619 if p_hr_installed = 'I' then
620 per_pse_bus.chk_security_pos
621 (p_Subordinate_position_Id => p_rec.Subordinate_position_Id
622 ,p_Business_Group_Id => p_rec.Business_Group_Id
623 ,p_Pos_Structure_version_Id => p_rec.Pos_Structure_version_Id
624 );
625 end if;
626 --
627 hr_utility.set_location(' Leaving:'||l_proc, 10);
628 End delete_validate;
629 --
630 end per_pse_bus;
[Home] [Help]