USP_POST

CREATE procedure [dbo].[USP_POST]
( @CHANGEAGENTID as uniqueidentifier
 ,@FINANCIALBATCHID as uniqueidentifier
 ,@FINANCIALSYSTEM as uniqueidentifier
 ,@SUMMARIZEBYDATECRITERIA as tinyint = 1    --1=Fiscal Period, 2=Week, 3=Day

)
as
set nocount on
declare @WorkTable table (ID uniqueidentifier, CLASSCODE tinyint, JOURNALCODEID uniqueidentifier, BASEAMOUNT money, TRANSACTIONAMOUNT money, ORGANIZATIONAMOUNT money, TRANSACTIONTYPECODE smallint, POSTDATE datetime, GLACCOUNTID uniqueidentifier, DATEFROM datetime, PRIMARY KEY clustered (TRANSACTIONTYPECODE,POSTDATE,GLACCOUNTID)  )
declare @CHANGEDATE datetime = getdate()

if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

with CTE
as
(select JOURNALENTRY.CLASSCODE, JOURNALENTRY.JOURNALCODEID, JOURNALENTRY.BASEAMOUNT, JOURNALENTRY.TRANSACTIONAMOUNT, JOURNALENTRY.ORGAMOUNT, JOURNALENTRY.TRANSACTIONTYPECODE, 
    case @SUMMARIZEBYDATECRITERIA 
        when 1 then GLFISCALPERIOD.ENDDATE
        when 2 then FINANCIALTRANSACTION.POSTDATE
        when 3 then case when dateadd(d,7-datepart(dw,FINANCIALTRANSACTION.POSTDATE),FINANCIALTRANSACTION.POSTDATE) < GLFISCALPERIOD.ENDDATE then dateadd(d,7-datepart(dw,FINANCIALTRANSACTION.POSTDATE),FINANCIALTRANSACTION.POSTDATE) else GLFISCALPERIOD.ENDDATE end 
        end as DateEnd, 
    JOURNALENTRY.GLACCOUNTID, 
    case @SUMMARIZEBYDATECRITERIA 
        when 1 then GLFISCALPERIOD.STARTDATE
        when 2 then FINANCIALTRANSACTION.POSTDATE
        when 3 then case when dateadd(d,-(datepart(dw,FINANCIALTRANSACTION.POSTDATE)-1),FINANCIALTRANSACTION.POSTDATE) > GLFISCALPERIOD.STARTDATE then dateadd(d,-(datepart(dw,FINANCIALTRANSACTION.POSTDATE)-1),FINANCIALTRANSACTION.POSTDATE) else GLFISCALPERIOD.STARTDATE end 
        end as DateStart
from dbo.JOURNALENTRY 
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.GLFISCALPERIOD on FINANCIALTRANSACTION.POSTDATE between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE and GLFISCALPERIOD.CLOSED = 0
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
and FINANCIALSYSTEMTRANSACTIONTYPE.FINANCIALSYSTEMID = @FINANCIALSYSTEM
and JOURNALENTRY.GLACCOUNTID is not null)
insert into @WorkTable (ID, CLASSCODE, JOURNALCODEID, BASEAMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONTYPECODE, POSTDATE, GLACCOUNTID, DATEFROM)
select newid(), CLASSCODE, JOURNALCODEID, sum(BASEAMOUNT), sum(TRANSACTIONAMOUNT), sum(ORGAMOUNT), TRANSACTIONTYPECODE, 
    DateEnd, GLACCOUNTID, DateStart
from CTE
group by TRANSACTIONTYPECODE, CLASSCODE, JOURNALCODEID, DateEnd, GLACCOUNTID, DateStart

begin tran

begin try
insert into dbo.JournalEntry(ID, CLASSCODE, JOURNALCODEID, SEQUENCE, BASEAMOUNT, TRANSACTIONAMOUNT, ORGAMOUNT, TRANSACTIONTYPECODE, POSTDATE,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLACCOUNTID, FINANCIALBATCHID) 
select ID, CLASSCODE, JOURNALCODEID, row_number() over (order by TRANSACTIONTYPECODE, POSTDATE), BASEAMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONTYPECODE, POSTDATE,
    @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, GLACCOUNTID, @FINANCIALBATCHID
from @WorkTable

update dbo.JournalEntry
set SummaryID = TV1.ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from dbo.JOURNALENTRY 
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 @WorkTable TV1 on JOURNALENTRY.TRANSACTIONTYPECODE = TV1.TRANSACTIONTYPECODE
    and JOURNALENTRY.GLACCOUNTID= TV1.GLACCOUNTID
    and FINANCIALTRANSACTION.POSTDATE between TV1.DATEFROM and TV1.POSTDATE
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
and FINANCIALSYSTEMTRANSACTIONTYPE.FINANCIALSYSTEMID = @FINANCIALSYSTEM
and JOURNALENTRY.GLACCOUNTID is not null

update dbo.FINANCIALTRANSACTIONLINEITEM
set 
POSTSTATUSCODE = 2,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from dbo.JOURNALENTRY 
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 @WorkTable TV1 on JOURNALENTRY.TRANSACTIONTYPECODE = TV1.TRANSACTIONTYPECODE
    and JOURNALENTRY.GLACCOUNTID = TV1.GLACCOUNTID
    and FINANCIALTRANSACTIONLINEITEM.POSTDATE between TV1.DATEFROM and TV1.POSTDATE
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
and FINANCIALSYSTEMTRANSACTIONTYPE.FINANCIALSYSTEMID = @FINANCIALSYSTEM
and JOURNALENTRY.GLACCOUNTID is not null

end try
begin catch
if @@trancount > 0
    rollback
end catch

if @@trancount > 0
    commit