CREATE OR REPLACE PROCEDURE TAB_MERCSERV_495(
   P_CDEMPRESA    IN NOTAFISCAL.CDEMPRESA%TYPE,
   P_NRINSJURFILI IN FILIAL.NRINSJURFILI%TYPE,
   P_CDIMPOSTO    IN IMPOSTONF.CDIMPOSTO%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE)
AS
   V_SEQUNCIAL NUMBER;
   V_DATA_ATU  VARCHAR2(08);
BEGIN

   FOR C_TMP IN (SELECT CDREGISTRO, CDCHAVEREG, CDCAMPO1
                   FROM TMP_REGDADOS_IN86
                  WHERE CDREGISTRO = '495' ) LOOP

      FOR C_MERCSERV IN (SELECT DISTINCT RPAD(NVL(P.CDARVPROD, ' '), 20, ' ')||
                                RPAD(NVL(SUBSTR(P.NMPRODUTO,1,45), ' '), 45, ' ') AS DSARQMAG, P.CDARVPROD
                           FROM PRODUTO P
                          WHERE P.CDPRODUTO = C_TMP.CDCHAVEREG
                          ORDER BY P.CDARVPROD) LOOP

         SELECT RPAD(TO_CHAR(MIN(DTEMISSAO), 'DDMMYYYY'), 8, ' ')
           INTO V_DATA_ATU
           FROM (SELECT DTEMISSAO
                   FROM NOTAFISCAL N, TMP_REGDADOS_IN86 T
                  WHERE N.CDFILIAL = SUBSTR(T.CDCHAVEREG,1,4)
                    AND N.NRLANCTONF = SUBSTR(T.CDCHAVEREG,5,10)
                    AND T.CDREGISTRO = '431'
                 UNION ALL
                 SELECT DTEMISSAO
                   FROM NOTAFISCAL N, TMP_REGDADOS_IN86 T
                  WHERE N.CDFILIAL = SUBSTR(T.CDCHAVEREG,1,4)
                    AND N.NRLANCTONF = SUBSTR(T.CDCHAVEREG,5,10)
                    AND T.CDREGISTRO = '433'
                 UNION ALL
                 SELECT DTEMISSAO
                   FROM NOTAFISCAL N, TMP_REGDADOS_IN86 T
                  WHERE N.CDFILIAL = SUBSTR(T.CDCHAVEREG,1,4)
                    AND N.NRLANCTONF = SUBSTR(T.CDCHAVEREG,5,10)
                    AND T.CDREGISTRO = '435'
                 UNION ALL
                 SELECT DTEMISSAO
                   FROM NOTAFISCAL N, TMP_REGDADOS_IN86 T
                  WHERE N.CDFILIAL = SUBSTR(T.CDCHAVEREG,1,4)
                    AND N.NRLANCTONF = SUBSTR(T.CDCHAVEREG,5,10)
                    AND T.CDREGISTRO = '438');

         V_SEQUNCIAL := NVL(V_SEQUNCIAL,0) +1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('06'||LPAD(RTRIM(V_SEQUNCIAL), 8, '0'), V_DATA_ATU||C_MERCSERV.DSARQMAG, P_NRSEQARQMAG);
      END LOOP;
   END LOOP;
END;
/
