[Home] [Help]
PACKAGE BODY: APPS.BEN_ABV_LER
Source
1 package body ben_abv_ler as
2 /* $Header: beabvtrg.pkb 120.0 2005/05/28 00:19:54 appldev noship $*/
3 procedure ler_chk(p_old IN g_abv_ler_rec
4 ,p_new IN g_abv_ler_rec
5 ,p_effective_date in date default null ) is
6 l_session_date DATE;
7 l_person_id NUMBER;
8 l_system_date DATE;
9 --
10 cursor get_person_id IS
11 select person_id
12 from per_all_assignments_f
13 where assignment_id = p_new.assignment_id
14 and business_group_id = p_new.business_group_id
15 and l_session_date
16 between effective_start_date
17 and effective_end_date;
18 --
19 cursor get_session_date IS
20 select effective_date
21 from fnd_sessions
22 where session_id = userenv('SESSIONID');
23 --
24 cursor get_system_date IS
25 select trunc(sysdate)
26 from dual;
27 --
28 cursor get_ler(l_status varchar2) is
29 select ler.ler_id
30 , ler.typ_cd
31 , ler.ocrd_dt_det_cd
32 from ben_ler_f ler
33 where ler.business_group_id = p_new.business_group_id
34 and l_session_date
35 between ler.effective_start_date
36 and ler.effective_end_date
37 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
38 and ((exists
39 (select 1
40 from ben_per_info_chg_cs_ler_f psl
41 , ben_ler_per_info_cs_ler_f lpl
42 where source_table = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
43 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
44 and lpl.business_group_id = psl.business_group_id
45 and lpl.business_group_id = ler.business_group_id
46 and l_session_date between psl.effective_start_date
47 and psl.effective_end_date
48 and l_session_date between lpl.effective_start_date
49 and lpl.effective_end_date
50 and lpl.ler_id = ler.ler_id)
51 )
52 OR (exists
53 (select 1
54 from ben_rltd_per_chg_cs_ler_f rpc
55 , ben_ler_rltd_per_cs_ler_f lrp
56 where source_table = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
57 and lrp.business_group_id = rpc.business_group_id
58 and lrp.business_group_id = ler.business_group_id
59 and l_session_date between rpc.effective_start_date
60 and rpc.effective_end_date
61 and l_session_date between lrp.effective_start_date
62 and lrp.effective_end_date
63 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
64 and lrp.ler_id = ler.ler_id)
65 ))
66 order by ler.ler_id;
67 --
68 cursor get_ler_col(p_ler_id IN NUMBER) is
69 select psl.source_column, psl.new_val, psl.old_val, 'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
70 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
71 where lpl.ler_id = p_ler_id and
72 lpl.business_group_id = p_new.business_group_id
73 and lpl.business_group_id = psl.business_group_id
74 and l_session_date between psl.effective_start_date
75 and psl.effective_end_date
76 and l_session_date between lpl.effective_start_date
77 and lpl.effective_end_date
78 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
79 and source_table = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
80 UNION
81 select rpc.source_column, rpc.new_val, rpc.old_val, 'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
82 from ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
83 where lrp.ler_id = p_ler_id and
84 lrp.business_group_id = p_new.business_group_id
85 and lrp.business_group_id = rpc.business_group_id
86 and l_session_date between rpc.effective_start_date
87 and rpc.effective_end_date
88 and l_session_date between lrp.effective_start_date
89 and lrp.effective_end_date
90 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
91 and source_table = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
92 order by 1;
93 --
94 cursor le_exists(p_person_id in number
95 ,p_ler_id in number
96 ,p_lf_evt_ocrd_dt in date) is
97 select 'Y'
98 from ben_ptnl_ler_for_per
99 where person_id = p_person_id
100 and ler_id = p_ler_id
101 and ptnl_ler_for_per_stat_cd = 'DTCTD'
102 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
103 --
104 cursor get_contacts(p_person_id in number) is
105 select person_id
106 from per_contact_relationships
107 where contact_person_id = p_person_id
108 and business_group_id = p_new.business_group_id
109 and l_session_date between nvl(date_start,l_session_date)
110 and nvl(date_end,l_session_date)
111 and personal_flag = 'Y'
112 order by person_id;
113 --
114 l_changed BOOLEAN;
115 l_ler_id NUMBER;
116 l_typ_cd ben_ler_f.typ_cd%type ;
117 l_ocrd_dt_cd VARCHAR2(30);
118 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
119 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
120 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
121 l_per_info_chg_cs_ler_rl number;
122 l_rule_output VARCHAR2(1);
123 l_ovn NUMBER;
124 l_ptnl_id NUMBER;
125 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
126 l_effective_start_date DATE ;
127 --l_session_date DATE ;
128 l_lf_evt_ocrd_date DATE ;
129 l_le_exists VARCHAR2(1);
130 l_mnl_dt date;
131 l_dtctd_dt date;
132 l_procd_dt date;
133 l_unprocd_dt date;
134 l_voidd_dt date;
135 l_type VARCHAR2(1);
136 l_hld_person_id NUMBER;
137 --
138 l_bool BOOLEAN;
139 l_status VARCHAR2(1);
140 l_industry VARCHAR2(1);
141 --
142 l_col_new_val VARCHAR2(1000);
143 l_col_old_val varchar2(1000);
144 --
145 l_rule_overrides_flag VARCHAR2(1);
146 l_chg_mandatory_cd VARCHAR2(1);
147 l_trigger boolean := TRUE;
148 --
149 --
150 begin
151
152 benutils.set_data_migrator_mode; -- Bug 3320133
153
154 if hr_general.g_data_migrator_mode in ( 'Y','P') then
155 --
156 return;
157 --
158 end if;
159
160 l_bool :=fnd_installation.get(appl_id => 805
161 ,dep_appl_id =>805
162 ,status => l_status
163 ,industry => l_industry);
164
165 begin
166 hr_utility.set_location(' Entering: ben_abv_trigger', 10);
167 l_changed := FALSE;
168 If p_effective_date is not null then
169 l_session_date := p_effective_date ;
170 else
171 open get_session_date;
172 fetch get_session_date into l_session_date;
173 close get_session_date;
174 end if ;
175 open get_person_id;
176 fetch get_person_id into l_person_id;
177 close get_person_id;
178 open get_system_date;
179 fetch get_system_date into l_system_date;
180 close get_system_date;
181 l_effective_start_date := l_session_date;
182 -- l_lf_evt_ocrd_date := l_session_date;
183 --
184 hr_utility.set_location('ASSIGNMENT_ID'||p_new.assignment_id,10);
185 hr_utility.set_location('BUSINESS_GROUP_ID'||p_new.assignment_id,10);
186 hr_utility.set_location('VALUE'||p_new.assignment_id,10);
187 hr_utility.set_location('ASSIGNMENT_BUDGET_VALUE_ID'||p_new.assignment_id,10);
188 hr_utility.set_location('EFFECTIVE_START_DATE'||p_new.assignment_id,10);
189 hr_utility.set_location('EFFECTIVE_END_DATE'||p_new.assignment_id,10);
190 hr_utility.set_location('sess '||l_session_date, 20);
191 hr_utility.set_location('PERSON '||l_person_id, 20);
192 hr_utility.set_location('BG '||p_new.business_group_id, 20);
193 --
194 open get_ler(l_status);
195 loop
196 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
197 exit when get_ler%notfound;
198 l_trigger := TRUE;
199 --
200 if l_ocrd_dt_cd is null then
201 l_lf_evt_ocrd_date := p_new.effective_start_date;
202 else
203 --
204 -- Call the common date procedure.
205 --
206 ben_determine_date.main
207 (p_date_cd => l_ocrd_dt_cd
208 ,p_effective_date => p_new.effective_start_date
209 ,p_lf_evt_ocrd_dt => p_new.effective_start_date
210 ,p_returned_date => l_lf_evt_ocrd_date
211 );
212 end if;
213 --
214 open get_ler_col(l_ler_id);
215 loop
216 fetch get_ler_col into l_column,l_new_val, l_old_val, l_type, l_per_info_chg_cs_ler_rl, l_rule_overrides_flag, l_chg_mandatory_cd;
217 exit when get_ler_col%NOTFOUND;
218 --
219
220 hr_utility.set_location('LER '||l_ler_id, 20);
221 hr_utility.set_location('COLUMN '||l_column, 20);
222 hr_utility.set_location('NEWVAL '||l_new_val, 20);
223 l_changed := TRUE;
224 if get_ler_col%ROWCOUNT = 1 then
225 l_changed := TRUE;
226 hr_utility.set_location('rowcount 1 ', 20);
227 end if;
228 hr_utility.set_location(' ben_abv_trigger', 20);
229 -- hr_utility.set_location('New'||p_new.person_type_id, 20);
230 -- hr_utility.set_location('New'||p_old.person_type_id, 20);
231 hr_utility.set_location(' ben_abv_trigger', 20);
232 --
233 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
234 -- If it returns Y, then see the applicability of the data
235 -- changes based on new and old values.
236 --
237 l_rule_output := 'Y';
238 --
239 if l_per_info_chg_cs_ler_rl is not null then
240 --
241 if l_column = 'ASSIGNMENT_ID' then
242 l_col_old_val := to_char(p_old.ASSIGNMENT_ID);
243 l_col_new_val := to_char(p_new.ASSIGNMENT_ID);
244 end if;
245 --
246 if l_column = 'VALUE' then
247 l_col_old_val := to_char(p_old.VALUE);
248 l_col_new_val := to_char(p_new.VALUE);
249 end if;
250 --
251 --
252 if l_column = 'EFFECTIVE_START_DATE' then
253 l_col_old_val := to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
254 l_col_new_val := to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
255 end if;
256 --
257 if l_column = 'EFFECTIVE_END_DATE' then
258 l_col_old_val := to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24 :MI:SS');
259 l_col_new_val := to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24 :MI:SS');
260 end if;
261 benutils.exec_rule(
262 p_formula_id => l_per_info_chg_cs_ler_rl,
263 p_effective_date => l_session_date,
264 p_lf_evt_ocrd_dt => null,
265 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
266 p_person_id => l_person_id, -- nvl(p_new.person_id, p_old.person_id),
267 p_new_value => l_col_new_val,
268 p_old_value => l_col_old_val,
269 p_column_name => l_column,
270 p_ret_val => l_rule_output,
271 p_param5 => 'BEN_ABV_IN_EFFECTIVE_START_DATE',
272 p_param5_value => to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
273 p_param6 => 'BEN_ABV_IO_EFFECTIVE_START_DATE',
274 p_param6_value => to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
275 p_param7 => 'BEN_ABV_IN_EFFECTIVE_END_DATE',
276 p_param7_value => to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
277 p_param8 => 'BEN_ABV_IO_EFFECTIVE_END_DATE',
278 p_param8_value => to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
279 p_param9 => 'BEN_IV_LER_ID', /* Bug 3891096 */
280 p_param9_value => to_char(l_ler_id),
281 p_pk_id => to_char(p_new.assignment_budget_value_id)
282 );
283 --
284 end if;
285 --
286
287 --
288 if l_column = 'VALUE' then
289 l_changed := (benutils.column_changed(p_old.value
290 ,p_new.value,l_new_val) AND
291 benutils.column_changed(p_new.value
292 ,p_old.value,l_old_val) AND
293 (l_changed));
294 end if;
295 hr_utility.set_location(' ben_abv_trigger', 30);
296 --
297 if l_column = 'ASSIGNMENT_BUDGET_VALUE_ID' then
298 l_changed := (benutils.column_changed(p_old.assignment_budget_value_id
299 ,p_new.assignment_budget_value_id,l_new_val) AND
300 benutils.column_changed(p_new.assignment_budget_value_id
301 ,p_old.assignment_budget_value_id,l_old_val) AND
302 (l_changed));
303 end if;
304 --
305
306 --
307 -- Checking the rule output and the rule override flag.
308 -- Whether the rule is mandatory or not, rule output should return 'Y'
312 l_changed := TRUE ;
309 -- Rule Mandatory flag is just to override the column data change.
310
311 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
313 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
314 l_changed := l_changed AND TRUE;
315 elsif l_rule_output = 'N' then
316 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
317 l_changed := FALSE;
318 end if;
319
320 hr_utility.set_location('After the rule Check ',20.05);
321 if l_changed then
322 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
323 else
324 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
325 end if;
326 -- Check for Column Mandatory Change
327 -- If column change is mandatory and data change has failed then dont trigger
328 -- If column change is non-mandatory and the data change has passed, then trigger.
329
330 if l_chg_mandatory_cd = 'Y' and not l_changed then
331 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
332 l_changed := FALSE;
333 l_trigger := FALSE;
334 exit;
335 elsif l_chg_mandatory_cd = 'Y' and l_changed then
336 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
337 l_changed := TRUE;
338 -- exit; */
339 elsif l_chg_mandatory_cd = 'N' and l_changed then
340 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
341 l_changed := TRUE;
342 l_trigger := TRUE;
343 exit;
344 end if;
345
346 hr_utility.set_location('After the Mandatory code check ',20.05);
347 if l_changed then
348 hr_utility.set_location(' l_change TRUE ', 20.1);
349 else
350 hr_utility.set_location(' l_change FALSE ', 20.1);
351 end if;
352 --
353 /* if not l_changed then
354 exit;
355 end if; */
356 hr_utility.set_location(' ben_abv_trigger', 40);
357 end loop;
358 hr_utility.set_location(' ben_abv_trigger', 50);
359 l_ptnl_id := 0;
360 l_ovn :=null;
361 if l_trigger then
362 if l_type = 'P' then
363 open le_exists(l_person_id,l_ler_id,l_lf_evt_ocrd_date);
364 fetch le_exists into l_le_exists;
365 if le_exists%notfound then
366 hr_utility.set_location(' Entering: ben_abv_trigger5', 60);
367
368 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
369 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
370 (p_validate => false
371 ,p_ptnl_ler_for_per_id => l_ptnl_id
372 ,p_ntfn_dt => l_system_date
373 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
374 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
375 ,p_ler_id => l_ler_id
376 ,p_ler_typ_cd => l_typ_cd
377 ,p_person_id => l_person_id
378 ,p_business_group_Id =>p_new.business_group_id
379 ,p_object_version_number => l_ovn
380 ,p_effective_date => l_effective_start_date
381 ,p_dtctd_dt => l_effective_start_date);
382 end if;
383 close le_exists;
384 elsif l_type = 'R' then
385 hr_utility.set_location(' Entering: ben_abv_trigger5-', 65);
386 open get_contacts(l_person_id);
387 loop
388 fetch get_contacts into l_hld_person_id;
389 exit when get_contacts%notfound;
390 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
391 fetch le_exists into l_le_exists;
392 if le_exists%notfound then
393 hr_utility.set_location(' Entering: ben_abv_trigger5', 60);
394
395 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
396 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
397 (p_validate => false
398 ,p_ptnl_ler_for_per_id => l_ptnl_id
399 ,p_ntfn_dt => l_system_date
400 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
401 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
402 ,p_ler_id => l_ler_id
403 ,p_ler_typ_cd => l_typ_cd
404 ,p_person_id => l_hld_person_id
405 ,p_business_group_Id =>p_new.business_group_id
406 ,p_object_version_number => l_ovn
407 ,p_effective_date => l_effective_start_date
408 ,p_mnl_dt => null
409 ,p_dtctd_dt => l_effective_start_date
410 ,p_procd_dt => null
411 ,p_unprocd_dt => null
412 ,p_mnlo_dt => null
413 ,p_voidd_dt => null);
414 end if;
415 l_ptnl_id := 0;
416 l_ovn :=null;
417 close le_exists;
418 end loop;
419 close get_contacts;
420 end if;
421 --
422 -- reset the variables.
423 --
427 l_ovn := NULL;
424 hr_utility.set_location(' ben_abv_trigger', 40);
425 l_changed := FALSE;
426 l_trigger := TRUE;
428 l_effective_start_date := l_session_date;
429 -- l_lf_evt_ocrd_date := l_session_date;
430 end if;
431 close get_ler_col;
432 end loop;
433 hr_utility.set_location(' ben_abv_trigger', 50);
434 close get_ler;
435 hr_utility.set_location(' leaving ben_abv_trigger', 70);
436 exception
437 when others then
438 hr_utility.set_location(sqlerrm, 70);
439 raise;
440 end;
441
442 end;
443 end ben_abv_ler;