VIEW: APPS.HR_ADP_EMP_REF_V

Source

View Text - Preformatted

SELECT hs.segment1, hs.segment1, pp.employee_number, pp.person_id, pp.business_group_id, pasg.assignment_id, decode(pasg.primary_flag, 'Y',0,pasg.assignment_sequence), pasg.primary_flag, pp.national_identifier, pp.first_name, pp.last_name, pp.middle_names, pp.suffix, pasg.rowid, pp.ROWID, ppr.period_type, pps.date_start, pps.final_process_date, pps.last_update_date FROM /* PLEASE DO NOT CHANGE THE TABLE ORDER */ per_all_assignments_f pasg, per_all_people_f pp, hr_soft_coding_keyflex hs, per_periods_of_service pps, pay_payrolls_f ppr WHERE TRUNC(SYSDATE) BETWEEN ppr.effective_start_date AND ppr.effective_end_date AND ppr.payroll_id = pasg.payroll_id AND pasg.person_id = pp.person_id AND hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id AND pp.employee_number IS NOT NULL AND TRUNC(hr_adp.get_adp_extract_date) BETWEEN pp.effective_start_date AND pp.effective_end_date AND pasg.assignment_type = 'E' AND pasg.effective_start_date = (SELECT MAX(a2.effective_start_date) FROM per_all_assignments_f a2 WHERE a2.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND a2.assignment_id = pasg.assignment_id) AND pps.period_of_service_id = pasg.period_of_service_id AND pps.date_start = (SELECT MAX(ps.date_start) FROM per_periods_of_service ps WHERE (ps.date_start <= TRUNC(hr_adp.get_adp_extract_date) AND ps.person_id = pp.person_id) AND ((ps.actual_termination_date is null) OR (ps.actual_termination_date >= hr_adp.get_ex_employee_date)))

View Text - HTML Formatted

SELECT HS.SEGMENT1
, HS.SEGMENT1
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PASG.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, PP.FIRST_NAME
, PP.LAST_NAME
, PP.MIDDLE_NAMES
, PP.SUFFIX
, PASG.ROWID
, PP.ROWID
, PPR.PERIOD_TYPE
, PPS.DATE_START
, PPS.FINAL_PROCESS_DATE
, PPS.LAST_UPDATE_DATE
FROM /* PLEASE DO NOT CHANGE THE TABLE ORDER */ PER_ALL_ASSIGNMENTS_F PASG
, PER_ALL_PEOPLE_F PP
, HR_SOFT_CODING_KEYFLEX HS
, PER_PERIODS_OF_SERVICE PPS
, PAY_PAYROLLS_F PPR
WHERE TRUNC(SYSDATE) BETWEEN PPR.EFFECTIVE_START_DATE
AND PPR.EFFECTIVE_END_DATE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND A2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID)
AND PPS.PERIOD_OF_SERVICE_ID = PASG.PERIOD_OF_SERVICE_ID
AND PPS.DATE_START = (SELECT MAX(PS.DATE_START)
FROM PER_PERIODS_OF_SERVICE PS
WHERE (PS.DATE_START <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PS.PERSON_ID = PP.PERSON_ID)
AND ((PS.ACTUAL_TERMINATION_DATE IS NULL) OR (PS.ACTUAL_TERMINATION_DATE >= HR_ADP.GET_EX_EMPLOYEE_DATE)))