eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: OTA_EVENT_ASSOCIATIONS_V

Product:	OTA - Learning Management
Description:	View to list External Asociations for an Event.
Implementation/DBA Data:	APPS.OTA_EVENT_ASSOCIATIONS_V

View Text

SELECT TEA.ROWID
, TEA.EVENT_ASSOCIATION_ID
, EVT.BUSINESS_GROUP_ID
, EVT.EVENT_ID
, ENT.TITLE
, TAD.ACTIVITY_ID
, EVT.ACTIVITY_VERSION_ID
, TAD.NAME
, AVT.VERSION_NAME
, EVT.COURSE_START_DATE
, EVT.COURSE_END_DATE
, EVT.CENTRE
, TC_LOOK.MEANING
, EVT.PRICE_BASIS
, EPB_LOOK.MEANING
, TEA.CUSTOMER_ID
, PARTY.PARTY_NAME CUSTOMER_NAME
, OTA_TEA_BUS.NUMBER_OF_DELEGATES(TEA.EVENT_ID
, TEA.CUSTOMER_ID)
, TEA.COMMENTS
, TEA.LAST_UPDATE_DATE
, TEA.LAST_UPDATED_BY
, TEA.LAST_UPDATE_LOGIN
, TEA.CREATED_BY
, TEA.CREATION_DATE
, TEA.TEA_INFORMATION_CATEGORY
, TEA.TEA_INFORMATION1
, TEA.TEA_INFORMATION2
, TEA.TEA_INFORMATION3
, TEA.TEA_INFORMATION4
, TEA.TEA_INFORMATION5
, TEA.TEA_INFORMATION6
, TEA.TEA_INFORMATION7
, TEA.TEA_INFORMATION8
, TEA.TEA_INFORMATION9
, TEA.TEA_INFORMATION10
, TEA.TEA_INFORMATION11
, TEA.TEA_INFORMATION12
, TEA.TEA_INFORMATION13
, TEA.TEA_INFORMATION14
, TEA.TEA_INFORMATION15
, TEA.TEA_INFORMATION16
, TEA.TEA_INFORMATION17
, TEA.TEA_INFORMATION18
, TEA.TEA_INFORMATION19
, TEA.TEA_INFORMATION20
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ENT
, OTA_ACTIVITY_DEFINITIONS_TL TAD
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL AVT
, OTA_EVENT_ASSOCIATIONS TEA
, HR_LOOKUPS TC_LOOK
, HR_LOOKUPS EPB_LOOK
WHERE EVT.EVENT_ID = TEA.EVENT_ID
AND ENT.EVENT_ID = EVT.EVENT_ID
AND ENT.LANGUAGE = USERENV('LANG')
AND EVT.PRICE_BASIS NOT IN ( 'C'
, 'O')
AND CUST_ACCT.CUST_ACCOUNT_ID = TEA.CUSTOMER_ID
AND TAV.ACTIVITY_VERSION_ID (+) = EVT.ACTIVITY_VERSION_ID
AND AVT.ACTIVITY_VERSION_ID (+) =TAV.ACTIVITY_VERSION_ID
AND AVT.LANGUAGE (+) = USERENV ('LANG')
AND TAD.ACTIVITY_ID (+) = TAV.ACTIVITY_ID
AND TAD.LANGUAGE (+) = USERENV ('LANG')
AND TC_LOOK.LOOKUP_TYPE (+) = 'TRAINING_CENTRE'
AND TC_LOOK.LOOKUP_CODE (+) = EVT.CENTRE
AND EPB_LOOK.LOOKUP_TYPE (+) = 'EVENT_PRICE_BASIS'
AND EPB_LOOK.LOOKUP_CODE (+) = EVT.PRICE_BASIS
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID UNION SELECT TEA.ROWID
, TEA.EVENT_ASSOCIATION_ID
, EVT.BUSINESS_GROUP_ID
, EVT.EVENT_ID
, ENT.TITLE
, TAD.ACTIVITY_ID
, EVT.ACTIVITY_VERSION_ID
, TAD.NAME
, AVT.VERSION_NAME
, EVT.COURSE_START_DATE
, EVT.COURSE_END_DATE
, EVT.CENTRE
, TC_LOOK.MEANING
, EVT.PRICE_BASIS
, EPB_LOOK.MEANING
, TEA.CUSTOMER_ID
, PARTY.PARTY_NAME CUSTOMER_NAME
, OTA_TEA_BUS.NUMBER_OF_DELEGATES(TEA.EVENT_ID
, TEA.CUSTOMER_ID)
, TEA.COMMENTS
, TEA.LAST_UPDATE_DATE
, TEA.LAST_UPDATED_BY
, TEA.LAST_UPDATE_LOGIN
, TEA.CREATED_BY
, TEA.CREATION_DATE
, TEA.TEA_INFORMATION_CATEGORY
, TEA.TEA_INFORMATION1
, TEA.TEA_INFORMATION2
, TEA.TEA_INFORMATION3
, TEA.TEA_INFORMATION4
, TEA.TEA_INFORMATION5
, TEA.TEA_INFORMATION6
, TEA.TEA_INFORMATION7
, TEA.TEA_INFORMATION8
, TEA.TEA_INFORMATION9
, TEA.TEA_INFORMATION10
, TEA.TEA_INFORMATION11
, TEA.TEA_INFORMATION12
, TEA.TEA_INFORMATION13
, TEA.TEA_INFORMATION14
, TEA.TEA_INFORMATION15
, TEA.TEA_INFORMATION16
, TEA.TEA_INFORMATION17
, TEA.TEA_INFORMATION18
, TEA.TEA_INFORMATION19
, TEA.TEA_INFORMATION20
, DB2.SOURCE_OF_BOOKING
, DB2.CONTACT_ID
, CON.LAST_NAME
, CON.FIRST_NAME
, CON.TITLE
, CTI_LOOK.MEANING
, OTA_TDB_BUS.GET_FULL_NAME (CON.LAST_NAME
, CTI_LOOK.MEANING
, CON.FIRST_NAME
, EVT.BUSINESS_GROUP_ID
, NULL
, NULL )
, DB2.DELEGATE_CONTACT_PHONE
, DB2.DELEGATE_CONTACT_FAX
, DB2.CONTACT_ADDRESS_ID
, LOC.ADDRESS1||DECODE(LOC.ADDRESS1
, NULL
, ''
, '
, ')|| LOC.ADDRESS2||DECODE(LOC.ADDRESS2
, NULL
, ''
, '
, ')|| LOC.ADDRESS3||DECODE(LOC.ADDRESS3
, NULL
, ''
, '
, ')|| LOC.ADDRESS4||DECODE(LOC.ADDRESS4
, NULL
, ''
, '
, ')|| LOC.CITY||DECODE(LOC.CITY
, NULL
, ''
, '
, ')|| LOC.STATE||DECODE(LOC.STATE
, NULL
, ''
, '
, ')|| LOC.PROVINCE||DECODE(LOC.PROVINCE
, NULL
, ''
, '
, ')|| LOC.COUNTY||DECODE(LOC.COUNTY
, NULL
, ''
, '
, ')|| LOC.POSTAL_CODE||DECODE(LOC.POSTAL_CODE
, NULL
, ''
, '
, ')|| LOC.COUNTRY
, DB2.BOOKING_STATUS_TYPE_ID
, STAT.NAME
, DB2.OBJECT_VERSION_NUMBER
, DB2.BOOKING_ID
, DB2.INTERNAL_BOOKING_FLAG
, SOB_LOOK.MEANING
, EVT.CURRENCY_CODE
, TFL.STANDARD_AMOUNT
, TFL.BOOKING_DEAL_ID
, DEAL.NAME
, DEAL.TYPE
, DECODE(DEAL.TYPE
, 'D'
, DEAL.NAME
, NULL)
, DECODE(DEAL.TYPE
, 'P'
, DEAL.NAME
, NULL)
, TPL.PRICE_LIST_TYPE
, TPL.TRAINING_UNIT_TYPE
, TU_LOOK.MEANING
, TFH.FINANCE_HEADER_ID
, TFH.CUSTOMER_ID
, PARTY2.PARTY_NAME CUSTOMER_NAME
, TFH.INVOICE_CONTACT
, TFH.INVOICE_ADDRESS
, TFL.FINANCE_LINE_ID
, TFL.MONEY_AMOUNT
, TFL.UNITARY_AMOUNT
, TFL.OBJECT_VERSION_NUMBER
, NULL LAST_NAME_ALT
, NULL FIRST_NAME_ALT
, DB2.DELEGATE_CONTACT_EMAIL
FROM OTA_DELEGATE_BOOKINGS DB2
, OTA_FINANCE_LINES TFL
, OTA_FINANCE_HEADERS TFH
, OTA_BOOKING_DEALS DEAL
, OTA_BOOKING_STATUS_TYPES_TL STAT
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ENT
, OTA_ACTIVITY_DEFINITIONS_TL TAD
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL AVT
, OTA_EVENT_ASSOCIATIONS TEA
, HR_LOOKUPS TC_LOOK
, HR_LOOKUPS EPB_LOOK
, HR_LOOKUPS TU_LOOK
, HR_LOOKUPS SOB_LOOK
, HZ_PARTIES PARTY2
, HZ_CUST_ACCOUNTS CUST_ACCT2
, OTA_PRICE_LISTS TPL
, (SELECT SUBSTRB( PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, SUBSTRB( PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, ORG_CONT.TITLE TITLE
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, ACCT_ROLE.CUST_ACCOUNT_ID CUSTOMER_ID
, ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.DIRECTIONAL_FLAG = 'F' ) CON
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, AR_LOOKUPS CTI_LOOK
WHERE EVT.EVENT_ID = TEA.EVENT_ID
AND ENT.EVENT_ID = EVT.EVENT_ID
AND ENT.LANGUAGE = USERENV ('LANG')
AND EVT.PRICE_BASIS IN ( 'C'
, 'O')
AND CUST_ACCT.CUST_ACCOUNT_ID = TEA.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND DB2.CUSTOMER_ID = TEA.CUSTOMER_ID
AND DB2.EVENT_ID = TEA.EVENT_ID
AND DB2.BOOKING_ID = (SELECT MIN(BOOKING_ID)
FROM OTA_DELEGATE_BOOKINGS
WHERE EVENT_ID = TEA.EVENT_ID
AND CUSTOMER_ID = DB2.CUSTOMER_ID) AND ACCT_SITE.CUST_ACCT_SITE_ID (+) = DB2.CONTACT_ADDRESS_ID AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID (+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID (+)
AND NVL(ACCT_SITE.ORG_ID
, NVL( TO_NUMBER( DECODE( SUBSTRB( USERENV( 'CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND STAT.BOOKING_STATUS_TYPE_ID = DB2.BOOKING_STATUS_TYPE_ID
AND STAT.LANGUAGE = USERENV ('LANG')
AND TFL.BOOKING_ID (+) = DB2.BOOKING_ID
AND TFL.CANCELLED_FLAG (+) = 'N'
AND TFH.FINANCE_HEADER_ID (+) = TFL.FINANCE_HEADER_ID
AND CUST_ACCT2.CUST_ACCOUNT_ID (+) = TFH.CUSTOMER_ID
AND CUST_ACCT2.PARTY_ID = PARTY2.PARTY_ID(+)
AND DEAL.BOOKING_DEAL_ID (+) = TFL.BOOKING_DEAL_ID
AND TPL.PRICE_LIST_ID (+) = DEAL.PRICE_LIST_ID
AND TU_LOOK.LOOKUP_TYPE (+) = 'TRAINING_UNIT'
AND TU_LOOK.LOOKUP_CODE (+) = TPL.TRAINING_UNIT_TYPE
AND TAV.ACTIVITY_VERSION_ID (+) = EVT.ACTIVITY_VERSION_ID
AND AVT.ACTIVITY_VERSION_ID (+) = TAV.ACTIVITY_VERSION_ID
AND AVT.LANGUAGE (+) = USERENV ('LANG')
AND TAD.ACTIVITY_ID (+) = TAV.ACTIVITY_ID
AND TAD.LANGUAGE (+) = USERENV ('LANG')
AND TC_LOOK.LOOKUP_TYPE (+) = 'TRAINING_CENTRE'
AND TC_LOOK.LOOKUP_CODE (+) = EVT.CENTRE
AND EPB_LOOK.LOOKUP_TYPE = 'EVENT_PRICE_BASIS'
AND EPB_LOOK.LOOKUP_CODE = EVT.PRICE_BASIS
AND CON.CONTACT_ID (+) = DB2.CONTACT_ID
AND CTI_LOOK.LOOKUP_TYPE (+) = 'CONTACT_TITLE'
AND CTI_LOOK.LOOKUP_CODE (+) = CON.TITLE
AND SOB_LOOK.LOOKUP_TYPE (+) = 'BOOKING_SOURCE'
AND SOB_LOOK.LOOKUP_CODE (+) = DB2.SOURCE_OF_BOOKING
AND DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(ACCT_SITE.ORG_ID (+)
, NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)))
, 1 ) = DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 10)))
, -99))
, 1 )

Columns

Name
ROW_ID
EVENT_ASSOCIATION_ID
BUSINESS_GROUP_ID
EVENT_ID
EVENT_TITLE
EVENT_ACTIVITY_ID
EVENT_ACTIVITY_VERSION_ID
EVENT_ACTIVITY_NAME
EVENT_ACTIVITY_VERSION_NAME
EVENT_START_DATE
EVENT_END_DATE
EVENT_CENTRE
EVENT_CENTRE_MEANING
EVENT_PRICE_BASIS
EVENT_PRICE_BASIS_MEANING
CUSTOMER_ID
CUSTOMER_NAME
CUSTOMER_TOTAL_DELEGATES
COMMENTS
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
CREATED_BY
CREATION_DATE
TEA_INFORMATION_CATEGORY
TEA_INFORMATION1
TEA_INFORMATION2
TEA_INFORMATION3
TEA_INFORMATION4
TEA_INFORMATION5
TEA_INFORMATION6
TEA_INFORMATION7
TEA_INFORMATION8
TEA_INFORMATION9
TEA_INFORMATION10
TEA_INFORMATION11
TEA_INFORMATION12
TEA_INFORMATION13
TEA_INFORMATION14
TEA_INFORMATION15
TEA_INFORMATION16
TEA_INFORMATION17
TEA_INFORMATION18
TEA_INFORMATION19
TEA_INFORMATION20
SOURCE_OF_BOOKING
DELEGATE_CONTACT_ID
DELEGATE_CONTACT_LAST_NAME
DELEGATE_CONTACT_FIRST_NAME
DELEGATE_CONTACT_TITLE
DELEGATE_CONTACT_TITLE_MEANING
DELEGATE_CONTACT_FULL_NAME
DELEGATE_CONTACT_PHONE
DELEGATE_CONTACT_FAX
DELEGATE_CONTACT_ADDRESS_ID
DELEGATE_CONTACT_FULL_ADDRESS
BOOKING_STATUS_TYPE_ID
BOOKING_STATUS_MEANING
TDB_OBJECT_VERSION_NUMBER
DELEGATE_BOOKING_ID
INTERNAL_BOOKING_FLAG
SOURCE_OF_BOOKING_MEANING
EVENT_CURRENCY_CODE
FINANCE_STANDARD_AMOUNT
BOOKING_DEAL_ID
BOOKING_DEAL_NAME
BOOKING_DEAL_TYPE
DISCOUNT_DEAL_NAME
PRE_PURCHASE_DEAL_NAME
PRICE_LIST_TYPE
TRAINING_UNIT_TYPE
TRAINING_UNIT_MEANING
FINANCE_HEADER_ID
INVOICE_CUSTOMER_ID
INVOICE_CUSTOMER_NAME
INVOICE_CONTACT
INVOICE_ADDRESS
FINANCE_LINE_ID
MONEY_AMOUNT
UNITARY_AMOUNT
TFL_OBJECT_VERSION_NUMBER
DELEGATE_CONT_LAST_NAME_ALT
DELEGATE_CONT_FIRST_NAME_ALT
DELEGATE_CONTACT_EMAIL