View: AMS_P_CAMPAIGN_MKT_SEGMENTS_V

Product:	AMS - Marketing
Description:	This public view returns the market segments associated with a marketing campaigns.
Implementation/DBA Data:	APPS.AMS_P_CAMPAIGN_MKT_SEGMENTS_V

View Text

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 */

Columns