[Home] [Help]
PACKAGE BODY: APPS.PAY_TER_BUS
Source
1 Package Body pay_ter_bus as
2 /* $Header: pyterrhi.pkb 120.0 2005/05/29 09:04:14 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ter_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------< chk_non_updateable_args >------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure chk_non_updateable_args
14 (p_rec in pay_ter_shd.g_rec_type
15 ) is
16 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
17 l_updating boolean;
18 l_error exception;
19 l_argument varchar2(30);
20 l_api_updating boolean;
21 --
22 Begin
23 hr_utility.set_location('Entering:'||l_proc, 5);
24 l_api_updating := pay_ter_shd.api_updating
25 (p_exclusion_rule_id => p_rec.exclusion_rule_id
26 ,p_object_version_number => p_rec.object_version_number
27 );
28 if not l_api_updating then
29 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
30 hr_utility.set_message_token('PROCEDURE', l_proc);
31 hr_utility.set_message_token('STEP', '10');
32 hr_utility.raise_error;
33 end if;
34 --
35 hr_utility.set_location(l_proc, 15);
36 --
37 -- p_template_id
38 --
39 if nvl(p_rec.template_id, hr_api.g_number) <>
40 nvl(pay_ter_shd.g_old_rec.template_id, hr_api.g_number)
41 then
42 l_argument := 'p_template_id';
43 raise l_error;
44 end if;
45 hr_utility.set_location(' Leaving:'||l_proc, 20);
46 exception
47 when l_error then
48 hr_utility.set_location('Leaving:'||l_proc, 25);
49 hr_api.argument_changed_error
50 (p_api_name => l_proc
51 ,p_argument => l_argument);
52 when others then
53 hr_utility.set_location('Leaving:'||l_proc, 30);
54 raise;
55 End chk_non_updateable_args;
56 -- ----------------------------------------------------------------------------
57 -- |---------------------------< chk_template_id >----------------------------|
58 -- ----------------------------------------------------------------------------
59 Procedure chk_template_id
60 (p_template_id in number
61 ) is
62 --
66 cursor csr_template_id_valid is
63 -- Cursor to check that template_id is valid and refers to a template
64 -- of type 'T'.
65 --
67 select null
68 from pay_element_templates pet
69 where pet.template_id = p_template_id
70 and pet.template_type = 'T';
71 --
72 l_proc varchar2(72) := g_package||'chk_template_id';
73 l_valid varchar2(1);
74 --
75 Begin
76 hr_utility.set_location('Entering:'||l_proc, 5);
77 --
78 -- Check that template_id is not null.
79 --
80 hr_api.mandatory_arg_error
81 (p_api_name => l_proc
82 ,p_argument => 'p_template_id'
83 ,p_argument_value => p_template_id
84 );
85 --
86 -- Check that template_id is valid.
87 --
88 open csr_template_id_valid;
89 fetch csr_template_id_valid into l_valid;
90 if csr_template_id_valid%notfound then
91 hr_utility.set_location(' Leaving:'||l_proc, 10);
92 close csr_template_id_valid;
93 fnd_message.set_name('PAY', 'PAY_50057_BAD_SOURCE_TEMPLATE');
94 fnd_message.raise_error;
95 end if;
96 close csr_template_id_valid;
97 hr_utility.set_location(' Leaving:'||l_proc, 15);
98 End chk_template_id;
99 -- ----------------------------------------------------------------------------
100 -- |-------------------------< chk_flexfield_column >-------------------------|
101 -- ----------------------------------------------------------------------------
102 Procedure chk_flexfield_column
103 (p_flexfield_column in varchar2
104 ,p_template_id in number
105 ,p_exclusion_rule_id in number
106 ,p_object_version_number in number
107 ) is
108 --
109 -- Cursor to ensure that the flexfield column is not being used as
110 -- a default value column in PAY_SHADOW_INPUT_VALUES.
111 --
112 cursor csr_default_value_clash is
113 select null
114 from pay_shadow_element_types pset
115 , pay_shadow_input_values psiv
116 where pset.template_id = p_template_id
117 and psiv.element_type_id = pset.element_type_id
118 and nvl(upper(psiv.default_value_column), hr_api.g_varchar2) =
119 upper(p_flexfield_column);
120 --
121 l_proc varchar2(72) := g_package||'chk_flexfield_column';
122 l_len number;
123 l_prefix varchar2(2000);
124 l_suffix number;
125 l_error exception;
126 l_api_updating boolean;
127 l_clash varchar2(1);
128 --
129 Begin
130 hr_utility.set_location('Entering:'||l_proc, 5);
131 l_api_updating := pay_ter_shd.api_updating
132 (p_exclusion_rule_id => p_exclusion_rule_id
133 ,p_object_version_number => p_object_version_number
134 );
135 if (l_api_updating and nvl(p_flexfield_column, hr_api.g_varchar2) <>
136 nvl(pay_ter_shd.g_old_rec.flexfield_column, hr_api.g_varchar2)) or
137 not l_api_updating
138 then
139 --
140 -- Check that the flexfield column is not null.
141 --
142 hr_api.mandatory_arg_error
143 (p_api_name => l_proc
144 ,p_argument => 'p_flexfield_column'
145 ,p_argument_value => p_flexfield_column
146 );
147 --
148 -- Check that the flexfield column name is valid i.e. in the set
149 -- CONFIGURATION_INFORMATION1 .. CONFIGURATION_INFORMATION30
150 --
151 begin
152 l_len := length('CONFIGURATION_INFORMATION');
153 l_prefix := upper(substr(p_flexfield_column, 1, l_len));
154 l_suffix :=
155 fnd_number.canonical_to_number(substr(p_flexfield_column, l_len + 1));
156 l_suffix := nvl(l_suffix, -1);
157 if l_prefix <> 'CONFIGURATION_INFORMATION' or
158 (l_suffix < 1 or l_suffix > 30) or
159 (l_suffix <> trunc(l_suffix))
160 then
161 raise l_error;
162 end if;
163 exception
164 --
165 -- All exceptions are due to the name being in the incorrect
166 -- format.
167 --
168 when others then
169 hr_utility.set_location(' Leaving:'||l_proc, 10);
170 fnd_message.set_name('PAY', 'PAY_50130_ETM_BAD_FLEX_COLUMN');
171 fnd_message.set_token('FLEXFIELD_COLUMN', p_flexfield_column);
172 fnd_message.raise_error;
173 end;
174 --
175 -- Check that there are no clashes with default_value columns in
176 -- PAY_SHADOW_INPUT_VALUES.
177 --
178 open csr_default_value_clash;
179 fetch csr_default_value_clash into l_clash;
180 if csr_default_value_clash%found then
181 hr_utility.set_location(' Leaving:'||l_proc, 15);
182 close csr_default_value_clash;
183 fnd_message.set_name('PAY', 'PAY_50131_TER_SIV_CLASH');
184 fnd_message.raise_error;
185 end if;
186 close csr_default_value_clash;
187 end if;
188 hr_utility.set_location(' Leaving:'||l_proc, 20);
189 End chk_flexfield_column;
190 -- ----------------------------------------------------------------------------
191 -- |-------------------------< chk_exclusion_value >--------------------------|
192 -- ----------------------------------------------------------------------------
193 Procedure chk_exclusion_value
194 (p_exclusion_value in varchar2
195 ,p_exclusion_rule_id in number
196 ,p_object_version_number in number
197 ) is
198 --
199 l_proc varchar2(72) := g_package||'chk_exclusion_value';
200 l_api_updating boolean;
201 --
202 Begin
203 hr_utility.set_location('Entering:'||l_proc, 5);
204 l_api_updating := pay_ter_shd.api_updating
205 (p_exclusion_rule_id => p_exclusion_rule_id
206 ,p_object_version_number => p_object_version_number
207 );
208 if (l_api_updating and nvl(p_exclusion_value, hr_api.g_varchar2) <>
209 nvl(pay_ter_shd.g_old_rec.exclusion_value, hr_api.g_varchar2)) or
210 not l_api_updating
211 then
212 --
213 -- Check that the exclusion value is not null.
214 --
215 hr_api.mandatory_arg_error
216 (p_api_name => l_proc
217 ,p_argument => 'p_exclusion_value'
218 ,p_argument_value => p_exclusion_value
219 );
220 end if;
221 hr_utility.set_location(' Leaving:'||l_proc, 15);
222 End chk_exclusion_value;
223 -- ----------------------------------------------------------------------------
224 -- |------------------------------< chk_delete >------------------------------|
225 -- ----------------------------------------------------------------------------
226 Procedure chk_delete
227 (p_exclusion_rule_id in number
228 ) is
229 --
230 -- Cursors to check for rows referencing the exclusion rule.
231 --
232 cursor csr_element_types is
233 select null
234 from pay_shadow_element_types pset
235 where pset.exclusion_rule_id = p_exclusion_rule_id;
236 --
237 cursor csr_balance_types is
238 select null
239 from pay_shadow_balance_types sbt
240 where sbt.exclusion_rule_id = p_exclusion_rule_id;
241 --
242 cursor csr_input_values is
243 select null
244 from pay_shadow_input_values siv
245 where siv.exclusion_rule_id = p_exclusion_rule_id;
246 --
247 cursor csr_balance_feeds is
248 select null
249 from pay_shadow_balance_feeds sbf
250 where sbf.exclusion_rule_id = p_exclusion_rule_id;
251 --
252 cursor csr_formula_rules is
253 select null
254 from pay_shadow_formula_rules sfr
255 where sfr.exclusion_rule_id = p_exclusion_rule_id;
256 --
257 cursor csr_iterative_rules is
258 select null
259 from pay_shadow_iterative_rules sir
260 where sir.exclusion_rule_id = p_exclusion_rule_id;
261 --
262 cursor csr_ele_type_usages is
263 select null
264 from pay_shadow_ele_type_usages etu
265 where etu.exclusion_rule_id = p_exclusion_rule_id;
266 --
267 cursor csr_gu_bal_exclusions is
268 select null
269 from pay_shadow_gu_bal_exclusions sgb
270 where sgb.exclusion_rule_id = p_exclusion_rule_id;
271 --
272 cursor csr_balance_classi is
273 select null
274 from pay_shadow_balance_classi sbc
275 where sbc.exclusion_rule_id = p_exclusion_rule_id;
276 --
277 cursor csr_defined_balances is
278 select null
279 from pay_shadow_defined_balances sdb
280 where sdb.exclusion_rule_id = p_exclusion_rule_id;
281 --
282 cursor csr_sub_classi_rules is
283 select null
284 from pay_shadow_sub_classi_rules ssr
285 where ssr.exclusion_rule_id = p_exclusion_rule_id;
286 --
287 cursor csr_bal_attributes is
288 select null
289 from pay_shadow_bal_attributes sba
290 where sba.exclusion_rule_id = p_exclusion_rule_id;
291 --
292 cursor csr_template_ff_usages is
293 select null
294 from pay_template_ff_usages tfu
295 where tfu.exclusion_rule_id = p_exclusion_rule_id;
296 --
297 l_proc varchar2(72) := g_package||'chk_delete';
298 l_exists varchar(1);
299 l_error exception;
300 --
301 Begin
302 hr_utility.set_location('Entering:'||l_proc, 5);
303 --
304 open csr_element_types;
305 fetch csr_element_types into l_exists;
306 if csr_element_types%found then
307 hr_utility.set_location(' Leaving:'||l_proc, 10);
308 close csr_element_types;
309 raise l_error;
310 end if;
311 close csr_element_types;
312 --
313 open csr_balance_types;
314 fetch csr_balance_types into l_exists;
315 if csr_balance_types%found then
316 hr_utility.set_location(' Leaving:'||l_proc, 15);
317 close csr_balance_types;
318 raise l_error;
319 end if;
320 close csr_balance_types;
321 --
322 open csr_input_values;
323 fetch csr_input_values into l_exists;
324 if csr_input_values%found then
325 hr_utility.set_location(' Leaving:'||l_proc, 20);
326 close csr_input_values;
327 raise l_error;
328 end if;
329 close csr_input_values;
330 --
331 open csr_balance_feeds;
332 fetch csr_balance_feeds into l_exists;
333 if csr_balance_feeds%found then
334 hr_utility.set_location(' Leaving:'||l_proc, 25);
335 close csr_balance_feeds;
336 raise l_error;
337 end if;
338 close csr_balance_feeds;
339 --
340 open csr_formula_rules;
341 fetch csr_formula_rules into l_exists;
342 if csr_formula_rules%found then
343 hr_utility.set_location(' Leaving:'||l_proc, 30);
344 close csr_formula_rules;
345 raise l_error;
346 end if;
347 close csr_formula_rules;
348 --
349 open csr_iterative_rules;
350 fetch csr_iterative_rules into l_exists;
351 if csr_iterative_rules%found then
352 hr_utility.set_location(' Leaving:'||l_proc, 35);
353 close csr_iterative_rules;
354 raise l_error;
355 end if;
356 close csr_iterative_rules;
357 --
358 open csr_ele_type_usages;
359 fetch csr_ele_type_usages into l_exists;
360 if csr_ele_type_usages%found then
361 hr_utility.set_location(' Leaving:'||l_proc, 40);
362 close csr_ele_type_usages;
363 raise l_error;
364 end if;
365 close csr_ele_type_usages;
366 --
367 open csr_gu_bal_exclusions;
368 fetch csr_gu_bal_exclusions into l_exists;
369 if csr_gu_bal_exclusions%found then
370 hr_utility.set_location(' Leaving:'||l_proc, 45);
371 close csr_gu_bal_exclusions;
372 raise l_error;
373 end if;
374 close csr_gu_bal_exclusions;
375 --
376 open csr_balance_classi;
377 fetch csr_balance_classi into l_exists;
378 if csr_balance_classi%found then
379 hr_utility.set_location(' Leaving:'||l_proc, 50);
380 close csr_balance_classi;
381 raise l_error;
382 end if;
383 close csr_balance_classi;
384 --
385 open csr_defined_balances;
386 fetch csr_defined_balances into l_exists;
387 if csr_defined_balances%found then
388 hr_utility.set_location(' Leaving:'||l_proc, 55);
389 close csr_defined_balances;
390 raise l_error;
391 end if;
392 close csr_defined_balances;
393 --
394 open csr_sub_classi_rules;
395 fetch csr_sub_classi_rules into l_exists;
396 if csr_sub_classi_rules%found then
397 hr_utility.set_location(' Leaving:'||l_proc, 60);
398 close csr_sub_classi_rules;
399 raise l_error;
400 end if;
401 close csr_sub_classi_rules;
402 --
403 open csr_template_ff_usages;
404 fetch csr_template_ff_usages into l_exists;
405 if csr_template_ff_usages%found then
406 hr_utility.set_location(' Leaving:'||l_proc, 65);
407 close csr_template_ff_usages;
408 raise l_error;
409 end if;
410 close csr_template_ff_usages;
411 --
412 open csr_bal_attributes;
413 fetch csr_bal_attributes into l_exists;
414 if csr_bal_attributes%found then
415 hr_utility.set_location(' Leaving:'||l_proc, 70);
416 close csr_bal_attributes;
417 raise l_error;
418 end if;
419 close csr_bal_attributes;
420 hr_utility.set_location(' Leaving:'||l_proc, 75);
421 exception
422 when l_error then
423 fnd_message.set_name('PAY', 'PAY_50129_TER_INVALID_DELETE');
424 fnd_message.raise_error;
425 when others then
426 hr_utility.set_location(' Leaving:'||l_proc, 200);
427 raise;
428 End chk_delete;
429 -- ----------------------------------------------------------------------------
430 -- |---------------------------< insert_validate >----------------------------|
431 -- ----------------------------------------------------------------------------
432 Procedure insert_validate(p_rec in pay_ter_shd.g_rec_type) is
433 --
434 l_proc varchar2(72) := g_package||'insert_validate';
435 --
436 Begin
437 hr_utility.set_location('Entering:'||l_proc, 5);
438 --
439 -- Call all supporting business operations
440 --
441 chk_template_id(p_rec.template_id);
442 --
443 chk_flexfield_column
444 (p_flexfield_column => p_rec.flexfield_column
445 ,p_template_id => p_rec.template_id
446 ,p_exclusion_rule_id => p_rec.exclusion_rule_id
447 ,p_object_version_number => p_rec.object_version_number
448 );
449 --
450 chk_exclusion_value
451 (p_exclusion_value => p_rec.exclusion_value
452 ,p_exclusion_rule_id => p_rec.exclusion_rule_id
453 ,p_object_version_number => p_rec.object_version_number
454 );
455 --
456 hr_utility.set_location(' Leaving:'||l_proc, 10);
457 End insert_validate;
458 --
459 -- ----------------------------------------------------------------------------
460 -- |---------------------------< update_validate >----------------------------|
461 -- ----------------------------------------------------------------------------
462 Procedure update_validate(p_rec in pay_ter_shd.g_rec_type) is
463 --
464 l_proc varchar2(72) := g_package||'update_validate';
465 --
466 Begin
467 hr_utility.set_location('Entering:'||l_proc, 5);
468 --
469 -- Call all supporting business operations
470 --
471 chk_non_updateable_args(p_rec);
472 --
473 chk_flexfield_column
474 (p_flexfield_column => p_rec.flexfield_column
475 ,p_template_id => p_rec.template_id
476 ,p_exclusion_rule_id => p_rec.exclusion_rule_id
477 ,p_object_version_number => p_rec.object_version_number
478 );
479 --
480 chk_exclusion_value
481 (p_exclusion_value => p_rec.exclusion_value
482 ,p_exclusion_rule_id => p_rec.exclusion_rule_id
483 ,p_object_version_number => p_rec.object_version_number
484 );
485 --
486 hr_utility.set_location(' Leaving:'||l_proc, 10);
487 End update_validate;
488 --
489 -- ----------------------------------------------------------------------------
490 -- |---------------------------< delete_validate >----------------------------|
491 -- ----------------------------------------------------------------------------
492 Procedure delete_validate(p_rec in pay_ter_shd.g_rec_type) is
493 --
494 l_proc varchar2(72) := g_package||'delete_validate';
495 --
496 Begin
497 hr_utility.set_location('Entering:'||l_proc, 5);
498 --
499 -- Call all supporting business operations
500 --
501 chk_delete(p_rec.exclusion_rule_id);
502 hr_utility.set_location(' Leaving:'||l_proc, 10);
503 End delete_validate;
504 --
505 end pay_ter_bus;