--@C:\Web_411\Srv\Src\Cli\SAP\SQL\0009_SAPSTPO_USR_EXPORTA_DADOS.sql

CREATE OR REPLACE FUNCTION USR_PICTPESS
(P_IDPESSOA  IN NOTAFISCAL.IDTPIJURNF%TYPE,
 P_INSCRICAO IN NOTAFISCAL.NRINSJURNF%TYPE) RETURN VARCHAR
AS
   V_AUXILIAR VARCHAR2(20);
BEGIN
   V_AUXILIAR := P_INSCRICAO;
   IF P_IDPESSOA = 'J' THEN
      V_AUXILIAR := SUBSTR(P_INSCRICAO,1,2)||'.'||SUBSTR(P_INSCRICAO,3,3)||'.'||SUBSTR(P_INSCRICAO,6,3)||'/'||SUBSTR(P_INSCRICAO,9,4)||'-'||SUBSTR(P_INSCRICAO,13,2);
   ELSIF P_IDPESSOA = 'F' THEN
      V_AUXILIAR := SUBSTR(P_INSCRICAO,1,3)||'.'||SUBSTR(P_INSCRICAO,4,3)||'.'||SUBSTR(P_INSCRICAO,7,3)||'-'||SUBSTR(P_INSCRICAO,10,2);
   END IF;
   RETURN(V_AUXILIAR);
END;
/

CREATE OR REPLACE FUNCTION USR_CODIGOINTEGRACAO
(P_CDPRODUTO IN PRODUTO.CDPRODUTO%TYPE) RETURN VARCHAR
AS
   V_AUXILIAR   VARCHAR2(20);
   V_CDPRODESTO VARCHAR2(20);
BEGIN
   BEGIN
      SELECT CDPRODINTE, CDPRODESTO
      INTO V_AUXILIAR, V_CDPRODESTO
      FROM PRODUTO
      WHERE CDPRODUTO = P_CDPRODUTO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   IF V_AUXILIAR IS NULL THEN
      BEGIN
         SELECT CDPRODINTE
         INTO V_AUXILIAR
         FROM PRODUTO
         WHERE CDPRODUTO = V_CDPRODESTO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            NULL;
      END;
   END IF;

   IF V_AUXILIAR IS NULL THEN
      V_AUXILIAR := P_CDPRODUTO;
   END IF;

   RETURN(V_AUXILIAR);
END;
/

CREATE OR REPLACE PROCEDURE USR_EXPORTA_DADOS
(P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
 P_DTEXPORTACAO IN NOTAFISCAL.DTENTRSAID%TYPE,
 P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
 P_IDRELATORIO  IN CHAR DEFAULT 'N',
 P_DSTIPOEXP    IN ARQMAGNETICO.DSREGARQMAG%TYPE DEFAULT '_0_1_2_3_')

