View: OTFV_CUST_RESTRICTED_EVENTS

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, EVT.TITLE EVENT_TITLE
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE EVENT_START_DATE
, EVT.COURSE_END_DATE EVENT_END_DATE
, EVT.COURSE_START_TIME EVENT_START_TIME
, EVT.COURSE_END_TIME EVENT_END_TIME
, EVT.TIMEZONE TIME_ZONE
, EVT.DURATION EVENT_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) EVENT_DURATION_UNITS
, PER.FULL_NAME EVENT_CREATOR
, PER.FULL_NAME EVENT_OWNER
, DECODE(EVT.SECURE_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EVT.SECURE_EVENT_FLAG)) SECURE_EVENT
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, SUBSTR (OTA_EVT_SHD.RESOURCE_BOOKING_FLAG(EVT.EVENT_ID)
, 1
, 1)) RESOURCE_BOOKINGS
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, TSR.RESOURCE_TYPE) RESOURCE_TYPE
, TSR.NAME RESOURCE_NAME
, ORGT.NAME TRAINING_CENTER
, VEN.VENDOR_NAME SUPPLIER_NAME
, EVT.ENROLMENT_START_DATE ENROLLMENT_START_DATE
, EVT.ENROLMENT_END_DATE ENROLLMENT_END_DATE
, LNG.DESCRIPTION LANGUAGE
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)) EVENT_STATUS
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS)) EVENT_USER_STATUS
, EVT.MINIMUM_ATTENDEES MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES MAXIMUM_ATTENDEES
, EVT.STANDARD_PRICE STANDARD_PRICE
, CUR.NAME CURRENCY
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, TEA.COMMENTS EVENT_ASSOCIATION_COMMENTS
, TAD.NAME ACTIVITY_TYPE
, TAV.VERSION_NAME ACTIVITY_NAME
, TFL.FINANCE_HEADER_ID ORDER_NUMBER
, TFL.FINANCE_LINE_ID LINE_NUMBER
, '_DF:OTA:OTA_EVENT_ASSOCIATIONS:TEA'
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, TEA.CUSTOMER_ID CUSTOMER_ID
, TEA.EVENT_ASSOCIATION_ID EVENT_ASSOCIATION_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, EVT.OFFERING_ID OFFERING_ID
, EVT.OWNER_ID OWNER_ID
, TRB.RESOURCE_BOOKING_ID RESOURCE_BOOKING_ID
, TSR.SUPPLIED_RESOURCE_ID SUPPLIED_RESOURCE_ID
, TFL.FINANCE_HEADER_ID FINANCE_HEADER_ID
, TFL.FINANCE_LINE_ID FINANCE_LINE_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, RA_CUSTOMERS CUS
, OTA_EVENT_ASSOCIATIONS TEA
, PO_VENDORS VEN
, FND_CURRENCIES_VL CUR
, FND_LANGUAGES_VL LNG
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_ALL_PEOPLE_F PER
, OTA_RESOURCE_BOOKINGS TRB
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_FINANCE_LINES TFL
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
WHERE EVT.PUBLIC_EVENT_FLAG = 'N'
AND EVT.TRAINING_CENTER_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(ORGT.LANGUAGE
, NULL
, 'X'
, ORGT.LANGUAGE)= DECODE(ORGT.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND EVT.EVENT_ID = TEA.EVENT_ID
AND EVT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TEA.CUSTOMER_ID IS NOT NULL
AND TEA.CUSTOMER_ID = CUS.CUSTOMER_ID
AND EVT.VENDOR_ID = VEN.VENDOR_ID (+)
AND EVT.CURRENCY_CODE = CUR.CURRENCY_CODE (+)
AND EVT.CREATED_BY = PER.PERSON_ID (+)
AND EVT.OWNER_ID = PER.PERSON_ID (+)
AND EVT.EVENT_ID = TRB.EVENT_ID (+)
AND TRB.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID (+)
AND TRB.RESOURCE_BOOKING_ID = TFL.RESOURCE_BOOKING_ID (+)
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND EVT.LANGUAGE_ID = LNG.LANGUAGE_ID (+)
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID) WITH READ ONLY

Columns