[Home] [Help]
PACKAGE BODY: APPS.BEN_CWG_BUS
Source
1 Package Body ben_cwg_bus as
2 /* $Header: becwgrhi.pkb 120.0 2005/05/28 01:29:54 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cwg_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_cwb_wksht_grp_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cwb_wksht_grp_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 , ben_cwb_wksht_grp cwg
32 where cwg.cwb_wksht_grp_id = p_cwb_wksht_grp_id
33 and pbg.business_group_id = cwg.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 => 'cwb_wksht_grp_id'
50 ,p_argument_value => p_cwb_wksht_grp_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,'CWB_WKSHT_GRP_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_cwb_wksht_grp_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 , ben_cwb_wksht_grp cwg
102 where cwg.cwb_wksht_grp_id = p_cwb_wksht_grp_id
103 and pbg.business_group_id = cwg.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 => 'cwb_wksht_grp_id'
119 ,p_argument_value => p_cwb_wksht_grp_id
120 );
121 --
122 if ( nvl(ben_cwg_bus.g_cwb_wksht_grp_id, hr_api.g_number)
123 = p_cwb_wksht_grp_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 := ben_cwg_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 ben_cwg_bus.g_cwb_wksht_grp_id := p_cwb_wksht_grp_id;
154 ben_cwg_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 ben_cwg_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.cwb_wksht_grp_id is not null) and (
198 nvl(ben_cwg_shd.g_old_rec.cwg_attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.cwg_attribute_category, hr_api.g_varchar2) or
200 nvl(ben_cwg_shd.g_old_rec.cwg_attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.cwg_attribute1, hr_api.g_varchar2) or
202 nvl(ben_cwg_shd.g_old_rec.cwg_attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.cwg_attribute2, hr_api.g_varchar2) or
204 nvl(ben_cwg_shd.g_old_rec.cwg_attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.cwg_attribute3, hr_api.g_varchar2) or
206 nvl(ben_cwg_shd.g_old_rec.cwg_attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.cwg_attribute4, hr_api.g_varchar2) or
208 nvl(ben_cwg_shd.g_old_rec.cwg_attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.cwg_attribute5, hr_api.g_varchar2) or
210 nvl(ben_cwg_shd.g_old_rec.cwg_attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.cwg_attribute6, hr_api.g_varchar2) or
212 nvl(ben_cwg_shd.g_old_rec.cwg_attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.cwg_attribute7, hr_api.g_varchar2) or
214 nvl(ben_cwg_shd.g_old_rec.cwg_attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.cwg_attribute8, hr_api.g_varchar2) or
216 nvl(ben_cwg_shd.g_old_rec.cwg_attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.cwg_attribute9, hr_api.g_varchar2) or
218 nvl(ben_cwg_shd.g_old_rec.cwg_attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.cwg_attribute10, hr_api.g_varchar2) or
220 nvl(ben_cwg_shd.g_old_rec.cwg_attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.cwg_attribute11, hr_api.g_varchar2) or
222 nvl(ben_cwg_shd.g_old_rec.cwg_attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.cwg_attribute12, hr_api.g_varchar2) or
224 nvl(ben_cwg_shd.g_old_rec.cwg_attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.cwg_attribute13, hr_api.g_varchar2) or
226 nvl(ben_cwg_shd.g_old_rec.cwg_attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.cwg_attribute14, hr_api.g_varchar2) or
228 nvl(ben_cwg_shd.g_old_rec.cwg_attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.cwg_attribute15, hr_api.g_varchar2) or
230 nvl(ben_cwg_shd.g_old_rec.cwg_attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.cwg_attribute16, hr_api.g_varchar2) or
232 nvl(ben_cwg_shd.g_old_rec.cwg_attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.cwg_attribute17, hr_api.g_varchar2) or
234 nvl(ben_cwg_shd.g_old_rec.cwg_attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.cwg_attribute18, hr_api.g_varchar2) or
236 nvl(ben_cwg_shd.g_old_rec.cwg_attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.cwg_attribute19, hr_api.g_varchar2) or
238 nvl(ben_cwg_shd.g_old_rec.cwg_attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.cwg_attribute20, hr_api.g_varchar2) or
240 nvl(ben_cwg_shd.g_old_rec.cwg_attribute21, hr_api.g_varchar2) <>
241 nvl(p_rec.cwg_attribute21, hr_api.g_varchar2) or
242 nvl(ben_cwg_shd.g_old_rec.cwg_attribute22, hr_api.g_varchar2) <>
243 nvl(p_rec.cwg_attribute22, hr_api.g_varchar2) or
244 nvl(ben_cwg_shd.g_old_rec.cwg_attribute23, hr_api.g_varchar2) <>
245 nvl(p_rec.cwg_attribute23, hr_api.g_varchar2) or
246 nvl(ben_cwg_shd.g_old_rec.cwg_attribute24, hr_api.g_varchar2) <>
247 nvl(p_rec.cwg_attribute24, hr_api.g_varchar2) or
248 nvl(ben_cwg_shd.g_old_rec.cwg_attribute25, hr_api.g_varchar2) <>
249 nvl(p_rec.cwg_attribute25, hr_api.g_varchar2) or
250 nvl(ben_cwg_shd.g_old_rec.cwg_attribute26, hr_api.g_varchar2) <>
251 nvl(p_rec.cwg_attribute26, hr_api.g_varchar2) or
252 nvl(ben_cwg_shd.g_old_rec.cwg_attribute27, hr_api.g_varchar2) <>
253 nvl(p_rec.cwg_attribute27, hr_api.g_varchar2) or
254 nvl(ben_cwg_shd.g_old_rec.cwg_attribute28, hr_api.g_varchar2) <>
255 nvl(p_rec.cwg_attribute28, hr_api.g_varchar2) or
256 nvl(ben_cwg_shd.g_old_rec.cwg_attribute29, hr_api.g_varchar2) <>
257 nvl(p_rec.cwg_attribute29, hr_api.g_varchar2) or
258 nvl(ben_cwg_shd.g_old_rec.cwg_attribute30, hr_api.g_varchar2) <>
259 nvl(p_rec.cwg_attribute30, hr_api.g_varchar2)
260 ))
261 or (p_rec.cwb_wksht_grp_id is null) then
262 --
263 -- Only execute the validation if absolutely necessary:
264 -- a) During update, the structure column value or any
265 -- of the attribute values have actually changed.
266 -- b) During insert.
267 --
268 hr_dflex_utility.ins_or_upd_descflex_attribs
269 (p_appl_short_name => 'BEN'
270 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
271 ,p_attribute_category => 'CWG_ATTRIBUTE_CATEGORY'
272 ,p_attribute1_name => 'CWG_ATTRIBUTE1'
273 ,p_attribute1_value => p_rec.cwg_attribute1
274 ,p_attribute2_name => 'CWG_ATTRIBUTE2'
275 ,p_attribute2_value => p_rec.cwg_attribute2
276 ,p_attribute3_name => 'CWG_ATTRIBUTE3'
277 ,p_attribute3_value => p_rec.cwg_attribute3
278 ,p_attribute4_name => 'CWG_ATTRIBUTE4'
279 ,p_attribute4_value => p_rec.cwg_attribute4
280 ,p_attribute5_name => 'CWG_ATTRIBUTE5'
281 ,p_attribute5_value => p_rec.cwg_attribute5
282 ,p_attribute6_name => 'CWG_ATTRIBUTE6'
283 ,p_attribute6_value => p_rec.cwg_attribute6
284 ,p_attribute7_name => 'CWG_ATTRIBUTE7'
285 ,p_attribute7_value => p_rec.cwg_attribute7
286 ,p_attribute8_name => 'CWG_ATTRIBUTE8'
287 ,p_attribute8_value => p_rec.cwg_attribute8
288 ,p_attribute9_name => 'CWG_ATTRIBUTE9'
289 ,p_attribute9_value => p_rec.cwg_attribute9
290 ,p_attribute10_name => 'CWG_ATTRIBUTE10'
291 ,p_attribute10_value => p_rec.cwg_attribute10
292 ,p_attribute11_name => 'CWG_ATTRIBUTE11'
293 ,p_attribute11_value => p_rec.cwg_attribute11
294 ,p_attribute12_name => 'CWG_ATTRIBUTE12'
295 ,p_attribute12_value => p_rec.cwg_attribute12
296 ,p_attribute13_name => 'CWG_ATTRIBUTE13'
297 ,p_attribute13_value => p_rec.cwg_attribute13
298 ,p_attribute14_name => 'CWG_ATTRIBUTE14'
299 ,p_attribute14_value => p_rec.cwg_attribute14
300 ,p_attribute15_name => 'CWG_ATTRIBUTE15'
301 ,p_attribute15_value => p_rec.cwg_attribute15
302 ,p_attribute16_name => 'CWG_ATTRIBUTE16'
303 ,p_attribute16_value => p_rec.cwg_attribute16
304 ,p_attribute17_name => 'CWG_ATTRIBUTE17'
305 ,p_attribute17_value => p_rec.cwg_attribute17
306 ,p_attribute18_name => 'CWG_ATTRIBUTE18'
307 ,p_attribute18_value => p_rec.cwg_attribute18
308 ,p_attribute19_name => 'CWG_ATTRIBUTE19'
309 ,p_attribute19_value => p_rec.cwg_attribute19
310 ,p_attribute20_name => 'CWG_ATTRIBUTE20'
311 ,p_attribute20_value => p_rec.cwg_attribute20
312 ,p_attribute21_name => 'CWG_ATTRIBUTE21'
313 ,p_attribute21_value => p_rec.cwg_attribute21
314 ,p_attribute22_name => 'CWG_ATTRIBUTE22'
315 ,p_attribute22_value => p_rec.cwg_attribute22
316 ,p_attribute23_name => 'CWG_ATTRIBUTE23'
317 ,p_attribute23_value => p_rec.cwg_attribute23
318 ,p_attribute24_name => 'CWG_ATTRIBUTE24'
319 ,p_attribute24_value => p_rec.cwg_attribute24
320 ,p_attribute25_name => 'CWG_ATTRIBUTE25'
321 ,p_attribute25_value => p_rec.cwg_attribute25
322 ,p_attribute26_name => 'CWG_ATTRIBUTE26'
323 ,p_attribute26_value => p_rec.cwg_attribute26
324 ,p_attribute27_name => 'CWG_ATTRIBUTE27'
325 ,p_attribute27_value => p_rec.cwg_attribute27
326 ,p_attribute28_name => 'CWG_ATTRIBUTE28'
327 ,p_attribute28_value => p_rec.cwg_attribute28
328 ,p_attribute29_name => 'CWG_ATTRIBUTE29'
329 ,p_attribute29_value => p_rec.cwg_attribute29
330 ,p_attribute30_name => 'CWG_ATTRIBUTE30'
331 ,p_attribute30_value => p_rec.cwg_attribute30
332 );
333 end if;
334 --
335 hr_utility.set_location(' Leaving:'||l_proc,20);
336 end chk_df;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |-----------------------< chk_non_updateable_args >------------------------|
340 -- ----------------------------------------------------------------------------
341 -- {Start Of Comments}
342 --
343 -- Description:
344 -- This procedure is used to ensure that non updateable attributes have
345 -- not been updated. If an attribute has been updated an error is generated.
346 --
347 -- Pre Conditions:
348 -- g_old_rec has been populated with details of the values currently in
349 -- the database.
350 --
351 -- In Arguments:
352 -- p_rec has been populated with the updated values the user would like the
353 -- record set to.
354 --
355 -- Post Success:
356 -- Processing continues if all the non updateable attributes have not
357 -- changed.
358 --
359 -- Post Failure:
360 -- An application error is raised if any of the non updatable attributes
361 -- have been altered.
362 --
363 -- {End Of Comments}
364 -- ----------------------------------------------------------------------------
365 Procedure chk_non_updateable_args
366 (p_effective_date in date
367 ,p_rec in ben_cwg_shd.g_rec_type
368 ) IS
369 --
370 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
371 --
372 Begin
373 --
374 -- Only proceed with the validation if a row exists for the current
375 -- record in the HR Schema.
376 --
377 IF NOT ben_cwg_shd.api_updating
378 (p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id
379 ,p_object_version_number => p_rec.object_version_number
380 ) THEN
381 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
382 fnd_message.set_token('PROCEDURE ', l_proc);
383 fnd_message.set_token('STEP ', '5');
384 fnd_message.raise_error;
385 END IF;
386 --
387 IF nvl(p_rec.business_group_id,hr_api.g_number) <>
388 nvl(ben_cwg_shd.g_old_rec.business_group_id,hr_api.g_number) then
389
390 hr_api.argument_changed_error
391 (p_api_name => l_proc,
392 p_argument => 'BUSINESS_GROUP_ID',
393 p_base_table => ben_cwg_shd.g_tab_nam);
394 END IF;
395 --
396 IF nvl(p_rec.pl_id,hr_api.g_number) <>
397 nvl(ben_cwg_shd.g_old_rec.pl_id,hr_api.g_number) then
398
399 hr_api.argument_changed_error
400 (p_api_name => l_proc,
401 p_argument => 'PL_ID',
402 p_base_table => ben_cwg_shd.g_tab_nam);
403 END IF;
404
405 End chk_non_updateable_args;
406 --
407
408 /*
409 Procedure chk_status_cd(p_cwb_wksht_grp_id in number,
410 p_status_cd in varchar2,
411 p_effective_date in date,
412 p_object_version_number in number) is
413 --
414 l_proc varchar2(72) := g_package||'chk_status_cd';
415 l_api_updating boolean;
416 --
417 Begin
418 --
419 hr_utility.set_location('Entering:'||l_proc, 5);
420 --
421 l_api_updating := ben_cwg_shd.api_updating
422 (p_cwb_wksht_grp_id => p_cwb_wksht_grp_id,
423 p_object_version_number => p_object_version_number);
424 --
425 if (l_api_updating
426 and p_status_cd <>
427 nvl(ben_cwg_shd.g_old_rec.status_cd,hr_api.g_varchar2)
428 or not l_api_updating) then
429 --
430 -- check if value of lookup falls within lookup type.
431 --
432 --
433 if hr_api.not_exists_in_hr_lookups
434 (p_lookup_type => 'BEN_CWG_STAT',
435 p_lookup_code => p_status_cd,
436 p_effective_date => p_effective_date) then
437 --
438 -- raise error as does not exist as lookup
439 --
440 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
441 fnd_message.set_token('FIELD', 'p_status_cd '||p_status_cd);
442 fnd_message.set_token('TYPE','BEN_CWG_STAT');
443 fnd_message.raise_error;
444 end if;
445 --
446 end if;
447 --
448 hr_utility.set_location('Leaving:'||l_proc,10);
449 end ;
450 */
451
452 ---
453
454 Procedure chk_hidden_cd(p_cwb_wksht_grp_id in number
455 ,p_hidden_cd in varchar2
456 ,p_effective_date in date
457 ,p_object_version_number in number
458 ) is
459 l_proc varchar2(72) := g_package||'chk_hidden_cd';
460 l_api_updating boolean;
461 Begin
462 --
463 hr_utility.set_location('Entering:'||l_proc, 5);
464 --
465 l_api_updating := ben_cwg_shd.api_updating
466 (p_cwb_wksht_grp_id => p_cwb_wksht_grp_id,
467 p_object_version_number => p_object_version_number);
468 --
469 if (l_api_updating
470 and p_hidden_cd
471 <> nvl(ben_cwg_shd.g_old_rec.hidden_cd,hr_api.g_varchar2)
472 or not l_api_updating) then
473 --
474 -- check if value of lookup falls within lookup type.
475 --
476 --
477 if p_hidden_cd is not null then
478 if hr_api.not_exists_in_hr_lookups
479 (p_lookup_type => 'BEN_WS_ACC',
480 p_lookup_code => p_hidden_cd,
481 p_effective_date => p_effective_date) then
482 --
483 -- raise error as does not exist as lookup
484 --
485 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
486 fnd_message.set_token('FIELD', 'p_hidden_cd');
487 fnd_message.set_token('VALUE', p_hidden_cd);
488 fnd_message.set_token('TYPE','BEN_WS_ACC');
489 fnd_message.raise_error;
490 --
491 end if;
492 end if ;
493 --
494 end if;
495 hr_utility.set_location('Leaving:'||l_proc,10);
496 --
497 end chk_hidden_cd;
498 --
499
500
501
502
503 --
504 -- ----------------------------------------------------------------------------
505 -- |----------------------------< chk_ordr_num >---------------------------|
506 -- ----------------------------------------------------------------------------
507 --
508 -- Description
509 -- This procedure is used to check that if display on enrollment check box
510 -- is selected and the Plan Type display code for self service is assigned
511 --
512 -- Pre Conditions
513 -- None.
514 --
515 -- In Parameters
516 -- cwb_wksht_grp_id PK of record being inserted or updated.
517 -- object_version_number Object version number of record being
518 -- inserted or updated.
519 -- p_business_group_id Business group id
520 -- p_ordr_num Ordr_num
521 --
522 -- Post Success
523 -- Processing continues
524 --
525 -- Post Failure
526 -- Error handled by procedure
527 --
528 -- Access Status
529 -- Internal table handler use only.
530 --
531 Procedure chk_ordr_num(p_cwb_wksht_grp_id in number,
532 p_object_version_number in number,
533 p_pl_id in number,
534 p_business_group_id in number,
535 p_ordr_num in number) is
536 --
537 l_proc varchar2(72) := g_package||'chk_ordr_num';
538 l_api_updating boolean;
539 l_dummy number;
540 --
541 cursor c_cwg is
542 select 1
543 from ben_cwb_wksht_grp cwg
544 where cwg.cwb_wksht_grp_id <> nvl(p_cwb_wksht_grp_id,-9999)
545 and pl_id = p_pl_id
546 and cwg.ordr_num = p_ordr_num
547 and cwg.business_group_id = p_business_group_id;
548
549 Begin
550 --
551 hr_utility.set_location('Entering:'||l_proc, 5);
552 --
553 l_api_updating := ben_cwg_shd.api_updating
554 (p_cwb_wksht_grp_id => p_cwb_wksht_grp_id,
555 p_object_version_number => p_object_version_number);
556 --
557 if (l_api_updating
558 and p_ordr_num
559 <> nvl(ben_cwg_shd.g_old_rec.ordr_num,hr_api.g_number)
560 or not l_api_updating)
561 and p_ordr_num is not null then
562 --
563 open c_cwg;
564 fetch c_cwg into l_dummy;
565 close c_cwg;
566
567 if l_dummy = 1 then
568 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
569 fnd_message.raise_error;
570 end if;
571 end if;
572 end chk_ordr_num;
573 --
574 -- ----------------------------------------------------------------------------
575 -- |----------------------------< chk_uniq_label >---------------------------|
576 -- ----------------------------------------------------------------------------
577 --
578 -- Description
579 -- This procedure is used to check that if display on enrollment check box
580 -- is selected and the Plan Type display code for self service is assigned
581 --
582 -- Pre Conditions
583 -- None.
584 --
585 -- In Parameters
586 -- cwb_wksht_grp_id PK of record being inserted or updated.
587 -- object_version_number Object version number of record being
588 -- inserted or updated.
589 -- p_business_group_id Business group id
590 -- p_label Label
591 --
592 -- Post Success
593 -- Processing continues
594 --
595 -- Post Failure
596 -- Error handled by procedure
597 --
598 -- Access Status
599 -- Internal table handler use only.
600 --
601 Procedure chk_uniq_label(p_cwb_wksht_grp_id in number,
602 p_object_version_number in number,
603 p_pl_id in number,
604 p_business_group_id in number,
605 p_label in varchar2) is
606 --
607 l_proc varchar2(72) := g_package||'chk_uniq_label';
608 l_api_updating boolean;
609 l_dummy number;
610 --
611 cursor c_cwg is
612 select 1
613 from ben_cwb_wksht_grp cwg
614 where cwg.cwb_wksht_grp_id <> nvl(p_cwb_wksht_grp_id,-9999)
615 and pl_id = p_pl_id
616 and upper(cwg.label) = upper(p_label)
617 and cwg.business_group_id = p_business_group_id;
618
619 Begin
620 --
621 hr_utility.set_location('Entering:'||l_proc, 5);
622 --
623 l_api_updating := ben_cwg_shd.api_updating
624 (p_cwb_wksht_grp_id => p_cwb_wksht_grp_id,
625 p_object_version_number => p_object_version_number);
626 --
627 if (l_api_updating
628 and p_label
629 <> nvl(ben_cwg_shd.g_old_rec.label,hr_api.g_varchar2)
630 or not l_api_updating)
631 and p_label is not null then
632 --
633 open c_cwg;
634 fetch c_cwg into l_dummy;
635 close c_cwg;
636
637 if l_dummy = 1 then
638 fnd_message.set_name('BEN','BEN_93316_LABEL_NOT_UNIQUE');
639 fnd_message.raise_error;
640 end if;
641 end if;
642 end chk_uniq_label;
643 --
644 -- ----------------------------------------------------------------------------
645 -- |----------------------------< chk_lookup_cd >---------------------------|
646 -- ----------------------------------------------------------------------------
647 --
648 -- Description
649 -- This procedure is used to check that if the lookup code is valid
650 --
651 -- Pre Conditions
652 -- None.
653 --
654 -- In Parameters
655 -- cwb_wksht_grp_id PK of record being inserted or updated.
656 -- object_version_number Object version number of record being
657 -- inserted or updated.
658 -- p_business_group_id Business group id
659 -- p_wksht_grp_cd WorkSheet Group Code
660 --
661 -- Post Success
662 -- Processing continues
663 --
664 -- Post Failure
665 -- Error handled by procedure
666 --
667 -- Access Status
668 -- Internal table handler use only.
669 --
670 Procedure chk_lookup_cd(p_cwb_wksht_grp_id in number,
671 p_object_version_number in number,
672 p_effective_date in date,
673 p_wksht_grp_cd in varchar2) is
674 --
675 l_proc varchar2(72) := g_package||'chk_lookup_cd';
676 l_api_updating boolean;
677 l_dummy number;
678 --
679
680 Begin
681 --
682 hr_utility.set_location('Entering:'||l_proc, 5);
683 --
684 l_api_updating := ben_cwg_shd.api_updating
685 (p_cwb_wksht_grp_id => p_cwb_wksht_grp_id,
686 p_object_version_number => p_object_version_number);
687 --
688 if (l_api_updating
689 and p_wksht_grp_cd
690 <> nvl(ben_cwg_shd.g_old_rec.wksht_grp_cd,hr_api.g_number)
691 or not l_api_updating)
692 and p_wksht_grp_cd is not null then
693 --
694 --
695 -- check if value of lookup falls within lookup type.
696 --
697 if hr_api.not_exists_in_hr_lookups
698 (p_lookup_type => 'BEN_WKSHT_GRP',
699 p_lookup_code => p_wksht_grp_cd,
700 p_effective_date => p_effective_date) then
701 --
702 -- raise error as does not exist as lookup
703 --
704 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
705 fnd_message.set_token('FIELD','p_wksht_grp_cd');
706 fnd_message.set_token('VALUE', p_wksht_grp_cd);
707 fnd_message.set_token('TYPE','BEN_WKSHT_GRP');
708 fnd_message.raise_error;
709 --
710 end if;
711
712 end if;
713 end chk_lookup_cd;
714
715 -- ----------------------------------------------------------------------------
716 -- |---------------------------< insert_validate >----------------------------|
717 -- ----------------------------------------------------------------------------
718 Procedure insert_validate
719 (p_effective_date in date
720 ,p_rec in ben_cwg_shd.g_rec_type
721 ) is
722 --
723 l_proc varchar2(72) := g_package||'insert_validate';
724 --
725 Begin
726 hr_utility.set_location('Entering:'||l_proc, 5);
727 --
728 -- Call all supporting business operations
729 --
730 hr_api.validate_bus_grp_id
731 (p_business_group_id => p_rec.business_group_id
732 ,p_associated_column1 => ben_cwg_shd.g_tab_nam
733 || '.BUSINESS_GROUP_ID');
734 --
735 -- After validating the set of important attributes,
736 -- if Multiple Message detection is enabled and at least
737 -- one error has been found then abort further validation.
738 --
739 hr_multi_message.end_validation_set;
740 --
741 -- Validate Dependent Attributes
742 --
743 chk_ordr_num(p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
744 p_object_version_number => p_rec.object_version_number,
745 p_pl_id => p_rec.pl_id,
746 p_business_group_id => p_rec.business_group_id,
747 p_ordr_num => p_rec.ordr_num);
748 --
749 chk_uniq_label(p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
750 p_object_version_number => p_rec.object_version_number,
751 p_pl_id => p_rec.pl_id,
752 p_business_group_id => p_rec.business_group_id,
753 p_label => p_rec.label);
754 --
755 chk_lookup_cd(p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
756 p_object_version_number => p_rec.object_version_number,
757 p_effective_date => p_effective_date,
758 p_wksht_grp_cd => p_rec.wksht_grp_cd);
759 --
760 chk_hidden_cd
761 (p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
762 p_hidden_cd => p_rec.hidden_cd,
763 p_effective_date => p_effective_date,
764 p_object_version_number => p_rec.object_version_number);
765 --
766 /*
767 chk_status_cd
768 (p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
769 p_status_cd => p_rec.status_cd,
770 p_effective_date => p_effective_date,
771 p_object_version_number => p_rec.object_version_number);
772 */
773 --
774 hr_utility.set_location(' Leaving:'||l_proc, 10);
775 End insert_validate;
776 --
777 -- ----------------------------------------------------------------------------
778 -- |---------------------------< update_validate >----------------------------|
779 -- ----------------------------------------------------------------------------
780 Procedure update_validate
781 (p_effective_date in date
782 ,p_rec in ben_cwg_shd.g_rec_type
783 ) is
784 --
785 l_proc varchar2(72) := g_package||'update_validate';
786 --
787 Begin
788 hr_utility.set_location('Entering:'||l_proc, 5);
789 --
790 -- Call all supporting business operations
791 --
792 hr_api.validate_bus_grp_id
793 (p_business_group_id => p_rec.business_group_id
794 ,p_associated_column1 => ben_cwg_shd.g_tab_nam
795 || '.BUSINESS_GROUP_ID');
796 --
797 -- After validating the set of important attributes,
798 -- if Multiple Message detection is enabled and at least
799 -- one error has been found then abort further validation.
800 --
801 hr_multi_message.end_validation_set;
802 --
803 -- Validate Dependent Attributes
804 --
805 chk_non_updateable_args
806 (p_effective_date => p_effective_date
807 ,p_rec => p_rec
808 );
809 --
810 chk_ordr_num(p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
811 p_object_version_number => p_rec.object_version_number,
812 p_pl_id => p_rec.pl_id,
813 p_business_group_id => p_rec.business_group_id,
814 p_ordr_num => p_rec.ordr_num);
815 --
816 chk_uniq_label(p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
817 p_object_version_number => p_rec.object_version_number,
818 p_pl_id => p_rec.pl_id,
819 p_business_group_id => p_rec.business_group_id,
820 p_label => p_rec.label);
821 --
822 chk_lookup_cd(p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
823 p_object_version_number => p_rec.object_version_number,
824 p_effective_date => p_effective_date,
825 p_wksht_grp_cd => p_rec.wksht_grp_cd);
826 --
827 chk_hidden_cd
828 (p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
829 p_hidden_cd => p_rec.hidden_cd,
830 p_effective_date => p_effective_date,
831 p_object_version_number => p_rec.object_version_number);
832 --
833 /*
834 chk_status_cd
835 (p_cwb_wksht_grp_id => p_rec.cwb_wksht_grp_id,
836 p_status_cd => p_rec.status_cd,
837 p_effective_date => p_effective_date,
838 p_object_version_number => p_rec.object_version_number);
839 */
840 --
841 hr_utility.set_location(' Leaving:'||l_proc, 10);
842 End update_validate;
843 --
844 -- ----------------------------------------------------------------------------
845 -- |---------------------------< delete_validate >----------------------------|
846 -- ----------------------------------------------------------------------------
847 Procedure delete_validate
848 (p_rec in ben_cwg_shd.g_rec_type
849 ) is
850 --
851 l_proc varchar2(72) := g_package||'delete_validate';
852 --
853 Begin
854 hr_utility.set_location('Entering:'||l_proc, 5);
855 --
856 -- Call all supporting business operations
857 --
858 hr_utility.set_location(' Leaving:'||l_proc, 10);
859 End delete_validate;
860 --
861 end ben_cwg_bus;