CREATE TABLE FCT_NEGCONTRATO (
	NRNEGCONTRATO     NUMBER  NOT NULL,
	NRPARCNEGOCIO     NUMBER, 
	NRAGRUPAMENTO     NUMBER, 
	IDTPFATURA 	      CHAR(1),
	CDFILIAL          VARCHAR2 (4),
 	NRPARCFATURA      NUMBER,
 	CDTIPOOPERFATURA  VARCHAR2 (2),
 	CDSERIENF         VARCHAR2 (5), 
 	CDEMPRESA         VARCHAR2 (2), 
 	CDINSCESTA        VARCHAR2 (16), 
 	CDINSCMUNI        VARCHAR2 (16), 
 	NRSEQCONTRATO     NUMBER, 
 	NRITEMOCONTRATO   NUMBER, 
 	IDELEMENTO        VARCHAR2(100), 
 	DSVARIAVELNEGOCIA VARCHAR2 (500), 
 	IDTIPONEGOCIA     VARCHAR2 (2) DEFAULT 'VF', 
 	DSOBSNEGOCIA      CLOB, 
    NRORG             NUMBER 	     DEFAULT         SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
    IDATIVO           VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CK_IDATIVO_NEGOCONTR 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'
)
/

ALTER TABLE FCT_NEGCONTRATO ADD CONSTRAINT PK_FCT_NEGCONTRATO PRIMARY KEY (NRNEGCONTRATO)
/

ALTER TABLE FCT_NEGCONTRATO ADD CONSTRAINT CK_IDTPFATURA_NEGCONTR CHECK (IDTPFATURA IS NULL OR IDTPFATURA IN ('A', 'X', 'P'))
/

ALTER TABLE FCT_NEGCONTRATO ADD CONSTRAINT CK_IDTIPONEGOCIA_NEGOCONTR CHECK (IDTIPONEGOCIA IN ('VF','DP','DV','AP','AV'))
/

CREATE INDEX SM00316_FK ON FCT_NEGCONTRATO (NRAGRUPAMENTO ASC)
/

ALTER TABLE FCT_NEGCONTRATO 
  ADD CONSTRAINT FKSM00316AGRUPA_NEGOCONTR FOREIGN KEY (NRAGRUPAMENTO) 
      REFERENCES FCT_AGRUPAMENTO (NRAGRUPAMENTO)
/

CREATE INDEX SM00317_FK ON FCT_NEGCONTRATO (NRSEQCONTRATO ASC)
/

ALTER TABLE FCT_NEGCONTRATO 
  ADD CONSTRAINT FKSM00317CONTRATO_NEGOCONTR FOREIGN KEY (NRSEQCONTRATO) 
      REFERENCES FCT_CONTRATO (NRSEQCONTRATO)
/

CREATE INDEX SM00318_FK ON FCT_NEGCONTRATO (NRITEMOCONTRATO ASC)
/

ALTER TABLE FCT_NEGCONTRATO 
  ADD CONSTRAINT FKSM00318ITEMCONT_NEGOCONTR FOREIGN KEY (NRITEMOCONTRATO) 
      REFERENCES FCT_SERVICOORCA (NRITEMOCONTRATO)
/

CREATE TABLE FCT_PARCELASNEGCONTR (
	NRPARCELASNEGCONTR NUMBER  NOT NULL,
	NRNEGCONTRATO      NUMBER  NOT NULL,
	DTINIPARCNEGOCIA   DATE,
	DTFIMPARCNEGOCIA   DATE,
	IDMULTIPLICAQTMED  CHAR(1) DEFAULT 'N',
	VRPARCELANEGOCIA   FLOAT,
	NRORG             NUMBER 	     DEFAULT         SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
    IDATIVO           VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CK_IDATIVO_PARCNEGOCONTR 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'
)
/

ALTER TABLE FCT_PARCELASNEGCONTR ADD CONSTRAINT PK_FCT_PARCELASNEGCONTR PRIMARY KEY (NRPARCELASNEGCONTR)
/

ALTER TABLE FCT_PARCELASNEGCONTR ADD CONSTRAINT CK_IDMULTIPLICAQTMED CHECK (IDMULTIPLICAQTMED IN ('S', 'N'))
/

CREATE INDEX SM00319_FK ON FCT_PARCELASNEGCONTR (NRNEGCONTRATO ASC)
/

ALTER TABLE FCT_PARCELASNEGCONTR 
  ADD CONSTRAINT FKSM00319NEGCONT_PARCNEGCONT FOREIGN KEY (NRNEGCONTRATO) 
      REFERENCES FCT_NEGCONTRATO (NRNEGCONTRATO) ON DELETE CASCADE
/