APPS.WF_QUEUE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 55

  select shutdown_pending into shutdown from v$instance;

Line: 263

     select sys_context('USERENV', 'CURRENT_SCHEMA')
       into wf_queue.account_name
       from sys.dual;

Line: 531

select  aa.name,
        aa.value_type, -- CONSTANT or ITEMATTR
        aa.type,       -- NUMBER/TEXT/DATE etc
        aa.format,
        av.TEXT_VALUE,
        av.NUMBER_VALUE,
        av.DATE_VALUE
from wf_activity_attr_values av,
     wf_activity_attributes aa,
     wf_activities a,
     wf_process_activities pa
where pa.activity_item_type = a.item_type
and pa.activity_name = a.name
and pa.instance_id=actid
and a.begin_date< startdate and nvl(a.end_date,startdate) >= startdate
and a.item_type = aa.activity_item_type
and a.name = aa.activity_name
and a.version = aa.activity_version
and av.process_activity_id = actid
and av.name=aa.name
order by aa.sequence;

Line: 835

      SELECT CWA.COST, CWIAS.BEGIN_DATE
      into cost, l_begdate
      FROM WF_ITEM_ACTIVITY_STATUSES CWIAS,
           WF_PROCESS_ACTIVITIES CWPA,
           WF_ITEMS WI,
           WF_ACTIVITIES CWA
      where CWIAS.ACTIVITY_STATUS = 'DEFERRED'
      and CWIAS.PROCESS_ACTIVITY = CWPA.INSTANCE_ID
      and CWPA.ACTIVITY_ITEM_TYPE = CWA.ITEM_TYPE
      and CWPA.ACTIVITY_NAME = CWA.NAME
      and CWIAS.ITEM_TYPE = WI.ITEM_TYPE
      and CWIAS.ITEM_KEY = WI.ITEM_KEY
      and WI.BEGIN_DATE >= CWA.BEGIN_DATE
      and WI.BEGIN_DATE < nvl(CWA.END_DATE, WI.BEGIN_DATE+1)
      and CWIAS.ITEM_TYPE = event.itemtype
      and CWIAS.ITEM_KEY = event.itemkey
      and CWIAS.PROCESS_ACTIVITY = event.actid;

Line: 940

      select count(process_activity)
      into   l_background_occurrence
      from   wf_item_activity_statuses_h
      where  item_type = enqueue_event.itemtype
      and    item_key = enqueue_event.itemkey
      and    process_activity = enqueue_event.actid
      and    begin_date >= g_background_begin_date;

Line: 1016

     select ROOT_ACTIVITY into l_process
     from WF_ITEMS
     where ITEM_TYPE=itemtype
       and ITEM_KEY=itemkey;

Line: 1408

      ** Update the event to let everyone know it expired
      */
      l_event.SetErrorMessage(wf_core.translate('WFE_MESSAGE_EXPIRED'));

Line: 1419

      select name into lsysname
      from   wf_systems
      where  guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'));

Line: 1652

    select  wfq.protocol,
            wfq.inbound_outbound,
            wfq.description,
            wfq.queue_count
    from    wf_queues wfq
    where   NVL(wfq.disable_flag, 'N') = 'N'
    order by     wfq.protocol, wfq.inbound_outbound;

Line: 1722

                  wf_core.translate('DELETE')||'',
                  calign=>'Center',
                  cattributes=>'id="' || wf_core.translate('DELETE') || '"');

Line: 1764

                                  '/wf_queue.generic_queue_confirm_delete?p_protocol='||
                                  queues.protocol||'&p_inbound_outbound='||
                                   queues.inbound_outbound,
                              ctext=>''),
                              'center', cattributes=>'valign="MIDDLE"
                     headers="' || wf_core.translate('DELETE') || '"');

Line: 1867

  SELECT queue_count
  INTO   l_count
  FROM   wf_queues
  WHERE  UPPER(p_protocol) = protocol
  AND    p_inbound_outbound = inbound_outbound;

Line: 1926

    l_sql := 'SELECT COUNT(1) FROM WF_'||p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_TABLE';

