View: AMS_P_OFFER_MKT_SEGMENTS_V

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

View Text

SELECT OFF.ACTIVITY_OFFER_ID
, QPL.DESCRIPTION
, OFF.OFFER_CODE
, OFF.START_DATE
, OFF.END_DATE
, OFF.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_ACT_OFFERS OFF
, QP_LIST_HEADERS_VL QPL
, JTF_TERR_ALL HIER
WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'OFFR'
AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = OFF.ACTIVITY_OFFER_ID
AND ( OFF.END_DATE IS NULL OR OFF.END_DATE > SYSDATE )
AND OFF.QP_LIST_HEADER_ID = QPL.LIST_HEADER_ID
AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = OFF.ACTIVITY_OFFER_ID
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 OFF.ACTIVITY_OFFER_ID
, QPL.DESCRIPTION
, OFF.OFFER_CODE
, OFF.START_DATE
, OFF.END_DATE
, OFF.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_ACT_OFFERS OFF
, QP_LIST_HEADERS_VL QPL
WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'OFFR'
AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = OFF.ACTIVITY_OFFER_ID
AND ( OFF.END_DATE IS NULL OR OFF.END_DATE > SYSDATE )
AND OFF.QP_LIST_HEADER_ID = QPL.LIST_HEADER_ID
AND MSEG.MARKET_SEGMENT_ID = CELL.CELL_ID UNION ALL SELECT OFF.ACTIVITY_OFFER_ID
, QPL.DESCRIPTION
, OFF.OFFER_CODE
, OFF.START_DATE
, OFF.END_DATE
, OFF.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_ACT_OFFERS OFF
, QP_LIST_HEADERS_VL QPL
, JTF_LOC_TYPES_VL LOCT
WHERE MSEG.ARC_ACT_MARKET_SEGMENT_USED_BY = 'OFFR'
AND MSEG.ACT_MARKET_SEGMENT_USED_BY_ID = OFF.ACTIVITY_OFFER_ID
AND ( OFF.END_DATE IS NULL OR OFF.END_DATE > SYSDATE )
AND OFF.QP_LIST_HEADER_ID = QPL.LIST_HEADER_ID
AND MSEG.MARKET_SEGMENT_ID = GEOA.LOCATION_HIERARCHY_ID
AND LOCT.LOCATION_TYPE_CODE = MSEG.SEGMENT_TYPE

Columns