View: OTFV_INT_TRAINING_EVENT_ENROLS

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, DEL.FULL_NAME PERSON_NAME
, EVT.TITLE EVENT_NAME
, EVT.COMMENTS EVENT_COMMENTS
, EVT.EVENT_TYPE EVENT_TYPE
, EVT.TIMEZONE TIME_ZONE
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_START_TIME COURSE_START_TIME
, EVT.COURSE_END_DATE COURSE_END_DATE
, EVT.COURSE_START_TIME COURSE_START_TIME
, TDB.DATE_BOOKING_PLACED DATE_BOOKING_PLACED
, ORGT1.NAME TRAINING_CENTER
, TDB.ATTENDANCE_RESULT ATTENDANCE_RESULT
, DECODE(TDB.FAILURE_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP ('DELEGATE_FAILURE_REASON'
, TDB.FAILURE_REASON)) FAILURE_REASON
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TDB.SUCCESSFUL_ATTENDANCE_FLAG)) SUCCESSFUL_ATTENDANCE_FLAG
, TDB.TOTAL_TRAINING_TIME TOTAL_TIME_TAKEN
, 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
, DECODE(DEL.CURRENT_EMPLOYEE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DEL.CURRENT_EMPLOYEE_FLAG)) CURRENT_EMPLOYEE_FLAG
, DECODE(TDB.INTERNAL_BOOKING_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TDB.INTERNAL_BOOKING_FLAG)) INTERNAL_BOOKING_FLAG
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ORG1T.NAME ASSIGNMENT_ORGANIZATION
, LOCT.LOCATION_CODE ASSIGNMENT_LOCATION
, JEI.JEI_INFORMATION1 ASSIGNMENT_JOB_CATEGORY
, JOB.NAME ASSIGNMENT_JOB
, BST.NAME BOOKING_STATUS
, TAD.NAME ACTIVITY_TYPE
, TAV.VERSION_NAME ACTIVITY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_CATEGORY'
, ACI.ACTIVITY_CATEGORY) CATEGORY_NAME
, LNG.DESCRIPTION LANGUAGE
, ORGT.NAME ORGANIZATION_NAME
, TDB.NUMBER_OF_PLACES NUMBER_OF_PLACES
, (TDB.NUMBER_OF_PLACES * DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, 1
, 'N'
, 0
, 0)) NUMBER_OF_SUCCESSFUL_PLACES
, TDB.COMMENTS ENROLMENT_COMMENTS
, TDB.SPECIAL_BOOKING_INSTRUCTIONS SPECIAL_REQUIREMENTS
, DECODE(TDB.SOURCE_OF_BOOKING
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_SOURCE'
, TDB.SOURCE_OF_BOOKING)) SOURCE_OF_BOOKING_MEANING
, DECODE(TDB.CORESPONDENT
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('CORRESPONDENT'
, TDB.CORESPONDENT)) CORRESPONDENCE_MEANING
, TDB.DELEGATE_CONTACT_PHONE CORRESPONDENCE_PHONE
, TDB.DELEGATE_CONTACT_FAX CORRESPONDENCE_FAX
, TDB.DELEGATE_CONTACT_EMAIL CORRESPONDENCE_EMAIL
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.ADDRESS_LINE1|| DECODE(ADDR.ADDRESS_LINE1
, NULL
, ''
, '
, ') || ADDR.ADDRESS_LINE2|| DECODE(ADDR.ADDRESS_LINE2
, NULL
, ''
, '
, ')|| ADDR.ADDRESS_LINE3|| DECODE(ADDR.ADDRESS_LINE3
, NULL
, ''
, '
, ')|| ADDR.TOWN_OR_CITY || DECODE(ADDR.TOWN_OR_CITY
, NULL
, ''
, '
, ')|| ADDR.REGION_1|| DECODE(ADDR.REGION_1
, NULL
, ''
, '
, ') || ADDR.REGION_2|| DECODE(ADDR.REGION_2
, NULL
, ''
, '
, ') || ADDR.REGION_3|| DECODE(ADDR.REGION_3
, NULL
, ''
, '
, ')|| ADDR.POSTAL_CODE|| DECODE(ADDR.POSTAL_CODE
, NULL
, ''
, '
, ')|| ADDR.COUNTRY||DECODE(ADDR.COUNTRY
, NULL
, ''
, '
, ')
, 'I'
, DECODE(TDB.CORESPONDENT
, 'S'
, ASG.INTERNAL_ADDRESS_LINE || DECODE(ASG.INTERNAL_ADDRESS_LINE
, NULL
, ''
, '
, ') || HR_GENERAL.HR_LOOKUP_LOCATIONS(ASG.LOCATION_ID)
, 'C'
, ASG2.INTERNAL_ADDRESS_LINE|| DECODE(ASG2.INTERNAL_ADDRESS_LINE
, NULL
, ''
, '
, ')|| HR_GENERAL.HR_LOOKUP_LOCATIONS(ASG2.LOCATION_ID)
, NULL)) CORRESPONDENCE_ADDRESS
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TDB.DELEGATE_ASSIGNMENT_ID ASSIGNMENT_ID
, TDB.BOOKING_ID ENROLLMENT_ID
, TDB.BOOKING_STATUS_TYPE_ID BOOKING_STATUS_TYPE_ID
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORG1T
, FND_LANGUAGES_VL LNG
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_VERSIONS TAV
, PER_ALL_ASSIGNMENTS_F ASG
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_BOOKING_STATUS_TYPES BST
, PER_ALL_PEOPLE_F DEL
, OTA_EVENTS EVT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, OTA_DELEGATE_BOOKINGS TDB
, HR_LOCATIONS_ALL_TL LOCT
, PER_JOBS JOB
, PER_JOB_EXTRA_INFO JEI
, PER_ADDRESSES ADDR
, PER_ALL_ASSIGNMENTS_F ASG2
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
, OTA_ACT_CAT_INCLUSIONS ACI
, OTA_CATEGORY_USAGES TCU
WHERE TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_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 TDB.EVENT_ID = EVT.EVENT_ID
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
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 TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.DELEGATE_PERSON_ID = DEL.PERSON_ID (+)
AND ( ( TDB.INTERNAL_BOOKING_FLAG = 'Y') OR ((TDB.INTERNAL_BOOKING_FLAG = 'N')
AND (TDB.DELEGATE_PERSON_ID IS NOT NULL)
AND (DEL.PERSON_ID IS NOT NULL) ) )
AND NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED) BETWEEN NVL(DEL.EFFECTIVE_START_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND NVL(DEL.EFFECTIVE_END_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND TDB.DELEGATE_ASSIGNMENT_ID = ASG.ASSIGNMENT_ID (+)
AND (ASG.ROWID (+) = OTA_TDB_BUS.ASSIGNMENT_OK('STUDENT'
, TDB.DELEGATE_ASSIGNMENT_ID
, TDB.EVENT_ID
, TDB.DATE_BOOKING_PLACED))
AND ASG.ORGANIZATION_ID = ORG1T.ORGANIZATION_ID (+)
AND DECODE(ORG1T.ORGANIZATION_ID
, NULL
, 'X'
, ORG1T.LANGUAGE) = DECODE(ORG1T.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND ASG.LOCATION_ID = LOCT.LOCATION_ID (+)
AND DECODE(LOCT.LOCATION_ID
, NULL
, 'X'
, LOCT.LANGUAGE) = DECODE(LOCT.LOCATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND ASG.JOB_ID = JEI.JOB_ID (+)
AND ASG.JOB_ID = JOB.JOB_ID (+)
AND TDB.LANGUAGE_ID = LNG.LANGUAGE_ID (+)
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID (+)
AND DECODE(BGRT.ORGANIZATION_ID
, NULL
, 'X'
, BGRT.LANGUAGE) = DECODE(BGRT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID)
AND TDB.PERSON_ADDRESS_ID = ADDR.ADDRESS_ID (+)
AND TDB.SPONSOR_ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID (+)
AND TDB.DATE_BOOKING_PLACED BETWEEN ASG2.EFFECTIVE_START_DATE (+)
AND ASG2.EFFECTIVE_END_DATE (+) WITH READ ONLY

Columns