IS

   V_DSREGARQMAG ARQMAGNETICO.DSREGARQMAG%TYPE;

   V_IDVALORIMP  CHAR(1) := 'I';
   V_IDPERCALIQ  CHAR(1) := 'A';
   V_CDFISCALIP  CHAR(1) := '1';

   V_IDICMSSUBS  CHAR(2) := '05';
   V_IDOUTRDESP  CHAR(2) := '04';
   V_IDSEGURO    CHAR(2) := '02';
   V_IDFRETE     CHAR(2) := '01';
   V_IDTOTICMS   CHAR(2) := '00';
   V_IDTOTIPI    CHAR(2) := '10';
   V_IDTOTICMSS  CHAR(2) := '90';
   V_IDDESCONTO  CHAR(2) := '03';

   V_IDENCADESC  CHAR(1) := 'D';
   V_IDENCAACRE  CHAR(1) := 'A';

   V_IDPGTOFRETE CHAR(1) := '1';
   V_IDFRETECIF  CHAR(1) := 'C';
   V_IDFRETEFOB  CHAR(1) := '1';

   V_IDENTRSAID  CHAR(1) := 'E';
   V_DSENTRADA   CHAR(1) := '1';
   V_DSSAIDA     CHAR(1) := '2';

   V_SIM         CHAR(1) := 'S';
   V_NAO         CHAR(1) := 'N';

   V_CDFILIAL    ITEMPRODUTO.CDFILIAL%TYPE;
   V_NRLANCTONF  ITEMPRODUTO.NRLANCTONF%TYPE;
   V_NRSEQITPROD ITEMPRODUTO.NRSEQITPROD%TYPE;
   V_CFOPITEMNF  ITEMPRODUTO.CDFISOPEITEM%TYPE;

   V_NRAUTOFORN  NFRELACAF.NRAUTOFORN%TYPE;

   V_NRORDREGARQ NUMBER;

   V_IDINCICMS   CHAR(1) := 'S';
   V_IDINCIPI    CHAR(1) := 'S';

   V_IDTIPODOCU  CHAR(4);

   V_DSNFORIGEM  VARCHAR2(200);

   CURSOR C_NOTAFISCAL_ENTRADA IS
   SELECT N.CDFILIAL,
          N.NRLANCTONF,
          N.CDTIPOOPER,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDICMSSUBS),0) AS VRICMSSUBS,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDOUTRDESP),0) AS VROUTRDESP,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDSEGURO  ),0) AS VRSEGURO,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDFRETE   ),0) AS VRFRETE,
          NVL(VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, V_IDTOTICMS, V_IDVALORIMP, V_CDFISCALIP),0) AS VRTOTICMS,
          NVL(VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, V_IDTOTIPI, V_IDVALORIMP, V_CDFISCALIP),0) AS VRTOTIPI,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCADESC, V_IDDESCONTO),0) AS VRDESCONTO,
          N.VRNOTAFISC,
          N.VRPRODUTOS,
          N.DTENTRSAID,
          N.DSQTDEVOLU,
          N.DSESPECVOLU,
          N.DSMARCAVOLU,
          O.NMOPERADOR,
          N.DTEMISSAO,
          (SELECT E.SGESTADO
           FROM ENDEFORN E
           WHERE E.CDFORNECED = N.CDFORNECED) AS SGESTAMOVI,
          USR_PICTPESS(N.IDTPIJURNF, N.NRINSJURNF) AS NRINSJURNF,
          N.NRNOTAFISC,
          N.CDFILIRECE AS CDFILIMOVI,
          T.CDEXTIPOOPER,
          T.IDUTILVENC,
          N.NRPLACAVEIC,
          N.SGUFVEIC,
          DECODE(N.IDPGTOFRETE, V_IDPGTOFRETE, V_IDFRETECIF, V_IDFRETEFOB) AS IDPGTOFRETE,
          N.CDSERIEENTR AS CDSERIENF,
          NVL(F.NMRAZSOCFORN,FL.NMRAZSOCFILI) AS NMRAZSOCFORN,
          V_DSENTRADA AS IDENTRSAIDOP,
          ' ' || CDFILIEMIT AS CDFILITRANS
   FROM NOTAFISCAL N, TIPOOPERAC T, OPERADOR O, FORNECEDOR F, FILIAL FL
   WHERE N.CDOPERAPRO = O.CDOPERADOR(+)
     AND N.CDFORNECED = F.CDFORNECED(+)
     AND N.CDFILIEMIT = FL.CDFILIAL(+)
     AND (N.DTEXPORTACAO IS NULL OR P_IDRELATORIO = V_SIM)
     AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
     AND (NVL(N.IDSISTEMA,'DFE') IN ('DFE','EST','PDV'))
     AND N.CDTIPOOPER = T.CDTIPOOPER
     AND T.CDEXTIPOOPER IS NOT NULL
     AND (N.DTENTRSAID = P_DTEXPORTACAO OR (N.DTENTRSAID < P_DTEXPORTACAO AND P_IDRELATORIO = V_NAO))
     AND N.CDFILIRECE = P_CDFILIAL
     AND N.IDENTRSAIDOP = V_IDENTRSAID
   UNION ALL
   SELECT N.CDFILIAL,
          N.NRLANCTONF,
          N.CDTIPOOPER,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDICMSSUBS),0) AS VRICMSSUBS,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDOUTRDESP),0) AS VROUTRDESP,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDSEGURO  ),0) AS VRSEGURO,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDFRETE   ),0) AS VRFRETE,
          NVL(VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, V_IDTOTICMS, V_IDVALORIMP, V_CDFISCALIP),0) AS VRTOTICMS,
          NVL(VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, V_IDTOTIPI, V_IDVALORIMP, V_CDFISCALIP),0) AS VRTOTIPI,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCADESC, V_IDDESCONTO),0) AS VRDESCONTO,
          N.VRNOTAFISC,
          N.VRPRODUTOS,
          N.DTENTRSAID,
          N.DSQTDEVOLU,
          N.DSESPECVOLU,
          N.DSMARCAVOLU,
          O.NMOPERADOR,
          NVL(N.DTEMISSAO,N.DTENTRSAID) AS DTEMISSAO,
          (SELECT E.SGESTADO
           FROM ENDEFORN E
           WHERE E.CDFORNECED = N.CDFORNECED) AS SGESTAMOVI,
          USR_PICTPESS(N.IDTPIJURNF, N.NRINSJURNF) AS NRINSJURNF,
          N.NRNOTAFISC,
          N.CDFILIEMIT AS CDFILIMOVI,
          T.CDEXTIPOOPER,
          T.IDUTILVENC,
          N.NRPLACAVEIC,
          N.SGUFVEIC,
          DECODE(N.IDPGTOFRETE, V_IDPGTOFRETE, V_IDFRETECIF, V_IDFRETEFOB) AS IDPGTOFRETE,
          N.CDSERIEENTR AS CDSERIENF,
          F.NMRAZSOCFORN AS NMRAZSOCFORN,
          V_DSSAIDA AS IDENTRSAIDOP,
          ' ' || CDFILIRECE AS CDFILITRANS
   FROM NOTAFISCAL N, TIPOOPERAC T, OPERADOR O, FORNECEDOR F
   WHERE N.CDOPERAPRO = O.CDOPERADOR(+)
     AND N.CDFORNECED = F.CDFORNECED
     AND (N.DTEXPORTACAO IS NULL OR P_IDRELATORIO = V_SIM)
     AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
     AND N.CDTIPOOPER = T.CDTIPOOPER
     AND T.CDEXTIPOOPER IS NOT NULL
     AND (N.DTENTRSAID = P_DTEXPORTACAO OR (N.DTENTRSAID < P_DTEXPORTACAO AND P_IDRELATORIO = V_NAO))
     AND NVL(N.IDSISTEMA, 'DFS') NOT IN ('REC','FIS')
     AND N.IDENTRSAIDOP = V_SIM
     AND N.CDFILIEMIT = P_CDFILIAL
   UNION ALL
   SELECT N.CDFILIAL,
          N.NRLANCTONF,
          N.CDTIPOOPER,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDICMSSUBS),0) AS VRICMSSUBS,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDOUTRDESP),0) AS VROUTRDESP,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDSEGURO  ),0) AS VRSEGURO,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCAACRE, V_IDFRETE   ),0) AS VRFRETE,
          NVL(VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, V_IDTOTICMS, V_IDVALORIMP, V_CDFISCALIP),0) AS VRTOTICMS,
          NVL(VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, V_IDTOTIPI, V_IDVALORIMP, V_CDFISCALIP),0) AS VRTOTIPI,
          NVL(VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, V_IDENCADESC, V_IDDESCONTO),0) AS VRDESCONTO,
          N.VRNOTAFISC,
          N.VRPRODUTOS,
          N.DTENTRSAID,
          N.DSQTDEVOLU,
          N.DSESPECVOLU,
          N.DSMARCAVOLU,
          O.NMOPERADOR,
          NVL(N.DTEMISSAO,N.DTENTRSAID) AS DTEMISSAO,
          E.SGESTADO,
          USR_PICTPESS(N.IDTPIJURNF, N.NRINSJURNF) AS NRINSJURNF,
          N.NRNOTAFISC,
          N.CDFILIEMIT AS CDFILIMOVI,
          T.CDEXTIPOOPER,
          T.IDUTILVENC,
          N.NRPLACAVEIC,
          N.SGUFVEIC,
          DECODE(N.IDPGTOFRETE, V_IDPGTOFRETE, V_IDFRETECIF, V_IDFRETEFOB) AS IDPGTOFRETE,
          N.CDSERIEENTR AS CDSERIENF,
          F.NMRAZSOCFILI AS NMRAZSOCFORN,
          V_DSSAIDA AS IDENTRSAIDOP,
          ' ' || CDFILIRECE AS CDFILITRANS
   FROM NOTAFISCAL N, TIPOOPERAC T, OPERADOR O, FILIAL F, ENDEFILI E
   WHERE N.CDOPERAPRO = O.CDOPERADOR(+)
     AND N.CDFILIRECE = F.CDFILIAL
     AND E.CDFILIAL = N.CDFILIRECE
     AND (N.DTEXPORTACAO IS NULL OR P_IDRELATORIO = V_SIM)
     AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
     AND N.CDTIPOOPER = T.CDTIPOOPER
     AND T.CDEXTIPOOPER IS NOT NULL
     AND (N.DTENTRSAID = P_DTEXPORTACAO OR (N.DTENTRSAID < P_DTEXPORTACAO AND P_IDRELATORIO = V_NAO))
     AND NVL(N.IDSISTEMA, 'DFS') NOT IN ('REC','FIS')
     AND N.IDENTRSAIDOP = V_SIM
     AND N.CDFILIEMIT = P_CDFILIAL
   ORDER BY 1, 2;

   CURSOR C_NOTAFISCAL_SAIDA IS
   SELECT N.CDFILIAL,
          N.NRLANCTONF,
          N.CDFILIEMIT AS CDFILIMOVI,
          USR_PICTPESS(N.IDTPIJURNF, N.NRINSJURNF) AS NRINSJURNF,
          N.DTENTRSAID,
          N.VRNOTAFISC
   FROM NOTAFISCAL N, TIPOOPERAC T, CLIENTE C
   WHERE N.CDCLIENTE = C.CDCLIENTE
     AND (N.DTEXPORTACAO IS NULL OR P_IDRELATORIO = V_SIM)
     AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
     AND N.CDTIPOOPER = T.CDTIPOOPER
     AND T.CDEXTIPOOPER IS NOT NULL
     AND (N.DTENTRSAID = P_DTEXPORTACAO OR (N.DTENTRSAID < P_DTEXPORTACAO AND P_IDRELATORIO = V_NAO))
     AND N.CDFILIEMIT = P_CDFILIAL
     AND NVL(N.IDSISTEMA, 'DFS') NOT IN ('REC','FIS')
   ORDER BY N.CDFILIAL, N.NRLANCTONF;

   CURSOR C_ITEMPRODUTO_SAIDA IS
   SELECT N.CDFILIAL,
          N.NRLANCTONF,
          N.CDTIPOOPER,
          N.CDFILIEMIT AS CDFILIMOVI,
          USR_PICTPESS(N.IDTPIJURNF, N.NRINSJURNF) AS NRINSJURNF,
          USR_CODIGOINTEGRACAO(I.CDPRODUTO) AS CDPRODINTE,
          I.DSPRODEMIS,
          I.QTITEMPROD,
          I.VRUNITITEM,
          I.VRTOTAITEM,
          N.DTENTRSAID
   FROM NOTAFISCAL N, TIPOOPERAC T, ITEMPRODUTO I, PRODUTO P
   WHERE N.CDFILIAL = V_CDFILIAL
     AND N.NRLANCTONF  = V_NRLANCTONF
     AND N.CDFILIAL = I.CDFILIAL
     AND N.NRLANCTONF = I.NRLANCTONF
     AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
     AND N.CDTIPOOPER = T.CDTIPOOPER
     AND I.CDPRODUTO = P.CDPRODUTO
   ORDER BY N.CDFILIAL, N.NRLANCTONF;

   CURSOR C_ITEMPRODUTO_ENTRADA IS
   SELECT N.CDFILIAL,
          N.NRLANCTONF,
          N.CDTIPOOPER,
          NVL(VALOR_IMPOSTO_ITEM(N.CDFILIAL, N.NRLANCTONF, I.NRSEQITPROD, V_IDTOTICMSS, V_IDVALORIMP, V_CDFISCALIP),0) AS VRTOTICMSS,
          NVL(VALOR_IMPOSTO_ITEM(N.CDFILIAL, N.NRLANCTONF, I.NRSEQITPROD, V_IDTOTICMS, V_IDVALORIMP, V_CDFISCALIP),0)  AS VRTOTICMS,
          NVL(VALOR_IMPOSTO_ITEM(N.CDFILIAL, N.NRLANCTONF, I.NRSEQITPROD, V_IDTOTIPI, V_IDVALORIMP, V_CDFISCALIP),0)   AS VRTOTIPI,
          NVL(I.VRDESCITEM,0) AS VRDESCITEM,
          NVL(I.VRTOTAITEM,0) AS VRTOTAITEM,
          NVL(I.VRUNITITEM,0) /  P.VRFATOCONV AS VRUNITITEM,
          NVL(I.QTITEMPROD,0)  * P.VRFATOCONV AS QTITEMPROD,
          NVL(I.QTITEMPROD * P.VRPESOLIQUPR,0) AS VRPESOLIQUPR,
          NVL(I.QTITEMPROD * P.VRPESOBRUTPR,0) AS VRPESOBRUTPR,
          NVL(VALOR_IMPOSTO_ITEM(N.CDFILIAL, N.NRLANCTONF, I.NRSEQITPROD, V_IDTOTIPI, V_IDPERCALIQ),0)   AS VRPEIPI,
          NVL(VALOR_IMPOSTO_ITEM(N.CDFILIAL, N.NRLANCTONF, I.NRSEQITPROD, V_IDTOTICMS, V_IDPERCALIQ),0)  AS VRPEICMS,
          P.SGUNIDADE,
          I.NRSEQITPROD,
          N.NRNOTAFISC,
          NVL(I.VRDESPERITEM,0) AS VRDESPERITEM,
          DECODE(N.IDENTRSAIDOP, V_IDENTRSAID, N.CDFILIRECE, N.CDFILIEMIT) AS CDFILIMOVI,
          T.CDEXTIPOOPER,
          NVL(I.CDFISOPEITEM,' ') AS CDFISOPEITEM,
          DECODE(N.IDENTRSAIDOP, N.CDSERIE, N.CDSERIEENTR) AS CDSERIENF,
          N.IDENTRSAIDOP,
          USR_CODIGOINTEGRACAO(P.CDPRODUTO) AS CDPRODINTE,
          P.NMPRODUTO,
          I.CDSITUTRIBPR
   FROM NOTAFISCAL N, ITEMPRODUTO I, PRODUTO P, TIPOOPERAC T
   WHERE N.CDFILIAL = I.CDFILIAL
     AND N.NRLANCTONF = I.NRLANCTONF
     AND N.CDFILIAL = V_CDFILIAL
     AND N.NRLANCTONF = V_NRLANCTONF
     AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
     AND N.CDTIPOOPER = T.CDTIPOOPER
     AND I.CDPRODUTO = P.CDPRODUTO;

   CURSOR C_AUTOFORN IS
   SELECT A.CDFILLANC,
          A.CDFILDEST,
          A.NRAUTOFORN,
          USR_PICTPESS(F.IDTPIJURFORN, F.NRINSJURFORN) AS NRINSJURFORN,
          A.DTAUTOFORN,
          I.DTPROGENTAF,
          SUM((((NVL(I.QTPROGENTAF,0) - NVL(QTEFETENTRAF,0)) * P.VRFATOCONV) * VRPREUNIAF)) AS VRTOTAAF
   FROM AUTOFORNE A, ITEMAUTF I, FORNECEDOR F, PRODUTO P
   WHERE A.CDFILDEST = I.CDFILDEST
     AND A.CDFILLANC = I.CDFILLANC
     AND A.NRAUTOFORN = I.NRAUTOFORN
     AND A.CDFORNECED = F.CDFORNECED
     AND I.CDPRODUTO = P.CDPRODUTO
     AND A.CDFILDEST = P_CDFILIAL
     AND (NVL(I.QTPROGENTAF,0) - NVL(QTEFETENTRAF,0)) > 0
     AND (I.IDBAIXITMAF = '1' OR I.IDBAIXITMAF IS NULL)
     AND (A.IDBAIXAAF = '1' OR A.IDBAIXAAF IS NULL)
     AND (I.USR_DTEXITAUTOFORN IS NULL OR P_IDRELATORIO = V_SIM)
     AND (A.DTAUTOFORN = P_DTEXPORTACAO OR (A.DTAUTOFORN < P_DTEXPORTACAO AND P_IDRELATORIO = V_NAO))
   GROUP BY A.CDFILLANC, A.CDFILDEST, A.NRAUTOFORN, F.IDTPIJURFORN,
            F.NRINSJURFORN, A.DTAUTOFORN, I.DTPROGENTAF
   ORDER BY 3, 6;

   CURSOR C_EXCAUTOFORN IS
   SELECT A.CDFILLANC,
          A.CDFILDEST,
          A.NRAUTOFORN,
          USR_PICTPESS(F.IDTPIJURFORN, F.NRINSJURFORN) AS NRINSJURFORN,
          A.DTAUTOFORN,
          I.DTPROGENTAF,
          SUM(((USR_QTEXPORTACAO * P.VRFATOCONV) * VRPREUNIAF)) AS VRTOTAAF
   FROM AUTOFORNE A, ITEMAUTF I, FORNECEDOR F, PRODUTO P
   WHERE A.CDFILDEST = I.CDFILDEST
     AND A.CDFILLANC = I.CDFILLANC
     AND A.NRAUTOFORN = I.NRAUTOFORN
     AND A.CDFORNECED = F.CDFORNECED
     AND I.CDPRODUTO = P.CDPRODUTO
     AND A.CDFILDEST = P_CDFILIAL
     AND (I.USR_DTEXITAUTOFORN IS NULL OR P_IDRELATORIO = V_SIM)
     AND (NVL(I.USR_IDALTEITEM,V_NAO) = V_SIM)
     AND (A.DTAUTOFORN = P_DTEXPORTACAO OR (A.DTAUTOFORN < P_DTEXPORTACAO AND P_IDRELATORIO = V_NAO))
   GROUP BY A.CDFILLANC, A.CDFILDEST, A.NRAUTOFORN, F.IDTPIJURFORN,
            F.NRINSJURFORN, A.DTAUTOFORN, I.DTPROGENTAF
   ORDER BY 3, 6;

   CURSOR C_POSESTCONS IS
   SELECT P.CDFILIAL,
          P.DTPOSESTCON,
          P.CDPRODUTO,
          USR_CODIGOINTEGRACAO(P.CDPRODUTO) AS CDPRODINTE,
          P.VRESTDIACON,
          P.QTESTDIACON,
          PR.NMPRODUTO
   FROM POSESTCONS P, PRODUTO PR
   WHERE P.CDPRODUTO = PR.CDPRODUTO
     AND P.QTESTDIACON <> 0
     AND P.CDFILIAL = P_CDFILIAL
     AND P.DTPOSESTCON = (SELECT MAX(DTPOSESTCON)
                          FROM POSESTCONS D
                          WHERE D.CDFILIAL = P.CDFILIAL
                            AND D.CDPRODUTO = P.CDPRODUTO
                            AND D.DTPOSESTCON <= (P_DTEXPORTACAO + 1));

