CREATE OR REPLACE PROCEDURE ARQCAD_JURIFISC_491(
   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 = '491' ) LOOP

      FOR C_ARQCAD_JURIFISC IN (SELECT DISTINCT SUBSTR(LPAD(NVL(REPLACE(REPLACE(REPLACE(F.CDFORNECED,'/',''),'.',''),'-',''), ' '), 21, ' '),8,14)||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(NRINSJURFORN,'/',''),'.',''),'-',''),1,14), ' '), 14, ' ')||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(NRINSESTFORN,'/',''),'-',''),'.',''),1,14), ' '), 14, ' ')||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(NRINSMUNFORN,'/',''),'-',''),'.',''),1,14), ' '), 14, ' ')||
                                       RPAD(NVL(NMRAZSOCFORN, ' '), 70, ' ')              ||
                                       RPAD(NVL(E.DSENDEFORN, ' '), 60, ' ')              ||
                                       RPAD(SUBSTR(NVL(NMBAIRFORN,' '),1,20), 20, ' ')    ||
                                       RPAD(NVL(M.NMMUNICIPIO, ' '), 20, ' ')             ||
                                       RPAD(NVL(E.SGESTADO,' '), 2, ' ')                  ||
                                       RPAD(DECODE(E.CDPAIS, '0055', ' ', NVL(P.NMPAIS,' ')), 20, ' ') ||
                                       RPAD(SUBSTR(NVL(REPLACE(REPLACE(NRCEPFORN,'.',''),'-',''),' '),1,8),8, ' ') AS DSARQMAG
                                  FROM FORNECEDOR F, ENDEFORN E, MUNICIPIO M, PAIS P
                                 WHERE C_TMP.CDCAMPO1 = 'F'            AND
                                       C_TMP.CDCHAVEREG = F.CDFORNECED AND
                                       F.CDFORNECED = E.CDFORNECED     AND
                                       E.CDPAIS = P.CDPAIS             AND
                                       E.CDPAIS = M.CDPAIS             AND
                                       E.SGESTADO = M.SGESTADO         AND
                                       E.CDMUNICIPIO = M.CDMUNICIPIO   AND
                                       E.IDTPENDEFORN = 'P'
                                UNION ALL
                                SELECT DISTINCT SUBSTR(LPAD(NVL(REPLACE(REPLACE(REPLACE(C.CDCLIENTE,'/',''),'.',''),'-',''), ' '), 21, ' '),8,14)||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(NRINSJURCLIE,'/',''),'-',''),'.',''),1,14), ' '), 14, ' ') ||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(NRINSESTCLIE,'/',''),'-',''),'.',''),1,14), ' '), 14, ' ') ||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(NRINSMUNICLIE,'/',''),'-',''),'.',''),1,14), ' '), 14, ' ') ||
                                       RPAD(NVL(NMRAZSOCCLIE,' '), 70, ' ')               ||
                                       RPAD(NVL(E.DSENDECLIE, ' '), 60, ' ')              ||
                                       RPAD(SUBSTR(NVL(NMBAIRCLIE,' '),1,20), 20, ' ')    ||
                                       RPAD(NVL(M.NMMUNICIPIO, ' '), 20, ' ')             ||
                                       RPAD(NVL(E.SGESTADO,' '), 2, ' ')                  ||
                                       RPAD(DECODE(E.CDPAIS, '0055', ' ', NVL(P.NMPAIS,' ')), 20, ' ') ||
                                       RPAD(SUBSTR(NVL(REPLACE(REPLACE(NRCEPCLIE,'.',''),'-',''),' '),1,8),8, ' ') AS DSARQMAG
                                  FROM CLIENTE C, ENDECLIE E, MUNICIPIO M, PAIS P
                                 WHERE C_TMP.CDCAMPO1 = 'C'           AND
                                       C_TMP.CDCHAVEREG = C.CDCLIENTE AND
                                       C.CDCLIENTE = E.CDCLIENTE      AND
                                       E.CDPAIS = P.CDPAIS            AND
                                       E.CDPAIS = M.CDPAIS            AND
                                       E.SGESTADO = M.SGESTADO        AND
                                       E.CDMUNICIPIO = M.CDMUNICIPIO  AND
                                       E.IDTPENDECLIE = 'P'
                                UNION ALL
                                SELECT DISTINCT SUBSTR(LPAD(NVL(REPLACE(REPLACE(REPLACE(F.CDFILIAL,'/',''),'.',''),'-',''), ' '), 21, ' '),8,14)||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(F.NRINSJURFILI,'/',''),'.',''),'-',''),1,14), ' '), 14, ' ')||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(F.CDINSCESTA,'/',''),'-',''),'.',''),1,14), ' '), 14, ' ')||
                                       RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(F.CDINSCMUNI,'/',''),'-',''),'.',''),1,14), ' '), 14, ' ')||
                                       RPAD(NVL(NMRAZSOCFILI, ' '), 70, ' ')||
                                       RPAD(NVL(E.DSENDEFILI, ' '), 60, ' ')  ||
                                       RPAD(SUBSTR(NVL(E.NMBAIRFILI,' '),1,20), 20, ' ') ||
                                       RPAD(NVL(M.NMMUNICIPIO, ' '), 20, ' ')             ||
                                       RPAD(NVL(E.SGESTADO,' '), 2, ' ')                ||
                                       RPAD(DECODE(E.CDPAIS, '0055', ' ', NVL(P.NMPAIS,' ')), 20, ' ') ||
                                       RPAD(SUBSTR(NVL(REPLACE(REPLACE(E.NRCEPFILI,'.',''),'-',''),' '),1,8),8, ' ') AS DSARQMAG
                                  FROM FILIAL F, ENDEFILI E, MUNICIPIO M, PAIS P
                                 WHERE C_TMP.CDCAMPO1 = 'L'          AND
                                       C_TMP.CDCHAVEREG = F.CDFILIAL AND
                                       F.CDFILIAL = E.CDFILIAL       AND
                                       E.CDPAIS = P.CDPAIS           AND
                                       E.CDPAIS = M.CDPAIS           AND
                                       E.SGESTADO = M.SGESTADO       AND
                                       E.CDMUNICIPIO = M.CDMUNICIPIO AND
                                       E.IDTPENDEFILI = 'P') 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_ARQCAD_JURIFISC.DSARQMAG,  P_NRSEQARQMAG);
      END LOOP;
   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQCAD_JURIFISC_491','5.33.xxx'); END;
/

