create or replace
PROCEDURE ATUALIZA_SALDO_EST_TRANSF(P_CDFILIAL    IN VARCHAR2,
                                    P_DTINICIAL   IN DATE,
                                    P_DTFINAL     IN DATE)
IS
   V_VRUNILANCTO    FLOAT;

BEGIN
   FOR C_FILIAL IN (SELECT CDFILIAL
                      FROM PARAMFILIAL
                     WHERE IDUTILALMOX = 'S'
                       AND ((INSTR(P_CDFILIAL, CDFILIAL) <> 0) OR (P_CDFILIAL = 'T')))
   LOOP
      -- Verifica transferencias realizadas no periodo
      FOR C_TRANSF IN (SELECT C.CDFILIAL, C.NRCTRLMOVEST, C.DTMOVIESTQ,
                              C.CDFILIALENTR AS CDFILIAL_E, C.NRLANESTENTR AS NRLANCESTQ_E,
                              C.CDFILIALSAID AS CDFILIAL_S, C.NRLANESTSAID AS NRLANCESTQ_S
                         FROM CTRLMOVIESTQ C
                        WHERE C.CDFILIAL = C_FILIAL.CDFILIAL
                          AND C.DTMOVIESTQ BETWEEN P_DTINICIAL AND P_DTFINAL
                        ORDER BY C.CDFILIAL, C.DTMOVIESTQ, C.NRCTRLMOVEST)
      LOOP
         FOR C_LANC IN (SELECT S.NRSEQUITEM AS NRSEQUITEM_E, S.CDPRODUTO AS CDPRODUTO_E, S.VRUNILANCTO AS VRUNILANCTO_E,
                               E.NRSEQUITEM AS NRSEQUITEM_S, E.CDPRODUTO AS CDPRODUTO_S, E.VRUNILANCTO AS VRUNILANCTO_S,
                               NVL(P.VRFATOCONV, 1) AS VRFATOCONV,
                               CUSTO_PRODUTO_LCEST(C_TRANSF.CDFILIAL_S, P.CDPRODESTO, NVL(F.CDTIPCUSTEST,3), C_TRANSF.DTMOVIESTQ, SYSDATE,
                                                   S.CDALMOXARIFE, S.CDLOCALESTOQ, S.NRLOTEESTQ, S.NRSUBLOTE) AS CUSTOPROD,
                               CUSTO_PRODUTO_LCEST(C_TRANSF.CDFILIAL_S, P.CDPRODESTO, 4, C_TRANSF.DTMOVIESTQ, SYSDATE,
                                                   S.CDALMOXARIFE, S.CDLOCALESTOQ, S.NRLOTEESTQ, S.NRSUBLOTE) AS CUSTOBRUTO
                          FROM ITLANCTOEST E, ITLANCTOEST S, PRODUTO P, PARAMFILIAL F
                         WHERE E.CDFILIAL   = C_TRANSF.CDFILIAL_E
                           AND E.NRLANCESTQ = C_TRANSF.NRLANCESTQ_E
                           AND S.CDFILIAL   = C_TRANSF.CDFILIAL_S
                           AND S.NRLANCESTQ = C_TRANSF.NRLANCESTQ_S
                           AND E.NRSEQUITEM = S.NRSEQUITEM
                           AND P.CDPRODUTO = E.CDPRODUTO
                           AND F.CDFILIAL = C_TRANSF.CDFILIAL)
         LOOP

            BEGIN
               SELECT VRUNILANCTO
                 INTO V_VRUNILANCTO
                 FROM ITLANCTOEST
                WHERE CDFILIAL   = C_TRANSF.CDFILIAL_S
                  AND NRLANCESTQ = C_TRANSF.NRLANCESTQ_S
                  AND NRSEQUITEM = C_LANC.NRSEQUITEM_S;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_VRUNILANCTO := 0;
            END;

            IF ROUND(V_VRUNILANCTO,3) <> ROUND(C_LANC.CUSTOPROD,3) THEN
               UPDATE ITLANCTOEST
                  SET VRUNILANCTO  = C_LANC.CUSTOPROD * C_LANC.VRFATOCONV,
                      VRTOTLANCTO  = C_LANC.CUSTOPROD * C_LANC.VRFATOCONV * QTTOTLANCTO,
                      VRLANCTOBRUT = C_LANC.CUSTOBRUTO,
                      VRLANCTOEST  = C_LANC.CUSTOPROD
                WHERE CDFILIAL   = C_TRANSF.CDFILIAL_S
                  AND NRLANCESTQ = C_TRANSF.NRLANCESTQ_S
                  AND NRSEQUITEM = C_LANC.NRSEQUITEM_S;

               UPDATE ITLANCTOEST
                  SET VRUNILANCTO  = C_LANC.CUSTOPROD * C_LANC.VRFATOCONV,
                      VRTOTLANCTO  = C_LANC.CUSTOPROD * C_LANC.VRFATOCONV * QTTOTLANCTO,
                      VRLANCTOBRUT = C_LANC.CUSTOBRUTO,
                      VRLANCTOEST  = C_LANC.CUSTOPROD
                WHERE CDFILIAL   = C_TRANSF.CDFILIAL_E
                  AND NRLANCESTQ = C_TRANSF.NRLANCESTQ_E
                  AND NRSEQUITEM = C_LANC.NRSEQUITEM_E;
            END IF;
         END LOOP;

         ATUALIZA_ESTOQUE(C_FILIAL.CDFILIAL, C_TRANSF.DTMOVIESTQ, SYSDATE, C_TRANSF.CDFILIAL_S, C_TRANSF.NRLANCESTQ_S);
         ATUALIZA_ESTOQUE(C_FILIAL.CDFILIAL, C_TRANSF.DTMOVIESTQ, SYSDATE, C_TRANSF.CDFILIAL_E, C_TRANSF.NRLANCESTQ_E);
      END LOOP;
      COMMIT;
   END LOOP;
END;
/