create or replace PROCEDURE ARQUIVO_DIF_TO_CD(
   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,
   P_REG_C_K       OUT MAPAAPURAC.IDATIVO%TYPE
   )
AS
   V_CONTREG   NUMBER;
   V_REG_C_K   VARCHAR2(1);
BEGIN
   V_CONTREG := 0;
   V_REG_C_K := 'N';

    INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
    SELECT
        '03'||LPAD(RTRIM(RANK () OVER (ORDER BY SUBSTR(CDLAYOUTLMAG,3,2),TIPODOMICFISCAL)), 8, '0') ORDEM,
        'C'                                                                   || --C1 Segmento
        LPAD(P_CDINSCESTA,9,'0')                                              || --C2 Inscricao Estadual
        P_PERIODO                                                             || --C3 Periodo de Referencia
        SUBSTR(CDLAYOUTLMAG,3,2)                                              || --C4 Codigo Tipo de Entradas **se valor 11 gera registro K
        TIPODOMICFISCAL                                                       || --C5 Tipo Domicilio Fiscal
        CODMUNIBGE                                                            || --C6 Codigo do Municipio
        REPLACE(LTRIM(TO_CHAR(SUM(VALOR_CONTABIL),'000000000000.00')),'.')    || --C7 Valor Contabil
        REPLACE(LTRIM(TO_CHAR(SUM(BASE_CALC_ICMS),'000000000000.00')),'.')    || --C8 Base de Calculo
        REPLACE(LTRIM(TO_CHAR(SUM(OUTROS),'000000000000.00')),'.')            || --C9 Outras/Isentas nao Tributadas
        REPLACE(LTRIM(TO_CHAR(SUM(VR_CONT_ICMS_ST),'000000000000.00')),'.')      --C10 Substituicao Tributaria
        ,P_NRSEQARQMAG
    from TMP_DIF_TO_ENTSAI
    WHERE IDENTRSAIDOP = 'E'
    AND 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')
    GROUP BY
        SUBSTR(CDLAYOUTLMAG,3,2),
        TIPODOMICFISCAL,
        CODMUNIBGE;
    V_CONTREG := V_CONTREG + SQL%ROWCOUNT;

   BEGIN
    SELECT
        distinct 'S'
    INTO
        V_REG_C_K
    from TMP_DIF_TO_ENTSAI
    WHERE IDENTRSAIDOP = 'E'
    AND SUBSTR(CDLAYOUTLMAG,3,2) = '11';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_REG_C_K := 'N';
      END;

------------------------------/* SEGMENTO D  Entradas de Mercadorias, Bens e/ou Aquisicao de Servicos,  */------------------------------
------------------------------/* Detalhadas (por Unidade da Federacao)                                   */------------------------------
    INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
    SELECT
        '04'||LPAD(RTRIM(RANK () OVER (ORDER BY CD_F4,TIPODOMICFISCAL)), 8, '0') ORDEM,
        'D'                                                                   || --D1 Segmento
        LPAD(P_CDINSCESTA,9,'0')                                              || --D2 Inscricao Estadual
        P_PERIODO                                                             || --D3 Periodo de Referencia
        CD_F4                                                                 || --D4 Codigo UF
        TIPODOMICFISCAL                                                       || --D5 Tipo Domicilio Fiscal
        CODMUNIBGE                                                            || --D6 Codigo do Municipio
        REPLACE(LTRIM(TO_CHAR(SUM(VALOR_CONTABIL),'000000000000.00')),'.')    || --D7 Valor Contabil
        REPLACE(LTRIM(TO_CHAR(SUM(BASE_CALC_ICMS),'000000000000.00')),'.')    || --D8 Base de Calculo
        REPLACE(LTRIM(TO_CHAR(SUM(OUTROS),'000000000000.00')),'.')            || --D9 Outras/Isentas nao Tributadas
        REPLACE(LTRIM(TO_CHAR(0,'000000000000.00')),'.')                      || --D10 Petroleo/Energia
        REPLACE(LTRIM(TO_CHAR(SUM(VR_ICMS_ST),'000000000000.00')),'.')        || --D11 Outros Produtos
        REPLACE(LTRIM(TO_CHAR(SUM(VR_CONT_ICMS_ST),'000000000000.00')),'.')      --D12 Substituicao Tributaria
        ,P_NRSEQARQMAG
    from TMP_DIF_TO_ENTSAI
    WHERE IDENTRSAIDOP = 'E'
    AND 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')
    GROUP BY
        CD_F4,
        TIPODOMICFISCAL,
        CODMUNIBGE;

    V_CONTREG := V_CONTREG + SQL%ROWCOUNT;

   P_REG_C_K := V_REG_C_K;
   P_CONTREG := V_CONTREG;
END;
/