CREATE OR REPLACE FUNCTION REGISTRO88_SINTEGRA(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_REGISTRO     IN VARCHAR2,
   P_NRSEQUENCIAL IN NOTAFISCAL.NRLANCTONF%TYPE) RETURN VARCHAR2
AS
   V_CNPJ           VARCHAR2(14);
   V_ENTRADA        VARCHAR2(10);
   V_SAIDA          VARCHAR2(10);
   V_LINHA_88SMS    VARCHAR2(126);
   V_LINHA_88SME    VARCHAR2(126);
   V_CONTADOR       NUMBER;
   V_SGESTADO       VARCHAR2(02);
BEGIN
    BEGIN
      SELECT IE.SGESTADO
        INTO V_SGESTADO
        FROM INSCRESTAD IE
       WHERE IE.CDEMPRESA  = P_CDEMPRESA
         AND IE.CDINSCESTA = P_CDINSCESTA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_SGESTADO := NULL;
   END;

   --ESTADOS QUE GERAM O REGISTRO SEM MOVIMENTO BA/MG/MS/RJ/RN/SP
   IF V_SGESTADO IN ('BA','MG','MS','RJ','RN','SP') THEN
      V_CONTADOR := 0;


   BEGIN
      SELECT MAX(I.NRSEQUMAPAPU)
        INTO V_ENTRADA
        FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M
       WHERE M.CDEMPRESA     = P_CDEMPRESA
         AND M.CDINSCESTA    = P_CDINSCESTA
         AND M.DTINIPERIODO  = P_DTINICIAL
         AND M.DTFIMPERIODO  = P_DTFINAL
         AND M.CDIMPOSTO     = P_CDIMPOSTO
         AND I.CDIMPOSTO     = P_CDIMPOSTO
         AND I.CDFILIAL      = N.CDFILIAL
         AND I.NRLANCTONF    = N.NRLANCTONF
         AND I.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
         AND N.IDENTRSAIDOP  = 'E'
         AND N.IDSTATUS      IN ('A','E')
         AND N.CDMODDOCFISC  IN ('01', '1A', '04', '06', '21', '22', '55')
         AND NVL(I.IDIMPOSTONF,'1') <> '3';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_ENTRADA := NULL;
   END;

   IF V_ENTRADA IS NULL THEN
	   BEGIN
         SELECT MAX(N.NRSEQUMAPAPUSN)
           INTO V_ENTRADA
           FROM NOTAFISCAL N, MAPAAPURACSN M
          WHERE M.CDEMPRESA      = P_CDEMPRESA
            AND M.DTINIPERIODO   = P_DTINICIAL
            AND M.DTFIMPERIODO   = P_DTFINAL
            AND N.NRSEQUMAPAPUSN = M.NRSEQUMAPAPU
            AND N.IDENTRSAIDOP   = 'E'
            AND N.IDSTATUS       IN ('A','E')
            AND N.CDMODDOCFISC   IN ('01', '1A', '04', '06', '21', '22', '55');
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_ENTRADA := NULL;
      END;
   END IF;

   BEGIN
      SELECT MAX(IDSAIDA)
        INTO V_SAIDA
        FROM (SELECT 'S' AS IDSAIDA
                FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M
               WHERE M.CDEMPRESA    = P_CDEMPRESA
                 AND M.CDINSCESTA   = P_CDINSCESTA
                 AND M.DTINIPERIODO = P_DTINICIAL
                 AND M.DTFIMPERIODO = P_DTFINAL
                 AND M.CDIMPOSTO    = P_CDIMPOSTO
                 AND I.CDIMPOSTO    = P_CDIMPOSTO
                 AND I.CDFILIAL     = N.CDFILIAL
                 AND I.NRLANCTONF   = N.NRLANCTONF
                 AND I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
                 AND N.IDENTRSAIDOP = 'S'
                 AND N.IDSTATUS     IN ('A','E')
                 AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                 AND NVL(I.IDIMPOSTONF,'1') <> '3'
               UNION ALL
              SELECT 'S' AS IDSAIDA
                FROM VENDA V, FILIAL FI
               WHERE FI.CDEMPRESA      = P_CDEMPRESA
                 AND FI.CDINSCESTA     = P_CDINSCESTA
                 AND V.CDFILIAL        = FI.CDFILIAL
                 AND V.IDSITUVENDA     = 'O'
                 AND TRUNC(DTEMISSAONFCE) BETWEEN P_DTINICIAL AND P_DTFINAL
                 AND V.NRNOTAFISCALCE  IS NOT NULL
                 AND V.IDAPROVACAONFCE = 'A'
                 AND V.IDTPEMISVEND    = 'N'
               UNION ALL
              SELECT 'S' AS IDSAIDA
                FROM FILIAL FI, REDUCAOZ R
               WHERE FI.CDEMPRESA      = P_CDEMPRESA
                 AND FI.CDINSCESTA     = P_CDINSCESTA
                 AND FI.CDFILIAL       = R.CDFILIAL
                 AND R.DTREDUCAOZ BETWEEN P_DTINICIAL AND P_DTFINAL
                 AND R.IDSTATUS        = 'A');
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_SAIDA := NULL;
   END;

   IF V_SAIDA IS NULL THEN
		BEGIN
			SELECT MAX(IDSAIDA)
			  INTO V_SAIDA
			  FROM (SELECT 'S' AS IDSAIDA
                   FROM NOTAFISCAL N, MAPAAPURACSN M
                  WHERE M.CDEMPRESA    = P_CDEMPRESA
                    AND M.DTINIPERIODO = P_DTINICIAL
                    AND M.DTFIMPERIODO = P_DTFINAL
                    AND N.NRSEQUMAPAPUSN = M.NRSEQUMAPAPU
                    AND N.IDENTRSAIDOP = 'S'
                    AND N.IDSTATUS     IN ('A','E')
                    AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55'));
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_SAIDA := NULL;
		END;
	END IF;

   SELECT MAX(LPAD(REPLACE(REPLACE(REPLACE(NRINSJURIEST,'-',''),'.',''),'/',''), 14, '0'))
     INTO V_CNPJ
     FROM INSCRESTAD
    WHERE CDEMPRESA = P_CDEMPRESA
      AND CDINSCESTA = P_CDINSCESTA;

   IF V_ENTRADA IS NULL THEN
      V_LINHA_88SME := '88SME'||
                       V_CNPJ||
                       RPAD('SEM MOVIMENTO DE ENTRADAS',34,' ')||
                       RPAD(' ', 73, ' ');
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('19'||LPAD(RTRIM(V_CONTADOR), 8, '0'), V_LINHA_88SME, P_NRSEQUENCIAL);
      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
   END IF;

   IF V_SAIDA IS NULL THEN
      V_LINHA_88SMS := '88SMS'||
                       V_CNPJ||
                       RPAD('SEM MOVIMENTO DE SAIDAS',34,' ')||
                       RPAD(' ', 73, ' ');
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('19'||LPAD(RTRIM(V_CONTADOR), 8, '0'), V_LINHA_88SMS, P_NRSEQUENCIAL);
      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
   END IF;
    END IF;

   IF SUBSTR(P_REGISTRO,11,1) = 'S' THEN
   V_CONTADOR := 0;
   FOR C_REG88STES IN (SELECT ('88'||
                              'STES'||
                              REPLACE(REPLACE(REPLACE(P.NRINSJUREMPR,'-',''),'.',''),'/','')||
                              TO_CHAR(I.DTINVEFISCAL, 'YYYYMMDD')||
                              LPAD(I.CDPRODUTO,60,' ')||
                              LPAD(REPLACE(TRIM(TO_CHAR(I.QTESTINVFISC,'9999999999.000')),'.',''),13,'0')||
                              LPAD(REPLACE(TRIM(TO_CHAR((ROUND(VRTOTINVFISC)* 0.18),'999999999.000')),'.',''),12,'0')||
                              LPAD(REPLACE(TRIM(TO_CHAR((ROUND(VRTOTINVFISC)* 0.18),'999999999.000')),'.',''),12,'0')||
                              '0') AS DSARQUMAG
                         FROM INVENTFISC I , (SELECT DISTINCT PR.CDPRODESTO, NF.CDEMPRESA, NF.CDINSCESTA, EP.NRINSJUREMPR
                                                FROM NOTAFISCAL NF, ITEMPROIMPOS II, ITEMPRODUTO IP, PRODUTO PR, EMPRESA EP
                                               WHERE NF.CDFILIAL     = II.CDFILIAL
                                                 AND NF.NRLANCTONF   = II.NRLANCTONF
                                                 AND NF.CDFILIAL     = IP.CDFILIAL
                                                 AND NF.NRLANCTONF   = IP.NRLANCTONF
                                                 AND NF.CDEMPRESA    = EP.CDEMPRESA
                                                 AND NF.CDEMPRESA    = P_CDEMPRESA
                                                 AND NF.CDINSCESTA   = P_CDINSCESTA
                                                 AND II.NRSEQITPROD  = IP.NRSEQITPROD
                                                 AND IP.CDPRODUTO    = PR.CDPRODUTO
                                                 AND II.CDIMPOSTO    IN ('00','80')
                                                 AND NF.IDENTRSAIDOP = 'E'
                                                 AND NF.IDSTATUS     IN ('A','E')
                                                 AND II.CDSITUTRIBIT IN ('010','030','060','070')
                                                 AND NF.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL) P
                        WHERE I.CDPRODUTO    = P.CDPRODESTO
                          AND I.CDEMPRESA    = P.CDEMPRESA
                          AND I.CDINSCESTA   = I.CDINSCESTA
                          AND I.DTINVEFISCAL = TO_DATE(P_DTINICIAL)-1) LOOP

      INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
            ('20'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REG88STES.DSARQUMAG, P_NRSEQUENCIAL);
      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
   END LOOP;

   V_CONTADOR := 0;
   FOR C_REG88STITNF IN (SELECT ( '88'||
                                  'STITNF'||
                                  REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'-',''),'.',''),'/','')||
                                  CDMODDOCFISC||
                                  LPAD(CDSERIEENTR,3,' ')||
                                  LPAD(NRNOTAFISC,9,'0')||
                                  CDFISCOPERIT||
                                  CDSITUTRIBIT||
                                  SUBSTR(NRSEQITPROD,2,4)||
                                  TO_CHAR(DTENTRSAID, 'YYYYMMDD')||
                                  LPAD(CDPRODESTO,60,' ')||
                                  LPAD(REPLACE(TRIM(TO_CHAR(QTITEMPROD,'99999999.000')),'.',''),11,'0')||
                                  LPAD(REPLACE(TRIM(TO_CHAR(VRTOTAITEM,'9999999999.00')),'.',''),11,'0')||
                                  LPAD(REPLACE(TRIM(TO_CHAR(VRDESCITEM,'9999999999.00')),'.',''),12,'0')||
                                  LPAD(REPLACE(TRIM(TO_CHAR(BASEICMS,'9999999999.00')),'.',''),12,'0')||
                                  LPAD(REPLACE(TRIM(TO_CHAR(BASEST,'9999999999.00')),'.',''),12,'0')||
                                  LPAD(REPLACE(TRIM(TO_CHAR(ALIQST,'99.00')),'.',''),4,'0')||
                                  LPAD(REPLACE(TRIM(TO_CHAR(ALIQICMS,'99.00')),'.',''),4,'0')||
                                  '000000000000') AS DSARQUMAG
                            FROM (SELECT EP.NRINSJUREMPR, NF.CDMODDOCFISC, NF.CDSERIEENTR, NF.NRNOTAFISC,
                                         II.CDFISCOPERIT, II.CDSITUTRIBIT, II.NRSEQITPROD, NF.DTENTRSAID,
                                         PR.CDPRODESTO, IP.QTITEMPROD, IP.VRTOTAITEM, IP.VRDESCITEM,
                                         SUM(CASE WHEN II.CDIMPOSTO = '00' THEN II.VRBASECALCIT ELSE 0 END) AS BASEICMS,
                                         SUM(CASE WHEN II.CDIMPOSTO = '80' THEN II.VRBASECALCIT ELSE 0 END) AS BASEST,
                                         SUM(CASE WHEN II.CDIMPOSTO = '80' THEN II.VRALIQITEM ELSE 0 END) AS ALIQST,
                                         SUM(CASE WHEN II.CDIMPOSTO = '00' THEN II.VRALIQITEM ELSE 0 END) AS ALIQICMS
                                    FROM NOTAFISCAL NF, ITEMPROIMPOS II, ITEMPRODUTO IP, PRODUTO PR, EMPRESA EP
                                   WHERE NF.CDFILIAL     = II.CDFILIAL
                                     AND NF.NRLANCTONF   = II.NRLANCTONF
                                     AND NF.CDFILIAL     = IP.CDFILIAL
                                     AND NF.NRLANCTONF   = IP.NRLANCTONF
                                     AND NF.CDEMPRESA    = EP.CDEMPRESA
                                     AND NF.CDEMPRESA    = P_CDEMPRESA
                                     AND NF.CDINSCESTA   = P_CDINSCESTA
                                     AND II.NRSEQITPROD  = IP.NRSEQITPROD
                                     AND IP.CDPRODUTO    = PR.CDPRODUTO
                                     AND II.CDIMPOSTO    IN ('00','80')
                                     AND NF.IDENTRSAIDOP = 'E'
                                     AND NF.IDSTATUS     IN ('A','E')
                                     AND II.CDSITUTRIBIT IN ('010','030','060','070')
                                     AND NF.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL
                                GROUP BY PR.CDPRODESTO, EP.NRINSJUREMPR, NF.CDMODDOCFISC, NF.CDSERIEENTR,
                                         NF.NRNOTAFISC, II.CDFISCOPERIT, II.CDSITUTRIBIT, II.NRSEQITPROD,
                                         IP.QTITEMPROD, IP.VRTOTAITEM, IP.VRDESCITEM, NF.DTENTRSAID
                                ORDER BY DTENTRSAID, NRNOTAFISC, NRSEQITPROD)) LOOP
      INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
            ('21'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REG88STITNF.DSARQUMAG, P_NRSEQUENCIAL);
      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
   END LOOP;
   END IF;

   RETURN('');
END;
/