USP_REPORT_ELEMENTBALANCE

CREATE procedure dbo.USP_REPORT_ELEMENTBALANCE(
  @ASOFDATE datetime = null,
  @SELECTION uniqueidentifier = null,
  @ELEMENTID uniqueidentifier = null,
  @COLUMN int,
  @ACCOUNTSTRUCTUREID uniqueidentifier = null,
  @INCLUDENOTPOSTED bit = 0
)
with execute as owner
as
set nocount on;

  declare 
    @SQL as nvarchar(max)

  set @SQL =
  N'select PDACCOUNTSEGMENTVALUE.ID as DATAELEMENTID, PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as SHORTID, isnull(sum(isnull(JE.TRANSACTIONAMOUNT, 0) * 
    (case when ACCOUNTCODE.ID is not null then case when JE.TRANSACTIONTYPECODE = (ACCOUNTCODE.NORMALBALANCECODE-1) then 1 else -1 end else case JE.TRANSACTIONTYPECODE when 0 then 1 else -1 end end) *
    (case when ACCOUNTCODE.ID is not null then case ACCOUNTCODE.CONTRAACCOUNT when 1 then -1 else 1 end else 1 end)
  ), 0) as BALANCE
  from dbo.PDACCOUNTSEGMENTVALUE'+nchar(13);

  if @SELECTION is not null 
    set @SQL = @SQL + 
    N'inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTION) as SELECTION on PDACCOUNTSEGMENTVALUE.ID = SELECTION.[ID]'+nchar(13);

  set @SQL = @SQL +
  N'inner join dbo.JOURNALENTRY JE on JE.DATAELEMENT'+cast(@COLUMN as nvarchar(3))+N'ID = PDACCOUNTSEGMENTVALUE.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTL on JE.FINANCIALTRANSACTIONLINEITEMID = FTL.ID
    inner join dbo.FINANCIALTRANSACTION FT on FTL.FINANCIALTRANSACTIONID = FT.ID
    left join dbo.ACCOUNTCODE on PDACCOUNTSEGMENTVALUE.ID = ACCOUNTCODE.ID
  where (@ELEMENTID is null or DATAELEMENT'+cast(@COLUMN as nvarchar(3))+N'ID = @ELEMENTID)
    and (JE.ID is null or (FT.TYPECODE = 103 and JE.POSTDATE < @ASOFDATE) or (FT.TYPECODE <> 103 and FTL.POSTDATE < @ASOFDATE))
    and (@INCLUDENOTPOSTED = 1 or FTL.POSTSTATUSCODE = 2)
  group by PDACCOUNTSEGMENTVALUE.ID, SHORTDESCRIPTION'; -- Typecode 103 = Journal Entry batch


  exec sp_executesql @SQL, N'@ELEMENTID uniqueidentifier,@ASOFDATE datetime,@INCLUDENOTPOSTED bit,@SELECTION uniqueidentifier',@ELEMENTID=@ELEMENTID, @ASOFDATE=@ASOFDATE, @INCLUDENOTPOSTED=@INCLUDENOTPOSTED, @SELECTION=@SELECTION;