View: OTA_TRAINING_PLAN_COSTS_V

Product:	OTA - Learning Management
Description:
Implementation/DBA Data:	APPS.OTA_TRAINING_PLAN_COSTS_V

View Text

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

Columns