Criação das tabelas de trabalho em SQL Server

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

SET QUOTED_IDENTIFIER ON

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

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

/****** 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

/****** 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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

/****** Object: UserDefinedFunction [dbo].[CreateListTable] ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

/****** Object: UserDefinedFunction [dbo].[CreateKeyTable] ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

/****** Object: UserDefinedFunction [dbo].[CreateComposeValues] ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

/****** Object: Table [dbo].[tbWebServiceInput] ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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

SET ANSI_PADDING OFF

/****** 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

/****** 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

/****** 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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

/****** Object: Table [dbo].[TBWEBSERVICENSUCONTROL] ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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

SET ANSI_PADDING OFF

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]

SET QUOTED_IDENTIFIER ON

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

SET QUOTED_IDENTIFIER ON

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

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

Apos atualização para o patch 62 do WS e necessario atualizar esses scripts:

SQL:

ALTER TABLE [dbo].[TBWEBSERVICEDOCUMENTCONTROL] ADD

[CNPJSTR] VARCHAR(14) NULL,

[CNPJBASE] BIGINT NULL,

[CNPJORDEM] INT NULL,

[CNPJDV] TINYINT NULL;

ALTER TABLE [dbo].[TBWEBSERVICECOLDCONTROLCNPJ] ADD

[CNPJSTR] VARCHAR(14) NULL,

[CNPJBASE] BIGINT NULL,

[CNPJORDEM] INT NULL,

[CNPJDV] TINYINT NULL;

ALTER TABLE [dbo].[TBSTATISTICSCONSULT] ADD

[CNPJSTR] VARCHAR(14) NULL,

[CNPJBASE] BIGINT NULL,

[CNPJORDEM] INT NULL,

[CNPJDV] TINYINT NULL,

[ACCESSKEY] VARCHAR(44) NULL;

ALTER TABLE [dbo].[TBCOLDSEARCHRESULT] ADD

[CNPJSTR] VARCHAR(14) NULL,

[CNPJBASE] BIGINT NULL,

[CNPJORDEM] INT NULL,

[CNPJDV] TINYINT NULL,

[ACCESSKEY] VARCHAR(44) NULL,

[EMISSIONTYPE] TINYINT NULL,

[MODEL] SMALLINT NULL,

[SERIE] SMALLINT NULL,

[DOCUMENTNUMBER] INT NULL;

ALTER TABLE [dbo].[TBDOCSEARCHPROCESS] ADD

ACCESSKEY VARCHAR(255) NULL,

EMISSIONTYPE SMALLINT NULL,

MODEL SMALLINT NULL,

SERIE INT NULL,

DOCUMENTNUMBER INT NULL;

ALTER TABLE [dbo].[TBSTATISTICSCONSULT] ADD

SERIE INT NULL,

DOCUMENTNUMBER INT NULL;

Apos atualização para o patch 63 do WS e necessario atualizar esses scripts:

ALTER TABLE [dbo].[TBWEBSERVICEDOCUMENTCONTROL] DROP CONSTRAINT [PK_tbWebServiceDocumentControl]

ALTER TABLE TBWEBSERVICEDOCUMENTCONTROL ALTER COLUMN DOCCNPJ BIGINT NULL

DROP FUNCTION [dbo].[CreateKeyTable]

/****** Object: UserDefinedFunction [dbo].[CreateKeyTable] Script Date: 19/05/2026 11:02:53 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [DBO].[CREATEKEYTABLE] (@LIST NVARCHAR(MAX))

RETURNS @TBL TABLE

(

DOCKEY NVARCHAR(MAX) NOT NULL,

DOCPROTOCOL BIGINT NOT NULL,

EMISSIONTYPE SMALLINT NOT NULL,

DOCUMENTNUMBER BIGINT NOT NULL,

MODEL SMALLINT NOT NULL,

SERIE SMALLINT NOT NULL,

CNPJORDEM BIGINT NOT NULL,

CNPJBASE BIGINT NOT NULL,

CNPJDV SMALLINT NOT NULL

)

BEGIN

DECLARE @POS INT,

@NEXTPOS INT,

@VALUELEN INT,

@DOCUMENTKEY NVARCHAR(MAX),

@DOCUMENTTYPE NVARCHAR(2),

@PROTOCOL BIGINT,

@EMISSIONTYPE SMALLINT,

@DOCUMENTNUMBER BIGINT,

@MODEL SMALLINT,

@SERIE SMALLINT,

@CNPJORDEM BIGINT,

@CNPJBASE BIGINT,

@CNPJDV SMALLINT,

@CNPJ VARCHAR(14)

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)

-- PROTOCOLO

IF LEN(@DOCUMENTKEY) > 44

BEGIN

SELECT @PROTOCOL =

CONVERT(BIGINT, SUBSTRING(@DOCUMENTKEY, 45, LEN(@DOCUMENTKEY)))

SELECT @DOCUMENTKEY =

SUBSTRING(@DOCUMENTKEY, 1, 44)

END

ELSE

BEGIN

SELECT @PROTOCOL = 0

END

-- MODELO

SELECT @DOCUMENTTYPE =

SUBSTRING(@DOCUMENTKEY, 21, 2)

SELECT @MODEL =

CONVERT(SMALLINT, @DOCUMENTTYPE)

-- EMISSION TYPE

SELECT @EMISSIONTYPE =

CONVERT(SMALLINT, SUBSTRING(@DOCUMENTKEY, 35, 1))

-- SERIE

SELECT @SERIE =

CONVERT(SMALLINT, SUBSTRING(@DOCUMENTKEY, 23, 3))

-- DOCUMENT NUMBER

SELECT @DOCUMENTNUMBER =

CONVERT(BIGINT, SUBSTRING(@DOCUMENTKEY, 26, 9))

-- CNPJ (POSIÇÕES 7 A 20)

SELECT @CNPJ =

SUBSTRING(@DOCUMENTKEY, 7, 14)

-------------------------------------------------------------------

-- CNPJBASE (8 POSIÇÕES)

-------------------------------------------------------------------

SELECT @CNPJBASE =

TRY_CONVERT(BIGINT,

(CAST(ASCII(SUBSTRING(@CNPJ, 1, 1)) - 48 AS DECIMAL(38,0)) * 271818611107) +

(CAST(ASCII(SUBSTRING(@CNPJ, 2, 1)) - 48 AS DECIMAL(38,0)) * 6321363049) +

(CAST(ASCII(SUBSTRING(@CNPJ, 3, 1)) - 48 AS DECIMAL(38,0)) * 147008443) +

(CAST(ASCII(SUBSTRING(@CNPJ, 4, 1)) - 48 AS DECIMAL(38,0)) * 3418801) +

(CAST(ASCII(SUBSTRING(@CNPJ, 5, 1)) - 48 AS DECIMAL(38,0)) * 79507) +

(CAST(ASCII(SUBSTRING(@CNPJ, 6, 1)) - 48 AS DECIMAL(38,0)) * 1849) +

(CAST(ASCII(SUBSTRING(@CNPJ, 7, 1)) - 48 AS DECIMAL(38,0)) * 43) +

(CAST(ASCII(SUBSTRING(@CNPJ, 8, 1)) - 48 AS DECIMAL(38,0)) * 1)

)

-------------------------------------------------------------------

-- CNPJORDEM (4 POSIÇÕES)

-------------------------------------------------------------------

SELECT @CNPJORDEM =

TRY_CONVERT(BIGINT,

(

(ASCII(SUBSTRING(@CNPJ, 9, 1)) - 48) * 43 +

(ASCII(SUBSTRING(@CNPJ, 10, 1)) - 48)) * 43 +

(ASCII(SUBSTRING(@CNPJ, 11, 1)) - 48)) * 43 +

(ASCII(SUBSTRING(@CNPJ, 12, 1)) - 48)

)

-------------------------------------------------------------------

-- CNPJDV (2 POSIÇÕES)

-------------------------------------------------------------------

SELECT @CNPJDV =

TRY_CONVERT(SMALLINT,

(

(ASCII(SUBSTRING(@CNPJ, 13, 1)) - 48) * 10 +

(ASCII(SUBSTRING(@CNPJ, 14, 1)) - 48)

))

-------------------------------------------------------------------

-- PREFIXO DOCUMENTO

-------------------------------------------------------------------

IF @DOCUMENTTYPE = '55'

SELECT @DOCUMENTKEY = 'NFE' + @DOCUMENTKEY

ELSE

SELECT @DOCUMENTKEY = 'CTE' + @DOCUMENTKEY

-------------------------------------------------------------------

-- INSERT

-------------------------------------------------------------------

INSERT @TBL

(

DOCKEY,

DOCPROTOCOL,

EMISSIONTYPE,

DOCUMENTNUMBER,

MODEL,

SERIE,

CNPJORDEM,

CNPJBASE,

CNPJDV

)

VALUES

(

@DOCUMENTKEY,

@PROTOCOL,

@EMISSIONTYPE,

@DOCUMENTNUMBER,

@MODEL,

@SERIE,

@CNPJORDEM,

@CNPJBASE,

@CNPJDV

)

SELECT @POS = @NEXTPOS

END

RETURN

END

/* =========================================================

TBWEBSERVICEDOCUMENTCONTROL

========================================================= */

