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: EDW_BIM_MDCH_MDCHN_LCV

Product:	BIM - Marketing Intelligence(Obsolete)
Description:
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT AMC.MEDIA_CHANNEL_ID || '-' || INST.INSTANCE_CODE || '-MEDIA_CHANNEL' MDCHNL_PK
, AME.MEDIA_ID || '-' || INST.INSTANCE_CODE || '-MEDIA' MEDIA_FK
, ACH.CHANNEL_ID || '-' || INST.INSTANCE_CODE || '-CHANNEL' CHANNEL_FK
, 'NA_EDW' MDCHNL_DP
, 'NA_EDW' NAME
, 'MEDIA CHANNEL' LEVEL_NAME
, AMC.MEDIA_CHANNEL_ID MEDIA_CHANNEL_ID
, AMC.ACTIVE_FROM_DATE ACTIVE_FROM_DATE
, AMC.ACTIVE_TO_DATE ACTIVE_TO_DATE
, INST.INSTANCE_CODE INSTANCE
, AMC.CREATION_DATE CREATION_DATE
, GREATEST(AMC.LAST_UPDATE_DATE
, AME.LAST_UPDATE_DATE
, ACH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM AMS_MEDIA_CHANNELS AMC
, AMS_MEDIA_B AME
, AMS_CHANNELS_B ACH
, EDW_LOCAL_INSTANCE INST
WHERE AMC.MEDIA_ID = AME.MEDIA_ID
AND AMC.CHANNEL_ID = ACH.CHANNEL_ID
AND ((AMC.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD')) OR (AME.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD')) OR (ACH.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))) UNION SELECT ACH.CHANNEL_ID || '-' || INST.INSTANCE_CODE || '-CHANNEL-L3' MDCHNL_PK
, 'NA_EDW' MEDIA_FK
, ACH.CHANNEL_ID || '-' || INST.INSTANCE_CODE || '-CHANNEL' CHANNEL_FK
, ACH.CHANNEL_NAME || ':' || 'MARKETING CHANNEL' MDCHNL_DP
, ACH.CHANNEL_NAME || ':' || 'MARKETING CHANNEL' NAME
, 'CHANNEL' LEVEL_NAME
, TO_NUMBER(NULL) MEDIA_CHANNEL_ID
, TO_DATE(NULL) ACTIVE_FROM_DATE
, TO_DATE(NULL) ACTIVE_TO_DATE
, INST.INSTANCE_CODE INSTANCE
, ACH.CREATION_DATE CREATION_DATE
, ACH.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM AMS_CHANNELS_VL ACH
, EDW_LOCAL_INSTANCE INST UNION SELECT AME.MEDIA_ID || '-' || INST.INSTANCE_CODE || '-MEDIA-L2' MDCHNL_PK
, AME.MEDIA_ID || '-' || INST.INSTANCE_CODE || '-MEDIA' MEDIA_FK
, 'NA_EDW' CHANNEL_FK
, AME.MEDIA_NAME || ':' || 'MEDIA/ACTIVITY' MDCHNL_DP
, AME.MEDIA_NAME || ':' || 'MEDIA/ACTIVITY' NAME
, 'MEDIA/ACTIVITY' LEVEL_NAME
, TO_NUMBER(NULL) MEDIA_CHANNEL_ID
, TO_DATE(NULL) ACTIVE_FROM_DATE
, TO_DATE(NULL) ACTIVE_TO_DATE
, INST.INSTANCE_CODE INSTANCE
, AME.CREATION_DATE CREATION_DATE
, AME.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM AMS_MEDIA_VL AME
, EDW_LOCAL_INSTANCE INST

Columns

Name
MDCHNL_PK
MEDIA_FK
CHANNEL_FK
MDCHNL_DP
NAME
LEVEL_NAME
MEDIA_CHANNEL_ID
ACTIVE_FROM_DATE
ACTIVE_TO_DATE
INSTANCE
CREATION_DATE
LAST_UPDATE_DATE