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