USP_FENXT_SYNCHRONIZE_PROJECTS

            CREATE proc dbo.USP_FENXT_SYNCHRONIZE_PROJECTS
                (
                @PDACCOUNTSYSTEMID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @LASTUPDATETIME datetime = '17530101',
                @APPUSERID uniqueidentifier = null,
                @DEFAULTPURPOSETYPEID uniqueidentifier = null,
                @ROWSAFFECTED int OUTPUT,
                @BUSINESSPROCESSSTATUSID uniqueidentifier = null
                )
                as
                set nocount on

                set @ROWSAFFECTED = 0;

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

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

                begin try
                    select
                        USERID
                    into #USERIDGROUP
                    from dbo.DESIGNATIONLEVEL
                    union
                    select
                        USERID
                    from dbo.DESIGNATION;

                    create table #TEMPDESIGNATIONSTOSYNC
                        (ID uniqueidentifier,
                        DESIGNATIONID uniqueidentifier,
                        DESIGNATIONNAME nvarchar(255));

                    insert into #TEMPDESIGNATIONSTOSYNC
                        (ID,
                        DESIGNATIONID,
                        DESIGNATIONNAME)
                    select
                        FENXTPROJECTFILTERED.ID,
                        isnull(DESIGNATIONALTLOOKUPID.DESIGNATIONID, DESIGNATION.ID),
                        FENXTPROJECTFILTERED.DESCRIPTION
                    from
                        (select ID, DESCRIPTION, UIPROJECTID, TYPE, STARTDATE, ENDDATE, DESIGNATIONID
                        from dbo.FENXTPROJECT
                        where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) as FENXTPROJECTFILTERED
                    left join dbo.DESIGNATIONALTLOOKUPID 
                        on FENXTPROJECTFILTERED.UIPROJECTID = DESIGNATIONALTLOOKUPID.ALTLOOKUPID
                        and DESIGNATIONALTLOOKUPID.ALTLOOKUPIDTYPECODEID = 'E5837E89-B51A-4BAD-8E06-50D515DC64A2'
                    left join dbo.DESIGNATION
                        on FENXTPROJECTFILTERED.UIPROJECTID = DESIGNATION.USERID
                    where FENXTPROJECTFILTERED.DESIGNATIONID is null
                        and (FENXTPROJECTFILTERED.TYPE <> '' or @DEFAULTPURPOSETYPEID is not null);


                    if @DEFAULTPURPOSETYPEID is null
                        insert into dbo.DESIGNATIONLEVELTYPE
                            (ID,
                            DESCRIPTION,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED)
                        select
                            newid(),
                            FENXTPROJECTTYPEFILTERED.TYPE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        from 
                            (select distinct FENXTPROJECT.TYPE 
                            from #TEMPDESIGNATIONSTOSYNC inner join dbo.FENXTPROJECT on #TEMPDESIGNATIONSTOSYNC.ID = FENXTPROJECT.ID
                            where #TEMPDESIGNATIONSTOSYNC.DESIGNATIONID is null
                            and not exists(select 1 from DESIGNATIONLEVELTYPE where DESIGNATIONLEVELTYPE.DESCRIPTION = FENXTPROJECT.TYPE)) as FENXTPROJECTTYPEFILTERED;

                    declare PROJECTCURSOR cursor local fast_forward for
                        select
                            #TEMPDESIGNATIONSTOSYNC.ID,
                            #TEMPDESIGNATIONSTOSYNC.DESIGNATIONNAME,
                            FENXTPROJECT.UIPROJECTID, 
                            FENXTPROJECT.STARTDATE,
                            FENXTPROJECT.ENDDATE,
                            isnull(@DEFAULTPURPOSETYPEID, DESIGNATIONLEVELTYPE.ID) as PURPOSETYPEID
                        from #TEMPDESIGNATIONSTOSYNC
                        inner join dbo.FENXTPROJECT on #TEMPDESIGNATIONSTOSYNC.ID = FENXTPROJECT.ID
                        left join dbo.DESIGNATIONLEVELTYPE on FENXTPROJECT.TYPE = DESIGNATIONLEVELTYPE.DESCRIPTION
                        left join dbo.DESIGNATIONLEVEL on FENXTPROJECT.DESCRIPTION = DESIGNATIONLEVEL.NAME and isnull(@DEFAULTPURPOSETYPEID, DESIGNATIONLEVELTYPE.ID) = DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID
                        where #TEMPDESIGNATIONSTOSYNC.DESIGNATIONID is null
                        and FENXTPROJECT.UIPROJECTID not in (select USERID from #USERIDGROUP);

                        declare @PROJECTID uniqueidentifier;
                        declare @PROJECTDESCRIPTION nvarchar(255);
                        declare @UIPROJECTID nvarchar(100);
                        declare @STARTDATE date;
                        declare @ENDDATE date;
                        declare @PURPOSETYPEID as uniqueidentifier;

                        open PROJECTCURSOR
                        fetch next from PROJECTCURSOR into @PROJECTID, @PROJECTDESCRIPTION, @UIPROJECTID, @STARTDATE, @ENDDATE, @PURPOSETYPEID
                        while @@fetch_status = 0
                            begin
                                declare @ID uniqueidentifier = newid()

                                -- Update the PROJECTDESCRIPTION (designation/purpose name) in order to avoid a duplicate record DESIGNATIONLEVEL error

                                if exists (select 1 from dbo.DESIGNATIONLEVEL where DESIGNATIONLEVEL.NAME = @PROJECTDESCRIPTION and DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = @PURPOSETYPEID)
                                    set @PROJECTDESCRIPTION = @PROJECTDESCRIPTION + '_' + @UIPROJECTID;

                                exec dbo.USP_DATAFORMTEMPLATE_ADD_PURPOSEANDDESIGNATION
                                    @ID=@ID,
                                    @DESIGNATIONLOOKUPID = @UIPROJECTID,
                                    @DESIGNATIONVANITYNAME = @PROJECTDESCRIPTION,
                                    @PURPOSENAME = @PROJECTDESCRIPTION,
                                    @PURPOSETYPEID = @PURPOSETYPEID,
                                    @PURPOSELOOKUPID = @UIPROJECTID,
                                    @PURPOSEVANITYNAME = @PROJECTDESCRIPTION,
                                    @STARTDATE = @STARTDATE,
                                    @ENDDATE = @ENDDATE,
                                    @ISREVENUEDESIGNATION = 1,
                                    @INITIALPARENTDESIGNATIONID = null,
                                    @CURRENTAPPUSERID = @APPUSERID,
                                    @CHANGEAGENTID = @CHANGEAGENTID,
                                    @ISENDOWED = 0,
                                    @ISFULLYFUNDED = 0,
                                    @ISINCOMETOPRINCIPAL = 0,
                                    @STATEMENTWORDING = '';

                                update #TEMPDESIGNATIONSTOSYNC
                                    set DESIGNATIONID = @ID
                                where ID = @PROJECTID;

                                fetch next from PROJECTCURSOR into @PROJECTID, @PROJECTDESCRIPTION, @UIPROJECTID, @STARTDATE, @ENDDATE, @PURPOSETYPEID
                            end
                    close PROJECTCURSOR;
                    deallocate PROJECTCURSOR;

                    insert into dbo.DESIGNATIONALTLOOKUPID
                        (DESIGNATIONID,
                        ALTLOOKUPIDTYPECODEID,
                        ALTLOOKUPID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED)
                    select
                        #TEMPDESIGNATIONSTOSYNC.DESIGNATIONID,
                        'E5837E89-B51A-4BAD-8E06-50D515DC64A2',
                        FENXTPROJECT.UIPROJECTID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    from #TEMPDESIGNATIONSTOSYNC
                    inner join FENXTPROJECT on #TEMPDESIGNATIONSTOSYNC.ID = FENXTPROJECT.ID
                    where not exists (select 1 from DESIGNATIONALTLOOKUPID where DESIGNATIONALTLOOKUPID.DESIGNATIONID = #TEMPDESIGNATIONSTOSYNC.DESIGNATIONID and DESIGNATIONALTLOOKUPID.ALTLOOKUPIDTYPECODEID = 'E5837E89-B51A-4BAD-8E06-50D515DC64A2')
                    and #TEMPDESIGNATIONSTOSYNC.DESIGNATIONID is not null;

                    update dbo.FENXTPROJECT
                        set DESIGNATIONID = #TEMPDESIGNATIONSTOSYNC.DESIGNATIONID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                    from dbo.FENXTPROJECT
                    inner join #TEMPDESIGNATIONSTOSYNC on FENXTPROJECT.ID = #TEMPDESIGNATIONSTOSYNC.ID;

                    select @ROWSAFFECTED = @@ROWCOUNT;

                    update dbo.DESIGNATION
                        set ISACTIVE = 1,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                    from dbo.DESIGNATION
                    inner join dbo.FENXTPROJECT on DESIGNATION.ID = FENXTPROJECT.DESIGNATIONID
                    where FENXTPROJECT.DATEMODIFIED > @LASTUPDATETIME
                        and (FENXTPROJECT.POSTINGDATE is null or FENXTPROJECT.PREVENTPOSTINGAFTER = 0)
                        and DESIGNATION.ISACTIVE = 0
                        and FENXTPROJECT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

                    select @ROWSAFFECTED += @@ROWCOUNT;

                    update dbo.DESIGNATION
                        set ISACTIVE = 0,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                    from dbo.DESIGNATION inner join dbo.FENXTPROJECT on DESIGNATION.ID = FENXTPROJECT.DESIGNATIONID
                    where FENXTPROJECT.PREVENTPOSTINGAFTER = 1
                        and FENXTPROJECT.POSTINGDATE < dateadd(d,1,convert(date,@CURRENTDATE))
                        and DESIGNATION.ISACTIVE = 1
                        and FENXTPROJECT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

                    select @ROWSAFFECTED += @@ROWCOUNT;

                    --will gather any exceptions from process of creating the designations from the FENXTPROJECT staging table

                    insert into dbo.FENXTSYNCPROCESSEXCEPTIONS(
                        PDACCOUNTSYSTEMID,
                        BUSINESSPROCESSSTATUSID,
                        SYNCOPERATIONTYPECODE,
                        RAISERRORID,
                        EXCEPTIONRECORDID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED)
                    select
                        FENXTPROJECT.PDACCOUNTSYSTEMID,
                        @BUSINESSPROCESSSTATUSID,
                        2,
                        'UC_DESIGNATION_PURPOSE_USERID',
                        FENXTPROJECT.ID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                from #TEMPDESIGNATIONSTOSYNC
                inner join dbo.FENXTPROJECT on #TEMPDESIGNATIONSTOSYNC.ID = FENXTPROJECT.ID
                where #TEMPDESIGNATIONSTOSYNC.DESIGNATIONID is null
                    and FENXTPROJECT.UIPROJECTID in (select USERID from #USERIDGROUP);

                    select @ROWSAFFECTED -= @@ROWCOUNT;
            end try

            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            if(('tempdb..#USERIDGROUP') is not null)
            begin
                drop table #USERIDGROUP;
            end