[Home] [Help]
PACKAGE BODY: APPS.PAY_SDB_BUS
Source
1 Package Body pay_sdb_bus as
2 /* $Header: pysdbrhi.pkb 120.0 2005/05/29 08:35:06 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_sdb_bus.'; -- Global package name
12 -- ----------------------------------------------------------------------------
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------< chk_non_updateable_args >------------------------|
13 Procedure chk_non_updateable_args
14 (p_rec in pay_sdb_shd.g_rec_type
15 ) is
16 --
17 -- Cursor to disallow update if a core defined balance has been
18 -- generated from this shadow defined balance.
19 --
20 cursor csr_disallow_update is
21 select null
22 from pay_template_core_objects tco
23 where tco.core_object_type = pay_tco_shd.g_sdb_lookup_type
24 and tco.shadow_object_id = p_rec.defined_balance_id;
25 --
26 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
27 l_updating boolean;
28 l_error exception;
29 l_argument varchar2(30);
30 l_api_updating boolean;
31 l_disallow varchar2(1);
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 l_api_updating := pay_sdb_shd.api_updating
36 (p_defined_balance_id => p_rec.defined_balance_id
37 ,p_object_version_number => p_rec.object_version_number
38 );
39 if not l_api_updating then
40 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
41 hr_utility.set_message_token('PROCEDURE', l_proc);
42 hr_utility.set_message_token('STEP', '10');
43 hr_utility.raise_error;
44 end if;
45 --
46 hr_utility.set_location(l_proc, 15);
47 --
48 -- Check that the update is actually allowed.
49 --
50 open csr_disallow_update;
51 fetch csr_disallow_update into l_disallow;
52 if csr_disallow_update%found then
53 hr_utility.set_location(l_proc, 20);
54 close csr_disallow_update;
55 fnd_message.set_name('PAY', 'PAY_50090_SDB_CORE_ROW_EXISTS');
56 fnd_message.raise_error;
57 end if;
58 close csr_disallow_update;
59 --
60 -- p_balance_type_id
61 --
62 if nvl(p_rec.balance_type_id, hr_api.g_number) <>
63 nvl(pay_sdb_shd.g_old_rec.balance_type_id, hr_api.g_number)
64 then
65 hr_utility.set_location(l_proc, 25);
66 l_argument := 'p_balance_type_id';
67 raise l_error;
68 end if;
69 hr_utility.set_location(' Leaving:'||l_proc, 25);
70 exception
71 when l_error then
72 hr_utility.set_location('Leaving:'||l_proc, 30);
73 hr_api.argument_changed_error
74 (p_api_name => l_proc
75 ,p_argument => l_argument);
76 when others then
77 hr_utility.set_location('Leaving:'||l_proc, 35);
78 raise;
79 End chk_non_updateable_args;
80 -- ----------------------------------------------------------------------------
81 -- |-------------------------< chk_exclusion_rule_id >------------------------|
82 -- ----------------------------------------------------------------------------
83 Procedure chk_exclusion_rule_id
84 (p_exclusion_rule_id in number
85 ,p_balance_type_id in number
86 ,p_defined_balance_id in number
87 ,p_object_version_number in number
88 ) is
89 --
90 -- Cursor to check that the exclusion_rule_id is valid.
91 --
92 cursor csr_exclusion_rule_id_valid is
93 select null
94 from pay_template_exclusion_rules ter
95 , pay_shadow_balance_types bt
96 where bt.balance_type_id = p_balance_type_id
97 and ter.exclusion_rule_id = p_exclusion_rule_id
98 and ter.template_id = bt.template_id
99 ;
100 --
101 l_proc varchar2(72) := g_package||'chk_exclusion_rule_id';
102 l_api_updating boolean;
103 l_valid varchar2(1);
104 --
105 Begin
106 hr_utility.set_location('Entering:'||l_proc, 5);
107 l_api_updating := pay_sdb_shd.api_updating
108 (p_defined_balance_id => p_defined_balance_id
109 ,p_object_version_number => p_object_version_number
110 );
111 if (l_api_updating and nvl(p_exclusion_rule_id, hr_api.g_number) <>
112 nvl(pay_sdb_shd.g_old_rec.exclusion_rule_id, hr_api.g_number)) or
113 not l_api_updating
114 then
115 if p_exclusion_rule_id is not null then
116 open csr_exclusion_rule_id_valid;
117 fetch csr_exclusion_rule_id_valid into l_valid;
118 if csr_exclusion_rule_id_valid%notfound then
119 hr_utility.set_location('Leaving:'||l_proc, 10);
120 close csr_exclusion_rule_id_valid;
121 fnd_message.set_name('PAY', 'PAY_50100_ETM_INVALID_EXC_RULE');
122 fnd_message.raise_error;
123 end if;
124 close csr_exclusion_rule_id_valid;
125 end if;
126 end if;
127 hr_utility.set_location('Leaving:'||l_proc, 5);
128 end chk_exclusion_rule_id;
129 -- ----------------------------------------------------------------------------
130 -- |--------------------------< chk_balance_type_id >-------------------------|
131 -- ----------------------------------------------------------------------------
132 Procedure chk_balance_type_id
133 (p_balance_type_id in number
134 ) is
135 --
136 -- Cursor to check that the balance type exists.
137 --
138 cursor csr_balance_type_exists is
139 select null
140 from pay_shadow_balance_types sbt
141 where sbt.balance_type_id = p_balance_type_id;
142 --
143 l_proc varchar2(72) := g_package||'chk_balance_type_id';
144 l_exists varchar2(1);
145 --
146 Begin
147 hr_utility.set_location('Entering:'||l_proc, 5);
148 --
149 -- Check that the balance type is not null.
150 --
151 hr_api.mandatory_arg_error
152 (p_api_name => l_proc
153 ,p_argument => 'p_balance_type_id'
154 ,p_argument_value => p_balance_type_id
155 );
156 --
157 -- Check that the balance type exists.
158 --
159 open csr_balance_type_exists;
160 fetch csr_balance_type_exists into l_exists;
161 if csr_balance_type_exists%notfound then
162 hr_utility.set_location(' Leaving:'||l_proc, 10);
163 close csr_balance_type_exists;
164 fnd_message.set_name('PAY', 'PAY_50086_ETM_INVALID_BAL_TYPE');
165 fnd_message.raise_error;
166 end if;
167 close csr_balance_type_exists;
168 hr_utility.set_location(' Leaving:'||l_proc, 15);
169 End chk_balance_type_id;
170 -- ----------------------------------------------------------------------------
171 -- |--------------------------< chk_dimension_name >--------------------------|
172 -- ----------------------------------------------------------------------------
173 Procedure chk_dimension_name
174 (p_dimension_name in varchar2
175 ,p_balance_type_id in number
176 ,p_defined_balance_id in number
177 ,p_object_version_number in number
178 ) is
179 --
180 -- Cursor to check the combination of dimension and balance type is
181 -- unique.
182 --
183 cursor csr_defined_balance_exists is
184 select null
185 from pay_shadow_defined_balances sdb
186 where sdb.balance_type_id = p_balance_type_id
187 and upper(sdb.dimension_name) = upper(p_dimension_name);
188 --
189 l_proc varchar2(72) := g_package||'chk_dimension_name';
190 l_api_updating boolean;
191 l_exists varchar2(1);
192 --
193 Begin
194 hr_utility.set_location('Entering:'||l_proc, 5);
195 l_api_updating := pay_sdb_shd.api_updating
196 (p_defined_balance_id => p_defined_balance_id
197 ,p_object_version_number => p_object_version_number
198 );
199 if (l_api_updating and nvl(p_dimension_name, hr_api.g_varchar2)
200 <> nvl(pay_sdb_shd.g_old_rec.dimension_name, hr_api.g_varchar2))
201 or not l_api_updating
202 then
203 --
204 -- Check that the balance dimension is not null.
205 --
206 hr_api.mandatory_arg_error
207 (p_api_name => l_proc
208 ,p_argument => 'p_dimension_name'
209 ,p_argument_value => p_dimension_name
210 );
211 --
212 -- Check that the defined balance is unique.
213 --
214 open csr_defined_balance_exists;
215 fetch csr_defined_balance_exists into l_exists;
216 if csr_defined_balance_exists%found then
217 hr_utility.set_location(' Leaving:'||l_proc, 10);
218 close csr_defined_balance_exists;
219 fnd_message.set_name('PAY', 'PAY_50091_SDB_DEF_BAL_EXISTS');
220 fnd_message.raise_error;
221 end if;
222 close csr_defined_balance_exists;
223 end if;
224 hr_utility.set_location(' Leaving:'||l_proc, 25);
225 End chk_dimension_name;
226 -- ----------------------------------------------------------------------------
227 -- |---------------------< chk_force_latest_bal_flag >------------------------|
228 -- ----------------------------------------------------------------------------
229 Procedure chk_force_latest_bal_flag
230 (p_effective_date in date
231 ,p_force_latest_balance_flag in varchar2
232 ,p_defined_balance_id in number
233 ,p_object_version_number in number
234 ) is
235 --
236 l_proc varchar2(72) := g_package||'chk_force_latest_bal_flag';
237 l_api_updating boolean;
238 --
239 Begin
240 hr_utility.set_location('Entering:'||l_proc, 5);
241 l_api_updating := pay_sdb_shd.api_updating
242 (p_defined_balance_id => p_defined_balance_id
243 ,p_object_version_number => p_object_version_number
244 );
245 if (l_api_updating and
246 nvl(p_force_latest_balance_flag, hr_api.g_varchar2) <>
247 nvl(pay_sdb_shd.g_old_rec.force_latest_balance_flag, hr_api.g_varchar2))
248 or not l_api_updating
249 then
250 if p_force_latest_balance_flag is not null then
251 --
252 -- Validate against hr_lookups.
253 --
254 if hr_api.not_exists_in_hr_lookups
255 (p_effective_date => p_effective_date
256 ,p_lookup_type => 'YES_NO'
257 ,p_lookup_code => p_force_latest_balance_flag
258 )
259 then
260 hr_utility.set_location(' Leaving:'||l_proc, 10);
261 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
262 fnd_message.set_token('LOOKUP_TYPE', 'YES_NO');
263 fnd_message.set_token('COLUMN', 'FORCE_LATEST_BALANCE_FLAG');
264 fnd_message.raise_error;
265 end if;
266 end if;
267 end if;
268 hr_utility.set_location(' Leaving:'||l_proc, 20);
269 End chk_force_latest_bal_flag;
270 -- ----------------------------------------------------------------------------
271 -- |---------------------< chk_grossup_allowed_flag >-------------------------|
272 -- ----------------------------------------------------------------------------
273 Procedure chk_grossup_allowed_flag
274 (p_effective_date in date
275 ,p_grossup_allowed_flag in varchar2
276 ,p_defined_balance_id in number
277 ,p_object_version_number in number
278 ) is
279 --
280 l_proc varchar2(72) := g_package||'chk_grossup_allowed_flag';
281 l_api_updating boolean;
282 --
283 Begin
284 hr_utility.set_location('Entering:'||l_proc, 5);
285 l_api_updating := pay_sdb_shd.api_updating
286 (p_defined_balance_id => p_defined_balance_id
287 ,p_object_version_number => p_object_version_number
288 );
289 if (l_api_updating and
290 nvl(p_grossup_allowed_flag, hr_api.g_varchar2) <>
291 nvl(pay_sdb_shd.g_old_rec.grossup_allowed_flag, hr_api.g_varchar2))
292 or not l_api_updating
293 then
294 if p_grossup_allowed_flag is not null then
295 --
296 -- Validate against hr_lookups.
297 --
298 if hr_api.not_exists_in_hr_lookups
299 (p_effective_date => p_effective_date
300 ,p_lookup_type => 'YES_NO'
301 ,p_lookup_code => p_grossup_allowed_flag
302 )
303 then
304 hr_utility.set_location(' Leaving:'||l_proc, 10);
305 fnd_message.set_name('PAY', 'HR_52966_INVALID_LOOKUP');
306 fnd_message.set_token('LOOKUP_TYPE', 'YES_NO');
307 fnd_message.set_token('COLUMN', 'GROSSUP_ALLOWED_FLAG');
308 fnd_message.raise_error;
309 end if;
310 end if;
311 end if;
312 hr_utility.set_location(' Leaving:'||l_proc, 20);
313 End chk_grossup_allowed_flag;
314 -- ----------------------------------------------------------------------------
315 -- |------------------------------< chk_delete >------------------------------|
316 -- ----------------------------------------------------------------------------
317 Procedure chk_delete
318 (p_defined_balance_id in number
319 ) is
320 --
321 -- Cursors to check for rows referencing the balance classification.
322 --
323 cursor csr_core_objects is
324 select null
325 from pay_template_core_objects tco
326 where tco.core_object_type = pay_tco_shd.g_sdb_lookup_type
327 and tco.shadow_object_id = p_defined_balance_id;
328 --
329 cursor csr_bal_attributes is
330 select null
331 from pay_shadow_bal_attributes ba
332 where ba.defined_balance_id = p_defined_balance_id
333 ;
334 --
335 l_proc varchar2(72) := g_package||'chk_delete';
336 l_error exception;
337 l_exists varchar2(1);
338 --
339 Begin
340 hr_utility.set_location('Entering:'||l_proc, 5);
341 --
342 open csr_core_objects;
343 fetch csr_core_objects into l_exists;
344 if csr_core_objects%found then
345 hr_utility.set_location(' Leaving:'||l_proc, 10);
346 close csr_core_objects;
347 raise l_error;
348 end if;
349 close csr_core_objects;
350 --
351 open csr_bal_attributes;
352 fetch csr_bal_attributes into l_exists;
353 if csr_bal_attributes%found then
354 hr_utility.set_location(' Leaving:'||l_proc, 12);
355 close csr_bal_attributes;
356 raise l_error;
357 end if;
358 close csr_bal_attributes;
359 hr_utility.set_location(' Leaving:'||l_proc, 15);
360 exception
361 when l_error then
362 fnd_message.set_name('PAY', 'PAY_50092_SDB_INVALID_DELETE');
363 fnd_message.raise_error;
364 when others then
365 hr_utility.set_location(' Leaving:'||l_proc, 20);
366 raise;
367 End chk_delete;
368 -- ----------------------------------------------------------------------------
369 -- |---------------------------< insert_validate >----------------------------|
370 -- ----------------------------------------------------------------------------
371 Procedure insert_validate
372 (p_effective_date in date
373 ,p_rec in pay_sdb_shd.g_rec_type
374 ) is
375 --
376 l_proc varchar2(72) := g_package||'insert_validate';
377 --
378 Begin
379 hr_utility.set_location('Entering:'||l_proc, 5);
380 --
381 -- Call all supporting business operations
382 --
383 chk_balance_type_id(p_rec.balance_type_id);
384 --
385 chk_dimension_name
386 (p_dimension_name => p_rec.dimension_name
387 ,p_balance_type_id => p_rec.balance_type_id
388 ,p_defined_balance_id => p_rec.defined_balance_id
389 ,p_object_version_number => p_rec.object_version_number
390 );
391 --
392 chk_force_latest_bal_flag
393 (p_effective_date => p_effective_date
394 ,p_force_latest_balance_flag => p_rec.force_latest_balance_flag
395 ,p_defined_balance_id => p_rec.defined_balance_id
396 ,p_object_version_number => p_rec.object_version_number
397 );
398 --
399 chk_grossup_allowed_flag
400 (p_effective_date => p_effective_date
401 ,p_grossup_allowed_flag => p_rec.grossup_allowed_flag
402 ,p_defined_balance_id => p_rec.defined_balance_id
403 ,p_object_version_number => p_rec.object_version_number
404 );
405 --
406 chk_exclusion_rule_id
407 (p_exclusion_rule_id => p_rec.exclusion_rule_id
408 ,p_balance_type_id => p_rec.balance_type_id
409 ,p_defined_balance_id => p_rec.defined_balance_id
410 ,p_object_version_number => p_rec.object_version_number
411 );
412 hr_utility.set_location(' Leaving:'||l_proc, 10);
413 End insert_validate;
414 --
415 -- ----------------------------------------------------------------------------
416 -- |---------------------------< update_validate >----------------------------|
417 -- ----------------------------------------------------------------------------
418 Procedure update_validate
419 (p_effective_date in date
420 ,p_rec in pay_sdb_shd.g_rec_type
421 ) is
422 --
423 l_proc varchar2(72) := g_package||'update_validate';
424 --
425 Begin
426 hr_utility.set_location('Entering:'||l_proc, 5);
427 --
428 -- Call all supporting business operations
429 --
430 chk_non_updateable_args(p_rec);
431 --
432 chk_dimension_name
433 (p_dimension_name => p_rec.dimension_name
434 ,p_balance_type_id => p_rec.balance_type_id
435 ,p_defined_balance_id => p_rec.defined_balance_id
436 ,p_object_version_number => p_rec.object_version_number
437 );
438 --
439 chk_force_latest_bal_flag
440 (p_effective_date => p_effective_date
441 ,p_force_latest_balance_flag => p_rec.force_latest_balance_flag
442 ,p_defined_balance_id => p_rec.defined_balance_id
443 ,p_object_version_number => p_rec.object_version_number
444 );
445 --
446 chk_grossup_allowed_flag
447 (p_effective_date => p_effective_date
448 ,p_grossup_allowed_flag => p_rec.grossup_allowed_flag
449 ,p_defined_balance_id => p_rec.defined_balance_id
450 ,p_object_version_number => p_rec.object_version_number
451 );
452 --
453 chk_exclusion_rule_id
454 (p_exclusion_rule_id => p_rec.exclusion_rule_id
455 ,p_balance_type_id => p_rec.balance_type_id
456 ,p_defined_balance_id => p_rec.defined_balance_id
457 ,p_object_version_number => p_rec.object_version_number
458 );
459 --
460 hr_utility.set_location(' Leaving:'||l_proc, 10);
461 End update_validate;
462 --
463 -- ----------------------------------------------------------------------------
464 -- |---------------------------< delete_validate >----------------------------|
465 -- ----------------------------------------------------------------------------
466 Procedure delete_validate(p_rec in pay_sdb_shd.g_rec_type) is
467 --
468 l_proc varchar2(72) := g_package||'delete_validate';
469 --
470 Begin
471 hr_utility.set_location('Entering:'||l_proc, 5);
472 --
473 -- Call all supporting business operations
474 --
475 chk_delete(p_rec.defined_balance_id);
476 --
477 hr_utility.set_location(' Leaving:'||l_proc, 10);
478 End delete_validate;
479 --
480 end pay_sdb_bus;