ALTER TABLE CRM_PARAMSERVICE ADD IDRESUEVOL VARCHAR2(1) DEFAULT 'N'
/
ALTER TABLE CRM_PARAMSERVICE MODIFY IDRESUEVOL  VARCHAR2(1) NOT NULL
	CONSTRAINT CKC_IDRESUEVOL_PARSERVI CHECK (IDRESUEVOL IN ('S','N'))
/
CREATE INDEX ENDERECOPARC_1 ON ENDERECOPARC (NRPARCNEGOCIO ASC, CDTIPOENDERECO ASC)
/
CREATE INDEX CRM_CANDIDATO_1 ON CRM_CANDIDATO (NRPARCNEGOCIO ASC)
/
CREATE INDEX FILACONTATO_1 ON FILACONTATO (NRPARCNEGOCIO ASC, DTPLANFILACONTA ASC)
/
CREATE INDEX EVOLCONT_3 ON EVOLCONT (NRSEQCONTATO ASC, DTHREVOLCONT ASC)
/
ALTER TABLE CRM_ANEXOCONTATO ADD DSARQUIVO VARCHAR2(2000)
/
ALTER TABLE CONTATO DROP CONSTRAINT  CKC_IDORIGEMTICKET_CONTATO 
/
ALTER TABLE CONTATO ADD  
	CONSTRAINT CKC_IDORIGEMTICKET_CONTATO CHECK (IDORIGEMTICKET IN ('EMAIL','CRM','SM', 'SD','PORTALCLIENTE', 'PORTALATENDIMENTO','TELFONE', 'CHAT', 'TWITTER', 'FACEBOOK', 'CHATBOT', 'WIDGET'))
/
ALTER TABLE CRM_PARAMETRIZACAO ADD IDEVOLTRANSOPORT VARCHAR2(1) DEFAULT 'S' NOT NULL
	CONSTRAINT CKC_IDEVOLTRANSOPORT_PARAM CHECK (IDEVOLTRANSOPORT IN ('S','N'))
/
CREATE OR REPLACE TRIGGER TG_ATUALIZA_ENDEPROSPECT
AFTER UPDATE
ON ENDERECOPARC
REFERENCING NEW AS NEW 
FOR EACH ROW
BEGIN
    IF :NEW.CDTIPOENDERECO = 'PRINCIPAL'
        THEN UPDATE ENDEPROSPECT SET DTULTATU = SYSDATE,
                                     CDPAIS = :NEW.CDPAIS, 
                                     SGESTADO = :NEW.SGESTADO, 
                                     CDMUNICIPIO = :NEW.CDMUNICIPIO,
                                     CDBAIRRO = :NEW.CDBAIRRO,
                                     DSENDEPROS = :NEW.DSENDERECO,
                                     NMBAIRPROS = :NEW.NMBAIRROENDERECO,
                                     NRCEPPROS = :NEW.NRCEPENDERECO,
                                     NRIMOVELENDERECO = :NEW.NRIMOVELENDERECO
        WHERE IDTPENDEPROS = 'P'
          AND NRORG = :NEW.NRORG
          AND CDPROSPECT = (SELECT CDPROSPECT FROM PROSPECT 
                             WHERE NRPARCNEGOCIO = :NEW.NRPARCNEGOCIO
                               AND NRORG = :NEW.NRORG);
    END IF;    
END;
/
CREATE TABLE AUDALTERDADOS
(  
   NRAUDALTERDADOS 	NUMBER        	NOT NULL,
   DATAALTERACAO   	DATE          	DEFAULT SYSDATE NOT NULL,
   CDOPERADOR      	VARCHAR2(12)  	NOT NULL,
   TABELA          		VARCHAR2(255) 	NOT NULL,
   DSAUDALTERDADOS 	CLOB          	NOT NULL,
   NRORG                       	NUMBER 		DEFAULT 	 SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    	VARCHAR2(1)     	DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_AUDALTERDADOS CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             	DATE                  	NULL,
   DTULTATU                  	DATE                  	NULL,
   NRORGINCLUSAO      	NUMBER             	DEFAULT 1 NULL,
   CDOPERINCLUSAO    	VARCHAR2(12)  	DEFAULT '1' NULL,
   NRORGULTATU          	NUMBER             	DEFAULT 1 NULL,
   CDOPERULTATU        	VARCHAR2(12)  	DEFAULT '1',
	CONSTRAINT PK_AUDALTERDADOS PRIMARY KEY (NRAUDALTERDADOS)
)
/ 