BEN_ELIG_PERSON_DETAILS

Details

Schema: FUSION
Object owner: BEN
Object type: VIEW

Columns

Name
ELIG_PER_ID ELIG_PER_OPT_ID PER_IN_LER_ID PERSON_ID PGM_ID PL_TYP_ID PL_ID OPT_ID LER_ID PTIP_ID PLIP_ID DPNT_OTHR_PL_CVRD_RL_FLAG PRTN_OVRIDN_THRU_DT PL_KEY_EE_FLAG ELIG_FLAG COMP_REF_AMT AGE_VAL LOS_VAL PRTN_END_DT PRTN_STRT_DT WV_CTFN_TYP_CD HRS_WKD_VAL NO_MX_PRTN_OVRID_THRU_FLAG PRTN_OVRIDN_RSN_CD PCT_FL_TM_VAL FRZ_AGE_FLAG REL_PRMRY_ASG_ID BENEFIT_RELATION_NAME BENEFIT_RELATION_ID PRIMARY_REL LEGAL_ENTITY_ID BENEFIT_GROUP_ID ELIG_EFFECTIVE_START_DATE ELIG_EFFECTIVE_END_DATE OPT_EFFECTIVE_START_DATE OPT_EFFECTIVE_END_DATE PTIP_EFFECTIVE_START_DATE PTIP_EFFECTIVE_END_DATE PLIP_EFFECTIVE_START_DATE PLIP_EFFECTIVE_END_DATE PL_TYP_EFFECTIVE_START_DATE PL_TYP_EFFECTIVE_END_DATE PL_TYP2_EFFECTIVE_START_DATE PL_TYP2_EFFECTIVE_END_DATE PL_TYP3_EFFECTIVE_START_DATE PL_TYP3_EFFECTIVE_END_DATE PL_EFFECTIVE_START_DATE PL_EFFECTIVE_END_DATE PL2_EFFECTIVE_START_DATE PL2_EFFECTIVE_END_DATE REL_EFFECTIVE_START_DATE REL_EFFECTIVE_END_DATE GRP_EFFECTIVE_START_DATE GRP_EFFECTIVE_END_DATE ELIG_LAST_UPDATED_BY ELIG_CREATED_BY ELIG_CREATION_DATE ELIG_LAST_UPDATE_DATE ELIG_LAST_UPDATE_LOGIN OPT_LAST_UPDATED_BY OPT_CREATED_BY OPT_CREATION_DATE OPT_LAST_UPDATE_DATE OPT_LAST_UPDATE_LOGIN

Name

ELIG_PER_ID

ELIG_PER_OPT_ID

PER_IN_LER_ID

PERSON_ID

PGM_ID

PL_TYP_ID

PL_ID

OPT_ID

LER_ID

PTIP_ID

PLIP_ID

DPNT_OTHR_PL_CVRD_RL_FLAG

PRTN_OVRIDN_THRU_DT

PL_KEY_EE_FLAG

ELIG_FLAG

COMP_REF_AMT

AGE_VAL

LOS_VAL

PRTN_END_DT

PRTN_STRT_DT

WV_CTFN_TYP_CD

HRS_WKD_VAL

NO_MX_PRTN_OVRID_THRU_FLAG

PRTN_OVRIDN_RSN_CD

PCT_FL_TM_VAL

FRZ_AGE_FLAG

REL_PRMRY_ASG_ID

BENEFIT_RELATION_NAME

BENEFIT_RELATION_ID

PRIMARY_REL

LEGAL_ENTITY_ID

BENEFIT_GROUP_ID

ELIG_EFFECTIVE_START_DATE

ELIG_EFFECTIVE_END_DATE

OPT_EFFECTIVE_START_DATE

OPT_EFFECTIVE_END_DATE

PTIP_EFFECTIVE_START_DATE

PTIP_EFFECTIVE_END_DATE

PLIP_EFFECTIVE_START_DATE

PLIP_EFFECTIVE_END_DATE

PL_TYP_EFFECTIVE_START_DATE

PL_TYP_EFFECTIVE_END_DATE

PL_TYP2_EFFECTIVE_START_DATE

PL_TYP2_EFFECTIVE_END_DATE

