USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTDEPOSIT2

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTDEPOSIT2 (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @REFERENCE nvarchar(100),
                        @DEPOSITDATE datetime,
                        @POSTSTATUSCODE tinyint,
                        @POSTDATE datetime,
                        @PROJECTEDNUMBEROFPAYMENTS int,
                        @PROJECTEDAMOUNT numeric(19,4),
                        @STATUSCODE tinyint
                        ,@TRANSACTIONCURRENCYID uniqueidentifier
                        ,@TRANSACTIONEXCHANGERATEID uniqueidentifier
                        ,@TRANSACTIONEXCHANGERATE decimal(20,8)
                        ,@BASEEXCHANGERATEID uniqueidentifier
                        ,@BASEEXCHANGERATE decimal(20,8)
                    )
                    as

                        set nocount on;

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

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        declare @ORIGINALTRANSACTIONCURRENCYID uniqueidentifier
                        declare @ORIGINALTRANSACTIONEXCHANGERATEID uniqueidentifier
                        declare @ORIGINALBASEEXCHANGERATEID uniqueidentifier
                        declare @ORIGINALORGANIZATIONEXCHANGERATEID uniqueidentifier
                        declare @ORIGINALTRANRATE decimal(20,8)
                        declare @ORIGINALBASERATE decimal(20,8) 
                        declare @TOTALPAYMENTAMOUNT money;
                        declare @PDACCOUNTSYSTEMID uniqueidentifier;
                        declare @ORIGINALREFERENCE nvarchar(100);

                        select @ORIGINALTRANSACTIONCURRENCYID = D.TRANSACTIONCURRENCYID
                            ,@ORIGINALTRANSACTIONEXCHANGERATEID = D.TRANSACTIONEXCHANGERATEID
                            ,@ORIGINALBASEEXCHANGERATEID = BAT.BASEEXCHANGERATEID
                            ,@ORIGINALTRANRATE = TRANRATE.RATE
                            ,@ORIGINALBASERATE = BASERATE.RATE
                            ,@TOTALPAYMENTAMOUNT = D.TOTALPAYMENTAMOUNT
                            ,@PDACCOUNTSYSTEMID  = BA.PDACCOUNTSYSTEMID
                            ,@ORIGINALORGANIZATIONEXCHANGERATEID = BAT.ORGANIZATIONEXCHANGERATEID
                            ,@ORIGINALREFERENCE = BAT.REFERENCE
                        from dbo.BANKACCOUNTDEPOSIT D
                        inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.ID
                        inner join dbo.BANKACCOUNT as BA on BAT.BANKACCOUNTID = BA.ID
                        left outer join dbo.CURRENCYEXCHANGERATE TRANRATE on TRANRATE.ID = D.TRANSACTIONEXCHANGERATEID
                        left outer join dbo.CURRENCYEXCHANGERATE BASERATE on BASERATE.ID = BAT.BASEEXCHANGERATEID
                        where D.ID = @ID

                        if (@TRANSACTIONEXCHANGERATEID = '00000000-0000-0000-0000-000000000001' 
                                and ((not @ORIGINALTRANRATE is null) and @ORIGINALTRANRATE = @TRANSACTIONEXCHANGERATE))
                            set @TRANSACTIONEXCHANGERATEID = @ORIGINALTRANSACTIONEXCHANGERATEID;
                        if (@BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001' 
                                and ((not @ORIGINALBASERATE is null) and @ORIGINALBASERATE = @BASEEXCHANGERATE))
                            set @BASEEXCHANGERATEID = @ORIGINALBASEEXCHANGERATEID;

                        begin try

                            if (@ORIGINALTRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID) 
                                or (not (exists(select P.ID from dbo.BANKACCOUNTDEPOSITPAYMENT P where P.DEPOSITID = @ID) 
                                    or exists(select C.ID from dbo.BANKACCOUNTDEPOSITCORRECTION C where C.DEPOSITID = @ID)))
                            begin
                                declare @BANKACCOUNTCURRENCYID uniqueidentifier;
                                declare @BASECURRENCYID uniqueidentifier;

                                select @BANKACCOUNTCURRENCYID = BA.TRANSACTIONCURRENCYID, 
                                             @BASECURRENCYID = BAT.BASECURRENCYID
                                from dbo.BANKACCOUNT as BA
                                inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.BANKACCOUNTID = BA.ID
                                where BAT.ID = @ID

                                --Get Multicurrency values.
                                declare @BANKACCOUNTTRANSACTIONAMOUNT money;
                                declare @BASEAMOUNT money;
                                declare @ORGANIZATIONAMOUNT money;
                                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                                declare @ORGANIZATIONCURRENCYID uniqueidentifier;

                                if @TRANSACTIONCURRENCYID <> @BANKACCOUNTCURRENCYID
                                    --Add a spot exchange rate if an existing rate hasn't
                                    --been selected, the bank and transaction currencies are different, and the rate
                                    --entered isn't zero (which indicates that the user wants to enter the record without a rate).
                                    if @TRANSACTIONEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
                                    begin
                                        set @TRANSACTIONEXCHANGERATEID = newid()

                                        insert into dbo.CURRENCYEXCHANGERATE(
                                            ID, 
                                            FROMCURRENCYID,
                                            TOCURRENCYID,
                                            RATE,
                                            ASOFDATE,
                                            TYPECODE,
                                            SOURCECODEID,
                                            ADDEDBYID, 
                                            CHANGEDBYID, 
                                            DATEADDED, 
                                            DATECHANGED
                                        )
                                        values(
                                            @TRANSACTIONEXCHANGERATEID,
                                            @TRANSACTIONCURRENCYID,
                                            @BANKACCOUNTCURRENCYID,
                                            @TRANSACTIONEXCHANGERATE,
                                            @DEPOSITDATE,
                                            2,
                                            null,
                                            @CHANGEAGENTID,
                                            @CHANGEAGENTID,
                                            @CURRENTDATE,
                                            @CURRENTDATE
                                        );
                                    end

                                    set @BANKACCOUNTTRANSACTIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@TOTALPAYMENTAMOUNT, @TRANSACTIONEXCHANGERATEID);


                                if @BANKACCOUNTCURRENCYID <> @BASECURRENCYID
                                    --Add a spot exchange rate if an existing rate hasn't
                                    --been selected, the base and transaction currencies are different, and the rate
                                    --entered isn't zero (which indicates that the user wants to enter the record without a rate).
                                    if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
                                    begin
                                        set @BASEEXCHANGERATEID = newid()

                                        insert into dbo.CURRENCYEXCHANGERATE(
                                            ID, 
                                            FROMCURRENCYID,
                                            TOCURRENCYID,
                                            RATE,
                                            ASOFDATE,
                                            TYPECODE,
                                            SOURCECODEID,
                                            ADDEDBYID, 
                                            CHANGEDBYID, 
                                            DATEADDED, 
                                            DATECHANGED
                                        )
                                        values(
                                            @BASEEXCHANGERATEID,
                                            @BANKACCOUNTCURRENCYID,
                                            @BASECURRENCYID,
                                            @BASEEXCHANGERATE,
                                            @DEPOSITDATE,
                                            2,
                                            null,
                                            @CHANGEAGENTID,
                                            @CHANGEAGENTID,
                                            @CURRENTDATE,
                                            @CURRENTDATE
                                        );
                                    end

                                declare @ORGAMOUNTORIGINCODE tinyint;
                                declare @ORGCURRENCYID uniqueidentifier;
                                declare @ORGCURRENCYNAME nvarchar(100);
                                select @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
                                select @ORGCURRENCYID = ID, @ORGCURRENCYNAME = NAME from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;

                                exec dbo.USP_CURRENCY_GETCURRENCYVALUES @BANKACCOUNTTRANSACTIONAMOUNT, @DEPOSITDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @BANKACCOUNTCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;
                                if (@ORGAMOUNTORIGINCODE = 1 and @BANKACCOUNTCURRENCYID <> @ORGCURRENCYID) or (@ORGAMOUNTORIGINCODE = 0 and @BASECURRENCYID <> @ORGCURRENCYID)
                                    if @ORGANIZATIONEXCHANGERATEID is null
                                    begin
                                        declare @CURRENCYNAME nvarchar(100);
                                        select @CURRENCYNAME = NAME from dbo.CURRENCY where (ID = @TRANSACTIONCURRENCYID and @ORGAMOUNTORIGINCODE = 1) or (ID = @BASECURRENCYID and @ORGAMOUNTORIGINCODE = 0)
                                        declare @errormessage nvarchar(200);
                                        set @errormessage = 'A corporate exchange rate does not exist for ' + @CURRENCYNAME + ' to ' + @ORGCURRENCYNAME
                                        raiserror (@errormessage, 16, 1);
                                    end
                            end
                            else
                            begin
                                --User cannot change the transaction currency of a deposit after any records have been linked to it.
                                raiserror('ERR_TRANSACTIONCURRENCYID_CANNOT_BE_CHANGED', 16, 1)
                            end

                            declare @ORIGINALPOSTSTATUS tinyint;
                            declare @ORIGINALPOSTDATE date;
                            select @ORIGINALPOSTSTATUS = POSTSTATUSCODE,
                            @ORIGINALPOSTDATE = POSTDATE
                            from dbo.BANKACCOUNTTRANSACTION where ID = @ID;

                            --update the bank account transaction
                            update dbo.BANKACCOUNTTRANSACTION
                                set
                                REFERENCE = @REFERENCE,
                                TRANSACTIONDATE = @DEPOSITDATE,  
                                POSTSTATUSCODE = @POSTSTATUSCODE, 
                                POSTDATE = @POSTDATE, 
                                CHANGEDBYID = @CHANGEAGENTID, 
                                DATECHANGED = @CURRENTDATE
                                ,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
                                ,ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
                            where ID = @ID;

                            -- update the bank account deposit record
                            update dbo.BANKACCOUNTDEPOSIT
                                set
                                PROJECTEDAMOUNT = @PROJECTEDAMOUNT,
                                PROJECTEDNUMBEROFPAYMENTS = coalesce(@PROJECTEDNUMBEROFPAYMENTS, 0), 
                                STATUSCODE = @STATUSCODE,
                                CHANGEDBYID = @CHANGEAGENTID, 
                                DATECHANGED = @CURRENTDATE
                                ,TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
                                ,TRANSACTIONEXCHANGERATEID = @TRANSACTIONEXCHANGERATEID
                            where ID = @ID;

                            if @ORIGINALPOSTSTATUS != @POSTSTATUSCODE
                            begin
                                if @ORIGINALPOSTSTATUS = 2 and exists(select 1
                                    from dbo.BANKACCOUNTDEPOSITCREDITPAYMENT DCP
                                    inner join dbo.CREDITPAYMENT CP on CP.ID = DCP.ID
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = CP.CREDITID
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM SLI on SLI.ID = LI.SOURCELINEITEMID
                                    left join dbo.BANKACCOUNTDEPOSITPAYMENT DP on DP.ID = SLI.FINANCIALTRANSACTIONID
                                    where LI.POSTSTATUSCODE = 3 and SLI.POSTSTATUSCODE = 3 and DCP.DEPOSITID = @ID and (DP.DEPOSITID is null or DP.DEPOSITID != @ID))
                                        raiserror('ERR_CANNOT_CHANGE_POSTSTATUS', 16, 1);

                                if @ORIGINALPOSTSTATUS = 2 and exists(select 1
                                    from dbo.BANKACCOUNT BA
                                    inner join dbo.BANKACCOUNTTRANSACTION_EXT BAT on BAT.BANKACCOUNTID = BA.ID
                                    where BA.GLACCOUNTID is null and BA.PDACCOUNTSEGMENTVALUEID is null and BAT.ID = @ID)
                                        raiserror('ERR_MUST_ASSIGN_CASH_ACCOUNT', 16, 1);

                                exec dbo.USP_BANKACCOUNTDEPOSIT_UPDATEPAYMENTSPOSTSTATUS @ID, @CHANGEAGENTID, @CURRENTDATE
                            end

                            --update related payments and refunds
                            if @ORIGINALPOSTDATE != @POSTDATE
                            begin
                                update dbo.JOURNALENTRY
                                set POSTDATE = DEPOSIT.POSTDATE
                                from dbo.FINANCIALTRANSACTION FT
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
                                inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                                inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
                                left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT CP on CP.ID = JEX.CREDITPAYMENTID
                                left join dbo.BANKACCOUNTDEPOSITPAYMENT P on P.ID = FT.ID
                                left join dbo.BANKACCOUNTDEPOSITCORRECTION DC on DC.ID = FT.ID
                                inner join dbo.FINANCIALTRANSACTION DEPOSIT on DEPOSIT.ID = CP.DEPOSITID or DEPOSIT.ID = P.DEPOSITID or DEPOSIT.ID = DC.DEPOSITID
                                where LI.POSTSTATUSCODE = 1 and  DEPOSIT.ID = @ID

                                update FT
                                set POSTDATE = DEPOSIT.POSTDATE
                                from dbo.FINANCIALTRANSACTION FT
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
                                inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                                inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
                                left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT CP on CP.ID = JEX.CREDITPAYMENTID
                                left join dbo.BANKACCOUNTDEPOSITPAYMENT P on P.ID = FT.ID
                                left join dbo.BANKACCOUNTDEPOSITCORRECTION DC on DC.ID = FT.ID
                                inner join dbo.FINANCIALTRANSACTION DEPOSIT on DEPOSIT.ID = CP.DEPOSITID or DEPOSIT.ID = P.DEPOSITID or DEPOSIT.ID = DC.DEPOSITID
                                where DEPOSIT.ID = @ID
                                and FT.POSTSTATUSCODE = 1

                                update dbo.FINANCIALTRANSACTIONLINEITEM
                                set POSTDATE = DEPOSIT.POSTDATE
                                from dbo.FINANCIALTRANSACTION FT
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
                                inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                                inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
                                left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT CP on CP.ID = JEX.CREDITPAYMENTID
                                left join dbo.BANKACCOUNTDEPOSITPAYMENT P on P.ID = FT.ID
                                left join dbo.BANKACCOUNTDEPOSITCORRECTION DC on DC.ID = FT.ID
                                inner join dbo.FINANCIALTRANSACTION DEPOSIT on DEPOSIT.ID = CP.DEPOSITID or DEPOSIT.ID = P.DEPOSITID or DEPOSIT.ID = DC.DEPOSITID
                                where DEPOSIT.ID = @ID
                                and LI.POSTSTATUSCODE = 1

                    update A
                                set POSTDATE = DEPOSIT.POSTDATE
                                from dbo.FINANCIALTRANSACTION FT
                                inner join dbo.ADJUSTMENT A on A.REVENUEID = FT.ID 
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
                                inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                                inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
                                left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT CP on CP.ID = JEX.CREDITPAYMENTID
                                left join dbo.BANKACCOUNTDEPOSITPAYMENT P on P.ID = FT.ID
                                left join dbo.BANKACCOUNTDEPOSITCORRECTION DC on DC.ID = FT.ID
                                inner join dbo.FINANCIALTRANSACTION DEPOSIT on DEPOSIT.ID = CP.DEPOSITID or DEPOSIT.ID = P.DEPOSITID or DEPOSIT.ID = DC.DEPOSITID
                                where DEPOSIT.ID = @ID
                                and A.POSTSTATUSCODE = 1        




                            end

                            declare @DUMMYID uniqueidentifier = NEWID();
                            if isnull(@ORIGINALTRANSACTIONEXCHANGERATEID, @DUMMYID) != isnull(@TRANSACTIONEXCHANGERATEID, @DUMMYID)
                                or isnull(@ORIGINALBASEEXCHANGERATEID, @DUMMYID) != isnull(@BASEEXCHANGERATEID, @DUMMYID)
                                or isnull(@ORIGINALORGANIZATIONEXCHANGERATEID, @DUMMYID) != isnull(@ORGANIZATIONEXCHANGERATEID, @DUMMYID)
                                or (@ORIGINALPOSTSTATUS != @POSTSTATUSCODE and @POSTSTATUSCODE = 1)
                            begin
                                -- Recalculate all of the payments gains and losses
                                if @ORIGINALPOSTSTATUS = @POSTSTATUSCODE and @POSTSTATUSCODE = 1
                                    exec dbo.USP_DEPOSITEDIT_UPDATEREVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID

                                -- Recreate all of the deposit correction distributions (including gains and losses with intercurrency) if needed.
                                if @POSTSTATUSCODE = 1
                                    exec dbo.USP_EDITDEPOSIT_RECREATEDEPOSITCORRECTIONGLDISTRIBUTIONS @ID, @CHANGEAGENTID

                                if @ORIGINALPOSTSTATUS != @POSTSTATUSCODE and @POSTSTATUSCODE = 1
                                    exec dbo.USP_EDITDEPOSIT_RECREATECREDITPAYMENTGLDISTRIBUTIONS @ID, @CHANGEAGENTID;

                                declare @DEPOSITS UDT_GENERICID
                                insert into @DEPOSITS(ID) values (@ID)

                                merge into dbo.BANKACCOUNTTRANSACTION
                                using dbo.UFN_BANKACCOUNTDEPOSIT_CALCULATEAMOUNTS(@DEPOSITS, @ORGAMOUNTORIGINCODE, @ORGCURRENCYID) as T1
                                on BANKACCOUNTTRANSACTION.ID = T1.ID
                                when matched
                                then update set
                                    TRANSACTIONAMOUNT = T1.TRANSACTIONAMOUNT
                                    ,AMOUNT = T1.BASEAMOUNT
                                    ,ORGANIZATIONAMOUNT = T1.ORGAMOUNT
                                    ,CHANGEDBYID = @CHANGEAGENTID
                                    ,DATECHANGED = @CURRENTDATE 
                                ;                                
                            end
                            else if @ORIGINALREFERENCE <> @REFERENCE
                            begin
                                update dbo.JOURNALENTRY set
                                        COMMENT = case when len(isnull(@REFERENCE, '')) > 0 then @REFERENCE 
                                        else dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE(
                                                        FTLI.ID
                                                        ,REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                                                        ,RSE.APPLICATION) end,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                from dbo.JOURNALENTRY    as JE
                                inner join dbo.JOURNALENTRY_EXT as JEXT on JE.ID = JEXT.ID
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                                inner join dbo.REVENUESPLIT_EXT as RSE on FTLI.ID = RSE.ID
                                inner join dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = FTLI.FINANCIALTRANSACTIONID
                                inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
                                inner join dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION.BANKACCOUNTID
                                inner join dbo.DEPOSITGLDISTRIBUTIONLINK on DEPOSITGLDISTRIBUTIONLINK.ID = JE.ID                                
                                left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FTLI.FINANCIALTRANSACTIONID                                
                                where BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = @ID and JE.TRANSACTIONTYPECODE = 0
                                    and ((JE.TYPECODE= 0 and @TRANSACTIONCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID)
                                        or (JE.TYPECODE = 1 and @TRANSACTIONCURRENCYID <> BANKACCOUNT.TRANSACTIONCURRENCYID 
                                        and JE.TRANSACTIONCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID));
                            end
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                    return 0;