rem
rem This script can be used to list out details of any Deployment or
rem Execution requests that are currently active.
rem
rem A DEPLOYMENT is a collection of DEPLOYMENT UNITS.
rem
rem Each DEPLOYMENT_UNIT is a collection of Objects that are all
rem being deployed to the same Location and with the same Deployment
rem Action.
rem
rem Each DEPLOYMENT or EXECUTION is uniquely identified by an Audit ID,
rem and these may be used as parameters to other SQL scripts,
rem e.g deactivate_deployment.sql, deactivate_execution.sql
rem
rem USAGE
rem Logon as WorkspaceOwner or WorkspaceUser with Execute/Deploy/Admin privilege
rem    
rem    @<OWB-HOME>/owb/rtp/sql/list_requests.sql <workspace>
rem
rem This script lists details about Deployments and Executions that are
rem currently Active.
rem
rem The given workspace is the one in which the jobs are running
rem <workspace> should be declared as workspaceOwner.workspaceName
rem if only workspaceName is given, workspaceOwner will be defaulted to user
rem

set serveroutput on;
set echo off
set verify off

set role OWB_USER;
call owbsys.wb_rt_script_util.set_workspace('&1.');

declare
  l_deployments owbsys.wb_rt_script_util.LIST_REQUESTS_TAB;
  l_units       owbsys.wb_rt_script_util.LIST_REQUESTS_TAB;
  l_executions  owbsys.wb_rt_script_util.LIST_REQUESTS_TAB;

  l_audit_id_col char(10) := 'Audit ID..';
  l_status_col   char(14) := 'Status........';
  l_name_col     char(18) := 'Name..............';
  l_date_col     char(18) := 'Date..............';
  l_owner_col    char(14) := 'Owner.........';
  l_audit_id char(10);
  l_status   char(14);
  l_name     char(18);
  l_date     char(18);
  l_owner    char(14);

begin
  DBMS_OUTPUT.PUT_LINE('====================');
  DBMS_OUTPUT.PUT_LINE('DEPLOYMENTS');
  DBMS_OUTPUT.PUT_LINE('====================');

  l_deployments := owbsys.wb_rt_script_util.list_deployment_requests();

  if l_deployments is not null and l_deployments.COUNT > 0
  then
    DBMS_OUTPUT.PUT_LINE(l_audit_id_col || ' ' || l_status_col || ' ' ||
                         l_name_col || ' ' || l_date_col || ' ' || l_owner_col);

    for i in l_deployments.FIRST .. l_deployments.LAST
    loop
      l_audit_id := substr(l_deployments(i).audit_id,1,10);
      l_status   := substr(l_deployments(i).audit_status,1,14);
      l_name     := substr(l_deployments(i).job_name,1,18);
      l_date     := substr(l_deployments(i).job_date,1,18);
      l_owner    := substr(l_deployments(i).job_owner,1,14);

      DBMS_OUTPUT.PUT_LINE(l_audit_id || ' ' || l_status || ' ' ||
                           l_name || ' ' || l_date || ' ' || l_owner);
    end loop;
  end if;

  DBMS_OUTPUT.PUT_LINE('====================');
  DBMS_OUTPUT.PUT_LINE('DEPLOYMENT UNITS');
  DBMS_OUTPUT.PUT_LINE('====================');

  l_units := owbsys.wb_rt_script_util.list_unit_requests();

  if l_units is not null and l_units.COUNT > 0
  then
    DBMS_OUTPUT.PUT_LINE(l_audit_id_col || ' ' || l_status_col || ' ' ||
                         l_name_col || ' ' || l_date_col || ' ' || l_owner_col);

    for j in l_units.FIRST .. l_units.LAST
    loop
      l_audit_id := substr(l_units(j).audit_id,1,10);
      l_status   := substr(l_units(j).audit_status,1,14);
      l_name     := substr(l_units(j).job_name,1,18);
      l_date     := substr(l_units(j).job_date,1,18);
      l_owner    := substr(l_units(j).job_owner,1,14);

      DBMS_OUTPUT.PUT_LINE(l_audit_id || ' ' || l_status || ' ' ||
                           l_name || ' ' || l_date || ' ' || l_owner);
    end loop;
  end if;

  DBMS_OUTPUT.PUT_LINE('====================');
  DBMS_OUTPUT.PUT_LINE('EXECUTIONS');
  DBMS_OUTPUT.PUT_LINE('====================');

  l_executions := owbsys.wb_rt_script_util.list_execution_requests();

  if l_executions is not null and l_executions.COUNT > 0
  then
    DBMS_OUTPUT.PUT_LINE(l_audit_id_col || ' ' || l_status_col || ' ' ||
                         l_name_col || ' ' || l_date_col || ' ' || l_owner_col);

    for k in l_executions.FIRST .. l_executions.LAST
    loop
      l_audit_id := substr(l_executions(k).audit_id,1,10);
      l_status   := substr(l_executions(k).audit_status,1,14);
      l_name     := substr(l_executions(k).job_name,1,18);
      l_date     := substr(l_executions(k).job_date,1,18);
      l_owner    := substr(l_executions(k).job_owner,1,14);

      DBMS_OUTPUT.PUT_LINE(l_audit_id || ' ' || l_status || ' ' ||
                           l_name || ' ' || l_date || ' ' || l_owner);      
    end loop;
  end if;

  commit;
end;
/