[Home] [Help]
PACKAGE BODY: APPS.BEN_OPTION_IN_PLAN_API
Source
1 Package Body ben_Option_in_Plan_api as
2 /* $Header: becopapi.pkb 120.0.12010000.2 2008/08/05 14:18:26 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_Option_in_Plan_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_Option_in_Plan >-------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_Option_in_Plan
13 (p_validate in boolean default false
14 ,p_oipl_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_ivr_ident in varchar2 default null
18 ,p_url_ref_name in varchar2 default null
19 ,p_opt_id in number default null
20 ,p_business_group_id in number default null
21 ,p_pl_id in number default null
22 ,p_ordr_num in number default null
23 ,p_rqd_perd_enrt_nenrt_val in number default null
24 ,p_dflt_flag in varchar2 default 'N'
25 ,p_actl_prem_id in number default null
26 ,p_mndtry_flag in varchar2 default 'N'
27 ,p_oipl_stat_cd in varchar2 default null
28 ,p_pcp_dsgn_cd in varchar2 default null
29 ,p_pcp_dpnt_dsgn_cd in varchar2 default null
30 ,p_rqd_perd_enrt_nenrt_uom in varchar2 default null
31 ,p_elig_apls_flag in varchar2 default 'N'
32 ,p_dflt_enrt_det_rl in number default null
33 ,p_trk_inelig_per_flag in varchar2 default 'N'
34 ,p_drvbl_fctr_prtn_elig_flag in varchar2 default 'N'
35 ,p_mndtry_rl in number default null
36 ,p_rqd_perd_enrt_nenrt_rl in number default null
37 ,p_dflt_enrt_cd in varchar2 default null
38 ,p_prtn_elig_ovrid_alwd_flag in varchar2 default 'N'
39 ,p_drvbl_fctr_apls_rts_flag in varchar2 default 'N'
40 ,p_per_cvrd_cd in varchar2 default null
41 ,p_postelcn_edit_rl in number default null
42 ,p_vrfy_fmly_mmbr_cd in varchar2 default null
43 ,p_vrfy_fmly_mmbr_rl in number default null
44 ,p_enrt_cd in varchar2 default null
45 ,p_enrt_rl in number default null
46 ,p_auto_enrt_flag in varchar2 default null
47 ,p_auto_enrt_mthd_rl in number default null
48 ,p_short_name in varchar2 default null /*FHR*/
49 ,p_short_code in varchar2 default null /*FHR*/
50 ,p_legislation_code in varchar2 default null /*FHR*/
51 ,p_legislation_subgroup in varchar2 default null /*FHR*/
52 ,p_hidden_flag in varchar2 default 'N'
53 ,p_susp_if_ctfn_not_prvd_flag in varchar2 default 'Y'
54 ,p_ctfn_determine_cd in varchar2 default null
55 ,p_cop_attribute_category in varchar2 default null
56 ,p_cop_attribute1 in varchar2 default null
57 ,p_cop_attribute2 in varchar2 default null
58 ,p_cop_attribute3 in varchar2 default null
59 ,p_cop_attribute4 in varchar2 default null
60 ,p_cop_attribute5 in varchar2 default null
61 ,p_cop_attribute6 in varchar2 default null
62 ,p_cop_attribute7 in varchar2 default null
63 ,p_cop_attribute8 in varchar2 default null
64 ,p_cop_attribute9 in varchar2 default null
65 ,p_cop_attribute10 in varchar2 default null
66 ,p_cop_attribute11 in varchar2 default null
67 ,p_cop_attribute12 in varchar2 default null
68 ,p_cop_attribute13 in varchar2 default null
69 ,p_cop_attribute14 in varchar2 default null
70 ,p_cop_attribute15 in varchar2 default null
71 ,p_cop_attribute16 in varchar2 default null
72 ,p_cop_attribute17 in varchar2 default null
73 ,p_cop_attribute18 in varchar2 default null
74 ,p_cop_attribute19 in varchar2 default null
75 ,p_cop_attribute20 in varchar2 default null
76 ,p_cop_attribute21 in varchar2 default null
77 ,p_cop_attribute22 in varchar2 default null
78 ,p_cop_attribute23 in varchar2 default null
79 ,p_cop_attribute24 in varchar2 default null
80 ,p_cop_attribute25 in varchar2 default null
81 ,p_cop_attribute26 in varchar2 default null
82 ,p_cop_attribute27 in varchar2 default null
83 ,p_cop_attribute28 in varchar2 default null
84 ,p_cop_attribute29 in varchar2 default null
85 ,p_cop_attribute30 in varchar2 default null
86 ,p_object_version_number out nocopy number
87 ,p_effective_date in date) is
88 --
89 -- Declare cursors and local variables
90 --
91 cursor c_cpp is
92 select cpp.plip_id
93 from ben_plip_f cpp
94 where cpp.pl_id = p_pl_id
95 and cpp.business_group_id + 0 = p_business_group_id
96 and p_effective_date between
97 cpp.effective_start_date and cpp.effective_end_date;
98 --
99 cursor c_otp is
100 select
101 plip.pgm_id,
102 pl.pl_typ_id,
103 ptip.ptip_id
104 from
105 ben_ptip_f ptip,
106 ben_plip_f plip,
107 ben_pl_f pl
108 where
109 pl.pl_id = p_pl_id
110 and plip.pl_id = pl.pl_id
111 and pl.business_group_id = p_business_group_id
112 and plip.business_group_id = p_business_group_id
113 and plip.pgm_id = ptip.pgm_id
114 and pl.pl_typ_id = ptip.pl_typ_id
115 and p_effective_date between plip.effective_start_date and plip.effective_end_date
116 and p_effective_date between pl.effective_start_date and pl.effective_end_date
117 and p_effective_date between ptip.effective_start_date and ptip.effective_end_date ;
118
119 l_oipl_id ben_oipl_f.oipl_id%TYPE;
120 l_effective_start_date ben_oipl_f.effective_start_date%TYPE;
121 l_effective_end_date ben_oipl_f.effective_end_date%TYPE;
122 l_proc varchar2(72) := g_package||
123 'create_Option_in_Plan';
124 l_object_version_number ben_oipl_f.object_version_number%TYPE;
125 --
126 l_oiplip_id ben_oiplip_f.oiplip_id%type;
127 l_opp_effective_start_date ben_oiplip_f.effective_start_date%type;
128 l_opp_effective_end_date ben_oiplip_f.effective_end_date%type;
129 l_opp_object_version_number ben_oiplip_f.object_version_number%type;
130 -- ben_optip_f
131 l_optip_id ben_optip_f.optip_id%type;
132 l_otp_effective_start_date ben_optip_f.effective_start_date%type;
133 l_otp_effective_end_date ben_optip_f.effective_end_date%type;
134 l_otp_object_version_number ben_optip_f.object_version_number%type;
135 --
136 begin
137 --
138 hr_utility.set_location('Entering:'|| l_proc, 10);
139 --
140 -- Issue a savepoint if operating in validation only mode
141 --
142 savepoint create_Option_in_Plan;
143 --
144 hr_utility.set_location(l_proc, 20);
145 --
146 -- Process Logic
147 --
148 begin
149 --
150 -- Start of API User Hook for the before hook of create_Option_in_Plan
151 --
152 ben_Option_in_Plan_bk1.create_Option_in_Plan_b
153 (p_ivr_ident => p_ivr_ident
154 ,p_url_ref_name => p_url_ref_name
155 ,p_opt_id => p_opt_id
156 ,p_business_group_id => p_business_group_id
157 ,p_pl_id => p_pl_id
158 ,p_ordr_num => p_ordr_num
159 ,p_rqd_perd_enrt_nenrt_val => p_rqd_perd_enrt_nenrt_val
160 ,p_dflt_flag => p_dflt_flag
161 ,p_actl_prem_id => p_actl_prem_id
162 ,p_mndtry_flag => p_mndtry_flag
163 ,p_oipl_stat_cd => p_oipl_stat_cd
164 ,p_pcp_dsgn_cd => p_pcp_dsgn_cd
165 ,p_pcp_dpnt_dsgn_cd => p_pcp_dpnt_dsgn_cd
166 ,p_rqd_perd_enrt_nenrt_uom => p_rqd_perd_enrt_nenrt_uom
167 ,p_elig_apls_flag => p_elig_apls_flag
168 ,p_dflt_enrt_det_rl => p_dflt_enrt_det_rl
169 ,p_trk_inelig_per_flag => p_trk_inelig_per_flag
170 ,p_drvbl_fctr_prtn_elig_flag => p_drvbl_fctr_prtn_elig_flag
171 ,p_mndtry_rl => p_mndtry_rl
172 ,p_rqd_perd_enrt_nenrt_rl => p_rqd_perd_enrt_nenrt_rl
173 ,p_dflt_enrt_cd => p_dflt_enrt_cd
174 ,p_prtn_elig_ovrid_alwd_flag => p_prtn_elig_ovrid_alwd_flag
175 ,p_drvbl_fctr_apls_rts_flag => p_drvbl_fctr_apls_rts_flag
176 ,p_per_cvrd_cd => p_per_cvrd_cd
177 ,p_postelcn_edit_rl => p_postelcn_edit_rl
178 ,p_vrfy_fmly_mmbr_cd => p_vrfy_fmly_mmbr_cd
179 ,p_vrfy_fmly_mmbr_rl => p_vrfy_fmly_mmbr_rl
180 ,p_enrt_cd => p_enrt_cd
181 ,p_enrt_rl => p_enrt_rl
182 ,p_auto_enrt_flag => p_auto_enrt_flag
183 ,p_auto_enrt_mthd_rl => p_auto_enrt_mthd_rl
184 ,p_short_name => p_short_name /*FHR*/
185 ,p_short_code => p_short_code /*FHR*/
186 ,p_legislation_code => p_legislation_code /*FHR*/
187 ,p_legislation_subgroup => p_legislation_subgroup /*FHR*/
188 ,p_hidden_flag => p_hidden_flag
189 ,p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag
190 ,p_ctfn_determine_cd => p_ctfn_determine_cd
191 ,p_cop_attribute_category => p_cop_attribute_category
192 ,p_cop_attribute1 => p_cop_attribute1
193 ,p_cop_attribute2 => p_cop_attribute2
194 ,p_cop_attribute3 => p_cop_attribute3
195 ,p_cop_attribute4 => p_cop_attribute4
196 ,p_cop_attribute5 => p_cop_attribute5
197 ,p_cop_attribute6 => p_cop_attribute6
198 ,p_cop_attribute7 => p_cop_attribute7
199 ,p_cop_attribute8 => p_cop_attribute8
200 ,p_cop_attribute9 => p_cop_attribute9
201 ,p_cop_attribute10 => p_cop_attribute10
202 ,p_cop_attribute11 => p_cop_attribute11
203 ,p_cop_attribute12 => p_cop_attribute12
204 ,p_cop_attribute13 => p_cop_attribute13
205 ,p_cop_attribute14 => p_cop_attribute14
206 ,p_cop_attribute15 => p_cop_attribute15
207 ,p_cop_attribute16 => p_cop_attribute16
208 ,p_cop_attribute17 => p_cop_attribute17
209 ,p_cop_attribute18 => p_cop_attribute18
210 ,p_cop_attribute19 => p_cop_attribute19
211 ,p_cop_attribute20 => p_cop_attribute20
212 ,p_cop_attribute21 => p_cop_attribute21
213 ,p_cop_attribute22 => p_cop_attribute22
214 ,p_cop_attribute23 => p_cop_attribute23
215 ,p_cop_attribute24 => p_cop_attribute24
216 ,p_cop_attribute25 => p_cop_attribute25
217 ,p_cop_attribute26 => p_cop_attribute26
218 ,p_cop_attribute27 => p_cop_attribute27
219 ,p_cop_attribute28 => p_cop_attribute28
220 ,p_cop_attribute29 => p_cop_attribute29
221 ,p_cop_attribute30 => p_cop_attribute30
222 ,p_effective_date => trunc(p_effective_date));
223 --
224 exception
225 --
226 when hr_api.cannot_find_prog_unit then
227 --
228 hr_api.cannot_find_prog_unit_error
229 (p_module_name => 'CREATE_Option_in_Plan'
230 ,p_hook_type => 'BP');
231 --
232 -- End of API User Hook for the before hook of create_Option_in_Plan
233 --
234 end;
235 --
236 ben_cop_ins.ins
237 (p_oipl_id => l_oipl_id
238 ,p_effective_start_date => l_effective_start_date
239 ,p_effective_end_date => l_effective_end_date
240 ,p_ivr_ident => p_ivr_ident
241 ,p_url_ref_name => p_url_ref_name
242 ,p_opt_id => p_opt_id
243 ,p_business_group_id => p_business_group_id
244 ,p_pl_id => p_pl_id
245 ,p_ordr_num => p_ordr_num
246 ,p_rqd_perd_enrt_nenrt_val => p_rqd_perd_enrt_nenrt_val
247 ,p_dflt_flag => p_dflt_flag
248 ,p_actl_prem_id => p_actl_prem_id
249 ,p_mndtry_flag => p_mndtry_flag
250 ,p_oipl_stat_cd => p_oipl_stat_cd
251 ,p_pcp_dsgn_cd => p_pcp_dsgn_cd
252 ,p_pcp_dpnt_dsgn_cd => p_pcp_dpnt_dsgn_cd
253 ,p_rqd_perd_enrt_nenrt_uom => p_rqd_perd_enrt_nenrt_uom
254 ,p_elig_apls_flag => p_elig_apls_flag
255 ,p_dflt_enrt_det_rl => p_dflt_enrt_det_rl
256 ,p_trk_inelig_per_flag => p_trk_inelig_per_flag
257 ,p_drvbl_fctr_prtn_elig_flag => p_drvbl_fctr_prtn_elig_flag
258 ,p_mndtry_rl => p_mndtry_rl
259 ,p_rqd_perd_enrt_nenrt_rl => p_rqd_perd_enrt_nenrt_rl
260 ,p_dflt_enrt_cd => p_dflt_enrt_cd
261 ,p_prtn_elig_ovrid_alwd_flag => p_prtn_elig_ovrid_alwd_flag
262 ,p_drvbl_fctr_apls_rts_flag => p_drvbl_fctr_apls_rts_flag
263 ,p_per_cvrd_cd => p_per_cvrd_cd
264 ,p_postelcn_edit_rl => p_postelcn_edit_rl
265 ,p_vrfy_fmly_mmbr_cd => p_vrfy_fmly_mmbr_cd
266 ,p_vrfy_fmly_mmbr_rl => p_vrfy_fmly_mmbr_rl
267 ,p_enrt_cd => p_enrt_cd
268 ,p_enrt_rl => p_enrt_rl
269 ,p_auto_enrt_flag => p_auto_enrt_flag
270 ,p_auto_enrt_mthd_rl => p_auto_enrt_mthd_rl
271 ,p_short_name => p_short_name /*FHR*/
272 ,p_short_code => p_short_code /*FHR*/
273 ,p_legislation_code => p_legislation_code /*FHR*/
274 ,p_legislation_subgroup => p_legislation_subgroup /*FHR*/
275 ,p_hidden_flag => p_hidden_flag
276 ,p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag
277 ,p_ctfn_determine_cd => p_ctfn_determine_cd
278 ,p_cop_attribute_category => p_cop_attribute_category
279 ,p_cop_attribute1 => p_cop_attribute1
280 ,p_cop_attribute2 => p_cop_attribute2
281 ,p_cop_attribute3 => p_cop_attribute3
282 ,p_cop_attribute4 => p_cop_attribute4
283 ,p_cop_attribute5 => p_cop_attribute5
284 ,p_cop_attribute6 => p_cop_attribute6
285 ,p_cop_attribute7 => p_cop_attribute7
286 ,p_cop_attribute8 => p_cop_attribute8
287 ,p_cop_attribute9 => p_cop_attribute9
288 ,p_cop_attribute10 => p_cop_attribute10
289 ,p_cop_attribute11 => p_cop_attribute11
290 ,p_cop_attribute12 => p_cop_attribute12
291 ,p_cop_attribute13 => p_cop_attribute13
292 ,p_cop_attribute14 => p_cop_attribute14
293 ,p_cop_attribute15 => p_cop_attribute15
294 ,p_cop_attribute16 => p_cop_attribute16
295 ,p_cop_attribute17 => p_cop_attribute17
296 ,p_cop_attribute18 => p_cop_attribute18
297 ,p_cop_attribute19 => p_cop_attribute19
298 ,p_cop_attribute20 => p_cop_attribute20
299 ,p_cop_attribute21 => p_cop_attribute21
300 ,p_cop_attribute22 => p_cop_attribute22
301 ,p_cop_attribute23 => p_cop_attribute23
302 ,p_cop_attribute24 => p_cop_attribute24
303 ,p_cop_attribute25 => p_cop_attribute25
304 ,p_cop_attribute26 => p_cop_attribute26
305 ,p_cop_attribute27 => p_cop_attribute27
306 ,p_cop_attribute28 => p_cop_attribute28
307 ,p_cop_attribute29 => p_cop_attribute29
308 ,p_cop_attribute30 => p_cop_attribute30
309 ,p_object_version_number => l_object_version_number
310 ,p_effective_date => trunc(p_effective_date));
311 --
312 begin
313 --
314 -- Start of API User Hook for the after hook of create_Option_in_Plan
315 --
316 ben_Option_in_Plan_bk1.create_Option_in_Plan_a
317 (p_oipl_id => l_oipl_id
318 ,p_effective_start_date => l_effective_start_date
319 ,p_effective_end_date => l_effective_end_date
320 ,p_ivr_ident => p_ivr_ident
321 ,p_url_ref_name => p_url_ref_name
322 ,p_opt_id => p_opt_id
323 ,p_business_group_id => p_business_group_id
324 ,p_pl_id => p_pl_id
325 ,p_ordr_num => p_ordr_num
326 ,p_rqd_perd_enrt_nenrt_val => p_rqd_perd_enrt_nenrt_val
327 ,p_dflt_flag => p_dflt_flag
328 ,p_actl_prem_id => p_actl_prem_id
329 ,p_mndtry_flag => p_mndtry_flag
330 ,p_oipl_stat_cd => p_oipl_stat_cd
331 ,p_pcp_dsgn_cd => p_pcp_dsgn_cd
332 ,p_pcp_dpnt_dsgn_cd => p_pcp_dpnt_dsgn_cd
333 ,p_rqd_perd_enrt_nenrt_uom => p_rqd_perd_enrt_nenrt_uom
334 ,p_elig_apls_flag => p_elig_apls_flag
335 ,p_dflt_enrt_det_rl => p_dflt_enrt_det_rl
336 ,p_trk_inelig_per_flag => p_trk_inelig_per_flag
337 ,p_drvbl_fctr_prtn_elig_flag => p_drvbl_fctr_prtn_elig_flag
338 ,p_mndtry_rl => p_mndtry_rl
339 ,p_rqd_perd_enrt_nenrt_rl => p_rqd_perd_enrt_nenrt_rl
340 ,p_dflt_enrt_cd => p_dflt_enrt_cd
341 ,p_prtn_elig_ovrid_alwd_flag => p_prtn_elig_ovrid_alwd_flag
342 ,p_drvbl_fctr_apls_rts_flag => p_drvbl_fctr_apls_rts_flag
343 ,p_per_cvrd_cd => p_per_cvrd_cd
344 ,p_postelcn_edit_rl => p_postelcn_edit_rl
345 ,p_vrfy_fmly_mmbr_cd => p_vrfy_fmly_mmbr_cd
346 ,p_vrfy_fmly_mmbr_rl => p_vrfy_fmly_mmbr_rl
347 ,p_enrt_cd => p_enrt_cd
348 ,p_enrt_rl => p_enrt_rl
349 ,p_auto_enrt_flag => p_auto_enrt_flag
350 ,p_auto_enrt_mthd_rl => p_auto_enrt_mthd_rl
351 ,p_short_name => p_short_name /*FHR*/
352 ,p_short_code => p_short_code /*FHR*/
353 ,p_legislation_code => p_legislation_code /*FHR*/
354 ,p_legislation_subgroup => p_legislation_subgroup /*FHR*/
355 ,p_hidden_flag => p_hidden_flag
356 ,p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag
357 ,p_ctfn_determine_cd => p_ctfn_determine_cd
358 ,p_cop_attribute_category => p_cop_attribute_category
359 ,p_cop_attribute1 => p_cop_attribute1
360 ,p_cop_attribute2 => p_cop_attribute2
361 ,p_cop_attribute3 => p_cop_attribute3
362 ,p_cop_attribute4 => p_cop_attribute4
363 ,p_cop_attribute5 => p_cop_attribute5
364 ,p_cop_attribute6 => p_cop_attribute6
365 ,p_cop_attribute7 => p_cop_attribute7
366 ,p_cop_attribute8 => p_cop_attribute8
367 ,p_cop_attribute9 => p_cop_attribute9
368 ,p_cop_attribute10 => p_cop_attribute10
369 ,p_cop_attribute11 => p_cop_attribute11
370 ,p_cop_attribute12 => p_cop_attribute12
371 ,p_cop_attribute13 => p_cop_attribute13
372 ,p_cop_attribute14 => p_cop_attribute14
373 ,p_cop_attribute15 => p_cop_attribute15
374 ,p_cop_attribute16 => p_cop_attribute16
375 ,p_cop_attribute17 => p_cop_attribute17
376 ,p_cop_attribute18 => p_cop_attribute18
377 ,p_cop_attribute19 => p_cop_attribute19
378 ,p_cop_attribute20 => p_cop_attribute20
379 ,p_cop_attribute21 => p_cop_attribute21
380 ,p_cop_attribute22 => p_cop_attribute22
381 ,p_cop_attribute23 => p_cop_attribute23
382 ,p_cop_attribute24 => p_cop_attribute24
383 ,p_cop_attribute25 => p_cop_attribute25
384 ,p_cop_attribute26 => p_cop_attribute26
385 ,p_cop_attribute27 => p_cop_attribute27
386 ,p_cop_attribute28 => p_cop_attribute28
387 ,p_cop_attribute29 => p_cop_attribute29
388 ,p_cop_attribute30 => p_cop_attribute30
389 ,p_object_version_number => l_object_version_number
390 ,p_effective_date => trunc(p_effective_date));
391 --
392 exception
393 --
394 when hr_api.cannot_find_prog_unit then
395 --
396 hr_api.cannot_find_prog_unit_error
397 (p_module_name => 'CREATE_Option_in_Plan'
398 ,p_hook_type => 'AP');
399 --
400 -- End of API User Hook for the after hook of create_Option_in_Plan
401 --
402 end;
403 --
404 for l_cpp in c_cpp loop
405 --
406 ben_option_in_plan_in_pgm_api.create_option_in_plan_in_pgm(
407 p_validate => false,
408 p_oiplip_id => l_oiplip_id,
409 p_effective_start_date => l_opp_effective_start_date,
410 p_effective_end_date => l_opp_effective_end_date,
411 p_oipl_id => l_oipl_id,
412 p_plip_id => l_cpp.plip_id,
413 p_business_group_id => p_business_group_id,
414 p_object_version_number => l_opp_object_version_number,
415 p_effective_date => p_effective_date);
416 --
417 end loop;
418 --
419 hr_utility.set_location(l_proc, 60);
420 -- bug 1285336
421 FOR l_otp IN c_otp LOOP
422 --
423 hr_utility.set_location('Before entering into create_opt_pltyp_in_pgm ', 65);
424 hr_utility.set_location('l_otp.pgm_id '||l_otp.pgm_id , 65.1);
425 hr_utility.set_location('l_otp.ptip_id'||l_otp.ptip_id,65.2);
426 hr_utility.set_location('l_otp.pl_typ_id'||l_otp.pl_typ_id,65.3);
427 --
428 ben_opt_pltyp_in_pgm_api.create_opt_pltyp_in_pgm
429 (p_validate => false
430 ,p_optip_id =>l_optip_id
431 ,p_effective_start_date =>l_otp_effective_start_date
432 ,p_effective_end_date =>l_otp_effective_end_date
433 ,p_business_group_id =>p_business_group_id
434 ,p_pgm_id =>l_otp.pgm_id
435 ,p_ptip_id =>l_otp.ptip_id
436 ,p_pl_typ_id =>l_otp.pl_typ_id
437 ,p_opt_id =>p_opt_id
438 -- ,p_cmbn_ptip_opt_id =>p_cmbn_ptip_opt_id
439 ,p_object_version_number =>l_otp_object_version_number
440 ,p_effective_date =>p_effective_date
441 );
442 hr_utility.set_location('Before entering into create_opt_pltyp_in_pgm ', 66);
443 END LOOP ;
444
445 -- When in validation only mode raise the Validate_Enabled exception
446 --
447 if p_validate then
448 raise hr_api.validate_enabled;
449 end if;
450 --
451 -- Set all output arguments
452 --
453 p_oipl_id := l_oipl_id;
454 p_effective_start_date := l_effective_start_date;
455 p_effective_end_date := l_effective_end_date;
456 p_object_version_number := l_object_version_number;
457 --
458 hr_utility.set_location(' Leaving:'||l_proc, 70);
459 --
460 exception
461 --
462 when hr_api.validate_enabled then
463 --
464 -- As the Validate_Enabled exception has been raised
465 -- we must rollback to the savepoint
466 --
467 ROLLBACK TO create_Option_in_Plan;
468 --
469 -- Only set output warning arguments
470 -- (Any key or derived arguments must be set to null
471 -- when validation only mode is being used.)
472 --
473 p_oipl_id := null;
474 p_effective_start_date := null;
475 p_effective_end_date := null;
476 p_object_version_number := null;
477 hr_utility.set_location(' Leaving:'||l_proc, 80);
478 --
479 when others then
480 --
481 -- A validation or unexpected error has occured
482 --
483 ROLLBACK TO create_Option_in_Plan;
484 /* Inserted for nocopy changes */
485 p_oipl_id := null;
486 p_effective_start_date := null;
487 p_effective_end_date := null;
488 p_object_version_number := null;
489 raise;
490 --
491 end create_Option_in_Plan;
492 -- ----------------------------------------------------------------------------
493 -- |------------------------< update_Option_in_Plan >-------------------------|
494 -- ----------------------------------------------------------------------------
495 --
496 procedure update_Option_in_Plan
497 (p_validate in boolean default false
498 ,p_oipl_id in number
499 ,p_effective_start_date out nocopy date
500 ,p_effective_end_date out nocopy date
501 ,p_ivr_ident in varchar2 default hr_api.g_varchar2
502 ,p_url_ref_name in varchar2 default hr_api.g_varchar2
503 ,p_opt_id in number default hr_api.g_number
504 ,p_business_group_id in number default hr_api.g_number
505 ,p_pl_id in number default hr_api.g_number
506 ,p_ordr_num in number default hr_api.g_number
507 ,p_rqd_perd_enrt_nenrt_val in number default hr_api.g_number
508 ,p_dflt_flag in varchar2 default hr_api.g_varchar2
509 ,p_actl_prem_id in number default hr_api.g_number
510 ,p_mndtry_flag in varchar2 default hr_api.g_varchar2
511 ,p_oipl_stat_cd in varchar2 default hr_api.g_varchar2
512 ,p_pcp_dsgn_cd in varchar2 default hr_api.g_varchar2
513 ,p_pcp_dpnt_dsgn_cd in varchar2 default hr_api.g_varchar2
514 ,p_rqd_perd_enrt_nenrt_uom in varchar2 default hr_api.g_varchar2
515 ,p_elig_apls_flag in varchar2 default hr_api.g_varchar2
516 ,p_dflt_enrt_det_rl in number default hr_api.g_number
517 ,p_trk_inelig_per_flag in varchar2 default hr_api.g_varchar2
518 ,p_drvbl_fctr_prtn_elig_flag in varchar2 default hr_api.g_varchar2
519 ,p_mndtry_rl in number default hr_api.g_number
520 ,p_rqd_perd_enrt_nenrt_rl in number default hr_api.g_number
521 ,p_dflt_enrt_cd in varchar2 default hr_api.g_varchar2
522 ,p_prtn_elig_ovrid_alwd_flag in varchar2 default hr_api.g_varchar2
523 ,p_drvbl_fctr_apls_rts_flag in varchar2 default hr_api.g_varchar2
524 ,p_per_cvrd_cd in varchar2 default hr_api.g_varchar2
525 ,p_postelcn_edit_rl in number default hr_api.g_number
526 ,p_vrfy_fmly_mmbr_cd in varchar2 default hr_api.g_varchar2
527 ,p_vrfy_fmly_mmbr_rl in number default hr_api.g_number
528 ,p_enrt_cd in varchar2 default hr_api.g_varchar2
529 ,p_enrt_rl in number default hr_api.g_number
530 ,p_auto_enrt_flag in varchar2 default hr_api.g_varchar2
531 ,p_auto_enrt_mthd_rl in number default hr_api.g_number
532 ,p_short_name in varchar2 default hr_api.g_varchar2 /*FHR*/
533 ,p_short_code in varchar2 default hr_api.g_varchar2 /*FHR*/
534 ,p_legislation_code in varchar2 default hr_api.g_varchar2 /*FHR*/
535 ,p_legislation_subgroup in varchar2 default hr_api.g_varchar2 /*FHR*/
536 ,p_hidden_flag in varchar2 default hr_api.g_varchar2
537 ,p_susp_if_ctfn_not_prvd_flag in varchar2 default hr_api.g_varchar2
538 ,p_ctfn_determine_cd in varchar2 default hr_api.g_varchar2
539 ,p_cop_attribute_category in varchar2 default hr_api.g_varchar2
540 ,p_cop_attribute1 in varchar2 default hr_api.g_varchar2
541 ,p_cop_attribute2 in varchar2 default hr_api.g_varchar2
542 ,p_cop_attribute3 in varchar2 default hr_api.g_varchar2
543 ,p_cop_attribute4 in varchar2 default hr_api.g_varchar2
544 ,p_cop_attribute5 in varchar2 default hr_api.g_varchar2
545 ,p_cop_attribute6 in varchar2 default hr_api.g_varchar2
546 ,p_cop_attribute7 in varchar2 default hr_api.g_varchar2
547 ,p_cop_attribute8 in varchar2 default hr_api.g_varchar2
548 ,p_cop_attribute9 in varchar2 default hr_api.g_varchar2
549 ,p_cop_attribute10 in varchar2 default hr_api.g_varchar2
550 ,p_cop_attribute11 in varchar2 default hr_api.g_varchar2
551 ,p_cop_attribute12 in varchar2 default hr_api.g_varchar2
552 ,p_cop_attribute13 in varchar2 default hr_api.g_varchar2
553 ,p_cop_attribute14 in varchar2 default hr_api.g_varchar2
554 ,p_cop_attribute15 in varchar2 default hr_api.g_varchar2
555 ,p_cop_attribute16 in varchar2 default hr_api.g_varchar2
556 ,p_cop_attribute17 in varchar2 default hr_api.g_varchar2
557 ,p_cop_attribute18 in varchar2 default hr_api.g_varchar2
558 ,p_cop_attribute19 in varchar2 default hr_api.g_varchar2
559 ,p_cop_attribute20 in varchar2 default hr_api.g_varchar2
560 ,p_cop_attribute21 in varchar2 default hr_api.g_varchar2
561 ,p_cop_attribute22 in varchar2 default hr_api.g_varchar2
562 ,p_cop_attribute23 in varchar2 default hr_api.g_varchar2
563 ,p_cop_attribute24 in varchar2 default hr_api.g_varchar2
564 ,p_cop_attribute25 in varchar2 default hr_api.g_varchar2
565 ,p_cop_attribute26 in varchar2 default hr_api.g_varchar2
566 ,p_cop_attribute27 in varchar2 default hr_api.g_varchar2
567 ,p_cop_attribute28 in varchar2 default hr_api.g_varchar2
568 ,p_cop_attribute29 in varchar2 default hr_api.g_varchar2
569 ,p_cop_attribute30 in varchar2 default hr_api.g_varchar2
570 ,p_object_version_number in out nocopy number
571 ,p_effective_date in date
572 ,p_datetrack_mode in varchar2) is
573 --
574 -- Declare cursors and local variables
575 --
576 l_proc varchar2(72) := g_package||'update_Option_in_Plan';
577 l_object_version_number ben_oipl_f.object_version_number%TYPE;
578 l_effective_start_date ben_oipl_f.effective_start_date%TYPE;
579 l_effective_end_date ben_oipl_f.effective_end_date%TYPE;
580 --
581 begin
582 --
583 hr_utility.set_location('Entering:'|| l_proc, 10);
584 --
585 -- Issue a savepoint if operating in validation only mode
586 --
587 savepoint update_Option_in_Plan;
588 --
589 hr_utility.set_location(l_proc, 20);
590 --
591 -- Process Logic
592 --
593 l_object_version_number := p_object_version_number;
594 --
595 begin
596 --
597 -- Start of API User Hook for the before hook of update_Option_in_Plan
598 --
599 ben_Option_in_Plan_bk2.update_Option_in_Plan_b
600 (p_oipl_id => p_oipl_id
601 ,p_ivr_ident => p_ivr_ident
602 ,p_url_ref_name => p_url_ref_name
603 ,p_opt_id => p_opt_id
604 ,p_business_group_id => p_business_group_id
605 ,p_pl_id => p_pl_id
606 ,p_ordr_num => p_ordr_num
607 ,p_rqd_perd_enrt_nenrt_val => p_rqd_perd_enrt_nenrt_val
608 ,p_dflt_flag => p_dflt_flag
609 ,p_actl_prem_id => p_actl_prem_id
610 ,p_mndtry_flag => p_mndtry_flag
611 ,p_oipl_stat_cd => p_oipl_stat_cd
612 ,p_pcp_dsgn_cd => p_pcp_dsgn_cd
613 ,p_pcp_dpnt_dsgn_cd => p_pcp_dpnt_dsgn_cd
614 ,p_rqd_perd_enrt_nenrt_uom => p_rqd_perd_enrt_nenrt_uom
615 ,p_elig_apls_flag => p_elig_apls_flag
616 ,p_dflt_enrt_det_rl => p_dflt_enrt_det_rl
617 ,p_trk_inelig_per_flag => p_trk_inelig_per_flag
618 ,p_drvbl_fctr_prtn_elig_flag => p_drvbl_fctr_prtn_elig_flag
619 ,p_mndtry_rl => p_mndtry_rl
620 ,p_rqd_perd_enrt_nenrt_rl => p_rqd_perd_enrt_nenrt_rl
621 ,p_dflt_enrt_cd => p_dflt_enrt_cd
622 ,p_prtn_elig_ovrid_alwd_flag => p_prtn_elig_ovrid_alwd_flag
623 ,p_drvbl_fctr_apls_rts_flag => p_drvbl_fctr_apls_rts_flag
624 ,p_per_cvrd_cd => p_per_cvrd_cd
625 ,p_postelcn_edit_rl => p_postelcn_edit_rl
626 ,p_vrfy_fmly_mmbr_cd => p_vrfy_fmly_mmbr_cd
627 ,p_vrfy_fmly_mmbr_rl => p_vrfy_fmly_mmbr_rl
628 ,p_enrt_cd => p_enrt_cd
629 ,p_enrt_rl => p_enrt_rl
630 ,p_auto_enrt_flag => p_auto_enrt_flag
631 ,p_auto_enrt_mthd_rl => p_auto_enrt_mthd_rl
632 ,p_short_name => p_short_name /*FHR*/
633 ,p_short_code => p_short_code /*FHR*/
634 ,p_legislation_code => p_legislation_code /*FHR*/
635 ,p_legislation_subgroup => p_legislation_subgroup /*FHR*/
636 ,p_hidden_flag => p_hidden_flag
637 ,p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag
638 ,p_ctfn_determine_cd => p_ctfn_determine_cd
639 ,p_cop_attribute_category => p_cop_attribute_category
640 ,p_cop_attribute1 => p_cop_attribute1
641 ,p_cop_attribute2 => p_cop_attribute2
642 ,p_cop_attribute3 => p_cop_attribute3
643 ,p_cop_attribute4 => p_cop_attribute4
644 ,p_cop_attribute5 => p_cop_attribute5
645 ,p_cop_attribute6 => p_cop_attribute6
646 ,p_cop_attribute7 => p_cop_attribute7
647 ,p_cop_attribute8 => p_cop_attribute8
648 ,p_cop_attribute9 => p_cop_attribute9
649 ,p_cop_attribute10 => p_cop_attribute10
650 ,p_cop_attribute11 => p_cop_attribute11
651 ,p_cop_attribute12 => p_cop_attribute12
652 ,p_cop_attribute13 => p_cop_attribute13
653 ,p_cop_attribute14 => p_cop_attribute14
654 ,p_cop_attribute15 => p_cop_attribute15
655 ,p_cop_attribute16 => p_cop_attribute16
656 ,p_cop_attribute17 => p_cop_attribute17
657 ,p_cop_attribute18 => p_cop_attribute18
658 ,p_cop_attribute19 => p_cop_attribute19
659 ,p_cop_attribute20 => p_cop_attribute20
660 ,p_cop_attribute21 => p_cop_attribute21
661 ,p_cop_attribute22 => p_cop_attribute22
662 ,p_cop_attribute23 => p_cop_attribute23
663 ,p_cop_attribute24 => p_cop_attribute24
664 ,p_cop_attribute25 => p_cop_attribute25
665 ,p_cop_attribute26 => p_cop_attribute26
666 ,p_cop_attribute27 => p_cop_attribute27
667 ,p_cop_attribute28 => p_cop_attribute28
668 ,p_cop_attribute29 => p_cop_attribute29
669 ,p_cop_attribute30 => p_cop_attribute30
670 ,p_object_version_number => p_object_version_number
671 ,p_effective_date => trunc(p_effective_date)
672 ,p_datetrack_mode => p_datetrack_mode);
673 --
674 exception
675 --
676 when hr_api.cannot_find_prog_unit then
677 --
678 hr_api.cannot_find_prog_unit_error
679 (p_module_name => 'UPDATE_Option_in_Plan'
680 ,p_hook_type => 'BP');
681 --
682 -- End of API User Hook for the before hook of update_Option_in_Plan
683 --
684 end;
685 --
686 ben_cop_upd.upd
687 (p_oipl_id => p_oipl_id
688 ,p_effective_start_date => l_effective_start_date
689 ,p_effective_end_date => l_effective_end_date
690 ,p_ivr_ident => p_ivr_ident
691 ,p_url_ref_name => p_url_ref_name
692 ,p_opt_id => p_opt_id
693 ,p_business_group_id => p_business_group_id
694 ,p_pl_id => p_pl_id
695 ,p_ordr_num => p_ordr_num
696 ,p_rqd_perd_enrt_nenrt_val => p_rqd_perd_enrt_nenrt_val
697 ,p_dflt_flag => p_dflt_flag
698 ,p_actl_prem_id => p_actl_prem_id
699 ,p_mndtry_flag => p_mndtry_flag
700 ,p_oipl_stat_cd => p_oipl_stat_cd
701 ,p_pcp_dsgn_cd => p_pcp_dsgn_cd
702 ,p_pcp_dpnt_dsgn_cd => p_pcp_dpnt_dsgn_cd
703 ,p_rqd_perd_enrt_nenrt_uom => p_rqd_perd_enrt_nenrt_uom
704 ,p_elig_apls_flag => p_elig_apls_flag
705 ,p_dflt_enrt_det_rl => p_dflt_enrt_det_rl
706 ,p_trk_inelig_per_flag => p_trk_inelig_per_flag
707 ,p_drvbl_fctr_prtn_elig_flag => p_drvbl_fctr_prtn_elig_flag
708 ,p_mndtry_rl => p_mndtry_rl
709 ,p_rqd_perd_enrt_nenrt_rl => p_rqd_perd_enrt_nenrt_rl
710 ,p_dflt_enrt_cd => p_dflt_enrt_cd
711 ,p_prtn_elig_ovrid_alwd_flag => p_prtn_elig_ovrid_alwd_flag
712 ,p_drvbl_fctr_apls_rts_flag => p_drvbl_fctr_apls_rts_flag
713 ,p_per_cvrd_cd => p_per_cvrd_cd
714 ,p_postelcn_edit_rl => p_postelcn_edit_rl
715 ,p_vrfy_fmly_mmbr_cd => p_vrfy_fmly_mmbr_cd
716 ,p_vrfy_fmly_mmbr_rl => p_vrfy_fmly_mmbr_rl
717 ,p_enrt_cd => p_enrt_cd
718 ,p_enrt_rl => p_enrt_rl
719 ,p_auto_enrt_flag => p_auto_enrt_flag
720 ,p_auto_enrt_mthd_rl => p_auto_enrt_mthd_rl
721 ,p_short_name => p_short_name /*FHR*/
722 ,p_short_code => p_short_code /*FHR*/
723 ,p_legislation_code => p_legislation_code /*FHR*/
724 ,p_legislation_subgroup => p_legislation_subgroup /*FHR*/
725 ,p_hidden_flag => p_hidden_flag
726 ,p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag
727 ,p_ctfn_determine_cd => p_ctfn_determine_cd
728 ,p_cop_attribute_category => p_cop_attribute_category
729 ,p_cop_attribute1 => p_cop_attribute1
730 ,p_cop_attribute2 => p_cop_attribute2
731 ,p_cop_attribute3 => p_cop_attribute3
732 ,p_cop_attribute4 => p_cop_attribute4
733 ,p_cop_attribute5 => p_cop_attribute5
734 ,p_cop_attribute6 => p_cop_attribute6
735 ,p_cop_attribute7 => p_cop_attribute7
736 ,p_cop_attribute8 => p_cop_attribute8
737 ,p_cop_attribute9 => p_cop_attribute9
738 ,p_cop_attribute10 => p_cop_attribute10
739 ,p_cop_attribute11 => p_cop_attribute11
740 ,p_cop_attribute12 => p_cop_attribute12
741 ,p_cop_attribute13 => p_cop_attribute13
742 ,p_cop_attribute14 => p_cop_attribute14
743 ,p_cop_attribute15 => p_cop_attribute15
744 ,p_cop_attribute16 => p_cop_attribute16
745 ,p_cop_attribute17 => p_cop_attribute17
746 ,p_cop_attribute18 => p_cop_attribute18
747 ,p_cop_attribute19 => p_cop_attribute19
748 ,p_cop_attribute20 => p_cop_attribute20
749 ,p_cop_attribute21 => p_cop_attribute21
750 ,p_cop_attribute22 => p_cop_attribute22
751 ,p_cop_attribute23 => p_cop_attribute23
752 ,p_cop_attribute24 => p_cop_attribute24
753 ,p_cop_attribute25 => p_cop_attribute25
754 ,p_cop_attribute26 => p_cop_attribute26
755 ,p_cop_attribute27 => p_cop_attribute27
756 ,p_cop_attribute28 => p_cop_attribute28
757 ,p_cop_attribute29 => p_cop_attribute29
758 ,p_cop_attribute30 => p_cop_attribute30
759 ,p_object_version_number => l_object_version_number
760 ,p_effective_date => trunc(p_effective_date)
761 ,p_datetrack_mode => p_datetrack_mode);
762 --
763 begin
764 --
765 -- Start of API User Hook for the after hook of update_Option_in_Plan
766 --
767 ben_Option_in_Plan_bk2.update_Option_in_Plan_a
768 (p_oipl_id => p_oipl_id
769 ,p_effective_start_date => l_effective_start_date
770 ,p_effective_end_date => l_effective_end_date
771 ,p_ivr_ident => p_ivr_ident
772 ,p_url_ref_name => p_url_ref_name
773 ,p_opt_id => p_opt_id
774 ,p_business_group_id => p_business_group_id
775 ,p_pl_id => p_pl_id
776 ,p_ordr_num => p_ordr_num
777 ,p_rqd_perd_enrt_nenrt_val => p_rqd_perd_enrt_nenrt_val
778 ,p_dflt_flag => p_dflt_flag
779 ,p_actl_prem_id => p_actl_prem_id
780 ,p_mndtry_flag => p_mndtry_flag
781 ,p_oipl_stat_cd => p_oipl_stat_cd
782 ,p_pcp_dsgn_cd => p_pcp_dsgn_cd
783 ,p_pcp_dpnt_dsgn_cd => p_pcp_dpnt_dsgn_cd
784 ,p_rqd_perd_enrt_nenrt_uom => p_rqd_perd_enrt_nenrt_uom
785 ,p_elig_apls_flag => p_elig_apls_flag
786 ,p_dflt_enrt_det_rl => p_dflt_enrt_det_rl
787 ,p_trk_inelig_per_flag => p_trk_inelig_per_flag
788 ,p_drvbl_fctr_prtn_elig_flag => p_drvbl_fctr_prtn_elig_flag
789 ,p_mndtry_rl => p_mndtry_rl
790 ,p_rqd_perd_enrt_nenrt_rl => p_rqd_perd_enrt_nenrt_rl
791 ,p_dflt_enrt_cd => p_dflt_enrt_cd
792 ,p_prtn_elig_ovrid_alwd_flag => p_prtn_elig_ovrid_alwd_flag
793 ,p_drvbl_fctr_apls_rts_flag => p_drvbl_fctr_apls_rts_flag
794 ,p_per_cvrd_cd => p_per_cvrd_cd
795 ,p_postelcn_edit_rl => p_postelcn_edit_rl
796 ,p_vrfy_fmly_mmbr_cd => p_vrfy_fmly_mmbr_cd
797 ,p_vrfy_fmly_mmbr_rl => p_vrfy_fmly_mmbr_rl
798 ,p_enrt_cd => p_enrt_cd
799 ,p_enrt_rl => p_enrt_rl
800 ,p_auto_enrt_flag => p_auto_enrt_flag
801 ,p_auto_enrt_mthd_rl => p_auto_enrt_mthd_rl
802 ,p_short_name => p_short_name /*FHR*/
803 ,p_short_code => p_short_code /*FHR*/
804 ,p_legislation_code => p_legislation_code /*FHR*/
805 ,p_legislation_subgroup => p_legislation_subgroup /*FHR*/
806 ,p_hidden_flag => p_hidden_flag
807 ,p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag
808 ,p_ctfn_determine_cd => p_ctfn_determine_cd
809 ,p_cop_attribute_category => p_cop_attribute_category
810 ,p_cop_attribute1 => p_cop_attribute1
811 ,p_cop_attribute2 => p_cop_attribute2
812 ,p_cop_attribute3 => p_cop_attribute3
813 ,p_cop_attribute4 => p_cop_attribute4
814 ,p_cop_attribute5 => p_cop_attribute5
815 ,p_cop_attribute6 => p_cop_attribute6
816 ,p_cop_attribute7 => p_cop_attribute7
817 ,p_cop_attribute8 => p_cop_attribute8
818 ,p_cop_attribute9 => p_cop_attribute9
819 ,p_cop_attribute10 => p_cop_attribute10
820 ,p_cop_attribute11 => p_cop_attribute11
821 ,p_cop_attribute12 => p_cop_attribute12
822 ,p_cop_attribute13 => p_cop_attribute13
823 ,p_cop_attribute14 => p_cop_attribute14
824 ,p_cop_attribute15 => p_cop_attribute15
825 ,p_cop_attribute16 => p_cop_attribute16
826 ,p_cop_attribute17 => p_cop_attribute17
827 ,p_cop_attribute18 => p_cop_attribute18
828 ,p_cop_attribute19 => p_cop_attribute19
829 ,p_cop_attribute20 => p_cop_attribute20
830 ,p_cop_attribute21 => p_cop_attribute21
831 ,p_cop_attribute22 => p_cop_attribute22
832 ,p_cop_attribute23 => p_cop_attribute23
833 ,p_cop_attribute24 => p_cop_attribute24
834 ,p_cop_attribute25 => p_cop_attribute25
835 ,p_cop_attribute26 => p_cop_attribute26
836 ,p_cop_attribute27 => p_cop_attribute27
837 ,p_cop_attribute28 => p_cop_attribute28
838 ,p_cop_attribute29 => p_cop_attribute29
839 ,p_cop_attribute30 => p_cop_attribute30
840 ,p_object_version_number => l_object_version_number
841 ,p_effective_date => trunc(p_effective_date)
842 ,p_datetrack_mode => p_datetrack_mode);
843 --
844 exception
845 --
846 when hr_api.cannot_find_prog_unit then
847 --
848 hr_api.cannot_find_prog_unit_error
849 (p_module_name => 'UPDATE_Option_in_Plan'
850 ,p_hook_type => 'AP');
851 --
852 -- End of API User Hook for the after hook of update_Option_in_Plan
853 --
854 end;
855 --
856 hr_utility.set_location(l_proc, 60);
857 --
858 -- When in validation only mode raise the Validate_Enabled exception
859 --
860 if p_validate then
861 raise hr_api.validate_enabled;
862 end if;
863 --
864 -- Set all output arguments
865 --
866 p_object_version_number := l_object_version_number;
867 p_effective_start_date := l_effective_start_date;
868 p_effective_end_date := l_effective_end_date;
869 --
870 hr_utility.set_location(' Leaving:'||l_proc, 70);
871 --
872 exception
873 --
874 when hr_api.validate_enabled then
875 --
876 -- As the Validate_Enabled exception has been raised
877 -- we must rollback to the savepoint
878 --
879 ROLLBACK TO update_Option_in_Plan;
880 --
881 -- Only set output warning arguments
882 -- (Any key or derived arguments must be set to null
883 -- when validation only mode is being used.)
884 --
885 hr_utility.set_location(' Leaving:'||l_proc, 80);
886 --
887 when others then
888 --
889 -- A validation or unexpected error has occured
890 --
891 ROLLBACK TO update_Option_in_Plan;
892 /* Inserted for nocopy changes */
893 p_object_version_number := l_object_version_number;
894 p_effective_start_date := null;
895 p_effective_end_date := null;
896
897 raise;
898 --
899 end update_Option_in_Plan;
900 -- ----------------------------------------------------------------------------
901 -- |------------------------< delete_Option_in_Plan >-------------------------|
902 -- ----------------------------------------------------------------------------
903 --
904 procedure delete_Option_in_Plan
905 (p_validate in boolean default false
906 ,p_oipl_id in number
907 ,p_effective_start_date out nocopy date
908 ,p_effective_end_date out nocopy date
909 ,p_object_version_number in out nocopy number
910 ,p_effective_date in date
911 ,p_datetrack_mode in varchar2) is
912 --
913 -- Declare cursors and local variables
914 --
915 cursor c_opp is
916 select opp.oiplip_id,
917 opp.object_version_number
918 from ben_oiplip_f opp
919 where opp.oipl_id = p_oipl_id
920 and p_effective_date between
921 opp.effective_start_date and opp.effective_end_date;
922 -- To find the pgm, plan, opt and plan_typ for the oipl
923 cursor c_exist_otp is
924 select
925 oipl.opt_id,
926 plip.pgm_id,
927 pl.pl_id,
928 pl.pl_typ_id
929 from
930 ben_plip_f plip,
931 ben_pl_f pl,
932 ben_oipl_f oipl
933 where
934 oipl.oipl_id = p_oipl_id
935 and oipl.pl_id = pl.pl_id
936 and plip.pl_id = pl.pl_id
937 -- and pl.business_group_id = p_business_group_id
938 -- and plip.business_group_id = p_business_group_id
939 -- and oipl.business_group_id = p_business_group_id
940 and p_effective_date between plip.effective_start_date and plip.effective_end_date
941 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
942 and p_effective_date between pl.effective_start_date and pl.effective_end_date ;
943 --
944 cursor c_delete_otp(p_opt_id number ,
945 p_pgm_id number ,
946 p_pl_id number,
947 p_pl_typ_id number ) is
948 select
949 otp.optip_id,
950 otp.object_version_number
951 from
952 ben_optip_f otp
953 where
954 otp.pgm_id = p_pgm_id
955 and otp.pl_typ_id = p_pl_typ_id
956 and otp.opt_id = p_opt_id
957 -- and otp.business_group_id = p_business_group_id
958 and p_effective_date between otp.effective_start_date and otp.effective_end_date -- Bug 3023622
959 and not exists ( select null
960 from
961 ben_plip_f plip,
962 ben_pl_f pl,
963 ben_oipl_f oipl
964 where -- plip.business_group_id = p_business_group_id
965 plip.pgm_id = p_pgm_id
966 and pl.pl_typ_id = p_pl_typ_id
967 and pl.pl_id = plip.pl_id
968 and pl.pl_id <> p_pl_id
969 and pl.pl_id = oipl.pl_id
970 and oipl.opt_id = p_opt_id
971 and p_effective_date
972 between plip.effective_start_date and plip.effective_end_date
973 and p_effective_date
974 between pl.effective_start_date and pl.effective_end_date
975 and p_effective_date
976 between oipl.effective_start_date and oipl.effective_end_date ) ;
977
978 l_proc varchar2(72) := g_package||'update_Option_in_Plan';
979 l_object_version_number ben_oipl_f.object_version_number%TYPE;
980 l_effective_start_date ben_oipl_f.effective_start_date%TYPE;
981 l_effective_end_date ben_oipl_f.effective_end_date%TYPE;
982 --
983 l_opp_effective_start_date ben_oiplip_f.effective_start_date%type;
984 l_opp_effective_end_date ben_oiplip_f.effective_end_date%type;
985 l_opp_object_version_number ben_oiplip_f.object_version_number%type;
986 --
987 l_otp_effective_start_date ben_optip_f.effective_start_date%type;
988 l_otp_effective_end_date ben_optip_f.effective_end_date%type;
989 l_otp_object_version_number ben_optip_f.object_version_number%type;
990 --
991
992 begin
993 --
994 hr_utility.set_location('Entering:'|| l_proc, 10);
995 --
996 -- Issue a savepoint if operating in validation only mode
997 --
998 savepoint delete_Option_in_Plan;
999 --
1000 hr_utility.set_location(l_proc, 20);
1001 --
1002 -- Process Logic
1003 --
1004 l_object_version_number := p_object_version_number;
1005 --
1006 --
1007 begin
1008 --
1009 -- Start of API User Hook for the before hook of delete_Option_in_Plan
1010 --
1011 ben_Option_in_Plan_bk3.delete_Option_in_Plan_b
1012 (p_oipl_id => p_oipl_id
1013 ,p_object_version_number => p_object_version_number
1014 ,p_effective_date => trunc(p_effective_date)
1015 ,p_datetrack_mode => p_datetrack_mode);
1016 --
1017 exception
1018 --
1019 when hr_api.cannot_find_prog_unit then
1020 --
1021 hr_api.cannot_find_prog_unit_error
1022 (p_module_name => 'DELETE_Option_in_Plan'
1023 ,p_hook_type => 'BP');
1024 --
1025 -- End of API User Hook for the before hook of delete_Option_in_Plan
1026 --
1027 end;
1028 --
1029 if p_datetrack_mode in ('ZAP') then
1030 -- bug 4339784, child records should not be checked for other delete datetrack modes
1031 -- Added above if condition to check for childs only for ZAP , DELETEs
1032 for l_opp in c_opp loop
1033 --
1034 l_opp_object_version_number := l_opp.object_version_number;
1035 --
1036 ben_option_in_plan_in_pgm_api.delete_option_in_plan_in_pgm(
1037 p_validate => false,
1038 p_oiplip_id => l_opp.oiplip_id,
1039 p_effective_start_date => l_opp_effective_start_date,
1040 p_effective_end_date => l_opp_effective_end_date,
1041 p_object_version_number => l_opp_object_version_number,
1042 p_effective_date => p_effective_date,
1043 p_datetrack_mode => p_datetrack_mode
1044 );
1045 --
1046 end loop;
1047 --
1048 hr_utility.set_location('Before entering into Optip delete api ',24);
1049 for l_exist_otp in c_exist_otp loop
1050 --
1051 hr_utility.set_location('Entering:'||l_proc||'in l_exist_otp ',25);
1052 for l_delete_otp in c_delete_otp(l_exist_otp.opt_id,
1053 l_exist_otp.pgm_id,
1054 l_exist_otp.pl_id,
1055 l_exist_otp.pl_typ_id ) loop
1056 --
1057 l_otp_object_version_number := l_delete_otp.object_version_number;
1058 --
1059 hr_utility.set_location('Entering:'||l_proc||'in l_delete_otp' ,26);
1060 hr_utility.set_location(' Opt_id '||l_exist_otp.opt_id , 26);
1061 hr_utility.set_location(' pgm_id '||l_exist_otp.pgm_id , 26);
1062 hr_utility.set_location(' pl_id '||l_exist_otp.pl_id , 26);
1063 hr_utility.set_location(' pl_typ_id '||l_exist_otp.pl_typ_id , 26);
1064 ben_opt_pltyp_in_pgm_api.delete_opt_pltyp_in_pgm(
1065 p_validate => false,
1066 p_optip_id => l_delete_otp.optip_id,
1067 p_effective_start_date => l_otp_effective_start_date,
1068 p_effective_end_date => l_otp_effective_end_date,
1069 p_object_version_number => l_otp_object_version_number,
1070 p_effective_date => p_effective_date,
1071 p_datetrack_mode => p_datetrack_mode
1072 );
1073 --
1074 end loop;
1075 --
1076 end loop ;
1077 End if;
1078 hr_utility.set_location('After leaving Optip delete api ',27);
1079 --
1080 ben_cop_del.del
1081 (p_oipl_id => p_oipl_id
1082 ,p_effective_start_date => l_effective_start_date
1083 ,p_effective_end_date => l_effective_end_date
1084 ,p_object_version_number => l_object_version_number
1085 ,p_effective_date => p_effective_date
1086 ,p_datetrack_mode => p_datetrack_mode);
1087 --
1088 begin
1089 --
1090 -- Start of API User Hook for the after hook of delete_Option_in_Plan
1091 --
1092 ben_Option_in_Plan_bk3.delete_Option_in_Plan_a
1093 (p_oipl_id => p_oipl_id
1094 ,p_effective_start_date => l_effective_start_date
1095 ,p_effective_end_date => l_effective_end_date
1096 ,p_object_version_number => l_object_version_number
1097 ,p_effective_date => trunc(p_effective_date)
1098 ,p_datetrack_mode => p_datetrack_mode);
1099 --
1100 exception
1101 --
1102 when hr_api.cannot_find_prog_unit then
1103 --
1104 hr_api.cannot_find_prog_unit_error
1105 (p_module_name => 'DELETE_Option_in_Plan'
1106 ,p_hook_type => 'AP');
1107 --
1108 -- End of API User Hook for the after hook of delete_Option_in_Plan
1109 --
1110 end;
1111 --
1112 hr_utility.set_location(l_proc, 60);
1113 --
1114 -- When in validation only mode raise the Validate_Enabled exception
1115 --
1116 if p_validate then
1117 raise hr_api.validate_enabled;
1118 end if;
1119 --
1120 hr_utility.set_location(' Leaving:'||l_proc, 70);
1121 --
1122 exception
1123 --
1124 when hr_api.validate_enabled then
1125 --
1126 -- As the Validate_Enabled exception has been raised
1127 -- we must rollback to the savepoint
1128 --
1129 ROLLBACK TO delete_Option_in_Plan;
1130 --
1131 -- Only set output warning arguments
1132 -- (Any key or derived arguments must be set to null
1133 -- when validation only mode is being used.)
1134 --
1135 p_effective_start_date := null;
1136 p_effective_end_date := null;
1137 --
1138 when others then
1139 --
1140 -- A validation or unexpected error has occured
1141 --
1142 ROLLBACK TO delete_Option_in_Plan;
1143 /* Inserted for nocopy changes */
1144 p_effective_start_date := null;
1145 p_effective_end_date := null;
1146 p_object_version_number := l_object_version_number;
1147 raise;
1148 --
1149 end delete_Option_in_Plan;
1150 --
1151 -- ----------------------------------------------------------------------------
1152 -- |-------------------------------< lck >------------------------------------|
1153 -- ----------------------------------------------------------------------------
1154 --
1155 procedure lck
1156 (p_oipl_id in number
1157 ,p_object_version_number in number
1158 ,p_effective_date in date
1159 ,p_datetrack_mode in varchar2
1160 ,p_validation_start_date out nocopy date
1161 ,p_validation_end_date out nocopy date) is
1162 --
1163 --
1164 -- Declare cursors and local variables
1165 --
1166 l_proc varchar2(72) := g_package||'lck';
1167 l_validation_start_date date;
1168 l_validation_end_date date;
1169 --
1170 begin
1171 --
1172 hr_utility.set_location('Entering:'|| l_proc, 10);
1173 --
1174 ben_cop_shd.lck
1175 (p_oipl_id => p_oipl_id
1176 ,p_validation_start_date => l_validation_start_date
1177 ,p_validation_end_date => l_validation_end_date
1178 ,p_object_version_number => p_object_version_number
1179 ,p_effective_date => p_effective_date
1180 ,p_datetrack_mode => p_datetrack_mode);
1181 --
1182 hr_utility.set_location(' Leaving:'||l_proc, 70);
1183 --
1184 end lck;
1185 --
1186 end ben_Option_in_Plan_api;