CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCO9(
   P_CONTREGA    IN FLOAT,
   P_CONTREGC    IN NUMBER,
   P_CONTREGD    IN NUMBER,
   P_CONTBLC0    IN NUMBER,
   P_GERABCLP    IN VARCHAR,
   P_LIVROMAG    IN RELACPRODITGEN.CDLIVROMAG%TYPE,
   P_NRSEQARQMAG IN ARQMAGPISCOFINS.NRSEQARQMAG%TYPE,
   P_DTINICIAL   IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_IDFORAPER   IN NOTAFISCAL.IDENTRSAIDOP%TYPE) RETURN NUMBER

AS
   V_MOVIMENT     NUMBER;
   V_TOTREG9      NUMBER;
   V_CONTREG9     NUMBER;
   V_TOTREG       DOUBLE PRECISION;
   V_CONTREGA     DOUBLE PRECISION;
   V_0200         VARCHAR2(400);
   V_0150         VARCHAR2(4000);
   V_0145         VARCHAR2(500);
   V_UNID         VARCHAR2(400);
   V_PROD         VARCHAR2(400);
   V_CONTBLC0     NUMBER;
   V_ESTAB        NUMBER;
   V_CDCHAVEREG   VARCHAR2(4);
   V_DSCFOPPADRAO VARCHAR2(500);

