CREATE TABLE PRODDESCOL (
	CDFILIALOL VARCHAR2(4),
	CDPRODUTOOL VARCHAR2(10),
	VRUNITITEMOL FLOAT,
	NRORG             NUMBER        DEFAULT         SYS_CONTEXT('nrorg_ctx','nrorg')  NOT NULL,
	IDATIVO           VARCHAR2(1)       DEFAULT 'S'                     NOT NULL
			CONSTRAINT CKC_IDATIVO_PRODDESCOL 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 LOGSAIDAOL ADD DTOPERACAO            DATE
/
ALTER TABLE LOGSAIDAOL ADD DSRDMUSUOPER          VARCHAR2(50)
/
ALTER TABLE LOGSAIDAOL ADD DSENDUSUOPER          VARCHAR2(50)
/
ALTER TABLE LOGSAIDAOL ADD DSNMUSUOPER           VARCHAR2(50)
/
ALTER TABLE LOGSAIDAOL ADD DSMETUSUOPER          VARCHAR2(250)
/
ALTER TABLE LOGSAIDAOL ADD CDORESPOPER           VARCHAR2(12)
/
CREATE INDEX IN_LOGSAIDAOL_01 ON LOGSAIDAOL (CDFILIOPELOG, DTPROGENPEOL, DTPROGEMBARQ, CDROTATRANS,
                   CDSUBGRPROD, IDEXPTITMOLEXP, CDLOTENVITOL, DTOPERACAO,
                   DSRDMUSUOPER, DSENDUSUOPER, DSNMUSUOPER, DSMETUSUOPER,
                   CDORESPOPER)
/
ALTER TABLE LOGSAIDAOL ADD NRLOGSAIDAOL          NUMBER
/
CREATE INDEX IN_LOGSAIDAOL_02 ON LOGSAIDAOL (NRLOGSAIDAOL)
/
 ALTER TABLE DIASCONSUMO ADD IDDIACONSINIANT VARCHAR2(1) DEFAULT 'N' NULL
    CONSTRAINT CKC_DIASCON_IDDIAINI CHECK (IDDIACONSINIANT IS NULL OR (IDDIACONSINIANT IN ('S','N')))
 /
  ALTER TABLE DIASCONSUMO ADD IDDIACONSFINANT VARCHAR2(1)  DEFAULT 'N' NULL
    CONSTRAINT CKC_DIASCON_IDDIAFIN CHECK (IDDIACONSFINANT IS NULL OR (IDDIACONSFINANT IN ('S','N')))
/
CREATE TABLE FILTROCOMPSIMPL (
  NRFILTROCOMPSFDA  NUMBER,  
  IDROTINA          VARCHAR2(10) DEFAULT 'NECCOMP' NOT NULL
		CONSTRAINT CKC_IDROTINA_FILTROCOMPSIMPL CHECK (IDROTINA IN ('NECCOMP', 'SOLICMP','APURAC', 'AUTOFORN')),
  CDPERIENTR        VARCHAR2(2) NOT NULL,
  DIENTREGA         VARCHAR2(2) NOT NULL, 
  DIACONSUMOINI     VARCHAR2(2) NOT NULL, 
  DIACONSUMOFIN     VARCHAR2(2) NOT NULL,
  DTFILTRO          DATE NOT NULL,
  DTULTATUFILTRO    DATE NOT NULL,
  CDOPERRESP        VARCHAR2(12) NOT NULL,    
  NRORG             NUMBER        DEFAULT         SYS_CONTEXT('nrorg_ctx','nrorg')  NOT NULL,
  IDATIVO           VARCHAR2(1)       DEFAULT 'S'                     NOT NULL
		CONSTRAINT CKC_IDATIVO_FILTROCOMPSIMPL 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_FILTROCOMPSIMPL PRIMARY KEY (NRFILTROCOMPSFDA)
)
/
CREATE TABLE FILICOMPSIMPL(
  NRFILICOMPSIMPL   NUMBER,
  NRFILTROCOMPSFDA  NUMBER,
  CDFILIAL          VARCHAR2(4)   NOT NULL,
  NRORG             NUMBER        DEFAULT         SYS_CONTEXT('nrorg_ctx','nrorg')  NOT NULL,
  IDATIVO           VARCHAR2(1)       DEFAULT 'S'                     NOT NULL
    CONSTRAINT CKC_IDATIVO_FILICOMPSIMPL 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_FILICOMPSIMPL PRIMARY KEY (NRFILICOMPSIMPL),
  CONSTRAINT UN_FILICOMPSIMPL UNIQUE (CDFILIAL, NRFILTROCOMPSFDA)
)
/
CREATE TABLE PRODCOMPSIMPL(
  NRPRODCOMPSIMPL   NUMBER,
  NRFILTROCOMPSFDA  NUMBER,
  CDPRODUTO         VARCHAR2(10)  NOT NULL,
  CDARVPROD         VARCHAR2(20)  NOT NULL,
  CDNVPRODUTO       VARCHAR2(1)   NOT NULL,
  NRORG             NUMBER        DEFAULT         SYS_CONTEXT('nrorg_ctx','nrorg')  NOT NULL,
  IDATIVO           VARCHAR2(1)       DEFAULT 'S'                     NOT NULL
    CONSTRAINT CKC_IDATIVO_PRODCOMPSIMPL 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_PRODCOMPSIMPL PRIMARY KEY (NRPRODCOMPSIMPL),
  CONSTRAINT UN_PRODCOMPSIMPL UNIQUE (CDPRODUTO, NRFILTROCOMPSFDA)
)
/

CREATE SEQUENCE SEQ_FILTROCOMPSIMPL START WITH 1 MINVALUE 1 INCREMENT BY 1
/

CREATE SEQUENCE SEQ_FILICOMPSIMPL START WITH 1 MINVALUE 1 INCREMENT BY 1
/

CREATE SEQUENCE SEQ_PRODCOMPSIMPL START WITH 1 MINVALUE 1 INCREMENT BY 1
/
CREATE INDEX CMP00601_FK ON FILICOMPSIMPL (NRFILTROCOMPSFDA ASC)
/
ALTER TABLE FILICOMPSIMPL
  ADD CONSTRAINT FKCMP00601FILICOMPSIMPL FOREIGN KEY  (NRFILTROCOMPSFDA)
    REFERENCES FILTROCOMPSIMPL (NRFILTROCOMPSFDA) ON DELETE CASCADE
/
CREATE INDEX CMP00602_FK ON FILICOMPSIMPL (CDFILIAL ASC)
/
ALTER TABLE FILICOMPSIMPL
  ADD CONSTRAINT FKCMP00602FILICOMPSIMPL FOREIGN KEY  (CDFILIAL)
    REFERENCES FILIAL (CDFILIAL)
/
CREATE INDEX CMP00603_FK ON PRODCOMPSIMPL (NRFILTROCOMPSFDA ASC)
/
ALTER TABLE PRODCOMPSIMPL
  ADD CONSTRAINT FKCMP00603PRODCOMPSIMPL FOREIGN KEY  (NRFILTROCOMPSFDA)
    REFERENCES FILTROCOMPSIMPL (NRFILTROCOMPSFDA) ON DELETE CASCADE
/
CREATE INDEX CMP00604_FK ON PRODCOMPSIMPL (CDPRODUTO ASC)
/
ALTER TABLE PRODCOMPSIMPL
  ADD CONSTRAINT FKCMP00604PRODCOMPSIMPL FOREIGN KEY  (CDPRODUTO)
    REFERENCES PRODUTO (CDPRODUTO)
/
ALTER TABLE LOGNECECOMP ADD NRCOMPSIMPLI VARCHAR2(10) NULL
/