
/*------ ATD 396677 ------*/
DECLARE v_count NUMBER(1) := 0; 

BEGIN 

SELECT 1 INTO v_count FROM USER_TAB_COLUMNS WHERE Upper(table_name) = 'PediCliComplemento' AND Upper(column_name) = 'ViaImpressao'; 

EXCEPTION 

WHEN NO_DATA_FOUND THEN 

EXECUTE IMMEDIATE 'ALTER TABLE PediCliComplemento ADD ViaImpressao integer default 0 not null '; 

END;






/*------ ATD 396863 ------*/

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'TIPOFRETE';
   IF v_count = 0 THEN
        BEGIN
          EXECUTE IMMEDIATE 'CREATE TABLE TIPOFRETE(
          ID INT NOT NULL,
          DESCRICAO CHAR(254) NOT NULL,
          PRAZOENTREGAEMHORAS INT NOT NULL,
          HORARIOCORTE DATE NOT NULL,
          INATIVO INT NOT NULL,
          ATUALIZADOEM DATE,
          RUSUARIO INT)';

          EXECUTE IMMEDIATE 'ALTER TABLE TIPOFRETE ADD CONSTRAINT PK_TIPOFRETE PRIMARY KEY (ID)'; 
          EXECUTE IMMEDIATE 'CREATE SEQUENCE TIPOFRETE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
          EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER TIPOFRETE_I
          BEFORE INSERT ON TIPOFRETE FOR EACH ROW
          WHEN (new.ID IS NULL)
              BEGIN
                  SELECT TIPOFRETE_SEQ.NEXTVAL INTO :new.ID FROM dual;
              END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'FRETEPESOOUCUBAGEM';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE FretePesoOuCubagem(
           ID INT NOT NULL,
           IDTIPOFRETE INT NOT NULL,
           METODOCALCULO INT NOT NULL,
           INATIVO INT,
           ATUALIZADOEM DATE,
           RUSUARIO INT)';

           EXECUTE IMMEDIATE 'ALTER TABLE FretePesoOuCubagem ADD CONSTRAINT PK_FretePesoOuCubagem PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE FretePesoOuCubagem_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER FretePesoOuCubagem_I
           BEFORE INSERT ON FretePesoOuCubagem FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT FretePesoOuCubagem_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'FRETEPESOOUCUBAGEM_PORFAIXA';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE FretePesoOuCubagem_PorFaixa(
           ID INT NOT NULL,
           IDFRETEPESOOUCUBAGEM INT NOT NULL,
           VALORDE DECIMAL(15,3) NOT NULL, 
           VALORATE DECIMAL(15,3) NOT NULL,
           VALORFRETEUNITARIO DECIMAL(15,2) NOT NULL)';

           EXECUTE IMMEDIATE 'ALTER TABLE FretePesoOuCubagem_PorFaixa ADD CONSTRAINT PK_FretePesoOuCubagem_PorFaixa PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE FretePesoOuCubagem_Faixa_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER FretePesoOuCubagem_PorFaixa_I
           BEFORE INSERT ON FretePesoOuCubagem_PorFaixa FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT FretePesoOuCubagem_Faixa_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN 
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'FRETEPESOOUCUBAGEM_ROTA';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE FretePesoOuCubagem_Rota(
           ID INT NOT NULL,
           IDFRETEPESOOUCUBAGEM INT NOT NULL,
           CODIGOROTEIRO CHAR(2) NOT NULL)';

           EXECUTE IMMEDIATE 'ALTER TABLE FretePesoOuCubagem_Rota ADD CONSTRAINT PK_FretePesoOuCubagem_Rota PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE FretePesoOuCubagem_Rota_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER FretePesoOuCubagem_Rota_I
           BEFORE INSERT ON FretePesoOuCubagem_Rota FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT FretePesoOuCubagem_Rota_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'TIPOFRETE_PRODUTO';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE TipoFrete_Produto(
           ID INT NOT NULL,
           CODIGOPRODUTO CHAR(5) NOT NULL,
           IDTIPOFRETE INT NOT NULL)';

           EXECUTE IMMEDIATE 'ALTER TABLE TipoFrete_Produto ADD CONSTRAINT PK_TipoFrete_Produto PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE TipoFrete_Produto_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER TipoFrete_Produto_I
           BEFORE INSERT ON TipoFrete_Produto FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT TipoFrete_Produto_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'FRETEESPECIAL';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE FreteEspecial(
           ID INT NOT NULL,
           DESCRICAO VARCHAR(254) NOT NULL,
           VALIDADEINICIAL DATE NOT NULL,
           VALIDADEFINAL DATE NOT NULL,
           CLIENTEMAIS INT NOT NULL,
           VALORMINIMOISENCAOFRETE DECIMAL(15,2) NOT NULL,
           PRAZOENTREGAEMHORAS INT NOT NULL,
           HORARIOCORTE CHAR(4) NOT NULL,
           INATIVO INT NOT NULL,
           ATUALIZADOEM DATE,
           RUSUARIO INT )';

           EXECUTE IMMEDIATE 'ALTER TABLE FreteEspecial ADD CONSTRAINT PK_FreteEspecial PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE FreteEspecial_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER FreteEspecial_I
           BEFORE INSERT ON FreteEspecial FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT FreteEspecial_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'FRETEESPECIAL_ROTA';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE FreteEspecial_Rota(
           ID INT NOT NULL,
           IDFRETEESPECIAL INT NOT NULL,
           CODIGOROTEIRO CHAR(2) NOT NULL)';

           EXECUTE IMMEDIATE 'ALTER TABLE FreteEspecial_Rota ADD CONSTRAINT PK_FreteEspecial_Rota PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE FreteEspecial_Rota_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER FreteEspecial_Rota_I
           BEFORE INSERT ON FreteEspecial_Rota FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT FreteEspecial_Rota_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'FRETEESPECIAL_FILIAL';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE FreteEspecial_Filial(
           ID INT NOT NULL,
           IDFRETEESPECIAL INT NOT NULL,
           FILIAL CHAR(2) NOT NULL)';

           EXECUTE IMMEDIATE 'ALTER TABLE FreteEspecial_Filial ADD CONSTRAINT PK_FreteEspecial_Filial PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE FreteEspecial_Filial_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER FreteEspecial_Filial_I
           BEFORE INSERT ON FreteEspecial_Filial FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT FreteEspecial_Filial_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'FRETEESPECIAL_PRODUTO';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE FreteEspecial_Produto(
           ID INT NOT NULL,
           IDFRETEESPECIAL INT NOT NULL,
           CODIGOPRODUTO CHAR(5) NOT NULL,
           INATIVO INT NOT NULL)';

           EXECUTE IMMEDIATE 'ALTER TABLE FreteEspecial_Produto ADD CONSTRAINT PK_FreteEspecial_Produto PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE FreteEspecial_Produto_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER FreteEspecial_Produto_I
           BEFORE INSERT ON FreteEspecial_Produto FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT FreteEspecial_Produto_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'CAPACIDADECARGA_TIPOFRETE';
   IF v_count = 0 THEN
        BEGIN
           EXECUTE IMMEDIATE 'CREATE TABLE CapacidadeCarga_TipoFrete(
           ID INT NOT NULL,
           IDCAPACIDADECARGA INT NOT NULL,
           IDTIPOFRETE INT NOT NULL)';

           EXECUTE IMMEDIATE 'ALTER TABLE CapacidadeCarga_TipoFrete ADD CONSTRAINT PK_CapacidadeCarga_TipoFrete PRIMARY KEY (ID)'; 
           EXECUTE IMMEDIATE 'CREATE SEQUENCE CapacidadeCarga_TipoFrete_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
           EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER CapacidadeCarga_TipoFrete_I
           BEFORE INSERT ON CapacidadeCarga_TipoFrete FOR EACH ROW
           WHEN (new.ID IS NULL)
               BEGIN
                   SELECT CapacidadeCarga_TipoFrete_SEQ.NEXTVAL INTO :new.ID FROM dual;
               END;';
        END;
   END IF;
