VIEW: APPS.AMS_P_CAMPAIGN_MKT_SEGMENTS_V

Source

View Text - Preformatted

SELECT CAM.CAMPAIGN_ID, CAM.CAMPAIGN_NAME, CAM.SOURCE_CODE, CAM.ACTUAL_EXEC_START_DATE, CAM.ACTUAL_EXEC_END_DATE, CAM.STATUS_CODE, CELL.CELL_ID MARKET_SEGMENT_ID, CELL.CELL_NAME MARKET_SEGMENT_NAME, MSEG.SEGMENT_TYPE, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MKT_SEGMENT_TYPE', MSEG.SEGMENT_TYPE) SEGMENT_NAME , MSEG.EXCLUDE_FLAG, MSEG.ELIGIBILITY_TYPE, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MARKET_ELIG_TYPE', MSEG.ELIGIBILITY_TYPE) ELIGIBILITY_NAME , TO_NUMBER(NULL) TERR_HIERARCHY_ID, NULL HIERARCHY_NAME from AMS_ACT_MARKET_SEGMENTS MSEG, AMS_CELLS_VL CELL, AMS_CAMPAIGNS_VL CAM /* CAM.CAMPAIGN_ID, CAM.CAMPAIGN_NAME, CAM.SOURCE_CODE, CAM.ACTUAL_EXEC_START_DATE, CAM.ACTUAL_EXEC_END_DATE, CAM.STATUS_CODE, MSEG.MARKET_SEGMENT_ID, TERR.NODE_VALUE MARKET_SEGMENT_NAME, MSEG.SEGMENT_TYPE, TERR.HIERARCHY_LEVEL SEGMENT_NAME , MSEG.EXCLUDE_FLAG, MSEG.ELIGIBILITY_TYPE, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MARKET_ELIG_TYPE', MSEG.ELIGIBILITY_TYPE) ELIGIBILITY_NAME , MSEG.TERR_HIERARCHY_ID, HIER.NAME HIERARCHY_NAME from AMS_ACT_MARKET_SEGMENTS MSEG, AMS_TERR_V TERR, AMS_CAMPAIGNS_VL CAM, JTF_TERR_ALL HIER */ WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP' AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = CAM.CAMPAIGN_ID AND CAM.ACTIVE_FLAG = 'Y' AND CAM.STATUS_CODE = 'ACTIVE' AND CAM.SHOW_CAMPAIGN_FLAG = 'Y' AND MSEG.MARKET_SEGMENT_ID = CELL.CELL_ID /* MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP' AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = CAM.CAMPAIGN_ID AND CAM.ACTIVE_FLAG = 'Y' AND CAM.STATUS_CODE = 'ACTIVE' AND CAM.SHOW_CAMPAIGN_FLAG = 'Y' AND MSEG.MARKET_SEGMENT_ID = TERR.NODE_ID AND MSEG.SEGMENT_TYPE = TO_CHAR(TERR.HIERARCHY_LEVEL_ID) AND HIER.TERR_ID = MSEG.TERR_HIERARCHY_ID UNION ALL SELECT CAM.CAMPAIGN_ID, CAM.CAMPAIGN_NAME, CAM.SOURCE_CODE, CAM.ACTUAL_EXEC_START_DATE, CAM.ACTUAL_EXEC_END_DATE, CAM.STATUS_CODE, CELL.CELL_ID MARKET_SEGMENT_ID, CELL.CELL_NAME MARKET_SEGMENT_NAME, MSEG.SEGMENT_TYPE, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MKT_SEGMENT_TYPE', MSEG.SEGMENT_TYPE) SEGMENT_NAME , MSEG.EXCLUDE_FLAG, MSEG.ELIGIBILITY_TYPE, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MARKET_ELIG_TYPE', MSEG.ELIGIBILITY_TYPE) ELIGIBILITY_NAME , TO_NUMBER(NULL) TERR_HIERARCHY_ID, NULL HIERARCHY_NAME from AMS_ACT_MARKET_SEGMENTS MSEG, AMS_CELLS_VL CELL, AMS_CAMPAIGNS_VL CAM WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP' AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = CAM.CAMPAIGN_ID AND CAM.ACTIVE_FLAG = 'Y' AND CAM.STATUS_CODE = 'ACTIVE' AND CAM.SHOW_CAMPAIGN_FLAG = 'Y' AND MSEG.MARKET_SEGMENT_ID = CELL.CELL_ID UNION ALL SELECT CAM.CAMPAIGN_ID, CAM.CAMPAIGN_NAME, CAM.SOURCE_CODE, CAM.ACTUAL_EXEC_START_DATE, CAM.ACTUAL_EXEC_END_DATE, CAM.STATUS_CODE, MSEG.MARKET_SEGMENT_ID, GEOA.LOCATION_NAME MARKET_SEGMENT_NAME, MSEG.SEGMENT_TYPE, LOCT.LOCATION_TYPE_NAME SEGMENT_NAME , MSEG.EXCLUDE_FLAG, MSEG.ELIGIBILITY_TYPE, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MARKET_ELIG_TYPE', MSEG.ELIGIBILITY_TYPE) ELIGIBILITY_NAME , TO_NUMBER(NULL) TERR_HIERARCHY_ID, NULL HIERARCHY_NAME from AMS_ACT_MARKET_SEGMENTS MSEG, AMS_GEOAREA_SCR_V GEOA, AMS_CAMPAIGNS_VL CAM, JTF_LOC_TYPES_VL LOCT WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP' AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = CAM.CAMPAIGN_ID AND CAM.ACTIVE_FLAG = 'Y' AND CAM.STATUS_CODE = 'ACTIVE' AND CAM.SHOW_CAMPAIGN_FLAG = 'Y' AND MSEG.MARKET_SEGMENT_ID = GEOA.LOCATION_HIERARCHY_ID AND LOCT.LOCATION_TYPE_CODE = MSEG.SEGMENT_TYPE */

