CREATE TABLE GRUPCLIEFAT
(
    NRGRUPCLIEFAT 	  NUMBER NOT NULL,
    CDFILIAL 		  VARCHAR2(4) NOT NULL,
    NMGRUPCLIEFAT 	  VARCHAR2(50) NOT NULL,
    VRMINFAT 		  FLOAT NULL,
    CDDIABASE 		  VARCHAR2(2) NULL,
    CDCLIENTE 	      VARCHAR2(21) NULL,
    CDFILIALSERV 	  VARCHAR2(4) NULL,
    CDSERVFILI 		  VARCHAR2(5) 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_GRUPCLIEFAT CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT PK_GRUPCLIEFAT PRIMARY KEY (NRGRUPCLIEFAT),
		CONSTRAINT CKC_CDDIABASE_GRUPCLIEFAT CHECK (CDDIABASE IN 
						  ('01', '02', '03', '04', '05','06', '07', '08',
						   '09', '10', '11', '12', '13','14', '15', '16',
						   '17', '18', '19', '20', '21','22', '23', '24',
						   '25', '26', '27', '28', '29','30', '31'))
)
/

ALTER TABLE GRUPCLIEFAT
    ADD CONSTRAINT FKFAI00645FILIAL FOREIGN KEY (CDFILIAL)
       REFERENCES FILIAL (CDFILIAL)
/

CREATE INDEX FAI00645_FK ON GRUPCLIEFAT (CDFILIAL)
/

ALTER TABLE GRUPCLIEFAT
    ADD CONSTRAINT FKFAI00646SERVCLIE FOREIGN KEY (CDCLIENTE, CDFILIALSERV, CDSERVFILI)
       REFERENCES SERVCLIE (CDCLIENTE, CDFILIAL, CDSERVFILI)
/

CREATE INDEX FAI00646_FK ON GRUPCLIEFAT (CDCLIENTE, CDFILIALSERV, CDSERVFILI)
/

CREATE TABLE ITGRUPCLIEFAT
(
    NRITGRUPCLIEFAT   NUMBER 		  NOT NULL,
    NRGRUPCLIEFAT	  NUMBER 		  NOT NULL,
    CDFILIAL 		  VARCHAR2(4) 	  NOT NULL,
    CDCLIENTE 		  VARCHAR2(21) 	  NOT NULL,
    IDPRINCIPAL 	  VARCHAR2(1) 	  DEFAULT 'N' 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_ITGRUPCLIEFAT CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT CKC_IDPRINCIPAL_ITGRUPCLIEFAT CHECK (IDPRINCIPAL IN ('S','N')),
		CONSTRAINT PK_ITGRUPCLIEFAT PRIMARY KEY (NRITGRUPCLIEFAT),
		CONSTRAINT UN_ITGRUPCLIEFAT UNIQUE (NRGRUPCLIEFAT, CDFILIAL, CDCLIENTE)
)
/

ALTER TABLE ITGRUPCLIEFAT
    ADD CONSTRAINT FKFAI00647GRUPCLIEFAT FOREIGN KEY (NRGRUPCLIEFAT)
       REFERENCES GRUPCLIEFAT (NRGRUPCLIEFAT) ON DELETE CASCADE
/

CREATE INDEX FAI00647_FK ON ITGRUPCLIEFAT (NRGRUPCLIEFAT)
/

ALTER TABLE ITGRUPCLIEFAT
    ADD CONSTRAINT FKFAI00648CLIENFILIAL FOREIGN KEY (CDFILIAL, CDCLIENTE)
       REFERENCES CLIENFILIAL (CDFILIAL, CDCLIENTE)
/

CREATE INDEX FAI00648_FK ON ITGRUPCLIEFAT (CDFILIAL, CDCLIENTE)
/

CREATE TABLE SERVGRUPCLIEFAT
(
    NRSERVGRUPCLIEFAT NUMBER NOT NULL,
    NRGRUPCLIEFAT 	  NUMBER NOT NULL,
    CDCLIENTE 		  VARCHAR2(21) NOT NULL,
    CDFILIAL 		  VARCHAR2(4) NOT NULL,
    CDSERVFILI 		  VARCHAR2(5) NOT NULL,
    VRMINEFETIVO      FLOAT NULL,
    VRMINEFETIVODOM   FLOAT NULL,
    VRMINEFETIVOSEG   FLOAT NULL,
    VRMINEFETIVOTER   FLOAT NULL,
    VRMINEFETIVOQUA   FLOAT NULL,
    VRMINEFETIVOQUI   FLOAT NULL,
    VRMINEFETIVOSEX   FLOAT NULL,
    VRMINEFETIVOSAB   FLOAT NULL,
    VRMINEFETIVOFER   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_SERVGRUPCLIEFAT CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT PK_SERVGRUPCLIEFAT PRIMARY KEY (NRSERVGRUPCLIEFAT),
		CONSTRAINT UN_SERVGRUPCLIEFAT UNIQUE (NRGRUPCLIEFAT, CDCLIENTE, CDFILIAL, CDSERVFILI)
)
/

ALTER TABLE SERVGRUPCLIEFAT
    ADD CONSTRAINT FKFAI00649GRUPCLIEFAT FOREIGN KEY (NRGRUPCLIEFAT)
       REFERENCES GRUPCLIEFAT (NRGRUPCLIEFAT) ON DELETE CASCADE
/

CREATE INDEX FAI00649_FK ON SERVGRUPCLIEFAT (NRGRUPCLIEFAT)
/


ALTER TABLE SERVGRUPCLIEFAT
    ADD CONSTRAINT FKFAI00650SERVCLIE FOREIGN KEY (CDCLIENTE, CDFILIAL, CDSERVFILI)
       REFERENCES SERVCLIE (CDCLIENTE, CDFILIAL, CDSERVFILI)
/

CREATE INDEX FAI00650_FK ON SERVGRUPCLIEFAT (CDCLIENTE, CDFILIAL, CDSERVFILI)
/