1 Package Body pay_evg_bus as
2 /* $Header: pyevgrhi.pkb 120.4 2005/11/07 09:03:07 mkataria noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_evg_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_event_group_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_event_group_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pay_event_groups evg
30 where evg.event_group_id = p_event_group_id
31 and pbg.business_group_id = evg.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'event_group_id'
47 ,p_argument_value => p_event_group_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_event_group_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , pay_event_groups evg
90 where evg.event_group_id = p_event_group_id
91 and pbg.business_group_id (+) = evg.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'event_group_id'
107 ,p_argument_value => p_event_group_id
108 );
109 --
110 if ( nvl(pay_evg_bus.g_event_group_id, hr_api.g_number)
111 = p_event_group_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := pay_evg_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 pay_evg_bus.g_event_group_id := p_event_group_id;
142 pay_evg_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_effective_date in date
177 ,p_rec in pay_evg_shd.g_rec_type
178 ) IS
179 --
180 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
181 l_error EXCEPTION;
182 l_argument varchar2(30);
183 --
184 Begin
185 --
186 -- Only proceed with the validation if a row exists for the current
187 -- record in the HR Schema.
188 --
189 IF NOT pay_evg_shd.api_updating
190 (p_event_group_id => p_rec.event_group_id
191 ,p_object_version_number => p_rec.object_version_number
192 ) THEN
193 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
194 fnd_message.set_token('PROCEDURE ', l_proc);
195 fnd_message.set_token('STEP ', '5');
196 fnd_message.raise_error;
197 END IF;
198 --
199 if (nvl(p_rec.event_group_name, hr_api.g_varchar2) <>
200 nvl(pay_evg_shd.g_old_rec.event_group_name, hr_api.g_varchar2)
201 ) THEN
202 l_argument := 'event_group_name';
203 raise l_error;
204 END IF;
205 --
206 if (nvl(p_rec.business_group_id, hr_api.g_number) <>
207 nvl(pay_evg_shd.g_old_rec.business_group_id,hr_api.g_number)
208 ) then
209 l_argument := 'business_group_id';
210 raise l_error;
211 END IF;
212 --
213 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
214 nvl(pay_evg_shd.g_old_rec.legislation_code, hr_api.g_varchar2)
215 then
216 l_argument := 'p_legislation_code';
217 raise l_error;
218 end if;
219 --
220 EXCEPTION
221 WHEN l_error THEN
222 hr_api.argument_changed_error
223 (p_api_name => l_proc
224 ,p_argument => l_argument);
225 WHEN OTHERS THEN
226 RAISE;
227 End chk_non_updateable_args;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |---------------------------< chk_unique_key >-----------------------------|
231 -- ----------------------------------------------------------------------------
232 --
233 -- Description
234 -- This procedure is used to check that the row being inserted or updated does
235 -- not already exists on the database, i.e, has the same event_group_name.
236 --
237 -- Pre Conditions
238 -- None.
239 --
240 -- In Parameters
241 --
242 -- Post Success
243 -- Processing continues
244 --
245 -- Post Failure
246 -- Errors handled by the procedure
247 --
248 -- Access Status
249 -- Internal table handler use only.
250 --
251 Procedure chk_unique_key ( p_rec in pay_evg_shd.g_rec_type ) is
252 --
253 l_exists varchar2(1);
254 l_proc varchar2(72) := g_package||'chk_unique_key';
255 --
256 cursor C1 is
257 select 'Y'
258 from pay_event_groups peg
259 where peg.event_group_name = p_rec.event_group_name
260 and ( nvl(peg.business_group_id,p_rec.business_group_id) = p_rec.business_group_id)
261 and ( nvl(peg.legislation_code,p_rec.legislation_code)= p_rec.legislation_code);
262 --
263 begin
264 --
265 hr_utility.set_location('Entering:'|| l_proc, 1);
266 --
267 open C1;
268 fetch C1 into l_exists;
269 if C1%found then
270 hr_utility.set_location(l_proc, 3);
271 -- row is not unique
272 close C1;
273 pay_evg_shd.constraint_error('PAY_EVENT_GROUPS_UK1');
274 end if;
275 close C1;
276 --
277 hr_utility.set_location('Leaving:'|| l_proc, 10);
278 --
279 end chk_unique_key;
280 --
281 -- ----------------------------------------------------------------------------
282 -- |---------------------------< chk_event_group_type >------------------------
283 -- ----------------------------------------------------------------------------
284 Procedure chk_event_group_type
285 (p_effective_date in date
286 ,p_rec in pay_evg_shd.g_rec_type) is
287 --
288 l_proc varchar2(72) := g_package || 'chk_event_group_type';
289 l_error EXCEPTION;
290 l_argument varchar2(30);
291 --
292 --
293 Begin
294 --
295 --
296 -- Check mandatory parameters have been set
297 --
298 hr_api.mandatory_arg_error
299 (p_api_name => l_proc
300 ,p_argument => 'event_group_type'
301 ,p_argument_value => p_rec.event_group_type
302 );
303 --
304 if hr_api.not_exists_in_hrstanlookups(p_effective_date => p_effective_date
305 ,p_lookup_type => 'EVENT_GROUP_TYPE'
306 ,p_lookup_code => p_rec.event_group_type) then
307 --
308 -- The event_group_type for this record is not recognised
309 --
310 fnd_message.set_name('PAY','HR_xxxx_INVALID_EVENT_GROUP');
311 fnd_message.raise_error;
312 end if;
313 hr_utility.set_location(l_proc,30);
314 --
315 -- Set the global variables so the values are
316 -- available for the next call to this function.
317 --
318 end chk_event_group_type;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |---------------------------< chk_proration_type >--------------------------
322 -- ----------------------------------------------------------------------------
323 Procedure chk_proration_type
324 (p_effective_date in date
325 ,p_rec in pay_evg_shd.g_rec_type) is
326 --
327 l_proc varchar2(72) := g_package || 'chk_proration_type';
328 l_error EXCEPTION;
329 l_argument varchar2(30);
330 --
331 --
332 Begin
333 --
334 if (p_rec.event_group_type = 'P') then
335 if hr_api.not_exists_in_hrstanlookups
336 (p_effective_date => p_effective_date
337 ,p_lookup_type => 'PRORATION_PERIOD_TYPE'
338 ,p_lookup_code => p_rec.proration_type) then
339 --
340 -- The proration_type for this record is not recognised
341 --
342 fnd_message.set_name('PAY','HR_xxxx_INVALID_PERIOD_TYPE');
343 fnd_message.raise_error;
344 end if;
345 else
346 if p_rec.proration_type is not null then
347 fnd_message.set_name('PAY','HR_xxxx_INVALID_PERIOD_TYPE');
348 fnd_message.raise_error;
349 end if;
350 end if;
351 --
352 hr_utility.set_location(l_proc,30);
353 --
354 -- Set the global variables so the values are
355 -- available for the next call to this function.
356 --
357 end chk_proration_type;
358 --
359 -- ----------------------------------------------------------------------------
360 -- |------------------------------< chk_delete >------------------------------|
361 -- ----------------------------------------------------------------------------
362 procedure chk_delete
363 (p_event_group_id in number
364 ) is
365 --
366 -- Only interested in child rows from PAY_EVENT_GROUP_USAGES.
367 --
368 cursor csr_child_exist
369 (p_event_group_id in number
370 ) is
371 select 'Y'
372 from pay_event_group_usages egu
373 where egu.event_group_id = p_event_group_id;
374 --
375 l_ret varchar2(1);
376 begin
377 open csr_child_exist(p_event_group_id => p_event_group_id);
378 fetch csr_child_exist into l_ret;
379 if csr_child_exist%found then
380 close csr_child_exist;
381 fnd_message.set_name('PAY', 'PAY_294526_ECU_CHILD_EXISTS');
382 fnd_message.raise_error;
383 end if;
384 close csr_child_exist;
385 exception
386 when others then
387 if csr_child_exist%isopen then
388 close csr_child_exist;
389 end if;
390 raise;
391 end chk_delete;
392 --
393 -- ----------------------------------------------------------------------------
394 -- |---------------------------< insert_validate >----------------------------|
395 -- ----------------------------------------------------------------------------
396 Procedure insert_validate
397 (p_effective_date in date
398 ,p_rec in pay_evg_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 -- Commenting this out as business group can be null
409 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
410 --
411 chk_unique_key (p_rec => p_rec);
412 --
413 chk_event_group_type (p_effective_date => p_effective_date
414 ,p_rec => p_rec);
415 --
416 chk_proration_type (p_effective_date => p_effective_date
417 ,p_rec => p_rec);
418 --
419 hr_utility.set_location(' Leaving:'||l_proc, 10);
420 End insert_validate;
421 --
422 -- ----------------------------------------------------------------------------
423 -- |---------------------------< update_validate >----------------------------|
424 -- ----------------------------------------------------------------------------
425 Procedure update_validate
426 (p_effective_date in date
427 ,p_rec in pay_evg_shd.g_rec_type
428 ) is
429 --
430 l_proc varchar2(72) := g_package||'update_validate';
431 --
432 Begin
433 hr_utility.set_location('Entering:'||l_proc, 5);
434 --
435 -- Call all supporting business operations
436 --
437 -- Commenting this out as business group can be null
438 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
439 --
440 chk_non_updateable_args
441 (p_effective_date => p_effective_date
442 ,p_rec => p_rec
443 );
444 --
445 --chk_unique_key (p_rec => p_rec);
446 --
447 chk_event_group_type (p_effective_date => p_effective_date
448 ,p_rec => p_rec);
449 --
450 chk_proration_type (p_effective_date => p_effective_date
451 ,p_rec => p_rec);
452 --
453 hr_utility.set_location(' Leaving:'||l_proc, 10);
454 --
455 End update_validate;
456 --
457 -- ----------------------------------------------------------------------------
458 -- |---------------------------< delete_validate >----------------------------|
459 -- ----------------------------------------------------------------------------
460 Procedure delete_validate
461 (p_rec in pay_evg_shd.g_rec_type
462 ) is
463 --
464 l_proc varchar2(72) := g_package||'delete_validate';
465 --
466 Begin
467 hr_utility.set_location('Entering:'||l_proc, 5);
468 --
469 -- Call all supporting business operations
470 --
471 chk_delete(p_event_group_id => p_rec.event_group_id);
472 --
473 hr_utility.set_location(' Leaving:'||l_proc, 10);
474 End delete_validate;
475 --
476 end pay_evg_bus;
[Home] [Help]