[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_BEN_ELIG_ENRL_EQ
Source
1 PACKAGE BODY hri_opl_ben_elig_enrl_eq AS
2 /* $Header: hrieqeec.pkb 120.2 2005/11/15 01:10:53 bmanyam noship $ */
3 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 Name : HRI_EQ_BEN_ELIGENRL_EVTS
5 -- Purpose : Populate event queue table
6 --
7 -- PROCEDURE insert_event
8 -- insert_event
9 -- ==============
10 --
11 -- PROCEDURE update_event
12 -- update_event
13 -- ==============
14 --
15 -- PROCEDURE delete_event
16 -- delete_event
17 -- ==============
18 --
19 -- ------------------------------------------------------------------------------
20 -- History
21 -- -------
22 -- Version Date Author Comment
23 -- -------+----------+----------------+------------------------------------------
24 -- 115.0 30-JUN-05 nhunur Initial Version
25 -- 115.1 14-Nov-05 bmanyam 4714512 - Suspended Enrollments also need
26 -- to be counted as enrolled
27 -- 115.2 15-Nov-05 bmanyam Fixed GSCC Errors.
28 -- -------------------------------------------------------------------------------
29 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
30 FUNCTION get_plip_id (
31 p_pl_id IN NUMBER,
32 p_pgm_id IN NUMBER,
33 p_effective_date IN DATE
34 )
35 RETURN NUMBER
36 AS
37 --
38 CURSOR c_plip
39 IS
40 SELECT plip_id
41 FROM ben_plip_f
42 WHERE pl_id = p_pl_id
43 AND pgm_id = p_pgm_id
44 AND p_effective_date BETWEEN effective_start_date
45 AND effective_end_date;
46
47 --
48 l_plip c_plip%ROWTYPE;
49 --
50 BEGIN
51 --
52 OPEN c_plip;
53 FETCH c_plip INTO l_plip;
54 CLOSE c_plip;
55 RETURN NVL (l_plip.plip_id, -1);
56 --
57 END;
58
59 --
60 FUNCTION get_lf_evt_dt (p_per_in_ler_id IN NUMBER)
61 RETURN DATE
62 AS
63 --
64 CURSOR c_date
65 IS
66 SELECT lf_evt_ocrd_dt
67 FROM ben_per_in_ler
68 WHERE per_in_ler_id = p_per_in_ler_id;
69
70 --
71 l_date DATE;
72 --
73 BEGIN
74 --
75 OPEN c_date;
76 FETCH c_date INTO l_date;
77 CLOSE c_date;
78 RETURN l_date;
79 --
80 END;
81
82 --
83 PROCEDURE insert_event (
84 p_rec IN ben_pen_shd.g_rec_type,
85 p_effective_date IN DATE,
86 p_datetrack_mode IN VARCHAR2
87 )
88 IS
89 BEGIN
90 INSERT INTO hri_eq_ben_eligenrl_evts
91 (person_id,
92 per_in_ler_id,
93 prtt_enrt_rslt_id,
94 interim_enrt_rslt_id,
95 lf_evt_ocrd_dt,
96 business_group_id,
97 pgm_id,
98 pl_id,
99 ptip_id,
100 pl_typ_id,
101 plip_id,
102 oipl_id,
103 fnd_concurrent_request_id,
104 event_date,
105 event_cd,
106 enrt_ind,
107 dflt_ind,
108 waive_expl_ind,
109 last_update_date,
110 last_updated_by,
111 last_update_login,
112 created_by,
113 creation_date
114 )
115 VALUES (p_rec.person_id,
116 p_rec.per_in_ler_id,
117 p_rec.prtt_enrt_rslt_id,
118 p_rec.rplcs_sspndd_rslt_id,
119 get_lf_evt_dt (p_rec.per_in_ler_id),
120 p_rec.business_group_id,
121 p_rec.pgm_id,
122 p_rec.pl_id,
123 p_rec.ptip_id,
124 p_rec.pl_typ_id,
125 get_plip_id (p_rec.pl_id, p_rec.pgm_id, p_effective_date),
126 p_rec.oipl_id,
127 -1,
128 p_effective_date,
129 'ENRD',
130 1,
131 DECODE (fnd_global.conc_request_id, -1, 0, 1),
132 0,
133 SYSDATE,
134 fnd_global.user_id,
135 fnd_global.login_id,
136 fnd_global.user_id,
137 SYSDATE
138 );
139 END;
140
141 --
142 PROCEDURE update_event (
143 p_rec IN ben_pen_shd.g_rec_type,
144 p_effective_date IN DATE,
145 p_datetrack_mode IN VARCHAR2
146 )
147 IS
148 BEGIN
149 --
150 IF (p_rec.effective_end_date <> hr_api.g_eot
151 OR p_rec.enrt_cvg_thru_dt <> hr_api.g_eot) THEN
152 -- 4714512 : Suspended Enrollments also need to be counted as enrolled
153 --
154 INSERT INTO hri_eq_ben_eligenrl_evts
155 (person_id,
156 per_in_ler_id,
157 prtt_enrt_rslt_id,
158 interim_enrt_rslt_id,
159 lf_evt_ocrd_dt,
160 business_group_id,
161 pgm_id,
162 pl_id,
163 ptip_id,
164 pl_typ_id,
165 plip_id,
166 oipl_id,
167 fnd_concurrent_request_id,
168 event_date,
169 event_cd,
170 enrt_ind,
171 dflt_ind,
172 waive_expl_ind,
173 last_update_date,
174 last_updated_by,
175 last_update_login,
176 created_by,
177 creation_date
178 )
179 VALUES (p_rec.person_id,
180 p_rec.per_in_ler_id,
181 p_rec.prtt_enrt_rslt_id,
182 p_rec.rplcs_sspndd_rslt_id,
183 get_lf_evt_dt (p_rec.per_in_ler_id),
184 p_rec.business_group_id,
185 p_rec.pgm_id,
186 p_rec.pl_id,
187 p_rec.ptip_id,
188 p_rec.pl_typ_id,
189 get_plip_id (p_rec.pl_id, p_rec.pgm_id, p_effective_date),
190 p_rec.oipl_id,
191 -1,
192 p_effective_date,
193 'DE-ENRD',
194 0,
195 DECODE (fnd_global.conc_request_id, -1, 0, 0),
196 0,
197 SYSDATE,
198 fnd_global.user_id,
199 fnd_global.login_id,
200 fnd_global.user_id,
201 SYSDATE
202 );
203 END IF;
204 END;
205
206 --
207 PROCEDURE delete_event (
208 p_rec IN ben_pen_shd.g_rec_type,
209 p_effective_date IN DATE,
210 p_datetrack_mode IN VARCHAR2
211 )
212 IS
213 BEGIN
214 INSERT INTO hri_eq_ben_eligenrl_evts
215 (person_id,
216 per_in_ler_id,
217 prtt_enrt_rslt_id,
218 interim_enrt_rslt_id,
219 lf_evt_ocrd_dt,
220 business_group_id,
221 pgm_id,
222 pl_id,
223 ptip_id,
224 pl_typ_id,
225 plip_id,
226 oipl_id,
227 fnd_concurrent_request_id,
228 event_date,
229 event_cd,
230 enrt_ind,
231 dflt_ind,
232 waive_expl_ind,
233 last_update_date,
234 last_updated_by,
235 last_update_login,
236 created_by,
237 creation_date
238 )
239 VALUES (p_rec.person_id,
240 p_rec.per_in_ler_id,
241 p_rec.prtt_enrt_rslt_id,
242 p_rec.rplcs_sspndd_rslt_id,
243 get_lf_evt_dt (p_rec.per_in_ler_id),
244 p_rec.business_group_id,
245 p_rec.pgm_id,
246 p_rec.pl_id,
247 p_rec.ptip_id,
248 p_rec.pl_typ_id,
249 get_plip_id (p_rec.pl_id, p_rec.pgm_id, p_effective_date),
250 p_rec.oipl_id,
251 -1,
252 p_effective_date,
253 DECODE (p_datetrack_mode, 'ZAP', 'ZAP', 'DE-ENRD'),
254 0,
255 DECODE (fnd_global.conc_request_id, -1, 0, 0),
256 0,
257 SYSDATE,
258 fnd_global.user_id,
259 fnd_global.login_id,
260 fnd_global.user_id,
261 SYSDATE
262 );
263 END;
264 --
265 END hri_opl_ben_elig_enrl_eq;