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_ADP_BENEFIT_V

Source

View Text - Preformatted


SELECT /*+ ORDERED */ REF.company_code_equivalent, hou.name, hs.segment1, pasg.organization_id, pp.employee_number, pp.person_id, pp.business_group_id, pasg.assignment_id, pasg.assignment_number, DECODE(pasg.primary_flag, 'Y', 0, pasg.assignment_sequence), pasg.primary_flag, DECODE( ptp2.number_per_fiscal_year, 52, 'W', 24, 'S', 26, 'B', 12, 'M', ptp2.number_per_fiscal_year ), pt.element_name, bbc.benefit_classification_name, tp.number_per_fiscal_year, pt.element_information4, TO_NUMBER(NVL(pv2.screen_entry_value, bcon.employer_contribution)), TO_NUMBER(NVL(pv3.screen_entry_value, bcon.employee_contribution)), bcon.coverage_type, MIN(pb.effective_start_date), DECODE( MAX(pb.effective_end_date), TO_DATE('31-12-4712', 'DD-MM-YYYY'), TO_DATE(NULL), MAX(pb.effective_end_date) + 1 ), GREATEST( NVL(pp.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date ), GREATEST( NVL(pasg.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date ), GREATEST( NVL(pt.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), pt.effective_start_date ), GREATEST( DECODE( GREATEST( TRUNC(hr_adp.get_adp_extract_date), pb.effective_end_date ), TRUNC(hr_adp.get_adp_extract_date), GREATEST( pb.effective_end_date, NVL( pb.last_update_date, TO_DATE( '1900/01/01', 'YYYY/MM/DD' ) ) ), 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 ), GREATEST( NVL(bbc.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), TO_DATE('1900/01/01', 'YYYY/MM/DD') ), GREATEST( NVL(tp.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), TO_DATE('1900/01/01', 'YYYY/MM/DD') ), GREATEST( DECODE( GREATEST( TRUNC(hr_adp.get_adp_extract_date), pb.effective_end_date ), TRUNC(hr_adp.get_adp_extract_date), GREATEST( pb.effective_end_date, NVL( pp.last_update_date, TO_DATE( '1900/01/01', 'YYYY/MM/DD' ) ), pp.effective_start_date, NVL( pasg.last_update_date, TO_DATE( '1900/01/01', 'YYYY/MM/DD' ) ), pasg.effective_start_date, NVL( pt.last_update_date, TO_DATE( '1900/01/01', 'YYYY/MM/DD' ) ), pt.effective_start_date, NVL( pb.last_update_date, TO_DATE( '1900/01/01', 'YYYY/MM/DD' ) ), NVL( bcon.last_update_date, TO_DATE( '1900/01/01', 'YYYY/MM/DD' ) ), bcon.effective_start_date, NVL( bbc.last_update_date, TO_DATE( '1900/01/01', 'YYYY/MM/DD' ) ), NVL( tp.last_update_date, TO_DATE( '1900/01/01', 'YYYY/MM/DD' ) ) ), GREATEST( NVL(pp.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date, NVL(pasg.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, NVL(pt.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), pt.effective_start_date, NVL(pb.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), NVL(bcon.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), bcon.effective_start_date, NVL(bbc.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')), NVL(tp.last_update_date, TO_DATE('1900/01/01', 'YYYY/MM/DD')) ) ), pb.effective_start_date ) FROM pay_element_types_f pt, pay_input_values_f iv1, pay_input_values_f iv2, pay_input_values_f iv3, pay_element_links_f el, pay_element_entries_f pb, hr_adp_emp_ref_v ref, per_time_period_types tp, per_all_assignments_f pasg, per_periods_of_service pps, per_all_people_f pp, hr_soft_coding_keyflex hs, hr_all_organization_units hou, pay_payrolls_x ppr, per_time_period_types ptp2, ben_benefit_classifications bbc, pay_element_entry_values_f pv1, ben_benefit_contributions_f bcon, pay_element_entry_values_f pv2, pay_element_entry_values_f pv3 WHERE ppr.payroll_id = pasg.payroll_id AND pasg.assignment_type = 'E' AND ppr.period_type = ptp2.period_type AND hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id AND hs.segment1 = hou.organization_id AND pasg.period_of_service_id = pps.period_of_service_id AND pasg.assignment_id = pb.assignment_id AND pasg.assignment_id = REF.assignment_id AND pps.person_id = pp.person_id AND pp.employee_number IS NOT NULL AND pp.ROWID = REF.per_rowid AND REF.assignment_id = pb.assignment_id AND pasg.ROWID = REF.asg_rowid AND TRUNC(hr_adp.get_adp_extract_date) BETWEEN pp.effective_start_date AND pp.effective_end_date AND pt.element_information4 = tp.period_type AND bbc.contributions_used = 'Y' AND bbc.benefit_classification_id = pt.benefit_classification_id AND bcon.element_type_id = pt.element_type_id AND bcon.coverage_type = pv1.screen_entry_value AND el.element_link_id = pb.element_link_id AND el.element_type_id = pt.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 pv1.effective_start_date = pb.effective_start_date AND pv1.effective_end_date = pb.effective_end_date AND pt.element_type_id = iv1.element_type_id AND iv1.name = 'Coverage') AND (pv2.input_value_id = iv2.input_value_id AND iv2.display_sequence = 2 AND pb.element_entry_id = pv2.element_entry_id AND pv2.effective_start_date = pb.effective_start_date AND pv2.effective_end_date = pb.effective_end_date AND pt.element_type_id = iv2.element_type_id) AND (pv3.input_value_id = iv3.input_value_id AND iv3.display_sequence = 3 AND pb.element_entry_id = pv3.element_entry_id AND pv3.effective_start_date = pb.effective_start_date AND pv3.effective_end_date = pb.effective_end_date AND pt.element_type_id = iv3.element_type_id) AND bcon.effective_start_date = (SELECT MAX(bcon2.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 bcon2.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date)) AND pb.effective_start_date = (SELECT MAX(pb2.effective_start_date) FROM pay_element_entries_f pb2, pay_element_links_f el2 WHERE el2.effective_start_date = (select max(el2.effective_start_date) from pay_element_links_f el3 where el3.element_link_id = el2.element_link_id and el3.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date)) AND pb2.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) 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 ((pb2.effective_end_date > trunc(hr_adp.get_adp_extract_date)) or (pb2.effective_end_date >= hr_adp.get_end_deduction_date))) AND pt.effective_start_date = (SELECT MAX(pt2.effective_start_date) FROM pay_element_types_f pt2 WHERE (pt2.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) 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(hr_adp.get_adp_extract_date) AND el2.element_link_id = el.element_link_id)) AND iv1.effective_start_date = (SELECT MAX(iv12.effective_start_date) FROM pay_input_values_f iv12 WHERE (iv12.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND iv12.input_value_id = iv1.input_value_id)) AND iv2.effective_start_date = (SELECT MAX(iv22.effective_start_date) FROM pay_input_values_f iv22 WHERE (iv22.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND iv22.input_value_id = iv2.input_value_id)) AND iv3.effective_start_date = (SELECT MAX(iv32.effective_start_date) FROM pay_input_values_f iv32 WHERE (iv32.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND iv32.input_value_id = iv3.input_value_id)) GROUP BY REF.company_code_equivalent, pp.employee_number, hou.name, hs.segment1, pasg.organization_id, pp.person_id, pp.business_group_id, pasg.assignment_id, pasg.assignment_number, pasg.assignment_sequence, pasg.primary_flag, bcon.coverage_type, bcon.employer_contribution, pt.element_name, bbc.benefit_classification_name, pv2.screen_entry_value, pv3.screen_entry_value, bcon.employee_contribution, pt.element_information4, tp.number_per_fiscal_year, pb.last_update_date, pp.last_update_date, pp.effective_start_date, hou.last_update_date, hou.date_from, pasg.last_update_date, pasg.effective_start_date, pb.effective_start_date, pb.effective_end_date, bcon.last_update_date, bbc.last_update_date, bcon.effective_start_date, bcon.effective_end_date, pt.last_update_date, pt.effective_start_date, tp.last_update_date, ptp2.number_per_fiscal_year

View Text - HTML Formatted

SELECT /*+ ORDERED */ REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PASG.PRIMARY_FLAG
, DECODE( PTP2.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP2.NUMBER_PER_FISCAL_YEAR )
, PT.ELEMENT_NAME
, BBC.BENEFIT_CLASSIFICATION_NAME
, TP.NUMBER_PER_FISCAL_YEAR
, PT.ELEMENT_INFORMATION4
, TO_NUMBER(NVL(PV2.SCREEN_ENTRY_VALUE
, BCON.EMPLOYER_CONTRIBUTION))
, TO_NUMBER(NVL(PV3.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION))
, BCON.COVERAGE_TYPE
, MIN(PB.EFFECTIVE_START_DATE)
, DECODE( MAX(PB.EFFECTIVE_END_DATE)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE(NULL)
, MAX(PB.EFFECTIVE_END_DATE) + 1 )
, GREATEST( NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE )
, GREATEST( NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE )
, GREATEST( NVL(PT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PT.EFFECTIVE_START_DATE )
, GREATEST( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE )
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST( PB.EFFECTIVE_END_DATE
, NVL( PB.LAST_UPDATE_DATE
, TO_DATE( '1900/01/01'
, 'YYYY/MM/DD' ) ) )
, 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 )
, GREATEST( NVL(BBC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD') )
, GREATEST( NVL(TP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD') )
, GREATEST( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE )
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST( PB.EFFECTIVE_END_DATE
, NVL( PP.LAST_UPDATE_DATE
, TO_DATE( '1900/01/01'
, 'YYYY/MM/DD' ) )
, PP.EFFECTIVE_START_DATE
, NVL( PASG.LAST_UPDATE_DATE
, TO_DATE( '1900/01/01'
, 'YYYY/MM/DD' ) )
, PASG.EFFECTIVE_START_DATE
, NVL( PT.LAST_UPDATE_DATE
, TO_DATE( '1900/01/01'
, 'YYYY/MM/DD' ) )
, PT.EFFECTIVE_START_DATE
, NVL( PB.LAST_UPDATE_DATE
, TO_DATE( '1900/01/01'
, 'YYYY/MM/DD' ) )
, NVL( BCON.LAST_UPDATE_DATE
, TO_DATE( '1900/01/01'
, 'YYYY/MM/DD' ) )
, BCON.EFFECTIVE_START_DATE
, NVL( BBC.LAST_UPDATE_DATE
, TO_DATE( '1900/01/01'
, 'YYYY/MM/DD' ) )
, NVL( TP.LAST_UPDATE_DATE
, TO_DATE( '1900/01/01'
, 'YYYY/MM/DD' ) ) )
, GREATEST( NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PT.EFFECTIVE_START_DATE
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(BCON.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, BCON.EFFECTIVE_START_DATE
, NVL(BBC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(TP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) ) )
, PB.EFFECTIVE_START_DATE )
FROM PAY_ELEMENT_TYPES_F PT
, PAY_INPUT_VALUES_F IV1
, PAY_INPUT_VALUES_F IV2
, PAY_INPUT_VALUES_F IV3
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_ENTRIES_F PB
, HR_ADP_EMP_REF_V REF
, PER_TIME_PERIOD_TYPES TP
, PER_ALL_ASSIGNMENTS_F PASG
, PER_PERIODS_OF_SERVICE PPS
, PER_ALL_PEOPLE_F PP
, HR_SOFT_CODING_KEYFLEX HS
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_PAYROLLS_X PPR
, PER_TIME_PERIOD_TYPES PTP2
, BEN_BENEFIT_CLASSIFICATIONS BBC
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, BEN_BENEFIT_CONTRIBUTIONS_F BCON
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
WHERE PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PPR.PERIOD_TYPE = PTP2.PERIOD_TYPE
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PASG.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PPS.PERSON_ID = PP.PERSON_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PP.ROWID = REF.PER_ROWID
AND REF.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND PASG.ROWID = REF.ASG_ROWID
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PT.ELEMENT_INFORMATION4 = TP.PERIOD_TYPE
AND BBC.CONTRIBUTIONS_USED = 'Y'
AND BBC.BENEFIT_CLASSIFICATION_ID = PT.BENEFIT_CLASSIFICATION_ID
AND BCON.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND BCON.COVERAGE_TYPE = PV1.SCREEN_ENTRY_VALUE
AND EL.ELEMENT_LINK_ID = PB.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.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 PV1.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV1.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = IV1.ELEMENT_TYPE_ID
AND IV1.NAME = 'COVERAGE')
AND (PV2.INPUT_VALUE_ID = IV2.INPUT_VALUE_ID
AND IV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PV2.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV2.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = IV2.ELEMENT_TYPE_ID)
AND (PV3.INPUT_VALUE_ID = IV3.INPUT_VALUE_ID
AND IV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PV3.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV3.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = IV3.ELEMENT_TYPE_ID)
AND BCON.EFFECTIVE_START_DATE = (SELECT MAX(BCON2.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 BCON2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE))
AND PB.EFFECTIVE_START_DATE = (SELECT MAX(PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
, PAY_ELEMENT_LINKS_F EL2
WHERE EL2.EFFECTIVE_START_DATE = (SELECT MAX(EL2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_F EL3
WHERE EL3.ELEMENT_LINK_ID = EL2.ELEMENT_LINK_ID
AND EL3.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE))
AND PB2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
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 ((PB2.EFFECTIVE_END_DATE > TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)) OR (PB2.EFFECTIVE_END_DATE >= HR_ADP.GET_END_DEDUCTION_DATE)))
AND PT.EFFECTIVE_START_DATE = (SELECT MAX(PT2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PT2
WHERE (PT2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
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(HR_ADP.GET_ADP_EXTRACT_DATE)
AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID))
AND IV1.EFFECTIVE_START_DATE = (SELECT MAX(IV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F IV12
WHERE (IV12.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND IV12.INPUT_VALUE_ID = IV1.INPUT_VALUE_ID))
AND IV2.EFFECTIVE_START_DATE = (SELECT MAX(IV22.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F IV22
WHERE (IV22.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND IV22.INPUT_VALUE_ID = IV2.INPUT_VALUE_ID))
AND IV3.EFFECTIVE_START_DATE = (SELECT MAX(IV32.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F IV32
WHERE (IV32.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND IV32.INPUT_VALUE_ID = IV3.INPUT_VALUE_ID)) GROUP BY REF.COMPANY_CODE_EQUIVALENT
, PP.EMPLOYEE_NUMBER
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, PASG.ASSIGNMENT_SEQUENCE
, PASG.PRIMARY_FLAG
, BCON.COVERAGE_TYPE
, BCON.EMPLOYER_CONTRIBUTION
, PT.ELEMENT_NAME
, BBC.BENEFIT_CLASSIFICATION_NAME
, PV2.SCREEN_ENTRY_VALUE
, PV3.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION
, PT.ELEMENT_INFORMATION4
, TP.NUMBER_PER_FISCAL_YEAR
, PB.LAST_UPDATE_DATE
, PP.LAST_UPDATE_DATE
, PP.EFFECTIVE_START_DATE
, HOU.LAST_UPDATE_DATE
, HOU.DATE_FROM
, PASG.LAST_UPDATE_DATE
, PASG.EFFECTIVE_START_DATE
, PB.EFFECTIVE_START_DATE
, PB.EFFECTIVE_END_DATE
, BCON.LAST_UPDATE_DATE
, BBC.LAST_UPDATE_DATE
, BCON.EFFECTIVE_START_DATE
, BCON.EFFECTIVE_END_DATE
, PT.LAST_UPDATE_DATE
, PT.EFFECTIVE_START_DATE
, TP.LAST_UPDATE_DATE
, PTP2.NUMBER_PER_FISCAL_YEAR