UFN_MKTSOURCECODE_GETCODEFORPART

Returns the code for the specified part from the full source code.

Return

Return Type
nvarchar(10)

Parameters

Parameter	Parameter Type	Mode
@SOURCECODEID	uniqueidentifier	IN
@SOURCECODE	nvarchar(50)	IN
@ITEMTYPECODE	tinyint	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSOURCECODE_GETCODEFORPART]
(
  @SOURCECODEID uniqueidentifier,
  @SOURCECODE nvarchar(50),
  @ITEMTYPECODE tinyint
)
returns nvarchar(10)
as
begin
  declare @START int;
  declare @LENGTH int;

  select
    @START = (select isnull(sum([T].[LENGTH] + [T].[DELIMLENGTH]), 0) + 1
              from (
                select distinct [SCI].[SEQUENCE], [SCI].[LENGTH], len([SCPDV].[DELIM]) as [DELIMLENGTH]
                from dbo.[MKTSOURCECODEITEM] as [SCI]
                inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [SCPDV] on [SCPDV].[MKTSOURCECODEPARTDEFINITIONID] = [SCI].[MKTSOURCECODEPARTDEFINITIONID]
                inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] as [SCVPV] on [SCVPV].[MKTSOURCECODEPARTDEFINITIONVALUESID] = [SCPDV].[ID] and [SCVPV].[MKTSOURCECODEITEMID] = [SCI].[ID]
                where [SCI].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID]
                and [SCI].[SEQUENCE] < [MKTSOURCECODEITEM].[SEQUENCE]
              ) as [T]),
    @LENGTH = [MKTSOURCECODEITEM].[LENGTH]
  from dbo.[MKTSOURCECODEITEM]
  inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
  where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
  and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = @ITEMTYPECODE;

  return substring(@SOURCECODE, @START, @LENGTH);
end