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;
/

-- VERSAO --
BEGIN ATUVEROBJ('GERA_PARCNEGOCIO_PROSPECT','0.00.000'); END;
/
