ALTER TABLE CONTROLSENHA ADD CDSENHALTWEB VARCHAR2(200)
/
ALTER TABLE PARCNEGOCIO ADD NRINSCESTADUAL VARCHAR2(20)
/
ALTER TABLE EVOLCONT DROP CONSTRAINT FKPED00411CONTATO
/
ALTER TABLE CRM_SLAPRODPARCNEG ADD NRSEQSLA NUMBER
/
ALTER TABLE CRM_SLAPRODPARCNEG ADD NRPARCNEGOCIO NUMBER
/
ALTER TABLE CRM_TERMOPORTUNI MODIFY CDOPERULTATU VARCHAR(12)
/
ALTER TABLE CRM_TERMOPORTUNI MODIFY CDOPERINCLUSAO  VARCHAR(12)
/
CREATE OR REPLACE FUNCTION GETSLA
  (P_NRORG IN NUMBER, P_NRPARCNEGOCIO IN NUMBER, P_CDPRODUTO IN NUMBER)
RETURN NUMBER
 
IS
   V_BUCARSLACLIENTE VARCHAR2(01);
   V_BUCARSLAPADARAO VARCHAR2(01);
   V_NAOEXITESLA VARCHAR2(01);
   V_NRSEQSLA NUMBER;
   
BEGIN  
   /* Busca o SLA utilizado pelo Produto/Cliente do Ticket */
   V_BUCARSLACLIENTE := 'N';
   BEGIN
      SELECT NRSEQSLA
        INTO V_NRSEQSLA
        FROM CRM_SLAPRODPARCNEG
       WHERE NRPARCNEGOCIO    = P_NRPARCNEGOCIO
         AND CDPRODUTO        = P_CDPRODUTO
         AND NRORG            = P_NRORG
         AND IDATIVO          = 'S'
         AND ROWNUM           = 1;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_BUCARSLACLIENTE := 'S';
   END;
   /* Busca o SLA utilizado pelo Cliente do Ticket */
   V_BUCARSLAPADARAO := 'N';
   IF V_BUCARSLACLIENTE = 'S' THEN
      BEGIN
         SELECT NRSEQSLA
           INTO V_NRSEQSLA
           FROM CRM_SLAPARCNEGOCIO
          WHERE NRORG         = P_NRORG
            AND NRPARCNEGOCIO = P_NRPARCNEGOCIO
            AND IDATIVO       = 'S'
            AND ROWNUM           = 1;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_BUCARSLAPADARAO := 'S';
      END;
   END IF;
   IF V_BUCARSLAPADARAO = 'S' THEN
      BEGIN
         SELECT NRSEQSLA
           INTO V_NRSEQSLA
           FROM CRM_SLA
          WHERE NRORG       = P_NRORG
            AND IDSLAPADRAO = 'S'
            AND IDATIVO     = 'S'
            AND ROWNUM           = 1;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NAOEXITESLA := 'S';
      END;
   END IF;
   RETURN (V_NRSEQSLA);
END;
/

create or replace TRIGGER GERA_PARCNEGOCIO_CLIENTE
BEFORE INSERT OR UPDATE ON CLIENTE
FOR EACH ROW

DECLARE

   V_NOVOCODIGO    VARCHAR2(20);
   V_INSCJUR       VARCHAR2(20);
   V_NRPARCNEGOCIO VARCHAR2(20);
   V_COUNT 	       NUMBER;
   V_TIPOINSC 	   VARCHAR2(10);
   V_IDATIVO       VARCHAR2(1);

