CREATE OR REPLACE FUNCTION ARQUIVO_GIA_MS_01(
   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_MESINI         VARCHAR2(2);
   V_CONTADOR       NUMBER;
   V_INSERT         VARCHAR2(100);
BEGIN
   V_CONTADOR := NVL(P_CONTADOR,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
               ||'01';

   FOR C_REGISTRO_01 IN(
       SELECT LPAD(NVL(T.CDFISCOPERNF,0),4,0)
              ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VRALIQUOTA,0),2),'990.00')),'.',''),5,0)
              ||LPAD(NVL(T.SGESTADO,' '),2,' ')
              ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VRCONTABIL,0)),2),'9999999999990.00')),'.',''),15,0)
              ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VRBASECALC,0)),2),'9999999999990.00')),'.',''),15,0)
              ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VRIMPOSTONF,0)),2),'9999999999990.00')),'.',''),15,0)
              ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VRINSOUTRAS,0)),2),'9999999999990.00')),'.',''),15,0)
              ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VROUTRAS,0)),2),'9999999999990.00')),'.',''),15,0)
              ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VRBASECALCSUBS,0)),2),'9999999999990.00')),'.',''),15,0)
              ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VRIMPSUBS,0)),2),'9999999999990.00')),'.',''),15,0) AS REGISTRO_01
         FROM(
              SELECT I.CDFISCOPERNF AS CDFISCOPERNF,
                     E.SGESTADO AS SGESTADO,
                     I.VRALIQNF AS VRALIQUOTA,
                     SUM(NVL(I.VRCONTABILNF,0)) AS VRCONTABIL,
                     SUM(NVL(VRBASECALCNF,0)) AS VRBASECALC,
                     SUM(NVL(I.VRIMPOSTONF,0)) AS VRIMPOSTONF,
                     SUM(DECODE(I.CDFISCALNF,'2', I.VRCONTABILNF, 0)) AS VRINSOUTRAS,
                     SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)) AS VROUTRAS,
                     SUM(VALOR_IMPOSTO_NF(I.CDFILIAL, I.NRLANCTONF, '80', 'B')) AS VRBASECALCSUBS,
                     SUM(VALOR_IMPOSTO_NF(I.CDFILIAL, I.NRLANCTONF, '80', 'I')) AS VRIMPSUBS
                FROM IMPOSTONF I,NOTAFISCAL N, MAPAAPURAC M,CLIENTE C,ENDECLIE E,IMPOSTO P
               WHERE M.CDEMPRESA = P_CDEMPRESA
                 AND M.CDINSCESTA = P_CDINSCESTA
                 AND M.CDIMPOSTO = P_CDIMPOSTO
                 AND M.DTINIPERIODO >= P_DTINICIAL
                 AND M.DTFIMPERIODO <= P_DTFINAL
                 AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                 AND M.CDIMPOSTO = P.CDIMPOSTONFE
                 AND P.CDIMPOSTO = I.CDIMPOSTO
                 AND I.CDFILIAL = N.CDFILIAL
                 AND I.NRLANCTONF = N.NRLANCTONF
                 AND N.CDCLIENTE = C.CDCLIENTE
                 AND C.CDCLIENTE = E.CDCLIENTE
                 AND E.IDTPENDECLIE = 'P'
                 AND IDORIGDESTNF = 'C'
                 AND NVL(IDIMPOSTONF,'1') <> '3'
               GROUP BY I.CDFISCOPERNF,E.SGESTADO,I.VRALIQNF
               UNION ALL
              SELECT I.CDFISCOPERNF AS CDFISCOPERNF,
                     E.SGESTADO AS SGESTADO,
                     I.VRALIQNF AS VRALIQUOTA,
                     SUM(NVL(I.VRCONTABILNF,0)) AS VRCONTABIL,
                     SUM(NVL(VRBASECALCNF,0)) AS VRBASECALC,
                     SUM(NVL(I.VRIMPOSTONF,0)) AS VRIMPOSTONF,
                     SUM(DECODE(I.CDFISCALNF,'2', I.VRCONTABILNF, 0)) AS VRINSOUTRAS,
                     SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)) AS VROUTRAS,
                     SUM(VALOR_IMPOSTO_NF(I.CDFILIAL, I.NRLANCTONF, '80', 'B')) AS VRBASECALCSUBS,
                     SUM(VALOR_IMPOSTO_NF(I.CDFILIAL, I.NRLANCTONF, '80', 'I')) AS VRIMPSUBS
                FROM IMPOSTONF I,NOTAFISCAL N, MAPAAPURAC M,FORNECEDOR F,ENDEFORN E,IMPOSTO P
               WHERE M.CDEMPRESA = P_CDEMPRESA
                 AND M.CDINSCESTA = P_CDINSCESTA
                 AND M.CDIMPOSTO = P_CDIMPOSTO
                 AND M.DTINIPERIODO >= P_DTINICIAL
                 AND M.DTFIMPERIODO <= P_DTFINAL
                 AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                 AND M.CDIMPOSTO = P.CDIMPOSTONFE
                 AND P.CDIMPOSTO = I.CDIMPOSTO
                 AND I.CDFILIAL = N.CDFILIAL
                 AND I.NRLANCTONF = N.NRLANCTONF
                 AND N.CDFORNECED = F.CDFORNECED
                 AND F.CDFORNECED = E.CDFORNECED
                 AND E.IDTPENDEFORN = 'P'
                 AND IDORIGDESTNF = 'F'
                 AND NVL(IDIMPOSTONF,'1') <> '3'
               GROUP BY I.CDFISCOPERNF,E.SGESTADO,I.VRALIQNF
               UNION ALL
              SELECT I.CDFISCOPERNF AS CDFISCOPERNF,
                     E.SGESTADO AS SGESTADO,
                     I.VRALIQNF AS VRALIQUOTA,
                     SUM(NVL(I.VRCONTABILNF,0)) AS VRCONTABIL,
                     SUM(NVL(VRBASECALCNF,0)) AS VRBASECALC,
                     SUM(NVL(I.VRIMPOSTONF,0)) AS VRIMPOSTONF,
                     SUM(DECODE(I.CDFISCALNF,'2', I.VRCONTABILNF, 0)) AS VRINSOUTRAS,
                     SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)) AS VROUTRAS,
                     SUM(VALOR_IMPOSTO_NF(I.CDFILIAL, I.NRLANCTONF, '80', 'B')) AS VRBASECALCSUBS,
                     SUM(VALOR_IMPOSTO_NF(I.CDFILIAL, I.NRLANCTONF, '80', 'I')) AS VRIMPSUBS
                FROM IMPOSTONF I,NOTAFISCAL N, MAPAAPURAC M,FILIAL F,ENDEFILI E,IMPOSTO P
               WHERE M.CDEMPRESA = P_CDEMPRESA
                 AND M.CDINSCESTA = P_CDINSCESTA
                 AND M.CDIMPOSTO = P_CDIMPOSTO
                 AND M.DTINIPERIODO >= P_DTINICIAL
                 AND M.DTFIMPERIODO <= P_DTFINAL
                 AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                 AND M.CDIMPOSTO = P.CDIMPOSTONFE
                 AND P.CDIMPOSTO = I.CDIMPOSTO
                 AND I.CDFILIAL = N.CDFILIAL
                 AND I.NRLANCTONF = N.NRLANCTONF
                 AND N.CDFILIEMIT = F.CDFILIAL
                 AND F.CDFILIAL = E.CDFILIAL
                 AND E.IDTPENDEFILI = 'P'
                 AND N.IDENTRSAIDOP = 'E'
                 AND IDORIGDESTNF = 'L'
                 AND NVL(IDIMPOSTONF,'1') <> '3'
               GROUP BY I.CDFISCOPERNF,E.SGESTADO,I.VRALIQNF
               UNION ALL
              SELECT I.CDFISCOPERNF AS CDFISCOPERNF,
                     E.SGESTADO AS SGESTADO,
                     I.VRALIQNF AS VRALIQUOTA,
                     SUM(NVL(I.VRCONTABILNF,0)) AS VRCONTABIL,
                     SUM(NVL(VRBASECALCNF,0)) AS VRBASECALC,
                     SUM(NVL(I.VRIMPOSTONF,0)) AS VRIMPOSTONF,
                     SUM(DECODE(I.CDFISCALNF,'2', I.VRCONTABILNF, 0)) AS VRINSOUTRAS,
                     SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)) AS VROUTRAS,
                     SUM(VALOR_IMPOSTO_NF(I.CDFILIAL, I.NRLANCTONF, '80', 'B')) AS VRBASECALCSUBS,
                     SUM(VALOR_IMPOSTO_NF(I.CDFILIAL, I.NRLANCTONF, '80', 'I')) AS VRIMPSUBS
                FROM IMPOSTONF I,NOTAFISCAL N, MAPAAPURAC M,FILIAL F,ENDEFILI E,IMPOSTO P
               WHERE M.CDEMPRESA = P_CDEMPRESA
                 AND M.CDINSCESTA = P_CDINSCESTA
                 AND M.CDIMPOSTO = P_CDIMPOSTO
                 AND M.DTINIPERIODO >= P_DTINICIAL
                 AND M.DTFIMPERIODO <= P_DTFINAL
                 AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                 AND M.CDIMPOSTO = P.CDIMPOSTONFE
                 AND P.CDIMPOSTO = I.CDIMPOSTO
                 AND I.CDFILIAL = N.CDFILIAL
                 AND I.NRLANCTONF = N.NRLANCTONF
                 AND N.CDFILIRECE = F.CDFILIAL
                 AND F.CDFILIAL = E.CDFILIAL
                 AND E.IDTPENDEFILI = 'P'
                 AND IDENTRSAIDOP = 'S'
                 AND IDORIGDESTNF = 'L'
                 AND NVL(IDIMPOSTONF,'1') <> '3'
               GROUP BY I.CDFISCOPERNF,E.SGESTADO,I.VRALIQNF) T
         GROUP BY T.CDFISCOPERNF,T.SGESTADO,T.VRALIQUOTA) LOOP

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

   END LOOP;

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

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_GIA_MS_01','5.30.000'); END;
/

