View: HR_CERIDIAN_500_EFT_V

Product:	PER - Human Resources
Description:	Ceridian Source 500 payroll interface view
Implementation/DBA Data:	APPS.HR_CERIDIAN_500_EFT_V

View Text

SELECT PP.EMPLOYEE_NUMBER
, PPM.EFFECTIVE_START_DATE
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PPM.EFFECTIVE_END_DATE)
, PPM.EFFECTIVE_END_DATE
, TO_DATE(NULL))
, PA.ASSIGNMENT_NUMBER
, PA.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, HO.NAME
, PEA.SEGMENT5
, PEA.SEGMENT6
, PEA.SEGMENT3
, PEA.SEGMENT2
, PEA.SEGMENT4
, DECODE(PPM.PRIORITY
, 1
, 'A'
, 2
, 'B'
, 3
, 'C'
, 4
, 'D'
, 5
, 'E'
, 6
, 'F'
, 7
, 'G'
, 8
, 'H' )
, PPM.PRIORITY
, PPM.AMOUNT
, PA.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, PPM.PERSONAL_PAYMENT_METHOD_ID
, GREATEST( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PPM.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PPM.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PPM.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PPM.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PPM.EFFECTIVE_START_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
FROM PER_ALL_PEOPLE_F PP
, PAY_EXTERNAL_ACCOUNTS PEA
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PAY_ORG_PAYMENT_METHODS_F POP
, PAY_PAYMENT_TYPES PPT
, HR_ALL_ORGANIZATION_UNITS HO
, PER_PERIODS_OF_SERVICE PPS
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PA
WHERE PPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID
AND PA.ASSIGNMENT_TYPE = 'E'
AND PPM.ORG_PAYMENT_METHOD_ID = POP.ORG_PAYMENT_METHOD_ID
AND POP.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID
AND PPT.CATEGORY = 'MT'
AND PPT.TERRITORY_CODE = 'US'
AND PP.PERSON_ID = PA.PERSON_ID
AND PPS.PERSON_ID = PA.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PA.PERIOD_OF_SERVICE_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PA.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HO.ORGANIZATION_ID
AND PPM.ASSIGNMENT_ID = PA.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PA.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 = PA.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND PPM.EFFECTIVE_START_DATE = (SELECT MAX(PPM2.EFFECTIVE_START_DATE)
FROM PAY_PERSONAL_PAYMENT_METHODS_F PPM2
WHERE (PPM2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PPM2.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID))
AND POP.EFFECTIVE_START_DATE = (SELECT MAX(POP2.EFFECTIVE_START_DATE)
FROM PAY_ORG_PAYMENT_METHODS_F POP2
WHERE (POP2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND POP2.ORG_PAYMENT_METHOD_ID = POP.ORG_PAYMENT_METHOD_ID))
AND TRUNC (HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
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)) GROUP BY PP.EMPLOYEE_NUMBER
, PA.ASSIGNMENT_NUMBER
, PA.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, HO.NAME
, PEA.SEGMENT5
, PEA.SEGMENT6
, PEA.SEGMENT3
, PEA.SEGMENT2
, PEA.SEGMENT4
, PPM.PRIORITY
, PPM.AMOUNT
, PPM.LAST_UPDATE_DATE
, PPM.EFFECTIVE_START_DATE
, PPM.EFFECTIVE_END_DATE
, PA.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, PPM.PERSONAL_PAYMENT_METHOD_ID

Columns