create or replace PROCEDURE CONSISTE_PEDIDO (
       P_CDFILIAL  IN  ITEMPEDIDO.CDFILIAL%TYPE,
       P_NRPEDIDO  IN  ITEMPEDIDO.NRPEDIDO%TYPE  DEFAULT NULL,
       P_NRITEMPED IN  ITEMPEDIDO.NRITEMPED%TYPE DEFAULT NULL,
       P_CDCLIENTE IN  CLIENTE.CDCLIENTE%TYPE DEFAULT NULL,
       P_TXMENSAGEM OUT  VARCHAR2,
       P_PEDIDOOK   OUT  VARCHAR2)
IS
   V_QTITEMPEDTOTAL  ITEMPEDIDO.QTITEMPED%TYPE;
   V_CDALMOXARIFE    ALMOXARIFADO.CDALMOXARIFE%TYPE;
   V_CDLOCALESTOQ    LOCALESTOQUE.CDLOCALESTOQ%TYPE;
   V_IDUTILALMOX     PARAMFILIAL.IDUTILALMOX%TYPE;
   V_IDUTILLCESTQ    PARAMFILIAL.IDUTILLCESTQ%TYPE;
   V_QTTOTALESTOQ    POSESTATU.QTTOTALESTOQ%TYPE;
   V_QTDISPONIVEL    POSESTATU.QTTOTALESTOQ%TYPE;
   V_CONSISTEPED     PEDIDO.IDCONSISTEPED%TYPE;
   V_CDPRECOBASE     PRECOBASE.CDPRECOBASE%TYPE;
   V_CDCLIENTE       CLIENTE.CDCLIENTE%TYPE;
   V_IDULTPRECPROMO  PRECOBASE.IDULTPRECPROMO%TYPE;
   V_VRMARGEM        MARGEMITPREBASE.VRMARGEM%TYPE;
   V_IDVERICONSPED   PARAMPEDIDO.IDVERICONSPED%TYPE;
   V_IDCONSLIBERADA  INCONSPEDIDO.IDCONSLIBERADA%TYPE;
