APPS.BEN_ENROLLMENT_ACTION_ITEMS dependencies on BEN_DSGN_RQMT_F

Line 42: 110.8 15 Jun 98 J Schneider ben_dsgn_rqmt_f now uses oipl_id

38: enrt_actn_f so all action items will
39: suspend enrt if new or cmpltd_dt is NULL
40: 110.7 13 Jun 98 J Schneider l_g_suspend,l_suspend_flag rechecked for
41: problems
42: 110.8 15 Jun 98 J Schneider ben_dsgn_rqmt_f now uses oipl_id
43: 110.9 15 Jun 98 J Schneider redo oipl pl dsgn_rqmt
44: 110.10 16 Jun 98 J Schneider dsgn_rqmt needs more work for DDADDNL
45: 110.11 17 Jun 98 J Schneider recheck for DDADDNL if no dependents found
46: 110.12 18 Jun 98 J Schneider return suspend flag at all times

Line 4697: from ben_dsgn_rqmt_f drq,

4693: select sum(tot_mn_dpnts_rqd_num) tot_mn_dpnts_rqd_num,
4694: sum(tot_mx_dpnts_alwd_num) tot_mx_dpnts_alwd_num
4695: from (select sum(nvl(drq.mn_dpnts_rqd_num, 0 )) tot_mn_dpnts_rqd_num,
4696: sum(nvl(drq.mx_dpnts_alwd_num,9999999999)) tot_mx_dpnts_alwd_num
4697: from ben_dsgn_rqmt_f drq,
4698: ben_oipl_f cop,
4699: ben_prtt_enrt_rslt_f pen
4700: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4701: -- and drq.oipl_id = pen.oipl_id -- 3730053: OPT level DSGN_RQMTS override OIPL level

Line 4706: from ben_dsgn_rqmt_f drq2

4702: and pen.oipl_id = cop.oipl_id
4703: and ( (drq.oipl_id = pen.oipl_id
4704: and not exists
4705: (select null
4706: from ben_dsgn_rqmt_f drq2
4707: where drq2.opt_id = cop.opt_id
4708: and p_effective_date between drq2.effective_start_date
4709: and drq2.effective_end_date))
4710: or cop.opt_id = drq.opt_id)

Line 4726: from ben_dsgn_rqmt_f drq,

4722: and drq.effective_end_date
4723: UNION -- 3730053: Added this to check PL-level designation requirements are specified at PLAN-LEVEL.
4724: select sum(nvl(drq.mn_dpnts_rqd_num, 0 )) tot_mn_dpnts_rqd_num,
4725: sum(nvl(drq.mx_dpnts_alwd_num,9999999999)) tot_mx_dpnts_alwd_num
4726: from ben_dsgn_rqmt_f drq,
4727: ben_prtt_enrt_rslt_f pen
4728: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4729: and drq.pl_id = pen.pl_id
4730: and drq.dsgn_typ_cd = 'DPNT'

Line 4741: from ben_dsgn_rqmt_f drq3

4737: and p_effective_date between drq.effective_start_date
4738: and drq.effective_end_date
4739: and (pen.oipl_id IS NULL
4740: OR NOT EXISTS (select null
4741: from ben_dsgn_rqmt_f drq3
4742: where drq3.oipl_id = pen.oipl_id
4743: and p_effective_date between drq3.effective_start_date and drq3.effective_end_date
4744: UNION ALL
4745: select null

Line 4746: from ben_dsgn_rqmt_f drq4,

4742: where drq3.oipl_id = pen.oipl_id
4743: and p_effective_date between drq3.effective_start_date and drq3.effective_end_date
4744: UNION ALL
4745: select null
4746: from ben_dsgn_rqmt_f drq4,
4747: ben_oipl_f cop
4748: where cop.oipl_id = pen.oipl_id
4749: and drq4.opt_id = cop.opt_id
4750: and p_effective_date between cop.effective_start_date and cop.effective_end_date

Line 5068: ben_dsgn_rqmt_f drq

5064: -- cursor c_min_no_grp modified against Bug 7516987
5065: cursor c_min_no_grp (c_effective_date date) is
5066: select mn_dpnts_rqd_num
5067: from ben_prtt_enrt_rslt_f perslt,
5068: ben_dsgn_rqmt_f drq
5069: where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5070: and perslt.prtt_enrt_rslt_stat_cd is null
5071: and drq.dsgn_typ_cd = 'DPNT'
5072: and drq.grp_rlshp_cd is null

Line 5117: ben_dsgn_rqmt_f drq

5113: -- cursor c_min_typ modified against Bug 7516987
5114: cursor c_min_typ (c_effective_date date) is
5115: select distinct 'X' -- mn_dpnts_rqd_num
5116: from ben_prtt_enrt_rslt_f perslt,
5117: ben_dsgn_rqmt_f drq
5118: where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5119: and drq.dsgn_typ_cd = 'DPNT'
5120: and drq.grp_rlshp_cd is not null
5121: and perslt.prtt_enrt_rslt_stat_cd is null