DROP TABLE TMP_REGDADOSSPED_PIS
/
DROP TABLE TMP_REGBLOCOM_PIS
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_PIS
(
   CDREGISTRO      VARCHAR2(04)   NOT NULL,
   CDCONTREGI      NUMBER         NOT NULL,
   CDCHAVEREG      VARCHAR2(50)   NOT NULL,
   CDESTAB         VARCHAR2(1000)   NULL,
   CDCAMPO1        VARCHAR2(1000)   NULL,
   CDCAMPO2        VARCHAR2(1000)   NULL,
   CDCAMPO3        VARCHAR2(1000)   NULL,
   CDCAMPO4        VARCHAR2(1000)   NULL,
   CDCAMPO5        VARCHAR2(1000)   NULL,
   CDCAMPO6        VARCHAR2(1000)   NULL,
   CDCAMPO7        VARCHAR2(1000)   NULL,
   CDCAMPO8        VARCHAR2(1000)    NULL,
   CDCAMPO9        VARCHAR2(1000)   NULL,
   NRINSJUREST     VARCHAR2(50)     NULL,
   CONSTRAINT PK_TMP_REGDADOSSPED_PIS PRIMARY KEY(CDREGISTRO, CDCONTREGI, CDCHAVEREG)) ON COMMIT PRESERVE ROWS
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGBLOCOM_PIS
(
   CDREGISTRO      VARCHAR2(04)   NOT NULL,
   CDCONTREGI      NUMBER         NOT NULL,
   CDTPCRED        VARCHAR2(04)   NULL,
   VRBCCRED        FLOAT          NULL,
   VRALIQCRED      FLOAT          NULL,
   IDTPALIQ        VARCHAR2(1)    NULL,
   VRCRED          FLOAT          NULL,
   CSTPISCOFINS    VARCHAR2(2)    NULL,
   CDFILIAL        VARCHAR2(4)    NULL,
   CDNATUBCALPCIT  VARCHAR2(2)    NULL,
   VRIMPRETIDO     FLOAT          NULL,
   VRTOTNF         FLOAT          NULL,
   CDNATUREC       VARCHAR(5)     NULL,
   CDESTAB         VARCHAR2(1000) NULL,
   NRINSJUREST     VARCHAR2(50)   NULL,
   CDCONTCTBL      VARCHAR2(20)   NULL,
   IDAJUSTE        VARCHAR2(1)    NULL,
   CDAJUSTE        VARCHAR2(2)    NULL,
   VRAJUSTE        FLOAT          NULL,
   CHAVENOTA       VARCHAR2(25)   NULL,
   CONSTRAINT PK_TMP_REGBLOCOM_PIS PRIMARY KEY(CDREGISTRO, CDCONTREGI)) ON COMMIT PRESERVE ROWS
/

DROP TABLE TMP_REGDADOSSPED_PIS_AUX
/

DROP TABLE TMP_REGBLOCOM_PIS_AUX
/

CREATE TABLE TMP_REGDADOSSPED_PIS_AUX AS (SELECT * FROM TMP_REGDADOSSPED_PIS)
/

CREATE TABLE TMP_REGBLOCOM_PIS_AUX AS (SELECT * FROM TMP_REGBLOCOM_PIS)
/

DROP TABLE TMP_REGDADOSSPED_NF_AUX
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_NF_AUX 
(
	IDENTRSAIDOP 	VARCHAR2(1 BYTE), 
	CDTIPOOPER 		VARCHAR2(2 BYTE), 
	CDFILIAL 		VARCHAR2(4 BYTE)
) ON COMMIT DELETE ROWS 
/

DROP TABLE TMP_REGDADOSSPED_NF
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_NF
(	
	IDENTRSAIDOP	VARCHAR2(1 BYTE), 
	CDTIPOOPER		VARCHAR2(2 BYTE), 
	CDFILIAL		VARCHAR2(4 BYTE), 
	CONTCTBL		VARCHAR2(20 BYTE)
) ON COMMIT DELETE ROWS 
/

DROP TABLE TMP_REGDADOSSPED_ECF_NF
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_ECF_NF AS
	SELECT *
	FROM NOTAFISCAL NF
	WHERE 1=0
/

