VIEW: APPS.HR_DE_SOCIAL_INSURANCE_INFO_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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