VIEW: APPS.PAY_JP_NONRESIDENT_ENTRIES_V

Source

View Text - Preformatted

SELECT pee.element_entry_id, pee.effective_start_date, pee.effective_end_date, pee.assignment_id, pel.element_link_id, pee.object_version_number, min(distinct decode(piv.name,'NRES_START_DATE',peev.input_value_id,NULL)), min(distinct decode(piv.name,'PROJECTED_RES_DATE',peev.input_value_id,NULL)), min(distinct decode(piv.name,'NRES_START_DATE',fnd_date.canonical_to_date(peev.screen_entry_value),NULL)), min(distinct decode(piv.name,'PROJECTED_RES_DATE',fnd_date.canonical_to_date(peev.screen_entry_value),NULL)), min(distinct pee.creation_date), min(distinct pee.created_by), min(distinct pee.last_update_date), min(distinct pee.last_updated_by), min(distinct pee.last_update_login) from pay_input_values_f piv, pay_element_entry_values_f peev, pay_element_links_f pel, pay_element_entries_f pee, pay_element_types_f pet, fnd_sessions fnd where fnd.session_id = userenv('sessionid') and pet.element_name = 'COM_NRES_INFO' and fnd.effective_date between pet.effective_start_date and pet.effective_end_date and pee.entry_type = 'E' and fnd.effective_date between pee.effective_start_date and pee.effective_end_date and pel.element_link_id = pee.element_link_id and pel.element_type_id + 0 = pet.element_type_id and fnd.effective_date between pel.effective_start_date and pel.effective_end_date and peev.element_entry_id = nvl(pee.element_entry_id,pel.element_link_id) and peev.effective_start_date = pee.effective_start_date and peev.effective_end_date = pee.effective_end_date and piv.input_value_id = peev.input_value_id and fnd.effective_date between piv.effective_start_date and piv.effective_end_date group by pee.element_entry_id, pee.effective_start_date, pee.effective_end_date, pee.assignment_id, pel.element_link_id, pee.object_version_number

View Text - HTML Formatted

SELECT PEE.ELEMENT_ENTRY_ID
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE
, PEE.ASSIGNMENT_ID
, PEL.ELEMENT_LINK_ID
, PEE.OBJECT_VERSION_NUMBER
, MIN(DISTINCT DECODE(PIV.NAME
, 'NRES_START_DATE'
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.NAME
, 'PROJECTED_RES_DATE'
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.NAME
, 'NRES_START_DATE'
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, MIN(DISTINCT DECODE(PIV.NAME
, 'PROJECTED_RES_DATE'
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, MIN(DISTINCT PEE.CREATION_DATE)
, MIN(DISTINCT PEE.CREATED_BY)
, MIN(DISTINCT PEE.LAST_UPDATE_DATE)
, MIN(DISTINCT PEE.LAST_UPDATED_BY)
, MIN(DISTINCT PEE.LAST_UPDATE_LOGIN)
FROM PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_ENTRY_VALUES_F PEEV
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_TYPES_F PET
, FND_SESSIONS FND
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND PET.ELEMENT_NAME = 'COM_NRES_INFO'
AND FND.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PEE.ENTRY_TYPE = 'E'
AND FND.EFFECTIVE_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
AND PEL.ELEMENT_TYPE_ID + 0 = PET.ELEMENT_TYPE_ID
AND FND.EFFECTIVE_DATE BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND PEEV.ELEMENT_ENTRY_ID = NVL(PEE.ELEMENT_ENTRY_ID
, PEL.ELEMENT_LINK_ID)
AND PEEV.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE
AND PEEV.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND PIV.INPUT_VALUE_ID = PEEV.INPUT_VALUE_ID
AND FND.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE GROUP BY PEE.ELEMENT_ENTRY_ID
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE
, PEE.ASSIGNMENT_ID
, PEL.ELEMENT_LINK_ID
, PEE.OBJECT_VERSION_NUMBER