CREATE TABLE PARAMWEBSERVICE
(
  IDAMBIENTEWS 		VARCHAR2(12)
	CONSTRAINT CKC_IDAMBIENTEWS CHECK (IDAMBIENTEWS IS NULL OR (IDAMBIENTEWS IN ('PRODUCAO','HOMOLOGACAO'))),
  IDSITUACAOWS 		VARCHAR2(1)
	CONSTRAINT CKC_IDSITUACAOWS CHECK (IDSITUACAOWS IS NULL OR (IDSITUACAOWS IN ('A','I'))),
   DSUSUARIOWS  		VARCHAR2(80),
   NRPORTAWEBSERV NUMBER,
   DSURLWEBSERV VARCHAR2(100),
   CDOPERADORWS VARCHAR2(12),
   DSSERVPROXY  VARCHAR2(15),
   CDSENHPROXY  VARCHAR2(60),
   IDUTILPROXY 		VARCHAR2(1)
	CONSTRAINT CKC_IDUTILPROXY CHECK (IDUTILPROXY IS NULL OR (IDUTILPROXY IN ('S','N'))),
   NRORG NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO VARCHAR2(1) DEFAULT 'S' NOT NULL
   CONSTRAINT CKC_IDATIVO_PARAMWEBSERVICE CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO DATE NULL,
   DTULTATU DATE NULL,
   NRORGINCLUSAO NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERINCLUSAO VARCHAR2(12) DEFAULT '1' NULL,
   NRORGULTATU NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU VARCHAR2(12) DEFAULT '1',
	CONSTRAINT PK_PARAMWEBSERVICE PRIMARY KEY (IDAMBIENTEWS)
)
/

ALTER TABLE PARAMWEBSERVICE
    ADD CONSTRAINT FKFIS00729PARAMWEBSERVICE FOREIGN KEY  (CDOPERADORWS)
       REFERENCES OPERADOR (CDOPERADOR)
/

DECLARE
V_COUNT NUMBER;
V_COMANDO VARCHAR2(4000);
BEGIN
   BEGIN
      SELECT COUNT(1) INTO V_COUNT FROM PARAMWEBSERVICE;
   EXCEPTION
     WHEN OTHERS THEN
        V_COUNT := 0;
   END;

   IF V_COUNT = 0 THEN
      V_COMANDO := 'INSERT INTO PARAMWEBSERVICE (IDAMBIENTEWS, IDSITUACAOWS, DSUSUARIOWS) VALUES('||CHR(39)||'PRODUCAO'||CHR(39)||','||CHR(39)||'I'||CHR(39)||',NULL)';
      EXECUTE IMMEDIATE V_COMANDO;
      V_COMANDO := 'INSERT INTO PARAMWEBSERVICE (IDAMBIENTEWS, IDSITUACAOWS, DSUSUARIOWS) VALUES('||CHR(39)||'HOMOLOGACAO'||CHR(39)||','||CHR(39)||'A'||CHR(39)||',NULL)';
      EXECUTE IMMEDIATE V_COMANDO;
   END IF;
END;
/

CREATE TABLE SERVWEBSERVICE
(
  IDAMBIENTEWS  		VARCHAR2(12),
  CDSERVWEBSERV 	 VARCHAR2(3),
  NMSERVWEBSERV 	 VARCHAR2(60),
  NMOBJBDWEBSERV  VARCHAR2(100),
  IDSERVATIVWS  		VARCHAR2(1)
	CONSTRAINT CKC_IDSERVATIVWS CHECK (IDSERVATIVWS IS NULL OR (IDSERVATIVWS IN ('A','I'))),
  IDTIPINTEGR   		VARCHAR2(7)
	CONSTRAINT CKC_IDTIPINTEGR CHECK (IDTIPINTEGR IS NULL OR (IDTIPINTEGR IN ('ENVIAR','RECEBER'))),
   NRPORTAWS       NUMBER,
   DSURLWS       		VARCHAR2(100),
   DSMETODOWS    		VARCHAR2(100),
   DSPROGRAMAWS  	 VARCHAR2(100),
   IDPERIODOWS        VARCHAR2(7)
	CONSTRAINT CKC_IDPERIODOWS CHECK (IDPERIODOWS IS NULL OR (IDPERIODOWS IN ('DIARIO','SEMANAL','MENSAL','REGULAR','UNICO'))),
   CDDIAMES    NUMBER,
   CDDIASEMANA VARCHAR2(3)
 CONSTRAINT CKC_CDDIASEMANA CHECK (CDDIASEMANA IS NULL OR (CDDIASEMANA IN ('DOM','SEG','TER','QUA','QUI','SEX','SAB'))),
   CDINTERVALO NUMBER,
   IDTIPINTERVALO VARCHAR2(6)
 CONSTRAINT CKC_IDTIPINTERVALO CHECK (IDTIPINTERVALO IS NULL OR (IDTIPINTERVALO IN ('HORA','MINUTO'))),
   HRINICIO    VARCHAR2(5),
   DTINICIO    DATE,
   NRORG               NUMBER  			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO           VARCHAR2(1)      	DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_SERVWEBSERVICE  CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO        DATE                   NULL,
   DTULTATU          DATE                   NULL,
   NRORGINCLUSAO     NUMBER  DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg')  NULL,
   CDOPERINCLUSAO    VARCHAR2(12)   		DEFAULT '1' NULL,
   NRORGULTATU       NUMBER  			DEFAULT  SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU      VARCHAR2(12)   		DEFAULT '1',
	CONSTRAINT PK_SERVWEBSERVICE PRIMARY KEY (IDAMBIENTEWS, CDSERVWEBSERV)
)
/

