-- FALTA AJUSTAR PARA TER SOMENTE 1 NIVEL DE CLASSE DE PRODUTO
-- CAMPANA 27/12
-- MESMA VISAO DA FLORESTA

DROP TABLE TAB_PARAMETROS_AC_CP
CREATE TABLE tab_parametros_AC_CP (
   empresa    varchar(50),
   nivel1     varchar(20),
   nivel2     varchar(20),
   nivel3     varchar(20),
   nivel4     varchar(20),
   nivel5     varchar(20),
   nivel6     varchar(20),
   nivel7     varchar(20),
   nivel8     varchar(20),
   nivel9     varchar(20),
   nivel10    varchar(20),
   atr1       varchar(20),
   atr2       varchar(20),
   atr3       varchar(20),
   atr4       varchar(20),
   atr5       varchar(20),
   atr6       varchar(20),
   atr7       varchar(20),
   atr8       varchar(20),
   atr9       varchar(20),
   atr10      varchar(20),
   atualizadoem datetime)

insert into tab_parametros_AC_CP (empresa,nivel1,nivel2,nivel3,nivel4,nivel5,nivel6,nivel7,atr1,atr2,ATR3,ATR4)
 values ('COMPRAS','TEMPO', 'FILIAL','FORNECEDOR', 'TPO','COMPRADOR','GRUPO','PRODUTO','QT_COMP','VALOR_COMP','PRAZO','NUM_COMP')
go
/*
DROP TABLE TAB_INI
CREATE TABLE TAB_INI(
    VISAO	nvarchar(50),
   CAMPO4	varchar(250),	
   CAMPO5	varchar(250),	
   CAMPO6	varchar(250),	
   CAMPO7	varchar(250),	
   CAMPO8	varchar(250), 
 CONSTRAINT PK_TAB_INI PRIMARY KEY NONCLUSTERED (VISAO) WITH FILLFACTOR=98)
*/

delete from tab_ini where visao = 'COMPRAS'
GO

INSERT INTO TAB_INI  VALUES 
('COMPRAS', 
 '*NIVEL1_AC_CP,NIVEL2_AC_CP,NIVEL3_AC_CP,NIVEL4_AC_CP,NIVEL5_AC_CP,NIVEL6_AC_CP,NIVEL7_AC_CP',
 'TAB_NIVEL1_AC_CP,TAB_NIVEL2_AC_CP,TAB_NIVEL3_AC_CP,TAB_NIVEL4_AC_CP,TAB_NIVEL5_AC_CP,TAB_NIVEL6_AC_CP,TAB_NIVEL7_AC_CP' ,
 'TAB_PARAMETROS_AC_CP,TAB_SENHA_AC_CP,,ULTIMAS_AC_CP,,,CONSULTA_AC_CP,FORMULA_AC_CP',
 'NIVEL1,NIVEL2,NIVEL3,NIVEL4,NIVEL5,NIVEL6,NIVEL7,NIVEL8,NIVEL9,NIVEL10,DATA,DESCRICAO,,,,,,,,,,ATR1,ATR2,ATR3,ATR4,ATR5,ATR6,ATR7,ATR8,ATR9,ATR10,' ,
 '08,02,05,02,05,09,05' )
GO

DROP TABLE TAB_NIVEL1_AC_CP
CREATE TABLE TAB_NIVEL1_AC_CP (
	NIVEL1 char (8) NOT NULL ,
	DESCRICAO varchar (100) NULL ,
	MES VARchar (50) NULL ,
	BIMESTRE  VARchar (50) NULL ,
	TRIMESTRE VARchar (50) NULL ,
	QUADRIMESTRE VARchar (50) NULL ,
	SEMESTRE VARchar (50)  NULL ,
	ANO VARchar (50) NULL ,
	DIAUTIL VARCHAR(1) NULL,
        CONSTRAINT PK_TAB_NIVEL1_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL1) WITH FILLFACTOR=98)

CREATE INDEX FK_TAB_NIVEL1_AC_CP_1 ON TAB_NIVEL1_AC_CP (DESCRICAO)
CREATE INDEX FK_TAB_NIVEL1_AC_CP_2 ON TAB_NIVEL1_AC_CP (MES)
CREATE INDEX FK_TAB_NIVEL1_AC_CP_3 ON TAB_NIVEL1_AC_CP (BIMESTRE)
CREATE INDEX FK_TAB_NIVEL1_AC_CP_4 ON TAB_NIVEL1_AC_CP (TRIMESTRE)
CREATE INDEX FK_TAB_NIVEL1_AC_CP_5 ON TAB_NIVEL1_AC_CP (QUADRIMESTRE)
CREATE INDEX FK_TAB_NIVEL1_AC_CP_6 ON TAB_NIVEL1_AC_CP (SEMESTRE)
CREATE INDEX FK_TAB_NIVEL1_AC_CP_7 ON TAB_NIVEL1_AC_CP (ANO)
CREATE INDEX FK_TAB_NIVEL1_AC_CP_8 ON TAB_NIVEL1_AC_CP (DIAUTIL)


