CREATE OR REPLACE FUNCTION FORMATMASK (P_IDTIPOINS IN VARCHAR2,
									            P_INSCRICAO IN VARCHAR2) RETURN VARCHAR2 IS

   V_FORMATMASK VARCHAR2(50);
BEGIN
   SELECT CASE P_IDTIPOINS
            WHEN 'F' THEN (SUBSTR(P_INSCRICAO,0,3)||'.'||
                           SUBSTR(P_INSCRICAO,3,3)||'.'||
                           SUBSTR(P_INSCRICAO,6,3)||'-'||
                           SUBSTR(P_INSCRICAO,9,2))
            WHEN 'J' THEN (SUBSTR(P_INSCRICAO,0,2)||'.'||
                           SUBSTR(P_INSCRICAO,3,3)||'.'||
                           SUBSTR(P_INSCRICAO,6,3)||'/'||
                           SUBSTR(P_INSCRICAO,9,4)||'-'||
                           SUBSTR(P_INSCRICAO,13))
            ELSE  P_INSCRICAO END
     INTO V_FORMATMASK
     FROM DUAL;

   RETURN(V_FORMATMASK);
END;
/
DROP TYPE RELATORIO_AF_EMAIL_TABLE
/ 
DROP TYPE RELATORIO_AF_EMAIL_TYPE
/
CREATE TYPE RELATORIO_AF_EMAIL_TYPE AS OBJECT (
  NRSEQAPURAF     VARCHAR2(10),
  CDFILDEST       VARCHAR2(4),
  NMFILIAL        VARCHAR2(30),
  CDFILLANC       VARCHAR2(4),
  NRAUTOFORN      VARCHAR2(10),
  CDOPERADOR      VARCHAR2(12),
  CDFORNECED      VARCHAR2(21),
  VRTOTALAF       FLOAT(126),
  DTAUTOFORN      DATE,
  IDTIPOEMITAF    VARCHAR2(1),
  NMEMPRESA       VARCHAR2(40),
  SGESTADOEMPR    VARCHAR2(2),
  NRCEPEMPR       VARCHAR2(15),
  DSENDEEMPR      VARCHAR2(50),
  NMMUNIEMPR      VARCHAR2(30),
  IDTPIJUREMPR    VARCHAR2(30),
  NRINSJUREMPR    VARCHAR2(20),
  NMBAIREMPR      VARCHAR2(50),
  NMFILIDEST      VARCHAR2(30),
  NRINSJURFILI    VARCHAR2(20),
  IDTPIJURFILI    VARCHAR2(1),
  NMRAZSOCFILI    VARCHAR2(100),
  DSPERIENTFIL    VARCHAR2(50),
  OBSERVACAO      VARCHAR2(1),
  CDINSCESTA      VARCHAR2(16),
  NRINSCESTAEMPR  VARCHAR2(16),
  CDFORMPGTO      VARCHAR2(2),
  IDTPFORPGTAF    VARCHAR2(1),
  NRDIAPGFIXAF    NUMBER(2),
  NRPARCPGTOAF    NUMBER(2),
  IDSITUATUAAF    VARCHAR2(1),
  IDIMAUTOFORN    VARCHAR2(1),
  IDTIPOFRETAF    VARCHAR2(1),
  IDAUTOFORNEX    VARCHAR2(1),
  IDTIPOLANCAF    VARCHAR2(1),
  IDAFFORALIM     VARCHAR2(1),
  IDBAIXAAF       VARCHAR2(1),
  IDTPIJURFORN    VARCHAR2(1),
  NRINSJURFORN    VARCHAR2(20),
  NMRAZSOCFORN    VARCHAR2(100),
  NMFANTFORN      VARCHAR2(40),
  DSENDEFORN      VARCHAR2(50),
  NMCONTFORN      VARCHAR2(30),
  NRTELEFORN1     VARCHAR2(25),
  DSEMAILCOT      VARCHAR2(100),
  IDTPIJURCLIE    VARCHAR2(1),
  NRINSJURCLIE    VARCHAR2(20),
  NMRAZSOCCLIE    VARCHAR2(100),
  DSENDECLIE      VARCHAR2(50),
  NMBAIRCLIE      VARCHAR2(50),
  SGESTADOCLIE    VARCHAR2(2),
  NRCEPCLIE       VARCHAR2(15),
  NRINSESTCLIE    VARCHAR2(20),
  NMMUNICLIE      VARCHAR2(30),
  CDCLIENTE       VARCHAR2(21),
  NMOPERADOR      VARCHAR2(40),
  NMMUNICIFIL     VARCHAR2(30),
  DSENDEFILI      VARCHAR2(50),
  NMBAIRFILI      VARCHAR2(30),
  SGESTADOFIL     VARCHAR2(2),
  NRCEPFILI       VARCHAR2(15),
  DTPROGENTAF     DATE,
  CDPRODUTO       VARCHAR2(10),
  QTPROGENTAF     FLOAT(126),
  VRPREUNIAF      FLOAT(126),
  VALTOTAL        FLOAT(126),
  CDARVPROD       VARCHAR2(20),
  NMPRODUTO       VARCHAR2(50),
  CDMARCA         VARCHAR2(5),
  NMMARCA         VARCHAR2(20),
  NMMARCA_SEG     VARCHAR2(20),
  NMSEGMARCA      VARCHAR2(50),
  TXOBSITMAF      VARCHAR2(2000),
  DSPRAZOPGTAF    VARCHAR2(200),
  NMFORMPGTO      VARCHAR2(30),
  DSOBSFILIAL     VARCHAR2(2000),
  IDEXIBEOBSFIL   VARCHAR2(1))
