[Home] [Help]
PACKAGE BODY: APPS.PER_ASR_BUS
Source
1 Package Body per_asr_bus as
2 /* $Header: peasrrhi.pkb 115.5 99/10/05 09:44:16 porting ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_asr_bus.'; -- Global package name
9 -- The following two global variables are only to be
10 -- used by the return_legislation_code function.
11 --
12 g_legislation_code varchar2(150) default null;
13 g_assessment_group_id number default null;
14 --
15 --
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_non_updateable_args >------------------------|
18 -- ----------------------------------------------------------------------------
19 --
20 -- In the ASSESSMENT_GROUP entity, there is only one non updatable argument :
21 -- business_group_id
22 --
23 Procedure chk_non_updateable_args(p_rec in per_asr_shd.g_rec_type) is
24 --
25 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
26 l_error exception;
27 l_argument varchar2(30);
28 --
29 Begin
30 --
31 hr_utility.set_location('Entering:'|| l_proc,5);
32 --
33 -- Only proceed with validation if a row exists for
34 -- the current record in the HR Schema.
35 if not per_asr_shd.api_updating
36 --
37 (p_assessment_group_id => p_rec.assessment_group_id
38 ,p_object_version_number => p_rec.object_version_number
39 ) then
40 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
41 hr_utility.set_message_token('PROCEDURE', l_proc);
42 hr_utility.set_message_token('STEP', '5');
43 end if;
44 --
45 hr_utility.set_location (l_proc, 6);
46 --
47 if p_rec.business_group_id <> per_asn_shd.g_old_rec.business_group_id then
48 l_argument := 'business_group_id';
49 raise l_error;
50 end if;
51 --
52 exception
53 when l_error then
54 hr_api.argument_changed_error
55 (p_api_name => l_proc
56 ,p_argument => l_argument);
57 when others then
58 raise;
59 --
60 hr_utility.set_location(' Leaving : '|| l_proc, 10);
61 --
62 end chk_non_updateable_args;
63 --
64 --
65 -- ----------------------------------------------------------------------------
66 -- |--------------------------< chk_name_unique >-----------------------------|
67 -- ----------------------------------------------------------------------------
68 --
69 -- DESCRIPTION
70 -- This checks to make sure the name is unique within the business group.
71 --
72 -- PRE-REQUISITES
73 --
74 -- IN PARAMETERS
75 -- p_name
76 -- p_assessment_group_id
77 -- p_business_group_id
78 -- p_object_version_number
79 --
80 -- POST SUCCESS
81 -- Processing continues
82 --
83 -- POST FAILURE
84 -- Processing halts and an error is displayed.
85 --
86 -- ACCESS STATUS
87 -- Internal Development Use Only
88 --
89 Procedure chk_name_unique
90 (p_name in per_assessment_groups.name%TYPE
91 ,p_assessment_group_id in per_assessment_groups.assessment_group_id%TYPE
92 ,p_business_group_id in per_assessment_groups.business_group_id%TYPE
93 ,p_object_version_number in per_assessment_groups.object_version_number%TYPE
94 ) is
95 --
96 l_proc varchar2(72) := g_package||'chk_name_unique';
97 --
98 -- Define cusror for duplicate name check
99 --
100 cursor csr_chk_name_unique is
101 select null
102 from per_assessment_groups ag
103 where ((p_assessment_group_id is NULL) -- if the row is being inserted
104 or (p_assessment_group_id <> ag.assessment_group_id))
105 and p_name = ag.name -- there is a duplicate name
106 and p_business_group_id = ag.business_group_id +0 ;
107 --
108 l_exists varchar2(1);
109 l_api_updating boolean;
110 --
111 begin
112 hr_utility.set_location('Entering:'||l_proc, 5);
113 --
114 -- Check that the business_group_id is not null.
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'business_group_id'
119 ,p_argument_value => p_business_group_id
120 );
121 --
122 -- Only proceed with validation if:
123 -- a) The current g_old_rec is current and
124 -- b) The value for name has changed.
125 -- c) The value is being inserted.
126 --
127 l_api_updating := per_asr_shd.api_updating
128 (p_assessment_group_id => p_assessment_group_id
129 ,p_object_version_number => p_object_version_number
130 );
131 --
132 hr_utility.set_location (l_proc, 2);
133 --
134 if (l_api_updating AND
135 nvl(per_asr_shd.g_old_rec.name, hr_api.g_varchar2)
136 <> nvl(p_name, hr_api.g_varchar2)
137 or not l_api_updating)
138 then
139 --
140 hr_utility.set_location (l_proc, 3);
141 --
142 -- Check that the name isn't NULL
143 --
144 if p_name is NULL then
145 hr_utility.set_message(801,'HR_51595_ASR_NAME_NULL');
146 hr_utility.raise_error;
147 end if;
148 --
149 -- Check that the name is unique within the business group.
150 --
151 hr_utility.set_location('Cursor open;name:'||l_proc, 5);
152 open csr_chk_name_unique;
153 fetch csr_chk_name_unique into l_exists;
154 if csr_chk_name_unique%found then
155 hr_utility.set_location('name not unique:'||l_proc, 5);
156 -- The name already exists in the business group
157 close csr_chk_name_unique;
158 per_asr_shd.constraint_error
159 (p_constraint_name => 'PER_ASSESSMENT_GROUPS_NAME_UK1');
160 end if;
161 close csr_chk_name_unique;
162 --
163 end if;
164 --
165 hr_utility.set_location(' Leaving:'||l_proc, 10);
166 --
167 end chk_name_unique;
168 --
169 -- ----------------------------------------------------------------------------
170 -- |---------------------------< CHK_REF_ROWS_ASN >---------------------------|
171 -- ----------------------------------------------------------------------------
172 --
173 -- DESCRIPTION
174 -- Before an assessment group can be removed, a check is carried out to make
175 -- sure that no rows reference it in per_assessments.
176 -- If they do, an error is raised.
177 --
178 -- PRE-REQUISITES
179 --
180 -- IN PARAMETERS
181 -- p_assessment_group_id
182 --
183 -- POST SUCCESS
184 -- Processing continues
185 -- POST FAILURE
186 -- Processing halts.
187 --
188 -- ACCESS STATUS
189 -- Internal Development Use Only
190 --
191 Procedure chk_ref_rows_asn
192 (p_assessment_group_id in per_assessment_groups.assessment_group_id%TYPE
193 ) is
194 --
195 l_proc varchar2(72):=g_package||'chk_ref_rows_asn';
196 --
197 cursor csr_chk_ref_rows_asn is
198 select null
199 from per_assessments asn
200 where p_assessment_group_id = asn.assessment_group_id;
201 --
202 l_exists varchar2(1);
203 --
204 begin
205 hr_utility.set_location('Entering:'|| l_proc, 1);
206 --
207 -- Check that the assessment group is not referenced by an assessment
208 --
209 open csr_chk_ref_rows_asn;
210 fetch csr_chk_ref_rows_asn into l_exists;
211 --
212 if csr_chk_ref_rows_asn%found then
213 close csr_chk_ref_rows_asn;
214 --
215 hr_utility.set_location(l_proc,5);
216 hr_utility.set_message (801, 'HR_51597_ASR_REF_BY_ASS');
217 hr_utility.raise_error;
218 --
219 end if;
220 close csr_chk_ref_rows_asn;
221 --
222 end chk_ref_rows_asn;
223 --
224 -- -----------------------------------------------------------------------
225 -- |------------------------------< chk_df >-----------------------------|
226 -- -----------------------------------------------------------------------
227 --
228 -- Description:
229 -- Validates the all Descriptive Flexfield values.
230 --
231 -- Pre-conditions:
232 -- All other columns have been validated. Must be called as the
233 -- last step from insert_validate and update_validate.
234 --
235 -- In Arguments:
236 -- p_rec
237 --
238 -- Post Success:
239 -- If the Descriptive Flexfield structure column and data values are
240 -- all valid this procedure will end normally and processing will
241 -- continue.
242 --
243 -- Post Failure:
244 -- If the Descriptive Flexfield structure column value or any of
245 -- the data values are invalid then an application error is raised as
246 -- a PL/SQL exception.
247 --
248 -- Access Status:
249 -- Internal Row Handler Use Only.
250 --
251 procedure chk_df
252 (p_rec in per_asr_shd.g_rec_type) is
253 --
254 l_proc varchar2(72) := g_package||'chk_df';
255 --
256 begin
257 hr_utility.set_location('Entering:'||l_proc, 10);
258 --
259 if ((p_rec.assessment_group_id is not null) and (
260 nvl(per_asr_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
261 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
262 nvl(per_asr_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
263 nvl(p_rec.attribute1, hr_api.g_varchar2) or
264 nvl(per_asr_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
265 nvl(p_rec.attribute2, hr_api.g_varchar2) or
266 nvl(per_asr_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
267 nvl(p_rec.attribute3, hr_api.g_varchar2) or
268 nvl(per_asr_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
269 nvl(p_rec.attribute4, hr_api.g_varchar2) or
270 nvl(per_asr_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
271 nvl(p_rec.attribute5, hr_api.g_varchar2) or
272 nvl(per_asr_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
273 nvl(p_rec.attribute6, hr_api.g_varchar2) or
274 nvl(per_asr_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
275 nvl(p_rec.attribute7, hr_api.g_varchar2) or
276 nvl(per_asr_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
277 nvl(p_rec.attribute8, hr_api.g_varchar2) or
278 nvl(per_asr_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
279 nvl(p_rec.attribute9, hr_api.g_varchar2) or
280 nvl(per_asr_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
281 nvl(p_rec.attribute10, hr_api.g_varchar2) or
282 nvl(per_asr_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
283 nvl(p_rec.attribute11, hr_api.g_varchar2) or
284 nvl(per_asr_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
285 nvl(p_rec.attribute12, hr_api.g_varchar2) or
286 nvl(per_asr_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
287 nvl(p_rec.attribute13, hr_api.g_varchar2) or
288 nvl(per_asr_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
289 nvl(p_rec.attribute14, hr_api.g_varchar2) or
290 nvl(per_asr_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
291 nvl(p_rec.attribute15, hr_api.g_varchar2) or
292 nvl(per_asr_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
293 nvl(p_rec.attribute16, hr_api.g_varchar2) or
294 nvl(per_asr_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
295 nvl(p_rec.attribute17, hr_api.g_varchar2) or
296 nvl(per_asr_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
297 nvl(p_rec.attribute18, hr_api.g_varchar2) or
298 nvl(per_asr_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
299 nvl(p_rec.attribute19, hr_api.g_varchar2) or
300 nvl(per_asr_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
301 nvl(p_rec.attribute20, hr_api.g_varchar2)))
302 or
303 (p_rec.assessment_group_id is null) then
304 --
305 -- Only execute the validation if absolutely necessary:
306 -- a) During update, the structure column value or any
307 -- of the attribute values have actually changed.
308 -- b) During insert.
309 --
310 hr_dflex_utility.ins_or_upd_descflex_attribs
311 (p_appl_short_name => 'PER'
312 ,p_descflex_name => 'PER_ASSESSMENT_GROUPS'
313 ,p_attribute_category => p_rec.attribute_category
314 ,p_attribute1_name => 'ATTRIBUTE1'
315 ,p_attribute1_value => p_rec.attribute1
316 ,p_attribute2_name => 'ATTRIBUTE2'
317 ,p_attribute2_value => p_rec.attribute2
318 ,p_attribute3_name => 'ATTRIBUTE3'
319 ,p_attribute3_value => p_rec.attribute3
320 ,p_attribute4_name => 'ATTRIBUTE4'
321 ,p_attribute4_value => p_rec.attribute4
322 ,p_attribute5_name => 'ATTRIBUTE5'
323 ,p_attribute5_value => p_rec.attribute5
324 ,p_attribute6_name => 'ATTRIBUTE6'
325 ,p_attribute6_value => p_rec.attribute6
326 ,p_attribute7_name => 'ATTRIBUTE7'
327 ,p_attribute7_value => p_rec.attribute7
328 ,p_attribute8_name => 'ATTRIBUTE8'
329 ,p_attribute8_value => p_rec.attribute8
330 ,p_attribute9_name => 'ATTRIBUTE9'
331 ,p_attribute9_value => p_rec.attribute9
332 ,p_attribute10_name => 'ATTRIBUTE10'
333 ,p_attribute10_value => p_rec.attribute10
334 ,p_attribute11_name => 'ATTRIBUTE11'
335 ,p_attribute11_value => p_rec.attribute11
339 ,p_attribute13_value => p_rec.attribute13
336 ,p_attribute12_name => 'ATTRIBUTE12'
337 ,p_attribute12_value => p_rec.attribute12
338 ,p_attribute13_name => 'ATTRIBUTE13'
340 ,p_attribute14_name => 'ATTRIBUTE14'
341 ,p_attribute14_value => p_rec.attribute14
342 ,p_attribute15_name => 'ATTRIBUTE15'
343 ,p_attribute15_value => p_rec.attribute15
344 ,p_attribute16_name => 'ATTRIBUTE16'
345 ,p_attribute16_value => p_rec.attribute16
346 ,p_attribute17_name => 'ATTRIBUTE17'
347 ,p_attribute17_value => p_rec.attribute17
348 ,p_attribute18_name => 'ATTRIBUTE18'
349 ,p_attribute18_value => p_rec.attribute18
350 ,p_attribute19_name => 'ATTRIBUTE19'
351 ,p_attribute19_value => p_rec.attribute19
352 ,p_attribute20_name => 'ATTRIBUTE20'
353 ,p_attribute20_value => p_rec.attribute20
354 );
355 end if;
356 --
357 hr_utility.set_location(' Leaving:'||l_proc, 20);
358 --
359 end chk_df;
360 --
361 -- ----------------------------------------------------------------------------
362 -- |---------------------------< insert_validate >----------------------------|
363 -- ----------------------------------------------------------------------------
364 Procedure insert_validate
365 (p_rec in per_asr_shd.g_rec_type
366 ,p_effective_date in date) is
367 --
368 l_proc varchar2(72) := g_package||'insert_validate';
369 --
370 Begin
371 hr_utility.set_location('Entering:'||l_proc, 5);
372 --
373 -- Call all supporting business operations
374 --
375 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
376 --
377 -- VALIDATE CHK_NAME_UNIQUE
378 -- Business Rule Mapping
379 -- ---------------------
380 -- Rule CHK_NAME_UNIQUE a. b.
381 --
382 per_asr_bus.chk_name_unique
383 (p_name => p_rec.name
384 ,p_assessment_group_id => p_rec.assessment_group_id
385 ,p_business_group_id => p_rec.business_group_id
386 ,p_object_version_number => p_rec.object_version_number
387 );
388 --
389 -- Call descriptive flexfield validation routines
390 --
391 per_asr_bus.chk_df(p_rec => p_rec);
392 --
393 hr_utility.set_location(' Leaving:'||l_proc, 10);
394 End insert_validate;
395 --
396 -- ----------------------------------------------------------------------------
397 -- |---------------------------< update_validate >----------------------------|
398 -- ----------------------------------------------------------------------------
399 Procedure update_validate
400 (p_rec in per_asr_shd.g_rec_type
401 ,p_effective_date in date) is
402 --
403 l_proc varchar2(72) := g_package||'update_validate';
404 --
405 Begin
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 -- Call all supporting business operations. Mapping is provided
409 --
410 --
411 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
412 --
413 -- VALIDATE CHK_NON_UPDATABLE_ARGS
414 -- Check those columns which cannot be updated have not changed.
415 -- Business Rule Mapping
416 -- =====================
417 -- Rule CHK_BUSINESS_GROUP_ID a
418 --
419 per_asr_bus.chk_non_updateable_args
420 (p_rec => p_rec);
421 --
422 -- VALIDATE CHK_NAME_UNIQUE
423 -- Business Rule Mapping
424 -- ---------------------
425 -- Rule CHK_NAME_UNIQUE a. b.
426 --
427 per_asr_bus.chk_name_unique
428 (p_name => p_rec.name
429 ,p_assessment_group_id => p_rec.assessment_group_id
430 ,p_business_group_id => p_rec.business_group_id
431 ,p_object_version_number => p_rec.object_version_number
432 );
433 --
434 -- Call descriptive flexfield validation routines
435 --
436 per_asr_bus.chk_df(p_rec => p_rec);
437 --
438 hr_utility.set_location(' Leaving:'||l_proc, 10);
439 End update_validate;
440 --
441 -- ----------------------------------------------------------------------------
442 -- |---------------------------< delete_validate >----------------------------|
443 -- ----------------------------------------------------------------------------
444 Procedure delete_validate(p_rec in per_asr_shd.g_rec_type) is
445 --
446 l_proc varchar2(72) := g_package||'delete_validate';
447 --
448 Begin
449 hr_utility.set_location('Entering:'||l_proc, 5);
450 --
451 -- Call all supporting business operations
452 per_asr_bus.chk_ref_rows_asn
453 (p_assessment_group_id => p_rec.assessment_group_id);
454 --
455 hr_utility.set_location(' Leaving:'||l_proc, 10);
456 End delete_validate;
457 --
458 -- ----------------------------------------------------------------------------
459 -- |-----------------------< return_legislation_code >-------------------------|
460 -- ----------------------------------------------------------------------------
461 Function return_legislation_code
462 ( p_assessment_group_id in number
463 ) return varchar2 is
464 --
465 -- Declare cursor
466 --
467 cursor csr_leg_code is
468 select legislation_code
469 from per_business_groups pbg,
470 per_assessment_groups pag
471 where pag.assessment_group_id = p_assessment_group_id
472 and pbg.business_group_id = pag.business_group_id;
473
474 l_proc varchar2(72) := g_package||'return_legislation_code';
475 l_legislation_code varchar2(150);
476 --
477 Begin
478 hr_utility.set_location('Entering:'||l_proc, 5);
479 --
480 -- Ensure that all the mandatory parameters are not null
481 --
485 if nvl(g_assessment_group_id, hr_api.g_number) = p_assessment_group_id then
482 hr_api.mandatory_arg_error (p_api_name => l_proc,
483 p_argument => 'assessment_group_id',
484 p_argument_value => p_assessment_group_id );
486 --
487 -- The legislation code has already been found with a previous
488 -- call to this function. Just return the value in the global
489 -- variable.
490 --
491 l_legislation_code := g_legislation_code;
492 hr_utility.set_location(l_proc, 10);
493 else
494 --
495 -- The ID is different to the last call to this function
496 -- or this is the first call to this function.
497 --
498 open csr_leg_code;
499 fetch csr_leg_code into l_legislation_code;
500 if csr_leg_code%notfound then
501 close csr_leg_code;
502 --
503 -- The primary key is invalid therefore we must error out
504 --
505 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
506 hr_utility.raise_error;
507 end if;
508 --
509 close csr_leg_code;
510 --
511 g_assessment_group_id:= p_assessment_group_id;
512 g_legislation_code := l_legislation_code;
513 end if;
514 hr_utility.set_location(' Leaving:'|| l_proc, 15);
515 --
516 return l_legislation_code;
517 --
518 hr_utility.set_location(' Leaving:'||l_proc, 20);
519 --
520 End return_legislation_code;
521 --
522 --
523 end per_asr_bus;