eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PER_EMPLOYEE_BENEFITS_V

Source

View Text - Preformatted


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')

View Text - HTML Formatted

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')