USP_DATALIST_GL_CONTROLACCOUNT

CREATE procedure dbo.USP_DATALIST_GL_CONTROLACCOUNT
(
  @FINANCIALSYSTEMID uniqueidentifier,
  @ACCOUNTTYPECODEFILTER tinyint = null
)
as
begin
    set nocount on;

  declare @SEPARATOR nvarchar(1);
  select top 1 @SEPARATOR = SEPARATOR from dbo.PDACCOUNTSTRUCTURE;

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

  declare @TempTbl table
  (
  CONTROLACCOUNTID uniqueidentifier,
  SHORTDESCRIPTION nvarchar(100),
  SEQUENCE int,
  LENGTH int,
  ACCOUNTTYPE nvarchar(17),
  ACCOUNTTYPECODE tinyint)

  insert into @TempTbl
  select t2.ID, t3.SHORTDESCRIPTION, t2.SEQUENCE, t2.LENGTH, t2.ACCOUNTTYPE, t2.ACCOUNTTYPECODE from
  (select t.NUM, CONTROLACCOUNT.ID, t.LENGTH, t.SEQUENCE, CONTROLACCOUNT.ACCOUNTTYPE, CONTROLACCOUNT.ACCOUNTTYPECODE
    from
      (select NUMBERS.NUM, PDACCOUNTSTRUCTURE.LENGTH, PDACCOUNTSTRUCTURE.SEQUENCE
        from NUMBERS
        inner join PDACCOUNTSTRUCTURE on NUMBERS.NUM = PDACCOUNTSTRUCTURE.SEQUENCE
        where ISBASICGL = 0 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) t
    cross join  CONTROLACCOUNT
  where CONTROLACCOUNT.SYSTEMID = @FINANCIALSYSTEMID) t2
  left join
  (select unPvt.ID, PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION, PDACCOUNTSTRUCTURE.SEQUENCE, PDACCOUNTSTRUCTURE.LENGTH
  from 
      (select ID, 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
      from dbo.CONTROLACCOUNT) p
      unpivot
      (DATAELEMENTID for dColumn in
          (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)) as unPvt
  inner join PDACCOUNTSEGMENTVALUE on unPvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
  inner join PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID) t3
  on t2.ID = t3.ID and t2.NUM = t3.SEQUENCE

  select distinct CONTROLACCOUNTID, ACCOUNTTYPE, ACCOUNTTYPECODE,
  substring((select isnull(SHORTDESCRIPTION+@SEPARATOR,replicate('*', LENGTH)+@SEPARATOR)
        from @TempTbl T
        where T.CONTROLACCOUNTID = T2.CONTROLACCOUNTID
        order by T.SEQUENCE
        for xml path(''), type).value('.','nvarchar(max)'), 1, LEN((select isnull(SHORTDESCRIPTION+@SEPARATOR,replicate('*', LENGTH)+@SEPARATOR)
        from @TempTbl T
        where T.CONTROLACCOUNTID = T2.CONTROLACCOUNTID
        order by T.SEQUENCE
        for xml path(''), type).value('.','nvarchar(max)'))-1) as APSUMMARYACCOUNT
   from @TempTbl T2
   where (ACCOUNTTYPE = (select top 1 ACCOUNTTYPE from dbo.CONTROLACCOUNT where ACCOUNTTYPECODE = @ACCOUNTTYPECODEFILTER) or
    @ACCOUNTTYPECODEFILTER is null)
end