CREATE OR REPLACE FUNCTION BUSCA_LOCAL_RETIRADA_CDP_MP(P_CDFILIAL  IN FILIAL.CDFILIAL%TYPE,
                                                       P_CDPRODUTO IN PRODUTO.CDPRODUTO%TYPE,
                                                       P_IDPRODPILOTO IN ORDPRODUCAO.IDPRODPILOTO%TYPE)RETURN VARCHAR2
AS
   V_IDCTRLESTQ      PARAMFILIAL.IDCTRLESTQ%TYPE;
   V_IDUTILLCESTQ    PARAMFILIAL.IDUTILLCESTQ%TYPE;
   V_CDALMOXINSPRPIL ALMOXARIFADO.CDALMOXARIFE%TYPE;
   V_CDLOCALINSPRPIL LOCALESTOQUE.CDLOCALESTOQ%TYPE;
   V_CDALMOXINSPR    ALMOXARIFADO.CDALMOXARIFE%TYPE;
   V_CDLOCALINSPR    LOCALESTOQUE.CDLOCALESTOQ%TYPE;
BEGIN
   SELECT MAX(IDCTRLESTQ), MAX(IDUTILLCESTQ)
     INTO V_IDCTRLESTQ,    V_IDUTILLCESTQ
     FROM PARAMFILIAL
    WHERE CDFILIAL = P_CDFILIAL;

   IF(V_IDCTRLESTQ <> 'N')  AND (V_IDUTILLCESTQ = 'S') THEN
      SELECT MAX(CDALMOXINSPRPIL), MAX(CDLOCALINSPRPIL), MAX(CDALMOXINSPR), MAX(CDLOCALINSPR)
        INTO V_CDALMOXINSPRPIL,    V_CDLOCALINSPRPIL,    V_CDALMOXINSPR,    V_CDLOCALINSPR
        FROM PRODFILI PF JOIN PRODUTO P
                           ON PF.CDPRODUTO = P.CDPRODESTO
       WHERE PF.CDFILIAL = P_CDFILIAL
         AND P.CDPRODUTO = P_CDPRODUTO;

      IF (P_IDPRODPILOTO = 'S') THEN
         IF (V_CDLOCALINSPRPIL IS NOT NULL) AND (V_CDALMOXINSPRPIL IS NOT NULL) THEN
            RETURN(V_CDLOCALINSPRPIL);
         ELSE
            RETURN(NULL);
         END IF;
      ELSE
         IF (V_CDLOCALINSPR IS NOT NULL) AND (V_CDALMOXINSPR IS NOT NULL) THEN
            RETURN(V_CDLOCALINSPR);
         ELSE
            RETURN(NULL);
         END IF;
      END IF;
   ELSE
      RETURN(' ');
   END IF;
END;
/