/*
@C:\WEB_407\CLI\SRC\CLI\COAN\SQL\COMPARATIVO_MOVPOS.SQL

 EXECUTE COMPARATIVO_MOVPOS('01/10/2005', '01/06/2006', '1','zzzzzzzzzzzz');

*/
CREATE OR REPLACE PROCEDURE COMPARATIVO_MOVPOS(
                            P_CDFILIAL       IN VARCHAR2 DEFAULT NULL,
                            P_CDALMOXARIFE   IN VARCHAR2 DEFAULT NULL,
                            P_CDLOCALESTOQ   IN VARCHAR2 DEFAULT NULL,
                            P_DTINICIAL      IN DATE,
                            P_DTFINAL        IN DATE,
                            P_CDPRODINI      IN PRODUTO.CDPRODUTO%TYPE,
                            P_CDPRODFIM      IN PRODUTO.CDPRODUTO%TYPE)
IS

   V_NRMOVIESTQ       VARCHAR2(20);
   V_IDTIPOMOVIEST    VARCHAR2(2);
   V_NRLANCMOVIEST    VARCHAR2(10);
   V_NRSEQUMOVIEST    VARCHAR2(10);
   V_QTDEMOVIEST      FLOAT;
   V_VRCUSTMOVIEST    FLOAT;
   V_QTESTOQDIA       FLOAT;
   V_VRESTOQDIA       FLOAT;
   V_COUNT            INTEGER;
   V_PRODUTODIF      VARCHAR2(20);

