UFN_GLACCOUNT_VERIFYFENXTACCOUNT

  CREATE function dbo.UFN_GLACCOUNT_VERIFYFENXTACCOUNT(@ACCTSYSID uniqueidentifier, @ACCOUNTALIAS nvarchar(100), @ACCOUNTNUMBER nvarchar(100))
  returns bit
  with execute as caller
  as
  begin
    declare @VALIDFENXTACCOUNT bit;

    declare @PDACCOUNTSTRUCTURE_PROJECTSEGMENT table
      (PDACCOUNTSYSTEMID uniqueidentifier,
      LENGTH tinyint,
      STARTPOS tinyint,
      SEQUENCE integer,
      MAXSEQ integer,
      ISPROJECTSEGMENT tinyint);

    insert into @PDACCOUNTSTRUCTURE_PROJECTSEGMENT
    select distinct
      PDACCOUNTSYSTEM.ID as PDACCOUNTSYSTEMID,
      isnull(PDACCOUNTSTRUCTURE.LENGTH+1,0) as LENGTH,
      isnull((select sum(STRUCT2.LENGTH) + sum(case STRUCT2.SEPARATORCODE when 6 then 0 else 1 end)
              from dbo.PDACCOUNTSTRUCTURE as STRUCT2 where PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = STRUCT2.PDACCOUNTSYSTEMID and PDACCOUNTSTRUCTURE.SEQUENCE > STRUCT2.SEQUENCE),0)+1 as STARTPOS,
      PDACCOUNTSTRUCTURE.SEQUENCE,
      (select max(STRUCT3.SEQUENCE) as MAXSEQ from dbo.PDACCOUNTSTRUCTURE as STRUCT3 where PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = STRUCT3.PDACCOUNTSYSTEMID) as MAXSEQ,
      ISNULL(PDACCOUNTSTRUCTURE.ISPROJECTSEGMENT, 0) as ISPROJECTSEGMENT
      from dbo.PDACCOUNTSYSTEM
      left join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSYSTEM.ID = PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID and PDACCOUNTSTRUCTURE.ISPROJECTSEGMENT = 1;

    if exists (select 1 from dbo.GLACCOUNT
    inner join dbo.FENXTINTEGRATION on @ACCTSYSID = FENXTINTEGRATION.PDACCOUNTSYSTEMID
    inner join @PDACCOUNTSTRUCTURE_PROJECTSEGMENT as V1 on @ACCTSYSID = V1.PDACCOUNTSYSTEMID
    where not exists (select 1 from dbo.FENXTACCOUNT
                      where V1.PDACCOUNTSYSTEMID = FENXTACCOUNT.PDACCOUNTSYSTEMID
                      and isnull(nullif(@ACCOUNTALIAS,''),stuff(@ACCOUNTNUMBER, case when V1.ISPROJECTSEGMENT = 0 then V1.STARTPOS when V1.ISPROJECTSEGMENT = 1 and V1.STARTPOS = 1 then 1 else V1.STARTPOS - 1 end, V1.LENGTH,'')) = FENXTACCOUNT.ACCOUNTNUMBER)
            )
      set @VALIDFENXTACCOUNT = 0;
    else
      set @VALIDFENXTACCOUNT = 1;
  return @VALIDFENXTACCOUNT;
  end