VIEW: APPS.HR_CERIDIAN_500_SELECTION_V

Source

View Text - Preformatted

SELECT pp.employee_number , hou.name , pp.national_identifier , ptp.period_type , decode(ptp.number_per_fiscal_year, 52,'W',24,'S',26,'B', 12,'M', ptp.number_per_fiscal_year) , bgr.name , ppr.payroll_name , pj.assignment_number , pj.assignment_id , pp.business_group_id , hou.organization_id , ppr.payroll_id , pp.person_id from per_all_people_f pp , hr_all_organization_units hou , hr_all_organization_units bgr , hr_soft_coding_keyflex hs , per_all_assignments_f pj , pay_payrolls_x ppr , per_time_period_types ptp , per_periods_of_service pps WHERE pps.person_id = pp.person_id and pj.assignment_type = 'E' and pps.period_of_service_id = pj.period_of_service_id and ppr.period_type = ptp.period_type and ppr.payroll_id = pj.payroll_id and hs.soft_coding_keyflex_id = pj.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and pj.primary_flag = 'Y' and pp.employee_number is not null and trunc(hr_ceridian.get_cer_extract_date) between pp.effective_start_date and pp.effective_end_date and pj.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(hr_ceridian.get_cer_extract_date) and a2.assignment_type = 'E' and a2.person_id = pj.person_id and a2.assignment_id = pj.assignment_id and a2.primary_flag = 'Y')) and pps.date_start = (select max(ps.date_start) from per_periods_of_service ps where (ps.date_start <= trunc(hr_ceridian.get_cer_extract_date) and ps.person_id = pp.person_id)) and pp.business_group_id+0=bgr.organization_id

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, HOU.NAME
, PP.NATIONAL_IDENTIFIER
, PTP.PERIOD_TYPE
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, BGR.NAME
, PPR.PAYROLL_NAME
, PJ.ASSIGNMENT_NUMBER
, PJ.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, HOU.ORGANIZATION_ID
, PPR.PAYROLL_ID
, PP.PERSON_ID
FROM PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PJ
, PAY_PAYROLLS_X PPR
, PER_TIME_PERIOD_TYPES PTP
, PER_PERIODS_OF_SERVICE PPS
WHERE PPS.PERSON_ID = PP.PERSON_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PPR.PAYROLL_ID = PJ.PAYROLL_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PJ.PRIMARY_FLAG = 'Y'
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PJ.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PERSON_ID = PJ.PERSON_ID
AND A2.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND PPS.DATE_START = (SELECT MAX(PS.DATE_START)
FROM PER_PERIODS_OF_SERVICE PS
WHERE (PS.DATE_START <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PS.PERSON_ID = PP.PERSON_ID))
AND PP.BUSINESS_GROUP_ID+0=BGR.ORGANIZATION_ID