USP_FENXT_SYNCHRONIZE_FISCALYEARS

      CREATE procedure dbo.USP_FENXT_SYNCHRONIZE_FISCALYEARS
        (
        @PDACCOUNTSYSTEMID uniqueidentifier,
        @CHANGEAGENTID uniqueidentifier = null,
        @ROWSAFFECTED int OUTPUT,
        @BUSINESSPROCESSSTATUSID uniqueidentifier = null
        )
        as

        set @ROWSAFFECTED = 0;

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

        declare @CURRENTDATE datetime;
        set @CURRENTDATE = getdate();


          begin try
            begin transaction FYSYNC

            declare @VALIDFISCALYEARS bit;
            set @VALIDFISCALYEARS=1;

            --Verify that all linked fiscal years are still present in Financial Edge NXT

            declare @MISSMATCHEDEXISTINGFISCALYEARS bit;
            set @MISSMATCHEDEXISTINGFISCALYEARS=0;

            if exists 
            (
               select 1
               from dbo.GLFISCALYEAR
                 left join dbo.FENXTFISCALYEAR on GLFISCALYEAR.ID = FENXTFISCALYEAR.GLFISCALYEARID
                 left join dbo.FENXTFISCALYEARSTAGING on FENXTFISCALYEAR.FENXTFISCALYEARID = FENXTFISCALYEARSTAGING.FENXTFISCALYEARID
               where FENXTFISCALYEAR.FENXTFISCALYEARID is null
                 or FENXTFISCALYEARSTAGING.ID is null
             )
               set @MISSMATCHEDEXISTINGFISCALYEARS=1;

             if @MISSMATCHEDEXISTINGFISCALYEARS=1
               raiserror ('FENXTMISMATCHEDEXISTINGFISCALYEARS', 16, 2) with seterror;

             update dbo.FENXTFISCALYEARSTAGING
              set 
                GLFISCALYEARID = isnull(FENXTFISCALYEAR.GLFISCALYEARID, newid()),
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
             from dbo.FENXTFISCALYEARSTAGING
                 left join dbo.FENXTFISCALYEAR on FENXTFISCALYEARSTAGING.FENXTFISCALYEARID = FENXTFISCALYEAR.FENXTFISCALYEARID;

             update dbo.FENXTFISCALYEARSTAGING
              set 
                FENXTFISCALYEARSTAGING.PREVIOUSGLFISCALYEARID = FY2.GLFISCALYEARID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
             from dbo.FENXTFISCALYEARSTAGING
               inner join dbo.FENXTFISCALYEARSTAGING as FY2 on dateadd(d, -1, dbo.FENXTFISCALYEARSTAGING.STARTDATE) = FY2.ENDDATE;

             --Validate that the staged fiscal years are contiguous.  

             --Check for duplicated sequence in the FENXT Fiscal years

             if (select count(YEARSEQUENCE) from dbo.FENXTFISCALYEARSTAGING) <> (select count(distinct YEARSEQUENCE) from dbo.FENXTFISCALYEARSTAGING)
               set @VALIDFISCALYEARS=0;

             --Only the first fiscal year should not have a previous fiscal year

             if exists 
             (
               select 1 
               from dbo.FENXTFISCALYEARSTAGING 
               where (FENXTFISCALYEARSTAGING.YEARSEQUENCE>1)
                 and FENXTFISCALYEARSTAGING.PREVIOUSGLFISCALYEARID is null
             )
               set @VALIDFISCALYEARS=0 ;

            --Get the Financial Edge NXT fiscal year id for the GL fiscal year id with sequence 1

            declare @NEWPRECEDINGFISCALYEARS integer;
            set @NEWPRECEDINGFISCALYEARS=0;

            select @NEWPRECEDINGFISCALYEARS=(FENXTFISCALYEARSTAGING.YEARSEQUENCE-1)
            from dbo.FENXTFISCALYEARSTAGING
              inner join dbo.FENXTFISCALYEAR on FENXTFISCALYEARSTAGING.FENXTFISCALYEARID=FENXTFISCALYEAR.FENXTFISCALYEARID
              inner join dbo.GLFISCALYEAR on FENXTFISCALYEAR.GLFISCALYEARID=GLFISCALYEAR.ID
            where dbo.GLFISCALYEAR.YEARSEQUENCE=1;

            --check that the there are no extra fiscal years that overlap with the existing ones     

            if exists
            (
         select FENXTFISCALYEAR.FENXTFISCALYEARID
              from dbo.FENXTFISCALYEAR
                inner join dbo.FENXTFISCALYEARSTAGING on FENXTFISCALYEAR.FENXTFISCALYEARID=FENXTFISCALYEARSTAGING.FENXTFISCALYEARID
                inner join dbo.GLFISCALYEAR on GLFISCALYEAR.ID=FENXTFISCALYEAR.GLFISCALYEARID
              where GLFISCALYEAR.YEARSEQUENCE<>(FENXTFISCALYEARSTAGING.YEARSEQUENCE-@NEWPRECEDINGFISCALYEARS)
            )
              set @VALIDFISCALYEARS=0 ;

            if @VALIDFISCALYEARS=0
              raiserror ('FENXTVALIDFISCALYEARS', 16, 2) with seterror;

            merge into dbo.GLFISCALYEAR
            using 
            (
              select
                FENXTFISCALYEARSTAGING.GLFISCALYEARID,
                FENXTFISCALYEARSTAGING.YEARID,
                FENXTFISCALYEARSTAGING.DESCRIPTION,
                FENXTFISCALYEARSTAGING.NUMBEROFFISCALPERIODS,
                FENXTFISCALYEARSTAGING.STATUS,
                FENXTFISCALYEARSTAGING.YEARSEQUENCE
              from dbo.FENXTFISCALYEARSTAGING
            ) as source
            on GLFISCALYEAR.ID = Source.GLFISCALYEARID
            when not matched then
              insert
              (
                ID,
                YEARID,
                DESCRIPTION,
                STATUSCODE,
                FISCALPERIODSINYEAR,
                YEARSEQUENCE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
              )
            values
            (
              source.GLFISCALYEARID,
              source.YEARID,
              source.DESCRIPTION,
              case source.STATUS when 'Purged' then 4 when 'Closed' then 3 else 1 end,
              source.NUMBEROFFISCALPERIODS,
              source.YEARSEQUENCE,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            )
            when matched and GLFISCALYEAR.YEARSEQUENCE != source.YEARSEQUENCE then
              update
              set 
                YEARSEQUENCE=source.YEARSEQUENCE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE;

            select @ROWSAFFECTED += @@ROWCOUNT;

            --Now update the previousGlFiscalYear

            --As we cannot determine the order the rows are added in the merge statement

            --this can't be done in the merge statement as can get a foreign key error

            --if the row for the previous year does not exist yet.

            update dbo.GLFISCALYEAR
            set GLFISCALYEAR.PREVIOUSYEARID = FENXTFISCALYEARSTAGING.PREVIOUSGLFISCALYEARID
              from dbo.GLFISCALYEAR
              inner join dbo.FENXTFISCALYEARSTAGING on FENXTFISCALYEARSTAGING.GLFISCALYEARID=GLFISCALYEAR.ID;

            insert into dbo.FENXTFISCALYEAR
            (
              ID,
              GLFISCALYEARID,
              FENXTFISCALYEARID,
              ADDEDBYID,
              CHANGEDBYID,
              DATEADDED,
              DATECHANGED
            )
            select
              newid(),
              FENXTFISCALYEARSTAGING.GLFISCALYEARID,
              FENXTFISCALYEARSTAGING.FENXTFISCALYEARID,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            from dbo.FENXTFISCALYEARSTAGING
            where not exists 
              (select 1 from dbo.FENXTFISCALYEAR where FENXTFISCALYEAR.GLFISCALYEARID = FENXTFISCALYEARSTAGING.GLFISCALYEARID);

            exec dbo.USP_FENXT_ADDFENXTFISCALPERIODS @CHANGEAGENTID, @ROWSAFFECTED output;
            commit transaction FYSYNC;
            end try

          begin catch
            -- if there is an error in the fiscal years none of them should be added to CRM.

            If (@@TRANCOUNT > 0)
            begin
            rollback transaction FYSYNC;
            end

           -- catch known errors.

            declare @ERRMESSAGE varchar(MAX) = ERROR_MESSAGE();
            if @ERRMESSAGE like '%UC_GLFISCALYEAR_DESCRIPTION%'
            begin 
            set @ERRMESSAGE = 'UC_GLFISCALYEAR_DESCRIPTION'
            end
            else
            begin
            set @ERRMESSAGE = ERROR_MESSAGE();
            end
            -- insert into the sync process exceptions table

            insert into dbo.FENXTSYNCPROCESSEXCEPTIONS(
            PDACCOUNTSYSTEMID,
            BUSINESSPROCESSSTATUSID,
            SYNCOPERATIONTYPECODE,
            RAISERRORID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED)
          values(
            @PDACCOUNTSYSTEMID,
            @BUSINESSPROCESSSTATUSID,
            3,
            @ERRMESSAGE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE);

            set @ROWSAFFECTED=0;
          end catch

        --Make sure that the fiscal year status is updated for linked fiscal years.

        update dbo.GLFISCALYEAR 
        set 
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE,
          STATUSCODE =
            (case FENXTFISCALYEARSTAGING.STATUS when 'Purged' then 4 when 'Closed' then 3 else 1 end) 
        from dbo.FENXTFISCALYEARSTAGING 
          inner join dbo.FENXTFISCALYEAR on FENXTFISCALYEAR.FENXTFISCALYEARID=FENXTFISCALYEARSTAGING.FENXTFISCALYEARID
          inner join dbo.GLFISCALYEAR on GLFISCALYEAR.ID=FENXTFISCALYEAR.GLFISCALYEARID
        where GLFISCALYEAR.STATUSCODE!=(case FENXTFISCALYEARSTAGING.STATUS when 'Purged' then 4 when 'Closed' then 3 else 1 end);

        select @ROWSAFFECTED += @@ROWCOUNT;

        create table #FINANCIALEDGENXTFISCALPERIOD
        (
          GLFISCALPERIODID uniqueidentifier,
          CLOSED bit
        );

        insert into #FINANCIALEDGENXTFISCALPERIOD
        (
          GLFISCALPERIODID,
          CLOSED
        )
        select
          FENXTFISCALPERIOD.GLFISCALPERIODID,
          T.c.value('(CLOSED)[1]','bit') as 'CLOSED'
        from FENXTFISCALYEARSTAGING
          cross apply FiscalPeriods.nodes('/FISCALYEARPERIODS/ITEM') T(c)
          inner join dbo.FENXTFISCALYEAR on FENXTFISCALYEARSTAGING.FENXTFISCALYEARID = FENXTFISCALYEAR.FENXTFISCALYEARID
          inner join dbo.FENXTFISCALPERIOD on T.c.value('(FENXTID)[1]','int') = FENXTFISCALPERIOD.FENXTFISCALPERIODID;

        --If the gl fiscal year is closed make sure that all associated fiscal periods are closed.

        update dbo.#FINANCIALEDGENXTFISCALPERIOD set CLOSED=1 
          where #FINANCIALEDGENXTFISCALPERIOD.GLFISCALPERIODID in
          (
            select GLFISCALPERIOD.ID 
            from dbo.GLFISCALPERIOD
              inner join GLFISCALYEAR on GLFISCALYEAR.ID=GLFISCALPERIOD.GLFISCALYEARID
            where GLFISCALYEAR.STATUSCODE=3
          );

        update dbo.GLFISCALPERIOD 
        set  
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE,
          CLOSED=#FINANCIALEDGENXTFISCALPERIOD.CLOSED from #FINANCIALEDGENXTFISCALPERIOD
        where GLFISCALPERIOD.ID=#FINANCIALEDGENXTFISCALPERIOD.GLFISCALPERIODID
          and GLFISCALPERIOD.CLOSED!=#FINANCIALEDGENXTFISCALPERIOD.CLOSED;

        select @ROWSAFFECTED += @@ROWCOUNT;