/*=======================================================================+
 |  Copyright (c) 2000 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
 | FILENAME
 |   wfevsetob.pls
 | DESCRIPTION
 |   PL/SQL body for package:  WF_SETUP_OMB
 | NOTE
 |
 | MODIFICATION LOG:
 |   01/2002 JWSMITH BUG 2001012 - Increased username, admin_role to
 |           varchar2(320)
 *=======================================================================*/
whenever sqlerror exit failure rollback;

set arraysize 1
set scan off

create or replace package body WF_SETUP_OMB as
/* $Header: wfevsetob.sql 26.1 2002/01/28 19:13:35 jwsmith ship $ */

function GetLocalSystemGUID
  return raw
is
  lguid varchar2(32);
begin
  select substr(TEXT, 1, 32) into lguid
    from WF_RESOURCES
   where NAME = 'WF_SYSTEM_GUID'
     and LANGUAGE = userenv('LANG');

  return hextoraw(lguid);
exception
  when OTHERS then
    wf_core.context('WF_SETUP_OMB', 'GetLocalSystemGUID');
    raise;
end GetLocalSystemGUID;

procedure Check_InitParameters
is
  cursor pcurs is
    select NAME, VALUE
      from v$parameter
     where NAME in ('aq_tm_processes', 'job_queue_processes',
                    'job_queue_interval')
     order by NAME;

  hTab wfe_html_util.headerTabType;
  dTab wfe_html_util.dataTabType;
  i pls_integer;
  vTab wfe_html_util.tmpTabType;

begin
  -- set the recommended values
  i := 1;
  vTab(i) := '1';  -- for aq_tm_processes
  i := i+1;
  vTab(i) := '5';  -- for job_queue_interval
  i := i+1;
  vTab(i) := '2';  -- for job_queue_processes

  i := 0;
  for pr in pcurs loop
    i := i+1;
    dTab(i).col01 := pr.name;
    dTab(i).col02 := pr.value;
    if (i <= 3) then
      dTab(i).col03 := vTab(i);
    end if;

    dTab(i).selectable := FALSE;
    dTab(i).deletable  := FALSE;
    dTab(i).hasdetail  := FALSE;
  end loop;

  -- popluate the header table
  i := 1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('NAME');
  i := i+1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('VALUE');
  i := i+1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('VALUE_RECOMMENDED');

  htp.p('<p>'||wf_core.translate('WFE_INITPARAMS'));
  -- ### Database Init Parameters

  -- render table
  Wfe_Html_Util.Simple_Table(hTab, dTab);

exception
  when OTHERS then
    wf_core.context('WF_SETUP_OMB', 'Check_InitParameters');
    wfe_html_util.Error;
end Check_InitParameters;

procedure Check_Dblinks(
  localsguid  raw
)
is
  -- all db link
  cursor dblcurs is
    select distinct S.GUID, S.NAME
      from WF_SYSTEMS S, WF_AGENTS A
     where S.GUID = A.SYSTEM_GUID
       and S.GUID <> localsguid;

  hTab wfe_html_util.headerTabType;
  dTab wfe_html_util.dataTabType;
  i pls_integer;

begin
  -- populate the data table
  i := 0;
  for dblr in dblcurs loop
    i := i+1;
    dTab(i).guid := dblr.guid;
    dTab(i).col01:= dblr.name;

    -- find out if such dblr.name exists
    begin
      select 'EXIST' into dTab(i).col02
        from sys.dual
       where upper(dblr.name) in (
           select DB_LINK from USER_DB_LINKS
           union all
           select DB_LINK from ALL_DB_LINKS
            where OWNER = 'PUBLIC');
    exception
      when NO_DATA_FOUND then
        dTab(i).col02 := 'NOT_EXIST';

      when OTHERS then
        raise;
    end;
    dTab(i).col02 := wf_core.translate(dTab(i).col02);

    dTab(i).selectable := FALSE;
    dTab(i).deletable  := FALSE;
    dTab(i).hasdetail  := FALSE;
  end loop;

  -- popluate the header table
  i := 1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('NAME');
  i := i+1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('STATUS');

  htp.p('<p>'||wf_core.translate('WFE_DBLINKS'));
  -- ### DBLinks

  -- render table
  Wfe_Html_Util.Simple_Table(hTab, dTab);

