CREATE TABLE RESPONSAVELPROPCOM (
	NRRESPONSAVELPROPCOM	NUMBER 														NOT NULL,
    NRPROPOSTACOMERCIAL 	NUMBER														NOT NULL,
    NRGERENTENEGOCIO 		NUMBER														NOT NULL,
	NRORG 					NUMBER			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 	NOT NULL,
	IDATIVO 				VARCHAR2(1) 	DEFAULT 'S' 								NOT NULL
		CONSTRAINT CKC_IDATIVO_RESPONSAVELPROPCOM 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_RESPONSAVELPROPCOM PRIMARY KEY (NRRESPONSAVELPROPCOM),
	CONSTRAINT UN_RESPONSAVELPROPCOM UNIQUE (NRPROPOSTACOMERCIAL, NRGERENTENEGOCIO)
)
/
ALTER TABLE RESPONSAVELPROPCOM ADD
	CONSTRAINT FKPLA01101PROPOSTACOMERCIAL FOREIGN KEY (NRPROPOSTACOMERCIAL)
		REFERENCES PROPOSTACOMERCIAL (NRPROPOSTACOMERCIAL)
/
CREATE INDEX FKPLA01101PROPOSTACOMERCIAL ON RESPONSAVELPROPCOM (NRPROPOSTACOMERCIAL)
/
ALTER TABLE RESPONSAVELPROPCOM ADD
	CONSTRAINT FKPLA01102GERENTENEGOCIO FOREIGN KEY (NRGERENTENEGOCIO)
		REFERENCES GERENTENEGOCIO (NRGERENTENEGOCIO)
/
CREATE INDEX FKPLA01102GERENTENEGOCIO ON RESPONSAVELPROPCOM (NRGERENTENEGOCIO)
/
CREATE TABLE VERSAOPROPCOM (
	NRVERSAOPROPCOM			NUMBER 														NOT NULL,
    NRPROPOSTACOMERCIAL 	NUMBER														NOT NULL,
    CDVERSAOPROPCOM 		VARCHAR2(3)													NOT NULL,
	IDVERSAOPROPCOM			VARCHAR2(30)	DEFAULT 'PENDENTE'							NOT NULL
		CONSTRAINT CKC_IDVERSAOPROPCOM CHECK (IDVERSAOPROPCOM IN ('APROVADA', 'CONCLUIDA', 'PENDENTE')),
	NRORG 					NUMBER			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 	NOT NULL,
	IDATIVO 				VARCHAR2(1) 	DEFAULT 'S' 								NOT NULL
		CONSTRAINT CKC_IDATIVO_VERSAOPROPCOM 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_VERSAOPROPCOM PRIMARY KEY (NRVERSAOPROPCOM),
	CONSTRAINT UN_VERSAOPROPCOM UNIQUE (NRPROPOSTACOMERCIAL, CDVERSAOPROPCOM)
)
/
ALTER TABLE VERSAOPROPCOM ADD
	CONSTRAINT FKPLA01103PROPOSTACOMERCIAL FOREIGN KEY (NRPROPOSTACOMERCIAL)
		REFERENCES PROPOSTACOMERCIAL (NRPROPOSTACOMERCIAL)
/
CREATE INDEX FKPLA01103PROPOSTACOMERCIAL ON VERSAOPROPCOM (NRPROPOSTACOMERCIAL)
/
CREATE TABLE SERVICOPROPCOM (
	NRSERVICOPROPCOM		NUMBER 														NOT NULL,
    NRVERSAOPROPCOM 		NUMBER														NOT NULL,
    NRSERVICOPROPOSTA 		NUMBER														NOT NULL,
	NRPADRAOQUALIDADE 		NUMBER														NOT NULL,
	NRTIPOPUBLICO 			NUMBER														NOT NULL,
	IDSERVICOPROPCOM		VARCHAR2(30)	DEFAULT 'PENDENTE'							NOT NULL
		CONSTRAINT CKC_IDSERVICOPROPCOM CHECK (IDSERVICOPROPCOM IN ('AGUARDANDOGN', 'AGUARDANDOPLANEJAMENTO', 'CALCULADO', 'EFETIVADO', 'FINALIZADOPLANEJAMENTO', 'PENDENTE', 'RECUSADO')),
	QTSEGSEX				FLOAT														NULL,
	QTSABADO				FLOAT														NULL,
	QTDOMFERI				FLOAT														NULL,
	QTTOTDIAS				FLOAT														NOT NULL,
	QTTURNOS				FLOAT														NOT NULL,
	QTTOTREFEICOES			FLOAT														NOT NULL,
	NRCARDAPIOBASE 			NUMBER														NULL,
	NRDETALHESERVPROPCOM 	NUMBER														NULL,
	NRDETSERVPROPCOMPLAN 	NUMBER														NULL,
	CDFILIAL				VARCHAR2(4)													NULL,
	NRORG 					NUMBER			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 	NOT NULL,
	IDATIVO 				VARCHAR2(1) 	DEFAULT 'S' 								NOT NULL
		CONSTRAINT CKC_IDATIVO_SERVICOPROPCOM 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_SERVICOPROPCOM PRIMARY KEY (NRSERVICOPROPCOM),
	CONSTRAINT UN_SERVICOPROPCOM UNIQUE (NRVERSAOPROPCOM, NRSERVICOPROPOSTA, NRPADRAOQUALIDADE, NRTIPOPUBLICO)
)
/
ALTER TABLE SERVICOPROPCOM ADD
	CONSTRAINT FKPLA01104VERSAOPROPCOM FOREIGN KEY (NRVERSAOPROPCOM)
		REFERENCES VERSAOPROPCOM (NRVERSAOPROPCOM)
