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.

VIEW: APPS.OTA_EVENT_ASSOCIATIONS_V

Source

View Text - Preformatted


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 )

View Text - HTML Formatted

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 )