View: OTFV_ENROLLMENT_STATUS_HISTORY

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, BSH.START_DATE START_DATE
, BST.NAME ENROLLMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BST.ACTIVE_FLAG) STATUS_ACTIVE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BST.DEFAULT_FLAG) STATUS_DEFAULT
, BSH.COMMENTS STATUS_HISTORY_COMMENTS
, PER.FULL_NAME STUDENT_FULL_NAME
, ORGT.NAME ORGANIZATION_NAME
, TO_CHAR(NULL) CUSTOMER_NAME
, EVT.TITLE EVENT_TITLE
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, EVT.DURATION COURSE_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) COURSE_DURATION_UNITS
, TDB.DATE_BOOKING_PLACED DATE_BOOKING_PLACED
, TAV.VERSION_NAME ACTIVITY_NAME
, TAD.NAME ACTIVITY_TYPE
, TDB.TOTAL_TRAINING_TIME TOTAL_TRAINING_TIME
, DECODE(TDB.CONTENT_PLAYER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, TDB.CONTENT_PLAYER_STATUS)) PLAYER_STATUS
, TDB.SCORE TEST_SCORE
, TDB.COMPLETED_CONTENT NUMBER_OF_MODULES_COMPLETED
, TDB.TOTAL_CONTENT TOTAL_MODULES
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TDB.DELEGATE_PERSON_ID STUDENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TO_NUMBER(NULL) CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM OTA_BOOKING_STATUS_HISTORIES BSH
, OTA_BOOKING_STATUS_TYPES BST
, OTA_DELEGATE_BOOKINGS TDB
, OTA_EVENTS EVT
, PER_ALL_PEOPLE_F PER
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
WHERE TDB.CUSTOMER_ID IS NULL
AND BSH.BOOKING_ID = TDB.BOOKING_ID
AND BSH.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID
AND TDB.EVENT_ID = EVT.EVENT_ID
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+) AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+) AND TDB.DELEGATE_PERSON_ID = PER.PERSON_ID
AND NVL (EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED) BETWEEN NVL(PER.EFFECTIVE_START_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND NVL(PER.EFFECTIVE_END_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(TDB.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(TDB.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, BSH.START_DATE START_DATE
, BST.NAME ENROLLMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BST.ACTIVE_FLAG) STATUS_ACTIVE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BST.DEFAULT_FLAG) STATUS_DEFAULT
, BSH.COMMENTS STATUS_HISTORY_COMMENTS
, CON.FIRST_NAME||' '||CON.LAST_NAME STUDENT_FULL_NAME
, TO_CHAR(NULL) ORGANIZATION_NAME
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, EVT.TITLE EVENT_TITLE
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, EVT.DURATION COURSE_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) COURSE_DURATION_UNITS
, TDB.DATE_BOOKING_PLACED DATE_BOOKING_PLACED
, TAV.VERSION_NAME ACTIVITY_NAME
, TAD.NAME ACTIVITY_TYPE
, TDB.TOTAL_TRAINING_TIME TOTAL_TRAINING_TIME
, DECODE(TDB.CONTENT_PLAYER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, TDB.CONTENT_PLAYER_STATUS)) PLAYER_STATUS
, TDB.SCORE TEST_SCORE
, TDB.COMPLETED_CONTENT NUMBER_OF_MODULES_COMPLETED
, TDB.TOTAL_CONTENT TOTAL_MODULES
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TDB.DELEGATE_PERSON_ID STUDENT_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, CUS.CUSTOMER_ID CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM OTA_DELEGATE_BOOKINGS TDB
, RA_CUSTOMERS CUS
, RA_CONTACTS CON
, OTA_BOOKING_STATUS_TYPES BST
, OTA_BOOKING_STATUS_HISTORIES BSH
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_EVENTS EVT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
WHERE TDB.CUSTOMER_ID IS NOT NULL
AND BSH.BOOKING_ID = TDB.BOOKING_ID
AND BSH.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID
AND CON.CONTACT_ID = TDB.DELEGATE_PERSON_ID
AND CUS.CUSTOMER_ID = TDB.CUSTOMER_ID
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) WITH READ ONLY

Columns