CREATE OR REPLACE FUNCTION GIA_RS_OBS(
   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_CDLIVROMAG     IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_VERSAO         IN IMPOSTO.CDIMPOSTO%TYPE,
   P_NRSEQUENCIAL   IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_NRSEQUMAPAPU   IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_SEQUENCIA      IN NUMBER)RETURN VARCHAR2
AS
   V_SEQUENCIA  NUMBER;
   V_DADOS      VARCHAR(4036);
   V_OBSERVACAO VARCHAR(4000);
   V_DESCRICAO  VARCHAR(80);
   V_QTDLINHAS  NUMBER;
/****************************DADOS DA EMPRESA - REGISTRO XDC*************************************************/

BEGIN
   V_SEQUENCIA:= P_SEQUENCIA;
   V_QTDLINHAS:= 0;

   FOR C_CFOP IN  (/*Requer cadastro de cfops, tela FIS07300, em layout codigo 002 - CFOP - EXCLUIDAS VALOR INTEGRAL*/
                   /*Preencher Excluidas com diferenca Valor Contabil - Valores Fiscais*/
                   SELECT DISTINCT I.CDFISCOPERNF
                     FROM IMPOSTONF I, IMPOSTO P
                    WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                AND
                          I.CDIMPOSTO    = P.CDIMPOSTO                   AND
                          P.CDIMPOSTONFE = P_CDIMPOSTO                   AND
                          I.CDFISCOPERNF NOT IN (SELECT CDCFOPLMAG
                                                   FROM CFOPLIVROMAG
                                                  WHERE CDLIVROMAG = P_CDLIVROMAG
                                                    AND CDLAYOUTLMAG = '002') AND
                          ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                    GROUP BY I.CDFISCOPERNF
                   HAVING SUM(ROUND(I.VRCONTABILNF-I.VRBASECALCNF-DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)-DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) > 0
                   UNION ALL
                   /*Requer cadastro de cfops, tela FIS07300, em layout codigo 002 - CFOP - EXCLUIDAS VALOR INTEGRAL*/
                   /*Preencher apenas Valor Contabil e Excluidas*/
                   SELECT DISTINCT I.CDFISCOPERNF
                     FROM IMPOSTONF I, IMPOSTO P, CFOPLIVROMAG C
                    WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                AND
                          I.CDIMPOSTO    = P.CDIMPOSTO                   AND
                          P.CDIMPOSTONFE = P_CDIMPOSTO                   AND
                          I.CDFISCOPERNF = C.CDCFOPLMAG                  AND
                          C.CDLIVROMAG   = P_CDLIVROMAG                  AND
                          C.CDLAYOUTLMAG = '002'                         AND
                          ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                   ORDER BY 1 ) LOOP

      IF (C_CFOP.CDFISCOPERNF IS NOT NULL) THEN
         IF V_OBSERVACAO IS NULL THEN
            V_OBSERVACAO := RPAD(C_CFOP.CDFISCOPERNF,80,' ');
         ELSE
            V_OBSERVACAO := V_OBSERVACAO||RPAD(C_CFOP.CDFISCOPERNF,80,' ');
         END IF;
         V_QTDLINHAS := V_QTDLINHAS + 1;
      END IF;
   END LOOP;

   IF V_OBSERVACAO IS NOT NULL THEN
      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')                                                                ||
             LTRIM(TO_CHAR(V_SEQUENCIA,'0000'))                                                       ||
             'OBS '                                                                                   ||
             LPAD(V_QTDLINHAS,2,'0')                                                                  ||
             V_OBSERVACAO
        INTO V_DADOS
        FROM DUAL;

      V_SEQUENCIA := V_SEQUENCIA+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
         (LTRIM(TO_CHAR(V_SEQUENCIA,'0000000000')),V_DADOS,P_NRSEQUENCIAL);
   END IF;

   RETURN(V_SEQUENCIA);
END;
/