CREATE OR REPLACE TRIGGER MHA_TRG_UPDPROD_XML
BEFORE UPDATE OF NMPRODUTO, SGUNIDADE, VRPESOUNID, VRFATOCONV, NRDIASVALIDA, VRPESOBRUTPR, VRPESOLIQUPR ON PRODUTO
FOR EACH ROW
BEGIN
   :NEW.USR_DTEXPXML := NULL;
END;
/

DROP TYPE XML_ARQ
/

DROP TYPE XML
/

CREATE OR REPLACE TYPE XML AS OBJECT
   (XMLRETORNO XMLTYPE,
    CONSTRUCTOR FUNCTION XML RETURN SELF AS RESULT)
/

CREATE OR REPLACE TYPE XML_ARQ AS TABLE OF XML
/

create or replace
FUNCTION MHA_EXPORTA_PRODUTO RETURN XML_ARQ PIPELINED
IS
   V_CLIDEPOS VARCHAR2(6);
   V_N VARCHAR2(3);
   V_S VARCHAR2(3);
   V_ZERO INTEGER;
   V_SETE INTEGER;
   
   V_CDEMPRESA VARCHAR2(2);
   V_CDFILIAL VARCHAR2(10);
   V_CODPRODUTO VARCHAR2(20);
   V_DESCRICAO VARCHAR2(50);
   V_CODFAMILIA VARCHAR2(20);
   V_FAMILIADESCRICAO VARCHAR2(50);
   V_CODUNIDADEMEDIDA VARCHAR2(10);
   V_UNIDADEMEDIDADESCRICAO VARCHAR2(30);
   V_PESO FLOAT;
   V_QTDEEMBALAGEM FLOAT;
   V_DIASVALIDADE FLOAT;
   V_TARA FLOAT;
   V_SGPCODIGO VARCHAR2(10);
   V_SGPDESCRICAO VARCHAR2(40);
   V_VAZIO VARCHAR2(10);
   V_PRODUTO VARCHAR2(10);
   V_TIPOINTERFACE VARCHAR2(5);
   
   V_PESOOBRIG VARCHAR2(3);
   
   OUT_XML_ARQ XML := XML(NULL);
   
   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_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_PRODUTO IS
      SELECT P.CDPRODUTO, E.CDEMPRESA, PP.CDFILWMSMHA, P.NMPRODUTO, P_ANT.CDARVPROD NV_ANT, P_ANT.NMPRODUTO AS NV_ANTERIOR, UPPER(P.SGUNIDADE),
             U.DSUNIDADE, NVL(ROUND(P.VRPESOBRUTPR,9),0), NVL(P.VRFATOCONV, 1), NVL(P.NRDIASVALIDA,0), NVL((NVL(P.VRPESOBRUTPR,0)-NVL(P.VRPESOLIQUPR,0)),0),
             CASE WHEN PF.IDPRODATIV = 'A'
                  THEN 'INC'
                  ELSE 'EXC'
              END AS TIPOINTERFACE,
              P.CDARVPROD, SUB.CDSUBGRPROD, SUB.NMSUBGRPROD
        FROM PRODFILI PF JOIN PRODUTO P
                           ON PF.CDPRODUTO = P.CDPRODUTO
                         JOIN FILIAL F
                           ON PF.CDFILIAL = F.CDFILIAL
                         JOIN PARAMFILIAL PP
                           ON PP.CDFILIAL = F.CDFILIAL
                         JOIN EMPRESA E
                           ON F.CDEMPRESA = E.CDEMPRESA
                         JOIN PRODUTO P_ANT
                           ON P_ANT.CDARVPROD = SUBSTR(P.CDARVPROD, 1, 5)
                         JOIN UNIDMEDIDA U
                           ON P.SGUNIDADE = U.SGUNIDADE
                         JOIN RELACPRODOL R
                           ON R.CDPRODARVOL   = P.CDPRODUTO
                         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 = F.CDFILIAL
       WHERE F.IDFUNCAOFILI = 'O'
         AND INSTR(P.CDARVPROD, '9') = 1
         AND P.CDNVPRODUTO = (SELECT MAX(CDNVPRODUTO) FROM PRODUTO)
         AND PP.CDFILWMSMHA IS NOT NULL
         AND P.USR_DTEXPXML IS NULL
         AND PR.CDFILISUBGR  = 'P'
       ORDER BY P.CDPRODUTO, E.CDEMPRESA, F.CDFILIAL;