exception
  when OTHERS then
    rollback;
    wf_core.context('WF_SETUP_OMB', 'Check_Dblinks');
    wfe_html_util.Error;
end Check_Dblinks;

procedure Check_Queues(
  localsguid  raw
)
is
  cursor lquecurs is
    select A.GUID, A.QUEUE_NAME
      from WF_AGENTS A
     where A.SYSTEM_GUID = localsguid
       and A.PROTOCOL = 'SQLNET';

  hTab wfe_html_util.headerTabType;
  dTab wfe_html_util.dataTabType;
  i pls_integer;

  include_create  boolean := FALSE;
  creatable       boolean;

begin
  -- populate the data table
  i := 0;
  for lquer in lquecurs loop
    i := i+1;
    dTab(i).guid := lquer.guid;
    dTab(i).col01:= nvl(lquer.queue_name, '&nbsp');

    -- check existence
    creatable := FALSE;
    begin
      select 'EXIST'
        into dTab(i).col02
        from sys.dual
       where lquer.queue_name in (
         select DQ.OWNER||'.'||DQ.NAME
           from ALL_QUEUES DQ
          where QUEUE_TYPE = 'NORMAL_QUEUE');

    exception
      when NO_DATA_FOUND then
        dTab(i).col02 := 'NOT_EXIST';
        creatable := TRUE;

      when OTHERS then
        raise;
    end;
    dTab(i).col02 := wf_core.translate(dTab(i).col02);

    -- create queue
    if (creatable) then
      dTab(i).col02 := dTab(i).col02||'&nbsp&nbsp&nbsp'||
        '<a href='||wfa_html.base_url||'/wf_setup_omb.create_queue?aguid='||
        rawtohex(dTab(i).guid)||'>'||
        wf_core.translate('CREATE')||
        '</a>';
    end if;

    dTab(i).selectable := FALSE;
    dTab(i).deletable  := FALSE;
    dTab(i).hasdetail  := FALSE;
  end loop;

  -- popluate the header table
  i := 1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('QUEUE');
  i := i+1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('STATUS');

  htp.p('<p>'||wf_core.translate('WFE_LOCAL_QUEUES'));
  -- ### Local queues

  -- render table
  Wfe_Html_Util.Simple_Table(hTab, dTab);

exception
  when OTHERS then
    rollback;
    wf_core.context('WF_SETUP_OMB', 'Check_Queues');
    wfe_html_util.Error;
end Check_Queues;

procedure Check_Listeners(
  localsguid  raw
)is
  -- find all local queues that have direction of IN, ANY or undefined
  cursor lqcurs is
    select A.GUID, A.QUEUE_NAME
      from WF_AGENTS A
     where A.SYSTEM_GUID = localsguid
       and A.PROTOCOL = 'SQLNET'
       and (A.DIRECTION is null or A.DIRECTION in ('IN', 'ANY'));

  hTab wfe_html_util.headerTabType;
  dTab wfe_html_util.dataTabType;
  i pls_integer;

  creatable       boolean;

