Product: | BIM - Marketing Intelligence(Obsolete) |
---|---|
Description: | |
Implementation/DBA Data: | Not implemented in this database |
SELECT AEO.EVENT_OFFER_ID || '-' || INST.INSTANCE_CODE || '-EVENT_OFFER' EVTOFFER_PK
, AEH.EVENT_HEADER_ID || '-' || INST.INSTANCE_CODE || '-EVENT' EVENT_FK
, AEO.EVENT_OFFER_NAME EVTOFFER_DP
, AEO.EVENT_OFFER_NAME NAME
, 'EVENT OFFERING' LEVEL_NAME
, AEO.EVENT_OFFER_ID EVTOFFER_ID
, AEO.PARENT_EVENT_OFFER_ID PARENT_EVENT_OFFER_ID
, AEO.EVENT_HEADER_ID EVENT_HEADER_ID
, AEO.EVENT_LEVEL EVENT_LEVEL
, AEO.EVENT_TYPE_CODE EVENT_TYPE_CODE
, AEO.USER_STATUS_ID USER_STATUS_ID
, AEO.LAST_STATUS_DATE LAST_STATUS_DATE
, AEO.SYSTEM_STATUS_CODE SYSTEM_STATUS_CODE
, AEO.TIMEZONE_ID TIMEZONE_ID
, AEO.REG_WAITLIST_PCT REG_WAITLIST_PCT
, AEO.REG_OVERBOOK_PCT REG_OVERBOOK_PCT
, AEO.REG_MINIMUM_CAPACITY REG_MINIMUM_CAPACITY
, AEO.REG_MAXIMUM_CAPACITY REG_MAXIMUM_CAPACITY
, AEO.REG_EFFECTIVE_CAPACITY REG_EFFECTIVE_CAPACITY
, AEO.PRICELIST_LINE_ID PRICELIST_LINE_ID
, AEO.PRICELIST_HEADER_ID PRICELIST_HEADER_ID
, AEO.OWNER_USER_ID OWNER_USER_ID
, AEO.ORG_ID ORG_ID
, AEO.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, AEO.EVENT_LOCATION_ID EVENT_LOCATION_ID
, AEO.EVENT_DURATION EVENT_DURATION
, AEO.EVENT_DELIVERY_METHOD_ID EVENT_DELIVERY_METHOD_ID
, AEO.COORDINATOR_ID COORDINATOR_ID
, AEO.CERTIFICATION_CREDITS CERTIFICATION_CREDITS
, AEO.WAITLIST_ACTION_TYPE_CODE WAITLIST_ACTION_TYPE_CODE
, AEO.STREAM_TYPE_CODE STREAM_TYPE_CODE
, AEO.SOURCE_CODE SOURCE_CODE
, AEO.REG_WAITLIST_ALLOWED_FLAG REG_WAITLIST_ALLOWED_FLAG
, AEO.REG_START_TIME REG_START_TIME
, AEO.REG_REQUIRED_FLAG REG_REQUIRED_FLAG
, AEO.REG_OVERBOOK_ALLOWED_FLAG REG_OVERBOOK_ALLOWED_FLAG
, AEO.REG_INVITED_ONLY_FLAG REG_INVITED_ONLY_FLAG
, AEO.REG_FROZEN_FLAG REG_FROZEN_FLAG
, AEO.REG_END_TIME REG_END_TIME
, AEO.REG_CHARGE_FLAG REG_CHARGE_FLAG
, AEO.PRIORITY_TYPE_CODE PRIORITY_TYPE_CODE
, AEO.PARTNER_FLAG PARTNER_FLAG
, AEO.OVERFLOW_FLAG OVERFLOW_FLAG
, AEO.EVENT_START_DATE_TIME EVENT_START_DATE_TIME
, AEO.EVENT_STANDALONE_FLAG EVENT_STANDALONE_FLAG
, AEO.EVENT_REQUIRED_FLAG EVENT_REQUIRED_FLAG
, AEO.EVENT_LANGUAGE_CODE EVENT_LANGUAGE_CODE
, AEO.EVENT_FULL_FLAG EVENT_FULL_FLAG
, AEO.EVENT_END_DATE_TIME EVENT_END_DATE_TIME
, AEO.EVENT_DURATION_UOM_CODE EVENT_DURATION_UOM_CODE
, AEO.CERT_CREDIT_TYPE_CODE CERT_CREDIT_TYPE_CODE
, AEO.CANCELLATION_REASON_CODE CANCELLATION_REASON_CODE
, AEO.INBOUND_SCRIPT_NAME INBOUND_SCRIPT_NAME
, AEO.EMAIL EMAIL
, AEO.PHONE PHONE
, AEO.URL URL
, AEO.AUTO_REGISTER_FLAG AUTO_REGISTER_FLAG
, AEO.EVENT_VENUE_ID EVENT_VENUE_ID
, AEO.REG_START_DATE REG_START_DATE
, AEO.REG_MINIMUM_REQ_BY_DATE REG_MINIMUM_REQ_BY_DATE
, AEO.REG_END_DATE REG_END_DATE
, AEO.EVENT_START_DATE EVENT_START_DATE
, AEO.EVENT_END_DATE EVENT_END_DATE
, AEO.EVENT_MKTG_MESSAGE EVENT_MKTG_MESSAGE
, AEO.EVENT_OFFER_NAME EVENT_OFFER_NAME
, AEO.DESCRIPTION DESCRIPTION
, INST.INSTANCE_CODE INSTANCE
, AEO.CREATION_DATE CREATION_DATE
, GREATEST(AEO.LAST_UPDATE_DATE
, AEH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM AMS_EVENT_OFFERS_VL AEO
, AMS_EVENT_HEADERS_ALL_B AEH
, EDW_LOCAL_INSTANCE INST
WHERE AEO.EVENT_HEADER_ID = AEH.EVENT_HEADER_ID
AND AEO.EVENT_LEVEL = 'MAIN'
AND ((AEO.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD')) OR (AEH.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))) UNION SELECT AEH.EVENT_HEADER_ID || '-' || INST.INSTANCE_CODE || '-EVENT-L1' EVTOFFER_PK
, AEH.EVENT_HEADER_ID || '-' || INST.INSTANCE_CODE || '-EVENT' EVENT_FK
, AEH.EVENT_HEADER_NAME || ':' || 'EVENT HEADER' EVTOFFER_DP
, AEH.EVENT_HEADER_NAME || ':' || 'EVENT HEADER' NAME
, 'EVENT HEADER' LEVEL_NAME
, TO_NUMBER(NULL) EVTOFFER_ID
, TO_NUMBER(NULL) PARENT_EVENT_OFFER_ID
, TO_NUMBER(NULL) EVENT_HEADER_ID
, NULL EVENT_LEVEL
, NULL EVENT_TYPE_CODE
, TO_NUMBER(NULL) USER_STATUS_ID
, TO_DATE(NULL) LAST_STATUS_DATE
, NULL SYSTEM_STATUS_CODE
, TO_NUMBER(NULL) TIMEZONE_ID
, TO_NUMBER(NULL) REG_WAITLIST_PCT
, TO_NUMBER(NULL) REG_OVERBOOK_PCT
, TO_NUMBER(NULL) REG_MINIMUM_CAPACITY
, TO_NUMBER(NULL) REG_MAXIMUM_CAPACITY
, TO_NUMBER(NULL) REG_EFFECTIVE_CAPACITY
, TO_NUMBER(NULL) PRICELIST_LINE_ID
, TO_NUMBER(NULL) PRICELIST_HEADER_ID
, TO_NUMBER(NULL) OWNER_USER_ID
, TO_NUMBER(NULL) ORG_ID
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_NUMBER(NULL) EVENT_LOCATION_ID
, TO_NUMBER(NULL) EVENT_DURATION
, TO_NUMBER(NULL) EVENT_DELIVERY_METHOD_ID
, TO_NUMBER(NULL) COORDINATOR_ID
, TO_NUMBER(NULL) CERTIFICATION_CREDITS
, NULL WAITLIST_ACTION_TYPE_CODE
, NULL STREAM_TYPE_CODE
, NULL SOURCE_CODE
, NULL REG_WAITLIST_ALLOWED_FLAG
, TO_DATE(NULL) REG_START_TIME
, NULL REG_REQUIRED_FLAG
, NULL REG_OVERBOOK_ALLOWED_FLAG
, NULL REG_INVITED_ONLY_FLAG
, NULL REG_FROZEN_FLAG
, TO_DATE(NULL) REG_END_TIME
, NULL REG_CHARGE_FLAG
, NULL PRIORITY_TYPE_CODE
, NULL PARTNER_FLAG
, NULL OVERFLOW_FLAG
, TO_DATE(NULL) EVENT_START_DATE_TIME
, NULL EVENT_STANDALONE_FLAG
, NULL EVENT_REQUIRED_FLAG
, NULL EVENT_LANGUAGE_CODE
, NULL EVENT_FULL_FLAG
, TO_DATE(NULL) EVENT_END_DATE_TIME
, NULL EVENT_DURATION_UOM_CODE
, NULL CERT_CREDIT_TYPE_CODE
, NULL CANCELLATION_REASON_CODE
, NULL INBOUND_SCRIPT_NAME
, NULL EMAIL
, NULL PHONE
, NULL URL
, NULL AUTO_REGISTER_FLAG
, TO_NUMBER(NULL) EVENT_VENUE_ID
, TO_DATE(NULL) REG_START_DATE
, TO_DATE(NULL) REG_MINIMUM_REQ_BY_DATE
, TO_DATE(NULL) REG_END_DATE
, TO_DATE(NULL) EVENT_START_DATE
, TO_DATE(NULL) EVENT_END_DATE
, NULL EVENT_MKTG_MESSAGE
, NULL EVENT_OFFER_NAME
, NULL DESCRIPTION
, INST.INSTANCE_CODE INSTANCE
, AEH.CREATION_DATE CREATION_DATE
, AEH.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM AMS_EVENT_HEADERS_VL AEH
, EDW_LOCAL_INSTANCE INST
WHERE AEH.EVENT_LEVEL = 'MAIN'