BEGIN
   V_CONTBLC0 := P_CONTBLC0;
   V_CONTREGA := P_CONTREGA;

  /*--------------------------REGISTRO 0145 - BLOCO P IDENTIFICADOR DO REGIME ESPECIAL----------------------*/
   IF P_GERABCLP IN ('1','2') THEN
      SELECT MAX( '|0145'              ||'|'||
                  '1'                  ||'|'||
                  CDCAMPO1             ||'|'||
                  CDCAMPO2             ||'|'||
                  '0,00'               ||'|'||
                  ''                   ||'|') , CDESTAB
        INTO V_0145, V_ESTAB
        FROM TMP_REGDADOSSPED_PIS
       WHERE CDREGISTRO = '0145'
       GROUP BY CDESTAB;

      V_CONTBLC0 := NVL(V_CONTBLC0,0)+1;
      V_CONTREGA := NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('104'||LTRIM(TO_CHAR(V_ESTAB,'000'))||'00'||LPAD(RTRIM(V_CONTREGA),22, '0'), V_0145 ,P_NRSEQARQMAG);
   END IF;


  /*SELECIONA O BLOCO 0150 PARA O BLOCO A*/
  /*FOR C_REG0150A IN(SELECT CDCAMPO1, NVL(CDESTAB,0) AS CDESTAB
                      FROM TMP_REGDADOSSPED_PIS
                     WHERE CDREGISTRO = '0150'
                       AND CDCAMPO3 = 'A'
                       AND CDCHAVEREG IS NOT NULL)LOOP
        INSERT INTO ARQMAGPISCOFINS(NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
             VALUES(P_NRSEQARQMAG,'104'||C_REG0150A.CDESTAB||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REG0150A.CDCAMPO1);


    V_CONTREGA:=NVL(V_CONTREGA,0)+1;
    V_CONTBLC0:= NVL(V_CONTBLC0,0)+1;
  END LOOP;*/

  /*SELECIONA O REGISTRO 0150 */
   FOR C_0150 IN (SELECT CDCHAVEREG, NVL(CDESTAB,0) AS CDESTAB
                    FROM TMP_REGDADOSSPED_PIS
                   WHERE CDREGISTRO = '0150'
                     AND CDCHAVEREG IS NOT NULL
                     AND LENGTH(CDCHAVEREG) > 3
                  ORDER BY CDESTAB, CDCHAVEREG) LOOP

                 SELECT MAX('|0150'                           || '|' ||
                        CODIGO                                || '|' ||
                        TRIM(REPLACE(REPLACE(NMRAZAO, CHR(10), ' ' ), CHR(9), ' '))  || '|' ||
                        LPAD(CDPAIS,5,'0')                    || '|' ||
                        CNPJ1                                 || '|' ||
                        CPF                                   || '|' ||
                        TRIM(REPLACE(REPLACE(INSCESTA, CHR(10), ' ' ), CHR(9), ' ')) || '|' ||
                        MUNICIPIO                             || '|' ||
                        SUFRAMA                               || '|' ||
                        TRIM(REPLACE(REPLACE(ENDERECO, CHR(10), ' ' ), CHR(9), ' ')) || '|' ||
                        NUMERO                                || '|' ||
                        TRIM(REPLACE(REPLACE(SUBSTR(COMPLEMENT,1,60), CHR(10), ' ' ), CHR(9), ' ')) || '|' ||
                        TRIM(REPLACE(REPLACE(BAIRRO, CHR(10), ' ' ), CHR(9), ' ')) || '|' )
                    INTO V_0150
                    FROM(SELECT 'L'||FI.CDFILIAL                    AS CODIGO,
                                FI.NMRAZSOCFILI                     AS NMRAZAO,
                                PA.CDPAISBACEN                      AS CDPAIS,
                                REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI,'.',''),'/',''),'-','') AS CNPJ1,
                                ''                                  AS CPF,
                                ''                                  AS CEI,
                                ''                                  AS NIT,
                                EF.SGESTADO                         AS ESTADO,
                                DECODE(FI.CDINSCESTA, 'ISENTO', '', REPLACE(REPLACE(REPLACE(FI.CDINSCESTA,'.',''),'/',''),'-','')) AS INSCESTA,
                                ''                                  AS INSCESTST,
                                DECODE(PA.CDPAISBACEN,'1058',LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0'),'') AS MUNICIPIO,
                                ''                                  AS INSCMUNI,
                                ''                                  AS SUFRAMA,
                                FI.CDFILIAL                         AS CNPJ,
                                NVL(EF.DSENDEFILISEP,EF.DSENDEFILI) AS ENDERECO,
                                EF.NRENDEFILISEP                    AS NUMERO,
                                EF.DSCOMPENDFIL                     AS COMPLEMENT,
                                EF.NMBAIRFILI                       AS BAIRRO
                           FROM FILIAL FI, ENDEFILI EF, MUNICIPIO M, PAIS PA
                          WHERE EF.CDFILIAL(+)    = FI.CDFILIAL
                            AND NVL(EF.IDTPENDEFILI,'P') = 'P'
                            AND EF.CDPAIS        = M.CDPAIS
                            AND EF.SGESTADO      = M.SGESTADO
                            AND EF.CDMUNICIPIO   = M.CDMUNICIPIO
                            AND EF.CDPAIS        = PA.CDPAIS
                            AND 'L'||FI.CDFILIAL = C_0150.CDCHAVEREG
                          UNION
                         SELECT 'C'||CL.CDCLIENTE                             AS CODIGO,
                                CL.NMRAZSOCCLIE                               AS NMRAZAO,
                                PA.CDPAISBACEN                                AS CDPAIS,
                                DECODE(LENGTH(REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.',''),'/',''),'-','')), 14, REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.',''),'/',''),'-','')) AS CNPJ1,
                                DECODE(LENGTH(REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.',''),'/',''),'-','')), 11, REPLACE(REPLACE(REPLACE(CL.NRINSJURCLIE,'.',''),'/',''),'-','')) AS CPF,
                                ''                                            AS CEI,
                                ''                                            AS NIT,
                                EC.SGESTADO                                   AS ESTADO,
                                DECODE(CL.NRINSESTCLIE, 'ISENTO', '', REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE,'.',''),'/',''),'-','')) AS INSCESTA,
                                ''                                            AS INSCESTST,
                                DECODE(PA.CDPAISBACEN,'1058',LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0'),'') AS MUNICIPIO,
                                ''                                            AS INSCMUNI,
                                ''                                            AS SUFRAMA,
                                CL.CDCLIENTE                                  AS CNPJ,
                                NVL(EC.DSENDECLIESEP,EC.DSENDECLIE)           AS ENDERECO,
                                EC.NRENDECLIESEP                              AS NUMERO,
                                EC.DSCOMPENDCLI                               AS COMPLEMENT,
                                EC.NMBAIRCLIE                                 AS BAIRRO
                           FROM CLIENTE CL, ENDECLIE EC, MUNICIPIO M, PAIS PA
                          WHERE EC.CDCLIENTE(+)    = CL.CDCLIENTE
                            AND EC.IDTPENDECLIE(+) = NVL(CL.IDENDEMISCLI,'P')
                            AND EC.CDPAIS          = M.CDPAIS
                            AND EC.SGESTADO        = M.SGESTADO
                            AND EC.CDMUNICIPIO     = M.CDMUNICIPIO
                            AND EC.CDPAIS          = PA.CDPAIS
                            AND 'C'||CL.CDCLIENTE  = C_0150.CDCHAVEREG
                         UNION
                         SELECT 'F'||FO.CDFORNECED                               AS CODIGO,
                                FO.NMRAZSOCFORN                                  AS NMRAZAO,
                                PA.CDPAISBACEN                                   AS CDPAIS,
                                DECODE(LENGTH(REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.',''),'/',''),'-','')), 14, REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.',''),'/',''),'-','')) AS CNPJ1,
                                DECODE(LENGTH(REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.',''),'/',''),'-','')), 11, REPLACE(REPLACE(REPLACE(FO.NRINSJURFORN,'.',''),'/',''),'-','')) AS CPF,
                                ''                                               AS CEI,
                                ''                                               AS NIT,
                                EF.SGESTADO                                      AS ESTADO,
                                DECODE(FO.NRINSESTFORN, 'ISENTO', '', REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),'/',''),'-','')) AS INSCESTA,
                                ''                                               AS INSCESTST,
                                DECODE(PA.CDPAISBACEN,'1058',LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0'),'') AS MUNICIPIO ,
                                REPLACE(REPLACE(FO.NRINSMUNFORN,'-',''),'/','')  AS INSCMUNI,
                                ''                                               AS SUFRAMA,
                                FO.CDFORNECED                                    AS CNPJ,
                                NVL(EF.DSENDEFORNSEP,EF.DSENDEFORN)              AS ENDERECO,
                                EF.NRENDEFORNSEP                                 AS NUMERO,
                                EF.DSCOMPENDFOR                                  AS COMPLEMENT,
                                EF.NMBAIRFORN                                    AS BAIRRO
                           FROM FORNECEDOR FO, ENDEFORN EF, MUNICIPIO M, PAIS PA
                          WHERE EF.CDFORNECED(+)   = FO.CDFORNECED
                            AND EF.IDTPENDEFORN(+) = NVL(FO.IDENDEMISFOR,'P')
                            AND EF.CDPAIS          = M.CDPAIS
                            AND EF.SGESTADO        = M.SGESTADO
                            AND EF.CDMUNICIPIO     = M.CDMUNICIPIO
                            AND EF.CDPAIS          = PA.CDPAIS
                            AND 'F'||FO.CDFORNECED = C_0150.CDCHAVEREG);

     V_CONTBLC0 := NVL(V_CONTBLC0,0 ) + 1;
     V_CONTREGA := NVL(V_CONTREGA,0) + 1;
     INSERT INTO ARQMAGPISCOFINS
       (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
     VALUES
       ('104'||C_0150.CDESTAB||'01'||LPAD(RTRIM(V_CONTREGA),22, '0'), NVL(V_0150,'REGISTRO 0150 VAZIO -'||C_0150.CDCHAVEREG),P_NRSEQARQMAG);
   END LOOP;

   FOR C_0190 IN (SELECT '|0190'        || '|' ||
                         U.SGUNIDADE    || '|' ||
                         U.DSUNIDADE    || '|' AS UNID, CDESTAB
                    FROM TMP_REGDADOSSPED_PIS R, UNIDMEDIDA U
                   WHERE R.CDREGISTRO = '0190'
                     AND R.CDCHAVEREG = U.SGUNIDADE) LOOP

      V_CONTBLC0:= NVL(V_CONTBLC0,0)+1;
      V_CONTREGA := NVL(V_CONTREGA,0) + 1;

      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('104'||C_0190.CDESTAB||'02'||LPAD(RTRIM(V_CONTREGA), 22, '0'), NVL(C_0190.UNID,'REGISTRO 0190 VAZIO'), P_NRSEQARQMAG);
   END LOOP;

   /*Atualiza a tabela de alteracao dos produtos, para gravar a referencia de envio no SPED*/  
   UPDATE ALTERAPRODSPED
      SET DTENVEFDCONTR = P_DTINICIAL
    WHERE DTENVEFDCONTR IS NULL
      AND CDPRODUTO     IN (SELECT P.CDPRODUTO
                              FROM TMP_REGDADOSSPED_PIS T, PRODUTO P
                             WHERE T.CDREGISTRO = '0200'
                               AND (T.CDCHAVEREG = P.CDARVPROD AND NVL(T.CDCAMPO6,'N') = 'N') OR
                                    T.CDCAMPO5 = P.CDARVPROD AND NVL(T.CDCAMPO6,'N') = 'S')
      AND DTALTERACAO   <= LAST_DAY(P_DTINICIAL);
   COMMIT;

   /* REGISTRO 0200 */
   FOR C_0200 IN (SELECT CDCHAVEREG, CDESTAB, CDCAMPO4 AS CDCLASFISC, CDCAMPO5 AS CDARVORIG, CDCAMPO6 AS IDUTCDEXTPRONF
                    FROM TMP_REGDADOSSPED_PIS
                   WHERE CDREGISTRO = '0200') LOOP

      SELECT MAX('|0200'     || '|' ||
             C_0200.CDCHAVEREG  || '|' ||
             TRIM(REPLACE(REPLACE(REPLACE(P.NMPRODUTO,'|','/'), CHR(10), ' ' ), CHR(9), ' ')) || '|' ||
             NVL(P.CDBARPRODUTO,'')|| '|' || /* COD_BARRA */
             ''              || '|' || /* COD_ANT_ITEM */
             P.SGUNIDADE     || '|' ||
             R.CDTPITEMPROD  || '|' || /* TIPO ITEM */
             REPLACE(REPLACE(NVL(C_0200.CDCLASFISC,P.CDCLASFISC),'.',''),',','') || '|' ||
             ''              || '|' ||
             R.CDGENPROD     || '|' ||
             ''              || '|' ||
             ''              || '|'  ), MAX(P.SGUNIDADE), MAX(P.CDARVPROD)
        INTO V_0200, V_UNID, V_PROD
        FROM RELACPRODITGEN R, PRODUTO P
       WHERE ((P.CDARVPROD     = C_0200.CDCHAVEREG AND C_0200.IDUTCDEXTPRONF = 'N') OR (P.CDARVPROD = C_0200.CDARVORIG AND C_0200.IDUTCDEXTPRONF = 'S'))
         AND R.CDPRODUTO(+)  = P.CDPRODUTO
         AND R.CDLIVROMAG(+) = P_LIVROMAG;

      IF V_0200 IS NOT NULL THEN
         V_CONTBLC0:= NVL(V_CONTBLC0,0)+1;
         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGPISCOFINS
           (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('104'||C_0200.CDESTAB||'03'||LPAD(RTRIM(V_CONTREGA),22,'0'), NVL(V_0200,'REGISTRO 0200 VAZIO -'||C_0200.CDCHAVEREG),P_NRSEQARQMAG);
      END IF;

      /*REGISTRO 0205: Alteracao do Item*/
      FOR C_REG0205 IN (SELECT MAX('|0205'                                 ||'|'||
                               RETIRA_CARACTER_ESPECIAL(DSCONTEUDOANT,'S') ||'|'||
                               TO_CHAR(DTINICIAL,'DDMMYYYY')               ||'|'||
                               TO_CHAR(DTALTERACAO,'DDMMYYYY')             ||'|'||
                               ''  ||'|') AS ARQ_0205, A.CDPRODUTO
                          FROM ALTERAPRODSPED A, PRODUTO P
                         WHERE A.DTENVEFDCONTR = P_DTINICIAL
                           AND A.CDPRODUTO     = P.CDPRODUTO
                           AND ((P.CDARVPROD   = C_0200.CDCHAVEREG AND C_0200.IDUTCDEXTPRONF = 'N') OR (P.CDARVPROD = C_0200.CDARVORIG AND C_0200.IDUTCDEXTPRONF = 'S'))
                           AND A.DTALTERACAO  <= LAST_DAY(P_DTINICIAL)
                         GROUP BY A.CDPRODUTO) LOOP

         V_CONTBLC0:= NVL(V_CONTBLC0,0)+1;
         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         INSERT INTO ARQMAGPISCOFINS
           (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('104'||C_0200.CDESTAB||'03'||LPAD(RTRIM(V_CONTREGA),22,'0'), C_REG0205.ARQ_0205,P_NRSEQARQMAG);
      END LOOP;
   END LOOP;

   FOR C_0400 IN (SELECT '|0400'       ||'|'||
                         TR.CDCHAVEREG ||'|'||
                         TP.NMTIPOOPER ||'|' AS DSREGARQMAG, TR.CDESTAB
                    FROM TMP_REGDADOSSPED_PIS TR, TIPOOPERAC TP
                   WHERE TR.CDREGISTRO = '0400'
                     AND TR.CDCHAVEREG IS NOT NULL
                     AND TR.CDCAMPO1   = TP.IDENTRSAIDOP
                     AND TR.CDCAMPO2   = TP.CDTIPOOPER
                   ORDER BY TR.CDCHAVEREG) LOOP

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      V_CONTBLC0 := NVL(V_CONTBLC0,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('104'||C_0400.CDESTAB||'04'||LPAD(RTRIM(V_CONTREGA), 22, '0'), C_0400.DSREGARQMAG, P_NRSEQARQMAG);
   END LOOP;

   /*REGISTRO 0450 - Informacao Complementar do documento fiscal*/
   FOR C_0450 IN (SELECT '|0450'     || '|' ||
                         CDCHAVEREG  || '|' ||
                         CDCAMPO1    || '|'  AS ARQ_0450, CDESTAB
                    FROM TMP_REGDADOSSPED_PIS
                   WHERE CDREGISTRO = '0450'
                   ORDER BY CDCHAVEREG) LOOP

      V_CONTREGA := NVL(V_CONTREGA,0) + 1;
      V_CONTBLC0 := NVL(V_CONTBLC0,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
         (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('104'||C_0450.CDESTAB||'04'||LPAD(RTRIM(V_CONTREGA), 22, '0'), C_0450.ARQ_0450, P_NRSEQARQMAG);
   END LOOP;

   FOR C_0500 IN (SELECT CDCHAVEREG, CDESTAB
                    FROM TMP_REGDADOSSPED_PIS
                   WHERE CDREGISTRO = '0500'
                     AND CDCONTREGI IS NOT NULL) LOOP
      FOR C_REG0500 IN (SELECT ('|0500'            || '|' ||
                                TO_CHAR((CASE
                                   WHEN DTULTATUCCTB > P_DTFINAL
                                     THEN P_DTFINAL
                                   ELSE DTULTATUCCTB END),'ddmmyyyy')|| '|' ||
                                IDNATUCONT         || '|' ||
                                IDTIPOCONT         || '|' ||
                                CDNVCONTCTBL       || '|' ||
                                C_0500.CDCHAVEREG  || '|' ||
                                NMCONTCTBL         || '|' ||
                                ''                 || '|' ||
                                ''                 || '|'  ) ARQ_0500
                        FROM CONTCTBL
                        WHERE CDCONTCTBL = C_0500.CDCHAVEREG) LOOP
         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         V_CONTBLC0 := NVL(V_CONTBLC0,0)+1;
         INSERT INTO ARQMAGPISCOFINS
           (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('105'||C_0500.CDESTAB||'04'||LPAD(RTRIM(V_CONTREGA), 22, '0'), NVL(C_REG0500.ARQ_0500,'REGISTRO 0500 VAZIO -'||C_0500.CDCHAVEREG), P_NRSEQARQMAG);
      END LOOP;
   END LOOP;

   /*REGISTRO 0900: COMPOSICAO DAS RECEITAS DO PERIODO  RECEITA BRUTA E DEMAIS RECEITAS - APENAS PARA ENTREGAS FORA DO PRAZO*/
   IF P_IDFORAPER = 'S' THEN
      FOR C_REG0900 IN (SELECT '|0900'  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_TOTAL_BLOCO_A,2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_NRB_BLOCO_A,  2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_TOTAL_BLOCO_C,2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_NRB_BLOCO_C,  2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_TOTAL_BLOCO_D,2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_NRB_BLOCO_D,  2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_TOTAL_BLOCO_F,2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_NRB_BLOCO_F,  2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_TOTAL_BLOCO_I,2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_NRB_BLOCO_I,  2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_TOTAL_BLOCO_1,2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_NRB_BLOCO_1,  2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_TOTAL_PERIODO,2),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(ROUND(REC_NRB_PERIODO,  2),'999999999990.00')),'.',',') ||'|' AS ARQ_0900
                          FROM (SELECT SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'A' THEN T.VRTOTNF ELSE 0 END) AS REC_TOTAL_BLOCO_A,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'A' AND T.CSTPISCOFINS IN ('04','05','06','07','08','09') THEN T.VRTOTNF ELSE 0 END) AS REC_NRB_BLOCO_A,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'C' THEN T.VRTOTNF ELSE 0 END) AS REC_TOTAL_BLOCO_C,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'C' AND T.CSTPISCOFINS IN ('04','05','06','07','08','09') THEN T.VRTOTNF ELSE 0 END) AS REC_NRB_BLOCO_C,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'D' THEN T.VRTOTNF ELSE 0 END) AS REC_TOTAL_BLOCO_D,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'D' AND T.CSTPISCOFINS IN ('04','05','06','07','08','09') THEN T.VRTOTNF ELSE 0 END) AS REC_NRB_BLOCO_D,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'F' THEN T.VRTOTNF ELSE 0 END) AS REC_TOTAL_BLOCO_F,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'F' AND T.CSTPISCOFINS IN ('04','05','06','07','08','09') THEN T.VRTOTNF ELSE 0 END) AS REC_NRB_BLOCO_F,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'I' THEN T.VRTOTNF ELSE 0 END) AS REC_TOTAL_BLOCO_I,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = 'I' AND T.CSTPISCOFINS IN ('04','05','06','07','08','09') THEN T.VRTOTNF ELSE 0 END) AS REC_NRB_BLOCO_I,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = '1' THEN T.VRTOTNF ELSE 0 END) AS REC_TOTAL_BLOCO_1,
                                       SUM(CASE WHEN SUBSTR(T.CHAVENOTA,1,1) = '1' AND T.CSTPISCOFINS IN ('04','05','06','07','08','09') THEN T.VRTOTNF ELSE 0 END) AS REC_NRB_BLOCO_1,
                                       SUM(T.VRTOTNF) AS REC_TOTAL_PERIODO,
                                       SUM(CASE WHEN T.CSTPISCOFINS IN ('04','05','06','07','08','09') THEN T.VRTOTNF ELSE 0 END) AS REC_NRB_PERIODO
                                  FROM TMP_REGBLOCOM_PIS T
                                 WHERE T.CDREGISTRO = 'M200')) LOOP

         V_CONTREGA := NVL(V_CONTREGA,0) + 1;
         V_CONTBLC0 := NVL(V_CONTBLC0,0)+1;
         INSERT INTO ARQMAGPISCOFINS
           (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('109'||LPAD(RTRIM(V_CONTREGA), 27, '0'),C_REG0900.ARQ_0900, P_NRSEQARQMAG);
      END LOOP;
   END IF;

   /*INSERE O BLOCO 0990 - ENCERRAMENTO*/
   V_CONTBLC0 := NVL(V_CONTBLC0,0)+1;
   INSERT INTO ARQMAGPISCOFINS(NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
       VALUES(P_NRSEQARQMAG,'118','|0990|'||TO_CHAR(V_CONTBLC0)||'|');

  /*GERACAO DO BLOCO 9*/

  /*BLOCO 9900 REGISTROS DO BLOCO 0*/
  FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGPISCOFINS
                  WHERE SUBSTR(DSREGARQMAG,2,1) = '0'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9), 27, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /*BLOCO 9900 REGOSTROS DO BLOCO A*/
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGPISCOFINS
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'A'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9),27, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /*BLOCO 9900 REGOSTROS DO BLOCO C*/
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGPISCOFINS
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'C'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;
   /*BLOCO 9900 REGISTROS DO BLOCO D*/
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGPISCOFINS
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'D'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;
   /*BLOCO 9900 REGISTROS DO BLOCO F*/
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGPISCOFINS
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'F'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4) ) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /*BLOCO 9900 REGISTROS DO BLOCO M*/
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGPISCOFINS
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'M'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4)) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   /*BLOCO 9900 REGISTROS DO BLOCO P*/
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGPISCOFINS
                  WHERE SUBSTR(DSREGARQMAG,2,1) = 'P'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4)) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;



   /*BLOCO 9900 REGISTROS DO BLOCO 1*/
   /*V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGPISCOFINS
     (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'1001'|| '|' ||'1'|| '|', P_NRSEQARQMAG);
   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;

   INSERT INTO ARQMAGPISCOFINS
     (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'1990'|| '|' ||'1'|| '|', P_NRSEQARQMAG);
   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;*/
   FOR REG_C IN (SELECT SUBSTR(DSREGARQMAG,2,4) AS BLOCO, COUNT(*) AS QTDE
                   FROM ARQMAGPISCOFINS
                  WHERE SUBSTR(DSREGARQMAG,2,1) = '1'
                    AND NRSEQARQMAG = P_NRSEQARQMAG
                  GROUP BY SUBSTR(DSREGARQMAG,2,4)
                  ORDER BY SUBSTR(DSREGARQMAG,2,4)) LOOP
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||REG_C.BLOCO||'|' ||REG_C.QTDE|| '|', P_NRSEQARQMAG);
   END LOOP;

   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGPISCOFINS
     (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'9001'|| '|' ||'1'|| '|', P_NRSEQARQMAG);
   V_TOTREG := 0;


   SELECT COUNT(*)
     INTO V_TOTREG
     FROM ARQMAGPISCOFINS
    WHERE SUBSTR(DSREGARQMAG,2,4) = '9900'
      AND NRSEQARQMAG = P_NRSEQARQMAG;
   IF V_TOTREG IS NOT NULL THEN
      V_TOTREG := NVL(V_TOTREG,0) + 3;
      V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
      INSERT INTO ARQMAGPISCOFINS
        (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'9900'|| '|' ||V_TOTREG|| '|', P_NRSEQARQMAG);
   END IF;

   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGPISCOFINS
     (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'9990'|| '|' ||'1'|| '|', P_NRSEQARQMAG);

   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGPISCOFINS
     (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('902'||LPAD(RTRIM(V_CONTREG9), 8, '0'), '|9900'|| '|' ||'9999'|| '|' ||'1'|| '|', P_NRSEQARQMAG);

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

   V_CONTREG9 := NVL(V_CONTREG9,0) + 1;
   INSERT INTO ARQMAGPISCOFINS
     (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('901'||LPAD(RTRIM(V_CONTREG9), 8, '0'),'|9001|'||V_MOVIMENT||'|', P_NRSEQARQMAG);

   BEGIN
      SELECT COUNT(*) AS QTDE
         INTO V_TOTREG
        FROM ARQMAGPISCOFINS
       WHERE  NRSEQARQMAG = P_NRSEQARQMAG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_TOTREG := 0;
   END;

   V_TOTREG:= NVL(V_TOTREG,0)+2;/*INSERCAO DE +2 DEVIDO AO REGISTRO 9999 E 9990*/

   INSERT INTO ARQMAGPISCOFINS
     (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('904'||LPAD(RTRIM(V_CONTREG9), 8, '0'),'|9999|'||V_TOTREG||'|', P_NRSEQARQMAG);
     V_CONTREG9 := NVL(V_CONTREG9,0) + 2;/*INSERERE +2 DEVIDO O REGISTRO 9990*/

   INSERT INTO ARQMAGPISCOFINS
     (NRORDREGARQMAG, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('903'||LPAD(RTRIM(V_CONTREG9), 8, '0'),'|9990|'||V_CONTREG9||'|', P_NRSEQARQMAG);


   RETURN(V_CONTREGA);
END;
/