PL_TYP3_EFFECTIVE_START_DATE

PL_TYP3_EFFECTIVE_END_DATE

PL_EFFECTIVE_START_DATE

PL_EFFECTIVE_END_DATE

PL2_EFFECTIVE_START_DATE

PL2_EFFECTIVE_END_DATE

REL_EFFECTIVE_START_DATE

REL_EFFECTIVE_END_DATE

GRP_EFFECTIVE_START_DATE

GRP_EFFECTIVE_END_DATE

ELIG_LAST_UPDATED_BY

ELIG_CREATED_BY

ELIG_CREATION_DATE

ELIG_LAST_UPDATE_DATE

ELIG_LAST_UPDATE_LOGIN

OPT_LAST_UPDATED_BY

OPT_CREATED_BY

OPT_CREATION_DATE

OPT_LAST_UPDATE_DATE

OPT_LAST_UPDATE_LOGIN

Query

SQL_Statement
SELECT fact.elig_per_id elig_per_id, opt.elig_per_opt_id elig_per_opt_id, fact.per_in_ler_id per_in_ler_id, fact.person_id person_id, fact.pgm_id pgm_id, nvl(nvl(ptip.pl_typ_id,pl_typ2.pl_typ_id),pl_typ3.pl_typ_id) pl_typ_id, fact.pl_id pl_id, opt.opt_id opt_id, fact.ler_id ler_id, fact.ptip_id ptip_id, fact.plip_id plip_id , fact.DPNT_OTHR_PL_CVRD_RL_FLAG DPNT_OTHR_PL_CVRD_RL_FLAG, fact.PRTN_OVRIDN_THRU_DT PRTN_OVRIDN_THRU_DT, fact.PL_KEY_EE_FLAG PL_KEY_EE_FLAG, fact.ELIG_FLAG ELIG_FLAG , fact.COMP_REF_AMT COMP_REF_AMT, fact.AGE_VAL AGE_VAL, fact.LOS_VAL LOS_VAL, fact.PRTN_END_DT PRTN_END_DT, fact.PRTN_STRT_DT PRTN_STRT_DT, fact.WV_CTFN_TYP_CD WV_CTFN_TYP_CD, fact.HRS_WKD_VAL HRS_WKD_VAL, fact.NO_MX_PRTN_OVRID_THRU_FLAG NO_MX_PRTN_OVRID_THRU_FLAG, fact.PRTN_OVRIDN_RSN_CD PRTN_OVRIDN_RSN_CD, fact.PCT_FL_TM_VAL PCT_FL_TM_VAL, fact.FRZ_AGE_FLAG FRZ_AGE_FLAG, rel.REL_PRMRY_ASG_ID REL_PRMRY_ASG_ID, rel.BENEFIT_RELATION_NAME BENEFIT_RELATION_NAME, rel.BENEFIT_RELATION_ID BENEFIT_RELATION_ID, rel.PRIMARY_REL PRIMARY_REL, rel.LEGAL_ENTITY_ID LEGAL_ENTITY_ID, grp.BENEFIT_GROUP_ID BENEFIT_GROUP_ID, fact.EFFECTIVE_START_DATE elig_EFFECTIVE_START_DATE, fact.EFFECTIVE_END_DATE elig_EFFECTIVE_END_DATE, opt.EFFECTIVE_START_DATE opt_EFFECTIVE_START_DATE, opt.EFFECTIVE_END_DATE opt_EFFECTIVE_END_DATE, ptip.EFFECTIVE_START_DATE ptip_EFFECTIVE_START_DATE, ptip.EFFECTIVE_END_DATE ptip_EFFECTIVE_END_DATE, plip.EFFECTIVE_START_DATE plip_EFFECTIVE_START_DATE, plip.EFFECTIVE_END_DATE plip_EFFECTIVE_END_DATE, pl_typ.EFFECTIVE_START_DATE pl_typ_EFFECTIVE_START_DATE, pl_typ.EFFECTIVE_END_DATE pl_typ_EFFECTIVE_END_DATE, pl_typ2.EFFECTIVE_START_DATE pl_typ2_EFFECTIVE_START_DATE, pl_typ2.EFFECTIVE_END_DATE pl_typ2_EFFECTIVE_END_DATE, pl_typ3.EFFECTIVE_START_DATE pl_typ3_EFFECTIVE_START_DATE, pl_typ3.EFFECTIVE_END_DATE pl_typ3_EFFECTIVE_END_DATE, pl.EFFECTIVE_START_DATE pl_EFFECTIVE_START_DATE, pl.EFFECTIVE_END_DATE pl_EFFECTIVE_END_DATE, pl2.EFFECTIVE_START_DATE pl2_EFFECTIVE_START_DATE, pl2.EFFECTIVE_END_DATE pl2_EFFECTIVE_END_DATE, rel.EFFECTIVE_START_DATE rel_EFFECTIVE_START_DATE, rel.EFFECTIVE_END_DATE rel_EFFECTIVE_END_DATE, grp.EFFECTIVE_START_DATE grp_EFFECTIVE_START_DATE, grp.EFFECTIVE_END_DATE grp_EFFECTIVE_END_DATE, fact.last_updated_by elig_last_updated_by, fact.created_by elig_created_by, fact.creation_date elig_creation_date, fact.last_update_date elig_last_update_date, fact.last_update_login elig_last_update_login, opt.last_updated_by opt_last_updated_by, opt.created_by opt_created_by, opt.creation_date opt_creation_date, opt.last_update_date opt_last_update_date, opt.last_update_login opt_last_update_login FROM ben_elig_per_f fact, ben_elig_per_opt_f opt, ben_ptip_f ptip, ben_plip_f plip, ben_pl_typ_f pl_typ, ben_pl_typ_f pl_typ2, ben_pl_typ_f pl_typ3, ben_pl_f pl, ben_pl_f pl2, ben_benefit_relations_f rel, BEN_PER_BENEFIT_GROUP_F grp WHERE fact.elig_per_id = opt.elig_per_id(+) AND fact.ptip_id = ptip.ptip_id(+) AND ptip.pl_typ_id = pl_typ.pl_typ_id(+) AND fact.pl_id = pl.pl_id(+) AND pl.pl_typ_id = pl_typ2.pl_typ_id (+) AND fact.plip_id = plip.plip_id (+) AND plip.pl_id = pl2.pl_id (+) AND pl2.pl_typ_id = pl_typ3.pl_typ_id (+) and fact.per_in_ler_id = opt.per_in_ler_id (+) and fact.benefit_relation_id = rel.benefit_relation_id and fact.PERSON_ID = grp.PERSON_ID(+) and fact.pl_id is not null

