1 package body ben_collapse_life_events as
2 /* $Header: benclple.pkb 120.0 2005/05/28 03:49:35 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Collapse Life Events
13 Purpose
14 This package is used to collapse life events. It is desigend to be
15 caled from formula but can also be called form forms or reports.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 01 Dec 98 G Perry 115.0 Created.
20 07 Dec 98 G Perry 115.1 Added in function
21 get_life_event_occured_date.
22 22 Mar 99 TMathers 115.2 Changed -MON- to /MM/
23 02 May 99 TMathers 115.3 Undatetracked PTNL_ler_for_per.
24 19 dec 01 pbodla 115.4 CWB Changes : Consider only non
25 comp work bench type potentials.
26 07-Jan-02 Rpillay 115.5 Added dbdrv and checkfile command
27 14 jul 02 pbodla 115.6 ABSENCES : DO not include absence
28 life events.
29 19-Sep-03 tjesumic 115.7 GSP LE is not considered
30 19-Sep-04 pabodla 115.8 iRec - Avoid iRec potentials in
31 collapse logic.
32 */
33 --------------------------------------------------------------------------------
34 --
35 g_package varchar2(80) := 'ben_collapse_life_events';
36 g_lf_evt_ocrd_dt date;
37 --
38 function collapse_potential(p_ler_id in number,
39 p_person_id in number,
40 p_lf_evt_ocrd_dt in date,
41 p_effective_date in date) return number is
42 --
43 l_package varchar2(80) := g_package||'.collapse_potential';
44 --
45 -- This cursor gets all the potential life events that exist for a
46 -- particular person.
47 --
48 cursor c_get_potentials is
49 select ler.typ_cd,
50 ler.ler_id,
51 ptn.lf_evt_ocrd_dt
52 from ben_ler_f ler,
53 ben_ptnl_ler_for_per ptn
54 where ptn.person_id = p_person_id
55 and ptn.ler_id = ler.ler_id
56 -- CWB Changes
57 -- ABSENCES - avoid collapsing absences life events.
58 and ler.typ_cd not in ('COMP', 'ABS','GSP', 'IREC')
59 and ptnl_ler_for_per_stat_cd in ('UNPROCD','DTCTD')
60 and p_effective_date
61 between ler.effective_start_date
62 and ler.effective_end_date
63 order by ptn.lf_evt_ocrd_dt asc;
64 --
65 -- This cursor gets information about the passed in context. It works out
66 -- whether the passed in context is a derived factor life event.
67 --
68 cursor c_life_event is
69 select null
70 from ben_ler_f ler
71 where ler.ler_id = p_ler_id
72 and ler.typ_cd in ('DRVDAGE','DRVDCAL','DRVDCMP',
73 'DRVDHRW','DRVDLOS','DRVDTPF')
74 and p_effective_date
75 between ler.effective_start_date
76 and ler.effective_end_date;
77 --
78 l_potentials c_get_potentials%rowtype;
79 l_dummy varchar2(1);
80 l_return_ler_id number;
81 l_current_derived boolean;
82 --
83 begin
84 --
85 hr_utility.set_location('Entering: '||l_package,10);
86 --
87 -- Open cursor to work out if current event is a derived factor life
88 -- event.
89 --
90 open c_life_event;
91 --
92 fetch c_life_event into l_dummy;
93 --
94 if c_life_event%found then
95 --
96 -- Current life event is for a derived factor
97 --
98 l_current_derived := true;
99 --
100 else
101 --
102 -- Current life event is not a derived factor
103 --
104 l_current_derived := false;
105 --
106 end if;
107 --
108 close c_life_event;
109 --
110 -- Open Cursor to grab all unprocessed and detected life events for
111 -- the person we are processing
112 --
113 open c_get_potentials;
114 --
115 loop
116 --
117 fetch c_get_potentials into l_potentials;
118 exit when c_get_potentials%notfound;
119 --
120 -- Now lets assume the tests we want are as follows :
121 --
122 -- If our current life event is a Derived Factor then
123 -- that life event is the collapsed life event unless
124 -- there is another life event which is a non derived factor
125 --
126 -- If our current life event is not a derived factor then
127 -- any derived factor life event is our collapsed life event
128 -- Remember we are sorting by life event occured date.
129 --
130 if l_potentials.typ_cd in ('DRVDAGE','DRVDCAL','DRVDCMP',
134 -- This life event is a derived factor and our current life
131 'DRVDHRW','DRVDLOS','DRVDTPF') and
132 not l_current_derived then
133 --
135 -- event is not a derived factor so return this as the collapsed
136 -- life event.
137 --
138 l_return_ler_id := l_potentials.ler_id;
139 g_lf_evt_ocrd_dt := l_potentials.lf_evt_ocrd_dt;
140 exit;
141 --
142 elsif l_potentials.typ_cd not in ('DRVDAGE','DRVDCAL','DRVDCMP',
143 'DRVDHRW','DRVDLOS','DRVDTPF') and
144 l_current_derived then
145 --
146 -- This life event is not a derived factor and our current life event
147 -- is a derived factor so the life event becomes the collapsed life
148 -- event.
149 --
150 l_return_ler_id := l_potentials.ler_id;
151 g_lf_evt_ocrd_dt := l_potentials.lf_evt_ocrd_dt;
152 exit;
153 --
154 end if;
155 --
156 end loop;
157 --
158 close c_get_potentials;
159 --
160 -- First check whether the l_return_ler_id variable has been set, this
161 -- could occur if there were no potential life events that existed
162 -- for the person. If so then set the l_return_ler_id to the current
163 -- life event.
164 --
165 if l_return_ler_id is null then
166 --
167 l_return_ler_id := p_ler_id;
168 g_lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
169 --
170 end if;
171 --
172 return l_return_ler_id;
173 --
174 hr_utility.set_location('Leaving: '||l_package,10);
175 --
176 end collapse_potential;
177 --
178 function collapse_life_event(p_effective_date in varchar2,
179 p_assignment_id in number,
180 p_ler_id in number) return number is
181 --
182 l_package varchar2(80) := g_package||'.collapse_life_event';
183 l_effective_date date;
184 l_lf_evt_ocrd_dt date;
185 l_person_id number;
186 l_ler_id number;
187 --
188 cursor c_get_person is
189 select paf.person_id
190 from per_assignments_f paf
191 where paf.assignment_id = p_assignment_id
192 and paf.primary_flag = 'Y'
193 and l_effective_date
194 between paf.effective_start_date
195 and paf.effective_end_date;
196 --
197 -- CWB Changes : Cursor joined to ben_ler_f
198 --
199 cursor c_person_life is
200 select ptn.lf_evt_ocrd_dt
201 from ben_ptnl_ler_for_per ptn,
202 ben_ler_f ler
203 where ptn.person_id = l_person_id
204 and ptn.ptnl_ler_for_per_stat_cd in ('UNPROCD','DTCTD')
205 and ptn.ler_id = p_ler_id
206 and ler.ler_id = ptn.ler_id
207 -- CWB Changes
208 -- ABSENCES - avoid collapsing absences life events.
209 and ler.typ_cd not in ('COMP', 'ABS','GSP', 'IREC')
210 and l_effective_date
211 between ler.effective_start_date
212 and ler.effective_end_date
213 and ptn.rowid = (select min(ptn2.rowid)
214 from ben_ptnl_ler_for_per ptn2,
215 ben_ler_f ler2
216 where ptn2.person_id = ptn.person_id
217 and ptn2.ler_id = ler2.ler_id
218 and l_effective_date
219 between ler2.effective_start_date
220 and ler2.effective_end_date
221 and ler2.typ_cd not in ('COMP', 'ABS','GSP', 'IREC')
222 and ptn2.ptnl_ler_for_per_stat_cd in
223 ('UNPROCD','DTCTD')
224 and ptn2.lf_evt_ocrd_dt =
225 (select min(ptn3.lf_evt_ocrd_dt)
226 from ben_ptnl_ler_for_per ptn3,
227 ben_ler_f ler3
228 where ptn3.person_id = ptn2.person_id
229 and ptn3.ler_id = ler3.ler_id
230 and l_effective_date
231 between ler3.effective_start_date
232 and ler3.effective_end_date
233 and ler3.typ_cd not in ( 'COMP','GSP', 'IREC', 'ABS')
234 and ptn3.ptnl_ler_for_per_stat_cd in
235 ('UNPROCD','DTCTD')
236 ));
237 --
238 -- CWB Changes End
239 --
240 begin
241 --
242 hr_utility.set_location('Entering: '||l_package,10);
243 --
244 -- First lets convert the effective_date into a date as formula only
245 -- understands text or numbers but we need it in a date format.
246 --
247 l_effective_date := to_date(p_effective_date,'DD/MM/YYYY');
248 --
249 -- Now lets derive the person we are using, since person is not a context.
250 -- but there is always a one to one mapping between assignment and person
251 --
252 open c_get_person;
253 --
254 fetch c_get_person into l_person_id;
255 --
256 if c_get_person%notfound then
257 --
258 -- In this example we error if the person can not be derived
259 --
260 hr_api.mandatory_arg_error(p_api_name => l_package,
261 p_argument => 'l_person_id',
262 p_argument_value => l_person_id);
263 --
264 end if;
265 --
266 close c_get_person;
267 --
268 -- Get the life event occured date of the event we are trying to process
272 open c_person_life;
269 -- we need this if there are no other potential life events that are out
270 -- there for the person.
271 --
273 --
274 fetch c_person_life into l_lf_evt_ocrd_dt;
275 --
276 close c_person_life;
277 --
278 -- At this point we know the person id and the effective date so
279 -- we can work out all the life events that the person currently has
280 -- as potential life events and then we can process them accordingly
281 --
282 l_ler_id := collapse_potential(p_ler_id => p_ler_id,
283 p_person_id => l_person_id,
284 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
285 p_effective_date => l_effective_date);
286 --
287 -- Return the collapsed life event
288 --
289 return l_ler_id;
290 --
291 hr_utility.set_location('Leaving: '||l_package,10);
292 --
293 end collapse_life_event;
294 --
295 function get_life_event_occured_date return varchar2 is
296 --
297 l_package varchar2(80) := g_package||'.get_life_event_occured_date';
298 --
299 begin
300 --
301 hr_utility.set_location('Entering: '||l_package,10);
302 --
303 -- We need to return the life event occured date, remember formula can't
304 -- handle dates so we must typecast the date as a string.
305 --
306 return to_char(g_lf_evt_ocrd_dt,'DD/MM/YYYY');
307 --
308 hr_utility.set_location('Leaving: '||l_package,10);
309 --
310 end get_life_event_occured_date;
311 --
312 end ben_collapse_life_events;
[Home] [Help]