Wednesday, December 7, 2016

Oracle Workflow Delegation Vacation Rule Details Query

SELECT   wit.display_name workflow_process, NVL (wi.user_key,
              (SELECT user_key
                 FROM wf_items w
                WHERE w.item_key = wi.parent_item_key
                  AND w.item_type = wi.item_type)
             ) document_number, from_role, recipient_role, status,
         mail_status, original_recipient, from_user, to_user,
         wias.activity_result_code notification_status,
         n.subject, n.begin_date notification_begin_date,
         n.end_date notification_end_date,
         wrr.begin_date delegation_start_date,
         wrr.end_date delegation_end_date
    FROM wf_notifications n,
         wf_routing_rules r,
         apps.wf_routing_rules wrr,
         wf_item_activity_statuses wias,
         wf_items wi,
         wf_item_types_vl wit
   WHERE n.from_role = r.ROLE
     AND wrr.ROLE = n.from_role
     AND wrr.action_argument = n.recipient_role
     --  AND n.notification_id = 9700654
     AND wias.item_key = n.item_key                        -- '1009738-532623'
     AND wias.notification_id = n.notification_id
     AND n.item_key = wi.item_key
     AND n.MESSAGE_TYPE = wi.item_type
     AND wit.NAME = wi.item_type
     AND n.begin_date > SYSDATE - 100    
ORDER BY n.begin_date DESC

1 comment: