VIEW: APPS.OTA_TRAINING_PLAN_COSTS_V

Source

View Text - Preformatted

SELECT TPC.ROWID ROW_ID,'PLAN' COST_LEVEL, TPC.TRAINING_PLAN_COST_ID ,TPC.TP_MEASUREMENT_TYPE_ID ,TMT.TP_MEASUREMENT_CODE ,LU1.MEANING MEASUREMENT_MEANING, TMT.UNIT , LU2.MEANING UNIT_MEANING, TMT.ITEM_TYPE_USAGE_ID , TPC.TRAINING_PLAN_ID ,TO_NUMBER(NULL) BOOKING_ID, TO_NUMBER(NULL) DELEGATE_PERSON_ID, NULL DELEGATE_FULL_NAME,TO_NUMBER(NULL) EVENT_ID, NULL EVENT_TITLE,TPC.AMOUNT ,TPC.CURRENCY_CODE ,TPC.BUSINESS_GROUP_ID , TPC.OBJECT_VERSION_NUMBER , TPC.ATTRIBUTE_CATEGORY ,TPC.ATTRIBUTE1 ,TPC.ATTRIBUTE2 ,TPC.ATTRIBUTE3 , TPC.ATTRIBUTE4 ,TPC.ATTRIBUTE5 ,TPC.ATTRIBUTE6 ,TPC.ATTRIBUTE7 ,TPC.ATTRIBUTE8 , TPC.ATTRIBUTE9 ,TPC.ATTRIBUTE10 ,TPC.ATTRIBUTE11 ,TPC.ATTRIBUTE12 ,TPC.ATTRIBUTE13 , TPC.ATTRIBUTE14 ,TPC.ATTRIBUTE15 ,TPC.ATTRIBUTE16 ,TPC.ATTRIBUTE17 , TPC.ATTRIBUTE18 ,TPC.ATTRIBUTE19 ,TPC.ATTRIBUTE20 ,TPC.ATTRIBUTE21 ,TPC.ATTRIBUTE22, TPC.ATTRIBUTE23 ,TPC.ATTRIBUTE24 ,TPC.ATTRIBUTE25 ,TPC.ATTRIBUTE26 ,TPC.ATTRIBUTE27 , TPC.ATTRIBUTE28 ,TPC.ATTRIBUTE29 ,TPC.ATTRIBUTE30 ,TPC.INFORMATION_CATEGORY , TPC.TP_COST_INFORMATION1 ,TPC.TP_COST_INFORMATION2 ,TPC.TP_COST_INFORMATION3 ,TPC.TP_COST_INFORMATION4 ,TPC.TP_COST_INFORMATION5 ,TPC.TP_COST_INFORMATION6 , TPC.TP_COST_INFORMATION7 ,TPC.TP_COST_INFORMATION8 ,TPC.TP_COST_INFORMATION9 , TPC.TP_COST_INFORMATION10 ,TPC.TP_COST_INFORMATION11 ,TPC.TP_COST_INFORMATION12 , TPC.TP_COST_INFORMATION13 ,TPC.TP_COST_INFORMATION14 ,TPC.TP_COST_INFORMATION15 , TPC.TP_COST_INFORMATION16 ,TPC.TP_COST_INFORMATION17 ,TPC.TP_COST_INFORMATION18 , TPC.TP_COST_INFORMATION19 ,TPC.TP_COST_INFORMATION20 ,TPC.TP_COST_INFORMATION21 ,TPC.TP_COST_INFORMATION22 ,TPC.TP_COST_INFORMATION23 ,TPC.TP_COST_INFORMATION24 ,TPC.TP_COST_INFORMATION25 ,TPC.TP_COST_INFORMATION26 ,TPC.TP_COST_INFORMATION27 , TPC.TP_COST_INFORMATION28 ,TPC.TP_COST_INFORMATION29 ,TPC.TP_COST_INFORMATION30 , TPC.CREATED_BY ,TPC.CREATION_DATE ,TPC.LAST_UPDATED_BY ,TPC.LAST_UPDATE_DATE ,TPC.LAST_UPDATE_LOGIN FROM OTA_TRAINING_PLAN_COSTS TPC, FND_COMMON_LOOKUPS LU1, FND_COMMON_LOOKUPS LU2, OTA_TP_MEASUREMENT_TYPES TMT WHERE LU1.LOOKUP_TYPE = 'OTA_PLAN_MEASUREMENT_TYPE' AND LU2.LOOKUP_TYPE = 'UNITS' AND LU1.APPLICATION_ID = 800 AND LU2.APPLICATION_ID = 800 AND TMT.COST_LEVEL = 'PLAN' AND TPC.TP_MEASUREMENT_TYPE_ID = TMT.TP_MEASUREMENT_TYPE_ID AND LU1.LOOKUP_CODE = TMT.TP_MEASUREMENT_CODE AND LU2.LOOKUP_CODE = TMT.UNIT UNION SELECT TPC.ROWID ROW_ID,'EVENT' COST_LEVEL, TPC.TRAINING_PLAN_COST_ID , TPC.TP_MEASUREMENT_TYPE_ID ,TMT.TP_MEASUREMENT_CODE ,LU1.MEANING MEASUREMENT_MEANING, TMT.UNIT , LU2.MEANING UNIT_MEANING,TMT.ITEM_TYPE_USAGE_ID ,TPC.TRAINING_PLAN_ID ,TO_NUMBER(NULL) BOOKING_ID,TO_NUMBER(NULL) DELEGATE_PERSON_ID,NULL DELEGATE_FULL_NAME, TPC.EVENT_ID ,EVT.TITLE ,TPC.AMOUNT , TPC.CURRENCY_CODE ,TPC.BUSINESS_GROUP_ID , TPC.OBJECT_VERSION_NUMBER , TPC.ATTRIBUTE_CATEGORY ,TPC.ATTRIBUTE1 ,TPC.ATTRIBUTE2 ,TPC.ATTRIBUTE3 ,TPC.ATTRIBUTE4 , TPC.ATTRIBUTE5 ,TPC.ATTRIBUTE6 ,TPC.ATTRIBUTE7 ,TPC.ATTRIBUTE8 ,TPC.ATTRIBUTE9 , TPC.ATTRIBUTE10 ,TPC.ATTRIBUTE11 ,TPC.ATTRIBUTE12 ,TPC.ATTRIBUTE13 ,TPC.ATTRIBUTE14 , TPC.ATTRIBUTE15 ,TPC.ATTRIBUTE16 ,TPC.ATTRIBUTE17 ,TPC.ATTRIBUTE18 ,TPC.ATTRIBUTE19 , TPC.ATTRIBUTE20 ,TPC.ATTRIBUTE21 ,TPC.ATTRIBUTE22 ,TPC.ATTRIBUTE23 ,TPC.ATTRIBUTE24 , TPC.ATTRIBUTE25 ,TPC.ATTRIBUTE26 ,TPC.ATTRIBUTE27 ,TPC.ATTRIBUTE28 ,TPC.ATTRIBUTE29 , TPC.ATTRIBUTE30 ,TPC.INFORMATION_CATEGORY ,TPC.TP_COST_INFORMATION1 , TPC.TP_COST_INFORMATION2 ,TPC.TP_COST_INFORMATION3 ,TPC.TP_COST_INFORMATION4 ,TPC.TP_COST_INFORMATION5 ,TPC.TP_COST_INFORMATION6 ,TPC.TP_COST_INFORMATION7 , TPC.TP_COST_INFORMATION8 ,TPC.TP_COST_INFORMATION9 ,TPC.TP_COST_INFORMATION10 , TPC.TP_COST_INFORMATION11 ,TPC.TP_COST_INFORMATION12 ,TPC.TP_COST_INFORMATION13 , TPC.TP_COST_INFORMATION14 ,TPC.TP_COST_INFORMATION15 ,TPC.TP_COST_INFORMATION16 ,TPC.TP_COST_INFORMATION17 ,TPC.TP_COST_INFORMATION18 ,TPC.TP_COST_INFORMATION19 ,TPC.TP_COST_INFORMATION20 ,TPC.TP_COST_INFORMATION21 ,TPC.TP_COST_INFORMATION22 , TPC.TP_COST_INFORMATION23 ,TPC.TP_COST_INFORMATION24 ,TPC.TP_COST_INFORMATION25 , TPC.TP_COST_INFORMATION26 ,TPC.TP_COST_INFORMATION27 ,TPC.TP_COST_INFORMATION28 , TPC.TP_COST_INFORMATION29 ,TPC.TP_COST_INFORMATION30 ,TPC.CREATED_BY , TPC.CREATION_DATE ,TPC.LAST_UPDATED_BY ,TPC.LAST_UPDATE_DATE ,TPC.LAST_UPDATE_LOGIN FROM OTA_TRAINING_PLAN_COSTS TPC, FND_COMMON_LOOKUPS LU1 , FND_COMMON_LOOKUPS LU2 , OTA_TP_MEASUREMENT_TYPES TMT, OTA_EVENTS EVT WHERE LU1.LOOKUP_TYPE = 'OTA_PLAN_MEASUREMENT_TYPE' AND LU2.LOOKUP_TYPE = 'UNITS' AND LU1.APPLICATION_ID = 800 AND LU2.APPLICATION_ID = 800 AND TMT.COST_LEVEL = 'EVENT' AND TPC.TP_MEASUREMENT_TYPE_ID = TMT.TP_MEASUREMENT_TYPE_ID AND TPC.EVENT_ID = EVT.EVENT_ID AND LU1.LOOKUP_CODE = TMT.TP_MEASUREMENT_CODE AND LU2.LOOKUP_CODE = TMT.UNIT UNION SELECT TPC.ROWID ROW_ID,'DELEGATE' COST_LEVEL, TPC.TRAINING_PLAN_COST_ID ,TPC.TP_MEASUREMENT_TYPE_ID ,TMT.TP_MEASUREMENT_CODE , LU1.MEANING ,TMT.UNIT ,LU2.MEANING ,TMT.ITEM_TYPE_USAGE_ID ,TPC.TRAINING_PLAN_ID , TPC.BOOKING_ID ,ODB.DELEGATE_PERSON_ID ,PER.FULL_NAME ,ODB.EVENT_ID ,EVT.TITLE , TPC.AMOUNT ,TPC.CURRENCY_CODE ,TPC.BUSINESS_GROUP_ID , TPC.OBJECT_VERSION_NUMBER , TPC.ATTRIBUTE_CATEGORY ,TPC.ATTRIBUTE1 ,TPC.ATTRIBUTE2 ,TPC.ATTRIBUTE3 , TPC.ATTRIBUTE4 ,TPC.ATTRIBUTE5 ,TPC.ATTRIBUTE6 ,TPC.ATTRIBUTE7 ,TPC.ATTRIBUTE8 , TPC.ATTRIBUTE9 ,TPC.ATTRIBUTE10 ,TPC.ATTRIBUTE11 ,TPC.ATTRIBUTE12 ,TPC.ATTRIBUTE13 , TPC.ATTRIBUTE14 ,TPC.ATTRIBUTE15 ,TPC.ATTRIBUTE16 ,TPC.ATTRIBUTE17 ,TPC.ATTRIBUTE18 , TPC.ATTRIBUTE19 ,TPC.ATTRIBUTE20 ,TPC.ATTRIBUTE21 ,TPC.ATTRIBUTE22 ,TPC.ATTRIBUTE23 , TPC.ATTRIBUTE24 ,TPC.ATTRIBUTE25 ,TPC.ATTRIBUTE26 ,TPC.ATTRIBUTE27 ,TPC.ATTRIBUTE28 , TPC.ATTRIBUTE29 ,TPC.ATTRIBUTE30 ,TPC.INFORMATION_CATEGORY ,TPC.TP_COST_INFORMATION1 , TPC.TP_COST_INFORMATION2 ,TPC.TP_COST_INFORMATION3 ,TPC.TP_COST_INFORMATION4 , TPC.TP_COST_INFORMATION5 ,TPC.TP_COST_INFORMATION6 ,TPC.TP_COST_INFORMATION7 , TPC.TP_COST_INFORMATION8 ,TPC.TP_COST_INFORMATION9 ,TPC.TP_COST_INFORMATION10 , TPC.TP_COST_INFORMATION11 ,TPC.TP_COST_INFORMATION12 ,TPC.TP_COST_INFORMATION13 , TPC.TP_COST_INFORMATION14 ,TPC.TP_COST_INFORMATION15 ,TPC.TP_COST_INFORMATION16 , TPC.TP_COST_INFORMATION17 ,TPC.TP_COST_INFORMATION18 ,TPC.TP_COST_INFORMATION19 , TPC.TP_COST_INFORMATION20 ,TPC.TP_COST_INFORMATION21 ,TPC.TP_COST_INFORMATION22 , TPC.TP_COST_INFORMATION23 ,TPC.TP_COST_INFORMATION24 ,TPC.TP_COST_INFORMATION25 , TPC.TP_COST_INFORMATION26 ,TPC.TP_COST_INFORMATION27 ,TPC.TP_COST_INFORMATION28 , TPC.TP_COST_INFORMATION29 ,TPC.TP_COST_INFORMATION30 ,TPC.CREATED_BY , TPC.CREATION_DATE ,TPC.LAST_UPDATED_BY ,TPC.LAST_UPDATE_DATE ,TPC.LAST_UPDATE_LOGIN FROM OTA_TRAINING_PLAN_COSTS TPC, FND_COMMON_LOOKUPS LU1 , FND_COMMON_LOOKUPS LU2 , OTA_TP_MEASUREMENT_TYPES TMT, OTA_EVENTS EVT, OTA_DELEGATE_BOOKINGS ODB, PER_ALL_PEOPLE_F PER WHERE LU1.LOOKUP_TYPE = 'OTA_PLAN_MEASUREMENT_TYPE' AND LU2.LOOKUP_TYPE = 'UNITS' AND LU1.APPLICATION_ID = 800 AND LU2.APPLICATION_ID = 800 AND TMT.COST_LEVEL = 'DELEGATE' AND ( EXISTS (SELECT NULL FROM PER_ALL_PEOPLE_F WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID AND EVT.COURSE_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) AND EVT.COURSE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE OR NOT EXISTS (SELECT NULL FROM PER_ALL_PEOPLE_F WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID AND EFFECTIVE_END_DATE >= EVT.COURSE_START_DATE) AND PER.EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID) OR NOT EXISTS (SELECT NULL FROM PER_ALL_PEOPLE_F WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID AND EFFECTIVE_START_DATE <= EVT.COURSE_START_DATE) AND PER.EFFECTIVE_START_DATE = (SELECT MIN(EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID) ) AND TPC.TP_MEASUREMENT_TYPE_ID = TMT.TP_MEASUREMENT_TYPE_ID AND TPC.BOOKING_ID = ODB.BOOKING_ID AND LU1.LOOKUP_CODE = TMT.TP_MEASUREMENT_CODE AND LU2.LOOKUP_CODE = TMT.UNIT AND ODB.DELEGATE_PERSON_ID = PER.PERSON_ID AND EVT.EVENT_ID = ODB.EVENT_ID

