CREATE TABLE TRABRETROFAT 
(
	NRTRABRETROFAT 		NUMBER 		NOT NULL,
	NRCTRLTRABRETROFAT 	NUMBER 		NOT NULL,
	CDFILIAL 			VARCHAR2(4) NOT NULL,
	CDSERVFILI 			VARCHAR2(5) NOT NULL,
	CDTURNO 			VARCHAR2(1) NULL,
	NREFETIVOS 			FLOAT 		NOT NULL,
	VRPRECUNIT 			FLOAT 		NOT NULL,
	VRPRECUNITATUAL 	FLOAT 		NOT NULL,
	VRUNAFATURAR 		FLOAT 		NOT NULL,
	NRGRUPONF 			NUMBER 		NULL,
    NRORG               NUMBER 		DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
    IDATIVO             VARCHAR2(1) DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_TRABRETROFAT 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_TRABRETROFAT PRIMARY KEY (NRTRABRETROFAT),
	CONSTRAINT UN_TRABRETROFAT UNIQUE (NRCTRLTRABRETROFAT, CDFILIAL, CDSERVFILI, CDTURNO, VRPRECUNIT, VRPRECUNITATUAL)
)
/

ALTER TABLE TRABRETROFAT
    ADD CONSTRAINT FKFAI00626SERVICO FOREIGN KEY (CDFILIAL, CDSERVFILI)
       REFERENCES SERVICO (CDFILIAL, CDSERVFILI)
/

CREATE INDEX FAI00626_FK ON TRABRETROFAT (CDFILIAL, CDSERVFILI)
/

CREATE TABLE RETROFAT
(
	NRRETROFAT 			NUMBER 		NOT NULL,
	CDCLIENTE 			VARCHAR2(21) NOT NULL,
	DTINICIAL 			DATE 		NOT NULL,
	DTFINAL 			DATE 		NOT NULL,	
	CDFILIAL 			VARCHAR2(4) NOT NULL,
    NRAPURACFAT 		VARCHAR2(10) NOT NULL,
    NRORG               NUMBER 		DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
    IDATIVO             VARCHAR2(1) DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_RETROFAT 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_RETROFAT PRIMARY KEY (NRRETROFAT)
)
/

ALTER TABLE RETROFAT
    ADD CONSTRAINT FKFAI00627CLIENTE FOREIGN KEY (CDCLIENTE)
       REFERENCES CLIENTE (CDCLIENTE)
/

CREATE INDEX FAI00627_FK ON RETROFAT (CDCLIENTE)
/

ALTER TABLE RETROFAT
    ADD CONSTRAINT FKFAI00628CTRLAPURFAT FOREIGN KEY (CDFILIAL, NRAPURACFAT)
       REFERENCES CTRLAPURFAT (CDFILIAL, NRAPURACFAT)
/

CREATE INDEX FAI00628_FK ON RETROFAT (CDFILIAL, NRAPURACFAT)
/

CREATE INDEX FAI00629_FK ON RETROFAT (DTINCLUSAO)
/

CREATE INDEX FAI00630_FK ON RETROFAT (CDFILIAL, CDCLIENTE)
/

CREATE TABLE CTRLAPURRETROFAT
(
	NRCTRLAPURRETROFAT 	NUMBER 		NOT NULL,
	NRRETROFAT 			NUMBER 		NOT NULL,
	CDFILIAL 			VARCHAR2(4) NOT NULL,
    NRAPURACFAT 		VARCHAR2(10) NOT NULL,
    NRORG               NUMBER 		DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
    IDATIVO             VARCHAR2(1) DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_CTRLAPURRETROFAT 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_CTRLAPURRETROFAT PRIMARY KEY (NRCTRLAPURRETROFAT),
	CONSTRAINT UN_CTRLAPURRETROFAT UNIQUE(NRRETROFAT, CDFILIAL, NRAPURACFAT)
)
/

ALTER TABLE CTRLAPURRETROFAT
    ADD CONSTRAINT FKFAI00631RETROFAT FOREIGN KEY (NRRETROFAT)
       REFERENCES RETROFAT (NRRETROFAT)
/

CREATE INDEX FAI00631_FK ON CTRLAPURRETROFAT (NRRETROFAT)
/

ALTER TABLE CTRLAPURRETROFAT
    ADD CONSTRAINT FKFAI00632CTRLAPURFAT FOREIGN KEY (CDFILIAL, NRAPURACFAT)
       REFERENCES CTRLAPURFAT (CDFILIAL, NRAPURACFAT)
/

CREATE INDEX FAI00632_FK ON CTRLAPURRETROFAT (CDFILIAL, NRAPURACFAT)
/

