[Home] [Help]
PACKAGE BODY: APPS.ECX_INBOUND_LISTENER_QH
Source
1 package body ECX_INBOUND_LISTENER_QH as
2 -- $Header: ECXILQHB.pls 120.1.12000000.7 2007/06/06 09:55:55 susaha ship $
3 ------------------------------------------------------------------------------+
4
5 /**
6 Returns the correct event name based on the value of attributte4
7 **/
8 procedure get_event_name (p_attribute4 in varchar2,
9 p_event_name out nocopy varchar2)
10 is
11 l_position pls_integer;
12 l_temp varchar2(240);
13 begin
14 if (p_attribute4 is not null)
15 then
16 if (substr(p_attribute4, 1, 12) = 'oracle.apps.')
17 then
18 p_event_name := lower(p_attribute4);
19 else
20 -- starting from 1st position get the characters till the first underscore
21 l_position := instrb(p_attribute4, '_');
22 l_temp := substr(p_attribute4, 1, l_position - 1);
23
24 if (length(l_temp) <= 3)
25 then
26 p_event_name := 'oracle.apps.' || lower(l_temp) || '.inbound.message.receive';
27 else
28 p_event_name := 'oracle.apps.ecx.inbound.message.receive';
29 end if;
30 end if;
31 else
32 p_event_name := 'oracle.apps.ecx.inbound.message.receive';
33 end if;
34 exception
35 when others then
36 raise;
37 end get_event_name;
38
39
40 PROCEDURE Dequeue(p_agent_guid in RAW, p_event out nocopy WF_EVENT_T)
41 is
42 x_queue_name varchar2(80);
43 x_agent_name varchar2(30);
44 x_dequeue_options dbms_aq.dequeue_options_t;
45 x_message_properties dbms_aq.message_properties_t;
46 x_msgid RAW(16);
47 x_clob clob;
48 no_messages exception;
49 pragma exception_init (no_messages, -25228);
50 x_ecxmsg system.ecxmsg;
51 x_sys_guid RAW(16);
52 x_sys_name VARCHAR2(30);
53 x_from_agt wf_agent_t := wf_agent_t(null,null);
54 x_to_agt wf_agent_t := wf_agent_t(null,null);
55 i_message_counter pls_integer;
56 x_trigger_id number;
57 l_retcode pls_integer := 0;
58 l_retmsg varchar2(200) := null;
59 encrypt_password ecx_tp_details.password%type;
60 l_module varchar2(2000);
61 cursor c_ecx_trigger_id
62 is
63 select ecx_trigger_id_s.NEXTVAL
64 from dual;
65
66 begin
67 l_module := 'ecx.plsql.ecx_inbound_listener_qh.dequeue';
68 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
69 wf_log_pkg.string(wf_log_pkg.level_procedure, l_module || '.begin',
70 'in dequeue');
71 end if;
72
73 select upper(queue_name), upper(name), system_guid
74 into x_queue_name, x_agent_name, x_sys_guid -- 20
75 from wf_agents
76 where guid = p_agent_guid;
77
78 select upper(name)
79 into x_sys_name
80 from wf_systems
81 where guid = x_sys_guid;
82
83 x_from_agt := wf_agent_t(x_agent_name, x_sys_name);
84 x_to_agt := wf_agent_t(x_agent_name, x_sys_name);
85
86 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
87 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,'p_agent_guid' ||hextoraw(p_agent_guid));
88 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,'System: ' || x_sys_name);
89 end if;
90
91 x_dequeue_options.wait := 1;
92
93 savepoint before_dequeue;
94
95 dbms_aq.dequeue
96 (
97 queue_name => x_queue_name,
98 dequeue_options => x_dequeue_options,
99 message_properties => x_message_properties,
100 payload => x_ecxmsg,
101 msgid => x_msgid
102 );
103
104 --- bug#2016123. If the transaction subtype is null ,
105 --- default it to the transaction_type
106 if x_ecxmsg.transaction_subtype is null
107 then
108 x_ecxmsg.transaction_subtype := x_ecxmsg.transaction_type;
109 end if;
110
111 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
112 wf_log_pkg.string(wf_log_pkg.level_statement, l_module, 'after dequeue');
113 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
114 'Message Id: ' || x_msgid);
115 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
116 'Message Type: ' || x_ecxmsg.message_type);
117 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
118 'Message Standard: ' || x_ecxmsg.message_standard);
119 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
120 'transaction type: ' || x_ecxmsg.transaction_type);
121 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
122 'transaction subtype: '|| x_ecxmsg.transaction_subtype);
123 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
124 ' Defaulted transaction subtype: '|| x_ecxmsg.transaction_subtype);
125 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
126 'Party Site Id: '|| x_ecxmsg.party_site_id);
127 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
128 'From: '|| x_ecxmsg.attribute1);
129 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
130 'Final Destination: '|| x_ecxmsg.attribute3);
131 end if;
132
133 x_from_agt := wf_agent_t(substr(x_ecxmsg.party_site_id,1,30),
134 'EXTERNAL_PARTY_SITEID');
135
136 wf_event_t.initialize(p_event);
137
138 -- get the correct event name
139 get_event_name(x_ecxmsg.attribute4, p_event.event_name);
140 p_event.priority := 1;
141 p_event.event_key := x_msgid;
142 p_event.from_agent := x_from_agt ;
143 p_event.to_agent := x_to_agt ;
144 p_event.error_subscription := null;
145 p_event.error_message := null;
146 p_event.error_stack := null;
147
148 select ecx_inlstn_s.nextval into i_message_counter from dual ;
149 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
150 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
151 'setting message props');
152 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
153 'msg_counter: ' || i_message_counter);
154 end if;
155
156 --- Encrypt the password
157 ecx_obfuscate.ecx_data_encrypt(
158 l_input_string => x_ecxmsg.password,
159 l_output_string => encrypt_password,
160 errmsg => l_retmsg,
161 retcode => l_retcode);
162
163 p_event.AddParameterToList('ECX_MSGID', x_msgid);
164 p_event.AddParameterToList('ECX_TRANSACTION_TYPE', x_ecxmsg.transaction_type);
165 p_event.AddParameterToList('ECX_TRANSACTION_SUBTYPE', x_ecxmsg.transaction_subtype);
166 p_event.AddParameterToList('ECX_MESSAGE_STANDARD', x_ecxmsg.message_standard);
167 p_event.AddParameterToList('ECX_MESSAGE_TYPE', x_ecxmsg.message_type);
168 p_event.AddParameterToList('ECX_DOCUMENT_NUMBER', x_ecxmsg.document_number);
169 p_event.AddParameterToList('ECX_PARTY_ID', x_ecxmsg.partyid);
170 p_event.AddParameterToList('ECX_PARTY_SITE_ID', x_ecxmsg.party_site_id);
171 p_event.AddParameterToList('ECX_PARTY_TYPE', x_ecxmsg.party_type);
172 p_event.AddParameterToList('ECX_PROTOCOL_TYPE', x_ecxmsg.protocol_type);
173 p_event.AddParameterToList('ECX_PROTOCOL_ADDRESS', x_ecxmsg.protocol_ADDRESS);
174 p_event.AddParameterToList('ECX_USERNAME', x_ecxmsg.username);
175 p_event.AddParameterToList('ECX_PASSWORD', encrypt_password);
176 p_event.AddParameterToList('ECX_ATTRIBUTE1', x_ecxmsg.attribute1);
177 p_event.AddParameterToList('ECX_ATTRIBUTE2', nvl(to_number(x_ecxmsg.attribute2),0));
178 p_event.AddParameterToList('ECX_ATTRIBUTE3', x_ecxmsg.attribute3);
179 p_event.AddParameterToList('ECX_ATTRIBUTE4', x_ecxmsg.attribute4);
180 p_event.AddParameterToList('ECX_ATTRIBUTE5', x_ecxmsg.attribute5);
181 p_event.AddParameterToList('ECX_ICN', i_message_counter);
182 open c_ecx_trigger_id;
183 fetch c_ecx_trigger_id into x_trigger_id;
184 close c_ecx_trigger_id;
185
186 p_event.AddParameterToList('ECX_TRIGGER_ID', x_trigger_id);
187
188 -- KH How do we pass this parameter dynamically through the Interface
189 --p_event.AddParameterToList('ECX_DEBUG_LEVEL', 0);
190 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
191 wf_log_pkg.string(wf_log_pkg.level_statement, l_module, 'Setting payload');
192 end if;
193 p_event.setEventData(x_ecxmsg.payload);
194
195 exception
196 when no_messages then
197 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
198 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
199 'No more messages in dequeue.');
200 end if;
201 p_event := NULL;
202 return;
203
204 when queue_handler_exit then
205 rollback to before_dequeue;
206 if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
207 wf_log_pkg.string(wf_log_pkg.level_error, l_module,
208 ecx_debug.getMessage(ecx_utils.i_errbuf, ecx_utils.i_errparams));
209 wf_log_pkg.string(wf_log_pkg.level_error, l_module,
210 'Exception in dequeue ' || x_queue_name);
211 end if;
212 Wf_Core.Context('ECX_INBOUND_LISTENER_QH', 'Dequeue', x_queue_name);
213 raise;
214
215 when others then
216 rollback to before_dequeue;
217 if (wf_log_pkg.level_unexpected >= fnd_log.g_current_runtime_level) then
218 wf_log_pkg.string(wf_log_pkg.level_unexpected, l_module,
219 substr(sqlerrm, 1,2000));
220 wf_log_pkg.string(wf_log_pkg.level_unexpected, l_module,
221 'Exception in dequeue '|| x_queue_name);
222 end if;
223 Wf_Core.Context('ECX_INBOUND_LISTENER_QH', 'Dequeue', x_queue_name);
224 raise;
225 end Dequeue;
226
227
228 PROCEDURE Enqueue(p_event in wf_event_t,
229 p_out_agent_override in wf_agent_t )
230 is
231
232 x_out_queue varchar2(80);
233 x_enqueue_options dbms_aq.enqueue_options_t;
234 x_message_properties dbms_aq.message_properties_t;
235 x_msgid RAW(16);
236 x_ecxmsg SYSTEM.ecxmsg ;
237 l_module varchar2(2000);
238
239 begin
240 l_module := 'ecx.plsql.ecx_inbound_listener_qh.enqueue';
241 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
242 wf_log_pkg.string(wf_log_pkg.level_procedure, l_module || '.begin',
243 'in enqueue');
244 end if;
245
246 x_out_queue := 'APPLSYS.ECX_INBOUND';
247
248 x_ecxmsg := SYSTEM.ecxmsg(null,null,null,null, null,null,null,null,
249 null,null,null,null, null,null,
250 null,null,null,null);
251
252 x_ecxmsg.message_type := p_event.getValueForParameter('ECX_MESSAGE_TYPE');
253 x_ecxmsg.message_standard := p_event.getValueForParameter('ECX_MESSAGE_STANDARD');
254 x_ecxmsg.transaction_type := p_event.getValueForParameter('ECX_TRANSACTION_TYPE');
255 x_ecxmsg.transaction_subtype := p_event.getValueForParameter('ECX_TRANSACTION_SUBTYPE');
256 x_ecxmsg.document_number := p_event.getValueForParameter('ECX_DOCUMENT_NUMBER');
257 x_ecxmsg.partyid := p_event.getValueForParameter('ECX_PARTY_ID');
258 x_ecxmsg.party_site_id := p_event.getValueForParameter('ECX_PARTY_SITE_ID');
259 x_ecxmsg.protocol_type := p_event.getValueForParameter('ECX_PROTOCOL_TYPE');
260 x_ecxmsg.protocol_address := p_event.getValueForParameter('ECX_PROTOCOL_ADDRESS');
261 x_ecxmsg.username := p_event.getValueForParameter('ECX_USERNAME');
262 x_ecxmsg.password := p_event.getValueForParameter('ECX_PASSWORD');
263 x_ecxmsg.attribute1 := p_event.getValueForParameter('ECX_ATTRIBUTE1');
264 x_ecxmsg.attribute2 := p_event.getValueForParameter('ECX_ATTRIBUTE2');
265 x_ecxmsg.attribute3 := p_event.getValueForParameter('ECX_ATTRIBUTE3');
266 x_ecxmsg.attribute4 := p_event.getValueForParameter('ECX_ATTRIBUTE4');
267 x_ecxmsg.attribute5 := p_event.getValueForParameter('ECX_ATTRIBUTE5');
268 x_ecxmsg.payload := p_event.getEventData();
269
270 x_message_properties.priority := p_event.GetPriority();
271
272
273 DBMS_AQ.ENQUEUE
274 (
275 queue_name => x_out_queue,
276 enqueue_options => x_enqueue_options,
277 message_properties => x_message_properties,
278 payload => x_ecxmsg,
279 msgid => x_msgid
280 );
281
282 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
283 wf_log_pkg.string(wf_log_pkg.level_procedure, l_module || '.end',
284 'finished calling dbms_aq.enqueue');
285 end if;
286
287 exception
288 when others then
289 Wf_Core.Context('ECX_INBOUND_LISTENER_QH', 'Enqueue', x_out_queue);
290 raise;
291 end Enqueue;
292
293
294 end ECX_INBOUND_LISTENER_QH;