DROP TABLE TMP_REGDADOSSPED_ECF_RE
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGDADOSSPED_ECF_RE AS
	SELECT *
	FROM RELACNFCEAPROV
	WHERE 1=0
/

CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCOA(
   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_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 NUMBER,
   P_VRDESTOTIT   IN FLOAT,
   P_SEQUPROD     IN IMPOSTONF.VRIMPOSTONF%TYPE,
   P_TMPBLCM      IN IMPOSTONF.VRIMPOSTONF%TYPE,
   P_LIVROMAG     IN RELACPRODITGEN.CDLIVROMAG%TYPE) RETURN VARCHAR
AS
   V_REG_A010          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);
   CSTPIS              VARCHAR2(2);
   CSTCOFINS           VARCHAR2(2);
   CDNATBCCOFINS       VARCHAR2(2);
   CDNATBCPIS          VARCHAR2(2);
   CDTPCREDPIS         VARCHAR2(3);
   CDTPCREDCOFINS      VARCHAR2(3);
   CDFISCALPIS         VARCHAR2(1);
   CDFISCALCOFINS      VARCHAR2(1);
   V_PARTICIPANTE      VARCHAR(40);
   V_IDA010            VARCHAR(20);
   V_COD_CC            VARCHAR2(20);
   V_SEQCCTBL          NUMBER;
   V_SEQITPROD         NUMBER;
   V_CONTREGA          NUMBER;
   V_COUNTPARC         NUMBER;
   V_TMPBLCM           NUMBER;
   V_SEQUPROD          NUMBER;
   VRBCPIS             NUMBER;
   VRBCCOFINS          NUMBER;
   VRCREDPIS           NUMBER;
   VRCREDCOFINS        NUMBER;
   VRALIQPIS           NUMBER;
   VRALIQCOFINS        NUMBER;
   VRPISRET            NUMBER;
   VRCOFINSRET         NUMBER;
