USP_POSTGENERIC

Post procedure for all post processes
Parameters

Parameter	Parameter Type	Mode
@CHANGEAGENTID	uniqueidentifier	IN
@GLPOSTPROCESSID	uniqueidentifier	IN
@SUMMARIZEBYDATECRITERIA	tinyint	IN
@FINANCIALBATCHID	uniqueidentifier	IN
@GLPOSTPROCESSSTATUSID	uniqueidentifier	IN
Definition

Copy


CREATE procedure [dbo].[USP_POSTGENERIC]
( @CHANGEAGENTID as uniqueidentifier
 ,@GLPOSTPROCESSID as uniqueidentifier
 ,@SUMMARIZEBYDATECRITERIA as tinyint = 1    --0 = Day, 1=Fiscal Period

 ,@FINANCIALBATCHID as uniqueidentifier = null
 ,@GLPOSTPROCESSSTATUSID as uniqueidentifier = null
)
as
set nocount on
--declare @WorkTable table (ID uniqueidentifier, CLASSCODE tinyint, JOURNALCODEID uniqueidentifier, AMOUNT money, TRANSACTIONAMOUNT money, ORGANIZATIONAMOUNT money, TRANSACTIONTYPECODE smallint, POSTDATE datetime, GLACCOUNTID uniqueidentifier, DATEFROM datetime, ORIGID uniqueidentifier, PRIMARY KEY clustered (TRANSACTIONTYPECODE,POSTDATE,GLACCOUNTID, ORIGID)  )

declare @CHANGEDATE datetime = getdate()
if @FINANCIALBATCHID is null
    select @FINANCIALBATCHID = newid()

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

create table #WorkTable (ID uniqueidentifier, CLASSCODE tinyint, JOURNALCODEID uniqueidentifier, BASEAMOUNT money, TRANSACTIONAMOUNT money, ORGANIZATIONAMOUNT money, TRANSACTIONTYPECODE smallint, POSTDATE datetime, GLACCOUNTID uniqueidentifier, DATEFROM datetime, ORIGID uniqueidentifier, PRIMARY KEY clustered (TRANSACTIONTYPECODE,POSTDATE,GLACCOUNTID,ORIGID));

insert into dbo.FINANCIALBATCH(ID, COMMITTED, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLPOSTPROCESSSTATUSID)
values 
(@FINANCIALBATCHID, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @GLPOSTPROCESSSTATUSID);

with CTE
as
(select JOURNALENTRY.CLASSCODE, JOURNALENTRY.JOURNALCODEID, JOURNALENTRY.BASEAMOUNT, JOURNALENTRY.TRANSACTIONAMOUNT, JOURNALENTRY.ORGAMOUNT, JOURNALENTRY.TRANSACTIONTYPECODE, 
    case @SUMMARIZEBYDATECRITERIA 
        when 1 then GLFISCALPERIOD.ENDDATE
        when 0 then FINANCIALTRANSACTIONLINEITEM.POSTDATE
        end as DateEnd, 
    JOURNALENTRY.GLACCOUNTID, 
    case @SUMMARIZEBYDATECRITERIA 
        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
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 = @GLPOSTPROCESSID AND JOURNALENTRY.TRANSACTIONTYPECODE = GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE
inner join dbo.GLFISCALPERIOD on FINANCIALTRANSACTIONLINEITEM.POSTDATE between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE and GLFISCALPERIOD.CLOSED = 0
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
and JOURNALENTRY.GLACCOUNTID is not null)
insert into #WorkTable (ID, CLASSCODE, JOURNALCODEID, BASEAMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONTYPECODE, POSTDATE, GLACCOUNTID, DATEFROM, ORIGID)
select newid(), CLASSCODE, JOURNALCODEID, sum(BASEAMOUNT), sum(TRANSACTIONAMOUNT), sum(ORGANIZATIONAMOUNT), TRANSACTIONTYPECODE, 
    DateEnd, GLACCOUNTID, DateStart, SumDetGroupingCol
from CTE
group by TRANSACTIONTYPECODE, CLASSCODE, JOURNALCODEID, DateEnd, GLACCOUNTID, DateStart, SumDetGroupingCol

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 #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 = @GLPOSTPROCESSID AND JOURNALENTRY.TRANSACTIONTYPECODE = GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE
inner join #WorkTable TV1 on JOURNALENTRY.TRANSACTIONTYPECODE = TV1.TRANSACTIONTYPECODE
    and JOURNALENTRY.GLACCOUNTID= TV1.GLACCOUNTID
    and FINANCIALTRANSACTIONLINEITEM.POSTDATE between TV1.DATEFROM and TV1.POSTDATE
    and case GLPOSTPROCESSPOSTMETHOD.POSTMETHODCODE
        when 1 then JOURNALENTRY.ID
        when 2 then FINANCIALTRANSACTION.ID
        when 3 then JOURNALENTRY.ID
        end = case GLPOSTPROCESSPOSTMETHOD.POSTMETHODCODE
            when 1 then TV1.ORIGID
            when 2 then TV1.ORIGID
            when 3 then JOURNALENTRY.ID
            end
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
and JOURNALENTRY.GLACCOUNTID is not null

update dbo.FINANCIALTRANSACTIONLINEITEM
set 
POSTSTATUSCODE = 2,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
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 = @GLPOSTPROCESSID AND JOURNALENTRY.TRANSACTIONTYPECODE = GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE
inner join #WorkTable TV1 on JOURNALENTRY.TRANSACTIONTYPECODE = TV1.TRANSACTIONTYPECODE
    and JOURNALENTRY.GLACCOUNTID = TV1.GLACCOUNTID
    and FINANCIALTRANSACTIONLINEITEM.POSTDATE between TV1.DATEFROM and TV1.POSTDATE
    and case GLPOSTPROCESSPOSTMETHOD.POSTMETHODCODE
        when 1 then JOURNALENTRY.ID
        when 2 then FINANCIALTRANSACTION.ID
        when 3 then JOURNALENTRY.ID
        end = case GLPOSTPROCESSPOSTMETHOD.POSTMETHODCODE
            when 1 then TV1.ORIGID
            when 2 then TV1.ORIGID
            when 3 then JOURNALENTRY.ID
            end
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
and JOURNALENTRY.GLACCOUNTID is not null

update dbo.FINANCIALTRANSACTION
set 
POSTSTATUSCODE = 2,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
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 = @GLPOSTPROCESSID AND JOURNALENTRY.TRANSACTIONTYPECODE = GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE
inner join #WorkTable TV1 on JOURNALENTRY.TRANSACTIONTYPECODE = TV1.TRANSACTIONTYPECODE
    and JOURNALENTRY.GLACCOUNTID = TV1.GLACCOUNTID
    and FINANCIALTRANSACTIONLINEITEM.POSTDATE between TV1.DATEFROM and TV1.POSTDATE
    and case GLPOSTPROCESSPOSTMETHOD.POSTMETHODCODE
        when 1 then JOURNALENTRY.ID
        when 2 then FINANCIALTRANSACTION.ID
        when 3 then JOURNALENTRY.ID
        end = case GLPOSTPROCESSPOSTMETHOD.POSTMETHODCODE
            when 1 then TV1.ORIGID
            when 2 then TV1.ORIGID
            when 3 then JOURNALENTRY.ID
            end
where FINANCIALTRANSACTION.POSTSTATUSCODE = 1
and JOURNALENTRY.GLACCOUNTID is not null

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

if @@trancount > 0
    commit

select count(*) from #JOURNALENTRIESTOPOST