View: OTFV_COMBINED_TRAINING_HISTORY

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, TNH.TRNG_TITLE TRAINING_TITLE
, TNH.COMPLETION_DATE TRAINING_COMPLETION_DATE
, DECODE(TNH.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_TRAINING_TYPES'
, TNH.TYPE)) TRAINING_TYPE
, TNH.CENTRE TRAINING_CENTER
, TNH.PROVIDER TRAINING_PROVIDER
, TNH.AWARD TRAINING_AWARD
, TNH.RATING TRAINING_RATING
, TNH.DURATION TRAINING_DURATION
, DECODE(TNH.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, TNH.DURATION_UNITS)) TRAINING_DURATION_UNITS
, DECODE(TNH.STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_TRAINING_STATUSES'
, TNH.STATUS)) TRAINING_STATUS
, TO_CHAR(NULL) TOTAL_TRAINING_TIME
, TO_CHAR(NULL) PLAYER_STATUS
, TO_NUMBER(NULL) TEST_SCORE
, TO_NUMBER(NULL) NUMBER_OF_MODULES_COMPLETED
, TO_NUMBER(NULL) TOTAL_MODULES
, ORGT.NAME ORGANIZATION_NAME
, NULL CUSTOMER_NAME
, PER.FULL_NAME PERSON_NAME
, CON.FULL_NAME CONTACT_NAME
, TAV.VERSION_NAME OTA_EQUI_ACTIVITY_NAME
, TAD.NAME OTA_EQUI_ACTIVITY_TYPE
, 'NON_OTA' DATA_SOURCE
, '_DF:OTA:OTA_NOTRNG_HISTORIES:TNH'
, '_DF:_DUMMY:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TNH.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TNH.CONTACT_ID CONTACT_ID
, TNH.PERSON_ID PERSON_ID
, TNH.CUSTOMER_ID CUSTOMER_ID
, TNH.ORGANIZATION_ID ORGANIZATION_ID
, TNH.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL) TRAINING_CENTER_ID
, TO_NUMBER(NULL) ROC_ID
FROM OTA_NOTRNG_HISTORIES TNH
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F CON
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
WHERE TNH.ORGANIZATION_ID IS NOT NULL
AND TNH.ORGANIZATION_ID = ORGT.ORGANIZATION_ID(+)
AND ORGT.LANGUAGE = USERENV('LANG')
AND TNH.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TNH.PERSON_ID = PER.PERSON_ID
AND TNH.COMPLETION_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND TNH.CONTACT_ID = CON.PERSON_ID(+)
AND TNH.COMPLETION_DATE BETWEEN CON.EFFECTIVE_START_DATE(+)
AND CON.EFFECTIVE_END_DATE(+)
AND TNH.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TNH.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TNH.BUSINESS_GROUP_ID) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, TNH.TRNG_TITLE TRAINING_TITLE
, TNH.COMPLETION_DATE TRAINING_COMPLETION_DATE
, DECODE(TNH.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_TRAINING_TYPES'
, TNH.TYPE)) TRAINING_TYPE
, TNH.CENTRE TRAINING_CENTER
, TNH.PROVIDER TRAINING_PROVIDER
, TNH.AWARD TRAINING_AWARD
, TNH.RATING TRAINING_RATING
, TNH.DURATION TRAINING_DURATION
, DECODE(TNH.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, TNH.DURATION_UNITS)) TRAINING_DURATION_UNITS
, DECODE(TNH.STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_TRAINING_STATUSES'
, TNH.STATUS)) TRAINING_STATUS
, TO_CHAR(NULL) TOTAL_TRAINING_TIME
, TO_CHAR(NULL) PLAYER_STATUS
, TO_NUMBER(NULL) TEST_SCORE
, TO_NUMBER(NULL) NUMBER_OF_MODULES_COMPLETED
, TO_NUMBER(NULL) TOTAL_MODULES
, ORGT.NAME ORGANIZATION_NAME
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, PER.FIRST_NAME||' '||PER.LAST_NAME PERSON_NAME
, CON.FIRST_NAME||' '||CON.LAST_NAME CONTACT_NAME
, TAV.VERSION_NAME OTA_EQUI_ACTIVITY_VERSION_NAME
, TAD.NAME OTA_EQUI_ACTIVITY_NAME
, 'NON_OTA' DATA_SOURCE
, '_DF:OTA:OTA_NOTRNG_HISTORIES:TNH'
, '_DF:_DUMMY:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TNH.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TNH.CONTACT_ID CONTACT_ID
, TNH.PERSON_ID PERSON_ID
, TNH.CUSTOMER_ID CUSTOMER_ID
, TNH.ORGANIZATION_ID ORGANIZATION_ID
, TNH.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL) TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
FROM OTA_NOTRNG_HISTORIES TNH
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, RA_CONTACTS PER
, RA_CONTACTS CON
, RA_CUSTOMERS CUS
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
WHERE TNH.CUSTOMER_ID IS NOT NULL
AND TNH.CUSTOMER_ID = CUS.CUSTOMER_ID
AND TNH.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TNH.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TNH.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TNH.PERSON_ID = PER.CONTACT_ID
AND TNH.CONTACT_ID = CON.CONTACT_ID(+)
AND TNH.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TNH.BUSINESS_GROUP_ID) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, EVT.TITLE TRAINING_TITLE
, EVT.COURSE_END_DATE TRAINING_COMPLETION_DATE
, NULL TRAINING_TYPE
, ORGT1.NAME TRAINING_CENTER
, VEN.VENDOR_NAME TRAINING_PROVIDER
, NULL TRAINING_AWARD
, NULL TRAINING_RATING
, EVT.DURATION TRAINING_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) TRAINING_DURATION_UNITS
, NULL TRAINING_STATUS
, 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
, ORGT.NAME ORGANIZATION_NAME
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, PER.FIRST_NAME||' '||PER.LAST_NAME PERSON_NAME
, CON.FIRST_NAME||' '||CON.LAST_NAME CONTACT_NAME
, TAV.VERSION_NAME OTA_EQUI_ACTIVITY_VERSION_NAME
, TAD.NAME OTA_EQUI_ACTIVITY_NAME
, 'OTA' DATA_SOURCE
, '_DF:_DUMMY:OTA:OTA_NOTRNG_HISTORIES:TNH'
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, EVT.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TDB.CONTACT_ID CONTACT_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PO_VENDORS VEN
, OTA_DELEGATE_BOOKINGS TDB
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F CON
, RA_CUSTOMERS CUS
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
WHERE TDB.ORGANIZATION_ID IS NOT NULL
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE = USERENV('LANG')
AND TDB.EVENT_ID = EVT.EVENT_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.VENDOR_ID = VEN.VENDOR_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 TDB.DATE_BOOKING_PLACED BETWEEN PER.EFFECTIVE_START_DATE(+)
AND PER.EFFECTIVE_END_DATE (+)
AND TDB.CONTACT_ID = CON.PERSON_ID(+)
AND TDB.DATE_BOOKING_PLACED BETWEEN CON.EFFECTIVE_START_DATE(+)
AND CON.EFFECTIVE_END_DATE(+)
AND TDB.CUSTOMER_ID = CUS.CUSTOMER_ID(+)
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, EVT.TITLE TRAINING_TITLE
, EVT.COURSE_END_DATE TRAINING_COMPLETION_DATE
, NULL TRAINING_TYPE
, ORGT1.NAME TRAINING_CENTER
, VEN.VENDOR_NAME TRAINING_PROVIDER
, NULL TRAINING_AWARD
, NULL TRAINING_RATING
, EVT.DURATION TRAINING_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) TRAINING_DURATION_UNITS
, NULL TRAINING_STATUS
, 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
, ORGT.NAME ORGANIZATION_NAME
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, PER.FIRST_NAME||' '||PER.LAST_NAME PERSON_NAME
, CON.FIRST_NAME||' '||CON.LAST_NAME CONTACT_NAME
, TAV.VERSION_NAME OTA_EQUI_ACTIVITY_VERSION_NAME
, TAD.NAME OTA_EQUI_ACTIVITY_NAME
, 'OTA' DATA_SOURCE
, '_DF:_DUMMY:OTA:OTA_NOTRNG_HISTORIES:TNH'
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, EVT.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TDB.CONTACT_ID CONTACT_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PO_VENDORS VEN
, OTA_DELEGATE_BOOKINGS TDB
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
, RA_CONTACTS PER
, RA_CONTACTS CON
, RA_CUSTOMERS CUS
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
WHERE TDB.CUSTOMER_ID IS NOT NULL
AND TDB.CUSTOMER_ID = CUS.CUSTOMER_ID
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
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.EVENT_ID = EVT.EVENT_ID (+)
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND DECODE(ORGT1.ORGANIZATION_ID
, NULL
, 'X'
, ORGT1.LANGUAGE) = DECODE(ORGT1.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND EVT.VENDOR_ID = VEN.VENDOR_ID (+)
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TDB.DELEGATE_PERSON_ID = PER.CONTACT_ID (+)
AND TDB.CONTACT_ID = CON.CONTACT_ID(+)
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) WITH READ ONLY

Columns