[Home] [Help]
PACKAGE BODY: APPS.BEN_DRR_BUS
Source
1 Package Body ben_drr_bus as
2 /* $Header: bedrrrhi.pkb 120.0 2005/05/28 01:40:22 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_drr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------< chk_dsgn_rqmt_rlshp_typ_id >-----------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- dsgn_rqmt_rlshp_typ_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_dsgn_rqmt_rlshp_typ_id
37 (p_dsgn_rqmt_rlshp_typ_id in number,
38 p_object_version_number in number) is
39 --
40 l_proc varchar2(72) := g_package||'chk_dsgn_rqmt_rlshp_typ_id';
41 l_api_updating boolean;
42 --
43 Begin
44 --
45 hr_utility.set_location('Entering:'||l_proc, 5);
46 --
47 l_api_updating := ben_drr_shd.api_updating
48 (p_dsgn_rqmt_rlshp_typ_id => p_dsgn_rqmt_rlshp_typ_id,
49 p_object_version_number => p_object_version_number);
50 --
51 if (l_api_updating
52 and nvl(p_dsgn_rqmt_rlshp_typ_id,hr_api.g_number)
53 <> ben_drr_shd.g_old_rec.dsgn_rqmt_rlshp_typ_id) then
54 --
55 -- raise error as PK has changed
56 --
57 ben_drr_shd.constraint_error('BEN_DSGN_RQMT_RLSHP_TYP_PK');
58 --
59 elsif not l_api_updating then
60 --
61 -- check if PK is null
62 --
63 if p_dsgn_rqmt_rlshp_typ_id is not null then
64 --
65 -- raise error as PK is not null
66 --
67 ben_drr_shd.constraint_error('BEN_DSGN_RQMT_RLSHP_TYP_PK');
68 --
69 end if;
70 --
71 end if;
72 --
73 hr_utility.set_location('Leaving:'||l_proc, 10);
74 --
75 End chk_dsgn_rqmt_rlshp_typ_id;
76 --
77 -- ----------------------------------------------------------------------------
78 -- |-------------------------< chk_dsgn_rqmt_id >-----------------------------|
79 -- ----------------------------------------------------------------------------
80 --
81 -- Description
82 -- This procedure checks that a referenced foreign key actually exists
83 -- in the referenced table.
84 --
85 -- Pre-Conditions
86 -- None.
87 --
88 -- In Parameters
89 -- p_dsgn_rqmt_rlshp_typ_id PK
90 -- p_dsgn_rqmt_id ID of FK column
91 -- p_effective_date Session Date of record
92 -- p_object_version_number object version number
93 --
94 -- Post Success
95 -- Processing continues
96 --
97 -- Post Failure
98 -- Error raised.
99 --
100 -- Access Status
101 -- Internal table handler use only.
102 --
103 Procedure chk_dsgn_rqmt_id (p_dsgn_rqmt_rlshp_typ_id in number,
104 p_dsgn_rqmt_id in number,
105 p_effective_date in date,
106 p_object_version_number in number) is
107 --
108 l_proc varchar2(72) := g_package||'chk_dsgn_rqmt_id';
109 l_api_updating boolean;
110 l_dummy varchar2(1);
111 --
112 cursor c1 is
113 select null
114 from ben_dsgn_rqmt_f a
115 where a.dsgn_rqmt_id = p_dsgn_rqmt_id
116 and p_effective_date
117 between a.effective_start_date
118 and a.effective_end_date;
119 --
120 Begin
121 --
122 hr_utility.set_location('Entering:'||l_proc,5);
123 --
124 l_api_updating := ben_drr_shd.api_updating
125 (p_dsgn_rqmt_rlshp_typ_id => p_dsgn_rqmt_rlshp_typ_id,
126 p_object_version_number => p_object_version_number);
127 --
128 if (l_api_updating
129 and nvl(p_dsgn_rqmt_id,hr_api.g_number)
130 <> nvl(ben_drr_shd.g_old_rec.dsgn_rqmt_id,hr_api.g_number)
131 or not l_api_updating) then
132 --
133 -- check if dsgn_rqmt_id value exists in ben_dsgn_rqmt_f table
134 --
135 open c1;
136 --
137 fetch c1 into l_dummy;
138 if c1%notfound then
139 --
140 close c1;
141 --
142 -- raise error as FK does not relate to PK in ben_dsgn_rqmt_f
143 -- table.
144 --
145 ben_drr_shd.constraint_error('BEN_DSGN_RQMT_RLSHP_TYP_DT1');
146 --
147 end if;
148 --
149 close c1;
150 --
151 end if;
152 --
153 hr_utility.set_location('Leaving:'||l_proc,10);
154 --
155 End chk_dsgn_rqmt_id;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |------------------------< chk_rlshp_typ_cd >------------------------------|
159 -- ----------------------------------------------------------------------------
160 --
161 -- Description
162 -- This procedure is used to check that the lookup value is valid.
163 --
164 -- Pre Conditions
165 -- None.
166 --
167 -- In Parameters
168 -- dsgn_rqmt_rlshp_typ_id PK of record being inserted or updated.
169 -- rlshp_typ_cd Value of lookup code.
170 -- effective_date effective date
171 -- object_version_number Object version number of record being
172 -- inserted or updated.
173 --
174 -- Post Success
175 -- Processing continues
176 --
177 -- Post Failure
178 -- Error handled by procedure
179 --
180 -- Access Status
181 -- Internal table handler use only.
182 --
183 Procedure chk_rlshp_typ_cd
184 (p_dsgn_rqmt_rlshp_typ_id in number,
185 p_rlshp_typ_cd in varchar2,
186 p_effective_date in date,
187 p_business_group_id in number,
188 p_object_version_number in number) is
189 --
190 l_proc varchar2(72) := g_package||'chk_rlshp_typ_cd';
191 l_api_updating boolean;
192 --
193 -- Bug 3686523
194 -- We need to validate the lookup values against the security group
195 --
196 l_dummy VARCHAR2(1);
197 --
198 CURSOR c_lookup_exists (
199 cv_business_group_id NUMBER,
200 cv_lookup_code VARCHAR2,
201 cv_effective_date DATE
202 )
203 IS
204 SELECT NULL
205 FROM fnd_lookup_types_vl flt, fnd_lookup_values_vl flv
206 WHERE flt.lookup_type = 'CONTACT'
207 AND ( flv.security_group_id = 0
208 OR flv.security_group_id IN (
209 SELECT security_group_id
210 FROM fnd_security_groups
211 WHERE security_group_key =
212 TO_CHAR (cv_business_group_id))
213 )
214 AND flt.lookup_type = flv.lookup_type
215 AND flt.security_group_id = flv.security_group_id
216 AND flv.lookup_code = cv_lookup_code
217 AND cv_effective_date BETWEEN NVL (flv.start_date_active,
218 cv_effective_date
219 )
220 AND NVL (flv.end_date_active,
221 cv_effective_date
222 );
223 --
224 Begin
225 --
226 hr_utility.set_location('Entering:'||l_proc, 5);
227 --
228 l_api_updating := ben_drr_shd.api_updating
229 (p_dsgn_rqmt_rlshp_typ_id => p_dsgn_rqmt_rlshp_typ_id,
230 p_object_version_number => p_object_version_number);
231 --
232 if (l_api_updating
233 and p_rlshp_typ_cd
234 <> nvl(ben_drr_shd.g_old_rec.rlshp_typ_cd,hr_api.g_varchar2)
235 or not l_api_updating)
236 and p_rlshp_typ_cd is not null then
237 --
238 -- check if value of lookup falls within lookup type.
239 --
240 /* Commented the following code for Bug 3686523
241 if hr_api.not_exists_in_hr_lookups
242 (p_lookup_type => 'CONTACT',
243 p_lookup_code => p_rlshp_typ_cd,
244 p_effective_date => p_effective_date) then
245 --
246 -- raise error as does not exist as lookup
247 --
248 --Bug 2736727 Changed parameter to 805 frm 'BEN' in hr_utility call.
249
250 hr_utility.set_message(805,'BEN_91964_INVLD_RLSHP_TYP');
251 hr_utility.raise_error;
252 --
253 end if;
254 */
255 open c_lookup_exists (cv_business_group_id => p_business_group_id,
256 cv_lookup_code => p_rlshp_typ_cd,
257 cv_effective_date => p_effective_date );
258 --
259 fetch c_lookup_exists into l_dummy;
260 --
261 if c_lookup_exists%notfound
262 then
263 --
264 close c_lookup_exists;
265 --
266 fnd_message.set_name('BEN', 'BEN_91964_INVLD_RLSHP_TYP');
267 fnd_message.raise_error;
268 --
269 end if;
270 --
271 close c_lookup_exists;
272 --
273 end if;
274 --
275 hr_utility.set_location('Leaving:'||l_proc,10);
276 --
277 end chk_rlshp_typ_cd;
278 --
279 --
280 -- bug 2837189
281 -- validate dupliaction of records
282 -- ----------------------------------------------------------------------------
283 -- |-------------------------< chk_dsgn_rqmt_rlshp_uniq >-----------------------------|
284 -- ----------------------------------------------------------------------------
285 --
286 -- Description
287 -- This procedure is used to enforce uniquness of the relationship type
288 --
289 -- Pre-Conditions
290 -- None.
291 --
292 -- In Parameters
293 -- dsgn_rqmt_rlshp_typ_id PK of record being inserted or updated
294 -- rlshp_typ_cd Relationship type code
295 -- dsgn_rqmt_id Foreign key of parent record
296 -- business_group_id Business group id
297 -- object_version_number Object version number of record being
298 -- inserted or updated.
299 --
300 -- Post Success
301 -- Processing continues
302 --
303 -- Post Failure
304 -- Error raised.
305 --
306 -- Access Status
307 -- Internal table handler use only.
308 --
309 Procedure chk_dsgn_rqmt_rlshp_uniq
310 (p_dsgn_rqmt_rlshp_typ_id in number,
311 p_rlshp_typ_cd in varchar2,
312 p_dsgn_rqmt_id in number,
313 p_business_group_id in number,
314 p_object_version_number in number) is
315 --
316 l_proc varchar2(72) := g_package||'chk_dsgn_rqmt_rlshp_uniq';
317 l_dummy varchar2(1);
318 --
319 cursor c_chk_uniq_dsgn_rqmt is
320 select 'X'
321 from ben_dsgn_rqmt_rlshp_typ
322 where dsgn_rqmt_id = p_dsgn_rqmt_id
323 and rlshp_typ_cd = p_rlshp_typ_cd
324 and business_group_id = p_business_group_id
325 and dsgn_rqmt_rlshp_typ_id <> nvl(p_dsgn_rqmt_rlshp_typ_id, -999);
326 --
327 Begin
328 --
329 hr_utility.set_location('Entering:'||l_proc,5);
330 --
331 --
332 -- check if dsgn_rqmt_id value exists in ben_dsgn_rqmt_f table
333 --
334 open c_chk_uniq_dsgn_rqmt;
335 --
336 fetch c_chk_uniq_dsgn_rqmt into l_dummy;
337 --
338 close c_chk_uniq_dsgn_rqmt;
339
340 if l_dummy is not null then
341 -- raise error
342 --
343 fnd_message.set_name('BEN','BEN_93355_RLSHP_TYP_UNIQUE');
344 fnd_message.raise_error;
345
346 --
347 end if;
348 --
349 --
350 hr_utility.set_location('Leaving:'||l_proc,10);
351 --
352 End chk_dsgn_rqmt_rlshp_uniq;
353 --
354 -- end bug 2837189
355
356 -- ----------------------------------------------------------------------------
357 -- |---------------------------< insert_validate >----------------------------|
358 -- ----------------------------------------------------------------------------
359 Procedure insert_validate(p_rec in ben_drr_shd.g_rec_type
360 ,p_effective_date in date) is
361 --
362 l_proc varchar2(72) := g_package||'insert_validate';
363 --
364 Begin
365 hr_utility.set_location('Entering:'||l_proc, 5);
366 --
367 -- Call all supporting business operations
368 --
369 --
370 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
371 --
372 chk_dsgn_rqmt_rlshp_typ_id
373 (p_dsgn_rqmt_rlshp_typ_id => p_rec.dsgn_rqmt_rlshp_typ_id,
374 p_object_version_number => p_rec.object_version_number);
375 --
376 chk_rlshp_typ_cd
377 (p_dsgn_rqmt_rlshp_typ_id => p_rec.dsgn_rqmt_rlshp_typ_id,
378 p_rlshp_typ_cd => p_rec.rlshp_typ_cd,
379 p_effective_date => p_effective_date,
380 p_business_group_id => p_rec.business_group_id,
381 p_object_version_number => p_rec.object_version_number);
382 --
383 --bug 2837189
384 chk_dsgn_rqmt_rlshp_uniq
385 (p_dsgn_rqmt_rlshp_typ_id => p_rec.dsgn_rqmt_rlshp_typ_id,
386 p_rlshp_typ_cd => p_rec.rlshp_typ_cd,
387 p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
388 p_business_group_id => p_rec.business_group_id,
389 p_object_version_number => p_rec.object_version_number );
390 --
391 hr_utility.set_location(' Leaving:'||l_proc, 10);
392 End insert_validate;
393 --
394 -- ----------------------------------------------------------------------------
395 -- |---------------------------< update_validate >----------------------------|
396 -- ----------------------------------------------------------------------------
397 Procedure update_validate(p_rec in ben_drr_shd.g_rec_type
398 ,p_effective_date in date) is
399 --
400 l_proc varchar2(72) := g_package||'update_validate';
401 --
402 Begin
403 hr_utility.set_location('Entering:'||l_proc, 5);
404 --
405 -- Call all supporting business operations
406 --
407 --
408 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
409 --
410 chk_dsgn_rqmt_rlshp_typ_id
411 (p_dsgn_rqmt_rlshp_typ_id => p_rec.dsgn_rqmt_rlshp_typ_id,
412 p_object_version_number => p_rec.object_version_number);
413 --
414 chk_rlshp_typ_cd
415 (p_dsgn_rqmt_rlshp_typ_id => p_rec.dsgn_rqmt_rlshp_typ_id,
416 p_rlshp_typ_cd => p_rec.rlshp_typ_cd,
417 p_effective_date => p_effective_date,
418 p_business_group_id => p_rec.business_group_id,
419 p_object_version_number => p_rec.object_version_number);
420 --
421 --bug 2837189
422 chk_dsgn_rqmt_rlshp_uniq
423 (p_dsgn_rqmt_rlshp_typ_id => p_rec.dsgn_rqmt_rlshp_typ_id,
424 p_rlshp_typ_cd => p_rec.rlshp_typ_cd,
425 p_dsgn_rqmt_id => p_rec.dsgn_rqmt_id,
426 p_business_group_id => p_rec.business_group_id,
427 p_object_version_number => p_rec.object_version_number );
428 --
429 hr_utility.set_location(' Leaving:'||l_proc, 10);
430 End update_validate;
431 --
432 -- ----------------------------------------------------------------------------
433 -- |---------------------------< delete_validate >----------------------------|
434 -- ----------------------------------------------------------------------------
435 Procedure delete_validate(p_rec in ben_drr_shd.g_rec_type
436 ,p_effective_date in date) is
437 --
438 l_proc varchar2(72) := g_package||'delete_validate';
439 --
440 Begin
441 hr_utility.set_location('Entering:'||l_proc, 5);
442 --
443 -- Call all supporting business operations
444 --
445 hr_utility.set_location(' Leaving:'||l_proc, 10);
446 End delete_validate;
447 --
448 --
449 -- ---------------------------------------------------------------------------
450 -- |---------------------< return_legislation_code >-------------------------|
451 -- ---------------------------------------------------------------------------
452 --
453 function return_legislation_code
454 (p_dsgn_rqmt_rlshp_typ_id in number) return varchar2 is
455 --
456 -- Declare cursor
457 --
458 cursor csr_leg_code is
459 select a.legislation_code
460 from per_business_groups a,
461 ben_dsgn_rqmt_rlshp_typ b
462 where b.dsgn_rqmt_rlshp_typ_id = p_dsgn_rqmt_rlshp_typ_id
463 and a.business_group_id = b.business_group_id;
464 --
465 -- Declare local variables
466 --
467 l_legislation_code varchar2(150);
468 l_proc varchar2(72) := g_package||'return_legislation_code';
469 --
470 begin
471 --
472 hr_utility.set_location('Entering:'|| l_proc, 10);
473 --
474 -- Ensure that all the mandatory parameter are not null
475 --
476 hr_api.mandatory_arg_error(p_api_name => l_proc,
477 p_argument => 'dsgn_rqmt_rlshp_typ_id',
478 p_argument_value => p_dsgn_rqmt_rlshp_typ_id);
479 --
480 open csr_leg_code;
481 --
482 fetch csr_leg_code into l_legislation_code;
483 --
484 if csr_leg_code%notfound then
485 --
486 close csr_leg_code;
487 --
488 -- The primary key is invalid therefore we must error
489 --
490 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
491 hr_utility.raise_error;
492 --
493 end if;
494 --
495 close csr_leg_code;
496 --
497 hr_utility.set_location(' Leaving:'|| l_proc, 20);
498 --
499 return l_legislation_code;
500 --
501 end return_legislation_code;
502 --
503 end ben_drr_bus;