CREATE TABLE INFORMATIVOECOMMERCE
(
   DSTITULOINF      VARCHAR2(100)         NOT NULL,
   TXCONTEUDOINF     CLOB                 NOT NULL,
   IDNOTIFICACAO     VARCHAR2(1)         NULL, 
   DTEXPIRAINFO      DATE                 NOT NULL,
   DTINFORMARIVI       DATE                 NOT NULL,
   NRORG                NUMBER DEFAULT SYS_CONTEXT('NRORG_CTX','NRORG') NOT NULL,
   IDATIVO            VARCHAR2(1) DEFAULT 'S' NOT NULL,
      CONSTRAINT CKC_IDATIVO_INFOECOMMERCE 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'
)
/
ALTER TABLE INFORMATIVOECOMMERCE ADD NRINFECOMMERCE NUMBER
/
ALTER TABLE INFORMATIVOECOMMERCE ADD CONSTRAINT PK_INFORMATIVOECOMMERCE PRIMARY KEY (NRINFECOMMERCE) 
/ 
CREATE TABLE INFORMATIVOECOMOPER (
    NRINFECOMOPER  	  NUMBER 		  NOT NULL,
    NRINFECOMMERCE 	  NUMBER 		  NOT NULL ,
    CDOPERADOR     	  VARCHAR2(12) 	  NOT NULL,
    NRORG             NUMBER 	      DEFAULT SYS_CONTEXT('NRORG_CTX','NRORG') 		NOT NULL,
    IDATIVO           VARCHAR2(1)     DEFAULT 'S'									NOT NULL,
    DTINCLUSAO        DATE            DEFAULT SYSDATE,
    DTULTATU          DATE            DEFAULT SYSDATE,
    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 CKC_IDATIVO_INFORMATECOMOPER CHECK (IDATIVO IN ('S','N')),
    CONSTRAINT INFORMATIVOECOMOPER_PK  PRIMARY KEY(NRINFECOMOPER),
    CONSTRAINT INFORECOMOPER_INFORM_FK FOREIGN KEY (NRINFECOMMERCE) REFERENCES INFORMATIVOECOMMERCE (NRINFECOMMERCE),
    CONSTRAINT INFORECOMOPER_OPER_FK   FOREIGN KEY (CDOPERADOR) REFERENCES OPERADOR (CDOPERADOR)
)
/
CREATE TABLE INFORMATIVOECOMSILE (
    ID               NUMBER        NOT NULL,
    DSNOME           VARCHAR2(500) NOT NULL,
    QTDIAS           NUMBER        NOT NULL,
    CDPUBLICOALVO    VARCHAR2(5)   NOT NULL,
    IDATIVO          VARCHAR2(1)   DEFAULT 'S' NOT NULL,
    NRORG            NUMBER NOT NULL,
    DTINCLUSAO       DATE          DEFAULT SYSDATE,
    DTULTATU         DATE          DEFAULT SYSDATE,
    NRORGINCLUSAO    NUMBER,
    CDOPERINCLUSAO   VARCHAR2(12),
    NRORGULTATU      NUMBER,
    CDOPERULTATU     VARCHAR2(12), 
		CONSTRAINT PK_INFORMATIVOECOMSILE PRIMARY KEY(ID),
		CONSTRAINT CKC_IDATIVO_INFORECOMSILE CHECK (IDATIVO IN ('S','N'))
)
/

ALTER TABLE INFORMATIVOECOMMERCE ADD CDPUBLICOALVO VARCHAR2(1)
/
ALTER TABLE INFORMATIVOECOMMERCE ADD MARKISREAD VARCHAR2(1)
/

CREATE TABLE INFORMATIVOECOMSILEOPER  (
    ID                  NUMBER        NOT NULL,
    CDOPERADOR          VARCHAR2(12)  NOT NULL,
    CDPUBLICOALVO       VARCHAR2(1)   NOT NULL,
    DTINFOSILE          DATE          NOT NULL,
    IDATIVO             VARCHAR2(1)   DEFAULT 'S' NOT NULL,
    NRORG               NUMBER        NOT NULL,
    DTINCLUSAO          DATE          DEFAULT SYSDATE,
    DTULTATU            DATE          DEFAULT SYSDATE,
    NRORGINCLUSAO       NUMBER      ,
    CDOPERINCLUSAO      VARCHAR2(12),
    NRORGULTATU         NUMBER      ,
    CDOPERULTATU        VARCHAR2(12),
    MARKISREAD          VARCHAR2(1),
		CONSTRAINT PK_INFORMATIVOECOMSILEOPER PRIMARY KEY(ID),
		CONSTRAINT CKC_IDATIVO_INFORECOMSILEOPER CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT FK_INFORMATIVOECOMSILEOPER FOREIGN KEY (CDOPERADOR) REFERENCES OPERADOR(CDOPERADOR)
)
/

ALTER TABLE INFORMATIVOECOMOPER DROP CONSTRAINT INFORECOMOPER_INFORM_FK
/

ALTER TABLE INFORMATIVOECOMMERCE ADD LINKDIRIMAGE CLOB
/
ALTER TABLE INFORMATIVOECOMSILEOPER ADD IDINFOECOMSILE NUMBER
/
CREATE TABLE INFORMATIVO_PERFILACESSO(
    CDINFORPERFACESS    NUMBER        NOT NULL,
    NRPERFILACESSO      NUMBER        NOT NULL,
    NRINFECOMMERCE      NUMBER        NOT NULL, 	
    NRORG             NUMBER 	      DEFAULT SYS_CONTEXT('NRORG_CTX','NRORG') 		NOT NULL,
    IDATIVO           VARCHAR2(1)     DEFAULT 'S'					NOT NULL,
    DTINCLUSAO        DATE            DEFAULT SYSDATE,
    DTULTATU          DATE            DEFAULT SYSDATE,
    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_INFORMATIVO_PERFILACESSO PRIMARY KEY(CDINFORPERFACESS),
		CONSTRAINT CKC_IDATIVO_INFOR_PERFACESS CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT FK_INFORMATIVO_PERFILACESSO FOREIGN KEY (NRPERFILACESSO) REFERENCES PERFILACESSO(NRPERFILACESSO),
		CONSTRAINT FK_INFORMATIVO_INFECOMMERCE FOREIGN KEY (NRINFECOMMERCE) REFERENCES INFORMATIVOECOMMERCE(NRINFECOMMERCE)
)
/
CREATE TABLE INFORMATIVO_PRODUTO(
    CDINFORPRODUTO      NUMBER        NOT NULL,
    NRPRODUTO           NUMBER        NOT NULL,
    NRINFECOMMERCE      NUMBER        NOT NULL, 	
    NRORG             NUMBER 	      DEFAULT SYS_CONTEXT('NRORG_CTX','NRORG') 		NOT NULL,
    IDATIVO           VARCHAR2(1)     DEFAULT 'S'					NOT NULL,
    DTINCLUSAO        DATE            DEFAULT SYSDATE,
    DTULTATU          DATE            DEFAULT SYSDATE,
    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_INFORMATIVO_PRODUTO PRIMARY KEY(CDINFORPRODUTO),
		CONSTRAINT CKC_IDATIVO_INFOR_PRODUTO CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT FK_INFORMATIVO_PRODUTO FOREIGN KEY (NRPRODUTO) REFERENCES OM_PRODUTO(ID),
		CONSTRAINT FK_PRODUTO_INFECOMMERCE FOREIGN KEY (NRINFECOMMERCE) REFERENCES INFORMATIVOECOMMERCE(NRINFECOMMERCE)
)
/
