USP_REPORT_ACCOUNTCODE_SUMMARY

      CREATE procedure dbo.USP_REPORT_ACCOUNTCODE_SUMMARY
      (
         @STARTDATE datetime = null,
         @ENDDATE datetime = null,
         @DATETYPE nvarchar(36) = null,
         @ACCOUNTCODEQUERY uniqueidentifier = null,
         @ACCOUNTCODEID uniqueidentifier = null,
         @ACCOUNTSTRUCTUREID uniqueidentifier = null,
         @INCLUDEINACTIVE bit = 0,
         @INCLUDENOTPOSTED bit = 0,
         @INCLUDENOACTIVITY bit = 0
      )
      with execute as owner
      as
      set nocount on;

        declare @SQLTOEXEC nvarchar(max);
        create table #BALANCE (DATAELEMENTID uniqueidentifier, SHORTID nvarchar(100) collate DATABASE_DEFAULT, BALANCE money);
        create table #DATAELEMENTS (DATAELEMENTID uniqueidentifier)

        declare @COLUMN as int;
          select @COLUMN = SEGMENTCOLUMN 
            from PDACCOUNTSTRUCTURE
            where ID=@ACCOUNTSTRUCTUREID;

        exec dbo.USP_REPORT_ELEMENTACTIVITY_GETSTARTENDDATES @DATETYPE, @STARTDATE output, @ENDDATE output;

        insert into #BALANCE exec dbo.USP_REPORT_ELEMENTBALANCE @STARTDATE, @ACCOUNTCODEQUERY, @ACCOUNTCODEID, @COLUMN, @ACCOUNTSTRUCTUREID, @INCLUDENOTPOSTED

        declare @QUERYSTRING nvarchar(200)
        if @ACCOUNTCODEQUERY is not null
            set @QUERYSTRING = ' inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@ACCOUNTCODEQUERY) as SELECTION on JE.DATAELEMENT'+cast(@COLUMN as nvarchar(3))+N'ID = SELECTION.[ID]'
        else
          set @QUERYSTRING = ''

        if @ACCOUNTCODEID is not null
        begin
          insert into #DATAELEMENTS(DATAELEMENTID)
          values(@ACCOUNTCODEID)
        end
        else
        begin
          if @ACCOUNTCODEQUERY is not null
          begin
            insert into #DATAELEMENTS(DATAELEMENTID)
            select SELECTION.ID
            from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@ACCOUNTCODEQUERY) as SELECTION
            inner join dbo.PDACCOUNTSEGMENTVALUE on selection.ID = PDACCOUNTSEGMENTVALUE.ID
            where @INCLUDEINACTIVE = 1 or PDACCOUNTSEGMENTVALUE.ISACTIVE = 1
          end
          else
          begin
            insert into #DATAELEMENTS(DATAELEMENTID)
            select ID
            from dbo.PDACCOUNTSEGMENTVALUE
            where PDACCOUNTSTRUCTUREID = @ACCOUNTSTRUCTUREID
            and (@INCLUDEINACTIVE = 1 or ISACTIVE = 1)
          end
        end

        set @SQLTOEXEC = 
                    'select    
                        FP.ENDDATE as TRANSACTIONDATE,
            DE.SHORTDESCRIPTION as SHORTID,
            DE.DESCRIPTION as DESCRIPTION,
            ACTST.DESCRIPTION as ELEMENTNAME,
            isnull(SUM(case T4.TRANSACTIONTYPECODE WHEN 0 THEN T4.AMOUNT END),0) as DEBITAMOUNT,
            isnull(SUM(case T4.TRANSACTIONTYPECODE WHEN 1 THEN T4.AMOUNT END),0) as CREDITAMOUNT,
            isnull(SUM(T4.AMOUNT * 
            (case when ACCOUNTCODE.ID is not null then
              case when T4.TRANSACTIONTYPECODE = (ACCOUNTCODE.NORMALBALANCECODE-1) then 
                case when ACCOUNTCODE.ID is not null then case ACCOUNTCODE.CONTRAACCOUNT when 1 then -1 else 1 end else 1 end 
                else case when ACCOUNTCODE.ID is not null then case ACCOUNTCODE.CONTRAACCOUNT when 1 then 1 else -1 end else -1 end end
             else case T4.TRANSACTIONTYPECODE when 0 then 1 else -1 end end)),0) as AMOUNT,
             isnull(BALANCE.BALANCE,0) as BALANCE,
             FP.GLFISCALYEARID as FISCALYEARID
            from
              (select * from
                (select DE.DATAELEMENTID 
                from #DATAELEMENTS DE
                left join (select distinct JE.DATAELEMENT'+cast(@COLUMN as nvarchar(3))+N'ID as DATAELEMENTID
                  from dbo.FINANCIALTRANSACTION FT
                  inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FINANCIALTRANSACTIONID
                  inner join dbo.JOURNALENTRY JE on FTLI.ID = JE.FINANCIALTRANSACTIONLINEITEMID' + @QUERYSTRING +
                  ' where ((FT.TYPECODE = 103 and JE.POSTDATE between @STARTDATE and @ENDDATE)
                  or (FT.TYPECODE <> 103 and FTLI.POSTDATE between @STARTDATE and @ENDDATE))
                  and (@ACCOUNTCODEID is null or JE.DATAELEMENT'+cast(@COLUMN as nvarchar(3))+N'ID = @ACCOUNTCODEID)
                  and (@INCLUDENOTPOSTED = 1 or FTLI.POSTSTATUSCODE = 2)) T on DE.DATAELEMENTID = T.DATAELEMENTID
                left join #BALANCE BALANCE2 on BALANCE2.DATAELEMENTID = DE.DATAELEMENTID
                where @ACCOUNTCODEID is not null or @INCLUDENOACTIVITY = 1 or T.DATAELEMENTID is not null or isnull(BALANCE2.BALANCE,0) <> 0) T5
              cross join
                (select ID as FISCALPERIODID
                from dbo.GLFISCALPERIOD
                where ((STARTDATE >= @STARTDATE and STARTDATE <= @ENDDATE)
                or (ENDDATE >= @STARTDATE and ENDDATE <= @ENDDATE))) T2) T3
            inner join dbo.PDACCOUNTSEGMENTVALUE DE on T3.DATAELEMENTID = DE.ID
            inner join dbo.PDACCOUNTSTRUCTURE ACTST on DE.PDACCOUNTSTRUCTUREID = ACTST.ID
            inner join dbo.GLFISCALPERIOD FP on T3.FISCALPERIODID = FP.ID
            left outer join
              (select JE.AMOUNT, JE.TRANSACTIONTYPECODE, JE.DATAELEMENT'+cast(@COLUMN as nvarchar(3))+N'ID as DATAELEMENTID,
              case FT.TYPECODE when 103 then JE.POSTDATE else FTLI.POSTDATE end as TRANSACTIONDATE
              from dbo.FINANCIALTRANSACTION FT
              inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FINANCIALTRANSACTIONID
              inner join dbo.JOURNALENTRY JE on FTLI.ID = JE.FINANCIALTRANSACTIONLINEITEMID' + @QUERYSTRING +
              ' where ((FT.TYPECODE = 103 and JE.POSTDATE between @STARTDATE and @ENDDATE)
              or (FT.TYPECODE <> 103 and FTLI.POSTDATE between @STARTDATE and @ENDDATE))
              and (@INCLUDENOTPOSTED = 1 or FTLI.POSTSTATUSCODE = 2)) T4 on T4.TRANSACTIONDATE between FP.STARTDATE and FP.ENDDATE and T4.DATAELEMENTID = T3.DATAELEMENTID
            left outer join dbo.ACCOUNTCODE on DE.ID = ACCOUNTCODE.ID
            left join #BALANCE BALANCE on BALANCE.DATAELEMENTID = DE.ID
            group by DE.SHORTDESCRIPTION, DE.DESCRIPTION, FP.ENDDATE, ACTST.DESCRIPTION, BALANCE.BALANCE, FP.GLFISCALYEARID';                        

                exec sp_executesql @SQLTOEXEC, 
                    N'@STARTDATE datetime, @ENDDATE datetime, @ACCOUNTCODEID uniqueidentifier, @INCLUDENOTPOSTED bit, @INCLUDENOACTIVITY bit, @ACCOUNTCODEQUERY uniqueidentifier',
                    @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @ACCOUNTCODEID=@ACCOUNTCODEID, @INCLUDENOTPOSTED=@INCLUDENOTPOSTED, @INCLUDENOACTIVITY=@INCLUDENOACTIVITY, @ACCOUNTCODEQUERY=@ACCOUNTCODEQUERY;