V_QUERY_MKTLISTSEGMENT
Provides the ability to query a list's segments.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(255) | Description | |
| CATEGORY | nvarchar(100) | yes | Category |
| CODE | nvarchar(10) | Code | |
| ISINUSE | bit | yes | Is in use? |
| DATEREFRESHED | datetime | Current as of | |
| TYPE | nvarchar(14) | yes | Type |
| STATUS | nvarchar(14) | yes | Status |
| ORDERDATE | datetime | yes | Order date |
| EXPIRATIONDATE | datetime | yes | Expiration date |
| NUMBEROFCONTACTS | smallint | Number of contacts | |
| LAYOUTNAME | nvarchar(50) | yes | Layout name |
| FILENAME | nvarchar(255) | File name | |
| RECORDCOUNT | int | yes | Record count |
| RENTALQUANTITY | int | Rental quantity | |
| RENTALCOSTADJUSTMENT | money | Rental cost adjustment | |
| RENTALCOSTBASIS | nvarchar(12) | yes | Rental cost basis |
| EXCHANGEQUANTITY | int | Exchange quantity | |
| EXCHANGECOSTADJUSTMENT | money | Exchange cost adjustment | |
| EXCHANGECOSTBASIS | nvarchar(12) | yes | Exchange cost basis |
| RECEIVEDVIA | varchar(8) | yes | Received via |
| LISTID | uniqueidentifier | List ID | |
| KPICONTEXTRECORDID | nvarchar(100) | yes | KPIs context record ID |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| CURRENCYEXCHANGERATEID | uniqueidentifier | yes | Organization currency exchange rate ID |
| ORGANIZATIONRENTALCOSTADJUSTMENT | money | Rental cost adjustment (organization currency) | |
| ORGANIZATIONEXCHANGECOSTADJUSTMENT | money | Exchange cost adjustment (organization currency) | |
| SITEID | uniqueidentifier | yes | Site ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:11:54 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MKTLISTSEGMENT AS
select
[MKTSEGMENT].[ID],
[MKTSEGMENT].[NAME],
[MKTSEGMENT].[DESCRIPTION],
(select [DESCRIPTION] from dbo.[MKTSEGMENTCATEGORYCODE] where [ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]) as [CATEGORY],
[MKTSEGMENT].[CODE],
dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) as [ISINUSE],
isnull([IDSETREGISTER].[DATECHANGED], [MKTSEGMENTLIST].[DATECHANGED]) as [DATEREFRESHED],
[MKTSEGMENTLIST].[TYPE],
[MKTSEGMENTLIST].[STATUS],
[MKTSEGMENTLIST].[ORDERDATE],
[MKTSEGMENTLIST].[EXPIRATIONDATE],
[MKTSEGMENTLIST].[NUMBEROFCONTACTS],
(select [NAME] from dbo.[MKTLISTLAYOUT] where [ID] = [MKTSEGMENTLIST].[LISTLAYOUTID]) as [LAYOUTNAME],
[MKTSEGMENTLIST].[FILENAME],
[MKTSEGMENTLIST].[TOTALRECORDCOUNT] as [RECORDCOUNT],
[MKTSEGMENTLIST].[RENTALQUANTITY],
[MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
[MKTSEGMENTLIST].[RENTALCOSTBASIS],
[MKTSEGMENTLIST].[EXCHANGEQUANTITY],
[MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
[MKTSEGMENTLIST].[EXCHANGECOSTBASIS],
[MKTSEGMENTLIST].[RECEIVEDVIA],
/* Hidden fields */
[MKTSEGMENTLIST].[LISTID],
cast([MKTSEGMENT].[ID] as nvarchar(100)) as [KPICONTEXTRECORDID],
/* System fields */
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[MKTSEGMENT].[DATEADDED],
[MKTSEGMENT].[DATECHANGED],
[MKTSEGMENT].[TSLONG],
[MKTSEGMENTLIST].[BASECURRENCYID],
[MKTSEGMENTLIST].[CURRENCYEXCHANGERATEID],
[MKTSEGMENTLIST].[ORGANIZATIONRENTALCOSTADJUSTMENT],
[MKTSEGMENTLIST].[ORGANIZATIONEXCHANGECOSTADJUSTMENT],
[MKTSEGMENT].[SITEID]
/*#EXTENSION*/
from dbo.[MKTSEGMENTLIST]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[CURRENTSEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
left outer join dbo.[IDSETREGISTER] on [IDSETREGISTER].[DBOBJECTNAME] = ('V_MKTSEGMENTLIST_' + replace(cast([MKTSEGMENTLIST].[ID] as varchar(36)), '-', '_'))
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEGMENT].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEGMENT].[CHANGEDBYID];