DBA Data[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;