Query to Get Element Fast Formula, Formula Function & Database Function

Step 1:  Create Copy of Formula Table to Convert Formula Text Long Data type to Lob

CREATE TABLE xxh_formula_f

AS

   SELECT formula_id,

          effective_start_date,

          effective_end_date,

          TO_LOB (formula_text) formula_text,

          formula_name

     FROM ff_formulas_f

    WHERE business_group_id = 10665;

Step 2: Create Global Temp Table to Load Records for Temporary Purpose

CREATE GLOBAL TEMPORARY TABLE TEMP1 (function_name varchar2(3000), db_function varchar2(3000)) ON COMMIT DELETE ROWS

Step 3: Function to Extract Formula Function and DB Function

 

CREATE OR REPLACE FUNCTION xxhr_get_function_f (p_formula_id NUMBER)

   RETURN VARCHAR2

AS

   l_formula_text   CLOB;

   CURSOR c1

   IS

      SELECT * FROM ff_functions;

   l_return_value   VARCHAR2 (3000);

   l_exists         NUMBER := 0;

   PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

   SELECT formula_text

     INTO l_formula_text

     FROM xxh_formula_f

    WHERE formula_id = p_formula_id;

   FOR i IN c1

   LOOP

      l_exists := 0;

      SELECT INSTR (l_formula_text, i.name) INTO l_exists FROM DUAL;

      IF l_exists > 0

      THEN

         INSERT INTO TEMP1

              VALUES (i.name, i.definition);

      END IF;

   END LOOP;

   FOR j IN (SELECT DISTINCT function_name, db_function

               FROM TEMP1

              WHERE function_name LIKE '%GET%')

   LOOP

      l_return_value :=

            l_return_value

         || CHR (10)

         || j.function_name

         || '-->'

         || j.db_function;

   END LOOP;

   COMMIT;

   RETURN l_return_value;

END;

Step 4: Query to Extract Element, Fast Formula, Formula Function and Corresponding Database Function

SELECT petf.element_type_id,

       petf.element_name,

       formula.formula_id,

       formula.formula_name,

       formula.formula_text,

       xxhr_get_function_f(formula.formula_id)      

  FROM pay_status_processing_rules_f rul,

       pay_element_types_f petf,

       xxh_formula_f formula

 WHERE     petf.element_type_id = rul.element_type_id

       AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date

                               AND petf.effective_end_date

       AND TRUNC (SYSDATE) BETWEEN rul.effective_start_date

                               AND rul.effective_end_date

       AND petf.business_group_id = rul.business_group_id

       AND formula.formula_id = rul.formula_id

       AND TRUNC (SYSDATE) BETWEEN formula.effective_start_date

                               AND formula.effective_end_date

       AND petf.business_group_id = 10665