View: HR_CERIDIAN_EFT_V

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

View Text

SELECT PP.EMPLOYEE_NUMBER
, 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
, DECODE(MAX(MPM.EFFECTIVE_END_DATE)
, GREATEST(MAX(MPM.EFFECTIVE_END_DATE)
, SYSDATE)
, 'Y'
, 'N')
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST(PPM.LAST_UPDATE_DATE
, PPM.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PP
, PAY_EXTERNAL_ACCOUNTS PEA
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PAY_PERSONAL_PAYMENT_METHODS_F MPM
, PAY_ORG_PAYMENT_METHODS_F POP
, PAY_PAYMENT_TYPES PPT
, HR_ALL_ORGANIZATION_UNITS HO
, 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 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 MPM.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID
AND PA.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
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(SYSDATE)
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(SYSDATE)
AND POP2.ORG_PAYMENT_METHOD_ID = POP.ORG_PAYMENT_METHOD_ID))
AND TRUNC (SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE GROUP BY PP.EMPLOYEE_NUMBER
, HO.NAME
, PEA.SEGMENT5
, PEA.SEGMENT6
, PEA.SEGMENT3
, PEA.SEGMENT2
, PEA.SEGMENT4
, PPM.PRIORITY
, PPM.AMOUNT
, PPM.LAST_UPDATE_DATE
, PPM.EFFECTIVE_START_DATE
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID

Columns