View: HR_CERIDIAN_ASSIGNMENT_V

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

View Text

SELECT PP.EMPLOYEE_NUMBER
, HOU.NAME
, PP.LAST_NAME
, PP.FIRST_NAME
, PP.MIDDLE_NAMES
, PPS.DATE_START
, PPS.FINAL_PROCESS_DATE
, NVL(HS.SEGMENT8
, PJW.WC_CODE)
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, DECODE(PPB.PAY_BASIS
, 'HOURLY'
, PBEV.SCREEN_ENTRY_VALUE
, 'ANNUAL'
, NULL
, 'ERROR')
, DECODE(PPB.PAY_BASIS
, 'ANNUAL'
, PBEV.SCREEN_ENTRY_VALUE
, 'HOURLY'
, NULL
, 'ERROR')
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS)
, PJ.NORMAL_HOURS
, PCA.SEGMENT1||PCA.SEGMENT2||PCA.SEGMENT3
, HS.SEGMENT5
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PBEE.EFFECTIVE_START_DATE)
, GREATEST(NVL(PA.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PA.DATE_FROM)
, GREATEST(NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PJ.EFFECTIVE_START_DATE)
, GREATEST(NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, GREATEST( NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(PJW.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(HL.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(AST.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(ASTA.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
FROM PER_ADDRESSES PA
, PER_ALL_PEOPLE_F PP
, PAY_ELEMENT_ENTRY_VALUES_F PBEV
, PAY_ELEMENT_TYPES_X PBET
, PAY_ELEMENT_LINKS_X PBEL
, PAY_ELEMENT_ENTRIES_F PBEE
, HR_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PAY_INPUT_VALUES_F PPIV
, PER_PAY_BASES PPB
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASSIGNMENT_STATUS_TYPES AST
, PAY_COST_ALLOCATIONS_F PCF
, PAY_COST_ALLOCATION_KEYFLEX PCA
, HR_LOCATIONS_ALL HL
, PAY_JOB_WC_CODE_USAGES PJW
, PER_ALL_ASSIGNMENTS_F PJ
, PER_PERIODS_OF_SERVICE PPS
WHERE PP.PERSON_ID = PA.PERSON_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PTP.PERIOD_TYPE = PPR.PERIOD_TYPE
AND PPR.PAYROLL_ID = PJ.PAYROLL_ID
AND PA.STYLE IN ('US'
, 'US_GLB')
AND HL.REGION_2 = PJW.STATE_CODE
AND HL.LOCATION_ID = PJ.LOCATION_ID
AND PJ.JOB_ID = PJW.JOB_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PJ.PRIMARY_FLAG = 'Y'
AND PA.PRIMARY_FLAG = 'Y'
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PJ.JOB_ID IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PA.DATE_FROM
AND NVL(PA.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PJ.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 = PJ.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND PJ.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PPB.INPUT_VALUE_ID = PBEV.INPUT_VALUE_ID + 0
AND PPB.INPUT_VALUE_ID = PPIV.INPUT_VALUE_ID
AND PPIV.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEV.ELEMENT_ENTRY_ID = PBEE.ELEMENT_ENTRY_ID
AND PBEE.ELEMENT_LINK_ID = PBEL.ELEMENT_LINK_ID
AND PBEL.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEE.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND PBEE.EFFECTIVE_START_DATE = (SELECT MAX(PBEE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEE2
, PAY_ELEMENT_LINKS_X PBEL2
WHERE (PBEE2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PBEE2.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEE2.ELEMENT_LINK_ID = PBEL2.ELEMENT_LINK_ID
AND PBEL2.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID))
AND PBEV.EFFECTIVE_START_DATE = (SELECT MAX(PBEV2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEV2
WHERE (PBEV2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PBEV2.ELEMENT_ENTRY_ID = PBEV.ELEMENT_ENTRY_ID))
AND PPIV.EFFECTIVE_START_DATE = (SELECT MAX(PPIV2.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PPIV2
WHERE (PPIV2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PPIV2.INPUT_VALUE_ID = PPIV.INPUT_VALUE_ID))
AND PCF.EFFECTIVE_START_DATE = (SELECT MAX(PCF2.EFFECTIVE_START_DATE)
FROM PAY_COST_ALLOCATIONS_F PCF2
WHERE (PCF2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PCF2.ASSIGNMENT_ID = PCF.ASSIGNMENT_ID))
AND AST.ASSIGNMENT_STATUS_TYPE_ID = PJ.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND PCA.COST_ALLOCATION_KEYFLEX_ID = PCF.COST_ALLOCATION_KEYFLEX_ID
AND PCF.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID

Columns