VIEW: APPS.PAY_AU_TFN_TAX_INFO_V

Source

View Text - Preformatted

SELECT pee.rowid , pee.object_version_number , pee.element_entry_id , eev0.INPUT_VALUE_ID , hrl0.MEANING , decode(eev0.screen_entry_value,'YS','Y','YI','Y','YC','Y','NN','N','YN','Y','Y','Y','N','N',Null) , eev1.INPUT_VALUE_ID , hrl1.MEANING , eev1.SCREEN_ENTRY_VALUE , eev3.INPUT_VALUE_ID , hrl3.MEANING , DECODE( eev3.screen_entry_value, 'N', 'N', 'Y', 'Y', 'NF','N', 'NP','N', 'NC','N', 'YF','Y', 'YP','Y', 'YC','Y', 'N' ) , DECODE( eev3.screen_entry_value, 'Y', 'X', 'N', 'X', 'NF','F', 'NP','P', 'NC','C', 'YF','F', 'YP','P', 'YC','C', 'X') , eev4.INPUT_VALUE_ID , hrl4.MEANING , eev4.SCREEN_ENTRY_VALUE , eev5.INPUT_VALUE_ID , hrl5.MEANING , decode(eev5.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N',Null) HECS_ENTRY_VALUE , hrl5.MEANING , decode(eev5.SCREEN_ENTRY_VALUE,'YY','Y','NY','Y','N') SFSS_ENTRY_VALUE , eev6.INPUT_VALUE_ID , eev6.SCREEN_ENTRY_VALUE , eev6.SCREEN_ENTRY_VALUE , eev8.INPUT_VALUE_ID , hrl8.MEANING , decode(eev8.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N',Null) SPOUSE_ENTRY_VALUE , decode(decode(eev8.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N','N'),'Y','Y',decode(eev4.SCREEN_ENTRY_VALUE,'Y','Y','N')) , eev13.INPUT_VALUE_ID , eev13.SCREEN_ENTRY_VALUE , eev13.SCREEN_ENTRY_VALUE , pee.assignment_id , pee.effective_start_date , pee.effective_end_date , trunc(fnd_date.canonical_to_date(pee.entry_information1)) from pay_element_entries_f pee , pay_element_types_f pet , pay_input_values_f piv0 , pay_element_entry_values_f eev0 , pay_input_values_f piv1 , pay_element_entry_values_f eev1 , pay_input_values_f piv3 , pay_element_entry_values_f eev3 , pay_input_values_f piv4 , pay_element_entry_values_f eev4 , pay_input_values_f piv5 , pay_element_entry_values_f eev5 , pay_input_values_f piv6 , pay_element_entry_values_f eev6 , pay_input_values_f piv8 , pay_element_entry_values_f eev8 , pay_input_values_f piv13 , pay_element_entry_values_f eev13 , hr_lookups hrl0 , hr_lookups hrl1 , hr_lookups hrl3 , hr_lookups hrl4 , hr_lookups hrl5 , hr_lookups hrl8 WHERE pet.ELEMENT_NAME= 'Tax Information' and pet.ELEMENT_TYPE_ID = piv0.ELEMENT_TYPE_ID and eev0.INPUT_VALUE_ID = piv0.INPUT_VALUE_ID and eev0.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID and pee.entry_information_category = 'AU_TAX DEDUCTIONS' and (piv0.NAME) = 'Australian Resident' and hrl0.lookup_type (+) = 'AU_AUST_RES_SENR_AUS' and hrl0.lookup_code (+) = eev0.SCREEN_ENTRY_VALUE and hrl0.enabled_flag (+)= 'Y' and eev1.INPUT_VALUE_ID = piv1.INPUT_VALUE_ID and eev1.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID and piv1.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID and (piv1.NAME) = 'Tax Free Threshold' and hrl1.lookup_type (+) = 'YES_NO' and hrl1.lookup_code (+) = eev1.SCREEN_ENTRY_VALUE and hrl1.enabled_flag (+)= 'Y' and eev3.INPUT_VALUE_ID = piv3.INPUT_VALUE_ID and piv3.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID and eev3.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID and (piv3.NAME) = 'FTA Claim' and hrl3.lookup_type (+) = 'HR_AU_FTA_PAYMENT_BASIS' and hrl3.lookup_code (+) = eev3.SCREEN_ENTRY_VALUE and hrl3.enabled_flag (+) = 'Y' and eev4.INPUT_VALUE_ID = piv4.INPUT_VALUE_ID and piv4.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID and eev4.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID and (piv4.NAME) = 'Savings Rebate' and hrl4.lookup_type(+) = 'YES_NO' and hrl4.lookup_code(+) = eev4.SCREEN_ENTRY_VALUE and hrl4.enabled_flag (+) = 'Y' and eev5.INPUT_VALUE_ID = piv5.INPUT_VALUE_ID and piv5.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID and eev5.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID and (piv5.NAME) = 'HECS' and hrl5.lookup_type(+) = 'AU_HECS_SFSS' and hrl5.lookup_code (+) = eev5.SCREEN_ENTRY_VALUE and hrl5.enabled_flag (+) = 'Y' and eev6.INPUT_VALUE_ID = piv6.INPUT_VALUE_ID and piv6.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID and eev6.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID and (piv6.NAME) = 'Date Declaration Signed' and eev8.INPUT_VALUE_ID = piv8.INPUT_VALUE_ID and piv8.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID and eev8.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID and (piv8.NAME) = 'Spouse' and hrl8.lookup_type (+) = 'AU_SPOUSE_MLS' and hrl8.lookup_code (+) = eev8.SCREEN_ENTRY_VALUE and hrl8.enabled_flag (+) = 'Y' and eev13.INPUT_VALUE_ID = piv13.INPUT_VALUE_ID and piv13.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID and eev13.ELEMENT_ENTRY_ID= pee.ELEMENT_ENTRY_ID and (piv13.NAME ) = 'Tax File Number' and pee.effective_start_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE and eev0.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE and eev1.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE and eev3.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE and eev4.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE and eev5.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE and eev6.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE and eev8.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE and eev13.effective_start_date between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE and eev0.effective_start_date between piv0.EFFECTIVE_START_DATE and piv0.EFFECTIVE_END_DATE and eev1.effective_start_date between piv1.EFFECTIVE_START_DATE and piv1.EFFECTIVE_END_DATE and eev3.effective_start_date between piv3.EFFECTIVE_START_DATE and piv3.EFFECTIVE_END_DATE and eev4.effective_start_date between piv4.EFFECTIVE_START_DATE and piv4.EFFECTIVE_END_DATE and eev5.effective_start_date between piv5.EFFECTIVE_START_DATE and piv5.EFFECTIVE_END_DATE and eev6.effective_start_date between piv6.EFFECTIVE_START_DATE and piv6.EFFECTIVE_END_DATE and eev8.effective_start_date between piv8.EFFECTIVE_START_DATE and piv8.EFFECTIVE_END_DATE and eev13.effective_start_date between piv13.EFFECTIVE_START_DATE and piv13.EFFECTIVE_END_DATE

View Text - HTML Formatted

SELECT PEE.ROWID
, PEE.OBJECT_VERSION_NUMBER
, PEE.ELEMENT_ENTRY_ID
, EEV0.INPUT_VALUE_ID
, HRL0.MEANING
, DECODE(EEV0.SCREEN_ENTRY_VALUE
, 'YS'
, 'Y'
, 'YI'
, 'Y'
, 'YC'
, 'Y'
, 'NN'
, 'N'
, 'YN'
, 'Y'
, 'Y'
, 'Y'
, 'N'
, 'N'
, NULL)
, EEV1.INPUT_VALUE_ID
, HRL1.MEANING
, EEV1.SCREEN_ENTRY_VALUE
, EEV3.INPUT_VALUE_ID
, HRL3.MEANING
, DECODE( EEV3.SCREEN_ENTRY_VALUE
, 'N'
, 'N'
, 'Y'
, 'Y'
, 'NF'
, 'N'
, 'NP'
, 'N'
, 'NC'
, 'N'
, 'YF'
, 'Y'
, 'YP'
, 'Y'
, 'YC'
, 'Y'
, 'N' )
, DECODE( EEV3.SCREEN_ENTRY_VALUE
, 'Y'
, 'X'
, 'N'
, 'X'
, 'NF'
, 'F'
, 'NP'
, 'P'
, 'NC'
, 'C'
, 'YF'
, 'F'
, 'YP'
, 'P'
, 'YC'
, 'C'
, 'X')
, EEV4.INPUT_VALUE_ID
, HRL4.MEANING
, EEV4.SCREEN_ENTRY_VALUE
, EEV5.INPUT_VALUE_ID
, HRL5.MEANING
, DECODE(EEV5.SCREEN_ENTRY_VALUE
, 'Y'
, 'Y'
, 'N'
, 'N'
, 'YY'
, 'Y'
, 'NY'
, 'N'
, NULL) HECS_ENTRY_VALUE
, HRL5.MEANING
, DECODE(EEV5.SCREEN_ENTRY_VALUE
, 'YY'
, 'Y'
, 'NY'
, 'Y'
, 'N') SFSS_ENTRY_VALUE
, EEV6.INPUT_VALUE_ID
, EEV6.SCREEN_ENTRY_VALUE
, EEV6.SCREEN_ENTRY_VALUE
, EEV8.INPUT_VALUE_ID
, HRL8.MEANING
, DECODE(EEV8.SCREEN_ENTRY_VALUE
, 'Y'
, 'Y'
, 'N'
, 'N'
, 'YY'
, 'Y'
, 'NY'
, 'N'
, NULL) SPOUSE_ENTRY_VALUE
, DECODE(DECODE(EEV8.SCREEN_ENTRY_VALUE
, 'Y'
, 'Y'
, 'N'
, 'N'
, 'YY'
, 'Y'
, 'NY'
, 'N'
, 'N')
, 'Y'
, 'Y'
, DECODE(EEV4.SCREEN_ENTRY_VALUE
, 'Y'
, 'Y'
, 'N'))
, EEV13.INPUT_VALUE_ID
, EEV13.SCREEN_ENTRY_VALUE
, EEV13.SCREEN_ENTRY_VALUE
, PEE.ASSIGNMENT_ID
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE
, TRUNC(FND_DATE.CANONICAL_TO_DATE(PEE.ENTRY_INFORMATION1))
FROM PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV0
, PAY_ELEMENT_ENTRY_VALUES_F EEV0
, PAY_INPUT_VALUES_F PIV1
, PAY_ELEMENT_ENTRY_VALUES_F EEV1
, PAY_INPUT_VALUES_F PIV3
, PAY_ELEMENT_ENTRY_VALUES_F EEV3
, PAY_INPUT_VALUES_F PIV4
, PAY_ELEMENT_ENTRY_VALUES_F EEV4
, PAY_INPUT_VALUES_F PIV5
, PAY_ELEMENT_ENTRY_VALUES_F EEV5
, PAY_INPUT_VALUES_F PIV6
, PAY_ELEMENT_ENTRY_VALUES_F EEV6
, PAY_INPUT_VALUES_F PIV8
, PAY_ELEMENT_ENTRY_VALUES_F EEV8
, PAY_INPUT_VALUES_F PIV13
, PAY_ELEMENT_ENTRY_VALUES_F EEV13
, HR_LOOKUPS HRL0
, HR_LOOKUPS HRL1
, HR_LOOKUPS HRL3
, HR_LOOKUPS HRL4
, HR_LOOKUPS HRL5
, HR_LOOKUPS HRL8
WHERE PET.ELEMENT_NAME= 'TAX INFORMATION'
AND PET.ELEMENT_TYPE_ID = PIV0.ELEMENT_TYPE_ID
AND EEV0.INPUT_VALUE_ID = PIV0.INPUT_VALUE_ID
AND EEV0.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEE.ENTRY_INFORMATION_CATEGORY = 'AU_TAX DEDUCTIONS'
AND (PIV0.NAME) = 'AUSTRALIAN RESIDENT'
AND HRL0.LOOKUP_TYPE (+) = 'AU_AUST_RES_SENR_AUS'
AND HRL0.LOOKUP_CODE (+) = EEV0.SCREEN_ENTRY_VALUE
AND HRL0.ENABLED_FLAG (+)= 'Y'
AND EEV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND EEV1.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PIV1.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND (PIV1.NAME) = 'TAX FREE THRESHOLD'
AND HRL1.LOOKUP_TYPE (+) = 'YES_NO'
AND HRL1.LOOKUP_CODE (+) = EEV1.SCREEN_ENTRY_VALUE
AND HRL1.ENABLED_FLAG (+)= 'Y'
AND EEV3.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID
AND PIV3.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND EEV3.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND (PIV3.NAME) = 'FTA CLAIM'
AND HRL3.LOOKUP_TYPE (+) = 'HR_AU_FTA_PAYMENT_BASIS'
AND HRL3.LOOKUP_CODE (+) = EEV3.SCREEN_ENTRY_VALUE
AND HRL3.ENABLED_FLAG (+) = 'Y'
AND EEV4.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID
AND PIV4.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND EEV4.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND (PIV4.NAME) = 'SAVINGS REBATE'
AND HRL4.LOOKUP_TYPE(+) = 'YES_NO'
AND HRL4.LOOKUP_CODE(+) = EEV4.SCREEN_ENTRY_VALUE
AND HRL4.ENABLED_FLAG (+) = 'Y'
AND EEV5.INPUT_VALUE_ID = PIV5.INPUT_VALUE_ID
AND PIV5.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND EEV5.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND (PIV5.NAME) = 'HECS'
AND HRL5.LOOKUP_TYPE(+) = 'AU_HECS_SFSS'
AND HRL5.LOOKUP_CODE (+) = EEV5.SCREEN_ENTRY_VALUE
AND HRL5.ENABLED_FLAG (+) = 'Y'
AND EEV6.INPUT_VALUE_ID = PIV6.INPUT_VALUE_ID
AND PIV6.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND EEV6.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND (PIV6.NAME) = 'DATE DECLARATION SIGNED'
AND EEV8.INPUT_VALUE_ID = PIV8.INPUT_VALUE_ID
AND PIV8.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND EEV8.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND (PIV8.NAME) = 'SPOUSE'
AND HRL8.LOOKUP_TYPE (+) = 'AU_SPOUSE_MLS'
AND HRL8.LOOKUP_CODE (+) = EEV8.SCREEN_ENTRY_VALUE
AND HRL8.ENABLED_FLAG (+) = 'Y'
AND EEV13.INPUT_VALUE_ID = PIV13.INPUT_VALUE_ID
AND PIV13.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND EEV13.ELEMENT_ENTRY_ID= PEE.ELEMENT_ENTRY_ID
AND (PIV13.NAME ) = 'TAX FILE NUMBER'
AND PEE.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND EEV0.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV1.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV3.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV4.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV5.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV6.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV8.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV13.EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV0.EFFECTIVE_START_DATE BETWEEN PIV0.EFFECTIVE_START_DATE
AND PIV0.EFFECTIVE_END_DATE
AND EEV1.EFFECTIVE_START_DATE BETWEEN PIV1.EFFECTIVE_START_DATE
AND PIV1.EFFECTIVE_END_DATE
AND EEV3.EFFECTIVE_START_DATE BETWEEN PIV3.EFFECTIVE_START_DATE
AND PIV3.EFFECTIVE_END_DATE
AND EEV4.EFFECTIVE_START_DATE BETWEEN PIV4.EFFECTIVE_START_DATE
AND PIV4.EFFECTIVE_END_DATE
AND EEV5.EFFECTIVE_START_DATE BETWEEN PIV5.EFFECTIVE_START_DATE
AND PIV5.EFFECTIVE_END_DATE
AND EEV6.EFFECTIVE_START_DATE BETWEEN PIV6.EFFECTIVE_START_DATE
AND PIV6.EFFECTIVE_END_DATE
AND EEV8.EFFECTIVE_START_DATE BETWEEN PIV8.EFFECTIVE_START_DATE
AND PIV8.EFFECTIVE_END_DATE
AND EEV13.EFFECTIVE_START_DATE BETWEEN PIV13.EFFECTIVE_START_DATE
AND PIV13.EFFECTIVE_END_DATE