CREATE OR REPLACE TRIGGER MHA_TRG_UPDFORN_XML
BEFORE UPDATE OF NMRAZSOCFORN, NMFANTFORN, NRINSJURFORN ON FORNECEDOR
FOR EACH ROW
BEGIN
   :NEW.USR_DTEXPXML := NULL;
END;
/

CREATE OR REPLACE TRIGGER MHA_TRG_UPDCLIE_XML
BEFORE UPDATE OF NMRAZSOCCLIE, NMFANTCLIE, NRINSJURCLIE ON CLIENTE
FOR EACH ROW
BEGIN
   :NEW.USR_DTEXPXML := NULL;
END;
/

CREATE OR REPLACE TRIGGER MHA_TRG_UPDFILI_XML
BEFORE UPDATE OF NMRAZSOCFILI, NMFILIAL, NRINSJURFILI, CDEMPRESA ON FILIAL
FOR EACH ROW
BEGIN
   :NEW.USR_DTEXPXML := NULL;
END;
/

CREATE OR REPLACE FUNCTION MHA_EXPORTA_AGENTE RETURN XML_ARQ PIPELINED
IS
   L_XMLTYPE XMLTYPE;
   OUT_XML_ARQ XML := XML(NULL);
   V_CLIDEPOS VARCHAR2(6);

   V_CDEMPRESA VARCHAR2(2);
   V_CDFILIAL VARCHAR2(4);

   V_CODAGENTE VARCHAR2(20);
   V_TIPAGENTE VARCHAR2(4);
   V_RAZAOSOCIAL VARCHAR2(50);
   V_NOMEFANTASIA VARCHAR2(50);
   V_CNPJ VARCHAR2(20);
   V_TIPOINTERFACE VARCHAR2(5);
   
   V_ZERO  INTEGER;
   V_VAZIO VARCHAR2(5);

   L_DOMDOC DBMS_XMLDOM.DOMDOCUMENT;
   L_ROOT_NODE DBMS_XMLDOM.DOMNODE;

   L_AGENTE_ELEMENT DBMS_XMLDOM.DOMELEMENT;
   L_AGENTES_NODE DBMS_XMLDOM.DOMNODE;

   L_AGENT_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_AGENTE IS
      SELECT NRINSJURFORN AS CODAGENTE, 'F' AS TIPAGENTE, NMRAZSOCFORN AS RAZAOSOCIAL, NMFANTFORN AS NOMEFANTASIA, NRINSJURFORN AS CNPJ,
             CASE WHEN IDSITUCADA = 'A'
                  THEN 'INC'
                  ELSE 'EXC'
              END AS TIPOINTERFACE
        FROM FORNECEDOR
       WHERE USR_DTEXPXML IS NULL
       UNION
      SELECT NRINSJURCLIE, 'C', NMRAZSOCCLIE, NMFANTCLIE, NRINSJURCLIE,
             CASE WHEN IDCLIEATIV = 'S'
                  THEN 'INC'
                  ELSE 'EXC'
              END AS TIPOINTERFACE
        FROM CLIENTE
       WHERE USR_DTEXPXML IS NULL
       UNION
      SELECT F.CDFILIAL, 'A', F.NMRAZSOCFILI, F.NMFILIAL, F.NRINSJURFILI,
             CASE WHEN IDCNTRRESC = 'N'
                  THEN 'INC'
                  ELSE 'EXC'
              END AS TIPOINTERFACE
        FROM FILIAL F
       WHERE USR_DTEXPXML IS NULL
       ORDER BY 2, 1;
BEGIN
   V_CLIDEPOS := '006';
   V_ZERO     := 0;
   V_VAZIO    := '*';

   FOR C_FILIAL IN (SELECT F.CDEMPRESA, PF.CDFILWMSMHA
                      FROM FILIAL F, PARAMFILIAL PF
                     WHERE F.CDFILIAL = PF.CDFILIAL
                       AND F.IDFUNCAOFILI = 'O'
                       AND PF.CDFILWMSMHA IS NOT NULL)
   LOOP
      OPEN C_AGENTE;
      LOOP
         FETCH C_AGENTE INTO V_CODAGENTE, V_TIPAGENTE, V_RAZAOSOCIAL, V_NOMEFANTASIA, V_CNPJ, V_TIPOINTERFACE;
         EXIT WHEN C_AGENTE%NOTFOUND;

         L_DOMDOC    := DBMS_XMLDOM.NEWDOMDOCUMENT;
         L_ROOT_NODE := DBMS_XMLDOM.MAKENODE(L_DOMDOC);
         
         L_AGENTE_ELEMENT := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CadastroAgente');
         DBMS_XMLDOM.SETATTRIBUTE(L_AGENTE_ELEMENT, 'CODIGO', V_CODAGENTE);
         --L_AGENT_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENTES_NODE, DBMS_XMLDOM.MAKENODE(L_AGENTE_ELEMENT));
         L_AGENT_NODE     := DBMS_XMLDOM.APPENDCHILD(L_ROOT_NODE, DBMS_XMLDOM.MAKENODE(L_AGENTE_ELEMENT));

         V_CDEMPRESA := C_FILIAL.CDEMPRESA;
         V_CDFILIAL  := C_FILIAL.CDFILWMSMHA;
         
         /* VALORES FIXOS */
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'TIPOGERADOR');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_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_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_TIPOINTERFACE);
         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_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CDEMPRESA);
         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_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CDFILIAL);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         /* VALORES FIXOS */
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CLIENTEDEPOSITANTE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_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_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CODAGENTE);
         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_AGENT_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, 'RAZAOSOCIAL');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_RAZAOSOCIAL);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'NOMEFANTASIA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_NOMEFANTASIA);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CNPJ');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CNPJ);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         /* VALORES NAO ENVIADOS */
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'ENDERECOENTREGA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'NUMERO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'COMPLEMENTO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'BAIRROENTREGA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CIDADE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'UF');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CEP');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'INSCRICAOESTADUAL');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'EMAILCONTATO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_VAZIO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'LEADTIMEENTREGA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_ZERO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'PRIORIDADEGERACAOSERPARACAO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_AGENT_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_ZERO);
         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;
      CLOSE C_AGENTE;
   
   END LOOP;
   /*
   L_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE(L_DOMDOC);
   DBMS_XMLDOM.FREEDOCUMENT(L_DOMDOC);
   
   OUT_XML_ARQ.XMLRETORNO := L_XMLTYPE;
   
   PIPE ROW(OUT_XML_ARQ);
   */
   RETURN;
END;
/

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