-- Permitir NULL temporariamente para remoção da PK

ALTER TABLE TBWEBSERVICEDOCUMENTCONTROL

ALTER COLUMN docCnpj VARCHAR(14) NULL;

-- Remover chave primária atual

ALTER TABLE TBWEBSERVICEDOCUMENTCONTROL

DROP CONSTRAINT PK_tbWebServiceDocumentControl;

-- Alterar tipo da coluna

ALTER TABLE TBWEBSERVICEDOCUMENTCONTROL

ALTER COLUMN docCnpj BIGINT NULL;

-- Recriar chave primária sem docCnpj

ALTER TABLE [dbo].[TBWEBSERVICEDOCUMENTCONTROL]

ADD CONSTRAINT [PK_tbWebServiceDocumentControl]

PRIMARY KEY CLUSTERED

(

[docNumber] ASC,

[docSerie] ASC

)

WITH

(

PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

SORT_IN_TEMPDB = OFF,

IGNORE_DUP_KEY = OFF,

ONLINE = OFF,

ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON,

OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF

)

ON [PRIMARY];

/* =========================================================

TBWEBSERVICECOLDCONTROLCNPJ

========================================================= */

-- Permitir NULL e alterar tipo da coluna CNPJ

ALTER TABLE TBWEBSERVICECOLDCONTROLCNPJ

