USP_BANKACCOUNTDEPOSIT_UNLINKEDPAYMENTS_UPDATEFROMXML

CREATE procedure [dbo].[USP_BANKACCOUNTDEPOSIT_UNLINKEDPAYMENTS_UPDATEFROMXML] 
(
    @DEPOSITID uniqueidentifier,
    @XML xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
set nocount on;

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate()

    declare @POSTDATE datetime;
    declare @POSTSTATUSCODE tinyint;
    declare @REFERENCE nvarchar(100);
    declare @TRANSACTIONCURRENCYID uniqueidentifier;

    declare @UnlinkedPayments table (AMOUNT money, ID uniqueidentifier primary key, PAYMENTMETHOD nvarchar(14), TRANSACTIONDATE datetime, TRANSACTIONTYPE nvarchar(19))
    insert into @UnlinkedPayments (AMOUNT, ID, PAYMENTMETHOD, TRANSACTIONDATE, TRANSACTIONTYPE)
    select AMOUNT, ID, PAYMENTMETHOD, TRANSACTIONDATE, TRANSACTIONTYPE 
    from dbo.UFN_BANKACCOUNTDEPOSIT_UNLINKEDPAYMENTS_FROMITEMLISTXML(@XML)

    select @POSTDATE = POSTDATE, @POSTSTATUSCODE = POSTSTATUSCODE, @REFERENCE = REFERENCE, @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
    from dbo.BANKACCOUNTTRANSACTION with (nolock)
    inner join dbo.BANKACCOUNT with (nolock) on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION.BANKACCOUNTID
    where BANKACCOUNTTRANSACTION.ID = @DEPOSITID

    update dbo.ADJUSTMENT
    set POSTDATE = @POSTDATE, DATECHANGED = @CHANGEDATE, CHANGEDBYID = @CHANGEAGENTID
    from dbo.ADJUSTMENT
    inner join @UnlinkedPayments as temp on ADJUSTMENT.REVENUEID = temp.ID
    where POSTSTATUSCODE <> 0;

    if len(@REFERENCE) = 0
    begin
        declare @AffectedTransactions table (ID uniqueidentifier primary key)

        insert into @AffectedTransactions (ID)
        select JOURNALENTRY.ID                    
        from @UnLinkedPayments as temp inner join dbo.FINANCIALTRANSACTION on temp.ID = FINANCIALTRANSACTION.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.JOURNALENTRY on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
        where FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
        and ((JOURNALENTRY_EXT.TABLENAMECODE in (1,8) and JOURNALENTRY_EXT.OUTDATED = 0)
            or (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 1 and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2))

        update dbo.JOURNALENTRY
            set POSTDATE = @POSTDATE,
        DATECHANGED = @POSTDATE,
        CHANGEDBYID = @CHANGEAGENTID
        where JOURNALENTRY.ID in (select t2.ID from @AffectedTransactions t2)

    end

    else
    begin
        declare @AffectedTransactions2 table (ID uniqueidentifier primary key, PostDate datetime, Reference nvarchar(255))

        insert into @AffectedTransactions2 (ID, PostDate, Reference)
        select JOURNALENTRY.ID,  @POSTDATE,
        case when JOURNALENTRY_EXT.TABLENAMECODE = 1 and JOURNALENTRY.TRANSACTIONTYPECODE = 0 and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1 then @REFERENCE
            else JOURNALENTRY.COMMENT
            end
        from @UnlinkedPayments as temp inner join dbo.FINANCIALTRANSACTION on temp.ID = FINANCIALTRANSACTION.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.JOURNALENTRY on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
        left join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
        where FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
        and ((JOURNALENTRY_EXT.TABLENAMECODE in (1,8) and JOURNALENTRY_EXT.OUTDATED = 0)
            or (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 1 and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2)
            or (JOURNALENTRY_EXT.TABLENAMECODE = 1 and JOURNALENTRY.TRANSACTIONTYPECODE = 0 and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1))

        update dbo.JOURNALENTRY
            set POSTDATE = temp.PostDate,
            COMMENT = case when DEPOSITGLDISTRIBUTIONLINK.ID is null then JOURNALENTRY.COMMENT else temp.Reference end,
            DATECHANGED = @CHANGEDATE,
            CHANGEDBYID = @CHANGEAGENTID
        from dbo.JOURNALENTRY inner join @AffectedTransactions2 as temp on JOURNALENTRY.ID = temp.ID
        left join dbo.DEPOSITGLDISTRIBUTIONLINK on JOURNALENTRY.ID = DEPOSITGLDISTRIBUTIONLINK.ID

    end


    -- insert new items

    merge dbo.BANKACCOUNTDEPOSITPAYMENT as Target
          using (select ID, @DEPOSITID, @CHANGEAGENTID, @CHANGEDATE from @UnlinkedPayments) as Source (ID, DEPOSITID, CHANGEAGENTID, CHANGEDATE)
          on (Target.ID = Source.ID)
          when matched 
          then update set DEPOSITID = Source.DEPOSITID,
                CHANGEDBYID = Source.CHANGEAGENTID,
                DATECHANGED = Source.CHANGEDATE
          when not matched by Target
          then insert (ID, DEPOSITID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values (Source.ID, Source.DEPOSITID, Source.CHANGEAGENTID, Source.CHANGEAGENTID, Source.CHANGEDATE, Source.ChangeDate);
      ;
    --This thing can still cause deadlocks  

    exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEMULTIPLEPAYMENTSDEBITACCOUNTS @DEPOSITID, @XML, @CHANGEAGENTID, @CHANGEDATE;

    if len(@REFERENCE) > 0
    begin
        update dbo.JOURNALENTRY
            set COMMENT = @REFERENCE,
            DATECHANGED = @CHANGEDATE,
            CHANGEDBYID = @CHANGEAGENTID
        from dbo.JOURNALENTRY inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join @UnlinkedPayments as temp on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = temp.ID
        inner join dbo.DEPOSITGLDISTRIBUTIONLINK on temp.ID = DEPOSITGLDISTRIBUTIONLINK.ID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
        where JOURNALENTRY.TRANSACTIONTYPECODE = 0
            and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
            and FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
            and (JOURNALENTRY_EXT.TABLENAMECODE = 1 or JOURNALENTRY_EXT.TABLENAMECODE = 2)

        if @@Error <> 0
            return 2;

        return 0;

    end