VIEW: APPS.OTA_EVENT_SEARCH_V2

Source

View Text - Preformatted

SELECT EVT.ROWID , EVT.EVENT_ID , EVT.OBJECT_VERSION_NUMBER , EVT.BUSINESS_GROUP_ID , ENT.TITLE , EVT.COURSE_START_DATE , EVT.COURSE_START_TIME , EVT.COURSE_END_DATE , EVT.COURSE_END_TIME , TO_CHAR(EVT.COURSE_START_DATE,'IW') WEEK_NUMBER , EVT.DURATION , EVT.ENROLMENT_START_DATE , EVT.ENROLMENT_END_DATE , EVT.PUBLIC_EVENT_FLAG , EVT.MINIMUM_ATTENDEES , EVT.MAXIMUM_ATTENDEES , EVT.MAXIMUM_INTERNAL_ATTENDEES , EVT.STANDARD_PRICE , EVT.PARENT_EVENT_ID , EVT.BOOK_INDEPENDENT_FLAG , EVT.ACTUAL_COST , EVT.BUDGET_COST , EVT.BUDGET_CURRENCY_CODE , EVT.CREATED_BY , EVT.CREATION_DATE , EVT.LAST_UPDATED_BY , EVT.LAST_UPDATE_LOGIN , EVT.LAST_UPDATE_DATE , EVT.COMMENTS , EVT.SECURE_EVENT_FLAG , EVT.EVT_INFORMATION_CATEGORY , EVT.EVT_INFORMATION1 , EVT.EVT_INFORMATION2 , EVT.EVT_INFORMATION3 , EVT.EVT_INFORMATION4 , EVT.EVT_INFORMATION5 , EVT.EVT_INFORMATION6 , EVT.EVT_INFORMATION7 , EVT.EVT_INFORMATION8 , EVT.EVT_INFORMATION9 , EVT.EVT_INFORMATION10 , EVT.EVT_INFORMATION11 , EVT.EVT_INFORMATION12 , EVT.EVT_INFORMATION13 , EVT.EVT_INFORMATION14 , EVT.EVT_INFORMATION15 , EVT.EVT_INFORMATION16 , EVT.EVT_INFORMATION17 , EVT.EVT_INFORMATION18 , EVT.EVT_INFORMATION19 , EVT.EVT_INFORMATION20 , ADM.ORGANIZATION_ID , ADM.NAME ORGANIZATION_NAME , EVT.CENTRE , HR_GENERAL.DECODE_LOOKUP('TRAINING_CENTRE',EVT.CENTRE) CENTRE_MEANING , CUR.CURRENCY_CODE , CUR.NAME CURRENCY_NAME , EVT.DEVELOPMENT_EVENT_TYPE , HR_GENERAL.DECODE_LOOKUP('DEV_EVENT_TYPE', EVT.DEVELOPMENT_EVENT_TYPE) DEVELOPMENT_EVENT_TYPE_MEANING , EVT.DURATION_UNITS , HR_GENERAL.DECODE_LOOKUP('OTA_DURATION_UNITS',EVT.DURATION_UNITS) DURATION_UNITS_MEANING , OFV.LANGUAGE_CODE , ota_utility.get_lang_name(ofv.language_code) language_description , EVT.PRICE_BASIS , HR_GENERAL.DECODE_LOOKUP('EVENT_PRICE_BASIS',EVT.PRICE_BASIS) PRICE_BASIS_MEANING , EVT.CATEGORY_CODE PROGRAMME_CODE , HR_GENERAL.DECODE_LOOKUP('ACTIVITY_CATEGORY',EVT.CATEGORY_CODE) PROGRAMME_CODE_MEANING , EVT.EVENT_STATUS , HR_GENERAL.DECODE_LOOKUP('SCHEDULED_EVENT_STATUS', EVT.EVENT_STATUS) EVENT_STATUS_MEANING , TAV.ACTIVITY_VERSION_ID , AVT.VERSION_NAME ACTIVITY_VERSION_NAME , EVT.EVENT_TYPE , HR_GENERAL.DECODE_LOOKUP('TRAINING_EVENT_TYPE',EVT.EVENT_TYPE) EVENT_TYPE_MEANING , OTA_EVT_SHD.INVOICED_AMOUNT_TOTAL (EVT.EVENT_ID) INVOICED_AMOUNT , EVT.USER_STATUS , HR_GENERAL.DECODE_LOOKUP('EVENT_USER_STATUS',EVT.USER_STATUS) USER_STATUS_MEANING , VEN.VENDOR_ID , VEN.VENDOR_NAME , PARL.TITLE PARENT_EVENT_TITLE , TSR.NAME PRIMARY_VENUE ,DECODE (EVT.EVENT_TYPE,'P', ENT.TITLE, EPPML.TITLE) PROGRAM_TITLE , 'X' SEARCH_FIELD , PPM.REQUIRED_FLAG ,TAV.INVENTORY_ITEM_ID ,TAV.ORGANIZATION_ID INV_ORGANIZATION_ID ,EVT.LINE_ID ,EVT.ORG_ID ,EVT.OWNER_ID ,EVT.TRAINING_CENTER_ID ,EVT.LOCATION_ID ,EVT.PARENT_OFFERING_ID FROM OTA_SUPPLIABLE_RESOURCES_TL TSR, OTA_RESOURCE_BOOKINGS TRB, PO_VENDORS VEN, OTA_ACTIVITY_VERSIONS TAV, OTA_ACTIVITY_VERSIONS_TL AVT, FND_CURRENCIES_VL CUR, HR_ALL_ORGANIZATION_UNITS_TL ADM, OTA_EVENTS PAR, OTA_EVENTS_TL PARL, OTA_EVENTS EVT, OTA_EVENTS_TL ENT, OTA_PROGRAM_MEMBERSHIPS PPM, OTA_EVENTS EPPM, OTA_EVENTS_TL EPPML, OTA_OFFERINGS_VL OFV WHERE ADM.LANGUAGE (+) = USERENV ('LANG') AND ADM.ORGANIZATION_ID (+) = EVT.ORGANIZATION_ID AND CUR.CURRENCY_CODE (+) = EVT.CURRENCY_CODE AND TAV.ACTIVITY_VERSION_ID = EVT.ACTIVITY_VERSION_ID AND AVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID AND AVT.LANGUAGE= USERENV('LANG') AND VEN.VENDOR_ID (+) = EVT.VENDOR_ID AND PAR.EVENT_ID (+) = EVT.PARENT_EVENT_ID AND PARL.EVENT_ID (+) = PAR.EVENT_ID AND PARL.LANGUAGE(+) = USERENV('LANG') AND TRB.EVENT_ID (+) = EVT.EVENT_ID AND TRB.PRIMARY_VENUE_FLAG (+) = 'Y' AND TSR.SUPPLIED_RESOURCE_ID (+) = TRB.SUPPLIED_RESOURCE_ID AND TSR.LANGUAGE = USERENV('LANG') AND PPM.EVENT_ID (+) = EVT.EVENT_ID AND EPPM.EVENT_ID(+) = PPM.PROGRAM_EVENT_ID AND EPPML.EVENT_ID (+) = EPPM.EVENT_ID AND EPPML.LANGUAGE (+) = USERENV('LANG') AND ENT.EVENT_ID = EVT.EVENT_ID AND ENT.LANGUAGE= USERENV('LANG') AND evt.parent_offering_id = ofv.offering_id(+)

