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