[Home] [Help]
PACKAGE BODY: APPS.OTA_ANC_BUS
Source
1 Package Body ota_anc_bus as
2 /* $Header: otancrhi.pkb 115.0 2003/12/01 05:59 arkashya noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_anc_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_announcement_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_announcement_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , ota_announcements anc
32 where anc.announcement_id = p_announcement_id
33 and pbg.business_group_id = anc.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'announcement_id'
50 ,p_argument_value => p_announcement_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'ANNOUNCEMENT_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
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_announcement_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , ota_announcements anc
102 where anc.announcement_id = p_announcement_id
103 and pbg.business_group_id = anc.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'announcement_id'
119 ,p_argument_value => p_announcement_id
120 );
121 --
122 if ( nvl(ota_anc_bus.g_announcement_id, hr_api.g_number)
123 = p_announcement_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := ota_anc_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 ota_anc_bus.g_announcement_id := p_announcement_id;
154 ota_anc_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- --------------------------< check_start_end_dates >------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- The start date must be less than, or equal to, the end date.
165 --
166 procedure check_start_end_dates
167 (
168 p_start_date in date
169 ,p_end_date in date
170 ) is
171 --
172 l_proc varchar2 (72) := g_package || 'check_start_end_dates';
173 --
174 --
175 begin
176 --
177 hr_utility.set_location ('entering:' || l_proc, 5);
178 --
179 if p_start_date is not null then
180 ota_general.check_start_end_dates(p_start_date,p_end_date);
181 end if;
182
183 --
184 hr_utility.set_location (' leaving:' || l_proc, 5);
185 --
186 Exception
187 WHEN app_exception.application_exception THEN
188
189 IF hr_multi_message.exception_add(
190 p_associated_column1 => 'OTA_ANNOUNCEMENTS.START_DATE_ACTIVE'
191 ,p_associated_column2 => 'OTA_ANNOUNCEMENTS.END_DATE_ACTIVE')
192 THEN
193
194 hr_utility.set_location(' Leaving:'||l_proc, 22);
195 RAISE;
196
197 END IF;
198 hr_utility.set_location(' Leaving:'||l_proc, 25);
199 end check_start_end_dates;
200 --
201 --
202 -- ----------------------------------------------------------------------------
203 -- |------------------------------< chk_df >----------------------------------|
204 -- ----------------------------------------------------------------------------
205 --
206 -- Description:
207 -- Validates all the Descriptive Flexfield values.
208 --
209 -- Prerequisites:
210 -- All other columns have been validated. Must be called as the
211 -- last step from insert_validate and update_validate.
212 --
213 -- In Arguments:
214 -- p_rec
215 --
216 -- Post Success:
217 -- If the Descriptive Flexfield structure column and data values are
218 -- all valid this procedure will end normally and processing will
219 -- continue.
220 --
221 -- Post Failure:
222 -- If the Descriptive Flexfield structure column value or any of
223 -- the data values are invalid then an application error is raised as
224 -- a PL/SQL exception.
225 --
226 -- Access Status:
227 -- Internal Row Handler Use Only.
228 --
229 -- ----------------------------------------------------------------------------
230 procedure chk_df
231 (p_rec in ota_anc_shd.g_rec_type
232 ) is
233 --
234 l_proc varchar2(72) := g_package || 'chk_df';
235 --
236 begin
237 hr_utility.set_location('Entering:'||l_proc,10);
238 --
239 if ((p_rec.announcement_id is not null) and (
240 nvl(ota_anc_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
241 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
242 nvl(ota_anc_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
243 nvl(p_rec.attribute1, hr_api.g_varchar2) or
244 nvl(ota_anc_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
245 nvl(p_rec.attribute2, hr_api.g_varchar2) or
246 nvl(ota_anc_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
247 nvl(p_rec.attribute3, hr_api.g_varchar2) or
248 nvl(ota_anc_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
249 nvl(p_rec.attribute4, hr_api.g_varchar2) or
250 nvl(ota_anc_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
251 nvl(p_rec.attribute5, hr_api.g_varchar2) or
252 nvl(ota_anc_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
253 nvl(p_rec.attribute6, hr_api.g_varchar2) or
254 nvl(ota_anc_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
255 nvl(p_rec.attribute7, hr_api.g_varchar2) or
256 nvl(ota_anc_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
257 nvl(p_rec.attribute8, hr_api.g_varchar2) or
258 nvl(ota_anc_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
259 nvl(p_rec.attribute9, hr_api.g_varchar2) or
260 nvl(ota_anc_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
261 nvl(p_rec.attribute10, hr_api.g_varchar2) or
262 nvl(ota_anc_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
263 nvl(p_rec.attribute11, hr_api.g_varchar2) or
264 nvl(ota_anc_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
265 nvl(p_rec.attribute12, hr_api.g_varchar2) or
266 nvl(ota_anc_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
267 nvl(p_rec.attribute13, hr_api.g_varchar2) or
268 nvl(ota_anc_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
269 nvl(p_rec.attribute14, hr_api.g_varchar2) or
270 nvl(ota_anc_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
271 nvl(p_rec.attribute15, hr_api.g_varchar2) or
272 nvl(ota_anc_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
273 nvl(p_rec.attribute16, hr_api.g_varchar2) or
274 nvl(ota_anc_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
275 nvl(p_rec.attribute17, hr_api.g_varchar2) or
276 nvl(ota_anc_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
277 nvl(p_rec.attribute18, hr_api.g_varchar2) or
278 nvl(ota_anc_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
279 nvl(p_rec.attribute19, hr_api.g_varchar2) or
280 nvl(ota_anc_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
281 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
282 or (p_rec.announcement_id is null) then
283 --
284 -- Only execute the validation if absolutely necessary:
285 -- a) During update, the structure column value or any
286 -- of the attribute values have actually changed.
287 -- b) During insert.
288 --
289 hr_dflex_utility.ins_or_upd_descflex_attribs
290 (p_appl_short_name => 'OTA'
291 ,p_descflex_name => 'OTA_ANNOUNCEMENTS'
292 ,p_attribute_category => p_rec.attribute_category
293 ,p_attribute1_name => 'ATTRIBUTE1'
294 ,p_attribute1_value => p_rec.attribute1
295 ,p_attribute2_name => 'ATTRIBUTE2'
296 ,p_attribute2_value => p_rec.attribute2
297 ,p_attribute3_name => 'ATTRIBUTE3'
298 ,p_attribute3_value => p_rec.attribute3
299 ,p_attribute4_name => 'ATTRIBUTE4'
300 ,p_attribute4_value => p_rec.attribute4
301 ,p_attribute5_name => 'ATTRIBUTE5'
302 ,p_attribute5_value => p_rec.attribute5
303 ,p_attribute6_name => 'ATTRIBUTE6'
304 ,p_attribute6_value => p_rec.attribute6
305 ,p_attribute7_name => 'ATTRIBUTE7'
306 ,p_attribute7_value => p_rec.attribute7
307 ,p_attribute8_name => 'ATTRIBUTE8'
308 ,p_attribute8_value => p_rec.attribute8
309 ,p_attribute9_name => 'ATTRIBUTE9'
310 ,p_attribute9_value => p_rec.attribute9
311 ,p_attribute10_name => 'ATTRIBUTE10'
312 ,p_attribute10_value => p_rec.attribute10
313 ,p_attribute11_name => 'ATTRIBUTE11'
314 ,p_attribute11_value => p_rec.attribute11
315 ,p_attribute12_name => 'ATTRIBUTE12'
316 ,p_attribute12_value => p_rec.attribute12
317 ,p_attribute13_name => 'ATTRIBUTE13'
318 ,p_attribute13_value => p_rec.attribute13
319 ,p_attribute14_name => 'ATTRIBUTE14'
320 ,p_attribute14_value => p_rec.attribute14
321 ,p_attribute15_name => 'ATTRIBUTE15'
322 ,p_attribute15_value => p_rec.attribute15
323 ,p_attribute16_name => 'ATTRIBUTE16'
324 ,p_attribute16_value => p_rec.attribute16
325 ,p_attribute17_name => 'ATTRIBUTE17'
326 ,p_attribute17_value => p_rec.attribute17
327 ,p_attribute18_name => 'ATTRIBUTE18'
328 ,p_attribute18_value => p_rec.attribute18
329 ,p_attribute19_name => 'ATTRIBUTE19'
330 ,p_attribute19_value => p_rec.attribute19
331 ,p_attribute20_name => 'ATTRIBUTE20'
332 ,p_attribute20_value => p_rec.attribute20
333 );
334 end if;
335 --
336 hr_utility.set_location(' Leaving:'||l_proc,20);
337 end chk_df;
338 --
339 -- ----------------------------------------------------------------------------
340 -- |-----------------------< chk_non_updateable_args >------------------------|
341 -- ----------------------------------------------------------------------------
342 -- {Start Of Comments}
343 --
344 -- Description:
345 -- This procedure is used to ensure that non updateable attributes have
346 -- not been updated. If an attribute has been updated an error is generated.
347 --
348 -- Pre Conditions:
349 -- g_old_rec has been populated with details of the values currently in
350 -- the database.
351 --
352 -- In Arguments:
353 -- p_rec has been populated with the updated values the user would like the
354 -- record set to.
355 --
356 -- Post Success:
357 -- Processing continues if all the non updateable attributes have not
358 -- changed.
359 --
360 -- Post Failure:
361 -- An application error is raised if any of the non updatable attributes
362 -- have been altered.
363 --
364 -- {End Of Comments}
365 -- ----------------------------------------------------------------------------
366 Procedure chk_non_updateable_args
367 (p_effective_date in date
368 ,p_rec in ota_anc_shd.g_rec_type
369 ) IS
370 --
371 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
372 --
373 Begin
374 --
375 -- Only proceed with the validation if a row exists for the current
376 -- record in the HR Schema.
377 --
378 IF NOT ota_anc_shd.api_updating
379 (p_announcement_id => p_rec.announcement_id
380 ,p_object_version_number => p_rec.object_version_number
381 ) THEN
382 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
383 fnd_message.set_token('PROCEDURE ', l_proc);
384 fnd_message.set_token('STEP ', '5');
385 fnd_message.raise_error;
386 END IF;
387 --
388 -- EDIT_HERE: Add checks to ensure non-updateable args have
389 -- not been updated.
390 --
391 End chk_non_updateable_args;
392 --
393 -- ----------------------------------------------------------------------------
394 -- |---------------------------< insert_validate >----------------------------|
395 -- ----------------------------------------------------------------------------
396 Procedure insert_validate
397 (p_effective_date in date
398 ,p_rec in ota_anc_shd.g_rec_type
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 hr_api.validate_bus_grp_id
409 (p_business_group_id => p_rec.business_group_id
410 ,p_associated_column1 => ota_anc_shd.g_tab_nam
414 -- if Multiple Message detection is enabled and at least
411 || '.BUSINESS_GROUP_ID');
412 --
413 -- After validating the set of important attributes,
415 -- one error has been found then abort further validation.
416 --
417 check_start_end_dates(p_rec.start_date_active,p_rec.end_date_active);
418 --
419 hr_multi_message.end_validation_set;
420 --
421 -- Validate Dependent Attributes
422 --
423 --
424 ota_anc_bus.chk_df(p_rec);
425 --
426 hr_utility.set_location(' Leaving:'||l_proc, 10);
427 End insert_validate;
428 --
429 -- ----------------------------------------------------------------------------
430 -- |---------------------------< update_validate >----------------------------|
431 -- ----------------------------------------------------------------------------
432 Procedure update_validate
433 (p_effective_date in date
434 ,p_rec in ota_anc_shd.g_rec_type
435 ) is
436 --
437 l_proc varchar2(72) := g_package||'update_validate';
438 --
439 Begin
440 hr_utility.set_location('Entering:'||l_proc, 5);
441 --
442 -- Call all supporting business operations
443 --
444 hr_api.validate_bus_grp_id
445 (p_business_group_id => p_rec.business_group_id
446 ,p_associated_column1 => ota_anc_shd.g_tab_nam
447 || '.BUSINESS_GROUP_ID');
448 --
449 -- After validating the set of important attributes,
450 -- if Multiple Message detection is enabled and at least
451 -- one error has been found then abort further validation.
452 --
453 check_start_end_dates(p_rec.start_date_active,p_rec.end_date_active);
454 --
455 hr_multi_message.end_validation_set;
456 --
457 -- Validate Dependent Attributes
458 --
459 chk_non_updateable_args
460 (p_effective_date => p_effective_date
461 ,p_rec => p_rec
462 );
463 --
464 --
465 ota_anc_bus.chk_df(p_rec);
466 --
467 hr_utility.set_location(' Leaving:'||l_proc, 10);
468 End update_validate;
469 --
470 -- ----------------------------------------------------------------------------
471 -- |---------------------------< delete_validate >----------------------------|
472 -- ----------------------------------------------------------------------------
473 Procedure delete_validate
474 (p_rec in ota_anc_shd.g_rec_type
475 ) is
476 --
477 l_proc varchar2(72) := g_package||'delete_validate';
478 --
479 Begin
480 hr_utility.set_location('Entering:'||l_proc, 5);
481 --
482 -- Call all supporting business operations
483 --
484 hr_utility.set_location(' Leaving:'||l_proc, 10);
485 End delete_validate;
486 --
487 end ota_anc_bus;