create or replace 
PROCEDURE AJUSTE_ZERA_INVE 
  ( P_CDFILIAL          IN FILIAL.CDFILIAL%TYPE,
    P_DTIMPLANT         IN ITLANCTOEST.DTLANCMOVI%TYPE,
    P_FILILANCTO        IN FILIAL.CDFILIAL%TYPE,
    P_NRLANCESTQ        IN ITLANCTOEST.NRLANCESTQ%TYPE,
    P_CDOPERADOR        IN ITLANCTOEST.CDOPERADOR%TYPE,
    P_IDTIPOMOVI        IN ITLANCTOEST.IDTIPOMOVI%TYPE,
    P_IDORDEMCLC        IN ITLANCTOEST.IDORDEMCLC%TYPE,
    P_CDALMOXARIFE      IN ITLANCTOEST.CDALMOXARIFE%TYPE,
    P_CDLOCALESTOQ      IN ITLANCTOEST.CDLOCALESTOQ%TYPE
   )
   IS
   V_NRSEQUIITEM      ITLANCTOEST.NRSEQUITEM%TYPE;
   V_CDALMOXARIFE     ITLANCTOEST.CDALMOXARIFE%TYPE;
   V_CDLOCALESTOQ     ITLANCTOEST.CDLOCALESTOQ%TYPE;
