USP_DATALIST_GL_CONTROLACCOUNTTREASURY

CREATE procedure dbo.USP_DATALIST_GL_CONTROLACCOUNTTREASURY
(
  @FINANCIALSYSTEMID uniqueidentifier,
  @BANKACCOUNTID uniqueidentifier = 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,
  BANKACCOUNT nvarchar(100),
  ACCOUNTTYPE nvarchar(17)
  )

  insert into @TempTbl
  select t2.ID, t3.SHORTDESCRIPTION, t2.SEQUENCE, t2.LENGTH,  
    (select BANKACCOUNT.ACCOUNTNAME from dbo.BANKACCOUNT where BANKACCOUNT.ID = (select BANKACCOUNTID from dbo.CONTROLACCOUNTTREASURY where ID = t2.ID)),
  t2.ACCOUNTTYPE
  from
    (select t.NUM, CONTROLACCOUNT.ID, t.LENGTH, t.SEQUENCE, CONTROLACCOUNT.ACCOUNTTYPE
  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,
  BANKACCOUNT as BANKACCOUNT,
    ACCOUNTTYPE,
  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 TREASURYCASHACCOUNT
   from @TempTbl T2
   where (BANKACCOUNT = (select ACCOUNTNAME from dbo.BANKACCOUNT where ID = @BANKACCOUNTID) or
    @BANKACCOUNTID is null)

end