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.HR_CERIDIAN_BENEFIT_V

Source

View Text - Preformatted


SELECT pp.employee_number , ho.name , pt.reporting_name , decode (iv4.uom, 'N', '%'||nvl(pv4.screen_entry_value, bcon.employee_contribution), nvl(pv4.screen_entry_value, bcon.employee_contribution)) , pt.element_information4 , tp.number_per_fiscal_year , decode(max(pbm.effective_end_date), greatest(max(pbm.effective_end_date),sysdate), 'Y', 'N') , pp.business_group_id , pp.person_id , greatest(nvl(pb.last_update_date,to_date('1900/01/01','YYYY/MM/DD')), pb.effective_start_date) , greatest(nvl(bcon.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), bcon.effective_start_date) from pay_element_entry_values_f pv4 , pay_element_entry_values_f pv1 , per_time_period_types tp , ben_benefit_contributions_f bcon , pay_input_values_f iv4 , pay_input_values_f iv1 , ben_benefit_classifications bc , pay_element_types_f pt , pay_element_links_f el , pay_element_entries_f pb , pay_element_entries_f pbm , hr_all_organization_units ho , hr_soft_coding_keyflex hs , per_all_assignments_f pj , per_all_people_f pp , per_periods_of_service pps WHERE hs.soft_coding_keyflex_id = pj.soft_coding_keyflex_id and pj.assignment_type = 'E' and hs.segment1 = ho.organization_id and pps.period_of_service_id = pj.period_of_service_id and pp.person_id = pps.person_id and pj.assignment_id = pb.assignment_id and (pv4.input_value_id + 0 = 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 pb.element_link_id = el.element_link_id and el.element_type_id = pt.element_type_id and pp.employee_number is not null and pj.primary_flag = 'Y' and bc.benefit_classification_id = pt.benefit_classification_id and bc.contributions_used = 'Y' and bcon.effective_start_date = (select max(effective_start_date) from ben_benefit_contributions_f bcon2 where bcon2.element_type_id = bcon.element_type_id and bcon2.coverage_type = bcon.coverage_type and effective_start_date <= trunc (sysdate)) and pb.effective_start_date = (select max(pb2.effective_start_date) from pay_element_entries_f pb2 , pay_element_links_x el2 where (pb2.effective_start_date <= trunc(sysdate) and pb2.element_link_id = el2.element_link_id and el2.element_type_id = pt.element_type_id and pb2.assignment_id = pb.assignment_id )) and pv4.effective_start_date = (select max(pv42.effective_start_date) from pay_element_entry_values_f pv42 where (pv42.effective_start_date <= trunc(sysdate) and pv42.element_entry_value_id = pv4.element_entry_value_id)) and pv1.effective_start_date = (select max(pv12.effective_start_date) from pay_element_entry_values_f pv12 where (pv12.effective_start_date <= trunc(sysdate) and pv12.element_entry_value_id = pv1.element_entry_value_id)) and pt.effective_start_date = (select max(pt2.effective_start_date) from pay_element_types_f pt2 where (pt2.effective_start_date <= trunc(sysdate) and pt2.element_type_id = pt.element_type_id)) and el.effective_start_date = (select max(el2.effective_start_date) from pay_element_links_f el2 where (el2.effective_start_date <= trunc(sysdate) and el2.element_link_id = el.element_link_id)) and iv4.effective_start_date = (select max(iv42.effective_start_date) from pay_input_values_f iv42 where (iv42.effective_start_date <= trunc(sysdate) and iv42.input_value_id = iv4.input_value_id)) and iv1.effective_start_date = (select max(iv12.effective_start_date) from pay_input_values_f iv12 where (iv12.effective_start_date <= trunc(sysdate) and iv12.input_value_id = iv1.input_value_id)) and (pv1.input_value_id + 0 = 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 pj.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(sysdate) and a2.assignment_type = 'E' and a2.person_id = pj.person_id and a2.primary_flag = 'Y')) and pb.element_entry_id = pbm.element_entry_id and pt.element_information4 = tp.period_type and pt.element_type_id = bcon.element_type_id and bcon.coverage_type = pv1.screen_entry_value and trunc (sysdate) between pp.effective_start_date and pp.effective_end_date group by employee_number, ho.name, pt.reporting_name, iv4.uom, pv4.screen_entry_value, bcon.employee_contribution, pt.element_information4, tp.number_per_fiscal_year, pb.last_update_date, pb.effective_start_date, pp.business_group_id, pp.person_id, bcon.last_update_date, bcon.effective_start_date

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, HO.NAME
, PT.REPORTING_NAME
, DECODE (IV4.UOM
, 'N'
, '%'||NVL(PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION)
, NVL(PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION))
, PT.ELEMENT_INFORMATION4
, TP.NUMBER_PER_FISCAL_YEAR
, DECODE(MAX(PBM.EFFECTIVE_END_DATE)
, GREATEST(MAX(PBM.EFFECTIVE_END_DATE)
, SYSDATE)
, 'Y'
, 'N')
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST(NVL(PB.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PB.EFFECTIVE_START_DATE)
, GREATEST(NVL(BCON.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, BCON.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV4
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PER_TIME_PERIOD_TYPES TP
, BEN_BENEFIT_CONTRIBUTIONS_F BCON
, PAY_INPUT_VALUES_F IV4
, PAY_INPUT_VALUES_F IV1
, BEN_BENEFIT_CLASSIFICATIONS BC
, PAY_ELEMENT_TYPES_F PT
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_ENTRIES_F PB
, PAY_ELEMENT_ENTRIES_F PBM
, HR_ALL_ORGANIZATION_UNITS HO
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PJ
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
WHERE HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND HS.SEGMENT1 = HO.ORGANIZATION_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PP.PERSON_ID = PPS.PERSON_ID
AND PJ.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND (PV4.INPUT_VALUE_ID + 0 = 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 PB.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PJ.PRIMARY_FLAG = 'Y'
AND BC.BENEFIT_CLASSIFICATION_ID = PT.BENEFIT_CLASSIFICATION_ID
AND BC.CONTRIBUTIONS_USED = 'Y'
AND BCON.EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE)
FROM BEN_BENEFIT_CONTRIBUTIONS_F BCON2
WHERE BCON2.ELEMENT_TYPE_ID = BCON.ELEMENT_TYPE_ID
AND BCON2.COVERAGE_TYPE = BCON.COVERAGE_TYPE
AND EFFECTIVE_START_DATE <= TRUNC (SYSDATE))
AND PB.EFFECTIVE_START_DATE = (SELECT MAX(PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
, PAY_ELEMENT_LINKS_X EL2
WHERE (PB2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PB2.ELEMENT_LINK_ID = EL2.ELEMENT_LINK_ID
AND EL2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PB2.ASSIGNMENT_ID = PB.ASSIGNMENT_ID ))
AND PV4.EFFECTIVE_START_DATE = (SELECT MAX(PV42.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV42
WHERE (PV42.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PV42.ELEMENT_ENTRY_VALUE_ID = PV4.ELEMENT_ENTRY_VALUE_ID))
AND PV1.EFFECTIVE_START_DATE = (SELECT MAX(PV12.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV12
WHERE (PV12.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PV12.ELEMENT_ENTRY_VALUE_ID = PV1.ELEMENT_ENTRY_VALUE_ID))
AND PT.EFFECTIVE_START_DATE = (SELECT MAX(PT2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PT2
WHERE (PT2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PT2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID))
AND EL.EFFECTIVE_START_DATE = (SELECT MAX(EL2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_F EL2
WHERE (EL2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID))
AND IV4.EFFECTIVE_START_DATE = (SELECT MAX(IV42.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F IV42
WHERE (IV42.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND IV42.INPUT_VALUE_ID = IV4.INPUT_VALUE_ID))
AND IV1.EFFECTIVE_START_DATE = (SELECT MAX(IV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F IV12
WHERE (IV12.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND IV12.INPUT_VALUE_ID = IV1.INPUT_VALUE_ID))
AND (PV1.INPUT_VALUE_ID + 0 = 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 PJ.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PERSON_ID = PJ.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND PB.ELEMENT_ENTRY_ID = PBM.ELEMENT_ENTRY_ID
AND PT.ELEMENT_INFORMATION4 = TP.PERIOD_TYPE
AND PT.ELEMENT_TYPE_ID = BCON.ELEMENT_TYPE_ID
AND BCON.COVERAGE_TYPE = PV1.SCREEN_ENTRY_VALUE
AND TRUNC (SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE GROUP BY EMPLOYEE_NUMBER
, HO.NAME
, PT.REPORTING_NAME
, IV4.UOM
, PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION
, PT.ELEMENT_INFORMATION4
, TP.NUMBER_PER_FISCAL_YEAR
, PB.LAST_UPDATE_DATE
, PB.EFFECTIVE_START_DATE
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, BCON.LAST_UPDATE_DATE
, BCON.EFFECTIVE_START_DATE