[Home] [Help]
View: HR_CERIDIAN_500_SELECTION_V
View Text
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.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
Columns
Name |
EMPLOYEE_NUMBER |
COMPANY |
SOCIAL_SECURITY_NUMBER |
PAY_FREQUENCY_NAME |
PAY_FREQUENCY |
BUSINESS_GROUP_NAME |
PAYROLL_NAME |
ASSIGNMENT_NUMBER |
ASSIGNMENT_ID |
BUSINESS_GROUP_ID |
GRE_ID |
PAYROLL_ID |
PERSON_ID |