VIEW: APPS.HR_ATH_ENROLL_ALL_PLANS_V

Source

View Text - Preformatted

SELECT TO_CHAR(pen.person_id) employee_id ,TO_CHAR(pen.pl_id)||'A'||TO_CHAR(plip.plip_id) plan_id ,pen.enrt_cvg_strt_dt start_date ,NVL(pen.enrt_cvg_thru_dt,TO_DATE('12/31/4712','MM/DD/YYYY')) end_date ,opt.name || DECODE(pen.sspndd_flag,'Y',FND_MESSAGE.GET_STRING('BEN','BEN_92677_SUSPENDED_PLAN'),'N',NULL)|| HR_AUTH_BRIDGE.GET_INTERIM_FLAG(pen.prtt_enrt_rslt_id,pen.business_group_id) coverage_tier ,TO_CHAR(pen.bnft_amt) coverage_amount ,HR_AUTH_BRIDGE.GET_COVERAGE(pen.prtt_enrt_rslt_id,pen.per_in_ler_id,'EEIC','EE') coverage_cost_eeic ,HR_AUTH_BRIDGE.GET_COVERAGE(pen.prtt_enrt_rslt_id,pen.per_in_ler_id,'EEPYC','EE') coverage_cost_eepyc ,HR_AUTH_BRIDGE.GET_COVERAGE(pen.prtt_enrt_rslt_id,pen.per_in_ler_id,'ERPYC','ER') er_contribution_erpyc,HR_AUTH_BRIDGE.GET_BENEFICIARIES(pen.prtt_enrt_rslt_id,pen.per_in_ler_id,'PRIMY') primary_beneficiaries ,HR_AUTH_BRIDGE.GET_BENEFICIARIES(pen.prtt_enrt_rslt_id,pen.per_in_ler_id,'CNTNGNT') contingent_beneficiaries ,HR_AUTH_BRIDGE.GET_PRIMARY_CARE_PROVIDERS(pen.prtt_enrt_rslt_id,pen.business_group_id) primary_care_providers ,TO_CHAR(pen.enrt_cvg_strt_dt,'MM/DD/YYYY') enrt_cvg_strt_dt ,DECODE(TO_CHAR(pen.enrt_cvg_thru_dt,'MM/DD/YYYY'),'12/31/4712','No End Date', TO_CHAR(pen.enrt_cvg_thru_dt,'MM/DD/YYYY')) enrt_cvg_thru_dt FROM ben_prtt_enrt_rslt_f pen ,ben_oipl_f oipl ,ben_opt_f opt ,ben_plip_f plip WHERE pen.oipl_id = oipl.oipl_id(+) AND oipl.opt_id = opt.opt_id(+) AND TRUNC(SYSDATE) BETWEEN NVL(oipl.effective_start_date,TRUNC(SYSDATE)) AND NVL(oipl.effective_end_date,TRUNC(SYSDATE)) AND TRUNC(SYSDATE) BETWEEN NVL(opt.effective_start_date,TRUNC(SYSDATE)) AND NVL(opt.effective_end_date,TRUNC(SYSDATE)) AND pen.prtt_enrt_rslt_stat_cd IS NULL AND TRUNC(SYSDATE) BETWEEN pen.effective_start_date AND pen.effective_end_date AND pen.enrt_cvg_thru_dt >= pen.enrt_cvg_strt_dt AND pen.pgm_id = plip.pgm_id(+) AND pen.pl_id = plip.pl_id(+) AND TRUNC(SYSDATE) BETWEEN NVL(plip.effective_start_date,trunc(SYSDATE)) AND NVL(plip.effective_end_date,trunc(SYSDATE))

View Text - HTML Formatted

SELECT TO_CHAR(PEN.PERSON_ID) EMPLOYEE_ID
, TO_CHAR(PEN.PL_ID)||'A'||TO_CHAR(PLIP.PLIP_ID) PLAN_ID
, PEN.ENRT_CVG_STRT_DT START_DATE
, NVL(PEN.ENRT_CVG_THRU_DT
, TO_DATE('12/31/4712'
, 'MM/DD/YYYY')) END_DATE
, OPT.NAME || DECODE(PEN.SSPNDD_FLAG
, 'Y'
, FND_MESSAGE.GET_STRING('BEN'
, 'BEN_92677_SUSPENDED_PLAN')
, 'N'
, NULL)|| HR_AUTH_BRIDGE.GET_INTERIM_FLAG(PEN.PRTT_ENRT_RSLT_ID
, PEN.BUSINESS_GROUP_ID) COVERAGE_TIER
, TO_CHAR(PEN.BNFT_AMT) COVERAGE_AMOUNT
, HR_AUTH_BRIDGE.GET_COVERAGE(PEN.PRTT_ENRT_RSLT_ID
, PEN.PER_IN_LER_ID
, 'EEIC'
, 'EE') COVERAGE_COST_EEIC
, HR_AUTH_BRIDGE.GET_COVERAGE(PEN.PRTT_ENRT_RSLT_ID
, PEN.PER_IN_LER_ID
, 'EEPYC'
, 'EE') COVERAGE_COST_EEPYC
, HR_AUTH_BRIDGE.GET_COVERAGE(PEN.PRTT_ENRT_RSLT_ID
, PEN.PER_IN_LER_ID
, 'ERPYC'
, 'ER') ER_CONTRIBUTION_ERPYC
, HR_AUTH_BRIDGE.GET_BENEFICIARIES(PEN.PRTT_ENRT_RSLT_ID
, PEN.PER_IN_LER_ID
, 'PRIMY') PRIMARY_BENEFICIARIES
, HR_AUTH_BRIDGE.GET_BENEFICIARIES(PEN.PRTT_ENRT_RSLT_ID
, PEN.PER_IN_LER_ID
, 'CNTNGNT') CONTINGENT_BENEFICIARIES
, HR_AUTH_BRIDGE.GET_PRIMARY_CARE_PROVIDERS(PEN.PRTT_ENRT_RSLT_ID
, PEN.BUSINESS_GROUP_ID) PRIMARY_CARE_PROVIDERS
, TO_CHAR(PEN.ENRT_CVG_STRT_DT
, 'MM/DD/YYYY') ENRT_CVG_STRT_DT
, DECODE(TO_CHAR(PEN.ENRT_CVG_THRU_DT
, 'MM/DD/YYYY')
, '12/31/4712'
, 'NO END DATE'
, TO_CHAR(PEN.ENRT_CVG_THRU_DT
, 'MM/DD/YYYY')) ENRT_CVG_THRU_DT
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PLIP_F PLIP
WHERE PEN.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(OIPL.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(OPT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND TRUNC(SYSDATE) BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.ENRT_CVG_THRU_DT >= PEN.ENRT_CVG_STRT_DT
AND PEN.PGM_ID = PLIP.PGM_ID(+)
AND PEN.PL_ID = PLIP.PL_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(PLIP.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(PLIP.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))