CREATE TABLE HISTCTRLAPURFAT
(
	NRHISTCTRLAPURFAT 	NUMBER NOT 	NULL,
	CDFILIAL			VARCHAR2(4) NOT NULL,
	NRAPURACFAT			VARCHAR2(10) NOT NULL,
	CDFILIAPUR 			VARCHAR2(4) NOT NULL,
	CDCLIENTE 			VARCHAR2(21) NOT NULL,
	DTAPURACAO 			DATE 		NOT NULL,
	CDSERVFILI 			VARCHAR2(5) NOT NULL,
	CDTURNO				VARCHAR2(1) NULL,
	NMSERVICO 			VARCHAR2(40) NULL,
	CDSERVORI 			VARCHAR2(5) NULL,
	IDUTILEQUI 			VARCHAR2(1) DEFAULT 'N' NOT NULL
	CONSTRAINT CKC_IDUTILEQUI_HISTCTRLAPURFAT CHECK (IDUTILEQUI IN ('S','N')),
	IDAJUSEFET 			VARCHAR2(1) DEFAULT 'N' NOT NULL
	CONSTRAINT CKC_IDAJUSEFET_HISTCTRLAPURFAT CHECK (IDAJUSEFET IN ('S','N')),
	IDPRODSUBS 			VARCHAR2(1) DEFAULT 'N' NOT NULL
	CONSTRAINT CKC_IDPRODSUBS_HISTCTRLAPURFAT CHECK (IDPRODSUBS IN ('S','N')),
	CDPRODUTO 			VARCHAR2(10) NULL,
	DSPRODEMIS 			VARCHAR2(400) NULL,
	NREFETIVOS 			FLOAT 		NULL, 
	VRPRECUNIT 			FLOAT 		NULL,
	CDGRSERVFAT 		VARCHAR2(3) NULL,
	CDFILIAGREGADA		VARCHAR2(4) NULL,
	NRAPURACENTRAL		VARCHAR2(10) NULL,
	CDFILIALCENTRAL 	VARCHAR2(4) NULL,
	NRGRUPONF 			NUMBER 		NULL,
	CDTIPOOPER 			VARCHAR2(2) NULL,
	NRRETROFAT 			NUMBER 		NULL,
    NRORG               NUMBER 		DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
    IDATIVO             VARCHAR2(1) DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_HISTCTRLAPURFAT 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_HISTCTRLAPURFAT PRIMARY KEY (NRHISTCTRLAPURFAT)
)
/

ALTER TABLE HISTCTRLAPURFAT
    ADD CONSTRAINT FKFAI00633CTRLAPURFAT FOREIGN KEY (CDFILIAL, NRAPURACFAT)
       REFERENCES CTRLAPURFAT (CDFILIAL, NRAPURACFAT) ON DELETE CASCADE
/

CREATE INDEX FAI00633_FK ON HISTCTRLAPURFAT (CDFILIAL, NRAPURACFAT)
/

ALTER TABLE HISTCTRLAPURFAT
    ADD CONSTRAINT FKFAI00634CLIENTE FOREIGN KEY (CDCLIENTE)
       REFERENCES CLIENTE (CDCLIENTE)
/

CREATE INDEX FAI00634_FK ON HISTCTRLAPURFAT (CDCLIENTE)
/

CREATE INDEX FAI00635_FK ON HISTCTRLAPURFAT (CDFILIAPUR, CDCLIENTE, DTAPURACAO)
/

ALTER TABLE HISTCTRLAPURFAT
    ADD CONSTRAINT FKFAI00636SERVICO FOREIGN KEY (CDFILIAPUR, CDSERVFILI)
       REFERENCES SERVICO (CDFILIAL, CDSERVFILI)
/

CREATE INDEX FAI00636_FK ON HISTCTRLAPURFAT (CDFILIAPUR, CDSERVFILI)
/

ALTER TABLE HISTCTRLAPURFAT
    ADD CONSTRAINT FKFAI00637PRODUTO FOREIGN KEY (CDPRODUTO)
       REFERENCES PRODUTO (CDPRODUTO)
/

CREATE INDEX FAI00637_FK ON HISTCTRLAPURFAT (CDPRODUTO)
/

ALTER TABLE HISTCTRLAPURFAT
    ADD CONSTRAINT FKFAI00638RETROFAT FOREIGN KEY (NRRETROFAT)
       REFERENCES RETROFAT (NRRETROFAT)
/

CREATE INDEX FAI00638_FK ON HISTCTRLAPURFAT (NRRETROFAT)