[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_BEN_ENRLACTN_EQ
Source
1 PACKAGE BODY hri_opl_ben_enrlactn_eq AS
2 /* $Header: hrieqeea.pkb 120.0 2005/09/21 01:26:15 anmajumd noship $ */
3 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 Name : HRI_OPL_BEN_ENRLACTN_EQ
5 Purpose : Populate Benefits Enrollment Action event 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 abparekh Initial Version
25 -- -------------------------------------------------------------------------------
26 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
27 --
28 --
29 -- ----------------------------------------------------------------------------
30 -- |-----------------------------< GET_LF_EVT_DT >----------------------------|
31 -- ----------------------------------------------------------------------------
32 FUNCTION get_lf_evt_dt (p_per_in_ler_id IN NUMBER)
33 RETURN DATE
34 AS
35 --
36 CURSOR c_date
37 IS
38 SELECT lf_evt_ocrd_dt
39 FROM ben_per_in_ler
40 WHERE per_in_ler_id = p_per_in_ler_id;
41
42 --
43 l_date DATE;
44 --
45 BEGIN
46 --
47 OPEN c_date;
48 --
49 FETCH c_date INTO l_date;
50 --
51 CLOSE c_date;
52 --
53 RETURN l_date;
54 --
55 END;
56
57 --
58 -- ----------------------------------------------------------------------------
59 -- |------------------------------< INSERT_EVENT >----------------------------|
60 -- ----------------------------------------------------------------------------
61 PROCEDURE insert_event (
62 p_rec IN ben_pea_shd.g_rec_type,
63 p_effective_date IN DATE,
64 p_datetrack_mode IN VARCHAR2
65 )
66 IS
67 --
68 CURSOR c_act_typ
69 IS
70 SELECT type_cd
71 FROM ben_actn_typ
72 WHERE actn_typ_id = p_rec.actn_typ_id;
73
74 --
75 CURSOR c_pen
76 IS
77 SELECT pil.person_id, pen.pgm_id, pen.pl_id, pen.oipl_id,
78 pen.sspndd_flag, pil.lf_evt_ocrd_dt
79 FROM ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
80 WHERE pil.per_in_ler_id = p_rec.per_in_ler_id
81 AND pen.prtt_enrt_rslt_id = p_rec.prtt_enrt_rslt_id
82 AND p_effective_date BETWEEN pen.effective_start_date
83 AND pen.effective_end_date;
84
85 --
86 l_pen_row c_pen%ROWTYPE;
87 l_actn_typ_cd VARCHAR2 (30);
88 --
89 BEGIN
90 --
91 OPEN c_pen;
92 --
93 FETCH c_pen INTO l_pen_row;
94
95 --
96
97 IF c_pen%NOTFOUND
98 THEN
99 --
100 CLOSE c_pen;
101 RETURN;
102 --
103 ELSE
104 --
105 OPEN c_act_typ;
106 --
107 FETCH c_act_typ INTO l_actn_typ_cd;
108
109 --
110 IF c_act_typ%NOTFOUND
111 THEN
112 --
113 CLOSE c_act_typ;
114 RETURN;
115 --
116 END IF;
117 --
118 END IF;
119
120 --
121 CLOSE c_pen;
122 CLOSE c_act_typ;
123
124 --
125 INSERT INTO hri_eq_ben_enrlactn_evts
126 ( person_id,
127 per_in_ler_id,
128 prtt_enrt_rslt_id,
129 pgm_id,
130 pl_id,
131 oipl_id,
132 lf_evt_ocrd_dt,
133 sspndd_flag,
134 actn_typ_cd,
135 actn_typ_id,
136 prtt_enrt_actn_id,
137 elig_cvrd_dpnt_id,
138 pl_bnf_id,
139 due_dt,
140 cmpltd_dt,
141 rqd_flag,
142 business_group_id,
143 fnd_concurrent_request_id,
144 event_date,
145 event_cd,
146 last_update_date,
147 last_updated_by,
148 last_update_login,
149 created_by,
150 creation_date
151 )
152 VALUES ( l_pen_row.person_id,
153 p_rec.per_in_ler_id,
154 p_rec.prtt_enrt_rslt_id,
155 l_pen_row.pgm_id,
156 l_pen_row.pl_id,
157 l_pen_row.oipl_id,
158 l_pen_row.lf_evt_ocrd_dt,
159 l_pen_row.sspndd_flag,
160 l_actn_typ_cd,
161 p_rec.actn_typ_id,
162 p_rec.prtt_enrt_actn_id,
163 p_rec.elig_cvrd_dpnt_id,
164 p_rec.pl_bnf_id,
165 p_rec.due_dt,
166 p_rec.cmpltd_dt,
167 p_rec.rqd_flag,
168 p_rec.business_group_id,
169 1,
170 p_effective_date,
171 DECODE (p_datetrack_mode,
172 'INSERT', 'INSERTED',
173 'ZAP', 'ZAP',
174 'COMPLETED'
175 ),
176 SYSDATE,
177 fnd_global.user_id,
178 fnd_global.login_id,
179 fnd_global.user_id,
180 SYSDATE
181 );
182 END;
183
184 -- ----------------------------------------------------------------------------
185 -- |------------------------------< UPDATE_EVENT >----------------------------|
186 -- ----------------------------------------------------------------------------
187 PROCEDURE update_event (
188 p_rec IN ben_pea_shd.g_rec_type,
189 p_effective_date IN DATE,
190 p_datetrack_mode IN VARCHAR2
191 )
192 IS
193 BEGIN
194 insert_event (p_rec, p_effective_date, p_datetrack_mode);
195 END;
196
197 -- ----------------------------------------------------------------------------
198 -- |------------------------------< DELETE_EVENT >----------------------------|
199 -- ----------------------------------------------------------------------------
200 PROCEDURE delete_event (
201 p_rec IN ben_pea_shd.g_rec_type,
202 p_effective_date IN DATE,
203 p_datetrack_mode IN VARCHAR2
204 )
205 IS
206 BEGIN
207 insert_event (p_rec, p_effective_date, p_datetrack_mode);
208 END;
209 --
210 --
211 END hri_opl_ben_enrlactn_eq;