[Home] [Help]
PACKAGE BODY: APPS.HR_TIP_BUS
Source
1 Package Body hr_tip_bus as
2 /* $Header: hrtiprhi.pkb 115.4 2002/12/03 12:59:50 raranjan noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_tip_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_template_item_tab_page_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_template_item_tab_page_id in number
22 ) is
23 --
24 l_proc varchar2(72) := g_package||'set_security_group_id';
25 --
26 begin
27 --
28 hr_utility.set_location('Entering:'|| l_proc, 10);
29 --
30 -- No business group context. Security group is not applicable.
31 --
32 null;
33 --
34 hr_utility.set_location(' Leaving:'|| l_proc, 20);
35 --
36 end set_security_group_id;
37 --
38 -- ---------------------------------------------------------------------------
39 -- |---------------------< return_legislation_code >-------------------------|
40 -- ---------------------------------------------------------------------------
41 --
42 Function return_legislation_code
43 (p_template_item_tab_page_id in number
44 )
45 Return Varchar2 Is
46 --
47 -- Declare cursor
48 --
49 cursor csr_leg_code is
50 select tmp.legislation_code
51 from hr_form_templates_b tmp
52 ,hr_template_items_b tim
53 ,hr_template_item_tab_pages tip
54 where tmp.form_template_id = tim.form_template_id
55 and tim.template_item_id = tip.template_item_id
56 and tip.template_item_tab_page_id = p_template_item_tab_page_id;
57 --
58 -- Declare local variables
59 --
60 l_legislation_code varchar2(150);
61 l_proc varchar2(72) := g_package||'return_legislation_code';
62 --
63 Begin
64 --
65 hr_utility.set_location('Entering:'|| l_proc, 10);
66 --
67 -- Ensure that all the mandatory parameter are not null
68 --
69 hr_api.mandatory_arg_error
70 (p_api_name => l_proc
71 ,p_argument => 'template_item_tab_page_id'
72 ,p_argument_value => p_template_item_tab_page_id
73 );
74 --
75 if ( nvl(hr_tip_bus.g_template_item_tab_page_id, hr_api.g_number)
76 = p_template_item_tab_page_id) then
77 --
78 -- The legislation code has already been found with a previous
79 -- call to this function. Just return the value in the global
80 -- variable.
81 --
82 l_legislation_code := hr_tip_bus.g_legislation_code;
83 hr_utility.set_location(l_proc, 20);
84 else
85 --
86 -- The ID is different to the last call to this function
87 -- or this is the first call to this function.
88 --
89 open csr_leg_code;
90 fetch csr_leg_code into l_legislation_code;
91 --
92 if csr_leg_code%notfound then
93 --
94 -- The primary key is invalid therefore we must error
95 --
96 close csr_leg_code;
97 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
98 fnd_message.raise_error;
99 end if;
100 hr_utility.set_location(l_proc,30);
101 --
102 -- Set the global variables so the values are
103 -- available for the next call to this function.
104 --
105 close csr_leg_code;
106 hr_tip_bus.g_template_item_tab_page_id := p_template_item_tab_page_id;
107 hr_tip_bus.g_legislation_code := l_legislation_code;
108 end if;
109 hr_utility.set_location(' Leaving:'|| l_proc, 40);
110 return l_legislation_code;
111 end return_legislation_code;
112 --
113 -- ----------------------------------------------------------------------------
114 -- |-----------------------< chk_non_updateable_args >------------------------|
115 -- ----------------------------------------------------------------------------
116 -- {Start Of Comments}
117 --
118 -- Description:
119 -- This procedure is used to ensure that non updateable attributes have
120 -- not been updated. If an attribute has been updated an error is generated.
121 --
122 -- Pre Conditions:
123 -- g_old_rec has been populated with details of the values currently in
124 -- the database.
125 --
126 -- In Arguments:
127 -- p_rec has been populated with the updated values the user would like the
128 -- record set to.
129 --
130 -- Post Success:
131 -- Processing continues if all the non updateable attributes have not
132 -- changed.
133 --
134 -- Post Failure:
135 -- An application error is raised if any of the non updatable attributes
136 -- have been altered.
137 --
138 -- {End Of Comments}
139 -- ----------------------------------------------------------------------------
140 Procedure chk_non_updateable_args
141 (p_rec in hr_tip_shd.g_rec_type
142 ) IS
143 --
144 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
145 l_error EXCEPTION;
146 l_argument varchar2(30);
147 --
148 Begin
149 --
150 -- Only proceed with the validation if a row exists for the current
151 -- record in the HR Schema.
152 --
153 IF NOT hr_tip_shd.api_updating
154 (p_template_item_tab_page_id => p_rec.template_item_tab_page_id
155 ,p_object_version_number => p_rec.object_version_number
156 ) THEN
157 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
158 fnd_message.set_token('PROCEDURE ', l_proc);
159 fnd_message.set_token('STEP ', '5');
160 fnd_message.raise_error;
161 END IF;
162 --
163 IF (nvl(p_rec.template_item_id,hr_api.g_number) <>
164 nvl(hr_tip_shd.g_old_rec.template_item_id,hr_api.g_number)
165 ) THEN
166 l_argument := 'template_item_id';
167 RAISE l_error;
168 END IF;
169 --
170 IF (nvl(p_rec.template_tab_page_id,hr_api.g_number) <>
171 nvl(hr_tip_shd.g_old_rec.template_tab_page_id,hr_api.g_number)
172 ) THEN
173 l_argument := 'template_tab_page_id';
174 RAISE l_error;
175 END IF;
176 --
177 EXCEPTION
178 WHEN l_error THEN
179 hr_api.argument_changed_error
180 (p_api_name => l_proc
181 ,p_argument => l_argument);
182 WHEN OTHERS THEN
183 RAISE;
184 End chk_non_updateable_args;
185 --
186 -- ----------------------------------------------------------------------------
187 -- |-------------------------< chk_template_item_id >-------------------------|
188 -- ----------------------------------------------------------------------------
189 Procedure chk_template_item_id
190 (p_template_item_tab_page_id in number
191 ,p_object_version_number in number
192 ,p_template_item_id in number
193 ) is
194 --
195 l_proc varchar2(72) := g_package || 'chk_template_item_id';
196 l_api_updating boolean;
197 --
198 Begin
199 hr_utility.set_location('Entering:'||l_proc, 10);
200 --
201 -- Check value has been passed
202 --
203 hr_api.mandatory_arg_error
204 (p_api_name => l_proc
205 ,p_argument => 'template_item_id'
206 ,p_argument_value => p_template_item_id
207 );
208 --
209 hr_utility.set_location('Leaving:'||l_proc, 100);
210 End chk_template_item_id;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |-----------------------< chk_template_tab_page_id >-----------------------|
214 -- ----------------------------------------------------------------------------
215 Procedure chk_template_tab_page_id
216 (p_template_item_tab_page_id in number
217 ,p_object_version_number in number
218 ,p_template_tab_page_id in number
219 ) is
220 --
221 l_proc varchar2(72) := g_package || 'chk_template_tab_page_id';
222 l_api_updating boolean;
223 --
224 Begin
225 hr_utility.set_location('Entering:'||l_proc, 10);
226 --
227 -- Check value has been passed
228 --
229 hr_api.mandatory_arg_error
230 (p_api_name => l_proc
231 ,p_argument => 'template_tab_page_id'
232 ,p_argument_value => p_template_tab_page_id
233 );
234 --
235 hr_utility.set_location('Leaving:'||l_proc, 100);
236 End chk_template_tab_page_id;
237 --
238 -- ----------------------------------------------------------------------------
239 -- |-------------------------< chk_item_and_tab_page >------------------------|
240 -- ----------------------------------------------------------------------------
241 Procedure chk_item_and_tab_page
242 (p_template_item_tab_page_id in number
243 ,p_object_version_number in number
244 ,p_template_item_id in number
245 ,p_template_tab_page_id in number
246 ) is
247 --
248 cursor csr_template_item is
249 select tim.form_template_id
250 from hr_template_items_b tim
251 where tim.template_item_id = p_template_item_id;
252 --
253 cursor csr_template_tab_page is
254 select twn.form_template_id
255 from hr_template_windows_b twn
256 ,hr_template_canvases_b tcn
257 ,hr_template_tab_pages_b ttp
258 where twn.template_window_id = tcn.template_window_id
259 and tcn.template_canvas_id = ttp.template_canvas_id
260 and ttp.template_tab_page_id = p_template_tab_page_id;
261 --
262 cursor csr_tab_stacked_canvas is
263 select null
264 from hr_template_tab_pages_b ttp
265 ,hr_form_tab_stacked_canvases fs2
266 ,hr_form_tab_stacked_canvases fs1
267 ,hr_form_items_b fim
268 ,hr_template_items_b tim
269 where p_template_tab_page_id = ttp.template_tab_page_id
270 and ttp.form_tab_page_id = fs2.form_tab_page_id
271 and fs2.form_canvas_id = fs1.form_canvas_id
272 and fs1.form_tab_page_id = fim.form_tab_page_id
273 and fim.form_item_id = tim.form_item_id
274 and tim.template_item_id = p_template_item_id;
275 --
276 l_proc varchar2(72) := g_package || 'chk_item_and_tab_page';
277 l_api_updating boolean;
278 l_item_template_id number;
279 l_tab_page_template_id number;
280 l_dummy varchar2(1);
281 --
282 Begin
283 hr_utility.set_location('Entering:'||l_proc, 10);
284 --
285 l_api_updating := hr_tip_shd.api_updating
286 (p_template_item_tab_page_id => p_template_item_tab_page_id
287 ,p_object_version_number => p_object_version_number
288 );
289 hr_utility.set_location(l_proc,20);
290 --
291 -- Only proceed with SQL validation if absolutely necessary
292 --
293 if ( ( l_api_updating
294 and ( nvl(hr_tip_shd.g_old_rec.template_item_id,hr_api.g_number) <>
295 nvl(p_template_item_id,hr_api.g_number)
296 or nvl(hr_tip_shd.g_old_rec.template_tab_page_id,hr_api.g_number) <>
297 nvl(p_template_tab_page_id,hr_api.g_number)))
298 or (NOT l_api_updating)) then
299 --
300 hr_utility.set_location(l_proc,30);
301 --
302 open csr_template_item;
303 fetch csr_template_item into l_item_template_id;
304 if csr_template_item%notfound then
305 close csr_template_item;
306 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
307 fnd_message.set_token('PROCEDURE', l_proc);
308 fnd_message.set_token('STEP','10');
309 fnd_message.raise_error;
310 end if;
311 close csr_template_item;
312 --
313 open csr_template_tab_page;
314 fetch csr_template_tab_page into l_tab_page_template_id;
315 if csr_template_tab_page%notfound then
316 close csr_template_tab_page;
317 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
318 fnd_message.set_token('PROCEDURE', l_proc);
319 fnd_message.set_token('STEP','20');
320 fnd_message.raise_error;
321 end if;
322 close csr_template_tab_page;
323 --
324 hr_utility.set_location(l_proc,40);
325 --
326 -- Check item and tab page reference the same template
327 --
328 if nvl(l_item_template_id,hr_api.g_number) <>
329 nvl(l_tab_page_template_id,hr_api.g_number) then
330 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
331 fnd_message.set_token('PROCEDURE', l_proc);
332 fnd_message.set_token('STEP','30');
333 fnd_message.raise_error;
334 end if;
335 --
336 hr_utility.set_location(l_proc,50);
337 --
338 -- Check item and tab page reference the same tab stacked canvas
339 --
340 open csr_tab_stacked_canvas;
341 fetch csr_tab_stacked_canvas into l_dummy;
342 if csr_tab_stacked_canvas%notfound then
343 close csr_tab_stacked_canvas;
344 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
345 fnd_message.set_token('PROCEDURE', l_proc);
346 fnd_message.set_token('STEP','40');
347 fnd_message.raise_error;
348 end if;
349 close csr_tab_stacked_canvas;
350 --
351 end if;
352 --
353 hr_utility.set_location('Leaving:'||l_proc, 100);
354 End chk_item_and_tab_page;
355 --
356 -- ----------------------------------------------------------------------------
357 -- |------------------------------< chk_delete >------------------------------|
358 -- ----------------------------------------------------------------------------
359 Procedure chk_delete
360 (p_rec in hr_tip_shd.g_rec_type
361 ) is
362 --
363 l_proc varchar2(72) := g_package||'chk_delete';
364 --
365 Begin
366 hr_utility.set_location('Entering:'||l_proc, 5);
367 --
368 -- No additional validation required
369 --
370 null;
371 --
372 hr_utility.set_location(' Leaving:'||l_proc, 10);
373 End chk_delete;
374 --
375 -- ----------------------------------------------------------------------------
376 -- |---------------------------< insert_validate >----------------------------|
377 -- ----------------------------------------------------------------------------
378 Procedure insert_validate
379 (p_rec in hr_tip_shd.g_rec_type
380 ) is
381 --
382 l_proc varchar2(72) := g_package||'insert_validate';
383 --
384 Begin
385 hr_utility.set_location('Entering:'||l_proc, 5);
386 --
387 -- Call all supporting business operations
388 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
389 --
390 chk_template_item_id
391 (p_template_item_tab_page_id => p_rec.template_item_tab_page_id
392 ,p_object_version_number => p_rec.object_version_number
393 ,p_template_item_id => p_rec.template_item_id
394 );
395 --
396 chk_template_tab_page_id
397 (p_template_item_tab_page_id => p_rec.template_item_tab_page_id
398 ,p_object_version_number => p_rec.object_version_number
399 ,p_template_tab_page_id => p_rec.template_tab_page_id
400 );
401 --
402 chk_item_and_tab_page
403 (p_template_item_tab_page_id => p_rec.template_item_tab_page_id
404 ,p_object_version_number => p_rec.object_version_number
405 ,p_template_item_id => p_rec.template_item_id
406 ,p_template_tab_page_id => p_rec.template_tab_page_id
407 );
408 --
409 hr_utility.set_location(' Leaving:'||l_proc, 10);
410 End insert_validate;
411 --
412 -- ----------------------------------------------------------------------------
413 -- |---------------------------< update_validate >----------------------------|
414 -- ----------------------------------------------------------------------------
415 Procedure update_validate
416 (p_rec in hr_tip_shd.g_rec_type
417 ) is
418 --
419 l_proc varchar2(72) := g_package||'update_validate';
420 --
421 Begin
422 hr_utility.set_location('Entering:'||l_proc, 5);
423 --
424 -- Call all supporting business operations
425 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
426 --
427 chk_non_updateable_args
428 (p_rec => p_rec
429 );
430 --
431 chk_template_item_id
432 (p_template_item_tab_page_id => p_rec.template_item_tab_page_id
433 ,p_object_version_number => p_rec.object_version_number
434 ,p_template_item_id => p_rec.template_item_id
435 );
436 --
437 chk_template_tab_page_id
438 (p_template_item_tab_page_id => p_rec.template_item_tab_page_id
439 ,p_object_version_number => p_rec.object_version_number
440 ,p_template_tab_page_id => p_rec.template_tab_page_id
441 );
442 --
443 chk_item_and_tab_page
444 (p_template_item_tab_page_id => p_rec.template_item_tab_page_id
445 ,p_object_version_number => p_rec.object_version_number
446 ,p_template_item_id => p_rec.template_item_id
447 ,p_template_tab_page_id => p_rec.template_tab_page_id
448 );
449 --
450 hr_utility.set_location(' Leaving:'||l_proc, 10);
451 End update_validate;
452 --
453 -- ----------------------------------------------------------------------------
454 -- |---------------------------< delete_validate >----------------------------|
455 -- ----------------------------------------------------------------------------
456 Procedure delete_validate
457 (p_rec in hr_tip_shd.g_rec_type
458 ) is
459 --
460 l_proc varchar2(72) := g_package||'delete_validate';
461 --
462 Begin
463 hr_utility.set_location('Entering:'||l_proc, 5);
464 --
465 -- Call all supporting business operations
466 --
467 chk_delete
468 (p_rec => p_rec
469 );
470 --
471 hr_utility.set_location(' Leaving:'||l_proc, 10);
472 End delete_validate;
473 --
474 end hr_tip_bus;