[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_BEN_ELCTN_EVNTS_EQ
Source
1 PACKAGE BODY hri_opl_ben_elctn_evnts_eq AS
2 /* $Header: hrieqele.pkb 120.0 2005/09/21 01:26:58 anmajumd noship $ */
3 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 Name : HRI_OPL_BEN_ELCTN_EVNTS_EQ
5 -- Purpose : Populate Benefits Election events queue
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 -- 12.0 30-JUN-05 nhunur Initial Version
25 -- -------------------------------------------------------------------------------
26 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
27 --
28 PROCEDURE insert_event (
29 p_rec IN ben_pel_shd.g_rec_type,
30 p_pil_rec IN ben_pil_shd.g_rec_type,
31 p_called_from IN VARCHAR2,
32 p_effective_date IN DATE,
33 p_datetrack_mode IN VARCHAR2
34 )
35 IS
36 CURSOR c_date
37 IS
38 SELECT person_id, lf_evt_ocrd_dt
39 FROM ben_per_in_ler
40 WHERE per_in_ler_id = p_rec.per_in_ler_id;
41
42 --
43 l_date c_date%ROWTYPE;
44 --
45 BEGIN
46 --
47
48 IF p_called_from = 'PEL'
49 THEN
50 OPEN c_date;
51 FETCH c_date INTO l_date;
52
53 IF c_date%NOTFOUND
54 THEN
55 CLOSE c_date;
56 RETURN;
57 END IF;
58
59 CLOSE c_date;
60
61 INSERT INTO hri_eq_ben_elctn_evts
62 ( person_id,
63 per_in_ler_id,
64 pil_elctbl_chc_popl_id,
65 lf_evt_ocrd_dt,
66 business_group_id,
67 pgm_id,
68 pl_id,
69 fnd_concurrent_request_id,
70 event_date,
71 event_cd,
72 dflt_enrt_dt,
73 dflt_asnd_dt,
74 elcns_made_dt,
75 enrt_typ_cycl_cd,
76 enrt_perd_end_dt,
77 enrt_perd_strt_dt,
78 procg_end_dt,
79 pil_elctbl_popl_stat_cd,
80 auto_asnd_dt, enrt_perd_id,
81 assignment_id,
82 elig_ind,
83 enrt_ind,
84 dflt_ind,
85 last_update_date,
86 last_updated_by,
87 last_update_login,
88 created_by,
89 creation_date
90 )
91 VALUES ( l_date.person_id,
92 p_rec.per_in_ler_id,
93 p_rec.pil_elctbl_chc_popl_id,
94 l_date.lf_evt_ocrd_dt,
95 p_rec.business_group_id,
96 p_rec.pgm_id,
97 p_rec.pl_id,
98 -1,
99 p_effective_date,
100 DECODE (p_datetrack_mode, 'INSERT', 'INSERT', 'UPDATE'),
101 p_rec.dflt_enrt_dt,
102 p_rec.dflt_asnd_dt,
103 p_rec.elcns_made_dt,
104 p_rec.enrt_typ_cycl_cd,
105 p_rec.enrt_perd_end_dt,
106 p_rec.enrt_perd_strt_dt,
107 p_rec.procg_end_dt,
108 p_rec.pil_elctbl_popl_stat_cd,
109 p_rec.auto_asnd_dt,
110 p_rec.enrt_perd_id,
111 p_rec.assignment_id,
112 DECODE (p_rec.pil_elctbl_popl_stat_cd, NULL, 1, 0),
113 DECODE (p_rec.elcns_made_dt, NULL, 0, 1),
114 DECODE (p_rec.dflt_enrt_dt, NULL, 0, 1),
115 SYSDATE,
116 fnd_global.user_id,
117 fnd_global.login_id,
118 fnd_global.user_id,
119 SYSDATE
120 );
121 END IF;
122
123 IF p_called_from = 'PIL'
124 THEN
125 IF p_pil_rec.per_in_ler_stat_cd IN ('VOIDD')
126 THEN
127 FOR pel_rec IN (SELECT *
128 FROM ben_pil_elctbl_chc_popl pel
129 WHERE per_in_ler_id = p_pil_rec.per_in_ler_id
130 AND pel.pgm_id IS NOT NULL)
131 LOOP
132 INSERT INTO hri_eq_ben_elctn_evts
133 ( person_id,
134 per_in_ler_id,
135 pil_elctbl_chc_popl_id,
136 lf_evt_ocrd_dt,
137 business_group_id,
138 pgm_id,
139 pl_id,
140 fnd_concurrent_request_id,
141 event_date,
142 event_cd,
143 dflt_enrt_dt,
144 dflt_asnd_dt,
145 elcns_made_dt,
146 enrt_typ_cycl_cd,
147 enrt_perd_end_dt,
148 enrt_perd_strt_dt,
149 procg_end_dt,
150 pil_elctbl_popl_stat_cd,
151 auto_asnd_dt,
152 enrt_perd_id,
153 assignment_id,
154 elig_ind,
155 enrt_ind,
156 dflt_ind,
157 last_update_date,
158 last_updated_by,
159 last_update_login,
160 created_by,
161 creation_date
162 )
163 VALUES ( l_date.person_id,
164 pel_rec.per_in_ler_id,
165 pel_rec.pil_elctbl_chc_popl_id,
166 p_pil_rec.lf_evt_ocrd_dt,
167 pel_rec.business_group_id,
168 pel_rec.pgm_id,
169 pel_rec.pl_id,
170 -1,
171 p_effective_date,
172 'UPDATE',
173 pel_rec.dflt_enrt_dt,
174 pel_rec.dflt_asnd_dt,
175 pel_rec.elcns_made_dt,
176 pel_rec.enrt_typ_cycl_cd,
177 pel_rec.enrt_perd_end_dt,
178 pel_rec.enrt_perd_strt_dt,
179 pel_rec.procg_end_dt,
180 pel_rec.pil_elctbl_popl_stat_cd,
181 pel_rec.auto_asnd_dt,
182 pel_rec.enrt_perd_id,
183 pel_rec.assignment_id,
184 DECODE (pel_rec.pil_elctbl_popl_stat_cd,
185 NULL, 1,
186 0
187 ),
188 DECODE (pel_rec.elcns_made_dt, NULL, 0, 1),
189 DECODE (pel_rec.dflt_enrt_dt, NULL, 0, 1),
190 SYSDATE,
191 fnd_global.user_id,
192 fnd_global.login_id,
193 fnd_global.user_id,
194 SYSDATE
195 );
196 END LOOP;
197 END IF;
198
199 --
200 IF p_pil_rec.per_in_ler_stat_cd IN ('BCKDT')
201 THEN
202 --
203 INSERT INTO hri_eq_ben_enrlactn_evts
204 ( person_id,
205 per_in_ler_id,
206 lf_evt_ocrd_dt,
207 business_group_id,
208 fnd_concurrent_request_id,
209 event_date,
210 event_cd,
211 last_update_date,
212 last_updated_by,
213 last_update_login,
214 created_by,
215 creation_date
216 )
217 VALUES ( p_pil_rec.person_id,
218 p_pil_rec.per_in_ler_id,
219 p_pil_rec.lf_evt_ocrd_dt,
220 p_pil_rec.business_group_id, -1,
221 p_effective_date,
222 'ZAP',
223 SYSDATE,
224 fnd_global.user_id,
225 fnd_global.login_id,
226 fnd_global.user_id,
227 SYSDATE
228 );
229 END IF;
230 END IF;
231 END;
232
233 --
234 PROCEDURE update_event (
235 p_rec IN ben_pel_shd.g_rec_type,
236 p_pil_rec IN ben_pil_shd.g_rec_type,
237 p_called_from IN VARCHAR2,
238 p_effective_date IN DATE,
239 p_datetrack_mode IN VARCHAR2
240 )
241 IS
242 BEGIN
243 insert_event (p_rec => p_rec,
244 p_pil_rec => p_pil_rec,
245 p_called_from => p_called_from,
246 p_effective_date => p_effective_date,
247 p_datetrack_mode => p_datetrack_mode
248 );
249 END;
250
251 --
252 PROCEDURE delete_event (
253 p_rec IN ben_pel_shd.g_rec_type,
254 p_effective_date IN DATE,
255 p_datetrack_mode IN VARCHAR2
256 )
257 IS
258 BEGIN
259 insert_event (p_rec => p_rec,
260 p_pil_rec => NULL,
261 p_called_from => NULL,
262 p_effective_date => p_effective_date,
263 p_datetrack_mode => p_datetrack_mode
264 );
265 END;
266 --
267 END hri_opl_ben_elctn_evnts_eq;