Product: | AMS - Marketing |
---|---|
Description: | Segment selection view definition |
Implementation/DBA Data: | APPS.AMS_CELLS_SEL_ALL_V |
SELECT PARTIAL_OLD.ROW_ID
, PARTIAL_OLD.CELL_ID CELL_ID
, PARTIAL_OLD.CELL_NAME CELL_NAME
, PARTIAL_OLD.DESCRIPTION
, PARTIAL_OLD.LAST_UPDATE_DATE
, PARTIAL_OLD.LAST_UPDATED_BY
, PARTIAL_OLD.CREATION_DATE
, PARTIAL_OLD.CREATED_BY
, PARTIAL_OLD.LAST_UPDATE_LOGIN
, PARTIAL_OLD.CELL_CODE
, PARTIAL_OLD.OWNER_ID
, PARTIAL_OLD.MARKET_SEGMENT_FLAG
, PARTIAL_OLD.ENABLED_FLAG
, PARTIAL_OLD.ORIGINAL_SIZE
, PARTIAL_OLD.PARENT_CELL_ID
, PARTIAL_OLD.OBJECT_VERSION_NUMBER
, PARTIAL_OLD.SEL_TYPE
, PARTIAL_OLD.USER_STATUS_ID
, PARTIAL_OLD.STATUS_CODE
, PARTIAL_OLD.STATUS_DATE
, PARTIAL_OLD.COUNTRY
, PARTIAL_OLD.DISCOVERER_SQL_ID
, PARTIAL_OLD.WORKBOOK_OWNER
, PARTIAL_OLD.WORKBOOK_NAME
, PARTIAL_OLD.WORKSHEET_NAME
, PARTIAL_OLD.ACTIVITY_DISCOVERER_ID
, PARTIAL_OLD.OBJECT_VERSION_NUMBER ACT_DISC_VERSION_NUMBER
, PARTIAL_OLD.LIST_QUERY_ID
, NEW.QUERY LIST_SQL_STRING
, PARTIAL_OLD.SOURCE_OBJECT_NAME
, PARTIAL_OLD.LIST_QUERY_VERSION_NUMBER
FROM (SELECT B.ROW_ID
, B.CELL_ID CELL_ID
, B.CELL_NAME CELL_NAME
, B.DESCRIPTION
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.CELL_CODE
, B.OWNER_ID
, B.MARKET_SEGMENT_FLAG
, B.ENABLED_FLAG
, B.ORIGINAL_SIZE
, B.PARENT_CELL_ID
, B.OBJECT_VERSION_NUMBER
, B.SEL_TYPE
, B.USER_STATUS_ID
, B.STATUS_CODE
, B.STATUS_DATE
, B.COUNTRY
, DISC.DISCOVERER_SQL_ID
, DISC.WORKBOOK_OWNER
, DISC.WORKBOOK_NAME
, DISC.WORKSHEET_NAME
, DISC.ACTIVITY_DISCOVERER_ID
, DISC.OBJECT_VERSION_NUMBER ACT_DISC_VERSION_NUMBER
, TO_NUMBER(NULL) LIST_QUERY_ID
, TO_CHAR(NULL) SOURCE_OBJECT_NAME
, TO_NUMBER(NULL) LIST_QUERY_VERSION_NUMBER
FROM AMS_CELLS_VL B
, AMS_ACT_DISCOVERER_ALL DISC
WHERE B.CELL_ID = DISC.ACT_DISCOVERER_USED_BY_ID
AND DISC.ARC_ACT_DISCOVERER_USED_BY = 'CELL'
AND B.SEL_TYPE = 'DIWB' UNION ALL SELECT B.ROW_ID
, B.CELL_ID CELL_ID
, B.CELL_NAME CELL_NAME
, B.DESCRIPTION
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.CELL_CODE
, B.OWNER_ID
, B.MARKET_SEGMENT_FLAG
, B.ENABLED_FLAG
, B.ORIGINAL_SIZE
, B.PARENT_CELL_ID
, B.OBJECT_VERSION_NUMBER
, B.SEL_TYPE
, B.USER_STATUS_ID
, B.STATUS_CODE
, B.STATUS_DATE
, B.COUNTRY
, TO_NUMBER(NULL) DISCOVERER_SQL_ID
, TO_CHAR(NULL) WORKBOOK_OWNER
, TO_CHAR(NULL) WORKBOOK_NAME
, TO_CHAR(NULL) WORKSHEET_NAME
, TO_NUMBER(NULL) ACTIVITY_DISCOVERER_ID
, TO_NUMBER(NULL) ACT_DISC_VERSION_NUMBER
, LIST.LIST_QUERY_ID
, LIST.SOURCE_OBJECT_NAME
, LIST.OBJECT_VERSION_NUMBER LIST_QUERY_VERSION_NUMBER
FROM AMS_CELLS_VL B
, AMS_LIST_QUERIES_ALL LIST
WHERE B.CELL_ID = LIST.ACT_LIST_QUERY_USED_BY_ID
AND LIST.ARC_ACT_LIST_QUERY_USED_BY = 'CELL'
AND B.SEL_TYPE = 'SQL' ) PARTIAL_OLD
, AMS_LIST_QUERIES_ALL NEW
WHERE NVL(PARTIAL_OLD.LIST_QUERY_ID
, 0) = NEW.LIST_QUERY_ID(+)