1 Package Body per_stc_bus as
2 /* $Header: pestcrhi.pkb 120.1 2005/08/29 11:48:34 ndorai noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_stc_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_component_name varchar2(120) default null;
15 g_solution_type_name varchar2(120) default null;
16 /* bug# 4574419 g_legislation_code varchar2(30) default null; */
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< set_security_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure set_security_group_id
23 (p_component_name in varchar2
24 ,p_solution_type_name in varchar2
25 ,p_legislation_code in varchar2
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 -- per_solution_type_cmpts and PER_BUSINESS_GROUPS
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 from per_business_groups pbg
39 , per_solution_type_cmpts stc
40 -- , EDIT_HERE table_name(s) 333
41 where stc.component_name = p_component_name
42 and stc.solution_type_name = p_solution_type_name;
43 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
44 --
45 -- Declare local variables
46 --
47 l_security_group_id number;
48 l_proc varchar2(72) := g_package||'set_security_group_id';
49 --
50 begin
51 --
52 hr_utility.set_location('Entering:'|| l_proc, 10);
53 --
54 -- Ensure that all the mandatory parameter are not null
55 --
56 hr_api.mandatory_arg_error
57 (p_api_name => l_proc
58 ,p_argument => 'component_name'
59 ,p_argument_value => p_component_name
60 );
61 hr_api.mandatory_arg_error
62 (p_api_name => l_proc
63 ,p_argument => 'solution_type_name'
64 ,p_argument_value => p_solution_type_name
65 );
66 hr_api.mandatory_arg_error
67 (p_api_name => l_proc
68 ,p_argument => 'legislation_code'
69 ,p_argument_value => p_legislation_code
70 );
71 --
72 open csr_sec_grp;
73 fetch csr_sec_grp into l_security_group_id;
74 --
75 if csr_sec_grp%notfound then
76 --
77 close csr_sec_grp;
78 --
79 -- The primary key is invalid therefore we must error
80 --
81 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
82 hr_multi_message.add
83 (p_associated_column1
84 => nvl(p_component_name,'COMPONENT_NAME')
85 ,p_associated_column2
86 => nvl(p_solution_type_name,'SOLUTION_TYPE_NAME')
87 ,p_associated_column3
88 => nvl(p_legislation_code,'LEGISLATION_CODE')
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 end if;
100 --
101 hr_utility.set_location(' Leaving:'|| l_proc, 20);
102 --
103 end set_security_group_id;
104 --
105 -- ---------------------------------------------------------------------------
106 -- |---------------------< return_legislation_code >-------------------------|
107 -- ---------------------------------------------------------------------------
108 --
109 Function return_legislation_code
110 (p_component_name in varchar2
111 ,p_solution_type_name in varchar2
112 )
113 Return Varchar2 Is
114 --
115 -- Declare cursor
116 --
117 cursor csr_leg_code is
118 select stc.legislation_code
119 from per_solution_type_cmpts stc
120 where stc.component_name = p_component_name
121 and stc.solution_type_name = p_solution_type_name;
122 --
123 -- Declare local variables
124 --
125 l_legislation_code varchar2(150);
126 l_proc varchar2(72) := g_package||'return_legislation_code';
127 --
128 Begin
129 --
130 hr_utility.set_location('Entering:'|| l_proc, 10);
131 --
132 -- Ensure that all the mandatory parameter are not null
133 --
134 hr_api.mandatory_arg_error
135 (p_api_name => l_proc
136 ,p_argument => 'component_name'
137 ,p_argument_value => p_component_name
138 );
139 hr_api.mandatory_arg_error
140 (p_api_name => l_proc
141 ,p_argument => 'solution_type_name'
142 ,p_argument_value => p_solution_type_name
143 );
144 --
145 if (( nvl(per_stc_bus.g_component_name, hr_api.g_varchar2)
146 = p_component_name)
147 and ( nvl(per_stc_bus.g_solution_type_name, hr_api.g_varchar2)
148 = p_solution_type_name)) then
149 --
150 -- The legislation code has already been found with a previous
151 -- call to this function. Just return the value in the global
152 -- variable.
153 --
154 l_legislation_code := per_stc_bus.g_legislation_code;
155 hr_utility.set_location(l_proc, 20);
156 else
157 --
158 -- The ID is different to the last call to this function
159 -- or this is the first call to this function.
160 --
161 open csr_leg_code;
162 fetch csr_leg_code into l_legislation_code;
163 --
164 if csr_leg_code%notfound then
165 --
166 -- The primary key is invalid therefore we must error
167 --
168 close csr_leg_code;
169 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
170 fnd_message.raise_error;
171 end if;
172 hr_utility.set_location(l_proc,30);
173 --
174 -- Set the global variables so the values are
175 -- available for the next call to this function.
176 --
177 close csr_leg_code;
178 per_stc_bus.g_component_name := p_component_name;
179 per_stc_bus.g_solution_type_name := p_solution_type_name;
180 per_stc_bus.g_legislation_code := l_legislation_code;
181 end if;
182 hr_utility.set_location(' Leaving:'|| l_proc, 40);
183 return l_legislation_code;
184 end return_legislation_code;
185 --
186 -- ----------------------------------------------------------------------------
187 -- |-----------------------< chk_non_updateable_args >------------------------|
188 -- ----------------------------------------------------------------------------
189 -- {Start Of Comments}
190 --
191 -- Description:
192 -- This procedure is used to ensure that non updateable attributes have
193 -- not been updated. If an attribute has been updated an error is generated.
194 --
195 -- Pre Conditions:
196 -- g_old_rec has been populated with details of the values currently in
197 -- the database.
198 --
199 -- In Arguments:
200 -- p_rec has been populated with the updated values the user would like the
201 -- record set to.
202 --
203 -- Post Success:
204 -- Processing continues if all the non updateable attributes have not
205 -- changed.
206 --
207 -- Post Failure:
208 -- An application error is raised if any of the non updatable attributes
209 -- have been altered.
210 --
211 -- {End Of Comments}
212 -- ----------------------------------------------------------------------------
213 Procedure chk_non_updateable_args
214 (p_effective_date in date
215 ,p_rec in per_stc_shd.g_rec_type
216 ) IS
217 --
218 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
219 --
220 Begin
221 --
222 -- Only proceed with the validation if a row exists for the current
223 -- record in the HR Schema.
224 --
225 IF NOT per_stc_shd.api_updating
226 (p_component_name => p_rec.component_name
227 ,p_solution_type_name => p_rec.solution_type_name
228 ,p_legislation_code => p_rec.legislation_code
229 ,p_object_version_number => p_rec.object_version_number
230 ) THEN
231 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
232 fnd_message.set_token('PROCEDURE ', l_proc);
233 fnd_message.set_token('STEP ', '5');
234 fnd_message.raise_error;
235 END IF;
236 --
237 -- EDIT_HERE: Add checks to ensure non-updateable args have
238 -- not been updated.
239 --
240 End chk_non_updateable_args;
241 --
242 -- ---------------------------------------------------------------------------
243 -- |-----------------------< chk_updateable >------------------------------|
244 -- ---------------------------------------------------------------------------
245 --
246 -- Description:
247 -- Checks that the updateable flag value is valid
248 --
249 -- Pre-conditions:
250 -- None
251 --
252 -- In Arguments:
253 -- p_component_name
254 -- p_solution_type_name
255 -- p_legislation_code
256 -- p_updateable
257 -- p_effective_date
258 -- p_object_version_number
259 --
260 -- Post Success:
261 -- Processing continues if:
262 -- - updateable exists as a lookup code in HR_STANDARD_LOOKUPS
263 -- for the lookup type 'YES_NO' where the enabled
264 -- flag is 'Y' and the effective date is between start date active
265 -- and end date active on HR_STANDARD_LOOKUPS.
266 --
267 -- Post Failure:
268 -- An application error is raised and processing is terminated if:
269 -- - updateable doesn't exist as a lookup code in HR_STANDARD_LOOKUPS
270 -- for the lookup type 'YES_NO' where the enabled
271 -- flag is 'Y' and the effective date is between start date active
272 -- and end date active on HR_STANDARD_LOOKUPS.
273 --
274 -- Access Status:
275 -- Internal Table Handler Use Only.
276 --
277 procedure chk_updateable
278 (p_component_name in per_solution_type_cmpts.component_name%TYPE
279 ,p_solution_type_name in per_solution_type_cmpts.solution_type_name%TYPE
280 ,p_legislation_code in per_solution_type_cmpts.legislation_code%TYPE
281 ,p_updateable in per_solution_type_cmpts.updateable%TYPE
285 is
282 ,p_effective_date in date
283 ,p_object_version_number in per_solution_type_cmpts.object_version_number%TYPE
284 )
286 --
287 l_exists varchar2(1);
288 l_proc varchar2(72) := g_package||'chk_updateable';
289 l_api_updating boolean;
290 --
291 begin
292 hr_utility.set_location('Entering:'|| l_proc, 10);
293 --
294 -- Check mandatory parameters have been set
295 --
296 hr_api.mandatory_arg_error
297 (p_api_name => l_proc
298 ,p_argument => 'effective date'
299 ,p_argument_value => p_effective_date
300 );
301 --
302 hr_api.mandatory_arg_error
303 (p_api_name => l_proc
304 ,p_argument => 'updateable'
305 ,p_argument_value => p_updateable
306 );
307 --
308 -- Only proceed with validation if:
309 -- a) The current g_old_rec is current and
310 -- b) The solution category value has changed
311 -- c) A record is being inserted
312 --
313 l_api_updating := per_stc_shd.api_updating
314 (p_component_name => p_component_name
315 ,p_solution_type_name => p_solution_type_name
316 ,p_legislation_code => p_legislation_code
317 ,p_object_version_number => p_object_version_number
318 );
319 if ((l_api_updating
320 and nvl(per_stc_shd.g_old_rec.updateable, hr_api.g_varchar2)
321 <> nvl(p_updateable,hr_api.g_varchar2))
322 or
323 (NOT l_api_updating))
324 then
325 hr_utility.set_location(l_proc, 40);
326 --
327 -- Check if updateable is set
328 --
329 if p_updateable is not null then
330 --
331 -- Check that the updateable flag exists in HR_STANDARD_LOOKUPS for the
332 -- lookup type 'YES_NO' with an enabled flag set to 'Y'
333 -- and that the effective date is between start date
334 -- active and end date active in HR_STANDARD_LOOKUPS.
335 --
336 if hr_api.not_exists_in_hrstanlookups
337 (p_effective_date => p_effective_date
338 ,p_lookup_type => 'YES_NO'
339 ,p_lookup_code => p_updateable
340 )
341 then
342 --
343 hr_utility.set_message(801, 'HR_EDIT');
344 hr_utility.raise_error;
345 --
346 end if;
347 hr_utility.set_location(l_proc, 50);
348 --
349 end if;
350 end if;
351 hr_utility.set_location(' Leaving:'||l_proc,60);
352 end chk_updateable;
353 --
354 -- ---------------------------------------------------------------------------
355 -- |-----------------------< chk_extensible >------------------------------|
359 -- Checks that the extensible flag value is valid
356 -- ---------------------------------------------------------------------------
357 --
358 -- Description:
360 --
361 -- Pre-conditions:
362 -- None
363 --
364 -- In Arguments:
365 -- p_component_name
366 -- p_solution_type_name
367 -- p_legislation_code
368 -- p_extensible
369 -- p_effective_date
370 -- p_object_version_number
371 --
372 -- Post Success:
373 -- Processing continues if:
374 -- - extensible exists as a lookup code in HR_STANDARD_LOOKUPS
375 -- for the lookup type 'YES_NO' where the enabled
376 -- flag is 'Y' and the effective date is between start date active
377 -- and end date active on HR_STANDARD_LOOKUPS.
378 --
379 -- Post Failure:
380 -- An application error is raised and processing is terminated if:
381 -- - extensible doesn't exist as a lookup code in HR_STANDARD_LOOKUPS
382 -- for the lookup type 'YES_NO' where the enabled
383 -- flag is 'Y' and the effective date is between start date active
384 -- and end date active on HR_STANDARD_LOOKUPS.
385 --
386 -- Access Status:
387 -- Internal Table Handler Use Only.
388 --
389 procedure chk_extensible
390 (p_component_name in per_solution_type_cmpts.component_name%TYPE
391 ,p_solution_type_name in per_solution_type_cmpts.solution_type_name%TYPE
392 ,p_legislation_code in per_solution_type_cmpts.legislation_code%TYPE
393 ,p_extensible in per_solution_type_cmpts.extensible%TYPE
394 ,p_effective_date in date
395 ,p_object_version_number in per_solution_type_cmpts.object_version_number%TYPE
396 )
397 is
398 --
399 l_exists varchar2(1);
400 l_proc varchar2(72) := g_package||'chk_extensible';
401 l_api_updating boolean;
402 --
403 begin
404 hr_utility.set_location('Entering:'|| l_proc, 10);
405 --
406 -- Check mandatory parameters have been set
407 --
408 hr_api.mandatory_arg_error
409 (p_api_name => l_proc
410 ,p_argument => 'effective date'
411 ,p_argument_value => p_effective_date
412 );
413 --
414 hr_api.mandatory_arg_error
415 (p_api_name => l_proc
416 ,p_argument => 'extensible'
417 ,p_argument_value => p_extensible
418 );
419 --
423 -- c) A record is being inserted
420 -- Only proceed with validation if:
421 -- a) The current g_old_rec is current and
422 -- b) The solution category value has changed
424 --
425 l_api_updating := per_stc_shd.api_updating
426 (p_component_name => p_component_name
427 ,p_solution_type_name => p_solution_type_name
428 ,p_legislation_code => p_legislation_code
429 ,p_object_version_number => p_object_version_number
430 );
431 if ((l_api_updating
432 and nvl(per_stc_shd.g_old_rec.extensible, hr_api.g_varchar2)
433 <> nvl(p_extensible,hr_api.g_varchar2))
434 or
435 (NOT l_api_updating))
436 then
437 hr_utility.set_location(l_proc, 40);
438 --
439 -- Check if extensible is set
440 --
441 if p_extensible is not null then
442 --
443 -- Check that the extensible flag exists in HR_STANDARD_LOOKUPS for the
444 -- lookup type 'YES_NO' with an enabled flag set to 'Y'
445 -- and that the effective date is between start date
446 -- active and end date active in HR_STANDARD_LOOKUPS.
447 --
448 if hr_api.not_exists_in_hrstanlookups
449 (p_effective_date => p_effective_date
450 ,p_lookup_type => 'YES_NO'
451 ,p_lookup_code => p_extensible
452 )
453 then
454 --
455 hr_utility.set_message(801, 'HR_EDIT');
456 hr_utility.raise_error;
457 --
458 end if;
459 hr_utility.set_location(l_proc, 50);
460 --
461 end if;
462 end if;
463 hr_utility.set_location(' Leaving:'||l_proc,60);
464 end chk_extensible;
465 --
466 -- ----------------------------------------------------------------------------
467 -- |---------------------------< insert_validate >----------------------------|
468 -- ----------------------------------------------------------------------------
469 Procedure insert_validate
470 (p_effective_date in date
471 ,p_rec in per_stc_shd.g_rec_type
472 ) is
473 --
474 l_proc varchar2(72) := g_package||'insert_validate';
475 --
476 Begin
477 hr_utility.set_location('Entering:'||l_proc, 5);
478 --
479 -- Call all supporting business operations
480 --
481 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
482 --
483 -- Validate Dependent Attributes
484 --
485 chk_updateable
486 (p_component_name => p_rec.component_name
487 ,p_solution_type_name => p_rec.solution_type_name
488 ,p_legislation_code => p_rec.legislation_code
489 ,p_updateable => p_rec.updateable
490 ,p_effective_date => p_effective_date
491 ,p_object_version_number => p_rec.object_version_number
492 );
493 --
494 chk_extensible
495 (p_component_name => p_rec.component_name
496 ,p_solution_type_name => p_rec.solution_type_name
497 ,p_legislation_code => p_rec.legislation_code
498 ,p_extensible => p_rec.extensible
499 ,p_effective_date => p_effective_date
500 ,p_object_version_number => p_rec.object_version_number
501 );
502 --
503 hr_utility.set_location(' Leaving:'||l_proc, 10);
504 End insert_validate;
505 --
506 -- ----------------------------------------------------------------------------
507 -- |---------------------------< update_validate >----------------------------|
508 -- ----------------------------------------------------------------------------
509 Procedure update_validate
510 (p_effective_date in date
511 ,p_rec in per_stc_shd.g_rec_type
512 ) is
513 --
514 l_proc varchar2(72) := g_package||'update_validate';
515 --
516 Begin
517 hr_utility.set_location('Entering:'||l_proc, 5);
518 --
519 -- Call all supporting business operations
520 --
521 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
522 --
523 -- Validate Dependent Attributes
524 --
525 chk_non_updateable_args
526 (p_effective_date => p_effective_date
527 ,p_rec => p_rec
528 );
529 --
530 chk_updateable
531 (p_component_name => p_rec.component_name
532 ,p_solution_type_name => p_rec.solution_type_name
533 ,p_legislation_code => p_rec.legislation_code
534 ,p_updateable => p_rec.updateable
535 ,p_effective_date => p_effective_date
536 ,p_object_version_number => p_rec.object_version_number
537 );
538 --
539 chk_extensible
540 (p_component_name => p_rec.component_name
541 ,p_solution_type_name => p_rec.solution_type_name
542 ,p_legislation_code => p_rec.legislation_code
543 ,p_extensible => p_rec.extensible
544 ,p_effective_date => p_effective_date
545 ,p_object_version_number => p_rec.object_version_number
546 );
547 --
548 --
549 hr_utility.set_location(' Leaving:'||l_proc, 10);
550 End update_validate;
551 --
552 -- ----------------------------------------------------------------------------
553 -- |---------------------------< delete_validate >----------------------------|
554 -- ----------------------------------------------------------------------------
555 Procedure delete_validate
556 (p_rec in per_stc_shd.g_rec_type
557 ) is
558 --
559 l_proc varchar2(72) := g_package||'delete_validate';
560 --
561 Begin
562 hr_utility.set_location('Entering:'||l_proc, 5);
563 --
564 -- Call all supporting business operations
565 --
566 hr_utility.set_location(' Leaving:'||l_proc, 10);
567 End delete_validate;
568 --
569 end per_stc_bus;
[Home] [Help]