CREATE OR REPLACE FUNCTION GIA_PE_ANUAL_2(
   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;
   V_VRDIFENTR   NUMBER;
   V_VRSUSPENTR  NUMBER;
   V_VRDIFSAID   NUMBER;
   V_VRSUSPSAID  NUMBER;
BEGIN
   V_CONTREG := NVL(P_CONTREG,0);

   BEGIN
      SELECT SUM(DECODE(IDENTRSAID,'E',VRDIFERIMENTO,0)) AS VRDIFENTR,
             SUM(DECODE(IDENTRSAID,'S',VRDIFERIMENTO,0)) AS VRDIFSAID,
             SUM(DECODE(IDENTRSAID,'E',VRSUSPENSAO,0)) AS VRSUSPENTR,
             SUM(DECODE(IDENTRSAID,'S',VRSUSPENSAO,0)) AS VRSUSPSAID
        INTO V_VRDIFENTR, V_VRSUSPENTR, V_VRDIFSAID, V_VRSUSPSAID
        FROM CTRLOUTINFGIAPE
       WHERE CDEMPRESA  = P_CDEMPRESA
         AND CDINSCESTA = P_CDINSCESTA
         AND CDANO      = P_ANO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRDIFENTR  := 0;
         V_VRSUSPENTR := 0;
         V_VRDIFSAID  := 0;
         V_VRSUSPSAID := 0;
   END;

   FOR C_REG_2 IN (SELECT LPAD(V_CONTREG,6,'0')                                                            || -- Sequeencial
                          '2'                                                                              || -- Tipo de Registro
                          TRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'E',VRCONTABILNF_C,0)),'000000000000000'))  || -- Valor Contabil
                          TRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'E',VRBASECALCNF_C,0)),'000000000000000'))  || -- Base de Calculo
                          TRIM(TO_CHAR(NVL(V_VRDIFENTR,0)+NVL(V_VRSUSPENTR,0),'000000000000000'))          || -- Outras
                          '000000000000000'                                                                || -- Petroleo/Energia
                          TRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'E',VR_ST,0)),'000000000000000'))           || -- Outros Produtos
                          TRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'S',VRCONTABILNF_NC,0)),'000000000000000')) || -- Valor Contabil Nao Contribuinte
                          TRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'S',VRCONTABILNF_C,0)),'000000000000000'))  || -- Valor Contabil Contribuinte
                          TRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'S',VRBASECALCNF_NC,0)),'000000000000000')) || -- Base de Calculo Nao Contribuinte
                          TRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'S',VRBASECALCNF_C,0)),'000000000000000'))  || -- Base de Calculo Contribuinte
                          TRIM(TO_CHAR(NVL(V_VRDIFSAID,0)+NVL(V_VRSUSPSAID,0),'000000000000000'))          || -- Outras
                          TRIM(TO_CHAR(SUM(DECODE(IDENTRSAIDOP,'S',VR_ST,0)),'000000000000000'))    AS DSARQMAG -- ICMS Cobrado p/Subst. Tributaria
                     FROM (SELECT CASE WHEN CDFISCOPERNF IN ('5258','5307','5357','6107','6108','6258','6307','6357') THEN VRCONTABILNF ELSE 0 END AS VRCONTABILNF_NC,
                                  CASE WHEN CDFISCOPERNF NOT IN ('5258','5307','5357','6107','6108','6258','6307','6357') THEN VRCONTABILNF ELSE 0 END AS VRCONTABILNF_C,
                                  DECODE(CDFISCALNF, '1', CASE WHEN CDFISCOPERNF IN ('5258','5307','5357','6107','6108','6258','6307','6357') THEN VRBASECALCNF ELSE 0 END, 0) AS VRBASECALCNF_NC,
                                  DECODE(CDFISCALNF, '1', CASE WHEN CDFISCOPERNF NOT IN ('5258','5307','5357','6107','6108','6258','6307','6357') THEN VRBASECALCNF ELSE 0 END, 0) AS VRBASECALCNF_C,
                                  DECODE(CDFISCALNF, '3', VRCONTABILNF,0) AS VROUTRAS,
                                  VALOR_IMPOSTO_NFE(NF.CDFILIAL, NF.NRLANCTONF, '80', 'B','1') AS VR_ST, NF.IDENTRSAIDOP
                             FROM IMPOSTONF IP, NOTAFISCAL NF, MAPAAPURAC MP, IMPOSTO I,
                                  ENDEFORN F, ENDECLIE C, ENDEFILI L
                            WHERE NF.CDFORNECED     = F.CDFORNECED(+)
                              AND F.IDTPENDEFORN(+) = 'P'
                              AND NF.CDCLIENTE      = C.CDCLIENTE(+)
                              AND C.IDTPENDECLIE(+) = 'P'
                              AND DECODE(NF.IDENTRSAIDOP,'S',NF.CDFILIRECE,NF.CDFILIEMIT) = L.CDFILIAL(+)
                              AND L.IDTPENDEFILI(+) = 'P'
                              AND IP.NRLANCTONF     = NF.NRLANCTONF
                              AND IP.CDFILIAL       = NF.CDFILIAL
                              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')) LOOP

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

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

  RETURN V_CONTREG;
END;
/