USP_ACCOUNTINGELEMENTRELATIONSHIPS_SAVE

      CREATE procedure dbo.USP_ACCOUNTINGELEMENTRELATIONSHIPS_SAVE
      (
        @ACCOUNTINGELEMENTRELATIONSHIPID uniqueidentifier,
        @BASEELEMENTSTRUCTUREID uniqueidentifier,
        @RESTRICTION tinyint,
        @ACCOUNTSTRUCTURE xml,
        @ACCOUNTQUERYID uniqueidentifier,
        @SELECTEDACCOUNTS xml,
        @CHANGEAGENTID uniqueidentifier,
        @CURRENTDATE datetime
      )
      with execute as owner
      as
      begin
        declare @BASEELEMENTSELECTEDELEMENTS xml
        declare @BASEELEMENTCOLUMN tinyint
        declare @BASEELEMENTTYPECODE tinyint
        declare @BASEELEMENTQUERYID uniqueidentifier
        select @BASEELEMENTSELECTEDELEMENTS = ACCOUNTSTRUCTUREXML.[ACTSTR].query('(SELECTEDELEMENTS)'),
          @BASEELEMENTCOLUMN = ACCOUNTSTRUCTUREXML.[ACTSTR].value('(SEGMENTCOLUMN)[1]', 'tinyint'),
          @BASEELEMENTTYPECODE = ACCOUNTSTRUCTUREXML.[ACTSTR].value('(ELEMENTTYPECODE)[1]', 'tinyint'),
          @BASEELEMENTQUERYID = ACCOUNTSTRUCTUREXML.[ACTSTR].value('(QUERYID)[1]', 'uniqueidentifier')
          from @ACCOUNTSTRUCTURE.nodes('ACCOUNTSTRUCTURE/ITEM') as ACCOUNTSTRUCTUREXML([ACTSTR])
          where ACCOUNTSTRUCTUREXML.[ACTSTR].value('(ID)[1]', 'uniqueidentifier') = @BASEELEMENTSTRUCTUREID

        declare @SEQUENCE int, @MAXSEQUENCE int, @USEACCOUNTS bit;
        select @SEQUENCE = min(SEGMENTCOLUMN), @MAXSEQUENCE = max(SEGMENTCOLUMN) from dbo.PDACCOUNTSTRUCTURE

       delete from dbo.ACCOUNTINGELEMENTRELATIONSHIPQUERYPARAMETER where ACCOUNTINGELEMENTRELATIONSHIPID = @ACCOUNTINGELEMENTRELATIONSHIPID
       delete from dbo.ACCOUNTINGELEMENTRELATIONSHIPELEMENTPARAMETER where ACCOUNTINGELEMENTRELATIONSHIPID = @ACCOUNTINGELEMENTRELATIONSHIPID
       delete from dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL where ACCOUNTINGELEMENTRELATIONSHIPID = @ACCOUNTINGELEMENTRELATIONSHIPID

       create table #BASEELEMENTS (DATAELEMENTID uniqueidentifier)

       insert into #BASEELEMENTS
       select distinct T1.c.value('(ID)[1]', 'uniqueidentifier') as BASEELEMENTID
       from @BASEELEMENTSELECTEDELEMENTS.nodes('SELECTEDELEMENTS/ITEM') T1(c)

       insert into dbo.ACCOUNTINGELEMENTRELATIONSHIPELEMENTPARAMETER(ACCOUNTINGELEMENTRELATIONSHIPID, DATAELEMENTID, PDACCOUNTSTRUCTUREID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
       select distinct @ACCOUNTINGELEMENTRELATIONSHIPID, DATAELEMENTID, @BASEELEMENTSTRUCTUREID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
       from #BASEELEMENTS

       if @BASEELEMENTQUERYID is not null
       begin
         if (select count(distinct ID) from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@BASEELEMENTQUERYID)) = 0   
         begin
            raiserror('ERR_BASEELEMENTSELECTEDELEMENTS_QUERY_VALUE_REQUIRED',13,1);
            return 1;
         end

         insert into #BASEELEMENTS(DATAELEMENTID)
         select distinct QUERY.ID
         from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@BASEELEMENTQUERYID) QUERY
         inner join dbo.PDACCOUNTSEGMENTVALUE PDASV on QUERY.ID = PDASV.ID
         where PDASV.PDACCOUNTSTRUCTUREID = @BASEELEMENTSTRUCTUREID

         insert into dbo.ACCOUNTINGELEMENTRELATIONSHIPQUERYPARAMETER(ACCOUNTINGELEMENTRELATIONSHIPID, QUERYID, PDACCOUNTSTRUCTUREID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
         values(@ACCOUNTINGELEMENTRELATIONSHIPID, @BASEELEMENTQUERYID, @BASEELEMENTSTRUCTUREID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
       end

        if (select count(*) from #BASEELEMENTS) = 0
        begin
              raiserror('ERR_BASEELEMENTSELECTEDELEMENTS_REQUIRED',13,1);
              return 1;     
        end

       if @BASEELEMENTTYPECODE = 1
          set @USEACCOUNTS = 0
        else
        begin
          create table #ACCOUNTS (ACCOUNTID uniqueidentifier)

          insert into #ACCOUNTS (ACCOUNTID)
          select distinct T2.c.value('(ID)[1]', 'uniqueidentifier')
          from @SELECTEDACCOUNTS.nodes('SELECTEDELEMENTS/ITEM') T2(c)

          insert into dbo.ACCOUNTINGELEMENTRELATIONSHIPELEMENTPARAMETER(ACCOUNTINGELEMENTRELATIONSHIPID, ACCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          select distinct @ACCOUNTINGELEMENTRELATIONSHIPID, ACCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
          from #ACCOUNTS

          if @ACCOUNTQUERYID is not null
          begin            

            if (select count(distinct ID) from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@ACCOUNTQUERYID)) = 0   
            begin
              raiserror('ERR_BASEELEMENTSELECTEDELEMENTS_QUERY_VALUE_REQUIRED',13,1);
              return 1;
            end

            insert into #ACCOUNTS (ACCOUNTID)
            select distinct ID
            from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@ACCOUNTQUERYID)

            insert into dbo.ACCOUNTINGELEMENTRELATIONSHIPQUERYPARAMETER(ACCOUNTINGELEMENTRELATIONSHIPID, QUERYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@ACCOUNTINGELEMENTRELATIONSHIPID, @ACCOUNTQUERYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)              
          end

          if (select count(1) from #ACCOUNTS) > 0
          begin         
            set @USEACCOUNTS = 1
            insert into dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL(ACCOUNTINGELEMENTRELATIONSHIPID, BASEELEMENTID, RESTRICTIONCODE, ACCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
              DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
              DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
              DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)
            select distinct @ACCOUNTINGELEMENTRELATIONSHIPID,
            BASEELEMENTID,
            @RESTRICTION,
            GLACCOUNT.ID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
            case @BASEELEMENTCOLUMN when 1 then BASEELEMENTID else DATAELEMENT1ID end, case @BASEELEMENTCOLUMN when 2 then BASEELEMENTID else DATAELEMENT2ID end, case @BASEELEMENTCOLUMN when 3 then BASEELEMENTID else DATAELEMENT3ID end, case @BASEELEMENTCOLUMN when 4 then BASEELEMENTID else DATAELEMENT4ID end, case @BASEELEMENTCOLUMN when 5 then BASEELEMENTID else DATAELEMENT5ID end, 
            case @BASEELEMENTCOLUMN when 6 then BASEELEMENTID else DATAELEMENT6ID end, case @BASEELEMENTCOLUMN when 7 then BASEELEMENTID else DATAELEMENT7ID end, case @BASEELEMENTCOLUMN when 8 then BASEELEMENTID else DATAELEMENT8ID end, case @BASEELEMENTCOLUMN when 9 then BASEELEMENTID else DATAELEMENT9ID end, case @BASEELEMENTCOLUMN when 10 then BASEELEMENTID else DATAELEMENT10ID end, 
            case @BASEELEMENTCOLUMN when 11 then BASEELEMENTID else DATAELEMENT11ID end, case @BASEELEMENTCOLUMN when 12 then BASEELEMENTID else DATAELEMENT12ID end, case @BASEELEMENTCOLUMN when 13 then BASEELEMENTID else DATAELEMENT13ID end, case @BASEELEMENTCOLUMN when 14 then BASEELEMENTID else DATAELEMENT14ID end, case @BASEELEMENTCOLUMN when 15 then BASEELEMENTID else DATAELEMENT15ID end, 
            case @BASEELEMENTCOLUMN when 16 then BASEELEMENTID else DATAELEMENT16ID end, case @BASEELEMENTCOLUMN when 17 then BASEELEMENTID else DATAELEMENT17ID end, case @BASEELEMENTCOLUMN when 18 then BASEELEMENTID else DATAELEMENT18ID end, case @BASEELEMENTCOLUMN when 19 then BASEELEMENTID else DATAELEMENT19ID end, case @BASEELEMENTCOLUMN when 20 then BASEELEMENTID else DATAELEMENT20ID end, 
   case @BASEELEMENTCOLUMN when 21 then BASEELEMENTID else DATAELEMENT21ID end, case @BASEELEMENTCOLUMN when 22 then BASEELEMENTID else DATAELEMENT22ID end, case @BASEELEMENTCOLUMN when 23 then BASEELEMENTID else DATAELEMENT23ID end, case @BASEELEMENTCOLUMN when 24 then BASEELEMENTID else DATAELEMENT24ID end, case @BASEELEMENTCOLUMN when 25 then BASEELEMENTID else DATAELEMENT25ID end, 
            case @BASEELEMENTCOLUMN when 26 then BASEELEMENTID else DATAELEMENT26ID end, case @BASEELEMENTCOLUMN when 27 then BASEELEMENTID else DATAELEMENT27ID end, case @BASEELEMENTCOLUMN when 28 then BASEELEMENTID else DATAELEMENT28ID end, case @BASEELEMENTCOLUMN when 29 then BASEELEMENTID else DATAELEMENT29ID end, case @BASEELEMENTCOLUMN when 30 then BASEELEMENTID else DATAELEMENT30ID end
            from
              (select distinct DATAELEMENTID as BASEELEMENTID
              from #BASEELEMENTS) as a
              cross join
              (select distinct ACCOUNTID
              from #ACCOUNTS) as b
            inner join dbo.GLACCOUNT on b.ACCOUNTID = GLACCOUNT.ID 

          end
          else
            set @USEACCOUNTS = 0

          drop table #ACCOUNTS
        end

        create table #DATAELEMENTS (DATAELEMENTID uniqueidentifier)
        while @SEQUENCE <= @MAXSEQUENCE
          begin
          if @SEQUENCE <> @BASEELEMENTCOLUMN
          begin
                declare @SQL nvarchar(max)
            declare @SELECTEDELEMENTS xml
            declare @ELEMENTTYPECODE tinyint
            declare @QUERYID uniqueidentifier
            declare @STRUCTUREID uniqueidentifier

            select @SELECTEDELEMENTS = ACCOUNTSTRUCTUREXML.[ACTSTR].query('(SELECTEDELEMENTS)'),
            @ELEMENTTYPECODE = ACCOUNTSTRUCTUREXML.[ACTSTR].value('(ELEMENTTYPECODE)[1]', 'tinyint'),
            @QUERYID = ACCOUNTSTRUCTUREXML.[ACTSTR].value('(QUERYID)[1]', 'uniqueidentifier'),
            @STRUCTUREID = ACCOUNTSTRUCTUREXML.[ACTSTR].value('(ID)[1]', 'uniqueidentifier')
            from @ACCOUNTSTRUCTURE.nodes('ACCOUNTSTRUCTURE/ITEM') as ACCOUNTSTRUCTUREXML([ACTSTR])
            where ACCOUNTSTRUCTUREXML.[ACTSTR].value('(SEGMENTCOLUMN)[1]', 'tinyint') = @SEQUENCE

            insert into #DATAELEMENTS(DATAELEMENTID)
            select distinct T2.c.value('(ID)[1]', 'uniqueidentifier')
            from @SELECTEDELEMENTS.nodes('SELECTEDELEMENTS/ITEM') T2(c)

            insert into dbo.ACCOUNTINGELEMENTRELATIONSHIPELEMENTPARAMETER(ACCOUNTINGELEMENTRELATIONSHIPID, DATAELEMENTID, PDACCOUNTSTRUCTUREID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select distinct @ACCOUNTINGELEMENTRELATIONSHIPID, DATAELEMENTID, @STRUCTUREID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from #DATAELEMENTS

            if @QUERYID is not null
            begin

              if (select count(distinct ID) from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@QUERYID)) = 0   
              begin
                raiserror('ERR_BASEELEMENTSELECTEDELEMENTS_QUERY_VALUE_REQUIRED',13,1);
                return 1;
              end

              insert into #DATAELEMENTS(DATAELEMENTID)
              select distinct QUERY.ID
              from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@QUERYID) QUERY
              inner join dbo.PDACCOUNTSEGMENTVALUE PDASV on QUERY.ID = PDASV.ID
              where PDASV.PDACCOUNTSTRUCTUREID = @STRUCTUREID

              insert into dbo.ACCOUNTINGELEMENTRELATIONSHIPQUERYPARAMETER(ACCOUNTINGELEMENTRELATIONSHIPID, QUERYID, PDACCOUNTSTRUCTUREID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values(@ACCOUNTINGELEMENTRELATIONSHIPID, @QUERYID, @STRUCTUREID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
            end

                if (@USEACCOUNTS = 0 or @ELEMENTTYPECODE = 2) and ((select count(*) from #DATAELEMENTS) > 0)
       begin
                  set @SQL = N'insert into dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL(ACCOUNTINGELEMENTRELATIONSHIPID, BASEELEMENTID, RESTRICTIONCODE, DATAELEMENT' + cast(@BASEELEMENTCOLUMN as nvarchar(2)) + 'ID, DATAELEMENT' + cast(@SEQUENCE as nvarchar(2)) + 'ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              select distinct @ACCOUNTINGELEMENTRELATIONSHIPID,
              BASEELEMENTID,
              @RESTRICTION,
              BASEELEMENTID,
              SELECTEDELEMENTID,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
              from
                (select distinct DATAELEMENTID as BASEELEMENTID
                from #BASEELEMENTS) as a
                cross join
                (select distinct DATAELEMENTID as SELECTEDELEMENTID
                from #DATAELEMENTS) as b';

              exec sp_executesql @SQL, N'@ACCOUNTINGELEMENTRELATIONSHIPID uniqueidentifier, @RESTRICTION tinyint, @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime', 
                @ACCOUNTINGELEMENTRELATIONSHIPID = @ACCOUNTINGELEMENTRELATIONSHIPID, @RESTRICTION = @RESTRICTION, @CHANGEAGENTID = @CHANGEAGENTID, @CURRENTDATE = @CURRENTDATE;
            end

            delete from #DATAELEMENTS
          end

          set @SEQUENCE = @SEQUENCE + 1
          end 

        drop table #DATAELEMENTS
        drop table #BASEELEMENTS

        if (select count(*) from dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL where ACCOUNTINGELEMENTRELATIONSHIPID = @ACCOUNTINGELEMENTRELATIONSHIPID) = 0
        begin
              raiserror('ERR_SELECTEDELEMENTS_REQUIRED',13,1);
              return 1;     
        end
      end