[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_WHERE_CLAUSE_API
Source
6 g_package varchar2(33) := ' ben_ext_where_clause_api.';
1 Package Body ben_ext_where_clause_api as
2 /* $Header: bexwcapi.pkb 120.2 2005/10/11 06:44:36 rbingi noship $ */
3 --
4 -- Package Variables
5 --
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_ext_where_clause >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ext_where_clause
13 (p_validate in boolean default false
14 ,p_ext_where_clause_id out nocopy number
15 ,p_seq_num in number default null
16 ,p_oper_cd in varchar2 default null
17 ,p_val in varchar2 default null
18 ,p_and_or_cd in varchar2 default null
19 ,p_ext_data_elmt_id in number default null
20 ,p_cond_ext_data_elmt_id in number default null
21 ,p_ext_rcd_in_file_id in number default null
22 ,p_ext_data_elmt_in_rcd_id in number default null
23 ,p_business_group_id in number default null
24 ,p_legislation_code in varchar2 default null
25 ,p_object_version_number out nocopy number
26 ,p_cond_ext_data_elmt_in_rcd_id in number default null
27 ,p_effective_date in date
28 ) is
29 --
30 -- Declare cursors and local variables
31 --
32 l_ext_where_clause_id ben_ext_where_clause.ext_where_clause_id%TYPE;
33 l_proc varchar2(72) := g_package||'create_ext_where_clause';
34 l_object_version_number ben_ext_where_clause.object_version_number%TYPE;
35 --
36 begin
37 --
38 hr_utility.set_location('Entering:'|| l_proc, 10);
39 --
40 -- Issue a savepoint if operating in validation only mode
41 --
42 savepoint create_ext_where_clause;
43 --
44 hr_utility.set_location(l_proc, 20);
45 --
46 -- Process Logic
47 --
48 begin
49 --
50 -- Start of API User Hook for the before hook of create_ext_where_clause
51 --
52 ben_ext_where_clause_bk1.create_ext_where_clause_b
53 (
54 p_seq_num => p_seq_num
55 ,p_oper_cd => p_oper_cd
56 ,p_val => p_val
57 ,p_and_or_cd => p_and_or_cd
58 ,p_ext_data_elmt_id => p_ext_data_elmt_id
59 ,p_cond_ext_data_elmt_id => p_cond_ext_data_elmt_id
60 ,p_ext_rcd_in_file_id => p_ext_rcd_in_file_id
61 ,p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id
62 ,p_business_group_id => p_business_group_id
63 ,p_legislation_code => p_legislation_code
64 ,p_cond_ext_data_elmt_in_rcd_id => p_cond_ext_data_elmt_in_rcd_id
65 ,p_effective_date => trunc(p_effective_date)
66 );
67 exception
68 when hr_api.cannot_find_prog_unit then
69 hr_api.cannot_find_prog_unit_error
70 (
71 p_module_name => 'CREATE_ext_where_clause'
72 ,p_hook_type => 'BP'
73 );
74 --
75 -- End of API User Hook for the before hook of create_ext_where_clause
76 --
77 end;
78 --
79 ben_xwc_ins.ins
80 (
81 p_ext_where_clause_id => l_ext_where_clause_id
82 ,p_seq_num => p_seq_num
83 ,p_oper_cd => p_oper_cd
84 ,p_val => p_val
85 ,p_and_or_cd => p_and_or_cd
86 ,p_ext_data_elmt_id => p_ext_data_elmt_id
87 ,p_cond_ext_data_elmt_id => p_cond_ext_data_elmt_id
88 ,p_ext_rcd_in_file_id => p_ext_rcd_in_file_id
89 ,p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id
90 ,p_business_group_id => p_business_group_id
91 ,p_legislation_code => p_legislation_code
92 ,p_object_version_number => l_object_version_number
93 ,p_cond_ext_data_elmt_in_rcd_id => p_cond_ext_data_elmt_in_rcd_id
94 ,p_effective_date => trunc(p_effective_date)
95 );
96 --
97 begin
98 --
99 -- Start of API User Hook for the after hook of create_ext_where_clause
100 --
101 ben_ext_where_clause_bk1.create_ext_where_clause_a
102 (
103 p_ext_where_clause_id => l_ext_where_clause_id
104 ,p_seq_num => p_seq_num
105 ,p_oper_cd => p_oper_cd
106 ,p_val => p_val
107 ,p_and_or_cd => p_and_or_cd
108 ,p_ext_data_elmt_id => p_ext_data_elmt_id
109 ,p_cond_ext_data_elmt_id => p_cond_ext_data_elmt_id
110 ,p_ext_rcd_in_file_id => p_ext_rcd_in_file_id
111 ,p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id
112 ,p_business_group_id => p_business_group_id
113 ,p_legislation_code => p_legislation_code
114 ,p_object_version_number => l_object_version_number
115 ,p_cond_ext_data_elmt_in_rcd_id => p_cond_ext_data_elmt_in_rcd_id
116 ,p_effective_date => trunc(p_effective_date)
117 );
118 exception
119 when hr_api.cannot_find_prog_unit then
120 hr_api.cannot_find_prog_unit_error
121 (p_module_name => 'CREATE_ext_where_clause'
122 ,p_hook_type => 'AP'
123 );
124 --
125 -- End of API User Hook for the after hook of create_ext_where_clause
126 --
127 end;
128 --
132 --
129 hr_utility.set_location(l_proc, 60);
130 --
131 -- When in validation only mode raise the Validate_Enabled exception
133 if p_validate then
134 raise hr_api.validate_enabled;
135 end if;
136 --
137 -- Set all output arguments
138 --
139 p_ext_where_clause_id := l_ext_where_clause_id;
140 p_object_version_number := l_object_version_number;
141 --
142 hr_utility.set_location(' Leaving:'||l_proc, 70);
143 --
144 exception
145 --
146 when hr_api.validate_enabled then
147 --
148 -- As the Validate_Enabled exception has been raised
149 -- we must rollback to the savepoint
150 --
151 ROLLBACK TO create_ext_where_clause;
152 --
153 -- Only set output warning arguments
154 -- (Any key or derived arguments must be set to null
155 -- when validation only mode is being used.)
156 --
157 p_ext_where_clause_id := null;
158 p_object_version_number := null;
162 --
159 hr_utility.set_location(' Leaving:'||l_proc, 80);
160 --
161 when others then
163 -- A validation or unexpected error has occured
164 --
165 ROLLBACK TO create_ext_where_clause;
166 p_ext_where_clause_id := null;
167 p_object_version_number := null;
168 raise;
169 --
170 end create_ext_where_clause;
171 -- ----------------------------------------------------------------------------
172 -- |------------------------< update_ext_where_clause >--- ------------------|
173 -- ----------------------------------------------------------------------------
174 --
175 procedure update_ext_where_clause
176 (p_validate in boolean default false
177 ,p_ext_where_clause_id in number
178 ,p_seq_num in number default hr_api.g_number
179 ,p_oper_cd in varchar2 default hr_api.g_varchar2
180 ,p_val in varchar2 default hr_api.g_varchar2
181 ,p_and_or_cd in varchar2 default hr_api.g_varchar2
182 ,p_ext_data_elmt_id in number default hr_api.g_number
183 ,p_cond_ext_data_elmt_id in number default hr_api.g_number
184 ,p_ext_rcd_in_file_id in number default hr_api.g_number
185 ,p_ext_data_elmt_in_rcd_id in number default hr_api.g_number
186 ,p_business_group_id in number default hr_api.g_number
187 ,p_legislation_code in varchar2 default hr_api.g_varchar2
188 ,p_object_version_number in out nocopy number
189 ,p_cond_ext_data_elmt_in_rcd_id in number default hr_api.g_number
190 ,p_effective_date in date
191 ) is
192 --
193 -- Declare cursors and local variables
194 --
195 l_proc varchar2(72) := g_package||'update_ext_where_clause';
196 l_object_version_number ben_ext_where_clause.object_version_number%TYPE;
197 --
198 begin
199 --
200 hr_utility.set_location('Entering:'|| l_proc, 10);
201 --
202 -- Issue a savepoint if operating in validation only mode
203 --
204 savepoint update_ext_where_clause;
205 --
206 hr_utility.set_location(l_proc, 20);
207 --
208 -- Process Logic
209 --
210 l_object_version_number := p_object_version_number;
211 --
212 begin
213 --
214 -- Start of API User Hook for the before hook of update_ext_where_clause
215 --
216 ben_ext_where_clause_bk2.update_ext_where_clause_b
217 (
218 p_ext_where_clause_id => p_ext_where_clause_id
219 ,p_seq_num => p_seq_num
220 ,p_oper_cd => p_oper_cd
221 ,p_val => p_val
222 ,p_and_or_cd => p_and_or_cd
223 ,p_ext_data_elmt_id => p_ext_data_elmt_id
224 ,p_cond_ext_data_elmt_id => p_cond_ext_data_elmt_id
225 ,p_ext_rcd_in_file_id => p_ext_rcd_in_file_id
226 ,p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id
227 ,p_business_group_id => p_business_group_id
228 ,p_legislation_code => p_legislation_code
229 ,p_object_version_number => p_object_version_number
230 ,p_cond_ext_data_elmt_in_rcd_id => p_cond_ext_data_elmt_in_rcd_id
231 ,p_effective_date => trunc(p_effective_date)
232 );
233 exception
234 when hr_api.cannot_find_prog_unit then
235 hr_api.cannot_find_prog_unit_error
236 (p_module_name => 'UPDATE_ext_where_clause'
237 ,p_hook_type => 'BP'
238 );
239 --
240 -- End of API User Hook for the before hook of update_ext_where_clause
241 --
242 end;
243 --
247 ,p_seq_num => p_seq_num
244 ben_xwc_upd.upd
245 (
246 p_ext_where_clause_id => p_ext_where_clause_id
248 ,p_oper_cd => p_oper_cd
249 ,p_val => p_val
250 ,p_and_or_cd => p_and_or_cd
251 ,p_ext_data_elmt_id => p_ext_data_elmt_id
252 ,p_cond_ext_data_elmt_id => p_cond_ext_data_elmt_id
253 ,p_ext_rcd_in_file_id => p_ext_rcd_in_file_id
254 ,p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id
255 ,p_business_group_id => p_business_group_id
256 ,p_legislation_code => p_legislation_code
257 ,p_object_version_number => l_object_version_number
258 ,p_cond_ext_data_elmt_in_rcd_id => p_cond_ext_data_elmt_in_rcd_id
259 ,p_effective_date => trunc(p_effective_date)
260 );
261 --
262 begin
263 --
264 -- Start of API User Hook for the after hook of update_ext_where_clause
265 --
266 ben_ext_where_clause_bk2.update_ext_where_clause_a
267 (
268 p_ext_where_clause_id => p_ext_where_clause_id
269 ,p_seq_num => p_seq_num
270 ,p_oper_cd => p_oper_cd
271 ,p_val => p_val
272 ,p_and_or_cd => p_and_or_cd
273 ,p_ext_data_elmt_id => p_ext_data_elmt_id
274 ,p_cond_ext_data_elmt_id => p_cond_ext_data_elmt_id
275 ,p_ext_rcd_in_file_id => p_ext_rcd_in_file_id
276 ,p_ext_data_elmt_in_rcd_id => p_ext_data_elmt_in_rcd_id
277 ,p_business_group_id => p_business_group_id
278 ,p_legislation_code => p_legislation_code
279 ,p_object_version_number => l_object_version_number
280 ,p_cond_ext_data_elmt_in_rcd_id => p_cond_ext_data_elmt_in_rcd_id
281 ,p_effective_date => trunc(p_effective_date)
282 );
283 exception
284 when hr_api.cannot_find_prog_unit then
285 hr_api.cannot_find_prog_unit_error
286 (p_module_name => 'UPDATE_ext_where_clause'
287 ,p_hook_type => 'AP'
288 );
289 --
290 -- End of API User Hook for the after hook of update_ext_where_clause
291 --
292 end;
293 --
294 hr_utility.set_location(l_proc, 60);
295 --
296 -- When in validation only mode raise the Validate_Enabled exception
297 --
298 if p_validate then
299 raise hr_api.validate_enabled;
300 end if;
301 --
302 -- Set all output arguments
303 --
304 p_object_version_number := l_object_version_number;
305 --
306 hr_utility.set_location(' Leaving:'||l_proc, 70);
307 --
308 exception
309 --
310 when hr_api.validate_enabled then
311 --
312 -- As the Validate_Enabled exception has been raised
316 --
313 -- we must rollback to the savepoint
314 --
315 ROLLBACK TO update_ext_where_clause;
317 -- Only set output warning arguments
318 -- (Any key or derived arguments must be set to null
319 -- when validation only mode is being used.)
320 --
321 hr_utility.set_location(' Leaving:'||l_proc, 80);
322 --
323 when others then
324 --
325 -- A validation or unexpected error has occured
326 --
327 ROLLBACK TO update_ext_where_clause;
328 raise;
329 --
330 end update_ext_where_clause;
331 -- ----------------------------------------------------------------------------
332 -- |------------------------< delete_ext_where_clause >----------------------|
333 -- ----------------------------------------------------------------------------
334 --
335 procedure delete_ext_where_clause
336 (p_validate in boolean default false
337 ,p_ext_where_clause_id in number
338 ,p_legislation_code in varchar2 default null
339 ,p_object_version_number in out nocopy number
340 ,p_effective_date in date
341 ) is
342 --
343 -- Declare cursors and local variables
344 --
345 l_proc varchar2(72) := g_package||'update_ext_where_clause';
346 l_object_version_number ben_ext_where_clause.object_version_number%TYPE;
347 --
348 begin
349 --
350 hr_utility.set_location('Entering:'|| l_proc, 10);
351 --
352 -- Issue a savepoint if operating in validation only mode
353 --
354 savepoint delete_ext_where_clause;
355 --
356 hr_utility.set_location(l_proc, 20);
360 l_object_version_number := p_object_version_number;
357 --
358 -- Process Logic
359 --
361 --
362 --
363 begin
364 --
365 -- Start of API User Hook for the before hook of delete_ext_where_clause
366 --
367 ben_ext_where_clause_bk3.delete_ext_where_clause_b
368 (
369 p_ext_where_clause_id => p_ext_where_clause_id
370 ,p_legislation_code => p_legislation_code
371 ,p_object_version_number => p_object_version_number
372 ,p_effective_date => trunc(p_effective_date)
373 );
374 exception
375 when hr_api.cannot_find_prog_unit then
376 hr_api.cannot_find_prog_unit_error
377 (p_module_name => 'DELETE_ext_where_clause'
378 ,p_hook_type => 'BP'
379 );
380 --
381 -- End of API User Hook for the before hook of delete_ext_where_clause
382 --
383 end;
384 --
385 ben_xwc_del.del
386 (
387 p_ext_where_clause_id => p_ext_where_clause_id
388 ,p_legislation_code => p_legislation_code
389 ,p_object_version_number => l_object_version_number
390 ,p_effective_date => p_effective_date
391 );
392 --
393 begin
394 --
395 -- Start of API User Hook for the after hook of delete_ext_where_clause
396 --
397 ben_ext_where_clause_bk3.delete_ext_where_clause_a
398 (
399 p_ext_where_clause_id => p_ext_where_clause_id
400 ,p_legislation_code => p_legislation_code
401 ,p_object_version_number => l_object_version_number
402 ,p_effective_date => trunc(p_effective_date)
403 );
404 exception
405 when hr_api.cannot_find_prog_unit then
406 hr_api.cannot_find_prog_unit_error
407 (p_module_name => 'DELETE_ext_where_clause'
408 ,p_hook_type => 'AP'
409 );
410 --
411 -- End of API User Hook for the after hook of delete_ext_where_clause
412 --
413 end;
414 --
415 hr_utility.set_location(l_proc, 60);
416 --
417 -- When in validation only mode raise the Validate_Enabled exception
418 --
419 if p_validate then
420 raise hr_api.validate_enabled;
421 end if;
422 --
423 hr_utility.set_location(' Leaving:'||l_proc, 70);
424 --
425 exception
426 --
427 when hr_api.validate_enabled then
428 --
429 -- As the Validate_Enabled exception has been raised
430 -- we must rollback to the savepoint
431 --
432 ROLLBACK TO delete_ext_where_clause;
433 --
434 -- Only set output warning arguments
435 -- (Any key or derived arguments must be set to null
436 -- when validation only mode is being used.)
437 --
438 --
439 when others then
440 --
441 -- A validation or unexpected error has occured
442 --
443 ROLLBACK TO delete_ext_where_clause;
444 raise;
445 --
446 end delete_ext_where_clause;
447 --
448 -- ----------------------------------------------------------------------------
449 -- |-------------------------------< lck >------------------------------------|
450 -- ----------------------------------------------------------------------------
451 --
452 procedure lck
453 (
454 p_ext_where_clause_id in number
455 ,p_object_version_number in number
456 ) is
457 --
458 --
459 -- Declare cursors and local variables
460 --
461 l_proc varchar2(72) := g_package||'lck';
462 --
463 begin
464 --
465 hr_utility.set_location('Entering:'|| l_proc, 10);
466 --
467 ben_xwc_shd.lck
468 (
469 p_ext_where_clause_id => p_ext_where_clause_id
470 ,p_object_version_number => p_object_version_number
471 );
472 --
473 hr_utility.set_location(' Leaving:'||l_proc, 70);
474 --
475 end lck;
476 --
477 -- ----------------------------------------------------------------------------
478 -- |---------------------< multi_rows_edit >----------------------------------|
479 -- |----The procedure checks for Completeness of the AND , OR Conditon -------|
480 -- |-------and will be called from ON-COMMIT Trigger of the Form -------------|
481 -- ----------------------------------------------------------------------------
482 --
483 procedure multi_rows_edit
484 (p_business_group_id in number
485 ,p_legislation_code in varchar2
486 ,p_ext_rcd_in_file_id in number
487 ,p_ext_data_elmt_in_rcd_id in number
488 ,p_ext_data_elmt_id in number )
489 is
490 --
491 l_proc varchar2(72) := 'insert_validate';
492 --
493 cursor c_xwc is
494 SELECT seq_num, and_or_cd
495 FROM ben_ext_where_clause xwc
499 or legislation_code = p_legislation_code )
496 WHERE ( business_group_id is null
497 or business_group_id = p_business_group_id )
498 and (legislation_code is null
500 and (ext_rcd_in_file_id = p_ext_rcd_in_file_id
501 or p_ext_rcd_in_file_id is null )
502 and (ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
503 or p_ext_data_elmt_in_rcd_id is null)
504 and (ext_data_elmt_id = p_ext_data_elmt_id
505 or p_ext_data_elmt_id is null)
506 ORDER BY seq_num;
507 --
508 l_dynamic_sql_stmt varchar2(500);
509 l_rec_defined BOOLEAN := FALSE;
510 --
511 Begin
512 --
513 l_dynamic_sql_stmt := 'Begin If ';
514 --
515 for l_xwc in c_xwc
516 Loop
517 l_dynamic_sql_stmt := l_dynamic_sql_stmt || ' TRUE '||l_xwc.and_or_cd;
518 l_rec_defined := TRUE ;
519 --
520 End Loop;
521 --
525 execute immediate l_dynamic_sql_stmt;
522 l_dynamic_sql_stmt := l_dynamic_sql_stmt || ' then null; end if; end;';
523 If l_rec_defined then
524 begin
526 exception
527 when others then
528 fnd_message.set_name('BEN','BEN_94457_CHK_AND_OR_CD');
529 fnd_message.raise_error;
530 end;
531 End if;
532 --
533 End multi_rows_edit;
534 --
535 end ben_ext_where_clause_api;