/
CREATE TYPE RELATORIO_AF_EMAIL_TABLE AS TABLE OF RELATORIO_AF_EMAIL_TYPE
/
CREATE OR REPLACE FUNCTION RELATORIO_AF_EMAIL (
  P_NRSEQAPURAF      IN VARCHAR2,
  P_CDFILLANC        IN VARCHAR2,
  P_IMPRIMEOBSPROD   IN VARCHAR2,
  P_IDEXIBEOBSFIL    IN VARCHAR2, 
  P_CDFORNECED       IN VARCHAR2,
  P_CDARVPRODINI     IN VARCHAR2,
  P_CDARVPRODFIN     IN VARCHAR2,
  P_CDFILDEST        IN VARCHAR2, --Lista
  P_NRAUTOFORN       IN VARCHAR2, --Lista
  P_NRSEQENVIOAFORN  IN VARCHAR2, --Lista
  P_CDOPERADOR       IN VARCHAR2, --Lista
  P_DTENTREGAINI     IN DATE,
  P_DTENTREGAFIN     IN DATE,
  P_DTAUTOFORNINI    IN DATE,
  P_DTAUTOFORNFIN    IN DATE)
  RETURN RELATORIO_AF_EMAIL_TABLE PIPELINED AS L_RELATORIO_AF_EMAIL_TYPE RELATORIO_AF_EMAIL_TYPE;
  V_PRAZOPGTAF    VARCHAR2(200);
  V_DSOBSFILIAL   VARCHAR2(2000);
