[Home] [Help]
PACKAGE BODY: APPS.PER_SPH_BUS
Source
3 --
1 Package Body per_sph_bus as
2 /* $Header: pesphrhi.pkb 120.4 2011/05/06 09:30:35 vkodedal noship $ */
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sph_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_plan_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_plan_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , per_sp_plan spp
32 where spp.plan_id = p_plan_id
33 and pbg.business_group_id = spp.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'plan_id'
50 ,p_argument_value => p_plan_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'PLAN_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_plan_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , per_sp_plan spp
102 where spp.plan_id = p_plan_id
103 and pbg.business_group_id = spp.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'plan_id'
119 ,p_argument_value => p_plan_id
120 );
121 --
122 if ( nvl(per_sph_bus.g_plan_id, hr_api.g_number)
123 = p_plan_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := per_sph_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
149 -- Set the global variables so the values are
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 per_sph_bus.g_plan_id := p_plan_id;
154 per_sph_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------------< chk_df >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Descriptive Flexfield structure column and data values are
176 -- all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Descriptive Flexfield structure column value or any of
184 -- Access Status:
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_df
189 (p_rec in per_sph_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_df';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.plan_id is not null) and (
198 nvl(per_sph_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(per_sph_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(per_sph_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(per_sph_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(per_sph_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(per_sph_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(per_sph_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
214 nvl(per_sph_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(per_sph_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(per_sph_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(per_sph_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(per_sph_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(per_sph_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(per_sph_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(per_sph_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(per_sph_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(per_sph_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(per_sph_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(per_sph_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(per_sph_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(per_sph_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
240 or (p_rec.plan_id is null and p_rec.attribute_category IS NOT NULL) then
241 --
242 -- Only execute the validation if absolutely necessary:
243 -- a) During update, the structure column value or any
244 -- of the attribute values have actually changed.
245 -- b) During insert.
246 --
247 hr_dflex_utility.ins_or_upd_descflex_attribs
248 (p_appl_short_name => 'PER'
249 ,p_descflex_name => 'PER_SUCCESSION_PLANNING'
250 ,p_attribute_category => p_rec.attribute_category
251 ,p_attribute1_name => 'ATTRIBUTE1'
252 ,p_attribute1_value => p_rec.attribute1
253 ,p_attribute2_name => 'ATTRIBUTE2'
254 ,p_attribute2_value => p_rec.attribute2
255 ,p_attribute3_name => 'ATTRIBUTE3'
256 ,p_attribute3_value => p_rec.attribute3
257 ,p_attribute4_name => 'ATTRIBUTE4'
258 ,p_attribute4_value => p_rec.attribute4
259 ,p_attribute5_name => 'ATTRIBUTE5'
260 ,p_attribute5_value => p_rec.attribute5
261 ,p_attribute6_name => 'ATTRIBUTE6'
262 ,p_attribute6_value => p_rec.attribute6
263 ,p_attribute7_name => 'ATTRIBUTE7'
264 ,p_attribute7_value => p_rec.attribute7
265 ,p_attribute8_name => 'ATTRIBUTE8'
266 ,p_attribute8_value => p_rec.attribute8
267 ,p_attribute9_name => 'ATTRIBUTE9'
268 ,p_attribute9_value => p_rec.attribute9
269 ,p_attribute10_name => 'ATTRIBUTE10'
270 ,p_attribute10_value => p_rec.attribute10
271 ,p_attribute11_name => 'ATTRIBUTE11'
272 ,p_attribute11_value => p_rec.attribute11
273 ,p_attribute12_name => 'ATTRIBUTE12'
274 ,p_attribute12_value => p_rec.attribute12
275 ,p_attribute13_name => 'ATTRIBUTE13'
276 ,p_attribute13_value => p_rec.attribute13
277 ,p_attribute14_name => 'ATTRIBUTE14'
278 ,p_attribute14_value => p_rec.attribute14
279 ,p_attribute15_name => 'ATTRIBUTE15'
280 ,p_attribute15_value => p_rec.attribute15
281 ,p_attribute16_name => 'ATTRIBUTE16'
282 ,p_attribute16_value => p_rec.attribute16
283 ,p_attribute17_name => 'ATTRIBUTE17'
284 ,p_attribute17_value => p_rec.attribute17
285 ,p_attribute18_name => 'ATTRIBUTE18'
286 ,p_attribute18_value => p_rec.attribute18
287 ,p_attribute19_name => 'ATTRIBUTE19'
288 ,p_attribute19_value => p_rec.attribute19
289 ,p_attribute20_name => 'ATTRIBUTE20'
290 ,p_attribute20_value => p_rec.attribute20
291 );
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc,20);
295 end chk_df;
296 --
297 -- ----------------------------------------------------------------------------
298 -- |-----------------------< chk_non_updateable_args >------------------------|
299 -- ----------------------------------------------------------------------------
300 -- {Start Of Comments}
301 --
302 -- Description:
303 -- This procedure is used to ensure that non updateable attributes have
304 -- not been updated. If an attribute has been updated an error is generated.
305 --
306 -- Pre Conditions:
307 -- g_old_rec has been populated with details of the values currently in
308 -- the database.
309 --
310 -- In Arguments:
311 -- p_rec has been populated with the updated values the user would like the
312 -- record set to.
313 --
314 -- Post Success:
315 -- Processing continues if all the non updateable attributes have not
316 -- changed.
317 --
318 -- Post Failure:
322 -- {End Of Comments}
319 -- An application error is raised if any of the non updatable attributes
320 -- have been altered.
321 --
323 -- ----------------------------------------------------------------------------
324 Procedure chk_non_updateable_args
325 (p_effective_date in date
326 ,p_rec in per_sph_shd.g_rec_type
327 ) IS
328 --
329 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
330 --
331 Begin
332 --
333 -- Only proceed with the validation if a row exists for the current
334 -- record in the HR Schema.
335 --
336 IF NOT per_sph_shd.api_updating
337 (p_plan_id => p_rec.plan_id
338 ,p_object_version_number => p_rec.object_version_number
339 ) THEN
340 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
341 fnd_message.set_token('PROCEDURE ', l_proc);
342 fnd_message.set_token('STEP ', '5');
343 fnd_message.raise_error;
344 END IF;
345 --
346 -- EDIT_HERE: Add checks to ensure non-updateable args have
347 -- not been updated.
348 --
349 End chk_non_updateable_args;
350
351 --
352 -- ----------------------------------------------------------------------------
353 -- |---------------------------< chkDates >----------------------------|
354 -- ----------------------------------------------------------------------------
355 Procedure chkDates
356 (p_start_date in date
357 ,p_end_date in date
358 ,p_rvw_date in date
359 ,p_filled_on in date
360 ) is
361 --
362 l_proc varchar2(72) := g_package||'chkDates';
363
364
365 --
366 Begin
367 hr_utility.set_location('Entering:'||l_proc, 5);
368
369 hr_api.mandatory_arg_error
370 (p_api_name => l_proc
371 ,p_argument => 'start_date'
372 ,p_argument_value => p_start_date
373 );
374
375 if p_start_date is not null and p_end_date is not null and p_start_date > p_end_date
376 then
377 hr_utility.set_location(l_proc, 20);
378 hr_utility.set_message (800, 'PER_33885_SP_PL_ENDDT_STRTDT');
379 fnd_msg_pub.add;
380 end if;
381
382 if p_start_date is not null and (p_rvw_date <= p_start_date or p_rvw_date > nvl(p_end_date, p_rvw_date) )
383 then
384 hr_utility.set_location(l_proc, 30);
385 hr_utility.set_message (800, 'PER_33886_SP_RVWDT_LESS_STRTDT');
386 fnd_msg_pub.add;
387 end if;
388
389 if p_filled_on is not null and (p_filled_on < p_start_date or p_filled_on > nvl(p_end_date,p_filled_on) )
390 then
391 hr_utility.set_location(l_proc, 40);
392 hr_utility.set_message (800, 'PER_33889_SP_FILLED_ON_DT_VAL');
393 fnd_msg_pub.add;
394
395 end if;
396
397 --
398 hr_utility.set_location(' Leaving:'||l_proc, 99);
399 End chkDates;
400
401 --
402 -- ----------------------------------------------------------------------------
403 -- |---------------------------< chkMaxPlans >----------------------------|
404 -- ----------------------------------------------------------------------------
405 Procedure chkMaxPlans
406 (p_successee_id in number,
407 p_successee_type in varchar2
408 ) is
409 --
410 l_proc varchar2(72) := g_package||'chkMaxPlans';
411 l_plan_count number;
412 l_max_plans number;
413 cursor csrSuccesseeDtls(p_successee_id number, p_successee_type varchar2) is
414 select dtls.max_plans
415 from per_sp_successee_details dtls
416 where dtls.successee_id=p_successee_id
417 and dtls.successee_type=p_successee_type;
418 --
419 Begin
420 hr_utility.set_location('Entering:'||l_proc, 5);
421
422 open csrSuccesseeDtls(p_successee_id, p_successee_type);
423 fetch csrSuccesseeDtls into l_max_plans;
424 if csrSuccesseeDtls%notfound
425 then
426 hr_utility.set_location(l_proc, 30);
427 close csrSuccesseeDtls;
428 return;
429 end if;
430 close csrSuccesseeDtls;
431
432 hr_utility.set_location('l_max_plans :'||l_max_plans, 40);
433
434 select count(*) into l_plan_count
435 from per_sp_plan
436 where successee_id=p_successee_id
437 and plan_type=p_successee_type
438 and status='A';
439
440 hr_utility.set_location('l_plan_count :'||l_plan_count, 50);
441
442 if l_plan_count >= l_max_plans
443 then
444 hr_utility.set_location(l_proc, 80);
445 hr_utility.set_message (800, 'PER_33887_SP_MAX_PLANS');
446 fnd_msg_pub.add;
447 end if;
448 --
449 hr_utility.set_location(' Leaving:'||l_proc, 99);
450 End chkMaxPlans;
451
452 --
453 -- ----------------------------------------------------------------------------
454 -- |---------------------------< insert_validate >----------------------------|
455 -- ----------------------------------------------------------------------------
456 Procedure insert_validate
457 (p_effective_date in date
458 ,p_rec in per_sph_shd.g_rec_type
459 ) is
460 --
461 l_proc varchar2(72) := g_package||'insert_validate';
462 --
463 Begin
464 hr_utility.set_location('Entering:'||l_proc, 5);
465 --
466 -- Call all supporting business operations
467 --
468 hr_api.validate_bus_grp_id
469 (p_business_group_id => p_rec.business_group_id
473
470 ,p_associated_column1 => per_sph_shd.g_tab_nam
471 || '.BUSINESS_GROUP_ID');
472
474 chkMaxPlans(p_rec.successee_id, p_rec.plan_type);
475
476 chkDates(p_rec.start_date, p_rec.end_date, p_rec.next_review_date, p_rec.filled_on);
477 --
478 -- After validating the set of important attributes,
479 -- if Multiple Message detection is enabled and at least
480 -- one error has been found then abort further validation.
481 --
482 hr_multi_message.end_validation_set;
483 --
484 -- Validate Dependent Attributes
485 --
489 hr_utility.set_location(' Leaving:'||l_proc, 10);
486 --
487 per_sph_bus.chk_df(p_rec);
488 --
490 End insert_validate;
491 --
492 -- ----------------------------------------------------------------------------
493 -- |---------------------------< update_validate >----------------------------|
494 -- ----------------------------------------------------------------------------
495 Procedure update_validate
496 (p_effective_date in date
497 ,p_rec in per_sph_shd.g_rec_type
498 ) is
499 --
500 l_proc varchar2(72) := g_package||'update_validate';
501 --
502 Begin
503 hr_utility.set_location('Entering:'||l_proc, 5);
504 --
505 -- Call all supporting business operations
506 --
507 hr_api.validate_bus_grp_id
508 (p_business_group_id => p_rec.business_group_id
509 ,p_associated_column1 => per_sph_shd.g_tab_nam
510 || '.BUSINESS_GROUP_ID');
511
512 chkDates(p_rec.start_date, p_rec.end_date, p_rec.next_review_date, p_rec.filled_on);
513
514 --
515 -- After validating the set of important attributes,
516 -- if Multiple Message detection is enabled and at least
517 -- one error has been found then abort further validation.
518 --
519 hr_multi_message.end_validation_set;
520 --
521 -- Validate Dependent Attributes
522 --
523 chk_non_updateable_args
524 (p_effective_date => p_effective_date
525 ,p_rec => p_rec
526 );
527 --
528 --
529 per_sph_bus.chk_df(p_rec);
530 --
531 hr_utility.set_location(' Leaving:'||l_proc, 10);
532 End update_validate;
533 --
534 -- ----------------------------------------------------------------------------
535 -- |---------------------------< delete_validate >----------------------------|
536 -- ----------------------------------------------------------------------------
537 Procedure delete_validate
538 (p_rec in per_sph_shd.g_rec_type
539 ) is
540 --
541 l_proc varchar2(72) := g_package||'delete_validate';
542 --
543 Begin
544 hr_utility.set_location('Entering:'||l_proc, 5);
545 --
546 -- Call all supporting business operations
547 --
548 hr_utility.set_location(' Leaving:'||l_proc, 10);
549 End delete_validate;
550 --
551 end per_sph_bus;