BEGIN
   FOR C_FILIAL IN(SELECT F.CDFILIAL
                   FROM FILIAL F, PARAMFILIAL P
                   WHERE IDCNTRRESC = 'N'        AND
                         F.CDFILIAL = NVL(P_CDFILIAL, F.CDFILIAL) AND
                         IDCTRLESTQ = 'S') LOOP

      FOR C_LOCALESTOQ IN(SELECT CDALMOXARIFE, CDLOCALESTOQ
                          FROM LOCALESTOQUE
                          WHERE CDFILIAL = C_FILIAL.CDFILIAL  AND
                                CDALMOXARIFE = NVL(P_CDALMOXARIFE, CDALMOXARIFE)  AND
                                CDLOCALESTOQ = NVL(P_CDLOCALESTOQ, CDLOCALESTOQ)) LOOP

         NOVO_CODIGO('MOVIESTOQUE' || C_FILIAL.CDFILIAL, V_NRMOVIESTQ);
         V_NRMOVIESTQ := SUBSTR(V_NRMOVIESTQ, 11, 10);

         MOVIMENTACAO_ESTOQUE(C_FILIAL.CDFILIAL, C_LOCALESTOQ.CDALMOXARIFE, C_LOCALESTOQ.CDLOCALESTOQ, ' ', 1,
                             P_DTINICIAL,  P_DTFINAL, P_CDPRODINI, P_CDPRODFIM, 3, 1, V_NRMOVIESTQ);
         DELETE FROM RELAT_DIFMOVEST
         WHERE CDFILIAL       = C_FILIAL.CDFILIAL                   AND
               CDALMOXARIFE   = C_LOCALESTOQ.CDALMOXARIFE           AND
               CDLOCALESTOQ   = C_LOCALESTOQ.CDLOCALESTOQ           AND
               CDPRODMOVIEST  BETWEEN P_CDPRODINI AND P_CDPRODFIM   AND
               DTLANCMOVIEST  BETWEEN P_DTINICIAL AND P_DTFINAL;
         V_COUNT := 0;
         V_PRODUTODIF := '';
         FOR C_ITENS IN(SELECT DISTINCT CDPRODMOVIEST, DTLANCMOVIEST
                        FROM RELAT_MOVIEST R
                        WHERE R.NRRELATMOVI  = V_NRMOVIESTQ    AND
                              CDFILIMOVIEST  = C_FILIAL.CDFILIAL
                        ORDER BY CDPRODMOVIEST, DTLANCMOVIEST) LOOP

            SELECT MAX(NVL(IDORDEMCALC,IDTIPOMOVIEST))
            INTO V_IDTIPOMOVIEST
            FROM RELAT_MOVIEST R
            WHERE R.NRRELATMOVI  = V_NRMOVIESTQ          AND
                  CDFILIMOVIEST  = C_FILIAL.CDFILIAL            AND
                  DTLANCMOVIEST  = C_ITENS.DTLANCMOVIEST AND
                  CDPRODMOVIEST  = C_ITENS.CDPRODMOVIEST;

            SELECT MAX(NRLANCMOVIEST), MAX(NRSEQUMOVIEST)
            INTO V_NRLANCMOVIEST, V_NRSEQUMOVIEST
            FROM RELAT_MOVIEST R
            WHERE R.NRRELATMOVI  = V_NRMOVIESTQ           AND
                  CDFILIMOVIEST  = C_FILIAL.CDFILIAL             AND
                  DTLANCMOVIEST  = C_ITENS.DTLANCMOVIEST  AND
                  CDPRODMOVIEST  = C_ITENS.CDPRODMOVIEST  AND
                  IDTIPOMOVIEST  = V_IDTIPOMOVIEST;

            SELECT MAX(QTDEMOVIEST), MAX(VRCUSTMOVIEST)
            INTO V_QTDEMOVIEST, V_VRCUSTMOVIEST
            FROM RELAT_MOVIEST R
            WHERE R.NRRELATMOVI  = V_NRMOVIESTQ             AND
                  CDFILIMOVIEST  = C_FILIAL.CDFILIAL               AND
                  DTLANCMOVIEST  = C_ITENS.DTLANCMOVIEST    AND
                  CDPRODMOVIEST  = C_ITENS.CDPRODMOVIEST    AND
                  NRLANCMOVIEST  = V_NRLANCMOVIEST          AND
                  NRSEQUMOVIEST  = V_NRSEQUMOVIEST;


            SELECT SUM(QTESTOQDIA),
                   SUM(VRESTOQDIA)
            INTO V_QTESTOQDIA, V_VRESTOQDIA
            FROM POSESTDIA P, PRODUTO O
            WHERE CDFILIAL       = C_FILIAL.CDFILIAL                   AND
                  O.CDARVPROD    = C_ITENS.CDPRODMOVIEST        AND
                  P.CDPRODUTO    = O.CDPRODUTO                  AND
                  CDALMOXARIFE   = C_LOCALESTOQ.CDALMOXARIFE    AND
                  CDLOCALESTOQ   = C_LOCALESTOQ.CDLOCALESTOQ    AND
                  NRLOTEESTQ     = ' '                          AND
                  NRSUBLOTE      = ' '                          AND
                  DTPOSIESTQ     = C_ITENS.DTLANCMOVIEST + 1;

            IF ((V_QTESTOQDIA <> V_QTDEMOVIEST) OR (ABS(V_VRESTOQDIA - V_VRCUSTMOVIEST)> 0.01)) AND (C_ITENS.CDPRODMOVIEST <> V_PRODUTODIF) THEN
               V_COUNT := V_COUNT + 1 ;

               V_PRODUTODIF := C_ITENS.CDPRODMOVIEST;

               INSERT INTO RELAT_DIFMOVEST
                  (CDFILIAL, CDPRODMOVIEST, CDALMOXARIFE, CDLOCALESTOQ ,DTLANCMOVIEST, QTESTOQDIA,
                   QTDEMOVIEST, VRESTOQDIA, VRCUSTMOVIEST)
               VALUES
                  (C_FILIAL.CDFILIAL, C_ITENS.CDPRODMOVIEST, C_LOCALESTOQ.CDALMOXARIFE, C_LOCALESTOQ.CDLOCALESTOQ,C_ITENS.DTLANCMOVIEST,
                   V_QTESTOQDIA, V_QTDEMOVIEST, V_VRESTOQDIA, V_VRCUSTMOVIEST );
               IF V_COUNT > 500 THEN
                  COMMIT;
                  V_COUNT := 0;
               END IF;
            END IF;
         END LOOP;
         DELETE FROM RELAT_MOVIEST
         WHERE NRRELATMOVI   = V_NRMOVIESTQ          AND
               CDFILIMOVIEST = C_FILIAL.CDFILIAL;
      END LOOP;
   END LOOP;
END;

/
/*
CREATE TABLE RELAT_DIFMOVEST
(CDFILIAL         VARCHAR2(04)           not null,
 CDPRODMOVIEST    VARCHAR2(20)           not null,
 CDALMOXARIFE     VARCHAR2(6)            not null,
 CDLOCALESTOQ    VARCHAR2(6)             not null,
 DTLANCMOVIEST    DATE,
 QTESTOQDIA       FLOAT,
 QTDEMOVIEST      FLOAT,
 VRESTOQDIA       FLOAT,
 VRCUSTMOVIEST    FLOAT,
 constraint PK_RELAT_DIFMOVEST primary key (CDFILIAL, CDPRODMOVIEST, CDALMOXARIFE,
                                            CDLOCALESTOQ, DTLANCMOVIEST))

*/

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

