[Home] [Help]
PACKAGE BODY: APPS.ECX_ERRORLOG
Source
1 package body ecx_errorlog as
2 -- $Header: ECXERRB.pls 120.4 2006/07/21 16:27:08 gsingh ship $
3
4 cursor c_ecx_errorno
5 is
6 select ecx_error_no_s.nextval
7 from dual;
8 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
9 l_statement PLS_INTEGER := ecx_debug.g_statement;
10 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
11 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
12 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
13 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
14
15 procedure outbound_engine
16 (
17 i_trigger_id IN number,
18 i_status IN varchar2,
19 i_errmsg IN varchar2,
20 i_outmsgid IN RAW,
21 i_errparams IN varchar2 ,
22 i_party_type IN varchar2
23 )
24 is
25 i_error_no pls_integer;
26 PRAGMA AUTONOMOUS_TRANSACTION;
27 begin
28 if i_trigger_id is null
29 then
30 return;
31 end if;
32
33 if i_errmsg is not null
34 then
35 open c_ecx_errorno;
36 fetch c_ecx_errorno into i_error_no;
37 close c_ecx_errorno;
38 end if;
39 update ecx_outbound_logs
40 set status=i_status,
41 out_msgid = i_outmsgid,
42 error_id = i_error_no,
43 logfile = ecx_utils.g_logfile,
44 time_stamp = sysdate,
45 party_type = decode(i_party_type,null,party_type,i_party_type)
46 where trigger_id=i_trigger_id;
47
48 if i_errmsg is not null
49 then
50 insert into ecx_msg_logs
51 (
52 log_id,
53 trigger_id,
54 error_id
55 )
56 values
57 (
58 ecx_msg_logs_s.NEXTVAL,
59 i_trigger_id,
60 i_error_no
61 );
62
63 insert into ecx_error_msgs
64 (
65 error_id,
66 message,
67 message_parameters
68 )
69 values (
70 i_error_no,
71 i_errmsg,
72 nvl(i_errparams, ecx_utils.i_errparams)
73 );
74 ecx_utils.i_curr_errid := i_error_no;
75
76 end if;
77
78 /**
79 Commit for the Autonomous transaction.
80 **/
81 commit;
82 exception
83 when others then
84 rollback;
85 raise;
86 end outbound_engine;
87
88 procedure inbound_engine
89 (
90 i_process_id IN RAW,
91 i_status IN varchar2,
92 i_errmsg IN varchar2,
93 i_errparams IN varchar2
94 )
95 is
96 i_error_no pls_integer;
97 i_trigger_id number;
98
99 cursor get_curr_trigger_id(p_process_id raw)
100 is
101 select trigger_id
102 from ecx_inbound_logs
103 where process_id = p_process_id;
104
105 PRAGMA AUTONOMOUS_TRANSACTION;
106 begin
107 if i_process_id is null
108 then
109 return;
110 end if;
111
112 if i_errmsg is not null
113 then
114 open c_ecx_errorno;
115 fetch c_ecx_errorno into i_error_no;
116 close c_ecx_errorno;
117 end if;
118
119 update ecx_inbound_logs
120 set status = i_status,
121 error_id = i_error_no,
122 logfile = ecx_utils.g_logfile,
123 time_stamp = sysdate
124 where process_id = i_process_id;
125
126 for c_curr_trigger_id in get_curr_trigger_id(i_process_id)
127 loop
128 i_trigger_id := c_curr_trigger_id.trigger_id;
129 end loop;
130
131 if i_errmsg is not null
132 then
133 insert into ecx_msg_logs
134 (
135 log_id,
136 trigger_id,
137 error_id
138 )
139 values
140 (
141 ecx_msg_logs_s.NEXTVAL,
142 i_trigger_id,
143 i_error_no
144 );
145
146 insert into ecx_error_msgs
147 (
148 error_id,
149 message,
150 message_parameters
151 )
152 values (
153 i_error_no,
154 i_errmsg,
155 nvl(i_errparams, ecx_utils.i_errparams)
156 );
157 ecx_utils.i_curr_errid := i_error_no;
158 end if;
159 /**
160 Commit for the Autonomous transaction.
161 **/
162 commit;
163
164 exception
165 when others then
166 rollback;
167 raise;
168 end inbound_engine;
169
170 procedure external_system
171 (
172 i_outmsgid IN RAW,
173 i_status IN pls_integer,
174 i_errmsg IN varchar2,
175 i_timestamp IN date,
176 o_ret_code OUT NOCOPY pls_integer,
177 o_ret_msg OUT NOCOPY varchar2,
178 i_errparams IN varchar2
179 )
180 is
181 i_error_no pls_integer;
182 i_trigger_id number;
183
184 i_params wf_parameter_list_t;
185 i_event_name varchar2(240);
186 i_event_key varchar2(240);
187 i_item_type varchar2(8);
188 i_item_key varchar2(240);
189 i_transaction_type varchar2(100);
190 i_transaction_subtype varchar2(100);
191 i_party_type varchar2(30);
192 i_party_id varchar2(100);
193 i_party_site_id varchar2(100);
194 i_msgid raw(16);
195 i_message_type varchar2(100);
196 i_message_standard varchar2(100);
197 i_document_number varchar2(256);
198 i_protocol_type varchar2(500);
199 i_protocol_address varchar2(2000);
200 i_username varchar2(500);
201 i_attribute1 varchar2(500);
202 i_attribute2 varchar2(500);
203 i_attribute3 varchar2(500);
204 i_attribute4 varchar2(500);
205 i_attribute5 varchar2(500);
206 i_block_mode varchar2(1);
207 i_activity_id number;
208 i_admin_email ecx_tp_headers.company_admin_email%type;
209 i_sysdate date;
210 i_command Varchar2(10);
211 i_text_val varchar2(2000);
212 i_err_code varchar2(100);
213 i_num_val number;
214 i_date date;
215 i_error_type varchar2(10);
216 i_random_value number;
217 debug_mode number := 0;
218 cnt number;
219 i_message_id raw(16);
220
221 cursor c_trigger_id
222 is
223 select ecx_trigger_id_s.NEXTVAL
224 from dual;
225
226 PRAGMA AUTONOMOUS_TRANSACTION;
227 i_method_name varchar2(2000) := 'ecx_errorlog.external_system';
228
229 begin
230
231 --- Sets the Log Directory in both Standalone and the Embedded mode
232 ecx_utils.getLogDirectory;
233 ecx_debug.enable_debug_new(debug_mode, ecx_utils.g_logdir, 'otacb_'||i_outmsgid||'.log', 'otacb_'||i_outmsgid||'.log');
234
235 /* Assign local variables with the ecx_debug global variables*/
236 l_procedure := ecx_debug.g_procedure;
237 l_statement := ecx_debug.g_statement;
238 l_unexpected := ecx_debug.g_unexpected;
239 l_procedureEnabled := ecx_debug.g_procedureEnabled;
240 l_statementEnabled := ecx_debug.g_statementEnabled;
241 l_unexpectedEnabled := ecx_debug.g_unexpectedEnabled;
242
243 if (l_procedureEnabled) then
244 ecx_debug.push(i_method_name);
245 end if;
246
247 if i_outmsgid is null
248 then
249 o_ret_code :=1;
250 o_ret_msg := 'Message Id is null';
251 return;
252 end if;
253
254 open c_trigger_id;
255 fetch c_trigger_id into i_trigger_id;
256 close c_trigger_id;
257
258 if i_errmsg is not null
259 then
260 open c_ecx_errorno;
261 fetch c_ecx_errorno into i_error_no;
262 close c_ecx_errorno;
263 end if;
264 insert into ecx_external_logs
265 (
266 external_process_id,
267 out_msgid,
268 status,
269 error_id,
270 time_stamp
271 )
272 values
273 (
274 i_trigger_id,
275 i_outmsgid,
276 i_status,
277 i_error_no,
278 i_timestamp
279 );
280
281 if i_errmsg is not null
282 then
283 insert into ecx_msg_logs
284 (
285 log_id,
286 trigger_id,
287 error_id
288 )
289 values
290 (
291 ecx_msg_logs_s.NEXTVAL,
292 i_trigger_id,
293 i_error_no
294 );
295
296 insert into ecx_error_msgs
297 (
298 error_id,
299 message,
300 message_parameters
301 )
302 values (
303 i_error_no,
304 i_errmsg,
305 nvl(i_errparams, ecx_utils.i_errparams)
306 );
307 ecx_utils.i_curr_errid := i_error_no;
308 end if;
309 /* Start of Bug #2167164 */
310 BEGIN
311 update ecx_external_retry set status= i_status,time_stamp = i_timestamp,error_id=i_error_no where retry_msgid=i_outmsgid;
312 If sql%notfound then
313 NULL;
314 end if;
315 END;
316 /* End of Bug 2167164 */
317
318 /** Begin of the Bug 1999883 Callback to Workflow Routine **/
319 if(l_statementEnabled) then
320 ecx_debug.log(l_statement,'ecx_errorlog.external_system- callback to Workflow Code',
321 i_method_name);
322 ecx_debug.log(l_statement,'i_outmsgid',i_outmsgid,i_method_name);
323 end if;
324
325 Begin
326 Select count(*)
327 into cnt
328 from ecx_doclogs
329 where msgid = i_outmsgid;
330 Exception
331 When others then
332 Null;
333 End;
334
335 if cnt = 0 then
336 -- since the msgid is not in ecx_doclogs it is possible that this was a resend of the
337 -- original msgid. So, check if it exists in ecx_external_retry and get the original
338 -- msgid
339 begin
340 select msgid
341 into i_message_id
342 from ecx_external_retry
343 where retry_msgid = i_outmsgid;
344 exception
345 when no_data_found then
346 null;
347 when others then
348 raise;
349 end;
350 else
351 -- msgid found in ecx_doclogs
352 i_message_id := i_outmsgid;
353 end if;
354
355 if (i_message_id is not null) then
356
357 Begin
358 select cb_event_name, cb_event_key, block_mode,
359 msgid,message_type,message_standard,
360 transaction_type, transaction_subtype,
361 msgid, message_type, message_standard,
362 document_number,protocol_type,protocol_address,
363 username,attribute1, attribute2,
364 attribute3, attribute4, attribute5,
365 party_type, partyid, party_site_id,
366 item_type, item_key, activity_id
367 into i_event_name, i_event_key, i_block_mode,
368 i_msgid,i_message_type,i_message_standard,
369 i_transaction_type, i_transaction_subtype,
370 i_msgid, i_message_type, i_message_standard,
371 i_document_number,i_protocol_type,i_protocol_address,
372 i_username,i_attribute1, i_attribute2,
373 i_attribute3, i_attribute4, i_attribute5,
374 i_party_type, i_party_id, i_party_site_id,
375 i_item_type, i_item_key, i_activity_id
376 from ecx_doclogs
377 where msgid = i_message_id;
378 exception
379 when others then
380 raise;
381 end;
382
383 if(l_statementEnabled) then
384 ecx_debug.log(l_statement,'i_event_name',i_event_name,i_method_name);
385 ecx_debug.log(l_statement,'i_event_key',i_event_key,i_method_name);
386 ecx_debug.log(l_statement,'i_block_mode',i_block_mode,i_method_name);
387 ecx_debug.log(l_statement,'i_transaction_type',i_transaction_type,i_method_name);
388 ecx_debug.log(l_statement,'i_transaction_subtype',i_transaction_subtype,i_method_name);
389 ecx_debug.log(l_statement,'i_party_site_id',i_party_site_id,i_method_name);
390 ecx_debug.log(l_statement,'i_msgid',i_msgid,i_method_name);
391 ecx_debug.log(l_statement,'i_message_type',i_message_type,i_method_name);
392 ecx_debug.log(l_statement,'i_message_standard',i_message_standard,i_method_name);
393 ecx_debug.log(l_statement,'i_document_number',i_document_number,i_method_name);
394 ecx_debug.log(l_statement,'i_protocol_type',i_protocol_Type,i_method_name);
395 ecx_debug.log(l_statement,'i_protocol_address',i_protocol_address,i_method_name);
396 ecx_debug.log(l_statement,'i_username',i_username,i_method_name);
397 ecx_debug.log(l_statement,'i_attribute1',i_attribute1,i_method_name);
398 ecx_debug.log(l_statement,'i_attribute2',i_attribute2,i_method_name);
399 ecx_debug.log(l_statement,'i_attribute3',i_attribute3,i_method_name);
400 ecx_debug.log(l_statement,'i_attribute4',i_attribute4,i_method_name);
401 ecx_debug.log(l_statement,'i_attribute5',i_attribute5,i_method_name);
402 end if;
403
404 i_err_code := i_errmsg;
405 i_text_val := ecx_debug.getMessage(i_errmsg);
406 i_num_val := i_status;
407
408 ---- Raise a custom Event
409
410 if (i_event_name is not null)
411 then
412
413 i_params := wf_parameter_list_t();
414 wf_event.addParameterToList(p_name => 'ECX_PARTY_TYPE',
415 p_value => i_party_type,
416 p_parameterlist => i_params);
417 wf_event.addParameterToList(p_name => 'ECX_PARTY_ID',
418 p_value => i_party_id,
419 p_parameterlist => i_params);
420 wf_event.addParameterToList(p_name => 'ECX_PARTY_SITE_ID',
421 p_value => i_party_site_id,
422 p_parameterlist => i_params);
423 wf_event.addParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
424 p_value => i_transaction_type,
425 p_parameterlist => i_params);
426 wf_event.addParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
427 p_value => i_transaction_subtype,
428 p_parameterlist => i_params);
429 wf_event.addParameterToList(p_name => 'ECX_MESSAGE_TYPE',
430 p_value => i_message_type,
431 p_parameterlist => i_params);
432 wf_event.addParameterToList(p_name => 'ECX_MESSAGE_STANDARD',
433 p_value => i_message_standard,
434 p_parameterlist => i_params);
435 wf_event.addParameterToList(p_name => 'ECX_DOCUMENT_ID',
436 p_value => i_document_number,
437 p_parameterlist => i_params);
438 wf_event.addParameterToList(p_name => 'ECX_PROTOCOL_TYPE',
439 p_value => i_protocol_type,
440 p_parameterlist => i_params);
441 wf_event.addParameterToList(p_name => 'ECX_PROTOCOL_ADDRESS',
442 p_value => i_protocol_address,
443 p_parameterlist => i_params);
444 wf_event.addParameterToList(p_name => 'ECX_USERNAME',
445 p_value => i_username,
446 p_parameterlist => i_params);
447 wf_event.addParameterToList(p_name => 'ECX_MSGID',
448 p_value => i_message_id,
449 p_parameterlist => i_params);
450 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE1',
451 p_value => i_attribute1,
452 p_parameterlist => i_params);
453 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE2',
454 p_value => i_attribute2,
455 p_parameterlist => i_params);
456 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE3',
457 p_value => i_attribute3,
458 p_parameterlist => i_params);
459 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE4',
460 p_value => i_attribute4,
461 p_parameterlist => i_params);
462 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE5',
463 p_value => i_attribute5,
464 p_parameterlist => i_params);
465 wf_event.addParameterToList(p_name => 'ECX_ERROR_MSG',
466 p_value => i_text_val,
467 p_parameterlist => i_params);
468 wf_event.addParameterToList(p_name => 'ECX_RETURN_CODE',
469 p_value => i_status,
470 p_parameterlist => i_params);
471 wf_event.addParameterToList(p_name => 'ECX_ERR_PARAMS',
472 p_value => i_errparams,
473 p_parameterlist => i_params);
474
475
476 if(l_statementEnabled) then
477 ecx_debug.log(l_statement,'Raising the Customized event', i_event_name,
478 i_method_name);
479 end if;
480 i_random_value := wf_core.random;
481 i_event_key := i_event_key||i_random_value;
482 wf_event.raise(i_event_name, i_event_key, null, i_params);
483
484 end if;
485
486 if (i_block_mode = 'Y')
487 then
488 if (i_status = 0) then
489 i_command := 'COMPLETE';
490 else
491 i_command := 'ERROR';
492 end if;
493
494 wf_core.error_name := i_command;
495 wf_core.error_message := i_text_val;
496
497 if(l_statementEnabled) then
498 ecx_debug.log(l_statement,'i_command',i_command,i_method_name);
499 ecx_debug.log(l_statement,'i_errmsg',i_errmsg,i_method_name);
500 ecx_debug.log(l_statement,'i_status',i_status,i_method_name);
501 ecx_debug.log(l_statement,'i_text_val',i_text_val,i_method_name);
502 ecx_debug.log(l_statement,'i_num_val',i_num_val,i_method_name);
503 ecx_debug.log(l_statement,'i_item_type',i_item_type,i_method_name);
504 ecx_debug.log(l_statement,'i_item_key',i_item_key,i_method_name);
505 ecx_debug.log(l_statement,'i_activity_id',i_activity_id,i_method_name);
506 end if;
507
508 wf_engine.CB(command => i_command,
509 context => i_item_type || ':' || i_item_key ||
510 ':' || i_activity_id,
511 text_value => i_err_code,
512 number_value => i_num_val,
513 date_value => i_date);
514
515 if(l_statementEnabled) then
516 ecx_debug.log(l_statement,'wf error_name',wf_core.error_name,
517 i_method_name);
518 ecx_debug.log(l_statement,'wf error_message',wf_core.error_message,
519 i_method_name);
520 ecx_debug.log(l_statement,'ecx_errorlog.external_system Callback DONE ',
521 i_method_name);
522 end if;
523 else
524 if(l_statementEnabled) then
525 ecx_debug.log(l_statement,'ecx_errorlog.external_system-testing non block mode',
526 i_method_name);
527 ecx_debug.log(l_statement,'ecx_errorlog.external_system-i_status',
528 i_status,i_method_name);
529 end if;
530 if (i_status <> 0)
531 then
532 i_params := wf_parameter_list_t();
533 wf_event.addParameterToList(p_name => 'ECX_PARTY_TYPE',
534 p_value => i_party_type,
535 p_parameterlist => i_params);
536 wf_event.addParameterToList(p_name => 'ECX_PARTY_ID',
537 p_value => i_party_id,
538 p_parameterlist => i_params);
539 wf_event.addParameterToList(p_name => 'ECX_PARTY_SITE_ID',
540 p_value => i_party_site_id,
541 p_parameterlist => i_params);
542 wf_event.addParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
543 p_value => i_transaction_type,
544 p_parameterlist => i_params);
545 wf_event.addParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
546 p_value => i_transaction_subtype,
547 p_parameterlist => i_params);
548 wf_event.addParameterToList(p_name => 'ECX_MESSAGE_TYPE',
549 p_value => i_message_type,
550 p_parameterlist => i_params);
551 wf_event.addParameterToList(p_name => 'ECX_MESSAGE_STANDARD',
552 p_value => i_message_standard,
553 p_parameterlist => i_params);
554 wf_event.addParameterToList(p_name => 'ECX_DOCUMENT_ID',
555 p_value => i_document_number,
556 p_parameterlist => i_params);
557 wf_event.addParameterToList(p_name => 'ECX_PROTOCOL_TYPE',
558 p_value => i_protocol_type,
559 p_parameterlist => i_params);
560 wf_event.addParameterToList(p_name => 'ECX_PROTOCOL_ADDRESS',
561 p_value => i_protocol_address,
562 p_parameterlist => i_params);
563 wf_event.addParameterToList(p_name => 'ECX_USERNAME',
564 p_value => i_username,
565 p_parameterlist => i_params);
566 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE1',
567 p_value => i_attribute1,
568 p_parameterlist => i_params);
569 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE2',
570 p_value => i_attribute2,
571 p_parameterlist => i_params);
572 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE3',
573 p_value => i_attribute3,
574 p_parameterlist => i_params);
575 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE4',
576 p_value => i_attribute4,
577 p_parameterlist => i_params);
578 wf_event.addParameterToList(p_name => 'ECX_ATTRIBUTE5',
579 p_value => i_attribute5,
580 p_parameterlist => i_params);
581 wf_event.addParameterToList(p_name => 'ECX_MSGID',
582 p_value => i_message_id,
583 p_parameterlist => i_params);
584 wf_event.addParameterToList(p_name => 'ECX_ERROR_MSG',
585 p_value => i_text_val,
586 p_parameterlist => i_params);
587 wf_event.addParameterToList(p_name => 'ECX_RETURN_CODE',
588 p_value => i_status,
589 p_parameterlist => i_params);
590 wf_event.addParameterToList(p_name => 'ECX_ERR_PARAMS',
591 p_value => i_errparams,
592 p_parameterlist => i_params);
593 -- set the error type
594 If (i_status <> 0)
595 then
596 i_error_type := 30; /*** Notify System Administrator ***/
597 --- ECX_Trading_Partner_PVT.get_sysadmin_email(i_admin_email,o_ret_code,o_ret_msg);
598 end if;
599
600 wf_event.addParameterToList(p_name => 'ECX_ERROR_TYPE',
601 p_value => i_error_Type,
602 p_parameterlist => i_params);
603
604 wf_event.addParameterToList(p_name => 'ECX_SA_ROLE',
605 p_value => 'ECX_SA',
606 p_parameterlist => i_params);
607
608 if(l_statementEnabled) then
609 ecx_debug.log(l_statement,'Raising the event - oracle.apps.ecx.processing.message.callback',
610 i_method_name);
611 end if;
612 wf_event.raise('oracle.apps.ecx.processing.message.callback',
613 i_transaction_type|| '-' || i_transaction_subtype || '-' ||
614 i_party_site_id || '-' || i_error_no,
615 null,
616 i_params
617 );
618 if(l_statementEnabled) then
619 ecx_debug.log(l_statement,'i_message_id', i_message_id,i_method_name);
620 end if;
621 end if;
622 end if;
623 end if;
624
625 /** End of the Bug 1999883 Callback to Workflow Routine **/
626
627 o_ret_code :=0;
628 o_ret_msg := 'Message Successfully recorded';
629
630 /**
631 Commit for the Autonomous transaction.
632 **/
633 commit;
634 if (l_procedureEnabled) then
635 ecx_debug.pop(i_method_name);
636 end if;
637 ecx_debug.print_log;
638 ecx_debug.disable_debug;
639
640 exception
641 when dup_val_on_index then
642 o_ret_code := ecx_util_api.g_dup_error;
643 o_ret_msg := SQLERRM;
644 if (l_procedureEnabled) then
645 ecx_debug.pop(i_method_name);
646 end if;
647 ecx_debug.print_log;
648 ecx_debug.disable_debug;
649 when others then
650 rollback;
651 o_ret_code :=2;
652 o_ret_msg := SQLERRM||' - ECT_ERRLOG.EXTERNAL_SYSTEM';
653 if (l_procedureEnabled) then
654 ecx_debug.pop(i_method_name);
655 end if;
656 ecx_debug.print_log;
657 ecx_debug.disable_debug;
658 end external_system;
659
660 procedure send_error
661 (
662 i_ret_code IN pls_integer,
663 i_errbuf IN varchar2,
664 i_snd_tp_id IN varchar2,
665 i_document_number IN varchar2,
666 i_transaction_type IN varchar2,
667 o_ret_code OUT NOCOPY pls_integer,
668 o_ret_msg OUT NOCOPY varchar2
669 )
670 is
671 p_event wf_event_t;
672 x_from_agt wf_agent_t := wf_agent_t(null, null);
673 m_transaction_subtype varchar2(250);
674 m_party_id number; /* Bug 2122579 */
675 m_party_site_id number; /* Bug 2122579 */
676 m_org_id pls_integer;
677 m_admin_email varchar2(256);
678 retcode pls_integer;
679 retmsg varchar2(2000);
680 i_sysdate date;
681 i_error_no number(15);
682
683 cursor c1
684 is
685 select sysdate from dual;
686
687 begin
688
689 /* Since ret_code tells us whether there was an error,
690 return back to the calling program if it is null */
691
692 if (i_ret_code is null) or (i_ret_code = 0)
693 then
694 return;
695 elsif (i_ret_code = 1)
696 then
697 ecx_utils.error_type := 20;
698 elsif (i_ret_code = 2)
699 then
700 ecx_utils.error_type := 30;
701 end if;
702
703 open c1;
704 fetch c1 into i_sysdate;
705 close c1;
706
707 if ( ecx_utils.g_direction = 'IN' ) and (ecx_utils.error_type = 20)
708 then
709 ecx_trading_partner_pvt.get_senders_tp_info
710 (
711 p_party_id => m_party_id,
712 p_party_site_id => m_party_site_id,
713 p_org_id => m_org_id,
714 p_admin_email => m_admin_email,
715 retcode => retcode,
716 retmsg => retmsg
717 );
718 elsif ( ecx_utils.g_direction = 'OUT' ) and (ecx_utils.error_type = 20)
719 then
720 ecx_trading_partner_pvt.get_receivers_tp_info
721 (
722 p_party_id => m_party_id,
723 p_party_site_id => m_party_site_id,
724 p_org_id => m_org_id,
725 p_admin_email => m_admin_email,
726 retcode => retcode,
727 retmsg => retmsg
728 );
729
730 end if;
731
732 open c_ecx_errorno;
733 fetch c_ecx_errorno into i_error_no;
734 close c_ecx_errorno;
735
736 wf_event_t.initialize(p_event);
737 x_from_agt := null;
738 p_event.setEventName('oracle.apps.ecx.processing.notification.send');
739 p_event.setEventKey(i_transaction_type|| '-' || m_transaction_subtype || '-' || m_party_site_id || '-' || i_error_no);
740 p_event.setFromAgent(x_from_agt);
741 p_event.setSendDate(i_sysdate);
742
743 p_event.addParameterToList('ECX_RETURN_CODE', i_ret_code);
744 p_event.addParameterToList('ECX_ERROR_MSG',i_errbuf);
745 p_event.addParameterToList('ECX_ERROR_TYPE',ecx_utils.error_type);
746 p_event.addParameterToList('ECX_PARTY_ID', m_party_id);
747 p_event.addParameterToList('ECX_PARTY_SITE_ID', m_party_site_id);
748 p_event.addParameterToList('ECX_TRANSACTION_TYPE', ecx_utils.g_transaction_type);
749 p_event.addParameterToList('ECX_TRANSACTION_SUBTYPE',ecx_utils.g_transaction_subtype);
750 p_event.addParameterToList('ECX_PARTY_ADMIN_EMAIL',m_admin_email);
751
752 wf_event.raise(p_event.getEventName(),
753 p_event.getEventKey(),
754 null,
755 p_event.getParameterList()
756 );
757
758 o_ret_code :=0;
759 o_ret_msg := 'SUCCESS';
760 exception
761 when others then
762 o_ret_code :=2;
763 o_ret_msg :=SQLERRM || ' at ECX_ERRORLOG.SEND_ERROR';
764 end send_error;
765
766 procedure send_msg_api
767 (
768 x_retcode OUT NOCOPY pls_integer,
769 x_retmsg OUT NOCOPY varchar2,
770 p_retcode IN pls_integer,
771 p_errbuf IN varchar2,
772 p_error_type IN pls_integer ,
773 p_party_id IN varchar2,
774 p_party_site_id IN varchar2,
775 p_transaction_type IN varchar2,
776 p_transaction_subtype IN varchar2,
777 p_party_type IN varchar2 ,
778 p_document_number IN varchar2
779 )
780 is
781 cursor c1
782 is
783 select sysdate from dual;
784
785 cursor c_party_type(i_transaction_type in varchar2, i_transaction_subtype in varchar2)
786 is
787 select party_type
788 from ecx_transactions
789 where transaction_type = i_transaction_type
790 and transaction_subtype = i_transaction_subtype;
791
792 p_event wf_event_t;
793 x_from_agt wf_agent_t := wf_agent_t(null, null);
794
795 i_admin_email ecx_tp_headers.company_admin_email%type;
796 i_party_type ecx_tp_headers.party_type%type;
797 i_error_no pls_integer;
798 i_sysdate date;
799 i_c_dt date;
800 i_c_by pls_integer;
801 i_l_by pls_integer;
802 i_l_dt date;
803 i_tp_hdr_id number;
804
805 begin
806
807 ecx_utils.error_type := p_error_type;
808 ecx_utils.g_transaction_type := p_transaction_type;
809 ecx_utils.g_transaction_subtype := p_transaction_subtype;
810
811 open c1;
812 fetch c1 into i_sysdate;
813 close c1;
814
815 if p_party_type is null
816 then
817 for party_type_rec in c_party_type(p_transaction_type, p_transaction_subtype) loop
818 i_party_type := party_type_rec.party_type;
819 end loop;
820 elsif NOT(ecx_util_api.validate_party_type(p_party_type)) then
821 x_retmsg := ecx_debug.getTranslatedMessage('ECX_INVALID_PARTY_TYPE',
822 'p_party_type',p_party_type);
823 x_retcode := 2;
824 end if;
825
826 if ecx_utils.error_type = 20
827 then
828 ecx_tp_api.retrieve_trading_partner(
829 x_return_status => x_retcode,
830 x_msg => x_retmsg,
831 x_tp_header_id => i_tp_hdr_id,
832 p_party_type => p_party_type,
833 p_party_id => p_party_id,
834 p_party_site_id => p_party_site_id,
835 x_company_admin_email => i_admin_email,
836 x_created_by => i_c_by,
837 x_creation_date => i_c_dt,
838 x_last_updated_by => i_l_by,
839 x_last_update_date => i_l_dt);
840
841 end if;
842
843 open c_ecx_errorno;
844 fetch c_ecx_errorno into i_error_no;
845 close c_ecx_errorno;
846
847 wf_event_t.initialize(p_event);
848 x_from_agt := null;
849 p_event.setEventName('oracle.apps.ecx.processing.notification.send');
850 p_event.setEventKey(p_transaction_type|| '-' || p_transaction_subtype || '-' || p_party_site_id || '-' || i_error_no);
851 p_event.setFromAgent(x_from_agt);
852 p_event.setSendDate(i_sysdate);
853
854 p_event.addParameterToList('ECX_RETURN_CODE', p_retcode);
855 p_event.addParameterToList('ECX_ERROR_MSG',p_errbuf);
856 p_event.addParameterToList('ECX_ERROR_TYPE',ecx_utils.error_type);
857 p_event.addParameterToList('ECX_PARTY_ID', p_party_id);
858 p_event.addParameterToList('ECX_PARTY_SITE_ID', p_party_site_id);
859 p_event.addParameterToList('ECX_TRANSACTION_TYPE', p_transaction_type);
860 p_event.addParameterToList('ECX_TRANSACTION_SUBTYPE',p_transaction_subtype);
861 p_event.addParameterToList('ECX_PARTY_ADMIN_EMAIL',i_admin_email);
862
863
864 wf_event.raise(p_event.getEventName(),
865 p_event.getEventKey(),
866 null,
867 p_event.getParameterList()
868 );
869
870 x_retcode := ecx_util_api.g_no_error;
871 x_retmsg := 'SUCCESS';
872 exception
873 when others then
874 x_retcode := ecx_util_api.g_unexp_error;
875 x_retmsg := SQLERRM || ' at ECX_ERRORLOG.SEND_ERROR_API';
876 end send_msg_api;
877
878 procedure inbound_trigger
879 (
880 i_trigger_id IN number,
881 i_msgid IN raw,
882 i_process_id IN raw,
883 i_status IN varchar2,
884 i_errmsg IN varchar2,
885 i_errparams IN varchar2
886 )
887 is
888 i_error_no pls_integer;
889 PRAGMA AUTONOMOUS_TRANSACTION;
890 begin
891
892 if i_trigger_id is null or i_msgid is null
893 then
894 return;
895 end if;
896
897 if i_errmsg is not null
898 then
899 open c_ecx_errorno;
900 fetch c_ecx_errorno into i_error_no;
901 close c_ecx_errorno;
902 end if;
903
904 begin
905 insert into ecx_inbound_logs
906 (
907 trigger_id,
908 process_id,
909 error_id,
910 msgid,
911 status,
912 time_stamp
913 )
914 values
915 (
916 i_trigger_id,
917 i_process_id,
918 i_error_no,
919 i_msgid,
920 i_status,
921 sysdate
922 );
923 exception
924 /** Requires unique index on trigger_id **/
925 when dup_val_on_index then
926 update ecx_inbound_logs
927 set process_id = i_process_id,
928 msgid = i_msgid,
929 error_id = i_error_no,
930 status = i_status,
931 time_stamp = sysdate
932 where trigger_id = i_trigger_id;
933 end;
934
935 if i_errmsg is not null
936 then
937 insert into ecx_msg_logs
938 (
939 log_id,
940 trigger_id,
941 error_id
942 )
943 values
944 (
945 ecx_msg_logs_s.NEXTVAL,
946 i_trigger_id,
947 i_error_no
948 );
949 insert into ecx_error_msgs
950 (
951 error_id,
952 message,
953 message_parameters
954 )
955 values (
956 i_error_no,
957 i_errmsg,
958 nvl(i_errparams, ecx_utils.i_errparams)
959 );
960 ecx_utils.i_curr_errid := i_error_no;
961 end if;
962
963 /**
964 Commit for the Autonomous transaction.
965 **/
966 commit;
967 exception
968 when others then
969 rollback;
970 raise;
971 end inbound_trigger;
972
973 procedure outbound_trigger
974 (
975 i_trigger_id IN number,
976 i_transaction_type IN varchar2,
977 i_transaction_subtype IN varchar2,
978 i_party_id IN number,
979 i_party_site_id IN varchar2,
980 i_party_type IN varchar2 , -- 2183619
981 i_document_number IN varchar2,
982 i_status IN varchar2,
983 i_errmsg IN varchar2,
984 i_errparams IN varchar2
985 )
986 is
987 i_error_no number(15);
988 PRAGMA AUTONOMOUS_TRANSACTION;
989 l_document_number varchar2(2000);
990
991 begin
992 if i_trigger_id is null
993 then
994 return;
995 end if;
996
997 if i_errmsg is not null
998 then
999 open c_ecx_errorno;
1000 fetch c_ecx_errorno into i_error_no;
1001 close c_ecx_errorno;
1002 end if;
1003
1004 -- bug 4224455
1005 if i_document_number is null then
1006 l_document_number := i_trigger_id;
1007 else
1008 l_document_number := i_document_number;
1009 end if;
1010 begin
1011 insert into ecx_outbound_logs
1012 (
1013 trigger_id,
1014 transaction_type,
1015 transaction_subtype,
1016 party_id,
1017 party_site_id,
1018 party_type, --bug #2183619
1019 document_number,
1020 error_id,
1021 status,
1022 time_stamp
1023 )
1024 values
1025 (
1026 i_trigger_id,
1027 i_transaction_type,
1028 i_transaction_subtype,
1029 i_party_id,
1030 i_party_site_id,
1031 i_party_type, --bug #2183619
1032 l_document_number,
1033 i_error_no,
1034 i_status,
1035 sysdate
1036 );
1037 exception
1038 when dup_val_on_index then
1039 update ecx_outbound_logs
1040 set error_id = i_error_no,
1041 status = i_status,
1042 time_stamp = sysdate
1043 where trigger_id = i_trigger_id;
1044 end;
1045
1046 if i_errmsg is not null
1047 then
1048 insert into ecx_msg_logs
1049 (
1050 log_id,
1051 trigger_id,
1052 error_id
1053 )
1054 values
1055 (
1056 ecx_msg_logs_s.NEXTVAL,
1057 i_trigger_id,
1058 i_error_no
1059 );
1060
1061 insert into ecx_error_msgs
1062 (
1063 error_id,
1064 message,
1065 message_parameters
1066 )
1067 values (
1068 i_error_no,
1069 i_errmsg,
1070 nvl(i_errparams, ecx_utils.i_errparams)
1071 );
1072 ecx_utils.i_curr_errid := i_error_no;
1073 end if;
1074
1075 /**
1076 Commit for the Autonomous transaction.
1077 **/
1078 commit;
1079 exception
1080 when others then
1081 rollback;
1082 raise;
1083 end outbound_trigger;
1084
1085 procedure log_document
1086 (
1087 o_retcode OUT NOCOPY pls_integer,
1088 o_retmsg OUT NOCOPY varchar2,
1089 i_msgid IN raw,
1090 i_message_type IN varchar2,
1091 i_message_standard IN varchar2,
1092 i_transaction_type IN varchar2,
1093 i_transaction_subtype IN varchar2,
1094 i_document_number IN varchar2,
1095 i_partyid IN varchar2,
1096 i_party_site_id IN varchar2,
1097 i_party_type IN varchar2,
1098 i_protocol_type IN varchar2,
1099 i_protocol_address IN varchar2,
1100 i_username IN varchar2,
1101 i_password IN varchar2,
1102 i_attribute1 IN varchar2,
1103 i_attribute2 IN varchar2,
1104 i_attribute3 IN varchar2,
1105 i_attribute4 IN varchar2,
1106 i_attribute5 IN varchar2,
1107 i_payload IN clob,
1108 i_internal_control_num IN number,
1109 i_status IN varchar2 ,
1110 i_direction IN varchar2 ,
1111 i_outmsgid IN raw,
1112 i_logfile IN varchar2,
1113 i_item_type IN varchar2,
1114 i_item_key IN varchar2,
1115 i_activity_id IN varchar2,
1116 i_event_name IN varchar2,
1117 i_event_key IN varchar2,
1118 i_cb_event_name IN varchar2,
1119 i_cb_event_key IN varchar2,
1120 i_block_mode IN varchar2
1121 )
1122 is
1123
1124 l_party_type Varchar2(30) := null;
1125 l_size number;
1126 l_payload clob;
1127 PRAGMA AUTONOMOUS_TRANSACTION;
1128
1129 begin
1130 o_retcode := 0;
1131 l_size := dbms_lob.getlength(i_payload);
1132 dbms_lob.createtemporary(l_payload, TRUE, DBMS_LOB.SESSION);
1133 dbms_lob.copy(l_payload, i_payload, l_size);
1134
1135 if NOT (ecx_util_api.validate_direction(i_direction)) then
1136 o_retcode := ecx_util_api.G_INVALID_PARAM;
1137 o_retmsg := 'ECX_INVALID_DIRECTION';
1138 end if;
1139
1140 ecx_utils.convertPartyTypeToCode(i_party_type, l_party_type);
1141
1142 insert into ecx_doclogs
1143 (
1144 msgid,
1145 message_type,
1146 message_standard,
1147 transaction_type,
1148 transaction_subtype,
1149 document_number,
1150 partyid,
1151 party_site_id,
1152 party_type,
1153 protocol_type,
1154 protocol_address,
1155 username,
1156 password,
1157 attribute1,
1158 attribute2,
1159 attribute3,
1160 attribute4,
1161 attribute5,
1162 payload,
1163 internal_control_number,
1164 status,
1165 direction,
1166 time_stamp,
1167 out_msgid,
1168 logfile,
1169 item_type,
1170 item_key,
1171 activity_id,
1172 event_name,
1173 event_key,
1174 cb_event_name,
1175 cb_event_key,
1176 block_mode
1177 )
1178 values
1179 (
1180 i_msgid,
1181 i_message_type,
1182 i_message_standard,
1183 i_transaction_type,
1184 i_transaction_subtype,
1185 i_document_number,
1186 i_partyid,
1187 i_party_site_id,
1188 l_party_type,
1189 i_protocol_type,
1190 i_protocol_address,
1191 i_username,
1192 i_password,
1193 i_attribute1,
1194 i_attribute2,
1195 i_attribute3,
1196 i_attribute4,
1197 i_attribute5,
1198 l_payload,
1199 i_internal_control_num,
1200 i_status,
1201 i_direction,
1202 sysdate,
1203 i_outmsgid,
1204 i_logfile,
1205 i_item_type,
1206 i_item_key,
1207 i_activity_id,
1208 i_event_name,
1209 i_event_key,
1210 i_cb_event_name,
1211 i_cb_event_key,
1212 i_block_mode
1213 );
1214
1215 /**
1216 Commit for the Autonomous transaction.
1217 **/
1218 commit;
1219
1220 exception
1221 when dup_val_on_index then
1222 o_retcode := 1;
1223 o_retmsg := ecx_debug.getTranslatedMessage('ECX_DOCLOGS_EXISTS','p_msgid',i_msgid);
1224 ecx_debug.setErrorInfo(2, 30, 'ECX_DOCLOGS_EXISTS', 'p_msgid', i_msgid);
1225
1226 when others then
1227 o_retcode := 2;
1228 o_retmsg := SQLERRM;
1229 ecx_debug.setErrorInfo(2, 30, SQLERRM || '- ECX_ERRORLOG.LOG_DOCUMENT');
1230 end log_document;
1231
1232
1233 procedure update_log_document
1234 (
1235 i_msgid In raw,
1236 i_outmsgid In raw,
1237 i_status In varchar2,
1238 i_logfile In varchar2,
1239 i_update_type In varchar2
1240 )
1241 is
1242
1243 begin
1244 if i_update_type = 'STATUS'
1245 then
1246 update ecx_doclogs
1247 set status = i_status,
1248 time_stamp = sysdate
1249 where msgid = i_msgid;
1250 else
1251 update ecx_doclogs
1252 set status = i_status,
1253 logfile = i_logfile,
1254 out_msgid = i_outmsgid,
1255 time_stamp = sysdate
1256 where msgid = i_msgid;
1257
1258 end if;
1259 exception
1260 when others then
1261 raise;
1262 end update_log_document;
1263
1264 procedure getDoclogDetails
1265 (
1266 i_msgid in raw,
1267 i_message_type OUT NOCOPY varchar2,
1268 i_message_standard OUT NOCOPY varchar2,
1269 i_transaction_type OUT NOCOPY varchar2,
1270 i_transaction_subtype OUT NOCOPY varchar2,
1271 i_document_number OUT NOCOPY varchar2,
1272 i_party_id OUT NOCOPY varchar2,
1273 i_party_site_id OUT NOCOPY varchar2,
1274 i_protocol_type OUT NOCOPY varchar2,
1275 i_protocol_address OUT NOCOPY varchar2,
1276 i_username OUT NOCOPY varchar2,
1277 i_password OUT NOCOPY varchar2,
1278 i_attribute1 OUT NOCOPY varchar2,
1279 i_attribute2 OUT NOCOPY varchar2,
1280 i_attribute3 OUT NOCOPY varchar2,
1281 i_attribute4 OUT NOCOPY varchar2,
1282 i_attribute5 OUT NOCOPY varchar2,
1283 i_logfile OUT NOCOPY varchar2,
1284 i_internal_control_number OUT NOCOPY number,
1285 i_status OUT NOCOPY varchar2,
1286 i_time_stamp OUT NOCOPY date,
1287 i_direction OUT NOCOPY varchar2,
1288 /* Bug 2241292 */
1289 o_retcode OUT NOCOPY pls_integer,
1290 o_retmsg OUT NOCOPY varchar2
1291
1292 )
1293 is
1294
1295 cursor get_msg_attributes(p_msgid raw)
1296 is
1297 select 1 msg_count,
1298 message_type,
1299 message_standard,
1300 transaction_type,
1301 transaction_subtype,
1302 document_number,
1303 partyid,
1304 party_site_id,
1305 protocol_type,
1306 protocol_address,
1307 username,
1308 password,
1309 attribute1,
1310 attribute2,
1311 attribute3,
1312 attribute4,
1313 attribute5,
1314 logfile,
1315 internal_control_number,
1316 status,
1317 time_stamp,
1318 direction
1319 from ecx_doclogs
1320 where msgid = p_msgid;
1321
1322 i_msg_count number :=0;
1323
1324 begin
1325 o_retcode := 0; --Bug 2241292
1326
1327 for c_msg_attributes in get_msg_attributes(i_msgid)
1328 loop
1329 i_msg_count := c_msg_attributes.msg_count;
1330 i_message_type := c_msg_attributes.message_type;
1331 i_message_standard := c_msg_attributes.message_standard;
1332 i_transaction_type := c_msg_attributes.transaction_type;
1333 i_transaction_subtype := c_msg_attributes.transaction_subtype;
1334 i_document_number := c_msg_attributes.document_number;
1335 i_party_id := c_msg_attributes.partyid;
1336 i_party_site_id := c_msg_attributes.party_site_id;
1337 i_protocol_type := c_msg_attributes.protocol_type;
1338 i_protocol_address := c_msg_attributes.protocol_address;
1339 i_username := c_msg_attributes.username;
1340 i_password := c_msg_attributes.password;
1341 i_attribute1 := c_msg_attributes.attribute1;
1342 i_attribute2 := c_msg_attributes.attribute2;
1343 i_attribute3 := c_msg_attributes.attribute3;
1344 i_attribute4 := c_msg_attributes.attribute4;
1345 i_attribute5 := c_msg_attributes.attribute5;
1346 i_logfile := c_msg_attributes.logfile;
1347 i_internal_control_number := c_msg_attributes.internal_control_number;
1348 i_status := c_msg_attributes.status;
1349 i_time_stamp := c_msg_attributes.time_stamp;
1350 i_direction := c_msg_attributes.direction;
1351 end loop;
1352
1353 if (i_msg_count = 0) then
1354 raise no_data_found;
1355 end if;
1356
1357 o_retmsg := 'SUCCESS';
1358 exception
1359 when no_data_found then
1360 o_retcode := 2;
1361 o_retmsg := ecx_debug.getTranslatedMessage('ECX_DOCLOGS_NOT_EXISTS', 'p_msgid', i_msgid);
1362 ecx_utils.i_ret_code := o_retcode;
1363 ecx_utils.i_errbuf := o_retmsg;
1364 when others then
1365 o_retcode := 2;
1366 o_retmsg := SQLERRM;
1367 ecx_utils.i_ret_code := o_retcode;
1368 ecx_utils.i_errbuf := o_retmsg;
1369
1370 end getDoclogDetails;
1371
1372 procedure log_resend(
1373 o_retcode OUT NOCOPY pls_integer,
1374 o_retmsg OUT NOCOPY varchar2,
1375 i_resend_msgid IN raw,
1376 i_msgid IN raw,
1377 i_errmsg IN varchar2,
1378 i_status IN varchar2,
1379 i_timestamp IN date
1380 )
1381 is
1382 i_error_no pls_integer:= 0;
1383 begin
1384 o_retcode := 0;
1385 o_retmsg := null;
1386
1387 if(i_errmsg is not null) then
1388 open c_ecx_errorno;
1389 fetch c_ecx_errorno into i_error_no;
1390 close c_ecx_errorno;
1391 end if;
1392
1393 insert into ecx_external_retry
1394 (
1395 retry_msgid,
1396 msgid,
1397 status,
1398 error_id,
1399 time_stamp
1400 )
1401 values
1402 (
1403 i_resend_msgid,
1404 i_msgid,
1405 i_status,
1406 i_error_no,
1407 i_timestamp
1408 );
1409
1410 if (i_errmsg is not null) then
1411 insert into ecx_error_msgs
1412 (
1413 error_id,
1414 message
1415 )
1416 values
1417 (
1418 i_error_no,
1419 i_errmsg
1420 );
1421 end if;
1422 exception
1423 when dup_val_on_index then
1424 o_retcode := ecx_util_api.g_dup_error;
1425 o_retmsg := SQLERRM;
1426 when others then
1427 o_retcode := 2;
1428 o_retmsg := SQLERRM;
1429 end log_resend;
1430
1431
1432 procedure get_event_params
1433 (p_event in wf_event_t,
1434 x_message_type out NOCOPY varchar2,
1435 x_message_standard out NOCOPY varchar2,
1436 x_ext_type out NOCOPY varchar2,
1437 x_ext_subtype out NOCOPY varchar2,
1438 x_document_id out NOCOPY varchar2,
1439 x_logfile out NOCOPY varchar2,
1440 x_party_id out NOCOPY varchar2,
1441 x_party_type out NOCOPY varchar2,
1442 x_party_site_id out NOCOPY varchar2,
1443 x_protocol_type out NOCOPY varchar2,
1444 x_protocol_address out NOCOPY varchar2,
1445 x_username out NOCOPY ecx_tp_details.username%TYPE,
1446 x_password out NOCOPY ecx_tp_details.password%TYPE,
1447 x_attribute1 out NOCOPY varchar2,
1448 x_attribute2 out NOCOPY varchar2,
1449 x_attribute3 out NOCOPY varchar2,
1450 x_attribute4 out NOCOPY varchar2,
1451 x_attribute5 out NOCOPY varchar2,
1452 x_direction out NOCOPY varchar2,
1453 x_trigger_id out NOCOPY varchar2,
1454 x_ecx_msgid out NOCOPY raw,
1455 x_item_type out NOCOPY varchar2,
1456 x_item_key out NOCOPY varchar2,
1457 x_activity_id out NOCOPY varchar2,
1458 x_cb_event_name out NOCOPY varchar2,
1459 x_cb_event_key out NOCOPY varchar2,
1460 x_block_mode out NOCOPY varchar2,
1461 x_int_type out NOCOPY varchar2,
1462 x_int_subtype out NOCOPY varchar2,
1463 x_int_party_site_id out NOCOPY number,
1464 x_resend out NOCOPY boolean
1465 )
1466 is
1467 l_param_list wf_parameter_list_t;
1468 l_param_name varchar2(2000);
1469 l_param_value varchar2(2000);
1470 l_module varchar2(2000);
1471
1472 begin
1473
1474 -- loop through the parameter list and get all the info for logging
1475 l_param_list := p_event.getParameterList();
1476 l_module := 'ecx.plsql.ecx_errorlog.get_event_params';
1477 if (l_param_list is null) then
1478 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1479 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
1480 'event object parameter list in empty');
1481 end if;
1482 return;
1483 else
1484 x_ecx_msgid := p_event.GetValueForParameter('ECX_MSG_ID');
1485 l_param_value := p_event.GetValueForParameter('RESEND');
1486 if l_param_value = 'Y' then
1487 x_resend := true;
1488 return;
1489 else
1490 x_resend := false;
1491 end if;
1492
1493 for i in l_param_list.first..l_param_list.last loop
1494 l_param_name := l_param_list(i).GetName;
1495 l_param_value := l_param_list(i).GetValue;
1496
1497 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1498 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
1499 l_param_name || ': ' || l_param_value);
1500 end if;
1501
1502 if l_param_name = 'MESSAGE_TYPE' then
1503 x_message_type := l_param_value;
1504
1505 elsif l_param_name = 'MESSAGE_STANDARD' then
1506 x_message_standard := l_param_value;
1507
1508 elsif l_param_name = 'TRANSACTION_TYPE' then
1509 x_ext_type := l_param_value;
1510
1511 elsif l_param_name = 'TRANSACTION_SUBTYPE' then
1512 x_ext_subtype := l_param_value;
1513
1514 elsif l_param_name = 'DOCUMENT_NUMBER' then
1515 x_document_id := l_param_value;
1516
1517 elsif l_param_name = 'PARTY_TYPE' then
1518 x_party_type := l_param_value;
1519
1520 elsif l_param_name = 'PARTYID' then
1521 x_party_id := l_param_value;
1522
1523 elsif l_param_name = 'PARTY_SITE_ID' then
1524 x_party_site_id := l_param_value;
1525
1526 elsif l_param_name = 'PROTOCOL_TYPE' then
1527 x_protocol_type := l_param_value;
1528
1529 elsif l_param_name = 'PROTOCOL_ADDRESS' then
1530 x_protocol_address := l_param_value;
1531
1532 elsif l_param_name = 'USERNAME' then
1533 x_username := l_param_value;
1534
1535 elsif l_param_name = 'PASSWORD' then
1536 x_password := l_param_value;
1537
1538 elsif l_param_name = 'ATTRIBUTE1' then
1539 x_attribute1 := l_param_value;
1540
1541 elsif l_param_name = 'ATTRIBUTE2' then
1542 x_attribute2 := l_param_value;
1543
1544 elsif l_param_name = 'ATTRIBUTE3' then
1545 x_attribute3 := l_param_value;
1546
1547 elsif l_param_name = 'ATTRIBUTE4' then
1548 x_attribute4 := l_param_value;
1549
1550 elsif l_param_name = 'ATTRIBUTE5' then
1551 x_attribute5 := l_param_value;
1552
1553 elsif l_param_name = 'DIRECTION' then
1554 x_direction:= l_param_value;
1555
1556 elsif l_param_name = 'ITEM_TYPE' then
1557 x_item_type := l_param_value;
1558
1559 elsif l_param_name = 'ITEM_KEY' then
1560 x_item_key := l_param_value;
1561
1562 elsif l_param_name = 'ACTIVITY_ID' then
1563 x_activity_id := l_param_value;
1564
1565 elsif l_param_name = '#CB_EVENT_NAME' then
1566 x_cb_event_name := l_param_value;
1567
1568 elsif l_param_name = '#CB_EVENT_KEY' then
1569 x_cb_event_key := l_param_value;
1570
1571 elsif l_param_name = '#BLOCK_MODE' then
1572 x_block_mode := l_param_value;
1573
1574 elsif l_param_name = 'LOGFILE' then
1575 x_logfile := l_param_value;
1576
1577 elsif l_param_name = 'TRIGGER_ID' then
1578 x_trigger_id := l_param_value;
1579
1580 elsif l_param_name = 'INT_TRANSACTION_TYPE' then
1581 x_int_type := l_param_value;
1582
1583 elsif l_param_name = 'INT_TRANSACTION_SUBTYPE' then
1584 x_int_subtype := l_param_value;
1585
1586 elsif l_param_name = 'INT_PARTY_SITE_ID' then
1587 x_int_party_site_id := l_param_value;
1588
1589 end if;
1590 end loop;
1591 end if;
1592
1593 exception
1594 when others then
1595 raise;
1596 end;
1597
1598
1599 procedure outbound_log (
1600 p_event in wf_event_t)
1601
1602 is
1603
1604 l_out_msgid raw(16);
1605 l_ecx_msgid raw(16);
1606 l_message_type varchar2(200);
1607 l_message_standard varchar2(200);
1608 l_ext_type varchar2(200);
1609 l_ext_subtype varchar2(200);
1610 l_document_id varchar2(200);
1611 l_retcode pls_integer;
1612 l_retmsg varchar2(200);
1613 l_logfile varchar2(500);
1614 l_party_id varchar2(200);
1615 l_party_type varchar2(200);
1616 l_party_site_id varchar2(200);
1617 l_protocol_type varchar2(200);
1618 l_protocol_address ecx_tp_details.protocol_address%TYPE;
1619 l_username ecx_tp_details.username%TYPE;
1620 l_password ecx_tp_details.password%TYPE;
1621 l_attribute1 varchar2(200);
1622 l_attribute2 varchar2(200);
1623 l_attribute3 varchar2(200);
1624 l_attribute4 varchar2(200);
1625 l_attribute5 varchar2(200);
1626 l_item_type varchar2(200);
1627 l_item_key varchar2(200);
1628 l_activity_id varchar2(200);
1629 l_cb_event_name varchar2(200);
1630 l_cb_event_key varchar2(200);
1631 l_block_mode varchar2(200);
1632 l_direction varchar2(200);
1633 l_trigger_id varchar2(200);
1634
1635 -- internal transaction type and subtype for loging outbound passthrough
1636 l_tran_type varchar2(200);
1637 l_tran_subtype varchar2(200);
1638 l_int_party_site_id number;
1639
1640 l_resend boolean := false;
1641 l_log_msg varchar2(2000);
1642 l_passthr_trigger_id varchar2(200);
1643 l_module varchar2(2000);
1644
1645 cursor c1
1646 is
1647 select ecx_trigger_id_s.NEXTVAL
1648 from dual;
1649
1650 begin
1651 if (p_event is null) then
1652 return;
1653 end if;
1654
1655 if (wf_event.g_msgid is null) then
1656 return;
1657 else
1658 l_out_msgid := wf_event.g_msgid;
1659 end if;
1660
1661 l_module := 'ecx.plsql.ecx_errorlog.outbound_log';
1662 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1663 wf_log_pkg.string(wf_log_pkg.level_procedure, l_module ||'.begin',
1664 'outbound_log');
1665 end if;
1666
1667 get_event_params(p_event => p_event,
1668 x_message_type => l_message_type,
1669 x_message_standard => l_message_standard,
1670 x_ext_type => l_ext_type,
1671 x_ext_subtype => l_ext_subtype,
1672 x_document_id => l_document_id,
1673 x_logfile => l_logfile,
1674 x_party_id => l_party_id,
1675 x_party_type => l_party_type,
1676 x_party_site_id => l_party_site_id,
1677 x_protocol_type => l_protocol_type,
1678 x_protocol_address => l_protocol_address,
1679 x_username => l_username,
1680 x_password => l_password,
1681 x_attribute1 => l_attribute1,
1682 x_attribute2 => l_attribute2,
1683 x_attribute3 => l_attribute3,
1684 x_attribute4 => l_attribute4,
1685 x_attribute5 => l_attribute5,
1686 x_direction => l_direction,
1687 x_trigger_id => l_trigger_id,
1688 x_ecx_msgid => l_ecx_msgid,
1689 x_item_type => l_item_type,
1690 x_item_key => l_item_key,
1691 x_activity_id => l_activity_id,
1692 x_cb_event_name => l_cb_event_name,
1693 x_cb_event_key => l_cb_event_key,
1694 x_block_mode => l_block_mode,
1695 x_int_type => l_tran_type,
1696 x_int_subtype => l_tran_subtype,
1697 x_int_party_site_id => l_int_party_site_id,
1698 x_resend => l_resend
1699 );
1700
1701 -- logging resend and doesn't need to log to ecx_doclogs and outbound_logs
1702 if l_resend then
1703 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1704 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
1705 'Logged resend for outbound.');
1706 end if;
1707 ecx_errorlog.log_resend (
1708 o_retcode => l_retcode,
1709 o_retmsg => l_retmsg,
1710 i_resend_msgid => l_out_msgid, -- RESEND ID
1711 i_msgid => l_ecx_msgid,
1712 i_errmsg => 'ECX_PENDING_AFTER_RESEND',
1713 i_status => '0',
1714 i_timestamp => sysdate
1715 );
1716 ecx_attachment.map_attachments(p_event, l_out_msgid);
1717 return;
1718 end if;
1719
1720 -- This is not a ecx message since l_ext_type and
1721 -- l_ext_subtype are required.
1722 if (l_ext_type is null or
1723 l_ext_subtype is null) then
1724 return;
1725 end if;
1726
1727 l_log_msg := 'ECX_MSG_CREATED_ENQUEUED';
1728
1729 -- for passthrough, update doclogs with the inbound status
1730 if (l_direction = 'IN') then
1731 ecx_errorlog.update_log_document
1732 (
1733 l_ecx_msgid,
1734 l_out_msgid, -- OUT MSG ID
1735 'Inbound processing complete.',
1736 l_logfile,
1737 null
1738 );
1739
1740 open c1;
1741 fetch c1 into l_passthr_trigger_id;
1742 close c1;
1743
1744 ecx_errorlog.outbound_trigger
1745 (
1746 i_trigger_id => l_passthr_trigger_id,
1747 i_transaction_type => l_tran_type,
1748 i_transaction_subtype => l_tran_subtype,
1749 i_party_id => l_party_id,
1750 i_party_site_id => l_int_party_site_id,
1751 i_party_type => l_party_type,
1752 i_document_number => l_document_id,
1753 i_status => '0',
1754 i_errmsg => 'ECX_PASSTHRU_OUTBOUND'
1755 );
1756
1757 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1758 wf_log_pkg.string(wf_log_pkg.level_statement, l_module,
1759 'Logged passthrough on outbound.');
1760 end if;
1761
1762 l_trigger_id := l_passthr_trigger_id;
1763 l_log_msg := 'ECX_PASSTHRU_MSG_ENQUEUED';
1764
1765 ecx_attachment.remap_attachments(l_out_msgid);
1766 else
1767 ecx_attachment.map_attachments(p_event, l_out_msgid);
1768 end if;
1769
1770 -- This is commong logging for regular outbound and passthru on the outbound side.
1771 ecx_errorlog.log_document
1772 (o_retcode => l_retcode,
1773 o_retmsg => l_retmsg,
1774 i_msgid => l_out_msgid,
1775 i_message_type => l_message_type,
1776 i_message_standard => l_message_standard,
1777 i_transaction_type => l_ext_type,
1778 i_transaction_subtype => l_ext_subtype,
1779 i_document_number => l_document_id,
1780 i_partyid => l_party_id,
1781 i_party_site_id => l_party_site_id,
1782 i_party_type => l_party_type,
1783 i_protocol_type => l_protocol_type,
1784 i_protocol_address => l_protocol_address,
1785 i_username => l_username,
1786 i_password => l_password,
1787 i_attribute1 => l_attribute1,
1788 i_attribute2 => l_attribute2,
1789 i_attribute3 => l_attribute3,
1790 i_attribute4 => l_attribute4,
1791 i_attribute5 => l_attribute5,
1792 i_payload => p_event.getEventData(),
1793 i_status => 'Message pending delivery.',
1794 i_direction => 'OUT',
1795 i_logfile => l_logfile,
1796 i_item_type => l_item_type,
1797 i_item_key => l_item_key,
1798 i_activity_id => l_activity_id,
1799 i_event_name => p_event.getEventName(),
1800 i_event_key => p_event.getEventKey(),
1801 i_cb_event_name => l_cb_event_name,
1802 i_cb_event_key => l_cb_event_key,
1803 i_block_mode => l_block_mode
1804 );
1805
1806 -- This code was originally in the queue handler and this is moved to here for
1807 -- backward compatible purpose. Not sure who is using this.
1808 if (l_direction is null) then
1809 l_direction := ecx_utils.g_direction;
1810 end if;
1811
1812 if (UPPER(l_direction) = 'OUT') then
1813 if (l_activity_id is not null) then
1814 begin
1815 wf_engine.SetItemAttrText(l_item_type, l_item_key, 'ECX_MSGID_ATTR', l_out_msgid);
1816 exception
1817 when others then
1818 -- If attr is not already defined, add a runtime attribute
1819 -- with this name, then try the set again.
1820 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
1821 wf_core.clear;
1822 wf_engine.AddItemAttr(itemtype => l_item_type,
1823 itemkey => l_item_key,
1824 aname => 'ECX_MSGID_ATTR',
1825 text_value => l_out_msgid);
1826 WF_CORE.Clear;
1827 else
1828 raise;
1829 end if;
1830 end;
1831 end if;
1832 end if;
1833
1834 ecx_errorlog.outbound_engine
1835 (
1836 l_trigger_id,
1837 '0',
1838 l_log_msg,
1839 l_out_msgid
1840 );
1841
1842 exception
1843 when others then
1844 raise;
1845 end outbound_log;
1846
1847 procedure log_receivemessage
1848 (caller varchar2,status_text varchar2,err_msg varchar2,receipt_msgid raw,trigger_id pls_integer,
1849 message_type varchar2,message_standard varchar2,transaction_type varchar2,transaction_subtype varchar2,
1850 document_number varchar2,partyid varchar2,party_site_id varchar2,party_type varchar2,protocol_type varchar2,
1851 protocol_address varchar2,username varchar2,encrypt_password varchar2,attribute1 varchar2,attribute2 varchar2,
1852 attribute3 varchar2,attribute4 varchar2,attribute5 varchar2,payload clob,returnval out nocopy varchar2) --return varchar2
1853 is
1854
1855 status binary_integer;
1856 -- err_msg varchar2(4000);
1857 err_params varchar2(255);
1858 i_message_counter pls_integer;
1859 l_retcode pls_integer := 0;
1860 l_retmsg varchar2(2000) := null;
1861
1862 begin
1863 select ecx_inlstn_s.nextval into i_message_counter from dual ;
1864 status := 2;
1865 if (status_text = 'SUCCESS') then
1866 status := 10;
1867 end if;
1868 begin
1869 ecx_errorlog.log_document(
1870 l_retcode,
1871 l_retmsg,
1872 receipt_msgid,
1873 message_type,
1874 message_standard,
1875 transaction_type,
1876 transaction_subtype,
1877 document_number,
1878 partyid,
1879 party_site_id,
1880 party_type,
1881 protocol_type,
1882 protocol_address,
1883 username,
1884 encrypt_password,
1885 attribute1,
1886 attribute2,
1887 attribute3,
1888 attribute4,
1889 attribute5,
1890 payload,
1891 i_message_counter,
1892 caller || ' receives and accepts inbound message.',
1893 'IN',
1894 null
1895 );
1896
1897 if (l_retcode = 1) then
1898 wf_log_pkg.string(6, 'WF_ECX_Q', l_retmsg);
1899 elsif (l_retcode >= 2) then
1900 raise ecx_log_exit;
1901 end if;
1902 end;
1903
1904 ecx_errorlog.inbound_trigger
1905 (
1906 trigger_id,
1907 receipt_msgid,
1908 null,
1909 status,
1910 err_msg,
1911 err_params
1912 );
1913
1914 returnval:= 'SUCCESS';
1915
1916 exception
1917 when others then
1918 returnval := 'ERROR. SQLERRM:='||sqlerrm;
1919
1920 end log_receivemessage;
1921
1922 end ecx_errorlog;