BEGIN
/* P_PEDIDOOK
   S - OK
   N - INCONSISTENTE
*/
   V_CONSISTEPED := 'S';
   P_TXMENSAGEM := '';
   BEGIN
      SELECT NVL(MAX(IDVERICONSPED), 'N')
        INTO V_IDVERICONSPED
        FROM PARAMPEDIDO
       WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDVERICONSPED := 'N';
   END;
   IF (V_IDVERICONSPED = 'S') THEN
      BEGIN
         SELECT MAX(CDPRECOBASE), MAX(CDCLIENTE)
           INTO V_CDPRECOBASE, V_CDCLIENTE
           FROM PEDIDO
          WHERE CDFILIAL = P_CDFILIAL
            AND NRPEDIDO = P_NRPEDIDO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDPRECOBASE := NULL;
            V_CDCLIENTE   := NULL;
      END;
      BEGIN
         SELECT MAX(IDULTPRECPROMO)
           INTO V_IDULTPRECPROMO
           FROM PRECOBASE
          WHERE CDPRECOBASE = V_CDPRECOBASE;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_IDULTPRECPROMO := 'P';
      END;
      UPDATE ITEMPEDIDO
         SET IDCONSITEM = 'S'
       WHERE CDFILIAL  = P_CDFILIAL
         AND NRPEDIDO  = P_NRPEDIDO;
      DELETE FROM INCONSPEDIDO
            WHERE CDFILIAL  = P_CDFILIAL
              AND NRPEDIDO  = P_NRPEDIDO
              AND IDCONSLIBERADA = 'N';
      FOR C_ITEM IN (SELECT I.NRITEMPED, I.CDPRODUTO, (I.QTITEMPED - I.QTEXPITEMPED) QTEXPEDIR, I.VRMARGITPED,
                            P.CDPRODESTO, P.VRFATOCONV, ((I.QTITEMPED - I.QTEXPITEMPED)*P.VRFATOCONV) QTEXPEDIREST
                       FROM ITEMPEDIDO I JOIN PRODUTO P
                                           ON I.CDPRODUTO = P.CDPRODUTO
                      WHERE I.CDFILIAL  = P_CDFILIAL
                        AND I.NRPEDIDO  = P_NRPEDIDO
                        AND ((I.NRITEMPED = P_NRITEMPED) OR (P_NRITEMPED = 'T'))) LOOP
         BEGIN
            SELECT SUM(QTITEMPED - QTEXPITEMPED)
              INTO V_QTITEMPEDTOTAL
              FROM ITEMPEDIDO
             WHERE CDFILIAL = P_CDFILIAL
               AND CDPRODUTO = C_ITEM.CDPRODUTO
               AND IDSITITEMPED IN ('1', '2', '3', '6');
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_QTITEMPEDTOTAL := 0;
         END;
         V_QTITEMPEDTOTAL := V_QTITEMPEDTOTAL*C_ITEM.VRFATOCONV;
         V_QTITEMPEDTOTAL := V_QTITEMPEDTOTAL-C_ITEM.QTEXPEDIREST;
         BEGIN
            SELECT NVL(IDUTILALMOX, 'N'), NVL(IDUTILLCESTQ, 'N')
              INTO V_IDUTILALMOX, V_IDUTILLCESTQ
              FROM PARAMFILIAL
             WHERE CDFILIAL = P_CDFILIAL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_IDUTILALMOX := 'N';
               V_IDUTILLCESTQ := 'N';
         END;
        IF (V_IDUTILALMOX = 'S') THEN
             BEGIN
                SELECT CDALMOXPRCLI
                  INTO V_CDALMOXARIFE
                  FROM PRODFILI
                 WHERE CDFILIAL  = P_CDFILIAL
                   AND CDPRODUTO = C_ITEM.CDPRODESTO;
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_CDALMOXARIFE := NULL;
             END;
             IF V_CDALMOXARIFE IS NULL THEN
                BEGIN
                  SELECT CDALMOXARIFE
                    INTO V_CDALMOXARIFE
                    FROM PARAMFILIAL
                   WHERE CDFILIAL = P_CDFILIAL;
                EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                      V_CDALMOXARIFE := Null;
                END;
             END IF;
        ELSE
            V_CDALMOXARIFE := ' ';
        END IF;
        IF (V_IDUTILLCESTQ = 'S') THEN
             BEGIN
                SELECT CDLOCALPRCLI
                  INTO V_CDLOCALESTOQ
                  FROM PRODFILI
                 WHERE CDFILIAL = P_CDFILIAL
                   AND CDPRODUTO = C_ITEM.CDPRODESTO;
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_CDLOCALESTOQ := Null;
             END;
             IF V_CDLOCALESTOQ IS NULL THEN
                BEGIN
                  SELECT CDLOCALESTOQ
                    INTO V_CDLOCALESTOQ
                    FROM PARAMFILIAL
                   WHERE CDFILIAL = P_CDFILIAL;
                EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                      V_CDLOCALESTOQ := Null;
                END;
             END IF;
        ELSE
            V_CDLOCALESTOQ := ' ';
        END IF;
         BEGIN
            SELECT NVL(SUM(POSESTATU.QTTOTALESTOQ), 0) AS QTTOTALESTOQ
              INTO V_QTTOTALESTOQ
              FROM POSESTATU JOIN LOTE
                               ON POSESTATU.CDFILIAL   = LOTE.CDFILIAL
                              AND POSESTATU.NRLOTEESTQ = LOTE.NRLOTEESTQ
                              AND POSESTATU.NRSUBLOTE  = LOTE.NRSUBLOTE
                              AND POSESTATU.CDPRODUTO  = LOTE.CDPRODUTO
                             JOIN PRODFILI
                               ON POSESTATU.CDFILIAL  = PRODFILI.CDFILIAL
                              AND POSESTATU.CDPRODUTO = PRODFILI.CDPRODUTO
             WHERE POSESTATU.CDFILIAL     = P_CDFILIAL
               AND POSESTATU.CDALMOXARIFE = V_CDALMOXARIFE
               AND POSESTATU.CDLOCALESTOQ = V_CDLOCALESTOQ
               AND POSESTATU.CDPRODUTO    = C_ITEM.CDPRODESTO
               AND POSESTATU.QTTOTALESTOQ > 0
               AND (((PRODFILI.IDUTCTRLLOTE  = 'S') AND (LOTE.DTVALILOTE > SYSDATE)) OR
                    (PRODFILI.IDUTCTRLLOTE  = 'N'));
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_QTTOTALESTOQ := 0;
         END;
         V_QTDISPONIVEL := V_QTTOTALESTOQ - V_QTITEMPEDTOTAL;
         IF ROUND(C_ITEM.QTEXPEDIREST, 3) > ROUND(V_QTDISPONIVEL, 3) THEN
            GRAVA_INCONSPEDIDO(P_CDFILIAL, P_NRPEDIDO, C_ITEM.NRITEMPED, 'EST', 'N');
         END IF;
         IF (V_IDULTPRECPROMO = 'M') THEN
            BEGIN
               SELECT MIN(VRMARGEM)
                 INTO V_VRMARGEM
                 FROM MARGEMITPREBASE
                WHERE CDPRECOBASE = V_CDPRECOBASE
                  AND CDPRODUTO = C_ITEM.CDPRODUTO;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_VRMARGEM := 0;
            END;
            IF (V_VRMARGEM > C_ITEM.VRMARGITPED) THEN
               GRAVA_INCONSPEDIDO(P_CDFILIAL, P_NRPEDIDO, C_ITEM.NRITEMPED, 'MAR', 'N');
            END IF;
         END IF;
      END LOOP;
      V_CONSISTEPED := 'S';
      BEGIN
         SELECT NVL(MAX(IDCONSITEM), 'S')
           INTO V_CONSISTEPED
           FROM ITEMPEDIDO
          WHERE CDFILIAL  = P_CDFILIAL
            AND NRPEDIDO  = P_NRPEDIDO
            AND IDCONSITEM = 'N';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CONSISTEPED := 'S';
      END;
      UPDATE PEDIDO
         SET IDCONSISTEPED = V_CONSISTEPED
       WHERE CDFILIAL  = P_CDFILIAL
         AND NRPEDIDO  = P_NRPEDIDO;
   END IF;
   P_PEDIDOOK := V_CONSISTEPED;
END;