CREATE OR REPLACE TRIGGER USR_INS_DEL_ENVIA_MHA
BEFORE INSERT OR DELETE
ON ITEMPEDEXOL FOR EACH ROW
BEGIN
   UPDATE PEDIDOEXOL
      SET USR_DTEXPXML = NULL
    WHERE CDFILDEST = :NEW.CDFILDEST
      AND CDFILLANC = :NEW.CDFILLANC
      AND NRPEDEXOL = :NEW.NRPEDEXOL;
END;
/

create or replace
PROCEDURE MHA_EXPORTA_ARQ_SEPARACAO(P_DTPEDIDO IN DATE)
IS
   V_CDFILIAL    VARCHAR2(4);
   V_EMPRESA     VARCHAR2(2);
   V_FILIAL      VARCHAR2(10);

   V_NRSEQUITEM  INTEGER;

   V_ORDEMCARR  VARCHAR2(20);
   V_CDTRANSER  VARCHAR2(20);
   
   V_CDFILDEST  VARCHAR2(20);
   V_CODPRODUTO VARCHAR2(20);
   V_DESPRODUTO VARCHAR2(50);
   V_CODUNIDADE VARCHAR2(10);
   V_QUANTIDADE FLOAT;
   V_UNITARIO   FLOAT;
   V_CDFILLANC VARCHAR2(4);
   V_NRPEDEXOL VARCHAR2(10);
   
   V_CDPRODUTO VARCHAR2(10);
   V_DTPROGENPEOL DATE;
   V_CDMARCA      VARCHAR2(5);
   
   V_TIPO         VARCHAR2(3);
   V_TIPAGENTE    VARCHAR2(1);
   V_TIPDOCUMENTO VARCHAR2(5);
   
   V_FILIALNF VARCHAR2(4);
   V_LANCTONF VARCHAR2(10);
   V_CODDEPOSITOERP VARCHAR2(30);
   V_LETRA VARCHAR2(3);
   
   CURSOR C_ARQ_SEPARACAO IS
      SELECT F.CDFILIAL, F.CDEMPRESA, R.CDFILWMSMHA
        FROM PARAMFILIAL R, FILIAL F
       WHERE R.CDFILWMSMHA IS NOT NULL
         AND F.CDFILIAL = R.CDFILIAL
         AND F.IDFUNCAOFILI = 'O'
       ORDER BY F.CDFILIAL;
   
   CURSOR C_PEDIDO(C_CDFILIAL IN VARCHAR2) IS
      SELECT DISTINCT N.CDFILDEST||N.CDFILLANC||N.NRPEDEXOL AS REMESSA, N.CDFILDEST, N.CDFILLANC, N.NRPEDEXOL, 
             TO_CHAR(IT.DTPROGEMBARQ, 'YYYYMMDD'), 'PED' AS TIPO, IT.CDLOTENVITOL AS CODDEPOSITOERP
        FROM PEDIDOEXOL N, ITEMPEDEXOL IT
       WHERE (IT.CDFILDEST   = N.CDFILDEST)
         AND (IT.CDFILLANC   = N.CDFILLANC)
         AND (IT.NRPEDEXOL   = N.NRPEDEXOL)
         AND (NVL(IT.IDBAIXAOLSAI, '1') = '1')
         AND (NVL(IT.IDBAIXENPEOL, '1') = '1')
         AND (IT.IDITMPEDOLBLOQ = 'S')
         AND (N.CDFILIOPELOG = C_CDFILIAL)
         AND (NVL(N.IDBAIXEXSAI, '1') = '1')
         AND (NVL(N.IDBAIXEXOL, '1')  = '1')
         AND (IT.IDITEMAJUSTE = 'N')
         AND (IT.IDENVITMOL = 'S')
         AND (TRUNC(IT.DTENVITMOL) = TRUNC(P_DTPEDIDO))
         AND (IT.USR_DTEXPXML IS NULL)
       UNION ALL
      SELECT NF.CDFILIAL||NF.NRLANCTONF, NVL(NF.NRINSJURNF, REGEXP_REPLACE(NF.CDFORNECED, '\D')), NF.CDFILIAL, NF.NRLANCTONF,
             TO_CHAR(NF.DTENTRSAID, 'YYYYMMDD'), 'NF', '' CODDEPOSITOERP
        FROM NOTAFISCAL NF JOIN FILIAL F
                             ON NF.CDFILIEMIT = F.CDFILIAL
                           JOIN PARAMFILIAL PF
                             ON PF.CDFILIAL = F.CDFILIAL
       WHERE (NF.IDENTRSAIDOP = 'S')
         AND (NF.CDTIPOOPER = '06')
         AND (F.IDFUNCAOFILI  = 'O')
         AND (PF.CDFILWMSMHA IS NOT NULL)
         AND (NF.USR_DTEXPXML IS NULL)
         AND EXISTS(SELECT I.CDFILIAL
                      FROM ITEMPRODUTO I
                     WHERE I.CDFILIAL   = NF.CDFILIAL
                       AND I.NRLANCTONF = NF.NRLANCTONF);

   CURSOR C_ITEMPEDEXOL(C_CDFILDEST IN VARCHAR2, C_CDFILLANC IN VARCHAR2, C_NRPEDEXOL IN VARCHAR2, C_CDFILIAL IN VARCHAR2) IS
      SELECT P.CDARVPROD AS CODPRODUTO, P.NMPRODUTO AS DESPRODUTO, P.SGUNIDADE AS CODUNIDADE, ROUND(IT.QTPROGENPEOL,7) AS QUANTIDADE,
             IT.CDPRODUTO, IT.DTPROGENPEOL, IT.CDMARCA,
             CASE WHEN ((INSTR(UPPER(SUB.NMSUBGRPROD), 'HORTI') > 0) AND (IT.CDLOTENVITOL NOT IN('00000000006', '00000000015', '00000000048','00000000051') OR IT.CDLOTENVITOL IS NULL))
                  THEN 'H'
                  WHEN (IT.CDLOTENVITOL IN('00000000006', '00000000015', '00000000048','00000000051'))
                  THEN 'VENC'
                  ELSE 'V'
              END AS TIPODOCUMENTO
        FROM ITEMPEDEXOL IT JOIN RELACPRODOL R
                              ON (R.CDPRARVORIG = IT.CDPRODUTO)
                             AND (R.CDMARCA = IT.CDMARCA)
                            JOIN PRODUTO P
                              ON (P.CDPRODUTO = R.CDPRODARVOL)
                            JOIN PRSUBGRPROD PR
                              ON (R.CDPRARVORIG = PR.CDPRODUTO)
                            JOIN SUBGRUPPROD SUB
                              ON (PR.CDGRUPPROD  = SUB.CDGRUPPROD)
                             AND (PR.CDSUBGRPROD = SUB.CDSUBGRPROD)
                            JOIN SAP_SUBGRUPPRODFIL S
                              ON (SUB.CDGRUPPROD  = S.CDGRUPPROD)
                             AND (SUB.CDSUBGRPROD = S.CDSUBGRPROD)
                             AND (S.CDFILIAL = C_CDFILIAL)
       WHERE (IT.CDFILDEST = C_CDFILDEST)
         AND (IT.CDFILLANC = C_CDFILLANC)
         AND (IT.NRPEDEXOL = C_NRPEDEXOL)
         AND (NVL(IT.IDBAIXAOLSAI, '1') = '1')
         AND (NVL(IT.IDBAIXENPEOL, '1') = '1')
         AND (IT.IDITMPEDOLBLOQ = 'S')
         AND (IT.IDITEMAJUSTE = 'N')
         AND (IT.IDENVITMOL = 'S')
         AND (R.CDPRODARVOL IS NOT NULL)
       ORDER BY IT.CDFILDEST||IT.CDFILLANC||IT.NRPEDEXOL, IT.CDFILDEST, P.CDARVPROD;
   
   CURSOR C_ITEMPRODUTO(C_CDFILIAL IN VARCHAR2, C_NRLANCTONF IN VARCHAR2) IS
      SELECT TO_NUMBER(I.NRSEQITPROD), P.CDARVPROD, P.NMPRODUTO AS DESPRODUTO, P.SGUNIDADE AS CODUNIDADE, ROUND(I.QTITEMPROD, 7), ROUND(I.VRUNITITEM, 7),
             'D' TIPODOCUMENTO
        FROM ITEMPRODUTO I, PRODUTO P
       WHERE I.CDPRODUTO  = P.CDPRODUTO
         AND I.CDFILIAL   = C_CDFILIAL
         AND I.NRLANCTONF = C_NRLANCTONF
       ORDER BY I.NRSEQITPROD;
    
   FUNCTION VALOR(F_CDFILIAL IN VARCHAR2, F_CDPRODUTO IN VARCHAR2, F_DTPROGENPEOL IN DATE) RETURN FLOAT
   IS
      V_CUSTO_PROD FLOAT;
      V_CDTIPCUSTEST VARCHAR2(5);
   BEGIN
      SELECT NVL(CDTIPCUSTEST, '3') 
        INTO V_CDTIPCUSTEST
        FROM PARAMFILIAL 
       WHERE CDFILIAL = F_CDFILIAL;
      
      V_CUSTO_PROD := ROUND(CUSTO_PRODUTO(F_CDFILIAL, F_CDPRODUTO, V_CDTIPCUSTEST, F_DTPROGENPEOL, SYSDATE),7);
      
      RETURN V_CUSTO_PROD;
   END;
