CREATE TABLE CP_ORG_FILIAL (
    ID       NUMBER      NOT NULL,
    NRORG_ID NUMBER      NOT NULL,
    CDFILIAL VARCHAR2(4) 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'),
    CDOPERINCLUSAO VARCHAR2(12) DEFAULT '1',
    NRORGULTATU    NUMBER       DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg'),
    CDOPERULTATU   VARCHAR2(12) DEFAULT '1',    
    CONSTRAINT PK_CP_ORG_FILIAL          PRIMARY KEY(ID),
    CONSTRAINT CKC_IDATIVO_CP_ORG_FILIAL CHECK (IDATIVO IN ('S','N'))
)
/

ALTER TABLE CP_ORG_FILIAL ADD CONSTRAINT FK_CP_ORG_FILIAL_ORG      
	FOREIGN KEY (NRORG_ID) REFERENCES ORGANIZACAO (NRORG)
/

ALTER TABLE CP_ORG_FILIAL ADD CONSTRAINT FK_CP_ORG_FILIAL_FILI     
	FOREIGN KEY (CDFILIAL) REFERENCES FILIAL (CDFILIAL)
/

CREATE TABLE ERROS_COLETA (
    ID             NUMBER        NOT NULL,
    SERVIDOR       VARCHAR2(500) NOT NULL,
    NRORG_ID       NUMBER        NOT NULL,
    CDTPMEDIDASERV NUMBER        NOT NULL,
    NRSEQCONTRATO  NUMBER        NOT NULL,
    CDSERVORCA     NUMBER,
    PRODUCT_ID     NUMBER,
    ERRO_COLETA    CLOB,
    DTINICOMPET    DATE,
    DTFINCOMPET    DATE,    
    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'),
    CDOPERINCLUSAO VARCHAR2(12) DEFAULT '1',
    NRORGULTATU    NUMBER       DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg'),
    CDOPERULTATU   VARCHAR2(12) DEFAULT '1',   
    CONSTRAINT PK_ERROS_COLETA          PRIMARY KEY(ID),
    CONSTRAINT CKC_IDATIVO_ERROS_COLETA CHECK (IDATIVO IN ('S','N'))
)
/

ALTER TABLE ERROS_COLETA ADD CONSTRAINT FK_CP_ORG_FILIAL_ORGA  
	FOREIGN KEY (NRORG_ID) REFERENCES ORGANIZACAO (NRORG)
/

ALTER TABLE ERROS_COLETA ADD CONSTRAINT FK_CP_ORG_FILIAL_MEDV    
	FOREIGN KEY (CDTPMEDIDASERV) REFERENCES FCT_TIPOMEDIDASERV (CDTPMEDIDASERV)
/
	
ALTER TABLE ERROS_COLETA ADD CONSTRAINT FK_CP_ORG_FILIAL_SEQC    
	FOREIGN KEY (NRSEQCONTRATO) REFERENCES FCT_CONTRATO (NRSEQCONTRATO)
/
	
ALTER TABLE ERROS_COLETA ADD CONSTRAINT FK_CP_ORG_FILIAL_SERV    
	FOREIGN KEY (CDSERVORCA) REFERENCES FCT_SERVICOORCA (CDSERVORCA)
/
	
ALTER TABLE ERROS_COLETA ADD CONSTRAINT FK_CP_ORG_FILIAL_PROD    
	FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (ID)
/