/*
@C:\WEB_407\CLI\SRC\CLI\COAN\SQL\0002_COMPARATIVO_ESTCONS.SQL
*/
CREATE OR REPLACE PROCEDURE COMPARATIVO_ESTCONS(P_CDFILIAL       IN VARCHAR2  DEFAULT NULL,
                                                P_DTINICIAL      IN DATE,
                                                P_CDPRODINI      IN PRODUTO.CDPRODUTO%TYPE,
                                                P_CDPRODFIM      IN PRODUTO.CDPRODUTO%TYPE)
is
     V_QTESTDIACON  NUMBER;
     V_VRESTDIACON  NUMBER;
     V_CDFILIAL VARCHAR2(06);
     V_CDPRODUTO VARCHAR2(10);
     V_QTPOS NUMBER;
     V_VRPOS NUMBER;
     V_COUNT NUMBER;
BEGIN
   V_COUNT := 0;

   DELETE FROM RELAT_COMPESTCONS
   WHERE CDFILIAL   = NVL(P_CDFILIAL, CDFILIAL)        AND
         DTPOSICAO  > P_DTINICIAL                      AND
         CDPRODUTO IN (SELECT CDPRODUTO FROM PRODUTO
                       WHERE CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIM);  


   FOR C_POSESTCONS IN(SELECT CDFILIAL, P.CDPRODUTO, DTPOSESTCON, QTESTDIACON, VRESTDIACON
                       FROM POSESTCONS P, PRODUTO O
                       WHERE CDFILIAL     = NVL(P_CDFILIAL, CDFILIAL)      AND
                             P.CDPRODUTO  = O.CDPRODUTO                    AND
                             CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIM AND
                             DTPOSESTCON > P_DTINICIAL
                       ORDER BY DTPOSESTCON) LOOP

     SELECT SUM(QTESTOQDIA) QT, SUM(VRESTOQDIA) VR
     INTO V_QTPOS, V_VRPOS
     FROM POSESTDIA P
     WHERE CDFILIAL = C_POSESTCONS.CDFILIAL AND
           CDPRODUTO = C_POSESTCONS.CDPRODUTO  and
           DTPOSIESTQ = (SELECT MAX(A.DTPOSIESTQ)
                         FROM POSESTDIA A
                         WHERE A.CDFILIAL = C_POSESTCONS.CDFILIAL    AND
                               A.CDPRODUTO = P.CDPRODUTO             AND
                               A.CDALMOXARIFE = P.CDALMOXARIFE       AND
                               A.CDLOCALESTOQ = P.CDLOCALESTOQ       AND
                               A.NRLOTEESTQ = P.NRLOTEESTQ           AND
                               A.NRSUBLOTE = P.NRSUBLOTE             AND
                                A.DTPOSIESTQ <= C_POSESTCONS.DTPOSESTCON);

      IF   ABS(ROUND(C_POSESTCONS.VRESTDIACON,2) - ROUND(V_VRPOS,2)) > 1 THEN
         V_COUNT := V_COUNT + 1;
         INSERT INTO RELAT_COMPESTCONS
            (CDFILIAL, CDPRODUTO, DTPOSICAO, QTESTOQDIA, QTESTDIACON, VRESTOQDIA, VRESTDIACON)
         VALUES
            (C_POSESTCONS.CDFILIAL, C_POSESTCONS.CDPRODUTO, C_POSESTCONS.DTPOSESTCON, V_QTPOS,
             C_POSESTCONS.QTESTDIACON, V_VRPOS, C_POSESTCONS.VRESTDIACON);
         COMMIT;
      END IF;
      IF V_COUNT > 200 THEN
         COMMIT;
         V_COUNT := 0;
      END IF;
   END LOOP;

END;
/


-- VERSAO --
BEGIN ATUVEROBJ('COMPARATIVO_ESTCONS','4.09.008'); END;
/