/
CREATE INDEX FKPLA01104VERSAOPROPCOM ON SERVICOPROPCOM (NRVERSAOPROPCOM)
/
ALTER TABLE SERVICOPROPCOM ADD
	CONSTRAINT FKPLA01105PADRAOQUALIDADE FOREIGN KEY (NRPADRAOQUALIDADE)
		REFERENCES PADRAOQUALIDADE (NRPADRAOQUALIDADE)
/
CREATE INDEX FKPLA01105PADRAOQUALIDADE ON SERVICOPROPCOM (NRPADRAOQUALIDADE)
/
ALTER TABLE SERVICOPROPCOM ADD
	CONSTRAINT FKPLA01106FILIAL FOREIGN KEY (CDFILIAL)
		REFERENCES FILIAL (CDFILIAL)
/
CREATE INDEX FKPLA01106FILIAL ON SERVICOPROPCOM (CDFILIAL)
/
ALTER TABLE SERVICOPROPCOM ADD
	CONSTRAINT FKPLA01107SERVICOPROPOSTA FOREIGN KEY (NRSERVICOPROPOSTA)
		REFERENCES SERVICOPROPOSTA (NRSERVICOPROPOSTA)
/
CREATE INDEX FKPLA01107SERVICOPROPOSTA ON SERVICOPROPCOM (NRSERVICOPROPOSTA)
/
ALTER TABLE SERVICOPROPCOM ADD
	CONSTRAINT FKPLA01108TIPOPUBLICO FOREIGN KEY (NRTIPOPUBLICO)
		REFERENCES TIPOPUBLICO (NRTIPOPUBLICO)
/
CREATE INDEX FKPLA01108TIPOPUBLICO ON SERVICOPROPCOM (NRTIPOPUBLICO)
/
ALTER TABLE SERVICOPROPCOM ADD
	CONSTRAINT FKPLA01109CARDAPIOBASE FOREIGN KEY (NRCARDAPIOBASE)
		REFERENCES CARDAPIOBASE (NRCARDAPIOBASE)
/
CREATE INDEX FKPLA01109CARDAPIOBASE ON SERVICOPROPCOM (NRCARDAPIOBASE)
/
CREATE TABLE DETALHESERVPROPCOM (
	NRDETALHESERVPROPCOM	NUMBER 														NOT NULL,
    NRSERVICOPROPCOM 		NUMBER														NOT NULL,
    CDRESPONSAVELPLAN		VARCHAR2(12)												NULL,
	NRCARDAPIOTRAB			NUMBER														NULL,
	NRORG 					NUMBER			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 	NOT NULL,
	IDATIVO 				VARCHAR2(1) 	DEFAULT 'S' 								NOT NULL
		CONSTRAINT CKC_IDATIVO_DETALHESERVPROPCOM 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_DETALHESERVPROPCOM PRIMARY KEY (NRDETALHESERVPROPCOM)
)
/
ALTER TABLE DETALHESERVPROPCOM ADD
	CONSTRAINT FKPLA01110OPERADOR FOREIGN KEY (CDRESPONSAVELPLAN)
		REFERENCES OPERADOR (CDOPERADOR)
/
CREATE INDEX FKPLA01110OPERADOR ON DETALHESERVPROPCOM (CDRESPONSAVELPLAN)
/
ALTER TABLE DETALHESERVPROPCOM ADD
	CONSTRAINT FKPLA01111SERVICOPROPCOM FOREIGN KEY (NRSERVICOPROPCOM)
		REFERENCES SERVICOPROPCOM (NRSERVICOPROPCOM)
/
CREATE INDEX FKPLA01111SERVICOPROPCOM ON DETALHESERVPROPCOM (NRSERVICOPROPCOM)
/
ALTER TABLE DETALHESERVPROPCOM ADD
	CONSTRAINT FKPLA01112CARDAPIOTRAB FOREIGN KEY (NRCARDAPIOTRAB)
		REFERENCES CARDAPIOTRAB (NRCARDAPIOTRAB)
/
CREATE INDEX FKPLA01112CARDAPIOTRAB ON DETALHESERVPROPCOM (NRCARDAPIOTRAB)
/
ALTER TABLE SERVICOPROPCOM ADD
	CONSTRAINT FKPLA01113DETALHESERVPROPCOM FOREIGN KEY (NRDETALHESERVPROPCOM)
		REFERENCES DETALHESERVPROPCOM (NRDETALHESERVPROPCOM)
/
CREATE INDEX FKPLA01113DETALHESERVPROPCOM ON SERVICOPROPCOM (NRDETALHESERVPROPCOM)
/
ALTER TABLE SERVICOPROPCOM ADD
	CONSTRAINT FKPLA01114DETALHESERVPROPCOM FOREIGN KEY (NRDETSERVPROPCOMPLAN)
		REFERENCES DETALHESERVPROPCOM (NRDETALHESERVPROPCOM)
/
CREATE INDEX FKPLA01114DETALHESERVPROPCOM ON SERVICOPROPCOM (NRDETSERVPROPCOMPLAN)