View: AMS_ACT_MKT_SEGMENTS_VL

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

View Text

SELECT MSEG.ACTIVITY_MARKET_SEGMENT_ID
, MSEG.MARKET_SEGMENT_ID
, MSEG.ACT_MARKET_SEGMENT_USED_BY_ID
, MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY
, MSEG.OBJECT_VERSION_NUMBER
, 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
, JTF_TERR_ALL HIER
WHERE 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 MSEG.ACTIVITY_MARKET_SEGMENT_ID
, MSEG.MARKET_SEGMENT_ID
, MSEG.ACT_MARKET_SEGMENT_USED_BY_ID
, MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY
, MSEG.OBJECT_VERSION_NUMBER
, 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
WHERE MSEG.MARKET_SEGMENT_ID = CELL.CELL_ID UNION ALL SELECT MSEG.ACTIVITY_MARKET_SEGMENT_ID
, MSEG.MARKET_SEGMENT_ID
, MSEG.ACT_MARKET_SEGMENT_USED_BY_ID
, MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY
, MSEG.OBJECT_VERSION_NUMBER
, 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
, JTF_LOC_TYPES_VL LOCT
WHERE MSEG.MARKET_SEGMENT_ID = GEOA.LOCATION_HIERARCHY_ID
AND LOCT.LOCATION_TYPE_CODE = MSEG.SEGMENT_TYPE

Columns