[Home] [Help]
PACKAGE BODY: APPS.BEN_CRD_BUS
Source
1 Package Body ben_crd_bus as
2 /* $Header: becrdrhi.pkb 115.4 2002/12/16 11:03:52 rpgupta ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_crd_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_crt_ordr_cvrd_per_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 -- crt_ordr_cvrd_per_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_crt_ordr_cvrd_per_id(p_crt_ordr_cvrd_per_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_crt_ordr_cvrd_per_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_crd_shd.api_updating
47 (p_crt_ordr_cvrd_per_id => p_crt_ordr_cvrd_per_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_crt_ordr_cvrd_per_id,hr_api.g_number)
52 <> ben_crd_shd.g_old_rec.crt_ordr_cvrd_per_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_crd_shd.constraint_error('BEN_CRT_ORDR_CVRD_PER_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_crt_ordr_cvrd_per_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_crd_shd.constraint_error('BEN_CRT_ORDR_CVRD_PER_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_crt_ordr_cvrd_per_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_crt_ordr_cvrd_per_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_crt_ordr_cvrd_per_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_crd_shd.api_updating
124 (p_crt_ordr_cvrd_per_id => p_crt_ordr_cvrd_per_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_crd_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_crd_shd.constraint_error('BEN_CRT_ORDR_CVRD_PER_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_crt_ordr_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_crt_ordr_cvrd_per_id PK
169 -- p_crt_ordr_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_crt_ordr_id (p_crt_ordr_cvrd_per_id in number,
182 p_crt_ordr_id in number,
183 p_object_version_number in number) is
184 --
185 l_proc varchar2(72) := g_package||'chk_crt_ordr_id';
186 l_api_updating boolean;
187 l_dummy varchar2(1);
188 --
189 cursor c1 is
190 select null
191 from ben_crt_ordr a
192 where a.crt_ordr_id = p_crt_ordr_id;
193 --
194 Begin
195 --
196 hr_utility.set_location('Entering:'||l_proc,5);
197 --
198 l_api_updating := ben_crd_shd.api_updating
199 (p_crt_ordr_cvrd_per_id => p_crt_ordr_cvrd_per_id,
200 p_object_version_number => p_object_version_number);
201 --
202 if (l_api_updating
203 and nvl(p_crt_ordr_id,hr_api.g_number)
204 <> nvl(ben_crd_shd.g_old_rec.crt_ordr_id,hr_api.g_number)
205 or not l_api_updating) and
206 p_crt_ordr_id is not null then
207 --
208 -- check if crt_ordr_id value exists in ben_crt_ordr table
209 --
210 open c1;
211 --
212 fetch c1 into l_dummy;
213 if c1%notfound then
214 --
215 close c1;
216 --
217 -- raise error as FK does not relate to PK in ben_crt_ordr
218 -- table.
219 --
220 ben_crd_shd.constraint_error('BEN_CRT_ORDR_CVRD_PER_FK1');
221 --
222 end if;
223 --
224 close c1;
225 --
226 end if;
227 --
228 hr_utility.set_location('Leaving:'||l_proc,10);
229 --
230 End chk_crt_ordr_id;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |---------------------------< insert_validate >----------------------------|
234 -- ----------------------------------------------------------------------------
235 Procedure insert_validate(p_rec in ben_crd_shd.g_rec_type) is
236 --
237 l_proc varchar2(72) := g_package||'insert_validate';
238 --
239 Begin
240 hr_utility.set_location('Entering:'||l_proc, 5);
241 --
242 -- Call all supporting business operations
243 --
244 --
245 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
246 --
247 chk_crt_ordr_cvrd_per_id
248 (p_crt_ordr_cvrd_per_id => p_rec.crt_ordr_cvrd_per_id,
249 p_object_version_number => p_rec.object_version_number);
250 --
251 chk_crt_ordr_id
252 (p_crt_ordr_cvrd_per_id => p_rec.crt_ordr_cvrd_per_id,
253 p_crt_ordr_id => p_rec.crt_ordr_id,
254 p_object_version_number => p_rec.object_version_number);
255 --
256 hr_utility.set_location(' Leaving:'||l_proc, 10);
257 End insert_validate;
258 --
259 -- ----------------------------------------------------------------------------
260 -- |---------------------------< update_validate >----------------------------|
261 -- ----------------------------------------------------------------------------
262 Procedure update_validate(p_rec in ben_crd_shd.g_rec_type) is
263 --
264 l_proc varchar2(72) := g_package||'update_validate';
265 --
266 Begin
267 hr_utility.set_location('Entering:'||l_proc, 5);
268 --
269 -- Call all supporting business operations
270 --
271 --
272 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
273 --
274 chk_crt_ordr_cvrd_per_id
275 (p_crt_ordr_cvrd_per_id => p_rec.crt_ordr_cvrd_per_id,
276 p_object_version_number => p_rec.object_version_number);
277 --
278 chk_crt_ordr_id
279 (p_crt_ordr_cvrd_per_id => p_rec.crt_ordr_cvrd_per_id,
280 p_crt_ordr_id => p_rec.crt_ordr_id,
281 p_object_version_number => p_rec.object_version_number);
282 --
283 hr_utility.set_location(' Leaving:'||l_proc, 10);
284 End update_validate;
285 --
286 -- ----------------------------------------------------------------------------
287 -- |---------------------------< delete_validate >----------------------------|
288 -- ----------------------------------------------------------------------------
289 Procedure delete_validate(p_rec in ben_crd_shd.g_rec_type) is
290 --
291 l_proc varchar2(72) := g_package||'delete_validate';
292 --
293 Begin
294 hr_utility.set_location('Entering:'||l_proc, 5);
295 --
296 -- Call all supporting business operations
297 --
298 hr_utility.set_location(' Leaving:'||l_proc, 10);
299 End delete_validate;
300 --
301 --
302 -- ---------------------------------------------------------------------------
303 -- |---------------------< return_legislation_code >-------------------------|
304 -- ---------------------------------------------------------------------------
305 --
306 function return_legislation_code
307 (p_crt_ordr_cvrd_per_id in number) return varchar2 is
308 --
309 -- Declare cursor
310 --
311 cursor csr_leg_code is
312 select a.legislation_code
313 from per_business_groups a,
314 ben_crt_ordr_cvrd_per b
315 where b.crt_ordr_cvrd_per_id = p_crt_ordr_cvrd_per_id
316 and a.business_group_id = b.business_group_id;
317 --
318 -- Declare local variables
319 --
320 l_legislation_code varchar2(150);
321 l_proc varchar2(72) := g_package||'return_legislation_code';
322 --
323 begin
324 --
325 hr_utility.set_location('Entering:'|| l_proc, 10);
326 --
327 -- Ensure that all the mandatory parameter are not null
328 --
329 hr_api.mandatory_arg_error(p_api_name => l_proc,
330 p_argument => 'crt_ordr_cvrd_per_id',
331 p_argument_value => p_crt_ordr_cvrd_per_id);
332 --
333 open csr_leg_code;
334 --
335 fetch csr_leg_code into l_legislation_code;
336 --
337 if csr_leg_code%notfound then
338 --
339 close csr_leg_code;
340 --
341 -- The primary key is invalid therefore we must error
342 --
343 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
344 fnd_message.raise_error;
345 --
346 end if;
347 --
348 close csr_leg_code;
349 --
350 hr_utility.set_location(' Leaving:'|| l_proc, 20);
351 --
352 return l_legislation_code;
353 --
354 end return_legislation_code;
355 --
356 end ben_crd_bus;