CREATE SEQUENCE SEQ_LOGVALIDACARD START WITH 1
/

CREATE TABLE LOGVALIDACARD
(
    NRLOGVALIDACARD   NUMBER 		  NOT NULL,
    NRVALIDACAO 	  NUMBER 		  NOT NULL,    
    CDFILIAL 		  VARCHAR2(4) 	  NOT NULL,
    CDSERVFILI 		  VARCHAR2(5) 	  NOT NULL,    
    DTMESCARD 		  DATE 		      NOT NULL,
    DTVALIDACAO 	  DATE 			  DEFAULT TRUNC(SYSDATE) NOT NULL,
    IDVDCUSTO		  VARCHAR2(1) 	  DEFAULT 'N' NOT NULL,  
    VRCUSTOPERC 	  FLOAT 		  NULL,
    VRCUSTOPADRAO 	  FLOAT 		  NULL,
    VRDIFECUSTO 	  FLOAT 		  NULL,
    VRMINIMOCUSTO 	  FLOAT 		  NULL,
    VRMAXIMOCUSTO 	  FLOAT		      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_LOGVALIDACARD  CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT CKC_LOGVALCARD_IDVDCUSTO CHECK (IDVDCUSTO IN ('S', 'N')),
		CONSTRAINT PK_LOGVALIDACARD PRIMARY KEY (NRLOGVALIDACARD),
		CONSTRAINT UN_LOGVALIDACARD UNIQUE (NRVALIDACAO, CDFILIAL, CDSERVFILI, DTMESCARD),
		CONSTRAINT FKPLA01312SERVICO_LOGVALIDCARD FOREIGN KEY (CDFILIAL, CDSERVFILI) REFERENCES SERVICO (CDFILIAL, CDSERVFILI)
)
/

CREATE INDEX PLA01312_FK ON LOGVALIDACARD (CDFILIAL ASC, CDSERVFILI ASC)
/

CREATE INDEX IDX_LOGVALIDACARD_01 ON LOGVALIDACARD (DTVALIDACAO)
/

CREATE SEQUENCE SEQ_LOGVDCARDCOMEN START WITH 1
/ 

CREATE TABLE LOGVDCARDCOMEN
(
    NRLOGVDCARDCOMEN  NUMBER 		  NOT NULL,
    NRLOGVALIDACARD   NUMBER 		  NOT NULL,
    DTCARDAPIO 		  DATE 			  NOT NULL,
    CDTIPOPRAT 		  VARCHAR2(3)	  NOT NULL,
    QTCOMENTPPRAT 	  FLOAT 		  NULL,
    QTCOMENDIA 		  FLOAT		      NULL,
    QTDIFCOMEN		  FLOAT 		  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_LOGVDCARDCOMEN  CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT PK_LOGVDCARDCOMEN PRIMARY KEY (NRLOGVDCARDCOMEN),
		CONSTRAINT FKPLA01313LOGVLICAR_VDCARCOMEN FOREIGN KEY (NRLOGVALIDACARD) REFERENCES LOGVALIDACARD (NRLOGVALIDACARD) ON DELETE CASCADE
)
/

CREATE INDEX PLA01313_FK ON LOGVDCARDCOMEN(NRLOGVALIDACARD ASC)
/

CREATE SEQUENCE SEQ_LOGVDCARDRESTR START WITH 1
/

CREATE TABLE LOGVDCARDRESTR
(	
    NRLOGVDCARDRESTR  NUMBER 		  NOT NULL,
    NRLOGVALIDACARD   NUMBER 		  NOT NULL,
    IDRESTRICAO 	  VARCHAR2(30) 	  NOT NULL,   
    CDFILAUXPRAT 	  VARCHAR2(4) 	  NULL,
    CDPRATO 		  VARCHAR2(10) 	  NULL,
    QTOCORRENCIA 	  FLOAT 		  NULL,
    NRSEMANA 		  FLOAT 		  NULL,
    QTMAXIMO 		  FLOAT		      NULL,
    QTMINIMO 		  FLOAT 		  NULL,
    NRGRUPOPRATO 	  NUMBER 		  NULL,
    CDPRODUTO 		  VARCHAR2(10)    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_LOGVDCARDRESTR  CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT CKC_LOGVDCARDRESTR_IDRESTRICAO  CHECK (IDRESTRICAO IN ('FREQPRATMESMIN', 'FREQPRATMESMAX', 'FREQPRATSEM', 
																		  'FREQGRUPPRATMESMIN', 'FREQGRUPPRATMESMAX', 'FREQGRUPPRATSEM',
																		  'FREQOPGRUPPRATMESMIN', 'FREQOPGRUPPRATMESMAX', 'FREQOPGRUPPRATSEM',
																		  'FREQPRODGRUPPRATMESMIN', 'FREQPRODGRUPPRATMESMAX', 'FREQPRODGRUPPRATSEM',
																		  'RESTRICAOPRODGRUPPRAT', 'LIMITEPERCSERVMIN', 'LIMITEPERCSERVMAX')),
		CONSTRAINT PK_LOGVDCARDRESTR PRIMARY KEY (NRLOGVDCARDRESTR),
		CONSTRAINT FKPLA01314LOGVALIDACARD FOREIGN KEY (NRLOGVALIDACARD) REFERENCES LOGVALIDACARD (NRLOGVALIDACARD) ON DELETE CASCADE
)
/

CREATE INDEX PLA01314_FK ON LOGVDCARDRESTR(NRLOGVALIDACARD)
/