CREATE OR REPLACE PROCEDURE ARQUIVO_DIF_TO(
   P_CDEMPRESA     IN MAPAAPURAC.CDEMPRESA%TYPE,  --Cod empresa referencia
   P_CDINSCESTA    IN MAPAAPURAC.CDINSCESTA%TYPE,  --I.E. da empresa referencia
   P_LIVRO         IN LIVROMAGNETICO.CDLIVROMAG%TYPE, --Referencia de livro a ser usado nos registros C4 e E4 relacao de CFOP x tipo documento
   P_DTINICIO      IN MAPAAPURAC.DTINIPERIODO%TYPE, --Data inicio periodo
   P_DTFIM         IN MAPAAPURAC.DTFIMPERIODO%TYPE, --Data final periodo o ano sera usado para refencia
   P_NRSEQARQMAG   IN ARQMAGNETICO.NRSEQARQMAG%TYPE, --Sequencial controle tabela ARQMAGNETICO
   P_IDRETIFIC     IN NOTAFISCAL.IDENTRSAIDOP%TYPE, --(S=Sim, N=Nao).
   P_IDTPESTAB     IN NOTAFISCAL.IDENTRSAIDOP%TYPE, --(1=Unico, 2=Matriz, 3=Filial)
   P_IDFINALIDADE  IN NOTAFISCAL.IDENTRSAIDOP%TYPE, --(1=Informacao anual,2=Baixa Voluntaria,4=Suspensao Voluntaria)
   P_IDTPESCRIT    IN NOTAFISCAL.IDENTRSAIDOP%TYPE, --(1=Fiscal, 2= Contabil)
   P_VRINICAIXA    IN NOTAFISCAL.VRNOTAFISC%TYPE, --Saldo inicial de caixa
   P_VRFIMCAIXA    IN NOTAFISCAL.VRNOTAFISC%TYPE, --Saldo final de caixa
   P_VRPATRLIQ     IN NOTAFISCAL.VRNOTAFISC%TYPE, --Valor de patrimonio liquido
   P_IECENTRAL     IN NOTAFISCAL.IDENTRSAIDOP%TYPE, --Inscricao estadual centralizada (S=Sim, N=Nao).
   P_REGAPURACAO   IN NOTAFISCAL.IDENTRSAIDOP%TYPE, --Regime de apuracao
   P_VERSAO        IN VARCHAR2, --versao do laiout
   P_IDMUDDOMICI   IN NOTAFISCAL.IDENTRSAIDOP%TYPE, --Houve mudanca de domicilio (S=Sim, N=Nao)
   P_CDMUNIANT     IN MUNICIPIO.CDMUNICIPIO%TYPE, --Cod Municipio Anterior
   P_DTINIMUNIANT  IN NOTAFISCAL.DTENTRSAID%TYPE, --Data inicio municipio anterior
   P_DTFINMUNIANT  IN NOTAFISCAL.DTENTRSAID%TYPE --Data inicio municipio anterior
   )
AS
   V_CONTREG   NUMBER; --contador de linhas auxiliar
   V_CONTTOT   NUMBER; --contador de linhas total
   V_AUX_UF    NUMBER; --auxiliar para verificar se preencher tabela temporaria de uf
   V_MAPAS     VARCHAR2(2000); --lista de mapas a serem usados
   V_REG_C_K   VARCHAR2(1); --auxiliar de gatilho para registro K
   V_REG_E_K   VARCHAR2(1); --auxiliar de gatilho para registro K

   V_PERIODO    VARCHAR2(4);
   V_CNAE       EMPRESA.CDCNAE%TYPE;
   V_IBGE_A     VARCHAR(7);
   V_IBGE_B     VARCHAR(7);
   V_DTMUDANCA  DATE;
BEGIN
   V_CONTTOT := 0;
   DELETE FROM ARQMAGNETICO WHERE NRSEQARQMAG = P_NRSEQARQMAG;
   DELETE FROM TMP_DIF_TO_ENTSAI;
   COMMIT;

   V_PERIODO := TO_CHAR(P_DTFIM,'YYYY');

