DECLARE
   V_SEQUENCIAL INTEGER;
   V_CDFILIANTE VARCHAR2(4);
   V_NRLANCANTE VARCHAR2(10);
BEGIN
   DECLARE V_FILEHANDLE  UTL_FILE.FILE_TYPE;
BEGIN
   V_CDFILIANTE := ' ';
   V_NRLANCANTE := ' ';

   FOR CUR_FIL IN (
   SELECT F.CDFILIAL, LPAD(TO_CHAR(TO_NUMBER(F.CDFILIAL)),2,'0') AS CDEXPORTEMPR
   FROM FILIAL F, PARAMEMPRESA P
   WHERE F.CDEMPRESA = P.CDEMPRESA
   ORDER BY F.CDFILIAL) LOOP

      V_FILEHANDLE := UTL_FILE.FOPEN('EXPORA_DIR', 'ExpNFECN'||NVL(CUR_FIL.CDEXPORTEMPR,CUR_FIL.CDFILIAL)||'.txt', 'w');

      FOR CUR_SEQU IN (
      SELECT
         A.CDFILIAL, A.NRLANCTONF,
         RPAD(SUBSTR(NVL(CDSERIEENTR, ' '),2,4),4)||
         LPAD(substr(to_char(NRNOTAFISC),1,8),8, '0')||' '||
         TO_CHAR(DTENTRSAID, 'DD-MON-YY')||' '||
         DECODE(NUMEVENC, 1, '  ',('-'||SUBSTR(NRSEQCOUNT,2,1)))||
         RPAD(NVL(B.TIPO, ' '),5)||
         RPAD(NVL(B.CDIMPOSTO, ' '),2)||
         RPAD(NVL(TO_CHAR(B.VRALIQITEM),'0'),4)||
         RPAD(DECODE(B.CDIMPOSTO||B.VRALIQITEM,            '0010.5', 'IVABSUSO105',                  --codigoImpuesto
              DECODE(B.CDIMPOSTO||B.VRALIQITEM||S.SERVICO, '0121',   'IVACOMBIE21',
              DECODE(B.CDIMPOSTO||B.VRALIQITEM||S.SERVICO, '0121S',  'IVACOSERV21',
              DECODE(B.CDIMPOSTO||B.VRALIQITEM,            '000',    'EXENTODEIVA',
              DECODE(B.CDIMPOSTO||B.VRALIQITEM,            '010',    'EXENTODEIVA',
              DECODE(B.CDIMPOSTO,                          '02',     'EXENTODEIVA',
              DECODE(B.CDIMPOSTO,                          '03',     'EXENTODEIVA',
              DECODE(B.CDIMPOSTO,                          '04',     'EXENTODEIVA',
              DECODE(B.CDIMPOSTO,                          '15',     'EXENTODEIVA',
              DECODE(B.CDIMPOSTO,                          '20',     'EXENTODEIVA',
              DECODE(B.CDIMPOSTO||B.VRALIQITEM,            '0110.5', 'IVAPRIMA105',
              DECODE(B.CDIMPOSTO||B.VRALIQITEM||S.SERVICO, '0127S',  'IVASERPUB27',
              DECODE(B.CDIMPOSTO||B.VRALIQITEM||S.SERVICO, '0027S',  'IVASERPUB27',
              DECODE(B.CDIMPOSTO||B.VRALIQITEM,            'SI0',    'IVACOMBIE21',
              DECODE(B.CDIMPOSTO||B.VRALIQITEM,            '0021',   'IVABSU21',
              DECODE(B.CDIMPOSTO||TO_CHAR(B.VRALIQITEM),   NULL,     'EXENTODEIVA', ' ')))))))))))))))),12)||
         NVL(CUR_FIL.CDEXPORTEMPR, '  ')||'-'||
         RPAD(DECODE(SUBSTR(B.CONTACTBL, 1, 1), '1', '0', '2', '0', NVL(CDEXFILIAL, CDCENTLUCREN)), 1)||'-'||
         RPAD(B.CONTACTBL, 10)||'-'||
         '010'||
         LPAD(TO_CHAR(NVL(B.VRCONTABIL,0),'99999999999990.00'),18)||
         SUBSTR(NMCOMPRZSFOR,1,5)||
         RPAD(SUBSTR(NVL(CDSERIEENTR, '   '),2,5),4)||
         SUBSTR(USR_DSDADOSADIC(A.CDFILIAL, A.NRLANCTONF),1,240) AS DSARQUIVO
      FROM
         FORNECEDOR F,
         (SELECT
          DISTINCT NF.CDFILIAL,     NF.NRLANCTONF,   CP.NRSEQVENPAG,
                   NF.DTEMISSAO,    NF.DTENTRSAID,   CP.VRORIGPAG,    PR.CDEXPORTEMPR,
                   NF.IDTPIJURNF,   NF.NRINSJURNF,   NF.CDSERIEENTR,  CP.DTORIGVENPAG,
                   NF.NRNOTAFISC,   NV.VRNOTAFISC,   FO.NRINSJURFORN, FO.NMRAZSOCFORN,
                   FO.NMFANTFORN,   NV.NUMEVENC,     1  AS NRCONT,    PR.CDCENTLUCREN,
                   UC.NRSEQCOUNT,   PF.CDEXFILIAL,   NF.CDFORNECED,   NF.CDCLIENTE
          FROM
             CONTAPAGAR CP, TIPOOPERAC TC,
             FORNECEDOR FO, PARAMFILIAL PF, NOTAFISCAL NF, PARAMEMPRESA PR, USR_CONTAPAGAR UC,
             (SELECT CP1.CDFILIAL, CP1.NRLANCTONF, COUNT(*) AS NUMEVENC,
                     SUM(CP1.VRORIGPAG) AS VRNOTAFISC
              FROM NOTAFISCAL NF1, CONTAPAGAR CP1
              WHERE (NF1.CDFILIRECE   = CUR_FIL.CDFILIAL)                    AND
                    (NF1.IDSTATUS     = 'A')                                 AND
                    (NF1.IDENTRSAIDOP = 'E')                                 AND
                    (NF1.CDTIPOOPER <> '10')                                 AND
                    (NF1.DTENTRSAID <= SYSDATE)                              AND
                    ((NF1.IDORIGDESTNF IS NULL) OR (NF1.IDORIGDESTNF = 'F')) AND
                    (CP1.CDFILIAL     = NF1.CDFILIAL)                        AND
                    (CP1.NRLANCTONF   = NF1.NRLANCTONF)
              GROUP BY CP1.CDFILIAL, CP1.NRLANCTONF) NV
          WHERE (NF.CDFILIRECE      = CUR_FIL.CDFILIAL)                AND
                (NF.IDSTATUS        = 'A')                             AND
                (NF.IDENTRSAIDOP    = 'E')                             AND
                (NF.CDTIPOOPER     <> '10')                            AND
                (NF.DTENTRSAID    <= SYSDATE)                          AND
                ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                (PF.CDFILIAL        = NF.CDFILIRECE)                   AND
                (CP.CDFILIAL        = NF.CDFILIAL)                     AND
                (CP.NRLANCTONF      = NF.NRLANCTONF)                   AND
                (NV.CDFILIAL        = NF.CDFILIAL)                     AND
                (NV.NRLANCTONF      = NF.NRLANCTONF)                   AND
                (CP.CDFILIAL        = UC.CDFILIAL(+))                  AND
                (CP.NRLANCTONF      = UC.NRLANCTONF(+))                AND
                (CP.NRSEQVENPAG     = UC.NRSEQVENPAG(+))               AND
                (TC.IDENTRSAIDOP(+) = NF.IDENTRSAIDOP)                 AND
                (TC.CDTIPOOPER(+)   = NF.CDTIPOOPER)                   AND
                (FO.CDFORNECED(+)   = NF.CDFORNECED)                   AND
                (NF.DTEXPORTACAO IS NULL)                              AND
                (NF.CDEMPRESA       = PR.CDEMPRESA(+))
         ) A,

         (SELECT
             I.CDFILIAL,
             I.NRLANCTONF,
             I.CDIMPOSTO,
             I.TIPO,
             I.VRALIQITEM,
             I.CONTACTBL,
             DECODE(NVL(C.VRCONTABIL,0), 0,I.VRCONTABIL, (I.VRCONTABIL - ((I.VRCONTABIL / NVL(T.VRCONTABIL,1))) * NVL(C.VRCONTABIL,1))) AS VRCONTABIL
          FROM
             (SELECT
                 I.CDFILIAL,
                 I.NRLANCTONF,
                 I.CDIMPOSTO,
                 'ITEM' AS TIPO,
                 I.VRALIQITEM,
                 SUBSTR(USR_CONTCTBL_PROD(IT.CDFILIAL, IT.NRLANCTONF, P.CDPRODUTO, P.CDARVPROD,'E'),1,20) AS CONTACTBL,
                 SUM(VRCONTITEM)  AS VRCONTABIL
              FROM
                 NOTAFISCAL NF, ITEMPROIMPOS I, ITEMPRODUTO IT, PRODUTO P, IMPOSTO IMP
              WHERE
                 (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
                 (NF.IDSTATUS     = 'A')                                AND
                 (NF.IDENTRSAIDOP = 'E')                                AND
                 (NF.CDTIPOOPER  <> '10')                               AND
                 (NF.DTENTRSAID  <= SYSDATE)                            AND
                 ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                 (IT.CDFILIAL     = NF.CDFILIAL)                        AND
                 (IT.NRLANCTONF   = NF.NRLANCTONF)                      AND
                 (I.CDFILIAL      = IT.CDFILIAL)                        AND
                 (I.NRLANCTONF    = IT.NRLANCTONF)                      AND
                 (I.NRSEQITPROD   = IT.NRSEQITPROD)                     AND
                 (I.VRIMPOSTOIT    > 0)                                 AND
                 (P.CDPRODUTO     = IT.CDPRODUTO)                       AND
                 (IMP.CDIMPOSTO   = I.CDIMPOSTO)                        AND
                 (IMP.IDIMPORECUP = 'S')

              GROUP BY
                 I.CDFILIAL, I.NRLANCTONF, I.CDIMPOSTO, 'ITEM', I.VRALIQITEM,
                 SUBSTR(USR_CONTCTBL_PROD(IT.CDFILIAL, IT.NRLANCTONF, P.CDPRODUTO, P.CDARVPROD,'E'),1,20)
             ) I,
             (SELECT
                 I.CDFILIAL,
                 I.NRLANCTONF,
                 I.CDIMPOSTO,
                 SUBSTR(USR_CONTCTBL_PROD(IT.CDFILIAL, IT.NRLANCTONF, P.CDPRODUTO, P.CDARVPROD,'E'),1,20) AS CONTACTBL,
                 SUM(VRCONTITEM)  AS VRCONTABIL
              FROM
                 NOTAFISCAL NF, ITEMPROIMPOS I, ITEMPRODUTO IT, PRODUTO P, IMPOSTO IMP
              WHERE
                 (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
                 (NF.IDSTATUS     = 'A')                                AND
                 (NF.IDENTRSAIDOP = 'E')                                AND
                 (NF.CDTIPOOPER  <> '10')                               AND
                 (NF.DTENTRSAID  <= SYSDATE)                            AND
                 ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                 (IT.CDFILIAL     = NF.CDFILIAL)                        AND
                 (IT.NRLANCTONF   = NF.NRLANCTONF)                      AND
                 (I.CDFILIAL      = IT.CDFILIAL)                        AND
                 (I.NRLANCTONF    = IT.NRLANCTONF)                      AND
                 (I.NRSEQITPROD   = IT.NRSEQITPROD)                     AND
                 (I.VRIMPOSTOIT   > 0)                                  AND
                 (P.CDPRODUTO     = IT.CDPRODUTO)                       AND
                 (IMP.CDIMPOSTO   = I.CDIMPOSTO)                        AND
                 (IMP.IDIMPORECUP = 'S')

              GROUP BY
                 I.CDFILIAL, I.NRLANCTONF, I.CDIMPOSTO,
                 SUBSTR(USR_CONTCTBL_PROD(IT.CDFILIAL, IT.NRLANCTONF, P.CDPRODUTO, P.CDARVPROD,'E'),1,20)
             ) T,
             (SELECT
                 I.CDFILIAL,
                 I.NRLANCTONF,
                 SUM(I.VRIMPOSTONF) AS VRCONTABIL
              FROM NOTAFISCAL NF, IMPOSTONF I, IMPOSTO IP
              WHERE
                 (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
                 (NF.IDSTATUS     = 'A')                                AND
                 (NF.IDENTRSAIDOP = 'E')                                AND
                 (NF.CDTIPOOPER  <> '10')                               AND
                 (NF.DTENTRSAID  <= SYSDATE)                            AND
                 ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                 (I.CDFILIAL      = NF.CDFILIAL)                        AND
                 (I.NRLANCTONF    = NF.NRLANCTONF)                      AND
                 (I.CDIMPOSTO     = IP.CDIMPOSTO)                       AND
                 (IP.IDIMPORECUP  = 'N')                                AND
                 (I.VRIMPOSTONF   > 0)
              GROUP BY
                 I.CDFILIAL,
                 I.NRLANCTONF
             ) C
          WHERE
             (I.CDFILIAL   = C.CDFILIAL (+))   AND
             (I.NRLANCTONF = C.NRLANCTONF (+)) AND
             (T.CDFILIAL   = I.CDFILIAL)       AND
             (T.NRLANCTONF = I.NRLANCTONF)     AND
             (T.CDIMPOSTO  = I.CDIMPOSTO)      AND
             (T.CONTACTBL  = I.CONTACTBL)

          UNION ALL

          SELECT
             I.CDFILIAL,
             I.NRLANCTONF,
             I.CDIMPOSTO,
             'ITEM' as TIPO,
             C.VRALIQITEM,
             C.CONTACTBL,
             SUM(I.VRIMPOSTONF)  AS VRCONTABIL
          FROM NOTAFISCAL NF, IMPOSTONF I, IMPOSTO IP,
             (SELECT
                 NF.CDFILIAL,
		 NF.NRLANCTONF,
		 MAX(I.VRALIQITEM) AS VRALIQITEM,
                 MAX(SUBSTR(USR_CONTCTBL_PROD(IT.CDFILIAL, IT.NRLANCTONF, P.CDPRODUTO, P.CDARVPROD,'E'),1,20)) AS CONTACTBL
              FROM
                 NOTAFISCAL NF, ITEMPROIMPOS I, ITEMPRODUTO IT, PRODUTO P
              WHERE
                 (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
                 (NF.IDSTATUS     = 'A')                                AND
                 (NF.IDENTRSAIDOP = 'E')                                AND
                 (NF.CDTIPOOPER  <> '10')                               AND
                 (NF.DTENTRSAID  <= SYSDATE)                            AND
                 ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                 (IT.CDFILIAL    = NF.CDFILIAL)                         AND
                 (IT.NRLANCTONF  = NF.NRLANCTONF)                       AND
                 (I.CDFILIAL     = IT.CDFILIAL)                         AND
                 (I.NRLANCTONF   = IT.NRLANCTONF)                       AND
                 (I.NRSEQITPROD  = IT.NRSEQITPROD)                      AND
                 (I.VRIMPOSTOIT  <> 0)                                  AND
                 (P.CDPRODUTO    = IT.CDPRODUTO)
	      GROUP BY
		 NF.CDFILIAL,
		 NF.NRLANCTONF) C
          WHERE
             (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
             (NF.IDSTATUS     = 'A')                                AND
             (NF.IDENTRSAIDOP = 'E')                                AND
             (NF.CDTIPOOPER  <> '10')                               AND
             (NF.DTENTRSAID  <= SYSDATE)                            AND
             ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
             (I.CDFILIAL      = NF.CDFILIAL)                        AND
             (I.NRLANCTONF    = NF.NRLANCTONF)                      AND
             (I.CDIMPOSTO     = IP.CDIMPOSTO)                       AND
             (IP.IDIMPORECUP  = 'N')                                AND
             (I.VRIMPOSTONF   > 0)                                  AND
             (NF.CDFILIAL   = C.CDFILIAL)                           AND
             (NF.NRLANCTONF = C.NRLANCTONF)
          GROUP BY
             I.CDFILIAL,
             I.NRLANCTONF,
             I.CDIMPOSTO,
             'ITEM',
             C.VRALIQITEM,
             C.CONTACTBL

          UNION ALL

          SELECT
             INF.CDFILIAL,
             INF.NRLANCTONF,
             INF.CDIMPOSTO,
             'TAX' AS TIPO,
             INF.VRALIQNF AS VRALIQITEM,
             SUBSTR(USR_CONTCTBL_IMPOSTO(INF.CDFILIAL, INF.NRLANCTONF, INF.CDIMPOSTO),1,20) AS CONTACTBL,
             VRIMPOSTONF AS VRCONTABIL
          FROM NOTAFISCAL NF, IMPOSTONF INF, IMPOSTO I, PARAMFILIAL PF
          WHERE
             (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
             (NF.IDSTATUS     = 'A')                                AND
             (NF.IDENTRSAIDOP = 'E')                                AND
             (NF.CDTIPOOPER  <> '10')                               AND
             (NF.DTENTRSAID  <= SYSDATE)                            AND
             ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
             (INF.CDFILIAL    = NF.CDFILIAL)                        AND
             (INF.NRLANCTONF  = NF.NRLANCTONF)                      AND
             (NF.CDFILIRECE   = PF.CDFILIAL (+))                    AND
             (INF.CDIMPOSTO   = I.CDIMPOSTO)                        AND
             (INF.VRIMPOSTONF > 0)                                  AND
             (INF.IDIMPOSTONF = '1') AND
             ((((I.IDIMPORECUP = 'S' AND INF.CDFISCALNF = '1' AND NVL(PF.IDCARACFIS,'C') = 'C') OR
              (PF.IDCARACFIS = 'A')) AND (I.CDCCTBLATIV IS NOT NULL)) OR
              ((I.IDIMPORECUP <> 'S') AND (I.CDCCTBLPASS IS NOT NULL)))

          UNION ALL

          SELECT
             NF.CDFILIAL,
             NF.NRLANCTONF,
             I.CDIMPOSTO,
             'ITEM' AS TIPO,
             0 AS VRALIQITEM,
             SUBSTR(USR_CONTCTBL_IMPOSTO(I.CDFILIAL, I.NRLANCTONF, I.CDIMPOSTO),1,20) AS CONTACTBL,
             SUM(VRCONTITIMP) AS VRCONTABIL
          FROM NOTAFISCAL NF, ITEMPROIMPOS I
          WHERE
             (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
             (NF.IDSTATUS     = 'A')                                AND
             (NF.IDENTRSAIDOP = 'E')                                AND
             (NF.CDTIPOOPER  <> '10')                               AND
             (NF.DTENTRSAID  <= SYSDATE)                            AND
             ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
             (I.CDFILIAL      = NF.CDFILIAL)                        AND
             (I.NRLANCTONF    = NF.NRLANCTONF)                      AND
             (NF.CDFILIAL||NF.NRLANCTONF NOT IN (SELECT NF.CDFILIAL||NF.NRLANCTONF
                                                 FROM NOTAFISCAL NF, ITEMPROIMPOS I
                                                 WHERE
                                                    (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
                                                    (NF.IDSTATUS     = 'A')                                AND
                                                    (NF.IDENTRSAIDOP = 'E')                                AND
                                                    (NF.CDTIPOOPER  <> '10')                               AND
                                                    (NF.DTENTRSAID  <= SYSDATE)                            AND
                                                    ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
                                                    (I.CDFILIAL      = NF.CDFILIAL)                        AND
                                                    (I.NRLANCTONF    = NF.NRLANCTONF)                      AND
                                                    (I.VRIMPOSTOIT <> 0)))
          GROUP BY
             NF.CDFILIAL,
             NF.NRLANCTONF,
             I.CDIMPOSTO,
             'ITEM',
             0,
             SUBSTR(USR_CONTCTBL_IMPOSTO(I.CDFILIAL, I.NRLANCTONF, I.CDIMPOSTO),1,20)
         ) B,
         (SELECT
             DISTINCT
             NF.CDFILIAL,
             NF.NRLANCTONF,
             'S' AS SERVICO
          FROM NOTAFISCAL NF, ITEMPRODUTO IT, PRODUTO P
          WHERE
             (NF.CDFILIRECE   = CUR_FIL.CDFILIAL)                   AND
             (NF.IDSTATUS     = 'A')                                AND
             (NF.IDENTRSAIDOP = 'E')                                AND
             (NF.CDTIPOOPER  <> '10')                               AND
             (NF.DTENTRSAID  <= SYSDATE)                            AND
             ((NF.IDORIGDESTNF IS NULL) OR (NF.IDORIGDESTNF = 'F')) AND
             (IT.CDFILIAL    = NF.CDFILIAL)                         AND
             (IT.NRLANCTONF  = NF.NRLANCTONF)                       AND
             (P.CDPRODUTO    = IT.CDPRODUTO)                        AND
             (P.CDARVPROD LIKE '5%')
         ) S
      WHERE
         (A.CDFILIAL   = B.CDFILIAL   (+)) AND
         (A.NRLANCTONF = B.NRLANCTONF (+)) AND
         (A.CDFILIAL   = S.CDFILIAL   (+)) AND
         (A.NRLANCTONF = S.NRLANCTONF (+)) AND
         (A.CDFORNECED = F.CDFORNECED (+))
      ORDER BY A.CDFILIAL, A.NRLANCTONF) LOOP

         IF ((V_CDFILIANTE <> CUR_SEQU.CDFILIAL)     OR
             (V_NRLANCANTE <> CUR_SEQU.NRLANCTONF))  THEN

            V_CDFILIANTE := CUR_SEQU.CDFILIAL;
            V_NRLANCANTE := CUR_SEQU.NRLANCTONF;
            V_SEQUENCIAL := 1;
         END IF;

         INSERT INTO USR_SKYLCONTNFE
         (CDFILIAL, NRLANCTONF, CDSEQNF,DSARQUIVO)
         VALUES
         (CUR_SEQU.CDFILIAL, CUR_SEQU.NRLANCTONF, V_SEQUENCIAL, CUR_SEQU.DSARQUIVO);

         V_SEQUENCIAL := V_SEQUENCIAL + 1;

      END LOOP;
      FOR CUR_DADOS IN (
         SELECT TO_CHAR(CDSEQNF)||DSARQUIVO AS DSARQUIVO
         FROM USR_SKYLCONTNFE
         ORDER BY
            CDFILIAL, NRLANCTONF, CDSEQNF
      ) LOOP
         UTL_FILE.PUT(V_FILEHANDLE, CUR_DADOS.DSARQUIVO||CHR(13)||CHR(10));
         UTL_FILE.FFLUSH(V_FILEHANDLE);
      END LOOP;

      DELETE FROM USR_SKYLCONTNFE;

      UTL_FILE.FCLOSE(V_FILEHANDLE);
   END LOOP;
END;
END;
/
