[Home] [Help]
PACKAGE BODY: APPS.HXC_RETRIEVAL_RULE_COMPS_API
Source
1 Package Body hxc_retrieval_rule_comps_api as
2 /* $Header: hxcrtcapi.pkb 120.2 2005/09/23 06:21:33 nissharm noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hxc_retrieval_rule_comps_api.';
7
8 g_debug boolean := hr_utility.debug_enabled;
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< create_retrieval_rule_comps >------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 procedure create_retrieval_rule_comps
15 (p_validate in boolean default false
16 ,p_retrieval_rule_comp_id in out nocopy number
20 ,p_time_recipient_id in number
17 ,p_object_version_number in out nocopy number
18 ,p_retrieval_rule_id in number
19 ,p_status in varchar2
21 ,p_effective_date in date default null
22 ) is
23 --
24 -- Declare cursors and local variables
25 --
26
27 l_proc varchar2(72);
28 l_object_version_number hxc_retrieval_rule_comps.object_version_number%TYPE;
29 l_retrieval_rule_comp_id hxc_retrieval_rule_comps.retrieval_rule_comp_id%TYPE;
30 --
31 Begin
32 g_debug := hr_utility.debug_enabled;
33 --
34 if g_debug then
35 l_proc := g_package||'create_retrieval_rule_comps ';
36 hr_utility.set_location('Entering:'|| l_proc, 10);
37 end if;
38 --
39 -- Issue a savepoint
40 --
41 savepoint create_retrieval_rule_comps;
42 --
43 if g_debug then
44 hr_utility.set_location(l_proc, 20);
45 end if;
46 --
47 -- Truncate the time portion from all IN date parameters
48 --
49
50 --
51 -- Call Before Process User Hook
52 --
53 begin
54 hxc_retrieval_rule_comps_bk_1.create_retrieval_rule_comps_b
55 (p_retrieval_rule_comp_id => p_retrieval_rule_comp_id
56 ,p_object_version_number => p_object_version_number
57 ,p_retrieval_rule_id => p_retrieval_rule_id
58 ,p_status => p_status
59 ,p_time_recipient_id => p_time_recipient_id
60 ,p_effective_date => p_effective_date
61 );
62 exception
63 when hr_api.cannot_find_prog_unit then
64 hr_api.cannot_find_prog_unit_error
65 (p_module_name => 'create_retrieval_rule_comps'
66 ,p_hook_type => 'BP'
67 );
68 end;
69 --
70 if g_debug then
71 hr_utility.set_location(l_proc, 30);
72 end if;
73 --
74
75 --
76 -- Validation in addition to Row Handlers
77 --
78
79
80
81 --
82 -- Process Logic
83 --
84 if g_debug then
85 hr_utility.set_location(l_proc, 40);
86 end if;
87 --
88 -- call row handler
89 --
90 hxc_rtc_ins.ins
91 (p_effective_date => p_effective_date
92 ,p_retrieval_rule_id => p_retrieval_rule_id
93 ,p_status => p_status
94 ,p_time_recipient_id => p_time_recipient_id
95 ,p_retrieval_rule_comp_id => l_retrieval_rule_comp_id
96 ,p_object_version_number => l_object_version_number
97 );
98 --
99 if g_debug then
100 hr_utility.set_location(l_proc, 50);
101 end if;
102 --
103 -- Call After Process User Hook
104 --
105 begin
106 hxc_retrieval_rule_comps_bk_1.create_retrieval_rule_comps_a
107 (p_retrieval_rule_comp_id => p_retrieval_rule_comp_id
108 ,p_object_version_number => p_object_version_number
109 ,p_retrieval_rule_id => p_retrieval_rule_id
110 ,p_status => p_status
111 ,p_time_recipient_id => p_time_recipient_id
112 ,p_effective_date => p_effective_date
113 );
114 exception
115 when hr_api.cannot_find_prog_unit then
116 hr_api.cannot_find_prog_unit_error
117 (p_module_name => 'create_retrieval_rule_comps'
118 ,p_hook_type => 'AP'
119 );
120 end;
121 --
122 if g_debug then
123 hr_utility.set_location(l_proc, 60);
124 end if;
125 --
126 --
127 -- When in validation only mode raise the Validate_Enabled exception
128 --
129 if p_validate then
130 raise hr_api.validate_enabled;
131 end if;
132 --
133 --if g_debug then
134 --hr_utility.set_location(' Leaving:'||l_proc, 70);
135 --end if;
136 --
137 --
138 -- Set all output arguments
139 --
140 p_retrieval_rule_comp_id := l_retrieval_rule_comp_id;
141 p_object_version_number := l_object_version_number;
142 --
143 if g_debug then
144 hr_utility.set_location(' Leaving:'||l_proc, 70);
145 end if;
146 exception
147 when hr_api.validate_enabled then
148 --
149 -- As the Validate_Enabled exception has been raised
150 -- we must rollback to the savepoint
151 --
152 rollback to create_retrieval_rule_comps;
153 --
154 -- Only set output warning arguments
155 -- (Any key or derived arguments must be set to null
156 -- when validation only mode is being used.)
157 --
158 p_retrieval_rule_comp_id := null;
159 p_object_version_number := null;
160 if g_debug then
161 hr_utility.set_location(' Leaving:'||l_proc, 80);
162 end if;
163 when others then
164 --
165 -- A validation or unexpected error has occured
166 --
167 rollback to create_retrieval_rule_comps;
168 if g_debug then
169 hr_utility.set_location(' Leaving:'||l_proc, 90);
170 end if;
171 raise;
172 --
173 end create_retrieval_rule_comps;
174 --
175 --
176 -- ----------------------------------------------------------------------------
177 -- |------------------------< update_retrieval_rule_comps>---------------------|
178 -- ----------------------------------------------------------------------------
179 --
180 procedure update_retrieval_rule_comps
181 (p_validate in boolean default false
182 ,p_retrieval_rule_comp_id in number
183 ,p_object_version_number in out nocopy number
184 ,p_retrieval_rule_id in number
185 ,p_status in varchar2
186 ,p_time_recipient_id in number
187 ,p_effective_date in date default null
188 )is
189 --
190 -- Declare cursors and local variables
191 --
192 l_proc varchar2(72);
193 l_object_version_number hxc_retrieval_rule_comps.object_version_number%TYPE := p_object_version_number;
194 --
195 Begin
196 g_debug := hr_utility.debug_enabled;
197 --
198 if g_debug then
199 l_proc := g_package||' update_retrieval_rule_comps';
200 hr_utility.set_location('Entering:'|| l_proc, 10);
201 end if;
202 --
203 -- Issue a savepoint if operating in validation only mode
204 --
205 savepoint update_retrieval_rule_comps;
206 --
207 if g_debug then
208 hr_utility.set_location(l_proc, 20);
209 end if;
210 --
211 -- Call Before Process User Hook
212 --
213 begin
214 hxc_retrieval_rule_comps_bk_1.update_retrieval_rule_comps_b
215 (p_retrieval_rule_comp_id => p_retrieval_rule_comp_id
216 ,p_object_version_number => p_object_version_number
217 ,p_retrieval_rule_id => p_retrieval_rule_id
218 ,p_status => p_status
219 ,p_time_recipient_id => p_time_recipient_id
220 ,p_effective_date => p_effective_date
221 );
222 exception
223 when hr_api.cannot_find_prog_unit then
224 hr_api.cannot_find_prog_unit_error
225 (p_module_name => 'update_retrieval_rule_comps'
226 ,p_hook_type => 'BP'
227 );
228 end;
229 --
230 --if g_debug then
231 --hr_utility.set_location(l_proc, 30);
232 --end if;
233 --
234 -- Process Logic
235 --
236 -- call row handler
237 --
238 hxc_rtc_upd.upd
239 (p_effective_date => p_effective_date
240 ,p_retrieval_rule_comp_id => p_retrieval_rule_comp_id
241 ,p_object_version_number => l_object_version_number
242 ,p_retrieval_rule_id => p_retrieval_rule_id
243 ,p_status => p_status
244 ,p_time_recipient_id => p_time_recipient_id
245 );
246 --
247 if g_debug then
248 hr_utility.set_location(l_proc, 40);
249 end if;
250 --
251 -- Call After Process User Hook
252 --
253 begin
254 hxc_retrieval_rule_comps_bk_1.update_retrieval_rule_comps_a
255 (p_retrieval_rule_comp_id => p_retrieval_rule_comp_id
256 ,p_object_version_number => p_object_version_number
257 ,p_retrieval_rule_id => p_retrieval_rule_id
258 ,p_status => p_status
259 ,p_time_recipient_id => p_time_recipient_id
260 ,p_effective_date => p_effective_date
261 );
262 exception
263 when hr_api.cannot_find_prog_unit then
264 hr_api.cannot_find_prog_unit_error
265 (p_module_name => 'update_retrieval_rule_comps'
266 ,p_hook_type => 'AP'
267 );
268 end;
269 --
270 if g_debug then
271 hr_utility.set_location(l_proc, 50);
272 end if;
273 --
274 -- When in validation only mode raise the Validate_Enabled exception
275 --
276 if p_validate then
277 raise hr_api.validate_enabled;
278 end if;
279 --
280 if g_debug then
281 hr_utility.set_location(' Leaving:'||l_proc, 60);
282 end if;
283 --
284 -- Set all output arguments
285 --
286
287 p_object_version_number := l_object_version_number;
288 --
289 exception
290 --
291 when hr_api.validate_enabled then
292 --
293 -- As the Validate_Enabled exception has been raised
294 -- we must rollback to the savepoint
295 --
296 ROLLBACK TO update_retrieval_rule_comps;
297 --
298 -- Only set output warning arguments
299 -- (Any key or derived arguments must be set to null
300 -- when validation only mode is being used.)
301 --
302 p_object_version_number := null;
303 --
304 if g_debug then
305 hr_utility.set_location(' Leaving:'||l_proc, 60);
306 end if;
307 --
308 when others then
309 --
310 -- A validation or unexpected error has occured
311 --
312 ROLLBACK TO update_retrieval_rule_comps;
313 if g_debug then
314 hr_utility.set_location(' Leaving:'||l_proc, 70);
315 end if;
316 raise;
317 --
318 END update_retrieval_rule_comps;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |------------------------< delete_retrieval_rule_comps >--------------------|
322 -- ----------------------------------------------------------------------------
323 --
324 procedure delete_retrieval_rule_comps
325 (p_validate in boolean default false
326 ,p_retrieval_rule_comp_id in number
327 ,p_object_version_number in number
328 ) is
329 --
330 -- Declare cursors and local variables
331 --
332 l_proc varchar2(72);
333 --
334 begin
335 g_debug := hr_utility.debug_enabled;
336 --
337 if g_debug then
338 l_proc := g_package||'delete_retrieval_rule_comps';
339 hr_utility.set_location('Entering:'|| l_proc, 10);
340 end if;
341 --
342 -- Issue a savepoint if operating in validation only mode
343 --
344 savepoint delete_retrieval_rule_comps;
345 --
346 if g_debug then
347 hr_utility.set_location(l_proc, 20);
348 end if;
349 --
350 -- Call Before Process User Hook
351 --
352 begin
353 --
354 hxc_retrieval_rule_comps_bk_1.delete_retrieval_rule_comps_b
355 (p_retrieval_rule_comp_id => p_retrieval_rule_comp_id
356 ,p_object_version_number => p_object_version_number
357 );
358 exception
359 when hr_api.cannot_find_prog_unit then
360 hr_api.cannot_find_prog_unit_error
361 (p_module_name => 'delete_retrieval_rule_comps'
362 ,p_hook_type => 'BP'
363 );
364 end;
365 --
366 if g_debug then
367 hr_utility.set_location(l_proc, 30);
368 end if;
369 --
370 -- Process Logic
371 --
372 hxc_rtc_del.del
373 (
374 p_retrieval_rule_comp_id => p_retrieval_rule_comp_id
375 ,p_object_version_number => p_object_version_number
376 );
377 --
378 if g_debug then
379 hr_utility.set_location(l_proc, 40);
380 end if;
381 --
382 -- Call After Process User Hook
383 --
384 begin
385 --
386 hxc_retrieval_rule_comps_bk_1.delete_retrieval_rule_comps_a
387 (p_retrieval_rule_comp_id => p_retrieval_rule_comp_id
388 ,p_object_version_number => p_object_version_number
389 );
390 exception
391 when hr_api.cannot_find_prog_unit then
392 hr_api.cannot_find_prog_unit_error
393 (p_module_name => 'delete_retrieval_rule_comps'
394 ,p_hook_type => 'AP'
395 );
396 end;
397 --
398 -- When in validation only mode raise the Validate_Enabled exception
399 --
400 if p_validate then
401 raise hr_api.validate_enabled;
402 end if;
403 --
404 if g_debug then
405 hr_utility.set_location(' Leaving:'||l_proc, 50);
406 end if;
407 --
408 exception
409 --
410 when hr_api.validate_enabled then
411 --
412 -- As the Validate_Enabled exception has been raised
413 -- we must rollback to the savepoint
414 --
415 ROLLBACK TO delete_retrieval_rule_comps;
416 --
417 when others then
418 --
419 -- A validation or unexpected error has occured
420 --
421 ROLLBACK TO delete_retrieval_rule_comps;
422 raise;
423 --
424 end delete_retrieval_rule_comps;
425 --
426 -- Added by ksethi ver 115.4
427 -- ----------------------------------------------------------------------------
428 -- |------------------------< chk_retr_as_unique >-----------------------------|
429 -- ----------------------------------------------------------------------------
430 --
431 -- Description:
432 -- This procedure insures a unique Retrieval Rule is only entered
433 -- in the system
434 --
435 -- Pre Conditions:
436 -- None
437 --
438 -- In Arguments:
439 --
440 -- retrieval_rule_id
441 --
442 --
443 -- Post Success:
444 -- Processing continues if the retrieval rule set business rules
445 -- have not been violated
446 --
447 -- Post Failure:
448 -- An application error is raised if already another retrieval rule
449 -- is present in the system with the same Retrieval Process Name,
450 -- Application and Status
451 --
452 -- ----------------------------------------------------------------------------
453 Procedure chk_retr_as_unique
454 (
455 p_retrieval_rule_id in hxc_retrieval_rules.retrieval_rule_id%TYPE
456 ) IS
457 --
458 l_proc varchar2(72);
459 --
460 -- cursor to check retrieval rule is unique
461 --
462 CURSOR csr_chk_as IS
463 SELECT 'error'
464 FROM sys.dual
465 WHERE EXISTS (select a
466 from (
467 select rc.retrieval_rule_id a,count(*) CNT
468 from hxc_retrieval_rule_comps rc, hxc_retrieval_rules rr
469 where rr.RETRIEVAL_PROCESS_ID = (select RETRIEVAL_PROCESS_ID
470 from hxc_retrieval_rules
471 where retrieval_rule_id=p_retrieval_rule_id)
472 and rr.retrieval_rule_id <> p_retrieval_rule_id
473 and rc.retrieval_rule_id <> p_retrieval_rule_id
474 and rc.retrieval_rule_id = rr.retrieval_rule_id
475 and (rc.TIME_RECIPIENT_ID, rc.STATUS) in ( select TIME_RECIPIENT_ID, STATUS
476 from hxc_retrieval_rule_comps
477 where retrieval_rule_id = p_retrieval_rule_id
478 )
479 group by rc.retrieval_rule_id
480
481 )
482 where CNT = (select count(distinct time_recipient_id)
483 from hxc_retrieval_rule_comps
484 where retrieval_rule_id = p_retrieval_rule_id)
485 and a not in (
486 select retrieval_rule_id
487 from hxc_retrieval_rule_comps
488 where
489 (TIME_RECIPIENT_ID, STATUS) not in ( select TIME_RECIPIENT_ID, STATUS
490 from hxc_retrieval_rule_comps
491 where retrieval_rule_id = p_retrieval_rule_id
492 )
493 ));
494
495 --
496 l_error varchar2(5) := NULL;
497 --
498 BEGIN
499 g_debug := hr_utility.debug_enabled;
500 if g_debug then
501 l_proc := g_package||'chk_retr_as_unique';
502 hr_utility.set_location('Entering:'||l_proc, 5);
503 end if;
504 --
505 -- check that there is no other similat retrieval rule in the system
506 --
507 OPEN csr_chk_as;
508 FETCH csr_chk_as INTO l_error;
509 CLOSE csr_chk_as;
510 --
511 IF l_error IS NOT NULL
512 THEN
513 --
514 hr_utility.set_message(809, 'HXC_RETR_AS_NOT_UNIQUE');
515 hr_utility.raise_error;
516 --
517 END IF;
518 --
519 if g_debug then
520 hr_utility.set_location('Leaving:'||l_proc, 20);
521 end if;
522 --
523 END chk_retr_as_unique;
524 --
525 --
526 end hxc_retrieval_rule_comps_api;