1 Package Body hxc_hsd_bus as
2 /* $Header: hxchsdrhi.pkb 120.3 2005/09/23 10:44:51 sechandr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_hsd_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_object_id number default null;
16 g_object_type varchar2(80) default null;
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< set_security_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_object_id in number
24 ,p_object_type in varchar2
25 ,p_associated_column1 in varchar2 default null
26 ,p_associated_column2 in varchar2 default null
27 ) is
28 --
29 -- Declare cursor
30 --
31 -- EDIT_HERE In the following cursor statement add join(s) between
32 -- hxc_seeddata_by_level and PER_BUSINESS_GROUPS_PERF
33 -- so that the security_group_id for
34 -- the current business group context can be derived.
35 -- Remove this comment when the edit has been completed.
36 cursor csr_sec_grp is
37 select pbg.security_group_id,
38 pbg.legislation_code
39 from per_business_groups_perf pbg
40 , hxc_seeddata_by_level hsd
41 -- , EDIT_HERE table_name(s) 333
42 where hsd.object_id = p_object_id
43 and hsd.object_type = p_object_type;
44 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
45 --
46 -- Declare local variables
47 --
48 l_security_group_id number;
49 l_proc varchar2(72);
50 l_legislation_code varchar2(150);
51 --
52 begin
53 --
54 g_debug:=hr_utility.debug_enabled;
55 if g_debug then
56 l_proc := g_package||'set_security_group_id';
57 hr_utility.set_location('Entering:'|| l_proc, 10);
58 end if;
59 --
60 -- Ensure that all the mandatory parameter are not null
61 --
62 hr_api.mandatory_arg_error
63 (p_api_name => l_proc
64 ,p_argument => 'object_id'
65 ,p_argument_value => p_object_id
66 );
67 hr_api.mandatory_arg_error
68 (p_api_name => l_proc
69 ,p_argument => 'object_type'
70 ,p_argument_value => p_object_type
71 );
72 --
73 open csr_sec_grp;
74 fetch csr_sec_grp into l_security_group_id
75 , l_legislation_code;
76 --
77 if csr_sec_grp%notfound then
78 --
79 close csr_sec_grp;
80 --
81 -- The primary key is invalid therefore we must error
82 --
83 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
84 hr_multi_message.add
85 (p_associated_column1
86 => nvl(p_associated_column1,'OBJECT_ID')
87 ,p_associated_column2
88 => nvl(p_associated_column2,'OBJECT_TYPE')
89 );
90 --
91 else
92 close csr_sec_grp;
93 --
94 -- Set the security_group_id in CLIENT_INFO
95 --
96 hr_api.set_security_group_id
97 (p_security_group_id => l_security_group_id
98 );
99 --
100 -- Set the sessions legislation context in HR_SESSION_DATA
101 --
102 hr_api.set_legislation_context(l_legislation_code);
103 end if;
104 --
105 if g_debug then
106 hr_utility.set_location(' Leaving:'|| l_proc, 20);
107 end if;
108 --
109 end set_security_group_id;
110 --
111 -- ---------------------------------------------------------------------------
112 -- |---------------------< return_legislation_code >-------------------------|
113 -- ---------------------------------------------------------------------------
114 --
115 Function return_legislation_code
116 (p_object_id in number
117 ,p_object_type in varchar2
118 )
119 Return Varchar2 Is
120 --
121 -- Declare cursor
122 --
123 -- EDIT_HERE In the following cursor statement add join(s) between
124 -- hxc_seeddata_by_level and PER_BUSINESS_GROUPS_PERF
125 -- so that the legislation_code for
126 -- the current business group context can be derived.
127 -- Remove this comment when the edit has been completed.
128 cursor csr_leg_code is
129 select pbg.legislation_code
130 from per_business_groups_perf pbg
131 , hxc_seeddata_by_level hsd
132 -- , EDIT_HERE table_name(s) 333
133 where hsd.object_id = p_object_id
134 and hsd.object_type = p_object_type;
135 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
136 --
137 -- Declare local variables
138 --
139 l_legislation_code varchar2(150);
140 l_proc varchar2(72);
141 --
142 Begin
143 --
144 g_debug:=hr_utility.debug_enabled;
145 if g_debug then
146 l_proc := g_package||'return_legislation_code';
147 hr_utility.set_location('Entering:'|| l_proc, 10);
148 end if;
149 --
150 -- Ensure that all the mandatory parameter are not null
151 --
152 hr_api.mandatory_arg_error
153 (p_api_name => l_proc
154 ,p_argument => 'object_id'
155 ,p_argument_value => p_object_id
156 );
157 hr_api.mandatory_arg_error
158 (p_api_name => l_proc
159 ,p_argument => 'object_type'
160 ,p_argument_value => p_object_type
161 );
162 --
163 if (( nvl(hxc_hsd_bus.g_object_id, hr_api.g_number)
164 = p_object_id)
165 and ( nvl(hxc_hsd_bus.g_object_type, hr_api.g_varchar2)
166 = p_object_type)) then
167 --
168 -- The legislation code has already been found with a previous
169 -- call to this function. Just return the value in the global
170 -- variable.
171 --
172 l_legislation_code := hxc_hsd_bus.g_legislation_code;
173 if g_debug then
174 hr_utility.set_location(l_proc, 20);
175 end if;
176 else
177 --
178 -- The ID is different to the last call to this function
179 -- or this is the first call to this function.
180 --
181 open csr_leg_code;
182 fetch csr_leg_code into l_legislation_code;
183 --
184 if csr_leg_code%notfound then
185 --
186 -- The primary key is invalid therefore we must error
187 --
188 close csr_leg_code;
189 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
190 fnd_message.raise_error;
191 end if;
192 if g_debug then
193 hr_utility.set_location(l_proc,30);
194 end if;
195 --
196 -- Set the global variables so the values are
197 -- available for the next call to this function.
198 --
199 close csr_leg_code;
200 hxc_hsd_bus.g_object_id := p_object_id;
201 hxc_hsd_bus.g_object_type := p_object_type;
202 hxc_hsd_bus.g_legislation_code := l_legislation_code;
203 end if;
204 if g_debug then
205 hr_utility.set_location(' Leaving:'|| l_proc, 40);
206 end if;
207 return l_legislation_code;
208 end return_legislation_code;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |-----------------------< chk_non_updateable_args >------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- This procedure is used to ensure that non updateable attributes have
217 -- not been updated. If an attribute has been updated an error is generated.
218 --
219 -- Pre Conditions:
220 -- g_old_rec has been populated with details of the values currently in
221 -- the database.
222 --
223 -- In Arguments:
224 -- p_rec has been populated with the updated values the user would like the
225 -- record set to.
226 --
227 -- Post Success:
228 -- Processing continues if all the non updateable attributes have not
229 -- changed.
230 --
231 -- Post Failure:
232 -- An application error is raised if any of the non updatable attributes
233 -- have been altered.
234 --
235 -- {End Of Comments}
236 -- ----------------------------------------------------------------------------
237 Procedure chk_non_updateable_args
238 (p_rec in hxc_hsd_shd.g_rec_type
239 ) IS
240 --
241 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
242 --
243 Begin
244 --
245 -- Only proceed with the validation if a row exists for the current
246 -- record in the HR Schema.
247 --
248 IF NOT hxc_hsd_shd.api_updating
249 (p_object_id => p_rec.object_id
250 ,p_object_type => p_rec.object_type
251 ) THEN
252 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
253 fnd_message.set_token('PROCEDURE ', l_proc);
254 fnd_message.set_token('STEP ', '5');
255 fnd_message.raise_error;
256 END IF;
257 --
258 -- EDIT_HERE: Add checks to ensure non-updateable args have
259 -- not been updated.
260 --
261 End chk_non_updateable_args;
262 --
263
264 --
265 -- ----------------------------------------------------------------------------
266 -- |--------------------------< chk_hxc_required >----------------------------|
267 -- ----------------------------------------------------------------------------
268 -- {Start Of Comments}
269 --
270 -- Description:
271 -- This procedure is used to ensure that the p_hxc_required parameter has a
272 -- valid value i.e. it must be one of the the lookup codes corresponding
273 -- to the lookup type 'HXC_REQUIRED'.
274 --
275 -- Pre Conditions:
276 -- None
277 --
278 -- In Arguments:
279 -- p_hxc_required
280 --
281 -- Post Success:
282 -- Processing continues.
283 --
284 -- Post Failure:
285 -- An application error is raised if p_hxc_required is invalid.
286 --
287 -- {End Of Comments}
288 -- ----------------------------------------------------------------------------
289
290
291 Procedure chk_hxc_required
292 (p_hxc_required in hxc_seeddata_by_level.hxc_required%TYPE
293 ) IS
294
295 Cursor c_chk_required
296 IS
297 Select 'Y'
298 from hr_lookups hrl
299 where hrl.lookup_type = 'HXC_REQUIRED'
300 and hrl.lookup_code = p_hxc_required;
301
302 l_dummy varchar2(1);
303 l_proc varchar2(72) := g_package||'chk_hxc_required';
304 Begin
305
306 if p_hxc_required is null then
307 hr_utility.set_message
308 (809
309 ,'HXC_HSD_INV_REQ_LEVEL'
310 );
311 hr_utility.raise_error;
312 end if;
313
314 Open c_chk_required;
315 Fetch c_chk_required into l_dummy;
316
317 if (c_chk_required%NOTFOUND) then
318 Close c_chk_required;
319
320 hr_utility.set_message
321 (809
322 ,'HXC_HSD_INV_REQ_LEVEL'
323 );
324 hr_utility.raise_error;
325
326 end if;
327 Close c_chk_required;
328
329
330 End chk_hxc_required;
331
332
333 --
334 -- ----------------------------------------------------------------------------
335 -- |--------------------------< chk_application_id >----------------------------|
336 -- ----------------------------------------------------------------------------
337 -- {Start Of Comments}
338 --
339 -- Description:
340 -- This procedure is used to ensure that the p_owner_application_id refers to
341 -- a valid application id.
342 --
343 -- Pre Conditions:
344 -- None
345 --
346 -- In Arguments:
347 -- p_owner_application_id
348 --
349 -- Post Success:
350 -- Processing continues.
351 --
352 -- Post Failure:
353 -- An application error is raised if p_owner_application_id is invalid.
354 --
355 -- {End Of Comments}
356 -- ----------------------------------------------------------------------------
357
358 Procedure chk_application_id
359 (p_owner_application_id in hxc_seeddata_by_level.owner_application_id%TYPE)
360 IS
361
362 Cursor c_chk_application is
363 select 'Y' from fnd_application
364 where application_id = p_owner_application_id;
365
366 l_dummy varchar2(1);
367 l_proc varchar2(72) := g_package||'chk_application_id';
368 Begin
369
370 if p_owner_application_id is null then
371 hr_utility.set_message
372 (809
373 ,'HXC_HSD_INV_APPL'
374 );
375 hr_utility.raise_error;
376 end if;
377
378 Open c_chk_application;
379 Fetch c_chk_application into l_dummy;
380 if (c_chk_application%NOTFOUND) then
381
382 Close c_chk_application;
383
384 hr_utility.set_message
385 (809
386 ,'HXC_HSD_INV_APPL'
387 );
388 hr_utility.raise_error;
389
390 end if;
391 Close c_chk_application;
392
393
394 End chk_application_id;
395
396
397 --
398 -- ----------------------------------------------------------------------------
399 -- |--------------------------< chk_object >----------------------------------|
400 -- ----------------------------------------------------------------------------
401 -- {Start Of Comments}
402 --
403 -- Description:
404 -- This procedure is used to ensure that the p_object_type has a valid
405 -- value i.e. it must be one of the lookup codes corresponding to the
406 -- lookup type 'HXC_SEED_DATA_REFERENCE'. Also p_object_id must be a valid
407 -- object_id in the table corresponding to the p_object_type.
408
409 --
410 -- Pre Conditions:
411 -- None
412 --
413 -- In Arguments:
414 -- p_object_id
415 -- p_object_type
416 --
417 -- Post Success:
418 -- Processing continues.
419 --
420 -- Post Failure:
421 -- An application error is raised p_object_id and p_object_type are invalid.
422 --
423 -- {End Of Comments}
424 -- ----------------------------------------------------------------------------
425
426 Procedure chk_object
427 (p_object_id in hxc_seeddata_by_level.object_id%TYPE
428 ,p_object_type in hxc_seeddata_by_level.object_type%TYPE ) IS
429
430 l_query varchar2(2000);
431
432 TYPE get_value IS REF CURSOR; -- define REF CURSOR type
433 c_get_value get_value; -- declare cursor variable
434
435 l_dummy number;
436 l_proc varchar2(72) := g_package || 'chk_object';
437 Begin
438
439 if p_object_type is null then
440 hr_api.mandatory_arg_error
441 (p_api_name => l_proc
442 ,p_argument => 'object_type'
443 ,p_argument_value => p_object_type
444 );
445 end if;
446
447 if p_object_id is null then
448 hr_api.mandatory_arg_error
449 (p_api_name => l_proc
450 ,p_argument => 'object_id'
451 ,p_argument_value => p_object_id
452 );
453 end if;
454
455 --based on the object type, we shall get the query
456 l_query := hxc_seeddata_pkg.get_query(p_object_type);
457 if (l_query is null) then
458 hr_utility.set_message
459 (809
460 ,'HXC_HSD_INV_OBJ_TYP'
461 );
462 hr_utility.raise_error;
463 end if;
464
465 l_query := 'select 1 from ('||l_query||') where ID = :p_object_id';
466
467 OPEN c_get_value FOR l_query USING p_object_id;
468 FETCH c_get_value INTO l_dummy;
469 IF (c_get_value%NOTFOUND) then
470 CLOSE c_get_value;
471 hr_utility.set_message
472 (809
473 ,'HXC_HSD_INV_OBJ_ID'
474 );
475 hr_utility.raise_error;
476 END IF;
477 CLOSE c_get_value;
478
479 End chk_object;
480 -- ----------------------------------------------------------------------------
481 -- |---------------------------< insert_validate >----------------------------|
482 -- ----------------------------------------------------------------------------
483 Procedure insert_validate
484 (p_rec in hxc_hsd_shd.g_rec_type
485 ) is
486 --
487 l_proc varchar2(72);
488 --
489 Begin
490 g_debug:=hr_utility.debug_enabled;
491 if g_debug then
492 l_proc := g_package||'insert_validate';
493 hr_utility.set_location('Entering:'||l_proc, 5);
494 end if;
495 --
496 -- Call all supporting business operations
497 --
498 --
499 -- EDIT_HERE: As this table does not have a mandatory business_group_id
500 -- column, ensure client_info is populated by calling a suitable
501 -- ???_???_bus.set_security_group_id procedure, or add one of the following
502 -- comments:
503 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
504 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
505 --
506 -- Validate Dependent Attributes
507 --
508 --
509
510 chk_object
511 (p_object_id => p_rec.object_id
512 ,p_object_type => p_rec.object_type );
513
514 chk_application_id
515 (p_owner_application_id => p_rec.owner_application_id);
516
517 chk_hxc_required
518 (p_hxc_required => p_rec.hxc_required
519 ) ;
520
521
522 if g_debug then
523 hr_utility.set_location(' Leaving:'||l_proc, 10);
524 end if;
525
526 End insert_validate;
527 --
528 -- ----------------------------------------------------------------------------
529 -- |---------------------------< update_validate >----------------------------|
530 -- ----------------------------------------------------------------------------
531 Procedure update_validate
532 (p_rec in hxc_hsd_shd.g_rec_type
533 ) is
534 --
535 l_proc varchar2(72);
536 --
537 Begin
538 g_debug:=hr_utility.debug_enabled;
539 if g_debug then
540 l_proc := g_package||'update_validate';
541 hr_utility.set_location('Entering:'||l_proc, 5);
542 end if;
543 --
544 -- Call all supporting business operations
545 --
546 --
547 -- EDIT_HERE: As this table does not have a mandatory business_group_id
548 -- column, ensure client_info is populated by calling a suitable
549 -- ???_???_bus.set_security_group_id procedure, or add one of the following
550 -- comments:
551 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
552 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
553 --
554 -- Validate Dependent Attributes
555 --
556 chk_non_updateable_args
557 (p_rec => p_rec
558 );
559
560 chk_object
561 (p_object_id => p_rec.object_id
562 ,p_object_type => p_rec.object_type );
563
564 chk_application_id
565 (p_owner_application_id => p_rec.owner_application_id);
566
567
568 chk_hxc_required
569 (p_hxc_required => p_rec.hxc_required
570 ) ;
571
572 --
573 --
574 if g_debug then
575 hr_utility.set_location(' Leaving:'||l_proc, 10);
576 end if;
577 End update_validate;
578 --
579 -- ----------------------------------------------------------------------------
580 -- |---------------------------< delete_validate >----------------------------|
581 -- ----------------------------------------------------------------------------
582 Procedure delete_validate
583 (p_rec in hxc_hsd_shd.g_rec_type
584 ) is
585 --
586 l_proc varchar2(72);
587 --
588 Begin
589 g_debug:=hr_utility.debug_enabled;
590 if g_debug then
591 l_proc := g_package||'delete_validate';
592 hr_utility.set_location('Entering:'||l_proc, 5);
593 end if;
594 --
595 -- Call all supporting business operations
596 --
597 if g_debug then
598 hr_utility.set_location(' Leaving:'||l_proc, 10);
599 end if;
600 End delete_validate;
601 --
602 end hxc_hsd_bus;
[Home] [Help]