DROP TABLE TAB_NIVEL2_AC_CP
CREATE TABLE TAB_NIVEL2_AC_CP (
	NIVEL2 char (2) NOT NULL ,
        DESCRICAO varchar (100) NULL,
        CONSTRAINT PK_TAB_NIVEL2_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL2) WITH FILLFACTOR=98)

CREATE INDEX FK_TAB_NIVEL2_AC_CP_1 ON TAB_NIVEL2_AC_CP (DESCRICAO)

DROP TABLE TAB_NIVEL3_AC_CP
CREATE TABLE TAB_NIVEL3_AC_CP (
	NIVEL3 char (10) NOT NULL ,
	DESCRICAO varchar (100) NULL,
        CONSTRAINT PK_TAB_NIVEL3_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL3) WITH FILLFACTOR=98)

CREATE INDEX FK_TAB_NIVEL3_AC_CP_1 ON TAB_NIVEL3_AC_CP (DESCRICAO)



DROP TABLE TAB_NIVEL4_AC_CP
CREATE TABLE TAB_NIVEL4_AC_CP (
	NIVEL4 char(10) NOT NULL ,
        DESCRICAO varchar (100) NULL,
        CONSTRAINT PK_TAB_NIVEL4_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL4) WITH FILLFACTOR=98)

CREATE INDEX FK_TAB_NIVEL4_AC_CP_2 ON TAB_NIVEL4_AC_CP (DESCRICAO)

DROP TABLE TAB_NIVEL5_AC_CP
CREATE TABLE TAB_NIVEL5_AC_CP (
	NIVEL5 char(2) NOT NULL ,
        DESCRICAO varchar (100) NULL,
        CONSTRAINT PK_TAB_NIVEL5_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL5) WITH FILLFACTOR=98)

CREATE INDEX FK_TAB_NIVEL5_AC_CP_2 ON TAB_NIVEL5_AC_CP (DESCRICAO)

DROP TABLE TAB_NIVEL6_AC_CP
CREATE TABLE TAB_NIVEL6_AC_CP (
	NIVEL6 char(3) NOT NULL ,
        DESCRICAO varchar (100) NULL,
        CONSTRAINT PK_TAB_NIVEL6_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL6) WITH FILLFACTOR=98)

CREATE INDEX FK_TAB_NIVEL6_AC_CP_2 ON TAB_NIVEL6_AC_CP (DESCRICAO)

DROP TABLE TAB_NIVEL7_AC_CP
CREATE TABLE TAB_NIVEL7_AC_CP (
	NIVEL7 char(5) NOT NULL ,
        DESCRICAO varchar (100) NULL,
        FORN_PROD CHAR(50) NULL,
        MODELO CHAR(50) NULL,
        CONSTRAINT PK_TAB_NIVEL7_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL7) WITH FILLFACTOR=98)

CREATE INDEX FK_TAB_NIVEL7_AC_CP_2 ON TAB_NIVEL7_AC_CP (DESCRICAO)
CREATE INDEX FK_TAB_NIVEL7_AC_CP_3 ON TAB_NIVEL7_AC_CP (FORN_PROD)
CREATE INDEX FK_TAB_NIVEL7_AC_CP_4 ON TAB_NIVEL7_AC_CP (MODELO)





DROP TABLE NIVEL1_AC_CP
CREATE TABLE NIVEL1_AC_CP (
	NIVEL1 char (8) NOT NULL ,
	DATA datetime NOT NULL ,
	ATR1 float NULL ,
	ATR2 float NULL ,
	ATR3 float NULL ,
	ATR4 float NULL ,
	ATR5 float NULL ,
	ATR6 float NULL ,
        CONSTRAINT PK_NIVEL1_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL1,DATA) WITH FILLFACTOR=98)

DROP TABLE NIVEL2_AC_CP
CREATE TABLE NIVEL2_AC_CP (
	NIVEL2 char (2) NOT NULL ,
	NIVEL1 char (8) NOT NULL ,
	DATA datetime NOT NULL ,
	ATR1 float NULL ,
	ATR2 float NULL ,
	ATR3 float NULL ,
	ATR4 float NULL ,
	ATR5 float NULL ,
	ATR6 float NULL ,
        CONSTRAINT PK_NIVEL2_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL1,NIVEL2,DATA) WITH FILLFACTOR=98)
 
CREATE INDEX FK_NIVEL2_AC_CP_1 ON NIVEL2_AC_CP (NIVEL2,DATA)

DROP TABLE NIVEL3_AC_CP
CREATE TABLE NIVEL3_AC_CP (
	NIVEL3 char (10) NOT NULL ,
	NIVEL1 char (8) NOT NULL ,
	NIVEL2 char (2) NOT NULL ,
	DATA datetime NOT NULL ,
	ATR1 float NULL ,
	ATR2 float NULL ,
	ATR3 float NULL ,
	ATR4 float NULL ,
	ATR5 float NULL ,
	ATR6 float NULL ,
        CONSTRAINT PK_NIVEL3_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL1,NIVEL2,NIVEL3,DATA) WITH FILLFACTOR=98)

