CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCO0(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_NRSEQARQMAG  IN ARQMAGPISCOFINS.NRSEQARQMAG%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_VERSAO       IN VARCHAR2,
   P_TPESCRITU    IN NOTAFISCAL.IDSTATUS%TYPE,
   P_IDSITUESP    IN NOTAFISCAL.IDSTATUS%TYPE,
   P_NUMRECANT    IN VARCHAR2,
   P_TPNATJURPJ   IN NOTAFISCAL.CDMODDOCFISC%TYPE,
   P_ATIVIDADE    IN NOTAFISCAL.IDSTATUS%TYPE,
   P_CDINCTRIB    IN NOTAFISCAL.IDSTATUS%TYPE,
   P_INDAPROCRED  IN NOTAFISCAL.IDSTATUS%TYPE,
   P_CDTPCON      IN NOTAFISCAL.IDSTATUS%TYPE,
   P_CRTESCRITUR  IN NOTAFISCAL.IDSTATUS%TYPE,
   P_LIVROMAG     IN RELACPRODITGEN.CDLIVROMAG%TYPE,
   P_GERABCLP     IN VARCHAR2,
   P_CDPAIS       IN PAIS.CDPAIS%TYPE,
   P_ICMS         IN IMPOSTO.CDIMPOSTO%TYPE,
   P_IPI          IN IMPOSTO.CDIMPOSTO%TYPE,
   P_ISS_E        IN IMPOSTO.CDIMPOSTO%TYPE,
   P_ISS_S        IN IMPOSTO.CDIMPOSTO%TYPE,
   P_ISS_RET_E    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_ISS_RET_S    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_PIS          IN IMPOSTO.CDIMPOSTO%TYPE,
   P_PIS_RET_E    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_PIS_RET_S    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_COFINS       IN IMPOSTO.CDIMPOSTO%TYPE,
   P_COFINS_RET_E IN IMPOSTO.CDIMPOSTO%TYPE,
   P_COFINS_RET_S IN IMPOSTO.CDIMPOSTO%TYPE,
   P_ICMS_ST      IN IMPOSTO.CDIMPOSTO%TYPE,
   P_NATUJURID    IN VARCHAR2) RETURN NUMBER
AS
   V_REG_0000       VARCHAR2(1000);
   V_REG_0110       VARCHAR2(1000);
   V_REG_0140       VARCHAR2(1000);
   V_REG_A001       VARCHAR2(100);
   V_REG_C001       VARCHAR2(100);
   V_REG_D001       VARCHAR2(100);
   V_REG_M001       VARCHAR2(100);
   V_REG_F001       VARCHAR2(100);
   V_REG_P001       VARCHAR2(100);
   V_CNPJ_0140      VARCHAR2(20);
   V_INSJUREST      VARCHAR2(20);
   V_OLDESTAB       VARCHAR2(20);
   V_CONTEST        NUMBER;
   V_NEWESTAB       NUMBER;
   V_TOTLINHAS      NUMBER;
   V_TOTBLC0        NUMBER;
   V_TOTBLCA        NUMBER;
   V_TOTBLCC        NUMBER;
   V_TOTBLCD        NUMBER;
   V_TOTBLCM        NUMBER;
   V_TOTBLCP        NUMBER;
   V_SEQNOTAS       NUMBER;
   V_SEQESTAB       NUMBER;
   V_CONTREGA       NUMBER;
   V_IDNOVOESTA     VARCHAR2(1);
   V_IDNOVOESTC     VARCHAR2(1);
   V_IDNOVOESTD     VARCHAR2(1);
   VDIMPISS         VARCHAR2(2);
   VDIMPICMS        VARCHAR2(2);
   V_SGESTEMPR      VARCHAR2(2);
   V_CDPAISEMPR     VARCHAR2(4);
   V_CDMUNIEMPR     VARCHAR2(4);
   V_PRODUTO        VARCHAR(100);
   V_UNID           VARCHAR(4);
   TIPOOPER_COMPL_S VARCHAR2(2);
   TIPOOPER_COMPL_E VARCHAR2(2);
   V_RESULTADO      VARCHAR2(100);
   IDGEROUBCLACD    VARCHAR2(1);
   V_REG            VARCHAR2(1000);
   V_SEQA           NUMBER;
   V_SEQC           NUMBER;
   V_SEQD           NUMBER;
   V_TMPBLCM        NUMBER;
   V_TOTALBCLF      NUMBER;
   V_SEQESTABBLCF   NUMBER;
   V_IDOUTROS       VARCHAR2(1);
   V_VRDESTOTIT     FLOAT;
   V_VROUTDESPIT    FLOAT;
   V_FRETE          FLOAT;
   V_DESCONTO       FLOAT;
   V_OUTROS         FLOAT;
   V_SEGUR          FLOAT;
   V_COUNTBL0ECF    NUMBER;
   V_COUNTBL0P      NUMBER;
   V_CNPJ_REAL      VARCHAR2(20);
   V_CONTREG1       NUMBER;
   V_SEMMOVIMENTO   VARCHAR2(1);
   V_SEQVENDAS      NUMBER;
   V_GERADEVOLVEND  VARCHAR2(1);
   V_NRSEQUMAPPIS   VARCHAR2(10);
   V_COD_CC         VARCHAR2(20);
   V_SEQCCTBL       NUMBER;
   V_CONTBLC0M      NUMBER;
