CREATE OR REPLACE FUNCTION ARQUIVO_GIA_MS_22(
   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);
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
               ||'22';

   FOR C_REGISTRO_22 IN(
     SELECT  SUBSTR(LPAD(NVL(T.NRNOTAFISC,' '),9,' '),2,8)||
             LPAD(SUBSTR(NVL(T.CDSERIE,' '),1,3),3,' ')||
             LPAD(NVL(TO_CHAR(T.DTEMISSAO,'DDMMYYYY'),' '),8,' ')||
             LPAD(NVL(T.SGESTADO,' '),2,' ')||
             SUBSTR(T.OPERACAO,1,2)||
             LPAD(NVL(SUBSTR(LTRIM(T.OPERACAO),3,11),' '),9,' ')||
             LPAD(NVL(SUBSTR(RTRIM(T.OPERACAO),12,25),' '),13,' ')||
             LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(T.VRNOTAFISC,0)),2),'99999999990.00')),'.',''),13,0) AS REGISTRO_22

     FROM(
          SELECT N.NRNOTAFISC AS NRNOTAFISC,
                 N.CDSERIE AS CDSERIE,
                 N.DTEMISSAO AS DTEMISSAO,
                 E.SGESTADO AS SGESTADO,
                 DECODE(IDTPIJURCLIE,'F','PF'||LPAD(' ',23,' '),
                  DECODE(IDTPIJURCLIE,'J',
                   DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'5','CI'||NVL(C.NRINSESTCLIE,' ')||LPAD(' ',13,' '),
                    DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'6','CI'||LPAD(' ',9,' ')||NVL(C.NRINSJURCLIE,' '),'PS'||LPAD(' ',9,' ')||NVL(C.NRINSJURCLIE,' '))),' ')) AS OPERACAO,
                 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 = 'S'
             AND IDORIGDESTNF = 'C'
             AND NVL(IDIMPOSTONF,'1') <> '3'
           GROUP BY N.NRNOTAFISC,N.CDSERIE,N.DTEMISSAO,E.SGESTADO,
                    DECODE(IDTPIJURCLIE,'F','PF'||LPAD(' ',23,' '),
                     DECODE(IDTPIJURCLIE,'J',
                      DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'5','CI'||NVL(C.NRINSESTCLIE,' ')||LPAD(' ',13,' '),
                       DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'6','CI'||LPAD(' ',9,' ')||NVL(C.NRINSJURCLIE,' '),'PS'||LPAD(' ',9,' ')||NVL(C.NRINSJURCLIE,' '))),' '))
          UNION ALL
          SELECT N.NRNOTAFISC AS NRNOTAFISC,
                 N.CDSERIE AS CDSERIE,
                 N.DTEMISSAO AS DTEMISSAO,
                 E.SGESTADO AS SGESTADO,
                 DECODE(IDTPIJURFILI,'F','PF'||LPAD(' ',23,' '),
                  DECODE(IDTPIJURFILI,'J',
                   DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'5','CI'||NVL(F.CDINSCESTA,' ')||LPAD(' ',13,' '),
                    DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'6','CI'||LPAD(' ',9,' ')||NVL(F.NRINSJURFILI,' '),'PS'||LPAD(' ',9,' ')||NVL(F.NRINSJURFILI,' '))),' ')) AS OPERACAO,
                 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.CDFILIRECE = F.CDFILIAL
             AND F.CDFILIAL = E.CDFILIAL
             AND E.IDTPENDEFILI = 'P'
             AND N.IDENTRSAIDOP = 'S'
             AND IDORIGDESTNF = 'L'
             AND NVL(IDIMPOSTONF,'1') <> '3'
           GROUP BY N.NRNOTAFISC,N.CDSERIE,N.DTEMISSAO,E.SGESTADO,
                    DECODE(IDTPIJURFILI,'F','PF'||LPAD(' ',23,' '),
                     DECODE(IDTPIJURFILI,'J',
                      DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'5','CI'||NVL(F.CDINSCESTA,' ')||LPAD(' ',13,' '),
                       DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'6','CI'||LPAD(' ',9,' ')||NVL(F.NRINSJURFILI,' '),'PS'||LPAD(' ',9,' ')||NVL(F.NRINSJURFILI,' '))),' '))
          UNION ALL
          SELECT N.NRNOTAFISC AS NRNOTAFISC,
                 N.CDSERIE AS CDSERIE,
                 N.DTEMISSAO AS DTEMISSAO,
                 E.SGESTADO AS SGESTADO,
                 DECODE(IDTPIJURFORN,'F','PF'||LPAD(' ',23,' '),
                  DECODE(IDTPIJURFORN,'J',
                   DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'5','CI'||NVL(F.NRINSESTFORN,' ')||LPAD(' ',13,' '),
                    DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'6','CI'||LPAD(' ',9,' ')||NVL(F.NRINSJURFORN,' '),'PS'||LPAD(' ',9,' ')||NVL(F.NRINSJURFORN,' '))),' ')) AS OPERACAO,
                 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 = 'S'
             AND IDORIGDESTNF = 'F'
             AND NVL(IDIMPOSTONF,'1') <> '3'
           GROUP BY N.NRNOTAFISC,N.CDSERIE,N.DTEMISSAO,E.SGESTADO,
                    DECODE(IDTPIJURFORN,'F','PF'||LPAD(' ',23,' '),
                     DECODE(IDTPIJURFORN,'J',
                      DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'5','CI'||NVL(F.NRINSESTFORN,' ')||LPAD(' ',13,' '),
                       DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'6','CI'||LPAD(' ',9,' ')||NVL(F.NRINSJURFORN,' '),'PS'||LPAD(' ',9,' ')||NVL(F.NRINSJURFORN,' '))),' ')))T
        GROUP BY T.NRNOTAFISC,T.CDSERIE,T.DTEMISSAO,T.SGESTADO,T.OPERACAO) LOOP

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

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

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

