CREATE OR REPLACE PROCEDURE wf_jump ( p_itemkey        IN VARCHAR2
                                    , p_activity_id    IN PLS_INTEGER   DEFAULT NULL
                                    , p_process_name   IN VARCHAR2      DEFAULT NULL
                                    , p_instance_label IN VARCHAR2      DEFAULT NULL
                                    , p_itemtype       IN VARCHAR2      DEFAULT NULL
                                    , p_process_date   IN DATE          DEFAULT NULL ) IS

  CURSOR c_get_actid ( cp_itemtype       IN VARCHAR2
                     , cp_instance_label IN VARCHAR2
                     , cp_process_name   IN VARCHAR2 ) IS
    SELECT instance_id
    FROM   wf_process_activities
    WHERE  process_item_type = cp_itemtype
    AND    instance_label    = cp_instance_label
    AND    process_name      = cp_process_name
    ORDER BY process_version DESC;

  CURSOR c_get_actid_date ( cp_itemtype       IN VARCHAR2
                          , cp_instance_label IN VARCHAR2
                          , cp_process_name   IN VARCHAR2
                          , cp_process_date   IN DATE ) IS
    SELECT wpa.instance_id
    FROM   wf_process_activities   wpa
    ,      wf_activities           wfa
    WHERE  wfa.item_type             = wpa.process_item_type
    AND    wfa.name                  = wpa.process_name
    AND    wfa.version               = wpa.process_version
    AND    wpa.process_item_type     = cp_itemtype
    AND    wpa.instance_label        = cp_instance_label
    AND    wpa.process_name          = cp_process_name
    AND    wfa.begin_date            < cp_process_date
    AND    NVL(wfa.end_date,SYSDATE) > cp_process_date;

  CURSOR c_get_item_details ( cp_actid IN PLS_INTEGER ) IS
    SELECT process_item_type      itemtype
    ,      process_name
    ,      instance_label
    ,      process_version
    FROM   wf_process_activities
    WHERE  instance_id = cp_actid;

  CURSOR c_current_details ( cp_itemtype IN VARCHAR2
                           , cp_itemkey  IN VARCHAR2 ) IS
    SELECT process_activity    actid
    ,      notification_id     nid
    FROM   wf_item_activity_statuses   wias
    ,      wf_process_activities       wpa
    WHERE  wias.process_activity = wpa.instance_id
    AND    wias.item_type        = cp_itemtype
    AND    wias.item_key         = cp_itemkey
    AND    wias.activity_status  = 'NOTIFIED';

  CURSOR c_subprocesses ( cp_itemtype        IN VARCHAR2
                        , cp_process_version IN PLS_INTEGER
                        , cp_actid           IN PLS_INTEGER ) IS
    SELECT DISTINCT instance_id    actid
    FROM   wf_process_activities   wpa
    WHERE  process_item_type = cp_itemtype
    AND    process_version   = cp_process_version
    AND    process_name     != 'ROOT'
    START WITH instance_id   = cp_actid
    CONNECT BY activity_name = PRIOR process_name;

  r_current_details           c_current_details%ROWTYPE;
  r_item_details              c_get_item_details%ROWTYPE;

  e_missing_parameters        EXCEPTION;
  e_missing_parameters#       PLS_INTEGER  := -20000;
  e_missing_actid             EXCEPTION;
  e_missing_actid#            PLS_INTEGER  := -20001;

  PRAGMA EXCEPTION_INIT ( e_missing_parameters,      -20000 );
  PRAGMA EXCEPTION_INIT ( e_missing_actid,           -20001 );

  v_actid            PLS_INTEGER;
  v_process_version  PLS_INTEGER;

BEGIN
  IF p_activity_id IS NULL THEN
    -- If activity ID is not passed in, then need to derive the activity ID
    -- from the workflow tables
    IF p_instance_label IS NULL OR p_itemtype IS NULL OR p_process_name IS NULL THEN
      -- Missing mandatory parameters
      RAISE e_missing_parameters;
    END IF;

    IF p_process_date IS NOT NULL THEN
      OPEN  c_get_actid_date ( cp_itemtype       => p_itemtype
                             , cp_instance_label => p_instance_label
                             , cp_process_name   => p_process_name
                             , cp_process_date   => p_process_date );
      FETCH c_get_actid_date INTO v_actid;
      CLOSE c_get_actid_date;
    ELSE
      OPEN  c_get_actid ( cp_itemtype       => p_itemtype
                        , cp_instance_label => p_instance_label
                        , cp_process_name   => p_process_name );
      FETCH c_get_actid INTO v_actid;
      CLOSE c_get_actid;
    END IF;

    IF v_actid IS NULL THEN
      RAISE e_missing_actid;
    END IF;
  ELSE
    v_actid := p_activity_id;
  END IF;

  OPEN  c_get_item_details ( cp_actid => v_actid );
  FETCH c_get_item_details INTO r_item_details;
  CLOSE c_get_item_details;

  OPEN  c_current_details ( cp_itemtype => r_item_details.itemtype
                          , cp_itemkey  => p_itemkey );
  FETCH c_current_details INTO r_current_details;
  CLOSE c_current_details;

  UPDATE wf_item_activity_statuses
  SET    activity_status      = 'COMPLETE'
  ,      activity_result_code = 'Manual Transition'
  ,      end_date             = SYSDATE
  WHERE  item_type            = r_item_details.itemtype
  AND    item_key             = p_itemkey
  AND    process_activity     = r_current_details.actid;

  FOR i IN c_subprocesses ( cp_itemtype        => r_item_details.itemtype
                          , cp_process_version => r_item_details.process_version
                          , cp_actid           => r_current_details.actid ) LOOP
    UPDATE wf_item_activity_statuses
    SET    activity_status = 'COMPLETE'
    ,      activity_result_code = 'Manual Transition'
    ,      end_date             = SYSDATE
    WHERE  item_key             = p_itemkey
    AND    process_activity     = i.actid
    AND    item_type            = r_item_details.itemtype;
    
  END LOOP;
  
  UPDATE wf_notifications
  SET    status   = 'CLOSED'
  ,      end_date = SYSDATE
  ,      user_comment = 'Manual transition'
  WHERE  notification_id = r_current_details.nid;
    
  WF_ITEM_ACTIVITY_STATUS.create_status ( itemtype  => r_item_details.itemtype
                                        , itemkey   => p_itemkey
                                        , actid     => v_actid
                                        , status    => 'COMPLETE'
                                        , result    => 'Forced Completion'
                                        , beginning => SYSDATE );
                                        
  WF_ENGINE.HandleError ( itemtype => r_item_details.itemtype
                        , itemkey  => p_itemkey
                        , activity => r_item_details.process_name||':'||r_item_details.instance_label
                        , command  => 'RETRY' );
                        
EXCEPTION
  WHEN e_missing_parameters THEN
    RAISE_APPLICATION_ERROR(e_missing_parameters#,'Missing mandatory parameters in call to WF_JUMP');
    
  WHEN e_missing_actid THEN
    RAISE_APPLICATION_ERROR(e_missing_actid#,'Activity ID could not be found');
    
  WHEN OTHERS THEN
    RAISE;

END wf_jump;
/