1 Package Body per_cpl_bus as
2 /* $Header: pecplrhi.pkb 120.0.12000000.2 2007/05/30 12:19:15 arumukhe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cpl_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_competence_id number default null;
15 g_language varchar2(4) default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_competence_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25 --
26 -- Declare cursor
27 --
28 -- Amended on 23-Dec-2002 by meburton
29 -- Get the PER_BUSINESS_GROUPS
30
31 cursor csr_sec_grp is
32 select pbg.security_group_id
33 from per_business_groups pbg
34 , per_competences cpn
35 , per_competences_tl cpl
36 where cpl.competence_id = p_competence_id
37 and pbg.business_group_id = cpn.business_group_id
38 and cpl.competence_id = cpn.competence_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 => 'competence_id'
54 ,p_argument_value => p_competence_id
55 );
56 --
57 --
58 open csr_sec_grp;
59 fetch csr_sec_grp into l_security_group_id;
60 --
61 if csr_sec_grp%notfound then
62 --
63 close csr_sec_grp;
64 --
65 -- The primary key is invalid therefore we must error
66 --
67 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
68 hr_multi_message.add
69 (p_associated_column1
70 => nvl(p_associated_column1,'COMPETENCE_ID')
71 );
72 --
73 else
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_competence_id in number
93 ,p_language in varchar2
94 )
95 Return Varchar2 Is
96 --
97 -- Declare cursor
98 -- amended on 23-Dec-2002 added join to per_compentence table
99 --
100 cursor csr_leg_code is
101 select pbg.legislation_code
102 from per_business_groups pbg
103 , per_competences_tl cpl
104 , per_competences cpn
105 where cpl.competence_id = p_competence_id
106 and cpl.language = p_language
107 and cpl.competence_id = cpn.competence_id
108 and pbg.business_group_id = cpn.competence_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 => 'competence_id'
124 ,p_argument_value => p_competence_id
125 );
126 --
127 --
128 if (( nvl(per_cpl_bus.g_competence_id, hr_api.g_number)
129 = p_competence_id)
130 and ( nvl(per_cpl_bus.g_language, hr_api.g_varchar2)
131 = p_language)) then
132 --
133 -- The legislation code has already been found with a previous
134 -- call to this function. Just return the value in the global
135 -- variable.
136 --
137 l_legislation_code := per_cpl_bus.g_legislation_code;
138 hr_utility.set_location(l_proc, 20);
139 else
140 --
141 -- The ID is different to the last call to this function
142 -- or this is the first call to this function.
143 --
144 open csr_leg_code;
145 fetch csr_leg_code into l_legislation_code;
149 -- The primary key is invalid therefore we must error
146 --
147 if csr_leg_code%notfound then
148 --
150 --
151 close csr_leg_code;
152 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
153 fnd_message.raise_error;
154 end if;
155 hr_utility.set_location(l_proc,30);
156 --
157 -- Set the global variables so the values are
158 -- available for the next call to this function.
159 --
160 close csr_leg_code;
161 per_cpl_bus.g_competence_id := p_competence_id;
162 per_cpl_bus.g_language := p_language;
163 per_cpl_bus.g_legislation_code := l_legislation_code;
164 end if;
165 hr_utility.set_location(' Leaving:'|| l_proc, 40);
166 return l_legislation_code;
167 end return_legislation_code;
168 --
169 -- ----------------------------------------------------------------------------
170 -- |-----------------------< chk_non_updateable_args >------------------------|
171 -- ----------------------------------------------------------------------------
172 -- {Start Of Comments}
173 --
174 -- Description:
175 -- This procedure is used to ensure that non updateable attributes have
176 -- not been updated. If an attribute has been updated an error is generated.
177 --
178 -- Pre Conditions:
179 -- g_old_rec has been populated with details of the values currently in
180 -- the database.
181 --
182 -- In Arguments:
183 -- p_rec has been populated with the updated values the user would like the
184 -- record set to.
185 --
186 -- Post Success:
187 -- Processing continues if all the non updateable attributes have not
188 -- changed.
189 --
190 -- Post Failure:
191 -- An application error is raised if any of the non updatable attributes
192 -- have been altered.
193 --
194 -- {End Of Comments}
195 -- ----------------------------------------------------------------------------
196 Procedure chk_non_updateable_args
197 (p_effective_date in date
198 ,p_rec in per_cpl_shd.g_rec_type
199 ) IS
200 --
201 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
202 --
203 Begin
204 --
205 -- Only proceed with the validation if a row exists for the current
206 -- record in the HR Schema.
207 --
208 IF NOT per_cpl_shd.api_updating
209 (p_competence_id => p_rec.competence_id
210 ,p_language => p_rec.language
211 ) THEN
212 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
213 fnd_message.set_token('PROCEDURE ', l_proc);
214 fnd_message.set_token('STEP ', '5');
215 fnd_message.raise_error;
216 END IF;
217 --
218 --
219 hr_utility.set_location(l_proc, 6);
220 --
221
222 --
223 End chk_non_updateable_args;
224 --
225 -------------------------------------------------------------------------------
226 -------------------------------< chk_alias >-----------------------------------
227 -------------------------------------------------------------------------------
228 --
229 -- Description:
230 --
231 -- - Validates that it is unique within the table as per the constraint
232 -- it replaces (PER_COMPETENCES_UK3)
233 --
234 -- Pre_conditions:
235 -- None
236 --
237 -- In Arguments:
238 -- p_alias
239 --
240 -- Post Success:
241 -- Process continues if :
242 -- All the in parameters are valid.
243 --
244 -- Post Failure:
245 -- An application error is raised and processing is terminated if any of
246 -- the following cases are found :
247 -- - name is not unique
248 --
249 -- Access Status
250 -- Internal Table Handler Use Only.
251 --
252 --
253 --
254 procedure chk_alias
255 (p_competence_id in per_competences_tl.competence_id%TYPE
256 ,p_language in per_competences_tl.language%TYPE
257 ,p_competence_alias in per_competences_tl.competence_alias%TYPE
258 )
259 is
260 --
261 l_exists varchar2(1);
262 l_api_updating boolean;
263 l_proc varchar2(72) := g_package||'chk_alias';
264 --
265 -- Cursor to check if competence_alias is unique
266 --
267 cursor csr_chk_alias is
268 select 'x'
269 from per_competences_tl pct
270 where (( p_competence_id is null)
271 or (p_competence_id <> pct.competence_id)
272 )
273 and language = p_language
274 and competence_alias = p_competence_alias;
275
276 --
277 begin
278 hr_utility.set_location('Entering:'|| l_proc, 1);
279 --
280 -- Check mandatory parameters have been set
281 --
282 -- Only proceed with validation if :
283 -- a) The current g_old_rec is current and
284 -- b) The value for name has changed
285 --
286 l_api_updating := per_cpl_shd.api_updating
287 (p_competence_id => p_competence_id
288 ,p_language => p_language);
289 --
290 if ( (l_api_updating and (per_cpl_shd.g_old_rec.competence_alias
291 <> nvl(p_competence_alias,hr_api.g_varchar2))
292 ) or
293 (NOT l_api_updating)
294 ) then
295 --
296 -- check if alias is unique
297 --
298 open csr_chk_alias;
299 fetch csr_chk_alias into l_exists;
300 if csr_chk_alias%found then
301 hr_utility.set_location(l_proc, 3);
302 -- alias is not unique
306 end if;
303 close csr_chk_alias;
304 fnd_message.set_name('PER','HR_52700_ALIAS_NOT_UNIQUE');
305 fnd_message.raise_error;
307 close csr_chk_alias;
308 end if;
309 hr_utility.set_location('Leaving:'|| l_proc, 10);
310 end chk_alias;
311 --
312 --
313 -- ----------------------------------------------------------------------------
314 -- |---------------------------< insert_validate >----------------------------|
315 -- ----------------------------------------------------------------------------
316 Procedure insert_validate
317 (p_effective_date in date
318 ,p_rec in per_cpl_shd.g_rec_type
319 ) is
320 --
321 l_proc varchar2(72) := g_package||'insert_validate';
322 --
323 Begin
324 hr_utility.set_location('Entering:'||l_proc, 5);
325 --
326 -- Call all supporting business operations
327 --
328 --
329 -- Name is a key flex field and will be handled by the definitions table
330 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
331
332 per_cpl_bus.chk_alias
333 ( p_competence_id => p_rec.competence_id
334 , p_language => p_rec.language
335 , p_competence_alias => p_rec.competence_alias
336 );
337
338 -- Validate Dependent Attributes
339 --
340 --
341 hr_utility.set_location(' Leaving:'||l_proc, 10);
342 End insert_validate;
343 --
344 -- ----------------------------------------------------------------------------
345 -- |---------------------------< update_validate >----------------------------|
346 -- ----------------------------------------------------------------------------
347 Procedure update_validate
348 (p_effective_date in date
349 ,p_rec in per_cpl_shd.g_rec_type
350 ) is
351 --
352 l_proc varchar2(72) := g_package||'update_validate';
353 --
354 Begin
355 hr_utility.set_location('Entering:'||l_proc, 5);
356 --
357 -- Call all supporting business operations
358
359 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
360
361 -- Validate Dependent Attributes
362 --
363 chk_non_updateable_args
364 (p_effective_date => p_effective_date
365 ,p_rec => p_rec
366 );
367 --
368 per_cpl_bus.chk_alias
369 ( p_competence_id => p_rec.competence_id
370 , p_language => p_rec.language
371 , p_competence_alias => p_rec.competence_alias
372 );
373 --
374 hr_utility.set_location(' Leaving:'||l_proc, 10);
375 End update_validate;
376 --
377 -- ----------------------------------------------------------------------------
378 -- |---------------------------< delete_validate >----------------------------|
379 -- ----------------------------------------------------------------------------
380 Procedure delete_validate
381 (p_rec in per_cpl_shd.g_rec_type
382 ) is
383 --
384 l_proc varchar2(72) := g_package||'delete_validate';
385 --
386 Begin
387 hr_utility.set_location('Entering:'||l_proc, 5);
388 --
389 -- Call all supporting business operations
390 --
391 hr_utility.set_location(' Leaving:'||l_proc, 10);
392 End delete_validate;
393 --
394 --
395 -- ----------------------------------------------------------------------------
396 -- |---------------------------< validate_translation >------------------------|
397 -- ----------------------------------------------------------------------------
398 -- {Start Of Comments}
399 --
400 -- Description:
401 -- This procedure performs the validation for the MLS widget.
402 --
403 -- Prerequisites:
404 -- This procedure is called from from the MLS widget.
405 --
406 -- In Parameters:
407 --
408 -- Post Success:
409 -- Processing continues.
410 --
411 -- Post Failure:
412 -- If a business rules fails the error will not be handled by this procedure
413 --
414 -- Developer Implementation Notes:
415 --
416 -- Access Status:
417 -- MLS Widget Only.
418 --
419 -- {End Of Comments}
420 -- ----------------------------------------------------------------------------
421 Procedure validate_translation
422 ( competence_id in NUMBER
423 , language in VARCHAR2
424 , competence_alias in VARCHAR2
425 , behavioural_indicator in VARCHAR2
426 , description in VARCHAR2
427 ) IS
428 begin
429 per_cpl_bus.chk_alias
430 ( p_competence_id => competence_id
431 , p_language => language
432 , p_competence_alias => competence_alias
433 );
434 end;
435 --
436 end per_cpl_bus;
[Home] [Help]