SQL_Statement

SELECT fact.elig_per_id elig_per_id, opt.elig_per_opt_id elig_per_opt_id, fact.per_in_ler_id per_in_ler_id,

fact.person_id person_id, fact.pgm_id pgm_id,

nvl(nvl(ptip.pl_typ_id,pl_typ2.pl_typ_id),pl_typ3.pl_typ_id) pl_typ_id,

fact.pl_id pl_id, opt.opt_id opt_id, fact.ler_id ler_id, fact.ptip_id ptip_id,

fact.plip_id plip_id , fact.DPNT_OTHR_PL_CVRD_RL_FLAG DPNT_OTHR_PL_CVRD_RL_FLAG,

fact.PRTN_OVRIDN_THRU_DT PRTN_OVRIDN_THRU_DT,

fact.PL_KEY_EE_FLAG PL_KEY_EE_FLAG, fact.ELIG_FLAG ELIG_FLAG ,

fact.COMP_REF_AMT COMP_REF_AMT, fact.AGE_VAL AGE_VAL,

fact.LOS_VAL LOS_VAL, fact.PRTN_END_DT PRTN_END_DT,

fact.PRTN_STRT_DT PRTN_STRT_DT, fact.WV_CTFN_TYP_CD WV_CTFN_TYP_CD,

fact.HRS_WKD_VAL HRS_WKD_VAL, fact.NO_MX_PRTN_OVRID_THRU_FLAG NO_MX_PRTN_OVRID_THRU_FLAG,

fact.PRTN_OVRIDN_RSN_CD PRTN_OVRIDN_RSN_CD, fact.PCT_FL_TM_VAL PCT_FL_TM_VAL,

