CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCOC(
   P_NRLANCTONF       IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_CDFILIAL         IN NOTAFISCAL.CDFILIAL%TYPE,
   P_CDCAIXA          IN VENDA.CDCAIXA%TYPE,
   P_NRSEQARQMAG      IN ARQMAGPISCOFINS.NRSEQARQMAG%TYPE,
   P_NRINSJUREST      IN INSCRESTAD.NRINSJURIEST%TYPE,
   P_IDNOVOEST        IN NOTAFISCAL.IDSTATUS%TYPE,
   P_IDENTRSAIDOP     IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDSTATUS         IN NOTAFISCAL.IDSTATUS%TYPE,
   P_IDORIGDESTNF     IN NOTAFISCAL.IDORIGDESTNF%TYPE,
   P_CDFILIEMIT       IN NOTAFISCAL.CDFILIEMIT%TYPE,
   P_CDFILIRECE       IN NOTAFISCAL.CDFILIRECE%TYPE,       
   P_CDCLIENTE        IN NOTAFISCAL.CDCLIENTE%TYPE,
   P_CDFORNECED       IN NOTAFISCAL.CDFORNECED%TYPE,
   P_CDSERIE          IN NOTAFISCAL.CDSERIE%TYPE,
   P_NRNOTAFISC       IN NOTAFISCAL.NRNOTAFISC%TYPE,
   P_DTEMISSAO        IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTENTRSAID       IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_VRNOTAFISC       IN NOTAFISCAL.VRNOTAFISC%TYPE,
   P_VRCONTABIL       IN IMPOSTONF.VRCONTABILNF%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_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_CONTEST          IN NUMBER,
   P_CDPAIS           IN PAIS.CDPAIS%TYPE,
   P_CDEMPRESA        IN EMPRESA.CDEMPRESA%TYPE,
   P_CONTREGA         IN DOUBLE PRECISION,
   P_IDPGTOFRETE      IN NOTAFISCAL.IDPGTOFRETE%TYPE,
   P_NRACESSONFE      IN NOTAFISCAL.NRACESSONFE%TYPE,
   P_CDMODDOCFISC     IN NOTAFISCAL.CDMODDOCFISC%TYPE,
   P_VRPRODUTOS       IN NOTAFISCAL.VRPRODUTOS%TYPE,
   P_ICMS_ST          IN VARCHAR2,
   P_IPI              IN VARCHAR2,
   P_ICMS             IN VARCHAR2,
   P_DTINICIAL        IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL          IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_VRDESCONTO       IN FLOAT,
   P_CDTIPOOPER       IN NOTAFISCAL.CDTIPOOPER%TYPE,
   P_IDSTATUSNFE      IN NOTAFISCAL.IDSTATUSNFE%TYPE,
   P_VRFRETE          IN FLOAT,
   P_VROUTROS         IN FLOAT,
   P_VRSEGUR          IN FLOAT,
   P_TIPOOPER_COMPL_S IN TIPOOPERAC.CDTIPOOPER%TYPE,
   P_TIPOOPER_COMPL_E IN TIPOOPERAC.CDTIPOOPER%TYPE,
   P_SEQUPROD         IN IMPOSTONF.VRIMPOSTONF%TYPE,
   P_TMPBLCM          IN IMPOSTONF.VRIMPOSTONF%TYPE,
   P_NRNOTAFISCALELE  IN NOTAFISCAL.NRNOTAFISCALELE%TYPE,
   P_LIVROMAG         IN RELACPRODITGEN.CDLIVROMAG%TYPE,
   P_GERADEVOLVEND    IN VARCHAR2,
   P_VERSAO           IN VARCHAR2) RETURN VARCHAR
AS
   V_REG_C010         VARCHAR2(1000);
   V_REG              VARCHAR2(1000);
   V_COD_PAR          VARCHAR(100);
   V_COD_NAT          VARCHAR2(100);
   V_UNID             VARCHAR2(100);
   V_PRODUTO          VARCHAR2(100);
   V_CSTPIS           VARCHAR2(2);
   V_CSTCOFINS        VARCHAR2(2);
   V_CDNATBCCOFINS    VARCHAR2(2);
   V_CDNATBCPIS       VARCHAR2(2);
   V_CDTPCREDPIS      VARCHAR2(3);
   V_CDTPCREDCOFINS   VARCHAR2(3);
   V_CDFISCALPIS      VARCHAR2(1);
   V_CDFISCALCOFINS   VARCHAR2(1);
   V_TIPOOPER_COMPL_S VARCHAR2(2);
   V_TIPOOPER_COMPL_E VARCHAR2(2);
   V_IDC010           VARCHAR2(20);
   V_COD_CC           VARCHAR2(20);
   V_SEQCCTBL         NUMBER;
   V_SEQUPROD         NUMBER;
   V_TMPBLCM          NUMBER;
   V_SEQITPROD        NUMBER;
   V_CONTREGA         NUMBER;
   V_COUNTPARC        NUMBER;
   V_VRBCPIS          NUMBER;
   V_VRBCCOFINS       NUMBER;
   V_VRCREDPIS        NUMBER;
   V_VRCREDCOFINS     NUMBER;
   V_VRALIQPIS        NUMBER;
   V_VRALIQCOFINS     NUMBER;
   V_VRPISRET         NUMBER;
   V_VRCOFINSRET      NUMBER;
   V_IDATIVO          VARCHAR2(1);
   V_IDGERANOTA       VARCHAR2(1);
   V_VRPRODATIV       NUMBER;
   V_VRBCICMSATIV     NUMBER;
   V_VRICMSATIV       NUMBER;
   V_VRBCSTATIV       NUMBER;
   V_VRSTATIV         NUMBER;
   V_VRPISATIV        NUMBER;
   V_VRCOFINSATIV     NUMBER;
   V_SEQNOTAS         NUMBER;
   V_INSCRICAO        VARCHAR2(14);
   V_VRPIS            NUMBER;
   V_VRCOFINS         NUMBER;
   V_REG_C120         VARCHAR2(1000);
   V_NRDOCIMP         VARCHAR2(10);
   V_NRDRAWBACK       VARCHAR2(20);
   V_IDTPALIQ         VARCHAR2(01);
   V_ENERGIA          VARCHAR2(01);
