CREATE OR REPLACE PROCEDURE MOVIMENTACAO_ESTOQUE_K280(
   P_CDEMPRESA    IN FILIAL.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN FILIAL.CDINSCESTA%TYPE,
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE,
   P_SEQUMOVI     IN RELAT_MOVIEST_K280.NRRELATMOVI%TYPE)

IS
   V_CDALMOXLCEST VARCHAR2(12);
   V_CDPRODUTO    VARCHAR2(20);
   V_NRLOTEESTQ   VARCHAR2(20);
   V_NRSUBLOTE    VARCHAR2(04);

   V_QTATUAL      NUMBER;
   V_QTMOVI       NUMBER;
   V_QTDEANT      NUMBER;
   V_QTMOVANT     NUMBER;

   V_QTDEAJUSTE   NUMBER;
   V_QTPOSINI     NUMBER;

   w_N            CHAR(1) := 'N';
   w_S            CHAR(1) := 'S';

   V_CDALMOXARIFE  VARCHAR2(06);
   V_CDLOCALESTOQ  VARCHAR2(06);
   V_CDFILIAL      VARCHAR2(04);

   CURSOR C_POSESTDIA IS
      SELECT A.CDFILIAL,
             A.CDPRODUTO,
             PR.CDARVPROD,
             A.NRLOTEESTQ AS NRLOTEESTQ,
             A.NRSUBLOTE,
             A.DTPOSIESTQ AS DTLANCAMEN,
             '-' AS IDTIPOLANC,
             '----' AS CDFILLANC,
             '----------' AS NRLANCAMEN,
             '0001' AS NRSEQULANC,
             A.QTESTOQDIA AS QTDEESTO,
             A.QTESTOQDIA AS QTLANCAMEN,
             ' ' AS IDALTCMEDMV,
             0.0 AS QTLANCTOEST,
             '-' AS IDORDEMCLC,
             0.0 AS QTTOTALANTE,
             A.CDALMOXARIFE AS CDALMOXARIFE,
             A.CDLOCALESTOQ AS CDLOCALESTOQ,
             '--' AS CDTIPORETIME,
             '-' AS IDENTRSAIDME,
             '--' AS CDTIPOOPERME
        FROM POSESTDIA A, (SELECT I.CDPRODUTO, P.CDARVPROD, I.CDFILIMOVI, F.CDEMPRESA, F.CDINSCESTA, MAX (I.DTLANCMOVI) AS DTLANCMOVI, TO_CHAR(DTLANCMOVI,'MM/YYYY') AS DTREFERENCIA
                             FROM ITLANCTOEST I, FILIAL F, (SELECT CDPRODUTO, CDARVPROD FROM PRODUTO
                                                             WHERE CDPRODUTO = CDPRODESTO) P
                            WHERE I.IDTIPOMOVI  = '1'
                              AND I.CDFILIMOVI  = F.CDFILIAL
                              AND I.CDPRODMOVI  = P.CDPRODUTO
                              AND F.CDEMPRESA  = P_CDEMPRESA
                              AND F.CDINSCESTA = P_CDINSCESTA
                              AND I.CDFILIMOVI = V_CDFILIAL
                            GROUP BY I.CDPRODUTO, P.CDARVPROD, I.CDFILIMOVI, F.CDEMPRESA, F.CDINSCESTA,  TO_CHAR(DTLANCMOVI,'MM/YYYY')) PR
       WHERE A.CDFILIAL      = V_CDFILIAL
         AND A.CDALMOXARIFE  = V_CDALMOXARIFE
         AND A.CDLOCALESTOQ  = V_CDLOCALESTOQ
         AND A.CDPRODUTO     = PR.CDPRODUTO
         AND PR.DTLANCMOVI   BETWEEN P_DTINICIAL AND P_DTFINAL
         AND PR.CDFILIMOVI   = A.CDFILIAL
         AND A.DTPOSIESTQ    = (SELECT MAX(P.DTPOSIESTQ)
                                  FROM POSESTDIA P
                                 WHERE P.CDFILIAL     = A.CDFILIAL
                                   AND P.CDPRODUTO    = A.CDPRODUTO
                                   AND P.CDALMOXARIFE = A.CDALMOXARIFE
                                   AND P.CDLOCALESTOQ = A.CDLOCALESTOQ
                                   AND P.NRLOTEESTQ   = A.NRLOTEESTQ
                                   AND P.NRSUBLOTE    = A.NRSUBLOTE
                                   AND P.DTPOSIESTQ   <= P_DTINICIAL);

   CURSOR C_ITLANCTOEST IS
      SELECT D.CDFILIMOVI AS CDFILIAL,
             PR.CDPRODUTO,
             PR.CDARVPROD,
             D.NRLOTEESTQ,
             D.NRSUBLOTE,
             D.DTLANCMOVI AS DTLANCAMEN,
             D.IDTIPOMOVI AS IDTIPOLANC,
             D.CDFILIAL AS CDFILLANC,
             D.NRLANCESTQ AS NRLANCAMEN,
             D.NRSEQUITEM AS NRSEQULANC,
             0.0 AS QTDEESTO,
             0.0 AS QTLANCAMEN,
             D.IDALTCMEDMV,
             D.QTLANCTOEST,
             D.IDORDEMCLC,
             SUBSTR(P_SEQUMOVI,1,10) AS NRRELATMOVI,
             0.0 AS QTTOTALANTE,
             D.CDALMOXARIFE,
             D.CDLOCALESTOQ,
             NVL(D.CDTIPORETI  ,'--') AS CDTIPORETIME,
             NVL(D.IDENTRSAIDOP,'-') AS IDENTRSAIDME,
             NVL(D.CDTIPOOPER  ,'--') AS CDTIPOOPERME
        FROM PRODFILI C, ITLANCTOEST D, (SELECT I.CDPRODUTO, P.CDARVPROD, I.CDFILIMOVI, F.CDEMPRESA, F.CDINSCESTA, MAX (I.DTLANCMOVI) AS DTLANCMOVI, TO_CHAR(DTLANCMOVI,'MM/YYYY') AS DTREFERENCIA
                                           FROM ITLANCTOEST I, FILIAL F, (SELECT CDPRODUTO, CDARVPROD FROM PRODUTO
                                                                           WHERE CDPRODUTO = CDPRODESTO) P
                                          WHERE I.IDTIPOMOVI  = '1'
                                            AND I.CDFILIMOVI  = F.CDFILIAL
                                            AND I.CDPRODMOVI  = P.CDPRODUTO
                                            AND F.CDEMPRESA  = P_CDEMPRESA
                                            AND F.CDINSCESTA = P_CDINSCESTA
                                            AND I.CDFILIMOVI = V_CDFILIAL
                                          GROUP BY I.CDPRODUTO, P.CDARVPROD, I.CDFILIMOVI, F.CDEMPRESA, F.CDINSCESTA,  TO_CHAR(DTLANCMOVI,'MM/YYYY')) PR
       WHERE D.CDFILIMOVI   = V_CDFILIAL
         AND D.CDALMOXARIFE = V_CDALMOXARIFE
         AND D.CDLOCALESTOQ = V_CDLOCALESTOQ
         AND D.CDPRODMOVI   = PR.CDPRODUTO
         AND PR.CDEMPRESA   = P_CDEMPRESA
         AND PR.CDINSCESTA  = P_CDINSCESTA
         AND PR.DTLANCMOVI  BETWEEN P_DTINICIAL AND P_DTFINAL
         AND PR.CDFILIMOVI  = D.CDFILIMOVI
         AND NVL(IDEVOLESTOQ,'S') = 'S'
         AND D.CDFILIMOVI   = C.CDFILIAL(+)
         AND D.CDPRODMOVI   = C.CDPRODUTO(+)
         AND NVL(C.IDCNTRESTOQ,'S') = 'S'
         AND D.DTLANCMOVI   BETWEEN P_DTINICIAL AND P_DTFINAL
         AND D.IDTIPOMOVI   <> '0';

   CURSOR C_IMPLANTACAO IS
      SELECT D.CDFILIMOVI AS CDFILIAL,
             PR.CDPRODUTO,
             PR.CDARVPROD,
             D.NRLOTEESTQ,
             D.NRSUBLOTE,
             D.DTLANCMOVI AS DTLANCAMEN,
             D.IDTIPOMOVI AS IDTIPOLANC,
             D.CDFILIAL AS CDFILLANC,
             D.NRLANCESTQ AS NRLANCAMEN,
             '0000' AS NRSEQULANC,
             0.0 AS QTDEESTO,
             0.0 AS QTLANCAMEN,
             D.IDALTCMEDMV,
             SUM(QTLANCTOEST) AS QTLANCTOEST,
             D.IDORDEMCLC,
             SUBSTR(P_SEQUMOVI,1,10) AS NRRELATMOVI,
             0.0 AS QTTOTALANTE,
             D.CDALMOXARIFE,
             D.CDLOCALESTOQ,
             NVL(D.CDTIPORETI  ,'--') AS CDTIPORETIME,
             NVL(D.IDENTRSAIDOP,'-') AS IDENTRSAIDME,
             NVL(D.CDTIPOOPER  ,'--') AS CDTIPOOPERME
        FROM PRODFILI C, ITLANCTOEST D, (SELECT I.CDPRODUTO, P.CDARVPROD, I.CDFILIMOVI, F.CDEMPRESA, F.CDINSCESTA, MAX (I.DTLANCMOVI) AS DTLANCMOVI, TO_CHAR(DTLANCMOVI,'MM/YYYY') AS DTREFERENCIA
                                           FROM ITLANCTOEST I, FILIAL F, (SELECT CDPRODUTO, CDARVPROD FROM PRODUTO
                                                                           WHERE CDPRODUTO = CDPRODESTO) P
                                          WHERE I.IDTIPOMOVI  = '1'
                                            AND I.CDFILIMOVI  = F.CDFILIAL
                                            AND I.CDPRODMOVI  = P.CDPRODUTO
                                            AND F.CDEMPRESA  = P_CDEMPRESA
                                            AND F.CDINSCESTA = P_CDINSCESTA
                                            AND I.CDFILIMOVI = V_CDFILIAL
                                          GROUP BY I.CDPRODUTO, P.CDARVPROD, I.CDFILIMOVI, F.CDEMPRESA, F.CDINSCESTA,  TO_CHAR(DTLANCMOVI,'MM/YYYY')) PR
       WHERE D.CDFILIMOVI   = V_CDFILIAL
         AND D.CDALMOXARIFE = V_CDALMOXARIFE
         AND D.CDLOCALESTOQ = V_CDLOCALESTOQ
         AND D.CDPRODMOVI   = PR.CDPRODUTO
         AND PR.CDEMPRESA   = P_CDEMPRESA
         AND PR.CDINSCESTA  = P_CDINSCESTA
         AND PR.DTLANCMOVI  BETWEEN P_DTINICIAL AND P_DTFINAL
         AND PR.CDFILIMOVI  = D.CDFILIMOVI
         AND NVL(IDEVOLESTOQ,'S') = 'S'
         AND D.CDFILIMOVI   = C.CDFILIAL(+)
         AND D.CDPRODMOVI   = C.CDPRODUTO(+)
         AND NVL(C.IDCNTRESTOQ,'S') = 'S'
         AND D.DTLANCMOVI   BETWEEN P_DTINICIAL AND P_DTFINAL
         AND D.IDTIPOMOVI   = '0' --Implantacao de estoque
       GROUP BY D.CDFILIMOVI, PR.CDPRODUTO, PR.CDARVPROD, D.NRLOTEESTQ, D.NRSUBLOTE, D.DTLANCMOVI,
                D.IDTIPOMOVI, D.CDFILIAL, D.NRLANCESTQ, D.IDALTCMEDMV, D.IDORDEMCLC, D.CDPRODMOVI,
                D.CDALMOXARIFE, D.CDLOCALESTOQ, D.CDTIPORETI,
                D.IDENTRSAIDOP, D.CDTIPOOPER;

