1 Package Body ben_beo_bus as
2 /* $Header: bebeorhi.pkb 120.0.12010000.3 2008/08/05 14:07:36 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_beo_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_elig_obj_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_elig_obj_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 , ben_elig_obj_f beo
32 where beo.elig_obj_id = p_elig_obj_id
33 and pbg.business_group_id = beo.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 => 'elig_obj_id'
50 ,p_argument_value => p_elig_obj_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,'ELIG_OBJ_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_elig_obj_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 , ben_elig_obj_f beo
102 where beo.elig_obj_id = p_elig_obj_id
103 and pbg.business_group_id = beo.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 => 'elig_obj_id'
119 ,p_argument_value => p_elig_obj_id
120 );
121 --
122 if ( nvl(ben_beo_bus.g_elig_obj_id, hr_api.g_number)
123 = p_elig_obj_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 := ben_beo_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;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 ben_beo_bus.g_elig_obj_id := p_elig_obj_id;
154 ben_beo_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 -- ----------------------------------------------------------------------------
162 -- |-----------------------< chk_non_updateable_args >------------------------|
163 -- ----------------------------------------------------------------------------
164 -- {Start Of Comments}
165 --
166 -- Description:
167 -- This procedure is used to ensure that non updateable attributes have
168 -- not been updated. If an attribute has been updated an error is generated.
169 --
170 -- Pre Conditions:
171 -- g_old_rec has been populated with details of the values currently in
172 -- the database.
173 --
174 -- In Arguments:
175 -- p_rec has been populated with the updated values the user would like the
176 -- record set to.
177 --
178 -- Post Success:
179 -- Processing continues if all the non updateable attributes have not
180 -- changed.
181 --
182 -- Post Failure:
183 -- An application error is raised if any of the non updatable attributes
184 -- have been altered.
185 --
186 -- {End Of Comments}
187 -- ----------------------------------------------------------------------------
188 Procedure chk_non_updateable_args
189 (p_effective_date in date
190 ,p_rec in ben_beo_shd.g_rec_type
191 ) IS
192 --
193 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
194 --
195 Begin
196 --
197 -- Only proceed with the validation if a row exists for the current
198 -- record in the HR Schema.
199 --
200 IF NOT ben_beo_shd.api_updating
201 (p_elig_obj_id => p_rec.elig_obj_id
202 ,p_effective_date => p_effective_date
203 ,p_object_version_number => p_rec.object_version_number
204 ) THEN
205 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
206 fnd_message.set_token('PROCEDURE ', l_proc);
207 fnd_message.set_token('STEP ', '5');
208 fnd_message.raise_error;
209 END IF;
210 --
211 -- EDIT_HERE: Add checks to ensure non-updateable args have
212 -- not been updated.
213 --
214 End chk_non_updateable_args;
215 --
216 -- ----------------------------------------------------------------------------
217 -- |--------------------------< dt_update_validate >--------------------------|
218 -- ----------------------------------------------------------------------------
219 -- {Start Of Comments}
220 --
221 -- Description:
222 -- This procedure is used for referential integrity of datetracked
223 -- parent entities when a datetrack update operation is taking place
224 -- and where there is no cascading of update defined for this entity.
225 --
226 -- Prerequisites:
227 -- This procedure is called from the update_validate.
228 --
229 -- In Parameters:
230 --
231 -- Post Success:
232 -- Processing continues.
233 --
234 -- Post Failure:
235 --
236 -- Developer Implementation Notes:
237 -- This procedure should not need maintenance unless the HR Schema model
238 -- changes.
239 --
240 -- Access Status:
241 -- Internal Row Handler Use Only.
242 --
243 -- {End Of Comments}
244 -- ----------------------------------------------------------------------------
245 Procedure dt_update_validate
246 (p_table_name in varchar2
247 ,p_column_name in varchar2
248 ,p_column_value in varchar2 default hr_api.g_varchar2
249 ,p_datetrack_mode in varchar2
250 ,p_validation_start_date in date
251 ,p_validation_end_date in date
252 ) Is
253 --
254 l_proc varchar2(72) := g_package||'dt_update_validate';
255 --
256 Begin
257 --
258 -- Ensure that the p_datetrack_mode argument is not null
259 --
260 hr_api.mandatory_arg_error
261 (p_api_name => l_proc
262 ,p_argument => 'datetrack_mode'
263 ,p_argument_value => p_datetrack_mode
264 );
265 --
266 -- Mode will be valid, as this is checked at the start of the upd.
267 --
268 -- Ensure the arguments are not null
269 --
270 hr_api.mandatory_arg_error
271 (p_api_name => l_proc
272 ,p_argument => 'validation_start_date'
273 ,p_argument_value => p_validation_start_date
274 );
275 --
276 hr_api.mandatory_arg_error
277 (p_api_name => l_proc
278 ,p_argument => 'validation_end_date'
279 ,p_argument_value => p_validation_end_date
280 );
281 --
282 If ((nvl(p_column_value, hr_api.g_varchar2) <> hr_api.g_varchar2) and
283 NOT (dt_api.check_min_max_dates
284 (p_base_table_name => p_table_name
285 ,p_base_key_column => p_column_name
286 ,p_base_key_value => p_column_value
287 ,p_from_date => p_validation_start_date
288 ,p_to_date => p_validation_end_date))) Then
289 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
290 fnd_message.set_token('TABLE_NAME','elig obj');
291 hr_multi_message.add
292 (p_associated_column1 => ben_beo_shd.g_tab_nam || '.column_value');
293 End If;
294 --
295 Exception
296 When Others Then
297 --
298 -- An unhandled or unexpected error has occurred which
299 -- we must report
300 --
301 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
302 fnd_message.set_token('PROCEDURE', l_proc);
303 fnd_message.set_token('STEP','15');
304 fnd_message.raise_error;
305 End dt_update_validate;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |--------------------------< dt_delete_validate >--------------------------|
309 -- ----------------------------------------------------------------------------
310 -- {Start Of Comments}
311 --
312 -- Description:
313 -- This procedure is used for referential integrity of datetracked
314 -- child entities when either a datetrack DELETE or ZAP is in operation
315 -- and where there is no cascading of delete defined for this entity.
316 -- For the datetrack mode of DELETE or ZAP we must ensure that no
317 -- datetracked child rows exist between the validation start and end
318 -- dates.
319 --
320 -- Prerequisites:
321 -- This procedure is called from the delete_validate.
322 --
323 -- In Parameters:
324 --
325 -- Post Success:
326 -- Processing continues.
327 --
328 -- Post Failure:
329 -- If a row exists by determining the returning Boolean value from the
330 -- generic dt_api.rows_exist function then we must supply an error via
331 -- the use of the local exception handler l_rows_exist.
332 --
333 -- Developer Implementation Notes:
334 -- This procedure should not need maintenance unless the HR Schema model
335 -- changes.
336 --
337 -- Access Status:
338 -- Internal Row Handler Use Only.
339 --
340 -- {End Of Comments}
341 -- ----------------------------------------------------------------------------
342 Procedure dt_delete_validate
343 (p_elig_obj_id in number
344 ,p_datetrack_mode in varchar2
345 ,p_validation_start_date in date
346 ,p_validation_end_date in date
347 ) Is
348 --
349 l_proc varchar2(72) := g_package||'dt_delete_validate';
350 --
351 Begin
352 --
353 -- Ensure that the p_datetrack_mode argument is not null
354 --
355 hr_api.mandatory_arg_error
356 (p_api_name => l_proc
357 ,p_argument => 'datetrack_mode'
358 ,p_argument_value => p_datetrack_mode
359 );
360 --
361 -- Only perform the validation if the datetrack mode is either
362 -- DELETE or ZAP
363 --
364 If (p_datetrack_mode = hr_api.g_delete or
365 p_datetrack_mode = hr_api.g_zap) then
366 --
367 --
368 -- Ensure the arguments are not null
369 --
370 hr_api.mandatory_arg_error
371 (p_api_name => l_proc
372 ,p_argument => 'validation_start_date'
373 ,p_argument_value => p_validation_start_date
374 );
375 --
376 hr_api.mandatory_arg_error
377 (p_api_name => l_proc
378 ,p_argument => 'validation_end_date'
379 ,p_argument_value => p_validation_end_date
380 );
381 --
382 hr_api.mandatory_arg_error
383 (p_api_name => l_proc
384 ,p_argument => 'elig_obj_id'
385 ,p_argument_value => p_elig_obj_id
386 );
387 --
388 --
389 --
390 End If;
391 --
392 Exception
393 When Others Then
394 --
395 -- An unhandled or unexpected error has occurred which
396 -- we must report
397 --
398 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
399 fnd_message.set_token('PROCEDURE', l_proc);
400 fnd_message.set_token('STEP','15');
401 fnd_message.raise_error;
402 --
403 End dt_delete_validate;
404 --
405 -- ----------------------------------------------------------------------------
406 -- |---------------------------< insert_validate >----------------------------|
407 -- ----------------------------------------------------------------------------
408 Procedure insert_validate
409 (p_rec in ben_beo_shd.g_rec_type
410 ,p_effective_date in date
411 ,p_datetrack_mode in varchar2
412 ,p_validation_start_date in date
413 ,p_validation_end_date in date
414 ) is
415 --
416 l_proc varchar2(72) := g_package||'insert_validate';
417 --
418 Begin
419 hr_utility.set_location('Entering:'||l_proc, 5);
420 --
421 -- Call all supporting business operations
422 --
423 hr_api.validate_bus_grp_id
424 (p_business_group_id => p_rec.business_group_id
425 ,p_associated_column1 => ben_beo_shd.g_tab_nam
426 || '.BUSINESS_GROUP_ID');
427 --
428 -- After validating the set of important attributes,
429 -- if Multiple Message detection is enabled and at least
430 -- one error has been found then abort further validation.
431 --
432 hr_multi_message.end_validation_set;
433 --
434 hr_utility.set_location(' Leaving:'||l_proc, 10);
435 End insert_validate;
436 --
437 -- ----------------------------------------------------------------------------
438 -- |---------------------------< update_validate >----------------------------|
439 -- ----------------------------------------------------------------------------
440 Procedure update_validate
441 (p_rec in ben_beo_shd.g_rec_type
442 ,p_effective_date in date
443 ,p_datetrack_mode in varchar2
444 ,p_validation_start_date in date
445 ,p_validation_end_date in date
446 ) is
447 --
448 l_proc varchar2(72) := g_package||'update_validate';
449 --
450 Begin
451 hr_utility.set_location('Entering:'||l_proc, 5);
452 --
453 -- Call all supporting business operations
454 --
455 hr_api.validate_bus_grp_id
456 (p_business_group_id => p_rec.business_group_id
457 ,p_associated_column1 => ben_beo_shd.g_tab_nam
458 || '.BUSINESS_GROUP_ID');
459 --
460 -- After validating the set of important attributes,
461 -- if Multiple Message detection is enabled and at least
462 -- one error has been found then abort further validation.
463 --
464 hr_multi_message.end_validation_set;
465 --
466 -- Validate Dependent Attributes
467 --
468 -- Call the datetrack update integrity operation
469 --
470 dt_update_validate
471 (p_table_name => p_rec.table_name
472 ,p_column_name => p_rec.column_name
473 ,p_column_value => p_rec.column_value
474 ,p_datetrack_mode => p_datetrack_mode
475 ,p_validation_start_date => p_validation_start_date
476 ,p_validation_end_date => p_validation_end_date
477 );
478 --
479 chk_non_updateable_args
480 (p_effective_date => p_effective_date
481 ,p_rec => p_rec
482 );
483 --
484 --
485 hr_utility.set_location(' Leaving:'||l_proc, 10);
486 End update_validate;
487 --
488 -- ----------------------------------------------------------------------------
489 -- |---------------------------< delete_validate >----------------------------|
490 -- ----------------------------------------------------------------------------
491 Procedure delete_validate
492 (p_rec in ben_beo_shd.g_rec_type
493 ,p_effective_date in date
494 ,p_datetrack_mode in varchar2
495 ,p_validation_start_date in date
496 ,p_validation_end_date in date
497 ) is
498 --
499 l_proc varchar2(72) := g_package||'delete_validate';
500 --
501 Begin
502 hr_utility.set_location('Entering:'||l_proc, 5);
503 --
504 -- Call all supporting business operations
505 --
506 dt_delete_validate
507 (p_datetrack_mode => p_datetrack_mode
508 ,p_validation_start_date => p_validation_start_date
509 ,p_validation_end_date => p_validation_end_date
510 ,p_elig_obj_id => p_rec.elig_obj_id
511 );
512 --
513 hr_utility.set_location(' Leaving:'||l_proc, 10);
514 End delete_validate;
515 --
516 end ben_beo_bus;