UFN_GLACCOUNT_VERIFYFENXTPROJECTREQUIREDACCOUNT

  CREATE function dbo.UFN_GLACCOUNT_VERIFYFENXTPROJECTREQUIREDACCOUNT(@ACCTSYSID uniqueidentifier, @ACCOUNTALIAS nvarchar(100), @ACCOUNTNUMBER nvarchar(100))
  returns bit
  with execute as caller
  as
  begin
    declare @PROJECTMISSING 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),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
    inner join dbo.FENXTACCOUNT on 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
    where FENXTACCOUNT.PROJECTREQUIRED = 2
    and not exists (select 1 from dbo.PDACCOUNTSTRUCTURE
                    where V1.PDACCOUNTSYSTEMID = PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID
                    and PDACCOUNTSTRUCTURE.ISPROJECTSEGMENT = 1)
            )
            set @PROJECTMISSING = 1;
            else
            set @PROJECTMISSING = 0;

  return @PROJECTMISSING;
  end