create or replace PROCEDURE EXPORTA_EXPED_TEK
IS
   V_CDFILIAL     VARCHAR2(4);
   V_NREXPEDICAO  VARCHAR2(10);
   V_CDCLIEEXPORT VARCHAR2(30);
   V_CDFILIEMIT   VARCHAR2(4);
   V_CDPRODINTE   VARCHAR2(20);
   V_NRORDCMPPED  VARCHAR2(15);
   V_NRITEMPED    VARCHAR2(10);
   V_QTITEMPROD   FLOAT;
   V_CDALMOXARIFE VARCHAR2(6);
   V_SGUNIDADE    VARCHAR2(10);
   V_DTENTRSAID   DATE;   
   V_IDINTERFACE  VARCHAR2(5);
   V_CDFILIALRECE VARCHAR2(4);
   V_PEDEXTERNO   VARCHAR2(15);
   
   CURSOR C_EXPEDICAO IS
      SELECT E.CDFILIAL, E.NREXPEDICAO, NVL(EX.CDCLIEEXPORT, C.NRINSJURCLIE), NF.CDFILIEMIT, P.CDPRODINTE, NVL(I.NRPEDPROD, PE.NRPEDIDO), I.NRITEMPED,
             I.QTITEMPROD, I.CDALMOXARIFE, P.SGUNIDADE, NF.DTENTRSAID, NF.CDFILIRECE, PE.PEDEXTERNO
        FROM NOTAFISCAL NF, EXPEDICAO E, CLIENTE C, EXPORTCLIE EX, ITEMPRODUTO I, PRODUTO P, PEDIDO PE, ITEMPEDIDO IP        
       WHERE NF.CDFILIAL = E.CDFILIAL
         AND NF.NREXPEDICAO = E.NREXPEDICAO
         AND PE.CDCLIENTE = C.CDCLIENTE(+)
         AND C.CDCLIENTE = EX.CDCLIENTE(+)
         AND NF.CDFILIAL = I.CDFILIAL
         AND NF.NRLANCTONF = I.NRLANCTONF
         AND I.CDPRODUTO = P.CDPRODUTO
         AND I.CDFILIALPED = PE.CDFILIAL
         AND I.NRPEDIDO = PE.NRPEDIDO
         AND I.CDFILIALPED = IP.CDFILIAL
         AND I.NRPEDIDO = IP.NRPEDIDO
         AND I.NRITEMPED = IP.NRITEMPED
         AND PE.CDFILIAL = IP.CDFILIAL
         AND PE.NRPEDIDO = IP.NRPEDIDO
         AND E.DTEXPORTA_RIQ IS NULL
       ORDER BY E.CDFILIAL, E.NREXPEDICAO;
BEGIN
   V_IDINTERFACE := 'Baixa';
   
   OPEN C_EXPEDICAO;
   LOOP
      FETCH C_EXPEDICAO INTO V_CDFILIAL, V_NREXPEDICAO, V_CDCLIEEXPORT, V_CDFILIEMIT, V_CDPRODINTE, V_NRORDCMPPED, V_NRITEMPED,
                             V_QTITEMPROD, V_CDALMOXARIFE, V_SGUNIDADE, V_DTENTRSAID, V_CDFILIALRECE, V_PEDEXTERNO;
      EXIT WHEN C_EXPEDICAO%NOTFOUND;
      
      
      IF (V_CDCLIEEXPORT IS NULL) THEN
         BEGIN
            SELECT CDFILIALEXT
              INTO V_CDCLIEEXPORT
              FROM FILIAL
             WHERE CDFILIAL = V_CDFILIALRECE;
         EXCEPTION
            WHEN OTHERS THEN
               V_CDCLIEEXPORT := NULL;
         END;         
      END IF;
      
      IF (V_PEDEXTERNO IS NOT NULL) THEN
         V_NRORDCMPPED := V_PEDEXTERNO;
      END IF;
      
      IF (V_CDCLIEEXPORT IS NOT NULL) THEN
         INSERT INTO INT_EXPEDICAO_TEK(NREXPEDICAO, CDCLIENTE, CDFILIRECE, CDPRODEXTERNO, NRPEDPROD, NRITEMPED,
                                       QTITEMPROD, CDALMOXARIFE, SGUNIDADE, DTENTRSAID, IDINTERFACE,
                                       DTGRAVACAO, HRGRAVACAO)
         VALUES(V_NREXPEDICAO, V_CDCLIEEXPORT, V_CDFILIEMIT, V_CDPRODINTE, V_NRORDCMPPED, V_NRITEMPED,
                V_QTITEMPROD, V_CDALMOXARIFE, V_SGUNIDADE, V_DTENTRSAID, V_IDINTERFACE,
                TRUNC(SYSDATE), TO_CHAR(SYSDATE, 'HH24:MI'));
         
         UPDATE EXPEDICAO
            SET DTEXPORTA_RIQ = SYSDATE
          WHERE CDFILIAL    = V_CDFILIAL
            AND NREXPEDICAO = V_NREXPEDICAO;         
      END IF;
   END LOOP;
   CLOSE C_EXPEDICAO;
   
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      IF C_EXPEDICAO%ISOPEN THEN
         CLOSE C_EXPEDICAO;
      END IF;
      
      ROLLBACK;
      RAISE;
END;
/

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