CREATE TABLE MAPAFCI
(
    CDFILIALFCI       VARCHAR2(4)     NOT NULL,
    NRMAPAFCI         INTEGER         NOT NULL,
    DTINIPERIODO      DATE            NOT NULL,
    DTFIMPERIODO      DATE            NOT NULL,
    IDSITMAPAFCI      VARCHAR2(1)  	  DEFAULT 'P' 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_MAPAFCI CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT CKC_IDSITMAPAFCI_MAPAFCI CHECK ( IDSITMAPAFCI IN ('P','A','C')),
		CONSTRAINT PK_MAPAFCI PRIMARY KEY (CDFILIALFCI, NRMAPAFCI)
)
/
ALTER TABLE MAPAFCI ADD CONSTRAINT FKCDP01007MAPAFCI_FILIAL
	FOREIGN KEY  (CDFILIALFCI) REFERENCES FILIAL (CDFILIAL)
/

CREATE INDEX CDP01007_FK ON MAPAFCI (CDFILIALFCI ASC)
/


CREATE TABLE ITMAPAFCI
(
    CDFILIALFCI       VARCHAR2(4)     	       NOT NULL,
    NRMAPAFCI         INTEGER                  NOT NULL, 
	NRSEQITMAPAFCI    INTEGER                  NOT NULL,
    CDPRODUTOOP       VARCHAR2(10)             NULL,
    NRORDPROD         VARCHAR2(10)             NULL,
    NRSUBORDPROD      VARCHAR2(1)              NULL,
	QTPRODUZIDA       FLOAT                    NULL,
	CDPRODINSUMO      VARCHAR2(10)             NULL,
	NRLOTEESTQ        VARCHAR2(20)             NULL,
    NRSUBLOTE         VARCHAR2(04)             NULL,
	CDFILIAL          VARCHAR2(04)             NULL, 
	NRLANCTONF        VARCHAR2(10)             NULL,
	NRSEQITPROD       VARCHAR2(04)             NULL,
	VRUNITITEM        FLOAT                    NULL, 
	VRPARCIMPO        FLOAT                    NULL,
	VRVENDAIE         FLOAT                    NULL, 
    VRFCI             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_ITMAPAFCI CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT PK_ITMAPAFCI PRIMARY KEY (CDFILIALFCI, NRMAPAFCI, NRSEQITMAPAFCI)
)
/

ALTER TABLE ITMAPAFCI
    ADD CONSTRAINT FKCDP01008ITMAPAFCI_MAPAFCI FOREIGN KEY  (CDFILIALFCI, NRMAPAFCI)
       REFERENCES MAPAFCI (CDFILIALFCI, NRMAPAFCI) ON DELETE CASCADE
/

CREATE INDEX CDP01008_FK ON ITMAPAFCI (CDFILIALFCI ASC, NRMAPAFCI ASC)
/

ALTER TABLE ITMAPAFCI
    ADD CONSTRAINT FKCDP01009ITMAPAFCI_PRODUTO FOREIGN KEY  (CDPRODUTOOP)
       REFERENCES PRODUTO (CDPRODUTO)
/

CREATE INDEX CDP01009_FK ON ITMAPAFCI (CDPRODUTOOP ASC)
/

ALTER TABLE ITMAPAFCI
    ADD CONSTRAINT FKCDP01010ITMAPAFCI_ORDPRODCAO FOREIGN KEY  (CDFILIALFCI, NRORDPROD, NRSUBORDPROD)
       REFERENCES ORDPRODUCAO (CDFILIAL, NRORDPROD, NRSUBORDPROD)
/

CREATE INDEX CDP01010_FK ON ITMAPAFCI (CDFILIALFCI ASC, NRORDPROD ASC, NRSUBORDPROD ASC)
/

ALTER TABLE ITMAPAFCI
    ADD CONSTRAINT FKCDP01011ITMAPAFCI_PRODINSU FOREIGN KEY  (CDPRODINSUMO)
       REFERENCES PRODUTO (CDPRODUTO)
/

CREATE INDEX CDP01011_FK ON ITMAPAFCI (CDPRODINSUMO ASC)
/

ALTER TABLE ITMAPAFCI
    ADD CONSTRAINT FKCDP01011ITMAPAFCI_LOTE FOREIGN KEY  (CDFILIALFCI, NRLOTEESTQ, NRSUBLOTE,CDPRODINSUMO)
       REFERENCES LOTE (CDFILIAL, NRLOTEESTQ, NRSUBLOTE, CDPRODUTO)
/

CREATE INDEX CDP01012_FK ON ITMAPAFCI (CDFILIALFCI ASC, NRLOTEESTQ ASC, NRSUBLOTE ASC,CDPRODINSUMO ASC)
/



CREATE TABLE DIGITOCST
(
    DIGITO1CST        VARCHAR2(1)     NOT NULL,
    IDCSTIFCI         VARCHAR2(1)     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_DIGITOCST CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT PK_DIGITOCST PRIMARY KEY (DIGITO1CST)
)
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES('0', 'N')
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES ('1', 'S')
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES ('2', 'S')
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES ('3', 'S')
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES ('4', 'N')
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES ('5', 'N')
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES ('6', 'N')
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES ('7', 'N')
/
INSERT INTO DIGITOCST (DIGITO1CST, IDCSTIFCI)
	VALUES ('8', 'S')
/

ALTER TABLE LOTE ADD NRCTRLFCI VARCHAR2(36) NULL
/

CREATE TABLE ARQUIVOTXT
 ( 
	CDFILIALFCI 	  VARCHAR2(4)	  NOT NULL, 
	NRMAPAFCI 		  INTEGER		  NOT NULL, 
	NRBLOCO 		  INTEGER  		  NOT NULL, 
	NRLINHA 		  INTEGER 		  NOT NULL, 
	DSLINHA 		  VARCHAR2(4000)  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_ARQUIVOTXT CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT PK_ARQUIVOTXT PRIMARY KEY (CDFILIALFCI, NRMAPAFCI, NRBLOCO, NRLINHA) 
)
/