USP_CLOSINGREQUIREMENT_VALIDATEINCLUDE

CREATE procedure dbo.USP_CLOSINGREQUIREMENT_VALIDATEINCLUDE(@CLOSINGREQUIREMENTID uniqueidentifier = null, @DATAELEMENTID uniqueidentifier = null, @DATAELEMENTQUERYID uniqueidentifier = null, @LEDGERACCOUNTID uniqueidentifier = null)
with execute as owner
as
begin
    declare @COUNT int
  declare @CATEGORYCODE int
    declare @idsetregisters table(id uniqueidentifier)
    declare @id uniqueidentifier

    -- Only one requirement allowed if it's All records

    select @COUNT = count(ID) from dbo.CLOSINGREQUIREMENT 
    where (ID <> @CLOSINGREQUIREMENTID) and DATAELEMENTID is null and DATAELEMENTQUERYID is null
    if @COUNT <> 0
    begin
        raiserror('ERR_INUSE_ALLRECORDS',13,1);
        return 1;
    end

  -- An account other than net asset has been selected

  if @LEDGERACCOUNTID is not null
    begin
        select @CATEGORYCODE = CATEGORYCODE from dbo.ACCOUNTCODE 
        inner join dbo.GLACCOUNT on ACCOUNTCODE.ID = GLACCOUNT.ACCOUNTCODEID
      where GLACCOUNT.ID = @LEDGERACCOUNTID
        if @CATEGORYCODE <> 3
        begin
            raiserror('ERR_SELECT_NONASSETACCOUNT',13,1);
            return 1;
        end
    end

    -- Can't use All records if any other requirements defined

    if @DATAELEMENTID is null and @DATAELEMENTQUERYID is null
    begin
    if exists (select ID from dbo.CLOSINGREQUIREMENT where ID <> @CLOSINGREQUIREMENTID)
        begin
            raiserror('ERR_ALLRECORDS_EXISTINGREQS',13,1);
            return 1;
        end
    end

    if @DATAELEMENTID is not null
    begin
        -- Check for specific element in use on other requirements

    if exists 
      (select ID from dbo.CLOSINGREQUIREMENT 
      where ID <> @CLOSINGREQUIREMENTID and DATAELEMENTID = @DATAELEMENTID)
        begin
            raiserror('ERR_INUSE_SPECIFICELEMENT',13,1);
            return 1;
        end

        -- Check for specific element included in other queries

        insert @idsetregisters 
      select DATAELEMENTQUERYID 
      from dbo.CLOSINGREQUIREMENT 
      where ID <> @CLOSINGREQUIREMENTID
        and DATAELEMENTQUERYID is not null
        if @@ROWCOUNT <> 0
        begin
            select top 1 @id = ID from @idsetregisters order by id
            while @id is not null
            begin
                select @COUNT=count(ID) from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@id) where ID = @DATAELEMENTID
                if @COUNT<>0
                begin
                    raiserror('ERR_SPECIFICELEMENT_IN_OTHER_SELECTION',13,1);
                    return 1;
                end

                delete from @idsetregisters where id = @id;
        set @id = null;
                select top 1 @id = ID from @idsetregisters order by id;
            end
        end

    end

    if @DATAELEMENTQUERYID is not null
    begin
        --Check for selection including an in-use specific element

        select @COUNT=count(ID) from dbo.CLOSINGREQUIREMENT where CLOSINGREQUIREMENT.ID <> @CLOSINGREQUIREMENTID 
    and DATAELEMENTID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DATAELEMENTQUERYID))

        if @COUNT <> 0
        begin
            raiserror('ERR_SELECTION_CONTAINS_SPECIFICELEMENT',13,1);
            return 1;
        end

        --Check for overlapping queries

        insert @idsetregisters 
      select DATAELEMENTQUERYID 
      from dbo.CLOSINGREQUIREMENT 
      where ID <> @CLOSINGREQUIREMENTID
        and DATAELEMENTQUERYID is not null
        if @@ROWCOUNT <> 0
        begin
            select top 1 @id = ID from @idsetregisters order by id
            while @id is not null
            begin
                select @COUNT=count(ID) from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DATAELEMENTQUERYID) where ID in 
        (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@id))

                if @COUNT<>0
                begin
                    raiserror('ERR_SELECTION_OVERLAP',13,1);
                    return 1;
                end

                delete from @idsetregisters where id = @id
        set @id = null
        select top 1 @id = ID from @idsetregisters order by id
            end
        end
    end

    return 0;
end