ALTER COLUMN CNPJ BIGINT NULL;

/* =========================================================

TBSTATISTICSCONSULT

========================================================= */

-- Permitir NULL e alterar tipo da coluna CNPJ

ALTER TABLE TBSTATISTICSCONSULT

ALTER COLUMN CNPJ BIGINT NULL;

-- Permitir NULL e alterar tipo das chaves de documento

ALTER TABLE TBSTATISTICSCONSULT

ALTER COLUMN DocumentKey1 BIGINT NULL;

ALTER TABLE TBSTATISTICSCONSULT

ALTER COLUMN DocumentKey2 BIGINT NULL;

ALTER TABLE TBSTATISTICSCONSULT

ALTER COLUMN DocumentKey3 BIGINT NULL;

Exclusão e criação de indices:

IF EXISTS (

SELECT 1

FROM sys.indexes i

WHERE i.object_id = OBJECT_ID(N'[DBO].[TBWEBSERVICEDOCUMENTCONTROL]')

AND i.name = N'IDX_TBWEBSERVICEDOCUMENTCONTROL'

)

BEGIN

DROP INDEX IDX_TBWEBSERVICEDOCUMENTCONTROL ON [DBO].[TBWEBSERVICEDOCUMENTCONTROL];

END;

CREATE NONCLUSTERED INDEX IDX_TBWEBSERVICEDOCUMENTCONTROL

ON [DBO].[TBWEBSERVICEDOCUMENTCONTROL]

(

CNPJBASE ASC,

CNPJORDEM ASC,

CNPJDV ASC,

DOCNUMBER ASC,

DOCSERIE ASC

)

INCLUDE (PROTOCOLID)

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];