[Home] [Help]
PACKAGE BODY: APPS.BEN_PRTT_REIMBMT_RQST_API
Source
1 Package Body ben_PRTT_REIMBMT_RQST_api as
2 /* $Header: beprcapi.pkb 120.4 2008/01/14 12:17:29 sallumwa noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'ben_PRTT_REIMBMT_RQST_api.';
7 --
8 procedure generate_communications
9 ( p_submitter_person_id in number,
10 p_pl_id in number,
11 p_prtt_reimbmt_rqst_stat_cd in varchar2,
12 p_business_group_id in number,
13 p_effective_date in date) is
14 --
15 cursor c_pen is
16 select pen.pgm_id,
17 pen.pl_typ_id,
18 pen.ler_id,
19 pen.per_in_ler_id
20 from ben_prtt_enrt_rslt_f pen
21 where pen.person_id = p_submitter_person_id
22 and pen.pl_id = p_pl_id
23 and pen.business_group_id = p_business_group_id
24 and pen.prtt_enrt_rslt_stat_cd is null
25 and p_effective_date between
26 pen.effective_start_date and pen.effective_end_date
27 and pen.enrt_cvg_thru_dt = hr_api.g_eot;
28 --
29 l_pen c_pen%rowtype;
30 l_proc_cd varchar2(30) := null;
31 l_proc varchar2(72) := g_package||'generate_communications';
32 --
33 begin
34 --
35 hr_utility.set_location('Entering:'|| l_proc, 10);
36 --
37 if p_prtt_reimbmt_rqst_stat_cd = 'PNDGPYMT' then --- Pending Payment
38 l_proc_cd := 'RMBRQST';
39 elsif p_prtt_reimbmt_rqst_stat_cd = 'PDINFL' then --- Paid in Full
40 l_proc_cd := 'RMBPYMT';
41 elsif p_prtt_reimbmt_rqst_stat_cd = 'PRTLYPD' then --- Partilly paid
42 l_proc_cd := 'RMBPRPY';
43 elsif p_prtt_reimbmt_rqst_stat_cd = 'DND' then --- Denied
44 l_proc_cd := 'RMBDND';
45 elsif p_prtt_reimbmt_rqst_stat_cd = 'APPRVD' then --- approved
46 l_proc_cd := 'RMBAPRVD';
47 elsif p_prtt_reimbmt_rqst_stat_cd = 'PDINFL' then --- approved for fully paid
48 l_proc_cd := 'RMBAPRVD';
49 elsif p_prtt_reimbmt_rqst_stat_cd = 'PRTLYPD' then --- approved for partial payment
50 l_proc_cd := 'RMBAPRVD';
51 elsif p_prtt_reimbmt_rqst_stat_cd = 'PNDNG' then --- Pending
52 l_proc_cd := 'RMBPNDG';
53 elsif p_prtt_reimbmt_rqst_stat_cd = 'INAPEL' then --- in appeal
54 l_proc_cd := 'RMBNAPEL';
55 elsif p_prtt_reimbmt_rqst_stat_cd = 'VOIDED' then --- Voided
56 l_proc_cd := 'RMBVOID';
57 elsif p_prtt_reimbmt_rqst_stat_cd = 'DPLICT' then --- duplicate
58 l_proc_cd := 'RMBDPLCT';
59 else
60 hr_utility.set_location('Leaving:'|| l_proc, 15);
61 return;
62 end if;
63 --
64 hr_utility.set_location( l_proc, 20);
65 --
66 open c_pen;
67 fetch c_pen into l_pen;
68 --
69 if c_pen%found then
70 --
71 -- Add environment init procedure
72 --
73 -- Work out if we are being called from a concurrent program
74 -- otherwise we need to initialize the environment
75 --
76 if fnd_global.conc_request_id = -1 then
77 --
78 ben_env_object.init(p_business_group_id => p_business_group_id,
79 p_effective_date => p_effective_date,
80 p_thread_id => 1,
81 p_chunk_size => 1,
82 p_threads => 1,
83 p_max_errors => 1,
84 p_benefit_action_id => null);
85 --
86 end if;
87 --
88 ben_generate_communications.main
89 (p_person_id => p_submitter_person_id,
90 -- CWB Changes.
91 p_per_in_ler_id => l_pen.per_in_ler_id,
92 p_pl_id => p_pl_id,
93 p_pl_typ_id => l_pen.pl_typ_id,
94 p_pgm_id => l_pen.pgm_id,
95 p_ler_id => l_pen.ler_id,
96 p_business_group_id => p_business_group_id,
97 p_proc_cd1 => l_proc_cd,
98 p_effective_date => p_effective_date);
99 --
100 end if;
101 --
102 close c_pen;
103 --
104 hr_utility.set_location('Leaving:'|| l_proc, 20);
105 --
106 end generate_communications;
107 --
108 procedure find_pymt_amt (p_effective_date date,
109 p_prtt_reimbmt_rqst_id number,
110 p_business_group_id number,
111 p_pymt_amt in out nocopy number) is
112 --
113
114 l_proc varchar2(72) := g_package || 'find_pymt_amt';
115
116 cursor c_prc is
117 select nvl(prc.aprvd_for_pymt_amt,prc.rqst_amt),
118 prc.popl_yr_perd_id_1,
119 prc.popl_yr_perd_id_2,
120 prc.amt_year1,
121 prc.amt_year2
122 from ben_prtt_reimbmt_rqst_f prc
123 where p_prtt_reimbmt_rqst_id = prc.prtt_reimbmt_rqst_id
124 and p_business_group_id = prc.business_group_id
125 and p_effective_Date between prc.effective_start_date
126 and prc.effective_end_date ;
127
128 l_rqst_amt number ;
129 l_total_pymt_amt number ;
130 l_popl_yr_perd_id_1 number;
131 l_popl_yr_perd_id_2 number;
132 --
133
134 cursor c_pl_info is
135 select pl.pl_id
136 ,pl.cmpr_clms_to_cvg_or_bal_cd
137 ,prc.SUBMITTER_PERSON_ID
138 ,prc.PRTT_ENRT_RSLT_ID
139 ,prc.EXP_INCURD_DT
140 from ben_prtt_reimbmt_rqst_f prc,
141 ben_pl_f pl
142 where prc.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
143 and pl.pl_id = prc.pl_id
144 and prc.EXP_INCURD_DT between pl.effective_start_date
145 and pl.effective_end_date ;
146
147
148 cursor c_yr_amount (p_pl_id number ,
149 p_person_id number,
150 p_popl_yr_perd number ) is
151 select sum(nvl(pry.APRVD_FR_PYMT_AMT,0))
152 from ben_prtt_reimbmt_rqst_f prc,
153 ben_prtt_rmt_aprvd_fr_pymt_f pry
154 where prc.submitter_person_id = p_person_id
155 and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
156 and p_pl_id = prc.pl_id
157 and ((prc.popl_yr_perd_id_1 = p_popl_yr_perd and
158 prc.amt_year2 is null) or
159 (prc.popl_yr_perd_id_2 = p_popl_yr_perd
160 and prc.amt_year1 is null))
161 and prc.effective_end_date = hr_api.g_eot
162 and prc.prtt_reimbmt_rqst_id = pry.prtt_reimbmt_rqst_id
163 ;
164 cursor c_prc_overlap(p_pl_id number ,
165 p_person_id number,
166 p_popl_yr_perd number ) is
167 select prtt_reimbmt_rqst_id,
168 popl_yr_perd_id_1,
169 popl_yr_perd_id_2,
170 amt_year1,
171 amt_year2
172 from ben_prtt_reimbmt_rqst_f prc
173 where prc.submitter_person_id = p_person_id
174 and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
175 and p_pl_id = prc.pl_id
176 and (( prc.popl_yr_perd_id_1 = p_popl_yr_perd and prc.amt_year2 is not null)
177 or (prc.popl_yr_perd_id_2 = p_popl_yr_perd and prc.amt_year1 is not null))
178 ;
179 l_prc_overlap c_prc_overlap%rowtype;
180 --
181 cursor c_paid_amt (p_prtt_reimbmt_rqst_id number) is
182 select sum(nvl(pry.APRVD_FR_PYMT_AMT,0))
183 from ben_prtt_rmt_aprvd_fr_pymt_f pry
184 where pry.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id;
185 --
186 l_paid_amt number;
187 l_pl_info c_pl_info%rowtype ;
188 --
189 cursor c_yr_perd(p_popl_yr_perd_id number) is
190 select END_DATE
191 from ben_yr_perd yrp,
192 ben_popl_yr_perd cpy
193 where cpy.popl_yr_perd_id = p_popl_yr_perd_id
194 and cpy.yr_perd_id = yrp.yr_perd_id;
195 --
196 l_yr_end date;
197 l_amt_year1 number;
198 l_amt_year2 number;
199 l_rqst_amt_1 number;
200
201 begin
202 hr_utility.set_location('Entering:'||l_proc,10);
203 --
204 open c_prc ;
205 fetch c_prc into l_rqst_amt,l_popl_yr_perd_id_1,l_popl_yr_perd_id_2,
206 l_amt_year1,l_amt_year2 ;
207 close c_prc ;
208
209 hr_utility.set_location( 'reimbursement id' || p_prtt_reimbmt_rqst_id , 11);
210 open c_pl_info ;
211 fetch c_pl_info into l_pl_info ;
212 close c_pl_info ;
213 hr_utility.set_location(' bal type '|| l_pl_info.cmpr_clms_to_cvg_or_bal_cd ,20);
214 if l_pl_info.cmpr_clms_to_cvg_or_bal_cd = 'BAL' then
215 -- not in grace period or claim is in only for current year
216 if l_popl_yr_perd_id_2 is null or (l_popl_yr_perd_id_2 is not null and
217 l_amt_year1 is null) then
218 --
219 l_total_pymt_amt := ben_prc_bus.get_year_balance (
220 p_person_id => l_pl_info.submitter_person_id
221 ,p_pgm_id => null
222 ,p_pl_id => l_pl_info.pl_id
223 ,p_business_group_id => p_business_group_id
224 ,p_per_in_ler_id => null
225 ,p_prtt_enrt_rslt_id => l_pl_info.prtt_enrt_rslt_id
226 ,p_effective_date => p_effective_date
227 ,p_exp_incurd_dt => l_pl_info.exp_incurd_dt
228 ) ;
229 hr_utility.set_location(' balance '|| l_total_pymt_amt ,20);
230 --
231 if l_popl_yr_perd_id_2 is null then
232 open c_yr_amount(l_pl_info.pl_id ,
233 l_pl_info.submitter_person_id ,
234 l_popl_yr_perd_id_1 ) ;
235 fetch c_yr_amount into l_rqst_amt ;
236 close c_yr_amount ;
237 --
238 open c_prc_overlap(l_pl_info.pl_id ,
239 l_pl_info.submitter_person_id ,
240 l_popl_yr_perd_id_1 ) ;
241 fetch c_prc_overlap into l_prc_overlap;
242 close c_prc_overlap;
243 --
244 --
245 if l_prc_overlap.prtt_reimbmt_rqst_id is not null then
246 open c_paid_amt(l_prc_overlap.prtt_reimbmt_rqst_id);
247 fetch c_paid_amt into l_paid_amt;
248 close c_paid_amt;
249 end if;
250 if l_paid_amt > 0 then
251 hr_utility.set_location ('Paid Amount '||l_paid_amt,10);
252 if l_prc_overlap.popl_yr_perd_id_1 = l_popl_yr_perd_id_1 then
253 --
254 l_rqst_amt := l_rqst_amt + least(l_paid_amt,l_prc_overlap.amt_year1);
255 elsif
256 --
257 l_prc_overlap.popl_yr_perd_id_2 = l_popl_yr_perd_id_1 then
258 --
259 l_rqst_amt := l_rqst_amt + least((l_paid_amt - l_prc_overlap.amt_year1),l_prc_overlap.amt_year2);
260 end if;
261 --
262 end if;
263 else
264 --
265 open c_yr_amount(l_pl_info.pl_id ,
266 l_pl_info.submitter_person_id ,
267 l_popl_yr_perd_id_2 ) ;
268 fetch c_yr_amount into l_rqst_amt ;
269 close c_yr_amount ;
270 --
271 open c_prc_overlap(l_pl_info.pl_id ,
272 l_pl_info.submitter_person_id ,
273 l_popl_yr_perd_id_2 ) ;
274 fetch c_prc_overlap into l_prc_overlap;
275 close c_prc_overlap;
276 --
277 if l_prc_overlap.prtt_reimbmt_rqst_id is not null then
278 open c_paid_amt(l_prc_overlap.prtt_reimbmt_rqst_id);
279 fetch c_paid_amt into l_paid_amt;
280 close c_paid_amt;
281 end if;
282 --
283 if l_paid_amt > 0 then
284 --
285 hr_utility.set_location ('Paid Amount '||l_paid_amt,11);
286 if l_prc_overlap.popl_yr_perd_id_1 = l_popl_yr_perd_id_2 then
287 --
288 l_rqst_amt := l_rqst_amt + least(l_paid_amt,l_prc_overlap.amt_year1);
289 elsif
290 --
291 l_prc_overlap.popl_yr_perd_id_2 = l_popl_yr_perd_id_1 then
292 --
293 l_rqst_amt := l_rqst_amt + least((l_paid_amt - l_prc_overlap.amt_year1),l_prc_overlap.amt_year2);
294 end if;
295 --
296 end if;
297 end if;
298
299 hr_utility.set_location(' total for the yr '|| l_rqst_amt ,20);
300
301 else
302 --
303 open c_yr_perd (l_popl_yr_perd_id_1);
304 fetch c_yr_perd into l_yr_end;
305 close c_yr_perd;
306 --
307 --
308 l_total_pymt_amt := ben_prc_bus.get_year_balance (
309 p_person_id => l_pl_info.submitter_person_id
310 ,p_pgm_id => null
311 ,p_pl_id => l_pl_info.pl_id
312 ,p_business_group_id => p_business_group_id
313 ,p_per_in_ler_id => null
314 ,p_prtt_enrt_rslt_id => l_pl_info.prtt_enrt_rslt_id
315 ,p_effective_date => l_yr_end
316 ,p_exp_incurd_dt => l_yr_end
317 ) ;
318 hr_utility.set_location(' prevbalance '|| l_total_pymt_amt ,21);
319 open c_yr_amount(l_pl_info.pl_id ,
320 l_pl_info.submitter_person_id ,
321 l_popl_yr_perd_id_1) ;
322 fetch c_yr_amount into l_rqst_amt ;
323 close c_yr_amount ;
324 --
325 open c_prc_overlap(l_pl_info.pl_id ,
326 l_pl_info.submitter_person_id ,
327 l_popl_yr_perd_id_1 ) ;
328 fetch c_prc_overlap into l_prc_overlap;
329 close c_prc_overlap;
330 --
331 if l_prc_overlap.prtt_reimbmt_rqst_id is not null then
332 open c_paid_amt(l_prc_overlap.prtt_reimbmt_rqst_id);
333 fetch c_paid_amt into l_paid_amt;
334 close c_paid_amt;
335 end if;
336 --
337 if l_paid_amt > 0 then
338 hr_utility.set_location ('Paid Amount '||l_paid_amt,12);
339 if l_prc_overlap.popl_yr_perd_id_1 = l_popl_yr_perd_id_1 then
340 --
341 l_rqst_amt := l_rqst_amt + least(l_paid_amt,l_prc_overlap.amt_year1);
342 elsif
343 --
344 l_prc_overlap.popl_yr_perd_id_2 = l_popl_yr_perd_id_1 then
345 --
346 l_rqst_amt := l_rqst_amt + least((l_paid_amt - l_prc_overlap.amt_year1),l_prc_overlap.amt_year2);
347 end if;
348 --
349 end if;
350 hr_utility.set_location(' total for the yr '|| l_rqst_amt ,21);
351 --
352 if l_amt_year2 is not null then
353 --
354 l_total_pymt_amt := l_total_pymt_amt +
355 ben_prc_bus.get_year_balance (
356 p_person_id => l_pl_info.submitter_person_id
357 ,p_pgm_id => null
358 ,p_pl_id => l_pl_info.pl_id
359 ,p_business_group_id => p_business_group_id
360 ,p_per_in_ler_id => null
361 ,p_prtt_enrt_rslt_id => l_pl_info.prtt_enrt_rslt_id
362 ,p_effective_date => p_effective_date
363 ,p_exp_incurd_dt => l_pl_info.exp_incurd_dt
364 ) ;
365 open c_yr_amount(l_pl_info.pl_id ,
366 l_pl_info.submitter_person_id ,
367 l_popl_yr_perd_id_2) ;
368 fetch c_yr_amount into l_rqst_amt ;
369 close c_yr_amount ;
370 --
371 open c_prc_overlap(l_pl_info.pl_id ,
372 l_pl_info.submitter_person_id ,
373 l_popl_yr_perd_id_1 ) ;
374 fetch c_prc_overlap into l_prc_overlap;
375 close c_prc_overlap;
376 --
377 if l_prc_overlap.prtt_reimbmt_rqst_id is not null then
378 open c_paid_amt(l_prc_overlap.prtt_reimbmt_rqst_id);
379 fetch c_paid_amt into l_paid_amt;
380 close c_paid_amt;
381 end if;
382 --
383 if l_paid_amt > 0 then
384 hr_utility.set_location ('Paid Amount '||l_paid_amt,13);
385 if l_prc_overlap.popl_yr_perd_id_1 = l_popl_yr_perd_id_1 then
386 --
387 l_rqst_amt := l_rqst_amt + least(l_paid_amt,l_prc_overlap.amt_year1);
388 elsif
389 --
390 l_prc_overlap.popl_yr_perd_id_2 = l_popl_yr_perd_id_1 then
391 --
392 l_rqst_amt := l_rqst_amt + least((l_paid_amt - l_prc_overlap.amt_year1),l_prc_overlap.amt_year2);
393 end if;
394 --
395 end if;
396 --
397 end if;
398
399
400 end if;
401 --
402
403 if nvl(l_rqst_amt,0) + nvl(p_pymt_amt,0)
404 > l_total_pymt_amt then
405 -- adjust the payment to total contribution - already paid amount
406 p_pymt_amt := l_total_pymt_amt - nvl(l_rqst_amt,0);
407
408 end if ;
409
410
411 end if ;
412
413 --
414 hr_utility.set_location(' Leaving:'||l_proc,20);
415 end find_pymt_amt;
416 --
417 procedure find_popl_yr_perd
418 (p_pl_id number,
419 p_exp_incurd_dt date,
420 p_effective_date date,
421 p_business_group_id number,
422 p_popl_yr_perd_id_1 out nocopy number,
423 p_popl_yr_perd_id_2 out nocopy number) is
424 --
425 cursor c1 is
426 select popl_yr_perd_id
427 from ben_popl_yr_perd cpy,
428 ben_yr_perd yrp
429 where p_exp_incurd_dt between yrp.start_date
430 and nvl(cpy.PY_CLMS_THRU_DT,yrp.end_date)
431 and p_effective_date between yrp.start_date
432 and nvl(cpy.ACPT_CLM_RQSTS_THRU_DT,yrp.end_date)
433 and cpy.yr_perd_id = yrp.yr_perd_id
434 and cpy.pl_id = p_pl_id
435 and cpy.business_group_id = p_business_group_id
436 and yrp.business_group_id = p_business_group_id
437 order by yrp.start_date;
438 --
439 cnt number;
440 l_popl_yr_perd_id number;
441 --
442 begin
443 --
444 cnt := 1;
445 open c1;
446 loop
447 fetch c1 into l_popl_yr_perd_id;
448 if c1%found then
449 if cnt = 1 then
450 p_popl_yr_perd_id_1 := l_popl_yr_perd_id;
451 elsif cnt = 2 then
452 p_popl_yr_perd_id_2 := l_popl_yr_perd_id;
453 end if;
454 else
455 exit;
456 end if;
457 cnt := cnt + 1;
458 if cnt >= 3 then
459 exit;
460 end if;
461 end loop;
462 close c1;
463 --
464 end;
465
466
467
468 -- ----------------------------------------------------------------------------
469 -- |------------------------< create_PRTT_REIMBMT_RQST >----------------------|
470 -- ----------------------------------------------------------------------------
471 --
472 procedure create_PRTT_REIMBMT_RQST
473 (p_validate in boolean default false
474 ,p_prtt_reimbmt_rqst_id out nocopy number
475 ,p_effective_start_date out nocopy date
476 ,p_effective_end_date out nocopy date
477 ,p_incrd_from_dt in date default null
478 ,p_incrd_to_dt in date default null
479 ,p_rqst_num in out nocopy number
480 ,p_rqst_amt in number default null
481 ,p_rqst_amt_uom in varchar2 default null
482 ,p_rqst_btch_num in number default null
483 ,p_prtt_reimbmt_rqst_stat_cd in out nocopy varchar2
484 ,p_reimbmt_ctfn_typ_prvdd_cd in varchar2 default null
485 ,p_rcrrg_cd in varchar2 default null
486 ,p_submitter_person_id in number default null
487 ,p_recipient_person_id in number default null
488 ,p_provider_person_id in number default null
489 ,p_provider_ssn_person_id in number default null
490 ,p_pl_id in number default null
491 ,p_gd_or_svc_typ_id in number default null
492 ,p_contact_relationship_id in number default null
493 ,p_business_group_id in number default null
494 ,p_opt_id in number default null
495 ,p_popl_yr_perd_id_1 in number default null
496 ,p_popl_yr_perd_id_2 in number default null
497 ,p_amt_year1 in number default null
498 ,p_amt_year2 in number default null
499 ,p_prc_attribute_category in varchar2 default null
500 ,p_prc_attribute1 in varchar2 default null
501 ,p_prc_attribute2 in varchar2 default null
502 ,p_prc_attribute3 in varchar2 default null
503 ,p_prc_attribute4 in varchar2 default null
504 ,p_prc_attribute5 in varchar2 default null
505 ,p_prc_attribute6 in varchar2 default null
506 ,p_prc_attribute7 in varchar2 default null
507 ,p_prc_attribute8 in varchar2 default null
508 ,p_prc_attribute9 in varchar2 default null
509 ,p_prc_attribute10 in varchar2 default null
510 ,p_prc_attribute11 in varchar2 default null
511 ,p_prc_attribute12 in varchar2 default null
512 ,p_prc_attribute13 in varchar2 default null
513 ,p_prc_attribute14 in varchar2 default null
514 ,p_prc_attribute15 in varchar2 default null
515 ,p_prc_attribute16 in varchar2 default null
516 ,p_prc_attribute17 in varchar2 default null
517 ,p_prc_attribute18 in varchar2 default null
518 ,p_prc_attribute19 in varchar2 default null
519 ,p_prc_attribute20 in varchar2 default null
520 ,p_prc_attribute21 in varchar2 default null
521 ,p_prc_attribute22 in varchar2 default null
522 ,p_prc_attribute23 in varchar2 default null
523 ,p_prc_attribute24 in varchar2 default null
524 ,p_prc_attribute25 in varchar2 default null
525 ,p_prc_attribute26 in varchar2 default null
526 ,p_prc_attribute27 in varchar2 default null
527 ,p_prc_attribute28 in varchar2 default null
528 ,p_prc_attribute29 in varchar2 default null
529 ,p_prc_attribute30 in varchar2 default null
530 ,p_prtt_enrt_rslt_id out nocopy number
531 ,p_comment_id in number default null
532 ,p_object_version_number out nocopy number
533 ,p_effective_date in date
534 ,p_stat_rsn_cd in out nocopy varchar2
535 ,p_pymt_stat_cd in out nocopy varchar2
536 ,p_pymt_stat_rsn_cd in out nocopy varchar2
537 ,p_stat_ovrdn_flag in varchar2 default null
538 ,p_stat_ovrdn_rsn_cd in varchar2 default null
539 ,p_stat_prr_to_ovrd in varchar2 default null
540 ,p_pymt_stat_ovrdn_flag in varchar2 default null
541 ,p_pymt_stat_ovrdn_rsn_cd in varchar2 default null
542 ,p_pymt_stat_prr_to_ovrd in varchar2 default null
543 ,p_adjmt_flag in varchar2 default null
544 ,p_submtd_dt in date default null
545 ,p_ttl_rqst_amt in number default null
546 ,p_aprvd_for_pymt_amt in out nocopy number
547 ,p_exp_incurd_dt in date default null
548 ) is
549 --
550 -- Declare cursors and local variables
551 --
552 cursor c_rslt_rec is
553 select pen.prtt_enrt_rslt_id,
554 pen.pgm_id,
555 pen.per_in_ler_id
556 from ben_prtt_enrt_rslt_f pen
557 where pen.pl_id = p_pl_id
558 and pen.prtt_enrt_rslt_stat_cd is null
559 and pen.person_id = p_submitter_person_id
560 and pen.business_group_id = p_business_group_id
561 and p_effective_date between
562 pen.effective_start_date and pen.effective_end_date
563 and p_incrd_from_dt <= pen.enrt_cvg_thru_dt
564 and p_incrd_to_dt >= pen.enrt_cvg_strt_dt ;
565 --and p_effective_date between
566 -- pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt;
567 --
568 l_rslt_rec c_rslt_rec%rowtype;
569 --
570 cursor c_abr_pl
571 is
572 select abr.acty_base_rt_id,
573 abr.rt_typ_cd,
574 abr.tx_typ_cd,
575 abr.acty_typ_cd,
576 abr.rt_mlt_cd,
577 abr.bnft_rt_typ_cd,
578 abr.dsply_on_enrt_flag,
579 abr.comp_lvl_fctr_id,
580 abr.actl_prem_id,
581 abr.input_value_id,
582 abr.element_type_id
583 from ben_acty_base_rt_f abr
584 where pl_id = p_pl_id
585 and acty_typ_cd like 'PRD%'
586 and acty_base_rt_stat_cd = 'A'
587 -- and p_effective_date between
588 and p_exp_incurd_dt between
589 abr.effective_start_date and
590 abr.effective_end_date;
591
592 --- reimbursement rate can be fixed whether plan level or plan in program level
593 cursor c_abr_plip (p_pl_id number, p_pgm_id number)
594 is
595 select abr.acty_base_rt_id,
596 abr.rt_typ_cd,
597 abr.tx_typ_cd,
598 abr.acty_typ_cd,
599 abr.rt_mlt_cd,
600 abr.bnft_rt_typ_cd,
601 abr.dsply_on_enrt_flag,
602 abr.comp_lvl_fctr_id,
603 abr.actl_prem_id,
604 abr.input_value_id,
605 abr.element_type_id
606 from ben_acty_base_rt_f abr,
607 ben_plip_f plp
608 where plp.pl_id = p_pl_id
609 and plp.pgm_id = p_pgm_id
610 and abr.acty_base_rt_stat_cd = 'A'
611 --and p_effective_date between
612 and p_exp_incurd_dt between
613 plp.effective_start_date and
614 plp.effective_end_date
615 and plp.plip_id = abr.plip_id
616 and abr.acty_typ_cd like 'PRD%'
617 --and p_effective_date between
618 and p_exp_incurd_dt between
619 abr.effective_start_date and
620 abr.effective_end_date;
621 --
622 l_acty_base_rt c_abr_pl%rowtype;
623
624 l_cvg_amt_calc_mthd_id number;
625 l_prtt_rmt_aprvd_fr_pymt_id number ;
626 l_prtt_reimbmt_rqst_id ben_prtt_reimbmt_rqst_f.prtt_reimbmt_rqst_id%TYPE;
627 l_effective_start_date ben_prtt_reimbmt_rqst_f.effective_start_date%TYPE;
628 l_effective_end_date ben_prtt_reimbmt_rqst_f.effective_end_date%TYPE;
629 l_effective_start_date_pymt ben_prtt_reimbmt_rqst_f.effective_start_date%TYPE;
630 l_effective_end_date_pymt ben_prtt_reimbmt_rqst_f.effective_end_date%TYPE;
631 l_proc varchar2(72) := g_package||'create_PRTT_REIMBMT_RQST';
632 l_object_version_number ben_prtt_reimbmt_rqst_f.object_version_number%TYPE;
633 l_object_version_number_pymt ben_prtt_reimbmt_rqst_f.object_version_number%TYPE;
634 --
635 l_pymt_amount number ;
636 l_popl_yr_perd_id_1 number;
637 l_popl_yr_perd_id_2 number;
638 begin
639 --
640 hr_utility.set_location('Entering:'|| l_proc, 10);
641 --
642 -- Issue a savepoint if operating in validation only mode
643 --
644 savepoint create_PRTT_REIMBMT_RQST;
645 --
646 hr_utility.set_location(l_proc, 20);
647 --this cursor is for gettin value for resutlt id and to create prtt_rt_val
648 open c_rslt_rec;
649 fetch c_rslt_rec into l_rslt_rec;
650 close c_rslt_rec;
651 p_prtt_enrt_rslt_id := l_rslt_rec.prtt_enrt_rslt_id ;
652
653 ---- chek for reimbursement rate
654 open c_abr_plip(p_pl_id,l_rslt_rec.pgm_id);
655 fetch c_abr_plip into l_acty_base_rt;
656 if c_abr_plip%notfound then
657 open c_abr_pl ;
658 fetch c_abr_pl into l_acty_base_rt ;
659 if c_abr_pl%notfound then
660 close c_abr_pl;
661 close c_abr_plip ;
662 fnd_message.set_name('BEN','BEN_92697_NO_REMBMT_RATE');
663 fnd_message.raise_error;
664 end if ;
665 close c_abr_pl;
666 end if;
667 close c_abr_plip;
668 --
669 --populate year period ids based on exp incurred date
670 find_popl_yr_perd (p_pl_id => p_pl_id,
671 p_business_group_id => p_business_group_id,
672 p_effective_date => p_effective_date,
673 p_exp_incurd_dt => p_exp_incurd_dt,
674 p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1,
675 p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2);
676 -- Process Logic
677 --
678 begin
679 --
680 -- Start of API User Hook for the before hook of create_PRTT_REIMBMT_RQST
681 --
682 ben_PRTT_REIMBMT_RQST_bk1.create_PRTT_REIMBMT_RQST_b
683 (
684 p_incrd_from_dt => p_incrd_from_dt
685 ,p_incrd_to_dt => p_incrd_to_dt
686 ,p_rqst_num => p_rqst_num
687 ,p_rqst_amt => p_rqst_amt
688 ,p_rqst_amt_uom => p_rqst_amt_uom
689 ,p_rqst_btch_num => p_rqst_btch_num
690 ,p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd
691 ,p_reimbmt_ctfn_typ_prvdd_cd => p_reimbmt_ctfn_typ_prvdd_cd
692 ,p_rcrrg_cd => p_rcrrg_cd
693 ,p_submitter_person_id => p_submitter_person_id
694 ,p_recipient_person_id => p_recipient_person_id
695 ,p_provider_person_id => p_provider_person_id
696 ,p_provider_ssn_person_id => p_provider_ssn_person_id
697 ,p_pl_id => p_pl_id
698 ,p_gd_or_svc_typ_id => p_gd_or_svc_typ_id
699 ,p_contact_relationship_id => p_contact_relationship_id
700 ,p_business_group_id => p_business_group_id
701 ,p_opt_id => p_opt_id
702 ,p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1
703 ,p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2
704 ,p_amt_year1 => p_amt_year1
705 ,p_amt_year2 => p_amt_year2
706 ,p_prc_attribute_category => p_prc_attribute_category
707 ,p_prc_attribute1 => p_prc_attribute1
708 ,p_prc_attribute2 => p_prc_attribute2
709 ,p_prc_attribute3 => p_prc_attribute3
710 ,p_prc_attribute4 => p_prc_attribute4
711 ,p_prc_attribute5 => p_prc_attribute5
712 ,p_prc_attribute6 => p_prc_attribute6
713 ,p_prc_attribute7 => p_prc_attribute7
714 ,p_prc_attribute8 => p_prc_attribute8
715 ,p_prc_attribute9 => p_prc_attribute9
716 ,p_prc_attribute10 => p_prc_attribute10
717 ,p_prc_attribute11 => p_prc_attribute11
718 ,p_prc_attribute12 => p_prc_attribute12
719 ,p_prc_attribute13 => p_prc_attribute13
720 ,p_prc_attribute14 => p_prc_attribute14
721 ,p_prc_attribute15 => p_prc_attribute15
722 ,p_prc_attribute16 => p_prc_attribute16
723 ,p_prc_attribute17 => p_prc_attribute17
724 ,p_prc_attribute18 => p_prc_attribute18
725 ,p_prc_attribute19 => p_prc_attribute19
726 ,p_prc_attribute20 => p_prc_attribute20
727 ,p_prc_attribute21 => p_prc_attribute21
728 ,p_prc_attribute22 => p_prc_attribute22
729 ,p_prc_attribute23 => p_prc_attribute23
730 ,p_prc_attribute24 => p_prc_attribute24
731 ,p_prc_attribute25 => p_prc_attribute25
732 ,p_prc_attribute26 => p_prc_attribute26
733 ,p_prc_attribute27 => p_prc_attribute27
734 ,p_prc_attribute28 => p_prc_attribute28
735 ,p_prc_attribute29 => p_prc_attribute29
736 ,p_prc_attribute30 => p_prc_attribute30
737 ,p_effective_date => trunc(p_effective_date)
738 ,P_STAT_RSN_CD => P_STAT_RSN_CD
739 ,p_Pymt_stat_cd => p_Pymt_stat_cd
740 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
741 ,p_stat_ovrdn_flag => p_stat_ovrdn_flag
742 ,p_stat_ovrdn_rsn_cd => p_stat_ovrdn_rsn_cd
743 ,p_stat_prr_to_ovrd => p_stat_prr_to_ovrd
744 ,p_pymt_stat_ovrdn_flag => p_pymt_stat_ovrdn_flag
745 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
746 ,p_pymt_stat_prr_to_ovrd => p_pymt_stat_prr_to_ovrd
747 ,p_Adjmt_flag => p_Adjmt_flag
748 ,p_Submtd_dt => trunc(p_Submtd_dt)
749 ,p_Ttl_rqst_amt => p_Ttl_rqst_amt
750 ,p_Aprvd_for_pymt_amt => p_Aprvd_for_pymt_amt
751 ,p_exp_incurd_dt => p_exp_incurd_dt
752 );
753 exception
754 when hr_api.cannot_find_prog_unit then
755 hr_api.cannot_find_prog_unit_error
756 (
757 p_module_name => 'CREATE_PRTT_REIMBMT_RQST'
758 ,p_hook_type => 'BP'
759 );
760 --
761 -- End of API User Hook for the before hook of create_PRTT_REIMBMT_RQST
762 --
763 end;
764 --
765 ben_prc_ins.ins
766 (
767 p_prtt_reimbmt_rqst_id => l_prtt_reimbmt_rqst_id
768 ,p_effective_start_date => l_effective_start_date
769 ,p_effective_end_date => l_effective_end_date
770 ,p_incrd_from_dt => p_incrd_from_dt
771 ,p_incrd_to_dt => p_incrd_to_dt
772 ,p_rqst_num => p_rqst_num
773 ,p_rqst_amt => p_rqst_amt
774 ,p_rqst_amt_uom => p_rqst_amt_uom
775 ,p_rqst_btch_num => p_rqst_btch_num
776 ,p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd
777 ,p_reimbmt_ctfn_typ_prvdd_cd => p_reimbmt_ctfn_typ_prvdd_cd
778 ,p_rcrrg_cd => p_rcrrg_cd
779 ,p_submitter_person_id => p_submitter_person_id
780 ,p_recipient_person_id => p_recipient_person_id
781 ,p_provider_person_id => p_provider_person_id
782 ,p_provider_ssn_person_id => p_provider_ssn_person_id
783 ,p_pl_id => p_pl_id
784 ,p_gd_or_svc_typ_id => p_gd_or_svc_typ_id
785 ,p_contact_relationship_id => p_contact_relationship_id
786 ,p_business_group_id => p_business_group_id
787 ,p_opt_id => p_opt_id
788 ,p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1
789 ,p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2
790 ,p_amt_year1 => p_amt_year1
791 ,p_amt_year2 => p_amt_year2
792 ,p_prc_attribute_category => p_prc_attribute_category
793 ,p_prc_attribute1 => p_prc_attribute1
794 ,p_prc_attribute2 => p_prc_attribute2
795 ,p_prc_attribute3 => p_prc_attribute3
796 ,p_prc_attribute4 => p_prc_attribute4
797 ,p_prc_attribute5 => p_prc_attribute5
798 ,p_prc_attribute6 => p_prc_attribute6
799 ,p_prc_attribute7 => p_prc_attribute7
800 ,p_prc_attribute8 => p_prc_attribute8
801 ,p_prc_attribute9 => p_prc_attribute9
802 ,p_prc_attribute10 => p_prc_attribute10
803 ,p_prc_attribute11 => p_prc_attribute11
804 ,p_prc_attribute12 => p_prc_attribute12
805 ,p_prc_attribute13 => p_prc_attribute13
806 ,p_prc_attribute14 => p_prc_attribute14
807 ,p_prc_attribute15 => p_prc_attribute15
808 ,p_prc_attribute16 => p_prc_attribute16
809 ,p_prc_attribute17 => p_prc_attribute17
810 ,p_prc_attribute18 => p_prc_attribute18
811 ,p_prc_attribute19 => p_prc_attribute19
812 ,p_prc_attribute20 => p_prc_attribute20
813 ,p_prc_attribute21 => p_prc_attribute21
814 ,p_prc_attribute22 => p_prc_attribute22
815 ,p_prc_attribute23 => p_prc_attribute23
816 ,p_prc_attribute24 => p_prc_attribute24
817 ,p_prc_attribute25 => p_prc_attribute25
818 ,p_prc_attribute26 => p_prc_attribute26
819 ,p_prc_attribute27 => p_prc_attribute27
820 ,p_prc_attribute28 => p_prc_attribute28
821 ,p_prc_attribute29 => p_prc_attribute29
822 ,p_prc_attribute30 => p_prc_attribute30
823 ,p_prtt_enrt_rslt_id => l_rslt_rec.prtt_enrt_rslt_id
824 ,p_comment_id => p_comment_id
825 ,p_object_version_number => l_object_version_number
826 ,p_effective_date => trunc(p_effective_date)
827 ,P_STAT_RSN_CD => P_STAT_RSN_CD
828 ,p_Pymt_stat_cd => p_Pymt_stat_cd
829 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
830 ,p_stat_ovrdn_flag => p_stat_ovrdn_flag
831 ,p_stat_ovrdn_rsn_cd => p_stat_ovrdn_rsn_cd
832 ,p_stat_prr_to_ovrd => p_stat_prr_to_ovrd
833 ,p_pymt_stat_ovrdn_flag => p_pymt_stat_ovrdn_flag
834 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
835 ,p_pymt_stat_prr_to_ovrd => p_pymt_stat_prr_to_ovrd
836 ,p_Adjmt_flag => p_Adjmt_flag
837 ,p_Submtd_dt => trunc(p_Submtd_dt)
838 ,p_Ttl_rqst_amt => p_Ttl_rqst_amt
839 ,p_Aprvd_for_pymt_amt => p_Aprvd_for_pymt_amt
840 ,p_pymt_amount => l_pymt_amount
841 ,p_exp_incurd_dt => p_exp_incurd_dt
842 );
843 --hr_utility.set_location('after ins in api ' || p_prtt_reimbmt_rqst_stat_cd, 110);
844 --hr_utility.set_location('after ins status ' || P_STAT_RSN_CD, 110);
845 --hr_utility.set_location('after ins in api ' || p_Pymt_stat_cd, 110);
846 --hr_utility.set_location('after ins status ' || p_pymt_stat_rsn_cd, 110);
847
848
849 --
850 begin
851 --
852 -- Start of API User Hook for the after hook of create_PRTT_REIMBMT_RQST
853 --
854 ben_PRTT_REIMBMT_RQST_bk1.create_PRTT_REIMBMT_RQST_a
855 (
856 p_prtt_reimbmt_rqst_id => l_prtt_reimbmt_rqst_id
857 ,p_effective_start_date => l_effective_start_date
858 ,p_effective_end_date => l_effective_end_date
859 ,p_incrd_from_dt => p_incrd_from_dt
860 ,p_incrd_to_dt => p_incrd_to_dt
861 ,p_rqst_num => p_rqst_num
862 ,p_rqst_amt => p_rqst_amt
863 ,p_rqst_amt_uom => p_rqst_amt_uom
864 ,p_rqst_btch_num => p_rqst_btch_num
865 ,p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd
866 ,p_reimbmt_ctfn_typ_prvdd_cd => p_reimbmt_ctfn_typ_prvdd_cd
867 ,p_rcrrg_cd => p_rcrrg_cd
868 ,p_submitter_person_id => p_submitter_person_id
869 ,p_recipient_person_id => p_recipient_person_id
870 ,p_provider_person_id => p_provider_person_id
871 ,p_provider_ssn_person_id => p_provider_ssn_person_id
872 ,p_pl_id => p_pl_id
873 ,p_gd_or_svc_typ_id => p_gd_or_svc_typ_id
874 ,p_contact_relationship_id => p_contact_relationship_id
875 ,p_business_group_id => p_business_group_id
876 ,p_opt_id => p_opt_id
877 ,p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1
878 ,p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2
879 ,p_amt_year1 => p_amt_year1
880 ,p_amt_year2 => p_amt_year2
881 ,p_prc_attribute_category => p_prc_attribute_category
882 ,p_prc_attribute1 => p_prc_attribute1
883 ,p_prc_attribute2 => p_prc_attribute2
884 ,p_prc_attribute3 => p_prc_attribute3
885 ,p_prc_attribute4 => p_prc_attribute4
886 ,p_prc_attribute5 => p_prc_attribute5
887 ,p_prc_attribute6 => p_prc_attribute6
888 ,p_prc_attribute7 => p_prc_attribute7
889 ,p_prc_attribute8 => p_prc_attribute8
890 ,p_prc_attribute9 => p_prc_attribute9
891 ,p_prc_attribute10 => p_prc_attribute10
892 ,p_prc_attribute11 => p_prc_attribute11
893 ,p_prc_attribute12 => p_prc_attribute12
894 ,p_prc_attribute13 => p_prc_attribute13
895 ,p_prc_attribute14 => p_prc_attribute14
896 ,p_prc_attribute15 => p_prc_attribute15
897 ,p_prc_attribute16 => p_prc_attribute16
898 ,p_prc_attribute17 => p_prc_attribute17
899 ,p_prc_attribute18 => p_prc_attribute18
900 ,p_prc_attribute19 => p_prc_attribute19
901 ,p_prc_attribute20 => p_prc_attribute20
902 ,p_prc_attribute21 => p_prc_attribute21
903 ,p_prc_attribute22 => p_prc_attribute22
904 ,p_prc_attribute23 => p_prc_attribute23
905 ,p_prc_attribute24 => p_prc_attribute24
906 ,p_prc_attribute25 => p_prc_attribute25
907 ,p_prc_attribute26 => p_prc_attribute26
908 ,p_prc_attribute27 => p_prc_attribute27
909 ,p_prc_attribute28 => p_prc_attribute28
910 ,p_prc_attribute29 => p_prc_attribute29
911 ,p_prc_attribute30 => p_prc_attribute30
912 ,p_object_version_number => l_object_version_number
913 ,p_effective_date => trunc(p_effective_date)
914 ,P_STAT_RSN_CD => P_STAT_RSN_CD
915 ,p_Pymt_stat_cd => p_Pymt_stat_cd
916 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
917 ,p_stat_ovrdn_flag => p_stat_ovrdn_flag
918 ,p_stat_ovrdn_rsn_cd => p_stat_ovrdn_rsn_cd
919 ,p_stat_prr_to_ovrd => p_stat_prr_to_ovrd
920 ,p_pymt_stat_ovrdn_flag => p_pymt_stat_ovrdn_flag
921 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
922 ,p_pymt_stat_prr_to_ovrd => p_pymt_stat_prr_to_ovrd
923 ,p_Adjmt_flag => p_Adjmt_flag
924 ,p_Submtd_dt => trunc(p_Submtd_dt)
925 ,p_Ttl_rqst_amt => p_Ttl_rqst_amt
926 ,p_Aprvd_for_pymt_amt => p_Aprvd_for_pymt_amt
927 ,p_exp_incurd_dt => p_exp_incurd_dt
928 );
929 exception
930 when hr_api.cannot_find_prog_unit then
931 hr_api.cannot_find_prog_unit_error
932 (p_module_name => 'CREATE_PRTT_REIMBMT_RQST'
933 ,p_hook_type => 'AP'
934 );
935 --
936 -- End of API User Hook for the after hook of create_PRTT_REIMBMT_RQST
937 --
938 end;
939 --
940 --- Create payment entry only when approved
941 hr_utility.set_location(' Payable Amount in api '|| l_pymt_amount, 110);
942 if p_prtt_reimbmt_rqst_stat_cd in ('APPRVD','PDINFL','PRTLYPD') then
943 --bug#5527088
944 find_pymt_amt ( p_effective_date => p_effective_date,
945 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
946 p_business_group_id => p_business_group_id,
947 p_pymt_amt => l_pymt_amount);
948 --
949 if nvl(l_pymt_amount,0) <> 0 then
950 ben_prtt_rmt_aprvd_pymt_api.create_prtt_rmt_aprvd_pymt
951 (p_validate => p_validate
952 ,p_prtt_rmt_aprvd_fr_pymt_id => l_prtt_rmt_aprvd_fr_pymt_id
953 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
954 ,p_effective_start_date => l_effective_start_date_pymt
955 ,p_effective_end_date => l_effective_end_date_pymt
956 ,p_adjmt_flag => 'N'
957 ,p_aprvd_fr_pymt_amt => l_pymt_amount
958 ,p_business_group_id => p_business_group_id
959 ,p_object_version_number => l_object_version_number_pymt
960 ,p_effective_date => p_effective_date
961 );
962 end if ;
963
964 end if ;
965
966 hr_utility.set_location(l_proc, 135);
967 if p_prtt_reimbmt_rqst_stat_cd is not null then
968 generate_communications(
969 p_submitter_person_id => p_submitter_person_id,
970 p_pl_id => p_pl_id,
971 p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd,
972 p_business_group_id => p_business_group_id,
973 p_effective_date => p_effective_date);
974 end if ;
975 --
976 hr_utility.set_location(l_proc, 60);
977 --
978 -- When in validation only mode raise the Validate_Enabled exception
979 --
980 if p_validate then
981 raise hr_api.validate_enabled;
982 end if;
983 --
984 -- Set all output arguments
985 --
986 p_prtt_reimbmt_rqst_id := l_prtt_reimbmt_rqst_id;
987 p_effective_start_date := l_effective_start_date;
988 p_effective_end_date := l_effective_end_date;
989 p_object_version_number := l_object_version_number;
990 --
991 hr_utility.set_location(' Leaving:'||l_proc, 70);
992 --
993 exception
994 --
995 when hr_api.validate_enabled then
996 --
997 -- As the Validate_Enabled exception has been raised
998 -- we must rollback to the savepoint
999 --
1000 ROLLBACK TO create_PRTT_REIMBMT_RQST;
1001 --
1002 -- Only set output warning arguments
1003 -- (Any key or derived arguments must be set to null
1004 -- when validation only mode is being used.)
1005 --
1006 p_prtt_reimbmt_rqst_id := null;
1007 p_effective_start_date := null;
1008 p_effective_end_date := null;
1009 p_object_version_number := null;
1010 hr_utility.set_location(' Leaving:'||l_proc, 80);
1011 --
1012 when others then
1013 --
1014 -- A validation or unexpected error has occured
1015 --
1016 ROLLBACK TO create_PRTT_REIMBMT_RQST;
1017 raise;
1018 --
1019 end create_PRTT_REIMBMT_RQST;
1020 -- ----------------------------------------------------------------------------
1021 -- |------------------------< update_PRTT_REIMBMT_RQST >--- ------------------|
1022 -- ----------------------------------------------------------------------------
1023 --
1024 procedure update_PRTT_REIMBMT_RQST
1025 (p_validate in boolean default false
1026 ,p_prtt_reimbmt_rqst_id in number
1027 ,p_effective_start_date out nocopy date
1028 ,p_effective_end_date out nocopy date
1029 ,p_incrd_from_dt in date default hr_api.g_date
1030 ,p_incrd_to_dt in date default hr_api.g_date
1031 ,p_rqst_num in number default hr_api.g_number
1032 ,p_rqst_amt in number default hr_api.g_number
1033 ,p_rqst_amt_uom in varchar2 default hr_api.g_varchar2
1034 ,p_rqst_btch_num in number default hr_api.g_number
1035 ,p_prtt_reimbmt_rqst_stat_cd in out nocopy varchar2
1036 ,p_reimbmt_ctfn_typ_prvdd_cd in varchar2 default hr_api.g_varchar2
1037 ,p_rcrrg_cd in varchar2 default hr_api.g_varchar2
1038 ,p_submitter_person_id in number default hr_api.g_number
1039 ,p_recipient_person_id in number default hr_api.g_number
1040 ,p_provider_person_id in number default hr_api.g_number
1041 ,p_provider_ssn_person_id in number default hr_api.g_number
1042 ,p_pl_id in number default hr_api.g_number
1043 ,p_gd_or_svc_typ_id in number default hr_api.g_number
1044 ,p_contact_relationship_id in number default hr_api.g_number
1045 ,p_business_group_id in number default hr_api.g_number
1046 ,p_opt_id in number default hr_api.g_number
1047 ,p_popl_yr_perd_id_1 in number default hr_api.g_number
1048 ,p_popl_yr_perd_id_2 in number default hr_api.g_number
1049 ,p_amt_year1 in number default hr_api.g_number
1050 ,p_amt_year2 in number default hr_api.g_number
1051 ,p_prc_attribute_category in varchar2 default hr_api.g_varchar2
1052 ,p_prc_attribute1 in varchar2 default hr_api.g_varchar2
1053 ,p_prc_attribute2 in varchar2 default hr_api.g_varchar2
1054 ,p_prc_attribute3 in varchar2 default hr_api.g_varchar2
1055 ,p_prc_attribute4 in varchar2 default hr_api.g_varchar2
1056 ,p_prc_attribute5 in varchar2 default hr_api.g_varchar2
1057 ,p_prc_attribute6 in varchar2 default hr_api.g_varchar2
1058 ,p_prc_attribute7 in varchar2 default hr_api.g_varchar2
1059 ,p_prc_attribute8 in varchar2 default hr_api.g_varchar2
1060 ,p_prc_attribute9 in varchar2 default hr_api.g_varchar2
1061 ,p_prc_attribute10 in varchar2 default hr_api.g_varchar2
1062 ,p_prc_attribute11 in varchar2 default hr_api.g_varchar2
1063 ,p_prc_attribute12 in varchar2 default hr_api.g_varchar2
1064 ,p_prc_attribute13 in varchar2 default hr_api.g_varchar2
1065 ,p_prc_attribute14 in varchar2 default hr_api.g_varchar2
1066 ,p_prc_attribute15 in varchar2 default hr_api.g_varchar2
1067 ,p_prc_attribute16 in varchar2 default hr_api.g_varchar2
1068 ,p_prc_attribute17 in varchar2 default hr_api.g_varchar2
1069 ,p_prc_attribute18 in varchar2 default hr_api.g_varchar2
1070 ,p_prc_attribute19 in varchar2 default hr_api.g_varchar2
1071 ,p_prc_attribute20 in varchar2 default hr_api.g_varchar2
1072 ,p_prc_attribute21 in varchar2 default hr_api.g_varchar2
1073 ,p_prc_attribute22 in varchar2 default hr_api.g_varchar2
1074 ,p_prc_attribute23 in varchar2 default hr_api.g_varchar2
1075 ,p_prc_attribute24 in varchar2 default hr_api.g_varchar2
1076 ,p_prc_attribute25 in varchar2 default hr_api.g_varchar2
1077 ,p_prc_attribute26 in varchar2 default hr_api.g_varchar2
1078 ,p_prc_attribute27 in varchar2 default hr_api.g_varchar2
1079 ,p_prc_attribute28 in varchar2 default hr_api.g_varchar2
1080 ,p_prc_attribute29 in varchar2 default hr_api.g_varchar2
1081 ,p_prc_attribute30 in varchar2 default hr_api.g_varchar2
1082 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
1083 ,p_comment_id in number default hr_api.g_number
1084 ,p_object_version_number in out nocopy number
1085 ,p_effective_date in date
1086 ,p_datetrack_mode in varchar2
1087 ,p_stat_rsn_cd in out nocopy varchar2
1088 ,p_pymt_stat_cd in out nocopy varchar2
1089 ,p_pymt_stat_rsn_cd in out nocopy varchar2
1090 ,p_stat_ovrdn_flag in varchar2 default hr_api.g_varchar2
1091 ,p_stat_ovrdn_rsn_cd in varchar2 default hr_api.g_varchar2
1092 ,p_stat_prr_to_ovrd in varchar2 default hr_api.g_varchar2
1093 ,p_pymt_stat_ovrdn_flag in varchar2 default hr_api.g_varchar2
1094 ,p_pymt_stat_ovrdn_rsn_cd in varchar2 default hr_api.g_varchar2
1095 ,p_pymt_stat_prr_to_ovrd in varchar2 default hr_api.g_varchar2
1096 ,p_Adjmt_flag in varchar2 default hr_api.g_varchar2
1097 ,p_Submtd_dt in date default hr_api.g_date
1098 ,p_Ttl_rqst_amt in number default hr_api.g_number
1099 ,p_Aprvd_for_pymt_amt in out nocopy number
1100 ,p_exp_incurd_dt in date default hr_api.g_date
1101 ) is
1102 -- DECLARE CURSORS
1103 --- Select old information to find the status
1104 Cursor c_prcold is
1105 select prc.*
1106 From ben_prtt_reimbmt_rqst_f prc
1107 where prc.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
1108 and p_effective_date between
1109 prc.effective_start_date and
1110 prc.effective_end_date;
1111 l_prc_old c_prcold%rowtype ;
1112 --
1113 -- bug fix 2223214
1114 --
1115 l_prtt_rmt_aprvd_pymt_id number;
1116 l_pry_ovn number;
1117 l_pry_eff_strt_dt date;
1118 l_pry_eff_end_dt date;
1119 --
1120 cursor c_pry is
1121 select pry.prtt_rmt_aprvd_fr_pymt_id
1122 , pry.object_version_number
1123 from ben_prtt_rmt_aprvd_fr_pymt_f pry
1124 where pry.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id;
1125 --
1126 -- end fix 2223214
1127 --
1128
1129 -- Declare cursors and local variables
1130
1131 l_prtt_rmt_aprvd_fr_pymt_id number ;
1132 l_proc varchar2(72) := g_package||'update_PRTT_REIMBMT_RQST';
1133 l_object_version_number ben_prtt_reimbmt_rqst_f.object_version_number%TYPE;
1134 l_object_version_number_pymt ben_prtt_reimbmt_rqst_f.object_version_number%TYPE;
1135 l_effective_start_date ben_prtt_reimbmt_rqst_f.effective_start_date%TYPE;
1136 l_effective_end_date ben_prtt_reimbmt_rqst_f.effective_end_date%TYPE;
1137 l_effective_start_date_pymt ben_prtt_reimbmt_rqst_f.effective_start_date%TYPE;
1138 l_effective_end_date_pymt ben_prtt_reimbmt_rqst_f.effective_end_date%TYPE;
1139 --
1140 l_dummy_number number;
1141 l_pymt_amount number ;
1142 l_popl_yr_perd_id_1 number := p_popl_yr_perd_id_1;
1143 l_popl_yr_perd_id_2 number := p_popl_yr_perd_id_2;
1144
1145 --
1146 begin
1147 --
1148 hr_utility.set_location('Entering:'|| l_proc, 10);
1149 --
1150 -- Issue a savepoint if operating in validation only mode
1151 --
1152 savepoint update_PRTT_REIMBMT_RQST;
1153 --
1154 hr_utility.set_location(l_proc, 20);
1155 --
1156 -- Process Logic
1157 ----Get all before update data of reimbursement
1158 open c_prcold ;
1159 fetch c_prcold into l_prc_old ;
1160 close c_prcold ;
1161 --
1162 l_object_version_number := p_object_version_number;
1163 --
1164 if p_exp_incurd_dt <> hr_api.g_date then
1165 find_popl_yr_perd (p_pl_id => p_pl_id,
1166 p_business_group_id => p_business_group_id,
1167 p_effective_date => l_prc_old.SUBMTD_DT,
1168 p_exp_incurd_dt => p_exp_incurd_dt,
1169 p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1,
1170 p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2);
1171 end if;
1172 --
1173 begin
1174 --
1175 -- Start of API User Hook for the before hook of update_PRTT_REIMBMT_RQST
1176 --
1177 ben_PRTT_REIMBMT_RQST_bk2.update_PRTT_REIMBMT_RQST_b
1178 (
1179 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1180 ,p_incrd_from_dt => p_incrd_from_dt
1181 ,p_incrd_to_dt => p_incrd_to_dt
1182 ,p_rqst_num => p_rqst_num
1183 ,p_rqst_amt => p_rqst_amt
1184 ,p_rqst_amt_uom => p_rqst_amt_uom
1185 ,p_rqst_btch_num => p_rqst_btch_num
1186 ,p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd
1187 ,p_reimbmt_ctfn_typ_prvdd_cd => p_reimbmt_ctfn_typ_prvdd_cd
1188 ,p_rcrrg_cd => p_rcrrg_cd
1189 ,p_submitter_person_id => p_submitter_person_id
1190 ,p_recipient_person_id => p_recipient_person_id
1191 ,p_provider_person_id => p_provider_person_id
1192 ,p_provider_ssn_person_id => p_provider_ssn_person_id
1193 ,p_pl_id => p_pl_id
1194 ,p_gd_or_svc_typ_id => p_gd_or_svc_typ_id
1195 ,p_contact_relationship_id => p_contact_relationship_id
1196 ,p_business_group_id => p_business_group_id
1197 ,p_opt_id => p_opt_id
1198 ,p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1
1199 ,p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2
1200 ,p_amt_year1 => p_amt_year1
1201 ,p_amt_year2 => p_amt_year2
1202 ,p_prc_attribute_category => p_prc_attribute_category
1203 ,p_prc_attribute1 => p_prc_attribute1
1204 ,p_prc_attribute2 => p_prc_attribute2
1205 ,p_prc_attribute3 => p_prc_attribute3
1206 ,p_prc_attribute4 => p_prc_attribute4
1207 ,p_prc_attribute5 => p_prc_attribute5
1208 ,p_prc_attribute6 => p_prc_attribute6
1209 ,p_prc_attribute7 => p_prc_attribute7
1210 ,p_prc_attribute8 => p_prc_attribute8
1211 ,p_prc_attribute9 => p_prc_attribute9
1212 ,p_prc_attribute10 => p_prc_attribute10
1213 ,p_prc_attribute11 => p_prc_attribute11
1214 ,p_prc_attribute12 => p_prc_attribute12
1215 ,p_prc_attribute13 => p_prc_attribute13
1216 ,p_prc_attribute14 => p_prc_attribute14
1217 ,p_prc_attribute15 => p_prc_attribute15
1218 ,p_prc_attribute16 => p_prc_attribute16
1219 ,p_prc_attribute17 => p_prc_attribute17
1220 ,p_prc_attribute18 => p_prc_attribute18
1221 ,p_prc_attribute19 => p_prc_attribute19
1222 ,p_prc_attribute20 => p_prc_attribute20
1223 ,p_prc_attribute21 => p_prc_attribute21
1224 ,p_prc_attribute22 => p_prc_attribute22
1225 ,p_prc_attribute23 => p_prc_attribute23
1226 ,p_prc_attribute24 => p_prc_attribute24
1227 ,p_prc_attribute25 => p_prc_attribute25
1228 ,p_prc_attribute26 => p_prc_attribute26
1229 ,p_prc_attribute27 => p_prc_attribute27
1230 ,p_prc_attribute28 => p_prc_attribute28
1231 ,p_prc_attribute29 => p_prc_attribute29
1232 ,p_prc_attribute30 => p_prc_attribute30
1233 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1234 ,p_comment_id => p_comment_id
1235 ,p_object_version_number => p_object_version_number
1236 ,p_effective_date => trunc(p_effective_date)
1237 ,P_STAT_RSN_CD => P_STAT_RSN_CD
1238 ,p_Pymt_stat_cd => p_Pymt_stat_cd
1239 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
1240 ,p_stat_ovrdn_flag => p_stat_ovrdn_flag
1241 ,p_stat_ovrdn_rsn_cd => p_stat_ovrdn_rsn_cd
1242 ,p_stat_prr_to_ovrd => p_stat_prr_to_ovrd
1243 ,p_pymt_stat_ovrdn_flag => p_pymt_stat_ovrdn_flag
1244 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
1245 ,p_pymt_stat_prr_to_ovrd => p_pymt_stat_prr_to_ovrd
1246 ,p_Adjmt_flag => p_Adjmt_flag
1247 ,p_Submtd_dt => trunc(p_Submtd_dt)
1248 ,p_Ttl_rqst_amt => p_Ttl_rqst_amt
1249 ,p_Aprvd_for_pymt_amt => p_Aprvd_for_pymt_amt
1250 ,p_exp_incurd_dt => p_exp_incurd_dt
1251 );
1252 exception
1253 when hr_api.cannot_find_prog_unit then
1254 hr_api.cannot_find_prog_unit_error
1255 (p_module_name => 'UPDATE_PRTT_REIMBMT_RQST'
1256 ,p_hook_type => 'BP'
1257 );
1258 --
1259 -- End of API User Hook for the before hook of update_PRTT_REIMBMT_RQST
1260 --
1261 end;
1262 --
1263 ben_prc_upd.upd
1264 (
1265 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1266 ,p_effective_start_date => l_effective_start_date
1267 ,p_effective_end_date => l_effective_end_date
1268 ,p_incrd_from_dt => p_incrd_from_dt
1269 ,p_incrd_to_dt => p_incrd_to_dt
1270 ,p_rqst_num => p_rqst_num
1271 ,p_rqst_amt => p_rqst_amt
1272 ,p_rqst_amt_uom => p_rqst_amt_uom
1273 ,p_rqst_btch_num => p_rqst_btch_num
1274 ,p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd
1275 ,p_reimbmt_ctfn_typ_prvdd_cd => p_reimbmt_ctfn_typ_prvdd_cd
1276 ,p_rcrrg_cd => p_rcrrg_cd
1277 ,p_submitter_person_id => p_submitter_person_id
1278 ,p_recipient_person_id => p_recipient_person_id
1279 ,p_provider_person_id => p_provider_person_id
1280 ,p_provider_ssn_person_id => p_provider_ssn_person_id
1281 ,p_pl_id => p_pl_id
1282 ,p_gd_or_svc_typ_id => p_gd_or_svc_typ_id
1283 ,p_contact_relationship_id => p_contact_relationship_id
1284 ,p_business_group_id => p_business_group_id
1285 ,p_opt_id => p_opt_id
1286 ,p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1
1287 ,p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2
1288 ,p_amt_year1 => p_amt_year1
1289 ,p_amt_year2 => p_amt_year2
1290 ,p_prc_attribute_category => p_prc_attribute_category
1291 ,p_prc_attribute1 => p_prc_attribute1
1292 ,p_prc_attribute2 => p_prc_attribute2
1293 ,p_prc_attribute3 => p_prc_attribute3
1294 ,p_prc_attribute4 => p_prc_attribute4
1295 ,p_prc_attribute5 => p_prc_attribute5
1296 ,p_prc_attribute6 => p_prc_attribute6
1297 ,p_prc_attribute7 => p_prc_attribute7
1298 ,p_prc_attribute8 => p_prc_attribute8
1299 ,p_prc_attribute9 => p_prc_attribute9
1300 ,p_prc_attribute10 => p_prc_attribute10
1301 ,p_prc_attribute11 => p_prc_attribute11
1302 ,p_prc_attribute12 => p_prc_attribute12
1303 ,p_prc_attribute13 => p_prc_attribute13
1304 ,p_prc_attribute14 => p_prc_attribute14
1305 ,p_prc_attribute15 => p_prc_attribute15
1306 ,p_prc_attribute16 => p_prc_attribute16
1307 ,p_prc_attribute17 => p_prc_attribute17
1308 ,p_prc_attribute18 => p_prc_attribute18
1309 ,p_prc_attribute19 => p_prc_attribute19
1310 ,p_prc_attribute20 => p_prc_attribute20
1311 ,p_prc_attribute21 => p_prc_attribute21
1312 ,p_prc_attribute22 => p_prc_attribute22
1313 ,p_prc_attribute23 => p_prc_attribute23
1314 ,p_prc_attribute24 => p_prc_attribute24
1315 ,p_prc_attribute25 => p_prc_attribute25
1316 ,p_prc_attribute26 => p_prc_attribute26
1317 ,p_prc_attribute27 => p_prc_attribute27
1318 ,p_prc_attribute28 => p_prc_attribute28
1319 ,p_prc_attribute29 => p_prc_attribute29
1320 ,p_prc_attribute30 => p_prc_attribute30
1321 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1322 ,p_comment_id => p_comment_id
1323 ,p_object_version_number => l_object_version_number
1324 ,p_effective_date => trunc(p_effective_date)
1325 ,p_datetrack_mode => p_datetrack_mode
1326 ,P_STAT_RSN_CD => P_STAT_RSN_CD
1327 ,p_Pymt_stat_cd => p_Pymt_stat_cd
1328 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
1329 ,p_stat_ovrdn_flag => p_stat_ovrdn_flag
1330 ,p_stat_ovrdn_rsn_cd => p_stat_ovrdn_rsn_cd
1331 ,p_stat_prr_to_ovrd => p_stat_prr_to_ovrd
1332 ,p_pymt_stat_ovrdn_flag => p_pymt_stat_ovrdn_flag
1333 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
1334 ,p_pymt_stat_prr_to_ovrd => p_pymt_stat_prr_to_ovrd
1335 ,p_Adjmt_flag => p_Adjmt_flag
1336 ,p_Submtd_dt => trunc(p_Submtd_dt)
1337 ,p_Ttl_rqst_amt => p_Ttl_rqst_amt
1338 ,p_Aprvd_for_pymt_amt => p_Aprvd_for_pymt_amt
1339 ,p_pymt_amount => l_pymt_amount
1340 ,p_exp_incurd_dt => p_exp_incurd_dt
1341 );
1342 --
1343 --hr_utility.set_location('after upd in api ' || p_prtt_reimbmt_rqst_stat_cd, 110);
1344 --hr_utility.set_location('after ins in api ' || P_STAT_RSN_CD, 110);
1345 --hr_utility.set_location('after ins in api ' || p_Pymt_stat_cd, 110);
1346 --hr_utility.set_location('after ins status ' || p_pymt_stat_rsn_cd, 110);
1347
1348 --
1349 -- bug fix 2223214
1350 -- when the user tries to move from an approved status to any of the unapproved
1351 -- status (taking into consideration that run result values are already checked for
1352 -- in the PLD), we have to delete the reimbursement payment records, rate value records,
1353 -- and element entry and their corresponding value records for this
1354 -- participant reimbursement request which had been earlier approved.
1355 --
1356
1357 if nvl(p_prtt_reimbmt_rqst_stat_cd,'-1') not in ('APPRVD','PDINFL','PRTLYPD') and
1358 nvl(l_prc_old.prtt_reimbmt_rqst_stat_cd,'-1') in ('APPRVD','PDINFL','PRTLYPD') then
1359 --
1360 hr_utility.set_location(l_proc, 30);
1361 --
1362 open c_pry;
1363 loop
1364 --
1365 fetch c_pry into l_prtt_rmt_aprvd_pymt_id
1366 , l_pry_ovn;
1367 --
1368 hr_utility.set_location(l_proc, 40);
1369 hr_utility.set_location(' l_prtt_rmt_aprvd_pymt_id is ' || l_prtt_rmt_aprvd_pymt_id , 999);
1370 --
1371 if c_pry%found then
1372 --
1373 -- delete reimbursement payment records
1374 --
1375 ben_prtt_rmt_aprvd_pymt_api.delete_prtt_rmt_aprvd_pymt
1376 (p_prtt_rmt_aprvd_fr_pymt_id => l_prtt_rmt_aprvd_pymt_id,
1377 p_effective_start_date => l_pry_eff_strt_dt,
1378 p_effective_end_date => l_pry_eff_end_dt,
1379 p_object_version_number => l_pry_ovn,
1380 p_effective_date => p_effective_date,
1381 p_datetrack_mode => 'ZAP'
1382 );
1383 --
1384 else
1385 --
1386 exit;
1387 --
1388 end if;
1389 --
1390 end loop;
1391 close c_pry;
1392 --
1393 hr_utility.set_location(l_proc, 50);
1394 --
1395 end if;
1396
1397 --
1398 -- end fix 2223214
1399 --
1400
1401 hr_utility.set_location(' Payable Amount in api '|| l_pymt_amount, 110);
1402 if nvl(p_prtt_reimbmt_rqst_stat_cd,'-1') in ('APPRVD','PDINFL','PRTLYPD') and
1403 nvl(l_prc_old.prtt_reimbmt_rqst_stat_cd,'-1') not in ('APPRVD','PDINFL','PRTLYPD') then
1404 --bug#5527088
1405 find_pymt_amt ( p_effective_date => p_effective_date,
1406 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
1407 p_business_group_id => p_business_group_id,
1408 p_pymt_amt => l_pymt_amount);
1409
1410 if nvl(l_pymt_amount,0) <> 0 then
1411 -- Once the Amount is approved create the Payment
1412 ben_prtt_rmt_aprvd_pymt_api.create_prtt_rmt_aprvd_pymt
1413 (p_validate => false
1414 ,p_prtt_rmt_aprvd_fr_pymt_id => l_prtt_rmt_aprvd_fr_pymt_id
1415 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1416 ,p_effective_start_date => l_effective_start_date_pymt
1417 ,p_effective_end_date => l_effective_end_date_pymt
1418 ,p_adjmt_flag => 'N'
1419 ,p_aprvd_fr_pymt_amt => l_pymt_amount
1420 ,p_business_group_id => p_business_group_id
1421 ,p_object_version_number => l_object_version_number_pymt
1422 ,p_effective_date => p_effective_date
1423 );
1424 end if ;
1425 --
1426 end if;
1427
1428 begin
1429 --
1430 -- Start of API User Hook for the after hook of update_PRTT_REIMBMT_RQST
1431 --
1432 ben_PRTT_REIMBMT_RQST_bk2.update_PRTT_REIMBMT_RQST_a
1433 (
1434 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1435 ,p_effective_start_date => l_effective_start_date
1436 ,p_effective_end_date => l_effective_end_date
1437 ,p_incrd_from_dt => p_incrd_from_dt
1438 ,p_incrd_to_dt => p_incrd_to_dt
1439 ,p_rqst_num => p_rqst_num
1440 ,p_rqst_amt => p_rqst_amt
1441 ,p_rqst_amt_uom => p_rqst_amt_uom
1442 ,p_rqst_btch_num => p_rqst_btch_num
1443 ,p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd
1444 ,p_reimbmt_ctfn_typ_prvdd_cd => p_reimbmt_ctfn_typ_prvdd_cd
1445 ,p_rcrrg_cd => p_rcrrg_cd
1446 ,p_submitter_person_id => p_submitter_person_id
1447 ,p_recipient_person_id => p_recipient_person_id
1448 ,p_provider_person_id => p_provider_person_id
1449 ,p_provider_ssn_person_id => p_provider_ssn_person_id
1450 ,p_pl_id => p_pl_id
1451 ,p_gd_or_svc_typ_id => p_gd_or_svc_typ_id
1452 ,p_contact_relationship_id => p_contact_relationship_id
1453 ,p_business_group_id => p_business_group_id
1454 ,p_opt_id => p_opt_id
1455 ,p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1
1456 ,p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2
1457 ,p_amt_year1 => p_amt_year1
1458 ,p_amt_year2 => p_amt_year2
1459 ,p_prc_attribute_category => p_prc_attribute_category
1460 ,p_prc_attribute1 => p_prc_attribute1
1461 ,p_prc_attribute2 => p_prc_attribute2
1462 ,p_prc_attribute3 => p_prc_attribute3
1463 ,p_prc_attribute4 => p_prc_attribute4
1464 ,p_prc_attribute5 => p_prc_attribute5
1465 ,p_prc_attribute6 => p_prc_attribute6
1466 ,p_prc_attribute7 => p_prc_attribute7
1467 ,p_prc_attribute8 => p_prc_attribute8
1468 ,p_prc_attribute9 => p_prc_attribute9
1469 ,p_prc_attribute10 => p_prc_attribute10
1470 ,p_prc_attribute11 => p_prc_attribute11
1471 ,p_prc_attribute12 => p_prc_attribute12
1472 ,p_prc_attribute13 => p_prc_attribute13
1473 ,p_prc_attribute14 => p_prc_attribute14
1474 ,p_prc_attribute15 => p_prc_attribute15
1475 ,p_prc_attribute16 => p_prc_attribute16
1476 ,p_prc_attribute17 => p_prc_attribute17
1477 ,p_prc_attribute18 => p_prc_attribute18
1478 ,p_prc_attribute19 => p_prc_attribute19
1479 ,p_prc_attribute20 => p_prc_attribute20
1480 ,p_prc_attribute21 => p_prc_attribute21
1481 ,p_prc_attribute22 => p_prc_attribute22
1482 ,p_prc_attribute23 => p_prc_attribute23
1483 ,p_prc_attribute24 => p_prc_attribute24
1484 ,p_prc_attribute25 => p_prc_attribute25
1485 ,p_prc_attribute26 => p_prc_attribute26
1486 ,p_prc_attribute27 => p_prc_attribute27
1487 ,p_prc_attribute28 => p_prc_attribute28
1488 ,p_prc_attribute29 => p_prc_attribute29
1489 ,p_prc_attribute30 => p_prc_attribute30
1490 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1491 ,p_comment_id => p_comment_id
1492 ,p_object_version_number => l_object_version_number
1493 ,p_effective_date => trunc(p_effective_date)
1494 ,p_datetrack_mode => p_datetrack_mode
1495 ,P_STAT_RSN_CD => P_STAT_RSN_CD
1496 ,p_Pymt_stat_cd => p_Pymt_stat_cd
1497 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
1498 ,p_stat_ovrdn_flag => p_stat_ovrdn_flag
1499 ,p_stat_ovrdn_rsn_cd => p_stat_ovrdn_rsn_cd
1500 ,p_stat_prr_to_ovrd => p_stat_prr_to_ovrd
1501 ,p_pymt_stat_ovrdn_flag => p_pymt_stat_ovrdn_flag
1502 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
1503 ,p_pymt_stat_prr_to_ovrd => p_pymt_stat_prr_to_ovrd
1504 ,p_Adjmt_flag => p_Adjmt_flag
1505 ,p_Submtd_dt => trunc(p_Submtd_dt)
1506 ,p_Ttl_rqst_amt => p_Ttl_rqst_amt
1507 ,p_Aprvd_for_pymt_amt => p_Aprvd_for_pymt_amt
1508 ,p_exp_incurd_dt => p_exp_incurd_dt
1509 );
1510 exception
1511 when hr_api.cannot_find_prog_unit then
1512 hr_api.cannot_find_prog_unit_error
1513 (p_module_name => 'UPDATE_PRTT_REIMBMT_RQST'
1514 ,p_hook_type => 'AP'
1515 );
1516 --
1517 -- End of API User Hook for the after hook of update_PRTT_REIMBMT_RQST
1518 --
1519 end;
1520 --
1521 ---Call The communication when the status us changed
1522 --if p_prtt_reimbmt_rqst_stat_cd is not null and
1523 -- nvl(l_prc_old.prtt_reimbmt_rqst_stat_cd,'-1') <> p_prtt_reimbmt_rqst_stat_cd
1524 -- then
1525
1526 generate_communications(
1527 p_submitter_person_id => p_submitter_person_id,
1528 p_pl_id => p_pl_id,
1529 p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd,
1530 p_business_group_id => p_business_group_id,
1531 p_effective_date => p_effective_date);
1532 --end if ;
1533 --
1534 hr_utility.set_location(l_proc, 60);
1535 --
1536 -- When in validation only mode raise the Validate_Enabled exception
1537 --
1538 if p_validate then
1539 raise hr_api.validate_enabled;
1540 end if;
1541 --
1542 -- Set all output arguments
1543 --
1544 p_object_version_number := l_object_version_number;
1545 p_effective_start_date := l_effective_start_date;
1546 p_effective_end_date := l_effective_end_date;
1547 --
1548 hr_utility.set_location(' Leaving:'||l_proc, 70);
1549 --
1550 exception
1551 --
1552 when hr_api.validate_enabled then
1553 --
1554 -- As the Validate_Enabled exception has been raised
1555 -- we must rollback to the savepoint
1556 --
1557 ROLLBACK TO update_PRTT_REIMBMT_RQST;
1558 --
1559 -- Only set output warning arguments
1560 -- (Any key or derived arguments must be set to null
1561 -- when validation only mode is being used.)
1562 --
1563 hr_utility.set_location(' Leaving:'||l_proc, 80);
1564 --
1565 when others then
1566 --
1567 -- A validation or unexpected error has occured
1568 --
1569 ROLLBACK TO update_PRTT_REIMBMT_RQST;
1570 raise;
1571 --
1572 end update_PRTT_REIMBMT_RQST;
1573 -- ----------------------------------------------------------------------------
1574 -- |------------------------< delete_PRTT_REIMBMT_RQST >----------------------|
1575 -- ----------------------------------------------------------------------------
1576 --
1577 procedure delete_PRTT_REIMBMT_RQST
1578 (p_validate in boolean default false
1579 ,p_prtt_reimbmt_rqst_id in number
1580 ,p_effective_start_date out nocopy date
1581 ,p_effective_end_date out nocopy date
1582 ,p_object_version_number in out nocopy number
1583 ,p_effective_date in date
1584 ,p_datetrack_mode in varchar2
1585 ,p_submitter_person_id in number
1586 ) is
1587 --
1588 -- Declare cursors and local variables
1589 --
1590
1591 l_proc varchar2(72) := g_package||'delete_PRTT_REIMBMT_RQST';
1592 l_object_version_number ben_prtt_reimbmt_rqst_f.object_version_number%TYPE;
1593 l_effective_start_date ben_prtt_reimbmt_rqst_f.effective_start_date%TYPE;
1594 l_effective_end_date ben_prtt_reimbmt_rqst_f.effective_end_date%TYPE;
1595
1596 --
1597 begin
1598 --
1599 hr_utility.set_location('Entering:'|| l_proc, 10);
1600 --
1601 -- Issue a savepoint if operating in validation only mode
1602 --
1603 savepoint delete_PRTT_REIMBMT_RQST;
1604 --
1605 hr_utility.set_location(l_proc, 20);
1606 --
1607 -- Process Logic
1608 --
1609 l_object_version_number := p_object_version_number;
1610 --
1611 --
1612 begin
1613 --
1614 -- Start of API User Hook for the before hook of delete_PRTT_REIMBMT_RQST
1615 --
1616 ben_PRTT_REIMBMT_RQST_bk3.delete_PRTT_REIMBMT_RQST_b
1617 (
1618 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1619 ,p_object_version_number => p_object_version_number
1620 ,p_effective_date => trunc(p_effective_date)
1621 ,p_datetrack_mode => p_datetrack_mode
1622 );
1623 exception
1624 when hr_api.cannot_find_prog_unit then
1625 hr_api.cannot_find_prog_unit_error
1626 (p_module_name => 'DELETE_PRTT_REIMBMT_RQST'
1627 ,p_hook_type => 'BP'
1628 );
1629 --
1630 -- End of API User Hook for the before hook of delete_PRTT_REIMBMT_RQST
1631 --
1632 end;
1633 --
1634
1635 ben_prc_del.del
1636 (
1637 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1638 ,p_effective_start_date => l_effective_start_date
1639 ,p_effective_end_date => l_effective_end_date
1640 ,p_object_version_number => l_object_version_number
1641 ,p_effective_date => p_effective_date
1642 ,p_datetrack_mode => p_datetrack_mode
1643 );
1644 --
1645 begin
1646 --
1647 -- Start of API User Hook for the after hook of delete_PRTT_REIMBMT_RQST
1648 --
1649 ben_PRTT_REIMBMT_RQST_bk3.delete_PRTT_REIMBMT_RQST_a
1650 (
1651 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1652 ,p_effective_start_date => l_effective_start_date
1653 ,p_effective_end_date => l_effective_end_date
1654 ,p_object_version_number => l_object_version_number
1655 ,p_effective_date => trunc(p_effective_date)
1656 ,p_datetrack_mode => p_datetrack_mode
1657 );
1658 exception
1659 when hr_api.cannot_find_prog_unit then
1660 hr_api.cannot_find_prog_unit_error
1661 (p_module_name => 'DELETE_PRTT_REIMBMT_RQST'
1662 ,p_hook_type => 'AP'
1663 );
1664 --
1665 -- End of API User Hook for the after hook of delete_PRTT_REIMBMT_RQST
1666 --
1667 end;
1668 --
1669 hr_utility.set_location(l_proc, 60);
1670 -- When in validation only mode raise the Validate_Enabled exception
1671 --
1672 if p_validate then
1673 raise hr_api.validate_enabled;
1674 end if;
1675 --
1676 hr_utility.set_location(' Leaving:'||l_proc, 70);
1677 --
1678 exception
1679 --
1680 when hr_api.validate_enabled then
1681 --
1682 -- As the Validate_Enabled exception has been raised
1683 -- we must rollback to the savepoint
1684 --
1685 ROLLBACK TO delete_PRTT_REIMBMT_RQST;
1686 --
1687 -- Only set output warning arguments
1688 -- (Any key or derived arguments must be set to null
1689 -- when validation only mode is being used.)
1690 --
1691 p_effective_start_date := null;
1692 p_effective_end_date := null;
1693 --
1694 when others then
1695 --
1696 -- A validation or unexpected error has occured
1697 --
1698 ROLLBACK TO delete_PRTT_REIMBMT_RQST;
1699 raise;
1700 --
1701 end delete_PRTT_REIMBMT_RQST;
1702 --
1703 -- ----------------------------------------------------------------------------
1704 -- |-------------------------------< lck >------------------------------------|
1705 -- ----------------------------------------------------------------------------
1706 --
1707 procedure lck
1708 (
1709 p_prtt_reimbmt_rqst_id in number
1710 ,p_object_version_number in number
1711 ,p_effective_date in date
1712 ,p_datetrack_mode in varchar2
1713 ,p_validation_start_date out nocopy date
1714 ,p_validation_end_date out nocopy date
1715 ) is
1716 --
1717 --
1718 -- Declare cursors and local variables
1719 --
1720 l_proc varchar2(72) := g_package||'lck';
1721 l_validation_start_date date;
1722 l_validation_end_date date;
1723 --
1724 begin
1725 --
1726 hr_utility.set_location('Entering:'|| l_proc, 10);
1727 --
1728 ben_prc_shd.lck
1729 (
1730 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
1731 ,p_validation_start_date => l_validation_start_date
1732 ,p_validation_end_date => l_validation_end_date
1733 ,p_object_version_number => p_object_version_number
1734 ,p_effective_date => p_effective_date
1735 ,p_datetrack_mode => p_datetrack_mode
1736 );
1737 --
1738 hr_utility.set_location(' Leaving:'||l_proc, 70);
1739 --
1740 end lck;
1741 --
1742 end ben_PRTT_REIMBMT_RQST_api;