Use case to Schedule and Run Apply Workflows

This use case demonstrates how to run a lineage of the APPLY workflow, which is scheduled to run the WF_RUN_API.

To run the lineage, specify the INSUR_CUST_LTV_SAMPLE APPLY node and use the RERUN_NODE_CHILDREN run mode. The use case, as demonstrated in the example, does the following:

• Schedules the APPLY workflow to run daily from mid night of 12/31/2014 to 12/31/2015 in EST zone

• Executes the WF_RUN API

• Polls the status of the workflow from the ODMR_USER_PROJECT_WORKFLOW view

• Prints the failed nodes

Example 8-1 Schedule and Run the Apply Workflow, Poll Status and Print Node Failures

CONNECT DMUSER/DMUSER
SET SERVEROUTPUT ON 
DECLARE
    v_jobId VARCHAR2(30) := NULL;
    v_status VARCHAR2(30) := NULL;
    v_projectName VARCHAR2(30) := 'Project';
    v_workflow_name VARCHAR2(30) := 'apply_workflow';
    v_node VARCHAR2(30) := 'INSUR_CUST_LTV_SAMPLE APPLY';
    v_run_mode VARCHAR2(30) := ODMRSYS.ODMR_WORKFLOW.RERUN_NODE_CHILDREN;
    v_failure NUMBER := 0;
    v_nodes ODMRSYS.ODMR_OBJECT_NAMES := ODMRSYS.ODMR_OBJECT_NAMES();
BEGIN
    v_nodes.extend();
    v_nodes(v_nodes.count) := v_node;
    v_jobId := ODMRSYS.ODMR_WORKFLOW.WF_RUN(p_project_name => v_projectName,
                       p_workflow_name => v_workflow_name,
                       p_node_names => v_nodes,
                       p_run_mode => v_run_mode,
                       p_start_date => '31-DEC-14 12.00.00 AM AMERICA/NEW_YORK',
                       p_repeat_interval => 'FREQ= DAILY',
                       p_end_date => '31-DEC-15 12.00.00 AM AMERICA/NEW_YORK'));
    DBMS_OUTPUT.PUT_LINE('Job: '||v_jobId);
    -- wait for workflow to run to completion
    LOOP
      SELECT STATUS INTO v_status FROM ODMR_USER_PROJECT_WORKFLOW
      WHERE WORKFLOW_NAME = v_workflow_name;
      IF (v_status IN ('SCHEDULED', 'ACTIVE')) THEN
         DBMS_LOCK.SLEEP(10); -- wait for 10 secs
      ELSE
        EXIT; -- workflow run completes
      END IF;
    END LOOP;
    -- print all failed nodes (see example above)
  EXCEPTION WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error: '||SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK(), 1, 4000));
END;

• Querying Scoring Result
  You can query the scoring result after the workflow run completes successfully.