CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCOD(
   P_NRLANCTONF   IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_CDFILIAL     IN NOTAFISCAL.CDFILIAL%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_CDTIPOOPER   IN NOTAFISCAL.CDTIPOOPER%TYPE,
   P_IDSTATUS     IN NOTAFISCAL.IDSTATUS%TYPE,
   P_IDORIGDESTNF IN NOTAFISCAL.IDORIGDESTNF%TYPE,
   P_IDNATFRETECTE IN NOTAFISCAL.IDNATFRETECTE%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_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_ICMS         IN IMPOSTO.CDIMPOSTO%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_CDMODDOCFISC IN NOTAFISCAL.CDMODDOCFISC%TYPE,
   P_VRDESTOTIT   IN FLOAT,
   P_IDOUTROS     IN ENCARGO.IDENCARGO%TYPE,
   P_SEQUPROD     IN IMPOSTONF.VRIMPOSTONF%TYPE,
   P_TMPBLCM      IN IMPOSTONF.VRIMPOSTONF%TYPE,
   P_NRACESSONFE  IN NOTAFISCAL.NRACESSONFE%TYPE,
   P_CDTIPOCTE    IN NOTAFISCAL.CDTIPOCTE%TYPE,
   P_IDSTATUSNFE  IN NOTAFISCAL.IDSTATUSNFE%TYPE) RETURN VARCHAR
AS
   V_REG_D010     VARCHAR2(1000);
   V_REG          VARCHAR2(1000);
   V_COD_PAR      VARCHAR(100);
   V_COD_NAT      VARCHAR2(100);
   V_IDIMPORTACAO VARCHAR2(1);
   V_UNID         VARCHAR2(100);
   V_PRODUTO      VARCHAR2(100);
   V_IDD010       VARCHAR2(20);
   V_SEQUPROD     NUMBER;
   V_CONTREGA     NUMBER;
   V_COUNTPARC    NUMBER;
   V_TMPBLCM      NUMBER;
   V_COD_CC       VARCHAR2(20);
   V_SEQCCTBL     NUMBER;
   V_CONTCTBLD100 VARCHAR2(20);
