VIEW: APPS.PAY_JP_CMA_TRAIN_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.display_sequence,1,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,2,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,3,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,4,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,5,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,6,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,7,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,8,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,9,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,10,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,11,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,12,peev.input_value_id,NULL)) , min(distinct decode(piv.display_sequence,13,peev.input_value_id,NULL)) , substrb(min(distinct decode(piv.display_sequence,1,peev.screen_entry_value,NULL)),1,30) , substrb(min(distinct decode(piv.display_sequence,2,peev.screen_entry_value,NULL)),1,60) , substrb(min(distinct decode(piv.display_sequence,3,peev.screen_entry_value,NULL)),1,60) , substrb(min(distinct decode(piv.display_sequence,4,peev.screen_entry_value,NULL)),1,60) , substrb(min(distinct decode(piv.display_sequence,5,peev.screen_entry_value,NULL)),1,30) , substrb(min(distinct decode(piv.display_sequence,6,peev.screen_entry_value,NULL)),1,30) , min(distinct decode(piv.display_sequence,7,to_number(peev.screen_entry_value),NULL)) , substrb(min(distinct decode(piv.display_sequence,8,peev.screen_entry_value,NULL)),1,6) , substrb(min(distinct decode(piv.display_sequence,9,peev.screen_entry_value,NULL)),1,6) , substrb(min(distinct decode(piv.display_sequence,10,peev.screen_entry_value,NULL)),1,30) , min(distinct decode(piv.display_sequence,11,fnd_date.canonical_to_date(peev.screen_entry_value),NULL)) , substrb(min(distinct decode(piv.display_sequence,12,peev.screen_entry_value,NULL)),1,30) , substrb(min(distinct decode(piv.display_sequence,13,peev.screen_entry_value,NULL)),1,60) , substrb(min(distinct decode(piv.display_sequence,1 ,hr_general.decode_lookup(piv.lookup_type,peev.screen_entry_value),NULL)),1,80) , substrb(min(distinct decode(piv.display_sequence,5 ,hr_general.decode_lookup(piv.lookup_type,peev.screen_entry_value),NULL)),1,80) , substrb(min(distinct decode(piv.display_sequence,6 ,hr_general.decode_lookup(piv.lookup_type,peev.screen_entry_value),NULL)),1,80) , substrb(min(distinct decode(piv.display_sequence,10 ,hr_general.decode_lookup(piv.lookup_type,peev.screen_entry_value),NULL)),1,80) , substrb(min(distinct decode(piv.display_sequence,12 ,hr_general.decode_lookup(piv.lookup_type,peev.screen_entry_value),NULL)),1,80) , min(distinct pee.attribute_category) , min(distinct pee.attribute1) , min(distinct pee.attribute2) , min(distinct pee.attribute3) , min(distinct pee.attribute4) , min(distinct pee.attribute5) , min(distinct pee.attribute6) , min(distinct pee.attribute7) , min(distinct pee.attribute8) , min(distinct pee.attribute9) , min(distinct pee.attribute10) , min(distinct pee.attribute11) , min(distinct pee.attribute12) , min(distinct pee.attribute13) , min(distinct pee.attribute14) , min(distinct pee.attribute15) , min(distinct pee.attribute16) , min(distinct pee.attribute17) , min(distinct pee.attribute18) , min(distinct pee.attribute19) , min(distinct pee.attribute20) , 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 = 'SAL_CMA_PUBLIC_TRANSPORT_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.DISPLAY_SEQUENCE
, 1
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 2
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 3
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 4
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 5
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 6
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 7
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 8
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 9
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 10
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 11
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 12
, PEEV.INPUT_VALUE_ID
, NULL))
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 13
, PEEV.INPUT_VALUE_ID
, NULL))
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 1
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 30)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 2
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 60)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 3
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 60)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 4
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 60)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 5
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 30)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 6
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 30)
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 7
, TO_NUMBER(PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 8
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 6)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 9
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 6)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 10
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 30)
, MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 11
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 12
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 30)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 13
, PEEV.SCREEN_ENTRY_VALUE
, NULL))
, 1
, 60)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 1
, HR_GENERAL.DECODE_LOOKUP(PIV.LOOKUP_TYPE
, PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, 1
, 80)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 5
, HR_GENERAL.DECODE_LOOKUP(PIV.LOOKUP_TYPE
, PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, 1
, 80)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 6
, HR_GENERAL.DECODE_LOOKUP(PIV.LOOKUP_TYPE
, PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, 1
, 80)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 10
, HR_GENERAL.DECODE_LOOKUP(PIV.LOOKUP_TYPE
, PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, 1
, 80)
, SUBSTRB(MIN(DISTINCT DECODE(PIV.DISPLAY_SEQUENCE
, 12
, HR_GENERAL.DECODE_LOOKUP(PIV.LOOKUP_TYPE
, PEEV.SCREEN_ENTRY_VALUE)
, NULL))
, 1
, 80)
, MIN(DISTINCT PEE.ATTRIBUTE_CATEGORY)
, MIN(DISTINCT PEE.ATTRIBUTE1)
, MIN(DISTINCT PEE.ATTRIBUTE2)
, MIN(DISTINCT PEE.ATTRIBUTE3)
, MIN(DISTINCT PEE.ATTRIBUTE4)
, MIN(DISTINCT PEE.ATTRIBUTE5)
, MIN(DISTINCT PEE.ATTRIBUTE6)
, MIN(DISTINCT PEE.ATTRIBUTE7)
, MIN(DISTINCT PEE.ATTRIBUTE8)
, MIN(DISTINCT PEE.ATTRIBUTE9)
, MIN(DISTINCT PEE.ATTRIBUTE10)
, MIN(DISTINCT PEE.ATTRIBUTE11)
, MIN(DISTINCT PEE.ATTRIBUTE12)
, MIN(DISTINCT PEE.ATTRIBUTE13)
, MIN(DISTINCT PEE.ATTRIBUTE14)
, MIN(DISTINCT PEE.ATTRIBUTE15)
, MIN(DISTINCT PEE.ATTRIBUTE16)
, MIN(DISTINCT PEE.ATTRIBUTE17)
, MIN(DISTINCT PEE.ATTRIBUTE18)
, MIN(DISTINCT PEE.ATTRIBUTE19)
, MIN(DISTINCT PEE.ATTRIBUTE20)
, 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 = 'SAL_CMA_PUBLIC_TRANSPORT_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