/* Sistemas Integrados TEKNISA
  Gera��o RNF em Tabelas

Nome: 0002_SDXSTP0_EXPORTA_RNF.SQL

@C:\web_545\sql\end_conv\oracle\clientes\sdx\0002_SDXSTPO_EXPORTA_RNF.SQL

Descri��o:
Rotina para gera��o da RNF em tabelas.

P_CDFILIAL      - C�digo da filial a ser gerado a RNF.
P_DATA          - Data de processamento para gera��o da RNF.
P_IDENTRSAIDOP  - Identificador de notas fiscais de entrada ou sa�da.
P_IDTIPOOPER    - 'E' - Entrada / 'C' - Compra a Vista / 'F' - Transferencia Entrada / 'T' - Transfer�ncia Sa�da
                  'D' - Devolu��o / 'V' - Venda a Vista / 'P' - Venda a Prazo
P_CDTIPOOPER    - Tipo de Opera��o ('_01_02_03_04_06_08_11_12_13_16_17_99_')
P_IDFORMPGTSAP  - '_V_P_' (Vencidas - Pagar)
P_IDNMARQUIVO   - Identificador do nome do arquivo (RNF)

PARA DEBUGAR
raise_application_error(-20501, '' , True);

Cria��o:
06/08/2007 - Fabr�cio Marcelo da Silva
 
*/ 
 
