CREATE OR REPLACE FUNCTION CUSTO_PROD_LISTADESATU(
P_CDFILIAL IN FILIAL.CDFILIAL%TYPE,
P_CDPRODUTO IN PRODUTO.CDPRODUTO%TYPE,
P_NRTIPOCUSTO IN NUMBER,
P_DTREFERENCIA IN DATE
) RETURN VARCHAR AS

V_CUSTDESATU    VARCHAR2(01);

BEGIN

   IF (P_NRTIPOCUSTO IN (06, 10, 44, 48, 30, 61, 31, 62)) THEN
      BEGIN
         SELECT DISTINCT CUSTDESATU
           INTO V_CUSTDESATU
           FROM (
            SELECT 'N' AS CUSTDESATU
              FROM CUSTOSUP                 
             WHERE CUSTOSUP.CDFILIAL  = P_CDFILIAL
               AND CUSTOSUP.CDPRODUTO = P_CDPRODUTO
               AND P_DTREFERENCIA BETWEEN CUSTOSUP.DTCUSTOSUP AND CUSTOSUP.DTFINCUSTSUP
             UNION ALL
            SELECT 'N' AS CUSTDESATU
              FROM FILIAL, PARASUPRI, PARAMPRODSUP, CUSTOSUP
             WHERE FILIAL.CDFILIAL = P_CDFILIAL
               AND PARASUPRI.CDFILIAL = NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL)
               AND NVL(PARASUPRI.IDUTILCONSSOLIOL, 'N') = 'S'
               AND PARAMPRODSUP.CDFILIAL = P_CDFILIAL
               AND PARAMPRODSUP.CDPRODUTO = P_CDPRODUTO
               AND CUSTOSUP.CDFILIAL = PARAMPRODSUP.CDFILIOPELOG
               AND CUSTOSUP.CDPRODUTO = P_CDPRODUTO
               AND P_DTREFERENCIA BETWEEN CUSTOSUP.DTCUSTOSUP AND CUSTOSUP.DTFINCUSTSUP
           );
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
            V_CUSTDESATU := 'S';
       END;
   ELSE
       V_CUSTDESATU := 'N';
   END IF;
   RETURN(V_CUSTDESATU);
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('CUSTO_PROD_LISTADESATU','5.05.037'); END;
/