BEGIN

   DELETE FROM ARQMAGNETICO
   WHERE NRSEQARQMAG = P_NRSEQARQMAG;

   IF P_DSTIPOEXP LIKE '%_0_%' THEN
      V_NRORDREGARQ := 0;
      FOR N IN C_NOTAFISCAL_ENTRADA LOOP
         SELECT MIN(NRSEQITPROD)
         INTO V_NRSEQITPROD
         FROM ITEMPRODUTO
         WHERE CDFILIAL = N.CDFILIAL
           AND NRLANCTONF = N.NRLANCTONF;

         IF V_NRSEQITPROD IS NOT NULL THEN
            BEGIN
               SELECT CDFISOPEITEM
               INTO V_CFOPITEMNF
               FROM ITEMPRODUTO
               WHERE CDFILIAL = N.CDFILIAL
                 AND NRLANCTONF = N.NRLANCTONF
                 AND NRSEQITPROD = V_NRSEQITPROD;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  NULL;
            END;
         END IF;

         IF NVL(N.VRTOTIPI,0) = 0  THEN
            V_IDINCIPI := 'N';
         END IF;

         IF NVL(N.VRTOTICMS,0) = 0 THEN
            V_IDINCICMS := 'N';
         END IF;

         V_IDTIPODOCU := '    ';
         IF (N.CDTIPOOPER = '01') OR (N.CDTIPOOPER = '21') THEN
            V_IDTIPODOCU := 'COM ';
         ELSIF N.CDTIPOOPER = '02' THEN
            IF N.IDENTRSAIDOP = '1' THEN
               V_IDTIPODOCU := 'TREP';
            ELSE
               V_IDTIPODOCU := 'TRSP';
            END IF;
         ELSIF N.CDTIPOOPER = '06' THEN
            V_IDTIPODOCU := 'DEVP';
         END IF;

         V_CDFILIAL := N.CDFILIAL;
         V_NRLANCTONF := N.NRLANCTONF;

         FOR I IN C_ITEMPRODUTO_ENTRADA LOOP
            V_DSREGARQMAG := V_IDTIPODOCU    ||
                             RPAD(N.NRINSJURNF,20)   ||
                             RPAD(SUBSTR(N.NMRAZSOCFORN,1,50),50) ||
                             RPAD(N.CDEXTIPOOPER,4)  ||
                             NVL(RPAD(TO_CHAR(N.DTEMISSAO,'DD/MM/YYYY'),12),'            ') ||
                             NVL(RPAD(LPAD(TO_CHAR(TO_NUMBER(N.NRNOTAFISC)),6,'0'),7),'       ')    ||
                             RPAD(I.CDFISOPEITEM,10)  ||
                             RPAD(I.CDPRODINTE,20)   ||
                             RPAD(SUBSTR(I.NMPRODUTO,1,50),50) ||
                             LPAD(TO_CHAR(ROUND(I.VRUNITITEM,2),'999999990.0000'),15)    ||
                             LPAD(TO_CHAR(ROUND(I.QTITEMPROD,3),'999999990.000'),15)    ||
                             LPAD(TO_CHAR(ROUND(I.VRTOTAITEM,2),'999999990.00'),15)    ||
                             LPAD(TO_CHAR(ROUND(I.VRPEICMS,2),'999999990.00'),15)      ||
                             LPAD(TO_CHAR(ROUND(I.VRPEIPI,2),'999999990.00'),15)       ||
                             LPAD(TO_CHAR(ROUND(I.VRDESCITEM,2),'999999990.00'),15)    ||
                             LPAD(TO_CHAR(ROUND(I.VRTOTIPI,2),'999999990.00'),15)      ||
                             LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),9)      ||
                             LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),15)      ||
                             LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),15)      ||
                             LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),15)      ||
                             LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),15)      ||
                             LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),15)      ||
                             LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),15)      ||
                             LPAD(TO_CHAR(ROUND(I.VRTOTICMS,2),'999999990.00'),15)     ||
                             LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),15)  ||
                             '   S ' ||
                             RPAD(N.CDFILIMOVI,5)   ||
                             TO_CHAR(N.DTENTRSAID,'DD/MM/YYYY');

            V_NRORDREGARQ := V_NRORDREGARQ + 1;

            INSERT INTO ARQMAGNETICO
            (NRSEQARQMAG, NRORDREGARQ, DSREGARQMAG)
            VALUES
            (P_NRSEQARQMAG, 'CADNF' || LPAD(TO_CHAR(V_NRORDREGARQ),5,'0'), V_DSREGARQMAG);
         END LOOP;

         V_IDTIPODOCU := '    ';
         IF (N.CDTIPOOPER = '01') OR (N.CDTIPOOPER = '21') THEN
            V_IDTIPODOCU := 'TOT ';
         ELSIF N.CDTIPOOPER = '02' THEN
            IF N.IDENTRSAIDOP = '1' THEN
               V_IDTIPODOCU := 'TRET';
            ELSE
               V_IDTIPODOCU := 'TRST';
            END IF;
         ELSIF N.CDTIPOOPER = '06' THEN
            V_IDTIPODOCU := 'DEVT';

            SELECT ' '||RPAD(USR_PICTPESS(MAX(NF.IDTPIJURNF), MAX(NF.NRINSJURNF)),19)||
                   NVL(RPAD(TO_CHAR(MAX(NF.DTENTRSAID),'DD/MM/YYYY'),11),'           ') ||
                   NVL(RPAD(LPAD(TO_CHAR(TO_NUMBER(MAX(NF.NRNOTAFISC))),6,'0'),7),'       ')    ||
                   NVL(LPAD(MAX(NF.CDSERIEENTR),3),'   ')   ||
                   ' '||NVL(RPAD(MAX(NF.CDFILIRECE),4),'   ')
            INTO V_DSNFORIGEM
            FROM ITEMNOTAFISC IT, NOTAFISCAL NF
            WHERE IT.CDFILIAL   = V_CDFILIAL
              AND IT.NRLANCTONF = V_NRLANCTONF
              AND NF.CDFILIAL = IT.CDFILIALIT
              AND NF.NRLANCTONF = IT.NRLANCTONFIT;
         END IF;

         V_DSREGARQMAG := V_IDTIPODOCU    ||
                          RPAD(N.NRINSJURNF,20)   ||
                          RPAD('*** TOTAL A PAGAR DESTA NF ***',50) ||
                          RPAD(N.CDEXTIPOOPER,4)  ||
                          '**/**/****  ' ||
                          NVL(RPAD(LPAD(TO_CHAR(TO_NUMBER(N.NRNOTAFISC)),6,'0'),7),'       ')    ||
                          LPAD(' ',110) ||
                          LPAD(TO_CHAR(ROUND(NVL(N.VRPRODUTOS,0)-NVL(N.VRDESCONTO,0),2),'999999990.00'),15)  ||
                          ' Desconto da NF:'||
                          LPAD(TO_CHAR(ROUND(N.VRDESCONTO,2),'999999990.00'),14)  ||
                          LPAD(TO_CHAR(ROUND(N.VRNOTAFISC,2),'999999990.00'),15)  ||
                          LPAD(' ',149) ||
                          RPAD(N.CDFILIMOVI,5)   ||
                          TO_CHAR(N.DTENTRSAID,'DD/MM/YYYY') ||
                          ' ' || LPAD(TO_CHAR(ROUND(N.VRFRETE,2),'999999990.00'),15)  ||
                          ' ' || LPAD(TO_CHAR(ROUND(N.VROUTRDESP,2),'999999990.00'),15) ||
                          NVL(V_DSNFORIGEM,'') ||
                          N.CDFILITRANS||
                          ' ' || LPAD(TO_CHAR(ROUND(0,2),'999999990.00'),15)  ||
                          ' ' || LPAD(TO_CHAR(ROUND(N.VRICMSSUBS,2),'999999990.00'),15);

         V_NRORDREGARQ := V_NRORDREGARQ + 1;

         INSERT INTO ARQMAGNETICO
         (NRSEQARQMAG, NRORDREGARQ, DSREGARQMAG)
         VALUES
         (P_NRSEQARQMAG, 'CADNF' || LPAD(TO_CHAR(V_NRORDREGARQ),'5','0'), V_DSREGARQMAG);

         UPDATE NOTAFISCAL
         SET DTEXPORTACAO = SYSDATE
         WHERE CDFILIAL = N.CDFILIAL
           AND NRLANCTONF = N.NRLANCTONF;
      END LOOP;
   END IF;

   IF P_DSTIPOEXP LIKE '%_1_%' THEN
      V_NRORDREGARQ := 0;

      FOR A IN C_EXCAUTOFORN LOOP
         IF P_IDRELATORIO <> 'S' THEN
            UPDATE ITEMAUTF
            SET USR_IDALTEITEM = NULL
            WHERE CDFILLANC = A.CDFILLANC
              AND CDFILDEST = A.CDFILDEST
              AND NRAUTOFORN = A.NRAUTOFORN
              AND USR_DTEXITAUTOFORN IS NULL
              AND DTPROGENTAF = A.DTPROGENTAF;
         END IF;

         V_NRAUTOFORN := RPAD(A.CDFILDEST||TO_CHAR(TO_NUMBER(A.NRAUTOFORN)),10);

         V_DSREGARQMAG := 'EXCAF '        ||
                          RPAD(A.CDFILDEST,5)     ||
                          RPAD(A.NRINSJURFORN,20,' ')  ||
                          RPAD(TO_CHAR(A.DTAUTOFORN,'DD/MM/YYYY'),12)    ||
                          RPAD(TO_CHAR(A.DTPROGENTAF,'DD/MM/YYYY'),12)   ||
                          LPAD(TO_CHAR(NVL(A.VRTOTAAF,0),'999999990.00'),15) ||
                          '    '||NVL(V_NRAUTOFORN,'            ');

         V_NRORDREGARQ := V_NRORDREGARQ + 1;

         INSERT INTO ARQMAGNETICO
         (NRSEQARQMAG, NRORDREGARQ, DSREGARQMAG)
         VALUES
         (P_NRSEQARQMAG, 'CADAF' || LPAD(TO_CHAR(V_NRORDREGARQ),'5','0'), V_DSREGARQMAG);
      END LOOP;

      FOR A IN C_AUTOFORN LOOP
         IF P_IDRELATORIO <> 'S' THEN
            UPDATE AUTOFORNE
            SET USR_DTEXPOAUTOFORN = SYSDATE
            WHERE CDFILLANC = A.CDFILLANC
              AND CDFILDEST = A.CDFILDEST
              AND NRAUTOFORN = A.NRAUTOFORN
              AND USR_DTEXPOAUTOFORN IS NULL;

            UPDATE ITEMAUTF
            SET USR_DTEXITAUTOFORN = SYSDATE
            WHERE CDFILLANC = A.CDFILLANC
              AND CDFILDEST = A.CDFILDEST
              AND NRAUTOFORN = A.NRAUTOFORN
              AND USR_DTEXITAUTOFORN IS NULL
              AND DTPROGENTAF = A.DTPROGENTAF;
         END IF;

         V_NRAUTOFORN := RPAD(A.CDFILDEST||TO_CHAR(TO_NUMBER(A.NRAUTOFORN)),10);

         V_DSREGARQMAG := 'CADAF '        ||
                          RPAD(A.CDFILDEST,5)     ||
                          RPAD(A.NRINSJURFORN,20,' ')  ||
                          RPAD(TO_CHAR(A.DTAUTOFORN,'DD/MM/YYYY'),12)    ||
                          RPAD(TO_CHAR(A.DTPROGENTAF,'DD/MM/YYYY'),12)   ||
                          LPAD(TO_CHAR(NVL(A.VRTOTAAF,0),'999999990.00'),15) ||
                          '    '||NVL(V_NRAUTOFORN,'            ');

         V_NRORDREGARQ := V_NRORDREGARQ + 1;

         INSERT INTO ARQMAGNETICO
         (NRSEQARQMAG, NRORDREGARQ, DSREGARQMAG)
         VALUES
         (P_NRSEQARQMAG, 'CADAF' || LPAD(TO_CHAR(V_NRORDREGARQ),'5','0'), V_DSREGARQMAG);
      END LOOP;
   END IF;

   IF P_DSTIPOEXP LIKE '%_2_%' THEN
      V_NRORDREGARQ := 0;
      FOR P IN C_POSESTCONS LOOP
         V_DSREGARQMAG := 'CADET '        ||
                          RPAD(P.CDFILIAL,5)      ||
                          RPAD(TO_CHAR(P.DTPOSESTCON,'DD/MM/YYYY'),12)      ||
                          RPAD(P.CDPRODINTE, 15)    ||
                          ' '||LPAD(TO_CHAR(ROUND(P.VRESTDIACON/P.QTESTDIACON,5),'999999990.00000'),15) ||
                          ' '||LPAD(TO_CHAR(ROUND(P.QTESTDIACON,2),'999999990.00'),15) ||
                          ' '||LPAD(TO_CHAR(ROUND(P.VRESTDIACON,2),'999999990.00'),15) ||
                          ' '||RPAD(P.NMPRODUTO, 50);

         V_NRORDREGARQ := V_NRORDREGARQ + 1;

         INSERT INTO ARQMAGNETICO
         (NRSEQARQMAG, NRORDREGARQ, DSREGARQMAG)
         VALUES
         (P_NRSEQARQMAG, 'CADET' || LPAD(TO_CHAR(V_NRORDREGARQ),'5','0'), V_DSREGARQMAG);
      END LOOP;
   END IF;

   IF P_DSTIPOEXP LIKE '%_3_%' THEN

      FOR S IN C_NOTAFISCAL_SAIDA LOOP

         V_CDFILIAL := S.CDFILIAL;
         V_NRLANCTONF := S.NRLANCTONF;
         V_DSREGARQMAG := NULL;

         FOR N IN C_ITEMPRODUTO_SAIDA LOOP
            V_DSREGARQMAG := 'FAT '    ||
                          RPAD(N.CDFILIMOVI,5)   ||
                          RPAD(N.CDPRODINTE,16)    ||
                          RPAD(SUBSTR(N.DSPRODEMIS,1,50),51) ||
                          LPAD(TO_CHAR(ROUND(N.QTITEMPROD,3),'999999990.000'),15)    ||
                          ' '||LPAD(TO_CHAR(ROUND(N.VRUNITITEM,2),'999999990.0000'),15)    ||
                          ' '||LPAD(TO_CHAR(ROUND(N.VRTOTAITEM,2),'999999990.00'),15)    ||
                          ' '||TO_CHAR(N.DTENTRSAID,'DD/MM/YYYY')||
                          ' '||RPAD(NVL(N.NRINSJURNF,' '),20);

            V_NRORDREGARQ := NVL(V_NRORDREGARQ,0) + 1;

            INSERT INTO ARQMAGNETICO
            (NRSEQARQMAG, NRORDREGARQ, DSREGARQMAG)
            VALUES
            (P_NRSEQARQMAG, 'CADNF' || LPAD(TO_CHAR(V_NRORDREGARQ),'5','0'), V_DSREGARQMAG);
         END LOOP;

         V_DSREGARQMAG := 'SOM '||
                          RPAD(NVL(S.CDFILIMOVI,' '),5)||
                          RPAD(' ', 86, ' ')||
                          'Total NF:   '||
                          ' '||LPAD(TO_CHAR(ROUND(NVL(S.VRNOTAFISC,0),2),'999999990.00'),15)||
                          ' '||TO_CHAR(S.DTENTRSAID,'DD/MM/YYYY')||
                          ' '||RPAD(NVL(S.NRINSJURNF,' '), 20, ' ');

         V_NRORDREGARQ := NVL(V_NRORDREGARQ,0)+ 1;

         INSERT INTO ARQMAGNETICO
         (NRSEQARQMAG, NRORDREGARQ, DSREGARQMAG)
         VALUES
         (P_NRSEQARQMAG, 'CADNF' || LPAD(TO_CHAR(V_NRORDREGARQ),'5','0'), V_DSREGARQMAG);

         UPDATE NOTAFISCAL
         SET DTEXPORTACAO = SYSDATE
         WHERE CDFILIAL = S.CDFILIAL
           AND NRLANCTONF = S.NRLANCTONF;

      END LOOP;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_EXPORTA_DADOS','5.03.031'); END;
/
