CREATE OR REPLACE FUNCTION GIA_RS_X06(
   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_CODDEBI        IN VARCHAR2,
   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);

BEGIN
/*************************Discriminacao dos Creditos Transferidos - ANEXO VI*************************/
   V_QUANTIDADE:= 0;
   V_SEQUENCIA:= P_SEQUENCIA;
   V_REG_1:= P_REG_1;
   FOR C_REG_SAI 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,
                           'X06 '                                                        ||
                           '01'                                                          ||
                           LPAD(NVL(T.INSCRICAO,'0'),10,'0')                             || /* CGC/TE Destino */
                           LPAD(NVL(P_CODDEBI,'0'),3,'0')                                || /* Codigo */
                           REPLACE(LTRIM(TO_CHAR(SUM(T.VRIMPOSAID),'00000000000.00')),'.')   /* Valor Credito */
                        AS CORPO
                        FROM (SELECT C.NRINSESTCLIE AS INSCRICAO,
                                     SUM(ROUND(I.VRIMPOSTONF,2)) AS  VRIMPOSAID
                               FROM IMPOSTONF I, NOTAFISCAL N, CLIENTE C, IMPOSTO P
                               WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU          AND
                                     I.NRLANCTONF   = N.NRLANCTONF            AND
                                     I.CDFILIAL     = N.CDFILIAL              AND
                                     P.CDIMPOSTONFE = P_CDIMPOSTO             AND
                                     I.CDIMPOSTO    = P.CDIMPOSTO             AND
                                     I.CDFISCOPERNF IN ('5601','5602','5605') AND
                                     N.CDEMPRESA    = P_CDEMPRESA             AND
                                     N.CDINSCESTA   = P_CDINSCESTA            AND
                                     N.IDENTRSAIDOP = 'S'                     AND
                                     N.CDCLIENTE    = C.CDCLIENTE             AND
                                     N.IDORIGDESTNF  = 'C'                    AND
                                     ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                             GROUP BY C.NRINSESTCLIE
                             UNION ALL
                             SELECT F.NRINSESTFORN AS INSCRICAO,
                                    SUM(ROUND(I.VRIMPOSTONF,2)) AS  VRIMPOSAID
                               FROM IMPOSTONF I, NOTAFISCAL N, FORNECEDOR F, IMPOSTO P
                               WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU          AND
                                     I.NRLANCTONF   = N.NRLANCTONF            AND
                                     I.CDFILIAL     = N.CDFILIAL              AND
                                     P.CDIMPOSTONFE = P_CDIMPOSTO             AND
                                     I.CDIMPOSTO    = P.CDIMPOSTO             AND
                                     I.CDFISCOPERNF IN ('5601','5602','5605') AND
                                     N.CDEMPRESA    = P_CDEMPRESA             AND
                                     N.CDINSCESTA   = P_CDINSCESTA            AND
                                     N.IDENTRSAIDOP = 'S'                     AND
                                     N.CDFORNECED   = F.CDFORNECED            AND
                                     N.IDORIGDESTNF  = 'F'                    AND
                                     ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                             GROUP BY F.NRINSESTFORN
                             UNION ALL
                             SELECT F.CDINSCESTA AS INSCRICAO,
                                    SUM(ROUND(I.VRIMPOSTONF,2)) AS  VRIMPOSAID
                               FROM IMPOSTONF I, NOTAFISCAL N, FILIAL F, IMPOSTO P
                               WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU          AND
                                     I.NRLANCTONF   = N.NRLANCTONF            AND
                                     I.CDFILIAL     = N.CDFILIAL              AND
                                     P.CDIMPOSTONFE = P_CDIMPOSTO             AND
                                     I.CDIMPOSTO    = P.CDIMPOSTO             AND
                                     I.CDFISCOPERNF IN ('5601','5602','5605') AND
                                     N.CDEMPRESA    = P_CDEMPRESA             AND
                                     N.CDINSCESTA   = P_CDINSCESTA            AND
                                     N.IDENTRSAIDOP = 'S'                     AND
                                     N.CDFILIRECE   = F.CDFILIAL              AND
                                     N.IDORIGDESTNF  = 'L'                    AND
                                     ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                             GROUP BY F.CDINSCESTA)T
                 GROUP BY T.INSCRICAO
                 ORDER BY T.INSCRICAO)LOOP
         V_SEQUENCIA:= V_SEQUENCIA+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
         VALUES(LTRIM(TO_CHAR(V_SEQUENCIA,'0000000000')),C_REG_SAI.CABECALHO||LTRIM(TO_CHAR(V_SEQUENCIA,'0000'))||
                C_REG_SAI.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;
/