BEGIN
   IF P_CDALMOXARIFE IS NULL THEN
      V_CDALMOXARIFE := ' ';
   ELSE
      V_CDALMOXARIFE := P_CDALMOXARIFE;
   END IF;

   IF P_CDLOCALESTOQ IS NULL THEN
      V_CDLOCALESTOQ := ' ';
   ELSE
      V_CDLOCALESTOQ := P_CDLOCALESTOQ;
   END IF;


   -- Se nao for informado almoxarifado no lancamento
   IF V_CDALMOXARIFE = 'T' THEN
      FOR C_ALMOX IN (SELECT CDALMOXARIFE
                        FROM ALMOXARIFADO
                       WHERE CDFILIAL = P_CDFILIAL
                       ORDER BY CDALMOXARIFE)
      LOOP
         FOR C_LOCAL IN (SELECT CDLOCALESTOQ
                           FROM LOCALESTOQUE
                          WHERE CDFILIAL = P_CDFILIAL
                            AND CDALMOXARIFE = C_ALMOX.CDALMOXARIFE
                           ORDER BY CDLOCALESTOQ)
         LOOP
            FOR C_PROD_ZERA IN
                (SELECT B.CDARVPROD, P.CDPRODUTO,  P.CDALMOXARIFE, P.CDLOCALESTOQ,
                        P.NRLOTEESTQ, P.NRSUBLOTE, B.NMPRODUTO, B.SGUNIDADE, B.CDPRODESTO
                   FROM PRODUTO B, POSESTDIA P
                  WHERE (P.CDFILIAL     = P_CDFILIAL)
                    AND (P.CDPRODUTO    = B.CDPRODUTO)
                    AND P.CDALMOXARIFE = C_ALMOX.CDALMOXARIFE
                    AND P.CDLOCALESTOQ = C_LOCAL.CDLOCALESTOQ
                    AND (P.QTESTOQDIA <> 0)
                    AND (P.DTPOSIESTQ = (SELECT MAX(D.DTPOSIESTQ)
                                           FROM POSESTDIA D
                                          WHERE D.CDFILIAL     = P_CDFILIAL
                                            AND D.CDPRODUTO    = P.CDPRODUTO
                                            AND D.CDALMOXARIFE = P.CDALMOXARIFE
                                            AND D.CDLOCALESTOQ = P.CDLOCALESTOQ
                                            AND D.NRLOTEESTQ   = P.NRLOTEESTQ
                                            AND D.NRSUBLOTE    = P.NRSUBLOTE
                                            AND D.DTPOSIESTQ   <= TO_DATE(P_DTIMPLANT) + 1))
                    AND (NOT EXISTS(SELECT I.CDPRODMOVI
                                      FROM ITLANCTOEST I
                                     WHERE (I.CDFILIAL     = P_FILILANCTO)
                                       AND (I.NRLANCESTQ   = P_NRLANCESTQ)
                                       AND (P.CDPRODUTO    = I.CDPRODMOVI)
                                       AND (I.CDALMOXARIFE = P.CDALMOXARIFE)
                                       AND (I.CDLOCALESTOQ = P.CDLOCALESTOQ)
                                       AND (I.NRLOTEESTQ   = P.NRLOTEESTQ)
                                       AND (I.NRSUBLOTE    = P.NRSUBLOTE)))
                      )
            LOOP
               SELECT SUBSTR('0000'||NVL((MAX(TO_NUMBER(NRSEQUITEM))+1),'0001'),-4) AS NRSEQUITEM
                 INTO V_NRSEQUIITEM
                 FROM ITLANCTOEST
                WHERE CDFILIAL = P_FILILANCTO
                  AND NRLANCESTQ = P_NRLANCESTQ;
                  
               INSERT INTO ITLANCTOEST
                 (CDFILIAL, NRLANCESTQ, NRSEQUITEM, CDPRODUTO, QTLANCTOEST, VRUNILANCTO,
                  VRTOTLANCTO, IDTIPOMOVI, DTLANCMOVI, IDORDEMCLC, VRLANCTOEST, QTTOTLANCTO,
                  CDFILIMOVI, CDPRODMOVI, VRLANCTOBRUT, IDEVOLESTOQ, CDALMOXARIFE, CDLOCALESTOQ,
                  NRLOTEESTQ, NRSUBLOTE, CDOPERADOR)
               VALUES
                (P_FILILANCTO, P_NRLANCESTQ, V_NRSEQUIITEM, C_PROD_ZERA.CDPRODUTO, 0, 0,
                  0, P_IDTIPOMOVI, P_DTIMPLANT, P_IDORDEMCLC, 0, 0,
                 P_CDFILIAL, C_PROD_ZERA.CDPRODESTO, 0, 'N', C_PROD_ZERA.CDALMOXARIFE, C_PROD_ZERA.CDLOCALESTOQ,
                 C_PROD_ZERA.NRLOTEESTQ, C_PROD_ZERA.NRSUBLOTE, P_CDOPERADOR);
            END LOOP;
         END LOOP;
      END LOOP;
   ELSE
      IF V_CDLOCALESTOQ = 'T' THEN
         FOR C_LOCAL IN (SELECT CDLOCALESTOQ
                           FROM LOCALESTOQUE
                          WHERE CDFILIAL = P_CDFILIAL
                            AND CDALMOXARIFE = V_CDALMOXARIFE
                           ORDER BY CDLOCALESTOQ)
         LOOP
            FOR C_PROD_ZERA IN
                (SELECT B.CDARVPROD, P.CDPRODUTO,  P.CDALMOXARIFE, P.CDLOCALESTOQ,
                        P.NRLOTEESTQ, P.NRSUBLOTE, B.NMPRODUTO, B.SGUNIDADE, B.CDPRODESTO
                   FROM PRODUTO B, POSESTDIA P
                  WHERE (P.CDFILIAL     = P_CDFILIAL)
                    AND (P.CDPRODUTO    = B.CDPRODUTO)
                    AND P.CDALMOXARIFE = V_CDALMOXARIFE
                    AND P.CDLOCALESTOQ = C_LOCAL.CDLOCALESTOQ
                    AND (P.QTESTOQDIA <> 0)
                    AND (P.DTPOSIESTQ = (SELECT MAX(D.DTPOSIESTQ)
                                           FROM POSESTDIA D
                                          WHERE D.CDFILIAL     = P_CDFILIAL
                                            AND D.CDPRODUTO    = P.CDPRODUTO
                                            AND D.CDALMOXARIFE = P.CDALMOXARIFE
                                            AND D.CDLOCALESTOQ = P.CDLOCALESTOQ
                                            AND D.NRLOTEESTQ   = P.NRLOTEESTQ
                                            AND D.NRSUBLOTE    = P.NRSUBLOTE
                                            AND D.DTPOSIESTQ   <= TO_DATE(P_DTIMPLANT) + 1))
                    AND (NOT EXISTS(SELECT I.CDPRODMOVI
                                      FROM ITLANCTOEST I
                                     WHERE (I.CDFILIAL     = P_FILILANCTO)
                                 AND (I.NRLANCESTQ   = P_NRLANCESTQ)
                                 AND (P.CDPRODUTO    = I.CDPRODMOVI)
                                 AND (I.CDALMOXARIFE = P.CDALMOXARIFE)
                                 AND (I.CDLOCALESTOQ = P.CDLOCALESTOQ)
                                 AND (I.NRLOTEESTQ   = P.NRLOTEESTQ)
                                 AND (I.NRSUBLOTE    = P.NRSUBLOTE)))
                )
            LOOP
               SELECT SUBSTR('0000'||NVL((MAX(TO_NUMBER(NRSEQUITEM))+1),'0001'),-4) AS NRSEQUITEM
                 INTO V_NRSEQUIITEM
                 FROM ITLANCTOEST
                WHERE CDFILIAL = P_FILILANCTO
                  AND NRLANCESTQ = P_NRLANCESTQ;

               INSERT INTO ITLANCTOEST
                 (CDFILIAL, NRLANCESTQ, NRSEQUITEM, CDPRODUTO, QTLANCTOEST, VRUNILANCTO,
                  VRTOTLANCTO, IDTIPOMOVI, DTLANCMOVI, IDORDEMCLC, VRLANCTOEST, QTTOTLANCTO,
                  CDFILIMOVI, CDPRODMOVI, VRLANCTOBRUT, IDEVOLESTOQ, CDALMOXARIFE, CDLOCALESTOQ,
                  NRLOTEESTQ, NRSUBLOTE, CDOPERADOR)
               VALUES
                (P_FILILANCTO, P_NRLANCESTQ, V_NRSEQUIITEM, C_PROD_ZERA.CDPRODUTO, 0, 0,
                  0, P_IDTIPOMOVI, P_DTIMPLANT, P_IDORDEMCLC, 0, 0,
                 P_CDFILIAL, C_PROD_ZERA.CDPRODESTO, 0, 'N', C_PROD_ZERA.CDALMOXARIFE, C_PROD_ZERA.CDLOCALESTOQ,
                 C_PROD_ZERA.NRLOTEESTQ, C_PROD_ZERA.NRSUBLOTE, P_CDOPERADOR);
            END LOOP;
         END LOOP;
      ELSE
         FOR C_PROD_ZERA IN
             (SELECT B.CDARVPROD, P.CDPRODUTO,  P.CDALMOXARIFE, P.CDLOCALESTOQ,
                     P.NRLOTEESTQ, P.NRSUBLOTE, B.NMPRODUTO, B.SGUNIDADE, B.CDPRODESTO
                FROM PRODUTO B, POSESTDIA P
               WHERE (P.CDFILIAL    = P_CDFILIAL)
                 AND (P.CDPRODUTO   = B.CDPRODUTO)
                 AND P.CDALMOXARIFE = V_CDALMOXARIFE
                 AND P.CDLOCALESTOQ = V_CDLOCALESTOQ
                 AND (P.QTESTOQDIA <> 0)
                 AND (P.DTPOSIESTQ = (SELECT MAX(D.DTPOSIESTQ)
                                        FROM POSESTDIA D
                                       WHERE D.CDFILIAL     = P_CDFILIAL
                                         AND D.CDPRODUTO    = P.CDPRODUTO
                                         AND D.CDALMOXARIFE = P.CDALMOXARIFE
                                         AND D.CDLOCALESTOQ = P.CDLOCALESTOQ
                                         AND D.NRLOTEESTQ   = P.NRLOTEESTQ
                                         AND D.NRSUBLOTE    = P.NRSUBLOTE
                                         AND D.DTPOSIESTQ   <= TO_DATE(P_DTIMPLANT) + 1))
                 AND (NOT EXISTS(SELECT I.CDPRODMOVI
                                   FROM ITLANCTOEST I
                                  WHERE (I.CDFILIAL     = P_FILILANCTO)
                              AND (I.NRLANCESTQ   = P_NRLANCESTQ)
                              AND (P.CDPRODUTO    = I.CDPRODMOVI)
                              AND (I.CDALMOXARIFE = P.CDALMOXARIFE)
                              AND (I.CDLOCALESTOQ = P.CDLOCALESTOQ)
                              AND (I.NRLOTEESTQ   = P.NRLOTEESTQ)
                              AND (I.NRSUBLOTE    = P.NRSUBLOTE)))
             )
         LOOP
            SELECT SUBSTR('0000'||NVL((MAX(TO_NUMBER(NRSEQUITEM))+1),'0001'),-4) AS NRSEQUITEM
              INTO V_NRSEQUIITEM
              FROM ITLANCTOEST
             WHERE CDFILIAL = P_FILILANCTO
               AND NRLANCESTQ = P_NRLANCESTQ;

            INSERT INTO ITLANCTOEST
              (CDFILIAL, NRLANCESTQ, NRSEQUITEM, CDPRODUTO, QTLANCTOEST, VRUNILANCTO,
               VRTOTLANCTO, IDTIPOMOVI, DTLANCMOVI, IDORDEMCLC, VRLANCTOEST, QTTOTLANCTO,
               CDFILIMOVI, CDPRODMOVI, VRLANCTOBRUT, IDEVOLESTOQ, CDALMOXARIFE, CDLOCALESTOQ,
               NRLOTEESTQ, NRSUBLOTE, CDOPERADOR)
            VALUES
             (P_FILILANCTO, P_NRLANCESTQ, V_NRSEQUIITEM, C_PROD_ZERA.CDPRODUTO, 0, 0,
               0, P_IDTIPOMOVI, P_DTIMPLANT, P_IDORDEMCLC, 0, 0,
              P_CDFILIAL, C_PROD_ZERA.CDPRODESTO, 0, 'N', C_PROD_ZERA.CDALMOXARIFE, C_PROD_ZERA.CDLOCALESTOQ,
              C_PROD_ZERA.NRLOTEESTQ, C_PROD_ZERA.NRSUBLOTE, P_CDOPERADOR);
         END LOOP;
      END IF;
   END IF;
   COMMIT;
END;
/