CREATE OR REPLACE FUNCTION GIA_PE_ANUAL_3(
   P_CONTREG      IN NUMBER,
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_ANO          IN VARCHAR2,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER
AS
   V_CONTREG  NUMBER;
BEGIN
   V_CONTREG := NVL(P_CONTREG,0);

   FOR C_REG_3 IN (SELECT '3'                                                                      || --Tipo de Registro
                          D.SGESTADO                                                               || --Estado
                          TRIM(TO_CHAR(SUM(NVL(D.VRCONTABIL,0)),'00000000000'))                    || --Valor Contabil
                          TRIM(TO_CHAR(SUM(NVL(D.VRBASECALC,0)),'00000000000'))                    || --Base de Calculo
                          TRIM(TO_CHAR(SUM(NVL((C.VRDIFERIMENTO+C.VRSUSPENSAO),0)),'00000000000')) || --Outras
                          '00000000000'                                                            || --Energia/Petroleo
                          TRIM(TO_CHAR(SUM(NVL(D.VROUTPROD,0)),'00000000000')) AS DSARQMAG --Outros produtos
                     FROM CTRLOUTINFGIAPE C,
                          (SELECT DECODE(IDORIGDESTNF,'F',F.SGESTADO,'C',C.SGESTADO,L.SGESTADO) AS SGESTADO,
                                  SUM(IP.VRCONTABILNF) AS VRCONTABIL, SUM(DECODE(CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VRBASECALC,
                                  SUM(VALOR_IMPOSTO_NFE(NF.CDFILIAL, NF.NRLANCTONF, '80', 'B','1')) AS VROUTPROD
                             FROM IMPOSTONF IP, NOTAFISCAL NF, MAPAAPURAC MP, IMPOSTO I,
                                  ENDEFORN F,   ENDECLIE C,    ENDEFILI L
                            WHERE IP.NRLANCTONF     = NF.NRLANCTONF
                              AND IP.CDFILIAL       = NF.CDFILIAL
                              AND NF.IDENTRSAIDOP   = 'E'
                              AND NF.CDFORNECED     = F.CDFORNECED(+)
                              AND F.IDTPENDEFORN(+) = 'P'
                              AND NF.CDCLIENTE      = C.CDCLIENTE(+)
                              AND C.IDTPENDECLIE(+) = 'P'
                              AND NF.CDFILIEMIT     = L.CDFILIAL(+)
                              AND L.IDTPENDEFILI(+) = 'P'
                              AND IP.CDIMPOSTO      = I.CDIMPOSTO
                              AND I.CDIMPOSTONFE    = P_CDIMPOSTO
                              AND IP.NRSEQUMAPAPU   = MP.NRSEQUMAPAPU
                              AND MP.CDEMPRESA      = P_CDEMPRESA
                              AND MP.CDINSCESTA     = P_CDINSCESTA
                              AND TO_CHAR(MP.DTINIPERIODO,'YYYY') = P_ANO
                              AND MP.CDIMPOSTO      = P_CDIMPOSTO
                              AND DECODE(IDORIGDESTNF,'F',F.SGESTADO,'C',C.SGESTADO,L.SGESTADO) <> 'PE'
                            GROUP BY DECODE(IDORIGDESTNF,'F',F.SGESTADO,'C',C.SGESTADO,L.SGESTADO)) D
                    WHERE D.SGESTADO      = C.SGESTADO(+)
                      AND C.CDINSCESTA(+) = P_CDINSCESTA
                      AND C.CDEMPRESA(+)  = P_CDEMPRESA
                      AND C.CDANO(+)      = P_ANO
                      AND C.IDENTRSAID(+) = 'E'
                    GROUP BY D.SGESTADO

                    UNION

                   SELECT '3'                                                                      || --Tipo de Registro
                          C.SGESTADO                                                               || --Estado
                          TRIM(TO_CHAR(SUM(NVL(D.VRCONTABIL,0)),'00000000000'))                    || --Valor Contabil
                          TRIM(TO_CHAR(SUM(NVL(D.VRBASECALC,0)),'00000000000'))                    || --Base de Calculo
                          TRIM(TO_CHAR(SUM(NVL(C.VRDIFERIMENTO+C.VRSUSPENSAO,0)),'00000000000'))   || --Outras
                          '00000000000'                                                            || --Energia/Petroleo
                          TRIM(TO_CHAR(SUM(NVL(D.VROUTPROD,0)),'00000000000')) AS DSARQMAG --Outros produtos
                     FROM CTRLOUTINFGIAPE C,
                          (SELECT DECODE(IDORIGDESTNF,'F',F.SGESTADO,'C',C.SGESTADO,L.SGESTADO) AS SGESTADO,
                                  SUM(IP.VRCONTABILNF) AS VRCONTABIL, SUM(DECODE(CDFISCALNF,'1',IP.VRBASECALCNF,0)) AS VRBASECALC,
                                  SUM(VALOR_IMPOSTO_NFE(NF.CDFILIAL, NF.NRLANCTONF, '80', 'B','1')) AS VROUTPROD
                             FROM IMPOSTONF IP, NOTAFISCAL NF, MAPAAPURAC MP, IMPOSTO I,
                                  ENDEFORN F,   ENDECLIE C,    ENDEFILI L
                            WHERE IP.NRLANCTONF     = NF.NRLANCTONF
                              AND IP.CDFILIAL       = NF.CDFILIAL
                              AND NF.IDENTRSAIDOP   = 'E'
                              AND NF.CDFORNECED     = F.CDFORNECED(+)
                              AND F.IDTPENDEFORN(+) = 'P'
                              AND NF.CDCLIENTE      = C.CDCLIENTE(+)
                              AND C.IDTPENDECLIE(+) = 'P'
                              AND NF.CDFILIEMIT     = L.CDFILIAL(+)
                              AND L.IDTPENDEFILI(+) = 'P'
                              AND IP.CDIMPOSTO      = I.CDIMPOSTO
                              AND I.CDIMPOSTONFE    = P_CDIMPOSTO
                              AND IP.NRSEQUMAPAPU   = MP.NRSEQUMAPAPU
                              AND MP.CDEMPRESA      = P_CDEMPRESA
                              AND MP.CDINSCESTA     = P_CDINSCESTA
                              AND TO_CHAR(MP.DTINIPERIODO,'YYYY') = P_ANO
                              AND MP.CDIMPOSTO      = P_CDIMPOSTO
                              AND DECODE(IDORIGDESTNF,'F',F.SGESTADO,'C',C.SGESTADO,L.SGESTADO) <> 'PE'
                            GROUP BY DECODE(IDORIGDESTNF,'F',F.SGESTADO,'C',C.SGESTADO,L.SGESTADO)) D
                    WHERE D.SGESTADO(+)   = C.SGESTADO
                      AND C.CDINSCESTA    = P_CDINSCESTA
                      AND C.CDEMPRESA     = P_CDEMPRESA
                      AND C.IDENTRSAID    = 'E'
                      AND C.CDANO         = P_ANO
                      AND C.SGESTADO     <> 'PE'
                    GROUP BY C.SGESTADO) LOOP

     INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
     VALUES
        (LPAD(RTRIM(V_CONTREG), 10, '0'), LPAD(V_CONTREG,6,'0')||C_REG_3.DSARQMAG, P_NRSEQARQMAG);

     V_CONTREG := NVL(V_CONTREG,0) + 1;
  END LOOP;

  RETURN V_CONTREG;
END;
/