V_QUERY_DIRECTMARKETINGEFFORTBATCHCONTROLREPORT
View used for output for Direct Marketing Effort Batch Control Report.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| MAILINGNAME | nvarchar(100) | Marketing effort name | |
| SOURCECODE | nvarchar(50) | yes | Source code |
| SEGMENTNAME | nvarchar(100) | Segment name | |
| SEGMENTCODE | nvarchar(10) | Segment code | |
| TESTSEGMENTNAME | nvarchar(203) | yes | Test segment name |
| TESTSEGMENTCODE | nvarchar(10) | Test segment code | |
| PACKAGENAME | nvarchar(100) | Package name | |
| PACKAGECODE | nvarchar(10) | yes | Package code |
| LISTNAME | nvarchar(43) | yes | List name |
| LISTCODE | nvarchar(10) | yes | List code |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 4/29/2010 7:38:53 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.6.1444.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_DIRECTMARKETINGEFFORTBATCHCONTROLREPORT AS
--Mailing segments
select
[MKTSEGMENTATIONSEGMENT].[ID],
[MKTSEGMENTATION].[NAME] as [MAILINGNAME],
dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONSEGMENT].[ID], default, default) as [SOURCECODE],
[MKTSEGMENT].[NAME] as [SEGMENTNAME],
[MKTSEGMENTATIONSEGMENT].[CODE] as [SEGMENTCODE],
null as [TESTSEGMENTNAME],
[MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE],
[MKTPACKAGE].[NAME] as [PACKAGENAME],
(case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CODE] else [MKTPACKAGE].[CODE] end) as [PACKAGECODE],
[MKTLIST].[NAME] as [LISTNAME],
(case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSOURCECODEPART].[CODE] else [MKTLIST].[CODE] end) as [LISTCODE]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
left join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID]
left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] or [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
left join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
left join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSOURCECODEPART].[LISTID] = [MKTLIST].[ID]
union all
--Mailing test segments
select
[MKTSEGMENTATIONTESTSEGMENT].[ID],
[MKTSEGMENTATION].[NAME] as [MAILINGNAME],
dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONTESTSEGMENT].[ID], default, default) as [SOURCECODE],
[MKTSEGMENT].[NAME] as [SEGMENTNAME],
[MKTSEGMENTATIONSEGMENT].[CODE] as [SEGMENTCODE],
dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]) as [TESTSEGMENTNAME],
[MKTSEGMENTATIONTESTSEGMENT].[TESTSEGMENTCODE],
[MKTPACKAGE].[NAME] as [PACKAGENAME],
(case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CODE] else [MKTPACKAGE].[CODE] end) as [PACKAGECODE],
[MKTLIST].[NAME] as [LISTNAME],
(case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSOURCECODEPART].[CODE] else [MKTLIST].[CODE] end) as [LISTCODE]
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
left join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID]
left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] or [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
left join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
left join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSOURCECODEPART].[LISTID] = [MKTLIST].[ID]