CREATE OR REPLACE FUNCTION INGREDIENTES_PRODACAB(
   P_CDFILIAL      IN FILIAL.CDFILIAL%TYPE,
   P_CDPRODUTO     IN ORDPRODUCAO.CDPRODUTO%TYPE,
   P_LISTASEMI     IN VARCHAR2 DEFAULT 'S'
   ) RETURN VARCHAR IS

   V_CDESPEPROD    VARCHAR2(10);
   V_CDTIPOCOMP    VARCHAR2(03);
   V_RESULT        VARCHAR2(8000);
BEGIN
   IF (P_LISTASEMI = 'R') THEN
      FOR C_ORDENA IN (SELECT I.CDPRODINSUMO, NVL(ID.NMTECNICO, P.NMPRODUTO) AS NMPRODUTO, I.NRORDINSUMO, I.QTINSUMO
                         FROM INSUMPROD I, PRODUTO P, INFADICPROD ID
                        WHERE I.CDPRODINSUMO = P.CDPRODUTO
                          AND I.CDPRODINSUMO = ID.CDPRODUTO(+)
                          AND I.CDFILIAL = P_CDFILIAL
                          AND I.CDPRODUTO = P_CDPRODUTO
                       ORDER BY I.QTINSUMO DESC, I.NRORDINSUMO)
      LOOP
         V_RESULT := V_RESULT||C_ORDENA.NMPRODUTO||', ';
      END LOOP;
   ELSE
      SELECT MAX(CDESPEPROD)
        INTO V_CDESPEPROD
        FROM ESPEPROD
       WHERE (CDFILIAL = P_CDFILIAL)
         AND (CDPRODUTO  = P_CDPRODUTO)
         AND (DTVALESPEPRO = (SELECT MAX(DTVALESPEPRO)
                                FROM ESPEPROD
                               WHERE (CDFILIAL = P_CDFILIAL)
                                 AND (CDPRODUTO  = P_CDPRODUTO)
                                 AND (DTVALESPEPRO < SYSDATE)));
      IF V_CDESPEPROD IS NULL THEN
         SELECT MAX(E1.CDESPEPROD)
           INTO V_CDESPEPROD
           FROM ESPEPROD E1
          WHERE (E1.CDPRODUTO  = P_CDPRODUTO)
            AND (E1.DTVALESPEPRO = (SELECT MAX(E2.DTVALESPEPRO)
                                   FROM ESPEPROD E2
                                  WHERE (E1.CDFILIAL = E2.CDFILIAL)
                                    AND (E1.CDPRODUTO  = E2.CDPRODUTO)
                                    AND (E2.DTVALESPEPRO < SYSDATE)));
      END IF;

      IF V_CDESPEPROD IS NOT NULL THEN

         SELECT MAX(TCOMPPRO.CDTIPOCOMP)
           INTO V_CDTIPOCOMP
           FROM TCOMPPRO, TIPOCOMP
          WHERE (TCOMPPRO.CDPRODUTO  = P_CDPRODUTO )
            AND (TCOMPPRO.CDESPEPROD = V_CDESPEPROD)
            AND (TCOMPPRO.CDTIPOCOMP = TIPOCOMP.CDTIPOCOMP)
            AND IDTCOMPPROC = 'S';

         FOR C_INGREDIENTES IN (SELECT C.CDPRODINSUMO, NVL(I.NMTECNICO, P.NMPRODUTO) AS NMPRODUTO, C.QTINSUMO, C.IDSEMIACCOMP
                                  FROM COMPPROD C, PRODUTO P, TIPOINSU T ,  INFADICPROD I
                                 WHERE (C.CDPRODUTO    = P_CDPRODUTO)
                                   AND (C.CDESPEPROD   = V_CDESPEPROD)
                                   AND (C.CDTIPOCOMP   = V_CDTIPOCOMP)
                                   AND (C.CDPRODINSUMO = P.CDPRODUTO)
                                   AND (C.CDPRODINSUMO = I.CDPRODUTO(+))
                                   AND (C.CDTIPOINSU = T.CDTIPOINSU(+))
                                   AND (NVL(T.IDTPMATERIAL, 'XXX') <> 'E')
                                ORDER BY C.QTINSUMO DESC, NRORDINSUMO)
         LOOP
            IF (C_INGREDIENTES.IDSEMIACCOMP = 'S') AND (P_LISTASEMI = 'S') THEN
               V_RESULT := V_RESULT||C_INGREDIENTES.NMPRODUTO||' ('||INGREDIENTES_PRODACAB(P_CDFILIAL, C_INGREDIENTES.CDPRODINSUMO)||'),';
            ELSE
               V_RESULT := V_RESULT||C_INGREDIENTES.NMPRODUTO||', ';
            END IF;
         END LOOP;
      END IF;
   END IF;
   V_RESULT := SUBSTR(V_RESULT,0,INSTR(V_RESULT,',',-1,1)-1);
   RETURN(V_RESULT);
END;
/