[Home] [Help]
PACKAGE BODY: APPS.PAY_SF_BUS
Source
1 Package Body pay_sf_bus as
2 /* $Header: pysfrhi.pkb 120.0 2005/05/29 02:17:28 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_sf_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------< chk_non_updateable_args >------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure chk_non_updateable_args
14 (p_rec in pay_sf_shd.g_rec_type
15 ) is
16 --
17 -- Cursor to disallow update if a formula has been generated from
18 -- this shadow formula.
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_sf_lookup_type
24 and tco.shadow_object_id = p_rec.formula_id;
25 --
26 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
27 l_error exception;
28 l_api_updating boolean;
29 l_argument varchar2(30);
30 l_disallow varchar2(1);
31 --
32 Begin
33 hr_utility.set_location('Entering:'||l_proc, 5);
34 l_api_updating := pay_sf_shd.api_updating
35 (p_formula_id => p_rec.formula_id
36 ,p_object_version_number => p_rec.object_version_number
37 );
38 if not l_api_updating then
39 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
40 hr_utility.set_message_token('PROCEDURE', l_proc);
41 hr_utility.set_message_token('STEP', '10');
42 hr_utility.raise_error;
43 end if;
44 --
45 hr_utility.set_location(l_proc, 20);
46 --
47 -- Check that the update is actually allowed.
48 --
49 open csr_disallow_update;
50 fetch csr_disallow_update into l_disallow;
51 if csr_disallow_update%found then
52 hr_utility.set_location(l_proc, 25);
53 close csr_disallow_update;
54 fnd_message.set_name('PAY', 'PAY_50101_SF_CORE_ROW_EXISTS');
55 fnd_message.raise_error;
56 end if;
57 close csr_disallow_update;
58 --
59 -- Check the otherwise non-updateable arguments.
60 --
61 -- p_business_group_id
62 --
63 if nvl(p_rec.business_group_id, hr_api.g_number) <>
64 nvl(pay_sf_shd.g_old_rec.business_group_id, hr_api.g_number)
65 then
66 l_argument := 'p_business_group_id';
67 raise l_error;
68 end if;
69 --
70 -- p_legislation_code
71 --
72 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
73 nvl(pay_sf_shd.g_old_rec.legislation_code, hr_api.g_varchar2)
74 then
75 l_argument := 'p_legislation_code';
76 raise l_error;
77 end if;
78 --
79 -- p_template_type
80 --
81 if nvl(p_rec.template_type, hr_api.g_varchar2) <>
82 nvl(pay_sf_shd.g_old_rec.template_type, hr_api.g_varchar2)
83 then
84 l_argument := 'p_template_type';
85 raise l_error;
86 end if;
87 --
88 -- p_formula_name
89 --
90 if nvl(p_rec.formula_name, hr_api.g_varchar2) <>
91 nvl(pay_sf_shd.g_old_rec.formula_name, hr_api.g_varchar2)
92 then
93 l_argument := 'p_formula_name';
94 raise l_error;
95 end if;
96
97 hr_utility.set_location('Leaving:'||l_proc, 25);
98 exception
99 when l_error then
100 hr_utility.set_location('Leaving:'||l_proc, 30);
101 hr_api.argument_changed_error
102 (p_api_name => l_proc
103 ,p_argument => l_argument);
104 when others then
105 hr_utility.set_location('Leaving:'||l_proc, 35);
106 raise;
107 End chk_non_updateable_args;
108 -- ----------------------------------------------------------------------------
109 -- |-------------------------< chk_busgrp_legcode >---------------------------|
110 -- ----------------------------------------------------------------------------
111 Procedure chk_busgrp_legcode
112 (p_business_group_id in number
113 ,p_legislation_code in varchar2
114 ) is
115 --
116 -- Cursor to validate the legislation_code.
117 --
118 cursor csr_valid_leg_code is
119 select null
120 from fnd_territories ft
121 where ft.territory_code = p_legislation_code;
122 --
123 l_proc varchar2(72) := g_package||'chk_busgrp_legcode';
124 l_valid varchar2(1);
125 --
126 Begin
127 hr_utility.set_location('Entering:'||l_proc, 5);
128 --
129 -- Check that at least one of legislation_code and business_group_id
130 -- is null.
131 --
132 if p_business_group_id is not null and p_legislation_code is not null
133 then
134 hr_utility.set_location(' Leaving:'||l_proc, 10);
135 fnd_message.set_name('PAY', 'PAY_50069_ETM_LEG_BUS_NOT_NULL');
136 fnd_message.raise_error;
137 end if;
138 --
139 -- Validate business_group_id.
140 --
141 if p_business_group_id is not null then
142 hr_api.validate_bus_grp_id( p_business_group_id );
143 end if;
144 --
145 -- Validate legislation_code.
146 --
147 if p_legislation_code is not null and
148 p_legislation_code <> 'ZZ' then
149 open csr_valid_leg_code;
150 fetch csr_valid_leg_code into l_valid;
151 if csr_valid_leg_code%notfound then
152 hr_utility.set_location(' Leaving:'||l_proc, 15);
153 close csr_valid_leg_code;
154 fnd_message.set_name('PAY', 'PAY_50070_INVALID_LEG_CODE');
155 fnd_message.raise_error;
156 end if;
157 close csr_valid_leg_code;
158 end if;
159 hr_utility.set_location(' Leaving:'||l_proc, 20);
160 End chk_busgrp_legcode;
161 -- ----------------------------------------------------------------------------
162 -- |---------------------------< chk_formula_name >---------------------------|
163 -- ----------------------------------------------------------------------------
164 Procedure chk_formula_name
165 (p_formula_name in varchar2
166 ,p_template_type in varchar2
167 ,p_legislation_code in varchar2
168 ,p_business_group_id in number
169 ) is
170 --
171 -- Cursor to check that the formula name is unique within a legislation
172 -- for all templates of template_type 'T'.
173 --
174 cursor csr_T_formula_name_exists
175 (p_formula_name in varchar2
176 ,p_legislation_code in varchar2
177 ,p_business_group_id in number
178 ) is
179 select null
180 from pay_shadow_formulas sf
181 where sf.template_type = 'T'
182 and upper(sf.formula_name) = upper(p_formula_name)
183 and
184 (
185 (sf.legislation_code is null and sf.business_group_id is null) or
186 (p_legislation_code is null and p_business_group_id is null) or
187 (sf.legislation_code = p_legislation_code) or
188 (sf.business_group_id = p_business_group_id) or
189 (p_legislation_code = (select legislation_code from per_business_groups_perf
190 where business_group_id = sf.business_group_id))
191 );
192 --
193 -- Cursor to check that the formula name is unique within its business
194 -- group if the template type is 'U'.
195 --
196 cursor csr_U_formula_name_exists is
197 select null
198 from pay_shadow_formulas sf
199 where sf.template_type = 'U'
200 and upper(sf.formula_name) = upper(p_formula_name)
201 and sf.business_group_id = p_business_group_id;
202 --
203 l_proc varchar2(72) := g_package||'chk_formula_name';
204 l_legislation_code varchar2(2000);
205 l_exists varchar2(1);
206 l_value varchar2(2000);
207 l_output varchar2(2000);
208 l_rgeflg varchar2(2000);
209 --
210 Begin
211 hr_utility.set_location('Entering:'||l_proc, 5);
212 --
213 -- Check that the name is not null.
214 --
215 hr_api.mandatory_arg_error
216 (p_api_name => l_proc
217 ,p_argument => 'p_formula_name'
218 ,p_argument_value => p_formula_name
219 );
220 --
221 -- Check that the formula name format is correct (database item name).
222 --
223 l_value := p_formula_name;
224 if p_template_type = 'T' then
225 --
226 -- If the template type is 'T' then the name can start with a '_'
227 -- which is not strictly the correct format.
228 --
229 l_value := replace(l_value, '_', 'A');
230 end if;
231 hr_chkfmt.checkformat
232 (value => l_value
233 ,format => 'DB_ITEM_NAME'
234 ,output => l_output
235 ,minimum => null
236 ,maximum => null
237 ,nullok => 'N'
238 ,rgeflg => l_rgeflg
239 ,curcode => null
240 );
241 --
242 -- Uniqueness checks.
243 --
244 if p_template_type = 'T' then
245 --
246 -- Get the legislation_code for the new template.
247 --
248 if p_business_group_id is not null then
249 l_legislation_code :=
250 hr_api.return_legislation_code(p_business_group_id);
251 else
252 l_legislation_code := p_legislation_code;
253 end if;
254 --
255 -- Check for uniqueness using the cursor.
256 --
257 open csr_T_formula_name_exists
258 (p_formula_name => p_formula_name
259 ,p_legislation_code => l_legislation_code
260 ,p_business_group_id => p_business_group_id
261 );
262 fetch csr_T_formula_name_exists into l_exists;
263 if csr_T_formula_name_exists%found then
264 close csr_T_formula_name_exists;
265 hr_utility.set_location(' Leaving:'||l_proc, 10);
266 fnd_message.set_name('PAY', 'PAY_50102_SF_FORMULA_EXISTS');
267 fnd_message.set_token('FORMULA_NAME', p_formula_name);
268 fnd_message.raise_error;
269 end if;
270 close csr_T_formula_name_exists;
271 elsif p_template_type = 'U' then
272 open csr_U_formula_name_exists;
273 fetch csr_U_formula_name_exists into l_exists;
274 if csr_U_formula_name_exists%found then
275 hr_utility.set_location(' Leaving:'||l_proc, 15);
276 close csr_U_formula_name_exists;
277 fnd_message.set_name('PAY', 'PAY_50102_SF_FORMULA_EXISTS');
278 fnd_message.set_token('FORMULA_NAME', p_formula_name);
279 fnd_message.raise_error;
280 end if;
281 close csr_U_formula_name_exists;
282 end if;
283 hr_utility.set_location(' Leaving:'||l_proc, 20);
284 End chk_formula_name;
285 --
286 -- ----------------------------------------------------------------------------
287 -- |--------------------------< chk_template_type >---------------------------|
288 -- ----------------------------------------------------------------------------
289 Procedure chk_template_type
290 (p_effective_date in date
291 ,p_legislation_code in varchar2
292 ,p_business_group_id in number
293 ,p_template_type in varchar2
294 ) is
295 --
296 l_proc varchar2(72) := g_package||'chk_template_type';
297 --
298 Begin
299 hr_utility.set_location('Entering:'||l_proc, 5);
300 --
301 -- Check that the template type is not null.
302 --
303 hr_api.mandatory_arg_error
304 (p_api_name => l_proc
305 ,p_argument => 'p_template_type'
306 ,p_argument_value => p_template_type
307 );
308 --
309 -- Validate against hr_lookups.
310 --
311 if p_template_type not in ('U','T') or
312 hr_api.not_exists_in_hr_lookups
313 (p_effective_date => p_effective_date
314 ,p_lookup_type => 'ELEMENT_TEMPLATE_TYPE'
315 ,p_lookup_code => p_template_type
316 )
317 then
318 hr_utility.set_location(' Leaving:'||l_proc, 10);
319 fnd_message.set_name('PAY', 'PAY_50082_ETM_BAD_TEMP_TYPE');
320 fnd_message.set_token('TEMPLATE_TYPE', p_template_type);
321 fnd_message.raise_error;
322 end if;
323 --
324 -- The legislation_code must be null and the business_group_id
325 -- not null if the template_type is 'U'.
326 --
327 if p_template_type = 'U' and
328 (p_legislation_code is not null or p_business_group_id is null)
329 then
330 hr_utility.set_location(' Leaving:'||l_proc, 20);
331 fnd_message.set_name('PAY', 'PAY_50081_ETM_BAD_BUS_GROUP');
332 fnd_message.raise_error;
333 end if;
334 hr_utility.set_location(' Leaving:'||l_proc, 20);
335 End chk_template_type;
336 -- ----------------------------------------------------------------------------
337 -- |------------------------------< chk_delete >------------------------------|
338 -- ----------------------------------------------------------------------------
339 Procedure chk_delete
340 (p_formula_id in number
341 ) is
342 --
343 -- Cursors to check for rows referencing the template.
344 --
345 cursor csr_element_types is
346 select null
347 from pay_shadow_element_types pset
348 where pset.payroll_formula_id = p_formula_id;
349 --
350 --
351 cursor csr_input_values is
352 select null
353 from pay_shadow_input_values psiv
354 where psiv.formula_id = p_formula_id;
355 --
356 cursor csr_core_objects is
357 select null
358 from pay_template_core_objects tco
359 where tco.core_object_type = pay_tco_shd.g_sf_lookup_type
360 and tco.shadow_object_id = p_formula_id;
361 --
362 cursor csr_ff_usages is
363 select null
364 from pay_template_ff_usages tfu
365 where tfu.formula_id = p_formula_id;
366 --
367 l_proc varchar2(72) := g_package||'chk_delete';
368 l_exists varchar2(1);
369 l_error exception;
370 --
371 Begin
372 hr_utility.set_location('Entering:'||l_proc, 5);
373 --
374 open csr_element_types;
375 fetch csr_element_types into l_exists;
376 if csr_element_types%found then
377 hr_utility.set_location(' Leaving:'||l_proc, 10);
378 close csr_element_types;
379 raise l_error;
380 end if;
381 close csr_element_types;
382 --
383 open csr_input_values;
384 fetch csr_input_values into l_exists;
385 if csr_input_values%found then
386 hr_utility.set_location(' Leaving:'||l_proc, 12);
387 close csr_input_values;
388 raise l_error;
389 end if;
390 close csr_input_values;
391 --
392 open csr_core_objects;
393 fetch csr_core_objects into l_exists;
394 if csr_core_objects%found then
395 hr_utility.set_location(' Leaving:'||l_proc, 15);
396 close csr_core_objects;
397 raise l_error;
398 end if;
399 close csr_core_objects;
400 --
401 open csr_ff_usages;
402 fetch csr_ff_usages into l_exists;
403 if csr_ff_usages%found then
404 hr_utility.set_location(' Leaving:'||l_proc, 18);
405 close csr_ff_usages;
406 raise l_error;
407 end if;
408 close csr_ff_usages;
409 --
410 hr_utility.set_location(' Leaving:'||l_proc, 50);
411 exception
412 when l_error then
413 fnd_message.set_name('PAY', 'PAY_50103_SF_INVALID_DELETE');
414 fnd_message.raise_error;
415 when others then
416 hr_utility.set_location(' Leaving:'||l_proc, 60);
417 raise;
418 End chk_delete;
419 -- ----------------------------------------------------------------------------
420 -- |---------------------------< insert_validate >----------------------------|
421 -- ----------------------------------------------------------------------------
422 Procedure insert_validate
423 (p_effective_date in date
424 ,p_rec in pay_sf_shd.g_rec_type
425 ) is
426 --
427 l_proc varchar2(72) := g_package||'insert_validate';
428 --
429 Begin
430 hr_utility.set_location('Entering:'||l_proc, 5);
431 --
432 -- Call all supporting business operations
433 --
434 chk_busgrp_legcode
435 (p_business_group_id => p_rec.business_group_id
436 ,p_legislation_code => p_rec.legislation_code
437 );
438 --
439 chk_template_type
440 (p_effective_date => p_effective_date
441 ,p_legislation_code => p_rec.legislation_code
442 ,p_business_group_id => p_rec.business_group_id
443 ,p_template_type => p_rec.template_type
444 );
445 --
446 chk_formula_name
447 (p_formula_name => p_rec.formula_name
448 ,p_template_type => p_rec.template_type
449 ,p_legislation_code => p_rec.legislation_code
450 ,p_business_group_id => p_rec.business_group_id
451 );
452 --
453 hr_utility.set_location(' Leaving:'||l_proc, 10);
454 End insert_validate;
455 --
456 -- ----------------------------------------------------------------------------
457 -- |---------------------------< update_validate >----------------------------|
458 -- ----------------------------------------------------------------------------
459 Procedure update_validate
460 (p_effective_date in date
461 ,p_rec in pay_sf_shd.g_rec_type
462 ) 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 hr_utility.set_location(' Leaving:'||l_proc, 10);
474 End update_validate;
475 --
476 -- ----------------------------------------------------------------------------
477 -- |---------------------------< delete_validate >----------------------------|
478 -- ----------------------------------------------------------------------------
479 Procedure delete_validate(p_rec in pay_sf_shd.g_rec_type) is
480 --
481 l_proc varchar2(72) := g_package||'delete_validate';
482 --
483 Begin
484 hr_utility.set_location('Entering:'||l_proc, 5);
485 --
486 -- Call all supporting business operations
487 --
488 chk_delete(p_rec.formula_id);
489 --
490 hr_utility.set_location(' Leaving:'||l_proc, 10);
491 End delete_validate;
492 --
493 end pay_sf_bus;