UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM

CREATE function dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM
(
  @GLACCOUNTID uniqueidentifier,
  @ACCOUNTCODE nvarchar(30),
  @PDACCOUNTSYSTEMID uniqueidentifier
)
returns nvarchar(100)
with execute as caller
as begin
  declare @ACCOUNT nvarchar(100) = ''

    if @GLACCOUNTID is not null
        select @ACCOUNT = @ACCOUNT + CASE WHEN VALUE.PDACCOUNTSTRUCTUREID in 
                (select ID from PDACCOUNTSTRUCTURE where SEGMENTTYPE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
                            THEN @ACCOUNTCODE
                            ELSE VALUE.SHORTDESCRIPTION END + --SUBSTRING(STRUCT.SEPARATOR, 0, 2)

                            case STRUCT.SEPARATORCODE when 6 then '' else SUBSTRING(STRUCT.SEPARATOR, 0, 2) end
        from dbo.PDACCOUNTSEGMENT SEG
        inner join dbo.PDACCOUNTSTRUCTURE STRUCT on SEG.PDACCOUNTSTRUCTUREID = STRUCT.ID and STRUCT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
        inner join dbo.PDACCOUNTSEGMENTVALUE VALUE on SEG.PDACCOUNTSEGMENTVALUEID = VALUE.ID
        where SEG.GLACCOUNTID = @GLACCOUNTID
        order by STRUCT.SEQUENCE asc;
  else
        --select @ACCOUNT = @ACCOUNT + ISNULL(CASE WHEN VALUE.PDACCOUNTSTRUCTUREID = '90C72BDA-7994-401B-8A9F-7B36FA65D9FC' 

        select @ACCOUNT = @ACCOUNT + ISNULL(CASE WHEN VALUE.PDACCOUNTSTRUCTUREID in 
                (select ID from PDACCOUNTSTRUCTURE where SEGMENTTYPE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        THEN NULL
           ELSE VALUE.SHORTDESCRIPTION END, @ACCOUNTCODE) + --SUBSTRING(STRUCT.SEPARATOR, 0, 2)

           case STRUCT.SEPARATORCODE when 6 then '' else SUBSTRING(STRUCT.SEPARATOR, 0, 2) end
    from dbo.PDACCOUNTSTRUCTURE STRUCT 
    left outer join dbo.PDACCOUNTSEGMENTMAPPING MAP on STRUCT.ID = MAP.PDACCOUNTSTRUCTUREID and MAP.ISDEFAULT = 1
    left outer join dbo.PDACCOUNTSEGMENTVALUE VALUE on MAP.PDACCOUNTSEGMENTVALUEID = VALUE.ID
    where STRUCT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID

    order by STRUCT.SEQUENCE asc;

    if (select top 1 SEPARATORCODE from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID order by SEQUENCE desc) != 6
        set @ACCOUNT = SUBSTRING(@ACCOUNT, 0, LEN(@ACCOUNT));

    return @ACCOUNT;
end