create or replace FUNCTION BUSCA_CNPJ_ESTAB(
      P_ICMS           IN IMPOSTO.CDIMPOSTO%TYPE,
      P_ISS_E          IN IMPOSTO.CDIMPOSTO%TYPE,
      P_ISS_S          IN IMPOSTO.CDIMPOSTO%TYPE,
      P_ISS_RET_E      IN IMPOSTO.CDIMPOSTO%TYPE,
      P_ISS_RET_S      IN IMPOSTO.CDIMPOSTO%TYPE,
      P_NRLANCTONF     IN NOTAFISCAL.NRLANCTONF%TYPE,
      P_CDFILIAL       IN NOTAFISCAL.CDFILIAL%TYPE,
      P_CDINSCESTA     IN INSCRESTAD.CDINSCESTA%TYPE,
      P_CDINSCMUNI     IN INSCRMUNICIP.CDINSCMUNI%TYPE,
      P_CDFILIEMIT     IN NOTAFISCAL.CDFILIAL%TYPE,
      P_CDFILIRECE     IN NOTAFISCAL.CDFILIAL%TYPE,
      P_IDENTRSAIDOP   IN NOTAFISCAL.IDENTRSAIDOP%TYPE)RETURN VARCHAR

AS
   VDIMPICMS       VARCHAR2(2);
   VDIMPISS        VARCHAR2(2);
   V_NRINSJURI     VARCHAR2(30);

BEGIN

   SELECT MAX(ICMS), MAX(ISS)
     INTO VDIMPICMS, VDIMPISS
     FROM (SELECT (CASE WHEN IM.CDIMPOSTONFE = P_ICMS THEN IM.CDIMPOSTONFE ELSE NULL END) AS ICMS,
                  (CASE WHEN IM.CDIMPOSTONFE IN (P_ISS_E,P_ISS_S,P_ISS_RET_E,P_ISS_RET_S) THEN IM.CDIMPOSTONFE ELSE NULL END) AS ISS
             FROM IMPOSTONF I, IMPOSTO IM
            WHERE IM.CDIMPOSTONFE IN (P_ICMS,P_ISS_E,P_ISS_S,P_ISS_RET_E,P_ISS_RET_S)
              AND I.CDIMPOSTO = IM.CDIMPOSTO
              AND NRLANCTONF  = P_NRLANCTONF
              AND CDFILIAL    = P_CDFILIAL);

   /*NOTA MERCADORIA*/
   IF (VDIMPICMS IS NOT NULL) AND (VDIMPISS IS NULL) THEN
      SELECT MAX(LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJURFILI,'.',''),'/',''),'-',''),'\',''),' ',''),14,'0')||'ICM')
        INTO V_NRINSJURI
        FROM FILIAL
       WHERE CDFILIAL = DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT) ;
   /*NOTA SERVICO*/
   ELSIF (VDIMPICMS IS NULL) AND (VDIMPISS IS NOT NULL) THEN
      SELECT MAX(LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(NRINSJURFILI,P_CDINSCMUNI),'.',''),'/',''),'-',''),'\',''),' ',''),14,'0')||'ISS')
        INTO V_NRINSJURI
        FROM FILIAL
       WHERE CDFILIAL = DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT);
   /*OUTROS DOCUMENTOS SEM ICMS E SEM ISS*/
   ELSIF (VDIMPICMS IS NULL) AND (VDIMPISS IS NULL) THEN
      SELECT MAX(LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(NRINSJURFILI,P_CDINSCMUNI),'.',''),'/',''),'-',''),'\',''),' ',''),14,'0')||'XXX')
        INTO V_NRINSJURI
        FROM FILIAL
       WHERE CDFILIAL = DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT);
   END IF;
   RETURN(V_NRINSJURI);
END;
/