UFN_CLOSINGREQUIREMENT_GETREQUIREDELEMENTS

CREATE function dbo.UFN_CLOSINGREQUIREMENT_GETREQUIREDELEMENTS(@DATAELEMENTID uniqueidentifier, @INCOMESTATEMENTACCOUNT bit = 0)
returns @ACCOUNTSTRUCTURES table (ACCOUNTSTRUCTUREID uniqueidentifier)
as begin
  declare @CLOSINGREQUIREMENTID uniqueidentifier

  declare @PDACCOUNTSYSTEMID uniqueidentifier
  set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

  declare @ACCOUNTCODESTRUCTUREID uniqueidentifier
  set @ACCOUNTCODESTRUCTUREID = (select ID from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0 and ELEMENTDEFINITIONCODE = 1)

  insert @ACCOUNTSTRUCTURES values (@ACCOUNTCODESTRUCTUREID) --Account code is always required


  --Is there an "All records" closing requirement?

  select @CLOSINGREQUIREMENTID = ID from dbo.CLOSINGREQUIREMENT where DATAELEMENTID is null and DATAELEMENTQUERYID is null

  --If not, is this element chosen as a specific element in a requirement?

  if @CLOSINGREQUIREMENTID is null
    select @CLOSINGREQUIREMENTID = ID from dbo.CLOSINGREQUIREMENT where DATAELEMENTID = @DATAELEMENTID

  --Finally, check for queries that contain this element

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

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

  if @CLOSINGREQUIREMENTID is not null
  begin
    insert @ACCOUNTSTRUCTURES select ACCOUNTSTRUCTUREID from dbo.CLOSINGREQUIREMENTDETAIL 
    where CLOSINGREQUIREMENTID = @CLOSINGREQUIREMENTID and 
      (ACCOUNTREQUIREMENTSCODE = 3 or (@INCOMESTATEMENTACCOUNT = 1 and ACCOUNTREQUIREMENTSCODE = 2))
  end

  return;
end