REM dbdrv: none /*=======================================================================+ | Copyright (c) 2000 Oracle Corporation Redwood Shores, California, USA| | All rights reserved. | +=======================================================================+ | FILENAME | wfquhndb.pls | DESCRIPTION | PL/SQL body for package: WF_EVENT_OMB_QH *=======================================================================*/ set verify off; whenever sqlerror exit failure rollback; whenever oserror exit failure rollback; /* ** FUNCTION SetOMBAQProp ** FUNCTION Serialize ** FUNCTION Unserialize ** PROCEDURE Dequeue ** PROCEDURE Enqueue ** FUNCTION xgetValueForParameter ** FUNCTION xgetNameForParameter */ create or replace package body WF_EVENT_OMB_QH as /* $Header: wfquhndob.pls 26.16 2003/09/10 13:34:43 vshanmug ship $ */ ------------------------------------------------------------------------------ FUNCTION SetOMBAQProp(id in varchar2, str in varchar2) return aq.ombaq_property is retProp aq.ombaq_property := aq.ombaq_property(null, null, 0, null); OMBAQ_STRING number default 8; tmpid varchar2(100); tmpstr varchar2(2000); begin if (length(id) > 100) then if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.SetOMBAQProp.id_length', 'id too long, chopped character(s) over 100th'); end if; tmpid := substr(id,1,100); else tmpid := id; end if; if (length(str) > 2000) then if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT.OMB_QH.SetOMBAQProp.str_length', 'str too long, chopped character(s) over 2000th'); end if; tmpstr := substr(str,1,2000); else tmpstr := str; end if; --wf_log_pkg.string(6, 'OMB_QH SetOMBAQProp','id:'||tmpid); --wf_log_pkg.string(6, 'OMB_QH SetOMBAQProp','str:'||tmpstr); retProp.name := tmpid; retProp.str_value := tmpstr; retProp.ombaq_type := OMBAQ_STRING; return(retProp); exception when others then Wf_Core.Context('Wf_Event_OMB_QH', 'SetOMBAQProp'); raise; return(null); end SetOMBAQProp; ------------------------------------------------------------------------------ FUNCTION Serialize(p_event in wf_event_t) return aq.ombaq_properties is tempstr varchar2(4000); prop aq.ombaq_property := aq.ombaq_property(null, null, 0, null); retprop aq.ombaq_properties := aq.ombaq_properties(prop,prop,prop,prop, prop,prop,prop,prop, prop,prop,prop,prop,prop, prop,prop); a_name varchar2(30); a_system varchar2(30); a_toname varchar2(30); a_tosystem varchar2(30); e_priority number; e_corr_id varchar2(240); e_send_date date; e_receive_date date; e_event_name varchar2(240); e_event_key varchar2(240); e_error_sub varchar2(50); e_error_msg varchar2(4000); e_error_stk varchar2(4000); ctr number default 1; i number; OMBAQ_STRING number default 8; begin -- Get attribute type info a_name := p_event.GetFromAgent().GetName(); a_system := p_event.GetFromAgent().GetSystem(); a_toname := p_event.GetToAgent().GetName(); a_tosystem := p_event.GetToAgent().GetSystem(); e_priority := p_event.getPriority(); e_send_date := p_event.getSendDate(); e_receive_date := p_event.getReceiveDate(); e_corr_id := p_event.getCorrelationID(); e_event_name := p_event.getEventName(); e_event_key := p_event.getEventKey(); e_error_sub := p_event.getErrorSubscription(); e_error_msg := p_event.getErrorMessage(); e_error_stk := p_event.getErrorStack(); retprop(1) := SetOMBAQProp('FROMAGENTNAME',a_name); retprop(2) := SetOMBAQProp('FROMAGENTSYSTEM',a_system); retprop(3) := SetOMBAQProp('TOAGENTNAME',a_toname); retprop(4) := SetOMBAQProp('TOAGENTSYSTEM',a_tosystem); retprop(5) := SetOMBAQProp('PRIORITY',to_char(e_priority)); retprop(6) := SetOMBAQProp('SENDDATE',to_char(e_send_date, 'DD-MON-YYYY HH24:MI:SS')); retprop(7) := SetOMBAQProp('RECEIVEDATE',to_char(e_receive_date,'DD-MON-YYYY HH24:MI:SS')); retprop(8) := SetOMBAQProp('CORRELATIONID',e_corr_id); retprop(9) := SetOMBAQProp('EVENTNAME',e_event_name); retprop(10) := SetOMBAQProp('EVENTKEY',e_event_key); retprop(11) := SetOMBAQProp('ERRORSUBSCRIPTION',e_error_sub); if (length(e_error_msg) < 2000) then retprop(12) := SetOMBAQProp('ERRORMESSAGE1',e_error_msg); retprop(13) := SetOMBAQProp('ERRORMESSAGE2',null); else retprop(12) := SetOMBAQProp('ERRORMESSAGE1',substr(1,2000,e_error_msg)); retprop(13) := SetOMBAQProp('ERRORMESSAGE2',substr(2001,4000,e_error_msg)); end if; if (length(e_error_stk) < 2000) then retprop(14) := SetOMBAQProp('ERRORSTACK1',e_error_stk); retprop(15) := SetOMBAQProp('ERRORSTACK2',null); else retprop(14) := SetOMBAQProp('ERRORSTACK1',substr(1,2000,e_error_stk)); retprop(15) := SetOMBAQProp('ERRORSTACK2',substr(2001,4000,e_error_stk)); end if; ctr := 16; if (p_event.parameter_list is not null) then for i in 1..p_event.parameter_list.COUNT loop retprop.EXTEND; retprop(ctr) := SetOMBAQProp(p_event.parameter_list(i).name,p_event.parameter_list(i).value); ctr := ctr + 1; end loop; end if; if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT.OMB_QH.Serialize.End', 'Finished transformation'); end if; return(retprop); exception when others then Wf_Core.Context('Wf_Event_OMB_QH', 'Serialize'); raise; return(null); end Serialize; ------------------------------------------------------------------------------ FUNCTION Unserialize(inProp in aq.ombaq_properties) return wf_event_t is p_event wf_event_t; p_agent wf_agent_t := wf_agent_t(null,null); p_toagent wf_agent_t := wf_agent_t(null,null); event_str varchar2(2000); a_name varchar2(30); a_system varchar2(30); a_toname varchar2(30); a_tosystem varchar2(30); e_priority number; e_corr_id varchar2(240); e_send_date date; e_receive_date date; e_event_name varchar2(240); e_event_key varchar2(240); e_error_sub varchar2(50); e_error_msg varchar2(4000); e_error_stk varchar2(4000); p_name varchar2(30); p_str varchar2(2000); p_list wf_parameter_list_t := null; tmp_lst wf_parameter_t := wf_parameter_t(null,null); ctr number; pos number; i number; strLen number; begin wf_event_t.initialize(p_event); pos := 1; -- -- It has been a saga, but this should be the way we deal with the -- OMBAQ properties: we start at the beginning of the array, if the -- name value pair is one of our reserved words, we set it into the -- the main event adt, else it goes into the parameter list -- while (pos <= inProp.Count) loop p_name := inProp(pos).Name; -- Bug 1620991 OMB sets null values to NO_VALUE_STRING -- we will reset to null p_str := inProp(pos).Str_Value; if p_str = 'NO_VALUE_STRING' then p_str := null; end if; if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.prop', 'OMB Prop: '||pos||' Name: '||p_name); end if; if (p_name = 'FROMAGENTNAME' ) then a_name := p_str; elsif (p_name = 'FROMAGENTSYSTEM') then a_system := p_str; elsif (p_Name = 'TOAGENTNAME') then a_toname := p_str; elsif (p_Name = 'TOAGENTSYSTEM') then a_tosystem := p_str; elsif (p_Name = 'PRIORITY') then e_priority := p_str; elsif (p_Name = 'SENDDATE') then e_send_date := to_date(p_str,'DD-MON-YYYY HH24:MI:SS'); elsif (p_Name = 'RECEIVEDATE') then e_receive_date := to_date(p_str,'DD-MON-YYYY HH24:MI:SS'); elsif (p_Name = 'CORRELATIONID') then e_corr_id := p_str; elsif (p_Name = 'EVENTNAME') then e_event_name := p_str; elsif (p_Name = 'EVENTKEY') then e_event_key := p_str; elsif (p_Name = 'ERRORSUBSCRIPTION') then e_error_sub := p_str; elsif (p_Name = 'ERRORMESSAGE1') then if e_error_msg is null then e_error_msg := p_str; else e_error_msg := p_str||e_error_msg; end if; elsif (p_Name = 'ERRORMESSAGE2') then if e_error_msg is null then e_error_msg := p_str; else e_error_msg := e_error_msg||p_str; end if; elsif (p_Name = 'ERRORSTACK1') then if e_error_stk is null then e_error_stk := p_str; else e_error_stk := p_str||e_error_stk; end if; elsif (p_Name = 'ERRORSTACK2') then if e_error_stk is null then e_error_stk := p_str; else e_error_stk := e_error_stk||p_str; end if; else -- Must be a custom parameter p_event.AddParameterToList(p_Name, p_str); end if; pos := pos + 1; end loop; p_agent.setName(a_name); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.agent', 'Set Agent Name: '||a_name); end if; p_agent.setSystem(a_system); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.system', 'Set Syetem name: '||a_system); end if; p_toagent.setName(a_toname); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.to_agent_name', 'Set Agent To Name: '||a_toname); end if; p_toagent.setSystem(a_tosystem); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.to_system_name', 'Set Agent To System: '||a_tosystem); end if; p_event.setFromAgent(p_agent); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.from_agent', 'Set From Agent'); end if; p_event.setToAgent(p_toagent); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.to_agent', 'Set To Agent'); end if; p_event.setPriority(e_priority); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.priority', 'Set Priority: '||e_priority); end if; p_event.setSendDate(e_send_date); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.send_date', 'Set Send date: '||to_char(e_send_date,'DD-MON-YYYY HH24:MI:SS')); end if; p_event.setReceiveDate(e_receive_date); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.receive_date', 'Set Receive date: '||to_char(e_receive_date,'DD-MON-YYYY HH24:MI:SS')); end if; p_event.setCorrelationID(e_corr_id); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.corr_id', 'Set Corr ID: '||e_corr_id); end if; p_event.setEventName(e_event_name); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.event_name', 'Set Event Name: '||e_event_name); end if; p_event.setEventKey(e_event_key); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.event_key', 'Set Event Name: '||e_event_key); end if; p_event.setErrorSubscription(e_error_sub); p_event.setErrorMessage(e_error_msg); p_event.setErrorStack(e_error_stk); if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Unserialize.End', 'Finished setting p_event'); end if; return(p_event); exception when others then Wf_Core.Context('Wf_Event_OMB_QH', 'Unserialize'); raise; return(null); end Unserialize; ------------------------------------------------------------------------------ FUNCTION xgetValueForParameter(inProp in aq.ombaq_properties, pName in varchar2) return varchar2 is pos number := 1; begin if (inProp is null) then return NULL; end if; while (pos <= inProp.Count) loop if (inProp(pos).Name = pName) then return inProp(pos).Str_Value; elsif (substr(inProp(pos).Name,1,9) = 'PARAMETER' and substr(inProp(pos).Name,1,14) = pName) then return inProp(pos).Str_Value; end if; pos := pos + 1; end loop; return NULL; end xgetValueForParameter; ------------------------------------------------------------------------------ FUNCTION xgetNameForParameter(inProp in aq.ombaq_properties, pName in varchar2) return varchar2 is pos number := 1; begin if (inProp is null) then return NULL; end if; while (pos <= inProp.Count) loop if (substr(inProp(pos).Name,1,length(pName)) = pName) then return substr(inProp(pos).Name,length(pName)+2); end if; pos := pos + 1; end loop; return NULL; end xgetNameForParameter; ------------------------------------------------------------------------------ PROCEDURE Dequeue(p_agent_guid in raw, p_event out nocopy wf_event_t) is x_queue_name varchar2(80); x_agent_name varchar2(30); x_dequeue_options dbms_aq.dequeue_options_t; x_message_properties dbms_aq.message_properties_t; x_msgid RAW(16); x_clob clob; no_messages exception; pragma exception_init (no_messages, -25228); x_text_msg aq.ombaq_text_msg; x_sys_guid RAW(16); x_sys_name VARCHAR2(30); x_to_agt wf_agent_t := wf_agent_t(null,null); x_ombaq_message_properties_t aq.ombaq_message_properties_t; begin select upper(queue_name), upper(name), system_guid into x_queue_name, x_agent_name, x_sys_guid from wf_agents where guid = p_agent_guid; select upper(name) into x_sys_name from wf_systems where guid = x_sys_guid; if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_procedure, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.Begin', 'Dequeuing '||x_queue_name||' on '||x_agent_name ||' in '|| x_sys_name); end if; --x_dequeue_options.consumer_name := 'NULL'; --x_dequeue_options.wait := DBMS_AQ.NO_WAIT; x_dequeue_options.wait := 1; BEGIN if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.dbms_aq', 'Calling dbms_aq.dequeue'); end if; /** Bug 1620991 The OMB Dequeue API will differ depeinding on size of payload. Instead, we will go around these APIs, less code, better performance. aq.ombaqpublic.dequeue_clob(queue_name => x_queue_name, queue_type => 'OMBAQ_TEXT_MSG', dequeue_options => x_dequeue_options, message_properties => x_message_properties, ombaq_message_properties => x_ombaq_message_properties_t, payload => x_clob, msgid => x_msgid); **/ dbms_aq.dequeue(queue_name => x_queue_name, dequeue_options => x_dequeue_options, message_properties => x_message_properties, payload => x_text_msg, msgid => x_msgid); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.done', 'Finished calling dbms_aq.dequeue'); end if; --p_event := Unserialize(x_ombaq_message_properties_t.properties); p_event := Unserialize(x_text_msg.header.properties); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.unserialize', 'Finished Unserialize'); end if; if x_text_msg.text_vc is null then if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT.OMB_QH.Dequeue.no_data', 'No Data in TEXT_VC'); end if; p_event.setEventData(x_text_msg.text_lob); else if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.data', 'Data in TEXT_VC'); end if; dbms_lob.createtemporary(x_clob, FALSE, DBMS_LOB.CALL); dbms_lob.write(x_clob, length(x_text_msg.text_vc),1, x_text_msg.text_vc); p_event.setEventData(x_clob); end if; if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.event_data', 'Finished setEventData'); end if; x_to_agt.setName(x_agent_name); x_to_agt.setSystem(x_sys_name); if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.to_agent', 'Finished create ToAgent'); end if; p_event.setToAgent(x_to_agt); if p_event.getEventName() is null and p_event.GetValueForParameter('MESSAGE_TYPE') = 'XML' then p_event.SetEventName('oracle.apps.ecx.outbound.message.send'); end if; if instr(upper(p_event.GetValueForParameter('PROTOCOL_TYPE')),'HTTPS',1,1) > 0 then p_event.AddParameterToList('PROTOCOL_TYPE','HTTPS-OXTA'); --elsif instr(upper(p_event.GetValueForParameter('PROTOCOL_TYPE')),'HTTP',1,1) --> 0 then else p_event.AddParameterToList('PROTOCOL_TYPE','HTTP-OXTA'); end if; if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.from_agent_name', 'from_agent.name: '||p_event.GetFromAgent().GetName()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.from_agent_sys', 'from_agent.system: '||p_event.GetFromAgent().GetSystem()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.to_agent_name', 'to_agent.name: '||p_event.GetToAgent().GetName()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.to_agent_sys', 'to_agent.system: '||p_event.GetToAgent().GetSystem()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.priority', 'priority: '||p_event.getPriority()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.send_date', 'send date: '||p_event.getSendDate()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.receive_date', 'receive date: '||p_event.getReceiveDate()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.corr', 'correlation id: '||p_event.getCorrelationID()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Dequeue.event_name', 'event name: '||p_event.getEventName()); end if; 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_EVENT_OMB_QH.Dequeue.queue_empty', 'No more messages in dequeue.'); end if; p_event := NULL; return; END; exception when others then Wf_Core.Context('Wf_Event_OMB_QH', 'Dequeue', x_queue_name); raise; end Dequeue; ------------------------------------------------------------------------------ PROCEDURE Enqueue(p_event in wf_event_t, p_out_agent_override in wf_agent_t default null) is x_out_agent_name varchar2(30); x_out_system_name varchar2(30); x_to_agent_name varchar2(30) := p_event.GetToAgent().GetName(); x_to_system_name varchar2(30) := p_event.GetToAgent().GetSystem(); x_out_queue varchar2(80); x_to_queue varchar2(80); x_enqueue_options dbms_aq.enqueue_options_t; x_message_properties dbms_aq.message_properties_t; x_omb_prop aq.ombaq_properties; x_msgid RAW(16); x_name varchar2(30); x_address varchar2(1024); x_protocol varchar2(30); x_protocol_num number := 0; x_text_msg aq.ombaq_text_msg; x_header aq.ombaq_header; x_ombaq_message_properties_t aq.ombaq_message_properties_t; begin if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.from_agent_name', 'from_agent.name: '||p_event.GetFromAgent().GetName()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.from_agent_sys', 'from_agent.system: '||p_event.GetFromAgent().GetSystem()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.to_agent_name', 'to_agent.name: '||p_event.GetToAgent().GetName()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.to_agent_sys', 'to_agent.system: '||p_event.GetToAgent().GetSystem()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.priority', 'priority: '||p_event.getPriority()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.send_date', 'send date: '||p_event.getSendDate()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.receive_date', 'receive date: '||p_event.getReceiveDate()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.corr', 'correlation id: '||p_event.getCorrelationID()); wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.event_name', 'event name: '||p_event.getEventName()); end if; -- Determine the out queue -- if (p_out_agent_override is not null) then x_out_agent_name := p_out_agent_override.GetName(); x_out_system_name := p_out_agent_override.GetSystem(); else x_out_agent_name := p_event.GetFromAgent().GetName(); x_out_system_name := p_event.GetFromAgent().GetSystem(); end if; select agt.queue_name into x_out_queue from wf_agents agt, wf_systems sys where agt.name = x_out_agent_name and sys.name = x_out_system_name and sys.guid = agt.system_guid; if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.Begin', 'Enqueuing on Queue: '||x_out_queue); end if; /* Someday, the recipient agent may be a group of agents ** and we'll need to explode that group out and walk it. ** For now, it's a singleton agent */ /* jvc KevinH suggests breaking this out to make it easier in ** the future to swap in different lookups (like LDAP) */ select agt.name, agt.address, agt.protocol, agt.queue_name into x_name, x_address, x_protocol, x_to_queue from wf_agents agt, wf_systems sys where agt.name = x_to_agent_name and sys.name = x_to_system_name and sys.guid = agt.system_guid; /* ** mjc We need to make sure the recipient address is in the correct ** format otherwise dequeue will not work. ** Rule 1: Local Consumer dequeues from same queue as enqueued ** --> Address must be null ** Rule 2: Propagating to Local Queue ** --> Address must be . ** Rule 3: Propagating to Local Database ** --> Address must be .@dblink */ if (x_to_agent_name = x_out_agent_name) and (x_to_system_name = x_out_system_name) then x_address := null; elsif (x_to_agent_name <> x_out_agent_name) and (x_to_system_name = x_out_system_name) then x_address := x_to_queue; else null; -- the default end if; if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.details', 'Recipient is name: '||x_name|| ' address: '||x_address||' protocol: '||x_protocol); end if; /* ** Here is where we will add additional protocol mappings as AQ ** supports new protocols. This bit will form a hardcoded mapping ** from protocol names as used in the event manager and the ** protocol numbers used by AQ. */ if((x_protocol is NULL) or (x_protocol not in ('SQLNET'))) then Wf_Core.Context('Wf_Event_OMB_QH', 'Enqueue', 'Bad Protocol', x_protocol, x_out_queue); end if; x_protocol_num := 0; /** As OMB will be a single consumer queue, no recipient list x_message_properties.recipient_list(1) := sys.aq$_agent(x_name, x_address, x_protocol_num); **/ x_omb_prop := Serialize(p_event); x_ombaq_message_properties_t := aq.ombaq_message_properties_t(null,null, x_omb_prop); /* ** Set the Priority */ x_message_properties.priority := p_event.GetPriority(); --x_header := aq.ombaq_header(null, 0, null, x_omb_prop); --x_text_msg := aq.ombaq_text_msg(x_header, 0, null, p_event.getEventData()); -- Enqueue the message if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_statement, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.aq.ombaqpublic', 'Calling aq.ombaqpublic.enqueue_clob'); end if; /** Commented out because we have to use the OMB AQ APIs dbms_aq.enqueue(queue_name => x_out_queue, enqueue_options => x_enqueue_options, message_properties => x_message_properties, payload => x_text_msg, msgid => x_msgid); **/ aq.ombaqpublic.enqueue_clob(queue_name => x_out_queue, queue_type => 'OMBAQ_TEXT_MSG', enqueue_options => x_enqueue_options, message_properties => x_message_properties, ombaq_message_properties => x_ombaq_message_properties_t, payload => p_event.getEventData(), msgid => x_msgid); if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then wf_log_pkg.string(wf_log_pkg.level_procedure, 'wf.plsql.WF_EVENT_OMB_QH.Enqueue.End', 'Finished calling enqueue'); end if; exception when others then Wf_Core.Context('Wf_Event_OMB_QH', 'Enqueue', x_out_queue); raise; end Enqueue; ------------------------------------------------------------------------------ end WF_EVENT_OMB_QH; / -- show errors; commit; exit;