BEGIN
   V_CONTREGA     := P_CONTREGA;
   V_SEQUPROD     := P_SEQUPROD;
   V_TMPBLCM      := P_TMPBLCM;
   V_IDATIVO      := 'S';
   V_VRPRODATIV   := 0;
   V_VRBCICMSATIV := 0;
   V_VRICMSATIV   := 0;
   V_VRBCSTATIV   := 0;
   V_VRSTATIV     := 0;
   V_VRPISATIV    := 0;
   V_VRCOFINSATIV := 0;
   V_SEQNOTAS     := 0;
   V_IDGERANOTA   := 'S';
   V_ENERGIA      := 'N';

   /*SELECIONA O INDICADOR DE PAGAMENTO DAS NOTAS(a prazo ou a vista)*/
   BEGIN
     SELECT SUM(PARC)
       INTO V_COUNTPARC
       FROM (SELECT COUNT(*) AS  PARC
               FROM CONTARECEBER
              WHERE CDFILIAL   = P_CDFILIAL
                AND NRLANCTONF = P_NRLANCTONF
              UNION ALL
             SELECT COUNT(*) AS PARC
               FROM CONTAPAGAR
              WHERE CDFILIAL   = P_CDFILIAL
                AND NRLANCTONF = P_NRLANCTONF);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_COUNTPARC := 0;
   END;

   IF P_IDENTRSAIDOP = 'E' THEN
      BEGIN
      /*VERIFICA SE A NOTA POSSUI CREDITO DE AQUISICAO PARA ATIVO IMOBILIZADO. CASO SIM, GERA C100 SOMENTE SE EXISTIR ITEM QUE NAO SEJA DO ATIVO.
      OS VALORES REFERENTES AS AQUISICOES PARA O ATIVO SAO GERADOS NO F130*/
         SELECT NVL(SUM(DECODE(IM.CDIMPOSTONFE,P_PIS,IT.VRCONTITIMP,0)),0),
                NVL(SUM(DECODE(IM.CDIMPOSTONFE,P_ICMS,IT.VRBASECALCIT,0)),0), NVL(SUM(DECODE(IM.CDIMPOSTONFE,P_ICMS,IT.VRIMPOSTOIT,0)),0),
                NVL(SUM(DECODE(IM.CDIMPOSTONFE,P_ICMS_ST,IT.VRBASECALCIT,0)),0), NVL(SUM(DECODE(IM.CDIMPOSTONFE,P_ICMS_ST,IT.VRIMPOSTOIT,0)),0),
                NVL(SUM(DECODE(IM.CDIMPOSTONFE,P_PIS,IT.VRIMPOSTOIT,0)),0), NVL(SUM(DECODE(IM.CDIMPOSTONFE,P_COFINS,IT.VRIMPOSTOIT,0)),0)
           INTO V_VRPRODATIV, V_VRBCICMSATIV, V_VRICMSATIV, V_VRBCSTATIV, V_VRSTATIV, V_VRPISATIV, V_VRCOFINSATIV
           FROM ITEMPRODUTO IP, ITEMPROIMPOS IT, IMPOSTO IM
          WHERE IT.CDFILIAL    = P_CDFILIAL
            AND IT.NRLANCTONF  = P_NRLANCTONF
            AND SUBSTR(IT.CDFISCOPERIT,2,3) IN ('406','551','552','553','554','555','604')
            AND IT.CDIMPOSTO   = IM.CDIMPOSTO
            AND IT.CDFISCALIT  = '1'
            AND DECODE(IM.CDIMPOSTONFE,P_PIS,IT.VRIMPOSTOIT,0) > 0
            AND IT.CDFILIAL    = IP.CDFILIAL
            AND IT.NRLANCTONF  = IP.NRLANCTONF
            AND IT.NRSEQITPROD = IP.NRSEQITPROD;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRPRODATIV   :=0;
            V_VRBCICMSATIV :=0;
            V_VRICMSATIV   :=0;
            V_VRBCSTATIV   :=0;
            V_VRSTATIV     :=0;
            V_VRPISATIV    :=0;
            V_VRCOFINSATIV :=0;
      END;
   END IF;

   IF ((V_IDATIVO = 'S') AND (V_VRPISATIV+V_VRCOFINSATIV > 0)) THEN
      BEGIN
      /*VERIFICA SE A NOTA POSSUI OUTRO CFOP ALEM DO ITEM DE AQUISICAO PARA ATIVO IMOBILIZADO PARA GERAR O C100.*/
         SELECT DISTINCT 'S'
           INTO V_IDGERANOTA
           FROM IMPOSTONF I, IMPOSTO P
          WHERE I.CDFILIAL     = P_CDFILIAL
            AND I.NRLANCTONF   = P_NRLANCTONF
            AND P_IDENTRSAIDOP = 'E'
            AND SUBSTR(I.CDFISCOPERNF,2,3) NOT IN ('406','551','552','553','554','555','604')
            AND I.CDIMPOSTO    = P.CDIMPOSTO
            AND P.CDIMPOSTONFE IN (P_PIS,P_COFINS);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_IDGERANOTA := 'N';
      END;

      SELECT MAX(CDINSCESTA)
        INTO V_INSCRICAO
        FROM NOTAFISCAL
       WHERE CDFILIAL   = P_CDFILIAL
         AND NRLANCTONF = P_NRLANCTONF;

      /*INSERE AS NOTAS QUE POSSUEM CREDITO DE AQUISICOES DE ATIVO PARA GERAR O REGISTRO F130 NO BLOCO F*/
      V_SEQNOTAS := NVL(V_SEQNOTAS,0) + 1;
      INSERT INTO TMP_REGDADOSSPED_PIS
         (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2, CDCAMPO7, CDESTAB, CDCAMPO3, CDCAMPO4)
       VALUES
         ('F130', V_SEQNOTAS, P_CDFILIAL||P_NRLANCTONF, P_CDFILIAL, P_NRLANCTONF, V_INSCRICAO, P_CONTEST, TO_CHAR(P_DTENTRSAID,'MMYYYY'), P_CDFILIRECE);
   ELSE
      V_IDATIVO := 'N';
   END IF;

   /*VERIFICA SE A NOTA FOR MODELO 55 E POSSUI CFOP DE ENERGIA ELETRICA, NESSE CASO SERA ESCRITURADA NO REGISTRO C500*/
   IF P_IDENTRSAIDOP = 'E' AND P_CDMODDOCFISC = '55' THEN
      BEGIN
         SELECT DISTINCT 'S'
           INTO V_ENERGIA
           FROM IMPOSTONF I, IMPOSTO P
          WHERE I.CDFILIAL     = P_CDFILIAL
            AND I.NRLANCTONF   = P_NRLANCTONF
            AND SUBSTR(I.CDFISCOPERNF,2,3) IN ('251','252','253','254','255','256','257')
            AND I.CDIMPOSTO    = P.CDIMPOSTO
            AND P.CDIMPOSTONFE IN (P_PIS,P_COFINS);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_ENERGIA := 'N';
      END;
   END IF;

   IF V_ENERGIA = 'S' THEN
      V_IDGERANOTA := 'N';
   END IF;

   IF P_CDMODDOCFISC IN ('55','65','01','1B','04') AND V_IDGERANOTA = 'S' THEN
      BEGIN
         SELECT SUM(I.VRIMPPIS)    AS VRPIS,
                SUM(I.VRIMPCOFINS) AS VRCOFINS
           INTO V_VRPIS, V_VRCOFINS
           FROM ITVENDAIMPOS I
          WHERE I.CDFILIAL     = P_CDFILIAL
            AND I.CDCAIXA      = P_CDCAIXA
            AND I.NRSEQVENDA   = P_NRLANCTONF
            AND (I.VRPERPIS IS NOT NULL OR I.VRPERCOFINS IS NOT NULL);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_VRPIS    := 0;
            V_VRCOFINS := 0;
      END;
      /*GERA O REGISTRO C100*/
      FOR REG_C100 IN(SELECT ('|C100'                            ||'|'||
                              DECODE(P_IDENTRSAIDOP,'E','0','1') ||'|'||
                              DECODE(P_IDENTRSAIDOP,'E',DECODE(P_IDSTATUSNFE,'A','0',DECODE(P_IDSTATUS,'E','0','1')), '0')||'|'||/*INDICADOR DE EMISSAO*/
                              DECODE(P_CDMODDOCFISC,'65','',P_IDORIGDESTNF||DECODE(P_IDORIGDESTNF,'L',DECODE(P_IDENTRSAIDOP,'E',P_CDFILIEMIT,P_CDFILIRECE),NVL(P_CDCLIENTE,P_CDFORNECED)))||'|'||
                              P_CDMODDOCFISC                     ||'|'||
                              DECODE(P_IDSTATUS,'C','02',
                                     DECODE(P_IDENTRSAIDOP,'S', (CASE WHEN P_DTEMISSAO < P_DTINICIAL
                                                                      THEN DECODE(P_CDTIPOOPER,'03','07',V_TIPOOPER_COMPL_S,'07','01')
                                                                      ELSE DECODE(P_CDTIPOOPER,'03','06',V_TIPOOPER_COMPL_S,'06','00') END),
                                                                (CASE WHEN ((P_CDSERIE BETWEEN '890' AND '899') AND (P_CDMODDOCFISC = '55'))
                                                                      THEN '08'
                                                                      ELSE (CASE WHEN P_DTENTRSAID < P_DTINICIAL
                                                                                 THEN DECODE(P_CDTIPOOPER,'03','07',V_TIPOOPER_COMPL_E,'07','01')
                                                                                 ELSE DECODE(P_CDTIPOOPER,'03','06',V_TIPOOPER_COMPL_E,'06','00') END) END))) ||'|'||
                              P_CDSERIE                          ||'|'||
                              DECODE(P_IDSTATUSNFE, 'A', LTRIM(P_NRNOTAFISCALELE, '0'),LTRIM(REPLACE(LTRIM(P_NRNOTAFISC, '0'),' ')))  || '|' ||/*NUMERO DA NOTA FISCAL*/
                              DECODE(P_IDSTATUS,'C','',DECODE(P_CDMODDOCFISC,'65',P_NRACESSONFE,CASE WHEN P_CDMODDOCFISC = '55' AND P_IDSTATUSNFE = 'A' THEN P_NRACESSONFE
                                      WHEN P_CDMODDOCFISC = '55' AND P_IDSTATUSNFE <> 'A' AND P_DTINICIAL >= '01/04/2012' THEN P_NRACESSONFE ELSE '' END))  || '|' ||  --chave da nota fiscal eletronica
                              TO_CHAR(P_DTEMISSAO,'ddmmyyyy')    ||'|'||
                              CASE WHEN P_DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL THEN TO_CHAR(P_DTENTRSAID,'ddmmyyyy') ELSE TO_CHAR(P_DTFINAL,'ddmmyyyy') END ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(P_VRNOTAFISC,2),'9999999999990.00')),'.',',') ||'|'||
                              DECODE(NVL(V_COUNTPARC,0),0,'0','1')        ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(NVL(P_VRDESCONTO,0),2),'9999999999990.00')),'.',',') ||'|'||
                              ''                                          ||'|'|| /*CAMPO 15*/
                              REPLACE(LTRIM(TO_CHAR(ROUND(P_VRCONTABIL-NVL(V_VRPRODATIV,0),2),'9999999999990.00')),'.',',') ||'|'||
                              DECODE(P_IDSTATUS, 'C', '', NVL(P_IDPGTOFRETE,1) )                                                         ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(P_IDSTATUS, 'C', '', NVL(P_VRFRETE,0)) ,2),'999999999990.00')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(P_IDSTATUS, 'C', '', NVL(P_VRSEGUR,0)) ,2),'999999999990.00')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(P_IDSTATUS, 'C', '', NVL(P_VROUTROS,0)),2),'999999999990.00')),'.',',') ||'|'|| --campo20
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'B','1')-V_VRBCICMSATIV,2),'9999999999990.00')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'I','1'),2)-V_VRICMSATIV,'9999999999990.00')),'.',',') ||'|'||
                              DECODE(P_IDSTATUS,'C','',DECODE(P_CDMODDOCFISC,'65','',REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL, P_NRLANCTONF,P_ICMS_ST, 'B','1')-V_VRBCSTATIV,2),'9999999999990.00')),'.',',')))||'|'||/*BASE DE CALCULO ICMS ST*/
                              DECODE(P_IDSTATUS,'C','',DECODE(P_CDMODDOCFISC,'65','',REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL, P_NRLANCTONF,P_ICMS_ST, 'I','1')-V_VRSTATIV,  2),'9999999999990.00')),'.',',')))||'|'||/*IMPOSTO ICMS ST*/
                              DECODE(P_IDSTATUS,'C','',DECODE(P_CDMODDOCFISC,'65','',REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL, P_NRLANCTONF,P_IPI,     'I','1'),2),             '9999999999990.00')),'.',',')))||'|'||/*VALOR IPI*/
                              REPLACE(LTRIM(TO_CHAR(DECODE(P_CDMODDOCFISC,'65',V_VRPIS,   ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_PIS,   'I','1')-V_VRPISATIV,   2)),    '9999999999990.00')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(DECODE(P_CDMODDOCFISC,'65',V_VRCOFINS,ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_COFINS,'I','1')-V_VRCOFINSATIV,2)),    '9999999999990.00')),'.',',') ||'|'||
                              ''                                         ||'|'||
                              ''                                         ||'|') AS REG0100,
                              P_IDORIGDESTNF||DECODE(P_IDORIGDESTNF,'L',DECODE(P_IDENTRSAIDOP,'E',P_CDFILIEMIT,P_CDFILIRECE),NVL(P_CDCLIENTE,P_CDFORNECED)) AS CD_PARTC
                         FROM DUAL)LOOP

         V_CONTREGA := NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_C100.REG0100);

         V_COD_PAR:= NULL;

         /* CODIGO DO PARTICIPANTE */
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDCHAVEREG = REG_C100.CD_PARTC
            AND CDREGISTRO = '0150'
            AND CDESTAB    = LTRIM(TO_CHAR(P_CONTEST,'000'));

         IF (V_COD_PAR IS NULL) AND (REG_C100.CD_PARTC IS NOT NULL) THEN
            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
              ('0150', V_SEQUPROD, REG_C100.CD_PARTC, NULL, LTRIM(TO_CHAR(P_CONTEST,'000')));
         END IF;
      END LOOP;

      IF (P_IDENTRSAIDOP = 'E')  THEN
         BEGIN
            SELECT MAX(NRDOCIMPNFE), MAX(A.NRDRAWBACK)
              INTO V_NRDOCIMP, V_NRDRAWBACK
              FROM DECIMPNFE D, ADICDECIMPNFE A
             WHERE D.CDFILIAL   = P_CDFILIAL
               AND D.NRLANCTONF = P_NRLANCTONF
               AND D.CDFILIAL   = A.CDFILIAL
               AND D.NRLANCTONF = A.NRLANCTONF;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NRDOCIMP := NULL;
               V_NRDRAWBACK := NULL;
         END;

         /*INSERE O REGISTRO C120 PARA NOTAS FISCAIS DE IMPORTACAO*/
         IF V_NRDOCIMP IS NOT NULL THEN
            V_REG_C120 := '|C120|'   ||
                          '0'        ||'|'||
                          V_NRDOCIMP ||'|'||
                          REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_PIS,'I','1'),2),'9999999999990.00')),'.',',')    ||'|'||
                          REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_COFINS,'I','1'),2),'9999999999990.00')),'.',',') ||'|'||
                          V_NRDRAWBACK ||'|';
            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REG_C120);
         END IF;
      END IF;

      IF P_CDMODDOCFISC = '65' THEN /*Para NFC-e*/
         /*REGISTRO C175*/
         FOR REG_C175 IN (SELECT '|C175'                                                        ||'|'||
                                 CDCFOPPROD                                                     ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(VROPERACAO, '999999999990.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(VEDESCONTO, '999999999990.00')),'.',',') ||'|'||
                                 CDCSTPRODPC                                                    ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(VRBASECALC, '999999999990.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(VRPERPIS,   '999999999990.00')),'.',',') ||'|'||
                                 ''                                                             ||'|'||
                                 ''                                                             ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(TRUNC(VRPIS,2),      '999999999990.00')),'.',',') ||'|'||
                                 CDCSTPRODPC                                                    ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(VRBASECALC, '999999999990.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(VRPERCOFINS,'999999999990.00')),'.',',') ||'|'||
                                 ''                                                             ||'|'||
                                 ''                                                             ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(TRUNC(VRCOFINS,2),   '999999999990.00')),'.',',') ||'|'||
                                 CONTCTBL_SPED(P_IDENTRSAIDOP,'',P_CDTIPOOPER,P_CDFILIAL)       ||'|'||
                                 ''                                                             ||'|' AS REG0175,
                                 VROPERACAO, VRBASECALC, VRPERPIS, VRPIS, VRPERCOFINS, VRCOFINS, CDCFOPPROD, CDCSTPRODPC,
                                 CONTCTBL_SPED(P_IDENTRSAIDOP,'',P_CDTIPOOPER,P_CDFILIAL) AS COD_CTA
                            FROM (SELECT SUM(TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VROPERACAO,
                                         SUM(NVL(T.VRDESITVEND,0)) AS VEDESCONTO, I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS,
                                         SUM(DECODE(I.VRPERPIS,0,0,TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0))),2)-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0))) AS VRBASECALC,
                                         SUM(I.VRIMPPIS)    AS VRPIS,
                                         SUM(I.VRIMPCOFINS) AS VRCOFINS
                                    FROM ITEMVENDA T, ITVENDAIMPOS I
                                   WHERE T.CDFILIAL     = P_CDFILIAL
                                     AND T.NRSEQVENDA   = P_NRLANCTONF
                                     AND T.CDCAIXA      = P_CDCAIXA
                                     AND T.CDFILIAL     = I.CDFILIAL
                                     AND T.CDCAIXA      = I.CDCAIXA
                                     AND T.NRSEQUITVEND = I.NRSEQUITVEND
                                     AND T.NRSEQVENDA   = I.NRSEQVENDA
                                     AND (I.VRPERPIS IS NOT NULL OR I.VRPERCOFINS IS NOT NULL)
                                   GROUP BY I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS))LOOP

            /*RETIRADO PARA MELHORIA NA PERFORMANCE, CAMPOS NAO SAO NECESSARIOS PARA SAIDA
            BEGIN
               SELECT MAX(DECODE(CDIMPOSTONFE,'50',CDTPCRPICOSTNFE,'')) AS CDTPCREDPIS,
                      MAX(DECODE(CDIMPOSTONFE,'60',CDTPCRPICOSTNFE,'')) AS CDTPCREDCOFINS,
                      MAX(DECODE(CDIMPOSTONFE,'50',CDNATUBCPCSTNFE,'')) AS CDNATBCPIS,
                      MAX(DECODE(CDIMPOSTONFE,'60',CDNATUBCPCSTNFE,'')) AS CDNATBCCOFINS
                 INTO V_CDTPCREDPIS, V_CDTPCREDCOFINS, V_CDNATBCPIS, V_CDNATBCCOFINS
                 FROM VENDA VE, ITEMVENOUTIMPOS IP, ITEMVENDA IT, IMPOSTO IM
                WHERE VE.CDFILIAL     = P_CDFILIAL
                  AND VE.NRSEQVENDA   = P_NRLANCTONF
                  AND VE.CDCAIXA      = P_CDCAIXA
                  AND VE.CDFILIAL     = IT.CDFILIAL
                  AND VE.NRSEQVENDA   = IT.NRSEQVENDA
                  AND VE.CDCAIXA      = IT.CDCAIXA
                  AND VE.CDFILIAL     = IP.CDFILIAL
                  AND VE.CDCAIXA      = IP.CDCAIXA
                  AND TRUNC(VE.DTEMISSAONFCE) = IP.DTREDUCAOZ
                  AND IT.CDPRODUTO    = IP.CDPRODUTO
                  AND IP.CDIMPOSTO    = IM.CDIMPOSTO;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  CDNATBCCOFINS  := NULL;
                  CDNATBCPIS     := NULL;
                  CDTPCREDPIS    := NULL;
                  CDTPCREDCOFINS := NULL;
            END;*/

            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO
               ARQMAGPISCOFINS(NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_C175.REG0175);

            V_COD_NAT := NULL;

            /* CODIGO DO CFOP*/
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_NAT
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDCHAVEREG = REG_C175.CDCFOPPROD
               AND CDREGISTRO = '0400';

            IF (V_COD_NAT IS NULL) AND (REG_C175.CDCFOPPROD IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                 (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1,CDESTAB,CDCAMPO2)
               VALUES
                 ('0400', V_SEQUPROD, REG_C175.CDCFOPPROD,NULL,LTRIM(TO_CHAR(P_CONTEST,'000')),P_IDENTRSAIDOP);
            END IF;

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

            IF (V_COD_CC IS NULL) AND (REG_C175.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, REG_C175.COD_CTA, NULL);
            END IF;
         END LOOP;
         /*ALIMENTA A TABELA TEMPORARIA PARA GERAR O BLOCO M - FOI NECESSARIO UM SELECT A PARTE PARA GRAVAR AS INFORMACOES
           DE NATUREZA DA RECEITA QUE PE POR PRODUTO, ENQUANTO QUE O REGISTRO C175 E POR CST E ALIQUOTA*/
         /*PARA O CST 05 NAO E NECESSARIO O DETALHAMENTO NAS VENDAS ECF, NFCE E SAT, A PARTIR DA VERSAO 2.05 DO VALIDADOR, DE AGOSTO DE 2013*/
         FOR C_BLOCOMNFCE IN (SELECT SUM(TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0)))-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0),2)) AS VROPERACAO,
                                     SUM(DECODE(I.VRPERPIS,0,0,TRUNC((T.QTPRODVEND*(T.VRUNITVEND+NVL(T.VRUNITVENDCL,0))),2)-NVL(T.VRDESITVEND,0)+NVL(T.VRACRITVEND,0))) AS VRBASECALC,
                                     SUM(I.VRIMPPIS)    AS VRPIS,
                                     SUM(I.VRIMPCOFINS) AS VRCOFINS,
                                     I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS, R.CDNATUREC, CONTCTBL_SPED(P_IDENTRSAIDOP,'',P_CDTIPOOPER,P_CDFILIAL) AS COD_CTA
                                FROM ITEMVENDA T, ITVENDAIMPOS I, RELACPRODITGEN R
                               WHERE T.CDFILIAL     = P_CDFILIAL
                                 AND T.NRSEQVENDA   = P_NRLANCTONF
                                 AND T.CDCAIXA      = P_CDCAIXA
                                 AND T.CDFILIAL     = I.CDFILIAL
                                 AND T.CDCAIXA      = I.CDCAIXA
                                 AND T.NRSEQUITVEND = I.NRSEQUITVEND
                                 AND T.NRSEQVENDA   = I.NRSEQVENDA
                                 AND (I.VRPERPIS IS NOT NULL OR I.VRPERCOFINS IS NOT NULL)
                                 AND ((CDCSTPRODPC <> '05') OR (CDCSTPRODPC = '05' AND P_DTEMISSAO < '01/08/2013'))
                                 AND T.CDPRODUTO     = R.CDPRODUTO(+)
                                 AND P_LIVROMAG      = R.CDLIVROMAG(+)
                               GROUP BY I.CDCFOPPROD, I.CDCSTPRODPC, I.VRPERCOFINS, I.VRPERPIS, R.CDNATUREC,
                                     CONTCTBL_SPED(P_IDENTRSAIDOP,'',P_CDTIPOOPER,P_CDFILIAL)) LOOP

            /*INSERE O VALOR REFERENTE AO PIS PARA SAIDAS(DEBITO)*/
            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,CDCONTCTBL)
            VALUES
               ('C175'||P_CDFILIAL||P_NRLANCTONF,'M200',V_TMPBLCM,V_CDTPCREDPIS,C_BLOCOMNFCE.VRBASECALC,C_BLOCOMNFCE.VRPERPIS,'P',C_BLOCOMNFCE.VRPIS,C_BLOCOMNFCE.CDCSTPRODPC,P_CDFILIAL,V_CDNATBCPIS,0,C_BLOCOMNFCE.VROPERACAO,C_BLOCOMNFCE.CDNATUREC,C_BLOCOMNFCE.COD_CTA);

            /*INSERE O VALOR REFERENTE AO COFINS SAIDAS(DEBITO)*/
            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,CDCONTCTBL)
            VALUES
               ('C175'||P_CDFILIAL||P_NRLANCTONF,'M600',V_TMPBLCM,V_CDTPCREDCOFINS,C_BLOCOMNFCE.VRBASECALC,C_BLOCOMNFCE.VRPERCOFINS,'P',C_BLOCOMNFCE.VRCOFINS,C_BLOCOMNFCE.CDCSTPRODPC,P_CDFILIAL,V_CDNATBCCOFINS,0,C_BLOCOMNFCE.VROPERACAO,C_BLOCOMNFCE.CDNATUREC,C_BLOCOMNFCE.COD_CTA);
         END LOOP;
      ELSE
         V_SEQITPROD:=NULL;
         /*REGISTRO C170*/
         FOR REG_C170 IN (SELECT P.CDARVPROD                                                       ||'|'||
                                 ''                                                                ||'|'|| /*DESCRICAO COMPLEMENTAR*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(QTITEMPROD,5),'999999900.00000')),'.',',')     ||'|'||
                                 P.SGUNIDADE                                                       ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(I.VRCONTPIS,'9999999999900.00')),'.',',') ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(IP.VRDESCITEM,'9999999999900.00')),'.',',') ||'|'||
                                 '0'                                                               ||'|'||/*MOVIMENTACAO FISICA*/
                                 IT.CDSITUTRIBIT                                                   ||'|'||
                                 IT.CDFISCOPERIT                                                   ||'|'||
                                 IT.CDFISCOPERIT                                                   ||'|'||
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IT.CDFISCALIT, '1', IT.VRBASECALCIT, 0),2), '999999999990.00')), '.', ',')                                    || '|' ||
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IT.CDFISCALIT, '1', IT.VRALIQITEM, 0),2), '999999999990.00')), '.', ',')                                      || '|' ||
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IT.CDFISCALIT, '1', IT.VRIMPOSTOIT, 0),2), '999999999990.00')), '.', ',')                                     || '|' ||
                                 REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_ICMS_ST,'B','1'),2), '999999999990.00')), '.', ',')|| '|' || --16
                                 REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_ICMS_ST,'A','1'),2), '999999999990.00')), '.', ',')|| '|' || --17
                                 REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_ICMS_ST,'I','1'),2), '999999999990.00')), '.', ',')|| '|' || --18
                                 ''                                                                || '|' || -- indicador de apurcao do IPI CAMPO 19
                                (SELECT CDSITUTRIBITNFE
                                   FROM ITEMPROIMPOS N, IMPOSTO IM
                                  WHERE IP.CDFILIAL    = N.CDFILIAL
                                    AND IP.NRLANCTONF  = N.NRLANCTONF
                                    AND IP.NRSEQITPROD = N.NRSEQITPROD
                                    AND N.NRITPROIMPOS   = '1'
                                    AND N.CDIMPOSTO    = IM.CDIMPOSTO
                                    AND IM.CDIMPOSTONFE   = P_IPI)                                 || '|' ||/*CST DO IPI*/
                                 ''                                                                || '|' || -- COdigo enquadramento IPI campo 21
                                 REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_IPI,'B','1'),2), '999999999990.00')), '.', ',')  || '|' ||/*BC DO IPI*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_IPI,'A','1'),2), '9990.00')), '.', ',')  || '|' ||/*ALI IPI*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_IPI,'I','1'),2), '999999999990.00')), '.', ',') /*VALOR DO IPI*/   AS PARTE1,                                 
								         REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(NVL(I.IDUTVLRQTDEIMPO,'N'),'N',I.VRBASEPIS,''),2),'9999999999990.00')),'.',',') ||'|'||/*BASE CALCULO PIS*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(NVL(I.IDUTVLRQTDEIMPO,'N'),'N',I.VRALIQPIS,''),4),'990.0000')),'.',',') ||'|'||/*ALIQUOTA PIS*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(NVL(I.IDUTVLRQTDEIMPO,'N'),'S',I.VRBASEPIS,''),2),'9999999999990.00')),'.',',')  ||'|'|| /*QUANTIDADE BC PIS*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(NVL(I.IDUTVLRQTDEIMPO,'N'),'S',I.VRALIQPIS,''),4),'990.0000')),'.',',')  ||'|'|| /*ALIQUOTA PIS EM REAIS*/                                 
								         REPLACE(LTRIM(TO_CHAR(ROUND(I.VRIMPPIS,2),'9999999999990.00')),'.',',') /*VR IMPOSTO PIS*/ AS PARTE2,
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(NVL(I.IDUTVLRQTDEIMPO,'N'),'N',I.VRBASECOFINS,''),2),'9999999999990.00')),'.',',') ||'|'||/*BASE CALCULO COFINS*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(NVL(I.IDUTVLRQTDEIMPO,'N'),'N',I.VRALICOFINS,''),4),'990.0000')),'.',',') ||'|'||/*ALIQUOTA COFINS*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(NVL(I.IDUTVLRQTDEIMPO,'N'),'S',I.VRBASECOFINS,''),2),'9999999999990.00')),'.',',') ||'|'|| /*QUANTIDADE BC COFINS*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(NVL(I.IDUTVLRQTDEIMPO,'N'),'S',I.VRALICOFINS,''),4),'990.0000')),'.',',') ||'|'|| /*ALIQOTA EM QUANTIDADE COFINS*/
                                 REPLACE(LTRIM(TO_CHAR(ROUND(I.VRIMPCOFINS,2),'9999999999990.00')),'.',',') ||'|'||/*VR IMPOSTO COFINS*/
                                 CONTCTBL_SPED(P_IDENTRSAIDOP,P.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)) ||'|' /*CODIGO DA CONTA ANALITICA CONTABIL */   AS PARTE3,
                                 P.CDPRODUTO, P.SGUNIDADE,CDFISCOPERIT,DSCDFISOPEIT,CDTPCREDPICOITE,IT.NRSEQITPROD,CDARVPROD, CDNATUREC, IP.VRTOTAITEM, I.VRCONTPIS, I.VRCONTCOFINS,
                                 CONTCTBL_SPED(P_IDENTRSAIDOP,P.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)) AS COD_CTA,
                                 IP.CDPRODNCMTRANS AS CDCLASFISC
                            FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, PRODUTO P, IMPOSTO IM, RELACPRODITGEN R,
                                 (SELECT SUM(DECODE(IM.CDIMPOSTONFE,P_PIS,I.VRCONTITIMP,0)) AS VRCONTPIS,
                                         SUM(DECODE(IM.CDIMPOSTONFE,P_COFINS,I.VRCONTITIMP,0)) AS VRCONTCOFINS,
                                         SUM(DECODE(IM.CDIMPOSTONFE,P_PIS,DECODE(I.CDFISCALIT,'1',I.VRBASECALCIT,0),0)) AS VRBASEPIS,
                                         SUM(DECODE(IM.CDIMPOSTONFE,P_COFINS,DECODE(I.CDFISCALIT,'1',I.VRBASECALCIT,0),0)) AS VRBASECOFINS,
                                         SUM(DECODE(IM.CDIMPOSTONFE,P_PIS,DECODE(I.CDFISCALIT,'1',I.VRIMPOSTOIT,0),0)) AS VRIMPPIS,
                                         SUM(DECODE(IM.CDIMPOSTONFE,P_COFINS,DECODE(I.CDFISCALIT,'1',I.VRIMPOSTOIT,0),0)) AS VRIMPCOFINS,
                                         MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,DECODE(I.CDFISCALIT,'1',I.VRALIQITEM,0),'')) AS VRALIQPIS,
                                         MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,DECODE(I.CDFISCALIT,'1',I.VRALIQITEM,0),'')) AS VRALICOFINS,
                                         I.CDFILIAL, I.NRLANCTONF, I.NRSEQITPROD, IM.IDUTVLRQTDEIMPO
                                    FROM ITEMPROIMPOS I, IMPOSTO IM
                                   WHERE I.CDFILIAL     = P_CDFILIAL
                                     AND I.NRLANCTONF   = P_NRLANCTONF
                                     AND I.NRITPROIMPOS  = '1'
                                     AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                     AND IM.CDIMPOSTONFE IN (P_PIS, P_COFINS)
                                   GROUP BY I.CDFILIAL, I.NRLANCTONF, I.NRSEQITPROD, IM.IDUTVLRQTDEIMPO) I
                           WHERE IT.NRLANCTONF   = IP.NRLANCTONF
                             AND IT.CDFILIAL     = IP.CDFILIAL
                             AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                             AND IT.CDFILIAL     = P_CDFILIAL
                             AND IT.NRLANCTONF   = P_NRLANCTONF
                             AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                             AND IM.CDIMPOSTONFE = P_ICMS
                             AND IP.CDPRODUTO    = P.CDPRODUTO
                             AND P.CDPRODUTO     = R.CDPRODUTO(+)
                             AND P_LIVROMAG      = R.CDLIVROMAG(+)
                             AND IT.NRITPROIMPOS = '1'
                             AND ROUND(IP.QTITEMPROD,5)   > 0
                             AND IT.CDFILIAL     = I.CDFILIAL(+)
                             AND IT.NRLANCTONF   = I.NRLANCTONF(+)
                             AND IT.NRSEQITPROD  = I.NRSEQITPROD(+)
                             AND ((P_IDENTRSAIDOP = 'E' AND SUBSTR(IT.CDFISCOPERIT,2,3) NOT IN ('406','551','552','553','554','555','604') AND V_IDATIVO = 'S') OR
                                   V_IDATIVO = 'N')
                           ORDER BY IP.NRSEQITPROD)LOOP

            V_SEQITPROD     := NVL(V_SEQITPROD,0)+1;
            V_CDNATBCCOFINS := NULL;
            V_CDNATBCPIS    := NULL;
            V_CSTCOFINS     := NULL;
            V_CSTPIS        := NULL;
            V_VRBCPIS       := NULL;
            V_VRBCCOFINS    := NULL;
            V_VRCREDPIS     := NULL;
            V_VRCREDCOFINS  := NULL;
            V_VRALIQPIS     := NULL;
            V_VRALIQCOFINS  := NULL;
            V_VRPISRET      := NULL;
            V_VRCOFINSRET   := NULL;

            BEGIN
               SELECT MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,LPAD(I.CDNATUBCALPCIT,2,'0'),''))  AS CDNATUBCCOFINS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,LPAD(I.CDNATUBCALPCIT,2,'0'),''))  AS CDNATUBCPIS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,I.CDSITUTRIBITNFE,'')) AS CSTCOFINS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,I.CDSITUTRIBITNFE,'')) AS CSTPIS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,I.CDTPCREDPICOITE,'')) AS TPCREDPIS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,I.CDTPCREDPICOITE,'')) AS TPCREDCOFINS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,I.CDFISCALIT,'')) AS CDFISCALPIS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,I.CDFISCALIT,'')) AS CDFISCALCOFINS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,DECODE(I.CDFISCALIT,'1',VRBASECALCIT,0),'')) AS VRBCPIS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,DECODE(I.CDFISCALIT,'1',VRBASECALCIT,0),'')) AS VRBCCOFINS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,DECODE(I.CDFISCALIT,'1',VRIMPOSTOIT,0),'')) AS VRCREDPIS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,DECODE(I.CDFISCALIT,'1',VRIMPOSTOIT,0),'')) AS VRCREDCOFINS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,VRALIQITEM,'')) AS ALIQPIS,
                      MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,VRALIQITEM,'')) AS ALIQCOFINS,
                      0 AS VRPISRET,
                      0 AS VRCOFINSRET,
                      MAX(DECODE(NVL(IDUTVLRQTDEIMPO,'N'),'N','P','Q')) AS IDUTVLRQTDEIMPO
                 INTO V_CDNATBCCOFINS, V_CDNATBCPIS, V_CSTCOFINS, V_CSTPIS, V_CDTPCREDPIS,
                      V_CDTPCREDCOFINS, V_CDFISCALPIS, V_CDFISCALCOFINS, V_VRBCPIS, V_VRBCCOFINS,
                      V_VRCREDPIS, V_VRCREDCOFINS, V_VRALIQPIS, V_VRALIQCOFINS, V_VRPISRET, V_VRCOFINSRET, V_IDTPALIQ
                 FROM ITEMPROIMPOS I, IMPOSTO IM
                WHERE ((IM.CDIMPOSTONFE = P_PIS) OR (IM.CDIMPOSTONFE = P_PIS_RET_E) OR (IM.CDIMPOSTONFE = P_PIS_RET_S) OR
                       (IM.CDIMPOSTONFE = P_COFINS) OR (IM.CDIMPOSTONFE = P_COFINS_RET_E) OR (IM.CDIMPOSTONFE = P_COFINS_RET_S))
                  AND IM.CDIMPOSTO   = I.CDIMPOSTO
                  AND I.NRSEQITPROD  = REG_C170.NRSEQITPROD
                  AND I.CDFILIAL     = P_CDFILIAL
                  AND I.NRLANCTONF   = P_NRLANCTONF
                  AND I.NRITPROIMPOS = '1';
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                 IF P_IDENTRSAIDOP = 'S' THEN
                    V_CSTPIS    := '08';
                    V_CSTCOFINS := '08';
                 ELSE
                    V_CSTPIS    := '74';
                    V_CSTCOFINS := '74';
                 END IF;
                 V_CDNATBCCOFINS := NULL;
                 V_CDNATBCPIS    := NULL;
            END;

            V_REG:= '|C170|'||LPAD(V_SEQITPROD,3,'0')||'|'||REG_C170.PARTE1||'|'||V_CSTPIS||'|'||REG_C170.PARTE2||'|'||V_CSTCOFINS||'|'||REG_C170.PARTE3;
            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REG);

            /*INSERE OS VALORES DE CREDITO PARA AS NOTAS DE ENTRADA NA TABELA TEMPORARIA*/
            IF (P_IDENTRSAIDOP = 'E')  THEN
               /*INSERE O VALOR REFERENTE AO PIS ENTRADA*/
               IF (V_CSTPIS IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66','98')) AND (V_VRBCPIS>0)/*CDFISCALPIS = '1' AND VRALIQPIS > 0*/ THEN
                  V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
                  INSERT INTO TMP_REGBLOCOM_PIS
                     (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT)
                  VALUES
                     ('C170'||P_CDFILIAL||P_NRLANCTONF,'M100',V_TMPBLCM,V_CDTPCREDPIS,V_VRBCPIS,V_VRALIQPIS,V_IDTPALIQ,V_VRCREDPIS,V_CSTPIS,P_CDFILIRECE,V_CDNATBCPIS);
               END IF;

               /*INSERE O VALOR REFERENTE AO COFINS ENTRADA*/
               IF (V_CSTCOFINS IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66','98')) AND (V_VRBCCOFINS>0)/*CDFISCALCOFINS = '1' AND VRALIQCOFINS > 0*/ THEN
                  V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
                  INSERT INTO TMP_REGBLOCOM_PIS
                     (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT)
                  VALUES
                     ('C170'||P_CDFILIAL||P_NRLANCTONF,'M500',V_TMPBLCM,V_CDTPCREDCOFINS,V_VRBCCOFINS,V_VRALIQCOFINS,V_IDTPALIQ,V_VRCREDCOFINS,V_CSTCOFINS,P_CDFILIRECE,V_CDNATBCCOFINS);
               END IF;
            ELSE
               /*INSERE O VALOR REFERENTE AO PIS PARA SAIDAS(DEBITO)*/
               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
                  ('C170'||P_CDFILIAL||P_NRLANCTONF,'M200',V_TMPBLCM,V_CDTPCREDPIS,V_VRBCPIS,V_VRALIQPIS,V_IDTPALIQ,V_VRCREDPIS,V_CSTPIS,P_CDFILIEMIT,V_CDNATBCPIS,V_VRPISRET,REG_C170.VRCONTPIS,REG_C170.CDNATUREC,LTRIM(TO_CHAR(P_CONTEST,'000')),P_NRINSJUREST,REG_C170.COD_CTA);

               /*INSERE O VALOR REFERENTE AO COFINS SAIDAS(DEBITO)*/
               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
                  ('C170'||P_CDFILIAL||P_NRLANCTONF,'M600',V_TMPBLCM,V_CDTPCREDCOFINS,V_VRBCCOFINS,V_VRALIQCOFINS,V_IDTPALIQ,V_VRCREDCOFINS,V_CSTCOFINS,P_CDFILIEMIT,V_CDNATBCCOFINS,V_VRCOFINSRET,REG_C170.VRCONTCOFINS,REG_C170.CDNATUREC,LTRIM(TO_CHAR(P_CONTEST,'000')),P_NRINSJUREST,REG_C170.COD_CTA);
            END IF;

            V_UNID    := NULL;
            V_COD_NAT := NULL;
            V_PRODUTO := NULL;

            /* CODIGO DO CFOP*/
            SELECT MAX(CDCHAVEREG)
              INTO V_COD_NAT
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDCHAVEREG = REG_C170.CDFISCOPERIT
               AND CDREGISTRO = '0400'
               AND CDESTAB    = LTRIM(TO_CHAR(P_CONTEST,'000'));
            IF (V_COD_NAT IS NULL) AND (REG_C170.CDFISCOPERIT IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1,CDESTAB,CDCAMPO2)
               VALUES
                  ('0400', V_SEQUPROD, REG_C170.CDFISCOPERIT,NULL,LTRIM(TO_CHAR(P_CONTEST,'000')),P_IDENTRSAIDOP);
            END IF;

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

            /* CODIGO DO PRODUTO */
            SELECT MAX(CDCHAVEREG)
              INTO V_PRODUTO
              FROM TMP_REGDADOSSPED_PIS
             WHERE CDCHAVEREG = REG_C170.CDARVPROD
               AND CDREGISTRO = '0200'
               AND CDESTAB    = LTRIM(TO_CHAR(P_CONTEST,'000'));
            IF (V_PRODUTO IS NULL) AND (REG_C170.CDARVPROD IS NOT NULL) THEN
               V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
               INSERT INTO TMP_REGDADOSSPED_PIS
                  (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB,CDCAMPO4)
               VALUES
                  ('0200', V_SEQUPROD, REG_C170.CDARVPROD,NULL,LTRIM(TO_CHAR(P_CONTEST,'000')),REG_C170.CDCLASFISC);
            END IF;

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

            IF (V_COD_CC IS NULL) AND (REG_C170.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, REG_C170.COD_CTA, NULL);
            END IF;
         END LOOP;
      END IF;
   END IF;

   /*INSERE AS NOTAS DE MODELO 02 PARA GERAR O REGISTRO C380 - REGISTRO GERADO NO BLOCO 0*/
   IF P_IDENTRSAIDOP = 'S' AND P_CDMODDOCFISC = '02' THEN
      V_SEQUPROD:= NVL(V_SEQUPROD,0) + 1;
      INSERT INTO TMP_REGDADOSSPED_PIS
         (CDREGISTRO,CDCONTREGI,CDCHAVEREG,CDCAMPO1,CDESTAB,CDCAMPO3,CDCAMPO4,CDCAMPO5,CDCAMPO6,CDCAMPO7,CDCAMPO8,CDCAMPO9,NRINSJUREST)
      VALUES
         ('C380',V_SEQUPROD,P_NRLANCTONF||P_CDFILIAL,P_DTEMISSAO,P_CONTEST,P_IDSTATUS,P_CDFILIAL, P_NRLANCTONF,P_NRNOTAFISC,P_CDFILIEMIT,P_VRNOTAFISC,P_CDTIPOOPER,P_NRINSJUREST);
   END IF;

   /*REGISTRO C500*/
   IF (P_CDMODDOCFISC IN ('06','29','28','66')) OR ((P_CDMODDOCFISC = '55') AND (V_ENERGIA = 'S')) THEN
      FOR REG_C500 IN (SELECT ('|C500'                            ||'|'||
                              P_IDORIGDESTNF||DECODE(P_IDORIGDESTNF,'L',DECODE(P_IDENTRSAIDOP,'E',P_CDFILIEMIT,P_CDFILIRECE),NVL(P_CDCLIENTE,P_CDFORNECED))||'|'||
                              P_CDMODDOCFISC                     ||'|'||
                              DECODE(P_IDSTATUS,'C','02',DECODE(P_IDENTRSAIDOP,'S', (CASE WHEN P_DTEMISSAO < P_DTINICIAL THEN DECODE(P_CDTIPOOPER,'03','07',P_TIPOOPER_COMPL_S,'07','01')
                                      ELSE DECODE(P_CDTIPOOPER,'03','06',P_TIPOOPER_COMPL_S,'06','00')END),(CASE WHEN P_DTENTRSAID < P_DTINICIAL THEN DECODE(P_CDTIPOOPER,'03','07',P_TIPOOPER_COMPL_E,'07','01')
                                         ELSE DECODE(P_CDTIPOOPER,'03','06',P_TIPOOPER_COMPL_E,'06','00')END))) ||'|'||
                              P_CDSERIE                          ||'|'||
                              ''                                 ||'|'||/*SUBSERIE*/
                              SUBSTR(LPAD(NVL(P_NRNOTAFISC,P_NRACESSONFE),10,'0'),2,9)                      ||'|'||
                              TO_CHAR(P_DTEMISSAO,'ddmmyyyy')    ||'|'||
                              CASE WHEN P_DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL THEN TO_CHAR(P_DTENTRSAID,'ddmmyyyy') ELSE TO_CHAR(P_DTFINAL,'ddmmyyyy') END  ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(P_VRNOTAFISC,2),'9999999999990.00')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'I','1'),2),'9999999999990.00')),'.',',') ||'|'||
                              '' ||'|'|| /*CODIGO INFORMACAO*/
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(P_CDFILIAL,P_NRLANCTONF,P_PIS,'I','1'),2),'9999999999990.00')),'.',',') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(P_CDFILIAL,P_NRLANCTONF,P_COFINS,'I','1'),2),'9999999999990.00')),'.',',') ||'|' ||
                              CASE WHEN P_VERSAO >= '006' THEN P_NRACESSONFE ||'|' ELSE '' END) AS REGC500,
                              P_IDORIGDESTNF||DECODE(P_IDORIGDESTNF,'L',DECODE(P_IDENTRSAIDOP,'E',P_CDFILIEMIT,P_CDFILIRECE),NVL(P_CDCLIENTE,P_CDFORNECED)) AS CD_PARTC
                         FROM DUAL)LOOP

         V_CONTREGA := NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'04'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_C500.REGC500);

         V_COD_PAR:=NULL;
         /*CODIGO DO PARTICIPANTE*/
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDCHAVEREG = REG_C500.CD_PARTC
            AND CDREGISTRO = '0150'
            AND CDESTAB   = LTRIM(TO_CHAR(P_CONTEST,'000'));

         IF (V_COD_PAR IS NULL) AND (REG_C500.CD_PARTC IS NOT NULL) THEN
            V_SEQUPROD:= NVL(V_SEQUPROD,0)+1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
            VALUES
               ('0150', V_SEQUPROD, REG_C500.CD_PARTC, NULL, LTRIM(TO_CHAR(P_CONTEST,'000')));
         END IF;

         /*C501 COMPLEMENTO DO DOCUMENTO DE TRANSPORTE PIS*/
         FOR C_REG_C501 IN (SELECT '|C501'                                                               ||'|'||
                                    IT.CDSITUTRIBITNFE                                                   ||'|'||
                                    REPLACE(LTRIM(TO_CHAR(SUM(IP.VRTOTAITEM),'9999999999900.00')),'.',',')    ||'|'||
                                    IT.CDNATUBCALPCIT                                                  ||'|'||/*NATUREZA BASE DE CALCULO*/
                                    REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0)),'9999999999900.00')),'.',',')||'|'||
                                    REPLACE(LTRIM(TO_CHAR(DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0),'9900.0000')),'.',',')||'|'||
                                    REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IT.CDFISCALIT,'1',VRIMPOSTOIT,0)),'9999999999900.00')),'.',',')||'|'||
                                    CONTCTBL_SPED(P_IDENTRSAIDOP,IP.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)) ||'|'  /*CODIGO DA CONTA ANALITICA*/ AS DADOSPIS,
                                    IT.CDTPCREDPICOITE, SUM(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0)) AS VRBASECALCIT,
                                    DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0) AS VRALIQITEM,
                                    SUM(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0)) AS VRIMPOSTOIT,
                                    IT.CDSITUTRIBITNFE, IT.CDNATUBCALPCIT, CDFISCALIT,
                                    CONTCTBL_SPED(P_IDENTRSAIDOP,IP.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)) AS COD_CTA
                               FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM
                              WHERE IT.NRLANCTONF   = P_NRLANCTONF
                                AND IT.CDFILIAL     = P_CDFILIAL
                                AND IM.CDIMPOSTONFE = P_PIS
                                AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                AND IT.NRLANCTONF   = IP.NRLANCTONF
                                AND IT.CDFILIAL     = IP.CDFILIAL
                                AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                                AND IT.NRITPROIMPOS = '1'
                               GROUP BY IT.CDSITUTRIBITNFE, IT.CDNATUBCALPCIT, IT.CDFISCALIT, DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0), IT.CDTPCREDPICOITE,
                                     CONTCTBL_SPED(P_IDENTRSAIDOP,IP.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)))LOOP

            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'04'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REG_C501.DADOSPIS);

            IF ((C_REG_C501.CDSITUTRIBITNFE BETWEEN '50' AND '67') OR (C_REG_C501.CDSITUTRIBITNFE='98')) AND (C_REG_C501.VRBASECALCIT > 0) THEN
               /*INSERE O VALOR REFERENTE AO PIS*/
               V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED, CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT)
               VALUES
                  ('C501'||P_CDFILIAL||P_NRLANCTONF,'M100',V_TMPBLCM,C_REG_C501.CDTPCREDPICOITE,C_REG_C501.VRBASECALCIT,C_REG_C501.VRALIQITEM,'P',C_REG_C501.VRIMPOSTOIT,C_REG_C501.CDSITUTRIBITNFE,P_CDFILIRECE,C_REG_C501.CDNATUBCALPCIT);
            END IF;

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

            IF (V_COD_CC IS NULL) AND (C_REG_C501.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_REG_C501.COD_CTA, NULL);
            END IF;
         END LOOP;

         /*C505 COMPLEMENTO DO DOCUMENTO DE TRANSPORTE COFINS*/
         FOR C_REG_C505 IN (SELECT '|C505'                                                         ||'|'||
                                   IT.CDSITUTRIBITNFE                                             ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(IP.VRTOTAITEM),'9999999999900.00')),'.',',')  ||'|'||
                                   IT.CDNATUBCALPCIT                                                  ||'|'||/*NATUREZA BASE DE CALCULO*/
                                   REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0)),'9999999999900.00')),'.',',')||'|'||
                                   REPLACE(LTRIM(TO_CHAR(DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0),'9900.0000')),'.',',')||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0)),'9999999999900.00')),'.',',')||'|'||
                                   CONTCTBL_SPED(P_IDENTRSAIDOP,IP.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)) ||'|' /*CODIGO DA CONTA ANALICA*/ AS DADOSCOFINS,
                                   IT.CDTPCREDPICOITE, SUM(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0)) AS VRBASECALCIT,
                                   DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0) AS VRALIQITEM,
                                   SUM(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0)) AS VRIMPOSTOIT,
                                   IT.CDSITUTRIBITNFE,IT.CDNATUBCALPCIT, CDFISCALIT,
                                   CONTCTBL_SPED(P_IDENTRSAIDOP,IP.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)) AS COD_CTA
                              FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM
                             WHERE IT.NRLANCTONF   = P_NRLANCTONF
                               AND IT.CDFILIAL     = P_CDFILIAL
                               AND IM.CDIMPOSTONFE = P_COFINS
                               AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IT.NRLANCTONF   = IP.NRLANCTONF
                               AND IT.CDFILIAL     = IP.CDFILIAL
                               AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                               AND IT.NRITPROIMPOS = '1'
                             GROUP BY IT.CDSITUTRIBITNFE, IT.CDNATUBCALPCIT, IT.CDFISCALIT, DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0),IT.VRALIQITEM,IT.CDTPCREDPICOITE,
                                   CONTCTBL_SPED(P_IDENTRSAIDOP,IP.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)))LOOP

            V_CONTREGA := NVL(V_CONTREGA,0)+1;
            INSERT INTO ARQMAGPISCOFINS
               (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
            VALUES
               (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'04'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REG_C505.DADOSCOFINS );

            IF ((C_REG_C505.CDSITUTRIBITNFE BETWEEN '50' AND '67') OR (C_REG_C505.CDSITUTRIBITNFE='98')) AND (C_REG_C505.VRBASECALCIT > 0) THEN
               /*INSERE O VALOR REFERENTE AO PIS*/
               V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO,CDCONTREGI,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CSTPISCOFINS,CDFILIAL,CDNATUBCALPCIT)
               VALUES
                  ('C505'||P_CDFILIAL||P_NRLANCTONF,'M500',V_TMPBLCM,C_REG_C505.CDTPCREDPICOITE,C_REG_C505.VRBASECALCIT,C_REG_C505.VRALIQITEM,'P',C_REG_C505.VRIMPOSTOIT,C_REG_C505.CDSITUTRIBITNFE,P_CDFILIRECE,C_REG_C505.CDNATUBCALPCIT);
            END IF;

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

            IF (V_COD_CC IS NULL) AND (C_REG_C505.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_REG_C505.COD_CTA, NULL);
            END IF;
         END LOOP;
      END LOOP;
   END IF;

   /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
   V_IDC010:= NULL;
   SELECT MAX(CDCHAVEREG)
     INTO V_IDC010
     FROM TMP_REGDADOSSPED_PIS
    WHERE CDREGISTRO = 'C010'
      AND CDCHAVEREG =  P_NRINSJUREST;

   /*IDENTIFICACAO DO ESTABELECIMENTO*/
   IF (V_IDC010 IS NULL) AND (P_NRINSJUREST IS NOT NULL) THEN
      V_REG_C010 := '|C010|'||P_NRINSJUREST||'|2|';
      V_CONTREGA := NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'303'||LTRIM(TO_CHAR(P_CONTEST,'000')),V_REG_C010);

      INSERT INTO TMP_REGDADOSSPED_PIS
         (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
      VALUES
         ('C010', V_SEQUPROD, P_NRINSJUREST ,NULL,LTRIM(TO_CHAR(P_CONTEST,'000')));
   END IF;
   RETURN(LPAD(TO_CHAR(V_CONTREGA),20,'0')||'|'||LPAD(TO_CHAR(V_SEQUPROD),20,'0')||'|'||LPAD(TO_CHAR(V_TMPBLCM),20,'0'));
END;
/