DROP TYPE RELATORIO_AF_EMAIL_TABLE
/

DROP TYPE RELATORIO_AF_EMAIL_TYPE
/

CREATE TYPE RELATORIO_AF_EMAIL_TYPE AS OBJECT (
    CDFILDEST           VARCHAR2(4),    --01
    NRSEQAPURAF         VARCHAR2(10),   --02
    NMFILIAL            VARCHAR2(30),   --03
    CDFILLANC           VARCHAR2(4),    --04
    NRAUTOFORN          VARCHAR2(10),   --05
    CDOPERADOR          VARCHAR2(12),   --06
    CDFORNECED          VARCHAR2(21),   --07
    VRTOTALAF           FLOAT(126),     --08
    DTAUTOFORN          DATE,           --09
    IDTIPOEMITAF        VARCHAR2(1),    --10
    NMEMPRESA           VARCHAR2(150),  --11
    SGESTADOEMPR        VARCHAR2(2),    --12
    NRCEPEMPR           VARCHAR2(15),   --13
    DSENDEEMPR          VARCHAR2(250),  --14
    NMMUNIEMPR          VARCHAR2(30),   --15
    IDTPIJUREMPR        VARCHAR2(1),    --16
    NRINSJUREMPR        VARCHAR2(20),   --17
    NMBAIREMPR          VARCHAR2(50),   --18
    NMFILIDEST          VARCHAR2(30),   --19
    NRINSJURFILI        VARCHAR2(20),   --20
    IDTPIJURFILI        VARCHAR2(1),    --21
    NMRAZSOCFILI        VARCHAR2(100),  --22
    DSPERIENTFIL        VARCHAR2(50),   --23
    OBSERVACAO          VARCHAR2(1),    --24
    CDINSCESTA          VARCHAR2(20),   --25
    NRINSCESTAEMPR      VARCHAR2(20),   --26
    CDFORMPGTO          VARCHAR2(2),    --27
    IDTPFORPGTAF        VARCHAR2(1),    --28
    NRDIAPGFIXAF        NUMBER(2),      --29
    NRPARCPGTOAF        NUMBER(2),      --30
    IDSITUATUAAF        VARCHAR2(1),    --31
    IDIMAUTOFORN        VARCHAR2(1),    --32
    IDTIPOFRETAF        VARCHAR2(1),    --33
    IDAUTOFORNEX        VARCHAR2(1),    --34
    IDTIPOLANCAF        VARCHAR2(1),    --35
    IDAFFORALIM         VARCHAR2(1),    --36
    IDBAIXAAF           VARCHAR2(1),    --37
    IDTPIJURFORN        VARCHAR2(1),    --38
    NRINSJURFORN        VARCHAR2(20),   --39
    NMRAZSOCFORN        VARCHAR2(100),  --40
    NMFANTFORN          VARCHAR2(250),  --41
    DSENDEFORN          VARCHAR2(60),   --42
    NMCONTFORN          VARCHAR2(30),   --43
    NRTELEFORN1         VARCHAR2(25),   --44
    NRTELEFORN          VARCHAR2(25),   --45
    DSEMAILCOT          VARCHAR2(100),  --46
    IDTPIJURCLIE        VARCHAR2(1),    --47
    NRINSJURCLIE        VARCHAR2(20),   --48
    NMRAZSOCCLIE        VARCHAR2(100),  --49
    DSENDECLIE          VARCHAR2(60),   --50
    NMBAIRCLIE          VARCHAR2(50),   --51
    SGESTADOCLIE        VARCHAR2(2),    --52
    NRCEPCLIE           VARCHAR2(15),   --53
    NRINSESTCLIE        VARCHAR2(20),   --54
    NMMUNICLIE          VARCHAR2(30),   --55
    CDCLIENTE           VARCHAR2(21),   --56
    NMOPERADOR          VARCHAR2(40),   --57
    NMMUNICIFIL         VARCHAR2(30),   --58
    DSENDEFILI          VARCHAR2(60),   --59
    NMBAIRFILI          VARCHAR2(50),   --60
    SGESTADOFIL         VARCHAR2(2),    --61
    NRCEPFILI           VARCHAR2(15),   --62
    NRTELEFILI          VARCHAR2(25),   --63
    DSEMAILFILI         VARCHAR2(40),   --64
    DTPROGENTAF         DATE,           --65
    CDPRODUTO           VARCHAR2(10),   --66
    QTPROGENTAF         FLOAT(126),     --67
    VRPREUNIAF          FLOAT(126),     --68
    VALTOTAL            FLOAT(126),     --69
    CDARVPROD           VARCHAR2(20),   --70
    NMPRODUTO           VARCHAR2(50),   --71
    CDMARCA             VARCHAR2(5),    --72
    NMMARCA             VARCHAR2(20),   --73
    NMMARCA_SEG         VARCHAR2(20),   --74
    NMSEGMARCA          VARCHAR2(50),   --75
    TXOBSITMAF          VARCHAR2(2000), --76
    DSPRAZOPGTAF        VARCHAR2(1100), --77
    NMFORMPGTO          VARCHAR2(30),   --78
    DSOBSFILIAL         VARCHAR2(2000), --79
    DSOBSUNIDADE        CLOB,           --80
    IDEXIBEOBSFIL       VARCHAR2(1),    --81
    NMREPRINSEST        VARCHAR2(150),  --82
    NRINSJURIEST        VARCHAR2(20),   --83
    IDTPIJURIEST        VARCHAR2(1),    --84
    DSENDEIEST          VARCHAR2(250),  --85
    NMBAIRIEST          VARCHAR2(50),   --86
    SGESTADOEST         VARCHAR2(2),    --87
    NRCEPIEST           VARCHAR2(15),   --88
    NMMUNICIPIOEST      VARCHAR2(30),   --89
    IDENDFATURAMENTOAF  VARCHAR2(2),    --90
    IDREPRESENTAFATAF   VARCHAR2(2),    --91
    IDENDCOBRANCAAF     VARCHAR2(2),    --92
    IDREPRESENTACOBRAAF VARCHAR2(2),    --93
    CDPRODUTOXML        VARCHAR2(2000), --94
    DSPRODUTO           VARCHAR2(4000), --95
    DSCOMPENDFIL        VARCHAR2(200),  --96
    IDUTDIAPGTAF        VARCHAR2(1),    --97
    DSMENSAUTF          VARCHAR2(2000)) --98
