USP_DATAFORMTEMPLATE_ADD_FENXTADDACCOUNTSTRUCTURE

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_FENXTADDACCOUNTSTRUCTURE
                    (
                        @ID uniqueidentifier = null output,
                        @PDACCOUNTSYSTEMID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @INCLUDEPROJECT bit = 0
                    )
                    as

                    set nocount on;

                    if @ID is null
                        set @ID = newid();

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

                    declare @CURRENTDATE datetime = getdate();

                    begin try
                        create table #tempAccountStructure (ID uniqueidentifier, DESCRIPTION nvarchar(100), SEQUENCE tinyint, LENGTH tinyint, SEPARATORCODE tinyint, ISPROJECTSEGMENT bit, PDACCOUNTTABLESAVAILABLEFORSEGMENTID uniqueidentifier)

                        insert into #tempAccountStructure (ID, DESCRIPTION, SEQUENCE, LENGTH, ISPROJECTSEGMENT, PDACCOUNTTABLESAVAILABLEFORSEGMENTID, SEPARATORCODE)
                        select newid(), SEGMENTTYPE, SEGMENTSEQUENCE, SEGMENTLENGTH, 0, '1BA6F27F-8454-4FC4-A830-3FDA8CD35D04',
                        case SEPARATOR when 'Hyphen' then 1 when 'Comma' then 2 when 'Slash' then 3 when 'Semicolon' then 4 when 'Period' then 5 when '<No separator>' then 6 end as SepCode
                        from 
                            (select isnull(nullif(SEPARATOR,''),lag(SEPARATOR) over (order by SEGMENTSEQUENCE)) as SEPARATOR,
                            SEGMENTTYPE, SEGMENTLENGTH, SEGMENTSEQUENCE
                            from dbo.FENXTACCOUNTSTRUCTURE
                            where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) as v1

                        if @INCLUDEPROJECT = 1
                            insert into #tempAccountStructure (ID, DESCRIPTION, SEQUENCE, LENGTH, ISPROJECTSEGMENT, PDACCOUNTTABLESAVAILABLEFORSEGMENTID, SEPARATORCODE)
                            values 
                            (newid(), 'Project', (select max(SEGMENTSEQUENCE) from dbo.FENXTACCOUNTSTRUCTURE)+1, len(convert(varchar,(select max(projectid) from dbo.FENXTPROJECT)))+1, 1, '2B1E041E-8FA3-4301-A5DB-E6531E9C3CED', 1)

                        --insert trigger on PDACCOUNTSTRUCTURE can't handle more than 1 row inserted at a timeso use a cursor

                        declare @STRUCTID uniqueidentifier 
                        declare @DESCRIPTION nvarchar(100) 
                        declare @SEQUENCE tinyint
                        declare @LENGTH tinyint
                        declare @SEPARATORCODE tinyint
                        declare @ISPROJECTSEGMENT tinyint
                        declare @PDACCOUNTTABLESAVAILABLEFORSEGMENTID uniqueidentifier

                        declare STRUCTURECURSOR cursor local fast_forward for
                            select ID, DESCRIPTION, SEQUENCE, LENGTH, SEPARATORCODE, ISPROJECTSEGMENT, PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                            from #tempAccountStructure
                            order by SEQUENCE

                            open STRUCTURECURSOR
                            fetch next from STRUCTURECURSOR into @STRUCTID, @DESCRIPTION, @SEQUENCE, @LENGTH, @SEPARATORCODE, @ISPROJECTSEGMENT, @PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                            while @@FETCH_STATUS = 0
                                begin
                                if @DESCRIPTION = 'Account code'
                                    update dbo.PDACCOUNTSTRUCTURE
                                        set SEQUENCE = @SEQUENCE,
                                        SEGMENTSEQUENCE = @SEQUENCE,
                                        LENGTH = @LENGTH,
                                        SEPARATORCODE = @SEPARATORCODE,
                                        CHANGEDBYID = @CHANGEAGENTID,
                                        DATECHANGED = @CURRENTDATE
                                    where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
                                    and SEGMENTTYPE = 1;
                                else
                                    exec dbo.USP_DATAFORMTEMPLATE_ADD_PDACCOUNTSTRUCTURE2
                                        @SYSTEMID = @PDACCOUNTSYSTEMID,
                                        @ID = @STRUCTID,
                                        @CHANGEAGENTID = @CHANGEAGENTID,
                                        @DESCRIPTION = @DESCRIPTION,
                                        @SEQUENCE = @SEQUENCE,
                                        @LENGTH = @LENGTH,
                                        @PDACCOUNTTABLESAVAILABLEFORSEGMENTID = @PDACCOUNTTABLESAVAILABLEFORSEGMENTID,
                                        @SEPARATORCODE = @SEPARATORCODE,
                                        @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
                                        @ISPROJECTSEGMENT = @ISPROJECTSEGMENT;

                                fetch next from STRUCTURECURSOR into @STRUCTID, @DESCRIPTION, @SEQUENCE, @LENGTH, @SEPARATORCODE, @ISPROJECTSEGMENT, @PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                                end

                        close STRUCTURECURSOR;
                        deallocate STRUCTURECURSOR;

                        drop table #tempAccountStructure;
                    end try

                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch

                    return 0;