CREATE INDEX FK_FIS00726 ON SERVWEBSERVICE (IDAMBIENTEWS ASC)
/

ALTER TABLE SERVWEBSERVICE
    ADD CONSTRAINT FKFIS00726PARAMWEBSERVICE FOREIGN KEY  (IDAMBIENTEWS)
       REFERENCES PARAMWEBSERVICE (IDAMBIENTEWS) ON DELETE CASCADE
/

DECLARE
V_COUNT       NUMBER;
V_COMANDO     VARCHAR2(4000);
V_AMBIENTE    VARCHAR2(12);
V_NMSERVICOWS VARCHAR2(60);
V_STATUS      VARCHAR2(1);
V_CODIGO      VARCHAR2(3);
V_OBJETO      VARCHAR2(250);
BEGIN
   BEGIN
      SELECT COUNT(1) INTO V_COUNT FROM SERVWEBSERVICE;
   EXCEPTION
     WHEN OTHERS THEN
        V_COUNT := 0;
   END;

   IF V_COUNT = 0 THEN
      FOR I IN 1..7 LOOP
         CASE WHEN I = 1 THEN BEGIN V_NMSERVICOWS := 'Autoriza'||CHR(231)||CHR(227)||'o de Fornecimento(AF)';
                                    V_OBJETO := 'INTEGR_EXPORTA_AF';
                               END;
              WHEN I = 2 THEN BEGIN V_NMSERVICOWS := 'Notas Fiscais de Entrada(Entrada de Mercadorias)';
                                    V_OBJETO := 'INTEGR_IMPORTA_NFE';
                              END;
              WHEN I = 3 THEN BEGIN V_NMSERVICOWS := 'Notas Fiscais de Devolu'||CHR(231)||CHR(227)||'o';
                                    V_OBJETO := 'INTEGR_IMPORTA_DEVOLUCAO';
                              END;
              WHEN I = 4 THEN BEGIN V_NMSERVICOWS := 'Exclus'||CHR(227)||'o de Notas Fiscais de Mercadorias';
                                    V_OBJETO := 'INTEGR_EXCLUI_NFE';
                              END;
              WHEN I = 5 THEN BEGIN V_NMSERVICOWS := 'Cadastro de Produtos';
                                    V_OBJETO := '';
                              END;
              WHEN I = 6 THEN BEGIN V_NMSERVICOWS := 'Cadastro de Clientes';
                                    V_OBJETO := '';
                              END;
              WHEN I = 7 THEN BEGIN V_NMSERVICOWS := 'Cadastro de Fornecedores';
                                    V_OBJETO := '';
                              END;
         END CASE;

         V_CODIGO := '00'||I;
         V_STATUS := 'I';

         V_AMBIENTE := 'PRODUCAO';
         V_COMANDO := 'INSERT INTO SERVWEBSERVICE (IDAMBIENTEWS, CDSERVWEBSERV, NMSERVWEBSERV, NMOBJBDWEBSERV, IDSERVATIVWS) VALUES ('||CHR(39)||V_AMBIENTE||CHR(39)||', ' ||CHR(39)||V_CODIGO||CHR(39)||', '||CHR(39)||V_NMSERVICOWS||CHR(39)||', '||CHR(39)||V_OBJETO||CHR(39)||', '||CHR(39)||V_STATUS||CHR(39)||')';
         --dbms_output.put_line('V_COMANDO1 = '||V_COMANDO);
         EXECUTE IMMEDIATE V_COMANDO;

         V_AMBIENTE := 'HOMOLOGACAO';
         V_COMANDO := 'INSERT INTO SERVWEBSERVICE (IDAMBIENTEWS, CDSERVWEBSERV, NMSERVWEBSERV, NMOBJBDWEBSERV, IDSERVATIVWS) VALUES ('||CHR(39)||V_AMBIENTE||CHR(39)||', ' ||CHR(39)||V_CODIGO||CHR(39)||', '||CHR(39)||V_NMSERVICOWS||CHR(39)||', '||CHR(39)||V_OBJETO||CHR(39)||', '||CHR(39)||V_STATUS||CHR(39)||')';
         --dbms_output.put_line('V_COMANDO2 = '||V_COMANDO);
         EXECUTE IMMEDIATE V_COMANDO;
      END LOOP;
   END IF;
   COMMIT;
