USP_GLPOSTREPORTBALANCINGINFO

CREATE procedure dbo.USP_GLPOSTREPORTBALANCINGINFO @ID varchar(36)
with execute as owner
as
begin

select GLFISCALYEAR.DESCRIPTION, GLFISCALPERIOD.SEQUENCE, sum(case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then JOURNALENTRY.TRANSACTIONAMOUNT else 0 end) as DEBITS,
    sum(case JOURNALENTRY.TRANSACTIONTYPECODE when 1 then JOURNALENTRY.TRANSACTIONAMOUNT else 0 end) as CREDITS
from
dbo.FINANCIALBATCH inner join dbo.JOURNALENTRY on FINANCIALBATCH.ID = JOURNALENTRY.FINANCIALBATCHID
inner join dbo.GLFISCALPERIOD on JOURNALENTRY.POSTDATE between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
inner join dbo.GLFISCALYEAR on GLFISCALPERIOD.GLFISCALYEARID = GLFISCALYEAR.ID
where FINANCIALBATCH.GLPOSTPROCESSSTATUSID = @ID
group by GLFISCALYEAR.DESCRIPTION, GLFISCALPERIOD.SEQUENCE
end