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