-- =============================================
-- Author: Asamco BV - Alex
-- Create date: 26-05-2023
-- Description: Create Segmented GL Account
-- =============================================
CREATE OR ALTER PROCEDURE dbo._as_MergeGLAccount
@OldAccountId bigint
,@NewAccountId bigint
AS
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'
*/
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
update [_etblBudgets] set [iBudgetAccountID] = @New where [iBudgetAccountID] = @Old
update [_etblBudgetsPrev] set [iBudgetPrevAccountID] = @New where [iBudgetPrevAccountID] = @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 [_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 _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 _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 _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 _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 STDFTBL set SInvGLVarAccID = @New where SInvGLVarAccID = @Old;
insert into _AS_GLRENUM_ConversionLinks (OldId, [NewId]) values (@Old, @New);
END --sproc
Overview
Content Tools