CREATE OR REPLACE FUNCTION MHA_EXPORTA_NF_TRANSMITIDA
   RETURN XML_ARQ PIPELINED
IS
   V_CLIDEPOS    VARCHAR2(3);
   V_TIPAGENTE   VARCHAR2(1);

   V_CDFILIAL    VARCHAR2(4);
   V_EMPRESA     VARCHAR2(2);
   V_FILIAL      VARCHAR2(10);

   V_NRPEDIDO    VARCHAR2(18);

   OUT_XML_ARQ XML := XML(NULL);

   L_XMLTYPE XMLTYPE;
   L_DOMDOC DBMS_XMLDOM.DOMDOCUMENT;
   L_ROOT_NODE DBMS_XMLDOM.DOMNODE;

   L_ELEMENT DBMS_XMLDOM.DOMELEMENT;
   L_ELEMENT_NODE DBMS_XMLDOM.DOMNODE;

   L_NODE DBMS_XMLDOM.DOMNODE;

   L_NAME_ELEMENT DBMS_XMLDOM.DOMELEMENT;
   L_NAME_NODE DBMS_XMLDOM.DOMNODE;
   L_NAME_TEXT DBMS_XMLDOM.DOMTEXT;
   L_NAME_TEXTNODE DBMS_XMLDOM.DOMNODE;

   CURSOR C_NF_TRANS 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;
BEGIN
   V_CLIDEPOS  := '006';
   V_TIPAGENTE := 'A';

   OPEN C_NF_TRANS;
   LOOP
      FETCH C_NF_TRANS INTO V_CDFILIAL, V_EMPRESA, V_FILIAL;
      EXIT WHEN C_NF_TRANS%NOTFOUND;

      -- Verifica notas fiscais transmitidas
      FOR C_NF IN (SELECT N.CDFILIEMIT, N.CDFILIAL, N.NRLANCTONF, N.NRNOTAFISC,
                          N.CDSERIE, N.CDFILIRECE
                     FROM NOTAFISCAL N
                    WHERE N.CDFILIEMIT   = V_CDFILIAL
                      AND N.IDENTRSAIDOP = 'S'
                      AND N.IDSTATUSNFE  = 'A'
                      AND N.IDORIGDESTNF = 'L'
                      AND N.USR_DTEXPXML IS NULL
                    ORDER BY N.CDFILIEMIT)
      LOOP
         BEGIN
            SELECT MAX(P.CDFILDEST||P.CDFILLANC||P.NRPEDEXOL)
              INTO V_NRPEDIDO
              FROM NFRELACPEXOL R, PEDIDOEXOL P
             WHERE R.CDFILIAL   = C_NF.CDFILIAL
               AND R.NRLANCTONF = C_NF.NRLANCTONF
               AND R.CDFILDEST  = P.CDFILDEST
               AND R.CDFILLANC  = P.CDFILLANC
               AND R.NRPEDEXOL  = P.NRPEDEXOL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NRPEDIDO := '*';
         END;

         L_DOMDOC    := DBMS_XMLDOM.NEWDOMDOCUMENT;
         L_ROOT_NODE := DBMS_XMLDOM.MAKENODE(L_DOMDOC);

         L_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'NumNotaFiscalSaida');
         DBMS_XMLDOM.SETATTRIBUTE(L_ELEMENT, 'Lancamento', C_NF.CDFILIAL||C_NF.NRLANCTONF);
         L_NODE     := DBMS_XMLDOM.APPENDCHILD(L_ROOT_NODE, DBMS_XMLDOM.MAKENODE(L_ELEMENT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'TIPOGERADOR');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, 'ERP');
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'TIPOINTERFACE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, 'INC');
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'EMPRESA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_EMPRESA);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'FILIAL');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_FILIAL);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CLIENTEDEPOSITANTE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CLIDEPOS);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CODAGENTE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, C_NF.CDFILIRECE);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'TIPAGENTE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_TIPAGENTE);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'ORDEMCARREGAMENTO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, C_NF.CDFILIAL||C_NF.NRLANCTONF);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CODTRANSACAONF');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, C_NF.CDFILIAL||C_NF.NRLANCTONF);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'NOTA_FISCAL');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, C_NF.NRNOTAFISC);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'NOTA_SERIE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, C_NF.CDSERIE);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'DOCUMENTOREMESSA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_NRPEDIDO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE(L_DOMDOC);
         DBMS_XMLDOM.FREEDOCUMENT(L_DOMDOC);
         OUT_XML_ARQ.XMLRETORNO := L_XMLTYPE;
         PIPE ROW(OUT_XML_ARQ);
      END LOOP;
   END LOOP;

   CLOSE C_NF_TRANS;
   --L_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE(L_DOMDOC);
   RETURN;
END;
/

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