[Home] [Help]
PACKAGE BODY: APPS.BEN_XCL_BUS
Source
1 Package Body ben_xcl_bus as
2 /* $Header: bexclrhi.pkb 115.7 2002/12/24 21:28:21 rpillay ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xcl_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ext_chg_evt_log_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_chg_evt_log_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_chg_evt_log_id(p_ext_chg_evt_log_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_ext_chg_evt_log_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_xcl_shd.api_updating
47 (p_ext_chg_evt_log_id => p_ext_chg_evt_log_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_ext_chg_evt_log_id,hr_api.g_number)
52 <> ben_xcl_shd.g_old_rec.ext_chg_evt_log_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_xcl_shd.constraint_error('BEN_EXT_CHG_EVT_LOG_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_ext_chg_evt_log_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_xcl_shd.constraint_error('BEN_EXT_CHG_EVT_LOG_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_ext_chg_evt_log_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_chg_evt_log_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_chg_evt_log_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_xcl_shd.api_updating
124 (p_ext_chg_evt_log_id => p_ext_chg_evt_log_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_xcl_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_xcl_shd.constraint_error('BEN_EXT_CHG_EVT_LOG_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_chg_evt_cd >------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description
161 -- This procedure is used to check that the lookup value is valid.
162 --
163 -- Pre Conditions
164 -- None.
165 --
166 -- In Parameters
167 -- ext_chg_evt_log_id PK of record being inserted or updated.
168 -- chg_evt_cd Value of lookup code.
169 -- effective_date effective date
170 -- object_version_number Object version number of record being
171 -- inserted or updated.
172 --
173 -- Post Success
174 -- Processing continues
175 --
176 -- Post Failure
177 -- Error handled by procedure
178 --
179 -- Access Status
180 -- Internal table handler use only.
181 --
182 Procedure chk_chg_evt_cd(p_ext_chg_evt_log_id in number,
183 p_chg_evt_cd in varchar2,
184 p_effective_date in date,
185 p_object_version_number in number) is
186 --
187 l_proc varchar2(72) := g_package||'chk_chg_evt_cd';
188 l_api_updating boolean;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc, 5);
193 --
194 l_api_updating := ben_xcl_shd.api_updating
195 (p_ext_chg_evt_log_id => p_ext_chg_evt_log_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and p_chg_evt_cd
200 <> nvl(ben_xcl_shd.g_old_rec.chg_evt_cd,hr_api.g_varchar2)
201 or not l_api_updating) then
202 --
203 -- check if value of lookup falls within lookup type.
204 --
205 --
206 if hr_api.not_exists_in_hr_lookups
207 (p_lookup_type => 'BEN_EXT_CHG_EVT',
208 p_lookup_code => p_chg_evt_cd,
209 p_effective_date => p_effective_date) then
210 --
211 -- raise error as does not exist as lookup
212 --
213 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
214 hr_utility.raise_error;
215 --
216 end if;
217 --
218 end if;
219 --
220 hr_utility.set_location('Leaving:'||l_proc,10);
221 --
222 end chk_chg_evt_cd;
223 --
224 -- ----------------------------------------------------------------------------
225 -- |---------------------------< insert_validate >----------------------------|
226 -- ----------------------------------------------------------------------------
227 Procedure insert_validate(p_rec in ben_xcl_shd.g_rec_type
228 ,p_effective_date in date) is
229 --
230 l_proc varchar2(72) := g_package||'insert_validate';
231 --
232 Begin
233 hr_utility.set_location('Entering:'||l_proc, 5);
234 --
235 -- Call all supporting business operations
236 --
237 --
238 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
239 --
240 chk_ext_chg_evt_log_id
241 (p_ext_chg_evt_log_id => p_rec.ext_chg_evt_log_id,
242 p_object_version_number => p_rec.object_version_number);
243 --
244 /* chk_chg_evt_cd
245 (p_ext_chg_evt_log_id => p_rec.ext_chg_evt_log_id,
246 p_chg_evt_cd => p_rec.chg_evt_cd,
247 p_effective_date => p_effective_date,
248 p_object_version_number => p_rec.object_version_number); */
249 --
250 hr_utility.set_location(' Leaving:'||l_proc, 10);
251 End insert_validate;
252 --
253 -- ----------------------------------------------------------------------------
254 -- |---------------------------< update_validate >----------------------------|
255 -- ----------------------------------------------------------------------------
256 Procedure update_validate(p_rec in ben_xcl_shd.g_rec_type
257 ,p_effective_date in date) is
258 --
259 l_proc varchar2(72) := g_package||'update_validate';
260 --
261 Begin
262 hr_utility.set_location('Entering:'||l_proc, 5);
263 --
264 -- Call all supporting business operations
265 --
266 --
267 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
268 --
269 chk_ext_chg_evt_log_id
270 (p_ext_chg_evt_log_id => p_rec.ext_chg_evt_log_id,
271 p_object_version_number => p_rec.object_version_number);
272 --
273 /*chk_chg_evt_cd
274 (p_ext_chg_evt_log_id => p_rec.ext_chg_evt_log_id,
275 p_chg_evt_cd => p_rec.chg_evt_cd,
276 p_effective_date => p_effective_date,
277 p_object_version_number => p_rec.object_version_number);*/
278 --
279 hr_utility.set_location(' Leaving:'||l_proc, 10);
280 End update_validate;
281 --
282 -- ----------------------------------------------------------------------------
283 -- |---------------------------< delete_validate >----------------------------|
284 -- ----------------------------------------------------------------------------
285 Procedure delete_validate(p_rec in ben_xcl_shd.g_rec_type
286 ,p_effective_date in date) is
287 --
288 l_proc varchar2(72) := g_package||'delete_validate';
289 --
290 Begin
291 hr_utility.set_location('Entering:'||l_proc, 5);
292 --
293 -- Call all supporting business operations
294 --
295 hr_utility.set_location(' Leaving:'||l_proc, 10);
296 End delete_validate;
297 --
298 --
299 -- ---------------------------------------------------------------------------
300 -- |---------------------< return_legislation_code >-------------------------|
301 -- ---------------------------------------------------------------------------
302 --
303 function return_legislation_code
304 (p_ext_chg_evt_log_id in number) return varchar2 is
305 --
306 -- Declare cursor
307 --
308 cursor csr_leg_code is
309 select a.legislation_code
310 from per_business_groups a,
311 ben_ext_chg_evt_log b
312 where b.ext_chg_evt_log_id = p_ext_chg_evt_log_id
313 and a.business_group_id = b.business_group_id;
314 --
315 -- Declare local variables
316 --
317 l_legislation_code varchar2(150);
318 l_proc varchar2(72) := g_package||'return_legislation_code';
319 --
320 begin
321 --
322 hr_utility.set_location('Entering:'|| l_proc, 10);
323 --
324 -- Ensure that all the mandatory parameter are not null
325 --
326 hr_api.mandatory_arg_error(p_api_name => l_proc,
327 p_argument => 'ext_chg_evt_log_id',
328 p_argument_value => p_ext_chg_evt_log_id);
329 --
330 open csr_leg_code;
331 --
332 fetch csr_leg_code into l_legislation_code;
333 --
334 if csr_leg_code%notfound then
335 --
336 close csr_leg_code;
337 --
338 -- The primary key is invalid therefore we must error
339 --
340 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
341 hr_utility.raise_error;
342 --
343 end if;
344 --
345 close csr_leg_code;
346 --
347 hr_utility.set_location(' Leaving:'|| l_proc, 20);
348 --
349 return l_legislation_code;
350 --
351 end return_legislation_code;
352 --
353 end ben_xcl_bus;