[Home] [Help]
PACKAGE BODY: APPS.BEN_XCR_BUS
Source
1 Package Body ben_xcr_bus as
2 /* $Header: bexcrrhi.pkb 120.0 2005/05/28 12:25:40 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xcr_bus.'; -- Global package name
9
10 --
11 -- ---------------------------------------------------------------------------
12 -- |----------------------< set_security_group_id >--------------------------|
13 -- ---------------------------------------------------------------------------
14 --
15 Procedure set_security_group_id
16 (p_ext_crit_prfl_id in number
17 ) is
18 --
19 -- Declare cursor
20 --
21 cursor csr_sec_grp is
22 select pbg.security_group_id
23 from per_business_groups pbg
24 , ben_ext_crit_prfl xcr
25 where xcr.ext_crit_prfl_id = p_ext_crit_prfl_id
26 and pbg.business_group_id = xcr.business_group_id;
27 --
28 -- Declare local variables
29 --
30 l_security_group_id number;
31 l_proc varchar2(72) := g_package||'set_security_group_id';
32 --
33 begin
34 --
35 hr_utility.set_location('Entering:'|| l_proc, 10);
36 --
37 -- Ensure that all the mandatory parameter are not null
38 --
39 hr_api.mandatory_arg_error
40 (p_api_name => l_proc
41 ,p_argument => 'ext_crit_prfl_id'
42 ,p_argument_value => p_ext_crit_prfl_id
43 );
44 --
45 open csr_sec_grp;
46 fetch csr_sec_grp into l_security_group_id;
47 --
48 if csr_sec_grp%notfound then
49 --
50 close csr_sec_grp;
51 --
52 -- The primary key is invalid therefore we must error
53 --
54 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
55 fnd_message.raise_error;
56 --
57 end if;
58 close csr_sec_grp;
59 --
60 -- Set the security_group_id in CLIENT_INFO
61 --
62 hr_api.set_security_group_id
63 (p_security_group_id => l_security_group_id
64 );
65 --
66 hr_utility.set_location(' Leaving:'|| l_proc, 20);
67 --
68 end set_security_group_id;
69
70 --
71 -- ----------------------------------------------------------------------------
72 -- |----------------------< chk_startup_action >------------------------------|
73 -- ----------------------------------------------------------------------------
74 --
75 -- Description:
76 -- This procedure will check that the current action is allowed according
77 -- to the current startup mode.
78 --
79 -- ----------------------------------------------------------------------------
80 PROCEDURE chk_startup_action
81 (p_insert IN boolean
82 ,p_business_group_id IN number
83 ,p_legislation_code IN varchar2
84 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
85 --
86 BEGIN
87 --
88 -- Call the supporting procedure to check startup mode
89 --
90 IF (p_insert) THEN
91 --
92 -- Call procedure to check startup_action for inserts.
93 --
94 hr_startup_data_api_support.chk_startup_action
95 (p_generic_allowed => TRUE
96 ,p_startup_allowed => TRUE
97 ,p_user_allowed => TRUE
98 ,p_business_group_id => p_business_group_id
99 ,p_legislation_code => p_legislation_code
100 ,p_legislation_subgroup => p_legislation_subgroup
101 );
102 ELSE
103 --
104 -- Call procedure to check startup_action for updates and deletes.
105 --
106 hr_startup_data_api_support.chk_upd_del_startup_action
107 (p_generic_allowed => TRUE
108 ,p_startup_allowed => TRUE
109 ,p_user_allowed => TRUE
110 ,p_business_group_id => p_business_group_id
111 ,p_legislation_code => p_legislation_code
112 ,p_legislation_subgroup => p_legislation_subgroup
113 );
114 END IF;
115 --
116 END chk_startup_action;
117
118 --
119 -- ----------------------------------------------------------------------------
120 -- |------< chk_ext_crit_prfl_id >------|
121 -- ----------------------------------------------------------------------------
122 --
123 -- Description
124 -- This procedure is used to check that the primary key for the table
125 -- is created properly. It should be null on insert and
126 -- should not be able to be updated.
127 --
128 -- Pre Conditions
129 -- None.
130 --
131 -- In Parameters
132 -- ext_crit_prfl_id PK of record being inserted or updated.
133 -- object_version_number Object version number of record being
134 -- inserted or updated.
135 --
136 -- Post Success
137 -- Processing continues
138 --
139 -- Post Failure
140 -- Errors handled by the procedure
141 --
142 -- Access Status
143 -- Internal table handler use only.
144 --
145 Procedure chk_ext_crit_prfl_id(p_ext_crit_prfl_id in number,
146 p_object_version_number in number) is
147 --
148 l_proc varchar2(72) := g_package||'chk_ext_crit_prfl_id';
149 l_api_updating boolean;
150 --
151 Begin
152 --
153 hr_utility.set_location('Entering:'||l_proc, 5);
154 --
155 l_api_updating := ben_xcr_shd.api_updating
156 (p_ext_crit_prfl_id => p_ext_crit_prfl_id,
157 p_object_version_number => p_object_version_number);
158 --
159 if (l_api_updating
160 and nvl(p_ext_crit_prfl_id,hr_api.g_number)
161 <> ben_xcr_shd.g_old_rec.ext_crit_prfl_id) then
162 --
163 -- raise error as PK has changed
164 --
165 ben_xcr_shd.constraint_error('BEN_EXT_CRIT_PRFL_PK');
166 --
167 elsif not l_api_updating then
168 --
169 -- check if PK is null
170 --
171 if p_ext_crit_prfl_id is not null then
172 --
173 -- raise error as PK is not null
174 --
175 ben_xcr_shd.constraint_error('BEN_EXT_CRIT_PRFL_PK');
176 --
177 end if;
178 --
179 end if;
180 --
181 hr_utility.set_location('Leaving:'||l_proc, 10);
182 --
183 End chk_ext_crit_prfl_id;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |------------------------< chk_name_unique >-------------------------------|
187 -- ----------------------------------------------------------------------------
188 --
189 -- Description
190 -- ensure that the Profile Name is unique
191 -- within business_group
192 --
193 -- Pre Conditions
194 -- None.
195 --
196 -- In Parameters
197 -- p_name is Profile name
198 -- p_ext_crit_prfl_id
199 -- p_business_group_id
200 -- p_object_version_number
201 --
202 -- Post Success
203 -- Processing continues
204 --
205 -- Post Failure
206 -- Errors handled by the procedure
207 --
208 -- Access Status
209 -- Internal table handler use only.
210 --
211 -- ----------------------------------------------------------------------------
212 Procedure chk_name_unique
213 ( p_ext_crit_prfl_id in number
214 ,p_name in varchar2
215 ,p_business_group_id in number
216 ,p_legislation_code in varchar2
217 ,p_object_version_number in number) is
218 --
219 l_proc varchar2(72) := g_package||'chk_name_unique';
220 l_dummy char(1);
221 l_api_updating boolean;
222 --
223 cursor c1 is select null
224 from ben_ext_crit_prfl a
225 Where a.ext_crit_prfl_id <> nvl(p_ext_crit_prfl_id,hr_api.g_number)
226 and a.name = p_name
227 and ((business_group_id is null and legislation_code is null)
228 or (legislation_code is not null
229 and business_group_id is null
230 and legislation_code = p_legislation_code)
231 or (business_group_id is not null
232 and business_group_id = p_business_group_id)
233 );
234 --
235 Begin
236 hr_utility.set_location('Entering:'||l_proc, 5);
237 --
238 l_api_updating := ben_xcr_shd.api_updating
239 (p_ext_crit_prfl_id => p_ext_crit_prfl_id,
240 p_object_version_number => p_object_version_number);
241 --
242 if (l_api_updating
243 and nvl(p_name,hr_api.g_varchar2)
244 <> ben_xcr_shd.g_old_rec.name
245 or not l_api_updating) then
246 --
247 open c1;
248 fetch c1 into l_dummy;
249 if c1%found then
250 close c1;
251 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
252 fnd_message.raise_error;
253 end if;
254 end if;
255 --
256 hr_utility.set_location('Leaving:'||l_proc, 15);
257 End chk_name_unique;
258 --
259
260
261 Procedure chk_ext_global_flag
262 ( p_ext_crit_prfl_id in number
263 ,p_ext_global_flag in varchar2
264 ,p_business_group_id in number
265 ,p_legislation_code in varchar2
266 ,p_object_version_number in number
267 ,p_effective_date in date
268 ) is
269 --
270 l_proc varchar2(72) := g_package||'chk_ext_global_flag';
271 l_dummy char(1);
272 l_api_updating boolean;
273
274 cursor c is
275 select 'x'
276 from ben_ext_crit_val ecv ,
277 ben_ext_crit_typ ect
278 where ect.ext_Crit_prfl_id = p_ext_crit_prfl_id
279 and ecv.ext_crit_typ_id = ect.ext_crit_typ_id
280 and ecv.ext_crit_bg_id is not null
281 and ecv.ext_crit_bg_id <> ecv.business_group_id
282 ;
283 --
284 Begin
285 hr_utility.set_location('Entering:'||l_proc, 5);
286 --
287 l_api_updating := ben_xcr_shd.api_updating
288 (p_ext_crit_prfl_id => p_ext_crit_prfl_id,
289 p_object_version_number => p_object_version_number);
290 --
291 if (l_api_updating
292 and nvl(p_ext_global_flag,hr_api.g_varchar2)
293 <> ben_xcr_shd.g_old_rec.ext_global_flag
294 or not l_api_updating) then
295
296
297 if p_business_group_id is not null then
298 /* BG is set, so use the existing call, with no modifications*/
299 if hr_api.not_exists_in_hr_lookups
300 (p_lookup_type => 'YES_NO',
301 p_lookup_code => p_ext_global_flag,
302 p_effective_date => p_effective_date) then
303 --
304 -- raise error message
305 --
306 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
307 fnd_message.set_token('FIELD', 'p_ext_global_flag');
308 fnd_message.set_token('TYPE', 'YES_NO');
309 fnd_message.raise_error;
310 --
311 end if;
312 else
313 /* BG is null, so alternative call is required */
314 if hr_api.not_exists_in_hrstanlookups
315 (p_lookup_type => 'YES_NO',
316 p_lookup_code => p_ext_global_flag,
317 p_effective_date => p_effective_date) then
318 --
319 -- raise error message
320 --
321 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
322 fnd_message.set_token('FIELD', 'p_ext_global_flag');
323 fnd_message.set_token('TYPE', 'YES_NO');
324 fnd_message.raise_error;
325 --
326 end if;
327 end if ;
328
329 --- when the old flag is 'Y' and current flag 'N' then
330 -- make sure no child belongs to global
331 if ben_xcr_shd.g_old_rec.ext_global_flag = 'Y' and p_ext_global_flag = 'N' then
332 open c ;
333 fetch c into l_dummy ;
334 if c%found then
335 --- create new error
336 fnd_message.set_name('BEN', 'BEN_92775_CHILD_REC_EXISTS');
337 fnd_message.raise_error;
338
339 end if ;
340 close c ;
341 end if ;
342
343 end if;
344 --
345 hr_utility.set_location('Leaving:'||l_proc, 15);
346 End chk_ext_global_flag;
347
348
349
350 -- ----------------------------------------------------------------------------
351 -- |---------------------------< insert_validate >----------------------------|
352 -- ----------------------------------------------------------------------------
353 Procedure insert_validate(p_rec in ben_xcr_shd.g_rec_type) is
354 --
355 l_proc varchar2(72) := g_package||'insert_validate';
356 --
357 Begin
358 hr_utility.set_location('Entering:'||l_proc, 5);
359 --
360 -- Call all supporting business operations
361 --
362 --
363 chk_startup_action(True
364 ,p_rec.business_group_id
365 ,p_rec.legislation_code);
366 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
367 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
368 END IF;
369 --
370 chk_ext_crit_prfl_id
371 (p_ext_crit_prfl_id => p_rec.ext_crit_prfl_id,
372 p_object_version_number => p_rec.object_version_number);
373 --
374 chk_name_unique
375 ( p_ext_crit_prfl_id => p_rec.ext_crit_prfl_id
376 ,p_name => p_rec.name
377 ,p_business_group_id => p_rec.business_group_id
378 ,p_legislation_code => p_rec.legislation_code
379 ,p_object_version_number => p_rec.object_version_number);
380 --
381
382
383 chk_ext_global_flag
384 ( p_ext_crit_prfl_id => p_rec.ext_crit_prfl_id
385 ,p_ext_global_flag => p_rec.ext_global_flag
386 ,p_business_group_id => p_rec.business_group_id
387 ,p_legislation_code => p_rec.legislation_code
388 ,p_object_version_number => p_rec.object_version_number
389 ,p_effective_date => trunc(sysdate)
390 ) ;
391
392
393
394 hr_utility.set_location(' Leaving:'||l_proc, 10);
395 End insert_validate;
396 --
397 -- ----------------------------------------------------------------------------
398 -- |---------------------------< update_validate >----------------------------|
399 -- ----------------------------------------------------------------------------
400 Procedure update_validate(p_rec in ben_xcr_shd.g_rec_type) is
401 --
402 l_proc varchar2(72) := g_package||'update_validate';
403 --
404 Begin
405 hr_utility.set_location('Entering:'||l_proc, 5);
406 --
407 -- Call all supporting business operations
408 --
409 --
410 chk_startup_action(False
411 ,p_rec.business_group_id
412 ,p_rec.legislation_code);
413 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
414 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
415 END IF;
416 --
417 chk_ext_crit_prfl_id
418 (p_ext_crit_prfl_id => p_rec.ext_crit_prfl_id,
419 p_object_version_number => p_rec.object_version_number);
420 --
421 chk_name_unique
422 ( p_ext_crit_prfl_id => p_rec.ext_crit_prfl_id
423 ,p_name => p_rec.name
424 ,p_business_group_id => p_rec.business_group_id
425 ,p_legislation_code => p_rec.legislation_code
426 ,p_object_version_number => p_rec.object_version_number);
427 --
428
429 chk_ext_global_flag
430 ( p_ext_crit_prfl_id => p_rec.ext_crit_prfl_id
431 ,p_ext_global_flag => p_rec.ext_global_flag
432 ,p_business_group_id => p_rec.business_group_id
433 ,p_legislation_code => p_rec.legislation_code
434 ,p_object_version_number => p_rec.object_version_number
435 ,p_effective_date => trunc(sysdate)
436 ) ;
437
438
439 hr_utility.set_location(' Leaving:'||l_proc, 10);
440 End update_validate;
441 --
442 -- ----------------------------------------------------------------------------
443 -- |---------------------------< delete_validate >----------------------------|
444 -- ----------------------------------------------------------------------------
445 Procedure delete_validate(p_rec in ben_xcr_shd.g_rec_type) is
446 --
447 l_proc varchar2(72) := g_package||'delete_validate';
448 --
449 Begin
450 hr_utility.set_location('Entering:'||l_proc, 5);
451 --
452 -- Call all supporting business operations
453 --
454 chk_startup_action(False
455 ,ben_xcr_shd.g_old_rec.business_group_id
456 ,ben_xcr_shd.g_old_rec.legislation_code);
457 --
458 hr_utility.set_location(' Leaving:'||l_proc, 10);
459 End delete_validate;
460 --
461 --
462 -- ---------------------------------------------------------------------------
463 -- |---------------------< return_legislation_code >-------------------------|
464 -- ---------------------------------------------------------------------------
465 --
466 function return_legislation_code
467 (p_ext_crit_prfl_id in number) return varchar2 is
468 --
469 -- Declare cursor
470 --
471 cursor csr_leg_code is
472 select a.legislation_code
473 from per_business_groups a,
474 ben_ext_crit_prfl b
475 where b.ext_crit_prfl_id = p_ext_crit_prfl_id
476 and a.business_group_id(+) = b.business_group_id;
477 --
478 -- Declare local variables
479 --
480 l_legislation_code varchar2(150);
481 l_proc varchar2(72) := g_package||'return_legislation_code';
482 --
483 begin
484 --
485 hr_utility.set_location('Entering:'|| l_proc, 10);
486 --
487 -- Ensure that all the mandatory parameter are not null
488 --
489 hr_api.mandatory_arg_error(p_api_name => l_proc,
490 p_argument => 'ext_crit_prfl_id',
491 p_argument_value => p_ext_crit_prfl_id);
492 --
493 open csr_leg_code;
494 --
495 fetch csr_leg_code into l_legislation_code;
496 --
497 if csr_leg_code%notfound then
498 --
499 close csr_leg_code;
500 --
501 -- The primary key is invalid therefore we must error
502 --
503 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
504 fnd_message.raise_error;
505 --
506 end if;
507 --
508 close csr_leg_code;
509 --
510 hr_utility.set_location(' Leaving:'|| l_proc, 20);
511 --
512 return l_legislation_code;
513 --
514 end return_legislation_code;
515 --
516 end ben_xcr_bus;