4 -- ----------------------------------------------------------------------------
1 Package Body hxc_htr_bus as
2 /* $Header: hxchtrrhi.pkb 120.2 2005/09/23 07:45:11 nissharm noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_htr_bus.'; -- Global package name
9
10 g_debug boolean := hr_utility.debug_enabled;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |-----------------------< chk_name >---------------------------------------|
14 -- ----------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 --
19 -- SEE DESCRIPTION IN PACKAGE HEADER
20 --
21 -- Pre Conditions:
22 -- None
23 --
24 -- In Arguments:
25 -- name
26 -- time_recipient_id
27 --
28 -- Post Success:
29 -- Processing continues if the name business rules have not been violated
30 --
31 -- Post Failure:
32 -- An application error is raised if the name is not valid
33 --
34 -- {End Of Comments}
35 -- ----------------------------------------------------------------------------
36 Procedure chk_name
37 (
38 p_name in hxc_time_recipients.name%TYPE
39 ,p_time_recipient_id in hxc_time_recipients.time_recipient_id%TYPE
40 ) IS
41 --
42 l_proc varchar2(72);
43 --
44 -- cursor to check name is unique
45 --
46 CURSOR csr_chk_name IS
47 SELECT 'error'
48 FROM sys.dual
49 WHERE EXISTS (
50 SELECT 'x'
51 FROM hxc_time_recipients tr
52 WHERE tr.name = p_name
53 AND ( tr.time_recipient_id <> p_time_recipient_id
54 OR p_time_recipient_id IS NULL) );
55 --
56 l_dup_name varchar2(5) := NULL;
57 --
58 BEGIN
59 g_debug := hr_utility.debug_enabled;
60
61 if g_debug then
62 l_proc := g_package||'chk_name';
63 hr_utility.set_location('Entering:'||l_proc, 5);
64 end if;
65 --
66 -- check that the name has been entered
67 --
68 IF p_name IS NULL
69 THEN
70 --
71 hr_utility.set_message(809, 'HXC_0065_HTR_NAME_MAND');
72 hr_utility.raise_error;
73 --
74 END IF;
75 if g_debug then
76 hr_utility.set_location('Processing:'||l_proc, 10);
77 end if;
78 --
79 -- check that the name is unique
80 --
81 OPEN csr_chk_name;
82 FETCH csr_chk_name INTO l_dup_name;
83 CLOSE csr_chk_name;
84 --
85 IF l_dup_name IS NOT NULL
86 THEN
87 --
88 hr_utility.set_message(809, 'HXC_0066_HTR_DUP_NAME');
89 hr_utility.raise_error;
90 --
91 END IF;
92 --
93 if g_debug then
94 hr_utility.set_location('Leaving:'||l_proc, 20);
95 end if;
96 --
97 END chk_name;
98 --
99 -- ----------------------------------------------------------------------------
100 -- |-----------------------< chk_application_id >-----------------------------|
101 -- ----------------------------------------------------------------------------
102 -- {Start Of Comments}
103 --
104 -- Description:
105 --
106 -- SEE DESCRIPTION IN PACKAGE HEADER
107 --
108 -- Pre Conditions:
109 -- None
110 --
111 -- In Arguments:
112 -- application_id
113 --
114 -- Post Success:
115 -- Processing continues if the name business rules have not been violated
116 --
117 -- Post Failure:
118 -- An application error is raised if the name is not valid
119 --
120 -- {End Of Comments}
121 -- ----------------------------------------------------------------------------
122 Procedure chk_application_id
123 (
124 p_application_id in hxc_time_recipients.application_id%TYPE
125 ) IS
126
127 l_invalid_id VARCHAR2(1) := 'Y';
128
129 CURSOR csr_chk_app_id IS
130 SELECT 'N'
134 BEGIN
131 FROM fnd_application
132 WHERE application_id = p_application_id;
133
135
136 OPEN csr_chk_app_id;
137 FETCH csr_chk_app_id INTO l_invalid_id;
138 CLOSE csr_chk_app_id;
139
140 IF ( l_invalid_id = 'Y' )
141 THEN
142 hr_utility.set_message(809, 'HXC_0068_HTR_APP_ID_INVLD');
143 hr_utility.raise_error;
144 END IF;
145 --
146 END chk_application_id;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_delete >-------------------------------------|
150 -- ----------------------------------------------------------------------------
151 --
152 -- Description:
153 --
154 -- SEE DESCRIPTION IN PACKAGE HEADER
155 --
156 -- Pre Conditions:
157 -- None
158 --
159 -- In Arguments:
160 -- time_recipient_id
161 --
162 -- Post Success:
163 -- Processing continues if the name is not being referenced
164 --
165 -- Post Failure:
166 -- An application error is raised if the rule is being used.
167 --
168 -- {End Of Comments}
169 -- ----------------------------------------------------------------------------
170 Procedure chk_delete
171 (
172 p_time_recipient_id in hxc_time_recipients.time_recipient_id%TYPE
173 ) IS
174
175 l_used_elsewhere VARCHAR2(1) := 'N';
176
177 CURSOR csr_chk_ret IS
178 SELECT 'Y'
179 FROM sys.dual
180 WHERE EXISTS (
181 SELECT 'x'
182 FROM hxc_retrieval_processes ret
183 WHERE ret.time_recipient_id = p_time_recipient_id);
184
185 CURSOR csr_chk_rrc IS
186 SELECT 'Y'
187 FROM sys.dual
188 WHERE EXISTS (
189 SELECT 'x'
190 FROM hxc_retrieval_rule_comps rrc
191 WHERE rrc.time_recipient_id = p_time_recipient_id);
192
193 CURSOR csr_chk_apc IS
194 SELECT 'Y'
195 FROM sys.dual
196 WHERE EXISTS (
197 SELECT 'x'
198 FROM hxc_approval_period_comps apc
199 WHERE apc.time_recipient_id = p_time_recipient_id);
200
201 CURSOR csr_chk_daru IS
202 SELECT 'Y'
203 FROM sys.dual
204 WHERE EXISTS (
205 SELECT 'x'
206 FROM hxc_data_app_rule_usages daru
207 WHERE daru.time_recipient_id = p_time_recipient_id);
208
209 CURSOR csr_chk_ac IS
210 SELECT 'Y'
211 FROM sys.dual
212 WHERE EXISTS (
213 SELECT 'x'
214 FROM hxc_approval_comps ac
215 WHERE ac.time_recipient_id = p_time_recipient_id);
216
217 CURSOR csr_chk_asc IS
218 SELECT 'Y'
219 FROM sys.dual
220 WHERE EXISTS (
221 SELECT 'x'
222 FROM hxc_application_set_comps_v appsc
223 WHERE appsc.time_recipient_id = p_time_recipient_id);
224
225 BEGIN -- chk_delete
226
227 OPEN csr_chk_ret;
228 FETCH csr_chk_ret INTO l_used_elsewhere;
229 CLOSE csr_chk_ret;
230
231 IF ( l_used_elsewhere = 'Y' )
232 THEN
233 hr_utility.set_message(809, 'HXC_0069_HTR_IN_USE');
234 hr_utility.raise_error;
235 END IF;
236
237 OPEN csr_chk_rrc;
238 FETCH csr_chk_rrc INTO l_used_elsewhere;
239 CLOSE csr_chk_rrc;
240
241 IF ( l_used_elsewhere = 'Y' )
242 THEN
243 hr_utility.set_message(809, 'HXC_0069_HTR_IN_USE');
244 hr_utility.raise_error;
245 END IF;
246
247 OPEN csr_chk_apc;
248 FETCH csr_chk_apc INTO l_used_elsewhere;
249 CLOSE csr_chk_apc;
250
251 IF ( l_used_elsewhere = 'Y' )
252 THEN
253 hr_utility.set_message(809, 'HXC_0069_HTR_IN_USE');
254 hr_utility.raise_error;
255 END IF;
256
257 OPEN csr_chk_daru;
258 FETCH csr_chk_daru INTO l_used_elsewhere;
259 CLOSE csr_chk_daru;
260
261 IF ( l_used_elsewhere = 'Y' )
262 THEN
263 hr_utility.set_message(809, 'HXC_0069_HTR_IN_USE');
264 hr_utility.raise_error;
265 END IF;
266
267 OPEN csr_chk_ac;
268 FETCH csr_chk_ac INTO l_used_elsewhere;
269 CLOSE csr_chk_ac;
270
271 IF ( l_used_elsewhere = 'Y' )
272 THEN
273 hr_utility.set_message(809, 'HXC_0069_HTR_IN_USE');
274 hr_utility.raise_error;
275 END IF;
276
277 OPEN csr_chk_asc;
278 FETCH csr_chk_asc INTO l_used_elsewhere;
279 CLOSE csr_chk_asc;
280
281 IF ( l_used_elsewhere = 'Y' )
282 THEN
283 hr_utility.set_message(809, 'HXC_0069_HTR_IN_USE');
284 hr_utility.raise_error;
285 END IF;
286
287 END chk_delete;
288
289 --
290 -- ----------------------------------------------------------------------------
291 -- |-----------------------< chk_non_updateable_args >------------------------|
292 -- ----------------------------------------------------------------------------
293 -- {Start Of Comments}
294 --
295 -- Description:
296 -- This procedure is used to ensure that non updateable attributes have
297 -- not been updated. If an attribute has been updated an error is generated.
298 --
299 -- Pre Conditions:
300 -- g_old_rec has been populated with details of the values currently in
301 -- the database.
302 --
303 -- In Arguments:
304 -- p_rec has been populated with the updated values the user would like the
305 -- record set to.
306 --
307 -- Post Success:
308 -- Processing continues if all the non updateable attributes have not
309 -- changed.
310 --
311 -- Post Failure:
312 -- An application error is raised if any of the non updatable attributes
313 -- have been altered.
314 --
315 -- {End Of Comments}
316 -- ----------------------------------------------------------------------------
320 ) IS
317 Procedure chk_non_updateable_args
318 (p_effective_date in date
319 ,p_rec in hxc_htr_shd.g_rec_type
321 --
322 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
323 l_error EXCEPTION;
324 l_argument varchar2(30);
325 --
326 Begin
327 --
328 -- Only proceed with the validation if a row exists for the current
329 -- record in the HR Schema.
330 --
331 IF NOT hxc_htr_shd.api_updating
332 (p_time_recipient_id => p_rec.time_recipient_id
333 ,p_object_version_number => p_rec.object_version_number
334 ) THEN
335 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
336 fnd_message.set_token('PROCEDURE ', l_proc);
337 fnd_message.set_token('STEP ', '5');
338 fnd_message.raise_error;
339 END IF;
340 --
341 EXCEPTION
342 WHEN l_error THEN
343 hr_api.argument_changed_error
344 (p_api_name => l_proc
345 ,p_argument => l_argument);
346 WHEN OTHERS THEN
347 RAISE;
348 End chk_non_updateable_args;
349 --
350 -- ----------------------------------------------------------------------------
351 -- |---------------------------< insert_validate >----------------------------|
352 -- ----------------------------------------------------------------------------
353 Procedure insert_validate
354 (p_effective_date in date
355 ,p_rec in hxc_htr_shd.g_rec_type
356 ) is
357 --
358 l_proc varchar2(72);
359 --
360 Begin
361 g_debug := hr_utility.debug_enabled;
362
363 if g_debug then
364 l_proc := g_package||'insert_validate';
365 hr_utility.set_location('Entering:'||l_proc, 5);
366 end if;
367 --
368 -- Call all supporting business operations
369 --
370 hxc_htr_bus.chk_name ( p_name => p_rec.name
371 ,p_time_recipient_id => p_rec.time_recipient_id );
372
373 hxc_htr_bus.chk_application_id ( p_application_id => p_rec.application_id );
374 --
375 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS.
376 --
377 --
378 if g_debug then
379 hr_utility.set_location(' Leaving:'||l_proc, 10);
380 end if;
381 End insert_validate;
382 --
383 -- ----------------------------------------------------------------------------
384 -- |---------------------------< update_validate >----------------------------|
385 -- ----------------------------------------------------------------------------
386 Procedure update_validate
387 (p_effective_date in date
388 ,p_rec in hxc_htr_shd.g_rec_type
389 ) is
390 --
391 l_proc varchar2(72);
392 --
393 Begin
394 g_debug := hr_utility.debug_enabled;
395
396 if g_debug then
397 l_proc := g_package||'update_validate';
398 hr_utility.set_location('Entering:'||l_proc, 5);
399 end if;
400 --
401 -- Call all supporting business operations
402 --
403 hxc_htr_bus.chk_name ( p_name => p_rec.name
404 ,p_time_recipient_id => p_rec.time_recipient_id );
405
406 hxc_htr_bus.chk_application_id ( p_application_id => p_rec.application_id );
407 --
408 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS.
409 --
410 chk_non_updateable_args
411 (p_effective_date => p_effective_date
412 ,p_rec => p_rec
413 );
414 --
415 --
416 if g_debug then
417 hr_utility.set_location(' Leaving:'||l_proc, 10);
418 end if;
419 End update_validate;
420 --
421 -- ----------------------------------------------------------------------------
422 -- |---------------------------< delete_validate >----------------------------|
423 -- ----------------------------------------------------------------------------
424 Procedure delete_validate
425 (p_rec in hxc_htr_shd.g_rec_type
426 ) is
427 --
428 l_proc varchar2(72);
429 --
430 Begin
431 g_debug := hr_utility.debug_enabled;
432
433 if g_debug then
434 l_proc := g_package||'delete_validate';
435 hr_utility.set_location('Entering:'||l_proc, 5);
436 end if;
437 --
438 -- Call all supporting business operations
439 --
440 hxc_htr_bus.chk_delete ( p_time_recipient_id => p_rec.time_recipient_id );
441
442 if g_debug then
443 hr_utility.set_location(' Leaving:'||l_proc, 10);
444 end if;
445 End delete_validate;
446 --
447 end hxc_htr_bus;