VIEW: APPS.HRFV_DEF_BENEFIT_CONTRIBUTIONS

Source

View Text - Preformatted

SELECT houT.name business_group_name ,etT.element_name benefit_name ,bcs.benefit_classification_name benefit_classification_name ,etT.description description ,bco.effective_start_date effective_start_date ,hr_discoverer.check_end_date(bco.effective_end_date) effective_end_date ,hr_bis.bis_decode_lookup('US_BENEFIT_COVERAGE', bco.coverage_type) coverage_type ,TO_NUMBER(bco.employee_contribution, '999999999D999') employee_contribution ,TO_NUMBER(bco.employer_contribution, '999999999D999') employer_contribution ,TO_NUMBER(bco.employee_contribution, '999999999D999') + TO_NUMBER(bco.employer_contribution, '999999999D999') total_contribution ,hr_bis.bis_decode_lookup('YES_NO',bcs.cobra_flag) cobra_eligible ,hr_bis.bis_decode_lookup('YES_NO',bcs.dependents_allowed_flag) dependents_covered_flag ,bco.benefit_contribution_id benefit_contribution_id ,bco.business_group_id business_group_id ,bco.element_type_id element_type_id ,bco.formula_id formula_id FROM ben_benefit_contributions_f bco ,ben_benefit_classifications bcs ,pay_element_types_f_tl etT ,pay_element_types_f et ,hr_all_organization_units_tl houT WHERE bco.element_type_id = etT.element_type_id AND et.element_type_id = etT.element_type_id AND etT.language = userenv('LANG') AND bcs.benefit_classification_id = et.benefit_classification_id AND bco.business_group_id = houT.organization_id AND houT.language = userenv('LANG') AND bco.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,bco.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT HOUT.NAME BUSINESS_GROUP_NAME
, ETT.ELEMENT_NAME BENEFIT_NAME
, BCS.BENEFIT_CLASSIFICATION_NAME BENEFIT_CLASSIFICATION_NAME
, ETT.DESCRIPTION DESCRIPTION
, BCO.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, HR_DISCOVERER.CHECK_END_DATE(BCO.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, HR_BIS.BIS_DECODE_LOOKUP('US_BENEFIT_COVERAGE'
, BCO.COVERAGE_TYPE) COVERAGE_TYPE
, TO_NUMBER(BCO.EMPLOYEE_CONTRIBUTION
, '999999999D999') EMPLOYEE_CONTRIBUTION
, TO_NUMBER(BCO.EMPLOYER_CONTRIBUTION
, '999999999D999') EMPLOYER_CONTRIBUTION
, TO_NUMBER(BCO.EMPLOYEE_CONTRIBUTION
, '999999999D999') + TO_NUMBER(BCO.EMPLOYER_CONTRIBUTION
, '999999999D999') TOTAL_CONTRIBUTION
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BCS.COBRA_FLAG) COBRA_ELIGIBLE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BCS.DEPENDENTS_ALLOWED_FLAG) DEPENDENTS_COVERED_FLAG
, BCO.BENEFIT_CONTRIBUTION_ID BENEFIT_CONTRIBUTION_ID
, BCO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, BCO.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, BCO.FORMULA_ID FORMULA_ID
FROM BEN_BENEFIT_CONTRIBUTIONS_F BCO
, BEN_BENEFIT_CLASSIFICATIONS BCS
, PAY_ELEMENT_TYPES_F_TL ETT
, PAY_ELEMENT_TYPES_F ET
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
WHERE BCO.ELEMENT_TYPE_ID = ETT.ELEMENT_TYPE_ID
AND ET.ELEMENT_TYPE_ID = ETT.ELEMENT_TYPE_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND BCS.BENEFIT_CLASSIFICATION_ID = ET.BENEFIT_CLASSIFICATION_ID
AND BCO.BUSINESS_GROUP_ID = HOUT.ORGANIZATION_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND BCO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, BCO.BUSINESS_GROUP_ID) WITH READ ONLY