[Home] [Help]
PACKAGE BODY: APPS.BEN_PRTT_RMT_APRVD_PYMT_API
Source
1 Package Body ben_prtt_rmt_aprvd_pymt_api as
2 /* $Header: bepryapi.pkb 120.4 2008/02/22 16:21:15 rtagarra noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_prtt_rmt_aprvd_pymt_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_prtt_rmt_aprvd_pymt >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_prtt_rmt_aprvd_pymt
13 (p_validate in boolean default false
14 ,p_prtt_rmt_aprvd_fr_pymt_id out nocopy number
15 ,p_prtt_reimbmt_rqst_id in number default null
16 ,p_effective_start_date out nocopy date
17 ,p_effective_end_date out nocopy date
18 ,p_apprvd_fr_pymt_num in number default null
19 ,p_adjmt_flag in varchar2 default null
20 ,p_aprvd_fr_pymt_amt in number default null
21 ,p_pymt_stat_cd in varchar2 default null
22 ,p_pymt_stat_rsn_cd in varchar2 default null
23 ,p_pymt_stat_ovrdn_rsn_cd in varchar2 default null
24 ,p_pymt_stat_prr_to_ovrd_cd in varchar2 default null
25 ,p_business_group_id in number default null
26 ,p_element_entry_value_id in number default null
27 ,p_pry_attribute_category in varchar2 default null
28 ,p_pry_attribute1 in varchar2 default null
29 ,p_pry_attribute2 in varchar2 default null
30 ,p_pry_attribute3 in varchar2 default null
31 ,p_pry_attribute4 in varchar2 default null
32 ,p_pry_attribute5 in varchar2 default null
33 ,p_pry_attribute6 in varchar2 default null
34 ,p_pry_attribute7 in varchar2 default null
35 ,p_pry_attribute8 in varchar2 default null
36 ,p_pry_attribute9 in varchar2 default null
37 ,p_pry_attribute10 in varchar2 default null
38 ,p_pry_attribute11 in varchar2 default null
39 ,p_pry_attribute12 in varchar2 default null
40 ,p_pry_attribute13 in varchar2 default null
41 ,p_pry_attribute14 in varchar2 default null
42 ,p_pry_attribute15 in varchar2 default null
43 ,p_pry_attribute16 in varchar2 default null
44 ,p_pry_attribute17 in varchar2 default null
45 ,p_pry_attribute18 in varchar2 default null
46 ,p_pry_attribute19 in varchar2 default null
47 ,p_pry_attribute20 in varchar2 default null
48 ,p_pry_attribute21 in varchar2 default null
49 ,p_pry_attribute22 in varchar2 default null
50 ,p_pry_attribute23 in varchar2 default null
51 ,p_pry_attribute24 in varchar2 default null
52 ,p_pry_attribute25 in varchar2 default null
53 ,p_pry_attribute26 in varchar2 default null
54 ,p_pry_attribute27 in varchar2 default null
55 ,p_pry_attribute28 in varchar2 default null
56 ,p_pry_attribute29 in varchar2 default null
57 ,p_pry_attribute30 in varchar2 default null
58 ,p_object_version_number out nocopy number
59 ,p_effective_date in date
60 ) is
61 --
62
63
64 cursor c_pln
65 is
66 select pln.nip_acty_ref_perd_cd
67 ,pln.pl_id
68 ,prc.prtt_enrt_rslt_id
69 ,prc.submitter_person_id
70 ,prc.incrd_from_dt
71 ,prc.incrd_to_dt
72 ,prc.exp_incurd_dt
73 from ben_pl_f pln ,
74 ben_prtt_reimbmt_rqst_f prc
75 where pln.pl_id = prc.pl_id
76 and prc.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
77 and p_effective_date
78 between prc.effective_start_date
79 and prc.effective_end_date
80 and p_effective_date
81 between pln.effective_start_date
82 and pln.effective_end_date;
83
84 Cursor c_rslt_rec (p_prtt_enrt_rslt_id number ,
85 p_incrd_from_dt date ,
86 p_incrd_to_dt date ) is
87 select pen.pgm_id,
88 pen.per_in_ler_id
89 from ben_prtt_enrt_rslt_f pen
90 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
91 and pen.prtt_enrt_rslt_stat_cd is null
92 and pen.business_group_id = p_business_group_id
93 and p_effective_date between
94 pen.effective_start_date and pen.effective_end_date
95 and p_incrd_from_dt <= pen.enrt_cvg_thru_dt
96 and p_incrd_to_dt >= pen.enrt_cvg_strt_dt
97 ;
98 --
99 l_rslt_rec c_rslt_rec%rowtype;
100 --
101
102 cursor c_abr_pl(p_pl_id number)
103 is
104 select abr.acty_base_rt_id ,
105 abr.rt_typ_cd,
106 abr.tx_typ_cd,
107 abr.acty_typ_cd,
108 abr.rt_mlt_cd,
109 abr.bnft_rt_typ_cd,
110 abr.dsply_on_enrt_flag,
111 abr.comp_lvl_fctr_id,
112 abr.actl_prem_id,
113 abr.input_value_id,
114 abr.element_type_id
115 from ben_acty_base_rt_f abr
116 where pl_id = p_pl_id
117 and acty_typ_cd like 'PRD%'
118 and acty_base_rt_stat_cd = 'A'
119 and p_effective_date between
120 abr.effective_start_date and
121 abr.effective_end_date;
122
123
124 cursor c_abr_plip (p_pl_id number, p_pgm_id number)
125 is
126 select abr.acty_base_rt_id,
127 abr.rt_typ_cd,
128 abr.tx_typ_cd,
129 abr.acty_typ_cd,
130 abr.rt_mlt_cd,
131 abr.bnft_rt_typ_cd,
132 abr.dsply_on_enrt_flag,
133 abr.comp_lvl_fctr_id,
134 abr.actl_prem_id,
135 abr.input_value_id,
136 abr.element_type_id
137 from ben_acty_base_rt_f abr,
138 ben_plip_f plp
139 where plp.pl_id = p_pl_id
140 and plp.pgm_id = p_pgm_id
141 and p_effective_date between
142 plp.effective_start_date and
143 plp.effective_end_date
144 and plp.plip_id = abr.plip_id
145 and acty_base_rt_stat_cd = 'A'
146 and abr.acty_typ_cd like 'PRD%'
147 and p_effective_date between
148 abr.effective_start_date and
149 abr.effective_end_date;
150
151 --
152 l_acty_base_rt c_abr_pl%rowtype;
153
154 cursor c_cvg_pl (p_pl_id number)
155 is
156 select ccm.cvg_amt_calc_mthd_id
157 from ben_cvg_amt_calc_mthd_f ccm
158 where pl_id = p_pl_id
159 and p_effective_date
160 between ccm.effective_start_date
161 and ccm.effective_end_date;
162
163 cursor c_pgm
164 (c_pgm_id number)
165 is
166 select pgm.acty_ref_perd_cd
167 from ben_pgm_f pgm
168 where pgm.pgm_id = c_pgm_id
169 and p_effective_date
170 between pgm.effective_start_date
171 and pgm.effective_end_date;
172
173
174 -- Declare cursors and local variables
175 --
176 l_prtt_rmt_aprvd_fr_pymt_id ben_prtt_rmt_aprvd_fr_pymt_f.prtt_rmt_aprvd_fr_pymt_id%TYPE;
177 l_effective_start_date ben_prtt_rmt_aprvd_fr_pymt_f.effective_start_date%TYPE;
178 l_effective_end_date ben_prtt_rmt_aprvd_fr_pymt_f.effective_end_date%TYPE;
179 l_proc varchar2(72) := g_package||'create_prtt_rmt_aprvd_pymt';
180 l_object_version_number ben_prtt_rmt_aprvd_fr_pymt_f.object_version_number%TYPE;
181 --
182 l_prtt_rt_val_id number;
183 l_prtt_enrt_rslt_id number;
184 l_object_version_number_prt ben_prtt_rmt_aprvd_fr_pymt_f.object_version_number%TYPE;
185 l_acty_ref_perd_cd varchar2(30);
186 l_pl_id number ;
187 l_cvg_amt_calc_mthd_id number ;
188 l_submitter_person_id number ;
189 l_incrd_from_dt date ;
190 l_incrd_to_dt date ;
191 l_exp_incurd_dt date ;
192
193 begin
194 --
195 hr_utility.set_location('Entering:'|| l_proc, 10);
196 --
197 -- Issue a savepoint if operating in validation only mode
198 --
199 savepoint create_prtt_rmt_aprvd_pymt;
200 --
201 hr_utility.set_location(l_proc, 20);
202 --
203 -- Process Logic
204 --
205 begin
206 --
207 -- Start of API User Hook for the before hook of create_prtt_rmt_aprvd_pymt
208 --
209 ben_prtt_rmt_aprvd_pymt_bk1.create_prtt_rmt_aprvd_pymt_b
210 (
211 p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
212 ,p_apprvd_fr_pymt_num => p_apprvd_fr_pymt_num
213 ,p_adjmt_flag => p_adjmt_flag
214 ,p_aprvd_fr_pymt_amt => p_aprvd_fr_pymt_amt
215 ,p_pymt_stat_cd => p_pymt_stat_cd
216 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
217 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
218 ,p_pymt_stat_prr_to_ovrd_cd => p_pymt_stat_prr_to_ovrd_cd
219 ,p_business_group_id => p_business_group_id
220 ,p_element_entry_value_id => p_element_entry_value_id
221 ,p_pry_attribute_category => p_pry_attribute_category
222 ,p_pry_attribute1 => p_pry_attribute1
223 ,p_pry_attribute2 => p_pry_attribute2
224 ,p_pry_attribute3 => p_pry_attribute3
225 ,p_pry_attribute4 => p_pry_attribute4
226 ,p_pry_attribute5 => p_pry_attribute5
227 ,p_pry_attribute6 => p_pry_attribute6
228 ,p_pry_attribute7 => p_pry_attribute7
229 ,p_pry_attribute8 => p_pry_attribute8
230 ,p_pry_attribute9 => p_pry_attribute9
231 ,p_pry_attribute10 => p_pry_attribute10
232 ,p_pry_attribute11 => p_pry_attribute11
233 ,p_pry_attribute12 => p_pry_attribute12
234 ,p_pry_attribute13 => p_pry_attribute13
235 ,p_pry_attribute14 => p_pry_attribute14
236 ,p_pry_attribute15 => p_pry_attribute15
237 ,p_pry_attribute16 => p_pry_attribute16
238 ,p_pry_attribute17 => p_pry_attribute17
239 ,p_pry_attribute18 => p_pry_attribute18
240 ,p_pry_attribute19 => p_pry_attribute19
241 ,p_pry_attribute20 => p_pry_attribute20
242 ,p_pry_attribute21 => p_pry_attribute21
243 ,p_pry_attribute22 => p_pry_attribute22
244 ,p_pry_attribute23 => p_pry_attribute23
245 ,p_pry_attribute24 => p_pry_attribute24
246 ,p_pry_attribute25 => p_pry_attribute25
247 ,p_pry_attribute26 => p_pry_attribute26
248 ,p_pry_attribute27 => p_pry_attribute27
249 ,p_pry_attribute28 => p_pry_attribute28
250 ,p_pry_attribute29 => p_pry_attribute29
251 ,p_pry_attribute30 => p_pry_attribute30
252 ,p_effective_date => trunc(p_effective_date)
253 );
254 exception
255 when hr_api.cannot_find_prog_unit then
256 hr_api.cannot_find_prog_unit_error
257 (
258 p_module_name => 'CREATE_prtt_rmt_aprvd_pymt'
259 ,p_hook_type => 'BP'
260 );
261 --
262 -- End of API User Hook for the before hook of create_prtt_rmt_aprvd_pymt
263 --
264 end;
265 --
266 ben_pry_ins.ins
267 (
268 p_prtt_rmt_aprvd_fr_pymt_id => l_prtt_rmt_aprvd_fr_pymt_id
269 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
270 ,p_effective_start_date => l_effective_start_date
271 ,p_effective_end_date => l_effective_end_date
272 ,p_apprvd_fr_pymt_num => p_apprvd_fr_pymt_num
273 ,p_adjmt_flag => p_adjmt_flag
274 ,p_aprvd_fr_pymt_amt => p_aprvd_fr_pymt_amt
275 ,p_pymt_stat_cd => p_pymt_stat_cd
276 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
277 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
278 ,p_pymt_stat_prr_to_ovrd_cd => p_pymt_stat_prr_to_ovrd_cd
279 ,p_business_group_id => p_business_group_id
280 ,p_element_entry_value_id => p_element_entry_value_id
281 ,p_pry_attribute_category => p_pry_attribute_category
282 ,p_pry_attribute1 => p_pry_attribute1
283 ,p_pry_attribute2 => p_pry_attribute2
284 ,p_pry_attribute3 => p_pry_attribute3
285 ,p_pry_attribute4 => p_pry_attribute4
286 ,p_pry_attribute5 => p_pry_attribute5
287 ,p_pry_attribute6 => p_pry_attribute6
288 ,p_pry_attribute7 => p_pry_attribute7
289 ,p_pry_attribute8 => p_pry_attribute8
290 ,p_pry_attribute9 => p_pry_attribute9
291 ,p_pry_attribute10 => p_pry_attribute10
292 ,p_pry_attribute11 => p_pry_attribute11
293 ,p_pry_attribute12 => p_pry_attribute12
294 ,p_pry_attribute13 => p_pry_attribute13
295 ,p_pry_attribute14 => p_pry_attribute14
296 ,p_pry_attribute15 => p_pry_attribute15
297 ,p_pry_attribute16 => p_pry_attribute16
298 ,p_pry_attribute17 => p_pry_attribute17
299 ,p_pry_attribute18 => p_pry_attribute18
300 ,p_pry_attribute19 => p_pry_attribute19
301 ,p_pry_attribute20 => p_pry_attribute20
302 ,p_pry_attribute21 => p_pry_attribute21
303 ,p_pry_attribute22 => p_pry_attribute22
304 ,p_pry_attribute23 => p_pry_attribute23
305 ,p_pry_attribute24 => p_pry_attribute24
306 ,p_pry_attribute25 => p_pry_attribute25
307 ,p_pry_attribute26 => p_pry_attribute26
308 ,p_pry_attribute27 => p_pry_attribute27
309 ,p_pry_attribute28 => p_pry_attribute28
310 ,p_pry_attribute29 => p_pry_attribute29
311 ,p_pry_attribute30 => p_pry_attribute30
312 ,p_object_version_number => l_object_version_number
313 ,p_effective_date => trunc(p_effective_date)
314 );
315 --
316 begin
317 --
318 -- Start of API User Hook for the after hook of create_prtt_rmt_aprvd_pymt
319 --
320 ben_prtt_rmt_aprvd_pymt_bk1.create_prtt_rmt_aprvd_pymt_a
321 (
322 p_prtt_rmt_aprvd_fr_pymt_id => l_prtt_rmt_aprvd_fr_pymt_id
323 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
324 ,p_effective_start_date => l_effective_start_date
325 ,p_effective_end_date => l_effective_end_date
326 ,p_apprvd_fr_pymt_num => p_apprvd_fr_pymt_num
327 ,p_adjmt_flag => p_adjmt_flag
328 ,p_aprvd_fr_pymt_amt => p_aprvd_fr_pymt_amt
329 ,p_pymt_stat_cd => p_pymt_stat_cd
330 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
331 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
332 ,p_pymt_stat_prr_to_ovrd_cd => p_pymt_stat_prr_to_ovrd_cd
333 ,p_business_group_id => p_business_group_id
334 ,p_element_entry_value_id => p_element_entry_value_id
335 ,p_pry_attribute_category => p_pry_attribute_category
336 ,p_pry_attribute1 => p_pry_attribute1
337 ,p_pry_attribute2 => p_pry_attribute2
338 ,p_pry_attribute3 => p_pry_attribute3
339 ,p_pry_attribute4 => p_pry_attribute4
340 ,p_pry_attribute5 => p_pry_attribute5
341 ,p_pry_attribute6 => p_pry_attribute6
342 ,p_pry_attribute7 => p_pry_attribute7
343 ,p_pry_attribute8 => p_pry_attribute8
344 ,p_pry_attribute9 => p_pry_attribute9
345 ,p_pry_attribute10 => p_pry_attribute10
346 ,p_pry_attribute11 => p_pry_attribute11
347 ,p_pry_attribute12 => p_pry_attribute12
348 ,p_pry_attribute13 => p_pry_attribute13
349 ,p_pry_attribute14 => p_pry_attribute14
350 ,p_pry_attribute15 => p_pry_attribute15
351 ,p_pry_attribute16 => p_pry_attribute16
352 ,p_pry_attribute17 => p_pry_attribute17
353 ,p_pry_attribute18 => p_pry_attribute18
354 ,p_pry_attribute19 => p_pry_attribute19
355 ,p_pry_attribute20 => p_pry_attribute20
356 ,p_pry_attribute21 => p_pry_attribute21
357 ,p_pry_attribute22 => p_pry_attribute22
358 ,p_pry_attribute23 => p_pry_attribute23
359 ,p_pry_attribute24 => p_pry_attribute24
360 ,p_pry_attribute25 => p_pry_attribute25
361 ,p_pry_attribute26 => p_pry_attribute26
362 ,p_pry_attribute27 => p_pry_attribute27
363 ,p_pry_attribute28 => p_pry_attribute28
364 ,p_pry_attribute29 => p_pry_attribute29
365 ,p_pry_attribute30 => p_pry_attribute30
366 ,p_object_version_number => l_object_version_number
367 ,p_effective_date => trunc(p_effective_date)
368 );
369 exception
370 when hr_api.cannot_find_prog_unit then
371 hr_api.cannot_find_prog_unit_error
372 (p_module_name => 'CREATE_prtt_rmt_aprvd_pymt'
373 ,p_hook_type => 'AP'
374 );
375 --
376 -- End of API User Hook for the after hook of create_prtt_rmt_aprvd_pymt
377 --
378 end;
379 --
380 hr_utility.set_location(l_proc, 60);
381 --
382 ----Creating variable to create element entry
383 open c_pln;
384 fetch c_pln into
385 l_acty_ref_perd_cd ,
386 l_pl_id ,
387 l_prtt_enrt_rslt_id,
388 l_submitter_person_id ,
389 l_incrd_from_dt ,
390 l_incrd_to_dt ,
391 l_exp_incurd_dt ;
392 close c_pln;
393
394 open c_rslt_rec(l_prtt_enrt_rslt_id,
395 l_incrd_from_dt ,
396 l_incrd_to_dt ) ;
397 fetch c_rslt_rec into l_rslt_rec;
398 close c_rslt_rec;
399
400 if l_rslt_rec.pgm_id is not null then
401 --
402 hr_utility.set_location('pgm '||l_rslt_rec.pgm_id,100);
403 open c_pgm (l_rslt_rec.pgm_id);
404 fetch c_pgm into l_acty_ref_perd_cd;
405 close c_pgm;
406 --
407 End if ;
408
409
410 ---- chek for reimbursement rate
411 open c_abr_plip(l_pl_id,l_rslt_rec.pgm_id);
412 fetch c_abr_plip into l_acty_base_rt;
413 if c_abr_plip%notfound then
414 open c_abr_pl(l_pl_id) ;
415 fetch c_abr_pl into l_acty_base_rt ;
416 if c_abr_pl%notfound then
417 close c_abr_pl;
418 close c_abr_plip ;
419 fnd_message.set_name('BEN','BEN_92697_NO_REMBMT_RATE');
420 fnd_message.raise_error;
421 end if ;
422 close c_abr_pl;
423 end if;
424 close c_abr_plip;
425
426
427 open c_cvg_pl(l_pl_id ) ;
428 fetch c_cvg_pl into l_cvg_amt_calc_mthd_id;
429 close c_cvg_pl;
430
431 /*
432 ----Creating Element Entry
433 ben_prtt_rt_val_api.create_prtt_rt_val(
434 p_prtt_rt_val_id => l_prtt_rt_val_id
435 ,p_per_in_ler_id => l_rslt_rec.per_in_ler_id
436 ,p_rt_typ_cd => l_acty_base_rt.rt_typ_cd
437 ,p_tx_typ_cd => l_acty_base_rt.tx_typ_cd
438 ,p_acty_typ_cd => l_acty_base_rt.acty_typ_cd
439 ,p_mlt_cd => l_acty_base_rt.rt_mlt_cd
440 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
441 ,p_rt_val => p_aprvd_fr_pymt_amt
442 ,p_rt_strt_dt => l_effective_start_date
443 ,p_rt_end_dt => l_effective_start_date
444 ,p_bnft_rt_typ_cd => l_acty_base_rt.bnft_rt_typ_cd
445 ,p_dsply_on_enrt_flag => 'N'--l_acty_base_rt.dsply_on_enrt_flag
446 ,p_elctns_made_dt => l_effective_start_date
447 ,p_cvg_amt_calc_mthd_id => l_cvg_amt_calc_mthd_id
448 ,p_actl_prem_id => l_acty_base_rt.actl_prem_id
449 ,p_comp_lvl_fctr_id => l_acty_base_rt.comp_lvl_fctr_id
450 ,p_business_group_id => p_business_group_id
451 ,p_object_version_number => l_object_version_number_prt
452 ,p_effective_date => p_effective_date
453 ,p_acty_base_rt_id => l_acty_base_rt.acty_base_rt_id
454 ,p_person_id => l_submitter_person_id
455 ,p_PRTT_REIMBMT_RQST_ID => p_PRTT_REIMBMT_RQST_ID
456 ,p_prtt_rmt_aprvd_fr_pymt_id => l_prtt_rmt_aprvd_fr_pymt_id
457 ,p_input_value_id => l_acty_base_rt.input_value_id
458 ,p_element_type_id => l_acty_base_rt.element_type_id
459 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
460 );
461 */
462 --bug#5523456
463 if l_acty_base_rt.element_type_id is not null then
464 --
465 ben_element_entry.create_reimburse_element
466 ( p_validate => p_validate
467 ,p_person_id => l_submitter_person_id
468 ,p_acty_base_rt_id => l_acty_base_rt.acty_base_rt_id
469 ,p_amt => p_aprvd_fr_pymt_amt
470 ,p_business_group_id => p_business_group_id
471 ,p_effective_date => p_effective_date
472 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
473 ,p_input_value_id => l_acty_base_rt.input_value_id
474 ,p_element_type_id => l_acty_base_rt.element_type_id
475 ,p_pl_id => l_pl_id
476 ,p_prtt_rmt_aprvd_fr_pymt_id => l_prtt_rmt_aprvd_fr_pymt_id
477 ,p_object_version_number => l_object_version_number
478 );
479 --
480 end if;
481
482
483
484
485
486 -- When in validation only mode raise the Validate_Enabled exception
487 --
488 if p_validate then
489 raise hr_api.validate_enabled;
490 end if;
491 --
492 -- Set all output arguments
493 --
494 p_prtt_rmt_aprvd_fr_pymt_id := l_prtt_rmt_aprvd_fr_pymt_id;
495 p_effective_start_date := l_effective_start_date;
496 p_effective_end_date := l_effective_end_date;
497 p_object_version_number := l_object_version_number;
498 --
499 hr_utility.set_location(' Leaving:'||l_proc, 70);
500 --
501 exception
502 --
503 when hr_api.validate_enabled then
504 --
505 -- As the Validate_Enabled exception has been raised
506 -- we must rollback to the savepoint
507 --
508 ROLLBACK TO create_prtt_rmt_aprvd_pymt;
509 --
510 -- Only set output warning arguments
511 -- (Any key or derived arguments must be set to null
512 -- when validation only mode is being used.)
513 --
514 p_prtt_rmt_aprvd_fr_pymt_id := null;
515 p_effective_start_date := null;
516 p_effective_end_date := null;
517 p_object_version_number := null;
518 hr_utility.set_location(' Leaving:'||l_proc, 80);
519 --
520 when others then
521 --
522 -- A validation or unexpected error has occured
523 --
524 ROLLBACK TO create_prtt_rmt_aprvd_pymt;
525 p_prtt_rmt_aprvd_fr_pymt_id := null;
526 p_effective_start_date := null;
527 p_effective_end_date := null;
528 p_object_version_number := null;
529 raise;
530 --
531 end create_prtt_rmt_aprvd_pymt;
532 -- ----------------------------------------------------------------------------
533 -- |------------------------< update_prtt_rmt_aprvd_pymt >--- ------------------|
534 -- ----------------------------------------------------------------------------
535 --
536 procedure update_prtt_rmt_aprvd_pymt
537 (p_validate in boolean default false
538 ,p_prtt_rmt_aprvd_fr_pymt_id in number
539 ,p_prtt_reimbmt_rqst_id in number default hr_api.g_number
540 ,p_effective_start_date out nocopy date
541 ,p_effective_end_date out nocopy date
542 ,p_apprvd_fr_pymt_num in number default hr_api.g_number
543 ,p_adjmt_flag in varchar2 default hr_api.g_varchar2
544 ,p_aprvd_fr_pymt_amt in number default hr_api.g_number
545 ,p_pymt_stat_cd in varchar2 default hr_api.g_varchar2
546 ,p_pymt_stat_rsn_cd in varchar2 default hr_api.g_varchar2
547 ,p_pymt_stat_ovrdn_rsn_cd in varchar2 default hr_api.g_varchar2
548 ,p_pymt_stat_prr_to_ovrd_cd in varchar2 default hr_api.g_varchar2
549 ,p_business_group_id in number default hr_api.g_number
550 ,p_element_entry_value_id in number default hr_api.g_number
551 ,p_pry_attribute_category in varchar2 default hr_api.g_varchar2
552 ,p_pry_attribute1 in varchar2 default hr_api.g_varchar2
553 ,p_pry_attribute2 in varchar2 default hr_api.g_varchar2
554 ,p_pry_attribute3 in varchar2 default hr_api.g_varchar2
555 ,p_pry_attribute4 in varchar2 default hr_api.g_varchar2
556 ,p_pry_attribute5 in varchar2 default hr_api.g_varchar2
557 ,p_pry_attribute6 in varchar2 default hr_api.g_varchar2
558 ,p_pry_attribute7 in varchar2 default hr_api.g_varchar2
559 ,p_pry_attribute8 in varchar2 default hr_api.g_varchar2
560 ,p_pry_attribute9 in varchar2 default hr_api.g_varchar2
561 ,p_pry_attribute10 in varchar2 default hr_api.g_varchar2
562 ,p_pry_attribute11 in varchar2 default hr_api.g_varchar2
563 ,p_pry_attribute12 in varchar2 default hr_api.g_varchar2
564 ,p_pry_attribute13 in varchar2 default hr_api.g_varchar2
565 ,p_pry_attribute14 in varchar2 default hr_api.g_varchar2
566 ,p_pry_attribute15 in varchar2 default hr_api.g_varchar2
567 ,p_pry_attribute16 in varchar2 default hr_api.g_varchar2
568 ,p_pry_attribute17 in varchar2 default hr_api.g_varchar2
569 ,p_pry_attribute18 in varchar2 default hr_api.g_varchar2
570 ,p_pry_attribute19 in varchar2 default hr_api.g_varchar2
571 ,p_pry_attribute20 in varchar2 default hr_api.g_varchar2
572 ,p_pry_attribute21 in varchar2 default hr_api.g_varchar2
573 ,p_pry_attribute22 in varchar2 default hr_api.g_varchar2
574 ,p_pry_attribute23 in varchar2 default hr_api.g_varchar2
575 ,p_pry_attribute24 in varchar2 default hr_api.g_varchar2
576 ,p_pry_attribute25 in varchar2 default hr_api.g_varchar2
577 ,p_pry_attribute26 in varchar2 default hr_api.g_varchar2
578 ,p_pry_attribute27 in varchar2 default hr_api.g_varchar2
579 ,p_pry_attribute28 in varchar2 default hr_api.g_varchar2
580 ,p_pry_attribute29 in varchar2 default hr_api.g_varchar2
581 ,p_pry_attribute30 in varchar2 default hr_api.g_varchar2
582 ,p_object_version_number in out nocopy number
583 ,p_effective_date in date
584 ,p_datetrack_mode in varchar2
585 ) is
586 --
587 -- Declare cursors and local variables
588 cursor c_pry is
589 select * From ben_prtt_rmt_aprvd_fr_pymt_f pry
590 where p_effective_date between pry.effective_start_date
591 and pry.effective_end_date
592 and pry.prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id;
593
594 l_pry_rec c_pry%rowtype ;
595
596 cursor c_acty_base_rt (p_acty_base_rt number)
597 is
598 select abr.input_value_id,
599 abr.element_type_id
600 from ben_acty_base_rt_f abr
601 where acty_base_rt_id = p_acty_base_rt
602 and p_effective_date between
603 abr.effective_start_date and
604 abr.effective_end_date;
605
606 cursor c_old_prc is
607 select prc.submitter_person_id,
608 prc.pl_id,
609 prc.prtt_enrt_rslt_id
610 from ben_prtt_reimbmt_rqst_f prc
611 where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
612 and p_effective_date between
613 prc.effective_start_date and
614 prc.effective_end_date;
615
616 cursor c_abr_pl(p_pl_id number)
617 is
618 select abr.acty_base_rt_id ,
619 abr.rt_typ_cd,
620 abr.tx_typ_cd,
621 abr.acty_typ_cd,
622 abr.rt_mlt_cd,
623 abr.bnft_rt_typ_cd,
624 abr.dsply_on_enrt_flag,
625 abr.comp_lvl_fctr_id,
626 abr.actl_prem_id,
627 abr.input_value_id,
628 abr.element_type_id
629 from ben_acty_base_rt_f abr
630 where pl_id = p_pl_id
631 and acty_typ_cd like 'PRD%'
632 and acty_base_rt_stat_cd = 'A'
633 and p_effective_date between
634 abr.effective_start_date and
635 abr.effective_end_date;
636
637
638 cursor c_abr_plip (p_pl_id number, p_pgm_id number)
639 is
640 select abr.acty_base_rt_id,
641 abr.rt_typ_cd,
642 abr.tx_typ_cd,
643 abr.acty_typ_cd,
644 abr.rt_mlt_cd,
645 abr.bnft_rt_typ_cd,
646 abr.dsply_on_enrt_flag,
647 abr.comp_lvl_fctr_id,
648 abr.actl_prem_id,
649 abr.input_value_id,
650 abr.element_type_id
651 from ben_acty_base_rt_f abr,
652 ben_plip_f plp
653 where plp.pl_id = p_pl_id
654 and plp.pgm_id = p_pgm_id
655 and p_effective_date between
656 plp.effective_start_date and
657 plp.effective_end_date
658 and plp.plip_id = abr.plip_id
659 and acty_base_rt_stat_cd = 'A'
660 and abr.acty_typ_cd like 'PRD%'
661 and p_effective_date between
662 abr.effective_start_date and
663 abr.effective_end_date;
664
665 --
666 cursor c_rslt (p_prtt_enrt_rslt_id number) is
667 select pgm_id
668 from ben_prtt_enrt_rslt_f pen
669 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
670 and pen.prtt_enrt_rslt_stat_cd is null;
671 --
672 l_acty_base_rt c_abr_pl%rowtype;
673 --
674
675
676
677 --
678 l_input_value_id ben_acty_base_rt_f.input_value_id%type;
679 l_element_type_id ben_acty_base_rt_f.element_type_id%type ;
680 l_proc varchar2(72) := g_package||'update_prtt_rmt_aprvd_pymt';
681 l_object_version_number ben_prtt_rmt_aprvd_fr_pymt_f.object_version_number%TYPE;
682 l_effective_start_date ben_prtt_rmt_aprvd_fr_pymt_f.effective_start_date%TYPE;
683 l_effective_end_date ben_prtt_rmt_aprvd_fr_pymt_f.effective_end_date%TYPE;
684 l_submitter_person_id number ;
685 l_dummy_number number ;
686 l_pgm_id number;
687 l_prtt_enrt_rslt_id number;
688 l_pl_id number;
689 --Bug 5558175
690 l_datetrack_mode varchar2(30);
691 --Bug 5558175
692 --
693 begin
694 --
695 hr_utility.set_location('Entering:'|| l_proc, 10);
696 --
697 -- Issue a savepoint if operating in validation only mode
698 --
699 savepoint update_prtt_rmt_aprvd_pymt;
700 --
701 hr_utility.set_location(l_proc, 20);
702 --
703 -- Process Logic
704
705 open c_pry ;
706 fetch c_pry into l_pry_rec ;
707 close c_pry ;
708 --
709 l_object_version_number := p_object_version_number;
710
711 --Bug 5558175 : Datetrack functionality on the Reimbursement Payment block (PRY) should not be
712 --present and hence hard-coded datetrack_mode in update, delete to correction and zap respectively.
713 l_datetrack_mode := hr_api.g_correction;
714 --End Bug 5558175
715 --
716 begin
717 --
718 -- Start of API User Hook for the before hook of update_prtt_rmt_aprvd_pymt
719 --
720 ben_prtt_rmt_aprvd_pymt_bk2.update_prtt_rmt_aprvd_pymt_b
721 (
722 p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
723 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
724 ,p_apprvd_fr_pymt_num => p_apprvd_fr_pymt_num
725 ,p_adjmt_flag => p_adjmt_flag
726 ,p_aprvd_fr_pymt_amt => p_aprvd_fr_pymt_amt
727 ,p_pymt_stat_cd => p_pymt_stat_cd
728 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
729 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
730 ,p_pymt_stat_prr_to_ovrd_cd => p_pymt_stat_prr_to_ovrd_cd
731 ,p_business_group_id => p_business_group_id
732 ,p_element_entry_value_id => p_element_entry_value_id
733 ,p_pry_attribute_category => p_pry_attribute_category
734 ,p_pry_attribute1 => p_pry_attribute1
735 ,p_pry_attribute2 => p_pry_attribute2
736 ,p_pry_attribute3 => p_pry_attribute3
737 ,p_pry_attribute4 => p_pry_attribute4
738 ,p_pry_attribute5 => p_pry_attribute5
739 ,p_pry_attribute6 => p_pry_attribute6
740 ,p_pry_attribute7 => p_pry_attribute7
741 ,p_pry_attribute8 => p_pry_attribute8
742 ,p_pry_attribute9 => p_pry_attribute9
743 ,p_pry_attribute10 => p_pry_attribute10
744 ,p_pry_attribute11 => p_pry_attribute11
745 ,p_pry_attribute12 => p_pry_attribute12
746 ,p_pry_attribute13 => p_pry_attribute13
747 ,p_pry_attribute14 => p_pry_attribute14
748 ,p_pry_attribute15 => p_pry_attribute15
749 ,p_pry_attribute16 => p_pry_attribute16
750 ,p_pry_attribute17 => p_pry_attribute17
751 ,p_pry_attribute18 => p_pry_attribute18
752 ,p_pry_attribute19 => p_pry_attribute19
753 ,p_pry_attribute20 => p_pry_attribute20
754 ,p_pry_attribute21 => p_pry_attribute21
755 ,p_pry_attribute22 => p_pry_attribute22
756 ,p_pry_attribute23 => p_pry_attribute23
757 ,p_pry_attribute24 => p_pry_attribute24
758 ,p_pry_attribute25 => p_pry_attribute25
759 ,p_pry_attribute26 => p_pry_attribute26
760 ,p_pry_attribute27 => p_pry_attribute27
761 ,p_pry_attribute28 => p_pry_attribute28
762 ,p_pry_attribute29 => p_pry_attribute29
763 ,p_pry_attribute30 => p_pry_attribute30
764 ,p_object_version_number => p_object_version_number
765 ,p_effective_date => trunc(p_effective_date)
766 ,p_datetrack_mode => l_datetrack_mode --Bug 5558175
767 );
768 exception
769 when hr_api.cannot_find_prog_unit then
770 hr_api.cannot_find_prog_unit_error
771 (p_module_name => 'UPDATE_prtt_rmt_aprvd_pymt'
772 ,p_hook_type => 'BP'
773 );
774 --
775 -- End of API User Hook for the before hook of update_prtt_rmt_aprvd_pymt
776 --
777 end;
778 --
779 ben_pry_upd.upd
780 (
781 p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
782 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
783 ,p_effective_start_date => l_effective_start_date
784 ,p_effective_end_date => l_effective_end_date
785 ,p_apprvd_fr_pymt_num => p_apprvd_fr_pymt_num
786 ,p_adjmt_flag => p_adjmt_flag
787 ,p_aprvd_fr_pymt_amt => p_aprvd_fr_pymt_amt
788 ,p_pymt_stat_cd => p_pymt_stat_cd
789 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
790 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
791 ,p_pymt_stat_prr_to_ovrd_cd => p_pymt_stat_prr_to_ovrd_cd
792 ,p_business_group_id => p_business_group_id
793 ,p_element_entry_value_id => p_element_entry_value_id
794 ,p_pry_attribute_category => p_pry_attribute_category
795 ,p_pry_attribute1 => p_pry_attribute1
796 ,p_pry_attribute2 => p_pry_attribute2
797 ,p_pry_attribute3 => p_pry_attribute3
798 ,p_pry_attribute4 => p_pry_attribute4
799 ,p_pry_attribute5 => p_pry_attribute5
800 ,p_pry_attribute6 => p_pry_attribute6
801 ,p_pry_attribute7 => p_pry_attribute7
802 ,p_pry_attribute8 => p_pry_attribute8
803 ,p_pry_attribute9 => p_pry_attribute9
804 ,p_pry_attribute10 => p_pry_attribute10
805 ,p_pry_attribute11 => p_pry_attribute11
806 ,p_pry_attribute12 => p_pry_attribute12
807 ,p_pry_attribute13 => p_pry_attribute13
808 ,p_pry_attribute14 => p_pry_attribute14
809 ,p_pry_attribute15 => p_pry_attribute15
810 ,p_pry_attribute16 => p_pry_attribute16
811 ,p_pry_attribute17 => p_pry_attribute17
812 ,p_pry_attribute18 => p_pry_attribute18
813 ,p_pry_attribute19 => p_pry_attribute19
814 ,p_pry_attribute20 => p_pry_attribute20
815 ,p_pry_attribute21 => p_pry_attribute21
816 ,p_pry_attribute22 => p_pry_attribute22
817 ,p_pry_attribute23 => p_pry_attribute23
818 ,p_pry_attribute24 => p_pry_attribute24
819 ,p_pry_attribute25 => p_pry_attribute25
820 ,p_pry_attribute26 => p_pry_attribute26
821 ,p_pry_attribute27 => p_pry_attribute27
822 ,p_pry_attribute28 => p_pry_attribute28
823 ,p_pry_attribute29 => p_pry_attribute29
824 ,p_pry_attribute30 => p_pry_attribute30
825 ,p_object_version_number => l_object_version_number
826 ,p_effective_date => trunc(p_effective_date)
827 ,p_datetrack_mode => l_datetrack_mode --Bug 5558175
828 );
829 --
830 begin
831 --
832 -- Start of API User Hook for the after hook of update_prtt_rmt_aprvd_pymt
833 --
834 ben_prtt_rmt_aprvd_pymt_bk2.update_prtt_rmt_aprvd_pymt_a
835 (
836 p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
837 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
838 ,p_effective_start_date => l_effective_start_date
839 ,p_effective_end_date => l_effective_end_date
840 ,p_apprvd_fr_pymt_num => p_apprvd_fr_pymt_num
841 ,p_adjmt_flag => p_adjmt_flag
842 ,p_aprvd_fr_pymt_amt => p_aprvd_fr_pymt_amt
843 ,p_pymt_stat_cd => p_pymt_stat_cd
844 ,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
845 ,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
846 ,p_pymt_stat_prr_to_ovrd_cd => p_pymt_stat_prr_to_ovrd_cd
847 ,p_business_group_id => p_business_group_id
848 ,p_element_entry_value_id => p_element_entry_value_id
849 ,p_pry_attribute_category => p_pry_attribute_category
850 ,p_pry_attribute1 => p_pry_attribute1
851 ,p_pry_attribute2 => p_pry_attribute2
852 ,p_pry_attribute3 => p_pry_attribute3
853 ,p_pry_attribute4 => p_pry_attribute4
854 ,p_pry_attribute5 => p_pry_attribute5
855 ,p_pry_attribute6 => p_pry_attribute6
856 ,p_pry_attribute7 => p_pry_attribute7
857 ,p_pry_attribute8 => p_pry_attribute8
858 ,p_pry_attribute9 => p_pry_attribute9
859 ,p_pry_attribute10 => p_pry_attribute10
860 ,p_pry_attribute11 => p_pry_attribute11
861 ,p_pry_attribute12 => p_pry_attribute12
862 ,p_pry_attribute13 => p_pry_attribute13
863 ,p_pry_attribute14 => p_pry_attribute14
864 ,p_pry_attribute15 => p_pry_attribute15
865 ,p_pry_attribute16 => p_pry_attribute16
866 ,p_pry_attribute17 => p_pry_attribute17
867 ,p_pry_attribute18 => p_pry_attribute18
868 ,p_pry_attribute19 => p_pry_attribute19
869 ,p_pry_attribute20 => p_pry_attribute20
870 ,p_pry_attribute21 => p_pry_attribute21
871 ,p_pry_attribute22 => p_pry_attribute22
872 ,p_pry_attribute23 => p_pry_attribute23
873 ,p_pry_attribute24 => p_pry_attribute24
874 ,p_pry_attribute25 => p_pry_attribute25
875 ,p_pry_attribute26 => p_pry_attribute26
876 ,p_pry_attribute27 => p_pry_attribute27
877 ,p_pry_attribute28 => p_pry_attribute28
878 ,p_pry_attribute29 => p_pry_attribute29
879 ,p_pry_attribute30 => p_pry_attribute30
880 ,p_object_version_number => l_object_version_number
881 ,p_effective_date => trunc(p_effective_date)
882 ,p_datetrack_mode => l_datetrack_mode --Bug 5558175
883 );
884 exception
885 when hr_api.cannot_find_prog_unit then
886 hr_api.cannot_find_prog_unit_error
887 (p_module_name => 'UPDATE_prtt_rmt_aprvd_pymt'
888 ,p_hook_type => 'AP'
889 );
890 --
891 -- End of API User Hook for the after hook of update_prtt_rmt_aprvd_pymt
892 --
893 end;
894 --
895
896 if nvl(l_pry_rec.aprvd_fr_pymt_amt,0) <> nvl(p_aprvd_fr_pymt_amt,0) then
897
898 open c_old_prc ;
899 fetch c_old_prc into l_submitter_person_id, l_pl_id, l_prtt_enrt_rslt_id;
900 close c_old_prc ;
901
902 /*
903 --update benr_prtt_rt_val
904 ben_prtt_rt_val_api.update_prtt_rt_val(
905 p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
906 ,p_rt_end_dt => hr_api.g_eot
907 ,p_acty_base_rt_id => l_prv_rec.acty_base_rt_id
908 ,p_input_value_id => l_input_value_id
909 ,p_element_type_id => l_element_type_id
910 ,p_person_id => l_submitter_person_id
911 ,p_ended_per_in_ler_id => null
912 ,p_rt_val => p_aprvd_fr_pymt_amt
913 ,p_business_group_id => l_prv_rec.business_group_id
914 ,p_object_version_number => l_prv_rec.object_version_number
915 ,p_effective_date => p_effective_date
916 );
917 */
918 -- delete the element and create a new element
919 --
920 open c_rslt (l_prtt_enrt_rslt_id);
921 fetch c_rslt into l_pgm_id;
922 close c_rslt;
923 --
924 open c_abr_plip(l_pl_id,l_pgm_id);
925 fetch c_abr_plip into l_acty_base_rt;
926 if c_abr_plip%notfound then
927 open c_abr_pl(l_pl_id) ;
928 fetch c_abr_pl into l_acty_base_rt ;
929 if c_abr_pl%notfound then
930 close c_abr_pl;
931 close c_abr_plip ;
932 fnd_message.set_name('BEN','BEN_92697_NO_REMBMT_RATE');
933 fnd_message.raise_error;
934 end if ;
935 close c_abr_pl;
936 end if;
937 close c_abr_plip;
938 --
939 if l_pry_rec.element_entry_value_id is not null then
940 --
941 ben_element_entry.end_reimburse_element
942 (p_validate => p_validate
943 ,p_business_group_id => p_business_group_id
944 ,p_person_id => l_submitter_person_id
945 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
946 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
947 ,p_effective_date => p_effective_date
948 ,p_element_entry_value_id => l_pry_rec.element_entry_value_id );
949 end if;
950 --
951 --bug#5523456
952 if l_acty_base_rt.element_type_id is not null then
953 --
954 ben_element_entry.create_reimburse_element
955 ( p_validate => p_validate
956 ,p_person_id => l_submitter_person_id
957 ,p_acty_base_rt_id => l_acty_base_rt.acty_base_rt_id
958 ,p_amt => p_aprvd_fr_pymt_amt
959 ,p_business_group_id => p_business_group_id
960 ,p_effective_date => p_effective_date
961 ,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
962 ,p_input_value_id => l_acty_base_rt.input_value_id
963 ,p_element_type_id => l_acty_base_rt.element_type_id
964 ,p_pl_id => l_pl_id
965 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
966 ,p_object_version_number => l_object_version_number
967 );
968 --
969 end if;
970
971
972
973 end if ;
974
975
976
977 hr_utility.set_location(l_proc, 60);
978 --
979 -- When in validation only mode raise the Validate_Enabled exception
980 --
981 if p_validate then
982 raise hr_api.validate_enabled;
983 end if;
984 --
985 -- Set all output arguments
986 --
987 p_object_version_number := l_object_version_number;
988 p_effective_start_date := l_effective_start_date;
989 p_effective_end_date := l_effective_end_date;
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 update_prtt_rmt_aprvd_pymt;
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_effective_start_date := null;
1007 p_effective_end_date := null;
1008 hr_utility.set_location(' Leaving:'||l_proc, 80);
1009 --
1010 when others then
1011 --
1012 -- A validation or unexpected error has occured
1013 --
1014 ROLLBACK TO update_prtt_rmt_aprvd_pymt;
1015 p_effective_start_date := null;
1016 p_effective_end_date := null;
1017 raise;
1018 --
1019 end update_prtt_rmt_aprvd_pymt;
1020 -- ----------------------------------------------------------------------------
1021 -- |------------------------< delete_prtt_rmt_aprvd_pymt >----------------------|
1022 -- ----------------------------------------------------------------------------
1023 --
1024 procedure delete_prtt_rmt_aprvd_pymt
1025 (p_validate in boolean default false
1026 ,p_prtt_rmt_aprvd_fr_pymt_id in number
1027 ,p_effective_start_date out nocopy date
1028 ,p_effective_end_date out nocopy date
1029 ,p_object_version_number in out nocopy number
1030 ,p_effective_date in date
1031 ,p_datetrack_mode in varchar2
1032 ) is
1033 --
1034 -- Declare cursors and local variables
1035
1036
1037 cursor c_pry is
1038 select * From ben_prtt_rmt_aprvd_fr_pymt_f pry
1039 where p_effective_date between pry.effective_start_date
1040 and pry.effective_end_date
1041 and pry.prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id;
1042 --
1043 l_pry_rec c_pry%rowtype;
1044 --
1045 cursor c_old_prc is
1046 select prc.submitter_person_id
1047 ,prc.prtt_reimbmt_rqst_id
1048 ,prc.business_group_id
1049 from ben_prtt_reimbmt_rqst_f prc ,
1050 ben_prtt_rmt_aprvd_fr_pymt_f pyr
1051 where prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id
1052 and prc.prtt_reimbmt_rqst_id = pyr.prtt_reimbmt_rqst_id
1053 and p_effective_date between
1054 pyr.effective_start_date and
1055 pyr.effective_end_date
1056 and p_effective_date between
1057 prc.effective_start_date and
1058 prc.effective_end_date;
1059
1060
1061
1062 --
1063 l_proc varchar2(72) := g_package||'delete_prtt_rmt_aprvd_pymt';
1064 l_object_version_number ben_prtt_rmt_aprvd_fr_pymt_f.object_version_number%TYPE;
1065 l_effective_start_date ben_prtt_rmt_aprvd_fr_pymt_f.effective_start_date%TYPE;
1066 l_effective_end_date ben_prtt_rmt_aprvd_fr_pymt_f.effective_end_date%TYPE;
1067 l_submitter_person_id number ;
1068 l_prtt_reimbmt_rqst_id number ;
1069 l_business_group_id number;
1070
1071 --Bug 5558175
1072 l_datetrack_mode varchar2(30);
1073 --End Bug 5558175
1074 --
1075 begin
1076 --
1077 hr_utility.set_location('Entering:'|| l_proc, 10);
1078 --
1079 -- Issue a savepoint if operating in validation only mode
1080 --
1081 savepoint delete_prtt_rmt_aprvd_pymt;
1082 --
1083 -- bug fix 2223214
1084 -- added old rec retrieval to before delete , previously it was after delete
1085 --
1086 open c_old_prc ;
1087 fetch c_old_prc
1088 into l_submitter_person_id,
1089 l_prtt_reimbmt_rqst_id,
1090 l_business_group_id;
1091 close c_old_prc ;
1092 --
1093 -- end fix 2223214
1094 hr_utility.set_location(l_proc, 20);
1095 --
1096 -- Process Logic
1097 open c_pry ;
1098 fetch c_pry into l_pry_rec ;
1099 close c_pry ;
1100
1101 --
1102 l_object_version_number := p_object_version_number;
1103
1104 --Bug 5558175 : Datetrack functionality on the Reimbursement Payment block (PRY) should not be
1105 --present and hence hard-coded datetrack_mode in update, delete to correction and zap respectively.
1106 l_datetrack_mode := hr_api.g_zap;
1107 --End Bug 5558175
1108 --
1109 --
1110 begin
1111 --
1112 -- Start of API User Hook for the before hook of delete_prtt_rmt_aprvd_pymt
1113 --
1114 ben_prtt_rmt_aprvd_pymt_bk3.delete_prtt_rmt_aprvd_pymt_b
1115 (
1116 p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1117 ,p_object_version_number => p_object_version_number
1118 ,p_effective_date => trunc(p_effective_date)
1119 ,p_datetrack_mode => l_datetrack_mode --Bug 5558175
1120 );
1121 exception
1122 when hr_api.cannot_find_prog_unit then
1123 hr_api.cannot_find_prog_unit_error
1124 (p_module_name => 'DELETE_prtt_rmt_aprvd_pymt'
1125 ,p_hook_type => 'BP'
1126 );
1127 --
1128 -- End of API User Hook for the before hook of delete_prtt_rmt_aprvd_pymt
1129 --
1130 end;
1131 --
1132 ben_pry_del.del
1133 (
1134 p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1135 ,p_effective_start_date => l_effective_start_date
1136 ,p_effective_end_date => l_effective_end_date
1137 ,p_object_version_number => l_object_version_number
1138 ,p_effective_date => p_effective_date
1139 ,p_datetrack_mode => l_datetrack_mode --Bug 5558175
1140 );
1141 --
1142 begin
1143 --
1144 -- Start of API User Hook for the after hook of delete_prtt_rmt_aprvd_pymt
1145 --
1146 ben_prtt_rmt_aprvd_pymt_bk3.delete_prtt_rmt_aprvd_pymt_a
1147 (
1148 p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1149 ,p_effective_start_date => l_effective_start_date
1150 ,p_effective_end_date => l_effective_end_date
1151 ,p_object_version_number => l_object_version_number
1152 ,p_effective_date => trunc(p_effective_date)
1153 ,p_datetrack_mode => l_datetrack_mode --Bug 5558175
1154 );
1155 exception
1156 when hr_api.cannot_find_prog_unit then
1157 hr_api.cannot_find_prog_unit_error
1158 (p_module_name => 'DELETE_prtt_rmt_aprvd_pymt'
1159 ,p_hook_type => 'AP'
1160 );
1161 --
1162 -- End of API User Hook for the after hook of delete_prtt_rmt_aprvd_pymt
1163 --
1164 end;
1165 --
1166 -- Delete the prt_rt_rate
1167 -- before deleting the run result to be validated
1168 -- if the run result exist the logic to be decided
1169
1170 /*
1171 open c_prv(l_prtt_reimbmt_rqst_id ) ;
1172 fetch c_prv into l_prv_rec ;
1173 close c_prv ;
1174
1175 ben_prtt_rt_val_api.delete_prtt_rt_val(
1176 -- p_validate => p_validate
1177 p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
1178 --,p_enrt_rt_id => l_prv_rec.enrt_rt_id
1179 ,p_person_id => l_submitter_person_id
1180 ,p_business_group_id => l_prv_rec.business_group_id
1181 ,p_object_version_number => l_prv_rec.object_version_number
1182 ,p_effective_date => p_effective_date );
1183
1184 */
1185
1186 ben_element_entry.end_reimburse_element
1187 (p_validate => p_validate
1188 ,p_business_group_id => l_business_group_id
1189 ,p_person_id => l_submitter_person_id
1190 ,p_prtt_reimbmt_rqst_id => l_prtt_reimbmt_rqst_id
1191 ,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1192 ,p_effective_date => p_effective_date
1193 ,p_element_entry_value_id => l_pry_rec.element_entry_value_id );
1194
1195
1196 hr_utility.set_location(l_proc, 60);
1197 --
1198 -- When in validation only mode raise the Validate_Enabled exception
1199 --
1200 if p_validate then
1201 raise hr_api.validate_enabled;
1202 end if;
1203 --
1204 hr_utility.set_location(' Leaving:'||l_proc, 70);
1205 --
1206 exception
1207 --
1208 when hr_api.validate_enabled then
1209 --
1210 -- As the Validate_Enabled exception has been raised
1211 -- we must rollback to the savepoint
1212 --
1213 ROLLBACK TO delete_prtt_rmt_aprvd_pymt;
1214 --
1215 -- Only set output warning arguments
1216 -- (Any key or derived arguments must be set to null
1217 -- when validation only mode is being used.)
1218 --
1219 p_effective_start_date := null;
1220 p_effective_end_date := null;
1221 --
1222 when others then
1223 --
1224 -- A validation or unexpected error has occured
1225 --
1226 ROLLBACK TO delete_prtt_rmt_aprvd_pymt;
1227 p_effective_start_date := null;
1228 p_effective_end_date := null;
1229 raise;
1230 --
1231 end delete_prtt_rmt_aprvd_pymt;
1232 --
1233 -- ----------------------------------------------------------------------------
1234 -- |-------------------------------< lck >------------------------------------|
1235 -- ----------------------------------------------------------------------------
1236 --
1237 procedure lck
1238 (
1239 p_prtt_rmt_aprvd_fr_pymt_id in number
1240 ,p_object_version_number in number
1241 ,p_effective_date in date
1242 ,p_datetrack_mode in varchar2
1243 ,p_validation_start_date out nocopy date
1244 ,p_validation_end_date out nocopy date
1245 ) is
1246 --
1247 --
1248 -- Declare cursors and local variables
1249 --
1250 l_proc varchar2(72) := g_package||'lck';
1251 l_validation_start_date date;
1252 l_validation_end_date date;
1253 --
1254 begin
1255 --
1256 hr_utility.set_location('Entering:'|| l_proc, 10);
1257 --
1258 ben_pry_shd.lck
1259 (
1260 p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
1261 ,p_validation_start_date => l_validation_start_date
1262 ,p_validation_end_date => l_validation_end_date
1263 ,p_object_version_number => p_object_version_number
1264 ,p_effective_date => p_effective_date
1265 ,p_datetrack_mode => p_datetrack_mode
1266 );
1267 --
1268 hr_utility.set_location(' Leaving:'||l_proc, 70);
1269 --
1270 end lck;
1271 --
1272 end ben_prtt_rmt_aprvd_pymt_api;