Example of Function with IN and OUT Parameters

FUNCTION WITH IN AND OUT PARAMETERS
AND TESTING



DECLARE
V_DAYS NUMBER;
V_DAYS2 varchar2(200);
BEGIN
V_DAYS2 :=  FUJ_TOT_LEV_PERIOD (p_business_group_id => 81 ,
                               P_PERSON_ID => 2974,
                               P_LEAVE_ST_DT => to_date('01-FEB-2015') ,
                               P_LEAVE_TYPE => 'Sick Leave',
                               P_TOT_DAYS_PERIOD => V_DAYS);
                                --from dual
--DBMS_OUTPUT.PUT_LINE(V_DAYS);
END;    


FUNCTION FUJ_TOT_LEV_PERIOD (p_business_group_id number ,
                               P_PERSON_ID NUMBER ,
                               P_LEAVE_ST_DT DATE ,
                               P_LEAVE_TYPE IN VARCHAR2,
                               P_TOT_DAYS_PERIOD  OUT     NUMBER
                               )
  RETURN VARCHAR2 IS
  l_hire_date date;
l_curr_period number;
l_curr_period_tr number;
l_curr_period_st_dt date;
l_tot_lev_days number;
begin
SELECT DISTINCT papf.original_date_of_hire
into l_hire_date 
FROM PER_ALL_PEOPLE_F PAPF
WHERE papf.person_id = P_PERSON_ID;--26876;

l_curr_period := (MONTHS_BETWEEN(P_LEAVE_ST_DT,l_hire_date)/12)/3 ; --PERIOD  3 range of each period is 3 years.
l_curr_period_tr := trunc(l_curr_period);
l_curr_period_st_dt := ADD_MONTHS(l_hire_date,(l_curr_period_tr*3*12)); --CURRENT PERIOD START_DATE

--SELECT ADD_MONTHS(TRUNC(ADD_MONTHS(TO_DATE('01-JAN-2005'),(3*3*12)),'YY'),36) FROM DUAL--CURRENT PERIOD END_DATE
--SELECT ADD_MONTHS(TRUNC(SYSDATE,'YY'),36) FROM DUAL
select sum(Duration)
into l_tot_lev_days
from
(
SELECT ABS.DATE_START, ABS.DATE_END,( ABS.DATE_END - ABS.DATE_START ) + 1 Duration 
FROM
per_absence_attendances      ABS,
per_absence_attendance_types abt
WHERE
ABS.absence_attendance_type_id = abt.absence_attendance_type_id
--and abt.absence_attendance_type_id = 69--'Sick Leave'
and abt.name = P_LEAVE_TYPE--'Sick Leave'
AND abt.business_group_id = p_business_group_id
and EXISTS (SELECT 1
                              FROM per_all_assignments_f asg
                             WHERE --asg.assignment_id = :p_assignment_id
                                     asg.person_id = P_PERSON_ID
                               AND ABS.person_id = asg.person_id
                               AND asg.primary_flag = 'Y')                                                            
AND ABS.date_end <=  P_LEAVE_ST_DT
AND ABS.date_start >= l_curr_period_st_dt
);
dbms_output.put_line(l_tot_lev_days);
P_TOT_DAYS_PERIOD := l_tot_lev_days;
RETURN 'S';
EXCEPTION WHEN OTHERS THEN RETURN '';
end;
  
    
END;
/