USP_BANKACCOUNTDEPOSIT_OVERWRITECREDITPAYMENTCREDITACCOUNTS

            CREATE procedure dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITECREDITPAYMENTCREDITACCOUNTS
            (
              @CREDITPAYMENTID uniqueidentifier,
              @DEPOSITID uniqueidentifier,
              @CHANGEAGENTID uniqueidentifier = null,
              @CHANGEDATE datetime = null
            )
            as
            begin
                set nocount on;

                if @CHANGEAGENTID is null
                    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

                declare @GLACCOUNTID uniqueidentifier = null;
                declare @PDACCOUNTSEGMENTVALUEID uniqueidentifier = null;
                declare @ACCOUNT nvarchar(100) = '';
                declare @ACCOUNTCODE nvarchar(30) = '';
                declare @PDACCOUNTSYSTEMID uniqueidentifier = null;
                declare @DEFAULTGLACCOUNTID uniqueidentifier = null;
                declare @DEFAULTGLACCOUNT nvarchar(100);
                declare @DEPOSITPOSTDATE datetime;

                select 
                    @GLACCOUNTID = [BANKACCOUNT].[GLACCOUNTID],
                    @PDACCOUNTSEGMENTVALUEID = [BANKACCOUNT].[PDACCOUNTSEGMENTVALUEID], 
                    @ACCOUNT = [GLACCOUNT].[ACCOUNTNUMBER],
                    @ACCOUNTCODE = [PDACCOUNTSEGMENTVALUE].[SHORTDESCRIPTION],
                    @PDACCOUNTSYSTEMID = BANKACCOUNT.PDACCOUNTSYSTEMID,
                    @DEPOSITPOSTDATE = BANKACCOUNTTRANSACTION.POSTDATE
                from dbo.[BANKACCOUNTTRANSACTION]
                inner join dbo.[BANKACCOUNT] 
                    on [BANKACCOUNTTRANSACTION].[BANKACCOUNTID] = [BANKACCOUNT].[ID]
                left outer join dbo.[GLACCOUNT] 
                    on [GLACCOUNT].[ID] = [BANKACCOUNT].[GLACCOUNTID]
                left outer join dbo.[PDACCOUNTSEGMENTVALUE] 
                    on [PDACCOUNTSEGMENTVALUE].[ID] = [BANKACCOUNT].[PDACCOUNTSEGMENTVALUEID]
                where [BANKACCOUNTTRANSACTION].[ID] = @DEPOSITID;

                update dbo.JOURNALENTRY
                    set POSTDATE = @DEPOSITPOSTDATE
                where JOURNALENTRY.ID in (
                    select JE.ID
                        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
                        inner join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT CP on CP.ID = JEX.CREDITPAYMENTID
                        inner join dbo.FINANCIALTRANSACTION DEPOSIT on DEPOSIT.ID = CP.DEPOSITID
                        where DEPOSIT.ID = @DEPOSITID
                            and CP.ID = @CREDITPAYMENTID)


                select @DEFAULTGLACCOUNTID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID, @DEFAULTGLACCOUNT = GLACCOUNT.ACCOUNTNUMBER 
                from dbo.PDACCOUNTSYSTEM left join dbo.GLACCOUNT on PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID = GLACCOUNT.ID
                where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID

                declare @NEWACCOUNTS table (
                    [ID] uniqueidentifier,
                    [CREDITPAYMENTID] uniqueidentifier, 
                    [GLPAYMENTMETHODREVENUETYPEMAPPINGID] uniqueidentifier, 
                    [PROJECT] nvarchar(100),
                    [REFERENCE] nvarchar(255),
                    [GLACCOUNTID] uniqueidentifier, 
                    [ACCOUNTNUMBERS] nvarchar(100), 
                    [AMOUNT] money,
                    [JOURNAL] nvarchar(255),
                    [POSTDATE] datetime,
                    [CREDITITEMID] uniqueidentifier,
                    [TRANSACTIONCURRENCYID] uniqueidentifier
                );

                update dbo.GLTRANSACTION set
                    POSTDATE = @DEPOSITPOSTDATE,
                    CHANGEDBYID = @CHANGEAGENTID, 
                    DATECHANGED = @CHANGEDATE
                where [GLTRANSACTION].[POSTSTATUSCODE] <> 0
                    and [GLTRANSACTION].[ID] in (select [CREDITGLDISTRIBUTION].[GLTRANSACTIONID] from dbo.[CREDITGLDISTRIBUTION] where [CREDITGLDISTRIBUTION].[CREDITPAYMENTID] = @CREDITPAYMENTID);

                insert into @NewAccounts(
                    [ID], 
                    [CREDITPAYMENTID], 
                    [REFERENCE], 
                    [AMOUNT], 
                    [JOURNAL], 
                    [POSTDATE], 
                    [ACCOUNTNUMBERS], 
                    [GLACCOUNTID],
                    [CREDITITEMID],
                    [TRANSACTIONCURRENCYID],
                    [PROJECT]
                )
                select 
                    newid(), 
                    @CREDITPAYMENTID, 
                    JE.COMMENT, 
                    JE.TRANSACTIONAMOUNT, 
                    EXT.JOURNAL, 
                    @DEPOSITPOSTDATE,
                    isnull(@ACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(JE.[GLACCOUNTID], @ACCOUNTCODE,@PDACCOUNTSYSTEMID)), 
                    @GLACCOUNTID,
                    EXT.CREDITITEMID,
                    JE.TRANSACTIONCURRENCYID,
                    EXT.PROJECT
                from dbo.JOURNALENTRY JE
                inner join dbo.JOURNALENTRY_EXT EXT on JE.ID = EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                where 
                    [EXT].[CREDITPAYMENTID] = @CREDITPAYMENTID and 
                    [JE].[TRANSACTIONTYPECODE] = 0 and 
                    [LI].[POSTSTATUSCODE] <> 2;

                if @GLACCOUNTID is null
                begin
                    update @NEWACCOUNTS
                    set [GLACCOUNTID] = (select [GLACCOUNT].[ID] from dbo.[GLACCOUNT] where [GLACCOUNT].[ACCOUNTNUMBER] = [ACCOUNTNUMBERS]);

                    if exists(select [ID] from @NewAccounts where [GLACCOUNTID] is null) and @DEFAULTGLACCOUNTID is null
                    begin
                        select top 1 @ACCOUNT = [ACCOUNTNUMBERS]
                        from @NewAccounts 
                        where [GLACCOUNTID] is null;
                        raiserror('The account %s does not exist. The action could not be completed.', 13, 1, @ACCOUNT);
                    end
                    else if @DEFAULTGLACCOUNTID is not null
                        update @NewAccounts set
                            GLACCOUNTID = @DEFAULTGLACCOUNTID
                            ,ACCOUNTNUMBERS = @DEFAULTGLACCOUNT
                        where GLACCOUNTID is null;
                end

                --remove gltransactions for this creditpayment

                declare gltransactiondelete_cursor cursor local fast_forward for 
                    select [GLTRANSACTION].[ID]
                    from dbo.[GLTRANSACTION]
                    inner join dbo.[CREDITGLDISTRIBUTION]
                        on [GLTRANSACTION].[ID] = [CREDITGLDISTRIBUTION].[GLTRANSACTIONID]
                    where 
                        [CREDITGLDISTRIBUTION].[CREDITPAYMENTID] = @CREDITPAYMENTID and
                        [CREDITGLDISTRIBUTION].[TRANSACTIONTYPECODE] = 1 and
                        [GLTRANSACTION].[TRANSACTIONTYPECODE] = 1 and 
                        [GLTRANSACTION].[POSTSTATUSCODE] <> 0;

                open gltransactiondelete_cursor

                declare @GLTRANSACTIONID uniqueidentifier = null
                fetch next from gltransactiondelete_cursor
                into @GLTRANSACTIONID

                while @@FETCH_STATUS = 0
                begin
                    exec dbo.USP_GLTRANSACTION_DELETEBYID_WITHCHANGEAGENTID @GLTRANSACTIONID, @CHANGEAGENTID

                    fetch next from gltransactiondelete_cursor
                    into @GLTRANSACTIONID
                end

                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                close gltransactiondelete_cursor;
                deallocate gltransactiondelete_cursor;

                insert into dbo.JOURNALENTRY (
                    ID
                    ,FINANCIALTRANSACTIONLINEITEMID
                    ,GLACCOUNTID
                    ,TRANSACTIONAMOUNT
                    ,BASEAMOUNT
                    ,ORGAMOUNT
                    ,COMMENT
                    ,POSTDATE
                    ,TRANSACTIONTYPECODE
                    ,TRANSACTIONCURRENCYID
                    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select 
                    [NEW].ID
                    ,[NEW].CREDITITEMID
                    ,[NEW].GLACCOUNTID
                    ,[NEW].AMOUNT
                    ,[NEW].AMOUNT
                    ,[NEW].AMOUNT
                    ,[NEW].REFERENCE
                    ,[NEW].POSTDATE
                    ,1
                    ,[NEW].TRANSACTIONCURRENCYID
                    ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                from @NewAccounts as [NEW];

                insert into dbo.JOURNALENTRY_EXT (
                    ID
                    ,PROJECT
                    ,ACCOUNT
                    ,JOURNAL
                    ,CREDITITEMID
                    ,CREDITPAYMENTID
                    ,TABLENAMECODE
                    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    [NEW].ID
                    ,[NEW].PROJECT
                    ,[NEW].ACCOUNTNUMBERS
                    ,[NEW].JOURNAL
                    ,[NEW].CREDITITEMID
                    ,[NEW].CREDITPAYMENTID
                    ,6
                    ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                from @NewAccounts as [NEW];
            end