APPS.BEN_MANAGE_UNRES_LIFE_EVENTS dependencies on BEN_ELIG_PER_ELCTBL_CHC

Line 55: 05-Dec-09 krupani 115.24 Bug 9181975 - elig_flag is not updated in ben_elig_per_elctbl_chc

51: returning from the proc.
52: 04-APR-06 ssarkar 115.19 Bug 5055119 - added end_date_elig_per_rows
53: 10-apr-06 ssarkar 115.20 5055119 - redesigned the code of end_date_elig_per_rows
54: 29-Nov-06 gsehgal 115.21 Bug 5658405 - modified procedure end_date_elig_per_rows
55: 05-Dec-09 krupani 115.24 Bug 9181975 - elig_flag is not updated in ben_elig_per_elctbl_chc
56: while running unrestricted. Fixed the same.
57: */
58:
59: --------------------------------------------------------------------------------

Line 111: from ben_elig_per_elctbl_chc epe,

107: --
108: */
109: cursor c_sspndd_enrts is
110: select epe.elig_per_elctbl_chc_id, epe.object_version_number -- 3638279: Added ovn;
111: from ben_elig_per_elctbl_chc epe,
112: ben_prtt_enrt_rslt_f pen
113: where pen.person_id = p_person_id
114: and pen.per_in_ler_id = p_per_in_ler_id
115: and epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id

Line 133: from ben_elig_per_elctbl_chc

129: */
130: --
131: cursor c_elig_per_elctbl_chc is
132: select object_version_number
133: from ben_elig_per_elctbl_chc
134: where elig_per_elctbl_chc_id = l_pending;
135: --
136: cursor c_elig_per_elctbl_chc_2 is
137: select elig_per_elctbl_chc_id,

Line 139: from ben_elig_per_elctbl_chc

135: --
136: cursor c_elig_per_elctbl_chc_2 is
137: select elig_per_elctbl_chc_id,
138: object_version_number
139: from ben_elig_per_elctbl_chc
140: where per_in_ler_id = p_per_in_ler_id
141: and in_pndg_wkflow_flag = 'Y';
142: --
143: cursor c_elig_per_elctbl_chc_3 is

Line 146: from ben_elig_per_elctbl_chc

142: --
143: cursor c_elig_per_elctbl_chc_3 is
144: select elig_per_elctbl_chc_id,
145: object_version_number
146: from ben_elig_per_elctbl_chc
147: where per_in_ler_id = p_per_in_ler_id
148: and in_pndg_wkflow_flag <> 'Y'
149: and pil_elctbl_chc_popl_id in
150: (select pil_elctbl_chc_popl_id

Line 155: from ben_elig_per_elctbl_chc epe

151: from ben_pil_elctbl_chc_popl pel
152: where pel.per_in_ler_id = p_per_in_ler_id and
153: exists
154: ( select null
155: from ben_elig_per_elctbl_chc epe
156: where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
157: and nvl(epe.in_pndg_wkflow_flag,'N') in ('Y', 'S') ));
158: --
159: l_object_version_number number;

Line 226: update ben_elig_per_elctbl_chc

222: );
223: --
224: /*
225: hr_utility.set_location ('sspndd epe = '||l_epe_rec.elig_per_elctbl_chc_id,20);
226: update ben_elig_per_elctbl_chc
227: set in_pndg_wkflow_flag = 'S'
228: where elig_per_elctbl_chc_id = l_epe_rec.elig_per_elctbl_chc_id;
229: */
230: --

Line 268: type epetab is table of ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type;

264: is
265: --
266: l_rec benutils.g_active_life_event;
267:
268: type epetab is table of ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type;
269: t_epe_tbl epetab;
270:
271: l_object_version_number number ;
272: l_package varchar2(100):= 'delete_elctbl_choice';

