1 Package Body per_sls_bus as
2 /* $Header: peslsrhi.pkb 115.2 2003/08/07 23:58:22 vkonda noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sls_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_set_name varchar2(120) default null;
15 g_user_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_solution_set_name in varchar2
23 ,p_user_id in number
24 ,p_associated_column1 in varchar2 default null
25 ,p_associated_column2 in varchar2 default null
26 ) is
27 --
28 -- Declare cursor
29 --
30 -- EDIT_HERE In the following cursor statement add join(s) between
31 -- per_solution_sets and PER_BUSINESS_GROUPS
32 -- so that the security_group_id for
33 -- the current business group context can be derived.
34 -- Remove this comment when the edit has been completed.
35 cursor csr_sec_grp is
36 select pbg.security_group_id
37 from per_business_groups pbg
38 , per_solution_sets sls
39 -- , EDIT_HERE table_name(s) 333
40 where sls.solution_set_name = p_solution_set_name
41 and sls.user_id = p_user_id;
42 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
43 --
44 -- Declare local variables
45 --
46 l_security_group_id number;
47 l_proc varchar2(72) := g_package||'set_security_group_id';
48 --
49 begin
50 --
51 hr_utility.set_location('Entering:'|| l_proc, 10);
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 => 'solution_set_name'
58 ,p_argument_value => p_solution_set_name
59 );
60 hr_api.mandatory_arg_error
61 (p_api_name => l_proc
62 ,p_argument => 'user_id'
63 ,p_argument_value => p_user_id
64 );
65 --
66 open csr_sec_grp;
67 fetch csr_sec_grp into l_security_group_id;
68 --
69 if csr_sec_grp%notfound then
70 --
71 close csr_sec_grp;
72 --
73 -- The primary key is invalid therefore we must error
74 --
75 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
76 hr_multi_message.add
77 (p_associated_column1
78 => nvl(p_associated_column1,'SOLUTION_SET_NAME')
79 ,p_associated_column2
80 => nvl(p_associated_column2,'USER_ID')
81 );
82 --
83 else
84 close csr_sec_grp;
85 --
86 -- Set the security_group_id in CLIENT_INFO
87 --
88 hr_api.set_security_group_id
89 (p_security_group_id => l_security_group_id
90 );
91 end if;
92 --
93 hr_utility.set_location(' Leaving:'|| l_proc, 20);
94 --
95 end set_security_group_id;
96 --
97 -- ---------------------------------------------------------------------------
98 -- |---------------------< return_legislation_code >-------------------------|
99 -- ---------------------------------------------------------------------------
100 --
101 Function return_legislation_code
102 (p_solution_set_name in varchar2
103 ,p_user_id in number
104 )
105 Return Varchar2 Is
106 --
107 -- Declare cursor
108 --
109 -- EDIT_HERE In the following cursor statement add join(s) between
110 -- per_solution_sets and PER_BUSINESS_GROUPS
111 -- so that the legislation_code for
112 -- the current business group context can be derived.
113 -- Remove this comment when the edit has been completed.
114 cursor csr_leg_code is
115 select pbg.legislation_code
116 from per_business_groups pbg
117 , per_solution_sets sls
118 -- , EDIT_HERE table_name(s) 333
119 where sls.solution_set_name = p_solution_set_name
120 and sls.user_id = p_user_id;
121 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
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 => 'solution_set_name'
137 ,p_argument_value => p_solution_set_name
138 );
139 hr_api.mandatory_arg_error
140 (p_api_name => l_proc
141 ,p_argument => 'user_id'
142 ,p_argument_value => p_user_id
143 );
144 --
145 if (( nvl(per_sls_bus.g_solution_set_name, hr_api.g_varchar2)
146 = p_solution_set_name)
147 and ( nvl(per_sls_bus.g_user_id, hr_api.g_number)
148 = p_user_id)) 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_sls_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_sls_bus.g_solution_set_name := p_solution_set_name;
179 per_sls_bus.g_user_id := p_user_id;
180 per_sls_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_sls_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_sls_shd.api_updating
226 (p_solution_set_name => p_rec.solution_set_name
227 ,p_user_id => p_rec.user_id
228 ,p_object_version_number => p_rec.object_version_number
229 ) THEN
230 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
231 fnd_message.set_token('PROCEDURE ', l_proc);
232 fnd_message.set_token('STEP ', '5');
233 fnd_message.raise_error;
234 END IF;
235 --
236 -- EDIT_HERE: Add checks to ensure non-updateable args have
237 -- not been updated.
238 --
239 End chk_non_updateable_args;
240 --
241 -- ---------------------------------------------------------------------------
242 -- |-------------------------< chk_status >--------------------------------|
243 -- ---------------------------------------------------------------------------
244 --
245 -- Description:
246 -- Checks that a status value is valid
247 -- - Validates that the status exists as a lookup code on
248 -- HR_STANDARD_LOOKUPS for the lookup type 'PER_SOLUTION_STATUSES' with
249 -- an enabled flag set to 'Y' and the effective date between the
250 -- start date active and end date active on HR_STANDARD_LOOKUPS.
251 --
252 -- Pre-conditions:
253 -- None
254 --
255 -- In Arguments:
256 -- p_solution_set_name
257 -- p_user_id
258 -- p_status
259 -- p_effective_date
260 -- p_object_version_number
261 --
262 -- Post Success:
263 -- Processing continues if:
264 -- - status exists as a lookup code in HR_STANDARD_LOOKUPS
265 -- for the lookup type 'PER_SOLUTION_STATUSES' where the enabled
266 -- flag is 'Y' and the effective date is between start date active
267 -- and end date active on HR_STANDARD_LOOKUPS.
268 --
269 -- Post Failure:
270 -- An application error is raised and processing is terminated if:
271 -- - status doesn't exist as a lookup code in HR_STANDARD_LOOKUPS
272 -- for the lookup type 'PER_SOLUTION_STATUSES' where the enabled
273 -- flag is 'Y' and the effective date is between start date active
274 -- and end date active on HR_STANDARD_LOOKUPS.
275 --
276 -- Access Status:
277 -- Internal Table Handler Use Only.
278 --
279 procedure chk_status
280 (p_solution_set_name in per_solution_sets.solution_set_name%TYPE
281 ,p_user_id in per_solution_sets.user_id%TYPE
282 ,p_status in per_solution_sets.status%TYPE
283 ,p_effective_date in date
284 ,p_object_version_number in per_solution_sets.object_version_number%TYPE
285 )
286 is
287 --
288 l_exists varchar2(1);
289 l_proc varchar2(72) := g_package||'chk_status';
290 l_api_updating boolean;
291 --
292 begin
293 hr_utility.set_location('Entering:'|| l_proc, 10);
294 --
295 -- Check mandatory parameters have been set
296 --
297 hr_api.mandatory_arg_error
298 (p_api_name => l_proc
299 ,p_argument => 'effective date'
300 ,p_argument_value => p_effective_date
301 );
302 --
303 -- Only proceed with validation if:
304 -- a) The current g_old_rec is current and
305 -- b) The solution category value has changed
306 -- c) A record is being inserted
307 --
308 hr_utility.set_location(l_proc, 30);
309 l_api_updating := per_sls_shd.api_updating
310 (p_solution_set_name => p_solution_set_name
311 ,p_user_id => p_user_id
312 ,p_object_version_number => p_object_version_number
313 );
314 if ((l_api_updating
315 and nvl(per_sls_shd.g_old_rec.status, hr_api.g_varchar2)
316 <> nvl(p_status,hr_api.g_varchar2))
317 or
318 (NOT l_api_updating))
319 then
320 hr_utility.set_location(l_proc, 40);
321 --
322 -- Check if solution category is set
323 --
324 if p_status is not null then
325 --
326 -- Check that the status exists in HR_STANDARD_LOOKUPS for the
327 -- lookup type 'PER_SOLUTION_STATUSES' with an enabled flag set to 'Y'
328 -- and that the effective date is between start date
329 -- active and end date active in HR_STANDARD_LOOKUPS.
330 --
331 if hr_api.not_exists_in_hrstanlookups
332 (p_effective_date => p_effective_date
333 ,p_lookup_type => 'PER_SOLUTION_STATUSES'
334 ,p_lookup_code => p_status
335 )
336 then
337 --
338 hr_utility.set_message(801, 'HR_EDIT');
339 hr_utility.raise_error;
340 --
341 end if;
342 hr_utility.set_location(l_proc, 50);
343 --
344 end if;
345 end if;
346 hr_utility.set_location(' Leaving:'|| l_proc, 60);
347 end chk_status;
348 --
349 -- ----------------------------------------------------------------------------
350 -- |---------------------------< insert_validate >----------------------------|
351 -- ----------------------------------------------------------------------------
352 Procedure insert_validate
353 (p_effective_date in date
354 ,p_rec in per_sls_shd.g_rec_type
355 ) is
356 --
357 l_proc varchar2(72) := g_package||'insert_validate';
358 --
359 Begin
360 hr_utility.set_location('Entering:'||l_proc, 5);
364 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
361 --
362 -- Call all supporting business operations
363 --
365 --
366 -- Validate Dependent Attributes
367 --
368 chk_status
369 (p_solution_set_name => p_rec.solution_set_name
370 ,p_user_id => p_rec.user_id
371 ,p_status => p_rec.status
372 ,p_effective_date => p_effective_date
373 ,p_object_version_number => p_rec.object_version_number
374 );
375 --
376 hr_utility.set_location(' Leaving:'||l_proc, 10);
377 End insert_validate;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |---------------------------< update_validate >----------------------------|
381 -- ----------------------------------------------------------------------------
382 Procedure update_validate
383 (p_effective_date in date
384 ,p_rec in per_sls_shd.g_rec_type
385 ) is
386 --
387 l_proc varchar2(72) := g_package||'update_validate';
388 --
389 Begin
390 hr_utility.set_location('Entering:'||l_proc, 5);
391 --
392 -- Call all supporting business operations
393 --
394 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
395 --
396 -- Validate Dependent Attributes
397 --
398 chk_non_updateable_args
399 (p_effective_date => p_effective_date
400 ,p_rec => p_rec
401 );
402 --
403 chk_status
404 (p_solution_set_name => p_rec.solution_set_name
405 ,p_user_id => p_rec.user_id
406 ,p_status => p_rec.status
407 ,p_effective_date => p_effective_date
408 ,p_object_version_number => p_rec.object_version_number
409 );
410 --
411 --
412 hr_utility.set_location(' Leaving:'||l_proc, 10);
413 End update_validate;
414 --
415 -- ----------------------------------------------------------------------------
416 -- |---------------------------< delete_validate >----------------------------|
417 -- ----------------------------------------------------------------------------
418 Procedure delete_validate
419 (p_rec in per_sls_shd.g_rec_type
420 ) is
421 --
422 l_proc varchar2(72) := g_package||'delete_validate';
423 --
424 Begin
425 hr_utility.set_location('Entering:'||l_proc, 5);
426 --
427 -- Call all supporting business operations
428 --
429 hr_utility.set_location(' Leaving:'||l_proc, 10);
430 End delete_validate;
431 --
432 end per_sls_bus;
[Home] [Help]