BEGIN
   DELETE FROM ITEM_ARQ_SEP;
   DELETE FROM CABEC_ARQ_SEP;
   
   COMMIT;
   OPEN C_ARQ_SEPARACAO;
   LOOP
      FETCH C_ARQ_SEPARACAO INTO V_CDFILIAL, V_EMPRESA, V_FILIAL;
      EXIT WHEN C_ARQ_SEPARACAO%NOTFOUND;

      -- Verifica pedidos
      OPEN C_PEDIDO(V_CDFILIAL);
      LOOP
         FETCH C_PEDIDO INTO V_CDTRANSER, V_CDFILDEST, V_CDFILLANC, V_NRPEDEXOL, V_ORDEMCARR, V_TIPO, V_CODDEPOSITOERP;
         EXIT WHEN C_PEDIDO%NOTFOUND;
         -- corpo pedido
         
         IF V_TIPO = 'NF' THEN
            V_TIPAGENTE    := 'F';
            --V_TIPDOCUMENTO := 'D';
            V_FILIALNF     := V_CDFILLANC;
            V_LANCTONF     := V_NRPEDEXOL;
         ELSE
            V_TIPAGENTE    := 'A';
            --V_TIPDOCUMENTO := 'V';
            V_FILIALNF     := NULL;
            V_LANCTONF     := NULL;
         END IF;
         
         IF (V_CODDEPOSITOERP = '0000000005') OR (V_CODDEPOSITOERP = '0000000028') OR (V_CODDEPOSITOERP = '0000000046') OR (V_CODDEPOSITOERP = '0000000047') THEN
            V_LETRA := 'R';
         ELSE
            V_LETRA := '';
         END IF;
                  
         V_ORDEMCARR := V_LETRA||V_ORDEMCARR;         
         
         INSERT INTO CABEC_ARQ_SEP(CODTRANSACAOERP, EMPRESA, FILIAL, ORDEMCARREGAMENTO, CODAGENTE, DOCUMENTOREMESSA, TIPAGENTE, --TIPDOCUMENTO,
                                   IDENTIFICADOR, CDFILIAL, NRLANCTONF, CODDEPOSITOERP)
         VALUES(V_CDTRANSER, V_EMPRESA, V_FILIAL, V_ORDEMCARR, V_CDFILDEST, V_CDTRANSER, V_TIPAGENTE, --V_TIPDOCUMENTO,
                V_TIPO, V_FILIALNF, V_LANCTONF, V_CODDEPOSITOERP);
         
         IF V_TIPO = 'PED' THEN
            V_NRSEQUITEM := 1;
            OPEN C_ITEMPEDEXOL(V_CDFILDEST, V_CDFILLANC, V_NRPEDEXOL, V_CDFILIAL);
            LOOP
               FETCH C_ITEMPEDEXOL INTO V_CODPRODUTO, V_DESPRODUTO, V_CODUNIDADE, V_QUANTIDADE, V_CDPRODUTO, V_DTPROGENPEOL, V_CDMARCA, V_TIPDOCUMENTO;
               EXIT WHEN C_ITEMPEDEXOL%NOTFOUND;
               
               V_UNITARIO := VALOR(V_CDFILIAL, V_CDPRODUTO, V_DTPROGENPEOL);
               
               INSERT INTO ITEM_ARQ_SEP(CODTRANSACAOERP, CODPRODUTO, DESPRODUTO, SEQITEM, CODUNIDADE, QUANTIDADE, VLR_UNITARIO, 
                                        CDFILDEST, CDFILLANC, NRPEDEXOL, CDMARCA, CDPRODUTO, DTPROGENPEOL, TIPDOCUMENTO)
               VALUES(V_CDTRANSER, V_CODPRODUTO, V_DESPRODUTO, V_NRSEQUITEM, V_CODUNIDADE, V_QUANTIDADE, V_UNITARIO,
                      V_CDFILDEST, V_CDFILLANC, V_NRPEDEXOL, V_CDMARCA, V_CDPRODUTO, V_DTPROGENPEOL, V_TIPDOCUMENTO);
               
               V_NRSEQUITEM := V_NRSEQUITEM+1;
            END LOOP;
            CLOSE C_ITEMPEDEXOL;
         ELSE
            OPEN C_ITEMPRODUTO(V_CDFILLANC, V_NRPEDEXOL);
            LOOP
               FETCH C_ITEMPRODUTO INTO V_NRSEQUITEM, V_CODPRODUTO, V_DESPRODUTO, V_CODUNIDADE, V_QUANTIDADE, V_UNITARIO, V_TIPDOCUMENTO;
               EXIT WHEN C_ITEMPRODUTO%NOTFOUND;
               
               INSERT INTO ITEM_ARQ_SEP(CODTRANSACAOERP, CODPRODUTO, DESPRODUTO, SEQITEM, CODUNIDADE, QUANTIDADE, VLR_UNITARIO, TIPDOCUMENTO)
               VALUES(V_CDTRANSER, V_CODPRODUTO, V_DESPRODUTO, V_NRSEQUITEM, V_CODUNIDADE, V_QUANTIDADE, V_UNITARIO, V_TIPDOCUMENTO);
               
            END LOOP;
            
            CLOSE C_ITEMPRODUTO;
         END IF;
      END LOOP;
      
      COMMIT;
      CLOSE C_PEDIDO;
   END LOOP;
   
   CLOSE C_ARQ_SEPARACAO;
EXCEPTION
   WHEN OTHERS THEN
      IF C_ARQ_SEPARACAO%ISOPEN THEN
         CLOSE C_ARQ_SEPARACAO;
      END IF;
      
      IF C_PEDIDO%ISOPEN THEN
         CLOSE C_PEDIDO;
      END IF;
      
      IF C_ITEMPEDEXOL%ISOPEN THEN
         CLOSE C_ITEMPEDEXOL;
      END IF;
      
      IF C_ITEMPRODUTO%ISOPEN THEN
         CLOSE C_ITEMPRODUTO;
      END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('MHA_EXPORTA_ARQ_SEPARACAO','5.87.XXX'); END;
/