[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIGY_PROFILE_API
Source
1 Package Body ben_ELIGY_PROFILE_api as
2 /* $Header: beelpapi.pkb 120.1 2005/06/07 01:03:05 swjain noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_ELIGY_PROFILE_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_ELIGY_PROFILE >--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ELIGY_PROFILE
13 (p_validate in boolean default false
14 ,p_eligy_prfl_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_name in varchar2 default null
18 ,p_description in varchar2 default null
19 ,p_stat_cd in varchar2 default null
20 ,p_asmt_to_use_cd in varchar2 default null
21 ,p_elig_enrld_plip_flag in varchar2 default 'N'
22 ,p_elig_cbr_quald_bnf_flag in varchar2 default 'N'
23 ,p_elig_enrld_ptip_flag in varchar2 default 'N'
24 ,p_elig_dpnt_cvrd_plip_flag in varchar2 default 'N'
25 ,p_elig_dpnt_cvrd_ptip_flag in varchar2 default 'N'
26 ,p_elig_dpnt_cvrd_pgm_flag in varchar2 default 'N'
27 ,p_elig_job_flag in varchar2 default 'N'
28 ,p_elig_hrly_slrd_flag in varchar2 default 'N'
29 ,p_elig_pstl_cd_flag in varchar2 default 'N'
30 ,p_elig_lbr_mmbr_flag in varchar2 default 'N'
31 ,p_elig_lgl_enty_flag in varchar2 default 'N'
32 ,p_elig_benfts_grp_flag in varchar2 default 'N'
33 ,p_elig_wk_loc_flag in varchar2 default 'N'
34 ,p_elig_brgng_unit_flag in varchar2 default 'N'
35 ,p_elig_age_flag in varchar2 default 'N'
36 ,p_elig_los_flag in varchar2 default 'N'
37 ,p_elig_per_typ_flag in varchar2 default 'N'
38 ,p_elig_fl_tm_pt_tm_flag in varchar2 default 'N'
39 ,p_elig_ee_stat_flag in varchar2 default 'N'
40 ,p_elig_grd_flag in varchar2 default 'N'
41 ,p_elig_pct_fl_tm_flag in varchar2 default 'N'
42 ,p_elig_asnt_set_flag in varchar2 default 'N'
43 ,p_elig_hrs_wkd_flag in varchar2 default 'N'
44 ,p_elig_comp_lvl_flag in varchar2 default 'N'
45 ,p_elig_org_unit_flag in varchar2 default 'N'
46 ,p_elig_loa_rsn_flag in varchar2 default 'N'
47 ,p_elig_pyrl_flag in varchar2 default 'N'
48 ,p_elig_schedd_hrs_flag in varchar2 default 'N'
49 ,p_elig_py_bss_flag in varchar2 default 'N'
50 ,p_eligy_prfl_rl_flag in varchar2 default 'N'
51 ,p_elig_cmbn_age_los_flag in varchar2 default 'N'
52 ,p_cntng_prtn_elig_prfl_flag in varchar2 default 'N'
53 ,p_elig_prtt_pl_flag in varchar2 default 'N'
54 ,p_elig_ppl_grp_flag in varchar2 default 'N'
55 ,p_elig_svc_area_flag in varchar2 default 'N'
56 ,p_elig_ptip_prte_flag in varchar2 default 'N'
57 ,p_elig_no_othr_cvg_flag in varchar2 default 'N'
58 ,p_elig_enrld_pl_flag in varchar2 default 'N'
59 ,p_elig_enrld_oipl_flag in varchar2 default 'N'
60 ,p_elig_enrld_pgm_flag in varchar2 default 'N'
61 ,p_elig_dpnt_cvrd_pl_flag in varchar2 default 'N'
62 ,p_elig_lvg_rsn_flag in varchar2 default 'N'
63 ,p_elig_optd_mdcr_flag in varchar2 default 'N'
64 ,p_elig_tbco_use_flag in varchar2 default 'N'
65 ,p_elig_dpnt_othr_ptip_flag in varchar2 default 'N'
66 ,p_business_group_id in number default null
67 ,p_elp_attribute_category in varchar2 default null
68 ,p_elp_attribute1 in varchar2 default null
69 ,p_elp_attribute2 in varchar2 default null
70 ,p_elp_attribute3 in varchar2 default null
71 ,p_elp_attribute4 in varchar2 default null
72 ,p_elp_attribute5 in varchar2 default null
73 ,p_elp_attribute6 in varchar2 default null
74 ,p_elp_attribute7 in varchar2 default null
75 ,p_elp_attribute8 in varchar2 default null
76 ,p_elp_attribute9 in varchar2 default null
77 ,p_elp_attribute10 in varchar2 default null
78 ,p_elp_attribute11 in varchar2 default null
79 ,p_elp_attribute12 in varchar2 default null
80 ,p_elp_attribute13 in varchar2 default null
81 ,p_elp_attribute14 in varchar2 default null
82 ,p_elp_attribute15 in varchar2 default null
83 ,p_elp_attribute16 in varchar2 default null
84 ,p_elp_attribute17 in varchar2 default null
85 ,p_elp_attribute18 in varchar2 default null
86 ,p_elp_attribute19 in varchar2 default null
87 ,p_elp_attribute20 in varchar2 default null
88 ,p_elp_attribute21 in varchar2 default null
89 ,p_elp_attribute22 in varchar2 default null
90 ,p_elp_attribute23 in varchar2 default null
91 ,p_elp_attribute24 in varchar2 default null
92 ,p_elp_attribute25 in varchar2 default null
93 ,p_elp_attribute26 in varchar2 default null
94 ,p_elp_attribute27 in varchar2 default null
95 ,p_elp_attribute28 in varchar2 default null
96 ,p_elp_attribute29 in varchar2 default null
97 ,p_elp_attribute30 in varchar2 default null
98 ,p_elig_mrtl_sts_flag in varchar2 default 'N'
99 ,p_elig_gndr_flag in varchar2 default 'N'
100 ,p_elig_dsblty_ctg_flag in varchar2 default 'N'
101 ,p_elig_dsblty_rsn_flag in varchar2 default 'N'
102 ,p_elig_dsblty_dgr_flag in varchar2 default 'N'
103 ,p_elig_suppl_role_flag in varchar2 default 'N'
104 ,p_elig_qual_titl_flag in varchar2 default 'N'
105 ,p_elig_pstn_flag in varchar2 default 'N'
106 ,p_elig_prbtn_perd_flag in varchar2 default 'N'
107 ,p_elig_sp_clng_prg_pt_flag in varchar2 default 'N'
108 ,p_bnft_cagr_prtn_cd in varchar2 default null
109 ,p_elig_dsbld_flag in varchar2 default 'N'
110 ,p_elig_ttl_cvg_vol_flag in varchar2 default 'N'
111 ,p_elig_ttl_prtt_flag in varchar2 default 'N'
112 ,p_elig_comptncy_flag in varchar2 default 'N'
113 ,p_elig_hlth_cvg_flag in varchar2 default 'N'
114 ,p_elig_anthr_pl_flag in varchar2 default 'N'
115 ,p_elig_qua_in_gr_flag in varchar2 default 'N'
116 ,p_elig_perf_rtng_flag in varchar2 default 'N'
117 ,p_elig_crit_values_flag in varchar2 default 'N' /* RBC */
118 ,p_object_version_number out nocopy number
119 ,p_effective_date in date
120 ) is
121 --
122 -- Declare cursors and local variables
123 --
124 l_eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%TYPE;
125 l_effective_start_date ben_eligy_prfl_f.effective_start_date%TYPE;
126 l_effective_end_date ben_eligy_prfl_f.effective_end_date%TYPE;
127 l_proc varchar2(72) := g_package||'create_ELIGY_PROFILE';
128 l_object_version_number ben_eligy_prfl_f.object_version_number%TYPE;
129 --
130 begin
131 --
132 hr_utility.set_location('Entering:'|| l_proc, 10);
133 --
134 -- Issue a savepoint if operating in validation only mode
135 --
136 savepoint create_ELIGY_PROFILE;
137 --
138 hr_utility.set_location(l_proc, 20);
139 --
140 -- Process Logic
141 --
142 begin
143 --
144 -- Start of API User Hook for the before hook of create_ELIGY_PROFILE
145 --
146 ben_ELIGY_PROFILE_bk1.create_ELIGY_PROFILE_b
147 (p_name => p_name
148 ,p_description => p_description
149 ,p_stat_cd => p_stat_cd
150 ,p_asmt_to_use_cd => p_asmt_to_use_cd
151 ,p_elig_enrld_plip_flag => p_elig_enrld_plip_flag
152 ,p_elig_cbr_quald_bnf_flag => p_elig_cbr_quald_bnf_flag
153 ,p_elig_enrld_ptip_flag => p_elig_enrld_ptip_flag
154 ,p_elig_dpnt_cvrd_plip_flag => p_elig_dpnt_cvrd_plip_flag
155 ,p_elig_dpnt_cvrd_ptip_flag => p_elig_dpnt_cvrd_ptip_flag
156 ,p_elig_dpnt_cvrd_pgm_flag => p_elig_dpnt_cvrd_pgm_flag
157 ,p_elig_job_flag => p_elig_job_flag
158 ,p_elig_hrly_slrd_flag => p_elig_hrly_slrd_flag
159 ,p_elig_pstl_cd_flag => p_elig_pstl_cd_flag
160 ,p_elig_lbr_mmbr_flag => p_elig_lbr_mmbr_flag
161 ,p_elig_lgl_enty_flag => p_elig_lgl_enty_flag
162 ,p_elig_benfts_grp_flag => p_elig_benfts_grp_flag
163 ,p_elig_wk_loc_flag => p_elig_wk_loc_flag
164 ,p_elig_brgng_unit_flag => p_elig_brgng_unit_flag
165 ,p_elig_age_flag => p_elig_age_flag
166 ,p_elig_los_flag => p_elig_los_flag
167 ,p_elig_per_typ_flag => p_elig_per_typ_flag
168 ,p_elig_fl_tm_pt_tm_flag => p_elig_fl_tm_pt_tm_flag
169 ,p_elig_ee_stat_flag => p_elig_ee_stat_flag
170 ,p_elig_grd_flag => p_elig_grd_flag
171 ,p_elig_pct_fl_tm_flag => p_elig_pct_fl_tm_flag
172 ,p_elig_asnt_set_flag => p_elig_asnt_set_flag
173 ,p_elig_hrs_wkd_flag => p_elig_hrs_wkd_flag
174 ,p_elig_comp_lvl_flag => p_elig_comp_lvl_flag
175 ,p_elig_org_unit_flag => p_elig_org_unit_flag
176 ,p_elig_loa_rsn_flag => p_elig_loa_rsn_flag
177 ,p_elig_pyrl_flag => p_elig_pyrl_flag
178 ,p_elig_schedd_hrs_flag => p_elig_schedd_hrs_flag
179 ,p_elig_py_bss_flag => p_elig_py_bss_flag
180 ,p_eligy_prfl_rl_flag => p_eligy_prfl_rl_flag
181 ,p_elig_cmbn_age_los_flag => p_elig_cmbn_age_los_flag
182 ,p_cntng_prtn_elig_prfl_flag => p_cntng_prtn_elig_prfl_flag
183 ,p_elig_prtt_pl_flag => p_elig_prtt_pl_flag
184 ,p_elig_ppl_grp_flag => p_elig_ppl_grp_flag
185 ,p_elig_svc_area_flag => p_elig_svc_area_flag
186 ,p_elig_ptip_prte_flag => p_elig_ptip_prte_flag
187 ,p_elig_no_othr_cvg_flag => p_elig_no_othr_cvg_flag
188 ,p_elig_enrld_pl_flag => p_elig_enrld_pl_flag
189 ,p_elig_enrld_oipl_flag => p_elig_enrld_oipl_flag
190 ,p_elig_enrld_pgm_flag => p_elig_enrld_pgm_flag
191 ,p_elig_dpnt_cvrd_pl_flag => p_elig_dpnt_cvrd_pl_flag
192 ,p_elig_lvg_rsn_flag => p_elig_lvg_rsn_flag
193 ,p_elig_optd_mdcr_flag => p_elig_optd_mdcr_flag
194 ,p_elig_tbco_use_flag => p_elig_tbco_use_flag
195 ,p_elig_dpnt_othr_ptip_flag => p_elig_dpnt_othr_ptip_flag
196 ,p_business_group_id => p_business_group_id
197 ,p_elp_attribute_category => p_elp_attribute_category
198 ,p_elp_attribute1 => p_elp_attribute1
199 ,p_elp_attribute2 => p_elp_attribute2
200 ,p_elp_attribute3 => p_elp_attribute3
201 ,p_elp_attribute4 => p_elp_attribute4
202 ,p_elp_attribute5 => p_elp_attribute5
203 ,p_elp_attribute6 => p_elp_attribute6
204 ,p_elp_attribute7 => p_elp_attribute7
205 ,p_elp_attribute8 => p_elp_attribute8
206 ,p_elp_attribute9 => p_elp_attribute9
207 ,p_elp_attribute10 => p_elp_attribute10
208 ,p_elp_attribute11 => p_elp_attribute11
209 ,p_elp_attribute12 => p_elp_attribute12
210 ,p_elp_attribute13 => p_elp_attribute13
211 ,p_elp_attribute14 => p_elp_attribute14
212 ,p_elp_attribute15 => p_elp_attribute15
213 ,p_elp_attribute16 => p_elp_attribute16
214 ,p_elp_attribute17 => p_elp_attribute17
215 ,p_elp_attribute18 => p_elp_attribute18
216 ,p_elp_attribute19 => p_elp_attribute19
217 ,p_elp_attribute20 => p_elp_attribute20
218 ,p_elp_attribute21 => p_elp_attribute21
219 ,p_elp_attribute22 => p_elp_attribute22
220 ,p_elp_attribute23 => p_elp_attribute23
221 ,p_elp_attribute24 => p_elp_attribute24
222 ,p_elp_attribute25 => p_elp_attribute25
223 ,p_elp_attribute26 => p_elp_attribute26
224 ,p_elp_attribute27 => p_elp_attribute27
225 ,p_elp_attribute28 => p_elp_attribute28
226 ,p_elp_attribute29 => p_elp_attribute29
227 ,p_elp_attribute30 => p_elp_attribute30
228 ,p_elig_mrtl_sts_flag => p_elig_mrtl_sts_flag
229 ,p_elig_gndr_flag => p_elig_gndr_flag
230 ,p_elig_dsblty_ctg_flag => p_elig_dsblty_ctg_flag
231 ,p_elig_dsblty_rsn_flag => p_elig_dsblty_rsn_flag
232 ,p_elig_dsblty_dgr_flag => p_elig_dsblty_dgr_flag
233 ,p_elig_suppl_role_flag => p_elig_suppl_role_flag
234 ,p_elig_qual_titl_flag => p_elig_qual_titl_flag
235 ,p_elig_pstn_flag => p_elig_pstn_flag
236 ,p_elig_prbtn_perd_flag => p_elig_prbtn_perd_flag
237 ,p_elig_sp_clng_prg_pt_flag => p_elig_sp_clng_prg_pt_flag
238 ,p_bnft_cagr_prtn_cd => p_bnft_cagr_prtn_cd
239 ,p_elig_dsbld_flag => p_elig_dsbld_flag
240 ,p_elig_ttl_cvg_vol_flag => p_elig_ttl_cvg_vol_flag
241 ,p_elig_ttl_prtt_flag => p_elig_ttl_prtt_flag
242 ,p_elig_comptncy_flag => p_elig_comptncy_flag
243 ,p_elig_hlth_cvg_flag => p_elig_hlth_cvg_flag
244 ,p_elig_anthr_pl_flag => p_elig_anthr_pl_flag
245 ,p_elig_qua_in_gr_flag => p_elig_qua_in_gr_flag
246 ,p_elig_perf_rtng_flag => p_elig_perf_rtng_flag
247 ,p_elig_crit_values_flag => p_elig_crit_values_flag /* RBC */
248 ,p_effective_date => trunc(p_effective_date));
249 --
250 exception
251 --
252 when hr_api.cannot_find_prog_unit then
253 --
254 hr_api.cannot_find_prog_unit_error
255 (p_module_name => 'CREATE_ELIGY_PROFILE'
256 ,p_hook_type => 'BP');
257 --
258 -- End of API User Hook for the before hook of create_ELIGY_PROFILE
259 --
260 end;
261 --
262 ben_elp_ins.ins
263 (p_eligy_prfl_id => l_eligy_prfl_id
264 ,p_effective_start_date => l_effective_start_date
265 ,p_effective_end_date => l_effective_end_date
266 ,p_name => p_name
267 ,p_description => p_description
268 ,p_stat_cd => p_stat_cd
269 ,p_asmt_to_use_cd => p_asmt_to_use_cd
270 ,p_elig_enrld_plip_flag => p_elig_enrld_plip_flag
271 ,p_elig_cbr_quald_bnf_flag => p_elig_cbr_quald_bnf_flag
272 ,p_elig_enrld_ptip_flag => p_elig_enrld_ptip_flag
273 ,p_elig_dpnt_cvrd_plip_flag => p_elig_dpnt_cvrd_plip_flag
274 ,p_elig_dpnt_cvrd_ptip_flag => p_elig_dpnt_cvrd_ptip_flag
275 ,p_elig_dpnt_cvrd_pgm_flag => p_elig_dpnt_cvrd_pgm_flag
276 ,p_elig_job_flag => p_elig_job_flag
277 ,p_elig_hrly_slrd_flag => p_elig_hrly_slrd_flag
278 ,p_elig_pstl_cd_flag => p_elig_pstl_cd_flag
279 ,p_elig_lbr_mmbr_flag => p_elig_lbr_mmbr_flag
280 ,p_elig_lgl_enty_flag => p_elig_lgl_enty_flag
281 ,p_elig_benfts_grp_flag => p_elig_benfts_grp_flag
282 ,p_elig_wk_loc_flag => p_elig_wk_loc_flag
283 ,p_elig_brgng_unit_flag => p_elig_brgng_unit_flag
284 ,p_elig_age_flag => p_elig_age_flag
285 ,p_elig_los_flag => p_elig_los_flag
286 ,p_elig_per_typ_flag => p_elig_per_typ_flag
287 ,p_elig_fl_tm_pt_tm_flag => p_elig_fl_tm_pt_tm_flag
288 ,p_elig_ee_stat_flag => p_elig_ee_stat_flag
289 ,p_elig_grd_flag => p_elig_grd_flag
290 ,p_elig_pct_fl_tm_flag => p_elig_pct_fl_tm_flag
291 ,p_elig_asnt_set_flag => p_elig_asnt_set_flag
292 ,p_elig_hrs_wkd_flag => p_elig_hrs_wkd_flag
293 ,p_elig_comp_lvl_flag => p_elig_comp_lvl_flag
294 ,p_elig_org_unit_flag => p_elig_org_unit_flag
295 ,p_elig_loa_rsn_flag => p_elig_loa_rsn_flag
296 ,p_elig_pyrl_flag => p_elig_pyrl_flag
297 ,p_elig_schedd_hrs_flag => p_elig_schedd_hrs_flag
298 ,p_elig_py_bss_flag => p_elig_py_bss_flag
299 ,p_eligy_prfl_rl_flag => p_eligy_prfl_rl_flag
300 ,p_elig_cmbn_age_los_flag => p_elig_cmbn_age_los_flag
301 ,p_cntng_prtn_elig_prfl_flag => p_cntng_prtn_elig_prfl_flag
302 ,p_elig_prtt_pl_flag => p_elig_prtt_pl_flag
303 ,p_elig_ppl_grp_flag => p_elig_ppl_grp_flag
304 ,p_elig_svc_area_flag => p_elig_svc_area_flag
305 ,p_elig_ptip_prte_flag => p_elig_ptip_prte_flag
306 ,p_elig_no_othr_cvg_flag => p_elig_no_othr_cvg_flag
307 ,p_elig_enrld_pl_flag => p_elig_enrld_pl_flag
308 ,p_elig_enrld_oipl_flag => p_elig_enrld_oipl_flag
309 ,p_elig_enrld_pgm_flag => p_elig_enrld_pgm_flag
310 ,p_elig_dpnt_cvrd_pl_flag => p_elig_dpnt_cvrd_pl_flag
311 ,p_elig_lvg_rsn_flag => p_elig_lvg_rsn_flag
312 ,p_elig_optd_mdcr_flag => p_elig_optd_mdcr_flag
313 ,p_elig_tbco_use_flag => p_elig_tbco_use_flag
314 ,p_elig_dpnt_othr_ptip_flag => p_elig_dpnt_othr_ptip_flag
315 ,p_business_group_id => p_business_group_id
316 ,p_elp_attribute_category => p_elp_attribute_category
317 ,p_elp_attribute1 => p_elp_attribute1
318 ,p_elp_attribute2 => p_elp_attribute2
319 ,p_elp_attribute3 => p_elp_attribute3
320 ,p_elp_attribute4 => p_elp_attribute4
321 ,p_elp_attribute5 => p_elp_attribute5
322 ,p_elp_attribute6 => p_elp_attribute6
323 ,p_elp_attribute7 => p_elp_attribute7
324 ,p_elp_attribute8 => p_elp_attribute8
325 ,p_elp_attribute9 => p_elp_attribute9
326 ,p_elp_attribute10 => p_elp_attribute10
327 ,p_elp_attribute11 => p_elp_attribute11
328 ,p_elp_attribute12 => p_elp_attribute12
329 ,p_elp_attribute13 => p_elp_attribute13
330 ,p_elp_attribute14 => p_elp_attribute14
331 ,p_elp_attribute15 => p_elp_attribute15
332 ,p_elp_attribute16 => p_elp_attribute16
333 ,p_elp_attribute17 => p_elp_attribute17
334 ,p_elp_attribute18 => p_elp_attribute18
335 ,p_elp_attribute19 => p_elp_attribute19
336 ,p_elp_attribute20 => p_elp_attribute20
337 ,p_elp_attribute21 => p_elp_attribute21
338 ,p_elp_attribute22 => p_elp_attribute22
339 ,p_elp_attribute23 => p_elp_attribute23
340 ,p_elp_attribute24 => p_elp_attribute24
341 ,p_elp_attribute25 => p_elp_attribute25
342 ,p_elp_attribute26 => p_elp_attribute26
343 ,p_elp_attribute27 => p_elp_attribute27
344 ,p_elp_attribute28 => p_elp_attribute28
345 ,p_elp_attribute29 => p_elp_attribute29
346 ,p_elp_attribute30 => p_elp_attribute30
347 ,p_elig_mrtl_sts_flag => p_elig_mrtl_sts_flag
348 ,p_elig_gndr_flag => p_elig_gndr_flag
349 ,p_elig_dsblty_ctg_flag => p_elig_dsblty_ctg_flag
350 ,p_elig_dsblty_rsn_flag => p_elig_dsblty_rsn_flag
351 ,p_elig_dsblty_dgr_flag => p_elig_dsblty_dgr_flag
352 ,p_elig_suppl_role_flag => p_elig_suppl_role_flag
353 ,p_elig_qual_titl_flag => p_elig_qual_titl_flag
354 ,p_elig_pstn_flag => p_elig_pstn_flag
355 ,p_elig_prbtn_perd_flag => p_elig_prbtn_perd_flag
356 ,p_elig_sp_clng_prg_pt_flag => p_elig_sp_clng_prg_pt_flag
357 ,p_bnft_cagr_prtn_cd => p_bnft_cagr_prtn_cd
358 ,p_elig_dsbld_flag => p_elig_dsbld_flag
359 ,p_elig_ttl_cvg_vol_flag => p_elig_ttl_cvg_vol_flag
360 ,p_elig_ttl_prtt_flag => p_elig_ttl_prtt_flag
361 ,p_elig_comptncy_flag => p_elig_comptncy_flag
362 ,p_elig_hlth_cvg_flag => p_elig_hlth_cvg_flag
363 ,p_elig_anthr_pl_flag => p_elig_anthr_pl_flag
364 ,p_elig_qua_in_gr_flag => p_elig_qua_in_gr_flag
365 ,p_elig_perf_rtng_flag => p_elig_perf_rtng_flag
366 ,p_elig_crit_values_flag => p_elig_crit_values_flag /* RBC */
367 ,p_object_version_number => l_object_version_number
368 ,p_effective_date => trunc(p_effective_date));
369 --
370 begin
371 --
372 -- Start of API User Hook for the after hook of create_ELIGY_PROFILE
373 --
374 ben_ELIGY_PROFILE_bk1.create_ELIGY_PROFILE_a
375 (p_eligy_prfl_id => l_eligy_prfl_id
376 ,p_effective_start_date => l_effective_start_date
377 ,p_effective_end_date => l_effective_end_date
378 ,p_name => p_name
379 ,p_description => p_description
380 ,p_stat_cd => p_stat_cd
381 ,p_asmt_to_use_cd => p_asmt_to_use_cd
382 ,p_elig_enrld_plip_flag => p_elig_enrld_plip_flag
383 ,p_elig_cbr_quald_bnf_flag => p_elig_cbr_quald_bnf_flag
384 ,p_elig_enrld_ptip_flag => p_elig_enrld_ptip_flag
385 ,p_elig_dpnt_cvrd_plip_flag => p_elig_dpnt_cvrd_plip_flag
386 ,p_elig_dpnt_cvrd_ptip_flag => p_elig_dpnt_cvrd_ptip_flag
387 ,p_elig_dpnt_cvrd_pgm_flag => p_elig_dpnt_cvrd_pgm_flag
388 ,p_elig_job_flag => p_elig_job_flag
389 ,p_elig_hrly_slrd_flag => p_elig_hrly_slrd_flag
390 ,p_elig_pstl_cd_flag => p_elig_pstl_cd_flag
391 ,p_elig_lbr_mmbr_flag => p_elig_lbr_mmbr_flag
392 ,p_elig_lgl_enty_flag => p_elig_lgl_enty_flag
393 ,p_elig_benfts_grp_flag => p_elig_benfts_grp_flag
394 ,p_elig_wk_loc_flag => p_elig_wk_loc_flag
395 ,p_elig_brgng_unit_flag => p_elig_brgng_unit_flag
396 ,p_elig_age_flag => p_elig_age_flag
397 ,p_elig_los_flag => p_elig_los_flag
398 ,p_elig_per_typ_flag => p_elig_per_typ_flag
399 ,p_elig_fl_tm_pt_tm_flag => p_elig_fl_tm_pt_tm_flag
400 ,p_elig_ee_stat_flag => p_elig_ee_stat_flag
401 ,p_elig_grd_flag => p_elig_grd_flag
402 ,p_elig_pct_fl_tm_flag => p_elig_pct_fl_tm_flag
403 ,p_elig_asnt_set_flag => p_elig_asnt_set_flag
404 ,p_elig_hrs_wkd_flag => p_elig_hrs_wkd_flag
405 ,p_elig_comp_lvl_flag => p_elig_comp_lvl_flag
406 ,p_elig_org_unit_flag => p_elig_org_unit_flag
407 ,p_elig_loa_rsn_flag => p_elig_loa_rsn_flag
408 ,p_elig_pyrl_flag => p_elig_pyrl_flag
409 ,p_elig_schedd_hrs_flag => p_elig_schedd_hrs_flag
410 ,p_elig_py_bss_flag => p_elig_py_bss_flag
411 ,p_eligy_prfl_rl_flag => p_eligy_prfl_rl_flag
412 ,p_elig_cmbn_age_los_flag => p_elig_cmbn_age_los_flag
413 ,p_cntng_prtn_elig_prfl_flag => p_cntng_prtn_elig_prfl_flag
414 ,p_elig_prtt_pl_flag => p_elig_prtt_pl_flag
415 ,p_elig_ppl_grp_flag => p_elig_ppl_grp_flag
416 ,p_elig_svc_area_flag => p_elig_svc_area_flag
417 ,p_elig_ptip_prte_flag => p_elig_ptip_prte_flag
418 ,p_elig_no_othr_cvg_flag => p_elig_no_othr_cvg_flag
419 ,p_elig_enrld_pl_flag => p_elig_enrld_pl_flag
420 ,p_elig_enrld_oipl_flag => p_elig_enrld_oipl_flag
421 ,p_elig_enrld_pgm_flag => p_elig_enrld_pgm_flag
422 ,p_elig_dpnt_cvrd_pl_flag => p_elig_dpnt_cvrd_pl_flag
423 ,p_elig_lvg_rsn_flag => p_elig_lvg_rsn_flag
424 ,p_elig_optd_mdcr_flag => p_elig_optd_mdcr_flag
425 ,p_elig_tbco_use_flag => p_elig_tbco_use_flag
426 ,p_elig_dpnt_othr_ptip_flag => p_elig_dpnt_othr_ptip_flag
427 ,p_business_group_id => p_business_group_id
428 ,p_elp_attribute_category => p_elp_attribute_category
429 ,p_elp_attribute1 => p_elp_attribute1
430 ,p_elp_attribute2 => p_elp_attribute2
431 ,p_elp_attribute3 => p_elp_attribute3
432 ,p_elp_attribute4 => p_elp_attribute4
433 ,p_elp_attribute5 => p_elp_attribute5
434 ,p_elp_attribute6 => p_elp_attribute6
435 ,p_elp_attribute7 => p_elp_attribute7
436 ,p_elp_attribute8 => p_elp_attribute8
437 ,p_elp_attribute9 => p_elp_attribute9
438 ,p_elp_attribute10 => p_elp_attribute10
439 ,p_elp_attribute11 => p_elp_attribute11
440 ,p_elp_attribute12 => p_elp_attribute12
441 ,p_elp_attribute13 => p_elp_attribute13
442 ,p_elp_attribute14 => p_elp_attribute14
443 ,p_elp_attribute15 => p_elp_attribute15
444 ,p_elp_attribute16 => p_elp_attribute16
445 ,p_elp_attribute17 => p_elp_attribute17
446 ,p_elp_attribute18 => p_elp_attribute18
447 ,p_elp_attribute19 => p_elp_attribute19
448 ,p_elp_attribute20 => p_elp_attribute20
449 ,p_elp_attribute21 => p_elp_attribute21
450 ,p_elp_attribute22 => p_elp_attribute22
451 ,p_elp_attribute23 => p_elp_attribute23
452 ,p_elp_attribute24 => p_elp_attribute24
453 ,p_elp_attribute25 => p_elp_attribute25
454 ,p_elp_attribute26 => p_elp_attribute26
455 ,p_elp_attribute27 => p_elp_attribute27
456 ,p_elp_attribute28 => p_elp_attribute28
457 ,p_elp_attribute29 => p_elp_attribute29
458 ,p_elp_attribute30 => p_elp_attribute30
459 ,p_elig_mrtl_sts_flag => p_elig_mrtl_sts_flag
460 ,p_elig_gndr_flag => p_elig_gndr_flag
461 ,p_elig_dsblty_ctg_flag => p_elig_dsblty_ctg_flag
462 ,p_elig_dsblty_rsn_flag => p_elig_dsblty_rsn_flag
463 ,p_elig_dsblty_dgr_flag => p_elig_dsblty_dgr_flag
464 ,p_elig_suppl_role_flag => p_elig_suppl_role_flag
465 ,p_elig_qual_titl_flag => p_elig_qual_titl_flag
466 ,p_elig_pstn_flag => p_elig_pstn_flag
467 ,p_elig_prbtn_perd_flag => p_elig_prbtn_perd_flag
468 ,p_elig_sp_clng_prg_pt_flag => p_elig_sp_clng_prg_pt_flag
469 ,p_bnft_cagr_prtn_cd => p_bnft_cagr_prtn_cd
470 ,p_elig_dsbld_flag => p_elig_dsbld_flag
471 ,p_elig_ttl_cvg_vol_flag => p_elig_ttl_cvg_vol_flag
472 ,p_elig_ttl_prtt_flag => p_elig_ttl_prtt_flag
473 ,p_elig_comptncy_flag => p_elig_comptncy_flag
474 ,p_elig_hlth_cvg_flag => p_elig_hlth_cvg_flag
475 ,p_elig_anthr_pl_flag => p_elig_anthr_pl_flag
476 ,p_elig_qua_in_gr_flag => p_elig_qua_in_gr_flag
477 ,p_elig_perf_rtng_flag => p_elig_perf_rtng_flag
478 ,p_elig_crit_values_flag => p_elig_crit_values_flag /* RBC */
479 ,p_object_version_number => l_object_version_number
480 ,p_effective_date => trunc(p_effective_date));
481 --
482 exception
483 --
484 when hr_api.cannot_find_prog_unit then
485 --
486 hr_api.cannot_find_prog_unit_error
487 (p_module_name => 'CREATE_ELIGY_PROFILE'
488 ,p_hook_type => 'AP');
489 --
490 -- End of API User Hook for the after hook of create_ELIGY_PROFILE
491 --
492 end;
493 --
494 hr_utility.set_location(l_proc, 60);
495 --
496 -- When in validation only mode raise the Validate_Enabled exception
497 --
498 if p_validate then
499 raise hr_api.validate_enabled;
500 end if;
501 --
502 -- Set all output arguments
503 --
504 p_eligy_prfl_id := l_eligy_prfl_id;
505 p_effective_start_date := l_effective_start_date;
506 p_effective_end_date := l_effective_end_date;
507 p_object_version_number := l_object_version_number;
508 --
509 hr_utility.set_location(' Leaving:'||l_proc, 70);
510 --
511 exception
512 --
513 when hr_api.validate_enabled then
514 --
515 -- As the Validate_Enabled exception has been raised
516 -- we must rollback to the savepoint
517 --
518 ROLLBACK TO create_ELIGY_PROFILE;
519 --
520 -- Only set output warning arguments
521 -- (Any key or derived arguments must be set to null
522 -- when validation only mode is being used.)
523 --
524 p_eligy_prfl_id := null;
525 p_effective_start_date := null;
526 p_effective_end_date := null;
527 p_object_version_number := null;
528 hr_utility.set_location(' Leaving:'||l_proc, 80);
529 --
530 when others then
531 --
532 -- NOCOPY
533 p_eligy_prfl_id := null;
534 p_effective_start_date := null;
535 p_effective_end_date := null;
536 p_object_version_number := null;
537 --
538 -- A validation or unexpected error has occured
539 --
540 ROLLBACK TO create_ELIGY_PROFILE;
541 raise;
542 --
543 end create_ELIGY_PROFILE;
544 -- ----------------------------------------------------------------------------
545 -- |------------------------< update_ELIGY_PROFILE >--------------------------|
546 -- ----------------------------------------------------------------------------
547 --
548 procedure update_ELIGY_PROFILE
549 (p_validate in boolean default false
550 ,p_eligy_prfl_id in number
551 ,p_effective_start_date out nocopy date
552 ,p_effective_end_date out nocopy date
553 ,p_name in varchar2 default hr_api.g_varchar2
554 ,p_description in varchar2 default hr_api.g_varchar2
555 ,p_stat_cd in varchar2 default hr_api.g_varchar2
556 ,p_asmt_to_use_cd in varchar2 default hr_api.g_varchar2
557 ,p_elig_enrld_plip_flag in varchar2 default hr_api.g_varchar2
558 ,p_elig_cbr_quald_bnf_flag in varchar2 default hr_api.g_varchar2
559 ,p_elig_enrld_ptip_flag in varchar2 default hr_api.g_varchar2
560 ,p_elig_dpnt_cvrd_plip_flag in varchar2 default hr_api.g_varchar2
561 ,p_elig_dpnt_cvrd_ptip_flag in varchar2 default hr_api.g_varchar2
562 ,p_elig_dpnt_cvrd_pgm_flag in varchar2 default hr_api.g_varchar2
563 ,p_elig_job_flag in varchar2 default hr_api.g_varchar2
564 ,p_elig_hrly_slrd_flag in varchar2 default hr_api.g_varchar2
565 ,p_elig_pstl_cd_flag in varchar2 default hr_api.g_varchar2
566 ,p_elig_lbr_mmbr_flag in varchar2 default hr_api.g_varchar2
567 ,p_elig_lgl_enty_flag in varchar2 default hr_api.g_varchar2
568 ,p_elig_benfts_grp_flag in varchar2 default hr_api.g_varchar2
569 ,p_elig_wk_loc_flag in varchar2 default hr_api.g_varchar2
570 ,p_elig_brgng_unit_flag in varchar2 default hr_api.g_varchar2
571 ,p_elig_age_flag in varchar2 default hr_api.g_varchar2
572 ,p_elig_los_flag in varchar2 default hr_api.g_varchar2
573 ,p_elig_per_typ_flag in varchar2 default hr_api.g_varchar2
574 ,p_elig_fl_tm_pt_tm_flag in varchar2 default hr_api.g_varchar2
575 ,p_elig_ee_stat_flag in varchar2 default hr_api.g_varchar2
576 ,p_elig_grd_flag in varchar2 default hr_api.g_varchar2
577 ,p_elig_pct_fl_tm_flag in varchar2 default hr_api.g_varchar2
578 ,p_elig_asnt_set_flag in varchar2 default hr_api.g_varchar2
579 ,p_elig_hrs_wkd_flag in varchar2 default hr_api.g_varchar2
580 ,p_elig_comp_lvl_flag in varchar2 default hr_api.g_varchar2
581 ,p_elig_org_unit_flag in varchar2 default hr_api.g_varchar2
582 ,p_elig_loa_rsn_flag in varchar2 default hr_api.g_varchar2
583 ,p_elig_pyrl_flag in varchar2 default hr_api.g_varchar2
584 ,p_elig_schedd_hrs_flag in varchar2 default hr_api.g_varchar2
585 ,p_elig_py_bss_flag in varchar2 default hr_api.g_varchar2
586 ,p_eligy_prfl_rl_flag in varchar2 default hr_api.g_varchar2
587 ,p_elig_cmbn_age_los_flag in varchar2 default hr_api.g_varchar2
588 ,p_cntng_prtn_elig_prfl_flag in varchar2 default hr_api.g_varchar2
589 ,p_elig_prtt_pl_flag in varchar2 default hr_api.g_varchar2
590 ,p_elig_ppl_grp_flag in varchar2 default hr_api.g_varchar2
591 ,p_elig_svc_area_flag in varchar2 default hr_api.g_varchar2
592 ,p_elig_ptip_prte_flag in varchar2 default hr_api.g_varchar2
593 ,p_elig_no_othr_cvg_flag in varchar2 default hr_api.g_varchar2
594 ,p_elig_enrld_pl_flag in varchar2 default hr_api.g_varchar2
595 ,p_elig_enrld_oipl_flag in varchar2 default hr_api.g_varchar2
596 ,p_elig_enrld_pgm_flag in varchar2 default hr_api.g_varchar2
597 ,p_elig_dpnt_cvrd_pl_flag in varchar2 default hr_api.g_varchar2
598 ,p_elig_lvg_rsn_flag in varchar2 default hr_api.g_varchar2
599 ,p_elig_optd_mdcr_flag in varchar2 default hr_api.g_varchar2
600 ,p_elig_tbco_use_flag in varchar2 default hr_api.g_varchar2
601 ,p_elig_dpnt_othr_ptip_flag in varchar2 default hr_api.g_varchar2
602 ,p_business_group_id in number default hr_api.g_number
603 ,p_elp_attribute_category in varchar2 default hr_api.g_varchar2
604 ,p_elp_attribute1 in varchar2 default hr_api.g_varchar2
605 ,p_elp_attribute2 in varchar2 default hr_api.g_varchar2
606 ,p_elp_attribute3 in varchar2 default hr_api.g_varchar2
607 ,p_elp_attribute4 in varchar2 default hr_api.g_varchar2
608 ,p_elp_attribute5 in varchar2 default hr_api.g_varchar2
609 ,p_elp_attribute6 in varchar2 default hr_api.g_varchar2
610 ,p_elp_attribute7 in varchar2 default hr_api.g_varchar2
611 ,p_elp_attribute8 in varchar2 default hr_api.g_varchar2
612 ,p_elp_attribute9 in varchar2 default hr_api.g_varchar2
613 ,p_elp_attribute10 in varchar2 default hr_api.g_varchar2
614 ,p_elp_attribute11 in varchar2 default hr_api.g_varchar2
615 ,p_elp_attribute12 in varchar2 default hr_api.g_varchar2
616 ,p_elp_attribute13 in varchar2 default hr_api.g_varchar2
617 ,p_elp_attribute14 in varchar2 default hr_api.g_varchar2
618 ,p_elp_attribute15 in varchar2 default hr_api.g_varchar2
619 ,p_elp_attribute16 in varchar2 default hr_api.g_varchar2
620 ,p_elp_attribute17 in varchar2 default hr_api.g_varchar2
621 ,p_elp_attribute18 in varchar2 default hr_api.g_varchar2
622 ,p_elp_attribute19 in varchar2 default hr_api.g_varchar2
623 ,p_elp_attribute20 in varchar2 default hr_api.g_varchar2
624 ,p_elp_attribute21 in varchar2 default hr_api.g_varchar2
625 ,p_elp_attribute22 in varchar2 default hr_api.g_varchar2
626 ,p_elp_attribute23 in varchar2 default hr_api.g_varchar2
627 ,p_elp_attribute24 in varchar2 default hr_api.g_varchar2
628 ,p_elp_attribute25 in varchar2 default hr_api.g_varchar2
629 ,p_elp_attribute26 in varchar2 default hr_api.g_varchar2
630 ,p_elp_attribute27 in varchar2 default hr_api.g_varchar2
631 ,p_elp_attribute28 in varchar2 default hr_api.g_varchar2
632 ,p_elp_attribute29 in varchar2 default hr_api.g_varchar2
633 ,p_elp_attribute30 in varchar2 default hr_api.g_varchar2
634 ,p_elig_mrtl_sts_flag in varchar2 default hr_api.g_varchar2
635 ,p_elig_gndr_flag in varchar2 default hr_api.g_varchar2
636 ,p_elig_dsblty_ctg_flag in varchar2 default hr_api.g_varchar2
637 ,p_elig_dsblty_rsn_flag in varchar2 default hr_api.g_varchar2
638 ,p_elig_dsblty_dgr_flag in varchar2 default hr_api.g_varchar2
639 ,p_elig_suppl_role_flag in varchar2 default hr_api.g_varchar2
640 ,p_elig_qual_titl_flag in varchar2 default hr_api.g_varchar2
641 ,p_elig_pstn_flag in varchar2 default hr_api.g_varchar2
642 ,p_elig_prbtn_perd_flag in varchar2 default hr_api.g_varchar2
643 ,p_elig_sp_clng_prg_pt_flag in varchar2 default hr_api.g_varchar2
644 ,p_bnft_cagr_prtn_cd in varchar2 default hr_api.g_varchar2
645 ,p_elig_dsbld_flag in varchar2 default hr_api.g_varchar2
646 ,p_elig_ttl_cvg_vol_flag in varchar2 default hr_api.g_varchar2
647 ,p_elig_ttl_prtt_flag in varchar2 default hr_api.g_varchar2
648 ,p_elig_comptncy_flag in varchar2 default hr_api.g_varchar2
649 ,p_elig_hlth_cvg_flag in varchar2 default hr_api.g_varchar2
650 ,p_elig_anthr_pl_flag in varchar2 default hr_api.g_varchar2
651 ,p_elig_qua_in_gr_flag in varchar2 default hr_api.g_varchar2
652 ,p_elig_perf_rtng_flag in varchar2 default hr_api.g_varchar2
653 ,p_elig_crit_values_flag in varchar2 default hr_api.g_varchar2 /* RBC */
654 ,p_object_version_number in out nocopy number
655 ,p_effective_date in date
656 ,p_datetrack_mode in varchar2) is
657 --
658 -- Declare cursors and local variables
659 --
660 l_proc varchar2(72) := g_package||'update_ELIGY_PROFILE';
661 l_object_version_number ben_eligy_prfl_f.object_version_number%TYPE;
662 l_effective_start_date ben_eligy_prfl_f.effective_start_date%TYPE;
663 l_effective_end_date ben_eligy_prfl_f.effective_end_date%TYPE;
664 --
665 begin
666 --
667 hr_utility.set_location('Entering:'|| l_proc, 10);
668 --
669 -- Issue a savepoint if operating in validation only mode
670 --
671 savepoint update_ELIGY_PROFILE;
672 --
673 hr_utility.set_location(l_proc, 20);
674 --
675 -- Process Logic
676 --
677 l_object_version_number := p_object_version_number;
678 --
679 begin
680 --
681 -- Start of API User Hook for the before hook of update_ELIGY_PROFILE
682 --
683 ben_ELIGY_PROFILE_bk2.update_ELIGY_PROFILE_b
684 (p_eligy_prfl_id => p_eligy_prfl_id
685 ,p_name => p_name
686 ,p_description => p_description
687 ,p_stat_cd => p_stat_cd
688 ,p_asmt_to_use_cd => p_asmt_to_use_cd
689 ,p_elig_enrld_plip_flag => p_elig_enrld_plip_flag
690 ,p_elig_cbr_quald_bnf_flag => p_elig_cbr_quald_bnf_flag
691 ,p_elig_enrld_ptip_flag => p_elig_enrld_ptip_flag
692 ,p_elig_dpnt_cvrd_plip_flag => p_elig_dpnt_cvrd_plip_flag
693 ,p_elig_dpnt_cvrd_ptip_flag => p_elig_dpnt_cvrd_ptip_flag
694 ,p_elig_dpnt_cvrd_pgm_flag => p_elig_dpnt_cvrd_pgm_flag
695 ,p_elig_job_flag => p_elig_job_flag
696 ,p_elig_hrly_slrd_flag => p_elig_hrly_slrd_flag
697 ,p_elig_pstl_cd_flag => p_elig_pstl_cd_flag
698 ,p_elig_lbr_mmbr_flag => p_elig_lbr_mmbr_flag
699 ,p_elig_lgl_enty_flag => p_elig_lgl_enty_flag
700 ,p_elig_benfts_grp_flag => p_elig_benfts_grp_flag
701 ,p_elig_wk_loc_flag => p_elig_wk_loc_flag
702 ,p_elig_brgng_unit_flag => p_elig_brgng_unit_flag
703 ,p_elig_age_flag => p_elig_age_flag
704 ,p_elig_los_flag => p_elig_los_flag
705 ,p_elig_per_typ_flag => p_elig_per_typ_flag
706 ,p_elig_fl_tm_pt_tm_flag => p_elig_fl_tm_pt_tm_flag
707 ,p_elig_ee_stat_flag => p_elig_ee_stat_flag
708 ,p_elig_grd_flag => p_elig_grd_flag
709 ,p_elig_pct_fl_tm_flag => p_elig_pct_fl_tm_flag
710 ,p_elig_asnt_set_flag => p_elig_asnt_set_flag
711 ,p_elig_hrs_wkd_flag => p_elig_hrs_wkd_flag
712 ,p_elig_comp_lvl_flag => p_elig_comp_lvl_flag
713 ,p_elig_org_unit_flag => p_elig_org_unit_flag
714 ,p_elig_loa_rsn_flag => p_elig_loa_rsn_flag
715 ,p_elig_pyrl_flag => p_elig_pyrl_flag
716 ,p_elig_schedd_hrs_flag => p_elig_schedd_hrs_flag
717 ,p_elig_py_bss_flag => p_elig_py_bss_flag
718 ,p_eligy_prfl_rl_flag => p_eligy_prfl_rl_flag
719 ,p_elig_cmbn_age_los_flag => p_elig_cmbn_age_los_flag
720 ,p_cntng_prtn_elig_prfl_flag => p_cntng_prtn_elig_prfl_flag
721 ,p_elig_prtt_pl_flag => p_elig_prtt_pl_flag
722 ,p_elig_ppl_grp_flag => p_elig_ppl_grp_flag
723 ,p_elig_svc_area_flag => p_elig_svc_area_flag
724 ,p_elig_ptip_prte_flag => p_elig_ptip_prte_flag
725 ,p_elig_no_othr_cvg_flag => p_elig_no_othr_cvg_flag
726 ,p_elig_enrld_pl_flag => p_elig_enrld_pl_flag
727 ,p_elig_enrld_oipl_flag => p_elig_enrld_oipl_flag
728 ,p_elig_enrld_pgm_flag => p_elig_enrld_pgm_flag
729 ,p_elig_dpnt_cvrd_pl_flag => p_elig_dpnt_cvrd_pl_flag
730 ,p_elig_lvg_rsn_flag => p_elig_lvg_rsn_flag
731 ,p_elig_optd_mdcr_flag => p_elig_optd_mdcr_flag
732 ,p_elig_tbco_use_flag => p_elig_tbco_use_flag
733 ,p_elig_dpnt_othr_ptip_flag => p_elig_dpnt_othr_ptip_flag
734 ,p_business_group_id => p_business_group_id
735 ,p_elp_attribute_category => p_elp_attribute_category
736 ,p_elp_attribute1 => p_elp_attribute1
737 ,p_elp_attribute2 => p_elp_attribute2
738 ,p_elp_attribute3 => p_elp_attribute3
739 ,p_elp_attribute4 => p_elp_attribute4
740 ,p_elp_attribute5 => p_elp_attribute5
741 ,p_elp_attribute6 => p_elp_attribute6
742 ,p_elp_attribute7 => p_elp_attribute7
743 ,p_elp_attribute8 => p_elp_attribute8
744 ,p_elp_attribute9 => p_elp_attribute9
745 ,p_elp_attribute10 => p_elp_attribute10
746 ,p_elp_attribute11 => p_elp_attribute11
747 ,p_elp_attribute12 => p_elp_attribute12
748 ,p_elp_attribute13 => p_elp_attribute13
749 ,p_elp_attribute14 => p_elp_attribute14
750 ,p_elp_attribute15 => p_elp_attribute15
751 ,p_elp_attribute16 => p_elp_attribute16
752 ,p_elp_attribute17 => p_elp_attribute17
753 ,p_elp_attribute18 => p_elp_attribute18
754 ,p_elp_attribute19 => p_elp_attribute19
755 ,p_elp_attribute20 => p_elp_attribute20
756 ,p_elp_attribute21 => p_elp_attribute21
757 ,p_elp_attribute22 => p_elp_attribute22
758 ,p_elp_attribute23 => p_elp_attribute23
759 ,p_elp_attribute24 => p_elp_attribute24
760 ,p_elp_attribute25 => p_elp_attribute25
761 ,p_elp_attribute26 => p_elp_attribute26
762 ,p_elp_attribute27 => p_elp_attribute27
763 ,p_elp_attribute28 => p_elp_attribute28
764 ,p_elp_attribute29 => p_elp_attribute29
765 ,p_elp_attribute30 => p_elp_attribute30
766 ,p_elig_mrtl_sts_flag => p_elig_mrtl_sts_flag
767 ,p_elig_gndr_flag => p_elig_gndr_flag
768 ,p_elig_dsblty_ctg_flag => p_elig_dsblty_ctg_flag
769 ,p_elig_dsblty_rsn_flag => p_elig_dsblty_rsn_flag
770 ,p_elig_dsblty_dgr_flag => p_elig_dsblty_dgr_flag
771 ,p_elig_suppl_role_flag => p_elig_suppl_role_flag
772 ,p_elig_qual_titl_flag => p_elig_qual_titl_flag
773 ,p_elig_pstn_flag => p_elig_pstn_flag
774 ,p_elig_prbtn_perd_flag => p_elig_prbtn_perd_flag
775 ,p_elig_sp_clng_prg_pt_flag => p_elig_sp_clng_prg_pt_flag
776 ,p_bnft_cagr_prtn_cd => p_bnft_cagr_prtn_cd
777 ,p_elig_dsbld_flag => p_elig_dsbld_flag
778 ,p_elig_ttl_cvg_vol_flag => p_elig_ttl_cvg_vol_flag
779 ,p_elig_ttl_prtt_flag => p_elig_ttl_prtt_flag
780 ,p_elig_comptncy_flag => p_elig_comptncy_flag
781 ,p_elig_hlth_cvg_flag => p_elig_hlth_cvg_flag
782 ,p_elig_anthr_pl_flag => p_elig_anthr_pl_flag
783 ,p_elig_qua_in_gr_flag => p_elig_qua_in_gr_flag
784 ,p_elig_perf_rtng_flag => p_elig_perf_rtng_flag
785 ,p_elig_crit_values_flag => p_elig_crit_values_flag /* RBC */
786 ,p_object_version_number => p_object_version_number
787 ,p_effective_date => trunc(p_effective_date)
788 ,p_datetrack_mode => p_datetrack_mode);
789 --
790 exception
791 --
792 when hr_api.cannot_find_prog_unit then
793 --
794 hr_api.cannot_find_prog_unit_error
795 (p_module_name => 'UPDATE_ELIGY_PROFILE'
796 ,p_hook_type => 'BP');
797 --
798 -- End of API User Hook for the before hook of update_ELIGY_PROFILE
799 --
800 end;
801 --
802 ben_elp_upd.upd
803 (p_eligy_prfl_id => p_eligy_prfl_id
804 ,p_effective_start_date => l_effective_start_date
805 ,p_effective_end_date => l_effective_end_date
806 ,p_name => p_name
807 ,p_description => p_description
808 ,p_stat_cd => p_stat_cd
809 ,p_asmt_to_use_cd => p_asmt_to_use_cd
810 ,p_elig_enrld_plip_flag => p_elig_enrld_plip_flag
811 ,p_elig_cbr_quald_bnf_flag => p_elig_cbr_quald_bnf_flag
812 ,p_elig_enrld_ptip_flag => p_elig_enrld_ptip_flag
813 ,p_elig_dpnt_cvrd_plip_flag => p_elig_dpnt_cvrd_plip_flag
814 ,p_elig_dpnt_cvrd_ptip_flag => p_elig_dpnt_cvrd_ptip_flag
815 ,p_elig_dpnt_cvrd_pgm_flag => p_elig_dpnt_cvrd_pgm_flag
816 ,p_elig_job_flag => p_elig_job_flag
817 ,p_elig_hrly_slrd_flag => p_elig_hrly_slrd_flag
818 ,p_elig_pstl_cd_flag => p_elig_pstl_cd_flag
819 ,p_elig_lbr_mmbr_flag => p_elig_lbr_mmbr_flag
820 ,p_elig_lgl_enty_flag => p_elig_lgl_enty_flag
821 ,p_elig_benfts_grp_flag => p_elig_benfts_grp_flag
822 ,p_elig_wk_loc_flag => p_elig_wk_loc_flag
823 ,p_elig_brgng_unit_flag => p_elig_brgng_unit_flag
824 ,p_elig_age_flag => p_elig_age_flag
825 ,p_elig_los_flag => p_elig_los_flag
826 ,p_elig_per_typ_flag => p_elig_per_typ_flag
827 ,p_elig_fl_tm_pt_tm_flag => p_elig_fl_tm_pt_tm_flag
828 ,p_elig_ee_stat_flag => p_elig_ee_stat_flag
829 ,p_elig_grd_flag => p_elig_grd_flag
830 ,p_elig_pct_fl_tm_flag => p_elig_pct_fl_tm_flag
831 ,p_elig_asnt_set_flag => p_elig_asnt_set_flag
832 ,p_elig_hrs_wkd_flag => p_elig_hrs_wkd_flag
833 ,p_elig_comp_lvl_flag => p_elig_comp_lvl_flag
834 ,p_elig_org_unit_flag => p_elig_org_unit_flag
835 ,p_elig_loa_rsn_flag => p_elig_loa_rsn_flag
836 ,p_elig_pyrl_flag => p_elig_pyrl_flag
837 ,p_elig_schedd_hrs_flag => p_elig_schedd_hrs_flag
838 ,p_elig_py_bss_flag => p_elig_py_bss_flag
839 ,p_eligy_prfl_rl_flag => p_eligy_prfl_rl_flag
840 ,p_elig_cmbn_age_los_flag => p_elig_cmbn_age_los_flag
841 ,p_cntng_prtn_elig_prfl_flag => p_cntng_prtn_elig_prfl_flag
842 ,p_elig_prtt_pl_flag => p_elig_prtt_pl_flag
843 ,p_elig_ppl_grp_flag => p_elig_ppl_grp_flag
844 ,p_elig_svc_area_flag => p_elig_svc_area_flag
845 ,p_elig_ptip_prte_flag => p_elig_ptip_prte_flag
846 ,p_elig_no_othr_cvg_flag => p_elig_no_othr_cvg_flag
847 ,p_elig_enrld_pl_flag => p_elig_enrld_pl_flag
848 ,p_elig_enrld_oipl_flag => p_elig_enrld_oipl_flag
849 ,p_elig_enrld_pgm_flag => p_elig_enrld_pgm_flag
850 ,p_elig_dpnt_cvrd_pl_flag => p_elig_dpnt_cvrd_pl_flag
851 ,p_elig_lvg_rsn_flag => p_elig_lvg_rsn_flag
852 ,p_elig_optd_mdcr_flag => p_elig_optd_mdcr_flag
853 ,p_elig_tbco_use_flag => p_elig_tbco_use_flag
854 ,p_elig_dpnt_othr_ptip_flag => p_elig_dpnt_othr_ptip_flag
855 ,p_business_group_id => p_business_group_id
856 ,p_elp_attribute_category => p_elp_attribute_category
857 ,p_elp_attribute1 => p_elp_attribute1
858 ,p_elp_attribute2 => p_elp_attribute2
859 ,p_elp_attribute3 => p_elp_attribute3
860 ,p_elp_attribute4 => p_elp_attribute4
861 ,p_elp_attribute5 => p_elp_attribute5
862 ,p_elp_attribute6 => p_elp_attribute6
863 ,p_elp_attribute7 => p_elp_attribute7
864 ,p_elp_attribute8 => p_elp_attribute8
865 ,p_elp_attribute9 => p_elp_attribute9
866 ,p_elp_attribute10 => p_elp_attribute10
867 ,p_elp_attribute11 => p_elp_attribute11
868 ,p_elp_attribute12 => p_elp_attribute12
869 ,p_elp_attribute13 => p_elp_attribute13
870 ,p_elp_attribute14 => p_elp_attribute14
871 ,p_elp_attribute15 => p_elp_attribute15
872 ,p_elp_attribute16 => p_elp_attribute16
873 ,p_elp_attribute17 => p_elp_attribute17
874 ,p_elp_attribute18 => p_elp_attribute18
875 ,p_elp_attribute19 => p_elp_attribute19
876 ,p_elp_attribute20 => p_elp_attribute20
877 ,p_elp_attribute21 => p_elp_attribute21
878 ,p_elp_attribute22 => p_elp_attribute22
879 ,p_elp_attribute23 => p_elp_attribute23
880 ,p_elp_attribute24 => p_elp_attribute24
881 ,p_elp_attribute25 => p_elp_attribute25
882 ,p_elp_attribute26 => p_elp_attribute26
883 ,p_elp_attribute27 => p_elp_attribute27
884 ,p_elp_attribute28 => p_elp_attribute28
885 ,p_elp_attribute29 => p_elp_attribute29
886 ,p_elp_attribute30 => p_elp_attribute30
887 ,p_elig_mrtl_sts_flag => p_elig_mrtl_sts_flag
888 ,p_elig_gndr_flag => p_elig_gndr_flag
889 ,p_elig_dsblty_ctg_flag => p_elig_dsblty_ctg_flag
890 ,p_elig_dsblty_rsn_flag => p_elig_dsblty_rsn_flag
891 ,p_elig_dsblty_dgr_flag => p_elig_dsblty_dgr_flag
892 ,p_elig_suppl_role_flag => p_elig_suppl_role_flag
893 ,p_elig_qual_titl_flag => p_elig_qual_titl_flag
894 ,p_elig_pstn_flag => p_elig_pstn_flag
895 ,p_elig_prbtn_perd_flag => p_elig_prbtn_perd_flag
896 ,p_elig_sp_clng_prg_pt_flag => p_elig_sp_clng_prg_pt_flag
897 ,p_bnft_cagr_prtn_cd => p_bnft_cagr_prtn_cd
898 ,p_elig_dsbld_flag => p_elig_dsbld_flag
899 ,p_elig_ttl_cvg_vol_flag => p_elig_ttl_cvg_vol_flag
900 ,p_elig_ttl_prtt_flag => p_elig_ttl_prtt_flag
901 ,p_elig_comptncy_flag => p_elig_comptncy_flag
902 ,p_elig_hlth_cvg_flag => p_elig_hlth_cvg_flag
903 ,p_elig_anthr_pl_flag => p_elig_anthr_pl_flag
904 ,p_elig_qua_in_gr_flag => p_elig_qua_in_gr_flag
905 ,p_elig_perf_rtng_flag => p_elig_perf_rtng_flag
906 ,p_elig_crit_values_flag => p_elig_crit_values_flag /* RBC */
907 ,p_object_version_number => l_object_version_number
908 ,p_effective_date => trunc(p_effective_date)
909 ,p_datetrack_mode => p_datetrack_mode);
910 --
911 begin
912 --
913 -- Start of API User Hook for the after hook of update_ELIGY_PROFILE
914 --
915 ben_ELIGY_PROFILE_bk2.update_ELIGY_PROFILE_a
916 (p_eligy_prfl_id => p_eligy_prfl_id
917 ,p_effective_start_date => l_effective_start_date
918 ,p_effective_end_date => l_effective_end_date
919 ,p_name => p_name
920 ,p_description => p_description
921 ,p_stat_cd => p_stat_cd
922 ,p_asmt_to_use_cd => p_asmt_to_use_cd
923 ,p_elig_enrld_plip_flag => p_elig_enrld_plip_flag
924 ,p_elig_cbr_quald_bnf_flag => p_elig_cbr_quald_bnf_flag
925 ,p_elig_enrld_ptip_flag => p_elig_enrld_ptip_flag
926 ,p_elig_dpnt_cvrd_plip_flag => p_elig_dpnt_cvrd_plip_flag
927 ,p_elig_dpnt_cvrd_ptip_flag => p_elig_dpnt_cvrd_ptip_flag
928 ,p_elig_dpnt_cvrd_pgm_flag => p_elig_dpnt_cvrd_pgm_flag
929 ,p_elig_job_flag => p_elig_job_flag
930 ,p_elig_hrly_slrd_flag => p_elig_hrly_slrd_flag
931 ,p_elig_pstl_cd_flag => p_elig_pstl_cd_flag
932 ,p_elig_lbr_mmbr_flag => p_elig_lbr_mmbr_flag
933 ,p_elig_lgl_enty_flag => p_elig_lgl_enty_flag
934 ,p_elig_benfts_grp_flag => p_elig_benfts_grp_flag
935 ,p_elig_wk_loc_flag => p_elig_wk_loc_flag
936 ,p_elig_brgng_unit_flag => p_elig_brgng_unit_flag
937 ,p_elig_age_flag => p_elig_age_flag
938 ,p_elig_los_flag => p_elig_los_flag
939 ,p_elig_per_typ_flag => p_elig_per_typ_flag
940 ,p_elig_fl_tm_pt_tm_flag => p_elig_fl_tm_pt_tm_flag
941 ,p_elig_ee_stat_flag => p_elig_ee_stat_flag
942 ,p_elig_grd_flag => p_elig_grd_flag
943 ,p_elig_pct_fl_tm_flag => p_elig_pct_fl_tm_flag
944 ,p_elig_asnt_set_flag => p_elig_asnt_set_flag
945 ,p_elig_hrs_wkd_flag => p_elig_hrs_wkd_flag
946 ,p_elig_comp_lvl_flag => p_elig_comp_lvl_flag
947 ,p_elig_org_unit_flag => p_elig_org_unit_flag
948 ,p_elig_loa_rsn_flag => p_elig_loa_rsn_flag
949 ,p_elig_pyrl_flag => p_elig_pyrl_flag
950 ,p_elig_schedd_hrs_flag => p_elig_schedd_hrs_flag
951 ,p_elig_py_bss_flag => p_elig_py_bss_flag
952 ,p_eligy_prfl_rl_flag => p_eligy_prfl_rl_flag
953 ,p_elig_cmbn_age_los_flag => p_elig_cmbn_age_los_flag
954 ,p_cntng_prtn_elig_prfl_flag => p_cntng_prtn_elig_prfl_flag
955 ,p_elig_prtt_pl_flag => p_elig_prtt_pl_flag
956 ,p_elig_ppl_grp_flag => p_elig_ppl_grp_flag
957 ,p_elig_svc_area_flag => p_elig_svc_area_flag
958 ,p_elig_ptip_prte_flag => p_elig_ptip_prte_flag
959 ,p_elig_no_othr_cvg_flag => p_elig_no_othr_cvg_flag
960 ,p_elig_enrld_pl_flag => p_elig_enrld_pl_flag
961 ,p_elig_enrld_oipl_flag => p_elig_enrld_oipl_flag
962 ,p_elig_enrld_pgm_flag => p_elig_enrld_pgm_flag
963 ,p_elig_dpnt_cvrd_pl_flag => p_elig_dpnt_cvrd_pl_flag
964 ,p_elig_lvg_rsn_flag => p_elig_lvg_rsn_flag
965 ,p_elig_optd_mdcr_flag => p_elig_optd_mdcr_flag
966 ,p_elig_tbco_use_flag => p_elig_tbco_use_flag
967 ,p_elig_dpnt_othr_ptip_flag => p_elig_dpnt_othr_ptip_flag
968 ,p_business_group_id => p_business_group_id
969 ,p_elp_attribute_category => p_elp_attribute_category
970 ,p_elp_attribute1 => p_elp_attribute1
971 ,p_elp_attribute2 => p_elp_attribute2
972 ,p_elp_attribute3 => p_elp_attribute3
973 ,p_elp_attribute4 => p_elp_attribute4
974 ,p_elp_attribute5 => p_elp_attribute5
975 ,p_elp_attribute6 => p_elp_attribute6
976 ,p_elp_attribute7 => p_elp_attribute7
977 ,p_elp_attribute8 => p_elp_attribute8
978 ,p_elp_attribute9 => p_elp_attribute9
979 ,p_elp_attribute10 => p_elp_attribute10
980 ,p_elp_attribute11 => p_elp_attribute11
981 ,p_elp_attribute12 => p_elp_attribute12
982 ,p_elp_attribute13 => p_elp_attribute13
983 ,p_elp_attribute14 => p_elp_attribute14
984 ,p_elp_attribute15 => p_elp_attribute15
985 ,p_elp_attribute16 => p_elp_attribute16
986 ,p_elp_attribute17 => p_elp_attribute17
987 ,p_elp_attribute18 => p_elp_attribute18
988 ,p_elp_attribute19 => p_elp_attribute19
989 ,p_elp_attribute20 => p_elp_attribute20
990 ,p_elp_attribute21 => p_elp_attribute21
991 ,p_elp_attribute22 => p_elp_attribute22
992 ,p_elp_attribute23 => p_elp_attribute23
993 ,p_elp_attribute24 => p_elp_attribute24
994 ,p_elp_attribute25 => p_elp_attribute25
995 ,p_elp_attribute26 => p_elp_attribute26
996 ,p_elp_attribute27 => p_elp_attribute27
997 ,p_elp_attribute28 => p_elp_attribute28
998 ,p_elp_attribute29 => p_elp_attribute29
999 ,p_elp_attribute30 => p_elp_attribute30
1000 ,p_elig_mrtl_sts_flag => p_elig_mrtl_sts_flag
1001 ,p_elig_gndr_flag => p_elig_gndr_flag
1002 ,p_elig_dsblty_ctg_flag => p_elig_dsblty_ctg_flag
1003 ,p_elig_dsblty_rsn_flag => p_elig_dsblty_rsn_flag
1004 ,p_elig_dsblty_dgr_flag => p_elig_dsblty_dgr_flag
1005 ,p_elig_suppl_role_flag => p_elig_suppl_role_flag
1006 ,p_elig_qual_titl_flag => p_elig_qual_titl_flag
1007 ,p_elig_pstn_flag => p_elig_pstn_flag
1008 ,p_elig_prbtn_perd_flag => p_elig_prbtn_perd_flag
1009 ,p_elig_sp_clng_prg_pt_flag => p_elig_sp_clng_prg_pt_flag
1010 ,p_bnft_cagr_prtn_cd => p_bnft_cagr_prtn_cd
1011 ,p_elig_dsbld_flag => p_elig_dsbld_flag
1012 ,p_elig_ttl_cvg_vol_flag => p_elig_ttl_cvg_vol_flag
1013 ,p_elig_ttl_prtt_flag => p_elig_ttl_prtt_flag
1014 ,p_elig_comptncy_flag => p_elig_comptncy_flag
1015 ,p_elig_hlth_cvg_flag => p_elig_hlth_cvg_flag
1016 ,p_elig_anthr_pl_flag => p_elig_anthr_pl_flag
1017 ,p_elig_qua_in_gr_flag => p_elig_qua_in_gr_flag
1018 ,p_elig_perf_rtng_flag => p_elig_perf_rtng_flag
1019 ,p_elig_crit_values_flag => p_elig_crit_values_flag /* RBC */
1020 ,p_object_version_number => l_object_version_number
1021 ,p_effective_date => trunc(p_effective_date)
1022 ,p_datetrack_mode => p_datetrack_mode);
1023 --
1024 exception
1025 --
1026 when hr_api.cannot_find_prog_unit then
1027 --
1028 hr_api.cannot_find_prog_unit_error
1029 (p_module_name => 'UPDATE_ELIGY_PROFILE'
1030 ,p_hook_type => 'AP');
1031 --
1032 -- End of API User Hook for the after hook of update_ELIGY_PROFILE
1033 --
1034 end;
1035 --
1036 hr_utility.set_location(l_proc, 60);
1037 --
1038 -- When in validation only mode raise the Validate_Enabled exception
1039 --
1040 if p_validate then
1041 raise hr_api.validate_enabled;
1042 end if;
1043 --
1044 -- Set all output arguments
1045 --
1046 p_object_version_number := l_object_version_number;
1047 p_effective_start_date := l_effective_start_date;
1048 p_effective_end_date := l_effective_end_date;
1049 --
1050 hr_utility.set_location(' Leaving:'||l_proc, 70);
1051 --
1052 exception
1053 --
1054 when hr_api.validate_enabled then
1055 --
1056 -- As the Validate_Enabled exception has been raised
1057 -- we must rollback to the savepoint
1058 --
1059 ROLLBACK TO update_ELIGY_PROFILE;
1060 --
1061 -- Only set output warning arguments
1062 -- (Any key or derived arguments must be set to null
1063 -- when validation only mode is being used.)
1064 --
1065 hr_utility.set_location(' Leaving:'||l_proc, 80);
1066 --
1067 when others then
1068 --
1069 -- NOCOPY
1070 p_effective_start_date := null;
1071 p_effective_end_date := null;
1072 p_object_version_number := l_object_version_number;
1073 -- A validation or unexpected error has occured
1074 --
1075 ROLLBACK TO update_ELIGY_PROFILE;
1076 raise;
1077 --
1078 end update_ELIGY_PROFILE;
1079 -- ----------------------------------------------------------------------------
1080 -- |------------------------< delete_children >--------------------------|
1081 -- ----------------------------------------------------------------------------
1082 --
1083 procedure delete_children
1084 (p_eligy_prfl_id in number
1085 ,p_effective_date in date
1086 ,p_datetrack_mode in varchar2) is
1087
1088 cursor BEN_ELIGY_PRFL_RL_F is
1089 select a.eligy_prfl_rl_id, a.object_version_number, a.effective_end_date eed
1090 from BEN_ELIGY_PRFL_RL_F a
1091 where a.eligy_prfl_id = p_eligy_prfl_id
1092 and a.effective_end_date = (select max(b.effective_end_date)
1093 from BEN_ELIGY_PRFL_RL_F b
1094 where a.eligy_prfl_rl_id = b.eligy_prfl_rl_id);
1095
1096 cursor BEN_ELIG_AGE_PRTE_F is
1097 select a.elig_age_prte_id, a.object_version_number, a.effective_end_date eed
1098 from BEN_ELIG_AGE_PRTE_F a
1099 where a.eligy_prfl_id = p_eligy_prfl_id
1100 and a.effective_end_date = (select max(b.effective_end_date)
1101 from BEN_ELIG_AGE_PRTE_F b
1102 where a.elig_age_prte_id = b.elig_age_prte_id);
1103
1104 cursor BEN_ELIG_ASNT_SET_PRTE_F is
1105 select a.elig_asnt_set_prte_id, a.object_version_number, a.effective_end_date eed
1106 from BEN_ELIG_ASNT_SET_PRTE_F a
1107 where a.eligy_prfl_id = p_eligy_prfl_id
1108 and a.effective_end_date = (select max(b.effective_end_date)
1109 from BEN_ELIG_ASNT_SET_PRTE_F b
1110 where a.elig_asnt_set_prte_id = b.elig_asnt_set_prte_id);
1111
1112 cursor BEN_ELIG_BENFTS_GRP_PRTE_F is
1113 select a.elig_benfts_grp_prte_id, a.object_version_number, a.effective_end_date eed
1114 from BEN_ELIG_BENFTS_GRP_PRTE_F a
1115 where a.eligy_prfl_id = p_eligy_prfl_id
1116 and a.effective_end_date = (select max(b.effective_end_date)
1117 from BEN_ELIG_BENFTS_GRP_PRTE_F b
1118 where a.elig_benfts_grp_prte_id = b.elig_benfts_grp_prte_id);
1119
1120 cursor BEN_ELIG_BRGNG_UNIT_PRTE_F is
1121 select a.elig_brgng_unit_prte_id, a.object_version_number, a.effective_end_date eed
1122 from BEN_ELIG_BRGNG_UNIT_PRTE_F a
1123 where a.eligy_prfl_id = p_eligy_prfl_id
1124 and a.effective_end_date = (select max(b.effective_end_date)
1125 from BEN_ELIG_BRGNG_UNIT_PRTE_F b
1126 where a.elig_brgng_unit_prte_id = b.elig_brgng_unit_prte_id);
1127
1128 cursor BEN_ELIG_CBR_QUALD_BNF_F is
1129 select a.elig_cbr_quald_bnf_id, a.object_version_number, a.effective_end_date eed
1130 from BEN_ELIG_CBR_QUALD_BNF_F a
1131 where a.eligy_prfl_id = p_eligy_prfl_id
1132 and a.effective_end_date = (select max(b.effective_end_date)
1133 from BEN_ELIG_CBR_QUALD_BNF_F b
1134 where a.elig_cbr_quald_bnf_id = b.elig_cbr_quald_bnf_id);
1135
1136 cursor BEN_ELIG_CMBN_AGE_LOS_PRTE_F is
1137 select a.elig_cmbn_age_los_prte_id, a.object_version_number, a.effective_end_date eed
1138 from BEN_ELIG_CMBN_AGE_LOS_PRTE_F a
1139 where a.eligy_prfl_id = p_eligy_prfl_id
1140 and a.effective_end_date = (select max(b.effective_end_date)
1141 from BEN_ELIG_CMBN_AGE_LOS_PRTE_F b
1142 where a.elig_cmbn_age_los_prte_id = b.elig_cmbn_age_los_prte_id);
1143
1144 cursor BEN_ELIG_COMP_LVL_PRTE_F is
1145 select a.elig_comp_lvl_prte_id, a.object_version_number, a.effective_end_date eed
1146 from BEN_ELIG_COMP_LVL_PRTE_F a
1147 where a.eligy_prfl_id = p_eligy_prfl_id
1148 and a.effective_end_date = (select max(b.effective_end_date)
1149 from BEN_ELIG_COMP_LVL_PRTE_F b
1150 where a.elig_comp_lvl_prte_id = b.elig_comp_lvl_prte_id);
1151
1152 cursor BEN_ELIG_DPNT_CVRD_OTHR_PGM_F is
1153 select a.elig_dpnt_cvrd_othr_pgm_id, a.object_version_number, a.effective_end_date eed
1154 from BEN_ELIG_DPNT_CVRD_OTHR_PGM_F a
1155 where a.eligy_prfl_id = p_eligy_prfl_id
1156 and a.effective_end_date = (select max(b.effective_end_date)
1157 from BEN_ELIG_DPNT_CVRD_OTHR_PGM_F b
1158 where a.elig_dpnt_cvrd_othr_pgm_id = b.elig_dpnt_cvrd_othr_pgm_id);
1159
1160 cursor BEN_ELIG_DPNT_CVRD_OTHR_PL_F is
1161 select a.elig_dpnt_cvrd_othr_pl_id, a.object_version_number, a.effective_end_date eed
1162 from BEN_ELIG_DPNT_CVRD_OTHR_PL_F a
1163 where a.eligy_prfl_id = p_eligy_prfl_id
1164 and a.effective_end_date = (select max(b.effective_end_date)
1165 from BEN_ELIG_DPNT_CVRD_OTHR_PL_F b
1166 where a.elig_dpnt_cvrd_othr_pl_id = b.elig_dpnt_cvrd_othr_pl_id);
1167
1168 cursor BEN_ELIG_DPNT_CVRD_OTHR_PTIP_F is
1169 select a.elig_dpnt_cvrd_othr_ptip_id, a.object_version_number, a.effective_end_date eed
1170 from BEN_ELIG_DPNT_CVRD_OTHR_PTIP_F a
1171 where a.eligy_prfl_id = p_eligy_prfl_id
1172 and a.effective_end_date = (select max(b.effective_end_date)
1173 from BEN_ELIG_DPNT_CVRD_OTHR_PTIP_F b
1174 where a.elig_dpnt_cvrd_othr_ptip_id = b.elig_dpnt_cvrd_othr_ptip_id);
1175
1176 cursor BEN_ELIG_DPNT_CVRD_PLIP_F is
1177 select a.elig_dpnt_cvrd_plip_id, a.object_version_number, a.effective_end_date eed
1178 from BEN_ELIG_DPNT_CVRD_PLIP_F a
1179 where a.eligy_prfl_id = p_eligy_prfl_id
1180 and a.effective_end_date = (select max(b.effective_end_date)
1181 from BEN_ELIG_DPNT_CVRD_PLIP_F b
1182 where a.elig_dpnt_cvrd_plip_id = b.elig_dpnt_cvrd_plip_id);
1183
1184 cursor BEN_ELIG_EE_STAT_PRTE_F is
1185 select a.elig_ee_stat_prte_id, a.object_version_number, a.effective_end_date eed
1186 from BEN_ELIG_EE_STAT_PRTE_F a
1187 where a.eligy_prfl_id = p_eligy_prfl_id
1188 and a.effective_end_date = (select max(b.effective_end_date)
1189 from BEN_ELIG_EE_STAT_PRTE_F b
1190 where a.elig_ee_stat_prte_id = b.elig_ee_stat_prte_id);
1191
1192 cursor BEN_ELIG_ENRLD_ANTHR_OIPL_F is
1193 select a.elig_enrld_anthr_oipl_id, a.object_version_number, a.effective_end_date eed
1194 from BEN_ELIG_ENRLD_ANTHR_OIPL_F a
1195 where a.eligy_prfl_id = p_eligy_prfl_id
1196 and a.effective_end_date = (select max(b.effective_end_date)
1197 from BEN_ELIG_ENRLD_ANTHR_OIPL_F b
1198 where a.elig_enrld_anthr_oipl_id = b.elig_enrld_anthr_oipl_id);
1199
1200 cursor BEN_ELIG_ENRLD_ANTHR_PGM_F is
1201 select a.elig_enrld_anthr_pgm_id, a.object_version_number, a.effective_end_date eed
1202 from BEN_ELIG_ENRLD_ANTHR_PGM_F a
1203 where a.eligy_prfl_id = p_eligy_prfl_id
1204 and a.effective_end_date = (select max(b.effective_end_date)
1205 from BEN_ELIG_ENRLD_ANTHR_PGM_F b
1206 where a.elig_enrld_anthr_pgm_id = b.elig_enrld_anthr_pgm_id);
1207
1208 cursor BEN_ELIG_ENRLD_ANTHR_PLIP_F is
1209 select a.elig_enrld_anthr_plip_id, a.object_version_number, a.effective_end_date eed
1210 from BEN_ELIG_ENRLD_ANTHR_PLIP_F a
1211 where a.eligy_prfl_id = p_eligy_prfl_id
1212 and a.effective_end_date = (select max(b.effective_end_date)
1213 from BEN_ELIG_ENRLD_ANTHR_PLIP_F b
1214 where a.elig_enrld_anthr_plip_id = b.elig_enrld_anthr_plip_id);
1215
1216 cursor BEN_ELIG_ENRLD_ANTHR_PL_F is
1217 select a.elig_enrld_anthr_pl_id, a.object_version_number, a.effective_end_date eed
1218 from BEN_ELIG_ENRLD_ANTHR_PL_F a
1219 where a.eligy_prfl_id = p_eligy_prfl_id
1220 and a.effective_end_date = (select max(b.effective_end_date)
1221 from BEN_ELIG_ENRLD_ANTHR_PL_F b
1222 where a.elig_enrld_anthr_pl_id = b.elig_enrld_anthr_pl_id);
1223
1224 cursor BEN_ELIG_ENRLD_ANTHR_PTIP_F is
1225 select a.elig_enrld_anthr_ptip_id, a.object_version_number, a.effective_end_date eed
1226 from BEN_ELIG_ENRLD_ANTHR_PTIP_F a
1227 where a.eligy_prfl_id = p_eligy_prfl_id
1228 and a.effective_end_date = (select max(b.effective_end_date)
1229 from BEN_ELIG_ENRLD_ANTHR_PTIP_F b
1230 where a.elig_enrld_anthr_ptip_id = b.elig_enrld_anthr_ptip_id);
1231
1232 cursor BEN_ELIG_FL_TM_PT_TM_PRTE_F is
1233 select a.elig_fl_tm_pt_tm_prte_id, a.object_version_number, a.effective_end_date eed
1234 from BEN_ELIG_FL_TM_PT_TM_PRTE_F a
1235 where a.eligy_prfl_id = p_eligy_prfl_id
1236 and a.effective_end_date = (select max(b.effective_end_date)
1237 from BEN_ELIG_FL_TM_PT_TM_PRTE_F b
1238 where a.elig_fl_tm_pt_tm_prte_id = b.elig_fl_tm_pt_tm_prte_id);
1239
1240 cursor BEN_ELIG_GRD_PRTE_F is
1241 select a.elig_grd_prte_id, a.object_version_number, a.effective_end_date eed
1242 from BEN_ELIG_GRD_PRTE_F a
1243 where a.eligy_prfl_id = p_eligy_prfl_id
1244 and a.effective_end_date = (select max(b.effective_end_date)
1245 from BEN_ELIG_GRD_PRTE_F b
1246 where a.elig_grd_prte_id = b.elig_grd_prte_id);
1247
1248 cursor BEN_ELIG_HRLY_SLRD_PRTE_F is
1249 select a.elig_hrly_slrd_prte_id, a.object_version_number, a.effective_end_date eed
1250 from BEN_ELIG_HRLY_SLRD_PRTE_F a
1251 where a.eligy_prfl_id = p_eligy_prfl_id
1252 and a.effective_end_date = (select max(b.effective_end_date)
1253 from BEN_ELIG_HRLY_SLRD_PRTE_F b
1254 where a.elig_hrly_slrd_prte_id = b.elig_hrly_slrd_prte_id);
1255
1256 cursor BEN_ELIG_HRS_WKD_PRTE_F is
1257 select a.elig_hrs_wkd_prte_id, a.object_version_number, a.effective_end_date eed
1258 from BEN_ELIG_HRS_WKD_PRTE_F a
1259 where a.eligy_prfl_id = p_eligy_prfl_id
1260 and a.effective_end_date = (select max(b.effective_end_date)
1261 from BEN_ELIG_HRS_WKD_PRTE_F b
1262 where a.elig_hrs_wkd_prte_id = b.elig_hrs_wkd_prte_id);
1263
1264 cursor BEN_ELIG_JOB_PRTE_F is
1265 select a.elig_job_prte_id, a.object_version_number, a.effective_end_date eed
1266 from BEN_ELIG_JOB_PRTE_F a
1267 where a.eligy_prfl_id = p_eligy_prfl_id
1268 and a.effective_end_date = (select max(b.effective_end_date)
1269 from BEN_ELIG_JOB_PRTE_F b
1270 where a.elig_job_prte_id = b.elig_job_prte_id);
1271
1272 cursor BEN_ELIG_LBR_MMBR_PRTE_F is
1273 select a.elig_lbr_mmbr_prte_id, a.object_version_number, a.effective_end_date eed
1274 from BEN_ELIG_LBR_MMBR_PRTE_F a
1275 where a.eligy_prfl_id = p_eligy_prfl_id
1276 and a.effective_end_date = (select max(b.effective_end_date)
1277 from BEN_ELIG_LBR_MMBR_PRTE_F b
1278 where a.elig_lbr_mmbr_prte_id = b.elig_lbr_mmbr_prte_id);
1279
1280 cursor BEN_ELIG_LGL_ENTY_PRTE_F is
1281 select a.elig_lgl_enty_prte_id, a.object_version_number, a.effective_end_date eed
1282 from BEN_ELIG_LGL_ENTY_PRTE_F a
1283 where a.eligy_prfl_id = p_eligy_prfl_id
1284 and a.effective_end_date = (select max(b.effective_end_date)
1285 from BEN_ELIG_LGL_ENTY_PRTE_F b
1286 where a.elig_lgl_enty_prte_id = b.elig_lgl_enty_prte_id);
1287
1288 cursor BEN_ELIG_LOA_RSN_PRTE_F is
1289 select a.elig_loa_rsn_prte_id, a.object_version_number, a.effective_end_date eed
1290 from BEN_ELIG_LOA_RSN_PRTE_F a
1291 where a.eligy_prfl_id = p_eligy_prfl_id
1292 and a.effective_end_date = (select max(b.effective_end_date)
1293 from BEN_ELIG_LOA_RSN_PRTE_F b
1294 where a.elig_loa_rsn_prte_id = b.elig_loa_rsn_prte_id);
1295
1296 cursor BEN_ELIG_LOS_PRTE_F is
1297 select a.elig_los_prte_id, a.object_version_number, a.effective_end_date eed
1298 from BEN_ELIG_LOS_PRTE_F a
1299 where a.eligy_prfl_id = p_eligy_prfl_id
1300 and a.effective_end_date = (select max(b.effective_end_date)
1301 from BEN_ELIG_LOS_PRTE_F b
1302 where a.elig_los_prte_id = b.elig_los_prte_id);
1303
1304 cursor BEN_ELIG_LVG_RSN_PRTE_F is
1305 select a.elig_lvg_rsn_prte_id, a.object_version_number, a.effective_end_date eed
1306 from BEN_ELIG_LVG_RSN_PRTE_F a
1307 where a.eligy_prfl_id = p_eligy_prfl_id
1308 and a.effective_end_date = (select max(b.effective_end_date)
1309 from BEN_ELIG_LVG_RSN_PRTE_F b
1310 where a.elig_lvg_rsn_prte_id = b.elig_lvg_rsn_prte_id);
1311
1312 cursor BEN_ELIG_NO_OTHR_CVG_PRTE_F is
1313 select a.elig_no_othr_cvg_prte_id, a.object_version_number, a.effective_end_date eed
1314 from BEN_ELIG_NO_OTHR_CVG_PRTE_F a
1315 where a.eligy_prfl_id = p_eligy_prfl_id
1316 and a.effective_end_date = (select max(b.effective_end_date)
1317 from BEN_ELIG_NO_OTHR_CVG_PRTE_F b
1318 where a.elig_no_othr_cvg_prte_id = b.elig_no_othr_cvg_prte_id);
1319
1320 cursor BEN_ELIG_OPTD_MDCR_PRTE_F is
1321 select a.elig_optd_mdcr_prte_id, a.object_version_number, a.effective_end_date eed
1322 from BEN_ELIG_OPTD_MDCR_PRTE_F a
1323 where a.eligy_prfl_id = p_eligy_prfl_id
1324 and a.effective_end_date = (select max(b.effective_end_date)
1325 from BEN_ELIG_OPTD_MDCR_PRTE_F b
1326 where a.elig_optd_mdcr_prte_id = b.elig_optd_mdcr_prte_id);
1327
1328 cursor BEN_ELIG_ORG_UNIT_PRTE_F is
1329 select a.elig_org_unit_prte_id, a.object_version_number, a.effective_end_date eed
1330 from BEN_ELIG_ORG_UNIT_PRTE_F a
1331 where a.eligy_prfl_id = p_eligy_prfl_id
1332 and a.effective_end_date = (select max(b.effective_end_date)
1333 from BEN_ELIG_ORG_UNIT_PRTE_F b
1334 where a.elig_org_unit_prte_id = b.elig_org_unit_prte_id);
1335
1336 cursor BEN_ELIG_OTHR_PTIP_PRTE_F is
1337 select a.elig_othr_ptip_prte_id, a.object_version_number, a.effective_end_date eed
1338 from BEN_ELIG_OTHR_PTIP_PRTE_F a
1339 where a.eligy_prfl_id = p_eligy_prfl_id
1340 and a.effective_end_date = (select max(b.effective_end_date)
1341 from BEN_ELIG_OTHR_PTIP_PRTE_F b
1342 where a.elig_othr_ptip_prte_id = b.elig_othr_ptip_prte_id);
1343
1344 cursor BEN_ELIG_DPNT_OTHR_PTIP_F is
1345 select a.elig_dpnt_othr_ptip_id, a.object_version_number, a.effective_end_date eed
1346 from BEN_ELIG_DPNT_OTHR_PTIP_F a
1347 where a.eligy_prfl_id = p_eligy_prfl_id
1348 and a.effective_end_date = (select max(b.effective_end_date)
1349 from BEN_ELIG_DPNT_OTHR_PTIP_F b
1350 where a.elig_dpnt_othr_ptip_id = b.elig_dpnt_othr_ptip_id);
1351
1352 cursor BEN_ELIG_PCT_FL_TM_PRTE_F is
1353 select a.elig_pct_fl_tm_prte_id, a.object_version_number, a.effective_end_date eed
1354 from BEN_ELIG_PCT_FL_TM_PRTE_F a
1355 where a.eligy_prfl_id = p_eligy_prfl_id
1356 and a.effective_end_date = (select max(b.effective_end_date)
1357 from BEN_ELIG_PCT_FL_TM_PRTE_F b
1358 where a.elig_pct_fl_tm_prte_id = b.elig_pct_fl_tm_prte_id);
1359
1360 cursor BEN_ELIG_PER_TYP_PRTE_F is
1361 select a.elig_per_typ_prte_id, a.object_version_number, a.effective_end_date eed
1362 from BEN_ELIG_PER_TYP_PRTE_F a
1363 where a.eligy_prfl_id = p_eligy_prfl_id
1364 and a.effective_end_date = (select max(b.effective_end_date)
1365 from BEN_ELIG_PER_TYP_PRTE_F b
1366 where a.elig_per_typ_prte_id = b.elig_per_typ_prte_id);
1367
1368 cursor BEN_ELIG_PPL_GRP_PRTE_F is
1369 select a.elig_ppl_grp_prte_id, a.object_version_number, a.effective_end_date eed
1370 from BEN_ELIG_PPL_GRP_PRTE_F a
1371 where a.eligy_prfl_id = p_eligy_prfl_id
1372 and a.effective_end_date = (select max(b.effective_end_date)
1373 from BEN_ELIG_PPL_GRP_PRTE_F b
1374 where a.elig_ppl_grp_prte_id = b.elig_ppl_grp_prte_id);
1375
1376 cursor BEN_ELIG_PRTT_ANTHR_PL_PRTE_F is
1377 select a.elig_prtt_anthr_pl_prte_id, a.object_version_number, a.effective_end_date eed
1378 from BEN_ELIG_PRTT_ANTHR_PL_PRTE_F a
1379 where a.eligy_prfl_id = p_eligy_prfl_id
1380 and a.effective_end_date = (select max(b.effective_end_date)
1381 from BEN_ELIG_PRTT_ANTHR_PL_PRTE_F b
1382 where a.elig_prtt_anthr_pl_prte_id = b.elig_prtt_anthr_pl_prte_id);
1383
1384 cursor BEN_ELIG_PSTL_CD_R_RNG_PRTE_F is
1385 select a.elig_pstl_cd_r_rng_prte_id, a.object_version_number, a.effective_end_date eed
1386 from BEN_ELIG_PSTL_CD_R_RNG_PRTE_F a
1387 where a.eligy_prfl_id = p_eligy_prfl_id
1388 and a.effective_end_date = (select max(b.effective_end_date)
1389 from BEN_ELIG_PSTL_CD_R_RNG_PRTE_F b
1390 where a.elig_pstl_cd_r_rng_prte_id = b.elig_pstl_cd_r_rng_prte_id);
1391
1392 cursor BEN_ELIG_PYRL_PRTE_F is
1393 select a.elig_pyrl_prte_id, a.object_version_number, a.effective_end_date eed
1394 from BEN_ELIG_PYRL_PRTE_F a
1395 where a.eligy_prfl_id = p_eligy_prfl_id
1396 and a.effective_end_date = (select max(b.effective_end_date)
1397 from BEN_ELIG_PYRL_PRTE_F b
1398 where a.elig_pyrl_prte_id = b.elig_pyrl_prte_id);
1399
1400 cursor BEN_ELIG_PY_BSS_PRTE_F is
1401 select a.elig_py_bss_prte_id, a.object_version_number, a.effective_end_date eed
1402 from BEN_ELIG_PY_BSS_PRTE_F a
1403 where a.eligy_prfl_id = p_eligy_prfl_id
1404 and a.effective_end_date = (select max(b.effective_end_date)
1405 from BEN_ELIG_PY_BSS_PRTE_F b
1406 where a.elig_py_bss_prte_id = b.elig_py_bss_prte_id);
1407
1408 cursor BEN_ELIG_SCHEDD_HRS_PRTE_F is
1409 select a.elig_schedd_hrs_prte_id, a.object_version_number, a.effective_end_date eed
1410 from BEN_ELIG_SCHEDD_HRS_PRTE_F a
1411 where a.eligy_prfl_id = p_eligy_prfl_id
1412 and a.effective_end_date = (select max(b.effective_end_date)
1413 from BEN_ELIG_SCHEDD_HRS_PRTE_F b
1414 where a.elig_schedd_hrs_prte_id = b.elig_schedd_hrs_prte_id);
1415
1416 cursor BEN_ELIG_SVC_AREA_PRTE_F is
1417 select a.elig_svc_area_prte_id, a.object_version_number, a.effective_end_date eed
1418 from BEN_ELIG_SVC_AREA_PRTE_F a
1419 where a.eligy_prfl_id = p_eligy_prfl_id
1420 and a.effective_end_date = (select max(b.effective_end_date)
1421 from BEN_ELIG_SVC_AREA_PRTE_F b
1422 where a.elig_svc_area_prte_id = b.elig_svc_area_prte_id);
1423
1424 cursor BEN_ELIG_WK_LOC_PRTE_F is
1425 select a.elig_wk_loc_prte_id, a.object_version_number, a.effective_end_date eed
1426 from BEN_ELIG_WK_LOC_PRTE_F a
1427 where a.eligy_prfl_id = p_eligy_prfl_id
1428 and a.effective_end_date = (select max(b.effective_end_date)
1429 from BEN_ELIG_WK_LOC_PRTE_F b
1430 where a.elig_wk_loc_prte_id = b.elig_wk_loc_prte_id);
1431
1432 cursor BEN_PRTN_ELIG_PRFL_F is
1433 select a.prtn_elig_prfl_id, a.object_version_number, a.effective_end_date eed
1434 from BEN_PRTN_ELIG_PRFL_F a
1435 where a.eligy_prfl_id = p_eligy_prfl_id
1436 and a.effective_end_date = (select max(b.effective_end_date)
1437 from BEN_PRTN_ELIG_PRFL_F b
1438 where a.prtn_elig_prfl_id = b.prtn_elig_prfl_id);
1439
1440
1441 cursor BEN_CNTNG_PRTN_ELIG_PRFL_F is
1442 select a.cntng_prtn_elig_prfl_id, a.object_version_number, a.effective_end_date eed
1443 from BEN_CNTNG_PRTN_ELIG_PRFL_F a
1444 where a.eligy_prfl_id = p_eligy_prfl_id
1445 and a.effective_end_date = (select max(b.effective_end_date)
1446 from BEN_CNTNG_PRTN_ELIG_PRFL_F b
1447 where a.cntng_prtn_elig_prfl_id = b.cntng_prtn_elig_prfl_id);
1448
1449
1450
1451 cursor BEN_ELIG_GNDR_PRTE_F is
1452 select a.ELIG_GNDR_PRTE_ID, a.object_version_number, a.effective_end_date eed
1453 from BEN_ELIG_GNDR_PRTE_F a
1454 where a.eligy_prfl_id = p_eligy_prfl_id
1455 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1456
1457
1458 cursor BEN_ELIG_MRTL_STS_PRTE_F is
1459 select a.ELIG_MRTL_STS_PRTE_ID , a.object_version_number, a.effective_end_date eed
1460 from BEN_ELIG_MRTL_STS_PRTE_F a
1461 where a.eligy_prfl_id = p_eligy_prfl_id
1462 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1463
1464 cursor BEN_ELIG_DSBLTY_CTG_PRTE_F is
1465 select a.ELIG_DSBLTY_CTG_PRTE_ID , a.object_version_number, a.effective_end_date eed
1466 from BEN_ELIG_DSBLTY_CTG_PRTE_f a
1467 where a.eligy_prfl_id = p_eligy_prfl_id
1468 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1469
1470 cursor BEN_ELIG_DSBLTY_RSN_PRTE_F is
1471 select a.ELIG_DSBLTY_RSN_PRTE_ID , a.object_version_number, a.effective_end_date eed
1472 from BEN_ELIG_DSBLTY_RSN_PRTE_F a
1473 where a.eligy_prfl_id = p_eligy_prfl_id
1474 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1475
1476 cursor BEN_ELIG_DSBLTY_DGR_PRTE_F is
1477 select a.ELIG_DSBLTY_DGR_PRTE_ID , a.object_version_number, a.effective_end_date eed
1478 from BEN_ELIG_DSBLTY_DGR_PRTE_F a
1479 where a.eligy_prfl_id = p_eligy_prfl_id
1480 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1481
1482 cursor BEN_ELIG_Suppl_role_prte_f is
1483 select a.ELIG_Suppl_role_prte_ID , a.object_version_number, a.effective_end_date eed
1484 from BEN_ELIG_Suppl_role_prte_f a
1485 where a.eligy_prfl_id = p_eligy_prfl_id
1486 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1487
1488 cursor BEN_ELIG_qual_titl_prte_f is
1489 select a.ELIG_qual_titl_prte_ID , a.object_version_number, a.effective_end_date eed
1490 from BEN_ELIG_qual_titl_prte_f a
1491 where a.eligy_prfl_id = p_eligy_prfl_id
1492 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1493
1494
1495 cursor BEN_ELIG_pstn_prte_f is
1496 select a.ELIG_pstn_prte_ID , a.object_version_number, a.effective_end_date eed
1497 from BEN_ELIG_pstn_prte_f a
1498 where a.eligy_prfl_id = p_eligy_prfl_id
1499 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1500
1501 cursor BEN_ELIG_prbtn_perd_prte_f is
1502 select a.ELIG_prbtn_perd_prte_ID , a.object_version_number, a.effective_end_date eed
1503 from BEN_ELIG_prbtn_perd_prte_f a
1504 where a.eligy_prfl_id = p_eligy_prfl_id
1505 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1506
1507 cursor BEN_ELIG_sp_clng_prg_prte_f is
1508 select a.ELIG_sp_clng_prg_prte_ID , a.object_version_number, a.effective_end_date eed
1509 from BEN_ELIG_sp_clng_prg_prte_f a
1510 where a.eligy_prfl_id = p_eligy_prfl_id
1511 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1512
1513 cursor BEN_ELIG_DSBLD_PRTE_F is
1514 select a.ELIG_DSBLD_PRTE_ID , a.object_version_number, a.effective_end_date eed
1515 from BEN_ELIG_DSBLD_PRTE_F a
1516 where a.eligy_prfl_id = p_eligy_prfl_id
1517 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1518
1519 cursor BEN_ELIG_TTL_CVG_VOL_PRTE_F is
1520 select a.ELIG_TTL_CVG_VOL_PRTE_ID , a.object_version_number, a.effective_end_date eed
1521 from BEN_ELIG_TTL_CVG_VOL_PRTE_F a
1522 where a.eligy_prfl_id = p_eligy_prfl_id
1523 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1524
1525 cursor BEN_ELIG_TTL_PRTT_PRTE_F is
1526 select a.ELIG_TTL_PRTT_PRTE_ID , a.object_version_number, a.effective_end_date eed
1527 from BEN_ELIG_TTL_PRTT_PRTE_F a
1528 where a.eligy_prfl_id = p_eligy_prfl_id
1529 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1530
1531 cursor BEN_ELIG_ANTHR_PL_PRTE_F is
1532 select a.ELIG_ANTHR_PL_PRTE_ID , a.object_version_number, a.effective_end_date eed
1533 from BEN_ELIG_ANTHR_PL_PRTE_F a
1534 where a.eligy_prfl_id = p_eligy_prfl_id
1535 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1536
1537 cursor BEN_ELIG_HLTH_CVG_PRTE_F is
1538 select a.ELIG_HLTH_CVG_PRTE_ID , a.object_version_number, a.effective_end_date eed
1539 from BEN_ELIG_HLTH_CVG_PRTE_F a
1540 where a.eligy_prfl_id = p_eligy_prfl_id
1541 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1542
1543 cursor BEN_ELIG_COMPTNCY_PRTE_F is
1544 select a.ELIG_COMPTNCY_PRTE_ID , a.object_version_number, a.effective_end_date eed
1545 from BEN_ELIG_COMPTNCY_PRTE_F a
1546 where a.eligy_prfl_id = p_eligy_prfl_id
1547 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1548
1549 cursor BEN_ELIG_QUA_IN_GR_PRTE_F is
1550 select a.ELIG_QUA_IN_GR_PRTE_ID , a.object_version_number, a.effective_end_date eed
1551 from BEN_ELIG_QUA_IN_GR_PRTE_F a
1552 where a.eligy_prfl_id = p_eligy_prfl_id
1553 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1554
1555 cursor BEN_ELIG_PERF_RTNG_PRTE_F is
1556 select a.ELIG_PERF_RTNG_PRTE_ID , a.object_version_number, a.effective_end_date eed
1557 from BEN_ELIG_PERF_RTNG_PRTE_F a
1558 where a.eligy_prfl_id = p_eligy_prfl_id
1559 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1560 --
1561 -- Bug No 4411798
1562 --
1563 cursor BEN_ELIGY_CRIT_VALUES_F is
1564 select a.ELIGY_CRIT_VALUES_ID , a.object_version_number, a.effective_end_date eed
1565 from BEN_ELIGY_CRIT_VALUES_F a
1566 where a.eligy_prfl_id = p_eligy_prfl_id
1567 and p_effective_date between a.effective_start_date and a.effective_end_date ;
1568 --
1569 -- End Bug No 4411798
1570 --
1571 l_row BEN_ELIGY_PRFL_RL_F%rowtype;
1572 l_effective_start_date date;
1573 l_effective_end_date date;
1574 l_effective_date date;
1575
1576 begin
1577 -- delete all children records of the eligy-prfl
1578 --
1579 -- These tables are in bendev11 as children, but the table are not used in our
1580 -- product at this time:
1581 -- BEN_ELIG_DPNT_CVRD_OTHR_OIPL_F
1582 -- BEN_ELIG_DSBLD_STAT_PRTE_F
1583 -- BEN_ELIG_MLTRY_STAT_PRTE_F
1584 -- BEN_ELIG_PRTT_ANTHR_PGM_F
1585 for l_row in BEN_ELIGY_PRFL_RL_F loop
1586 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1587 or p_datetrack_mode <> hr_api.g_delete then
1588 if p_datetrack_mode = hr_api.g_zap then
1589 l_effective_date := l_row.eed;
1590 else l_effective_date := p_effective_date;
1591 end if;
1592 ben_ELIGY_PROFILE_RULE_api.delete_ELIGY_PROFILE_RULE
1593 (p_validate => false
1594 ,p_eligy_prfl_rl_id => l_row.eligy_prfl_rl_id
1595 ,p_effective_start_date => l_effective_start_date
1596 ,p_effective_end_date => l_effective_end_date
1597 ,p_object_version_number => l_row.object_version_number
1598 ,p_effective_date => l_effective_date
1599 ,p_datetrack_mode => p_datetrack_mode);
1600 end if;
1601 end loop;
1602 for l_row in BEN_ELIG_AGE_PRTE_F loop
1603 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1604 or p_datetrack_mode <> hr_api.g_delete then
1605 if p_datetrack_mode = hr_api.g_zap then
1606 l_effective_date := l_row.eed;
1607 else l_effective_date := p_effective_date;
1608 end if;
1609 ben_ELIG_AGE_PRTE_api.delete_ELIG_AGE_PRTE
1610 (p_validate => false
1611 ,p_elig_age_prte_id => l_row.elig_age_prte_id
1612 ,p_effective_start_date => l_effective_start_date
1613 ,p_effective_end_date => l_effective_end_date
1614 ,p_object_version_number => l_row.object_version_number
1615 ,p_effective_date => l_effective_date
1616 ,p_datetrack_mode => p_datetrack_mode);
1617 end if;
1618 end loop;
1619 for l_row in BEN_ELIG_ASNT_SET_PRTE_F loop
1620 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1621 or p_datetrack_mode <> hr_api.g_delete then
1622 if p_datetrack_mode = hr_api.g_zap then
1623 l_effective_date := l_row.eed;
1624 else l_effective_date := p_effective_date;
1625 end if;
1626 BEN_ELIG_ASNT_SET_PRTE_api.delete_ELIG_ASNT_SET_PRTE
1627 (p_validate => false
1628 ,p_elig_asnt_set_prte_id => l_row.elig_asnt_set_prte_id
1629 ,p_effective_start_date => l_effective_start_date
1630 ,p_effective_end_date => l_effective_end_date
1631 ,p_object_version_number => l_row.object_version_number
1632 ,p_effective_date => l_effective_date
1633 ,p_datetrack_mode => p_datetrack_mode);
1634 end if;
1635 end loop;
1636 for l_row in BEN_ELIG_BENFTS_GRP_PRTE_F loop
1637 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1638 or p_datetrack_mode <> hr_api.g_delete then
1639 if p_datetrack_mode = hr_api.g_zap then
1640 l_effective_date := l_row.eed;
1641 else l_effective_date := p_effective_date;
1642 end if;
1643 BEN_ELIG_BENFTS_GRP_PRTE_api.delete_ELIG_BENFTS_GRP_PRTE
1644 (p_validate => false
1645 ,p_elig_benfts_grp_prte_id => l_row.elig_benfts_grp_prte_id
1646 ,p_effective_start_date => l_effective_start_date
1647 ,p_effective_end_date => l_effective_end_date
1648 ,p_object_version_number => l_row.object_version_number
1649 ,p_effective_date => l_effective_date
1650 ,p_datetrack_mode => p_datetrack_mode);
1651 end if;
1652 end loop;
1653 for l_row in BEN_ELIG_BRGNG_UNIT_PRTE_F loop
1654 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1655 or p_datetrack_mode <> hr_api.g_delete then
1656 if p_datetrack_mode = hr_api.g_zap then
1657 l_effective_date := l_row.eed;
1658 else l_effective_date := p_effective_date;
1659 end if;
1660 BEN_ELIG_BRGNG_UNIT_PRTE_api.delete_ELIG_BRGNG_UNIT_PRTE
1661 (p_validate => false
1662 ,p_elig_brgng_unit_prte_id => l_row.elig_brgng_unit_prte_id
1663 ,p_effective_start_date => l_effective_start_date
1664 ,p_effective_end_date => l_effective_end_date
1665 ,p_object_version_number => l_row.object_version_number
1666 ,p_effective_date => l_effective_date
1667 ,p_datetrack_mode => p_datetrack_mode);
1668 end if;
1669 end loop;
1670 for l_row in BEN_ELIG_CBR_QUALD_BNF_F loop
1671 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1672 or p_datetrack_mode <> hr_api.g_delete then
1673 if p_datetrack_mode = hr_api.g_zap then
1674 l_effective_date := l_row.eed;
1675 else l_effective_date := p_effective_date;
1676 end if;
1677 BEN_ELIG_CBR_QUALD_BNF_api.delete_ELIG_CBR_QUALD_BNF
1678 (p_validate => false
1679 ,p_elig_cbr_quald_bnf_id => l_row.elig_cbr_quald_bnf_id
1680 ,p_effective_start_date => l_effective_start_date
1681 ,p_effective_end_date => l_effective_end_date
1682 ,p_object_version_number => l_row.object_version_number
1683 ,p_effective_date => l_effective_date
1684 ,p_datetrack_mode => p_datetrack_mode);
1685 end if;
1686 end loop;
1687 for l_row in BEN_ELIG_CMBN_AGE_LOS_PRTE_F loop
1688 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1689 or p_datetrack_mode <> hr_api.g_delete then
1690 if p_datetrack_mode = hr_api.g_zap then
1691 l_effective_date := l_row.eed;
1692 else l_effective_date := p_effective_date;
1693 end if;
1694 ben_ELIG_CMBN_AGE_LOS_api.delete_ELIG_CMBN_AGE_LOS
1695 (p_validate => false
1696 ,p_elig_cmbn_age_los_prte_id => l_row.elig_cmbn_age_los_prte_id
1697 ,p_effective_start_date => l_effective_start_date
1698 ,p_effective_end_date => l_effective_end_date
1699 ,p_object_version_number => l_row.object_version_number
1700 ,p_effective_date => l_effective_date
1701 ,p_datetrack_mode => p_datetrack_mode);
1702 end if;
1703 end loop;
1704 for l_row in BEN_ELIG_COMP_LVL_PRTE_F loop
1705 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1706 or p_datetrack_mode <> hr_api.g_delete then
1707 if p_datetrack_mode = hr_api.g_zap then
1708 l_effective_date := l_row.eed;
1709 else l_effective_date := p_effective_date;
1710 end if;
1711 BEN_ELIG_COMP_LVL_PRTE_api.delete_ELIG_COMP_LVL_PRTE
1712 (p_validate => false
1713 ,p_elig_comp_lvl_prte_id => l_row.elig_comp_lvl_prte_id
1714 ,p_effective_start_date => l_effective_start_date
1715 ,p_effective_end_date => l_effective_end_date
1716 ,p_object_version_number => l_row.object_version_number
1717 ,p_effective_date => l_effective_date
1718 ,p_datetrack_mode => p_datetrack_mode);
1719 end if;
1720 end loop;
1721 for l_row in BEN_ELIG_DPNT_CVRD_OTHR_PGM_F loop
1722 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1723 or p_datetrack_mode <> hr_api.g_delete then
1724 if p_datetrack_mode = hr_api.g_zap then
1725 l_effective_date := l_row.eed;
1726 else l_effective_date := p_effective_date;
1727 end if;
1728 ben_ELIG_DPNT_CVRD_O_PGM_api.delete_ELIG_DPNT_CVRD_O_PGM
1729 (p_validate => false
1730 ,p_elig_dpnt_cvrd_othr_pgm_id => l_row.elig_dpnt_cvrd_othr_pgm_id
1731 ,p_effective_start_date => l_effective_start_date
1732 ,p_effective_end_date => l_effective_end_date
1733 ,p_object_version_number => l_row.object_version_number
1734 ,p_effective_date => l_effective_date
1735 ,p_datetrack_mode => p_datetrack_mode);
1736 end if;
1737 end loop;
1738 for l_row in BEN_ELIG_DPNT_CVRD_OTHR_PL_F loop
1739 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1740 or p_datetrack_mode <> hr_api.g_delete then
1741 if p_datetrack_mode = hr_api.g_zap then
1742 l_effective_date := l_row.eed;
1743 else l_effective_date := p_effective_date;
1744 end if;
1745 ben_ELIG_DPNT_CVD_OTHR_PL_api.delete_ELIG_DPNT_CVD_OTHR_PL
1746 (p_validate => false
1747 ,p_elig_dpnt_cvrd_othr_pl_id => l_row.elig_dpnt_cvrd_othr_pl_id
1748 ,p_effective_start_date => l_effective_start_date
1749 ,p_effective_end_date => l_effective_end_date
1750 ,p_object_version_number => l_row.object_version_number
1751 ,p_effective_date => l_effective_date
1752 ,p_datetrack_mode => p_datetrack_mode);
1753 end if;
1754 end loop;
1755 for l_row in BEN_ELIG_DPNT_CVRD_OTHR_PTIP_F loop
1756 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1757 or p_datetrack_mode <> hr_api.g_delete then
1758 if p_datetrack_mode = hr_api.g_zap then
1759 l_effective_date := l_row.eed;
1760 else l_effective_date := p_effective_date;
1761 end if;
1762 ben_ELIG_DPNT_CVRD_O_PTIP_api.delete_ELIG_DPNT_CVRD_O_PTIP
1763 (p_validate => false
1764 ,p_elig_dpnt_cvrd_othr_ptip_id => l_row.elig_dpnt_cvrd_othr_ptip_id
1765 ,p_effective_start_date => l_effective_start_date
1766 ,p_effective_end_date => l_effective_end_date
1767 ,p_object_version_number => l_row.object_version_number
1768 ,p_effective_date => l_effective_date
1769 ,p_datetrack_mode => p_datetrack_mode);
1770 end if;
1771 end loop;
1772 for l_row in BEN_ELIG_DPNT_CVRD_PLIP_F loop
1773 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1774 or p_datetrack_mode <> hr_api.g_delete then
1775 if p_datetrack_mode = hr_api.g_zap then
1776 l_effective_date := l_row.eed;
1777 else l_effective_date := p_effective_date;
1778 end if;
1779 ben_ELIG_DPNT_CVRD_PLIP_api.delete_ELIG_DPNT_CVRD_PLIP
1780 (p_validate => false
1781 ,p_elig_dpnt_cvrd_plip_id => l_row.elig_dpnt_cvrd_plip_id
1782 ,p_effective_start_date => l_effective_start_date
1783 ,p_effective_end_date => l_effective_end_date
1784 ,p_object_version_number => l_row.object_version_number
1785 ,p_effective_date => l_effective_date
1786 ,p_datetrack_mode => p_datetrack_mode);
1787 end if;
1788 end loop;
1789 for l_row in BEN_ELIG_EE_STAT_PRTE_F loop
1790 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1791 or p_datetrack_mode <> hr_api.g_delete then
1792 if p_datetrack_mode = hr_api.g_zap then
1793 l_effective_date := l_row.eed;
1794 else l_effective_date := p_effective_date;
1795 end if;
1796 BEN_ELIG_EE_STAT_PRTE_api.delete_ELIG_EE_STAT_PRTE
1797 (p_validate => false
1798 ,p_elig_ee_stat_prte_id => l_row.elig_ee_stat_prte_id
1799 ,p_effective_start_date => l_effective_start_date
1800 ,p_effective_end_date => l_effective_end_date
1801 ,p_object_version_number => l_row.object_version_number
1802 ,p_effective_date => l_effective_date
1803 ,p_datetrack_mode => p_datetrack_mode);
1804 end if;
1805 end loop;
1806 for l_row in BEN_ELIG_ENRLD_ANTHR_OIPL_F loop
1807 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1808 or p_datetrack_mode <> hr_api.g_delete then
1809 if p_datetrack_mode = hr_api.g_zap then
1810 l_effective_date := l_row.eed;
1811 else l_effective_date := p_effective_date;
1812 end if;
1813 BEN_ELIG_ENRLD_ANTHR_OIPL_api.delete_ELIG_ENRLD_ANTHR_OIPL
1814 (p_validate => false
1815 ,p_elig_enrld_anthr_oipl_id => l_row.elig_enrld_anthr_oipl_id
1816 ,p_effective_start_date => l_effective_start_date
1817 ,p_effective_end_date => l_effective_end_date
1818 ,p_object_version_number => l_row.object_version_number
1819 ,p_effective_date => l_effective_date
1820 ,p_datetrack_mode => p_datetrack_mode);
1821 end if;
1822 end loop;
1823 for l_row in BEN_ELIG_ENRLD_ANTHR_PGM_F loop
1824 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1825 or p_datetrack_mode <> hr_api.g_delete then
1826 if p_datetrack_mode = hr_api.g_zap then
1827 l_effective_date := l_row.eed;
1828 else l_effective_date := p_effective_date;
1829 end if;
1830 BEN_ELIG_ENRLD_ANTHR_PGM_api.delete_ELIG_ENRLD_ANTHR_PGM
1831 (p_validate => false
1832 ,p_elig_enrld_anthr_pgm_id => l_row.elig_enrld_anthr_pgm_id
1833 ,p_effective_start_date => l_effective_start_date
1834 ,p_effective_end_date => l_effective_end_date
1835 ,p_object_version_number => l_row.object_version_number
1836 ,p_effective_date => l_effective_date
1837 ,p_datetrack_mode => p_datetrack_mode);
1838 end if;
1839 end loop;
1840 for l_row in BEN_ELIG_ENRLD_ANTHR_PLIP_F loop
1841 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1842 or p_datetrack_mode <> hr_api.g_delete then
1843 if p_datetrack_mode = hr_api.g_zap then
1844 l_effective_date := l_row.eed;
1845 else l_effective_date := p_effective_date;
1846 end if;
1847 BEN_ELIG_ENRLD_ANTHR_PLIP_api.delete_ELIG_ENRLD_ANTHR_PLIP
1848 (p_validate => false
1849 ,p_elig_enrld_anthr_plip_id => l_row.elig_enrld_anthr_plip_id
1850 ,p_effective_start_date => l_effective_start_date
1851 ,p_effective_end_date => l_effective_end_date
1852 ,p_object_version_number => l_row.object_version_number
1853 ,p_effective_date => l_effective_date
1854 ,p_datetrack_mode => p_datetrack_mode);
1855 end if;
1856 end loop;
1857 for l_row in BEN_ELIG_ENRLD_ANTHR_PL_F loop
1858 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1859 or p_datetrack_mode <> hr_api.g_delete then
1860 if p_datetrack_mode = hr_api.g_zap then
1861 l_effective_date := l_row.eed;
1862 else l_effective_date := p_effective_date;
1863 end if;
1864 BEN_ELIG_ENRLD_ANTHR_PL_api.delete_ELIG_ENRLD_ANTHR_PL
1865 (p_validate => false
1866 ,p_elig_enrld_anthr_pl_id => l_row.elig_enrld_anthr_pl_id
1867 ,p_effective_start_date => l_effective_start_date
1868 ,p_effective_end_date => l_effective_end_date
1869 ,p_object_version_number => l_row.object_version_number
1870 ,p_effective_date => l_effective_date
1871 ,p_datetrack_mode => p_datetrack_mode);
1872 end if;
1873 end loop;
1874 for l_row in BEN_ELIG_ENRLD_ANTHR_PTIP_F loop
1875 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1876 or p_datetrack_mode <> hr_api.g_delete then
1877 if p_datetrack_mode = hr_api.g_zap then
1878 l_effective_date := l_row.eed;
1879 else l_effective_date := p_effective_date;
1880 end if;
1881 BEN_ELIG_ENRLD_ANTHR_PTIP_api.delete_ELIG_ENRLD_ANTHR_PTIP
1882 (p_validate => false
1883 ,p_elig_enrld_anthr_ptip_id => l_row.elig_enrld_anthr_ptip_id
1884 ,p_effective_start_date => l_effective_start_date
1885 ,p_effective_end_date => l_effective_end_date
1886 ,p_object_version_number => l_row.object_version_number
1887 ,p_effective_date => l_effective_date
1888 ,p_datetrack_mode => p_datetrack_mode);
1889 end if;
1890 end loop;
1891 for l_row in BEN_ELIG_FL_TM_PT_TM_PRTE_F loop
1892 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1893 or p_datetrack_mode <> hr_api.g_delete then
1894 if p_datetrack_mode = hr_api.g_zap then
1895 l_effective_date := l_row.eed;
1896 else l_effective_date := p_effective_date;
1897 end if;
1898 BEN_ELIG_FL_TM_PT_TM_PRTE_api.delete_ELIG_FL_TM_PT_TM_PRTE
1899 (p_validate => false
1900 ,p_elig_fl_tm_pt_tm_prte_id => l_row.elig_fl_tm_pt_tm_prte_id
1901 ,p_effective_start_date => l_effective_start_date
1902 ,p_effective_end_date => l_effective_end_date
1903 ,p_object_version_number => l_row.object_version_number
1904 ,p_effective_date => l_effective_date
1905 ,p_datetrack_mode => p_datetrack_mode);
1906 end if;
1907 end loop;
1908 for l_row in BEN_ELIG_GRD_PRTE_F loop
1909 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1910 or p_datetrack_mode <> hr_api.g_delete then
1911 if p_datetrack_mode = hr_api.g_zap then
1912 l_effective_date := l_row.eed;
1913 else l_effective_date := p_effective_date;
1914 end if;
1915 BEN_ELIG_GRD_PRTE_api.delete_ELIG_GRD_PRTE
1916 (p_validate => false
1917 ,p_elig_grd_prte_id => l_row.elig_grd_prte_id
1918 ,p_effective_start_date => l_effective_start_date
1919 ,p_effective_end_date => l_effective_end_date
1920 ,p_object_version_number => l_row.object_version_number
1921 ,p_effective_date => l_effective_date
1922 ,p_datetrack_mode => p_datetrack_mode);
1923 end if;
1924 end loop;
1925 for l_row in BEN_ELIG_HRLY_SLRD_PRTE_F loop
1926 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1927 or p_datetrack_mode <> hr_api.g_delete then
1928 if p_datetrack_mode = hr_api.g_zap then
1929 l_effective_date := l_row.eed;
1930 else l_effective_date := p_effective_date;
1931 end if;
1932 BEN_ELIG_HRLY_SLRD_PRTE_api.delete_ELIG_HRLY_SLRD_PRTE
1933 (p_validate => false
1934 ,p_elig_hrly_slrd_prte_id => l_row.elig_hrly_slrd_prte_id
1935 ,p_effective_start_date => l_effective_start_date
1936 ,p_effective_end_date => l_effective_end_date
1937 ,p_object_version_number => l_row.object_version_number
1938 ,p_effective_date => l_effective_date
1939 ,p_datetrack_mode => p_datetrack_mode);
1940 end if;
1941 end loop;
1942 for l_row in BEN_ELIG_HRS_WKD_PRTE_F loop
1943 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1944 or p_datetrack_mode <> hr_api.g_delete then
1945 if p_datetrack_mode = hr_api.g_zap then
1946 l_effective_date := l_row.eed;
1947 else l_effective_date := p_effective_date;
1948 end if;
1949 BEN_ELIG_HRS_WKD_PRTE_api.delete_ELIG_HRS_WKD_PRTE
1950 (p_validate => false
1951 ,p_elig_hrs_wkd_prte_id => l_row.elig_hrs_wkd_prte_id
1952 ,p_effective_start_date => l_effective_start_date
1953 ,p_effective_end_date => l_effective_end_date
1954 ,p_object_version_number => l_row.object_version_number
1955 ,p_effective_date => l_effective_date
1956 ,p_datetrack_mode => p_datetrack_mode);
1957 end if;
1958 end loop;
1959 for l_row in BEN_ELIG_JOB_PRTE_F loop
1960 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1961 or p_datetrack_mode <> hr_api.g_delete then
1962 if p_datetrack_mode = hr_api.g_zap then
1963 l_effective_date := l_row.eed;
1964 else l_effective_date := p_effective_date;
1965 end if;
1966 ben_ELIGY_JOB_PRTE_api.delete_ELIGY_JOB_PRTE
1967 (p_validate => false
1968 ,p_elig_job_prte_id => l_row.elig_job_prte_id
1969 ,p_effective_start_date => l_effective_start_date
1970 ,p_effective_end_date => l_effective_end_date
1971 ,p_object_version_number => l_row.object_version_number
1972 ,p_effective_date => l_effective_date
1973 ,p_datetrack_mode => p_datetrack_mode);
1974 end if;
1975 end loop;
1976 for l_row in BEN_ELIG_LBR_MMBR_PRTE_F loop
1977 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1978 or p_datetrack_mode <> hr_api.g_delete then
1979 if p_datetrack_mode = hr_api.g_zap then
1980 l_effective_date := l_row.eed;
1981 else l_effective_date := p_effective_date;
1982 end if;
1983 BEN_ELIG_LBR_MMBR_PRTE_api.delete_ELIG_LBR_MMBR_PRTE
1984 (p_validate => false
1985 ,p_elig_lbr_mmbr_prte_id => l_row.elig_lbr_mmbr_prte_id
1986 ,p_effective_start_date => l_effective_start_date
1987 ,p_effective_end_date => l_effective_end_date
1988 ,p_object_version_number => l_row.object_version_number
1989 ,p_effective_date => l_effective_date
1990 ,p_datetrack_mode => p_datetrack_mode);
1991 end if;
1992 end loop;
1993 for l_row in BEN_ELIG_LGL_ENTY_PRTE_F loop
1994 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
1995 or p_datetrack_mode <> hr_api.g_delete then
1996 if p_datetrack_mode = hr_api.g_zap then
1997 l_effective_date := l_row.eed;
1998 else l_effective_date := p_effective_date;
1999 end if;
2000 BEN_ELIG_LGL_ENTY_PRTE_api.delete_ELIG_LGL_ENTY_PRTE
2001 (p_validate => false
2002 ,p_elig_lgl_enty_prte_id => l_row.elig_lgl_enty_prte_id
2003 ,p_effective_start_date => l_effective_start_date
2004 ,p_effective_end_date => l_effective_end_date
2005 ,p_object_version_number => l_row.object_version_number
2006 ,p_effective_date => l_effective_date
2007 ,p_datetrack_mode => p_datetrack_mode);
2008 end if;
2009 end loop;
2010 for l_row in BEN_ELIG_LOA_RSN_PRTE_F loop
2011 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2012 or p_datetrack_mode <> hr_api.g_delete then
2013 if p_datetrack_mode = hr_api.g_zap then
2014 l_effective_date := l_row.eed;
2015 else l_effective_date := p_effective_date;
2016 end if;
2017 BEN_ELIG_LOA_RSN_PRTE_api.delete_ELIG_LOA_RSN_PRTE
2018 (p_validate => false
2019 ,p_elig_loa_rsn_prte_id => l_row.elig_loa_rsn_prte_id
2020 ,p_effective_start_date => l_effective_start_date
2021 ,p_effective_end_date => l_effective_end_date
2022 ,p_object_version_number => l_row.object_version_number
2023 ,p_effective_date => l_effective_date
2024 ,p_datetrack_mode => p_datetrack_mode);
2025 end if;
2026 end loop;
2027 for l_row in BEN_ELIG_LOS_PRTE_F loop
2028 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2029 or p_datetrack_mode <> hr_api.g_delete then
2030 if p_datetrack_mode = hr_api.g_zap then
2031 l_effective_date := l_row.eed;
2032 else l_effective_date := p_effective_date;
2033 end if;
2034 BEN_ELIG_LOS_PRTE_api.delete_ELIG_LOS_PRTE
2035 (p_validate => false
2036 ,p_elig_los_prte_id => l_row.elig_los_prte_id
2037 ,p_effective_start_date => l_effective_start_date
2038 ,p_effective_end_date => l_effective_end_date
2039 ,p_object_version_number => l_row.object_version_number
2040 ,p_effective_date => l_effective_date
2041 ,p_datetrack_mode => p_datetrack_mode);
2042 end if;
2043 end loop;
2044 for l_row in BEN_ELIG_LVG_RSN_PRTE_F loop
2045 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2046 or p_datetrack_mode <> hr_api.g_delete then
2047 if p_datetrack_mode = hr_api.g_zap then
2048 l_effective_date := l_row.eed;
2049 else l_effective_date := p_effective_date;
2050 end if;
2051 BEN_ELIG_LVG_RSN_PRTE_api.delete_ELIG_LVG_RSN_PRTE
2052 (p_validate => false
2053 ,p_elig_lvg_rsn_prte_id => l_row.elig_lvg_rsn_prte_id
2054 ,p_effective_start_date => l_effective_start_date
2055 ,p_effective_end_date => l_effective_end_date
2056 ,p_object_version_number => l_row.object_version_number
2057 ,p_effective_date => l_effective_date
2058 ,p_datetrack_mode => p_datetrack_mode);
2059 end if;
2060 end loop;
2061 for l_row in BEN_ELIG_NO_OTHR_CVG_PRTE_F loop
2062 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2063 or p_datetrack_mode <> hr_api.g_delete then
2064 if p_datetrack_mode = hr_api.g_zap then
2065 l_effective_date := l_row.eed;
2066 else l_effective_date := p_effective_date;
2067 end if;
2068 BEN_ELIG_NO_OTHR_CVG_PRTE_api.delete_ELIG_NO_OTHR_CVG_PRTE
2069 (p_validate => false
2070 ,p_elig_no_othr_cvg_prte_id => l_row.elig_no_othr_cvg_prte_id
2071 ,p_effective_start_date => l_effective_start_date
2072 ,p_effective_end_date => l_effective_end_date
2073 ,p_object_version_number => l_row.object_version_number
2074 ,p_effective_date => l_effective_date
2075 ,p_datetrack_mode => p_datetrack_mode);
2076 end if;
2077 end loop;
2078 for l_row in BEN_ELIG_OPTD_MDCR_PRTE_F loop
2079 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2080 or p_datetrack_mode <> hr_api.g_delete then
2081 if p_datetrack_mode = hr_api.g_zap then
2082 l_effective_date := l_row.eed;
2083 else l_effective_date := p_effective_date;
2084 end if;
2085 BEN_ELIG_OPTD_MDCR_PRTE_api.delete_ELIG_OPTD_MDCR_PRTE
2086 (p_validate => false
2087 ,p_elig_optd_mdcr_prte_id => l_row.elig_optd_mdcr_prte_id
2088 ,p_effective_start_date => l_effective_start_date
2089 ,p_effective_end_date => l_effective_end_date
2090 ,p_object_version_number => l_row.object_version_number
2091 ,p_effective_date => l_effective_date
2092 ,p_datetrack_mode => p_datetrack_mode);
2093 end if;
2094 end loop;
2095 for l_row in BEN_ELIG_ORG_UNIT_PRTE_F loop
2096 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2097 or p_datetrack_mode <> hr_api.g_delete then
2098 if p_datetrack_mode = hr_api.g_zap then
2099 l_effective_date := l_row.eed;
2100 else l_effective_date := p_effective_date;
2101 end if;
2102 BEN_ELIG_ORG_UNIT_PRTE_api.delete_ELIG_ORG_UNIT_PRTE
2103 (p_validate => false
2104 ,p_elig_org_unit_prte_id => l_row.elig_org_unit_prte_id
2105 ,p_effective_start_date => l_effective_start_date
2106 ,p_effective_end_date => l_effective_end_date
2107 ,p_object_version_number => l_row.object_version_number
2108 ,p_effective_date => l_effective_date
2109 ,p_datetrack_mode => p_datetrack_mode);
2110 end if;
2111 end loop;
2112 for l_row in BEN_ELIG_OTHR_PTIP_PRTE_F loop
2113 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2114 or p_datetrack_mode <> hr_api.g_delete then
2115 if p_datetrack_mode = hr_api.g_zap then
2116 l_effective_date := l_row.eed;
2117 else l_effective_date := p_effective_date;
2118 end if;
2119 BEN_ELIG_OTHR_PTIP_PRTE_api.delete_ELIG_OTHR_PTIP_PRTE
2120 (p_validate => false
2121 ,p_elig_othr_ptip_prte_id => l_row.elig_othr_ptip_prte_id
2122 ,p_effective_start_date => l_effective_start_date
2123 ,p_effective_end_date => l_effective_end_date
2124 ,p_object_version_number => l_row.object_version_number
2125 ,p_effective_date => l_effective_date
2126 ,p_datetrack_mode => p_datetrack_mode);
2127 end if;
2128 end loop;
2129 for l_row in BEN_ELIG_DPNT_OTHR_PTIP_F loop
2130 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2131 or p_datetrack_mode <> hr_api.g_delete then
2132 if p_datetrack_mode = hr_api.g_zap then
2133 l_effective_date := l_row.eed;
2134 else l_effective_date := p_effective_date;
2135 end if;
2136 BEN_ELIG_DPNT_OTHR_PTIP_api.delete_ELIG_DPNT_OTHR_PTIP
2137 (p_validate => false
2138 ,p_elig_dpnt_othr_ptip_id => l_row.elig_dpnt_othr_ptip_id
2139 ,p_effective_start_date => l_effective_start_date
2140 ,p_effective_end_date => l_effective_end_date
2141 ,p_object_version_number => l_row.object_version_number
2142 ,p_effective_date => l_effective_date
2143 ,p_datetrack_mode => p_datetrack_mode);
2144 end if;
2145 end loop;
2146 for l_row in BEN_ELIG_PCT_FL_TM_PRTE_F loop
2147 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2148 or p_datetrack_mode <> hr_api.g_delete then
2149 if p_datetrack_mode = hr_api.g_zap then
2150 l_effective_date := l_row.eed;
2151 else l_effective_date := p_effective_date;
2152 end if;
2153 BEN_ELIG_PCT_FL_TM_PRTE_api.delete_ELIG_PCT_FL_TM_PRTE
2154 (p_validate => false
2155 ,p_elig_pct_fl_tm_prte_id => l_row.elig_pct_fl_tm_prte_id
2156 ,p_effective_start_date => l_effective_start_date
2157 ,p_effective_end_date => l_effective_end_date
2158 ,p_object_version_number => l_row.object_version_number
2159 ,p_effective_date => l_effective_date
2160 ,p_datetrack_mode => p_datetrack_mode);
2161 end if;
2162 end loop;
2163 for l_row in BEN_ELIG_PER_TYP_PRTE_F loop
2164 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2165 or p_datetrack_mode <> hr_api.g_delete then
2166 if p_datetrack_mode = hr_api.g_zap then
2167 l_effective_date := l_row.eed;
2168 else l_effective_date := p_effective_date;
2169 end if;
2170 BEN_ELIG_PER_TYP_PRTE_api.delete_ELIG_PER_TYP_PRTE
2171 (p_validate => false
2172 ,p_elig_per_typ_prte_id => l_row.elig_per_typ_prte_id
2173 ,p_effective_start_date => l_effective_start_date
2174 ,p_effective_end_date => l_effective_end_date
2175 ,p_object_version_number => l_row.object_version_number
2176 ,p_effective_date => l_effective_date
2177 ,p_datetrack_mode => p_datetrack_mode);
2178 end if;
2179 end loop;
2180 for l_row in BEN_ELIG_PPL_GRP_PRTE_F loop
2181 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2182 or p_datetrack_mode <> hr_api.g_delete then
2183 if p_datetrack_mode = hr_api.g_zap then
2184 l_effective_date := l_row.eed;
2185 else l_effective_date := p_effective_date;
2186 end if;
2187 BEN_ELIG_PPL_GRP_PRTE_api.delete_ELIG_PPL_GRP_PRTE
2188 (p_validate => false
2189 ,p_elig_ppl_grp_prte_id => l_row.elig_ppl_grp_prte_id
2190 ,p_effective_start_date => l_effective_start_date
2191 ,p_effective_end_date => l_effective_end_date
2192 ,p_object_version_number => l_row.object_version_number
2193 ,p_effective_date => l_effective_date
2194 ,p_datetrack_mode => p_datetrack_mode);
2195 end if;
2196 end loop;
2197 for l_row in BEN_ELIG_PRTT_ANTHR_PL_PRTE_F loop
2198 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2199 or p_datetrack_mode <> hr_api.g_delete then
2200 if p_datetrack_mode = hr_api.g_zap then
2201 l_effective_date := l_row.eed;
2202 else l_effective_date := p_effective_date;
2203 end if;
2204 ben_ELG_PRT_ANTHR_PL_PT_api.delete_ELG_PRT_ANTHR_PL_PT
2205 (p_validate => false
2206 ,p_elig_prtt_anthr_pl_prte_id => l_row.elig_prtt_anthr_pl_prte_id
2207 ,p_effective_start_date => l_effective_start_date
2208 ,p_effective_end_date => l_effective_end_date
2209 ,p_object_version_number => l_row.object_version_number
2210 ,p_effective_date => l_effective_date
2211 ,p_datetrack_mode => p_datetrack_mode);
2212 end if;
2213 end loop;
2214 for l_row in BEN_ELIG_PSTL_CD_R_RNG_PRTE_F loop
2215 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2216 or p_datetrack_mode <> hr_api.g_delete then
2217 if p_datetrack_mode = hr_api.g_zap then
2218 l_effective_date := l_row.eed;
2219 else l_effective_date := p_effective_date;
2220 end if;
2221 ben_ELIG_PSTL_CD_RNG_PRTE_api.delete_ELIG_PSTL_CD_RNG_PRTE
2222 (p_validate => false
2223 ,p_elig_pstl_cd_r_rng_prte_id => l_row.elig_pstl_cd_r_rng_prte_id
2224 ,p_effective_start_date => l_effective_start_date
2225 ,p_effective_end_date => l_effective_end_date
2226 ,p_object_version_number => l_row.object_version_number
2227 ,p_effective_date => l_effective_date
2228 ,p_datetrack_mode => p_datetrack_mode);
2229 end if;
2230 end loop;
2231 for l_row in BEN_ELIG_PYRL_PRTE_F loop
2232 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2233 or p_datetrack_mode <> hr_api.g_delete then
2234 if p_datetrack_mode = hr_api.g_zap then
2235 l_effective_date := l_row.eed;
2236 else l_effective_date := p_effective_date;
2237 end if;
2238 BEN_ELIG_PYRL_PRTE_api.delete_ELIG_PYRL_PRTE
2239 (p_validate => false
2240 ,p_elig_pyrl_prte_id => l_row.elig_pyrl_prte_id
2241 ,p_effective_start_date => l_effective_start_date
2242 ,p_effective_end_date => l_effective_end_date
2243 ,p_object_version_number => l_row.object_version_number
2244 ,p_effective_date => l_effective_date
2245 ,p_datetrack_mode => p_datetrack_mode);
2246 end if;
2247 end loop;
2248 for l_row in BEN_ELIG_PY_BSS_PRTE_F loop
2249 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2250 or p_datetrack_mode <> hr_api.g_delete then
2251 if p_datetrack_mode = hr_api.g_zap then
2252 l_effective_date := l_row.eed;
2253 else l_effective_date := p_effective_date;
2254 end if;
2255 BEN_ELIG_PY_BSS_PRTE_api.delete_ELIG_PY_BSS_PRTE
2256 (p_validate => false
2257 ,p_elig_py_bss_prte_id => l_row.elig_py_bss_prte_id
2258 ,p_effective_start_date => l_effective_start_date
2259 ,p_effective_end_date => l_effective_end_date
2260 ,p_object_version_number => l_row.object_version_number
2261 ,p_effective_date => l_effective_date
2262 ,p_datetrack_mode => p_datetrack_mode);
2263 end if;
2264 end loop;
2265 for l_row in BEN_ELIG_SCHEDD_HRS_PRTE_F loop
2266 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2267 or p_datetrack_mode <> hr_api.g_delete then
2268 if p_datetrack_mode = hr_api.g_zap then
2269 l_effective_date := l_row.eed;
2270 else l_effective_date := p_effective_date;
2271 end if;
2272 BEN_ELIG_SCHEDD_HRS_PRTE_api.delete_ELIG_SCHEDD_HRS_PRTE
2273 (p_validate => false
2274 ,p_elig_schedd_hrs_prte_id => l_row.elig_schedd_hrs_prte_id
2275 ,p_effective_start_date => l_effective_start_date
2276 ,p_effective_end_date => l_effective_end_date
2277 ,p_object_version_number => l_row.object_version_number
2278 ,p_effective_date => l_effective_date
2279 ,p_datetrack_mode => p_datetrack_mode);
2280 end if;
2281 end loop;
2282 for l_row in BEN_ELIG_SVC_AREA_PRTE_F loop
2283 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2284 or p_datetrack_mode <> hr_api.g_delete then
2285 if p_datetrack_mode = hr_api.g_zap then
2286 l_effective_date := l_row.eed;
2287 else l_effective_date := p_effective_date;
2288 end if;
2289 BEN_ELIG_SVC_AREA_PRTE_api.delete_ELIG_SVC_AREA_PRTE
2290 (p_validate => false
2291 ,p_elig_svc_area_prte_id => l_row.elig_svc_area_prte_id
2292 ,p_effective_start_date => l_effective_start_date
2293 ,p_effective_end_date => l_effective_end_date
2294 ,p_object_version_number => l_row.object_version_number
2295 ,p_effective_date => l_effective_date
2296 ,p_datetrack_mode => p_datetrack_mode);
2297 end if;
2298 end loop;
2299 for l_row in BEN_ELIG_WK_LOC_PRTE_F loop
2300 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2301 or p_datetrack_mode <> hr_api.g_delete then
2302 if p_datetrack_mode = hr_api.g_zap then
2303 l_effective_date := l_row.eed;
2304 else l_effective_date := p_effective_date;
2305 end if;
2306 BEN_ELIG_WK_LOC_PRTE_api.delete_ELIG_WK_LOC_PRTE
2307 (p_validate => false
2308 ,p_elig_wk_loc_prte_id => l_row.elig_wk_loc_prte_id
2309 ,p_effective_start_date => l_effective_start_date
2310 ,p_effective_end_date => l_effective_end_date
2311 ,p_object_version_number => l_row.object_version_number
2312 ,p_effective_date => l_effective_date
2313 ,p_datetrack_mode => p_datetrack_mode);
2314 end if;
2315 end loop;
2316 for l_row in BEN_PRTN_ELIG_PRFL_F loop
2317 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2318 or p_datetrack_mode <> hr_api.g_delete then
2319 if p_datetrack_mode = hr_api.g_zap then
2320 l_effective_date := l_row.eed;
2321 else l_effective_date := p_effective_date;
2322 end if;
2323 BEN_PRTN_ELIG_PRFL_api.delete_PRTN_ELIG_PRFL
2324 (p_validate => false
2325 ,p_prtn_elig_prfl_id => l_row.prtn_elig_prfl_id
2326 ,p_effective_start_date => l_effective_start_date
2327 ,p_effective_end_date => l_effective_end_date
2328 ,p_object_version_number => l_row.object_version_number
2329 ,p_effective_date => l_effective_date
2330 ,p_datetrack_mode => p_datetrack_mode);
2331 end if;
2332 end loop;
2333
2334 for l_row in BEN_CNTNG_PRTN_ELIG_PRFL_F loop
2335 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2336 or p_datetrack_mode <> hr_api.g_delete then
2337 if p_datetrack_mode = hr_api.g_zap then
2338 l_effective_date := l_row.eed;
2339 else l_effective_date := p_effective_date;
2340 end if;
2341 BEN_CNTNG_PRTN_ELIG_PRFL_api.delete_CNTNG_PRTN_ELIG_PRFL
2342 (p_validate => false
2343 ,p_cntng_prtn_elig_prfl_id => l_row.cntng_prtn_elig_prfl_id
2344 ,p_effective_start_date => l_effective_start_date
2345 ,p_effective_end_date => l_effective_end_date
2346 ,p_object_version_number => l_row.object_version_number
2347 ,p_effective_date => l_effective_date
2348 ,p_datetrack_mode => p_datetrack_mode);
2349 end if;
2350 end loop;
2351
2352
2353 for l_row in BEN_ELIG_GNDR_PRTE_F loop
2354 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2355 or p_datetrack_mode <> hr_api.g_delete then
2356 if p_datetrack_mode = hr_api.g_zap then
2357 l_effective_date := l_row.eed;
2358 else
2359 l_effective_date := p_effective_date;
2360 end if;
2361 BEN_ELIG_GNDR_PRTE_api.delete_ELIG_GNDR_PRTE
2362 (p_validate => false
2363 ,p_ELIG_GNDR_PRTE_ID => l_row.ELIG_GNDR_PRTE_ID
2364 ,p_effective_start_date => l_effective_start_date
2365 ,p_effective_end_date => l_effective_end_date
2366 ,p_object_version_number => l_row.object_version_number
2367 ,p_effective_date => p_effective_date
2368 ,p_datetrack_mode => p_datetrack_mode);
2369 end if;
2370 end loop;
2371
2372
2373 for l_row in BEN_ELIG_MRTL_STS_PRTE_F loop
2374 BEN_ELIG_MRTL_STS_PRTE_api.delete_ELIG_MRTL_STS_PRTE
2375 (p_validate => false
2376 ,p_ELIG_MRTL_STS_PRTE_ID => l_row.ELIG_MRTL_STS_PRTE_ID
2377 ,p_effective_start_date => l_effective_start_date
2378 ,p_effective_end_date => l_effective_end_date
2379 ,p_object_version_number => l_row.object_version_number
2380 ,p_effective_date => p_effective_date
2381 ,p_datetrack_mode => p_datetrack_mode);
2382 end loop;
2383
2384 for l_row in BEN_ELIG_DSBLTY_CTG_PRTE_F loop
2385 BEN_ELIG_DSBLTY_CTG_PRTE_api.delete_ELIG_DSBLTY_CTG_PRTE
2386 (p_validate => false
2387 ,p_ELIG_DSBLTY_CTG_PRTE_ID => l_row.ELIG_DSBLTY_CTG_PRTE_ID
2388 ,p_effective_start_date => l_effective_start_date
2389 ,p_effective_end_date => l_effective_end_date
2390 ,p_object_version_number => l_row.object_version_number
2391 ,p_effective_date => p_effective_date
2392 ,p_datetrack_mode => p_datetrack_mode);
2393 end loop;
2394
2395
2396 for l_row in BEN_ELIG_DSBLTY_RSN_PRTE_F loop
2397 BEN_ELIG_DSBLTY_RSN_PRTE_api.delete_ELIG_DSBLTY_RSN_PRTE
2398 (p_validate => false
2399 ,p_ELIG_DSBLTY_RSN_PRTE_ID => l_row.ELIG_DSBLTY_RSN_PRTE_ID
2400 ,p_effective_start_date => l_effective_start_date
2401 ,p_effective_end_date => l_effective_end_date
2402 ,p_object_version_number => l_row.object_version_number
2403 ,p_effective_date => p_effective_date
2404 ,p_datetrack_mode => p_datetrack_mode);
2405 end loop;
2406
2407 for l_row in BEN_ELIG_DSBLTY_DGR_PRTE_F loop
2408 BEN_ELIG_DSBLTY_DGR_PRTE_api.delete_ELIG_DSBLTY_DGR_PRTE
2409 (p_validate => false
2410 ,p_ELIG_DSBLTY_DGR_PRTE_ID => l_row.ELIG_DSBLTY_DGR_PRTE_ID
2411 ,p_effective_start_date => l_effective_start_date
2412 ,p_effective_end_date => l_effective_end_date
2413 ,p_object_version_number => l_row.object_version_number
2414 ,p_effective_date => p_effective_date
2415 ,p_datetrack_mode => p_datetrack_mode);
2416 end loop;
2417
2418
2419 for l_row in BEN_ELIG_DSBLTY_DGR_PRTE_F loop
2420 BEN_ELIG_DSBLTY_DGR_PRTE_api.delete_ELIG_DSBLTY_DGR_PRTE
2421 (p_validate => false
2422 ,p_ELIG_DSBLTY_DGR_PRTE_ID => l_row.ELIG_DSBLTY_DGR_PRTE_ID
2423 ,p_effective_start_date => l_effective_start_date
2424 ,p_effective_end_date => l_effective_end_date
2425 ,p_object_version_number => l_row.object_version_number
2426 ,p_effective_date => p_effective_date
2427 ,p_datetrack_mode => p_datetrack_mode);
2428 end loop;
2429
2430 for l_row in BEN_ELIG_Suppl_role_prte_f loop
2431 BEN_ELIG_Suppl_role_prte_api.delete_ELIG_Suppl_role_prte
2432 (p_validate => false
2433 ,p_ELIG_Suppl_role_prte_ID => l_row.ELIG_Suppl_role_prte_ID
2434 ,p_effective_start_date => l_effective_start_date
2435 ,p_effective_end_date => l_effective_end_date
2436 ,p_object_version_number => l_row.object_version_number
2437 ,p_effective_date => p_effective_date
2438 ,p_datetrack_mode => p_datetrack_mode);
2439 end loop;
2440
2441
2442 for l_row in BEN_ELIG_qual_titl_prte_f loop
2443 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2444 or p_datetrack_mode <> hr_api.g_delete then
2445 if p_datetrack_mode = hr_api.g_zap then
2446 l_effective_date := l_row.eed;
2447 else
2448 l_effective_date := p_effective_date;
2449 end if;
2450 BEN_ELIG_qual_titl_prte_api.delete_ELIG_qual_titl_prte
2451 (p_validate => false
2452 ,p_ELIG_qual_titl_prte_ID => l_row.ELIG_qual_titl_prte_ID
2453 ,p_effective_start_date => l_effective_start_date
2454 ,p_effective_end_date => l_effective_end_date
2455 ,p_object_version_number => l_row.object_version_number
2456 ,p_effective_date => p_effective_date
2457 ,p_datetrack_mode => p_datetrack_mode);
2458 end if;
2459 end loop;
2460
2461 for l_row in BEN_ELIG_pstn_prte_f loop
2462 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2463 or p_datetrack_mode <> hr_api.g_delete then
2464 if p_datetrack_mode = hr_api.g_zap then
2465 l_effective_date := l_row.eed;
2466 else
2467 l_effective_date := p_effective_date;
2468 end if;
2469 BEN_ELIG_pstn_prte_api.delete_ELIG_pstn_prte
2470 (p_validate => false
2471 ,p_ELIG_pstn_prte_ID => l_row.ELIG_pstn_prte_ID
2472 ,p_effective_start_date => l_effective_start_date
2473 ,p_effective_end_date => l_effective_end_date
2474 ,p_object_version_number => l_row.object_version_number
2475 ,p_effective_date => p_effective_date
2476 ,p_datetrack_mode => p_datetrack_mode);
2477 end if;
2478 end loop;
2479
2480 for l_row in BEN_ELIG_prbtn_perd_prte_f loop
2481 BEN_ELIG_prbtn_perd_prte_api.delete_ELIG_prbtn_perd_prte
2482 (p_validate => false
2483 ,p_ELIG_prbtn_perd_prte_ID => l_row.ELIG_prbtn_perd_prte_ID
2484 ,p_effective_start_date => l_effective_start_date
2485 ,p_effective_end_date => l_effective_end_date
2486 ,p_object_version_number => l_row.object_version_number
2487 ,p_effective_date => p_effective_date
2488 ,p_datetrack_mode => p_datetrack_mode);
2489 end loop;
2490
2491 for l_row in BEN_ELIG_sp_clng_prg_prte_f loop
2492 BEN_ELIG_sp_clng_prg_prte_api.delete_ELIG_sp_clng_prg_prte
2493 (p_validate => false
2494 ,p_ELIG_sp_clng_prg_prte_ID => l_row.ELIG_sp_clng_prg_prte_ID
2495 ,p_effective_start_date => l_effective_start_date
2496 ,p_effective_end_date => l_effective_end_date
2497 ,p_object_version_number => l_row.object_version_number
2498 ,p_effective_date => p_effective_date
2499 ,p_datetrack_mode => p_datetrack_mode);
2500 end loop;
2501
2502 for l_row in BEN_ELIG_DSBLD_PRTE_F loop
2503 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2504 or p_datetrack_mode <> hr_api.g_delete then
2505 if p_datetrack_mode = hr_api.g_zap then
2506 l_effective_date := l_row.eed;
2507 else
2508 l_effective_date := p_effective_date;
2509 end if;
2510 BEN_ELIG_DSBLD_PRTE_api.delete_ELIG_DSBLD_PRTE
2511 (p_validate => false
2512 ,p_ELIG_DSBLD_PRTE_ID => l_row.ELIG_DSBLD_PRTE_ID
2513 ,p_effective_start_date => l_effective_start_date
2514 ,p_effective_end_date => l_effective_end_date
2515 ,p_object_version_number => l_row.object_version_number
2516 ,p_effective_date => p_effective_date
2517 ,p_datetrack_mode => p_datetrack_mode);
2518 end if;
2519 end loop;
2520
2521 for l_row in BEN_ELIG_TTL_CVG_VOL_PRTE_F loop
2522 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2523 or p_datetrack_mode <> hr_api.g_delete then
2524 if p_datetrack_mode = hr_api.g_zap then
2525 l_effective_date := l_row.eed;
2526 else
2527 l_effective_date := p_effective_date;
2528 end if;
2529 BEN_ELIG_TTL_CVG_VOL_PRTE_api.delete_ELIG_TTL_CVG_VOL_PRTE
2530 (p_validate => false
2531 ,p_ELIG_TTL_CVG_VOL_PRTE_ID => l_row.ELIG_TTL_CVG_VOL_PRTE_ID
2532 ,p_effective_start_date => l_effective_start_date
2533 ,p_effective_end_date => l_effective_end_date
2534 ,p_object_version_number => l_row.object_version_number
2535 ,p_effective_date => p_effective_date
2536 ,p_datetrack_mode => p_datetrack_mode);
2537 end if;
2538 end loop;
2539
2540 for l_row in BEN_ELIG_TTL_PRTT_PRTE_F loop
2541 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2542 or p_datetrack_mode <> hr_api.g_delete then
2543 if p_datetrack_mode = hr_api.g_zap then
2544 l_effective_date := l_row.eed;
2545 else
2546 l_effective_date := p_effective_date;
2547 end if;
2548 BEN_ELIG_TTL_PRTT_PRTE_api.delete_ELIG_TTL_PRTT_PRTE
2549 (p_validate => false
2550 ,p_ELIG_TTL_PRTT_PRTE_ID => l_row.ELIG_TTL_PRTT_PRTE_ID
2551 ,p_effective_start_date => l_effective_start_date
2552 ,p_effective_end_date => l_effective_end_date
2553 ,p_object_version_number => l_row.object_version_number
2554 ,p_effective_date => p_effective_date
2555 ,p_datetrack_mode => p_datetrack_mode);
2556 end if;
2557 end loop;
2558
2559 for l_row in BEN_ELIG_ANTHR_PL_PRTE_F loop
2560 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2561 or p_datetrack_mode <> hr_api.g_delete then
2562 if p_datetrack_mode = hr_api.g_zap then
2563 l_effective_date := l_row.eed;
2564 else
2565 l_effective_date := p_effective_date;
2566 end if;
2567 BEN_ELIG_ANTHR_PL_PRTE_api.delete_ELIG_ANTHR_PL_PRTE
2568 (p_validate => false
2569 ,p_ELIG_ANTHR_PL_PRTE_ID => l_row.ELIG_ANTHR_PL_PRTE_ID
2570 ,p_effective_start_date => l_effective_start_date
2571 ,p_effective_end_date => l_effective_end_date
2572 ,p_object_version_number => l_row.object_version_number
2573 ,p_effective_date => p_effective_date
2574 ,p_datetrack_mode => p_datetrack_mode);
2575 end if;
2576 end loop;
2577
2578 for l_row in BEN_ELIG_HLTH_CVG_PRTE_F loop
2579 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2580 or p_datetrack_mode <> hr_api.g_delete then
2581 if p_datetrack_mode = hr_api.g_zap then
2582 l_effective_date := l_row.eed;
2583 else
2584 l_effective_date := p_effective_date;
2585 end if;
2586 BEN_ELIG_HLTH_CVG_PRTE_api.delete_ELIG_HLTH_CVG_PRTE
2587 (p_validate => false
2588 ,p_ELIG_HLTH_CVG_PRTE_ID => l_row.ELIG_HLTH_CVG_PRTE_ID
2589 ,p_effective_start_date => l_effective_start_date
2590 ,p_effective_end_date => l_effective_end_date
2591 ,p_object_version_number => l_row.object_version_number
2592 ,p_effective_date => p_effective_date
2593 ,p_datetrack_mode => p_datetrack_mode);
2594 end if;
2595 end loop;
2596
2597 for l_row in BEN_ELIG_COMPTNCY_PRTE_F loop
2598 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2599 or p_datetrack_mode <> hr_api.g_delete then
2600 if p_datetrack_mode = hr_api.g_zap then
2601 l_effective_date := l_row.eed;
2602 else
2603 l_effective_date := p_effective_date;
2604 end if;
2605 BEN_ELIG_COMPTNCY_PRTE_api.delete_ELIG_COMPTNCY_PRTE
2606 (p_validate => false
2607 ,p_ELIG_COMPTNCY_PRTE_ID => l_row.ELIG_COMPTNCY_PRTE_ID
2608 ,p_effective_start_date => l_effective_start_date
2609 ,p_effective_end_date => l_effective_end_date
2610 ,p_object_version_number => l_row.object_version_number
2611 ,p_effective_date => p_effective_date
2612 ,p_datetrack_mode => p_datetrack_mode);
2613 end if;
2614 end loop;
2615
2616 for l_row in BEN_ELIG_QUA_IN_GR_PRTE_F loop
2617 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2618 or p_datetrack_mode <> hr_api.g_delete then
2619 if p_datetrack_mode = hr_api.g_zap then
2620 l_effective_date := l_row.eed;
2621 else
2622 l_effective_date := p_effective_date;
2623 end if;
2624 BEN_ELIG_QUA_IN_GR_PRTE_api.delete_ELIG_QUA_IN_GR_PRTE
2625 (p_validate => false
2626 ,p_ELIG_QUA_IN_GR_PRTE_ID => l_row.ELIG_QUA_IN_GR_PRTE_ID
2627 ,p_effective_start_date => l_effective_start_date
2628 ,p_effective_end_date => l_effective_end_date
2629 ,p_object_version_number => l_row.object_version_number
2630 ,p_effective_date => p_effective_date
2631 ,p_datetrack_mode => p_datetrack_mode);
2632 end if;
2633 end loop;
2634
2635 for l_row in BEN_ELIG_PERF_RTNG_PRTE_F loop
2636 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2637 or p_datetrack_mode <> hr_api.g_delete then
2638 if p_datetrack_mode = hr_api.g_zap then
2639 l_effective_date := l_row.eed;
2640 else
2641 l_effective_date := p_effective_date;
2642 end if;
2643 BEN_ELIG_PERF_RTNG_PRTE_api.delete_ELIG_PERF_RTNG_PRTE
2644 (p_validate => false
2645 ,p_ELIG_PERF_RTNG_PRTE_ID => l_row.ELIG_PERF_RTNG_PRTE_ID
2646 ,p_effective_start_date => l_effective_start_date
2647 ,p_effective_end_date => l_effective_end_date
2648 ,p_object_version_number => l_row.object_version_number
2649 ,p_effective_date => p_effective_date
2650 ,p_datetrack_mode => p_datetrack_mode);
2651 end if;
2652 end loop;
2653
2654 --
2655 -- Bug No 4411798
2656 --
2657 for l_row in BEN_ELIGY_CRIT_VALUES_F loop
2658 if (p_datetrack_mode = hr_api.g_delete and l_row.eed > p_effective_date)
2659 or p_datetrack_mode <> hr_api.g_delete then
2660 if p_datetrack_mode = hr_api.g_zap then
2661 l_effective_date := l_row.eed;
2662 else
2663 l_effective_date := p_effective_date;
2664 end if;
2665 ben_eligy_crit_values_api.delete_eligy_crit_values
2666 (p_validate => false
2667 ,p_eligy_crit_values_id => l_row.eligy_crit_values_id
2668 ,p_effective_start_date => l_effective_start_date
2669 ,p_effective_end_date => l_effective_end_date
2670 ,p_object_version_number => l_row.object_version_number
2671 ,p_effective_date => p_effective_date
2672 ,p_datetrack_mode => p_datetrack_mode);
2673 end if;
2674 end loop;
2675 --
2676 -- End of Bug No 4411798
2677 --
2678
2679 end delete_children;
2680 -- ----------------------------------------------------------------------------
2681 -- |------------------------< delete_ELIGY_PROFILE >--------------------------|
2682 -- ----------------------------------------------------------------------------
2683 --
2684 procedure delete_ELIGY_PROFILE
2685 (p_validate in boolean default false
2686 ,p_eligy_prfl_id in number
2687 ,p_effective_start_date out nocopy date
2688 ,p_effective_end_date out nocopy date
2689 ,p_object_version_number in out nocopy number
2690 ,p_effective_date in date
2691 ,p_datetrack_mode in varchar2) is
2692 --
2693 -- Declare cursors and local variables
2694 --
2695 l_proc varchar2(72) := g_package||'update_ELIGY_PROFILE';
2696 l_object_version_number ben_eligy_prfl_f.object_version_number%TYPE;
2697 l_effective_start_date ben_eligy_prfl_f.effective_start_date%TYPE;
2698 l_effective_end_date ben_eligy_prfl_f.effective_end_date%TYPE;
2699 --
2700 begin
2701 --
2702 hr_utility.set_location('Entering:'|| l_proc, 10);
2703 --
2704 -- Issue a savepoint if operating in validation only mode
2705 --
2706 savepoint delete_ELIGY_PROFILE;
2707 --
2708 hr_utility.set_location(l_proc, 20);
2709 --
2710 -- Process Logic
2711 --
2712 l_object_version_number := p_object_version_number;
2713 --
2714 --
2715 begin
2716 --
2717 -- Start of API User Hook for the before hook of delete_ELIGY_PROFILE
2718 --
2719 ben_ELIGY_PROFILE_bk3.delete_ELIGY_PROFILE_b
2720 (p_eligy_prfl_id => p_eligy_prfl_id
2721 ,p_object_version_number => p_object_version_number
2722 ,p_effective_date => trunc(p_effective_date)
2723 ,p_datetrack_mode => p_datetrack_mode);
2724 --
2725 exception
2726 --
2727 when hr_api.cannot_find_prog_unit then
2728 --
2729 hr_api.cannot_find_prog_unit_error
2730 (p_module_name => 'DELETE_ELIGY_PROFILE'
2731 ,p_hook_type => 'BP');
2732 --
2733 -- End of API User Hook for the before hook of delete_ELIGY_PROFILE
2734 --
2735 end;
2736
2737 delete_children
2738 (p_eligy_prfl_id => p_eligy_prfl_id
2739 ,p_effective_date => p_effective_date
2740 ,p_datetrack_mode => p_datetrack_mode);
2741
2742 ben_elp_del.del
2743 (p_eligy_prfl_id => p_eligy_prfl_id
2744 ,p_effective_start_date => l_effective_start_date
2745 ,p_effective_end_date => l_effective_end_date
2746 ,p_object_version_number => l_object_version_number
2747 ,p_effective_date => p_effective_date
2748 ,p_datetrack_mode => p_datetrack_mode);
2749
2750 begin
2751 --
2752 -- Start of API User Hook for the after hook of delete_ELIGY_PROFILE
2753 --
2754 ben_ELIGY_PROFILE_bk3.delete_ELIGY_PROFILE_a
2755 (p_eligy_prfl_id => p_eligy_prfl_id
2756 ,p_effective_start_date => l_effective_start_date
2757 ,p_effective_end_date => l_effective_end_date
2758 ,p_object_version_number => l_object_version_number
2759 ,p_effective_date => trunc(p_effective_date)
2760 ,p_datetrack_mode => p_datetrack_mode);
2761 --
2762 exception
2763 --
2764 when hr_api.cannot_find_prog_unit then
2765 --
2766 hr_api.cannot_find_prog_unit_error
2767 (p_module_name => 'DELETE_ELIGY_PROFILE'
2768 ,p_hook_type => 'AP');
2769 --
2770 -- End of API User Hook for the after hook of delete_ELIGY_PROFILE
2771 --
2772 end;
2773 --
2774 hr_utility.set_location(l_proc, 60);
2775 --
2776 -- When in validation only mode raise the Validate_Enabled exception
2777 --
2778 if p_validate then
2779 raise hr_api.validate_enabled;
2780 end if;
2781 --
2782 hr_utility.set_location(' Leaving:'||l_proc, 70);
2783 --
2784 exception
2785 --
2786 when hr_api.validate_enabled then
2787 --
2788 -- As the Validate_Enabled exception has been raised
2789 -- we must rollback to the savepoint
2790 --
2791 ROLLBACK TO delete_ELIGY_PROFILE;
2792 --
2793 -- Only set output warning arguments
2794 -- (Any key or derived arguments must be set to null
2795 -- when validation only mode is being used.)
2796 --
2797 p_effective_start_date := null;
2798 p_effective_end_date := null;
2799 --
2800 when others then
2801 --
2802 -- NOCOPY
2803 p_effective_start_date := null;
2804 p_effective_end_date := null;
2805 p_object_version_number := l_object_version_number;
2806 -- A validation or unexpected error has occured
2807 --
2808 ROLLBACK TO delete_ELIGY_PROFILE;
2809 raise;
2810 --
2811 end delete_ELIGY_PROFILE;
2812 --
2813 -- ----------------------------------------------------------------------------
2814 -- |-------------------------------< lck >------------------------------------|
2815 -- ----------------------------------------------------------------------------
2816 --
2817 procedure lck
2818 (p_eligy_prfl_id in number
2819 ,p_object_version_number in number
2820 ,p_effective_date in date
2821 ,p_datetrack_mode in varchar2
2822 ,p_validation_start_date out nocopy date
2823 ,p_validation_end_date out nocopy date) is
2824 --
2825 --
2826 -- Declare cursors and local variables
2827 --
2828 l_proc varchar2(72) := g_package||'lck';
2829 l_validation_start_date date;
2830 l_validation_end_date date;
2831 --
2832 begin
2833 --
2834 hr_utility.set_location('Entering:'|| l_proc, 10);
2835 --
2836 ben_elp_shd.lck
2837 (p_eligy_prfl_id => p_eligy_prfl_id
2838 ,p_validation_start_date => l_validation_start_date
2839 ,p_validation_end_date => l_validation_end_date
2840 ,p_object_version_number => p_object_version_number
2841 ,p_effective_date => p_effective_date
2842 ,p_datetrack_mode => p_datetrack_mode);
2843 --
2844 hr_utility.set_location(' Leaving:'||l_proc, 70);
2845 --
2846 end lck;
2847 --
2848 end ben_ELIGY_PROFILE_api;