[Home] [Help]
PACKAGE BODY: APPS.PER_PST_BUS
Source
1 Package Body per_pst_bus as
2 /* $Header: pepstrhi.pkb 115.6 2002/12/04 17:14:16 eumenyio noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pst_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_position_structure_id number default null;
15
16 -- ---------------------------------------------------------------------------
17 -- |----------------------------< chk_y_or_n>--------------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure chk_y_or_n
21 (p_effective_date in date
22 ,p_flag in varchar2
23 ,p_flag_name in varchar2)
24 IS
25 l_proc VARCHAR2(72) := g_package||'chk_sec_profile';
26 begin
27 hr_utility.set_location('Entering:'|| l_proc, 10);
28 --
29 --
30 IF hr_api.not_exists_in_hrstanlookups
31 (p_effective_date => p_effective_date
32 ,p_lookup_type => 'YES_NO'
33 ,p_lookup_code => p_flag
34 ) THEN
35 fnd_message.set_name('801','HR_52970_COL_Y_OR_N');
36 fnd_message.set_token('COLUMN',p_flag_name);
37 fnd_message.raise_error;
38 end if;
39 --
40 hr_utility.set_location('Leaving:'||l_proc, 30);
41 --
42 end chk_y_or_n;
43
44 --
45 -- ---------------------------------------------------------------------------
46 -- |----------------------< chk_pos_name >--------------------------|
47 -- ---------------------------------------------------------------------------
48 --
49 Procedure chk_pos_name
50 (p_position_structure_id in number,
51 p_business_group_id in number,
52 p_name in varchar2
53 ) is
54 l_proc VARCHAR2(72) := g_package||'chk_pos_name';
55 --
56 -- Declare cursor
57 --
58 cursor csr_pos_name is
59 select position_structure_id, business_group_id
60 from per_position_structures
61 where name = p_name;
62 begin
63 --
64 hr_utility.set_location('Entering:'|| l_proc, 10);
65 --
66 --
67 hr_api.mandatory_arg_error
68 (p_api_name => l_proc
69 ,p_argument => 'p_name'
70 ,p_argument_value => p_name
71 );
72 hr_api.mandatory_arg_error
73 (p_api_name => l_proc
74 ,p_argument => 'p_business_group_id'
75 ,p_argument_value => p_business_group_id
76 );
77
78 --
79 --
80 hr_utility.set_location(l_proc, 20);
81 --
82 --
83
84 for Crec in csr_pos_name loop
85 if Crec.position_structure_id <> nvl(p_position_structure_id,-1)
86 and Crec.business_group_id = p_business_group_id then
87 hr_utility.set_message(800, 'HR_52751_DUPL_NAME'); -- raise error
88 hr_utility.raise_error;
89 end if;
90 end loop;
91 --
92 hr_utility.set_location('Leaving:'||l_proc, 30);
93 --
94 end chk_pos_name;
95
96 --
97 --
98 -- ---------------------------------------------------------------------------
99 -- |-------------------------< chk_primary_flag >----------------------------|
100 -- ---------------------------------------------------------------------------
101 --
102 Procedure chk_primary_flag
103 (p_position_structure_id in number,
104 p_business_group_id in number,
105 p_primary_position_flag in varchar2
106 ) is
107 l_proc VARCHAR2(72) := g_package||'chk_primary_flag';
108 --
109 -- Declare cursor
110 --
111 cursor csr_pos_primary is
112 select position_structure_id,business_group_id
113 from per_position_structures
114 where primary_position_flag = 'Y';
115 begin
116 --
117 hr_utility.set_location('Entering:'|| l_proc, 10);
118 --
119 --
120 hr_api.mandatory_arg_error
121 (p_api_name => l_proc
122 ,p_argument => 'p_primary_position_flag'
123 ,p_argument_value => p_primary_position_flag
124 );
125 hr_api.mandatory_arg_error
126 (p_api_name => l_proc
127 ,p_argument => 'p_business_group_id'
128 ,p_argument_value => p_business_group_id
129 );
130
131 --
132 --
133 hr_utility.set_location(l_proc, 20);
134 --
135 --
136 if p_primary_position_flag = 'Y' then
137 for Crec in csr_pos_primary loop
138 if Crec.position_structure_id <> nvl(p_position_structure_id,-1)
139 and Crec.business_group_id = p_business_group_id then
140 hr_utility.set_message(800, 'HR_6085_POS_ONE_PRIMARY');
141 hr_utility.raise_error;
142 end if;
143 end loop;
144 end if;
145 --
146 hr_utility.set_location('Leaving:'||l_proc, 30);
147 --
148 end chk_primary_flag;
149
150 -- ---------------------------------------------------------------------------
151 -- |---------------------------< chk_no_children >---------------------------|
152 -- ---------------------------------------------------------------------------
153 --
154 Procedure chk_no_children
155 (p_position_structure_id in number) is
156
157 l_proc VARCHAR2(72) := g_package||'chk_no_children';
158 l_count number(2);
159 begin
160 --
161 hr_utility.set_location('Entering:'|| l_proc, 10);
162 --
163 --
164 hr_api.mandatory_arg_error
165 (p_api_name => l_proc
166 ,p_argument => 'p_position_structure_id'
167 ,p_argument_value => p_position_structure_id
168 );
169 select count(*)
170 into l_count
171 from PER_POS_STRUCTURE_VERSIONS
172 where POSITION_STRUCTURE_ID = p_position_Structure_Id;
173 if l_count >0 then
174 hr_utility.set_message('801','HR_6084_PO_POS_HAS_HIER_VER');
175 hr_utility.raise_error;
176 end if;
177
178 --
179 hr_utility.set_location('Leaving:'||l_proc, 30);
180 --
181 end chk_no_children;
182
183 -- ---------------------------------------------------------------------------
184 -- |--------------------------< chk_sec_profile >----------------------------|
185 -- ---------------------------------------------------------------------------
186 --
187 Procedure chk_sec_profile
188 (p_position_structure_id in number) is
189
190 l_proc VARCHAR2(72) := g_package||'chk_sec_profile';
191 l_count number(2);
192 begin
193 --
194 hr_utility.set_location('Entering:'|| l_proc, 10);
195 --
196 --
197 hr_api.mandatory_arg_error
198 (p_api_name => l_proc
199 ,p_argument => 'p_position_structure_id'
200 ,p_argument_value => p_position_structure_id
201 );
202 select count(*)
203 into l_count
204 from per_security_profiles
205 where position_structure_id = p_position_structure_id;
206 if l_count >0 then
207 hr_utility.set_message('801','PAY_7694_PER_NO_DEL_STRUCTURE');
208 hr_utility.raise_error;
209 end if;
210
211 --
212 hr_utility.set_location('Leaving:'||l_proc, 30);
213 --
214 end chk_sec_profile;
215
216 --
217 --
218 -- ---------------------------------------------------------------------------
219 -- |----------------------< set_security_group_id >--------------------------|
220 -- ---------------------------------------------------------------------------
221 --
222 Procedure set_security_group_id
223 (p_position_structure_id in number
224 ) is
225 --
226 -- Declare cursor
227 --
228 cursor csr_sec_grp is
229 select pbg.security_group_id
230 from per_business_groups pbg
231 , per_position_structures pst
232 where pst.position_structure_id = p_position_structure_id
233 and pbg.business_group_id = pst.business_group_id;
234 --
235 -- Declare local variables
236 --
237 l_security_group_id number;
238 l_proc varchar2(72) := g_package||'set_security_group_id';
239 --
240 begin
241 --
242 hr_utility.set_location('Entering:'|| l_proc, 10);
243 --
244 -- Ensure that all the mandatory parameter are not null
245 --
246 hr_api.mandatory_arg_error
247 (p_api_name => l_proc
248 ,p_argument => 'position_structure_id'
249 ,p_argument_value => p_position_structure_id
250 );
251 --
252 open csr_sec_grp;
253 fetch csr_sec_grp into l_security_group_id;
254 --
255 if csr_sec_grp%notfound then
256 --
257 close csr_sec_grp;
258 --
259 -- The primary key is invalid therefore we must error
260 --
261 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
262 fnd_message.raise_error;
263 --
264 end if;
265 close csr_sec_grp;
266 --
267 -- Set the security_group_id in CLIENT_INFO
268 --
269 hr_api.set_security_group_id
270 (p_security_group_id => l_security_group_id
271 );
272 --
273 hr_utility.set_location(' Leaving:'|| l_proc, 20);
274 --
275 end set_security_group_id;
276 --
277 -- ---------------------------------------------------------------------------
278 -- |---------------------< return_legislation_code >-------------------------|
279 -- ---------------------------------------------------------------------------
280 --
281 Function return_legislation_code
282 (p_position_structure_id in number
283 )
284 Return Varchar2 Is
285 --
286 -- Declare cursor
287 --
288 cursor csr_leg_code is
289 select pbg.legislation_code
290 from per_business_groups pbg
291 , per_position_structures pst
292 where pst.position_structure_id = p_position_structure_id
293 and pbg.business_group_id = pst.business_group_id;
294 --
295 -- Declare local variables
296 --
297 l_legislation_code varchar2(150);
298 l_proc varchar2(72) := g_package||'return_legislation_code';
299 --
300 Begin
301 --
302 hr_utility.set_location('Entering:'|| l_proc, 10);
303 --
304 -- Ensure that all the mandatory parameter are not null
305 --
306 hr_api.mandatory_arg_error
307 (p_api_name => l_proc
308 ,p_argument => 'position_structure_id'
309 ,p_argument_value => p_position_structure_id
310 );
311 --
312 if ( nvl(per_pst_bus.g_position_structure_id, hr_api.g_number)
313 = p_position_structure_id) then
314 --
315 -- The legislation code has already been found with a previous
316 -- call to this function. Just return the value in the global
317 -- variable.
318 --
319 l_legislation_code := per_pst_bus.g_legislation_code;
320 hr_utility.set_location(l_proc, 20);
321 else
322 --
323 -- The ID is different to the last call to this function
324 -- or this is the first call to this function.
325 --
326 open csr_leg_code;
327 fetch csr_leg_code into l_legislation_code;
328 --
329 if csr_leg_code%notfound then
330 --
331 -- The primary key is invalid therefore we must error
332 --
333 close csr_leg_code;
334 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
335 fnd_message.raise_error;
336 end if;
337 hr_utility.set_location(l_proc,30);
338 --
339 -- Set the global variables so the values are
340 -- available for the next call to this function.
341 --
342 close csr_leg_code;
343 per_pst_bus.g_position_structure_id := p_position_structure_id;
344 per_pst_bus.g_legislation_code := l_legislation_code;
345 end if;
346 hr_utility.set_location(' Leaving:'|| l_proc, 40);
347 return l_legislation_code;
348 end return_legislation_code;
349 --
350 -- ----------------------------------------------------------------------------
351 -- |------------------------------< chk_df >----------------------------------|
352 -- ----------------------------------------------------------------------------
353 --
354 -- Description:
355 -- Validates all the Descriptive Flexfield values.
356 --
357 -- Prerequisites:
358 -- All other columns have been validated. Must be called as the
359 -- last step from insert_validate and update_validate.
360 --
361 -- In Arguments:
362 -- p_rec
363 --
364 -- Post Success:
365 -- If the Descriptive Flexfield structure column and data values are
366 -- all valid this procedure will end normally and processing will
367 -- continue.
368 --
369 -- Post Failure:
370 -- If the Descriptive Flexfield structure column value or any of
371 -- the data values are invalid then an application error is raised as
372 -- a PL/SQL exception.
373 --
374 -- Access Status:
375 -- Internal Row Handler Use Only.
376 --
377 -- ----------------------------------------------------------------------------
378 procedure chk_df
379 (p_rec in per_pst_shd.g_rec_type
380 ) is
381 --
382 l_proc varchar2(72) := g_package || 'chk_df';
383 --
384 begin
385 hr_utility.set_location('Entering:'||l_proc,10);
386 --
387 if ((p_rec.position_structure_id is not null) and (
388 nvl(per_pst_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
389 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
390 nvl(per_pst_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
391 nvl(p_rec.attribute1, hr_api.g_varchar2) or
392 nvl(per_pst_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
393 nvl(p_rec.attribute2, hr_api.g_varchar2) or
394 nvl(per_pst_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
395 nvl(p_rec.attribute3, hr_api.g_varchar2) or
396 nvl(per_pst_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
397 nvl(p_rec.attribute4, hr_api.g_varchar2) or
398 nvl(per_pst_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
399 nvl(p_rec.attribute5, hr_api.g_varchar2) or
400 nvl(per_pst_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
401 nvl(p_rec.attribute6, hr_api.g_varchar2) or
402 nvl(per_pst_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
403 nvl(p_rec.attribute7, hr_api.g_varchar2) or
404 nvl(per_pst_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
405 nvl(p_rec.attribute8, hr_api.g_varchar2) or
406 nvl(per_pst_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
407 nvl(p_rec.attribute9, hr_api.g_varchar2) or
408 nvl(per_pst_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
409 nvl(p_rec.attribute10, hr_api.g_varchar2) or
410 nvl(per_pst_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
411 nvl(p_rec.attribute11, hr_api.g_varchar2) or
412 nvl(per_pst_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
413 nvl(p_rec.attribute12, hr_api.g_varchar2) or
414 nvl(per_pst_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
415 nvl(p_rec.attribute13, hr_api.g_varchar2) or
416 nvl(per_pst_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
417 nvl(p_rec.attribute14, hr_api.g_varchar2) or
418 nvl(per_pst_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
419 nvl(p_rec.attribute15, hr_api.g_varchar2) or
420 nvl(per_pst_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
421 nvl(p_rec.attribute16, hr_api.g_varchar2) or
422 nvl(per_pst_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
423 nvl(p_rec.attribute17, hr_api.g_varchar2) or
424 nvl(per_pst_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
428 nvl(per_pst_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
425 nvl(p_rec.attribute18, hr_api.g_varchar2) or
426 nvl(per_pst_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
427 nvl(p_rec.attribute19, hr_api.g_varchar2) or
429 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
430 or (p_rec.position_structure_id is null) then
431 --
432 -- Only execute the validation if absolutely necessary:
433 -- a) During update, the structure column value or any
434 -- of the attribute values have actually changed.
435 -- b) During insert.
436 --
437 hr_dflex_utility.ins_or_upd_descflex_attribs
438 (p_appl_short_name => 'PER'
439 ,p_descflex_name => 'PER_POSITION_STRUCTURES'
440 ,p_attribute_category => p_rec.attribute_category
441 ,p_attribute1_name => 'ATTRIBUTE1'
442 ,p_attribute1_value => p_rec.attribute1
443 ,p_attribute2_name => 'ATTRIBUTE2'
444 ,p_attribute2_value => p_rec.attribute2
445 ,p_attribute3_name => 'ATTRIBUTE3'
446 ,p_attribute3_value => p_rec.attribute3
447 ,p_attribute4_name => 'ATTRIBUTE4'
448 ,p_attribute4_value => p_rec.attribute4
449 ,p_attribute5_name => 'ATTRIBUTE5'
450 ,p_attribute5_value => p_rec.attribute5
451 ,p_attribute6_name => 'ATTRIBUTE6'
452 ,p_attribute6_value => p_rec.attribute6
453 ,p_attribute7_name => 'ATTRIBUTE7'
454 ,p_attribute7_value => p_rec.attribute7
455 ,p_attribute8_name => 'ATTRIBUTE8'
456 ,p_attribute8_value => p_rec.attribute8
457 ,p_attribute9_name => 'ATTRIBUTE9'
458 ,p_attribute9_value => p_rec.attribute9
459 ,p_attribute10_name => 'ATTRIBUTE10'
460 ,p_attribute10_value => p_rec.attribute10
461 ,p_attribute11_name => 'ATTRIBUTE11'
462 ,p_attribute11_value => p_rec.attribute11
463 ,p_attribute12_name => 'ATTRIBUTE12'
464 ,p_attribute12_value => p_rec.attribute12
465 ,p_attribute13_name => 'ATTRIBUTE13'
466 ,p_attribute13_value => p_rec.attribute13
467 ,p_attribute14_name => 'ATTRIBUTE14'
468 ,p_attribute14_value => p_rec.attribute14
469 ,p_attribute15_name => 'ATTRIBUTE15'
470 ,p_attribute15_value => p_rec.attribute15
471 ,p_attribute16_name => 'ATTRIBUTE16'
472 ,p_attribute16_value => p_rec.attribute16
473 ,p_attribute17_name => 'ATTRIBUTE17'
474 ,p_attribute17_value => p_rec.attribute17
475 ,p_attribute18_name => 'ATTRIBUTE18'
476 ,p_attribute18_value => p_rec.attribute18
477 ,p_attribute19_name => 'ATTRIBUTE19'
478 ,p_attribute19_value => p_rec.attribute19
479 ,p_attribute20_name => 'ATTRIBUTE20'
480 ,p_attribute20_value => p_rec.attribute20
481 );
482 end if;
483 --
484 hr_utility.set_location(' Leaving:'||l_proc,20);
485 end chk_df;
486 --
487 -- ----------------------------------------------------------------------------
488 -- |-----------------------< chk_non_updateable_args >------------------------|
489 -- ----------------------------------------------------------------------------
490 -- {Start Of Comments}
491 --
492 -- Description:
493 -- This procedure is used to ensure that non updateable attributes have
494 -- not been updated. If an attribute has been updated an error is generated.
495 --
496 -- Pre Conditions:
497 -- g_old_rec has been populated with details of the values currently in
498 -- the database.
499 --
500 -- In Arguments:
501 -- p_rec has been populated with the updated values the user would like the
502 -- record set to.
503 --
504 -- Post Success:
505 -- Processing continues if all the non updateable attributes have not
506 -- changed.
507 --
508 -- Post Failure:
509 -- An application error is raised if any of the non updatable attributes
510 -- have been altered.
511 --
512 -- {End Of Comments}
513 -- ----------------------------------------------------------------------------
514 Procedure chk_non_updateable_args
515 (p_effective_date in date
516 ,p_rec in per_pst_shd.g_rec_type
517 ) IS
518 --
519 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
520 l_error EXCEPTION;
521 l_argument varchar2(30);
522 --
523 Begin
524 --
525 -- Only proceed with the validation if a row exists for the current
526 -- record in the HR Schema.
527 --
528 IF NOT per_pst_shd.api_updating
529 (p_position_structure_id => p_rec.position_structure_id
530 ,p_object_version_number => p_rec.object_version_number
531 ) THEN
532 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
533 fnd_message.set_token('PROCEDURE ', l_proc);
534 fnd_message.set_token('STEP ', '5');
538 WHEN l_error THEN
535 fnd_message.raise_error;
536 END IF;
537 EXCEPTION
539 hr_api.argument_changed_error
540 (p_api_name => l_proc
541 ,p_argument => l_argument);
542 WHEN OTHERS THEN
543 RAISE;
544 End chk_non_updateable_args;
545 --
546 -- ----------------------------------------------------------------------------
547 -- |---------------------------< insert_validate >----------------------------|
548 -- ----------------------------------------------------------------------------
549 Procedure insert_validate
550 (p_effective_date in date
551 ,p_rec in per_pst_shd.g_rec_type
552 ) is
553 --
554 l_proc varchar2(72) := g_package||'insert_validate';
555 --
556 Begin
557 hr_utility.set_location('Entering:'||l_proc, 5);
558 --
559 -- Call all supporting business operations
560 --
561
562 chk_pos_name
563 (p_position_structure_id => p_rec.position_structure_id
564 ,p_business_group_id => p_rec.business_group_id
565 ,p_name => p_rec.name);
566
567 chk_y_or_n
568 (p_effective_date => p_effective_date
569 ,p_flag => p_rec.primary_position_flag
570 ,p_flag_name => 'primary_position_flag');
571
572 chk_primary_flag
573 (p_position_structure_id => p_rec.position_structure_id
574 ,p_business_group_id => p_rec.business_group_id
575 ,p_primary_position_flag => p_rec.primary_position_flag);
576
577 if p_rec.business_group_id <> NULL then
578 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
579 end if;
580 --
581 --
582 per_pst_bus.chk_df(p_rec);
583 --
584 hr_utility.set_location(' Leaving:'||l_proc, 10);
585 End insert_validate;
586 --
587 -- ----------------------------------------------------------------------------
588 -- |---------------------------< update_validate >----------------------------|
589 -- ----------------------------------------------------------------------------
590 Procedure update_validate
591 (p_effective_date in date
592 ,p_rec in per_pst_shd.g_rec_type
593 ) is
594 --
595 l_proc varchar2(72) := g_package||'update_validate';
596 --
597 Begin
598 hr_utility.set_location('Entering:'||l_proc, 5);
599 --
600 -- Call all supporting business operations
601 --
602 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
603 --
604 chk_non_updateable_args
605 (p_effective_date => p_effective_date
606 ,p_rec => p_rec
607 );
608 --
609 --
610 chk_pos_name
611 (p_position_structure_id => p_rec.position_structure_id
612 ,p_business_group_id => p_rec.business_group_id
613 ,p_name => p_rec.name);
614
615 chk_y_or_n
616 (p_effective_date => p_effective_date
617 ,p_flag => p_rec.primary_position_flag
618 ,p_flag_name => 'primary_position_flag');
619
620
621 chk_primary_flag
622 (p_position_structure_id => p_rec.position_structure_id
623 ,p_business_group_id => p_rec.business_group_id
624 ,p_primary_position_flag => p_rec.primary_position_flag);
625
626 per_pst_bus.chk_df(p_rec);
627 --
628 hr_utility.set_location(' Leaving:'||l_proc, 10);
629 End update_validate;
630 --
631 -- ----------------------------------------------------------------------------
632 -- |---------------------------< delete_validate >----------------------------|
633 -- ----------------------------------------------------------------------------
634 Procedure delete_validate
635 (p_rec in per_pst_shd.g_rec_type
636 ) is
637 --
638 l_proc varchar2(72) := g_package||'delete_validate';
639 --
640 Begin
641 hr_utility.set_location('Entering:'||l_proc, 5);
642 --
643 -- Call all supporting business operations
644 chk_no_children(p_position_structure_id => p_rec.position_Structure_id);
645 chk_sec_profile(p_position_structure_id => p_rec.position_Structure_id);
646
647 --
648 hr_utility.set_location(' Leaving:'||l_proc, 10);
649 End delete_validate;
650 --
651 end per_pst_bus;