CREATE TABLE SUBGRUPOM (
  CDSUBGRU VARCHAR2(10) NOT NULL, 
  CDSETOR VARCHAR2(4) DEFAULT NULL NULL, 
  DSSUBGRU VARCHAR2(50) NOT NULL, 
  DSSUBGRUES VARCHAR2(50) DEFAULT NULL NULL, 
  DSSUBGRUIN VARCHAR2(50) DEFAULT NULL NULL, 
  NRORG NUMBER(10) DEFAULT 1 NOT NULL, 
  IDATIVO VARCHAR2(1) DEFAULT 'S' NOT NULL, 
  DTINCLUSAO DATE NOT NULL, 
  DTULTATU DATE NOT NULL, 
  NRORGINCLUSAO NUMBER(10) DEFAULT 1 NOT NULL, 
  CDOPERINCLUSAO VARCHAR2(12) NOT NULL, 
  NRORGULTATU NUMBER(10) DEFAULT 1 NOT NULL, 
  CDOPERULTATU VARCHAR2(12) NOT NULL, 
  CDARVPRODBKP VARCHAR2(20) DEFAULT NULL NULL,
  CONSTRAINT PK_SUBGRUPOM PRIMARY KEY(CDSUBGRU, NRORG)
)
/
ALTER TABLE SUBGRUPOM ADD CONSTRAINT FKCOC00309SETOR FOREIGN KEY (CDSETOR, NRORG) REFERENCES SETOR (CDSETOR, NRORG)
/
ALTER TABLE SUBGRUPOM ADD CONSTRAINT CKC_IDATIVO_SUBGRUPOM CHECK (IDATIVO in ('S','N'))
/
create index COC00309_FK on SUBGRUPOM (CDSETOR asc, NRORG ASC)
/
CREATE TABLE PRODSUBGRUM (
  CDSUBGRU VARCHAR2(10) NOT NULL, 
  CDPRODUTO VARCHAR2(10) NOT NULL, 
  QTPRODSU DOUBLE PRECISION NOT NULL, 
  NRORG NUMBER(10) DEFAULT 1 NOT NULL, 
  DTINCLUSAO DATE NOT NULL, 
  DTULTATU DATE NOT NULL, 
  NRORGINCLUSAO NUMBER(10) DEFAULT 1 NOT NULL, 
  CDOPERINCLUSAO VARCHAR2(12) DEFAULT '1' NOT NULL, 
  NRORGULTATU NUMBER(10) DEFAULT 1 NOT NULL, 
  CDOPERULTATU VARCHAR2(12) DEFAULT '1' NOT NULL, 
  IDATIVO VARCHAR2(1) DEFAULT 'S' NOT NULL,
  CONSTRAINT PK_PRODSUBGRUM PRIMARY KEY(CDSUBGRU, CDPRODUTO, NRORG)
)
/
ALTER TABLE PRODSUBGRUM ADD CONSTRAINT FKCOC00310SUBGRUPOM FOREIGN KEY (CDSUBGRU, NRORG) REFERENCES SUBGRUPOM (CDSUBGRU, NRORG)
/
ALTER TABLE PRODSUBGRUM ADD CONSTRAINT CKC_IDATIVO_PRODSUBGRUM CHECK (IDATIVO in ('S','N'))
/
CREATE TABLE RECSUBGRUM (
  CDSUBGRU VARCHAR2(10) NOT NULL, 
  CDRECEITA VARCHAR2(10) NOT NULL, 
  QTRECSUB DOUBLE PRECISION NOT NULL, 
  NRORG NUMBER(10) DEFAULT 1 NOT NULL, 
  DTINCLUSAO DATE NOT NULL, 
  DTULTATU DATE NOT NULL, 
  NRORGINCLUSAO NUMBER(10) DEFAULT 1 NOT NULL, 
  CDOPERINCLUSAO VARCHAR2(12) DEFAULT '1' NOT NULL, 
  NRORGULTATU NUMBER(10) DEFAULT 1 NOT NULL, 
  CDOPERULTATU VARCHAR2(12) DEFAULT '1' NOT NULL, 
  IDATIVO VARCHAR2(1) DEFAULT 'S' NOT NULL,
  CONSTRAINT PK_RECSUBGRUM PRIMARY KEY(CDSUBGRU, CDRECEITA, NRORG)
)
/
ALTER TABLE RECSUBGRUM ADD CONSTRAINT FKCOC00313SUBGRUPOM FOREIGN KEY (CDSUBGRU, NRORG) REFERENCES SUBGRUPOM (CDSUBGRU, NRORG)
/
ALTER TABLE RECSUBGRUM ADD CONSTRAINT CKC_IDATIVO_RECSUBGRUM CHECK (IDATIVO in ('S','N'))
/
alter table RECSUBGRUM
    add constraint FKCOC00312RECEITAM foreign key  (CDRECEITA, NRORG)
       references RECEITAM (CDRECEITA, NRORG)
/
CREATE TABLE SUBGRUPOMFILI (
  CDSUBGRU VARCHAR2(10) NOT NULL, 
  CDFILIAL VARCHAR2(4) NOT NULL, 
  NRSUBGRUMFILI NUMBER(10) NOT NULL, 
  DSSUBGRUMFILI VARCHAR2(100) NOT NULL, 
  DSSUBGRUMFILIING VARCHAR2(100) DEFAULT NULL NULL, 
  DSSUBGRUMFILIESP VARCHAR2(100) DEFAULT NULL NULL, 
  NRORG NUMBER(10) DEFAULT 1 NOT NULL, 
  DTINCLUSAO DATE NOT NULL, 
  DTULTATU DATE NOT NULL, 
  NRORGINCLUSAO NUMBER(10) DEFAULT 1 NOT NULL, 
  CDOPERINCLUSAO VARCHAR2(12) DEFAULT '1' NOT NULL, 
  NRORGULTATU NUMBER(10) DEFAULT 1 NOT NULL, 
  CDOPERULTATU VARCHAR2(12) DEFAULT '1' NOT NULL, 
  IDATIVO VARCHAR2(1) DEFAULT 'S' NOT NULL,
  CONSTRAINT PK_SUBGRUPOMFILI PRIMARY KEY(CDSUBGRU, CDFILIAL, NRORG)
)
/
ALTER TABLE SUBGRUPOMFILI ADD CONSTRAINT FKCOC00314SUBGRUPOM FOREIGN KEY (CDSUBGRU, NRORG) REFERENCES SUBGRUPOM (CDSUBGRU, NRORG)
/
ALTER TABLE SUBGRUPOMFILI ADD CONSTRAINT FKCOC00315FILIAL FOREIGN KEY (CDFILIAL, NRORG) REFERENCES FILIAL (CDFILIAL, NRORG)
/
ALTER TABLE SUBGRUPOMFILI ADD CONSTRAINT CKC_IDATIVO_SUBGRUPOMFILI CHECK (IDATIVO in ('S','N'))