View: HR_DE_SOCIAL_INSURANCE_INFO_V

Product:	PER - Human Resources
Description:	View based on Element Entry tables for German Social Insurance records
Implementation/DBA Data:	APPS.HR_DE_SOCIAL_INSURANCE_INFO_V

View Text

SELECT PEE.ROWID ROW_ID
, PEE.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, ELEMENT_ENTRY_ID
, SUBSTR(ENTRY_INFORMATION1
, 1
, 4) CONTRIBUTION_KEY
, SUBSTR(ENTRY_INFORMATION1
, 1
, 1) HEALTH_KEY_CODE
, HR_GENERAL.DECODE_LOOKUP( 'DE_HEALTH_KEY'
, SUBSTR(ENTRY_INFORMATION1
, 1
, 1)) HEALTH_KEY_DESC
, SUBSTR(ENTRY_INFORMATION1
, 2
, 1) PENSION_KEY_CODE
, HR_GENERAL.DECODE_LOOKUP( 'DE_PENSION_KEY'
, SUBSTR(ENTRY_INFORMATION1
, 2
, 1)) PENSION_KEY_DESC
, SUBSTR(ENTRY_INFORMATION1
, 3
, 1) UNEMPLOYMENT_KEY_CODE
, HR_GENERAL.DECODE_LOOKUP( 'DE_UNEMPLOYMENT_KEY'
, SUBSTR(ENTRY_INFORMATION1
, 3
, 1)) UNEMPLOYMENT_KEY_DESC
, SUBSTR(ENTRY_INFORMATION1
, 4
, 1) SPECIAL_CARE_KEY_CODE
, HR_GENERAL.DECODE_LOOKUP('DE_SPECIAL_CARE_KEY'
, SUBSTR(ENTRY_INFORMATION1
, 4
, 1)) SPECIAL_CARE_KEY_DESC
, TO_NUMBER(ENTRY_INFORMATION2) HEALTH_ORG_ID
, HR_DE_GENERAL.GET_ORG_NAME(TO_NUMBER(ENTRY_INFORMATION2)) HEALTH_PROVIDER
, TO_NUMBER(ENTRY_INFORMATION3) PENSION_ORG_ID
, HR_DE_GENERAL.GET_ORG_NAME(TO_NUMBER(ENTRY_INFORMATION3)) PENSION_PROVIDER
, TO_NUMBER(ENTRY_INFORMATION4) UNEMPLOYMENT_ORG_ID
, HR_DE_GENERAL.GET_ORG_NAME(TO_NUMBER(ENTRY_INFORMATION4)) UNEMPLOYMENT_PROVIDER
, TO_NUMBER(ENTRY_INFORMATION5) SPECIAL_CARE_ORG_ID
, HR_DE_GENERAL.GET_ORG_NAME(TO_NUMBER(ENTRY_INFORMATION5)) SPECIAL_CARE_PROVIDER
, SUBSTR(ENTRY_INFORMATION6
, 1
, 9) OCCUPATION_KEY
, SUBSTR(ENTRY_INFORMATION6
, 1
, 3) JOB_KEY_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_JOB_KEY'
, SUBSTR(ENTRY_INFORMATION6
, 1
, 3))
, 1
, 80) JOB_KEY
, SUBSTR(ENTRY_INFORMATION6
, 4
, 1) PROFESSION_KEY_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_PROFESSION_KEY'
, SUBSTR(ENTRY_INFORMATION6
, 4
, 1))
, 1
, 80) PROFESSION_KEY
, SUBSTR(ENTRY_INFORMATION6
, 5
, 1) EDUCATION_KEY_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_EDUCATION_KEY'
, SUBSTR(ENTRY_INFORMATION6
, 5
, 1))
, 1
, 80) EDUCATION_KEY
, SUBSTR(ENTRY_INFORMATION7
, 1
, 3) PEOPLE_CLASS_KEY
, HR_GENERAL.DECODE_LOOKUP('DE_PEOPLE_GROUP_KEY'
, SUBSTR(ENTRY_INFORMATION7
, 1
, 3)) PEOPLE_CLASS_DESC
, SUBSTR (ENTRY_INFORMATION8
, 1
, 1) LEGAL_AREA_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_LEGAL_AREA'
, ENTRY_INFORMATION8)
, 1
, 10) LEGAL_AREA
, SUBSTR(ENTRY_INFORMATION9
, 1
, 20) HEALTH_INSURANCE_NUMBER
, SUBSTR(ENTRY_INFORMATION10
, 1
, 10) PRIVATE_HEALTH_PROVIDER_ORG_ID
, HR_DE_GENERAL.GET_ORG_NAME(TO_NUMBER(ENTRY_INFORMATION10)) PRIVATE_HEALTH_PROVIDER
, SUBSTR(ENTRY_INFORMATION11
, 1
, 10) PRIVATE_HEALTH_CONTRIBUTION
, SUBSTR(ENTRY_INFORMATION12
, 1
, 10) VOL_PENSION_PROVIDER_ORG_ID
, HR_DE_GENERAL.GET_ORG_NAME(TO_NUMBER(ENTRY_INFORMATION12)) VOLUNTARY_PENSION_PROVIDER
, SUBSTR(ENTRY_INFORMATION13
, 1
, 10) VOLUNTARY_PENSION_CONTRIBUTION
, SUBSTR(ENTRY_INFORMATION17
, 1
, 20) VOLUNTARY_PENSION_NUMBER
, SUBSTR(ENTRY_INFORMATION14
, 1
, 10) CONTRIBUTION_TRANSFER_BY_CODE
, SUBSTR(ENTRY_INFORMATION15
, 1
, 10) SPECIAL_CARE_CONTRIBUTION
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_CONTRIBUTION_TRANSFER_BY'
, ENTRY_INFORMATION14)
, 1
, 10) CONTRIBUTION_TRANSFER_BY
, ENTRY_INFORMATION16 PRIVATELY_INSURED_FLAG
, TO_NUMBER(ENTRY_INFORMATION18) ADD_INSURANCE_PROVIDER
, HR_DE_GENERAL.GET_ORG_NAME(TO_NUMBER(ENTRY_INFORMATION18)) D_ADD_INSURANCE_PROVIDER
, ENTRY_INFORMATION19 ADD_INS_MEMSHIP_NUMBER
, ENTRY_INFORMATION20 ADD_INS_STATUS
, HR_GENERAL.DECODE_LOOKUP('DE_PQH_STATUS_OF_MEMBERSHIP'
, ENTRY_INFORMATION20) D_ADD_INS_STATUS
, ENTRY_INFORMATION21 ADD_INS_START_DATE
, ENTRY_INFORMATION22 ADD_INS_END_DATE
, TO_NUMBER(ENTRY_INFORMATION23) ADD_INS_END_REASON
, HR_DE_GENERAL.GET_END_REASON_NO(TO_NUMBER(ENTRY_INFORMATION23)) D_ADD_INS_END_REASON_NO
, HR_DE_GENERAL.GET_END_REASON_DESC(TO_NUMBER(ENTRY_INFORMATION23)) D_END_REASON_TEXT
, EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PEE.ASSIGNMENT_ID
FROM PAY_ELEMENT_ENTRIES_F PEE
, FND_SESSIONS SES
WHERE ENTRY_INFORMATION_CATEGORY = 'DE_SOCIAL INSURANCE'
AND SES.SESSION_ID = USERENV('SESSIONID')
AND ENTRY_TYPE = 'E'
AND SES.EFFECTIVE_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE

Columns