-- =============================================
-- Author: Asamco BV - Alex
-- Create date: 26-05-2023 // update 26-03-25
-- Description: Merge GL account into another GL account.
-- =============================================
CREATE OR ALTER PROCEDURE dbo._as_MergeGLAccount
@OldAccountId bigint
,@NewAccountId bigint
AS
BEGIN
SET NOCOUNT ON;
/* quick creating additional lines for tables:
select CONCAT('update [',object_name(object_id),'] set [',name,'] = @New where [',name,'] = @Old') as Up,
* from sys.columns where object_name(object_id) = '_etblARAPBatchHistoryLines'
*/
/*--REVIEW QUERY TO SEE ANY COLUMNS WHICH MIGHT NEED UPDATING:
with x as (SELECT
s.name AS SchemaName,
t.name AS TableName,
c.name AS ColumnName,
(SELECT SUM(p.row_count)
FROM sys.dm_db_partition_stats AS p
WHERE p.object_id = t.object_id
AND p.index_id IN (0, 1)
) AS [RowCount],
'update [' + s.name + '].[' + t.name + '] set [' + c.name + '] = @New where [' + c.name + '] = @Old' AS UpdateStatement
FROM
sys.tables AS t
INNER JOIN
sys.schemas AS s ON t.schema_id = s.schema_id
INNER JOIN
sys.columns AS c ON t.object_id = c.object_id
WHERE
c.name LIKE '%AccId%'
OR c.name LIKE '%AccountId%'
OR c.name LIKE '%GLLink%'
OR c.name LIKE '%AccountsId%'
OR c.name LIKE '%AccLink%')
select * from x
where x.[RowCount] > 0
ORDER BY
x.SchemaName, x.TableName, x.ColumnName;
*/
DECLARE @AccountTypeId int,@Msg nvarchar(1000)
/*validations:*/
IF COALESCE((SELECT AccountLink from Accounts where AccountLink = @NewAccountId),0) = 0
BEGIN
SET @Msg = CONCAT('New account with id ',@NewAccountId,' doesnt exist')
RAISERROR(@msg,17,1); return -1
END
IF COALESCE((SELECT AccountLink from Accounts where AccountLink = @OldAccountId),0) = 0
BEGIN
SET @Msg = CONCAT('Old account with id ',@OldAccountId,' doesnt exist')
RAISERROR(@msg,17,1); return -1
END
IF COALESCE((SELECT COUNT(*) from _etblAccPrev where iAccPrevAccountID IN(@OldAccountId,@NewAccountId) GROUP BY iAccPrevAccountID),0) = 2
BEGIN
SET @Msg = CONCAT('Values in AccPrev for both old (',@OldAccountId,') and new account. NOT YET SUPPORTED')
RAISERROR(@msg,17,1); return -1
END
/*log table:*/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '_AS_GLRENUM_ConversionLinks')
BEGIN
PRINT 'Creating integration log table (_AS_GLRENUM_ConversionLinks).';
--TODO: fix columns!
CREATE TABLE _AS_GLRENUM_ConversionLinks (
Id bigint IDENTITY(1,1) PRIMARY KEY,
OldId bigint,
[NewId] bigint,
Created_Date datetime NOT NULL DEFAULT GETDATE()
)
END
declare @New int; declare @Old int;
select @New = @NewAccountId
select @Old = @OldAccountId
insert into _AS_GLRENUM_ConversionLinks (OldId, [NewId]) values (@Old, @New);
BEGIN TRANSACTION
BEGIN TRY
update [_etblBudgets] set [iBudgetAccountID] = @New where [iBudgetAccountID] = @Old
update [_etblBudgetsPrev] set [iBudgetPrevAccountID] = @New where [iBudgetPrevAccountID] = @Old
update [dbo].[Accounts] set [iForeignBankLEXAccID] = @New where [iForeignBankLEXAccID] = @Old
update [dbo].[Accounts] set [iForeignBankPEXAccID] = @New where [iForeignBankPEXAccID] = @Old
update [dbo].[BomDef] set [iAccountsIDDefSurplus] = @New where [iAccountsIDDefSurplus] = @Old
--fixed asset:
update [_btblFAAssetType] set [iCreditGLAccountID] = @New where [iCreditGLAccountID] = @Old
update [_btblFAAssetType] set [iAssetGLAccountID] = @New where [iAssetGLAccountID] = @Old
update [_btblFAGLBatchGLEntries] set [iGLAccountID] = @New where [iGLAccountID] = @Old
update [_btblFAGLTotalGLEntries] set [iGLAccountID] = @New where [iGLAccountID] = @Old
update [dbo].[_btblFATxDefaultGLAccounts] set [iCreditGLAccountID] = @New where [iCreditGLAccountID] = @Old
update [dbo].[_btblFATxDefaultGLAccounts] set [iDebitGLAccountID] = @New where [iDebitGLAccountID] = @Old
update [dbo].[_btblFATxDefaultGLAccounts] set [iLossGLAccountID] = @New where [iLossGLAccountID] = @Old
update [dbo].[_btblFATxDefaultGLAccounts] set [iProfitGLAccountID] = @New where [iProfitGLAccountID] = @Old
update [dbo].[_btblFATxDefaultGLAccounts] set [iRevaluationGLAccountID] = @New where [iRevaluationGLAccountID] = @Old
update [_etblARAPBatchHistoryLines] set [iGLContraID] = @New where [iGLContraID] = @Old
update [_etblARAPBatchHistoryLines] set [iTaxAccountID] = @New where [iTaxAccountID] = @Old
update [_etblARAPBatchHistoryLines] set [iAccountID] = @New where [iAccountID] = @Old AND iModule = 2
update [_etblARAPBatchHistoryLines] set [iDiscGLContraID] = @New where [iDiscGLContraID] = @Old
update [dbo].[_etblARAPBatches] set [iBranchLoanAccountID] = @New where [iBranchLoanAccountID] = @Old
update [dbo].[_etblARAPBatches] set [iInputTaxAccID] = @New where [iInputTaxAccID] = @Old
update [dbo].[_etblARAPBatches] set [iOutputTaxAccID] = @New where [iOutputTaxAccID] = @Old
update [dbo].[_etblARAPBatchHistoryLines] set [iTaxAccountID] = @New where [iTaxAccountID] = @Old
update [dbo].[_etblGLLoanAccountLinks] set [iABLoanAccountID] = @New where [iABLoanAccountID] = @Old
update [dbo].[_etblGLLoanAccountLinks] set [iBranchLoanAccountID] = @New where [iBranchLoanAccountID] = @Old
update [dbo].[_etblWhDefaults] set [iBranchLoanAccountID] = @New where [iBranchLoanAccountID] = @Old
update _btblcbBatchDefs set iGLAPAccID = @New where iGLAPAccID = @Old;
update _btblcbBatchDefs set iGLARAccID = @New where iGLARAccID = @Old;
update _btblcbBatchDefs set iGLBankAccID = @New where iGLBankAccID = @Old;
update _btblcbBatchDefs set iInputTaxAccID = @New where iInputTaxAccID = @Old;
update _btblcbBatchDefs set iOutputTaxAccID = @New where iOutputTaxAccID = @Old;
update _btblCbBatches set iGLAPAccID = @New where iGLAPAccID = @Old;
update _btblCbBatches set iGLARAccID = @New where iGLARAccID = @Old;
update _btblCbBatches set iGLBankAccID = @New where iGLBankAccID = @Old;
update _btblCbBatches set iInputTaxAccID = @New where iInputTaxAccID = @Old;
update _btblCbBatches set iOutputTaxAccID = @New where iOutputTaxAccID = @Old;
update [dbo].[_btblCbBatches] set [iBranchLoanAccountID] = @New where [iBranchLoanAccountID] = @Old
update _btblCBBatchLines set iAccountID = @New where iAccountID = @Old;
update _btblCBBatchLines set iDiscTaxAccID = @New where iDiscTaxAccID = @Old;
update _btblCBBatchLines set iTaxAccountID = @New where iTaxAccountID = @Old;
update _btblJCInvoiceLines set iLedgerID = @New where iLedgerID = @Old;
update _btblJCMaster set iAccountsIdCOS = @New where iAccountsIdCOS = @Old;
update _btblJCMaster set iAccountsIDRecovery = @New where iAccountsIDRecovery = @Old;
update _btblJCMaster set iAccountsIdSales = @New where iAccountsIdSales = @Old;
update _btblJCMaster set iAccountsIdWIP = @New where iAccountsIdWIP = @Old;
update _btblJCTxLines set iLedgerID = @New where iLedgerID = @Old;
update _btblJrBatchDefs set iInputTaxAccID = @New where iInputTaxAccID = @Old;
update _btblJrBatchDefs set iOutputTaxAccID = @New where iOutputTaxAccID = @Old;
update _btblJrBatches set iInputTaxAccID = @New where iInputTaxAccID = @Old;
update _btblJrBatches set iOutputTaxAccID = @New where iOutputTaxAccID = @Old;
update [dbo].[_btblJrBatches] set [iBranchLoanAccountID] = @New where [iBranchLoanAccountID] = @Old
update _btblJrBatchLines set iAccountID = @New where iAccountID = @Old;
update _btblJrBatchLines set iTaxAccountID = @New where iTaxAccountID = @Old;
update CLICLASS set iAccountsIDControlAcc = @New where iAccountsIDControlAcc = @Old;
update [CliClass] set [iAccountsIDProfitAcc] = @New where [iAccountsIDProfitAcc] = @Old
update [CliClass] set [iAccountsIDLossAcc] = @New where [iAccountsIDLossAcc] = @Old
update [CliClass] set [iTaxControlAccID] = @New where [iTaxControlAccID] = @Old
update [CliClass] set [iRevProfitAcc] = @New where [iRevProfitAcc] = @Old
update [CliClass] set [iRevLossAcc] = @New where [iRevLossAcc] = @Old
update [CliClass] set [iProvForRevAcc] = @New where [iProvForRevAcc] = @Old
update StDfTbl set iDefStockCostVarianceAccID = @New where iDefStockCostVarianceAccID = @Old;
update COSTCNTR set iGLAccountID = @New where iGLAccountID = @Old;
update JOBDEF set RecoveryM2Link = @New where RecoveryM2Link = @Old;
update JOBDEF set SalesM1Link = @New where SalesM1Link = @Old;
update JOBDEF set SalesM2Link = @New where SalesM2Link = @Old;
update JOBDEF set StockM1Link = @New where StockM1Link = @Old;
update JOBDEF set StockM2Link = @New where StockM2Link = @Old;
update JOBDEF set TaxM1Link = @New where TaxM1Link = @Old;
update JOBDEF set TaxM2Link = @New where TaxM2Link = @Old;
update JOBDEF set WIPM1Link = @New where WIPM1Link = @Old;
update JOBDEF set WIPM2Link = @New where WIPM2Link = @Old;
update JOBTXTP set M1COSLink = @New where M1COSLink = @Old;
update JOBTXTP set M1CreditorLink = @New where M1CreditorLink = @Old;
update JOBTXTP set M1DebtorLink = @New where M1DebtorLink = @Old;
update JOBTXTP set M1RecoveryLink = @New where M1RecoveryLink = @Old;
update JOBTXTP set M1SalesLink = @New where M1SalesLink = @Old;
update JOBTXTP set M1StockLink = @New where M1StockLink = @Old;
update JOBTXTP set M1TaxLink = @New where M1TaxLink = @Old;
update JOBTXTP set M1WIPLink = @New where M1WIPLink = @Old;
update JOBTXTP set M2COSLink = @New where M2COSLink = @Old;
update JOBTXTP set M2CreditorLink = @New where M2CreditorLink = @Old;
update JOBTXTP set M2DebtorLink = @New where M2DebtorLink = @Old;
update JOBTXTP set M2RecoveryLink = @New where M2RecoveryLink = @Old;
update PostGL set AccountLink = @New where AccountLink = @Old;
update PostGL set DrCrAccount = @New where DrCrAccount = @Old AND Id IN ('JL', 'CB', 'Tax', 'Bnk', 'Oth', 'AcPrf');
update [_etblPostGLHist] set [AccountLink] = @New where [AccountLink] = @Old
update [_etblPostGLHist] set [DrCrAccount] = @New where [DrCrAccount] = @Old AND Id IN ('JL', 'CB', 'Tax', 'Bnk', 'Oth', 'AcPrf');
update [dbo].[PostAP] set [iGLTaxAccountID] = @New where [iGLTaxAccountID] = @Old
update [dbo].[PostAR] set [iGLTaxAccountID] = @New where [iGLTaxAccountID] = @Old
update [dbo].[PostGL] set [iGLTaxAccountID] = @New where [iGLTaxAccountID] = @Old
update [dbo].[PostST] set [iGLAccountID] = @New where [iGLAccountID] = @Old
update _btblInvoiceLines set iLedgerAccountID = @New where iLedgerAccountID = @Old;
update TrCodes set TaxAccountLink = @New where TaxAccountLink = @Old;
update TrCodes set Account1Link = @New where Account1Link = @Old;
update TrCodes set Account2Link = @New where Account2Link = @Old;
update VENCLASS set iAccountsIDControlAcc = @New where iAccountsIDControlAcc = @Old;
update [VenClass] set [iAccountsIDProfitAcc] = @New where [iAccountsIDProfitAcc] = @Old
update [VenClass] set [iAccountsIDLossAcc] = @New where [iAccountsIDLossAcc] = @Old
update [VenClass] set [iTaxControlAccID] = @New where [iTaxControlAccID] = @Old
update [VenClass] set [iRevProfitAcc] = @New where [iRevProfitAcc] = @Old
update [VenClass] set [iRevLossAcc] = @New where [iRevLossAcc] = @Old
update [VenClass] set [iProvForRevAcc] = @New where [iProvForRevAcc] = @Old
update BOMDEF set iAccountsIDDefSurplus = @New where iAccountsIDDefSurplus = @Old;
--update BPINTER set iCBBatchesBankID = @New where iCBBatchesBankID = @Old;
--update BPINTER set LoanAccLink = @New where LoanAccLink = @Old;
--update BPINTER set SalariesAccLink = @New where SalariesAccLink = @Old;
--update BPINTER set TaxAccLink = @New where TaxAccLink = @Old;
--update BPINTER set UIFAccLink = @New where UIFAccLink = @Old;
update GRPTBL set COSAccLink = @New where COSAccLink = @Old;
update GRPTBL set PurchasesAccLink = @New where PurchasesAccLink = @Old;
update GRPTBL set SalesAccLink = @New where SalesAccLink = @Old;
update GRPTBL set StockAccLink = @New where StockAccLink = @Old;
update GRPTBL set CostVarianceAccLink = @New where CostVarianceAccLink = @Old;
update GRPTBL set StockAdjustAccLink = @New where StockAdjustAccLink = @Old;
update GRPTBL set iStockCostVarianceAccID = @New where iStockCostVarianceAccID = @Old;
update GRPTBL set iWIPAccID = @New where iWIPAccID = @Old;
update JOBDEF set COSM1Link = @New where COSM1Link = @Old;
update JOBDEF set COSM2Link = @New where COSM2Link = @Old;
update JOBDEF set CreditorM1Link = @New where CreditorM1Link = @Old;
update JOBDEF set CreditorM2Link = @New where CreditorM2Link = @Old;
update JOBDEF set DebtorM1Link = @New where DebtorM1Link = @Old;
update JOBDEF set DebtorM2Link = @New where DebtorM2Link = @Old;
update JOBDEF set iCCCreditAccount = @New where iCCCreditAccount = @Old;
update JOBDEF set iCCDebitAccount = @New where iCCDebitAccount = @Old;
update JOBDEF set RecoveryM1Link = @New where RecoveryM1Link = @Old;
update JOBTXTP set M2SalesLink = @New where M2SalesLink = @Old;
update JOBTXTP set M2StockLink = @New where M2StockLink = @Old;
update JOBTXTP set M2TaxLink = @New where M2TaxLink = @Old;
update JOBTXTP set M2WIPLink = @New where M2WIPLink = @Old;
update ORDERSDF set iSInvGLVarAccID = @New where iSInvGLVarAccID = @Old;
update [dbo].[OrdersDf] set [iSOInvIssueSplitAccrualAccID] = @New where [iSOInvIssueSplitAccrualAccID] = @Old
update STDFTBL set SInvGLVarAccID = @New where SInvGLVarAccID = @Old;
update [dbo].[StDfTbl] set [iDefStockCostVarianceAccID] = @New where [iDefStockCostVarianceAccID] = @Old
update [dbo].[StDfTbl] set [iInvIssueSplitAccrualAccID] = @New where [iInvIssueSplitAccrualAccID] = @Old
update [dbo].[_btblFAAssetType] set [iAssetGLAccountID] = @New where [iAssetGLAccountID] = @Old
update [dbo].[_btblFAAssetType] set [iCreditGLAccountID] = @New where [iCreditGLAccountID] = @Old
update [dbo].[Entities] set [iDCBranchLoanAccountID] = @New where [iDCBranchLoanAccountID] = @Old
/*Evo Online & EPO:*/
update [dbo].[_as_epo_GLAccountOnIncident] set [GLAccountID] = @New where [GLAccountID] = @Old
update [dbo].[_as_EvoOnline_BranchJournalImportDetail] set [ContraAccountId] = @New where [ContraAccountId] = @Old
update [dbo].[_as_EvoOnline_BranchJournalImportDetail] set [TaxAccountId] = @New where [TaxAccountId] = @Old
update [dbo].[_as_Statements] set [BankAccountId] = @New where [BankAccountId] = @Old
update [dbo].[_as_EvoOnline_StockTransaction] set [DefaultToGlAccountId] = @New where [DefaultToGlAccountId] = @Old
update [dbo].[_as_EvoOnline_StockTransactionImportDetail] set [ContraAccountId] = @New where [ContraAccountId] = @Old
update [dbo].[_as_EvoOnline_StockTransactionLine] set [ToGLAccountId] = @New where [ToGLAccountId] = @Old
update [dbo].[_as_evo_ArapBatches] set [InputTaxAccId] = @New where [InputTaxAccId] = @Old
update [dbo].[_as_evo_ArapBatches] set [OutputTaxAccId] = @New where [OutputTaxAccId] = @Old
update [dbo].[_as_evo_ArapBatchLines] set [TaxAccountId] = @New where [TaxAccountId] = @Old
update [dbo].[_as_evo_ArapBatches] set [BranchLoanAccountId] = @New where [BranchLoanAccountId] = @Old
update [dbo].[_as_EPO_VendorDefaultInfo] set [GLAccountID] = @New where [GLAccountID] = @Old
update [dbo].[_as_epo_DebitBatchHeader] set [GlAccountId] = @New where [GlAccountId] = @Old
update [dbo].[_as_epo_PaymentBatchHeader] set [GLAccountID] = @New where [GLAccountID] = @Old
update [dbo].[_as_bi_ConvertedStatementLines] set [BankAccountId] = @New where [BankAccountId] = @Old
update [dbo].[_as_bi_ConvertedStatementLines] set [iAccountId] = @New where [iAccountId] = @Old and iModule = 0
update [dbo].[_as_bi_ConvertedStatementLines] set [iTaxAccountId] = @New where [iTaxAccountId] = @Old
update [dbo].[_as_epo_AccountOnUsergroup] set [AccountID] = @New where [AccountID] = @Old
/*Production module:*/
update [dbo].[_as_PM_BillOfMaterial] set [DefaultWipAccountId] = @New where [DefaultWipAccountId] = @Old
update [dbo].[_as_PM_MFManufacture] set [WiPAccountId] = @New where [WiPAccountId] = @Old
/*Farming module:*/
update [dbo].[_as_FM_ManufactureHeader] set [GLAccountID] = @New where [GLAccountID] = @Old
update [dbo].[_as_FM_ManufactureHeader] set [WiPAccountID] = @New where [WiPAccountID] = @Old
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
Throw;
END CATCH
END --sproc
Overview
Content Tools