
/*------ ATD 261118 ------*/
IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MANIFESTOCARGA')
BEGIN
	CREATE TABLE dbo.MANIFESTOCARGA
		(ID							INT	NOT NULL IDENTITY
		,rFilial					INT	NOT NULL
		,Ambiente					INT		
		,Modelo						INT		
		,Serie						VARCHAR(3)	NOT NULL
		,Numero						VARCHAR(9)	NOT NULL
		,Emissao					DATETIME		
		,FlagContingencia			INT		
		,rProvinciaCarregamento		INT NOT NULL
		,rProvinciaDescarregamento	INT NOT NULL
		,CodTran					CHAR(3)		
		,InfAdFisco					VARCHAR(254)		
		,InfCompl					VARCHAR(254)		
		,ChaveAcesso				VARCHAR(254)		
		,Situacao					CHAR(1)		
		,NomeArqXML					VARCHAR(254)		
		,ArqXML						TEXT		
		,Cancelamento				DATETIME		
		,Encerramento				DATETIME		
		,ProtocoloAutorizacao		VARCHAR(30)		
		,ProtocoloEncerramento		VARCHAR(30)		
		,ProtocoloCancelamento		VARCHAR(30)		
		,NumeroRecibo				VARCHAR(30)		
		,Placa						CHAR(7)		
		,Tara						NUMERIC(6)		
		,CapacidadeKG				NUMERIC(6)		
		,rTipoRodado				INT NOT NULL
		,rTipoCarroceria			INT NOT NULL
		,rProvinciaVeiculo			INT NOT NULL
		,CGCCPFProprietario			VARCHAR(254)		
		,RNTRCProprietario			INT		
		,NomeProprietario			VARCHAR(60)		
		,InscEstadualProprietario	VARCHAR(14)		
		,FlagImpresso				INT		
		,TotalMercadoria			NUMERIC(13,2)		
		,TotalPesoBruto				NUMERIC(11,4) 
		,Percurso					VARCHAR(254)
		,JustificativaCancelamento			VARCHAR(254)
		)
	
	ALTER TABLE dbo.MANIFESTOCARGA 
		ADD CONSTRAINT PK_MANIFESTOCARGA PRIMARY KEY (rFilial, Serie, Numero)
END
GO

IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MANIFESTOCARGALACRE')
BEGIN
	CREATE TABLE dbo.MANIFESTOCARGALACRE
		(ID					INT NOT NULL IDENTITY
		,rManifestoCarga	INT NOT NULL
		,Lacre				VARCHAR(60) NOT NULL
		)

	ALTER TABLE dbo.MANIFESTOCARGALACRE 
		ADD	CONSTRAINT PK_MANIFESTOCARGALACRE PRIMARY KEY (rManifestoCarga, Lacre)
END
GO
	
IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MANIFESTOCARGAREBOQUE')
BEGIN
	CREATE TABLE dbo.MANIFESTOCARGAREBOQUE
		(ID							INT NOT NULL IDENTITY
		,rManifestoCarga			INT NOT NULL
		,CodTran					CHAR(3) NOT NULL
		,Placa						CHAR(7)
		,Tara						NUMERIC(6)
		,CapacidadeKG				NUMERIC(6)
		,rTipoCarroceria			INT NOT NULL
		,rProvinciaVeiculo			INT NOT NULL
		,CGCCPFProprietario			VARCHAR(254)
		,RNTRCProprietario			INT
		,NomeProprietario			VARCHAR(60)
		,InscEstadualProprietario	VARCHAR(14)
		)

	ALTER TABLE dbo.MANIFESTOCARGAREBOQUE 
		ADD	CONSTRAINT PK_MANIFESTOCARGAREBOQUE PRIMARY KEY (rManifestoCarga, CodTran)
END
GO
	
IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MANIFESTOCARGAPEDAGIO')
BEGIN
	CREATE TABLE dbo.MANIFESTOCARGAPEDAGIO
		(ID					INT NOT NULL IDENTITY
		,rManifestoCarga	INT NOT NULL
		,CNPJForn			VARCHAR(254) NOT NULL
		,CNPJPG				VARCHAR(254) NOT NULL
		,NumeroComprovante	VARCHAR(20) NOT NULL
		)

	ALTER TABLE dbo.MANIFESTOCARGAPEDAGIO 
		ADD	CONSTRAINT PK_MANIFESTOCARGAPEDAGIO PRIMARY KEY (rManifestoCarga, CNPJForn, NumeroComprovante)
END
GO

IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MANIFESTOCARGADOCUMENTO')
BEGIN
	CREATE TABLE dbo.MANIFESTOCARGADOCUMENTO
		(ID						INT NOT NULL IDENTITY
		,rManifestoCarga		INT NOT NULL
		,NumOrd					INT NOT NULL
		,rCidadeDescarregamento	INT NOT NULL
		)

	ALTER TABLE dbo.MANIFESTOCARGADOCUMENTO 
		ADD CONSTRAINT PK_MANIFESTOCARGADOCUMENTO PRIMARY KEY (rManifestoCarga, NumOrd)
