eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: HR_CERIDIAN_500_ASSIGNMENT_V

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

View Text

SELECT /*+ ORDERED NO_INDEX(PBEV PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(PV1 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(PV2 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(PV3 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(PV4 PAY_ELEMENT_ENTRY_VALUES_F_N1) */ PP.EMPLOYEE_NUMBER
, PJ.ASSIGNMENT_NUMBER
, PJ.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, 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))
, SUBSTR (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 ) )
, 1
, 60 )
, SUBSTR (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 ) )
, 1
, 60 )
, SUBSTR (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 ) )
, 1
, 60 )
, SUBSTR (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 ) )
, 1
, 60 )
, ' ' WORKSITE_NUMBER
, ' ' WORKSITE_STATE_CODE
, DECODE (PPB.PAY_BASIS
, 'HOURLY'
, '4'
, 'ANNUAL'
, '2'
, 'MONTHLY'
, '2'
, 'PERIOD'
, '2'
, PPB.PAY_BASIS ) RATE_CODE
, DECODE (PPB.PAY_BASIS
, 'HOURLY'
, PBEV.SCREEN_ENTRY_VALUE
, 'ANNUAL'
, TO_CHAR (TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE) / PTP.NUMBER_PER_FISCAL_YEAR )
, 'MONTHLY'
, TO_CHAR (TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE) * 12 / PTP.NUMBER_PER_FISCAL_YEAR )
, 'PERIOD'
, PBEV.SCREEN_ENTRY_VALUE ) BASE_RATE
, HOU.NAME
, 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
, NULL) HOURLY_RATE
, DECODE (PPB.PAY_BASIS
, 'HOURLY'
, NULL
, 'ANNUAL'
, PBEV.SCREEN_ENTRY_VALUE
, 'MONTHLY'
, TO_CHAR (TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE) * 12)
, 'PERIOD'
, TO_CHAR (TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE) * PTP.NUMBER_PER_FISCAL_YEAR )
, 'ERROR' ) ANNUAL_SALARY
, DECODE (PPS.FINAL_PROCESS_DATE
, NULL
, NVL (ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS)
, DECODE (GREATEST (PPS.FINAL_PROCESS_DATE
, TRUNC (HR_CERIDIAN.GET_CER_EXTRACT_DATE))
, PPS.FINAL_PROCESS_DATE
, NVL (ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS )
, 'D' ) )
, PJ.NORMAL_HOURS
, HS.SEGMENT5
, PJ.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, PPP.CHANGE_DATE
, GREATEST( NVL(PP.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PJ.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PJ.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PJ.EFFECTIVE_START_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PBEE.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PBEE.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PBEE.EFFECTIVE_START_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PBEE.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PBEE.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PBEE.EFFECTIVE_START_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, 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 PAY_ELEMENT_TYPES_F PT
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PAY_INPUT_VALUES_F PIV4
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_ENTRIES_F PB
, PER_ALL_ASSIGNMENTS_F PJ
, PAY_PAYROLLS_F PPR
, PER_TIME_PERIOD_TYPES PTP
, PAY_JOB_WC_CODE_USAGES PJW
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_PAY_BASES PPB
, PER_PAY_PROPOSALS PPP
, HR_SOFT_CODING_KEYFLEX HS
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
, PAY_ELEMENT_ENTRY_VALUES_F PV4
, HR_LOCATIONS_ALL HL
, PAY_INPUT_VALUES_F PPIV
, PAY_ELEMENT_TYPES_F PBET
, PAY_ELEMENT_LINKS_F PBEL
, PAY_ELEMENT_ENTRIES_F PBEE
, PAY_ELEMENT_ENTRY_VALUES_F PBEV
, PER_PERIODS_OF_SERVICE PPS
, PER_ALL_PEOPLE_F PP
, HR_ORGANIZATION_UNITS HOU
WHERE TRUNC(SYSDATE) BETWEEN PBET.EFFECTIVE_START_DATE
AND PBET.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PBEL.EFFECTIVE_START_DATE
AND PBEL.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPR.EFFECTIVE_START_DATE
AND PPR.EFFECTIVE_END_DATE
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 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 PP.EMPLOYEE_NUMBER IS NOT NULL
AND PJ.JOB_ID IS NOT NULL
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PJ.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 = PJ.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y')
AND PPP.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (SELECT MAX(CHANGE_DATE)
FROM PER_PAY_PROPOSALS PP2
WHERE PP2.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND CHANGE_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE))
AND PJ.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PPB.INPUT_VALUE_ID = PBEV.INPUT_VALUE_ID
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 PB.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND PB.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PT.ELEMENT_NAME = 'CERIDIAN EMPLOYEE CODES'
AND (PIV1.INPUT_VALUE_ID = PV1.INPUT_VALUE_ID
AND PIV1.DISPLAY_SEQUENCE = 1
AND PV1.ELEMENT_ENTRY_ID = PB.ELEMENT_ENTRY_ID
AND PV1.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV1.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PIV1.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PIV1.NAME <> 'PAY VALUE')
AND (PV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND PIV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PV2.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV2.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID)
AND (PV3.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID
AND PIV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PV3.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV3.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV3.ELEMENT_TYPE_ID)
AND (PV4.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID
AND PIV4.DISPLAY_SEQUENCE = 4
AND PB.ELEMENT_ENTRY_ID = PV4.ELEMENT_ENTRY_ID
AND PV4.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV4.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV4.ELEMENT_TYPE_ID)
AND PB.EFFECTIVE_START_DATE = (SELECT MAX(PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
WHERE PB2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PB2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND PB2.ASSIGNMENT_ID = PB.ASSIGNMENT_ID)
AND PBEE.EFFECTIVE_START_DATE = (SELECT MAX(PBEE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEE2
WHERE PBEE2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PBEE2.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEE2.ELEMENT_LINK_ID = PBEL.ELEMENT_LINK_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_F 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 PBEV.EFFECTIVE_START_DATE = (SELECT MAX(PBEV2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEV2
WHERE PBEV2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
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(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PPIV2.INPUT_VALUE_ID = PPIV.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)
AND AST.ASSIGNMENT_STATUS_TYPE_ID = PJ.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND AST.BUSINESS_GROUP_ID = ASTA.BUSINESS_GROUP_ID(+)

Columns

Name
EMPLOYEE_NUMBER
ASSIGNMENT_NUMBER
PRIMARY_FLAG
SOCIAL_SECURITY_NUMBER
POL_EFFECTIVE_START_DATE
POL_EFFECTIVE_END_DATE
EMP_CODE
HOLIDAY_CODE
VACATION_CODE
SICK_CODE
WORKSITE_NUMBER
WORKSITE_STATE_CODE
RATE_CODE
BASE_RATE
COMPANY
WORKERS_COMP_CODE
PAY_FREQUENCY
HOURLY_RATE
ANNUAL_SALARY
PAYROLL_STATUS
STANDARD_HOURS
SHIFT
ASSIGNMENT_ID
BUSINESS_GROUP_ID
PERSON_ID
PREVIOUS_SALARY_CHANGE_DATE
LAST_UPDATE_DATE