-----------------------------------------/* SEGMENTO AB - ARQUIVO_DIF_TO_AB */-----------------------------------------------
   V_CONTREG := 0;
   ARQUIVO_DIF_TO_AB(P_CDEMPRESA,P_CDINSCESTA, V_PERIODO,P_DTINICIO,P_DTFIM,P_NRSEQARQMAG,
                     P_IDRETIFIC,P_IDTPESTAB,P_IDFINALIDADE,P_IDTPESCRIT,P_VRINICAIXA,
                     P_VRFIMCAIXA,P_VRPATRLIQ,P_IECENTRAL,P_REGAPURACAO,P_VERSAO,
                     P_IDMUDDOMICI,P_CDMUNIANT,P_DTINIMUNIANT,P_DTFINMUNIANT,V_CONTREG,V_CNAE,V_IBGE_A,V_IBGE_B);

   V_CONTTOT := NVL(V_CONTTOT,0) + NVL(V_CONTREG,0);
-----------------------------------------/* CARREGANDO BASE A SER USADA REFERENTE A ENTRADAS E SAIDA */-----------------------------------------------
   V_DTMUDANCA := NVL(P_DTFINMUNIANT,TO_DATE('01/01/1900','DD/MM/YYYY'));
   -- listando mapas a serem usados
   FOR C_MAPAS IN (SELECT
                   M.NRSEQUMAPAPU
                   FROM
                      MAPAAPURAC M
                   WHERE
                      1=1
                      AND M.CDIMPOSTO     = '00'
                      AND M.CDEMPRESA     = P_CDEMPRESA
                      AND M.CDINSCESTA    = P_CDINSCESTA
                      AND TRUNC(M.DTINIPERIODO) >= TRUNC(P_DTINICIO)
                      AND TRUNC(M.DTFIMPERIODO) <= TRUNC(P_DTFIM)
				  ) LOOP

      INSERT INTO TMP_DIF_TO_ENTSAI
        (CDFILIAL,NRLANCTONF,IDENTRSAIDOP,UF,TIPODOMICFISCAL,CODMUNIBGE,
         NRSEQITPROD,CFOP,CDLAYOUTLMAG,IDCONTRIBICMS,VALOR_CONTABIL,BASE_CALC_ICMS,OUTROS,VR_CONT_ICMS_ST,VR_ICMS_ST,
         CD_F4,IBGE_ORIGEM)
      SELECT
        CDFILIAL,
        NRLANCTONF,
        IDENTRSAIDOP,
        UF,
        TIPODOMICFISCAL,
        CODMUNIBGE,
        NRSEQITPROD,
        CFOP,
        CDLAYOUTLMAG,
        IDCONTRIBICMS,
        VALOR_CONTABIL,
        BASE_CALC_ICMS,
        CASE
          WHEN (VALOR_CONTABIL > (BASE_CALC_ICMS+VR_CONT_ICMS_ST))
            THEN VALOR_CONTABIL-BASE_CALC_ICMS-VR_CONT_ICMS_ST
          ELSE
            0
          END OUTROS,
        VR_CONT_ICMS_ST,
        VR_ICMS_ST,
        (SELECT UF.CDESTADO CD_F4 FROM UF_DECLARACAO UF WHERE UF.SGESTADO = A.UF AND UF.DECLARACAO = 'DIF TO'),
        IBGE_ORIGEM
      FROM
        (
        SELECT
          IT.CDFILIAL,
          IT.NRLANCTONF,
          N.IDENTRSAIDOP,
          CASE
            WHEN TRUNC(DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO)) > TRUNC(V_DTMUDANCA)
              THEN 'A'
            ELSE 'B'
          END TIPODOMICFISCAL,
          CASE
            WHEN TRUNC(DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO)) > TRUNC(V_DTMUDANCA)
              THEN V_IBGE_A
            ELSE V_IBGE_B
          END CODMUNIBGE,
          IT.NRSEQITPROD,
          IT.CDFISCOPERIT CFOP,
          CDLAYOUTLMAG,
            CASE
            WHEN (N.CDCLIENTE IS NOT NULL)
              THEN DECODE(N.IDENTRSAIDOP,'E','S',(SELECT C.IDCONTRIBICMS FROM CLIENTE C WHERE C.CDCLIENTE = N.CDCLIENTE))
            ELSE
              'S' END IDCONTRIBICMS,
          ROUND(NVL(IT.VRCONTITIMP,0)
               +NVL(VALOR_REDUCAO(IT.CDFILIAL, IT.NRLANCTONF, IT.CDSITUTRIBIT, IT.NRSEQITPROD, IT.CDFISCOPERIT, NULL, NULL, NULL),0)
               ,2) VALOR_CONTABIL,
          ROUND(DECODE(IT.CDFISCALIT,1,NVL(IT.VRBASECALCIT,0),0),2) BASE_CALC_ICMS,
          ROUND(NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,'80','C'),0),2) VR_CONT_ICMS_ST,
          ROUND(NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,IT.NRSEQITPROD,'80','I'),0),2) VR_ICMS_ST,
          CASE
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDFORNECED IS NOT NULL)) --COMPRA
              THEN (SELECT DECODE(EF.CDPAIS,'0055',EF.SGESTADO,'XX') FROM ENDEFORN EF WHERE EF.IDTPENDEFORN = 'P' AND EF.CDFORNECED = N.CDFORNECED)
            WHEN ((N.IDENTRSAIDOP = 'S') AND (N.CDCLIENTE IS NOT NULL))  --VENDA
              THEN (SELECT DECODE(EC.CDPAIS,'0055',EC.SGESTADO,'XX') FROM ENDECLIE EC WHERE EC.IDTPENDECLIE = 'P' AND EC.CDCLIENTE = N.CDCLIENTE)
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDCLIENTE IS NOT NULL)) --DEVOLUCAO COMPRA
              THEN (SELECT DECODE(ECD.CDPAIS,'0055',ECD.SGESTADO,'XX') FROM ENDECLIE ECD WHERE ECD.IDTPENDECLIE = 'P' AND ECD.CDCLIENTE = N.CDCLIENTE)
            WHEN ((N.IDENTRSAIDOP = 'S') AND (N.CDFORNECED IS NOT NULL))  --DEVOLUCAO VENDA
              THEN (SELECT DECODE(EFD.CDPAIS,'0055',EFD.SGESTADO,'XX') FROM ENDEFORN EFD WHERE EFD.IDTPENDEFORN = 'P' AND EFD.CDFORNECED = N.CDFORNECED)
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDFILIRECE IS NOT NULL)) --ENTRADA DE TRANSFERENCIA
              THEN (SELECT DECODE(FLE.CDPAIS,'0055',FLE.SGESTADO,'XX') FROM ENDEFILI FLE WHERE FLE.IDTPENDEFILI = 'P' AND FLE.CDFILIAL = N.CDFILIRECE)
            WHEN ((N.IDENTRSAIDOP = 'S') AND (N.CDFILIEMIT IS NOT NULL)) --SAIDA DE TRANSFERENCIA
              THEN (SELECT DECODE(FLS.CDPAIS,'0055',FLS.SGESTADO,'XX') FROM ENDEFILI FLS WHERE FLS.IDTPENDEFILI = 'P' AND FLS.CDFILIAL = N.CDFILIEMIT)
            ELSE
              '' END UF,
           CASE
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDFORNECED IS NOT NULL)) --COMPRA
              THEN (SELECT MAX(RPAD(NVL(M.CDMUNICIBGE,' '),'7',' ')) FROM ENDEFORN EF, MUNICIPIO M WHERE EF.CDPAIS = M.CDPAIS AND EF.SGESTADO = M.SGESTADO AND EF.CDMUNICIPIO = M.CDMUNICIPIO AND EF.IDTPENDEFORN = 'P' AND EF.CDFORNECED = N.CDFORNECED)
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDCLIENTE IS NOT NULL)) --DEVOLUCAO COMPRA
              THEN (SELECT MAX(RPAD(NVL(M.CDMUNICIBGE,' '),'7',' ')) FROM ENDECLIE ECD,  MUNICIPIO M WHERE ECD.CDPAIS = M.CDPAIS AND ECD.SGESTADO = M.SGESTADO AND ECD.CDMUNICIPIO = M.CDMUNICIPIO AND ECD.IDTPENDECLIE = 'P' AND ECD.CDCLIENTE = N.CDCLIENTE)
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDFILIRECE IS NOT NULL)) --ENTRADA DE TRANSFERENCIA
              THEN (SELECT MAX(RPAD(NVL(M.CDMUNICIBGE,' '),'7',' ')) FROM ENDEFILI FLE, MUNICIPIO M WHERE FLE.CDPAIS = M.CDPAIS AND FLE.SGESTADO = M.SGESTADO AND FLE.CDMUNICIPIO = M.CDMUNICIPIO AND FLE.IDTPENDEFILI = 'P' AND FLE.CDFILIAL = N.CDFILIRECE)
            WHEN (N.IDENTRSAIDOP = 'S')  --SAIDAS
              THEN (SELECT MAX(RPAD(NVL(M.CDMUNICIBGE,' '),'7',' ')) FROM ENDEFILI FLS, MUNICIPIO M WHERE FLS.CDPAIS = M.CDPAIS AND FLS.SGESTADO = M.SGESTADO AND FLS.CDMUNICIPIO = M.CDMUNICIPIO AND FLS.IDTPENDEFILI = 'P' AND FLS.CDFILIAL = N.CDFILIAL)
            ELSE
              '' END IBGE_ORIGEM,
          CASE
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDFORNECED IS NOT NULL)) --COMPRA
              THEN (SELECT LPAD(F.NRINSESTFORN,9,'0') FROM FORNECEDOR F WHERE F.CDFORNECED = N.CDFORNECED)
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDCLIENTE IS NOT NULL)) --DEVOLUCAO COMPRA
              THEN (SELECT LPAD(C.NRINSESTCLIE,9,'0') FROM CLIENTE C WHERE C.CDCLIENTE = N.CDCLIENTE)
            WHEN ((N.IDENTRSAIDOP = 'E') AND (N.CDFILIRECE IS NOT NULL)) --ENTRADA DE TRANSFERENCIA
              THEN (SELECT LPAD(FL.CDINSCESTA,9,'0') FROM FILIAL FL WHERE FL.CDFILIAL = N.CDFILIRECE)
            WHEN (N.IDENTRSAIDOP = 'S')  --SAIDAS
              THEN (SELECT LPAD(FL.CDINSCESTA,9,'0') FROM FILIAL FL WHERE FL.CDFILIAL = N.CDFILIAL)
            ELSE
              '' END IE_ORIGEM,
           N.NRNOTAFISC NOTA_FISCAL
        FROM
          ITEMPROIMPOS IT,
          NOTAFISCAL N,
          --IMPOSTO IP,
          (
          SELECT
            DISTINCT IMPOSTONF.CDFILIAL,IMPOSTONF.NRLANCTONF,IMPOSTONF.CDIMPOSTO,L.CDLAYOUTLMAG
          FROM
            IMPOSTONF ,
            CFOPLIVROMAG L
          WHERE
            IMPOSTONF.CDFISCOPERNF  = L.CDCFOPLMAG
            AND L.CDLIVROMAG    = P_LIVRO
            AND IMPOSTONF.NRSEQUMAPAPU = C_MAPAS.NRSEQUMAPAPU
          ) I
        WHERE
          /*IP.CDIMPOSTO = IT.CDIMPOSTO
          AND IP.CDIMPOSTONFE = '00'
          AND*/ IT.NRITPROIMPOS = '1'
          AND I.CDFILIAL   = IT.CDFILIAL
          AND I.NRLANCTONF = IT.NRLANCTONF
          AND I.CDIMPOSTO  = IT.CDIMPOSTO
          AND I.NRLANCTONF = N.NRLANCTONF
          AND I.CDFILIAL   = N.CDFILIAL
        ) A
      ORDER BY CDFILIAL,NRLANCTONF,NRSEQITPROD;
   END LOOP;


 /* RAISE_APPLICATION_ERROR(-20501,'MAPAS:'||V_MAPAS, True);    */



