View: HR_CERIDIAN_500_MISC_V

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

View Text

SELECT PASG.ASSIGNMENT_NUMBER
, PASG.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, PCA.SEGMENT1
, PCA.SEGMENT2
, PCA.SEGMENT3
, PCA.SEGMENT4
, PCA.SEGMENT5
, PCA.SEGMENT6
, PCA.SEGMENT7
, PCA.SEGMENT8
, PCA.SEGMENT9
, PCA.SEGMENT10
, PP.EMPLOYEE_NUMBER
, HOU.NAME
, HL.LOCATION_CODE
, PASG.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PASG.ORGANIZATION_ID
, PP.PERSON_ID
, GREATEST ( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PCF.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PCF.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PCF.EFFECTIVE_START_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) COST_LAST_UPDATE_DATE
, GREATEST ( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE)
, GREATEST(NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST ( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PCF.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PCF.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PCF.EFFECTIVE_START_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) ) LAST_UPDATE_DATE
FROM PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PAY_COST_ALLOCATION_KEYFLEX PCA
, PAY_COST_ALLOCATIONS_F PCF
, HR_LOCATIONS_ALL HL
, PER_ALL_ASSIGNMENTS_F PASG
, PER_PERIODS_OF_SERVICE PPS
WHERE PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.LOCATION_ID = HL.LOCATION_ID
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PCA.COST_ALLOCATION_KEYFLEX_ID = PCF.COST_ALLOCATION_KEYFLEX_ID
AND PCF.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PASG.PRIMARY_FLAG = 'Y'
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PCF.EFFECTIVE_START_DATE=(SELECT MAX(PCF2.EFFECTIVE_START_DATE)
FROM PAY_COST_ALLOCATIONS_F PCF2
WHERE (PCF2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PCF2.ASSIGNMENT_ID = PCF.ASSIGNMENT_ID))
AND PASG.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 = PP.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))

Columns