USP_BANKACCOUNTDEPOSIT_UPDATEPAYMENTSPOSTSTATUS

CREATE procedure dbo.USP_BANKACCOUNTDEPOSIT_UPDATEPAYMENTSPOSTSTATUS
(
    @DEPOSITID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
)
as
    set nocount on;

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

    if @CURRENTDATE is null
        set @CURRENTDATE = GetDate();

    create table #DEPOSITS
    (
        DEPOSITID uniqueidentifier,
        POSTSTATUSCODE int,
        POSTDATE datetime
    )
    create clustered index IX_DEPOSITS_DEPOSITID on #DEPOSITS (DEPOSITID);

    insert into #DEPOSITS    
        select 
                DEPOSIT.ID,
                DEPOSIT.POSTSTATUSCODE,
                DEPOSIT.POSTDATE
        from dbo.FINANCIALTRANSACTION FT
        left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = FT.ID
        left join dbo.CREDITPAYMENT CP on CP.CREDITID = FT.ID
        left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT BADCP on BADCP.ID = CP.ID
        left join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT BADCX on BADCX.ID = FT.ID
        inner join dbo.FINANCIALTRANSACTION DEPOSIT  on DEPOSIT.ID = BADP.DEPOSITID 
        where DEPOSIT.ID = @DEPOSITID
        union
        select 
                DEPOSIT.ID,
                DEPOSIT.POSTSTATUSCODE,
                DEPOSIT.POSTDATE
        from dbo.FINANCIALTRANSACTION FT
        left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = FT.ID
        left join dbo.CREDITPAYMENT CP on CP.CREDITID = FT.ID
        left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT BADCP on BADCP.ID = CP.ID
        left join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT BADCX on BADCX.ID = FT.ID
        inner join dbo.FINANCIALTRANSACTION DEPOSIT on DEPOSIT.ID = BADCP.DEPOSITID
        where DEPOSIT.ID = @DEPOSITID
        union
        select
                DEPOSIT.ID, 
                DEPOSIT.POSTSTATUSCODE,
                DEPOSIT.POSTDATE
        from dbo.FINANCIALTRANSACTION FT
        left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = FT.ID
        left join dbo.CREDITPAYMENT CP on CP.CREDITID = FT.ID
        left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT BADCP on BADCP.ID = CP.ID
        left join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT BADCX on BADCX.ID = FT.ID
        inner join dbo.FINANCIALTRANSACTION DEPOSIT on (DEPOSIT.ID = FT.PARENTID and BADCX.ID is not null)
        where DEPOSIT.ID = @DEPOSITID

    update FINANCIALTRANSACTION set
        POSTSTATUSCODE = DT.POSTSTATUSCODE,    
        POSTDATE = case DT.POSTSTATUSCODE when 3 then NULL else DT.POSTDATE end,
        CHANGEDBYID = @CHANGEAGENTID, 
        DATECHANGED = @CURRENTDATE
    from dbo.FINANCIALTRANSACTION FT
    inner join #DEPOSITS DT on DT.DEPOSITID = FT.ID

    update FINANCIALTRANSACTIONLINEITEM set
        POSTSTATUSCODE = DT.POSTSTATUSCODE,    
        POSTDATE = case DT.POSTSTATUSCODE when 3 then NULL else DT.POSTDATE end,
        CHANGEDBYID = @CHANGEAGENTID, 
        DATECHANGED = @CURRENTDATE
    from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
    inner join #DEPOSITS DT on DT.DEPOSITID = FTLI.FINANCIALTRANSACTIONID

    update REVENUE_EXT set
        CHANGEDBYID = @CHANGEAGENTID, 
        DATECHANGED = @CURRENTDATE
    from dbo.REVENUE_EXT REX
    inner join #DEPOSITS DT on DT.DEPOSITID = REX.ID

    if (select DEPOSIT.POSTSTATUSCODE from dbo.BANKACCOUNTTRANSACTION as DEPOSIT where DEPOSIT.ID = @DEPOSITID) = 1
        exec dbo.USP_DEPOSITEDIT_UPDATEREVENUEGLDISTRIBUTION @DEPOSITID, @CHANGEAGENTID
    else
    begin
        declare @CONTEXTCACHE varbinary(128); 
        set @CONTEXTCACHE = CONTEXT_INFO();

        if @CHANGEAGENTID is not null 
            set CONTEXT_INFO @CHANGEAGENTID;

    create table #DEPOSITIDS
        (
            DEPOSITID uniqueidentifier
        )
        create clustered index IX_DEPOSITIDS_DEPOSITID on #DEPOSITIDS (DEPOSITID);

        insert into #DEPOSITIDS
            select ID from FINANCIALTRANSACTION FT 
            where FT.TYPECODE in (24,25) -- Deposit correction

            and PARENTID = @DEPOSITID
            union
            -- Will not re-wtire this because CREDITGL view has complicated logic to determine CREDITPAYMENTID

            select DIST.GLTRANSACTIONID
            from dbo.BANKACCOUNTDEPOSITCREDITPAYMENT as C --view

            inner join dbo.CREDITGLDISTRIBUTION as DIST on C.ID = DIST.CREDITPAYMENTID --view

            where C.DEPOSITID = @DEPOSITID
            union
            select JE.ID
            from JOURNALENTRY JE
            inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
            inner join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID 
            inner join dbo.BANKACCOUNTDEPOSITPAYMENT DP on DP.ID = FT.ID            
            inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID and JEX.TABLENAMECODE = 1
            where FTLI.DELETEDON is null
            and JEX.OUTDATED = 0
            and DP.DEPOSITID = @DEPOSITID
            union
            select JE.ID
            from JOURNALENTRY JE
            inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID and JEX.TABLENAMECODE = 2
            inner join dbo.BANKACCOUNTDEPOSITPAYMENT DP on JEX.REVENUEPURCHASEID = DP.ID
            where JEX.OUTDATED = 0
            and DP.DEPOSITID = @DEPOSITID
            union
            select JE.ID
            from JOURNALENTRY JE
            inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID and JEX.TABLENAMECODE = 8
            inner join dbo.BANKACCOUNTDEPOSITPAYMENT DP on JEX.LOGICALREVENUEID = DP.ID
            where JEX.OUTDATED = 0
            and DP.DEPOSITID = @DEPOSITID;

            delete dbo.JOURNALENTRY 
            from dbo.JOURNALENTRY
            inner join  #DEPOSITIDS on #DEPOSITIDS.DEPOSITID = JOURNALENTRY.ID;

            delete from dbo.DEPOSITGLDISTRIBUTIONLINK
            where DEPOSITID = @DEPOSITID;

      drop table #DEPOSITS;
        drop table    #DEPOSITIDS;

        if not @CONTEXTCACHE is null 
            set CONTEXT_INFO @CONTEXTCACHE;
    end

    return 0;