END;


DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'ITEMCLICOMPLEMENTO' and UPPER(column_name) = 'IDFRETEPESOOUCUBAGEM';
   IF v_count = 0 THEN
      EXECUTE IMMEDIATE 'ALTER TABLE ITEMCLICOMPLEMENTO ADD IDFretePesoOuCubagem INT '; 
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'ITEMCLICOMPLEMENTO' and UPPER(column_name) = 'IDFRETEESPECIAL';
   IF v_count = 0 THEN
      EXECUTE IMMEDIATE 'ALTER TABLE ITEMCLICOMPLEMENTO ADD IDFreteEspecial INT '; 
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'ITEMCLICOMPLEMENTO' and UPPER(column_name) = 'VALORFRETEUNITARIO';
   IF v_count = 0 THEN
      EXECUTE IMMEDIATE 'ALTER TABLE ITEMCLICOMPLEMENTO ADD ValorFreteUnitario NUMERIC(15,2) '; 
   END IF;
END;

DECLARE v_count NUMBER:=0;
BEGIN
   SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'COMPLEMENTOPRODUTO' and Upper(column_name) = 'METODOCALCULOFRETE';
   IF v_count = 0 THEN
      EXECUTE IMMEDIATE 'ALTER TABLE COMPLEMENTOPRODUTO ADD MetodoCalculoFrete INT NOT NULL '; 
   END IF;
