DECLARE
  clobvar CLOB := EMPTY_CLOB;
  data_str varchar2(32767);
BEGIN

  data_str:= 'create or replace PROCEDURE "WB_TASK_RUNNER_PROC"
(
    p_parent_id in number,
    ---
    p_job_object_type_name in varchar2,
    p_job_object_name in varchar2,
    p_job_dep_location_name in varchar2,
    ---
    p_job_task_name in varchar2,
    p_job_exec_location_name in varchar2,
    ---
    p_activity_task_name in varchar2,
    p_activity_exec_location_name in varchar2 -- OWB RUNNER MACRO EXTRA COMMA        
    --
    -- Executed Object parameters
    -- OWB RUNNER MACRO PARAMETER LIST
)
as
    l_job_audit_execution_id number;
    l_audit_execution_id number;
    l_error_message varchar2(2000);
    l_debug_message varchar2(2000);
    l_stream_id number(22);
    l_result number;
-- OWB RUNNER MACRO LOCAL VARIABLES
  
  function equals
  (
    str1 IN VARCHAR2,
    str2 IN VARCHAR2
  ) 
  return boolean 
  is
  begin
    if(str1 is null and str2 is not null) then return false; end if;
    if(str1 is not null and str2 is null) then return false; end if;
    if(str1 != str2) then return false; end if;
    
    return true;
  end;
  
  procedure println
  (
    str IN VARCHAR2
  ) 
  as
  begin
    l_debug_message:= l_debug_message ||str;    
  end;    
    
  procedure call_custom_proc (p_name IN VARCHAR2)   
  as l_message varchar2(4056);  
  BEGIN    
    EXECUTE IMMEDIATE ''BEGIN '' || p_name || ''('' ||                    
    ''  p_job_name=>:1'' ||        
    '', p_message=>:2'' ||      
    '', p_job_audit_execution_id=>:3'' ||        
    '', p_audit_execution_id=>:4'' ||      
    '', p_parent_id=>:5'' ||       
    '', p_job_object_type_name=>:6'' ||       
    '', p_job_object_name=>:7'' ||        
    '', p_job_dep_location_name=>:8'' ||       
    '', p_job_task_name=>:9'' ||       
    '', p_job_exec_location_name=>:10'' ||       
    '', p_activity_task_name=>:11'' ||       
    '', p_activity_exec_location_name=>:12'' || -- OWB RUNNER MACRO CALL CUSTOM PROC DEF
    ''); END;''    
    USING                           
      ''WB_TASK_RUNNER_PROC''       
    , in out l_message 
    , l_job_audit_execution_id
    , l_audit_execution_id
    , p_parent_id
    , p_job_object_type_name
    , p_job_object_name
    , p_job_dep_location_name
    , p_job_task_name
    , p_job_exec_location_name
    , p_activity_task_name
    , p_activity_exec_location_name -- OWB RUNNER MACRO CALL CUSTOM PROC VALUE    
    ;     
    
    println(''call_custom_proc '' || p_name);    
    println(l_message);  
  EXCEPTION  
  WHEN others THEN    
    IF SQLCODE <> -6550 THEN      
      RAISE;    
    END IF;  
 END;      
    
begin

    println(''Begin.'');
    