CREATE OR REPLACE PROCEDURE EXPORTA_RNF
   (P_CDFILIAL      IN FILIAL.CDFILIAL%TYPE,
    P_DATA          IN DATE,
    P_IDENTRSAIDOP  IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
    P_IDTIPOOPER    IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
    P_CDTIPOOPER    IN VARCHAR2,
    P_IDFORMPGTSAP  IN VARCHAR2,
    P_IDNMARQUIVO   IN PARAMFILIAL.IDINVROTDIA%TYPE
   ) IS 

   V_IDOPERACAO      CHAR(1);
   V_IDCABECCRIADO   CHAR(1) := 'N';  -- Identificador de cabe�alho j� criado
   V_NUMERORNF       VARCHAR2(20);
   V_NRINJURNF       VARCHAR2(14);
   V_NRSEQITEM       INTEGER;
   V_IDMES           CHAR(1);
   V_NMARQUIVO       VARCHAR2(08);
   V_QTDEDOCU        INTEGER;
   V_IDCDRS          CHAR(1) := 'N';
   V_QTDECDRS        NUMBER;

   V_IDRMC           CHAR(1) := 'N';  -- Identificador de RNF j� gravado no RMC
   V_IDCONSRMCFDIA   VARCHAR2(1);     -- Identificador para consistir RMC no fim do dia
   V_NRSEQRMC        VARCHAR2(10);
   V_NRRMC           VARCHAR2(06);
   V_NRRNF           VARCHAR2(06);

   CURSOR C_NOTAFISCALE IS
      SELECT
          NOTA.CDFILIRECE,   NOTA.CDFILIEMIT,   NOTA.IDORIGDESTNF, NOTA.NMFILIAL,
          NOTA.IDENTRSAIDOP, NOTA.CDTIPOOPER,   NOTA.DTEMISSAO,    NOTA.DTENTRSAID,
          NOTA.VRNOTAFISC,   NOTA.VRIMPOSTOIT,
          NVL(ENCARGO.VRICMSSUBT,0) AS VRSUBSTRIB, NOTA.VRBASECALNF,
          NOTA.IDSISTEMA,    NOTA.NRLANCTONF,   NOTA.IDTPIJURNF, CGCFORCLIE_RNF(NOTA.CDTIPOOPER, NOTA.NRINSJURNF) AS CGCFORCLIE,
          ' ' AS DTATUAVENREC, SUBSTR(NOTA.CDSERIEENTR,1,3) AS CDSERIE,
          SUBSTR(NRNOTAFISC,LENGTH(NRNOTAFISC)-5,6) NRNOTAFISC,
          NOTA.CDCLIENTESAP, NOTA.IDFORMPGTSAP,
          NOTA.CDEMPRESAP, VRIMPOSIPI,  '000' AS NRSEQITPROD,
          NVL(ENCARGO.VRDESCNF,0) AS VRDESCNF,  NVL(ENCARGO.VRFRETE,0) AS VRFRETE,
          NVL(ENCARGO.VRSEGUR,0) AS VRSEGUR, NVL(ENCARGO.VLOUTDESP,0) AS VLOUTDESP,
          NFORIG.NRNOTAORIG, NFORIG.CDSERIEORIG, NOTA.CDFILIAL
       FROM
         (SELECT
             I.CDFILIAL, I.NRLANCTONF,
             SUM(DECODE(I.CDENCARGO, '05', I.VRITEMENCA, 0)) AS VRICMSSUBT,
             SUM(DECODE(I.CDENCARGO, '01', I.VRITEMENCA, 0)) AS VRFRETE,
             SUM(DECODE(I.CDENCARGO, '02', I.VRITEMENCA, 0)) AS VRSEGUR,
             SUM(DECODE(I.CDENCARGO, '03', I.VRITEMENCA, 0)) AS VLOUTDESP,
             SUM(DECODE(I.CDENCARGO, '04', I.VRITEMENCA, 0)) AS VRDESCNF
          FROM NOTAFISCAL N, ITEMENCARGO I
          WHERE
             (N.CDFILIRECE   = P_CDFILIAL)                 AND
             (N.IDENTRSAIDOP = P_IDENTRSAIDOP)             AND
             (N.DTENTRSAID   = P_DATA)                     AND
             (I.CDFILIAL     = N.CDFILIAL)                 AND
             (I.NRLANCTONF   = N.NRLANCTONF)               AND
             (I.CDENCARGO IN ('01', '02', '03', '04','05'))
          GROUP BY I.CDFILIAL, I.NRLANCTONF
         ) ENCARGO,
         (SELECT IT.CDFILIAL, IT.NRLANCTONF,
                 N.NRNOTAFISC AS NRNOTAORIG,
                 DECODE(N.CDSERIEENTR,'',N.CDSERIE,N.CDSERIEENTR) AS CDSERIEORIG
          FROM  NOTAFISCAL N, ITEMNOTAFISC IT
          WHERE
             ((N.IDENTRSAIDOP = P_IDENTRSAIDOP)   AND
              (N.CDTIPOOPER NOT IN ('10','11')))  AND
             (IT.CDFILIALIT   = N.CDFILIAL)       AND
             (IT.NRLANCTONFIT = N.NRLANCTONF)
         ) NFORIG,
         (SELECT NF.CDFILIAL,      NF.NRLANCTONF,    NF.NRINSJURNF,     NF.CDFILIEMIT,
                 NF.CDSERIEENTR,   NF.NRNOTAFISC,    NF.DTEMISSAO,      NF.CDTIPOOPER,
                 SUM(DECODE(CS.CDTRIBSDX,'01',NF.VRIMPOSTOIT,0.0)) AS VRIMPOSTOIT,
                 SUM(DECODE(NF.VRIMPOSTOIT,0.00,0.00,DECODE(CS.CDTRIBSDX,'01',NF.VRBASECALCIT,0.0))) AS VRBASECALNF , NF.IDSISTEMA,
                 NF.DTENTRSAID,    NF.IDTPIJURNF,    NF.IDENTRSAIDOP,   NF.CDINSCESTA,
                 NF.CDEMPRESA,     NF.CDFILIRECE,    NF.NMFILIAL,
                 NF.IDORIGDESTNF,  
                 NF.CDCLIENTESAP,  NF.IDFORMPGTSAP,
                 NF.VRNOTAFISC,    NF.CDEMPRESAP,
                 SUM(VRIMPOSIPI) AS VRIMPOSIPI
          FROM
             (SELECT CDEMPRESA , CDINSCESTA, IDENTRSAIDOP, CDTIPOOPER, CDFISCSAP,
                     CDCSTSAP,   PC.CDARVPROD, CDTRIBSDX
              FROM CODTRIBSAP CT, PRODUTO PC
              WHERE (INSTR(P_CDTIPOOPER, CT.CDTIPOOPER) <> 0) AND
                    (CT.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                    (CT.CDPRODUTO    = PC.CDPRODUTO(+))
             ) CS,
             (SELECT X.CDFILIAL,    X.NRLANCTONF,   X.NRINSJURNF,       X.CDFILIEMIT,
                    X.CDSERIEENTR,  X.NRNOTAFISC,   X.DTEMISSAO,        X.CDTIPOOPER,
                    X.VRIMPOSTOIT,  X.VRBASECALCIT, X.IDSISTEMA,
                    X.DTENTRSAID,   X.IDTPIJURNF,   X.IDENTRSAIDOP,     X.CDINSCESTA,
                    X.CDEMPRESA,    X.CDFILIRECE,   X.NMFILIAL,         X.IDORIGDESTNF,
                    '      ' AS CDCLIENTESAP,
                    ' '      AS IDFORMPGTSAP,
                    X.VRNOTAFISC,  X.NRSEQITPROD,      X.CDEMPRESAP,
                    X.VRIMPOSIPI,
                    MAX(C.CDARVPROD)    AS CDPRODSAP,
                    X.CDPRODUTO,   
                    X.CDSITUTRIBIT, X.CDFISCALIT
              FROM
                 (SELECT CDEMPRESA , CDINSCESTA, IDENTRSAIDOP, CDTIPOOPER, CDFISCSAP,
                         CDCSTSAP, PC.CDARVPROD
                  FROM CODTRIBSAP CT, PRODUTO PC
                  WHERE (INSTR(P_CDTIPOOPER, CT.CDTIPOOPER) <> 0) AND
                        (CT.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                        (CT.CDPRODUTO    = PC.CDPRODUTO(+))
                 ) C,
                 (SELECT
                      N.CDFILIAL,    N.NRLANCTONF,   N.NRINSJURNF,  N.CDFILIEMIT,
                      N.CDSERIEENTR, N.NRNOTAFISC,   N.DTEMISSAO,   N.CDTIPOOPER,
                      DECODE(T.CDIMPOSTO,'00',T.VRIMPOSTOIT,0.0)  VRIMPOSTOIT,
                      DECODE(T.CDIMPOSTO,'00',T.VRBASECALCIT,0.0) VRBASECALCIT,
                      N.IDSISTEMA,   N.IDENTRSAIDOP, N.DTENTRSAID,  N.IDTPIJURNF,
                      N.CDINSCESTA,  N.CDEMPRESA,    N.CDFILIRECE,  N.IDORIGDESTNF,
                      E.NMFILIAL,    N.VRNOTAFISC,   
                      '000' AS NRSEQITPROD, FL.CDEMPRESAP, DECODE(T.CDIMPOSTO,'10',T.VRIMPOSTOIT,0.0) AS VRIMPOSIPI,
                      SUBSTR(I.CDSITUTRIBPR,2,2) AS CDSITUTRIBIT, T.CDFISCALIT,PR.CDARVPROD AS CDPRODUTO
                  FROM NOTAFISCAL N, ITEMPRODUTO I, FORNECEDOR F, PRODUTO PR,
                       CLIENTE C, FILIAL E, FILIAL FL,
                      (SELECT
                          I.CDFILIAL, I.NRLANCTONF, I.NRSEQITPROD,
                          I.CDIMPOSTO, I.VRIMPOSTOIT, I.VRBASECALCIT, I.CDFISCALIT
                       FROM NOTAFISCAL N, ITEMPROIMPOS I
                       WHERE
                          (N.CDFILIRECE   = P_CDFILIAL)                                          AND
                          (N.IDENTRSAIDOP = P_IDENTRSAIDOP)                                      AND
                          (N.DTENTRSAID   = P_DATA)                                              AND
                          (N.IDSTATUS       <> 'C')                                              AND
                          (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)  AND
                          (INSTR(P_CDTIPOOPER, N.CDTIPOOPER) <> 0)                               AND
                          (N.DTEXPRNF IS NULL)                                                   AND
                          (I.CDFILIAL     = N.CDFILIAL)                                          AND
                          (I.NRLANCTONF   = N.NRLANCTONF)                                        AND
                          (I.NRITPROIMPOS = '1')
                      ) T
                  WHERE
                     (N.CDFILIRECE    = P_CDFILIAL)                                         AND
                     (N.IDENTRSAIDOP  = P_IDENTRSAIDOP)                                     AND
                     (N.DTENTRSAID    = P_DATA)                                             AND
                     (N.IDSTATUS       <> 'C')                                              AND
                     (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)  AND
                     (INSTR(P_CDTIPOOPER, N.CDTIPOOPER) <> 0)                               AND
                     (N.DTEXPRNF IS NULL)                                                   AND
                     (N.CDFILIAL      = I.CDFILIAL)                                         AND
                     (N.NRLANCTONF    = I.NRLANCTONF)                                       AND
                     (I.CDFILIAL      = T.CDFILIAL(+))                                      AND
                     (I.NRLANCTONF    = T.NRLANCTONF(+))                                    AND
                     (I.NRSEQITPROD   = T.NRSEQITPROD(+))                                   AND
                     (I.CDPRODUTO     = PR.CDPRODUTO)                                       AND
                     (N.CDCLIENTE     = C.CDCLIENTE(+))                                     AND
                     (N.CDFORNECED    = F.CDFORNECED(+))                                    AND
                     (N.CDFILIEMIT    = E.CDFILIAL(+))                                      AND
                     (N.CDFILIRECE    = FL.CDFILIAL(+))
                 ) X
              WHERE
                 (X.CDEMPRESA           = C.CDEMPRESA    (+))                AND
                 (X.CDINSCESTA          = C.CDINSCESTA   (+))                AND
                 (X.IDENTRSAIDOP        = C.IDENTRSAIDOP (+))                AND
                 (X.CDTIPOOPER          = C.CDTIPOOPER   (+))                AND
                 (NVL(X.CDFISCALIT,'3') = C.CDFISCSAP    (+))                AND
                 (X.CDSITUTRIBIT        = C.CDCSTSAP     (+))                AND
                 (INSTR('D'||X.CDPRODUTO,'D'||NVL(C.CDARVPROD(+),'')) = 1)
              GROUP BY
                 X.CDFILIAL,     X.NRLANCTONF,   X.NRINSJURNF,   X.CDFILIEMIT,
                 X.CDSERIEENTR,  X.NRNOTAFISC,   X.DTEMISSAO,    X.CDTIPOOPER,
                 X.VRIMPOSTOIT,  X.VRBASECALCIT, X.IDSISTEMA,    X.DTENTRSAID,   X.IDTPIJURNF,
                 X.IDENTRSAIDOP, X.CDINSCESTA,   X.CDEMPRESA,    X.CDFILIRECE,
                 X.NMFILIAL,     X.IDORIGDESTNF, X.NRSEQITPROD,  X.CDEMPRESAP,
                 X.VRNOTAFISC,   X.VRIMPOSIPI,   X.CDPRODUTO,
                 X.CDSITUTRIBIT, X.CDFISCALIT
             ) NF
          WHERE (NF.CDEMPRESA           = CS.CDEMPRESA    (+))        AND
                (NF.CDINSCESTA          = CS.CDINSCESTA   (+))        AND
                (NF.IDENTRSAIDOP        = CS.IDENTRSAIDOP (+))        AND
                (NF.CDTIPOOPER          = CS.CDTIPOOPER   (+))        AND
                (NVL(NF.CDFISCALIT,'3') = CS.CDFISCSAP    (+))        AND
                (NF.CDSITUTRIBIT        = CS.CDCSTSAP     (+))        AND
                (NVL(NF.CDPRODSAP,'X')  = NVL(CS.CDARVPROD(+),'X'))
          GROUP BY
                 NF.CDFILIAL,     NF.NRLANCTONF,   NF.NRINSJURNF,   NF.CDFILIEMIT,
                 NF.CDSERIEENTR,  NF.NRNOTAFISC,   NF.DTEMISSAO,    NF.CDTIPOOPER,
                 NF.IDSISTEMA,    NF.DTENTRSAID,   NF.IDTPIJURNF,
                 NF.IDENTRSAIDOP, NF.CDINSCESTA,   NF.CDEMPRESA,    NF.CDFILIRECE,
                 NF.NMFILIAL,     NF.IDORIGDESTNF,
                 NF.CDEMPRESAP,   NF.VRNOTAFISC,
                 NF.CDCLIENTESAP, NF.IDFORMPGTSAP
         ) NOTA
       WHERE(NOTA.CDFILIAL    = ENCARGO.CDFILIAL   (+)) AND
            (NOTA.NRLANCTONF  = ENCARGO.NRLANCTONF (+)) AND
            (NOTA.VRNOTAFISC <> 0)                      AND
            (NOTA.CDFILIAL    = NFORIG.CDFILIAL    (+)) AND
            (NOTA.NRLANCTONF  = NFORIG.NRLANCTONF  (+))
       ORDER BY NOTA.IDENTRSAIDOP, NOTA.CDTIPOOPER;

   CURSOR C_NOTAFISCALS IS
      SELECT
          NOTA.CDFILIRECE,   NOTA.CDFILIEMIT,   NOTA.IDORIGDESTNF, NOTA.NMFILIAL,
          NOTA.IDENTRSAIDOP, NOTA.CDTIPOOPER,   NOTA.DTEMISSAO,    NOTA.DTENTRSAID,
          NOTA.VRNOTAFISC,   NOTA.VRIMPOSTOIT,  NOTA.NRCPFCNPJDFS,
          NVL(ENCARGO.VRICMSSUBT,0) AS VRSUBSTRIB, NOTA.VRBASECALNF,
          NOTA.IDSISTEMA,    NOTA.NRLANCTONF,   NOTA.IDTPIJURNF,   NOTA.NRINSJURNF AS CGCFORCLIE,
          SUBSTR(NOTA.CDSERIE,1,3) AS CDSERIE,
          SUBSTR(NRNOTAFISC,LENGTH(NRNOTAFISC)-5,6) NRNOTAFISC,
          REC.DTATUAVENREC,  NOTA.CDCLIENTESAP, NOTA.IDFORMPGTSAP,
          NOTA.CDEMPRESAP, VRIMPOSIPI,  '000' AS NRSEQITPROD,
          NVL(ENCARGO.VRDESCNF,0) AS VRDESCNF,  NVL(ENCARGO.VRFRETE,0) AS VRFRETE,
          NVL(ENCARGO.VRSEGUR,0) AS VRSEGUR, NVL(ENCARGO.VLOUTDESP,0) AS VLOUTDESP,
          NFORIG.NRNOTAORIG, NFORIG.CDSERIEORIG, NFORIG.CDTIPOPEORIG, NOTA.CDFILIAL
       FROM
          (SELECT REC.CDFILIAL, REC.NRLANCTONF,
                  MAX(REC.DTATUAVENREC) DTATUAVENREC
           FROM  NOTAFISCAL CL, CONTARECEBER REC
           WHERE (CL.CDFILIEMIT      = P_CDFILIAL)      AND
                 (CL.IDENTRSAIDOP    = P_IDENTRSAIDOP)  AND
                 (CL.IDSTATUS      IN ('A','E'))        AND
                 (CL.DTENTRSAID      = P_DATA)          AND
                 (CL.CDFILIAL        = REC.CDFILIAL)    AND
                 (CL.NRLANCTONF      = REC.NRLANCTONF)
           GROUP BY REC.CDFILIAL, REC.NRLANCTONF
          ) REC,
          (SELECT I.CDFILIAL, I.NRLANCTONF,
                  SUM(DECODE(I.CDENCARGO, '05', I.VRITEMENCA, 0)) AS VRICMSSUBT,
                  SUM(DECODE(I.CDENCARGO, '01', I.VRITEMENCA, 0)) AS VRFRETE,
                  SUM(DECODE(I.CDENCARGO, '02', I.VRITEMENCA, 0)) AS VRSEGUR,
                  SUM(DECODE(I.CDENCARGO, '03', I.VRITEMENCA, 0)) AS VLOUTDESP,
                  SUM(DECODE(I.CDENCARGO, '04', I.VRITEMENCA, 0)) AS VRDESCNF
           FROM  NOTAFISCAL N, ITEMENCARGO I
           WHERE (N.CDFILIEMIT    = P_CDFILIAL)             AND
                 (N.IDENTRSAIDOP  = P_IDENTRSAIDOP)         AND
                 (N.DTENTRSAID    = P_DATA)                 AND
                 (N.CDFILIAL      = I.CDFILIAL)             AND
                 (N.NRLANCTONF    = I.NRLANCTONF)           AND
                 (I.CDENCARGO IN ('01', '02', '03', '04','05'))
           GROUP BY I.CDFILIAL, I.NRLANCTONF
          ) ENCARGO,
          (SELECT ITEMNOTAFISC.CDFILIAL, ITEMNOTAFISC.NRLANCTONF, NOTAFISCAL.NRNOTAFISC AS NRNOTAORIG,
                  DECODE(NOTAFISCAL.CDSERIEENTR,'',NOTAFISCAL.CDSERIE,NOTAFISCAL.CDSERIEENTR) AS CDSERIEORIG,
                  NOTAFISCAL.CDTIPOOPER AS CDTIPOPEORIG
           FROM  NOTAFISCAL , ITEMNOTAFISC
           WHERE (((NOTAFISCAL.IDENTRSAIDOP = 'E')                      AND
                  (NOTAFISCAL.CDTIPOOPER <> '11'))                      OR
                  (NOTAFISCAL.IDENTRSAIDOP  = P_IDENTRSAIDOP))          AND
                 (NOTAFISCAL.CDFILIAL       = ITEMNOTAFISC.CDFILIALIT)  AND
                 (NOTAFISCAL.NRLANCTONF     = ITEMNOTAFISC.NRLANCTONFIT)
          ) NFORIG,
          (SELECT NF.CDFILIAL,      NF.NRLANCTONF,    NF.NRINSJURNF,     NF.CDFILIEMIT,
                  NF.CDSERIE,       NF.NRNOTAFISC,    NF.DTEMISSAO,      NF.CDTIPOOPER,
                  SUM(DECODE(CS.CDTRIBSDX,'01',NF.VRIMPOSTOIT,0.0)) AS VRIMPOSTOIT,
                  SUM(DECODE(NF.VRIMPOSTOIT,0.00,0.00,DECODE(CS.CDTRIBSDX,'01',NF.VRBASECALCIT,0.0))) AS VRBASECALNF , NF.IDSISTEMA,
                  NF.DTENTRSAID,    NF.IDTPIJURNF,    NF.IDENTRSAIDOP,   NF.CDINSCESTA,
                  NF.CDEMPRESA,     NF.CDFILIRECE,    NF.NMFILIAL,
                  NF.IDORIGDESTNF,  NF.NRCPFCNPJDFS,
                  NF.CDCLIENTESAP,  NF.IDFORMPGTSAP,
                  NF.VRNOTAFISC,    NF.CDEMPRESAP,
                  SUM(VRIMPOSIPI) AS VRIMPOSIPI
           FROM
              (SELECT CDEMPRESA , CDINSCESTA, IDENTRSAIDOP, CDTIPOOPER, CDFISCSAP,
                      CDCSTSAP, PC.CDARVPROD, CDTRIBSDX
               FROM CODTRIBSAP CT, PRODUTO PC
               WHERE (INSTR(P_CDTIPOOPER, CT.CDTIPOOPER) <> 0) AND
                     (CT.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                     (CT.CDPRODUTO    = PC.CDPRODUTO(+))
              ) CS,
              (SELECT X.CDFILIAL,      X.NRLANCTONF,    X.NRINSJURNF,     X.CDFILIEMIT,
                      X.CDSERIE,       X.NRNOTAFISC,    X.DTEMISSAO,      X.CDTIPOOPER,
                      X.VRIMPOSTOIT,   X.VRBASECALCIT,  X.IDSISTEMA,
                      X.DTENTRSAID,    X.IDTPIJURNF,    X.IDENTRSAIDOP,   X.CDINSCESTA,
                      X.CDEMPRESA,     X.CDFILIRECE,    X.NMFILIAL,       X.IDORIGDESTNF,
                      '      ' AS CDCLIENTESAP, MAX(X.IDFORMPGTSAP) AS IDFORMPGTSAP,
                      X.VRNOTAFISC, X.NRSEQITPROD,    X.CDEMPRESAP,
                      X.VRIMPOSIPI,
                      MAX(C.CDARVPROD)    AS CDPRODSAP,
                      X.CDPRODUTO,    X.NRCPFCNPJDFS,
                      X.CDSITUTRIBIT, X.CDFISCALIT
               FROM
                  (SELECT CDEMPRESA , CDINSCESTA, IDENTRSAIDOP, CDTIPOOPER, CDFISCSAP,
                          CDCSTSAP, PC.CDARVPROD
                   FROM CODTRIBSAP CT, PRODUTO PC
                   WHERE (INSTR(P_CDTIPOOPER, CT.CDTIPOOPER) <> 0) AND
                         (CT.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                         (CT.CDPRODUTO = PC.CDPRODUTO(+))
                  ) C,
                  (SELECT N.CDFILIAL,    N.NRLANCTONF,   N.NRINSJURNF,   N.CDFILIEMIT,
                          N.CDSERIE,     N.NRNOTAFISC,   N.DTEMISSAO,    N.CDTIPOOPER,
                          DECODE(T.CDIMPOSTO,'00',T.VRIMPOSTOIT,0.0) VRIMPOSTOIT,
                          DECODE(T.CDIMPOSTO,'00',T.VRBASECALCIT,0.0) VRBASECALCIT,
                          N.IDSISTEMA,   N.IDENTRSAIDOP, N.DTENTRSAID,   N.IDTPIJURNF,
                          N.CDINSCESTA,  N.CDEMPRESA,    N.CDFILIRECE,   N.IDORIGDESTNF,
                          NVL(P.IDFORMPGTSAP,'V')    AS IDFORMPGTSAP, N.NRCPFCNPJDFS,
                          E.NMFILIAL,    N.VRNOTAFISC,  '000' AS NRSEQITPROD,
                          FL.CDEMPRESAP, DECODE(T.CDIMPOSTO,'10',T.VRIMPOSTOIT,0.0) AS VRIMPOSIPI,
                          SUBSTR(I.CDSITUTRIBPR,2,2) AS CDSITUTRIBIT, T.CDFISCALIT, PR.CDARVPROD AS CDPRODUTO
                   FROM NOTAFISCAL N, ITEMPRODUTO I, CLIENTE C, FORNECEDOR F, PRODUTO PR,
                        FILIAL E, FILIAL FL,
                        (SELECT
                            I.CDFILIAL, I.NRLANCTONF, I.NRSEQITPROD,
                            I.CDIMPOSTO, I.VRIMPOSTOIT, I.VRBASECALCIT, I.CDFISCALIT
                         FROM NOTAFISCAL N, ITEMPROIMPOS I
                         WHERE
                            (N.CDFILIEMIT     = P_CDFILIAL)                                          AND
                            (N.IDENTRSAIDOP   = P_IDENTRSAIDOP)                                      AND
                            (N.DTENTRSAID     = P_DATA)                                              AND
                            (N.IDSTATUS      IN ('A','E'))                                           AND
                            (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)    AND
                            ((INSTR(P_CDTIPOOPER, N.CDTIPOOPER) <> 0)                               AND
                             ((N.CDTIPOOPER =  '06' AND P_IDTIPOOPER =  'T' AND N.IDORIGDESTNF =  'L') OR
                              (N.CDTIPOOPER =  '06' AND P_IDTIPOOPER =  'D' AND N.IDORIGDESTNF <> 'L') OR
                              (N.CDTIPOOPER =  '06' AND P_IDTIPOOPER =  'S')                        OR
                              (N.CDTIPOOPER <> '06' AND P_IDTIPOOPER =  'T')                           OR
                              (N.CDTIPOOPER <> '06' AND P_IDTIPOOPER <> 'T')))                       AND
                            (N.DTEXPRNF IS NULL)                                                     AND
                            (I.CDFILIAL       = N.CDFILIAL)                                          AND
                            (I.NRLANCTONF     = N.NRLANCTONF)                                        AND
                            (I.NRITPROIMPOS   = '1')
                        ) T,
                        (SELECT B.CDFILIAL, B.NRLANCTONF,
                                DECODE(MAX(B.DTATUAVENREC) - DTENTRSAID,0,'V','P') AS IDFORMPGTSAP
                         FROM  NOTAFISCAL L, CONTARECEBER B
                         WHERE (L.CDFILIEMIT   = P_CDFILIAL)      AND
                               (L.IDENTRSAIDOP = P_IDENTRSAIDOP)  AND
                               (L.DTENTRSAID   = P_DATA)          AND
                               (L.CDFILIAL     = B.CDFILIAL)      AND
                               (L.NRLANCTONF   = B.NRLANCTONF)
                         GROUP BY B.CDFILIAL, B.NRLANCTONF, DTENTRSAID
                        ) P
                   WHERE
                      (N.CDFILIEMIT    = P_CDFILIAL)                                           AND
                      (N.IDENTRSAIDOP  = P_IDENTRSAIDOP)                                       AND
                      (N.DTENTRSAID    = P_DATA)                                               AND
                      (N.IDSTATUS      IN ('A','E'))                                           AND
                      (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)    AND
                      ((INSTR(P_CDTIPOOPER, N.CDTIPOOPER) <> 0)                                AND
                       ((N.CDTIPOOPER =  '06' AND P_IDTIPOOPER =  'T' AND N.IDORIGDESTNF =  'L') OR
                        (N.CDTIPOOPER =  '06' AND P_IDTIPOOPER =  'D' AND N.IDORIGDESTNF <> 'L') OR
                        (N.CDTIPOOPER =  '06' AND P_IDTIPOOPER =  'S')                        OR
                        (N.CDTIPOOPER <> '06' AND P_IDTIPOOPER =  'T')                           OR
                        (N.CDTIPOOPER <> '06' AND P_IDTIPOOPER <> 'T')))                       AND
                      (N.DTEXPRNF IS NULL)                                                     AND
                      (N.CDFILIAL      = I.CDFILIAL)                                           AND
                      (N.NRLANCTONF    = I.NRLANCTONF)                                         AND
                      (I.CDFILIAL      = T.CDFILIAL(+))                                        AND
                      (I.NRLANCTONF    = T.NRLANCTONF(+))                                      AND
                      (I.NRSEQITPROD   = T.NRSEQITPROD(+))                                     AND
                      (I.CDPRODUTO     = PR.CDPRODUTO)                                         AND
                      (N.CDCLIENTE     = C.CDCLIENTE(+))                                       AND
                      (N.CDFORNECED    = F.CDFORNECED(+))                                      AND
                      (N.CDFILIRECE    = E.CDFILIAL(+))                                        AND
                      (N.CDFILIEMIT    = FL.CDFILIAL(+))                                       AND
                      (N.CDFILIAL      = P.CDFILIAL(+))                                        AND
                      (N.NRLANCTONF    = P.NRLANCTONF(+))
                  ) X
               WHERE
                  (X.CDEMPRESA           = C.CDEMPRESA    (+))               AND
                  (X.CDINSCESTA          = C.CDINSCESTA   (+))               AND
                  (X.IDENTRSAIDOP        = C.IDENTRSAIDOP (+))               AND
                  (X.CDTIPOOPER          = C.CDTIPOOPER   (+))               AND
                  (NVL(X.CDFISCALIT,'3') = C.CDFISCSAP    (+))               AND
                  (X.CDSITUTRIBIT        = C.CDCSTSAP     (+))               AND
                  (INSTR('D'||X.CDPRODUTO,'D'||NVL(C.CDARVPROD(+),'')) = 1)
               GROUP BY
                  X.CDFILIAL,     X.NRLANCTONF,   X.NRINSJURNF,   X.CDFILIEMIT,
                  X.CDSERIE,      X.NRNOTAFISC,   X.DTEMISSAO,    X.CDTIPOOPER,
                  X.VRIMPOSTOIT,  X.VRBASECALCIT, X.IDSISTEMA,    X.DTENTRSAID,
                  X.IDTPIJURNF,   X.IDENTRSAIDOP, X.CDINSCESTA,   X.CDEMPRESA,
                  X.CDFILIRECE,   X.NMFILIAL,     X.IDORIGDESTNF, X.NRSEQITPROD,
                  X.CDEMPRESAP,   X.VRNOTAFISC,   X.VRIMPOSIPI,   X.CDPRODUTO,
                  X.CDSITUTRIBIT, X.CDFISCALIT,   X.NRCPFCNPJDFS
              ) NF
           WHERE
              (NF.CDEMPRESA           = CS.CDEMPRESA    (+))        AND
              (NF.CDINSCESTA          = CS.CDINSCESTA   (+))        AND
              (NF.IDENTRSAIDOP        = CS.IDENTRSAIDOP (+))        AND
              (NF.CDTIPOOPER          = CS.CDTIPOOPER   (+))        AND
              (NVL(NF.CDFISCALIT,'3') = CS.CDFISCSAP    (+))        AND
              (NF.CDSITUTRIBIT        = CS.CDCSTSAP     (+))        AND
              (NVL(NF.CDPRODSAP,'X')  = NVL(CS.CDARVPROD(+),'X'))
           GROUP BY
              NF.CDFILIAL,     NF.NRLANCTONF,   NF.NRINSJURNF,   NF.CDFILIEMIT,
              NF.CDSERIE,      NF.NRNOTAFISC,   NF.DTEMISSAO,    NF.CDTIPOOPER,
              NF.IDSISTEMA,    NF.DTENTRSAID,   NF.IDTPIJURNF,
              NF.IDENTRSAIDOP, NF.CDINSCESTA,   NF.CDEMPRESA,    NF.CDFILIRECE,
              NF.NMFILIAL,     NF.IDORIGDESTNF, NF.NRCPFCNPJDFS,
              NF.CDEMPRESAP,   NF.VRNOTAFISC,
              NF.CDCLIENTESAP, NF.IDFORMPGTSAP
          ) NOTA
       WHERE
          (NOTA.CDFILIAL    = ENCARGO.CDFILIAL(+))         AND
          (NOTA.NRLANCTONF  = ENCARGO.NRLANCTONF(+))       AND
          (NOTA.VRNOTAFISC <> 0)                           AND
          (NOTA.CDFILIAL    = REC.CDFILIAL(+))             AND
          (NOTA.NRLANCTONF  = REC.NRLANCTONF(+))           AND
          (NOTA.CDFILIAL    = NFORIG.CDFILIAL(+))          AND
          (INSTR(P_IDFORMPGTSAP, NOTA.IDFORMPGTSAP) <> 0)  AND
          (NOTA.NRLANCTONF  = NFORIG.NRLANCTONF(+))
       ORDER BY NOTA.IDENTRSAIDOP, NOTA.CDTIPOOPER;

BEGIN
   /* M�s de gera��o do arquivo para compor o nome do arquivo */
   IF TO_CHAR(P_DATA, 'MM') = '10' THEN
      V_IDMES := 'O';                           /* Outubro */
   ELSIF TO_CHAR(P_DATA, 'MM') = '11' THEN
      V_IDMES := 'N';                           /* Novembro */
   ELSIF TO_CHAR(P_DATA, 'MM') = '12' THEN
      V_IDMES := 'D';                           /* Dezembro */
   ELSE
      V_IDMES := SUBSTR(TO_CHAR(P_DATA, 'MM'),2,1);
   END IF;

   V_NMARQUIVO := (P_IDNMARQUIVO || P_CDFILIAL || TO_CHAR(P_DATA, 'DD') || V_IDMES);


   /*  DOCUMENTOS FISCAIS DE ENTRADA  */
   IF P_IDENTRSAIDOP = 'E' THEN

      V_QTDEDOCU := 0;

      FOR C_NOTAFISCAL IN C_NOTAFISCALE LOOP

         IF V_IDCABECCRIADO <> 'S' THEN

            NOVO_CODIGO('NUMERNF/FILIAL'||P_CDFILIAL, V_NUMERORNF, 1);
            V_NUMERORNF := SUBSTR(V_NUMERORNF, 11, 10);

            /* Insere novo n�mero de RNF */
            INSERT INTO NUMERORNF
              (CDFILIAL, DTNUMERNF, IDENTRSAID, IDTIPOOPERNF, NRNUMERNF)
            VALUES
              (P_CDFILIAL, P_DATA, C_NOTAFISCAL.IDENTRSAIDOP, P_IDTIPOOPER, V_NUMERORNF);

            /* Insere cabe�alho da RNF */
            INSERT INTO USR_HEAD_TRAIL_RNF
               (CDEMPSAP, FILIAL, NRRNF, DTRNF, HRRNF, NOMEA, QTDDT)
            VALUES
               (C_NOTAFISCAL.CDEMPRESAP, P_CDFILIAL, SUBSTR(V_NUMERORNF,5,6), P_DATA,
                TO_CHAR(SYSDATE,'HH24:MI'), V_NMARQUIVO, '0');

            V_IDCABECCRIADO := 'S';
         END IF;

         /* Inclui ou altera a data e o n�mero da RNF para os documentos */
         UPDATE NOTAFISCAL
         SET DTEXPRNF = P_DATA,
             NREXPRNF = V_NUMERORNF
         WHERE CDFILIAL   = C_NOTAFISCAL.CDFILIAL    AND
               NRLANCTONF = C_NOTAFISCAL.NRLANCTONF;

         IF C_NOTAFISCAL.IDORIGDESTNF = 'L' THEN
            V_NRINJURNF := C_NOTAFISCAL.CDFILIEMIT;
         ELSE
            IF C_NOTAFISCAL.IDTPIJURNF = 'I' THEN
               V_NRINJURNF := SUBSTR(C_NOTAFISCAL.CGCFORCLIE,15,6);
            ELSE
               V_NRINJURNF := SUBSTR(C_NOTAFISCAL.CGCFORCLIE,1,14);
            END IF;
         END IF;

         /* Insere Corpo das Notas Fiscais */
         INSERT INTO USR_CORPO_RNF
            (CDEMPSAP, FILIAL, NRRNF, CDCGC, CDSER, NRNOT, VLRNF, DTEMI, DTVEN,
             DTRNF, VLSTR, VLDESC, VLFRETE, VLSEGUR, VLODES)
         VALUES
            (C_NOTAFISCAL.CDEMPRESAP, C_NOTAFISCAL.CDFILIRECE, SUBSTR(V_NUMERORNF,5,6), LPAD(V_NRINJURNF,14, ' '), LPAD(C_NOTAFISCAL.CDSERIE,3, ' '),
             LPAD(C_NOTAFISCAL.NRNOTAFISC,6, '0'), NVL(C_NOTAFISCAL.VRNOTAFISC,0), C_NOTAFISCAL.DTEMISSAO, NULL,
             P_DATA, NVL(C_NOTAFISCAL.VRSUBSTRIB,0), NVL(C_NOTAFISCAL.VRDESCNF,0), NVL(C_NOTAFISCAL.VRFRETE,0),
             NVL(C_NOTAFISCAL.VRSEGUR,0), NVL(C_NOTAFISCAL.VLOUTDESP,0));

         V_NRSEQITEM := 0;

         FOR C_ITEMNF IN
            (SELECT
                NOTA.CDFILIRECE,   NOTA.CDFILIEMIT,   NOTA.IDORIGDESTNF, NOTA.NMRAZSOCCLIE,
                NOTA.NMRAZSOCFORN, NOTA.NMFILIAL,     NOTA.IDENTRSAIDOP, NOTA.CDTIPOOPER,
                NOTA.DTEMISSAO,    NOTA.DTENTRSAID,   NOTA.VRTOTAITEM,
                NOTA.VRBASECALCIT, NOTA.VRALIQITEM,   NOTA.VRIMPOSTOIT,
               ((NOTA.VRTOTAITEM / NOTA.VRNOTAFISC) * ENCARGO.VRICMSSUBT) AS VRSUBSTRIB,
                NOTA.IDSISTEMA,    NOTA.CDFISCOPERIT, NOTA.CDTRIBSDX,    NOTA.NRLANCTONF,
                NOTA.CDTIPDOCUSAP, NOTA.CDTIPTRANSAP, NOTA.IDTPIJURNF,   NOTA.CDFISCALIT,
                CGCFORCLIE_RNF(NOTA.CDTIPOOPER, NOTA.NRINSJURNF) AS CGCFORCLIE,
                SUBSTR(NOTA.CDSERIEENTR,1,3) AS CDSERIE,
                SUBSTR(NRNOTAFISC,LENGTH(NRNOTAFISC)-5,6) NRNOTAFISC,
                REC.DTATUAVENREC,
                NOTA.CDCLIENTESAP, NOTA.IDFORMPGTSAP,
                NOTA.ESPACO1, NOTA.ESPACO2, NOTA.CDEMPRESAP, VRIMPOSIPI,
                NOTA.CDPRODUTO, SUBSTR(NOTA.NRSEQITPROD,2,3) AS NRSEQITPROD,
                NOTA.QTITEMPROD, NOTA.VRUNITITEM, NOTA.VRDESCITEM,
                ENCARGO.VRFRETE, ENCARGO.VRSEGUR, ENCARGO.VLOUTDESP,
                NFORIG.NRNOTAORIG, NFORIG.CDSERIEORIG
             FROM
                (SELECT REC.CDFILIAL, REC.NRLANCTONF,
                        MAX(REC.DTATUAVENREC) DTATUAVENREC
                 FROM  NOTAFISCAL CL, CONTARECEBER REC
                 WHERE (CL.CDFILIEMIT   = P_CDFILIAL)     AND
                       (CL.IDENTRSAIDOP = 'S')            AND
                       (CL.IDSTATUS     IN ('A','E'))     AND
                       (CL.DTENTRSAID   = P_DATA)         AND
                       (REC.CDFILIAL    = CL.CDFILIAL)    AND
                       (REC.NRLANCTONF  = CL.NRLANCTONF)
                 GROUP BY REC.CDFILIAL, REC.NRLANCTONF
                ) REC,
                (SELECT I.CDFILIAL, I.NRLANCTONF,
                        SUM(DECODE(I.CDENCARGO, '05', I.VRITEMENCA, 0)) AS VRICMSSUBT,
                        SUM(DECODE(I.CDENCARGO, '01', I.VRITEMENCA, 0)) AS VRFRETE,
                        SUM(DECODE(I.CDENCARGO, '02', I.VRITEMENCA, 0)) AS VRSEGUR,
                        SUM(DECODE(I.CDENCARGO, '03', I.VRITEMENCA, 0)) AS VLOUTDESP
                 FROM  NOTAFISCAL N, ITEMENCARGO I
                 WHERE (N.CDFILIRECE   = P_CDFILIAL)              AND
                       (N.NRLANCTONF   = C_NOTAFISCAL.NRLANCTONF) AND
                       (N.IDENTRSAIDOP = P_IDENTRSAIDOP)          AND
                       (N.DTENTRSAID   = P_DATA)                  AND
                       (I.CDFILIAL     = N.CDFILIAL)              AND
                       (I.NRLANCTONF   = N.NRLANCTONF)            AND
                       (I.CDENCARGO IN ('01', '02', '03','05'))
                 GROUP BY I.CDFILIAL, I.NRLANCTONF
                ) ENCARGO,
                (SELECT IT.CDFILIAL, IT.NRLANCTONF, N.NRNOTAFISC AS NRNOTAORIG,
                       DECODe(N.CDSERIEENTR,'',N.CDSERIE,N.CDSERIEENTR) AS CDSERIEORIG
                 FROM  NOTAFISCAL N, ITEMNOTAFISC IT
                 WHERE (((N.IDENTRSAIDOP   = P_IDENTRSAIDOP)          AND
                        (N.CDTIPOOPER NOT IN ('10','11')))     OR
                        (N.IDENTRSAIDOP   = 'S'))                     AND
                        (N.CDFILIAL        = IT.CDFILIALIT)           AND
                        (N.NRLANCTONF      = IT.NRLANCTONFIT)
                ) NFORIG,
                (SELECT NF.CDFILIAL,     NF.NRLANCTONF,   NF.CDFILIRECE,   NF.CDFILIEMIT,
                        NF.CDINSCESTA,   NF.CDEMPRESA,    NF.CDSERIEENTR,  NF.NRNOTAFISC,
                        NF.DTEMISSAO,    NF.DTENTRSAID,   NF.IDENTRSAIDOP, NF.CDTIPOOPER,
                        NF.IDTPIJURNF,   NF.NRINSJURNF,   NF.IDSISTEMA,    NF.IDORIGDESTNF,
                        SUM(ROUND(NF.VRTOTAITEM,2))   AS VRTOTAITEM,
                        NF.CDFISCALIT,
                        SUM(DECODE(NF.VRIMPOSTOIT,0.00,0.00,DECODE(CS.CDTRIBSDX,'01',NF.VRBASECALCIT,0.0))) AS VRBASECALCIT,
                        DECODE(CS.CDTRIBSDX,'01',NF.VRALIQITEM,0.0) AS VRALIQITEM,
                        SUM(DECODE(CS.CDTRIBSDX,'01',NF.VRIMPOSTOIT,0.0))  AS VRIMPOSTOIT,
                        NF.VRNOTAFISC,
                        NF.CDFISCOPERIT, NF.NMRAZSOCFORN,
                        NF.NMFILIAL, NF.NMRAZSOCCLIE, CS.CDTRIBSDX,
                        TD.CDTIPDOCUSAP, TD.CDTIPTRANSAP,
                        '      ' AS CDCLIENTESAP,
                        ' '      AS IDFORMPGTSAP,
                        LPAD(' ',10) ESPACO1, LPAD(' ',14) ESPACO2,
                        NF.NRSEQITPROD, NF.CDEMPRESAP, NF.VRIMPOSIPI, NF.CDPRODUTO,
                        NF.QTITEMPROD, NF.VRUNITITEM, NF.VRDESCITEM
                 FROM TPDOCTRANSAP TD, PRODUTO PR,
                    (SELECT CDEMPRESA, CDINSCESTA, IDENTRSAIDOP, CDTIPOOPER, CDFISCSAP,
                            CDCSTSAP, P.CDARVPROD, CDTRIBSDX
                     FROM CODTRIBSAP C, PRODUTO P
                     WHERE (INSTR(P_CDTIPOOPER, C.CDTIPOOPER) <> 0) AND
                           (C.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                           (C.CDPRODUTO    = P.CDPRODUTO(+))
                    ) CS,
                    (SELECT X.CDFILIAL,     X.NRLANCTONF,  X.NRINSJURNF,   X.CDFILIEMIT,
                            X.CDSERIEENTR,  X.NRNOTAFISC,  ROUND(X.VRTOTAITEM,2) AS VRTOTAITEM,   X.DTEMISSAO,
                            X.VRALIQITEM,   X.CDTIPOOPER,  X.VRIMPOSTOIT,  X.CDFISCOPERIT,
                            X.VRBASECALCIT,X.IDSISTEMA,    X.DTENTRSAID,
                            X.IDTPIJURNF,   X.CDFISCALIT,  X.CDSITUTRIBIT, X.CDPRODUTO,
                            X.IDENTRSAIDOP, X.CDINSCESTA,  X.CDEMPRESA,    X.CDFILIRECE,
                            X.NMRAZSOCFORN, X.NMFILIAL,    X.NMRAZSOCCLIE, X.IDORIGDESTNF,
                            MAX(C.CDARVPROD) AS CDPRODSAP, MAX(T.CDARVPROD) AS CDPRODDOCTR,
                            X.VRNOTAFISC,   X.NRSEQITPROD, X.CDEMPRESAP,   X.VRIMPOSIPI,
                            X.QTITEMPROD, X.VRUNITITEM,    X.VRDESCITEM
                     FROM
                        (SELECT CDEMPRESA , CDINSCESTA, IDENTRSAIDOP, CDTIPOOPER, CDFISCSAP,
                                CDCSTSAP, PC.CDARVPROD
                         FROM CODTRIBSAP CT, PRODUTO PC
                         WHERE (INSTR(P_CDTIPOOPER, CT.CDTIPOOPER) <> 0) AND
                               (CT.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                               (CT.CDPRODUTO    = PC.CDPRODUTO(+))
                        ) C,
                        (SELECT IDENTRSAIDOP, CDTIPOOPER, IDFORMPGTSAP, PT.CDARVPROD
                         FROM  TPDOCTRANSAP TD, PRODUTO PT
                         WHERE (INSTR(P_CDTIPOOPER, TD.CDTIPOOPER) <> 0) AND
                               (TD.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                               (TD.CDPRODUTO   = PT.CDPRODUTO(+))
                        ) T,
                        (SELECT N.CDFILIAL,     N.NRLANCTONF,  N.NRINSJURNF,   N.CDFILIEMIT,
                                N.CDSERIEENTR,  N.NRNOTAFISC,  ROUND(I.VRTOTAITEM,2) AS VRTOTAITEM,   N.DTEMISSAO,
                                VRALIQITEM,
                                N.CDTIPOOPER,
                                VRBASECALCIT,
                                I.CDFISOPEITEM AS CDFISCOPERIT,
                                VRIMPOSTOIT,
                                N.IDSISTEMA,    N.IDENTRSAIDOP,
                                N.DTENTRSAID,   N.IDTPIJURNF,  T.CDFISCALIT,   PR.CDARVPROD AS CDPRODUTO,
                                N.CDINSCESTA,   N.CDEMPRESA,   N.CDFILIRECE,   N.IDORIGDESTNF,
                                F.NMRAZSOCFORN, E.NMFILIAL,    C.NMRAZSOCCLIE, N.VRNOTAFISC,
                                I.NRSEQITPROD,  
                                SUBSTR(I.CDSITUTRIBPR,2,2) AS CDSITUTRIBIT,
                                FL.CDEMPRESAP, T.VRIMPOSIPI, I.QTITEMPROD, I.VRUNITITEM, I.VRDESCITEM
                         FROM NOTAFISCAL N, ITEMPRODUTO I, PRODUTO PR,
                              FORNECEDOR F, CLIENTE C, FILIAL E, FILIAL FL,
                            (SELECT SUM(DECODE(T.CDIMPOSTO, '00',T.VRIMPOSTOIT,0.0)) AS VRIMPOSTOIT,
                                    SUM(DECODE(T.CDIMPOSTO, '10',T.VRIMPOSTOIT,0.0)) AS VRIMPOSIPI,
                                    SUM(DECODE(T.CDIMPOSTO, '00',T.VRBASECALCIT,0.0)) AS VRBASECALCIT,
                                    SUM(DECODE(T.CDIMPOSTO, '00',T.VRALIQITEM,0.0)) AS VRALIQITEM,
                                    MAX(DECODE(T.CDIMPOSTO, '00', T.CDFISCALIT)) AS CDFISCALIT,
                                    T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD
                             FROM NOTAFISCAL N, ITEMPRODUTO I, ITEMPROIMPOS T
                             WHERE
                                (N.CDFILIRECE     = P_CDFILIAL)                              AND
                                (N.NRLANCTONF     = C_NOTAFISCAL.NRLANCTONF)                 AND
                                (N.DTENTRSAID     = P_DATA)                                  AND
                                (N.IDENTRSAIDOP   = P_IDENTRSAIDOP)                          AND
                                (N.IDSTATUS       <> 'C')                                    AND
                                (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)  AND
                                (INSTR('_01_02_03_04_06_08_11_12_13_16_99_',N.CDTIPOOPER) <> 0)    AND
                                (N.CDFILIAL       = I.CDFILIAL)                              AND
                                (N.NRLANCTONF     = I.NRLANCTONF)                            AND
                                (I.CDFILIAL       = T.CDFILIAL)                              AND
                                (I.NRLANCTONF     = T.NRLANCTONF)                            AND
                                (I.NRSEQITPROD    = T.NRSEQITPROD)                           AND
                                (T.NRITPROIMPOS   = '1')                                     AND
                                (T.CDIMPOSTO IN ('00', '10'))
                             GROUP BY T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD
                            ) T
                         WHERE (N.CDFILIRECE     = P_CDFILIAL)                              AND
                               (N.NRLANCTONF     = C_NOTAFISCAL.NRLANCTONF)                 AND
                               (N.IDENTRSAIDOP   = P_IDENTRSAIDOP)                          AND
                               (N.DTENTRSAID     = P_DATA)                                  AND
                               (N.IDSTATUS       <> 'C')                                    AND
                               (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)  AND
                               (INSTR('_01_02_03_04_06_08_11_12_13_16_99_',N.CDTIPOOPER) <> 0)    AND
                               (N.CDFILIAL       = I.CDFILIAL)                              AND
                               (N.NRLANCTONF     = I.NRLANCTONF)                            AND
                               (I.CDFILIAL       = T.CDFILIAL(+))                           AND
                               (I.NRLANCTONF     = T.NRLANCTONF(+))                         AND
                               (I.NRSEQITPROD    = T.NRSEQITPROD(+))                        AND
                               (I.CDPRODUTO      = PR.CDPRODUTO)                            AND
                               (C.CDCLIENTE(+)   = N.CDCLIENTE)                             AND
                               (F.CDFORNECED(+)  = N.CDFORNECED)                            AND
                               (E.CDFILIAL(+)    = N.CDFILIEMIT)                            AND
                               (FL.CDFILIAL(+)   = N.CDFILIRECE)
                        ) X
                     WHERE
                        (X.CDEMPRESA           = C.CDEMPRESA(+))                      AND
                        (X.CDINSCESTA          = C.CDINSCESTA(+))                     AND
                        (X.IDENTRSAIDOP        = C.IDENTRSAIDOP(+))                   AND
                        (X.CDTIPOOPER          = C.CDTIPOOPER(+))                     AND
                        (NVL(X.CDFISCALIT,'3') = C.CDFISCSAP(+))                      AND
                        (X.CDSITUTRIBIT        = C.CDCSTSAP(+))                       AND
                        (X.IDENTRSAIDOP        = T.IDENTRSAIDOP(+))                   AND
                        (X.CDTIPOOPER          = T.CDTIPOOPER(+))                     AND
                        (INSTR('D'||X.CDPRODUTO,'D'||NVL(C.CDARVPROD(+),'')) = 1)     AND
                        (INSTR('D'||X.CDPRODUTO,'D'||NVL(T.CDARVPROD(+),'')) = 1)
                     GROUP BY
                        X.CDFILIAL,     X.NRLANCTONF,  X.NRINSJURNF,   X.CDFILIEMIT,
                        X.CDSERIEENTR,  X.NRNOTAFISC,  X.VRTOTAITEM,   X.DTEMISSAO,
                        X.VRALIQITEM,   X.CDTIPOOPER,  X.VRBASECALCIT, X.CDFISCOPERIT,
                        X.VRIMPOSTOIT,  X.IDSISTEMA,   X.DTENTRSAID,   
                        X.IDTPIJURNF,   X.CDFISCALIT,  X.CDSITUTRIBIT, X.CDPRODUTO,
                        X.IDENTRSAIDOP, X.CDINSCESTA,  X.CDEMPRESA,    X.CDFILIRECE,
                        X.NMRAZSOCFORN, X.NMFILIAL,    X.NMRAZSOCCLIE, X.IDORIGDESTNF,
                        X.VRNOTAFISC,   X.NRSEQITPROD, X.CDEMPRESAP,   X.VRIMPOSIPI,
                        X.QTITEMPROD,   X.VRUNITITEM,  X.VRDESCITEM
                    ) NF
                 WHERE
                    (NF.IDENTRSAIDOP        = TD.IDENTRSAIDOP(+))        AND
                    (NF.CDTIPOOPER          = TD.CDTIPOOPER(+))          AND
                    (NF.CDPRODDOCTR         = TD.CDPRODUTO(+))           AND
                    (NF.CDEMPRESA           = CS.CDEMPRESA(+))           AND
                    (NF.CDINSCESTA          = CS.CDINSCESTA(+))          AND
                    (NF.IDENTRSAIDOP        = CS.IDENTRSAIDOP(+))        AND
                    (NF.CDTIPOOPER          = CS.CDTIPOOPER(+))          AND
                    (NVL(NF.CDFISCALIT,'3') = CS.CDFISCSAP(+))           AND
                    (NF.CDSITUTRIBIT        = CS.CDCSTSAP(+))            AND
                    (NVL(NF.CDPRODSAP,'X')  = NVL(CS.CDARVPROD(+),'X'))  AND
                    (NF.CDPRODUTO           = PR.CDARVPROD)
                 GROUP BY
                     NF.CDFILIAL,     NF.NRLANCTONF,   NF.CDFILIRECE,   NF.CDFILIEMIT,
                     NF.CDINSCESTA,   NF.CDEMPRESA,    NF.CDSERIEENTR,  NF.NRNOTAFISC,
                     NF.DTEMISSAO,    NF.DTENTRSAID,   NF.IDENTRSAIDOP, NF.CDTIPOOPER,
                     NF.IDTPIJURNF,   NF.NRINSJURNF,   NF.IDSISTEMA,    
                     NF.VRALIQITEM,   NF.VRNOTAFISC,   NF.CDFISCOPERIT, NF.NMRAZSOCFORN,
                     NF.CDFISCALIT,   NF.NMFILIAL,     NF.NMRAZSOCCLIE, NF.IDORIGDESTNF,
                     CS.CDTRIBSDX,    TD.CDTIPDOCUSAP, TD.CDTIPTRANSAP, NF.NRSEQITPROD,
                     NF.CDEMPRESAP,   NF.VRIMPOSIPI,   NF.CDPRODUTO,    NF.QTITEMPROD,
                     NF.VRUNITITEM,   NF.VRDESCITEM
                ) NOTA
             WHERE
                (NOTA.CDFILIAL    = ENCARGO.CDFILIAL(+))   AND
                (NOTA.NRLANCTONF  = ENCARGO.NRLANCTONF(+)) AND
                (NOTA.VRNOTAFISC <> 0)                     AND
                (NOTA.CDFILIAL    = REC.CDFILIAL(+))       AND
                (NOTA.NRLANCTONF  = REC.NRLANCTONF(+))     AND
                (NOTA.CDFILIAL    = NFORIG.CDFILIAL(+))    AND
                (NOTA.NRLANCTONF  = NFORIG.NRLANCTONF(+))
             ORDER BY NOTA.IDENTRSAIDOP, NOTA.CDTIPOOPER, NRSEQITPROD)
         LOOP
            V_NRSEQITEM := V_NRSEQITEM + 1;

            /* Insere Itens das notas fiscais */
            INSERT INTO USR_ITEM_RNF
               (CDEMPSAP, FILIAL, NRRNF, CDCGC, CDSER, NRNOT, ITEMNF, TPDOC, VLRNF,
                DTEMI, ALIQU, BICMS, CDCFO, CDTRI, DTVEN, VLIPI, DTRNF, VLICM,
                VLSTR, CDMAT, QTDE, VLUNIT, VLDESC, NFORIGEM, SERORIG, CDRS, DESCPROD)
            VALUES
               (C_NOTAFISCAL.CDEMPRESAP, C_NOTAFISCAL.CDFILIRECE, SUBSTR(V_NUMERORNF,5,6), LPAD(V_NRINJURNF,14, ' '), LPAD(C_ITEMNF.CDSERIE,3, ' '),
                LPAD(C_ITEMNF.NRNOTAFISC,6, '0'), LPAD(TO_CHAR(V_NRSEQITEM),3,'0'), LPAD(C_ITEMNF.CDTIPDOCUSAP,3, ' '),
                NVL(ROUND(C_ITEMNF.VRTOTAITEM,2),0), C_NOTAFISCAL.DTEMISSAO, NVL(C_ITEMNF.VRALIQITEM,0),
                NVL(C_ITEMNF.VRBASECALCIT,0), SUBSTR(C_ITEMNF.CDFISCOPERIT,1,4),
                LPAD(C_ITEMNF.CDTRIBSDX,2,' '), NULL, NVL(C_ITEMNF.VRIMPOSIPI,0), P_DATA,
                NVL(C_ITEMNF.VRIMPOSTOIT,0), 0, LPAD(C_ITEMNF.CDPRODUTO,18, ' '),
                NVL(C_ITEMNF.QTITEMPROD,0), NVL(C_ITEMNF.VRUNITITEM,0),
                NVL(C_ITEMNF.VRDESCITEM,0), LPAD(SUBSTR(C_ITEMNF.NRNOTAORIG,2,6),6,'0'),
                LPAD(C_ITEMNF.CDSERIEORIG,3,' '), LPAD(' ',6, ' '), LPAD(' ',40, ' '));
         END LOOP;
         V_QTDEDOCU := V_QTDEDOCU + 1;
      END LOOP;

      IF V_QTDEDOCU > 0 THEN
         UPDATE USR_HEAD_TRAIL_RNF
         SET QTDDT = LPAD(TO_CHAR(V_QTDEDOCU),6,'0')
         WHERE
            (FILIAL   = P_CDFILIAL)                  AND
            (NRRNF    = SUBSTR(V_NUMERORNF,5,6))     AND
            (DTRNF    = P_DATA)                      AND
            (NOMEA    = V_NMARQUIVO);
      END IF;
   ELSE
      /*  DOCUMENTOS FISCAIS DE SA�DA */
      V_QTDEDOCU := 0;

      FOR C_NOTAFISCAL IN C_NOTAFISCALS LOOP

         IF V_IDCABECCRIADO <> 'S' THEN

            NOVO_CODIGO('NUMERNF/FILIAL'||P_CDFILIAL, V_NUMERORNF, 1);
            V_NUMERORNF := SUBSTR(V_NUMERORNF, 11, 10);

            /* Insere novo n�mero de RNF */
            INSERT INTO NUMERORNF
              (CDFILIAL, DTNUMERNF, IDENTRSAID, IDTIPOOPERNF, NRNUMERNF)
            VALUES
              (P_CDFILIAL, P_DATA, C_NOTAFISCAL.IDENTRSAIDOP, P_IDTIPOOPER, V_NUMERORNF);

            /* Insere cabe�alho da RNF */
            INSERT INTO USR_HEAD_TRAIL_RNF
               (CDEMPSAP, FILIAL, NRRNF, DTRNF, HRRNF, NOMEA, QTDDT)
            VALUES
               (C_NOTAFISCAL.CDEMPRESAP, P_CDFILIAL, SUBSTR(V_NUMERORNF,5,6), P_DATA,
                TO_CHAR(SYSDATE,'HH24:MI'), V_NMARQUIVO, '0');

            V_IDCABECCRIADO := 'S';
         END IF;

         /*  GRAVA RNF NO RMC */
         IF V_IDRMC <> 'S' THEN
            SELECT IDCONSRMCFDIA
              INTO V_IDCONSRMCFDIA
              FROM PARAMFILIAL
             WHERE
               CDFILIAL = P_CDFILIAL;

            IF C_NOTAFISCAL.CDTIPOOPER IN ('01','17') AND (V_IDCONSRMCFDIA = 'S') THEN

               SELECT USR_NRSEQRMC, USR_NRRMC, USR_NRRNF
                 INTO V_NRSEQRMC, V_NRRMC, V_NRRNF
                 FROM USR_RMC01
                WHERE
                  USR_FILIAL         = P_CDFILIAL AND
                  TO_DATE(USR_DTRMC) = P_DATA;

               UPDATE USR_RMC01
                  SET USR_NRRMC =  SUBSTR(V_NUMERORNF,5,6),
                      USR_NRRNF =  SUBSTR(V_NUMERORNF,5,6)
                WHERE
                  USR_FILIAL         = P_CDFILIAL AND
                  TO_DATE(USR_DTRMC) = P_DATA;

               UPDATE USR_RMC02
                  SET USR_NRRMC = SUBSTR(V_NUMERORNF,5,6)
                WHERE
                  USR_FILIAL   = P_CDFILIAL AND
                  USR_NRSEQRMC = V_NRSEQRMC;

               UPDATE USR_RMC03
                  SET USR_NRRMC = SUBSTR(V_NUMERORNF,5,6)
                WHERE
                  USR_FILIAL   = P_CDFILIAL AND
                  USR_NRSEQRMC = V_NRSEQRMC;

               V_IDRMC := 'S';
            END IF;
         END IF;

         /* Inclui ou altera a data e o n�mero da RNF para os documentos */
         UPDATE NOTAFISCAL
         SET DTEXPRNF = P_DATA,
             NREXPRNF = V_NUMERORNF
         WHERE CDFILIAL   = C_NOTAFISCAL.CDFILIAL    AND
               NRLANCTONF = C_NOTAFISCAL.NRLANCTONF;

         IF C_NOTAFISCAL.IDORIGDESTNF = 'L' THEN
            V_NRINJURNF := C_NOTAFISCAL.CDFILIRECE;
         ELSE
            IF C_NOTAFISCAL.IDTPIJURNF = 'I' THEN
               V_NRINJURNF := SUBSTR(C_NOTAFISCAL.CGCFORCLIE,15,6);
            ELSE
               V_NRINJURNF := SUBSTR(C_NOTAFISCAL.CGCFORCLIE,1,14);
            END IF;
         END IF;

         /* Insere Corpo das Notas Fiscais */
         INSERT INTO USR_CORPO_RNF
            (CDEMPSAP, FILIAL, NRRNF, CDCGC, CDSER, NRNOT, VLRNF, DTEMI, DTVEN,
             DTRNF, VLSTR, VLDESC, VLFRETE, VLSEGUR, VLODES, CDCNPJ)
         VALUES
            (C_NOTAFISCAL.CDEMPRESAP, C_NOTAFISCAL.CDFILIEMIT, SUBSTR(V_NUMERORNF,5,6), LPAD(V_NRINJURNF,14, ' '), LPAD(C_NOTAFISCAL.CDSERIE,3, ' '),
             LPAD(C_NOTAFISCAL.NRNOTAFISC,6, '0'), NVL(C_NOTAFISCAL.VRNOTAFISC,0), C_NOTAFISCAL.DTEMISSAO, C_NOTAFISCAL.DTATUAVENREC,
             P_DATA, NVL(C_NOTAFISCAL.VRSUBSTRIB,0), NVL(C_NOTAFISCAL.VRDESCNF,0), NVL(C_NOTAFISCAL.VRFRETE,0),
             NVL(C_NOTAFISCAL.VRSEGUR,0), NVL(C_NOTAFISCAL.VLOUTDESP,0), C_NOTAFISCAL.NRCPFCNPJDFS);

         V_NRSEQITEM := 0;

         FOR C_ITEMNF IN
            (SELECT
                NOTA.CDFILIRECE,   NOTA.CDFILIEMIT,   NOTA.IDORIGDESTNF, NOTA.NMRAZSOCCLIE,
                NOTA.NMRAZSOCFORN, NOTA.NMFILIAL,     NOTA.IDENTRSAIDOP, NOTA.CDTIPOOPER,
                NOTA.DTEMISSAO,    NOTA.DTENTRSAID,   NOTA.VRTOTAITEM,
                NOTA.VRBASECALCIT, NOTA.VRALIQITEM,   NOTA.VRIMPOSTOIT,
                ((NOTA.VRTOTAITEM / NOTA.VRNOTAFISC) * ENCARGO.VRICMSSUBT) AS VRSUBSTRIB,
                NOTA.IDSISTEMA,    NOTA.CDFISCOPERIT, NOTA.CDTRIBSDX,    NOTA.NRLANCTONF,
                NOTA.CDTIPDOCUSAP, NOTA.CDTIPTRANSAP, NOTA.IDTPIJURNF,   NOTA.CDFISCALIT,
                NOTA.NRINSJURNF AS CGCFORCLIE,
                SUBSTR(NOTA.CDSERIE,1,3) AS CDSERIE,
                SUBSTR(NRNOTAFISC,LENGTH(NRNOTAFISC)-5,6) NRNOTAFISC,
                REC.DTATUAVENREC,
                NOTA.CDCLIENTESAP, NOTA.IDFORMPGTSAP,
                NOTA.ESPACO1, NOTA.ESPACO2, NOTA.CDEMPRESAP, VRIMPOSIPI,
                NOTA.CDPRODUTO, SUBSTR(NOTA.NRSEQITPROD,2,3) AS NRSEQITPROD,
                NOTA.QTITEMPROD, NOTA.VRUNITITEM, NOTA.VRDESCITEM,
                ENCARGO.VRFRETE, ENCARGO.VRSEGUR, ENCARGO.VLOUTDESP,
                NFORIG.NRNOTAORIG, NFORIG.CDSERIEORIG, NOTA.CDSERVFILI, NOTA.DSPRODEMIS,
                NOTA.NRSEQITPROD as NRSEQPRODIT
              FROM
                 (SELECT REC.CDFILIAL, REC.NRLANCTONF,
                         MAX(REC.DTATUAVENREC) DTATUAVENREC
                  FROM  NOTAFISCAL CL, CONTARECEBER REC
                  WHERE (CL.CDFILIEMIT   = P_CDFILIAL)     AND
                        (CL.IDENTRSAIDOP = P_IDENTRSAIDOP) AND
                        (CL.IDSTATUS     IN ('A','E'))     AND
                        (CL.DTENTRSAID   = P_DATA)         AND
                        (REC.CDFILIAL    = CL.CDFILIAL)    AND
                        (REC.NRLANCTONF  = CL.NRLANCTONF)
                  GROUP BY REC.CDFILIAL, REC.NRLANCTONF
                 ) REC,
                 (SELECT I.CDFILIAL, I.NRLANCTONF,
                         SUM(DECODE(I.CDENCARGO, '05', I.VRITEMENCA, 0)) AS VRICMSSUBT,
                         SUM(DECODE(I.CDENCARGO, '01', I.VRITEMENCA, 0)) AS VRFRETE,
                         SUM(DECODE(I.CDENCARGO, '02', I.VRITEMENCA, 0)) AS VRSEGUR,
                         SUM(DECODE(I.CDENCARGO, '03', I.VRITEMENCA, 0)) AS VLOUTDESP
                  FROM  NOTAFISCAL N, ITEMENCARGO I
                  WHERE (N.CDFILIRECE   = P_CDFILIAL)              AND
                        (N.NRLANCTONF   = C_NOTAFISCAL.NRLANCTONF) AND
                        (N.IDENTRSAIDOP = 'S')                     AND
                        (N.DTENTRSAID   = P_DATA)                  AND
                        (I.CDFILIAL     = N.CDFILIAL)              AND
                        (I.NRLANCTONF   = N.NRLANCTONF)            AND
                        (I.CDENCARGO IN ('01', '02', '03','05'))
                  GROUP BY I.CDFILIAL, I.NRLANCTONF
                 ) ENCARGO,

                 (SELECT IT.CDFILIAL, IT.NRLANCTONF, N.NRNOTAFISC AS NRNOTAORIG,
                         DECODE(N.CDSERIEENTR,'',N.CDSERIE, N.CDSERIEENTR) AS CDSERIEORIG
                  FROM  NOTAFISCAL N, ITEMNOTAFISC IT
                  WHERE (((N.IDENTRSAIDOP = 'E')                     AND
                         (N.CDTIPOOPER <> '11'))                      OR
                         (N.IDENTRSAIDOP  = P_IDENTRSAIDOP))         AND
                        (N.CDFILIAL       = IT.CDFILIALIT)           AND
                        (N.NRLANCTONF     = IT.NRLANCTONFIT)
                 ) NFORIG,
                 (SELECT NF.CDFILIAL,     NF.NRLANCTONF,   NF.CDFILIRECE,   NF.CDFILIEMIT,
                         NF.CDINSCESTA,   NF.CDEMPRESA,    NF.CDSERIE,      NF.NRNOTAFISC,
                         NF.DTEMISSAO,    NF.DTENTRSAID,   NF.IDENTRSAIDOP, NF.CDTIPOOPER,
                         NF.IDTPIJURNF,   NF.NRINSJURNF,   NF.IDSISTEMA,    NF.IDORIGDESTNF,
                         SUM(ROUND(NF.VRTOTAITEM,2))   AS VRTOTAITEM,
                         NF.CDFISCALIT,
                         SUM(DECODE(NF.VRIMPOSTOIT,0.00,0.00,DECODE(CS.CDTRIBSDX,'01',NF.VRBASECALCIT,0.0))) AS VRBASECALCIT,
                         DECODE(CS.CDTRIBSDX,'01',NF.VRALIQITEM,0.0) AS VRALIQITEM,
                         SUM(DECODE(CS.CDTRIBSDX,'01',NF.VRIMPOSTOIT,0.0))  AS VRIMPOSTOIT,
                         NF.VRNOTAFISC,
                         NF.CDFISCOPERIT, NF.NMRAZSOCFORN, NF.NMFILIAL, NF.NMRAZSOCCLIE,
                         CS.CDTRIBSDX,    TD.CDTIPDOCUSAP, TD.CDTIPTRANSAP,
                         CL.CDCLIENTESAP, NF.IDFORMPGTSAP,
                         LPAD(' ',10) ESPACO1, LPAD(' ',14) ESPACO2,
                         NF.NRSEQITPROD, NF.CDEMPRESAP, NF.VRIMPOSIPI, NF.CDPRODUTO,
                         NF.QTITEMPROD, NF.VRUNITITEM, NF.VRDESCITEM, NF.CDSERVFILI, NF.DSPRODEMIS
                  FROM TPDOCTRANSAP TD, PRODUTO PR,
                     (SELECT CDEMPRESA, CDINSCESTA, IDENTRSAIDOP, CDTIPOOPER, CDFISCSAP,
                             CDCSTSAP, P.CDARVPROD, CDTRIBSDX
                      FROM CODTRIBSAP CT, PRODUTO P
                      WHERE
                         (INSTR(P_CDTIPOOPER, CT.CDTIPOOPER) <> 0) AND
                         (CT.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                         (CT.CDPRODUTO    = P.CDPRODUTO(+))
                     ) CS,
                     (SELECT IDENTRSAIDOP, CDTIPOOPER, P.CDARVPROD,
                             IDFORMPGTSAP, CDFILIAL, CDCLIENTESAP
                      FROM CLIEFILISAP CL, PRODUTO P
                      WHERE
                         (INSTR(P_CDTIPOOPER, CL.CDTIPOOPER) <> 0) AND
                         (CL.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                         (CL.CDPRODUTO    = P.CDPRODUTO(+))
                     ) CL,
                     (SELECT X.CDFILIAL,     X.NRLANCTONF,  X.NRINSJURNF,   X.CDFILIEMIT,
                             X.CDSERIE,      X.NRNOTAFISC,  ROUND(X.VRTOTAITEM,2) AS VRTOTAITEM,   X.DTEMISSAO,
                             X.VRALIQITEM,   X.CDTIPOOPER,  X.VRBASECALCIT,  X.CDFISCOPERIT,
                             X.VRIMPOSTOIT, X.IDSISTEMA,    X.DTENTRSAID,
                             X.IDTPIJURNF,   X.CDFISCALIT,  X.CDSITUTRIBIT, X.CDPRODUTO,
                             X.IDENTRSAIDOP, X.CDINSCESTA,  X.CDEMPRESA,    X.CDFILIRECE,
                             X.NMRAZSOCFORN, X.NMFILIAL,    X.NMRAZSOCCLIE, X.IDORIGDESTNF,
                             MAX(T.IDFORMPGTSAP) AS IDFORMPGTSAP,
                             MAX(C.CDARVPROD)    AS CDPRODSAP,
                             MAX(T.CDARVPROD)    AS CDPRODDOCTR,
                             X.VRNOTAFISC,   X.NRSEQITPROD,
                             X.CDEMPRESAP, VRIMPOSIPI, X.QTITEMPROD,
                             X.VRUNITITEM, X.VRDESCITEM, X.CDSERVFILI, X.DSPRODEMIS
                      FROM
                         (SELECT CDEMPRESA , CDINSCESTA, IDENTRSAIDOP, CDTIPOOPER, CDFISCSAP,
                                 CDCSTSAP, PC.CDARVPROD
                          FROM CODTRIBSAP CT, PRODUTO PC
                          WHERE (INSTR(P_CDTIPOOPER, CT.CDTIPOOPER) <> 0) AND
                                (CT.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                                (CT.CDPRODUTO    = PC.CDPRODUTO(+))
                         ) C,
                         (SELECT IDENTRSAIDOP, CDTIPOOPER, IDFORMPGTSAP, PT.CDARVPROD
                          FROM  TPDOCTRANSAP TD, PRODUTO PT
                          WHERE (INSTR(P_CDTIPOOPER, TD.CDTIPOOPER) <> 0) AND
                                (TD.IDENTRSAIDOP = P_IDENTRSAIDOP)        AND
                                (TD.CDPRODUTO    = PT.CDPRODUTO(+))
                         ) T,
                         (SELECT N.CDFILIAL,     N.NRLANCTONF,  N.NRINSJURNF,   N.CDFILIEMIT,
                                 N.CDSERIE,      N.NRNOTAFISC,  ROUND(I.VRTOTAITEM,2) AS VRTOTAITEM,   N.DTEMISSAO,
                                 VRALIQITEM,
                                 N.CDTIPOOPER,
                                 VRBASECALCIT,
                                 I.CDFISOPEITEM AS CDFISCOPERIT,
                                 VRIMPOSTOIT,
                                 N.IDSISTEMA,    N.IDENTRSAIDOP,
                                 N.DTENTRSAID,   N.IDTPIJURNF,  T.CDFISCALIT,   PR.CDARVPROD AS CDPRODUTO,
                                 N.CDINSCESTA,   N.CDEMPRESA,   N.CDFILIRECE,   N.IDORIGDESTNF,
                                 F.NMRAZSOCFORN, E.NMFILIAL,    C.NMRAZSOCCLIE,
                                 SUBSTR(I.CDSITUTRIBPR,2,2) AS CDSITUTRIBIT,
                                 NVL(P.IDFORMPGTSAP,'V')    AS IDFORMPGTSAP,
                                 N.VRNOTAFISC, I.NRSEQITPROD,
                                 FL.CDEMPRESAP, T.VRIMPOSIPI, I.QTITEMPROD,
                                 I.VRUNITITEM, I.VRDESCITEM,
                                 I.CDSERVFILI, I.DSPRODEMIS
                          FROM
                             NOTAFISCAL N, ITEMPRODUTO I, PRODUTO PR,
                             CLIENTE C, FORNECEDOR F, FILIAL E, FILIAL FL,
                             (SELECT B.CDFILIAL, B.NRLANCTONF,
                                     DECODE(MAX(B.DTATUAVENREC) - DTENTRSAID,0,'V','P') AS IDFORMPGTSAP
                              FROM  NOTAFISCAL L, CONTARECEBER B
                              WHERE (L.CDFILIEMIT      = P_CDFILIAL)     AND
                                    (L.IDENTRSAIDOP    = P_IDENTRSAIDOP) AND
                                    (L.DTENTRSAID      = P_DATA)         AND
                                    (B.CDFILIAL        = L.CDFILIAL)     AND
                                    (B.NRLANCTONF      = L.NRLANCTONF)
                              GROUP BY B.CDFILIAL, B.NRLANCTONF, DTENTRSAID
                             ) P,
                             (SELECT SUM(DECODE(T.CDIMPOSTO, '00',T.VRIMPOSTOIT,0.0)) AS VRIMPOSTOIT,
                                     SUM(DECODE(T.CDIMPOSTO, '10',T.VRIMPOSTOIT, 0.0)) AS VRIMPOSIPI,
                                     SUM(DECODE(T.CDIMPOSTO, '00',T.VRBASECALCIT,0.0)) AS VRBASECALCIT,
                                     SUM(DECODE(T.CDIMPOSTO, '00',T.VRALIQITEM,0.0)) AS VRALIQITEM,
                                     MAX(DECODE(T.CDIMPOSTO, '00', T.CDFISCALIT)) AS CDFISCALIT,
                                     T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD
                              FROM NOTAFISCAL N, ITEMPRODUTO I, ITEMPROIMPOS T
                              WHERE (N.CDFILIEMIT     = P_CDFILIAL)                                          AND
                                    (N.NRLANCTONF     = C_NOTAFISCAL.NRLANCTONF)                             AND
                                    (N.IDENTRSAIDOP   = P_IDENTRSAIDOP)                                      AND
                                    (N.DTENTRSAID     = P_DATA)                                              AND
                                    (N.IDSTATUS      IN ('A','E'))                                           AND
                                    (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)    AND
                                    (INSTR('_01_02_03_04_06_07_08_17_99_',N.CDTIPOOPER) <> 0)                AND
                                    (N.CDFILIAL       = I.CDFILIAL)                                          AND
                                    (N.NRLANCTONF     = I.NRLANCTONF)                                        AND
                                    (I.CDFILIAL       = T.CDFILIAL)                                          AND
                                    (I.NRLANCTONF     = T.NRLANCTONF)                                        AND
                                    (I.NRSEQITPROD    = T.NRSEQITPROD)                                       AND
                                    (T.NRITPROIMPOS   = '1')                                                 AND
                                    (T.CDIMPOSTO IN ('00', '10'))
                              GROUP BY T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD
                             ) T
                          WHERE (N.CDFILIEMIT      = P_CDFILIAL)                                         AND
                                (N.NRLANCTONF      = C_NOTAFISCAL.NRLANCTONF)                            AND
                                (N.IDENTRSAIDOP    = 'S')                                                AND
                                (N.DTENTRSAID      = P_DATA)                                             AND
                                (N.IDSTATUS      IN ('A','E'))                                           AND
                                (N.IDSISTEMA NOT IN ('PAG','REC','FIS','CMP') OR N.IDSISTEMA IS NULL)    AND
                                (INSTR('_01_02_03_04_06_07_08_17_99_',N.CDTIPOOPER) <> 0)                    AND
                                (N.CDFILIAL        = I.CDFILIAL)                                         AND
                                (N.NRLANCTONF      = I.NRLANCTONF)                                       AND
                                (I.CDFILIAL        = T.CDFILIAL(+))                                      AND
                                (I.NRLANCTONF      = T.NRLANCTONF(+))                                    AND
                                (I.NRSEQITPROD     = T.NRSEQITPROD(+))                                   AND
                                (I.CDPRODUTO       = PR.CDPRODUTO)                                       AND
                                (N.CDCLIENTE       = C.CDCLIENTE(+))                                     AND
                                (N.CDFORNECED      = F.CDFORNECED(+))                                    AND
                                (N.CDFILIRECE      = E.CDFILIAL(+))                                      AND
                                (N.CDFILIEMIT      = FL.CDFILIAL(+))                                     AND
                                (N.CDFILIAL        = P.CDFILIAL(+))                                      AND
                                (N.NRLANCTONF      = P.NRLANCTONF(+))                                    AND
                                (I.CDPRODUTO       = PR.CDPRODUTO)
                         ) X
                      WHERE (X.CDEMPRESA           = C.CDEMPRESA(+))                                         AND
                            (X.CDINSCESTA          = C.CDINSCESTA(+))                                        AND
                            (X.IDENTRSAIDOP        = C.IDENTRSAIDOP(+))                                      AND
                            (DECODE(X.CDTIPOOPER,'06',DECODE(C_NOTAFISCAL.CDTIPOPEORIG,'16','16',X.CDTIPOOPER),X.CDTIPOOPER) = C.CDTIPOOPER(+)) AND
                            (NVL(X.CDFISCALIT,'3') = C.CDFISCSAP(+))                                         AND
                            (X.CDSITUTRIBIT        = C.CDCSTSAP(+))                                          AND
                            (X.IDENTRSAIDOP        = T.IDENTRSAIDOP(+))                                      AND
                            (DECODE(X.CDTIPOOPER,'06',DECODE(C_NOTAFISCAL.CDTIPOPEORIG,'16','16',X.CDTIPOOPER),X.CDTIPOOPER) = T.CDTIPOOPER(+)) AND
                            (DECODE(T.IDFORMPGTSAP(+), 'N', 'N', X.IDFORMPGTSAP) = T.IDFORMPGTSAP(+))        AND
                            (INSTR('D'||X.CDPRODUTO,'D'||NVL(C.CDARVPROD(+),'')) = 1)                        AND
                            (INSTR('D'||X.CDPRODUTO,'D'||NVL(T.CDARVPROD(+),'')) = 1)
                      GROUP BY
                         X.CDFILIAL,     X.NRLANCTONF,  X.NRINSJURNF,   X.CDFILIEMIT,
                         X.CDSERIE,      X.NRNOTAFISC,  X.VRTOTAITEM,   X.DTEMISSAO,
                         X.VRALIQITEM,   X.CDTIPOOPER,  X.VRBASECALCIT, X.CDFISCOPERIT,
                         X.VRIMPOSTOIT ,X.IDSISTEMA,    X.DTENTRSAID,
                         X.IDTPIJURNF,   X.CDFISCALIT,  X.CDSITUTRIBIT, X.CDPRODUTO,
                         X.IDENTRSAIDOP, X.CDINSCESTA,  X.CDEMPRESA,    X.CDFILIRECE,
                         X.NMRAZSOCFORN, X.NMFILIAL,    X.NMRAZSOCCLIE, X.IDORIGDESTNF,
                         X.VRNOTAFISC,   X.NRSEQITPROD, X.CDEMPRESAP,   X.VRIMPOSIPI,
                         X.QTITEMPROD,   X.VRUNITITEM,  X.VRDESCITEM,
                         X.CDSERVFILI,   X.DSPRODEMIS
                     ) NF
                  WHERE (NF.IDENTRSAIDOP        = TD.IDENTRSAIDOP(+))         AND
                        (DECODE(NF.CDTIPOOPER,'06',DECODE(C_NOTAFISCAL.CDTIPOPEORIG,'16','16',NF.CDTIPOOPER),NF.CDTIPOOPER) = TD.CDTIPOOPER(+)) AND
                        (NF.CDPRODDOCTR         = TD.CDPRODUTO(+))            AND
                        (NF.IDFORMPGTSAP        = TD.IDFORMPGTSAP(+))         AND
                        (NF.IDENTRSAIDOP        = CL.IDENTRSAIDOP(+))         AND
                        (DECODE(NF.CDTIPOOPER,'06',DECODE(C_NOTAFISCAL.CDTIPOPEORIG,'16','16',NF.CDTIPOOPER),NF.CDTIPOOPER) = CL.CDTIPOOPER(+)) AND
                        (NF.CDPRODDOCTR         = CL.CDARVPROD(+))            AND
                        (NF.IDFORMPGTSAP        = CL.IDFORMPGTSAP(+))         AND
                        (NF.CDFILIEMIT          = CL.CDFILIAL(+))             AND
                        (NF.CDEMPRESA           = CS.CDEMPRESA(+))            AND
                        (NF.CDINSCESTA          = CS.CDINSCESTA(+))           AND
                        (NF.IDENTRSAIDOP        = CS.IDENTRSAIDOP(+))         AND
                        (DECODE(NF.CDTIPOOPER,'06',DECODE(C_NOTAFISCAL.CDTIPOPEORIG,'16','16',NF.CDTIPOOPER),NF.CDTIPOOPER) = CS.CDTIPOOPER(+)) AND
                        (NVL(NF.CDFISCALIT,'3') = CS.CDFISCSAP(+))            AND
                        (NF.CDSITUTRIBIT        = CS.CDCSTSAP(+))             AND
                        (NVL(NF.CDPRODSAP,'X')  = NVL(CS.CDARVPROD(+),'X'))   AND
                        (NF.CDPRODUTO           = PR.CDARVPROD)
                  GROUP BY
                     NF.CDFILIAL,     NF.NRLANCTONF,   NF.CDFILIRECE,   NF.CDFILIEMIT,
                     NF.CDINSCESTA,   NF.CDEMPRESA,    NF.CDSERIE,      NF.NRNOTAFISC,
                     NF.DTEMISSAO,    NF.DTENTRSAID,   NF.IDENTRSAIDOP, NF.CDTIPOOPER,
                     NF.IDTPIJURNF,   NF.NRINSJURNF,   NF.IDSISTEMA,
                     NF.VRALIQITEM,   NF.VRNOTAFISC,   NF.CDFISCOPERIT, NF.NMRAZSOCFORN,
                     NF.CDFISCALIT,   NF.NMFILIAL,     NF.NMRAZSOCCLIE, NF.IDORIGDESTNF,
                     NF.IDFORMPGTSAP, CS.CDTRIBSDX,    TD.CDTIPDOCUSAP, TD.CDTIPTRANSAP,
                     CL.CDCLIENTESAP, NF.NRSEQITPROD,  NF.CDEMPRESAP,   NF.VRIMPOSIPI,
                     NF.CDPRODUTO,    NF.QTITEMPROD,   NF.VRUNITITEM,   NF.VRDESCITEM,
                     NF.CDSERVFILI,   NF.DSPRODEMIS
                 ) NOTA
             WHERE(NOTA.CDFILIAL    = ENCARGO.CDFILIAL(+))   AND
                  (NOTA.NRLANCTONF  = ENCARGO.NRLANCTONF(+)) AND
                  (NOTA.VRNOTAFISC <> 0)                     AND
                  (NOTA.CDFILIAL    = REC.CDFILIAL(+))       AND
                  (NOTA.NRLANCTONF  = REC.NRLANCTONF(+))     AND
                  (NOTA.CDFILIAL    = NFORIG.CDFILIAL(+))    AND
                  (NOTA.NRLANCTONF  = NFORIG.NRLANCTONF(+))
             ORDER BY NOTA.IDENTRSAIDOP, NOTA.CDTIPOOPER, NRSEQITPROD)
         LOOP

            V_NRSEQITEM := V_NRSEQITEM + 1;

            IF P_IDTIPOOPER = 'P' THEN
               SELECT COUNT(*) AS QTDECDRS
               INTO V_QTDECDRS
               FROM RELAT_CDRS
               WHERE CDFILIAL   = C_ITEMNF.CDFILIEMIT  AND
                     NRLANCTONF = C_ITEMNF.NRLANCTONF  AND
                     CDPRODUTO  = C_ITEMNF.CDPRODUTO   AND
                     CDSERVFILI = C_ITEMNF.CDSERVFILI;

               IF V_QTDECDRS > 0 THEN
                  FOR C_CDRS IN
                     (SELECT SUM(QTEFETIVOS01+QTEFETIVOS02+QTEFETIVOS03+QTEFETIVOS04+QTEFETIVOS05+QTEFETIVOS06+
                     	     QTEFETIVOS07+QTEFETIVOS08+QTEFETIVOS09+QTEFETIVOS10+QTEFETIVOS11+QTEFETIVOS12+
                             QTEFETIVOS13+QTEFETIVOS14+QTEFETIVOS15+QTEFETIVOS16) AS QTDETOTAL, NRCDRS
                        FROM RELAT_CDRS R
                       WHERE (R.CDFILIAL   = C_ITEMNF.CDFILIEMIT)
                         AND (R.NRLANCTONF = C_ITEMNF.NRLANCTONF)
                         AND (R.CDPRODUTO  = C_ITEMNF.CDPRODUTO)
                         AND (R.CDSERVFILI = C_ITEMNF.CDSERVFILI)
                       GROUP BY NRCDRS
                      )
                   LOOP
                      V_IDCDRS := 'S';

                      /* Insere Itens das notas fiscais */
                      INSERT INTO USR_ITEM_RNF
                         (CDEMPSAP, FILIAL, NRRNF, CDCGC, CDSER, NRNOT, ITEMNF, TPDOC, VLRNF,
                          DTEMI, ALIQU, BICMS, CDCFO, CDTRI, DTVEN, VLIPI, DTRNF, VLICM,
                          VLSTR, CDMAT, QTDE, VLUNIT, VLDESC, NFORIGEM, SERORIG, CDRS, DESCPROD)
                      VALUES
                         (C_NOTAFISCAL.CDEMPRESAP, C_NOTAFISCAL.CDFILIEMIT, SUBSTR(V_NUMERORNF,5,6), LPAD(V_NRINJURNF,14, ' '), LPAD(C_ITEMNF.CDSERIE,3, ' '),
                          LPAD(C_ITEMNF.NRNOTAFISC,6, '0'), LPAD(TO_CHAR(V_NRSEQITEM),3,'0'), LPAD(C_ITEMNF.CDTIPDOCUSAP,3, ' '),
                          NVL(C_CDRS.QTDETOTAL * C_ITEMNF.VRUNITITEM,0), C_NOTAFISCAL.DTEMISSAO, NVL(C_ITEMNF.VRALIQITEM,0),
                          NVL(C_ITEMNF.VRBASECALCIT,0), SUBSTR(C_ITEMNF.CDFISCOPERIT,1,4),
                          LPAD(C_ITEMNF.CDTRIBSDX,2,' '), C_NOTAFISCAL.DTATUAVENREC, NVL(C_ITEMNF.VRIMPOSIPI,0), P_DATA,
                          NVL(C_ITEMNF.VRIMPOSTOIT,0), 0, LPAD(C_ITEMNF.CDPRODUTO,18, ' '),
                          NVL(C_CDRS.QTDETOTAL,0), NVL(C_ITEMNF.VRUNITITEM,0),
                          NVL(C_ITEMNF.VRDESCITEM,0), LPAD(SUBSTR(C_ITEMNF.NRNOTAORIG,2,6),6,'0'),
                          LPAD(C_ITEMNF.CDSERIEORIG,3,' '), SUBSTR(C_CDRS.NRCDRS,5,6), RPAD(C_ITEMNF.DSPRODEMIS,40, ' '));

                      V_NRSEQITEM := V_NRSEQITEM + 1;
                   END LOOP;
               ELSE
                  V_IDCDRS := 'N';
               END IF;
            END IF;

            IF V_IDCDRS = 'S' THEN
               V_NRSEQITEM := V_NRSEQITEM - 1;
            END IF;

            IF V_IDCDRS = 'N' THEN
               /* Insere Itens das notas fiscais */
               INSERT INTO USR_ITEM_RNF
                  (CDEMPSAP, FILIAL, NRRNF, CDCGC, CDSER, NRNOT, ITEMNF, TPDOC, VLRNF,
                   DTEMI, ALIQU, BICMS, CDCFO, CDTRI, DTVEN, VLIPI, DTRNF, VLICM,
                   VLSTR, CDMAT, QTDE, VLUNIT, VLDESC, NFORIGEM, SERORIG, CDRS, DESCPROD)
               VALUES
                  (C_NOTAFISCAL.CDEMPRESAP, C_NOTAFISCAL.CDFILIEMIT, SUBSTR(V_NUMERORNF,5,6), LPAD(V_NRINJURNF,14, ' '), LPAD(C_ITEMNF.CDSERIE,3, ' '),
                   LPAD(C_ITEMNF.NRNOTAFISC,6, '0'), LPAD(TO_CHAR(V_NRSEQITEM),3,'0'), LPAD(C_ITEMNF.CDTIPDOCUSAP,3, ' '),
                   NVL(ROUND(C_ITEMNF.VRTOTAITEM,2),0), C_NOTAFISCAL.DTEMISSAO, NVL(C_ITEMNF.VRALIQITEM,0),
                   NVL(C_ITEMNF.VRBASECALCIT,0), SUBSTR(C_ITEMNF.CDFISCOPERIT,1,4),
                   LPAD(C_ITEMNF.CDTRIBSDX,2,' '), C_NOTAFISCAL.DTATUAVENREC, NVL(C_ITEMNF.VRIMPOSIPI,0), P_DATA,
                   NVL(C_ITEMNF.VRIMPOSTOIT,0), 0, LPAD(C_ITEMNF.CDPRODUTO,18, ' '),
                   NVL(C_ITEMNF.QTITEMPROD,0), NVL(C_ITEMNF.VRUNITITEM,0),
                   NVL(C_ITEMNF.VRDESCITEM,0), LPAD(SUBSTR(C_ITEMNF.NRNOTAORIG,2,6),6,'0'),
                   LPAD(C_ITEMNF.CDSERIEORIG,3,' '), LPAD(' ',6, ' '), LPAD(' ',40, ' '));
            END IF;
         END LOOP;
         V_QTDEDOCU := V_QTDEDOCU + 1;
      END LOOP;

      IF V_QTDEDOCU > 0 THEN
         UPDATE USR_HEAD_TRAIL_RNF
         SET QTDDT = LPAD(TO_CHAR(V_QTDEDOCU),6,'0')
         WHERE
            (FILIAL   = P_CDFILIAL)                  AND
            (NRRNF    = SUBSTR(V_NUMERORNF,5,6))     AND
            (DTRNF    = P_DATA)                      AND
            (NOMEA    = V_NMARQUIVO);
      END IF;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('EXPORTA_RNF','5.45.XXX'); END;
/

