[Home] [Help]
PACKAGE BODY: APPS.OTA_CPE_BUS
Source
1 Package Body ota_cpe_bus as
2 /* $Header: otcperhi.pkb 120.3 2005/12/01 15:24 cmora noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_cpe_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_prd_enrollment_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cert_prd_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_prd_enrollments cpe
32 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
33 and pbg.business_group_id = cpe.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_prd_enrollment_id'
50 ,p_argument_value => p_cert_prd_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_PRD_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_prd_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_prd_enrollments cpe
102 where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
103 and pbg.business_group_id = cpe.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_prd_enrollment_id'
119 ,p_argument_value => p_cert_prd_enrollment_id
120 );
121 --
122 if ( nvl(ota_cpe_bus.g_cert_prd_enrollment_id, hr_api.g_number)
123 = p_cert_prd_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_cpe_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_cpe_bus.g_cert_prd_enrollment_id := p_cert_prd_enrollment_id;
154 ota_cpe_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_cpe_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_prd_enrollment_id is not null) and (
198 nvl(ota_cpe_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(ota_cpe_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(ota_cpe_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(ota_cpe_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(ota_cpe_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(ota_cpe_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(ota_cpe_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(ota_cpe_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(ota_cpe_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(ota_cpe_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(ota_cpe_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(ota_cpe_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(ota_cpe_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(ota_cpe_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(ota_cpe_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(ota_cpe_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(ota_cpe_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(ota_cpe_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(ota_cpe_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(ota_cpe_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(ota_cpe_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
240 or (p_rec.cert_prd_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_PRD_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_cpe_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_cpe_shd.api_updating
337 (p_cert_prd_enrollment_id => p_rec.cert_prd_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 -- ----------------------------------------------------------------------------
353 -- |----------------------<chk_period_status_code >-----------------------|
354 -- ----------------------------------------------------------------------------
355 --
356 PROCEDURE chk_period_status_code
357 (p_effective_date IN date
358 ,p_period_status_code IN ota_cert_prd_enrollments.period_status_code%TYPE
359 ) IS
360 --
361 l_proc varchar2(72) := g_package|| 'chk_period_status_code';
362 --
363 BEGIN
364 --
365 -- check mandatory parameters have been set
366 --
367 hr_utility.set_location(' Step:'|| l_proc, 10);
368 hr_api.mandatory_arg_error
369 (p_api_name => l_proc
370 ,p_argument => 'p_period_status_code'
371 ,p_argument_value => p_period_status_code
372 );
373 --
374 hr_utility.set_location(' Step:'|| l_proc, 20);
375 IF hr_api.not_exists_in_hr_lookups
376 (p_effective_date => p_effective_date
377 ,p_lookup_type => 'OTA_CERT_PRD_ENROLL_STATUS'
378 ,p_lookup_code => p_period_status_code
379 ) THEN
380 -- Error, lookup not available
381 fnd_message.set_name('OTA', 'OTA_443667_CPE_STAT_INVALID');
382 fnd_message.raise_error;
383 END IF;
384 --
385 hr_utility.set_location(' Leaving:'||l_proc, 30);
386
390 IF hr_multi_message.exception_add(
387 --MULTI MESSAGE SUPPORT
388 EXCEPTION
389 WHEN app_exception.application_exception THEN
391 p_associated_column1 => 'OTA_CERT_PRD_ENROLLMENTS.period_status_code') THEN
392 hr_utility.set_location(' Leaving:'||l_proc, 32);
393 RAISE;
394
395 END IF;
396 hr_utility.set_location(' Leaving:'||l_proc, 35);
397
398 END chk_period_status_code;
399 --
400 -- ----------------------------------------------------------------------------
401 -- |----------------------<check_active_cert_members >-------------------------|
402 -- ----------------------------------------------------------------------------
403 --
404 PROCEDURE check_active_cert_members
405 ( p_cert_enrollment_id IN ota_cert_enrollments.cert_enrollment_id%TYPE)
406 IS
407 --
408 l_proc varchar2(72) := g_package|| 'check_active_cert_members';
409
410 CURSOR csr_cert_mbr IS
411 select
412 cmb.CERTIFICATION_MEMBER_ID
413 from ota_certification_members cmb,
414 ota_certifications_b crt,
415 ota_cert_enrollments cre
416 where cre.cert_enrollment_id = p_cert_enrollment_id
417 and cre.certification_id = crt.certification_id
418 and crt.certification_id = cmb.certification_id
419 and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE) and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'));
420
421 l_CERTIFICATION_MEMBER_ID ota_certification_members.CERTIFICATION_MEMBER_ID%type;
422 --
423 BEGIN
424 --
425 -- check mandatory parameters have been set
426 --
427 hr_utility.set_location(' Step:'|| l_proc, 10);
428 hr_api.mandatory_arg_error
429 (p_api_name => l_proc
430 ,p_argument => 'p_cert_enrollment_id'
431 ,p_argument_value => p_cert_enrollment_id
432 );
433 -- check for atleast one active cert member
434 --
435 OPEN csr_cert_mbr;
436 FETCH csr_cert_mbr INTO l_CERTIFICATION_MEMBER_ID;
437 close csr_cert_mbr;
438
439 if l_CERTIFICATION_MEMBER_ID is null then
440 hr_utility.set_location(' Step:'|| l_proc, 20);
441 fnd_message.set_name('OTA', 'OTA_443926_CME_NO_CURR_CRT_MBR');
442 fnd_message.raise_error;
443 END IF;
444
445 hr_utility.set_location(' Leaving:'||l_proc, 20);
446
447 END check_active_cert_members;
448
449 -- ----------------------------------------------------------------------------
450 -- |---------------------------< insert_validate >----------------------------|
451 -- ----------------------------------------------------------------------------
452 Procedure insert_validate
453 (p_effective_date in date
454 ,p_rec in ota_cpe_shd.g_rec_type
455 ) is
456 --
457 l_proc varchar2(72) := g_package||'insert_validate';
458 --
459 Begin
460 hr_utility.set_location('Entering:'||l_proc, 5);
461 --
462 -- Call all supporting business operations
463 --
464 -- Validate Important Attributes
465 hr_api.validate_bus_grp_id
466 (p_business_group_id => p_rec.business_group_id
467 ,p_associated_column1 => ota_cpe_shd.g_tab_nam
468 || '.BUSINESS_GROUP_ID');
469 --
470 -- After validating the set of important attributes,
471 -- if Multiple Message detection is enabled and at least
472 -- one error has been found then abort further validation.
473 --
474 hr_multi_message.end_validation_set;
475 --
476 -- Validate Dependent Attributes
477 --
478 chk_period_status_code(p_effective_date => p_effective_date,
479 p_period_status_code => p_rec.period_status_code);
480 --
481 --check for atleast one active cert member
482 check_active_cert_members(p_rec.cert_enrollment_id);
483 --
484 ota_cpe_bus.chk_df(p_rec);
485 --
486 hr_utility.set_location(' Leaving:'||l_proc, 10);
487 End insert_validate;
488 --
489 -- ----------------------------------------------------------------------------
490 -- |---------------------------< update_validate >----------------------------|
491 -- ----------------------------------------------------------------------------
492 Procedure update_validate
493 (p_effective_date in date
494 ,p_rec in ota_cpe_shd.g_rec_type
495 ) is
496 --
497 l_proc varchar2(72) := g_package||'update_validate';
498 --
499 Begin
500 hr_utility.set_location('Entering:'||l_proc, 5);
501 --
502 -- Call all supporting business operations
503 --
504 -- Validate Important Attributes
505 hr_api.validate_bus_grp_id
506 (p_business_group_id => p_rec.business_group_id
507 ,p_associated_column1 => ota_cpe_shd.g_tab_nam
508 || '.BUSINESS_GROUP_ID');
509 --
510 -- After validating the set of important attributes,
511 -- if Multiple Message detection is enabled and at least
512 -- one error has been found then abort further validation.
513 --
514 hr_multi_message.end_validation_set;
515 --
516 -- Validate Dependent Attributes
517 --
518 chk_non_updateable_args
519 (p_effective_date => p_effective_date
520 ,p_rec => p_rec
521 );
522 --
523 chk_period_status_code(p_effective_date => p_effective_date,
524 p_period_status_code => p_rec.period_status_code);
525 --
526 ota_cpe_bus.chk_df(p_rec);
527 --
528 hr_utility.set_location(' Leaving:'||l_proc, 10);
529 End update_validate;
530 --
531 -- ----------------------------------------------------------------------------
532 -- |---------------------------< delete_validate >----------------------------|
533 -- ----------------------------------------------------------------------------
534 Procedure delete_validate
535 (p_rec in ota_cpe_shd.g_rec_type
536 ) is
537 --
538 l_proc varchar2(72) := g_package||'delete_validate';
539 --
540 Begin
541 hr_utility.set_location('Entering:'||l_proc, 5);
542 --
543 -- Call all supporting business operations
544 --
545 hr_utility.set_location(' Leaving:'||l_proc, 10);
546 End delete_validate;
547 --
548 end ota_cpe_bus;