CREATE OR REPLACE FUNCTION ARQUIVO_GIA_MS_21(
   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_REG21               NUMBER;
BEGIN
   V_CONTADOR := NVL(P_CONTADOR,0);
   V_REG21    := 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
               ||'21';

   FOR C_REGISTRO_21 IN(
      SELECT SUBSTR(LPAD(NVL(T.NRNOTAFISC,' '),9,' '),2,8)
             ||LPAD(SUBSTR(NVL(T.CDSERIEENTR,' '),1,3),3,' ') AS PARTE_01,
             LPAD(NVL(TO_CHAR(T.DTEMISSAO,'DDMMYYYY'),' '),8,' ')
             ||LPAD(NVL(T.SGESTADO,' '),2,' ')
             ||SUBSTR(RPAD(REPLACE(REPLACE(REPLACE(NVL(T.NRINSESTAD, ' '),'.',''),'-',''),'ISENT','ISENTO'),9,' '),1,9)
             ||SUBSTR(RPAD(REPLACE(REPLACE(REPLACE(REPLACE(NVL(T.CNPJ, ' '),'/',''),'.',''),'-',''),'ISENT','ISENTO'),14,' '),1,14)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VRNOTAFISC,0)),2),'99999999990.00')),'.',''),13,0) AS PARTE_02
        FROM(
             SELECT N.NRNOTAFISC AS NRNOTAFISC,
                    CDSERIEENTR AS CDSERIEENTR,
                    N.DTEMISSAO AS DTEMISSAO,
                    E.SGESTADO AS SGESTADO,
                    DECODE(E.SGESTADO,'MS',C.NRINSESTCLIE,' ') NRINSESTAD,
                    DECODE(E.SGESTADO,'MS',' ',C.NRINSJURCLIE) CNPJ,
                    SUM(NVL(N.VRNOTAFISC,0)) AS VRNOTAFISC
               FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO P, CLIENTE C, ENDECLIE E
              WHERE M.CDEMPRESA = P_CDEMPRESA
                AND M.CDIMPOSTO = P_CDIMPOSTO
                AND M.CDINSCESTA = P_CDINSCESTA
                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 N.IDENTRSAIDOP = 'E'
                AND IDORIGDESTNF = 'C'
                AND NVL(IDIMPOSTONF,'1') <> '3'
              GROUP BY N.NRNOTAFISC,N.CDSERIEENTR,N.DTEMISSAO,E.SGESTADO,
                       DECODE(E.SGESTADO,'MS',C.NRINSESTCLIE,' '),
                       DECODE(E.SGESTADO,'MS',' ',C.NRINSJURCLIE)
             UNION ALL
             SELECT N.NRNOTAFISC AS NRNOTAFISC,
                    CDSERIEENTR AS CDSERIEENTR,
                    N.DTEMISSAO AS DTEMISSAO,
                    E.SGESTADO AS SGESTADO,
                    DECODE(E.SGESTADO,'MS',F.CDINSCESTA,' ') NRINSESTAD,
                    DECODE(E.SGESTADO,'MS',' ',F.NRINSJURFILI) CNPJ,
                    SUM(NVL(N.VRNOTAFISC,0)) AS VRNOTAFISC
               FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO P, FILIAL F, ENDEFILI E
              WHERE M.CDEMPRESA = P_CDEMPRESA
                AND M.CDIMPOSTO = P_CDIMPOSTO
                AND M.CDINSCESTA = P_CDINSCESTA
                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 N.NRNOTAFISC,N.CDSERIEENTR,N.DTEMISSAO,E.SGESTADO,
                       DECODE(E.SGESTADO,'MS',F.CDINSCESTA,' '),
                       DECODE(E.SGESTADO,'MS',' ',F.NRINSJURFILI)
              UNION ALL
              SELECT N.NRNOTAFISC AS NRNOTAFISC,
                    CDSERIEENTR AS CDSERIEENTR,
                    N.DTEMISSAO AS DTEMISSAO,
                    E.SGESTADO AS SGESTADO,
                    DECODE(E.SGESTADO,'MS',F.NRINSESTFORN,' ') NRINSESTAD,
                    DECODE(E.SGESTADO,'MS',' ',F.NRINSJURFORN) CNPJ,
                    SUM(NVL(N.VRNOTAFISC,0)) AS VRNOTAFISC
               FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO P, FORNECEDOR F, ENDEFORN E
              WHERE M.CDEMPRESA = P_CDEMPRESA
                AND M.CDIMPOSTO = P_CDIMPOSTO
                AND M.CDINSCESTA = P_CDINSCESTA
                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 N.IDENTRSAIDOP = 'E'
                AND IDORIGDESTNF = 'F'
                AND NVL(IDIMPOSTONF,'1') <> '3'
              GROUP BY N.NRNOTAFISC,N.CDSERIEENTR,N.DTEMISSAO,E.SGESTADO,
                       DECODE(E.SGESTADO,'MS',F.NRINSESTFORN,' '),
                       DECODE(E.SGESTADO,'MS',' ',F.NRINSJURFORN)) T
         GROUP BY T.NRNOTAFISC,T.CDSERIEENTR,T.DTEMISSAO,T.SGESTADO,
                  T.NRINSESTAD,T.CNPJ) LOOP

      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
         (LPAD(V_CONTADOR, 10, '0'),V_INSERT||C_REGISTRO_21.PARTE_01||LPAD(TO_CHAR(V_REG21),4,'0')||C_REGISTRO_21.PARTE_02,P_SEQMAGNET);
          V_REG21 := V_REG21 + 1;
   END LOOP;

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

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