View Text - HTML Formatted

SELECT EVT.ROWID
, EVT.EVENT_ID
, EVT.OBJECT_VERSION_NUMBER
, EVT.BUSINESS_GROUP_ID
, ENT.TITLE
, EVT.COURSE_START_DATE
, EVT.COURSE_START_TIME
, EVT.COURSE_END_DATE
, EVT.COURSE_END_TIME
, TO_CHAR(EVT.COURSE_START_DATE
, 'IW') WEEK_NUMBER
, EVT.DURATION
, EVT.ENROLMENT_START_DATE
, EVT.ENROLMENT_END_DATE
, EVT.PUBLIC_EVENT_FLAG
, EVT.MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES
, EVT.MAXIMUM_INTERNAL_ATTENDEES
, EVT.STANDARD_PRICE
, EVT.PARENT_EVENT_ID
, EVT.BOOK_INDEPENDENT_FLAG
, EVT.ACTUAL_COST
, EVT.BUDGET_COST
, EVT.BUDGET_CURRENCY_CODE
, EVT.CREATED_BY
, EVT.CREATION_DATE
, EVT.LAST_UPDATED_BY
, EVT.LAST_UPDATE_LOGIN
, EVT.LAST_UPDATE_DATE
, EVT.COMMENTS
, EVT.SECURE_EVENT_FLAG
, EVT.EVT_INFORMATION_CATEGORY
, EVT.EVT_INFORMATION1
, EVT.EVT_INFORMATION2
, EVT.EVT_INFORMATION3
, EVT.EVT_INFORMATION4
, EVT.EVT_INFORMATION5
, EVT.EVT_INFORMATION6
, EVT.EVT_INFORMATION7
, EVT.EVT_INFORMATION8
, EVT.EVT_INFORMATION9
, EVT.EVT_INFORMATION10
, EVT.EVT_INFORMATION11
, EVT.EVT_INFORMATION12
, EVT.EVT_INFORMATION13
, EVT.EVT_INFORMATION14
, EVT.EVT_INFORMATION15
, EVT.EVT_INFORMATION16
, EVT.EVT_INFORMATION17
, EVT.EVT_INFORMATION18
, EVT.EVT_INFORMATION19
, EVT.EVT_INFORMATION20
, ADM.ORGANIZATION_ID
, ADM.NAME ORGANIZATION_NAME
, EVT.CENTRE
, HR_GENERAL.DECODE_LOOKUP('TRAINING_CENTRE'
, EVT.CENTRE) CENTRE_MEANING
, CUR.CURRENCY_CODE
, CUR.NAME CURRENCY_NAME
, EVT.DEVELOPMENT_EVENT_TYPE
, HR_GENERAL.DECODE_LOOKUP('DEV_EVENT_TYPE'
, EVT.DEVELOPMENT_EVENT_TYPE) DEVELOPMENT_EVENT_TYPE_MEANING
, EVT.DURATION_UNITS
, HR_GENERAL.DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS) DURATION_UNITS_MEANING
, OFV.LANGUAGE_CODE
, OTA_UTILITY.GET_LANG_NAME(OFV.LANGUAGE_CODE) LANGUAGE_DESCRIPTION
, EVT.PRICE_BASIS
, HR_GENERAL.DECODE_LOOKUP('EVENT_PRICE_BASIS'
, EVT.PRICE_BASIS) PRICE_BASIS_MEANING
, EVT.CATEGORY_CODE PROGRAMME_CODE
, HR_GENERAL.DECODE_LOOKUP('ACTIVITY_CATEGORY'
, EVT.CATEGORY_CODE) PROGRAMME_CODE_MEANING
, EVT.EVENT_STATUS
, HR_GENERAL.DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS) EVENT_STATUS_MEANING
, TAV.ACTIVITY_VERSION_ID
, AVT.VERSION_NAME ACTIVITY_VERSION_NAME
, EVT.EVENT_TYPE
, HR_GENERAL.DECODE_LOOKUP('TRAINING_EVENT_TYPE'
, EVT.EVENT_TYPE) EVENT_TYPE_MEANING
, OTA_EVT_SHD.INVOICED_AMOUNT_TOTAL (EVT.EVENT_ID) INVOICED_AMOUNT
, EVT.USER_STATUS
, HR_GENERAL.DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS) USER_STATUS_MEANING
, VEN.VENDOR_ID
, VEN.VENDOR_NAME
, PARL.TITLE PARENT_EVENT_TITLE
, TSR.NAME PRIMARY_VENUE
, DECODE (EVT.EVENT_TYPE
, 'P'
, ENT.TITLE
, EPPML.TITLE) PROGRAM_TITLE
, 'X' SEARCH_FIELD
, PPM.REQUIRED_FLAG
, TAV.INVENTORY_ITEM_ID
, TAV.ORGANIZATION_ID INV_ORGANIZATION_ID
, EVT.LINE_ID
, EVT.ORG_ID
, EVT.OWNER_ID
, EVT.TRAINING_CENTER_ID
, EVT.LOCATION_ID
, EVT.PARENT_OFFERING_ID
FROM OTA_SUPPLIABLE_RESOURCES_TL TSR
, OTA_RESOURCE_BOOKINGS TRB
, PO_VENDORS VEN
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL AVT
, FND_CURRENCIES_VL CUR
, HR_ALL_ORGANIZATION_UNITS_TL ADM
, OTA_EVENTS PAR
, OTA_EVENTS_TL PARL
, OTA_EVENTS EVT
, OTA_EVENTS_TL ENT
, OTA_PROGRAM_MEMBERSHIPS PPM
, OTA_EVENTS EPPM
, OTA_EVENTS_TL EPPML
, OTA_OFFERINGS_VL OFV
WHERE ADM.LANGUAGE (+) = USERENV ('LANG')
AND ADM.ORGANIZATION_ID (+) = EVT.ORGANIZATION_ID
AND CUR.CURRENCY_CODE (+) = EVT.CURRENCY_CODE
AND TAV.ACTIVITY_VERSION_ID = EVT.ACTIVITY_VERSION_ID
AND AVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND AVT.LANGUAGE= USERENV('LANG')
AND VEN.VENDOR_ID (+) = EVT.VENDOR_ID
AND PAR.EVENT_ID (+) = EVT.PARENT_EVENT_ID
AND PARL.EVENT_ID (+) = PAR.EVENT_ID
AND PARL.LANGUAGE(+) = USERENV('LANG')
AND TRB.EVENT_ID (+) = EVT.EVENT_ID
AND TRB.PRIMARY_VENUE_FLAG (+) = 'Y'
AND TSR.SUPPLIED_RESOURCE_ID (+) = TRB.SUPPLIED_RESOURCE_ID
AND TSR.LANGUAGE = USERENV('LANG')
AND PPM.EVENT_ID (+) = EVT.EVENT_ID
AND EPPM.EVENT_ID(+) = PPM.PROGRAM_EVENT_ID
AND EPPML.EVENT_ID (+) = EPPM.EVENT_ID
AND EPPML.LANGUAGE (+) = USERENV('LANG')
AND ENT.EVENT_ID = EVT.EVENT_ID
AND ENT.LANGUAGE= USERENV('LANG')
AND EVT.PARENT_OFFERING_ID = OFV.OFFERING_ID(+)