begin
  -- populate the data table
  i := 0;
  for lqr in lqcurs loop
    i := i + 1;
    dTab(i).guid := lqr.guid;
    dTab(i).col01:= lqr.queue_name;

    -- check existence
    creatable := FALSE;
    begin
      select 'EXIST'
        into dTab(i).col02
        from sys.dual
       where exists (
         select null
           from ALL_JOBS
          where upper(substr(WHAT, 1, 60))
                like 'WF_EVENT.LISTEN('''||lqr.queue_name||''')%');
    exception
      when NO_DATA_FOUND then
        dTab(i).col02 := 'NOT_EXIST';
        creatable := TRUE;

      when OTHERS then
        raise;
    end;
    dTab(i).col02 := wf_core.translate(dTab(i).col02);

    if (creatable) then
      dTab(i).col02 := dTab(i).col02||'&nbsp&nbsp&nbsp'||
        '<a href='||wfa_html.base_url||'/wf_setup_omb.create_listener?aguid='||
        rawtohex(lqr.guid)||'>'||
        wf_core.translate('CREATE')||
        '</a>';
    end if;

    dTab(i).selectable := FALSE;
    dTab(i).deletable  := FALSE;
    dTab(i).hasdetail  := FALSE;
  end loop;

  -- popluate the header table
  i := 1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('QUEUE_NAME');
  i := i + 1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('STATUS');

  htp.p('<p>'||wf_core.translate('WFE_LISTENERS'));
  -- ### Listeners for local queues.

  -- render table
  Wfe_Html_Util.Simple_Table(hTab, dTab);

exception
  when OTHERS then
    rollback;
    wf_core.context('WF_SETUP_OMB', 'Check_Listeners');
    wfe_html_util.Error;
end Check_Listeners;

procedure Check_Propagations(
  localsguid  raw
)is

  -- propagation for local system
  cursor ppgcurs is
    select distinct OA.GUID OGUID, OA.QUEUE_NAME OQUEUE, S.NAME, TA.SYSTEM_GUID
      from WF_EVENT_SUBSCRIPTIONS ES, WF_AGENTS OA, WF_AGENTS TA, WF_SYSTEMS S
     where OA.SYSTEM_GUID = localsguid
      and ES.OUT_AGENT_GUID = OA.GUID
      and ES.TO_AGENT_GUID = TA.GUID
      and TA.SYSTEM_GUID = S.GUID;

  hTab wfe_html_util.headerTabType;
  dTab wfe_html_util.dataTabType;
  i pls_integer;

  creatable       boolean;

begin
  -- populate the data table
  i := 0;
  for ppgr in ppgcurs loop
    i := i + 1;
    dTab(i).guid := ppgr.oguid;
    dTab(i).col01:= ppgr.oqueue;
    dTab(i).col02:= ppgr.name;   -- to system name

    -- check out queue
    begin
      select NAME
        into dTab(i).col03
        from WF_AGENTS
       where GUID = ppgr.oguid
         and (PROTOCOL <> 'SQLNET'
           or (DIRECTION is not null and DIRECTION = 'IN'));

       wf_core.token('NAME', dTab(i).col03);
       dTab(i).col03 := wf_core.translate('WFE_NOT_OUTAGENT');
    exception
      when NO_DATA_FOUND then
        null;
    end;

    -- check system (dblink) exist

    -- check existence
    creatable := FALSE;
    if (dTab(i).col03 is null) then
      begin
        select 'EXIST'
          into dTab(i).col03
          from sys.dual
         where exists (
           select NULL
             from DBA_QUEUE_SCHEDULES QS
            where QS.DESTINATION = upper(ppgr.name)
              and QS.SCHEMA||'.'||QS.QNAME = ppgr.oqueue);
      exception
        when NO_DATA_FOUND then
          dTab(i).col03 := 'NOT_EXIST';
          creatable := TRUE;
      end;
      dTab(i).col03 := wf_core.translate(dTab(i).col03);

      if (creatable) then
        dTab(i).col03 := dTab(i).col03||'&nbsp&nbsp&nbsp'||
          '<a href='||wfa_html.base_url||
          '/wf_setup_omb.create_propagation?oaguid='||
          rawtohex(dTab(i).guid)||'&sguid='||
          rawtohex(ppgr.system_guid)||'>'||
          wf_core.translate('CREATE')||
          '</a>';
      end if;
    end if;

    dTab(i).selectable := FALSE;
    dTab(i).deletable  := FALSE;
    dTab(i).hasdetail  := FALSE;
  end loop;

  -- popluate the header table
  i := 1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('OUT_AGENT');
  i := i + 1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('SYSTEM');
  i := i + 1;
  hTab(i).def_type := 'TITLE';
  hTab(i).value    := wf_core.translate('STATUS');

  htp.p('<p>'||wf_core.translate('WFE_PROPAGATIONS'));
  -- ### Propagations for local queue.

  -- render table
  Wfe_Html_Util.Simple_Table(hTab, dTab);

exception
  when OTHERS then
    rollback;
    wf_core.context('WF_SETUP_OMB', 'Check_Propagations');
    wfe_html_util.Error;
end Check_Propagations;

procedure Check_All
is
  username varchar2(320);   -- Username to query
  admin_role varchar2(320); -- Role for admin mode

  lguid raw(16);

begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  -- Check Admin Priviledge
  admin_role := wf_core.translate('WF_ADMIN_ROLE');
  if (admin_role = '*' or
      Wf_Directory.IsPerformer(username, admin_role)) then
    -- Have admin privledge, do nothing.
    null;
  else
    wf_core.raise('WF_NOTADMIN');
  end if;

  lguid := Wf_Setup_OMB.GetLocalSystemGUID;

  -- Render page
  htp.htmlOpen;

  -- Set page title
  htp.headOpen;

  htp.title(wf_core.translate('WFE_CHECK_ALL_TITLE'));
  wfa_html.create_help_function('wf/links/fin.htm?'||'SETUPCHECKALL');
  fnd_document_management.get_open_dm_display_window;
  Wfe_Html_Util.generate_confirm;

  htp.headClose;

  -- Page header
  wfa_sec.Header(FALSE,
            NULL,
            wf_core.translate('WFE_CHECK_ALL_TITLE'),
            TRUE);

  htp.br;  -- add some space between header and table

  Wf_Setup_OMB.Check_InitParameters;
  htp.br;

  Wf_Setup_OMB.Check_Dblinks(lguid);
  htp.br;

  Wf_Setup_OMB.Check_Queues(lguid);
  htp.br;

  Wf_Setup_OMB.Check_Listeners(lguid);
  htp.br;

  Wf_Setup_OMB.Check_Propagations(lguid);
  htp.br;

  wfa_sec.Footer;
  htp.htmlClose;

exception
  when OTHERS then
    rollback;
    wf_core.context('WF_SETUP_OMB', 'Check_All');
    wfe_html_util.Error;
end Check_All;

procedure Create_Queue(
  aguid  raw
)is
  lguid  raw(16);
  sguid  raw(16);
  qname  varchar2(30);
  qtable varchar2(30);

  l_msg  varchar2(4000);

  username varchar2(320);   -- Username to query
  admin_role varchar2(320); -- Role for admin mode

begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  -- Check Admin Priviledge
  admin_role := wf_core.translate('WF_ADMIN_ROLE');
  if (admin_role = '*' or
      Wf_Directory.IsPerformer(username, admin_role)) then
    -- Have admin privledge, do nothing.
    null;
  else
    wf_core.raise('WF_NOTADMIN');
  end if;

  -- check system is local
  lguid := Wf_Setup_OMB.GetLocalSystemGUID;

  begin
    select SYSTEM_GUID, substr(QUEUE_NAME,1,30)
      into sguid, qname
      from WF_AGENTS
     where GUID = aguid;
  exception
    when OTHERS then
      wf_core.raise('WFE_AGENT_NOTEXIST');
  end;

  qtable := substr(qname,1,24)||'_TABLE';

  if (lguid <> sguid) then
    wf_core.token('ENTITY', 'QUEUE');
    wf_core.raise('WFE_SYSTEM_NOTLOCAL');
  end if;

  -- confirm message
/* ###
  l_msg := wf_core.translate('WFE_ABOUT_TO_CREATE');
  l_msg := l_msg||': '||qtable
           ||'/'||wf_core.translate('WF_SCHEMA')||'.WF_EVENT_T'
           ||'/'||qname;

  htp.p('<HTML><HEAD>');
  Wfe_Html_Util.generate_confirm();
  htp.p('<BODY>');
  htp.p('<SCRIPT>
         confirm_continue("'||l_msg||'");
         </SCRIPT>');
*/

  -- create queue table
/*
  dbms_aqadm.create_queue_table
  (
   queue_table          => qtable,
   queue_payload_type   => wf_core.translate('WF_SCHEMA')||'.WF_EVENT_T',
   sort_list            => 'ENQ_TIME',
   comment              => 'Workflow event system default queue',
   compatible           => '8.1',
   multiple_consumers   => TRUE
  );
*/

  ombaqadm.create_table
  (
   table_name           => qtable,
   payload_type         => 'CLOB',
   storage_clause       => '',
   sort_list            => 'ENQ_TIME',
   multiple_consumers   => 1, -- set to TRUE
   message_grouping     => 0,
   comment              => 'Workflow event system default queue table'
  );

  -- create queue
/*
  dbms_aqadm.create_queue
  (
   queue_name           => qname,
   queue_table          => qtable
  );
*/

  ombaqadm.create_queue
  (
   q_name               => qname,
   tab_name             => qtable,
   q_type               => 0,  -- not in use
   max_retries          => 5,
   retry_delay          => 10,
   retention_time       => 10,
   dependency_checking  => 0,  -- set to FALSE
   comment              => 'Workflow event system default queue'
  );

  -- start queue
/*
  dbms_aqadm.start_queue
  (
   queue_name           => qname
  );
*/

  ombaqadm.start_queue
  (
   q_name           => qname,
   can_enqueue      => 1,  -- set to TRUE
   can_dequeue      => 1   -- set to TRUE
  );

  commit;

  -- go back to check_all
  Wfe_Html_Util.gotoURL(p_url=>wfa_html.base_url||'/Wf_Setup_OMB.Check_All');

exception
  when OTHERS then
    rollback;
    wf_core.context('WF_SETUP_OMB', 'Create_Queue', rawtohex(aguid),
                    qname, qtable);
    wfe_html_util.Error;
end Create_Queue;

procedure Create_Listener(
  aguid  raw
)is
  username varchar2(320);   -- Username to query
  admin_role varchar2(320); -- Role for admin mode

  qname  varchar2(30);
  lguid  raw(16);

  jobnum binary_integer;

begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  -- Check Admin Priviledge
  admin_role := wf_core.translate('WF_ADMIN_ROLE');
  if (admin_role = '*' or
      Wf_Directory.IsPerformer(username, admin_role)) then
    -- Have admin privledge, do nothing.
    null;
  else
    wf_core.raise('WF_NOTADMIN');
  end if;
  -- check system is local
  lguid := Wf_Setup_OMB.GetLocalSystemGUID;

  begin
    select substr(QUEUE_NAME,1,30)
      into qname
      from WF_AGENTS
     where GUID = aguid;
  exception
    when OTHERS then
      wf_core.raise('WFE_AGENT_NOTEXIST');
  end;

  -- submit a job to run the listener
  DBMS_JOB.Submit(
    job=>jobnum,
    what=>'Wf_Event.Listen('''||qname||''')',
    next_date=>sysdate,
    interval=>'sysdate+1/96'   -- once every 15 minutes
  );

  commit;

  -- go back to check_all
  Wfe_Html_Util.gotoURL(wfa_html.base_url||'/Wf_Setup_OMB.Check_All');

exception
  when OTHERS then
    rollback;
    wf_core.context('WF_SETUP_OMB', 'Create_Listener', rawtohex(aguid),
                    qname);
    wfe_html_util.Error;
end Create_Listener;

procedure Create_Propagation(
  oaguid raw,
  sguid  raw
)is
  username varchar2(320);   -- Username to query
  admin_role varchar2(320); -- Role for admin mode

  qname  varchar2(30);
  sname  varchar2(80);
  lguid  raw(16);
begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  -- Check Admin Priviledge
  admin_role := wf_core.translate('WF_ADMIN_ROLE');
  if (admin_role = '*' or
      Wf_Directory.IsPerformer(username, admin_role)) then
    -- Have admin privledge, do nothing.
    null;
  else
    wf_core.raise('WF_NOTADMIN');
  end if;
  -- check system is local
  lguid := Wf_Setup_OMB.GetLocalSystemGUID;

  begin
    select substr(QUEUE_NAME,1,30)
      into qname
      from WF_AGENTS
     where GUID = oaguid
       and SYSTEM_GUID = sguid;
  exception
    when OTHERS then
      wf_core.raise('WFE_AGENT_NOTEXIST');
  end;

  begin
    select substr(S.NAME,1,80)
      into sname
      from WF_SYSTEMS S
     where S.GUID = sguid;
  exception
    when OTHERS then
      wf_core.raise('WFE_SYSTEM_NOTEXIST');
  end;

  -- schedule propagation
  DBMS_AQADM.Schedule_Propagation(
    queue_name=>qname,
    destination=>sname
  );
  commit;

  -- go back to check_all
  Wfe_Html_Util.gotoURL(wfa_html.base_url||'/Wf_Setup_OMB.Check_All');

exception
  when OTHERS then
    rollback;
    wf_core.context('WF_SETUP_OMB', 'Create_Propagation', rawtohex(oaguid),
                    rawtohex(sguid), qname, sname);
    wfe_html_util.Error;
end Create_Propagation;

end WF_SETUP_OMB;
/
commit;
exit;