CREATE OR REPLACE FUNCTION GIA_RS_X07B(
   P_CDEMPRESA      IN EMPRESA.CDEMPRESA%TYPE,
   P_CDIMPOSTO      IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDINSCESTA     IN INSCRESTAD.CDINSCESTA%TYPE,
   P_DTINIPERIODO   IN MAPAAPURAC.DTINIPERIODO%TYPE,
   P_DTFIMPERIODO   IN MAPAAPURAC.DTINIPERIODO%TYPE,
   P_VERSAO         IN IMPOSTO.CDIMPOSTO%TYPE,
   P_NRSEQUENCIAL   IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_SEQUENCIA      IN NUMBER,
   P_REG_1          IN VARCHAR2,
   P_NRSEQUMAPAPU   IN MAPAAPURAC.NRSEQUMAPAPU%TYPE )RETURN VARCHAR2
AS
   V_SEQUENCIA  NUMBER;
   V_QUANTIDADE NUMBER;
   V_REG_1      VARCHAR2(1000);
/***************************ANEXO VII.B - SAIDAS COM ST********************************************/
BEGIN
   V_QUANTIDADE:= 0;
   V_REG_1:= P_REG_1;
   V_SEQUENCIA:= P_SEQUENCIA;
   FOR C_REG_SUB1 IN(SELECT '****'                                                            ||
                            P_VERSAO                                                          ||
                            DECODE(P_VERSAO,'07','00000000','')                               ||/*VERSAO 07*/
                            DECODE(P_VERSAO,'07',LPAD(' ',1,' '),'')                          ||/*VERSAO 07*/
                            LTRIM(TO_CHAR(P_DTINIPERIODO,'DD'))                               ||
                            LTRIM(TO_CHAR(P_DTFIMPERIODO,'DD'))                               ||
                            LTRIM(TO_CHAR(P_DTFIMPERIODO,'MMYYYY'))                           ||
                            LPAD(P_CDINSCESTA,10,'0') As CABECALHO,
                            'X07B'                                                            ||
                            '01'                                                              ||
                            I.CDFISCOPERNF                                                    ||
                            REPLACE(LTRIM(TO_CHAR(SUM(ROUND(I.VRBASECALCNF,2)),'00000000000.00')),'.')||
                            REPLACE(LTRIM(TO_CHAR(SUM(ROUND(I.VRIMPOSTONF,2)),'00000000000.00')),'.') AS CORPO
                      FROM MAPAAPURAC M, IMPOSTONF I, IMPOSTO P
                     WHERE M.CDEMPRESA    = P_CDEMPRESA     AND
                           M.CDIMPOSTO    = '80'            AND
                           M.CDINSCESTA   = P_CDINSCESTA    AND
                           M.DTINIPERIODO = P_DTINIPERIODO  AND
                           M.DTFIMPERIODO = P_DTFIMPERIODO  AND
                           I.NRSEQUMAPAPU = M.NRSEQUMAPAPU  AND
                           I.CDIMPOSTO    = P.CDIMPOSTO     AND
                           P.CDIMPOSTONFE = M.CDIMPOSTO     AND
                           SUBSTR(I.CDFISCOPERNF,1,1) IN ('5','6','7')
                     GROUP BY I.CDFISCOPERNF
                     ORDER BY I.CDFISCOPERNF)LOOP
         V_SEQUENCIA:= V_SEQUENCIA+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
         VALUES(LTRIM(TO_CHAR(V_SEQUENCIA,'0000000000')),C_REG_SUB1.CABECALHO||LTRIM(TO_CHAR(V_SEQUENCIA,'0000'))||
                C_REG_SUB1.CORPO,P_NRSEQUENCIAL);
         V_QUANTIDADE:=V_QUANTIDADE+1;
   END LOOP;
   /*******CONTADOR REGISTRO ABC *******************************************/
   V_REG_1:=V_REG_1||LTRIM(TO_CHAR(V_QUANTIDADE,'000'));
   RETURN(V_REG_1);
END;
/