Query to Extract Vacation Rules with Basic Employee Information

 SELECT papf_f.full_name from_user_name, RoutingRulesEO.ROLE from_user_id
    , ppos_f.name From_User_Function_Deptt, pjobs_f.name From_User_Designation, pgrade_f.name From_User_Band
    ,  papf_t.full_name to_user_name,RoutingRulesEO.ACTION_ARGUMENT to_user_id
    , ppos_t.name to_User_Function_Deptt, pjobs_t.name to_User_Designation, pgrade_t.name to_User_Band
    , LookupsEO.MEANING Action
    , nvl(ItemTypesEO.DISPLAY_NAME,'All')  Workflow_Name 
    , RoutingRulesEO.MESSAGE_TYPE, RoutingRulesEO.MESSAGE_NAME
    , to_char(RoutingRulesEO.begin_date, 'DD-MON-YYYY HH24:MI:SS') begin_date
    , to_char(RoutingRulesEO.end_date, 'DD-MON-YYYY HH24:MI:SS') end_date

    , trunc(nvl(RoutingRulesEO.end_date,sysdate+1000) - RoutingRulesEO.begin_date) no_of_days_for_rule

    , RoutingRulesEO.Rule_Comment

  FROM WF_ROUTING_RULES RoutingRulesEO, WF_ITEM_TYPES_VL ItemTypesEO, WF_MESSAGES_VL MessagesEO, WF_LOOKUPS LookupsEO
      ,fnd_user user_f, fnd_user user_t
      , per_all_people_f papf_f, per_all_people_f papf_T
      , per_all_assignments_f paaf_f, per_jobs pjobs_f,per_all_positions ppos_f, per_grades_tl pgrade_f
      , per_all_assignments_f paaf_t, per_jobs pjobs_t,per_all_positions ppos_t, per_grades_tl pgrade_t
 WHERE 1=1--RoutingRulesEO.ROLE = :1 
   and RoutingRulesEO.MESSAGE_TYPE = ItemTypesEO.NAME (+) and RoutingRulesEO.MESSAGE_TYPE = MessagesEO.TYPE (+) 
   and RoutingRulesEO.MESSAGE_NAME = MessagesEO.NAME (+) and RoutingRulesEO.ACTION = LookupsEO.LOOKUP_CODE 
   and LookupsEO.LOOKUP_TYPE = 'WFSTD_ROUTING_ACTIONS' 
   and sysdate between RoutingRulesEO.begin_date and RoutingRulesEO.end_date
   and nvl(RoutingRulesEO.end_date,sysdate+1000) - RoutingRulesEO.begin_date >= 15/*Rule applied for more than number of Days*/
   and RoutingRulesEO.ROLE = user_f.user_name
   and user_f.employee_id = papf_f.person_id
   --
   AND paaf_f.person_id(+) = papf_f.person_id
   --and papf_f.employee_number is not null
   AND pjobs_f.job_id(+) = paaf_f.job_id
   and nvl(paaf_f.primary_flag,'Y') = 'Y'
   AND pgrade_f.grade_id(+) = paaf_f.grade_id
   AND ppos_f.position_id(+) = paaf_f.position_id
   and sysdate between paaf_f.effective_start_date and paaf_f.effective_end_date
   --
   and sysdate between papf_f.effective_start_date and papf_f.effective_end_date
   and RoutingRulesEO.ACTION_ARGUMENT = user_t.user_name(+)   
   and user_t.employee_id = papf_t.person_id(+)
   and papf_t.effective_start_date(+)<= sysdate 
   and papf_t.effective_end_date (+) >= sysdate   
   --
   AND paaf_t.person_id(+) = papf_t.person_id
   --and papf_t.employee_number(+) is not null
   AND pjobs_t.job_id(+) = paaf_t.job_id
   and nvl(paaf_t.primary_flag(+),'Y') = 'Y'
   AND pgrade_t.grade_id(+) = paaf_t.grade_id
   AND ppos_t.position_id(+) = paaf_t.position_id
   and paaf_t.effective_start_date (+) <= sysdate
   and paaf_t.effective_end_date(+) >= sysdate
   --
 ORDER BY RoutingRulesEO.ROLE,RoutingRulesEO.ACTION_ARGUMENT,nvl(ItemTypesEO.DISPLAY_NAME,'All'),RoutingRulesEO.ACTION_ARGUMENT;