[Home] [Help]
PACKAGE BODY: APPS.BEN_XRD_BUS
Source
1 Package Body ben_xrd_bus as
2 /* $Header: bexrdrhi.pkb 120.1 2006/02/06 11:28:36 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xrd_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ext_rslt_dtl_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 -- ext_rslt_dtl_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_ext_rslt_dtl_id(p_ext_rslt_dtl_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_ext_rslt_dtl_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_xrd_shd.api_updating
47 (p_ext_rslt_dtl_id => p_ext_rslt_dtl_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_ext_rslt_dtl_id,hr_api.g_number)
52 <> ben_xrd_shd.g_old_rec.ext_rslt_dtl_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_xrd_shd.constraint_error('BEN_EXT_RSLT_DTL_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_ext_rslt_dtl_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_xrd_shd.constraint_error('BEN_EXT_RSLT_DTL_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_ext_rslt_dtl_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_person_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_ext_rslt_dtl_id PK
89 -- p_person_id ID of FK column
90 -- p_effective_date Session Date of record
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_person_id (p_ext_rslt_dtl_id in number,
103 p_person_id in number,
104 p_effective_date in date,
105 p_object_version_number in number) is
106 --
107 l_proc varchar2(72) := g_package||'chk_person_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from per_all_people_f a
114 where a.person_id = p_person_id
115 and p_effective_date
116 between a.effective_start_date
117 and a.effective_end_date;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_xrd_shd.api_updating
124 (p_ext_rslt_dtl_id => p_ext_rslt_dtl_id,
125 p_object_version_number => p_object_version_number);
126 --
127 if (l_api_updating
128 and nvl(p_person_id,hr_api.g_number)
129 <> nvl(ben_xrd_shd.g_old_rec.person_id,hr_api.g_number)
130 or not l_api_updating) then
131 --
132 -- check if person_id value exists in per_all_people_f table
133 --
134 open c1;
135 --
136 fetch c1 into l_dummy;
137 if c1%notfound then
138 --
139 close c1;
140 --
141 -- raise error as FK does not relate to PK in per_all_people_f
142 -- table.
143 --
144 ben_xrd_shd.constraint_error('BEN_EXT_RSLT_DTL_DT1');
145 --
146 end if;
147 --
148 close c1;
149 --
150 end if;
151 --
152 hr_utility.set_location('Leaving:'||l_proc,10);
153 --
154 End chk_person_id;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------< chk_ext_rcd_id >------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description
161 -- This procedure checks that a referenced foreign key actually exists
162 -- in the referenced table.
163 --
164 -- Pre-Conditions
165 -- None.
166 --
167 -- In Parameters
168 -- p_ext_rslt_dtl_id PK
169 -- p_ext_rcd_id ID of FK column
170 -- p_object_version_number object version number
171 --
172 -- Post Success
173 -- Processing continues
174 --
175 -- Post Failure
176 -- Error raised.
177 --
178 -- Access Status
179 -- Internal table handler use only.
180 --
181 Procedure chk_ext_rcd_id (p_ext_rslt_dtl_id in number,
182 p_ext_rcd_id in number,
183 p_object_version_number in number) is
184 --
185 l_proc varchar2(72) := g_package||'chk_ext_rcd_id';
186 l_api_updating boolean;
187 l_dummy varchar2(1);
188 --
189 cursor c1 is
190 select null
191 from ben_ext_rcd a
192 where a.ext_rcd_id = p_ext_rcd_id;
193 --
194 Begin
195 --
196 hr_utility.set_location('Entering:'||l_proc,5);
197 --
198 l_api_updating := ben_xrd_shd.api_updating
199 (p_ext_rslt_dtl_id => p_ext_rslt_dtl_id,
200 p_object_version_number => p_object_version_number);
201 --
202 if (l_api_updating
203 and nvl(p_ext_rcd_id,hr_api.g_number)
204 <> nvl(ben_xrd_shd.g_old_rec.ext_rcd_id,hr_api.g_number)
205 or not l_api_updating) then
206 --
207 -- check if ext_rcd_id value exists in ben_ext_rcd table
208 --
209 open c1;
210 --
211 fetch c1 into l_dummy;
212 if c1%notfound then
213 --
214 close c1;
215 --
216 -- raise error as FK does not relate to PK in ben_ext_rcd
217 -- table.
218 --
219 ben_xrd_shd.constraint_error('BEN_EXT_RSLT_DTL_FK3');
220 --
221 end if;
222 --
223 close c1;
224 --
225 end if;
226 --
227 hr_utility.set_location('Leaving:'||l_proc,10);
228 --
229 End chk_ext_rcd_id;
230
231
232
233
234
235 Procedure chk_ext_rcd_in_file_id (p_ext_rslt_dtl_id in number,
236 p_ext_rcd_in_file_id in number,
237 p_object_version_number in number) is
238 --
239 l_proc varchar2(72) := g_package||'chk_ext_rcd_in_file_id';
240 l_api_updating boolean;
241 l_dummy varchar2(1);
242 --
243 cursor c1 is
244 select null
245 from ben_ext_rcd_in_file a
246 where a.ext_rcd_in_file_id = p_ext_rcd_in_file_id;
247 --
248 Begin
249 --
250 hr_utility.set_location('Entering:'||l_proc,5);
251 --
252 l_api_updating := ben_xrd_shd.api_updating
253 (p_ext_rslt_dtl_id => p_ext_rslt_dtl_id,
254 p_object_version_number => p_object_version_number);
255 --
256 if (l_api_updating
257 and nvl(p_ext_rcd_in_file_id,hr_api.g_number)
258 <> nvl(ben_xrd_shd.g_old_rec.ext_rcd_in_file_id,hr_api.g_number)
259 or not l_api_updating) and p_ext_rcd_in_file_id is not null then
260 --
261 -- check if ext_rcd_id value exists in ben_ext_rcd table
262 --
263 open c1;
264 --
265 fetch c1 into l_dummy;
266 if c1%notfound then
267 --
268 close c1;
269 --
270 -- raise error as FK does not relate to PK in ben_ext_rcd
271 -- table.
272 --
273 ben_xrd_shd.constraint_error('BEN_EXT_RSLT_DTL_FK3');
274 --
275 end if;
276 --
277 close c1;
278 --
279 end if;
280 --
281 hr_utility.set_location('Leaving:'||l_proc,10);
282 --
283 End chk_ext_rcd_in_file_id;
284
285
286
287 --
288 -- ----------------------------------------------------------------------------
289 -- |------< chk_ext_rslt_id >------|
290 -- ----------------------------------------------------------------------------
291 --
292 -- Description
293 -- This procedure checks that a referenced foreign key actually exists
294 -- in the referenced table.
295 --
296 -- Pre-Conditions
297 -- None.
298 --
299 -- In Parameters
300 -- p_ext_rslt_dtl_id PK
301 -- p_ext_rslt_id ID of FK column
302 -- p_object_version_number object version number
303 --
304 -- Post Success
305 -- Processing continues
306 --
307 -- Post Failure
308 -- Error raised.
309 --
310 -- Access Status
311 -- Internal table handler use only.
312 --
313 Procedure chk_ext_rslt_id (p_ext_rslt_dtl_id in number,
314 p_ext_rslt_id in number,
315 p_object_version_number in number) is
316 --
317 l_proc varchar2(72) := g_package||'chk_ext_rslt_id';
318 l_api_updating boolean;
319 l_dummy varchar2(1);
320 --
321 cursor c1 is
322 select null
323 from ben_ext_rslt a
324 where a.ext_rslt_id = p_ext_rslt_id;
325 --
326 Begin
327 --
328 hr_utility.set_location('Entering:'||l_proc,5);
329 --
330 l_api_updating := ben_xrd_shd.api_updating
331 (p_ext_rslt_dtl_id => p_ext_rslt_dtl_id,
332 p_object_version_number => p_object_version_number);
333 --
334 if (l_api_updating
335 and nvl(p_ext_rslt_id,hr_api.g_number)
336 <> nvl(ben_xrd_shd.g_old_rec.ext_rslt_id,hr_api.g_number)
337 or not l_api_updating) then
338 --
339 -- check if ext_rslt_id value exists in ben_ext_rslt table
340 --
341 open c1;
342 --
343 fetch c1 into l_dummy;
344 if c1%notfound then
345 --
346 close c1;
347 --
348 -- raise error as FK does not relate to PK in ben_ext_rslt
349 -- table.
350 --
351 ben_xrd_shd.constraint_error('BEN_EXT_RSLT_DTL_FK1');
352 --
353 end if;
354 --
355 close c1;
356 --
357 end if;
358 --
359 hr_utility.set_location('Leaving:'||l_proc,10);
360 --
361 End chk_ext_rslt_id;
362 --
363 -- ----------------------------------------------------------------------------
364 -- |---------------------------< insert_validate >----------------------------|
365 -- ----------------------------------------------------------------------------
366 Procedure insert_validate(p_rec in ben_xrd_shd.g_rec_type) 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 --
376 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
377 --
378 if p_rec.ext_per_bg_id is not null then
379 hr_api.validate_bus_grp_id(p_rec.ext_per_bg_id); -- Validate Bus Grp
380 end if ;
381
382 chk_ext_rslt_dtl_id
383 (p_ext_rslt_dtl_id => p_rec.ext_rslt_dtl_id,
384 p_object_version_number => p_rec.object_version_number);
385 --
386 chk_ext_rcd_id
387 (p_ext_rslt_dtl_id => p_rec.ext_rslt_dtl_id,
388 p_ext_rcd_id => p_rec.ext_rcd_id,
389 p_object_version_number => p_rec.object_version_number);
390 --
391 chk_ext_rslt_id
392 (p_ext_rslt_dtl_id => p_rec.ext_rslt_dtl_id,
393 p_ext_rslt_id => p_rec.ext_rslt_id,
394 p_object_version_number => p_rec.object_version_number);
395 --
396
397 chk_ext_rcd_in_file_id
398 (p_ext_rslt_dtl_id => p_rec.ext_rslt_dtl_id,
399 p_ext_rcd_in_file_id => p_rec.ext_rcd_in_file_id,
400 p_object_version_number => p_rec.object_version_number);
401
402 hr_utility.set_location(' Leaving:'||l_proc, 10);
403 End insert_validate;
404 --
405 -- ----------------------------------------------------------------------------
406 -- |---------------------------< update_validate >----------------------------|
407 -- ----------------------------------------------------------------------------
408 Procedure update_validate(p_rec in ben_xrd_shd.g_rec_type) is
409 --
410 l_proc varchar2(72) := g_package||'update_validate';
411 --
412 Begin
413 hr_utility.set_location('Entering:'||l_proc, 5);
414 --
415 -- Call all supporting business operations
416 --
417 --
418 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
419 if p_rec.ext_per_bg_id is not null then
420 hr_api.validate_bus_grp_id(p_rec.ext_per_bg_id); -- Validate Bus Grp
421 end if ;
422 --
423 chk_ext_rslt_dtl_id
424 (p_ext_rslt_dtl_id => p_rec.ext_rslt_dtl_id,
425 p_object_version_number => p_rec.object_version_number);
426 --
427 chk_ext_rcd_id
428 (p_ext_rslt_dtl_id => p_rec.ext_rslt_dtl_id,
429 p_ext_rcd_id => p_rec.ext_rcd_id,
430 p_object_version_number => p_rec.object_version_number);
431 --
432 chk_ext_rslt_id
433 (p_ext_rslt_dtl_id => p_rec.ext_rslt_dtl_id,
434 p_ext_rslt_id => p_rec.ext_rslt_id,
435 p_object_version_number => p_rec.object_version_number);
436 --
437 chk_ext_rcd_in_file_id
438 (p_ext_rslt_dtl_id => p_rec.ext_rslt_dtl_id,
439 p_ext_rcd_in_file_id => p_rec.ext_rcd_in_file_id,
440 p_object_version_number => p_rec.object_version_number);
441
442 hr_utility.set_location(' Leaving:'||l_proc, 10);
443
444 End update_validate;
445 --
446 -- ----------------------------------------------------------------------------
447 -- |---------------------------< delete_validate >----------------------------|
448 -- ----------------------------------------------------------------------------
449 Procedure delete_validate(p_rec in ben_xrd_shd.g_rec_type) is
450 --
451 l_proc varchar2(72) := g_package||'delete_validate';
452 --
453 Begin
454 hr_utility.set_location('Entering:'||l_proc, 5);
455 --
456 -- Call all supporting business operations
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_rslt_dtl_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_rslt_dtl b
475 where b.ext_rslt_dtl_id = p_ext_rslt_dtl_id
476 and a.business_group_id = b.business_group_id;
477 --
478 -- Declare local variables
479 --
480 l_legislation_code per_business_groups.legislation_code%type ;
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_rslt_dtl_id',
491 p_argument_value => p_ext_rslt_dtl_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 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
504 hr_utility.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_xrd_bus;