USP_FENXT_ADDFENXTFISCALPERIODS

      CREATE procedure USP_FENXT_ADDFENXTFISCALPERIODS
       (
        @CHANGEAGENTID uniqueidentifier = null,
        @ROWSAFFECTED int OUTPUT
        )
        as

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

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

          begin try

          begin transaction FISCALPERIODSADDFROMFENXTSTAGING

          create table #FINANCIALEDGENXTFISCALPERIOD
            (GLFISCALYEARID uniqueidentifier,
            FINANCIALEDGENXTFISCALYEARID smallint,
            GLFISCALPERIODID uniqueidentifier,
            FINANCIALEDGENXTFISCALPERIODID smallint,
            STARTDATE date,
            ENDDATE date,
            CLOSED bit,
            SEQUENCE smallint)

          insert into #FINANCIALEDGENXTFISCALPERIOD
            (GLFISCALYEARID,
            FINANCIALEDGENXTFISCALYEARID,
            GLFISCALPERIODID,
            FINANCIALEDGENXTFISCALPERIODID,
            STARTDATE,
            ENDDATE,
            CLOSED,
            SEQUENCE)
          select
            isnull(FENXTFISCALYEAR.GLFISCALYEARID ,FENXTFISCALYEARSTAGING.GLFISCALYEARID),
            FENXTFISCALYEARSTAGING.FENXTFISCALYEARID, 
            isnull(FENXTFISCALPERIOD.GLFISCALPERIODID, newid()) as GLFISCALPERIODID,
            T.c.value('(FENXTID)[1]','int') AS 'FPID',
            T.c.value('(STARTDATE)[1]','date') as 'STARTDATE',
            T.c.value('(ENDDATE)[1]','date') as    'ENDDATE',
            T.c.value('(CLOSED)[1]','bit') as 'CLOSED',
            T.c.value('(SEQUENCE)[1]','smallint') as 'SEQUENCE'
          from FENXTFISCALYEARSTAGING
            cross apply FiscalPeriods.nodes('/FISCALYEARPERIODS/ITEM') T(c)
            left join dbo.FENXTFISCALYEAR on FENXTFISCALYEARSTAGING.FENXTFISCALYEARID = FENXTFISCALYEAR.FENXTFISCALYEARID
            left join dbo.FENXTFISCALPERIOD on T.c.value('(FENXTID)[1]','int') = FENXTFISCALPERIOD.FENXTFISCALPERIODID;

          --Verify that fiscal periods and dates are consistent.

          --The startdate of the first fiscal period for the fiscal year must be the same as the start date of the fiscal year.

          --The end date of the last fiscal period for the fiscal year must be the same as the end date of the fiscal year

          declare @INCONSISTENTYEARSANDPERIODS bit;
          set @INCONSISTENTYEARSANDPERIODS=0;
          if exists
          (
            select 1
            from dbo.FENXTFISCALYEARSTAGING
              inner join 
              (
                select FINANCIALEDGENXTFISCALYEARID, min(STARTDATE) as MINSTARTDATE, max(ENDDATE) as MAXENDDATE
                from #FINANCIALEDGENXTFISCALPERIOD
                group by FINANCIALEDGENXTFISCALYEARID
              ) as PERIODDATES on FENXTFISCALYEARSTAGING.FENXTFISCALYEARID = PERIODDATES.FINANCIALEDGENXTFISCALYEARID
            where FENXTFISCALYEARSTAGING.STARTDATE<>PERIODDATES.MINSTARTDATE
              or FENXTFISCALYEARSTAGING.ENDDATE<>PERIODDATES.MAXENDDATE
          )
          set @INCONSISTENTYEARSANDPERIODS=1;

          if @INCONSISTENTYEARSANDPERIODS=1
            RAISERROR ('New Financial Edge NXT fiscal years can not be synced from Financial Edge NXT.  Financial Edge NXT has fiscal years with invalid fiscal periods.', 16, 2) WITH SETERROR

         --Check for possible duplicate fiscal periods

         declare @DUPLICATEFISCALPERIODS bit;
         set @DUPLICATEFISCALPERIODS =0;
         if (select count(STARTDATE) from #FINANCIALEDGENXTFISCALPERIOD) <> (select count(distinct STARTDATE) from #FINANCIALEDGENXTFISCALPERIOD)
           set  @DUPLICATEFISCALPERIODS=1;
         if @DUPLICATEFISCALPERIODS=1
           RAISERROR ('New fiscal years cannot be added from Financial Edge NXT as there have duplicated start dates.', 16, 2) WITH SETERROR

          insert into dbo.GLFISCALPERIOD
            (ID,
            GLFISCALYEARID,
            STARTDATE,
            ENDDATE,
            CLOSED,
            SEQUENCE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED)
          select
            #FINANCIALEDGENXTFISCALPERIOD.GLFISCALPERIODID,
            #FINANCIALEDGENXTFISCALPERIOD.GLFISCALYEARID,
            #FINANCIALEDGENXTFISCALPERIOD.STARTDATE,
            #FINANCIALEDGENXTFISCALPERIOD.ENDDATE,
            #FINANCIALEDGENXTFISCALPERIOD.CLOSED,
            #FINANCIALEDGENXTFISCALPERIOD.SEQUENCE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          from #FINANCIALEDGENXTFISCALPERIOD
          where not exists (select 1 from dbo.GLFISCALPERIOD where GLFISCALPERIOD.ID = #FINANCIALEDGENXTFISCALPERIOD.GLFISCALPERIODID)
          order by #FINANCIALEDGENXTFISCALPERIOD.STARTDATE;

          set @ROWSAFFECTED += @@ROWCOUNT;

          insert into FENXTFISCALPERIOD
          (
            ID,
            GLFISCALPERIODID,
            FENXTFISCALPERIODID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED)
          select
            newid(),
            GLFISCALPERIODID,
            FINANCIALEDGENXTFISCALPERIODID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          from #FINANCIALEDGENXTFISCALPERIOD
          where not exists (select 1 from dbo.FENXTFISCALPERIOD where FENXTFISCALPERIOD.GLFISCALPERIODID = #FINANCIALEDGENXTFISCALPERIOD.GLFISCALPERIODID);

          commit transaction FISCALPERIODSADDFROMFENXTSTAGING

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

          raiserror ('FENXTFISCALPERIODSYNCERROR', 16, 2) with seterror
          rollback transaction FISCALPERIODSADDFROMFENXTSTAGING
        end catch