CREATE OR REPLACE FUNCTION RET_MOTIVO_CANCELAMENTO_PED(P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                                       P_NRPEDIDO     IN PEDIDO.NRPEDIDO%TYPE) RETURN VARCHAR2 IS 
                                                       
V_DSMOTCANCCOMISPED VARCHAR2(350);   

BEGIN
   BEGIN 
      SELECT DISTINCT CASE WHEN NVL(MAX(VE.IDCANCCOMISPED),'N') = 'S' AND (MAX(VE.DSMOTCANCCOMISPED) IS NULL OR MAX(VE.DSMOTCANCCOMISPED) = ' ') THEN NVL('MOTIVO DO CANCELAMENTO: MOTIVO NAO INFORMADO //'||MAX(VE.DSMOTCANCCOMISPED),'')||' OPERADOR: '||L.NMRESPOPER||' // DATA: '||L.DTOPERACAO 
                           WHEN NVL(MAX(VE.IDCANCCOMISPED),'N') = 'S' AND MAX(VE.DSMOTCANCCOMISPED) IS NOT NULL THEN NVL('MOTIVO DO CANCELAMENTO: '||SUBSTR(MAX(VE.DSMOTCANCCOMISPED),0,250),'')||' // OPERADOR: '||L.NMRESPOPER||' // DATA: '||L.DTOPERACAO 
                        ELSE '' END AS DSMOTCANCCOMISPED
              INTO V_DSMOTCANCCOMISPED
              FROM VENDITEMPED VE
              LEFT JOIN (SELECT DISTINCT SUBSTR(CDPESQITEM,0,4) AS CDFILIAL, SUBSTR(CDPESQITEM,5,10) AS NRPEDIDO, NMRESPOPER, DTOPERACAO
                           FROM LOGOPERA 
                          WHERE NMTABELA  = 'VENDITEMPED' 
                            AND CDPESQITEM LIKE P_CDFILIAL||P_NRPEDIDO||'%') L
                ON VE.CDFILIAL = L.CDFILIAL
               AND VE.NRPEDIDO = L.NRPEDIDO
             WHERE VE.CDFILIAL = P_CDFILIAL
               AND VE.NRPEDIDO = P_NRPEDIDO
             GROUP BY VE.CDFILIAL, VE.NRPEDIDO, L.NMRESPOPER, L.DTOPERACAO;
   EXCEPTION
        WHEN OTHERS THEN
           V_DSMOTCANCCOMISPED := NULL;
   END;             


   RETURN(V_DSMOTCANCCOMISPED);
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('VALOR_PEDIDO_BRUTO','6.39.021'); END;
/