-----------------------------------------/* SEGMENTO CD - ARQUIVO_DIF_TO_CD */-----------------------------------------------
   V_CONTREG := 0;
   ARQUIVO_DIF_TO_CD(P_CDINSCESTA,V_PERIODO,P_NRSEQARQMAG,
                     V_CONTREG,V_REG_C_K);

   V_CONTTOT := NVL(V_CONTTOT,0) + NVL(V_CONTREG,0);
----------------------------------------/* SEGMENTO ED - ARQUIVO_DIF_TO_EF */------------------------------------------------
   V_CONTREG := 0;
   ARQUIVO_DIF_TO_EF(P_CDINSCESTA,V_PERIODO,
                     P_NRSEQARQMAG,
                     V_CONTREG,V_REG_E_K);

   V_CONTTOT := NVL(V_CONTTOT,0) + NVL(V_CONTREG,0);
---------------------------------------/* SEGMENTO G - ARQUIVO_DIF_TO_G */---------------------------------------------------
   IF (
      (P_IECENTRAL = 'S')
      OR
      (V_CNAE in ('0151201','0155501','0155502','0322101','1011201','1012101','1012103','1020101','1041400','1042200',
                  '1043100','1051100','1052000','1053800','1061901','1062700','1063500','1064300','1065101','1065102',
                  '1065103','1066000','1069400','1071600','1072401','1081301','1081302','1091101','1091102','1092900',
                  '1093701','1093702','1094500','1095300','1096100','1099601','1099602','1099603','1099604','1099605',
                  '1099606','1099607','1099699','1099699','1931400','1932200','2013401','2013402','2229301','3511501',
                  '3512300','3513100','3514000','3520401','3520402','3600601','4622200','4623101','4623102','4623103',
                  '4623106','4623199','4631100','4632001','4632002','4632003','4633801','4633802','4634601','4634602',
                  '4634603','4634699','4635401','4635402','4635403','0635499','4636201','4636202','4637101','4637102',
                  '4637103','4637104','4637105','4637106','4637107','4637199','4639701','4639702','4641901','4641902',
                  '4641903','0642701','4642702','4643501','4643502','4644301','4644302','4645101','4645102','4645103',
                  '4646001','4646002','4647801','4647802','4649401','4649402','4649403','4649404','4649405','4649406',
                  '4649407','4649408','4649409','4649410','4649499','4651601','4651602','4652400','4661300','4662100',
                  '4663000','4664800','4665600','4669901','4669999','4671100','4672900','4673700','4674500','4679601',
                  '4679602','4679603','4679604','4679699','4681801','4681802','4681803','4681804','4681805','4682600',
                  '4683400','4684201','4684202','4684299','4685100','4686901','4686902','4687701','4687702','4687703',
                  '4689301','4689302','4689399','4691500','4692300','4693100','4911600','4912401','4912402','4912403',
                  '4921302','4922101','4922102','4922103','4929901','4929902','4929904','4930202','4930203','4930204',
                  '5021102','5022002','5091202','5111100','5112901','5112999','5120000','5310501','5310502','5320201',
                  '5320202','5822101','5822102','6110801','6110803','6110899','6120501','0120502','6120599','6130200',
                  '6141800','6142600','6143400','6190601','6190602','6190699'))
      ) THEN
     V_CONTREG := 0;
     ARQUIVO_DIF_TO_G (P_CDINSCESTA,V_PERIODO,
                       P_NRSEQARQMAG,
                       V_CONTREG);

     V_CONTTOT := NVL(V_CONTTOT,0) + NVL(V_CONTREG,0);
   END IF;