BEGIN

	V_TIPOINSC :=
    CASE :NEW.IDTPIJURCLIE
		WHEN 'J' THEN 'CNPJ'
		WHEN 'F' THEN 'CPF'
		WHEN 'O' THEN 'CODIGO'
		ELSE 'LIVRE'
    END;

	V_IDATIVO :=
	CASE :NEW.IDCLIEATIV
		WHEN 'N' THEN 'N'
		ELSE 'S'
	END;

   IF :NEW.NRPARCNEGOCIO IS NULL THEN

      SELECT MAX(NRINSCRICAOPARC), MAX(NRPARCNEGOCIO) INTO V_INSCJUR, V_NRPARCNEGOCIO
      FROM PARCNEGOCIO
      WHERE NRINSCRICAOPARC = :NEW.NRINSJURCLIE
	  AND NRORG = :NEW.NRORG;

      IF V_INSCJUR IS NULL THEN
         NOVO_CODIGO_SC('PARCNEGOCIO',V_NOVOCODIGO);
         V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO, 11, 10);

         INSERT INTO PARCNEGOCIO
           (NRPARCNEGOCIO, NRORG, CDTIPOPARCPRINCIPAL, CDSEGMER, NMPRINCIPALPARC, NMSECUNDARIPARC,
		    CDTIPOINSCRICAO, NRINSCRICAOPARC, IDATIVO, DTINCLUSAO, IDINSTITUICAO, DTNASCIFUNDPARC,
			NRORGINCLUSAO, CDOPERINCLUSAO, NRINSCESTADUAL)
         VALUES
           (V_NOVOCODIGO, :NEW.NRORG, 'CLIENTE', :NEW.CDSEGMER, :NEW.NMRAZSOCCLIE, :NEW.NMFANTCLIE,
		    V_TIPOINSC, :NEW.NRINSJURCLIE, V_IDATIVO, SYSDATE, 'S', :NEW.DTNASCCLIE,
			:NEW.NRORGINCLUSAO, :NEW.CDOPERINCLUSAO, :NEW.NRINSESTCLIE);

         :NEW.NRPARCNEGOCIO := V_NOVOCODIGO;
      ELSE
         :NEW.NRPARCNEGOCIO := V_NRPARCNEGOCIO;

		 UPDATE PARCNEGOCIO SET CDTIPOPARCPRINCIPAL =  'CLIENTE', CDOPERULTATU = :NEW.CDOPERULTATU
         WHERE NRPARCNEGOCIO = V_NRPARCNEGOCIO
	     AND NRORG = :NEW.NRORG;

      END IF;
   ELSE
      UPDATE PARCNEGOCIO SET CDTIPOINSCRICAO = V_TIPOINSC, DTULTATU = SYSDATE,
			 IDATIVO = V_IDATIVO,
			 CDTIPOPARCPRINCIPAL =  'CLIENTE', IDINSTITUICAO = 'S', CDSEGMER = :NEW.CDSEGMER,
			 NMPRINCIPALPARC = :NEW.NMRAZSOCCLIE, NMSECUNDARIPARC = :NEW.NMFANTCLIE,
			 NRINSCRICAOPARC = :NEW.NRINSJURCLIE, NRORGULTATU = :NEW.NRORGULTATU,
			 CDOPERULTATU = :NEW.CDOPERULTATU, DTNASCIFUNDPARC = :NEW.DTNASCCLIE, NRINSCESTADUAL = :NEW.NRINSESTCLIE
       WHERE NRPARCNEGOCIO = :NEW.NRPARCNEGOCIO
	   AND NRORG = :NEW.NRORG;
   END IF;

   SELECT COUNT(*) INTO V_COUNT
   FROM PARCERIA
   WHERE NRPARCNEGOCIO = :NEW.NRPARCNEGOCIO
   AND CDTIPOPARCEIRO = 'CLIENTE'
   AND NRORG = :NEW.NRORG;

   IF V_COUNT = 0 THEN
      NOVO_CODIGO_SC('PARCERIA', V_NOVOCODIGO);
      V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO, 11, 10);

	  INSERT INTO PARCERIA
        (NRPARCERIA, NRPARCNEGOCIO, CDTIPOPARCEIRO, NRORG)
      VALUES
        (V_NOVOCODIGO, :NEW.NRPARCNEGOCIO, 'CLIENTE', :NEW.NRORG);

   END IF;
END;
/

create or replace TRIGGER GERA_PARCNEGOCIO_PROSPECT
BEFORE INSERT OR UPDATE ON PROSPECT
FOR EACH ROW
DECLARE
   V_NOVOCODIGO    VARCHAR2(20);
   V_INSCJUR       VARCHAR2(20);
   V_NRPARCNEGOCIO VARCHAR2(20);
   V_IDATIVO       VARCHAR2(1);
   V_COUNT         NUMBER;
   V_TIPOINSC      VARCHAR2(10);
   V_TIPOPARC      VARCHAR2(12);
   V_TIPOPRINCIPAL VARCHAR2(30);
