1 Package Body hxc_apc_bus as
2 /* $Header: hxcapcrhi.pkb 120.2 2005/09/23 08:04:24 sechandr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_apc_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_approval_period_comp_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_approval_period_comp_id in number
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- hxc_approval_period_comps and PER_BUSINESS_GROUPS
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31 -- Remove this comment when the edit has been completed.
32 cursor csr_sec_grp is
33 select pbg.security_group_id
34 from per_business_groups pbg
35 , hxc_approval_period_comps apc
36 -- , EDIT_HERE table_name(s) 333
37 where apc.approval_period_comp_id = p_approval_period_comp_id;
38 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
39 --
40 -- Declare local variables
41 --
42 l_security_group_id number;
43 l_proc varchar2(72);
44 --
45 begin
46 g_debug:=hr_utility.debug_enabled;
47 --
48 if g_debug then
49 l_proc:= g_package||'set_security_group_id';
50 hr_utility.set_location('Entering:'|| l_proc, 10);
51 end if;
52 --
53 -- Ensure that all the mandatory parameter are not null
54 --
55 hr_api.mandatory_arg_error
56 (p_api_name => l_proc
57 ,p_argument => 'approval_period_comp_id'
58 ,p_argument_value => p_approval_period_comp_id
59 );
60 --
61 open csr_sec_grp;
62 fetch csr_sec_grp into l_security_group_id;
63 --
64 if csr_sec_grp%notfound then
65 --
66 close csr_sec_grp;
67 --
68 -- The primary key is invalid therefore we must error
69 --
70 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
71 fnd_message.raise_error;
72 --
73 end if;
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 --
82 if g_debug then
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 end if;
85 --
86 end set_security_group_id;
87 --
88 -- ---------------------------------------------------------------------------
89 -- |---------------------< return_legislation_code >-------------------------|
90 -- ---------------------------------------------------------------------------
91 --
92 Function return_legislation_code
93 (p_approval_period_comp_id in number
94 )
95 Return Varchar2 Is
96 --
97 -- Declare cursor
98 --
99 -- EDIT_HERE In the following cursor statement add join(s) between
100 -- hxc_approval_period_comps and PER_BUSINESS_GROUPS
101 -- so that the legislation_code for
102 -- the current business group context can be derived.
103 -- Remove this comment when the edit has been completed.
104 cursor csr_leg_code is
105 select pbg.legislation_code
106 from per_business_groups pbg
107 , hxc_approval_period_comps apc
108 -- , EDIT_HERE table_name(s) 333
109 where apc.approval_period_comp_id = p_approval_period_comp_id;
110 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
111 --
112 -- Declare local variables
113 --
114 l_legislation_code varchar2(150);
115 l_proc varchar2(72);
116 --
117 Begin
118 --
119 g_debug:=hr_utility.debug_enabled;
120 if g_debug then
121 l_proc := g_package||'return_legislation_code';
122 hr_utility.set_location('Entering:'|| l_proc, 10);
123 end if;
124 --
125 -- Ensure that all the mandatory parameter are not null
126 --
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc
129 ,p_argument => 'approval_period_comp_id'
130 ,p_argument_value => p_approval_period_comp_id
131 );
132 --
133 if ( nvl(hxc_apc_bus.g_approval_period_comp_id, hr_api.g_number)
134 = p_approval_period_comp_id) then
135 --
136 -- The legislation code has already been found with a previous
137 -- call to this function. Just return the value in the global
138 -- variable.
139 --
140 l_legislation_code := hxc_apc_bus.g_legislation_code;
141 if g_debug then
142 hr_utility.set_location(l_proc, 20);
143 end if;
144 else
145 --
146 -- The ID is different to the last call to this function
147 -- or this is the first call to this function.
148 --
149 open csr_leg_code;
150 fetch csr_leg_code into l_legislation_code;
151 --
152 if csr_leg_code%notfound then
153 --
154 -- The primary key is invalid therefore we must error
155 --
156 close csr_leg_code;
157 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
158 fnd_message.raise_error;
159 end if;
160 if g_debug then
161 hr_utility.set_location(l_proc,30);
162 end if;
163 --
164 -- Set the global variables so the values are
165 -- available for the next call to this function.
166 --
167 close csr_leg_code;
168 hxc_apc_bus.g_approval_period_comp_id := p_approval_period_comp_id;
169 hxc_apc_bus.g_legislation_code := l_legislation_code;
170 end if;
171 if g_debug then
172 hr_utility.set_location(' Leaving:'|| l_proc, 40);
173 end if;
174 return l_legislation_code;
175 end return_legislation_code;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------< chk_non_updateable_args >------------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This procedure is used to ensure that non updateable attributes have
184 -- not been updated. If an attribute has been updated an error is generated.
185 --
186 -- Pre Conditions:
187 -- g_old_rec has been populated with details of the values currently in
188 -- the database.
189 --
190 -- In Arguments:
191 -- p_rec has been populated with the updated values the user would like the
192 -- record set to.
193 --
194 -- Post Success:
195 -- Processing continues if all the non updateable attributes have not
196 -- changed.
197 --
198 -- Post Failure:
199 -- An application error is raised if any of the non updatable attributes
200 -- have been altered.
201 --
202 -- {End Of Comments}
203 -- ----------------------------------------------------------------------------
204 Procedure chk_non_updateable_args
205 (p_effective_date in date
206 ,p_rec in hxc_apc_shd.g_rec_type
207 ) IS
208 --
209 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
210 l_error EXCEPTION;
211 l_argument varchar2(30);
212 --
213 Begin
214 --
215 -- Only proceed with the validation if a row exists for the current
216 -- record in the HR Schema.
217 --
218 IF NOT hxc_apc_shd.api_updating
219 (p_approval_period_comp_id => p_rec.approval_period_comp_id
220 ,p_object_version_number => p_rec.object_version_number
221 ) THEN
222 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
223 fnd_message.set_token('PROCEDURE ', l_proc);
224 fnd_message.set_token('STEP ', '5');
225 fnd_message.raise_error;
226 END IF;
227 --
228 -- EDIT_HERE: Add checks to ensure non-updateable args have
229 -- not been updated.
230 --
231 EXCEPTION
232 WHEN l_error THEN
233 hr_api.argument_changed_error
234 (p_api_name => l_proc
235 ,p_argument => l_argument);
236 WHEN OTHERS THEN
237 RAISE;
238 End chk_non_updateable_args;
239
240 -- ----------------------------------------------------------------------------
241 -- |-----------------------------< chk_application-------------------------------|
242 ----------------------------------------------------------------------------
243 -- {Start Of Comments} --
244 -- Description:
245 -- This procedure ensures that a valid and a unique Application for a given
246 -- Approval Period Set is entered
247 --
248 -- Pre Conditions:
249 -- None
250 --
251 -- In Arguments:
252 -- p_approval_period_set_id
253 -- p_time_recipient_id
254 --
255 -- Post Success:
256 -- Processing continues if a valid and a unique name has been entered
257 --
258 -- Post Failure:
259 -- An application error is raised if the name is not valid
260 --
261 -- {End Of Comments}
262 -- ----------------------------------------------------------------------------
263 Procedure chk_application
264 (
265 p_approval_period_set_id in number
266 ,p_time_recipient_id in number
267 ,p_approval_period_comp_id in number default NULL
268 ) is
269 --
270 l_proc varchar2(72);
271 --
272 -- cursor to check that a duplicate retrieval rule name is not entered
273 --
274 -- Bug 3035142
275 CURSOR csr_chk_app IS
276 SELECT 'error'
277 FROM hxc_approval_period_comps hapc
278 WHERE hapc.approval_period_set_id = p_approval_period_set_id
279 and hapc.time_recipient_id = p_time_recipient_id
280 and hapc.approval_period_comp_id <> nvl(p_approval_period_comp_id,hapc.approval_period_comp_id+1);
281
282 --
283 l_error varchar2(5) := NULL;
284 --
285 BEGIN
286 if g_debug then
287 l_proc := g_package||'chk_name';
288 hr_utility.set_location('Entering:'||l_proc, 5);
289 end if;
290 --
291 -- Raise error if application is NULL as it is a mandatory field.
292 --
293 IF p_time_recipient_id IS NULL
294 THEN
295 --
296 hr_utility.set_message(809, 'HXC_0153_APC_APPL_NAME_MAND');
297 hr_utility.raise_error;
298 --
299 END IF;
300 if g_debug then
301 hr_utility.set_location('Processing:'||l_proc, 10);
302 end if;
303 --
304 -- Raise an error if the application is not unique
305 --
306 OPEN csr_chk_app;
307 FETCH csr_chk_app INTO l_error;
308 CLOSE csr_chk_app;
309 --
310 IF l_error IS NOT NULL
311 THEN
312 --
313 hr_utility.set_message(809, 'HXC_0154_APC_DUP_APPL_NAME');
314 hr_utility.raise_error;
315 --
316 END IF;
317 --
318 if g_debug then
319 hr_utility.set_location('Leaving:'||l_proc, 20);
320 end if;
321 --
322 END chk_application;
323
324
325
326 --
327 -- ----------------------------------------------------------------------------
328 -- |---------------------------< insert_validate >----------------------------|
329 -- ----------------------------------------------------------------------------
330 Procedure insert_validate
331 (p_effective_date in date
332 ,p_rec in hxc_apc_shd.g_rec_type
333 ) is
334 --
335 l_proc varchar2(72);
336 --
337 Begin
338 g_debug:=hr_utility.debug_enabled;
339 if g_debug then
340 l_proc := g_package||'insert_validate';
341 hr_utility.set_location('Entering:'||l_proc, 5);
342 end if;--
343 -- Call all supporting business operations
344 --
345 --
346 -- Check for unique application name
347 --
348
349 chk_application(
350 p_approval_period_set_id => p_rec.approval_period_set_id
351 ,p_time_recipient_id => p_rec.time_recipient_id
352 );
353
354 -- EDIT_HERE: As this table does not have a mandatory business_group_id
355 -- column, ensure client_info is populated by calling a suitable
356 -- ???_???_bus.set_security_group_id procedure, or add one of the following
357 -- comments:
358 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
359 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
360 --
361 --
362 if g_debug then
363 hr_utility.set_location(' Leaving:'||l_proc, 10);
364 end if;
365 End insert_validate;
366 --
367 -- ----------------------------------------------------------------------------
368 -- |---------------------------< update_validate >----------------------------|
369 -- ----------------------------------------------------------------------------
370 Procedure update_validate
371 (p_effective_date in date
372 ,p_rec in hxc_apc_shd.g_rec_type
373 ) is
374 --
375 l_proc varchar2(72);
376 --
377 Begin
378 g_debug:=hr_utility.debug_enabled;
379 if g_debug then
380 l_proc := g_package||'update_validate';
381 hr_utility.set_location('Entering:'||l_proc, 5);
382 end if;
383 --
384 -- Call all supporting business operations
385 --
386 --
387 -- EDIT_HERE: As this table does not have a mandatory business_group_id
388 -- column, ensure client_info is populated by calling a suitable
389 -- ???_???_bus.set_security_group_id procedure, or add one of the following
390 -- comments:
391 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
392 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
393 --
394 chk_non_updateable_args
395 (p_effective_date => p_effective_date
396 ,p_rec => p_rec
397 );
398 --
399 -- Check for unique application name
400 --
404 ,p_time_recipient_id => p_rec.time_recipient_id
401 -- Bug 3035142
402 chk_application(
403 p_approval_period_set_id => p_rec.approval_period_set_id
405 ,p_approval_period_comp_id => p_rec.approval_period_comp_id
406 );
407 --
408 --
409 if g_debug then
410 hr_utility.set_location(' Leaving:'||l_proc, 10);
411 end if;
412 End update_validate;
413 --
414 -- ----------------------------------------------------------------------------
415 -- |---------------------------< delete_validate >----------------------------|
416 -- ----------------------------------------------------------------------------
417 Procedure delete_validate
418 (p_rec in hxc_apc_shd.g_rec_type
419 ) is
420 --
421 l_proc varchar2(72) ;
422 --
423 Begin
424 g_debug:=hr_utility.debug_enabled;
425 if g_debug then
426 l_proc:= g_package||'delete_validate';
427 hr_utility.set_location('Entering:'||l_proc, 5);
428 end if;--
429 -- Call all supporting business operations
430 --
431 if g_debug then
432 hr_utility.set_location(' Leaving:'||l_proc, 10);
433 end if;
434 End delete_validate;
435 --
436 end hxc_apc_bus;
437