BEGIN
   V_CONTREGA:= P_CONTREGA;
   V_SEQUPROD:= P_SEQUPROD;
   V_TMPBLCM := P_TMPBLCM;

   IF P_CDMODDOCFISC NOT IN  ('22','21') THEN
      /*BUSCA A CONTA CONTABIL PARA GERAR O REGISTRO D100. UTILIZA O MAX PORQUE SO PODE HAVER UM D100 POR NOTA FISCAL.
        SE OS PRODUTOS DA NOTA POSSUIREM CONTAS DIFERENTES, APENAS UMA DELAS SERA INFORMADA NO REGISTRO*/
      BEGIN
         SELECT MAX(CONTCTBL_SPED(P_IDENTRSAIDOP,I.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)))
           INTO V_CONTCTBLD100
           FROM ITEMPRODUTO I
          WHERE I.CDFILIAL   = P_CDFILIAL
            AND I.NRLANCTONF = P_NRLANCTONF;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
           V_CONTCTBLD100 := NULL;
      END;
      /* GERA A NF */
      FOR REG_D100 IN(SELECT '|D100'   ||'|'||
                             '0'       ||'|'||
                             DECODE(P_IDENTRSAIDOP,'E','1','0') ||'|'||
                             P_IDORIGDESTNF||DECODE(P_IDORIGDESTNF,'L',DECODE(P_IDENTRSAIDOP,'E',P_CDFILIEMIT,P_CDFILIRECE),NVL(P_CDCLIENTE,P_CDFORNECED))||'|'||
                             P_CDMODDOCFISC                     ||'|'||
                             DECODE(P_IDSTATUSNFE,'D','04',DECODE(P_IDSTATUS,'C','02',DECODE(P_CDTIPOOPER,'03','06','00'))) ||'|'||
                             P_CDSERIE                          ||'|'||
                             ''                                 ||'|'|| /*SUBSERIE*/
                             SUBSTR(LPAD(P_NRNOTAFISC,10,'0'),2,9)  ||'|'||
                             CASE WHEN P_CDMODDOCFISC IN ('57','67') THEN P_NRACESSONFE ELSE '' END ||'|'||
                             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   ||'|'||
                             P_CDTIPOCTE                        ||'|'||
                             ''                                 ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(P_VRNOTAFISC,2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(P_CDFILIAL,P_NRLANCTONF,'D')+P_VRDESTOTIT,2),'9999999999990.00')),'.',',') ||'|'||
                             '2'                                                                       ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(P_VRNOTAFISC,2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'B','1'),2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'I','1'),2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'C','2')+ VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'C','3'),2),'9999999999990.00')),'.',',') ||'|'||
                             ''                                                                        ||'|'||
                             V_CONTCTBLD100                                                            ||'|' AS REGD100,
                             P_IDORIGDESTNF||DECODE(P_IDORIGDESTNF,'L',DECODE(P_IDENTRSAIDOP,'E',P_CDFILIEMIT,P_CDFILIRECE),NVL(P_CDCLIENTE,P_CDFORNECED)) AS CD_PARTC,
                             V_CONTCTBLD100 AS COD_CTA
                        FROM DUAL)LOOP

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

         V_COD_PAR := NULL;
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDCHAVEREG = REG_D100.CD_PARTC
            AND CDREGISTRO = '0150'
            AND CDESTAB = LTRIM(TO_CHAR(P_CONTEST,'000'));

         IF (V_COD_PAR IS NULL) AND (REG_D100.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_D100.CD_PARTC, NULL, LTRIM(TO_CHAR(P_CONTEST,'000')));
         END IF;

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

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

         /*D101 COMPLEMENTO DO DOCUMENTO DE TRANSPORTE PIS*/
         FOR C_REG_D101 IN (SELECT '|D101'                                                            ||'|'||
                                   NVL(P_IDNATFRETECTE,DECODE(P_IDORIGDESTNF,'L','4','2'))            ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(IP.VRTOTAITEM),'9999999999900.00')),'.',',')  ||'|'||
                                   IT.CDSITUTRIBITNFE                                                 ||'|'||
                                   IT.CDNATUBCALPCIT                                                  ||'|'|| /*NATUREZA BASE DE CALCULO OLHAR COM O WANDER O DESENVOLVIEMNTO*/
                                   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 DADOSPIS,
                                   IT.CDSITUTRIBITNFE, IT.CDNATUBCALPCIT, DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0) AS VRALIQITEM, IT.CDTPCREDPICOITE,
                                   SUM(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0)) AS VRBASECALCIT,
                                   SUM(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0)) AS VRIMPOSTOIT,
                                   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 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'
                             GROUP BY NVL(P_IDNATFRETECTE,DECODE(P_IDORIGDESTNF,'L','4','2')), IT.CDSITUTRIBITNFE, IT.CDNATUBCALPCIT, 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,'403'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REG_D101.DADOSPIS);

            IF (C_REG_D101.CDSITUTRIBITNFE IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66','98')) AND (C_REG_D101.VRIMPOSTOIT>0) THEN /*(P_IDENTRSAIDOP = 'E' AND C_REG_D101.CDFISCALIT = '1' AND C_REG_D101.VRALIQITEM > 0)*/
               /*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
                  ('D101'||P_CDFILIAL||P_NRLANCTONF,'M100',V_TMPBLCM,C_REG_D101.CDTPCREDPICOITE,C_REG_D101.VRBASECALCIT,C_REG_D101.VRALIQITEM,'P',C_REG_D101.VRIMPOSTOIT,C_REG_D101.CDSITUTRIBITNFE,P_CDFILIRECE,C_REG_D101.CDNATUBCALPCIT);
            END IF;

            /* CODIGO DA CONTA CONTABIL */
            BEGIN
               SELECT MAX(CDCHAVEREG)
                 INTO V_COD_CC
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = C_REG_D101.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_D101.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_D101.COD_CTA, NULL);
            END IF;
         END LOOP;
         /*D105 COMPLEMENTO DO DOCUMENTO DE TRANSPORTE COFINS*/
         FOR C_REG_D105 IN (SELECT '|D105'                                                            ||'|'||
                                   NVL(P_IDNATFRETECTE,DECODE(P_IDORIGDESTNF,'L','4','2'))            ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(IP.VRTOTAITEM),'9999999999900.00')),'.',',')  ||'|'||
                                   IT.CDSITUTRIBITNFE                                                 ||'|'||
                                   IT.CDNATUBCALPCIT                                                  ||'|'||
                                   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.CDSITUTRIBITNFE, IT.CDNATUBCALPCIT, DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0) AS VRALIQITEM, IT.CDTPCREDPICOITE,
                                   SUM(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0)) AS VRBASECALCIT,
                                   SUM(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0)) AS VRIMPOSTOIT,
                                   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.CDIMPOSTO    = IT.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'
                             GROUP BY IT.CDSITUTRIBITNFE, IT.CDNATUBCALPCIT, 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,'403'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REG_D105.DADOSCOFINS );

            IF (C_REG_D105.CDSITUTRIBITNFE IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66','98')) AND (C_REG_D105.VRIMPOSTOIT > 0) THEN  /*(P_IDENTRSAIDOP = 'E' AND C_REG_D105.CDFISCALIT = '1' AND C_REG_D105.VRALIQITEM > 0)*/
               /*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
                  ('D105'||P_CDFILIAL||P_NRLANCTONF,'M500',V_TMPBLCM,C_REG_D105.CDTPCREDPICOITE,C_REG_D105.VRBASECALCIT,C_REG_D105.VRALIQITEM,'P',C_REG_D105.VRIMPOSTOIT,C_REG_D105.CDSITUTRIBITNFE,P_CDFILIRECE,C_REG_D105.CDNATUBCALPCIT);
            END IF;

            /* CODIGO DA CONTA CONTABIL */
            BEGIN
               SELECT MAX(CDCHAVEREG)
                 INTO V_COD_CC
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = C_REG_D105.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_D105.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_D105.COD_CTA, NULL);
            END IF;
         END LOOP;
      END LOOP;
   END IF;

   IF P_CDMODDOCFISC IN ('21','22') THEN
      /*REGISTRO D500*/
      FOR REG_D500 IN(SELECT ('|D500'   ||'|'||
                             '0'       ||'|'||
                             '1'       ||'|'||
                             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','00')   ||'|'||
                             P_CDSERIE                          ||'|'||
                             ''                                 ||'|'||
                             SUBSTR(LPAD(P_NRNOTAFISC,10,'0'),2,9)                       ||'|'||
                             TO_CHAR(P_DTEMISSAO,'ddmmyyyy')    ||'|'||
                             TO_CHAR(P_DTENTRSAID,'ddmmyyyy')   ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(P_VRNOTAFISC,2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(P_CDFILIAL,P_NRLANCTONF,'D'),2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(P_VRNOTAFISC+VALOR_ENCARGO(P_CDFILIAL,P_NRLANCTONF,'D'),2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'C','2')+VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'C','3'),2),'9999999999990.00')),'.',',') ||'|'||
                             '0,00'                                                                                                                                                                            ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(P_CDFILIAL,P_NRLANCTONF,P_IDOUTROS),2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'B','1'),2),'9999999999990.00')),'.',',') ||'|'||
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ICMS,'I','1'),2),'9999999999990.00')),'.',',') ||'|'||
                             ''                                                                                                                    ||'|'||
                             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')),'.',',') ||'|') AS REGD500,
                             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,'403'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'02'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_D500.REGD500);

         V_COD_PAR := null;
         SELECT MAX(CDCHAVEREG)
           INTO V_COD_PAR
           FROM TMP_REGDADOSSPED_PIS
          WHERE CDCHAVEREG = REG_D500.CD_PARTC
            AND CDREGISTRO = '0150'
            AND CDESTAB = LTRIM(TO_CHAR(P_CONTEST,'000'));

         IF (V_COD_PAR IS NULL) AND (REG_D500.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_D500.CD_PARTC, NULL, LTRIM(TO_CHAR(P_CONTEST,'000')));
         END IF;

         /*D501 COMPLEMENTO DA OPERACAO PIS PASEP*/
         FOR  C_REG_D501 IN (SELECT '|D501'                                                                ||'|'||
                                    IT.CDSITUTRIBITNFE                                                     ||'|'||
                                    REPLACE(LTRIM(TO_CHAR(SUM(IP.VRTOTAITEM),'9999999999900.00')),'.',',') ||'|'||
                                    CDNATUBCALPCIT                                                         ||'|'||
                                    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 DADOSPIS,
                                    CDTPCREDPICOITE, SUM(VRBASECALCIT) AS VRBASECALCIT,VRALIQITEM,SUM(VRIMPOSTOIT) AS VRIMPOSTOIT,
                                    CDNATUBCALPCIT, CDFISCALIT,IT.CDSITUTRIBITNFE,
                                    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 CDTPCREDPICOITE,VRALIQITEM,CDNATUBCALPCIT, CDFISCALIT,IT.CDSITUTRIBITNFE,
                                    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,'403'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'02'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REG_D501.DADOSPIS);

            IF (C_REG_D501.CDSITUTRIBITNFE IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66','98')) AND (C_REG_D501.VRIMPOSTOIT>0) THEN/*(P_IDENTRSAIDOP = 'E' AND C_REG_D501.CDFISCALIT = '1' AND C_REG_D501.VRALIQITEM > 0)*/
               /*INSERE O VALOR REFERENTE AO PIS*/
               V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO,CDCONTREGI,CSTPISCOFINS,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CDNATUBCALPCIT)
               VALUES
                  ('D501'||P_CDFILIAL||P_NRLANCTONF,'M100',V_TMPBLCM,C_REG_D501.CDSITUTRIBITNFE,C_REG_D501.CDTPCREDPICOITE,C_REG_D501.VRBASECALCIT,C_REG_D501.VRALIQITEM,'P',C_REG_D501.VRIMPOSTOIT,C_REG_D501.CDNATUBCALPCIT);
            END IF;

            /* CODIGO DA CONTA CONTABIL */
            BEGIN
               SELECT MAX(CDCHAVEREG)
                 INTO V_COD_CC
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = C_REG_D501.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_D501.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_D501.COD_CTA, NULL);
            END IF;
         END LOOP;
         /*D501 COMPLEMENTO DA OPERACAO COFINS*/
         FOR C_REG_D505 IN (SELECT '|D505'                                                            ||'|'||
                                   IT.CDSITUTRIBITNFE                                                ||'|'||
                                   REPLACE(LTRIM(TO_CHAR(SUM(IP.VRTOTAITEM),'9999999999900.00')),'.',',') ||'|'||
                                   CDNATUBCALPCIT                                                    ||'|'||
                                   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,
                                   CDTPCREDPICOITE,SUM(VRBASECALCIT) AS VRBASECALCIT,VRALIQITEM,SUM(VRIMPOSTOIT) AS VRIMPOSTOIT,
                                   CDNATUBCALPCIT, CDFISCALIT, IT.CDSITUTRIBITNFE,
                                   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.NRLANCTONF   = IP.NRLANCTONF
                               AND IT.CDFILIAL     = IP.CDFILIAL
                               AND IM.CDIMPOSTO    = IT.CDIMPOSTO
                               AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                               AND IT.NRITPROIMPOS = '1'
                             GROUP BY CDTPCREDPICOITE,VRALIQITEM,CDNATUBCALPCIT, CDFISCALIT, IT.CDSITUTRIBITNFE,
                                   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,'403'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'02'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_REG_D505.DADOSCOFINS);

            IF (C_REG_D505.CDSITUTRIBITNFE IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66','98')) AND (C_REG_D505.VRIMPOSTOIT>0) THEN /*(P_IDENTRSAIDOP = 'E' AND C_REG_D505.CDFISCALIT = '1' AND C_REG_D505.VRALIQITEM>0)*/
               /*INSERE O VALOR REFERENTE AO COFINS*/
               V_TMPBLCM:= NVL(V_TMPBLCM,0)+1;
               INSERT INTO TMP_REGBLOCOM_PIS
                  (CHAVENOTA,CDREGISTRO,CDCONTREGI,CSTPISCOFINS,CDTPCRED,VRBCCRED,VRALIQCRED,IDTPALIQ,VRCRED,CDNATUBCALPCIT)
               VALUES
                  ('D505'||P_CDFILIAL||P_NRLANCTONF,'M500',V_TMPBLCM,C_REG_D505.CDSITUTRIBITNFE,C_REG_D505.CDTPCREDPICOITE,C_REG_D505.VRBASECALCIT,C_REG_D505.VRALIQITEM,'P',C_REG_D505.VRIMPOSTOIT,C_REG_D505.CDNATUBCALPCIT);
            END IF;

            /* CODIGO DA CONTA CONTABIL */
            BEGIN
               SELECT MAX(CDCHAVEREG)
                 INTO V_COD_CC
                 FROM TMP_REGDADOSSPED_PIS
                WHERE CDCHAVEREG = C_REG_D505.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_D505.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_D505.COD_CTA, NULL);
            END IF;
         END LOOP;
      END LOOP;
   END IF;

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

   /*IDENTIFICACAO DO ESTABELECIMENTO*/
   IF V_IDD010 IS NULL THEN
      V_REG_D010 := '|D010|'||P_NRINSJUREST||'|';
      V_CONTREGA := NVL(V_CONTREGA,0)+1;

      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG ,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'403'||LTRIM(TO_CHAR(P_CONTEST,'000')),V_REG_D010);

      INSERT INTO TMP_REGDADOSSPED_PIS
         (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
      VALUES
         ('D010', 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;
/