fact.FRZ_AGE_FLAG FRZ_AGE_FLAG, rel.REL_PRMRY_ASG_ID REL_PRMRY_ASG_ID,

rel.BENEFIT_RELATION_NAME BENEFIT_RELATION_NAME, rel.BENEFIT_RELATION_ID BENEFIT_RELATION_ID,

rel.PRIMARY_REL PRIMARY_REL, rel.LEGAL_ENTITY_ID LEGAL_ENTITY_ID,

grp.BENEFIT_GROUP_ID BENEFIT_GROUP_ID, fact.EFFECTIVE_START_DATE elig_EFFECTIVE_START_DATE,

fact.EFFECTIVE_END_DATE elig_EFFECTIVE_END_DATE, opt.EFFECTIVE_START_DATE opt_EFFECTIVE_START_DATE,

opt.EFFECTIVE_END_DATE opt_EFFECTIVE_END_DATE, ptip.EFFECTIVE_START_DATE ptip_EFFECTIVE_START_DATE,

ptip.EFFECTIVE_END_DATE ptip_EFFECTIVE_END_DATE, plip.EFFECTIVE_START_DATE plip_EFFECTIVE_START_DATE,

plip.EFFECTIVE_END_DATE plip_EFFECTIVE_END_DATE, pl_typ.EFFECTIVE_START_DATE pl_typ_EFFECTIVE_START_DATE,

pl_typ.EFFECTIVE_END_DATE pl_typ_EFFECTIVE_END_DATE, pl_typ2.EFFECTIVE_START_DATE pl_typ2_EFFECTIVE_START_DATE,

pl_typ2.EFFECTIVE_END_DATE pl_typ2_EFFECTIVE_END_DATE, pl_typ3.EFFECTIVE_START_DATE pl_typ3_EFFECTIVE_START_DATE,

pl_typ3.EFFECTIVE_END_DATE pl_typ3_EFFECTIVE_END_DATE, pl.EFFECTIVE_START_DATE pl_EFFECTIVE_START_DATE,

pl.EFFECTIVE_END_DATE pl_EFFECTIVE_END_DATE, pl2.EFFECTIVE_START_DATE pl2_EFFECTIVE_START_DATE,

pl2.EFFECTIVE_END_DATE pl2_EFFECTIVE_END_DATE, rel.EFFECTIVE_START_DATE rel_EFFECTIVE_START_DATE,

rel.EFFECTIVE_END_DATE rel_EFFECTIVE_END_DATE, grp.EFFECTIVE_START_DATE grp_EFFECTIVE_START_DATE,

grp.EFFECTIVE_END_DATE grp_EFFECTIVE_END_DATE,

fact.last_updated_by elig_last_updated_by,

fact.created_by elig_created_by,

fact.creation_date elig_creation_date,

fact.last_update_date elig_last_update_date,

fact.last_update_login elig_last_update_login,

opt.last_updated_by opt_last_updated_by,

opt.created_by opt_created_by,

opt.creation_date opt_creation_date,

opt.last_update_date opt_last_update_date,

opt.last_update_login opt_last_update_login

FROM ben_elig_per_f fact,

ben_elig_per_opt_f opt,

ben_ptip_f ptip,

ben_plip_f plip,

ben_pl_typ_f pl_typ,

ben_pl_typ_f pl_typ2,

ben_pl_typ_f pl_typ3,

ben_pl_f pl,

ben_pl_f pl2,

ben_benefit_relations_f rel,

BEN_PER_BENEFIT_GROUP_F grp

WHERE fact.elig_per_id = opt.elig_per_id(+)

AND fact.ptip_id = ptip.ptip_id(+)

AND ptip.pl_typ_id = pl_typ.pl_typ_id(+)

AND fact.pl_id = pl.pl_id(+)

AND pl.pl_typ_id = pl_typ2.pl_typ_id (+)

AND fact.plip_id = plip.plip_id (+)

AND plip.pl_id = pl2.pl_id (+)

AND pl2.pl_typ_id = pl_typ3.pl_typ_id (+)

and fact.per_in_ler_id = opt.per_in_ler_id (+)

and fact.benefit_relation_id = rel.benefit_relation_id

and fact.PERSON_ID = grp.PERSON_ID(+)

and fact.pl_id is not null