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