[Home] [Help]
PACKAGE BODY: APPS.BEN_EEV_LER
Source
4 ,p_new IN g_eev_ler_rec) is
1 package body ben_eev_ler as
2 /* $Header: beeevtrg.pkb 120.0 2005/05/28 02:07:20 appldev noship $*/
3 procedure ler_chk(p_old IN g_eev_ler_rec
5 l_session_date DATE;
6 l_person_id NUMBER;
7 l_business_group_id NUMBER;
8 l_system_date DATE;
9 --
10 cursor get_person_bg_id IS
11 select per.person_id, per.business_group_id
12 from ben_prtt_rt_val prv,
13 ben_prtt_enrt_rslt per
14 where prv.element_entry_value_id = p_new.element_entry_value_id
15 and l_session_date between rt_strt_dt and rt_end_dt
16 and prv.prtt_enrt_rslt_id = per.prtt_enrt_rslt_id
17 and prv.business_group_id = per.business_group_id ;
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 ler
33 where ler.business_group_id = l_business_group_id
34 and l_session_date between ler.effective_start_date
35 and ler.effective_end_date -- For Bug 3299709
36 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
37 and exists
38 (select 1
39 from ben_per_info_chg_cs_ler_f psl
40 ,ben_ler_per_info_cs_ler lpl
41 where source_table = 'PAY_ELEMENT_ENTRY_VALUES_F'
42 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
43 and lpl.business_group_id = psl.business_group_id
44 and lpl.business_group_id = ler.business_group_id
45 and l_session_date between psl.effective_start_date
46 and psl.effective_end_date
47 and l_session_date between lpl.effective_start_date
48 and lpl.effective_end_date -- For Bug 3299709
49 and lpl.ler_id = ler.ler_id)
50 OR
51 exists (select 1 from
52 ben_rltd_per_chg_cs_ler_f rpc,
53 ben_ler_rltd_per_cs_ler lrp
54 where source_table = 'PAY_ELEMENT_ENTRY_VALUES_F'
55 and lrp.business_group_id = rpc.business_group_id
56 and lrp.business_group_id = ler.business_group_id
57 and l_session_date between rpc.effective_start_date
58 and rpc.effective_end_date
59 and l_session_date between lrp.effective_start_date
60 and lrp.effective_end_date -- For Bug 3299709
61 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
62 and lrp.ler_id = ler.ler_id)
63 order by ler.ler_id;
64 --
65 cursor get_ler_col(p_ler_id IN NUMBER) is
66 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
67 from ben_ler_per_info_cs_ler lpl, ben_per_info_chg_cs_ler_f psl
68 where lpl.ler_id = p_ler_id and
69 lpl.business_group_id = l_business_group_id
70 and lpl.business_group_id = psl.business_group_id
71 and l_session_date between psl.effective_start_date
72 and psl.effective_end_date
73 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
74 and source_table = 'PAY_ELEMENT_ENTRY_VALUES_F'
75 UNION
76 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
77 from ben_ler_rltd_per_cs_ler lrp, ben_rltd_per_chg_cs_ler_f rpc
78 where lrp.ler_id = p_ler_id and
79 lrp.business_group_id = l_business_group_id
80 and lrp.business_group_id = rpc.business_group_id
81 and l_session_date between rpc.effective_start_date
82 and rpc.effective_end_date
83 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
84 and source_table = 'PAY_ELEMENT_ENTRY_VALUES_F'
85 order by 1;
86 --
87 cursor le_exists(p_person_id in number
91 from ben_ptnl_ler_for_per
88 ,p_ler_id in number
89 ,p_lf_evt_ocrd_dt in date) is
90 select 'Y'
92 where person_id = p_person_id
93 and ler_id = p_ler_id
94 and ptnl_ler_for_per_stat_cd = 'DTCTD'
95 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
96 --
97 cursor get_contacts(p_person_id in number) is
98 select contact_person_id
99 from per_contact_relationships
100 where person_id = p_person_id
101 and business_group_id = l_business_group_id
102 and l_session_date between nvl(date_start,l_session_date)
103 and nvl(date_end,l_session_date)
104 order by person_id;
105 --
106 l_changed BOOLEAN;
107 l_ler_id NUMBER;
108 l_typ_cd ben_ler_f.typ_cd%type ;
109 l_ocrd_dt_cd VARCHAR2(30);
110 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
111 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
112 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
113 l_per_info_chg_cs_ler_rl number;
114 l_rule_output VARCHAR2(1);
115 l_ovn NUMBER;
116 l_ptnl_id NUMBER;
117 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
118 l_effective_start_date DATE ;
119 --l_session_date DATE ;
120 l_lf_evt_ocrd_date DATE ;
121 l_le_exists VARCHAR2(1);
122 l_mnl_dt date;
123 l_dtctd_dt date;
124 l_procd_dt date;
125 l_unprocd_dt date;
126 l_voidd_dt date;
127 l_type VARCHAR2(1);
128 l_hld_person_id NUMBER;
129 --
130 --
131 l_rule_overrides_flag VARCHAR2(1);
132 l_chg_mandatory_cd VARCHAR2(1);
133 l_trigger boolean := TRUE;
134 --
135 --
136 l_bool BOOLEAN;
137 l_status VARCHAR2(1);
138 l_industry VARCHAR2(1);
139 --
140 begin
141
142 -- Bug 3320133
143 benutils.set_data_migrator_mode;
144 if hr_general.g_data_migrator_mode in ( 'Y','P') then
145 --
146 return;
147 --
148 end if;
149 --
150 -- End of Bug 3320133
151
152 hr_utility.set_location(' Entering: ben_eev_trigger', 10);
153
154 l_bool :=fnd_installation.get(appl_id => 805
155 ,dep_appl_id =>805
156 ,status => l_status
157 ,industry => l_industry);
158
159
160 l_changed := FALSE;
161 open get_session_date;
162 fetch get_session_date into l_session_date;
163 close get_session_date;
164 open get_person_bg_id;
165 fetch get_person_bg_id into l_person_id, l_business_group_id ;
166 close get_person_bg_id;
167 open get_system_date;
168 fetch get_system_date into l_system_date;
169 close get_system_date;
170 l_effective_start_date := l_session_date;
171 -- l_lf_evt_ocrd_date := l_session_date;
172 --
173 open get_ler(l_status);
174 loop
175 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
176 exit when get_ler%notfound;
177 l_trigger := TRUE;
178 if (l_ocrd_dt_cd = 'DR') and (l_system_date > l_session_date) then
182 l_lf_evt_ocrd_date := l_session_date;
179 l_lf_evt_ocrd_date := l_system_date;
180 hr_utility.set_location('DR system date '||l_lf_evt_ocrd_date, 20);
181 else
183 hr_utility.set_location('session date '||l_lf_evt_ocrd_date, 20);
184 end if;
185 open get_ler_col(l_ler_id);
186 loop
187 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;
188 exit when get_ler_col%NOTFOUND;
189 l_changed := TRUE;
190 if get_ler_col%ROWCOUNT = 1 then
191 l_changed := TRUE;
192 end if;
193 hr_utility.set_location(' ben_eev_trigger', 20);
194 -- hr_utility.set_location('New'||p_new.person_type_id, 20);
195 -- hr_utility.set_location('New'||p_old.person_type_id, 20);
196 hr_utility.set_location(' ben_eev_trigger', 20);
197 --
198 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
199 -- If it returns Y, then see the applicability of the data
200 -- changes based on new and old values.
201 --
202 l_rule_output := 'Y';
203 --
204 if l_per_info_chg_cs_ler_rl is not null then
205 --
206 benutils.exec_rule(
207 p_formula_id => l_per_info_chg_cs_ler_rl,
208 p_effective_date => l_session_date,
209 p_lf_evt_ocrd_dt => null,
210 p_business_group_id => l_business_group_id, -- nvl(p_new.business_group_id, p_old.business_group_id),
211 p_person_id => l_person_id, -- nvl(p_new.person_id, p_old.person_id),
212 p_new_value => l_new_val,
213 p_old_value => l_old_val,
214 p_column_name => l_column,
215 p_ret_val => l_rule_output);
216 --
217 end if;
218 --
219
220 --
221 if l_column = 'SCREEN_ENTRY_VALUE' then
222 l_changed := (benutils.column_changed(p_old.screen_entry_value
223 ,p_new.screen_entry_value,l_new_val) AND
224 benutils.column_changed(p_new.screen_entry_value
225 ,p_old.screen_entry_value,l_old_val) AND
226 (l_changed));
227 end if;
228 hr_utility.set_location(' ben_eev_trigger', 30);
229 --
230
231 --
232 -- Checking the rule output and the rule override flag.
233 -- Whether the rule is mandatory or not, rule output should return 'Y'
234 -- Rule Mandatory flag is just to override the column data change.
235
236 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
237 l_changed := TRUE ;
238 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
239 l_changed := l_changed AND TRUE;
240 elsif l_rule_output = 'N' then
241 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
242 l_changed := FALSE;
243 end if;
244
245 hr_utility.set_location('After the rule Check ',20.05);
246 if l_changed then
247 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
248 else
249 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
250 end if;
251 -- Check for Column Mandatory Change
252 -- If column change is mandatory and data change has failed then dont trigger
253 -- If column change is non-mandatory and the data change has passed, then trigger.
254
255 if l_chg_mandatory_cd = 'Y' and not l_changed then
256 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
257 l_changed := FALSE;
258 l_trigger := FALSE;
259 exit;
260 elsif l_chg_mandatory_cd = 'Y' and l_changed then
261 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
262 l_changed := TRUE;
263 -- exit; */
264 elsif l_chg_mandatory_cd = 'N' and l_changed then
265 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
266 l_changed := TRUE;
267 l_trigger := TRUE;
268 exit;
269 end if;
270
271
272 hr_utility.set_location('After the Mandatory code check ',20.05);
273 if l_changed then
274 hr_utility.set_location(' l_change TRUE ', 20.1);
275 else
276 hr_utility.set_location(' l_change FALSE ', 20.1);
277 end if;
278 --
279 /* if not l_changed then
280 exit;
281 end if; */
282 --
283 end loop;
284 hr_utility.set_location(' ben_eev_trigger', 50);
285 l_ptnl_id := 0;
286 l_ovn :=null;
287 if l_trigger then
288 if l_type = 'P' then
289 open le_exists(l_person_id,l_ler_id,l_lf_evt_ocrd_date);
290 fetch le_exists into l_le_exists;
291 if le_exists%notfound then
292 hr_utility.set_location(' Entering: ben_eev_trigger5', 60);
293
294 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
295 -- ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
296 (p_validate => false
297 ,p_ptnl_ler_for_per_id => l_ptnl_id
298 ,p_ntfn_dt => l_system_date
299 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
300 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
301 ,p_ler_id => l_ler_id
302 ,p_ler_typ_cd => l_typ_cd
303 ,p_person_id => l_person_id
304 ,p_business_group_Id =>l_business_group_id
305 ,p_object_version_number => l_ovn
306 ,p_effective_date => l_effective_start_date
307 ,p_dtctd_dt => l_effective_start_date);
308 end if;
309 close le_exists;
310 elsif l_type = 'R' then
311 hr_utility.set_location(' Entering: ben_eev_trigger5-', 65);
312 open get_contacts(l_person_id);
313 loop
314 fetch get_contacts into l_hld_person_id;
315 exit when get_contacts%notfound;
316 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
317 fetch le_exists into l_le_exists;
318 if le_exists%notfound then
319 hr_utility.set_location(' Entering: ben_eev_trigger5', 60);
320 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
321 -- ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
322 (p_validate => false
323 ,p_ptnl_ler_for_per_id => l_ptnl_id
324 ,p_ntfn_dt => l_system_date
325 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
326 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
327 ,p_ler_id => l_ler_id
328 ,p_ler_typ_cd => l_typ_cd
329 ,p_person_id => l_hld_person_id
330 ,p_business_group_Id =>l_business_group_id
331 ,p_object_version_number => l_ovn
332 ,p_effective_date => l_effective_start_date
333 ,p_dtctd_dt => l_effective_start_date);
334 end if;
335 l_ptnl_id := 0;
336 l_ovn :=null;
337 close le_exists;
338 end loop;
339 /****close le_exists; if exit when get_contcts **/
340 close get_contacts;
341 end if;
342 --
343 -- reset the variables.
344 --
345 hr_utility.set_location(' ben_eev_trigger', 40);
346 l_changed := FALSE;
347 l_trigger := TRUE;
348 l_ovn := NULL;
349 l_effective_start_date := l_session_date;
350 -- l_lf_evt_ocrd_date := l_session_date;
351 end if;
352 close get_ler_col;
353 end loop;
354 hr_utility.set_location(' ben_eev_trigger', 50);
355 close get_ler;
356 hr_utility.set_location(' leaving ben_eev_trigger', 70);
357 exception
358 when others then
359 hr_utility.set_location(sqlerrm, 70);
360 raise;
361 end;
362 end ben_eev_ler;