View: OTFV_TRAINING_EVENTS

Product:	OTA - Learning Management
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.OTFV_TRAINING_EVENTS

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, EVT.TITLE EVENT_TITLE
, EVT.COMMENTS EVENT_COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('TRAINING_EVENT_TYPE'
, EVT.EVENT_TYPE) EVENT_TYPE
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)) EVENT_STATUS
, TO_CHAR(EVT.COURSE_START_DATE
, 'IW') WEEK_NUMBER
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, EVT.COURSE_START_TIME COURSE_START_TIME
, EVT.COURSE_END_TIME COURSE_END_TIME
, EVT.TIMEZONE TIMEZONE
, EVT.DURATION DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) DURATION_UNITS
, EVT.ENROLMENT_START_DATE ENROLLMENT_START_DATE
, EVT.ENROLMENT_END_DATE ENROLLMENT_END_DATE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.EVENT_TYPE
, 'AD HOC'
, NULL
, SUBSTR(OTA_EVT_SHD.RESOURCE_BOOKING_FLAG(EVT.EVENT_ID)
, 1
, 1))) RESOURCE_BOOKED
, DECODE(EVT.PUBLIC_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EVT.PUBLIC_EVENT_FLAG)) PUBLIC_EVENT
, EVT.MINIMUM_ATTENDEES MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES MAXIMUM_ATTENDEES
, EVT.MAXIMUM_INTERNAL_ATTENDEES MAXIMUM_INTERNAL_ATTENDEES
, EVT.STANDARD_PRICE STANDARD_PRICE
, DECODE(EVT.BOOK_INDEPENDENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EVT.BOOK_INDEPENDENT_FLAG)) BOOK_INDEPENDENT
, EVT.ACTUAL_COST ACTUAL_COST
, EVT.BUDGET_COST BUDGET_COST
, OTA_GENERAL.FND_CURRENCY_NAME(EVT.BUDGET_CURRENCY_CODE) BUDGET_COST_CURRENCY
, ORGT1.NAME EVENT_CENTER
, DECODE(EVT.DEVELOPMENT_EVENT_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('DEV_EVENT_TYPE'
, EVT.DEVELOPMENT_EVENT_TYPE)) DEVELOPMENT_EVENT_TYPE
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS)) USER_STATUS
, DECODE(EVT.SECURE_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.EVENT_TYPE
, 'DEVELOPMENT'
, NULL
, 'AD HOC'
, NULL
, EVT.SECURE_EVENT_FLAG))) SECURED_EVENT
, OTA_GENERAL.HR_ORG_NAME(EVT.ORGANIZATION_ID) ORGANIZATION_NAME
, OTA_GENERAL.FND_LANG_DESC(EVT.LANGUAGE_ID) LANGUAGE
, DECODE(EVT.PRICE_BASIS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_PRICE_BASIS'
, EVT.PRICE_BASIS)) PRICE_BASIS
, DECODE(EVT.CATEGORY_CODE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_CATEGORY'
, EVT.CATEGORY_CODE)) PROGRAMME_CODE
, TAV.VERSION_NAME ACTIVITY_NAME
, TAD.NAME ACTIVITY_TYPE
, OTA_EVT_SHD.INVOICED_AMOUNT_TOTAL(EVT.EVENT_ID) INVOICE_AMOUNT
, VEN.VENDOR_NAME SUPPLIER_NAME
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 PROJECT_NUMBER
, PER.FULL_NAME OWNER_NAME
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_CATEGORY'
, ACI.ACTIVITY_CATEGORY) CATEGORY_NAME
, '_DF:OTA:OTA_EVENTS:EVT'
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, EVT.PARENT_EVENT_ID PARENT_EVENT_ID
, EVT.ORGANIZATION_ID ORGANIZATION_ID
, EVT.LANGUAGE_ID LANGUAGE_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, VEN.VENDOR_ID VENDOR_ID
, EVT.PROJECT_ID PROJECT_ID
, EVT.LINE_ID LINE_ID
, EVT.ORG_ID ORG_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, EVT.OWNER_ID OWNER_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM PO_VENDORS VEN
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_EVENTS EVT
, PA_PROJECTS PPA
, PER_ALL_PEOPLE_F PER
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
, OTA_ACT_CAT_INCLUSIONS ACI
, OTA_CATEGORY_USAGES TCU
WHERE EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, ORGT1.LANGUAGE) = DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND EVT.OWNER_ID = PER.PERSON_ID (+)
AND SYSDATE BETWEEN PER.EFFECTIVE_START_DATE (+)
AND PER.EFFECTIVE_END_DATE (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAV.ACTIVITY_VERSION_ID = ACI.ACTIVITY_VERSION_ID (+)
AND ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID (+)
AND (TCU.TYPE = 'DM' OR TCU.TYPE IS NULL)
AND EVT.VENDOR_ID = VEN.VENDOR_ID (+)
AND EVT.PROJECT_ID = PPA.PROJECT_ID (+)
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID)
AND EVT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG') WITH READ ONLY

Columns