View: OTA_TRAINING_COST_LEVELS_V

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

View Text

SELECT OTPC.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, ' ' ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OTPV.PERIOD_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'PLAN' ROLLUP_LEVEL
, ' ' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_TRAINING_PLANS_V OTPV
WHERE OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPV.TRAINING_PLAN_ID = OTPC.TRAINING_PLAN_ID UNION SELECT OTPC.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, ' ' ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OTPV.PERIOD_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'ACTIVITY_VERSION' ROLLUP_LEVEL
, 'PLAN' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_TRAINING_PLANS_V OTPV
WHERE OTMT.COST_LEVEL = 'PLAN'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPV.TRAINING_PLAN_ID = OTPC.TRAINING_PLAN_ID UNION SELECT OTPC.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, OAV.VERSION_NAME ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OE.COURSE_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'ACTIVITY_VERSION' ROLLUP_LEVEL
, 'EVENT' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_ACTIVITY_VERSIONS OAV
, OTA_EVENTS OE
WHERE OTMT.COST_LEVEL = 'EVENT'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPC.EVENT_ID IS NOT NULL
AND OTPC.BOOKING_ID IS NULL
AND OE.EVENT_ID = OTPC.EVENT_ID
AND OAV.ACTIVITY_VERSION_ID = OE.ACTIVITY_VERSION_ID UNION SELECT OTPC.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, OAV.VERSION_NAME ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OE.COURSE_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'ACTIVITY_VERSION' ROLLUP_LEVEL
, 'DELEGATE' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_DELEGATE_BOOKINGS ODB
, OTA_ACTIVITY_VERSIONS OAV
, OTA_EVENTS OE
WHERE OTMT.COST_LEVEL = 'DELEGATE'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPC.EVENT_ID IS NULL
AND OTPC.BOOKING_ID IS NOT NULL
AND ODB.BOOKING_ID = OTPC.BOOKING_ID
AND OE.EVENT_ID = ODB.EVENT_ID
AND OAV.ACTIVITY_VERSION_ID = OE.ACTIVITY_VERSION_ID UNION SELECT OTPC.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, ' ' ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OTPV.PERIOD_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'ACTIVITY_DEFINITION' ROLLUP_LEVEL
, 'PLAN'
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_TRAINING_PLANS_V OTPV
WHERE OTMT.COST_LEVEL = 'PLAN'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPV.TRAINING_PLAN_ID = OTPC.TRAINING_PLAN_ID UNION SELECT OTPC.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, OAD.NAME ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OE.COURSE_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'ACTIVITY_DEFINITION' ROLLUP_LEVEL
, 'EVENT' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_ACTIVITY_VERSIONS OAV
, OTA_ACTIVITY_DEFINITIONS OAD
, OTA_EVENTS OE
WHERE OTMT.COST_LEVEL = 'EVENT'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPC.EVENT_ID IS NOT NULL
AND OTPC.BOOKING_ID IS NULL
AND OE.EVENT_ID = OTPC.EVENT_ID
AND OAV.ACTIVITY_VERSION_ID = OE.ACTIVITY_VERSION_ID
AND OAD.ACTIVITY_ID = OAV.ACTIVITY_ID UNION SELECT OTPC.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, OAD.NAME ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OE.COURSE_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'ACTIVITY_DEFINITION' ROLLUP_LEVEL
, 'DELEGATE' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_DELEGATE_BOOKINGS ODB
, OTA_ACTIVITY_VERSIONS OAV
, OTA_ACTIVITY_DEFINITIONS OAD
, OTA_EVENTS OE
WHERE OTMT.COST_LEVEL = 'DELEGATE'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPC.EVENT_ID IS NULL
AND OTPC.BOOKING_ID IS NOT NULL
AND ODB.BOOKING_ID = OTPC.BOOKING_ID
AND OE.EVENT_ID = ODB.EVENT_ID
AND OAV.ACTIVITY_VERSION_ID = OE.ACTIVITY_VERSION_ID
AND OAD.ACTIVITY_ID = OAV.ACTIVITY_ID UNION SELECT OTPC.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, ' ' ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OTPV.PERIOD_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'EVENT' ROLLUP_LEVEL
, 'PLAN' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_TRAINING_PLANS_V OTPV
WHERE OTMT.COST_LEVEL = 'PLAN'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPV.TRAINING_PLAN_ID = OTPC.TRAINING_PLAN_ID UNION SELECT OTMT.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, OE.TITLE ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OE.COURSE_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'EVENT' ROLLUP_LEVEL
, 'EVENT' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_EVENTS OE
WHERE OTMT.COST_LEVEL = 'EVENT'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPC.EVENT_ID IS NOT NULL
AND OTPC.BOOKING_ID IS NULL
AND OE.EVENT_ID = OTPC.EVENT_ID UNION SELECT OTMT.TP_MEASUREMENT_TYPE_ID MEASUREMENT_TYPE_ID
, OE.TITLE ITEM_NAME
, OTPC.EVENT_ID EVENT_ID
, OTPC.BOOKING_ID BOOKING_ID
, OTPC.AMOUNT AMOUNT
, OTPC.CURRENCY_CODE CURRENCY_CODE
, OTMT.UNIT UNIT
, OE.COURSE_START_DATE PERIOD_START_DATE
, OTPC.TRAINING_PLAN_ID TRAINING_PLAN_ID
, 'EVENT' ROLLUP_LEVEL
, 'DELEGATE' COST_LEVEL
FROM OTA_TRAINING_PLAN_COSTS OTPC
, OTA_TP_MEASUREMENT_TYPES OTMT
, OTA_DELEGATE_BOOKINGS ODB
, OTA_EVENTS OE
WHERE OTMT.COST_LEVEL = 'DELEGATE'
AND OTPC.TP_MEASUREMENT_TYPE_ID = OTMT.TP_MEASUREMENT_TYPE_ID
AND OTPC.EVENT_ID IS NULL
AND OTPC.BOOKING_ID IS NOT NULL
AND ODB.BOOKING_ID = OTPC.BOOKING_ID
AND OE.EVENT_ID = ODB.EVENT_ID

Columns