VIEW: APPS.AMS_ACT_MKT_SEGMENTS_VL

Source

View Text - Preformatted

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

View Text - HTML Formatted

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