[Home] [Help]
PACKAGE BODY: APPS.BEN_PCG_BUS
Source
1 Package Body ben_pcg_bus as
2 /* $Header: bepcgrhi.pkb 115.8 2002/12/16 11:58:08 vsethi ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pcg_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_prtt_clm_gd_or_svc_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 -- prtt_clm_gd_or_svc_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_prtt_clm_gd_or_svc_typ_id
37 (p_prtt_clm_gd_or_svc_typ_id in number,
38 p_object_version_number in number) is
39 --
40 l_proc varchar2(72) := g_package||'chk_prtt_clm_gd_or_svc_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_pcg_shd.api_updating
48 (p_prtt_clm_gd_or_svc_typ_id => p_prtt_clm_gd_or_svc_typ_id,
49 p_object_version_number => p_object_version_number);
50 --
51 if (l_api_updating
52 and nvl(p_prtt_clm_gd_or_svc_typ_id,hr_api.g_number)
53 <> ben_pcg_shd.g_old_rec.prtt_clm_gd_or_svc_typ_id) then
54 --
55 -- raise error as PK has changed
56 --
57 ben_pcg_shd.constraint_error('BEN_PRTT_CLM_GD_OR_SVC_TYP_PK');
58 --
59 elsif not l_api_updating then
60 --
61 -- check if PK is null
62 --
63 if p_prtt_clm_gd_or_svc_typ_id is not null then
64 --
65 -- raise error as PK is not null
66 --
67 ben_pcg_shd.constraint_error('BEN_PRTT_CLM_GD_OR_SVC_TYP_PK');
68 --
69 end if;
70 --
71 end if;
72 --
73 hr_utility.set_location('Leaving:'||l_proc, 10);
74 --
75 End chk_prtt_clm_gd_or_svc_typ_id;
76 --
77 -- ----------------------------------------------------------------------------
78 -- |------< chk_prtt_reimbmt_rqst_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_prtt_clm_gd_or_svc_typ_id PK
90 -- p_prtt_reimbmt_rqst_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_prtt_reimbmt_rqst_id
104 (p_prtt_clm_gd_or_svc_typ_id in number,
105 p_prtt_reimbmt_rqst_id in number,
106 p_effective_date in date,
107 p_object_version_number in number) is
108 --
109 l_proc varchar2(72) := g_package||'chk_prtt_reimbmt_rqst_id';
110 l_api_updating boolean;
111 l_dummy varchar2(1);
112 --
113 cursor c1 is
114 select null
115 from ben_prtt_reimbmt_rqst_f a
116 where a.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
117 and p_effective_date
118 between a.effective_start_date
119 and a.effective_end_date;
120 --
121 Begin
122 --
123 hr_utility.set_location('Entering:'||l_proc,5);
124 --
125 l_api_updating := ben_pcg_shd.api_updating
126 (p_prtt_clm_gd_or_svc_typ_id => p_prtt_clm_gd_or_svc_typ_id,
127 p_object_version_number => p_object_version_number);
128 --
129 if (l_api_updating
130 and nvl(p_prtt_reimbmt_rqst_id,hr_api.g_number)
131 <> nvl(ben_pcg_shd.g_old_rec.prtt_reimbmt_rqst_id,hr_api.g_number)
132 or not l_api_updating) then
133 --
134 -- check if prtt_reimbmt_rqst_id value exists in ben_prtt_reimbmt_rqst_f table
135 --
136 open c1;
137 --
138 fetch c1 into l_dummy;
139 if c1%notfound then
140 --
141 close c1;
142 --
143 -- raise error as FK does not relate to PK in ben_prtt_reimbmt_rqst_f
144 -- table.
145 --
146 ben_pcg_shd.constraint_error('BEN_PRTT_CLM_GD_OR_SVC_TYP_DT1');
147 --
148 end if;
149 --
150 close c1;
151 --
152 end if;
153 --
154 hr_utility.set_location('Leaving:'||l_proc,10);
155 --
156 End chk_prtt_reimbmt_rqst_id;
157
158
159 -- ----------------------------------------------------------------------------
160 -- |------< chk_pl_gd_or_svc_id >------|
161 -- ----------------------------------------------------------------------------
162 --
163 -- Description
164 -- This procedure checks that a referenced foreign key actually exists
165 -- in the referenced table.
166 --
167 -- Pre-Conditions
168 -- None.
169 --
170 -- In Parameters
171 -- p_prtt_clm_gd_or_svc_typ_id PK
172 -- p_chk_pl_gd_or_cvc_id ID of FK column
173 -- p_effective_date Session Date of record
174 -- p_object_version_number object version number
175 --
176 -- Post Success
177 -- Processing continues
178 --
179 -- Post Failure
180 -- Error raised.
181 --
182 -- Access Status
183 -- Internal table handler use only.
184 --
185 Procedure chk_pl_gd_or_svc_id
186 (p_prtt_clm_gd_or_svc_typ_id in number,
187 p_pl_gd_or_svc_id in number,
188 p_effective_date in date,
189 p_object_version_number in number) is
190 --
191 l_proc varchar2(72) := g_package||'chk_pl_gd_or_svc_id';
192 l_api_updating boolean;
193 l_dummy varchar2(1);
194 cursor c1 is
195 select null
196 from ben_pl_gd_or_svc_f a
197 where a.pl_gd_or_svc_id = p_pl_gd_or_svc_id
198 and p_effective_date
199 between a.effective_start_date
200 and a.effective_end_date;
201 --
202 Begin
203 --
204 hr_utility.set_location('Entering:'||l_proc,5);
205 --
206 l_api_updating := ben_pcg_shd.api_updating
207 (p_prtt_clm_gd_or_svc_typ_id => p_prtt_clm_gd_or_svc_typ_id,
208 p_object_version_number => p_object_version_number);
209 --
210 if (l_api_updating
211 and nvl(p_pl_gd_or_svc_id,hr_api.g_number)
212 <> nvl(ben_pcg_shd.g_old_rec.pl_gd_or_svc_id,hr_api.g_number)
213 or not l_api_updating) then
214 --
215 -- check if prtt_reimbmt_rqst_id value exists in ben_prtt_reimbmt_rqst_f table
216 --
217 open c1;
218 --
219 fetch c1 into l_dummy;
220 if c1%notfound then
221 --
222 close c1;
223 --
224 -- raise error as FK does not relate to PK in ben_prtt_reimbmt_rqst_f
225 -- table.
226 --
227 ben_pcg_shd.constraint_error('BEN_PRTT_CLM_GD_OR_SVC_TYP_DT1');
228 --
229 end if;
230 --
231 close c1;
232 --
233 end if;
234 --
235 hr_utility.set_location('Leaving:'||l_proc,10);
236 --
237 End chk_pl_gd_or_svc_id;
238
239
240 --
241 -- ----------------------------------------------------------------------------
242 -- |------< chk_gd_or_svc_typ_id >------|
243 -- ----------------------------------------------------------------------------
244 --
245 -- Description
246 -- This procedure checks that a referenced foreign key actually exists
247 -- in the referenced table.
248 --
249 -- Pre-Conditions
250 -- None.
251 --
252 -- In Parameters
253 -- p_prtt_clm_gd_or_svc_typ_id PK
254 -- p_gd_or_svc_typ_id ID of FK column
255 -- p_object_version_number object version number
256 --
257 -- Post Success
258 -- Processing continues
259 --
260 -- Post Failure
261 -- Error raised.
262 --
263 -- Access Status
264 -- Internal table handler use only.
265 --
266 Procedure chk_gd_or_svc_typ_id
267 (p_prtt_clm_gd_or_svc_typ_id in number,
268 p_gd_or_svc_typ_id in number,
269 p_object_version_number in number) is
270 --
271 l_proc varchar2(72) := g_package||'chk_gd_or_svc_typ_id';
272 l_api_updating boolean;
273 l_dummy varchar2(1);
274 --
275 cursor c1 is
276 select null
277 from ben_gd_or_svc_typ a
278 where a.gd_or_svc_typ_id = p_gd_or_svc_typ_id;
279 --
280 Begin
281 --
282 hr_utility.set_location('Entering:'||l_proc,5);
283 --
284 l_api_updating := ben_pcg_shd.api_updating
285 (p_prtt_clm_gd_or_svc_typ_id => p_prtt_clm_gd_or_svc_typ_id,
286 p_object_version_number => p_object_version_number);
287 --
288 if (l_api_updating
289 and nvl(p_gd_or_svc_typ_id,hr_api.g_number)
290 <> nvl(ben_pcg_shd.g_old_rec.gd_or_svc_typ_id,hr_api.g_number)
291 or not l_api_updating) and
292 p_gd_or_svc_typ_id is not null then
293 --
294 -- check if gd_or_svc_typ_id value exists in ben_gd_or_svc_typ table
295 --
296 open c1;
297 --
298 fetch c1 into l_dummy;
299 if c1%notfound then
300 --
301 close c1;
302 --
303 -- raise error as FK does not relate to PK in ben_gd_or_svc_typ
304 -- table.
305 --
306 ben_pcg_shd.constraint_error('BEN_PRTT_CLM_GD_OR_SVC_TYP_FK3');
307 --
308 end if;
309 --
310 close c1;
311 --
312 end if;
313 --
314 hr_utility.set_location('Leaving:'||l_proc,10);
315 --
316 End chk_gd_or_svc_typ_id;
317 --
318 -- ----------------------------------------------------------------------------
319 -- |---------------------------< insert_validate >----------------------------|
320 -- ----------------------------------------------------------------------------
321 Procedure insert_validate(p_rec in ben_pcg_shd.g_rec_type) is
322 --
323 l_proc varchar2(72) := g_package||'insert_validate';
324 --
325 Begin
326 hr_utility.set_location('Entering:'||l_proc, 5);
327 --
328 -- Call all supporting business operations
329 --
330 --
331 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
332 --
333 chk_prtt_clm_gd_or_svc_typ_id
334 (p_prtt_clm_gd_or_svc_typ_id => p_rec.prtt_clm_gd_or_svc_typ_id,
335 p_object_version_number => p_rec.object_version_number);
336 --
337 chk_gd_or_svc_typ_id
338 (p_prtt_clm_gd_or_svc_typ_id => p_rec.prtt_clm_gd_or_svc_typ_id,
339 p_gd_or_svc_typ_id => p_rec.gd_or_svc_typ_id,
340 p_object_version_number => p_rec.object_version_number);
341 --
342 hr_utility.set_location(' Leaving:'||l_proc, 10);
343 End insert_validate;
344 --
345 -- ----------------------------------------------------------------------------
346 -- |---------------------------< update_validate >----------------------------|
347 -- ----------------------------------------------------------------------------
348 Procedure update_validate(p_rec in ben_pcg_shd.g_rec_type) is
349 --
350 l_proc varchar2(72) := g_package||'update_validate';
351 --
352 Begin
353 hr_utility.set_location('Entering:'||l_proc, 5);
354 --
355 -- Call all supporting business operations
356 --
357 --
358 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
359 --
360 chk_prtt_clm_gd_or_svc_typ_id
361 (p_prtt_clm_gd_or_svc_typ_id => p_rec.prtt_clm_gd_or_svc_typ_id,
362 p_object_version_number => p_rec.object_version_number);
363 --
364 chk_gd_or_svc_typ_id
365 (p_prtt_clm_gd_or_svc_typ_id => p_rec.prtt_clm_gd_or_svc_typ_id,
366 p_gd_or_svc_typ_id => p_rec.gd_or_svc_typ_id,
367 p_object_version_number => p_rec.object_version_number);
368 --
369 hr_utility.set_location(' Leaving:'||l_proc, 10);
370 End update_validate;
371 --
372 -- ----------------------------------------------------------------------------
373 -- |---------------------------< delete_validate >----------------------------|
374 -- ----------------------------------------------------------------------------
375 Procedure delete_validate(p_rec in ben_pcg_shd.g_rec_type) is
376 --
377 l_proc varchar2(72) := g_package||'delete_validate';
378 --
379 Begin
380 hr_utility.set_location('Entering:'||l_proc, 5);
381 --
382 -- Call all supporting business operations
383 --
384 hr_utility.set_location(' Leaving:'||l_proc, 10);
385 End delete_validate;
386 --
387 --
388 -- ---------------------------------------------------------------------------
389 -- |---------------------< return_legislation_code >-------------------------|
390 -- ---------------------------------------------------------------------------
391 --
392 function return_legislation_code
393 (p_prtt_clm_gd_or_svc_typ_id in number) return varchar2 is
394 --
395 -- Declare cursor
396 --
397 cursor csr_leg_code is
398 select a.legislation_code
399 from per_business_groups a,
400 ben_prtt_clm_gd_or_svc_typ b
401 where b.prtt_clm_gd_or_svc_typ_id = p_prtt_clm_gd_or_svc_typ_id
402 and a.business_group_id = b.business_group_id;
403 --
404 -- Declare local variables
405 --
406 l_legislation_code per_business_groups.legislation_code%TYPE; -- UTF8 varchar2(150);
407 l_proc varchar2(72) := g_package||'return_legislation_code';
408 --
409 begin
410 --
411 hr_utility.set_location('Entering:'|| l_proc, 10);
412 --
413 -- Ensure that all the mandatory parameter are not null
414 --
415 hr_api.mandatory_arg_error(p_api_name => l_proc,
416 p_argument => 'prtt_clm_gd_or_svc_typ_id',
417 p_argument_value => p_prtt_clm_gd_or_svc_typ_id);
418 --
419 open csr_leg_code;
420 --
421 fetch csr_leg_code into l_legislation_code;
422 --
423 if csr_leg_code%notfound then
424 --
425 close csr_leg_code;
426 --
427 -- The primary key is invalid therefore we must error
428 --
429 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
430 fnd_message.raise_error;
431 --
432 end if;
433 --
434 close csr_leg_code;
435 --
436 hr_utility.set_location(' Leaving:'|| l_proc, 20);
437 --
438 return l_legislation_code;
439 --
440 end return_legislation_code;
441 --
442 end ben_pcg_bus;