CREATE OR REPLACE PROCEDURE EXPORTA_SALDOEST_TEK
IS
   V_CDPRODINTE   VARCHAR2(20);
   V_QTESTOQDIA   FLOAT;
   V_SGUNIDADE    VARCHAR2(10);
   V_DTPOSIESTQ   DATE;
   V_QTESTOQANT   FLOAT;
   V_IDINTERFACE  VARCHAR2(5);
   V_COUNT        INTEGER;
   
   CURSOR C_POSESTDIA IS 
      SELECT A.CDPRODINTE, SUM(B.QTESTOQDIA) AS QTESTOQDIA, A.SGUNIDADE, B.DTPOSIESTQ, SUM(B.QTESTOQANT) AS QTESTOQANT
        FROM (SELECT CDPRODINTE, CDPRODESTO, SGUNIDADE
                FROM PRODUTO) A, POSESTDIA B
       WHERE B.CDFILIAL = '9999'
         AND B.CDPRODUTO = A.CDPRODESTO
         AND B.DTPOSIESTQ = (SELECT MAX(P.DTPOSIESTQ)
                               FROM POSESTDIA P
                              WHERE P.CDFILIAL = B.CDFILIAL 
                                AND P.CDPRODUTO = B.CDPRODUTO 
                                AND P.CDALMOXARIFE = B.CDALMOXARIFE 
                                AND P.CDLOCALESTOQ = B.CDLOCALESTOQ 
                                AND P.NRLOTEESTQ = B.NRLOTEESTQ 
                                AND P.NRSUBLOTE = B.NRSUBLOTE 
                                AND P.DTPOSIESTQ <= SYSDATE+1)
       GROUP BY A.CDPRODINTE, A.SGUNIDADE, B.DTPOSIESTQ;
BEGIN
   OPEN C_POSESTDIA;
   LOOP
      FETCH C_POSESTDIA INTO V_CDPRODINTE, V_QTESTOQDIA, V_SGUNIDADE, V_DTPOSIESTQ, V_QTESTOQANT;
      EXIT WHEN C_POSESTDIA%NOTFOUND;
      
      IF (V_QTESTOQDIA - V_QTESTOQANT) > 0 THEN
         V_IDINTERFACE := 'Alta';
      ELSE
         V_IDINTERFACE := 'Baixa';
      END IF;
      
      SELECT COUNT(1)
        INTO V_COUNT
        FROM INT_SALDOEST_TEK
       WHERE CDPRODEXTERNO = V_CDPRODINTE
         AND DTPOSICAO = V_DTPOSIESTQ;
      
      IF (V_COUNT = 0) THEN
         INSERT INTO INT_SALDOEST_TEK(CDPRODEXTERNO, QTESTOQDIA, SGUNIDADE, DTPOSICAO, IDINTERFACE,
                                      DTGRAVACAO, HRGRAVACAO)
         VALUES (V_CDPRODINTE, V_QTESTOQDIA, V_SGUNIDADE, V_DTPOSIESTQ, V_IDINTERFACE,
                 TRUNC(SYSDATE), TO_CHAR(SYSDATE, 'HH24:MI'));      
      ELSE
         UPDATE INT_SALDOEST_TEK
            SET QTESTOQDIA = V_QTESTOQDIA,
                DTIMPORTACAO = NULL,
                HRIMPORTACAO = NULL,
                LOG_ERRO     = NULL
          WHERE CDPRODEXTERNO = V_CDPRODINTE
            AND DTPOSICAO = V_DTPOSIESTQ;
      END IF;
   END LOOP;
   CLOSE C_POSESTDIA;
   
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      IF C_POSESTDIA%ISOPEN THEN
         CLOSE C_POSESTDIA;
      END IF;
      
      ROLLBACK;
      RAISE;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('EXPORTA_SALDOEST_TEK','6.39.014'); END;
/