/

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(1100);
    V_DSOBSFILIAL   VARCHAR2(2000);
    V_DSOBSUNIDADE  CLOB;

    V_NMMUNICIFIL   MUNICIPIO.NMMUNICIPIO%TYPE;
    V_DSENDEFILI    VARCHAR2(60);
    V_NMBAIRFILI    VARCHAR2(50);
    V_SGESTADOFIL   ENDEFILI.SGESTADO%TYPE;
    V_NRCEPFILI     ENDEFILI.NRCEPFILI%TYPE;
    V_NRTELEFILI    ENDEFILI.NRTELEFILI%TYPE;
    V_DSEMAILFILI   ENDEFILI.DSEMAILFILI%TYPE;
    V_DSCOMPENDFIL  ENDEFILI.DSCOMPENDFIL%TYPE;

    V_NMREPRINSEST    VARCHAR2(150);
    V_NRINSJURIEST    INSCRESTAD.NRINSJURIEST%TYPE;
    V_IDTPIJURIEST    INSCRESTAD.IDTPIJURIEST%TYPE;
    V_DSENDEIEST      VARCHAR2(250);
    V_NMBAIRIEST      INSCRESTAD.NMBAIRIEST%TYPE;
    V_SGESTADOEST     INSCRESTAD.SGESTADO%TYPE;
    V_NRCEPIEST       INSCRESTAD.NRCEPIEST%TYPE;
    V_NMMUNICIPIOEST MUNICIPIO.NMMUNICIPIO%TYPE;
    V_CDINSCESTA     VARCHAR2(20);

    V_IDTPIJUREMPR   EMPRESA.IDTPIJUREMPR%TYPE;
    V_NRINSJUREMPR   EMPRESA.NRINSJUREMPR%TYPE;
    V_NMEMPRESA      VARCHAR2(150);
    V_DSENDEEMPR     VARCHAR2(250);
    V_NMBAIREMPR     EMPRESA.NMBAIREMPR%TYPE;
    V_SGESTADOEMPR   EMPRESA.SGESTADO%TYPE;
    V_NRCEPEMPR      EMPRESA.NRCEPEMPR%TYPE;
    V_NMMUNIEMPR     MUNICIPIO.NMMUNICIPIO%TYPE;
    V_NRINSCESTAEMPR VARCHAR2(20);

    V_IDTPIJURCLIE  CLIENTE.IDTPIJURCLIE%TYPE;
    V_NRINSJURCLIE  CLIENTE.NRINSJURCLIE%TYPE;
    V_NMRAZSOCCLIE  CLIENTE.NMRAZSOCCLIE%TYPE;
    V_DSENDECLIE    ENDECLIE.DSENDECLIE%TYPE; 
    V_NMBAIRCLIE    ENDECLIE.NMBAIRCLIE%TYPE;
    V_SGESTADOCLIE  ENDECLIE.SGESTADO%TYPE;
    V_NRCEPCLIE     ENDECLIE.NRCEPCLIE%TYPE;
    V_NMMUNICLIE    MUNICIPIO.NMMUNICIPIO%TYPE;
    V_NRINSESTCLIE  CLIENTE.NRINSESTCLIE%TYPE;

    V_IDENDFATURAMENTOAF       VARCHAR2(2);
    V_IDREPRESENTAFATAF        VARCHAR2(2);
    V_IDENDCOBRANCAAF          VARCHAR2(2);
    V_IDREPRESENTACOBRAAF      VARCHAR2(2);
    V_CDPRODUTOXML             VARCHAR2(2000);
    V_DSPRODUTO                VARCHAR2(4000);
    V_IDSISDOS                 VARCHAR2(1);
    V_DSMENSAUTF                 VARCHAR2(2000);