BEGIN

---------------------------------------------------------------------------------
-- DELETA OS REGISTROS DAS TABELA TEMPORARIAS                                  --
---------------------------------------------------------------------------------
   DELETE FROM RELAT_MOVIEST_K280
    WHERE CDFILIMOVIEST IN (SELECT CDFILIAL
                              FROM FILIAL
                             WHERE CDEMPRESA  = P_CDEMPRESA
                               AND CDINSCESTA = P_CDINSCESTA)
      AND NRRELATMOVI = P_SEQUMOVI;

   FOR C_FILIAIS IN (SELECT DISTINCT F.CDFILIAL, L.CDALMOXARIFE, L.CDLOCALESTOQ
                       FROM FILIAL F, LOCALESTOQUE L
                      WHERE F.CDEMPRESA  = P_CDEMPRESA
                        AND F.CDINSCESTA = P_CDINSCESTA
                        AND L.CDFILIAL   = F.CDFILIAL
                      ORDER BY F.CDFILIAL, L.CDALMOXARIFE, L.CDLOCALESTOQ)
   LOOP

      V_CDALMOXARIFE := C_FILIAIS.CDALMOXARIFE;
      V_CDLOCALESTOQ := C_FILIAIS.CDLOCALESTOQ;
      V_CDFILIAL     := C_FILIAIS.CDFILIAL;

      ---------------------------------------------------------------------------------
      -- INSERE PRODUTOS NA TABELA RELAT_MOVIEST_K280 DA POSIESTDIA E ITLANCTOEST    --
      ---------------------------------------------------------------------------------

      FOR C_MOVIEST IN C_POSESTDIA LOOP
         GRAVA_RELAT_MOVIEST_K280(C_MOVIEST.CDFILIAL,     C_MOVIEST.CDPRODUTO,    C_MOVIEST.CDARVPROD,    C_MOVIEST.NRLOTEESTQ,   C_MOVIEST.NRSUBLOTE,
                                  C_MOVIEST.DTLANCAMEN,   C_MOVIEST.IDTIPOLANC,   C_MOVIEST.CDFILLANC,    C_MOVIEST.NRLANCAMEN,   C_MOVIEST.NRSEQULANC,
                                  C_MOVIEST.QTDEESTO,     C_MOVIEST.QTLANCAMEN,   C_MOVIEST.IDALTCMEDMV,  C_MOVIEST.QTLANCTOEST,  C_MOVIEST.IDORDEMCLC,
                                  P_SEQUMOVI,             C_MOVIEST.QTTOTALANTE,  C_MOVIEST.CDALMOXARIFE, C_MOVIEST.CDLOCALESTOQ, C_MOVIEST.CDTIPORETIME,
                                  C_MOVIEST.IDENTRSAIDME, C_MOVIEST.CDTIPOOPERME);
      END LOOP;

      FOR C_MOVIEST IN C_ITLANCTOEST LOOP
         GRAVA_RELAT_MOVIEST_K280(C_MOVIEST.CDFILIAL,     C_MOVIEST.CDPRODUTO,    C_MOVIEST.CDARVPROD,    C_MOVIEST.NRLOTEESTQ,   C_MOVIEST.NRSUBLOTE,
                                  C_MOVIEST.DTLANCAMEN,   C_MOVIEST.IDTIPOLANC,   C_MOVIEST.CDFILLANC,    C_MOVIEST.NRLANCAMEN,   C_MOVIEST.NRSEQULANC,
                                  C_MOVIEST.QTDEESTO,     C_MOVIEST.QTLANCAMEN,   C_MOVIEST.IDALTCMEDMV,  C_MOVIEST.QTLANCTOEST,  C_MOVIEST.IDORDEMCLC,
                                  P_SEQUMOVI,             C_MOVIEST.QTTOTALANTE,  C_MOVIEST.CDALMOXARIFE, C_MOVIEST.CDLOCALESTOQ, C_MOVIEST.CDTIPORETIME,
                                  C_MOVIEST.IDENTRSAIDME, C_MOVIEST.CDTIPOOPERME);
      END LOOP;

      FOR C_MOVIEST IN C_IMPLANTACAO LOOP
         GRAVA_RELAT_MOVIEST_K280(C_MOVIEST.CDFILIAL,     C_MOVIEST.CDPRODUTO,    C_MOVIEST.CDARVPROD,    C_MOVIEST.NRLOTEESTQ,   C_MOVIEST.NRSUBLOTE,
                                  C_MOVIEST.DTLANCAMEN,   C_MOVIEST.IDTIPOLANC,   C_MOVIEST.CDFILLANC,    C_MOVIEST.NRLANCAMEN,   C_MOVIEST.NRSEQULANC,
                                  C_MOVIEST.QTDEESTO,     C_MOVIEST.QTLANCAMEN,   C_MOVIEST.IDALTCMEDMV,  C_MOVIEST.QTLANCTOEST,  C_MOVIEST.IDORDEMCLC,
                                  P_SEQUMOVI,             C_MOVIEST.QTTOTALANTE,  C_MOVIEST.CDALMOXARIFE, C_MOVIEST.CDLOCALESTOQ, C_MOVIEST.CDTIPORETIME,
                                  C_MOVIEST.IDENTRSAIDME, C_MOVIEST.CDTIPOOPERME);
      END LOOP;

      ---------------------------------------------------------------------------------
      -- INSERE POSICAO INICIAL PARA PRODUTOS                                        --
      ---------------------------------------------------------------------------------
      V_CDPRODUTO    := ' ';
      V_NRLOTEESTQ   := 'X';
      V_NRSUBLOTE    := 'X';
      V_CDALMOXLCEST := ' ';

      FOR C_MOVIMENTA IN(SELECT R.*
                           FROM RELAT_MOVIEST_K280 R
                          WHERE R.NRRELATMOVI   = P_SEQUMOVI
                            AND R.CDFILIMOVIEST = V_CDFILIAL
                          ORDER BY CDPRODMOVIEST, NRLOTEESTQME, NRSUBLOTEME,
                                   DTLANCMOVIEST, NVL(IDORDEMCALC,IDTIPOMOVIEST),
                                   NRLANCMOVIEST, NRSEQUMOVIEST)
      LOOP
         IF (C_MOVIMENTA.IDTIPOMOVIEST <> '-')          AND
            ((V_CDPRODUTO  <> C_MOVIMENTA.CDPRODMOVIEST) OR
             (V_NRLOTEESTQ <> C_MOVIMENTA.NRLOTEESTQME)  OR
             (V_NRSUBLOTE  <> C_MOVIMENTA.NRSUBLOTEME)) THEN

            GRAVA_RELAT_MOVIEST_K280(V_CDFILIAL,  C_MOVIMENTA.CDPRODUTO, C_MOVIMENTA.CDPRODMOVIEST, C_MOVIMENTA.NRLOTEESTQME, C_MOVIMENTA.NRSUBLOTEME,
                                     P_DTINICIAL, '-',                   '----',                    '------',                 '0001',
                                     0,           0,                     ' ',                       0,                        '-',
                                     P_SEQUMOVI,  0,                     C_MOVIMENTA.CDALMOXARIRM,  C_MOVIMENTA.CDLOCALESTRM, NULL,
                                     NULL,        NULL);
            V_QTATUAL  := 0;
            V_QTMOVI   := 0;
            V_QTDEANT  := 0;
         END IF;

         ---------------------------------------------------------------------------------
         -- ALTERA OS VALORES DE ESTOQUE PARA OS PRODUTOS                               --
         ---------------------------------------------------------------------------------
         IF C_MOVIMENTA.IDTIPOMOVIEST = '-' THEN

            V_QTATUAL  := C_MOVIMENTA.QTDEMOVIEST;
            V_QTMOVI   := C_MOVIMENTA.QTLANCAMENTO;
            V_QTDEANT  := V_QTATUAL;
            V_QTMOVANT := V_QTATUAL;
            V_QTPOSINI := V_QTATUAL;

         ELSIF C_MOVIMENTA.IDTIPOMOVIEST = '0' THEN --Implantacao de estoque

            V_QTDEAJUSTE := 0;
            IF V_QTATUAL > 0 THEN
               V_QTMOVANT := V_QTATUAL;
            END IF;
            V_QTMOVI  := C_MOVIMENTA.QTLANCMOVIEST;
            V_QTATUAL := C_MOVIMENTA.QTLANCMOVIEST;

         ELSIF C_MOVIMENTA.IDTIPOMOVIEST = '1' THEN --Ajuste de inventario

            V_QTDEAJUSTE := 0;
            IF V_QTATUAL > 0 THEN
               V_QTMOVANT := V_QTATUAL;
            END IF;
            V_QTMOVI  := C_MOVIMENTA.QTLANCMOVIEST - V_QTATUAL;
            V_QTATUAL := C_MOVIMENTA.QTLANCMOVIEST;

         ELSIF C_MOVIMENTA.IDTIPOMOVIEST = '2' THEN --Entradas no estoque

            V_QTMOVI  := C_MOVIMENTA.QTLANCMOVIEST;
            V_QTMOVANT := V_QTATUAL;
            IF (C_MOVIMENTA.QTLANCMOVIEST <> 0)  OR (C_MOVIMENTA.CDTIPOOPERME = '03') THEN
               V_QTATUAL := V_QTATUAL + V_QTMOVI;
            END IF;

         ELSIF C_MOVIMENTA.IDTIPOMOVIEST = '3' THEN --Retiradas de estoque

            V_QTMOVI   := C_MOVIMENTA.QTLANCMOVIEST;
            V_QTMOVANT := V_QTATUAL;
            V_QTATUAL := V_QTATUAL - V_QTMOVI;

         ELSIF C_MOVIMENTA.IDTIPOMOVIEST = '4' THEN --Saidas de estoque

            V_QTMOVI     := C_MOVIMENTA.QTLANCMOVIEST;
            V_QTMOVANT   := V_QTATUAL;
            V_QTATUAL  := V_QTATUAL - V_QTMOVI;

         ELSIF C_MOVIMENTA.IDTIPOMOVIEST = '5' THEN --Devolucao ao fornecedor

            V_QTMOVI  := C_MOVIMENTA.QTLANCMOVIEST;
            V_QTATUAL := V_QTATUAL - V_QTMOVI;

         END IF;

         UPDATE RELAT_MOVIEST_K280
            SET QTLANCAMENTO  = V_QTMOVI,
                QTDEMOVIEST   = V_QTATUAL,
                QTTOTALANT    = V_QTDEANT
          WHERE NRRELATMOVI   = P_SEQUMOVI
            AND CDFILIMOVIEST = V_CDFILIAL
            AND NRLANCMOVIEST = C_MOVIMENTA.NRLANCMOVIEST
            AND NRSEQUMOVIEST = C_MOVIMENTA.NRSEQUMOVIEST
            AND CDPRODMOVIEST = C_MOVIMENTA.CDPRODMOVIEST
            AND NRLOTEESTQME  = C_MOVIMENTA.NRLOTEESTQME
            AND NRSUBLOTEME   = C_MOVIMENTA.NRSUBLOTEME
            AND DTLANCMOVIEST = C_MOVIMENTA.DTLANCMOVIEST
            AND CDALMOXARIRM  = C_MOVIMENTA.CDALMOXARIRM
            AND CDLOCALESTRM  = C_MOVIMENTA.CDLOCALESTRM
            AND CDFILLANCMEST = C_MOVIMENTA.CDFILLANCMEST
            AND IDTIPOMOVIEST = C_MOVIMENTA.IDTIPOMOVIEST;

         V_QTDEANT      := V_QTATUAL;
         V_CDPRODUTO    := C_MOVIMENTA.CDPRODMOVIEST;
         V_NRLOTEESTQ   := C_MOVIMENTA.NRLOTEESTQME;
         V_NRSUBLOTE    := C_MOVIMENTA.NRSUBLOTEME;
         V_CDALMOXLCEST := C_MOVIMENTA.CDALMOXARIRM||C_MOVIMENTA.CDLOCALESTRM;
      END LOOP;
   END LOOP;
END;
/