ALTER TABLE GRAMATURACARDBASE MODIFY IDTIPOGRAMATURA VARCHAR2(30) NULL
/

ALTER TABLE ITEMGRAMCARDBASE ADD NRGRUPOPRATO NUMBER NULL
/

ALTER TABLE ITEMGRAMCARDBASE 
    ADD CONSTRAINT FKFKPLA01224GRUPOPRATO FOREIGN KEY (NRGRUPOPRATO)
       REFERENCES GRUPOPRATO (NRGRUPOPRATO)
/

CREATE INDEX PLA01224_FK ON ITEMGRAMCARDBASE (NRGRUPOPRATO)
/

ALTER TABLE ITEMGRAMCARDBASE ADD NRTIPOPREPAROTCO NUMBER NULL
/

ALTER TABLE ITEMGRAMCARDBASE
    ADD CONSTRAINT FKPLA01225TIPOPREPAROTCO FOREIGN KEY (NRTIPOPREPAROTCO)
       REFERENCES TIPOPREPAROTCO (NRTIPOPREPAROTCO)
/

CREATE INDEX PLA01225_FK ON ITEMGRAMCARDBASE (NRTIPOPREPAROTCO) 
/

ALTER TABLE ITEMGRAMCARDBASE DROP CONSTRAINT UN_ITEMGRAMCARDBASE
/

DECLARE
BEGIN
	DELETE FROM ITEMGRAMCARDBASE
	 WHERE NRGRUPOPRATO IS NULL;
	COMMIT;	
END;
/

ALTER TABLE ITEMGRAMCARDBASE ADD CONSTRAINT UN_ITEMGRAMCARDBASE UNIQUE (NRGRAMATURACARDBASE, NRPORCIONAMENTO, NRGRUPOPRATO, NRTIPOPREPAROTCO, CDPRODUTO)
/

ALTER TABLE CONFGRUPOTPPROPCOM ADD IDTIPOGRAMATURA VARCHAR2(30) NULL
/

ALTER TABLE CONFGRUPOTPPROPCOM 
    ADD CONSTRAINT CKC_CONFGRUPOTPPC_IDTIPOGRAM	CHECK (IDTIPOGRAMATURA IN ('INNATURA', 'LIMPA', 'PRONTA'))
/

CREATE TABLE REGRACARDTRAB(
	NRREGRACARDTRAB 	NUMBER 			NOT NULL,
	NRCARDAPIOTRAB 		NUMBER 			NOT NULL,
	IDREGRACARDTRAB 	VARCHAR2(30) 	NOT NULL
		CONSTRAINT CKC_IDREGRA_REGRACARDTRAB CHECK (IDREGRACARDTRAB IN ('ADESAO', 'GRAMATURA', 'PARTSEG', 'AMOSTRA', 'DESCARTAVEL' , 'LIMPEZA')),
	CDGRTIPOPR 			VARCHAR2(2) 	NULL,
	NRPORCIONAMENTO 	NUMBER 			NULL,
	NRDIACARDBASE 		NUMBER 			NULL,
	NRATIVCLISEGMER 	NUMBER 			NULL,
	NRADESAOCARDBASE 	NUMBER 			NULL,
	NRGRAMATURACARDBASE NUMBER 			NULL,
	IDTIPOGRAMATURA 	VARCHAR2(30) 	NULL,
	NRTIPOPUBLICO 		NUMBER 			NULL,	
	NRPARTSEGCARDBASE 	NUMBER 			NULL,
	NRAMOSTRACARDBASE 	NUMBER 			NULL,
	IDTEMPERATURA 		VARCHAR2(30) 	NULL,	
	NRDESCARTCARDBASE 	NUMBER 			NULL,
	NRLIMPEZACARDBASE 	NUMBER 			NULL,
	NRORG 				NUMBER 			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
	IDATIVO 			VARCHAR2(1) 	DEFAULT 'S' NOT NULL,
		CONSTRAINT CKC_IDATIVO_REGRACARDTRAB 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',
		CONSTRAINT PK_REGRACARDTRAB PRIMARY KEY (NRREGRACARDTRAB)
)
/

ALTER TABLE REGRACARDTRAB
    ADD CONSTRAINT FKPLA01226CARDAPIOTRAB FOREIGN KEY (NRCARDAPIOTRAB)
       REFERENCES CARDAPIOTRAB (NRCARDAPIOTRAB) ON DELETE CASCADE
/

CREATE INDEX PLA01226_FK ON REGRACARDTRAB (NRCARDAPIOTRAB)
/

CREATE TABLE PORCIOGRAMCARDTRAB(
	NRPORCIOGRAMCARDTRAB NUMBER 		NOT NULL,
	NRCARDAPIOTRAB 		NUMBER 			NOT NULL,
	CDGRTIPOPR 			VARCHAR2(2) 	NOT NULL,
	NRPORCIONAMENTO 	NUMBER 			NOT NULL,
	NRGRAMATURACARDBASE NUMBER 			NULL,	
	IDTIPOGRAMATURA 	VARCHAR2(30) 	NULL,
	NRORG 				NUMBER 			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
	IDATIVO 			VARCHAR2(1) 	DEFAULT 'S' NOT NULL,
		CONSTRAINT CKC_IDATIVO_PORCIOGRAMCARDTRAB 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',
		CONSTRAINT PK_PORCIOGRAMCARDTRAB PRIMARY KEY (NRPORCIOGRAMCARDTRAB)
)
/

ALTER TABLE PORCIOGRAMCARDTRAB
    ADD CONSTRAINT FKPLA01227CARDAPIOTRAB FOREIGN KEY (NRCARDAPIOTRAB)
       REFERENCES CARDAPIOTRAB (NRCARDAPIOTRAB) ON DELETE CASCADE
/

CREATE INDEX PLA01227_FK ON PORCIOGRAMCARDTRAB (NRCARDAPIOTRAB)
/

ALTER TABLE PORCIOGRAMCARDTRAB
    ADD CONSTRAINT FKPLA01228GRTIPOPR FOREIGN KEY (CDGRTIPOPR)
       REFERENCES GRTIPOPR (CDGRTIPOPR) 
/

CREATE INDEX PLA01228_FK ON PORCIOGRAMCARDTRAB (CDGRTIPOPR)