--CreateProjectDB.sql-------------------------------------------------------------------------- use master create database [DBKeyword_ProjectDbName] collate Chinese_PRC_CI_AS; go USE [DBKeyword_ProjectDbName] GO --****************************start repalce holder part************************* --start v1.0.0.0-------------------------------------------------------------------------- IF NOT EXISTS (select 1 from sys.tables where name = 'VoucherMapping' and type = 'U') BEGIN create table dbo.VoucherMapping ( ID VARCHAR(128) NOT NULL, [TranCode] [nvarchar](50) NOT NULL, [VID] [nvarchar](50) NOT NULL, [Period] [int] NOT NULL, [Group] [nvarchar](50) NULL, [ItemID] [nvarchar](50) NULL, [CreatorID] VARCHAR(128) NULL, [CreateTime] [datetime] NOT NULL PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'InvoiceMapping' and type = 'U') BEGIN create table dbo.InvoiceMapping ( ID VARCHAR(128) NOT NULL, [TranCode] [nvarchar](50) NOT NULL, [InvoiceCode] [nvarchar](50) NOT NULL, [InvoiceNumber] [nvarchar](50) NOT NULL, [Period] [int] NOT NULL, [CreatorID] VARCHAR(128) NULL, [CreateTime] [datetime] NOT NULL PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'EnterpriseAccount' and type = 'U') BEGIN CREATE TABLE dbo.EnterpriseAccount( ID varchar(128) NOT NULL DEFAULT (CONVERT([varchar](128),newid())), [AcctCode] [nvarchar](50) NOT NULL, [Name] [nvarchar](200) NOT NULL, [ParentCode] [nvarchar](50) NULL, [FullName] [nvarchar](200) NULL, [AcctProp] [int] NULL, [SubProp] [int] NULL, [AcctLevel] [int] NULL, [Direction] [int] NOT NULL, [IsLeaf] [smallint] NULL, [IsActive] [smallint] NOT NULL DEFAULT 1, [EnglishName] [nvarchar](200) NULL, [StdCode] [nvarchar](50) NULL, [EnterpriseAccountSetID] [varchar](128) NOT NULL, [IsDummy] [int] NULL, [IsInVoucher] [int] NULL, [CreatorID] [VARCHAR](128) NOT NULL, [UpdatorID] [VARCHAR](128) NOT NULL, [CreateTime] [DATETIME] NOT NULL, [UpdateTime] [DATETIME] NOT NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'ImportFile' and type = 'U') BEGIN CREATE TABLE dbo.ImportFile( FileID varchar(128) NOT NULL, [FilePath] [nvarchar](500) NOT NULL, [FileName] [nvarchar](500) NULL, [PeriodID] [int] NULL, [FileType] [varchar](50) NULL, [CreatorID] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [FileImportType] [int] NULL, [Comments] [nvarchar](max) NULL, PRIMARY KEY(FileID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'OutputVATInvoice' and type = 'U') BEGIN CREATE TABLE dbo.OutputVATInvoice( InvoiceID varchar(128) NOT NULL, [InvoiceType] [int] NOT NULL, [ClassCode] [nvarchar](50) NOT NULL, [InvoiceNumber] [nvarchar](50) NOT NULL, [BuyerName] [nvarchar](200) NULL, [BuyerTaxNumber] [nvarchar](200) NULL, [BankAccount] [nvarchar](200) NULL, [PhoneNum] [nvarchar](200) NULL, [InvoiceDate] [datetime] NOT NULL, [SeqNo] [int] NOT NULL, [PeriodID] [int] NOT NULL, [Status] [int] NULL, [CreatorID] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [PartAccntedPeriod] [int] NULL, [AccntedPeriod] [int] NULL, PRIMARY KEY(InvoiceID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'OutputVATInvoiceItem' and type = 'U') BEGIN CREATE TABLE dbo.OutputVATInvoiceItem( ItemID varchar(128) NOT NULL, [InvoiceID] [varchar](128) NOT NULL, [CodeVersion] [nvarchar](50) NULL, [ProductName] [nvarchar](200) NULL, [DocumentNum] [nvarchar](100) NULL, [ProductStandard] [nvarchar](100) NULL, [Unit] [nvarchar](100) NULL, [Quantity] [int] NULL, [UnitPrice] [float] NULL, [Amount] [decimal](18, 4) NOT NULL, [TaxRate] [decimal](18, 4) NOT NULL, [TaxAmount] [decimal](18, 4) NOT NULL, [SeqNo] [int] NOT NULL, [TaxClassCode] [nvarchar](100) NULL, [CreatorID] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [PeriodID] [int] NOT NULL, PRIMARY KEY(ItemID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'InputVATInvoice' and type = 'U') BEGIN CREATE TABLE dbo.InputVATInvoice( [ID] [varchar](128) NOT NULL, [PeriodID] [int] NOT NULL, [InvoiceCode] [nvarchar](50) NOT NULL, [InvoiceNumber] [nvarchar](50) NOT NULL, [InvoiceDate] [datetime] NOT NULL, [SellerTaxNumber] [nvarchar](50) NOT NULL, [Amount] [decimal](18, 4) NULL, [TaxAmount] [decimal](18, 4) NULL, [InvoiceType] [int] NOT NULL, [CertificationResult] [nvarchar](50) NULL, [CertificationDate] [datetime] NULL, [CreatorID] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [Status] [int] NULL, [PartAccntedPeriod] [int] NULL, [AccntedPeriod] [int] NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'InputVATInvoiceItem' and type = 'U') BEGIN CREATE TABLE dbo.InputVATInvoiceItem( [ID] [varchar](128) NOT NULL, [InputVATInvoiceID] [varchar](128) NOT NULL, [PeriodID] [int] NOT NULL, [InvoiceCode] [nvarchar](50) NOT NULL, [InvoiceNumber] [nvarchar](50) NOT NULL, [Amount] [decimal](18, 4) NULL, [TaxAmount] [decimal](18, 4) NULL, [TaxRate] [decimal](18, 4) NULL, [ProductionName] [nvarchar](100) NOT NULL, [CreatorID] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [ProductionCode] [nvarchar](50) NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'GoodsMapping' and type = 'U') BEGIN CREATE TABLE dbo.GoodsMapping( ID [varchar](128) NOT NULL, [CustGoodsName] [nvarchar](50) NULL, [StdGoodsName] [nvarchar](50) NULL, [CreatorID] [varchar](128) NULL, [CreateTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'OperationLog' and type = 'U') BEGIN CREATE TABLE dbo.OperationLog( [ID] [varchar](128) NOT NULL, [OperationName] [nvarchar](200) NOT NULL, [ModuleID] [int] NOT NULL, [OperationObject] [nvarchar](max) NULL, [OperationType] [int] NOT NULL, [OperationContent] [nvarchar](max) NULL, [OriginalState] [nvarchar](max) NULL, [UpdateState] [nvarchar](max) NULL, [CreatorID] [varchar](128) NOT NULL, [Comment] [nvarchar](max) NULL, [CreateTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, [IP] [varchar](100) NULL, [Period] [int] NULL, [ServiceType] [int] NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'ValidationInfo' and type = 'U') BEGIN CREATE TABLE dbo.ValidationInfo( ID varchar(128) NOT NULL , [ImportTypeId] [int] NOT NULL, [ValidationResult] [nvarchar](max) NULL, [ValidationDetails] [nvarchar](max) NULL, [ValidationTips] [nvarchar](max) NULL, [Number] [int] NULL, [CreatorId] [nvarchar](50) NULL, [CreateTime] [datetime] NULL, [PeriodId] [int] NULL, [ErpCheckTypeId] [int] NULL, [ErrorLevel] [int] NULL, [IsShowDetail] [smallint] NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'StandardAccount' and type = 'U') BEGIN CREATE TABLE [dbo].[StandardAccount]( [ID] [varchar](128) NOT NULL, [Code] [nvarchar](50) NOT NULL, [Name] [nvarchar](50) NOT NULL, [ParentCode] [nvarchar](50) NULL, [FullName] [nvarchar](200) NULL, [AcctProp] [int] NULL, [SubProp] [int] NULL, [AcctLevel] [int] NULL, [Direction] [int] NOT NULL, [IsLeaf] [smallint] NOT NULL, [RuleType] [int] NOT NULL, [IsActive] [smallint] NOT NULL, [EnglishName] [nvarchar](200) NULL, [IndustryID] [varchar](128) NOT NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'BalanceStd' and type = 'U') BEGIN CREATE TABLE [dbo].[BalanceStd]( [BalanceId] [varchar](128) NOT NULL DEFAULT (CONVERT([varchar](128),newid())), [PeriodId] [int] NULL, [AcctCode] [nvarchar](50) NULL, [BegDebitBal] [numeric](18, 3) NULL DEFAULT ((0)), [BegCreditBal] [numeric](18, 3) NULL DEFAULT ((0)), [BegBal] [numeric](18, 3) NULL DEFAULT ((0)), [EndBal] [numeric](18, 3) NULL DEFAULT ((0)), [EndDebitBal] [numeric](18, 3) NULL DEFAULT ((0)), [EndCreditBal] [numeric](18, 3) NULL DEFAULT ((0)), [DebitBal] [numeric](18, 3) NULL DEFAULT ((0)), [CreditBal] [numeric](18, 3) NULL DEFAULT ((0)), [YearDebitBal] [numeric](18, 3) NULL, [YearCreditBal] [numeric](18, 3) NULL, [CarryoverDebit] [numeric](18, 3) NULL, [CarryoverCredit] [numeric](18, 3) NULL, [YearCarryoverDebit] [numeric](18, 3) NULL, [YearCarryoverCredit] [numeric](18, 3) NULL, [DebitNet] [numeric](18, 3) NULL, [CreditNet] [numeric](18, 3) NULL, [YearDebitNet] [numeric](18, 3) NULL, [YearCreditNet] [numeric](18, 3) NULL, [CreateTime] [datetime] NULL DEFAULT (getdate()), [UpdateTime] [datetime] NULL DEFAULT (getdate()), [SubProp] INT NULL, PRIMARY KEY(BalanceId) ); END -- report begin ---------- IF NOT EXISTS (select 1 from sys.tables where name = 'CellData' and type = 'U') BEGIN CREATE TABLE [dbo].[CellData]( [ID] [varchar](128) NOT NULL, [ReportID] [varchar](128) NOT NULL, [CellTemplateID] [varchar](128) NOT NULL, [Data] [nvarchar](200) NULL, [FormulaExp] [nvarchar](max) NULL, [Creator] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [Updater] [varchar](128) NOT NULL, [UpdateTime] [datetime] NOT NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'CellDataSource' and type = 'U') BEGIN CREATE TABLE [dbo].[CellDataSource]( [ID] [varchar](128) NOT NULL, [CellTemplateID] [varchar](128) NOT NULL, [CellDataID] [varchar](128) NOT NULL, [DataSourceID] [varchar](128) NOT NULL, [OperationType] [int] NULL, [CreateTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'DataSource' and type = 'U') BEGIN CREATE TABLE [dbo].[DataSource]( [ID] [varchar](128) NOT NULL, [Type] [int] NOT NULL, [Name] [nvarchar](500) NULL, [Amount] [decimal](20, 4) NULL, [Description] [nvarchar](max) NULL, [Creator] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [Updater] [varchar](128) NOT NULL, [UpdateTime] [datetime] NOT NULL, [KeyValueDataID] [varchar](128) NULL, [RowName] [nvarchar](2000) NULL, [ColumnName] [nvarchar](2000) NULL, [RowIndex] int NULL, [ColumnIndex] int NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'DataSourceDetail' and type = 'U') BEGIN CREATE TABLE [dbo].[DataSourceDetail]( [ID] [varchar](128) NOT NULL, [DataSourceID] [varchar](128) NOT NULL, [DataSourceType] [int] NOT NULL, [ItemValue] [varchar](128) NOT NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'Report' and type = 'U') BEGIN CREATE TABLE [dbo].[Report]( [ID] [varchar](128) NOT NULL, [TemplateID] [varchar](128) NOT NULL, [Period] [int] NOT NULL, [ProjectID] [varchar](128) NULL, [Creator] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [Updater] [varchar](128) NOT NULL, [UpdateTime] [datetime] NOT NULL, PRIMARY KEY(ID) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'FormulaCache' and type = 'U') BEGIN CREATE TABLE [dbo].[FormulaCache]( [ID] [varchar](128) NOT NULL, [Period] [int] NOT NULL, [Year] [int] NOT NULL, [Formula] [nvarchar](2000) NOT NULL, [FormulaValue] [nvarchar](200) NULL, [TargetReportTemplateID] [varchar](128) NULL, [TargetRowIndex] [int] NULL, [TargetRowName] [nvarchar](200) NULL, [TargetColumnIndex] [int] NULL, [TargetColumnName] [nvarchar](200) NULL, [TargetPeriod] [int] NULL, [TargetCellDataID] [varchar](128) NULL, [TargetCellTemplateID] [varchar](128) NULL, [ResultType] [int] NULL, [CreateTime] [datetime] NOT NULL, PRIMARY KEY(ID,Period) ); END --update 2017-6-29 IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodCellTemplate' and type = 'U') BEGIN CREATE TABLE [dbo].[PeriodCellTemplate]( [ID] [varchar](128) NOT NULL, [Period] [int] NOT NULL, [ReportTemplateID] [varchar](128) NOT NULL, [RowIndex] [int] NOT NULL, [RowName] [nvarchar](2000) NULL, [ColumnIndex] [int] NOT NULL, [ColumnName] [nvarchar](2000) NULL, [Comment] [nvarchar](2000) NULL, [CreateTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, [CopyFromID] [varchar](128) NULL, [DataType] [int] NULL, [IsReadOnly] [int] NULL, PRIMARY KEY(ID,Period) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodCellTemplateConfig' and type = 'U') BEGIN CREATE TABLE [dbo].[PeriodCellTemplateConfig]( [ID] [varchar](128) NOT NULL, [Period] [int] NOT NULL, [CellTemplateID] [varchar](128) NOT NULL, [ReportTemplateID] [varchar](128) NULL, [DataSourceType] [int] NOT NULL, [Formula] [nvarchar](4000) NULL, [ParsedFormula] [nvarchar](4000) NULL, [FormulaDescription] [nvarchar](2000) NULL, [AccountCodes] [nvarchar](2000) NULL, [InvoiceType] [int] NULL, [TaxRate] [varchar](50) NULL, [InvoiceAmountType] [int] NULL, [ModelIDs] [varchar](4000) NULL, [Creator] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [Updater] [varchar](128) NOT NULL, [UpdateTime] [datetime] NOT NULL, [InvoiceCategory] [varchar](50) NULL, [FormulaDataSource] [nvarchar](500) NULL, [Validation] [nvarchar](4000) NULL, [ParsedValidation] [nvarchar](4000) NULL, [ValidationDescription] [nvarchar](2000) NULL, [VoucherKeyword] [nvarchar](500) NULL, PRIMARY KEY(ID,Period) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodFormulaBlock' and type = 'U') BEGIN CREATE TABLE [dbo].[PeriodFormulaBlock]( [ID] [nvarchar](128) NOT NULL, [Period] [int] NOT NULL, [FormulaBlock] [nvarchar](50) NOT NULL, [Index] [int] NOT NULL, [Data] [nvarchar](200) NULL, [ResultType] [int] NULL, [DataSourceInfo] [nvarchar](max) NULL, [IsCalculated] [smallint] NULL, [Creator] [varchar](128) NULL, [CreateTime] [datetime] NULL, [Updater] [varchar](128) NULL, [UpdateTime] [datetime] NULL, PRIMARY KEY(ID,Period) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodTaxPayerReportRule' and type = 'U') BEGIN CREATE TABLE [dbo].[PeriodTaxPayerReportRule]( [ID] [varchar](128) NOT NULL, [IsDefault] [int] NOT NULL, [Period] [int] NOT NULL, [TaxPayerType] [int] NOT NULL, [TemplateGroupID] [varchar](128) NOT NULL, [OrganizationID] [varchar](max) NULL, [CreateTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, PRIMARY KEY(ID,Period) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodTaxRuleSetting' and type = 'U') BEGIN CREATE TABLE [dbo].[PeriodTaxRuleSetting]( [ID] [varchar](128) NOT NULL, [Name] [nvarchar](50) NOT NULL, [IsDefault] [int] NOT NULL, [Period] [int] NOT NULL, [GroupName] [nvarchar](50) NULL, [TaxBase] [varchar](500) NOT NULL, [TaxRate] [decimal](20, 4) NOT NULL, [CreateTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, PRIMARY KEY(ID,Period) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodTaxRuleSettingOrganization' and type = 'U') BEGIN CREATE TABLE [dbo].[PeriodTaxRuleSettingOrganization]( [ID] [varchar](128) NOT NULL, [TaxSettingID] [varchar](128) NOT NULL, [Period] [int] NOT NULL, [OrganizationID] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, PRIMARY KEY(ID,Period) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodTemplate' and type = 'U') BEGIN CREATE TABLE [dbo].[PeriodTemplate]( [ID] [varchar](128) NOT NULL, [Period] [int] NOT NULL, [Name] [nvarchar](500) NOT NULL, [Code] [varchar](500) NULL, [Path] [nvarchar](2000) NOT NULL, [ReportType] [int] NULL, [TemplateGroupID] [varchar](128) NOT NULL, [OrderIndex] [int] NULL, [CreateTime] [datetime] NOT NULL DEFAULT (getdate()), [UpdateTime] [datetime] NOT NULL DEFAULT (getdate()), [IsSystemType] [int] NOT NULL, [IsActiveAssociation] [int] NOT NULL, [ParentId] [varchar](128) NULL, PRIMARY KEY(ID,Period) ); END IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodCellReference' and type = 'U') BEGIN CREATE TABLE [dbo].[PeriodCellReference]( [ID] [nvarchar](128) NOT NULL, [Period] [int] NOT NULL, [FormulaBlockIndex] [int] NOT NULL, [ReferenceFromCellID] [nvarchar](128) NULL, [ReferenceFromTemplateID] [nvarchar](128) NULL, [ReferenceToCellID] [nvarchar](128) NULL, [ReferenceToTemplateID] [nvarchar](128) NULL, [ReferenceFromKeyValueID] [nvarchar](128) NULL, [ReferenceToKeyValueID] [nvarchar](128) NULL, [IsValidation] [int] NOT NULL, PRIMARY KEY(ID,Period) ); END GO -- report end ---------- --end v1.0.0.0------------------------------------------------------------------------------- GO IF (TYPE_ID(N'AcctValidTab') IS NULL) BEGIN CREATE TYPE [dbo].[AcctValidTab] AS TABLE( [AcctCode] [nvarchar](50) NOT NULL, [AcctSum] [numeric](18, 3) NULL, [SubSum] [numeric](18, 3) NULL, PRIMARY KEY CLUSTERED ( [AcctCode] ASC )WITH (IGNORE_DUP_KEY = OFF) ) END GO IF NOT EXISTS(SELECT 1 from sys.tables where name = 'Balance' and type = 'U') BEGIN CREATE TABLE [dbo].[Balance]( [BalanceId] [varchar](128) NOT NULL CONSTRAINT [DF_Balance_BalanceId] DEFAULT (CONVERT([varchar](128),newid())), [PeriodId] [int] NULL, [AcctCode] [nvarchar](50) NULL, [CustomerCode] [nvarchar](50) NULL CONSTRAINT [DF_Balance_CustomerCode] DEFAULT (''), [BegDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_BegDebitBal] DEFAULT ((0)), [BegCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_BegCreditBal] DEFAULT ((0)), [BegBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_BegBal] DEFAULT ((0)), [EndBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_EndBal] DEFAULT ((0)), [EndDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_EndDebitBal] DEFAULT ((0)), [EndCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_EndCreditBal] DEFAULT ((0)), [DebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_DebitBal] DEFAULT ((0)), [CreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_CreditBal] DEFAULT ((0)), [MonthId] [int] NULL, [YearDebitBal] [numeric](18, 3) NULL, [YearCreditBal] [numeric](18, 3) NULL, [CarryoverDebit] NUMERIC(18,3) NULL, [CarryoverCredit] NUMERIC(18,3) NULL, [YearCarryoverDebit] NUMERIC(18,3) NULL, [YearCarryoverCredit] NUMERIC(18,3) NULL, [DebitNet] NUMERIC(18,3) NULL, [CreditNet] NUMERIC(18,3) NULL, [YearDebitNet] NUMERIC(18,3) NULL, [YearCreditNet] NUMERIC(18,3) NULL, [StdCode] NVARCHAR(50) NULL, [MappingReason] NVARCHAR(500) NULL, [MappingUser] NVARCHAR(128) NULL, [MappingTime] DATETIME NULL, [SubProp] INT NULL, [IsDummy] [int] NULL DEFAULT(0), CONSTRAINT [PK_Balance] PRIMARY KEY CLUSTERED ( [BalanceId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) END GO IF NOT EXISTS(SELECT 1 from sys.tables where name = 'CompanyBalance' and type = 'U') BEGIN CREATE TABLE [dbo].[CompanyBalance]( [BalanceId] [varchar](128) NOT NULL CONSTRAINT [DF__CompanyBa__Balan__70DDC3D8] DEFAULT (CONVERT([varchar](128),newid())), [PeriodId] [int] NULL, [AcctCode] [nvarchar](50) NULL, [CustomerCode] [nvarchar](50) NULL CONSTRAINT [DF_CompanyBalance_CustomerCode] DEFAULT (''), [BegDebitBal] [numeric](18, 3) NULL, [BegCreditBal] [numeric](18, 3) NULL, [BegBal] [numeric](18, 3) NULL, [EndBal] [numeric](18, 3) NULL, [EndDebitBal] [numeric](18, 3) NULL, [EndCreditBal] [numeric](18, 3) NULL, [DebitBal] [numeric](18, 3) NULL, [CreditBal] [numeric](18, 3) NULL, [MonthId] [int] NULL, CONSTRAINT [PK_CompanyBalance] PRIMARY KEY CLUSTERED ( [BalanceId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 from sys.tables where name = 'CustBalance' and type = 'U') BEGIN CREATE TABLE [dbo].[CustBalance]( [BalanceId] [varchar](128) NOT NULL CONSTRAINT [DF_CustBalance_BalanceId] DEFAULT (CONVERT([varchar](50),newid())), [PeriodId] [bigint] NOT NULL CONSTRAINT [DF_CustBalance_PeriodID] DEFAULT ((1)), [AcctCode] [nvarchar](50) NOT NULL, [CustomerCode] [nvarchar](50) NULL CONSTRAINT [DF_CustBalance_CustomerCode] DEFAULT (''), [BegBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_BegBal] DEFAULT ((0)), [BegDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_BegDebitBal] DEFAULT ((0)), [BegCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_BegCreditBal] DEFAULT ((0)), [DebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_DebitBal] DEFAULT ((0)), [CreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_CreditBal] DEFAULT ((0)), [YearDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_YearDebitBal] DEFAULT ((0)), [YearCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_YearCreditBal] DEFAULT ((0)), [EndBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_EndBal] DEFAULT ((0)), [EndDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_EndDebitBal] DEFAULT ((0)), [EndCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_EndCreditBal] DEFAULT ((0)), [MonthId] [int] NULL, [IsDummy] [int] NULL DEFAULT(0), CONSTRAINT [PK_CustBalance] PRIMARY KEY CLUSTERED ( [BalanceId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 from sys.tables where name = 'CustomsInvoice' and type = 'U') BEGIN CREATE TABLE [dbo].[CustomsInvoice]( [CustomsId] [varchar](128) NOT NULL, [PayNum] [nvarchar](200) NULL, [IssueDate] [datetime] NULL, [InvoiceTaxAmount] [numeric](18, 3) NULL, [InvoiceAmount] [numeric](18, 3) NULL, [AuditResult] [nvarchar](500) NULL, [CreateTime] [datetime] NULL, [PeriodId] INT NULL, [CreatorID] [varchar](128) NULL, [Status] [int] NULL, [PartAccntedPeriod] [int] NULL, [AccntedPeriod] [int] NULL, CONSTRAINT [PK_CustomsInvoice] PRIMARY KEY CLUSTERED ( [CustomsId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 from sys.tables where name = 'Voucher' and type = 'U') BEGIN CREATE TABLE [dbo].[Voucher]( [VoucherID] [varchar](128) NOT NULL CONSTRAINT [DF_Voucher_VoucherID] DEFAULT (CONVERT([varchar](50),newid())), [VID] [nvarchar](256) NOT NULL, [Date] [date] NOT NULL, [Group] [nvarchar](50) NULL, [Period] [int] NULL, [CustomerCode] [nvarchar](50) NULL CONSTRAINT [DF_Voucher_CustomerCode] DEFAULT (''), [CustomerName] [nvarchar](100) NULL CONSTRAINT [DF_Voucher_CustomerName] DEFAULT (''), [Summary] [nvarchar](500) NULL, [AcctCode] [nvarchar](50) NULL, [Debit] [numeric](18, 3) NULL CONSTRAINT [DF_Voucher_Debit] DEFAULT ((0)), [Credit] [numeric](18, 3) NULL CONSTRAINT [DF_Voucher_Credit] DEFAULT ((0)), [Attach] [bigint] NULL, [ItemID] [nvarchar](256) NULL, [CreateTime] [datetime] NULL CONSTRAINT [DF__Voucher__Creat__1DE57479] DEFAULT (getdate()), [ImportType] [int] NULL, [CreatorID] [varchar](128) NULL, [MonthID] [int] NULL, [OriginalPeriod] INT NULL, [Status] [int] NULL, [PartBilledPeriod] [int] NULL, [BilledPeriod] [int] NULL, [StdCode] [nvarchar](50) NULL, [MappingReason] NVARCHAR(500) NULL, [MappingUser] NVARCHAR(128) NULL, [MappingTime] DATETIME NULL, [RemapId] VARCHAR(128) NULL, CONSTRAINT [PK_Voucher] PRIMARY KEY NONCLUSTERED ( [VoucherID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_Voucher_AcctCode' AND object_id = OBJECT_ID('dbo.Voucher')) BEGIN CREATE CLUSTERED INDEX [IX_Voucher_AcctCode] ON [dbo].[Voucher] ( [AcctCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_Voucher_CustomerCode' AND object_id = OBJECT_ID('dbo.Voucher')) BEGIN CREATE NONCLUSTERED INDEX [IX_Voucher_CustomerCode] ON [dbo].[Voucher] ( [CustomerCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_Voucher_Summary' AND object_id = OBJECT_ID('dbo.Voucher')) BEGIN CREATE NONCLUSTERED INDEX [IX_Voucher_Summary] ON [dbo].[Voucher] ( [Summary] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_Voucher_VID_ItemID_PeriodID' AND object_id = OBJECT_ID('dbo.Voucher')) BEGIN CREATE NONCLUSTERED INDEX [IX_Voucher_VID_ItemID_PeriodID] ON [dbo].[Voucher] ( [VID] ASC, [ItemID] ASC, [Period] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_Voucher_ImportType' AND object_id = OBJECT_ID('dbo.Voucher')) BEGIN CREATE NONCLUSTERED INDEX [IX_Voucher_ImportType] ON [dbo].[Voucher] ( [ImportType] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END GO ALTER TABLE [dbo].[CustomsInvoice] ADD CONSTRAINT [DF_CustomsInvoice_CustomsId] DEFAULT (CONVERT([varchar](50),newid())) FOR [CustomsId] GO ALTER TABLE [dbo].[ValidationInfo] ADD CONSTRAINT [DF_ValidationInfo_ID] DEFAULT (CONVERT([varchar](50),newid())) FOR [ID] GO IF NOT EXISTS (select 1 from sys.tables where name = 'CustBalanceStd' and type = 'U') BEGIN CREATE TABLE [dbo].[CustBalanceStd]( [BalanceID] [VARCHAR](128) NOT NULL DEFAULT (CONVERT([VARCHAR](50),NEWID())), [PeriodID] [INT] NOT NULL, [AcctCode] [NVARCHAR](50) NOT NULL, [CustomerCode] [NVARCHAR](50) NULL, [BegBal] [NUMERIC](18, 3) NULL, [DebitBal] [NUMERIC](18, 3) NULL, [CreditBal] [NUMERIC](18, 3) NULL, [YearDebitBal] [NUMERIC](18, 3) NULL, [YearCreditBal] [NUMERIC](18, 3) NULL, [EndBal] [NUMERIC](18, 3) NULL, [BegDebitBal] [NUMERIC](18, 3) NULL, [BegCreditBal] [NUMERIC](18, 3) NULL, [EndDebitBal] [NUMERIC](18, 3) NULL, [EndCreditBal] [NUMERIC](18, 3) NULL, CONSTRAINT [PK_CustBalanceStd] PRIMARY KEY CLUSTERED ( [BalanceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (select 1 from sys.tables where name = 'CustBalanceStdManual' and type = 'U') BEGIN CREATE TABLE [dbo].[CustBalanceStdManual]( [BalanceID] [VARCHAR](128) NOT NULL DEFAULT (CONVERT([VARCHAR](50),NEWID())), [PeriodID] [BIGINT] NOT NULL, [AcctCode] [NVARCHAR](50) NOT NULL, [CustomerCode] [NVARCHAR](50) NULL, [BegBal] [NUMERIC](18, 3) NULL, [BegDebitBal] [NUMERIC](18, 3) NULL, [BegCreditBal] [NUMERIC](18, 3) NULL, [DebitBal] [NUMERIC](18, 3) NULL, [CreditBal] [NUMERIC](18, 3) NULL, [YearDebitBal] [NUMERIC](18, 3) NULL, [YearCreditBal] [NUMERIC](18, 3) NULL, [EndBal] [NUMERIC](18, 3) NULL, [EndDebitBal] [NUMERIC](18, 3) NULL, [EndCreditBal] [NUMERIC](18, 3) NULL, CONSTRAINT [PK_CustBalanceStdManual] PRIMARY KEY CLUSTERED ( [BalanceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (select 1 from sys.tables where name = 'BalanceStdManual' and type = 'U') BEGIN CREATE TABLE [dbo].[BalanceStdManual]( [BalanceID] [VARCHAR](128) NOT NULL DEFAULT (CONVERT([VARCHAR](50),NEWID())), [PeriodID] [BIGINT] NOT NULL, [AcctCode] [NVARCHAR](50) NOT NULL, [BegBal] [NUMERIC](18, 3) NULL, [BegDebitBal] [NUMERIC](18, 3) NULL, [BegCreditBal] [NUMERIC](18, 3) NULL, [DebitBal] [NUMERIC](18, 3) NULL, [CreditBal] [NUMERIC](18, 3) NULL, [YearDebitBal] [NUMERIC](18, 3) NULL, [YearCreditBal] [NUMERIC](18, 3) NULL, [EndBal] [NUMERIC](18, 3) NULL, [EndDebitBal] [NUMERIC](18, 3) NULL, [EndCreditBal] [NUMERIC](18, 3) NULL, [SubProp] [INT] NULL, [CarryoverDebit] [NUMERIC](18, 3) NULL, [CarryoverCredit] [NUMERIC](18, 3) NULL, [YearCarryoverDebit] [NUMERIC](18, 3) NULL, [YearCarryoverCredit] [NUMERIC](18, 3) NULL, [DebitNet] [NUMERIC](18, 3) NULL, [CreditNet] [NUMERIC](18, 3) NULL, [YearDebitNet] [NUMERIC](18, 3) NULL, [YearCreditNet] [NUMERIC](18, 3) NULL, CONSTRAINT [PK_BalanceStdManual] PRIMARY KEY CLUSTERED ( [BalanceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS(SELECT 1 from sys.objects where name = 'VoucherMain' and type_desc = 'USER_TABLE') BEGIN CREATE TABLE [dbo].[VoucherMain]( [VoucherMainID] VARCHAR(128) NOT NULL DEFAULT (CONVERT([varchar](50),newid())), [Period] [int] NULL, [Group] [nvarchar](50) NULL, [VID] [nvarchar](256) NOT NULL, [VoucherCount] [int] NULL, [AcctCodeCount] [int] NULL, [Amount] [decimal](18, 3) NULL, [AmountCredit] [decimal](18, 3) NULL, [CreateTime] DATETIME NULL, [UpdateTime] DATETIME NULL, [CreatorID] VARCHAR(128) NULL PRIMARY KEY CLUSTERED ( [VoucherMainID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (select 1 from sys.tables where name = 'AccountRemap' and type = 'USER_TABLE') BEGIN CREATE TABLE [dbo].[AccountRemap]( [RemapId] [VARCHAR](128) NOT NULL CONSTRAINT [DF_AccountRemap_RemapId] DEFAULT (CONVERT([VARCHAR](50),NEWID())), [Period] [INT] NOT NULL, [AcctCode] [NVARCHAR](50) NULL, [CustomerCode] [NVARCHAR](50) NULL, [RemapDebit] [NUMERIC](18, 3) NULL, [RemapCredit] [NUMERIC](18, 3) NULL, [StdCode] [NVARCHAR](50) NULL, [ReMappName] [NVARCHAR](200) NOT NULL, [ReMappReason] [NVARCHAR](500) NULL, [ReMappTypeId] [INT] NOT NULL, [CreateTime] [DATETIME] NULL, [UpdateTime] [DATETIME] NULL, [CreatorID] [NVARCHAR](128) NULL, [RemapBatchId] [VARCHAR](128) NULL, CONSTRAINT [PK_C_AccountRemap] PRIMARY KEY CLUSTERED ( [RemapId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (select 1 from sys.tables where name = 'AccountRemapSum' and type = 'USER_TABLE') BEGIN CREATE TABLE [dbo].[AccountRemapSum]( [AccountRemapSumId] [VARCHAR](128) NOT NULL CONSTRAINT [DF_AccountRemapSum_AccountRemapSumId] DEFAULT (CONVERT([VARCHAR](50),NEWID())), [Period] [INT] NULL, [AcctCode] [NVARCHAR](50) NULL, [Parent] [NVARCHAR](50) NULL, [AcctLevel] [INT] NULL, [RemapDebit] [NUMERIC](18, 3) NULL, [RemapCredit] [NUMERIC](18, 3) NULL, [CreateTime] [DATETIME] NULL, [UpdateTime] [DATETIME] NULL, [CreatorID] [NVARCHAR](128) NULL, CONSTRAINT [PK_AccountRemapSum] PRIMARY KEY CLUSTERED ( [AccountRemapSumId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'AcctValid' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].AcctValid END GO IF EXISTS(SELECT 1 from sys.objects where name = 'ImportPackageCheckDuplicate' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].ImportPackageCheckDuplicate END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitBalanceByConditions' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].InitBalanceByConditions END GO IF EXISTS(SELECT 1 from sys.objects where name = 'UpdateNonLeafVocuhers' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].UpdateNonLeafVocuhers END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitVoucherMain' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitVoucherMain] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitAccountIsInVoucher' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitAccountIsInVoucher] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitAccount' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitAccount] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitCustBalanceStd' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitCustBalanceStd] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitCarryover' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitCarryover] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitBalanceStd' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitBalanceStd] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitCustBalanceStdRemap' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitCustBalanceStdRemap] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitBalanceStdRemap' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitBalanceStdRemap] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitBalanceStdRemapCarryover' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitBalanceStdRemapCarryover] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'InitAccountRemapSum' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[InitAccountRemapSum] END GO IF EXISTS(SELECT 1 from sys.objects where name = 'UpdateParentCalValues' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[UpdateParentCalValues] END GO CREATE PROCEDURE [dbo].[AcctValid] -- Add the parameters for the stored procedure here --@avt [dbo].[AcctValidTab] READONLY @periodId int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @temp AS [dbo].[AcctValidTab]; DECLARE @AcctLevel int; DECLARE @Parent nvarchar(50); DECLARE @ParentBegBal numeric(18,3); DECLARE @SumBegBal numeric(18,3); DECLARE @T_RealBegBal Table (BegBal numeric(18, 3) null); select @AcctLevel = Max(AcctLevel) from [dbo].[EnterpriseAccount] while @AcctLevel >1 begin DECLARE Parent_Cursor CURSOR FOR select distinct ParentCode from [dbo].[EnterpriseAccount] where AcctLevel = @AcctLevel; OPEN Parent_Cursor; FETCH NEXT FROM Parent_Cursor INTO @Parent WHILE @@FETCH_STATUS = 0 BEGIN --select @ParentBegBal= BegBal from [dbo].[C_CustBalance] where AcctCode=@Parent and PeriodID = @periodId and CustomerCode = ''; --select @SumBegBal = sum(c.BegBal) --from [dbo].[C_CustBalance] c --join [dbo].[C_Account] a on c.AcctCode = a.Code --where c.AcctCode like @Parent+'%' and a.AcctLevel= @AcctLevel and c.CustomerCode='' and PeriodID = @periodId; select @ParentBegBal= (c.BegBal * a.Direction) from [dbo].[CustBalance] c inner join [dbo].[EnterpriseAccount] a on c.AcctCode = a.AcctCode where c.AcctCode=@Parent and c.PeriodID = @periodId and c.CustomerCode = ''; insert into @T_RealBegBal(BegBal) select t.RealBegBal from (select (c.BegBal * a.Direction) as RealBegBal from [dbo].[CustBalance] c join [dbo].[EnterpriseAccount] a on c.AcctCode = a.AcctCode where c.AcctCode like @Parent+'%' and a.AcctLevel= @AcctLevel and c.CustomerCode='' and PeriodID = @periodId) t; select @SumBegBal = sum(BegBal) from @T_RealBegBal if round(@ParentBegBal,2) <>round(@SumBegBal,2) begin insert @temp values (@Parent,round(@ParentBegBal,2),round(@SumBegBal,2)); end; delete from @T_RealBegBal FETCH NEXT FROM Parent_Cursor INTO @Parent END; CLOSE Parent_Cursor; DEALLOCATE Parent_Cursor; set @AcctLevel = @AcctLevel -1; end; select AcctCode,AcctSum,SubSum from @temp; END GO CREATE PROCEDURE [dbo].[ImportPackageCheckDuplicate] AS BEGIN SET NOCOUNT ON; Create Table #dupTable ( Num int, TableName nvarchar(50), DuplicateCount int ) declare @acctDupCount as int, @cusBalDupCount as int, @cutDupCount as int, @periodDupCount as int, @voucherDupCount as int, @invoiceDupCount as int /* --C_Account duplicate check:Code SELECT @acctDupCount = COUNT(*) FROM( SELECT ROW_NUMBER() OVER(PARTITION BY Code ORDER BY AccountID ) AS rn FROM dbo.C_Account) as d where d.rn > 1 insert into #dupTable(Num,TableName,DuplicateCount) values (1,'Account',@acctDupCount) */ --C_CustBalance duplicate check:PeriodId, AcctCode,CustomerCode select @cusBalDupCount = Count(*) from( SELECT ROW_NUMBER() OVER(PARTITION BY PeriodID,AcctCode,CustomerCode ORDER BY BalanceID ) AS rn FROM dbo.CustBalance ) as d where d.rn > 1 insert into #dupTable(Num,TableName,DuplicateCount) values (1,'CustBalance',@cusBalDupCount) /* --C_Period duplicate check:BegDate,EndData select @periodDupCount = count(*) from ( select ROW_NUMBER() over(partition by BegDate,EndDate order by PeriodID) as rn from dbo.C_Period ) as d where d.rn > 1 insert into #dupTable(Num,TableName,DuplicateCount) values (3,'Period',@periodDupCount) --C_Customer duplicate check:CustomerCode select @cutDupCount = count(*) from ( SELECT ROW_NUMBER() OVER(PARTITION BY CustomerCode ORDER BY CustomerID ) AS rn FROM dbo.C_Customer ) as d where d.rn > 1 insert into #dupTable(Num,TableName,DuplicateCount) values (4,'Customer',@cutDupCount) */ --C_Voucher duplicate check:VID,ItemID,VID,Period,Group select @voucherDupCount = count(*) from ( select ROW_NUMBER() over (partition by VID,ItemID,Period,[Group] order by VoucherID) as rn from dbo.Voucher where ItemID is not null ) as d where d.rn > 1 insert into #dupTable(Num,TableName,DuplicateCount) values (2,'Voucher',@voucherDupCount) /* --V_VoucherInvoice duplicate check:Period, VID, ItemID, TransactionCode, InvoiceNumber,Group select @invoiceDupCount = count(*) from ( select ROW_NUMBER() over(partition by Period, VID, ItemID, TransactionCode, InvoiceNumber,[Group] order by VoucherInvoiceID) as rn from dbo.V_VoucherInvoice ) as d where d.rn > 1 insert into #dupTable(Num,TableName,DuplicateCount) values (6,'VoucherInvoice',@invoiceDupCount) */ select Num,TableName,DuplicateCount from #dupTable drop table #dupTable END GO /****** Object: StoredProcedure [dbo].[InitBalanceByConditions] Script Date: 08/05/2017 17:15:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Bin Li -- Create date: 2016.06.12 -- Parameters: 1. @PeriodIdParam: -- @PeriodIdParam < 0: calculate entire year; @StartPeriod > 0: calculate from period @PeriodIdParam -- Description: 1. manage data by conditions: 1) BegBal imported 2) BegDebitBal,BegCreditBal -- 2. voucher is imported by JournalEntry(ImportType = 3) will be filtered -- ============================================= CREATE PROCEDURE [dbo].[InitBalanceByConditions] @PeriodIdParam INT AS BEGIN SET NOCOUNT ON; DECLARE @AcctCode NVARCHAR(50); DECLARE @CustomerCode NVARCHAR(50); DECLARE @sumDebit FLOAT; DECLARE @sumCredit FLOAT; DECLARE @BegBal FLOAT; DECLARE @EndBal FLOAT; DECLARE @sumYearDebit FLOAT; DECLARE @sumYearCredit FLOAT; DECLARE @isLeaf INT; DECLARE @Direction INT; DECLARE @BegDebitBal FLOAT; DECLARE @BegCreditBal FLOAT; DECLARE @EndDebitBal FLOAT; DECLARE @EndCreditBal FLOAT; DECLARE @PeriodID INT; DECLARE @PeriodIDMax INT; DECLARE @StartPeriodID INT; DECLARE @IsDummy INT; DECLARE @DummyAcctChildrenTb TABLE(AcctCode NVARCHAR(50)); IF(@PeriodIdParam < 0) BEGIN SET @StartPeriodID = 1; END ELSE BEGIN SET @StartPeriodID = @PeriodIdParam; END --Step 1: Delete the data of CustBalance except the first period and truncate Balance DELETE FROM [dbo].[CustBalance] WHERE PeriodId > @StartPeriodID TRUNCATE TABLE [dbo].[Balance]; --Step 2: Get AcctCode + CustomerCode from Voucher that does not exist in CustBalance, then insert them into CustBalance DECLARE Customer_Cursor CURSOR FOR SELECT AcctCode,CustomerCode FROM [dbo].[Voucher] WHERE Period = @StartPeriodID GROUP BY AcctCode,CustomerCode EXCEPT SELECT AcctCode ,CustomerCode FROM [dbo].CustBalance WHERE PeriodId = @StartPeriodID GROUP BY AcctCode,CustomerCode; OPEN Customer_Cursor; FETCH NEXT FROM Customer_Cursor INTO @AcctCode, @CustomerCode; WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS(SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode) BEGIN SELECT @BegBal= ISNULL(EndBal,0) FROM dbo.CustBalance WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @StartPeriodID - 1 INSERT [dbo].[CustBalance](PeriodId,AcctCode,CustomerCode,BegBal) VALUES (@StartPeriodID,@AcctCode,@CustomerCode,@BegBal); END FETCH NEXT FROM Customer_Cursor INTO @AcctCode, @CustomerCode; END; CLOSE Customer_Cursor; DEALLOCATE Customer_Cursor; --Step 3: Get AcctCode + CustomerCode(always empty) from EnterpriseAccount that does not exist in CustBalance, then insert them into CustBalance DECLARE AcctCode_Cursor CURSOR FOR SELECT AcctCode, '' FROM [dbo].EnterpriseAccount EXCEPT SELECT AcctCode,CustomerCode FROM [dbo].[CustBalance] WHERE PeriodId = @StartPeriodID GROUP BY AcctCode,CustomerCode; OPEN AcctCode_Cursor; FETCH NEXT FROM AcctCode_Cursor INTO @AcctCode, @CustomerCode; WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS(SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode) BEGIN -- if missing account is not leaf AND it has any level children account in CompanyBalance, then set IsDummy = 1 SELECT @isLeaf = IsLeaf FROM dbo.EnterpriseAccount WHERE AcctCode = @AcctCode SET @IsDummy = 0; DELETE FROM @DummyAcctChildrenTb IF(@isLeaf = 0) BEGIN WITH tblChild AS -- get all level children for @AcctCode ( SELECT * FROM dbo.EnterpriseAccount WHERE AcctCode = @AcctCode UNION ALL SELECT a1.* FROM dbo.EnterpriseAccount a1 JOIN tblChild ON a1.ParentCode = tblChild.AcctCode ) INSERT INTO @DummyAcctChildrenTb ( AcctCode ) SELECT AcctCode FROM tblChild WHERE tblChild.AcctCode != @AcctCode IF EXISTS(SELECT 1 FROM dbo.CompanyBalance WHERE AcctCode IN (SELECT AcctCode FROM @DummyAcctChildrenTb)) BEGIN SET @IsDummy = 1; END END SELECT @BegBal= ISNULL(EndBal,0) FROM dbo.CustBalance WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @StartPeriodID - 1 INSERT [dbo].[CustBalance](PeriodId, AcctCode, CustomerCode,BegBal,IsDummy) VALUES (@StartPeriodID,@AcctCode ,'' ,@BegBal, @IsDummy); END FETCH NEXT FROM AcctCode_Cursor INTO @AcctCode, @CustomerCode; END; CLOSE AcctCode_Cursor; DEALLOCATE AcctCode_Cursor; IF EXISTS ( SELECT 1 FROM dbo.[CustBalance] WHERE BegBal != 0 AND PeriodId = @StartPeriodID) -- import BegBal BEGIN UPDATE dbo.CustBalance SET BegBal = 0 WHERE PeriodId = @StartPeriodID AND BegBal IS NULL; --Step 4: Update the @StartPeriodID period of each CustBalance record DECLARE CustBal_Cursor CURSOR FOR SELECT cb.AcctCode, CustomerCode, SUM(BegBal), IsLeaf, Direction FROM [dbo].[CustBalance] cb INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode WHERE PeriodId = @StartPeriodID GROUP BY cb.AcctCode, CustomerCode, IsLeaf, Direction; OPEN CustBal_Cursor; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode,@BegBal, @isLeaf, @Direction; WHILE @@FETCH_STATUS = 0 BEGIN -- if account + customer was calculated in (@StartPeriodID - 1), then use its EndBal @StartPeriodID's BegBal. -- otherwise, stay default IF EXISTS (SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @StartPeriodID - 1) BEGIN SELECT @BegBal = SUM(cb.EndBal) FROM [dbo].[CustBalance] cb INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode WHERE cb.PeriodId = @StartPeriodID - 1 AND cb.AcctCode = @AcctCode AND cb.CustomerCode = @CustomerCode UPDATE dbo.CustBalance SET BegBal = @BegBal WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode END IF ( @isLeaf = 1 AND @CustomerCode <> '') OR ( @isLeaf = 0 AND EXISTS ( SELECT 1 FROM dbo.Voucher WHERE AcctCode LIKE @AcctCode + '@%' AND (ImportType IS NULL OR ImportType != 3)) AND @CustomerCode <> '' ) BEGIN SELECT @sumDebit = SUM(Debit) , @sumCredit = SUM(Credit) FROM [dbo].[Voucher] WHERE AcctCode LIKE @AcctCode + '%' AND CustomerCode = @CustomerCode AND Period = @StartPeriodID AND (ImportType IS NULL OR ImportType != 3); END; ELSE BEGIN SELECT @sumDebit = SUM(Debit) , @sumCredit = SUM(Credit) FROM [dbo].[Voucher] WHERE AcctCode LIKE @AcctCode + '%' AND Period = @StartPeriodID AND (ImportType IS NULL OR ImportType != 3); END; --GET SUM(DebitBal) and SUM(CreditBal) of previous periods to cal YearDebitBal and YearCreditBal SELECT @sumYearDebit = ISNULL(SUM(DebitBal),0) , @sumYearCredit = ISNULL(SUM(CreditBal),0) FROM [dbo].[CustBalance] WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId <= @StartPeriodID - 1; IF @sumDebit IS NOT NULL BEGIN UPDATE [dbo].[CustBalance] SET DebitBal = @sumDebit , CreditBal = @sumCredit , YearDebitBal = @sumDebit + @sumYearDebit , YearCreditBal = @sumCredit + @sumYearCredit, EndBal = ( @BegBal * @Direction + @sumDebit - @sumCredit ) * @Direction , BegDebitBal = CASE WHEN @Direction = 1 THEN @BegBal ELSE 0 END , BegCreditBal = CASE WHEN @Direction = -1 THEN @BegBal ELSE 0 END , EndDebitBal = CASE WHEN @Direction = 1 THEN ( @BegBal * @Direction + @sumDebit - @sumCredit ) * @Direction ELSE 0 END , EndCreditBal = CASE WHEN @Direction = -1 THEN ( @BegBal * @Direction + @sumDebit - @sumCredit ) * @Direction ELSE 0 END WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @StartPeriodID; END; ELSE BEGIN UPDATE [dbo].[CustBalance] SET DebitBal = 0 , CreditBal = 0 , YearDebitBal = 0 , YearCreditBal = 0 , EndBal = @BegBal , BegDebitBal = CASE WHEN @Direction = 1 THEN @BegBal ELSE 0 END , BegCreditBal = CASE WHEN @Direction = -1 THEN @BegBal ELSE 0 END , EndDebitBal = CASE WHEN @Direction = 1 THEN @BegBal ELSE 0 END , EndCreditBal = CASE WHEN @Direction = -1 THEN @BegBal ELSE 0 END WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @StartPeriodID; END; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode, @BegBal, @isLeaf, @Direction; END; CLOSE CustBal_Cursor; DEALLOCATE CustBal_Cursor; --update BegBal for dummy account EXECUTE dbo.UpdateParentCalValues @CalPeriodId = @StartPeriodID, @IsImportBegBal = 1; --Step 5: Insert the rest 11 periods of each CustBalance record SET @PeriodID = @StartPeriodID + 1; SELECT @PeriodIDMax = MAX(Period) FROM dbo.Voucher WHERE ImportType IS NULL OR ImportType != 3; IF ISNULL(@PeriodIDMax,0) < 12 BEGIN SET @PeriodIDMax = 12 END WHILE @PeriodID <= @PeriodIDMax BEGIN DECLARE CustBal_Cursor CURSOR FOR SELECT cb.AcctCode , CustomerCode , SUM(EndBal) , IsLeaf , Direction FROM [dbo].[CustBalance] cb INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode WHERE PeriodId = @PeriodID - 1 -- Get last period EndBal as current period BegBal column value GROUP BY cb.AcctCode , CustomerCode , IsLeaf , Direction; OPEN CustBal_Cursor; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode, @EndBal, @isLeaf, @Direction; WHILE @@FETCH_STATUS = 0 BEGIN --1. Cal Debit and Credit of current period IF ( @isLeaf = 1 AND @CustomerCode <> '') OR ( @isLeaf = 0 AND EXISTS ( SELECT 1 FROM dbo.Voucher WHERE AcctCode LIKE @AcctCode + '@%' AND (ImportType IS NULL OR ImportType != 3)) AND @CustomerCode <> '' ) BEGIN SELECT @sumDebit = SUM(Debit) , @sumCredit = SUM(Credit) FROM [dbo].[Voucher] WHERE AcctCode LIKE @AcctCode + '%' AND CustomerCode = @CustomerCode AND Period = @PeriodID AND (ImportType IS NULL OR ImportType != 3); END; ELSE BEGIN SELECT @sumDebit = SUM(Debit) , @sumCredit = SUM(Credit) FROM [dbo].[Voucher] WHERE AcctCode LIKE @AcctCode + '%' AND Period = @PeriodID AND (ImportType IS NULL OR ImportType != 3); END; --2. GET SUM(DebitBal) and SUM(CreditBal) of previous periods to cal YearDebitBal and YearCreditBal SELECT @sumYearDebit = SUM(DebitBal) , @sumYearCredit = SUM(CreditBal) FROM [dbo].[CustBalance] WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId <= @PeriodID - 1; -- set dummy value for current period ; IF EXISTS(SELECT 1 FROM dbo.CustBalance WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @PeriodID -1 AND IsDummy = 1) BEGIN SET @IsDummy = 1; END ELSE BEGIN SET @IsDummy = 0; END --4. Insert value IF @sumDebit IS NOT NULL BEGIN INSERT [dbo].[CustBalance] ( PeriodId , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , BegDebitBal , BegCreditBal , EndDebitBal , EndCreditBal, IsDummy ) VALUES ( @PeriodID , @AcctCode , @CustomerCode , @EndBal , @sumDebit , @sumCredit , @sumYearDebit + @sumDebit , @sumYearCredit + @sumCredit , ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction , CASE WHEN @Direction = 1 THEN @EndBal ELSE 0 END , CASE WHEN @Direction = -1 THEN @EndBal ELSE 0 END , CASE WHEN @Direction = 1 THEN ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction ELSE 0 END , CASE WHEN @Direction = -1 THEN ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction ELSE 0 END, @IsDummy ); END; ELSE BEGIN INSERT [dbo].[CustBalance] ( PeriodId , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , BegDebitBal , BegCreditBal , EndDebitBal , EndCreditBal, IsDummy ) VALUES ( @PeriodID , @AcctCode , @CustomerCode , @EndBal , 0 , 0 , @sumYearDebit , @sumYearCredit , @EndBal , CASE WHEN @Direction = 1 THEN @EndBal ELSE 0 END , CASE WHEN @Direction = -1 THEN @EndBal ELSE 0 END , CASE WHEN @Direction = 1 THEN @EndBal ELSE 0 END , CASE WHEN @Direction = -1 THEN @EndBal ELSE 0 END, @IsDummy ); END; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode,@CustomerCode, @EndBal, @isLeaf,@Direction; END; CLOSE CustBal_Cursor; DEALLOCATE CustBal_Cursor; SET @PeriodID = @PeriodID + 1; END; END; ELSE -- import BegDebitBal and BegCreditBal BEGIN -- set BegDebitBal/BegCreditBal as 0 if it is null UPDATE dbo.CustBalance SET BegDebitBal = 0 WHERE PeriodId = @StartPeriodID AND BegDebitBal IS NULL UPDATE dbo.CustBalance SET BegCreditBal = 0 WHERE PeriodId = @StartPeriodID AND BegCreditBal IS NULL --Step 4: Update the first period of each CustBalance record DECLARE CustBal_Cursor CURSOR FOR SELECT cb.AcctCode , CustomerCode , SUM(BegBal) , IsLeaf , Direction , SUM(BegDebitBal) , SUM(BegCreditBal) FROM [dbo].[CustBalance] cb INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode WHERE PeriodId = @StartPeriodID GROUP BY cb.AcctCode, CustomerCode, IsLeaf, Direction; OPEN CustBal_Cursor; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode, @BegBal, @isLeaf, @Direction, @BegDebitBal, @BegCreditBal; WHILE @@FETCH_STATUS = 0 BEGIN -- if account + customer was calculated in (@StartPeriodID - 1), then use its EndDebitBal/EndCreditBal as @StartPeriodID's BegDebitBal/BegCreditBal -- otherwise, stay default IF EXISTS (SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @StartPeriodID - 1) BEGIN SELECT @BegDebitBal = SUM(cb.EndDebitBal), @BegCreditBal = SUM(cb.EndCreditBal) FROM [dbo].[CustBalance] cb INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode WHERE cb.PeriodId = @StartPeriodID - 1 AND cb.AcctCode = @AcctCode AND cb.CustomerCode = @CustomerCode UPDATE dbo.CustBalance SET BegDebitBal = @BegDebitBal WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode UPDATE dbo.CustBalance SET BegCreditBal = @BegCreditBal WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode END --set BegBal value SET @BegBal = ( @BegDebitBal - @BegCreditBal ) * @Direction; IF ( @isLeaf = 1 AND @CustomerCode <> '') OR ( @isLeaf = 0 AND EXISTS ( SELECT 1 FROM dbo.Voucher WHERE AcctCode LIKE @AcctCode + '@%' AND (ImportType IS NULL OR ImportType != 3)) AND @CustomerCode <> '' ) BEGIN SELECT @sumDebit = CASE WHEN SUM(Debit) IS NULL THEN 0 ELSE SUM(Debit) END , @sumCredit = CASE WHEN SUM(Credit) IS NULL THEN 0 ELSE SUM(Credit) END FROM [dbo].[Voucher] WHERE AcctCode LIKE @AcctCode + '%' AND CustomerCode = @CustomerCode AND Period = @StartPeriodID AND (ImportType IS NULL OR ImportType != 3); END; ELSE BEGIN SELECT @sumDebit = CASE WHEN SUM(Debit) IS NULL THEN 0 ELSE SUM(Debit) END , @sumCredit = CASE WHEN SUM(Credit) IS NULL THEN 0 ELSE SUM(Credit) END FROM [dbo].[Voucher] WHERE AcctCode LIKE @AcctCode + '%' AND Period = @StartPeriodID AND (ImportType IS NULL OR ImportType != 3); END; --GET SUM(DebitBal) and SUM(CreditBal) of previous periods to cal YearDebitBal and YearCreditBal SELECT @sumYearDebit = ISNULL(SUM(DebitBal),0) , @sumYearCredit = ISNULL(SUM(CreditBal),0) FROM [dbo].[CustBalance] WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId <= @StartPeriodID - 1; IF @sumDebit IS NOT NULL BEGIN UPDATE [dbo].[CustBalance] SET DebitBal = @sumDebit , CreditBal = @sumCredit , YearDebitBal = @sumDebit + @sumYearDebit , YearCreditBal = @sumCredit + @sumYearCredit, EndBal = ( @BegBal * @Direction + @sumDebit - @sumCredit ) * @Direction , BegBal = @BegBal , EndDebitBal = CASE WHEN @Direction = 1 THEN ( @BegBal * @Direction + @sumDebit - @sumCredit ) * @Direction ELSE 0 END , EndCreditBal = CASE WHEN @Direction = -1 THEN ( @BegBal * @Direction + @sumDebit - @sumCredit ) * @Direction ELSE 0 END WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @StartPeriodID; END; ELSE BEGIN UPDATE [dbo].[CustBalance] SET DebitBal = 0 , CreditBal = 0 , YearDebitBal = 0 , YearCreditBal = 0 , EndBal = @BegBal , BegBal = @BegBal , EndDebitBal = CASE WHEN @Direction = 1 THEN @BegBal ELSE 0 END , EndCreditBal = CASE WHEN @Direction = -1 THEN @BegBal ELSE 0 END WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @StartPeriodID; END; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode, @BegBal, @isLeaf, @Direction,@BegDebitBal, @BegCreditBal; END; CLOSE CustBal_Cursor; DEALLOCATE CustBal_Cursor; --update BegBal for dummy account EXECUTE dbo.UpdateParentCalValues @CalPeriodId = @StartPeriodID, @IsImportBegBal = 0; --Step 5: Insert the rest 11 periods of each CustBalance record SET @PeriodID = @StartPeriodID + 1; SELECT @PeriodIDMax = MAX(Period) FROM dbo.Voucher WHERE ImportType IS NULL OR ImportType != 3; IF ISNULL(@PeriodIDMax, 0) < 12 BEGIN SET @PeriodIDMax = 12; END; WHILE @PeriodID <= @PeriodIDMax BEGIN DECLARE CustBal_Cursor CURSOR FOR SELECT cb.AcctCode , CustomerCode , SUM(EndBal) , IsLeaf , Direction , SUM(EndDebitBal) , SUM(EndCreditBal) FROM [dbo].[CustBalance] cb INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode WHERE PeriodId = @PeriodID - 1 -- get last period EndBal, EndDebitBal,EndCreditBal as current period Beg columns value GROUP BY cb.AcctCode, CustomerCode, IsLeaf, Direction; OPEN CustBal_Cursor; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode,@CustomerCode, @EndBal, @isLeaf, @Direction,@EndDebitBal, @EndCreditBal; WHILE @@FETCH_STATUS = 0 BEGIN --1. Cal Debit and Credit of current period IF ( @isLeaf = 1 AND @CustomerCode <> '') OR ( @isLeaf = 0 AND EXISTS ( SELECT 1 FROM dbo.Voucher WHERE AcctCode LIKE @AcctCode + '@%' AND (ImportType IS NULL OR ImportType != 3)) AND @CustomerCode <> '' ) BEGIN SELECT @sumDebit = CASE WHEN SUM(Debit) IS NULL THEN 0 ELSE SUM(Debit) END , @sumCredit = CASE WHEN SUM(Credit) IS NULL THEN 0 ELSE SUM(Credit) END FROM [dbo].[Voucher] WHERE AcctCode LIKE @AcctCode + '%' AND CustomerCode = @CustomerCode AND Period = @PeriodID AND (ImportType IS NULL OR ImportType != 3); END; ELSE BEGIN SELECT @sumDebit = CASE WHEN SUM(Debit) IS NULL THEN 0 ELSE SUM(Debit) END , @sumCredit = CASE WHEN SUM(Credit) IS NULL THEN 0 ELSE SUM(Credit) END FROM [dbo].[Voucher] WHERE AcctCode LIKE @AcctCode + '%' AND Period = @PeriodID AND (ImportType IS NULL OR ImportType != 3); END; --2. GET SUM(DebitBal) and SUM(CreditBal) of previous periods to cal YearDebitBal and YearCreditBal SELECT @sumYearDebit = CASE WHEN SUM(DebitBal) IS NULL THEN 0 ELSE SUM(DebitBal) END , @sumYearCredit = CASE WHEN SUM(CreditBal) IS NULL THEN 0 ELSE SUM(CreditBal) END FROM [dbo].[CustBalance] WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId <= @PeriodID - 1; -- set dummy value for current period ; IF EXISTS(SELECT 1 FROM dbo.CustBalance WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @PeriodID - 1 AND IsDummy = 1) BEGIN SET @IsDummy = 1; END ELSE BEGIN SET @IsDummy = 0; END --4. Insert value IF @sumDebit IS NOT NULL BEGIN INSERT [dbo].[CustBalance] ( PeriodId , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , BegDebitBal , BegCreditBal , EndDebitBal , EndCreditBal, IsDummy ) VALUES ( @PeriodID , @AcctCode , @CustomerCode , @EndBal , @sumDebit , @sumCredit , @sumYearDebit + @sumDebit , @sumYearCredit + @sumCredit , ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction , @EndDebitBal , @EndCreditBal , CASE WHEN @Direction = 1 THEN ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction ELSE 0 END , CASE WHEN @Direction = -1 THEN ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction ELSE 0 END, @IsDummy ); END; ELSE BEGIN INSERT [dbo].[CustBalance] ( PeriodId , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , BegDebitBal , BegCreditBal , EndDebitBal , EndCreditBal, IsDummy ) VALUES ( @PeriodID , @AcctCode , @CustomerCode , @EndBal , 0 , 0 , @sumYearDebit , @sumYearCredit , @EndBal , @EndDebitBal , @EndCreditBal , CASE WHEN @Direction = 1 THEN @EndBal ELSE 0 END , CASE WHEN @Direction = -1 THEN @EndBal ELSE 0 END, @IsDummy ); END; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode,@CustomerCode, @EndBal, @isLeaf, @Direction, @EndDebitBal, @EndCreditBal; END; CLOSE CustBal_Cursor; DEALLOCATE CustBal_Cursor; SET @PeriodID = @PeriodID + 1; END; END; --step6: append records which period is greate than 12 to records period is 12 --IF EXISTS (select 1 from CustBalance where PeriodID > 12) --BEGIN -- update dbo.CustBalance -- set DebitBal = DebitBal + sumcs.SumDebitBal, CreditBal = CreditBal + sumcs.SumCreditBal, -- YearDebitBal = YearDebitBal + sumcs.SumDebitBal,YearCreditBal = YearCreditBal + sumcs.SumCreditBal, -- EndBal = (EndBal + sumcs.SumDebitBal - sumcs.SumCreditBal) * a.Direction, -- EndDebitBal = case when a.Direction = 1 then (EndDebitBal + sumcs.SumDebitBal - sumcs.SumCreditBal) * a.Direction else 0 end, -- EndCreditBal = case when a.Direction = -1 then (EndCreditBal + sumcs.SumDebitBal - sumcs.SumCreditBal) * a.Direction else 0 end -- from dbo.CustBalance cs -- inner join -- ( -- select AcctCode,CustomerCode,Sum(DebitBal)SumDebitBal,Sum(CreditBal)SumCreditBal -- from CustBalance where PeriodID > 12 -- group by AcctCode,CustomerCode -- ) sumcs -- on cs.AcctCode = sumcs.AcctCode and cs.CustomerCode = sumcs.CustomerCode -- inner join dbo.EnterpriseAccount a on cs.AcctCode = a.Code -- where PeriodID = 12 --END; --delete dbo.CustBalance where PeriodID > 12 --Step 7: Insert Balance by CustBalance SELECT @PeriodID = MIN(PeriodId) FROM dbo.CustBalance; WHILE @PeriodID <= 12 BEGIN DECLARE CustBal_Cursor CURSOR FOR SELECT b.AcctCode , BegBal , EndBal , DebitBal , CreditBal , BegDebitBal , BegCreditBal , EndDebitBal , EndCreditBal, b.IsDummy FROM [dbo].[CustBalance] b INNER JOIN [dbo].[EnterpriseAccount] a ON b.AcctCode = a.AcctCode WHERE PeriodId = @PeriodID AND CustomerCode = ''; OPEN CustBal_Cursor; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @BegBal, @EndBal, @sumDebit, @sumCredit, @BegDebitBal, @BegCreditBal, @EndDebitBal, @EndCreditBal,@IsDummy; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @sumYearDebit = ISNULL(SUM(DebitBal),0), @sumYearCredit = ISNULL(SUM(CreditBal),0) FROM [dbo].[CustBalance] WHERE AcctCode = @AcctCode AND CustomerCode = '' AND PeriodId <= @PeriodID - 1; IF @sumDebit IS NOT NULL BEGIN INSERT [dbo].[Balance] ( PeriodId , AcctCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , BegDebitBal , BegCreditBal , EndDebitBal , EndCreditBal, IsDummy ) VALUES ( @PeriodID , @AcctCode , @BegBal , @sumDebit , @sumCredit , @sumYearDebit + @sumDebit , @sumYearCredit + @sumCredit , @EndBal , @BegDebitBal , @BegCreditBal , @EndDebitBal , @EndCreditBal, @IsDummy ); END; ELSE BEGIN INSERT [dbo].[Balance] ( PeriodId , AcctCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , BegDebitBal , BegCreditBal , EndDebitBal , EndCreditBal, IsDummy ) VALUES ( @PeriodID , @AcctCode , @BegBal , 0 , 0 , @sumYearDebit , @sumYearCredit , @EndBal , @BegDebitBal , @BegCreditBal , @EndDebitBal , @EndCreditBal, @IsDummy ); END; FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @BegBal, @EndBal, @sumDebit, @sumCredit, @BegDebitBal, @BegCreditBal, @EndDebitBal, @EndCreditBal,@IsDummy; END; CLOSE CustBal_Cursor; DEALLOCATE CustBal_Cursor; SET @PeriodID = @PeriodID + 1; END; END; GO /****** Object: StoredProcedure [dbo].[UpdateParentCalValues] Script Date: 14/06/2017 11:01:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Bin LI> -- Description: <CustBalance�и���Ŀ��dummy�Ļ�������BegBal���������ӿ�Ŀ�ۼӡ������μ���������ۼƷ�������ĩ������> -- Parmeters: 1. @CalPeriodId: ������ڼ� -- 2. @IsImportBegBal �Ƿ�ֻ����BegBal��1���ǣ�0������ -- ============================================= CREATE PROCEDURE [dbo].[UpdateParentCalValues] @CalPeriodId INT, @IsImportBegBal INT AS BEGIN SET NOCOUNT ON; DECLARE @AcctCode NVARCHAR(50); DECLARE @CustomerCode NVARCHAR(50); DECLARE @BegBal FLOAT; DECLARE @BegDebitBal FLOAT; DECLARE @BegCreditBal FLOAT; DECLARE @Direction INT; DECLARE @sumYearDebit FLOAT; DECLARE @sumYearCredit FLOAT; DECLARE @MaxAcctLevle INT; DECLARE @PeriodId INT; DECLARE @ChildTbValues TABLE ( AcctCode NVARCHAR(50), ParentCode NVARCHAR(50), CustomerCode NVARCHAR(50), AcctLevel INT, BegBal FLOAT, BegDebitBal FLOAT, BegCreditBal FLOAT, PeriodId INT ) --1. atemp: children accounts of dummy acount INSERT INTO @ChildTbValues ( AcctCode , ParentCode , CustomerCode , AcctLevel , BegBal , BegDebitBal , BegCreditBal, PeriodId ) SELECT atemp.AcctCode , atemp.ParentCode , atemp.CustomerCode , atemp.AcctLevel , b1.BegBal , b1.BegDebitBal , b1.BegCreditBal, b1.PeriodId FROM dbo.CustBalance b1 INNER JOIN ( SELECT a1.AcctCode , a1.ParentCode , b2.CustomerCode , a1.AcctLevel FROM dbo.EnterpriseAccount a1 INNER JOIN dbo.CustBalance b2 ON a1.ParentCode = b2.AcctCode WHERE b2.IsDummy = 1 ) atemp ON b1.AcctCode = atemp.AcctCode WHERE b1.PeriodId = @CalPeriodId; SELECT @MaxAcctLevle = MAX(AcctLevel) FROM @ChildTbValues; WHILE @MaxAcctLevle > 1 BEGIN DECLARE DummyParent_Cursor CURSOR FOR SELECT ParentCode , ISNULL(SUM(BegBal), 0) AS SumBegBal , ISNULL(SUM(BegDebitBal), 0) AS SumBegDebitBal , ISNULL(SUM(BegCreditBal), 0) AS SumBegCreditBal, CustomerCode, PeriodId FROM @ChildTbValues WHERE AcctLevel = @MaxAcctLevle GROUP BY ParentCode , CustomerCode, PeriodId; OPEN DummyParent_Cursor; FETCH NEXT FROM DummyParent_Cursor INTO @AcctCode, @BegBal, @BegDebitBal, @BegCreditBal, @CustomerCode,@PeriodId; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Direction = Direction FROM dbo.EnterpriseAccount WHERE AcctCode = @AcctCode; IF @IsImportBegBal = 0 BEGIN SET @BegBal = ( @BegDebitBal - @BegCreditBal ) * @Direction; END; SELECT @sumYearDebit = ISNULL(SUM(DebitBal), 0) , @sumYearCredit = ISNULL(SUM(CreditBal), 0) FROM [dbo].[CustBalance] WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId <= @CalPeriodId; UPDATE dbo.CustBalance SET BegBal = @BegBal , BegDebitBal = CASE WHEN @Direction = 1 AND @IsImportBegBal = 1 THEN @BegBal WHEN @IsImportBegBal = 0 THEN @BegDebitBal ELSE 0 END , BegCreditBal = CASE WHEN @Direction = -1 AND @IsImportBegBal = 1 THEN @BegBal WHEN @IsImportBegBal = 0 THEN @BegCreditBal ELSE 0 END , YearDebitBal = @sumYearDebit , YearCreditBal = @sumYearCredit , EndBal = ( @BegBal * @Direction + DebitBal - CreditBal ) * @Direction , EndDebitBal = CASE WHEN @Direction = 1 THEN ( @BegBal * @Direction + DebitBal - CreditBal ) * @Direction ELSE 0 END , EndCreditBal = CASE WHEN @Direction = -1 THEN ( @BegBal * @Direction + DebitBal - CreditBal ) * @Direction ELSE 0 END FROM dbo.CustBalance WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @CalPeriodId; UPDATE @ChildTbValues SET BegBal = @BegBal , BegDebitBal = CASE WHEN @Direction = 1 AND @IsImportBegBal = 1 THEN @BegBal WHEN @IsImportBegBal = 0 THEN @BegDebitBal ELSE 0 END , BegCreditBal = CASE WHEN @Direction = -1 AND @IsImportBegBal = 1 THEN @BegBal WHEN @IsImportBegBal = 0 THEN @BegCreditBal ELSE 0 END FROM @ChildTbValues WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @CalPeriodId; FETCH NEXT FROM DummyParent_Cursor INTO @AcctCode, @BegBal, @BegDebitBal, @BegCreditBal, @CustomerCode,@PeriodId; END; CLOSE DummyParent_Cursor; DEALLOCATE DummyParent_Cursor; SET @MaxAcctLevle = @MaxAcctLevle - 1; END; END; GO CREATE PROCEDURE [dbo].[UpdateNonLeafVocuhers] @PeriodIdParam INT, @UserId VARCHAR(128) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @NewAccountCode NVARCHAR(50); DECLARE @AcctCode NVARCHAR(50); DECLARE @AcctLevel INT; DECLARE @CustomerCode NVARCHAR(50); DECLARE @BegBal NUMERIC(18, 3); DECLARE @BegBalChildren NUMERIC(18, 3); DECLARE @Direction INT; DECLARE @Symbol NVARCHAR(50); DECLARE @AccountSetId VARCHAR(128); SET @Symbol = '@'; SELECT TOP(1) @AccountSetId = EnterpriseAccountSetID FROM dbo.EnterpriseAccount WHERE EnterpriseAccountSetID IS NOT NULL --Step 1: Update CustBalance table to add balance record for the new-added accounts DECLARE Account_Cursor CURSOR FOR SELECT DISTINCT v.AcctCode , a.AcctLevel FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode INNER JOIN dbo.CustBalance cb ON a.AcctCode = cb.AcctCode WHERE a.IsLeaf = 0 AND cb.PeriodId = @PeriodIdParam ORDER BY a.AcctLevel DESC , v.AcctCode; OPEN Account_Cursor; FETCH NEXT FROM Account_Cursor INTO @AcctCode, @AcctLevel; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE CustomerCode_Cursor CURSOR FOR SELECT CustomerCode , BegBal FROM dbo.CustBalance WHERE AcctCode = @AcctCode AND PeriodId = @PeriodIdParam; OPEN CustomerCode_Cursor; FETCH NEXT FROM CustomerCode_Cursor INTO @CustomerCode, @BegBal; WHILE @@FETCH_STATUS = 0 BEGIN SELECT TOP 1 @BegBalChildren = ISNULL(SUM(a.Direction * cb.BegBal), 0) FROM CustBalance cb INNER JOIN dbo.EnterpriseAccount a ON cb.AcctCode = a.AcctCode WHERE PeriodId = @PeriodIdParam AND a.ParentCode = @AcctCode AND CustomerCode = @CustomerCode; SELECT @Direction = a.Direction FROM dbo.CustBalance cb INNER JOIN dbo.EnterpriseAccount a ON cb.AcctCode = a.AcctCode WHERE PeriodId = @PeriodIdParam AND a.AcctCode = @AcctCode AND CustomerCode = @CustomerCode; IF NOT EXISTS ( SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @PeriodIdParam AND AcctCode = @AcctCode + @Symbol AND CustomerCode = @CustomerCode ) BEGIN INSERT dbo.CustBalance ( PeriodId , AcctCode , CustomerCode , BegBal ) VALUES ( @PeriodIdParam , @AcctCode + @Symbol , @CustomerCode , @BegBal - @Direction * @BegBalChildren ); END; FETCH NEXT FROM CustomerCode_Cursor INTO @CustomerCode, @BegBal; END; CLOSE CustomerCode_Cursor; DEALLOCATE CustomerCode_Cursor; FETCH NEXT FROM Account_Cursor INTO @AcctCode, @AcctLevel; END; CLOSE Account_Cursor; DEALLOCATE Account_Cursor; --Step 2: Update EnterpriseAccount/Voucher table with special symbol DECLARE NewAccount_Cursor CURSOR FOR SELECT DISTINCT v.AcctCode FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode WHERE a.IsLeaf = 0 AND v.Period = @PeriodIdParam; OPEN NewAccount_Cursor; FETCH NEXT FROM NewAccount_Cursor INTO @NewAccountCode; WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS ( SELECT 1 FROM dbo.EnterpriseAccount WHERE AcctCode = @NewAccountCode + @Symbol ) BEGIN UPDATE dbo.EnterpriseAccount SET StdCode = NULL WHERE AcctCode = @NewAccountCode --Step 2.1: Update EnterpriseAccount table to add new accounts with special symbol INSERT dbo.EnterpriseAccount ( AcctCode , Name , FullName , ParentCode , AcctProp , Direction , StdCode , IsLeaf , AcctLevel, IsActive, EnterpriseAccountSetID, CreatorID, UpdatorID, CreateTime, UpdateTime ) SELECT AcctCode + @Symbol , Name , FullName , AcctCode , AcctProp , Direction , NULL, -- set stdcode value of new enterprise as NULL 1 , AcctLevel + 1, 1, @AccountSetId, @UserId, @UserId, GETDATE(), GETDATE() FROM dbo.EnterpriseAccount WHERE AcctCode = @NewAccountCode; END; --Step 2.2: Update Voucher table to add special symbol on vouchers of non-leaf accounts UPDATE dbo.Voucher SET AcctCode = AcctCode + @Symbol WHERE AcctCode = @NewAccountCode; FETCH NEXT FROM NewAccount_Cursor INTO @NewAccountCode; END; CLOSE NewAccount_Cursor; DEALLOCATE NewAccount_Cursor; END; GO CREATE PROCEDURE [dbo].[InitVoucherMain] @creatorId VARCHAR(128) AS BEGIN truncate table VoucherMain insert into VoucherMain (Period, [Group], VID, VoucherCount, AcctCodeCount, Amount, AmountCredit,CreateTime,UpdateTime,CreatorID) select [Period], [Group], [VID], count(1) as vc, count(distinct acctcode) as ac, sum(Debit) Amount, sum(Credit) AmountCredit,GETDATE(),GETDATE(),@creatorId from [Voucher] group by [Period], [Group], [VID] END GO CREATE PROCEDURE [dbo].[InitAccountIsInVoucher] AS BEGIN SET NOCOUNT ON; --Set IsInVoucher = 1 if account code is in C_Voucher,otherwise set it as 0 --If C_Accout has any record which IsInVoucher is null, means the project is old version before admin module v2.1 update dbo.EnterpriseAccount set IsInVoucher = 0 update dbo.EnterpriseAccount set IsInVoucher = 1 where AcctCode in ( select distinct v.AcctCode from dbo.Voucher v where v.AcctCode is not null ) END GO /* ******************************************************************************************************************************* */ /* CREATE NEW TABLE CALLED "Account" */ IF NOT EXISTS (select 1 from sys.tables where name = 'Account' and type = 'U') BEGIN CREATE TABLE [dbo].[Account]( [ID] [varchar](128) NOT NULL, [Code] [nvarchar](50) NOT NULL, [Name] [nvarchar](50) NOT NULL, [ParentCode] [nvarchar](50) NULL, [FullName] [nvarchar](200) NULL, [AcctProp] [int] NULL, [SubProp] [int] NULL, [AcctLevel] [int] NULL, [Direction] [int] NOT NULL, [IsLeaf] [smallint] NOT NULL DEFAULT ((1)), [RuleType] [int] NOT NULL, [IsActive] [smallint] NOT NULL DEFAULT ((1)), [EnglishName] [nvarchar](200) NULL, [IndustryID] [varchar](128) NOT NULL PRIMARY KEY (ID) ); END GO ---begin Rita 2017-05-05 MSSql V1.0.0.0 Model-------------------------------------- IF NOT EXISTS (select 1 from sys.tables where name = 'EntriesCheckResult') BEGIN CREATE TABLE [dbo].EntriesCheckResult( [ID] [varchar](128) NOT NULL, [ModelID] [varchar](128) NOT NULL, [Code] [varchar](20) NOT NULL, [Name] [nvarchar](50) NOT NULL, [Description] [nvarchar](300) NULL, [VoucherNum] [int] NOT NULL default 0, [EntriesNum] [int] NOT NULL default 0, [DebitRelevantAmt] [numeric](18,3) NOT NULL default 0, [CreditRelevantAmt] [numeric](18,3) NOT NULL default 0, [DebitTotalAmt] [numeric](18,3) NOT NULL default 0, [CreditTotalAmt] [numeric](18,3) NOT NULL default 0, [BigVoucherNum] [int] NOT NULL default 0, [BigEntriesNum] [int] NOT NULL default 0, [BigDebitRelevantAmt] [numeric](18,3) NOT NULL default 0, [BigCreditRelevantAmt] [numeric](18,3) NOT NULL default 0, [BigDebitTotalAmt] [numeric](18,3) NOT NULL default 0, [BigCreditTotalAmt] [numeric](18,3) NOT NULL default 0, [EntryDoubts] int NOT NULL default 0, [BigEntryDoubts] int NOT NULL default 0, [VoucherDoubts] int NOT NULL default 0, [BigVoucherDoubts] int NOT NULL default 0, [IsFiltered] [int] NOT NULL default 0, [ServiceTypeID] [varchar](128) NOT NULL, [CategoryID] [varchar](128) NOT NULL, [Type] [int] NOT NULL, [IsFilter] [int] NOT NULL default 0, [IsShow] [int] NOT NULL default 1, [Period] [int] NOT NULL, [CreateTime] [datetime] NOT NULL default getDate(), [UpdateTime] [datetime] NOT NULL default getDate(), PRIMARY KEY(ID) ); END GO IF NOT EXISTS (select 1 from sys.tables where name = 'EntriesCheckDetailResult') BEGIN CREATE TABLE [dbo].EntriesCheckDetailResult( [ID] [varchar](128) NOT NULL, [ResultID] [varchar](128) NOT NULL, [IsDoubt] [int] NOT NULL default 0, [IsBigVoucher] [int] NOT NULL default 0, [VoucherID] [varchar](128) NOT NULL, [VID] [nvarchar](256) NOT NULL, [Date] [date] NOT NULL, [Group] [nvarchar](50) NULL, [Period] [int] NULL, [CustomerCode] [nvarchar](50) NULL, [CustomerName] [nvarchar](100) NULL, [Summary] [nvarchar](500) NULL, [AcctCode] [nvarchar](50) NULL, [StdCode] [nvarchar](50) NULL, [Debit] [numeric](18,3) NULL, [Credit] [numeric](18,3) NULL, [Attach] [bigint] NULL, [ItemID] [nvarchar](256) NULL, [ImportType] [int] NULL, [IsFiltered] [int] NOT NULL default 0, [IsRelevantAmt] [int] NOT NULL default 0, [CreateTime] [datetime] NOT NULL default getDate(), [UpdateTime] [datetime] NOT NULL default getDate(), PRIMARY KEY(ID) ); END GO IF NOT EXISTS (select 1 from sys.tables where name = 'IndexAnalysisResult') BEGIN CREATE TABLE [dbo].IndexAnalysisResult( [ID] [varchar](128) NOT NULL, [ModelID] [varchar](128) NOT NULL, [Code] [varchar](20) NOT NULL, [Name] [nvarchar](50) NOT NULL, [Description] [nvarchar](300) NULL, [MonthExplain] [nvarchar](200) NULL, [YearExplain] [nvarchar](200) NULL, [ServiceTypeID] [varchar](128) NOT NULL, [CategoryID] [varchar](128) NOT NULL, [Type] [int] NOT NULL, [IndexCodeTotal] [varchar](8000) NULL, [IsShow] [int] NOT NULL default 1, [IsReport] [int] NOT NULL default 0, [CreateTime] [datetime] NOT NULL default getDate(), [UpdateTime] [datetime] NOT NULL default getDate(), PRIMARY KEY(ID) ); END GO IF NOT EXISTS (select 1 from sys.tables where name = 'IndexAnalysisDetailResult') BEGIN CREATE TABLE [dbo].IndexAnalysisDetailResult( [ID] [varchar](128) NOT NULL, [ResultID] [varchar](128) NOT NULL, [Year] [int] NOT NULL, [Period] [int] NOT NULL, [IndexName] [nvarchar](50) NULL, [Sequence] [int] NULL, [DisplayType] [int] NULL, [IsVisible] [int] NOT NULL default 1, [IsMainValue] [int] NOT NULL default 0, [IsYearValue] [int] NOT NULL default 1, [IsMonExp] [int] NOT NULL default 0, [IsYearExp] [int] NOT NULL default 0, [IndexResult] [varchar](50) NULL, [IndexCode] [varchar](8000) NULL, [CreateTime] [datetime] NOT NULL default getDate(), [UpdateTime] [datetime] NOT NULL default getDate(), PRIMARY KEY(ID) ); END GO IF NOT EXISTS (select 1 from sys.tables where name = 'ModelLog') BEGIN CREATE TABLE [dbo].ModelLog( [ID] [varchar](128) NOT NULL, [OrganizationID] [varchar](128) NOT NULL, [RunNumber] [int] NOT NULL, [Description] [nvarchar](500) NULL, [SuccessNumber] [int] NOT NULL, [FailureNumber] [int] NOT NULL, [ServiceTypeID] [varchar](128) NOT NULL, [CreateTime] [datetime] NOT NULL default getDate(), [UpdateTime] [datetime] NOT NULL default getDate(), PRIMARY KEY(ID) ); END GO IF NOT EXISTS (select 1 from sys.tables where name = 'ModelDetailLog') BEGIN CREATE TABLE [dbo].ModelDetailLog( [ID] [varchar](128) NOT NULL, [ModelLogID] [varchar](128) NOT NULL, [Feature] [int] not null, [ModelID] [varchar](128) NOT NULL, [Code] [varchar](20) NOT NULL, [Name] [nvarchar](50) NOT NULL, [Description] [nvarchar](500) NULL, [RunningResult] [int] NOT NULL, [Period] [int] NOT NULL, [CreateTime] [datetime] NOT NULL default getDate(), [UpdateTime] [datetime] NOT NULL default getDate(), PRIMARY KEY(ID) ); END GO IF NOT EXISTS (select 1 from sys.tables where name = 'ModelCategoryResult') BEGIN CREATE TABLE [dbo].ModelCategoryResult( [ID] [varchar](128) NOT NULL, [CategoryID] [varchar](128) NOT NULL, [ClientCode] [nvarchar](50) NOT NULL, [OrganizationID] [varchar](128) NOT NULL, [Category] [nvarchar](10) NOT NULL, [ParentID] [varchar](128) NOT NULL, [Sort] [int] NOT NULL default 0, [Quantity] [int] NOT NULL default 0, [CreateTime] [datetime] NOT NULL default getDate(), [UpdateTime] [datetime] NOT NULL default getDate(), [IndustryID] [varchar](128) NULL, [ServiceTypeID] [varchar](128) NOT NULL, PRIMARY KEY( ID ) ); END GO ---end Rita 2017-05-05 MSSql V1.0.0.0---------------------------------------------- GO CREATE PROCEDURE [dbo].[InitAccount] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @Code NVARCHAR(50); DECLARE @Code2 NVARCHAR(50); DECLARE @Parent NVARCHAR(50); DECLARE @Name NVARCHAR(50); DECLARE @AccountID VARCHAR(128); UPDATE dbo.EnterpriseAccount SET ParentCode = NULL , FullName = NULL , AcctLevel = NULL , IsLeaf = NULL -- Insert statements for procedure here DECLARE Code_Cursor CURSOR FOR SELECT AcctCode FROM [dbo].[EnterpriseAccount] ORDER BY AcctCode; OPEN Code_Cursor; FETCH NEXT FROM Code_Cursor INTO @Code; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE [dbo].[EnterpriseAccount] SET ParentCode = @Code WHERE AcctCode LIKE @Code + '%' AND AcctCode <> @Code; FETCH NEXT FROM Code_Cursor INTO @Code; END; CLOSE Code_Cursor; DEALLOCATE Code_Cursor; DECLARE @count1 INT; DECLARE @count2 INT; DECLARE @num INT; SET @num = 1; --update [dbo].[C_Account] set Parent = '';--ǧ���ƻ���ʹ��ϵͳĬ�Ϲ�������Ŀ���¼���ϵ����Ҫ�Ȱ�����Parent����Է��û������ļ�����Parent UPDATE [dbo].[EnterpriseAccount] SET ParentCode = NULL WHERE ParentCode = ''; UPDATE [dbo].[EnterpriseAccount] SET AcctLevel = 1 WHERE ParentCode IS NULL; SELECT @count1 = COUNT(1) FROM [dbo].[EnterpriseAccount] WHERE AcctLevel IS NULL; WHILE @count1 <> 0 BEGIN DECLARE Code1_Cursor CURSOR FOR SELECT AcctCode FROM [dbo].[EnterpriseAccount] WHERE AcctLevel = @num; OPEN Code1_Cursor; FETCH NEXT FROM Code1_Cursor INTO @Code; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE [dbo].[EnterpriseAccount] SET AcctLevel = @num + 1 WHERE ParentCode = @Code; SELECT @count2 = COUNT(1) FROM [dbo].[EnterpriseAccount] WHERE AcctCode LIKE @Code + '%' AND AcctCode <> @Code; IF @count2 = 0 BEGIN -- is leaf UPDATE [dbo].[EnterpriseAccount] SET IsLeaf = 1 WHERE AcctCode = @Code; END; ELSE BEGIN -- is not leaf UPDATE [dbo].[EnterpriseAccount] SET IsLeaf = 0 WHERE AcctCode = @Code; END; FETCH NEXT FROM Code1_Cursor INTO @Code; END; CLOSE Code1_Cursor; DEALLOCATE Code1_Cursor; SET @num = @num + 1; SELECT @count1 = COUNT(1) FROM [dbo].[EnterpriseAccount] WHERE AcctLevel IS NULL; END; UPDATE [dbo].[EnterpriseAccount] SET IsLeaf = 1 WHERE IsLeaf IS NULL; DECLARE FullName_Cursor CURSOR FOR SELECT AcctCode , Name , ParentCode , ID FROM [dbo].[EnterpriseAccount] ORDER BY AcctLevel,AcctCode; OPEN FullName_Cursor; FETCH NEXT FROM FullName_Cursor INTO @Code, @Name, @Parent, @AccountID; WHILE @@FETCH_STATUS = 0 BEGIN IF @Parent IS NULL BEGIN UPDATE [dbo].[EnterpriseAccount] SET FullName = @Name WHERE AcctCode = @Code AND ID = @AccountID; END; ELSE BEGIN UPDATE [dbo].[EnterpriseAccount] SET FullName = ( SELECT MAX(FullName) FROM [dbo].[EnterpriseAccount] WHERE AcctCode = @Parent ) + '-' + @Name WHERE AcctCode = @Code; END; FETCH NEXT FROM FullName_Cursor INTO @Code, @Name, @Parent, @AccountID; END; CLOSE FullName_Cursor; DEALLOCATE FullName_Cursor; END; GO CREATE PROCEDURE [dbo].[InitCustBalanceStd] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @PeriodID INT; DECLARE @AcctCode NVARCHAR(50); DECLARE @CustomerCode NVARCHAR(50); DECLARE @BegBal FLOAT; DECLARE @sumDebit FLOAT; DECLARE @sumCredit FLOAT; DECLARE @sumYearDebit FLOAT; DECLARE @sumYearCredit FLOAT; DECLARE @EndBal FLOAT; DECLARE @CurrAcctLevel INT; DECLARE @AcctDirectionDiff INT; DECLARE @StdDirection INT; --Step 1: clean up C_CustBalanceStd TRUNCATE TABLE dbo.CustBalanceStd; --Step 2: calculate the trial balance for leaf node DECLARE LeafNode_Cursor CURSOR FOR SELECT cb.PeriodId , a.StdCode , cb.CustomerCode , SUM(BegBal * sa.Direction * a.Direction) BegBal , SUM(DebitBal) DebitBal , SUM(CreditBal) CreditBal , SUM(YearDebitBal) YearDebitBal , SUM(YearCreditBal) YearCreditBal , SUM(EndBal * sa.Direction * a.Direction) EndBal FROM dbo.CustBalance cb INNER JOIN dbo.EnterpriseAccount a ON cb.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code WHERE a.StdCode IS NOT NULL AND a.IsLeaf = 1 GROUP BY cb.PeriodId , a.StdCode , cb.CustomerCode ORDER BY cb.PeriodId , a.StdCode , cb.CustomerCode; OPEN LeafNode_Cursor; FETCH NEXT FROM LeafNode_Cursor INTO @PeriodID, @AcctCode, @CustomerCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal; WHILE @@FETCH_STATUS = 0 BEGIN INSERT CustBalanceStd ( PeriodID , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal ) VALUES ( @PeriodID , @AcctCode , @CustomerCode , @BegBal , @sumDebit , @sumCredit , @sumYearDebit , @sumYearCredit , @EndBal ); FETCH NEXT FROM LeafNode_Cursor INTO @PeriodID, @AcctCode, @CustomerCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal; END; CLOSE LeafNode_Cursor; DEALLOCATE LeafNode_Cursor; --Step 3: get the maxium level of the standard account based trial balance SELECT @CurrAcctLevel = MAX(AcctLevel) FROM dbo.CustBalanceStd cbs INNER JOIN dbo.StandardAccount sa ON cbs.AcctCode = sa.Code; --Step 4: calculate trial balance for all the parent standard account based on the value of leaf node WHILE @CurrAcctLevel > 1 BEGIN DECLARE ParentNode_Cursor CURSOR FOR SELECT PeriodID , sa.ParentCode , CustomerCode , SUM(sa.Direction * BegBal) BegBal , SUM(DebitBal) DebitBal , SUM(CreditBal) CreditBal , SUM(YearDebitBal) YearDebitBal , SUM(YearCreditBal) YearCreditBal , SUM(sa.Direction * EndBal) EndBal FROM dbo.CustBalanceStd cbs INNER JOIN dbo.StandardAccount sa ON cbs.AcctCode = sa.Code WHERE sa.ParentCode IS NOT NULL AND sa.AcctLevel = @CurrAcctLevel GROUP BY PeriodID , sa.ParentCode , CustomerCode ORDER BY PeriodID , sa.ParentCode , CustomerCode; OPEN ParentNode_Cursor; FETCH NEXT FROM ParentNode_Cursor INTO @PeriodID, @AcctCode, @CustomerCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal; WHILE @@FETCH_STATUS = 0 BEGIN IF @CustomerCode = '' BEGIN SELECT @StdDirection = Direction FROM dbo.StandardAccount WHERE Code = @AcctCode; INSERT dbo.CustBalanceStd ( PeriodID , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal ) VALUES ( @PeriodID , @AcctCode , @CustomerCode , @StdDirection * @BegBal , @sumDebit , @sumCredit , @sumYearDebit , @sumYearCredit , @StdDirection * @EndBal ); END; FETCH NEXT FROM ParentNode_Cursor INTO @PeriodID, @AcctCode, @CustomerCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal; END; CLOSE ParentNode_Cursor; DEALLOCATE ParentNode_Cursor; SET @CurrAcctLevel = @CurrAcctLevel - 1; END; --step 5: update BegDebitBal/BegCreditBal; EndDebitBal/EndCreditBal UPDATE cbt SET cbt.BegDebitBal = CASE WHEN sta.Direction = 1 THEN cbt.BegBal ELSE 0 END, cbt.BegCreditBal = CASE WHEN sta.Direction = -1 THEN cbt.BegBal ELSE 0 END, cbt.EndDebitBal = CASE WHEN sta.Direction = 1 THEN cbt.EndBal ELSE 0 END, cbt.EndCreditBal = CASE WHEN sta.Direction = -1 THEN cbt.EndBal ELSE 0 END FROM dbo.CustBalanceStd cbt INNER JOIN dbo.StandardAccount sta ON cbt.AcctCode = sta.Code END; GO CREATE PROCEDURE [dbo].[InitCarryover] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @MaxAcctProp INT; DECLARE @GainLossAcctProp INT; --AcctProp of Gain & Loss accounts DECLARE @ProfitStdCode NVARCHAR(50); --Standard account code of Current Year Profit DECLARE @ProfitAcctCode NVARCHAR(50); --Account code of Current Year Profit DECLARE @Period INT; DECLARE @Group NVARCHAR(50); --Voucher type DECLARE @VID NVARCHAR(256); --Voucher number DECLARE @PeriodDetails INT; DECLARE @AcctCode NVARCHAR(50); DECLARE @CarryoverDebit NUMERIC(18, 3); DECLARE @CarryoverCredit NUMERIC(18, 3); DECLARE @YearCarryoverDebit NUMERIC(18, 3); DECLARE @YearCarryoverCredit NUMERIC(18, 3); DECLARE @CurrAcctLevel INT; --Step 1: Reset Carryover column of C_Balance table UPDATE dbo.Balance SET CarryoverDebit = 0 , CarryoverCredit = 0 , YearCarryoverDebit = 0 , YearCarryoverCredit = 0; --Step 2: Get the accounting rule (old 2001/new 2007) in current project SELECT @MaxAcctProp = MAX(AcctProp) FROM dbo.StandardAccount; IF @MaxAcctProp = 5 BEGIN --old rule 2001 SET @GainLossAcctProp = 5; SET @ProfitStdCode = '3131'; END; ELSE BEGIN --new rule 2007 SET @GainLossAcctProp = 6; SET @ProfitStdCode = '4103'; END; --Step 3: Get all the accounts per standard account code of current year profits SELECT @ProfitAcctCode = AcctCode FROM dbo.EnterpriseAccount WHERE StdCode = @ProfitStdCode; DECLARE ProfitAcct_Cursor CURSOR FOR SELECT AcctCode FROM dbo.EnterpriseAccount WHERE StdCode = @ProfitStdCode; OPEN ProfitAcct_Cursor; FETCH NEXT FROM ProfitAcct_Cursor INTO @ProfitAcctCode; WHILE @@FETCH_STATUS = 0 BEGIN --Step 3.1: Get Vouchers by AcctCode DECLARE Voucher_Cursor CURSOR FOR SELECT DISTINCT Period , [Group] , VID FROM dbo.Voucher WHERE AcctCode = @ProfitAcctCode ORDER BY Period , [Group] , VID; OPEN Voucher_Cursor; FETCH NEXT FROM Voucher_Cursor INTO @Period, @Group, @VID; WHILE @@FETCH_STATUS = 0 BEGIN --Step 3.1.1: Get Voucher Details by Voucher Period, Group, VID --Maybe gaint voucher IF EXISTS ( SELECT 1 FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode, a.StdCode) = sa.Code WHERE v.Period = @Period AND v.[Group] = @Group AND v.VID = @VID AND sa.Code <> @ProfitStdCode AND sa.AcctProp <> @GainLossAcctProp ) BEGIN DECLARE VoucherDetails_Cursor CURSOR FOR SELECT v.Period , v.AcctCode , SUM(Debit) CarryoverDebit , SUM(Credit) CarryoverCredit FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode, a.StdCode) = sa.Code WHERE v.Period = @Period AND v.[Group] = @Group AND v.VID = @VID AND sa.AcctProp = @GainLossAcctProp AND ( CASE a.Direction WHEN 1 THEN Debit ELSE Credit END ) = 0 GROUP BY v.Period , v.AcctCode , a.Direction; OPEN VoucherDetails_Cursor; FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails, @AcctCode, @CarryoverDebit, @CarryoverCredit; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.Balance SET CarryoverDebit = CarryoverDebit + @CarryoverDebit , CarryoverCredit = CarryoverCredit + @CarryoverCredit , YearCarryoverDebit = YearCarryoverDebit + @CarryoverDebit , YearCarryoverCredit = YearCarryoverCredit + @CarryoverDebit WHERE PeriodID = @PeriodDetails AND AcctCode = @AcctCode; FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails, @AcctCode, @CarryoverDebit, @CarryoverCredit; END; CLOSE VoucherDetails_Cursor; DEALLOCATE VoucherDetails_Cursor; END; --Maybe normal voucher ELSE BEGIN DECLARE VoucherDetails_Cursor CURSOR FOR SELECT v.Period , v.AcctCode , CASE a.Direction WHEN 1 THEN 0 ELSE SUM(Debit) - SUM(Credit) END CarryoverDebit , CASE a.Direction WHEN 1 THEN SUM(Credit) - SUM(Debit) ELSE 0 END CarryoverCredit FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode,a.StdCode) = sa.Code WHERE v.Period = @Period AND v.[Group] = @Group AND v.VID = @VID AND sa.AcctProp = @GainLossAcctProp GROUP BY v.Period , v.AcctCode , a.Direction; OPEN VoucherDetails_Cursor; FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails, @AcctCode, @CarryoverDebit, @CarryoverCredit; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.Balance SET CarryoverDebit = CarryoverDebit + @CarryoverDebit , CarryoverCredit = CarryoverCredit + @CarryoverCredit , YearCarryoverDebit = YearCarryoverDebit + @CarryoverDebit , YearCarryoverCredit = YearCarryoverCredit + @CarryoverDebit WHERE PeriodID = @PeriodDetails AND AcctCode = @AcctCode; FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails, @AcctCode, @CarryoverDebit, @CarryoverCredit; END; CLOSE VoucherDetails_Cursor; DEALLOCATE VoucherDetails_Cursor; END; FETCH NEXT FROM Voucher_Cursor INTO @Period, @Group, @VID; END; CLOSE Voucher_Cursor; DEALLOCATE Voucher_Cursor; FETCH NEXT FROM ProfitAcct_Cursor INTO @ProfitAcctCode; END; CLOSE ProfitAcct_Cursor; DEALLOCATE ProfitAcct_Cursor; --Step 4: Calculate the accumulated amount of current year profits DECLARE AccumulatedProfit_Cursor CURSOR FOR SELECT PeriodID , b.AcctCode FROM dbo.Balance b INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code WHERE sa.AcctProp = @GainLossAcctProp; OPEN AccumulatedProfit_Cursor; FETCH NEXT FROM AccumulatedProfit_Cursor INTO @Period, @AcctCode; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @YearCarryoverDebit = SUM(CarryoverDebit) , @YearCarryoverCredit = SUM(CarryoverCredit) FROM dbo.Balance WHERE PeriodID <= @Period AND AcctCode = @AcctCode; UPDATE dbo.Balance SET YearCarryoverDebit = @YearCarryoverDebit , YearCarryoverCredit = @YearCarryoverCredit WHERE PeriodID = @Period AND AcctCode = @AcctCode; FETCH NEXT FROM AccumulatedProfit_Cursor INTO @Period, @AcctCode; END; CLOSE AccumulatedProfit_Cursor; DEALLOCATE AccumulatedProfit_Cursor; --Step 5: Calculate the amount of all parent accounts SELECT @CurrAcctLevel = MAX(AcctLevel) FROM dbo.EnterpriseAccount; WHILE @CurrAcctLevel > 1 BEGIN DECLARE ParentNode_Cursor CURSOR FOR SELECT PeriodID , a.ParentCode , SUM(CarryoverDebit) CarryoverDebit , SUM(CarryoverCredit) CarryoverCredit , SUM(YearCarryoverDebit) YearCarryoverDebit , SUM(YearCarryoverCredit) YearCarryoverCredit FROM dbo.Balance b INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode WHERE a.ParentCode IS NOT NULL AND a.AcctLevel = @CurrAcctLevel GROUP BY PeriodID , a.ParentCode ORDER BY PeriodID , a.ParentCode; OPEN ParentNode_Cursor; FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode, @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit, @YearCarryoverCredit; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.Balance SET CarryoverDebit = CarryoverDebit + @CarryoverDebit , CarryoverCredit = CarryoverCredit + @CarryoverCredit , YearCarryoverDebit = YearCarryoverDebit + @YearCarryoverDebit , YearCarryoverCredit = YearCarryoverCredit + @YearCarryoverCredit WHERE PeriodID = @Period AND AcctCode = @AcctCode; FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode, @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit, @YearCarryoverCredit; END; CLOSE ParentNode_Cursor; DEALLOCATE ParentNode_Cursor; SET @CurrAcctLevel = @CurrAcctLevel - 1; END; --Step 6: Calculate the net values of Debit/Credit/Accumulated Debit/Accumulated Credit UPDATE dbo.Balance SET DebitNet = a.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) , CreditNet = 0 , YearDebitNet = a.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) , YearCreditNet = 0 FROM dbo.Balance b INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode WHERE a.Direction = 1; UPDATE dbo.Balance SET DebitNet = 0 , CreditNet = a.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) , YearDebitNet = 0 , YearCreditNet = a.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) FROM dbo.Balance b INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode WHERE a.Direction = -1; END; GO CREATE PROCEDURE [dbo].[InitBalanceStd] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @PeriodID INT; DECLARE @AcctCode NVARCHAR(50); DECLARE @BegBal FLOAT; DECLARE @sumDebit FLOAT; DECLARE @sumCredit FLOAT; DECLARE @sumYearDebit FLOAT; DECLARE @sumYearCredit FLOAT; DECLARE @EndBal FLOAT; DECLARE @CarryoverDebit NUMERIC(18, 3); DECLARE @CarryoverCredit NUMERIC(18, 3); DECLARE @YearCarryoverDebit NUMERIC(18, 3); DECLARE @YearCarryoverCredit NUMERIC(18, 3); DECLARE @CurrAcctLevel INT; DECLARE @AcctDirectionDiff INT; DECLARE @StdDirection INT; DECLARE @StdCode NVARCHAR(50); DECLARE @SubProp INT; DECLARE @Count INT; --Step 1: clean up C_BalanceStd TRUNCATE TABLE dbo.BalanceStd; --Step 2: calculate the trial balance for leaf node DECLARE LeafNode_Cursor CURSOR FOR SELECT b.PeriodId , a.StdCode , SUM(BegBal * sa.Direction * a.Direction) BegBal , SUM(DebitBal) DebitBal , SUM(CreditBal) CreditBal , SUM(YearDebitBal) YearDebitBal , SUM(YearCreditBal) YearCreditBal , SUM(EndBal * sa.Direction * a.Direction) EndBal , SUM(CarryoverDebit) CarryoverDebit , SUM(CarryoverCredit) CarryoverCredit , SUM(YearCarryoverDebit) YearCarryoverDebit , SUM(YearCarryoverCredit) YearCarryoverCredit FROM dbo.Balance b INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code WHERE a.StdCode IS NOT NULL AND a.IsLeaf = 1 GROUP BY b.PeriodId , a.StdCode ORDER BY b.PeriodId , a.StdCode; OPEN LeafNode_Cursor; FETCH NEXT FROM LeafNode_Cursor INTO @PeriodID, @AcctCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal, @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit, @YearCarryoverCredit; WHILE @@FETCH_STATUS = 0 BEGIN INSERT dbo.BalanceStd ( PeriodId , AcctCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , CarryoverDebit , CarryoverCredit , YearCarryoverDebit , YearCarryoverCredit ) VALUES ( @PeriodID , @AcctCode , @BegBal , @sumDebit , @sumCredit , @sumYearDebit , @sumYearCredit , @EndBal , @CarryoverDebit , @CarryoverCredit , @YearCarryoverDebit , @YearCarryoverCredit ); FETCH NEXT FROM LeafNode_Cursor INTO @PeriodID, @AcctCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal, @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit, @YearCarryoverCredit; END; CLOSE LeafNode_Cursor; DEALLOCATE LeafNode_Cursor; --Step 3: get the maxium level of the standard account based trial balance SELECT @CurrAcctLevel = MAX(AcctLevel) FROM dbo.BalanceStd bs INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code; --Step 4: calculate trial balance for all the parent standard account based on the value of leaf node WHILE @CurrAcctLevel > 1 BEGIN DECLARE ParentNode_Cursor CURSOR FOR SELECT PeriodId , sa.ParentCode , SUM(sa.Direction * BegBal) BegBal , SUM(DebitBal) DebitBal , SUM(CreditBal) CreditBal , SUM(YearDebitBal) YearDebitBal , SUM(YearCreditBal) YearCreditBal , SUM(sa.Direction * EndBal) EndBal , SUM(CarryoverDebit) CarryoverDebit , SUM(CarryoverCredit) CarryoverCredit , SUM(YearCarryoverDebit) YearCarryoverDebit , SUM(YearCarryoverCredit) YearCarryoverCredit FROM dbo.BalanceStd bs INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code WHERE sa.ParentCode IS NOT NULL AND sa.AcctLevel = @CurrAcctLevel GROUP BY PeriodId , sa.ParentCode ORDER BY PeriodId , sa.ParentCode; OPEN ParentNode_Cursor; FETCH NEXT FROM ParentNode_Cursor INTO @PeriodID, @AcctCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal, @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit, @YearCarryoverCredit; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @StdDirection = Direction FROM dbo.StandardAccount WHERE Code = @AcctCode; INSERT dbo.BalanceStd ( PeriodId , AcctCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , CarryoverDebit , CarryoverCredit , YearCarryoverDebit , YearCarryoverCredit ) VALUES ( @PeriodID , @AcctCode , @StdDirection * @BegBal , @sumDebit , @sumCredit , @sumYearDebit , @sumYearCredit , @StdDirection * @EndBal , @CarryoverDebit , @CarryoverCredit , @YearCarryoverDebit , @YearCarryoverCredit ); FETCH NEXT FROM ParentNode_Cursor INTO @PeriodID, @AcctCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal, @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit, @YearCarryoverCredit; END; CLOSE ParentNode_Cursor; DEALLOCATE ParentNode_Cursor; SET @CurrAcctLevel = @CurrAcctLevel - 1; END; --Step 5: update SubProp column of C_BalanceStd BEGIN UPDATE dbo.BalanceStd SET SubProp = bssa.SubProp FROM ( SELECT bs.PeriodId , bs.AcctCode , sa.SubProp FROM dbo.BalanceStd bs INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code ) bssa WHERE dbo.BalanceStd.PeriodId = bssa.PeriodId AND dbo.BalanceStd.AcctCode = bssa.AcctCode; END; --Step 6: update SubProp column of C_Balance BEGIN UPDATE dbo.Balance SET SubProp = bsa.SubProp FROM ( SELECT b.PeriodId , b.AcctCode , sa.SubProp FROM dbo.Balance b INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode LEFT JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code ) bsa WHERE dbo.Balance.PeriodId = bsa.PeriodId AND dbo.Balance.AcctCode = bsa.AcctCode; DECLARE Balance_Cursor CURSOR FOR SELECT b.PeriodId , b.AcctCode FROM dbo.Balance b INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode LEFT JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code WHERE a.StdCode = '0000'; OPEN Balance_Cursor; FETCH NEXT FROM Balance_Cursor INTO @PeriodID, @AcctCode; WHILE @@FETCH_STATUS = 0 BEGIN --select @Count = count(1) from C_Account where Code like @AcctCode + '%' and IsLeaf = 1 and StdCode is not null --IF @Count > 0 BEGIN SELECT TOP 1 @StdCode = StdCode FROM dbo.EnterpriseAccount WHERE AcctCode LIKE @AcctCode + '%' AND IsLeaf = 1 AND StdCode IS NOT NULL; SELECT @SubProp = SubProp FROM dbo.StandardAccount WHERE Code = @StdCode; UPDATE dbo.Balance SET SubProp = @SubProp WHERE PeriodID = @PeriodID AND AcctCode = @AcctCode; END; FETCH NEXT FROM Balance_Cursor INTO @PeriodID, @AcctCode; END; CLOSE Balance_Cursor; DEALLOCATE Balance_Cursor; END; --Step 7: Calculate the net values of Debit/Credit/Accumulated Debit/Accumulated Credit UPDATE dbo.BalanceStd SET DebitNet = sa.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) , CreditNet = 0 , YearDebitNet = sa.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) , YearCreditNet = 0 FROM dbo.BalanceStd bs INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code WHERE sa.Direction = 1; UPDATE dbo.BalanceStd SET DebitNet = 0 , CreditNet = sa.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) , YearDebitNet = 0 , YearCreditNet = sa.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) FROM dbo.BalanceStd bs INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code WHERE sa.Direction = -1; --Step 8: update BegDebitBal/BegCreditBal; EndDebitBal/EndCreditBal UPDATE bs SET bs.BegDebitBal = CASE WHEN sta.Direction = 1 THEN bs.BegBal ELSE 0 END, bs.BegCreditBal = CASE WHEN sta.Direction = -1 THEN bs.BegBal ELSE 0 END, bs.EndDebitBal = CASE WHEN sta.Direction = 1 THEN bs.EndBal ELSE 0 END, bs.EndCreditBal = CASE WHEN sta.Direction = -1 THEN bs.EndBal ELSE 0 END FROM dbo.BalanceStd bs INNER JOIN dbo.StandardAccount sta ON bs.AcctCode = sta.Code END; GO /******============================================= Description: �����ض�Ӧ�����������ı���ĿTB Parameters: @VoucherRemapId = 1, @CustRemapId = -1: ֻ��ȡƾ֤�ض�Ӧ������ @VoucherRemapId = -1, @CustRemapId = 2��ֻ��ȡ�ֶ��ض�Ӧ������ @VoucherRemapId = 1, @CustRemapId = 2�� ��ȡƾ֤�ض�Ӧ���ֶ��ض�Ӧ���� @VoucherRemapId = -1, @CustRemapId = -1��������ȡƾ֤�ض�Ӧ��Ҳ����ȡ�ֶ��ض�Ӧ���� =============================================******/ CREATE PROCEDURE [dbo].[InitCustBalanceStdRemap] @VoucherRemapId INT, @CustRemapId INT AS BEGIN SET NOCOUNT ON; DECLARE @Period INT; DECLARE @Periods INT; DECLARE @AcctCode NVARCHAR(50); DECLARE @CustomerCode NVARCHAR(50); DECLARE @RemapDebit NUMERIC(18, 3); DECLARE @RemapCredit NUMERIC(18, 3); DECLARE @StdCodeAccount NVARCHAR(50); --ת������std account code DECLARE @StdCodeVoucher NVARCHAR(50); --ת������std account code DECLARE @CurrAcctLevel INT; DECLARE @StdCode NVARCHAR(50); DECLARE @Direction INT; DECLARE @YearDebitBal NUMERIC(18, 3); DECLARE @YearCreditBal NUMERIC(18, 3); DECLARE @BegBal NUMERIC(18, 3); DECLARE @EndBal NUMERIC(18, 3); DECLARE @DebitBal NUMERIC(18, 3); DECLARE @CreditBal NUMERIC(18, 3); --Step 1: Clean up CustBalanceStdManual and copy CustBalanceStd to CustBalanceStdManual TRUNCATE TABLE dbo.CustBalanceStdManual INSERT dbo.CustBalanceStdManual ( PeriodID , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal ) SELECT PeriodID , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal FROM dbo.CustBalanceStd; --Step 2: Get remapping result from C_AccountRemap and calculate new debit/credit movements --�����ض�Ӧ���������C_CustBalanceStdManual�б���Ŀ�ġ���������� DECLARE AccountRemap_Cursor CURSOR FOR SELECT t.Period,t.AcctCode,t.CustomerCode,t.RemapDebit,t.RemapCredit,outa.StdCode AS StdCodeAccount, t.StdCodeVoucher FROM dbo.EnterpriseAccount outa INNER JOIN ( SELECT ar.Period , ar.AcctCode , ar.CustomerCode , SUM(ar.RemapDebit) AS RemapDebit, SUM(ar.RemapCredit) AS RemapCredit, ar.StdCode AS StdCodeVoucher FROM dbo.AccountRemap ar INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId) OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId) GROUP BY ar.Period,ar.AcctCode, ar.CustomerCode,ar.StdCode -- һ��coa + stdaccont ���Լ���ƾ֤�ض�Ӧ�����ֹ��ض�Ӧ,����ҪGroup��ȡSum ) t ON outa.AcctCode = t.AcctCode OPEN AccountRemap_Cursor; FETCH NEXT FROM AccountRemap_Cursor INTO @Period, @AcctCode, @CustomerCode, @RemapDebit, @RemapCredit, @StdCodeAccount,@StdCodeVoucher; WHILE @@FETCH_STATUS = 0 BEGIN --1. ���ת���std account��������C_CustBalanceStdManual�еĻ���insert�� IF NOT EXISTS ( SELECT 1 FROM dbo.CustBalanceStdManual WHERE PeriodId = @Period AND AcctCode = @StdCodeVoucher AND CustomerCode = @CustomerCode ) BEGIN SET @Periods = 1; WHILE @Periods < = 12 BEGIN INSERT dbo.CustBalanceStdManual ( PeriodID , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal ) VALUES ( @Periods , @StdCodeVoucher , @CustomerCode , 0 , 0 , 0 , 0 , 0 , 0 ); SET @Periods = @Periods + 1; END; END; --2. ����ת������std account�ķ����� UPDATE dbo.CustBalanceStdManual SET DebitBal = DebitBal + @RemapDebit , CreditBal = CreditBal + @RemapCredit WHERE PeriodId = @Period AND AcctCode = @StdCodeVoucher AND CustomerCode = @CustomerCode; --3. ����ת������std account�ķ����� UPDATE dbo.CustBalanceStdManual SET DebitBal = DebitBal - @RemapDebit , CreditBal = CreditBal - @RemapCredit WHERE PeriodId = @Period AND AcctCode = @StdCodeAccount AND CustomerCode = @CustomerCode; IF @CustomerCode <> '' BEGIN IF NOT EXISTS ( SELECT 1 FROM dbo.CustBalanceStdManual WHERE PeriodId = @Period AND AcctCode = @StdCodeVoucher AND CustomerCode = '' ) BEGIN SET @Periods = 1; WHILE @Periods < = 12 BEGIN INSERT dbo.CustBalanceStdManual ( PeriodID , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal ) VALUES ( @Periods , @StdCodeVoucher , '' , 0 , 0 , 0 , 0 , 0 , 0 ); SET @Periods = @Periods + 1; END; END; UPDATE dbo.CustBalanceStdManual SET DebitBal = DebitBal + @RemapDebit , CreditBal = CreditBal + @RemapCredit WHERE PeriodId = @Period AND AcctCode = @StdCodeVoucher AND CustomerCode = ''; UPDATE dbo.CustBalanceStdManual SET DebitBal = DebitBal - @RemapDebit , CreditBal = CreditBal - @RemapCredit WHERE PeriodId = @Period AND AcctCode = @StdCodeAccount AND CustomerCode = ''; END; FETCH NEXT FROM AccountRemap_Cursor INTO @Period, @AcctCode, @CustomerCode, @RemapDebit, @RemapCredit, @StdCodeAccount, @StdCodeVoucher; END; CLOSE AccountRemap_Cursor; DEALLOCATE AccountRemap_Cursor; --Step 3: Calculate the accumulated movements for remapped standard accounts --�������ض�Ӧ�ķ�����������±����ۼơ���ĩ�������ڳ� DECLARE UpdateEndBal_Cursor CURSOR FOR --union�ĵ�һ���֣���ҵ��Ŀԭ����Ӧ�ı���Ŀ��ת������ֻ����һ�����ݣ� union�ĵڶ����֣���ҵ��Ŀ�ض�Ӧ��ı���Ŀ��ת��������ܻ��ж��� SELECT MIN(Period) Period , StdCode , Direction FROM ( SELECT ar.Period , a.StdCode FROM dbo.AccountRemap ar INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId) OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId) UNION SELECT ar.Period , ar.StdCode FROM dbo.AccountRemap ar INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId) OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId) ) b INNER JOIN dbo.StandardAccount sa ON b.StdCode = sa.Code GROUP BY StdCode , Direction; OPEN UpdateEndBal_Cursor; FETCH NEXT FROM UpdateEndBal_Cursor INTO @Period, @StdCode, @Direction; WHILE @@FETCH_STATUS = 0 BEGIN WHILE @Period <= 12 BEGIN --Update by CustomerCode DECLARE UpdateEndBalCustomer_Cursor CURSOR FOR SELECT CustomerCode , SUM(DebitBal) , SUM(CreditBal) FROM dbo.CustBalanceStdManual WHERE PeriodID <= @Period AND AcctCode = @StdCode GROUP BY CustomerCode; OPEN UpdateEndBalCustomer_Cursor; FETCH NEXT FROM UpdateEndBalCustomer_Cursor INTO @CustomerCode, @YearDebitBal, @YearCreditBal; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.CustBalanceStdManual SET YearDebitBal = @YearDebitBal , YearCreditBal = @YearCreditBal , EndBal = BegBal + ( DebitBal - CreditBal ) * @Direction WHERE PeriodID = @Period AND AcctCode = @StdCode AND CustomerCode = @CustomerCode; SELECT @EndBal = EndBal FROM dbo.CustBalanceStdManual WHERE PeriodID = @Period AND AcctCode = @StdCode AND CustomerCode = @CustomerCode; UPDATE dbo.CustBalanceStdManual SET BegBal = @EndBal WHERE PeriodID = @Period + 1 AND AcctCode = @StdCode AND CustomerCode = @CustomerCode; FETCH NEXT FROM UpdateEndBalCustomer_Cursor INTO @CustomerCode, @YearDebitBal, @YearCreditBal; END; CLOSE UpdateEndBalCustomer_Cursor; DEALLOCATE UpdateEndBalCustomer_Cursor; SET @Period = @Period + 1; END; FETCH NEXT FROM UpdateEndBal_Cursor INTO @Period, @StdCode, @Direction; END; CLOSE UpdateEndBal_Cursor; DEALLOCATE UpdateEndBal_Cursor; --Step 4: calculate trial balance for all the parent standard account based on the value of leaf node --���¼���ParentCode������ SELECT @CurrAcctLevel = MAX(AcctLevel) FROM dbo.CustBalanceStdManual cbsm INNER JOIN dbo.StandardAccount sa ON cbsm.AcctCode = sa.Code; WHILE @CurrAcctLevel > 1 BEGIN DECLARE ParentNode_Cursor CURSOR FOR SELECT PeriodID , sa.ParentCode , CustomerCode , SUM(sa.Direction * BegBal) BegBal , SUM(DebitBal) DebitBal , SUM(CreditBal) CreditBal , SUM(YearDebitBal) YearDebitBal , SUM(YearCreditBal) YearCreditBal , SUM(sa.Direction * EndBal) EndBal FROM dbo.CustBalanceStdManual cbs INNER JOIN dbo.StandardAccount sa ON cbs.AcctCode = sa.Code WHERE sa.ParentCode IS NOT NULL AND sa.AcctLevel = @CurrAcctLevel GROUP BY PeriodID , sa.ParentCode , CustomerCode ORDER BY PeriodID , sa.ParentCode , CustomerCode; OPEN ParentNode_Cursor; FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode, @CustomerCode, @BegBal, @DebitBal, @CreditBal, @YearDebitBal, @YearCreditBal, @EndBal; WHILE @@FETCH_STATUS = 0 BEGIN IF @CustomerCode = '' BEGIN SELECT @Direction = Direction FROM dbo.StandardAccount WHERE Code = @AcctCode; -- ��������ڵĻ���insert IF NOT EXISTS ( SELECT 1 FROM dbo.CustBalanceStdManual WHERE PeriodID = @Period AND AcctCode = @AcctCode AND @CustomerCode = '' ) BEGIN INSERT dbo.CustBalanceStdManual ( PeriodID , AcctCode , CustomerCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal ) VALUES ( @Period , @AcctCode , @CustomerCode , @Direction * @BegBal , @DebitBal , @CreditBal , @YearDebitBal , @YearCreditBal , @Direction * @EndBal ); END; ELSE BEGIN UPDATE dbo.CustBalanceStdManual SET BegBal = @Direction * @BegBal , DebitBal = @DebitBal , CreditBal = @CreditBal , YearDebitBal = @YearDebitBal , YearCreditBal = @YearCreditBal , EndBal = @Direction * @EndBal WHERE PeriodID = @Period AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode; END; END; FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode, @CustomerCode, @BegBal, @DebitBal, @CreditBal, @YearDebitBal, @YearCreditBal, @EndBal; END; CLOSE ParentNode_Cursor; DEALLOCATE ParentNode_Cursor; SET @CurrAcctLevel = @CurrAcctLevel - 1; END; --Step 5: update BegDebitBal/BegCreditBal; EndDebitBal/EndCreditBal UPDATE bs SET bs.BegDebitBal = CASE WHEN sta.Direction = 1 THEN bs.BegBal ELSE 0 END, bs.BegCreditBal = CASE WHEN sta.Direction = -1 THEN bs.BegBal ELSE 0 END, bs.EndDebitBal = CASE WHEN sta.Direction = 1 THEN bs.EndBal ELSE 0 END, bs.EndCreditBal = CASE WHEN sta.Direction = -1 THEN bs.EndBal ELSE 0 END FROM dbo.CustBalanceStdManual bs INNER JOIN dbo.StandardAccount sta ON bs.AcctCode = sta.Code END; GO CREATE PROCEDURE [dbo].[InitBalanceStdRemap] @VoucherRemapId INT, @CustRemapId INT AS BEGIN SET NOCOUNT ON; DECLARE @Period INT; DECLARE @Periods INT; DECLARE @AcctCode NVARCHAR(50); DECLARE @CustomerCode NVARCHAR(50); DECLARE @RemapDebit NUMERIC(18, 3); DECLARE @RemapCredit NUMERIC(18, 3); DECLARE @StdCodeAccount NVARCHAR(50); DECLARE @StdCodeVoucher NVARCHAR(50); DECLARE @CurrAcctLevel INT; DECLARE @StdCode NVARCHAR(50); DECLARE @Direction INT; DECLARE @YearDebitBal NUMERIC(18, 3); DECLARE @YearCreditBal NUMERIC(18, 3); DECLARE @BegBal NUMERIC(18, 3); DECLARE @EndBal NUMERIC(18, 3); DECLARE @DebitBal NUMERIC(18, 3); DECLARE @CreditBal NUMERIC(18, 3); DECLARE @SubProp INT; --Step 1: Clean up C_BalanceStdManual and copy C_BalanceStd to C_BalanceStdManual TRUNCATE TABLE dbo.BalanceStdManual; INSERT dbo.BalanceStdManual ( PeriodID , AcctCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , SubProp , CarryoverDebit , CarryoverCredit , YearCarryoverDebit , YearCarryoverCredit , DebitNet , CreditNet , YearDebitNet , YearCreditNet ) SELECT PeriodID , AcctCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , SubProp , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 FROM dbo.BalanceStd; --Step 2: Get remapping result from C_AccountRemap and calculate new debit/credit movements DECLARE AccountRemap_Cursor CURSOR FOR SELECT t.Period,t.AcctCode,t.CustomerCode,t.RemapDebit,t.RemapCredit,outa.StdCode AS StdCodeAccount, t.StdCodeVoucher FROM dbo.EnterpriseAccount outa INNER JOIN ( SELECT ar.Period , ar.AcctCode , ar.CustomerCode , SUM(ar.RemapDebit) AS RemapDebit , SUM(ar.RemapCredit) AS RemapCredit , ar.StdCode AS StdCodeVoucher FROM dbo.AccountRemap ar INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId) OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId) GROUP BY ar.Period,ar.AcctCode, ar.CustomerCode,ar.StdCode) t ON outa.AcctCode = t.AcctCode OPEN AccountRemap_Cursor; FETCH NEXT FROM AccountRemap_Cursor INTO @Period, @AcctCode, @CustomerCode, @RemapDebit, @RemapCredit, @StdCodeAccount, @StdCodeVoucher; WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS ( SELECT 1 FROM dbo.BalanceStdManual WHERE PeriodId = @Period AND AcctCode = @StdCodeVoucher ) BEGIN SET @Periods = 1; WHILE @Periods < = 12 BEGIN SELECT @SubProp = SubProp FROM dbo.StandardAccount WHERE Code = @StdCodeVoucher; INSERT dbo.BalanceStdManual ( PeriodID , AcctCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , SubProp , CarryoverDebit , CarryoverCredit , YearCarryoverDebit , YearCarryoverCredit , DebitNet , CreditNet , YearDebitNet , YearCreditNet ) VALUES ( @Periods , @StdCodeVoucher , 0 , 0 , 0 , 0 , 0 , 0 , @SubProp , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ); SET @Periods = @Periods + 1; END; END; UPDATE dbo.BalanceStdManual SET DebitBal = DebitBal + @RemapDebit , CreditBal = CreditBal + @RemapCredit WHERE PeriodId = @Period AND AcctCode = @StdCodeVoucher; UPDATE dbo.BalanceStdManual SET DebitBal = DebitBal - @RemapDebit , CreditBal = CreditBal - @RemapCredit WHERE PeriodId = @Period AND AcctCode = @StdCodeAccount; FETCH NEXT FROM AccountRemap_Cursor INTO @Period, @AcctCode, @CustomerCode, @RemapDebit, @RemapCredit, @StdCodeAccount, @StdCodeVoucher; END; CLOSE AccountRemap_Cursor; DEALLOCATE AccountRemap_Cursor; --Step 3: Calculate the accumulated movements for remapped standard accounts DECLARE UpdateEndBal_Cursor CURSOR FOR SELECT MIN(Period) Period , StdCode , Direction FROM ( SELECT ar.Period , a.StdCode FROM dbo.AccountRemap ar INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId) OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId) UNION SELECT ar.Period , ar.StdCode FROM dbo.AccountRemap ar INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId) OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId) ) b INNER JOIN dbo.StandardAccount sa ON b.StdCode = sa.Code GROUP BY StdCode , Direction; OPEN UpdateEndBal_Cursor; FETCH NEXT FROM UpdateEndBal_Cursor INTO @Period, @StdCode, @Direction; WHILE @@FETCH_STATUS = 0 BEGIN WHILE @Period <= 12 BEGIN SELECT @YearDebitBal = SUM(DebitBal) , @YearCreditBal = SUM(CreditBal) FROM dbo.BalanceStdManual WHERE PeriodID <= @Period AND AcctCode = @StdCode; UPDATE dbo.BalanceStdManual SET YearDebitBal = @YearDebitBal , YearCreditBal = @YearCreditBal , EndBal = BegBal + ( DebitBal - CreditBal ) * @Direction WHERE PeriodID = @Period AND AcctCode = @StdCode; SELECT @EndBal = EndBal FROM dbo.BalanceStdManual WHERE PeriodID = @Period AND AcctCode = @StdCode; UPDATE dbo.BalanceStdManual SET BegBal = @EndBal WHERE PeriodID = @Period + 1 AND AcctCode = @StdCode; SET @Period = @Period + 1; END; FETCH NEXT FROM UpdateEndBal_Cursor INTO @Period, @StdCode, @Direction; END; CLOSE UpdateEndBal_Cursor; DEALLOCATE UpdateEndBal_Cursor; --Step 4: calculate trial balance for all the parent standard account based on the value of leaf node SELECT @CurrAcctLevel = MAX(AcctLevel) FROM dbo.BalanceStdManual bsm INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code; WHILE @CurrAcctLevel > 1 BEGIN DECLARE ParentNode_Cursor CURSOR FOR SELECT PeriodID , sa.ParentCode , SUM(sa.Direction * BegBal) BegBal , SUM(DebitBal) DebitBal , SUM(CreditBal) CreditBal , SUM(YearDebitBal) YearDebitBal , SUM(YearCreditBal) YearCreditBal , SUM(sa.Direction * EndBal) EndBal FROM dbo.BalanceStdManual bsm INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code WHERE sa.AcctLevel = @CurrAcctLevel AND sa.ParentCode IS NOT NULL GROUP BY PeriodID , sa.ParentCode ORDER BY PeriodID , sa.ParentCode; OPEN ParentNode_Cursor; FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode, @BegBal, @DebitBal, @CreditBal, @YearDebitBal, @YearCreditBal, @EndBal; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Direction = Direction FROM dbo.StandardAccount WHERE Code = @AcctCode; SELECT @SubProp = SubProp FROM dbo.StandardAccount WHERE Code = @AcctCode; IF NOT EXISTS ( SELECT 1 FROM dbo.BalanceStdManual WHERE PeriodID = @Period AND AcctCode = @AcctCode ) BEGIN INSERT dbo.BalanceStdManual ( PeriodID , AcctCode , BegBal , DebitBal , CreditBal , YearDebitBal , YearCreditBal , EndBal , SubProp , CarryoverDebit , CarryoverCredit , YearCarryoverDebit , YearCarryoverCredit , DebitNet , CreditNet , YearDebitNet , YearCreditNet ) VALUES ( @Period , @AcctCode , @Direction * @BegBal , @DebitBal , @CreditBal , @YearDebitBal , @YearCreditBal , @Direction * @EndBal , @SubProp , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 ); END; ELSE BEGIN UPDATE dbo.BalanceStdManual SET BegBal = @Direction * @BegBal , DebitBal = @DebitBal , CreditBal = @CreditBal , YearDebitBal = @YearDebitBal , YearCreditBal = @YearCreditBal , EndBal = @Direction * @EndBal WHERE PeriodID = @Period AND AcctCode = @AcctCode; END; FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode, @BegBal, @DebitBal, @CreditBal, @YearDebitBal, @YearCreditBal, @EndBal; END; CLOSE ParentNode_Cursor; DEALLOCATE ParentNode_Cursor; SET @CurrAcctLevel = @CurrAcctLevel - 1; END; --Step 5: UPDATE bs SET bs.BegDebitBal = CASE WHEN sta.Direction = 1 THEN bs.BegBal ELSE 0 END, bs.BegCreditBal = CASE WHEN sta.Direction = -1 THEN bs.BegBal ELSE 0 END, bs.EndDebitBal = CASE WHEN sta.Direction = 1 THEN bs.EndBal ELSE 0 END, bs.EndCreditBal = CASE WHEN sta.Direction = -1 THEN bs.EndBal ELSE 0 END FROM dbo.BalanceStdManual bs INNER JOIN dbo.StandardAccount sta ON bs.AcctCode = sta.Code --Step 6: Calculate the carryover and net value (in another stored procedure InitBalanceStdRemapCarryover) END; GO CREATE PROCEDURE [dbo].[InitBalanceStdRemapCarryover] AS BEGIN SET NOCOUNT ON; DECLARE @MaxAcctProp INT; DECLARE @GainLossAcctProp INT; --AcctProp of Gain & Loss accounts DECLARE @ProfitStdCode NVARCHAR(50); --Standard account code of Current Year Profit DECLARE @ProfitAcctCode NVARCHAR(50); --Account code of Current Year Profit DECLARE @Period INT; DECLARE @Group NVARCHAR(50); --Voucher type DECLARE @VID NVARCHAR(256); --Voucher number DECLARE @PeriodDetails INT; DECLARE @AcctCode NVARCHAR(50); DECLARE @CarryoverDebit NUMERIC(18, 3); DECLARE @CarryoverCredit NUMERIC(18, 3); DECLARE @YearCarryoverDebit NUMERIC(18, 3); DECLARE @YearCarryoverCredit NUMERIC(18, 3); DECLARE @CurrAcctLevel INT; --Step 1: Reset Carryover column of C_BalanceStdManual table UPDATE dbo.BalanceStdManual SET CarryoverDebit = 0 , CarryoverCredit = 0 , YearCarryoverDebit = 0 , YearCarryoverCredit = 0; --Step 2: Get the accounting rule (old 2001/new 2007) in current project SELECT @MaxAcctProp = MAX(AcctProp) FROM dbo.StandardAccount; IF @MaxAcctProp = 5 BEGIN --old rule 2001 SET @GainLossAcctProp = 5; SET @ProfitStdCode = '3131'; END; ELSE BEGIN --new rule 2007 SET @GainLossAcctProp = 6; SET @ProfitStdCode = '4103'; END; --Step 3: Get Vouchers by StdCode (combined from account level and voucher level mapping) DECLARE Voucher_Cursor CURSOR FOR SELECT DISTINCT v.Period , v.[Group] , v.VID FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode WHERE ISNULL(v.StdCode, a.StdCode) = @ProfitStdCode; OPEN Voucher_Cursor; FETCH NEXT FROM Voucher_Cursor INTO @Period, @Group, @VID; WHILE @@FETCH_STATUS = 0 BEGIN --Step 3.1: Get Voucher Details by Voucher Period, Group, VID --Maybe gaint voucher IF EXISTS ( SELECT 1 FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode,a.StdCode) = sa.Code WHERE v.Period = @Period AND v.[Group] = @Group AND v.VID = @VID AND sa.Code <> @ProfitStdCode AND sa.AcctProp <> @GainLossAcctProp ) BEGIN DECLARE VoucherDetails_Cursor CURSOR FOR SELECT v.Period , ISNULL(v.StdCode, a.StdCode) AS StdCode , SUM(Debit) AS CarryoverDebit , SUM(Credit) AS CarryoverCredit FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode,a.StdCode) = sa.Code WHERE v.Period = @Period AND v.[Group] = @Group AND v.VID = @VID AND sa.AcctProp = @GainLossAcctProp AND ( CASE sa.Direction WHEN 1 THEN Debit ELSE Credit END ) = 0 GROUP BY v.Period , ISNULL(v.StdCode, a.StdCode) , sa.Direction; OPEN VoucherDetails_Cursor; FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails, @AcctCode, @CarryoverDebit, @CarryoverCredit; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.BalanceStdManual SET CarryoverDebit = CarryoverDebit + @CarryoverDebit , CarryoverCredit = CarryoverCredit + @CarryoverCredit , YearCarryoverDebit = YearCarryoverDebit + @CarryoverDebit , YearCarryoverCredit = YearCarryoverCredit + @CarryoverDebit WHERE PeriodID = @PeriodDetails AND AcctCode = @AcctCode; FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails, @AcctCode, @CarryoverDebit, @CarryoverCredit; END; CLOSE VoucherDetails_Cursor; DEALLOCATE VoucherDetails_Cursor; END; --Maybe normal voucher ELSE BEGIN DECLARE VoucherDetails_Cursor CURSOR FOR SELECT v.Period , ISNULL(v.StdCode, a.StdCode) AS StdCode , CASE sa.Direction WHEN 1 THEN 0 ELSE SUM(Debit) - SUM(Credit) END AS CarryoverDebit , CASE sa.Direction WHEN 1 THEN SUM(Credit) - SUM(Debit) ELSE 0 END AS CarryoverCredit FROM dbo.Voucher v INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode, a.StdCode) = sa.Code WHERE v.Period = @Period AND v.[Group] = @Group AND v.VID = @VID AND sa.AcctProp = @GainLossAcctProp GROUP BY v.Period , ISNULL(v.StdCode, a.StdCode) , sa.Direction; OPEN VoucherDetails_Cursor; FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails, @AcctCode, @CarryoverDebit, @CarryoverCredit; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.BalanceStdManual SET CarryoverDebit = CarryoverDebit + @CarryoverDebit , CarryoverCredit = CarryoverCredit + @CarryoverCredit , YearCarryoverDebit = YearCarryoverDebit + @CarryoverDebit , YearCarryoverCredit = YearCarryoverCredit + @CarryoverDebit WHERE PeriodID = @PeriodDetails AND AcctCode = @AcctCode; FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails, @AcctCode, @CarryoverDebit, @CarryoverCredit; END; CLOSE VoucherDetails_Cursor; DEALLOCATE VoucherDetails_Cursor; END; FETCH NEXT FROM Voucher_Cursor INTO @Period, @Group, @VID; END; CLOSE Voucher_Cursor; DEALLOCATE Voucher_Cursor; --Step 4: Calculate the accumulated amount of current year profits DECLARE AccumulatedProfit_Cursor CURSOR FOR SELECT PeriodID , AcctCode FROM dbo.BalanceStdManual bsm INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code WHERE sa.AcctProp = @GainLossAcctProp; OPEN AccumulatedProfit_Cursor; FETCH NEXT FROM AccumulatedProfit_Cursor INTO @Period, @AcctCode; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @YearCarryoverDebit = SUM(CarryoverDebit) , @YearCarryoverCredit = SUM(CarryoverCredit) FROM dbo.BalanceStdManual WHERE PeriodID <= @Period AND AcctCode = @AcctCode; UPDATE dbo.BalanceStdManual SET YearCarryoverDebit = @YearCarryoverDebit , YearCarryoverCredit = @YearCarryoverCredit WHERE PeriodID = @Period AND AcctCode = @AcctCode; FETCH NEXT FROM AccumulatedProfit_Cursor INTO @Period, @AcctCode; END; CLOSE AccumulatedProfit_Cursor; DEALLOCATE AccumulatedProfit_Cursor; --Step 5: Calculate the amount of all parent accounts SELECT @CurrAcctLevel = MAX(AcctLevel) FROM dbo.StandardAccount; WHILE @CurrAcctLevel > 1 BEGIN DECLARE ParentNode_Cursor CURSOR FOR SELECT PeriodID , sa.ParentCode , SUM(CarryoverDebit) AS CarryoverDebit , SUM(CarryoverCredit) AS CarryoverCredit , SUM(YearCarryoverDebit) AS YearCarryoverDebit , SUM(YearCarryoverCredit) AS YearCarryoverCredit FROM dbo.BalanceStdManual bsm INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code WHERE sa.ParentCode IS NOT NULL AND sa.AcctLevel = @CurrAcctLevel GROUP BY PeriodID , sa.ParentCode ORDER BY PeriodID , sa.ParentCode; OPEN ParentNode_Cursor; FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode, @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit, @YearCarryoverCredit; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE dbo.BalanceStdManual SET CarryoverDebit = CarryoverDebit + @CarryoverDebit , CarryoverCredit = CarryoverCredit + @CarryoverCredit , YearCarryoverDebit = YearCarryoverDebit + @YearCarryoverDebit , YearCarryoverCredit = YearCarryoverCredit + @YearCarryoverCredit WHERE PeriodID = @Period AND AcctCode = @AcctCode; FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode, @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit, @YearCarryoverCredit; END; CLOSE ParentNode_Cursor; DEALLOCATE ParentNode_Cursor; SET @CurrAcctLevel = @CurrAcctLevel - 1; END; --Step 6: Calculate the net values of Debit/Credit/Accumulated Debit/Accumulated Credit UPDATE dbo.BalanceStdManual SET DebitNet = sa.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) , CreditNet = 0 , YearDebitNet = sa.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) , YearCreditNet = 0 FROM dbo.BalanceStdManual bsm INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code WHERE sa.Direction = 1; UPDATE dbo.BalanceStdManual SET DebitNet = 0 , CreditNet = sa.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) , YearDebitNet = 0 , YearCreditNet = sa.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) FROM dbo.BalanceStdManual bsm INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code WHERE sa.Direction = -1; END; GO GO -- ============================================= -- Description: �����ط����Ŀ���ط���total����������parent���ط���total��� -- Parameters: 1. @PeriodId > 0: ֻ��ȡAccountRemap����Period = @PeriodId������ -- @PeriodId < 0: ��ȡAccountRemap�������е����� -- ============================================= CREATE PROCEDURE [dbo].[InitAccountRemapSum] @PeriodId INT AS BEGIN SET NOCOUNT ON; DECLARE @CurrAcctLevel INT; DECLARE @AcctCode NVARCHAR(50); DECLARE @Parent NVARCHAR(50); DECLARE @AcctLevel INT; DECLARE @RemapDebit NUMERIC(18, 3); DECLARE @RemapCredit NUMERIC(18, 3); --Step 1: Clean up AccountRemapSum and insert data from AccountRemap TRUNCATE TABLE dbo.AccountRemapSum; INSERT dbo.AccountRemapSum ( AcctCode , Parent , Period, AcctLevel , RemapDebit , RemapCredit ) SELECT ar.AcctCode , a.ParentCode , @PeriodId, a.AcctLevel , SUM(RemapDebit) AS RemapDebit , SUM(RemapCredit) AS RemapCredit FROM dbo.AccountRemap ar INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode WHERE (@PeriodId > 0 AND Period = @PeriodId) OR(@PeriodId < 0 AND Period IS NOT NULL) GROUP BY ar.AcctCode , a.ParentCode , a.AcctLevel; --Step 2: Calculate the sum value of all parents SELECT @CurrAcctLevel = MAX(AcctLevel) FROM dbo.AccountRemapSum; WHILE @CurrAcctLevel > 1 BEGIN DECLARE ParentNode_Cursor CURSOR FOR SELECT ars.Parent AS AcctCode , a.ParentCode , a.AcctLevel , SUM(RemapDebit) AS RemapDebit , SUM(RemapCredit) AS RemapCredit FROM dbo.AccountRemapSum ars INNER JOIN dbo.EnterpriseAccount a ON ars.Parent = a.AcctCode WHERE ars.AcctLevel = @CurrAcctLevel GROUP BY ars.Parent , a.ParentCode , a.AcctLevel; OPEN ParentNode_Cursor; FETCH NEXT FROM ParentNode_Cursor INTO @AcctCode, @Parent, @AcctLevel, @RemapDebit, @RemapCredit; WHILE @@FETCH_STATUS = 0 BEGIN INSERT dbo.AccountRemapSum ( AcctCode , Parent , Period, AcctLevel , RemapDebit , RemapCredit ) VALUES ( @AcctCode , @Parent , @PeriodId, @AcctLevel , @RemapDebit , @RemapCredit ); FETCH NEXT FROM ParentNode_Cursor INTO @AcctCode, @Parent, @AcctLevel, @RemapDebit, @RemapCredit; END; CLOSE ParentNode_Cursor; DEALLOCATE ParentNode_Cursor; SET @CurrAcctLevel = @CurrAcctLevel - 1; END; END; GO -----------------------------------------Rita 20170519��¼�ȶ��㷨������һ���Զ��ŷָ���ַ�������һ�ű�---------------------------------------------------------------- IF EXISTS(SELECT 1 from sys.objects where name = 'F_Splitstr' and type_desc = 'SQL_TABLE_VALUED_FUNCTION') BEGIN DROP FUNCTION [dbo].[F_Splitstr] END go create function F_Splitstr( @str nvarchar(Max) )returns @return table(id int identity(1,1),value nvarchar(Max)) as begin declare @position int; set @position = CHARINDEX(',', @str); while @position>0 begin insert @return(value) values(left(@str,@position-1)); select @str = STUFF(@str,1,@position,''), @position = CHARINDEX(',', @str) end if @str>'' insert @return(value) values(@str); return end go ------------------------------------------------��¼�ȶ��㷨�������ַ������ƴ��SQL------------------------------------------------------------------------------- IF EXISTS(SELECT 1 from sys.objects where name = 'F_JointCondition' and type_desc = 'SQL_SCALAR_FUNCTION') BEGIN DROP FUNCTION [dbo].[F_JointCondition] END go create function F_JointCondition( @sql nvarchar(Max), @condition nvarchar(Max) )returns nvarchar(Max) as begin declare @i int; declare @j int; declare @str nvarchar(Max); declare @tempTable table(id int identity(1,1),value nvarchar(Max)); declare @value nvarchar(Max); set @str = ''; set @value = ''; --������ʱ�� insert into @tempTable (value) select value from dbo.F_Splitstr(@condition); select @i=count(1) from @tempTable; set @j=1; while(@i>=@j) begin select @value=rtrim(ltrim(value)) from @tempTable where id=@j; set @str = @str+@sql+@value+' '; set @j=@j+1; end return(@str); end go --------------------------------------------��¼�ȶ��㷨��AddEntriesCheckDetailResult������-------------------------------------------------------------------------- IF EXISTS(SELECT 1 from sys.objects where name = 'AddEntriesCheckDetailResult' and type_desc = 'SQL_STORED_PROCEDURE') BEGIN DROP PROCEDURE [dbo].[AddEntriesCheckDetailResult] END GO create procedure [dbo].[AddEntriesCheckDetailResult] ( @resultId varchar(128), @mainCondition nvarchar(Max), @parallelCondition nvarchar(Max), @exclusivesCondition nvarchar(Max), @entriesCondition nvarchar(Max), @isManual int=1, @mainConditionAtm nvarchar(Max), @parallelConditionAtm nvarchar(Max), @entriesConditionAtm nvarchar(Max), @isToAddResult int=1 ) as begin --��ȡ��¼�ȶ����ƾ֤ declare @getVoucherSql nvarchar(Max); declare @temp nvarchar(Max); --��ȡ��¼�ɵ� declare @getDoubtSql nvarchar(Max); declare @mainTempTable table(id int identity(1,1),value nvarchar(Max)); declare @entriesTempTable table(id int identity(1,1),value nvarchar(Max)); declare @i int; declare @j int; declare @value nvarchar(Max); --���÷�¼��ؽ�� declare @getAmtSql nvarchar(Max); declare @mainTempTableAtm table(id int identity(1,1),value nvarchar(Max)); declare @entriesTempTableAtm table(id int identity(1,1),value nvarchar(Max)); declare @k int; declare @g int; declare @valueAtm nvarchar(Max); --��¼�ȶԼ�����ʱ�� declare @voucherTmplCalculator table(id int identity(1,1),Period int,VID nvarchar(256),[Group] nvarchar(50)); --������ʱ��VoucherTmplCalculator set @getVoucherSql = 'select Period, VID, [Group] from (select Period,VID,[Group] from Voucher '; if(@isManual = 0) begin set @getVoucherSql = @getVoucherSql+'where importType <> 7 '; end if(@mainCondition<>'') begin set @temp = dbo.F_JointCondition(' intersect select Period,VID,[Group] from Voucher where ',@mainCondition); set @getVoucherSql = @getVoucherSql+@temp; end if(@parallelCondition<>'') begin set @getVoucherSql = @getVoucherSql+' intersect select Period,VID,[Group] from Voucher where '+@parallelCondition; end if(@exclusivesCondition<>'') begin set @temp = dbo.F_JointCondition(' except select Period,VID,[Group] from Voucher where ',@exclusivesCondition); set @getVoucherSql = @getVoucherSql+@temp; end if(@entriesCondition<>'') begin set @temp = dbo.F_JointCondition(' intersect select VoucherID,Period,VID,[Group] from Voucher where ',@entriesCondition); set @getVoucherSql = @getVoucherSql+' intersect select distinct Period,VID,[Group] from (select VoucherID,Period,VID,[Group] from Voucher '+@temp+')abc'; end set @getVoucherSql = @getVoucherSql + ')tb'; set @getVoucherSql = @getVoucherSql+' group by Period,VID,[Group]'; --print @getVoucherSql; insert into @voucherTmplCalculator(Period, VID,[Group]) exec(@getVoucherSql); --����#temp��ʱ�� select * into #temp from (select NEWID() as ID,@resultId as ResultId,0 as IsDoubt,0 as IsRelevantAmt,v.* from @voucherTmplCalculator t left join (select VoucherID,VID,[Date],[Group],Period,CustomerCode,CustomerName,Summary,StdCode,Debit,Credit,ItemID,ImportType,AcctCode from Voucher) v on v.Period = t.Period and v.[VID] = t.[VID] and v.[Group] = t.[Group])TB --�����ɵ��¼ set @getDoubtSql = 'update #temp set IsDoubt=1 where '; if(@mainCondition<>'') begin --������ʱ�� insert into @mainTempTable (value) select value from dbo.F_Splitstr(@mainCondition); select @i=count(1) from @mainTempTable; set @j=1; while(@i>=@j) begin select @value=rtrim(ltrim(value)) from @mainTempTable where id=@j; if(@j>1) set @getDoubtSql = @getDoubtSql+' or '+@value+' '; else set @getDoubtSql = @getDoubtSql+@value; set @j=@j+1; end end if(@parallelCondition<>'') begin if(@mainCondition<>'') begin set @getDoubtSql = @getDoubtSql+' or '; end set @getDoubtSql = @getDoubtSql+@parallelCondition; end if(@entriesCondition<>'') begin if(@mainCondition<>'' or @parallelCondition<>'') begin set @getDoubtSql = @getDoubtSql+' or '; end --������ʱ�� insert into @entriesTempTable (value) select value from dbo.F_Splitstr(@entriesCondition); select @i=count(1) from @entriesTempTable; set @j=1; while(@i>=@j) begin select @value=rtrim(ltrim(value)) from @entriesTempTable where id=@j; if(@j>1) set @getDoubtSql = @getDoubtSql+' or '+@value+' '; else set @getDoubtSql = @getDoubtSql+@value+' '; set @j=@j+1; end end if(@mainCondition = '' and @parallelCondition = '' and @entriesCondition='') begin set @getDoubtSql = @getDoubtSql + ' 0=1'; end --print @getDoubtSql; exec(@getDoubtSql); --���÷�¼��ؽ�� set @getAmtSql = 'update #temp set IsRelevantAmt=1 where '; if(@mainConditionAtm<>'') begin --������ʱ�� insert into @mainTempTableAtm (value) select value from dbo.F_Splitstr(@mainConditionAtm); select @i=count(1) from @mainTempTableAtm; set @j=1; while(@i>=@j) begin select @valueAtm=rtrim(ltrim(value)) from @mainTempTableAtm where id=@j; if(@j>1) set @getAmtSql = @getAmtSql+' or '+@value+' '; else set @getAmtSql = @getAmtSql+@value; set @j=@j+1; end end if(@parallelConditionAtm<>'') begin if(@mainConditionAtm<>'') begin set @getAmtSql = @getAmtSql+' or '; end set @getAmtSql = @getAmtSql+@parallelConditionAtm; end if(@entriesConditionAtm<>'') begin if(@mainConditionAtm<>'' or @parallelConditionAtm<>'') begin set @getAmtSql = @getAmtSql+' or '; end --������ʱ�� insert into @entriesTempTableAtm (value) select value from dbo.F_Splitstr(@entriesConditionAtm); select @i=count(1) from @entriesTempTableAtm; set @j=1; while(@i>=@j) begin select @valueAtm=rtrim(ltrim(value)) from @entriesTempTableAtm where id=@j; if(@j>1) set @getAmtSql = @getAmtSql+' or '+@value+' '; else set @getAmtSql = @getAmtSql+@value+' '; set @j=@j+1; end end if(@mainConditionAtm = '' and @parallelConditionAtm = '' and @entriesConditionAtm = '') begin set @getAmtSql = @getAmtSql + ' 0=1'; end --print @getAmtSql; exec(@getAmtSql); if(@isToAddResult = 1) begin insert into EntriesCheckDetailResult(ID,ResultID,IsDoubt,IsRelevantAmt,VoucherID,VID,[Date], [Group],Period,CustomerCode,CustomerName,Summary,StdCode,Debit,Credit,ItemID,ImportType,AcctCode) select ID,ResultID,IsDoubt,IsRelevantAmt,VoucherID,VID,[Date],[Group],Period,CustomerCode, CustomerName,Summary,StdCode,Debit,Credit,ItemID,ImportType,AcctCode from #temp; end --Return results of the model select * from #temp; end go ---------------------------------------------------------------------------------------------------------------------------------------------------------------- --***************************end replace holder part************************* -----start---------from taxadmin db------------------------------------ INSERT INTO [dbo].[StandardAccount] SELECT * FROM [TaxAdmin].[dbo].[StandardAccount] WHERE IsActive = 1 and IndustryId = 'DBKeyword_IndustryId' GO if not exists (select 1 from [dbo].[StandardAccount]) begin INSERT INTO [dbo].[StandardAccount] SELECT * FROM [TaxAdmin].[dbo].[StandardAccount] WHERE IsActive = 1 and IndustryId = '0' end Insert INTO [dbo].[EnterpriseAccount] SELECT a.[ID],a.[Code],a.[Name],a.[ParentCode],a.[FullName],a.[AcctProp],a.[SubProp],a.[AcctLevel],a.[Direction],a.[IsLeaf],a.[IsActive],a.[EnglishName], b.[StandardAccountCode] as StdCode,a.[EnterpriseAccountSetID],0,0,a.[CreatorID],a.[UpdatorID],a.[CreateTime],a.[UpdateTime] FROM [TaxAdmin].[dbo].[EnterpriseAccount] a left join [TaxAdmin].[dbo].[AccountMapping] b on a.EnterpriseAccountSetID = b.EnterpriseAccountSetID and a.Code = b.EnterpriseAccountCode and b.OrganizationID = 'DBKeyword_OrganizationID' --left join [TaxAdmin].[dbo].[StandardAccount] c on b.StandardAccountCode = c.Code and b.IndustryID = c.IndustryID and b.IndustryId = 'DBKeyword_IndustryId' and c.IsActive = 1 WHERE a.IsActive = 1 and a.[EnterpriseAccountSetID] = 'DBKeyword_EnterpriseAccountSetID'; GO -----end---------from taxadmin db------------------------------------ ---- start ModelBackFillAmount------ IF NOT EXISTS (select 1 from sys.tables where name = 'ModelBackFillAmount' and type = 'U') BEGIN create table dbo.ModelBackFillAmount ( [ID] [nvarchar](128) NOT NULL, [DataSourceID] [nvarchar](128) NOT NULL, [EntriesCheckResultID] [nvarchar](128) NOT NULL, [Amount] [decimal](20, 4) NOT NULL, [CellDataID] [nvarchar](128) NOT NULL, PRIMARY KEY(ID) ); END GO ---- end ModelBackFillAmount------ ----CellComment---- IF NOT EXISTS (select 1 from sys.tables where name = 'CellComment') BEGIN CREATE TABLE [dbo].[CellComment]( [ID] [varchar](128) NOT NULL, [CellDataId] [varchar](128) NOT NULL, [UserId] [varchar](128) NOT NULL, [UserName] [nvarchar](50) NULL, [Comment] [nvarchar](2048) NULL, [ReplyToUserName] [nvarchar](50) NULL, [CreateTime] [datetime] NOT NULL, [UpdateTime] [datetime] NOT NULL, CONSTRAINT [PK_CellComment] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO ----End of CellComment---- ----ModifiedReportCell---- IF NOT EXISTS (select 1 from sys.tables where name = 'ModifiedReportCell') BEGIN CREATE TABLE [dbo].[ModifiedReportCell]( [ID] [varchar](128) NOT NULL, [ReportId] [varchar](128) NOT NULL, [Row] [int] NOT NULL, [Col] [int] NOT NULL, [OriginalValue] [nvarchar](200) NULL, [Value] [nvarchar](200) NOT NULL, [RelatedCellDataId] [varchar](128) NULL, [Comment] [nvarchar](1024) NULL, [CreateTime] [datetime2](7) NOT NULL, [UpdateTime] [datetime2](7) NOT NULL, CONSTRAINT [PK_ModifiedReportCell] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO ----End of ModifiedReportCell----