1 Package Body hxc_aps_bus as
2 /* $Header: hxcaprpsrhi.pkb 120.2 2005/09/23 10:38:57 sechandr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_aps_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_set_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_approval_period_set_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_sets 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_sets aps
36 -- , EDIT_HERE table_name(s) 333
37 where aps.approval_period_set_id = p_approval_period_set_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 --
47 g_debug:=hr_utility.debug_enabled;
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_set_id'
58 ,p_argument_value => p_approval_period_set_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_set_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_sets 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_sets aps
108 -- , EDIT_HERE table_name(s) 333
109 where aps.approval_period_set_id = p_approval_period_set_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_set_id'
130 ,p_argument_value => p_approval_period_set_id
131 );
132 --
133 if ( nvl(hxc_aps_bus.g_approval_period_set_id, hr_api.g_number)
134 = p_approval_period_set_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_aps_bus.g_legislation_code;
141 if g_debug then
145 --
142 hr_utility.set_location(l_proc, 20);
143 end if;
144 else
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_aps_bus.g_approval_period_set_id := p_approval_period_set_id;
169 hxc_aps_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_rec in hxc_aps_shd.g_rec_type
206 ) IS
207 --
208 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
209 l_error EXCEPTION;
210 l_argument varchar2(30);
211 --
212 Begin
213 --
214 -- Only proceed with the validation if a row exists for the current
215 -- record in the HR Schema.
216 --
217 IF NOT hxc_aps_shd.api_updating
218 (p_approval_period_set_id => p_rec.approval_period_set_id
219 ,p_object_version_number => p_rec.object_version_number
220 ) THEN
221 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
222 fnd_message.set_token('PROCEDURE ', l_proc);
223 fnd_message.set_token('STEP ', '5');
224 fnd_message.raise_error;
225 END IF;
226 --
227 -- EDIT_HERE: Add checks to ensure non-updateable args have
228 -- not been updated.
229 --
230 EXCEPTION
231 WHEN l_error THEN
232 hr_api.argument_changed_error
233 (p_api_name => l_proc
234 ,p_argument => l_argument);
235 WHEN OTHERS THEN
236 RAISE;
237 End chk_non_updateable_args;
238 --
239 -- ----------------------------------------------------------------------------
240 -- |-----------------------------< chk_name >---------------------------------|
241 -- ----------------------------------------------------------------------------
242 -- {Start Of Comments}
243 --
244 -- Description:
245 -- This procedure ensures that a valid and a unique Approval set name
246 -- has been entered
247 --
248 -- Pre Conditions:
249 -- None
250 --
251 -- In Arguments:
252 -- name
253 -- object_version_number
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_name
264 (
265 p_name in hxc_approval_period_sets.name%TYPE,
266 p_object_version_number in hxc_approval_period_sets.object_version_number%TYPE
267 ) IS
268 --
269 l_proc varchar2(72);
270 --
271 -- cursor to check that a duplicate approval period set name is not entered
272 --
273 CURSOR csr_chk_name IS
274 SELECT 'error'
275 FROM sys.dual
276 WHERE EXISTS (
277 SELECT 'x'
278 FROM hxc_approval_period_sets aps
279 WHERE aps.name = p_name
280 AND aps.object_version_number <> NVL(p_object_version_number, -1) );
281 --
282 l_error varchar2(5) := NULL;
283 --
284 BEGIN
285 g_debug:=hr_utility.debug_enabled;
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 name is NULL as it is a mandatory field.
292 --
293 IF p_name IS NULL
294 THEN
295 --
296 hr_utility.set_message(809, 'HXC_0053_APS_APR_PRD_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 approval set name is not unique
308 CLOSE csr_chk_name;
305 --
306 OPEN csr_chk_name;
307 FETCH csr_chk_name INTO l_error;
309 --
310 IF l_error IS NOT NULL
311 THEN
312 --
313 hr_utility.set_message(809, 'HXC_0091_APS_DUP_APR_PRD');
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_name;
323 -- ----------------------------------------------------------------------------
324 -- |-----------------------< chk_delete >-------------------------------------|
325 -- ----------------------------------------------------------------------------
326 -- {Start Of Comments}
327 --
328 -- Description:
329 -- This procedure carries out refential integrity checks
330 --
331 -- Pre Conditions:
332 -- None
333 --
334 -- In Arguments:
335 -- approval_period_set_id
336 --
337 -- Post Success:
338 -- Processing continues if the Approval Period is not being referenced
339 --
340 -- Post Failure:
341 -- An application error is raised if the Approval Period is being used.
342 --
343 -- {End Of Comments}
344 -- ----------------------------------------------------------------------------
345 Procedure chk_delete
346 (
347 p_approval_period_set_id in hxc_approval_period_sets.approval_period_set_id%TYPE
348 ) IS
349 --
350 l_proc varchar2(72);
351 --
352 l_exists VARCHAR2(6) := NULL;
353 --
354 BEGIN
355 g_debug:=hr_utility.debug_enabled;
356 if g_debug then
357 l_proc:= g_package||'chk_delete';
358 hr_utility.set_location('Entering:'||l_proc, 5);
359 end if;
360 --
361 --check that Approval Period is not being used
362 --
363
364 if g_debug then
365 hr_utility.set_location('Calling num_hierarchy_occurances: '||l_proc, 10);
366 end if;
367 l_exists := HXC_PREFERENCE_EVALUATION.num_hierarchy_occurances
368 ('TS_PER_APPROVAL_PERIODS'
369 ,1
370 ,TO_CHAR(p_approval_period_set_id));
371 if g_debug then
372 hr_utility.set_location('After calling num_hierarchy_occurances:'||l_proc,20);
373 end if;
374 --
375 IF l_exists <> 0 THEN
376 --
377 hr_utility.set_message(809, 'HXC_HEG_APS_IN_USE');
378 hr_utility.raise_error;
379 --
380 END IF;
381 --
382 if g_debug then
383 hr_utility.set_location('Entering:'||l_proc, 5);
384 end if;
385 --
386 END chk_delete;
387
388 -- ----------------------------------------------------------------------------
389 -- |---------------------------< insert_validate >----------------------------|
390 -- ----------------------------------------------------------------------------
391 Procedure insert_validate
392 (p_rec in hxc_aps_shd.g_rec_type
393 ) is
394 --
395 l_proc varchar2(72);
396 --
397 Begin
398 g_debug:=hr_utility.debug_enabled;
399 if g_debug then
400 l_proc:= g_package||'insert_validate';
401 hr_utility.set_location('Entering:'||l_proc, 5);
402 end if;
403 --
404 -- Call all supporting business operations
405 --
406 if g_debug then
407 hr_utility.set_location('Processing:'||l_proc, 10);
408 end if;
409 --
410 chk_name ( p_name => p_rec.name,
411 p_object_version_number => p_rec.object_version_number );
412 --
413 if g_debug then
414 hr_utility.set_location(' Leaving:'||l_proc, 10);
415 end if;
416 --
417 -- EDIT_HERE: As this table does not have a mandatory business_group_id
418 -- column, ensure client_info is populated by calling a suitable
419 -- ???_???_bus.set_security_group_id procedure, or add one of the following
420 -- comments:
421 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
422 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
423 --
424 --
425 if g_debug then
426 hr_utility.set_location(' Leaving:'||l_proc, 10);
427 end if;
428 End insert_validate;
429 --
430 -- ----------------------------------------------------------------------------
431 -- |---------------------------< update_validate >----------------------------|
432 -- ----------------------------------------------------------------------------
433 Procedure update_validate
434 (p_rec in hxc_aps_shd.g_rec_type
435 ) is
436 --
437 l_proc varchar2(72);
438 --
439 Begin
440 g_debug:=hr_utility.debug_enabled;
441 if g_debug then
442 l_proc:= g_package||'update_validate';
443 hr_utility.set_location('Entering:'||l_proc, 5);
444 end if;
445 --
446 -- Call all supporting business operations
447 --
448 if g_debug then
449 hr_utility.set_location('Processing:'||l_proc, 10);
450 end if;
451 --
452 chk_name ( p_name => p_rec.name,
453 p_object_version_number => p_rec.object_version_number );
454 --
455 if g_debug then
456 hr_utility.set_location(' Leaving:'||l_proc, 10);
457 end if;
458 --
459 -- EDIT_HERE: As this table does not have a mandatory business_group_id
460 -- column, ensure client_info is populated by calling a suitable
461 -- ???_???_bus.set_security_group_id procedure, or add one of the following
462 -- comments:
463 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
464 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
465 --
466 chk_non_updateable_args
467 (p_rec => p_rec
468 );
469 --
473 end if;
470 --
471 if g_debug then
472 hr_utility.set_location(' Leaving:'||l_proc, 10);
474 End update_validate;
475 --
476 -- ----------------------------------------------------------------------------
477 -- |---------------------------< delete_validate >----------------------------|
478 -- ----------------------------------------------------------------------------
479 Procedure delete_validate
480 (p_rec in hxc_aps_shd.g_rec_type
481 ) is
482 --
483 l_proc varchar2(72);
484 --
485 Begin
486 g_debug:=hr_utility.debug_enabled;
487 if g_debug then
488 l_proc:= g_package||'delete_validate';
489 hr_utility.set_location('Entering:'||l_proc, 5);
490 end if;
491 --
492 -- Call all supporting business operations
493 --
494 chk_delete
495 ( p_approval_period_set_id => p_rec.approval_period_set_id);
496 --
497 if g_debug then
498 hr_utility.set_location(' Leaving:'||l_proc, 10);
499 end if;
500 End delete_validate;
501 --
502 end hxc_aps_bus;
[Home] [Help]