CREATE OR REPLACE FUNCTION VALIDA_EXPEDICAO
(P_CDFILIAL       IN PEDIDO.CDFILIAL%TYPE,
 P_NRPEDIDO       IN PEDIDO.NRPEDIDO%TYPE,
 P_CDTIPOOPER     IN PEDIDO.CDTIPOOPER%TYPE     DEFAULT NULL,
 P_CDSERIEENTR    IN SERIEENTR.CDSERIEENTR%TYPE DEFAULT NULL,
 P_CDTIPOOPERENTR IN PEDIDO.CDTIPOOPER%TYPE     DEFAULT NULL,
 P_CDEMPRESA      IN INSCRESTAD.CDEMPRESA%TYPE  DEFAULT NULL,
 P_CDINSCESTA     IN INSCRESTAD.CDINSCESTA%TYPE DEFAULT NULL,
 P_DTENTRSAID     IN NOTAFISCAL.DTENTRSAID%TYPE DEFAULT NULL) RETURN ERROEXPEDICAO_TABLE PIPELINED
IS
   V_CDFILIALPED    PEDIDO.CDFILIALPED%TYPE;
   V_IDGERAREPLIC   PAREXPEDICAO.IDGERAREPLIC%TYPE;
   V_CDTIPOOPER     PEDIDO.CDTIPOOPER%TYPE;
   V_CDTIPOOPERUT   PEDIDO.CDTIPOOPER%TYPE;
   V_IDORIGDESTOP   TIPOOPERAC.IDORIGDESTOP%TYPE;
   V_INSCESTADOK    INTEGER;
   V_CDCLIENTE      CLIENTE.CDCLIENTE%TYPE;
   V_IDEMPRESANFEXP PAREXPEDICAO.IDEMPRESANFEXP%TYPE;
   V_CDEMPRESA      INSCRESTAD.CDEMPRESA%TYPE;
   V_CDINSCESTA     INSCRESTAD.CDINSCESTA%TYPE;
   OUT_RECORD ERROEXPEDICAO_TYPE := ERROEXPEDICAO_TYPE (NULL, NULL, NULL);

BEGIN
   SELECT MAX(CDFILIALPED), MAX(CDTIPOOPER), MAX(CDCLIENTE)
     INTO V_CDFILIALPED,    V_CDTIPOOPER,    V_CDCLIENTE
     FROM PEDIDO
    WHERE CDFILIAL = P_CDFILIAL
      AND NRPEDIDO = P_NRPEDIDO;

   SELECT NVL(MAX(IDGERAREPLIC), '1'), NVL(MAX(IDEMPRESANFEXP),'EMPRESAFILIAL')
     INTO V_IDGERAREPLIC, V_IDEMPRESANFEXP
     FROM PAREXPEDICAO
    WHERE CDFILIAL = P_CDFILIAL;

   V_CDTIPOOPERUT := NVL(P_CDTIPOOPER, V_CDTIPOOPER);

    SELECT NVL(MAX(IDORIGDESTOP), 'C')
      INTO V_IDORIGDESTOP
      FROM TIPOOPERAC
     WHERE (IDENTRSAIDOP = 'S')
       AND (CDTIPOOPER = V_CDTIPOOPERUT);

   IF ((V_CDFILIALPED IS NOT NULL) AND (V_IDGERAREPLIC = '2')) THEN

      IF (V_IDORIGDESTOP <> 'L') THEN
         OUT_RECORD.CDFILIAL := P_CDFILIAL;
         OUT_RECORD.NRPEDIDO := P_NRPEDIDO;
         OUT_RECORD.DSERRO := 'TPNREPLIC';
         PIPE ROW (OUT_RECORD);
      END IF;
      IF (P_CDSERIEENTR IS NULL) THEN
      	OUT_RECORD.CDFILIAL := P_CDFILIAL;
         OUT_RECORD.NRPEDIDO := P_NRPEDIDO;
         OUT_RECORD.DSERRO := 'SERIEENTR';
			PIPE ROW (OUT_RECORD);
		END IF;
		IF (P_CDTIPOOPERENTR IS NULL) THEN
			OUT_RECORD.CDFILIAL := P_CDFILIAL;
			OUT_RECORD.NRPEDIDO := P_NRPEDIDO;
			OUT_RECORD.DSERRO := 'TIPOOPERENTR';
			PIPE ROW (OUT_RECORD);
		END IF;
	END IF;
	IF ((V_IDORIGDESTOP = 'L') AND (V_CDFILIALPED IS NULL)) THEN
	   OUT_RECORD.CDFILIAL := P_CDFILIAL;
	   OUT_RECORD.NRPEDIDO := P_NRPEDIDO;
	   OUT_RECORD.DSERRO := 'PEDNFILDEST';
	   PIPE ROW (OUT_RECORD);
	END IF;

       IF (V_IDEMPRESANFEXP = 'INFORMALANCEXP') THEN
         V_CDEMPRESA  := P_CDEMPRESA;
         V_CDINSCESTA := P_CDINSCESTA;
      ELSIF (V_IDEMPRESANFEXP = 'EMPRESAFILIAL') THEN
         BEGIN
            SELECT CDEMPRESA, CDINSCESTA
              INTO V_CDEMPRESA, V_CDINSCESTA
              FROM FILIAL
             WHERE CDFILIAL = P_CDFILIAL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CDEMPRESA  := NULL;
               V_CDINSCESTA := NULL;
         END;
      ELSIF (V_IDEMPRESANFEXP = 'EMPRESACLIENTE') THEN
          BEGIN
            SELECT CDEMPRESA, CDINSCESTA
              INTO V_CDEMPRESA, V_CDINSCESTA
              FROM CLIENTE
             WHERE CDCLIENTE = V_CDCLIENTE;
          EXCEPTION
             WHEN NO_DATA_FOUND THEN
                V_CDEMPRESA  := NULL;
                V_CDINSCESTA := NULL;
          END;
      END IF;
      V_INSCESTADOK := 0;
      SELECT COUNT(1)
         INTO V_INSCESTADOK
         FROM INSCRESTAD I
        WHERE (I.CDEMPRESA  = V_CDEMPRESA)
          AND (I.CDINSCESTA = V_CDINSCESTA)
          AND (NVL(P_DTENTRSAID, TRUNC(SYSDATE)) >= NVL(NVL(I.DTINIVIGIEST, P_DTENTRSAID), TRUNC(SYSDATE)))
          AND (NVL(P_DTENTRSAID, TRUNC(SYSDATE)) <= NVL(NVL(I.DTFIMVIGIEST, P_DTENTRSAID), TRUNC(SYSDATE)));
      IF (V_INSCESTADOK = 0) THEN
         OUT_RECORD.CDFILIAL := P_CDFILIAL;
    	   OUT_RECORD.NRPEDIDO := P_NRPEDIDO;
	      OUT_RECORD.DSERRO := 'VINGINSCESTA';
   	   PIPE ROW (OUT_RECORD);

      END IF;

END;
/