BEGIN
   V_CLIDEPOS := '006';
   V_N        := 'Nao';
   V_ZERO     := 0;
   V_SETE     := 7;
   V_VAZIO    := '*';
   V_S        := 'Sim';
   
   OPEN C_PRODUTO;
   LOOP
      FETCH C_PRODUTO INTO V_PRODUTO, V_CDEMPRESA, V_CDFILIAL, V_DESCRICAO, V_CODFAMILIA, V_FAMILIADESCRICAO, V_CODUNIDADEMEDIDA,
                           V_UNIDADEMEDIDADESCRICAO, V_PESO, V_QTDEEMBALAGEM, V_DIASVALIDADE, V_TARA,
                           V_TIPOINTERFACE,
                           V_CODPRODUTO, V_SGPCODIGO, V_SGPDESCRICAO;
      EXIT WHEN C_PRODUTO%NOTFOUND;
      
      IF (V_CODPRODUTO IS NOT NULL) THEN
         L_DOMDOC    := DBMS_XMLDOM.NEWDOMDOCUMENT;
         L_ROOT_NODE := DBMS_XMLDOM.MAKENODE(L_DOMDOC);

         L_PROD_ELEMENT := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CadastroProduto');
         DBMS_XMLDOM.SETATTRIBUTE(L_PROD_ELEMENT, 'CODIGO', V_CODPRODUTO);
         L_PROD_NODE    := DBMS_XMLDOM.APPENDCHILD(L_ROOT_NODE, DBMS_XMLDOM.MAKENODE(L_PROD_ELEMENT));
         
         /* VALORES FIXOS */
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'TIPOGERADOR');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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_PROD_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_PROD_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_PROD_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_PROD_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, 'CODPRODUTO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CODPRODUTO);
         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_DESCRICAO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CODFAMILIA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CODFAMILIA);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'FAMILIADESCRICAO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_FAMILIADESCRICAO);
         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, 'CODSUBGRUPO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, 'PPAD');
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
        
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'SUBGRUPODESCRICAO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, 'PESO PADRAO');
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
        
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CODSECAO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'SECAODESCRICAO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'CODUNIDADEMEDIDA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CODUNIDADEMEDIDA);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'UNIDADEMEDIDADESCRICAO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_UNIDADEMEDIDADESCRICAO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'PESO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_PESO);
         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, 'ALTURA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'LARGURA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'COMPRIMENTO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'CODBARRASUNIDADE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CODPRODUTO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'QTDEEMBALAGEM');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_QTDEEMBALAGEM);
         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, 'REQUISICAOFRACIONADA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'CODBARRASDUN14');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'LASTROPALETE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'CAMADAPALETE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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));
         /* *** */
         /* VALORES FIXOS */
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CONTROLELOTE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_N);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CONTROLEDATAVALIDADE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_S);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CONTROLEFABRICACAO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_N);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CONTROLESSCC');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_N);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'EMBALAGEMOBRIGATORIO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_N);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         /*IF (V_CODUNIDADEMEDIDA = 'KG') THEN
            V_PESOOBRIG := V_S;
         ELSE
            V_PESOOBRIG := V_N;
         END IF;*/
         
         V_PESOOBRIG := V_N;
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'PESOOBRIGATORIO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_PESOOBRIG);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         /* *** */

         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'DIASVALIDADE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_DIASVALIDADE);
         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, 'PERCENTUALTOLERANCIA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'DIASQUARENTENA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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));
         /* *** */
         /* VALORES FIXOS */
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'DIASSHELFLIFE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_SETE);
         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, 'DIASFIFO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'CODUNIDADECAIXAFECHADA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_CODUNIDADEMEDIDA);
         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, 'CODBARRASRECEBIMENTO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'QTDEEMBALAGEMRECEBIMENTO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'DIASPERMITIDORECEBIMENTO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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));
         
         /* *** */
         /* VALORES FIXOS */
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'VALORUNITARIO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'TARA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_TARA);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'SGPCODIGO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_SGPCODIGO);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'SGPDESCRICAO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_SGPDESCRICAO);
         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, 'TEMPERATURAIDEAL');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'TEMPERATURAMAXIMA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'TEMPERATURAMINRECUP');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'TEMPERATURAMAXRECUP');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'TEMPERATURAMINEXP');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'TEMPERATURAMAXEXP');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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));
         
         /* *** */
         /* VALORES FIXOS */
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'CONTROLESERIAL');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_N);
         L_NAME_TEXTNODE := DBMS_XMLDOM.APPENDCHILD(L_NAME_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_TEXT));
         
         L_NAME_ELEMENT  := DBMS_XMLDOM.CREATEELEMENT(L_DOMDOC, 'INSPECAOQUALIDADE');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_NODE,DBMS_XMLDOM.MAKENODE(L_NAME_ELEMENT));
         L_NAME_TEXT     := DBMS_XMLDOM.CREATETEXTNODE(L_DOMDOC, V_N);
         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, 'PRDNCM');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'REFERENCIA');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'TIPO_PRODUTO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'PESOMEDIO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'TEMPESOMEDIO');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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, 'PERCTOLERANCIAREC');
         L_NAME_NODE     := DBMS_XMLDOM.APPENDCHILD(L_PROD_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_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE(L_DOMDOC);
         DBMS_XMLDOM.FREEDOCUMENT(L_DOMDOC);
         OUT_XML_ARQ.XMLRETORNO := L_XMLTYPE;
         
         PIPE ROW(OUT_XML_ARQ);
      END IF;
   END LOOP;
   
   CLOSE C_PRODUTO;
   
   RETURN;
END;
/

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