[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIGIBLE_PERSON_PERF_API
Source
1 Package Body ben_Eligible_Person_perf_api as
2 /* $Header: bepepppi.pkb 120.6 2007/03/27 15:53:01 rtagarra noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_Eligible_Person_perf_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------< create_perf_Eligible_Person >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_perf_Eligible_Person
13 (p_validate in boolean default false
14 ,p_elig_per_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_business_group_id in number default null
18 ,p_pl_id in number default null
19 ,p_pgm_id in number default null
20 ,p_plip_id in number default null
21 ,p_ptip_id in number default null
22 ,p_ler_id in number default null
23 ,p_person_id in number default null
24 ,p_per_in_ler_id in number default null
25 ,p_dpnt_othr_pl_cvrd_rl_flag in varchar2 default 'N'
26 ,p_prtn_ovridn_thru_dt in date default null
27 ,p_pl_key_ee_flag in varchar2 default 'N'
28 ,p_pl_hghly_compd_flag in varchar2 default 'N'
29 ,p_elig_flag in varchar2 default 'N'
30 ,p_comp_ref_amt in number default null
31 ,p_cmbn_age_n_los_val in number default null
32 ,p_comp_ref_uom in varchar2 default null
33 ,p_age_val in number default null
34 ,p_los_val in number default null
35 ,p_prtn_end_dt in date default null
36 ,p_prtn_strt_dt in date default null
37 ,p_wait_perd_cmpltn_dt in date default null
38 ,p_wait_perd_strt_dt in date default null
39 ,p_wv_ctfn_typ_cd in varchar2 default null
40 ,p_hrs_wkd_val in number default null
41 ,p_hrs_wkd_bndry_perd_cd in varchar2 default null
42 ,p_prtn_ovridn_flag in varchar2 default null
43 ,p_no_mx_prtn_ovrid_thru_flag in varchar2 default 'N'
44 ,p_prtn_ovridn_rsn_cd in varchar2 default null
45 ,p_age_uom in varchar2 default null
46 ,p_los_uom in varchar2 default null
47 ,p_ovrid_svc_dt in date default null
48 ,p_inelg_rsn_cd in varchar2 default null
49 ,p_frz_los_flag in varchar2 default 'N'
50 ,p_frz_age_flag in varchar2 default 'N'
51 ,p_frz_cmp_lvl_flag in varchar2 default 'N'
52 ,p_frz_pct_fl_tm_flag in varchar2 default 'N'
53 ,p_frz_hrs_wkd_flag in varchar2 default 'N'
54 ,p_frz_comb_age_and_los_flag in varchar2 default 'N'
55 ,p_dstr_rstcn_flag in varchar2 default 'N'
56 ,p_pct_fl_tm_val in number default null
57 ,p_wv_prtn_rsn_cd in varchar2 default null
58 ,p_pl_wvd_flag in varchar2 default 'N'
59 ,p_rt_comp_ref_amt in number default null
60 ,p_rt_cmbn_age_n_los_val in number default null
61 ,p_rt_comp_ref_uom in varchar2 default null
62 ,p_rt_age_val in number default null
63 ,p_rt_los_val in number default null
64 ,p_rt_hrs_wkd_val in number default null
65 ,p_rt_hrs_wkd_bndry_perd_cd in varchar2 default null
66 ,p_rt_age_uom in varchar2 default null
67 ,p_rt_los_uom in varchar2 default null
68 ,p_rt_pct_fl_tm_val in number default null
69 ,p_rt_frz_los_flag in varchar2 default 'N'
70 ,p_rt_frz_age_flag in varchar2 default 'N'
71 ,p_rt_frz_cmp_lvl_flag in varchar2 default 'N'
72 ,p_rt_frz_pct_fl_tm_flag in varchar2 default 'N'
73 ,p_rt_frz_hrs_wkd_flag in varchar2 default 'N'
74 ,p_rt_frz_comb_age_and_los_flag in varchar2 default 'N'
75 ,p_once_r_cntug_cd in varchar2 default null
76 ,p_pl_ordr_num in number default null
77 ,p_plip_ordr_num in number default null
78 ,p_ptip_ordr_num in number default null
79 ,p_pep_attribute_category in varchar2 default null
80 ,p_pep_attribute1 in varchar2 default null
81 ,p_pep_attribute2 in varchar2 default null
82 ,p_pep_attribute3 in varchar2 default null
83 ,p_pep_attribute4 in varchar2 default null
84 ,p_pep_attribute5 in varchar2 default null
85 ,p_pep_attribute6 in varchar2 default null
86 ,p_pep_attribute7 in varchar2 default null
87 ,p_pep_attribute8 in varchar2 default null
88 ,p_pep_attribute9 in varchar2 default null
89 ,p_pep_attribute10 in varchar2 default null
90 ,p_pep_attribute11 in varchar2 default null
91 ,p_pep_attribute12 in varchar2 default null
92 ,p_pep_attribute13 in varchar2 default null
93 ,p_pep_attribute14 in varchar2 default null
94 ,p_pep_attribute15 in varchar2 default null
95 ,p_pep_attribute16 in varchar2 default null
96 ,p_pep_attribute17 in varchar2 default null
97 ,p_pep_attribute18 in varchar2 default null
98 ,p_pep_attribute19 in varchar2 default null
99 ,p_pep_attribute20 in varchar2 default null
100 ,p_pep_attribute21 in varchar2 default null
101 ,p_pep_attribute22 in varchar2 default null
102 ,p_pep_attribute23 in varchar2 default null
103 ,p_pep_attribute24 in varchar2 default null
104 ,p_pep_attribute25 in varchar2 default null
105 ,p_pep_attribute26 in varchar2 default null
106 ,p_pep_attribute27 in varchar2 default null
107 ,p_pep_attribute28 in varchar2 default null
108 ,p_pep_attribute29 in varchar2 default null
109 ,p_pep_attribute30 in varchar2 default null
110 ,p_request_id in number default null
111 ,p_program_application_id in number default null
112 ,p_program_id in number default null
113 ,p_program_update_date in date default null
114 ,p_object_version_number out nocopy number
115 ,p_effective_date in date
116 ,p_override_validation in boolean default false
117 ,p_defer in boolean
118 )
119 is
120 --
121 l_proc varchar2(72) := g_package||'create_Eligible_Person';
122 --
123 l_rec ben_pep_shd.g_rec_type;
124 l_pepinsplip g_pepapi_rectyp;
125 l_pepinsplip_score_tab ben_evaluate_elig_profiles.scoreTab;
126 --
127 -- Declare cursors and local variables
128 --
129 l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
130 l_effective_start_date ben_elig_per_f.effective_start_date%TYPE;
131 l_effective_end_date ben_elig_per_f.effective_end_date%TYPE;
132 l_object_version_number ben_elig_per_f.object_version_number%TYPE;
133 --
134 l_created_by ben_elig_per_f.created_by%TYPE;
135 l_creation_date ben_elig_per_f.creation_date%TYPE;
136 l_last_update_date ben_elig_per_f.last_update_date%TYPE;
137 l_last_updated_by ben_elig_per_f.last_updated_by%TYPE;
138 l_last_update_login ben_elig_per_f.last_update_login%TYPE;
139 --
140 l_minmax_rec ben_batch_dt_api.gtyp_dtsum_row;
141 l_dummy_pep_id number;
142 l_dummy_esd date;
143 l_dummy_eed date;
144 l_dummy_ovn number;
145 --
146 begin
147 --
148 hr_utility.set_location('Entering:'|| l_proc, 10);
149 --
150 -- Check for PLIP level eligibility
151 --
152 if p_pgm_id is not null
153 and p_plip_id is not null
154 and p_pl_id is null
155 and p_ptip_id is null
156 and p_defer
157 then
158 --
159 -- Defer PLIP insert to plan level write PEP Plip values to global
160 g_pepinsplip := l_pepinsplip;
161 --
162 g_pepinsplip.p_validate := p_validate;
163 g_pepinsplip.p_business_group_id := p_business_group_id;
164 g_pepinsplip.p_pl_id := p_pl_id;
165 g_pepinsplip.p_pgm_id := p_pgm_id;
166 g_pepinsplip.p_plip_id := p_plip_id;
167 g_pepinsplip.p_ptip_id := p_ptip_id;
168 g_pepinsplip.p_ler_id := p_ler_id;
169 g_pepinsplip.p_person_id := p_person_id;
170 g_pepinsplip.p_per_in_ler_id := p_per_in_ler_id;
171 g_pepinsplip.p_dpnt_othr_pl_cvrd_rl_flag := p_dpnt_othr_pl_cvrd_rl_flag;
172 g_pepinsplip.p_prtn_ovridn_thru_dt := p_prtn_ovridn_thru_dt;
173 g_pepinsplip.p_pl_key_ee_flag := p_pl_key_ee_flag;
174 g_pepinsplip.p_pl_hghly_compd_flag := p_pl_hghly_compd_flag;
175 g_pepinsplip.p_elig_flag := p_elig_flag;
176 g_pepinsplip.p_comp_ref_amt := p_comp_ref_amt;
177 g_pepinsplip.p_cmbn_age_n_los_val := p_cmbn_age_n_los_val;
178 g_pepinsplip.p_comp_ref_uom := p_comp_ref_uom;
179 g_pepinsplip.p_age_val := p_age_val;
180 g_pepinsplip.p_los_val := p_los_val;
181 g_pepinsplip.p_prtn_end_dt := p_prtn_end_dt;
182 g_pepinsplip.p_prtn_strt_dt := p_prtn_strt_dt;
183 g_pepinsplip.p_wait_perd_cmpltn_dt := p_wait_perd_cmpltn_dt;
184 g_pepinsplip.p_wait_perd_strt_dt := p_wait_perd_strt_dt;
185 g_pepinsplip.p_wv_ctfn_typ_cd := p_wv_ctfn_typ_cd;
186 g_pepinsplip.p_hrs_wkd_val := p_hrs_wkd_val;
187 g_pepinsplip.p_hrs_wkd_bndry_perd_cd := p_hrs_wkd_bndry_perd_cd;
188 g_pepinsplip.p_prtn_ovridn_flag := p_prtn_ovridn_flag;
189 g_pepinsplip.p_no_mx_prtn_ovrid_thru_flag := p_no_mx_prtn_ovrid_thru_flag;
190 g_pepinsplip.p_prtn_ovridn_rsn_cd := p_prtn_ovridn_rsn_cd;
191 g_pepinsplip.p_age_uom := p_age_uom;
192 g_pepinsplip.p_los_uom := p_los_uom;
193 g_pepinsplip.p_ovrid_svc_dt := p_ovrid_svc_dt;
194 g_pepinsplip.p_inelg_rsn_cd := p_inelg_rsn_cd;
195 g_pepinsplip.p_frz_los_flag := p_frz_los_flag;
196 g_pepinsplip.p_frz_age_flag := p_frz_age_flag;
197 g_pepinsplip.p_frz_cmp_lvl_flag := p_frz_cmp_lvl_flag;
198 g_pepinsplip.p_frz_pct_fl_tm_flag := p_frz_pct_fl_tm_flag;
199 g_pepinsplip.p_frz_hrs_wkd_flag := p_frz_hrs_wkd_flag;
200 g_pepinsplip.p_frz_comb_age_and_los_flag := p_frz_comb_age_and_los_flag;
201 g_pepinsplip.p_dstr_rstcn_flag := p_dstr_rstcn_flag;
202 g_pepinsplip.p_pct_fl_tm_val := p_pct_fl_tm_val;
203 g_pepinsplip.p_wv_prtn_rsn_cd := p_wv_prtn_rsn_cd;
204 g_pepinsplip.p_pl_wvd_flag := p_pl_wvd_flag;
205 g_pepinsplip.p_rt_comp_ref_amt := p_rt_comp_ref_amt;
206 g_pepinsplip.p_rt_cmbn_age_n_los_val := p_rt_cmbn_age_n_los_val;
207 g_pepinsplip.p_rt_comp_ref_uom := p_rt_comp_ref_uom;
208 g_pepinsplip.p_rt_age_val := p_rt_age_val;
209 g_pepinsplip.p_rt_los_val := p_rt_los_val;
210 g_pepinsplip.p_rt_hrs_wkd_val := p_rt_hrs_wkd_val;
211 g_pepinsplip.p_rt_hrs_wkd_bndry_perd_cd := p_rt_hrs_wkd_bndry_perd_cd;
212 g_pepinsplip.p_rt_age_uom := p_rt_age_uom;
213 g_pepinsplip.p_rt_los_uom := p_rt_los_uom;
214 g_pepinsplip.p_rt_pct_fl_tm_val := p_rt_pct_fl_tm_val;
215 g_pepinsplip.p_rt_frz_los_flag := p_rt_frz_los_flag;
216 g_pepinsplip.p_rt_frz_age_flag := p_rt_frz_age_flag;
217 g_pepinsplip.p_rt_frz_cmp_lvl_flag := p_rt_frz_cmp_lvl_flag;
218 g_pepinsplip.p_rt_frz_pct_fl_tm_flag := p_rt_frz_pct_fl_tm_flag;
219 g_pepinsplip.p_rt_frz_hrs_wkd_flag := p_rt_frz_hrs_wkd_flag;
220 g_pepinsplip.p_rt_frz_comb_age_and_los_flag := p_rt_frz_comb_age_and_los_flag;
221 g_pepinsplip.p_once_r_cntug_cd := p_once_r_cntug_cd;
222 g_pepinsplip.p_pl_ordr_num := p_pl_ordr_num;
223 g_pepinsplip.p_plip_ordr_num := p_plip_ordr_num;
224 g_pepinsplip.p_ptip_ordr_num := p_ptip_ordr_num;
225 g_pepinsplip.p_pep_attribute_category := p_pep_attribute_category;
226 g_pepinsplip.p_pep_attribute1 := p_pep_attribute1;
227 g_pepinsplip.p_pep_attribute2 := p_pep_attribute2;
228 g_pepinsplip.p_pep_attribute3 := p_pep_attribute3;
229 g_pepinsplip.p_pep_attribute4 := p_pep_attribute4;
230 g_pepinsplip.p_pep_attribute5 := p_pep_attribute5;
231 g_pepinsplip.p_pep_attribute6 := p_pep_attribute6;
232 g_pepinsplip.p_pep_attribute7 := p_pep_attribute7;
233 g_pepinsplip.p_pep_attribute8 := p_pep_attribute8;
234 g_pepinsplip.p_pep_attribute9 := p_pep_attribute9;
235 g_pepinsplip.p_pep_attribute10 := p_pep_attribute10;
236 g_pepinsplip.p_pep_attribute11 := p_pep_attribute11;
237 g_pepinsplip.p_pep_attribute12 := p_pep_attribute12;
238 g_pepinsplip.p_pep_attribute13 := p_pep_attribute13;
239 g_pepinsplip.p_pep_attribute14 := p_pep_attribute14;
240 g_pepinsplip.p_pep_attribute15 := p_pep_attribute15;
241 g_pepinsplip.p_pep_attribute16 := p_pep_attribute16;
242 g_pepinsplip.p_pep_attribute17 := p_pep_attribute17;
243 g_pepinsplip.p_pep_attribute18 := p_pep_attribute18;
244 g_pepinsplip.p_pep_attribute19 := p_pep_attribute19;
245 g_pepinsplip.p_pep_attribute20 := p_pep_attribute20;
246 g_pepinsplip.p_pep_attribute21 := p_pep_attribute21;
247 g_pepinsplip.p_pep_attribute22 := p_pep_attribute22;
248 g_pepinsplip.p_pep_attribute23 := p_pep_attribute23;
249 g_pepinsplip.p_pep_attribute24 := p_pep_attribute24;
250 g_pepinsplip.p_pep_attribute25 := p_pep_attribute25;
251 g_pepinsplip.p_pep_attribute26 := p_pep_attribute26;
252 g_pepinsplip.p_pep_attribute27 := p_pep_attribute27;
253 g_pepinsplip.p_pep_attribute28 := p_pep_attribute28;
254 g_pepinsplip.p_pep_attribute29 := p_pep_attribute29;
255 g_pepinsplip.p_pep_attribute30 := p_pep_attribute30;
256 g_pepinsplip.p_request_id := p_request_id;
257 g_pepinsplip.p_program_application_id := p_program_application_id;
258 g_pepinsplip.p_program_id := p_program_id;
259 g_pepinsplip.p_program_update_date := p_program_update_date;
260 g_pepinsplip.p_effective_date := p_effective_date;
261 g_pepinsplip.p_override_validation := p_override_validation;
262 --
263 hr_utility.set_location('Leaving - Defer'||l_proc,11);
264 return;
265 -- Check for deferred PLIP transactions
266 --
267 elsif p_pgm_id is not null
268 and p_plip_id is null
269 and p_pl_id is not null
270 and p_ptip_id is null
271 and g_pepinsplip.p_pgm_id is not null
272 and g_pepinsplip.p_plip_id is not null
273 and g_pepinsplip.p_pl_id is null
274 and g_pepinsplip.p_ptip_id is null
275 and p_defer
276 then
277 --
278 ben_Eligible_Person_perf_api.create_perf_Eligible_Person
279 (p_validate => g_pepinsplip.p_validate
280 ,p_elig_per_id => l_dummy_pep_id
281 ,p_effective_start_date => l_dummy_esd
282 ,p_effective_end_date => l_dummy_eed
283 ,p_business_group_id => g_pepinsplip.p_business_group_id
284 ,p_pl_id => g_pepinsplip.p_pl_id
285 ,p_pgm_id => g_pepinsplip.p_pgm_id
286 ,p_plip_id => g_pepinsplip.p_plip_id
287 ,p_ptip_id => g_pepinsplip.p_ptip_id
288 ,p_ler_id => g_pepinsplip.p_ler_id
289 ,p_person_id => g_pepinsplip.p_person_id
290 ,p_per_in_ler_id => g_pepinsplip.p_per_in_ler_id
291 ,p_dpnt_othr_pl_cvrd_rl_flag => g_pepinsplip.p_dpnt_othr_pl_cvrd_rl_flag
292 ,p_prtn_ovridn_thru_dt => g_pepinsplip.p_prtn_ovridn_thru_dt
293 ,p_pl_key_ee_flag => g_pepinsplip.p_pl_key_ee_flag
294 ,p_pl_hghly_compd_flag => g_pepinsplip.p_pl_hghly_compd_flag
295 ,p_elig_flag => g_pepinsplip.p_elig_flag
296 ,p_comp_ref_amt => g_pepinsplip.p_comp_ref_amt
297 ,p_cmbn_age_n_los_val => g_pepinsplip.p_cmbn_age_n_los_val
298 ,p_comp_ref_uom => g_pepinsplip.p_comp_ref_uom
299 ,p_age_val => g_pepinsplip.p_age_val
300 ,p_los_val => g_pepinsplip.p_los_val
301 ,p_prtn_end_dt => g_pepinsplip.p_prtn_end_dt
302 ,p_prtn_strt_dt => g_pepinsplip.p_prtn_strt_dt
303 ,p_wait_perd_cmpltn_dt => g_pepinsplip.p_wait_perd_cmpltn_dt
304 ,p_wait_perd_strt_dt => g_pepinsplip.p_wait_perd_strt_dt
305 ,p_wv_ctfn_typ_cd => g_pepinsplip.p_wv_ctfn_typ_cd
306 ,p_hrs_wkd_val => g_pepinsplip.p_hrs_wkd_val
307 ,p_hrs_wkd_bndry_perd_cd => g_pepinsplip.p_hrs_wkd_bndry_perd_cd
308 ,p_prtn_ovridn_flag => g_pepinsplip.p_prtn_ovridn_flag
309 ,p_no_mx_prtn_ovrid_thru_flag => g_pepinsplip.p_no_mx_prtn_ovrid_thru_flag
310 ,p_prtn_ovridn_rsn_cd => g_pepinsplip.p_prtn_ovridn_rsn_cd
311 ,p_age_uom => g_pepinsplip.p_age_uom
312 ,p_los_uom => g_pepinsplip.p_los_uom
313 ,p_ovrid_svc_dt => g_pepinsplip.p_ovrid_svc_dt
314 ,p_inelg_rsn_cd => g_pepinsplip.p_inelg_rsn_cd
315 ,p_frz_los_flag => g_pepinsplip.p_frz_los_flag
316 ,p_frz_age_flag => g_pepinsplip.p_frz_age_flag
317 ,p_frz_cmp_lvl_flag => g_pepinsplip.p_frz_cmp_lvl_flag
318 ,p_frz_pct_fl_tm_flag => g_pepinsplip.p_frz_pct_fl_tm_flag
319 ,p_frz_hrs_wkd_flag => g_pepinsplip.p_frz_hrs_wkd_flag
320 ,p_frz_comb_age_and_los_flag => g_pepinsplip.p_frz_comb_age_and_los_flag
321 ,p_dstr_rstcn_flag => g_pepinsplip.p_dstr_rstcn_flag
322 ,p_pct_fl_tm_val => g_pepinsplip.p_pct_fl_tm_val
323 ,p_wv_prtn_rsn_cd => g_pepinsplip.p_wv_prtn_rsn_cd
324 ,p_pl_wvd_flag => g_pepinsplip.p_pl_wvd_flag
325 ,p_rt_comp_ref_amt => g_pepinsplip.p_rt_comp_ref_amt
326 ,p_rt_cmbn_age_n_los_val => g_pepinsplip.p_rt_cmbn_age_n_los_val
327 ,p_rt_comp_ref_uom => g_pepinsplip.p_rt_comp_ref_uom
328 ,p_rt_age_val => g_pepinsplip.p_rt_age_val
329 ,p_rt_los_val => g_pepinsplip.p_rt_los_val
330 ,p_rt_hrs_wkd_val => g_pepinsplip.p_rt_hrs_wkd_val
331 ,p_rt_hrs_wkd_bndry_perd_cd => g_pepinsplip.p_rt_hrs_wkd_bndry_perd_cd
332 ,p_rt_age_uom => g_pepinsplip.p_rt_age_uom
333 ,p_rt_los_uom => g_pepinsplip.p_rt_los_uom
334 ,p_rt_pct_fl_tm_val => g_pepinsplip.p_rt_pct_fl_tm_val
335 ,p_rt_frz_los_flag => g_pepinsplip.p_rt_frz_los_flag
336 ,p_rt_frz_age_flag => g_pepinsplip.p_rt_frz_age_flag
337 ,p_rt_frz_cmp_lvl_flag => g_pepinsplip.p_rt_frz_cmp_lvl_flag
338 ,p_rt_frz_pct_fl_tm_flag => g_pepinsplip.p_rt_frz_pct_fl_tm_flag
339 ,p_rt_frz_hrs_wkd_flag => g_pepinsplip.p_rt_frz_hrs_wkd_flag
340 ,p_rt_frz_comb_age_and_los_flag => g_pepinsplip.p_rt_frz_comb_age_and_los_flag
341 ,p_once_r_cntug_cd => g_pepinsplip.p_once_r_cntug_cd
342 ,p_pl_ordr_num => g_pepinsplip.p_pl_ordr_num
343 ,p_plip_ordr_num => g_pepinsplip.p_plip_ordr_num
344 ,p_ptip_ordr_num => g_pepinsplip.p_ptip_ordr_num
345 ,p_pep_attribute_category => g_pepinsplip.p_pep_attribute_category
346 ,p_pep_attribute1 => g_pepinsplip.p_pep_attribute1
347 ,p_pep_attribute2 => g_pepinsplip.p_pep_attribute2
348 ,p_pep_attribute3 => g_pepinsplip.p_pep_attribute3
349 ,p_pep_attribute4 => g_pepinsplip.p_pep_attribute4
350 ,p_pep_attribute5 => g_pepinsplip.p_pep_attribute5
351 ,p_pep_attribute6 => g_pepinsplip.p_pep_attribute6
352 ,p_pep_attribute7 => g_pepinsplip.p_pep_attribute7
353 ,p_pep_attribute8 => g_pepinsplip.p_pep_attribute8
354 ,p_pep_attribute9 => g_pepinsplip.p_pep_attribute9
355 ,p_pep_attribute10 => g_pepinsplip.p_pep_attribute10
356 ,p_pep_attribute11 => g_pepinsplip.p_pep_attribute11
357 ,p_pep_attribute12 => g_pepinsplip.p_pep_attribute12
358 ,p_pep_attribute13 => g_pepinsplip.p_pep_attribute13
359 ,p_pep_attribute14 => g_pepinsplip.p_pep_attribute14
360 ,p_pep_attribute15 => g_pepinsplip.p_pep_attribute15
361 ,p_pep_attribute16 => g_pepinsplip.p_pep_attribute16
362 ,p_pep_attribute17 => g_pepinsplip.p_pep_attribute17
363 ,p_pep_attribute18 => g_pepinsplip.p_pep_attribute18
364 ,p_pep_attribute19 => g_pepinsplip.p_pep_attribute19
365 ,p_pep_attribute20 => g_pepinsplip.p_pep_attribute20
366 ,p_pep_attribute21 => g_pepinsplip.p_pep_attribute21
367 ,p_pep_attribute22 => g_pepinsplip.p_pep_attribute22
368 ,p_pep_attribute23 => g_pepinsplip.p_pep_attribute23
369 ,p_pep_attribute24 => g_pepinsplip.p_pep_attribute24
370 ,p_pep_attribute25 => g_pepinsplip.p_pep_attribute25
371 ,p_pep_attribute26 => g_pepinsplip.p_pep_attribute26
372 ,p_pep_attribute27 => g_pepinsplip.p_pep_attribute27
373 ,p_pep_attribute28 => g_pepinsplip.p_pep_attribute28
374 ,p_pep_attribute29 => g_pepinsplip.p_pep_attribute29
375 ,p_pep_attribute30 => g_pepinsplip.p_pep_attribute30
376 ,p_request_id => g_pepinsplip.p_request_id
377 ,p_program_application_id => g_pepinsplip.p_program_application_id
378 ,p_program_id => g_pepinsplip.p_program_id
379 ,p_program_update_date => g_pepinsplip.p_program_update_date
380 ,p_object_version_number => l_dummy_ovn
381 ,p_effective_date => g_pepinsplip.p_effective_date
382 ,p_override_validation => g_pepinsplip.p_override_validation
383 ,p_defer => false
384 );
385 --
386 -- Bug 4438430
387 -- Since creation of BEN_ELIG_PER_F at PLIP level is DEFERRED until we create BEN_ELIG_PER_F
388 -- record at PLN level, creation of BEN_ELIG_SCRE_WTG_F (PLIP) was also deferred. So now we create
389 -- BEN_ELIG_SCRE_WTG_F record for ELPROs at PLIP level.
390 -- In BEN_ELIG_SCRE_WTG_API.LOAD_SCORE_WEIGHT we store the P_SCORE_TAB in global table G_PEPINSPLIP
391 --
392 if g_pepinsplip_score_tab.count > 0
393 then
394 --
395 ben_elig_scre_wtg_api.load_score_weight
396 ( p_validate => false
397 ,p_score_tab => g_pepinsplip_score_tab /* Bug 4449745 */
398 ,p_effective_date => g_pepinsplip.p_effective_date
399 ,p_per_in_ler_id => g_pepinsplip.p_per_in_ler_id
400 ,p_elig_per_id => l_dummy_pep_id
401 ,p_elig_per_opt_id => null
402 );
403 --
404 end if;
405 --
406 g_pepinsplip := l_pepinsplip;
407 g_pepinsplip_score_tab := l_pepinsplip_score_tab; /* Bug 4449745 */
408 --
409 end if;
410 --
411 -- Issue a savepoint if operating in validation only mode
412 --
413 savepoint create_perf_Eligible_Person;
414 --
415 -- Derive maximum start and end dates
416 --
417 l_effective_start_date := p_effective_date;
418 l_effective_end_date := hr_api.g_eot;
419 --
420 -- Person
421 --
422 ben_batch_dt_api.get_personobject
423 (p_person_id => p_person_id
424 ,p_rec => l_minmax_rec
425 );
426 --
427 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
428 (p_effective_date => p_effective_date
429 ,p_parcolumn_name => 'person_id'
430 ,p_min_esd => l_minmax_rec.min_esd
431 ,p_max_eed => l_minmax_rec.max_eed
432 --
433 ,p_esd => l_effective_start_date
434 ,p_eed => l_effective_end_date
435 );
436 --
437 -- Ler
438 --
439 if p_ler_id is not null then
440 --
441 ben_batch_dt_api.get_lerobject
442 (p_ler_id => p_ler_id
443 ,p_rec => l_minmax_rec
444 );
445 --
446 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
447 (p_effective_date => p_effective_date
448 ,p_parcolumn_name => 'ler_id'
449 ,p_min_esd => l_minmax_rec.min_esd
450 ,p_max_eed => l_minmax_rec.max_eed
451 --
452 ,p_esd => l_effective_start_date
453 ,p_eed => l_effective_end_date
454 );
455 --
456 end if;
457 --
458 -- Pgm
459 --
460 if p_pgm_id is not null then
461 --
462 ben_batch_dt_api.get_pgmobject
463 (p_pgm_id => p_pgm_id
464 ,p_rec => l_minmax_rec
465 );
466 --
467 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
468 (p_effective_date => p_effective_date
469 ,p_parcolumn_name => 'pgm_id'
470 ,p_min_esd => l_minmax_rec.min_esd
471 ,p_max_eed => l_minmax_rec.max_eed
472 --
473 ,p_esd => l_effective_start_date
474 ,p_eed => l_effective_end_date
475 );
476 --
477 end if;
478 --
479 -- Ptip
480 --
481 if p_ptip_id is not null then
482 --
483 ben_batch_dt_api.get_ptipobject
484 (p_ptip_id => p_ptip_id
485 ,p_rec => l_minmax_rec
486 );
487 --
488 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
489 (p_effective_date => p_effective_date
490 ,p_parcolumn_name => 'ptip_id'
491 ,p_min_esd => l_minmax_rec.min_esd
492 ,p_max_eed => l_minmax_rec.max_eed
493 --
494 ,p_esd => l_effective_start_date
495 ,p_eed => l_effective_end_date
496 );
497 --
498 end if;
499 --
500 -- Plip
501 --
502 if p_plip_id is not null then
503 --
504 ben_batch_dt_api.get_plipobject
505 (p_plip_id => p_plip_id
506 ,p_rec => l_minmax_rec
507 );
508 --
509 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
510 (p_effective_date => p_effective_date
511 ,p_parcolumn_name => 'plip_id'
512 ,p_min_esd => l_minmax_rec.min_esd
513 ,p_max_eed => l_minmax_rec.max_eed
514 --
515 ,p_esd => l_effective_start_date
516 ,p_eed => l_effective_end_date
517 );
518 --
519 end if;
520 --
521 -- Plan
522 --
523 if p_pl_id is not null then
524 --
525 ben_batch_dt_api.get_plobject
526 (p_pl_id => p_pl_id
527 ,p_rec => l_minmax_rec
528 );
529 --
530 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
531 (p_effective_date => p_effective_date
532 ,p_parcolumn_name => 'pl_id'
533 ,p_min_esd => l_minmax_rec.min_esd
534 ,p_max_eed => l_minmax_rec.max_eed
535 --
536 ,p_esd => l_effective_start_date
537 ,p_eed => l_effective_end_date
538 );
539 --
540 end if;
541 --
542 -- Insert the row
543 --
544 -- Set the object version number for the insert
545 --
546 l_object_version_number := 1;
547 --
548 ben_pep_shd.g_api_dml := true; -- Set the api dml status
549 --
550 -- Insert the row into: ben_elig_per_f
551 --
552 hr_utility.set_location('Insert: '||l_proc, 5);
553 insert into ben_elig_per_f
554 (
555 elig_per_id,
556 effective_start_date,
557 effective_end_date,
558 business_group_id,
559 pl_id,
560 pgm_id,
561 plip_id,
562 ptip_id,
563 ler_id,
564 person_id,
565 per_in_ler_id,
566 dpnt_othr_pl_cvrd_rl_flag,
567 prtn_ovridn_thru_dt,
568 pl_key_ee_flag,
569 pl_hghly_compd_flag,
570 elig_flag,
571 comp_ref_amt,
572 cmbn_age_n_los_val,
573 comp_ref_uom,
574 age_val,
575 los_val,
576 prtn_end_dt,
577 prtn_strt_dt,
578 wait_perd_cmpltn_dt,
579 wait_perd_strt_dt ,
580 wv_ctfn_typ_cd,
581 hrs_wkd_val,
582 hrs_wkd_bndry_perd_cd,
583 prtn_ovridn_flag,
584 no_mx_prtn_ovrid_thru_flag,
585 prtn_ovridn_rsn_cd,
586 age_uom,
587 los_uom,
588 ovrid_svc_dt,
589 inelg_rsn_cd,
590 frz_los_flag,
591 frz_age_flag,
592 frz_cmp_lvl_flag,
593 frz_pct_fl_tm_flag,
594 frz_hrs_wkd_flag,
595 frz_comb_age_and_los_flag,
596 dstr_rstcn_flag,
597 pct_fl_tm_val,
598 wv_prtn_rsn_cd,
599 pl_wvd_flag,
600 rt_comp_ref_amt,
601 rt_cmbn_age_n_los_val,
602 rt_comp_ref_uom,
603 rt_age_val,
604 rt_los_val,
605 rt_hrs_wkd_val,
606 rt_hrs_wkd_bndry_perd_cd,
607 rt_age_uom,
608 rt_los_uom,
609 rt_pct_fl_tm_val,
610 rt_frz_los_flag,
611 rt_frz_age_flag,
612 rt_frz_cmp_lvl_flag,
613 rt_frz_pct_fl_tm_flag,
614 rt_frz_hrs_wkd_flag,
615 rt_frz_comb_age_and_los_flag,
616 once_r_cntug_cd,
617 pep_attribute_category,
618 pep_attribute1,
619 pep_attribute2,
620 pep_attribute3,
621 pep_attribute4,
622 pep_attribute5,
623 pep_attribute6,
624 pep_attribute7,
625 pep_attribute8,
626 pep_attribute9,
627 pep_attribute10,
628 pep_attribute11,
629 pep_attribute12,
630 pep_attribute13,
631 pep_attribute14,
632 pep_attribute15,
633 pep_attribute16,
634 pep_attribute17,
635 pep_attribute18,
636 pep_attribute19,
637 pep_attribute20,
638 pep_attribute21,
639 pep_attribute22,
640 pep_attribute23,
641 pep_attribute24,
642 pep_attribute25,
643 pep_attribute26,
644 pep_attribute27,
645 pep_attribute28,
646 pep_attribute29,
647 pep_attribute30,
648 request_id,
649 program_application_id,
650 program_id,
651 program_update_date,
652 object_version_number,
653 created_by,
654 creation_date,
655 last_update_date,
656 last_updated_by,
657 last_update_login
658 )
659 Values
660 (
661 ben_elig_per_f_s.nextval,
662 l_effective_start_date,
663 l_effective_end_date,
664 p_business_group_id,
665 p_pl_id,
666 p_pgm_id,
667 p_plip_id,
668 p_ptip_id,
669 p_ler_id,
670 p_person_id,
671 p_per_in_ler_id,
672 p_dpnt_othr_pl_cvrd_rl_flag,
673 p_prtn_ovridn_thru_dt,
674 p_pl_key_ee_flag,
675 p_pl_hghly_compd_flag,
676 p_elig_flag,
677 p_comp_ref_amt,
678 p_cmbn_age_n_los_val,
679 p_comp_ref_uom,
680 p_age_val,
681 p_los_val,
682 p_prtn_end_dt,
683 p_prtn_strt_dt,
684 p_wait_perd_cmpltn_dt,
685 p_wait_perd_strt_dt ,
686 p_wv_ctfn_typ_cd,
687 p_hrs_wkd_val,
688 p_hrs_wkd_bndry_perd_cd,
689 p_prtn_ovridn_flag,
690 p_no_mx_prtn_ovrid_thru_flag,
691 p_prtn_ovridn_rsn_cd,
692 p_age_uom,
693 p_los_uom,
694 p_ovrid_svc_dt,
695 p_inelg_rsn_cd,
696 p_frz_los_flag,
697 p_frz_age_flag,
698 p_frz_cmp_lvl_flag,
699 p_frz_pct_fl_tm_flag,
700 p_frz_hrs_wkd_flag,
701 p_frz_comb_age_and_los_flag,
702 p_dstr_rstcn_flag,
703 p_pct_fl_tm_val,
704 p_wv_prtn_rsn_cd,
705 p_pl_wvd_flag,
706 p_rt_comp_ref_amt,
707 p_rt_cmbn_age_n_los_val,
708 p_rt_comp_ref_uom,
709 p_rt_age_val,
710 p_rt_los_val,
711 p_rt_hrs_wkd_val,
712 p_rt_hrs_wkd_bndry_perd_cd,
713 p_rt_age_uom,
714 p_rt_los_uom,
715 p_rt_pct_fl_tm_val,
716 p_rt_frz_los_flag,
717 p_rt_frz_age_flag,
718 p_rt_frz_cmp_lvl_flag,
719 p_rt_frz_pct_fl_tm_flag,
720 p_rt_frz_hrs_wkd_flag,
721 p_rt_frz_comb_age_and_los_flag,
722 p_once_r_cntug_cd,
723 p_pep_attribute_category,
724 p_pep_attribute1,
725 p_pep_attribute2,
726 p_pep_attribute3,
727 p_pep_attribute4,
728 p_pep_attribute5,
729 p_pep_attribute6,
730 p_pep_attribute7,
731 p_pep_attribute8,
732 p_pep_attribute9,
733 p_pep_attribute10,
734 p_pep_attribute11,
735 p_pep_attribute12,
736 p_pep_attribute13,
737 p_pep_attribute14,
738 p_pep_attribute15,
739 p_pep_attribute16,
740 p_pep_attribute17,
741 p_pep_attribute18,
742 p_pep_attribute19,
743 p_pep_attribute20,
744 p_pep_attribute21,
745 p_pep_attribute22,
746 p_pep_attribute23,
747 p_pep_attribute24,
748 p_pep_attribute25,
749 p_pep_attribute26,
750 p_pep_attribute27,
751 p_pep_attribute28,
752 p_pep_attribute29,
753 p_pep_attribute30,
754 p_request_id,
755 p_program_application_id,
756 p_program_id,
757 p_program_update_date,
758 l_object_version_number,
759 l_created_by,
760 l_creation_date,
761 l_last_update_date,
762 l_last_updated_by,
763 l_last_update_login
764 ) RETURNING elig_per_id into l_elig_per_id;
765 hr_utility.set_location('Dn Insert: '||l_proc, 5);
766 --
767 ben_pep_shd.g_api_dml := false; -- Unset the api dml status
768 --
769 -- When in validation only mode raise the Validate_Enabled exception
770 --
771 if p_validate then
772 raise hr_api.validate_enabled;
773 end if;
774 --
775 -- Set all output arguments
776 --
777 p_elig_per_id := l_elig_per_id;
778 p_effective_start_date := l_effective_start_date;
779 p_effective_end_date := l_effective_end_date;
780 p_object_version_number := l_object_version_number;
781 --
782 hr_utility.set_location(' Leaving:'||l_proc, 70);
783 --
784 exception
785 --
786 when hr_api.validate_enabled then
787 --
788 -- As the Validate_Enabled exception has been raised
789 -- we must rollback to the savepoint
790 --
791 ROLLBACK TO create_perf_Eligible_Person;
792 --
793 -- Only set output warning arguments
794 -- (Any key or derived arguments must be set to null
795 -- when validation only mode is being used.)
796 --
797 p_elig_per_id := null;
798 p_effective_start_date := null;
799 p_effective_end_date := null;
800 p_object_version_number := null;
801 hr_utility.set_location(' Leaving:'||l_proc, 80);
802 --
803 when others then
804 --
805 -- A validation or unexpected error has occured
806 --
807 ROLLBACK TO create_perf_Eligible_Person;
808 p_effective_start_date := null;
809 p_effective_end_date := null;
810 p_object_version_number := l_object_version_number;
811
812 raise;
813 --
814 end create_perf_Eligible_Person;
815 --
816 Procedure convert_defs
817 (p_rec in out nocopy ben_pep_shd.g_rec_type
818 )
819 is
820 --
821 l_proc varchar2(72) := g_package||'convert_defs';
822 --
823 Begin
824 --
825 hr_utility.set_location('Entering:'||l_proc, 5);
826 --
827 -- We must now examine each argument value in the
828 -- p_rec plsql record structure
829 -- to see if a system default is being used. If a system default
830 -- is being used then we must set to the 'current' argument value.
831 --
832 If (p_rec.business_group_id = hr_api.g_number) then
833 p_rec.business_group_id :=
834 ben_pep_shd.g_old_rec.business_group_id;
835 End If;
836 If (p_rec.pl_id = hr_api.g_number) then
837 p_rec.pl_id :=
838 ben_pep_shd.g_old_rec.pl_id;
839 End If;
840 If (p_rec.pgm_id = hr_api.g_number) then
841 p_rec.pgm_id :=
842 ben_pep_shd.g_old_rec.pgm_id;
843 End If;
844 If (p_rec.plip_id = hr_api.g_number) then
845 p_rec.plip_id :=
846 ben_pep_shd.g_old_rec.plip_id;
847 End If;
848 If (p_rec.ptip_id = hr_api.g_number) then
849 p_rec.ptip_id :=
850 ben_pep_shd.g_old_rec.ptip_id;
851 End If;
852 If (p_rec.ler_id = hr_api.g_number) then
853 p_rec.ler_id :=
854 ben_pep_shd.g_old_rec.ler_id;
855 End If;
856 If (p_rec.person_id = hr_api.g_number) then
857 p_rec.person_id :=
858 ben_pep_shd.g_old_rec.person_id;
859 End If;
860 If (p_rec.per_in_ler_id = hr_api.g_number) then
861 p_rec.per_in_ler_id :=
862 ben_pep_shd.g_old_rec.per_in_ler_id;
863 End If;
864 If (p_rec.dpnt_othr_pl_cvrd_rl_flag = hr_api.g_varchar2) then
865 p_rec.dpnt_othr_pl_cvrd_rl_flag :=
866 ben_pep_shd.g_old_rec.dpnt_othr_pl_cvrd_rl_flag;
867 End If;
868 If (p_rec.prtn_ovridn_thru_dt = hr_api.g_date) then
869 p_rec.prtn_ovridn_thru_dt :=
870 ben_pep_shd.g_old_rec.prtn_ovridn_thru_dt;
871 End If;
872 If (p_rec.pl_key_ee_flag = hr_api.g_varchar2) then
873 p_rec.pl_key_ee_flag :=
874 ben_pep_shd.g_old_rec.pl_key_ee_flag;
875 End If;
876 If (p_rec.pl_hghly_compd_flag = hr_api.g_varchar2) then
877 p_rec.pl_hghly_compd_flag :=
878 ben_pep_shd.g_old_rec.pl_hghly_compd_flag;
879 End If;
880 If (p_rec.elig_flag = hr_api.g_varchar2) then
881 p_rec.elig_flag :=
882 ben_pep_shd.g_old_rec.elig_flag;
883 End If;
884 If (p_rec.comp_ref_amt = hr_api.g_number) then
885 p_rec.comp_ref_amt :=
886 ben_pep_shd.g_old_rec.comp_ref_amt;
887 End If;
888 If (p_rec.cmbn_age_n_los_val = hr_api.g_number) then
889 p_rec.cmbn_age_n_los_val :=
890 ben_pep_shd.g_old_rec.cmbn_age_n_los_val;
891 End If;
892 If (p_rec.comp_ref_uom = hr_api.g_varchar2) then
893 p_rec.comp_ref_uom :=
894 ben_pep_shd.g_old_rec.comp_ref_uom;
895 End If;
896 If (p_rec.age_val = hr_api.g_number) then
897 p_rec.age_val :=
898 ben_pep_shd.g_old_rec.age_val;
899 End If;
900 If (p_rec.los_val = hr_api.g_number) then
901 p_rec.los_val :=
902 ben_pep_shd.g_old_rec.los_val;
903 End If;
904 If (p_rec.prtn_end_dt = hr_api.g_date) then
905 p_rec.prtn_end_dt :=
906 ben_pep_shd.g_old_rec.prtn_end_dt;
907 End If;
908 If (p_rec.prtn_strt_dt = hr_api.g_date) then
909 p_rec.prtn_strt_dt :=
910 ben_pep_shd.g_old_rec.prtn_strt_dt;
911 End If;
912 If (p_rec.wait_perd_cmpltn_dt = hr_api.g_date) then
913 p_rec.wait_perd_cmpltn_dt :=
914 ben_pep_shd.g_old_rec.wait_perd_cmpltn_dt;
915 End If;
916
917 If (p_rec.wait_perd_strt_dt = hr_api.g_date) then
918 p_rec.wait_perd_strt_dt :=
919 ben_pep_shd.g_old_rec.wait_perd_strt_dt;
920 End If;
921 If (p_rec.wv_ctfn_typ_cd = hr_api.g_varchar2) then
922 p_rec.wv_ctfn_typ_cd :=
923 ben_pep_shd.g_old_rec.wv_ctfn_typ_cd;
924 End If;
925 If (p_rec.hrs_wkd_val = hr_api.g_number) then
926 p_rec.hrs_wkd_val :=
927 ben_pep_shd.g_old_rec.hrs_wkd_val;
928 End If;
929 If (p_rec.hrs_wkd_bndry_perd_cd = hr_api.g_varchar2) then
930 p_rec.hrs_wkd_bndry_perd_cd :=
931 ben_pep_shd.g_old_rec.hrs_wkd_bndry_perd_cd;
932 End If;
933 If (p_rec.prtn_ovridn_flag = hr_api.g_varchar2) then
934 p_rec.prtn_ovridn_flag :=
935 ben_pep_shd.g_old_rec.prtn_ovridn_flag;
936 End If;
937 If (p_rec.no_mx_prtn_ovrid_thru_flag = hr_api.g_varchar2) then
938 p_rec.no_mx_prtn_ovrid_thru_flag :=
939 ben_pep_shd.g_old_rec.no_mx_prtn_ovrid_thru_flag;
940 End If;
941 If (p_rec.prtn_ovridn_rsn_cd = hr_api.g_varchar2) then
942 p_rec.prtn_ovridn_rsn_cd :=
943 ben_pep_shd.g_old_rec.prtn_ovridn_rsn_cd;
944 End If;
945 If (p_rec.age_uom = hr_api.g_varchar2) then
946 p_rec.age_uom :=
947 ben_pep_shd.g_old_rec.age_uom;
948 End If;
949 If (p_rec.los_uom = hr_api.g_varchar2) then
950 p_rec.los_uom :=
951 ben_pep_shd.g_old_rec.los_uom;
952 End If;
953 If (p_rec.ovrid_svc_dt = hr_api.g_date) then
954 p_rec.ovrid_svc_dt :=
955 ben_pep_shd.g_old_rec.ovrid_svc_dt;
956 End If;
957 If (p_rec.inelg_rsn_cd = hr_api.g_varchar2) then
958 p_rec.inelg_rsn_cd :=
959 ben_pep_shd.g_old_rec.inelg_rsn_cd;
960 End If;
961 If (p_rec.frz_los_flag = hr_api.g_varchar2) then
962 p_rec.frz_los_flag :=
963 ben_pep_shd.g_old_rec.frz_los_flag;
964 End If;
965 If (p_rec.frz_age_flag = hr_api.g_varchar2) then
966 p_rec.frz_age_flag :=
967 ben_pep_shd.g_old_rec.frz_age_flag;
968 End If;
969 If (p_rec.frz_cmp_lvl_flag = hr_api.g_varchar2) then
970 p_rec.frz_cmp_lvl_flag :=
971 ben_pep_shd.g_old_rec.frz_cmp_lvl_flag;
972 End If;
973 If (p_rec.frz_pct_fl_tm_flag = hr_api.g_varchar2) then
974 p_rec.frz_pct_fl_tm_flag :=
975 ben_pep_shd.g_old_rec.frz_pct_fl_tm_flag;
976 End If;
977 If (p_rec.frz_hrs_wkd_flag = hr_api.g_varchar2) then
978 p_rec.frz_hrs_wkd_flag :=
979 ben_pep_shd.g_old_rec.frz_hrs_wkd_flag;
980 End If;
981 If (p_rec.frz_comb_age_and_los_flag = hr_api.g_varchar2) then
982 p_rec.frz_comb_age_and_los_flag :=
983 ben_pep_shd.g_old_rec.frz_comb_age_and_los_flag;
984 End If;
985 If (p_rec.dstr_rstcn_flag = hr_api.g_varchar2) then
986 p_rec.dstr_rstcn_flag :=
987 ben_pep_shd.g_old_rec.dstr_rstcn_flag;
988 End If;
989 If (p_rec.pct_fl_tm_val = hr_api.g_number) then
990 p_rec.pct_fl_tm_val :=
991 ben_pep_shd.g_old_rec.pct_fl_tm_val;
992 End If;
993 If (p_rec.wv_prtn_rsn_cd = hr_api.g_varchar2) then
994 p_rec.wv_prtn_rsn_cd :=
995 ben_pep_shd.g_old_rec.wv_prtn_rsn_cd;
996 End If;
997 If (p_rec.pl_wvd_flag = hr_api.g_varchar2) then
998 p_rec.pl_wvd_flag :=
999 ben_pep_shd.g_old_rec.pl_wvd_flag;
1000 End If;
1001 If (p_rec.rt_comp_ref_amt = hr_api.g_number) then
1002 p_rec.rt_comp_ref_amt :=
1003 ben_pep_shd.g_old_rec.rt_comp_ref_amt;
1004 End If;
1005 If (p_rec.rt_cmbn_age_n_los_val = hr_api.g_number) then
1006 p_rec.rt_cmbn_age_n_los_val :=
1007 ben_pep_shd.g_old_rec.rt_cmbn_age_n_los_val;
1008 End If;
1009 If (p_rec.rt_comp_ref_uom = hr_api.g_varchar2) then
1010 p_rec.rt_comp_ref_uom :=
1011 ben_pep_shd.g_old_rec.rt_comp_ref_uom;
1012 End If;
1013 If (p_rec.rt_age_val = hr_api.g_number) then
1014 p_rec.rt_age_val :=
1015 ben_pep_shd.g_old_rec.rt_age_val;
1016 End If;
1017 If (p_rec.rt_los_val = hr_api.g_number) then
1018 p_rec.rt_los_val :=
1019 ben_pep_shd.g_old_rec.rt_los_val;
1020 End If;
1021 If (p_rec.rt_hrs_wkd_val = hr_api.g_number) then
1022 p_rec.rt_hrs_wkd_val :=
1023 ben_pep_shd.g_old_rec.rt_hrs_wkd_val;
1024 End If;
1025 If (p_rec.rt_hrs_wkd_bndry_perd_cd = hr_api.g_varchar2) then
1026 p_rec.rt_hrs_wkd_bndry_perd_cd :=
1027 ben_pep_shd.g_old_rec.rt_hrs_wkd_bndry_perd_cd;
1028 End If;
1029 If (p_rec.rt_age_uom = hr_api.g_varchar2) then
1030 p_rec.rt_age_uom :=
1031 ben_pep_shd.g_old_rec.rt_age_uom;
1032 End If;
1033 If (p_rec.rt_los_uom = hr_api.g_varchar2) then
1034 p_rec.rt_los_uom :=
1035 ben_pep_shd.g_old_rec.rt_los_uom;
1036 End If;
1037 If (p_rec.rt_pct_fl_tm_val = hr_api.g_number) then
1038 p_rec.rt_pct_fl_tm_val :=
1039 ben_pep_shd.g_old_rec.rt_pct_fl_tm_val;
1040 End If;
1041 If (p_rec.rt_frz_los_flag = hr_api.g_varchar2) then
1042 p_rec.rt_frz_los_flag :=
1043 ben_pep_shd.g_old_rec.rt_frz_los_flag;
1044 End If;
1045 If (p_rec.rt_frz_age_flag = hr_api.g_varchar2) then
1046 p_rec.rt_frz_age_flag :=
1047 ben_pep_shd.g_old_rec.rt_frz_age_flag;
1048 End If;
1049 If (p_rec.rt_frz_cmp_lvl_flag = hr_api.g_varchar2) then
1050 p_rec.rt_frz_cmp_lvl_flag :=
1051 ben_pep_shd.g_old_rec.rt_frz_cmp_lvl_flag;
1052 End If;
1053 If (p_rec.rt_frz_pct_fl_tm_flag = hr_api.g_varchar2) then
1054 p_rec.rt_frz_pct_fl_tm_flag :=
1055 ben_pep_shd.g_old_rec.rt_frz_pct_fl_tm_flag;
1056 End If;
1057 If (p_rec.rt_frz_hrs_wkd_flag = hr_api.g_varchar2) then
1058 p_rec.rt_frz_hrs_wkd_flag :=
1059 ben_pep_shd.g_old_rec.rt_frz_hrs_wkd_flag;
1060 End If;
1061 If (p_rec.rt_frz_comb_age_and_los_flag = hr_api.g_varchar2) then
1062 p_rec.rt_frz_comb_age_and_los_flag :=
1063 ben_pep_shd.g_old_rec.rt_frz_comb_age_and_los_flag;
1064 End If;
1065 If (p_rec.once_r_cntug_cd = hr_api.g_varchar2) then
1066 p_rec.once_r_cntug_cd :=
1067 ben_pep_shd.g_old_rec.once_r_cntug_cd;
1068 End If;
1069 If (p_rec.pl_ordr_num = hr_api.g_number) then
1070 p_rec.pl_ordr_num :=
1071 ben_pep_shd.g_old_rec.pl_ordr_num;
1072 End If;
1073 If (p_rec.plip_ordr_num = hr_api.g_number) then
1074 p_rec.plip_ordr_num :=
1075 ben_pep_shd.g_old_rec.plip_ordr_num;
1076 End If;
1077 If (p_rec.ptip_ordr_num = hr_api.g_number) then
1078 p_rec.ptip_ordr_num :=
1079 ben_pep_shd.g_old_rec.ptip_ordr_num;
1080 End If;
1081 If (p_rec.pep_attribute_category = hr_api.g_varchar2) then
1082 p_rec.pep_attribute_category :=
1083 ben_pep_shd.g_old_rec.pep_attribute_category;
1084 End If;
1085 If (p_rec.pep_attribute1 = hr_api.g_varchar2) then
1086 p_rec.pep_attribute1 :=
1087 ben_pep_shd.g_old_rec.pep_attribute1;
1088 End If;
1089 If (p_rec.pep_attribute2 = hr_api.g_varchar2) then
1090 p_rec.pep_attribute2 :=
1091 ben_pep_shd.g_old_rec.pep_attribute2;
1092 End If;
1093 If (p_rec.pep_attribute3 = hr_api.g_varchar2) then
1094 p_rec.pep_attribute3 :=
1095 ben_pep_shd.g_old_rec.pep_attribute3;
1096 End If;
1097 If (p_rec.pep_attribute4 = hr_api.g_varchar2) then
1098 p_rec.pep_attribute4 :=
1099 ben_pep_shd.g_old_rec.pep_attribute4;
1100 End If;
1101 If (p_rec.pep_attribute5 = hr_api.g_varchar2) then
1102 p_rec.pep_attribute5 :=
1103 ben_pep_shd.g_old_rec.pep_attribute5;
1104 End If;
1105 If (p_rec.pep_attribute6 = hr_api.g_varchar2) then
1106 p_rec.pep_attribute6 :=
1107 ben_pep_shd.g_old_rec.pep_attribute6;
1108 End If;
1109 If (p_rec.pep_attribute7 = hr_api.g_varchar2) then
1110 p_rec.pep_attribute7 :=
1111 ben_pep_shd.g_old_rec.pep_attribute7;
1112 End If;
1113 If (p_rec.pep_attribute8 = hr_api.g_varchar2) then
1114 p_rec.pep_attribute8 :=
1115 ben_pep_shd.g_old_rec.pep_attribute8;
1116 End If;
1117 If (p_rec.pep_attribute9 = hr_api.g_varchar2) then
1118 p_rec.pep_attribute9 :=
1119 ben_pep_shd.g_old_rec.pep_attribute9;
1120 End If;
1121 If (p_rec.pep_attribute10 = hr_api.g_varchar2) then
1122 p_rec.pep_attribute10 :=
1123 ben_pep_shd.g_old_rec.pep_attribute10;
1124 End If;
1125 If (p_rec.pep_attribute11 = hr_api.g_varchar2) then
1126 p_rec.pep_attribute11 :=
1127 ben_pep_shd.g_old_rec.pep_attribute11;
1128 End If;
1129 If (p_rec.pep_attribute12 = hr_api.g_varchar2) then
1130 p_rec.pep_attribute12 :=
1131 ben_pep_shd.g_old_rec.pep_attribute12;
1132 End If;
1133 If (p_rec.pep_attribute13 = hr_api.g_varchar2) then
1134 p_rec.pep_attribute13 :=
1135 ben_pep_shd.g_old_rec.pep_attribute13;
1136 End If;
1137 If (p_rec.pep_attribute14 = hr_api.g_varchar2) then
1138 p_rec.pep_attribute14 :=
1139 ben_pep_shd.g_old_rec.pep_attribute14;
1140 End If;
1141 If (p_rec.pep_attribute15 = hr_api.g_varchar2) then
1142 p_rec.pep_attribute15 :=
1143 ben_pep_shd.g_old_rec.pep_attribute15;
1144 End If;
1145 If (p_rec.pep_attribute16 = hr_api.g_varchar2) then
1146 p_rec.pep_attribute16 :=
1147 ben_pep_shd.g_old_rec.pep_attribute16;
1148 End If;
1149 If (p_rec.pep_attribute17 = hr_api.g_varchar2) then
1150 p_rec.pep_attribute17 :=
1151 ben_pep_shd.g_old_rec.pep_attribute17;
1152 End If;
1153 If (p_rec.pep_attribute18 = hr_api.g_varchar2) then
1154 p_rec.pep_attribute18 :=
1155 ben_pep_shd.g_old_rec.pep_attribute18;
1156 End If;
1157 If (p_rec.pep_attribute19 = hr_api.g_varchar2) then
1158 p_rec.pep_attribute19 :=
1159 ben_pep_shd.g_old_rec.pep_attribute19;
1160 End If;
1161 If (p_rec.pep_attribute20 = hr_api.g_varchar2) then
1162 p_rec.pep_attribute20 :=
1163 ben_pep_shd.g_old_rec.pep_attribute20;
1164 End If;
1165 If (p_rec.pep_attribute21 = hr_api.g_varchar2) then
1166 p_rec.pep_attribute21 :=
1167 ben_pep_shd.g_old_rec.pep_attribute21;
1168 End If;
1169 If (p_rec.pep_attribute22 = hr_api.g_varchar2) then
1170 p_rec.pep_attribute22 :=
1171 ben_pep_shd.g_old_rec.pep_attribute22;
1172 End If;
1173 If (p_rec.pep_attribute23 = hr_api.g_varchar2) then
1174 p_rec.pep_attribute23 :=
1175 ben_pep_shd.g_old_rec.pep_attribute23;
1176 End If;
1177 If (p_rec.pep_attribute24 = hr_api.g_varchar2) then
1178 p_rec.pep_attribute24 :=
1179 ben_pep_shd.g_old_rec.pep_attribute24;
1180 End If;
1181 If (p_rec.pep_attribute25 = hr_api.g_varchar2) then
1182 p_rec.pep_attribute25 :=
1183 ben_pep_shd.g_old_rec.pep_attribute25;
1184 End If;
1185 If (p_rec.pep_attribute26 = hr_api.g_varchar2) then
1186 p_rec.pep_attribute26 :=
1187 ben_pep_shd.g_old_rec.pep_attribute26;
1188 End If;
1189 If (p_rec.pep_attribute27 = hr_api.g_varchar2) then
1190 p_rec.pep_attribute27 :=
1191 ben_pep_shd.g_old_rec.pep_attribute27;
1192 End If;
1193 If (p_rec.pep_attribute28 = hr_api.g_varchar2) then
1194 p_rec.pep_attribute28 :=
1195 ben_pep_shd.g_old_rec.pep_attribute28;
1196 End If;
1197 If (p_rec.pep_attribute29 = hr_api.g_varchar2) then
1198 p_rec.pep_attribute29 :=
1199 ben_pep_shd.g_old_rec.pep_attribute29;
1200 End If;
1201 If (p_rec.pep_attribute30 = hr_api.g_varchar2) then
1202 p_rec.pep_attribute30 :=
1203 ben_pep_shd.g_old_rec.pep_attribute30;
1204 End If;
1205 If (p_rec.request_id = hr_api.g_number) then
1206 p_rec.request_id :=
1207 ben_pep_shd.g_old_rec.request_id;
1208 End If;
1209 If (p_rec.program_application_id = hr_api.g_number) then
1210 p_rec.program_application_id :=
1211 ben_pep_shd.g_old_rec.program_application_id;
1212 End If;
1213 If (p_rec.program_id = hr_api.g_number) then
1214 p_rec.program_id :=
1215 ben_pep_shd.g_old_rec.program_id;
1216 End If;
1217 If (p_rec.program_update_date = hr_api.g_date) then
1218 p_rec.program_update_date :=
1219 ben_pep_shd.g_old_rec.program_update_date;
1220 End If;
1221 --
1222 hr_utility.set_location(' Leaving:'||l_proc, 10);
1223 --
1224 End convert_defs;
1225 --
1226 -- ----------------------------------------------------------------------------
1227 -- |-----------------------------< perf_lck >---------------------------------|
1228 -- ----------------------------------------------------------------------------
1229 --
1230 procedure perf_lck
1231 (p_elig_per_id in number
1232 ,p_object_version_number in number
1233 ,p_effective_date in date
1234 ,p_datetrack_mode in varchar2
1235 ,p_validation_start_date out nocopy date
1236 ,p_validation_end_date out nocopy date
1237 )
1238 is
1239 --
1240 -- Declare cursors and local variables
1241 --
1242 l_proc varchar2(72) := g_package||'perf_lck';
1243 --
1244 l_validation_start_date date;
1245 l_validation_end_date date;
1246 --
1247 l_object_invalid exception;
1248 --
1249 Cursor C_Sel1
1250 is
1251 select
1252 elig_per_id,
1253 effective_start_date,
1254 effective_end_date,
1255 business_group_id,
1256 pl_id,
1257 pgm_id,
1258 plip_id,
1259 ptip_id,
1260 ler_id,
1261 person_id,
1262 per_in_ler_id,
1263 dpnt_othr_pl_cvrd_rl_flag,
1264 prtn_ovridn_thru_dt,
1265 pl_key_ee_flag,
1266 pl_hghly_compd_flag,
1267 elig_flag,
1268 comp_ref_amt,
1269 cmbn_age_n_los_val,
1270 comp_ref_uom,
1271 age_val,
1272 los_val,
1273 prtn_end_dt,
1274 prtn_strt_dt,
1275 wait_perd_cmpltn_dt,
1276 wait_perd_strt_dt,
1277 wv_ctfn_typ_cd,
1278 hrs_wkd_val,
1279 hrs_wkd_bndry_perd_cd,
1280 prtn_ovridn_flag,
1281 no_mx_prtn_ovrid_thru_flag,
1282 prtn_ovridn_rsn_cd,
1283 age_uom,
1284 los_uom,
1285 ovrid_svc_dt,
1286 inelg_rsn_cd,
1287 frz_los_flag,
1288 frz_age_flag,
1289 frz_cmp_lvl_flag,
1290 frz_pct_fl_tm_flag,
1291 frz_hrs_wkd_flag,
1292 frz_comb_age_and_los_flag,
1293 dstr_rstcn_flag,
1294 pct_fl_tm_val,
1295 wv_prtn_rsn_cd,
1296 pl_wvd_flag,
1297 rt_comp_ref_amt,
1298 rt_cmbn_age_n_los_val,
1299 rt_comp_ref_uom,
1300 rt_age_val,
1301 rt_los_val,
1302 rt_hrs_wkd_val,
1303 rt_hrs_wkd_bndry_perd_cd,
1304 rt_age_uom,
1305 rt_los_uom,
1306 rt_pct_fl_tm_val,
1307 rt_frz_los_flag,
1308 rt_frz_age_flag,
1309 rt_frz_cmp_lvl_flag,
1310 rt_frz_pct_fl_tm_flag,
1311 rt_frz_hrs_wkd_flag,
1312 rt_frz_comb_age_and_los_flag,
1313 once_r_cntug_cd,
1314 pl_ordr_num,
1315 plip_ordr_num,
1316 ptip_ordr_num,
1317 pep_attribute_category,
1318 pep_attribute1,
1319 pep_attribute2,
1320 pep_attribute3,
1321 pep_attribute4,
1322 pep_attribute5,
1323 pep_attribute6,
1324 pep_attribute7,
1325 pep_attribute8,
1326 pep_attribute9,
1327 pep_attribute10,
1328 pep_attribute11,
1329 pep_attribute12,
1330 pep_attribute13,
1331 pep_attribute14,
1332 pep_attribute15,
1333 pep_attribute16,
1334 pep_attribute17,
1335 pep_attribute18,
1336 pep_attribute19,
1337 pep_attribute20,
1338 pep_attribute21,
1339 pep_attribute22,
1340 pep_attribute23,
1341 pep_attribute24,
1342 pep_attribute25,
1343 pep_attribute26,
1344 pep_attribute27,
1345 pep_attribute28,
1346 pep_attribute29,
1347 pep_attribute30,
1348 request_id,
1349 program_application_id,
1350 program_id,
1351 program_update_date,
1352 object_version_number
1353 from ben_elig_per_f
1354 where elig_per_id = p_elig_per_id
1355 and p_effective_date
1356 between effective_start_date and effective_end_date;
1357 --
1358 begin
1359 --
1360 -- Check to ensure the datetrack mode is not INSERT.
1361 --
1362 If (p_datetrack_mode <> 'INSERT') then
1363 --
1364 -- We must select and lock the current row.
1365 --
1366 Open C_Sel1;
1367 Fetch C_Sel1 Into ben_pep_shd.g_old_rec;
1368 If C_Sel1%notfound then
1369 Close C_Sel1;
1370 --
1371 -- The primary key is invalid therefore we must error
1372 --
1373 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1374 fnd_message.raise_error;
1375 End If;
1376 Close C_Sel1;
1377 If (p_object_version_number <> ben_pep_shd.g_old_rec.object_version_number)
1378 then
1379 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
1380 fnd_message.raise_error;
1381 End If;
1382 hr_utility.set_location(l_proc, 15);
1383 --
1384 -- Validate the datetrack mode mode getting the validation start
1385 -- and end dates for the specified datetrack operation.
1386 --
1387 ben_batch_dt_api.validate_dt_mode_pep
1388 (p_effective_date => p_effective_date
1389 ,p_datetrack_mode => p_datetrack_mode
1390 ,p_elig_per_id => p_elig_per_id
1391 --
1392 ,p_validation_start_date => l_validation_start_date
1393 ,p_validation_end_date => l_validation_end_date
1394 );
1395 --
1396 Else
1397 --
1398 -- We are doing a datetrack 'INSERT' which is illegal within this
1399 -- procedure therefore we must error (note: to lck on insert the
1400 -- private procedure ins_lck should be called).
1401 --
1402 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1403 fnd_message.set_token('PROCEDURE', l_proc);
1404 fnd_message.set_token('STEP','20');
1405 fnd_message.raise_error;
1406 --
1407 End If;
1408 --
1409 -- Set the validation start and end date OUT arguments
1410 --
1411 p_validation_start_date := l_validation_start_date;
1412 p_validation_end_date := l_validation_end_date;
1413 --
1414 hr_utility.set_location(' Leaving:'||l_proc, 30);
1415 --
1416 Exception
1417 When HR_Api.Object_Locked then
1418 --
1419 -- The object is locked therefore we need to supply a meaningful
1420 -- error message.
1421 --
1422 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1423 fnd_message.set_token('TABLE_NAME', 'ben_elig_per_f');
1424 fnd_message.raise_error;
1425 --
1426 When l_object_invalid then
1427 --
1428 -- The object doesn't exist or is invalid
1429 --
1430 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
1431 fnd_message.set_token('TABLE_NAME', 'ben_elig_per_f');
1432 fnd_message.raise_error;
1433 --
1434 End perf_lck;
1435 --
1436 -- ----------------------------------------------------------------------------
1437 -- |-------------------< update_perf_Eligible_Person >------------------------|
1438 -- ----------------------------------------------------------------------------
1439 --
1440 procedure update_perf_Eligible_Person
1441 (p_validate in boolean default false
1442 ,p_elig_per_id in number
1443 ,p_effective_start_date out nocopy date
1444 ,p_effective_end_date out nocopy date
1445 ,p_business_group_id in number default hr_api.g_number
1446 ,p_pl_id in number default hr_api.g_number
1447 ,p_pgm_id in number default hr_api.g_number
1448 ,p_plip_id in number default hr_api.g_number
1449 ,p_ptip_id in number default hr_api.g_number
1450 ,p_ler_id in number default hr_api.g_number
1451 ,p_person_id in number default hr_api.g_number
1452 ,p_per_in_ler_id in number default hr_api.g_number
1453 ,p_dpnt_othr_pl_cvrd_rl_flag in varchar2 default hr_api.g_varchar2
1454 ,p_prtn_ovridn_thru_dt in date default hr_api.g_date
1455 ,p_pl_key_ee_flag in varchar2 default hr_api.g_varchar2
1456 ,p_pl_hghly_compd_flag in varchar2 default hr_api.g_varchar2
1457 ,p_elig_flag in varchar2 default hr_api.g_varchar2
1458 ,p_comp_ref_amt in number default hr_api.g_number
1459 ,p_cmbn_age_n_los_val in number default hr_api.g_number
1460 ,p_comp_ref_uom in varchar2 default hr_api.g_varchar2
1461 ,p_age_val in number default hr_api.g_number
1462 ,p_los_val in number default hr_api.g_number
1463 ,p_prtn_end_dt in date default hr_api.g_date
1464 ,p_prtn_strt_dt in date default hr_api.g_date
1465 ,p_wait_perd_cmpltn_dt in date default hr_api.g_date
1466 ,p_wait_perd_strt_dt in date default hr_api.g_date
1467 ,p_wv_ctfn_typ_cd in varchar2 default hr_api.g_varchar2
1468 ,p_hrs_wkd_val in number default hr_api.g_number
1469 ,p_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2
1470 ,p_prtn_ovridn_flag in varchar2 default hr_api.g_varchar2
1471 ,p_no_mx_prtn_ovrid_thru_flag in varchar2 default hr_api.g_varchar2
1472 ,p_prtn_ovridn_rsn_cd in varchar2 default hr_api.g_varchar2
1473 ,p_age_uom in varchar2 default hr_api.g_varchar2
1474 ,p_los_uom in varchar2 default hr_api.g_varchar2
1475 ,p_ovrid_svc_dt in date default hr_api.g_date
1476 ,p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
1477 ,p_frz_los_flag in varchar2 default hr_api.g_varchar2
1478 ,p_frz_age_flag in varchar2 default hr_api.g_varchar2
1479 ,p_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2
1480 ,p_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
1481 ,p_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
1482 ,p_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2
1483 ,p_dstr_rstcn_flag in varchar2 default hr_api.g_varchar2
1484 ,p_pct_fl_tm_val in number default hr_api.g_number
1485 ,p_wv_prtn_rsn_cd in varchar2 default hr_api.g_varchar2
1486 ,p_pl_wvd_flag in varchar2 default hr_api.g_varchar2
1487 ,p_rt_comp_ref_amt in number default hr_api.g_number
1488 ,p_rt_cmbn_age_n_los_val in number default hr_api.g_number
1489 ,p_rt_comp_ref_uom in varchar2 default hr_api.g_varchar2
1490 ,p_rt_age_val in number default hr_api.g_number
1491 ,p_rt_los_val in number default hr_api.g_number
1492 ,p_rt_hrs_wkd_val in number default hr_api.g_number
1493 ,p_rt_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2
1494 ,p_rt_age_uom in varchar2 default hr_api.g_varchar2
1495 ,p_rt_los_uom in varchar2 default hr_api.g_varchar2
1496 ,p_rt_pct_fl_tm_val in number default hr_api.g_number
1497 ,p_rt_frz_los_flag in varchar2 default hr_api.g_varchar2
1498 ,p_rt_frz_age_flag in varchar2 default hr_api.g_varchar2
1499 ,p_rt_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2
1500 ,p_rt_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
1501 ,p_rt_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
1502 ,p_rt_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2
1503 ,p_once_r_cntug_cd in varchar2 default hr_api.g_varchar2
1504 ,p_pl_ordr_num in number default hr_api.g_number
1505 ,p_plip_ordr_num in number default hr_api.g_number
1506 ,p_ptip_ordr_num in number default hr_api.g_number
1507 ,p_pep_attribute_category in varchar2 default hr_api.g_varchar2
1508 ,p_pep_attribute1 in varchar2 default hr_api.g_varchar2
1509 ,p_pep_attribute2 in varchar2 default hr_api.g_varchar2
1510 ,p_pep_attribute3 in varchar2 default hr_api.g_varchar2
1511 ,p_pep_attribute4 in varchar2 default hr_api.g_varchar2
1512 ,p_pep_attribute5 in varchar2 default hr_api.g_varchar2
1513 ,p_pep_attribute6 in varchar2 default hr_api.g_varchar2
1514 ,p_pep_attribute7 in varchar2 default hr_api.g_varchar2
1515 ,p_pep_attribute8 in varchar2 default hr_api.g_varchar2
1516 ,p_pep_attribute9 in varchar2 default hr_api.g_varchar2
1517 ,p_pep_attribute10 in varchar2 default hr_api.g_varchar2
1518 ,p_pep_attribute11 in varchar2 default hr_api.g_varchar2
1519 ,p_pep_attribute12 in varchar2 default hr_api.g_varchar2
1520 ,p_pep_attribute13 in varchar2 default hr_api.g_varchar2
1521 ,p_pep_attribute14 in varchar2 default hr_api.g_varchar2
1522 ,p_pep_attribute15 in varchar2 default hr_api.g_varchar2
1523 ,p_pep_attribute16 in varchar2 default hr_api.g_varchar2
1524 ,p_pep_attribute17 in varchar2 default hr_api.g_varchar2
1525 ,p_pep_attribute18 in varchar2 default hr_api.g_varchar2
1526 ,p_pep_attribute19 in varchar2 default hr_api.g_varchar2
1527 ,p_pep_attribute20 in varchar2 default hr_api.g_varchar2
1528 ,p_pep_attribute21 in varchar2 default hr_api.g_varchar2
1529 ,p_pep_attribute22 in varchar2 default hr_api.g_varchar2
1530 ,p_pep_attribute23 in varchar2 default hr_api.g_varchar2
1531 ,p_pep_attribute24 in varchar2 default hr_api.g_varchar2
1532 ,p_pep_attribute25 in varchar2 default hr_api.g_varchar2
1533 ,p_pep_attribute26 in varchar2 default hr_api.g_varchar2
1534 ,p_pep_attribute27 in varchar2 default hr_api.g_varchar2
1535 ,p_pep_attribute28 in varchar2 default hr_api.g_varchar2
1536 ,p_pep_attribute29 in varchar2 default hr_api.g_varchar2
1537 ,p_pep_attribute30 in varchar2 default hr_api.g_varchar2
1538 ,p_request_id in number default hr_api.g_number
1539 ,p_program_application_id in number default hr_api.g_number
1540 ,p_program_id in number default hr_api.g_number
1541 ,p_program_update_date in date default hr_api.g_date
1542 ,p_object_version_number in out nocopy number
1543 ,p_effective_date in date
1544 ,p_datetrack_mode in varchar2
1545 )
1546 is
1547 --
1548 -- Declare cursors and local variables
1549 --
1550 l_proc varchar2(72) := g_package||'update_perf_Eligible_Person';
1551 --
1552 l_rec ben_pep_shd.g_rec_type;
1553 l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1554 l_effective_start_date ben_elig_per_f.effective_start_date%TYPE;
1555 l_effective_end_date ben_elig_per_f.effective_end_date%TYPE;
1556 --
1557 l_validation_start_date date;
1558 l_validation_end_date date;
1559 l_dummy_version_number number;
1560 --
1561 l_created_by number;
1562 l_creation_date date;
1563 l_last_update_date date;
1564 l_last_updated_by number;
1565 l_last_update_login number;
1566 --
1567 l_base_table_name varchar2(30);
1568 l_base_key_column varchar2(30);
1569 l_base_key_value number;
1570 --
1571 -- Cursor C_Sel1 selects the current locked row as of session date
1572 -- ensuring that the object version numbers match.
1573 --
1574 Cursor c_old_rec
1575 is
1576 select
1577 elig_per_id,
1578 effective_start_date,
1579 effective_end_date,
1580 business_group_id,
1581 pl_id,
1582 pgm_id,
1583 plip_id,
1584 ptip_id,
1585 ler_id,
1586 person_id,
1587 per_in_ler_id,
1588 dpnt_othr_pl_cvrd_rl_flag,
1589 prtn_ovridn_thru_dt,
1590 pl_key_ee_flag,
1591 pl_hghly_compd_flag,
1592 elig_flag,
1593 comp_ref_amt,
1594 cmbn_age_n_los_val,
1595 comp_ref_uom,
1596 age_val,
1597 los_val,
1598 prtn_end_dt,
1599 prtn_strt_dt,
1600 wait_perd_cmpltn_dt,
1601 wait_perd_strt_dt,
1602 wv_ctfn_typ_cd,
1603 hrs_wkd_val,
1604 hrs_wkd_bndry_perd_cd,
1605 prtn_ovridn_flag,
1606 no_mx_prtn_ovrid_thru_flag,
1607 prtn_ovridn_rsn_cd,
1608 age_uom,
1609 los_uom,
1610 ovrid_svc_dt,
1611 inelg_rsn_cd,
1612 frz_los_flag,
1613 frz_age_flag,
1614 frz_cmp_lvl_flag,
1615 frz_pct_fl_tm_flag,
1616 frz_hrs_wkd_flag,
1617 frz_comb_age_and_los_flag,
1618 dstr_rstcn_flag,
1619 pct_fl_tm_val,
1620 wv_prtn_rsn_cd,
1621 pl_wvd_flag,
1622 rt_comp_ref_amt,
1623 rt_cmbn_age_n_los_val,
1624 rt_comp_ref_uom,
1625 rt_age_val,
1626 rt_los_val,
1627 rt_hrs_wkd_val,
1628 rt_hrs_wkd_bndry_perd_cd,
1629 rt_age_uom,
1630 rt_los_uom,
1631 rt_pct_fl_tm_val,
1632 rt_frz_los_flag,
1633 rt_frz_age_flag,
1634 rt_frz_cmp_lvl_flag,
1635 rt_frz_pct_fl_tm_flag,
1636 rt_frz_hrs_wkd_flag,
1637 rt_frz_comb_age_and_los_flag,
1638 once_r_cntug_cd,
1639 pl_ordr_num,
1640 plip_ordr_num,
1641 ptip_ordr_num,
1642 pep_attribute_category,
1643 pep_attribute1,
1644 pep_attribute2,
1645 pep_attribute3,
1646 pep_attribute4,
1647 pep_attribute5,
1648 pep_attribute6,
1649 pep_attribute7,
1650 pep_attribute8,
1651 pep_attribute9,
1652 pep_attribute10,
1653 pep_attribute11,
1654 pep_attribute12,
1655 pep_attribute13,
1656 pep_attribute14,
1657 pep_attribute15,
1658 pep_attribute16,
1659 pep_attribute17,
1660 pep_attribute18,
1661 pep_attribute19,
1662 pep_attribute20,
1663 pep_attribute21,
1664 pep_attribute22,
1665 pep_attribute23,
1666 pep_attribute24,
1667 pep_attribute25,
1668 pep_attribute26,
1669 pep_attribute27,
1670 pep_attribute28,
1671 pep_attribute29,
1672 pep_attribute30,
1673 request_id,
1674 program_application_id,
1675 program_id,
1676 program_update_date,
1677 object_version_number
1678 from ben_elig_per_f
1679 where elig_per_id = p_elig_per_id
1680 and p_effective_date
1681 between effective_start_date and effective_end_date
1682 for update nowait;
1683 --
1684 Cursor C_Sel1
1685 Is
1686 select t.created_by,
1687 t.creation_date
1688 from ben_elig_per_f t
1689 where t.elig_per_id = p_elig_per_id
1690 and t.effective_start_date =
1691 ben_pep_shd.g_old_rec.effective_start_date
1692 and t.effective_end_date = (l_validation_start_date - 1);
1693 --
1694 cursor c_effdates
1695 (c_eff_date date
1696 ,c_pep_id number
1697 )
1698 is
1699 select pep.effective_start_date,
1700 pep.effective_end_date
1701 from ben_elig_per_f pep
1702 where pep.elig_per_id = c_pep_id
1703 and c_eff_date
1704 between pep.effective_start_date and pep.effective_end_date;
1705 --
1706 cursor c_getovn
1707 (c_pep_id number
1708 )
1709 is
1710 select nvl(max(pep.object_version_number),0) + 1
1711 from ben_elig_per_f pep
1712 where pep.elig_per_id = c_pep_id;
1713 --
1714 cursor c_plip
1715 (p_elig_per_id number, cv_pgm_id number) is
1716 select cpp.plip_id
1717 from ben_plip_f cpp,
1718 ben_elig_per_f pep
1719 where cpp.pl_id = pep.pl_id
1720 and pep.elig_per_id = p_elig_per_id
1721 and cpp.pgm_id = cv_pgm_id /* Bug 5098907 */
1722 and p_effective_date
1723 between pep.effective_start_date and pep.effective_end_date
1724 and p_effective_date
1725 between cpp.effective_start_date and cpp.effective_end_date;
1726 --
1727 l_pepinsplip g_pepapi_rectyp;
1728 l_plip_id number;
1729 l_dummy_pep_id number;
1730 l_dummy_esd date;
1731 l_dummy_eed date;
1732 l_dummy_ovn number;
1733 --
1734 begin
1735 --
1736 hr_utility.set_location('Entering:'|| l_proc, 10);
1737 --
1738 -- Issue a savepoint if operating in validation only mode
1739 --
1740 savepoint update_perf_Eligible_Person;
1741 --
1742 hr_utility.set_location(l_proc, 20);
1743 --
1744 --bug#3974928 - to take care of wrong setup of track ineligible flag at plan
1745 --level and not at plip level
1746 if g_pepinsplip.p_pgm_id is not null
1747 and g_pepinsplip.p_plip_id is not null
1748 and g_pepinsplip.p_pl_id is null
1749 and g_pepinsplip.p_ptip_id is null then
1750 --
1751 open c_plip (p_elig_per_id, g_pepinsplip.p_pgm_id);
1752 fetch c_plip into l_plip_id;
1753 close c_plip;
1754 --
1755 if g_pepinsplip.p_plip_id = l_plip_id then
1756 --
1757 ben_Eligible_Person_perf_api.create_perf_Eligible_Person
1758 (p_validate => g_pepinsplip.p_validate
1759 ,p_elig_per_id => l_dummy_pep_id
1760 ,p_effective_start_date => l_dummy_esd
1761 ,p_effective_end_date => l_dummy_eed
1762 ,p_business_group_id => g_pepinsplip.p_business_group_id
1763 ,p_pl_id => g_pepinsplip.p_pl_id
1764 ,p_pgm_id => g_pepinsplip.p_pgm_id
1765 ,p_plip_id => g_pepinsplip.p_plip_id
1766 ,p_ptip_id => g_pepinsplip.p_ptip_id
1767 ,p_ler_id => g_pepinsplip.p_ler_id
1768 ,p_person_id => g_pepinsplip.p_person_id
1769 ,p_per_in_ler_id => g_pepinsplip.p_per_in_ler_id
1770 ,p_dpnt_othr_pl_cvrd_rl_flag => g_pepinsplip.p_dpnt_othr_pl_cvrd_rl_flag
1771 ,p_prtn_ovridn_thru_dt => g_pepinsplip.p_prtn_ovridn_thru_dt
1772 ,p_pl_key_ee_flag => g_pepinsplip.p_pl_key_ee_flag
1773 ,p_pl_hghly_compd_flag => g_pepinsplip.p_pl_hghly_compd_flag
1774 ,p_elig_flag => g_pepinsplip.p_elig_flag
1775 ,p_comp_ref_amt => g_pepinsplip.p_comp_ref_amt
1776 ,p_cmbn_age_n_los_val => g_pepinsplip.p_cmbn_age_n_los_val
1777 ,p_comp_ref_uom => g_pepinsplip.p_comp_ref_uom
1778 ,p_age_val => g_pepinsplip.p_age_val
1779 ,p_los_val => g_pepinsplip.p_los_val
1780 ,p_prtn_end_dt => g_pepinsplip.p_prtn_end_dt
1781 ,p_prtn_strt_dt => g_pepinsplip.p_prtn_strt_dt
1782 ,p_wait_perd_cmpltn_dt => g_pepinsplip.p_wait_perd_cmpltn_dt
1783 ,p_wait_perd_strt_dt => g_pepinsplip.p_wait_perd_strt_dt
1784 ,p_wv_ctfn_typ_cd => g_pepinsplip.p_wv_ctfn_typ_cd
1785 ,p_hrs_wkd_val => g_pepinsplip.p_hrs_wkd_val
1786 ,p_hrs_wkd_bndry_perd_cd => g_pepinsplip.p_hrs_wkd_bndry_perd_cd
1787 ,p_prtn_ovridn_flag => g_pepinsplip.p_prtn_ovridn_flag
1788 ,p_no_mx_prtn_ovrid_thru_flag => g_pepinsplip.p_no_mx_prtn_ovrid_thru_flag
1789 ,p_prtn_ovridn_rsn_cd => g_pepinsplip.p_prtn_ovridn_rsn_cd
1790 ,p_age_uom => g_pepinsplip.p_age_uom
1791 ,p_los_uom => g_pepinsplip.p_los_uom
1792 ,p_ovrid_svc_dt => g_pepinsplip.p_ovrid_svc_dt
1793 ,p_inelg_rsn_cd => g_pepinsplip.p_inelg_rsn_cd
1794 ,p_frz_los_flag => g_pepinsplip.p_frz_los_flag
1795 ,p_frz_age_flag => g_pepinsplip.p_frz_age_flag
1796 ,p_frz_cmp_lvl_flag => g_pepinsplip.p_frz_cmp_lvl_flag
1797 ,p_frz_pct_fl_tm_flag => g_pepinsplip.p_frz_pct_fl_tm_flag
1798 ,p_frz_hrs_wkd_flag => g_pepinsplip.p_frz_hrs_wkd_flag
1799 ,p_frz_comb_age_and_los_flag => g_pepinsplip.p_frz_comb_age_and_los_flag
1800 ,p_dstr_rstcn_flag => g_pepinsplip.p_dstr_rstcn_flag
1801 ,p_pct_fl_tm_val => g_pepinsplip.p_pct_fl_tm_val
1802 ,p_wv_prtn_rsn_cd => g_pepinsplip.p_wv_prtn_rsn_cd
1803 ,p_pl_wvd_flag => g_pepinsplip.p_pl_wvd_flag
1804 ,p_rt_comp_ref_amt => g_pepinsplip.p_rt_comp_ref_amt
1805 ,p_rt_cmbn_age_n_los_val => g_pepinsplip.p_rt_cmbn_age_n_los_val
1806 ,p_rt_comp_ref_uom => g_pepinsplip.p_rt_comp_ref_uom
1807 ,p_rt_age_val => g_pepinsplip.p_rt_age_val
1808 ,p_rt_los_val => g_pepinsplip.p_rt_los_val
1809 ,p_rt_hrs_wkd_val => g_pepinsplip.p_rt_hrs_wkd_val
1810 ,p_rt_hrs_wkd_bndry_perd_cd => g_pepinsplip.p_rt_hrs_wkd_bndry_perd_cd
1811 ,p_rt_age_uom => g_pepinsplip.p_rt_age_uom
1812 ,p_rt_los_uom => g_pepinsplip.p_rt_los_uom
1813 ,p_rt_pct_fl_tm_val => g_pepinsplip.p_rt_pct_fl_tm_val
1814 ,p_rt_frz_los_flag => g_pepinsplip.p_rt_frz_los_flag
1815 ,p_rt_frz_age_flag => g_pepinsplip.p_rt_frz_age_flag
1816 ,p_rt_frz_cmp_lvl_flag => g_pepinsplip.p_rt_frz_cmp_lvl_flag
1817 ,p_rt_frz_pct_fl_tm_flag => g_pepinsplip.p_rt_frz_pct_fl_tm_flag
1818 ,p_rt_frz_hrs_wkd_flag => g_pepinsplip.p_rt_frz_hrs_wkd_flag
1819 ,p_rt_frz_comb_age_and_los_flag => g_pepinsplip.p_rt_frz_comb_age_and_los_flag
1820 ,p_once_r_cntug_cd => g_pepinsplip.p_once_r_cntug_cd
1821 ,p_pl_ordr_num => g_pepinsplip.p_pl_ordr_num
1822 ,p_plip_ordr_num => g_pepinsplip.p_plip_ordr_num
1823 ,p_ptip_ordr_num => g_pepinsplip.p_ptip_ordr_num
1824 ,p_pep_attribute_category => g_pepinsplip.p_pep_attribute_category
1825 ,p_pep_attribute1 => g_pepinsplip.p_pep_attribute1
1826 ,p_pep_attribute2 => g_pepinsplip.p_pep_attribute2
1827 ,p_pep_attribute3 => g_pepinsplip.p_pep_attribute3
1828 ,p_pep_attribute4 => g_pepinsplip.p_pep_attribute4
1829 ,p_pep_attribute5 => g_pepinsplip.p_pep_attribute5
1830 ,p_pep_attribute6 => g_pepinsplip.p_pep_attribute6
1831 ,p_pep_attribute7 => g_pepinsplip.p_pep_attribute7
1832 ,p_pep_attribute8 => g_pepinsplip.p_pep_attribute8
1833 ,p_pep_attribute9 => g_pepinsplip.p_pep_attribute9
1834 ,p_pep_attribute10 => g_pepinsplip.p_pep_attribute10
1835 ,p_pep_attribute11 => g_pepinsplip.p_pep_attribute11
1836 ,p_pep_attribute12 => g_pepinsplip.p_pep_attribute12
1837 ,p_pep_attribute13 => g_pepinsplip.p_pep_attribute13
1838 ,p_pep_attribute14 => g_pepinsplip.p_pep_attribute14
1839 ,p_pep_attribute15 => g_pepinsplip.p_pep_attribute15
1840 ,p_pep_attribute16 => g_pepinsplip.p_pep_attribute16
1841 ,p_pep_attribute17 => g_pepinsplip.p_pep_attribute17
1842 ,p_pep_attribute18 => g_pepinsplip.p_pep_attribute18
1843 ,p_pep_attribute19 => g_pepinsplip.p_pep_attribute19
1844 ,p_pep_attribute20 => g_pepinsplip.p_pep_attribute20
1845 ,p_pep_attribute21 => g_pepinsplip.p_pep_attribute21
1846 ,p_pep_attribute22 => g_pepinsplip.p_pep_attribute22
1847 ,p_pep_attribute23 => g_pepinsplip.p_pep_attribute23
1848 ,p_pep_attribute24 => g_pepinsplip.p_pep_attribute24
1849 ,p_pep_attribute25 => g_pepinsplip.p_pep_attribute25
1850 ,p_pep_attribute26 => g_pepinsplip.p_pep_attribute26
1851 ,p_pep_attribute27 => g_pepinsplip.p_pep_attribute27
1852 ,p_pep_attribute28 => g_pepinsplip.p_pep_attribute28
1853 ,p_pep_attribute29 => g_pepinsplip.p_pep_attribute29
1854 ,p_pep_attribute30 => g_pepinsplip.p_pep_attribute30
1855 ,p_request_id => g_pepinsplip.p_request_id
1856 ,p_program_application_id => g_pepinsplip.p_program_application_id
1857 ,p_program_id => g_pepinsplip.p_program_id
1858 ,p_program_update_date => g_pepinsplip.p_program_update_date
1859 ,p_object_version_number => l_dummy_ovn
1860 ,p_effective_date => g_pepinsplip.p_effective_date
1861 ,p_override_validation => g_pepinsplip.p_override_validation
1862 ,p_defer => false
1863 );
1864 --
1865 g_pepinsplip := l_pepinsplip;
1866 --
1867 end if;
1868 --
1869 end if;
1870 -- Process Logic
1871 --
1872 l_object_version_number := p_object_version_number;
1873 --
1874 begin
1875 --
1876 -- Start of API User Hook for the before hook of update_Eligible_Person
1877 --
1878 ben_Eligible_Person_bk2.update_Eligible_Person_b
1879 (p_elig_per_id => p_elig_per_id
1880 ,p_business_group_id => p_business_group_id
1881 ,p_pl_id => p_pl_id
1882 ,p_pgm_id => p_pgm_id
1883 ,p_plip_id => p_plip_id
1884 ,p_ptip_id => p_ptip_id
1885 ,p_ler_id => p_ler_id
1886 ,p_person_id => p_person_id
1887 ,p_per_in_ler_id => p_per_in_ler_id
1888 ,p_dpnt_othr_pl_cvrd_rl_flag => p_dpnt_othr_pl_cvrd_rl_flag
1889 ,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
1890 ,p_pl_key_ee_flag => p_pl_key_ee_flag
1891 ,p_pl_hghly_compd_flag => p_pl_hghly_compd_flag
1892 ,p_elig_flag => p_elig_flag
1893 ,p_comp_ref_amt => p_comp_ref_amt
1894 ,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
1895 ,p_comp_ref_uom => p_comp_ref_uom
1896 ,p_age_val => p_age_val
1897 ,p_los_val => p_los_val
1898 ,p_prtn_end_dt => p_prtn_end_dt
1899 ,p_prtn_strt_dt => p_prtn_strt_dt
1900 ,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
1901 ,p_wait_perd_strt_dt => p_wait_perd_strt_dt
1902 ,p_wv_ctfn_typ_cd => p_wv_ctfn_typ_cd
1903 ,p_hrs_wkd_val => p_hrs_wkd_val
1904 ,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
1905 ,p_prtn_ovridn_flag => p_prtn_ovridn_flag
1906 ,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
1907 ,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
1908 ,p_age_uom => p_age_uom
1909 ,p_los_uom => p_los_uom
1910 ,p_ovrid_svc_dt => p_ovrid_svc_dt
1911 ,p_inelg_rsn_cd => p_inelg_rsn_cd
1912 ,p_frz_los_flag => p_frz_los_flag
1913 ,p_frz_age_flag => p_frz_age_flag
1914 ,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
1915 ,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
1916 ,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
1917 ,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
1918 ,p_dstr_rstcn_flag => p_dstr_rstcn_flag
1919 ,p_pct_fl_tm_val => p_pct_fl_tm_val
1920 ,p_wv_prtn_rsn_cd => p_wv_prtn_rsn_cd
1921 ,p_pl_wvd_flag => p_pl_wvd_flag
1922 ,p_rt_comp_ref_amt => p_rt_comp_ref_amt
1923 ,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
1924 ,p_rt_comp_ref_uom => p_rt_comp_ref_uom
1925 ,p_rt_age_val => p_rt_age_val
1926 ,p_rt_los_val => p_rt_los_val
1927 ,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
1928 ,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
1929 ,p_rt_age_uom => p_rt_age_uom
1930 ,p_rt_los_uom => p_rt_los_uom
1931 ,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
1932 ,p_rt_frz_los_flag => p_rt_frz_los_flag
1933 ,p_rt_frz_age_flag => p_rt_frz_age_flag
1934 ,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
1935 ,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
1936 ,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
1937 ,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
1938 ,p_once_r_cntug_cd => p_once_r_cntug_cd
1939 ,p_pl_ordr_num => p_pl_ordr_num
1940 ,p_plip_ordr_num => p_plip_ordr_num
1941 ,p_ptip_ordr_num => p_ptip_ordr_num
1942 ,p_pep_attribute_category => p_pep_attribute_category
1943 ,p_pep_attribute1 => p_pep_attribute1
1944 ,p_pep_attribute2 => p_pep_attribute2
1945 ,p_pep_attribute3 => p_pep_attribute3
1946 ,p_pep_attribute4 => p_pep_attribute4
1947 ,p_pep_attribute5 => p_pep_attribute5
1948 ,p_pep_attribute6 => p_pep_attribute6
1949 ,p_pep_attribute7 => p_pep_attribute7
1950 ,p_pep_attribute8 => p_pep_attribute8
1951 ,p_pep_attribute9 => p_pep_attribute9
1952 ,p_pep_attribute10 => p_pep_attribute10
1953 ,p_pep_attribute11 => p_pep_attribute11
1954 ,p_pep_attribute12 => p_pep_attribute12
1955 ,p_pep_attribute13 => p_pep_attribute13
1956 ,p_pep_attribute14 => p_pep_attribute14
1957 ,p_pep_attribute15 => p_pep_attribute15
1958 ,p_pep_attribute16 => p_pep_attribute16
1959 ,p_pep_attribute17 => p_pep_attribute17
1960 ,p_pep_attribute18 => p_pep_attribute18
1961 ,p_pep_attribute19 => p_pep_attribute19
1962 ,p_pep_attribute20 => p_pep_attribute20
1963 ,p_pep_attribute21 => p_pep_attribute21
1964 ,p_pep_attribute22 => p_pep_attribute22
1965 ,p_pep_attribute23 => p_pep_attribute23
1966 ,p_pep_attribute24 => p_pep_attribute24
1967 ,p_pep_attribute25 => p_pep_attribute25
1968 ,p_pep_attribute26 => p_pep_attribute26
1969 ,p_pep_attribute27 => p_pep_attribute27
1970 ,p_pep_attribute28 => p_pep_attribute28
1971 ,p_pep_attribute29 => p_pep_attribute29
1972 ,p_pep_attribute30 => p_pep_attribute30
1973 ,p_request_id => p_request_id
1974 ,p_program_application_id => p_program_application_id
1975 ,p_program_id => p_program_id
1976 ,p_program_update_date => p_program_update_date
1977 ,p_object_version_number => p_object_version_number
1978 ,p_effective_date => trunc(p_effective_date)
1979 ,p_datetrack_mode => p_datetrack_mode);
1980 --
1981 exception
1982 --
1983 when hr_api.cannot_find_prog_unit then
1984 --
1985 hr_api.cannot_find_prog_unit_error
1986 (p_module_name => 'UPDATE_Eligible_Person'
1987 ,p_hook_type => 'BP');
1988 --
1989 -- End of API User Hook for the before hook of update_Eligible_Person
1990 --
1991 end;
1992 --
1993 -- Process Logic
1994 --
1995 l_object_version_number := p_object_version_number;
1996 l_rec :=
1997 ben_pep_shd.convert_args
1998 (
1999 p_elig_per_id,
2000 null,
2001 null,
2002 p_business_group_id,
2003 p_pl_id,
2004 p_pgm_id,
2005 p_plip_id,
2006 p_ptip_id,
2007 p_ler_id,
2008 p_person_id,
2009 p_per_in_ler_id,
2010 p_dpnt_othr_pl_cvrd_rl_flag,
2011 p_prtn_ovridn_thru_dt,
2012 p_pl_key_ee_flag,
2013 p_pl_hghly_compd_flag,
2014 p_elig_flag,
2015 p_comp_ref_amt,
2016 p_cmbn_age_n_los_val,
2017 p_comp_ref_uom,
2018 p_age_val,
2019 p_los_val,
2020 p_prtn_end_dt,
2021 p_prtn_strt_dt,
2022 p_wait_perd_cmpltn_dt,
2023 p_wait_perd_strt_dt ,
2024 p_wv_ctfn_typ_cd,
2025 p_hrs_wkd_val,
2026 p_hrs_wkd_bndry_perd_cd,
2027 p_prtn_ovridn_flag,
2028 p_no_mx_prtn_ovrid_thru_flag,
2029 p_prtn_ovridn_rsn_cd,
2030 p_age_uom,
2031 p_los_uom,
2032 p_ovrid_svc_dt,
2033 p_inelg_rsn_cd,
2034 p_frz_los_flag,
2035 p_frz_age_flag,
2036 p_frz_cmp_lvl_flag,
2037 p_frz_pct_fl_tm_flag,
2038 p_frz_hrs_wkd_flag,
2039 p_frz_comb_age_and_los_flag,
2040 p_dstr_rstcn_flag,
2041 p_pct_fl_tm_val,
2042 p_wv_prtn_rsn_cd,
2043 p_pl_wvd_flag,
2044 p_rt_comp_ref_amt,
2045 p_rt_cmbn_age_n_los_val,
2046 p_rt_comp_ref_uom,
2047 p_rt_age_val,
2048 p_rt_los_val,
2049 p_rt_hrs_wkd_val,
2050 p_rt_hrs_wkd_bndry_perd_cd,
2051 p_rt_age_uom,
2052 p_rt_los_uom,
2053 p_rt_pct_fl_tm_val,
2054 p_rt_frz_los_flag,
2055 p_rt_frz_age_flag,
2056 p_rt_frz_cmp_lvl_flag,
2057 p_rt_frz_pct_fl_tm_flag,
2058 p_rt_frz_hrs_wkd_flag,
2059 p_rt_frz_comb_age_and_los_flag,
2060 p_once_r_cntug_cd,
2061 p_pl_ordr_num,
2062 p_plip_ordr_num,
2063 p_ptip_ordr_num,
2064 p_pep_attribute_category,
2065 p_pep_attribute1,
2066 p_pep_attribute2,
2067 p_pep_attribute3,
2068 p_pep_attribute4,
2069 p_pep_attribute5,
2070 p_pep_attribute6,
2071 p_pep_attribute7,
2072 p_pep_attribute8,
2073 p_pep_attribute9,
2074 p_pep_attribute10,
2075 p_pep_attribute11,
2076 p_pep_attribute12,
2077 p_pep_attribute13,
2078 p_pep_attribute14,
2079 p_pep_attribute15,
2080 p_pep_attribute16,
2081 p_pep_attribute17,
2082 p_pep_attribute18,
2083 p_pep_attribute19,
2084 p_pep_attribute20,
2085 p_pep_attribute21,
2086 p_pep_attribute22,
2087 p_pep_attribute23,
2088 p_pep_attribute24,
2089 p_pep_attribute25,
2090 p_pep_attribute26,
2091 p_pep_attribute27,
2092 p_pep_attribute28,
2093 p_pep_attribute29,
2094 p_pep_attribute30,
2095 p_request_id,
2096 p_program_application_id,
2097 p_program_id,
2098 p_program_update_date,
2099 p_object_version_number
2100 );
2101 --
2102 -- We must lock the row which we need to update.
2103 --
2104 -- Check to ensure the datetrack mode is not INSERT.
2105 --
2106 If (p_datetrack_mode <> 'INSERT') then
2107 --
2108 -- We must select and lock the current row.
2109 --
2110 Open c_old_rec;
2111 Fetch c_old_rec Into ben_pep_shd.g_old_rec;
2112 If c_old_rec%notfound then
2113 Close c_old_rec;
2114 --
2115 -- The primary key is invalid therefore we must error
2116 --
2117 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2118 fnd_message.raise_error;
2119 End If;
2120 Close c_old_rec;
2121 If (p_object_version_number <> ben_pep_shd.g_old_rec.object_version_number) Then
2122 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
2123 fnd_message.raise_error;
2124 End If;
2125 hr_utility.set_location(l_proc, 15);
2126 --
2127 -- Validate the datetrack mode mode getting the validation start
2128 -- and end dates for the specified datetrack operation.
2129 --
2130 ben_batch_dt_api.validate_dt_mode_pep
2131 (p_effective_date => p_effective_date
2132 ,p_datetrack_mode => p_datetrack_mode
2133 ,p_elig_per_id => p_elig_per_id
2134 --
2135 ,p_validation_start_date => l_validation_start_date
2136 ,p_validation_end_date => l_validation_end_date
2137 );
2138 --
2139 Else
2140 --
2141 -- We are doing a datetrack 'INSERT' which is illegal within this
2142 -- procedure therefore we must error (note: to lck on insert the
2143 -- private procedure ins_lck should be called).
2144 --
2145 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2146 fnd_message.set_token('PROCEDURE', l_proc);
2147 fnd_message.set_token('STEP','20');
2148 fnd_message.raise_error;
2149 --
2150 End If;
2151 --
2152 hr_utility.set_location(' Leaving:'||l_proc, 30);
2153 convert_defs(l_rec);
2154 --
2155 if ben_manage_life_events.g_modified_mode in ('S','U','D') then --ICM
2156 --compare values to decide whether to update or not
2157 if
2158 --
2159 nvl(ben_pep_shd.g_old_rec.pl_id,-1) <> nvl(l_rec.pl_id,-1) or
2160 nvl(ben_pep_shd.g_old_rec.pgm_id,-1) <> nvl(l_rec.pgm_id,-1) or
2161 nvl(ben_pep_shd.g_old_rec.plip_id,-1) <> nvl(l_rec.plip_id,-1) or
2162 nvl(ben_pep_shd.g_old_rec.ptip_id,-1) <> nvl(l_rec.ptip_id,-1) or
2163 nvl(ben_pep_shd.g_old_rec.ler_id,-1) <> nvl(l_rec.ler_id,-1) or
2164 nvl(ben_pep_shd.g_old_rec.dpnt_othr_pl_cvrd_rl_flag,'N') <>
2165 nvl(l_rec.dpnt_othr_pl_cvrd_rl_flag,'N') or
2166 nvl(ben_pep_shd.g_old_rec.prtn_ovridn_thru_dt,to_date('01-01-1001','dd-mm-yyyy'))<>
2167 nvl(l_rec.prtn_ovridn_thru_dt,to_date('01-01-1001','dd-mm-yyyy')) or
2168 nvl(ben_pep_shd.g_old_rec.pl_key_ee_flag,'N')<> nvl(l_rec.pl_key_ee_flag,'N') or
2169 nvl(ben_pep_shd.g_old_rec.pl_hghly_compd_flag,'N') <> nvl(l_rec.pl_hghly_compd_flag,'N') or
2170 nvl(ben_pep_shd.g_old_rec.elig_flag,'N') <> nvl(l_rec.elig_flag,'N') or
2171 nvl(ben_pep_shd.g_old_rec.comp_ref_amt,-1) <> nvl(l_rec.comp_ref_amt,-1) or
2172 nvl(ben_pep_shd.g_old_rec.cmbn_age_n_los_val, -1) <> nvl(l_rec.cmbn_age_n_los_val, -1) or
2173 nvl(ben_pep_shd.g_old_rec.comp_ref_uom,'X') <> nvl(l_rec.comp_ref_uom,'X') or
2174 nvl(ben_pep_shd.g_old_rec.age_val, -1) <> nvl(l_rec.age_val, -1) or
2175 nvl(ben_pep_shd.g_old_rec.los_val, -1) <> nvl(l_rec.los_val, -1) or
2176 nvl(ben_pep_shd.g_old_rec.prtn_end_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
2177 nvl(l_rec.prtn_end_dt,to_date('01-01-1001','dd-mm-yyyy')) or
2178 nvl(ben_pep_shd.g_old_rec.wait_perd_cmpltn_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
2179 nvl(l_rec.wait_perd_cmpltn_dt,to_date('01-01-1001','dd-mm-yyyy')) or
2180 nvl(ben_pep_shd.g_old_rec.wait_perd_strt_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
2181 nvl(l_rec.wait_perd_strt_dt,to_date('01-01-1001','dd-mm-yyyy')) or
2182 nvl(ben_pep_shd.g_old_rec.wv_ctfn_typ_cd,'X') <> nvl(l_rec.wv_ctfn_typ_cd,'X') or
2183 nvl(ben_pep_shd.g_old_rec.hrs_wkd_val,-1) <> nvl(l_rec.hrs_wkd_val,-1) or
2184 nvl(ben_pep_shd.g_old_rec.hrs_wkd_bndry_perd_cd,'X') <> nvl(l_rec.hrs_wkd_bndry_perd_cd,'X') or
2185 nvl(ben_pep_shd.g_old_rec.prtn_ovridn_flag,'N') <> nvl(l_rec.prtn_ovridn_flag,'N') or
2186 nvl(ben_pep_shd.g_old_rec.no_mx_prtn_ovrid_thru_flag,'N') <>
2187 nvl(l_rec.no_mx_prtn_ovrid_thru_flag,'N') or
2188 nvl(ben_pep_shd.g_old_rec.prtn_ovridn_rsn_cd, 'X') <>
2189 nvl(l_rec.prtn_ovridn_rsn_cd, 'X') or
2190 nvl(ben_pep_shd.g_old_rec.age_uom,'X') <> nvl(l_rec.age_uom,'X') or
2191 nvl(ben_pep_shd.g_old_rec.los_uom,'X') <> nvl(l_rec.los_uom,'X') or
2192 nvl(ben_pep_shd.g_old_rec.ovrid_svc_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
2193 nvl(l_rec.ovrid_svc_dt,to_date('01-01-1001','dd-mm-yyyy')) or
2194 nvl(ben_pep_shd.g_old_rec.inelg_rsn_cd,'X') <> nvl(l_rec.inelg_rsn_cd,'X') or
2195 nvl(ben_pep_shd.g_old_rec.frz_los_flag,'N') <> nvl(l_rec.frz_los_flag,'N') or
2196 nvl(ben_pep_shd.g_old_rec.frz_age_flag,'N') <> nvl(l_rec.frz_age_flag,'N') or
2197 nvl(ben_pep_shd.g_old_rec.frz_cmp_lvl_flag,'N') <> nvl(l_rec.frz_cmp_lvl_flag,'N') or
2198 nvl(ben_pep_shd.g_old_rec.frz_pct_fl_tm_flag,'N') <> nvl(l_rec.frz_pct_fl_tm_flag,'N') or
2199 nvl(ben_pep_shd.g_old_rec.frz_hrs_wkd_flag,'N') <> nvl(l_rec.frz_hrs_wkd_flag,'N') or
2200 nvl(ben_pep_shd.g_old_rec.frz_comb_age_and_los_flag,'N') <> nvl(l_rec.frz_comb_age_and_los_flag,'N') or
2201 nvl(ben_pep_shd.g_old_rec.dstr_rstcn_flag,'N') <> nvl(l_rec.dstr_rstcn_flag,'N') or
2202 nvl(ben_pep_shd.g_old_rec.pct_fl_tm_val,-1) <> nvl(l_rec.pct_fl_tm_val,-1) or
2203 nvl(ben_pep_shd.g_old_rec.wv_prtn_rsn_cd,'X') <> nvl(l_rec.wv_prtn_rsn_cd,'X') or
2204 nvl(ben_pep_shd.g_old_rec.pl_wvd_flag,'N') <> nvl(l_rec.pl_wvd_flag,'N') or
2205 nvl(ben_pep_shd.g_old_rec.rt_comp_ref_amt,-1) <> nvl(l_rec.rt_comp_ref_amt,-1) or
2206 nvl(ben_pep_shd.g_old_rec.rt_cmbn_age_n_los_val,-1) <> nvl(l_rec.rt_cmbn_age_n_los_val,-1) or
2207 nvl(ben_pep_shd.g_old_rec.rt_comp_ref_uom,'X') <> nvl(l_rec.rt_comp_ref_uom,'X') or
2208 nvl(ben_pep_shd.g_old_rec.rt_age_val,-1) <> nvl(l_rec.rt_age_val,-1) or
2209 nvl(ben_pep_shd.g_old_rec.rt_los_val,-1) <> nvl(l_rec.rt_los_val,-1) or
2210 nvl(ben_pep_shd.g_old_rec.rt_hrs_wkd_val,-1) <> nvl(l_rec.rt_hrs_wkd_val,-1) or
2211 nvl(ben_pep_shd.g_old_rec.rt_hrs_wkd_bndry_perd_cd,'X') <>
2212 nvl(l_rec.rt_hrs_wkd_bndry_perd_cd,'X') or
2213 nvl(ben_pep_shd.g_old_rec.rt_age_uom,'X') <> nvl(l_rec.rt_age_uom,'X') or
2214 nvl(ben_pep_shd.g_old_rec.rt_los_uom,'X') <> nvl(l_rec.rt_los_uom,'X') or
2215 nvl(ben_pep_shd.g_old_rec.rt_pct_fl_tm_val,-1) <> nvl(l_rec.rt_pct_fl_tm_val,-1) or
2216 nvl(ben_pep_shd.g_old_rec.rt_frz_los_flag,'N') <> nvl(l_rec.rt_frz_los_flag,'N') or
2217 nvl(ben_pep_shd.g_old_rec.rt_frz_age_flag,'N') <> nvl(l_rec.rt_frz_age_flag,'N') or
2218 nvl(ben_pep_shd.g_old_rec.rt_frz_cmp_lvl_flag,'N') <> nvl(l_rec.rt_frz_cmp_lvl_flag,'N') or
2219 nvl(ben_pep_shd.g_old_rec.rt_frz_pct_fl_tm_flag,'N') <> nvl(l_rec.rt_frz_pct_fl_tm_flag,'N') or
2220 nvl(ben_pep_shd.g_old_rec.rt_frz_hrs_wkd_flag,'N') <> nvl(l_rec.rt_frz_hrs_wkd_flag,'N') or
2221 nvl(ben_pep_shd.g_old_rec.rt_frz_comb_age_and_los_flag,'N') <>
2222 nvl(l_rec.rt_frz_comb_age_and_los_flag,'N') or
2223 nvl(ben_pep_shd.g_old_rec.once_r_cntug_cd,'X') <> nvl(l_rec.once_r_cntug_cd,'X') or
2224 nvl(ben_pep_shd.g_old_rec.pl_ordr_num,-1) <> nvl(l_rec.pl_ordr_num,-1) or
2225 nvl(ben_pep_shd.g_old_rec.plip_ordr_num,-1) <> nvl(l_rec.plip_ordr_num,-1) or
2226 nvl(ben_pep_shd.g_old_rec.ptip_ordr_num,-1) <> nvl(l_rec.ptip_ordr_num,-1)
2227 then
2228 -- do nothing
2229 null;
2230 --
2231 else
2232 --
2233 return;
2234 --
2235 end if;
2236 --
2237 end if;
2238 -- Call the supporting pre-update operation
2239
2240 If (p_datetrack_mode <> 'CORRECTION') then
2241 hr_utility.set_location(l_proc, 10);
2242 --
2243 open c_getovn
2244 (c_pep_id => p_elig_per_id
2245 );
2246 fetch c_getovn into l_object_version_number;
2247 close c_getovn;
2248 --
2249 ben_pep_shd.g_api_dml := true; -- Set the api dml status
2250 --
2251 -- Update the specified datetrack row setting the effective
2252 -- end date to the specified new effective end date.
2253 --
2254 update ben_elig_per_f t
2255 set t.effective_end_date = l_validation_start_date - 1,
2256 t.object_version_number = l_object_version_number
2257 where t.elig_per_id = p_elig_per_id
2258 and p_effective_date
2259 between t.effective_start_date and t.effective_end_date;
2260 --
2261 ben_pep_shd.g_api_dml := false; -- Unset the api dml status
2262 --
2263 If (p_datetrack_mode = 'UPDATE_OVERRIDE') then
2264 hr_utility.set_location(l_proc, 15);
2265 --
2266 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
2267 -- delete any future rows
2268 --
2269 If (p_datetrack_mode = 'DELETE_NEXT_CHANGE') then
2270 hr_utility.set_location(l_proc, 10);
2271 ben_pep_shd.g_api_dml := true; -- Set the api dml status
2272 --
2273 -- Delete the where the effective start date is equal
2274 -- to the validation end date.
2275 --
2276 delete from ben_elig_per_f
2277 where elig_per_id = p_elig_per_id
2278 and effective_start_date = l_validation_start_date;
2279 --
2280 ben_pep_shd.g_api_dml := false; -- Unset the api dml status
2281 Else
2282 hr_utility.set_location(l_proc, 15);
2283 ben_pep_shd.g_api_dml := true; -- Set the api dml status
2284 --
2285 -- Delete the row(s) where the effective start date is greater than
2286 -- or equal to the validation start date.
2287 --
2288 delete from ben_elig_per_f
2289 where elig_per_id = p_elig_per_id
2290 and effective_start_date >= l_validation_start_date;
2291 --
2292 ben_pep_shd.g_api_dml := false; -- Unset the api dml status
2293 End If;
2294 --
2295 End If;
2296 hr_utility.set_location(l_proc, 20);
2297 --
2298 -- We must now insert the updated row (insert_dml)
2299 --
2300 -- Get the object version number for the insert
2301 --
2302 open c_getovn
2303 (c_pep_id => p_elig_per_id
2304 );
2305 fetch c_getovn into l_rec.object_version_number;
2306 close c_getovn;
2307 --
2308 hr_utility.set_location('Dn DTAPI_GOVN:'||l_proc, 5);
2309 --
2310 -- Set the effective start and end dates to the corresponding
2311 -- validation start and end dates
2312 --
2313 l_rec.effective_start_date := l_validation_start_date;
2314 l_rec.effective_end_date := l_validation_end_date;
2315 --
2316 -- If the datetrack_mode is not INSERT then we must populate the WHO
2317 -- columns with the 'old' creation values and 'new' updated values.
2318 --
2319 If (p_datetrack_mode <> 'INSERT') then
2320 hr_utility.set_location(l_proc, 10);
2321 --
2322 -- Select the 'old' created values
2323 --
2324 Open C_Sel1;
2325 Fetch C_Sel1 Into l_created_by, l_creation_date;
2326 If C_Sel1%notfound Then
2327 --
2328 -- The previous 'old' created row has not been found. We need
2329 -- to error as an internal datetrack problem exists.
2330 --
2331 Close C_Sel1;
2332 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2333 fnd_message.set_token('PROCEDURE', l_proc);
2334 fnd_message.set_token('STEP','10');
2335 fnd_message.raise_error;
2336 End If;
2337 Close C_Sel1;
2338 --
2339 -- Set the AOL updated WHO values
2340 --
2341 l_last_update_date := sysdate;
2342 l_last_updated_by := fnd_global.user_id;
2343 l_last_update_login := fnd_global.login_id;
2344 End If;
2345 --
2346 ben_pep_shd.g_api_dml := true; -- Set the api dml status
2347 --
2348 -- Insert the row into: ben_elig_per_f
2349 --
2350 hr_utility.set_location('Ins PEP:'||l_proc, 5);
2351 insert into ben_elig_per_f
2352 (
2353 elig_per_id,
2354 effective_start_date,
2355 effective_end_date,
2356 business_group_id,
2357 pl_id,
2358 pgm_id,
2359 plip_id,
2360 ptip_id,
2361 ler_id,
2362 person_id,
2363 per_in_ler_id,
2364 dpnt_othr_pl_cvrd_rl_flag,
2365 prtn_ovridn_thru_dt,
2366 pl_key_ee_flag,
2367 pl_hghly_compd_flag,
2368 elig_flag,
2369 comp_ref_amt,
2370 cmbn_age_n_los_val,
2371 comp_ref_uom,
2372 age_val,
2373 los_val,
2374 prtn_end_dt,
2375 prtn_strt_dt,
2376 wait_perd_cmpltn_dt,
2377 wait_perd_strt_dt ,
2378 wv_ctfn_typ_cd,
2379 hrs_wkd_val,
2380 hrs_wkd_bndry_perd_cd,
2381 prtn_ovridn_flag,
2382 no_mx_prtn_ovrid_thru_flag,
2383 prtn_ovridn_rsn_cd,
2384 age_uom,
2385 los_uom,
2386 ovrid_svc_dt,
2387 inelg_rsn_cd,
2388 frz_los_flag,
2389 frz_age_flag,
2390 frz_cmp_lvl_flag,
2391 frz_pct_fl_tm_flag,
2392 frz_hrs_wkd_flag,
2393 frz_comb_age_and_los_flag,
2394 dstr_rstcn_flag,
2395 pct_fl_tm_val,
2396 wv_prtn_rsn_cd,
2397 pl_wvd_flag,
2398 rt_comp_ref_amt,
2399 rt_cmbn_age_n_los_val,
2400 rt_comp_ref_uom,
2401 rt_age_val,
2402 rt_los_val,
2403 rt_hrs_wkd_val,
2404 rt_hrs_wkd_bndry_perd_cd,
2405 rt_age_uom,
2406 rt_los_uom,
2407 rt_pct_fl_tm_val,
2408 rt_frz_los_flag,
2409 rt_frz_age_flag,
2410 rt_frz_cmp_lvl_flag,
2411 rt_frz_pct_fl_tm_flag,
2412 rt_frz_hrs_wkd_flag,
2413 rt_frz_comb_age_and_los_flag,
2414 once_r_cntug_cd,
2415 pl_ordr_num,
2416 plip_ordr_num,
2417 ptip_ordr_num,
2418 pep_attribute_category,
2419 pep_attribute1,
2420 pep_attribute2,
2421 pep_attribute3,
2422 pep_attribute4,
2423 pep_attribute5,
2424 pep_attribute6,
2425 pep_attribute7,
2426 pep_attribute8,
2427 pep_attribute9,
2428 pep_attribute10,
2429 pep_attribute11,
2430 pep_attribute12,
2431 pep_attribute13,
2432 pep_attribute14,
2433 pep_attribute15,
2434 pep_attribute16,
2435 pep_attribute17,
2436 pep_attribute18,
2437 pep_attribute19,
2438 pep_attribute20,
2439 pep_attribute21,
2440 pep_attribute22,
2441 pep_attribute23,
2442 pep_attribute24,
2443 pep_attribute25,
2444 pep_attribute26,
2445 pep_attribute27,
2446 pep_attribute28,
2447 pep_attribute29,
2448 pep_attribute30,
2449 request_id,
2450 program_application_id,
2451 program_id,
2452 program_update_date,
2453 object_version_number,
2454 created_by,
2455 creation_date,
2456 last_update_date,
2457 last_updated_by,
2458 last_update_login
2459 )
2460 Values
2461 (
2462 l_rec.elig_per_id,
2463 l_rec.effective_start_date,
2464 l_rec.effective_end_date,
2465 l_rec.business_group_id,
2466 l_rec.pl_id,
2467 l_rec.pgm_id,
2468 l_rec.plip_id,
2469 l_rec.ptip_id,
2470 l_rec.ler_id,
2471 l_rec.person_id,
2472 l_rec.per_in_ler_id,
2473 l_rec.dpnt_othr_pl_cvrd_rl_flag,
2474 l_rec.prtn_ovridn_thru_dt,
2475 l_rec.pl_key_ee_flag,
2476 l_rec.pl_hghly_compd_flag,
2477 l_rec.elig_flag,
2478 l_rec.comp_ref_amt,
2479 l_rec.cmbn_age_n_los_val,
2480 l_rec.comp_ref_uom,
2481 l_rec.age_val,
2482 l_rec.los_val,
2483 l_rec.prtn_end_dt,
2484 l_rec.prtn_strt_dt,
2485 l_rec.wait_perd_cmpltn_dt,
2486 l_rec.wait_perd_strt_dt ,
2487 l_rec.wv_ctfn_typ_cd,
2488 l_rec.hrs_wkd_val,
2489 l_rec.hrs_wkd_bndry_perd_cd,
2490 l_rec.prtn_ovridn_flag,
2491 l_rec.no_mx_prtn_ovrid_thru_flag,
2492 l_rec.prtn_ovridn_rsn_cd,
2493 l_rec.age_uom,
2494 l_rec.los_uom,
2495 l_rec.ovrid_svc_dt,
2496 l_rec.inelg_rsn_cd,
2497 l_rec.frz_los_flag,
2498 l_rec.frz_age_flag,
2499 l_rec.frz_cmp_lvl_flag,
2500 l_rec.frz_pct_fl_tm_flag,
2501 l_rec.frz_hrs_wkd_flag,
2502 l_rec.frz_comb_age_and_los_flag,
2503 l_rec.dstr_rstcn_flag,
2504 l_rec.pct_fl_tm_val,
2505 l_rec.wv_prtn_rsn_cd,
2506 l_rec.pl_wvd_flag,
2507 l_rec.rt_comp_ref_amt,
2508 l_rec.rt_cmbn_age_n_los_val,
2509 l_rec.rt_comp_ref_uom,
2510 l_rec.rt_age_val,
2511 l_rec.rt_los_val,
2512 l_rec.rt_hrs_wkd_val,
2513 l_rec.rt_hrs_wkd_bndry_perd_cd,
2514 l_rec.rt_age_uom,
2515 l_rec.rt_los_uom,
2516 l_rec.rt_pct_fl_tm_val,
2517 l_rec.rt_frz_los_flag,
2518 l_rec.rt_frz_age_flag,
2519 l_rec.rt_frz_cmp_lvl_flag,
2520 l_rec.rt_frz_pct_fl_tm_flag,
2521 l_rec.rt_frz_hrs_wkd_flag,
2522 l_rec.rt_frz_comb_age_and_los_flag,
2523 l_rec.once_r_cntug_cd,
2524 l_rec.pl_ordr_num,
2525 l_rec.plip_ordr_num,
2526 l_rec.ptip_ordr_num,
2527 l_rec.pep_attribute_category,
2528 l_rec.pep_attribute1,
2529 l_rec.pep_attribute2,
2530 l_rec.pep_attribute3,
2531 l_rec.pep_attribute4,
2532 l_rec.pep_attribute5,
2533 l_rec.pep_attribute6,
2534 l_rec.pep_attribute7,
2535 l_rec.pep_attribute8,
2536 l_rec.pep_attribute9,
2537 l_rec.pep_attribute10,
2538 l_rec.pep_attribute11,
2539 l_rec.pep_attribute12,
2540 l_rec.pep_attribute13,
2541 l_rec.pep_attribute14,
2542 l_rec.pep_attribute15,
2543 l_rec.pep_attribute16,
2544 l_rec.pep_attribute17,
2545 l_rec.pep_attribute18,
2546 l_rec.pep_attribute19,
2547 l_rec.pep_attribute20,
2548 l_rec.pep_attribute21,
2549 l_rec.pep_attribute22,
2550 l_rec.pep_attribute23,
2551 l_rec.pep_attribute24,
2552 l_rec.pep_attribute25,
2553 l_rec.pep_attribute26,
2554 l_rec.pep_attribute27,
2555 l_rec.pep_attribute28,
2556 l_rec.pep_attribute29,
2557 l_rec.pep_attribute30,
2558 l_rec.request_id,
2559 l_rec.program_application_id,
2560 l_rec.program_id,
2561 l_rec.program_update_date,
2562 l_rec.object_version_number,
2563 l_created_by,
2564 l_creation_date,
2565 l_last_update_date,
2566 l_last_updated_by,
2567 l_last_update_login
2568 );
2569 --
2570 ben_pep_shd.g_api_dml := false; -- Unset the api dml status
2571 End If;
2572 --
2573 -- update_dml
2574 --
2575 If (p_datetrack_mode = 'CORRECTION') then
2576 hr_utility.set_location(l_proc, 10);
2577 --
2578 -- Because we are updating a row we must get the next object
2579 -- version number.
2580 --
2581 open c_getovn
2582 (c_pep_id => l_rec.elig_per_id
2583 );
2584 fetch c_getovn into l_rec.object_version_number;
2585 close c_getovn;
2586 --
2587 ben_pep_shd.g_api_dml := true; -- Set the api dml status
2588 --
2589 -- Update the ben_elig_per_f Row
2590 --
2591 update ben_elig_per_f
2592 set
2593 elig_per_id = l_rec.elig_per_id,
2594 business_group_id = l_rec.business_group_id,
2595 pl_id = l_rec.pl_id,
2596 pgm_id = l_rec.pgm_id,
2597 plip_id = l_rec.plip_id,
2598 ptip_id = l_rec.ptip_id,
2599 ler_id = l_rec.ler_id,
2600 person_id = l_rec.person_id,
2601 per_in_ler_id = l_rec.per_in_ler_id,
2602 dpnt_othr_pl_cvrd_rl_flag = l_rec.dpnt_othr_pl_cvrd_rl_flag,
2603 prtn_ovridn_thru_dt = l_rec.prtn_ovridn_thru_dt,
2604 pl_key_ee_flag = l_rec.pl_key_ee_flag,
2605 pl_hghly_compd_flag = l_rec.pl_hghly_compd_flag,
2606 elig_flag = l_rec.elig_flag,
2607 comp_ref_amt = l_rec.comp_ref_amt,
2608 cmbn_age_n_los_val = l_rec.cmbn_age_n_los_val,
2609 comp_ref_uom = l_rec.comp_ref_uom,
2610 age_val = l_rec.age_val,
2611 los_val = l_rec.los_val,
2612 prtn_end_dt = l_rec.prtn_end_dt,
2613 prtn_strt_dt = l_rec.prtn_strt_dt,
2614 wait_perd_cmpltn_dt = l_rec.wait_perd_cmpltn_dt,
2615 wait_perd_strt_dt = l_rec.wait_perd_strt_dt,
2616 wv_ctfn_typ_cd = l_rec.wv_ctfn_typ_cd,
2617 hrs_wkd_val = l_rec.hrs_wkd_val,
2618 hrs_wkd_bndry_perd_cd = l_rec.hrs_wkd_bndry_perd_cd,
2619 prtn_ovridn_flag = l_rec.prtn_ovridn_flag,
2620 no_mx_prtn_ovrid_thru_flag = l_rec.no_mx_prtn_ovrid_thru_flag,
2621 prtn_ovridn_rsn_cd = l_rec.prtn_ovridn_rsn_cd,
2622 age_uom = l_rec.age_uom,
2623 los_uom = l_rec.los_uom,
2624 ovrid_svc_dt = l_rec.ovrid_svc_dt,
2625 inelg_rsn_cd = l_rec.inelg_rsn_cd,
2626 frz_los_flag = l_rec.frz_los_flag,
2627 frz_age_flag = l_rec.frz_age_flag,
2628 frz_cmp_lvl_flag = l_rec.frz_cmp_lvl_flag,
2629 frz_pct_fl_tm_flag = l_rec.frz_pct_fl_tm_flag,
2630 frz_hrs_wkd_flag = l_rec.frz_hrs_wkd_flag,
2631 frz_comb_age_and_los_flag = l_rec.frz_comb_age_and_los_flag,
2632 dstr_rstcn_flag = l_rec.dstr_rstcn_flag,
2633 pct_fl_tm_val = l_rec.pct_fl_tm_val,
2634 wv_prtn_rsn_cd = l_rec.wv_prtn_rsn_cd,
2635 pl_wvd_flag = l_rec.pl_wvd_flag,
2636 rt_comp_ref_amt = l_rec.rt_comp_ref_amt,
2637 rt_cmbn_age_n_los_val = l_rec.rt_cmbn_age_n_los_val,
2638 rt_comp_ref_uom = l_rec.rt_comp_ref_uom,
2639 rt_age_val = l_rec.rt_age_val,
2640 rt_los_val = l_rec.rt_los_val,
2641 rt_hrs_wkd_val = l_rec.rt_hrs_wkd_val,
2642 rt_hrs_wkd_bndry_perd_cd = l_rec.rt_hrs_wkd_bndry_perd_cd,
2643 rt_age_uom = l_rec.rt_age_uom,
2644 rt_los_uom = l_rec.rt_los_uom,
2645 rt_pct_fl_tm_val = l_rec.rt_pct_fl_tm_val,
2646 rt_frz_los_flag = l_rec.rt_frz_los_flag,
2647 rt_frz_age_flag = l_rec.rt_frz_age_flag,
2648 rt_frz_cmp_lvl_flag = l_rec.rt_frz_cmp_lvl_flag,
2649 rt_frz_pct_fl_tm_flag = l_rec.rt_frz_pct_fl_tm_flag,
2650 rt_frz_hrs_wkd_flag = l_rec.rt_frz_hrs_wkd_flag,
2651 rt_frz_comb_age_and_los_flag = l_rec.rt_frz_comb_age_and_los_flag,
2652 once_r_cntug_cd = l_rec.once_r_cntug_cd,
2653 pl_ordr_num = l_rec.pl_ordr_num,
2654 plip_ordr_num = l_rec.plip_ordr_num,
2655 ptip_ordr_num = l_rec.ptip_ordr_num,
2656 pep_attribute_category = l_rec.pep_attribute_category,
2657 pep_attribute1 = l_rec.pep_attribute1,
2658 pep_attribute2 = l_rec.pep_attribute2,
2659 pep_attribute3 = l_rec.pep_attribute3,
2660 pep_attribute4 = l_rec.pep_attribute4,
2661 pep_attribute5 = l_rec.pep_attribute5,
2662 pep_attribute6 = l_rec.pep_attribute6,
2663 pep_attribute7 = l_rec.pep_attribute7,
2664 pep_attribute8 = l_rec.pep_attribute8,
2665 pep_attribute9 = l_rec.pep_attribute9,
2666 pep_attribute10 = l_rec.pep_attribute10,
2667 pep_attribute11 = l_rec.pep_attribute11,
2668 pep_attribute12 = l_rec.pep_attribute12,
2669 pep_attribute13 = l_rec.pep_attribute13,
2670 pep_attribute14 = l_rec.pep_attribute14,
2671 pep_attribute15 = l_rec.pep_attribute15,
2672 pep_attribute16 = l_rec.pep_attribute16,
2673 pep_attribute17 = l_rec.pep_attribute17,
2674 pep_attribute18 = l_rec.pep_attribute18,
2675 pep_attribute19 = l_rec.pep_attribute19,
2676 pep_attribute20 = l_rec.pep_attribute20,
2677 pep_attribute21 = l_rec.pep_attribute21,
2678 pep_attribute22 = l_rec.pep_attribute22,
2679 pep_attribute23 = l_rec.pep_attribute23,
2680 pep_attribute24 = l_rec.pep_attribute24,
2681 pep_attribute25 = l_rec.pep_attribute25,
2682 pep_attribute26 = l_rec.pep_attribute26,
2683 pep_attribute27 = l_rec.pep_attribute27,
2684 pep_attribute28 = l_rec.pep_attribute28,
2685 pep_attribute29 = l_rec.pep_attribute29,
2686 pep_attribute30 = l_rec.pep_attribute30,
2687 request_id = l_rec.request_id,
2688 program_application_id = l_rec.program_application_id,
2689 program_id = l_rec.program_id,
2690 program_update_date = l_rec.program_update_date,
2691 object_version_number = l_rec.object_version_number
2692 where elig_per_id = l_rec.elig_per_id
2693 and effective_start_date = l_validation_start_date
2694 and effective_end_date = l_validation_end_date;
2695 --
2696 ben_pep_shd.g_api_dml := false; -- Unset the api dml status
2697 --
2698 -- Set the effective start and end dates
2699 --
2700 l_rec.effective_start_date := l_validation_start_date;
2701 l_rec.effective_end_date := l_validation_end_date;
2702 End If;
2703 --
2704 begin
2705 --
2706 -- Start of API User Hook for the after hook of update_Eligible_Person
2707 --
2708 ben_Eligible_Person_bk2.update_Eligible_Person_a
2709 (p_elig_per_id => p_elig_per_id
2710 ,p_effective_start_date => l_rec.effective_start_date
2711 ,p_effective_end_date => l_rec.effective_end_date
2712 ,p_business_group_id => p_business_group_id
2713 ,p_pl_id => p_pl_id
2714 ,p_pgm_id => p_pgm_id
2715 ,p_plip_id => p_plip_id
2716 ,p_ptip_id => p_ptip_id
2717 ,p_ler_id => p_ler_id
2718 ,p_person_id => p_person_id
2719 ,p_per_in_ler_id => p_per_in_ler_id
2720 ,p_dpnt_othr_pl_cvrd_rl_flag => p_dpnt_othr_pl_cvrd_rl_flag
2721 ,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
2722 ,p_pl_key_ee_flag => p_pl_key_ee_flag
2723 ,p_pl_hghly_compd_flag => p_pl_hghly_compd_flag
2724 ,p_elig_flag => p_elig_flag
2725 ,p_comp_ref_amt => p_comp_ref_amt
2726 ,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
2727 ,p_comp_ref_uom => p_comp_ref_uom
2728 ,p_age_val => p_age_val
2729 ,p_los_val => p_los_val
2730 ,p_prtn_end_dt => p_prtn_end_dt
2731 ,p_prtn_strt_dt => p_prtn_strt_dt
2732 ,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
2733 ,p_wait_perd_strt_dt => p_wait_perd_strt_dt
2734 ,p_wv_ctfn_typ_cd => p_wv_ctfn_typ_cd
2735 ,p_hrs_wkd_val => p_hrs_wkd_val
2736 ,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
2737 ,p_prtn_ovridn_flag => p_prtn_ovridn_flag
2738 ,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
2739 ,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
2740 ,p_age_uom => p_age_uom
2741 ,p_los_uom => p_los_uom
2742 ,p_ovrid_svc_dt => p_ovrid_svc_dt
2743 ,p_inelg_rsn_cd => p_inelg_rsn_cd
2744 ,p_frz_los_flag => p_frz_los_flag
2745 ,p_frz_age_flag => p_frz_age_flag
2746 ,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
2747 ,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
2748 ,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
2749 ,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
2750 ,p_dstr_rstcn_flag => p_dstr_rstcn_flag
2751 ,p_pct_fl_tm_val => p_pct_fl_tm_val
2752 ,p_wv_prtn_rsn_cd => p_wv_prtn_rsn_cd
2753 ,p_pl_wvd_flag => p_pl_wvd_flag
2754 ,p_rt_comp_ref_amt => p_rt_comp_ref_amt
2755 ,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
2756 ,p_rt_comp_ref_uom => p_rt_comp_ref_uom
2757 ,p_rt_age_val => p_rt_age_val
2758 ,p_rt_los_val => p_rt_los_val
2759 ,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
2760 ,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
2761 ,p_rt_age_uom => p_rt_age_uom
2762 ,p_rt_los_uom => p_rt_los_uom
2763 ,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
2764 ,p_rt_frz_los_flag => p_rt_frz_los_flag
2765 ,p_rt_frz_age_flag => p_rt_frz_age_flag
2766 ,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
2767 ,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
2768 ,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
2769 ,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
2770 ,p_once_r_cntug_cd => p_once_r_cntug_cd
2771 ,p_pl_ordr_num => p_pl_ordr_num
2772 ,p_plip_ordr_num => p_plip_ordr_num
2773 ,p_ptip_ordr_num => p_ptip_ordr_num
2774 ,p_pep_attribute_category => p_pep_attribute_category
2775 ,p_pep_attribute1 => p_pep_attribute1
2776 ,p_pep_attribute2 => p_pep_attribute2
2777 ,p_pep_attribute3 => p_pep_attribute3
2778 ,p_pep_attribute4 => p_pep_attribute4
2779 ,p_pep_attribute5 => p_pep_attribute5
2780 ,p_pep_attribute6 => p_pep_attribute6
2781 ,p_pep_attribute7 => p_pep_attribute7
2782 ,p_pep_attribute8 => p_pep_attribute8
2783 ,p_pep_attribute9 => p_pep_attribute9
2784 ,p_pep_attribute10 => p_pep_attribute10
2785 ,p_pep_attribute11 => p_pep_attribute11
2786 ,p_pep_attribute12 => p_pep_attribute12
2787 ,p_pep_attribute13 => p_pep_attribute13
2788 ,p_pep_attribute14 => p_pep_attribute14
2789 ,p_pep_attribute15 => p_pep_attribute15
2790 ,p_pep_attribute16 => p_pep_attribute16
2791 ,p_pep_attribute17 => p_pep_attribute17
2792 ,p_pep_attribute18 => p_pep_attribute18
2793 ,p_pep_attribute19 => p_pep_attribute19
2794 ,p_pep_attribute20 => p_pep_attribute20
2795 ,p_pep_attribute21 => p_pep_attribute21
2796 ,p_pep_attribute22 => p_pep_attribute22
2797 ,p_pep_attribute23 => p_pep_attribute23
2798 ,p_pep_attribute24 => p_pep_attribute24
2799 ,p_pep_attribute25 => p_pep_attribute25
2800 ,p_pep_attribute26 => p_pep_attribute26
2801 ,p_pep_attribute27 => p_pep_attribute27
2802 ,p_pep_attribute28 => p_pep_attribute28
2803 ,p_pep_attribute29 => p_pep_attribute29
2804 ,p_pep_attribute30 => p_pep_attribute30
2805 ,p_request_id => p_request_id
2806 ,p_program_application_id => p_program_application_id
2807 ,p_program_id => p_program_id
2808 ,p_program_update_date => p_program_update_date
2809 ,p_object_version_number => l_object_version_number
2810 ,p_effective_date => trunc(p_effective_date)
2811 ,p_datetrack_mode => p_datetrack_mode);
2812 --
2813 exception
2814 --
2815 when hr_api.cannot_find_prog_unit then
2816 --
2817 hr_api.cannot_find_prog_unit_error
2818 (p_module_name => 'UPDATE_Eligible_Person'
2819 ,p_hook_type => 'AP');
2820 --
2821 -- End of API User Hook for the after hook of update_Eligible_Person
2822 --
2823 end;
2824 --
2825 hr_utility.set_location(l_proc, 60);
2826 --
2827 -- When in validation only mode raise the Validate_Enabled exception
2828 --
2829 if p_validate then
2830 raise hr_api.validate_enabled;
2831 end if;
2832 --
2833 -- Set all output arguments
2834 --
2835 p_object_version_number := l_rec.object_version_number;
2836 p_effective_start_date := l_rec.effective_start_date;
2837 p_effective_end_date := l_rec.effective_end_date;
2838 --
2839 hr_utility.set_location(' Leaving:'||l_proc, 70);
2840 --
2841 exception
2842 --
2843 when hr_api.validate_enabled then
2844 --
2845 -- As the Validate_Enabled exception has been raised
2846 -- we must rollback to the savepoint
2847 --
2848 ROLLBACK TO update_perf_Eligible_Person;
2849 --
2850 -- Only set output warning arguments
2851 -- (Any key or derived arguments must be set to null
2852 -- when validation only mode is being used.)
2853 --
2854 hr_utility.set_location(' Leaving:'||l_proc, 80);
2855 --
2856 when others then
2857 --
2858 -- A validation or unexpected error has occured
2859 --
2860 ROLLBACK TO update_perf_Eligible_Person;
2861 p_effective_start_date := null;
2862 p_effective_end_date := null;
2863 p_object_version_number := l_object_version_number;
2864 raise;
2865 --
2866 end update_perf_Eligible_Person;
2867 --
2868 -- ----------------------------------------------------------------------------
2869 -- |------------------< create_perf_Elig_Person_Option >----------------------|
2870 -- ----------------------------------------------------------------------------
2871 --
2872 procedure create_perf_Elig_Person_Option
2873 (p_validate in boolean default false
2874 ,p_elig_per_opt_id out nocopy number
2875 ,p_elig_per_id in number default null
2876 ,p_effective_start_date out nocopy date
2877 ,p_effective_end_date out nocopy date
2878 ,p_prtn_ovridn_flag in varchar2 default null
2879 ,p_prtn_ovridn_thru_dt in date default null
2880 ,p_no_mx_prtn_ovrid_thru_flag in varchar2 default null
2881 ,p_elig_flag in varchar2 default null
2882 ,p_prtn_strt_dt in date default null
2883 ,p_prtn_end_dt in date default null
2884 -- ,p_wait_perd_cmpltn_dt in date default null
2885 ,p_wait_perd_cmpltn_date in date default null
2886 ,p_wait_perd_strt_dt in date default null
2887 ,p_prtn_ovridn_rsn_cd in varchar2 default null
2888 ,p_pct_fl_tm_val in number default null
2889 ,p_opt_id in number default null
2890 ,p_per_in_ler_id in number default null
2891 ,p_rt_comp_ref_amt in number default null
2892 ,p_rt_cmbn_age_n_los_val in number default null
2893 ,p_rt_comp_ref_uom in varchar2 default null
2894 ,p_rt_age_val in number default null
2895 ,p_rt_los_val in number default null
2896 ,p_rt_hrs_wkd_val in number default null
2897 ,p_rt_hrs_wkd_bndry_perd_cd in varchar2 default null
2898 ,p_rt_age_uom in varchar2 default null
2899 ,p_rt_los_uom in varchar2 default null
2900 ,p_rt_pct_fl_tm_val in number default null
2901 ,p_rt_frz_los_flag in varchar2 default 'N'
2902 ,p_rt_frz_age_flag in varchar2 default 'N'
2903 ,p_rt_frz_cmp_lvl_flag in varchar2 default 'N'
2904 ,p_rt_frz_pct_fl_tm_flag in varchar2 default 'N'
2905 ,p_rt_frz_hrs_wkd_flag in varchar2 default 'N'
2906 ,p_rt_frz_comb_age_and_los_flag in varchar2 default 'N'
2907 ,p_comp_ref_amt in number default null
2908 ,p_cmbn_age_n_los_val in number default null
2909 ,p_comp_ref_uom in varchar2 default null
2910 ,p_age_val in number default null
2911 ,p_los_val in number default null
2912 ,p_hrs_wkd_val in number default null
2913 ,p_hrs_wkd_bndry_perd_cd in varchar2 default null
2914 ,p_age_uom in varchar2 default null
2915 ,p_los_uom in varchar2 default null
2916 ,p_frz_los_flag in varchar2 default 'N'
2917 ,p_frz_age_flag in varchar2 default 'N'
2918 ,p_frz_cmp_lvl_flag in varchar2 default 'N'
2919 ,p_frz_pct_fl_tm_flag in varchar2 default 'N'
2920 ,p_frz_hrs_wkd_flag in varchar2 default 'N'
2921 ,p_frz_comb_age_and_los_flag in varchar2 default 'N'
2922 ,p_ovrid_svc_dt in date default null
2923 ,p_inelg_rsn_cd in varchar2 default null
2924 ,p_once_r_cntug_cd in varchar2 default null
2925 ,p_oipl_ordr_num in number default null
2926 ,p_business_group_id in number default null
2927 ,p_epo_attribute_category in varchar2 default null
2928 ,p_epo_attribute1 in varchar2 default null
2929 ,p_epo_attribute2 in varchar2 default null
2930 ,p_epo_attribute3 in varchar2 default null
2931 ,p_epo_attribute4 in varchar2 default null
2932 ,p_epo_attribute5 in varchar2 default null
2933 ,p_epo_attribute6 in varchar2 default null
2934 ,p_epo_attribute7 in varchar2 default null
2935 ,p_epo_attribute8 in varchar2 default null
2936 ,p_epo_attribute9 in varchar2 default null
2937 ,p_epo_attribute10 in varchar2 default null
2938 ,p_epo_attribute11 in varchar2 default null
2939 ,p_epo_attribute12 in varchar2 default null
2940 ,p_epo_attribute13 in varchar2 default null
2941 ,p_epo_attribute14 in varchar2 default null
2942 ,p_epo_attribute15 in varchar2 default null
2943 ,p_epo_attribute16 in varchar2 default null
2944 ,p_epo_attribute17 in varchar2 default null
2945 ,p_epo_attribute18 in varchar2 default null
2946 ,p_epo_attribute19 in varchar2 default null
2947 ,p_epo_attribute20 in varchar2 default null
2948 ,p_epo_attribute21 in varchar2 default null
2949 ,p_epo_attribute22 in varchar2 default null
2950 ,p_epo_attribute23 in varchar2 default null
2951 ,p_epo_attribute24 in varchar2 default null
2952 ,p_epo_attribute25 in varchar2 default null
2953 ,p_epo_attribute26 in varchar2 default null
2954 ,p_epo_attribute27 in varchar2 default null
2955 ,p_epo_attribute28 in varchar2 default null
2956 ,p_epo_attribute29 in varchar2 default null
2957 ,p_epo_attribute30 in varchar2 default null
2958 ,p_request_id in number default null
2959 ,p_program_application_id in number default null
2960 ,p_program_id in number default null
2961 ,p_program_update_date in date default null
2962 ,p_object_version_number out nocopy number
2963 ,p_effective_date in date
2964 ,p_override_validation in boolean default false
2965 )
2966 is
2967 --
2968 l_proc varchar2(72) := g_package||'create_perf_Elig_Person_Option';
2969 --
2970 -- Declare cursors and local variables
2971 --
2972 l_object_version_number ben_elig_per_opt_f.object_version_number%TYPE;
2973 l_elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%TYPE;
2974 l_effective_start_date ben_elig_per_opt_f.effective_start_date%TYPE;
2975 l_effective_end_date ben_elig_per_opt_f.effective_end_date%TYPE;
2976 --
2977 l_created_by ben_elig_per_opt_f.created_by%TYPE;
2978 l_creation_date ben_elig_per_opt_f.creation_date%TYPE;
2979 l_last_update_date ben_elig_per_opt_f.last_update_date%TYPE;
2980 l_last_updated_by ben_elig_per_opt_f.last_updated_by%TYPE;
2981 l_last_update_login ben_elig_per_opt_f.last_update_login%TYPE;
2982 --
2983 Cursor C_Sel1 is select ben_elig_per_opt_f_s.nextval from sys.dual;
2984 --
2985 l_minmax_rec ben_batch_dt_api.gtyp_dtsum_row;
2986 --
2987 begin
2988 --
2989 hr_utility.set_location('Entering:'|| l_proc, 10);
2990 --
2991 -- Issue a savepoint if operating in validation only mode
2992 --
2993 savepoint create_perf_Elig_Person_Option;
2994 --
2995 -- Derive maximum start and end dates
2996 --
2997 l_effective_start_date := p_effective_date;
2998 l_effective_end_date := hr_api.g_eot;
2999 --
3000 -- Elig Per
3001 --
3002 if p_elig_per_id is not null then
3003 --
3004 ben_batch_dt_api.get_elig_perobject
3005 (p_elig_per_id => p_elig_per_id
3006 ,p_rec => l_minmax_rec
3007 );
3008 --
3009 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
3010 (p_effective_date => p_effective_date
3011 ,p_parcolumn_name => 'elig_per_id'
3012 ,p_min_esd => l_minmax_rec.min_esd
3013 ,p_max_eed => l_minmax_rec.max_eed
3014 --
3015 ,p_esd => l_effective_start_date
3016 ,p_eed => l_effective_end_date
3017 );
3018 --
3019 end if;
3020 --
3021 -- Insert the row
3022 --
3023 -- Set the object version number for the insert
3024 --
3025 l_object_version_number := 1;
3026 --
3027 ben_epo_shd.g_api_dml := true; -- Set the api dml status
3028 --
3029 -- Select the next sequence number
3030 --
3031 Open C_Sel1;
3032 Fetch C_Sel1 Into l_elig_per_opt_id;
3033 Close C_Sel1;
3034 --
3035 -- Insert the row into: ben_elig_per_f
3036 --
3037 hr_utility.set_location('Insert: '||l_proc, 5);
3038 hr_utility.set_location('Ins EPO:'||l_proc, 5);
3039 insert into ben_elig_per_opt_f
3040 ( elig_per_opt_id,
3041 elig_per_id,
3042 effective_start_date,
3043 effective_end_date,
3044 prtn_ovridn_flag,
3045 prtn_ovridn_thru_dt,
3046 no_mx_prtn_ovrid_thru_flag,
3047 elig_flag,
3048 prtn_strt_dt,
3049 prtn_end_dt,
3050 --wait_perd_cmpltn_dt,
3051 wait_perd_cmpltn_date,
3052 wait_perd_strt_dt,
3053 prtn_ovridn_rsn_cd,
3054 pct_fl_tm_val,
3055 opt_id,
3056 per_in_ler_id,
3057 rt_comp_ref_amt,
3058 rt_cmbn_age_n_los_val,
3059 rt_comp_ref_uom,
3060 rt_age_val,
3061 rt_los_val,
3062 rt_hrs_wkd_val,
3063 rt_hrs_wkd_bndry_perd_cd,
3064 rt_age_uom,
3065 rt_los_uom,
3066 rt_pct_fl_tm_val,
3067 rt_frz_los_flag,
3068 rt_frz_age_flag,
3069 rt_frz_cmp_lvl_flag,
3070 rt_frz_pct_fl_tm_flag,
3071 rt_frz_hrs_wkd_flag,
3072 rt_frz_comb_age_and_los_flag,
3073 comp_ref_amt,
3074 cmbn_age_n_los_val,
3075 comp_ref_uom,
3076 age_val,
3077 los_val,
3078 hrs_wkd_val,
3079 hrs_wkd_bndry_perd_cd,
3080 age_uom,
3081 los_uom,
3082 frz_los_flag,
3083 frz_age_flag,
3084 frz_cmp_lvl_flag,
3085 frz_pct_fl_tm_flag,
3086 frz_hrs_wkd_flag,
3087 frz_comb_age_and_los_flag,
3088 ovrid_svc_dt,
3089 inelg_rsn_cd,
3090 once_r_cntug_cd,
3091 oipl_ordr_num,
3092 business_group_id,
3093 epo_attribute_category,
3094 epo_attribute1,
3095 epo_attribute2,
3096 epo_attribute3,
3097 epo_attribute4,
3098 epo_attribute5,
3099 epo_attribute6,
3100 epo_attribute7,
3101 epo_attribute8,
3102 epo_attribute9,
3103 epo_attribute10,
3104 epo_attribute11,
3105 epo_attribute12,
3106 epo_attribute13,
3107 epo_attribute14,
3108 epo_attribute15,
3109 epo_attribute16,
3110 epo_attribute17,
3111 epo_attribute18,
3112 epo_attribute19,
3113 epo_attribute20,
3114 epo_attribute21,
3115 epo_attribute22,
3116 epo_attribute23,
3117 epo_attribute24,
3118 epo_attribute25,
3119 epo_attribute26,
3120 epo_attribute27,
3121 epo_attribute28,
3122 epo_attribute29,
3123 epo_attribute30,
3124 request_id,
3125 program_application_id,
3126 program_id,
3127 program_update_date,
3128 object_version_number,
3129 created_by,
3130 creation_date,
3131 last_update_date,
3132 last_updated_by,
3133 last_update_login
3134 )
3135 Values
3136 ( l_elig_per_opt_id,
3137 p_elig_per_id,
3138 l_effective_start_date,
3139 l_effective_end_date,
3140 p_prtn_ovridn_flag,
3141 p_prtn_ovridn_thru_dt,
3142 p_no_mx_prtn_ovrid_thru_flag,
3143 p_elig_flag,
3144 p_prtn_strt_dt,
3145 p_prtn_end_dt,
3146 -- p_wait_perd_cmpltn_dt,
3147 p_wait_perd_cmpltn_date,
3148 p_wait_perd_strt_dt,
3149 p_prtn_ovridn_rsn_cd,
3150 p_pct_fl_tm_val,
3151 p_opt_id,
3152 p_per_in_ler_id,
3153 p_rt_comp_ref_amt,
3154 p_rt_cmbn_age_n_los_val,
3155 p_rt_comp_ref_uom,
3156 p_rt_age_val,
3157 p_rt_los_val,
3158 p_rt_hrs_wkd_val,
3159 p_rt_hrs_wkd_bndry_perd_cd,
3160 p_rt_age_uom,
3161 p_rt_los_uom,
3162 p_rt_pct_fl_tm_val,
3163 p_rt_frz_los_flag,
3164 p_rt_frz_age_flag,
3165 p_rt_frz_cmp_lvl_flag,
3166 p_rt_frz_pct_fl_tm_flag,
3167 p_rt_frz_hrs_wkd_flag,
3168 p_rt_frz_comb_age_and_los_flag,
3169 p_comp_ref_amt,
3170 p_cmbn_age_n_los_val,
3171 p_comp_ref_uom,
3172 p_age_val,
3173 p_los_val,
3174 p_hrs_wkd_val,
3175 p_hrs_wkd_bndry_perd_cd,
3176 p_age_uom,
3177 p_los_uom,
3178 p_frz_los_flag,
3179 p_frz_age_flag,
3180 p_frz_cmp_lvl_flag,
3181 p_frz_pct_fl_tm_flag,
3182 p_frz_hrs_wkd_flag,
3183 p_frz_comb_age_and_los_flag,
3184 p_ovrid_svc_dt,
3185 p_inelg_rsn_cd,
3186 p_once_r_cntug_cd,
3187 p_oipl_ordr_num,
3188 p_business_group_id,
3189 p_epo_attribute_category,
3190 p_epo_attribute1,
3191 p_epo_attribute2,
3192 p_epo_attribute3,
3193 p_epo_attribute4,
3194 p_epo_attribute5,
3195 p_epo_attribute6,
3196 p_epo_attribute7,
3197 p_epo_attribute8,
3198 p_epo_attribute9,
3199 p_epo_attribute10,
3200 p_epo_attribute11,
3201 p_epo_attribute12,
3202 p_epo_attribute13,
3203 p_epo_attribute14,
3204 p_epo_attribute15,
3205 p_epo_attribute16,
3206 p_epo_attribute17,
3207 p_epo_attribute18,
3208 p_epo_attribute19,
3209 p_epo_attribute20,
3210 p_epo_attribute21,
3211 p_epo_attribute22,
3212 p_epo_attribute23,
3213 p_epo_attribute24,
3214 p_epo_attribute25,
3215 p_epo_attribute26,
3216 p_epo_attribute27,
3217 p_epo_attribute28,
3218 p_epo_attribute29,
3219 p_epo_attribute30,
3220 p_request_id,
3221 p_program_application_id,
3222 p_program_id,
3223 p_program_update_date,
3224 l_object_version_number,
3225 l_created_by,
3226 l_creation_date,
3227 l_last_update_date,
3228 l_last_updated_by,
3229 l_last_update_login
3230 );
3231 hr_utility.set_location('Dn Insert: '||l_proc, 5);
3232 --
3233 ben_epo_shd.g_api_dml := false; -- Unset the api dml status
3234 --
3235 -- When in validation only mode raise the Validate_Enabled exception
3236 --
3237 if p_validate then
3238 raise hr_api.validate_enabled;
3239 end if;
3240 --
3241 -- Set all output arguments
3242 --
3243 p_elig_per_opt_id := l_elig_per_opt_id;
3244 p_effective_start_date := l_effective_start_date;
3245 p_effective_end_date := l_effective_end_date;
3246 p_object_version_number := l_object_version_number;
3247 --
3248 hr_utility.set_location(' Leaving:'||l_proc, 70);
3249 --
3250 exception
3251 --
3252 when hr_api.validate_enabled then
3253 --
3254 -- As the Validate_Enabled exception has been raised
3255 -- we must rollback to the savepoint
3256 --
3257 ROLLBACK TO create_perf_Elig_Person_Option;
3258 --
3259 -- Only set output warning arguments
3260 -- (Any key or derived arguments must be set to null
3261 -- when validation only mode is being used.)
3262 --
3263 p_elig_per_opt_id := null;
3264 p_effective_start_date := null;
3265 p_effective_end_date := null;
3266 p_object_version_number := null;
3267 hr_utility.set_location(' Leaving:'||l_proc, 80);
3268 --
3269 when others then
3270 --
3271 -- A validation or unexpected error has occured
3272 --
3273 ROLLBACK TO create_perf_Elig_Person_Option;
3274 --
3275 p_elig_per_opt_id := null;
3276 p_effective_start_date := null;
3277 p_effective_end_date := null;
3278 p_object_version_number := null;
3279 hr_utility.set_location(' Leaving:'||l_proc, 80);
3280 --
3281 raise;
3282 --
3283 end create_perf_Elig_Person_Option;
3284 -- ----------------------------------------------------------------------------
3285 -- |----------------------< update_perf_Elig_Person_Option >------------------|
3286 -- ----------------------------------------------------------------------------
3287 --
3288 -- ----------------------------------------------------------------------------
3289 -- |---------------------------------< lck >----------------------------------|
3290 -- ----------------------------------------------------------------------------
3291 Procedure lck
3292 (p_effective_date in date
3293 ,p_datetrack_mode in varchar2
3294 ,p_elig_per_opt_id in number
3295 ,p_object_version_number in number
3296 ,p_validation_start_date out nocopy date
3297 ,p_validation_end_date out nocopy date
3298 )
3299 is
3300 --
3301 l_proc varchar2(72) := g_package||'lck';
3302 l_validation_start_date date;
3303 l_validation_end_date date;
3304 l_object_invalid exception;
3305 l_argument varchar2(30);
3306 --
3307 -- Cursor C_Sel1 selects the current locked row as of session date
3308 -- ensuring that the object version numbers match.
3309 --
3310 Cursor C_Sel1 is
3311 select
3312 elig_per_opt_id,
3313 elig_per_id,
3314 effective_start_date,
3315 effective_end_date,
3316 prtn_ovridn_flag,
3317 prtn_ovridn_thru_dt,
3318 no_mx_prtn_ovrid_thru_flag,
3319 elig_flag,
3320 prtn_strt_dt,
3321 prtn_end_dt,
3322 -- wait_perd_cmpltn_dt,
3323 wait_perd_cmpltn_date,
3324 wait_perd_strt_dt,
3325 prtn_ovridn_rsn_cd,
3326 pct_fl_tm_val,
3327 opt_id,
3328 per_in_ler_id,
3329 rt_comp_ref_amt,
3330 rt_cmbn_age_n_los_val,
3331 rt_comp_ref_uom,
3332 rt_age_val,
3333 rt_los_val,
3334 rt_hrs_wkd_val,
3335 rt_hrs_wkd_bndry_perd_cd,
3336 rt_age_uom,
3337 rt_los_uom,
3338 rt_pct_fl_tm_val,
3339 rt_frz_los_flag,
3340 rt_frz_age_flag,
3341 rt_frz_cmp_lvl_flag,
3342 rt_frz_pct_fl_tm_flag,
3343 rt_frz_hrs_wkd_flag,
3344 rt_frz_comb_age_and_los_flag,
3345 comp_ref_amt,
3346 cmbn_age_n_los_val,
3347 comp_ref_uom,
3348 age_val,
3349 los_val,
3350 hrs_wkd_val,
3351 hrs_wkd_bndry_perd_cd,
3352 age_uom,
3353 los_uom,
3354 frz_los_flag,
3355 frz_age_flag,
3356 frz_cmp_lvl_flag,
3357 frz_pct_fl_tm_flag,
3358 frz_hrs_wkd_flag,
3359 frz_comb_age_and_los_flag,
3360 ovrid_svc_dt,
3361 inelg_rsn_cd,
3362 once_r_cntug_cd,
3363 oipl_ordr_num,
3364 business_group_id,
3365 epo_attribute_category,
3366 epo_attribute1,
3367 epo_attribute2,
3368 epo_attribute3,
3369 epo_attribute4,
3370 epo_attribute5,
3371 epo_attribute6,
3372 epo_attribute7,
3373 epo_attribute8,
3374 epo_attribute9,
3375 epo_attribute10,
3376 epo_attribute11,
3377 epo_attribute12,
3378 epo_attribute13,
3379 epo_attribute14,
3380 epo_attribute15,
3381 epo_attribute16,
3382 epo_attribute17,
3383 epo_attribute18,
3384 epo_attribute19,
3385 epo_attribute20,
3386 epo_attribute21,
3387 epo_attribute22,
3388 epo_attribute23,
3389 epo_attribute24,
3390 epo_attribute25,
3391 epo_attribute26,
3392 epo_attribute27,
3393 epo_attribute28,
3394 epo_attribute29,
3395 epo_attribute30,
3396 request_id,
3397 program_application_id,
3398 program_id,
3399 program_update_date,
3400 object_version_number
3401 from ben_elig_per_opt_f
3402 where elig_per_opt_id = p_elig_per_opt_id
3403 and p_effective_date
3404 between effective_start_date and effective_end_date;
3405 --
3406 Begin
3407 hr_utility.set_location('Entering:'||l_proc, 5);
3408 --
3409 -- Ensure that all the mandatory arguments are not null
3410 --
3411 hr_api.mandatory_arg_error(p_api_name => l_proc,
3412 p_argument => 'effective_date',
3413 p_argument_value => p_effective_date);
3414 --
3415 hr_api.mandatory_arg_error(p_api_name => l_proc,
3416 p_argument => 'datetrack_mode',
3417 p_argument_value => p_datetrack_mode);
3418 --
3419 hr_api.mandatory_arg_error(p_api_name => l_proc,
3420 p_argument => 'elig_per_opt_id',
3421 p_argument_value => p_elig_per_opt_id);
3422 --
3423 hr_api.mandatory_arg_error(p_api_name => l_proc,
3424 p_argument => 'object_version_number',
3425 p_argument_value => p_object_version_number);
3426 --
3427 -- Check to ensure the datetrack mode is not INSERT.
3428 --
3429 If (p_datetrack_mode <> 'INSERT') then
3430 --
3431 -- We must select and lock the current row.
3432 --
3433 Open C_Sel1;
3434 Fetch C_Sel1 Into ben_epo_shd.g_old_rec;
3435 If C_Sel1%notfound then
3436 Close C_Sel1;
3437 --
3438 -- The primary key is invalid therefore we must error
3439 --
3440 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
3441 fnd_message.raise_error;
3442 End If;
3443 Close C_Sel1;
3444 If (p_object_version_number <> ben_epo_shd.g_old_rec.object_version_number) Then
3445 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
3446 fnd_message.raise_error;
3447 End If;
3448 hr_utility.set_location(l_proc, 15);
3449 --
3450 --
3451 -- Validate the datetrack mode mode getting the validation start
3452 -- and end dates for the specified datetrack operation.
3453 --
3454 dt_api.validate_dt_mode
3455 (p_effective_date => p_effective_date,
3456 p_datetrack_mode => p_datetrack_mode,
3457 p_base_table_name => 'ben_elig_per_opt_f',
3458 p_base_key_column => 'elig_per_opt_id',
3459 p_base_key_value => p_elig_per_opt_id,
3460 /*
3461 p_parent_table_name1 => 'ben_elig_per_f',
3462 p_parent_key_column1 => 'elig_per_id',
3463 p_parent_key_value1 => g_old_rec.elig_per_id,
3464 */
3465 p_parent_table_name2 => 'ben_opt_f',
3466 p_parent_key_column2 => 'opt_id',
3467 p_parent_key_value2 => ben_epo_shd.g_old_rec.opt_id,
3468 p_enforce_foreign_locking => false,
3469 p_validation_start_date => l_validation_start_date,
3470 p_validation_end_date => l_validation_end_date);
3471 --
3472 Else
3473 --
3474 -- We are doing a datetrack 'INSERT' which is illegal within this
3475 -- procedure therefore we must error (note: to lck on insert the
3476 -- private procedure ins_lck should be called).
3477 --
3478 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3479 fnd_message.set_token('PROCEDURE', l_proc);
3480 fnd_message.set_token('STEP','20');
3481 fnd_message.raise_error;
3482 End If;
3483 --
3484 -- Set the validation start and end date OUT arguments
3485 --
3486 p_validation_start_date := l_validation_start_date;
3487 p_validation_end_date := l_validation_end_date;
3488 --
3489 hr_utility.set_location(' Leaving:'||l_proc, 30);
3490 --
3491 -- We need to trap the ORA LOCK exception
3492 --
3493 Exception
3494 When HR_Api.Object_Locked then
3495 --
3496 -- The object is locked therefore we need to supply a meaningful
3497 -- error message.
3498 --
3499 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
3500 fnd_message.set_token('TABLE_NAME', 'ben_elig_per_opt_f');
3501 fnd_message.raise_error;
3502 When l_object_invalid then
3503 --
3504 -- The object doesn't exist or is invalid
3505 --
3506 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
3507 fnd_message.set_token('TABLE_NAME', 'ben_elig_per_opt_f');
3508 fnd_message.raise_error;
3509 End lck;
3510 -- ----------------------------------------------------------------------------
3511 -- |---------------------------< epo_convert_defs >---------------------------|
3512 -- ----------------------------------------------------------------------------
3513 --
3514 Procedure epo_convert_defs
3515 (p_rec in out nocopy ben_epo_shd.g_rec_type
3516 )
3517 is
3518 --
3519 l_proc varchar2(72) := g_package||'epo_convert_defs';
3520 --
3521 Begin
3522 --
3523 hr_utility.set_location('Entering:'||l_proc, 5);
3524 --
3525 -- We must now examine each argument value in the
3526 -- p_rec plsql record structure
3527 -- to see if a system default is being used. If a system default
3528 -- is being used then we must set to the 'current' argument value.
3529 --
3530 If (p_rec.elig_per_id = hr_api.g_number) then
3531 p_rec.elig_per_id :=
3532 ben_epo_shd.g_old_rec.elig_per_id;
3533 End If;
3534 If (p_rec.prtn_ovridn_flag = hr_api.g_varchar2) then
3535 p_rec.prtn_ovridn_flag :=
3536 ben_epo_shd.g_old_rec.prtn_ovridn_flag;
3537 End If;
3538 If (p_rec.prtn_ovridn_thru_dt = hr_api.g_date) then
3539 p_rec.prtn_ovridn_thru_dt :=
3540 ben_epo_shd.g_old_rec.prtn_ovridn_thru_dt;
3541 End If;
3542 If (p_rec.no_mx_prtn_ovrid_thru_flag = hr_api.g_varchar2) then
3543 p_rec.no_mx_prtn_ovrid_thru_flag :=
3544 ben_epo_shd.g_old_rec.no_mx_prtn_ovrid_thru_flag;
3545 End If;
3546 If (p_rec.elig_flag = hr_api.g_varchar2) then
3547 p_rec.elig_flag :=
3548 ben_epo_shd.g_old_rec.elig_flag;
3549 End If;
3550 If (p_rec.prtn_strt_dt = hr_api.g_date) then
3551 p_rec.prtn_strt_dt :=
3552 ben_epo_shd.g_old_rec.prtn_strt_dt;
3553 End If;
3554 If (p_rec.prtn_end_dt = hr_api.g_date) then
3555 p_rec.prtn_end_dt :=
3556 ben_epo_shd.g_old_rec.prtn_end_dt;
3557 End If;
3558 /*
3559 If (p_rec.wait_perd_cmpltn_dt = hr_api.g_date) then
3560 p_rec.wait_perd_cmpltn_dt :=
3561 ben_epo_shd.g_old_rec.wait_perd_cmpltn_dt;
3562 End If;
3563 */
3564 If (p_rec.wait_perd_cmpltn_date = hr_api.g_date) then
3565 p_rec.wait_perd_cmpltn_date :=
3566 ben_epo_shd.g_old_rec.wait_perd_cmpltn_date;
3567 End If;
3568 If (p_rec.wait_perd_strt_dt = hr_api.g_date) then
3569 p_rec.wait_perd_strt_dt :=
3570 ben_epo_shd.g_old_rec.wait_perd_strt_dt;
3571 End If;
3572 If (p_rec.prtn_ovridn_rsn_cd = hr_api.g_varchar2) then
3573 p_rec.prtn_ovridn_rsn_cd :=
3574 ben_epo_shd.g_old_rec.prtn_ovridn_rsn_cd;
3575 End If;
3576 If (p_rec.pct_fl_tm_val = hr_api.g_number) then
3577 p_rec.pct_fl_tm_val :=
3578 ben_epo_shd.g_old_rec.pct_fl_tm_val;
3579 End If;
3580 If (p_rec.opt_id = hr_api.g_number) then
3581 p_rec.opt_id :=
3582 ben_epo_shd.g_old_rec.opt_id;
3583 End If;
3584 If (p_rec.per_in_ler_id = hr_api.g_number) then
3585 p_rec.per_in_ler_id :=
3586 ben_epo_shd.g_old_rec.per_in_ler_id;
3587 End If;
3588 If (p_rec.rt_comp_ref_amt = hr_api.g_number) then
3589 p_rec.rt_comp_ref_amt :=
3590 ben_epo_shd.g_old_rec.rt_comp_ref_amt;
3591 End If;
3592 If (p_rec.rt_cmbn_age_n_los_val = hr_api.g_number) then
3593 p_rec.rt_cmbn_age_n_los_val :=
3594 ben_epo_shd.g_old_rec.rt_cmbn_age_n_los_val;
3595 End If;
3596 If (p_rec.rt_comp_ref_uom = hr_api.g_varchar2) then
3597 p_rec.rt_comp_ref_uom :=
3598 ben_epo_shd.g_old_rec.rt_comp_ref_uom;
3599 End If;
3600 If (p_rec.rt_age_val = hr_api.g_number) then
3601 p_rec.rt_age_val :=
3602 ben_epo_shd.g_old_rec.rt_age_val;
3603 End If;
3604 If (p_rec.rt_los_val = hr_api.g_number) then
3605 p_rec.rt_los_val :=
3606 ben_epo_shd.g_old_rec.rt_los_val;
3607 End If;
3608 If (p_rec.rt_hrs_wkd_val = hr_api.g_number) then
3609 p_rec.rt_hrs_wkd_val :=
3610 ben_epo_shd.g_old_rec.rt_hrs_wkd_val;
3611 End If;
3612 If (p_rec.rt_hrs_wkd_bndry_perd_cd = hr_api.g_varchar2) then
3613 p_rec.rt_hrs_wkd_bndry_perd_cd :=
3614 ben_epo_shd.g_old_rec.rt_hrs_wkd_bndry_perd_cd;
3615 End If;
3616 If (p_rec.rt_age_uom = hr_api.g_varchar2) then
3617 p_rec.rt_age_uom :=
3618 ben_epo_shd.g_old_rec.rt_age_uom;
3619 End If;
3620 If (p_rec.rt_los_uom = hr_api.g_varchar2) then
3621 p_rec.rt_los_uom :=
3622 ben_epo_shd.g_old_rec.rt_los_uom;
3623 End If;
3624 If (p_rec.rt_pct_fl_tm_val = hr_api.g_number) then
3625 p_rec.rt_pct_fl_tm_val :=
3626 ben_epo_shd.g_old_rec.rt_pct_fl_tm_val;
3627 End If;
3628 If (p_rec.rt_frz_los_flag = hr_api.g_varchar2) then
3629 p_rec.rt_frz_los_flag :=
3630 ben_epo_shd.g_old_rec.rt_frz_los_flag;
3631 End If;
3632 If (p_rec.rt_frz_age_flag = hr_api.g_varchar2) then
3633 p_rec.rt_frz_age_flag :=
3634 ben_epo_shd.g_old_rec.rt_frz_age_flag;
3635 End If;
3636 If (p_rec.rt_frz_cmp_lvl_flag = hr_api.g_varchar2) then
3637 p_rec.rt_frz_cmp_lvl_flag :=
3638 ben_epo_shd.g_old_rec.rt_frz_cmp_lvl_flag;
3639 End If;
3640 If (p_rec.rt_frz_pct_fl_tm_flag = hr_api.g_varchar2) then
3641 p_rec.rt_frz_pct_fl_tm_flag :=
3642 ben_epo_shd.g_old_rec.rt_frz_pct_fl_tm_flag;
3643 End If;
3644 If (p_rec.rt_frz_hrs_wkd_flag = hr_api.g_varchar2) then
3645 p_rec.rt_frz_hrs_wkd_flag :=
3646 ben_epo_shd.g_old_rec.rt_frz_hrs_wkd_flag;
3647 End If;
3648 If (p_rec.rt_frz_comb_age_and_los_flag = hr_api.g_varchar2) then
3649 p_rec.rt_frz_comb_age_and_los_flag :=
3650 ben_epo_shd.g_old_rec.rt_frz_comb_age_and_los_flag;
3651 End If;
3652 If (p_rec.comp_ref_amt = hr_api.g_number) then
3653 p_rec.comp_ref_amt :=
3654 ben_epo_shd.g_old_rec.comp_ref_amt;
3655 End If;
3656 If (p_rec.cmbn_age_n_los_val = hr_api.g_number) then
3657 p_rec.cmbn_age_n_los_val :=
3658 ben_epo_shd.g_old_rec.cmbn_age_n_los_val;
3659 End If;
3660 If (p_rec.comp_ref_uom = hr_api.g_varchar2) then
3661 p_rec.comp_ref_uom :=
3662 ben_epo_shd.g_old_rec.comp_ref_uom;
3663 End If;
3664 If (p_rec.age_val = hr_api.g_number) then
3665 p_rec.age_val :=
3666 ben_epo_shd.g_old_rec.age_val;
3667 End If;
3668 If (p_rec.los_val = hr_api.g_number) then
3669 p_rec.los_val :=
3670 ben_epo_shd.g_old_rec.los_val;
3671 End If;
3672 If (p_rec.hrs_wkd_val = hr_api.g_number) then
3673 p_rec.hrs_wkd_val :=
3674 ben_epo_shd.g_old_rec.hrs_wkd_val;
3675 End If;
3676 If (p_rec.hrs_wkd_bndry_perd_cd = hr_api.g_varchar2) then
3677 p_rec.hrs_wkd_bndry_perd_cd :=
3678 ben_epo_shd.g_old_rec.hrs_wkd_bndry_perd_cd;
3679 End If;
3680 If (p_rec.age_uom = hr_api.g_varchar2) then
3681 p_rec.age_uom :=
3682 ben_epo_shd.g_old_rec.age_uom;
3683 End If;
3684 If (p_rec.los_uom = hr_api.g_varchar2) then
3685 p_rec.los_uom :=
3686 ben_epo_shd.g_old_rec.los_uom;
3687 End If;
3688 If (p_rec.frz_los_flag = hr_api.g_varchar2) then
3689 p_rec.frz_los_flag :=
3690 ben_epo_shd.g_old_rec.frz_los_flag;
3691 End If;
3692 If (p_rec.frz_age_flag = hr_api.g_varchar2) then
3693 p_rec.frz_age_flag :=
3694 ben_epo_shd.g_old_rec.frz_age_flag;
3695 End If;
3696 If (p_rec.frz_cmp_lvl_flag = hr_api.g_varchar2) then
3697 p_rec.frz_cmp_lvl_flag :=
3698 ben_epo_shd.g_old_rec.frz_cmp_lvl_flag;
3699 End If;
3700 If (p_rec.frz_pct_fl_tm_flag = hr_api.g_varchar2) then
3701 p_rec.frz_pct_fl_tm_flag :=
3702 ben_epo_shd.g_old_rec.frz_pct_fl_tm_flag;
3703 End If;
3704 If (p_rec.frz_hrs_wkd_flag = hr_api.g_varchar2) then
3705 p_rec.frz_hrs_wkd_flag :=
3706 ben_epo_shd.g_old_rec.frz_hrs_wkd_flag;
3707 End If;
3708 If (p_rec.frz_comb_age_and_los_flag = hr_api.g_varchar2) then
3709 p_rec.frz_comb_age_and_los_flag :=
3710 ben_epo_shd.g_old_rec.frz_comb_age_and_los_flag;
3711 End If;
3712 If (p_rec.ovrid_svc_dt = hr_api.g_date) then
3713 p_rec.ovrid_svc_dt :=
3714 ben_epo_shd.g_old_rec.ovrid_svc_dt;
3715 End If;
3716 If (p_rec.inelg_rsn_cd = hr_api.g_varchar2) then
3717 p_rec.inelg_rsn_cd :=
3718 ben_epo_shd.g_old_rec.inelg_rsn_cd;
3719 End If;
3720 If (p_rec.once_r_cntug_cd = hr_api.g_varchar2) then
3721 p_rec.once_r_cntug_cd :=
3722 ben_epo_shd.g_old_rec.once_r_cntug_cd;
3723 End If;
3724 If (p_rec.oipl_ordr_num = hr_api.g_number) then
3725 p_rec.oipl_ordr_num :=
3726 ben_epo_shd.g_old_rec.oipl_ordr_num;
3727 End If;
3728 If (p_rec.business_group_id = hr_api.g_number) then
3729 p_rec.business_group_id :=
3730 ben_epo_shd.g_old_rec.business_group_id;
3731 End If;
3732 If (p_rec.epo_attribute_category = hr_api.g_varchar2) then
3733 p_rec.epo_attribute_category :=
3734 ben_epo_shd.g_old_rec.epo_attribute_category;
3735 End If;
3736 If (p_rec.epo_attribute1 = hr_api.g_varchar2) then
3737 p_rec.epo_attribute1 :=
3738 ben_epo_shd.g_old_rec.epo_attribute1;
3739 End If;
3740 If (p_rec.epo_attribute2 = hr_api.g_varchar2) then
3741 p_rec.epo_attribute2 :=
3742 ben_epo_shd.g_old_rec.epo_attribute2;
3743 End If;
3744 If (p_rec.epo_attribute3 = hr_api.g_varchar2) then
3745 p_rec.epo_attribute3 :=
3746 ben_epo_shd.g_old_rec.epo_attribute3;
3747 End If;
3748 If (p_rec.epo_attribute4 = hr_api.g_varchar2) then
3749 p_rec.epo_attribute4 :=
3750 ben_epo_shd.g_old_rec.epo_attribute4;
3751 End If;
3752 If (p_rec.epo_attribute5 = hr_api.g_varchar2) then
3753 p_rec.epo_attribute5 :=
3754 ben_epo_shd.g_old_rec.epo_attribute5;
3755 End If;
3756 If (p_rec.epo_attribute6 = hr_api.g_varchar2) then
3757 p_rec.epo_attribute6 :=
3758 ben_epo_shd.g_old_rec.epo_attribute6;
3759 End If;
3760 If (p_rec.epo_attribute7 = hr_api.g_varchar2) then
3761 p_rec.epo_attribute7 :=
3762 ben_epo_shd.g_old_rec.epo_attribute7;
3763 End If;
3764 If (p_rec.epo_attribute8 = hr_api.g_varchar2) then
3765 p_rec.epo_attribute8 :=
3766 ben_epo_shd.g_old_rec.epo_attribute8;
3767 End If;
3768 If (p_rec.epo_attribute9 = hr_api.g_varchar2) then
3769 p_rec.epo_attribute9 :=
3770 ben_epo_shd.g_old_rec.epo_attribute9;
3771 End If;
3772 If (p_rec.epo_attribute10 = hr_api.g_varchar2) then
3773 p_rec.epo_attribute10 :=
3774 ben_epo_shd.g_old_rec.epo_attribute10;
3775 End If;
3776 If (p_rec.epo_attribute11 = hr_api.g_varchar2) then
3777 p_rec.epo_attribute11 :=
3778 ben_epo_shd.g_old_rec.epo_attribute11;
3779 End If;
3780 If (p_rec.epo_attribute12 = hr_api.g_varchar2) then
3781 p_rec.epo_attribute12 :=
3782 ben_epo_shd.g_old_rec.epo_attribute12;
3783 End If;
3784 If (p_rec.epo_attribute13 = hr_api.g_varchar2) then
3785 p_rec.epo_attribute13 :=
3786 ben_epo_shd.g_old_rec.epo_attribute13;
3787 End If;
3788 If (p_rec.epo_attribute14 = hr_api.g_varchar2) then
3789 p_rec.epo_attribute14 :=
3790 ben_epo_shd.g_old_rec.epo_attribute14;
3791 End If;
3792 If (p_rec.epo_attribute15 = hr_api.g_varchar2) then
3793 p_rec.epo_attribute15 :=
3794 ben_epo_shd.g_old_rec.epo_attribute15;
3795 End If;
3796 If (p_rec.epo_attribute16 = hr_api.g_varchar2) then
3797 p_rec.epo_attribute16 :=
3798 ben_epo_shd.g_old_rec.epo_attribute16;
3799 End If;
3800 If (p_rec.epo_attribute17 = hr_api.g_varchar2) then
3801 p_rec.epo_attribute17 :=
3802 ben_epo_shd.g_old_rec.epo_attribute17;
3803 End If;
3804 If (p_rec.epo_attribute18 = hr_api.g_varchar2) then
3805 p_rec.epo_attribute18 :=
3806 ben_epo_shd.g_old_rec.epo_attribute18;
3807 End If;
3808 If (p_rec.epo_attribute19 = hr_api.g_varchar2) then
3809 p_rec.epo_attribute19 :=
3810 ben_epo_shd.g_old_rec.epo_attribute19;
3811 End If;
3812 If (p_rec.epo_attribute20 = hr_api.g_varchar2) then
3813 p_rec.epo_attribute20 :=
3814 ben_epo_shd.g_old_rec.epo_attribute20;
3815 End If;
3816 If (p_rec.epo_attribute21 = hr_api.g_varchar2) then
3817 p_rec.epo_attribute21 :=
3818 ben_epo_shd.g_old_rec.epo_attribute21;
3819 End If;
3820 If (p_rec.epo_attribute22 = hr_api.g_varchar2) then
3821 p_rec.epo_attribute22 :=
3822 ben_epo_shd.g_old_rec.epo_attribute22;
3823 End If;
3824 If (p_rec.epo_attribute23 = hr_api.g_varchar2) then
3825 p_rec.epo_attribute23 :=
3826 ben_epo_shd.g_old_rec.epo_attribute23;
3827 End If;
3828 If (p_rec.epo_attribute24 = hr_api.g_varchar2) then
3829 p_rec.epo_attribute24 :=
3830 ben_epo_shd.g_old_rec.epo_attribute24;
3831 End If;
3832 If (p_rec.epo_attribute25 = hr_api.g_varchar2) then
3833 p_rec.epo_attribute25 :=
3834 ben_epo_shd.g_old_rec.epo_attribute25;
3835 End If;
3836 If (p_rec.epo_attribute26 = hr_api.g_varchar2) then
3837 p_rec.epo_attribute26 :=
3838 ben_epo_shd.g_old_rec.epo_attribute26;
3839 End If;
3840 If (p_rec.epo_attribute27 = hr_api.g_varchar2) then
3841 p_rec.epo_attribute27 :=
3842 ben_epo_shd.g_old_rec.epo_attribute27;
3843 End If;
3844 If (p_rec.epo_attribute28 = hr_api.g_varchar2) then
3845 p_rec.epo_attribute28 :=
3846 ben_epo_shd.g_old_rec.epo_attribute28;
3847 End If;
3848 If (p_rec.epo_attribute29 = hr_api.g_varchar2) then
3849 p_rec.epo_attribute29 :=
3850 ben_epo_shd.g_old_rec.epo_attribute29;
3851 End If;
3852 If (p_rec.epo_attribute30 = hr_api.g_varchar2) then
3853 p_rec.epo_attribute30 :=
3854 ben_epo_shd.g_old_rec.epo_attribute30;
3855 End If;
3856 If (p_rec.request_id= hr_api.g_number) then
3857 p_rec.request_id :=
3858 ben_epo_shd.g_old_rec.request_id;
3859 End If;
3860 If (p_rec.program_application_id = hr_api.g_number) then
3861 p_rec.program_application_id :=
3862 ben_epo_shd.g_old_rec.program_application_id;
3863 End If;
3864 If (p_rec.program_id = hr_api.g_number) then
3865 p_rec.program_id :=
3866 ben_epo_shd.g_old_rec.program_id;
3867 End If;
3868 If (p_rec.program_update_date = hr_api.g_date) then
3869 p_rec.program_update_date :=
3870 ben_epo_shd.g_old_rec.program_update_date;
3871 End If;
3872
3873 --
3874 hr_utility.set_location(' Leaving:'||l_proc, 10);
3875 --
3876 End epo_convert_defs;
3877 -- ----------------------------------------------------------------------------
3878 -- |----------------------------< dt_pre_update >-----------------------------|
3879 -- ----------------------------------------------------------------------------
3880 --
3881 Procedure dt_pre_update
3882 (p_rec in out nocopy ben_epo_shd.g_rec_type
3883 ,p_effective_date in date
3884 ,p_datetrack_mode in varchar2
3885 ,p_validation_start_date in date
3886 ,p_validation_end_date in date
3887 )
3888 is
3889 --
3890 l_proc varchar2(72) := g_package||'dt_pre_update';
3891 l_dummy_version_number number;
3892 --
3893 Begin
3894 hr_utility.set_location('Entering:'||l_proc, 5);
3895 If (p_datetrack_mode <> 'CORRECTION') then
3896 hr_utility.set_location(l_proc, 10);
3897 --
3898 -- Update the current effective end date
3899 --
3900 ben_epo_shd.upd_effective_end_date
3901 (p_effective_date => p_effective_date,
3902 p_base_key_value => p_rec.elig_per_opt_id,
3903 p_new_effective_end_date => (p_validation_start_date - 1),
3904 p_validation_start_date => p_validation_start_date,
3905 p_validation_end_date => p_validation_end_date,
3906 p_object_version_number => l_dummy_version_number);
3907 --
3908 If (p_datetrack_mode = 'UPDATE_OVERRIDE') then
3909 hr_utility.set_location(l_proc, 15);
3910 --
3911 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
3912 -- delete any future rows
3913 --
3914 ben_epo_del.delete_dml
3915 (p_rec => p_rec,
3916 p_effective_date => p_effective_date,
3917 p_datetrack_mode => p_datetrack_mode,
3918 p_validation_start_date => p_validation_start_date,
3919 p_validation_end_date => p_validation_end_date);
3920 End If;
3921 hr_utility.set_location(l_proc, 20);
3922 --
3923 -- We must now insert the updated row
3924 --
3925 ben_epo_ins.insert_dml
3926 (p_rec => p_rec,
3927 p_effective_date => p_effective_date,
3928 p_datetrack_mode => p_datetrack_mode,
3929 p_validation_start_date => p_validation_start_date,
3930 p_validation_end_date => p_validation_end_date);
3931 End If;
3932 hr_utility.set_location(' Leaving:'||l_proc, 20);
3933 End dt_pre_update;
3934 --
3935 -- ----------------------------------------------------------------------------
3936 -- |------------------------------< pre_update >------------------------------|
3937 -- ----------------------------------------------------------------------------
3938 Procedure pre_update
3939 (p_rec in out nocopy ben_epo_shd.g_rec_type
3940 ,p_effective_date in date
3941 ,p_datetrack_mode in varchar2
3942 ,p_validation_start_date in date
3943 ,p_validation_end_date in date
3944 )
3945 is
3946 --
3947 l_proc varchar2(72) := g_package||'pre_update';
3948 --
3949 Begin
3950 hr_utility.set_location('Entering:'||l_proc, 5);
3951 --
3952 --
3953 --
3954 dt_pre_update
3955 (p_rec => p_rec,
3956 p_effective_date => p_effective_date,
3957 p_datetrack_mode => p_datetrack_mode,
3958 p_validation_start_date => p_validation_start_date,
3959 p_validation_end_date => p_validation_end_date);
3960 --
3961 hr_utility.set_location(' Leaving:'||l_proc, 10);
3962 End pre_update;
3963 -- ----------------------------------------------------------------------------
3964 -- |----------------------------< dt_update_dml >-----------------------------|
3965 -- ----------------------------------------------------------------------------
3966 --
3967 Procedure dt_update_dml
3968 (p_rec in out nocopy ben_epo_shd.g_rec_type
3969 ,p_effective_date in date
3970 ,p_datetrack_mode in varchar2
3971 ,p_validation_start_date in date
3972 ,p_validation_end_date in date
3973 )
3974 is
3975 --
3976 l_proc varchar2(72) := g_package||'dt_update_dml';
3977 --
3978 Begin
3979 hr_utility.set_location('Entering:'||l_proc, 5);
3980 --
3981 If (p_datetrack_mode = 'CORRECTION') then
3982 hr_utility.set_location(l_proc, 10);
3983 --
3984 -- Because we are updating a row we must get the next object
3985 -- version number.
3986 --
3987 p_rec.object_version_number :=
3988 dt_api.get_object_version_number
3989 (p_base_table_name => 'ben_elig_per_opt_f',
3990 p_base_key_column => 'elig_per_opt_id',
3991 p_base_key_value => p_rec.elig_per_opt_id);
3992 --
3993 ben_epo_shd.g_api_dml := true; -- Set the api dml status
3994 --
3995 -- Update the ben_elig_per_opt_f Row
3996 --
3997 update ben_elig_per_opt_f
3998 set
3999 elig_per_opt_id = p_rec.elig_per_opt_id,
4000 elig_per_id = p_rec.elig_per_id,
4001 prtn_ovridn_flag = p_rec.prtn_ovridn_flag,
4002 prtn_ovridn_thru_dt = p_rec.prtn_ovridn_thru_dt,
4003 no_mx_prtn_ovrid_thru_flag = p_rec.no_mx_prtn_ovrid_thru_flag,
4004 elig_flag = p_rec.elig_flag,
4005 prtn_strt_dt = p_rec.prtn_strt_dt,
4006 prtn_end_dt = p_rec.prtn_end_dt,
4007 -- wait_perd_cmpltn_dt = p_rec.wait_perd_cmpltn_dt,
4008 wait_perd_cmpltn_date = p_rec.wait_perd_cmpltn_date,
4009 wait_perd_strt_dt = p_rec.wait_perd_strt_dt,
4010 prtn_ovridn_rsn_cd = p_rec.prtn_ovridn_rsn_cd,
4011 pct_fl_tm_val = p_rec.pct_fl_tm_val,
4012 opt_id = p_rec.opt_id,
4013 per_in_ler_id = p_rec.per_in_ler_id,
4014 rt_comp_ref_amt = p_rec.rt_comp_ref_amt,
4015 rt_cmbn_age_n_los_val = p_rec.rt_cmbn_age_n_los_val,
4016 rt_comp_ref_uom = p_rec.rt_comp_ref_uom,
4017 rt_age_val = p_rec.rt_age_val,
4018 rt_los_val = p_rec.rt_los_val,
4019 rt_hrs_wkd_val = p_rec.rt_hrs_wkd_val,
4020 rt_hrs_wkd_bndry_perd_cd = p_rec.rt_hrs_wkd_bndry_perd_cd,
4021 rt_age_uom = p_rec.rt_age_uom,
4022 rt_los_uom = p_rec.rt_los_uom,
4023 rt_pct_fl_tm_val = p_rec.rt_pct_fl_tm_val,
4024 rt_frz_los_flag = p_rec.rt_frz_los_flag,
4025 rt_frz_age_flag = p_rec.rt_frz_age_flag,
4026 rt_frz_cmp_lvl_flag = p_rec.rt_frz_cmp_lvl_flag,
4027 rt_frz_pct_fl_tm_flag = p_rec.rt_frz_pct_fl_tm_flag,
4028 rt_frz_hrs_wkd_flag = p_rec.rt_frz_hrs_wkd_flag,
4029 rt_frz_comb_age_and_los_flag = p_rec.rt_frz_comb_age_and_los_flag,
4030 comp_ref_amt = p_rec.comp_ref_amt,
4031 cmbn_age_n_los_val = p_rec.cmbn_age_n_los_val,
4032 comp_ref_uom = p_rec.comp_ref_uom,
4033 age_val = p_rec.age_val,
4034 los_val = p_rec.los_val,
4035 hrs_wkd_val = p_rec.hrs_wkd_val,
4036 hrs_wkd_bndry_perd_cd = p_rec.hrs_wkd_bndry_perd_cd,
4037 age_uom = p_rec.age_uom,
4038 los_uom = p_rec.los_uom,
4039 frz_los_flag = p_rec.frz_los_flag,
4040 frz_age_flag = p_rec.frz_age_flag,
4041 frz_cmp_lvl_flag = p_rec.frz_cmp_lvl_flag,
4042 frz_pct_fl_tm_flag = p_rec.frz_pct_fl_tm_flag,
4043 frz_hrs_wkd_flag = p_rec.frz_hrs_wkd_flag,
4044 frz_comb_age_and_los_flag = p_rec.frz_comb_age_and_los_flag,
4045 ovrid_svc_dt = p_rec.ovrid_svc_dt,
4046 inelg_rsn_cd = p_rec.inelg_rsn_cd,
4047 once_r_cntug_cd = p_rec.once_r_cntug_cd,
4048 oipl_ordr_num = p_rec.oipl_ordr_num,
4049 business_group_id = p_rec.business_group_id,
4050 epo_attribute_category = p_rec.epo_attribute_category,
4051 epo_attribute1 = p_rec.epo_attribute1,
4052 epo_attribute2 = p_rec.epo_attribute2,
4053 epo_attribute3 = p_rec.epo_attribute3,
4054 epo_attribute4 = p_rec.epo_attribute4,
4055 epo_attribute5 = p_rec.epo_attribute5,
4056 epo_attribute6 = p_rec.epo_attribute6,
4057 epo_attribute7 = p_rec.epo_attribute7,
4058 epo_attribute8 = p_rec.epo_attribute8,
4059 epo_attribute9 = p_rec.epo_attribute9,
4060 epo_attribute10 = p_rec.epo_attribute10,
4061 epo_attribute11 = p_rec.epo_attribute11,
4062 epo_attribute12 = p_rec.epo_attribute12,
4063 epo_attribute13 = p_rec.epo_attribute13,
4064 epo_attribute14 = p_rec.epo_attribute14,
4065 epo_attribute15 = p_rec.epo_attribute15,
4066 epo_attribute16 = p_rec.epo_attribute16,
4067 epo_attribute17 = p_rec.epo_attribute17,
4068 epo_attribute18 = p_rec.epo_attribute18,
4069 epo_attribute19 = p_rec.epo_attribute19,
4070 epo_attribute20 = p_rec.epo_attribute20,
4071 epo_attribute21 = p_rec.epo_attribute21,
4072 epo_attribute22 = p_rec.epo_attribute22,
4073 epo_attribute23 = p_rec.epo_attribute23,
4074 epo_attribute24 = p_rec.epo_attribute24,
4075 epo_attribute25 = p_rec.epo_attribute25,
4076 epo_attribute26 = p_rec.epo_attribute26,
4077 epo_attribute27 = p_rec.epo_attribute27,
4078 epo_attribute28 = p_rec.epo_attribute28,
4079 epo_attribute29 = p_rec.epo_attribute29,
4080 epo_attribute30 = p_rec.epo_attribute30,
4081 request_id = p_rec.request_id,
4082 program_application_id = p_rec.program_application_id,
4083 program_id = p_rec.program_id,
4084 program_update_date = p_rec.program_update_date,
4085 object_version_number = p_rec.object_version_number
4086 where elig_per_opt_id = p_rec.elig_per_opt_id
4087 and effective_start_date = p_validation_start_date
4088 and effective_end_date = p_validation_end_date;
4089 --
4090 ben_epo_shd.g_api_dml := false; -- Unset the api dml status
4091 --
4092 -- Set the effective start and end dates
4093 --
4094 p_rec.effective_start_date := p_validation_start_date;
4095 p_rec.effective_end_date := p_validation_end_date;
4096 End If;
4097 --
4098 hr_utility.set_location(' Leaving:'||l_proc, 15);
4099 Exception
4100 When hr_api.check_integrity_violated Then
4101 -- A check constraint has been violated
4102 ben_epo_shd.g_api_dml := false; -- Unset the api dml status
4103 ben_epo_shd.constraint_error
4104 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
4105 When hr_api.unique_integrity_violated Then
4106 -- Unique integrity has been violated
4107 ben_epo_shd.g_api_dml := false; -- Unset the api dml status
4108 ben_epo_shd.constraint_error
4109 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
4110 When Others Then
4111 ben_epo_shd.g_api_dml := false; -- Unset the api dml status
4112 Raise;
4113 End dt_update_dml;
4114 --
4115 -- ----------------------------------------------------------------------------
4116 -- |------------------------------< update_dml >------------------------------|
4117 -- ----------------------------------------------------------------------------
4118 --
4119 Procedure update_dml
4120 (p_rec in out nocopy ben_epo_shd.g_rec_type
4121 ,p_effective_date in date
4122 ,p_datetrack_mode in varchar2
4123 ,p_validation_start_date in date
4124 ,p_validation_end_date in date
4125 )
4126 is
4127 --
4128 l_proc varchar2(72) := g_package||'update_dml';
4129 --
4130 Begin
4131 hr_utility.set_location('Entering:'||l_proc, 5);
4132 --
4133 dt_update_dml(p_rec => p_rec,
4134 p_effective_date => p_effective_date,
4135 p_datetrack_mode => p_datetrack_mode,
4136 p_validation_start_date => p_validation_start_date,
4137 p_validation_end_date => p_validation_end_date);
4138 --
4139 hr_utility.set_location(' Leaving:'||l_proc, 10);
4140 End update_dml;
4141 --
4142 -- ----------------------------------------------------------------------------
4143 -- |---------------------------------< upd >----------------------------------|
4144 -- ----------------------------------------------------------------------------
4145 Procedure upd
4146 (p_rec in out nocopy ben_epo_shd.g_rec_type
4147 ,p_effective_date in date
4148 ,p_datetrack_mode in varchar2
4149 )
4150 is
4151 --
4152 l_proc varchar2(72) := g_package||'upd';
4153 l_validation_start_date date;
4154 l_validation_end_date date;
4155 --
4156 Begin
4157 hr_utility.set_location('Entering:'||l_proc, 5);
4158 --
4159 -- We must lock the row which we need to update.
4160 --
4161 lck
4162 (p_effective_date => p_effective_date
4163 ,p_datetrack_mode => p_datetrack_mode
4164 ,p_elig_per_opt_id => p_rec.elig_per_opt_id
4165 ,p_object_version_number => p_rec.object_version_number
4166 ,p_validation_start_date => l_validation_start_date
4167 ,p_validation_end_date => l_validation_end_date
4168 );
4169 --
4170 -- 1. During an update system defaults are used to determine if
4171 -- arguments have been defaulted or not. We must therefore
4172 -- derive the full record structure values to be updated.
4173 --
4174 -- 2. Call the supporting update validate operations.
4175 --
4176 epo_convert_defs(p_rec);
4177 --
4178 if ben_manage_life_events.g_modified_mode in ('U','S','D') then
4179 -- compare the values and if there is no difference come out
4180 if (
4181 nvl(p_rec.per_in_ler_id,-1) <> nvl(ben_epo_shd.g_old_rec.per_in_ler_id,-1) or -- bug 5478994 epo to be updated first-time by unrestricted run
4182 nvl(p_rec.opt_id,-1) <> nvl(ben_epo_shd.g_old_rec.opt_id,-1) or
4183 nvl(p_rec.prtn_ovridn_thru_dt,to_date('01-01-1001','dd-mm-yyyy'))<>
4184 nvl(ben_epo_shd.g_old_rec.prtn_ovridn_thru_dt,to_date('01-01-1001','dd-mm-yyyy')) or
4185 nvl(p_rec.elig_flag,'N') <> nvl(ben_epo_shd.g_old_rec.elig_flag,'N') or
4186 nvl(p_rec.comp_ref_amt,-1) <> nvl(ben_epo_shd.g_old_rec.comp_ref_amt,-1) or
4187 nvl(p_rec.cmbn_age_n_los_val, -1) <> nvl(ben_epo_shd.g_old_rec.cmbn_age_n_los_val, -1) or
4188 nvl(p_rec.comp_ref_uom,'X') <> nvl(ben_epo_shd.g_old_rec.comp_ref_uom,'X') or
4189 nvl(p_rec.age_val, -1) <> nvl(ben_epo_shd.g_old_rec.age_val, -1) or
4190 nvl(p_rec.los_val, -1) <> nvl(ben_epo_shd.g_old_rec.los_val, -1) or
4191 nvl(p_rec.prtn_end_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
4192 nvl(ben_epo_shd.g_old_rec.prtn_end_dt,to_date('01-01-1001','dd-mm-yyyy')) or
4193 nvl(p_rec.wait_perd_cmpltn_date,to_date('01-01-1001','dd-mm-yyyy')) <>
4194 nvl(ben_epo_shd.g_old_rec.wait_perd_cmpltn_date,to_date('01-01-1001','dd-mm-yyyy')) or
4195 nvl(p_rec.wait_perd_strt_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
4196 nvl(ben_epo_shd.g_old_rec.wait_perd_strt_dt,to_date('01-01-1001','dd-mm-yyyy')) or
4197 nvl(p_rec.hrs_wkd_val,-1) <> nvl(ben_epo_shd.g_old_rec.hrs_wkd_val,-1) or
4198 nvl(p_rec.hrs_wkd_bndry_perd_cd,'X') <> nvl(ben_epo_shd.g_old_rec.hrs_wkd_bndry_perd_cd,'X') or
4199 nvl(p_rec.prtn_ovridn_flag,'N') <> nvl(ben_epo_shd.g_old_rec.prtn_ovridn_flag,'N') or
4200 nvl(p_rec.no_mx_prtn_ovrid_thru_flag,'N') <>
4201 nvl(ben_epo_shd.g_old_rec.no_mx_prtn_ovrid_thru_flag,'N') or
4202 nvl(p_rec.prtn_ovridn_rsn_cd, 'X') <>
4203 nvl(ben_epo_shd.g_old_rec.prtn_ovridn_rsn_cd, 'X') or
4204 nvl(p_rec.age_uom,'X') <> nvl(ben_epo_shd.g_old_rec.age_uom,'X') or
4205 nvl(p_rec.los_uom,'X') <> nvl(ben_epo_shd.g_old_rec.los_uom,'X') or
4206 nvl(p_rec.ovrid_svc_dt,to_date('01-01-1001','dd-mm-yyyy')) <>
4207 nvl(ben_epo_shd.g_old_rec.ovrid_svc_dt,to_date('01-01-1001','dd-mm-yyyy')) or
4208 nvl(p_rec.inelg_rsn_cd,'X') <> nvl(ben_epo_shd.g_old_rec.inelg_rsn_cd,'X') or
4209 nvl(p_rec.frz_los_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_los_flag,'N') or
4210 nvl(p_rec.frz_age_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_age_flag,'N') or
4211 nvl(p_rec.frz_cmp_lvl_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_cmp_lvl_flag,'N') or
4212 nvl(p_rec.frz_pct_fl_tm_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_pct_fl_tm_flag,'N') or
4213 nvl(p_rec.frz_hrs_wkd_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_hrs_wkd_flag,'N') or
4214 nvl(p_rec.frz_comb_age_and_los_flag,'N') <> nvl(ben_epo_shd.g_old_rec.frz_comb_age_and_los_flag,'N') or
4215 nvl(p_rec.pct_fl_tm_val,-1) <> nvl(ben_epo_shd.g_old_rec.pct_fl_tm_val,-1) or
4216 nvl(p_rec.rt_comp_ref_amt,-1) <> nvl(ben_epo_shd.g_old_rec.rt_comp_ref_amt,-1) or
4217 nvl(p_rec.rt_cmbn_age_n_los_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_cmbn_age_n_los_val,-1) or
4218 nvl(p_rec.rt_comp_ref_uom,'X') <> nvl(ben_epo_shd.g_old_rec.rt_comp_ref_uom,'X') or
4219 nvl(p_rec.rt_age_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_age_val,-1) or
4220 nvl(p_rec.rt_los_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_los_val,-1) or
4221 nvl(p_rec.rt_hrs_wkd_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_hrs_wkd_val,-1) or
4222 nvl(p_rec.rt_hrs_wkd_bndry_perd_cd,'X') <>
4223 nvl(ben_epo_shd.g_old_rec.rt_hrs_wkd_bndry_perd_cd,'X') or
4224 nvl(p_rec.rt_age_uom,'X') <> nvl(ben_epo_shd.g_old_rec.rt_age_uom,'X') or
4225 nvl(p_rec.rt_los_uom,'X') <> nvl(ben_epo_shd.g_old_rec.rt_los_uom,'X') or
4226 nvl(p_rec.rt_pct_fl_tm_val,-1) <> nvl(ben_epo_shd.g_old_rec.rt_pct_fl_tm_val,-1) or
4227 nvl(p_rec.rt_frz_los_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_los_flag,'N') or
4228 nvl(p_rec.rt_frz_age_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_age_flag,'N') or
4229 nvl(p_rec.rt_frz_cmp_lvl_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_cmp_lvl_flag,'N') or
4230 nvl(p_rec.rt_frz_pct_fl_tm_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_pct_fl_tm_flag,'N') or
4231 nvl(p_rec.rt_frz_hrs_wkd_flag,'N') <> nvl(ben_epo_shd.g_old_rec.rt_frz_hrs_wkd_flag,'N') or
4232 nvl(p_rec.rt_frz_comb_age_and_los_flag,'N') <>
4233 nvl(ben_epo_shd.g_old_rec.rt_frz_comb_age_and_los_flag,'N') or
4234 nvl(p_rec.once_r_cntug_cd,'X') <> nvl(ben_epo_shd.g_old_rec.once_r_cntug_cd,'X') or
4235 nvl(p_rec.oipl_ordr_num,-1) <> nvl(ben_epo_shd.g_old_rec.oipl_ordr_num,-1)
4236 ) then
4237 --
4238 null;
4239 else
4240 return;
4241 end if;
4242 --
4243 end if;
4244 -- Call the supporting pre-update operation
4245 --
4246 pre_update
4247 (p_rec => p_rec,
4248 p_effective_date => p_effective_date,
4249 p_datetrack_mode => p_datetrack_mode,
4250 p_validation_start_date => l_validation_start_date,
4251 p_validation_end_date => l_validation_end_date);
4252 --
4253 -- Update the row.
4254 --
4255 update_dml
4256 (p_rec => p_rec,
4257 p_effective_date => p_effective_date,
4258 p_datetrack_mode => p_datetrack_mode,
4259 p_validation_start_date => l_validation_start_date,
4260 p_validation_end_date => l_validation_end_date);
4261 --
4262 End upd;
4263 --
4264 -- ----------------------------------------------------------------------------
4265 -- |---------------------------------< upd >----------------------------------|
4266 -- ----------------------------------------------------------------------------
4267 Procedure upd
4268 (
4269 p_elig_per_opt_id in number,
4270 p_elig_per_id in number default hr_api.g_number,
4271 p_effective_start_date out nocopy date,
4272 p_effective_end_date out nocopy date,
4273 p_prtn_ovridn_flag in varchar2 default hr_api.g_varchar2,
4274 p_prtn_ovridn_thru_dt in date default hr_api.g_date,
4275 p_no_mx_prtn_ovrid_thru_flag in varchar2 default hr_api.g_varchar2,
4276 p_elig_flag in varchar2 default hr_api.g_varchar2,
4277 p_prtn_strt_dt in date default hr_api.g_date,
4278 p_prtn_end_dt in date default hr_api.g_date,
4279 -- p_wait_perd_cmpltn_dt in date default hr_api.g_date,
4280 p_wait_perd_cmpltn_date in date default hr_api.g_date,
4281 p_wait_perd_strt_dt in date default hr_api.g_date,
4282 p_prtn_ovridn_rsn_cd in varchar2 default hr_api.g_varchar2,
4283 p_pct_fl_tm_val in number default hr_api.g_number,
4284 p_opt_id in number default hr_api.g_number,
4285 p_per_in_ler_id in number default hr_api.g_number,
4286 p_rt_comp_ref_amt in number default hr_api.g_number,
4287 p_rt_cmbn_age_n_los_val in number default hr_api.g_number,
4288 p_rt_comp_ref_uom in varchar2 default hr_api.g_varchar2,
4289 p_rt_age_val in number default hr_api.g_number,
4290 p_rt_los_val in number default hr_api.g_number,
4291 p_rt_hrs_wkd_val in number default hr_api.g_number,
4292 p_rt_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2,
4293 p_rt_age_uom in varchar2 default hr_api.g_varchar2,
4294 p_rt_los_uom in varchar2 default hr_api.g_varchar2,
4295 p_rt_pct_fl_tm_val in number default hr_api.g_number,
4296 p_rt_frz_los_flag in varchar2 default hr_api.g_varchar2,
4297 p_rt_frz_age_flag in varchar2 default hr_api.g_varchar2,
4298 p_rt_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2,
4299 p_rt_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2,
4300 p_rt_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2,
4301 p_rt_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2,
4302 p_comp_ref_amt in number default hr_api.g_number,
4303 p_cmbn_age_n_los_val in number default hr_api.g_number,
4304 p_comp_ref_uom in varchar2 default hr_api.g_varchar2,
4305 p_age_val in number default hr_api.g_number,
4306 p_los_val in number default hr_api.g_number,
4307 p_hrs_wkd_val in number default hr_api.g_number,
4308 p_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2,
4309 p_age_uom in varchar2 default hr_api.g_varchar2,
4310 p_los_uom in varchar2 default hr_api.g_varchar2,
4311 p_frz_los_flag in varchar2 default hr_api.g_varchar2,
4312 p_frz_age_flag in varchar2 default hr_api.g_varchar2,
4313 p_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2,
4314 p_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2,
4315 p_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2,
4316 p_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2,
4317 p_ovrid_svc_dt in date default hr_api.g_date,
4318 p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2,
4319 p_once_r_cntug_cd in varchar2 default hr_api.g_varchar2,
4320 p_oipl_ordr_num in number default hr_api.g_number,
4321 p_business_group_id in number default hr_api.g_number,
4322 p_epo_attribute_category in varchar2 default hr_api.g_varchar2,
4323 p_epo_attribute1 in varchar2 default hr_api.g_varchar2,
4324 p_epo_attribute2 in varchar2 default hr_api.g_varchar2,
4325 p_epo_attribute3 in varchar2 default hr_api.g_varchar2,
4326 p_epo_attribute4 in varchar2 default hr_api.g_varchar2,
4327 p_epo_attribute5 in varchar2 default hr_api.g_varchar2,
4328 p_epo_attribute6 in varchar2 default hr_api.g_varchar2,
4329 p_epo_attribute7 in varchar2 default hr_api.g_varchar2,
4330 p_epo_attribute8 in varchar2 default hr_api.g_varchar2,
4331 p_epo_attribute9 in varchar2 default hr_api.g_varchar2,
4332 p_epo_attribute10 in varchar2 default hr_api.g_varchar2,
4333 p_epo_attribute11 in varchar2 default hr_api.g_varchar2,
4334 p_epo_attribute12 in varchar2 default hr_api.g_varchar2,
4335 p_epo_attribute13 in varchar2 default hr_api.g_varchar2,
4336 p_epo_attribute14 in varchar2 default hr_api.g_varchar2,
4337 p_epo_attribute15 in varchar2 default hr_api.g_varchar2,
4338 p_epo_attribute16 in varchar2 default hr_api.g_varchar2,
4339 p_epo_attribute17 in varchar2 default hr_api.g_varchar2,
4340 p_epo_attribute18 in varchar2 default hr_api.g_varchar2,
4341 p_epo_attribute19 in varchar2 default hr_api.g_varchar2,
4342 p_epo_attribute20 in varchar2 default hr_api.g_varchar2,
4343 p_epo_attribute21 in varchar2 default hr_api.g_varchar2,
4344 p_epo_attribute22 in varchar2 default hr_api.g_varchar2,
4345 p_epo_attribute23 in varchar2 default hr_api.g_varchar2,
4346 p_epo_attribute24 in varchar2 default hr_api.g_varchar2,
4347 p_epo_attribute25 in varchar2 default hr_api.g_varchar2,
4348 p_epo_attribute26 in varchar2 default hr_api.g_varchar2,
4349 p_epo_attribute27 in varchar2 default hr_api.g_varchar2,
4350 p_epo_attribute28 in varchar2 default hr_api.g_varchar2,
4351 p_epo_attribute29 in varchar2 default hr_api.g_varchar2,
4352 p_epo_attribute30 in varchar2 default hr_api.g_varchar2,
4353 p_request_id in number default hr_api.g_number,
4354 p_program_application_id in number default hr_api.g_number,
4355 p_program_id in number default hr_api.g_number,
4356 p_program_update_date in date default hr_api.g_date,
4357 p_object_version_number in out nocopy number,
4358 p_effective_date in date,
4359 p_datetrack_mode in varchar2
4360 ) is
4361 --
4362 l_rec ben_epo_shd.g_rec_type;
4363 l_proc varchar2(72) := g_package||'upd';
4364 --
4365 Begin
4366 hr_utility.set_location('Entering:'||l_proc, 5);
4367 --
4368 -- Call conversion function to turn arguments into the
4369 -- l_rec structure.
4370 --
4371 l_rec :=
4372 ben_epo_shd.convert_args
4373 (
4374 p_elig_per_opt_id,
4375 p_elig_per_id,
4376 null,
4377 null,
4378 p_prtn_ovridn_flag,
4379 p_prtn_ovridn_thru_dt,
4380 p_no_mx_prtn_ovrid_thru_flag,
4381 p_elig_flag,
4382 p_prtn_strt_dt,
4383 p_prtn_end_dt,
4384 --p_wait_perd_cmpltn_dt,
4385 p_wait_perd_cmpltn_date,
4386 p_wait_perd_strt_dt,
4387 p_prtn_ovridn_rsn_cd,
4388 p_pct_fl_tm_val,
4389 p_opt_id,
4390 p_per_in_ler_id,
4391 p_rt_comp_ref_amt,
4392 p_rt_cmbn_age_n_los_val,
4393 p_rt_comp_ref_uom,
4394 p_rt_age_val,
4395 p_rt_los_val,
4396 p_rt_hrs_wkd_val,
4397 p_rt_hrs_wkd_bndry_perd_cd,
4398 p_rt_age_uom,
4399 p_rt_los_uom,
4400 p_rt_pct_fl_tm_val,
4401 p_rt_frz_los_flag,
4402 p_rt_frz_age_flag,
4403 p_rt_frz_cmp_lvl_flag,
4404 p_rt_frz_pct_fl_tm_flag,
4405 p_rt_frz_hrs_wkd_flag,
4406 p_rt_frz_comb_age_and_los_flag,
4407 p_comp_ref_amt,
4408 p_cmbn_age_n_los_val,
4409 p_comp_ref_uom,
4410 p_age_val,
4411 p_los_val,
4412 p_hrs_wkd_val,
4413 p_hrs_wkd_bndry_perd_cd,
4414 p_age_uom,
4415 p_los_uom,
4416 p_frz_los_flag,
4417 p_frz_age_flag,
4418 p_frz_cmp_lvl_flag,
4419 p_frz_pct_fl_tm_flag,
4420 p_frz_hrs_wkd_flag,
4421 p_frz_comb_age_and_los_flag,
4422 p_ovrid_svc_dt,
4423 p_inelg_rsn_cd,
4424 p_once_r_cntug_cd,
4425 p_oipl_ordr_num,
4426 p_business_group_id,
4427 p_epo_attribute_category,
4428 p_epo_attribute1,
4429 p_epo_attribute2,
4430 p_epo_attribute3,
4431 p_epo_attribute4,
4432 p_epo_attribute5,
4433 p_epo_attribute6,
4434 p_epo_attribute7,
4435 p_epo_attribute8,
4436 p_epo_attribute9,
4437 p_epo_attribute10,
4438 p_epo_attribute11,
4439 p_epo_attribute12,
4440 p_epo_attribute13,
4441 p_epo_attribute14,
4442 p_epo_attribute15,
4443 p_epo_attribute16,
4444 p_epo_attribute17,
4445 p_epo_attribute18,
4446 p_epo_attribute19,
4447 p_epo_attribute20,
4448 p_epo_attribute21,
4449 p_epo_attribute22,
4450 p_epo_attribute23,
4451 p_epo_attribute24,
4452 p_epo_attribute25,
4453 p_epo_attribute26,
4454 p_epo_attribute27,
4455 p_epo_attribute28,
4456 p_epo_attribute29,
4457 p_epo_attribute30,
4458 p_request_id,
4459 p_program_application_id,
4460 p_program_id,
4461 p_program_update_date,
4462 p_object_version_number
4463 );
4464 --
4465 -- Having converted the arguments into the
4466 -- plsql record structure we call the corresponding record
4467 -- business process.
4468 --
4469 upd(l_rec, p_effective_date, p_datetrack_mode);
4470 p_object_version_number := l_rec.object_version_number;
4471 p_effective_start_date := l_rec.effective_start_date;
4472 p_effective_end_date := l_rec.effective_end_date;
4473 --
4474 --
4475 hr_utility.set_location(' Leaving:'||l_proc, 10);
4476 End upd;
4477 --
4478 procedure update_perf_Elig_Person_Option
4479 (p_validate in boolean default false
4480 ,p_elig_per_opt_id in number
4481 ,p_elig_per_id in number default hr_api.g_number
4482 ,p_effective_start_date out nocopy date
4483 ,p_effective_end_date out nocopy date
4484 ,p_prtn_ovridn_flag in varchar2 default hr_api.g_varchar2
4485 ,p_prtn_ovridn_thru_dt in date default hr_api.g_date
4486 ,p_no_mx_prtn_ovrid_thru_flag in varchar2 default hr_api.g_varchar2
4487 ,p_elig_flag in varchar2 default hr_api.g_varchar2
4488 ,p_prtn_strt_dt in date default hr_api.g_date
4489 ,p_prtn_end_dt in date default hr_api.g_date
4490 --,p_wait_perd_cmpltn_dt in date default hr_api.g_date
4491 ,p_wait_perd_cmpltn_date in date default hr_api.g_date
4492 ,p_wait_perd_strt_dt in date default hr_api.g_date
4493 ,p_prtn_ovridn_rsn_cd in varchar2 default hr_api.g_varchar2
4494 ,p_pct_fl_tm_val in number default hr_api.g_number
4495 ,p_opt_id in number default hr_api.g_number
4496 ,p_per_in_ler_id in number default hr_api.g_number
4497 ,p_rt_comp_ref_amt in number default hr_api.g_number
4498 ,p_rt_cmbn_age_n_los_val in number default hr_api.g_number
4499 ,p_rt_comp_ref_uom in varchar2 default hr_api.g_varchar2
4500 ,p_rt_age_val in number default hr_api.g_number
4501 ,p_rt_los_val in number default hr_api.g_number
4502 ,p_rt_hrs_wkd_val in number default hr_api.g_number
4503 ,p_rt_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2
4504 ,p_rt_age_uom in varchar2 default hr_api.g_varchar2
4505 ,p_rt_los_uom in varchar2 default hr_api.g_varchar2
4506 ,p_rt_pct_fl_tm_val in number default hr_api.g_number
4507 ,p_rt_frz_los_flag in varchar2 default hr_api.g_varchar2
4508 ,p_rt_frz_age_flag in varchar2 default hr_api.g_varchar2
4509 ,p_rt_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2
4510 ,p_rt_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
4511 ,p_rt_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
4512 ,p_rt_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2
4513 ,p_comp_ref_amt in number default hr_api.g_number
4514 ,p_cmbn_age_n_los_val in number default hr_api.g_number
4515 ,p_comp_ref_uom in varchar2 default hr_api.g_varchar2
4516 ,p_age_val in number default hr_api.g_number
4517 ,p_los_val in number default hr_api.g_number
4518 ,p_hrs_wkd_val in number default hr_api.g_number
4519 ,p_hrs_wkd_bndry_perd_cd in varchar2 default hr_api.g_varchar2
4520 ,p_age_uom in varchar2 default hr_api.g_varchar2
4521 ,p_los_uom in varchar2 default hr_api.g_varchar2
4522 ,p_frz_los_flag in varchar2 default hr_api.g_varchar2
4523 ,p_frz_age_flag in varchar2 default hr_api.g_varchar2
4524 ,p_frz_cmp_lvl_flag in varchar2 default hr_api.g_varchar2
4525 ,p_frz_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
4526 ,p_frz_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
4527 ,p_frz_comb_age_and_los_flag in varchar2 default hr_api.g_varchar2
4528 ,p_ovrid_svc_dt in date default hr_api.g_date
4529 ,p_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
4530 ,p_once_r_cntug_cd in varchar2 default hr_api.g_varchar2
4531 ,p_oipl_ordr_num in number default hr_api.g_number
4532 ,p_business_group_id in number default hr_api.g_number
4533 ,p_epo_attribute_category in varchar2 default hr_api.g_varchar2
4534 ,p_epo_attribute1 in varchar2 default hr_api.g_varchar2
4535 ,p_epo_attribute2 in varchar2 default hr_api.g_varchar2
4536 ,p_epo_attribute3 in varchar2 default hr_api.g_varchar2
4537 ,p_epo_attribute4 in varchar2 default hr_api.g_varchar2
4538 ,p_epo_attribute5 in varchar2 default hr_api.g_varchar2
4539 ,p_epo_attribute6 in varchar2 default hr_api.g_varchar2
4540 ,p_epo_attribute7 in varchar2 default hr_api.g_varchar2
4541 ,p_epo_attribute8 in varchar2 default hr_api.g_varchar2
4542 ,p_epo_attribute9 in varchar2 default hr_api.g_varchar2
4543 ,p_epo_attribute10 in varchar2 default hr_api.g_varchar2
4544 ,p_epo_attribute11 in varchar2 default hr_api.g_varchar2
4545 ,p_epo_attribute12 in varchar2 default hr_api.g_varchar2
4546 ,p_epo_attribute13 in varchar2 default hr_api.g_varchar2
4547 ,p_epo_attribute14 in varchar2 default hr_api.g_varchar2
4548 ,p_epo_attribute15 in varchar2 default hr_api.g_varchar2
4549 ,p_epo_attribute16 in varchar2 default hr_api.g_varchar2
4550 ,p_epo_attribute17 in varchar2 default hr_api.g_varchar2
4551 ,p_epo_attribute18 in varchar2 default hr_api.g_varchar2
4552 ,p_epo_attribute19 in varchar2 default hr_api.g_varchar2
4553 ,p_epo_attribute20 in varchar2 default hr_api.g_varchar2
4554 ,p_epo_attribute21 in varchar2 default hr_api.g_varchar2
4555 ,p_epo_attribute22 in varchar2 default hr_api.g_varchar2
4556 ,p_epo_attribute23 in varchar2 default hr_api.g_varchar2
4557 ,p_epo_attribute24 in varchar2 default hr_api.g_varchar2
4558 ,p_epo_attribute25 in varchar2 default hr_api.g_varchar2
4559 ,p_epo_attribute26 in varchar2 default hr_api.g_varchar2
4560 ,p_epo_attribute27 in varchar2 default hr_api.g_varchar2
4561 ,p_epo_attribute28 in varchar2 default hr_api.g_varchar2
4562 ,p_epo_attribute29 in varchar2 default hr_api.g_varchar2
4563 ,p_epo_attribute30 in varchar2 default hr_api.g_varchar2
4564 ,p_request_id in number default hr_api.g_number
4565 ,p_program_application_id in number default hr_api.g_number
4566 ,p_program_id in number default hr_api.g_number
4567 ,p_program_update_date in date default hr_api.g_date
4568 ,p_object_version_number in out nocopy number
4569 ,p_effective_date in date
4570 ,p_datetrack_mode in varchar2
4571 ) is
4572 --
4573 -- Declare cursors and local variables
4574 --
4575 l_proc varchar2(72) := g_package||'update_perf_Elig_Person_Option';
4576 l_object_version_number ben_elig_per_opt_f.object_version_number%TYPE;
4577 l_effective_start_date ben_elig_per_opt_f.effective_start_date%TYPE;
4578 l_effective_end_date ben_elig_per_opt_f.effective_end_date%TYPE;
4579 --
4580 begin
4581 --
4582 hr_utility.set_location('Entering:'|| l_proc, 10);
4583 --
4584 -- Issue a savepoint if operating in validation only mode
4585 --
4586 savepoint update_perf_Elig_Person_Option;
4587 --
4588 l_object_version_number := p_object_version_number;
4589 --
4590 begin
4591 --
4592 -- Start of API User Hook for the before hook of update_Elig_Person_Option
4593 --
4594 ben_Elig_Person_Option_bk2.update_Elig_Person_Option_b
4595 (
4596 p_elig_per_opt_id => p_elig_per_opt_id
4597 ,p_elig_per_id => p_elig_per_id
4598 ,p_prtn_ovridn_flag => p_prtn_ovridn_flag
4599 ,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
4600 ,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
4601 ,p_elig_flag => p_elig_flag
4602 ,p_prtn_strt_dt => p_prtn_strt_dt
4603 ,p_prtn_end_dt => p_prtn_end_dt
4604 --,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
4605 ,p_wait_perd_cmpltn_date => p_wait_perd_cmpltn_date
4606 ,p_wait_perd_strt_dt => p_wait_perd_strt_dt
4607 ,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
4608 ,p_pct_fl_tm_val => p_pct_fl_tm_val
4609 ,p_opt_id => p_opt_id
4610 ,p_per_in_ler_id => p_per_in_ler_id
4611 ,p_rt_comp_ref_amt => p_rt_comp_ref_amt
4612 ,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
4613 ,p_rt_comp_ref_uom => p_rt_comp_ref_uom
4614 ,p_rt_age_val => p_rt_age_val
4615 ,p_rt_los_val => p_rt_los_val
4616 ,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
4617 ,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
4618 ,p_rt_age_uom => p_rt_age_uom
4619 ,p_rt_los_uom => p_rt_los_uom
4620 ,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
4621 ,p_rt_frz_los_flag => p_rt_frz_los_flag
4622 ,p_rt_frz_age_flag => p_rt_frz_age_flag
4623 ,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
4624 ,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
4625 ,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
4626 ,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
4627 ,p_comp_ref_amt => p_comp_ref_amt
4628 ,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
4629 ,p_comp_ref_uom => p_comp_ref_uom
4630 ,p_age_val => p_age_val
4631 ,p_los_val => p_los_val
4632 ,p_hrs_wkd_val => p_hrs_wkd_val
4633 ,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
4634 ,p_age_uom => p_age_uom
4635 ,p_los_uom => p_los_uom
4636 ,p_frz_los_flag => p_frz_los_flag
4637 ,p_frz_age_flag => p_frz_age_flag
4638 ,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
4639 ,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
4640 ,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
4641 ,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
4642 ,p_ovrid_svc_dt => p_ovrid_svc_dt
4643 ,p_inelg_rsn_cd => p_inelg_rsn_cd
4644 ,p_once_r_cntug_cd => p_once_r_cntug_cd
4645 ,p_oipl_ordr_num => p_oipl_ordr_num
4646 ,p_business_group_id => p_business_group_id
4647 ,p_epo_attribute_category => p_epo_attribute_category
4648 ,p_epo_attribute1 => p_epo_attribute1
4649 ,p_epo_attribute2 => p_epo_attribute2
4650 ,p_epo_attribute3 => p_epo_attribute3
4651 ,p_epo_attribute4 => p_epo_attribute4
4652 ,p_epo_attribute5 => p_epo_attribute5
4653 ,p_epo_attribute6 => p_epo_attribute6
4654 ,p_epo_attribute7 => p_epo_attribute7
4655 ,p_epo_attribute8 => p_epo_attribute8
4656 ,p_epo_attribute9 => p_epo_attribute9
4657 ,p_epo_attribute10 => p_epo_attribute10
4658 ,p_epo_attribute11 => p_epo_attribute11
4659 ,p_epo_attribute12 => p_epo_attribute12
4660 ,p_epo_attribute13 => p_epo_attribute13
4661 ,p_epo_attribute14 => p_epo_attribute14
4662 ,p_epo_attribute15 => p_epo_attribute15
4663 ,p_epo_attribute16 => p_epo_attribute16
4664 ,p_epo_attribute17 => p_epo_attribute17
4665 ,p_epo_attribute18 => p_epo_attribute18
4666 ,p_epo_attribute19 => p_epo_attribute19
4667 ,p_epo_attribute20 => p_epo_attribute20
4668 ,p_epo_attribute21 => p_epo_attribute21
4669 ,p_epo_attribute22 => p_epo_attribute22
4670 ,p_epo_attribute23 => p_epo_attribute23
4671 ,p_epo_attribute24 => p_epo_attribute24
4672 ,p_epo_attribute25 => p_epo_attribute25
4673 ,p_epo_attribute26 => p_epo_attribute26
4674 ,p_epo_attribute27 => p_epo_attribute27
4675 ,p_epo_attribute28 => p_epo_attribute28
4676 ,p_epo_attribute29 => p_epo_attribute29
4677 ,p_epo_attribute30 => p_epo_attribute30
4678 ,p_request_id => p_request_id
4679 ,p_program_application_id => p_program_application_id
4680 ,p_program_id => p_program_id
4681 ,p_program_update_date => p_program_update_date
4682 ,p_object_version_number => p_object_version_number
4683 ,p_effective_date => trunc(p_effective_date)
4684 ,p_datetrack_mode => p_datetrack_mode
4685 );
4686 exception
4687 when hr_api.cannot_find_prog_unit then
4688 hr_api.cannot_find_prog_unit_error
4689 (p_module_name => 'UPDATE_Elig_Person_Option'
4690 ,p_hook_type => 'BP'
4691 );
4692 --
4693 -- End of API User Hook for the before hook of update_Elig_Person_Option
4694 --
4695 end;
4696 --
4697 upd
4698 (p_elig_per_opt_id => p_elig_per_opt_id
4699 ,p_elig_per_id => p_elig_per_id
4700 ,p_effective_start_date => l_effective_start_date
4701 ,p_effective_end_date => l_effective_end_date
4702 ,p_prtn_ovridn_flag => p_prtn_ovridn_flag
4703 ,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
4704 ,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
4705 ,p_elig_flag => p_elig_flag
4706 ,p_prtn_strt_dt => p_prtn_strt_dt
4707 ,p_prtn_end_dt => p_prtn_end_dt
4708 --,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
4709 ,p_wait_perd_cmpltn_date => p_wait_perd_cmpltn_date
4710 ,p_wait_perd_strt_dt => p_wait_perd_strt_dt
4711 ,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
4712 ,p_pct_fl_tm_val => p_pct_fl_tm_val
4713 ,p_opt_id => p_opt_id
4714 ,p_per_in_ler_id => p_per_in_ler_id
4715 ,p_rt_comp_ref_amt => p_rt_comp_ref_amt
4716 ,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
4717 ,p_rt_comp_ref_uom => p_rt_comp_ref_uom
4718 ,p_rt_age_val => p_rt_age_val
4719 ,p_rt_los_val => p_rt_los_val
4720 ,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
4721 ,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
4722 ,p_rt_age_uom => p_rt_age_uom
4723 ,p_rt_los_uom => p_rt_los_uom
4724 ,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
4725 ,p_rt_frz_los_flag => p_rt_frz_los_flag
4726 ,p_rt_frz_age_flag => p_rt_frz_age_flag
4727 ,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
4728 ,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
4729 ,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
4730 ,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
4731 ,p_comp_ref_amt => p_comp_ref_amt
4732 ,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
4733 ,p_comp_ref_uom => p_comp_ref_uom
4734 ,p_age_val => p_age_val
4735 ,p_los_val => p_los_val
4736 ,p_hrs_wkd_val => p_hrs_wkd_val
4737 ,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
4738 ,p_age_uom => p_age_uom
4739 ,p_los_uom => p_los_uom
4740 ,p_frz_los_flag => p_frz_los_flag
4741 ,p_frz_age_flag => p_frz_age_flag
4742 ,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
4743 ,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
4744 ,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
4745 ,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
4746 ,p_ovrid_svc_dt => p_ovrid_svc_dt
4747 ,p_inelg_rsn_cd => p_inelg_rsn_cd
4748 ,p_once_r_cntug_cd => p_once_r_cntug_cd
4749 ,p_oipl_ordr_num => p_oipl_ordr_num
4750 ,p_business_group_id => p_business_group_id
4751 ,p_epo_attribute_category => p_epo_attribute_category
4752 ,p_epo_attribute1 => p_epo_attribute1
4753 ,p_epo_attribute2 => p_epo_attribute2
4754 ,p_epo_attribute3 => p_epo_attribute3
4755 ,p_epo_attribute4 => p_epo_attribute4
4756 ,p_epo_attribute5 => p_epo_attribute5
4757 ,p_epo_attribute6 => p_epo_attribute6
4758 ,p_epo_attribute7 => p_epo_attribute7
4759 ,p_epo_attribute8 => p_epo_attribute8
4760 ,p_epo_attribute9 => p_epo_attribute9
4761 ,p_epo_attribute10 => p_epo_attribute10
4762 ,p_epo_attribute11 => p_epo_attribute11
4763 ,p_epo_attribute12 => p_epo_attribute12
4764 ,p_epo_attribute13 => p_epo_attribute13
4765 ,p_epo_attribute14 => p_epo_attribute14
4766 ,p_epo_attribute15 => p_epo_attribute15
4767 ,p_epo_attribute16 => p_epo_attribute16
4768 ,p_epo_attribute17 => p_epo_attribute17
4769 ,p_epo_attribute18 => p_epo_attribute18
4770 ,p_epo_attribute19 => p_epo_attribute19
4771 ,p_epo_attribute20 => p_epo_attribute20
4772 ,p_epo_attribute21 => p_epo_attribute21
4773 ,p_epo_attribute22 => p_epo_attribute22
4774 ,p_epo_attribute23 => p_epo_attribute23
4775 ,p_epo_attribute24 => p_epo_attribute24
4776 ,p_epo_attribute25 => p_epo_attribute25
4777 ,p_epo_attribute26 => p_epo_attribute26
4778 ,p_epo_attribute27 => p_epo_attribute27
4779 ,p_epo_attribute28 => p_epo_attribute28
4780 ,p_epo_attribute29 => p_epo_attribute29
4781 ,p_epo_attribute30 => p_epo_attribute30
4782 ,p_request_id => p_request_id
4783 ,p_program_application_id => p_program_application_id
4784 ,p_program_id => p_program_id
4785 ,p_program_update_date => p_program_update_date
4786 ,p_object_version_number => l_object_version_number
4787 ,p_effective_date => trunc(p_effective_date)
4788 ,p_datetrack_mode => p_datetrack_mode
4789 );
4790 --
4791 begin
4792 --
4793 -- Start of API User Hook for the after hook of update_Elig_Person_Option
4794 --
4795 ben_Elig_Person_Option_bk2.update_Elig_Person_Option_a
4796 (
4797 p_elig_per_opt_id => p_elig_per_opt_id
4798 ,p_elig_per_id => p_elig_per_id
4799 ,p_effective_start_date => l_effective_start_date
4800 ,p_effective_end_date => l_effective_end_date
4801 ,p_prtn_ovridn_flag => p_prtn_ovridn_flag
4802 ,p_prtn_ovridn_thru_dt => p_prtn_ovridn_thru_dt
4803 ,p_no_mx_prtn_ovrid_thru_flag => p_no_mx_prtn_ovrid_thru_flag
4804 ,p_elig_flag => p_elig_flag
4805 ,p_prtn_strt_dt => p_prtn_strt_dt
4806 ,p_prtn_end_dt => p_prtn_end_dt
4807 --,p_wait_perd_cmpltn_dt => p_wait_perd_cmpltn_dt
4808 ,p_wait_perd_cmpltn_date => p_wait_perd_cmpltn_date
4809 ,p_wait_perd_strt_dt => p_wait_perd_Strt_dt
4810 ,p_prtn_ovridn_rsn_cd => p_prtn_ovridn_rsn_cd
4811 ,p_pct_fl_tm_val => p_pct_fl_tm_val
4812 ,p_opt_id => p_opt_id
4813 ,p_per_in_ler_id => p_per_in_ler_id
4814 ,p_rt_comp_ref_amt => p_rt_comp_ref_amt
4815 ,p_rt_cmbn_age_n_los_val => p_rt_cmbn_age_n_los_val
4816 ,p_rt_comp_ref_uom => p_rt_comp_ref_uom
4817 ,p_rt_age_val => p_rt_age_val
4818 ,p_rt_los_val => p_rt_los_val
4819 ,p_rt_hrs_wkd_val => p_rt_hrs_wkd_val
4820 ,p_rt_hrs_wkd_bndry_perd_cd => p_rt_hrs_wkd_bndry_perd_cd
4821 ,p_rt_age_uom => p_rt_age_uom
4822 ,p_rt_los_uom => p_rt_los_uom
4823 ,p_rt_pct_fl_tm_val => p_rt_pct_fl_tm_val
4824 ,p_rt_frz_los_flag => p_rt_frz_los_flag
4825 ,p_rt_frz_age_flag => p_rt_frz_age_flag
4826 ,p_rt_frz_cmp_lvl_flag => p_rt_frz_cmp_lvl_flag
4827 ,p_rt_frz_pct_fl_tm_flag => p_rt_frz_pct_fl_tm_flag
4828 ,p_rt_frz_hrs_wkd_flag => p_rt_frz_hrs_wkd_flag
4829 ,p_rt_frz_comb_age_and_los_flag => p_rt_frz_comb_age_and_los_flag
4830 ,p_comp_ref_amt => p_comp_ref_amt
4831 ,p_cmbn_age_n_los_val => p_cmbn_age_n_los_val
4832 ,p_comp_ref_uom => p_comp_ref_uom
4833 ,p_age_val => p_age_val
4834 ,p_los_val => p_los_val
4835 ,p_hrs_wkd_val => p_hrs_wkd_val
4836 ,p_hrs_wkd_bndry_perd_cd => p_hrs_wkd_bndry_perd_cd
4837 ,p_age_uom => p_age_uom
4838 ,p_los_uom => p_los_uom
4839 ,p_frz_los_flag => p_frz_los_flag
4840 ,p_frz_age_flag => p_frz_age_flag
4841 ,p_frz_cmp_lvl_flag => p_frz_cmp_lvl_flag
4842 ,p_frz_pct_fl_tm_flag => p_frz_pct_fl_tm_flag
4843 ,p_frz_hrs_wkd_flag => p_frz_hrs_wkd_flag
4844 ,p_frz_comb_age_and_los_flag => p_frz_comb_age_and_los_flag
4845 ,p_ovrid_svc_dt => p_ovrid_svc_dt
4846 ,p_inelg_rsn_cd => p_inelg_rsn_cd
4847 ,p_once_r_cntug_cd => p_once_r_cntug_cd
4848 ,p_oipl_ordr_num => p_oipl_ordr_num
4849 ,p_business_group_id => p_business_group_id
4850 ,p_epo_attribute_category => p_epo_attribute_category
4851 ,p_epo_attribute1 => p_epo_attribute1
4852 ,p_epo_attribute2 => p_epo_attribute2
4853 ,p_epo_attribute3 => p_epo_attribute3
4854 ,p_epo_attribute4 => p_epo_attribute4
4855 ,p_epo_attribute5 => p_epo_attribute5
4856 ,p_epo_attribute6 => p_epo_attribute6
4857 ,p_epo_attribute7 => p_epo_attribute7
4858 ,p_epo_attribute8 => p_epo_attribute8
4859 ,p_epo_attribute9 => p_epo_attribute9
4860 ,p_epo_attribute10 => p_epo_attribute10
4861 ,p_epo_attribute11 => p_epo_attribute11
4862 ,p_epo_attribute12 => p_epo_attribute12
4863 ,p_epo_attribute13 => p_epo_attribute13
4864 ,p_epo_attribute14 => p_epo_attribute14
4865 ,p_epo_attribute15 => p_epo_attribute15
4866 ,p_epo_attribute16 => p_epo_attribute16
4867 ,p_epo_attribute17 => p_epo_attribute17
4868 ,p_epo_attribute18 => p_epo_attribute18
4869 ,p_epo_attribute19 => p_epo_attribute19
4870 ,p_epo_attribute20 => p_epo_attribute20
4871 ,p_epo_attribute21 => p_epo_attribute21
4872 ,p_epo_attribute22 => p_epo_attribute22
4873 ,p_epo_attribute23 => p_epo_attribute23
4874 ,p_epo_attribute24 => p_epo_attribute24
4875 ,p_epo_attribute25 => p_epo_attribute25
4876 ,p_epo_attribute26 => p_epo_attribute26
4877 ,p_epo_attribute27 => p_epo_attribute27
4878 ,p_epo_attribute28 => p_epo_attribute28
4879 ,p_epo_attribute29 => p_epo_attribute29
4880 ,p_epo_attribute30 => p_epo_attribute30
4881 ,p_request_id => p_request_id
4882 ,p_program_application_id => p_program_application_id
4883 ,p_program_id => p_program_id
4884 ,p_program_update_date => p_program_update_date
4885 ,p_object_version_number => l_object_version_number
4886 ,p_effective_date => trunc(p_effective_date)
4887 ,p_datetrack_mode => p_datetrack_mode
4888 );
4889 exception
4890 when hr_api.cannot_find_prog_unit then
4891 hr_api.cannot_find_prog_unit_error
4892 (p_module_name => 'UPDATE_Elig_Person_Option'
4893 ,p_hook_type => 'AP'
4894 );
4895 --
4896 -- End of API User Hook for the after hook of update_Elig_Person_Option
4897 --
4898 end;
4899 --
4900 -- When in validation only mode raise the Validate_Enabled exception
4901 --
4902 if p_validate then
4903 raise hr_api.validate_enabled;
4904 end if;
4905 --
4906 -- Set all output arguments
4907 --
4908 p_object_version_number := l_object_version_number;
4909 p_effective_start_date := l_effective_start_date;
4910 p_effective_end_date := l_effective_end_date;
4911 --
4912 hr_utility.set_location(' Leaving:'||l_proc, 70);
4913 --
4914 exception
4915 --
4916 when hr_api.validate_enabled then
4917 --
4918 -- As the Validate_Enabled exception has been raised
4919 -- we must rollback to the savepoint
4920 --
4921 ROLLBACK TO update_perf_Elig_Person_Option;
4922 --
4923 -- Only set output warning arguments
4924 -- (Any key or derived arguments must be set to null
4925 -- when validation only mode is being used.)
4926 --
4927 hr_utility.set_location(' Leaving:'||l_proc, 80);
4928 --
4929 when others then
4930 --
4931 -- A validation or unexpected error has occured
4932 --
4933 ROLLBACK TO update_perf_Elig_Person_Option;
4934 --
4935 p_object_version_number := l_object_version_number;
4936 p_effective_start_date := l_effective_start_date;
4937 p_effective_end_date := l_effective_end_date;
4938 --
4939 raise;
4940 --
4941 end update_perf_Elig_Person_Option;
4942 --
4943 PROCEDURE epecleanup(p_start_rowid IN rowid,
4944 p_end_rowid IN rowid,
4945 p_rows_processed OUT nocopy number) is
4946 --
4947 TYPE l_epo_id_type IS TABLE OF NUMBER(15) index by binary_integer;
4948 t_epo_id l_epo_id_type;
4949 --
4950 l_rows_processed number := 0 ;
4951 cursor csr_get_epo_ids is
4952 select distinct p.elig_per_opt_id
4953 from ben_elig_per_opt_f p
4954 where p.rowid
4955 between p_start_rowid and p_end_rowid
4956 and p.wait_perd_cmpltn_dt||'' is not null ;
4957 begin
4958 -- bulk collect
4959 open csr_get_epo_ids;
4960 LOOP
4961 --
4962 fetch csr_get_epo_ids BULK COLLECT INTO t_epo_id LIMIT 2000;
4963 -- if no rows fetched exit out of proc
4964 if t_epo_id.COUNT = 0 THEN
4965 EXIT;
4966 end if;
4967 --
4968 l_rows_processed := l_rows_processed + t_epo_id.COUNT ;
4969 --
4970 forall i in t_epo_id.FIRST..t_epo_id.LAST
4971 update ben_elig_per_opt_f epo
4972 set wait_perd_cmpltn_date = fnd_date.string_to_date(wait_perd_cmpltn_dt,'DD-MON-RRRR')
4973 where epo.elig_per_opt_id = t_epo_id(i) ;
4974 --
4975 commit;
4976 t_epo_id.delete;
4977 EXIT WHEN csr_get_epo_ids%NOTFOUND;
4978 --
4979 END LOOP;
4980 --
4981 close csr_get_epo_ids;
4982 --
4983 p_rows_processed := l_rows_processed ;
4984 --
4985 end epecleanup ;
4986 --
4987 --
4988 end ben_Eligible_Person_perf_api;