CREATE TABLE GRUPSUGESPROD
(
    CDGRUSUGESPROD    VARCHAR2(10)    NOT NULL,    
    NMGRUSUGESPROD    VARCHAR2(40)    NOT NULL,
    NRORDEM           VARCHAR2(4)     NOT NULL,
    DTULTATUSUGES     DATE            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_GRUPSUGESPROD CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT PK_GRUPSUGESPROD PRIMARY KEY (CDGRUSUGESPROD)
)
/

CREATE TABLE PRODPRINC
(   
    CDPRODUTO         VARCHAR2(10) 	  NOT NULL,
    CDGRUSUGESPROD    VARCHAR2(10) 	  NOT NULL,
    IDQUANTPRODPRINC  VARCHAR2(1)  	  DEFAULT 'N' NOT NULL,
    IDTOTALPRODPRINC  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_PRODPRINC CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT CKC_IDQUANTPRODPRINC CHECK (IDQUANTPRODPRINC IN ('N','S')),
		CONSTRAINT CKC_IDTOTALPRODPRINC CHECK (IDTOTALPRODPRINC IN ('N','S')),
		CONSTRAINT PK_PARAMPRODPRINC PRIMARY KEY (CDPRODUTO, CDGRUSUGESPROD)
)
/

CREATE TABLE SUGESPRODRELAC
(   
    CDSUGESPRODRELAC  NUMBER       	  NOT NULL,
    CDGRUSUGESPROD    VARCHAR2(10) 	  NOT NULL,
    CDPRODUTO         VARCHAR2(10)    NOT NULL,
    CDPRODUTORELAC    VARCHAR2(10) 	  NOT NULL,    
    IDQUANTPROD       VARCHAR2(1)  	  DEFAULT 'N' NOT NULL,
    IDTOTALPROD       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_SUGESPRODRELAC CHECK (IDATIVO IN ('S','N')),
		CONSTRAINT CKC_IDQUANTPROD CHECK (IDQUANTPROD IN ('N','S')),
		CONSTRAINT CKC_IDTOTALPROD CHECK (IDTOTALPROD IN ('N','S')),
		CONSTRAINT PK_SUGESPRODRELAC PRIMARY KEY (CDSUGESPRODRELAC, CDGRUSUGESPROD, CDPRODUTO, CDPRODUTORELAC)
)
/

ALTER TABLE SUGESPRODRELAC
    ADD CONSTRAINT FKFIS00876PRODPRINC FOREIGN KEY  (CDPRODUTO, CDGRUSUGESPROD)
       REFERENCES PRODPRINC (CDPRODUTO, CDGRUSUGESPROD) ON DELETE CASCADE
/
CREATE INDEX FIS00876_FK ON SUGESPRODRELAC (CDPRODUTO ASC,CDGRUSUGESPROD ASC)
/

ALTER TABLE SUGESPRODRELAC ADD CONSTRAINT FKFIS00877SUGESPRODRELAC FOREIGN KEY (CDGRUSUGESPROD)
  REFERENCES GRUPSUGESPROD (CDGRUSUGESPROD) ON DELETE CASCADE
/
CREATE INDEX FIS00877_FK ON SUGESPRODRELAC (CDGRUSUGESPROD ASC)
/

ALTER TABLE ITEMORCAEVEN ADD CDPRODSUGERIDO VARCHAR2(10)
/

ALTER TABLE ITEMORCAEVEN ADD CDGRUSUGESPROD VARCHAR2(10)
/

ALTER TABLE ITEMORCAEVEN ADD CONSTRAINT FKFIS00878PRODSUGERIDO FOREIGN KEY (CDPRODSUGERIDO)
  REFERENCES PRODUTO (CDPRODUTO)
/
CREATE INDEX FIS00878_FK ON ITEMORCAEVEN (CDPRODSUGERIDO ASC)
/

CREATE SEQUENCE SEQ_SUGESPRODRELAC
/

CREATE OR REPLACE TRIGGER TRG_SEQ_SUGESPRODRELAC
BEFORE INSERT ON SUGESPRODRELAC
FOR EACH ROW
BEGIN
   :NEW.CDSUGESPRODRELAC := SEQ_SUGESPRODRELAC.NEXTVAL;
END;
/ 