----------------------------------------/* SEGMENTO HI - ARQUIVO_DIF_TO_HI */------------------------------------------------
   V_CONTREG := 0;
   ARQUIVO_DIF_TO_HI(P_CDINSCESTA,V_PERIODO,
                     P_NRSEQARQMAG,
                     V_CONTREG);

   V_CONTTOT := NVL(V_CONTTOT,0) + NVL(V_CONTREG,0);
---------------------------------------/* SEGMENTO J - ARQUIVO_DIF_TO_J */---------------------------------------------------
   V_CONTREG := 0;
   ARQUIVO_DIF_TO_J (P_CDEMPRESA,P_CDINSCESTA,V_PERIODO,
                     P_DTINICIO,P_DTFIM,P_NRSEQARQMAG,
                     V_CONTREG);

   V_CONTTOT := NVL(V_CONTTOT,0) + NVL(V_CONTREG,0);
---------------------------------------/* SEGMENTO K - ARQUIVO_DIF_TO_K */---------------------------------------------------
   IF ((V_REG_C_K = 'S') OR (V_REG_E_K = 'S')) THEN
     V_CONTREG := 0;
     ARQUIVO_DIF_TO_K (P_CDEMPRESA,P_CDINSCESTA,V_PERIODO,
                       P_LIVRO,P_DTINICIO,P_DTFIM,P_NRSEQARQMAG,P_IECENTRAL,
                       V_CONTREG);

     V_CONTTOT := NVL(V_CONTTOT,0) + NVL(V_CONTREG,0);
   END IF;
--------------------------------------/* SEGMENTO Z */-----------------------------------------------------------------------
    INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
    VALUES
      ('99'||LPAD(RTRIM(V_CONTTOT+1), 8, '0'),
       'Z'||--Z1 Segmento
       LPAD(P_CDINSCESTA,9,'0')||--Z2 Inscricao Estadual
       V_PERIODO||--Z3 Periodo de referencia
       LPAD(NVL(V_CONTTOT,'0'),3,'0')--Z4 Total de registro que compoe a declaracao
      ,
      P_NRSEQARQMAG);

END;
/