USP_DATALIST_MKTSOURCECODEPARTS
Displays a list of the source code parts for a given marketing effort.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PARENTID | uniqueidentifier | IN | |
| @SOURCECODEITEMID | uniqueidentifier | IN | |
| @MARKETINGPLANITEMID | uniqueidentifier | IN | |
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @CHANNELCODE | tinyint | IN | CHANNELCODE |
| @ACKNOWLEDGEMENTMAILINGTEMPLATEID | uniqueidentifier | IN | |
| @MEMBERSHIPMAILINGTEMPLATEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_DATALIST_MKTSOURCECODEPARTS]
(
@PARENTID uniqueidentifier,
@SOURCECODEITEMID uniqueidentifier = null,
@MARKETINGPLANITEMID uniqueidentifier = null,
@SEGMENTATIONID uniqueidentifier = null,
@CHANNELCODE tinyint = null,
@ACKNOWLEDGEMENTMAILINGTEMPLATEID uniqueidentifier = null,
@MEMBERSHIPMAILINGTEMPLATEID uniqueidentifier = null
)
as
set nocount on;
declare @ACTIVE bit;
if @SEGMENTATIONID is null
set @ACTIVE = 0
else
select @ACTIVE = [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;
if @ACTIVE = 0
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTMARKETINGPLANITEM] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTMARKETINGPLANITEM] where [ID] = @MARKETINGPLANITEMID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0) where [P].[ID] = @MARKETINGPLANITEMID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [P].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [S].[ID] [PARENTID], [S].[CODE] from dbo.[MKTSEGMENTATION] as [S] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0) where [S].[ID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [S].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [S].[ID] [PARENTID], [S].[CODE] from dbo.[MKTSEGMENTATIONSEGMENT] as [S] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1) where [S].[SEGMENTATIONID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [S].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [T].[ID] [PARENTID], [T].[CODE] from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [T] inner join dbo.[MKTSEGMENTATIONSEGMENT] as [S] on [S].[ID] = [T].[SEGMENTID] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1) where [S].[SEGMENTATIONID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [T].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTPACKAGE] as [P] inner join dbo.[MKTSEGMENTATIONPACKAGE] as [S] on [P].[ID] = [S].[PACKAGEID] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID))inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 2) where [S].[SEGMENTATIONID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [P].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTSOURCECODEPART] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on [P].[SOURCECODEITEMID] = [I].[ID] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] where [P].[SEGMENTATIONID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and ([P].[CHANNELCODE] = @CHANNELCODE or @CHANNELCODE is null)
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTSOURCECODEPART] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on [P].[SOURCECODEITEMID] = [I].[ID] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] where [P].[MARKETINGPLANITEMID] = @MARKETINGPLANITEMID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and ([P].[CHANNELCODE] = @CHANNELCODE or @CHANNELCODE is null)
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [M].[ID] [PARENTID], [M].[CODE] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] as [M] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from MKTACKNOWLEDGEMENTMAILINGTEMPLATE where ID = @ACKNOWLEDGEMENTMAILINGTEMPLATEID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0) where [M].[ID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [M].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTSOURCECODEPART] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on [P].[SOURCECODEITEMID] = [I].[ID] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] where [P].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and ([P].[CHANNELCODE] = @CHANNELCODE or @CHANNELCODE is null)
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [M].[ID] [PARENTID], [M].[CODE] from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] as [M] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from MKTMEMBERSHIPMAILINGTEMPLATE where ID = @MEMBERSHIPMAILINGTEMPLATEID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0) where [M].[ID] = @MEMBERSHIPMAILINGTEMPLATEID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [M].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTSOURCECODEPART] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on [P].[SOURCECODEITEMID] = [I].[ID] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] where [P].[MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and ([P].[CHANNELCODE] = @CHANNELCODE or @CHANNELCODE is null);
else
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTMARKETINGPLANITEM] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTMARKETINGPLANITEM] where [ID] = @MARKETINGPLANITEMID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0) where [P].[ID] = @MARKETINGPLANITEMID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [P].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [S].[ID] [PARENTID], [S].[CODE] from dbo.[MKTSEGMENTATION] as [S] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0) where [S].[ID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [S].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [S].[ID] [PARENTID], [S].[CODE] from dbo.[MKTSEGMENTATIONSEGMENT] as [S] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1) where [S].[SEGMENTATIONID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [S].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [T].[ID] [PARENTID], [T].[CODE] from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [T] inner join dbo.[MKTSEGMENTATIONSEGMENT] as [S] on [S].[ID] = [T].[SEGMENTID] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1) where [S].[SEGMENTATIONID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [T].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [S].[CODE] from dbo.[MKTPACKAGE] as [P] inner join dbo.[MKTSEGMENTATIONPACKAGE] as [S] on [P].[ID] = [S].[PACKAGEID] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID))inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 2) where [S].[SEGMENTATIONID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [P].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTSOURCECODEPART] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on [P].[SOURCECODEITEMID] = [I].[ID] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] where [P].[SEGMENTATIONID] = @SEGMENTATIONID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and ([P].[CHANNELCODE] = @CHANNELCODE or @CHANNELCODE is null)
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTSOURCECODEPART] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on [P].[SOURCECODEITEMID] = [I].[ID] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] where [P].[MARKETINGPLANITEMID] = @MARKETINGPLANITEMID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and ([P].[CHANNELCODE] = @CHANNELCODE or @CHANNELCODE is null)
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [M].[ID] [PARENTID], [M].[CODE] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] as [M] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from MKTACKNOWLEDGEMENTMAILINGTEMPLATE where ID = @ACKNOWLEDGEMENTMAILINGTEMPLATEID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0) where [M].[ID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [M].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTSOURCECODEPART] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on [P].[SOURCECODEITEMID] = [I].[ID] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] where [P].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and ([P].[CHANNELCODE] = @CHANNELCODE or @CHANNELCODE is null)
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [M].[ID] [PARENTID], [M].[CODE] from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] as [M] inner join dbo.[MKTSOURCECODEITEM] as [I] on ([I].[SOURCECODEID] = (select [SOURCECODEID] from MKTMEMBERSHIPMAILINGTEMPLATE where ID = @MEMBERSHIPMAILINGTEMPLATEID)) inner join dbo.[MKTSOURCECODEPARTDEFINITION] on ([MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0) where [M].[ID] = @MEMBERSHIPMAILINGTEMPLATEID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and [M].[ID] = @PARENTID
union
select [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], [P].[ID] [PARENTID], [P].[CODE] from dbo.[MKTSOURCECODEPART] as [P] inner join dbo.[MKTSOURCECODEITEM] as [I] on [P].[SOURCECODEITEMID] = [I].[ID] inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [I].[MKTSOURCECODEPARTDEFINITIONID] where [P].[MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID and ([I].[ID] = @SOURCECODEITEMID or @SOURCECODEITEMID is null) and ([P].[CHANNELCODE] = @CHANNELCODE or @CHANNELCODE is null);
return 0;