UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM

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

  select @ACCOUNTCODE = SHORTDESCRIPTION from PDACCOUNTSEGMENTVALUE where ID = @ACCOUNTCODEID 
  and PDACCOUNTSTRUCTUREID in (select ID from PDACCOUNTSTRUCTURE where SEGMENTTYPE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) --= '90C72BDA-7994-401B-8A9F-7B36FA65D9FC';

  if @ACCOUNTCODE is not null
   begin  
    select @ACCOUNT = @ACCOUNT + isnull(isnull(VALUE.SHORTDESCRIPTION, VALUE1.SHORTDESCRIPTION) 
            , @ACCOUNTCODE) + 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.PDCOMPOSITESEGMENTMAPPING COMPMAP on COMPMAP.PDACCOUNTSTRUCTUREID = STRUCT.ID
    left outer join dbo.PDACCOUNTSEGMENTVALUE VALUE on MAP.PDACCOUNTSEGMENTVALUEID = VALUE.ID
    left outer join dbo.PDACCOUNTSEGMENTVALUE VALUE1 on COMPMAP.PDACCOUNTSEGMENTVALUEID = VALUE1.ID
    where ((COMPMAP.ID is null) or (COMPMAP.ID not in (select PDCOMPOSITESEGMENTMAPPINGENTRY.PDCOMPOSITESEGMENTMAPPINGID from PDCOMPOSITESEGMENTMAPPINGENTRY where PDCOMPOSITESEGMENTMAPPINGENTRY.ISDEFAULT = 0)))
    and STRUCT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
    group by STRUCT.ID, VALUE.SHORTDESCRIPTION, VALUE1.SHORTDESCRIPTION, STRUCT.SEPARATOR, STRUCT.SEQUENCE, STRUCT.SEPARATORCODE
    order by STRUCT.SEQUENCE asc

    set @ACCOUNT = SUBSTRING(@ACCOUNT, 0, LEN(@ACCOUNT));
   end

  return @ACCOUNT
end