VIEW: APPS.HR_DE_TAX_INFORMATION_V

Source

View Text - Preformatted

SELECT PEE.ROWID ROW_ID, PEE.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, ELEMENT_ENTRY_ID, substr(ENTRY_INFORMATION1,1,30) TAX_CARD_NUMBER, substr(ENTRY_INFORMATION2,1,30) TAX_YEAR, fnd_date.canonical_to_date(ENTRY_INFORMATION3) ISSUE_DATE, substr(ENTRY_INFORMATION4,1,150) CARD_ISSUER, substr(hr_de_general.get_TAX_OFFICE_details(to_number(entry_information20)),1,4), substr(hr_de_general.get_TAX_OFFICE_details(to_number(entry_information20)),5,240), to_number(entry_information20), substr(ENTRY_INFORMATION7,1,150) TAX_CARD_LOCATION, substr(ENTRY_INFORMATION8,1,30) Tax_status_code, substr(HR_GENERAL.DECODE_LOOKUP('DE_TAX_STATUS',ENTRY_INFORMATION8),1,30) Tax_Status, substr(ENTRY_INFORMATION9,1,30) Tax_table_code, substr(HR_GENERAL.DECODE_LOOKUP('DE_TAX_TABLE',ENTRY_INFORMATION9),1,30) Tax_Table, substr(ENTRY_INFORMATION10,1,30) Tax_class_code, substr(HR_GENERAL.DECODE_LOOKUP('DE_TAX_CLASS',ENTRY_INFORMATION10),1,80) Tax_Class, substr(ENTRY_INFORMATION24,1,30) yearly_church_tax_flag_code, substr(HR_GENERAL.DECODE_LOOKUP('YES_NO',ENTRY_INFORMATION24),1,30) yearly_church_tax_flag, substr(ENTRY_INFORMATION25,1,30) max_church_tax_flag_code, substr(HR_GENERAL.DECODE_LOOKUP('YES_NO',ENTRY_INFORMATION25),1,30) Max_church_tax_flag, substr(ENTRY_INFORMATION15,1,30) yearly_income_tax_flag_code, substr(HR_GENERAL.DECODE_LOOKUP('YES_NO',ENTRY_INFORMATION15),1,30) Yearly_Income_Tax_Calc_flag, substr(ENTRY_INFORMATION16,1,30) chamber_contribution_code, substr(HR_GENERAL.DECODE_LOOKUP('YES_NO',ENTRY_INFORMATION16),1,30) chamber_contribution, substr(ENTRY_INFORMATION17,1,30) pensioners_tax_flag_code, substr(HR_GENERAL.DECODE_LOOKUP('YES_NO',ENTRY_INFORMATION17),1,30) pensioners_tax_flag, substr(ENTRY_INFORMATION11,1,30) no_of_children, to_number(ENTRY_INFORMATION12) yearly_tax_free_income, to_number(ENTRY_INFORMATION13) monthly_tax_free_income, to_number(ENTRY_INFORMATION14) additional_year_taxable_income, to_number(ENTRY_INFORMATION18) additional_mth_taxable_income, substr(ENTRY_INFORMATION19,1,30) employee_church_code, substr(HR_GENERAL.DECODE_LOOKUP('DE_CHURCH',ENTRY_INFORMATION19),1,80) employee_church_name, substr(ENTRY_INFORMATION21,1,30) spouse_church_code, substr(HR_GENERAL.DECODE_LOOKUP('DE_CHURCH',ENTRY_INFORMATION21),1,80) spouse_church_name, substr(ENTRY_INFORMATION23,1,30) bundesland_church_code, substr(HR_GENERAL.DECODE_LOOKUP('DE_FED_STATE',ENTRY_INFORMATION23),1,80) bundesland_church, EFFECTIVE_START_DATE effective_start_date, EFFECTIVE_END_DATE effective_end_date, PEE.ASSIGNMENT_ID, ENTRY_INFORMATION26, DECODE(SUBSTR(ENTRY_INFORMATION26,1,1),'U','USER','BATCH') updated_by, hr_de_general.max_tax_info_date(ELEMENT_ENTRY_ID) updated_date From PAY_ELEMENT_ENTRIES_F PEE ,FND_SESSIONS SES WHERE ENTRY_INFORMATION_CATEGORY = 'DE_TAX INFORMATION' 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
, 30) TAX_CARD_NUMBER
, SUBSTR(ENTRY_INFORMATION2
, 1
, 30) TAX_YEAR
, FND_DATE.CANONICAL_TO_DATE(ENTRY_INFORMATION3) ISSUE_DATE
, SUBSTR(ENTRY_INFORMATION4
, 1
, 150) CARD_ISSUER
, SUBSTR(HR_DE_GENERAL.GET_TAX_OFFICE_DETAILS(TO_NUMBER(ENTRY_INFORMATION20))
, 1
, 4)
, SUBSTR(HR_DE_GENERAL.GET_TAX_OFFICE_DETAILS(TO_NUMBER(ENTRY_INFORMATION20))
, 5
, 240)
, TO_NUMBER(ENTRY_INFORMATION20)
, SUBSTR(ENTRY_INFORMATION7
, 1
, 150) TAX_CARD_LOCATION
, SUBSTR(ENTRY_INFORMATION8
, 1
, 30) TAX_STATUS_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_TAX_STATUS'
, ENTRY_INFORMATION8)
, 1
, 30) TAX_STATUS
, SUBSTR(ENTRY_INFORMATION9
, 1
, 30) TAX_TABLE_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_TAX_TABLE'
, ENTRY_INFORMATION9)
, 1
, 30) TAX_TABLE
, SUBSTR(ENTRY_INFORMATION10
, 1
, 30) TAX_CLASS_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_TAX_CLASS'
, ENTRY_INFORMATION10)
, 1
, 80) TAX_CLASS
, SUBSTR(ENTRY_INFORMATION24
, 1
, 30) YEARLY_CHURCH_TAX_FLAG_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('YES_NO'
, ENTRY_INFORMATION24)
, 1
, 30) YEARLY_CHURCH_TAX_FLAG
, SUBSTR(ENTRY_INFORMATION25
, 1
, 30) MAX_CHURCH_TAX_FLAG_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('YES_NO'
, ENTRY_INFORMATION25)
, 1
, 30) MAX_CHURCH_TAX_FLAG
, SUBSTR(ENTRY_INFORMATION15
, 1
, 30) YEARLY_INCOME_TAX_FLAG_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('YES_NO'
, ENTRY_INFORMATION15)
, 1
, 30) YEARLY_INCOME_TAX_CALC_FLAG
, SUBSTR(ENTRY_INFORMATION16
, 1
, 30) CHAMBER_CONTRIBUTION_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('YES_NO'
, ENTRY_INFORMATION16)
, 1
, 30) CHAMBER_CONTRIBUTION
, SUBSTR(ENTRY_INFORMATION17
, 1
, 30) PENSIONERS_TAX_FLAG_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('YES_NO'
, ENTRY_INFORMATION17)
, 1
, 30) PENSIONERS_TAX_FLAG
, SUBSTR(ENTRY_INFORMATION11
, 1
, 30) NO_OF_CHILDREN
, TO_NUMBER(ENTRY_INFORMATION12) YEARLY_TAX_FREE_INCOME
, TO_NUMBER(ENTRY_INFORMATION13) MONTHLY_TAX_FREE_INCOME
, TO_NUMBER(ENTRY_INFORMATION14) ADDITIONAL_YEAR_TAXABLE_INCOME
, TO_NUMBER(ENTRY_INFORMATION18) ADDITIONAL_MTH_TAXABLE_INCOME
, SUBSTR(ENTRY_INFORMATION19
, 1
, 30) EMPLOYEE_CHURCH_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_CHURCH'
, ENTRY_INFORMATION19)
, 1
, 80) EMPLOYEE_CHURCH_NAME
, SUBSTR(ENTRY_INFORMATION21
, 1
, 30) SPOUSE_CHURCH_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_CHURCH'
, ENTRY_INFORMATION21)
, 1
, 80) SPOUSE_CHURCH_NAME
, SUBSTR(ENTRY_INFORMATION23
, 1
, 30) BUNDESLAND_CHURCH_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DE_FED_STATE'
, ENTRY_INFORMATION23)
, 1
, 80) BUNDESLAND_CHURCH
, EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PEE.ASSIGNMENT_ID
, ENTRY_INFORMATION26
, DECODE(SUBSTR(ENTRY_INFORMATION26
, 1
, 1)
, 'U'
, 'USER'
, 'BATCH') UPDATED_BY
, HR_DE_GENERAL.MAX_TAX_INFO_DATE(ELEMENT_ENTRY_ID) UPDATED_DATE
FROM PAY_ELEMENT_ENTRIES_F PEE
, FND_SESSIONS SES
WHERE ENTRY_INFORMATION_CATEGORY = 'DE_TAX INFORMATION'
AND SES.SESSION_ID = USERENV('SESSIONID')
AND ENTRY_TYPE = 'E'
AND SES.EFFECTIVE_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE