View: HR_CERIDIAN_BENEFIT_V

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

View Text

SELECT PP.EMPLOYEE_NUMBER
, HO.NAME
, PT.REPORTING_NAME
, DECODE (IV4.UOM
, 'N'
, '%'||NVL(PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION)
, NVL(PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION))
, PT.ELEMENT_INFORMATION4
, TP.NUMBER_PER_FISCAL_YEAR
, DECODE(MAX(PBM.EFFECTIVE_END_DATE)
, GREATEST(MAX(PBM.EFFECTIVE_END_DATE)
, SYSDATE)
, 'Y'
, 'N')
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST(NVL(PB.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PB.EFFECTIVE_START_DATE)
, GREATEST(NVL(BCON.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, BCON.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV4
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PER_TIME_PERIOD_TYPES TP
, BEN_BENEFIT_CONTRIBUTIONS_F BCON
, PAY_INPUT_VALUES_F IV4
, PAY_INPUT_VALUES_F IV1
, BEN_BENEFIT_CLASSIFICATIONS BC
, PAY_ELEMENT_TYPES_F PT
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_ENTRIES_F PB
, PAY_ELEMENT_ENTRIES_F PBM
, HR_ALL_ORGANIZATION_UNITS HO
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PJ
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
WHERE HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND HS.SEGMENT1 = HO.ORGANIZATION_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PP.PERSON_ID = PPS.PERSON_ID
AND PJ.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND (PV4.INPUT_VALUE_ID + 0 = IV4.INPUT_VALUE_ID
AND IV4.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV4.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = IV4.ELEMENT_TYPE_ID)
AND PB.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PJ.PRIMARY_FLAG = 'Y'
AND BC.BENEFIT_CLASSIFICATION_ID = PT.BENEFIT_CLASSIFICATION_ID
AND BC.CONTRIBUTIONS_USED = 'Y'
AND BCON.EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE)
FROM BEN_BENEFIT_CONTRIBUTIONS_F BCON2
WHERE BCON2.ELEMENT_TYPE_ID = BCON.ELEMENT_TYPE_ID
AND BCON2.COVERAGE_TYPE = BCON.COVERAGE_TYPE
AND EFFECTIVE_START_DATE <= TRUNC (SYSDATE))
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(SYSDATE)
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 PV4.EFFECTIVE_START_DATE = (SELECT MAX(PV42.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV42
WHERE (PV42.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PV42.ELEMENT_ENTRY_VALUE_ID = PV4.ELEMENT_ENTRY_VALUE_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(SYSDATE)
AND PV12.ELEMENT_ENTRY_VALUE_ID = PV1.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(SYSDATE)
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(SYSDATE)
AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID))
AND IV4.EFFECTIVE_START_DATE = (SELECT MAX(IV42.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F IV42
WHERE (IV42.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND IV42.INPUT_VALUE_ID = IV4.INPUT_VALUE_ID))
AND IV1.EFFECTIVE_START_DATE = (SELECT MAX(IV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F IV12
WHERE (IV12.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND IV12.INPUT_VALUE_ID = IV1.INPUT_VALUE_ID))
AND (PV1.INPUT_VALUE_ID + 0 = IV1.INPUT_VALUE_ID
AND IV1.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV1.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = IV1.ELEMENT_TYPE_ID
AND IV1.NAME = 'COVERAGE')
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 PB.ELEMENT_ENTRY_ID = PBM.ELEMENT_ENTRY_ID
AND PT.ELEMENT_INFORMATION4 = TP.PERIOD_TYPE
AND PT.ELEMENT_TYPE_ID = BCON.ELEMENT_TYPE_ID
AND BCON.COVERAGE_TYPE = PV1.SCREEN_ENTRY_VALUE
AND TRUNC (SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE GROUP BY EMPLOYEE_NUMBER
, HO.NAME
, PT.REPORTING_NAME
, IV4.UOM
, PV4.SCREEN_ENTRY_VALUE
, BCON.EMPLOYEE_CONTRIBUTION
, PT.ELEMENT_INFORMATION4
, TP.NUMBER_PER_FISCAL_YEAR
, PB.LAST_UPDATE_DATE
, PB.EFFECTIVE_START_DATE
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, BCON.LAST_UPDATE_DATE
, BCON.EFFECTIVE_START_DATE

Columns