[Home] [Help]
PACKAGE BODY: APPS.BEN_MNG_PRMRY_CARE_PRVDR
Source
1 Package Body ben_mng_prmry_care_prvdr as
2 /* $Header: benmnppr.pkb 120.3 2006/10/30 13:04:57 rgajula ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_mng_prmry_care_prvdr.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< fetch_pcp_dsgn_cd >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure returns the primary care provider desgination code
16 -- at both PRTT and DPNT level based on p_level for a particular enrollment
17 -- as of effective date.
18 --
19 -- Pre-Conditions
20 -- None.
21 --
22 -- In Parameters
23 --p_prtt_enrt_rslt_id
24 --p_effective_date
25 --p_level
26 --p_pcp_dsgn_cd
27
28 -- Post Success
29 -- desginatio code is passed as the out parameter p_pcp_dsgn_cd
30 --
31 procedure fetch_pcp_dsgn_cd(p_prtt_enrt_rslt_id in number,
32 p_effective_date in date,
33 p_level in varchar2,
34 p_pcp_dsgn_cd out nocopy varchar2) is
35
36 l_proc varchar2(72) := g_package||'fetch_pcp_dsgn_cd';
37
38 cursor c_pcp is
39 select 1,decode(p_level,'PRTT',cop.pcp_dsgn_cd,'DPNT',cop.pcp_dpnt_dsgn_cd)
40 from ben_oipl_f cop
41 where oipl_id in (select pen.oipl_id
42 from ben_prtt_enrt_rslt_f pen
43 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
44 and prtt_enrt_rslt_stat_cd is null)
45 and p_effective_date between cop.effective_Start_date and cop.effective_end_date
46 UNION
47 select 2,decode(p_level,'PRTT',cop.pcp_dsgn_cd,'DPNT',cop.pcp_dpnt_dsgn_cd)
48 from ben_pl_pcp cop
49 where pl_id in ( select pen.pl_id
50 from ben_prtt_enrt_rslt_f pen
51 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
52 and prtt_enrt_rslt_stat_cd is null)
53 order by 1;
54
55 l_pcp_dsgn_cd varchar2(30) := null;
56 lvl_pcp_dsgn_cd number := 0;
57
58 begin
59
60 --
61 hr_utility.set_location('Entering:'||l_proc, 5);
62
63 hr_utility.set_location('p_prtt_enrt_rslt_id' || p_prtt_enrt_rslt_id, 198);
64 hr_utility.set_location('p_effective_date' || p_effective_date, 198);
65 hr_utility.set_location('p_level' || p_level, 198);
66
67 open c_pcp;
68 fetch c_pcp into lvl_pcp_dsgn_cd,l_pcp_dsgn_cd;
69
70 if c_pcp%notfound then
71 l_pcp_dsgn_cd := null;
72 end if;
73
74 p_pcp_dsgn_cd := nvl(l_pcp_dsgn_cd,'N');
75
76
77 hr_utility.set_location('lvl_pcp_dsgn_cd' || lvl_pcp_dsgn_cd, 198);
78 hr_utility.set_location('p_pcp_dsgn_cd' || p_pcp_dsgn_cd, 198);
79
80 hr_utility.set_location('Leaving:'||l_proc, 5);
81
82 end fetch_pcp_dsgn_cd;
83
84
85 -- ----------------------------------------------------------------------------
86 -- |------< recycle_ppr >------|
87 -- ----------------------------------------------------------------------------
88 --
89 -- Description
90 -- This procedure inherits primary care providers for the enrollment result
91 -- from the set of primary care providers previously selected for this plan
92 -- (in another result id) according to the set of rules.
93 --
94 -- Pre-Conditions
95 -- None.
96 --
97 -- In Parameters
98 -- p_new_prtt_enrt_rslt_id
99 -- p_old_prtt_enrt_rslt_id
100 -- p_business_group_id
101 -- p_effective_date session date
102 -- p_datetrack_mode
103 -- p_validate
104 --
105 -- Post Success
106 -- Processing continues
107 --
108 -- Post Failure
109 -- No database changes
110 --
111 -- Access Status
112 -- Internal table handler use only.
113 --
114 -- ----------------------------------------------------------------------------------------------
115 Procedure recycle_ppr(p_validate in boolean default false,
116 p_new_prtt_enrt_rslt_id in number,
117 p_old_prtt_enrt_rslt_id in number,
118 p_business_group_id in number,
119 p_effective_date in date,
120 p_datetrack_mode in varchar2
121 ) is
122
123 l_proc varchar2(72) := g_package||'recycle_ppr';
124
125 l_effective_start_date date;
126 l_effective_end_date date;
127 l_prmry_care_prvdr_id number;
128 l_object_version_number number;
129
130 -- check that results have same plan id
131 cursor chk_rslt_c is
132 select r2.effective_start_date, r2.effective_end_date
133 from ben_prtt_enrt_rslt_f r,
134 ben_prtt_enrt_rslt_f r2
135 where r.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
136 and r2.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
137 and r.pl_id = r2.pl_id
138 and r.business_group_id = p_business_group_id
139 and p_effective_date between r.effective_start_date
140 and r.effective_end_date
141 and r2.business_group_id = p_business_group_id
142 and p_effective_date between r2.effective_start_date
143 and r2.effective_end_date;
144 l_pen_effective_start_date date;
145 l_pen_effective_end_date date;
146
147 -- old primary care provders hooked to old result
148 cursor old_ppr_c is
149 select NAME,
150 EXT_IDENT,
151 PRMRY_CARE_PRVDR_TYP_CD,
152 PRTT_ENRT_RSLT_ID,
153 ELIG_CVRD_DPNT_ID,
154 BUSINESS_GROUP_ID,
155 PPR_ATTRIBUTE_CATEGORY,
156 PPR_ATTRIBUTE1,
157 PPR_ATTRIBUTE2,
158 PPR_ATTRIBUTE3,
159 PPR_ATTRIBUTE4,
160 PPR_ATTRIBUTE5,
161 PPR_ATTRIBUTE6,
162 PPR_ATTRIBUTE7,
163 PPR_ATTRIBUTE8,
164 PPR_ATTRIBUTE9,
165 PPR_ATTRIBUTE10,
166 PPR_ATTRIBUTE11,
167 PPR_ATTRIBUTE12,
168 PPR_ATTRIBUTE13,
169 PPR_ATTRIBUTE14,
170 PPR_ATTRIBUTE15,
171 PPR_ATTRIBUTE16,
172 PPR_ATTRIBUTE17,
173 PPR_ATTRIBUTE18,
174 PPR_ATTRIBUTE19,
175 PPR_ATTRIBUTE20,
176 PPR_ATTRIBUTE21,
177 PPR_ATTRIBUTE22,
178 PPR_ATTRIBUTE23,
179 PPR_ATTRIBUTE24,
180 PPR_ATTRIBUTE25,
181 PPR_ATTRIBUTE26,
182 PPR_ATTRIBUTE27,
183 PPR_ATTRIBUTE28,
184 PPR_ATTRIBUTE29,
185 PPR_ATTRIBUTE30
186 from ben_prmry_care_prvdr_f pcp
187 where pcp.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
188 and pcp.elig_cvrd_dpnt_id is null
189 and pcp.business_group_id = p_business_group_id
190 and p_effective_date between pcp.effective_start_date
191 and pcp.effective_end_date
192 ;
193
194 -- old primary care provders hooked to dependents for old result
195 cursor old_dpnt_ppr_c is
196 select pcp.NAME,
197 pcp.EXT_IDENT,
198 pcp.PRMRY_CARE_PRVDR_TYP_CD,
199 pcp.PRTT_ENRT_RSLT_ID,
200 pcp.ELIG_CVRD_DPNT_ID old_elig_cvrd_dpnt_id,
201 pcp.BUSINESS_GROUP_ID,
202 pcp.PPR_ATTRIBUTE_CATEGORY,
203 pcp.PPR_ATTRIBUTE1,
204 pcp.PPR_ATTRIBUTE2,
205 pcp.PPR_ATTRIBUTE3,
206 pcp.PPR_ATTRIBUTE4,
207 pcp.PPR_ATTRIBUTE5,
208 pcp.PPR_ATTRIBUTE6,
209 pcp.PPR_ATTRIBUTE7,
210 pcp.PPR_ATTRIBUTE8,
211 pcp.PPR_ATTRIBUTE9,
212 pcp.PPR_ATTRIBUTE10,
213 pcp.PPR_ATTRIBUTE11,
214 pcp.PPR_ATTRIBUTE12,
215 pcp.PPR_ATTRIBUTE13,
216 pcp.PPR_ATTRIBUTE14,
217 pcp.PPR_ATTRIBUTE15,
218 pcp.PPR_ATTRIBUTE16,
219 pcp.PPR_ATTRIBUTE17,
220 pcp.PPR_ATTRIBUTE18,
221 pcp.PPR_ATTRIBUTE19,
222 pcp.PPR_ATTRIBUTE20,
223 pcp.PPR_ATTRIBUTE21,
224 pcp.PPR_ATTRIBUTE22,
225 pcp.PPR_ATTRIBUTE23,
226 pcp.PPR_ATTRIBUTE24,
227 pcp.PPR_ATTRIBUTE25,
228 pcp.PPR_ATTRIBUTE26,
229 pcp.PPR_ATTRIBUTE27,
230 pcp.PPR_ATTRIBUTE28,
231 pcp.PPR_ATTRIBUTE29,
232 pcp.PPR_ATTRIBUTE30,
233 d2.elig_cvrd_dpnt_id elig_cvrd_dpnt_id,
234 d2.effective_start_date,
235 d2.effective_end_date
236 from ben_prtt_enrt_rslt_f r,
237 ben_prtt_enrt_rslt_f r2,
238 ben_elig_cvrd_dpnt_f d,
239 ben_elig_cvrd_dpnt_f d2,
240 ben_prmry_care_prvdr_f pcp,
241 ben_per_in_ler pil,
242 ben_per_in_ler pil2
243 where r.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
244 and r2.prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
245 and d.prtt_enrt_rslt_id = r.prtt_enrt_rslt_id
246 and d2.prtt_enrt_rslt_id = r2.prtt_enrt_rslt_id
247 and d.dpnt_person_id = d2.dpnt_person_id
248 and d.elig_cvrd_dpnt_id = pcp.elig_cvrd_dpnt_id
249 and pcp.prtt_enrt_rslt_id is null
250 and r.business_group_id = p_business_group_id
251 and pcp.business_group_id = p_business_group_id
252 and p_effective_date between r.effective_start_date
253 and r.effective_end_date
254 and p_effective_date between r2.effective_start_date
255 and r2.effective_end_date
256 and p_effective_date between d.effective_start_date
257 and d.effective_end_date
258 and p_effective_date between d2.effective_start_date
259 and d2.effective_end_date
260 and p_effective_date between pcp.effective_start_date
261 and pcp.effective_end_date
262 and pil.per_in_ler_id=d.per_in_ler_id
263 and pil.business_group_id=p_business_group_id
264 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
265 and pil2.per_in_ler_id=d2.per_in_ler_id
266 and pil2.business_group_id=p_business_group_id
267 and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
268
269 l_new_pcp_prtt_dsgn_cd varchar2(30) := null;
270
271 l_new_pcp_dpnt_dsgn_cd varchar2(30) := null;
272
273
274 --
275 begin
276 --
277 hr_utility.set_location('Entering:'||l_proc, 5);
278
279 -- Check that the new result has the same plan id as the old result
280 open chk_rslt_c;
281 fetch chk_rslt_c into
282 l_pen_effective_start_date, l_pen_effective_end_date;
283 if chk_rslt_c%NOTFOUND or chk_rslt_c%NOTFOUND is null then
284 fnd_message.set_name('BEN', 'BEN_91657_PL_NOT_SAME');
285 fnd_message.raise_error;
286 end if;
287
288 hr_utility.set_location('before old_ppr loop'||l_proc, 10);
289
290 --
291 --Bug 5610430
292
293 fetch_pcp_dsgn_cd(p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id,
294 p_effective_date => p_effective_date,
295 p_level => 'PRTT',
296 p_pcp_dsgn_cd => l_new_pcp_prtt_dsgn_cd);
297
298
299 if l_new_pcp_prtt_dsgn_cd in ('O','R') then
300
301 --End Bug 5610430
302
303 FOR old_ppr in old_ppr_c LOOP
304
305 ben_prmry_care_prvdr_api.create_prmry_care_prvdr(
306 p_validate => p_validate
307 ,p_prmry_care_prvdr_id => l_prmry_care_prvdr_id
308 ,p_effective_start_date => l_effective_start_date
309 ,p_effective_end_date => l_effective_end_date
310 ,p_name => old_ppr.name
311 ,p_ext_ident => old_ppr.ext_ident
312 ,p_prmry_care_prvdr_typ_cd => old_ppr.prmry_care_prvdr_typ_cd
313 ,p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id
314 ,p_elig_cvrd_dpnt_id => null
315 ,p_business_group_id => p_business_group_id
316 ,p_ppr_attribute_category => old_ppr.ppr_attribute_category
317 ,p_ppr_attribute1 => old_ppr.ppr_attribute1
318 ,p_ppr_attribute2 => old_ppr.ppr_attribute2
319 ,p_ppr_attribute3 => old_ppr.ppr_attribute3
320 ,p_ppr_attribute4 => old_ppr.ppr_attribute4
321 ,p_ppr_attribute5 => old_ppr.ppr_attribute5
322 ,p_ppr_attribute6 => old_ppr.ppr_attribute6
323 ,p_ppr_attribute7 => old_ppr.ppr_attribute7
324 ,p_ppr_attribute8 => old_ppr.ppr_attribute8
325 ,p_ppr_attribute9 => old_ppr.ppr_attribute9
326 ,p_ppr_attribute10 => old_ppr.ppr_attribute10
327 ,p_ppr_attribute11 => old_ppr.ppr_attribute11
328 ,p_ppr_attribute12 => old_ppr.ppr_attribute12
329 ,p_ppr_attribute13 => old_ppr.ppr_attribute13
330 ,p_ppr_attribute14 => old_ppr.ppr_attribute14
331 ,p_ppr_attribute15 => old_ppr.ppr_attribute15
332 ,p_ppr_attribute16 => old_ppr.ppr_attribute16
333 ,p_ppr_attribute17 => old_ppr.ppr_attribute17
334 ,p_ppr_attribute18 => old_ppr.ppr_attribute18
335 ,p_ppr_attribute19 => old_ppr.ppr_attribute19
336 ,p_ppr_attribute20 => old_ppr.ppr_attribute20
337 ,p_ppr_attribute21 => old_ppr.ppr_attribute21
338 ,p_ppr_attribute22 => old_ppr.ppr_attribute22
339 ,p_ppr_attribute23 => old_ppr.ppr_attribute23
340 ,p_ppr_attribute24 => old_ppr.ppr_attribute24
341 ,p_ppr_attribute25 => old_ppr.ppr_attribute25
342 ,p_ppr_attribute26 => old_ppr.ppr_attribute26
343 ,p_ppr_attribute27 => old_ppr.ppr_attribute27
344 ,p_ppr_attribute28 => old_ppr.ppr_attribute28
345 ,p_ppr_attribute29 => old_ppr.ppr_attribute29
346 ,p_ppr_attribute30 => old_ppr.ppr_attribute30
347 ,p_request_id => fnd_global.conc_request_id
348 ,p_program_application_id => fnd_global.prog_appl_id
349 ,p_program_id => fnd_global.conc_program_id
350 ,p_program_update_date => sysdate
351 ,p_object_version_number => l_object_version_number
352 ,p_effective_date => p_effective_date
353 );
354
355 END LOOP;
356 end if;
357
358 hr_utility.set_location('after old_ppr loop'||l_proc, 20);
359
360 --Bug 5610430
361 fetch_pcp_dsgn_cd(p_prtt_enrt_rslt_id => p_new_prtt_enrt_rslt_id,
362 p_effective_date => p_effective_date,
363 p_level => 'DPNT',
364 p_pcp_dsgn_cd => l_new_pcp_dpnt_dsgn_cd);
365
366
367 if l_new_pcp_dpnt_dsgn_cd in ('O','R') then
368
369 --End Bug 5610430
370
371 FOR old_dpnt_ppr in old_dpnt_ppr_c LOOP
372
373 ben_prmry_care_prvdr_api.create_prmry_care_prvdr(
374 p_validate => p_validate
375 ,p_prmry_care_prvdr_id => l_prmry_care_prvdr_id
376 ,p_effective_start_date => l_effective_start_date
377 ,p_effective_end_date => l_effective_end_date
378 ,p_name => old_dpnt_ppr.name
379 ,p_ext_ident => old_dpnt_ppr.ext_ident
380 ,p_prmry_care_prvdr_typ_cd => old_dpnt_ppr.prmry_care_prvdr_typ_cd
381 ,p_prtt_enrt_rslt_id => null
382 ,p_elig_cvrd_dpnt_id => old_dpnt_ppr.elig_cvrd_dpnt_id
383 ,p_business_group_id => p_business_group_id
384 ,p_ppr_attribute_category => old_dpnt_ppr.ppr_attribute_category
385 ,p_ppr_attribute1 => old_dpnt_ppr.ppr_attribute1
386 ,p_ppr_attribute2 => old_dpnt_ppr.ppr_attribute2
387 ,p_ppr_attribute3 => old_dpnt_ppr.ppr_attribute3
388 ,p_ppr_attribute4 => old_dpnt_ppr.ppr_attribute4
389 ,p_ppr_attribute5 => old_dpnt_ppr.ppr_attribute5
390 ,p_ppr_attribute6 => old_dpnt_ppr.ppr_attribute6
391 ,p_ppr_attribute7 => old_dpnt_ppr.ppr_attribute7
392 ,p_ppr_attribute8 => old_dpnt_ppr.ppr_attribute8
393 ,p_ppr_attribute9 => old_dpnt_ppr.ppr_attribute9
394 ,p_ppr_attribute10 => old_dpnt_ppr.ppr_attribute10
395 ,p_ppr_attribute11 => old_dpnt_ppr.ppr_attribute11
396 ,p_ppr_attribute12 => old_dpnt_ppr.ppr_attribute12
397 ,p_ppr_attribute13 => old_dpnt_ppr.ppr_attribute13
398 ,p_ppr_attribute14 => old_dpnt_ppr.ppr_attribute14
399 ,p_ppr_attribute15 => old_dpnt_ppr.ppr_attribute15
400 ,p_ppr_attribute16 => old_dpnt_ppr.ppr_attribute16
401 ,p_ppr_attribute17 => old_dpnt_ppr.ppr_attribute17
402 ,p_ppr_attribute18 => old_dpnt_ppr.ppr_attribute18
403 ,p_ppr_attribute19 => old_dpnt_ppr.ppr_attribute19
404 ,p_ppr_attribute20 => old_dpnt_ppr.ppr_attribute20
405 ,p_ppr_attribute21 => old_dpnt_ppr.ppr_attribute21
406 ,p_ppr_attribute22 => old_dpnt_ppr.ppr_attribute22
407 ,p_ppr_attribute23 => old_dpnt_ppr.ppr_attribute23
408 ,p_ppr_attribute24 => old_dpnt_ppr.ppr_attribute24
409 ,p_ppr_attribute25 => old_dpnt_ppr.ppr_attribute25
410 ,p_ppr_attribute26 => old_dpnt_ppr.ppr_attribute26
411 ,p_ppr_attribute27 => old_dpnt_ppr.ppr_attribute27
412 ,p_ppr_attribute28 => old_dpnt_ppr.ppr_attribute28
413 ,p_ppr_attribute29 => old_dpnt_ppr.ppr_attribute29
414 ,p_ppr_attribute30 => old_dpnt_ppr.ppr_attribute30
415 ,p_request_id => fnd_global.conc_request_id
416 ,p_program_application_id => fnd_global.prog_appl_id
417 ,p_program_id => fnd_global.conc_program_id
418 ,p_program_update_date => sysdate
419 ,p_object_version_number => l_object_version_number
420 ,p_effective_date => p_effective_date
421 );
422 END LOOP;
423
424 end if;
425
426
427 --
428 hr_utility.set_location('Exiting'||l_proc, 70);
429
430 End recycle_ppr;
431
432
433 end ben_mng_prmry_care_prvdr;