END;


--Oracle
MERGE into item using dual on (item.oid = 40127 ) when not matched then
insert (rescopo, criadopor, atualizadopor, codigo, criadoem, atualizadoem , observacao, nome, cid, oid, excluido) values (7           ,1,1,'','01/01/01','01/01/01','','Libera orçamento sem informar o tipo de frete',18,40127,0);

MERGE into categoria using dual on (categoria.oid =40127 ) when not matched then
insert (rsuper, nomeinterno, oid) values (36419,'TIPO_LIBERACAO_LIBERA_ORCAMENTO_SEM_INFORMAR_TIPO_FRETE',40127);

--Oracle
MERGE into item using dual on (item.oid = 40138 ) when not matched then
insert (rescopo, criadopor, atualizadopor, codigo, criadoem, atualizadoem , observacao, nome, cid, oid, excluido) values (7           ,1,1,'','01/01/01','01/01/01','Define se a filial utiliza o recurso de fretes especial para entrega. Valores Válidos: S ou N','Trabalha com frete Especial para entrega?',18,40138,0);
--Oracle
MERGE into item using dual on (item.oid = 40135 ) when not matched then
insert (rescopo, criadopor, atualizadopor, codigo, criadoem, atualizadoem , observacao, nome, cid, oid, excluido) values (7           ,1,1,'','01/01/01','01/01/01','Define se a filial utiliza o recurso de emissão automática de mapa de carga após a emissão da nota fiscal. Valores Válidos: S ou N','Trabalha com mapa de carga automático após a emissão da nota?',18,40135,0);
--Oracle
MERGE into item using dual on (item.oid = 40132 ) when not matched then
insert (rescopo, criadopor, atualizadopor, codigo, criadoem, atualizadoem , observacao, nome, cid, oid, excluido) values (7           ,1,1,'','01/01/01','01/01/01','Define se a filial utiliza o recurso de múltiplos tipos de fretes para entrega no orçamento. Valores Válidos: S ou N','Trabalha com múltiplos tipos de fretes para entregas?',18,40132,0);
--Oracle
MERGE into item using dual on (item.oid = 40129 ) when not matched then
insert (rescopo, criadopor, atualizadopor, codigo, criadoem, atualizadoem , observacao, nome, cid, oid, excluido) values (7           ,1,1,'','01/01/01','01/01/01','Define se a filial utiliza o recurso de frete por faixa de peso e cubagem. Valores Válidos: S ou N','Trabalha com frete por faixa de peso e cubagem?',18,40129,0);

