[Home] [Help]
PACKAGE BODY: APPS.OTA_AVT_BUS
Source
1 Package Body ota_avt_bus as
2 /* $Header: otavtrhi.pkb 120.0 2005/05/29 07:02:16 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_avt_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_activity_version_id number default null;
15 g_language varchar2(4) default null;
16 --
17 -- The following global vaiables are only to be used by the
18 -- validate_translation function.
19 --
20 g_activity_id number default null;
21 g_business_group_id number default null;
22
23 -- ---------------------------------------------------------------------------
24 -- |----------------------< set_security_group_id >--------------------------|
25 -- ---------------------------------------------------------------------------
26 --
27 Procedure set_security_group_id
28 (p_activity_version_id in number
29 ,p_associated_column1 in varchar2 default null
30 ) is
31 --
32 -- Declare cursor
33 --
34 cursor csr_sec_grp is
35 select pbg.security_group_id,
36 pbg.legislation_code
37 from per_business_groups_perf pbg
38 , ota_activity_versions avt
39 -- , ota_activity_definitions tad
40 where avt.activity_version_id = p_activity_version_id
41 -- and tad.activity_id = avt.activity_id
42 and pbg.business_group_id = avt.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 l_legislation_code varchar2(150);
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 => 'activity_version_id'
59 ,p_argument_value => p_activity_version_id
60 );
61 --
62 --
63 open csr_sec_grp;
64 fetch csr_sec_grp into l_security_group_id
65 , l_legislation_code;
66 --
67 if csr_sec_grp%notfound then
68 --
69 close csr_sec_grp;
70 --
71 -- The primary key is invalid therefore we must error
72 --
73 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
74 hr_multi_message.add
75 (p_associated_column1
76 => nvl(p_associated_column1,'ACTIVITY_VERSION_ID')
77 );
78 --
79 else
80 close csr_sec_grp;
81 --
82 -- Set the security_group_id in CLIENT_INFO
83 --
84 hr_api.set_security_group_id
85 (p_security_group_id => l_security_group_id
86 );
87 --
88 -- Set the sessions legislation context in HR_SESSION_DATA
89 --
90 hr_api.set_legislation_context(l_legislation_code);
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_activity_version_id in number
103 ,p_language in varchar2
104 )
105 Return Varchar2 Is
106 --
107 -- Declare cursor
108 --
109
110 cursor csr_leg_code is
111 select pbg.legislation_code
112 from per_business_groups_perf pbg
113 , ota_activity_versions tav
114 -- , ota_activity_definitions tad
115 where tav.activity_version_id = p_activity_version_id
116 -- and pbg.business_group_id = tad.business_group_id
117 and pbg.business_group_id = tav.business_group_id ;
118 -- and tad.activity_id = tav.activity_id
119 --
120 -- Declare local variables
121 --
122 l_legislation_code varchar2(150);
123 l_proc varchar2(72) := g_package||'return_legislation_code';
124 --
125 Begin
126 --
127 hr_utility.set_location('Entering:'|| l_proc, 10);
128 --
129 -- Ensure that all the mandatory parameter are not null
130 --
131 hr_api.mandatory_arg_error
132 (p_api_name => l_proc
133 ,p_argument => 'activity_version_id'
134 ,p_argument_value => p_activity_version_id
135 );
136 --
137 --
138 if (( nvl(ota_avt_bus.g_activity_version_id, hr_api.g_number)
139 = p_activity_version_id)
140 and ( nvl(ota_avt_bus.g_language, hr_api.g_varchar2)
141 = p_language)) then
142 --
143 -- The legislation code has already been found with a previous
144 -- call to this function. Just return the value in the global
145 -- variable.
146 --
147 l_legislation_code := ota_avt_bus.g_legislation_code;
148 hr_utility.set_location(l_proc, 20);
149 else
150 --
151 -- The ID is different to the last call to this function
152 -- or this is the first call to this function.
153 --
154 open csr_leg_code;
155 fetch csr_leg_code into l_legislation_code;
156 --
157 if csr_leg_code%notfound then
158 --
159 -- The primary key is invalid therefore we must error
160 --
161 close csr_leg_code;
162 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
163 fnd_message.raise_error;
164 end if;
165 hr_utility.set_location(l_proc,30);
166 --
167 -- Set the global variables so the values are
168 -- available for the next call to this function.
169 --
170 close csr_leg_code;
171 ota_avt_bus.g_activity_version_id := p_activity_version_id;
172 ota_avt_bus.g_language := p_language;
173 ota_avt_bus.g_legislation_code := l_legislation_code;
174 end if;
175 hr_utility.set_location(' Leaving:'|| l_proc, 40);
176 return l_legislation_code;
177 end return_legislation_code;
178 --
179 -- ----------------------------------------------------------------------------
180 -- |-----------------------< chk_non_updateable_args >------------------------|
181 -- ----------------------------------------------------------------------------
182 -- {Start Of Comments}
183 --
184 -- Description:
185 -- This procedure is used to ensure that non updateable attributes have
186 -- not been updated. If an attribute has been updated an error is generated.
187 --
188 -- Pre Conditions:
189 -- g_old_rec has been populated with details of the values currently in
190 -- the database.
191 --
192 -- In Arguments:
193 -- p_rec has been populated with the updated values the user would like the
194 -- record set to.
195 --
196 -- Post Success:
197 -- Processing continues if all the non updateable attributes have not
198 -- changed.
199 --
200 -- Post Failure:
201 -- An application error is raised if any of the non updatable attributes
202 -- have been altered.
203 --
204 -- {End Of Comments}
205 -- ----------------------------------------------------------------------------
206 Procedure chk_non_updateable_args
207 (p_effective_date in date
208 ,p_rec in ota_avt_shd.g_rec_type
209 ) IS
210 --
211 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
212 --
213 Begin
214 --
215 -- Only proceed with the validation if a row exists for the current
216 -- record in the HR Schema.
217 --
218 IF NOT ota_avt_shd.api_updating
219 (p_activity_version_id => p_rec.activity_version_id
220 ,p_language => p_rec.language
221 ) THEN
222 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
223 fnd_message.set_token('PROCEDURE ', l_proc);
224 fnd_message.set_token('STEP ', '5');
225 fnd_message.raise_error;
226 END IF;
227 --
228 --
229 End chk_non_updateable_args;
230 --
231 -- ----------------------------------------------------------------------------
232 -- |---------------------------< insert_validate >----------------------------|
233 -- ----------------------------------------------------------------------------
234 Procedure insert_validate
235 (p_effective_date in date
236 ,p_rec in ota_avt_shd.g_rec_type
237 ,p_activity_version_id in number
238 ) is
239 --
240 l_proc varchar2(72) := g_package||'insert_validate';
241 --
242 Begin
243 hr_utility.set_location('Entering:'||l_proc, 5);
244 --
245 -- Call all supporting business operations
246 --
247 --
248 ota_avt_bus.set_security_group_id(p_activity_version_id) ;
249 --
250 -- Validate Dependent Attributes
251 /*
252 check_unique_version_name(p_activity_version_id => p_activity_version_id
253 ,p_version_name => p_rec.version_name
254 ,p_language => p_rec.language ) ;
255 */
256 --
257 --
258 hr_utility.set_location(' Leaving:'||l_proc, 10);
259 End insert_validate;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------< update_validate >----------------------------|
263 -- ----------------------------------------------------------------------------
264 Procedure update_validate
265 (p_effective_date in date
266 ,p_rec in ota_avt_shd.g_rec_type
267 ) is
268 --
269 l_proc varchar2(72) := g_package||'update_validate';
270 --
271 Begin
272 hr_utility.set_location('Entering:'||l_proc, 5);
273 --
274 -- Call all supporting business operations
275 --
276 --
277 ota_avt_bus.set_security_group_id(p_rec.activity_version_id) ;
278 --
279 -- Validate Dependent Attributes
280 /*
281 check_unique_version_name(p_activity_version_id => p_rec.activity_version_id
282 ,p_version_name => p_rec.version_name
283 ,p_language => p_rec.language );
284 */
285 --
286 chk_non_updateable_args
287 (p_effective_date => p_effective_date
288 ,p_rec => p_rec
289 );
290 --
291 --
292 hr_utility.set_location(' Leaving:'||l_proc, 10);
293 End update_validate;
294 --
295 -- ----------------------------------------------------------------------------
296 -- |---------------------------< delete_validate >----------------------------|
297 -- ----------------------------------------------------------------------------
298 Procedure delete_validate
299 (p_rec in ota_avt_shd.g_rec_type
300 ) is
301 --
302 l_proc varchar2(72) := g_package||'delete_validate';
303 --
304 Begin
305 hr_utility.set_location('Entering:'||l_proc, 5);
306 --
307 -- Call all supporting business operations
308 --
309 hr_utility.set_location(' Leaving:'||l_proc, 10);
310 End delete_validate;
311 --
312 -- ----------------------------------------------------------------------------
313 -- |-----------------------< set_translation_globals >------------------------|
314 -- ----------------------------------------------------------------------------
315 PROCEDURE set_translation_globals
316 (p_business_group_id in number
317 ,p_activity_id in number
318 ) IS
319 --
320 l_proc varchar2(72) := g_package||'set_translation_globals';
321 --
322 BEGIN
323 --
324 hr_utility.set_location('Entering:'||l_proc,5);
325 --
326 g_business_group_id := p_business_group_id;
327 g_activity_id := p_activity_id ;
328
329 --
330 hr_utility.set_location('Leaving:'||l_proc,10);
331 --
332 END set_translation_globals;
333 --
334 -- ----------------------------------------------------------------------------
335 -- |---------------------------< validate_translation >------------------------|
336 -- ----------------------------------------------------------------------------
337 -- {Start Of Comments}
338 --
339 -- Description:
340 -- This procedure performs the validation for the MLS widget.
341 --
342 -- Prerequisites:
343 -- This procedure is called from from the MLS widget.
344 --
345 -- In Parameters:
346 --
347 -- Post Success:
348 -- Processing continues.
349 --
350 -- Post Failure:
351 -- If a business rules fails the error will not be handled by this procedure
352 --
353 -- Developer Implementation Notes:
354 --
355 -- Access Status:
356 -- MLS Widget Only.
357 --
358 -- {End Of Comments}
359 -- ----------------------------------------------------------------------------
360 Procedure validate_translation
361 (p_activity_version_id in number
362 ,p_language in varchar2
363 ,p_version_name in varchar2
364 ,p_description in varchar2
365 ,p_intended_audience in varchar2
366 ,p_objectives in varchar2
367 ,p_keywords in varchar2
368 ,p_business_group_id in number default null
369 ,p_activity_id in number default null
370 ) IS
371 --
372 l_proc varchar2(72) := g_package||'validate_translation';
373
374 --
375 BEGIN
376 --
377 hr_utility.set_location('Entering:'||l_proc,5);
378 --
379 /*
380 check_unique_version_name
381 ( p_activity_version_id => p_activity_version_id
382 , p_activity_id => Nvl(p_activity_id,g_activity_id)
383 , p_business_group_id => Nvl(p_business_group_id,g_business_group_id)
384 , p_language => p_language
385 , p_version_name => p_version_name
386 );
387 */
388 --
389 hr_utility.set_location('Leaving:'||l_proc,10);
390 --
391 END Validate_translation;
392 --
393 --
394 --
395 -- ----------------------------------------------------------------------------
396 -- |--------------------------< check_unique_version_name >---------------------------|
397 -- ----------------------------------------------------------------------------
398 --
399 -- PUBLIC
400 -- Description:
401 -- Validates the unique key.
402 --
403 Procedure check_unique_version_name
404 (
405 p_business_group_id in number default null
406 ,p_activity_id in number default null
407 ,p_version_name in varchar2
408 ,p_activity_version_id in number
409 ,p_language in varchar2
410 ) is
411 --
412 v_exists varchar2(1);
413 v_proc varchar2(72) := g_package||'check_unique_name';
414 --
415 v_business_group_id number;
416 v_activity_id number;
417 --
418 cursor get_activity is
419 select tad.business_group_id, tad.activity_id
420 from ota_activity_definitions tad
421 ,ota_activity_versions tav
422 where tav.activity_version_id = p_activity_version_id
423 and tav.activity_id = tad.activity_id ;
424 --
425 cursor sel_unique_key is
426 select 'Y'
427 from ota_activity_versions tav
428 , ota_activity_versions_tl avt
429 where avt.version_name = p_version_name
430 and avt.activity_version_id = tav.activity_version_id
431 and avt.language = p_language
432 and tav.activity_id = v_activity_id
433 and (p_activity_version_id is null or
434 (p_activity_version_id is not null and
435 avt.activity_version_id <> p_activity_version_id)) ;
436
437 --
438 Begin
439 --
440 hr_utility.set_location('Entering:'|| v_proc, 5);
441 --
442 if p_business_group_id is null or p_activity_id is null then
443 open get_activity;
444 fetch get_activity into v_business_group_id, v_activity_id ;
445 close get_activity;
446 else
447 v_business_group_id := p_business_group_id;
448 end if;
449 --
450 Open sel_unique_key;
451 fetch sel_unique_key into v_exists;
452 --
453 if sel_unique_key%found then
454 --
455 close sel_unique_key;
456 --
457 -- ** TEMP ** Add error message with the following text.
458 --
459 --call_error_message( p_error_appl => 'OTA'
460 -- , p_error_txt => 'OTA_13301_TAV_DUPLICATE'
461 -- );
462 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
463 fnd_message.raise_error;
464 --
465 end if;
466 --
467 close sel_unique_key;
468 --
469 hr_utility.set_location(' Leaving:'|| v_proc, 10);
470 --
471 End check_unique_version_name;
472 --
473 --
474 end ota_avt_bus;