[Home] [Help]
PACKAGE BODY: APPS.PAY_SBF_BUS
Source
1 Package Body pay_sbf_bus as
2 /* $Header: pysbfrhi.pkb 115.9 2003/02/05 17:28:10 arashid ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_sbf_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< get_template_info >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure get_template_info
14 (p_input_value_id in number
15 ,p_template_id in out nocopy number
16 ) is
17 --
18 -- Cursor to get the template information.
19 --
20 cursor csr_get_template_info is
21 select pet.template_id
22 from pay_shadow_input_values siv
23 , pay_shadow_element_types pset
24 , pay_element_templates pet
25 where siv.input_value_id = p_input_value_id
26 and pset.element_type_id = siv.element_type_id
27 and pet.template_id = pset.template_id;
28 --
29 l_proc varchar2(72) := g_package||'get_template_info';
30 l_api_updating boolean;
31 l_valid varchar2(1);
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 open csr_get_template_info;
36 fetch csr_get_template_info
37 into p_template_id;
38 close csr_get_template_info;
39 hr_utility.set_location(' Leaving:'||l_proc, 15);
40 End get_template_info;
41 -- ----------------------------------------------------------------------------
42 -- |-----------------------< chk_non_updateable_args >------------------------|
43 -- ----------------------------------------------------------------------------
44 Procedure chk_non_updateable_args
45 (p_rec in pay_sbf_shd.g_rec_type
46 ) is
47 --
48 -- Cursor to disallow update if a core balance classification has been
49 -- generated from this shadow balance classification.
50 --
51 cursor csr_disallow_update is
52 select null
53 from pay_template_core_objects tco
54 where tco.core_object_type = pay_tco_shd.g_sbf_lookup_type
55 and tco.shadow_object_id = p_rec.balance_feed_id;
56 --
57 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
58 l_updating boolean;
59 l_error exception;
60 l_argument varchar2(30);
61 l_api_updating boolean;
62 l_disallow varchar2(1);
63 --
64 Begin
65 hr_utility.set_location('Entering:'||l_proc, 5);
66 l_api_updating := pay_sbf_shd.api_updating
67 (p_balance_feed_id => p_rec.balance_feed_id
68 ,p_object_version_number => p_rec.object_version_number
69 );
70 if not l_api_updating then
71 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
72 hr_utility.set_message_token('PROCEDURE', l_proc);
73 hr_utility.set_message_token('STEP', '10');
74 hr_utility.raise_error;
75 end if;
76 --
77 hr_utility.set_location(l_proc, 15);
78 --
79 -- Check that the update is actually allowed.
80 --
81 open csr_disallow_update;
82 fetch csr_disallow_update into l_disallow;
83 if csr_disallow_update%found then
84 hr_utility.set_location(l_proc, 20);
85 close csr_disallow_update;
86 fnd_message.set_name('PAY', 'PAY_50104_SBF_CORE_ROW_EXISTS');
87 fnd_message.raise_error;
88 end if;
89 close csr_disallow_update;
90 --
91 -- p_input_value_id
92 --
93 if nvl(p_rec.input_value_id, hr_api.g_number) <>
94 nvl(pay_sbf_shd.g_old_rec.input_value_id, hr_api.g_number)
95 then
96 hr_utility.set_location(l_proc, 25);
97 l_argument := 'p_input_value_id';
98 raise l_error;
99 end if;
100 hr_utility.set_location(' Leaving:'||l_proc, 40);
101 exception
102 when l_error then
103 hr_api.argument_changed_error
104 (p_api_name => l_proc
105 ,p_argument => l_argument);
106 when others then
107 hr_utility.set_location('Leaving:'||l_proc, 45);
108 raise;
109 End chk_non_updateable_args;
110 -- ----------------------------------------------------------------------------
111 -- |---------------------------< chk_input_value_id >-------------------------|
112 -- ----------------------------------------------------------------------------
113 Procedure chk_input_value_id
114 (p_input_value_id in number
115 ) is
116 --
117 -- Cursor to check that the element type exists.
118 --
119 cursor csr_input_value_exists is
120 select null
121 from pay_shadow_input_values siv
122 where siv.input_value_id = p_input_value_id;
123 --
124 l_proc varchar2(72) := g_package||'chk_input_value_id';
125 l_exists varchar2(1);
126 --
127 Begin
128 hr_utility.set_location('Entering:'||l_proc, 5);
129 --
130 -- Check that the input value is not null.
131 --
132 hr_api.mandatory_arg_error
133 (p_api_name => l_proc
134 ,p_argument => 'p_input_value_id'
135 ,p_argument_value => p_input_value_id
136 );
137 --
138 -- Check that the input value exists.
139 --
140 open csr_input_value_exists;
141 fetch csr_input_value_exists into l_exists;
142 if csr_input_value_exists%notfound then
143 hr_utility.set_location(' Leaving:'||l_proc, 10);
144 close csr_input_value_exists;
145 fnd_message.set_name('PAY', 'PAY_50098_ETM_INVALID_INP_VAL');
146 fnd_message.raise_error;
147 end if;
148 close csr_input_value_exists;
149 hr_utility.set_location(' Leaving:'||l_proc, 15);
150 End chk_input_value_id;
151 -- ----------------------------------------------------------------------------
152 -- |--------------------------< chk_balance_type_id >-------------------------|
153 -- ----------------------------------------------------------------------------
154 Procedure chk_balance_type_id
155 (p_balance_type_id in number
156 ,p_input_value_id in number
157 ,p_template_id in number
158 ,p_balance_name in varchar2
159 ,p_balance_feed_id in number
160 ,p_object_version_number in number
161 ) is
162 --
163 -- Cursor to check that this combination of the input value and
164 -- balance type is unique.
165 --
166 cursor csr_exists is
167 select null
168 from pay_shadow_balance_feeds sbf
169 where sbf.input_value_id = p_input_value_id
170 and nvl(sbf.balance_type_id, hr_api.g_number) = p_balance_type_id;
171 --
172 -- Cursor to check that the balance type exists and is compatible with
173 -- the input value (same uom and from the same template).
174 --
175 cursor csr_compatible is
176 select null
177 from pay_shadow_input_values siv
178 , pay_shadow_balance_types sbt
179 where siv.input_value_id = p_input_value_id
180 and sbt.balance_type_id = p_balance_type_id
181 and upper(siv.uom) = upper(sbt.balance_uom)
182 and sbt.template_id = p_template_id;
183 --
184 l_proc varchar2(72) := g_package||'chk_balance_type_id';
185 l_okay varchar2(1);
186 l_exists varchar2(1);
187 l_api_updating boolean;
188 --
189 Begin
190 hr_utility.set_location('Entering:'||l_proc, 5);
191 l_api_updating := pay_sbf_shd.api_updating
192 (p_balance_feed_id => p_balance_feed_id
193 ,p_object_version_number => p_object_version_number
194 );
195 if (l_api_updating and nvl(p_balance_type_id, hr_api.g_number) <>
196 nvl(pay_sbf_shd.g_old_rec.balance_type_id, hr_api.g_number)) or
197 not l_api_updating
198 then
199 --
200 -- If the balance name is null then balance_type_id is mandatory.
201 --
202 if p_balance_name is null then
203 hr_api.mandatory_arg_error
204 (p_api_name => l_proc
205 ,p_argument => 'p_balance_type_id'
206 ,p_argument_value => p_balance_type_id
207 );
208 --
209 -- Only one of the balance name and balance_type_id may be not null.
210 --
211 elsif p_balance_type_id is not null then
212 hr_utility.set_location(' Leaving:'||l_proc, 15);
213 fnd_message.set_name('PAY', 'PAY_50105_SBF_ID_TYPE_NOT_NULL');
214 fnd_message.raise_error;
215 end if;
216 --
217 if p_balance_type_id is not null then
218 --
219 -- Check that the balance exists and is compatible with the
220 -- input value.
221 --
222 open csr_compatible;
223 fetch csr_compatible into l_okay;
224 if csr_compatible%notfound then
225 hr_utility.set_location(' Leaving:'||l_proc, 20);
226 close csr_compatible;
227 fnd_message.set_name('PAY', 'PAY_50106_SBF_UOM_MISMATCH');
228 fnd_message.raise_error;
229 end if;
230 close csr_compatible;
231 --
232 -- Check that this input value/balance type combination does not
233 -- exist.
234 --
235 open csr_exists;
236 fetch csr_exists into l_exists;
237 if csr_exists%found then
238 hr_utility.set_location(' Leaving:'||l_proc, 25);
239 close csr_exists;
240 fnd_message.set_name('PAY', 'PAY_50107_SBF_FEED_EXISTS');
241 fnd_message.raise_error;
242 end if;
243 close csr_exists;
244 end if;
245 end if;
246 hr_utility.set_location(' Leaving:'||l_proc, 30);
247 End chk_balance_type_id;
248 -- ----------------------------------------------------------------------------
249 -- |----------------------------< chk_balance_name >--------------------------|
250 -- ----------------------------------------------------------------------------
251 Procedure chk_balance_name
252 (p_balance_name in varchar2
253 ,p_input_value_id in number
254 ,p_balance_type_id in number
255 ,p_balance_feed_id in number
256 ,p_object_version_number in number
257 ) is
258 --
259 -- Cursor to check that this combination of balance name and input value
260 -- is unique.
261 --
262 cursor csr_exists is
263 select null
264 from pay_shadow_balance_feeds sbf
265 where sbf.input_value_id = p_input_value_id
266 and nvl(upper(sbf.balance_name), hr_api.g_varchar2) =
267 upper(p_balance_name);
268 --
269 l_proc varchar2(72) := g_package||'chk_balance_name';
270 l_exists varchar2(1);
271 l_api_updating boolean;
272 --
273 Begin
274 hr_utility.set_location('Entering:'||l_proc, 5);
275 l_api_updating := pay_sbf_shd.api_updating
276 (p_balance_feed_id => p_balance_feed_id
277 ,p_object_version_number => p_object_version_number
278 );
279 if (l_api_updating and nvl(p_balance_name, hr_api.g_varchar2) <>
280 nvl(pay_sbf_shd.g_old_rec.balance_name, hr_api.g_varchar2)) or
281 not l_api_updating
282 then
283 --
284 -- The balance name is mandatory if balance_type_id is null.
285 --
286 if p_balance_type_id is null then
287 hr_api.mandatory_arg_error
288 (p_api_name => l_proc
289 ,p_argument => 'p_balance_name'
290 ,p_argument_value => p_balance_name
291 );
292 end if;
293 --
294 if p_balance_name is not null then
295 --
296 -- Check that this input value/balance type combination does not
297 -- exist.
298 --
299 open csr_exists;
300 fetch csr_exists into l_exists;
301 if csr_exists%found then
302 hr_utility.set_location(' Leaving:'||l_proc, 20);
303 close csr_exists;
304 fnd_message.set_name('PAY', 'PAY_50107_SBF_FEED_EXISTS');
305 fnd_message.raise_error;
306 end if;
307 close csr_exists;
308 end if;
309 end if;
310 hr_utility.set_location(' Leaving:'||l_proc, 25);
311 End chk_balance_name;
312 -- ----------------------------------------------------------------------------
313 -- |-----------------------------< chk_scale >--------------------------------|
314 -- ----------------------------------------------------------------------------
315 Procedure chk_scale
316 (p_scale in number
317 ,p_balance_feed_id in number
318 ,p_object_version_number in number
319 ) is
320 l_proc varchar2(72) := g_package||'chk_scale';
321 l_api_updating boolean;
322 --
323 Begin
324 hr_utility.set_location('Entering:'||l_proc, 5);
325 l_api_updating := pay_sbf_shd.api_updating
326 (p_balance_feed_id => p_balance_feed_id
327 ,p_object_version_number => p_object_version_number
328 );
329 if (l_api_updating and nvl(p_scale, hr_api.g_number) <>
330 nvl(pay_sbc_shd.g_old_rec.scale, hr_api.g_number)) or
331 not l_api_updating
332 then
333 --
334 -- Check that scale is not null.
335 --
336 hr_api.mandatory_arg_error
337 (p_api_name => l_proc
338 ,p_argument => 'p_scale'
339 ,p_argument_value => p_scale
340 );
341 --
342 -- Check that scale is valid.
343 --
344 if p_scale <> 1 and p_scale <> -1 then
345 hr_utility.set_location(' Leaving:'||l_proc, 10);
346 fnd_message.set_name('PAY', 'PAY_50089_ETM_INVALID_SCALE');
347 fnd_message.set_token('POSITIVE', 1);
348 fnd_message.set_token('NEGATIVE', -1);
349 fnd_message.raise_error;
350 end if;
351 end if;
352 hr_utility.set_location(' Leaving:'||l_proc, 15);
353 End chk_scale;
354 -- ----------------------------------------------------------------------------
355 -- |-------------------------< chk_exclusion_rule_id >------------------------|
356 -- ----------------------------------------------------------------------------
357 Procedure chk_exclusion_rule_id
358 (p_exclusion_rule_id in number
359 ,p_template_id in number
360 ,p_balance_feed_id in number
361 ,p_object_version_number in number
362 ) is
363 --
364 -- Cursor to check that the exclusion_rule_id is valid.
365 --
366 cursor csr_exclusion_rule_id_valid is
367 select null
368 from pay_template_exclusion_rules ter
369 where ter.exclusion_rule_id = p_exclusion_rule_id
370 and ter.template_id = p_template_id;
371 --
372 l_proc varchar2(72) := g_package||'chk_exclusion_rule_id';
373 l_api_updating boolean;
374 l_valid varchar2(1);
375 --
376 Begin
377 hr_utility.set_location('Entering:'||l_proc, 5);
378 l_api_updating := pay_sbf_shd.api_updating
379 (p_balance_feed_id => p_balance_feed_id
380 ,p_object_version_number => p_object_version_number
381 );
382 if (l_api_updating and nvl(p_exclusion_rule_id, hr_api.g_number) <>
383 nvl(pay_sbf_shd.g_old_rec.exclusion_rule_id, hr_api.g_number)) or
384 not l_api_updating
385 then
386 if p_exclusion_rule_id is not null then
387 open csr_exclusion_rule_id_valid;
388 fetch csr_exclusion_rule_id_valid into l_valid;
389 if csr_exclusion_rule_id_valid%notfound then
390 hr_utility.set_location('Leaving:'||l_proc, 10);
391 close csr_exclusion_rule_id_valid;
392 fnd_message.set_name('PAY', 'PAY_50100_ETM_INVALID_EXC_RULE');
393 fnd_message.raise_error;
394 end if;
395 close csr_exclusion_rule_id_valid;
396 end if;
397 end if;
398 hr_utility.set_location(' Leaving:'||l_proc, 15);
399 End chk_exclusion_rule_id;
400 -- ----------------------------------------------------------------------------
401 -- |------------------------------< chk_delete >------------------------------|
402 -- ----------------------------------------------------------------------------
403 Procedure chk_delete
404 (p_balance_feed_id in number
405 ) is
406 --
410 select null
407 -- Cursors to check for rows referencing the balance classification.
408 --
409 cursor csr_core_objects is
411 from pay_template_core_objects tco
412 where tco.core_object_type = pay_tco_shd.g_sbf_lookup_type
413 and tco.shadow_object_id = p_balance_feed_id;
414 --
415 l_proc varchar2(72) := g_package||'chk_delete';
416 l_error exception;
417 l_exists varchar2(1);
418 --
419 Begin
420 hr_utility.set_location('Entering:'||l_proc, 5);
421 --
422 open csr_core_objects;
423 fetch csr_core_objects into l_exists;
424 if csr_core_objects%found then
425 hr_utility.set_location(' Leaving:'||l_proc, 10);
426 close csr_core_objects;
427 raise l_error;
428 end if;
429 close csr_core_objects;
430 hr_utility.set_location(' Leaving:'||l_proc, 15);
431 exception
432 when l_error then
433 fnd_message.set_name('PAY', 'PAY_50108_SBF_INVALID_DELETE');
434 fnd_message.raise_error;
435 when others then
436 hr_utility.set_location(' Leaving:'||l_proc, 20);
437 raise;
438 End chk_delete;
439 -- ----------------------------------------------------------------------------
440 -- |---------------------------< insert_validate >----------------------------|
441 -- ----------------------------------------------------------------------------
442 Procedure insert_validate(p_rec in pay_sbf_shd.g_rec_type) is
443 --
444 l_proc varchar2(72) := g_package||'insert_validate';
445 l_template_id number;
446 --
447 Begin
448 hr_utility.set_location('Entering:'||l_proc, 5);
449 --
450 -- Call all supporting business operations
451 --
452 chk_input_value_id(p_rec.input_value_id);
453 --
454 get_template_info
455 (p_input_value_id => p_rec.input_value_id
456 ,p_template_id => l_template_id
457 );
458 --
459 chk_balance_type_id
460 (p_balance_type_id => p_rec.balance_type_id
461 ,p_input_value_id => p_rec.input_value_id
462 ,p_template_id => l_template_id
463 ,p_balance_name => p_rec.balance_name
464 ,p_balance_feed_id => p_rec.balance_feed_id
465 ,p_object_version_number => p_rec.object_version_number
466 );
467 --
468 chk_balance_name
469 (p_balance_name => p_rec.balance_name
470 ,p_balance_type_id => p_rec.balance_type_id
471 ,p_input_value_id => p_rec.input_value_id
472 ,p_balance_feed_id => p_rec.balance_feed_id
473 ,p_object_version_number => p_rec.object_version_number
474 );
475 --
476 chk_scale
477 (p_scale => p_rec.scale
478 ,p_balance_feed_id => p_rec.balance_feed_id
479 ,p_object_version_number => p_rec.object_version_number
480 );
481 --
482 chk_exclusion_rule_id
483 (p_exclusion_rule_id => p_rec.exclusion_rule_id
484 ,p_template_id => l_template_id
485 ,p_balance_feed_id => p_rec.balance_feed_id
486 ,p_object_version_number => p_rec.object_version_number
487 );
488 --
489 hr_utility.set_location(' Leaving:'||l_proc, 10);
490 End insert_validate;
491 --
492 -- ----------------------------------------------------------------------------
493 -- |---------------------------< update_validate >----------------------------|
494 -- ----------------------------------------------------------------------------
495 Procedure update_validate(p_rec in pay_sbf_shd.g_rec_type) is
496 --
497 l_proc varchar2(72) := g_package||'update_validate';
498 l_template_id number;
499 --
500 Begin
501 hr_utility.set_location('Entering:'||l_proc, 5);
502 --
503 -- Call all supporting business operations
504 --
505 chk_non_updateable_args(p_rec);
506 --
507 get_template_info
508 (p_input_value_id => p_rec.input_value_id
509 ,p_template_id => l_template_id
510 );
511 --
512 chk_balance_type_id
513 (p_balance_type_id => p_rec.balance_type_id
514 ,p_input_value_id => p_rec.input_value_id
515 ,p_template_id => l_template_id
516 ,p_balance_name => p_rec.balance_name
517 ,p_balance_feed_id => p_rec.balance_feed_id
518 ,p_object_version_number => p_rec.object_version_number
519 );
520 --
521 chk_balance_name
522 (p_balance_name => p_rec.balance_name
523 ,p_balance_type_id => p_rec.balance_type_id
524 ,p_input_value_id => p_rec.input_value_id
525 ,p_balance_feed_id => p_rec.balance_feed_id
526 ,p_object_version_number => p_rec.object_version_number
527 );
528 --
529 chk_scale
530 (p_scale => p_rec.scale
531 ,p_balance_feed_id => p_rec.balance_feed_id
532 ,p_object_version_number => p_rec.object_version_number
533 );
534 --
535 chk_exclusion_rule_id
536 (p_exclusion_rule_id => p_rec.exclusion_rule_id
537 ,p_template_id => l_template_id
538 ,p_balance_feed_id => p_rec.balance_feed_id
539 ,p_object_version_number => p_rec.object_version_number
540 );
541 --
542 hr_utility.set_location(' Leaving:'||l_proc, 10);
543 End update_validate;
544 --
545 -- ----------------------------------------------------------------------------
546 -- |---------------------------< delete_validate >----------------------------|
547 -- ----------------------------------------------------------------------------
548 Procedure delete_validate(p_rec in pay_sbf_shd.g_rec_type) is
549 --
550 l_proc varchar2(72) := g_package||'delete_validate';
551 --
552 Begin
553 hr_utility.set_location('Entering:'||l_proc, 5);
554 --
555 -- Call all supporting business operations
556 --
557 chk_delete(p_rec.balance_feed_id);
558 --
559 hr_utility.set_location(' Leaving:'||l_proc, 10);
560 End delete_validate;
561 --
562 end pay_sbf_bus;