[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_AUDIT_API
Source
1 Package Body BEN_CWB_AUDIT_API as
2 /* $Header: beaudapi.pkb 120.4 2006/10/27 11:13:22 steotia noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' BEN_CWB_AUDIT_API.';
7 g_debug boolean := hr_utility.debug_enabled;
8 g_lookup_validity g_validity_table_type := g_validity_table_type();
9 --
10 --
11 -- -----------------------------------------------------------------------
12 -- |--------------------------< create_per_record >----------------------|
13 -- -----------------------------------------------------------------------
14 procedure create_per_record
15 (p_per_in_ler_id in number
16 ) is
17 --
18 l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
19 l_object_version_number ben_cwb_audit.object_version_number%type;
20 l_proc varchar2(72) := g_package||'create_per_record';
21 l_lf_evt_ocrd_dt ben_cwb_audit.lf_evt_ocrd_dt%type;
22 /*
23 l_created_by ben_per_in_ler.created_by%type;
24 */
25 l_creation_date ben_per_in_ler.creation_date%type;
26 l_last_updated_by ben_per_in_ler.last_updated_by%type;
27 l_change_made_by_person_id ben_cwb_audit.change_made_by_person_id%type;
28 l_group_pl_id ben_cwb_audit.group_pl_id%type;
29 l_person_id fnd_user.employee_id%type;
30
31 begin
32 if g_debug then
33 hr_utility.set_location('Entering:'|| l_proc, 100);
34 end if;
35
36 begin
37 /*
38 select created_by, creation_date, group_pl_id, lf_evt_ocrd_dt
39 into l_created_by, l_creation_date, l_group_pl_id, l_lf_evt_ocrd_dt
40 from ben_per_in_ler
41 where per_in_ler_id = p_per_in_ler_id;
42 */
43 select last_updated_by, group_pl_id, lf_evt_ocrd_dt,creation_date
44 into l_last_updated_by, l_group_pl_id, l_lf_evt_ocrd_dt, l_creation_date
45 from ben_per_in_ler
46 where per_in_ler_id = p_per_in_ler_id;
47
48 -- if record exists
49
50 select employee_id into l_person_id
51 from fnd_user
52 where user_id = l_last_updated_by;
53
54 if(ben_cwb_audit_api.return_lookup_validity('BG')=true) then
55 ben_cwb_audit_api.create_audit_entry
56 (p_group_per_in_ler_id => p_per_in_ler_id
57 ,p_group_pl_id => l_group_pl_id
58 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
59 ,p_pl_id => l_group_pl_id
60 ,p_group_oipl_id => -1
61 ,p_audit_type_cd => 'BG'
62 ,p_old_val_varchar => null
63 ,p_new_val_varchar => null
64 ,p_date_stamp => sysdate
65 ,p_change_made_by_person_id => l_person_id
66 ,p_supporting_information => '(as of '||trunc(l_creation_date)||')'
67 ,p_cwb_audit_id => l_cwb_audit_id
68 ,p_object_version_number => l_object_version_number
69 );
70 end if;
71
72 if g_debug then
73 hr_utility.set_location('BG:'||'p_per_in_ler_id : '||p_per_in_ler_id , 10);
74 hr_utility.set_location('done by :'||l_person_id, 12);
75 end if;
76
77
78 exception
79 when no_data_found then
80 null;
81 end;
82
83 if g_debug then
84 hr_utility.set_location('Leaving:'|| l_proc, 200);
85 end if;
86
87
88 end create_per_record;
89 --
90 --
91 -- -----------------------------------------------------------------------
92 -- |--------------------------< update_per_record >----------------------|
93 -- -----------------------------------------------------------------------
94 procedure update_per_record
95 (p_per_in_ler_id in ben_per_in_ler.per_in_ler_id%type
96 ) is
97 --
98 begin
99 update_per_record(p_per_in_ler_id => p_per_in_ler_id
100 ,p_old_val => null
101 ,p_audit_type_cd => null
102 );
103 end;
104 --
105 -- -----------------------------------------------------------------------
106 -- |--------------------------< update_per_record2 >----------------------|
107 -- -----------------------------------------------------------------------
108 procedure update_per_record2
109 (p_group_per_in_ler_id in number
110 ) is
111 --
112 l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
113 l_object_version_number ben_cwb_audit.object_version_number%type;
114 l_proc varchar2(72) := g_package||'update_per_record';
115 l_per_record_new ben_per_in_ler%rowtype;
116 l_person_id fnd_user.employee_id%type;
117
118 begin
119 if g_debug then
120 hr_utility.set_location('Entering:'|| l_proc, 100);
121 end if;
122
123 begin
124
125 select *
126 into l_per_record_new
127 from ben_per_in_ler
128 where per_in_ler_id = p_group_per_in_ler_id;
129
130 -- if record exists
131
132 select employee_id into l_person_id
133 from fnd_user
134 where user_id = l_per_record_new.last_updated_by;
135
136 if g_debug then
137 hr_utility.set_location('Record exists for per_in_ler_id: '|| l_per_record_new.per_in_ler_id, 10);
138 end if;
139 hr_utility.set_location('Record exists for per_in_ler_id: '|| l_per_record_new.per_in_ler_id, 10);
140
141 /* -------- overloading
142 if( ((p_per_record_old.ws_mgr_id is null)
143 and (l_per_record_new.ws_mgr_id is not null))
144 or ((l_per_record_new.ws_mgr_id is null)
145 and (p_per_record_old.ws_mgr_id is not null))
146 or (p_per_record_old.ws_mgr_id <> l_per_record_new.ws_mgr_id) ) then
147
148 if(ben_cwb_audit_api.return_lookup_validity('MG')=true) then
149
150 begin
151 select DECODE (ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
152 'BN', empinfo.brief_name,
153 'CN', empinfo.custom_name,
154 empinfo.full_name
155 )
156 into l_old_ws_mgr_name
157 from ben_cwb_person_info empinfo
158 where empinfo.group_per_in_ler_id = p_per_record_old.ws_mgr_id;
159 exception
160 when no_data_found then
161 l_old_ws_mgr_name := p_per_record_old.ws_mgr_id;
162 end;
163
164 begin
165 select DECODE (ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
166 'BN', empinfo.brief_name,
167 'CN', empinfo.custom_name,
168 empinfo.full_name
169 )
170 into l_new_ws_mgr_name
171 from ben_cwb_person_info empinfo
172 where empinfo.group_per_in_ler_id = l_per_record_new.ws_mgr_id;
173 exception
174 when no_data_found then
175 l_new_ws_mgr_name := l_per_record_new.ws_mgr_id;
176 end;
177
178
179 ben_cwb_audit_api.create_audit_entry
180 (p_group_per_in_ler_id => l_per_record_new.per_in_ler_id
181 ,p_group_pl_id => l_per_record_new.group_pl_id
182 ,p_lf_evt_ocrd_dt => l_per_record_new.lf_evt_ocrd_dt
183 ,p_pl_id => l_per_record_new.group_pl_id
184 ,p_group_oipl_id => -1
185 ,p_audit_type_cd => 'MG'
186 ,p_old_val_varchar => l_old_ws_mgr_name
187 ,p_new_val_varchar => l_new_ws_mgr_name
188 ,p_date_stamp => sysdate
189 ,p_change_made_by_person_id => l_person_id
190 ,p_cwb_audit_id => l_cwb_audit_id
191 ,p_object_version_number => l_object_version_number
192 );
193
194 if g_debug then
195 hr_utility.set_location('MG done: '|| l_per_record_new.per_in_ler_id, 20);
196 end if;
197
198 end if;
199 end if; */
200 /* Commenting out as old values are unavailable for comparison
201 if( (p_per_record_old.per_in_ler_stat_cd <> l_per_record_new.per_in_ler_stat_cd) ) then
202 --change in status
203
204 if g_debug then
205 hr_utility.set_location('Some Change in Status: '|| l_per_record_new.per_in_ler_id, 30);
206 end if;
207
208 */
209
210 if(l_per_record_new.per_in_ler_stat_cd = 'PROCD') then
211 -- if processed
212
213 if(ben_cwb_audit_api.return_lookup_validity('EN')=true) then
214
215 ben_cwb_audit_api.create_audit_entry
216 (p_group_per_in_ler_id => l_per_record_new.per_in_ler_id
217 ,p_group_pl_id => l_per_record_new.group_pl_id
218 ,p_lf_evt_ocrd_dt => l_per_record_new.lf_evt_ocrd_dt
219 ,p_pl_id => l_per_record_new.group_pl_id
220 ,p_group_oipl_id => -1
221 ,p_audit_type_cd => 'EN'
222 ,p_old_val_varchar => null
223 ,p_new_val_varchar => null
224 ,p_date_stamp => sysdate
225 ,p_change_made_by_person_id => l_person_id
226 ,p_cwb_audit_id => l_cwb_audit_id
227 ,p_object_version_number => l_object_version_number
228 );
229
230 if g_debug then
231 hr_utility.set_location('EN done: '|| l_per_record_new.per_in_ler_id, 30);
232 hr_utility.set_location('EN by: '|| l_person_id, 31);
233
234 end if;
235
236 end if;
237 end if;
238
239 if(l_per_record_new.per_in_ler_stat_cd = 'BCKDT') then
240 -- if backed-out
241
242 if(ben_cwb_audit_api.return_lookup_validity('BO')=true) then
243
244 ben_cwb_audit_api.create_audit_entry
245 (p_group_per_in_ler_id => l_per_record_new.per_in_ler_id
246 ,p_group_pl_id => l_per_record_new.group_pl_id
247 ,p_lf_evt_ocrd_dt => l_per_record_new.lf_evt_ocrd_dt
248 ,p_pl_id => l_per_record_new.group_pl_id
249 ,p_group_oipl_id => -1
250 ,p_audit_type_cd => 'BO'
251 ,p_old_val_varchar => null
252 ,p_new_val_varchar => null
253 ,p_date_stamp => sysdate
254 ,p_change_made_by_person_id => l_person_id
255 ,p_cwb_audit_id => l_cwb_audit_id
256 ,p_object_version_number => l_object_version_number
257 );
258
259 if g_debug then
260 hr_utility.set_location('BO done: '|| l_per_record_new.per_in_ler_id, 300);
261 hr_utility.set_location('BO by: '|| l_person_id, 301);
262 hr_utility.set_location('sysdate: '|| sysdate, 305);
263 end if;
264
265 end if;
266 end if;
267 /* end if; */
268
269
270 exception
271 when no_data_found then
272 null;
273 end;
274
275 if g_debug then
276 hr_utility.set_location('Leaving:'|| l_proc, 200);
277 end if;
278
279
280 end update_per_record2;
281
282 --
283 --
284 -- -----------------------------------------------------------------------
285 -- |--------------------------< update_per_record >----------------------|
286 -- -----------------------------------------------------------------------
287 procedure update_per_record
288 (p_per_in_ler_id in number
289 ,p_old_val in varchar2
290 ,p_audit_type_cd in varchar2
291 ) is
292 --
293 l_proc varchar2(72) := g_package||'update_per_record';
294 l_ws_mgr_id ben_per_in_ler.ws_mgr_id%type;
295 l_old_ws_mgr_name ben_cwb_person_info.full_name%type;
296 l_new_ws_mgr_name ben_cwb_person_info.full_name%type;
297 l_per_record_new ben_per_in_ler%rowtype;
298 l_person_id fnd_user.employee_id%type;
299 l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
300 l_object_version_number ben_cwb_audit.object_version_number%type;
301 l_personid ben_per_in_ler.ws_mgr_id%type;
302
303
304 begin
305
306 --hr_utility.trace_on(null,'audit1');
307 --g_debug:=true;
308
309 if g_debug then
310 hr_utility.set_location('Entering:'|| l_proc, 100);
311 end if;
312
313 if(p_audit_type_cd is null) then
314 update_per_record2(p_group_per_in_ler_id => p_per_in_ler_id);
315
316 elsif (p_audit_type_cd is not null) then
317
318 hr_utility.set_location('audit_type_cd :'|| p_audit_type_cd, 10);
319
320 begin
321
322 select *
323 into l_per_record_new
324 from ben_per_in_ler
325 where per_in_ler_id = p_per_in_ler_id;
326
327 -- if record exists
328
329 select employee_id into l_person_id
330 from fnd_user
331 where user_id = l_per_record_new.last_updated_by;
332
333 if g_debug then
334 hr_utility.set_location
335 ('Record exists for per_in_ler_id: '||l_per_record_new.per_in_ler_id, 10);
336 end if;
337
338 if( p_audit_type_cd = 'MG' ) then
339
340 l_ws_mgr_id := p_old_val;
341
342 if( ((l_ws_mgr_id is null)
343 and (l_per_record_new.ws_mgr_id is not null))
344 or ((l_per_record_new.ws_mgr_id is null)
345 and (l_ws_mgr_id is not null))
346 or (l_ws_mgr_id <> l_per_record_new.ws_mgr_id) ) then
347
348 if(ben_cwb_audit_api.return_lookup_validity('MG')=true) then
349
350 begin
351
352 select distinct ppf.person_id, DECODE
353 (ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
354 'BN', trim(ppf.first_name ||' '||ppf.last_name||' '||ppf.suffix),
355 'CN', nvl(ben_cwb_custom_person_pkg.get_custom_name
356 (ppf.person_id
357 ,pil.assignment_id
358 ,bg.legislation_code
359 ,pil.group_pl_id
360 ,pil.lf_evt_ocrd_dt
361 ,sysdate),
362 ppf.full_name),
363 ppf.full_name)
364 into l_personid, l_old_ws_mgr_name
365 from per_all_people_f ppf
366 ,ben_per_in_ler pil
367 ,per_all_assignments_f paf
368 ,per_business_groups bg
369 where ppf.person_id = l_ws_mgr_id
370 and sysdate between ppf.effective_start_date and
371 ppf.effective_end_date
372 and paf.assignment_id = pil.assignment_id
373 and sysdate between paf.effective_start_date and
374 paf.effective_end_date
375 and paf.person_id = ppf.person_id
376 and bg.business_group_id = paf.business_group_id;
377
378 exception
379 when no_data_found then
380 l_old_ws_mgr_name := l_ws_mgr_id;
381 end;
382
383 begin
384
385 select distinct ppf.person_id, DECODE
386 (ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
387 'BN', trim(ppf.first_name ||' '||ppf.last_name||' '||ppf.suffix),
388 'CN', nvl(ben_cwb_custom_person_pkg.get_custom_name
389 (ppf.person_id
390 ,pil.assignment_id
391 ,bg.legislation_code
392 ,pil.group_pl_id
393 ,pil.lf_evt_ocrd_dt
394 ,sysdate),
395 ppf.full_name),
396 ppf.full_name)
397 into l_personid, l_new_ws_mgr_name
398 from per_all_people_f ppf
399 ,ben_per_in_ler pil
400 ,per_all_assignments_f paf
401 ,per_business_groups bg
402 where ppf.person_id = l_per_record_new.ws_mgr_id
403 and sysdate between ppf.effective_start_date and
404 ppf.effective_end_date
405 and paf.assignment_id = pil.assignment_id
406 and sysdate between paf.effective_start_date and
407 paf.effective_end_date
408 and paf.person_id = ppf.person_id
409 and bg.business_group_id = paf.business_group_id;
410
411 exception
412 when no_data_found then
413 l_new_ws_mgr_name := l_per_record_new.ws_mgr_id;
414 end;
415
416
417 ben_cwb_audit_api.create_audit_entry
418 (p_group_per_in_ler_id => l_per_record_new.per_in_ler_id
419 ,p_group_pl_id => l_per_record_new.group_pl_id
420 ,p_lf_evt_ocrd_dt => l_per_record_new.lf_evt_ocrd_dt
421 ,p_pl_id => l_per_record_new.group_pl_id
422 ,p_group_oipl_id => -1
423 ,p_audit_type_cd => 'MG'
424 ,p_old_val_varchar => l_old_ws_mgr_name
425 ,p_new_val_varchar => l_new_ws_mgr_name
426 ,p_date_stamp => sysdate
427 ,p_change_made_by_person_id => l_person_id
428 ,p_cwb_audit_id => l_cwb_audit_id
429 ,p_object_version_number => l_object_version_number
430 );
431
432 if g_debug then
433 hr_utility.set_location
434 ('MG done: '|| l_per_record_new.per_in_ler_id, 20);
435 end if;
436
437 end if;
438 end if;
439 end if;
440
441 exception
442 when no_data_found then
443 null;
444 end;
445
446 end if;
447
448 if g_debug then
449 hr_utility.set_location('Leaving:'|| l_proc, 200);
450 end if;
451
452
453 end update_per_record;
454
455
456
457 --
458 --
459 -- -----------------------------------------------------------------------
460 -- |--------------------------< create_audit_entry >----------------------|
461 -- -----------------------------------------------------------------------
462 procedure create_audit_entry
463 (p_validate in boolean default false
464 ,p_group_per_in_ler_id in number
465 ,p_group_pl_id in number
466 ,p_lf_evt_ocrd_dt in date
467 ,p_pl_id in number
468 ,p_group_oipl_id in number default null
469 ,p_audit_type_cd in varchar2
470 ,p_old_val_varchar in varchar2 default null
471 ,p_new_val_varchar in varchar2 default null
472 ,p_old_val_number in number default null
473 ,p_new_val_number in number default null
474 ,p_old_val_date in date default null
475 ,p_new_val_date in date default null
476 ,p_date_stamp in date default null
477 ,p_change_made_by_person_id in number default null
478 ,p_supporting_information in varchar2 default null
479 ,p_request_id in number default null
480 ,p_cwb_audit_id out nocopy number
481 ,p_object_version_number out nocopy number
482 ) is
483 --
484 l_object_version_number number;
485 l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
486 l_change_made_by_person_id ben_cwb_audit.change_made_by_person_id%type;
487 --
488 l_proc varchar2(72) := g_package||'create_audit_entry';
489 /* l_change_made_by_person_id number;*/
490 begin
491
492 --hr_utility.trace_on(null,'audit1');
493 --g_debug:=true;
494
495 if g_debug then
496 hr_utility.set_location('Entering:'|| l_proc, 10);
497 end if;
498
499 /* removing because now receiving person_id from fnd_user.employee_id
500 begin
501 select person_id into l_change_made_by_person_id
502 from fnd_user fnduser, per_all_people_f per
503 where p_change_made_by_person_id=fnduser.user_id (+)
504 and fnduser.person_party_id = per.party_id (+)
505 and ((nvl(p_change_made_by_person_id, -1) = -1) or
506 (p_date_stamp between per.effective_start_date and per.effective_end_date));
507 exception
508 when no_data_found then
509 l_change_made_by_person_id := p_change_made_by_person_id;
510 end;
511 */
512
513 l_change_made_by_person_id := p_change_made_by_person_id;
514
515 if(p_change_made_by_person_id is null) then
516
517 if g_debug then
518 hr_utility.set_location('NULL p_change_made_by_person_id ', 12);
519 end if;
520
521 l_change_made_by_person_id := -1;
522 end if;
523
524
525 if g_debug then
526 hr_utility.set_location('l_change_made_by_person_id:'|| l_change_made_by_person_id||'END',11);
527 hr_utility.set_location('p_group_per_in_ler_id: '||p_group_per_in_ler_id,13);
528 hr_utility.set_location('p_group_pl_id: '||p_group_pl_id,14);
529 hr_utility.set_location('p_lf_evt_ocrd_dt: '||p_lf_evt_ocrd_dt,15);
530 hr_utility.set_location('p_pl_id: '||p_pl_id,16);
531 hr_utility.set_location('p_audit_type_cd: '||p_audit_type_cd,17);
532 end if;
533
534 --
535 -- Issue a savepoint
536 --
537 savepoint create_audit_entry;
538 --
539 -- Call Before Process User Hook
540 --
541 begin
542 ben_cwb_audit_bk1.create_audit_entry_b
543 (p_group_per_in_ler_id => p_group_per_in_ler_id
544 ,p_group_pl_id => p_group_pl_id
545 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
546 ,p_pl_id => p_pl_id
547 ,p_audit_type_cd => p_audit_type_cd
548 ,p_group_oipl_id => p_group_oipl_id
549 ,p_old_val_varchar => p_old_val_varchar
550 ,p_new_val_varchar => p_new_val_varchar
551 ,p_old_val_number => p_old_val_number
552 ,p_new_val_number => p_new_val_number
553 ,p_old_val_date => p_old_val_date
554 ,p_new_val_date => p_new_val_date
555 ,p_date_stamp => p_date_stamp
556 ,p_change_made_by_person_id => l_change_made_by_person_id
557 ,p_supporting_information => p_supporting_information
558 ,p_request_id => p_request_id
559 ,p_cwb_audit_id => l_cwb_audit_id
560 );
561 exception
562 when hr_api.cannot_find_prog_unit then
563 hr_api.cannot_find_prog_unit_error
564 (p_module_name => 'create_audit_entry'
565 ,p_hook_type => 'BP'
566 );
567 end;
568 --
569 -- Validation in addition to Row Handlers
570 --
571 --
572 -- Process Logic
573 --
574 ben_aud_ins.ins
575 (p_group_per_in_ler_id => p_group_per_in_ler_id
576 ,p_group_pl_id => p_group_pl_id
577 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
578 ,p_pl_id => p_pl_id
579 ,p_audit_type_cd => p_audit_type_cd
580 ,p_group_oipl_id => p_group_oipl_id
581 ,p_old_val_varchar => p_old_val_varchar
582 ,p_new_val_varchar => p_new_val_varchar
583 ,p_old_val_number => p_old_val_number
584 ,p_new_val_number => p_new_val_number
585 ,p_old_val_date => p_old_val_date
586 ,p_new_val_date => p_new_val_date
587 ,p_date_stamp => p_date_stamp
588 ,p_change_made_by_person_id => l_change_made_by_person_id
589 ,p_supporting_information => p_supporting_information
590 ,p_request_id => p_request_id
591 ,p_cwb_audit_id => l_cwb_audit_id
592 ,p_object_version_number => l_object_version_number
593 );
594 --
595 -- Call After Process User Hook
596 --
597 begin
598 ben_cwb_audit_bk1.create_audit_entry_a
599 (p_group_per_in_ler_id => p_group_per_in_ler_id
600 ,p_group_pl_id => p_group_pl_id
601 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
602 ,p_pl_id => p_pl_id
603 ,p_group_oipl_id => p_group_oipl_id
604 ,p_audit_type_cd => p_audit_type_cd
605 ,p_old_val_varchar => p_old_val_varchar
606 ,p_new_val_varchar => p_new_val_varchar
607 ,p_old_val_number => p_old_val_number
608 ,p_new_val_number => p_new_val_number
609 ,p_old_val_date => p_old_val_date
610 ,p_new_val_date => p_new_val_date
611 ,p_date_stamp => p_date_stamp
612 ,p_change_made_by_person_id => l_change_made_by_person_id
613 ,p_supporting_information => p_supporting_information
614 ,p_request_id => p_request_id
615 ,p_cwb_audit_id => l_cwb_audit_id
616 ,p_object_version_number => l_object_version_number
617 );
618 exception
619 when hr_api.cannot_find_prog_unit then
620 hr_api.cannot_find_prog_unit_error
621 (p_module_name => 'create_group_budget'
622 ,p_hook_type => 'AP'
623 );
624 end;
625 --
626 -- When in validation only mode raise the Validate_Enabled exception
627 --
628 if p_validate then
629 raise hr_api.validate_enabled;
630 end if;
631 --
632 -- Set all IN OUT and OUT parameters with out values
633 --
634 p_object_version_number := l_object_version_number;
635 --
636 if g_debug then
637 hr_utility.set_location(' Leaving:'||l_proc, 70);
638 end if;
639 exception
640 when hr_api.validate_enabled then
641 --
642 -- As the Validate_Enabled exception has been raised
643 -- we must rollback to the savepoint
644 --
645 rollback to create_audit_entry;
646 --
647 if g_debug then
648 hr_utility.set_location(' Leaving:'||l_proc, 80);
649 end if;
650 when others then
651 --
652 -- A validation or unexpected error has occured
653 --
654 rollback to create_audit_entry;
655 --
656 if g_debug then
657 hr_utility.set_location(' Leaving:'||l_proc, 90);
658 end if;
659 raise;
660 end create_audit_entry;
661 --
662 --
663 --
664 -- -------------------------------------------------------------------------
665 -- |--------------------------< update_audit_entry >------------------------|
666 -- -------------------------------------------------------------------------
667 --
668 procedure update_audit_entry
669 (p_validate in boolean default false
670 ,p_cwb_audit_id in number
671 ,p_group_per_in_ler_id in number
672 ,p_group_pl_id in number
673 ,p_lf_evt_ocrd_dt in date
674 ,p_pl_id in number
675 ,p_group_oipl_id in number default hr_api.g_number
676 ,p_audit_type_cd in varchar2
677 ,p_old_val_varchar in varchar2 default hr_api.g_varchar2
678 ,p_new_val_varchar in varchar2 default hr_api.g_varchar2
679 ,p_old_val_number in number default hr_api.g_number
680 ,p_new_val_number in number default hr_api.g_number
681 ,p_old_val_date in date default hr_api.g_date
682 ,p_new_val_date in date default hr_api.g_date
683 ,p_date_stamp in date default hr_api.g_date
684 ,p_change_made_by_person_id in number default hr_api.g_number
685 ,p_supporting_information in varchar2 default hr_api.g_varchar2
686 ,p_request_id in number default hr_api.g_number
687 ,p_object_version_number in out nocopy number
688 ) is
689 --
690 -- Declare cursors and local variables
691 --
692 l_object_version_number number;
693 /*l_change_made_by_person_id number;*/
694 --
695 l_proc varchar2(72) := g_package||'update_group_budget';
696 begin
697 /*
698 begin
699 select person_id into l_change_made_by_person_id
700 from fnd_user fnduser, per_all_people_f per
701 where p_change_made_by_person_id=fnduser.user_id (+)
702 and fnduser.person_party_id = per.party_id (+)
703 and ((nvl(p_change_made_by_person_id, -1) = -1) or
704 (p_date_stamp between per.effective_start_date and per.effective_end_date));
705 exception
706 when no_data_found then
707 l_change_made_by_person_id := p_change_made_by_person_id;
708 end;
709 */
710 if g_debug then
711 hr_utility.set_location('Entering:'|| l_proc, 10);
712 end if;
713 --
714 -- Issue a savepoint
715 --
716 savepoint update_audit_entry;
717 --
718 -- select the existing values from table.
719 --
720 -- Remember IN OUT parameter IN values
721 --
722 l_object_version_number := p_object_version_number;
723 --
724 -- Call Before Process User Hook
725 --
726 begin
727 ben_cwb_audit_bk2.update_audit_entry_b
728 (p_cwb_audit_id => p_cwb_audit_id
729 ,p_group_per_in_ler_id => p_group_per_in_ler_id
730 ,p_group_pl_id => p_group_pl_id
731 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
732 ,p_pl_id => p_pl_id
733 ,p_group_oipl_id => p_group_oipl_id
734 ,p_audit_type_cd => p_audit_type_cd
735 ,p_old_val_varchar => p_old_val_varchar
736 ,p_new_val_varchar => p_new_val_varchar
737 ,p_old_val_number => p_old_val_number
738 ,p_new_val_number => p_new_val_number
739 ,p_old_val_date => p_old_val_date
740 ,p_new_val_date => p_new_val_date
741 ,p_date_stamp => p_date_stamp
742 ,p_change_made_by_person_id => p_change_made_by_person_id
743 ,p_supporting_information => p_supporting_information
744 ,p_request_id => p_request_id
745 ,p_object_version_number => l_object_version_number
746 );
747 exception
748 when hr_api.cannot_find_prog_unit then
749 hr_api.cannot_find_prog_unit_error
750 (p_module_name => 'update_audit_entry'
751 ,p_hook_type => 'BP'
752 );
753 end;
754 --
755 -- Validation in addition to Row Handlers
756 --
757
758 -- Min Max Edits (removed)
759 --
760 --
761 if g_debug then
762 hr_utility.set_location(l_proc, 30);
763 end if;
764 --
765 --
766 if g_debug then
767 hr_utility.set_location(l_proc, 40);
768 end if;
769 --
770 --
771 if g_debug then
772 hr_utility.set_location(l_proc, 50);
773 end if;
774 --
775 --
776 if g_debug then
777 hr_utility.set_location(l_proc, 60);
778 end if;
779 --
780 --
781 -- Check Min, Max and Inc for Ws Bdgt Val
782 --
783 --
784 if g_debug then
785 hr_utility.set_location(l_proc, 70);
786 end if;
787 --
788 --
789 -- Check Min, Max and Inc for Rsrv Val
790 --
791 --
792 -- Process Logic
793 --
794 ben_aud_upd.upd
795 (p_cwb_audit_id => p_cwb_audit_id
796 ,p_group_per_in_ler_id => p_group_per_in_ler_id
797 ,p_group_pl_id => p_group_pl_id
798 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
799 ,p_pl_id => p_pl_id
800 ,p_group_oipl_id => p_group_oipl_id
801 ,p_audit_type_cd => p_audit_type_cd
802 ,p_old_val_varchar => p_old_val_varchar
803 ,p_new_val_varchar => p_new_val_varchar
804 ,p_old_val_number => p_old_val_number
805 ,p_new_val_number => p_new_val_number
806 ,p_old_val_date => p_old_val_date
807 ,p_new_val_date => p_new_val_date
808 ,p_date_stamp => p_date_stamp
809 ,p_change_made_by_person_id => p_change_made_by_person_id
810 ,p_supporting_information => p_supporting_information
811 ,p_request_id => p_request_id
812 ,p_object_version_number => l_object_version_number
813 );
814 --
815 -- Call After Process User Hook
816 --
817 begin
818 ben_cwb_audit_bk2.update_audit_entry_a
819 (p_cwb_audit_id => p_cwb_audit_id
820 ,p_group_per_in_ler_id => p_group_per_in_ler_id
821 ,p_group_pl_id => p_group_pl_id
822 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
823 ,p_pl_id => p_pl_id
824 ,p_group_oipl_id => p_group_oipl_id
825 ,p_audit_type_cd => p_audit_type_cd
826 ,p_old_val_varchar => p_old_val_varchar
827 ,p_new_val_varchar => p_new_val_varchar
828 ,p_old_val_number => p_old_val_number
829 ,p_new_val_number => p_new_val_number
830 ,p_old_val_date => p_old_val_date
831 ,p_new_val_date => p_new_val_date
832 ,p_date_stamp => p_date_stamp
833 ,p_change_made_by_person_id => p_change_made_by_person_id
834 ,p_supporting_information => p_supporting_information
835 ,p_request_id => p_request_id
836 ,p_object_version_number => l_object_version_number
837 );
838 exception
839 when hr_api.cannot_find_prog_unit then
840 hr_api.cannot_find_prog_unit_error
841 (p_module_name => 'update_audit_entry'
842 ,p_hook_type => 'AP'
843 );
844 end;
845 --
846 -- When in validation only mode raise the Validate_Enabled exception
847 --
848 if p_validate then
849 raise hr_api.validate_enabled;
850 end if;
851 --
852 -- Update is successful. So call the budget summary update.
853 --
854 -- Set all IN OUT and OUT parameters with out values
855 --
856 p_object_version_number := l_object_version_number;
857 --
858 if g_debug then
859 hr_utility.set_location(' Leaving:'||l_proc, 80);
860 end if;
861 exception
862 when hr_api.validate_enabled then
863 --
864 -- As the Validate_Enabled exception has been raised
865 -- we must rollback to the savepoint
866 --
867 rollback to update_audit_entry;
868 --
869 -- Reset IN OUT parameters and set OUT parameters
870 -- (Any key or derived arguments must be set to null
871 -- when validation only mode is being used.)
872 --
873 if g_debug then
874 hr_utility.set_location(' Leaving:'||l_proc, 90);
875 end if;
876 when others then
877 --
878 -- A validation or unexpected error has occured
879 --
880 rollback to update_audit_entry;
881 --
882 -- Reset IN OUT parameters and set all
883 -- OUT parameters, including warnings, to null
884 --
885 if g_debug then
886 hr_utility.set_location(' Leaving:'||l_proc, 99);
887 end if;
888 raise;
889 end update_audit_entry;
890 --
891 --
892 -- -------------------------------------------------------------------------
893 -- |-------------------------< delete_audit_entry >-------------------------|
894 -- -------------------------------------------------------------------------
895 --
896 procedure delete_audit_entry
897 (p_validate in boolean default false
898 ,p_cwb_audit_id in number
899 ,p_object_version_number in out nocopy number
900 ) is
901 --
902 -- Declare cursors and local variables
903 --
904 l_object_version_number number;
905 l_proc varchar2(72) := g_package||'delete_audit_entry';
906 begin
907 if g_debug then
908 hr_utility.set_location('Entering:'|| l_proc, 10);
909 end if;
910 --
911 -- Issue a savepoint
912 --
913 savepoint delete_audit_entry;
914 --
915 -- Remember IN OUT parameter IN values
916 --
917 --
918 l_object_version_number := p_object_version_number;
919 --
920 -- Call Before Process User Hook
921 --
922 begin
923 BEN_CWB_AUDIT_BK3.delete_audit_entry_b
924 (p_cwb_audit_id => p_cwb_audit_id
925 ,p_object_version_number => l_object_version_number
926 );
927 exception
928 when hr_api.cannot_find_prog_unit then
929 hr_api.cannot_find_prog_unit_error
930 (p_module_name => 'delete_audit_entry'
931 ,p_hook_type => 'BP'
932 );
933 end;
934 --
935 -- Validation in addition to Row Handlers
936 --
937
938 --
939 -- Process Logic
940 --
941 ben_aud_del.del
942 (p_cwb_audit_id => p_cwb_audit_id
943 ,p_object_version_number => l_object_version_number
944 );
945 --
946 -- Call After Process User Hook
947 --
948 begin
949 ben_cwb_audit_bk3.delete_audit_entry_a
950 (p_cwb_audit_id => p_cwb_audit_id
951 ,p_object_version_number => l_object_version_number
952 );
953 exception
954 when hr_api.cannot_find_prog_unit then
955 hr_api.cannot_find_prog_unit_error
956 (p_module_name => 'delete_audit_entry'
957 ,p_hook_type => 'AP'
958 );
959 end;
960 --
961 -- When in validation only mode raise the Validate_Enabled exception
962 --
963 if p_validate then
964 raise hr_api.validate_enabled;
965 end if;
966 --
967 if g_debug then
968 hr_utility.set_location(' Leaving:'||l_proc, 70);
969 end if;
970 exception
971 when hr_api.validate_enabled then
972 --
973 -- As the Validate_Enabled exception has been raised
974 -- we must rollback to the savepoint
975 --
976 rollback to delete_audit_entry;
977 --
978 if g_debug then
979 hr_utility.set_location(' Leaving:'||l_proc, 80);
980 end if;
981 when others then
982 --
983 -- A validation or unexpected error has occured
984 --
985 rollback to delete_audit_entry;
986 --
987 -- Reset IN OUT parameters and set all
988 -- OUT parameters, including warnings, to null
989 --
990 if g_debug then
991 hr_utility.set_location(' Leaving:'||l_proc, 90);
992 end if;
993 raise;
994 end delete_audit_entry;
995 --
996 --
997 -- ------------------------------------------------------------------------
998 -- |-------------------------< return_column_code >------------------------|
999 -- ------------------------------------------------------------------------
1000 --
1001 function return_column_code
1002 (p_lookup_code in varchar2
1003 )return number is
1004 p_code number;
1005 begin
1006 select decode(p_lookup_code,'BO',1
1007 ,'BG',1
1008 ,'EN',1
1009 ,'MG',1
1010 ,'RF',1
1011 ,'AD',3
1012 ,'AS',1
1013 ,'BS',2
1014 ,'BAD',2
1015 ,'BAA',2
1016 ,'BPA',2
1017 ,'BPD',2
1018 ,'BP',1
1019 ,'CF1',1
1020 ,'CF2',1
1021 ,'CF3',1
1022 ,'CF4',1
1023 ,'CF5',1
1024 ,'CF6',1
1025 ,'CF7',1
1026 ,'CF8',1
1027 ,'CF9',1
1028 ,'CF10',1
1029 ,'CF11',1
1030 ,'CF12',1
1031 ,'CF13',1
1032 ,'CF14',1
1033 ,'CF15',1
1034 ,'CF16',1
1035 ,'CF17',1
1036 ,'CF18',1
1037 ,'CF19',1
1038 ,'CF20',1
1039 ,'CF21',1
1040 ,'CF22',1
1041 ,'CF23',1
1042 ,'CF24',1
1043 ,'CF25',1
1044 ,'CF26',1
1045 ,'CF27',1
1046 ,'CF28',1
1047 ,'CF29',1
1048 ,'CF30',1
1049 ,'CR',1
1050 ,'CA',2
1051 ,'CU1',1
1052 ,'CU2',1
1053 ,'CU3',1
1054 ,'CU4',1
1055 ,'CU5',1
1056 ,'CU6',1
1057 ,'CU7',1
1058 ,'CU8',1
1059 ,'CU9',1
1060 ,'CU10',1
1061 ,'CU11',2
1062 ,'CU12',2
1063 ,'CU13',2
1064 ,'CU14',2
1065 ,'CU15',2
1066 ,'CU16',2
1067 ,'CU17',2
1068 ,'CU18',2
1069 ,'CU19',2
1070 ,'CU20',2
1071 ,'EL',1
1072 ,'ES',2
1073 ,'CM',1
1074 ,'ER',1
1075 ,'AC',1
1076 ,'M1',2
1077 ,'M2',2
1078 ,'M3',2
1079 ,'OC',2
1080 ,'PR',1
1081 ,'DD',3
1082 ,'AF1',1
1083 ,'AF10',1
1084 ,'AF11',1
1085 ,'AF12',1
1086 ,'AF13',1
1087 ,'AF14',1
1088 ,'AF15',1
1089 ,'AF16',1
1090 ,'AF17',1
1091 ,'AF18',1
1092 ,'AF19',1
1093 ,'AF20',1
1094 ,'AF21',1
1095 ,'AF22',1
1096 ,'AF23',1
1097 ,'AF24',1
1098 ,'AF25',1
1099 ,'AF26',1
1100 ,'AF27',1
1101 ,'AF28',1
1102 ,'AF29',1
1103 ,'AF30',1
1104 ,'AF2',1
1105 ,'AF3',1
1106 ,'AF4',1
1107 ,'AF5',1
1108 ,'AF6',1
1109 ,'AF7',1
1110 ,'AF8',1
1111 ,'AF9',1
1112 ,'GR',1
1113 ,'PG',1
1114 ,'JO',1
1115 ,'PO',1
1116 ,'SC',1
1117 ,'RA',2
1118 ,'RX',2
1119 ,'RN',2
1120 ,'RS',2
1121 ,'SS',2
1122 ,'SU',1
1123 ,'SD',3
1124 ,'TC',2
1125 ,'WX',2
1126 ,'WN',2
1127 ,1) into p_code from dual;
1128
1129 return p_code;
1130 exception
1131 when no_data_found then
1132 return 1;
1133 end return_column_code;
1134
1135 --
1136 --
1137 -- ------------------------------------------------------------------------
1138 -- |-------------------------< return_lookup_validity >------------------------|
1139 -- ------------------------------------------------------------------------
1140 --
1141 function return_lookup_validity
1142 (p_lookup_code in varchar2
1143 )return boolean is
1144 l_validity boolean;
1145 l_lookup hr_lookups%rowtype;
1146 l_audit_type_cd ben_cwb_audit.audit_type_cd%type;
1147 l_code_flag code_flag;
1148 l_found boolean;
1149 l_index number;
1150 begin
1151 l_validity := false;
1152 l_found := false;
1153
1154
1155 if g_debug then
1156 hr_utility.set_location('loop:'|| g_lookup_validity.COUNT, 23);
1157 end if;
1158
1159 if nvl(g_lookup_validity.COUNT,0) > 0 then
1160 FOR element IN 1..g_lookup_validity.COUNT loop
1161 if(g_lookup_validity.exists(element)) then
1162 if(g_lookup_validity(element).code = p_lookup_code) then
1163 l_found := true;
1164 l_index := element;
1165 if g_debug then
1166 hr_utility.set_location('found:'|| g_lookup_validity(element).code||element, 23);
1167 end if;
1168 end if;
1169 end if;
1170 exit when(l_found = true);
1171 end loop;
1172 end if;
1173
1174
1175 if(l_found = true) then
1176 if g_debug then
1177 hr_utility.set_location('found at: '|| l_index, 24);
1178 end if;
1179 if g_debug then
1180 hr_utility.set_location('with flag: '|| g_lookup_validity(l_index).flag, 25);
1181 end if;
1182 if(g_lookup_validity(l_index).flag = 'Y') then
1183 l_validity := true;
1184 else
1185 l_validity := false;
1186 end if;
1187
1188 else
1189
1190 begin
1191 select * into l_lookup
1192 from hr_lookups
1193 where lookup_type='BEN_CWB_AUDIT_TYPE'
1194 and lookup_code = p_lookup_code;
1195
1196 l_code_flag.code := l_lookup.lookup_code;
1197
1198 if( nvl(l_lookup.end_date_active,sysdate)<sysdate or (l_lookup.enabled_flag = 'N')) then
1199 l_lookup.enabled_flag := 'N';
1200 l_code_flag.flag := l_lookup.enabled_flag;
1201 g_lookup_validity.extend(1);
1202 g_lookup_validity(g_lookup_validity.last) := l_code_flag;
1203 l_validity := false;
1204
1205 if g_debug then
1206 hr_utility.set_location('flag: '||l_code_flag.flag, 25);
1207 end if;
1208
1209 else
1210 l_code_flag.flag := l_lookup.enabled_flag;
1211 g_lookup_validity.extend(1);
1212 g_lookup_validity(g_lookup_validity.last) := l_code_flag;
1213 l_validity := true;
1214 if g_debug then
1215 hr_utility.set_location('flag: '||l_code_flag.flag, 25);
1216 end if;
1217 end if;
1218 exception
1219 when no_data_found then
1220 l_validity := false;
1221 end;
1222
1223 end if;
1224
1225
1226 return l_validity;
1227
1228 end return_lookup_validity;
1229 --
1230 end ben_cwb_audit_api;