[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_ASG_UPDATE
Source
1 PACKAGE BODY BEN_CWB_ASG_UPDATE as
2 /* $Header: bencwbau.pkb 120.9.12010000.2 2008/08/05 14:37:03 ubhat ship $ */
3 /* ===========================================================================+
4 * Name
5 * Compensation Workbench Transaction Update Package
6 * Purpose
7 * This package is used to insert record into ben_transaction table
8 * when performance rating or promotion details
9 * are updated on the Worksheet.
10 *
11 * Version Date Author Comment
12 * -------+-----------+---------+----------------------------------------------
13 * 115.0 15-Feb-2003 maagrawa created
14 * 115.1 05-Mar-2003 maagrawa Added fnd_msg_pub.add to stack error msg.
15 * 115.2 10-Mar-2003 maagrawa Flex Errors thrown by AOL and HR are now
16 * available in pl/sql error stack.
17 * 115.3 20-Mar-2003 maagrawa If Performance Type is not defined then
18 * performance rating updates are done with
19 * no events. (Enh 2847438).
20 * 115.4 02-Apr-2003 maagrawa Bug 2876281.
21 * Mask the perf api error with user
22 * friendly BEN error.
23 * 115.5 12-Jan-2004 maagrawa Global Budgeting Change.
24 * 115.6 25-Mar-2004 maagrawa Add a general message before the errors
25 * received from HR apis for SS.
26 * 115.7 29-Mar-2004 maagrawa Update ben_cwb_person_info with perf/promo
27 * ids when called from PP.
28 * 115.8 25-May-2004 maagrawa Splitting of perf/promo record and have
29 * them floating across plans.
30 * 115.9 01-Jul-2004 maagrawa Do not validate data in online mode.(Temp)
31 * 115.10 14-Jul-2004 aprabhak Added get_update_mode routine to get the
32 * date track mode.
33 * 115.11 07-Dec-2004 maagrawa Error if perf date or promo date not defined.
34 * 115.12 22-Dec-2004 steotia Added create_audit_record and audit writing
35 * functionality
36 * 115.13 22-Dec-2004 steotia Taking care of null emp_interview_typ_cd
37 * Adding exception block to select stmts.
38 * 115.14 03-Jan-2005 steotia Bug 4083398 - no data found correction
39 * 115.16 07-Jan-2005 steotia Added SCL audit event for promotions.
40 * Corrected cursor for promotions.
41 * 115.17 11-Jan-2005 steotia Writing fnd_user.employee_id
42 * 115.18 02-Feb-2005 steotia Bug 4150327
43 * 115.19 10-Feb-2005 maagrawa Pass group_pl_id as plan reference to
44 * process_rating, process_promotions.
45 * 115.20 20-Feb-2005 steotia Performance improvement(audit):
46 * Replacing select into by explicit cursors
47 * 115.21 06-Apr-2005 steotia Bugfix 4273451
48 * 115.22 26-Sep-2005 steotia Bugfix 4618895: Taking in 'PROCD' employees
49 * also for audit.
50 * 115.23 06-Oct-2005 steotia Bugfix 4607721: Modified process_rating.
51 * 115.24 19-Oct-2005 steotia Bugfix 4607721: Corrected event comparison.
52 * 115.25 27-Oct-2005 maagrawa 4704175. Removed to_date on attribute1.
53 * 115.26 20-Dec-2005 maagrawa Valid online changes only if the profile
54 * BEN_CWB_ASG_PERF_VALIDATE is "Yes".
55 * 115.27 16-Feb-2006 maagrawa Null the new position id when job changes
56 * with no position change and the employee
57 * previously had a position.
58 * 115.28 28-Mar-2006 maagrawa PERF: SQL Repository changes.
59 * 115.29 29-Mar-2006 maagrawa Re-sync with R12 version.
60 * 115.30 01-Aug-2006 maagrawa 3227317. If people group involved, call the
61 * api to have group name appended.
62 * 115.31 20-Sep-2006 steotia 5531065: Using Performance Overrides (but
63 * only if used through SS)
64 * 115.32 13-Jun-2008 sgnanama 7167952:Used substr in cursors c_job
65 * c_grade and c_position
66 * ==========================================================================+
67 */
68
69 g_package varchar2(80) := 'ben_cwb_asg_update.';
70 g_debug boolean := hr_utility.debug_enabled;
71 g_validate varchar2(30) := fnd_profile.value('BEN_CWB_ASG_PERF_VALIDATE');
72
73
74 FUNCTION get_update_mode(
75 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
76 ,p_ovn IN per_all_assignments_f.object_version_number%TYPE
77 ,p_effective_date IN DATE
78 )
79 RETURN VARCHAR2
80 IS
81 l_correction boolean;
82 l_update boolean;
83 l_update_override boolean;
84 l_update_change_insert boolean;
85
86 l_datetrack_update_mode varchar2(100);
87
88 l_validation_start_date date;
89 l_validation_end_date date;
90
91 begin
92
93 per_asg_shd.lck
94 (p_effective_date => p_effective_date
95 ,p_datetrack_mode => hr_api.g_correction
96 ,p_assignment_id => p_assignment_id
97 ,p_object_version_number => p_ovn
98 ,p_validation_start_date => l_validation_start_date
99 ,p_validation_end_date => l_validation_end_date);
100
101 per_asg_shd.find_dt_upd_modes
102 (p_effective_date => p_effective_date
103 ,p_base_key_value => p_assignment_id
104 ,p_correction => l_correction
105 ,p_update => l_update
106 ,p_update_override => l_update_override
107 ,p_update_change_insert => l_update_change_insert);
108
109 if l_update then
110 -- we can do an update
111 l_datetrack_update_mode := hr_api.g_update;
112 elsif l_update_change_insert then
113 -- we can do an update change insert
114 l_datetrack_update_mode := hr_api.g_update_change_insert;
115 elsif (l_validation_start_date = p_effective_date) and
116 l_correction then
117 -- we can only perform a correction
118 l_datetrack_update_mode := hr_api.g_correction;
119 else
120 -- we cannot perform an update due to a restriction within the APIs
121 l_datetrack_update_mode := hr_api.g_update;
122 end if;
123
124 RETURN l_datetrack_update_mode;
125 END;
126
127
128
129
130
131
132 procedure delete_transaction
133 (p_transaction_id in number
134 ,p_transaction_type in varchar2) is
135 begin
136 delete ben_transaction
137 where transaction_id = p_transaction_id
138 and transaction_type = p_transaction_type;
139 end;
140 --
141 --
142 -- -------------------------------------------------------------------------
143 -- |-------------------------< create_audit_record_rating >-----------------|
144 -- -------------------------------------------------------------------------
145 --
146 -- Description
147 -- This is an internal procedure
148 --
149 procedure create_audit_record_rating
150 (p_txn_old in ben_transaction%rowtype
151 ,p_group_pl_id in number
152 ) is
153
154 l_txn_new ben_transaction%rowtype;
155 l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
156 l_object_version_number ben_cwb_audit.object_version_number%type;
157 l_cd_meaning_old hr_lookups.meaning%type;
158 l_cd_meaning_new hr_lookups.meaning%type;
159 l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
160 l_pl_id ben_cwb_pl_dsgn.pl_id%type;
161 l_lf_evt_ocrd_dt ben_cwb_pl_dsgn.lf_evt_ocrd_dt%type;
162 l_person_id fnd_user.employee_id%type;
163 --
164 cursor c_per_in_ler is
165 select inf.group_per_in_ler_id
166 ,inf.group_pl_id pl_id
167 ,inf.lf_evt_ocrd_dt
168 from ben_cwb_person_info inf
169 ,ben_cwb_pl_dsgn pl
170 where inf.assignment_id = p_txn_old.transaction_id
171 and inf.group_pl_id = p_group_pl_id
172 and inf.group_pl_id = pl.pl_id
173 and inf.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
174 and pl.oipl_id = -1
175 and to_char(pl.perf_revw_strt_dt,'yyyy/mm/dd') = p_txn_old.attribute1
176 and nvl(pl.emp_interview_typ_cd,'-1') = nvl(p_txn_old.attribute2,'-1');
177 --
178 cursor c_lookup(v_lookup_type varchar2
179 ,v_lookup_code varchar2) is
180 select meaning
181 from hr_lookups
182 where lookup_type = v_lookup_type
183 and lookup_code = v_lookup_code;
184 --
185 cursor c_person_id
186 (l_user_id fnd_user.employee_id%type) is
187 select employee_id
188 from fnd_user
189 where user_id = l_user_id;
190 --
191 cursor c_collect_record_rating
192 (p_assgn_id number
193 ,p_trans_type varchar2) is
194 select *
195 from ben_transaction
196 where transaction_id = p_assgn_id
197 and transaction_type = p_trans_type;
198 --
199
200 l_proc varchar2(72) := g_package||'create_audit_record_rating';
201
202 --
203 begin
204 --
205 if g_debug then
206 hr_utility.set_location('Entering:'|| l_proc,2);
207 hr_utility.set_location('p_txn_old.attribute1: ' || p_txn_old.attribute1 , 3);
208 hr_utility.set_location('p_txn_old.attribute2: ' || p_txn_old.attribute2, 5);
209 hr_utility.set_location('p_txn_old.transaction_id: ' || p_txn_old.transaction_id, 6);
210 end if;
211
212
213 l_txn_new := null;
214 open c_person_id(fnd_global.user_id);
215 fetch c_person_id into l_person_id;
216 close c_person_id;
217 --
218 open c_per_in_ler;
219 fetch c_per_in_ler into l_per_in_ler_id, l_pl_id, l_lf_evt_ocrd_dt;
220 close c_per_in_ler;
221 --
222 if g_debug then
223 hr_utility.set_location('l_per_in_ler_id: ' || l_per_in_ler_id, 6);
224 hr_utility.set_location('l_pl_id: ' || l_pl_id, 7);
225 hr_utility.set_location('l_lf_evt_ocrd_dt: ' || l_lf_evt_ocrd_dt, 10);
226 end if;
227
228 begin
229 select * into l_txn_new
230 from ben_transaction
231 where transaction_id = p_txn_old.transaction_id
232 and transaction_type = p_txn_old.transaction_type;
233 --
234 if( ((p_txn_old.attribute3 is null)
235 and (l_txn_new.attribute3 is not null))
236 or ((l_txn_new.attribute3 is null)
237 and (p_txn_old.attribute3 is not null))
238 or (p_txn_old.attribute3 <> l_txn_new.attribute3) ) then
239 -- write the meaning
240 open c_lookup('PERFORMANCE_RATING', p_txn_old.attribute3);
241 fetch c_lookup into l_cd_meaning_old;
242 close c_lookup;
243 --
244 open c_lookup('PERFORMANCE_RATING', l_txn_new.attribute3);
245 fetch c_lookup into l_cd_meaning_new;
246 close c_lookup;
247 --
248 --
249 if(ben_cwb_audit_api.return_lookup_validity('PR')=true) then
250 ben_cwb_audit_api.create_audit_entry
251 (p_group_per_in_ler_id => l_per_in_ler_id
252 ,p_group_pl_id => l_pl_id
253 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
254 ,p_pl_id => l_pl_id
255 ,p_group_oipl_id => -1
256 ,p_audit_type_cd => 'PR'
257 ,p_old_val_varchar => l_cd_meaning_old
258 ,p_new_val_varchar => l_cd_meaning_new
259 ,p_date_stamp => sysdate
260 ,p_change_made_by_person_id => l_person_id
261 ,p_cwb_audit_id => l_cwb_audit_id
262 ,p_object_version_number => l_object_version_number
263 );
264 end if;
265 end if;
266 exception
267 when no_data_found then
268 if(p_txn_old.attribute3 is not null) then
269 -- write the meaning
270 open c_lookup('PERFORMANCE_RATING', p_txn_old.attribute3);
271 fetch c_lookup into l_cd_meaning_old;
272 close c_lookup;
273 --
274 l_cd_meaning_new := null;
275 --
276 --
277 if(ben_cwb_audit_api.return_lookup_validity('PR')=true) then
278 ben_cwb_audit_api.create_audit_entry
279 (p_group_per_in_ler_id => l_per_in_ler_id
280 ,p_group_pl_id => l_pl_id
281 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
282 ,p_pl_id => l_pl_id
283 ,p_group_oipl_id => -1
284 ,p_audit_type_cd => 'PR'
285 ,p_old_val_varchar => l_cd_meaning_old
286 ,p_new_val_varchar => l_cd_meaning_new
287 ,p_date_stamp => sysdate
288 ,p_change_made_by_person_id => l_person_id
289 ,p_cwb_audit_id => l_cwb_audit_id
290 ,p_object_version_number => l_object_version_number
291 );
292 end if;
293 end if;
294 end;
295 --
296 --
297 end create_audit_record_rating;
298 --
299 --
300 -- ------------------------------------------------------------------------
301 -- |---------------------< create_audit_record_promotion >-----------------|
302 -- ------------------------------------------------------------------------
303 --
304 -- Description
305 -- This is an internal procedure
306 --
307 procedure create_audit_record_promotion
308 (p_txn_old in ben_transaction%rowtype
309 ,p_group_pl_id in number
310 ) is
311
312 l_txn_new ben_transaction%rowtype; --- line 118 ---
313 l_cwb_audit_id ben_cwb_audit.cwb_audit_id%type;
314 l_object_version_number ben_cwb_audit.object_version_number%type;
315 l_cd_meaning_old varchar2(240);
316 l_cd_meaning_new varchar2(240);
317 l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
318 l_pl_id ben_cwb_pl_dsgn.pl_id%type;
319 l_lf_evt_ocrd_dt ben_cwb_pl_dsgn.lf_evt_ocrd_dt%type;
320 l_lang varchar2(30);
321 l_temp varchar2(30);
322 l_person_id fnd_user.employee_id%type;
323
324 --
325 cursor c_per_in_ler is
326 select inf.group_per_in_ler_id
327 ,inf.group_pl_id pl_id
328 ,inf.lf_evt_ocrd_dt
329 from ben_cwb_person_info inf
330 ,ben_cwb_pl_dsgn pl
331 where inf.assignment_id = p_txn_old.transaction_id
332 and inf.group_pl_id = p_group_pl_id
333 and inf.group_pl_id = pl.pl_id
334 and inf.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
335 and pl.oipl_id = -1
336 and to_char(pl.asg_updt_eff_date,'yyyy/mm/dd') = p_txn_old.attribute1;
337 --
338 cursor c_lookup(v_lookup_type varchar2
339 ,v_lookup_code varchar2) is
340 select meaning
341 from hr_lookups
342 where lookup_type = v_lookup_type
343 and lookup_code = v_lookup_code;
344 --
345 cursor c_person_id is
346 select employee_id
347 from fnd_user
348 where user_id = fnd_global.user_id;
349 --
350 cursor c_collect_record_promotions
351 (v_assgn_id number
352 ,v_trans_type varchar2) is
353 select *
354 from ben_transaction
355 where transaction_id = v_assgn_id
356 and transaction_type = v_trans_type;
357 --
358 cursor c_job
359 (v_job_id number
360 ,v_lang varchar2) is
361 select substr(name,1,200)
362 from per_jobs_tl
363 where job_id = v_job_id
364 and language = v_lang;
365 --
366 cursor c_grade
367 (v_grade_id number
368 ,v_lang varchar2) is
369 select substr(name,1,200)
370 from per_grades_tl
371 where grade_id = v_grade_id
372 and language = v_lang;
373 --
374 cursor c_position
375 (v_position_id number
376 ,v_lang varchar2) is
377 select substr(name,1,200)
378 from hr_all_positions_f_tl
379 where position_id = v_position_id
380 and language = l_lang;
381 --
385 begin
382
383 l_proc varchar2(72) := g_package||'create_audit_record_promotion';
384
386 --
387 if g_debug then
388 hr_utility.set_location('Entering:'|| l_proc,2);
389 hr_utility.set_location('p_txn_old.attribute1: ' || p_txn_old.attribute1 , 3);
390 hr_utility.set_location('p_txn_old.attribute2: ' || p_txn_old.attribute2, 5);
391 hr_utility.set_location('p_txn_old.transaction_id: ' || p_txn_old.transaction_id, 6);
392 end if;
393
394 l_txn_new := null;
395 --
396 open c_person_id;
397 fetch c_person_id into l_person_id;
398 close c_person_id;
399
400 select userenv('LANG') into l_lang
401 from dual;
402 --
403 open c_per_in_ler;
404 fetch c_per_in_ler into l_per_in_ler_id, l_pl_id, l_lf_evt_ocrd_dt;
405 close c_per_in_ler;
406 --
407 if g_debug then
408 hr_utility.set_location('l_per_in_ler_id: ' || l_per_in_ler_id,6);
409 hr_utility.set_location('l_pl_id: ' || l_pl_id,7);
410 hr_utility.set_location('l_lf_evt_ocrd_dt: ' || l_lf_evt_ocrd_dt, 10);
411 end if;
412
413 begin
414 select * into l_txn_new
415 from ben_transaction
416 where transaction_id = p_txn_old.transaction_id
417 and transaction_type = p_txn_old.transaction_type;
418 --
419 if( ((p_txn_old.attribute5 is null)
420 and (l_txn_new.attribute5 is not null))
421 or ((l_txn_new.attribute5 is null)
422 and (p_txn_old.attribute5 is not null))
423 or (p_txn_old.attribute5 <> l_txn_new.attribute5) ) then
424 --
425 open c_job(p_txn_old.attribute5,l_lang);
426 fetch c_job into l_cd_meaning_old;
427 close c_job;
428 --
429 open c_job(l_txn_new.attribute5,l_lang);
430 fetch c_job into l_cd_meaning_new;
431 close c_job;
432 --
433 --
434 --
435 if(ben_cwb_audit_api.return_lookup_validity('JO')=true) then
436 ben_cwb_audit_api.create_audit_entry
437 (p_group_per_in_ler_id => l_per_in_ler_id
438 ,p_group_pl_id => l_pl_id
439 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
440 ,p_pl_id => l_pl_id
441 ,p_group_oipl_id => -1
442 ,p_audit_type_cd => 'JO'
443 ,p_old_val_varchar => l_cd_meaning_old
444 ,p_new_val_varchar => l_cd_meaning_new
445 ,p_date_stamp => sysdate
446 ,p_change_made_by_person_id => l_person_id
447 ,p_cwb_audit_id => l_cwb_audit_id
448 ,p_object_version_number => l_object_version_number
449 );
450 end if;
451 end if;
452 --
453 if( ((p_txn_old.attribute3 is null)
454 and (l_txn_new.attribute3 is not null))
455 or ((l_txn_new.attribute3 is null)
456 and (p_txn_old.attribute3 is not null))
457 or (p_txn_old.attribute3 <> l_txn_new.attribute3) ) then
458 --
459 open c_lookup('EMP_ASSIGN_REASON', p_txn_old.attribute3);
460 fetch c_lookup into l_cd_meaning_old;
461 close c_lookup;
462 --
463 open c_lookup('EMP_ASSIGN_REASON', l_txn_new.attribute3);
464 fetch c_lookup into l_cd_meaning_new;
465 close c_lookup;
466 --
467 --
468 if(ben_cwb_audit_api.return_lookup_validity('CR')=true) then
469 ben_cwb_audit_api.create_audit_entry
470 (p_group_per_in_ler_id => l_per_in_ler_id
471 ,p_group_pl_id => l_pl_id
472 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
473 ,p_pl_id => l_pl_id
474 ,p_group_oipl_id => -1
475 ,p_audit_type_cd => 'CR'
476 ,p_old_val_varchar => l_cd_meaning_old
477 ,p_new_val_varchar => l_cd_meaning_new
478 ,p_date_stamp => sysdate
479 ,p_change_made_by_person_id => l_person_id
480 ,p_cwb_audit_id => l_cwb_audit_id
481 ,p_object_version_number => l_object_version_number
482 );
483 end if;
484 end if;
485 --
486 if( ((p_txn_old.attribute7 is null)
487 and (l_txn_new.attribute7 is not null))
488 or ((l_txn_new.attribute7 is null)
489 and (p_txn_old.attribute7 is not null))
490 or (p_txn_old.attribute7 <> l_txn_new.attribute7) ) then
491 --
492 open c_grade(p_txn_old.attribute7,l_lang);
493 fetch c_grade into l_cd_meaning_old;
494 close c_grade;
495 --
496 open c_grade(l_txn_new.attribute7,l_lang);
497 fetch c_grade into l_cd_meaning_new;
498 close c_grade;
499 --
500 --
501 --
502 if(ben_cwb_audit_api.return_lookup_validity('GR')=true) then
503 ben_cwb_audit_api.create_audit_entry
504 (p_group_per_in_ler_id => l_per_in_ler_id
505 ,p_group_pl_id => l_pl_id
506 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
507 ,p_pl_id => l_pl_id
508 ,p_group_oipl_id => -1
509 ,p_audit_type_cd => 'GR'
510 ,p_old_val_varchar => l_cd_meaning_old
511 ,p_new_val_varchar => l_cd_meaning_new
515 ,p_object_version_number => l_object_version_number
512 ,p_date_stamp => sysdate
513 ,p_change_made_by_person_id => l_person_id
514 ,p_cwb_audit_id => l_cwb_audit_id
516 );
517 end if;
518 end if;
519 --
520 if( ((p_txn_old.attribute8 is null)
521 and (l_txn_new.attribute8 is not null))
522 or ((l_txn_new.attribute8 is null)
523 and (p_txn_old.attribute8 is not null))
524 or (p_txn_old.attribute8 <> l_txn_new.attribute8) ) then
525 --
526 if(ben_cwb_audit_api.return_lookup_validity('PG')=true) then
527 ben_cwb_audit_api.create_audit_entry
528 (p_group_per_in_ler_id => l_per_in_ler_id
529 ,p_group_pl_id => l_pl_id
530 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
531 ,p_pl_id => l_pl_id
532 ,p_group_oipl_id => -1
533 ,p_audit_type_cd => 'PG'
534 ,p_old_val_varchar => p_txn_old.attribute8
535 ,p_new_val_varchar => l_txn_new.attribute8
536 ,p_date_stamp => sysdate
537 ,p_change_made_by_person_id => l_person_id
538 ,p_cwb_audit_id => l_cwb_audit_id
539 ,p_object_version_number => l_object_version_number
540 );
541 end if;
542 end if;
543 if( ((p_txn_old.attribute6 is null)
544 and (l_txn_new.attribute6 is not null))
545 or ((l_txn_new.attribute6 is null)
546 and (p_txn_old.attribute6 is not null))
547 or (p_txn_old.attribute6 <> l_txn_new.attribute6) ) then
548 --
549 open c_position(p_txn_old.attribute6,l_lang);
550 fetch c_position into l_cd_meaning_old;
551 close c_position;
552 --
553 open c_position(l_txn_new.attribute6,l_lang);
554 fetch c_position into l_cd_meaning_new;
555 close c_position;
556 --
557 --
558 if(ben_cwb_audit_api.return_lookup_validity('PO')=true) then
559 ben_cwb_audit_api.create_audit_entry
560 (p_group_per_in_ler_id => l_per_in_ler_id
561 ,p_group_pl_id => l_pl_id
562 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
563 ,p_pl_id => l_pl_id
564 ,p_group_oipl_id => -1
565 ,p_audit_type_cd => 'PO'
566 ,p_old_val_varchar => l_cd_meaning_old
567 ,p_new_val_varchar => l_cd_meaning_new
568 ,p_date_stamp => sysdate
569 ,p_change_made_by_person_id => l_person_id
570 ,p_cwb_audit_id => l_cwb_audit_id
571 ,p_object_version_number => l_object_version_number
572 );
573 end if;
574 end if;
575 if( ((p_txn_old.attribute9 is null)
576 and (l_txn_new.attribute9 is not null))
577 or ((l_txn_new.attribute9 is null)
578 and (p_txn_old.attribute9 is not null))
579 or (p_txn_old.attribute9 <> l_txn_new.attribute9) ) then
580 --
581 if(ben_cwb_audit_api.return_lookup_validity('SCL')=true) then
582 ben_cwb_audit_api.create_audit_entry
583 (p_group_per_in_ler_id => l_per_in_ler_id
584 ,p_group_pl_id => l_pl_id
585 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
586 ,p_pl_id => l_pl_id
587 ,p_group_oipl_id => -1
588 ,p_audit_type_cd => 'SCL'
589 ,p_old_val_varchar => p_txn_old.attribute9
590 ,p_new_val_varchar => l_txn_new.attribute9
591 ,p_date_stamp => sysdate
592 ,p_change_made_by_person_id => l_person_id
593 ,p_cwb_audit_id => l_cwb_audit_id
594 ,p_object_version_number => l_object_version_number
595 );
596 end if;
597 end if;
598 if( ((p_txn_old.attribute11 is null)
599 and (l_txn_new.attribute11 is not null))
600 or ((l_txn_new.attribute11 is null)
601 and (p_txn_old.attribute11 is not null))
602 or (p_txn_old.attribute11 <> l_txn_new.attribute11) ) then
603 --
604 if(ben_cwb_audit_api.return_lookup_validity('AF1')=true) then
605 ben_cwb_audit_api.create_audit_entry
606 (p_group_per_in_ler_id => l_per_in_ler_id
607 ,p_group_pl_id => l_pl_id
608 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
609 ,p_pl_id => l_pl_id
610 ,p_group_oipl_id => -1
611 ,p_audit_type_cd => 'AF1'
612 ,p_old_val_varchar => p_txn_old.attribute11
613 ,p_new_val_varchar => l_txn_new.attribute11
614 ,p_date_stamp => sysdate
615 ,p_change_made_by_person_id => l_person_id
616 ,p_cwb_audit_id => l_cwb_audit_id
617 ,p_object_version_number => l_object_version_number
618 );
619 end if;
620 end if;
621 if( ((p_txn_old.attribute12 is null)
622 and (l_txn_new.attribute12 is not null))
623 or ((l_txn_new.attribute12 is null)
624 and (p_txn_old.attribute12 is not null))
625 or (p_txn_old.attribute12 <> l_txn_new.attribute12) ) then
629 (p_group_per_in_ler_id => l_per_in_ler_id
626 --
627 if(ben_cwb_audit_api.return_lookup_validity('AF2')=true) then
628 ben_cwb_audit_api.create_audit_entry
630 ,p_group_pl_id => l_pl_id
631 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
632 ,p_pl_id => l_pl_id
633 ,p_group_oipl_id => -1
634 ,p_audit_type_cd => 'AF2'
635 ,p_old_val_varchar => p_txn_old.attribute12
636 ,p_new_val_varchar => l_txn_new.attribute12
637 ,p_date_stamp => sysdate
638 ,p_change_made_by_person_id => l_person_id
639 ,p_cwb_audit_id => l_cwb_audit_id
640 ,p_object_version_number => l_object_version_number
641 );
642 end if;
643 end if;
644 if( ((p_txn_old.attribute13 is null)
645 and (l_txn_new.attribute13 is not null))
646 or ((l_txn_new.attribute13 is null)
647 and (p_txn_old.attribute13 is not null))
648 or (p_txn_old.attribute13 <> l_txn_new.attribute13) ) then
649 --
650 if(ben_cwb_audit_api.return_lookup_validity('AF3')=true) then
651 ben_cwb_audit_api.create_audit_entry
652 (p_group_per_in_ler_id => l_per_in_ler_id
653 ,p_group_pl_id => l_pl_id
654 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
655 ,p_pl_id => l_pl_id
656 ,p_group_oipl_id => -1
657 ,p_audit_type_cd => 'AF3'
658 ,p_old_val_varchar => p_txn_old.attribute13
659 ,p_new_val_varchar => l_txn_new.attribute13
660 ,p_date_stamp => sysdate
661 ,p_change_made_by_person_id => l_person_id
662 ,p_cwb_audit_id => l_cwb_audit_id
663 ,p_object_version_number => l_object_version_number
664 );
665 end if;
666 end if;
667 if( ((p_txn_old.attribute14 is null)
668 and (l_txn_new.attribute14 is not null))
669 or ((l_txn_new.attribute14 is null)
670 and (p_txn_old.attribute14 is not null))
671 or (p_txn_old.attribute14 <> l_txn_new.attribute14) ) then
672 --
673 if(ben_cwb_audit_api.return_lookup_validity('AF4')=true) then
674 ben_cwb_audit_api.create_audit_entry
675 (p_group_per_in_ler_id => l_per_in_ler_id
676 ,p_group_pl_id => l_pl_id
677 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
678 ,p_pl_id => l_pl_id
679 ,p_group_oipl_id => -1
680 ,p_audit_type_cd => 'AF4'
681 ,p_old_val_varchar => p_txn_old.attribute14
682 ,p_new_val_varchar => l_txn_new.attribute14
683 ,p_date_stamp => sysdate
684 ,p_change_made_by_person_id => l_person_id
685 ,p_cwb_audit_id => l_cwb_audit_id
686 ,p_object_version_number => l_object_version_number
687 );
688 end if;
689 end if;
690 if( ((p_txn_old.attribute15 is null)
691 and (l_txn_new.attribute15 is not null))
692 or ((l_txn_new.attribute15 is null)
693 and (p_txn_old.attribute15 is not null))
694 or (p_txn_old.attribute15 <> l_txn_new.attribute15) ) then
695 --
696 if(ben_cwb_audit_api.return_lookup_validity('AF5')=true) then
697 ben_cwb_audit_api.create_audit_entry
698 (p_group_per_in_ler_id => l_per_in_ler_id
699 ,p_group_pl_id => l_pl_id
700 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
701 ,p_pl_id => l_pl_id
702 ,p_group_oipl_id => -1
703 ,p_audit_type_cd => 'AF5'
704 ,p_old_val_varchar => p_txn_old.attribute15
705 ,p_new_val_varchar => l_txn_new.attribute15
706 ,p_date_stamp => sysdate
707 ,p_change_made_by_person_id => l_person_id
708 ,p_cwb_audit_id => l_cwb_audit_id
709 ,p_object_version_number => l_object_version_number
710 );
711 end if;
712 end if;
713 if( ((p_txn_old.attribute16 is null)
714 and (l_txn_new.attribute16 is not null))
715 or ((l_txn_new.attribute16 is null)
716 and (p_txn_old.attribute16 is not null))
717 or (p_txn_old.attribute16 <> l_txn_new.attribute16) ) then
718 --
719 if(ben_cwb_audit_api.return_lookup_validity('AF6')=true) then
720 ben_cwb_audit_api.create_audit_entry
721 (p_group_per_in_ler_id => l_per_in_ler_id
722 ,p_group_pl_id => l_pl_id
723 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
724 ,p_pl_id => l_pl_id
725 ,p_group_oipl_id => -1
726 ,p_audit_type_cd => 'AF6'
727 ,p_old_val_varchar => p_txn_old.attribute16
728 ,p_new_val_varchar => l_txn_new.attribute16
729 ,p_date_stamp => sysdate
730 ,p_change_made_by_person_id => l_person_id
731 ,p_cwb_audit_id => l_cwb_audit_id
732 ,p_object_version_number => l_object_version_number
733 );
737 and (l_txn_new.attribute17 is not null))
734 end if;
735 end if;
736 if( ((p_txn_old.attribute17 is null)
738 or ((l_txn_new.attribute17 is null)
739 and (p_txn_old.attribute17 is not null))
740 or (p_txn_old.attribute17 <> l_txn_new.attribute17) ) then
741 --
742 if(ben_cwb_audit_api.return_lookup_validity('AF7')=true) then
743 ben_cwb_audit_api.create_audit_entry
744 (p_group_per_in_ler_id => l_per_in_ler_id
745 ,p_group_pl_id => l_pl_id
746 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
747 ,p_pl_id => l_pl_id
748 ,p_group_oipl_id => -1
749 ,p_audit_type_cd => 'AF7'
750 ,p_old_val_varchar => p_txn_old.attribute17
751 ,p_new_val_varchar => l_txn_new.attribute17
752 ,p_date_stamp => sysdate
753 ,p_change_made_by_person_id => l_person_id
754 ,p_cwb_audit_id => l_cwb_audit_id
755 ,p_object_version_number => l_object_version_number
756 );
757 end if;
758 end if;
759 if( ((p_txn_old.attribute18 is null)
760 and (l_txn_new.attribute18 is not null))
761 or ((l_txn_new.attribute18 is null)
762 and (p_txn_old.attribute18 is not null))
763 or (p_txn_old.attribute18 <> l_txn_new.attribute18) ) then
764 --
765 if(ben_cwb_audit_api.return_lookup_validity('AF8')=true) then
766 ben_cwb_audit_api.create_audit_entry
767 (p_group_per_in_ler_id => l_per_in_ler_id
768 ,p_group_pl_id => l_pl_id
769 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
770 ,p_pl_id => l_pl_id
771 ,p_group_oipl_id => -1
772 ,p_audit_type_cd => 'AF8'
773 ,p_old_val_varchar => p_txn_old.attribute18
774 ,p_new_val_varchar => l_txn_new.attribute18
775 ,p_date_stamp => sysdate
776 ,p_change_made_by_person_id => l_person_id
777 ,p_cwb_audit_id => l_cwb_audit_id
778 ,p_object_version_number => l_object_version_number
779 );
780 end if;
781 end if;
782 if( ((p_txn_old.attribute19 is null)
783 and (l_txn_new.attribute19 is not null))
784 or ((l_txn_new.attribute19 is null)
785 and (p_txn_old.attribute19 is not null))
786 or (p_txn_old.attribute19 <> l_txn_new.attribute19) ) then
787 --
788 if(ben_cwb_audit_api.return_lookup_validity('AF9')=true) then
789 ben_cwb_audit_api.create_audit_entry
790 (p_group_per_in_ler_id => l_per_in_ler_id
791 ,p_group_pl_id => l_pl_id
792 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
793 ,p_pl_id => l_pl_id
794 ,p_group_oipl_id => -1
795 ,p_audit_type_cd => 'AF9'
796 ,p_old_val_varchar => p_txn_old.attribute19
797 ,p_new_val_varchar => l_txn_new.attribute19
798 ,p_date_stamp => sysdate
799 ,p_change_made_by_person_id => l_person_id
800 ,p_cwb_audit_id => l_cwb_audit_id
801 ,p_object_version_number => l_object_version_number
802 );
803 end if;
804 end if;
805 if( ((p_txn_old.attribute20 is null)
806 and (l_txn_new.attribute20 is not null))
807 or ((l_txn_new.attribute20 is null)
808 and (p_txn_old.attribute20 is not null))
809 or (p_txn_old.attribute20 <> l_txn_new.attribute20) ) then
810 --
811 if(ben_cwb_audit_api.return_lookup_validity('AF10')=true) then
812 ben_cwb_audit_api.create_audit_entry
813 (p_group_per_in_ler_id => l_per_in_ler_id
814 ,p_group_pl_id => l_pl_id
815 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
816 ,p_pl_id => l_pl_id
817 ,p_group_oipl_id => -1
818 ,p_audit_type_cd => 'AF10'
819 ,p_old_val_varchar => p_txn_old.attribute20
820 ,p_new_val_varchar => l_txn_new.attribute20
821 ,p_date_stamp => sysdate
822 ,p_change_made_by_person_id => l_person_id
823 ,p_cwb_audit_id => l_cwb_audit_id
824 ,p_object_version_number => l_object_version_number
825 );
826 end if;
827 end if;
828 if( ((p_txn_old.attribute21 is null)
829 and (l_txn_new.attribute21 is not null))
830 or ((l_txn_new.attribute21 is null)
831 and (p_txn_old.attribute21 is not null))
832 or (p_txn_old.attribute21 <> l_txn_new.attribute21) ) then
833 --
834 if(ben_cwb_audit_api.return_lookup_validity('AF11')=true) then
835 ben_cwb_audit_api.create_audit_entry
836 (p_group_per_in_ler_id => l_per_in_ler_id
837 ,p_group_pl_id => l_pl_id
838 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
839 ,p_pl_id => l_pl_id
840 ,p_group_oipl_id => -1
841 ,p_audit_type_cd => 'AF11'
842 ,p_old_val_varchar => p_txn_old.attribute21
846 ,p_cwb_audit_id => l_cwb_audit_id
843 ,p_new_val_varchar => l_txn_new.attribute21
844 ,p_date_stamp => sysdate
845 ,p_change_made_by_person_id => l_person_id
847 ,p_object_version_number => l_object_version_number
848 );
849 end if;
850 end if;
851 if( ((p_txn_old.attribute22 is null)
852 and (l_txn_new.attribute22 is not null))
853 or ((l_txn_new.attribute22 is null)
854 and (p_txn_old.attribute22 is not null))
855 or (p_txn_old.attribute22 <> l_txn_new.attribute22) ) then
856 --
857 if(ben_cwb_audit_api.return_lookup_validity('AF12')=true) then
858 ben_cwb_audit_api.create_audit_entry
859 (p_group_per_in_ler_id => l_per_in_ler_id
860 ,p_group_pl_id => l_pl_id
861 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
862 ,p_pl_id => l_pl_id
863 ,p_group_oipl_id => -1
864 ,p_audit_type_cd => 'AF12'
865 ,p_old_val_varchar => p_txn_old.attribute22
866 ,p_new_val_varchar => l_txn_new.attribute22
867 ,p_date_stamp => sysdate
868 ,p_change_made_by_person_id => l_person_id
869 ,p_cwb_audit_id => l_cwb_audit_id
870 ,p_object_version_number => l_object_version_number
871 );
872 end if;
873 end if;
874 if( ((p_txn_old.attribute23 is null)
875 and (l_txn_new.attribute23 is not null))
876 or ((l_txn_new.attribute23 is null)
877 and (p_txn_old.attribute23 is not null))
878 or (p_txn_old.attribute23 <> l_txn_new.attribute23) ) then
879 --
880 if(ben_cwb_audit_api.return_lookup_validity('AF13')=true) then
881 ben_cwb_audit_api.create_audit_entry
882 (p_group_per_in_ler_id => l_per_in_ler_id
883 ,p_group_pl_id => l_pl_id
884 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
885 ,p_pl_id => l_pl_id
886 ,p_group_oipl_id => -1
887 ,p_audit_type_cd => 'AF13'
888 ,p_old_val_varchar => p_txn_old.attribute23
889 ,p_new_val_varchar => l_txn_new.attribute23
890 ,p_date_stamp => sysdate
891 ,p_change_made_by_person_id => l_person_id
892 ,p_cwb_audit_id => l_cwb_audit_id
893 ,p_object_version_number => l_object_version_number
894 );
895 end if;
896 end if;
897 if( ((p_txn_old.attribute24 is null)
898 and (l_txn_new.attribute24 is not null))
899 or ((l_txn_new.attribute24 is null)
900 and (p_txn_old.attribute24 is not null))
901 or (p_txn_old.attribute24 <> l_txn_new.attribute24) ) then
902 --
903 if(ben_cwb_audit_api.return_lookup_validity('AF14')=true) then
904 ben_cwb_audit_api.create_audit_entry
905 (p_group_per_in_ler_id => l_per_in_ler_id
906 ,p_group_pl_id => l_pl_id
907 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
908 ,p_pl_id => l_pl_id
909 ,p_group_oipl_id => -1
910 ,p_audit_type_cd => 'AF14'
911 ,p_old_val_varchar => p_txn_old.attribute24
912 ,p_new_val_varchar => l_txn_new.attribute24
913 ,p_date_stamp => sysdate
914 ,p_change_made_by_person_id => l_person_id
915 ,p_cwb_audit_id => l_cwb_audit_id
916 ,p_object_version_number => l_object_version_number
917 );
918 end if;
919 end if;
920 if( ((p_txn_old.attribute25 is null)
921 and (l_txn_new.attribute25 is not null))
922 or ((l_txn_new.attribute25 is null)
923 and (p_txn_old.attribute25 is not null))
924 or (p_txn_old.attribute25 <> l_txn_new.attribute25) ) then
925 --
926 if(ben_cwb_audit_api.return_lookup_validity('AF15')=true) then
927 ben_cwb_audit_api.create_audit_entry
928 (p_group_per_in_ler_id => l_per_in_ler_id
929 ,p_group_pl_id => l_pl_id
930 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
931 ,p_pl_id => l_pl_id
932 ,p_group_oipl_id => -1
933 ,p_audit_type_cd => 'AF15'
934 ,p_old_val_varchar => p_txn_old.attribute25
935 ,p_new_val_varchar => l_txn_new.attribute25
936 ,p_date_stamp => sysdate
937 ,p_change_made_by_person_id => l_person_id
938 ,p_cwb_audit_id => l_cwb_audit_id
939 ,p_object_version_number => l_object_version_number
940 );
941 end if;
942 end if;
943 if( ((p_txn_old.attribute26 is null)
944 and (l_txn_new.attribute26 is not null))
945 or ((l_txn_new.attribute26 is null)
946 and (p_txn_old.attribute26 is not null))
947 or (p_txn_old.attribute26 <> l_txn_new.attribute26) ) then
948 --
949 if(ben_cwb_audit_api.return_lookup_validity('AF16')=true) then
950 ben_cwb_audit_api.create_audit_entry
951 (p_group_per_in_ler_id => l_per_in_ler_id
952 ,p_group_pl_id => l_pl_id
956 ,p_audit_type_cd => 'AF16'
953 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
954 ,p_pl_id => l_pl_id
955 ,p_group_oipl_id => -1
957 ,p_old_val_varchar => p_txn_old.attribute26
958 ,p_new_val_varchar => l_txn_new.attribute26
959 ,p_date_stamp => sysdate
960 ,p_change_made_by_person_id => l_person_id
961 ,p_cwb_audit_id => l_cwb_audit_id
962 ,p_object_version_number => l_object_version_number
963 );
964 end if;
965 end if;
966 if( ((p_txn_old.attribute27 is null)
967 and (l_txn_new.attribute27 is not null))
968 or ((l_txn_new.attribute27 is null)
969 and (p_txn_old.attribute27 is not null))
970 or (p_txn_old.attribute27 <> l_txn_new.attribute27) ) then
971 --
972 if(ben_cwb_audit_api.return_lookup_validity('AF17')=true) then
973 ben_cwb_audit_api.create_audit_entry
974 (p_group_per_in_ler_id => l_per_in_ler_id
975 ,p_group_pl_id => l_pl_id
976 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
977 ,p_pl_id => l_pl_id
978 ,p_group_oipl_id => -1
979 ,p_audit_type_cd => 'AF17'
980 ,p_old_val_varchar => p_txn_old.attribute27
981 ,p_new_val_varchar => l_txn_new.attribute27
982 ,p_date_stamp => sysdate
983 ,p_change_made_by_person_id => l_person_id
984 ,p_cwb_audit_id => l_cwb_audit_id
985 ,p_object_version_number => l_object_version_number
986 );
987 end if;
988 end if;
989 if( ((p_txn_old.attribute28 is null)
990 and (l_txn_new.attribute28 is not null))
991 or ((l_txn_new.attribute28 is null)
992 and (p_txn_old.attribute28 is not null))
993 or (p_txn_old.attribute28 <> l_txn_new.attribute28) ) then
994 --
995 if(ben_cwb_audit_api.return_lookup_validity('AF18')=true) then
996 ben_cwb_audit_api.create_audit_entry
997 (p_group_per_in_ler_id => l_per_in_ler_id
998 ,p_group_pl_id => l_pl_id
999 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1000 ,p_pl_id => l_pl_id
1001 ,p_group_oipl_id => -1
1002 ,p_audit_type_cd => 'AF18'
1003 ,p_old_val_varchar => p_txn_old.attribute28
1004 ,p_new_val_varchar => l_txn_new.attribute28
1005 ,p_date_stamp => sysdate
1006 ,p_change_made_by_person_id => l_person_id
1007 ,p_cwb_audit_id => l_cwb_audit_id
1008 ,p_object_version_number => l_object_version_number
1009 );
1010 end if;
1011 end if;
1012 if( ((p_txn_old.attribute29 is null)
1013 and (l_txn_new.attribute29 is not null))
1014 or ((l_txn_new.attribute29 is null)
1015 and (p_txn_old.attribute29 is not null))
1016 or (p_txn_old.attribute29 <> l_txn_new.attribute29) ) then
1017 --
1018 if(ben_cwb_audit_api.return_lookup_validity('AF19')=true) then
1019 ben_cwb_audit_api.create_audit_entry
1020 (p_group_per_in_ler_id => l_per_in_ler_id
1021 ,p_group_pl_id => l_pl_id
1022 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1023 ,p_pl_id => l_pl_id
1024 ,p_group_oipl_id => -1
1025 ,p_audit_type_cd => 'AF19'
1026 ,p_old_val_varchar => p_txn_old.attribute29
1027 ,p_new_val_varchar => l_txn_new.attribute29
1028 ,p_date_stamp => sysdate
1029 ,p_change_made_by_person_id => l_person_id
1030 ,p_cwb_audit_id => l_cwb_audit_id
1031 ,p_object_version_number => l_object_version_number
1032 );
1033 end if;
1034 end if;
1035 if( ((p_txn_old.attribute30 is null)
1036 and (l_txn_new.attribute30 is not null))
1037 or ((l_txn_new.attribute30 is null)
1038 and (p_txn_old.attribute30 is not null))
1039 or (p_txn_old.attribute30 <> l_txn_new.attribute30) ) then
1040 --
1041 if(ben_cwb_audit_api.return_lookup_validity('AF20')=true) then
1042 ben_cwb_audit_api.create_audit_entry
1043 (p_group_per_in_ler_id => l_per_in_ler_id
1044 ,p_group_pl_id => l_pl_id
1045 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1046 ,p_pl_id => l_pl_id
1047 ,p_group_oipl_id => -1
1048 ,p_audit_type_cd => 'AF20'
1049 ,p_old_val_varchar => p_txn_old.attribute30
1050 ,p_new_val_varchar => l_txn_new.attribute30
1051 ,p_date_stamp => sysdate
1052 ,p_change_made_by_person_id => l_person_id
1053 ,p_cwb_audit_id => l_cwb_audit_id
1054 ,p_object_version_number => l_object_version_number
1055 );
1056 end if;
1057 end if;
1058 if( ((p_txn_old.attribute31 is null)
1059 and (l_txn_new.attribute31 is not null))
1060 or ((l_txn_new.attribute31 is null)
1061 and (p_txn_old.attribute31 is not null))
1062 or (p_txn_old.attribute31 <> l_txn_new.attribute31) ) then
1063 --
1067 ,p_group_pl_id => l_pl_id
1064 if(ben_cwb_audit_api.return_lookup_validity('AF21')=true) then
1065 ben_cwb_audit_api.create_audit_entry
1066 (p_group_per_in_ler_id => l_per_in_ler_id
1068 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1069 ,p_pl_id => l_pl_id
1070 ,p_group_oipl_id => -1
1071 ,p_audit_type_cd => 'AF21'
1072 ,p_old_val_varchar => p_txn_old.attribute31
1073 ,p_new_val_varchar => l_txn_new.attribute31
1074 ,p_date_stamp => sysdate
1075 ,p_change_made_by_person_id => l_person_id
1076 ,p_cwb_audit_id => l_cwb_audit_id
1077 ,p_object_version_number => l_object_version_number
1078 );
1079 end if;
1080 end if;
1081 if( ((p_txn_old.attribute32 is null)
1082 and (l_txn_new.attribute32 is not null))
1083 or ((l_txn_new.attribute32 is null)
1084 and (p_txn_old.attribute32 is not null))
1085 or (p_txn_old.attribute32 <> l_txn_new.attribute32) ) then
1086 --
1087 if(ben_cwb_audit_api.return_lookup_validity('AF22')=true) then
1088 ben_cwb_audit_api.create_audit_entry
1089 (p_group_per_in_ler_id => l_per_in_ler_id
1090 ,p_group_pl_id => l_pl_id
1091 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1092 ,p_pl_id => l_pl_id
1093 ,p_group_oipl_id => -1
1094 ,p_audit_type_cd => 'AF22'
1095 ,p_old_val_varchar => p_txn_old.attribute32
1096 ,p_new_val_varchar => l_txn_new.attribute32
1097 ,p_date_stamp => sysdate
1098 ,p_change_made_by_person_id => l_person_id
1099 ,p_cwb_audit_id => l_cwb_audit_id
1100 ,p_object_version_number => l_object_version_number
1101 );
1102 end if;
1103 end if;
1104 if( ((p_txn_old.attribute33 is null)
1105 and (l_txn_new.attribute33 is not null))
1106 or ((l_txn_new.attribute33 is null)
1107 and (p_txn_old.attribute33 is not null))
1108 or (p_txn_old.attribute33 <> l_txn_new.attribute33) ) then
1109 --
1110 if(ben_cwb_audit_api.return_lookup_validity('AF23')=true) then
1111 ben_cwb_audit_api.create_audit_entry
1112 (p_group_per_in_ler_id => l_per_in_ler_id
1113 ,p_group_pl_id => l_pl_id
1114 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1115 ,p_pl_id => l_pl_id
1116 ,p_group_oipl_id => -1
1117 ,p_audit_type_cd => 'AF23'
1118 ,p_old_val_varchar => p_txn_old.attribute33
1119 ,p_new_val_varchar => l_txn_new.attribute33
1120 ,p_date_stamp => sysdate
1121 ,p_change_made_by_person_id => l_person_id
1122 ,p_cwb_audit_id => l_cwb_audit_id
1123 ,p_object_version_number => l_object_version_number
1124 );
1125 end if;
1126 end if;
1127 if( ((p_txn_old.attribute34 is null)
1128 and (l_txn_new.attribute34 is not null))
1129 or ((l_txn_new.attribute34 is null)
1130 and (p_txn_old.attribute34 is not null))
1131 or (p_txn_old.attribute34 <> l_txn_new.attribute34) ) then
1132 --
1133 if(ben_cwb_audit_api.return_lookup_validity('AF24')=true) then
1134 ben_cwb_audit_api.create_audit_entry
1135 (p_group_per_in_ler_id => l_per_in_ler_id
1136 ,p_group_pl_id => l_pl_id
1137 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1138 ,p_pl_id => l_pl_id
1139 ,p_group_oipl_id => -1
1140 ,p_audit_type_cd => 'AF24'
1141 ,p_old_val_varchar => p_txn_old.attribute34
1142 ,p_new_val_varchar => l_txn_new.attribute34
1143 ,p_date_stamp => sysdate
1144 ,p_change_made_by_person_id => l_person_id
1145 ,p_cwb_audit_id => l_cwb_audit_id
1146 ,p_object_version_number => l_object_version_number
1147 );
1148 end if;
1149 end if;
1150 if( ((p_txn_old.attribute35 is null)
1151 and (l_txn_new.attribute35 is not null))
1152 or ((l_txn_new.attribute35 is null)
1153 and (p_txn_old.attribute35 is not null))
1154 or (p_txn_old.attribute35 <> l_txn_new.attribute35) ) then
1155 --
1156 if(ben_cwb_audit_api.return_lookup_validity('AF25')=true) then
1157 ben_cwb_audit_api.create_audit_entry
1158 (p_group_per_in_ler_id => l_per_in_ler_id
1159 ,p_group_pl_id => l_pl_id
1160 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1161 ,p_pl_id => l_pl_id
1162 ,p_group_oipl_id => -1
1163 ,p_audit_type_cd => 'AF25'
1164 ,p_old_val_varchar => p_txn_old.attribute35
1165 ,p_new_val_varchar => l_txn_new.attribute35
1166 ,p_date_stamp => sysdate
1167 ,p_change_made_by_person_id => l_person_id
1168 ,p_cwb_audit_id => l_cwb_audit_id
1169 ,p_object_version_number => l_object_version_number
1170 );
1171 end if;
1172 end if;
1173 if( ((p_txn_old.attribute36 is null)
1177 or (p_txn_old.attribute36 <> l_txn_new.attribute36) ) then
1174 and (l_txn_new.attribute36 is not null))
1175 or ((l_txn_new.attribute36 is null)
1176 and (p_txn_old.attribute36 is not null))
1178 --
1179 if(ben_cwb_audit_api.return_lookup_validity('AF26')=true) then
1180 ben_cwb_audit_api.create_audit_entry
1181 (p_group_per_in_ler_id => l_per_in_ler_id
1182 ,p_group_pl_id => l_pl_id
1183 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1184 ,p_pl_id => l_pl_id
1185 ,p_group_oipl_id => -1
1186 ,p_audit_type_cd => 'AF26'
1187 ,p_old_val_varchar => p_txn_old.attribute36
1188 ,p_new_val_varchar => l_txn_new.attribute36
1189 ,p_date_stamp => sysdate
1190 ,p_change_made_by_person_id => l_person_id
1191 ,p_cwb_audit_id => l_cwb_audit_id
1192 ,p_object_version_number => l_object_version_number
1193 );
1194 end if;
1195 end if;
1196 if( ((p_txn_old.attribute37 is null)
1197 and (l_txn_new.attribute37 is not null))
1198 or ((l_txn_new.attribute37 is null)
1199 and (p_txn_old.attribute37 is not null))
1200 or (p_txn_old.attribute37 <> l_txn_new.attribute37) ) then
1201 --
1202 if(ben_cwb_audit_api.return_lookup_validity('AF27')=true) then
1203 ben_cwb_audit_api.create_audit_entry
1204 (p_group_per_in_ler_id => l_per_in_ler_id
1205 ,p_group_pl_id => l_pl_id
1206 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1207 ,p_pl_id => l_pl_id
1208 ,p_group_oipl_id => -1
1209 ,p_audit_type_cd => 'AF27'
1210 ,p_old_val_varchar => p_txn_old.attribute37
1211 ,p_new_val_varchar => l_txn_new.attribute37
1212 ,p_date_stamp => sysdate
1213 ,p_change_made_by_person_id => l_person_id
1214 ,p_cwb_audit_id => l_cwb_audit_id
1215 ,p_object_version_number => l_object_version_number
1216 );
1217 end if;
1218 end if;
1219 if( ((p_txn_old.attribute38 is null)
1220 and (l_txn_new.attribute38 is not null))
1221 or ((l_txn_new.attribute38 is null)
1222 and (p_txn_old.attribute38 is not null))
1223 or (p_txn_old.attribute38 <> l_txn_new.attribute38) ) then
1224 --
1225 if(ben_cwb_audit_api.return_lookup_validity('AF28')=true) then
1226 ben_cwb_audit_api.create_audit_entry
1227 (p_group_per_in_ler_id => l_per_in_ler_id
1228 ,p_group_pl_id => l_pl_id
1229 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1230 ,p_pl_id => l_pl_id
1231 ,p_group_oipl_id => -1
1232 ,p_audit_type_cd => 'AF28'
1233 ,p_old_val_varchar => p_txn_old.attribute38
1234 ,p_new_val_varchar => l_txn_new.attribute38
1235 ,p_date_stamp => sysdate
1236 ,p_change_made_by_person_id => l_person_id
1237 ,p_cwb_audit_id => l_cwb_audit_id
1238 ,p_object_version_number => l_object_version_number
1239 );
1240 end if;
1241 end if;
1242 if( ((p_txn_old.attribute39 is null)
1243 and (l_txn_new.attribute39 is not null))
1244 or ((l_txn_new.attribute39 is null)
1245 and (p_txn_old.attribute39 is not null))
1246 or (p_txn_old.attribute39 <> l_txn_new.attribute39) ) then
1247 --
1248 if(ben_cwb_audit_api.return_lookup_validity('AF29')=true) then
1249 ben_cwb_audit_api.create_audit_entry
1250 (p_group_per_in_ler_id => l_per_in_ler_id
1251 ,p_group_pl_id => l_pl_id
1252 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1253 ,p_pl_id => l_pl_id
1254 ,p_group_oipl_id => -1
1255 ,p_audit_type_cd => 'AF29'
1256 ,p_old_val_varchar => p_txn_old.attribute39
1257 ,p_new_val_varchar => l_txn_new.attribute39
1258 ,p_date_stamp => sysdate
1259 ,p_change_made_by_person_id => l_person_id
1260 ,p_cwb_audit_id => l_cwb_audit_id
1261 ,p_object_version_number => l_object_version_number
1262 );
1263 end if;
1264 end if;
1265 if( ((p_txn_old.attribute40 is null)
1266 and (l_txn_new.attribute40 is not null))
1267 or ((l_txn_new.attribute40 is null)
1268 and (p_txn_old.attribute40 is not null))
1269 or (p_txn_old.attribute40 <> l_txn_new.attribute40) ) then
1270 --
1271 if(ben_cwb_audit_api.return_lookup_validity('AF30')=true) then
1272 ben_cwb_audit_api.create_audit_entry
1273 (p_group_per_in_ler_id => l_per_in_ler_id
1274 ,p_group_pl_id => l_pl_id
1275 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1276 ,p_pl_id => l_pl_id
1277 ,p_group_oipl_id => -1
1278 ,p_audit_type_cd => 'AF30'
1279 ,p_old_val_varchar => p_txn_old.attribute40
1280 ,p_new_val_varchar => l_txn_new.attribute40
1281 ,p_date_stamp => sysdate
1282 ,p_change_made_by_person_id => l_person_id
1286 end if;
1283 ,p_cwb_audit_id => l_cwb_audit_id
1284 ,p_object_version_number => l_object_version_number
1285 );
1287 end if;
1288 exception
1289 when no_data_found then
1290 l_cd_meaning_new := null;
1291 if(p_txn_old.attribute5 is not null) then
1292 --
1293 open c_job(p_txn_old.attribute5,l_lang);
1294 fetch c_job into l_cd_meaning_old;
1295 close c_job;
1296 --
1297 --
1298 if(ben_cwb_audit_api.return_lookup_validity('JO')=true) then
1299 ben_cwb_audit_api.create_audit_entry
1300 (p_group_per_in_ler_id => l_per_in_ler_id
1301 ,p_group_pl_id => l_pl_id
1302 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1303 ,p_pl_id => l_pl_id
1304 ,p_group_oipl_id => -1
1305 ,p_audit_type_cd => 'JO'
1306 ,p_old_val_varchar => l_cd_meaning_old
1307 ,p_new_val_varchar => l_cd_meaning_new
1308 ,p_date_stamp => sysdate
1309 ,p_change_made_by_person_id => l_person_id
1310 ,p_cwb_audit_id => l_cwb_audit_id
1311 ,p_object_version_number => l_object_version_number
1312 );
1313 end if;
1314 end if;
1315 if(p_txn_old.attribute3 is not null) then
1316 --
1317 open c_lookup('EMP_ASSIGN_REASON', p_txn_old.attribute3);
1318 fetch c_lookup into l_cd_meaning_old;
1319 close c_lookup;
1320 --
1321 --
1322 if(ben_cwb_audit_api.return_lookup_validity('CR')=true) then
1323 ben_cwb_audit_api.create_audit_entry
1324 (p_group_per_in_ler_id => l_per_in_ler_id
1325 ,p_group_pl_id => l_pl_id
1326 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1327 ,p_pl_id => l_pl_id
1328 ,p_group_oipl_id => -1
1329 ,p_audit_type_cd => 'CR'
1330 ,p_old_val_varchar => l_cd_meaning_old
1331 ,p_new_val_varchar => l_cd_meaning_new
1332 ,p_date_stamp => sysdate
1333 ,p_change_made_by_person_id => l_person_id
1334 ,p_cwb_audit_id => l_cwb_audit_id
1335 ,p_object_version_number => l_object_version_number
1336 );
1337 end if;
1338 end if;
1339 --
1340 if(p_txn_old.attribute7 is not null) then
1341 --
1342 open c_grade(p_txn_old.attribute7,l_lang);
1343 fetch c_grade into l_cd_meaning_old;
1344 close c_grade;
1345 --
1346 --
1347 --
1348 if(ben_cwb_audit_api.return_lookup_validity('GR')=true) then
1349 ben_cwb_audit_api.create_audit_entry
1350 (p_group_per_in_ler_id => l_per_in_ler_id
1351 ,p_group_pl_id => l_pl_id
1352 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1353 ,p_pl_id => l_pl_id
1354 ,p_group_oipl_id => -1
1355 ,p_audit_type_cd => 'GR'
1356 ,p_old_val_varchar => l_cd_meaning_old
1357 ,p_new_val_varchar => l_cd_meaning_new
1358 ,p_date_stamp => sysdate
1359 ,p_change_made_by_person_id => l_person_id
1360 ,p_cwb_audit_id => l_cwb_audit_id
1361 ,p_object_version_number => l_object_version_number
1362 );
1363 end if;
1364 end if;
1365 --
1366 if(p_txn_old.attribute8 is not null) then
1367 --
1368 if(ben_cwb_audit_api.return_lookup_validity('AF')=true) then
1369 ben_cwb_audit_api.create_audit_entry
1370 (p_group_per_in_ler_id => l_per_in_ler_id
1371 ,p_group_pl_id => l_pl_id
1372 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1373 ,p_pl_id => l_pl_id
1374 ,p_group_oipl_id => -1
1375 ,p_audit_type_cd => 'AF'
1376 ,p_old_val_varchar => p_txn_old.attribute8
1377 ,p_new_val_varchar => l_txn_new.attribute8
1378 ,p_date_stamp => sysdate
1379 ,p_change_made_by_person_id => l_person_id
1380 ,p_cwb_audit_id => l_cwb_audit_id
1381 ,p_object_version_number => l_object_version_number
1382 );
1383 end if;
1384 end if;
1385 if(p_txn_old.attribute6 is not null) then
1386 --
1387 open c_position(p_txn_old.attribute6,l_lang);
1388 fetch c_position into l_cd_meaning_old;
1389 close c_position;
1390 --
1391 --
1392 --
1393 if(ben_cwb_audit_api.return_lookup_validity('PO')=true) then
1394 ben_cwb_audit_api.create_audit_entry
1395 (p_group_per_in_ler_id => l_per_in_ler_id
1396 ,p_group_pl_id => l_pl_id
1397 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1398 ,p_pl_id => l_pl_id
1399 ,p_group_oipl_id => -1
1400 ,p_audit_type_cd => 'PO'
1401 ,p_old_val_varchar => l_cd_meaning_old
1402 ,p_new_val_varchar => l_cd_meaning_new
1403 ,p_date_stamp => sysdate
1404 ,p_change_made_by_person_id => l_person_id
1408 end if;
1405 ,p_cwb_audit_id => l_cwb_audit_id
1406 ,p_object_version_number => l_object_version_number
1407 );
1409 end if;
1410 if(p_txn_old.attribute11 is not null) then
1411 --
1412 if(ben_cwb_audit_api.return_lookup_validity('AF1')=true) then
1413 ben_cwb_audit_api.create_audit_entry
1414 (p_group_per_in_ler_id => l_per_in_ler_id
1415 ,p_group_pl_id => l_pl_id
1416 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1417 ,p_pl_id => l_pl_id
1418 ,p_group_oipl_id => -1
1419 ,p_audit_type_cd => 'AF1'
1420 ,p_old_val_varchar => p_txn_old.attribute11
1421 ,p_new_val_varchar => l_txn_new.attribute11
1422 ,p_date_stamp => sysdate
1423 ,p_change_made_by_person_id => l_person_id
1424 ,p_cwb_audit_id => l_cwb_audit_id
1425 ,p_object_version_number => l_object_version_number
1426 );
1427 end if;
1428 end if;
1429 if(p_txn_old.attribute12 is not null) then
1430 --
1431 if(ben_cwb_audit_api.return_lookup_validity('AF2')=true) then
1432 ben_cwb_audit_api.create_audit_entry
1433 (p_group_per_in_ler_id => l_per_in_ler_id
1434 ,p_group_pl_id => l_pl_id
1435 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1436 ,p_pl_id => l_pl_id
1437 ,p_group_oipl_id => -1
1438 ,p_audit_type_cd => 'AF2'
1439 ,p_old_val_varchar => p_txn_old.attribute12
1440 ,p_new_val_varchar => l_txn_new.attribute12
1441 ,p_date_stamp => sysdate
1442 ,p_change_made_by_person_id => l_person_id
1443 ,p_cwb_audit_id => l_cwb_audit_id
1444 ,p_object_version_number => l_object_version_number
1445 );
1446 end if;
1447 end if;
1448 if(p_txn_old.attribute13 is not null) then
1449 --
1450 if(ben_cwb_audit_api.return_lookup_validity('AF3')=true) then
1451 ben_cwb_audit_api.create_audit_entry
1452 (p_group_per_in_ler_id => l_per_in_ler_id
1453 ,p_group_pl_id => l_pl_id
1454 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1455 ,p_pl_id => l_pl_id
1456 ,p_group_oipl_id => -1
1457 ,p_audit_type_cd => 'AF3'
1458 ,p_old_val_varchar => p_txn_old.attribute13
1459 ,p_new_val_varchar => l_txn_new.attribute13
1460 ,p_date_stamp => sysdate
1461 ,p_change_made_by_person_id => l_person_id
1462 ,p_cwb_audit_id => l_cwb_audit_id
1463 ,p_object_version_number => l_object_version_number
1464 );
1465 end if;
1466 end if;
1467 if(p_txn_old.attribute14 is not null) then
1468 --
1469 if(ben_cwb_audit_api.return_lookup_validity('AF4')=true) then
1470 ben_cwb_audit_api.create_audit_entry
1471 (p_group_per_in_ler_id => l_per_in_ler_id
1472 ,p_group_pl_id => l_pl_id
1473 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1474 ,p_pl_id => l_pl_id
1475 ,p_group_oipl_id => -1
1476 ,p_audit_type_cd => 'AF4'
1477 ,p_old_val_varchar => p_txn_old.attribute14
1478 ,p_new_val_varchar => l_txn_new.attribute14
1479 ,p_date_stamp => sysdate
1480 ,p_change_made_by_person_id => l_person_id
1481 ,p_cwb_audit_id => l_cwb_audit_id
1482 ,p_object_version_number => l_object_version_number
1483 );
1484 end if;
1485 end if;
1486 if(p_txn_old.attribute15 is not null) then
1487 --
1488 if(ben_cwb_audit_api.return_lookup_validity('AF5')=true) then
1489 ben_cwb_audit_api.create_audit_entry
1490 (p_group_per_in_ler_id => l_per_in_ler_id
1491 ,p_group_pl_id => l_pl_id
1492 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1493 ,p_pl_id => l_pl_id
1494 ,p_group_oipl_id => -1
1495 ,p_audit_type_cd => 'AF5'
1496 ,p_old_val_varchar => p_txn_old.attribute15
1497 ,p_new_val_varchar => l_txn_new.attribute15
1498 ,p_date_stamp => sysdate
1499 ,p_change_made_by_person_id => l_person_id
1500 ,p_cwb_audit_id => l_cwb_audit_id
1501 ,p_object_version_number => l_object_version_number
1502 );
1503 end if;
1504 end if;
1505 if(p_txn_old.attribute16 is not null) then
1506 --
1507 if(ben_cwb_audit_api.return_lookup_validity('AF6')=true) then
1508 ben_cwb_audit_api.create_audit_entry
1509 (p_group_per_in_ler_id => l_per_in_ler_id
1510 ,p_group_pl_id => l_pl_id
1511 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1512 ,p_pl_id => l_pl_id
1513 ,p_group_oipl_id => -1
1514 ,p_audit_type_cd => 'AF6'
1515 ,p_old_val_varchar => p_txn_old.attribute16
1519 ,p_cwb_audit_id => l_cwb_audit_id
1516 ,p_new_val_varchar => l_txn_new.attribute16
1517 ,p_date_stamp => sysdate
1518 ,p_change_made_by_person_id => l_person_id
1520 ,p_object_version_number => l_object_version_number
1521 );
1522 end if;
1523 end if;
1524 if(p_txn_old.attribute17 is not null) then
1525 --
1526 if(ben_cwb_audit_api.return_lookup_validity('AF7')=true) then
1527 ben_cwb_audit_api.create_audit_entry
1528 (p_group_per_in_ler_id => l_per_in_ler_id
1529 ,p_group_pl_id => l_pl_id
1530 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1531 ,p_pl_id => l_pl_id
1532 ,p_group_oipl_id => -1
1533 ,p_audit_type_cd => 'AF7'
1534 ,p_old_val_varchar => p_txn_old.attribute17
1535 ,p_new_val_varchar => l_txn_new.attribute17
1536 ,p_date_stamp => sysdate
1537 ,p_change_made_by_person_id => l_person_id
1538 ,p_cwb_audit_id => l_cwb_audit_id
1539 ,p_object_version_number => l_object_version_number
1540 );
1541 end if;
1542 end if;
1543 if(p_txn_old.attribute18 is not null) then
1544 --
1545 if(ben_cwb_audit_api.return_lookup_validity('AF8')=true) then
1546 ben_cwb_audit_api.create_audit_entry
1547 (p_group_per_in_ler_id => l_per_in_ler_id
1548 ,p_group_pl_id => l_pl_id
1549 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1550 ,p_pl_id => l_pl_id
1551 ,p_group_oipl_id => -1
1552 ,p_audit_type_cd => 'AF8'
1553 ,p_old_val_varchar => p_txn_old.attribute18
1554 ,p_new_val_varchar => l_txn_new.attribute18
1555 ,p_date_stamp => sysdate
1556 ,p_change_made_by_person_id => l_person_id
1557 ,p_cwb_audit_id => l_cwb_audit_id
1558 ,p_object_version_number => l_object_version_number
1559 );
1560 end if;
1561 end if;
1562 if(p_txn_old.attribute19 is not null) then
1563 --
1564 if(ben_cwb_audit_api.return_lookup_validity('AF9')=true) then
1565 ben_cwb_audit_api.create_audit_entry
1566 (p_group_per_in_ler_id => l_per_in_ler_id
1567 ,p_group_pl_id => l_pl_id
1568 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1569 ,p_pl_id => l_pl_id
1570 ,p_group_oipl_id => -1
1571 ,p_audit_type_cd => 'AF9'
1572 ,p_old_val_varchar => p_txn_old.attribute19
1573 ,p_new_val_varchar => l_txn_new.attribute19
1574 ,p_date_stamp => sysdate
1575 ,p_change_made_by_person_id => l_person_id
1576 ,p_cwb_audit_id => l_cwb_audit_id
1577 ,p_object_version_number => l_object_version_number
1578 );
1579 end if;
1580 end if;
1581 if(p_txn_old.attribute20 is not null) then
1582 --
1583 if(ben_cwb_audit_api.return_lookup_validity('AF10')=true) then
1584 ben_cwb_audit_api.create_audit_entry
1585 (p_group_per_in_ler_id => l_per_in_ler_id
1586 ,p_group_pl_id => l_pl_id
1587 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1588 ,p_pl_id => l_pl_id
1589 ,p_group_oipl_id => -1
1590 ,p_audit_type_cd => 'AF10'
1591 ,p_old_val_varchar => p_txn_old.attribute20
1592 ,p_new_val_varchar => l_txn_new.attribute20
1593 ,p_date_stamp => sysdate
1594 ,p_change_made_by_person_id => l_person_id
1595 ,p_cwb_audit_id => l_cwb_audit_id
1596 ,p_object_version_number => l_object_version_number
1597 );
1598 end if;
1599 end if;
1600 if(p_txn_old.attribute21 is not null) then
1601 --
1602 if(ben_cwb_audit_api.return_lookup_validity('AF11')=true) then
1603 ben_cwb_audit_api.create_audit_entry
1604 (p_group_per_in_ler_id => l_per_in_ler_id
1605 ,p_group_pl_id => l_pl_id
1606 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1607 ,p_pl_id => l_pl_id
1608 ,p_group_oipl_id => -1
1609 ,p_audit_type_cd => 'AF11'
1610 ,p_old_val_varchar => p_txn_old.attribute21
1611 ,p_new_val_varchar => l_txn_new.attribute21
1612 ,p_date_stamp => sysdate
1613 ,p_change_made_by_person_id => l_person_id
1614 ,p_cwb_audit_id => l_cwb_audit_id
1615 ,p_object_version_number => l_object_version_number
1616 );
1617 end if;
1618 end if;
1619 if(p_txn_old.attribute22 is not null) then
1620 --
1621 if(ben_cwb_audit_api.return_lookup_validity('AF12')=true) then
1622 ben_cwb_audit_api.create_audit_entry
1623 (p_group_per_in_ler_id => l_per_in_ler_id
1624 ,p_group_pl_id => l_pl_id
1625 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1626 ,p_pl_id => l_pl_id
1627 ,p_group_oipl_id => -1
1631 ,p_date_stamp => sysdate
1628 ,p_audit_type_cd => 'AF12'
1629 ,p_old_val_varchar => p_txn_old.attribute22
1630 ,p_new_val_varchar => l_txn_new.attribute22
1632 ,p_change_made_by_person_id => l_person_id
1633 ,p_cwb_audit_id => l_cwb_audit_id
1634 ,p_object_version_number => l_object_version_number
1635 );
1636 end if;
1637 end if;
1638 if(p_txn_old.attribute23 is not null) then
1639 --
1640 if(ben_cwb_audit_api.return_lookup_validity('AF13')=true) then
1641 ben_cwb_audit_api.create_audit_entry
1642 (p_group_per_in_ler_id => l_per_in_ler_id
1643 ,p_group_pl_id => l_pl_id
1644 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1645 ,p_pl_id => l_pl_id
1646 ,p_group_oipl_id => -1
1647 ,p_audit_type_cd => 'AF13'
1648 ,p_old_val_varchar => p_txn_old.attribute23
1649 ,p_new_val_varchar => l_txn_new.attribute23
1650 ,p_date_stamp => sysdate
1651 ,p_change_made_by_person_id => l_person_id
1652 ,p_cwb_audit_id => l_cwb_audit_id
1653 ,p_object_version_number => l_object_version_number
1654 );
1655 end if;
1656 end if;
1657 if(p_txn_old.attribute24 is not null) then
1658 --
1659 if(ben_cwb_audit_api.return_lookup_validity('AF14')=true) then
1660 ben_cwb_audit_api.create_audit_entry
1661 (p_group_per_in_ler_id => l_per_in_ler_id
1662 ,p_group_pl_id => l_pl_id
1663 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1664 ,p_pl_id => l_pl_id
1665 ,p_group_oipl_id => -1
1666 ,p_audit_type_cd => 'AF14'
1667 ,p_old_val_varchar => p_txn_old.attribute24
1668 ,p_new_val_varchar => l_txn_new.attribute24
1669 ,p_date_stamp => sysdate
1670 ,p_change_made_by_person_id => l_person_id
1671 ,p_cwb_audit_id => l_cwb_audit_id
1672 ,p_object_version_number => l_object_version_number
1673 );
1674 end if;
1675 end if;
1676 if(p_txn_old.attribute25 is not null) then
1677 --
1678 if(ben_cwb_audit_api.return_lookup_validity('AF15')=true) then
1679 ben_cwb_audit_api.create_audit_entry
1680 (p_group_per_in_ler_id => l_per_in_ler_id
1681 ,p_group_pl_id => l_pl_id
1682 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1683 ,p_pl_id => l_pl_id
1684 ,p_group_oipl_id => -1
1685 ,p_audit_type_cd => 'AF15'
1686 ,p_old_val_varchar => p_txn_old.attribute25
1687 ,p_new_val_varchar => l_txn_new.attribute25
1688 ,p_date_stamp => sysdate
1689 ,p_change_made_by_person_id => l_person_id
1690 ,p_cwb_audit_id => l_cwb_audit_id
1691 ,p_object_version_number => l_object_version_number
1692 );
1693 end if;
1694 end if;
1695 if(p_txn_old.attribute26 is not null) then
1696 --
1697 if(ben_cwb_audit_api.return_lookup_validity('AF16')=true) then
1698 ben_cwb_audit_api.create_audit_entry
1699 (p_group_per_in_ler_id => l_per_in_ler_id
1700 ,p_group_pl_id => l_pl_id
1701 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1702 ,p_pl_id => l_pl_id
1703 ,p_group_oipl_id => -1
1704 ,p_audit_type_cd => 'AF16'
1705 ,p_old_val_varchar => p_txn_old.attribute26
1706 ,p_new_val_varchar => l_txn_new.attribute26
1707 ,p_date_stamp => sysdate
1708 ,p_change_made_by_person_id => l_person_id
1709 ,p_cwb_audit_id => l_cwb_audit_id
1710 ,p_object_version_number => l_object_version_number
1711 );
1712 end if;
1713 end if;
1714 if(p_txn_old.attribute27 is not null) then
1715 --
1716 if(ben_cwb_audit_api.return_lookup_validity('AF17')=true) then
1717 ben_cwb_audit_api.create_audit_entry
1718 (p_group_per_in_ler_id => l_per_in_ler_id
1719 ,p_group_pl_id => l_pl_id
1720 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1721 ,p_pl_id => l_pl_id
1722 ,p_group_oipl_id => -1
1723 ,p_audit_type_cd => 'AF17'
1724 ,p_old_val_varchar => p_txn_old.attribute27
1725 ,p_new_val_varchar => l_txn_new.attribute27
1726 ,p_date_stamp => sysdate
1727 ,p_change_made_by_person_id => l_person_id
1728 ,p_cwb_audit_id => l_cwb_audit_id
1729 ,p_object_version_number => l_object_version_number
1730 );
1731 end if;
1732 end if;
1733 if(p_txn_old.attribute28 is not null) then
1734 --
1735 if(ben_cwb_audit_api.return_lookup_validity('AF18')=true) then
1736 ben_cwb_audit_api.create_audit_entry
1737 (p_group_per_in_ler_id => l_per_in_ler_id
1738 ,p_group_pl_id => l_pl_id
1742 ,p_audit_type_cd => 'AF18'
1739 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1740 ,p_pl_id => l_pl_id
1741 ,p_group_oipl_id => -1
1743 ,p_old_val_varchar => p_txn_old.attribute28
1744 ,p_new_val_varchar => l_txn_new.attribute28
1745 ,p_date_stamp => sysdate
1746 ,p_change_made_by_person_id => l_person_id
1747 ,p_cwb_audit_id => l_cwb_audit_id
1748 ,p_object_version_number => l_object_version_number
1749 );
1750 end if;
1751 end if;
1752 if(p_txn_old.attribute29 is not null) then
1753 --
1754 if(ben_cwb_audit_api.return_lookup_validity('AF19')=true) then
1755 ben_cwb_audit_api.create_audit_entry
1756 (p_group_per_in_ler_id => l_per_in_ler_id
1757 ,p_group_pl_id => l_pl_id
1758 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1759 ,p_pl_id => l_pl_id
1760 ,p_group_oipl_id => -1
1761 ,p_audit_type_cd => 'AF19'
1762 ,p_old_val_varchar => p_txn_old.attribute29
1763 ,p_new_val_varchar => l_txn_new.attribute29
1764 ,p_date_stamp => sysdate
1765 ,p_change_made_by_person_id => l_person_id
1766 ,p_cwb_audit_id => l_cwb_audit_id
1767 ,p_object_version_number => l_object_version_number
1768 );
1769 end if;
1770 end if;
1771 if(p_txn_old.attribute30 is not null) then
1772 --
1773 if(ben_cwb_audit_api.return_lookup_validity('AF20')=true) then
1774 ben_cwb_audit_api.create_audit_entry
1775 (p_group_per_in_ler_id => l_per_in_ler_id
1776 ,p_group_pl_id => l_pl_id
1777 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1778 ,p_pl_id => l_pl_id
1779 ,p_group_oipl_id => -1
1780 ,p_audit_type_cd => 'AF20'
1781 ,p_old_val_varchar => p_txn_old.attribute30
1782 ,p_new_val_varchar => l_txn_new.attribute30
1783 ,p_date_stamp => sysdate
1784 ,p_change_made_by_person_id => l_person_id
1785 ,p_cwb_audit_id => l_cwb_audit_id
1786 ,p_object_version_number => l_object_version_number
1787 );
1788 end if;
1789 end if;
1790 if(p_txn_old.attribute31 is not null) then
1791 --
1792 if(ben_cwb_audit_api.return_lookup_validity('AF21')=true) then
1793 ben_cwb_audit_api.create_audit_entry
1794 (p_group_per_in_ler_id => l_per_in_ler_id
1795 ,p_group_pl_id => l_pl_id
1796 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1797 ,p_pl_id => l_pl_id
1798 ,p_group_oipl_id => -1
1799 ,p_audit_type_cd => 'AF21'
1800 ,p_old_val_varchar => p_txn_old.attribute31
1801 ,p_new_val_varchar => l_txn_new.attribute31
1802 ,p_date_stamp => sysdate
1803 ,p_change_made_by_person_id => l_person_id
1804 ,p_cwb_audit_id => l_cwb_audit_id
1805 ,p_object_version_number => l_object_version_number
1806 );
1807 end if;
1808 end if;
1809 if(p_txn_old.attribute32 is not null) then
1810 --
1811 if(ben_cwb_audit_api.return_lookup_validity('AF22')=true) then
1812 ben_cwb_audit_api.create_audit_entry
1813 (p_group_per_in_ler_id => l_per_in_ler_id
1814 ,p_group_pl_id => l_pl_id
1815 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1816 ,p_pl_id => l_pl_id
1817 ,p_group_oipl_id => -1
1818 ,p_audit_type_cd => 'AF22'
1819 ,p_old_val_varchar => p_txn_old.attribute32
1820 ,p_new_val_varchar => l_txn_new.attribute32
1821 ,p_date_stamp => sysdate
1822 ,p_change_made_by_person_id => l_person_id
1823 ,p_cwb_audit_id => l_cwb_audit_id
1824 ,p_object_version_number => l_object_version_number
1825 );
1826 end if;
1827 end if;
1828 if(p_txn_old.attribute33 is not null) then
1829 --
1830 if(ben_cwb_audit_api.return_lookup_validity('AF23')=true) then
1831 ben_cwb_audit_api.create_audit_entry
1832 (p_group_per_in_ler_id => l_per_in_ler_id
1833 ,p_group_pl_id => l_pl_id
1834 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1835 ,p_pl_id => l_pl_id
1836 ,p_group_oipl_id => -1
1837 ,p_audit_type_cd => 'AF23'
1838 ,p_old_val_varchar => p_txn_old.attribute33
1839 ,p_new_val_varchar => l_txn_new.attribute33
1840 ,p_date_stamp => sysdate
1841 ,p_change_made_by_person_id => l_person_id
1842 ,p_cwb_audit_id => l_cwb_audit_id
1843 ,p_object_version_number => l_object_version_number
1844 );
1845 end if;
1846 end if;
1847 if(p_txn_old.attribute34 is not null) then
1848 --
1849 if(ben_cwb_audit_api.return_lookup_validity('AF24')=true) then
1853 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1850 ben_cwb_audit_api.create_audit_entry
1851 (p_group_per_in_ler_id => l_per_in_ler_id
1852 ,p_group_pl_id => l_pl_id
1854 ,p_pl_id => l_pl_id
1855 ,p_group_oipl_id => -1
1856 ,p_audit_type_cd => 'AF24'
1857 ,p_old_val_varchar => p_txn_old.attribute34
1858 ,p_new_val_varchar => l_txn_new.attribute34
1859 ,p_date_stamp => sysdate
1860 ,p_change_made_by_person_id => l_person_id
1861 ,p_cwb_audit_id => l_cwb_audit_id
1862 ,p_object_version_number => l_object_version_number
1863 );
1864 end if;
1865 end if;
1866 if(p_txn_old.attribute35 is not null) then
1867 --
1868 if(ben_cwb_audit_api.return_lookup_validity('AF25')=true) then
1869 ben_cwb_audit_api.create_audit_entry
1870 (p_group_per_in_ler_id => l_per_in_ler_id
1871 ,p_group_pl_id => l_pl_id
1872 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1873 ,p_pl_id => l_pl_id
1874 ,p_group_oipl_id => -1
1875 ,p_audit_type_cd => 'AF25'
1876 ,p_old_val_varchar => p_txn_old.attribute35
1877 ,p_new_val_varchar => l_txn_new.attribute35
1878 ,p_date_stamp => sysdate
1879 ,p_change_made_by_person_id => l_person_id
1880 ,p_cwb_audit_id => l_cwb_audit_id
1881 ,p_object_version_number => l_object_version_number
1882 );
1883 end if;
1884 end if;
1885 if(p_txn_old.attribute36 is not null) then
1886 --
1887 if(ben_cwb_audit_api.return_lookup_validity('AF26')=true) then
1888 ben_cwb_audit_api.create_audit_entry
1889 (p_group_per_in_ler_id => l_per_in_ler_id
1890 ,p_group_pl_id => l_pl_id
1891 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1892 ,p_pl_id => l_pl_id
1893 ,p_group_oipl_id => -1
1894 ,p_audit_type_cd => 'AF26'
1895 ,p_old_val_varchar => p_txn_old.attribute36
1896 ,p_new_val_varchar => l_txn_new.attribute36
1897 ,p_date_stamp => sysdate
1898 ,p_change_made_by_person_id => l_person_id
1899 ,p_cwb_audit_id => l_cwb_audit_id
1900 ,p_object_version_number => l_object_version_number
1901 );
1902 end if;
1903 end if;
1904 if(p_txn_old.attribute37 is not null) then
1905 --
1906 if(ben_cwb_audit_api.return_lookup_validity('AF27')=true) then
1907 ben_cwb_audit_api.create_audit_entry
1908 (p_group_per_in_ler_id => l_per_in_ler_id
1909 ,p_group_pl_id => l_pl_id
1910 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1911 ,p_pl_id => l_pl_id
1912 ,p_group_oipl_id => -1
1913 ,p_audit_type_cd => 'AF27'
1914 ,p_old_val_varchar => p_txn_old.attribute37
1915 ,p_new_val_varchar => l_txn_new.attribute37
1916 ,p_date_stamp => sysdate
1917 ,p_change_made_by_person_id => l_person_id
1918 ,p_cwb_audit_id => l_cwb_audit_id
1919 ,p_object_version_number => l_object_version_number
1920 );
1921 end if;
1922 end if;
1923 if(p_txn_old.attribute38 is not null) then
1924 --
1925 if(ben_cwb_audit_api.return_lookup_validity('AF28')=true) then
1926 ben_cwb_audit_api.create_audit_entry
1927 (p_group_per_in_ler_id => l_per_in_ler_id
1928 ,p_group_pl_id => l_pl_id
1929 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1930 ,p_pl_id => l_pl_id
1931 ,p_group_oipl_id => -1
1932 ,p_audit_type_cd => 'AF28'
1933 ,p_old_val_varchar => p_txn_old.attribute38
1934 ,p_new_val_varchar => l_txn_new.attribute38
1935 ,p_date_stamp => sysdate
1936 ,p_change_made_by_person_id => l_person_id
1937 ,p_cwb_audit_id => l_cwb_audit_id
1938 ,p_object_version_number => l_object_version_number
1939 );
1940 end if;
1941 end if;
1942 if(p_txn_old.attribute39 is not null) then
1943 --
1944 if(ben_cwb_audit_api.return_lookup_validity('AF29')=true) then
1945 ben_cwb_audit_api.create_audit_entry
1946 (p_group_per_in_ler_id => l_per_in_ler_id
1947 ,p_group_pl_id => l_pl_id
1948 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1949 ,p_pl_id => l_pl_id
1950 ,p_group_oipl_id => -1
1951 ,p_audit_type_cd => 'AF29'
1952 ,p_old_val_varchar => p_txn_old.attribute39
1953 ,p_new_val_varchar => l_txn_new.attribute39
1954 ,p_date_stamp => sysdate
1955 ,p_change_made_by_person_id => l_person_id
1956 ,p_cwb_audit_id => l_cwb_audit_id
1957 ,p_object_version_number => l_object_version_number
1958 );
1959 end if;
1960 end if;
1964 ben_cwb_audit_api.create_audit_entry
1961 if(p_txn_old.attribute40 is not null) then
1962 --
1963 if(ben_cwb_audit_api.return_lookup_validity('AF30')=true) then
1965 (p_group_per_in_ler_id => l_per_in_ler_id
1966 ,p_group_pl_id => l_pl_id
1967 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1968 ,p_pl_id => l_pl_id
1969 ,p_group_oipl_id => -1
1970 ,p_audit_type_cd => 'AF30'
1971 ,p_old_val_varchar => p_txn_old.attribute40
1972 ,p_new_val_varchar => l_txn_new.attribute40
1973 ,p_date_stamp => sysdate
1974 ,p_change_made_by_person_id => l_person_id
1975 ,p_cwb_audit_id => l_cwb_audit_id
1976 ,p_object_version_number => l_object_version_number
1977 );
1978 end if;
1979 end if;
1980
1981 end;
1982 --
1983 --
1984 end create_audit_record_promotion;
1985 ----------------------------------------------------
1986
1987 procedure insert_or_update_transaction
1988 (p_transaction_id in number
1989 ,p_transaction_type in varchar2
1990 ,p_txn_rec in g_txn%rowtype) is
1991 cursor c_found is
1992 select txn.transaction_id
1993 from ben_transaction txn
1994 where txn.transaction_id = p_transaction_id
1995 and txn.transaction_type = p_transaction_type;
1996 l_transaction_id number;
1997 begin
1998
1999 open c_found;
2000 fetch c_found into l_transaction_id;
2001 close c_found;
2002
2003 if l_transaction_id is null then
2004 insert into ben_transaction
2005 (transaction_id ,transaction_type
2006 ,attribute1 ,attribute2
2007 ,attribute3 ,attribute4
2008 ,attribute5 ,attribute6
2009 ,attribute7 ,attribute8
2010 ,attribute9 ,attribute10
2011 ,attribute11 ,attribute12
2012 ,attribute13 ,attribute14
2013 ,attribute15 ,attribute16
2014 ,attribute17 ,attribute18
2015 ,attribute19 ,attribute20
2016 ,attribute21 ,attribute22
2017 ,attribute23 ,attribute24
2018 ,attribute25 ,attribute26
2019 ,attribute27 ,attribute28
2020 ,attribute29 ,attribute30
2021 ,attribute31 ,attribute32
2022 ,attribute33 ,attribute34
2023 ,attribute35 ,attribute36
2024 ,attribute37 ,attribute38
2025 ,attribute39 ,attribute40)
2026 values
2027 (p_transaction_id ,p_transaction_type
2028 ,p_txn_rec.attribute1 ,p_txn_rec.attribute2
2029 ,p_txn_rec.attribute3 ,p_txn_rec.attribute4
2030 ,p_txn_rec.attribute5 ,p_txn_rec.attribute6
2031 ,p_txn_rec.attribute7 ,p_txn_rec.attribute8
2032 ,p_txn_rec.attribute9 ,p_txn_rec.attribute10
2033 ,p_txn_rec.attribute11 ,p_txn_rec.attribute12
2034 ,p_txn_rec.attribute13 ,p_txn_rec.attribute14
2035 ,p_txn_rec.attribute15 ,p_txn_rec.attribute16
2036 ,p_txn_rec.attribute17 ,p_txn_rec.attribute18
2037 ,p_txn_rec.attribute19 ,p_txn_rec.attribute20
2038 ,p_txn_rec.attribute21 ,p_txn_rec.attribute22
2039 ,p_txn_rec.attribute23 ,p_txn_rec.attribute24
2040 ,p_txn_rec.attribute25 ,p_txn_rec.attribute26
2041 ,p_txn_rec.attribute27 ,p_txn_rec.attribute28
2042 ,p_txn_rec.attribute29 ,p_txn_rec.attribute30
2043 ,p_txn_rec.attribute31 ,p_txn_rec.attribute32
2044 ,p_txn_rec.attribute33 ,p_txn_rec.attribute34
2045 ,p_txn_rec.attribute35 ,p_txn_rec.attribute36
2046 ,p_txn_rec.attribute37 ,p_txn_rec.attribute38
2047 ,p_txn_rec.attribute39 ,p_txn_rec.attribute40);
2048 else
2049 update ben_transaction
2050 set attribute1 = p_txn_rec.attribute1
2051 ,attribute2 = p_txn_rec.attribute2
2052 ,attribute3 = p_txn_rec.attribute3
2053 ,attribute4 = p_txn_rec.attribute4
2054 ,attribute5 = p_txn_rec.attribute5
2055 ,attribute6 = p_txn_rec.attribute6
2056 ,attribute7 = p_txn_rec.attribute7
2057 ,attribute8 = p_txn_rec.attribute8
2058 ,attribute9 = p_txn_rec.attribute9
2059 ,attribute10 = p_txn_rec.attribute10
2060 ,attribute11 = p_txn_rec.attribute11
2061 ,attribute12 = p_txn_rec.attribute12
2062 ,attribute13 = p_txn_rec.attribute13
2063 ,attribute14 = p_txn_rec.attribute14
2064 ,attribute15 = p_txn_rec.attribute15
2065 ,attribute16 = p_txn_rec.attribute16
2066 ,attribute17 = p_txn_rec.attribute17
2067 ,attribute18 = p_txn_rec.attribute18
2068 ,attribute19 = p_txn_rec.attribute19
2069 ,attribute20 = p_txn_rec.attribute20
2070 ,attribute21 = p_txn_rec.attribute21
2071 ,attribute22 = p_txn_rec.attribute22
2072 ,attribute23 = p_txn_rec.attribute23
2073 ,attribute24 = p_txn_rec.attribute24
2074 ,attribute25 = p_txn_rec.attribute25
2075 ,attribute26 = p_txn_rec.attribute26
2076 ,attribute27 = p_txn_rec.attribute27
2077 ,attribute28 = p_txn_rec.attribute28
2078 ,attribute29 = p_txn_rec.attribute29
2079 ,attribute30 = p_txn_rec.attribute30
2080 ,attribute31 = p_txn_rec.attribute31
2081 ,attribute32 = p_txn_rec.attribute32
2082 ,attribute33 = p_txn_rec.attribute33
2083 ,attribute34 = p_txn_rec.attribute34
2084 ,attribute35 = p_txn_rec.attribute35
2085 ,attribute36 = p_txn_rec.attribute36
2089 ,attribute40 = p_txn_rec.attribute40
2086 ,attribute37 = p_txn_rec.attribute37
2087 ,attribute38 = p_txn_rec.attribute38
2088 ,attribute39 = p_txn_rec.attribute39
2090 where transaction_id = p_transaction_id
2091 and transaction_type = p_transaction_type;
2092 end if;
2093 end insert_or_update_transaction;
2094
2095 procedure process_rating
2096 (p_validate_data in varchar2 default 'Y'
2097 ,p_assignment_id in number
2098 ,p_person_id in number
2099 ,p_business_group_id in number
2100 ,p_perf_revw_strt_dt in varchar2
2101 ,p_perf_type in varchar2
2102 ,p_perf_rating in varchar2
2103 ,p_person_name in varchar2
2104 ,p_update_person_id in number
2105 ,p_update_date in date
2106 ,p_group_pl_id in number) is
2107 l_txn g_txn%rowtype;
2108 l_process_status varchar2(30) := null;
2109 l_save_status varchar2(30) := null;
2110
2111 ---- audit changes begin ------------------------------
2112 l_txn_record ben_transaction%rowtype;
2113 cursor c_collect_record_rating
2114 (p_assgn_id number
2115 ,g_ws_perf_rec_typ varchar2
2116 ,p_perf_revw_dt varchar2
2117 ,p_perfor_type varchar2
2118 ) is
2119 select *
2120 from ben_transaction
2121 where transaction_id = p_assgn_id
2122 and transaction_type = g_ws_perf_rec_typ || p_perf_revw_dt || p_perfor_type;
2123 -------- end ------------------------------------------
2124
2125 l_encoded_message varchar2(2000);
2126 l_app_short_name varchar2(2000);
2127 l_message_name varchar2(2000);
2128
2129 begin
2130 --
2131 -- If Performance Start Date not defined, return with error.
2132 --
2133 if p_perf_revw_strt_dt is null then
2134 fnd_message.set_name ('BEN', 'BEN_93190_PERF_STRT_NOT_DFND');
2135 fnd_message.raise_error;
2136 end if;
2137 --
2138
2139 ---- audit changes begin ------------------------------
2140 -------------------------------------------------------
2141 -- need of old record for writing into the audit log --
2142 -------------------------------------------------------
2143 open c_collect_record_rating(p_assignment_id,g_ws_perf_rec_type,p_perf_revw_strt_dt,p_perf_type);
2144 fetch c_collect_record_rating into l_txn_record;
2145 close c_collect_record_rating;
2146 -------- end ------------------------------------------
2147
2148 if p_perf_rating is not null then
2149 null;
2150 else
2151 delete_transaction(p_transaction_id => p_assignment_id
2152 ,p_transaction_type => g_ws_perf_rec_type||p_perf_revw_strt_dt||p_perf_type);
2153
2154 ---- audit changes begin ------------------------------
2155 if(l_txn_record.transaction_id is not null) then
2156 create_audit_record_rating(l_txn_record,p_group_pl_id);
2157 end if;
2158 -------- end ------------------------------------------
2159
2160 return;
2161 end if;
2162
2163 l_txn.assignment_id := p_assignment_id;
2164 l_txn.attribute1 := p_perf_revw_strt_dt;
2165 l_txn.attribute2 := p_perf_type;
2166 l_txn.attribute3 := p_perf_rating;
2167 l_txn.attribute4 := to_char(p_update_date, 'yyyy/mm/dd');
2168 l_txn.attribute5 := p_update_person_id;
2169
2170 if p_validate_data = 'Y' and g_validate = 'Y' then
2171 --
2172 savepoint ben_cwb_perf_upd_trans;
2173
2174 l_save_status := 'PERF_STARTED';
2175 process_rating(p_person_id => p_person_id
2176 ,p_txn_rec => l_txn
2177 ,p_business_group_id => p_business_group_id
2178 ,p_process_status => l_process_status
2179 ,p_effective_date => to_date(p_perf_revw_strt_dt,'yyyy/mm/dd'));
2180 l_save_status := 'PERF_COMPLETE';
2181
2182 rollback to ben_cwb_perf_upd_trans;
2183 l_save_status := null;
2184 --
2185 end if;
2186 --
2187 insert_or_update_transaction(p_transaction_id => p_assignment_id
2188 ,p_transaction_type => g_ws_perf_rec_type||p_perf_revw_strt_dt||p_perf_type
2189 ,p_txn_rec => l_txn);
2190
2191
2192 ---- audit changes begin ------------------------------
2193 if(l_txn_record.transaction_id is not null) then
2194 create_audit_record_rating(l_txn_record,p_group_pl_id);
2195 else
2196 l_txn_record.transaction_id := p_assignment_id;
2197 l_txn_record.attribute1 := p_perf_revw_strt_dt;
2198 l_txn_record.attribute2 := p_perf_type;
2199 l_txn_record.transaction_type := (g_ws_perf_rec_type||p_perf_revw_strt_dt||p_perf_type);
2200 create_audit_record_rating(l_txn_record,p_group_pl_id);
2201 end if;
2202 -------- end ------------------------------------------
2203
2204 --
2205 exception
2206 when others then
2207 if l_save_status is not null then
2208 rollback to ben_cwb_perf_upd_trans;
2209 end if;
2210 --
2211 ben_on_line_lf_evt.get_ser_message
2212 (p_encoded_message => l_encoded_message,
2213 p_app_short_name => l_app_short_name,
2214 p_message_name => l_message_name);
2215
2216 if l_message_name is null then
2217 fnd_message.set_name('PER','HR_ASG_PROCESS_API_ERROR');
2218 fnd_message.set_token('ERROR_MSG',substr(sqlerrm,1,1000));
2222 fnd_message.set_name('BEN', 'BEN_93371_RATING_EXST_FOR_DATE');
2219 elsif l_save_status = 'PERF_STARTED' and
2220 l_message_name = 'HR_13000_SAL_DATE_NOT_UNIQUE' then
2221 l_encoded_message := fnd_message.get_encoded;
2223 else
2224 l_encoded_message := fnd_message.get;
2225 fnd_message.set_name('BEN', 'BEN_93934_CWB_EMP_SAVE_API_ERR');
2226 fnd_message.set_token('NAME', p_person_name);
2227 fnd_message.set_token('MESSAGE', l_encoded_message);
2228 end if;
2229 fnd_msg_pub.add;
2230
2231 end process_rating;
2232
2233
2234 procedure process_promotions
2235 (p_validate_data in varchar2 default 'Y'
2236 ,p_assignment_id in number
2237 ,p_person_id in number
2238 ,p_business_group_id in number
2239 ,p_asg_updt_eff_date in varchar2
2240 ,p_change_reason in varchar2
2241 ,p_job_id in number
2242 ,p_position_id in number
2243 ,p_grade_id in number
2244 ,p_people_group_id in number
2245 ,p_soft_coding_keyflex_id in number
2246 ,p_ass_attribute1 in varchar2
2247 ,p_ass_attribute2 in varchar2
2248 ,p_ass_attribute3 in varchar2
2249 ,p_ass_attribute4 in varchar2
2250 ,p_ass_attribute5 in varchar2
2251 ,p_ass_attribute6 in varchar2
2252 ,p_ass_attribute7 in varchar2
2253 ,p_ass_attribute8 in varchar2
2254 ,p_ass_attribute9 in varchar2
2255 ,p_ass_attribute10 in varchar2
2256 ,p_ass_attribute11 in varchar2
2257 ,p_ass_attribute12 in varchar2
2258 ,p_ass_attribute13 in varchar2
2259 ,p_ass_attribute14 in varchar2
2260 ,p_ass_attribute15 in varchar2
2261 ,p_ass_attribute16 in varchar2
2262 ,p_ass_attribute17 in varchar2
2263 ,p_ass_attribute18 in varchar2
2264 ,p_ass_attribute19 in varchar2
2265 ,p_ass_attribute20 in varchar2
2266 ,p_ass_attribute21 in varchar2
2267 ,p_ass_attribute22 in varchar2
2268 ,p_ass_attribute23 in varchar2
2269 ,p_ass_attribute24 in varchar2
2270 ,p_ass_attribute25 in varchar2
2271 ,p_ass_attribute26 in varchar2
2272 ,p_ass_attribute27 in varchar2
2273 ,p_ass_attribute28 in varchar2
2274 ,p_ass_attribute29 in varchar2
2275 ,p_ass_attribute30 in varchar2
2276 ,p_person_name in varchar2
2277 ,p_update_person_id in number
2278 ,p_update_date in date
2279 ,p_group_pl_id in number) is
2280 l_txn g_txn%rowtype;
2281 l_process_status varchar2(30) := null;
2282 l_save_status varchar2(30) := null;
2283
2284 ---- audit changes begin ------------------------------
2285 l_txn_record ben_transaction%rowtype;
2286 l_txn_record_n ben_transaction%rowtype;
2287 cursor c_collect_record_promotions
2288 (p_assgn_id number
2289 ,g_ws_perf_rec_typ varchar2
2290 ,p_asg_updt_eff_dt varchar2
2291 ) is
2292 select *
2293 from ben_transaction
2294 where transaction_id = p_assgn_id
2295 and transaction_type = g_ws_perf_rec_typ || p_asg_updt_eff_dt;
2296
2297 -------- end ------------------------------------------
2298
2299
2300 l_encoded_message varchar2(2000);
2301 l_app_short_name varchar2(2000);
2302 l_message_name varchar2(2000);
2303
2304 begin
2305
2306 --
2307 -- If Assignment Update Date not defined, return with error.
2308 --
2309 if p_asg_updt_eff_date is null then
2310 fnd_message.set_name ('BEN', 'BEN_93191_PROMO_EFFDT_NOT_DFND');
2311 fnd_message.raise_error;
2312 end if;
2313
2314 ---- audit changes begin ------------------------------
2315 -------------------------------------------------------
2316 -- need of old record for writing into the audit log --
2317 -------------------------------------------------------
2318 open c_collect_record_promotions(p_assignment_id,g_ws_asg_rec_type,p_asg_updt_eff_date);
2319 fetch c_collect_record_promotions into l_txn_record;
2320 close c_collect_record_promotions;
2321 -------- end ------------------------------------------
2322
2323
2324 if p_job_id is not null or p_grade_id is not null or
2325 p_position_id is not null or p_change_reason is not null or
2326 p_people_group_id is not null or p_soft_coding_keyflex_id is not null or
2327 p_ass_attribute1 is not null or p_ass_attribute2 is not null or
2328 p_ass_attribute3 is not null or p_ass_attribute4 is not null or
2329 p_ass_attribute5 is not null or p_ass_attribute6 is not null or
2330 p_ass_attribute7 is not null or p_ass_attribute8 is not null or
2331 p_ass_attribute9 is not null or p_ass_attribute10 is not null or
2332 p_ass_attribute11 is not null or p_ass_attribute12 is not null or
2333 p_ass_attribute13 is not null or p_ass_attribute14 is not null or
2334 p_ass_attribute15 is not null or p_ass_attribute16 is not null or
2335 p_ass_attribute17 is not null or p_ass_attribute18 is not null or
2336 p_ass_attribute19 is not null or p_ass_attribute20 is not null or
2337 p_ass_attribute21 is not null or p_ass_attribute22 is not null or
2338 p_ass_attribute23 is not null or p_ass_attribute24 is not null or
2342 null;
2339 p_ass_attribute25 is not null or p_ass_attribute26 is not null or
2340 p_ass_attribute27 is not null or p_ass_attribute28 is not null or
2341 p_ass_attribute29 is not null or p_ass_attribute30 is not null then
2343 else
2344 delete_transaction(p_transaction_id => p_assignment_id
2345 ,p_transaction_type => g_ws_asg_rec_type||p_asg_updt_eff_date);
2346
2347 ---- audit changes begin ------------------------------
2348 if(l_txn_record.transaction_id is not null) then
2349 create_audit_record_promotion(l_txn_record,p_group_pl_id);
2350 end if;
2351 -------- end ------------------------------------------
2352
2353 return;
2354 end if;
2355
2356 l_txn.assignment_id := p_assignment_id;
2357 l_txn.attribute1 := p_asg_updt_eff_date;
2358 l_txn.attribute3 := p_change_reason;
2359 l_txn.attribute5 := p_job_id;
2360 l_txn.attribute6 := p_position_id;
2361 l_txn.attribute7 := p_grade_id;
2362 l_txn.attribute8 := p_people_group_id;
2363 l_txn.attribute9 := p_soft_coding_keyflex_id;
2364 l_txn.attribute11 := p_ass_attribute1;
2365 l_txn.attribute12 := p_ass_attribute2;
2366 l_txn.attribute13 := p_ass_attribute3;
2367 l_txn.attribute14 := p_ass_attribute4;
2368 l_txn.attribute15 := p_ass_attribute5;
2369 l_txn.attribute16 := p_ass_attribute6;
2370 l_txn.attribute17 := p_ass_attribute7;
2371 l_txn.attribute18 := p_ass_attribute8;
2372 l_txn.attribute19 := p_ass_attribute9;
2373 l_txn.attribute20 := p_ass_attribute10;
2374 l_txn.attribute21 := p_ass_attribute11;
2375 l_txn.attribute22 := p_ass_attribute12;
2376 l_txn.attribute23 := p_ass_attribute13;
2377 l_txn.attribute24 := p_ass_attribute14;
2378 l_txn.attribute25 := p_ass_attribute15;
2379 l_txn.attribute26 := p_ass_attribute16;
2380 l_txn.attribute27 := p_ass_attribute17;
2381 l_txn.attribute28 := p_ass_attribute18;
2382 l_txn.attribute29 := p_ass_attribute19;
2383 l_txn.attribute30 := p_ass_attribute20;
2384 l_txn.attribute31 := p_ass_attribute21;
2385 l_txn.attribute32 := p_ass_attribute22;
2386 l_txn.attribute33 := p_ass_attribute23;
2387 l_txn.attribute34 := p_ass_attribute24;
2388 l_txn.attribute35 := p_ass_attribute25;
2389 l_txn.attribute36 := p_ass_attribute26;
2390 l_txn.attribute37 := p_ass_attribute27;
2391 l_txn.attribute38 := p_ass_attribute28;
2392 l_txn.attribute39 := p_ass_attribute29;
2393 l_txn.attribute40 := p_ass_attribute30;
2394
2395 -- If people group is involved, always call the api as it
2396 -- will take care to append all the people group segments.
2397 -- If this procedure is not called in validation mode and
2398 -- as we are calling the api for people group only, do not
2399 -- show any errors as user is not expecting any now and would
2400 -- like to handle only in post-process (Bug 3227317).
2401 if (p_validate_data = 'Y' and g_validate = 'Y') or
2402 p_people_group_id is not null then
2403 --
2404 savepoint ben_cwb_asg_upd_trans;
2405
2406 l_save_status := 'PROMO_STARTED';
2407
2408 begin
2409 process_promotions(p_person_id => p_person_id
2410 ,p_asg_txn_rec => l_txn
2411 ,p_business_group_id => p_business_group_id
2412 ,p_process_status => l_process_status
2413 ,p_effective_date => to_date(p_asg_updt_eff_date,'yyyy/mm/dd'));
2414 exception
2415 when others then
2416 if p_validate_data = 'Y' and g_validate = 'Y' then
2417 raise;
2418 end if;
2419 end;
2420
2421 l_save_status := 'PROMO_COMPLETE';
2422
2423 rollback to ben_cwb_asg_upd_trans;
2424 l_save_status := null;
2425 --
2426 end if;
2427 --
2428 insert_or_update_transaction(p_transaction_id => p_assignment_id
2429 ,p_transaction_type => g_ws_asg_rec_type||p_asg_updt_eff_date
2430 ,p_txn_rec => l_txn);
2431
2432 ---- audit changes begin ------------------------------
2433 if(l_txn_record.transaction_id is not null) then
2434 create_audit_record_promotion(l_txn_record,p_group_pl_id);
2435 else
2436 l_txn_record.transaction_id := p_assignment_id;
2437 l_txn_record.attribute1 := p_asg_updt_eff_date;
2438 l_txn_record.transaction_type := (g_ws_asg_rec_type||p_asg_updt_eff_date);
2439 create_audit_record_promotion(l_txn_record,p_group_pl_id);
2440 end if;
2441 -------- end ------------------------------------------
2442
2443 --
2444 exception
2445 when others then
2446 if l_save_status is not null then
2447 rollback to ben_cwb_asg_upd_trans;
2448 end if;
2449 --
2450 ben_on_line_lf_evt.get_ser_message
2451 (p_encoded_message => l_encoded_message,
2452 p_app_short_name => l_app_short_name,
2453 p_message_name => l_message_name);
2454
2455 if l_message_name is null then
2456 fnd_message.set_name('PER','HR_ASG_PROCESS_API_ERROR');
2457 fnd_message.set_token('ERROR_MSG',substr(sqlerrm,1,1000));
2458 else
2459 l_encoded_message := fnd_message.get;
2460 fnd_message.set_name('BEN', 'BEN_93934_CWB_EMP_SAVE_API_ERR');
2461 fnd_message.set_token('NAME', p_person_name);
2462 fnd_message.set_token('MESSAGE', l_encoded_message);
2463 end if;
2467
2464 fnd_msg_pub.add;
2465
2466 end process_promotions;
2468
2469 procedure process_rating
2470 (p_person_id in number
2471 ,p_txn_rec in g_txn%rowtype
2472 ,p_business_group_id in number
2473 ,p_audit_log in varchar2 default 'N'
2474 ,p_process_status in out nocopy varchar2
2475 ,p_group_per_in_ler_id in number default null
2476 ,p_effective_date in date) is
2477 l_proc varchar2(80) := g_package || '.process_rating';
2478 l_evt_ovn number;
2479 l_next_review_date_warning boolean;
2480 l_perf_date date;
2481 l_event_id number;
2482 l_performance_review_id number;
2483 l_perf_ovn number;
2484 l_update_event_id number;
2485 l_update_review_id number;
2486 l_event_type varchar2(30);
2487 --
2488 cursor c_performance_id_in_db is
2489 select perf.performance_review_id
2490 ,perf.event_id
2491 ,perf.object_version_number
2492 from per_performance_reviews perf
2493 where perf.person_id = p_person_id
2494 and perf.review_date = l_perf_date;
2495 --
2496 cursor c_perf_id_attached_event_type is
2497 select evt.type
2498 from per_events evt
2499 where evt.assignment_id = p_txn_rec.assignment_id
2500 and evt.date_start <= l_perf_date
2501 and evt.event_id = l_event_id;
2502 --
2503 begin
2504 --
2505 hr_utility.set_location('Entering ' || l_proc, 5);
2506 l_event_type := null;
2507 --
2508 if (p_txn_rec.attribute1 is null or
2509 p_txn_rec.attribute3 is null) then
2510 hr_utility.set_location('No Processing returning ' || l_proc, 5);
2511 return;
2512 else
2513 l_perf_date := to_date(p_txn_rec.attribute1, 'yyyy/mm/dd');
2514
2515 l_perf_date := p_effective_date;
2516
2517 hr_utility.set_location('l_perf_date ' || l_perf_date, 10);
2518
2519 open c_performance_id_in_db;
2520 fetch c_performance_id_in_db into l_performance_review_id, l_event_id, l_perf_ovn;
2521 close c_performance_id_in_db;
2522
2523 if l_event_id is not null then
2524 open c_perf_id_attached_event_type;
2525 fetch c_perf_id_attached_event_type into l_event_type;
2526 close c_perf_id_attached_event_type;
2527 end if;
2528
2529 hr_utility.set_location(l_performance_review_id||','||l_event_id||','||l_perf_date,15);
2530 hr_utility.set_location(l_event_type,16);
2531 hr_utility.set_location(p_txn_rec.attribute2,17);
2532
2533 if(l_event_type is not null) then
2534 if (l_event_type <> nvl(p_txn_rec.attribute2,'-1')) then
2535 hr_utility.set_location('Rating type mismatch '||l_event_type||'&'||p_txn_rec.attribute2, 20);
2536 fnd_message.set_name ('BEN', 'BEN_93371_RATING_EXST_FOR_DATE');
2537 fnd_message.raise_error;
2538 end if;
2539 end if;
2540
2541 if (p_txn_rec.attribute2 is not null and l_event_id is null) then
2542 if(l_performance_review_id is not null) then
2543 hr_utility.set_location('Not Creating Evt. Assign Id: ' ||p_txn_rec.assignment_id, 30);
2544 fnd_message.set_name ('BEN', 'BEN_93371_RATING_EXST_FOR_DATE');
2545 fnd_message.raise_error;
2546 else
2547 hr_utility.set_location('Creating Evt. Assign Id: ' ||p_txn_rec.assignment_id, 30);
2548 per_events_api.create_event(
2549 p_validate => false
2550 ,p_date_start => l_perf_date
2551 ,p_type => p_txn_rec.attribute2
2552 ,p_business_group_id => p_business_group_id
2553 ,p_assignment_id => p_txn_rec.assignment_id
2554 ,p_emp_or_apl => 'E'
2555 ,p_event_id => l_event_id
2556 ,p_object_version_number => l_evt_ovn);
2557 end if;
2558 end if;
2559
2560 if(l_performance_review_id is not null) then
2561 hr_utility.set_location('Updating Review Record ', 40);
2562 hr_perf_review_api.update_perf_review(
2563 p_validate => false
2564 ,p_performance_review_id => l_performance_review_id
2565 ,p_performance_rating => p_txn_rec.attribute3
2566 ,p_object_version_number => l_perf_ovn
2567 ,p_next_review_date_warning => l_next_review_date_warning);
2568 else
2569 if(l_event_id is not null) then
2570 hr_utility.set_location('Creating Review Record ', 40);
2571 hr_perf_review_api.create_perf_review(
2572 p_validate => false
2573 ,p_performance_review_id => l_performance_review_id
2574 ,p_person_id => p_person_id
2575 ,p_event_id => l_event_id
2576 ,p_review_date => l_perf_date
2577 ,p_performance_rating => p_txn_rec.attribute3
2578 ,p_object_version_number => l_perf_ovn
2579 ,p_next_review_date_warning => l_next_review_date_warning);
2580 else
2581 hr_utility.set_location('Creating Review Record with null event', 40);
2582 hr_perf_review_api.create_perf_review(
2583 p_validate => false
2584 ,p_performance_review_id => l_performance_review_id
2585 ,p_person_id => p_person_id
2589 ,p_next_review_date_warning => l_next_review_date_warning);
2586 ,p_review_date => l_perf_date
2587 ,p_performance_rating => p_txn_rec.attribute3
2588 ,p_object_version_number => l_perf_ovn
2590 end if;
2591 end if;
2592 end if;
2593
2594 --
2595 if p_group_per_in_ler_id is not null then
2596 --
2597 update ben_cwb_person_info
2598 set new_perf_event_id = l_update_event_id,
2599 new_perf_review_id = l_update_review_id
2600 where group_per_in_ler_id = p_group_per_in_ler_id;
2601 --
2602 end if;
2603 --
2604 p_process_status := 'CWB_PERF_SUS';
2605 --
2606 hr_utility.set_location('Leaving ' || l_proc, 5);
2607 --
2608 EXCEPTION
2609 WHEN OTHERS THEN
2610 p_process_status := null;
2611 raise;
2612
2613 end process_rating;
2614
2615 procedure process_promotions
2616 (p_person_id in number
2617 ,p_asg_txn_rec in g_txn%rowtype
2618 ,p_business_group_id in number
2619 ,p_audit_log in varchar2 default 'N'
2620 ,p_process_status in out nocopy varchar2
2621 ,p_group_per_in_ler_id in number default null
2622 ,p_effective_date in date) is
2623 --
2624 l_proc varchar2(80) := g_package || '.process_promotions';
2625 l_effective_date date;
2626 l_assignment_id number;
2627 l_datetrack_mode varchar2(30);
2628 l_concat_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
2629 l_group_name pay_people_groups.group_name%TYPE;
2630 l_comment_id per_all_assignments_f.comment_id%TYPE;
2631 l_no_mgr_warning boolean;
2632 l_othr_mgr_warning boolean;
2633 l_spp_delete_warning boolean;
2634 l_tax_dist_changed boolean;
2635 l_entry_chg_warning varchar2(30);
2636 l_scl_id number;
2637 l_people_group_id number;
2638 l_update_position_id number;
2639 l_update_done boolean := false;
2640 --
2641 cursor c_asg is
2642 select asg.*
2643 from per_all_assignments_f asg
2644 where asg.assignment_id = l_assignment_id
2645 and l_effective_date between
2646 asg.effective_start_date and asg.effective_end_date;
2647 l_asg_rec c_asg%rowtype;
2648 --
2649 begin
2650 --
2651 hr_utility.set_location('Entering ' || l_proc, 5);
2652 --
2653 if p_asg_txn_rec.attribute1 is not null then
2654 l_effective_date := to_date(p_asg_txn_rec.attribute1, 'yyyy/mm/dd');
2655 l_assignment_id := p_asg_txn_rec.assignment_id;
2656 end if;
2657
2658 l_effective_date := p_effective_date;
2659
2660 if l_effective_date is null or l_assignment_id is null then
2661 return;
2662 end if;
2663
2664 open c_asg;
2665 fetch c_asg into l_asg_rec;
2666 close c_asg;
2667
2668 l_people_group_id := nvl(p_asg_txn_rec.attribute8, l_asg_rec.people_group_id);
2669 l_scl_id := nvl(p_asg_txn_rec.attribute9, l_asg_rec.soft_coding_keyflex_id);
2670
2671 if p_asg_txn_rec.attribute3 is not null or p_asg_txn_rec.attribute10 is not null or
2672 p_asg_txn_rec.attribute11 is not null or p_asg_txn_rec.attribute12 is not null or
2673 p_asg_txn_rec.attribute13 is not null or p_asg_txn_rec.attribute14 is not null or
2674 p_asg_txn_rec.attribute15 is not null or p_asg_txn_rec.attribute16 is not null or
2675 p_asg_txn_rec.attribute17 is not null or p_asg_txn_rec.attribute18 is not null or
2676 p_asg_txn_rec.attribute19 is not null or p_asg_txn_rec.attribute20 is not null or
2677 p_asg_txn_rec.attribute21 is not null or p_asg_txn_rec.attribute22 is not null or
2678 p_asg_txn_rec.attribute23 is not null or p_asg_txn_rec.attribute24 is not null or
2679 p_asg_txn_rec.attribute25 is not null or p_asg_txn_rec.attribute26 is not null or
2680 p_asg_txn_rec.attribute27 is not null or p_asg_txn_rec.attribute28 is not null or
2681 p_asg_txn_rec.attribute29 is not null or p_asg_txn_rec.attribute30 is not null or
2682 p_asg_txn_rec.attribute31 is not null or p_asg_txn_rec.attribute32 is not null or
2683 p_asg_txn_rec.attribute33 is not null or p_asg_txn_rec.attribute34 is not null or
2684 p_asg_txn_rec.attribute35 is not null or p_asg_txn_rec.attribute36 is not null or
2685 p_asg_txn_rec.attribute37 is not null or p_asg_txn_rec.attribute38 is not null or
2686 p_asg_txn_rec.attribute39 is not null or p_asg_txn_rec.attribute40 is not null or
2687 p_asg_txn_rec.attribute9 is not null then
2688 --
2689 hr_utility.set_location('Updating Assign Flex' , 30);
2690 /*if l_asg_rec.effective_start_date = l_effective_date then
2691 l_datetrack_mode := hr_api.g_correction;
2692 else
2693 l_datetrack_mode := hr_api.g_update;
2694 end if;*/
2695
2696 l_datetrack_mode := get_update_mode(
2697 p_assignment_id => l_assignment_id
2698 ,p_ovn => l_asg_rec.object_version_number
2699 ,p_effective_date => l_effective_date
2700 );
2701 ben_batch_utils.WRITE('l_datetrack_mode '|| l_datetrack_mode);
2702
2703 hr_assignment_api.update_emp_asg(
2704 p_validate => false
2705 ,p_effective_date => l_effective_date
2709 ,p_change_reason => nvl(p_asg_txn_rec.attribute3,
2706 ,p_datetrack_update_mode => l_datetrack_mode
2707 ,p_assignment_id => l_assignment_id
2708 ,p_object_version_number => l_asg_rec.object_version_number
2710 l_asg_rec.change_reason)
2711 ,p_ass_attribute_category => l_asg_rec.ass_attribute_category
2712 ,p_ass_attribute1 => nvl(p_asg_txn_rec.attribute11,
2713 l_asg_rec.ass_attribute1)
2714 ,p_ass_attribute2 => nvl(p_asg_txn_rec.attribute12,
2715 l_asg_rec.ass_attribute2)
2716 ,p_ass_attribute3 => nvl(p_asg_txn_rec.attribute13,
2717 l_asg_rec.ass_attribute3)
2718 ,p_ass_attribute4 => nvl(p_asg_txn_rec.attribute14,
2719 l_asg_rec.ass_attribute4)
2720 ,p_ass_attribute5 => nvl(p_asg_txn_rec.attribute15,
2721 l_asg_rec.ass_attribute5)
2722 ,p_ass_attribute6 => nvl(p_asg_txn_rec.attribute16,
2723 l_asg_rec.ass_attribute6)
2724 ,p_ass_attribute7 => nvl(p_asg_txn_rec.attribute17,
2725 l_asg_rec.ass_attribute7)
2726 ,p_ass_attribute8 => nvl(p_asg_txn_rec.attribute18,
2727 l_asg_rec.ass_attribute8)
2728 ,p_ass_attribute9 => nvl(p_asg_txn_rec.attribute19,
2729 l_asg_rec.ass_attribute9)
2730 ,p_ass_attribute10 => nvl(p_asg_txn_rec.attribute20,
2731 l_asg_rec.ass_attribute10)
2732 ,p_ass_attribute11 => nvl(p_asg_txn_rec.attribute21,
2733 l_asg_rec.ass_attribute11)
2734 ,p_ass_attribute12 => nvl(p_asg_txn_rec.attribute22,
2735 l_asg_rec.ass_attribute12)
2736 ,p_ass_attribute13 => nvl(p_asg_txn_rec.attribute23,
2737 l_asg_rec.ass_attribute13)
2738 ,p_ass_attribute14 => nvl(p_asg_txn_rec.attribute24,
2739 l_asg_rec.ass_attribute14)
2740 ,p_ass_attribute15 => nvl(p_asg_txn_rec.attribute25,
2741 l_asg_rec.ass_attribute15)
2742 ,p_ass_attribute16 => nvl(p_asg_txn_rec.attribute26,
2743 l_asg_rec.ass_attribute16)
2744 ,p_ass_attribute17 => nvl(p_asg_txn_rec.attribute27,
2745 l_asg_rec.ass_attribute17)
2746 ,p_ass_attribute18 => nvl(p_asg_txn_rec.attribute28,
2747 l_asg_rec.ass_attribute18)
2748 ,p_ass_attribute19 => nvl(p_asg_txn_rec.attribute29,
2749 l_asg_rec.ass_attribute19)
2750 ,p_ass_attribute20 => nvl(p_asg_txn_rec.attribute30,
2751 l_asg_rec.ass_attribute20)
2752 ,p_ass_attribute21 => nvl(p_asg_txn_rec.attribute31,
2753 l_asg_rec.ass_attribute21)
2754 ,p_ass_attribute22 => nvl(p_asg_txn_rec.attribute32,
2755 l_asg_rec.ass_attribute22)
2756 ,p_ass_attribute23 => nvl(p_asg_txn_rec.attribute33,
2757 l_asg_rec.ass_attribute23)
2758 ,p_ass_attribute24 => nvl(p_asg_txn_rec.attribute34,
2759 l_asg_rec.ass_attribute24)
2760 ,p_ass_attribute25 => nvl(p_asg_txn_rec.attribute35,
2761 l_asg_rec.ass_attribute25)
2762 ,p_ass_attribute26 => nvl(p_asg_txn_rec.attribute36,
2763 l_asg_rec.ass_attribute26)
2764 ,p_ass_attribute27 => nvl(p_asg_txn_rec.attribute37,
2765 l_asg_rec.ass_attribute27)
2766 ,p_ass_attribute28 => nvl(p_asg_txn_rec.attribute38,
2767 l_asg_rec.ass_attribute28)
2768 ,p_ass_attribute29 => nvl(p_asg_txn_rec.attribute39,
2769 l_asg_rec.ass_attribute29)
2770 ,p_ass_attribute30 => nvl(p_asg_txn_rec.attribute40,
2771 l_asg_rec.ass_attribute30)
2772 ,p_concatenated_segments => l_concat_segments
2773 ,p_soft_coding_keyflex_id => l_scl_id
2774 ,p_comment_id => l_comment_id
2775 ,p_effective_start_date => l_asg_rec.effective_start_date
2776 ,p_effective_end_date => l_asg_rec.effective_end_date
2777 ,p_no_managers_warning => l_no_mgr_warning
2778 ,p_other_manager_warning => l_othr_mgr_warning);
2779 --
2780 l_update_done := true;
2781 p_process_status := 'CWB_PROM_SUS';
2782 --
2783 end if;
2784 --
2785 if p_asg_txn_rec.attribute5 is not null or
2786 p_asg_txn_rec.attribute6 is not null or
2787 p_asg_txn_rec.attribute7 is not null or
2788 p_asg_txn_rec.attribute8 is not null then
2789 --
2790 hr_utility.set_location('Updating Job/Grade/Position People Group ', 40);
2791 --
2792 /*if l_asg_rec.effective_start_date = l_effective_date then
2793 l_datetrack_mode := hr_api.g_correction;
2794 else
2795 l_datetrack_mode := hr_api.g_update;
2796 end if;*/
2797
2798 l_datetrack_mode := get_update_mode(
2799 p_assignment_id => l_assignment_id
2800 ,p_ovn => l_asg_rec.object_version_number
2801 ,p_effective_date => l_effective_date
2802 );
2803 ben_batch_utils.WRITE('l_datetrack_mode '|| l_datetrack_mode);
2804
2805 --
2806 -- If the employee already had a position and the user changes only
2807 -- the job and does not change the position, then update the
2808 -- assignment with null position id as it would have resulted
2809 -- in invalid job/position combination.
2810 --
2811 if p_asg_txn_rec.attribute6 is null and
2812 l_asg_rec.position_id is not null and
2813 p_asg_txn_rec.attribute5 is not null then
2814 -- Shashank, please have a warning logged in post-process
2815 -- in this case.
2816 l_update_position_id := null;
2817 else
2818 l_update_position_id := nvl(p_asg_txn_rec.attribute6,
2819 l_asg_rec.position_id);
2820 end if;
2821
2822 hr_assignment_api.update_emp_asg_criteria(
2823 p_validate => false
2824 ,p_effective_date => l_effective_date
2825 ,p_datetrack_update_mode => l_datetrack_mode
2826 ,p_assignment_id => l_assignment_id
2827 ,p_object_version_number => l_asg_rec.object_version_number
2828 ,p_grade_id => nvl(p_asg_txn_rec.attribute7,
2829 l_asg_rec.grade_id)
2830 ,p_position_id => l_update_position_id
2831 ,p_job_id => nvl(p_asg_txn_rec.attribute5,
2832 l_asg_rec.job_id)
2833 ,p_special_ceiling_step_id => l_asg_rec.special_ceiling_step_id
2834 ,p_group_name => l_group_name
2835 ,p_effective_start_date => l_asg_rec.effective_start_date
2836 ,p_effective_end_date => l_asg_rec.effective_end_date
2837 ,p_people_group_id => l_people_group_id
2838 ,p_org_now_no_manager_warning => l_no_mgr_warning
2839 ,p_other_manager_warning => l_othr_mgr_warning
2840 ,p_spp_delete_warning => l_spp_delete_warning
2841 ,p_entries_changed_warning => l_entry_chg_warning
2842 ,p_tax_district_changed_warning => l_tax_dist_changed);
2843 --
2844 l_update_done := true;
2845 p_process_status := 'CWB_PROM_SUS';
2846 --
2847 end if;
2848 --
2849 if p_group_per_in_ler_id is not null and l_update_done then
2850 --
2851 update ben_cwb_person_info
2852 set new_assgn_ovn = l_asg_rec.object_version_number
2853 where group_per_in_ler_id = p_group_per_in_ler_id;
2854 --
2855 end if;
2856 --
2857 hr_utility.set_location('Leaving ' || l_proc, 5);
2858 --
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 p_process_status := null;
2862 raise;
2863 end process_promotions;
2864
2865
2866 end ben_cwb_asg_update;