BEGIN
 V_TIPOINSC :=
    CASE  :NEW.IDTPIJURPROS
  WHEN 'J' THEN 'CNPJ'
  WHEN 'F' THEN 'CPF'
  WHEN 'O' THEN 'CODIGO'
  ELSE 'LIVRE'
    END;
  BEGIN
    SELECT CDTIPOPARCPRINCIPAL
      INTO V_TIPOPRINCIPAL
      FROM PARCNEGOCIO
     WHERE NRPARCNEGOCIO =  :NEW.NRPARCNEGOCIO
       AND NRORG = :NEW.NRORG;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
     V_TIPOPRINCIPAL := NULL;
  END;
  IF V_TIPOPRINCIPAL = 'CLIENTE' THEN
     V_IDATIVO := 'S';
  ELSE
     V_IDATIVO :=
     CASE :NEW.IDSITUPROS
      WHEN 'I' THEN 'N'
      ELSE 'S'
     END;
  END IF;
   IF :NEW.NRPARCNEGOCIO IS NULL THEN
      SELECT MAX(NRINSCRICAOPARC), MAX(NRPARCNEGOCIO) INTO V_INSCJUR, V_NRPARCNEGOCIO
      FROM PARCNEGOCIO
      WHERE NRINSCRICAOPARC =  :NEW.NRINSJURPROS
        AND NRORG = :NEW.NRORG;
      IF V_INSCJUR IS NULL THEN
         NOVO_CODIGO_SC('PARCNEGOCIO',V_NOVOCODIGO);
         V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO, 11, 10);
         INSERT INTO PARCNEGOCIO
           (NRPARCNEGOCIO, NRORG, CDTIPOPARCPRINCIPAL, CDSEGMER, NMPRINCIPALPARC, NMSECUNDARIPARC,
      CDTIPOINSCRICAO, NRINSCRICAOPARC, IDATIVO, DTINCLUSAO, IDINSTITUICAO, DTNASCIFUNDPARC,
   NRORGINCLUSAO, CDOPERINCLUSAO, NRINSCESTADUAL)
         VALUES
           (V_NOVOCODIGO, :NEW.NRORG, 'PROSPECT', :NEW.CDSEGMER, :NEW.NMRAZSOCPROS, :NEW.NMFANTPROS,
      V_TIPOINSC, :NEW.NRINSJURPROS, V_IDATIVO, SYSDATE, 'S', :NEW.DTNASCPROS,
   :NEW.NRORGINCLUSAO, :NEW.CDOPERINCLUSAO, :NEW.NRINSESTPROS);
         :NEW.NRPARCNEGOCIO := V_NOVOCODIGO;
      ELSE
         :NEW.NRPARCNEGOCIO := V_NRPARCNEGOCIO;
      END IF;
   ELSE
   SELECT COUNT(NRPARCERIA) INTO V_TIPOPARC
   FROM PARCERIA
   WHERE NRPARCNEGOCIO = :NEW.NRPARCNEGOCIO
   AND CDTIPOPARCEIRO = 'CLIENTE'
   AND NRORG = :NEW.NRORG;
   IF V_TIPOPARC = 1 THEN
   V_TIPOPARC := 'CLIENTE';
   ELSE
  SELECT COUNT(NRPARCERIA) INTO V_TIPOPARC
     FROM PARCERIA
     WHERE NRPARCNEGOCIO = :NEW.NRPARCNEGOCIO
     AND CDTIPOPARCEIRO = 'FORNECEDOR'
     AND NRORG = :NEW.NRORG;
  IF  V_TIPOPARC = 1 THEN
   V_TIPOPARC := 'FORNECEDOR';
  ELSE
   V_TIPOPARC := 'PROSPECT';
  END IF;
   END IF;
      UPDATE PARCNEGOCIO SET CDTIPOINSCRICAO = V_TIPOINSC, DTULTATU = SYSDATE, IDATIVO = V_IDATIVO,
    CDTIPOPARCPRINCIPAL =  V_TIPOPARC, IDINSTITUICAO = 'S', CDSEGMER = :NEW.CDSEGMER,
    NMPRINCIPALPARC = :NEW.NMRAZSOCPROS, NMSECUNDARIPARC = :NEW.NMFANTPROS,
    NRINSCRICAOPARC = :NEW.NRINSJURPROS, NRORGULTATU = :NEW.NRORGULTATU,
    CDOPERULTATU = :NEW.CDOPERULTATU, DTNASCIFUNDPARC = :NEW.DTNASCPROS, NRINSCESTADUAL = :NEW.NRINSESTPROS
      WHERE NRPARCNEGOCIO = :NEW.NRPARCNEGOCIO
    AND NRORG = :NEW.NRORG;
   END IF;
   SELECT COUNT(*) INTO V_COUNT
   FROM PARCERIA
   WHERE NRPARCNEGOCIO = :NEW.NRPARCNEGOCIO
   AND CDTIPOPARCEIRO = 'PROSPECT'
   AND NRORG = :NEW.NRORG;
   IF V_COUNT = 0 THEN
      NOVO_CODIGO_SC('PARCERIA', V_NOVOCODIGO);
      V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO, 11, 10);
      INSERT INTO PARCERIA
        (NRPARCERIA, NRPARCNEGOCIO, CDTIPOPARCEIRO, NRORG)
      VALUES
        (V_NOVOCODIGO, :NEW.NRPARCNEGOCIO, 'PROSPECT', :NEW.NRORG);
   END IF;
END;