USP_REPORT_GLPREPOST

create procedure dbo.USP_REPORT_GLPREPOST
@ID varchar(36)
with execute as owner
as
declare @IncludeByPostDate as bit
declare @PostThroughDateCode as int
declare @SummarizeByDateCode as int
declare @PostThroughDate as date
declare @DebitTotal money
declare @CreditTotal money

select @IncludeByPostDate = INCLUDEBYPOSTDATE, @PostThroughDateCode = POSTTHROUGHDATECODE, @SummarizeByDateCode = SUMMARIZEBYDATECODE
from dbo.GLPOSTPROCESS
where GLPOSTPROCESS.ID = @ID;

create table #JOURNALENTRIESTOPOST (ID uniqueidentifier, GLACCOUNTID uniqueidentifier, ACCOUNTSTRING varchar(130), TRANSACTIONTYPE smallint, AMOUNT money, POSTDATE date, GLRECORD bit default 0)

exec dbo.USP_GLPOSTPROCESS_INSERTRECORDS @ID, @IncludeByPostDate, @PostThroughDateCode

select @DebitTotal = sum(case TRANSACTIONTYPE when 0 then AMOUNT else 0 end), @CreditTotal = sum(case TRANSACTIONTYPE when 1 then AMOUNT else 0 end) from #JOURNALENTRIESTOPOST;

with CTE
as
(select FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID as ID, JOURNALENTRY.CLASSCODE, JOURNALENTRY.JOURNALCODEID, JOURNALENTRY.AMOUNT, JOURNALENTRY.TRANSACTIONTYPECODE, 
    case @SummarizeByDateCode 
        when 1 then GLFISCALPERIOD.ENDDATE
        when 0 then FINANCIALTRANSACTIONLINEITEM.POSTDATE
        end as DateEnd, 
    JOURNALENTRY.GLACCOUNTID, 
    case @SummarizeByDateCode 
        when 1 then GLFISCALPERIOD.STARTDATE
        when 0 then FINANCIALTRANSACTIONLINEITEM.POSTDATE
        end as DateStart,
    case GLPOSTPROCESSPOSTMETHOD.POSTMETHODCODE
        when 1 then JOURNALENTRY.ID
        when 2 then FINANCIALTRANSACTION.ID
        when 3 then '00000000-0000-0000-0000-000000000000'
        end as SumDetGroupingCol, FINANCIALTRANSACTIONLINEITEM.POSTDATE, GLACCOUNT.ACCOUNTSTRING, FINANCIALTRANSACTION.TYPE, FINANCIALTRANSACTION.DESCRIPTION
from dbo.JOURNALENTRY 
inner join #JOURNALENTRIESTOPOST on JOURNALENTRY.ID = #JOURNALENTRIESTOPOST.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE on FINANCIALTRANSACTION.TYPECODE = FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE
inner join dbo.GLPOSTPROCESSPOSTMETHOD on FINANCIALSYSTEMTRANSACTIONTYPE.ID = GLPOSTPROCESSPOSTMETHOD.FINANCIALSYSTEMTRANSACTIONTYPEID AND GLPOSTPROCESSPOSTMETHOD.GLPOSTPROCESSID = @ID AND JOURNALENTRY.TRANSACTIONTYPECODE = GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE
inner join dbo.GLFISCALPERIOD on FINANCIALTRANSACTIONLINEITEM.POSTDATE between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE and GLFISCALPERIOD.CLOSED = 0
inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
and JOURNALENTRY.GLACCOUNTID is not null)
select ACCOUNTSTRING as Link, ACCOUNTSTRING, NULL as DESCRIPTION, DateEnd as PostDate, TRANSACTIONTYPECODE, sum(AMOUNT) as Amount, CLASSCODE, JOURNALCODEID, DateEnd, DateStart, SumDetGroupingCol, 0 as ISSubsidiary, @DebitTotal as DEBITTOTAL, @CreditTotal as CREDITTOTAL
from CTE
group by TRANSACTIONTYPECODE, CLASSCODE, JOURNALCODEID, DateEnd, ACCOUNTSTRING, DateStart, SumDetGroupingCol
union all
select 'http://www.blackbaud.com?'+replace(TYPE,' ','')+'ID='+CONVERT(nvarchar(36),ID), ACCOUNTSTRING, isnull(nullif(DESCRIPTION,''),TYPE), PostDate, TRANSACTIONTYPECODE, AMOUNT, CLASSCODE, JOURNALCODEID, DateEnd, DateStart, SumDetGroupingCol, 1, @DebitTotal, @CreditTotal
from CTE
order by ACCOUNTSTRING, DateEnd, DateStart, TRANSACTIONTYPECODE, SumDetGroupingCol, ISSubsidiary, CLASSCODE, JOURNALCODEID, PostDate