Line: 2026

  htp.p('
');

Line: 2180

l_inbound_selected   varchar2(1) := 'N';

Line: 2181

l_outbound_selected  varchar2(1) := 'N';

Line: 2212

      SELECT description,
             queue_count
      INTO   l_description,
             l_queue_count
      FROM   wf_queues
      WHERE  protocol = p_protocol
      AND    inbound_outbound = p_inbound_outbound;

Line: 2244

  htp.p('');

Line: 2269

      l_inbound_selected  := 'Y';

Line: 2270

      l_outbound_selected := NULL;

Line: 2274

      l_inbound_selected  := NULL;

Line: 2275

      l_outbound_selected := 'Y';

Line: 2281

  htp.formSelectOpen(cname=>'p_inbound_outbound',cattributes=>'id="i_inbound_outbound"');

Line: 2283

  htp.formSelectOption(cvalue=>wf_core.translate('INBOUND'),
                       cattributes=>'value=INBOUND',
                       cselected=>l_inbound_selected);

Line: 2287

  htp.formSelectOption(cvalue=>wf_core.translate('OUTBOUND'),
                       cattributes=>'value=OUTBOUND',
                       cselected=>l_outbound_selected);

Line: 2291

  htp.formSelectClose;

Line: 2379

procedure generic_queue_delete_check
(p_protocol          in varchar2,
 p_inbound_outbound  in varchar2,
 p_queue_start_range in number,
 p_queue_end_range   in number) IS

ii      NUMBER := 0;

Line: 2393

  ** you delete it.
  */
  for ii in p_queue_start_range..p_queue_end_range loop

     /*
     ** Check to see if there are any messages in the specified queue
     */
     -- p_protocol and p_inbound was verified before coming here.
     -- BINDVAR_SCAN_IGNORE
     l_sql := 'SELECT COUNT(1) INTO :a FROM WF_'||p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_TABLE';

Line: 2422

    Wf_Core.Context('Wf_Queue', 'generic_queue_delete_check',
                    p_protocol, p_inbound_outbound);

Line: 2426

end generic_queue_delete_check;

Line: 2428

procedure generic_queue_delete_queues
(p_protocol          in varchar2,
 p_inbound_outbound  in varchar2,
 p_queue_start_range in number,
 p_queue_end_range   in number) IS

ii      NUMBER := 0;

Line: 2440

  ** Delete the queues and queue tables
  */
  for ii in p_queue_start_range..p_queue_end_range loop

     /*
     ** Stop the queue
     */
     dbms_aqadm.stop_queue(queue_name => wf_core.translate('WF_SCHEMA')||'.'||'WF_'||
                           p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||
                           to_char(ii)||'_QUEUE');

Line: 2451

     ** Delete the Queues
     */
     dbms_aqadm.drop_queue(
       queue_name => wf_core.translate('WF_SCHEMA')||'.'||'WF_'||p_protocol||'_'||
                     substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_QUEUE');

Line: 2458

     ** Delete the Queue Table
     */
     dbms_aqadm.drop_queue_table (
       queue_table        => wf_core.translate('WF_SCHEMA')||'.'||'WF_'||p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_TABLE');

Line: 2467

    Wf_Core.Context('Wf_Queue', 'generic_queue_delete_queues',
                    p_protocol, p_inbound_outbound);

Line: 2471

end generic_queue_delete_queues;

Line: 2478

procedure Generic_Queue_Update (
p_protocol           IN VARCHAR2   DEFAULT NULL,
p_inbound_outbound   IN VARCHAR2   DEFAULT NULL,
p_description        IN VARCHAR2   DEFAULT NULL,
p_queue_count        IN VARCHAR2   DEFAULT NULL,
p_original_protocol  IN VARCHAR2   DEFAULT NULL,
p_original_inbound   IN VARCHAR2   DEFAULT NULL
) IS

username varchar2(320);   -- Username to query

Line: 2525

      SELECT count(1)
      INTO   l_count
      FROM   wf_queues
      WHERE  UPPER(p_protocol) = protocol
      AND    p_inbound_outbound = inbound_outbound;

Line: 2572

          wf_queue.update_generic_queue (p_protocol=>p_protocol,
                                         p_inbound_outbound => p_inbound_outbound,
                                         p_description => p_description,
                                         p_queue_count => to_number(p_queue_count),
                                         p_original_protocol=> p_original_protocol,
                                         p_original_inbound=> p_original_inbound);

Line: 2591

    wf_core.context('FND_DOCUMENT_MANAGEMENT', 'Generic_Queue_update');

Line: 2594

END Generic_Queue_Update;

Line: 2614

  select count(1)
  into   l_count
  from   wf_queues wfq
  where  wfq.protocol = p_protocol
  and    wfq.inbound_outbound = p_inbound_outbound;

Line: 2668

  insert into wf_queues
   (protocol,
    inbound_outbound,
    description,
    queue_count,
    disable_flag)
  values
   (p_protocol,
    p_inbound_outbound,
    p_description,
    p_queue_count,
    'N');

Line: 2690

** delete a generic queue with the object type of WF_MESSAGE_PAYLOAD_T which
** is basically just a clob
*/
procedure delete_generic_queue
(p_protocol          IN VARCHAR2,
 p_inbound_outbound  IN VARCHAR2) IS

l_queue_count NUMBER := 0;

Line: 2706

  select queue_count
  into   l_queue_count
  from   wf_queues wfq
  where  wfq.protocol = p_protocol
  and    wfq.inbound_outbound = p_inbound_outbound;

Line: 2724

  wf_queue.generic_queue_delete_check (p_protocol, p_inbound_outbound,
      1, l_queue_count);

Line: 2728

  ** Delete the queues and queue tables
  */
  wf_queue.generic_queue_delete_queues(p_protocol, p_inbound_outbound,
      1, l_queue_count);

Line: 2734

  ** delete an entry in WF_QUEUES table
  */
  delete from wf_queues
  where protocol = p_protocol
  and   inbound_outbound = p_inbound_outbound;

Line: 2746

end delete_generic_queue;

Line: 2765

select protocol, inbound_outbound, queue_count
from   wf_queues
order by protocol, inbound_outbound;

Line: 2853

      select queue_table, dequeue_enabled
      into l_queue_table, l_dequeue_enabled
      from all_queues
      where owner = l_schema_name
        and name = l_queue_name;

Line: 2913

  SELECT  name
  FROM    all_queues
  WHERE   name  = queue_name
  AND     owner = schema
  AND   ((trim(enqueue_enabled) = 'NO') OR (trim(dequeue_enabled) = 'NO'));

Line: 2981

select queue_name
from wf_agents
where system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'))
and name like upper(p_agent);

Line: 2988

select owner
from all_queue_tables
where queue_table = l_qt;

Line: 2995

  select queue_table
  from all_queues
  where owner = l_schema
  and name = l_qname;

Line: 3042

    l_sqlstmt := 'select msg_state, count(*) from '||l_owner||'.'||'aq$'||l_qt
		||' where (queue = :q or queue = :r) group by msg_state';

Line: 3144

      SELECT TRIM(queue_name), TRIM(queue_handler)
      INTO   l_queue_name, l_queue_handler
      FROM   wf_agents
      WHERE  name = upper(p_agent_name)
      AND    SYSTEM_GUID = wf_event.local_system_guid;

Line: 3161

   SELECT TRIM(object_type)
   INTO   l_object_type
   FROM   all_queue_tables
   WHERE  queue_table in
      (
         SELECT queue_table
	 FROM   all_queues
	 WHERE  name = l_qname
	 AND owner = l_schema
      )
   AND owner=l_schema;

Line: 3311

   SELECT     queue_name
   INTO       l_queue_name
   FROM       wf_agents
   WHERE      name  = upper(p_agent_name)
   AND        SYSTEM_GUID = wf_event.local_system_guid;

Line: 3321

   SELECT TRIM(object_type)
   INTO   l_data_type
   FROM   all_queue_tables
   WHERE  queue_table in
      (
         SELECT queue_table
	 FROM   all_queues
	 WHERE  name = l_qname
	 AND owner = l_schema
      )
   AND owner=l_schema;

Line: 3499

   SELECT     queue_name
   INTO       l_queue_name
   FROM       wf_agents
   WHERE      name  = upper(p_agent_name)
   AND        SYSTEM_GUID = wf_event.local_system_guid;

Line: 3509

   SELECT TRIM(object_type)
   INTO   l_data_type
   FROM   all_queue_tables
   WHERE  queue_table in
      (
         SELECT queue_table
	 FROM   all_queues
	 WHERE  name = l_qname
	 AND owner = l_schema
      )
   AND owner=l_schema;

Line: 3556

	 'SELECT msg_id FROM '
	 || l_qname
	 || ' WHERE  msg_state = ''' || 'READY'' '
	 || ' AND enq_time < (sysdate - :1) '
	 || ' AND corr_id like :2 ' using p_age,l_corrid;

Line: 3566

	 'SELECT msg_id FROM '
         || l_qname
         || ' WHERE  msg_state = ''' || 'READY'' '
         || ' AND enq_time < (sysdate - :1) ' using p_age;