USP_REPORT_ACCOUNTCODE_DETAIL

CREATE procedure dbo.USP_REPORT_ACCOUNTCODE_DETAIL(
 @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);

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

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



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

  set @SQLTOEXEC = 
                    'select    
             PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as SHORTID,
             PDACCOUNTSEGMENTVALUE.DESCRIPTION as DESCRIPTION,
             PDACCOUNTSTRUCTURE.DESCRIPTION as ELEMENTNAME,
             T.POSTDATE as TRANSACTIONDATE,
            isnull(T.SEQUENCE,0) AS SEQUENCE,
             isnull(T.BATCHNUMBER,0) AS BATCHNUMBER,
             T.COMMENT AS JOURNALREF,
             (select case T.TRANSACTIONTYPECODE WHEN 0 THEN T.AMOUNT else 0 END) AS DEBITAMOUNT,
            (select case T.TRANSACTIONTYPECODE WHEN 1 THEN T.AMOUNT else 0 END) AS CREDITAMOUNT,
             case when AC.ID is not null then case AC.NORMALBALANCECODE when 2 then -1 else 1 end else 1 end AS BALANCETYPE,
             CASE WHEN T.TYPECODE = 101 THEN ''http://www.blackbaud.com?INVOICEID='' + CONVERT(nvarchar(36), T.FTID) ELSE '''' END as [INVOICEID],
             CASE WHEN T.TYPECODE = 102 THEN ''http://www.blackbaud.com?CREDITMEMOID='' + CONVERT(nvarchar(36), T.FTID) ELSE '''' END as [CREDITMEMOID],
             CASE WHEN T.TYPECODE = 103 THEN ''http://www.blackbaud.com?JOURNALENTRYBATCHID='' + CONVERT(nvarchar(36), T.FTLIID) ELSE '''' END as [JOURNALENTRYBATCHID],
             isnull(BALANCE.BALANCE,0) as BALANCE,
             case when AC.ID is not null then case AC.CONTRAACCOUNT when 1 then -1 else 1 end else 1 end as CONTRA,
             T.JOURNAL as JOURNAL,
             T.TYPECODE as TYPECODE,
             T.TRANDESCRIPTION as TRANDESCRIPTION,
             (select case T.POSTSTATUSCODE when 2 then 1 else 0 end) as POSTED,
             T.NAME as VENDOR
            from dbo.PDACCOUNTSEGMENTVALUE' + nchar(13);

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

                set @SQLTOEXEC = @SQLTOEXEC + 
            N'left join 
            (select JE.DATAELEMENT' + cast(@COLUMN as nvarchar(3)) + 'ID as DATAELEMENTID, JE.POSTDATE, JE.SEQUENCE, JEB.BATCHNUMBER, JE.COMMENT, JE.TRANSACTIONTYPECODE, 
            JE.AMOUNT, JC.DESCRIPTION as JOURNAL, FT.TYPECODE, FT.DESCRIPTION as TRANDESCRIPTION, FT.POSTSTATUSCODE, CONSTITUENT.NAME,
            FT.ID as FTID, FTLI.ID as FTLIID
            from dbo.JOURNALENTRY JE
            left join FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
            left join FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
            left join dbo.CONSTITUENT on CONSTITUENT.ID = FT.CONSTITUENTID
            left join dbo.JOURNALENTRYBATCH JEB on FT.ID = JEB.ID
            left join dbo.JOURNALCODE JC on JE.JOURNALCODEID = JC.ID
            where JE.POSTDATE between @STARTDATE and @ENDDATE
            and (@INCLUDENOTPOSTED = 1 or FT.POSTSTATUSCODE = 2)) T on T.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
            left join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
            left join dbo.ACCOUNTCODE AC on PDACCOUNTSEGMENTVALUE.ID = AC.ID
            left join #BALANCE BALANCE on BALANCE.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
                    where 
                          (@ACCOUNTCODEID is null or 
                          T.DATAELEMENTID = @ACCOUNTCODEID 
              or PDACCOUNTSEGMENTVALUE.ID = @ACCOUNTCODEID)
              and PDACCOUNTSTRUCTURE.SEGMENTCOLUMN = @COLUMN
              and (@ACCOUNTCODEID is not null or @INCLUDEINACTIVE = 1 or PDACCOUNTSEGMENTVALUE.ISACTIVE = 1)
              and (@ACCOUNTCODEID is not null or @INCLUDENOACTIVITY = 1 or T.DATAELEMENTID is not null or isnull(BALANCE.BALANCE,0) <> 0)
                          '

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

        drop table #BALANCE;