1 Package Body per_sol_bus as
2 /* $Header: pesolrhi.pkb 115.2 2003/08/08 00:05:06 vkonda noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sol_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_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_solution_id in number
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_solutions 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_solutions sol
36 -- , EDIT_HERE table_name(s) 333
37 where sol.solution_id = p_solution_id;
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_id'
54 ,p_argument_value => p_solution_id
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_ID')
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_id in number
92 )
93 Return Varchar2 Is
94 --
95 -- Declare cursor
96 --
97 -- EDIT_HERE In the following cursor statement add join(s) between
98 -- per_solutions 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_solutions sol
106 -- , EDIT_HERE table_name(s) 333
107 where sol.solution_id = p_solution_id;
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_id'
124 ,p_argument_value => p_solution_id
125 );
126 --
127 if ( nvl(per_sol_bus.g_solution_id, hr_api.g_number)
128 = p_solution_id) 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_sol_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_sol_bus.g_solution_id := p_solution_id;
159 per_sol_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_non_updateable_args >------------------------|
167 -- ----------------------------------------------------------------------------
168 -- {Start Of Comments}
169 --
170 -- Description:
171 -- This procedure is used to ensure that non updateable attributes have
172 -- not been updated. If an attribute has been updated an error is generated.
173 --
174 -- Pre Conditions:
175 -- g_old_rec has been populated with details of the values currently in
176 -- the database.
177 --
178 -- In Arguments:
179 -- p_rec has been populated with the updated values the user would like the
180 -- record set to.
181 --
182 -- Post Success:
183 -- Processing continues if all the non updateable attributes have not
184 -- changed.
185 --
186 -- Post Failure:
187 -- An application error is raised if any of the non updatable attributes
188 -- have been altered.
189 --
190 -- {End Of Comments}
191 -- ----------------------------------------------------------------------------
192 Procedure chk_non_updateable_args
193 (p_effective_date in date
194 ,p_rec in per_sol_shd.g_rec_type
195 ) IS
196 --
197 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
198 --
199 Begin
200 --
201 -- Only proceed with the validation if a row exists for the current
202 -- record in the HR Schema.
203 --
204 IF NOT per_sol_shd.api_updating
205 (p_solution_id => p_rec.solution_id
206 ,p_object_version_number => p_rec.object_version_number
207 ) THEN
208 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
209 fnd_message.set_token('PROCEDURE ', l_proc);
210 fnd_message.set_token('STEP ', '5');
211 fnd_message.raise_error;
212 END IF;
213 --
214 -- EDIT_HERE: Add checks to ensure non-updateable args have
215 -- not been updated.
216 --
217 End chk_non_updateable_args;
218 --
219 -- ---------------------------------------------------------------------------
220 -- |------------------------< chk_vertical >-------------------------------|
221 -- ---------------------------------------------------------------------------
222 --
223 -- Description:
224 -- Checks that a vertical value is valid
225 -- - Validates that the vertical exists as a lookup code on
226 -- HR_STANDARD_LOOKUPS for the lookup type 'PER_SOLUTION_VERTICALS' with
227 -- an enabled flag set to 'Y' and the effective date between the
228 -- start date active and end date active on HR_STANDARD_LOOKUPS.
229 --
230 -- Pre-conditions:
231 -- None
232 --
233 -- In Arguments:
234 -- p_solution_id
235 -- p_vertical
236 -- p_effective_date
237 -- p_object_version_number
238 --
239 -- Post Success:
240 -- Processing continues if:
241 -- - vertical exists as a lookup code in HR_STANDARD_LOOKUPS
242 -- for the lookup type 'PER_SOLUTION_VERTICALS' where the enabled
243 -- flag is 'Y' and the effective date is between start date active
244 -- and end date active on HR_STANDARD_LOOKUPS.
245 --
246 -- Post Failure:
247 -- An application error is raised and processing is terminated if:
248 -- - vertical doesn't exist as a lookup code in HR_STANDARD_LOOKUPS
249 -- for the lookup type 'PER_SOLUTION_VERTICALS' where the enabled
250 -- flag is 'Y' and the effective date is between start date active
251 -- and end date active on HR_STANDARD_LOOKUPS.
252 --
253 -- Access Status:
254 -- Internal Table Handler Use Only.
255 --
256 procedure chk_vertical
257 (p_solution_id in per_solutions.solution_id%TYPE
258 ,p_vertical in per_solutions.vertical%TYPE
259 ,p_effective_date in date
260 ,p_object_version_number in per_solutions.object_version_number%TYPE
261 )
262 is
263 --
264 l_exists varchar2(1);
265 l_proc varchar2(72) := g_package||'chk_vertical';
266 l_api_updating boolean;
267 --
268 begin
269 hr_utility.set_location('Entering:'|| l_proc, 10);
270 --
271 -- Check mandatory parameters have been set
272 --
273 hr_api.mandatory_arg_error
274 (p_api_name => l_proc
275 ,p_argument => 'effective date'
276 ,p_argument_value => p_effective_date
277 );
278 --
279 -- Only proceed with validation if:
280 -- a) The current g_old_rec is current and
281 -- b) The vertical value has changed
282 -- c) A record is being inserted
283 --
284 l_api_updating := per_sol_shd.api_updating
285 (p_solution_id => p_solution_id
286 ,p_object_version_number => p_object_version_number
287 );
288 if ((l_api_updating
289 and nvl(per_sol_shd.g_old_rec.vertical, hr_api.g_varchar2)
290 <> nvl(p_vertical,hr_api.g_varchar2))
291 or
292 (NOT l_api_updating))
293 then
294 hr_utility.set_location(l_proc, 40);
295 --
296 -- Check if vertical is set
297 --
298 if p_vertical is not null then
299 --
300 -- Check that the vertical exists in HR_STANDARD_LOOKUPS for the
301 -- lookup type 'PER_SOLUTION_VERTICALS' with an enabled flag set to 'Y'
302 -- and that the effective date is between start date
303 -- active and end date active in HR_STANDARD_LOOKUPS.
304 --
305 if hr_api.not_exists_in_hrstanlookups
306 (p_effective_date => p_effective_date
307 ,p_lookup_type => 'PER_SOLUTION_VERTICALS'
308 ,p_lookup_code => p_vertical
309 )
310 then
311 --
312 hr_utility.set_message(801, 'HR_EDIT');
313 hr_utility.raise_error;
314 --
315 end if;
316 hr_utility.set_location(l_proc, 50);
317 --
318 end if;
319 end if;
320 hr_utility.set_location(' Leaving:'|| l_proc, 60);
321 end chk_vertical;
322 --
323 -- ----------------------------------------------------------------------------
324 -- |---------------------------< insert_validate >----------------------------|
325 -- ----------------------------------------------------------------------------
326 Procedure insert_validate
327 (p_effective_date in date
328 ,p_rec in per_sol_shd.g_rec_type
329 ) is
330 --
331 l_proc varchar2(72) := g_package||'insert_validate';
332 --
333 Begin
334 hr_utility.set_location('Entering:'||l_proc, 5);
335 --
336 -- Call all supporting business operations
337 --
338 -- No business group context. HR_STANDARD_LOOKUPS used for validation."
339 --
340 -- Validate Dependent Attributes
341 --
342 --
343 chk_vertical
344 (p_solution_id => p_rec.solution_id
345 ,p_vertical => p_rec.vertical
346 ,p_effective_date => p_effective_date
347 ,p_object_version_number => p_rec.object_version_number
348 );
349 --
350 hr_utility.set_location(' Leaving:'||l_proc, 10);
351 End insert_validate;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |---------------------------< update_validate >----------------------------|
355 -- ----------------------------------------------------------------------------
356 Procedure update_validate
357 (p_effective_date in date
358 ,p_rec in per_sol_shd.g_rec_type
359 ) is
360 --
361 l_proc varchar2(72) := g_package||'update_validate';
362 --
363 Begin
364 hr_utility.set_location('Entering:'||l_proc, 5);
365 --
366 -- Call all supporting business operations
367 --
368 -- No business group context. HR_STANDARD_LOOKUPS used for validation."
369 --
370 -- Validate Dependent Attributes
371 --
372 chk_non_updateable_args
373 (p_effective_date => p_effective_date
374 ,p_rec => p_rec
375 );
376 --
377 chk_vertical
378 (p_solution_id => p_rec.solution_id
379 ,p_vertical => p_rec.vertical
380 ,p_effective_date => p_effective_date
381 ,p_object_version_number => p_rec.object_version_number
382 );
383 --
384 --
385 hr_utility.set_location(' Leaving:'||l_proc, 10);
386 End update_validate;
387 --
388 -- ----------------------------------------------------------------------------
389 -- |---------------------------< delete_validate >----------------------------|
390 -- ----------------------------------------------------------------------------
391 Procedure delete_validate
392 (p_rec in per_sol_shd.g_rec_type
393 ) is
394 --
395 l_proc varchar2(72) := g_package||'delete_validate';
396 --
397 Begin
398 hr_utility.set_location('Entering:'||l_proc, 5);
399 --
400 -- Call all supporting business operations
401 --
402 hr_utility.set_location(' Leaving:'||l_proc, 10);
403 End delete_validate;
404 --
405 end per_sol_bus;