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
