CREATE OR REPLACE FUNCTION ARQUIVO_GIA_MS_04(
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA  IN NOTAFISCAL.CDINSCESTA%TYPE,
   P_CDIMPOSTO   IN IMPOSTO.CDIMPOSTO%TYPE,
   P_DTINICIAL   IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_TPDEC       IN VARCHAR,
   P_PDEC        IN VARCHAR,
   P_CONTADOR    IN VARCHAR,
   P_SEQMAGNET   IN VARCHAR)
RETURN VARCHAR
AS
   V_CONTADOR            NUMBER;
   V_INSERT              VARCHAR2(100);
   V_MESINI              VARCHAR2(2);
   V_DADOS               VARCHAR(1000);
   V_VRBASEX               FLOAT;
   V_VRIMPOSTOX          FLOAT;
   V_VRALIQNFX           FLOAT;
   V_VRBASEY               FLOAT;
   V_VRIMPOSTOY          FLOAT;
   V_VRALIQNFY           FLOAT;
BEGIN
   V_CONTADOR := NVL(P_CONTADOR,0);

   V_VRBASEX       := 0;
   V_VRIMPOSTOX  := 0;
   V_VRALIQNFX   := 0;
   V_VRBASEY       := 0;
   V_VRIMPOSTOY  := 0;
   V_VRALIQNFY   := 0;

   IF P_PDEC = '2' THEN
      V_MESINI := '00';
   ELSE
      V_MESINI := TO_CHAR(P_DTINICIAL,'MM');
   END IF;
   V_INSERT := SUBSTR(RPAD(REPLACE(REPLACE(REPLACE(NVL(P_CDINSCESTA, ' '),'.',''),'-',''),'ISENT','ISENTO'),9,' '),1,9)
               ||TO_CHAR(P_DTINICIAL,'YYYY')
               ||V_MESINI
               ||TO_CHAR(P_DTFINAL,'MM')
               ||P_TPDEC
               ||'04';


   /* BUSCA O VALOR TOTAL DO DIFERENCIAL DE ALIQUOTA POR ALIQUOTA */
   BEGIN
      SELECT SUM(ROUND(VRCONTABILNF,2)) AS VRBASE, SUM(ROUND(VRIMPOSTONF,2)) AS VRIMPOSTO, VRALIQNF
        INTO V_VRBASEX, V_VRIMPOSTOX, V_VRALIQNFX
        FROM IMPOSTONF I, MAPAAPURAC M
       WHERE I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
         AND M.CDIMPOSTO = P_CDIMPOSTO
         AND M.CDEMPRESA = P_CDEMPRESA
         AND M.DTINIPERIODO >= P_DTINICIAL
         AND M.DTFIMPERIODO <= P_DTFINAL
         AND M.CDINSCESTA = P_CDINSCESTA
         AND IDIMPOSTONF = '3'
         AND VRALIQNF    = 10
       GROUP BY VRALIQNF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRBASEX := 0;
         V_VRIMPOSTOX := 0;
         V_VRALIQNFX := 0;
   END;

   BEGIN
      SELECT SUM(ROUND(VRCONTABILNF,2)) AS VRBASE, SUM(ROUND(VRIMPOSTONF,2)) AS VRIMPOSTO, MAX(VRALIQNF) AS VRALIQNF
        INTO V_VRBASEY, V_VRIMPOSTOY, V_VRALIQNFY
        FROM IMPOSTONF I, MAPAAPURAC M
       WHERE I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
         AND M.CDIMPOSTO = P_CDIMPOSTO
         AND M.CDEMPRESA = P_CDEMPRESA
         AND M.DTINIPERIODO >= P_DTINICIAL
         AND M.DTFIMPERIODO <= P_DTFINAL
         AND M.CDINSCESTA = P_CDINSCESTA
         AND IDIMPOSTONF = '3'
         AND VRALIQNF    <> 10;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRBASEY := 0;
         V_VRIMPOSTOY := 0;
         V_VRALIQNFY := 0;
   END;

   IF (V_VRALIQNFX > 0) OR (V_VRALIQNFY > 0) THEN
       V_DADOS := LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VRALIQNFX,0),2),'990.00')),'.',''),5,0)
                  ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VRALIQNFY,0),2),'990.00')),'.',''),5,0)
                  ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VRBASEX,0),2),'9999999999990.00')),'.',''),15,0)
                  ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VRBASEY,0),2),'9999999999990.00')),'.',''),15,0)
                  ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VRIMPOSTOX,0),2),'9999999999990.00')),'.',''),15,0)
                  ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VRIMPOSTOY,0),2),'9999999999990.00')),'.',''),15,0);

       V_CONTADOR := NVL(V_CONTADOR,0)+1;
       INSERT INTO ARQMAGNETICO
          (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
          (LPAD(V_CONTADOR, 10, '0'),V_INSERT||V_DADOS,P_SEQMAGNET);
   END IF;

   V_DADOS := NULL;

   RETURN (TO_CHAR(LPAD(V_CONTADOR, 10, '0')));
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_GIA_MS_04','5.33.000'); END;
/
