CREATE OR REPLACE FUNCTION BUSCA_CDEMISSAO_CLIENTE(P_CDFILIAL  IN ITEMPEDIDO.CDFILIAL%TYPE,
                                                   P_CDCLIENTE IN CLIENTE.CDCLIENTE%TYPE,
                                                   P_CDPRODUTO IN PRODUTO.CDPRODUTO%TYPE)RETURN VARCHAR2
AS
   V_IDUTCDEXTPRONF PARAMFILIAL.IDUTCDEXTPRONF%TYPE;
   V_CDPRODCLIEPED  PRODCLIEPED.CDPRODCLIEPED%TYPE;
BEGIN
   V_CDPRODCLIEPED := NULL;
   BEGIN
      SELECT NVL(PFI.IDUTCDEXTPRONF, 'N')
        INTO V_IDUTCDEXTPRONF
        FROM PARAMFILIAL PFI
       WHERE PFI.CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_IDUTCDEXTPRONF := 'N';
      END;
   END;
   IF V_IDUTCDEXTPRONF = 'S' THEN
      BEGIN
         SELECT P.CDPRODINTE
           INTO V_CDPRODCLIEPED
           FROM PRODUTO P
          WHERE P.CDPRODUTO = P_CDPRODUTO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_CDPRODCLIEPED := NULL;
         END;
      END;
      IF V_CDPRODCLIEPED IS NULL THEN
         BEGIN
            SELECT CDPRODCLIEPED
              INTO V_CDPRODCLIEPED
              FROM PRODCLIEPED
             WHERE CDCLIENTE = P_CDCLIENTE
               AND CDPRODUTO = P_CDPRODUTO;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_CDPRODCLIEPED := NULL;
            END;
         END;
      END IF;
   ELSE
      BEGIN
         SELECT CDPRODCLIEPED
           INTO V_CDPRODCLIEPED
           FROM PRODCLIEPED
          WHERE CDCLIENTE = P_CDCLIENTE
            AND CDPRODUTO = P_CDPRODUTO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_CDPRODCLIEPED := NULL;
         END;
      END;
   END IF;

   RETURN(V_CDPRODCLIEPED);
END;
/