create or replace PROCEDURE ARQUIVO_DIF_TO_G(
   P_CDINSCESTA    IN MAPAAPURAC.CDINSCESTA%TYPE,  --I.E. da empresa referencia
   P_PERIODO       IN MAPAAPURAC.CDINSCESTA%TYPE,  --Periodo referEncia AAAA
   P_NRSEQARQMAG   IN ARQMAGNETICO.NRSEQARQMAG%TYPE, --Sequencial controle tabela ARQMAGNETICO
   P_CONTREG       OUT NUMBER
   )
AS
   V_CONTREG   NUMBER;
BEGIN
   V_CONTREG := 0;

    INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
    SELECT
        '07'||LPAD(RTRIM(RANK () OVER (ORDER BY TIPODOMICFISCAL,DECODE(UF,'TO',IBGE_ORIGEM,'9999999'))), 8, '0') ORDEM,
        'G'                                                                   || --G1 Segmento
        LPAD(P_CDINSCESTA,9,'0')                                              || --G2 Inscricao Estadual
        P_PERIODO                                                             || --G3 Periodo de Referencia
        DECODE(UF,'TO',IBGE_ORIGEM,'9999999')                                 || --G4 Municipio de Origem   
        TIPODOMICFISCAL                                                       || --G5 Tipo Domicilio Fiscal
        CODMUNIBGE                                                            || --G6 Codigo do Municipio
        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'S',VALOR_CONTABIL,0)),'000000000000.00')),'.')    || --G7 Saidas e/ou Prestacoes
        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'E',VALOR_CONTABIL,0)),'000000000000.00')),'.')       --G8 Entradas e/ou Aquisicoes
        ,P_NRSEQARQMAG
    from TMP_DIF_TO_ENTSAI
    where
        CFOP NOT IN ('1128','1131','1154','1213','1252','1253','1254','1255','1256','1257',
                     '1302','1303','1304','1305','1306','1352','1353','1354','1355','1356',
                     '1360','1451','1452','1505','1506','1552','1553','1554','1555','1601',
                     '1602','1603','1604','1605','1663','1664','2128','2131','2154','2159',
                     '2213','2214','2505','2506','2552','2554','2555','2603','2663','2664')
      AND CFOP NOT IN ('5131','5213','5412','5413','5414','5451','5504','5505','5552','5554',
                     '5555','5557','5601','5602','5603','5605','5606','5663','5664','5665',
                     '5666','6131','6213','6412','6413','6414','6504','6505','6555','6557',
                     '6663','6664','6665','6666')
      AND CDLAYOUTLMAG in ('C401','C402','C403','C407','C408','C409','C410',
                           'E401','E402','E403','E404','E405','E406','E407','E408','E409')
    GROUP BY
        DECODE(UF,'TO',IBGE_ORIGEM,'9999999'),
        TIPODOMICFISCAL,
        CODMUNIBGE;

    V_CONTREG := V_CONTREG + SQL%ROWCOUNT;

   P_CONTREG := V_CONTREG;
END;
/