V_QUERY_MKTSEGMENTATIONEXPORT
Includes marketing effort fields for use in an export definition.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| MAILING_ID | uniqueidentifier | Marketing effort system record ID | |
| MAILING_IDINTEGER | int | Effort ID | |
| MAILING_NAME | nvarchar(100) | Marketing effort name | |
| MAILING_DESCRIPTION | nvarchar(255) | Marketing effort description | |
| MAILING_CODE | nvarchar(10) | Marketing effort code | |
| MAILING_HOUSEHOLDINGTYPE | nvarchar(50) | yes | Marketing effort universe include type |
| MAILING_HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD | bit | Marketing effort universe include qualifying individuals who are not members of any household | |
| MAILING_HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS | bit | Marketing effort universe include qualifying households which do not have any members | |
| MAILING_HOUSEHOLDINGONERECORDPERHOUSEHOLD | bit | Marketing effort universe send to one person per household | |
| MAILING_ACTIVE | bit | Marketing effort active | |
| MAILING_ACTIVATEDATE | datetime | yes | Marketing effort activate date |
| MAILING_MAILDATE | datetime | yes | Marketing effort mail date |
| MAILING_APPEALSYSTEMID | nvarchar(36) | yes | Marketing effort appeal system ID |
| MAILING_APPEALID | nvarchar(100) | yes | Marketing effort appeal ID |
| MAILING_APPEALDESCRIPTION | nvarchar(255) | yes | Marketing effort appeal description |
| MAILING_SITE | nvarchar(250) | yes | Marketing effort site |
| SEGMENTATION_OVERRIDEBUSINESSUNITS | bit | Marketing effort override business units | |
| SEGMENTATION_BUSINESSUNITS | nvarchar(max) | yes | Marketing effort business units |
| SEGMENT_ID | uniqueidentifier | Segment ID | |
| SEGMENT_NAME | nvarchar(100) | Segment name | |
| SEGMENT_DESCRIPTION | nvarchar(255) | Segment description | |
| SEGMENT_CODE | nvarchar(10) | Segment code | |
| SEGMENT_TESTCODE | nvarchar(10) | Segment test code | |
| SEGMENT_RESPONSERATE | decimal(5, 2) | Segment response rate | |
| SEGMENT_GIFTAMOUNT | money | Segment gift amount | |
| SEGMENT_SAMPLESIZE | int | Segment sample size | |
| SEGMENT_SAMPLESIZETYPE | nvarchar(50) | yes | Segment sample size type |
| SEGMENT_SEQUENCE | int | Segment sequence | |
| SEGMENTATIONSEGMENT_OVERRIDEBUSINESSUNITS | bit | Segment override business units | |
| SEGMENTATIONSEGMENT_BUSINESSUNITS | nvarchar(max) | yes | Segment effort business units |
| SEGMENTPACKAGE_ID | uniqueidentifier | Segment package ID | |
| SEGMENTPACKAGE_NAME | nvarchar(100) | Segment package name | |
| SEGMENTPACKAGE_DESCRIPTION | nvarchar(255) | Segment package description | |
| SEGMENTPACKAGE_CODE | nvarchar(10) | Segment package code | |
| SEGMENTPACKAGE_UNITCOST | money | Segment package cost | |
| SEGMENTPACKAGE_CHANNEL | nvarchar(12) | yes | Segment package channel |
| SEGMENTPACKAGE_CATEGORY | nvarchar(100) | yes | Segment package category |
| TESTSEGMENT_ID | uniqueidentifier | yes | Test segment ID |
| TESTSEGMENT_NAME | nvarchar(100) | yes | Test segment name |
| TESTSEGMENT_DESCRIPTION | nvarchar(255) | yes | Test segment description |
| TESTSEGMENT_CODE | nvarchar(10) | yes | Test segment code |
| TESTSEGMENT_TESTCODE | nvarchar(10) | yes | Test segment test code |
| TESTSEGMENT_RESPONSERATE | decimal(5, 2) | yes | Test segment response rate |
| TESTSEGMENT_GIFTAMOUNT | money | yes | Test segment gift amount |
| TESTSEGMENT_SAMPLESIZE | int | yes | Test segment sample size |
| TESTSEGMENT_SAMPLESIZETYPE | nvarchar(50) | yes | Test segment sample size type |
| TESTSEGMENT_SEQUENCE | int | yes | Test segment sequence |
| TESTSEGMENT_OVERRIDEBUSINESSUNITS | bit | yes | Test segment override business units |
| TESTSEGMENT_BUSINESSUNITS | nvarchar(max) | yes | Test segment business units |
| TESTSEGMENTPACKAGE_ID | uniqueidentifier | yes | Test segment package ID |
| TESTSEGMENTPACKAGE_NAME | nvarchar(100) | yes | Test segment package name |
| TESTSEGMENTPACKAGE_DESCRIPTION | nvarchar(255) | yes | Test segment package description |
| TESTSEGMENTPACKAGE_CODE | nvarchar(10) | yes | Test segment package code |
| TESTSEGMENTPACKAGE_UNITCOST | money | yes | Test segment package cost |
| TESTSEGMENTPACKAGE_CHANNEL | nvarchar(50) | yes | Test Segment package channel |
| TESTSEGMENTPACKAGE_CATEGORY | nvarchar(100) | yes | Test Segment package category |
| FINDERNUMBER | nvarchar(20) | yes | Finder number |
| SOURCECODE | nvarchar(50) | yes | Source code |
| ASKLADDER_ASK1 | money | yes | Ask 1 |
| ASKLADDER_ASK2 | money | yes | Ask 2 |
| ASKLADDER_ASK3 | money | yes | Ask 3 |
| ASKLADDER_ASK4 | money | yes | Ask 4 |
| ASKLADDER_ASK5 | money | yes | Ask 5 |
| ASKLADDER_WRITEINPROMPT | nvarchar(100) | yes | Write in prompt |
| ASKLADDER_ENTRYAMOUNT | money | yes | Entry amount |
| ASKLADDER_NAME | nvarchar(100) | yes | Ask ladder name |
| MAILING_ADDEDBY_USERNAME | nvarchar(128) | yes | Marketing effort added by |
| MAILING_CHANGEDBY_USERNAME | nvarchar(128) | yes | Marketing effort changed by |
| MAILING_DATEADDED | datetime | Marketing effort date added | |
| MAILING_DATECHANGED | datetime | Marketing effort date changed | |
| TESTSEGMENT_ORGANIZATIONGIFTAMOUNT | money | yes | Test segment gift amount (organization currency) |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| SEGMENT_ORGANIZATIONGIFTAMOUNT | money | Segment gift amount (organization currency) | |
| SEGMENTPACKAGE_ORGANIZATIONUNITCOST | money | Segment package cost (organization currency) | |
| TESTSEGMENTPACKAGE_ORGANIZATIONUNITCOST | money | yes | Test segment package cost (organization currency) |
| TESTSEGMENTPACKAGE_CURRENCYEXCHANGERATEID | uniqueidentifier | yes | Test segment package organization currency exchange rate ID |
| SEGMENTPACKAGE_CURRENCYEXCHANGERATEID | uniqueidentifier | yes | Segment package organization currency exchange rate ID |
| SEGMENT_CURRENCYEXCHANGERATEID | uniqueidentifier | yes | Segment organization currency exchange rate ID |
| TESTSEGMENT_CURRENCYEXCHANGERATEID | uniqueidentifier | yes | Test segment organization currency exchange rate ID |
| SITEID | uniqueidentifier | yes | Site ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:14:09 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MKTSEGMENTATIONEXPORT AS
-- note: convert is being used on computed columns on purpose
-- otherwise, the inferred value in INFORMATION_SCHEMA.COLUMNS for the computed column (the length of the longest string returned) can result in
-- "string or binary data will be truncated" errors on export
/* Select all the segment info first, then union it to all the test segment information below. */
select
[MKTSEGMENTATION].[ID] as [MAILING_ID],
[MKTSEGMENTATION].[IDINTEGER] as [MAILING_IDINTEGER],
[MKTSEGMENTATION].[NAME] as [MAILING_NAME],
[MKTSEGMENTATION].[DESCRIPTION] as [MAILING_DESCRIPTION],
[MKTSEGMENTATION].[CODE] as [MAILING_CODE],
convert(nvarchar(50), [MKTSEGMENTATION].[HOUSEHOLDINGTYPE]) as [MAILING_HOUSEHOLDINGTYPE],
[MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD] as [MAILING_HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
[MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS] as [MAILING_HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
[MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD] as [MAILING_HOUSEHOLDINGONERECORDPERHOUSEHOLD],
[MKTSEGMENTATION].[ACTIVE] as [MAILING_ACTIVE],
[MKTSEGMENTATION].[ACTIVATEDATE] as [MAILING_ACTIVATEDATE],
[MKTSEGMENTATION].[MAILDATE] as [MAILING_MAILDATE],
(select [APPEALSYSTEMID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]) as [MAILING_APPEALSYSTEMID],
(select [APPEALID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]) as [MAILING_APPEALID],
(select [APPEALDESCRIPTION] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]) as [MAILING_APPEALDESCRIPTION],
[SITE].[NAME] as [MAILING_SITE],
[MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS] as [SEGMENTATION_OVERRIDEBUSINESSUNITS],
dbo.[UFN_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITDESCRIPTION]([MKTSEGMENTATION].[ID]) as [SEGMENTATION_BUSINESSUNITS],
/* Segment fields */
[MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENT_ID],
[MKTSEGMENT].[NAME] as [SEGMENT_NAME],
[MKTSEGMENT].[DESCRIPTION] as [SEGMENT_DESCRIPTION],
[MKTSEGMENTATIONSEGMENT].[CODE] as [SEGMENT_CODE],
[MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE] as [SEGMENT_TESTCODE],
[MKTSEGMENTATIONSEGMENT].[RESPONSERATE] as [SEGMENT_RESPONSERATE],
[MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] as [SEGMENT_GIFTAMOUNT],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZE] as [SEGMENT_SAMPLESIZE],
convert(nvarchar(50), [MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPE]) as [SEGMENT_SAMPLESIZETYPE],
[MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENT_SEQUENCE],
[MKTSEGMENTATIONSEGMENT].[OVERRIDEBUSINESSUNITS] as [SEGMENTATIONSEGMENT_OVERRIDEBUSINESSUNITS],
dbo.[UFN_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITDESCRIPTION]([MKTSEGMENTATIONSEGMENT].[ID]) as [SEGMENTATIONSEGMENT_BUSINESSUNITS],
/* Segment package fields */
[MKTPACKAGE].[ID] as [SEGMENTPACKAGE_ID],
[MKTPACKAGE].[NAME] as [SEGMENTPACKAGE_NAME],
[MKTPACKAGE].[DESCRIPTION] as [SEGMENTPACKAGE_DESCRIPTION],
[MKTPACKAGE].[CODE] as [SEGMENTPACKAGE_CODE],
[MKTPACKAGE].[UNITCOST] as [SEGMENTPACKAGE_UNITCOST],
[MKTPACKAGE].[CHANNEL] as [SEGMENTPACKAGE_CHANNEL],
[MKTPACKAGECATEGORYCODE].[DESCRIPTION] as [SEGMENTPACKAGE_CATEGORY],
/* Test segment fields */
cast(null as uniqueidentifier) as [TESTSEGMENT_ID],
cast(null as nvarchar(100)) as [TESTSEGMENT_NAME],
cast(null as nvarchar(255)) as [TESTSEGMENT_DESCRIPTION],
cast(null as nvarchar(10)) as [TESTSEGMENT_CODE],
cast(null as nvarchar(10)) as [TESTSEGMENT_TESTCODE],
cast(null as decimal(5,2)) as [TESTSEGMENT_RESPONSERATE],
cast(null as money) as [TESTSEGMENT_GIFTAMOUNT],
cast(null as int) as [TESTSEGMENT_SAMPLESIZE],
cast(null as nvarchar(50)) as [TESTSEGMENT_SAMPLESIZETYPE],
cast(null as int) as [TESTSEGMENT_SEQUENCE],
cast(null as bit) as [TESTSEGMENT_OVERRIDEBUSINESSUNITS],
cast('' as nvarchar(max)) as [TESTSEGMENT_BUSINESSUNITS],
/* Test segment package fields */
cast(null as uniqueidentifier) as [TESTSEGMENTPACKAGE_ID],
cast(null as nvarchar(100)) as [TESTSEGMENTPACKAGE_NAME],
cast(null as nvarchar(255)) as [TESTSEGMENTPACKAGE_DESCRIPTION],
cast(null as nvarchar(10)) as [TESTSEGMENTPACKAGE_CODE],
cast(null as money) as [TESTSEGMENTPACKAGE_UNITCOST],
cast(null as nvarchar(50)) as [TESTSEGMENTPACKAGE_CHANNEL],
cast(null as nvarchar(100)) as [TESTSEGMENTPACKAGE_CATEGORY],
cast(null as nvarchar(20)) as [FINDERNUMBER], /*Placeholder*/
cast(null as nvarchar(50)) as [SOURCECODE], /*Placeholder*/
/* Ask ladder fields */
cast(null as money) as [ASKLADDER_ASK1], /*Placeholder*/
cast(null as money) as [ASKLADDER_ASK2], /*Placeholder*/
cast(null as money) as [ASKLADDER_ASK3], /*Placeholder*/
cast(null as money) as [ASKLADDER_ASK4], /*Placeholder*/
cast(null as money) as [ASKLADDER_ASK5], /*Placeholder*/
cast(null as nvarchar(100)) as [ASKLADDER_WRITEINPROMPT], /*Placeholder*/
cast(null as money) as [ASKLADDER_ENTRYAMOUNT], /*Placeholder*/
cast(null as nvarchar(100)) as [ASKLADDER_NAME], /*Placeholder*/
/* System fields */
[ADDEDBY].[USERNAME] as [MAILING_ADDEDBY_USERNAME],
[CHANGEDBY].[USERNAME] as [MAILING_CHANGEDBY_USERNAME],
[MKTSEGMENTATION].[DATEADDED] as [MAILING_DATEADDED],
[MKTSEGMENTATION].[DATECHANGED] as [MAILING_DATECHANGED],
/* Multicurrency support */
cast(null as money) as [TESTSEGMENT_ORGANIZATIONGIFTAMOUNT],
[MKTSEGMENTATION].[BASECURRENCYID] as [BASECURRENCYID],
[MKTSEGMENTATIONSEGMENT].[ORGANIZATIONGIFTAMOUNT] as [SEGMENT_ORGANIZATIONGIFTAMOUNT],
[MKTPACKAGE].[ORGANIZATIONUNITCOST] as [SEGMENTPACKAGE_ORGANIZATIONUNITCOST],
cast(null as money) as [TESTSEGMENTPACKAGE_ORGANIZATIONUNITCOST],
cast(null as uniqueidentifier) as [TESTSEGMENTPACKAGE_CURRENCYEXCHANGERATEID],
[MKTPACKAGE].[CURRENCYEXCHANGERATEID] as [SEGMENTPACKAGE_CURRENCYEXCHANGERATEID],
[MKTSEGMENTATIONSEGMENT].[CURRENCYEXCHANGERATEID] as [SEGMENT_CURRENCYEXCHANGERATEID],
cast(null as uniqueidentifier) as [TESTSEGMENT_CURRENCYEXCHANGERATEID],
[SITE].[ID] as SITEID
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
left outer join dbo.[MKTPACKAGECATEGORYCODE] on [MKTPACKAGECATEGORYCODE].[ID] = [MKTPACKAGE].[PACKAGECATEGORYCODEID]
left join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEGMENTATION].[ADDEDBYID]
left join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEGMENTATION].[CHANGEDBYID]
left join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENTATION].[SITEID]
union all
select
[MKTSEGMENTATION].[ID] as [MAILING_ID],
[MKTSEGMENTATION].[IDINTEGER] as [MAILING_IDINTEGER],
[MKTSEGMENTATION].[NAME] as [MAILING_NAME],
[MKTSEGMENTATION].[DESCRIPTION] as [MAILING_DESCRIPTION],
[MKTSEGMENTATION].[CODE] as [MAILING_CODE],
convert(nvarchar(50), [MKTSEGMENTATION].[HOUSEHOLDINGTYPE]) as [MAILING_HOUSEHOLDINGTYPE],
[MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD] as [MAILING_HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
[MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS] as [MAILING_HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
[MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD] as [MAILING_HOUSEHOLDINGONERECORDPERHOUSEHOLD],
[MKTSEGMENTATION].[ACTIVE] as [MAILING_ACTIVE],
[MKTSEGMENTATION].[ACTIVATEDATE] as [MAILING_ACTIVATEDATE],
[MKTSEGMENTATION].[MAILDATE] as [MAILING_MAILDATE],
(select [APPEALSYSTEMID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]) as [MAILING_APPEALSYSTEMID],
(select [APPEALID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]) as [MAILING_APPEALID],
(select [APPEALDESCRIPTION] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]) as [MAILING_APPEALDESCRIPTION],
[SITE].[NAME] as [MAILING_SITE],
[MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS] as [SEGMENTATION_OVERRIDEBUSINESSUNITS],
dbo.[UFN_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITDESCRIPTION]([MKTSEGMENTATION].[ID]) as [SEGMENTATION_BUSINESSUNITS],
/* Segment fields */
[SEG].[ID] as [SEGMENT_ID],
[MKTSEGMENT].[NAME] as [SEGMENT_NAME],
[MKTSEGMENT].[DESCRIPTION] as [SEGMENT_DESCRIPTION],
[SEG].[CODE] as [SEGMENT_CODE],
[SEG].[TESTSEGMENTCODE] as [SEGMENT_TESTCODE],
[SEG].[RESPONSERATE] as [SEGMENT_RESPONSERATE],
[SEG].[GIFTAMOUNT] as [SEGMENT_GIFTAMOUNT],
[SEG].[SAMPLESIZE] as [SEGMENT_SAMPLESIZE],
convert(nvarchar(50), [SEG].[SAMPLESIZETYPE]) as [SEGMENT_SAMPLESIZETYPE],
[SEG].[SEQUENCE] as [SEGMENT_SEQUENCE],
[SEG].[OVERRIDEBUSINESSUNITS] as [SEGMENTATIONSEGMENT_OVERRIDEBUSINESSUNITS],
dbo.[UFN_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITDESCRIPTION]([SEG].[ID]) as [SEGMENTATIONSEGMENT_BUSINESSUNITS],
/* Segment package fields */
[SEGPAK].[ID] as [SEGMENTPACKAGE_ID],
[SEGPAK].[NAME] as [SEGMENTPACKAGE_NAME],
[SEGPAK].[DESCRIPTION] as [SEGMENTPACKAGE_DESCRIPTION],
[SEGPAK].[CODE] as [SEGMENTPACKAGE_CODE],
[SEGPAK].[UNITCOST] as [SEGMENTPACKAGE_UNITCOST],
[SEGPAK].[CHANNEL] as [SEGMENTPACKAGE_CHANNEL],
[SEGPCC].[DESCRIPTION] as [SEGMENTPACKAGE_CATEGORY],
/* Test segment fields */
[TESTSEG].[ID] as [TESTSEGMENT_ID],
[TESTSEG].[NAME] as [TESTSEGMENT_NAME],
[TESTSEG].[DESCRIPTION] as [TESTSEGMENT_DESCRIPTION],
[TESTSEG].[CODE] as [TESTSEGMENT_CODE],
[TESTSEG].[TESTSEGMENTCODE] as [TESTSEGMENT_TESTCODE],
[TESTSEG].[RESPONSERATE] as [TESTSEGMENT_RESPONSERATE],
[TESTSEG].[GIFTAMOUNT] as [TESTSEGMENT_GIFTAMOUNT],
[TESTSEG].[SAMPLESIZE] as [TESTSEGMENT_SAMPLESIZE],
convert(nvarchar(50), [TESTSEG].[SAMPLESIZETYPE]) as [TESTSEGMENT_SAMPLESIZETYPE],
[TESTSEG].[SEQUENCE] as [TESTSEGMENT_SEQUENCE],
[TESTSEG].[OVERRIDEBUSINESSUNITS] as [TESTSEGMENT_OVERRIDEBUSINESSUNITS],
dbo.[UFN_MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT_GETBUSINESSUNITDESCRIPTION]([TESTSEG].[ID]) as [TESTSEGMENT_BUSINESSUNITS],
/* Test segment package fields */
[TESTSEGPAK].[ID] as [TESTSEGMENTPACKAGE_ID],
[TESTSEGPAK].[NAME] as [TESTSEGMENTPACKAGE_NAME],
[TESTSEGPAK].[DESCRIPTION] as [TESTSEGMENTPACKAGE_DESCRIPTION],
[TESTSEGPAK].[CODE] as [TESTSEGMENTPACKAGE_CODE],
[TESTSEGPAK].[UNITCOST] as [TESTSEGMENTPACKAGE_UNITCOST],
[TESTSEGPAK].[CHANNEL] as [TESTSEGMENTPACKAGE_CHANNEL],
[TESTSEGPCC].[DESCRIPTION] as [TESTSEGMENTPACKAGE_CATEGORY],
cast(null as nvarchar(20)) as [FINDERNUMBER], /*Placeholder*/
cast(null as nvarchar(50)) as [SOURCECODE], /*Placeholder*/
/* Ask ladder fields */
cast(null as money) as [ASKLADDER_ASK1], /*Placeholder*/
cast(null as money) as [ASKLADDER_ASK2], /*Placeholder*/
cast(null as money) as [ASKLADDER_ASK3], /*Placeholder*/
cast(null as money) as [ASKLADDER_ASK4], /*Placeholder*/
cast(null as money) as [ASKLADDER_ASK5], /*Placeholder*/
cast(null as nvarchar(100)) as [ASKLADDER_WRITEINPROMPT], /*Placeholder*/
cast(null as money) as [ASKLADDER_ENTRYAMOUNT], /*Placeholder*/
cast(null as nvarchar(100)) as [ASKLADDER_NAME], /*Placeholder*/
/* System fields */
[ADDEDBY].[USERNAME] as [MAILING_ADDEDBY_USERNAME],
[CHANGEDBY].[USERNAME] as [MAILING_CHANGEDBY_USERNAME],
[MKTSEGMENTATION].[DATEADDED] as [MAILING_DATEADDED],
[MKTSEGMENTATION].[DATECHANGED] as [MAILING_DATECHANGED],
/* Multicurrency support */
[TESTSEG].[ORGANIZATIONGIFTAMOUNT] as [TESTSEGMENT_ORGANIZATIONGIFTAMOUNT],
[MKTSEGMENTATION].[BASECURRENCYID] as [BASECURRENCYID],
[SEG].[ORGANIZATIONGIFTAMOUNT] as [SEGMENT_ORGANIZATIONGIFTAMOUNT],
[SEGPAK].[ORGANIZATIONUNITCOST] as [SEGMENTPACKAGE_ORGANIZATIONUNITCOST],
[TESTSEGPAK].[ORGANIZATIONUNITCOST] as [TESTSEGMENTPACKAGE_ORGANIZATIONUNITCOST],
[TESTSEGPAK].[CURRENCYEXCHANGERATEID] as [TESTSEGMENTPACKAGE_CURRENCYEXCHANGERATEID],
[SEGPAK].[CURRENCYEXCHANGERATEID] as [SEGMENTPACKAGE_CURRENCYEXCHANGERATEID],
[SEG].[CURRENCYEXCHANGERATEID] as [SEGMENT_CURRENCYEXCHANGERATEID],
[TESTSEG].[ORGANIZATIONCURRENCYEXCHANGERATEID] as [TESTSEGMENT_CURRENCYEXCHANGERATEID],
[SITE].[ID] as SITEID
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] as [SEG] on [SEG].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [SEG].[SEGMENTID]
inner join dbo.[MKTPACKAGE] as [SEGPAK] on [SEGPAK].[ID] = [SEG].[PACKAGEID]
left outer join dbo.[MKTPACKAGECATEGORYCODE] as [SEGPCC] on [SEGPCC].[ID] = [SEGPAK].[PACKAGECATEGORYCODEID]
inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] as [TESTSEG] on [TESTSEG].[SEGMENTID] = [SEG].[ID]
left join dbo.[MKTPACKAGE] as [TESTSEGPAK] on [TESTSEGPAK].[ID] = [TESTSEG].[PACKAGEID]
left outer join dbo.[MKTPACKAGECATEGORYCODE] as [TESTSEGPCC] on [TESTSEGPCC].[ID] = [TESTSEGPAK].[PACKAGECATEGORYCODEID]
left join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEGMENTATION].[ADDEDBYID]
left join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEGMENTATION].[CHANGEDBYID]
left join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENTATION].[SITEID];