CREATE TABLE HISTNOTIFPROPCOM (
	NRHISTNOTIFPROPCOM		NUMBER 														NOT NULL,
    NRSERVICOPROPCOM		NUMBER														NOT NULL,
    IDORIGEM				VARCHAR2(30)												NOT NULL
		CONSTRAINT CKC_IDORIGEM_HISTNOTIFPROPCOM CHECK (IDORIGEM IN ('GN', 'PLANEJAMENTO')),
    IDDESTINO				VARCHAR2(30)												NOT NULL
		CONSTRAINT CKC_IDDESTINO CHECK (IDDESTINO IN ('GN', 'PLANEJAMENTO')),	
    IDMOTIVO				VARCHAR2(30)												NOT NULL
		CONSTRAINT CKC_IDMOTIVO CHECK (IDMOTIVO IN ('APROVADO', 'CUSTOMIZACAO', 'DUVIDA', 'DUVIDARESPONDIDA', 'EDITAL', 'ERROCALCULO', 'FINALIZADO', 'RECUSADO', 'SEMCARDBASE', 'SERVICOCOMPLEMENTAR')),			
	DSOBSERVACAO			VARCHAR2(4000)												NOT NULL,
	TXHISTORICO				CLOB														NOT NULL,
	NRORG 					NUMBER			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 	NOT NULL,
	IDATIVO 				VARCHAR2(1) 	DEFAULT 'S' 								NOT NULL
		CONSTRAINT CKC_IDATIVO_HISTNOTIFPROPCOM 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' 								NULL,
	CONSTRAINT PK_HISTNOTIFPROPCOM PRIMARY KEY (NRHISTNOTIFPROPCOM)
)
/
ALTER TABLE HISTNOTIFPROPCOM ADD
	CONSTRAINT FKPLA01127SERVICOPROPCOM FOREIGN KEY (NRSERVICOPROPCOM)
		REFERENCES SERVICOPROPCOM (NRSERVICOPROPCOM)
/
CREATE INDEX FKPLA01127SERVICOPROPCOM ON HISTNOTIFPROPCOM (NRSERVICOPROPCOM)
/
CREATE TABLE NOTIFICACAOTCO (
	NRNOTIFICACAOTCO		NUMBER 														NOT NULL,
    DSEMAILS				VARCHAR2(4000)												NOT NULL,
    DSASSUNTO				VARCHAR2(4000)												NOT NULL,
	TXNOTIFICACAO			CLOB														NOT NULL,
	NRORG 					NUMBER			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 	NOT NULL,
	IDATIVO 				VARCHAR2(1) 	DEFAULT 'S' 								NOT NULL
		CONSTRAINT CKC_IDATIVO_NOTIFICACAOTCO 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' 								NULL,
	CONSTRAINT PK_NOTIFICACAOTCO PRIMARY KEY (NRNOTIFICACAOTCO)
)
/
ALTER TABLE SERVICO ADD NRSERVICOPROPCOM NUMBER NULL
/
ALTER TABLE SERVICO ADD NRTIPOCONTRATO NUMBER NULL
/
ALTER TABLE SERVICO ADD NRTIPOPUBLICO NUMBER NULL
/
ALTER TABLE SERVICO ADD NRPADRAOQUALIDADE NUMBER NULL
/
ALTER TABLE SERVICO ADD
	CONSTRAINT FKPLA01128SERVICOPROPCOM FOREIGN KEY (NRSERVICOPROPCOM)
		REFERENCES SERVICOPROPCOM (NRSERVICOPROPCOM)
/
CREATE INDEX FKPLA01128SERVICOPROPCOM ON SERVICO (NRSERVICOPROPCOM)
/
ALTER TABLE SERVICO ADD
	CONSTRAINT FKPLA01129TIPOCONTRATO FOREIGN KEY (NRTIPOCONTRATO)
		REFERENCES TIPOCONTRATO (NRTIPOCONTRATO)
/
CREATE INDEX FKPLA01129TIPOCONTRATO ON SERVICO (NRTIPOCONTRATO)
/
ALTER TABLE SERVICO ADD
	CONSTRAINT FKPLA01130TIPOPUBLICO FOREIGN KEY (NRTIPOPUBLICO)
		REFERENCES TIPOPUBLICO (NRTIPOPUBLICO)
/
CREATE INDEX FKPLA01130TIPOPUBLICO ON SERVICO (NRTIPOPUBLICO)
/
ALTER TABLE SERVICO ADD
	CONSTRAINT FKPLA01131PADRAOQUALIDADE FOREIGN KEY (NRPADRAOQUALIDADE)
		REFERENCES PADRAOQUALIDADE (NRPADRAOQUALIDADE)
/
CREATE INDEX FKPLA01131PADRAOQUALIDADE ON SERVICO (NRPADRAOQUALIDADE)
/
ALTER TABLE CONFCARD ADD NRCONFIGSERVPROPCOM NUMBER NULL
/
ALTER TABLE CONFCARD ADD NRPORCIONAMENTO NUMBER NULL
/
ALTER TABLE CONFCARD ADD NRGRAMATURACARDBASE NUMBER NULL
/
ALTER TABLE CONFCARD ADD
	CONSTRAINT FKPLA01132CONFIGSERVPROPCOM FOREIGN KEY (NRCONFIGSERVPROPCOM)
		REFERENCES CONFIGSERVPROPCOM (NRCONFIGSERVPROPCOM)
/
CREATE INDEX FKPLA01132CONFIGSERVPROPCOM ON CONFCARD (NRCONFIGSERVPROPCOM)