USP_FENXT_SYNCHRONIZE_ACCOUNTS

CREATE procedure dbo.USP_FENXT_SYNCHRONIZE_ACCOUNTS
    (
    @PDACCOUNTSYSTEMID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @LASTUPDATETIME datetime = '17530101',
    @ROWSAFFECTED int output
    )
    as
        set @ROWSAFFECTED = 0;

        if @CHANGEAGENTID is null
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

        --Accounts that are currently not active that had their PREVENTPOSTINGDATE removed

        update dbo.GLACCOUNT
            set ACTIVE = 1,
             CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = getdate()
        from dbo.GLACCOUNT
        inner join 
            (select
                distinct PDACCOUNTSYSTEM.ID as PDACCOUNTSYSTEMID,
                isnull(PDACCOUNTSTRUCTURE.LENGTH+1,0) as LENGTH,
                isnull((select sum(LENGTH) + sum(case 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,
                SEQUENCE,
                (select max(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) as V1
            on GLACCOUNT.PDACCOUNTSYSTEMID = V1.PDACCOUNTSYSTEMID
        inner join dbo.FENXTACCOUNT
            on isnull(nullif(GLACCOUNT.ACCOUNTALIAS,''),stuff(GLACCOUNT.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 dbo.FENXTACCOUNT.DATEMODIFIED > @LASTUPDATETIME
            and FENXTACCOUNT.PREVENTPOSTINGDATE is null
            and GLACCOUNT.ACTIVE = 0
            and GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

        select @ROWSAFFECTED += @@ROWCOUNT;

        --Deactivate active accounts with PREVENTPOSTINGDATE <= today

        update dbo.GLACCOUNT
            set ACTIVE = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = getdate()
        from dbo.GLACCOUNT
        inner join 
            (select
                distinct PDACCOUNTSYSTEM.ID as PDACCOUNTSYSTEMID,
                isnull(PDACCOUNTSTRUCTURE.LENGTH+1,0) as LENGTH,
                isnull((select sum(LENGTH) + sum(case 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,
                SEQUENCE,
                (select max(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) as V1
        on GLACCOUNT.PDACCOUNTSYSTEMID = V1.PDACCOUNTSYSTEMID
        inner join FENXTACCOUNT on isnull(nullif(GLACCOUNT.ACCOUNTALIAS,''),stuff(GLACCOUNT.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.PREVENTPOSTINGDATE < dateadd(d,1,convert(date,getdate()))
            and GLACCOUNT.ACTIVE = 1
            and GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

        select @ROWSAFFECTED += @@ROWCOUNT;