View: HR_CERIDIAN_500_ALT_RATES_V

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

View Text

SELECT HOU.NAME
, PP.EMPLOYEE_NUMBER
, PB.EFFECTIVE_START_DATE
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE)
, PB.EFFECTIVE_END_DATE
, TO_DATE(NULL))
, PASG.ASSIGNMENT_NUMBER
, PASG.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, PASG.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, TO_NUMBER(DECODE(PIV1.HOT_DEFAULT_FLAG
, 'N'
, PV1.SCREEN_ENTRY_VALUE
, DECODE (PV1.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV1.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV1.SCREEN_ENTRY_VALUE)))
, TO_NUMBER(DECODE(PIV2.HOT_DEFAULT_FLAG
, 'N'
, PV2.SCREEN_ENTRY_VALUE
, DECODE (PV2.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV2.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV2.SCREEN_ENTRY_VALUE)))
, TO_NUMBER(DECODE(PIV3.HOT_DEFAULT_FLAG
, 'N'
, PV3.SCREEN_ENTRY_VALUE
, DECODE (PV3.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV3.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV3.SCREEN_ENTRY_VALUE)))
, TO_NUMBER(DECODE(PIV4.HOT_DEFAULT_FLAG
, 'N'
, PV4.SCREEN_ENTRY_VALUE
, DECODE (PV4.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV4.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV4.SCREEN_ENTRY_VALUE)))
, GREATEST ( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PB.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PB.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PB.EFFECTIVE_START_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) ) FROM HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
, PAY_ELEMENT_ENTRY_VALUES_F PV4
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PAY_INPUT_VALUES_F PIV4
, PAY_ELEMENT_TYPES_X PT
, PAY_ELEMENT_ENTRIES_F PB
, PAY_ELEMENT_LINKS_X EL
, PER_PERIODS_OF_SERVICE PPS
WHERE PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND PASG.PRIMARY_FLAG = 'Y'
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 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 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 = PASG.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND (PV1.INPUT_VALUE_ID + 0 = PIV1.INPUT_VALUE_ID
AND PIV1.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV1.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PIV1.NAME <> 'PAY VALUE')
AND (PV2.INPUT_VALUE_ID + 0 = PIV2.INPUT_VALUE_ID
AND PIV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID)
AND (PV3.INPUT_VALUE_ID + 0 = PIV3.INPUT_VALUE_ID
AND PIV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV3.ELEMENT_TYPE_ID)
AND (PV4.INPUT_VALUE_ID + 0 = PIV4.INPUT_VALUE_ID
AND PIV4.DISPLAY_SEQUENCE = 4
AND PB.ELEMENT_ENTRY_ID = PV4.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV4.ELEMENT_TYPE_ID)
AND PB.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PT.ELEMENT_NAME = 'CERIDIAN ALTERNATE PAY RATES'
AND PB.EFFECTIVE_START_DATE = (SELECT MAX(PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
, PAY_ELEMENT_LINKS_X EL2
WHERE (PB2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PB2.ELEMENT_LINK_ID = EL2.ELEMENT_LINK_ID
AND EL2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PB2.ASSIGNMENT_ID = PB.ASSIGNMENT_ID ))
AND PV1.EFFECTIVE_START_DATE = (SELECT MAX(PV12.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV12
WHERE (PV12.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PV12.ELEMENT_ENTRY_VALUE_ID = PV1.ELEMENT_ENTRY_VALUE_ID))
AND PV2.EFFECTIVE_START_DATE = (SELECT MAX(PV22.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV22
WHERE (PV22.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PV22.ELEMENT_ENTRY_VALUE_ID = PV2.ELEMENT_ENTRY_VALUE_ID))
AND PV3.EFFECTIVE_START_DATE = (SELECT MAX(PV32.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV32
WHERE (PV32.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PV32.ELEMENT_ENTRY_VALUE_ID = PV3.ELEMENT_ENTRY_VALUE_ID))
AND PV4.EFFECTIVE_START_DATE = (SELECT MAX(PV42.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV42
WHERE (PV42.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PV42.ELEMENT_ENTRY_VALUE_ID = PV4.ELEMENT_ENTRY_VALUE_ID))
AND PT.EFFECTIVE_START_DATE = (SELECT MAX(PT2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PT2
WHERE (PT2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PT2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID))
AND EL.EFFECTIVE_START_DATE = (SELECT MAX(EL2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_X EL2
WHERE (EL2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID))
AND PIV1.EFFECTIVE_START_DATE = (SELECT MAX(PIV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV12
WHERE (PIV12.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV12.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID))
AND PIV2.EFFECTIVE_START_DATE = (SELECT MAX(PIV22.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV22
WHERE (PIV22.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV22.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID))
AND PIV3.EFFECTIVE_START_DATE = (SELECT MAX(PIV32.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV32
WHERE (PIV32.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV32.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID))
AND PIV4.EFFECTIVE_START_DATE = (SELECT MAX(PIV42.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV42
WHERE (PIV42.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV42.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID))
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