CREATE OR REPLACE FUNCTION EXPORTA_PRODUTO_SYSTAX(
   P_PRODUTOS IN VARCHAR2) RETURN XMLTYPE
IS
   L_XMLTYPE XMLTYPE;
   L_DOMDOC DBMS_XMLDOM.DOMDOCUMENT;
   L_ROOT_NODE DBMS_XMLDOM.DOMNODE;
   L_PRODUTO_ELEMENT DBMS_XMLDOM.DOMELEMENT;
   L_PRODUTOS_NODE DBMS_XMLDOM.DOMNODE;
   L_PROD_ELEMENT DBMS_XMLDOM.DOMELEMENT;
   L_PROD_NODE DBMS_XMLDOM.DOMNODE;
   L_CADASTRO_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;
   V_NMPRODUTO VARCHAR2(50);
   V_CDCLASFISC VARCHAR2(10);
   V_CDARVPROD VARCHAR2(20);
   V_CDEXTIPI VARCHAR2(10);
   V_CDBARPRODUTO VARCHAR2(30);
   V_CDCSTDIG1PROD VARCHAR2(1);
   V_CDPRODUTO VARCHAR2(10);
   CURSOR C_PRODUTO(C_PRODUTOS IN VARCHAR2) IS
      SELECT SUBSTR(NMPRODUTO, 1, 50) NMPRODUTO, 
             CDCLASFISC, 
             CDARVPROD, 
             CDEXTIPI, 
             CDBARPRODUTO, 
             CDCSTDIG1PROD, 
             CDPRODUTO
        FROM PRODUTO
       WHERE INSTR(C_PRODUTOS, '_'||CDARVPROD||'_') > 0;
BEGIN
   L_DOMDOC    := DBMS_XMLDOM.NEWDOMDOCUMENT;
   
   L_ROOT_NODE := DBMS_XMLDOM.MAKENODE(L_DOMDOC);
   
   L_PROD_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'systaxCadastro');
   
   L_CADASTRO_NODE := DBMS_XMLDOM.APPENDCHILD(L_ROOT_NODE, DBMS_XMLDOM.MAKENODE(L_PROD_ELEMENT));
   
   OPEN C_PRODUTO(P_PRODUTOS);
   LOOP
      FETCH C_PRODUTO INTO V_NMPRODUTO, V_CDCLASFISC, V_CDARVPROD, V_CDEXTIPI, V_CDBARPRODUTO, V_CDCSTDIG1PROD, V_CDPRODUTO;
   
      EXIT WHEN C_PRODUTO%NOTFOUND;
      L_PROD_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'produto');
      L_PROD_NODE     := DBMS_XMLDOM.APPENDCHILD(L_CADASTRO_NODE, DBMS_XMLDOM.MAKENODE(L_PROD_ELEMENT));
   
      L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'cProd');
      L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
      L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CDARVPROD);
      L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
   
      L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'NCM');
      L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
      L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CDCLASFISC);
      L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
   
      L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'EX_TIPI');
      L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
      L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CDEXTIPI);
      L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
   
      L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'cEAN');
      L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
      L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CDBARPRODUTO);
      L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
   
      L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'descricao');
      L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
      L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_NMPRODUTO);
      L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
   
      L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'orig');
      L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
      L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CDCSTDIG1PROD);
      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_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
      L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, '');
      L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
   END LOOP;

   CLOSE C_PRODUTO;

   L_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE(L_DOMDOC);

   DBMS_XMLDOM.FREEDOCUMENT(L_DOMDOC);

   RETURN L_XMLTYPE;
EXCEPTION
   WHEN OTHERS THEN
      IF C_PRODUTO%ISOPEN THEN
         CLOSE C_PRODUTO;
      END IF;
      DBMS_XMLDOM.FREEDOCUMENT(L_DOMDOC);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('EXPORTA_PRODUTO_SYSTAX','6.35.033'); END;
/
