USP_DATALIST_BALANCINGENTRY

CREATE procedure dbo.USP_DATALIST_BALANCINGENTRY
as
    set nocount on;

  declare @TempTblDebit table
  (
  BALANCINGENTRYID uniqueidentifier,
  SHORTDESCRIPTION nvarchar(100),
  SEGMENTSEQUENCE int,
  LENGTH int)

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

  insert into @TempTblDebit
  select t2.ID, t3.SHORTDESCRIPTION, t2.SEGMENTSEQUENCE, t2.LENGTH from
  (select t.NUM, BALANCINGENTRY.ID, t.LENGTH, t.SEGMENTSEQUENCE
  from
  (select NUMBERS.NUM, PDACCOUNTSTRUCTURE.LENGTH, PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
  from NUMBERS
  inner join PDACCOUNTSTRUCTURE on NUMBERS.NUM = PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
  where PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1
  and ISBASICGL = 0
  and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) t
  cross join
  BALANCINGENTRY) t2
  left join
  (select unPvt.BALANCINGENTRYID as ID, PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION, PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE, PDACCOUNTSTRUCTURE.LENGTH
  from 
      (select BALANCINGENTRYID, 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.BALANCINGENTRYELEMENT
      where BALANCINGENTRYTYPECODE = 1) 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.SEGMENTSEQUENCE

  declare @TempTblCredit table
  (
  BALANCINGENTRYID uniqueidentifier,
  SHORTDESCRIPTION nvarchar(100),
  SEGMENTSEQUENCE int,
  LENGTH int)

  insert into @TempTblCredit
  select t2.ID, t3.SHORTDESCRIPTION, t2.SEGMENTSEQUENCE, t2.LENGTH from
  (select t.NUM, BALANCINGENTRY.ID, t.LENGTH, t.SEGMENTSEQUENCE
  from
  (select NUMBERS.NUM, PDACCOUNTSTRUCTURE.LENGTH, PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
  from NUMBERS
  inner join PDACCOUNTSTRUCTURE on NUMBERS.NUM = PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
  where PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1
  and ISBASICGL = 0
  and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) t
  cross join
  BALANCINGENTRY) t2
  left join
  (select unPvt.BALANCINGENTRYID as ID, PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION, PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE, PDACCOUNTSTRUCTURE.LENGTH
  from 
      (select BALANCINGENTRYID, 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.BALANCINGENTRYELEMENT
      where BALANCINGENTRYTYPECODE = 2) 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.SEGMENTSEQUENCE

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

  select distinct BE.ID,
  BE.DESCRIPTION,
  substring((select isnull(SHORTDESCRIPTION+@SEPARATOR,replicate('*', LENGTH)+@SEPARATOR)
        from @TempTblDebit TD
        where TD.BALANCINGENTRYID = BE.ID
        order by TD.SEGMENTSEQUENCE
        for xml path(''), type).value('.','nvarchar(max)'), 1, LEN((select isnull(SHORTDESCRIPTION+@SEPARATOR,replicate('*', LENGTH)+@SEPARATOR)
        from @TempTblDebit TD
        where TD.BALANCINGENTRYID = BE.ID
        order by TD.SEGMENTSEQUENCE
        for xml path(''), type).value('.','nvarchar(max)'))-1) as DEBITACCOUNT,
     substring((select isnull(SHORTDESCRIPTION+@SEPARATOR,replicate('*', LENGTH)+@SEPARATOR)
        from @TempTblCredit TC
        where TC.BALANCINGENTRYID = BE.ID
        order by TC.SEGMENTSEQUENCE
        for xml path(''), type).value('.','nvarchar(max)'), 1, LEN((select isnull(SHORTDESCRIPTION+@SEPARATOR,replicate('*', LENGTH)+@SEPARATOR)
        from @TempTblCredit TC
        where TC.BALANCINGENTRYID = BE.ID
        order by TC.SEGMENTSEQUENCE
        for xml path(''), type).value('.','nvarchar(max)'))-1) as CREDITACCOUNT
   from dbo.BALANCINGENTRY BE
   order by BE.DESCRIPTION asc