VIEW: APPS.HR_ATH_DEPENDENT_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 ,TO_CHAR(ecd.dpnt_person_id) name ,ppf.full_name full_name ,ecd.cvg_strt_dt start_date ,NVL(ecd.cvg_thru_dt,TO_DATE('12/31/4712','MM/DD/YYYY')) end_date ,TO_CHAR(ppf.date_of_birth,'MM/DD/YYYY') date_of_birth ,national_identifier national_identifier ,TO_CHAR(ecd.cvg_strt_dt,'MM/DD/YYYY') cvg_strt_dt ,DECODE(TO_CHAR(ecd.cvg_thru_dt,'MM/DD/YYYY'),'12/31/4712','No End Date', TO_CHAR(ecd.cvg_thru_dt,'MM/DD/YYYY')) cvg_thru_dt ,HR_AUTH_BRIDGE.GET_CONTACT_RELATIONSHIPS(pen.person_id,ecd.dpnt_person_id) relationships FROM ben_prtt_enrt_rslt_f pen ,ben_elig_cvrd_dpnt_f ecd ,per_all_people_f ppf ,ben_plip_f plip WHERE pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id AND pen.per_in_ler_id = ecd.per_in_ler_id AND ecd.dpnt_person_id= ppf.person_id AND TRUNC(SYSDATE) BETWEEN ecd.effective_start_date AND ecd.effective_end_date AND ecd.cvg_thru_dt >= ecd.cvg_strt_dt AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND pen.prtt_enrt_rslt_stat_cd IS NULL AND TRUNC(SYSDATE) BETWEEN pen.effective_start_date AND pen.effective_end_date AND pen.pl_id = plip.pl_id(+) AND pen.pgm_id = plip.pgm_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
, TO_CHAR(ECD.DPNT_PERSON_ID) NAME
, PPF.FULL_NAME FULL_NAME
, ECD.CVG_STRT_DT START_DATE
, NVL(ECD.CVG_THRU_DT
, TO_DATE('12/31/4712'
, 'MM/DD/YYYY')) END_DATE
, TO_CHAR(PPF.DATE_OF_BIRTH
, 'MM/DD/YYYY') DATE_OF_BIRTH
, NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, TO_CHAR(ECD.CVG_STRT_DT
, 'MM/DD/YYYY') CVG_STRT_DT
, DECODE(TO_CHAR(ECD.CVG_THRU_DT
, 'MM/DD/YYYY')
, '12/31/4712'
, 'NO END DATE'
, TO_CHAR(ECD.CVG_THRU_DT
, 'MM/DD/YYYY')) CVG_THRU_DT
, HR_AUTH_BRIDGE.GET_CONTACT_RELATIONSHIPS(PEN.PERSON_ID
, ECD.DPNT_PERSON_ID) RELATIONSHIPS
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_ELIG_CVRD_DPNT_F ECD
, PER_ALL_PEOPLE_F PPF
, BEN_PLIP_F PLIP
WHERE PEN.PRTT_ENRT_RSLT_ID = ECD.PRTT_ENRT_RSLT_ID
AND PEN.PER_IN_LER_ID = ECD.PER_IN_LER_ID
AND ECD.DPNT_PERSON_ID= PPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN ECD.EFFECTIVE_START_DATE
AND ECD.EFFECTIVE_END_DATE
AND ECD.CVG_THRU_DT >= ECD.CVG_STRT_DT
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND TRUNC(SYSDATE) BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.PL_ID = PLIP.PL_ID(+)
AND PEN.PGM_ID = PLIP.PGM_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(PLIP.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(PLIP.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))