VIEW: APPS.PAY_BE_TAX_ELEMENT_ENTRIES_V

Source

View Text - Preformatted

SELECT pee.rowid ROW_ID ,pee.element_entry_id ,max(pee.object_version_number) object_version_number ,pel.element_link_id ,min(decode(piv.name, 'RSZ Submission Needed', eev.input_value_id, null)) INPUT_VALUE_ID1 ,min(decode(piv.name, 'RSZ Submission Needed', eev.screen_entry_value, null)) RSZ_Submission_Required ,min(decode(piv.name, 'RSZ Category', eev.input_value_id, null)) INPUT_VALUE_ID2 ,min(decode(piv.name, 'RSZ Category', eev.screen_entry_value, null)) RSZ_Category ,min(decode(piv.name, 'RSZ Category', substr(HR_GENERAL.DECODE_LOOKUP ('BE_RSZ_CATEGORY', eev.screen_entry_value),1,80), null)) RSZ_Category_Meaning ,min(decode(piv.name, 'Agreed To Part Time', eev.input_value_id, null)) INPUT_VALUE_ID3 ,min(decode(piv.name, 'Agreed To Part Time', eev.screen_entry_value, null)) Working_Part_Time_Voluntarily ,min(decode(piv.name, 'Cross Border Worker', eev.input_value_id, null)) INPUT_VALUE_ID4 ,min(decode(piv.name, 'Cross Border Worker', eev.screen_entry_value, null)) Cross_Border_Worker ,min(decode(piv.name, 'Reduced Category', eev.input_value_id, null)) INPUT_VALUE_ID5 ,min(decode(piv.name, 'Reduced Category', eev.screen_entry_value, null)) Reduced_Charges_Category ,min(decode(piv.name, 'Reduced Category', substr(HR_GENERAL.DECODE_LOOKUP('BE_REDUCED_CHARGES_CATEGORY', eev.screen_entry_value),1,80), null)) Reduced_Charges_Cat_Meaning ,min(decode(piv.name, 'Reduced Category Start', eev.input_value_id, null)) INPUT_VALUE_ID6 ,min(decode(piv.name, 'Reduced Category Start',fnd_date.canonical_to_date(eev.screen_entry_value), null)) Reduced_Charges_Start_Date ,min(decode(piv.name, 'Reduced Category End', eev.input_value_id, null)) INPUT_VALUE_ID7 ,min(decode(piv.name, 'Reduced Category End',fnd_date.canonical_to_date( eev.screen_entry_value), null)) Reduced_Charges_End_Date ,pee.effective_start_date ,pee.effective_end_date ,pee.assignment_id from pay_element_entries_f pee ,pay_element_entry_values_f eev ,pay_input_values_f piv ,pay_element_links_f pel ,pay_element_types_f pet ,fnd_sessions ses where ses.session_id = userenv('SESSIONID') and pee.element_entry_id = eev.element_entry_id and ses.effective_date between pee.effective_start_date and pee.effective_end_date and eev.input_value_id + 0 = piv.input_value_id and ses.effective_date between eev.effective_start_date and eev.effective_end_date and piv.element_type_id = pet.element_type_id and ses.effective_date between piv.effective_start_date and piv.effective_end_date and pee.element_link_id = pel.element_link_id and pet.element_type_id = pel.element_type_id and ses.effective_date between pel.effective_start_date and pel.effective_end_date and pet.element_name = 'Tax Information' and ses.effective_date between pet.effective_start_date and pet.effective_end_date group by pee.rowid ,pee.element_entry_id ,pel.element_link_id ,pee.assignment_id ,pee.effective_start_date ,pee.effective_end_date

View Text - HTML Formatted

SELECT PEE.ROWID ROW_ID
, PEE.ELEMENT_ENTRY_ID
, MAX(PEE.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
, PEL.ELEMENT_LINK_ID
, MIN(DECODE(PIV.NAME
, 'RSZ SUBMISSION NEEDED'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID1
, MIN(DECODE(PIV.NAME
, 'RSZ SUBMISSION NEEDED'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) RSZ_SUBMISSION_REQUIRED
, MIN(DECODE(PIV.NAME
, 'RSZ CATEGORY'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID2
, MIN(DECODE(PIV.NAME
, 'RSZ CATEGORY'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) RSZ_CATEGORY
, MIN(DECODE(PIV.NAME
, 'RSZ CATEGORY'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP ('BE_RSZ_CATEGORY'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) RSZ_CATEGORY_MEANING
, MIN(DECODE(PIV.NAME
, 'AGREED TO PART TIME'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID3
, MIN(DECODE(PIV.NAME
, 'AGREED TO PART TIME'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) WORKING_PART_TIME_VOLUNTARILY
, MIN(DECODE(PIV.NAME
, 'CROSS BORDER WORKER'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID4
, MIN(DECODE(PIV.NAME
, 'CROSS BORDER WORKER'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) CROSS_BORDER_WORKER
, MIN(DECODE(PIV.NAME
, 'REDUCED CATEGORY'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID5
, MIN(DECODE(PIV.NAME
, 'REDUCED CATEGORY'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) REDUCED_CHARGES_CATEGORY
, MIN(DECODE(PIV.NAME
, 'REDUCED CATEGORY'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('BE_REDUCED_CHARGES_CATEGORY'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) REDUCED_CHARGES_CAT_MEANING
, MIN(DECODE(PIV.NAME
, 'REDUCED CATEGORY START'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID6
, MIN(DECODE(PIV.NAME
, 'REDUCED CATEGORY START'
, FND_DATE.CANONICAL_TO_DATE(EEV.SCREEN_ENTRY_VALUE)
, NULL)) REDUCED_CHARGES_START_DATE
, MIN(DECODE(PIV.NAME
, 'REDUCED CATEGORY END'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID7
, MIN(DECODE(PIV.NAME
, 'REDUCED CATEGORY END'
, FND_DATE.CANONICAL_TO_DATE( EEV.SCREEN_ENTRY_VALUE)
, NULL)) REDUCED_CHARGES_END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE
, PEE.ASSIGNMENT_ID
FROM PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
, FND_SESSIONS SES
WHERE SES.SESSION_ID = USERENV('SESSIONID')
AND PEE.ELEMENT_ENTRY_ID = EEV.ELEMENT_ENTRY_ID
AND SES.EFFECTIVE_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV.INPUT_VALUE_ID + 0 = PIV.INPUT_VALUE_ID
AND SES.EFFECTIVE_DATE BETWEEN EEV.EFFECTIVE_START_DATE
AND EEV.EFFECTIVE_END_DATE
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND SES.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND SES.EFFECTIVE_DATE BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND PET.ELEMENT_NAME = 'TAX INFORMATION'
AND SES.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE GROUP BY PEE.ROWID
, PEE.ELEMENT_ENTRY_ID
, PEL.ELEMENT_LINK_ID
, PEE.ASSIGNMENT_ID
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE