[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_ANSI
Source
1 Package Body ben_ext_ansi as
2 /* $Header: benxansi.pkb 120.2 2006/07/18 18:43:36 tjesumic ship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 --
8 g_package varchar2(33) := ' ben_ext_ansi.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |--------------------< main >----------------------------------------------|
13 -- ----------------------------------------------------------------------------
14
15 --
16 Procedure main(
17 p_person_id in number,
18 p_ext_rslt_id in number,
19 p_ext_file_id in number,
20 p_data_typ_cd in varchar2,
21 p_ext_typ_cd in varchar2,
22 p_ext_crit_prfl_id in number,
23 p_business_group_id in number,
24 p_effective_date in date
25 ) IS
26 --
27 l_proc varchar2(72) := g_package||'main';
28 --
29 l_ansi_subscriber_flag varchar2(1);
30 l_include varchar2(1);
31 --
32 cursor c_enrt_rslt is
33 select rslt.pl_id,
34 rslt.enrt_cvg_strt_dt,
35 rslt.enrt_cvg_thru_dt,
36 rslt.sspndd_flag,
37 rslt.prtt_enrt_rslt_stat_cd,
38 rslt.enrt_mthd_cd,
39 rslt.pgm_id,
40 rslt.pl_typ_id,
41 rslt.per_in_ler_id,
42 rslt.prtt_enrt_rslt_id,
43 rslt.last_update_date,
44 pil.ler_id,
45 pil.per_in_ler_stat_cd,
46 pil.lf_evt_ocrd_dt,
47 pil.ntfn_dt
48 from ben_prtt_enrt_rslt_f rslt,
49 ben_per_in_ler pil
50 where rslt.person_id = p_person_id
51 AND pil.per_in_ler_id(+) = rslt.per_in_ler_id
52 and p_effective_date between rslt.effective_start_date
53 and rslt.effective_end_date;
54 --
55 cursor c_dpnt_of_distinct_participant is
56 select distinct rslt.person_id
57 FROM ben_prtt_enrt_rslt_f rslt,
58 ben_elig_cvrd_dpnt_f dpnt
59 WHERE rslt.prtt_enrt_rslt_id = dpnt.prtt_enrt_rslt_id
60 AND dpnt.dpnt_person_id = p_person_id
61 --AND p_effective_date between rslt.enrt_cvg_strt_dt
62 -- and rslt.enrt_cvg_thru_dt
63 -- when the person and dpnt coverd fonm, person extracted not the dpnt
64 AND p_effective_date between rslt.effective_start_date
65 and rslt.effective_end_date
66 AND p_effective_date between dpnt.effective_start_date
67 and dpnt.effective_end_date;
68
69 cursor c_cvrd_dpnt(p_prtt_person_id in number, p_dpnt_person_id in number) is
70 SELECT rslt.pl_id,
71 rslt.sspndd_flag,
72 dpnt.cvg_strt_dt,
73 dpnt.cvg_thru_dt,
74 rslt.prtt_enrt_rslt_stat_cd,
75 rslt.enrt_mthd_cd,
76 rslt.pgm_id,
77 rslt.pl_typ_id,
78 rslt.per_in_ler_id,
79 rslt.prtt_enrt_rslt_id,
80 rslt.last_update_date,
81 pil.ler_id,
82 pil.per_in_ler_stat_cd,
83 pil.lf_evt_ocrd_dt,
84 pil.ntfn_dt
85 FROM ben_prtt_enrt_rslt_f rslt,
86 ben_elig_cvrd_dpnt_f dpnt,
87 ben_per_in_ler pil
88 WHERE rslt.prtt_enrt_rslt_id = dpnt.prtt_enrt_rslt_id
89 and rslt.per_in_ler_id = pil.per_in_ler_id(+)
90 AND dpnt.dpnt_person_id = p_dpnt_person_id
91 and rslt.person_id = p_prtt_person_id
92 --AND p_effective_date between rslt.enrt_cvg_strt_dt
93 -- and rslt.enrt_cvg_thru_dt
94 AND p_effective_date between rslt.effective_start_date
95 and rslt.effective_end_date
96 AND p_effective_date between dpnt.effective_start_date
97 and dpnt.effective_end_date;
98
99 --
100 cursor c_get_contact_info (p_person_id number,p_dpnt_person_id number,p_effective_date date) is
101 select c.contact_type,
102 p.national_identifier,
103 p.first_name,
104 p.last_name,
105 c.SEQUENCE_NUMBER
106 from
107 per_contact_relationships c,
108 per_all_people_f p
109 where
110 c.contact_person_id = p_dpnt_person_id
111 and c.person_id = p_person_id
112 and c.person_id = p.person_id
113 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
114 and nvl(p.effective_end_date, p_effective_date)
115 and p_effective_date between nvl(c.date_Start,p_effective_date)
116 and nvl(c.date_end ,p_effective_date)
117 order by c.sequence_number, decode(c.contact_type,'EMRG',2,1) ;
118 l_rollback boolean ;
119 l_dpnt_cvg_thru_dt date ;
120
121
122 cursor c_get_contact_info2 (p_person_id number,p_dpnt_person_id number,p_effective_date date) is
123 select c.contact_type,
124 p.national_identifier,
125 p.first_name,
126 p.last_name,
127 c.SEQUENCE_NUMBER
128 from
129 per_contact_relationships c,
130 per_all_people_f p
131 where
132 c.contact_person_id = p_dpnt_person_id
133 and c.person_id = p_person_id
134 and c.person_id = p.person_id
135 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
136 and nvl(p.effective_end_date, p_effective_date)
137 and p_effective_date >= nvl(c.date_Start,p_effective_date)
138 order by c.sequence_number, decode(c.contact_type,'EMRG',2,1),c.date_end desc ;
139
140
141 --
142 Begin
143 --
144 hr_utility.set_location('Entering'||l_proc, 5);
145 --
146 -- new way of determining if this person is a participant (aka subscriber);
147 l_include := 'N';
148 for enrt in c_enrt_rslt loop
149 ben_ext_evaluate_inclusion.evaluate_benefit_incl
150 (p_pl_id => enrt.pl_id,
151 p_sspndd_flag => enrt.sspndd_flag,
152 p_enrt_cvg_strt_dt => enrt.enrt_cvg_strt_dt,
153 p_enrt_cvg_thru_dt => enrt.enrt_cvg_thru_dt,
154 p_prtt_enrt_rslt_stat_cd => enrt.prtt_enrt_rslt_stat_cd,
155 p_enrt_mthd_cd => enrt.enrt_mthd_cd,
156 p_pgm_id => enrt.pgm_id,
157 p_pl_typ_id => enrt.pl_typ_id,
158 p_last_update_date => enrt.last_update_date,
159 p_ler_id => enrt.ler_id,
160 p_ntfn_dt => enrt.ntfn_dt,
161 p_lf_evt_ocrd_dt => enrt.lf_evt_ocrd_dt,
162 p_per_in_ler_stat_cd => enrt.per_in_ler_stat_cd,
163 p_per_in_ler_id => enrt.per_in_ler_id,
164 p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
165 p_effective_date => p_effective_date,
166 p_include => l_include);
167 --
168 if l_include = 'Y' then -- l_include means they met inclusion criteria.
169 exit;
170 end if;
171 --
172 end loop;
173 --
174 if l_include = 'Y' then
175 ben_ext_person.g_part_type := 'P'; -- Participant (aka Subscriber)
176 ben_ext_person.g_per_rlshp_type := '18'; -- 18 is 'Self'
177 ben_ext_person.g_part_ssn :=
178 ben_ext_person.g_national_identifier; --this is the reference ssn
179 ben_ext_person.g_part_first_name :=
180 ben_ext_person.g_first_name; --this is the reference first name
181 ben_ext_person.g_part_last_name :=
182 ben_ext_person.g_last_name; --this is the reference last name
183 --
184 if ben_extract.g_per_lvl = 'Y' THEN
185 --
186 -- Process Person Level Records
187 --
188 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
189 p_ext_file_id => p_ext_file_id,
190 p_data_typ_cd => p_data_typ_cd,
191 p_ext_typ_cd => p_ext_typ_cd,
192 p_rcd_typ_cd => 'D',
193 p_low_lvl_cd => 'P',
194 p_person_id => p_person_id,
195 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
196 p_business_group_id => p_business_group_id,
197 p_effective_date => p_effective_date
198 );
199 --
200 end if;
201 --
202 -- Process Enrollment Level Records
203 --
204 IF ben_ext_person.g_part_type = 'P' AND ben_extract.g_enrt_lvl = 'Y' then
205 --
206 -- create enrollment extract rows
207 -- =========================================
208 ben_ext_enrt.main(
209 p_person_id => p_person_id,
210 p_ext_rslt_id => p_ext_rslt_id,
211 p_ext_file_id => p_ext_file_id,
212 p_data_typ_cd => p_data_typ_cd,
213 p_ext_typ_cd => p_ext_typ_cd,
214 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
215 p_business_group_id => p_business_group_id,
216 p_effective_date => p_effective_date);
217 --
218 end if;
219 --
220 end if; -- l_include = 'Y'
221 --
222 -- create eligibility extract rows
223 -- =========================================
224 if ben_extract.g_elig_lvl = 'Y' then
225 --
226 ben_ext_elig.main(
227 p_person_id => p_person_id,
228 -- p_per_in_ler_id => ben_ext_person.g_per_in_ler_id,
229 p_ext_rslt_id => p_ext_rslt_id,
230 p_ext_file_id => p_ext_file_id,
231 p_data_typ_cd => p_data_typ_cd,
232 p_ext_typ_cd => p_ext_typ_cd,
233 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
234 p_business_group_id => p_business_group_id,
235 p_effective_date => p_effective_date
236 );
237 --
238 --
239 end if;
240 --
241 --
242 -- remember a person can be both a subsciber of one plan and a dependent of another!
243 --
244 hr_utility.set_location (' try as dpnt ' , 99 ) ;
245 ben_ext_person.g_part_type := 'D'; -- Dependent
246 -- remember a person can be a dpnt for two diff subscribers.
247 for i in c_dpnt_of_distinct_participant loop
248 --
249 hr_utility.set_location (' dpnt loop ' , 99 ) ;
250 -- now loop through each of their enrollments and see if they are included.
251 --
252 l_include := 'N';
253 for j in c_cvrd_dpnt(i.person_id,p_person_id) loop
254 --
255 ben_ext_evaluate_inclusion.evaluate_benefit_incl
256 (p_pl_id => j.pl_id,
257 p_sspndd_flag => j.sspndd_flag,
258 p_enrt_cvg_strt_dt => j.cvg_strt_dt,
259 p_enrt_cvg_thru_dt => j.cvg_thru_dt,
260 p_prtt_enrt_rslt_stat_cd => j.prtt_enrt_rslt_stat_cd,
261 p_enrt_mthd_cd => j.enrt_mthd_cd,
262 p_pgm_id => j.pgm_id,
263 p_pl_typ_id => j.pl_typ_id,
264 p_last_update_date => j.last_update_date,
265 p_ler_id => j.ler_id,
266 p_ntfn_dt => j.ntfn_dt,
267 p_lf_evt_ocrd_dt => j.lf_evt_ocrd_dt,
268 p_per_in_ler_stat_cd => j.per_in_ler_stat_cd,
269 p_per_in_ler_id => j.per_in_ler_id,
270 p_prtt_enrt_rslt_id => j.prtt_enrt_rslt_id,
271 p_effective_date => p_effective_date,
272 p_include => l_include);
273 --
274 l_dpnt_cvg_thru_dt := j.cvg_thru_dt ;
275
276 hr_utility.set_location (' dpnt cvg thryu dat ' || l_dpnt_cvg_thru_dt , 99 ) ;
277 hr_utility.set_location (' dpnt include ' || l_include , 99 ) ;
278 if l_include = 'Y' then -- l_include means they met inclusion criteria.
279 exit; -- out of j loop.
280 end if;
281 --
282 end loop; -- j
283 --
284 -- this will retreive the relationship between the two people.
285 --
286 if l_include = 'Y' then
287 --
288 open c_get_contact_info(i.person_id, p_person_id, p_effective_date);
289 fetch c_get_contact_info into ben_ext_person.g_per_rlshp_type,
290 ben_ext_person.g_part_ssn,
291 ben_ext_person.g_part_first_name,
292 ben_ext_person.g_part_last_name,
293 ben_ext_person.g_dpnt_contact_seq_num;
294
295 --- 5264053 if the contact relationship end dated then
296 --- get the contact latest information
297 if c_get_contact_info%notfound then
298 --if l_dpnt_cvg_thru_dt is not null and l_dpnt_cvg_thru_dt <> hr_api.g_eot then
299 open c_get_contact_info2(i.person_id, p_person_id,p_effective_date);
300 fetch c_get_contact_info2 into ben_ext_person.g_per_rlshp_type,
301 ben_ext_person.g_part_ssn,
302 ben_ext_person.g_part_first_name,
303 ben_ext_person.g_part_last_name,
304 ben_ext_person.g_dpnt_contact_seq_num;
305 close c_get_contact_info2;
306 --end if ;
307 end if ;
308
309
310 close c_get_contact_info;
311 --
312 if ben_extract.g_per_lvl = 'Y' THEN
313 --
314 -- Process Person Level Records
315 --
316 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
317 p_ext_file_id => p_ext_file_id,
318 p_data_typ_cd => p_data_typ_cd,
319 p_ext_typ_cd => p_ext_typ_cd,
320 p_rcd_typ_cd => 'D',
321 p_low_lvl_cd => 'P',
322 p_person_id => p_person_id,
323 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
324 p_business_group_id => p_business_group_id,
325 p_effective_date => p_effective_date
326 );
327 --
328 end if;
329 --
330 -- Process Enrollment Level Records
331 --
332 if ben_ext_person.g_part_type = 'D' and ben_extract.g_enrt_lvl = 'Y' then
333 --
334 ben_ext_enrt_spcl.main(
335 p_dpnt_person_id => p_person_id,
336 p_prtt_person_id => i.person_id,
337 p_ext_rslt_id => p_ext_rslt_id,
338 p_ext_file_id => p_ext_file_id,
339 p_data_typ_cd => p_data_typ_cd,
340 p_ext_typ_cd => p_ext_typ_cd,
341 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
342 p_business_group_id => p_business_group_id,
343 p_effective_date => p_effective_date
344 );
345 --
346 end if; -- part type
347 --
348 end if; -- l_include = 'Y'
349 --
350 end loop; -- i
351
352
353 -- validate the mandatory for low level in sequenc
354 FOR i in ben_extract.gtt_rcd_rqd_vals_seq.first .. ben_extract.gtt_rcd_rqd_vals_seq.last LOOP
355 --
356 If NOT ben_extract.gtt_rcd_rqd_vals_seq(i).rcd_found THEN
357 hr_utility.set_location('Mandatory failed '||ben_extract.gtt_rcd_rqd_vals_seq(i).low_lvl_cd || ' '||
358 ben_extract.gtt_rcd_rqd_vals_seq(i).seq_num , 15);
359 l_rollback := TRUE; -- raise required_error;
360 end if ;
361
362 if ben_extract.gtt_rcd_rqd_vals_seq(1).low_lvl_cd <> 'NOREQDRCD' then
363 ben_extract.gtt_rcd_rqd_vals_seq(i).rcd_found := false ;
364 end if ;
365 END LOOP;
366 --
367
368 if l_rollback then
369 hr_utility.set_location(' record not found ' || l_proc, 15) ;
370 raise ben_ext_person.required_error ;
371 end if ;
372
373 --
374 hr_utility.set_location('Exiting'||l_proc, 15);
375 --
376 End main;
377 --
378 --
379 END ben_ext_ansi;