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
        set @ACCOUNT = isnull((
            select
                case when VALUE.PDACCOUNTSTRUCTUREID in
                        (select ID from PDACCOUNTSTRUCTURE where SEGMENTTYPE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
                            then @ACCOUNTCODE
                            else VALUE.SHORTDESCRIPTION end
                + case STRUCT.SEPARATORCODE when 6 then '' else SUBSTRING(STRUCT.SEPARATOR, 0, 2) end as [text()]
            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
            for xml path(''), type
        ).value('.', 'nvarchar(100)'), '');
  else
        set @ACCOUNT = isnull((
            select
                isnull(case when VALUE.PDACCOUNTSTRUCTUREID in
                        (select ID from PDACCOUNTSTRUCTURE where SEGMENTTYPE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
                            then NULL
                            else VALUE.SHORTDESCRIPTION end, @ACCOUNTCODE)
                + case STRUCT.SEPARATORCODE when 6 then '' else SUBSTRING(STRUCT.SEPARATOR, 0, 2) end as [text()]
            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
            for xml path(''), type
        ).value('.', 'nvarchar(100)'), '');

    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