-- OWB RUNNER MACRO COPY VARIABLES    

  println(''l_job_audit_execution_id='' || l_job_audit_execution_id);  
  println(''p_job_object_type_name='' || p_job_object_type_name); 
  println(''p_job_object_name='' || p_job_object_name); 
  println(''p_job_dep_location_name='' || p_job_dep_location_name); 
  println(''p_job_task_name='' || p_job_task_name); 
  println(''p_job_exec_location_name='' || p_job_exec_location_name); 
  println(''p_activity_task_name='' || p_activity_task_name); 
  println(''p_activity_exec_location_name='' || p_activity_exec_location_name);
  -- OWB RUNNER MACRO PRINT PARAMETER LIST 
  println('''');

    -- Work around to make sure that package wb_rti_workflow_util is compiled
    begin
      WB_TASK_RUNNER_SYNONYM.initialize;
    EXCEPTION WHEN OTHERS THEN
      WB_TASK_RUNNER_SYNONYM.initialize;
    end; 
    
    println(''Initialize complete.''); 
    
    if p_parent_id <= 0
    then
       println('' starting operator.'');
    
       -- Start the Operator
       l_job_audit_execution_id:= WB_TASK_RUNNER_SYNONYM.open_by_name
       (
         null,
         p_job_object_type_name,
         p_job_object_name,
         p_job_dep_location_name,
         p_job_task_name,
         p_job_exec_location_name
       );
      
       commit;
 
       -- OWB RUNNER MACRO PARAMETER STATEMENTS
       commit;
    
       l_result:= WB_TASK_RUNNER_SYNONYM.execute_in_background_and_wait(
         l_job_audit_execution_id, 20); 
         
       println('' execute_in_background complete.'');

       commit;

       dbms_scheduler.set_job_argument_value
       (
         job_name=>p_job_task_name,
         argument_position=>1,
         argument_value=>l_job_audit_execution_id
       );
       
       println('' set_job_argument_value complete.'');
       
    else
      l_job_audit_execution_id:= p_parent_id;      
    end if;
    
    println('' l_job_audit_execution_id= '' || l_job_audit_execution_id || ''.'');    

    commit;
    
    call_custom_proc(''OWB_JOB_OVERRIDE.PRE_JOB'');
    
    l_audit_execution_id:= WB_TASK_RUNNER_SYNONYM.open_by_name
    (
      l_job_audit_execution_id,
      p_job_object_type_name,
      p_job_object_name,
      p_job_dep_location_name,
      p_activity_task_name,
      p_activity_exec_location_name
    );
    
    -- OWB RUNNER MACRO TASK PARAMETER STATEMENTS
     
    l_stream_id := WB_TASK_RUNNER_SYNONYM.create_stream;
    
    WB_TASK_RUNNER_SYNONYM.activate_execution(l_audit_execution_id);
    
    WB_TASK_RUNNER_SYNONYM.execute_child(l_audit_execution_id, l_stream_id);
    
    println('' execute_task complete.'');
    println('' l_audit_execution_id= '' || l_audit_execution_id || ''.'');
    println('' l_stream_id= '' || l_stream_id || ''.'');
        
    commit;    

    WB_TASK_RUNNER_SYNONYM.wait_for_task_then_close(
       l_audit_execution_id,
       l_stream_id
    );
    
    println('' wait_for_task_then_close complete.''); 

    call_custom_proc(''OWB_JOB_OVERRIDE.POST_JOB'');

    commit;

EXCEPTION WHEN OTHERS THEN
  BEGIN
    l_error_message:= l_debug_message || '' '' || SQLERRM;
    /*||
      '' p_parent_id ='' || p_parent_id ||
      '' p_job_object_type_name ='' || p_job_object_type_name ||
      '' p_job_object_name ='' || p_job_object_name ||
      '' p_job_dep_location_name ='' || p_job_dep_location_name ||
      '' p_job_task_name ='' || p_job_task_name ||
      '' p_job_exec_location_name ='' || p_job_exec_location_name ||
      '' p_activity_task_name ='' || p_activity_task_name ||
      '' p_activity_exec_location_name ='' || p_activity_exec_location_name; */
    
    raise_application_error(-20001, l_error_message, true);
  END;
end;
';

  dbms_lob.createtemporary(clobvar, TRUE);
  dbms_lob.open(clobvar, dbms_lob.lob_readwrite);
  dbms_lob.writeappend(clobvar, length(data_str), data_str);
  dbms_lob.close(clobvar);     
 
  update wb_rt_platform_properties set
    property_value = clobvar 
  where
    property_path like 'property.%DBMSSchedulerDeployment.job_runner.sql';  

  data_str:= 'CREATE OR REPLACE PROCEDURE "WB_TASK_RUNNER_PROC"
(
    -- Request parameters
    errbuf IN VARCHAR2,
    retcode IN NUMBER,   
    --    
    p_parent_id in varchar2,
    ---
    p_job_object_type_name in varchar2,
    p_job_object_name in varchar2,
    p_job_dep_location_name in varchar2,
    ---
    p_job_task_name in varchar2,
    p_job_exec_location_name in varchar2,
    ---
    p_activity_task_name in varchar2,
    p_activity_exec_location_name in varchar2 -- OWB RUNNER MACRO EXTRA COMMA        
    --
    -- Executed Object parameters
    -- OWB RUNNER MACRO PARAMETER LIST
) 
AS
    l_conc_request_id number;  
    l_root_request_id number;          
    l_job_audit_execution_id number; 
    l_audit_execution_id number; 
    l_stream_id number(22);
    l_result number;
-- OWB RUNNER MACRO LOCAL VARIABLES

  function equals
  (
    str1 IN VARCHAR2,
    str2 IN VARCHAR2
  ) 
  return boolean 
  is
  begin
    if(str1 is null and str2 is not null) then return false; end if;
    if(str1 is not null and str2 is null) then return false; end if;
    if(str1 != str2) then return false; end if;
    
    return true;
  end;

  procedure println
  (
    str IN VARCHAR2
  ) 
  as
  begin
    FND_FILE.PUT_LINE(2, str);
    dbms_output.put_line(str);     
  end;
  
  procedure setStatus
  (
    status IN VARCHAR2, 
    message IN VARCHAR2
  ) 
  as
    result boolean;
  begin
    result:= FND_CONCURRENT.SET_COMPLETION_STATUS(status, message);
  end;  
  
  function getRequestID 
  return number 
  is
  begin
    return FND_GLOBAL.CONC_REQUEST_ID();
  end;
  
  procedure call_custom_proc (p_name IN VARCHAR2)   
  as l_message varchar2(4056);  
  BEGIN    
    EXECUTE IMMEDIATE ''BEGIN '' || p_name || ''('' ||                    
    ''  p_job_name=>:1'' ||        
    '', p_message=>:2'' ||      
    '', p_job_audit_execution_id=>:3'' ||        
    '', p_audit_execution_id=>:4'' ||      
    '', p_parent_id=>:5'' ||       
    '', p_job_object_type_name=>:6'' ||       
    '', p_job_object_name=>:7'' ||        
    '', p_job_dep_location_name=>:8'' ||       
    '', p_job_task_name=>:9'' ||       
    '', p_job_exec_location_name=>:10'' ||       
    '', p_activity_task_name=>:11'' ||       
    '', p_activity_exec_location_name=>:12'' || -- OWB RUNNER MACRO CALL CUSTOM PROC DEF
    ''); END;''    
    USING                           
      ''WB_TASK_RUNNER_PROC''       
    , in out l_message 
    , l_job_audit_execution_id
    , l_audit_execution_id
    , p_parent_id
    , p_job_object_type_name
    , p_job_object_name
    , p_job_dep_location_name
    , p_job_task_name
    , p_job_exec_location_name
    , p_activity_task_name
    , p_activity_exec_location_name -- OWB RUNNER MACRO CALL CUSTOM PROC VALUE    
    ;     
    
    println(''call_custom_proc '' || p_name);    
    println(l_message);  
  EXCEPTION  
  WHEN others THEN    
    IF SQLCODE <> -6550 THEN      
      RAISE;    
    END IF;  
 END;     
  
BEGIN             
  println(''Welcome to the OWB PL/SQL Scheduler Runner'');
  println('''');
  
  if(p_parent_id is null) then 
    l_job_audit_execution_id:=0; 
  else  
    l_job_audit_execution_id:= to_number(p_parent_id);    
  end if;  

-- OWB RUNNER MACRO COPY VARIABLES  

  println(''l_job_audit_execution_id='' || l_job_audit_execution_id);  
  println(''p_job_object_type_name='' || p_job_object_type_name); 
  println(''p_job_object_name='' || p_job_object_name); 
  println(''p_job_dep_location_name='' || p_job_dep_location_name); 
  println(''p_job_task_name='' || p_job_task_name); 
  println(''p_job_exec_location_name='' || p_job_exec_location_name); 
  println(''p_activity_task_name='' || p_activity_task_name); 
  println(''p_activity_exec_location_name='' || p_activity_exec_location_name);
  -- OWB RUNNER MACRO PRINT PARAMETER LIST  
  println('''');
  
  -- Work around to make sure that package wb_rti_workflow_util is compiled
  begin
    WB_TASK_RUNNER_SYNONYM.initialize;
  EXCEPTION WHEN OTHERS THEN
    WB_TASK_RUNNER_SYNONYM.initialize;
  end; 
    
  println(''Initialize complete.'');   

  if (l_job_audit_execution_id <= 0) 
  then
    println(''The root request was submitted directly from an Applications UI'');
    l_conc_request_id:= getRequestID;
    select ROOT_REQUEST_ID into l_root_request_id from fnd_concurrent_requests where REQUEST_ID= l_conc_request_id;

    if(l_root_request_id is null)
    then
      l_root_request_id:= l_conc_request_id;
      println(''This is the root request submission with id '' || l_root_request_id);
    else
      println(''This is a re-submitted request submission of root request id '' || l_root_request_id);  
    end if;

    begin
      l_job_audit_execution_id:=
        to_number(WB_TASK_RUNNER_SYNONYM.get_textual_attr(l_root_request_id, ''APPS_AUDIT_EXECUTION_ID''));

      println(''Recovered l_job_audit_execution_id= '' || l_job_audit_execution_id);

    EXCEPTION WHEN OTHERS THEN
      BEGIN 
        println(''The Operator needs to be started'');
        commit;
      END;
    END;

    if(l_job_audit_execution_id = -1)
    then 
      println(''Starting Operator'');

      l_job_audit_execution_id:= WB_TASK_RUNNER_SYNONYM.open_by_name
      (
        null,
        p_job_object_type_name,
        p_job_object_name,
        p_job_dep_location_name,
        p_job_task_name,
        p_job_exec_location_name
      );     
     commit;
     println(''Created l_job_audit_execution_id= '' || l_job_audit_execution_id);     

     -- This is required so the operator can recover the request id
     WB_TASK_RUNNER_SYNONYM.create_textual_attr
     (
       l_job_audit_execution_id,     
       ''APPS_AUDIT_EXECUTION_ID'', 
       l_conc_request_id
     );
     
     -- This is required so that this runner can recover the l_job_audit_execution_id
     WB_TASK_RUNNER_SYNONYM.create_textual_attr
     (
       l_conc_request_id,
       ''APPS_AUDIT_EXECUTION_ID'',
       l_job_audit_execution_id
     );     

     commit;
     -- OWB RUNNER MACRO PARAMETER STATEMENTS
     commit;
     println(''override_input_parameter completed'');                              
     l_result:= WB_TASK_RUNNER_SYNONYM.execute_in_background_and_wait(
       l_job_audit_execution_id, 20); 
     commit; 
     println(''execute_in_background completed'');     
    end if;   
  end if;
  
  
  println(''Starting execution of target object'');
  
  call_custom_proc(''OWB_JOB_OVERRIDE.PRE_JOB'');
  
  l_audit_execution_id:= WB_TASK_RUNNER_SYNONYM.open_by_name
  (
    l_job_audit_execution_id,
    p_job_object_type_name,
    p_job_object_name,
    p_job_dep_location_name,
    p_activity_task_name,
    p_activity_exec_location_name
  );
  
  -- OWB RUNNER MACRO TASK PARAMETER STATEMENTS
   
  l_stream_id := WB_TASK_RUNNER_SYNONYM.create_stream;
  
  WB_TASK_RUNNER_SYNONYM.activate_execution(l_audit_execution_id);
  
  WB_TASK_RUNNER_SYNONYM.execute_child(l_audit_execution_id, l_stream_id); 
    
  commit;    
  
  println(''Waiting for task to complete'');  

  WB_TASK_RUNNER_SYNONYM.wait_for_task_then_close(
     l_audit_execution_id,
     l_stream_id
  );
  
  println(''Task completed'');  
    
  commit;
  
  setStatus(''NORMAL'', ''''); 
  
  call_custom_proc(''OWB_JOB_OVERRIDE.POST_JOB'');
  
  println(''Request completed'');  
  
EXCEPTION WHEN OTHERS THEN
  BEGIN
   println(SQLERRM(SQLCODE)); 
   setStatus(''ERROR'', SQLERRM(SQLCODE));  
   commit;
  END;
  
END;
';

  dbms_lob.createtemporary(clobvar, TRUE);
  dbms_lob.open(clobvar, dbms_lob.lob_readwrite);
  dbms_lob.writeappend(clobvar, length(data_str), data_str);
  dbms_lob.close(clobvar);     
 
  update wb_rt_platform_properties set
    property_value = clobvar 
  where
    property_path like 'property.%AppsCMSchedulerDeployment.job_runner.sql';  

commit;
END;
/