CREATE OR REPLACE FUNCTION GIA_RS_X11(
   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 XI - OPERACOES INTERESTADUAIS - DETALHAMENTO POR UF**********************/
BEGIN
   V_QUANTIDADE:= 0;
   V_REG_1:= P_REG_1;
   V_SEQUENCIA:= P_SEQUENCIA;
   FOR C_UF 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,
                      'X11 '                                                        ||
                       '01'                                                         ||
                      EST.SGESTADO                                                  ||
                      REPLACE(LTRIM(TO_CHAR(SUM(EST.ENTRADA),'00000000000.00')),'.')||
                      REPLACE(LTRIM(TO_CHAR(SUM(EST.SAIDA),'00000000000.00')),'.') AS CORPO
                FROM(SELECT ED.SGESTADO,
                            DECODE(N.IDENTRSAIDOP, 'E', ROUND(SUM(I.VRCONTABILNF),2),'0') AS ENTRADA,
                            DECODE(N.IDENTRSAIDOP, 'S', ROUND(SUM(I.VRCONTABILNF),2),'0') AS SAIDA
                       FROM IMPOSTONF I, NOTAFISCAL N, ENDECLIE ED, IMPOSTO P
                      WHERE I.NRLANCTONF = N.NRLANCTONF                         AND
                            N.CDFILIAL   = I.CDFILIAL                           AND
                            ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL)) AND
                            I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                     AND
                            I.CDIMPOSTO    =  P.CDIMPOSTO                       AND
                            P.CDIMPOSTONFE =  P_CDIMPOSTO                       AND
                            N.CDEMPRESA    =  P_CDEMPRESA                       AND
                            N.CDINSCESTA   =  P_CDINSCESTA                      AND
                            N.CDCLIENTE    =  ED.CDCLIENTE                      AND
                            SUBSTR(I.CDFISCOPERNF,1,1) IN ('2','6')             AND
                            ED.IDTPENDECLIE = 'P'
                     GROUP BY ED.SGESTADO,N.IDENTRSAIDOP
                     UNION ALL
                     SELECT ED.SGESTADO,
                            DECODE(N.IDENTRSAIDOP, 'E', ROUND(SUM(I.VRCONTABILNF),2),'0') AS ENTRADA,
                            DECODE(N.IDENTRSAIDOP, 'S', ROUND(SUM(I.VRCONTABILNF),2),'0') AS SAIDA
                       FROM IMPOSTONF I, NOTAFISCAL N, ENDEFORN ED, IMPOSTO P
                      WHERE I.NRLANCTONF = N.NRLANCTONF                         AND
                            N.CDFILIAL   = I.CDFILIAL                           AND
                            ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL)) AND
                            I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                     AND
                            I.CDIMPOSTO    =  P.CDIMPOSTO                       AND
                            P.CDIMPOSTONFE =  P_CDIMPOSTO                       AND
                            N.CDEMPRESA    =  P_CDEMPRESA                       AND
                            N.CDINSCESTA   =  P_CDINSCESTA                      AND
                            N.CDFORNECED   =  ED.CDFORNECED                     AND
                            SUBSTR(I.CDFISCOPERNF,1,1)  IN('2','6')             AND
                            ED.IDTPENDEFORN = 'P'
                     GROUP BY ED.SGESTADO, N.IDENTRSAIDOP
                     UNION ALL
                     SELECT ED.SGESTADO,
                            0 As ENTRADA,
                            NVL(ROUND(SUM(I.VRCONTABILNF),2),'0') AS SAIDA
                       FROM NOTAFISCAL N, IMPOSTONF I, FILIAL F, ENDEFILI ED, IMPOSTO P
                      WHERE I.NRLANCTONF = N.NRLANCTONF                         AND
                            N.CDFILIAL   = I.CDFILIAL                           AND
                            ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL)) AND
                            I.NRSEQUMAPAPU =  P_NRSEQUMAPAPU                    AND
                            I.CDIMPOSTO    =  P.CDIMPOSTO                       AND
                            P.CDIMPOSTONFE =  P_CDIMPOSTO                       AND
                            N.CDEMPRESA    =  P_CDEMPRESA                       AND
                            N.CDINSCESTA   =  P_CDINSCESTA                      AND
                            F.CDFILIAL     =  ED.CDFILIAL                       AND
                            SUBSTR(I.CDFISCOPERNF,1,1) IN ('2','6')             AND
                            ED.IDTPENDEFILI = 'P'                               AND
                            ED.CDFILIAL = N.CDFILIRECE                          AND
                            N.IDORIGDESTNF  = 'L'                               AND
                            N.IDENTRSAIDOP = 'S'
                     GROUP BY  ED.SGESTADO, N.IDENTRSAIDOP
                     UNION ALL
                     SELECT ED.SGESTADO,
                            NVL(ROUND(SUM(I.VRCONTABILNF),2),'0') AS ENTRADA,
                            0 AS SAIDA
                       FROM NOTAFISCAL N, IMPOSTONF I, FILIAL F, ENDEFILI ED, IMPOSTO P
                      WHERE I.NRLANCTONF = N.NRLANCTONF                         AND
                            N.CDFILIAL   = I.CDFILIAL                           AND
                            ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL)) AND
                            I.NRSEQUMAPAPU =   P_NRSEQUMAPAPU                   AND
                            I.CDIMPOSTO    =  P.CDIMPOSTO                       AND
                            P.CDIMPOSTONFE =  P_CDIMPOSTO                       AND
                            N.CDEMPRESA    =   P_CDEMPRESA                      AND
                            N.CDINSCESTA   =   P_CDINSCESTA                     AND
                            F.CDFILIAL     =  ED.CDFILIAL                       AND
                            SUBSTR(I.CDFISCOPERNF,1,1)IN ('2','6')              AND
                            ED.IDTPENDEFILI = 'P'                               AND
                            ED.CDFILIAL = N.CDFILIEMIT                          AND
                            N.IDORIGDESTNF = 'L'                                AND
                            N.IDENTRSAIDOP = 'E'
                     GROUP BY  ED.SGESTADO, N.IDENTRSAIDOP)EST
         GROUP BY EST.SGESTADO
         ORDER BY EST.SGESTADO)LOOP

      V_SEQUENCIA:= V_SEQUENCIA+1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LTRIM(TO_CHAR(V_SEQUENCIA,'0000000000')),C_UF.CABECALHO||LTRIM(TO_CHAR(V_SEQUENCIA,'0000'))||C_UF.CORPO,P_NRSEQUENCIAL);

      V_QUANTIDADE:=V_QUANTIDADE+1;

   END LOOP;

/************************************************ CONTADOR QUANDRO ABC*****************************************************/
   V_REG_1:=V_REG_1||LTRIM(TO_CHAR(V_QUANTIDADE,'000'));
   RETURN(V_REG_1);
END;
/