V_QUERY_DIRECTMARKETINGEFFORTBATCHEXCEPTIONREPORT
View used for output for Direct Marketing Effort Batch Exception Report.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| MAILINGNAME | nvarchar(100) | Marketing effort name | |
| SOURCECODE | nvarchar(50) | Source code | |
| SEGMENTNAME | nvarchar(100) | yes | Segment name |
| SEGMENTCODE | nvarchar(10) | Segment code | |
| TESTSEGMENTNAME | nvarchar(203) | yes | Test segment name |
| TESTSEGMENTCODE | nvarchar(10) | yes | Test segment code |
| PACKAGENAME | nvarchar(100) | yes | Package name |
| PACKAGECODE | nvarchar(10) | Package code | |
| LISTNAME | nvarchar(100) | 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_DIRECTMARKETINGEFFORTBATCHEXCEPTIONREPORT AS
/* Note: [BATCHDIRECTMARKETINGEFFORT].[ID] is the ID from the original batch, not the new exception batch. */
/* Since an exception batch may not have been created, use a left join in the second join (which is joining */
/* on the exception batch table. */
select
[BATCHDIRECTMARKETINGEFFORT].[ID],
[MKTSEGMENTATION].[NAME] as [MAILINGNAME],
[BATCHDIRECTMARKETINGEFFORT].[SOURCECODE],
(case when [MKTSEGMENT].[ID] is not null then [MKTSEGMENT].[NAME] else [BATCHDIRECTMARKETINGEFFORTSEGMENT].[NAME] end) as [SEGMENTNAME],
[BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODE] as [SEGMENTCODE],
(case when [MKTSEGMENTATIONTESTSEGMENT].[ID] is not null then dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]) else [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[NAME] end) as [TESTSEGMENTNAME],
(case when [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[ID] is not null then [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[CODE] else [BATCHDIRECTMARKETINGEFFORT].[SEGMENTTESTCODE] end) as [TESTSEGMENTCODE],
(case when [MKTPACKAGE].[ID] is not null then [MKTPACKAGE].[NAME] else [BATCHDIRECTMARKETINGEFFORTPACKAGE].[NAME] end) as [PACKAGENAME],
[BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODE] as [PACKAGECODE],
(case when [MKTLIST].[ID] is not null then [MKTLIST].[NAME] else [BATCHDIRECTMARKETINGEFFORTLIST].[NAME] end) as [LISTNAME],
[BATCHDIRECTMARKETINGEFFORTLIST].[CODE] as [LISTCODE]
from dbo.[BATCHDIRECTMARKETINGEFFORT]
inner join dbo.[BATCH] as [ORIGINAL] on [ORIGINAL].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHID]
left join dbo.[BATCH] on [BATCH].[ORIGINATINGBATCHID] = [ORIGINAL].[ID]
inner join dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTSEGMENTID]
inner join dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] on [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTPACKAGEID]
left join dbo.[BATCHDIRECTMARKETINGEFFORTLIST] on [BATCHDIRECTMARKETINGEFFORTLIST].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTLISTID]
left join dbo.[BATCHDIRECTMARKETINGEFFORTTESTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [BATCHDIRECTMARKETINGEFFORT].[SEGMENTATIONID]
left join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORTSEGMENT].[MKTSEGMENTID]
left join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[MKTPACKAGEID]
left join dbo.[MKTLIST] on [MKTLIST].[ID] = [BATCHDIRECTMARKETINGEFFORTLIST].[MKTLISTID]
left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[MKTSEGMENTATIONTESTSEGMENTID]