BEGIN
   V_CONTREGA:= P_CONTREGA;
   V_SEQUPROD:= P_SEQUPROD;
   V_TMPBLCM := P_TMPBLCM;

   /*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;

   /* SELECIONA A NF COM ISS */
   FOR REG_A100 IN(SELECT ('|A100'                               ||'|'||
                           DECODE(P_IDENTRSAIDOP,'E','0','1')    ||'|'||
                           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))||'|'||
                           DECODE(P_IDSTATUS,'C','02','00')      ||'|'||
                           P_CDSERIE                             ||'|'||
                           ''                                    ||'|'||
                           SUBSTR(LPAD(P_NRNOTAFISC,20,'0'),6,15)||'|'||
                           ''                                    ||'|'||
                           TO_CHAR(P_DTEMISSAO,'ddmmyyyy')       ||'|'||
                           TO_CHAR(P_DTENTRSAID,'ddmmyyyy')      ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(P_VRNOTAFISC,2),'9999999999990.00')),'.',',')                                                  ||'|'||
                           DECODE(NVL(V_COUNTPARC,0),0,'0',1)                                                                                         ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(P_CDFILIAL,P_NRLANCTONF,'D')+P_VRDESTOTIT,2),'9999999999990.00')),'.',',')       ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_PIS,'B','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,'B','1'),2),'9999999999990.00')),'.',',')   ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_COFINS,'I','1'),2),'9999999999990.00')),'.',',')   ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_PIS_RET_E,'I','1')+
                                                       VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_PIS_RET_S,'I','1'),2),'9999999999990.00')),'.',',')   ||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_COFINS_RET_E,'I','1')+
                                                       VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_COFINS_RET_S,'I','1'),2),'9999999999990.00')),'.',',')||'|'||
                           REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ISS_E,'I','1')+
                                                       VALOR_IMPOSTO_NFE(P_CDFILIAL,P_NRLANCTONF,P_ISS_S,'I','1'),2),'9999999999990.00')),'.',',') ||'|') AS REGA100,
                           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,'203'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),REG_A100.REGA100);

      V_PARTICIPANTE:= NULL;
      /*BUSCA ENDERECO DO CLIENTE*/
      IF P_IDORIGDESTNF = 'C' THEN
         FOR C_CLI IN(SELECT CDCLIENTE, CDPAIS
                       FROM ENDECLIE E
                      WHERE E.IDTPENDECLIE = 'P'
                        AND E.CDCLIENTE  = REG_A100.CD_PARTC)LOOP
            V_PARTICIPANTE:= C_CLI.CDCLIENTE;
            IF (P_IDENTRSAIDOP = 'E') THEN
               IF (C_CLI.CDPAIS <> P_CDPAIS) THEN
                  V_IDIMPORTACAO := '1';
               ELSE
                  V_IDIMPORTACAO := '0';
               END IF;
            ELSE
               V_IDIMPORTACAO := '';
            END IF;
         END LOOP;
      END IF;

      /*ENDERECO PARA FILIAL*/
      IF P_IDORIGDESTNF = 'L' THEN
         FOR C_FIL IN (SELECT CDFILIAL
                        FROM ENDEFILI E
                       WHERE IDTPENDEFILI = 'P'
                         AND E.CDFILIAL   = REG_A100.CD_PARTC)LOOP
            V_PARTICIPANTE:= C_FIL.CDFILIAL;
         END LOOP;
         V_IDIMPORTACAO := '0';
      END IF;

      IF P_IDORIGDESTNF = 'F' THEN
         FOR C_FOR IN(SELECT CDFORNECED, CDPAIS
                        FROM ENDEFORN E
                       WHERE E.IDTPENDEFORN = 'P'
                         AND E.CDFORNECED   = REG_A100.CD_PARTC)LOOP
            V_PARTICIPANTE:= C_FOR.CDFORNECED;
            IF (P_IDENTRSAIDOP = 'E') THEN
               IF (C_FOR.CDPAIS <> P_CDPAIS) THEN
                  V_IDIMPORTACAO := '1';
               ELSE
                  V_IDIMPORTACAO := '0';
               END IF;
            ELSE
               V_IDIMPORTACAO := '';
            END IF;
        END LOOP;
      END IF;

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

      IF (V_COD_PAR IS NULL) AND (P_IDORIGDESTNF||V_PARTICIPANTE 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, P_IDORIGDESTNF||V_PARTICIPANTE ,NULL,LTRIM(TO_CHAR(P_CONTEST,'000')));
      END IF;

      /*GRAVA OS SERVICOS DA NOTA FISCAL*/
      FOR REG_A170 IN (SELECT P.CDARVPROD                                                      ||'|'||
                              ''                                                               ||'|'|| /*DESCRICAO COMPLEMENTAR*/
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_PIS,'C'),2),'9999999999900.00')),'.',',')||'|'||
                              REPLACE(LTRIM(TO_CHAR(IP.VRDESCITEM,'9999999999900.00')),'.',',') AS PARTE0,
                              V_IDIMPORTACAO                                                    AS PARTE1,
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_PIS,'B','1'),2),'9999999999990.00')),'.',',') ||'|'||/*BASE CALCULO PIS*/
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_PIS,'A','1'),2),'990.00')),'.',',') ||'|'||/*ALIQUOTA PIS*/
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_PIS,'I','1'),2),'9999999999990.00')),'.',',') /*VR IMPOSTO PIS*/ AS PARTE2,
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_COFINS,'B','1'),2),'9999999999990.00')),'.',',') ||'|'||/*BASE CALCULO COFINS*/
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_COFINS,'A','1'),2),'990.00')),'.',',') ||'|'||/*ALIQUOTA COFINS*/
                              REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM_NFE(IP.CDFILIAL,IP.NRLANCTONF,IP.NRSEQITPROD,P_COFINS,'I','1'),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, IT.CDFISCOPERIT, IP.DSCDFISOPEIT, IT.CDTPCREDPICOITE, P.CDARVPROD,
                              IT.NRSEQITPROD, NVL(IT.CDNATUREC,R.CDNATUREC) AS CDNATUREC, IP.VRTOTAITEM,
                              CONTCTBL_SPED(P_IDENTRSAIDOP,P.CDPRODUTO,P_CDTIPOOPER,DECODE(P_IDENTRSAIDOP,'E',P_CDFILIRECE,P_CDFILIEMIT)) AS COD_CTA, IP.CDPRODNCMTRANS AS CDCLASFISC
                         FROM ITEMPRODUTO IP, PRODUTO P, RELACPRODITGEN R,
                              (SELECT DISTINCT IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.CDFISCOPERIT, IT.CDTPCREDPICOITE, IT.CDNATUREC
                                 FROM ITEMPROIMPOS IT, IMPOSTO IM
                                WHERE IT.CDFILIAL     = P_CDFILIAL
                                  AND IT.NRLANCTONF   = P_NRLANCTONF
                                  AND IM.CDIMPOSTONFE IN (P_ISS_E,P_ISS_S,P_ISS_RET_E,P_ISS_RET_S)
                                  AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                                  AND IT.NRITPROIMPOS = '1') IT
                        WHERE IT.NRLANCTONF   = IP.NRLANCTONF
                          AND IT.CDFILIAL     = IP.CDFILIAL
                          AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                          AND IP.CDPRODUTO    = P.CDPRODUTO
                          AND P.CDPRODUTO     = R.CDPRODUTO(+)
                          AND P_LIVROMAG      = R.CDLIVROMAG(+)
                          AND ROUND(IP.QTITEMPROD,5) > 0
                        ORDER BY IP.NRSEQITPROD)LOOP
         BEGIN
            SELECT MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,CASE WHEN I.VRIMPOSTOIT > 0 THEN LPAD(I.CDNATUBCALPCIT,2,'0') ELSE '' END,'' )) AS CDNATUBCCOFINS,
                   MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,CASE WHEN I.VRIMPOSTOIT > 0 THEN LPAD(I.CDNATUBCALPCIT,2,'0') ELSE '' END,'' )) 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,I.VRBASECALCIT,0)) AS VRBCPIS,
                   MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,I.VRBASECALCIT,0)) AS VRBCCOFINS,
                   MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,I.VRIMPOSTOIT,0)) AS VRCREDPIS,
                   MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,I.VRIMPOSTOIT,0)) AS VRCREDCOFINS,
                   MAX(DECODE(IM.CDIMPOSTONFE,P_PIS,I.VRALIQITEM,0)) AS ALIQPIS,
                   MAX(DECODE(IM.CDIMPOSTONFE,P_COFINS,I.VRALIQITEM,0)) AS ALIQCOFINS,
                   0 AS VRPISRET,
                   0 AS VRCOFINSRET
              INTO CDNATBCCOFINS, CDNATBCPIS,CSTCOFINS,CSTPIS, CDTPCREDPIS, CDTPCREDCOFINS, CDFISCALPIS, CDFISCALCOFINS,
                   VRBCPIS, VRBCCOFINS, VRCREDPIS, VRCREDCOFINS, VRALIQPIS, VRALIQCOFINS, VRPISRET, VRCOFINSRET
              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 I.NRSEQITPROD  = REG_A170.NRSEQITPROD
               AND I.CDIMPOSTO    = IM.CDIMPOSTO
               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
                  CSTPIS       :='08';
                  CSTCOFINS    :='08';
               ELSE
                  CSTPIS       :='74';
                  CSTCOFINS    :='74';
               END IF;
               CDNATBCCOFINS:=NULL;
               CDNATBCPIS   :=NULL;
         END;

         V_SEQITPROD:= NVL(V_SEQITPROD,0) +1;


         V_REG:= '|A170|'||LPAD(V_SEQITPROD,3,'0')||'|'||REG_A170.PARTE0||'|'||CDNATBCPIS||'|'||REG_A170.PARTE1||'|'||CSTPIS||'|'||REG_A170.PARTE2||'|'||CSTCOFINS||'|'||REG_A170.PARTE3;

         V_CONTREGA := NVL(V_CONTREGA,0)+1;
         INSERT INTO ARQMAGPISCOFINS
            (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
         VALUES
            (P_NRSEQARQMAG,'203'||LTRIM(TO_CHAR(P_CONTEST,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REG);
         V_UNID    := null;
         V_COD_NAT := null;
         V_PRODUTO := NULL;

         /*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*/ /* CDFISCALPIS = '1' AND VRALIQPIS > 0*/
            IF (CSTPIS IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66','98')) AND (VRCREDPIS>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
                  ('A170'||P_CDFILIAL||P_NRLANCTONF,'M100',V_TMPBLCM,CDTPCREDPIS,VRBCPIS,VRALIQPIS,'P',VRCREDPIS,CSTPIS,P_CDFILIRECE,CDNATBCPIS);
            END IF;

            /*INSERE O VALOR REFERENTE AO COFINS ENTRADA*/ /*CDFISCALCOFINS = '1' AND VRALIQCOFINS > 0*/
            IF (CSTCOFINS IN ('50','51','52','53','54','55','56','60','61','62','63','64','65','66','98')) AND (VRCREDCOFINS>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
                  ('A170'||P_CDFILIAL||P_NRLANCTONF,'M500',V_TMPBLCM,CDTPCREDCOFINS,VRBCCOFINS,VRALIQCOFINS,'P',VRCREDCOFINS,CSTCOFINS,P_CDFILIRECE,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
               ('A170'||P_CDFILIAL||P_NRLANCTONF,'M200',V_TMPBLCM,CDTPCREDPIS,VRBCPIS,VRALIQPIS,'P',VRCREDPIS,CSTPIS,P_CDFILIEMIT,CDNATBCPIS,VRPISRET,REG_A170.VRTOTAITEM,REG_A170.CDNATUREC,LTRIM(TO_CHAR(P_CONTEST,'000')),P_NRINSJUREST,REG_A170.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
               ('A170'||P_CDFILIAL||P_NRLANCTONF,'M600',V_TMPBLCM,CDTPCREDCOFINS,VRBCCOFINS,VRALIQCOFINS,'P',VRCREDCOFINS,CSTCOFINS,P_CDFILIEMIT,CDNATBCCOFINS,VRCOFINSRET,REG_A170.VRTOTAITEM,REG_A170.CDNATUREC,LTRIM(TO_CHAR(P_CONTEST,'000')),P_NRINSJUREST,REG_A170.COD_CTA);
         END IF;

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

         IF (V_UNID IS NULL) AND (REG_A170.SGUNIDADE IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB,CDCAMPO3)
            VALUES
               ('0190', V_SEQUPROD, REG_A170.SGUNIDADE,NULL,LTRIM(TO_CHAR(P_CONTEST,'000')),'A');
         END IF;

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

         IF (V_PRODUTO IS NULL) AND (REG_A170.CDARVPROD IS NOT NULL) THEN
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
            INSERT INTO TMP_REGDADOSSPED_PIS
               (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB,CDCAMPO3,CDCAMPO4)
            VALUES
               ('0200', V_SEQUPROD, REG_A170.CDARVPROD,NULL,LTRIM(TO_CHAR(P_CONTEST,'000')),'A',REG_A170.CDCLASFISC);
         END IF;

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

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

   V_IDA010:= NULL;
   SELECT MAX(CDCHAVEREG)
     INTO V_IDA010
     FROM TMP_REGDADOSSPED_PIS
    WHERE CDREGISTRO = 'A010'
      AND CDCHAVEREG = P_NRINSJUREST;

   /*IDENTIFICACAO DO ESTABELECIMENTO*/
   IF V_IDA010 IS NULL THEN /*P_IDNOVOEST*/
      V_REG_A010 := '|A010|'||P_NRINSJUREST||'|';
      V_CONTREGA := NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'203'||LTRIM(TO_CHAR(P_CONTEST,'000')),V_REG_A010);

      INSERT INTO TMP_REGDADOSSPED_PIS
         (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
      VALUES
         ('A010', V_SEQUPROD, P_NRINSJUREST ,NULL,LTRIM(TO_CHAR(P_CONTEST,'000')));
   END IF;

   /*LPAD(TO_CHAR(V_CONTREGA),20,'0')||'|'||LPAD(TO_CHAR(V_SEQUPROD),20,'0')||'|'||LPAD(TO_CHAR(V_TMPBLCM),20,'0'))*/
   RETURN(LPAD(TO_CHAR(V_CONTREGA),20,'0')||'|'||LPAD(TO_CHAR(V_SEQUPROD),20,'0')||'|'||LPAD(TO_CHAR(V_TMPBLCM),20,'0'));
   /*RETURN(V_CONTREGA);*/
END;
/