BEGIN
    SELECT NVL(MAX(IDSISDOS), '1')
      INTO V_IDSISDOS
      FROM PARAMGERAL;
    
    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) NRINSJURFILI,
                                      FILIAL.IDTPIJURFILI,
                                      FILIAL.NMRAZSOCFILI,
                                      FILIAL.CDINSCESTA,
                                      FILIAL.CDREGIAO,
                                      FILIAL.NMFILIAL NMFILIDEST,
                                      EMPRESA.NMEMPRESA,
                                      SUBSTR(REPLACE(EMPRESA.NRCEPEMPR, '-', ''),0,5)||'-'||SUBSTR(REPLACE(EMPRESA.NRCEPEMPR, '-', ''),6,3) NRCEPEMPR,
                                      EMPRESA.DSENDEEMPR,
                                      EMPRESA.IDTPIJUREMPR,
                                      EMPRESA.SGESTADO SGESTADOEMPR,
                                      FORMATMASK(EMPRESA.IDTPIJUREMPR, EMPRESA.NRINSJUREMPR) NRINSJUREMPR,
                                      EMPRESA.NMBAIREMPR,
                                      EMPRESA.NRINSCESTA NRINSCESTAEMPR,
                                      MUNICIPIO.NMMUNICIPIO NMMUNIEMPR,
                                      PARCMPSUP.DSPERIENTFIL, 
                                      NVL(P_IMPRIMEOBSPROD, 'N') OBSERVACAO,
                                      INSCRESTAD.NMREPRINSEST,
                                      INSCRESTAD.DSENDEIEST,
                                      FORMATMASK(INSCRESTAD.IDTPIJURIEST, INSCRESTAD.NRINSJURIEST) NRINSJURIEST, INSCRESTAD.IDTPIJURIEST,
                                      INSCRESTAD.NMBAIRIEST,
                                      SUBSTR(REPLACE(INSCRESTAD.NRCEPIEST, '-', ''),0,5)||'-'||SUBSTR(REPLACE(INSCRESTAD.NRCEPIEST, '-', ''),6,3) NRCEPIEST,
                                      INSCRESTAD.CDMUNICIPIO CDMUNICIPIOEST,
                                      MUNICIPIOEST.NMMUNICIPIO NMMUNICIPIOEST, 
                                      INSCRESTAD.SGESTADO SGESTADOEST, 
                                      AUTOFORNE.IDUTDIAPGTAF
                                 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
                                                  LEFT JOIN INSCRESTAD ON FILIAL.CDINSCESTA = INSCRESTAD.CDINSCESTA
                                                                      AND FILIAL.CDEMPRESA = INSCRESTAD.CDEMPRESA
                                                  LEFT JOIN MUNICIPIO MUNICIPIOEST ON INSCRESTAD.CDPAIS = MUNICIPIOEST.CDPAIS
                                                                                  AND INSCRESTAD.SGESTADO = MUNICIPIOEST.SGESTADO
                                                                                  AND INSCRESTAD.CDMUNICIPIO = MUNICIPIOEST.CDMUNICIPIO
                                WHERE NVL(AUTOFORNE.NRSEQAPURAF, 'N') = NVL(P_NRSEQAPURAF, NVL(AUTOFORNE.NRSEQAPURAF, 'N')) 
                                  AND INSTR(NVL(P_CDFILLANC, AUTOFORNE.CDFILLANC), AUTOFORNE.CDFILLANC) > 0
                                  AND INSTR(NVL(P_CDFILDEST, FILIAL.CDFILIAL), FILIAL.CDFILIAL) > 0
                                  AND ((INSTR(NVL(P_NRAUTOFORN, AUTOFORNE.NRAUTOFORN), AUTOFORNE.NRAUTOFORN) > 0) OR (P_NRAUTOFORN = 'T'))
                                  AND ((INSTR(NVL(P_NRSEQENVIOAFORN, NVL(AUTOFORNE.NRSEQENVIOAFORN, 'X')), NVL(AUTOFORNE.NRSEQENVIOAFORN, 'X')) > 0) OR (P_NRSEQENVIOAFORN = 'T'))
                                  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 NVL(AUTOFORNE.IDBAIXAAF, '1') IN ('1', '2', '3')
                                  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 VALTOTAL,
                                      PRODUTO.CDPRODUTO,
                                      PRODUTO.CDARVPROD,
                                      PRODUTO.NMPRODUTO,
                                      MARCA.CDMARCA,
                                      MARCA.NMMARCA,
                                      SEGMARCA.NMMARCA 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) NRINSJURFORN,
                                      F.NMRAZSOCFORN,
                                      F.NMFANTFORN,
                                      E.DSENDEFORN,
                                      E.NMCONTFORN,
                                      E.NRTELEFORN1,
                                      E.NRTELEFORN,
                                      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 SGESTADOCLIE,
                                      SUBSTR(REPLACE(ENDECLIE.NRCEPCLIE, '-', ''),0,5)||'-'||SUBSTR(REPLACE(ENDECLIE.NRCEPCLIE, '-', ''),6,3) NRCEPCLIE,
                                      CLIENTE.CDCLIENTE,
                                      CLIENTE.IDTPIJURCLIE,
                                      FORMATMASK(CLIENTE.IDTPIJURCLIE, CLIENTE.NRINSJURCLIE) NRINSJURCLIE,
                                      CLIENTE.NMRAZSOCCLIE,
                                      CLIENTE.NRINSESTCLIE,
                                      MUNICIPIO.NMMUNICIPIO 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(REPLACE(ENDEFILI.NRCEPFILI, '-', ''),0,5)||'-'||SUBSTR(REPLACE(ENDEFILI.NRCEPFILI, '-', ''),6,3) NRCEPFILI,
                                      ENDEFILI.SGESTADO SGESTADOFIL,
                                      ENDEFILI.NMBAIRFILI,
                                      DSENDEFILI,
                                      NMMUNICIPIO NMMUNICIFIL,
                                      NRTELEFILI,
                                      DSEMAILFILI,
                                      ENDEFILI.DSCOMPENDFIL
                                 FROM ENDEFILI INNER JOIN MUNICIPIO ON ENDEFILI.CDPAIS = MUNICIPIO.CDPAIS
                                                                   AND ENDEFILI.SGESTADO = MUNICIPIO.SGESTADO
                                                                   AND ENDEFILI.CDMUNICIPIO = MUNICIPIO.CDMUNICIPIO
                                WHERE ENDEFILI.IDTPENDEFILI = 'P'),
                        EE AS (SELECT ENDEFILI.CDFILIAL,
                                      SUBSTR(REPLACE(ENDEFILI.NRCEPFILI, '-', ''),0,5)||'-'||SUBSTR(REPLACE(ENDEFILI.NRCEPFILI, '-', ''),6,3) NRCEPFILIEE,
                                      ENDEFILI.SGESTADO SGESTADOFILEE,
                                      ENDEFILI.NMBAIRFILI NMBAIRFILIEE,
                                      DSENDEFILI DSENDEFILIEE,
                                      NMMUNICIPIO NMMUNICIFILEE,
                                      NRTELEFILI NRTELEFILIEE,
                                      DSEMAILFILI DSEMAILFILIEE,
                                      ENDEFILI.DSCOMPENDFIL DSCOMPENDFILEE
                                 FROM ENDEFILI INNER JOIN MUNICIPIO ON ENDEFILI.CDPAIS = MUNICIPIO.CDPAIS
                                                                   AND ENDEFILI.SGESTADO = MUNICIPIO.SGESTADO
                                                                   AND ENDEFILI.CDMUNICIPIO = MUNICIPIO.CDMUNICIPIO
                                WHERE ENDEFILI.IDTPENDEFILI = 'T'),
                        CC AS (SELECT ENDEFILI.CDFILIAL,
                                      SUBSTR(REPLACE(ENDEFILI.NRCEPFILI, '-', ''),0,5)||'-'||SUBSTR(REPLACE(ENDEFILI.NRCEPFILI, '-', ''),6,3) NRCEPFILICC,
                                      ENDEFILI.SGESTADO SGESTADOFILCC,
                                      ENDEFILI.NMBAIRFILI NMBAIRFILICC,
                                      DSENDEFILI DSENDEFILICC,
                                      NMMUNICIPIO NMMUNICIFILCC
                                 FROM ENDEFILI INNER JOIN MUNICIPIO ON ENDEFILI.CDPAIS = MUNICIPIO.CDPAIS
                                                                   AND ENDEFILI.SGESTADO = MUNICIPIO.SGESTADO
                                                                   AND ENDEFILI.CDMUNICIPIO = MUNICIPIO.CDMUNICIPIO
                                WHERE ENDEFILI.IDTPENDEFILI = 'C'),
                        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.NRTELEFORN,
                               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,
                               AF.NMREPRINSEST,
                               AF.DSENDEIEST,
                               AF.NRINSJURIEST,
                               AF.IDTPIJURIEST,
                               AF.NMBAIRIEST,
                               AF.NRCEPIEST,
                               AF.CDMUNICIPIOEST, 
                               AF.NMMUNICIPIOEST,
                               AF.SGESTADOEST,
                               EE.NRCEPFILIEE,
                               EE.SGESTADOFILEE,
                               EE.NMBAIRFILIEE,
                               EE.DSENDEFILIEE,
                               EE.NMMUNICIFILEE,
                               CC.NRCEPFILICC,
                               CC.SGESTADOFILCC,
                               CC.NMBAIRFILICC,
                               CC.DSENDEFILICC,
                               CC.NMMUNICIFILCC,
                               EF.NRTELEFILI,
                               EF.DSEMAILFILI,
                               EE.NRTELEFILIEE,
                               EE.DSEMAILFILIEE,
                               EF.DSCOMPENDFIL,
                               EE.DSCOMPENDFILEE,
                               AF.IDUTDIAPGTAF
                          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 EE ON AF.CDFILDEST  = EE.CDFILIAL
                                   LEFT JOIN CC ON AF.CDFILDEST  = CC.CDFILIAL
                                   LEFT JOIN FP ON AF.CDFORMPGTO = FP.CDFORMPGTO
                         ORDER BY AF.CDFILDEST, AF.NRAUTOFORN)
    LOOP
        V_PRAZOPGTAF := '';
        
        FOR C_PRAZOPGTAF IN (SELECT CONCAT(CONCAT(CONCAT(' Nº:', NRDIASPGTOAF), CONCAT(' P:', VRPERCPGTOAF)), '%   /   ') FORMPGTOF
                               FROM PRAZOPGTAF
                              WHERE CDFILDEST  = C_ITEM.CDFILDEST
                                AND CDFILLANC  = C_ITEM.CDFILLANC
                                AND NRAUTOFORN = C_ITEM.NRAUTOFORN
                              ORDER BY NRDIASPGTOAF)
        LOOP
            IF LENGTH(NVL(V_PRAZOPGTAF, '1')||C_PRAZOPGTAF.FORMPGTOF) < 1100 THEN
                V_PRAZOPGTAF := V_PRAZOPGTAF||C_PRAZOPGTAF.FORMPGTOF;
            ELSE
                EXIT;
            END IF;
        END LOOP;
        
        IF LENGTH(V_PRAZOPGTAF) > 0 THEN
            V_PRAZOPGTAF := SUBSTR(V_PRAZOPGTAF, 1, LENGTH(V_PRAZOPGTAF)-4);
        END IF;
        IF P_IDEXIBEOBSFIL = 'S' THEN
           BEGIN
               SELECT DSOBSFILIAL, DSOBSUNIDADE
                 INTO V_DSOBSFILIAL, V_DSOBSUNIDADE
                 FROM PARCMPSUP
                WHERE CDFILIAL = C_ITEM.CDFILDEST
                  AND P_IDEXIBEOBSFIL = 'S';
           EXCEPTION
               WHEN NO_DATA_FOUND THEN BEGIN
                   V_DSOBSFILIAL  := NULL;
                   V_DSOBSUNIDADE := NULL;
               END;
           END;

           BEGIN
               SELECT SUBSTR(LISTAGG(NRMENSAUTFOR||'|'||DSMENSAF,' - ') WITHIN GROUP (ORDER BY NRMENSAUTFOR), 1, 2000)
                 INTO V_DSMENSAUTF
                 FROM MENSAUTF
                WHERE CDFILDEST  = C_ITEM.CDFILDEST
                  AND CDFILLANC  = C_ITEM.CDFILLANC
                  AND NRAUTOFORN = C_ITEM.NRAUTOFORN;
           EXCEPTION
               WHEN NO_DATA_FOUND THEN
                   V_DSMENSAUTF := ' ';
           END;
        END IF;

        BEGIN
            SELECT LISTAGG(CDPRODUTOXML || ' / ') WITHIN GROUP (ORDER BY CDPRODUTOXML) AS PRODFORNE
              INTO V_CDPRODUTOXML
              FROM PRODFORNXML
             WHERE CDPRODUTO  = C_ITEM.CDPRODUTO
               AND CDFORNECED = C_ITEM.CDFORNECED;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                V_CDPRODUTOXML := '';
        END;
  
        BEGIN
            SELECT DSPRODUTO
              INTO V_DSPRODUTO
              FROM PRODFORNCOMP
             WHERE CDPRODCOMPL  = C_ITEM.CDPRODUTO
               AND CDFORNECED = C_ITEM.CDFORNECED;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                V_DSPRODUTO := '';
        END;

        V_NMMUNICIFIL   := '';
        V_DSENDEFILI    := '';
        V_NMBAIRFILI    := '';
        V_SGESTADOFIL   := '';
        V_NRCEPFILI     := '';
        V_DSCOMPENDFIL  := '';

        V_IDTPIJUREMPR   := '';
        V_NRINSJUREMPR   := '';
        V_NMEMPRESA      := '';
        V_DSENDEEMPR     := '';
        V_NMBAIREMPR     := '';
        V_SGESTADOEMPR   := '';
        V_NRCEPEMPR      := '';
        V_NMMUNIEMPR     := '';
        V_NRINSCESTAEMPR := '';

        V_IDTPIJURIEST   := '';
        V_NRINSJURIEST   := '';
        V_NMREPRINSEST   := '';
        V_DSENDEIEST     := '';
        V_NMBAIRIEST     := '';
        V_SGESTADOEST    := '';
        V_NRCEPIEST      := '';
        V_NMMUNICIPIOEST := '';
        V_CDINSCESTA     := '';

        V_IDENDFATURAMENTOAF  := '';
        V_IDREPRESENTAFATAF   := '';
        V_IDENDCOBRANCAAF     := '';
        V_IDREPRESENTACOBRAAF := '';

        IF LENGTH(C_ITEM.NMMUNICIFILEE||C_ITEM.DSENDEFILIEE||C_ITEM.NMBAIRFILIEE||C_ITEM.SGESTADOFILEE||C_ITEM.NRCEPFILIEE) = 0 THEN
            V_NMMUNICIFIL   := C_ITEM.NMMUNICIFIL;
            V_DSENDEFILI    := C_ITEM.DSENDEFILI;
            V_NMBAIRFILI    := C_ITEM.NMBAIRFILI;
            V_SGESTADOFIL   := C_ITEM.SGESTADOFIL;
            V_NRCEPFILI     := C_ITEM.NRCEPFILI;
            V_NRTELEFILI    := C_ITEM.NRTELEFILI;
            V_DSEMAILFILI   := C_ITEM.DSEMAILFILI;
            V_DSCOMPENDFIL  := C_ITEM.DSCOMPENDFIL;
        ELSE
            V_NMMUNICIFIL   := C_ITEM.NMMUNICIFILEE;
            V_DSENDEFILI    := C_ITEM.DSENDEFILIEE;
            V_NMBAIRFILI    := C_ITEM.NMBAIRFILIEE;
            V_SGESTADOFIL   := C_ITEM.SGESTADOFILEE;
            V_NRCEPFILI     := C_ITEM.NRCEPFILIEE;
            V_NRTELEFILI    := C_ITEM.NRTELEFILIEE;
            V_DSEMAILFILI   := C_ITEM.DSEMAILFILIEE;
            V_DSCOMPENDFIL  := C_ITEM.DSCOMPENDFILEE;
        END IF;

        IF C_ITEM.IDTIPOEMITAF = '3' THEN
            V_NMREPRINSEST   := C_ITEM.NMRAZSOCCLIE;
            V_NRINSJURIEST   := C_ITEM.NRINSJURCLIE;
            V_IDTPIJURIEST   := C_ITEM.IDTPIJURCLIE;
            V_DSENDEIEST     := C_ITEM.DSENDECLIE;
            V_NMBAIRIEST     := C_ITEM.NMBAIRCLIE;
            V_SGESTADOEST    := C_ITEM.SGESTADOCLIE;
            V_NRCEPIEST      := C_ITEM.NRCEPCLIE;
            V_NMMUNICIPIOEST := C_ITEM.NMMUNICLIE;
            V_CDINSCESTA     := C_ITEM.NRINSESTCLIE;

            V_IDTPIJUREMPR   := C_ITEM.IDTPIJURCLIE;
            V_NRINSJUREMPR   := C_ITEM.NRINSJURCLIE;
            V_NMEMPRESA      := C_ITEM.NMRAZSOCCLIE;
            V_DSENDEEMPR     := C_ITEM.DSENDECLIE;
            V_NMBAIREMPR     := C_ITEM.NMBAIRCLIE;
            V_SGESTADOEMPR   := C_ITEM.SGESTADOCLIE;
            V_NRCEPEMPR      := C_ITEM.NRCEPCLIE;
            V_NMMUNIEMPR     := C_ITEM.NMMUNICLIE;
            V_NRINSCESTAEMPR := C_ITEM.NRINSESTCLIE;
        ELSE
            V_IDTPIJURIEST   := C_ITEM.IDTPIJURIEST;
            V_NRINSJURIEST   := C_ITEM.NRINSJURIEST;
            V_NMREPRINSEST   := C_ITEM.NMREPRINSEST;
            V_DSENDEIEST     := C_ITEM.DSENDEIEST;
            V_NMBAIRIEST     := C_ITEM.NMBAIRIEST;
            V_SGESTADOEST    := C_ITEM.SGESTADOEST;
            V_NRCEPIEST      := C_ITEM.NRCEPIEST;
            V_NMMUNICIPIOEST := C_ITEM.NMMUNICIPIOEST;
            V_CDINSCESTA     := C_ITEM.CDINSCESTA;

            V_IDTPIJUREMPR   := C_ITEM.IDTPIJUREMPR;
            V_NRINSJUREMPR   := C_ITEM.NRINSJUREMPR;
            V_NMEMPRESA      := C_ITEM.NMEMPRESA;
            V_DSENDEEMPR     := C_ITEM.DSENDEEMPR;
            V_NMBAIREMPR     := C_ITEM.NMBAIREMPR;
            V_SGESTADOEMPR   := C_ITEM.SGESTADOEMPR;
            V_NRCEPEMPR      := C_ITEM.NRCEPEMPR;
            V_NMMUNIEMPR     := C_ITEM.NMMUNIEMPR;
            V_NRINSCESTAEMPR := C_ITEM.NRINSCESTAEMPR;
        END IF;

        FOR C_MODELO IN (SELECT NRENDERAUTOFORNE,
                                CDFILIAL,
                                IDENDENTREGAAF,
                                IDENDFATURAMENTOAF,
                                IDREPRESENTAFATAF,
                                IDENDCOBRANCAAF,
                                IDREPRESENTACOBRAAF
                           FROM ENDERAUTOFORNE) LOOP
            IF C_MODELO.IDENDENTREGAAF = '01' THEN
                V_NMMUNICIFIL   := C_ITEM.NMMUNICIFILEE;
                V_DSENDEFILI    := C_ITEM.DSENDEFILIEE;
                V_NMBAIRFILI    := C_ITEM.NMBAIRFILIEE;
                V_SGESTADOFIL   := C_ITEM.SGESTADOFILEE;
                V_NRCEPFILI     := C_ITEM.NRCEPFILIEE;
                V_NRTELEFILI    := C_ITEM.NRTELEFILIEE;   
                V_DSEMAILFILI   := C_ITEM.DSEMAILFILIEE;
                V_DSCOMPENDFIL  := C_ITEM.DSCOMPENDFILEE;
            ELSIF C_MODELO.IDENDENTREGAAF = '02' THEN
                V_NMMUNICIFIL   := C_ITEM.NMMUNICIFIL;
                V_DSENDEFILI    := C_ITEM.DSENDEFILI;
                V_NMBAIRFILI    := C_ITEM.NMBAIRFILI;
                V_SGESTADOFIL   := C_ITEM.SGESTADOFIL;
                V_NRCEPFILI     := C_ITEM.NRCEPFILI;
                V_NRTELEFILI    := C_ITEM.NRTELEFILI;   
                V_DSEMAILFILI   := C_ITEM.DSEMAILFILI;
                V_DSCOMPENDFIL  := C_ITEM.DSCOMPENDFIL;
            END IF;

            IF C_MODELO.IDENDFATURAMENTOAF = 'NN' THEN
                V_IDTPIJURIEST   := '';
                V_NRINSJURIEST   := '';
                V_DSENDEIEST     := '';
                V_NMBAIRIEST     := '';
                V_SGESTADOEST    := '';
                V_NRCEPIEST      := '';
                V_NMMUNICIPIOEST := '';
                V_CDINSCESTA     := '';
            ELSIF C_MODELO.IDENDFATURAMENTOAF = 'EP' THEN
                V_IDTPIJURIEST   := C_ITEM.IDTPIJURIEST;
                V_NRINSJURIEST   := C_ITEM.NRINSJURIEST;
                V_DSENDEIEST     := C_ITEM.DSENDEIEST;
                V_NMBAIRIEST     := C_ITEM.NMBAIRIEST;
                V_SGESTADOEST    := C_ITEM.SGESTADOEST;
                V_NRCEPIEST      := C_ITEM.NRCEPIEST;
                V_NMMUNICIPIOEST := C_ITEM.NMMUNICIPIOEST;
                V_CDINSCESTA     := C_ITEM.CDINSCESTA;
            ELSIF C_MODELO.IDENDFATURAMENTOAF = 'EE' THEN
                V_IDTPIJURIEST   := C_ITEM.IDTPIJUREMPR;
                V_NRINSJURIEST   := C_ITEM.NRINSJUREMPR;
                V_DSENDEIEST     := C_ITEM.DSENDEEMPR;
                V_NMBAIRIEST     := C_ITEM.NMBAIREMPR;
                V_SGESTADOEST    := C_ITEM.SGESTADOEMPR;
                V_NRCEPIEST      := C_ITEM.NRCEPEMPR;
                V_NMMUNICIPIOEST := C_ITEM.NMMUNIEMPR;
                V_CDINSCESTA     := C_ITEM.NRINSCESTAEMPR;
            ELSIF C_MODELO.IDENDFATURAMENTOAF = 'CL' THEN
                V_IDTPIJURIEST   := C_ITEM.IDTPIJURCLIE;
                V_NRINSJURIEST   := C_ITEM.NRINSJURCLIE;
                V_DSENDEIEST     := C_ITEM.DSENDECLIE;
                V_NMBAIRIEST     := C_ITEM.NMBAIRCLIE;
                V_SGESTADOEST    := C_ITEM.SGESTADOCLIE;
                V_NRCEPIEST      := C_ITEM.NRCEPCLIE;
                V_NMMUNICIPIOEST := C_ITEM.NMMUNICLIE;
                V_CDINSCESTA     := C_ITEM.NRINSESTCLIE;
            ELSIF C_MODELO.IDENDFATURAMENTOAF = 'IE' THEN
                V_IDTPIJURIEST   := C_ITEM.IDTPIJURIEST;
                V_NRINSJURIEST   := C_ITEM.NRINSJURIEST;
                V_DSENDEIEST     := C_ITEM.DSENDEIEST;
                V_NMBAIRIEST     := C_ITEM.NMBAIRIEST;
                V_SGESTADOEST    := C_ITEM.SGESTADOEST;
                V_NRCEPIEST      := C_ITEM.NRCEPIEST;
                V_NMMUNICIPIOEST := C_ITEM.NMMUNICIPIOEST;
                V_CDINSCESTA     := C_ITEM.CDINSCESTA;
            ELSIF C_MODELO.IDENDFATURAMENTOAF = 'FC' THEN
                IF V_IDSISDOS <> '3' THEN
                    V_IDTPIJURIEST   := V_IDTPIJUREMPR;
                    V_NRINSJURIEST   := V_NRINSJUREMPR;
                    V_DSENDEIEST     := V_DSENDEEMPR;
                    V_NMBAIRIEST     := V_NMBAIREMPR;
                    V_SGESTADOEST    := V_SGESTADOEMPR;
                    V_NRCEPIEST      := V_NRCEPEMPR;
                    V_NMMUNICIPIOEST := V_NMMUNIEMPR;
                    V_CDINSCESTA     := V_NRINSCESTAEMPR;
                ELSE
                    IF C_ITEM.IDTIPOEMITAF = '3' THEN
                        V_IDTPIJURIEST   := V_IDTPIJUREMPR;
                        V_NRINSJURIEST   := V_NRINSJUREMPR;
                        V_DSENDEIEST     := V_DSENDEEMPR;
                        V_NMBAIRIEST     := V_NMBAIREMPR;
                        V_SGESTADOEST    := V_SGESTADOEMPR;
                        V_NRCEPIEST      := V_NRCEPEMPR;
                        V_NMMUNICIPIOEST := V_NMMUNIEMPR;
                        V_CDINSCESTA     := V_NRINSCESTAEMPR;
                    END IF;
                END IF;
            END IF;

            IF C_MODELO.IDENDCOBRANCAAF = 'NN' THEN
                V_IDTPIJUREMPR   := '';
                V_NRINSJUREMPR   := '';
                V_DSENDEEMPR     := '';
                V_NMBAIREMPR     := '';
                V_SGESTADOEMPR   := '';
                V_NRCEPEMPR      := '';
                V_NMMUNIEMPR     := '';
                V_NRINSCESTAEMPR := '';
            ELSIF C_MODELO.IDENDCOBRANCAAF = 'EP' THEN
                V_IDTPIJUREMPR   := C_ITEM.IDTPIJURFILI;
                V_NRINSJUREMPR   := C_ITEM.NRINSJURFILI;
                V_NMEMPRESA      := C_ITEM.NMFILIAL;
                V_DSENDEEMPR     := C_ITEM.DSENDEFILI;
                V_NMBAIREMPR     := C_ITEM.NMBAIRFILI;
                V_SGESTADOEMPR   := C_ITEM.SGESTADOFIL;
                V_NRCEPEMPR      := C_ITEM.NRCEPFILI;
                V_NMMUNIEMPR     := C_ITEM.NMMUNICIFIL;
                V_NRINSCESTAEMPR := C_ITEM.CDINSCESTA;
            ELSIF C_MODELO.IDENDCOBRANCAAF = 'EC' THEN
                V_IDTPIJUREMPR   := C_ITEM.IDTPIJURFILI;
                V_NRINSJUREMPR   := C_ITEM.NRINSJURFILI;
                V_NMEMPRESA      := C_ITEM.NMFILIAL;
                V_DSENDEEMPR     := C_ITEM.DSENDEFILICC;
                V_NMBAIREMPR     := C_ITEM.NMBAIRFILICC;
                V_SGESTADOEMPR   := C_ITEM.SGESTADOFILCC;
                V_NRCEPEMPR      := C_ITEM.NRCEPFILICC;
                V_NMMUNIEMPR     := C_ITEM.NMMUNICIFILCC;
                V_NRINSCESTAEMPR := C_ITEM.CDINSCESTA;
            ELSIF C_MODELO.IDENDCOBRANCAAF = 'EE' THEN
                V_IDTPIJUREMPR   := C_ITEM.IDTPIJUREMPR;
                V_NRINSJUREMPR   := C_ITEM.NRINSJUREMPR;
                V_NMEMPRESA      := C_ITEM.NMEMPRESA;
                V_DSENDEEMPR     := C_ITEM.DSENDEEMPR;
                V_NMBAIREMPR     := C_ITEM.NMBAIREMPR;
                V_SGESTADOEMPR   := C_ITEM.SGESTADOEMPR;
                V_NRCEPEMPR      := C_ITEM.NRCEPEMPR;
                V_NMMUNIEMPR     := C_ITEM.NMMUNIEMPR;
                V_NRINSCESTAEMPR := C_ITEM.NRINSCESTAEMPR;
            ELSIF C_MODELO.IDENDCOBRANCAAF = 'CL' THEN
                V_IDTPIJUREMPR   := C_ITEM.IDTPIJURCLIE;
                V_NRINSJUREMPR   := C_ITEM.NRINSJURCLIE;
                V_NMEMPRESA      := C_ITEM.NMRAZSOCCLIE;
                V_DSENDEEMPR     := C_ITEM.DSENDECLIE;
                V_NMBAIREMPR     := C_ITEM.NMBAIRCLIE;
                V_SGESTADOEMPR   := C_ITEM.SGESTADOCLIE;
                V_NRCEPEMPR      := C_ITEM.NRCEPCLIE;
                V_NMMUNIEMPR     := C_ITEM.NMMUNICLIE;
                V_NRINSCESTAEMPR := C_ITEM.NRINSESTCLIE;
            ELSIF C_MODELO.IDENDCOBRANCAAF = 'IE' THEN
                V_NMEMPRESA      := C_ITEM.NMREPRINSEST;
                V_IDTPIJUREMPR   := C_ITEM.IDTPIJURIEST;
                V_NRINSJUREMPR   := C_ITEM.NRINSJURIEST;
                V_DSENDEEMPR     := C_ITEM.DSENDEIEST;
                V_NMBAIREMPR     := C_ITEM.NMBAIRIEST;
                V_SGESTADOEMPR   := C_ITEM.SGESTADOEST;
                V_NRCEPEMPR      := C_ITEM.NRCEPIEST;
                V_NMMUNIEMPR     := C_ITEM.NMMUNICIPIOEST;
                V_NRINSCESTAEMPR := C_ITEM.CDINSCESTA;
            END IF;

            IF C_MODELO.IDREPRESENTAFATAF = 'NN' THEN
                V_NMREPRINSEST   := '';
            ELSIF C_MODELO.IDREPRESENTAFATAF = 'NF' THEN
                V_NMREPRINSEST   := C_ITEM.NMFILIAL;
            ELSIF C_MODELO.IDREPRESENTAFATAF = 'RF' THEN
                V_NMREPRINSEST   := C_ITEM.NMRAZSOCFILI;
            ELSIF C_MODELO.IDREPRESENTAFATAF = 'EP' THEN
                V_NMREPRINSEST   := C_ITEM.NMREPRINSEST;
            ELSIF C_MODELO.IDREPRESENTAFATAF = 'CL' THEN
                V_NMREPRINSEST   := C_ITEM.NMRAZSOCCLIE;
            ELSIF C_MODELO.IDREPRESENTAFATAF = 'EE' THEN
                V_NMREPRINSEST   := C_ITEM.NMEMPRESA;
            ELSIF C_MODELO.IDREPRESENTAFATAF = 'FC' THEN
                IF V_IDSISDOS <> '3' THEN
                    V_NMREPRINSEST   := V_NMEMPRESA;
                ELSE
                    IF C_ITEM.IDTIPOEMITAF = '3' THEN
                        V_NMREPRINSEST   := V_NMEMPRESA;
                    END IF;
                END IF;
            END IF;

            IF C_MODELO.IDREPRESENTACOBRAAF  = 'NN' THEN
                V_NMEMPRESA   := '';
            ELSIF C_MODELO.IDREPRESENTACOBRAAF  = 'NF' THEN
                V_NMEMPRESA   := C_ITEM.NMFILIAL;
            ELSIF C_MODELO.IDREPRESENTACOBRAAF  = 'RF' THEN
                V_NMEMPRESA   := C_ITEM.NMRAZSOCFILI;
            ELSIF C_MODELO.IDREPRESENTACOBRAAF  = 'EP' THEN
                V_NMEMPRESA   := C_ITEM.NMREPRINSEST;
            ELSIF C_MODELO.IDREPRESENTACOBRAAF  = 'CL' THEN
                V_NMEMPRESA   := C_ITEM.NMRAZSOCCLIE;
            ELSIF C_MODELO.IDREPRESENTACOBRAAF  = 'EE' THEN
                V_NMEMPRESA   := C_ITEM.NMEMPRESA;
            END IF;

            V_IDENDFATURAMENTOAF  := C_MODELO.IDENDFATURAMENTOAF;
            V_IDREPRESENTAFATAF   := C_MODELO.IDREPRESENTAFATAF;
            V_IDENDCOBRANCAAF     := C_MODELO.IDENDCOBRANCAAF;
            V_IDREPRESENTACOBRAAF := C_MODELO.IDREPRESENTACOBRAAF;

        END LOOP;
        
        L_RELATORIO_AF_EMAIL_TYPE := RELATORIO_AF_EMAIL_TYPE (
            C_ITEM.CDFILDEST,                               --01
            C_ITEM.NRSEQAPURAF,                             --02
            C_ITEM.NMFILIAL,                                --03
            C_ITEM.CDFILLANC,                               --04
            C_ITEM.NRAUTOFORN,                              --05
            C_ITEM.CDOPERADOR,                              --06
            C_ITEM.CDFORNECED,                              --07
            C_ITEM.VRTOTALAF,                               --08
            C_ITEM.DTAUTOFORN,                              --09
            C_ITEM.IDTIPOEMITAF,                            --10
            V_NMEMPRESA,                                    --11
            V_SGESTADOEMPR,                                 --12
            V_NRCEPEMPR,                                    --13
            V_DSENDEEMPR,                                   --14
            V_NMMUNIEMPR,                                   --15
            V_IDTPIJUREMPR,                                 --16
            V_NRINSJUREMPR,                                 --17
            V_NMBAIREMPR,                                   --18
            C_ITEM.NMFILIDEST,                              --19
            C_ITEM.NRINSJURFILI,                            --20
            C_ITEM.IDTPIJURFILI,                            --21
            C_ITEM.NMRAZSOCFILI,                            --22
            C_ITEM.DSPERIENTFIL,                            --23
            C_ITEM.OBSERVACAO,                              --24
            V_CDINSCESTA,                                   --25
            V_NRINSCESTAEMPR,                               --26
            C_ITEM.CDFORMPGTO,                              --27
            C_ITEM.IDTPFORPGTAF,                            --28
            C_ITEM.NRDIAPGFIXAF,                            --29
            C_ITEM.NRPARCPGTOAF,                            --30
            C_ITEM.IDSITUATUAAF,                            --31
            C_ITEM.IDIMAUTOFORN,                            --32
            C_ITEM.IDTIPOFRETAF,                            --33
            C_ITEM.IDAUTOFORNEX,                            --34
            C_ITEM.IDTIPOLANCAF,                            --35
            C_ITEM.IDAFFORALIM,                             --36
            C_ITEM.IDBAIXAAF,                               --37
            C_ITEM.IDTPIJURFORN,                            --38
            C_ITEM.NRINSJURFORN,                            --39
            C_ITEM.NMRAZSOCFORN,                            --40
            C_ITEM.NMFANTFORN,                              --41
            C_ITEM.DSENDEFORN,                              --42
            C_ITEM.NMCONTFORN,                              --43
            C_ITEM.NRTELEFORN1,                             --44
            C_ITEM.NRTELEFORN,                              --45
            C_ITEM.DSEMAILCOT,                              --46
            V_IDTPIJURCLIE,                                 --47
            V_NRINSJURCLIE,                                 --48
            V_NMRAZSOCCLIE,                                 --49
            V_DSENDECLIE,                                   --50
            V_NMBAIRCLIE,                                   --51
            V_SGESTADOCLIE,                                 --52
            V_NRCEPCLIE,                                    --53
            V_NRINSESTCLIE,                                 --54
            V_NMMUNICLIE,                                   --55
            C_ITEM.CDCLIENTE,                               --56
            C_ITEM.NMOPERADOR,                              --57
            V_NMMUNICIFIL,                                  --58
            V_DSENDEFILI,                                   --59
            V_NMBAIRFILI,                                   --60
            V_SGESTADOFIL,                                  --61
            V_NRCEPFILI,                                    --62
            V_NRTELEFILI,                                   --63
            V_DSEMAILFILI,                                  --64
            C_ITEM.DTPROGENTAF,                             --65
            C_ITEM.CDPRODUTO,                               --66
            C_ITEM.QTPROGENTAF,                             --67
            C_ITEM.VRPREUNIAF,                              --68
            C_ITEM.VALTOTAL,                                --69
            C_ITEM.CDARVPROD,                               --70
            C_ITEM.NMPRODUTO,                               --71
            C_ITEM.CDMARCA,                                 --72
            C_ITEM.NMMARCA,                                 --73
            C_ITEM.NMMARCA_SEG,                             --74
            C_ITEM.NMSEGMARCA,                              --75
            C_ITEM.TXOBSITMAF,                              --76
            V_PRAZOPGTAF,                                   --77
            C_ITEM.NMFORMPGTO,                              --78
            SUBSTR((V_DSOBSFILIAL||V_DSMENSAUTF), 1, 2000), --79
            V_DSOBSUNIDADE,                                 --80
            P_IDEXIBEOBSFIL,                                --81
            V_NMREPRINSEST,                                 --82
            V_NRINSJURIEST,                                 --83
            V_IDTPIJURIEST,                                 --84
            V_DSENDEIEST,                                   --85
            V_NMBAIRIEST,                                   --86
            V_SGESTADOEST,                                  --87
            V_NRCEPIEST,                                    --88
            V_NMMUNICIPIOEST,                               --89
            V_IDENDFATURAMENTOAF,                           --90
            V_IDREPRESENTAFATAF,                            --91
            V_IDENDCOBRANCAAF,                              --92
            V_IDREPRESENTACOBRAAF,                          --93
            V_CDPRODUTOXML,                                 --94
            V_DSPRODUTO,                                    --95
            V_DSCOMPENDFIL,                                 --96
            C_ITEM.IDUTDIAPGTAF,                            --97
            V_DSMENSAUTF);                                  --98
        PIPE ROW (L_RELATORIO_AF_EMAIL_TYPE);
    END LOOP;
    RETURN;
END RELATORIO_AF_EMAIL;
/