END
GO

IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MANIFESTOCARGACONDUTOR')
BEGIN
	CREATE TABLE dbo.MANIFESTOCARGACONDUTOR
		(ID					INT NOT NULL IDENTITY
		,rManifestoCarga	INT NOT NULL
		,Nome				VARCHAR(60)		
		,CPF				CHAR(11) NOT NULL
		)

	ALTER TABLE dbo.MANIFESTOCARGACONDUTOR
		ADD CONSTRAINT PK_MANIFESTOCARGACONDUTOR PRIMARY KEY (rManifestoCarga, CPF)
END
GO

IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MANIFESTOCARGAHISTORICO')
BEGIN
	CREATE TABLE dbo.MANIFESTOCARGAHISTORICO
		(ID					INT NOT NULL IDENTITY
		,rManifestoCarga	INT	NOT NULL
		,DataProcessamento	DATETIME		
		,Versao				VARCHAR(30)
		,rUsuario			INT NOT NULL
		,Historico			VARCHAR(254)
		,CodigoResposta		VARCHAR(3)		
		,DescricaoResposta	VARCHAR(254)
		,ArqXML				VARCHAR(4000)		
		)

	ALTER TABLE dbo.MANIFESTOCARGAHISTORICO
		ADD CONSTRAINT PK_MANIFESTOCARGAHISTORICO PRIMARY KEY (ID)
END
GO

IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MANIFESTOCARGAMUNCARREGA')
BEGIN
	CREATE TABLE dbo.MANIFESTOCARGAMUNCARREGA
		(ID						INT NOT NULL IDENTITY
		,rManifestoCarga		INT	NOT NULL
		,rCidadeCarregamento	INT	NOT NULL
		)

	ALTER TABLE dbo.MANIFESTOCARGAMUNCARREGA
		ADD CONSTRAINT PK_MANIFESTOCARGAMUNCARREGA PRIMARY KEY (rManifestoCarga,rCidadeCarregamento)
END
GO

IF NOT EXISTS (SELECT T.NAME TABELA, C.NAME COLUNA FROM SYSCOLUMNS C, SYSOBJECTS T
				WHERE T.XTYPE = 'U' AND C.ID = T.ID AND T.NAME = 'TABTRANFAT' AND C.NAME = 'rTipoRodado')
BEGIN
	ALTER TABLE dbo.TABTRANFAT ADD
		 rTipoRodado		INT	NOT NULL CONSTRAINT DF_TABTRANFAT_RTIPORODADO DEFAULT 7
		,rTipoCarroceria	INT	NOT NULL CONSTRAINT DF_TABTRANFAT_RTIPOCARROC DEFAULT 7
		,RNTRCProprietario	INT 
END
GO

IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE xtype='U' AND name='MODOEMISSAOMDFE')
BEGIN
	CREATE TABLE dbo.MODOEMISSAOMDFE
		(ID				INT NOT NULL IDENTITY
		,rFilial		INT NOT NULL
		,dhCont			DATETIME
		,xJust			VARCHAR(255)
		,rTipoEmissao	INT NOT NULL
		)
		
	ALTER TABLE dbo.MODOEMISSAOMDFE
		ADD CONSTRAINT PK_MODOEMISSAOMDFE PRIMARY KEY (ID)
END
GO
IF NOT EXISTS (SELECT T.NAME TABELA, C.NAME COLUNA FROM SYSCOLUMNS C, SYSOBJECTS T
				WHERE T.XTYPE = 'U' AND C.ID = T.ID AND T.NAME = 'COMPLEMENTOPRODUTO' AND C.NAME = 'PesoBruto')
BEGIN
	ALTER TABLE COMPLEMENTOPRODUTO ADD PesoBruto NUMERIC(11,4) NOT NULL CONSTRAINT DF_COMPLEMENTOPRODUTOPESOBRUTO DEFAULT 0
END
GO
IF NOT EXISTS (SELECT 1 FROM SYSINDEXES WHERE NAME = 'IDX_MANIFESTOCARGA01')
	create index IDX_MANIFESTOCARGA01 on dbo.MANIFESTOCARGA(RFILIAL,EMISSAO)
GO
IF NOT EXISTS (SELECT 1 FROM SYSINDEXES WHERE NAME = 'IDX_MANIFESTOCARGA02')
	create index IDX_MANIFESTOCARGA02 on dbo.MANIFESTOCARGA(SITUACAO)
GO
IF NOT EXISTS (SELECT 1 FROM SYSINDEXES WHERE NAME = 'IDX_MANIFESTOCARGAHISTORICO01')
	create index IDX_MANIFESTOCARGAHISTORICO01 on dbo.MANIFESTOCARGAHISTORICO(RMANIFESTOCARGA)
GO
IF NOT EXISTS (SELECT 1 FROM SYSINDEXES WHERE NAME = 'IDX_MODOEMISSAOMDFE01')
	create index IDX_MODOEMISSAOMDFE01 on dbo.MODOEMISSAOMDFE(RFILIAL)
GO