CREATE SEQUENCE SEQ_EQUIPRATO START WITH 1
/

CREATE TABLE EQUIPRATO
(
    NREQUIPRATO 	  NUMBER 		  NOT NULL,
    CDFILAUXPRAT 	  VARCHAR2(4) 	  NOT NULL,
    CDPRATO 	   	  VARCHAR2(10) 	  NOT NULL,
    IDEQUIPRATO 	  VARCHAR2(1) 	  NOT NULL,
    CDFILIAL 		  VARCHAR2(4) 	  NULL,
    CDSERVFILI 		  VARCHAR2(5) 	  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_EQUIPRATO CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT CKC_EQUIPRATO_IDEQUIPRATO CHECK (IDEQUIPRATO IN ('P', 'F', 'S')),
		CONSTRAINT PK_EQUIPRATO PRIMARY KEY (NREQUIPRATO),
		CONSTRAINT FKPLA01315PRATO FOREIGN KEY (CDFILAUXPRAT, CDPRATO) REFERENCES PRATO (CDFILAUXPRAT, CDPRATO),
		CONSTRAINT FKPLA01316FILIAL FOREIGN KEY (CDFILIAL) REFERENCES FILIAL (CDFILIAL),
		CONSTRAINT UN_EQUIPRATO UNIQUE (IDEQUIPRATO, CDFILIAL, CDSERVFILI, CDFILAUXPRAT, CDPRATO)
)
/

CREATE INDEX IDXEQUIPRATOSERVICO ON EQUIPRATO(CDFILIAL, CDSERVFILI)
/

CREATE INDEX PLA01315_FK ON EQUIPRATO (CDFILAUXPRAT, CDPRATO)
/

CREATE INDEX PLA01316_FK ON EQUIPRATO(CDFILIAL)
/

CREATE SEQUENCE SEQ_PRATEQUIPRATO START WITH 1
/

CREATE TABLE PRATEQUIPRATO
(
    NREQUIPRATO 	  NUMBER 		  NOT NULL,
    NRPRATEQUIPRATO   NUMBER 	   	  NOT NULL,
    CDFILAUXPRAT 	  VARCHAR2(4) 	  NOT NULL,
    CDPRATO 	 	  VARCHAR2(10) 	  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_PRATEQUIPRATO PRIMARY KEY (NRPRATEQUIPRATO),
		CONSTRAINT UN_PRATEQUIPRATO UNIQUE (NREQUIPRATO, CDFILAUXPRAT, CDPRATO),
		CONSTRAINT FKPLA01318PRATO FOREIGN KEY (CDFILAUXPRAT, CDPRATO) REFERENCES PRATO (CDFILAUXPRAT, CDPRATO),
		CONSTRAINT FKPLA01319EQUIPRATO FOREIGN KEY (NREQUIPRATO) REFERENCES EQUIPRATO (NREQUIPRATO) ON DELETE CASCADE,
		CONSTRAINT CKC_IDATIVO_PRATEQUIPRATO CHECK (IDATIVO IN ('S','N'))
)
/

CREATE INDEX PLA01318_FK ON PRATEQUIPRATO (CDFILAUXPRAT,CDPRATO)
/

CREATE INDEX PLA01319_FK ON PRATEQUIPRATO (NREQUIPRATO)
/