Skip Rule for an Year using Oracle Fast Forumula

Scenario:

The Element Processed once from the date it was attached should not be Processed again during that period

Example:

Element has been Attached and Processed on 01-FEB-2016, so therefore the life span of the element will be from 01-FEB-2016 to 31-JAN-2017. This element should be processed again during this time period.

Step 1: Create Database Function

CREATE OR REPLACE FUNCTION xxhr_skip_one_year_rule_f (

   p_assignment_id     IN NUMBER,

   p_element_type_id   IN NUMBER,

   p_date_earned          DATE)

   RETURN VARCHAR2

IS

   l_first_effective_date   DATE := NULL;

   l_element_st_dt          DATE := NULL;

   l_element_ed_date        DATE := NULL;

   l_count                  NUMBER := 0;

BEGIN

   SELECT peef.effective_start_date

     INTO l_first_effective_date

     FROM pay_element_entries_f peef

    WHERE     peef.assignment_id = p_assignment_id

          AND peef.element_type_id = p_element_type_id

          AND peef.effective_start_date =

                 (SELECT MIN (peef1.effective_start_date)

                    FROM pay_element_entries_f peef1

                   WHERE     peef1.assignment_id = p_assignment_id

                         AND peef1.element_type_id = p_element_type_id);

   --Get Element Year Cycle

   SELECT st, ed

     INTO l_element_st_dt, l_element_ed_date

     FROM (SELECT TRUNC (l_first_effective_date, 'MON') st,

                  ADD_MONTHS (TRUNC (l_first_effective_date, 'MON'), 12) - 1

                     ed

             FROM DUAL

           UNION

               SELECT ADD_MONTHS (TRUNC (l_first_effective_date, 'MON'),

                                  LEVEL * 12)

                         st,

                      ADD_MONTHS (

                           ADD_MONTHS (TRUNC (l_first_effective_date, 'MON'), 12)

                         - 1,

                         LEVEL * 12)

                         ed

                 FROM DUAL

           CONNECT BY LEVEL < 500)

    WHERE p_date_earned BETWEEN st AND ed;

dbms_output.put_line('l_element_st_dt: ' ||l_element_st_dt);

dbms_output.put_line('l_element_ed_date: ' ||l_element_ed_date);

   -- Check if the Element has been Processed Earlier

   SELECT COUNT (*)

     INTO l_count

     FROM pay_element_entries_f peef

    WHERE     element_type_id = p_element_type_id

          AND peef.assignment_id = p_assignment_id

          AND pay_paywsmee_pkg.processed (peef.element_entry_id,

                                       peef.original_entry_id,

                                       peef.element_type_id,

                                       peef.entry_type,

                                       peef.effective_start_date) = 'Y'

          AND peef.effective_start_date BETWEEN l_element_st_dt

                                            AND l_element_ed_date;

   IF l_count > 0

   THEN

      RETURN 'Y';

   ELSE

      RETURN 'N';

   END IF;

   RETURN 'N';

EXCEPTION

   WHEN OTHERS

   THEN

     DBMS_OUTPUT.PUT_LINE(SQLERRM);

      RETURN 'N';

END;

Step 2: Create Formula Function

 Step 3: Create Formula

 

Step 4: Attach the Skip rule to the element