CREATE OR REPLACE FUNCTION GIA_RS_ABC(
   P_SUBTRIB        IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   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_EMPREGADOS     IN NOTAFISCAL.CDROTATRANS%TYPE,
   P_FOLHA          IN IMPOSTONF.VRIMPOSTONF%TYPE,
   P_ENERGIA        IN NOTAFISCAL.NRNOTAFISCALELE%TYPE,
   P_RETIFICADORA   IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_VERSAO         IN IMPOSTO.CDIMPOSTO%TYPE,
   P_IDREG          IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_NRSEQUENCIAL   IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_CODENTREGA     IN NOTAFISCAL.DSQTDEVOLU%TYPE,
   P_IMPPAGMES      IN NUMBER,
   P_NRSEQUMAPAPU   IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_SEQUENCIA      IN NUMBER ) RETURN VARCHAR2
AS
   V_QUANTIDADE   NUMBER;
   V_REG_1        VARCHAR(1000);
   V_SEQUENCIA    NUMBER;
/***************************************REGISTRO 1 - QUADRO ABC****************************************/
BEGIN
   BEGIN
      V_SEQUENCIA:=1;
      SELECT /*** CABECALHO ***/
            '****'                                                                                       ||
            P_VERSAO                                                                                     ||
            '00000000'                                                                                   ||
            P_RETIFICADORA                                                                               ||
            LTRIM(TO_CHAR(P_DTINIPERIODO,'DD'))                                                          ||
            LTRIM(TO_CHAR(P_DTFIMPERIODO,'DD'))                                                          ||
            LTRIM(TO_CHAR(P_DTFIMPERIODO,'MMYYYY'))                                                      ||
            LPAD(M.CDINSCESTA,10,'0')                                                                    ||
            LTRIM(TO_CHAR(V_SEQUENCIA,'0000'))                                                           ||
            RPAD(P_IDREG,4,' ')                                                                          ||
             /*** QUADRO A ***/
            REPLACE(LTRIM(TO_CHAR(L.IMPINT+L.IMPOUTES-NVL(E.VRANEXO2,0),'0000000000000.00')),'.')        ||/*REF 01*/
            REPLACE(LTRIM(TO_CHAR(L.IMPEXT,'0000000000000.00')),'.')                                     ||/*REF 02*/
            REPLACE(LTRIM(TO_CHAR(NVL(E.VRANEXO2,0),'0000000000000.00')),'.')                            ||/*REF 03*/
            REPLACE(LTRIM(TO_CHAR(DECODE(IE.IDAPURLUCPRE,'S',MR.VROUTRCRED,'0'),'0000000000000.00')),'.')||/*REF 04*/
            '000000000000000'                                                                            ||/*REF 05*/
            REPLACE(LTRIM(TO_CHAR(DECODE(IE.IDAPURLUCPRE,'N',MR.VROUTRCRED,'0'),'0000000000000.00')),'.')||/*REF 06*/
            DECODE(P_VERSAO,'07',LPAD(' ',60,' '),'')                                                    ||/*VERSAO 07*/
            REPLACE(LTRIM(TO_CHAR((MR.VRTOTACRED-MR.VRESTODEBI),'0000000000000.00')),'.')                ||/*REF 07*/
            REPLACE(LTRIM(TO_CHAR(L.DEBINT+L.DEBOUTES-NVL(S.VRANEXO6,0),'0000000000000.00')),'.')        ||/*REF 08*/
            REPLACE(LTRIM(TO_CHAR(L.DEBEXT,'0000000000000.00')),'.')                                    ||/*REF 09*/
            '000000000000000'                                                                            ||/*REF 10*/
            REPLACE(LTRIM(TO_CHAR(NVL(S.VRANEXO6,0),'0000000000000.00')),'.')                            ||/*REF 11*/
            '000000000000000'                                                                            ||/*REF 12*/
            REPLACE(LTRIM(TO_CHAR(MR.VRDIFALIQU+MR.VRESTOCRED+MR.VROUTRDEBI,'0000000000000.00')),'.')    ||/*REF 13*/
            DECODE(P_VERSAO,'07',LPAD(' ',60,' '),'')                                                    ||/*VERSAO 07*/
            REPLACE(LTRIM(TO_CHAR((MR.VRTOTADEBI-MR.VRESTOCRED),'0000000000000.00')),'.')                ||/*REF 14*/
            P_SUBTRIB                                                                                    ||/*REF 15*/
             /*** QUADRO B ***/
            REPLACE(LTRIM(TO_CHAR(MR.VRSLDCRANTE,'0000000000000.00')),'.')                               ||/*REF 16*/
            DECODE(P_VERSAO,'07','000000000000000','')                                                   ||/*REF 17 - VERSAO 07*/
            '000000000000000'                                                                            ||/*REF 18*/
            DECODE(P_VERSAO,'07',LPAD('0',15,'0'),'')                                                    ||/*VERSAO 07*/
            REPLACE(LTRIM(TO_CHAR(NVL(P_IMPPAGMES,'0'),'0000000000000.00')),'.')                         ||/*REF 20*/
            '000000000000000'                                                                            ||/*REF 21*/
            REPLACE(LTRIM(TO_CHAR(NVL(ST.VRIMPOSTRIB,0),'0000000000000.00')),'.')                        ||/*REF 22*/
            REPLACE(LTRIM(TO_CHAR(MR.VRSALDODEBI+MR.VRDIFALIQU,'0000000000000.00')),'.')                 ||/*REF 23*/
            DECODE(P_VERSAO,'07',LPAD('0',15,'0'),'')                                                    ||/*VERSAO 07*/
            REPLACE(LTRIM(TO_CHAR(MR.VRSALDODEBI+MR.VRDIFALIQU,'0000000000000.00')),'.')                 ||/*REF 25*/
            '000000000000000'                                                                            ||/*REF 26*/
            '000000000000000'                                                                            ||/*REF 27*/
            REPLACE(LTRIM(TO_CHAR(MR.VRSALDOCRED,'0000000000000.00')),'.')                               ||/*REF 28*/
            '000000000000000'                                                                            ||/*REF 29*/
            DECODE(P_VERSAO,'07',LPAD('0',15,'0'),'')                                                    ||/*VERSAO 07*/
             /*** QUADRO C ***/
            REPLACE(LTRIM(TO_CHAR(NVL(F.VRFATU,0)-NVL(F.VRNFATU,0),'0000000000000.00')),'.')             ||/*REF 31*/
            LPAD(NVL(P_EMPREGADOS,'0'),6,'0')                                                            ||/*REF 32*/
            REPLACE(LTRIM(TO_CHAR(NVL(P_FOLHA,'0'),'0000000000000.00')),'.')                             ||/*REF 33*/
            DECODE(P_VERSAO,'07',REPLACE(LTRIM(TO_CHAR(NVL(P_ENERGIA,'0'),'000000000')),'.'),'')         ||/*REF 34 - VERSAO 07*/
            DECODE(P_VERSAO,'07','000000000000000','')                                                   ||/*REF 35 - VERSAO 07*/
            DECODE(P_VERSAO,'07','000000000000000','')                                                   ||/*REF 36 - VERSAO 07*/
            DECODE(P_VERSAO,'07',REPLACE(LTRIM(TO_CHAR(L.ENTINT,'0000000000000.00')),'.'),'000000000000000')||/*REF 37*/
            DECODE(P_VERSAO,'07',REPLACE(LTRIM(TO_CHAR(L.SAIDINT,'0000000000000.00')),'.'),'000000000000000')||/*REF 38*/
            DECODE(P_VERSAO,'07',REPLACE(LTRIM(TO_CHAR(L.ENTOUTES,'0000000000000.00')),'.'),'000000000000000')||/*REF 39*/
            DECODE(P_VERSAO,'07',REPLACE(LTRIM(TO_CHAR(L.SAIDOUTES,'0000000000000.00')),'.'),'000000000000000')||/*REF 40*/
            DECODE(P_VERSAO,'07',REPLACE(LTRIM(TO_CHAR(L.ENTEXT,'0000000000000.00')),'.'),'000000000000000')||/*REF 41*/
            DECODE(P_VERSAO,'07',REPLACE(LTRIM(TO_CHAR(L.SAIDEXT,'0000000000000.00')),'.'),'000000000000000')||/*REF 42*/
            REPLACE(LTRIM(TO_CHAR(L.ENTINT+ENTOUTES+ENTEXT,'0000000000000.00')),'.')                     ||/*REF 43*/
            REPLACE(LTRIM(TO_CHAR(L.SAIDINT+SAIDOUTES+SAIDEXT,'0000000000000.00')),'.')                  ||/*REF 44*/
            DECODE(P_VERSAO,'07','000','')                                                               ||/*DDD - VERSAO 07*/
            DECODE(P_VERSAO,'07',LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRTELEIEST,'-'),'('),')'),'.'),' '),8,'0'),'')||/*TELEFONE - VERSAO 07*/
            REPLACE(LTRIM(TO_CHAR(NVL(P_IMPPAGMES,'0'),'0000000000000.00')),'.')                         ||/*ICMS PROPRIO*/
            '000000000000000'                                                                            ||/*ICMS ST*/
            'N'                                                                                          ||/*TRANSP. ICMS*/
            DECODE(P_VERSAO,'07',RPAD(NVL(P_CODENTREGA,' '),6,' '),'')                                   ||/*VERSAO 07*/
             /*** QUADRO E - SOMENTE VERSAO 08 ***/
            DECODE(P_VERSAO,'08','0000000000000000','')                                                  ||/*REF 45*/
            DECODE(P_VERSAO,'08','0000000000000000','')                                                  ||/*REF 46*/
            DECODE(P_VERSAO,'08','0000000000000000','')                                                  ||/*REF 47*/
            DECODE(P_VERSAO,'08','0000000000000000','')                                                  ||/*REF 48*/
            DECODE(P_VERSAO,'08','0000000000000000','')                                                  ||/*REF 49*/
            DECODE(P_VERSAO,'08','0000000000000000','')                                                  ||/*REF 50*/
            DECODE(P_VERSAO,'08','0000000000000000','')                                                  ||/*REF 51*/
            DECODE(P_VERSAO,'08','0000000000000000','')                                                    /*REF 52*/
       INTO V_REG_1
       FROM MAPAAPURAC M, INSCRESTAD IE, MAPARECUP MR,
            (SELECT SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'1',I.VRCONTABILNF,0),2)) AS ENTINT,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'2',I.VRCONTABILNF,0),2)) AS ENTOUTES,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'3',I.VRCONTABILNF,0),2)) AS ENTEXT,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'5',I.VRCONTABILNF,0),2)) AS SAIDINT,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'6',I.VRCONTABILNF,0),2)) AS SAIDOUTES,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'7',I.VRCONTABILNF,0),2)) AS SAIDEXT,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'1',I.VRIMPOSTONF,0),2))  AS IMPINT,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'2',I.VRIMPOSTONF,0),2))  AS IMPOUTES,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'3',I.VRIMPOSTONF,0),2))  AS IMPEXT,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'5',I.VRIMPOSTONF,0),2))  AS DEBINT,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'6',I.VRIMPOSTONF,0),2))  AS DEBOUTES,
                    SUM(ROUND(DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'7',I.VRIMPOSTONF,0),2))  AS DEBEXT
              FROM IMPOSTONF I, IMPOSTO P
              WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU     AND
                    I.CDIMPOSTO    = P.CDIMPOSTO        AND
                    P.CDIMPOSTONFE = P_CDIMPOSTO        AND
                    ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL)))L,
            (SELECT SUM(ROUND(CASE WHEN SUBSTR(I.CDFISCOPERNF,1,1) IN ('5','6','7') THEN I.VRCONTABILNF ELSE 0 END,2)) AS VRFATU,
                    SUM(ROUND(CASE WHEN SUBSTR(I.CDFISCOPERNF,1,1) IN ('1','2','3') THEN I.VRCONTABILNF ELSE 0 END,2)) AS VRNFATU
               FROM IMPOSTONF I, CFOPLIVROMAG L, IMPOSTO P
              WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                AND I.CDIMPOSTO    = P.CDIMPOSTO
                AND P.CDIMPOSTONFE = P_CDIMPOSTO
                AND ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                AND L.CDLIVROMAG = P_CDLIVROMAG
                AND L.CDLAYOUTLMAG = '004'
                AND I.CDFISCOPERNF = L.CDCFOPLMAG)F,
            (SELECT SUM(ROUND(I.VRIMPOSTONF,2)) AS  VRANEXO2
               FROM IMPOSTONF I, NOTAFISCAL N, 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 ('1601','1602','1605') AND
                    N.CDEMPRESA    = P_CDEMPRESA             AND
                    N.CDINSCESTA   = P_CDINSCESTA            AND
                    N.IDENTRSAIDOP = 'E'                     AND
                    ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL)))E,
            (SELECT SUM(ROUND(I.VRIMPOSTONF,2)) AS  VRANEXO6
               FROM IMPOSTONF I, NOTAFISCAL N, 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
                    ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL)))S,
            (SELECT SUM(ROUND(VRDEBITONORMAL-(ST.VRCREDITONORMAL+ST.VRDEVMAPA+ST.VRRESSAMAPA),2)) AS VRIMPOSTRIB
               FROM MAPAAPURAC M, MAPAAPURESTADO ST
              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
                    M.NRSEQUMAPAPU  = ST.NRSEQUMAPAPU)ST
      WHERE M.CDEMPRESA     = P_CDEMPRESA                AND
            M.CDIMPOSTO     = P_CDIMPOSTO                AND
            M.CDINSCESTA    = P_CDINSCESTA               AND
            M.DTINIPERIODO  = P_DTINIPERIODO             AND
            M.DTFIMPERIODO  = P_DTFIMPERIODO             AND
            M.CDINSCESTA    = IE.CDINSCESTA              AND
            M.CDEMPRESA     = IE.CDEMPRESA               AND
            IE.CDINSCESTA   = P_CDINSCESTA               AND
            IE.CDEMPRESA    = P_CDEMPRESA               AND
            MR.NRSEQUMAPAPU = M.NRSEQUMAPAPU;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        V_REG_1:='';
   END;
   RETURN(V_REG_1);
END;
/