Segue abaixo o script que deve ser executado junto à base de dados de processamento do e-Forms:
/****** Object: Table [dbo].[tbWebServiceDocumentControl] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICEDOCSITUATION]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBWEBSERVICEDOCSITUATION]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [SEARCHIN] [nvarchar](max) NOT NULL, [SEARCHOUT] [nvarchar](max) NULL, [STATUS] [tinyint] NOT NULL, CONSTRAINT [PK_TBWEBSERVICEDOCSITUATION] 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 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICEDOCUMENTCONTROL]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBWEBSERVICEDOCUMENTCONTROL]( [protocolId] [bigint] NOT NULL, [docCnpj] [bigint] NOT NULL, [docNumber] [int] NOT NULL, [docSerie] [smallint] NOT NULL, [insertDate] [smalldatetime] NULL, CONSTRAINT [PK_tbWebServiceDocumentControl] PRIMARY KEY CLUSTERED ( [docCnpj] ASC, [docNumber] ASC, [docSerie] 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 /****** Object: Default [DF_TBWEBSERVICEDOCUMENTCONTROL_insertDate] ******/ IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_TBWEBSERVICEDOCUMENTCONTROL_insertDate]') AND parent_object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICEDOCUMENTCONTROL]')) Begin IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_TBWEBSERVICEDOCUMENTCONTROL_insertDate]') AND type = 'D') BEGIN --VERIFYING IF COLUMN 'insertDate' EXISTS IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TBWEBSERVICEDOCUMENTCONTROL' AND COLUMN_NAME = 'insertDate' AND TABLE_SCHEMA = 'dbo') ALTER TABLE [dbo].[TBWEBSERVICEDOCUMENTCONTROL] ADD insertDate SMALLDATETIME NULL -- ALTER TABLE [dbo].[TBWEBSERVICEDOCUMENTCONTROL] ADD CONSTRAINT [DF_TBWEBSERVICEDOCUMENTCONTROL_insertDate] DEFAULT (getdate()) FOR [insertDate] END
END
/****** Object: Table [dbo].[TBDOCSEARCHPROCESS] ******/ IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBDOCSEARCHPROCESS]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBDOCSEARCHPROCESS]( [PROCESSID] [bigint] IDENTITY(1,1) NOT NULL, [DOCKEY1] [bigint] NOT NULL, [DOCKEY2] [bigint] NOT NULL, [DOCKEY3] [bigint] NOT NULL, [PROTOCOL] [varchar](max) NOT NULL, CONSTRAINT [PK_TBDOCSEARCHPROCESS] PRIMARY KEY CLUSTERED ( [PROCESSID] 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
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbWebServiceDocument]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBWEBSERVICEDOCUMENT]( [protocolId] [bigint] NOT NULL, [insertDate] [smalldatetime] NOT NULL, [documentData] [xml] NOT NULL, [serviceKind] [tinyint] NOT NULL, CONSTRAINT [PK_tbWebServiceDocument] PRIMARY KEY CLUSTERED ( [protocolId] 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 /****** Object: UserDefinedFunction [dbo].[CreateListTable] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateListTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE FUNCTION [dbo].[CreateListTable] (@list nvarchar(MAX)) RETURNS @tbl TABLE (number bigint NOT NULL) AS BEGIN DECLARE @pos int, @nextpos int, @valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex('','', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1 INSERT @tbl (number) VALUES (convert(bigint, substring(@list, @pos + 1, @valuelen))) SELECT @pos = @nextpos END RETURN END ' END GO /****** Object: UserDefinedFunction [dbo].[CreateKeyTable] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateKeyTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE FUNCTION [dbo].[CreateKeyTable] (@list nvarchar(MAX)) RETURNS @tbl TABLE ( dockey nvarchar(MAX) NOT NULL, docprotocol bigint NOT NULL, dckey01 bigint NOT NULL, dckey02 bigint NOT NULL, dckey03 bigint NOT NULL) AS BEGIN DECLARE @pos int, @nextpos int, @valuelen int, @documentkey nvarchar(MAX), @documenttype nvarchar(2), @protocol bigint, @dockey01 bigint, @dockey02 bigint, @dockey03 bigint
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0 BEGIN
SELECT @nextpos = charindex('','', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1
SELECT @documentkey = substring(@list, @pos + 1, @valuelen) IF len(@documentkey) > 44 BEGIN SELECT @protocol = convert(bigint, substring(@documentkey,45,len(@documentkey))) SELECT @documentkey = substring(@documentkey, 1,44) END ELSE SELECT @protocol = 0 SELECT @documenttype = substring(@documentkey,21,2) IF @documenttype = ''55'' SELECT @documentkey = ''NFe'' + @documentkey ELSE SELECT @documentkey = ''CTe'' + @documentkey SELECT @dockey01 = CONVERT(BIGINT, SUBSTRING(@documentkey, 4,18)) SELECT @dockey02 = CONVERT(BIGINT, SUBSTRING(@documentkey, 22,18)) SELECT @dockey03 = CONVERT(INT, SUBSTRING(@documentkey, 40,9)) INSERT @tbl (dockey, docprotocol, dckey01, dckey02, dckey03) VALUES (@documentkey, @protocol, @dockey01, @dockey02, @dockey03) SELECT @pos = @nextpos END RETURN END ' END GO /****** Object: UserDefinedFunction [dbo].[CreateComposeValues] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateComposeValues]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE FUNCTION [dbo].[CreateComposeValues] (@list nvarchar(MAX)) RETURNS @tbl TABLE (doccnpj bigint NOT NULL, docnumber int NOT NULL, docserie smallint NOT NULL) AS BEGIN DECLARE @pos int, @nextpos int, @valuelen int, @document nvarchar(MAX), @vcnpj bigint, @vnumber int, @vserie smallint
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0 BEGIN
SELECT @nextpos = charindex('','', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1
SELECT @document = substring(@list, @pos + 1, @valuelen) SELECT @vcnpj = convert(bigint, substring(@document, 1, 14)) SELECT @vnumber = convert(int, substring(@document, 15, 9)) SELECT @vserie = convert(smallint, substring(@document, 24, 3))
INSERT @tbl (doccnpj, docnumber, docserie) VALUES (@vcnpj, @vnumber, @vserie)
SELECT @pos = @nextpos END RETURN END ' END GO /****** Object: Table [dbo].[tbWebServiceInput] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbWebServiceInput]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBWEBSERVICEINPUT]( [serviceInputId] [bigint] IDENTITY(1,1) NOT NULL, [insertDate] [smalldatetime] NOT NULL, [header] [xml] NOT NULL, [documentData] [varbinary](max) NOT NULL, [singleProcess] [tinyint], CONSTRAINT [PK_tbWebServiceInput] PRIMARY KEY CLUSTERED ( [serviceInputId] 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 SET ANSI_PADDING OFF GO
/****** ADICIONA NOVA COLUNA singleProcess A TABELA TBWEBSERVICEINPUT caso não exista ******/ IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name = N'singleProcess' AND Object_ID = Object_ID(N'TBWEBSERVICEINPUT')) BEGIN ALTER TABLE TBWEBSERVICEINPUT ADD singleProcess TINYINT END GO
/****** Object: Default [DF_tbWebServiceDocument_insertDate] ******/ IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_tbWebServiceDocument_insertDate]') AND parent_object_id = OBJECT_ID(N'[dbo].[tbWebServiceDocument]')) Begin IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tbWebServiceDocument_insertDate]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tbWebServiceDocument] ADD CONSTRAINT [DF_tbWebServiceDocument_insertDate] DEFAULT (getdate()) FOR [insertDate] END
End GO /****** Object: Default [DF_tbWebServiceInput_insertDate] ******/ IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_tbWebServiceInput_insertDate]') AND parent_object_id = OBJECT_ID(N'[dbo].[tbWebServiceInput]')) Begin IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tbWebServiceInput_insertDate]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tbWebServiceInput] ADD CONSTRAINT [DF_tbWebServiceInput_insertDate] DEFAULT (getdate()) FOR [insertDate] END
End GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBSTATISTICSCONSULT]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBSTATISTICSCONSULT]( [STATISTICSID] [bigint] IDENTITY(1,1) NOT NULL, [CNPJ] [bigint] NOT NULL, [DOCTYPE] [tinyint] NOT NULL, [AMBIENTTYPE] [smallint] NOT NULL, [PROCSTATUS] [bit] NOT NULL, [DOCUMENTKEY1] [bigint] NOT NULL, [DOCUMENTKEY2] [bigint] NOT NULL, [DOCUMENTKEY3] [int] NOT NULL, [CONSULTDATE] [smalldatetime] NOT NULL, CONSTRAINT [PK_ TBSTATISTICSCONSULT] PRIMARY KEY CLUSTERED ( [STATISTICSID] 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 /****** Object: Table [dbo].[TBWEBSERVICENSUCONTROL] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICENSUCONTROL]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBWEBSERVICENSUCONTROL]( [OID] [bigint] IDENTITY(1,1) NOT NULL, [CNPJ] [varchar](14) NOT NULL, [JOB] [varchar](60) NOT NULL, [UF] [varchar](2) NOT NULL, [ULTNSU] [bigint] NOT NULL, CONSTRAINT [PK_TBWEBSERVICENSUCONTROL] PRIMARY KEY CLUSTERED ( [OID] 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 SET ANSI_PADDING OFF GO IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICENSUCONTROL]') AND name = N'IX_NSUCONTROL') CREATE NONCLUSTERED INDEX [IX_NSUCONTROL] ON [dbo].[TBWEBSERVICENSUCONTROL] ( [CNPJ] ASC, [JOB] ASC, [UF] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICECOLDNSU]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBWEBSERVICECOLDNSU] ( [COLDID] [bigint] NOT NULL, [CNPJ] [varchar](max) NOT NULL, [NSU] [bigint] NULL ) ON [PRIMARY] END GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICECOLDCONTROL]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBWEBSERVICECOLDCONTROL] ( [COLDID] [bigint] NOT NULL, [DOCQTD] [bigint] NOT NULL ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBWEBSERVICECOLDCONTROLCNPJ]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBWEBSERVICECOLDCONTROLCNPJ] ( [CNPJ] [bigint] NOT NULL, [STATUS] [smallint] NOT NULL, [BLOCKDATE] [datetime] ) ON [PRIMARY] END GO |