Product: | BEN - Advanced Benefits |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | APPS.BEN_PTIP_D |
SELECT CTP.ROWID ROW_ID
, CTP.PTIP_ID
, CTP.EFFECTIVE_START_DATE
, CTP.EFFECTIVE_END_DATE
, COORD_CVG_FOR_ALL_PLS.MEANING
, DPNT_DSGN.MEANING
, DPNT_CVG_NO_CTFN_RQD.MEANING
, DPNT_DOB_RQD.MEANING
, PTIP_STAT.MEANING
, CTP.MX_CVG_ALWD_AMT
, CTP.MX_ENRD_ALWD_OVRID_NUM
, CTP.MN_ENRD_RQD_OVRID_NUM
, NO_MX_PL_TYP_OVRID.MEANING
, CTP.ORDR_NUM
, PRVDS_CR.MEANING
, CTP.RQD_PERD_ENRT_NENRT_VAL
, RQD_PERD_ENRT_NENRT_TM.MEANING
, WVBL.MEANING
, DRVD_FCTR_DPNT_CVG.MEANING
, NO_MN_PL_TYP_OVERID.MEANING
, SBJ_TO_SPS_LF_INS_MX.MEANING
, SBJ_TO_DPNT_LF_INS_MX.MEANING
, USE_TO_SUM_EE_LF_INS.MEANING
, CTP.IVR_IDENT
, CTP.URL_REF_NAME
, DRVBL_FCTR_APLS_RTS.MEANING
, DRVBL_FCTR_PRTN_ELIG.MEANING
, PGM.NAME
, CMBN_PTIP.NAME
, CMBN_PTIP_OPT.NAME
, CTP.LAST_UPDATE_DATE
, FUSER.USER_NAME
, CTP.SHORT_NAME
, CTP.SHORT_CODE
, CTP.LEGISLATION_CODE
, CTP.LEGISLATION_SUBGROUP
FROM BEN_PTIP_F CTP
, FND_USER FUSER
, BEN_PGM_F PGM
, BEN_PL_TYP_F PL_TYP
, BEN_CMBN_PTIP_F CMBN_PTIP
, BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT
, BEN_ACRS_PTIP_CVG_F ACRS_PTIP_CVG
, HR_LOOKUPS DPNT_DSGN
, HR_LOOKUPS PTIP_STAT
, HR_LOOKUPS RQD_PERD_ENRT_NENRT_TM
, HR_LOOKUPS COORD_CVG_FOR_ALL_PLS
, HR_LOOKUPS DPNT_CVG_NO_CTFN_RQD
, HR_LOOKUPS DPNT_DOB_RQD
, HR_LOOKUPS NO_MX_PL_TYP_OVRID
, HR_LOOKUPS PRVDS_CR
, HR_LOOKUPS WVBL
, HR_LOOKUPS DRVD_FCTR_DPNT_CVG
, HR_LOOKUPS NO_MN_PL_TYP_OVERID
, HR_LOOKUPS SBJ_TO_SPS_LF_INS_MX
, HR_LOOKUPS SBJ_TO_DPNT_LF_INS_MX
, HR_LOOKUPS USE_TO_SUM_EE_LF_INS
, HR_LOOKUPS DRVBL_FCTR_APLS_RTS
, HR_LOOKUPS DRVBL_FCTR_PRTN_ELIG
WHERE FUSER.USER_ID (+)= CTP .LAST_UPDATED_BY
AND PGM.PGM_ID (+)= CTP .PGM_ID
AND CTP.EFFECTIVE_START_DATE BETWEEN PGM.EFFECTIVE_START_DATE(+)
AND PGM.EFFECTIVE_END_DATE(+)
AND PL_TYP.PL_TYP_ID (+)= CTP .PL_TYP_ID
AND CTP.EFFECTIVE_START_DATE BETWEEN PL_TYP.EFFECTIVE_START_DATE(+)
AND PL_TYP.EFFECTIVE_END_DATE(+)
AND CMBN_PTIP.CMBN_PTIP_ID (+)= CTP .CMBN_PTIP_ID
AND CTP.EFFECTIVE_START_DATE BETWEEN CMBN_PTIP.EFFECTIVE_START_DATE(+)
AND CMBN_PTIP.EFFECTIVE_END_DATE(+)
AND CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID (+)= CTP .CMBN_PTIP_OPT_ID
AND CTP.EFFECTIVE_START_DATE BETWEEN CMBN_PTIP_OPT.EFFECTIVE_START_DATE(+)
AND CMBN_PTIP_OPT.EFFECTIVE_END_DATE(+)
AND ACRS_PTIP_CVG.ACRS_PTIP_CVG_ID (+)= CTP.ACRS_PTIP_CVG_ID
AND CTP.EFFECTIVE_START_DATE BETWEEN ACRS_PTIP_CVG.EFFECTIVE_START_DATE(+)
AND ACRS_PTIP_CVG.EFFECTIVE_END_DATE(+)
AND DPNT_DSGN.LOOKUP_CODE (+)= CTP .DPNT_DSGN_CD
AND DPNT_DSGN.LOOKUP_TYPE (+)= 'BEN_DPNT_DSGN'
AND PTIP_STAT.LOOKUP_CODE (+)= CTP .PTIP_STAT_CD
AND PTIP_STAT.LOOKUP_TYPE (+)= 'BEN_STAT'
AND RQD_PERD_ENRT_NENRT_TM.LOOKUP_CODE (+)= CTP .RQD_PERD_ENRT_NENRT_TM_UOM
AND RQD_PERD_ENRT_NENRT_TM.LOOKUP_TYPE (+)= 'BEN_RQD_PERD_ENRT_NENRT_TM_UOM'
AND COORD_CVG_FOR_ALL_PLS.LOOKUP_CODE (+)= CTP .COORD_CVG_FOR_ALL_PLS_FLAG
AND COORD_CVG_FOR_ALL_PLS.LOOKUP_TYPE (+)= 'YES_NO'
AND DPNT_CVG_NO_CTFN_RQD.LOOKUP_CODE (+)= CTP .DPNT_CVG_NO_CTFN_RQD_FLAG
AND DPNT_CVG_NO_CTFN_RQD.LOOKUP_TYPE (+)= 'YES_NO'
AND DPNT_DOB_RQD.LOOKUP_CODE (+)= CTP .DPNT_DOB_RQD_FLAG
AND DPNT_DOB_RQD.LOOKUP_TYPE (+)= 'YES_NO'
AND NO_MX_PL_TYP_OVRID.LOOKUP_CODE (+)= CTP .NO_MX_PL_TYP_OVRID_FLAG
AND NO_MX_PL_TYP_OVRID.LOOKUP_TYPE (+)= 'YES_NO'
AND PRVDS_CR.LOOKUP_CODE (+)= CTP .PRVDS_CR_FLAG
AND PRVDS_CR.LOOKUP_TYPE (+)= 'YES_NO'
AND WVBL.LOOKUP_CODE (+)= CTP .WVBL_FLAG
AND WVBL.LOOKUP_TYPE (+)= 'YES_NO'
AND DRVD_FCTR_DPNT_CVG.LOOKUP_CODE (+)= CTP .DRVD_FCTR_DPNT_CVG_FLAG
AND DRVD_FCTR_DPNT_CVG.LOOKUP_TYPE (+)= 'YES_NO'
AND DRVBL_FCTR_APLS_RTS.LOOKUP_CODE (+)= CTP .DRVBL_FCTR_APLS_RTS_FLAG
AND DRVBL_FCTR_APLS_RTS.LOOKUP_TYPE (+)= 'YES_NO'
AND DRVBL_FCTR_PRTN_ELIG.LOOKUP_CODE (+)= CTP .DRVBL_FCTR_PRTN_ELIG_FLAG
AND DRVBL_FCTR_PRTN_ELIG.LOOKUP_TYPE (+)= 'YES_NO'
AND NO_MN_PL_TYP_OVERID.LOOKUP_CODE (+)= CTP .NO_MN_PL_TYP_OVERID_FLAG
AND NO_MN_PL_TYP_OVERID.LOOKUP_TYPE (+)= 'YES_NO'
AND SBJ_TO_SPS_LF_INS_MX.LOOKUP_CODE (+)= CTP .SBJ_TO_SPS_LF_INS_MX_FLAG
AND SBJ_TO_SPS_LF_INS_MX.LOOKUP_TYPE (+)= 'YES_NO'
AND SBJ_TO_DPNT_LF_INS_MX.LOOKUP_CODE (+)= CTP .SBJ_TO_DPNT_LF_INS_MX_FLAG
AND SBJ_TO_DPNT_LF_INS_MX.LOOKUP_TYPE (+)= 'YES_NO'
AND USE_TO_SUM_EE_LF_INS.LOOKUP_CODE (+)= CTP .USE_TO_SUM_EE_LF_INS_FLAG
AND USE_TO_SUM_EE_LF_INS.LOOKUP_TYPE (+)= 'YES_NO'