CREATE OR REPLACE FUNCTION SPED_BLOCOB(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_CDINSCMUNI   IN EMPRESA.CDINSCMUNI%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_VERSAO       IN VARCHAR2) RETURN VARCHAR
AS
   V_SEQUPROD        NUMBER;
   V_CONTREGB        NUMBER;
   V_MOVIMENT        NUMBER;
   V_COD_PAR         VARCHAR2(100);
   V_SGESTADO        VARCHAR2(02);
   TIPOOPER_COMPL_S  VARCHAR2(02);
   TIPOOPER_COMPL_E  VARCHAR2(02);
   V_COD_OBS         VARCHAR2(1000);
   V_PARTICIPANTE    VARCHAR2(50);
BEGIN
   V_SEQUPROD := 0;
   IF P_VERSAO >= '013' THEN
      BEGIN
         SELECT SGESTADO
           INTO V_SGESTADO
           FROM INSCRESTAD
          WHERE CDEMPRESA  = P_CDEMPRESA
            AND CDINSCESTA = P_CDINSCESTA;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_SGESTADO := NULL;
      END;

      IF V_SGESTADO = 'DF' THEN

         /*BUSCA O TIPO DE OPERACAO DE COMPLEMENTO */
         BEGIN
            SELECT MAX(TIPOOPER_S), MAX(TIPOOPER_E)
              INTO TIPOOPER_COMPL_S, TIPOOPER_COMPL_E
              FROM (SELECT DECODE(IDENTRSAIDOP,'S',CDTIPOOPER,NULL) AS TIPOOPER_S,
                           DECODE(IDENTRSAIDOP,'E',CDTIPOOPER,NULL) AS TIPOOPER_E
                      FROM TIPOOPERAC
                     WHERE UPPER(NMTIPOOPER) LIKE '%NF COMPLEMENTO%'
                       AND CDTIPOOPER <> '03');
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               TIPOOPER_COMPL_S := NULL;
               TIPOOPER_COMPL_E := NULL;
         END;

         /*REGISTRO B020 - NOTA FISCAL (CODIGO 01), NOTA FISCAL DE SERVICOS (CODIGO 03),NOTA FISCAL DE SERVICOS AVULSA (CODIGO 3B),
           NOTA FISCAL DE PRODUTOR (CODIGO 04), CONHECIMENTO DE TRANSPORTE RODOVIARIO DE CARGAS (CODIGO 08),
           NF-e (CODIGO 55) e NFC-e (CODIGO 65) e NF3-e (CODIGO 66).*/
         FOR C_REGB020 IN (SELECT '|B020'                                              ||'|'||
                                  IND_OPER                                             ||'|'||
                                  IND_EMIT                                             ||'|' AS DADOS1,
                                  --DECODE(COD_SIT,'02','','04','','05','',REPLACE(REPLACE(REPLACE(REPLACE(COD_PART,'.'),'-'),'/'),' ')) ||'|'||
                                  COD_MOD                                              ||'|'||
                                  COD_SIT                                              ||'|'||
                                  SER                                                  ||'|'||
                                  NUM_DOC                                              ||'|'||
                                  DECODE(COD_SIT,'05','',CHV_NFE)                      ||'|'||
                                  DECODE(COD_SIT,'02','','04','','05','',DT_DOC)       ||'|'||
                                  DECODE(COD_SIT,'02','','04','','05','',M.CDMUNICIBGE)||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_CONT)),2),'999999999990.00')),'.',',')      ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_MAT_TERC)),2),'999999999990.00')),'.',',')  ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_SUB)),2),'999999999990.00')),'.',',')       ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_ISNT_ISS)),2),'999999999990.00')),'.',',')  ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_DED_BC)),2),'999999999990.00')),'.',',')    ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_BC_ISS)),2),'999999999990.00')),'.',',')    ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_BC_ISS_RT)),2),'999999999990.00')),'.',',') ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_ISS_RT)),2),'999999999990.00')),'.',',')    ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(COD_SIT,'02','','04','','05','',SUM(VL_ISS)),2),'999999999990.00')),'.',',')       ||'|'||
                                  DECODE(COD_SIT,'02','','04','','05','',COD_INF_OBS)  ||'|' AS ARQ_B020,
                                  COD_SIT, COD_PART, CDFILIAL, NRLANCTONF
                             FROM MUNICIPIO M,
                                  (SELECT DECODE(NF.IDENTRSAIDOP,'E','0','1') AS IND_OPER,
                                          DECODE(NF.IDENTRSAIDOP, 'E', DECODE(NF.IDSTATUSNFE,'A','0',DECODE(NF.IDSTATUS,'E','0','1')), '0') AS IND_EMIT,
                                          NF.IDORIGDESTNF||DECODE(NF.IDORIGDESTNF,'F',NF.CDFORNECED,'C',NF.CDCLIENTE,'L',DECODE(NF.IDENTRSAIDOP,'E',NF.CDFILIEMIT,NF.CDFILIRECE)) AS COD_PART,
                                          NF.CDMODDOCFISC AS COD_MOD,
                                          DECODE(NF.IDSTATUSNFE,'D','04',DECODE(NF.IDSTATUSNFE,'I','05',DECODE(IDSTATUS,'C','02',DECODE(NF.CDTIPOOPER,'03','06',TIPOOPER_COMPL_E,'06',TIPOOPER_COMPL_S,'06','00')))) AS COD_SIT,
                                          CASE WHEN LENGTH(NF.NRACESSONFE) = '44' THEN SUBSTR(NRACESSONFE,23,3) ELSE REPLACE(REPLACE(DECODE(NF.IDENTRSAIDOP, 'E', NF.CDSERIEENTR, NF.CDSERIE),'-'),'.') END AS SER,
                                          DECODE(NF.IDENTRSAIDOP,'E',LTRIM(NF.NRNOTAFISC,'0'), DECODE(NF.NRACESSONFE, '', LTRIM(REPLACE(LTRIM(NF.NRNOTAFISC, '0'),' ')),DECODE(NF.IDSTATUS,'C',LTRIM(NF.NRNOTAFISC,'0'),LTRIM(NF.NRNOTAFISCALELE,'0')))) AS NUM_DOC,
                                          NF.NRACESSONFE AS CHV_NFE, TO_CHAR(DECODE(NF.IDENTRSAIDOP,'E',NF.DTENTRSAID,NF.DTEMISSAO),'DDMMYYYY') AS DT_DOC,
                                          ROUND(IP.VRCONTABILNF,2) AS VL_CONT, 0 AS VL_MAT_TERC, 0 AS VL_SUB,
                                          ROUND(DECODE(IP.CDFISCALNF,'1',0,IP.VRCONTABILNF),2) AS VL_ISNT_ISS, 0 AS VL_DED_BC,
                                          CASE WHEN MP.CDIMPOSTO IN ('30','31') THEN ROUND(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),2) ELSE 0 END AS VL_BC_ISS,
                                          CASE WHEN MP.CDIMPOSTO IN ('35','36') THEN ROUND(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),2) ELSE 0 END AS VL_BC_ISS_RT,
                                          CASE WHEN MP.CDIMPOSTO IN ('35','36') THEN ROUND(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),2)  ELSE 0 END AS VL_ISS_RT,
                                          CASE WHEN MP.CDIMPOSTO IN ('30','31') THEN ROUND(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF,0),2)  ELSE 0 END AS VL_ISS,
                                          '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF,
                                          DECODE(NF.IDENTRSAIDOP,'E',NVL(NF.SGESTADO,EF.SGESTADO),NVL(DS.SGESTADOPRESSERV,EC.SGESTADO)) AS SGESTADO,
                                          DECODE(NF.IDENTRSAIDOP,'E',NVL(NF.CDMUNICIPIO,EF.CDMUNICIPIO),NVL(DS.CDMUNICIPRESSERV,EC.CDMUNICIPIO)) AS CDMUNICIPIO
                                     FROM MAPAAPURAC MP, NOTAFISCAL NF, IMPOSTONF IP, DADOSERVPRES DS, ENDEFILI EF, ENDECLIE EC
                                    WHERE MP.CDEMPRESA       = P_CDEMPRESA
                                      AND MP.CDINSCMUNI      = P_CDINSCMUNI
                                      AND MP.CDIMPOSTO       IN ('30','31','35','36')
                                      AND MP.DTINIPERIODO    = P_DTINICIAL
                                      AND MP.DTFIMPERIODO    = P_DTFINAL
                                      AND MP.NRSEQUMAPAPU    = IP.NRSEQUMAPAPU
                                      AND IP.CDFILIAL        = NF.CDFILIAL
                                      AND IP.NRLANCTONF      = NF.NRLANCTONF
                                      AND NF.CDMODDOCFISC    IN ('01','03','3B','55', '66')
                                      AND NF.CDFILIAL        = DS.CDFILIAL(+)
                                      AND NF.NRLANCTONF      = DS.NRLANCTONF(+)
                                      AND NF.CDFILIRECE      = EF.CDFILIAL(+)
                                      AND EF.IDTPENDEFILI(+) = 'P'
                                      AND NF.CDCLIENTE       = EC.CDCLIENTE(+)
                                      AND EC.IDTPENDECLIE(+) = 'P') N
                            WHERE M.CDPAIS(+)   = '0055'
                              AND N.SGESTADO    = M.SGESTADO(+)
                              AND N.CDMUNICIPIO = M.CDMUNICIPIO(+)
                            GROUP BY IND_OPER, IND_EMIT, COD_PART, COD_MOD, COD_SIT, SER, NUM_DOC, CHV_NFE,
                                  DT_DOC, M.CDMUNICIBGE, COD_INF_OBS, CDFILIAL, NRLANCTONF) LOOP

            V_PARTICIPANTE := '';
            /* CODIGO DO PARTICIPANTE */
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_PAR
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REGB020.COD_PART
               AND CDREGISTRO = '0150';

            IF (V_COD_PAR IS NULL) AND (C_REGB020.COD_PART IS NOT NULL) THEN
               V_PARTICIPANTE := RETORNA_PARTICIPANTE_SPED(C_REGB020.COD_PART);
               IF V_PARTICIPANTE IS NULL THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                  VALUES
                    ('0150', V_SEQUPROD, C_REGB020.COD_PART);
               END IF;
            END IF;

            V_PARTICIPANTE := CASE WHEN C_REGB020.COD_SIT IN ('02','04','05') THEN '|'
                                   ELSE REPLACE(REPLACE(REPLACE(REPLACE(NVL(V_PARTICIPANTE,C_REGB020.COD_PART),'.'),'-'),'/'),' ')||'|' END;

            V_CONTREGB := NVL(V_CONTREGB,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('12'||LPAD(RTRIM(V_CONTREGB),8,'0'), C_REGB020.DADOS1||V_PARTICIPANTE||C_REGB020.ARQ_B020, P_NRSEQARQMAG);

            /*REGISTRO B025: DETALHAMENTO POR COMBINACAO DE ALIQUOTA E ITEM DA LISTA DE SERVICOS DA LC 116/2003)*/
            FOR C_REGB025 IN (SELECT '|B025'                                                                                                               ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(SUM(ROUND(IT.VRCONTITIMP,2)),'999999999990.00')),'.',',')                                                ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IT.CDFISCALIT,'1',CASE WHEN IM.CDIMPOSTONFE IN ('30','31') THEN ROUND(IT.VRBASECALCIT,2) ELSE 0 END,0)),'999999999990.00')),'.',',')                   ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(CASE WHEN IM.CDIMPOSTONFE IN ('30','31') THEN IT.VRALIQITEM ELSE 0 END,'999999999990.00')),'.',',')                                                      ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IT.CDFISCALIT,'1',CASE WHEN IM.CDIMPOSTONFE IN ('30','31') THEN ROUND(IT.VRIMPOSTOIT,2) ELSE 0 END,0)),'999999999990.00')),'.',',')                    ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IT.CDFISCALIT,'1',0, CASE WHEN IM.CDIMPOSTONFE IN ('30','31') THEN ROUND(IT.VRCONTITIMP,2) ELSE 0 END)),'999999999990.00')),'.',',') ||'|'||
                                     CLASSE_CODTRIBPROD(PR.CDARVPROD,P_CDEMPRESA,P_CDINSCMUNI) ||'|' AS ARQ_B025
                                FROM ITEMPROIMPOS IT, IMPOSTO IM, ITEMPRODUTO IP, PRODUTO PR
                               WHERE IT.CDFILIAL     = C_REGB020.CDFILIAL
                                 AND IT.NRLANCTONF   = C_REGB020.NRLANCTONF
                                 AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                 AND IM.CDIMPOSTONFE IN ('30','31','35','36')
                                 AND IT.CDFILIAL     = IP.CDFILIAL
                                 AND IT.NRLANCTONF   = IP.NRLANCTONF
                                 AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                 AND IP.CDPRODUTO    = PR.CDPRODUTO
                               GROUP BY CASE WHEN IM.CDIMPOSTONFE IN ('30','31') THEN IT.VRALIQITEM ELSE 0 END, CLASSE_CODTRIBPROD(PR.CDARVPROD,P_CDEMPRESA,P_CDINSCMUNI)) LOOP

               V_CONTREGB := NVL(V_CONTREGB,0) + 1;
               INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                 ('12'||LPAD(RTRIM(V_CONTREGB),8,'0'), C_REGB025.ARQ_B025, P_NRSEQARQMAG);
            END LOOP;
         END LOOP;

         /*REGISTRO B030: NOTA FISCAL DE SERVICOS SIMPLIFICADA (CODIGO 3A)*/
         FOR C_REGB030 IN (SELECT '|B030'                                                                      ||'|'||
                                  COD_MOD                                                                      ||'|'||
                                  SER                                                                          ||'|'||
                                  NUM_DOC                                                                      ||'|'||
                                  NUM_DOC                                                                      ||'|'||
                                  DT_DOC                                                                       ||'|'||
                                  QTD_CANC                                                                     ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_CONT),2),'999999999990.00')),'.',',')     ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_ISNT_ISS),2),'999999999990.00')),'.',',') ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_BC_ISS),2),'999999999990.00')),'.',',')   ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_ISS),2),'999999999990.00')),'.',',')      ||'|'||
                                  COD_INF_OBS                                                                  ||'|' AS ARQ_B030,
                                  CDFILIAL, NRLANCTONF
                             FROM (SELECT NF.CDMODDOCFISC AS COD_MOD,
                                          REPLACE(REPLACE(NF.CDSERIE,'-'),'.') AS SER,
                                          LTRIM(REPLACE(LTRIM(NF.NRNOTAFISC, '0'),' ')) AS NUM_DOC,
                                          TO_CHAR(NF.DTEMISSAO,'DDMMYYYY') AS DT_DOC,
                                          DECODE(IDSTATUS,'C','1','0') AS QTD_CANC,
                                          ROUND(IP.VRCONTABILNF,2) AS VL_CONT,
                                          ROUND(DECODE(IP.CDFISCALNF,'1',0,IP.VRCONTABILNF),2) AS VL_ISNT_ISS,
                                          ROUND(DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),2) AS VL_BC_ISS,
                                          ROUND(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF, 0),2) AS VL_ISS,
                                          '' AS COD_INF_OBS, NF.CDFILIAL, NF.NRLANCTONF
                                     FROM MAPAAPURAC MP, NOTAFISCAL NF, IMPOSTONF IP
                                    WHERE MP.CDEMPRESA    = P_CDEMPRESA
                                      AND MP.CDINSCMUNI   = P_CDINSCMUNI
                                      AND MP.CDIMPOSTO    = '31'
                                      AND MP.DTINIPERIODO = P_DTINICIAL
                                      AND MP.DTFIMPERIODO = P_DTFINAL
                                      AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                                      AND IP.CDFILIAL     = NF.CDFILIAL
                                      AND IP.NRLANCTONF   = NF.NRLANCTONF
                                      AND NF.CDMODDOCFISC = '3A')
                            GROUP BY COD_MOD, SER, NUM_DOC, DT_DOC, QTD_CANC, COD_INF_OBS, CDFILIAL, NRLANCTONF) LOOP

            V_CONTREGB := NVL(V_CONTREGB,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('12'||LPAD(RTRIM(V_CONTREGB),8,'0'), C_REGB030.ARQ_B030, P_NRSEQARQMAG);

            /*REGISTRO B035: DETALHAMENTO POR COMBINACAO DE ALIQUOTA E ITEM DA LISTA DE SERVICOS DA LC 116/2003)*/
            FOR C_REGB035 IN (SELECT '|B035'                                                                                                           ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(SUM(ROUND(IT.VRCONTITIMP,2)),'999999999990.00')),'.',',')                                   ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),2)),'999999999990.00')),'.',',')      ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(IT.VRALIQITEM,'999999999990.00')),'.',',')                                                  ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0),2)),'999999999990.00')),'.',',')       ||'|'||
                                     REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',0,IT.VRCONTITIMP),2)),'999999999990.00')),'.',',')       ||'|'||
                                     CLASSE_CODTRIBPROD(PR.CDARVPROD,P_CDEMPRESA,P_CDINSCMUNI) ||'|' AS ARQ_B035
                                FROM ITEMPROIMPOS IT, IMPOSTO IM, ITEMPRODUTO IP, PRODUTO PR
                               WHERE IT.CDFILIAL     = C_REGB030.CDFILIAL
                                 AND IT.NRLANCTONF   = C_REGB030.NRLANCTONF
                                 AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                 AND IM.CDIMPOSTONFE = '31'
                                 AND IT.CDFILIAL     = IP.CDFILIAL
                                 AND IT.NRLANCTONF   = IP.NRLANCTONF
                                 AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                 AND IP.CDPRODUTO    = PR.CDPRODUTO
                               GROUP BY IT.VRALIQITEM, CLASSE_CODTRIBPROD(PR.CDARVPROD,P_CDEMPRESA,P_CDINSCMUNI)) LOOP

               V_CONTREGB := NVL(V_CONTREGB,0) + 1;
               INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                 ('12'||LPAD(RTRIM(V_CONTREGB),8,'0'), C_REGB035.ARQ_B035, P_NRSEQARQMAG);
            END LOOP;
         END LOOP;

         /*REGISTRO B420: TOTALIZACAO DOS VALORES DE SERVICOS PRESTADOS POR COMBINACAO DE ALIQUOTA E ITEM DA LISTA DE SERVICOS DA LC 116/2003*/
         FOR C_REGB420 IN (SELECT '|B420'                                                                                              ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(IT.VRCONTITIMP,2)),'999999999990.00')),'.',',')                               ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),2)),'999999999990.00')),'.',',')  ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(IT.VRALIQITEM,'999999999990.00')),'.',',')                                     ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',0,IT.VRCONTITIMP),2)),'999999999990.00')),'.',',')   ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0),2)),'999999999990.00')),'.',',')   ||'|'||
                                  CLASSE_CODTRIBPROD(PR.CDARVPROD,P_CDEMPRESA,P_CDINSCMUNI) ||'|' AS ARQ_B420
                             FROM NOTAFISCAL NF, ITEMPROIMPOS IT, IMPOSTO IM, ITEMPRODUTO IP, PRODUTO PR,
                                  (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF, I.CDIMPOSTO
                                     FROM MAPAAPURAC M, IMPOSTONF I
                                    WHERE M.CDEMPRESA    = P_CDEMPRESA
                                      AND M.CDINSCMUNI   = P_CDINSCMUNI
                                      AND M.CDIMPOSTO    IN ('31','36')
                                      AND M.DTINIPERIODO = P_DTINICIAL
                                      AND M.DTFIMPERIODO = P_DTFINAL
                                      AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU) MP
                            WHERE MP.CDFILIAL     = NF.CDFILIAL
                              AND MP.NRLANCTONF   = NF.NRLANCTONF
                              AND NF.CDMODDOCFISC IN ('01','03','3B','55','3A')
                              AND IT.CDFILIAL     = NF.CDFILIAL
                              AND IT.NRLANCTONF   = NF.NRLANCTONF
                              AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                              AND IT.CDIMPOSTO    = MP.CDIMPOSTO
                              AND IT.CDFILIAL     = IP.CDFILIAL
                              AND IT.NRLANCTONF   = IP.NRLANCTONF
                              AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                              AND IP.CDPRODUTO    = PR.CDPRODUTO
                              AND NF.IDENTRSAIDOP = 'S'
                            GROUP BY IT.VRALIQITEM, CLASSE_CODTRIBPROD(PR.CDARVPROD,P_CDEMPRESA,P_CDINSCMUNI)) LOOP
            V_CONTREGB := NVL(V_CONTREGB,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('12'||LPAD(RTRIM(V_CONTREGB),8,'0'), C_REGB420.ARQ_B420, P_NRSEQARQMAG);
         END LOOP;

         /*REGISTRO B440: TOTALIZACAO DOS VALORES RETIDOS*/
         FOR C_REGB440 IN (SELECT '|B440'                                                                      ||'|'||
                                  '1'                                                                          ||'|' AS PARTE1,
                                  --REPLACE(REPLACE(REPLACE(REPLACE('C'||NF.CDCLIENTE,'.'),'-'),'/'),' ') ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(IP.VRCONTABILNF,2)),'999999999990.00')),'.',',')      ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IM.CDIMPOSTONFE,'36',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0),2)),'999999999990.00')),'.',',') ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IM.CDIMPOSTONFE,'36',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF, 0),0),2)),'999999999990.00')),'.',',') ||'|' AS ARQ_B440,
                                  'C'||NF.CDCLIENTE AS COD_PART
                             FROM MAPAAPURAC MP, NOTAFISCAL NF, IMPOSTONF IP, IMPOSTO IM, CLIENTE CL
                            WHERE MP.CDEMPRESA    = P_CDEMPRESA
                              AND MP.CDINSCMUNI   = P_CDINSCMUNI
                              AND MP.CDIMPOSTO    IN ('31','36')
                              AND MP.DTINIPERIODO = P_DTINICIAL
                              AND MP.DTFIMPERIODO = P_DTFINAL
                              AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                              AND IP.CDFILIAL     = NF.CDFILIAL
                              AND IP.NRLANCTONF   = NF.NRLANCTONF
                              AND NF.CDMODDOCFISC IN ('01','03','3B','55','3A')
                              AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                              AND NF.IDENTRSAIDOP = 'S'
                              AND NF.CDCLIENTE    = CL.CDCLIENTE
                            GROUP BY NF.CDCLIENTE
                            UNION ALL
                           SELECT '|B440'                                                                      ||'|'||
                                  '0'                                                                          ||'|' AS PARTE1,
                                  --REPLACE(REPLACE(REPLACE(REPLACE('F'||NF.CDFORNECED,'.'),'-'),'/'),' ')     ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(IP.VRCONTABILNF,2)),'999999999990.00')),'.',',')      ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IM.CDIMPOSTONFE,'35',DECODE(IP.CDFISCALNF,'1',IP.VRBASECALCNF,0),0),2)),'999999999990.00')),'.',',') ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(DECODE(IM.CDIMPOSTONFE,'35',DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF, 0),0),2)),'999999999990.00')),'.',',') ||'|' AS ARQ_B440,
                                  'F'||NF.CDFORNECED AS COD_PART
                             FROM MAPAAPURAC MP, NOTAFISCAL NF, IMPOSTONF IP, IMPOSTO IM, FORNECEDOR FO
                            WHERE MP.CDEMPRESA    = P_CDEMPRESA
                              AND MP.CDINSCMUNI   = P_CDINSCMUNI
                              AND MP.CDIMPOSTO    IN ('30','35')
                              AND MP.DTINIPERIODO = P_DTINICIAL
                              AND MP.DTFIMPERIODO = P_DTFINAL
                              AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                              AND IP.CDFILIAL     = NF.CDFILIAL
                              AND IP.NRLANCTONF   = NF.NRLANCTONF
                              AND NF.CDMODDOCFISC IN ('01','03','3B','55','3A')
                              AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                              AND NF.IDENTRSAIDOP = 'E'
                              AND NF.CDFORNECED   = FO.CDFORNECED
                            GROUP BY NF.CDFORNECED) LOOP

            V_PARTICIPANTE := '';
            /* CODIGO DO PARTICIPANTE */
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_PAR
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REGB440.COD_PART
               AND CDREGISTRO = '0150';

            IF (V_COD_PAR IS NULL) AND (C_REGB440.COD_PART IS NOT NULL) THEN
               V_PARTICIPANTE := RETORNA_PARTICIPANTE_SPED(C_REGB440.COD_PART);
               IF V_PARTICIPANTE IS NULL THEN
                  V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED
                    (CDREGISTRO, CDCONTREGI, CDCHAVEREG)
                  VALUES
                    ('0150', V_SEQUPROD, C_REGB440.COD_PART);
               END IF;
            END IF;

            V_PARTICIPANTE := REPLACE(REPLACE(REPLACE(REPLACE(NVL(V_PARTICIPANTE,C_REGB440.COD_PART),'.'),'-'),'/'),' ')||'|';

            V_CONTREGB := NVL(V_CONTREGB,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('12'||LPAD(RTRIM(V_CONTREGB),8,'0'), C_REGB440.PARTE1||V_PARTICIPANTE||C_REGB440.ARQ_B440, P_NRSEQARQMAG);
         END LOOP;

         FOR C_REGB460 IN (SELECT '|B460'                                                                                     || '|' ||
                                  IT.IDDEDUCAO                                                                                || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(IT.VRITEMMAPR,0.00),2)), '999999999990.00')), '.', ',') || '|' ||
                                  IT.NRPROCESSO                                                                               || '|' ||
                                  DECODE(IT.NRPROCESSO,'','',IT.IDORIGEMPROC)                                                 || '|' ||
                                  IT.DSPROCESSO                                                                               || '|' ||
                                  IT.IDTIPOITMAPR||SUBSTR(LPAD(IT.NRSEQITMAPR,10,'0'),7,4)                                    || '|' ||
                                  DECODE(MP.CDIMPOSTO,'31','0','35','1')                                                      || '|' AS ARQ_B460,
                                  IT.IDTIPOITMAPR||SUBSTR(LPAD(IT.NRSEQITMAPR,10,'0'),7,4) AS COD_OBS, IT.DSITEMMAPR AS DS_OBS
                             FROM ITMAPARECUP IT, MAPAAPURAC MP
                            WHERE MP.CDEMPRESA    = P_CDEMPRESA
                              AND MP.CDINSCMUNI   = P_CDINSCMUNI
                              AND MP.DTINIPERIODO = P_DTINICIAL
                              AND MP.DTFIMPERIODO = P_DTFINAL
                              AND MP.CDIMPOSTO    IN ('31','35')
                              AND MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
                              AND IT.IDTIPOITMAPR IN ('DD','ED')
                            GROUP BY IT.IDDEDUCAO, IT.NRPROCESSO, DECODE(IT.NRPROCESSO,'','',IT.IDORIGEMPROC), IT.DSPROCESSO,
                                  IT.IDTIPOITMAPR, IT.NRSEQITMAPR, SUBSTR(LPAD(IT.NRSEQITMAPR,10,'0'),7,4),
                                  DECODE(MP.CDIMPOSTO,'31','0','35','1'), IT.DSITEMMAPR) LOOP

            V_CONTREGB := NVL(V_CONTREGB,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('12'||LPAD(RTRIM(V_CONTREGB),8,'0'), C_REGB460.ARQ_B460, P_NRSEQARQMAG);

            V_COD_OBS := NULL;
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_OBS
              FROM TMP_REGDADOSSPED
             WHERE CDCHAVEREG = C_REGB460.COD_OBS
               AND CDREGISTRO = '0460';

            IF (V_COD_OBS IS NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
               VALUES
                  ('0460', V_SEQUPROD, C_REGB460.COD_OBS, C_REGB460.DS_OBS);
            END IF;

         END LOOP;

         FOR C_REGB470 IN (SELECT '|B470'                                                                               || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_CONT,0),       2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_MAT_TERC,0),   2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_MAT_PROP,0),   2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_SUB,0),        2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_ISNT,0),       2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_DED_BC,0),     2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_BC_ISS,0),     2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_BC_ISS_RT,0),  2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_ISS,0),        2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_ISS_RT,0),     2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_DED,0),        2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_ISS_REC,0),    2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_ISS_ST,0),     2)), '999999999990.00')), '.', ',') || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(SUM(ROUND(NVL(NF.VL_ISS_REC_UNI,0),2)), '999999999990.00')), '.', ',') || '|' AS ARQ_B470
                             FROM (SELECT SUM(ROUND(IT.VRCONTITIMP,2)) AS VL_CONT,
                                          0 AS VL_MAT_TERC,
                                          0 AS VL_MAT_PROP,
                                          0 AS VL_SUB,
                                          SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '31', 'C', '2')) + SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '31', 'C', '3')) AS VL_ISNT,
                                          SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '31', 'C', '2')) + SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '31', 'C', '3')) AS VL_DED_BC,
                                          SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '31', 'B', '1')) AS VL_BC_ISS,
                                          SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '36', 'B', '1')) AS VL_BC_ISS_RT,
                                          SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '31', 'I', '1')) AS VL_ISS,
                                          SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '36', 'I', '1')) AS VL_ISS_RT,
                                          IP.VRDEDUCAO AS VL_DED,
                                          CASE WHEN NVL(SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '31', 'I', '1')),0) - NVL(SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '36', 'I', '1')),0) - NVL(IP.VRDEDUCAO,0) > 0
                                               THEN NVL(SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '31', 'I', '1')),0) - NVL(SUM(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '36', 'I', '1')),0) - NVL(IP.VRDEDUCAO,0)
                                               ELSE 0 END AS VL_ISS_REC,
                                          0 AS VL_ISS_ST,
                                          0 AS VL_ISS_REC_UNI
                                     FROM NOTAFISCAL N, ITEMPROIMPOS IT, IMPOSTO IM,
                                          (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF, (SELECT SUM(VRITEMMAPR)
                                                                                        FROM ITMAPARECUP IT
                                                                                       WHERE IT.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                                                                                         AND IT.IDTIPOITMAPR IN ('DD','ED')) AS VRDEDUCAO
                                             FROM MAPAAPURAC M, IMPOSTONF I
                                            WHERE M.CDEMPRESA    = P_CDEMPRESA
                                              AND M.CDINSCMUNI   = P_CDINSCMUNI
                                              AND M.DTINIPERIODO = P_DTINICIAL
                                              AND M.DTFIMPERIODO = P_DTFINAL
                                              AND M.CDIMPOSTO    = '31'
                                              AND I.NRSEQUMAPAPU = M.NRSEQUMAPAPU) IP
                                    WHERE N.CDFILIAL        = IP.CDFILIAL
                                      AND N.NRLANCTONF      = IP.NRLANCTONF
                                      AND N.CDFILIAL        = IT.CDFILIAL
                                      AND N.NRLANCTONF      = IT.NRLANCTONF
                                      AND IT.CDIMPOSTO      = IM.CDIMPOSTO
                                      AND IM.CDIMPOSTONFE   = '31'
                                      AND N.IDENTRSAIDOP    = 'S'
                                      AND N.CDMODDOCFISC    IN ('01','03','55')
                                      AND NVL(IT.NRITPROIMPOS, '1') <> '3'
                                    GROUP BY IP.VRDEDUCAO
                                    UNION ALL
                                   SELECT 0 AS VL_CONT,
                                          0 AS VL_MAT_TERC,
                                          0 AS VL_MAT_PROP,
                                          0 AS VL_SUB,
                                          0 AS VL_ISNT,
                                          0 AS VL_DED_BC,
                                          0 AS VL_BC_ISS,
                                          0 AS VL_BC_ISS_RT,
                                          0 AS VL_ISS,
                                          0 AS VL_ISS_RT,
                                          0 AS VL_DED,
                                          0 AS VL_ISS_REC,
                                          SUM(ROUND(VRIMPOSTONF,2)) AS VL_ISS_ST,
                                          0 AS VL_ISS_REC_UNI
                                     FROM MAPAAPURAC M, IMPOSTONF I
                                    WHERE M.CDEMPRESA    = P_CDEMPRESA
                                      AND M.CDINSCMUNI   = P_CDINSCMUNI
                                      AND M.DTINIPERIODO = P_DTINICIAL
                                      AND M.DTFIMPERIODO = P_DTFINAL
                                      AND M.CDIMPOSTO    = '35'
                                      AND I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                                      AND I.CDFISCALNF   = '1')NF) LOOP

            V_CONTREGB := NVL(V_CONTREGB,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('12'||LPAD(RTRIM(V_CONTREGB),8,'0'), C_REGB470.ARQ_B470, P_NRSEQARQMAG);
         END LOOP;
      END IF;

      IF V_CONTREGB <> 0 THEN
         V_MOVIMENT := 0;
      ELSE
         V_MOVIMENT := 1;
      END IF;

      /* ABERTURA DO REGISTRO B */
      V_CONTREGB := NVL(V_CONTREGB,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('11'||LPAD('0', 8, '0'), '|B001'|| '|' ||V_MOVIMENT|| '|', P_NRSEQARQMAG);

      /* ENCERRAMENTO DO BLOCO B */
      V_CONTREGB := NVL(V_CONTREGB,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('17'||LPAD(RTRIM(V_CONTREGB), 8, '0'), '|B990'|| '|' ||V_CONTREGB|| '|', P_NRSEQARQMAG);
   END IF;

   RETURN(LPAD(V_CONTREGB,10,'0')||LPAD(V_SEQUPROD,10,'0'));
END;
/