MERGE into categoria using dual on (categoria.oid =40138 ) when not matched then
insert (rsuper, nomeinterno, oid) values (11767,'TRABALHA_FRETE_ESPECIAL_ENTREGA',40138);
MERGE into categoria using dual on (categoria.oid =40135 ) when not matched then
insert (rsuper, nomeinterno, oid) values (11767,'TRABALHA_MAPA_CARGA_AUTOMATICO_APOS_EMISSAO_NF',40135);
MERGE into categoria using dual on (categoria.oid =40132 ) when not matched then
insert (rsuper, nomeinterno, oid) values (11767,'TRABALHA_MULTIPLOS_TIPOS_FRETES_ENTREGAS',40132);
MERGE into categoria using dual on (categoria.oid =40129 ) when not matched then
insert (rsuper, nomeinterno, oid) values (11767,'TRABALHA_FRETE_POR_FAIXA_PESO_CUBAGEM',40129);

MERGE into dadoadicional using dual on (oid = 40137) when not matched then insert ( RCLASSECONCRETA,RAPLICATIVO,RDOMINIO,RLISTA,RTIPO,RITEM,RESTRICAO,VISIVEL,MASCARA,PADRAO,MAXIMO,MINIMO,VALOR,OID,DESCRICAOEXTRA,INFORMACAOMU) values (7,1884,16024,7,40138,10128,0,0,'A','',0,0,'',40137,'','PA 396863');
MERGE into dadoadicional using dual on (oid = 40134) when not matched then insert ( RCLASSECONCRETA,RAPLICATIVO,RDOMINIO,RLISTA,RTIPO,RITEM,RESTRICAO,VISIVEL,MASCARA,PADRAO,MAXIMO,MINIMO,VALOR,OID,DESCRICAOEXTRA,INFORMACAOMU) values (7,1884,16024,7,40135,10128,0,0,'A','',0,0,'',40134,'','PA 396863');
MERGE into dadoadicional using dual on (oid = 40131) when not matched then insert ( RCLASSECONCRETA,RAPLICATIVO,RDOMINIO,RLISTA,RTIPO,RITEM,RESTRICAO,VISIVEL,MASCARA,PADRAO,MAXIMO,MINIMO,VALOR,OID,DESCRICAOEXTRA,INFORMACAOMU) values (7,1884,16024,7,40132,10128,0,0,'A','',0,0,'',40131,'','PA 396863');
MERGE into dadoadicional using dual on (oid = 40128) when not matched then insert ( RCLASSECONCRETA,RAPLICATIVO,RDOMINIO,RLISTA,RTIPO,RITEM,RESTRICAO,VISIVEL,MASCARA,PADRAO,MAXIMO,MINIMO,VALOR,OID,DESCRICAOEXTRA,INFORMACAOMU) values (7,1884,16024,7,40129,10128,0,0,'A','',0,0,'',40128,'','PA 396863');

MERGE into aditivo using dual on (oid = 40139 ) when not matched then
insert (RITEM,RDEFINICAO,SVALOR,OID) values (1    ,40137,'',40139);
MERGE into aditivo using dual on (oid = 40136 ) when not matched then
insert (RITEM,RDEFINICAO,SVALOR,OID) values (1    ,40134,'',40136);
MERGE into aditivo using dual on (oid = 40133 ) when not matched then
insert (RITEM,RDEFINICAO,SVALOR,OID) values (1    ,40131,'',40133);
MERGE into aditivo using dual on (oid = 40130 ) when not matched then
insert (RITEM,RDEFINICAO,SVALOR,OID) values (1    ,40128,'',40130);
