1 Package Body hxc_lck_bus as
2 /* $Header: hxclocktypesrhi.pkb 120.2 2005/09/23 08:08:21 nissharm noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_lck_bus.'; -- Global package name
9
10 g_debug boolean := hr_utility.debug_enabled;
11 --
12 -- The following two global variables are only to be
13 -- used by the return_legislation_code function.
14 --
15 g_legislation_code varchar2(150) default null;
16 g_locker_type_id number default null;
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< set_security_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_locker_type_id in number
24 ) is
25 --
26 -- Declare cursor
27 --
28 -- EDIT_HERE In the following cursor statement add join(s) between
29 -- hxc_locker_types and PER_BUSINESS_GROUPS_PERF
30 -- so that the security_group_id for
31 -- the current business group context can be derived.
32 -- Remove this comment when the edit has been completed.
33 cursor csr_sec_grp is
34 select distinct pbg.security_group_id
35 from per_business_groups_perf pbg
36 , hxc_locker_types lck
37 -- , EDIT_HERE table_name(s) 333
38 where lck.locker_type_id = p_locker_type_id;
39 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
40 --
41 -- Declare local variables
42 --
43 l_security_group_id number;
44 l_proc varchar2(72);
45 l_legislation_code varchar2(150);
46 --
47 begin
48 g_debug := hr_utility.debug_enabled;
49
50 --
51 if g_debug then
52 l_proc := g_package||'set_security_group_id';
53 hr_utility.set_location('Entering:'|| l_proc, 10);
54 end if;
55 --
56 -- Ensure that all the mandatory parameter are not null
57 --
58 hr_api.mandatory_arg_error
59 (p_api_name => l_proc
60 ,p_argument => 'locker_type_id'
61 ,p_argument_value => p_locker_type_id
62 );
63 --
64 open csr_sec_grp;
65 fetch csr_sec_grp into l_security_group_id;
66 --
67 if csr_sec_grp%notfound then
68 --
69 close csr_sec_grp;
70 --
71 -- The primary key is invalid therefore we must error
72 --
73 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
74 --
75 else
76 close csr_sec_grp;
77 --
78 -- Set the security_group_id in CLIENT_INFO
79 --
80 hr_api.set_security_group_id
81 (p_security_group_id => l_security_group_id
82 );
83
84 end if;
85 --
86 if g_debug then
87 hr_utility.set_location(' Leaving:'|| l_proc, 20);
88 end if;
89 --
90 end set_security_group_id;
91 --
92 -- ---------------------------------------------------------------------------
93 -- |---------------------< return_legislation_code >-------------------------|
94 -- ---------------------------------------------------------------------------
95 --
96 Function return_legislation_code
97 (p_locker_type_id in number
98 )
99 Return Varchar2 Is
100 --
101 -- Declare cursor
102 --
103 -- EDIT_HERE In the following cursor statement add join(s) between
104 -- hxc_locker_types and PER_BUSINESS_GROUPS_PERF
105 -- so that the legislation_code for
106 -- the current business group context can be derived.
107 -- Remove this comment when the edit has been completed.
108 cursor csr_leg_code is
109 select pbg.legislation_code
110 from per_business_groups_perf pbg
111 , hxc_locker_types lck
112 -- , EDIT_HERE table_name(s) 333
113 where lck.locker_type_id = p_locker_type_id;
114 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
115 --
116 -- Declare local variables
117 --
118 l_legislation_code varchar2(150);
119 l_proc varchar2(72) ;
120 --
121 Begin
122 g_debug := hr_utility.debug_enabled;
123
124 --
125 if g_debug then
126 l_proc := g_package||'return_legislation_code';
127 hr_utility.set_location('Entering:'|| l_proc, 10);
128 end if;
129 --
130 -- Ensure that all the mandatory parameter are not null
131 --
132 hr_api.mandatory_arg_error
133 (p_api_name => l_proc
134 ,p_argument => 'locker_type_id'
135 ,p_argument_value => p_locker_type_id
136 );
137 --
138 if ( nvl(hxc_lck_bus.g_locker_type_id, hr_api.g_number)
139 = p_locker_type_id) then
140 --
141 -- The legislation code has already been found with a previous
142 -- call to this function. Just return the value in the global
143 -- variable.
144 --
145 l_legislation_code := hxc_lck_bus.g_legislation_code;
146 if g_debug then
147 hr_utility.set_location(l_proc, 20);
148 end if;
149 else
150 --
151 -- The ID is different to the last call to this function
152 -- or this is the first call to this function.
153 --
154 open csr_leg_code;
155 fetch csr_leg_code into l_legislation_code;
156 --
157 if csr_leg_code%notfound then
158 --
159 -- The primary key is invalid therefore we must error
160 --
161 close csr_leg_code;
162 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
163 fnd_message.raise_error;
164 end if;
165 if g_debug then
166 hr_utility.set_location(l_proc,30);
167 end if;
168 --
169 -- Set the global variables so the values are
170 -- available for the next call to this function.
171 --
172 close csr_leg_code;
173 hxc_lck_bus.g_locker_type_id := p_locker_type_id;
174 hxc_lck_bus.g_legislation_code := l_legislation_code;
175 end if;
176 if g_debug then
177 hr_utility.set_location(' Leaving:'|| l_proc, 40);
178 end if;
179 return l_legislation_code;
180 end return_legislation_code;
181 --
182 -- ----------------------------------------------------------------------------
183 -- |-----------------------< chk_non_updateable_args >------------------------|
184 -- ----------------------------------------------------------------------------
185 -- {Start Of Comments}
186 --
187 -- Description:
188 -- This procedure is used to ensure that non updateable attributes have
189 -- not been updated. If an attribute has been updated an error is generated.
190 --
191 -- Pre Conditions:
192 -- g_old_rec has been populated with details of the values currently in
193 -- the database.
194 --
195 -- In Arguments:
196 -- p_rec has been populated with the updated values the user would like the
197 -- record set to.
198 --
199 -- Post Success:
200 -- Processing continues if all the non updateable attributes have not
201 -- changed.
202 --
203 -- Post Failure:
204 -- An application error is raised if any of the non updatable attributes
205 -- have been altered.
206 --
207 -- {End Of Comments}
208 -- ----------------------------------------------------------------------------
209 Procedure chk_non_updateable_args
210 (p_rec in hxc_lck_shd.g_rec_type
211 ) IS
212 --
213 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
214 --
215 Begin
216 --
217 -- Only proceed with the validation if a row exists for the current
218 -- record in the HR Schema.
219 --
220 IF NOT hxc_lck_shd.api_updating
221 (p_locker_type_id => p_rec.locker_type_id
222 ) THEN
223 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
224 fnd_message.set_token('PROCEDURE ', l_proc);
225 fnd_message.set_token('STEP ', '5');
226 fnd_message.raise_error;
227 END IF;
228 --
229 -- EDIT_HERE: Add checks to ensure non-updateable args have
230 -- not been updated.
231 --
232 End chk_non_updateable_args;
233
234 -- ----------------------------------------------------------------------------
235 -- |-----------------------< chk_locker_process_type >-------------------------|
236 -- ----------------------------------------------------------------------------
237 -- {Start Of Comments}
238 --
239 -- Description:
240 -- This procedure ensures a valid process Type and Locker Type
241 --
242 -- Pre Conditions:
243 -- None
244 --
245 -- In Arguments:
246 -- name
247 -- object_version_number
248 -- timecard_field
249 --
250 -- Post Success:
251 -- Processing continues if the process Type and locker Type are not existing
252 --
253 -- Post Failure:
254 -- An application error is raised if the name is not valid
255 --
256 -- {End Of Comments}
257 -- ----------------------------------------------------------------------------
258 Procedure chk_locker_process_type
259 (
260 p_locker_type in hxc_locker_types.locker_type%TYPE
261 ,p_process_type in hxc_locker_types.process_type%TYPE
262 ,p_locker_type_id in hxc_locker_types.locker_type_id%TYPE
263 ) IS
264 --
265 l_proc varchar2(72);
269 CURSOR csr_chk_name IS
266 --
267 -- cursor to check if the process Type and locker Type are not existing
268 --
270 SELECT 'error'
271 FROM hxc_locker_types
272 WHERE locker_type = p_locker_type
273 AND process_type = p_process_type
274 AND locker_type_id <> NVL(p_locker_type_id,9.99E125);
275
276 l_dup_name varchar2(5) := NULL;
277
278 BEGIN
279 g_debug := hr_utility.debug_enabled;
280
281 if g_debug then
282 l_proc := g_package||'chk_locker_process_type';
283 hr_utility.set_location('Entering:'||l_proc, 5);
284 end if;
285
286 -- check that the name has been entered
287
288 IF p_locker_type IS NULL THEN
289 hr_utility.set_message(809, 'HXC_LOCKER_TYPE_REQUIRED');
290 hr_utility.raise_error;
291 END IF;
292
293 IF p_process_type IS NULL THEN
294 hr_utility.set_message(809, 'HXC_PROCESS_TYPE_REQUIRED');
295 hr_utility.raise_error;
296 END IF;
297
298 if g_debug then
299 hr_utility.set_location('Processing:'||l_proc, 10);
300 end if;
301
302 -- check that the name is unique
303
304 OPEN csr_chk_name;
305 FETCH csr_chk_name INTO l_dup_name;
306 CLOSE csr_chk_name;
307 --
308 IF l_dup_name IS NOT NULL THEN
309 hr_utility.set_message(809, 'HXC_LOCKER_PROCESS_TYPE_EXISTS');
310 hr_utility.raise_error;
311 END IF;
312
313 if g_debug then
314 hr_utility.set_location('Leaving:'||l_proc, 20);
315 end if;
316
317 END chk_locker_process_type;
318 --
319 -- ----------------------------------------------------------------------------
320 -- |-----------------------< chk_delete >-------------------------------------|
321 -- ----------------------------------------------------------------------------
322 -- {Start Of Comments}
323 --
324 -- Description:
325 -- This procedure carries out refential integrity checks
326 --
327 -- Pre Conditions:
328 -- None
329 --
330 -- In Arguments:
331 -- locker_type_id
332 --
333 -- Post Success:
334 -- Processing continues if the locker_type_id is not being used
335 --
336 -- Post Failure:
337 -- An application error is raised if the locker_type_id is being used.
338 --
339 -- {End Of Comments}
340 -- ----------------------------------------------------------------------------
341 Procedure chk_delete
342 (
343 p_locker_type_id in hxc_locker_types.locker_type_id%TYPE
344 ) IS
345 --
346 l_proc varchar2(72);
347 --
348 -- cursor to check if the process Type and locker Type are used in hxc_locks.
349 --
350 CURSOR csr_chk_id_exists IS
351 SELECT 'error'
352 FROM hxc_locks
353 WHERE locker_type_id = p_locker_type_id;
354
355 CURSOR csr_chk_seed_id_exists IS
356 SELECT 'error'
357 FROM hxc_locking_rules
358 WHERE locker_type_owner_id = p_locker_type_id OR locker_type_requestor_id=p_locker_type_id;
359
360
361 l_exists_id VARCHAR2(6) := NULL;
362 l_seed_exists_id VARCHAR2(6):=NULL;
363 --
364 BEGIN
365 g_debug := hr_utility.debug_enabled;
366
367 if g_debug then
368 l_proc := g_package||'chk_delete';
369 hr_utility.set_location('Entering:'||l_proc, 5);
370 end if;
371 --
372 --check that locker_type_id is not being used
373 --
374
375 if g_debug then
376 hr_utility.set_location('Calling num_hierarchy_occurances: '||l_proc, 10);
377 end if;
378
379 OPEN csr_chk_id_exists;
380 FETCH csr_chk_id_exists INTO l_exists_id;
381 CLOSE csr_chk_id_exists;
382 --
383 IF l_exists_id IS NOT NULL
384 THEN
385 --
386 hr_utility.set_message(809, 'HXC_LOCKER_PROCESS_TYPE_USED');
387 hr_utility.raise_error;
388 --
389 END IF;
390
391 OPEN csr_chk_seed_id_exists;
392 FETCH csr_chk_seed_id_exists INTO l_seed_exists_id;
393 CLOSE csr_chk_seed_id_exists;
394
395 IF l_seed_exists_id IS NOT NULL
396 THEN
397 --
398 hr_utility.set_message(809, 'HXC_LOCKER_PROCESS_TYPE_USED');
399 hr_utility.raise_error;
400 --
401 END IF;
402 --
403 if g_debug then
404 hr_utility.set_location('Entering:'||l_proc, 5);
405 end if;
406 --
407 END chk_delete;
408 --
409 -- ----------------------------------------------------------------------------
410 -- |---------------------------< insert_validate >----------------------------|
411 -- ----------------------------------------------------------------------------
412 Procedure insert_validate
413 (p_rec in hxc_lck_shd.g_rec_type
414 ) is
415 --
416 l_proc varchar2(72);
417 --
418 Begin
419 g_debug := hr_utility.debug_enabled;
420
421 if g_debug then
422 l_proc := g_package||'insert_validate';
423 hr_utility.set_location('Entering:'||l_proc, 5);
424 end if;
425
426 --call the business validations
427
428 chk_locker_process_type
429 (p_locker_type => p_rec.locker_type
430 ,p_process_type => p_rec.process_type
431 ,p_locker_type_id => p_rec.locker_type_id
432 ) ;
433 --
434 -- Call all supporting business operations
435 --
436 --
437 -- EDIT_HERE: As this table does not have a mandatory business_group_id
438 -- column, ensure client_info is populated by calling a suitable
439 -- ???_???_bus.set_security_group_id procedure, or add one of the following
440 -- comments:
441 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
442 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
443 --
444 -- Validate Dependent Attributes
445 --
446 --
447 if g_debug then
448 hr_utility.set_location(' Leaving:'||l_proc, 10);
449 end if;
450 End insert_validate;
451 --
452 -- ----------------------------------------------------------------------------
453 -- |---------------------------< update_validate >----------------------------|
454 -- ----------------------------------------------------------------------------
455 Procedure update_validate
456 (p_rec in hxc_lck_shd.g_rec_type
457 ) is
458 --
459 l_proc varchar2(72);
460 --
461 Begin
462 g_debug := hr_utility.debug_enabled;
463
464 if g_debug then
465 l_proc := g_package||'update_validate';
466 hr_utility.set_location('Entering:'||l_proc, 5);
467 end if;
468
469 chk_locker_process_type
470 (p_locker_type => p_rec.locker_type
471 ,p_process_type => p_rec.process_type
472 ,p_locker_type_id => p_rec.locker_type_id
473 ) ;
474 --
475 -- Call all supporting business operations
476 --
477 --
478 -- EDIT_HERE: As this table does not have a mandatory business_group_id
479 -- column, ensure client_info is populated by calling a suitable
480 -- ???_???_bus.set_security_group_id procedure, or add one of the following
481 -- comments:
482 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
483 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
484 --
485 -- Validate Dependent Attributes
486 --
487 chk_non_updateable_args
488 (p_rec => p_rec
489 );
490 --
491 --
492 if g_debug then
493 hr_utility.set_location(' Leaving:'||l_proc, 10);
494 end if;
495 End update_validate;
496 --
497 -- ----------------------------------------------------------------------------
498 -- |---------------------------< delete_validate >----------------------------|
499 -- ----------------------------------------------------------------------------
500 Procedure delete_validate
501 (p_rec in hxc_lck_shd.g_rec_type
502 ) is
503 --
504 l_proc varchar2(72);
505 --
506 Begin
507 g_debug := hr_utility.debug_enabled;
508
509 if g_debug then
510 l_proc := g_package||'delete_validate';
511 hr_utility.set_location('Entering:'||l_proc, 5);
512 end if;
513 --
514 -- Call all supporting business operations
515 chk_delete
516 (p_locker_type_id => p_rec.locker_type_id);
517 --
518 if g_debug then
519 hr_utility.set_location(' Leaving:'||l_proc, 10);
520 end if;
521 End delete_validate;
522 --
523 end hxc_lck_bus;