USP_DATAFORMTEMPLATE_VIEW_ACCOUNTINGELEMENTRELATIONSHIPDETAILS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ACCOUNTINGELEMENTRELATIONSHIPDETAILS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @RELATIONSHIPID nvarchar(60) = null output,
  @DESCRIPTION nvarchar(60) = null output,
  @BASEELEMENTNAME nvarchar(60) = null output,
  @RESTRICTION nvarchar(60) = null output,
  @DATAELEMENTS xml= null output

) with execute as owner
as

    set nocount on;

    set @DATALOADED = 0;

    select @DATALOADED = 1,
       @RELATIONSHIPID = ACCOUNTINGELEMENTRELATIONSHIP.RELATIONSHIPID,
           @DESCRIPTION = ACCOUNTINGELEMENTRELATIONSHIP.DESCRIPTION, @BASEELEMENTNAME = PDACCOUNTSTRUCTURE.DESCRIPTION,
       @RESTRICTION = ACCOUNTINGELEMENTRELATIONSHIP.RESTRICTION
    from dbo.ACCOUNTINGELEMENTRELATIONSHIP
  inner join dbo.PDACCOUNTSTRUCTURE on ACCOUNTINGELEMENTRELATIONSHIP.BASEELEMENTACCOUNTSTRUCTUREID = 
  PDACCOUNTSTRUCTURE.ID
    where ACCOUNTINGELEMENTRELATIONSHIP.ID = @ID


  declare @SEQUENCE int, @MAXSEQUENCE int;

  Select @SEQUENCE = MIN(SEGMENTCOLUMN), @MAXSEQUENCE = Max(SEGMENTCOLUMN) from dbo.PDACCOUNTSTRUCTURE

  create table #ELEMENTS (ACCOUNTSTRUCTUREID uniqueidentifier, SELECTEDELEMENTS nvarchar(max) collate DATABASE_DEFAULT);

  with ACCOUNTSTEMP(ID, SELECTEDELEMENTS) as (select distinct null,
      (SUBSTRING((select distinct (', ' + LA.ACCOUNTSTRING)
      from dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
      inner join dbo.GLACCOUNT LA on LA.ID = AERD.ACCOUNTID
      where AERD.ACCOUNTINGELEMENTRELATIONSHIPID = @ID and AERD.ACCOUNTID is not null
      order by ', ' + LA.ACCOUNTSTRING
      for xml PATH( '' )),3,105)) AS SELECTEDELEMENTS)      
      insert into #ELEMENTS select ID,SELECTEDELEMENTS from ACCOUNTSTEMP where SELECTEDELEMENTS is not null;

  with BASEELEMENTSTEMP(ID,SELECTEDELEMENTS) as (select  distinct ASR.ID, 
      (SUBSTRING((select distinct (', ' + DE.SHORTDESCRIPTION)
      from dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
      inner join dbo.PDACCOUNTSEGMENTVALUE DE on DE.ID = AERD.BASEELEMENTID
      where AERD.ACCOUNTINGELEMENTRELATIONSHIPID = @ID
      order by ', ' + DE.SHORTDESCRIPTION
      for xml PATH( '' )),3,105)) AS SELECTEDELEMENTS
      from PDACCOUNTSTRUCTURE ASR inner join ACCOUNTINGELEMENTRELATIONSHIP AER
      on ASR.ID = AER.BASEELEMENTACCOUNTSTRUCTUREID and 
      AER.ID = @ID)
      insert into #ELEMENTS select ID,SELECTEDELEMENTS from BASEELEMENTSTEMP where SELECTEDELEMENTS is not null;

  While @SEQUENCE <= @MAXSEQUENCE
      begin
          declare @SQL nvarchar(max)

          set @SQL = N'with DETAILS(ID,SELECTEDELEMENTS) as (select ASR.ID, (substring((SELECT  DISTINCT ( '', '' + DE.SHORTDESCRIPTION )
      from dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
      inner join dbo.PDACCOUNTSEGMENTVALUE DE on DE.ID = AERD.DATAELEMENT' + CAST(@SEQUENCE AS NVARCHAR(3)) + 'ID
      where AERD.ACCOUNTINGELEMENTRELATIONSHIPID = @ID and AERD.ACCOUNTID is null
      ORDER BY '', '' + DE.SHORTDESCRIPTION
      for xml PATH( '''' )),3,105)) AS SELECTEDELEMENTS
     from dbo.PDACCOUNTSTRUCTURE ASR where SEGMENTCOLUMN=' + CAST(@SEQUENCE AS NVARCHAR(3)) + '
     and ASR.ID <> (select BASEELEMENTACCOUNTSTRUCTUREID from ACCOUNTINGELEMENTRELATIONSHIP where ID = @ID))
     insert into #ELEMENTS select ID,SELECTEDELEMENTS from DETAILS where SELECTEDELEMENTS is not null';

      exec sp_executesql @SQL, N'@ID uniqueidentifier', @ID = @ID

       set @SEQUENCE = @SEQUENCE + 1 

    end


  set @DATAELEMENTS = 
    (select coalesce(ASR.ID,newid()) as ID, coalesce(ASR.DESCRIPTION,'Account') as NAME, 
     case when len(EM.SELECTEDELEMENTS) > 100 then substring(EM.SELECTEDELEMENTS, 0, 100) + '...' else EM.SELECTEDELEMENTS end as SELECTEDELEMENTS
         from #ELEMENTS EM
     left join dbo.PDACCOUNTSTRUCTURE ASR on ASR.ID = EM.ACCOUNTSTRUCTUREID
     for xml raw('ITEM'),type,elements,root('DATAELEMENTS'),BINARY BASE64)

   drop table #ELEMENTS;           
    return 0;