View: PER_EMPLOYEE_BENEFITS_V

Product:	PER - Human Resources
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PER_EMPLOYEE_BENEFITS_V

View Text

SELECT EE.ASSIGNMENT_ID ASSIGNMENT_ID
, ETTL.ELEMENT_NAME ELEMENT_NAME
, ET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, BC2.BENEFIT_CLASSIFICATION_NAME BENEFIT_CLASSIFICATION_NAME
, NVL(EEV_COV.SCREEN_ENTRY_VALUE
, IV_COV.DEFAULT_VALUE) COVERAGE
, L1.MEANING COVERAGE_MEANING
, DECODE(BC2.COBRA_FLAG
, 'Y'
, L2.MEANING
, L3.MEANING) COBRA
, L4.MEANING UOM
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(EEV_ER.SCREEN_ENTRY_VALUE)
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(BC.EMPLOYER_CONTRIBUTION)
, FND_NUMBER.CANONICAL_TO_NUMBER(IV_ER.DEFAULT_VALUE))) ER_CONT
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(EEV_EE.SCREEN_ENTRY_VALUE)
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(BC.EMPLOYEE_CONTRIBUTION)
, FND_NUMBER.CANONICAL_TO_NUMBER(IV_EE.DEFAULT_VALUE))) EE_CONT
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(EEV_ER.SCREEN_ENTRY_VALUE)
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(BC.EMPLOYER_CONTRIBUTION)
, FND_NUMBER.CANONICAL_TO_NUMBER(IV_ER.DEFAULT_VALUE))) + NVL(FND_NUMBER.CANONICAL_TO_NUMBER(EEV_EE.SCREEN_ENTRY_VALUE)
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(BC.EMPLOYEE_CONTRIBUTION)
, FND_NUMBER.CANONICAL_TO_NUMBER(IV_EE.DEFAULT_VALUE))) TOTAL_CONT
FROM HR_LOOKUPS L1
, HR_LOOKUPS L3
, HR_LOOKUPS L2
, HR_LOOKUPS L4
, PAY_INPUT_VALUES_F IV_COV
, PAY_INPUT_VALUES_F IV_EE
, PAY_INPUT_VALUES_F IV_ER
, PAY_ELEMENT_ENTRY_VALUES_F EEV_COV
, PAY_ELEMENT_ENTRY_VALUES_F EEV_ER
, PAY_ELEMENT_ENTRY_VALUES_F EEV_EE
, BEN_BENEFIT_CONTRIBUTIONS_F BC
, BEN_BENEFIT_CLASSIFICATIONS BC2
, PAY_ELEMENT_ENTRIES_F EE
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_TYPES_F ET
, PAY_ELEMENT_TYPES_F_TL ETTL
, FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID')
AND S.EFFECTIVE_DATE BETWEEN ET.EFFECTIVE_START_DATE
AND ET.EFFECTIVE_END_DATE
AND EL.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID
AND S.EFFECTIVE_DATE BETWEEN EL.EFFECTIVE_START_DATE
AND EL.EFFECTIVE_END_DATE
AND EE.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND S.EFFECTIVE_DATE BETWEEN EE.EFFECTIVE_START_DATE
AND EE.EFFECTIVE_END_DATE
AND BC2.BENEFIT_CLASSIFICATION_ID = ET.BENEFIT_CLASSIFICATION_ID
AND BC2.CONTRIBUTIONS_USED = 'Y'
AND BC.ELEMENT_TYPE_ID(+) = ET.ELEMENT_TYPE_ID
AND ( S.EFFECTIVE_DATE BETWEEN BC.EFFECTIVE_START_DATE
AND BC.EFFECTIVE_END_DATE
AND BC.COVERAGE_TYPE = NVL(EEV_COV.SCREEN_ENTRY_VALUE
, IV_COV.DEFAULT_VALUE) OR BC.ELEMENT_TYPE_ID IS NULL )
AND IV_COV.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID
AND UPPER(IV_COV.NAME) = 'COVERAGE'
AND S.EFFECTIVE_DATE BETWEEN IV_COV.EFFECTIVE_START_DATE
AND IV_COV.EFFECTIVE_END_DATE
AND IV_ER.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID
AND UPPER(IV_ER.NAME) = 'ER CONTR'
AND S.EFFECTIVE_DATE BETWEEN IV_ER.EFFECTIVE_START_DATE
AND IV_ER.EFFECTIVE_END_DATE
AND IV_EE.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID
AND UPPER(IV_EE.NAME) = 'EE CONTR'
AND S.EFFECTIVE_DATE BETWEEN IV_EE.EFFECTIVE_START_DATE
AND IV_EE.EFFECTIVE_END_DATE
AND EEV_ER.ELEMENT_ENTRY_ID = EE.ELEMENT_ENTRY_ID
AND EEV_ER.INPUT_VALUE_ID = IV_ER.INPUT_VALUE_ID
AND S.EFFECTIVE_DATE BETWEEN EEV_ER.EFFECTIVE_START_DATE
AND EEV_ER.EFFECTIVE_END_DATE
AND EEV_EE.ELEMENT_ENTRY_ID = EE.ELEMENT_ENTRY_ID
AND EEV_EE.INPUT_VALUE_ID = IV_EE.INPUT_VALUE_ID
AND S.EFFECTIVE_DATE BETWEEN EEV_EE.EFFECTIVE_START_DATE
AND EEV_EE.EFFECTIVE_END_DATE
AND EEV_COV.ELEMENT_ENTRY_ID = EE.ELEMENT_ENTRY_ID
AND EEV_COV.INPUT_VALUE_ID = IV_COV.INPUT_VALUE_ID
AND S.EFFECTIVE_DATE BETWEEN EEV_COV.EFFECTIVE_START_DATE
AND EEV_COV.EFFECTIVE_END_DATE
AND L1.LOOKUP_TYPE = 'US_BENEFIT_COVERAGE'
AND L1.LOOKUP_CODE = NVL(EEV_COV.SCREEN_ENTRY_VALUE
, IV_COV.DEFAULT_VALUE)
AND L2.LOOKUP_TYPE = 'YES_NO'
AND L2.LOOKUP_CODE = 'Y'
AND L3.LOOKUP_TYPE = 'YES_NO'
AND L3.LOOKUP_CODE = 'N'
AND L4.LOOKUP_TYPE = 'UNITS'
AND L4.LOOKUP_CODE = IV_EE.UOM
AND ET.ELEMENT_TYPE_ID = ETTL.ELEMENT_TYPE_ID
AND ETTL.LANGUAGE = USERENV('LANG')

Columns