Wednesday, December 7, 2016

Oracle Workflow Delegation Vacation Rule Query

-- delegation query or Vacation Query

SELECT   wrr.ROLE from_role, ppf1.full_name from_employee,
         wl.meaning rule_type, wit.display_name specific_workflow,
                                                          -- wrr.MESSAGE_TYPE,
         wrr.rule_comment, wrr.action_argument to_role,
         ppf2.full_name to_employee, wrr.begin_date, wrr.end_date
    FROM apps.wf_routing_rules wrr,
         wf_lookups wl,
         fnd_user fu1,
         fnd_user fu2,
         wf_item_types_vl wit,
         per_all_people_f ppf1,
         per_all_people_f ppf2
   WHERE wl.lookup_type = 'WFSTD_ROUTING_ACTIONS'                       -- 456
     AND wrr.action = wl.lookup_code
     AND fu1.user_name(+) = wrr.ROLE
     AND fu2.user_name(+) = wrr.action_argument
     AND SYSDATE BETWEEN fu1.start_date(+) AND NVL (fu1.end_date(+), SYSDATE)
     AND SYSDATE BETWEEN fu2.start_date(+) AND NVL (fu2.end_date(+), SYSDATE)
     AND SYSDATE BETWEEN wrr.begin_date AND NVL (wrr.end_date, SYSDATE)
     AND wit.NAME(+) = wrr.MESSAGE_TYPE
     AND fu1.employee_id = ppf1.person_id(+)
     AND fu2.employee_id = ppf2.person_id(+)
     AND SYSDATE BETWEEN ppf1.effective_start_date(+) AND NVL (ppf1.effective_end_date(+),
     AND SYSDATE BETWEEN ppf2.effective_start_date(+) AND NVL (ppf2.effective_end_date(+),
ORDER BY begin_date;

No comments:

Post a Comment