View Text - HTML Formatted

SELECT CAM.CAMPAIGN_ID
, CAM.CAMPAIGN_NAME
, CAM.SOURCE_CODE
, CAM.ACTUAL_EXEC_START_DATE
, CAM.ACTUAL_EXEC_END_DATE
, CAM.STATUS_CODE
, CELL.CELL_ID MARKET_SEGMENT_ID
, CELL.CELL_NAME MARKET_SEGMENT_NAME
, MSEG.SEGMENT_TYPE
, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MKT_SEGMENT_TYPE'
, MSEG.SEGMENT_TYPE) SEGMENT_NAME
, MSEG.EXCLUDE_FLAG
, MSEG.ELIGIBILITY_TYPE
, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MARKET_ELIG_TYPE'
, MSEG.ELIGIBILITY_TYPE) ELIGIBILITY_NAME
, TO_NUMBER(NULL) TERR_HIERARCHY_ID
, NULL HIERARCHY_NAME
FROM AMS_ACT_MARKET_SEGMENTS MSEG
, AMS_CELLS_VL CELL
, AMS_CAMPAIGNS_VL CAM /* CAM.CAMPAIGN_ID
, CAM.CAMPAIGN_NAME
, CAM.SOURCE_CODE
, CAM.ACTUAL_EXEC_START_DATE
, CAM.ACTUAL_EXEC_END_DATE
, CAM.STATUS_CODE
, MSEG.MARKET_SEGMENT_ID
, TERR.NODE_VALUE MARKET_SEGMENT_NAME
, MSEG.SEGMENT_TYPE
, TERR.HIERARCHY_LEVEL SEGMENT_NAME
, MSEG.EXCLUDE_FLAG
, MSEG.ELIGIBILITY_TYPE
, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MARKET_ELIG_TYPE'
, MSEG.ELIGIBILITY_TYPE) ELIGIBILITY_NAME
, MSEG.TERR_HIERARCHY_ID
, HIER.NAME HIERARCHY_NAME
FROM AMS_ACT_MARKET_SEGMENTS MSEG
, AMS_TERR_V TERR
, AMS_CAMPAIGNS_VL CAM
, JTF_TERR_ALL HIER */
WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP'
AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = CAM.CAMPAIGN_ID
AND CAM.ACTIVE_FLAG = 'Y'
AND CAM.STATUS_CODE = 'ACTIVE'
AND CAM.SHOW_CAMPAIGN_FLAG = 'Y'
AND MSEG.MARKET_SEGMENT_ID = CELL.CELL_ID /* MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP'
AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = CAM.CAMPAIGN_ID
AND CAM.ACTIVE_FLAG = 'Y'
AND CAM.STATUS_CODE = 'ACTIVE'
AND CAM.SHOW_CAMPAIGN_FLAG = 'Y'
AND MSEG.MARKET_SEGMENT_ID = TERR.NODE_ID
AND MSEG.SEGMENT_TYPE = TO_CHAR(TERR.HIERARCHY_LEVEL_ID)
AND HIER.TERR_ID = MSEG.TERR_HIERARCHY_ID UNION ALL SELECT CAM.CAMPAIGN_ID
, CAM.CAMPAIGN_NAME
, CAM.SOURCE_CODE
, CAM.ACTUAL_EXEC_START_DATE
, CAM.ACTUAL_EXEC_END_DATE
, CAM.STATUS_CODE
, CELL.CELL_ID MARKET_SEGMENT_ID
, CELL.CELL_NAME MARKET_SEGMENT_NAME
, MSEG.SEGMENT_TYPE
, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MKT_SEGMENT_TYPE'
, MSEG.SEGMENT_TYPE) SEGMENT_NAME
, MSEG.EXCLUDE_FLAG
, MSEG.ELIGIBILITY_TYPE
, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MARKET_ELIG_TYPE'
, MSEG.ELIGIBILITY_TYPE) ELIGIBILITY_NAME
, TO_NUMBER(NULL) TERR_HIERARCHY_ID
, NULL HIERARCHY_NAME
FROM AMS_ACT_MARKET_SEGMENTS MSEG
, AMS_CELLS_VL CELL
, AMS_CAMPAIGNS_VL CAM
WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP'
AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = CAM.CAMPAIGN_ID
AND CAM.ACTIVE_FLAG = 'Y'
AND CAM.STATUS_CODE = 'ACTIVE'
AND CAM.SHOW_CAMPAIGN_FLAG = 'Y'
AND MSEG.MARKET_SEGMENT_ID = CELL.CELL_ID UNION ALL SELECT CAM.CAMPAIGN_ID
, CAM.CAMPAIGN_NAME
, CAM.SOURCE_CODE
, CAM.ACTUAL_EXEC_START_DATE
, CAM.ACTUAL_EXEC_END_DATE
, CAM.STATUS_CODE
, MSEG.MARKET_SEGMENT_ID
, GEOA.LOCATION_NAME MARKET_SEGMENT_NAME
, MSEG.SEGMENT_TYPE
, LOCT.LOCATION_TYPE_NAME SEGMENT_NAME
, MSEG.EXCLUDE_FLAG
, MSEG.ELIGIBILITY_TYPE
, AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MARKET_ELIG_TYPE'
, MSEG.ELIGIBILITY_TYPE) ELIGIBILITY_NAME
, TO_NUMBER(NULL) TERR_HIERARCHY_ID
, NULL HIERARCHY_NAME
FROM AMS_ACT_MARKET_SEGMENTS MSEG
, AMS_GEOAREA_SCR_V GEOA
, AMS_CAMPAIGNS_VL CAM
, JTF_LOC_TYPES_VL LOCT
WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP'
AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = CAM.CAMPAIGN_ID
AND CAM.ACTIVE_FLAG = 'Y'
AND CAM.STATUS_CODE = 'ACTIVE'
AND CAM.SHOW_CAMPAIGN_FLAG = 'Y'
AND MSEG.MARKET_SEGMENT_ID = GEOA.LOCATION_HIERARCHY_ID
AND LOCT.LOCATION_TYPE_CODE = MSEG.SEGMENT_TYPE */