CREATE OR REPLACE FUNCTION UTILIZA_FORMULA(
   P_CDIMPOSTO    IN MAPAAPURAC.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN MAPAAPURAC.CDEMPRESA%TYPE,
   P_CDINSCRICAO  IN MAPAAPURAC.CDINSCESTA%TYPE,
   P_NRSEQUMAPAPU IN MAPAAPURAC.NRSEQUMAPAPU%TYPE) RETURN VARCHAR
AS
   V_STFORMULA   VARCHAR2(4000);
   V_STRESULTADO VARCHAR2(200);
   V_STUTFORMULA VARCHAR2(01);
   V_SQLFORMULA  VARCHAR2(4200);
   V_SQLCALCULO  VARCHAR2(300);
   V_NMTABELA    VARCHAR2(15);
BEGIN
   V_STUTFORMULA := 'N';

   /******** Verifica se algum dos impostos relacionados esta parametrizado para utilizar formula na apuracao ********/
   SELECT MAX(IDUTFORMPADIMP) AS IDUTFORMPADIMP
     INTO V_STUTFORMULA
     FROM IMPOSTO
    WHERE CDIMPOSTONFE = P_CDIMPOSTO;

   IF V_STUTFORMULA = 'S' THEN
      V_STUTFORMULA := 'N';

      /******** Busca as formulas cadastradas ********/
      FOR C_FORMULA IN (SELECT CDITEMMAPA, DSFORMULAFISC
                          FROM FORMULAFISCAL F, IMPOSTO I
                         WHERE F.CDIMPOSTO     = I.CDIMPOSTO
                           AND I.CDIMPOSTONFE  = P_CDIMPOSTO
                           AND CDEMPRESA       = P_CDEMPRESA
                           AND (CDINSCESTAMUNI = P_CDINSCRICAO OR CDINSCESTAMUNI IS NULL)
                           AND DSFORMULAFISC IS NOT NULL
                         ORDER BY F.CDIMPOSTO, CDEMPRESA, CDINSCESTAMUNI, CDFORMULAFISC) LOOP

         V_STUTFORMULA := 'S';
         V_STFORMULA := REPLACE(C_FORMULA.DSFORMULAFISC,',','.');

         /******** Monta o select, considerando a formula cadastrada pelo usuario ********/
         V_SQLFORMULA := 'SELECT ' || V_STFORMULA || ' AS RESULTADO
                            FROM MAPARECUP MR, MAPAAPURAC MP
                           WHERE MP.NRSEQUMAPAPU = ' || P_NRSEQUMAPAPU || '
                             AND MP.NRSEQUMAPAPU = MR.NRSEQUMAPAPU';

         /******** Executa o select e faz o tratamento de erros ********/
         BEGIN
            EXECUTE IMMEDIATE V_SQLFORMULA INTO V_STRESULTADO;
         EXCEPTION
            WHEN ZERO_DIVIDE THEN
               RETURN('DIVIDE_ZERO');
            WHEN OTHERS THEN
               RETURN('VER_FORMULA');
         END;

         IF TO_NUMBER(V_STRESULTADO) < 0 THEN
            V_STRESULTADO := '0.00';
         ELSE
            V_STRESULTADO := REPLACE(V_STRESULTADO,',','.');
         END IF;

         IF C_FORMULA.CDITEMMAPA = 'VRTOTAIMPOS' THEN
            V_NMTABELA := 'MAPAAPURAC';
         ELSE
            V_NMTABELA := 'MAPARECUP';
         END IF;

         V_SQLCALCULO := 'UPDATE ' || V_NMTABELA || '
                             SET ' || C_FORMULA.CDITEMMAPA || ' = ' ||  V_STRESULTADO || '
                           WHERE NRSEQUMAPAPU = ' || P_NRSEQUMAPAPU;
         BEGIN
            EXECUTE IMMEDIATE V_SQLCALCULO;
            COMMIT;
         EXCEPTION
            WHEN OTHERS THEN
               RETURN('VER_FORMULA');
         END;
      END LOOP;
      IF V_STUTFORMULA = 'N' THEN
         RETURN('N_FORMULA');
      ELSE
         RETURN('OK');
      END IF;
   END IF;
END;
/