CREATE OR REPLACE FUNCTION SPED_PIS_COFINS_BLOCOP(
      P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
      P_NRSEQARQMAG  IN ARQMAGPISCOFINS.NRSEQARQMAG%TYPE,
      P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
      P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
      P_SEQUPROD     IN NUMBER,
      P_CONTEST      IN NUMBER,
      P_CONTBLC0     OUT NUMBER) RETURN VARCHAR
AS
   V_INSJUREST     VARCHAR2(20);
   V_CONTREGA      NUMBER;
   V_CONTREG0      NUMBER;
   V_SEQUPROD      NUMBER;
   OLD_CDESTAB     NUMBER;
   V_CONESTGRAVAR  NUMBER;
   V_IDP010        CHAR;
   V_REG_P010      VARCHAR2(100);
   V_REGISTRO      VARCHAR(500);
   V_REG           VARCHAR(500);
   V_CONEST        NUMBER;
   V_COD_CC         VARCHAR2(20);
   V_SEQCCTBL       NUMBER;
BEGIN
   V_CONEST   := P_CONTEST;
   V_CONTREG0 := P_CONTBLC0;
   V_SEQUPROD := P_SEQUPROD;

  /*VERIFICA SE O ESTABELECIMENTO JA FOI INSERIDO*/
   V_INSJUREST:= NULL;
   SELECT MAX(CDCHAVEREG), MAX(CDESTAB)
     INTO V_INSJUREST, OLD_CDESTAB
     FROM TMP_REGDADOSSPED_PIS TMP, EMPRESA E
    WHERE CDREGISTRO  = '0140'
      AND CDCHAVEREG  = E.NRINSJUREMPR
      AND E.CDEMPRESA = P_CDEMPRESA;

   IF V_INSJUREST IS NULL THEN
      V_CONEST:= NVL(V_CONEST,0) + 1;
      BEGIN
         SELECT '|0140'         ||'|'||
                ''              ||'|'||
                E.NMRAZSOCEMPR  ||'|'||
                REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\','') ||'|'||
                E.SGESTADO      ||'|'||
                ''              ||'|'||
                M.CDMUNICIBGE   ||'|'||
                 ''             ||'|'||
                 ''             ||'|' ,REPLACE(REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.',''),'/',''),'-',''),'\','')
           INTO V_REG, V_INSJUREST
           FROM MUNICIPIO M, EMPRESA E
          WHERE E.CDEMPRESA   = P_CDEMPRESA
            AND E.CDPAIS      = M.CDPAIS
            AND E.SGESTADO    = M.SGESTADO
            AND E.CDMUNICIPIO = M.CDMUNICIPIO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_REG:= NULL;
      END;

      V_CONTREGA := NVL(V_CONTREGA,0)+1;
      V_CONTREG0 := NVL(V_CONTREG0,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'104'||LTRIM(TO_CHAR(V_CONEST,'000')),V_REG);

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

   /*-----------------------REGISTRO P100 CONTRIBUICAO PREVIDENCIARIA SOBRE A RECEITA BRUTA -----------------*/
   FOR C_P100 IN (SELECT '|P100'                                                                       ||'|'||
                         TO_CHAR(P_DTINICIAL,'DDMMYYYY')                                               ||'|'||
                         TO_CHAR(P_DTFINAL,'DDMMYYYY')                                                 ||'|'||
                         REPLACE(LTRIM(TO_CHAR(VRTOTABRUT - VRENCARGO,'9999999999900.00')),'.',',')    ||'|'||
                         '00000025'                                                                    ||'|'||
                         REPLACE(LTRIM(TO_CHAR(VRTOTABRUT - VRENCARGO - VREXPORTACAO,'9999999999900.00')),'.',',')    ||'|'||
                         '0,00'                                                                        ||'|'||
                         REPLACE(LTRIM(TO_CHAR(VRTOTABRUT - VRENCARGO - VREXPORTACAO,'9999999999900.00')),'.',',')    ||'|'||
                         '4,5000'                                                                      ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ROUND((VRTOTABRUT-VRENCARGO-VREXPORTACAO)*(4.5/100),2),'9999999999900.00')),'.',',') ||'|'||
                         COD_CTA      ||'|'||
                         ''           ||'|' AS REGISTRO,
                         (VRTOTABRUT - VRENCARGO) AS VRFATURAMENTO, COD_CTA,
                         (VRTOTABRUT - VRENCARGO - VREXPORTACAO) AS VRBASECALC,
                         ROUND((VRTOTABRUT-VRENCARGO-VREXPORTACAO)*(4.5/100),2) AS VRTOTCONTR
                    FROM (SELECT SUM(NVL(VRNOTAFISC,0)) AS VRTOTABRUT,
                                 SUM((SELECT NVL(SUM(VRITEMENCA),0)
                                        FROM ENCARGO E, ITEMENCARGO ITE
                                       WHERE ITE.CDENCARGO = E.CDENCARGO
                                         AND N.CDFILIAL    = ITE.CDFILIAL
                                         AND N.NRLANCTONF  = ITE.NRLANCTONF
                                         AND E.IDENCARGO   = 'A')) AS VRENCARGO,
                                 SUM((SELECT NVL(SUM(N.VRNOTAFISC),0)
                                        FROM ENDECLIE E
                                       WHERE E.CDPAIS <> '0055'
                                         AND E.IDTPENDECLIE = 'P'
                                         AND E.CDCLIENTE = N.CDCLIENTE)) AS VREXPORTACAO,
                                 CONTCTBL_SPED(N.IDENTRSAIDOP,'',N.CDTIPOOPER,N.CDFILIEMIT) AS COD_CTA
                            FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, FILIAL F, TIPOOPERAC T
                           WHERE N.IDENTRSAIDOP = 'S'
                             AND N.IDSTATUS IN ('E','A','C')
                             AND (N.IDSISTEMA NOT IN ('REC','FIS') OR N.IDSISTEMA IS NULL)
                             AND N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
                             AND N.CDEMPRESA = P_CDEMPRESA
                             AND N.CDFILIAL  = I.CDFILIAL
                             AND N.NRLANCTONF = I.NRLANCTONF
                             AND I.NRSEQUMAPAPU IS NOT NULL
                             AND I.CDIMPOSTO = IM.CDIMPOSTO
                             AND IM.CDIMPOSTONFE = '50'
                             AND T.IDENTRSAIDOP = N.IDENTRSAIDOP
                             AND T.CDTIPOOPER   = N.CDTIPOOPER
                             AND F.CDFILIAL     = DECODE(N.IDENTRSAIDOP,'E', N.CDFILIRECE, N.CDFILIEMIT)
                             AND ((F.IDCUMUPISCOFIL = 'A' AND T.IDCUMUPISCOFIL IN ('S','N')) OR F.IDCUMUPISCOFIL <> 'A')
                           GROUP BY CONTCTBL_SPED(N.IDENTRSAIDOP,'',N.CDTIPOOPER,N.CDFILIEMIT)))LOOP

      V_CONTREGA := NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'704'||LTRIM(TO_CHAR(V_CONESTGRAVAR,'000'))||'01'||LPAD(RTRIM(V_CONTREGA),22,'0'),C_P100.REGISTRO);

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

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

      /*REGISTRO P200*/
      V_REGISTRO:= '|P200'                                                               ||'|'||
                   TO_CHAR(P_DTINICIAL,'MMYYYY')                                         ||'|'||
                   REPLACE(LTRIM(TO_CHAR(C_P100.VRTOTCONTR,'9999999999900.00')),'.',',') ||'|'||
                   '0,00'                                                                ||'|'||
                   '0,00'                                                                ||'|'||
                   REPLACE(LTRIM(TO_CHAR(C_P100.VRTOTCONTR,'9999999999900.00')),'.',',') ||'|'||
                   '298501'                                                              ||'|';

      V_CONTREGA := NVL(V_CONTREGA,0)+1;
      INSERT INTO ARQMAGPISCOFINS
         (NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
      VALUES
         (P_NRSEQARQMAG,'704'||LTRIM(TO_CHAR(V_CONESTGRAVAR,'000'))||'04'||LPAD(RTRIM(V_CONTREGA),22,'0'),V_REGISTRO);

      /*------------------------------------REGISTRO 0145-------------------------------------------------------------------*/
      INSERT INTO TMP_REGDADOSSPED_PIS
        (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2, CDESTAB)
      VALUES
        ('0145', V_SEQUPROD, V_INSJUREST, C_P100.VRFATURAMENTO, C_P100.VRBASECALC, LTRIM(TO_CHAR(V_CONESTGRAVAR,'000')));
   END LOOP;

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

   /*IDENTIFICACAO DO ESTABELECIMENTO*/
   IF V_IDP010 IS NULL THEN
      V_REG_P010 := '|P010|'||V_INSJUREST||'|';
      V_CONTREGA := NVL(V_CONTREGA,0)+1;

      INSERT INTO ARQMAGPISCOFINS(NRSEQARQMAG,NRORDREGARQMAG,DSREGARQMAG)
        VALUES(P_NRSEQARQMAG,'703'||LTRIM(TO_CHAR(V_CONESTGRAVAR,'000')),V_REG_P010);

      INSERT INTO TMP_REGDADOSSPED_PIS
        (CDREGISTRO, CDCONTREGI, CDCHAVEREG,CDCAMPO1,CDESTAB)
      VALUES
        ('P010', V_SEQUPROD, V_INSJUREST ,NULL,LTRIM(TO_CHAR(V_CONESTGRAVAR,'000')));
   END IF;

   P_CONTBLC0:= V_CONTREG0;
   RETURN(LPAD(TO_CHAR(V_CONTREGA),20,'0')||'|'||LPAD(TO_CHAR(V_SEQUPROD),20,'0')||'|'||LPAD(TO_CHAR(V_CONTREG0),20,'0'));
END;
/