VIEW: APPS.HRFV_BENEFIT_ENROLLMENTS

Source

View Text - Preformatted

SELECT bgrt.name business_group_name ,pp.full_name person_name ,ptt.element_name benefit_name ,bcls.benefit_classification_name benefit_classification_name ,TO_NUMBER(DECODE(iv4.uom, 'N', '%'||NVL(pv4.screen_entry_value, bcon.employee_contribution),NVL(pv4.screen_entry_value, bcon.employee_contribution)), '999999999D999') employee_contribution ,TO_NUMBER(DECODE(iv3.uom, 'N', '%'||NVL(pv3.screen_entry_value, bcon.employer_contribution), NVL(pv3.screen_entry_value, bcon.employer_contribution)), '999999999D999') employer_contribution ,TO_NUMBER(DECODE(iv4.uom, 'N', '%'||NVL(pv4.screen_entry_value, bcon.employee_contribution), NVL(pv4.screen_entry_value, bcon.employee_contribution)), '999999999D999') + TO_NUMBER(DECODE(iv3.uom, 'N', '%'||NVL(pv3.screen_entry_value, bcon.employer_contribution), NVL(pv3.screen_entry_value, bcon.employer_contribution)), '999999999D999') total_contribution ,hr_bis.bis_decode_lookup('UNITS',iv4.uom) units ,hr_bis.bis_decode_lookup('US_BENEFIT_COVERAGE',bcon.coverage_type) coverage_type ,pt.element_information4 period_type ,orgt.name organization_name ,pft.name position_name ,jbt.name job_name ,gdt.name grade_name ,hlT.location_code location_name ,hr_bis.bis_decode_lookup('YES_NO',bcls.cobra_flag) cobra_eligible ,pa.assignment_number assignment_number ,'_DF:PAY:PAY_ELEMENT_ENTRIES:pb' ,pa.assignment_id assignment_id ,pa.business_group_id business_group_id ,gdt.grade_id grade_id ,jbt.job_id job_id ,hlT.location_id location_id ,orgt.organization_id organization_id ,pp.person_id person_id ,pft.position_id position_id FROM pay_element_entry_values_x pv4 ,pay_element_entry_values_x pv3 ,pay_element_entry_values_x pv1 ,ben_benefit_contributions_x bcon ,ben_benefit_classifications bcls ,pay_input_values_x iv4 ,pay_input_values_x iv3 ,pay_input_values_x iv1 ,pay_element_types_f_tl ptt ,pay_element_types_x pt ,pay_element_links_x el ,pay_element_entries_x pb ,per_people_x pp ,per_periods_of_service pps ,hr_all_organization_units_tl orgt ,hr_all_organization_units_tl bgrt ,per_grades_tl gdt ,per_jobs_tl jbt ,hr_all_positions_f_tl pft ,hr_locations_all_tl hlT ,per_assignments_x pa WHERE pps.period_of_service_id = pa.period_of_service_id AND pp.person_id = pps.person_id AND pp.person_id = pa.person_id AND pa.organization_id = orgt.organization_id AND orgt.language = userenv('LANG') AND pa.grade_id = gdt.grade_id(+) AND gdt.language(+) = userenv('LANG') AND pa.job_id = jbt.job_id(+) AND jbt.language(+) = userenv('LANG') AND pa.position_id = pft.position_id(+) AND pft.language(+) = userenv('LANG') AND pa.location_id = hlT.location_id(+) AND hlT.language (+) = userenv('LANG') AND pa.assignment_id = pb.assignment_id AND (pv4.input_value_id = iv4.input_value_id and iv4.display_sequence = 3 and pb.element_entry_id = pv4.element_entry_id and pt.element_type_id = iv4.element_type_id) AND (pv3.input_value_id = iv3.input_value_id and iv3.display_sequence = 2 and pb.element_entry_id = pv3.element_entry_id and pt.element_type_id = iv3.element_type_id) AND (pv1.input_value_id = iv1.input_value_id AND iv1.display_sequence = 1 AND pb.element_entry_id = pv1.element_entry_id AND pt.element_type_id = iv1.element_type_id AND iv1.name = 'Coverage') AND pb.element_link_id = el.element_link_id AND el.element_type_id = pt.element_type_id AND pt.element_type_id = ptt.element_type_id AND ptt.language = userenv('LANG') AND pp.employee_number IS NOT NULL AND pt.element_type_id = bcon.element_type_id AND bcon.coverage_type = pv1.screen_entry_value AND (bcls.benefit_classification_id IS NOT NULL AND bcls.contributions_used = 'Y' AND pt.benefit_classification_id = bcls.benefit_classification_id) AND pa.business_group_id = bgrt.organization_id AND bgrt.language = userenv('LANG') AND pa.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,pa.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PP.FULL_NAME PERSON_NAME
, PTT.ELEMENT_NAME BENEFIT_NAME
, BCLS.BENEFIT_CLASSIFICATION_NAME BENEFIT_CLASSIFICATION_NAME
, TO_NUMBER(DECODE(IV4.UOM
, 'N'
, '%'||NVL(PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION)
, NVL(PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION))
, '999999999D999') EMPLOYEE_CONTRIBUTION
, TO_NUMBER(DECODE(IV3.UOM
, 'N'
, '%'||NVL(PV3.SCREEN_ENTRY_VALUE
, BCON.EMPLOYER_CONTRIBUTION)
, NVL(PV3.SCREEN_ENTRY_VALUE
, BCON.EMPLOYER_CONTRIBUTION))
, '999999999D999') EMPLOYER_CONTRIBUTION
, TO_NUMBER(DECODE(IV4.UOM
, 'N'
, '%'||NVL(PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION)
, NVL(PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION))
, '999999999D999') + TO_NUMBER(DECODE(IV3.UOM
, 'N'
, '%'||NVL(PV3.SCREEN_ENTRY_VALUE
, BCON.EMPLOYER_CONTRIBUTION)
, NVL(PV3.SCREEN_ENTRY_VALUE
, BCON.EMPLOYER_CONTRIBUTION))
, '999999999D999') TOTAL_CONTRIBUTION
, HR_BIS.BIS_DECODE_LOOKUP('UNITS'
, IV4.UOM) UNITS
, HR_BIS.BIS_DECODE_LOOKUP('US_BENEFIT_COVERAGE'
, BCON.COVERAGE_TYPE) COVERAGE_TYPE
, PT.ELEMENT_INFORMATION4 PERIOD_TYPE
, ORGT.NAME ORGANIZATION_NAME
, PFT.NAME POSITION_NAME
, JBT.NAME JOB_NAME
, GDT.NAME GRADE_NAME
, HLT.LOCATION_CODE LOCATION_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BCLS.COBRA_FLAG) COBRA_ELIGIBLE
, PA.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, '_DF:PAY:PAY_ELEMENT_ENTRIES:PB'
, PA.ASSIGNMENT_ID ASSIGNMENT_ID
, PA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, HLT.LOCATION_ID LOCATION_ID
, ORGT.ORGANIZATION_ID ORGANIZATION_ID
, PP.PERSON_ID PERSON_ID
, PFT.POSITION_ID POSITION_ID
FROM PAY_ELEMENT_ENTRY_VALUES_X PV4
, PAY_ELEMENT_ENTRY_VALUES_X PV3
, PAY_ELEMENT_ENTRY_VALUES_X PV1
, BEN_BENEFIT_CONTRIBUTIONS_X BCON
, BEN_BENEFIT_CLASSIFICATIONS BCLS
, PAY_INPUT_VALUES_X IV4
, PAY_INPUT_VALUES_X IV3
, PAY_INPUT_VALUES_X IV1
, PAY_ELEMENT_TYPES_F_TL PTT
, PAY_ELEMENT_TYPES_X PT
, PAY_ELEMENT_LINKS_X EL
, PAY_ELEMENT_ENTRIES_X PB
, PER_PEOPLE_X PP
, PER_PERIODS_OF_SERVICE PPS
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES_TL GDT
, PER_JOBS_TL JBT
, HR_ALL_POSITIONS_F_TL PFT
, HR_LOCATIONS_ALL_TL HLT
, PER_ASSIGNMENTS_X PA
WHERE PPS.PERIOD_OF_SERVICE_ID = PA.PERIOD_OF_SERVICE_ID
AND PP.PERSON_ID = PPS.PERSON_ID
AND PP.PERSON_ID = PA.PERSON_ID
AND PA.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND PA.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND PA.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND PA.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE(+) = USERENV('LANG')
AND PA.LOCATION_ID = HLT.LOCATION_ID(+)
AND HLT.LANGUAGE (+) = USERENV('LANG')
AND PA.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND (PV4.INPUT_VALUE_ID = IV4.INPUT_VALUE_ID
AND IV4.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV4.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = IV4.ELEMENT_TYPE_ID)
AND (PV3.INPUT_VALUE_ID = IV3.INPUT_VALUE_ID
AND IV3.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = IV3.ELEMENT_TYPE_ID)
AND (PV1.INPUT_VALUE_ID = IV1.INPUT_VALUE_ID
AND IV1.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV1.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = IV1.ELEMENT_TYPE_ID
AND IV1.NAME = 'COVERAGE')
AND PB.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PT.ELEMENT_TYPE_ID = PTT.ELEMENT_TYPE_ID
AND PTT.LANGUAGE = USERENV('LANG')
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PT.ELEMENT_TYPE_ID = BCON.ELEMENT_TYPE_ID
AND BCON.COVERAGE_TYPE = PV1.SCREEN_ENTRY_VALUE
AND (BCLS.BENEFIT_CLASSIFICATION_ID IS NOT NULL
AND BCLS.CONTRIBUTIONS_USED = 'Y'
AND PT.BENEFIT_CLASSIFICATION_ID = BCLS.BENEFIT_CLASSIFICATION_ID)
AND PA.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PA.BUSINESS_GROUP_ID) WITH READ ONLY