USP_DATAFORMTEMPLATE_VIEW_JOURNALENTRY_CLOSINGREQUIREMENTS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_JOURNALENTRY_CLOSINGREQUIREMENTS
(
    @ID xml,
    @DATALOADED bit = 0 output,
    @REQUIREDSTRUCTUREIDS xml = null output
)
with execute as owner
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    declare @DATAELEMENTID uniqueidentifier; if @ID.exist('/PARAMETERS/DATAELEMENTID')=1 set @DATAELEMENTID=@ID.query('/PARAMETERS/DATAELEMENTID').value('.', 'uniqueidentifier');    
    declare @ACCOUNTSTRUCTURES table (ACCOUNTSTRUCTUREID uniqueidentifier)
    declare @CLOSINGREQUIREMENTID uniqueidentifier

    insert @ACCOUNTSTRUCTURES values ('2F3E836D-55FF-4A5D-8477-D79703ABEC7A') --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 @dbobjectname nvarchar(2000), @dbobjecttype int, @tempID uniqueidentifier, @sql nvarchar(4000), @count int, @tmpClosingReqID uniqueidentifier, @tmpSQL nvarchar(255)
        declare @idsetregisters table (id uniqueidentifier, closingrequirementid uniqueidentifier, dbobjectname nvarchar(2000), dbobjecttype int)
        insert @idsetregisters select B.ID, A.ID, B.DBOBJECTNAME + case B.OBJECTTYPE when 1 then N'()' when 2 then N'(''' + convert(nvarchar(36), B.ID) + N''')' else '' end, B.OBJECTTYPE from dbo.CLOSINGREQUIREMENT A inner join dbo.IDSETREGISTER B on A.DATAELEMENTQUERYID=B.ID
        if @@ROWCOUNT <> 0
        begin
          set  @tmpSQL = N' where ID = '''+CONVERT(nvarchar(36), @dataelementid) + N'''';
          --REVISIT:  ordering by ID ensures a consistent order of evaluation within one DB, but not in multiple

      --DB's - for example it could result in different results in different DB's, even if the DB account structure 

      --is set up identically

      select top 1 @tempID = ID, @tmpClosingReqID=CLOSINGREQUIREMENTID, @dbobjectname=DBOBJECTNAME, @dbobjecttype=DBOBJECTTYPE from @idsetregisters order by id
          while @tempID is not null and @CLOSINGREQUIREMENTID is null
          begin
            set @sql = N'select @COUNT=count(1) from dbo.'+@dbobjectname+@tmpSQL
            exec sp_executesql @sql, N'@count int output', @count=@count output
            if @count > 0 set @CLOSINGREQUIREMENTID = @tmpClosingReqID

            delete from @idsetregisters where id = @tempID;
            set @tempID = null;
            select top 1 @tempID = ID, @tmpClosingReqID=CLOSINGREQUIREMENTID, @dbobjectname=DBOBJECTNAME, @dbobjecttype=DBOBJECTTYPE from @idsetregisters order by id;
          end
        end
    end

    if @CLOSINGREQUIREMENTID is not null
    begin

        declare @ACCOUNTCODEID uniqueidentifier; if @ID.exist('/PARAMETERS/ACCOUNTCODEID')=1 set @ACCOUNTCODEID=@ID.query('/PARAMETERS/ACCOUNTCODEID').value('.', 'uniqueidentifier');
        declare @INCOMESTATEMENTACCOUNT bit=0;
        declare @CATEGORY integer;
        SELECT @CATEGORY=dbo.[UFN_GLGETCATEGORYFROMACCTCODE](@ACCOUNTCODEID);
        if @CATEGORY IS NOT NULL if @CATEGORY=4 or @CATEGORY=5 SET @INCOMESTATEMENTACCOUNT=1;

        insert @ACCOUNTSTRUCTURES select ACCOUNTSTRUCTUREID from dbo.CLOSINGREQUIREMENTDETAIL 
        where CLOSINGREQUIREMENTID = @CLOSINGREQUIREMENTID and 
          (ACCOUNTREQUIREMENTSCODE = 3 or (@INCOMESTATEMENTACCOUNT = 1 and ACCOUNTREQUIREMENTSCODE = 2))

    end    

    select @DATALOADED = 1,
        @REQUIREDSTRUCTUREIDS = (
            select ACCOUNTSTRUCTUREID
            from
                @ACCOUNTSTRUCTURES 
            for xml raw('ITEM'),type,elements,root('REQUIREDSTRUCTUREIDS'),binary base64
        )

    return 0;