USP_REPORT_GLPOST

    create procedure dbo.USP_REPORT_GLPOST
    (
    @ID varchar(36)
    )
    with execute as owner
    as

    declare @DebitTotal money
    declare @CreditTotal money

    select @DebitTotal = sum(case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then JOURNALENTRY.TRANSACTIONAMOUNT else 0 end),
        @CreditTotal = sum(case JOURNALENTRY.TRANSACTIONTYPECODE when 1 then JOURNALENTRY.TRANSACTIONAMOUNT else 0 end)
    from
    dbo.FINANCIALBATCH inner join dbo.JOURNALENTRY on FINANCIALBATCH.ID = JOURNALENTRY.FINANCIALBATCHID
    where FINANCIALBATCH.GLPOSTPROCESSSTATUSID = @ID


    select GLACCOUNT.ACCOUNTSTRING, case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then JOURNALENTRY.TRANSACTIONAMOUNT else 0 end as DebitAmount,
        case JOURNALENTRY.TRANSACTIONTYPECODE when 1 then JOURNALENTRY.TRANSACTIONAMOUNT else 0 end as CreditAmount, JOURNALENTRY.POSTDATE, @DebitTotal as DEBITTOTAL, @CreditTotal as CREDITTOTAL
    from
    dbo.FINANCIALBATCH inner join dbo.JOURNALENTRY on FINANCIALBATCH.id = JOURNALENTRY.FINANCIALBATCHID
    inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
    where FINANCIALBATCH.GLPOSTPROCESSSTATUSID = @ID
    order by GLACCOUNT.ACCOUNTSTRING, JOURNALENTRY.POSTDATE