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


    INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
    SELECT
        '05'||LPAD(RTRIM(RANK () OVER (ORDER BY SUBSTR(CDLAYOUTLMAG,3,2),TIPODOMICFISCAL)), 8, '0') ORDEM,
        'E'                                                                   || --E1 Segmento
        LPAD(P_CDINSCESTA,9,'0')                                              || --E2 Inscricao Estadual
        P_PERIODO                                                             || --E3 Periodo de Referencia
        SUBSTR(CDLAYOUTLMAG,3,2)                                              || --E4 Tipo de Saidas **se valor 10 gera registro K     
        TIPODOMICFISCAL                                                       || --E5 Tipo Domicilio Fiscal
        CODMUNIBGE                                                            || --E6 Codigo do Municipio
        REPLACE(LTRIM(TO_CHAR(SUM(VALOR_CONTABIL),'000000000000.00')),'.')    || --E7 Valor Contabil
        REPLACE(LTRIM(TO_CHAR(SUM(BASE_CALC_ICMS),'000000000000.00')),'.')    || --E8 Base de Calculo
        REPLACE(LTRIM(TO_CHAR(SUM(OUTROS),'000000000000.00')),'.')            || --E9 Outras/Isentas nao Tributadas
        REPLACE(LTRIM(TO_CHAR(SUM(VR_CONT_ICMS_ST),'000000000000.00')),'.')      --E10 Substituicao Tributaria
        ,P_NRSEQARQMAG
    from TMP_DIF_TO_ENTSAI
    WHERE IDENTRSAIDOP = 'S'
    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')
    GROUP BY
        SUBSTR(CDLAYOUTLMAG,3,2),
        TIPODOMICFISCAL,
        CODMUNIBGE;

    V_CONTREG := V_CONTREG + SQL%ROWCOUNT;

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



    INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
    SELECT
        '06'||LPAD(RTRIM(RANK () OVER (ORDER BY CD_F4,TIPODOMICFISCAL)), 8, '0') ORDEM,
        'F'                                                                   || --F1 Segmento
        LPAD(P_CDINSCESTA,9,'0')                                              || --F2 Inscricao Estadual
        P_PERIODO                                                             || --F3 Periodo de Referencia
        CD_F4                                                                 || --F4 Codigo UF
        TIPODOMICFISCAL                                                       || --F5 Tipo Domicilio Fiscal
        CODMUNIBGE                                                            || --F6 Codigo do Municipio
        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDCONTRIBICMS,'S',VALOR_CONTABIL,0)),'000000000000.00')),'.')    || --F7 Valor Contabil Contribuinte
        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDCONTRIBICMS,'N',VALOR_CONTABIL,0)),'000000000000.00')),'.')    || --F8 Valor Contabil Nao Contribuinte
        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDCONTRIBICMS,'S',BASE_CALC_ICMS,0)),'000000000000.00')),'.')    || --F9 Base de Calculo Contribuinte
        REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IDCONTRIBICMS,'N',BASE_CALC_ICMS,0)),'000000000000.00')),'.')    || --F10 Base de Calculo Nao Contribuinte
        REPLACE(LTRIM(TO_CHAR(SUM(OUTROS),'000000000000.00')),'.')            || --F11 Outras/Isentas nao Tributadas
        REPLACE(LTRIM(TO_CHAR(SUM(VR_ICMS_ST),'000000000000.00')),'.')        || --F12 Substituicao Tributaria
        REPLACE(LTRIM(TO_CHAR(SUM(VR_CONT_ICMS_ST),'000000000000.00')),'.')      --F13 ICMS Cobrado por Substituicao Tributaria
        ,P_NRSEQARQMAG
    from TMP_DIF_TO_ENTSAI
    WHERE IDENTRSAIDOP = 'S'
    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')
    GROUP BY
        CD_F4,
        TIPODOMICFISCAL,
        CODMUNIBGE;

    V_CONTREG := V_CONTREG + SQL%ROWCOUNT;

   P_REG_E_K := V_REG_E_K;
   P_CONTREG := V_CONTREG;
END;
/