[Home] [Help]
PACKAGE BODY: APPS.OTA_CME_BUS
Source
1 Package Body ota_cme_bus as
2 /* $Header: otcmerhi.pkb 120.1 2005/12/01 15:24 cmora noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_cme_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_cert_mbr_enrollment_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cert_mbr_enrollment_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_cert_mbr_enrollments cme
32 where cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
33 and pbg.business_group_id = cme.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 => 'cert_mbr_enrollment_id'
50 ,p_argument_value => p_cert_mbr_enrollment_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,'CERT_MBR_ENROLLMENT_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_cert_mbr_enrollment_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_cert_mbr_enrollments cme
102 where cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
103 and pbg.business_group_id = cme.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 => 'cert_mbr_enrollment_id'
119 ,p_argument_value => p_cert_mbr_enrollment_id
120 );
121 --
122 if ( nvl(ota_cme_bus.g_cert_mbr_enrollment_id, hr_api.g_number)
123 = p_cert_mbr_enrollment_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_cme_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_cme_bus.g_cert_mbr_enrollment_id := p_cert_mbr_enrollment_id;
154 ota_cme_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 -- |------------------------------< chk_df >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Descriptive Flexfield structure column and data values are
176 -- all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Descriptive Flexfield structure column value or any of
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_df
189 (p_rec in ota_cme_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_df';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.cert_mbr_enrollment_id is not null) and (
198 nvl(ota_cme_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(ota_cme_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(ota_cme_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(ota_cme_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(ota_cme_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(ota_cme_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(ota_cme_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(ota_cme_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(ota_cme_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(ota_cme_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(ota_cme_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(ota_cme_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(ota_cme_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(ota_cme_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(ota_cme_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(ota_cme_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(ota_cme_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(ota_cme_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(ota_cme_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(ota_cme_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(ota_cme_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
240 or (p_rec.cert_mbr_enrollment_id is null) then
241 --
242 -- Only execute the validation if absolutely necessary:
243 -- a) During update, the structure column value or any
244 -- of the attribute values have actually changed.
245 -- b) During insert.
246 --
247 hr_dflex_utility.ins_or_upd_descflex_attribs
248 (p_appl_short_name => 'OTA'
249 ,p_descflex_name => 'OTA_CERT_MBR_ENROLLMENTS'
250 ,p_attribute_category => p_rec.attribute_category
251 ,p_attribute1_name => 'ATTRIBUTE1'
252 ,p_attribute1_value => p_rec.attribute1
253 ,p_attribute2_name => 'ATTRIBUTE2'
254 ,p_attribute2_value => p_rec.attribute2
255 ,p_attribute3_name => 'ATTRIBUTE3'
256 ,p_attribute3_value => p_rec.attribute3
257 ,p_attribute4_name => 'ATTRIBUTE4'
258 ,p_attribute4_value => p_rec.attribute4
259 ,p_attribute5_name => 'ATTRIBUTE5'
260 ,p_attribute5_value => p_rec.attribute5
261 ,p_attribute6_name => 'ATTRIBUTE6'
262 ,p_attribute6_value => p_rec.attribute6
263 ,p_attribute7_name => 'ATTRIBUTE7'
264 ,p_attribute7_value => p_rec.attribute7
265 ,p_attribute8_name => 'ATTRIBUTE8'
266 ,p_attribute8_value => p_rec.attribute8
267 ,p_attribute9_name => 'ATTRIBUTE9'
268 ,p_attribute9_value => p_rec.attribute9
269 ,p_attribute10_name => 'ATTRIBUTE10'
270 ,p_attribute10_value => p_rec.attribute10
271 ,p_attribute11_name => 'ATTRIBUTE11'
272 ,p_attribute11_value => p_rec.attribute11
273 ,p_attribute12_name => 'ATTRIBUTE12'
274 ,p_attribute12_value => p_rec.attribute12
275 ,p_attribute13_name => 'ATTRIBUTE13'
276 ,p_attribute13_value => p_rec.attribute13
277 ,p_attribute14_name => 'ATTRIBUTE14'
278 ,p_attribute14_value => p_rec.attribute14
279 ,p_attribute15_name => 'ATTRIBUTE15'
280 ,p_attribute15_value => p_rec.attribute15
281 ,p_attribute16_name => 'ATTRIBUTE16'
282 ,p_attribute16_value => p_rec.attribute16
283 ,p_attribute17_name => 'ATTRIBUTE17'
284 ,p_attribute17_value => p_rec.attribute17
285 ,p_attribute18_name => 'ATTRIBUTE18'
286 ,p_attribute18_value => p_rec.attribute18
287 ,p_attribute19_name => 'ATTRIBUTE19'
288 ,p_attribute19_value => p_rec.attribute19
289 ,p_attribute20_name => 'ATTRIBUTE20'
290 ,p_attribute20_value => p_rec.attribute20
291 );
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc,20);
295 end chk_df;
296 --
297 -- ----------------------------------------------------------------------------
298 -- |-----------------------< chk_non_updateable_args >------------------------|
299 -- ----------------------------------------------------------------------------
300 -- {Start Of Comments}
301 --
302 -- Description:
303 -- This procedure is used to ensure that non updateable attributes have
304 -- not been updated. If an attribute has been updated an error is generated.
305 --
306 -- Pre Conditions:
307 -- g_old_rec has been populated with details of the values currently in
308 -- the database.
309 --
310 -- In Arguments:
311 -- p_rec has been populated with the updated values the user would like the
312 -- record set to.
313 --
314 -- Post Success:
315 -- Processing continues if all the non updateable attributes have not
316 -- changed.
317 --
318 -- Post Failure:
319 -- An application error is raised if any of the non updatable attributes
320 -- have been altered.
321 --
322 -- {End Of Comments}
323 -- ----------------------------------------------------------------------------
324 Procedure chk_non_updateable_args
325 (p_effective_date in date
326 ,p_rec in ota_cme_shd.g_rec_type
327 ) IS
328 --
329 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
330 --
331 Begin
332 --
333 -- Only proceed with the validation if a row exists for the current
334 -- record in the HR Schema.
335 --
336 IF NOT ota_cme_shd.api_updating
337 (p_cert_mbr_enrollment_id => p_rec.cert_mbr_enrollment_id
338 ,p_object_version_number => p_rec.object_version_number
339 ) THEN
340 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
341 fnd_message.set_token('PROCEDURE ', l_proc);
342 fnd_message.set_token('STEP ', '5');
343 fnd_message.raise_error;
344 END IF;
345 --
346 -- Add checks to ensure non-updateable args have
347 -- not been updated.
348 --
349 End chk_non_updateable_args;
350 --
351 -- ----------------------------------------------------------------------------
352 -- |---------------------------< chk_member_status_code>------------------------|
353 -- ----------------------------------------------------------------------------
354 PROCEDURE chk_member_status_code(p_cert_mbr_enrollment_id IN number
355 ,p_object_version_number IN NUMBER
356 ,p_member_status_code IN VARCHAR2
357 ,p_effective_date IN date) IS
358
359 --
360 l_proc VARCHAR2(72) := g_package||'chk_member_status_code';
361 l_api_updating boolean;
362
363 BEGIN
364 hr_utility.set_location(' Leaving:'||l_proc, 10);
365 --
366 -- check mandatory parameters has been set
367 --
368 hr_api.mandatory_arg_error
369 (p_api_name => l_proc
370 ,p_argument => 'effective_date'
371 ,p_argument_value => p_effective_date);
372
373 l_api_updating := ota_cme_shd.api_updating
374 (p_cert_mbr_enrollment_id => p_cert_mbr_enrollment_id
375 ,p_object_version_number => p_object_version_number);
376
377
378 IF ((l_api_updating AND
379 NVL(ota_cme_shd.g_old_rec.member_status_code,hr_api.g_varchar2) <>
380 NVL(p_member_status_code, hr_api.g_varchar2))
381 OR NOT l_api_updating AND p_member_status_code IS NOT NULL) THEN
382
383 hr_utility.set_location(' Leaving:'||l_proc, 20);
384 --
385
386 IF p_member_status_code IS NOT NULL THEN
387 IF hr_api.not_exists_in_hr_lookups
388 (p_effective_date => p_effective_date
389 ,p_lookup_type => 'OTA_CERT_MBR_ENROLL_STATUS'
390 ,p_lookup_code => p_member_status_code) THEN
391 fnd_message.set_name('OTA','OTA_443668_CME_STAT_INVALID');
392 fnd_message.raise_error;
393 END IF;
394 hr_utility.set_location(' Leaving:'||l_proc, 30);
395
396 END IF;
397
398 END IF;
399 hr_utility.set_location(' Leaving:'||l_proc, 40);
400
401 EXCEPTION
402
403 WHEN app_exception.application_exception THEN
404
405 IF hr_multi_message.exception_add
406 (p_associated_column1 => 'OTA_CERT_MBR_ENROLLMENTS.MEMBER_STATUS_CODE') THEN
407
408 hr_utility.set_location(' Leaving:'||l_proc, 42);
409 RAISE;
410 END IF;
411
412 hr_utility.set_location(' Leaving:'||l_proc, 44);
413
414 END chk_member_status_code;
415 --
416 -- ----------------------------------------------------------------------------
417 -- |---------------------------< insert_validate >----------------------------|
418 -- ----------------------------------------------------------------------------
419 Procedure insert_validate
420 (p_effective_date in date
421 ,p_rec in ota_cme_shd.g_rec_type
422 ) is
423 --
424 l_proc varchar2(72) := g_package||'insert_validate';
425 --
426 Begin
427 hr_utility.set_location('Entering:'||l_proc, 5);
428 --
429 -- Call all supporting business operations
430 --
431 -- Validate Important Attributes
432 hr_api.validate_bus_grp_id
433 (p_business_group_id => p_rec.business_group_id
434 ,p_associated_column1 => ota_cme_shd.g_tab_nam
435 || '.BUSINESS_GROUP_ID');
436 --
437 -- After validating the set of important attributes,
438 -- if Multiple Message detection is enabled and at least
439 -- one error has been found then abort further validation.
440 --
441 hr_multi_message.end_validation_set;
442 --
443 -- Validate Dependent Attributes
444 --
445 --
446 ota_cme_bus.chk_df(p_rec);
447 --
448 hr_utility.set_location(' Leaving:'||l_proc, 10);
449 End insert_validate;
450 --
451 -- ----------------------------------------------------------------------------
452 -- |---------------------------< update_validate >----------------------------|
453 -- ----------------------------------------------------------------------------
454 Procedure update_validate
455 (p_effective_date in date
456 ,p_rec in ota_cme_shd.g_rec_type
457 ) is
458 --
459 l_proc varchar2(72) := g_package||'update_validate';
460 --
461 Begin
462 hr_utility.set_location('Entering:'||l_proc, 5);
463 --
464 -- Call all supporting business operations
465 --
466 -- Validate Important Attributes
467 hr_api.validate_bus_grp_id
468 (p_business_group_id => p_rec.business_group_id
469 ,p_associated_column1 => ota_cme_shd.g_tab_nam
470 || '.BUSINESS_GROUP_ID');
471 --
472 -- After validating the set of important attributes,
473 -- if Multiple Message detection is enabled and at least
474 -- one error has been found then abort further validation.
475 --
476 hr_multi_message.end_validation_set;
477 --
478 -- Validate Dependent Attributes
479 --
480 chk_non_updateable_args
481 (p_effective_date => p_effective_date
482 ,p_rec => p_rec
483 );
484 --
485 --
486 ota_cme_bus.chk_df(p_rec);
487 --
488 hr_utility.set_location(' Leaving:'||l_proc, 10);
489 End update_validate;
490 --
491 -- ----------------------------------------------------------------------------
492 -- |---------------------------< delete_validate >----------------------------|
493 -- ----------------------------------------------------------------------------
494 Procedure delete_validate
495 (p_rec in ota_cme_shd.g_rec_type
496 ) is
497 --
498 l_proc varchar2(72) := g_package||'delete_validate';
499 --
500 Begin
501 hr_utility.set_location('Entering:'||l_proc, 5);
502 --
503 -- Call all supporting business operations
504 --
505 hr_utility.set_location(' Leaving:'||l_proc, 10);
506 End delete_validate;
507 --
508 end ota_cme_bus;