REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \ REM dbdrv: checkfile:~PROD:~PATH:~FILE /*=======================================================================+ | Copyright (C) 1995 Oracle Corporation Redwood Shores, California, Usa| | All Rights Reserved. | +=======================================================================+ | FILENAME | wfqueb.pls | DESCRIPTION | Handles all processing for wf queues. | MODIFICATION LOG: | 06 JUN 2001 JWSMITH BUG 1819232 ADA ENHANCEMENT | - Added alt attr for IMG tag | - Added summary attr for table tags | - Added ID attr for TD tag | 02 JAN 2002 JWSMITH BUG 2001012 - Changed schema_name, username, | admin_role, l_schema_name to varchar2(320) | display_name to varchar2(360) | 21 FEB 2003 RRAHEJA BUG 2786474 - Tuned getCntMsgCt code for perf *=======================================================================*/ SET VERIFY OFF WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; set scan off; CREATE OR REPLACE PACKAGE BODY WF_QUEUE AS /* $Header: wfqueb.pls 26.47 2005/02/28 11:30:28 dmani ship $ */ -- -- Exceptions -- dequeue_timeout exception; pragma EXCEPTION_INIT(dequeue_timeout, -25228); dequeue_disabled exception; pragma EXCEPTION_INIT(dequeue_disabled, -25226); dequeue_outofseq exception; pragma EXCEPTION_INIT(dequeue_outofseq, -25237); no_queue exception; pragma EXCEPTION_INIT(no_queue, -24010); shutdown_pending exception; no_savepoint exception; pragma EXCEPTION_INIT(no_savepoint, -1086); msgid_notfound exception; pragma EXCEPTION_INIT(msgid_notfound, -25263); -- ==================================================================== -- -- Private Routine to check for shutdown -- -- ==================================================================== function check_instance return boolean as shutdown varchar2(3); begin select shutdown_pending into shutdown from v$instance; if shutdown = 'YES' then return(TRUE); else return(FALSE); end if; end; -- ==================================================================== -- -- Private Procedure which processes the payload dequeued off the -- Inbound queue . -- -- ==================================================================== -- Process_Inbound_Event (PRIVATE) -- Executes the payload dequeued off the inbound queue -- IN -- itemtype - itemtype,itemkey,actid to uniquely identify the -- itemkey - activity -- actid - -- message_handle - pointer to queue message -- p_payload - the message payload . Lets have it as in/out parameter -- so that if callback (for which it is in/out) changes something -- we can have it. procedure Process_Inbound_Event(itemtype in varchar2, itemkey in varchar2, actid in number, message_handle in raw, p_payload in out nocopy wf_payload_t) as colon number; status varchar2(30); plist varchar2(4000); attrpair varchar2(4000); delimiter number; aname varchar2(40); avalue varchar2(4000); lcorrelation varchar2(80); nvalue number; --required but not used by wf_engine.CB dvalue date; --required but not used by wf_engine.CB begin --process the parameter list. plist:= p_payload.param_list; if plist is not null then loop -- if plist is null then EXIT; end if; delimiter:=instr(plist,'^'); if delimiter = 0 then attrpair:=plist; else attrpair:=substr(plist,1,delimiter-1); end if; aname := upper(substr(attrpair,1,instr(attrpair,'=')-1)); avalue := substr(attrpair,instr(attrpair,'=')+1); begin --Set the value for the attribute wf_engine.SetItemAttrText(itemtype, itemkey, aname, avalue); exception when others then if ( wf_core.error_name = 'WFENG_ITEM_ATTR' ) then --If the attribute does not exist first create it --and then add the value Wf_Engine.AddItemAttr(itemtype, itemkey, aname); Wf_Engine.SetItemAttrText(itemtype, itemkey, aname, avalue); else raise; end if; end; exit when delimiter = 0; plist := substr(plist,delimiter+1); end loop; end if; --if payload contains a colon, then its ERROR else its COMPLETE status colon:= instr(p_payload.result,':'); if colon=0 or p_payload.result is null then -- check if activity is already complete wf_item_activity_status.status(itemtype,itemkey,actid,status); if (status is not null) and (status <> 'COMPLETE') then -- mark activity as Complete: wf_engine.CB(command => 'COMPLETE', context =>itemtype||':'|| itemkey ||':'|| actid, text_value => p_payload.result, number_value => nvalue, date_value => dvalue); end if; else -- at the moment we only accept :ERROR: (may add other statuses later) if substr(p_payload.result,colon+1,5) = 'ERROR' then begin wf_core.clear; -- set the function name for courtesy. wf_core.token('FUNCTION_NAME', Wf_Activity.activity_function(itemtype, itemkey,actid)); wf_core.raise('WF_EXT_FUNCTION'); exception when others then null; end; --function name on payload is upto 200 char so use it to record error wf_core.error_stack := p_payload.function_name; wf_engine.CB(command => 'ERROR', context =>itemtype||':'|| itemkey ||':'|| actid, text_value => p_payload.result, number_value => nvalue, date_value => dvalue); end if; end if; --If we came successfully till here let us purge off the --data from the Q wf_queue.PurgeEvent(wf_queue.InboundQueue, message_handle, FALSE); exception when others then Wf_Core.Context('Wf_Queue', 'Process_Inbound_Event', itemtype,itemkey); raise; end Process_Inbound_Event; -- ==================================================================== -- Queue Setup Functions (PUBLIC) -- ==================================================================== function DeferredQueue return varchar2 as begin if (wf_queue.name_init = FALSE) then wf_queue.set_queue_names; end if; return (wf_queue.deferred_queue_name); exception when others then Wf_Core.Context('Wf_Queue', 'DeferredQueue'); raise; end; function OutboundQueue return varchar2 as begin if (wf_queue.name_init = FALSE) then wf_queue.set_queue_names; end if; return (wf_queue.outbound_queue_name); exception when others then Wf_Core.Context('Wf_Queue', 'OutboundQueue'); raise; end; function InboundQueue return varchar2 as begin if (wf_queue.name_init = FALSE) then wf_queue.set_queue_names; end if; return (wf_queue.inbound_queue_name); exception when others then Wf_Core.Context('Wf_Queue', 'InboundQueue'); raise; end; -- NAME: Set_queue_names (PRIVATE) -- called once at the beginning of a session to set up queue names -- when AQ supports db synonyms, remove this and use synonyms instead procedure set_queue_names as schema_name varchar2(320); begin --dont bother re-executing this if already initialized. if wf_queue.name_init then return; end if; schema_name := wf_core.translate('WF_SCHEMA'); -- Do not determine account name by STANDALONE vs. EMBEDDED any more -- Current_schema is the schema in effect. -- Sys_context is an 8i feature. Below allows us to tag on the -- intended schema whether the install is with invoker's right or -- definer's right (default). begin select sys_context('USERENV', 'CURRENT_SCHEMA') into wf_queue.account_name from sys.dual; exception when OTHERS then wf_queue.account_name := NULL; end; wf_queue.deferred_queue_name := schema_name||'.WF_DEFERRED_QUEUE_M'; wf_queue.outbound_queue_name := schema_name||'.WF_OUTBOUND_QUEUE'; wf_queue.inbound_queue_name := schema_name||'.WF_INBOUND_QUEUE'; wf_queue.name_init := TRUE; exception when others then Wf_Core.Context('Wf_Queue', 'Set_queue_names'); raise; end set_queue_names; -- ==================================================================== -- Public routines -- ==================================================================== -- NAME: PurgeEvent -- removes the event from the specified queue WITHOUT PROCESSING -- queuename - the queue to purge -- message_handle - the specific event to purge -- procedure PurgeEvent(queuename in varchar2, message_handle in raw, multiconsumer in boolean default FALSE) as event wf_payload_t; dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; msg_id raw(16); begin if message_handle is not null then dequeue_options.dequeue_mode := dbms_aq.REMOVE; dequeue_options.msgid := message_handle; dequeue_options.wait := dbms_aq.NO_WAIT; dequeue_options.navigation := dbms_aq.FIRST_MESSAGE; -- check if we need to have a consumer if (multiconsumer) then dequeue_options.consumer_name := wf_queue.account_name; end if; dbms_aq.dequeue ( queue_name => queuename, dequeue_options => dequeue_options, message_properties => message_properties, payload => event, msgid => msg_id ); end if; exception when dequeue_timeout then null; -- not found on queue so must already be removed. when msgid_notfound then null; -- Already purged from the queue. when others then Wf_Core.Context('Wf_Queue', 'PurgeEvent', queuename, rawtohex(message_handle)); raise; end PurgeEvent; -- NAME: PurgeItemtype -- removes all events belonging to an itemtype from the specified queue -- ** WARNING ** IT DOES NOT PROCESS THE EVENT -- queuename - the queue to purge -- itemtype - the itemtype to purge -- procedure PurgeItemtype(queuename in varchar2, itemtype in varchar2 default null, correlation in varchar2 default null ) as event wf_payload_t; dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; msg_id raw(16); begin dequeue_options.dequeue_mode := dbms_aq.REMOVE; dequeue_options.wait := dbms_aq.NO_WAIT; dequeue_options.navigation := dbms_aq.FIRST_MESSAGE; wf_queue.set_queue_names; if correlation is not null then dequeue_options.correlation := correlation; else dequeue_options.correlation := wf_queue.account_name||nvl(itemtype,'%'); end if; LOOP dbms_aq.dequeue ( queue_name => queuename, dequeue_options => dequeue_options, message_properties => message_properties, payload => event, msgid => msg_id ); END LOOP; exception when dequeue_timeout then null; -- nothing left on queue to remove when others then Wf_Core.Context('Wf_Queue', 'PurgeItemtype', queuename, itemtype); raise; end PurgeItemtype; -- ProcessDeferredEvent (PRIVATE) -- Executes the event payload dequeued off the deferred queue -- IN -- itemtype - itemtype,itemkey,actid to uniquely identify the -- itemkey - activity -- actid - -- message_handle - pointer to queue message -- minthreshold - threshold levels of the background engine -- maxthreshold -- procedure ProcessDeferredEvent(itemtype in varchar2, itemkey in varchar2, actid in number, message_handle in raw, minthreshold in number, maxthreshold in number) as begin Wf_Item_Activity_Status.Create_Status(itemtype, itemkey, actid, wf_engine.eng_active, null, null, null); -- Continue processing on activity begin begin begin savepoint wf_savepoint; Wf_Engine_Util.Process_Activity(itemtype, itemkey, actid, maxthreshold, TRUE); -- we successfully processed the activity so dequeue it. wf_queue.PurgeEvent(wf_queue.DeferredQueue, message_handle, TRUE); Exception when others then -- In the unlikely event this process thread raises an exception: -- 1. rollback any work in this process thread -- raise an error for the next excption handler to complete -- remaining steps. rollback to wf_savepoint; raise; end; exception when NO_SAVEPOINT then -- Catch any savepoint error in case of a commit happened. Wf_Core.Token('ACTIVITY', Wf_Engine.GetActivityLabel(actid)); Wf_Core.Raise('WFENG_COMMIT_IN_PROCESS'); end; exception when OTHERS then -- Remaining steps for proces thread raises an exception: -- 2. set this activity to error status -- 3. execute the error process (if any) -- 4. clear the error to continue with next activity -- **note the error stack will refer to the actid that has been -- rolled back! Wf_Core.Context('Wf_Queue', 'ProcessDeferredEvent', itemtype, to_char(minthreshold), to_char(maxthreshold)); Wf_Item_Activity_Status.Set_Error(itemtype, itemkey, actid, wf_engine.eng_exception, FALSE); Wf_Engine_Util.Execute_Error_Process(itemtype, itemkey, actid, wf_engine.eng_exception); Wf_Core.Clear; end; -- Commit work to insure this activity thread doesn't interfere -- with others. commit; Fnd_Concurrent.Set_Preferred_RBS; exception when others then Wf_Core.Context('Wf_Queue', 'ProcessDeferredEvent', itemtype, to_char(minthreshold), to_char(maxthreshold)); raise; end ProcessDeferredEvent; --Name: EnqueueInbound (PUBLIC) --Enqueues the result from an outbound event onto --the inbound queue. Wf will mark this as complete with the --given result when it processes the queue. procedure EnqueueInbound( itemtype in varchar2, itemkey in varchar2, actid in number, result in varchar2 default null, attrlist in varchar2 default null, correlation in varchar2 default null, error_stack in varchar2 default null) as handle raw(16); lcorrelation varchar2(80); lresult varchar2(30); begin if correlation is not null then lcorrelation := correlation; else wf_queue.set_queue_names; lcorrelation := wf_queue.account_name||itemtype; end if; -- if error stack is defined then set result to ERROR. if error_stack is null then lresult := result; else lresult := ':ERROR'; end if; wf_queue.Enqueue_Event(queuename =>wf_queue.InboundQueue, itemtype =>enqueueInbound.itemtype, itemkey =>enqueueInbound.itemkey, actid =>enqueueInbound.actid, funcname =>enqueueInbound.error_stack, correlation =>lcorrelation, paramlist =>enqueueInbound.attrlist, result =>lresult, message_handle =>handle); exception when others then Wf_Core.Context('Wf_Queue', 'EnqueueInbound', itemtype, itemkey, actid); raise; end EnqueueInbound; function Get_param_list (itemtype in varchar2, itemkey in varchar2, actid in number) return varchar2 as startdate date; paramlist varchar2(4000); lvalue varchar2(4000); cursor attr_list is 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; begin paramlist:=null; startdate:=wf_item.active_date(itemtype,itemkey); for attr_row in attr_list loop if (attr_row.value_type = 'ITEMATTR' and attr_row.text_value is not null) then -- null itemattr text_value means null value, not an error lvalue := wf_engine.GetItemAttrText(itemtype,itemkey, attr_row.text_value); else --must be CONSTANT if (attr_row.type = 'NUMBER') then if (attr_row.format is null) then lvalue := to_char(attr_row.NUMBER_VALUE); else lvalue := to_char(attr_row.NUMBER_VALUE, attr_row.format); end if; elsif (attr_row.type = 'DATE') then if (attr_row.format is null) then lvalue := to_char(attr_row.DATE_VALUE); else lvalue := to_char(attr_row.DATE_VALUE, attr_row.format); end if; else lvalue := attr_row.text_value; end if; end if; if paramlist is not null then -- Overflow, cannot hold anymore attributes. if (lengthb(paramlist||'^') > 4000) then exit; else paramlist := paramlist||'^'; end if; end if; if (lengthb(paramlist||attr_row.name||'='||lvalue) > 4000) then -- Overflow, cannot hold anymore attributes. paramlist:=substrb(paramlist||attr_row.name||'='||lvalue, 1, 4000); exit; else paramlist := paramlist||attr_row.name||'='||lvalue; end if; end loop; return(paramlist); exception when others then Wf_Core.Context('Wf_Queue', 'Get_param_list', itemtype, itemkey, actid); raise; end Get_param_list; --Name: DequeueOutbound (PUBLIC) procedure DequeueOutbound( dequeuemode in number, navigation in number default 1, correlation in varchar2 default null, itemtype in varchar2 default null, payload out NOCOPY wf_payload_t, message_handle in out NOCOPY raw, timeout out NOCOPY boolean) as lcorrelation varchar2(80); begin wf_queue.set_queue_names; if correlation is not null then lcorrelation := correlation; else lcorrelation := wf_queue.account_name||nvl(itemtype,'%'); end if; wf_queue.Dequeue_Event(queuename =>wf_queue.OutboundQueue, dequeuemode =>DequeueOutbound.dequeuemode, navigation =>DequeueOutbound.navigation, correlation =>lcorrelation, payload =>DequeueOutbound.payload, message_handle =>DequeueOutbound.message_handle, timeout =>DequeueOutbound.timeout); exception when others then Wf_Core.Context('Wf_Queue', 'DequeueOutbound', payload.itemtype, payload.itemkey, payload.actid); raise; end DequeueOutbound; --Name: DequeueEventDetail (PUBLIC) -- --Wrapper to Dequeue_Event in which the payload is EXPanded out to avoid --use of type itemtypes. procedure DequeueEventDetail( dequeuemode in number, navigation in number default 1, correlation in varchar2 default null, itemtype in out NOCOPY varchar2, itemkey out NOCOPY varchar2, actid out NOCOPY number, function_name out NOCOPY varchar2, param_list out NOCOPY varchar2, message_handle in out NOCOPY raw, timeout out NOCOPY boolean) as event wf_payload_t; lcorrelation varchar2(80); begin wf_queue.set_queue_names; --use the correlation or default it if null if DequeueEventDetail.correlation is not null then lcorrelation := DequeueEventDetail.correlation; else lcorrelation := wf_queue.account_name||nvl(itemtype,'%'); end if; -- call dequeue to retrieve the event wf_queue.Dequeue_Event(queuename=>wf_queue.OutboundQueue, dequeuemode=>DequeueEventDetail.dequeuemode, navigation =>DequeueEventDetail.navigation, correlation=>lcorrelation, payload=>event, message_handle=>DequeueEventDetail.message_handle, timeout =>DequeueEventDetail.timeout); --expand the payload structure DequeueEventDetail.itemtype:=event.itemtype; DequeueEventDetail.itemkey:=event.itemkey; DequeueEventDetail.actid:=event.actid; DequeueEventDetail.function_name:=event.function_name; DequeueEventDetail.param_list:=event.param_list; exception when others then Wf_Core.Context('Wf_Queue', 'DequeueEventDetail', itemtype||':'||itemkey,to_char(actid)); raise; end DequeueEventDetail; --Dequeue_Event (PRIVATE) -- --Dequeues an event (message) from any queue --IN -- QueueName - the queue name, may contain owner or database -- DeQueueMode - either 1 (Browse), 2 (Locked) or 3 (Remove) -- Navigation - either First or Next -- Correlation - helps restrict the queue -- Payload - the event actually dequeued -- message_handle - id for the event -- timeout - determines if anything was found or if the q timedout. procedure Dequeue_Event(queuename in varchar2, dequeuemode in number, navigation in number default 1, correlation in varchar2 default null, payload out NOCOPY wf_payload_t, message_handle in out NOCOPY raw, timeout out NOCOPY boolean, multiconsumer in boolean default FALSE) as dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; snap_too_old exception; pragma exception_init(snap_too_old, -1555); begin -- find out the schema name wf_queue.set_queue_names; dequeue_options.dequeue_mode := dequeuemode; dequeue_options.wait := dbms_aq.NO_WAIT; dequeue_options.navigation := navigation; -- if message_handle is set then use it instead of correlation -- NOTE: if message_handle is set FIRST/NEXT_MESSAGE dont have effect if message_handle is not null then dequeue_options.msgid := message_handle; dequeue_options.correlation := null; message_handle := null; else -- set correlation to item_type or % if its null if correlation is null then dequeue_options.correlation := '%'; else dequeue_options.correlation := correlation; end if; end if; -- check if we need to have a consumer if (multiconsumer) then dequeue_options.consumer_name := wf_queue.account_name; end if; begin dbms_aq.dequeue( queue_name => Dequeue_Event.queuename, dequeue_options => dequeue_options, message_properties => message_properties, payload => Dequeue_Event.payload, msgid => message_handle ); exception when snap_too_old then --Workaround for AQ when receiving ORA-01555 using NEXT_MESSAGE as --navigation. We will try to set to FIRST_MESSAGE and dequeue to --silently handle this exception. if (dequeue_options.navigation = dbms_aq.FIRST_MESSAGE) then raise; else dequeue_options.navigation := dbms_aq.FIRST_MESSAGE; dbms_aq.dequeue( queue_name => Dequeue_Event.queuename, dequeue_options => dequeue_options, message_properties => message_properties, payload => Dequeue_Event.payload, msgid => message_handle ); end if; when OTHERS then raise; end; timeout:= FALSE; exception when dequeue_timeout then timeout := TRUE; when others then if correlation is null then Wf_Core.Context('WF_QUEUE', 'Dequeue_Event', queuename, '%'); else Wf_Core.Context('WF_QUEUE', 'Dequeue_Event', queuename, correlation); end if; timeout := FALSE; raise; end Dequeue_Event; -- Activity_Valid (PRIVATE) -- checks the deferred activity is valid for processing -- -- IN -- event - the event to check -- message_handle of event in the deferred queue -- maxthreshold - the threshold level -- minthreshold -- function activity_valid (event in wf_payload_t, message_handle in raw, maxthreshold in number default null, minthreshold in number default null) return BOOLEAN is cost pls_integer; litemtype varchar2(8); l_begdate date; -- resource_busy exception; pragma exception_init(resource_busy, -00054); begin -- Activity must be valid if -- 1) in given cost range -- 2) parent is not suspended -- note: suspendprocess/resumeprocess will remove/add deferred jobs -- check begin date as well -- move the BEGIN_DATE, SYSDATE comparion to a separate clause 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; -- dont bother locking: the original msg has been locked on the queue -- for update of CWIAS.ACTIVITY_STATUS NOWAIT; -- dont bother checking if parent is suspended. -- the suspend process should remove any jobs from the queue, -- but if any get through, process_activity will manage it. -- -- begin date has not reached yet, leave the message alone. -- this is to work around a problem where the aq delay seems to -- to be shorter than expected if (l_begdate > sysdate) then return(FALSE); end if; if cost < nvl(minthreshold,cost) or cost > nvl(maxthreshold,cost) then return(FALSE); else return(TRUE); end if; exception when no_data_found then -- this event is no longer valid so remove it from the queue -- happens when a rewind moved activity to history table -- or the activity status is no longer defered wf_queue.PurgeEvent(wf_queue.DeferredQueue,message_handle,TRUE); return(FALSE); when resource_busy then return(FALSE); when others then Wf_Core.Context('Wf_Queue', 'Activity_valid', 'Invalid', event.itemtype||':'||event.itemkey, to_char(event.actid)); return(FALSE); end activity_valid; -- -- ==================================================================== -- -- Enqueue_Event (PRIVATE) -- Enqueues a message onto any WF queue (because all queues have same payload) -- procedure Enqueue_Event(queuename in varchar2, itemtype in varchar2, itemkey in varchar2, actid in number, correlation in varchar2 default null, delay in number default 0, funcname in varchar2 default null, paramlist in varchar2 default null, result in varchar2 default null, message_handle in out NOCOPY raw, priority in number default null) as event wf_payload_t; enqueue_options dbms_aq.enqueue_options_t; message_properties dbms_aq.message_properties_t; begin wf_queue.set_queue_names; -- construct the event object event:=wf_payload_t(itemtype,itemkey,actid,funcname,paramlist,result); -- dont make the data visible on the queue until a commit is issued -- this way queue data and normal table data (wf statuses) are in synch. enqueue_options.visibility := DBMS_AQ.ON_COMMIT; -- Set the delay if any if delay <0 then message_properties.delay := 0; else -- message_properties.delay is BINARY_INTEGER, so check if delay is -- too big, and set the max delay to be (2**31)-1. if (delay >= power(2,31)) then message_properties.delay := power(2,31)-1; else message_properties.delay := delay; end if; end if; if correlation is not null then message_properties.correlation := enqueue_event.correlation; else message_properties.correlation := wf_queue.account_name||itemtype; end if; -- check the correlation is always set to something -- else it wil never be dequeued because we always default the dequeue -- corellation to '%' if message_properties.correlation is null then -- this shouldnt happen. message_properties.correlation := '%'; end if; -- Set the priority so that we can dequeue by priority if priority is not null then message_properties.priority := priority; end if; dbms_aq.enqueue ( queue_name => Enqueue_Event.queuename, enqueue_options => enqueue_options, message_properties => message_properties, payload => event, msgid => message_handle ); exception when others then Wf_Core.Context('Wf_Queue', 'Enqueue_event', itemtype, itemkey, to_char(actid), to_char(delay)); raise; end; -- ProcessInboundQueue (PUBLIC) -- reads everythig off the Inbound queue and records it as complete -- with the given result and updates item attributes as specified in -- the paramlist procedure ProcessInboundQueue (itemtype in varchar2 default null, correlation in varchar2 default null) as payload wf_payload_t; navigation varchar2(10); timeout boolean:= FALSE; cursor_name number; row_processed integer; message_handle raw(16); -- first_time boolean := TRUE; plist varchar2(4000); lcorrelation varchar2(80); nothing_processed boolean := TRUE; begin commit; Fnd_Concurrent.Set_Preferred_RBS; wf_queue.set_queue_names; if correlation is not null then lcorrelation := correlation; else lcorrelation := wf_queue.account_name||nvl(itemtype,'%'); end if; -- loop through the inbound queue. loop --Process until nothing left on the queue navigation := dbms_aq.FIRST_MESSAGE; nothing_processed :=TRUE; loop -- Process till timeout message_handle:=null; --Lets set a savepoint here --We would use this savepoint to rollback if we found that a --lock is not possible in this session for the reterived itemytype key wf_queue.Dequeue_Event(wf_queue.InboundQueue, dbms_aq.LOCKED, navigation, lcorrelation, payload, message_handle, timeout); -- if no message is found, the message may be enqueued with the -- old correlation format, so reset the correlation id and retry. if (navigation = dbms_aq.FIRST_MESSAGE and message_handle is null and correlation is null and lcorrelation <> nvl(itemtype,'%')) then lcorrelation := nvl(itemtype,'%'); goto nextmesg; end if; --else check timeout if (timeout) then EXIT; end if; --Bug 2607770 --Ensure that we have got a message --Now try to acquire the lock --Check the parameterlist null/not within Process_Inbound_Event if wf_item.acquire_lock(payload.itemtype, payload.itemkey) then --Process the payload wf_queue.Process_Inbound_Event(itemtype=>payload.itemtype, itemkey=>payload.itemkey, actid=>payload.actid, message_handle=>ProcessInboundQueue.message_handle, p_payload => payload); nothing_processed:=FALSE; end if; -- commit any processing or any clean up commit; Fnd_Concurrent.Set_Preferred_RBS; navigation := dbms_aq.NEXT_MESSAGE; <> -- This is for the case when we reset the corrid and verify null; end loop; -- process till timeout exit when nothing_processed; end loop; exception when others then Wf_Core.Context('Wf_Queue', 'ProcessInboundQueue'); raise; end ProcessInboundQueue; procedure ProcessDeferredQueue (itemtype in varchar2 default null, minthreshold in number default null, maxthreshold in number default null, correlation in varchar2 default null) as payload wf_payload_t; timeout boolean:= FALSE; navigation varchar2(10); row_processed integer; message_handle raw(16); -- first_time boolean := TRUE; nothing_processed boolean:=TRUE; lcorrelation varchar2(80); begin wf_queue.set_queue_names; if correlation is not null then lcorrelation := correlation; -- for standalone, we first try the old correlation id format. elsif (wf_core.translate('WF_INSTALL') = 'STANDALONE' and itemtype is not null) then lcorrelation := itemtype; -- for embedded, there was never the old format, so we are fine. -- or it is standalone with null item type, we cannot support the -- old correlation id format; otherwise, it will pick up everything. else lcorrelation := wf_queue.account_name||nvl(itemtype,'%'); end if; loop -- keep processing the queue until there is nothing left navigation := dbms_aq.FIRST_MESSAGE; nothing_processed :=TRUE; loop -- keep processing until a timeout. message_handle:=null; wf_queue.Dequeue_Event( wf_queue.DeferredQueue, dbms_aq.LOCKED, navigation, lcorrelation, payload, message_handle, timeout, TRUE); -- if no message is found, the message may be enqueued with the -- new correlation format, so reset the correlation id and retry. if (navigation = dbms_aq.FIRST_MESSAGE and message_handle is null and correlation is null and lcorrelation = itemtype) then lcorrelation := wf_queue.account_name||nvl(itemtype,'%'); -- otherwise, process the message else if (timeout) then EXIT; end if; -- -- Execute the PL/SQL call stored in the payload if this is valid -- if activity_valid (payload, message_handle, maxthreshold, minthreshold ) AND wf_item.acquire_lock(payload.itemtype,payload.itemkey) then wf_queue.ProcessDeferredEvent(itemtype=>payload.itemtype, itemkey=>payload.itemkey, actid=>payload.actid, message_handle=>ProcessDeferredQueue.message_handle, minthreshold=>ProcessDeferredQueue.minthreshold, maxthreshold=>ProcessDeferredQueue.maxthreshold); nothing_processed:=FALSE; end if; -- commit any processing or any clean up from activity_valid commit; Fnd_Concurrent.Set_Preferred_RBS; -- -- Test for Instance Shutdown -- if wf_queue.check_instance then raise shutdown_pending; end if; navigation := dbms_aq.NEXT_MESSAGE; end if; end loop; -- process till time out exit when nothing_processed; end loop; exception when dequeue_disabled then Wf_Core.Context('Wf_Queue', 'ProcessDeferredQueue', 'Queue shutdown'); raise; when shutdown_pending then Wf_Core.Context('Wf_Queue', 'ProcessDeferredQueue', 'DB shutting down'); raise; when others then Wf_Core.Context('Wf_Queue', 'ProcessDeferredQueue'); raise; end ProcessDeferredQueue; --============================================================ -- Support utilities. not sure if we want to release these --============================================================ -- GetMessageHandle -- does a sequential search through the queue for the message handle function GetMessageHandle(queuename in varchar2, itemtype in varchar2, itemkey in varchar2, actid in number, correlation in varchar2 default null, multiconsumer in boolean default FALSE) return raw is event wf_payload_t; dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; msg_id raw(16); begin dequeue_options.dequeue_mode := dbms_aq.BROWSE; dequeue_options.wait := dbms_aq.NO_WAIT; wf_queue.set_queue_names; if correlation is not null then dequeue_options.correlation := correlation; else dequeue_options.correlation := wf_queue.account_name||nvl(itemtype,'%'); end if; if (multiconsumer) then dequeue_options.consumer_name := wf_queue.account_name; end if; --execute first read dequeue_options.navigation := dbms_aq.FIRST_MESSAGE; dbms_aq.dequeue ( queue_name => queuename, dequeue_options => dequeue_options, message_properties => message_properties, payload => event, msgid => msg_id ); if event.itemtype = itemtype and event.itemkey = itemkey and event.actid = nvl(actid,event.actid) then return (msg_id); end if; -- loop with next message LOOP dequeue_options.navigation := dbms_aq.NEXT_MESSAGE; dbms_aq.dequeue ( queue_name => queuename, dequeue_options => dequeue_options, message_properties => message_properties, payload => event, msgid => msg_id ); if event.itemtype = itemtype and event.itemkey = itemkey and event.actid = actid then return (msg_id); end if; END LOOP; return(null); exception -- timeout will fall to here when others then return(null); end GetMessageHandle; --============================================================= -- PUBLIC API to dequeue from exception queue to wf_error -- queue --============================================================= procedure DequeueException (queuename in varchar2) is l_event wf_event_t; x_dequeue_options dbms_aq.dequeue_options_t; x_message_properties dbms_aq.message_properties_t; x_msgid RAW(16); erragt wf_agent_t; lsysname varchar2(30); cmd varchar2(1000); no_messages exception; pragma exception_init (no_messages, -25228); begin -- To Dequeue from Exception Queue, consumer name must be null x_dequeue_options.consumer_name := null; x_dequeue_options.wait := 1; loop begin dbms_aq.dequeue(queue_name => queuename, dequeue_options => x_dequeue_options, message_properties => x_message_properties, /* OUT */ payload => l_event, /* OUT */ msgid => x_msgid); /* OUT */ /* ** Update the event to let everyone know it expired */ l_event.SetErrorMessage(wf_core.translate('WFE_MESSAGE_EXPIRED')); l_event.addParameterToList('ERROR_NAME', wf_core.translate('WFE_MESSAGE_EXPIRED') ); l_event.addParameterToList('ERROR_TYPE', 'ERROR'); /* ** As we can't use the private API SaveErrorToQueue ** we copy a little bit of code to do it */ select name into lsysname from wf_systems where guid = wf_core.translate('WF_SYSTEM_GUID'); erragt := wf_agent_t('WF_ERROR', lsysname); cmd := 'begin WF_ERROR_QH.enqueue(:v1, :v2); end;'; execute immediate cmd using in l_event, in erragt; commit; exception when no_messages then if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_event, 'wf.plsql.WF_QUEUE.DequeueException.queue_empty', 'No more messages in ExceptionDequeue.'); end if; exit; end; end loop; exception when others then Wf_Core.Context('Wf_Queue', 'DequeueException',queuename); raise; end DequeueException; --============================================================= -- Declare all developer APIs for Inbound queue manipulation -- --============================================================= -- -- ClearMsgStack -- Clears runtime cache procedure ClearMsgStack is begin wf_queue.stck_itemtype(1) := ''; wf_queue.stck_itemkey(1) := ''; wf_queue.stck_actid(1) := 0; wf_queue.stck_ctr := 0; exception when others then Wf_Core.Context('Wf_Queue', 'ClearMsgStack'); raise; end ClearMsgStack; --Name: WriteMsg --writes a message from stack to the queue procedure WriteMsg ( itemtype in varchar2, itemkey in varchar2, actid in number) is i pls_integer; begin i := wf_queue.SearchMsgStack(itemtype,itemkey,actid); wf_queue.EnqueueInbound( itemtype=>wf_queue.stck_itemtype(i), itemkey =>wf_queue.stck_itemkey(i), actid =>wf_queue.stck_actid(i), result =>wf_queue.stck_result(i), attrlist=>wf_queue.stck_attrlist(i)); exception when others then Wf_Core.Context('Wf_Queue', 'WriteMsg'); raise; end WriteMsg; --Name: CreateMsg --creates a message on the stack -- procedure CreateMsg ( itemtype in varchar2, itemkey in varchar2, actid in number) is i pls_integer; begin i := wf_queue.SearchMsgStack(itemtype,itemkey,actid); exception when others then Wf_Core.Context('Wf_Queue', 'CreateMsg'); raise; end CreateMsg; --Name: SetMsgAttr (PUBLIC) --Appends message attributes. -- procedure SetMsgAttr( itemtype in varchar2, itemkey in varchar2, actid in number, attrName in varchar2, attrValue in varchar2) is i pls_integer; begin i := SearchMsgStack (itemtype, itemkey, actid); if wf_queue.stck_attrlist(i) is null then wf_queue.stck_attrlist(i) := upper(attrName)||'='||AttrValue; else wf_queue.stck_attrlist(i) := wf_queue.stck_attrlist(i) ||'^'||attrName||'='||AttrValue; end if; exception when others then Wf_Core.Context('Wf_Queue', 'SetMsgAttr', itemtype, itemkey, actid, to_char(stck_ctr)); raise; end SetMsgAttr; --Name: SetMsgResult (PUBLIC) --Sets the result value for this message. -- procedure SetMsgResult( itemtype in varchar2, itemkey in varchar2, actid in number, result in varchar2) is i pls_integer; begin i := SearchMsgStack (itemtype, itemkey, actid); wf_queue.stck_result(i) :=result; exception when others then Wf_Core.Context('Wf_Queue', 'AddResult', itemtype, itemkey, actid, to_char(stck_ctr)); raise; end SetMsgResult; -- -- AddNewMsg (PRIVATE) -- Add a new message to the stack -- IN -- itemtype - item itemtype -- itemkey - item itemkey -- actid - instance id of process -- procedure AddNewMsg( itemtype in varchar2, itemkey in varchar2, actid in number) is begin -- Add the process to the stack wf_queue.stck_ctr := wf_queue.stck_ctr + 1; wf_queue.stck_itemtype(wf_queue.stck_ctr) := itemtype; wf_queue.stck_itemkey(wf_queue.stck_ctr) := itemkey; wf_queue.stck_actid(wf_queue.stck_ctr) := actid; wf_queue.stck_result(wf_queue.stck_ctr) := null; wf_queue.stck_AttrList(wf_queue.stck_ctr) := null; exception when others then Wf_Core.Context('Wf_Queue', 'AddNewMsg', itemtype, itemkey, actid, to_char(stck_ctr)); raise; end AddNewMsg; --Name: SearchMsgStack (PRIVATE) --Desc: sequential search of the message stack -- starting from the top -- function SearchMsgStack ( itemtype in varchar2, itemkey in varchar2, actid in number) RETURN number is i pls_integer; begin if ( nvl(wf_queue.stck_ctr, 0) > 0) then for i in reverse 1 .. wf_queue.stck_ctr loop if ((itemtype = wf_queue.stck_itemtype(i)) and (itemkey = wf_queue.stck_itemkey(i)) and (actid = wf_queue.stck_actid(i))) then -- Found a match. return(i); end if; end loop; end if; -- not in the Stack so add it. AddNewMsg(itemtype,itemkey,actid); return (stck_ctr); end SearchMsgStack; -- -- Generic_Queue_Display -- Produce list of generic_queues -- -- MODIFICATION LOG: -- 06-JUN-2001 JWSMITH BUG 1819232 - added alt attrib for IMG tag for ADA -- - Added summary attr for table tags for ADA -- - Added ID attr for TD tags for ADA -- procedure Generic_Queue_Display is username varchar2(320); -- Username to query admin_role varchar2(320); -- Role for admin mode admin_mode varchar2(1) := 'N'; realname varchar2(360); -- Display name of username s0 varchar2(2000); -- Dummy l_error_msg varchar2(240); l_url varchar2(240); l_media varchar2(240) := wfa_html.image_loc; l_icon varchar2(40); l_text varchar2(240); l_onmouseover varchar2(240); cursor queues_cursor is 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; rowcount number; begin -- Check current user has admin authority wfa_sec.GetSession(username); username := upper(username); wf_directory.GetRoleInfo(username, realname, s0, s0, s0, s0); admin_role := wf_core.translate('WF_ADMIN_ROLE'); if (admin_role = '*' or Wf_Directory.IsPerformer(username, admin_role)) then admin_mode := 'Y'; else l_error_msg := wf_core.translate('WFPREF_INVALID_ADMIN'); end if; -- Set page title htp.htmlOpen; htp.headOpen; htp.p(''); htp.title(wf_core.translate('WFGENERIC_QUEUE_TITLE')); wfa_html.create_help_function('wf/links/dmr.htm?DMREP'); htp.headClose; wfa_sec.Header(FALSE, '',wf_core.translate('WFGENERIC_QUEUE_TITLE'), FALSE); htp.br; IF (admin_mode = 'N') THEN htp.center(htf.bold(l_error_msg)); return; END IF; -- Column headers htp.tableOpen(cattributes=>'border=1 cellpadding=3 bgcolor=white width="100%" summary=""'); htp.tableRowOpen(cattributes=>'bgcolor=#006699'); htp.tableHeader(cvalue=>''|| wf_core.translate('PROTOCOL')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('PROTOCOL') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('QUEUE_DESCRIPTION')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('QUEUE_DESCRIPTION') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('INBOUND_PROMPT')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('INBOUND_PROMPT') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('QUEUE_COUNT')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('QUEUE_COUNT') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('VIEW_DETAIL')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('VIEW_DETAIL') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('DELETE')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('DELETE') || '"'); htp.tableRowClose; htp.tableRowOpen; htp.tableRowClose; -- Show all nodes for queues in queues_cursor loop htp.tableRowOpen(null, 'TOP'); htp.tableData(htf.anchor2( curl=>wfa_html.base_url|| '/wf_queue.generic_queue_edit?p_protocol='|| queues.protocol||'&p_inbound_outbound='|| queues.inbound_outbound, ctext=>queues.protocol, ctarget=>'_top'), 'Left', cattributes=>'headers="' || wf_core.translate('PROTOCOL') || '"'); htp.tableData(queues.description, 'left', cattributes=>'headers="' || wf_core.translate('QUEUE_DESCRIPTION') || '"'); htp.tableData(queues.inbound_outbound, 'left', cattributes=>'headers="' || wf_core.translate('INBOUND_PROMPT') || '"'); htp.tableData(queues.queue_count, 'left', cattributes=>'headers="' || wf_core.translate('QUEUE_COUNT') || '"'); htp.tableData(htf.anchor2(curl=>wfa_html.base_url|| '/wf_queue.Generic_Queue_View_Detail?p_protocol='|| queues.protocol||'&p_inbound_outbound='|| queues.inbound_outbound, ctext=>''||wf_core.translate('FIND') || ''), 'center', cattributes=>'valign="MIDDLE" headers="' || wf_core.translate('VIEW_DETAIL') || '"'); htp.tableData(htf.anchor2(curl=>wfa_html.base_url|| '/wf_queue.generic_queue_confirm_delete?p_protocol='|| queues.protocol||'&p_inbound_outbound='|| queues.inbound_outbound, ctext=>'' || wf_core.translate('WFRTG_DELETE') || ''), 'center', cattributes=>'valign="MIDDLE" headers="' || wf_core.translate('DELETE') || '"'); end loop; htp.tableclose; htp.br; htp.tableopen(calign=>'CENTER',cattributes=>'summary=""'); --Add new node Button htp.tableRowOpen; l_url := wfa_html.base_url||'/wf_queue.generic_queue_edit'; l_icon := 'FNDJLFOK.gif'; l_text := wf_core.translate ('WFQUEUE_CREATE'); l_onmouseover := wf_core.translate ('WFQUEUE_CREATE'); htp.p(''); wf_pref.create_reg_button (l_url, l_onmouseover, l_media, l_icon, l_text); htp.p(''); htp.tableRowClose; htp.tableclose; wfa_sec.Footer; htp.htmlClose; exception when others then wf_core.context('FND_DOCUMENT_MANAGEMENT', 'Generic_Queue_Display'); raise; end Generic_Queue_Display; -- -- Generic_Queue_View_Detail -- Produce list of generic_queues -- -- MODIFICATION LOG: -- 06-JUN-2001 JWSMITH BUG 1819232 - added alt attrib for IMG tag for ADA -- - Added summary attribute for table tags for ADA -- procedure Generic_Queue_View_Detail ( p_protocol IN VARCHAR2 DEFAULT NULL, p_inbound_outbound IN VARCHAR2 DEFAULT NULL ) IS l_count number := 0; username varchar2(320); -- Username to query admin_role varchar2(320); -- Role for admin mode admin_mode varchar2(1) := 'N'; realname varchar2(360); -- Display name of username s0 varchar2(2000); -- Dummy l_error_msg varchar2(240); l_url varchar2(240); l_media varchar2(240) := wfa_html.image_loc; l_icon varchar2(40); l_text varchar2(240); l_onmouseover varchar2(240); l_sql varchar2(1000); begin -- Check current user has admin authority wfa_sec.GetSession(username); username := upper(username); wf_directory.GetRoleInfo(username, realname, s0, s0, s0, s0); admin_role := wf_core.translate('WF_ADMIN_ROLE'); if (admin_role = '*' or Wf_Directory.IsPerformer(username, admin_role)) then admin_mode := 'Y'; else l_error_msg := wf_core.translate('WFPREF_INVALID_ADMIN'); end if; -- Set page title htp.htmlOpen; htp.headOpen; htp.p(''); htp.title(wf_core.translate('WFGENERIC_QUEUE_TITLE')); wfa_html.create_help_function('wf/links/dmr.htm?DMREP'); htp.headClose; wfa_sec.Header(FALSE, '',wf_core.translate('WFGENERIC_QUEUE_TITLE'), FALSE); htp.br; IF (admin_mode = 'N') THEN htp.center(htf.bold(l_error_msg)); return; END IF; SELECT queue_count INTO l_count FROM wf_queues WHERE UPPER(p_protocol) = protocol AND p_inbound_outbound = inbound_outbound; -- Column headers htp.tableOpen(cattributes=>'border=1 cellpadding=3 bgcolor=white width="100%" summary=""'); htp.tableRowOpen(cattributes=>'bgcolor=#006699'); htp.tableHeader(cvalue=>''|| wf_core.translate('PROTOCOL')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('PROTOCOL') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('QUEUE_NUMBER')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('QUEUE_NUMBER') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('QUEUE_NAME')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('QUEUE_NAME') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('QUEUE_COUNT')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('QUEUE_COUNT') || '"'); htp.tableHeader(cvalue=>''|| wf_core.translate('VIEW_DETAIL')||'', calign=>'Center', cattributes=>'id="' || wf_core.translate('VIEW_DETAIL') || '"'); htp.tableRowClose; htp.tableRowOpen; htp.tableRowClose; -- Show all queues for the given protocol for ii in 1..l_count loop htp.tableRowOpen(null, 'TOP'); htp.tableData(p_protocol, 'left', cattributes=>'headers="' || wf_core.translate('PROTOCOL') || '"'); htp.tableData(to_char(ii), 'left', cattributes=>'headers="' || wf_core.translate('QUEUE_NUMBER') || '"'); -- p_protocol and p_inbound_outbound were verified above -- ii must be a number -- BINDVAR_SCAN_IGNORE htp.tableData(wf_core.translate('WF_SCHEMA')||'.'||'WF_'||p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_QUEUE', 'left', cattributes=>'headers="' || wf_core.translate('QUEUE_NAME') || '"'); /* ** Check to see if there are any messages in the specified queue */ l_sql := 'SELECT COUNT(1) FROM WF_'||p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_TABLE'; execute immediate l_sql INTO l_count; htp.tableData(to_char(l_count), 'left', cattributes=>'headers="' || wf_core.translate('QUEUE_COUNT') || '"'); htp.tableData(htf.anchor2(curl=>wfa_html.base_url|| '/wf_queue.generic_queue_display_contents?p_protocol='|| p_protocol||'&p_inbound_outbound='|| p_inbound_outbound||'&p_queue_number='|| to_char(ii)||'&p_message_number=1', ctext=>'' || wf_core.translate('FIND') || ''), 'center', cattributes=>'valign="MIDDLE" headers="' || wf_core.translate('VIEW_DETAIL') || '"'); end loop; htp.tableclose; htp.br; wfa_sec.Footer; htp.htmlClose; exception when others then wf_core.context('FND_DOCUMENT_MANAGEMENT', 'Generic_Queue_View_Detail'); raise; end Generic_Queue_View_Detail; -- MODIFICATION LOG: -- 06-JUN-2001 JWSMITH BUG 1819232 - Added summary attr for table tag for ADA -- - Added ID attr for TD tags for ADA procedure generic_queue_display_contents (p_protocol IN VARCHAR2 DEFAULT NULL, p_inbound_outbound IN VARCHAR2 DEFAULT NULL, p_queue_number IN NUMBER DEFAULT NULL, p_message_number IN NUMBER DEFAULT 1) IS username varchar2(320); -- Username to query admin_role varchar2(320); -- Role for admin mode admin_mode varchar2(1) := 'N'; l_media varchar2(240) := wfa_html.image_loc; l_icon varchar2(40) := 'FNDILOV.gif'; l_text varchar2(240) := ''; l_onmouseover varchar2(240) := wf_core.translate ('WFPREF_LOV'); l_url varchar2(4000); l_error_msg varchar2(240); l_more_data BOOLEAN := TRUE; dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; ii number := 0; l_loc number := 1; l_queue_name varchar2(30); l_msg_id RAW(16); begin /* wf_message_payload_t is obsolete from 2.6.4 onwards */ null; exception when others then Wf_Core.Context('Wf_Queue', 'generic_queue_display_contents', p_protocol, p_inbound_outbound); raise; end generic_queue_display_contents; -- MODIFICATION LOG: -- 06-JUN-2001 JWSMITH BUG 1819232 - Added summary attr for table tag for ADA -- - Added ID attr for TD tags procedure Generic_Queue_Edit ( p_protocol IN VARCHAR2 DEFAULT NULL, p_inbound_outbound IN VARCHAR2 DEFAULT NULL ) IS username varchar2(320); -- Username to query admin_role varchar2(320); -- Role for admin mode admin_mode varchar2(1) := 'N'; l_inbound_selected varchar2(1) := 'N'; l_outbound_selected varchar2(1) := 'N'; l_description VARCHAR2(240); l_queue_count NUMBER; l_media varchar2(240) := wfa_html.image_loc; l_icon varchar2(40) := 'FNDILOV.gif'; l_text varchar2(240) := ''; l_onmouseover varchar2(240) := wf_core.translate ('WFPREF_LOV'); l_url varchar2(4000); l_error_msg varchar2(240); BEGIN -- Check current user has admin authority wfa_sec.GetSession(username); username := upper(username); admin_role := wf_core.translate('WF_ADMIN_ROLE'); if (admin_role = '*' or Wf_Directory.IsPerformer(username, admin_role)) then admin_mode := 'Y'; else l_error_msg := wf_core.translate('WFPREF_INVALID_ADMIN'); end if; /* ** If this protocol already exists then go fetch the definition */ IF (p_protocol IS NOT NULL) THEN SELECT description, queue_count INTO l_description, l_queue_count FROM wf_queues WHERE protocol = p_protocol AND inbound_outbound = p_inbound_outbound; END IF; -- Set page title htp.htmlOpen; htp.headOpen; htp.title(wf_core.translate('WFQUEUE_EDIT_QUEUE_TITLE')); wfa_html.create_help_function('wf/links/dmr.htm?DMREP'); wf_lov.OpenLovWinHtml; htp.headClose; -- Page header wfa_sec.Header(FALSE, '', wf_core.translate('WFQUEUE_EDIT_QUEUE_TITLE'), TRUE); IF (admin_mode = 'N') THEN htp.center(htf.bold(l_error_msg)); return; END IF; htp.tableopen(calign=>'CENTER',cattributes=>'summary="' || wf_core.translate('WFQUEUE_EDIT_QUEUE_TITLE') || '"'); htp.p('
'); -- Protocol Name htp.tableRowOpen; htp.tableData(cvalue=>'', calign=>'right', cattributes=>'id=""'); htp.tableData(htf.formText(cname=>'p_protocol', csize=>'30', cvalue=>p_protocol, cmaxlength=>'30', cattributes=>'id="i_protocol"'), cattributes=>'id=""'); htp.tableRowClose; -- Inbound/outbound htp.tableRowOpen; htp.tableData(cvalue=>'', calign=>'right', cattributes=>'id=""'); if (NVL(p_inbound_outbound, 'OUTBOUND') = 'INBOUND') then l_inbound_selected := 'Y'; l_outbound_selected := NULL; else l_inbound_selected := NULL; l_outbound_selected := 'Y'; end if; htp.p(''); htp.formSelectOpen(cname=>'p_inbound_outbound',cattributes=>'id="i_inbound_outbound"'); htp.formSelectOption(cvalue=>wf_core.translate('INBOUND'), cattributes=>'value=INBOUND', cselected=>l_inbound_selected); htp.formSelectOption(cvalue=>wf_core.translate('OUTBOUND'), cattributes=>'value=OUTBOUND', cselected=>l_outbound_selected); htp.formSelectClose; htp.p(''); htp.tableRowClose; -- Description htp.tableRowOpen; htp.tableData(cvalue=>'