[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_AUDIT_API
Source
1 Package Body BEN_CWB_AUDIT_API as
2 /* $Header: beaudapi.pkb 120.6 2012/01/19 06:15:20 rpahune ship $ */
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 --11741025
442 if(p_audit_type_cd = 'BOA' ) then
443
444 if(ben_cwb_audit_api.return_lookup_validity('BOA')=true) then
445
446 ben_cwb_audit_api.create_audit_entry
447 (p_group_per_in_ler_id => l_per_record_new.per_in_ler_id
448 ,p_group_pl_id => l_per_record_new.group_pl_id
449 ,p_lf_evt_ocrd_dt => l_per_record_new.lf_evt_ocrd_dt
450 ,p_pl_id => l_per_record_new.group_pl_id
451 ,p_group_oipl_id => -1
452 ,p_audit_type_cd => 'BOA'
453 ,p_old_val_varchar => null
454 ,p_new_val_varchar => null
455 ,p_date_stamp => sysdate
456 ,p_change_made_by_person_id => l_person_id
457 ,p_cwb_audit_id => l_cwb_audit_id
458 ,p_object_version_number => l_object_version_number
459 );
460
461 if g_debug then
462 hr_utility.set_location('BOA done: '|| l_per_record_new.per_in_ler_id, 30);
463 hr_utility.set_location('BOA by: '|| l_person_id, 31);
464
465 end if;
466
467 end if;
468 end if;
469
470 exception
471 when no_data_found then
472 null;
473 end;
474
475 end if;
476
477 if g_debug then
478 hr_utility.set_location('Leaving:'|| l_proc, 200);
479 end if;
480
481
482 end update_per_record;
483
484
485
486 --
487 --
488 -- -----------------------------------------------------------------------
489 -- |--------------------------< create_audit_entry >----------------------|
490 -- -----------------------------------------------------------------------
491 procedure create_audit_entry
492 (p_validate in boolean default false
493 ,p_group_per_in_ler_id in number
494 ,p_group_pl_id in number
495 ,p_lf_evt_ocrd_dt in date
496 ,p_pl_id in number
497 ,p_group_oipl_id in number default null
498 ,p_audit_type_cd in varchar2
499 ,p_old_val_varchar in varchar2 default null
500 ,p_new_val_varchar in varchar2 default null
501 ,p_old_val_number in number default null
502 ,p_new_val_number in number default null
503 ,p_old_val_date in date default null
504 ,p_new_val_date in date default null
505 ,p_date_stamp in date default null
506 ,p_change_made_by_person_id in number default null
507 ,p_supporting_information in varchar2 default null
508 ,p_request_id in number default null
509 ,p_cwb_audit_id out nocopy number
510 ,p_object_version_number out nocopy number
511 ) is
512 --
513 l_object_version_number number;
514 l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
515 l_change_made_by_person_id ben_cwb_audit.change_made_by_person_id%type;
516 --
517 l_proc varchar2(72) := g_package||'create_audit_entry';
518 /* l_change_made_by_person_id number;*/
519 begin
520
521 --hr_utility.trace_on(null,'audit1');
522 --g_debug:=true;
523
524 if g_debug then
525 hr_utility.set_location('Entering:'|| l_proc, 10);
526 end if;
527
528 /* removing because now receiving person_id from fnd_user.employee_id
529 begin
530 select person_id into l_change_made_by_person_id
531 from fnd_user fnduser, per_all_people_f per
532 where p_change_made_by_person_id=fnduser.user_id (+)
533 and fnduser.person_party_id = per.party_id (+)
534 and ((nvl(p_change_made_by_person_id, -1) = -1) or
535 (p_date_stamp between per.effective_start_date and per.effective_end_date));
536 exception
537 when no_data_found then
538 l_change_made_by_person_id := p_change_made_by_person_id;
539 end;
540 */
541
542 l_change_made_by_person_id := p_change_made_by_person_id;
543
544 if(p_change_made_by_person_id is null) then
545
546 if g_debug then
547 hr_utility.set_location('NULL p_change_made_by_person_id ', 12);
548 end if;
549
550 l_change_made_by_person_id := -1;
551 end if;
552
553
554 if g_debug then
555 hr_utility.set_location('l_change_made_by_person_id:'|| l_change_made_by_person_id||'END',11);
556 hr_utility.set_location('p_group_per_in_ler_id: '||p_group_per_in_ler_id,13);
557 hr_utility.set_location('p_group_pl_id: '||p_group_pl_id,14);
558 hr_utility.set_location('p_lf_evt_ocrd_dt: '||p_lf_evt_ocrd_dt,15);
559 hr_utility.set_location('p_pl_id: '||p_pl_id,16);
560 hr_utility.set_location('p_audit_type_cd: '||p_audit_type_cd,17);
561 end if;
562
563 --
564 -- Issue a savepoint
565 --
566 savepoint create_audit_entry;
567 --
568 -- Call Before Process User Hook
569 --
570 begin
571 ben_cwb_audit_bk1.create_audit_entry_b
572 (p_group_per_in_ler_id => p_group_per_in_ler_id
573 ,p_group_pl_id => p_group_pl_id
574 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
575 ,p_pl_id => p_pl_id
576 ,p_audit_type_cd => p_audit_type_cd
577 ,p_group_oipl_id => p_group_oipl_id
578 ,p_old_val_varchar => p_old_val_varchar
579 ,p_new_val_varchar => p_new_val_varchar
580 ,p_old_val_number => p_old_val_number
581 ,p_new_val_number => p_new_val_number
582 ,p_old_val_date => p_old_val_date
583 ,p_new_val_date => p_new_val_date
584 ,p_date_stamp => p_date_stamp
585 ,p_change_made_by_person_id => l_change_made_by_person_id
586 ,p_supporting_information => p_supporting_information
587 ,p_request_id => p_request_id
588 ,p_cwb_audit_id => l_cwb_audit_id
589 );
590 exception
591 when hr_api.cannot_find_prog_unit then
592 hr_api.cannot_find_prog_unit_error
593 (p_module_name => 'create_audit_entry'
594 ,p_hook_type => 'BP'
595 );
596 end;
597 --
598 -- Validation in addition to Row Handlers
599 --
600 --
601 -- Process Logic
602 --
603 ben_aud_ins.ins
604 (p_group_per_in_ler_id => p_group_per_in_ler_id
605 ,p_group_pl_id => p_group_pl_id
606 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
607 ,p_pl_id => p_pl_id
608 ,p_audit_type_cd => p_audit_type_cd
609 ,p_group_oipl_id => p_group_oipl_id
610 ,p_old_val_varchar => p_old_val_varchar
611 ,p_new_val_varchar => p_new_val_varchar
612 ,p_old_val_number => p_old_val_number
613 ,p_new_val_number => p_new_val_number
614 ,p_old_val_date => p_old_val_date
615 ,p_new_val_date => p_new_val_date
616 ,p_date_stamp => p_date_stamp
617 ,p_change_made_by_person_id => l_change_made_by_person_id
618 ,p_supporting_information => p_supporting_information
619 ,p_request_id => p_request_id
620 ,p_cwb_audit_id => l_cwb_audit_id
621 ,p_object_version_number => l_object_version_number
622 );
623 --
624 -- Call After Process User Hook
625 --
626 begin
627 ben_cwb_audit_bk1.create_audit_entry_a
628 (p_group_per_in_ler_id => p_group_per_in_ler_id
629 ,p_group_pl_id => p_group_pl_id
630 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
631 ,p_pl_id => p_pl_id
632 ,p_group_oipl_id => p_group_oipl_id
633 ,p_audit_type_cd => p_audit_type_cd
634 ,p_old_val_varchar => p_old_val_varchar
635 ,p_new_val_varchar => p_new_val_varchar
636 ,p_old_val_number => p_old_val_number
637 ,p_new_val_number => p_new_val_number
638 ,p_old_val_date => p_old_val_date
639 ,p_new_val_date => p_new_val_date
640 ,p_date_stamp => p_date_stamp
641 ,p_change_made_by_person_id => l_change_made_by_person_id
642 ,p_supporting_information => p_supporting_information
643 ,p_request_id => p_request_id
644 ,p_cwb_audit_id => l_cwb_audit_id
645 ,p_object_version_number => l_object_version_number
646 );
647 exception
648 when hr_api.cannot_find_prog_unit then
649 hr_api.cannot_find_prog_unit_error
650 (p_module_name => 'create_group_budget'
651 ,p_hook_type => 'AP'
652 );
653 end;
654 --
655 -- When in validation only mode raise the Validate_Enabled exception
656 --
657 if p_validate then
658 raise hr_api.validate_enabled;
659 end if;
660 --
661 -- Set all IN OUT and OUT parameters with out values
662 --
663 p_object_version_number := l_object_version_number;
664 --
665 if g_debug then
666 hr_utility.set_location(' Leaving:'||l_proc, 70);
667 end if;
668 exception
669 when hr_api.validate_enabled then
670 --
671 -- As the Validate_Enabled exception has been raised
672 -- we must rollback to the savepoint
673 --
674 rollback to create_audit_entry;
675 --
676 if g_debug then
677 hr_utility.set_location(' Leaving:'||l_proc, 80);
678 end if;
679 when others then
680 --
681 -- A validation or unexpected error has occured
682 --
683 rollback to create_audit_entry;
684 --
685 if g_debug then
686 hr_utility.set_location(' Leaving:'||l_proc, 90);
687 end if;
688 raise;
689 end create_audit_entry;
690 --
691 --
692 --
693 -- -------------------------------------------------------------------------
694 -- |--------------------------< update_audit_entry >------------------------|
695 -- -------------------------------------------------------------------------
696 --
697 procedure update_audit_entry
698 (p_validate in boolean default false
699 ,p_cwb_audit_id in number
700 ,p_group_per_in_ler_id in number
701 ,p_group_pl_id in number
702 ,p_lf_evt_ocrd_dt in date
703 ,p_pl_id in number
704 ,p_group_oipl_id in number default hr_api.g_number
705 ,p_audit_type_cd in varchar2
706 ,p_old_val_varchar in varchar2 default hr_api.g_varchar2
707 ,p_new_val_varchar in varchar2 default hr_api.g_varchar2
708 ,p_old_val_number in number default hr_api.g_number
709 ,p_new_val_number in number default hr_api.g_number
710 ,p_old_val_date in date default hr_api.g_date
711 ,p_new_val_date in date default hr_api.g_date
712 ,p_date_stamp in date default hr_api.g_date
713 ,p_change_made_by_person_id in number default hr_api.g_number
714 ,p_supporting_information in varchar2 default hr_api.g_varchar2
715 ,p_request_id in number default hr_api.g_number
716 ,p_object_version_number in out nocopy number
717 ) is
718 --
719 -- Declare cursors and local variables
720 --
721 l_object_version_number number;
722 /*l_change_made_by_person_id number;*/
723 --
724 l_proc varchar2(72) := g_package||'update_group_budget';
725 begin
726 /*
727 begin
728 select person_id into l_change_made_by_person_id
729 from fnd_user fnduser, per_all_people_f per
730 where p_change_made_by_person_id=fnduser.user_id (+)
731 and fnduser.person_party_id = per.party_id (+)
732 and ((nvl(p_change_made_by_person_id, -1) = -1) or
733 (p_date_stamp between per.effective_start_date and per.effective_end_date));
734 exception
735 when no_data_found then
736 l_change_made_by_person_id := p_change_made_by_person_id;
737 end;
738 */
739 if g_debug then
740 hr_utility.set_location('Entering:'|| l_proc, 10);
741 end if;
742 --
743 -- Issue a savepoint
744 --
745 savepoint update_audit_entry;
746 --
747 -- select the existing values from table.
748 --
749 -- Remember IN OUT parameter IN values
750 --
751 l_object_version_number := p_object_version_number;
752 --
753 -- Call Before Process User Hook
754 --
755 begin
756 ben_cwb_audit_bk2.update_audit_entry_b
757 (p_cwb_audit_id => p_cwb_audit_id
758 ,p_group_per_in_ler_id => p_group_per_in_ler_id
759 ,p_group_pl_id => p_group_pl_id
760 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
761 ,p_pl_id => p_pl_id
762 ,p_group_oipl_id => p_group_oipl_id
763 ,p_audit_type_cd => p_audit_type_cd
764 ,p_old_val_varchar => p_old_val_varchar
765 ,p_new_val_varchar => p_new_val_varchar
766 ,p_old_val_number => p_old_val_number
767 ,p_new_val_number => p_new_val_number
768 ,p_old_val_date => p_old_val_date
769 ,p_new_val_date => p_new_val_date
770 ,p_date_stamp => p_date_stamp
771 ,p_change_made_by_person_id => p_change_made_by_person_id
772 ,p_supporting_information => p_supporting_information
773 ,p_request_id => p_request_id
774 ,p_object_version_number => l_object_version_number
775 );
776 exception
777 when hr_api.cannot_find_prog_unit then
778 hr_api.cannot_find_prog_unit_error
779 (p_module_name => 'update_audit_entry'
780 ,p_hook_type => 'BP'
781 );
782 end;
783 --
784 -- Validation in addition to Row Handlers
785 --
786
787 -- Min Max Edits (removed)
788 --
789 --
790 if g_debug then
791 hr_utility.set_location(l_proc, 30);
792 end if;
793 --
794 --
795 if g_debug then
796 hr_utility.set_location(l_proc, 40);
797 end if;
798 --
799 --
800 if g_debug then
801 hr_utility.set_location(l_proc, 50);
802 end if;
803 --
804 --
805 if g_debug then
806 hr_utility.set_location(l_proc, 60);
807 end if;
808 --
809 --
810 -- Check Min, Max and Inc for Ws Bdgt Val
811 --
812 --
813 if g_debug then
814 hr_utility.set_location(l_proc, 70);
815 end if;
816 --
817 --
818 -- Check Min, Max and Inc for Rsrv Val
819 --
820 --
821 -- Process Logic
822 --
823 ben_aud_upd.upd
824 (p_cwb_audit_id => p_cwb_audit_id
825 ,p_group_per_in_ler_id => p_group_per_in_ler_id
826 ,p_group_pl_id => p_group_pl_id
827 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
828 ,p_pl_id => p_pl_id
829 ,p_group_oipl_id => p_group_oipl_id
830 ,p_audit_type_cd => p_audit_type_cd
831 ,p_old_val_varchar => p_old_val_varchar
832 ,p_new_val_varchar => p_new_val_varchar
833 ,p_old_val_number => p_old_val_number
834 ,p_new_val_number => p_new_val_number
835 ,p_old_val_date => p_old_val_date
836 ,p_new_val_date => p_new_val_date
837 ,p_date_stamp => p_date_stamp
838 ,p_change_made_by_person_id => p_change_made_by_person_id
839 ,p_supporting_information => p_supporting_information
840 ,p_request_id => p_request_id
841 ,p_object_version_number => l_object_version_number
842 );
843 --
844 -- Call After Process User Hook
845 --
846 begin
847 ben_cwb_audit_bk2.update_audit_entry_a
848 (p_cwb_audit_id => p_cwb_audit_id
849 ,p_group_per_in_ler_id => p_group_per_in_ler_id
850 ,p_group_pl_id => p_group_pl_id
851 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
852 ,p_pl_id => p_pl_id
853 ,p_group_oipl_id => p_group_oipl_id
854 ,p_audit_type_cd => p_audit_type_cd
855 ,p_old_val_varchar => p_old_val_varchar
856 ,p_new_val_varchar => p_new_val_varchar
857 ,p_old_val_number => p_old_val_number
858 ,p_new_val_number => p_new_val_number
859 ,p_old_val_date => p_old_val_date
860 ,p_new_val_date => p_new_val_date
861 ,p_date_stamp => p_date_stamp
862 ,p_change_made_by_person_id => p_change_made_by_person_id
863 ,p_supporting_information => p_supporting_information
864 ,p_request_id => p_request_id
865 ,p_object_version_number => l_object_version_number
866 );
867 exception
868 when hr_api.cannot_find_prog_unit then
869 hr_api.cannot_find_prog_unit_error
870 (p_module_name => 'update_audit_entry'
871 ,p_hook_type => 'AP'
872 );
873 end;
874 --
875 -- When in validation only mode raise the Validate_Enabled exception
876 --
877 if p_validate then
878 raise hr_api.validate_enabled;
879 end if;
880 --
881 -- Update is successful. So call the budget summary update.
882 --
883 -- Set all IN OUT and OUT parameters with out values
884 --
885 p_object_version_number := l_object_version_number;
886 --
887 if g_debug then
888 hr_utility.set_location(' Leaving:'||l_proc, 80);
889 end if;
890 exception
891 when hr_api.validate_enabled then
892 --
893 -- As the Validate_Enabled exception has been raised
894 -- we must rollback to the savepoint
895 --
896 rollback to update_audit_entry;
897 --
898 -- Reset IN OUT parameters and set OUT parameters
899 -- (Any key or derived arguments must be set to null
900 -- when validation only mode is being used.)
901 --
902 if g_debug then
903 hr_utility.set_location(' Leaving:'||l_proc, 90);
904 end if;
905 when others then
906 --
907 -- A validation or unexpected error has occured
908 --
909 rollback to update_audit_entry;
910 --
911 -- Reset IN OUT parameters and set all
912 -- OUT parameters, including warnings, to null
913 --
914 if g_debug then
915 hr_utility.set_location(' Leaving:'||l_proc, 99);
916 end if;
917 raise;
918 end update_audit_entry;
919 --
920 --
921 -- -------------------------------------------------------------------------
922 -- |-------------------------< delete_audit_entry >-------------------------|
923 -- -------------------------------------------------------------------------
924 --
925 procedure delete_audit_entry
926 (p_validate in boolean default false
927 ,p_cwb_audit_id in number
928 ,p_object_version_number in out nocopy number
929 ) is
930 --
931 -- Declare cursors and local variables
932 --
933 l_object_version_number number;
934 l_proc varchar2(72) := g_package||'delete_audit_entry';
935 begin
936 if g_debug then
937 hr_utility.set_location('Entering:'|| l_proc, 10);
938 end if;
939 --
940 -- Issue a savepoint
941 --
942 savepoint delete_audit_entry;
943 --
944 -- Remember IN OUT parameter IN values
945 --
946 --
947 l_object_version_number := p_object_version_number;
948 --
949 -- Call Before Process User Hook
950 --
951 begin
952 BEN_CWB_AUDIT_BK3.delete_audit_entry_b
953 (p_cwb_audit_id => p_cwb_audit_id
954 ,p_object_version_number => l_object_version_number
955 );
956 exception
957 when hr_api.cannot_find_prog_unit then
958 hr_api.cannot_find_prog_unit_error
959 (p_module_name => 'delete_audit_entry'
960 ,p_hook_type => 'BP'
961 );
962 end;
963 --
964 -- Validation in addition to Row Handlers
965 --
966
967 --
968 -- Process Logic
969 --
970 ben_aud_del.del
971 (p_cwb_audit_id => p_cwb_audit_id
972 ,p_object_version_number => l_object_version_number
973 );
974 --
975 -- Call After Process User Hook
976 --
977 begin
978 ben_cwb_audit_bk3.delete_audit_entry_a
979 (p_cwb_audit_id => p_cwb_audit_id
980 ,p_object_version_number => l_object_version_number
981 );
982 exception
983 when hr_api.cannot_find_prog_unit then
984 hr_api.cannot_find_prog_unit_error
985 (p_module_name => 'delete_audit_entry'
986 ,p_hook_type => 'AP'
987 );
988 end;
989 --
990 -- When in validation only mode raise the Validate_Enabled exception
991 --
992 if p_validate then
993 raise hr_api.validate_enabled;
994 end if;
995 --
996 if g_debug then
997 hr_utility.set_location(' Leaving:'||l_proc, 70);
998 end if;
999 exception
1000 when hr_api.validate_enabled then
1001 --
1002 -- As the Validate_Enabled exception has been raised
1003 -- we must rollback to the savepoint
1004 --
1005 rollback to delete_audit_entry;
1006 --
1007 if g_debug then
1008 hr_utility.set_location(' Leaving:'||l_proc, 80);
1009 end if;
1010 when others then
1011 --
1012 -- A validation or unexpected error has occured
1013 --
1014 rollback to delete_audit_entry;
1015 --
1016 -- Reset IN OUT parameters and set all
1017 -- OUT parameters, including warnings, to null
1018 --
1019 if g_debug then
1020 hr_utility.set_location(' Leaving:'||l_proc, 90);
1021 end if;
1022 raise;
1023 end delete_audit_entry;
1024 --
1025 --
1026 -- ------------------------------------------------------------------------
1027 -- |-------------------------< return_column_code >------------------------|
1028 -- ------------------------------------------------------------------------
1029 --
1030 function return_column_code
1031 (p_lookup_code in varchar2
1032 )return number is
1033 p_code number;
1034 begin
1035 select decode(p_lookup_code,'BO',1
1036 ,'BG',1
1037 ,'EN',1
1038 ,'MG',1
1039 ,'RF',1
1040 ,'AD',3
1041 ,'AS',1
1042 ,'BS',2
1043 ,'BAD',2
1044 ,'BAA',2
1045 ,'BPA',2
1046 ,'BPD',2
1047 ,'BP',1
1048 ,'CF1',1
1049 ,'CF2',1
1050 ,'CF3',1
1051 ,'CF4',1
1052 ,'CF5',1
1053 ,'CF6',1
1054 ,'CF7',1
1055 ,'CF8',1
1056 ,'CF9',1
1057 ,'CF10',1
1058 ,'CF11',1
1059 ,'CF12',1
1060 ,'CF13',1
1061 ,'CF14',1
1062 ,'CF15',1
1063 ,'CF16',1
1064 ,'CF17',1
1065 ,'CF18',1
1066 ,'CF19',1
1067 ,'CF20',1
1068 ,'CF21',1
1069 ,'CF22',1
1070 ,'CF23',1
1071 ,'CF24',1
1072 ,'CF25',1
1073 ,'CF26',1
1074 ,'CF27',1
1075 ,'CF28',1
1076 ,'CF29',1
1077 ,'CF30',1
1078 ,'CR',1
1079 ,'CA',2
1080 ,'CU1',1
1081 ,'CU2',1
1082 ,'CU3',1
1083 ,'CU4',1
1084 ,'CU5',1
1085 ,'CU6',1
1086 ,'CU7',1
1087 ,'CU8',1
1088 ,'CU9',1
1089 ,'CU10',1
1090 ,'CU11',2
1091 ,'CU12',2
1092 ,'CU13',2
1093 ,'CU14',2
1094 ,'CU15',2
1095 ,'CU16',2
1096 ,'CU17',2
1097 ,'CU18',2
1098 ,'CU19',2
1099 ,'CU20',2
1100 ,'EL',1
1101 ,'ES',2
1102 ,'CM',1
1103 ,'ER',1
1104 ,'AC',1
1105 ,'M1',2
1106 ,'M2',2
1107 ,'M3',2
1108 ,'OC',2
1109 ,'PR',1
1110 ,'DD',3
1111 ,'AF1',1
1112 ,'AF10',1
1113 ,'AF11',1
1114 ,'AF12',1
1115 ,'AF13',1
1116 ,'AF14',1
1117 ,'AF15',1
1118 ,'AF16',1
1119 ,'AF17',1
1120 ,'AF18',1
1121 ,'AF19',1
1122 ,'AF20',1
1123 ,'AF21',1
1124 ,'AF22',1
1125 ,'AF23',1
1126 ,'AF24',1
1127 ,'AF25',1
1128 ,'AF26',1
1129 ,'AF27',1
1130 ,'AF28',1
1131 ,'AF29',1
1132 ,'AF30',1
1133 ,'AF2',1
1134 ,'AF3',1
1135 ,'AF4',1
1136 ,'AF5',1
1137 ,'AF6',1
1138 ,'AF7',1
1139 ,'AF8',1
1140 ,'AF9',1
1141 ,'GR',1
1142 ,'PG',1
1143 ,'JO',1
1144 ,'PO',1
1145 ,'SC',1
1146 ,'RA',2
1147 ,'RX',2
1148 ,'RN',2
1149 ,'RS',2
1150 ,'SS',2
1151 ,'SU',1
1152 ,'SD',3
1153 ,'TC',2
1154 ,'WX',2
1155 ,'WN',2
1156 ,1) into p_code from dual;
1157
1158 return p_code;
1159 exception
1160 when no_data_found then
1161 return 1;
1162 end return_column_code;
1163
1164 --
1165 --
1166 -- ------------------------------------------------------------------------
1167 -- |-------------------------< return_lookup_validity >------------------------|
1168 -- ------------------------------------------------------------------------
1169 --
1170 function return_lookup_validity
1171 (p_lookup_code in varchar2
1172 )return boolean is
1173 l_validity boolean;
1174 l_lookup hr_lookups%rowtype;
1175 l_audit_type_cd ben_cwb_audit.audit_type_cd%type;
1176 l_code_flag code_flag;
1177 l_found boolean;
1178 l_index number;
1179 begin
1180 l_validity := false;
1181 l_found := false;
1182
1183
1184 if g_debug then
1185 hr_utility.set_location('loop:'|| g_lookup_validity.COUNT, 23);
1186 end if;
1187
1188 if nvl(g_lookup_validity.COUNT,0) > 0 then
1189 FOR element IN 1..g_lookup_validity.COUNT loop
1190 if(g_lookup_validity.exists(element)) then
1191 if(g_lookup_validity(element).code = p_lookup_code) then
1192 l_found := true;
1193 l_index := element;
1194 if g_debug then
1195 hr_utility.set_location('found:'|| g_lookup_validity(element).code||element, 23);
1196 end if;
1197 end if;
1198 end if;
1199 exit when(l_found = true);
1200 end loop;
1201 end if;
1202
1203
1204 if(l_found = true) then
1205 if g_debug then
1206 hr_utility.set_location('found at: '|| l_index, 24);
1207 end if;
1208 if g_debug then
1209 hr_utility.set_location('with flag: '|| g_lookup_validity(l_index).flag, 25);
1210 end if;
1211 if(g_lookup_validity(l_index).flag = 'Y') then
1212 l_validity := true;
1213 else
1214 l_validity := false;
1215 end if;
1216
1217 else
1218
1219 begin
1220 select * into l_lookup
1221 from hr_lookups
1222 where lookup_type='BEN_CWB_AUDIT_TYPE'
1223 and lookup_code = p_lookup_code;
1224
1225 l_code_flag.code := l_lookup.lookup_code;
1226
1227 if( nvl(l_lookup.end_date_active,sysdate)<sysdate or (l_lookup.enabled_flag = 'N')) then
1228 l_lookup.enabled_flag := 'N';
1229 l_code_flag.flag := l_lookup.enabled_flag;
1230 g_lookup_validity.extend(1);
1231 g_lookup_validity(g_lookup_validity.last) := l_code_flag;
1232 l_validity := false;
1233
1234 if g_debug then
1235 hr_utility.set_location('flag: '||l_code_flag.flag, 25);
1236 end if;
1237
1238 else
1239 l_code_flag.flag := l_lookup.enabled_flag;
1240 g_lookup_validity.extend(1);
1241 g_lookup_validity(g_lookup_validity.last) := l_code_flag;
1242 l_validity := true;
1243 if g_debug then
1244 hr_utility.set_location('flag: '||l_code_flag.flag, 25);
1245 end if;
1246 end if;
1247 exception
1248 when no_data_found then
1249 l_validity := false;
1250 end;
1251
1252 end if;
1253
1254
1255 return l_validity;
1256
1257 end return_lookup_validity;
1258 --
1259 end ben_cwb_audit_api;