1 Package Body ben_bcd_bus as
2 /* $Header: bebcdrhi.pkb 120.0 2005/05/28 00:34:41 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_bcd_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_cwb_matrix_dtl_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cwb_matrix_dtl_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , ben_cwb_matrix_dtl bcd
31 where bcd.cwb_matrix_dtl_id = p_cwb_matrix_dtl_id
32 and pbg.business_group_id = bcd.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'cwb_matrix_dtl_id'
48 ,p_argument_value => p_cwb_matrix_dtl_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 => nvl(p_associated_column1,'CWB_MATRIX_DTL_ID')
64 );
65 --
66 else
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 end if;
75 --
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_cwb_matrix_dtl_id in number
86 )
87 Return Varchar2 Is
88 --
89 -- Declare cursor
90 --
91 cursor csr_leg_code is
92 select pbg.legislation_code
93 from per_business_groups pbg
94 , ben_cwb_matrix_dtl bcd
95 where bcd.cwb_matrix_dtl_id = p_cwb_matrix_dtl_id
96 and pbg.business_group_id = bcd.business_group_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'cwb_matrix_dtl_id'
112 ,p_argument_value => p_cwb_matrix_dtl_id
113 );
114 --
115 if ( nvl(ben_bcd_bus.g_cwb_matrix_dtl_id, hr_api.g_number)
116 = p_cwb_matrix_dtl_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := ben_bcd_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
145 close csr_leg_code;
146 ben_bcd_bus.g_cwb_matrix_dtl_id := p_cwb_matrix_dtl_id;
147 ben_bcd_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------< chk_non_updateable_args >------------------------|
155 -- ----------------------------------------------------------------------------
156 -- {Start Of Comments}
157 --
158 -- Description:
159 -- This procedure is used to ensure that non updateable attributes have
160 -- not been updated. If an attribute has been updated an error is generated.
161 --
162 -- Pre Conditions:
163 -- g_old_rec has been populated with details of the values currently in
164 -- the database.
165 --
166 -- In Arguments:
167 -- p_rec has been populated with the updated values the user would like the
168 -- record set to.
169 --
170 -- Post Success:
171 -- Processing continues if all the non updateable attributes have not
172 -- changed.
173 --
174 -- Post Failure:
175 -- An application error is raised if any of the non updatable attributes
176 -- have been altered.
177 --
178 -- {End Of Comments}
179 -- ----------------------------------------------------------------------------
180 Procedure chk_non_updateable_args
181 (p_rec in ben_bcd_shd.g_rec_type
182 ) IS
183 --
184 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
185 --
186 Begin
187 --
188 -- Only proceed with the validation if a row exists for the current
189 -- record in the HR Schema.
190 --
191 IF NOT ben_bcd_shd.api_updating
192 (p_cwb_matrix_dtl_id => p_rec.cwb_matrix_dtl_id
193 ,p_object_version_number => p_rec.object_version_number
194 ) THEN
195 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
196 fnd_message.set_token('PROCEDURE ', l_proc);
197 fnd_message.set_token('STEP ', '5');
198 fnd_message.raise_error;
199 END IF;
200 --
201 -- EDIT_HERE: Add checks to ensure non-updateable args have
202 -- not been updated.
203 --
204 End chk_non_updateable_args;
205 --
206 -- ----------------------------------------------------------------------------
207 -- |-----------------------------< chk_cwb_matrix_id >------------------------|
208 -- ----------------------------------------------------------------------------
209 --
210 -- Description
211 -- This procedure checks that a referenced foreign key actually exists
212 -- in the referenced table.
213 --
214 -- Pre-Conditions
215 -- None.
216 --
217 -- In Parameters
218 -- p_cwb_matrix_dtl_id PK
219 -- p_cwb_matrix_id ID of FK column
220 -- p_object_version_number object version number
221 --
222 -- Post Success
223 -- Processing continues
224 --
225 -- Post Failure
226 -- Error raised.
227 --
228 -- Access Status
229 -- Internal table handler use only.
230 --
231 Procedure chk_cwb_matrix_id (p_cwb_matrix_dtl_id in number,
232 p_cwb_matrix_id in number,
233 p_object_version_number in number) is
234 --
235 l_proc varchar2(72) := g_package||'chk_cwb_matrix_id';
236 l_api_updating boolean;
237 l_dummy varchar2(1);
238 --
239 cursor c1 is
240 select null
241 from ben_cwb_matrix a
242 where a.cwb_matrix_id = p_cwb_matrix_id;
243 --
244 Begin
245 --
246 hr_utility.set_location('Entering:'||l_proc,5);
247 --
248 l_api_updating := ben_bcd_shd.api_updating
249 (p_cwb_matrix_dtl_id => p_cwb_matrix_dtl_id,
250 p_object_version_number => p_object_version_number);
251 --
252 if (l_api_updating
253 and nvl(p_cwb_matrix_id, hr_api.g_number)
254 <> nvl(ben_bcd_shd.g_old_rec.cwb_matrix_id, hr_api.g_number)
255 or not l_api_updating) then
256 --
257 -- check if cwb_matrix_id value exists in
258 -- ben_cwb_matrix table
259 --
260 open c1;
261 --
262 fetch c1 into l_dummy;
263 if c1%notfound then
264 --
265 close c1;
266 --
267 -- raise error as FK does not relate to PK in ben_cwb_matrix
268 -- table.
269 --
270 ben_bcd_shd.constraint_error('BEN_CWB_MATRIX_DTL_FK1');
271 --
272 end if;
273 --
274 close c1;
275 --
276 end if;
277 --
278 hr_utility.set_location('Leaving:'||l_proc,10);
279 --
280 End chk_cwb_matrix_id;
281 --
282 -- ----------------------------------------------------------------------------
283 -- |---------------------------< insert_validate >----------------------------|
284 -- ----------------------------------------------------------------------------
285 Procedure insert_validate
286 (p_rec in ben_bcd_shd.g_rec_type
287 ) is
288 --
289 l_proc varchar2(72) := g_package||'insert_validate';
290 --
291 Begin
292 hr_utility.set_location('Entering:'||l_proc, 5);
293 --
294 -- Call all supporting business operations
295 --
296 hr_api.validate_bus_grp_id
297 (p_business_group_id => p_rec.business_group_id
298 ,p_associated_column1 => ben_bcd_shd.g_tab_nam
299 || '.BUSINESS_GROUP_ID');
300 --
301 -- After validating the set of important attributes,
302 -- if Multiple Message detection is enabled and at least
303 -- one error has been found then abort further validation.
304 --
305 hr_multi_message.end_validation_set;
306 --
307 -- Validate Dependent Attributes
308 --
309 chk_cwb_matrix_id
310 (p_cwb_matrix_dtl_id => p_rec.cwb_matrix_dtl_id,
311 p_cwb_matrix_id => p_rec.cwb_matrix_id,
312 p_object_version_number => p_rec.object_version_number);
313 --
314 hr_utility.set_location(' Leaving:'||l_proc, 10);
315 End insert_validate;
316 --
317 -- ----------------------------------------------------------------------------
318 -- |---------------------------< update_validate >----------------------------|
319 -- ----------------------------------------------------------------------------
320 Procedure update_validate
321 (p_rec in ben_bcd_shd.g_rec_type
322 ) is
323 --
324 l_proc varchar2(72) := g_package||'update_validate';
325 --
326 Begin
327 hr_utility.set_location('Entering:'||l_proc, 5);
328 --
329 -- Call all supporting business operations
330 --
331 hr_api.validate_bus_grp_id
332 (p_business_group_id => p_rec.business_group_id
333 ,p_associated_column1 => ben_bcd_shd.g_tab_nam
334 || '.BUSINESS_GROUP_ID');
335 --
336 -- After validating the set of important attributes,
337 -- if Multiple Message detection is enabled and at least
338 -- one error has been found then abort further validation.
339 --
340 hr_multi_message.end_validation_set;
341 --
342 -- Validate Dependent Attributes
343 --
344 chk_cwb_matrix_id
345 (p_cwb_matrix_dtl_id => p_rec.cwb_matrix_dtl_id,
346 p_cwb_matrix_id => p_rec.cwb_matrix_id,
347 p_object_version_number => p_rec.object_version_number);
348 --
349 chk_non_updateable_args
350 (p_rec => p_rec
351 );
352 --
353 hr_utility.set_location(' Leaving:'||l_proc, 10);
354 End update_validate;
355 --
356 -- ----------------------------------------------------------------------------
357 -- |---------------------------< delete_validate >----------------------------|
358 -- ----------------------------------------------------------------------------
359 Procedure delete_validate
360 (p_rec in ben_bcd_shd.g_rec_type
361 ) is
362 --
363 l_proc varchar2(72) := g_package||'delete_validate';
364 --
365 Begin
366 hr_utility.set_location('Entering:'||l_proc, 5);
367 --
368 -- Call all supporting business operations
369 --
370 hr_utility.set_location(' Leaving:'||l_proc, 10);
371 End delete_validate;
372 --
373 end ben_bcd_bus;