4 -- ----------------------------------------------------------------------------
1 Package Body hxc_dru_bus as
2 /* $Header: hxcdrurhi.pkb 120.2 2005/09/23 08:07:21 sechandr noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_dru_bus.'; -- Global package name
9 g_debug boolean :=hr_utility.debug_enabled;
10 --
11 -- The following two global variables are only to be
12 -- used by the return_legislation_code function.
13 --
14 g_legislation_code varchar2(150) default null;
15 g_data_app_rule_usage_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_data_app_rule_usage_id in number
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- hxc_data_app_rule_usages and PER_BUSINESS_GROUPS
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31 -- Remove this comment when the edit has been completed.
32 cursor csr_sec_grp is
33 select pbg.security_group_id
34 from per_business_groups pbg
35 , hxc_data_app_rule_usages dru
36 -- , EDIT_HERE table_name(s) 333
37 where dru.data_app_rule_usage_id = p_data_app_rule_usage_id;
38 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
39 --
40 -- Declare local variables
41 --
42 l_security_group_id number;
43 l_proc varchar2(72);
44 --
45 begin
46 --
47 g_debug:=hr_utility.debug_enabled;
48 if g_debug then
49 l_proc := g_package||'set_security_group_id';
50 hr_utility.set_location('Entering:'|| l_proc, 10);
51 end if;--
52 -- Ensure that all the mandatory parameter are not null
53 --
54 hr_api.mandatory_arg_error
55 (p_api_name => l_proc
56 ,p_argument => 'data_app_rule_usage_id'
57 ,p_argument_value => p_data_app_rule_usage_id
58 );
59 --
60 open csr_sec_grp;
61 fetch csr_sec_grp into l_security_group_id;
62 --
63 if csr_sec_grp%notfound then
64 --
65 close csr_sec_grp;
66 --
67 -- The primary key is invalid therefore we must error
68 --
69 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
70 fnd_message.raise_error;
71 --
72 end if;
73 close csr_sec_grp;
77 hr_api.set_security_group_id
74 --
75 -- Set the security_group_id in CLIENT_INFO
76 --
78 (p_security_group_id => l_security_group_id
79 );
80 --
81 if g_debug then
82 hr_utility.set_location(' Leaving:'|| l_proc, 20);
83 end if;
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_data_app_rule_usage_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 -- EDIT_HERE In the following cursor statement add join(s) between
99 -- hxc_data_app_rule_usages and PER_BUSINESS_GROUPS
100 -- so that the legislation_code for
101 -- the current business group context can be derived.
102 -- Remove this comment when the edit has been completed.
103 cursor csr_leg_code is
104 select pbg.legislation_code
105 from per_business_groups pbg
106 , hxc_data_app_rule_usages dru
107 -- , EDIT_HERE table_name(s) 333
108 where dru.data_app_rule_usage_id = p_data_app_rule_usage_id;
109 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
110 --
111 -- Declare local variables
112 --
113 l_legislation_code varchar2(150);
114 l_proc varchar2(72);
115 --
116 Begin
117 --
118 g_debug:=hr_utility.debug_enabled;
119 if g_debug then
120 l_proc := g_package||'return_legislation_code';
121 hr_utility.set_location('Entering:'|| l_proc, 10);
122 end if;
123 --
124 -- Ensure that all the mandatory parameter are not null
125 --
126 hr_api.mandatory_arg_error
127 (p_api_name => l_proc
128 ,p_argument => 'data_app_rule_usage_id'
129 ,p_argument_value => p_data_app_rule_usage_id
130 );
131 --
132 if ( nvl(hxc_dru_bus.g_data_app_rule_usage_id, hr_api.g_number)
133 = p_data_app_rule_usage_id) then
134 --
135 -- The legislation code has already been found with a previous
136 -- call to this function. Just return the value in the global
137 -- variable.
138 --
139 l_legislation_code := hxc_dru_bus.g_legislation_code;
140 if g_debug then
141 hr_utility.set_location(l_proc, 20);
142 end if;
143 else
144 --
145 -- The ID is different to the last call to this function
146 -- or this is the first call to this function.
147 --
148 open csr_leg_code;
149 fetch csr_leg_code into l_legislation_code;
150 --
151 if csr_leg_code%notfound then
152 --
153 -- The primary key is invalid therefore we must error
154 --
155 close csr_leg_code;
156 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
157 fnd_message.raise_error;
158 end if;
159 if g_debug then
160 hr_utility.set_location(l_proc,30);
161 end if;
162 --
163 -- Set the global variables so the values are
164 -- available for the next call to this function.
165 --
166 close csr_leg_code;
167 hxc_dru_bus.g_data_app_rule_usage_id := p_data_app_rule_usage_id;
168 hxc_dru_bus.g_legislation_code := l_legislation_code;
169 end if;
170 if g_debug then
171 hr_utility.set_location(' Leaving:'|| l_proc, 40);
172 end if;
173 return l_legislation_code;
174 end return_legislation_code;
175 --
176 -- ----------------------------------------------------------------------------
177 -- |-----------------------< chk_non_updateable_args >------------------------|
178 -- ----------------------------------------------------------------------------
179 -- {Start Of Comments}
180 --
181 -- Description:
182 -- This procedure is used to ensure that non updateable attributes have
183 -- not been updated. If an attribute has been updated an error is generated.
184 --
185 -- Pre Conditions:
186 -- g_old_rec has been populated with details of the values currently in
187 -- the database.
188 --
189 -- In Arguments:
190 -- p_rec has been populated with the updated values the user would like the
191 -- record set to.
192 --
193 -- Post Success:
194 -- Processing continues if all the non updateable attributes have not
195 -- changed.
196 --
197 -- Post Failure:
198 -- An application error is raised if any of the non updatable attributes
199 -- have been altered.
200 --
201 -- {End Of Comments}
202 -- ----------------------------------------------------------------------------
203 Procedure chk_non_updateable_args
204 (p_effective_date in date
205 ,p_rec in hxc_dru_shd.g_rec_type
206 ) IS
207 --
208 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
209 l_error EXCEPTION;
210 l_argument varchar2(30);
211 --
212 Begin
213 --
214 -- Only proceed with the validation if a row exists for the current
215 -- record in the HR Schema.
216 --
217 IF NOT hxc_dru_shd.api_updating
218 (p_data_app_rule_usage_id => p_rec.data_app_rule_usage_id
219 ,p_object_version_number => p_rec.object_version_number
220 ) THEN
221 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
222 fnd_message.set_token('PROCEDURE ', l_proc);
223 fnd_message.set_token('STEP ', '5');
224 fnd_message.raise_error;
225 END IF;
226 --
227 -- EDIT_HERE: Add checks to ensure non-updateable args have
228 -- not been updated.
232 hr_api.argument_changed_error
229 --
230 EXCEPTION
231 WHEN l_error THEN
233 (p_api_name => l_proc
234 ,p_argument => l_argument);
235 WHEN OTHERS THEN
236 RAISE;
237 End chk_non_updateable_args;
238 --
239 -- ----------------------------------------------------------------------------
240 -- |-----------------------< chk_dependency_rule >----------------------------|
241 -- ----------------------------------------------------------------------------
242 -- {Start Of Comments}
243 --
244 -- Description:
245 -- This procedure ensures that no two rules are created using same time entry
246 -- rule and application
247 --
248 -- Pre Conditions:
249 -- None
250 --
251 -- In Arguments:
252 -- approval_style_id
253 -- time_entry_rule_id
254 -- time_recipient_id
255 -- object_version_number
256 --
257 -- Post Success:
258 -- Processing continues if a rule does not exist for the application
259 --
260 -- Post Failure:
261 -- An application error is raised if a rule already exists for an application
262 --
263 -- {End Of Comments}
264 -- ----------------------------------------------------------------------------
265 Procedure chk_dependency_rule
266 (
267 p_approval_style_id in hxc_data_app_rule_usages.approval_style_id%TYPE,
268 p_time_entry_rule_id in hxc_data_app_rule_usages.time_entry_rule_id%TYPE,
269 p_time_recipient_id in hxc_data_app_rule_usages.time_recipient_id%TYPE,
270 p_object_version_number in hxc_data_app_rule_usages.object_version_number%TYPE
271 ) IS
272 --
273 l_proc varchar2(72);
274 --
275 -- cursor to check that no two rules are created using same time entry rule
276 -- and application
277 --
278 CURSOR csr_chk_dependency_rule IS
279 SELECT 'error'
280 FROM sys.dual
281 WHERE EXISTS (
282 SELECT 'x'
283 FROM hxc_data_app_rule_usages dru
284 WHERE dru.approval_style_id = p_approval_style_id
285 AND dru.time_recipient_id = p_time_recipient_id
286 AND dru.time_entry_rule_id = p_time_entry_rule_id
287 AND dru.object_version_number <> NVL(p_object_version_number, -1) );
288 --
289 l_error varchar2(5) := NULL;
290 --
291 BEGIN
292 if g_debug then
293 l_proc := g_package||'chk_dependency_rule';
294 hr_utility.set_location('Entering:'||l_proc, 5);
295 end if;
296 --
297 -- Raise an error if the rule is not unique
298 --
299 OPEN csr_chk_dependency_rule;
300 FETCH csr_chk_dependency_rule INTO l_error;
301 CLOSE csr_chk_dependency_rule;
302 --
303 IF l_error IS NOT NULL
304 THEN
305 --
306 hr_utility.set_message(809, 'HXC_0189_DRU_APL_RULE_EXISTS');
307 hr_utility.raise_error;
308 --
309 END IF;
310 --
311 if g_debug then
312 hr_utility.set_location('Leaving:'||l_proc, 10);
313 end if;
314 --
315 END chk_dependency_rule;
316 --
317 -- ----------------------------------------------------------------------------
318 -- |---------------------------< insert_validate >----------------------------|
319 -- ----------------------------------------------------------------------------
320 Procedure insert_validate
321 (p_effective_date in date
322 ,p_rec in hxc_dru_shd.g_rec_type
323 ) is
324 --
325 l_proc varchar2(72);
326 --
327 Begin
328 g_debug:=hr_utility.debug_enabled;
329 if g_debug then
330 l_proc := g_package||'insert_validate';
331 hr_utility.set_location('Entering:'||l_proc, 5);
332 end if;
333 --
334 -- Call all supporting business operations
335 --
336 if g_debug then
337 hr_utility.set_location('Processing:'||l_proc, 10);
338 end if;
339 --
340 chk_dependency_rule (p_approval_style_id => p_rec.approval_style_id,
341 p_time_entry_rule_id => p_rec.time_entry_rule_id,
342 p_time_recipient_id => p_rec.time_recipient_id,
343 p_object_version_number => p_rec.object_version_number );
344 --
345 if g_debug then
346 hr_utility.set_location(' Leaving:'||l_proc, 10);
347 end if;
348 --
349 -- EDIT_HERE: As this table does not have a mandatory business_group_id
350 -- column, ensure client_info is populated by calling a suitable
351 -- ???_???_bus.set_security_group_id procedure, or add one of the following
352 -- comments:
353 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
354 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
355 --
356 --
357 if g_debug then
358 hr_utility.set_location(' Leaving:'||l_proc, 10);
359 end if;
360 End insert_validate;
361 --
362 -- ----------------------------------------------------------------------------
363 -- |---------------------------< update_validate >----------------------------|
364 -- ----------------------------------------------------------------------------
365 Procedure update_validate
366 (p_effective_date in date
367 ,p_rec in hxc_dru_shd.g_rec_type
368 ) is
369 --
370 l_proc varchar2(72);
371 --
372 Begin
373 g_debug:=hr_utility.debug_enabled;
374 if g_debug then
375 l_proc := g_package||'update_validate';
376 hr_utility.set_location('Entering:'||l_proc, 5);
377 end if;
378 --
379 -- Call all supporting business operations
380 --
381 if g_debug then
382 hr_utility.set_location('Processing:'||l_proc, 10);
383 end if;
384 --
385 chk_dependency_rule (p_approval_style_id => p_rec.approval_style_id,
389 --
386 p_time_entry_rule_id => p_rec.time_entry_rule_id,
387 p_time_recipient_id => p_rec.time_recipient_id,
388 p_object_version_number => p_rec.object_version_number );
390 if g_debug then
391 hr_utility.set_location(' Leaving:'||l_proc, 10);
392 end if;
393 --
394 -- EDIT_HERE: As this table does not have a mandatory business_group_id
395 -- column, ensure client_info is populated by calling a suitable
396 -- ???_???_bus.set_security_group_id procedure, or add one of the following
397 -- comments:
398 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
399 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
400 --
401 chk_non_updateable_args
402 (p_effective_date => p_effective_date
403 ,p_rec => p_rec
404 );
405 --
406 --
407 if g_debug then
408 hr_utility.set_location(' Leaving:'||l_proc, 10);
409 end if;
410 End update_validate;
411 --
412 -- ----------------------------------------------------------------------------
413 -- |---------------------------< delete_validate >----------------------------|
414 -- ----------------------------------------------------------------------------
415 Procedure delete_validate
416 (p_rec in hxc_dru_shd.g_rec_type
417 ) is
418 --
419 l_proc varchar2(72);
420 --
421 Begin
422 g_debug:=hr_utility.debug_enabled;
423 if g_debug then
424 l_proc := g_package||'delete_validate';
425 hr_utility.set_location('Entering:'||l_proc, 5);
426 end if;
427 --
428 -- Call all supporting business operations
429 --
430 if g_debug then
431 hr_utility.set_location(' Leaving:'||l_proc, 10);
432 end if;
433 End delete_validate;
434 --
435 end hxc_dru_bus;