END;
/

CREATE TABLE USR_LOG_WEBSERVICE
(
   SEQLOG      		NUMBER,
   DSMETODO    		VARCHAR2(20),
   IDACAO      		VARCHAR2(10)
      CONSTRAINT CKC_IDACAO_USR_LOG_WS CHECK (IDACAO IS NULL OR (IDACAO IN ('INCLUSAO','EXCLUSAO','ALTERACAO','REQUISICAO'))),
   IDEXIBE     		VARCHAR2(5),
   IDTIPINTEGR 		VARCHAR2(10)
      CONSTRAINT CKC_IDTIPINTEGR_USR_LOG_WS CHECK (IDTIPINTEGR IS NULL OR (IDTIPINTEGR IN ('ENVIAR','RECEBER','LANCAMENTO'))),
   XMLLOG      		CLOB,
   OBSLOG      		VARCHAR2(4000),
   DSLOGINT    		VARCHAR2(4000),
   CDFORNECED  		VARCHAR2(21),
   CDCLIENTE   		VARCHAR2(21),
   CDPRODUTO   		VARCHAR2(10),
   CDFILIAL    		VARCHAR2(04),
   NRLANCTONF  		VARCHAR2(10),
   NRSEQITPROD 		VARCHAR2(04),
   NRLANCESTQ  		VARCHAR2(10),
   NRSEQUITEM  		VARCHAR2(4),
   NRAUTOFORN  		VARCHAR2(10),
   CDFILMOVI   		VARCHAR2(4),
   NRORG NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO VARCHAR2(1) DEFAULT 'S' NOT NULL
   CONSTRAINT CKC_IDATIVO_USR_LOG_WEBSERVICE CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO DATE NULL,
   DTULTATU DATE NULL,
   NRORGINCLUSAO NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERINCLUSAO VARCHAR2(12) DEFAULT '1' NULL,
   NRORGULTATU NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU VARCHAR2(12) DEFAULT '1',
	CONSTRAINT PK_USR_LOG_WEBSERVICE PRIMARY KEY (SEQLOG)
)
/

CREATE TABLE USR_LOG_WEBSERVICE_DRILL
(
   SEQLOG      		NUMBER,
   DSMETODO    		VARCHAR2(20),
   IDACAO      		VARCHAR2(10),
   XMLLOG      		CLOB,
   OBSLOG      		VARCHAR2(4000),
   DSLOGINT    		VARCHAR2(4000),
   CDPRODUTO   		VARCHAR2(10),
   CDFILIAL    		VARCHAR2(4),
   NRLANCTONF  		VARCHAR2(10),
   NRSEQITPROD 		VARCHAR2(4),
   NRLANCESTQ  		VARCHAR2(10),
   NRSEQUITEM  		VARCHAR2(4),
   NRAUTOFORN  		VARCHAR2(10),
   CDFILMOVI   		VARCHAR2(4),
      NRORG NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO VARCHAR2(1) DEFAULT 'S' NOT NULL
   CONSTRAINT CKC_IDATIVO_USR_LOG_WEBSERV_D CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO DATE NULL,
   DTULTATU DATE NULL,
   NRORGINCLUSAO NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERINCLUSAO VARCHAR2(12) DEFAULT '1' NULL,
   NRORGULTATU NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU VARCHAR2(12) DEFAULT '1'
)
/

CREATE INDEX FK_FIS00728 ON USR_LOG_WEBSERVICE_DRILL (SEQLOG ASC)
/
ALTER TABLE USR_LOG_WEBSERVICE_DRILL
    ADD CONSTRAINT FKFIS00728USR_LOG_WS_DRILL FOREIGN KEY  (SEQLOG)
       REFERENCES USR_LOG_WEBSERVICE (SEQLOG) ON DELETE CASCADE
/

CREATE INDEX FKEST00625USR_ENTR_MERC ON USR_ENTR_MERC (NRNOTAFISCAL ASC, NRINSJURFORN ASC, CDSERIE ASC, DTEMISSAO ASC)
/

ALTER TABLE USR_ITEM_ENTR_MERC ADD IDIMPORTADO             VARCHAR2(1)       DEFAULT 'N' NULL
        CONSTRAINT CKC_USR_IE_IDIMPORTADO CHECK (IDIMPORTADO IS NULL OR (IDIMPORTADO IN ('S','N')))
/

CREATE INDEX FKEST00626USR_ITEM_ENTR_MERC ON USR_ITEM_ENTR_MERC (NRNOTAFISCAL ASC, NRINSJURFORN ASC, CDSERIE ASC, DTEMISSAO ASC)
/