CREATE INDEX FK_NIVEL3_AC_CP_1 ON NIVEL3_AC_CP (NIVEL3,DATA)
CREATE INDEX FK_NIVEL3_AC_CP_2 ON NIVEL3_AC_CP (NIVEL2,NIVEL3,DATA)

DROP TABLE NIVEL4_AC_CP
CREATE TABLE NIVEL4_AC_CP (
	NIVEL4 char (10) NOT NULL ,
	NIVEL1 char (8) NOT NULL ,
	NIVEL2 char (2) NOT NULL ,
	NIVEL3 char (10) NOT NULL ,
	DATA datetime NOT NULL ,
	ATR1 float NULL ,
	ATR2 float NULL ,
	ATR3 float NULL ,
	ATR4 float NULL ,
	ATR5 float NULL ,
	ATR6 float NULL ,
        CONSTRAINT PK_NIVEL4_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL1,NIVEL2,NIVEL3,NIVEL4,DATA) WITH FILLFACTOR=98)

CREATE INDEX FK_NIVEL4_AC_CP_1 ON NIVEL4_AC_CP (NIVEL4,DATA)
CREATE INDEX FK_NIVEL4_AC_CP_2 ON NIVEL4_AC_CP (NIVEL2,NIVEL3,NIVEL4,DATA)

DROP TABLE NIVEL5_AC_CP
CREATE TABLE NIVEL5_AC_CP (
	NIVEL5 char (2) NOT NULL ,
	NIVEL1 char (8) NOT NULL ,
	NIVEL2 char (2) NOT NULL ,
	NIVEL3 char (10) NOT NULL ,
	NIVEL4 char (10) NOT NULL ,
	DATA datetime NOT NULL ,
	ATR1 float NULL ,
	ATR2 float NULL ,
	ATR3 float NULL ,
	ATR4 float NULL ,
	ATR5 float NULL ,
	ATR6 float NULL ,
        CONSTRAINT PK_NIVEL5_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL1,NIVEL2,NIVEL3,NIVEL4,NIVEL5,DATA) WITH FILLFACTOR=98)

CREATE INDEX FK_NIVEL5_AC_CP_1 ON NIVEL5_AC_CP (NIVEL5,DATA)
CREATE INDEX FK_NIVEL5_AC_CP_2 ON NIVEL5_AC_CP (NIVEL2,NIVEL3,NIVEL4,NIVEL5,DATA)

DROP TABLE NIVEL6_AC_CP
CREATE TABLE NIVEL6_AC_CP (
	NIVEL6 char (3) NOT NULL ,
	NIVEL1 char (8) NOT NULL ,
	NIVEL2 char (2) NOT NULL ,
	NIVEL3 char (10) NOT NULL ,
	NIVEL4 char (10) NOT NULL ,
	NIVEL5 char (2) NOT NULL ,
	DATA datetime NOT NULL ,
     	ATR1 float NULL ,
	ATR2 float NULL ,
	ATR3 float NULL ,
	ATR4 float NULL ,
	ATR5 float NULL ,
	ATR6 float NULL,
        CONSTRAINT PK_NIVEL6_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL1,NIVEL2,NIVEL3,NIVEL4,NIVEL5,NIVEL6,DATA) WITH FILLFACTOR=98)

CREATE INDEX FK_NIVEL6_AC_CP_1 ON NIVEL6_AC_CP (NIVEL6,DATA)
CREATE INDEX FK_NIVEL6_AC_CP_2 ON NIVEL6_AC_CP (NIVEL2,NIVEL3,NIVEL4,NIVEL5,NIVEL6,DATA)

DROP TABLE NIVEL7_AC_CP
CREATE TABLE NIVEL7_AC_CP (
	NIVEL7 char (5) NOT NULL ,
	NIVEL1 char (8) NOT NULL ,
	NIVEL2 char (2) NOT NULL ,
	NIVEL3 char (10) NOT NULL ,
	NIVEL4 char (10) NOT NULL  ,
	NIVEL5 char (2) NOT NULL ,
        NIVEL6 char (3) NOT NULL ,
	DATA datetime NOT NULL ,
        TIPO CHAR(1) NOT NULL,
     	ATR1 float NULL ,
	ATR2 float NULL ,
	ATR3 float NULL ,
	ATR4 float NULL ,
	ATR5 float NULL ,
	ATR6 float NULL
        CONSTRAINT PK_NIVEL7_AC_CP PRIMARY KEY NONCLUSTERED (NIVEL1,NIVEL2,NIVEL3,NIVEL4,NIVEL5,NIVEL6,NIVEL7,DATA,TIPO) WITH FILLFACTOR=98)

CREATE INDEX FK_NIVEL7_AC_CP_1 ON NIVEL7_AC_CP (NIVEL7,DATA)
CREATE INDEX FK_NIVEL7_AC_CP_2 ON NIVEL7_AC_CP (NIVEL2,NIVEL3,NIVEL4,NIVEL5,NIVEL6,NIVEL7,DATA)



