CREATE OR REPLACE FUNCTION GIA_PE_ANUAL_4(
   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_4 IN (SELECT '4'                                                                 || --Tipo de Registro
                          A.SGESTADO                                                          || --EStado
                          TRIM(TO_CHAR(SUM(NVL(VRCONTABILNF_NC,0)),'00000000000'))            || --Valor Contabil nao Contribuinte
                          TRIM(TO_CHAR(SUM(NVL(VRCONTABILNF_C,0)),'00000000000'))             || --Valor Contabil Contribuinte
                          TRIM(TO_CHAR(SUM(NVL(VRBASECALCNF_NC,0)),'00000000000'))            || --Base de Calculo nao Contribuinte
                          TRIM(TO_CHAR(SUM(NVL(VRBASECALCNF_C,0)),'00000000000'))             || --Base de Calculo Contribuinte
                          TRIM(TO_CHAR(SUM(NVL((C.VRDIFERIMENTO+C.VRSUSPENSAO),0)),'00000000000')) || --Outras
                          TRIM(TO_CHAR(SUM(NVL(VR_ST,0)),'00000000000'))           AS DSARQMAG --Substituicao Tributaria
                     FROM CTRLOUTINFGIAPE C,
                          (SELECT DECODE(IDORIGDESTNF,'F',F.SGESTADO,'C',C.SGESTADO,L.SGESTADO) AS SGESTADO,
                                  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
                             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   = 'S'
                              AND NF.CDFORNECED     = F.CDFORNECED(+)
                              AND F.IDTPENDEFORN(+) = 'P'
                              AND NF.CDCLIENTE      = C.CDCLIENTE(+)
                              AND C.IDTPENDECLIE(+) = 'P'
                              AND NF.CDFILIRECE     = 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') A
                    WHERE A.SGESTADO      = C.SGESTADO(+) 
                      AND C.CDEMPRESA(+)  = P_CDEMPRESA
                      AND C.CDINSCESTA(+) = P_CDINSCESTA
                      AND C.IDENTRSAID(+) = 'S'
                      AND C.CDANO(+)      = P_ANO                    
                    GROUP BY A.SGESTADO
                    
                    UNION
                    
                   SELECT '4'                                                                 || --Tipo de Registro
                          C.SGESTADO                                                          || --EStado
                          TRIM(TO_CHAR(SUM(NVL(VRCONTABILNF_NC,0)),'00000000000'))            || --Valor Contabil nao Contribuinte
                          TRIM(TO_CHAR(SUM(NVL(VRCONTABILNF_C,0)),'00000000000'))             || --Valor Contabil Contribuinte
                          TRIM(TO_CHAR(SUM(NVL(VRBASECALCNF_NC,0)),'00000000000'))            || --Base de Calculo nao Contribuinte
                          TRIM(TO_CHAR(SUM(NVL(VRBASECALCNF_C,0)),'00000000000'))             || --Base de Calculo Contribuinte
                          TRIM(TO_CHAR(SUM(NVL((C.VRDIFERIMENTO+C.VRSUSPENSAO),0)),'00000000000')) || --Outras
                          TRIM(TO_CHAR(SUM(NVL(VR_ST,0)),'00000000000'))           AS DSARQMAG --Substituicao Tributaria
                     FROM CTRLOUTINFGIAPE C,
                          (SELECT DECODE(IDORIGDESTNF,'F',F.SGESTADO,'C',C.SGESTADO,L.SGESTADO) AS SGESTADO,
                                  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
                             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   = 'S'
                              AND NF.CDFORNECED     = F.CDFORNECED(+)
                              AND F.IDTPENDEFORN(+) = 'P'
                              AND NF.CDCLIENTE      = C.CDCLIENTE(+)
                              AND C.IDTPENDECLIE(+) = 'P'
                              AND NF.CDFILIRECE     = 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') A
                    WHERE A.SGESTADO(+) = C.SGESTADO
                      AND C.CDEMPRESA   = P_CDEMPRESA
                      AND C.CDINSCESTA  = P_CDINSCESTA
                      AND C.IDENTRSAID  = 'S'
                      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_4.DSARQMAG, P_NRSEQARQMAG);

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

  RETURN V_CONTREG;
END;
/