BEGIN
  FOR C_ITEM IN 
    ( 
      WITH 
        AF AS ( SELECT 
                  AUTOFORNE.NRSEQAPURAF,   AUTOFORNE.CDFILDEST,      AUTOFORNE.CDFILLANC,    AUTOFORNE.NRAUTOFORN,   AUTOFORNE.CDOPERADOR,
                  AUTOFORNE.CDFORNECED,    AUTOFORNE.VRTOTALAF,      AUTOFORNE.DTAUTOFORN,   AUTOFORNE.IDTIPOEMITAF, AUTOFORNE.CDFORMPGTO,
                  AUTOFORNE.IDTPFORPGTAF,  AUTOFORNE.NRDIAPGFIXAF,   AUTOFORNE.NRPARCPGTOAF, AUTOFORNE.IDSITUATUAAF, AUTOFORNE.IDIMAUTOFORN,
                  AUTOFORNE.IDTIPOFRETAF,  AUTOFORNE.IDAUTOFORNEX,   AUTOFORNE.IDTIPOLANCAF, AUTOFORNE.IDAFFORALIM,  AUTOFORNE.IDBAIXAAF,
                  FILIAL.NMFILIAL,         FORMATMASK(FILIAL.IDTPIJURFILI, FILIAL.NRINSJURFILI) AS NRINSJURFILI,      FILIAL.IDTPIJURFILI,    FILIAL.NMRAZSOCFILI,    FILIAL.CDINSCESTA,
                  FILIAL.CDREGIAO,         FILIAL.NMFILIAL AS NMFILIDEST,
                  EMPRESA.NMEMPRESA,       SUBSTR(EMPRESA.NRCEPEMPR,0,5)||'-'||SUBSTR(EMPRESA.NRCEPEMPR,6,3) NRCEPEMPR,        EMPRESA.DSENDEEMPR,     EMPRESA.IDTPIJUREMPR,   EMPRESA.SGESTADO AS SGESTADOEMPR,
                  FORMATMASK(EMPRESA.IDTPIJUREMPR, EMPRESA.NRINSJUREMPR) AS NRINSJUREMPR,    EMPRESA.NMBAIREMPR,       EMPRESA.NRINSCESTA AS NRINSCESTAEMPR,
                  MUNICIPIO.NMMUNICIPIO AS NMMUNIEMPR, PARCMPSUP.DSPERIENTFIL, NVL(P_IMPRIMEOBSPROD, 'N') OBSERVACAO
                FROM AUTOFORNE  INNER JOIN FILIAL    ON AUTOFORNE.CDFILDEST = FILIAL.CDFILIAL
                                INNER JOIN PARCMPSUP ON AUTOFORNE.CDFILDEST = PARCMPSUP.CDFILIAL
                                INNER JOIN EMPRESA   ON AUTOFORNE.CDEMPRESA = EMPRESA.CDEMPRESA
                                INNER JOIN MUNICIPIO ON EMPRESA.CDPAIS      = MUNICIPIO.CDPAIS
                                                    AND EMPRESA.SGESTADO    = MUNICIPIO.SGESTADO
                                                    AND EMPRESA.CDMUNICIPIO = MUNICIPIO.CDMUNICIPIO
                WHERE 
                  NVL(AUTOFORNE.NRSEQAPURAF, 'N') = NVL(P_NRSEQAPURAF, NVL(AUTOFORNE.NRSEQAPURAF, 'N')) AND
                  AUTOFORNE.CDFILLANC = NVL(P_CDFILLANC, AUTOFORNE.CDFILLANC) AND
                  INSTR(NVL(P_CDFILDEST, FILIAL.CDFILIAL), FILIAL.CDFILIAL) > 0 AND
                  INSTR(NVL(P_NRAUTOFORN, AUTOFORNE.NRAUTOFORN), AUTOFORNE.NRAUTOFORN) > 0  AND
                  INSTR(NVL(P_NRSEQENVIOAFORN, NVL(AUTOFORNE.NRSEQENVIOAFORN, 'X')), NVL(AUTOFORNE.NRSEQENVIOAFORN, 'X')) > 0 AND
                  (TO_CHAR(AUTOFORNE.DTAUTOFORN, 'DD/MM/YYYY') BETWEEN NVL(P_DTAUTOFORNINI, TO_CHAR(AUTOFORNE.DTAUTOFORN, 'DD/MM/YYYY')) AND NVL(P_DTAUTOFORNFIN, TO_CHAR(AUTOFORNE.DTAUTOFORN, 'DD/MM/YYYY'))) AND
                  AUTOFORNE.IDAFFORALIM = 'N' AND
                  AUTOFORNE.IDSITUATUAAF IS NULL  AND
                  AUTOFORNE.IDBAIXAAF    IS NULL  AND
                  NVL(AUTOFORNE.IDAFOPERLOG, '1') = '1' AND
                  ((NVL(AUTOFORNE.IDAFPRODWEB, 'N') = 'N') OR (AUTOFORNE.IDBAIXAAF IN ('2', '3')))  AND
                  NVL(AUTOFORNE.IDBAIXAAF,'0') <> '4' AND
                  NVL(AUTOFORNE.IDBAIXAAF,'0') <> '6' AND
                  NVL(AUTOFORNE.IDBAIXAAF,'0') <> '5'),

        IT AS ( SELECT 
                  ITEMAUTF.CDFILDEST,    ITEMAUTF.CDFILLANC,   ITEMAUTF.NRAUTOFORN,
                  ITEMAUTF.DTPROGENTAF,  ITEMAUTF.QTPROGENTAF, ITEMAUTF.VRPREUNIAF,
                  ITEMAUTF.QTPROGENTAF * ITEMAUTF.VRPREUNIAF AS VALTOTAL,
                  PRODUTO.CDPRODUTO,     PRODUTO.CDARVPROD,    PRODUTO.NMPRODUTO,
                  MARCA.CDMARCA,         MARCA.NMMARCA,        SEGMARCA.NMMARCA AS NMMARCA_SEG,
                  ITEMAUTF.TXOBSITMAF,   CASE NVL(SEGMARCA.NMMARCA, 'XXXX') WHEN 'XXXX' THEN MARCA.NMMARCA ELSE MARCA.NMMARCA||'/'||SEGMARCA.NMMARCA END NMSEGMARCA
                FROM 
                  ITEMAUTF  INNER JOIN PRODUTO        ON ITEMAUTF.CDPRODUTO   = PRODUTO.CDPRODUTO
                            INNER JOIN MARCA          ON ITEMAUTF.CDMARCA     = MARCA.CDMARCA
                             LEFT JOIN MARCA SEGMARCA ON ITEMAUTF.CDSEGMARCA  = SEGMARCA.CDMARCA
                WHERE 
                  (TO_CHAR(DTPROGENTAF, 'DD/MM/YYYY') BETWEEN NVL(P_DTENTREGAINI, TO_CHAR(DTPROGENTAF, 'DD/MM/YYYY')) AND NVL(P_DTENTREGAFIN, TO_CHAR(DTPROGENTAF, 'DD/MM/YYYY'))) AND
                  PRODUTO.CDARVPROD BETWEEN NVL(P_CDARVPRODINI, PRODUTO.CDARVPROD) AND NVL(P_CDARVPRODFIN, PRODUTO.CDARVPROD)),

        OP AS ( SELECT
                  CDOPERADOR, NMOPERADOR
                FROM
                  OPERADOR
                WHERE (INSTR(NVL(P_CDOPERADOR, CDOPERADOR), CDOPERADOR) > 0)),

        FN AS ( SELECT 
                  F.CDFORNECED, F.IDTPIJURFORN, FORMATMASK(F.IDTPIJURFORN, F.NRINSJURFORN) AS NRINSJURFORN, F.NMRAZSOCFORN, F.NMFANTFORN,
                  E.DSENDEFORN, E.NMCONTFORN, E.NRTELEFORN1, F.DSEMAILCOT, E.IDTPENDEFORN
                FROM
                  FORNECEDOR F LEFT JOIN ENDEFORN E  ON F.CDFORNECED   = E.CDFORNECED
                WHERE 
                  F.CDFORNECED = NVL(P_CDFORNECED, F.CDFORNECED)  AND 
                  E.IDTPENDEFORN = 'P'),

        CF AS ( SELECT 
                  CLIENFILIAL.CDCLIENTE, CLIENFILIAL.CDFILIAL
                FROM
                  CLIENFILIAL
                WHERE
                  CLIENFILIAL.IDCLIENPRIN = 'S'),

        EC AS ( SELECT
                  ENDECLIE.DSENDECLIE, ENDECLIE.NMBAIRCLIE, ENDECLIE.SGESTADO AS SGESTADOCLIE, SUBSTR(ENDECLIE.NRCEPCLIE,0,5)||'-'||SUBSTR(ENDECLIE.NRCEPCLIE,6,3) NRCEPCLIE,
                  CLIENTE.CDCLIENTE, CLIENTE.IDTPIJURCLIE, FORMATMASK(CLIENTE.IDTPIJURCLIE, CLIENTE.NRINSJURCLIE) AS NRINSJURCLIE, CLIENTE.NMRAZSOCCLIE,  CLIENTE.NRINSESTCLIE,
                  MUNICIPIO.NMMUNICIPIO AS NMMUNICLIE
                FROM
                  CLIENTE INNER JOIN ENDECLIE  ON CLIENTE.CDCLIENTE    = ENDECLIE.CDCLIENTE
                          INNER JOIN MUNICIPIO ON ENDECLIE.CDPAIS      = MUNICIPIO.CDPAIS
                                              AND ENDECLIE.SGESTADO    = MUNICIPIO.SGESTADO
                                              AND ENDECLIE.CDMUNICIPIO = MUNICIPIO.CDMUNICIPIO
                WHERE
                  ENDECLIE.IDTPENDECLIE = 'P'),

        EF AS ( SELECT
                  ENDEFILI.CDFILIAL, SUBSTR(ENDEFILI.NRCEPFILI,0,5)||'-'||SUBSTR(ENDEFILI.NRCEPFILI,6,3) NRCEPFILI, ENDEFILI.SGESTADO AS SGESTADOFIL,
                  ENDEFILI.NMBAIRFILI, DSENDEFILI, NMMUNICIPIO AS NMMUNICIFIL
                FROM
                  ENDEFILI  INNER JOIN MUNICIPIO ON ENDEFILI.CDPAIS       = MUNICIPIO.CDPAIS
                                                AND ENDEFILI.SGESTADO     = MUNICIPIO.SGESTADO
                                                AND ENDEFILI.CDMUNICIPIO  = MUNICIPIO.CDMUNICIPIO
                WHERE
                  ENDEFILI.IDTPENDEFILI = 'P'),

        FP AS ( SELECT
                  CDFORMPGTO, NMFORMPGTO
                FROM 
                  FORMPGTO)

        SELECT 
          AF.NRSEQAPURAF,  AF.CDFILDEST,       AF.NMFILIAL,      AF.CDFILLANC,     AF.NRAUTOFORN,    AF.CDOPERADOR,
          AF.CDFORNECED,   AF.VRTOTALAF,       AF.DTAUTOFORN,    AF.IDTIPOEMITAF,  AF.NMEMPRESA,     AF.SGESTADOEMPR,
          AF.NRCEPEMPR,    AF.DSENDEEMPR,      AF.NMMUNIEMPR,    AF.IDTPIJUREMPR,  AF.NRINSJUREMPR,  AF.NMBAIREMPR,
          AF.NMFILIDEST,   AF.NRINSJURFILI,    AF.IDTPIJURFILI,  AF.NMRAZSOCFILI,  AF.DSPERIENTFIL,  AF.OBSERVACAO,
          AF.CDINSCESTA,   AF.NRINSCESTAEMPR,  AF.CDFORMPGTO,    AF.IDTPFORPGTAF,  AF.NRDIAPGFIXAF,  AF.NRPARCPGTOAF,
          AF.IDSITUATUAAF, AF.IDIMAUTOFORN,    AF.IDTIPOFRETAF,  AF.IDAUTOFORNEX,  AF.IDTIPOLANCAF,  AF.IDAFFORALIM,
          AF.IDBAIXAAF,    FN.IDTPIJURFORN,    FN.NRINSJURFORN,  FN.NMRAZSOCFORN,  FN.NMFANTFORN,    FN.DSENDEFORN,
          FN.NMCONTFORN,   FN.NRTELEFORN1,     FN.DSEMAILCOT,    EC.IDTPIJURCLIE,  EC.NRINSJURCLIE,  EC.NMRAZSOCCLIE,
          EC.DSENDECLIE,   EC.NMBAIRCLIE,      EC.SGESTADOCLIE,  EC.NRCEPCLIE,     EC.NRINSESTCLIE,  EC.NMMUNICLIE,
          CF.CDCLIENTE,    OP.NMOPERADOR,      EF.NMMUNICIFIL,   EF.DSENDEFILI,    EF.NMBAIRFILI,    EF.SGESTADOFIL,
          EF.NRCEPFILI,    IT.DTPROGENTAF,     IT.QTPROGENTAF,   IT.VRPREUNIAF,    IT.VALTOTAL,      IT.CDPRODUTO,
          IT.CDARVPROD,    IT.NMPRODUTO,       IT.CDMARCA,       IT.NMMARCA,       IT.NMMARCA_SEG,   IT.TXOBSITMAF,
          IT.NMSEGMARCA,   FP.NMFORMPGTO
        FROM 
          AF INNER JOIN IT ON IT.CDFILDEST  = AF.CDFILDEST
                          AND IT.CDFILLANC  = AF.CDFILLANC
                          AND IT.NRAUTOFORN = AF.NRAUTOFORN
             INNER JOIN OP ON AF.CDOPERADOR = OP.CDOPERADOR
             INNER JOIN FN ON AF.CDFORNECED = FN.CDFORNECED
              LEFT JOIN CF ON AF.CDFILDEST  = CF.CDFILIAL
              LEFT JOIN EC ON CF.CDCLIENTE  = EC.CDCLIENTE
              LEFT JOIN EF ON AF.CDFILDEST  = EF.CDFILIAL
              LEFT JOIN FP ON AF.CDFORMPGTO = FP.CDFORMPGTO)
  LOOP
    SELECT 
      LISTAGG(CONCAT(CONCAT(CONCAT('Prazo (dias): ', NRDIASPGTOAF), CONCAT(' Pencentual: ', VRPERCPGTOAF)), '% / ')) WITHIN GROUP (ORDER BY NRDIASPGTOAF)
    INTO
      V_PRAZOPGTAF
    FROM
      PRAZOPGTAF
    WHERE
      CDFILDEST  = C_ITEM.CDFILDEST AND
      CDFILLANC  = C_ITEM.CDFILLANC AND
      NRAUTOFORN = C_ITEM.NRAUTOFORN;

    BEGIN
      SELECT
        DSOBSFILIAL
      INTO
        V_DSOBSFILIAL
      FROM
        PARCMPSUP
      WHERE
        CDFILIAL = C_ITEM.CDFILDEST AND
        P_IDEXIBEOBSFIL = 'S';
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_DSOBSFILIAL := NULL;
    END;

    L_RELATORIO_AF_EMAIL_TYPE := RELATORIO_AF_EMAIL_TYPE
      ( 
        C_ITEM.NRSEQAPURAF,   C_ITEM.CDFILDEST,       C_ITEM.NMFILIAL,      C_ITEM.CDFILLANC,     C_ITEM.NRAUTOFORN,    C_ITEM.CDOPERADOR,
        C_ITEM.CDFORNECED,    C_ITEM.VRTOTALAF,       C_ITEM.DTAUTOFORN,    C_ITEM.IDTIPOEMITAF,  C_ITEM.NMEMPRESA,     C_ITEM.SGESTADOEMPR,
        C_ITEM.NRCEPEMPR,     C_ITEM.DSENDEEMPR,      C_ITEM.NMMUNIEMPR,    C_ITEM.IDTPIJUREMPR,  C_ITEM.NRINSJUREMPR,  C_ITEM.NMBAIREMPR,
        C_ITEM.NMFILIDEST,    C_ITEM.NRINSJURFILI,    C_ITEM.IDTPIJURFILI,  C_ITEM.NMRAZSOCFILI,  C_ITEM.DSPERIENTFIL,  C_ITEM.OBSERVACAO,
        C_ITEM.CDINSCESTA,    C_ITEM.NRINSCESTAEMPR,  C_ITEM.CDFORMPGTO,    C_ITEM.IDTPFORPGTAF,  C_ITEM.NRDIAPGFIXAF,  C_ITEM.NRPARCPGTOAF,
        C_ITEM.IDSITUATUAAF,  C_ITEM.IDIMAUTOFORN,    C_ITEM.IDTIPOFRETAF,  C_ITEM.IDAUTOFORNEX,  C_ITEM.IDTIPOLANCAF,  C_ITEM.IDAFFORALIM,
        C_ITEM.IDBAIXAAF,     C_ITEM.IDTPIJURFORN,    C_ITEM.NRINSJURFORN,  C_ITEM.NMRAZSOCFORN,  C_ITEM.NMFANTFORN,    C_ITEM.DSENDEFORN,
        C_ITEM.NMCONTFORN,    C_ITEM.NRTELEFORN1,     C_ITEM.DSEMAILCOT,    C_ITEM.IDTPIJURCLIE,  C_ITEM.NRINSJURCLIE,  C_ITEM.NMRAZSOCCLIE,
        C_ITEM.DSENDECLIE,    C_ITEM.NMBAIRCLIE,      C_ITEM.SGESTADOCLIE,  C_ITEM.NRCEPCLIE,     C_ITEM.NRINSESTCLIE,  C_ITEM.NMMUNICLIE,
        C_ITEM.CDCLIENTE,     C_ITEM.NMOPERADOR,      C_ITEM.NMMUNICIFIL,   C_ITEM.DSENDEFILI,    C_ITEM.NMBAIRFILI,    C_ITEM.SGESTADOFIL,
        C_ITEM.NRCEPFILI,     C_ITEM.DTPROGENTAF,     C_ITEM.CDPRODUTO,     C_ITEM.QTPROGENTAF,   C_ITEM.VRPREUNIAF,    C_ITEM.VALTOTAL,
        C_ITEM.CDARVPROD,     C_ITEM.NMPRODUTO,       C_ITEM.CDMARCA,       C_ITEM.NMMARCA,       C_ITEM.NMMARCA_SEG,   C_ITEM.NMSEGMARCA,
        C_ITEM.TXOBSITMAF,    V_PRAZOPGTAF,           C_ITEM.NMFORMPGTO,    V_DSOBSFILIAL,        P_IDEXIBEOBSFIL
      );
    PIPE ROW (L_RELATORIO_AF_EMAIL_TYPE);
  END LOOP;
  RETURN;
END RELATORIO_AF_EMAIL; 