Line 280: (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc

276: cursor c_enrt_rt is
277: select *
278: from ben_enrt_rt
279: where elig_per_elctbl_chc_id in
280: (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
281: where per_in_ler_id = p_rec.per_in_ler_id)
282: union
283: select *
284: from ben_enrt_rt

Line 288: (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc

284: from ben_enrt_rt
285: where enrt_bnft_id in
286: (select enrt_bnft_id from ben_enrt_bnft
287: where elig_per_elctbl_chc_id in
288: (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
289: where per_in_ler_id = p_rec.per_in_ler_id));
290: */
291: --
292: cursor c_pil_popl is

Line 305: from ben_elig_per_elctbl_chc epe

301: and pl_id is not null;
302: --
303: cursor c_epe(p_per_in_ler_id number) is
304: select epe.elig_per_elctbl_chc_id
305: from ben_elig_per_elctbl_chc epe
306: where epe.per_in_ler_id = p_per_in_ler_id
307: and epe.pil_elctbl_chc_popl_id in
308: (select pel.pil_elctbl_chc_popl_id
309: from ben_pil_elctbl_chc_popl pel

Line 313: from ben_elig_per_elctbl_chc epe2

309: from ben_pil_elctbl_chc_popl pel
310: where pel.per_in_ler_id = p_per_in_ler_id
311: and not exists
312: (select null
313: from ben_elig_per_elctbl_chc epe2
314: where pel.pil_elctbl_chc_popl_id=epe2.pil_elctbl_chc_popl_id
315: and nvl(epe2.in_pndg_wkflow_flag,'N') in ('Y', 'S')));
316: --
317: cursor c_epe_ch (p_elig_per_elctbl_chc_id number) is

Line 371: from ben_elig_per_elctbl_chc

367: ,APPROVAL_STATUS_CD
368: ,elig_per_elctbl_chc_id
369: ,object_version_number
370: ,null mark_delete
371: from ben_elig_per_elctbl_chc
372: where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
373: --
374: cursor c_enb_ch (p_elig_per_elctbl_chc_id number) is
375: select enrt_bnft_id

Line 561: delete from ben_elig_per_elctbl_chc

557: delete from ben_elig_dpnt
558: where elig_per_elctbl_chc_id = t_epe_tbl(i);
559:
560: forall i in 1..t_epe_tbl.last
561: delete from ben_elig_per_elctbl_chc
562: where elig_per_elctbl_chc_id = t_epe_tbl(i);
563: */
564: for i in 1..t_epe_tbl.last loop
565: hr_utility.set_location (' within loop'||t_epe_tbl(i),10);

Line 569: update ben_elig_per_elctbl_chc set prtt_enrt_rslt_id = null

565: hr_utility.set_location (' within loop'||t_epe_tbl(i),10);
566: open c_epe_ch (t_epe_tbl(i));
567: fetch c_epe_ch into g_unrest_epe_instance(i);
568: --
569: update ben_elig_per_elctbl_chc set prtt_enrt_rslt_id = null
570: where elig_per_elctbl_chc_id = t_epe_tbl(i);
571: --
572: close c_epe_ch;
573: end loop;

Line 665: from ben_elig_per_elctbl_chc epe

661: delete from ben_pil_elctbl_chc_popl pel
662: where per_in_ler_id = p_rec.per_in_ler_id
663: and not exists
664: (select null
665: from ben_elig_per_elctbl_chc epe
666: where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
667: and nvl(epe.in_pndg_wkflow_flag,'N') in ('Y', 'S') ) ;
668: --
669: -- Reset back 'Z' value of the in_pndg_wkflow_flag to 'N'.

Line 674: update ben_elig_per_elctbl_chc

670: -- As it was just used to suppress the deletion of electable choice
671: -- data associated with the suspended enrollments.
672: --
673: /*
674: update ben_elig_per_elctbl_chc
675: set in_pndg_wkflow_flag = 'N'
676: where in_pndg_wkflow_flag = 'Z'
677: and per_in_ler_id = p_rec.per_in_ler_id;
678: */

Line 1908: delete from ben_elig_per_elctbl_chc where elig_per_elctbl_chc_id =

1904: --
1905: if g_unrest_epe_instance(i).mark_delete is null then
1906: --
1907: hr_utility.set_location ('Delete Elig Id '||g_unrest_epe_instance(i).elig_per_elctbl_chc_id,11);
1908: delete from ben_elig_per_elctbl_chc where elig_per_elctbl_chc_id =
1909: g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1910: --
1911: end if;
1912: --

Line 1942: delete from ben_elig_per_elctbl_chc where elig_per_elctbl_chc_id =

1938: and g_unrest_epe_instance(i).comp_lvl_cd in ('OIPL','PLAN') then
1939: -- and g_unrest_epe_instance(i).elctbl_flag = 'Y' -- bug 4761065 : Need to delete epe regardless whether its elctbl_flag is Y/N .
1940: --
1941: hr_utility.set_location ('Delete Elig Id in clear_epe '||g_unrest_epe_instance(i).elig_per_elctbl_chc_id,11);
1942: delete from ben_elig_per_elctbl_chc where elig_per_elctbl_chc_id =
1943: g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1944: --
1945: else
1946: --