UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE

CREATE function dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE]
(
  @SOURCECODEID uniqueidentifier = null,
  @CODE nvarchar(10),
  @ITEMTYPECODE tinyint,
  @SOURCECODEPARTDEFINITIONID uniqueidentifier = null   /* Required only when @ITEMTYPECODE = 5 (user-defined). */
)
returns uniqueidentifier
as
begin
  declare @RETURNID uniqueidentifier;
  declare @CODEVALUEID uniqueidentifier;
  declare @VALID bit = 0;
  declare @LENGTH tinyint = len(@CODE);
  declare @FORMAT nvarchar(10);
  declare @CODECHAR char;
  declare @FORMATCHAR char;
  declare @POS integer;
  declare @CODEMATCHES bit;

  --Set the source code part definition ID if not already set.

  if @SOURCECODEPARTDEFINITIONID is null and @ITEMTYPECODE <> 5
    begin
      select
        @SOURCECODEPARTDEFINITIONID = [ID]
      from dbo.[MKTSOURCECODEPARTDEFINITION]
      where [ITEMTYPECODE] = @ITEMTYPECODE;
    end

  --Loop through each value for the part and make sure we have a valid value.  If the code part contains a mix of literals and wildcards,

  --then we start matching with the most restrictive to the least restrictive.  Count up the number of literals, #'s, @?s, and ?'s.  Then

  --sort by the number of literals, #'s, @?s, ?'s, rank, and alpha.

  declare VALUECURSOR cursor local fast_forward for
    with [TEMP0] ([ID], [FORMAT], [NUMLITERALS], [NUMDIGITS], [NUMALPHAS], [NUMANYCHARS]) as
    (
      select distinct
        [MKTSOURCECODEPARTDEFINITIONVALUES].[ID],
        [MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT],
        len([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]) - ((len([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]) - len(replace([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT], '#', ''))) + (len([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]) - len(replace([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT], '@', ''))) + (len([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]) - len(replace([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT], '?', '')))) as [NUMLITERALS],
        len([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]) - len(replace([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT], '#', '')) as [NUMDIGITS],
        len([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]) - len(replace([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT], '@', '')) as [NUMALPHAS],
        len([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]) - len(replace([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT], '?', '')) as [NUMANYCHARS]
      from dbo.[MKTSOURCECODEPARTDEFINITION]
      inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID]
      left join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID] = [MKTSOURCECODEPARTDEFINITIONVALUES].[ID]
      left join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID]
      where (@SOURCECODEID is null or [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID or not exists(select 1 from dbo.[MKTSOURCECODEITEM] as [SCI] inner join dbo.[MKTSOURCECODEPARTDEFINITION] as [SCPD] on [SCPD].[ID] = [SCI].[MKTSOURCECODEPARTDEFINITIONID] where [SCI].[SOURCECODEID] = @SOURCECODEID and [SCPD].[ITEMTYPECODE] = @ITEMTYPECODE))
      and (@SOURCECODEPARTDEFINITIONID is null or [MKTSOURCECODEPARTDEFINITION].[ID] = @SOURCECODEPARTDEFINITIONID)
      and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = @ITEMTYPECODE
      and len([MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]) = @LENGTH
    ),
    [TEMP1] ([ID], [FORMAT], [NUMLITERALS], [NUMDIGITS], [NUMALPHAS], [NUMANYCHARS], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8], [C9], [C0]) as
    (
      --Split up the format string so we can rank the special format characters (#, @, ?) to get the correct sorting...

      select [ID], [FORMAT], [NUMLITERALS], [NUMDIGITS], [NUMALPHAS], [NUMANYCHARS],
        substring([FORMAT], 1, 1),
        substring([FORMAT], 2, 1),
      substring([FORMAT], 3, 1),
        substring([FORMAT], 4, 1),
        substring([FORMAT], 5, 1),
        substring([FORMAT], 6, 1),
        substring([FORMAT], 7, 1),
        substring([FORMAT], 8, 1),
        substring([FORMAT], 9, 1),
        substring([FORMAT], 10, 1)
      from [TEMP0]
    ),
    [TEMP2] ([ID], [FORMAT], [NUMLITERALS], [NUMDIGITS], [NUMALPHAS], [NUMANYCHARS], [R1], [R2], [R3], [R4], [R5], [R6], [R7], [R8], [R9], [R0]) as
    (
      --Rank each character so the special characters sort correctly...

      select [ID], [FORMAT], [NUMLITERALS], [NUMDIGITS], [NUMALPHAS], [NUMANYCHARS],
        (case [C1] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C2] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C3] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C4] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C5] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C6] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C7] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C8] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C9] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end),
        (case [C0] when '#' then 2 when '@' then 3 when '?' then 4 else 1 end)
      from [TEMP1]
    )
    select
      [ID],
      [FORMAT]
    from [TEMP2]
    order by
      [NUMLITERALS] desc, [NUMDIGITS] desc, [NUMALPHAS] desc, [NUMANYCHARS] desc,
      [R1] asc, [R2] asc, [R3] asc, [R4] asc, [R5] asc, [R6] asc, [R7] asc, [R8] asc, [R9] asc, [R0] asc,
      [FORMAT] asc;

  open VALUECURSOR;
  fetch next from VALUECURSOR into @CODEVALUEID, @FORMAT;

  while (@@FETCH_STATUS = 0 and @VALID = 0)
  begin
    --Parse each code and format to see if code conforms to the source code format...

    set @CODEMATCHES = 1;
    set @POS = 1;
    set @CODECHAR = substring(@CODE, @POS, 1);
    set @FORMATCHAR = substring(@FORMAT, @POS, 1);

    while (@POS <= @LENGTH and @CODEMATCHES = 1)
    begin
      --Compare code and format characters...

      if @FORMATCHAR = '#' -- number

        --If code character is numeric and format is # then code matches...

        set @CODEMATCHES = (case when patindex('[0-9]', @CODECHAR) > 0 then 1 else 0 end);
      else if @FORMATCHAR = '@' -- alpha

        --If code character is not numeric and format is @ then code matches...

        set @CODEMATCHES = (case when patindex('[A-Za-z]', @CODECHAR) > 0 then 1 else 0 end);
      else if @FORMATCHAR = '?' -- number or alpha character

        set @CODEMATCHES = (case when patindex('[0-9A-Za-z]', @CODECHAR) > 0 then 1 else 0 end);
      else  -- literal

        --Format character is a literal character, so code only matches if it is equal to the format character...

        set @CODEMATCHES = (case when @CODECHAR = @FORMATCHAR then 1 else 0 end);

      set @POS += 1;
      set @CODECHAR = substring(@CODE, @POS, 1);
      set @FORMATCHAR = substring(@FORMAT, @POS, 1);
    end

    set @VALID = @CODEMATCHES;
    set @RETURNID = @CODEVALUEID;

    fetch next from VALUECURSOR into @CODEVALUEID, @FORMAT;
  end

  close VALUECURSOR;
  deallocate VALUECURSOR;

  if @VALID = 0
    set @RETURNID = null;

  return @RETURNID;
end