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