FUNCTION Cf_1formula 
RETURN CHAR 
IS 
  v_leavetype CHAR(40); 
BEGIN 
    SELECT LT.description 
    INTO   v_leavetype 
    FROM   hrm_attendance L, 
           hrm_leavetype LT, 
           hrm_employees E 
    WHERE  E.employeecode = L.employeecode 
           AND L.employeecode = :EMPLOYEECODE 
           AND L.leavetype = LT.leavetype 
           AND L.leavetype NOT IN ( 'PPP' ); 
 
    RETURN v_leavetype; 
EXCEPTION 
  WHEN OTHERS THEN 
             RETURN NULL; 
END;

FUNCTION Cf_2formula 
RETURN DATE 
IS 
  v_leavedate DATE; 
BEGIN 
    SELECT L.leavedate 
    INTO   v_leavedate 
    FROM   hrm_attendance L, 
           hrm_leavetype LT, 
           hrm_employees E 
    WHERE  E.employeecode = L.employeecode 
           AND L.employeecode = :EMPLOYEECODE 
           AND L.leavetype = LT.leavetype 
           AND L.leavetype NOT IN ( 'PPP' ); 
 
    RETURN v_leavedate; 
EXCEPTION 
  WHEN OTHERS THEN 
             RETURN NULL; 
END;

SELECT e.employeecode,
       e.name,
       e.fathersname,
       des.designation,
       dep.department,
       r.cl,
       r.sl,
       Nvl(r.el, '0')                              "EL",
       r.cpl,
       r.leavedate                                 "FROM",
       r.todate,
       r.days,
       l.leavedate,
       Nvl(Initcap(lt.description), 'No Leave Yet')"LEAVETYPE"
FROM   hrm_employees e,
       hrm_leavereg r,
       hrm_attendance l,
       hrm_designation des,
       hrm_department dep,
       hrm_leavetype lt
WHERE  e.employeecode = r.employeecode
       AND e.employeecode = l.employeecode
       AND e.designationcode = des.designationcode
       AND e.departmentcode = dep.departmentcode
       AND l.leavetype = lt.leavetype(+)
       AND lt.leavetype NOT IN ( 'PPP' )
ORDER  BY l.leavedate;