[Home] [Help]
PACKAGE BODY: APPS.BEN_XRS_BUS
Source
1 Package Body ben_xrs_bus as
2 /* $Header: bexrsrhi.pkb 120.1 2005/06/08 14:21:35 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xrs_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ext_rslt_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_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_id(p_ext_rslt_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_ext_rslt_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_xrs_shd.api_updating
47 (p_ext_rslt_id => p_ext_rslt_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_ext_rslt_id,hr_api.g_number)
52 <> ben_xrs_shd.g_old_rec.ext_rslt_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_xrs_shd.constraint_error('BEN_EXT_RSLT_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_ext_rslt_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_xrs_shd.constraint_error('BEN_EXT_RSLT_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_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_ext_dfn_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_id PK
89 -- p_ext_dfn_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_ext_dfn_id (p_ext_rslt_id in number,
102 p_ext_dfn_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_ext_dfn_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from ben_ext_dfn a
112 where a.ext_dfn_id = p_ext_dfn_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := ben_xrs_shd.api_updating
119 (p_ext_rslt_id => p_ext_rslt_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_ext_dfn_id,hr_api.g_number)
124 <> nvl(ben_xrs_shd.g_old_rec.ext_dfn_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if ext_dfn_id value exists in ben_ext_dfn table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in ben_ext_dfn
137 -- table.
138 --
139 ben_xrs_shd.constraint_error('BEN_EXT_RSLT_FK1');
140 --
144 --
141 end if;
142 --
143 close c1;
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_ext_dfn_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_ext_stat_cd >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure is used to check that the lookup value is valid.
157 --
158 -- Pre Conditions
159 -- None.
160 --
161 -- In Parameters
162 -- ext_rslt_id PK of record being inserted or updated.
163 -- ext_stat_cd Value of lookup code.
164 -- effective_date effective date
165 -- object_version_number Object version number of record being
166 -- inserted or updated.
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error handled by procedure
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_ext_stat_cd(p_ext_rslt_id in number,
178 p_ext_stat_cd in varchar2,
179 p_effective_date in date,
180 p_object_version_number in number) is
181 --
182 l_proc varchar2(72) := g_package||'chk_ext_stat_cd';
183 l_api_updating boolean;
184 --
185 Begin
186 --
187 hr_utility.set_location('Entering:'||l_proc, 5);
188 --
189 l_api_updating := ben_xrs_shd.api_updating
190 (p_ext_rslt_id => p_ext_rslt_id,
191 p_object_version_number => p_object_version_number);
192 --
193 if (l_api_updating
194 and p_ext_stat_cd
195 <> nvl(ben_xrs_shd.g_old_rec.ext_stat_cd,hr_api.g_varchar2)
196 or not l_api_updating)
197 and p_ext_stat_cd is not null then
198 --
199 -- check if value of lookup falls within lookup type.
200 --
201 if hr_api.not_exists_in_hr_lookups
202 (p_lookup_type => 'BEN_EXT_STAT',
203 p_lookup_code => p_ext_stat_cd,
204 p_effective_date => p_effective_date) then
205 --
206 -- raise error as does not exist as lookup
207 --
208 null; -- not sure why this is failing so comment temporarily for deadline.
209 -- hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
210 -- hr_utility.raise_error;
211 --
212 end if;
213 --
214 /*if p_ext_stat_cd not in ('A','R') then
215 fnd_message.set_name('BEN','BEN_91944_INVLD_STAT_CD');
216 fnd_message.raise_error;
217 end if;*/
218
219 end if;
220 --
221 hr_utility.set_location('Leaving:'||l_proc,10);
222 --
223 end chk_ext_stat_cd;
224 --
225 -- ----------------------------------------------------------------------------
226 -- |------------------------< chk_output_file >---------------------------------|
227 -- ----------------------------------------------------------------------------
228 --
229 -- Description
230 -- ensure that output file names do not have blank spaces.
231 --
232 -- Pre Conditions
233 -- None.
234 --
235 -- In Parameters
236 -- p_output_name is output file name
237 -- p_drctry_name is drctry file name
238 --
239 -- Post Success
240 -- Processing continues
241 --
242 -- Post Failure
243 -- Errors handled by the procedure
244 --
245 -- Access Status
246 -- Internal table handler use only.
247 --
248 -- ----------------------------------------------------------------------------
249 Procedure chk_output_file
250 (p_output_name in varchar2
251 ,p_drctry_name in varchar2)
252 is
253 l_proc varchar2(72) := g_package||'chk_output_file';
254 l_dummy char(1);
255 --
256 Begin
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 if instr(p_output_name,' ') > 0 or instr(p_drctry_name,' ') > 0 then
260 fnd_message.set_name('BEN','BEN_91955_NAME_HAS_SPACE');
261 fnd_message.raise_error;
262 end if;
263 --
264 hr_utility.set_location('Leaving:'||l_proc, 15);
265 End chk_output_file;
266 --
267
268
269
270 Procedure chk_xdo_template_id
271 (p_output_type in varchar2
272 ,p_xdo_template_id in number)
273 is
274 l_proc varchar2(72) := g_package||'chk_xdo_template_id';
275 l_dummy char(1);
276 --
277 cursor c is
278 select 'x'
279 from xdo_templates_b
280 where template_id = p_xdo_template_id ;
281
282 Begin
283 hr_utility.set_location('Entering:'||l_proc, 5);
284 --
285
286 if ( (not nvl(p_output_type,'F') in ( 'F' , 'X' ) ) and p_xdo_template_id is null )
287 or ( p_xdo_template_id is not null and ( p_output_type in ('F' , 'X') ) )
288 then
289 fnd_message.set_name('BEN','BEN_94036_EXT_XDO_PDF_NULL');
290 fnd_message.raise_error;
291 end if;
292
293 if p_xdo_template_id is not null then
294 open c ;
295 fetch c into l_dummy ;
296 if c%notfound then
297 close c ;
298 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
299 fnd_message.set_token('PROCEDURE', l_proc);
300 fnd_message.set_token('CONSTRAINT_NAME', 'XDO_TEMPLATE_ID');
301 fnd_message.raise_error;
302 end if ;
303 close c ;
304
305 end if ;
306 --
310
307 hr_utility.set_location('Leaving:'||l_proc, 15);
308 End chk_xdo_template_id;
309
311 -- ----------------------------------------------------------------------------
312 -- |---------------------------< insert_validate >----------------------------|
313 -- ----------------------------------------------------------------------------
314 Procedure insert_validate(p_rec in ben_xrs_shd.g_rec_type
315 ,p_effective_date in date) is
316 --
317 l_proc varchar2(72) := g_package||'insert_validate';
318 --
319 Begin
320 hr_utility.set_location('Entering:'||l_proc, 5);
321 --
322 -- Call all supporting business operations
323 --
324 --
325 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
326 --
327 chk_ext_rslt_id
328 (p_ext_rslt_id => p_rec.ext_rslt_id,
329 p_object_version_number => p_rec.object_version_number);
330 --
331 chk_ext_dfn_id
332 (p_ext_rslt_id => p_rec.ext_rslt_id,
333 p_ext_dfn_id => p_rec.ext_dfn_id,
334 p_object_version_number => p_rec.object_version_number);
335 --
336 chk_ext_stat_cd
337 (p_ext_rslt_id => p_rec.ext_rslt_id,
338 p_ext_stat_cd => p_rec.ext_stat_cd,
339 p_effective_date => p_effective_date,
340 p_object_version_number => p_rec.object_version_number);
341 --
342 chk_output_file
343 (p_output_name => p_rec.output_name
344 ,p_drctry_name => p_rec.drctry_name);
345
346
347 chk_xdo_template_id
348 (p_output_type => p_rec.output_type
349 ,p_xdo_template_id => p_rec.xdo_template_id
350 ) ;
351
352
353 hr_utility.set_location(' Leaving:'||l_proc, 10);
354 End insert_validate;
355 --
356 -- ----------------------------------------------------------------------------
357 -- |---------------------------< update_validate >----------------------------|
358 -- ----------------------------------------------------------------------------
359 Procedure update_validate(p_rec in ben_xrs_shd.g_rec_type
360 ,p_effective_date in date) is
361 --
362 l_proc varchar2(72) := g_package||'update_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_ext_rslt_id
373 (p_ext_rslt_id => p_rec.ext_rslt_id,
374 p_object_version_number => p_rec.object_version_number);
375 --
376 chk_ext_dfn_id
377 (p_ext_rslt_id => p_rec.ext_rslt_id,
378 p_ext_dfn_id => p_rec.ext_dfn_id,
379 p_object_version_number => p_rec.object_version_number);
380 --
381 chk_ext_stat_cd
382 (p_ext_rslt_id => p_rec.ext_rslt_id,
383 p_ext_stat_cd => p_rec.ext_stat_cd,
384 p_effective_date => p_effective_date,
385 p_object_version_number => p_rec.object_version_number);
386 --
387 chk_output_file
388 (p_output_name => p_rec.output_name
389 ,p_drctry_name => p_rec.drctry_name);
390
391 chk_xdo_template_id
392 (p_output_type => p_rec.output_type
393 ,p_xdo_template_id => p_rec.xdo_template_id
394 ) ;
395
396 hr_utility.set_location(' Leaving:'||l_proc, 10);
397 End update_validate;
398 --
399 -- ----------------------------------------------------------------------------
400 -- |---------------------------< delete_validate >----------------------------|
401 -- ----------------------------------------------------------------------------
402 Procedure delete_validate(p_rec in ben_xrs_shd.g_rec_type
403 ,p_effective_date in date) is
404 --
405 l_proc varchar2(72) := g_package||'delete_validate';
406 --
407 Begin
408 hr_utility.set_location('Entering:'||l_proc, 5);
409 --
410 -- Call all supporting business operations
411 --
412 hr_utility.set_location(' Leaving:'||l_proc, 10);
413 End delete_validate;
414 --
415 --
416 -- ---------------------------------------------------------------------------
417 -- |---------------------< return_legislation_code >-------------------------|
418 -- ---------------------------------------------------------------------------
419 --
420 function return_legislation_code
421 (p_ext_rslt_id in number) return varchar2 is
422 --
423 -- Declare cursor
424 --
425 cursor csr_leg_code is
426 select a.legislation_code
427 from per_business_groups a,
428 ben_ext_rslt b
429 where b.ext_rslt_id = p_ext_rslt_id
430 and a.business_group_id = b.business_group_id;
431 --
432 -- Declare local variables
433 --
434 l_legislation_code per_business_groups.legislation_code%type ;
435 l_proc varchar2(72) := g_package||'return_legislation_code';
436 --
437 begin
438 --
439 hr_utility.set_location('Entering:'|| l_proc, 10);
440 --
441 -- Ensure that all the mandatory parameter are not null
442 --
443 hr_api.mandatory_arg_error(p_api_name => l_proc,
444 p_argument => 'ext_rslt_id',
445 p_argument_value => p_ext_rslt_id);
446 --
447 open csr_leg_code;
448 --
449 fetch csr_leg_code into l_legislation_code;
450 --
451 if csr_leg_code%notfound then
452 --
453 close csr_leg_code;
454 --
455 -- The primary key is invalid therefore we must error
456 --
457 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
458 hr_utility.raise_error;
459 --
460 end if;
461 --
462 close csr_leg_code;
463 --
464 hr_utility.set_location(' Leaving:'|| l_proc, 20);
465 --
466 return l_legislation_code;
467 --
468 end return_legislation_code;
469 --
470 end ben_xrs_bus;