View Text - HTML Formatted

SELECT TPC.ROWID ROW_ID
, 'PLAN' COST_LEVEL
, TPC.TRAINING_PLAN_COST_ID
, TPC.TP_MEASUREMENT_TYPE_ID
, TMT.TP_MEASUREMENT_CODE
, LU1.MEANING MEASUREMENT_MEANING
, TMT.UNIT
, LU2.MEANING UNIT_MEANING
, TMT.ITEM_TYPE_USAGE_ID
, TPC.TRAINING_PLAN_ID
, TO_NUMBER(NULL) BOOKING_ID
, TO_NUMBER(NULL) DELEGATE_PERSON_ID
, NULL DELEGATE_FULL_NAME
, TO_NUMBER(NULL) EVENT_ID
, NULL EVENT_TITLE
, TPC.AMOUNT
, TPC.CURRENCY_CODE
, TPC.BUSINESS_GROUP_ID
, TPC.OBJECT_VERSION_NUMBER
, TPC.ATTRIBUTE_CATEGORY
, TPC.ATTRIBUTE1
, TPC.ATTRIBUTE2
, TPC.ATTRIBUTE3
, TPC.ATTRIBUTE4
, TPC.ATTRIBUTE5
, TPC.ATTRIBUTE6
, TPC.ATTRIBUTE7
, TPC.ATTRIBUTE8
, TPC.ATTRIBUTE9
, TPC.ATTRIBUTE10
, TPC.ATTRIBUTE11
, TPC.ATTRIBUTE12
, TPC.ATTRIBUTE13
, TPC.ATTRIBUTE14
, TPC.ATTRIBUTE15
, TPC.ATTRIBUTE16
, TPC.ATTRIBUTE17
, TPC.ATTRIBUTE18
, TPC.ATTRIBUTE19
, TPC.ATTRIBUTE20
, TPC.ATTRIBUTE21
, TPC.ATTRIBUTE22
, TPC.ATTRIBUTE23
, TPC.ATTRIBUTE24
, TPC.ATTRIBUTE25
, TPC.ATTRIBUTE26
, TPC.ATTRIBUTE27
, TPC.ATTRIBUTE28
, TPC.ATTRIBUTE29
, TPC.ATTRIBUTE30
, TPC.INFORMATION_CATEGORY
, TPC.TP_COST_INFORMATION1
, TPC.TP_COST_INFORMATION2
, TPC.TP_COST_INFORMATION3
, TPC.TP_COST_INFORMATION4
, TPC.TP_COST_INFORMATION5
, TPC.TP_COST_INFORMATION6
, TPC.TP_COST_INFORMATION7
, TPC.TP_COST_INFORMATION8
, TPC.TP_COST_INFORMATION9
, TPC.TP_COST_INFORMATION10
, TPC.TP_COST_INFORMATION11
, TPC.TP_COST_INFORMATION12
, TPC.TP_COST_INFORMATION13
, TPC.TP_COST_INFORMATION14
, TPC.TP_COST_INFORMATION15
, TPC.TP_COST_INFORMATION16
, TPC.TP_COST_INFORMATION17
, TPC.TP_COST_INFORMATION18
, TPC.TP_COST_INFORMATION19
, TPC.TP_COST_INFORMATION20
, TPC.TP_COST_INFORMATION21
, TPC.TP_COST_INFORMATION22
, TPC.TP_COST_INFORMATION23
, TPC.TP_COST_INFORMATION24
, TPC.TP_COST_INFORMATION25
, TPC.TP_COST_INFORMATION26
, TPC.TP_COST_INFORMATION27
, TPC.TP_COST_INFORMATION28
, TPC.TP_COST_INFORMATION29
, TPC.TP_COST_INFORMATION30
, TPC.CREATED_BY
, TPC.CREATION_DATE
, TPC.LAST_UPDATED_BY
, TPC.LAST_UPDATE_DATE
, TPC.LAST_UPDATE_LOGIN
FROM OTA_TRAINING_PLAN_COSTS TPC
, FND_COMMON_LOOKUPS LU1
, FND_COMMON_LOOKUPS LU2
, OTA_TP_MEASUREMENT_TYPES TMT
WHERE LU1.LOOKUP_TYPE = 'OTA_PLAN_MEASUREMENT_TYPE'
AND LU2.LOOKUP_TYPE = 'UNITS'
AND LU1.APPLICATION_ID = 800
AND LU2.APPLICATION_ID = 800
AND TMT.COST_LEVEL = 'PLAN'
AND TPC.TP_MEASUREMENT_TYPE_ID = TMT.TP_MEASUREMENT_TYPE_ID
AND LU1.LOOKUP_CODE = TMT.TP_MEASUREMENT_CODE
AND LU2.LOOKUP_CODE = TMT.UNIT UNION SELECT TPC.ROWID ROW_ID
, 'EVENT' COST_LEVEL
, TPC.TRAINING_PLAN_COST_ID
, TPC.TP_MEASUREMENT_TYPE_ID
, TMT.TP_MEASUREMENT_CODE
, LU1.MEANING MEASUREMENT_MEANING
, TMT.UNIT
, LU2.MEANING UNIT_MEANING
, TMT.ITEM_TYPE_USAGE_ID
, TPC.TRAINING_PLAN_ID
, TO_NUMBER(NULL) BOOKING_ID
, TO_NUMBER(NULL) DELEGATE_PERSON_ID
, NULL DELEGATE_FULL_NAME
, TPC.EVENT_ID
, EVT.TITLE
, TPC.AMOUNT
, TPC.CURRENCY_CODE
, TPC.BUSINESS_GROUP_ID
, TPC.OBJECT_VERSION_NUMBER
, TPC.ATTRIBUTE_CATEGORY
, TPC.ATTRIBUTE1
, TPC.ATTRIBUTE2
, TPC.ATTRIBUTE3
, TPC.ATTRIBUTE4
, TPC.ATTRIBUTE5
, TPC.ATTRIBUTE6
, TPC.ATTRIBUTE7
, TPC.ATTRIBUTE8
, TPC.ATTRIBUTE9
, TPC.ATTRIBUTE10
, TPC.ATTRIBUTE11
, TPC.ATTRIBUTE12
, TPC.ATTRIBUTE13
, TPC.ATTRIBUTE14
, TPC.ATTRIBUTE15
, TPC.ATTRIBUTE16
, TPC.ATTRIBUTE17
, TPC.ATTRIBUTE18
, TPC.ATTRIBUTE19
, TPC.ATTRIBUTE20
, TPC.ATTRIBUTE21
, TPC.ATTRIBUTE22
, TPC.ATTRIBUTE23
, TPC.ATTRIBUTE24
, TPC.ATTRIBUTE25
, TPC.ATTRIBUTE26
, TPC.ATTRIBUTE27
, TPC.ATTRIBUTE28
, TPC.ATTRIBUTE29
, TPC.ATTRIBUTE30
, TPC.INFORMATION_CATEGORY
, TPC.TP_COST_INFORMATION1
, TPC.TP_COST_INFORMATION2
, TPC.TP_COST_INFORMATION3
, TPC.TP_COST_INFORMATION4
, TPC.TP_COST_INFORMATION5
, TPC.TP_COST_INFORMATION6
, TPC.TP_COST_INFORMATION7
, TPC.TP_COST_INFORMATION8
, TPC.TP_COST_INFORMATION9
, TPC.TP_COST_INFORMATION10
, TPC.TP_COST_INFORMATION11
, TPC.TP_COST_INFORMATION12
, TPC.TP_COST_INFORMATION13
, TPC.TP_COST_INFORMATION14
, TPC.TP_COST_INFORMATION15
, TPC.TP_COST_INFORMATION16
, TPC.TP_COST_INFORMATION17
, TPC.TP_COST_INFORMATION18
, TPC.TP_COST_INFORMATION19
, TPC.TP_COST_INFORMATION20
, TPC.TP_COST_INFORMATION21
, TPC.TP_COST_INFORMATION22
, TPC.TP_COST_INFORMATION23
, TPC.TP_COST_INFORMATION24
, TPC.TP_COST_INFORMATION25
, TPC.TP_COST_INFORMATION26
, TPC.TP_COST_INFORMATION27
, TPC.TP_COST_INFORMATION28
, TPC.TP_COST_INFORMATION29
, TPC.TP_COST_INFORMATION30
, TPC.CREATED_BY
, TPC.CREATION_DATE
, TPC.LAST_UPDATED_BY
, TPC.LAST_UPDATE_DATE
, TPC.LAST_UPDATE_LOGIN
FROM OTA_TRAINING_PLAN_COSTS TPC
, FND_COMMON_LOOKUPS LU1
, FND_COMMON_LOOKUPS LU2
, OTA_TP_MEASUREMENT_TYPES TMT
, OTA_EVENTS EVT
WHERE LU1.LOOKUP_TYPE = 'OTA_PLAN_MEASUREMENT_TYPE'
AND LU2.LOOKUP_TYPE = 'UNITS'
AND LU1.APPLICATION_ID = 800
AND LU2.APPLICATION_ID = 800
AND TMT.COST_LEVEL = 'EVENT'
AND TPC.TP_MEASUREMENT_TYPE_ID = TMT.TP_MEASUREMENT_TYPE_ID
AND TPC.EVENT_ID = EVT.EVENT_ID
AND LU1.LOOKUP_CODE = TMT.TP_MEASUREMENT_CODE
AND LU2.LOOKUP_CODE = TMT.UNIT UNION SELECT TPC.ROWID ROW_ID
, 'DELEGATE' COST_LEVEL
, TPC.TRAINING_PLAN_COST_ID
, TPC.TP_MEASUREMENT_TYPE_ID
, TMT.TP_MEASUREMENT_CODE
, LU1.MEANING
, TMT.UNIT
, LU2.MEANING
, TMT.ITEM_TYPE_USAGE_ID
, TPC.TRAINING_PLAN_ID
, TPC.BOOKING_ID
, ODB.DELEGATE_PERSON_ID
, PER.FULL_NAME
, ODB.EVENT_ID
, EVT.TITLE
, TPC.AMOUNT
, TPC.CURRENCY_CODE
, TPC.BUSINESS_GROUP_ID
, TPC.OBJECT_VERSION_NUMBER
, TPC.ATTRIBUTE_CATEGORY
, TPC.ATTRIBUTE1
, TPC.ATTRIBUTE2
, TPC.ATTRIBUTE3
, TPC.ATTRIBUTE4
, TPC.ATTRIBUTE5
, TPC.ATTRIBUTE6
, TPC.ATTRIBUTE7
, TPC.ATTRIBUTE8
, TPC.ATTRIBUTE9
, TPC.ATTRIBUTE10
, TPC.ATTRIBUTE11
, TPC.ATTRIBUTE12
, TPC.ATTRIBUTE13
, TPC.ATTRIBUTE14
, TPC.ATTRIBUTE15
, TPC.ATTRIBUTE16
, TPC.ATTRIBUTE17
, TPC.ATTRIBUTE18
, TPC.ATTRIBUTE19
, TPC.ATTRIBUTE20
, TPC.ATTRIBUTE21
, TPC.ATTRIBUTE22
, TPC.ATTRIBUTE23
, TPC.ATTRIBUTE24
, TPC.ATTRIBUTE25
, TPC.ATTRIBUTE26
, TPC.ATTRIBUTE27
, TPC.ATTRIBUTE28
, TPC.ATTRIBUTE29
, TPC.ATTRIBUTE30
, TPC.INFORMATION_CATEGORY
, TPC.TP_COST_INFORMATION1
, TPC.TP_COST_INFORMATION2
, TPC.TP_COST_INFORMATION3
, TPC.TP_COST_INFORMATION4
, TPC.TP_COST_INFORMATION5
, TPC.TP_COST_INFORMATION6
, TPC.TP_COST_INFORMATION7
, TPC.TP_COST_INFORMATION8
, TPC.TP_COST_INFORMATION9
, TPC.TP_COST_INFORMATION10
, TPC.TP_COST_INFORMATION11
, TPC.TP_COST_INFORMATION12
, TPC.TP_COST_INFORMATION13
, TPC.TP_COST_INFORMATION14
, TPC.TP_COST_INFORMATION15
, TPC.TP_COST_INFORMATION16
, TPC.TP_COST_INFORMATION17
, TPC.TP_COST_INFORMATION18
, TPC.TP_COST_INFORMATION19
, TPC.TP_COST_INFORMATION20
, TPC.TP_COST_INFORMATION21
, TPC.TP_COST_INFORMATION22
, TPC.TP_COST_INFORMATION23
, TPC.TP_COST_INFORMATION24
, TPC.TP_COST_INFORMATION25
, TPC.TP_COST_INFORMATION26
, TPC.TP_COST_INFORMATION27
, TPC.TP_COST_INFORMATION28
, TPC.TP_COST_INFORMATION29
, TPC.TP_COST_INFORMATION30
, TPC.CREATED_BY
, TPC.CREATION_DATE
, TPC.LAST_UPDATED_BY
, TPC.LAST_UPDATE_DATE
, TPC.LAST_UPDATE_LOGIN
FROM OTA_TRAINING_PLAN_COSTS TPC
, FND_COMMON_LOOKUPS LU1
, FND_COMMON_LOOKUPS LU2
, OTA_TP_MEASUREMENT_TYPES TMT
, OTA_EVENTS EVT
, OTA_DELEGATE_BOOKINGS ODB
, PER_ALL_PEOPLE_F PER
WHERE LU1.LOOKUP_TYPE = 'OTA_PLAN_MEASUREMENT_TYPE'
AND LU2.LOOKUP_TYPE = 'UNITS'
AND LU1.APPLICATION_ID = 800
AND LU2.APPLICATION_ID = 800
AND TMT.COST_LEVEL = 'DELEGATE'
AND ( EXISTS (SELECT NULL
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID
AND EVT.COURSE_START_DATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE)
AND EVT.COURSE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE OR NOT EXISTS (SELECT NULL
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID
AND EFFECTIVE_END_DATE >= EVT.COURSE_START_DATE)
AND PER.EFFECTIVE_START_DATE = (SELECT MAX(EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID) OR NOT EXISTS (SELECT NULL
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID
AND EFFECTIVE_START_DATE <= EVT.COURSE_START_DATE)
AND PER.EFFECTIVE_START_DATE = (SELECT MIN(EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = ODB.DELEGATE_PERSON_ID) )
AND TPC.TP_MEASUREMENT_TYPE_ID = TMT.TP_MEASUREMENT_TYPE_ID
AND TPC.BOOKING_ID = ODB.BOOKING_ID
AND LU1.LOOKUP_CODE = TMT.TP_MEASUREMENT_CODE
AND LU2.LOOKUP_CODE = TMT.UNIT
AND ODB.DELEGATE_PERSON_ID = PER.PERSON_ID
AND EVT.EVENT_ID = ODB.EVENT_ID