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_PEDIDOOK   OUT  VARCHAR2)
IS
   V_QTITEMPEDTOTAL  ITEMPEDIDO.QTITEMPED%TYPE;
   V_CDALMOXARIFE    ALMOXARIFADO.CDALMOXARIFE%TYPE;
   V_CDLOCALESTOQ    LOCALESTOQUE.CDLOCALESTOQ%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;


BEGIN
/* P_PEDIDOOK
   S - OK
   N - INCONSISTENTE
*/

   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;

   FOR C_ITEM IN (SELECT NRITEMPED, CDPRODUTO, (QTITEMPED - QTEXPITEMPED) QTEXPEDIR, VRMARGITPED
                    FROM ITEMPEDIDO
                   WHERE CDFILIAL  = P_CDFILIAL
                     AND NRPEDIDO  = P_NRPEDIDO
                     AND ((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', '6');
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_QTITEMPEDTOTAL := 0;
      END;

      BEGIN
         SELECT CDALMOXPRCLI
           INTO V_CDALMOXARIFE
           FROM PRODFILI
          WHERE CDFILIAL  = P_CDFILIAL
            AND CDPRODUTO = C_ITEM.CDPRODUTO;
      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;

      BEGIN
         SELECT CDLOCALPRCLI
           INTO V_CDLOCALESTOQ
           FROM PRODFILI
          WHERE CDFILIAL = P_CDFILIAL
            AND CDPRODUTO = C_ITEM.CDPRODUTO;
      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;

      BEGIN
         SELECT SUM(POSESTATU.QTTOTALESTOQ) 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.CDPRODUTO
            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.QTEXPEDIR, 3) > ROUND(V_QTDISPONIVEL, 3) THEN
         GRAVA_INCONSPEDIDO(P_CDFILIAL, P_NRPEDIDO, C_ITEM.NRITEMPED, 'EST');
         UPDATE ITEMPEDIDO
            SET IDCONSITEM = 'N'
          WHERE CDFILIAL  = P_CDFILIAL
            AND NRPEDIDO  = P_NRPEDIDO
            AND NRITEMPED = C_ITEM.NRITEMPED;
      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');
            UPDATE ITEMPEDIDO
               SET IDCONSITEM = 'N'
             WHERE CDFILIAL  = P_CDFILIAL
               AND NRPEDIDO  = P_NRPEDIDO
               AND NRITEMPED = C_ITEM.NRITEMPED;
         END IF;
      END IF;
   END LOOP;
   V_CONSISTEPED := 'S';
   BEGIN
      SELECT MAX(IDCONSITEM)
        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;
   P_PEDIDOOK := V_CONSISTEPED;
END;
/