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