BEGIN
   V_TMPBLCM := 0;
   V_SEQESTAB:= 0;

   BEGIN
      SELECT CDPAIS,SGESTADO, CDMUNICIPIO
        INTO V_CDPAISEMPR, V_SGESTEMPR, V_CDMUNIEMPR
        FROM EMPRESA
       WHERE CDEMPRESA = P_CDEMPRESA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CDPAISEMPR:= '55';
         V_SGESTEMPR:= 'MG';
         V_CDMUNIEMPR:= '0001';
   END;

   BEGIN
      SELECT MAX(IDDEVOLNCUMUSPED)
        INTO V_GERADEVOLVEND
        FROM PARAMFISCAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_GERADEVOLVEND := 'A';
   END;
   /*SELECTIONA O TIPO DE OPERAÇÃO DE NOTAS FISCAIS COMPLEMENTARES QUE SEJAM DIFERENTES DE 03. TIPO DE OPERAÇÃO 03 JÁ É FIXO NO SISTEMA COM COMPLEMENTAR*/
   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');

  /*REGISTRO 0000*/
   BEGIN
      SELECT '|0000'                         ||'|'||
             P_VERSAO                        ||'|'||
             P_TPESCRITU                     ||'|'||
             P_IDSITUESP                     ||'|'||
             P_NUMRECANT                     ||'|'||
             TO_CHAR(P_DTINICIAL,'ddmmyyyy') ||'|'||
             TO_CHAR(P_DTFINAL,'ddmmyyyy')   ||'|'||
             NMRAZSOCEMPR                    ||'|'||
             NRINSJUREMPR                    ||'|'||
             E.SGESTADO                      ||'|'||
             M.CDMUNICIBGE                   ||'|'||
             ''                              ||'|'||
             P_NATUJURID                     ||'|'||
             P_ATIVIDADE                     ||'|'
        INTO V_REG_0000
        FROM EMPRESA E, MUNICIPIO M
       WHERE E.CDPAIS      = M.CDPAIS
         AND E.SGESTADO    = M.SGESTADO
         AND E.CDMUNICIPIO = M.CDMUNICIPIO
         AND E.CDEMPRESA   = P_CDEMPRESA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_REG_0000 := '';
   END;

   V_TOTBLC0   := NVL(V_TOTBLC0,  0)+1;
   V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,LPAD('0',10,'0'),V_REG_0000);

   /*REGISTRO 0001*/
   V_TOTBLC0   := NVL(V_TOTBLC0,  0)+1;
   V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'101'||LPAD('0',7,'0'),'|0001|0|');

   /* GERAÇÃO DO REGISTRO 0035 SOMENTE PARA AS NATUREZAS JURÍDIAS 03 E 04 */
   IF P_NATUJURID IN ('03', '04') THEN
      FOR C_0035 IN (SELECT '|0035' ||'|'||
                            REPLACE(REPLACE(REPLACE(CODSCP,'-',''),'/',''),'.','') ||'|'||
                            DESCSCP ||'|'||
                            INFCOMP ||'|' AS REGISTRO
                       FROM SCPEMPRESA
                      WHERE CDEMPRESA = P_CDEMPRESA) LOOP

         V_TOTBLC0   := NVL(V_TOTBLC0,  0)+1;
         V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
         INSERT INTO ARQMAGPISCOFINS
           (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
           (P_NRSEQARQMAG,'101'||LPAD('0',8,'0'),C_0035.REGISTRO);
      END LOOP;
   END IF;

   /*Registro 0100*/
   FOR REG_0100 IN(SELECT ('|0100'                                  || '|' ||
                          NMCONTADOR                                || '|' ||
                          NRCPFRESPONS                              || '|' ||
                          REPLACE(REPLACE(NRCRCCONT2,'/',''),'-','')|| '|' ||
                          ''                                        || '|' ||
                          REPLACE(REPLACE(NRCEP,'/',''),'-','')     || '|' ||
                          LTRIM(RTRIM(DSLOGRADOURO))                || '|' ||
                          ''                                        || '|' ||
                          LTRIM(RTRIM(DSCOMPLLOGRA))                || '|' ||
                          LTRIM(RTRIM(DSBAIRRO))                    || '|' ||
                          SUBSTR(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(NRTELCONTATO, '(',''),')','') ,'-',''))),1,10)  || '|' ||
                          SUBSTR(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(NRFAXCONTATO, '(',''),')','') ,'-',''))),1,10)  || '|' ||
                          DSENDINTERN                               || '|' ||
                          M.CDMUNICIBGE                             || '|'  ) ARQ_0100
                     FROM CONTADOR C, MUNICIPIO M, EMPRESA E
                    WHERE E.CDCONTADOR2 = C.CDCONTADOR2
                      AND C.CDPAIS      = M.CDPAIS
                      AND C.CDMUNICIPIO = M.CDMUNICIPIO
                      AND C.SGESTADO    = M.SGESTADO
                      AND E.CDEMPRESA   = P_CDEMPRESA)LOOP

      V_TOTBLC0   := NVL(V_TOTBLC0,  0)+1;
      V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'102'||LPAD(RTRIM(V_TOTLINHAS),27,'0'),REG_0100.ARQ_0100);
   END LOOP;

   /*REGISTRO 0110*/
   V_REG_0110 := '|0110'        ||'|'||
                 P_CDINCTRIB    ||'|'||
                 P_INDAPROCRED  ||'|'||
                 P_CDTPCON      ||'|';

   /*Campo critério da escrituração a ser importado pelo PVA*/
   IF P_VERSAO NOT IN ('001','002') THEN
      IF P_CDINCTRIB = '2' THEN
         V_REG_0110:= V_REG_0110||P_CRTESCRITUR||'|';
      ELSE
         V_REG_0110:= V_REG_0110||'|';
      END IF;
   END IF;

   V_TOTBLC0   := NVL(V_TOTBLC0,  0)+1;
   V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'103'||LPAD(RTRIM(V_TOTLINHAS),22,'0'),V_REG_0110);

    /*SELECT QUE BUSCA AS NOTAS QUE IRÃO COMPOR O ARQUIVO - PRINCIPAL*/
   FOR C_NOTAS IN (SELECT NRLANCTONF, CDFILIAL, ESTAB
                     FROM (SELECT DISTINCT NF.NRLANCTONF, NF.CDFILIAL,
                                  BUSCA_CNPJ_ESTAB(P_ICMS,P_ISS_E,P_ISS_S,P_ISS_RET_E,P_ISS_RET_S,NF.NRLANCTONF,NF.CDFILIAL,NF.CDINSCESTA,NF.CDINSCMUNI,NF.CDFILIEMIT,NF.CDFILIRECE,NF.IDENTRSAIDOP) AS ESTAB
                             FROM NOTAFISCAL NF, FILIAL F, CFOPPADRAO C, (SELECT IP.CDFILIAL, IP.NRLANCTONF, IP.CDFISCOPERNF
                                                                            FROM MAPAAPURAC MP, IMPOSTONF IP, IMPOSTO IM
                                                                           WHERE MP.CDEMPRESA    = P_CDEMPRESA
                                                                             AND MP.CDIMPOSTO    IN (P_PIS,P_COFINS)
                                                                             AND MP.DTINIPERIODO = P_DTINICIAL
                                                                             AND MP.DTFIMPERIODO = P_DTFINAL
                                                                             AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                                                                             AND MP.CDIMPOSTO    = IM.CDIMPOSTONFE
                                                                             AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                                                                           UNION ALL
                                                                          SELECT IB.CDFILIAL, IB.NRLANCTONF, IB.CDFISCOPERNF
                                                                            FROM MAPAAPURAC MP, IMPBAIXAREC IB, IMPOSTO IM
                                                                           WHERE MP.CDEMPRESA    = P_CDEMPRESA
                                                                             AND MP.CDIMPOSTO    IN (P_PIS,P_COFINS)
                                                                             AND MP.DTINIPERIODO = P_DTINICIAL
                                                                             AND MP.DTFIMPERIODO = P_DTFINAL
                                                                             AND MP.NRSEQUMAPAPU = IB.NRSEQUMAPAPU
                                                                             AND MP.CDIMPOSTO    = IM.CDIMPOSTONFE
                                                                             AND IM.CDIMPOSTO    = IB.CDIMPOSTO) IP
                            WHERE IP.CDFILIAL     = NF.CDFILIAL
                              AND IP.NRLANCTONF   = NF.NRLANCTONF
                              AND (NF.IDSTATUS = 'A' OR IDSTATUS = 'E')
                              AND DECODE(NF.IDENTRSAIDOP,'E',NF.CDFILIRECE, NF.CDFILIEMIT) = F.CDFILIAL
                              AND ((NF.IDENTRSAIDOP = 'E' AND F.IDCUMUPISCOFIL = 'S') OR (NF.IDENTRSAIDOP = 'S' AND F.IDCUMUPISCOFIL IN ('S','N')) )
                              AND IP.CDFISCOPERNF   = C.CDCFOPPADRAO(+)
                              AND ((NVL(C.IDTIPOCFOP,'X') <> 'D') OR (((F.IDCUMUPISCOFIL = 'S') AND (NF.IDENTRSAIDOP = 'E') AND (IP.CDFISCOPERNF = C.CDCFOPPADRAO) AND (C.IDTIPOCFOP = 'D') AND (NVL(V_GERADEVOLVEND,'A') = 'C'))))
                              AND NVL(NF.IDSISTEMA, 'DFS')  <> 'REC'
                              AND NVL(NF.IDSISTEMA, 'DFS')  <> 'PAG'
                              AND NVL(NF.IDSISTEMA, 'DFS')  <> 'CMP'
                              AND NVL(NF.IDSISTEMA, 'DFS')  <> 'SUP'
                              AND NVL(NF.IDSISTEMA, 'DFS')  <> 'FIS'
                              --AND NVL(NF.IDSISTEMA, 'DFS') <> 'TMP' /*As notas do TMP devem entrar no arquivo para quem utiliza regime de caixa e o ForSale*/
                              ))LOOP

         /*GRAVA TODAS A NOTAS QUE ESTÃO NO ARQUIVO*/
         V_SEQNOTAS := NVL(V_SEQNOTAS,0) + 1;
         INSERT INTO TMP_REGDADOSSPED_PIS
            (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2, CDESTAB)
         VALUES
            ('NOTA', V_SEQNOTAS, C_NOTAS.CDFILIAL||C_NOTAS.NRLANCTONF, C_NOTAS.CDFILIAL, C_NOTAS.NRLANCTONF, C_NOTAS.ESTAB);
         END LOOP;

   /*SELECT QUE BUSCA AS NOTAS FISCAIS ELETRÔNICAS AO CONSUMIDOR FINAL (NFC-e) QUE IRÃO COMPOR O ARQUIVO - PRINCIPAL*/
   FOR C_VENDAS IN (SELECT NRSEQVENDA, CDFILIAL, CDCAIXA,
                           LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJURFILI,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0')||'ICM' AS ESTAB,
                           IDTPEMISVEND
                      FROM (SELECT DISTINCT VE.NRSEQVENDA, VE.CDFILIAL, VE.CDCAIXA, FI.NRINSJURFILI, VE.IDTPEMISVEND
                              FROM VENDA VE, FILIAL FI, MAPAAPURAC MP
                             WHERE MP.CDEMPRESA       = P_CDEMPRESA
                               AND MP.CDIMPOSTO       IN (P_PIS,P_COFINS)
                               AND MP.DTINIPERIODO    = P_DTINICIAL
                               AND MP.DTFIMPERIODO    = P_DTFINAL
                               AND FI.CDEMPRESA       = MP.CDEMPRESA
                               AND FI.CDFILIAL        = VE.CDFILIAL
                               AND TRUNC(VE.DTEMISSAONFCE) BETWEEN MP.DTINIPERIODO AND MP.DTFIMPERIODO
                               AND VE.IDSITUVENDA     = 'O'
                               AND VE.IDTPAMBNFCE     = '1'
                               AND VE.IDSTATUSNFCE    IN ('A','C')
                               AND VE.IDAPROVACAONFCE = 'A'
                               AND VE.IDTPEMISVEND    = 'N'
                             UNION
                            SELECT DISTINCT VE.NRSEQVENDA, VE.CDFILIAL, VE.CDCAIXA, FI.NRINSJURFILI, VE.IDTPEMISVEND
                              FROM VENDA VE, FILIAL FI, MAPAAPURAC MP
                             WHERE MP.CDEMPRESA       = P_CDEMPRESA
                               AND MP.CDIMPOSTO       IN (P_PIS,P_COFINS)
                               AND MP.DTINIPERIODO    = P_DTINICIAL
                               AND MP.DTFIMPERIODO    = P_DTFINAL
                               AND FI.CDEMPRESA       = MP.CDEMPRESA
                               AND FI.CDFILIAL        = VE.CDFILIAL
                               AND TRUNC(VE.DTEMISSAONFCE) BETWEEN MP.DTINIPERIODO AND MP.DTFIMPERIODO
                               AND VE.IDSITUVENDA     = 'O'
                               AND VE.IDTPAMBNFCE     = '1'
                               AND VE.IDSTATUSNFCE    IN ('A','C')
                               AND VE.IDAPROVACAONFCE = 'A'
                               AND VE.IDTPEMISVEND    = 'S'))LOOP


         /*GRAVA TODAS A NFC-E's QUE ESTÃO NO ARQUIVO*/
         V_SEQVENDAS := NVL(V_SEQVENDAS,0) + 1;
         INSERT INTO TMP_REGDADOSSPED_PIS
            (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2, CDCAMPO3, CDESTAB)
         VALUES
            (DECODE(C_VENDAS.IDTPEMISVEND, 'N', 'NFCE', 'SAT'), V_SEQVENDAS, C_VENDAS.CDFILIAL||C_VENDAS.NRSEQVENDA||C_VENDAS.CDCAIXA, C_VENDAS.CDFILIAL, C_VENDAS.NRSEQVENDA, C_VENDAS.CDCAIXA, C_VENDAS.ESTAB);
         END LOOP;

   V_SEMMOVIMENTO := 'S';
   V_CONTEST      := NULL;
   IF P_GERABCLP IN ('0','1') THEN
      IF ((P_CRTESCRITUR IS NULL) OR (P_CRTESCRITUR = '9')) THEN /*ESCRITURAÇÃO DETALHADA*/
         /*BUSCA AS NOTAS PARA GERAR O ARQUIVO DE FORMA DETALHADA - BLOCOS A,C,D,F*/
         FOR C_NOTAS_DETAL IN (SELECT CDREGISTRO, NRLANCTONF, CDFILIAL, '' AS CDCAIXA, IDENTRSAIDOP,IDSTATUS,CDINSCESTA, CDINSCMUNI,
                                      CDFORNECED, CDCLIENTE, CDFILIEMIT, CDFILIRECE, CDSERIE, CDSERIEENTR, NRNOTAFISC,
                                      DTEMISSAO, DTENTRSAID, NRACESSONFE, VRNOTAFISC, VRPRODUTOS,
                                      VALOR_IMPOSTO_NFE(NF.CDFILIAL, NF.NRLANCTONF, '50','C') AS VRCONTABIL,
                                      IDPGTOFRETE, CDMODDOCFISC, SUBSTR(CDESTAB,1,LENGTH(CDESTAB)-3) AS CNPJ,
                                      SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3) AS IDIMPOSTO,
                                      CDTIPOOPER, IDSTATUSNFE, NRNOTAFISCALELE,
                                      NVL(IDORIGDESTNF, CASE WHEN CDFORNECED IS NOT NULL THEN 'F'
                                                             WHEN CDCLIENTE  IS NOT NULL THEN 'C'
                                                             WHEN CDFORNECED IS NULL AND CDCLIENTE IS NULL THEN 'L' END) AS IDORIGDESTNF,
                                      NF.CDTIPOCTE
                                 FROM TMP_REGDADOSSPED_PIS TMP, NOTAFISCAL NF
                                WHERE TMP.CDREGISTRO = 'NOTA'
                                  AND TMP.CDCAMPO1   = NF.CDFILIAL
                                  AND TMP.CDCAMPO2   = NF.NRLANCTONF
                                UNION ALL
                               SELECT TMP.CDREGISTRO, VE.NRSEQVENDA AS NRLANCTONF, VE.CDFILIAL, VE.CDCAIXA, 'S' AS IDENTRSAIDOP, VE.IDSTATUSNFCE AS IDSTATUS,
                                      FI.CDINSCESTA AS CDINSCESTA, FI.CDINSCMUNI AS CDINSCMUNI, '' AS CDFORNECED, '' AS CDCLIENTE, '' AS CDFILIEMIT, '' AS CDFILIRECE,
                                      VE.CDSERIENFCE AS CDSERIE, '' AS CDSERIEENTR, VE.NRNOTAFISCALCE AS NRNOTAFISC,
                                      TRUNC(VE.DTEMISSAONFCE) AS  DTEMISSAO, TRUNC(VE.DTEMISSAONFCE) AS DTENTRSAID, VE.NRACESSONFCE AS NRACESSONFE,
                                      SUM(TRUNC((IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS VRNOTAFISC,
                                      SUM(TRUNC((IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS VRPRODUTOS,
                                      SUM(TRUNC((IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS VRCONTABIL,
                                      '9' AS IDPGTOFRETE, '65' AS CDMODDOCFISC, SUBSTR(CDESTAB,1,LENGTH(CDESTAB)-3) AS CNPJ, SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3) AS IDIMPOSTO,
                                      PV.CDTPOPERCNTREC AS CDTIPOOPER, VE.IDSTATUSNFCE AS IDSTATUSNFE,
                                      VE.NRNOTAFISCALCE AS NRNOTAFISCALELE, 'C' AS IDORIGDESTNF, '' AS CDTIPOCTE
                                 FROM TMP_REGDADOSSPED_PIS TMP, VENDA VE, ITEMVENDA IT, FILIAL FI, PARAVEND PV
                                WHERE TMP.CDREGISTRO = 'NFCE'
                                  AND TMP.CDCAMPO1   = VE.CDFILIAL
                                  AND TMP.CDCAMPO2   = VE.NRSEQVENDA
                                  AND TMP.CDCAMPO3   = VE.CDCAIXA
                                  AND TMP.CDCAMPO1   = IT.CDFILIAL
                                  AND TMP.CDCAMPO2   = IT.NRSEQVENDA
                                  AND TMP.CDCAMPO3   = IT.CDCAIXA
                                  AND TMP.CDCAMPO1   = FI.CDFILIAL
                                  AND TMP.CDCAMPO1   = PV.CDFILIAL
                                  AND VE.CDFILIAL    = IT.CDFILIAL
                                  AND VE.NRSEQVENDA  = IT.NRSEQVENDA
                                  AND VE.CDCAIXA     = IT.CDCAIXA
                                  AND VE.CDFILIAL    = FI.CDFILIAL
                                  AND VE.CDFILIAL    = PV.CDFILIAL
                                  AND PV.CDFILIAL    = FI.CDFILIAL
                                  AND IT.CDFILIAL    = FI.CDFILIAL
                                  AND PV.CDFILIAL    = IT.CDFILIAL
                                GROUP BY TMP.CDREGISTRO, VE.NRSEQVENDA, VE.CDFILIAL, VE.CDCAIXA, VE.IDSTATUSNFCE, FI.CDINSCESTA, FI.CDINSCMUNI,
                                         VE.CDSERIENFCE, VE.NRNOTAFISCALCE, TRUNC(VE.DTEMISSAONFCE), VE.DTENTRVENDA, VE.NRACESSONFCE,
                                         TMP.CDESTAB, PV.CDTPOPERCNTREC) LOOP

            IF C_NOTAS_DETAL.CDREGISTRO = 'NOTA' THEN
               /*BUSCA O VALOR DOS ENCARGOS*/
               /*PEGA O VALOR DO FRETE*/
               BEGIN
                  SELECT SUM(VRITEMENCA)
                    INTO V_FRETE
                    FROM ITEMENCARGO I, ENCARGO EN
                   WHERE I.CDENCARGO     = EN.CDENCARGO
                     AND EN.CDENCARGONFE = '01'
                     AND NRLANCTONF      = C_NOTAS_DETAL.NRLANCTONF
                     AND CDFILIAL        = C_NOTAS_DETAL.CDFILIAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_FRETE:=0;
               END;

               /*PEGA O VALOR DO SEGURO*/
               BEGIN
                  SELECT SUM(VRITEMENCA)
                    INTO V_SEGUR
                    FROM ITEMENCARGO I, ENCARGO EN
                   WHERE I.CDENCARGO     = EN.CDENCARGO
                     AND EN.CDENCARGONFE = '02'
                     AND NRLANCTONF      = C_NOTAS_DETAL.NRLANCTONF
                     AND CDFILIAL        = C_NOTAS_DETAL.CDFILIAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_SEGUR:=0;
               END;

               /*PEGA O VALOR DO DESCONTO*/
               BEGIN
                  SELECT SUM(VRDESCITEM)
                    INTO V_DESCONTO
                    FROM ITEMPRODUTO
                   WHERE NRLANCTONF = C_NOTAS_DETAL.NRLANCTONF
                     AND CDFILIAL   = C_NOTAS_DETAL.CDFILIAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_DESCONTO:=0;
               END;

               /*PEGA O VALOR DE OUTROS*/
               BEGIN
                  SELECT SUM(VRITEMENCA)
                    INTO V_OUTROS
                    FROM ITEMENCARGO I, ENCARGO EN
                   WHERE I.CDENCARGO     = EN.CDENCARGO
                     AND EN.CDENCARGONFE = '04'
                     AND NRLANCTONF      = C_NOTAS_DETAL.NRLANCTONF
                     AND CDFILIAL        = C_NOTAS_DETAL.CDFILIAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_OUTROS:=0;
               END;
            ELSE /*NFC-e*/
               /*BUSCA O VALOR DOS ENCARGOS*/
               V_FRETE :=0;
               V_SEGUR :=0;
               V_OUTROS:=0;
               V_INSJUREST := NULL;
               /*PEGA O VALOR DO DESCONTO*/
               BEGIN
                  SELECT SUM(VRDESITVEND)
                    INTO V_DESCONTO
                    FROM ITEMVENDA
                   WHERE NRSEQVENDA = C_NOTAS_DETAL.NRLANCTONF
                     AND CDFILIAL   = C_NOTAS_DETAL.CDFILIAL
                     AND CDCAIXA    = C_NOTAS_DETAL.CDCAIXA;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_DESCONTO:=0;
               END;
            END IF;

            /*ALTERADO----VERIFICA CNPJ A SER INSERIDO*/
            SELECT MAX(LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0'))
              INTO V_CNPJ_REAL
              FROM INSCRESTAD I
             WHERE I.CDEMPRESA  = P_CDEMPRESA
               AND I.CDINSCESTA = C_NOTAS_DETAL.CDINSCESTA;

            /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
            V_INSJUREST:= NULL;
            SELECT MAX(CDCHAVEREG),MAX(CDESTAB)
              INTO V_INSJUREST, V_OLDESTAB
              FROM TMP_REGDADOSSPED_PIS TMP
             WHERE CDREGISTRO = '0140'
               AND CDCHAVEREG = V_CNPJ_REAL;

            /*LEMBRAR QUE O REGISTRO 0140 TAMBEM  INSERIDO NA FUNCAO SPED_PIS_COFINS_BLOCOC_ECF*/
            /*GERA O REGISTRO 0140 - ESTABELECIMENTO*/
            IF V_INSJUREST IS NULL THEN
               /*ACRESCE UM NO NUMERO DO ESTABELECIMENTO*/
               V_CONTEST:= NVL(V_CONTEST,0)+1;
               V_NEWESTAB:= V_CONTEST;
               BEGIN
                  SELECT REG, INSJUREST
                    INTO V_REG, V_INSJUREST
                    FROM (SELECT '|0140'         ||'|'||
                                 LTRIM(TO_CHAR(V_NEWESTAB,'000'))      ||'|'||
                                 E.NMRAZSOCEMPR  ||'|'||
                                 LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0') ||'|'||
                                 I.SGESTADO     ||'|'||
                                 ''/*REPLACE(REPLACE(REPLACE(REPLACE(IE.CDINSCESTA,'.',''),'/',''),'-',''),'\','')*/ ||'|'||
                                 M.CDMUNICIBGE   ||'|'||
                                 ''             ||'|'||
                                 ''             ||'|' AS REG,
                                 LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0') AS INSJUREST
                            FROM MUNICIPIO M, INSCRESTAD I, EMPRESA E
                           WHERE I.CDEMPRESA    = P_CDEMPRESA
                             AND I.CDINSCESTA   = C_NOTAS_DETAL.CDINSCESTA
                             AND I.CDPAIS       = M.CDPAIS
                             AND I.SGESTADO     = M.SGESTADO
                             AND I.CDMUNICIPIO  = M.CDMUNICIPIO
                             AND E.CDEMPRESA    = I.CDEMPRESA);
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_REG:= C_NOTAS_DETAL.CDINSCESTA||'-'||C_NOTAS_DETAL.NRLANCTONF||'-'||C_NOTAS_DETAL.CDFILIAL;
                     V_INSJUREST:='';
               END;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'104'||LTRIM(TO_CHAR(V_CONTEST,'000')),V_REG);

               V_SEQESTAB:= NVL(V_SEQESTAB,0)+1;
               V_CONTREGA:= NVL(V_CONTREGA,0)+1;
               V_TOTBLC0:= NVL(V_TOTBLC0,0)+1;

               INSERT INTO TMP_REGDADOSSPED_PIS
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                VALUES
                 ('0140', V_SEQESTAB, V_INSJUREST ,NULL,LTRIM(TO_CHAR(V_CONTEST,'000')));
            ELSE
               V_NEWESTAB:= TO_NUMBER(V_OLDESTAB);
            END IF;

            /*VARIAVEL IDENTIFICA SE GEROU O BLOCO A,C,E -  SE NAO GERAR GERA PELO BLOCO F */
            IDGEROUBCLACD:= 'N';

            IF C_NOTAS_DETAL.IDIMPOSTO = 'ICM' THEN
               IF C_NOTAS_DETAL.CDMODDOCFISC IN ('01','1B','04','55','02','06','29','28','65') THEN
                  /*BLOCO C*/
                  V_RESULTADO:= NVL(SPED_PIS_COFINS_BLOCOC(C_NOTAS_DETAL.NRLANCTONF, C_NOTAS_DETAL.CDFILIAL, C_NOTAS_DETAL.CDCAIXA,
                                                           P_NRSEQARQMAG,V_INSJUREST,V_IDNOVOESTC,
                                                           C_NOTAS_DETAL.IDENTRSAIDOP,C_NOTAS_DETAL.IDSTATUS,
                                                           C_NOTAS_DETAL.IDORIGDESTNF,C_NOTAS_DETAL.CDFILIEMIT,
                                                           C_NOTAS_DETAL.CDFILIRECE,C_NOTAS_DETAL.CDCLIENTE,
                                                           C_NOTAS_DETAL.CDFORNECED,NVL(C_NOTAS_DETAL.CDSERIE, C_NOTAS_DETAL.CDSERIEENTR),
                                                           C_NOTAS_DETAL.NRNOTAFISC,C_NOTAS_DETAL.DTEMISSAO,C_NOTAS_DETAL.DTENTRSAID,
                                                           C_NOTAS_DETAL.VRNOTAFISC,C_NOTAS_DETAL.VRCONTABIL, P_PIS,P_PIS_RET_E,P_PIS_RET_S,
                                                           P_COFINS,P_COFINS_RET_E,P_COFINS_RET_S,P_ISS_E,P_ISS_S,P_ISS_RET_E,P_ISS_RET_S,
                                                           V_NEWESTAB,P_CDPAIS, P_CDEMPRESA,NVL(V_SEQC,0),C_NOTAS_DETAL.IDPGTOFRETE,
                                                           C_NOTAS_DETAL.NRACESSONFE, C_NOTAS_DETAL.CDMODDOCFISC,C_NOTAS_DETAL.VRPRODUTOS,
                                                           P_ICMS_ST,P_IPI,P_ICMS,P_DTINICIAL,P_DTFINAL,V_DESCONTO,C_NOTAS_DETAL.CDTIPOOPER,
                                                           C_NOTAS_DETAL.IDSTATUSNFE, V_FRETE,V_OUTROS,V_SEGUR,TIPOOPER_COMPL_S,
                                                           TIPOOPER_COMPL_E,V_SEQESTAB,V_TMPBLCM,C_NOTAS_DETAL.NRNOTAFISCALELE,
                                                           P_LIVROMAG,V_GERADEVOLVEND),0);
                  V_TOTBLCC  := TO_NUMBER(SUBSTR(V_RESULTADO,1,20));
                  V_SEQESTAB := NVL(V_SEQESTAB, 0) + TO_NUMBER(SUBSTR(V_RESULTADO,22,20));
                  V_TMPBLCM  := NVL(V_TMPBLCM, 0)  + TO_NUMBER(SUBSTR(V_RESULTADO,43,20));
                  V_SEQC     := NVL(V_TOTBLCC, 0);
                  IDGEROUBCLACD:= 'S';
                  /*V_IDNOVOESTC:= 'N';*/
               END IF;
            END IF;

            /*GERA BLOCO A*/
            IF C_NOTAS_DETAL.IDIMPOSTO = 'ISS' THEN
               V_RESULTADO:= NVL(SPED_PIS_COFINS_BLOCOA(C_NOTAS_DETAL.NRLANCTONF, C_NOTAS_DETAL.CDFILIAL,
                                                        P_NRSEQARQMAG,V_INSJUREST,V_IDNOVOESTA,
                                                        C_NOTAS_DETAL.IDENTRSAIDOP,C_NOTAS_DETAL.CDTIPOOPER,
                                                        C_NOTAS_DETAL.IDSTATUS, C_NOTAS_DETAL.IDORIGDESTNF,
                                                        C_NOTAS_DETAL.CDFILIEMIT,C_NOTAS_DETAL.CDFILIRECE,
                                                        C_NOTAS_DETAL.CDCLIENTE,C_NOTAS_DETAL.CDFORNECED,
                                                        NVL(C_NOTAS_DETAL.CDSERIE, C_NOTAS_DETAL.CDSERIEENTR),
                                                        C_NOTAS_DETAL.NRNOTAFISC,C_NOTAS_DETAL.DTEMISSAO,C_NOTAS_DETAL.DTENTRSAID,
                                                        C_NOTAS_DETAL.VRNOTAFISC,P_PIS,P_PIS_RET_E,P_PIS_RET_S,P_COFINS,P_COFINS_RET_E,P_COFINS_RET_S,P_ISS_E,P_ISS_S,P_ISS_RET_E,P_ISS_RET_S,V_NEWESTAB,P_CDPAIS,
                                                        P_CDEMPRESA,NVL(V_SEQA,0),V_VRDESTOTIT,V_SEQESTAB,V_TMPBLCM,P_LIVROMAG),0);
               V_TOTBLCA  := TO_NUMBER(SUBSTR(V_RESULTADO,1,20));
               V_SEQA     := NVL(V_TOTBLCA,0);
               V_SEQESTAB := NVL(V_SEQESTAB, 0) + TO_NUMBER(SUBSTR(V_RESULTADO,22,20));
               V_TMPBLCM  := NVL(V_TMPBLCM, 0)  + TO_NUMBER(SUBSTR(V_RESULTADO,43,20));
               IDGEROUBCLACD:= 'S';
            END IF;

            IF C_NOTAS_DETAL.CDMODDOCFISC IN ('07','08','09','10','11','26','27','57','67','21','22')  AND C_NOTAS_DETAL.IDENTRSAIDOP = 'E' AND C_NOTAS_DETAL.IDIMPOSTO = 'ICM' THEN
               /*GERA BLOCO D*/
               V_RESULTADO:= NVL(SPED_PIS_COFINS_BLOCOD(C_NOTAS_DETAL.NRLANCTONF, C_NOTAS_DETAL.CDFILIAL,
                                                        P_NRSEQARQMAG,V_INSJUREST,V_IDNOVOESTD,
                                                        C_NOTAS_DETAL.IDENTRSAIDOP,C_NOTAS_DETAL.CDTIPOOPER,
                                                        C_NOTAS_DETAL.IDSTATUS, C_NOTAS_DETAL.IDORIGDESTNF,
                                                        C_NOTAS_DETAL.CDFILIEMIT, C_NOTAS_DETAL.CDFILIRECE,
                                                        C_NOTAS_DETAL.CDCLIENTE, C_NOTAS_DETAL.CDFORNECED,
                                                        NVL(C_NOTAS_DETAL.CDSERIE, C_NOTAS_DETAL.CDSERIEENTR),
                                                        C_NOTAS_DETAL.NRNOTAFISC,C_NOTAS_DETAL.DTEMISSAO,C_NOTAS_DETAL.DTENTRSAID,
                                                        C_NOTAS_DETAL.VRNOTAFISC,P_PIS,P_PIS_RET_E,P_PIS_RET_S,P_COFINS,P_COFINS_RET_E,
                                                        P_COFINS_RET_S,P_ISS_E,P_ISS_S,P_ISS_RET_E,P_ISS_RET_S,V_NEWESTAB,P_CDPAIS,
                                                        P_CDEMPRESA,NVL(V_SEQD,0),P_ICMS,P_DTFINAL,P_DTINICIAL,C_NOTAS_DETAL.CDMODDOCFISC,
                                                        V_VRDESTOTIT,V_IDOUTROS,V_SEQESTAB,V_TMPBLCM,C_NOTAS_DETAL.NRACESSONFE,
                                                        C_NOTAS_DETAL.CDTIPOCTE),0);
               V_TOTBLCD  := TO_NUMBER(SUBSTR(V_RESULTADO,1,20));
               V_SEQD     := NVL(V_TOTBLCD, 0);
               V_SEQESTAB := NVL(V_SEQESTAB, 0) + TO_NUMBER(SUBSTR(V_RESULTADO,22,20));
               V_TMPBLCM  := NVL(V_TMPBLCM, 0)  + TO_NUMBER(SUBSTR(V_RESULTADO,43,20));
               IDGEROUBCLACD:= 'S';
               /*V_IDNOVOESTD:= 'N';*/
            END IF;

            /*VEFIFICA SE A NOTA FOI GERADA NOS BLOCOS A, C OU D. CASO NAO TENHA SIDO GERADA INSERE O LANCAMENTO PARA GERAR PELO BLOCO F*/
            IF IDGEROUBCLACD = 'N' THEN
               --V_SEQESTAB:= NVL(V_SEQESTAB,0)+1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDCAMPO2, CDCAMPO3, CDESTAB)
               VALUES
                  ('F100', V_SEQESTAB, C_NOTAS_DETAL.CDFILIAL||C_NOTAS_DETAL.NRLANCTONF,C_NOTAS_DETAL.CDFILIAL,C_NOTAS_DETAL.NRLANCTONF, C_NOTAS_DETAL.IDIMPOSTO,'0000');
            END IF;

            V_SEMMOVIMENTO := 'N';
         END LOOP;/*END C_NOTAS_DETAL*/

   ---------------------------------------   /*GERA O REGISTRO C380 - NOTAS FISCAIS MODELO 02*/------------------------------------------------------------
         FOR C_REGC380 IN (SELECT '|C380'                                   ||'|'||
                                  '02'                                      ||'|'||
                                  TO_CHAR(DTEMISSAO,'ddmmyyyy')             ||'|'||
                                  TO_CHAR(DTEMISSAO,'ddmmyyyy')             ||'|'||
                                  SUBSTR(LPAD(MIN(MIN_NUM_DOC),10,'0'),5,6) ||'|'||
                                  SUBSTR(LPAD(MIN(MAX_NUM_DOC),10,'0'),5,6) ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRNOTAFISC),2), '999999999990.00')), '.', ',')       || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(SUM(CANC_VRNOTAFISC),2), '999999999990.00')), '.', ',')  || '|' AS C380,
                                  ESTABELECIMENTO,DTEMISSAO, MAX(CDFILIEMIT) AS CDFILIEMIT,NRINSJUREST
                             FROM (SELECT SUM(CDCAMPO8) AS VRNOTAFISC, DECODE(CDCAMPO3,'C',SUM(CDCAMPO8),0) AS CANC_VRNOTAFISC,
                                          MIN(CDCAMPO6) AS  MIN_NUM_DOC, MAX(CDCAMPO6) AS MAX_NUM_DOC,
                                          TO_DATE(CDCAMPO1) AS DTEMISSAO, CDESTAB AS ESTABELECIMENTO, MAX(CDCAMPO7) AS CDFILIEMIT,NRINSJUREST
                                     FROM TMP_REGDADOSSPED_PIS
                                    WHERE CDREGISTRO = 'C380'
                                    GROUP BY CDCAMPO1,CDCAMPO3,CDESTAB,NRINSJUREST)
                            GROUP BY DTEMISSAO,ESTABELECIMENTO,NRINSJUREST)LOOP
            V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000'))||'02'||LPAD(RTRIM(V_TOTLINHAS),22,'0'),C_REGC380.C380);
            V_SEQC:= NVL(V_SEQC,0)+1;

            /*REGISTRO C381*/
            FOR REGC381 IN (SELECT '|C381'                  ||'|'||
                                   CDSITUTRIBITNFE          ||'|'||
                                   CDARVPROD                ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(VRTOTAITEM),'9999999999900.00')),'.',',') ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(VRBASECALCIT),'9999999999900.00')),'.',',')||'|'||
                                   REPLACE(LTRIM(TO_CHAR(VRALIQITEM,'9900.0000')),'.',',')||'|'||
                                   ''                                                                                    ||'|'||
                                   ''                                                                                    ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(VRIMPOSTOIT),'9999999999900.00')),'.',',')||'|'||
                                   CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,CDFILIEMIT) ||'|'/*CÓDIGO DA CONTA ANALÍTICA*/ AS C381,
                                   CDNATUBCALPCIT, CDTPCREDPICOITE, CDSITUTRIBITNFE, CDARVPROD, SGUNIDADE, CDNATUREC, SUM(VRTOTAITEM) AS VRTOTAITEM,
                                   SUM(VRBASECALCIT) AS VRBASECALCIT, SUM(VRIMPOSTOIT) AS VRIMPOSTOIT,VRALIQITEM, MAX(CDFILIEMIT) AS CDFILIEMIT,
                                   CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,CDFILIEMIT) AS COD_CTA
                              FROM (SELECT ROUND(IP.VRTOTAITEM,2) AS VRTOTAITEM,
                                           CDTPCREDPICOITE,
                                           DECODE(CDFISCALIT,'1',VRBASECALCIT,0) AS VRBASECALCIT ,
                                           ROUND(VRALIQITEM,4) AS VRALIQITEM,
                                           ROUND(DECODE(CDFISCALIT,'1',VRIMPOSTOIT,0),2) AS VRIMPOSTOIT,
                                           CDNATUBCALPCIT,
                                           CDSITUTRIBITNFE,
                                           P.CDARVPROD AS CDARVPROD,
                                           SGUNIDADE,
                                           CDFISCALIT,
                                           CDNATUREC,
                                           CDCAMPO7 AS CDFILIEMIT,
                                           P.CDPRODUTO,
                                           CDCAMPO9 AS CDTIPOOPER
                                      FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, PRODUTO P, TMP_REGDADOSSPED_PIS TMP, IMPOSTO IM, RELACPRODITGEN R
                                     WHERE IT.CDIMPOSTO    = IM.CDIMPOSTO
                                       AND IM.CDIMPOSTONFE = P_PIS
                                       AND IT.NRLANCTONF   = IP.NRLANCTONF
                                       AND IT.CDFILIAL     = IP.CDFILIAL
                                       AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                       AND IT.NRITPROIMPOS = '1'
                                       AND P.CDPRODUTO     = IP.CDPRODUTO
                                       AND TMP.CDREGISTRO  = 'C380'
                                       AND TMP.CDCAMPO4    = IT.CDFILIAL
                                       AND TMP.CDCAMPO5    = IT.NRLANCTONF
                                       AND TMP.CDCAMPO4    = IP.CDFILIAL
                                       AND TMP.CDCAMPO5    = IP.NRLANCTONF
                                       AND TMP.CDESTAB     = C_REGC380.ESTABELECIMENTO
                                       AND TMP.CDCAMPO1    = C_REGC380.DTEMISSAO
                                       AND P.CDPRODUTO     = R.CDPRODUTO(+)
                                       AND P_LIVROMAG      = R.CDLIVROMAG(+))
                                     GROUP BY CDNATUBCALPCIT, CDTPCREDPICOITE, CDSITUTRIBITNFE, CDARVPROD, SGUNIDADE,CDNATUREC,
                                           VRALIQITEM, CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,CDFILIEMIT))LOOP
               V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000'))||'02'||LPAD(RTRIM(V_TOTLINHAS),22,'0'),REGC381.C381);

               V_SEQC   := NVL(V_SEQC,0)+1;
               V_PRODUTO:=NULL;

               /* CODIGO DO PRODUTO */
               SELECT MAX(CDCHAVEREG)
                 INTO V_PRODUTO
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = REGC381.CDARVPROD
                  AND CDREGISTRO = '0200'
                  AND CDESTAB = LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000'));

               IF (V_PRODUTO IS NULL) AND (REGC381.CDARVPROD IS NOT NULL) THEN
                  V_SEQESTAB := NVL(V_SEQESTAB,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                  VALUES
                     ('0200', V_SEQESTAB, REGC381.CDARVPROD,NULL,LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000')));
               END IF;

               V_UNID:= NULL;
               /* UNIDADE DE MEDIDA */
               SELECT MAX(CDCHAVEREG)
                 INTO V_UNID
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = REGC381.SGUNIDADE
                  AND CDREGISTRO = '0190'
                  AND CDESTAB = LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000'));
               IF (V_UNID IS NULL) AND (REGC381.SGUNIDADE IS NOT NULL) THEN
                  V_SEQESTAB := NVL(V_SEQESTAB,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                  VALUES
                     ('0190', V_SEQESTAB, REGC381.SGUNIDADE,NULL,LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000')));
               END IF;

               /* CODIGO DA CONTA CONTÁBIL */
               BEGIN
                  SELECT MAX(CDCHAVEREG)
                    INTO V_COD_CC
                    FROM TMP_REGDADOSSPED_PIS
                   WHERE CDCHAVEREG = REGC381.COD_CTA
                     AND CDREGISTRO = '0500';
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_COD_CC := NULL;
               END;

               IF (V_COD_CC IS NULL) AND (REGC381.COD_CTA IS NOT NULL) THEN
                  V_SEQCCTBL := NVL(V_SEQCCTBL,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
                  VALUES
                     ('0500', V_SEQCCTBL, REGC381.COD_CTA, NULL);
               END IF;
            END LOOP;

            FOR REGC385 IN (SELECT '|C385'                                                              ||'|'||
                                   CDSITUTRIBITNFE                                                      ||'|'||
                                   CDARVPROD                                                            ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(VRTOTAITEM),'9999999999900.00')),'.',',')  ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(VRBASECALCIT),'9999999999900.00')),'.',',')||'|'||
                                   REPLACE(LTRIM(TO_CHAR(VRALIQITEM,'9900.0000')),'.',',')              ||'|'||
                                   ''                                                                   ||'|'||
                                   ''                                                                   ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(VRIMPOSTOIT),'9999999999900.00')),'.',',') ||'|'||
                                   CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,CDFILIEMIT)                   ||'|'/*CÓDIGO DA CONTA ANALÍTICA*/ AS C385,
                                   CDNATUBCALPCIT, CDTPCREDPICOITE, CDSITUTRIBITNFE, CDARVPROD, SGUNIDADE, CDNATUREC, SUM(VRTOTAITEM) AS VRTOTAITEM,
                                   SUM(VRBASECALCIT) AS VRBASECALCIT, SUM(VRIMPOSTOIT) AS VRIMPOSTOIT,VRALIQITEM, MAX(CDFILIEMIT) AS CDFILIEMIT,
                                   CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,CDFILIEMIT)AS COD_CTA
                              FROM (SELECT ROUND(IP.VRTOTAITEM,2) AS VRTOTAITEM,
                                           CDTPCREDPICOITE,
                                           DECODE(CDFISCALIT,'1',VRBASECALCIT,0) AS VRBASECALCIT ,
                                           ROUND(VRALIQITEM,4) AS VRALIQITEM,
                                           ROUND(DECODE(CDFISCALIT,'1',VRIMPOSTOIT,0),2) AS VRIMPOSTOIT,
                                           CDNATUBCALPCIT,
                                           CDSITUTRIBITNFE,
                                           P.CDARVPROD AS CDARVPROD,
                                           SGUNIDADE,
                                           CDFISCALIT,
                                           CDNATUREC,
                                           CDCAMPO7 AS CDFILIEMIT,
                                           P.CDPRODUTO,
                                           CDCAMPO9 AS CDTIPOOPER
                                      FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, PRODUTO P, TMP_REGDADOSSPED_PIS TMP, IMPOSTO IM, RELACPRODITGEN R
                                     WHERE IT.CDIMPOSTO    = IM.CDIMPOSTO
                                       AND IM.CDIMPOSTONFE  = P_COFINS
                                       AND IT.NRLANCTONF   = IP.NRLANCTONF
                                       AND IT.CDFILIAL     = IP.CDFILIAL
                                       AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                       AND IT.NRITPROIMPOS = '1'
                                       AND P.CDPRODUTO     = IP.CDPRODUTO
                                       AND TMP.CDREGISTRO  = 'C380'
                                       AND TMP.CDCAMPO4    = IT.CDFILIAL
                                       AND TMP.CDCAMPO5    = IT.NRLANCTONF
                                       AND TMP.CDCAMPO4    = IP.CDFILIAL
                                       AND TMP.CDCAMPO5    = IP.NRLANCTONF
                                       AND TMP.CDESTAB     = C_REGC380.ESTABELECIMENTO
                                       AND TMP.CDCAMPO1    = C_REGC380.DTEMISSAO
                                       AND P.CDPRODUTO     = R.CDPRODUTO(+)
                                       AND P_LIVROMAG      = R.CDLIVROMAG(+))
                                     GROUP BY CDNATUBCALPCIT, CDTPCREDPICOITE, CDSITUTRIBITNFE, CDARVPROD, SGUNIDADE,CDNATUREC,
                                           VRALIQITEM, CONTCTBL_SPED('S',CDPRODUTO,CDTIPOOPER,CDFILIEMIT))LOOP

               V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
               INSERT INTO ARQMAGPISCOFINS
                  (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
               VALUES
                  (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000'))||'02'||LPAD(RTRIM(V_TOTLINHAS),22,'0'),REGC385.C385);
               V_SEQC   := NVL(V_SEQC,0)+1;
               V_PRODUTO:=NULL;

               /* CODIGO DO PRODUTO */
               SELECT MAX(CDCHAVEREG)
                 INTO V_PRODUTO
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = REGC385.CDARVPROD
                  AND CDREGISTRO = '0200'
                  AND CDESTAB = LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000'));

               IF (V_PRODUTO IS NULL) AND (REGC385.CDARVPROD IS NOT NULL) THEN
                  V_SEQESTAB := NVL(V_SEQESTAB,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                  VALUES
                     ('0200', V_SEQESTAB, REGC385.CDARVPROD,NULL,LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000')));
               END IF;

               V_UNID:= NULL;
               /* UNIDADE DE MEDIDA */
               SELECT MAX(CDCHAVEREG)
                 INTO V_UNID
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = REGC385.SGUNIDADE
                  AND CDREGISTRO = '0190'
                  AND CDESTAB = LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000'));

               IF (V_UNID IS NULL) AND (REGC385.SGUNIDADE IS NOT NULL) THEN
                  V_SEQESTAB := NVL(V_SEQESTAB,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
                  VALUES
                     ('0190', V_SEQESTAB, REGC385.SGUNIDADE,NULL,LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000')));
               END IF;

               /* CODIGO DA CONTA CONTÁBIL */
               BEGIN
                  SELECT MAX(CDCHAVEREG)
                    INTO V_COD_CC
                    FROM TMP_REGDADOSSPED_PIS
                   WHERE CDCHAVEREG = REGC385.COD_CTA
                     AND CDREGISTRO = '0500';
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_COD_CC := NULL;
               END;

               IF (V_COD_CC IS NULL) AND (REGC385.COD_CTA IS NOT NULL) THEN
                  V_SEQCCTBL := NVL(V_SEQCCTBL,0) + 1;
                  INSERT INTO TMP_REGDADOSSPED_PIS
                     (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
                  VALUES
                     ('0500', V_SEQCCTBL, REGC385.COD_CTA, NULL);
               END IF;
            END LOOP;
            /*INSERE O VALOR REFERENTE AO PIS PARA SAÍDAS(DÉBITO)*/
            /*SEPARA OS VALORES DO C381 POR REGIME, PARA GERAR O M200 CORRETAMENTE*/
            FOR REGC381_VR IN (SELECT CDTPCREDPICOITE,
                                      SUM(DECODE(CDFISCALIT,'1',VRBASECALCIT,0)) AS VRBASECALCIT,
                                      ROUND(VRALIQITEM,4) AS VRALIQITEM,
                                      SUM(ROUND(DECODE(CDFISCALIT,'1',VRIMPOSTOIT,0),2)) AS VRIMPOSTOIT,
                                      CDSITUTRIBITNFE,
                                      MAX(CDCAMPO7) AS CDFILIEMIT,
                                      CDNATUBCALPCIT,
                                      SUM(ROUND(IP.VRTOTAITEM,2)) AS VRTOTAITEM,
                                      CDNATUREC,
                                      IDCUMUPISCOFIL,
                                      CONTCTBL_SPED('S',IP.CDPRODUTO,TMP.CDCAMPO9,TMP.CDCAMPO7) AS COD_CTA
                                 FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, TMP_REGDADOSSPED_PIS TMP, IMPOSTO IM, RELACPRODITGEN R, FILIAL F
                                WHERE IT.CDIMPOSTO    = IM.CDIMPOSTO
                                  AND IM.CDIMPOSTONFE = P_PIS
                                  AND IT.NRLANCTONF   = IP.NRLANCTONF
                                  AND IT.CDFILIAL     = IP.CDFILIAL
                                  AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                  AND IT.NRITPROIMPOS = '1'
                                  AND TMP.CDREGISTRO  = 'C380'
                                  AND TMP.CDCAMPO4    = IT.CDFILIAL
                                  AND TMP.CDCAMPO5    = IT.NRLANCTONF
                                  AND TMP.CDCAMPO4    = IP.CDFILIAL
                                  AND TMP.CDCAMPO5    = IP.NRLANCTONF
                                  AND TMP.CDESTAB     = C_REGC380.ESTABELECIMENTO
                                  AND TMP.CDCAMPO1    = C_REGC380.DTEMISSAO
                                  AND IP.CDPRODUTO    = R.CDPRODUTO(+)
                                  AND P_LIVROMAG      = R.CDLIVROMAG(+)
                                  AND CDCAMPO7        = F.CDFILIAL
                                GROUP BY CDTPCREDPICOITE, ROUND(VRALIQITEM,4), CDSITUTRIBITNFE, CDNATUBCALPCIT, CDNATUREC,
                                      IDCUMUPISCOFIL, CONTCTBL_SPED('S',IP.CDPRODUTO,TMP.CDCAMPO9,TMP.CDCAMPO7)) LOOP
            V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
            INSERT INTO TMP_REGBLOCOM_PIS
               (CHAVENOTA, CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF,CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
            VALUES
               ('C381'||REGC381_VR.CDFILIEMIT,'M200',V_TMPBLCM,REGC381_VR.CDTPCREDPICOITE,REGC381_VR.VRBASECALCIT,REGC381_VR.VRALIQITEM,'P',REGC381_VR.VRIMPOSTOIT,REGC381_VR.CDSITUTRIBITNFE, REGC381_VR.CDFILIEMIT,REGC381_VR.CDNATUBCALPCIT,'0',REGC381_VR.VRTOTAITEM,REGC381_VR.CDNATUREC,LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000')),C_REGC380.NRINSJUREST,REGC381_VR.COD_CTA);

            END LOOP;
            /*INSERE O VALOR REFERENTE AO COFINS SAÍDAS(DÉBITO)*/
            /*SEPARA OS VALORES DO C385 POR REGIME, PARA GERAR O M600 CORRETAMENTE*/
            FOR REGC385_VR IN (SELECT CDTPCREDPICOITE,
                                      SUM(DECODE(CDFISCALIT,'1',VRBASECALCIT,0)) AS VRBASECALCIT,
                                      ROUND(VRALIQITEM,4) AS VRALIQITEM,
                                      SUM(ROUND(DECODE(CDFISCALIT,'1',VRIMPOSTOIT,0),2)) AS VRIMPOSTOIT,
                                      CDSITUTRIBITNFE,
                                      MAX(CDCAMPO7) AS CDFILIEMIT,
                                      CDNATUBCALPCIT,
                                      SUM(ROUND(IP.VRTOTAITEM,2)) AS VRTOTAITEM,
                                      CDNATUREC,
                                      IDCUMUPISCOFIL,
                                      CONTCTBL_SPED('S',IP.CDPRODUTO,TMP.CDCAMPO9,TMP.CDCAMPO7) AS COD_CTA
                                 FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, TMP_REGDADOSSPED_PIS TMP, IMPOSTO IM, RELACPRODITGEN R, FILIAL F
                                WHERE IT.CDIMPOSTO    = IM.CDIMPOSTO
                                  AND IM.CDIMPOSTONFE = P_COFINS
                                  AND IT.NRLANCTONF   = IP.NRLANCTONF
                                  AND IT.CDFILIAL     = IP.CDFILIAL
                                  AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                  AND IT.NRITPROIMPOS = '1'
                                  AND TMP.CDREGISTRO  = 'C380'
                                  AND TMP.CDCAMPO4    = IT.CDFILIAL
                                  AND TMP.CDCAMPO5    = IT.NRLANCTONF
                                  AND TMP.CDCAMPO4    = IP.CDFILIAL
                                  AND TMP.CDCAMPO5    = IP.NRLANCTONF
                                  AND TMP.CDESTAB     = C_REGC380.ESTABELECIMENTO
                                  AND TMP.CDCAMPO1    = C_REGC380.DTEMISSAO
                                  AND IP.CDPRODUTO    = R.CDPRODUTO(+)
                                  AND P_LIVROMAG      = R.CDLIVROMAG(+)
                                  AND CDCAMPO7        = F.CDFILIAL
                                GROUP BY CDTPCREDPICOITE, ROUND(VRALIQITEM,4), CDSITUTRIBITNFE, CDNATUBCALPCIT, CDNATUREC,
                                      IDCUMUPISCOFIL, CONTCTBL_SPED('S',IP.CDPRODUTO,TMP.CDCAMPO9,TMP.CDCAMPO7)) LOOP

               V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT,VRIMPRETIDO,VRTOTNF, CDNATUREC,CDESTAB,NRINSJUREST,CDCONTCTBL)
               VALUES
                  ('C385'||REGC385_VR.CDFILIEMIT,'M600',V_TMPBLCM,REGC385_VR.CDTPCREDPICOITE,REGC385_VR.VRBASECALCIT,REGC385_VR.VRALIQITEM,'P',REGC385_VR.VRIMPOSTOIT,REGC385_VR.CDSITUTRIBITNFE,REGC385_VR.CDFILIEMIT,REGC385_VR.CDNATUBCALPCIT,'0',REGC385_VR.VRTOTAITEM,REGC385_VR.CDNATUREC,LTRIM(TO_CHAR(C_REGC380.ESTABELECIMENTO,'000')),C_REGC380.NRINSJUREST,REGC385_VR.COD_CTA);
            END LOOP;
         END LOOP;

     -----------------------------------------------------------------/*GERAÇÃO DAS ROTINAS DE ECF*/-----------------------------------------------------------------------
         V_TOTBLCC := SPED_PIS_COFINS_BLOCOC_ECF(V_CONTEST,P_NRSEQARQMAG,P_DTINICIAL,P_DTFINAL,P_CDEMPRESA,P_PIS,P_COFINS,P_LIVROMAG,NVL(V_SEQC,0),V_COUNTBL0ECF,V_CONTEST);
         V_SEQC    := NVL(V_TOTBLCC,0);
         V_TOTBLC0 := NVL(V_TOTBLC0,0) + NVL(V_COUNTBL0ECF,0);
      END IF;/*FIM ESCRITURAÇÃO DETALHADA*/
   --------------------------------------------------------------------------------   /*CHAMADA DO BLOCO F */------------------------------------------------------------
      V_TOTALBCLF:= SPED_PIS_COFINS_BLOCOF(P_CDEMPRESA,P_NRSEQARQMAG,P_DTINICIAL,P_DTFINAL,P_CDINCTRIB,P_CRTESCRITUR,P_PIS,P_PIS_RET_E,P_PIS_RET_S,P_COFINS,P_COFINS_RET_E,P_COFINS_RET_S,V_NEWESTAB,P_LIVROMAG,V_SEQESTABBLCF);
      V_SEQESTAB:= NVL(V_SEQESTAB,0) +  NVL(V_SEQESTABBLCF,0);
      V_TOTBLC0:= NVL(V_TOTBLC0,0) + NVL(V_SEQESTABBLCF,0);
   -------------------------------------------/*GERA O 0140 PARA ARQUIVOS SEM MOVIMENTO*/---------------------------------------------------------------------------------
      IF ((V_SEMMOVIMENTO = 'S') AND (NVL(V_TOTBLCC,0) <= 0) AND (NVL(V_TOTALBCLF,0) <= 0))  THEN /*VERIFICA SE O ARQUIVO NÃO TEVE NENHUMA NOTA FISCAL PARA GERAR O 0140*/
         SELECT '|0140'         ||'|'||
                LTRIM(TO_CHAR(V_NEWESTAB,'000'))      ||'|'||
                E.NMRAZSOCEMPR  ||'|'||
                LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0') ||'|'||
                E.SGESTADO     ||'|'||
                ''/*REPLACE(REPLACE(REPLACE(REPLACE(IE.CDINSCESTA,'.',''),'/',''),'-',''),'\','')*/ ||'|'||
                M.CDMUNICIBGE   ||'|'||
                ''             ||'|'||
                ''             ||'|' AS REG,
                LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0') AS INSJUREST
           INTO V_REG_0140, V_INSJUREST
           FROM EMPRESA E, MUNICIPIO M
          WHERE E.CDEMPRESA    = P_CDEMPRESA
            AND E.CDPAIS       = M.CDPAIS
            AND E.SGESTADO     = M.SGESTADO
            AND E.CDMUNICIPIO  = M.CDMUNICIPIO;

         V_TOTBLC0  := NVL(V_TOTBLC0,0)+1;
         V_TOTLINHAS:= NVL(V_TOTLINHAS,0)+1;
         V_CONTEST  := NVL(V_CONTEST,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'104'||LTRIM(TO_CHAR(V_CONTEST,'000')),V_REG_0140);

         V_SEQESTAB:= NVL(V_SEQESTAB,0)+1;
         INSERT INTO TMP_REGDADOSSPED_PIS
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
         VALUES
           ('0140', V_SEQESTAB, V_INSJUREST ,NULL,LTRIM(TO_CHAR(V_CONTEST,'000')));
      END IF;

   ------------------------------------------/*REGISTRO 0111 - RATEIO PROPORCIONAL*/-------------------------------------------------------------------------------------
      /*REGISTRO 0111*/
      IF P_INDAPROCRED = '2' THEN
         FOR REG_0111 IN (SELECT '|0111'                                                                ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRRECNCINT),   '9999999999900.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRRECNCUMISEN),'9999999999900.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(RECNCUMEXP),   '9999999999900.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(VRRECCUM),     '9999999999900.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(SUM(NVL(VRRECNCINT,0)+NVL(VRRECNCUMISEN,0)+NVL(RECNCUMEXP,0)+NVL(VRRECCUM,0)),'9999999999900.00')),'.',',') ||'|' AS REG0111
                            FROM (SELECT  CASE WHEN CSTPISCOFINS NOT IN ('06','07', '08','09') THEN DECODE(IDCUMUPISCOFIL,'S',SUM(VRCRED),0) ELSE 0 END  AS VRRECNCINT,
                                          CASE WHEN CSTPISCOFINS IN ('06','07', '08','09') THEN SUM(VRCRED) ELSE 0 END AS VRRECNCUMISEN,
                                          '0' AS RECNCUMEXP,
                                          DECODE(IDCUMUPISCOFIL,'N',SUM(VRCRED),0) AS VRRECCUM
                                     FROM TMP_REGBLOCOM_PIS T, FILIAL F
                                    WHERE CDREGISTRO = 'M200'
                                      AND T.CDFILIAL = F.CDFILIAL
                                    GROUP BY IDCUMUPISCOFIL, CSTPISCOFINS)
                           GROUP BY '|0111')LOOP

            V_TOTLINHAS := NVL(V_TOTLINHAS,0)+1;
            V_TOTBLC0   := NVL(V_TOTBLC0,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'103'||LPAD(RTRIM(V_TOTLINHAS),22,'0'),REG_0111.REG0111);
         END LOOP;
      END IF;
--------------------------/*CHAMADA DO BLOCO M - APURAÇÃO DO PIS E COFINS*/-----------------------------------------------
      /*GERA BLOCO M*/
      V_TOTBLCM:= SPED_PIS_COFINS_BLOCOM(V_CONTEST,P_CDEMPRESA,P_NRSEQARQMAG,P_DTINICIAL,P_DTFINAL,P_LIVROMAG,P_CDINCTRIB,P_GERABCLP,P_PIS,P_COFINS, V_GERADEVOLVEND,P_VERSAO,V_CONTBLC0M);
      V_TOTBLC0 := NVL(V_TOTBLC0,0) + NVL(V_CONTBLC0M,0);
   END IF;
-------------------------------------------------/*CHAMADA DO BLOCO P*/---------------------------------------------------
   IF P_GERABCLP IN ('1','2') THEN
      V_RESULTADO:= SPED_PIS_COFINS_BLOCOP(P_CDEMPRESA,P_NRSEQARQMAG,P_DTINICIAL,P_DTFINAL,V_SEQESTAB,V_CONTEST,V_COUNTBL0P);
      V_TOTBLCP  := SUBSTR(V_RESULTADO,1,20);
      V_TOTBLC0  := NVL(V_TOTBLC0,0) + NVL(V_COUNTBL0P,0);
   END IF;
------------------------------------------------/*INSERE ABERTURA E FECHAMENTO DOS BLOCOS*/ ------------------------------
   /*O A ABERTURA DO BLOCO A */
   IF V_TOTBLCA > 0 THEN
      V_REG_A001 := '|A001|0|';
   ELSE
      V_REG_A001 := '|A001|1|';
   END IF;

   /*INSERE O REGISTRO A001*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'201',V_REG_A001);
   V_TOTLINHAS:= NVL(V_TOTLINHAS,0)+2; /*INSERE 2 DEVIDO AO ENCERRAMENTO*/
   V_TOTBLCA:= NVL(V_TOTBLCA,0)+2;

   V_SEQA:= NVL(V_SEQA,0)+2;
   /*INSERE O BLOCO A990 - ENCERRAMENTO*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'208','|A990|'||NVL(V_SEQA,2)||'|');

   /*O A ABERTURA DO BOCO C */
   IF V_TOTBLCC > 0 THEN
      V_REG_C001 := '|C001|0|';
   ELSE
      V_REG_C001 := '|C001|1|';
   END IF;

   /*INSERE O REGISTRO C001*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'301',V_REG_C001);
   V_TOTLINHAS:= NVL(V_TOTLINHAS,0)+2; /*INSERE 2 DEVIDO AO ENCERRAMENTO*/

   V_SEQC:= NVL(V_SEQC,0)+2;
   /*INSERE O BLOCO C990 - ENCERRAMENTO*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'318','|C990|'||NVL(V_SEQC,2)||'|');

   /*O A ABERTURA DO BOCO D */
   IF V_TOTBLCD > 0 THEN
      V_REG_D001 := '|D001|0|';
   ELSE
      V_REG_D001 := '|D001|1|';
   END IF;

   /*INSERE O REGISTRO D001*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'401',V_REG_D001);
   V_TOTLINHAS:= NVL(V_TOTLINHAS,0)+2; /*INSERE 2 DEVIDO AO ENCERRAMENTO*/

   V_SEQD:= NVL(V_SEQD,0)+2;
   /*INSERE O BLOCO D990 - ENCERRAMENTO*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'416','|D990|'||NVL(V_SEQD,2)||'|');

   /*ABERTURA DO BLOCO F*/
   IF V_TOTALBCLF > 0 THEN
      V_REG_F001 := '|F001|0|';
   ELSE
      V_REG_F001 := '|F001|1|';
   END IF;

   /*INSERE O REGISTRO F001*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'501',V_REG_F001);
   V_TOTLINHAS := NVL(V_TOTLINHAS,0)+2; /*INSERE 2 DEVIDO AO ENCERRAMENTO*/

   V_TOTALBCLF := NVL(V_TOTALBCLF,0) + 2;
   /*INSERE O BLOCO F990 - ENCERRAMENTO*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'509','|F990|'||NVL(V_TOTALBCLF,2)||'|');

   IF V_TOTBLCM > 0 THEN
      V_REG_M001 := '|M001|0|';
   ELSE
      V_REG_M001 := '|M001|1|';
   END IF;

   /*INSERE O REGISTRO M001*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'601',V_REG_M001);
   V_TOTLINHAS:= NVL(V_TOTLINHAS,0)+2; /*INSERE 2 DEVIDO AO ENCERRAMENTO*/

   V_TOTBLCM:= NVL(V_TOTBLCM,0)+2;/*INSERE 2 DEVIDO AO ENCERRAMENTO*/
   /*INSERE O BLOCO M990 - ENCERRAMENTO*/
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'690','|M990|'||V_TOTBLCM||'|');

   IF P_GERABCLP IN ('1','2') THEN
      IF V_TOTBLCP > 0 THEN
         V_REG_P001 := '|P001|0|';
      ELSE
         V_REG_P001 := '|P001|1|';
      END IF;
   END IF;

   IF P_GERABCLP IN ('1','2') THEN
      /*INSERE O REGISTRO P001*/
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'701',V_REG_P001);
      V_TOTLINHAS:= NVL(V_TOTLINHAS,0)+2; /*INSERE 2 DEVIDO AO ENCERRAMENTO*/

      V_TOTBLCP:= NVL(V_TOTBLCP,0) + 2;
      /*INSERE O BLOCO P990 - ENCERRAMENTO*/
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'709','|P990|'||V_TOTBLCP||'|');
   END IF;

   SELECT COUNT(*) AS QTDE
     INTO V_CONTREG1
     FROM ARQMAGPISCOFINS
    WHERE SUBSTR(DSREGARQMAG,2,1) = '1'
      AND NRSEQARQMAG = P_NRSEQARQMAG;

   IF P_VERSAO >= '005' THEN
      /* REGISTRO 1050 */
      FOR C_REG1050 IN (SELECT '|1050'         ||'|'||
                               TO_CHAR(NVL(A.DTAJUSTE,P_DTFINAL),'DDMMYYYY') ||'|'||
                               A.CDAJUSTEBC    ||'|'||
                               I.NRINSJURIEST  ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VRBASECALCAJ,0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'01',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'02',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'03',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'04',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'05',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'06',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'07',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'08',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'09',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'49',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               REPLACE(LTRIM(TO_CHAR(SUM(DECODE(A.CDSITUTRIBITNFE,'99',NVL(A.VRBASECALCAJ,0),0)),'999999999990.00')),'.',',') ||'|'||
                               A.IDAPROPRIACAO ||'|'||
                               A.NRRECIBO      ||'|'||
                               ''              ||'|' AS REG_1050
                          FROM AJUSTEAPURCONTR A, FILIAL F, INSCRESTAD I, MAPAAPURAC M
                         WHERE M.CDEMPRESA     = P_CDEMPRESA
                           AND M.CDIMPOSTO     IN (P_PIS, P_COFINS)
                           AND M.DTINIPERIODO  = P_DTINICIAL
                           AND M.DTFIMPERIODO  = P_DTFINAL
                           AND M.NRSEQUMAPAPU  = A.NRSEQUMAPAPU
                           AND A.CDFILIAL      = F.CDFILIAL
                           AND F.CDEMPRESA     = I.CDEMPRESA
                           AND F.CDINSCESTA    = I.CDINSCESTA
                           AND A.IDINDICAJUSTE = 'DB'
                         GROUP BY A.DTAJUSTE, A.CDAJUSTEBC, I.NRINSJURIEST, A.IDAPROPRIACAO, A.NRRECIBO) LOOP

         V_CONTREG1:= NVL(V_CONTREG1,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'8010'||V_CONTREG1,C_REG1050.REG_1050);

      END LOOP;
   END IF;

   IF (((P_GERABCLP IS NULL) OR (P_GERABCLP IN ('0','1'))) AND (P_CRTESCRITUR IN ('1','2'))) THEN
      BEGIN
         SELECT NRSEQUMAPAPU AS NRSEQUMAPPIS
           INTO V_NRSEQUMAPPIS
           FROM MAPAAPURAC MP
          WHERE MP.CDEMPRESA = P_CDEMPRESA
            AND MP.CDIMPOSTO = P_PIS
            AND MP.DTINIPERIODO BETWEEN P_DTINICIAL AND P_DTFINAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NRSEQUMAPPIS := NULL;
      END;

      /* REGISTRO 1900 */
      FOR C_1900 IN (SELECT '|1900'         ||'|'||
                            CNPJ            ||'|'||
                            COD_MOD         ||'|'||
                            SER             ||'|'||
                            SUB_SER         ||'|'||
                            COD_SIT         ||'|'||
                            REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VL_TOT_REC),2), '999999999990.00')), '.', ',') ||'|'||
                            SUM(QUANT_DOC)  ||'|'||
                            CST_PIS         ||'|'||
                            CST_COFINS      ||'|'||
                            CFOP            ||'|'||
                            INF_COMPL       ||'|'||
                            COD_CTA         ||'|' AS REGISTRO, COD_CTA,
                            TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CNPJ,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')) AS CNPJ
                       FROM (SELECT CNPJ, COD_MOD, SER, SUB_SER, COD_SIT, SUM(VL_TOT_REC) AS VL_TOT_REC, QUANT_DOC, MAX(CST_PIS) AS CST_PIS,
                                    MAX(CST_COFINS) AS CST_COFINS, CFOP, INF_COMPL, COD_CTA
                               FROM (SELECT E.NRINSJUREMPR AS CNPJ,
                                            DECODE(SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC) AS COD_MOD,
                                            N.CDSERIE AS SER, '' AS SUB_SER,
                                            CASE WHEN N.IDSTATUS = 'C' THEN '02' WHEN N.IDSTATUS IN ('A','E') THEN '00' ELSE '99' END AS COD_SIT,
                                            SUM(DECODE(IM.CDIMPOSTONFE,P_PIS,I.VRCONTITIMP,0)) AS VL_TOT_REC,
                                            COUNT(DISTINCT N.NRLANCTONF) AS QUANT_DOC,
                                            DECODE(IM.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,'') AS CST_PIS,
                                            DECODE(IM.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,'') AS CST_COFINS,
                                            I.CDFISCOPERIT AS CFOP,
                                            '' AS INF_COMPL,
                                            CONTCTBL_SPED(N.IDENTRSAIDOP,P.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)) AS COD_CTA
                                       FROM TMP_REGDADOSSPED_PIS T, NOTAFISCAL N, EMPRESA E, ITEMPROIMPOS I, ITEMPRODUTO P, IMPOSTO IM
                                      WHERE T.CDREGISTRO   = 'NOTA'
                                        AND T.CDCAMPO1     = N.CDFILIAL
                                        AND T.CDCAMPO2     = N.NRLANCTONF
                                        AND N.IDENTRSAIDOP = 'S'
                                        AND E.CDEMPRESA    = P_CDEMPRESA
                                        AND I.CDFILIAL     = N.CDFILIAL
                                        AND I.NRLANCTONF   = N.NRLANCTONF
                                        AND I.CDIMPOSTO    = IM.CDIMPOSTO
                                        AND I.CDFILIAL     = P.CDFILIAL
                                        AND I.NRLANCTONF   = P.NRLANCTONF
                                        AND I.NRSEQITPROD  = P.NRSEQITPROD
                                        AND IM.CDIMPOSTONFE IN (P_PIS,P_COFINS)
                                        AND NOT EXISTS (SELECT CDFILIAL, NRLANCTONF
                                                          FROM IMPBAIXAREC
                                                         WHERE CDFILIAL     = T.CDCAMPO1
                                                           AND NRLANCTONF   = T.CDCAMPO2
                                                           AND NRSEQUMAPAPU = V_NRSEQUMAPPIS)
                                      GROUP BY E.NRINSJUREMPR, DECODE(SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC), N.CDSERIE,
                                               CASE WHEN N.IDSTATUS = 'C' THEN '02' WHEN N.IDSTATUS IN ('A','E') THEN '00' ELSE '99' END,
                                               DECODE(IM.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,''), DECODE(IM.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,''),
                                               I.CDFISCOPERIT, CONTCTBL_SPED(N.IDENTRSAIDOP,P.CDPRODUTO,N.CDTIPOOPER,DECODE(N.IDENTRSAIDOP,'E',N.CDFILIRECE,N.CDFILIEMIT)))
                              GROUP BY CNPJ, COD_MOD, SER, SUB_SER, COD_SIT, QUANT_DOC, CFOP, INF_COMPL, COD_CTA
                              UNION ALL
                             SELECT E.NRINSJUREMPR AS CNPJ,
                                    DECODE(SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC) AS COD_MOD,
                                    N.CDSERIE AS SER, '' AS SUB_SER,
                                    CASE WHEN N.IDSTATUS = 'C' THEN '02' WHEN N.IDSTATUS IN ('A','E') THEN '00' ELSE '99' END AS COD_SIT,
                                    SUM(VALOR_IMPOSTO_ITEM_NFE_CAIXA(N.CDFILIAL,N.NRLANCTONF,P_PIS,'C',I.DTBAIXAREC,'',I.CDFISCOPERNF,I.CDSITUTRIBITNFE,I.NRSEQUENCIAL)) AS VL_TOT_REC,
                                    COUNT(DISTINCT N.NRLANCTONF) AS QUANT_DOC,
                                    I.CDSITUTRIBITNFE AS CST_PIS,
                                    I.CDSITUTRIBITNFE AS CST_COFINS,
                                    I.CDFISCOPERNF AS CFOP,
                                    '' AS INF_COMPL,
                                    CONTCTBL_SPED(N.IDENTRSAIDOP,'',N.CDTIPOOPER,N.CDFILIEMIT) AS COD_CTA
                               FROM TMP_REGDADOSSPED_PIS T, NOTAFISCAL N, EMPRESA E, IMPBAIXAREC I, IMPOSTO IM
                              WHERE T.CDREGISTRO    = 'NOTA'
                                AND T.CDCAMPO1      = N.CDFILIAL
                                AND T.CDCAMPO2      = N.NRLANCTONF
                                AND N.IDENTRSAIDOP  = 'S'
                                AND E.CDEMPRESA     = P_CDEMPRESA
                                AND I.CDFILIAL      = N.CDFILIAL
                                AND I.NRLANCTONF    = N.NRLANCTONF
                                AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                AND IM.CDIMPOSTONFE = P_PIS
                                AND I.NRSEQUMAPAPU  = V_NRSEQUMAPPIS
                              GROUP BY E.NRINSJUREMPR, DECODE(SUBSTR(CDESTAB,LENGTH(CDESTAB)-2,3),'ISS','98',N.CDMODDOCFISC),
                                    N.CDSERIE, CASE WHEN N.IDSTATUS = 'C' THEN '02' WHEN N.IDSTATUS IN ('A','E') THEN '00' ELSE '99' END,
                                    I.CDSITUTRIBITNFE, I.CDSITUTRIBITNFE, I.CDFISCOPERNF, CONTCTBL_SPED(N.IDENTRSAIDOP,'',N.CDTIPOOPER,N.CDFILIEMIT)
                              UNION ALL
                             SELECT E.NRINSJUREMPR AS CNPJ,
                                    '99' AS COD_MOD,
                                    '' AS SER,
                                    '' AS SUB_SER,
                                    '99' AS COD_SIT,
                                    SUM(ABS(LC.VRLANCAMENT)) AS VL_TOT_REC,
                                    0 AS QUANT_DOC,
                                    GP.CDSITUTRIBPICO AS CST_PIS,
                                    GP.CDSITUTRIBPICO AS CST_COFINS,
                                    '' AS CFOP,
                                    '' AS INF_COMPL,
                                    CGP.CDCONTCTBL AS COD_CTA
                               FROM OUTRECCONT GP, OUTRECCONTCTBL CGP, LANCCONT LC, EMPRESA E
                              WHERE GP.CDEMPRESA    = P_CDEMPRESA
                                AND GP.CDEMPRESA    = CGP.CDEMPRESA
                                AND GP.CDOUTRECCONT =  CGP.CDOUTRECCONT
                                AND LC.CDCONTCTBL LIKE CGP.CDCONTCTBL||'%'
                                AND LC.IDLANCINTE   NOT IN ('F','S')
                                AND LC.DTLANCCONT BETWEEN P_DTINICIAL AND P_DTFINAL
                                AND LC.CDEMPRESA    = P_CDEMPRESA
                                AND LC.CDEMPRESA    = E.CDEMPRESA
                                AND LC.VRLANCAMENT  > 0
                              GROUP BY GP.CDSITUTRIBPICO,  CGP.CDCONTCTBL, E.NRINSJUREMPR
                              UNION ALL
                             SELECT NRINSJURIEST AS CNPJ,
                                    '99' AS COD_MOD,
                                    '' AS SER,
                                    '' AS SUB_SER,
                                    '99' AS COD_SIT,
                                    SUM(VRRECEITA) AS VL_TOT_REC,
                                    0 AS QUANT_DOC,
                                    CSTPIS,
                                    CSTCOFINS,
                                    CFOP,
                                    INFO_COMPL,
                                    COD_CTA
                               FROM(SELECT IT.VRPRODUTO  AS VRRECEITA,
                                           CDSITUTRIBNFE AS CSTPIS,
                                           CDSITUTRIBNFE AS CSTCOFINS,
                                           '' AS CFOP,
                                           CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,RZ.CDFILIAL) AS COD_CTA,
                                           '' AS INFO_COMPL,
                                           IE.NRINSJURIEST
                                      FROM ITEMVENOUTIMPOS IT, REDUCAOZ RZ, FILIAL F, IMPOSTO I, INSCRESTAD IE, PARAVEND P
                                     WHERE IT.CDFILIAL    = RZ.CDFILIAL
                                       AND IT.CDCAIXA     = RZ.CDCAIXA
                                       AND IT.DTREDUCAOZ  = RZ.DTREDUCAOZ
                                       AND RZ.CDFILIAL    = P.CDFILIAL
                                       AND IT.CDIMPOSTO   = I.CDIMPOSTO
                                       AND F.CDFILIAL     = RZ.CDFILIAL
                                       AND F.CDFILIAL     = IT.CDFILIAL
                                       AND F.CDEMPRESA    = P_CDEMPRESA
                                       AND F.CDEMPRESA    = IE.CDEMPRESA
                                       AND F.CDINSCESTA   = IE.CDINSCESTA
                                       AND I.CDIMPOSTONFE = P_PIS
                                       AND IT.DTREDUCAOZ BETWEEN P_DTINICIAL AND P_DTFINAL)
                              GROUP BY NRINSJURIEST, CSTPIS, CSTCOFINS, CFOP, INFO_COMPL, COD_CTA
                              UNION ALL
                              SELECT E.NRINSJUREMPR AS CNPJ,
                                    '59' AS COD_MOD,
                                    V.CDSERIENFCE AS SER, '' AS SUB_SER,
                                    CASE WHEN V.IDSTATUSNFCE = 'C' THEN '02' WHEN V.IDSTATUSNFCE IN ('A','E') THEN '00' ELSE '99' END AS COD_SIT,
                                    SUM(TRUNC((IT.QTPRODVEND*(NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS VL_TOT_REC,
                                    COUNT(DISTINCT V.NRSEQVENDA) AS QUANT_DOC,
                                    I.CDCSTPRODPC AS CST_PIS,
                                    I.CDCSTPRODPC AS CST_COFINS,
                                    I.CDCFOPPROD AS CFOP,
                                    '' AS INF_COMPL,
                                    CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,IT.CDFILIAL) AS COD_CTA
                               FROM TMP_REGDADOSSPED_PIS T, VENDA V, EMPRESA E, ITVENDAIMPOS I, ITEMVENDA IT, PARAVEND P
                              WHERE T.CDREGISTRO   = 'SAT'
                                AND T.CDCAMPO1     = V.CDFILIAL
                                AND T.CDCAMPO2     = V.NRSEQVENDA
                                AND T.CDCAMPO3     = V.CDCAIXA
                                AND V.CDFILIAL     = P.CDFILIAL
                                AND E.CDEMPRESA    = P_CDEMPRESA
                                AND V.CDFILIAL     = I.CDFILIAL
                                AND V.NRSEQVENDA   = I.NRSEQVENDA
                                AND V.CDCAIXA      = I.CDCAIXA
                                AND I.CDFILIAL     = IT.CDFILIAL
                                AND I.NRSEQVENDA   = IT.NRSEQVENDA
                                AND I.CDCAIXA      = IT.CDCAIXA
                                AND I.NRSEQUITVEND = IT.NRSEQUITVEND
                              GROUP BY E.NRINSJUREMPR, V.CDSERIENFCE, I.CDCSTPRODPC, I.CDCSTPRODPC, I.CDCFOPPROD,
                                    CASE WHEN V.IDSTATUSNFCE = 'C' THEN '02' WHEN V.IDSTATUSNFCE IN ('A','E') THEN '00' ELSE '99' END,
                                    CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,IT.CDFILIAL)
                              UNION ALL
                             SELECT E.NRINSJUREMPR AS CNPJ,
                                    '65' AS COD_MOD,
                                    V.CDSERIENFCE AS SER, '' AS SUB_SER,
                                    CASE WHEN V.IDSTATUSNFCE = 'C' THEN '02' WHEN V.IDSTATUSNFCE IN ('A','E') THEN '00' ELSE '99' END AS COD_SIT,
                                    SUM(TRUNC((IT.QTPRODVEND*(NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS VL_TOT_REC,
                                    COUNT(DISTINCT V.NRSEQVENDA) AS QUANT_DOC,
                                    I.CDCSTPRODPC AS CST_PIS,
                                    I.CDCSTPRODPC AS CST_COFINS,
                                    I.CDCFOPPROD AS CFOP,
                                    '' AS INF_COMPL,
                                    CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,IT.CDFILIAL) AS COD_CTA
                               FROM TMP_REGDADOSSPED_PIS T, VENDA V, EMPRESA E, ITVENDAIMPOS I, ITEMVENDA IT, PARAVEND P
                              WHERE T.CDREGISTRO   = 'NFCE'
                                AND T.CDCAMPO1     = V.CDFILIAL
                                AND T.CDCAMPO2     = V.NRSEQVENDA
                                AND T.CDCAMPO3     = V.CDCAIXA
                                AND V.CDFILIAL     = P.CDFILIAL
                                AND E.CDEMPRESA    = P_CDEMPRESA
                                AND V.CDFILIAL     = I.CDFILIAL
                                AND V.NRSEQVENDA   = I.NRSEQVENDA
                                AND V.CDCAIXA      = I.CDCAIXA
                                AND I.CDFILIAL     = IT.CDFILIAL
                                AND I.NRSEQVENDA   = IT.NRSEQVENDA
                                AND I.CDCAIXA      = IT.CDCAIXA
                                AND I.NRSEQUITVEND = IT.NRSEQUITVEND
                              GROUP BY E.NRINSJUREMPR, V.CDSERIENFCE, I.CDCSTPRODPC, I.CDCSTPRODPC, I.CDCFOPPROD,
                                    CASE WHEN V.IDSTATUSNFCE = 'C' THEN '02' WHEN V.IDSTATUSNFCE IN ('A','E') THEN '00' ELSE '99' END,
                                    CONTCTBL_SPED('S',IT.CDPRODUTO,P.CDTPOPERCNTREC,IT.CDFILIAL))
                      GROUP BY CNPJ, COD_MOD, SER, SUB_SER, COD_SIT, CST_PIS, CST_COFINS, CFOP, INF_COMPL, COD_CTA, TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CNPJ,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),''))) LOOP

         V_CONTREG1:= NVL(V_CONTREG1,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'802'||V_CONTREG1,C_1900.REGISTRO);

         V_OLDESTAB := NULL;
         /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
         SELECT MAX(CDESTAB)
           INTO V_OLDESTAB
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDREGISTRO = '0140'
            AND CDCHAVEREG = C_1900.CNPJ;

         V_REG_0140 := '';
         IF V_OLDESTAB IS NULL THEN
            V_CONTEST := NVL(V_CONTEST,0)+1;
            BEGIN
               SELECT REG
                 INTO V_REG_0140
                 FROM (SELECT '|0140'         ||'|'||
                              LTRIM(TO_CHAR(V_CONTEST,'000')) ||'|'||
                              E.NMRAZSOCEMPR  ||'|'||
                              LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0') ||'|'||
                              E.SGESTADO      ||'|'||
                              ''              ||'|'||
                              M.CDMUNICIBGE   ||'|'||
                              ''              ||'|'||
                              ''              ||'|' AS REG,
                              LPAD(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\',''),' ',''),CHR(10),''),CHR(9),'')),14,'0') AS INSJUREST
                         FROM MUNICIPIO M, EMPRESA E
                        WHERE E.CDEMPRESA   = P_CDEMPRESA
                          AND E.CDPAIS      = M.CDPAIS
                          AND E.SGESTADO    = M.SGESTADO
                          AND E.CDMUNICIPIO = M.CDMUNICIPIO);
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_REG_0140:= NULL;
            END;

            V_TOTBLC0 := NVL(V_TOTBLC0,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'104'||LTRIM(TO_CHAR(V_CONTEST,'000')),V_REG_0140);

            V_SEQESTAB:= NVL(V_SEQESTAB,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
             VALUES
               ('0140', V_SEQESTAB, C_1900.CNPJ ,NULL,LTRIM(TO_CHAR(V_CONTEST,'000')));
         ELSE
            V_CONTEST:= TO_NUMBER(V_OLDESTAB);
         END IF;

         /* CODIGO DA CONTA CONTÁBIL */
         BEGIN
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_CC
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDCHAVEREG = C_1900.COD_CTA
               AND CDREGISTRO = '0500';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COD_CC := NULL;
         END;

         IF (V_COD_CC IS NULL) AND (C_1900.COD_CTA IS NOT NULL) THEN
            V_SEQCCTBL := NVL(V_SEQCCTBL,0) + 1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1)
            VALUES
               ('0500', V_SEQCCTBL, C_1900.COD_CTA, NULL);
         END IF;
      END LOOP;
   END IF;

   /*INSERE O REGISTRO 1001*/
   IF V_CONTREG1 > 0 THEN
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'801','|1001|0|');
   ELSE
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'801','|1001|1|');
   END IF;
   V_CONTREG1:= NVL(V_CONTREG1,0)+1;

   /*INSERE O BLOCO 1990 - ENCERRAMENTO*/
   V_CONTREG1:= NVL(V_CONTREG1,0)+1;
   INSERT INTO ARQMAGPISCOFINS
      (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
   VALUES
      (P_NRSEQARQMAG,'803'||V_CONTREG1,'|1990|'||V_CONTREG1||'|');

   V_TOTLINHAS:= NVL(SPED_PIS_COFINS_BLOCO9(NVL(V_TOTBLCA,0),NVL(V_TOTBLCC,0),NVL(V_TOTBLCD,0),NVL(V_TOTBLC0,0),P_GERABCLP,P_LIVROMAG,P_NRSEQARQMAG,P_DTINICIAL,P_DTFINAL),0);
   RETURN(V_TOTLINHAS);
END; 