[Home] [Help]
PACKAGE BODY: APPS.M4U_XML_GENPROCESS_OUT
Source
1 PACKAGE BODY m4u_xml_genprocess_out AS
2 /* $Header: M4UOUTWB.pls 120.3 2006/05/11 21:39:56 bsaratna noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'm4u_xml_genprocess_out';
5 l_debug_level NUMBER;
6
7
8 -- Name
9 -- create_collab_setattr
10 -- Purpose
11 --
12 -- Arguments
13 -- itemtype => WF item type
14 -- itemkey => WF item key
15 -- actid => WF act id
16 -- funcmode => WF func mode
17 -- resultout => result param
18 -- Notes
19 -- None.
20 PROCEDURE create_collab_setattr(
21 itemtype IN VARCHAR2,
22 itemkey IN VARCHAR2,
23 actid IN NUMBER,
24 funcmode IN VARCHAR2,
25 resultout IN OUT NOCOPY VARCHAR2
26 )
27 IS
28 l_xmlg_transaction_type VARCHAR2(100);
29 l_xmlg_transaction_subtype VARCHAR2(100);
30 l_xmlg_document_id VARCHAR2(100);
31 l_coll_type VARCHAR2(100);
32 l_doc_type VARCHAR2(100);
33 l_owner_role VARCHAR2(100);
34
35 l_fnd_msg VARCHAR2(255);
36 l_error_msg VARCHAR2(255);
37 l_debug_mode VARCHAR2(255);
38 l_return_status VARCHAR2(255);
39 l_doc_no VARCHAR2(255);
40 l_partner_doc_no VARCHAR2(255);
41
42 l_msg_data VARCHAR2(2000);
43 l_create_msg_text VARCHAR2(2000);
44
45 l_unique1 VARCHAR2(30);
46 l_unique2 VARCHAR2(30);
47 l_unique3 VARCHAR2(30);
48 l_unique4 VARCHAR2(30);
49 l_unique5 VARCHAR2(30);
50 l_truncated_key VARCHAR2(30);
51
52 l_event_key VARCHAR2(50);
53
54 l_ecx_parameter1 VARCHAR2(150);
55 l_ecx_parameter2 VARCHAR2(150);
56 l_ecx_parameter3 VARCHAR2(150);
57 l_ecx_parameter4 VARCHAR2(150);
58 l_ecx_parameter5 VARCHAR2(150);
59
60 l_attribute1 VARCHAR2(150);
61 l_attribute2 VARCHAR2(150);
62 l_attribute3 VARCHAR2(150);
63 l_attribute4 VARCHAR2(150);
64 l_attribute5 VARCHAR2(150);
65 l_attribute6 VARCHAR2(150);
66 l_attribute7 VARCHAR2(150);
67 l_attribute8 VARCHAR2(150);
68 l_attribute9 VARCHAR2(150);
69 l_attribute10 VARCHAR2(150);
70 l_attribute11 VARCHAR2(150);
71 l_attribute12 VARCHAR2(150);
72 l_attribute13 VARCHAR2(150);
73 l_attribute14 VARCHAR2(150);
74 l_attribute15 VARCHAR2(150);
75
76 l_error_code NUMBER;
77 l_coll_id NUMBER;
78 l_doc_owner NUMBER;
79 l_msg_count VARCHAR2(50);
80
81 l_dattribute1 DATE;
82 l_dattribute2 DATE;
83 l_dattribute3 DATE;
84 l_dattribute4 DATE;
85 l_dattribute5 DATE;
86
87 BEGIN
88
89 IF (l_debug_Level <= 2) THEN
90 cln_debug_pub.Add('========= Entering create_collab_setattr == ',2);
91 END IF;
92
93 -- Parameters received
94 IF (l_Debug_Level <= 1) THEN
95 cln_debug_pub.Add('-- Parameters received from Workflow process -- ',1);
96 cln_debug_pub.Add('itemtype - '||itemtype, 1);
97 cln_debug_pub.Add('itemkey - '||itemkey, 1);
98 cln_debug_pub.Add('actid - '||actid, 1);
99 cln_debug_pub.Add('funcmode - '||funcmode, 1);
100 cln_debug_pub.Add('---------------------------------------------',1);
101 END IF;
102
103 -- if funcmode is not null then exit
104 IF (funcmode <> wf_engine.eng_run) THEN
105 resultout := wf_engine.eng_null;
106 IF (l_Debug_Level <= 2) THEN
107 cln_debug_pub.Add('M4U:====== Exiting create_collab_setattr - Normal : resultout - ' || resultout,2);
108 END IF;
109 RETURN;
110 END IF;
111
112 l_event_key := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_EVENT_KEY',TRUE);
113 IF (l_Debug_Level <= 1) THEN
114 cln_debug_pub.Add('ECX Event Key ----'||l_event_key,1);
115 END IF;
116
117 l_ecx_parameter1 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER1',TRUE);
118 IF (l_Debug_Level <= 1) THEN
119 cln_debug_pub.Add('ECX Parameter1 ----'||l_ecx_parameter1,1);
120 END IF;
121
122 l_ecx_parameter2 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER2',TRUE);
123 IF (l_Debug_Level <= 1) THEN
124 cln_debug_pub.Add('ECX Parameter2 ----'||l_ecx_parameter2,1);
125 END IF;
126
127 l_ecx_parameter3 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER3',TRUE);
128 IF (l_Debug_Level <= 1) THEN
129 cln_debug_pub.Add('ECX Parameter3 ----'||l_ecx_parameter3,1);
130 END IF;
131
132 l_ecx_parameter4 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER4',TRUE);
136
133 IF (l_Debug_Level <= 1) THEN
134 cln_debug_pub.Add('ECX Parameter4 ----'||l_ecx_parameter4,1);
135 END IF;
137 l_ecx_parameter5 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER5',TRUE);
138 IF (l_Debug_Level <= 1) THEN
139 cln_debug_pub.Add('ECX Parameter5 ----'||l_ecx_parameter5,1);
140 END IF;
141
142 l_xmlg_transaction_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_TRANSACTION_TYPE',TRUE);
143 IF (l_Debug_Level <= 1) THEN
144 cln_debug_pub.Add('XMLG Ext Transaction Type ----'||l_xmlg_transaction_type, 1);
145 END IF;
146
147 l_xmlg_transaction_subtype := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_TRANSACTION_SUBTYPE',TRUE);
148 IF (l_Debug_Level <= 1) THEN
149 cln_debug_pub.Add('XMLG Ext Transaction Sub Type ----'||l_xmlg_transaction_subtype, 1);
150 END IF;
151
152 l_coll_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_CLN_COLL_TYPE',TRUE);
153 IF (l_Debug_Level <= 1) THEN
154 cln_debug_pub.Add('COLLABORATION TYPE ----'||l_coll_type,1);
155 END IF;
156
157 l_doc_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_CLN_DOC_TYPE',TRUE);
158 IF (l_Debug_Level <= 1) THEN
159 cln_debug_pub.Add('DOCUMENT TYPE ----'||l_doc_type,1);
160 END IF;
161
162 l_doc_owner := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_DOC_OWNER',TRUE);
163 IF (l_Debug_Level <= 1) THEN
164 cln_debug_pub.Add('DOCUMENT OWNER ----'||l_doc_owner,1);
165 END IF;
166
167 l_doc_no := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_DOC_NO',TRUE);
168 IF (l_Debug_Level <= 1) THEN
169 cln_debug_pub.Add('DOCUMENT NUMBER ----'||l_doc_no,1);
170 END IF;
171
172 l_xmlg_document_id := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_DOCUMENT_ID',TRUE);
173 IF (l_Debug_Level <= 1) THEN
174 cln_debug_pub.Add('ECX DOCUMENT ID ----'||l_xmlg_document_id,1);
175 END IF;
176
177 l_partner_doc_no := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_PARTNER_DOC_NO',TRUE);
178 IF (l_Debug_Level <= 1) THEN
179 cln_debug_pub.Add('PARTNER DOCUMENT NUMBER ----'||l_partner_doc_no,1);
180 END IF;
181
182 l_owner_role := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_OWNER_ROLE',TRUE);
183 IF (l_Debug_Level <= 1) THEN
184 cln_debug_pub.Add('OWNER ROLE ----'||l_owner_role,1);
185 END IF;
186
187 l_create_msg_text := wf_engine.GetItemAttrText(itemtype, itemkey, 'MESSAGE_TEXT',TRUE);
188 IF (l_Debug_Level <= 1) THEN
189 cln_debug_pub.Add('Message Text ----'||l_create_msg_text, 1);
190 END IF;
191
192 l_attribute1 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE1',TRUE);
193 IF (l_Debug_Level <= 1) THEN
194 cln_debug_pub.Add('ATTRIBUTE1 ----'||l_attribute1, 1);
195 END IF;
196
197 l_attribute2 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE2',TRUE);
198 IF (l_Debug_Level <= 1) THEN
199 cln_debug_pub.Add('ATTRIBUTE2 ----'||l_attribute2, 1);
200 END IF;
201
202 l_attribute3 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE3',TRUE);
203 IF (l_Debug_Level <= 1) THEN
204 cln_debug_pub.Add('ATTRIBUTE3 ----'||l_attribute3, 1);
205 END IF;
206
207 l_attribute4 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE4',TRUE);
208 IF (l_Debug_Level <= 1) THEN
209 cln_debug_pub.Add('ATTRIBUTE4 ----'||l_attribute4, 1);
210 END IF;
211
212 l_attribute5 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE5',TRUE);
213 IF (l_Debug_Level <= 1) THEN
214 cln_debug_pub.Add('ATTRIBUTE5 ----'||l_attribute5, 1);
215 END IF;
216
217 l_attribute6 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE6',TRUE);
218 IF (l_Debug_Level <= 1) THEN
219 cln_debug_pub.Add('ATTRIBUTE6 ----'||l_attribute6, 1);
220 END IF;
221
222 l_attribute7 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE7',TRUE);
223 IF (l_Debug_Level <= 1) THEN
224 cln_debug_pub.Add('ATTRIBUTE7 ----'||l_attribute7, 1);
225 END IF;
226
227 l_attribute8 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE8',TRUE);
228 IF (l_Debug_Level <= 1) THEN
229 cln_debug_pub.Add('ATTRIBUTE8 ----'||l_attribute8, 1);
230 END IF;
231
235 END IF;
232 l_attribute9 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE9',TRUE);
233 IF (l_Debug_Level <= 1) THEN
234 cln_debug_pub.Add('ATTRIBUTE9 ----'||l_attribute9, 1);
236
237 l_attribute10 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE10',TRUE);
238 IF (l_Debug_Level <= 1) THEN
239 cln_debug_pub.Add('ATTRIBUTE10 ----'||l_attribute10, 1);
240 END IF;
241
242 l_attribute11 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE11',TRUE);
243 IF (l_Debug_Level <= 1) THEN
244 cln_debug_pub.Add('ATTRIBUTE11 ----'||l_attribute11, 1);
245 END IF;
246
247 l_attribute12 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE12',TRUE);
248 IF (l_Debug_Level <= 1) THEN
249 cln_debug_pub.Add('ATTRIBUTE12 ----'||l_attribute12, 1);
250 END IF;
251
252 l_attribute13 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE13',TRUE);
253 IF (l_Debug_Level <= 1) THEN
254 cln_debug_pub.Add('ATTRIBUTE13 ----'||l_attribute13, 1);
255 END IF;
256
257 l_attribute14 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE14',TRUE);
258 IF (l_Debug_Level <= 1) THEN
259 cln_debug_pub.Add('ATTRIBUTE14 ----'||l_attribute14, 1);
260 END IF;
261
262 l_attribute15 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ATTRIBUTE15',TRUE);
263 IF (l_Debug_Level <= 1) THEN
264 cln_debug_pub.Add('ATTRIBUTE15 ----'||l_attribute15, 1);
265 END IF;
266
267 l_dattribute1 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey, 'DATTRIBUTE1',TRUE));
268 IF (l_Debug_Level <= 1) THEN
269 cln_debug_pub.Add('DATTRIBUTE1 ----'||l_dattribute1, 1);
270 END IF;
271
272 l_dattribute2 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey, 'DATTRIBUTE2',TRUE));
273 IF (l_Debug_Level <= 1) THEN
274 cln_debug_pub.Add('DATTRIBUTE2 ----'||l_dattribute2, 1);
275 END IF;
276
277 l_dattribute3 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey, 'DATTRIBUTE3',TRUE));
278 IF (l_Debug_Level <= 1) THEN
279 cln_debug_pub.Add('DATTRIBUTE3 ----'||l_dattribute3, 1);
280 END IF;
281
282 l_dattribute4 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey, 'DATTRIBUTE4',TRUE));
283 IF (l_Debug_Level <= 1) THEN
284 cln_debug_pub.Add('DATTRIBUTE4 ----'||l_dattribute4, 1);
285 END IF;
286
287 l_dattribute5 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey, 'DATTRIBUTE5',TRUE));
288 IF (l_Debug_Level <= 1) THEN
289 cln_debug_pub.Add('DATTRIBUTE5 ----'||l_dattribute5, 1);
290 END IF;
291
292 IF (l_Debug_Level <= 1) THEN
293 cln_debug_pub.Add('------------------------------------------', 1);
294 END IF;
295
296
297
298 -- set item-attributes of WF
299 IF (l_Debug_Level <= 1) THEN
300 cln_debug_pub.Add('----- Set WF Item Attributes ----- ',1);
301 END IF;
302
303 wf_engine.setItemAttrtext(
304 itemtype => itemtype,
305 itemkey => itemkey,
306 aname => 'ECX_PARTY_SITE_ID',
307 avalue => m4u_ucc_utils.g_party_site_id);
308
309 IF (l_Debug_Level <= 1) THEN
310 cln_debug_pub.Add(' Item Attribute - ECX_PARTY_SITE_ID set',1);
311 END IF;
312
313 wf_engine.setItemAttrtext(
314 itemtype => itemtype,
315 itemkey => itemkey,
316 aname => 'ECX_PARTY_ID',
317 avalue => m4u_ucc_utils.g_party_id);
318
319 IF (l_Debug_Level <= 1) THEN
320 cln_debug_pub.Add(' Item Attribute - ECX_PARTY_ID set',1);
321 END IF;
322
323 wf_engine.setItemAttrtext(
324 itemtype => itemtype,
325 itemkey => itemkey,
326 aname => 'ECX_PARTY_TYPE',
327 avalue => m4u_ucc_utils.c_party_type);
328
329 IF (l_Debug_Level <= 1) THEN
330 cln_debug_pub.Add(' Item Attribute - ECX_PARTY_TYPE set',1);
331 END IF;
332
333 wf_engine.setItemAttrtext(
334 itemtype => itemtype,
335 itemkey => itemkey,
336 aname => 'M4U_XML_VALIDATION_REQUIRED',
340 cln_debug_pub.Add(' XML Validation Reqd - ' || fnd_profile.value('M4U_XML_VALIDATION_REQUIRED'),1);
337 avalue => fnd_profile.value('M4U_XML_VALIDATION_REQUIRED'));
338
339 IF (l_Debug_Level <= 1) THEN
341 cln_debug_pub.Add('----- ----------------------------- ----- ',1);
342 END IF;
343
344 IF (l_Debug_Level <= 1) THEN
345 cln_debug_pub.Add('----- Before call to - cln_ch_collaboration_pkg.create_collaboration -----',1);
346 END IF;
347
348 cln_ch_collaboration_pkg.create_collaboration (
349 x_return_status => l_return_status,
350 x_msg_data => l_msg_data,
351 p_app_id => m4u_ucc_utils.c_resp_appl_id,
352 p_ref_id => NULL,
353 p_org_id => m4u_ucc_utils.g_org_id,
354 p_rel_no => NULL,
355 p_doc_no => l_doc_no,
356 p_doc_owner => l_doc_owner,
357 p_xmlg_int_transaction_type => NULL,
358 p_xmlg_int_transaction_subtype => NULL,
359 p_xmlg_transaction_type => l_xmlg_transaction_type,
360 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
361 p_xmlg_document_id => l_xmlg_document_id,
362 p_coll_type => l_coll_type,
363 p_tr_partner_type => m4u_ucc_utils.c_party_type,
364 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
365 p_doc_creation_date => sysdate,
366 p_doc_revision_date => sysdate,
367 p_init_date => sysdate,
368 p_doc_type => l_doc_type,
369 p_doc_dir => 'OUT',
370 p_coll_pt => 'APPS',
371 p_xmlg_msg_id => NULL,
372 p_unique1 => l_unique1,
373 p_unique2 => l_unique2,
374 p_unique3 => l_unique3,
375 p_unique4 => l_unique4,
376 p_unique5 => l_unique5,
377 p_rosettanet_check_required => FALSE,
378 x_coll_id => l_coll_id,
379 p_msg_text => l_create_msg_text,
380 p_xml_event_key => itemkey,
381 p_attribute1 => l_attribute1,
382 p_attribute2 => l_attribute2,
383 p_attribute3 => l_attribute3,
384 p_attribute4 => l_attribute4,
385 p_attribute5 => l_attribute5,
386 p_attribute6 => l_attribute6,
387 p_attribute7 => l_attribute7,
388 p_attribute8 => l_attribute8,
389 p_attribute9 => l_attribute9,
390 p_attribute10 => l_attribute10,
391 p_attribute11 => l_attribute11,
392 p_attribute12 => l_attribute12,
393 p_attribute13 => l_attribute13,
394 p_attribute14 => l_attribute14,
395 p_attribute15 => l_attribute15,
396 p_partner_doc_no => l_partner_doc_no,
397 p_collaboration_standard => 'UCCNET',
398 p_owner_role => l_owner_role);
399
400 IF (l_Debug_Level <= 1) THEN
401 cln_debug_pub.add('Create collaboration returned values',1);
402 cln_debug_pub.add('Collab_id - ' || l_coll_id,1);
403 cln_debug_pub.add('x_return_status - ' || l_return_status,1);
404 cln_debug_pub.add('x_msg_data - ' || l_msg_data,1);
405 END IF;
406
407
408 IF l_return_status = 'S' THEN
409 resultout := wf_engine.eng_completed || ':SUCCESS';
410 l_truncated_key := SUBSTR(l_event_key,1,10);
411
412 IF (l_Debug_Level <= 1) THEN
413 cln_debug_pub.add('Truncated Key Value - '||l_truncated_key,1);
414 END IF;
415
416 IF (l_truncated_key = 'M4U_EGOEVT') THEN
420 p_subbatch_id => l_ecx_parameter4,
417 ego_uccnet_events_pub.set_collaboration_id(
418 p_api_version => 1.0,
419 p_batch_id => l_ecx_parameter2 ,
421 p_top_gtin => l_doc_no,
422 p_cln_id => l_coll_id,
423 x_return_status => l_return_status,
424 x_msg_count => l_msg_count,
425 x_msg_data => l_msg_data);
426
427 IF (l_Debug_Level <= 1) THEN
428 cln_debug_pub.Add('ego_uccnet_events_pub.set_collaboration_id - ', 1);
429 cln_debug_pub.Add(' l_return_status - '|| l_return_status, 1);
430 cln_debug_pub.Add(' l_msg_data - '|| l_msg_data, 1);
431 cln_debug_pub.Add(' l_msg_count - '|| l_msg_count, 1);
432 END IF;
433 END IF;
434 ELSE
435 resultout := wf_engine.eng_completed || ':FAIL';
436
437 FND_MESSAGE.SET_NAME('CLN','M4U_CREATE_COLL_FAILURE');
438 /* 'Create Collaboration Failed. Details are
439 Colllaboration type : COLLTYPE
440 Document type : DOCTYPE
441 Event Key : EVTKEY
442 Item Type : ITMTYPE
443 Error Code : ERRCODE
444 Failure Reason : MSG
445 */
446
447 l_fnd_msg := FND_MESSAGE.GET;
448 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg );
449 RETURN;
450 END IF;
451
452 wf_engine.setItemAttrtext(
453 itemtype => itemtype,
454 itemkey => itemkey,
455 aname => 'ECX_PARAMETER1',
456 avalue => l_coll_id);
457
458 IF (l_Debug_Level <= 1) THEN
459 cln_debug_pub.Add('ECX_PARAMETER1 set to collaboration id value :- ' || l_coll_id,1);
460 END IF;
461
462 IF (l_Debug_Level <= 2) THEN
463 cln_debug_pub.Add('====== Exiting m4u_xml_genprocess_out.create_collab_setattr - Normal : resultout-' || resultout,2);
464 END IF;
465
466 RETURN;
467
468 -- Exception Handling
469 EXCEPTION
470
471 WHEN OTHERS THEN
472 l_error_code := SQLCODE;
473 l_error_msg :='Workflow - ' || itemtype || '/' || itemkey || ' - ' || SQLERRM;
474
475 FND_MESSAGE.SET_NAME('CLN','M4U_CREATE_COLL_FAILURE');
476 /* 'Create Collaboration Failed. Details are
477 Colllaboration type : COLLTYPE
478 Document type : DOCTYPE
479 Event Key : EVTKEY
480 Item Type : ITMTYPE
481 Error Code : ERRCODE
482 Failure Reason : MSG
483 */
484
485 l_fnd_msg := FND_MESSAGE.GET;
486 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg );
487
488 IF (l_Debug_Level <= 5) THEN
489 cln_debug_pub.Add('Unexpected Error -'||l_error_code||' : '||l_error_msg,5);
490 END IF;
491
492 IF (l_Debug_Level <= 2) THEN
493 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.create_collab_setattr - Exception =========== ',2);
494 END IF;
495
496 wf_Core.context('m4u_xml_genprocess_out','create_collab_setattr', itemtype, itemkey, to_char(actid), funcmode);
497 RAISE;
498 END;
499
500
501
502 -- Name
503 -- update_collab_setattr
504 -- Purpose
505 --
506 -- Arguments
507 -- itemtype => WF item type
508 -- itemkey => WF item key
509 -- actid => WF act id
510 -- funcmode => WF func mode
511 -- resultout => result param
512 -- Notes
513 -- None.
514 PROCEDURE update_collab_setattr(
515 itemtype IN VARCHAR2,
516 itemkey IN VARCHAR2,
517 actid IN NUMBER,
518 funcmode IN VARCHAR2,
519 resultout IN OUT NOCOPY VARCHAR2
520 )
521 IS
522
523 l_fnd_msg_key VARCHAR2(50);
524 l_coll_status VARCHAR2(50);
525 l_doc_status VARCHAR2(50);
526 l_disposition VARCHAR2(50);
527 l_event_key VARCHAR2(50);
528
529 l_xmlg_message_id VARCHAR2(100);
530 l_xmlg_transaction_type VARCHAR2(100);
531 l_xmlg_transaction_subtype VARCHAR2(100);
532 l_coll_type VARCHAR2(100);
533 l_doc_type VARCHAR2(100);
534
535 l_xmlg_document_id VARCHAR2(256);
536 l_debug_mode VARCHAR2(255);
537 l_return_status VARCHAR2(255);
538 l_doc_no VARCHAR2(255);
539 l_wf_error_type VARCHAR2(255);
540 l_fnd_msg VARCHAR2(255);
541
542 l_msg_data VARCHAR2(2000);
543 l_msg_text VARCHAR2(2000);
544 l_error_msg VARCHAR2(2000);
545 l_wf_error_msg VARCHAR2(2000);
546
547 l_coll_pt VARCHAR2(20);
548
549 l_tr_partner_id VARCHAR2(30);
550 l_tr_partner_site VARCHAR2(30);
551 l_unique1 VARCHAR2(30);
552 l_unique2 VARCHAR2(30);
553 l_unique3 VARCHAR2(30);
554 l_unique4 VARCHAR2(30);
555 l_unique5 VARCHAR2(30);
556
557 l_msg_count VARCHAR2(100);
558 l_attribute1 VARCHAR2(150);
559 l_attribute2 VARCHAR2(150);
560 l_attribute3 VARCHAR2(150);
561 l_attribute4 VARCHAR2(150);
562 l_attribute5 VARCHAR2(150);
563 l_attribute6 VARCHAR2(150);
564 l_attribute7 VARCHAR2(150);
565 l_attribute8 VARCHAR2(150);
566 l_attribute9 VARCHAR2(150);
567 l_attribute10 VARCHAR2(150);
568 l_attribute11 VARCHAR2(150);
569 l_attribute12 VARCHAR2(150);
570 l_attribute13 VARCHAR2(150);
571 l_attribute14 VARCHAR2(150);
572 l_attribute15 VARCHAR2(150);
573
574 l_ecx_parameter1 VARCHAR2(150);
575 l_ecx_parameter2 VARCHAR2(150);
576 l_ecx_parameter3 VARCHAR2(150);
577 l_ecx_parameter4 VARCHAR2(150);
578 l_ecx_parameter5 VARCHAR2(150);
579
580
581 l_error_code NUMBER;
582 l_coll_id NUMBER;
583 l_coll_dtl_id NUMBER;
584
585
586 l_dattribute1 DATE;
587 l_dattribute2 DATE;
588 l_dattribute3 DATE;
589 l_dattribute4 DATE;
590 l_dattribute5 DATE;
591
592 BEGIN
593
594 IF (l_debug_Level <= 2) THEN
595 cln_debug_pub.Add('========= Entering update_collab_setattr == ',2);
596 END IF;
597
598 -- Parameters received
599 IF (l_Debug_Level <= 1) THEN
600 cln_debug_pub.Add('-- Parameters received from Workflow process -- ',1);
601 cln_debug_pub.Add('itemtype - '||itemtype, 1);
602 cln_debug_pub.Add('itemkey - '||itemkey, 1);
603 cln_debug_pub.Add('actid - '||actid, 1);
604 cln_debug_pub.Add('funcmode - '||funcmode, 1);
605 cln_debug_pub.Add('---------------------------------------------',1);
606 END IF;
607
608 -- if funcmode is not null then exit
609 IF (funcmode <> wf_engine.eng_run) THEN
610 resultout := wf_engine.eng_null;
611 IF (l_Debug_Level <= 2) THEN
612 cln_debug_pub.Add('M4U:====== Exiting update_collab_setattr - Normal : resultout - ' || resultout,2);
613 END IF;
614 RETURN;
615 END IF;
616
617 l_event_key := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_EVENT_KEY',TRUE);
618 IF (l_Debug_Level <= 1) THEN
619 cln_debug_pub.Add('Ecx Event Key ----'||l_event_key,1);
620 END IF;
621
622 -- ECX Parameter1 contains the collaboration ID
623 l_ecx_parameter1 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER1',TRUE);
624 IF (l_Debug_Level <= 1) THEN
625 cln_debug_pub.Add('ECX Parameter1 ----'||l_ecx_parameter1,1);
626 END IF;
627
628 l_ecx_parameter2 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER2',TRUE);
629 IF (l_Debug_Level <= 1) THEN
630 cln_debug_pub.Add('ECX Parameter2 ----'||l_ecx_parameter2,1);
631 END IF;
632
633 l_ecx_parameter3 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER3',TRUE);
634 IF (l_Debug_Level <= 1) THEN
635 cln_debug_pub.Add('ECX Parameter3 ----'||l_ecx_parameter3,1);
636 END IF;
637
638 l_ecx_parameter4 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER4',TRUE);
639 IF (l_Debug_Level <= 1) THEN
640 cln_debug_pub.Add('ECX Parameter4 ----'||l_ecx_parameter4,1);
641 END IF;
642
643 l_ecx_parameter5 := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER5',TRUE);
644 IF (l_Debug_Level <= 1) THEN
645 cln_debug_pub.Add('ECX Parameter5 ----'||l_ecx_parameter5,1);
646 END IF;
647
648 l_coll_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_CLN_COLL_TYPE',TRUE);
649 IF (l_Debug_Level <= 1) THEN
650 cln_debug_pub.Add('COLLABORATION TYPE ----'||l_coll_type,1);
651 END IF;
652
653 l_doc_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_CLN_DOC_TYPE',TRUE);
654 IF (l_Debug_Level <= 1) THEN
655 cln_debug_pub.Add('DOCUMENT TYPE ----'||l_doc_type,1);
656 END IF;
657
658 l_xmlg_transaction_type := wf_engine.GetItemAttrText(itemtype, itemkey,'ECX_TRANSACTION_TYPE',TRUE);
659 IF (l_Debug_Level <= 1) THEN
660 cln_debug_pub.Add('ECX Transaction Type ----'||l_xmlg_transaction_type, 1);
661 END IF;
662
663 l_xmlg_transaction_subtype := wf_engine.GetItemAttrText(itemtype, itemkey,'ECX_TRANSACTION_SUBTYPE',TRUE);
664 IF (l_Debug_Level <= 1) THEN
665 cln_debug_pub.Add('ECX Transaction Sub Type ----'||l_xmlg_transaction_subtype, 1);
666 END IF;
667
668 l_msg_text := wf_engine.GetItemAttrText(itemtype, itemkey,'MESSAGE_TEXT',TRUE);
669 IF (l_Debug_Level <= 1) THEN
670 cln_debug_pub.Add('Message Text ----'||l_msg_text, 1);
671 END IF;
672
673 l_attribute1 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE1',TRUE);
674 IF (l_Debug_Level <= 1) THEN
675 cln_debug_pub.Add('ATTRIBUTE1 ----'||l_attribute1, 1);
676 END IF;
677
678 l_attribute2 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE2',TRUE);
679 IF (l_Debug_Level <= 1) THEN
680 cln_debug_pub.Add('ATTRIBUTE2 ----'||l_attribute2, 1);
681 END IF;
682
683 l_attribute3 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE3',TRUE);
684 IF (l_Debug_Level <= 1) THEN
685 cln_debug_pub.Add('ATTRIBUTE3 ----'||l_attribute3, 1);
686 END IF;
687
688 l_attribute4 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE4',TRUE);
689 IF (l_Debug_Level <= 1) THEN
690 cln_debug_pub.Add('ATTRIBUTE4 ----'||l_attribute4, 1);
691 END IF;
692
696 END IF;
693 l_attribute5 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE5',TRUE);
694 IF (l_Debug_Level <= 1) THEN
695 cln_debug_pub.Add('ATTRIBUTE5 ----'||l_attribute5, 1);
697
698 l_attribute6 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE6',TRUE);
699 IF (l_Debug_Level <= 1) THEN
700 cln_debug_pub.Add('ATTRIBUTE6 ----'||l_attribute6, 1);
701 END IF;
702
703 l_attribute7 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE7',TRUE);
704 IF (l_Debug_Level <= 1) THEN
705 cln_debug_pub.Add('ATTRIBUTE7 ----'||l_attribute7, 1);
706 END IF;
707
708 l_attribute8 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE8',TRUE);
709 IF (l_Debug_Level <= 1) THEN
710 cln_debug_pub.Add('ATTRIBUTE8 ----'||l_attribute8, 1);
711 END IF;
712
713 l_attribute9 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE9',TRUE);
714 IF (l_Debug_Level <= 1) THEN
715 cln_debug_pub.Add('ATTRIBUTE9 ----'||l_attribute9, 1);
716 END IF;
717
718 l_attribute10 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE10',TRUE);
719 IF (l_Debug_Level <= 1) THEN
720 cln_debug_pub.Add('ATTRIBUTE10 ----'||l_attribute10, 1);
721 END IF;
722
723 l_attribute11 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE11',TRUE);
724 IF (l_Debug_Level <= 1) THEN
725 cln_debug_pub.Add('ATTRIBUTE11 ----'||l_attribute11, 1);
726 END IF;
727
728 l_attribute12 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE12',TRUE);
729 IF (l_Debug_Level <= 1) THEN
730 cln_debug_pub.Add('ATTRIBUTE12 ----'||l_attribute12, 1);
731 END IF;
732
733 l_attribute13 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE13',TRUE);
734 IF (l_Debug_Level <= 1) THEN
735 cln_debug_pub.Add('ATTRIBUTE13 ----'||l_attribute13, 1);
736 END IF;
737
738 l_attribute14 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE14',TRUE);
739 IF (l_Debug_Level <= 1) THEN
740 cln_debug_pub.Add('ATTRIBUTE14 ----'||l_attribute14, 1);
741 END IF;
742
743 l_attribute15 := wf_engine.GetItemAttrText(itemtype, itemkey,'ATTRIBUTE15',TRUE);
744 IF (l_Debug_Level <= 1) THEN
745 cln_debug_pub.Add('ATTRIBUTE15 ----'||l_attribute15, 1);
746 END IF;
747
748 l_dattribute1 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey,'DATTRIBUTE1',TRUE));
749 IF (l_Debug_Level <= 1) THEN
750 cln_debug_pub.Add('DATTRIBUTE1 ----'||l_dattribute1, 1);
751 END IF;
752
753 l_dattribute2 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey,'DATTRIBUTE2',TRUE));
754 IF (l_Debug_Level <= 1) THEN
755 cln_debug_pub.Add('DATTRIBUTE2 ----'||l_dattribute2, 1);
756 END IF;
757
758 l_dattribute3 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey,'DATTRIBUTE3',TRUE));
759 IF (l_Debug_Level <= 1) THEN
760 cln_debug_pub.Add('DATTRIBUTE3 ----'||l_dattribute3, 1);
761 END IF;
762
763 l_dattribute4 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey,'DATTRIBUTE4',TRUE));
764 IF (l_Debug_Level <= 1) THEN
765 cln_debug_pub.Add('DATTRIBUTE4 ----'||l_dattribute4, 1);
766 END IF;
767
768 l_dattribute5 := m4u_ucc_utils.CONVERT_TO_DATE(wf_engine.GetItemAttrText(itemtype, itemkey,'DATTRIBUTE5',TRUE));
769 IF (l_Debug_Level <= 1) THEN
770 cln_debug_pub.Add('DATTRIBUTE5 ----'||l_dattribute5, 1);
771 END IF;
772
773 l_xmlg_message_id := wf_engine.GetItemAttrText(itemtype, itemkey,'ECX_MSGID_ATTR',TRUE);
774 IF (l_debug_Level <= 1) THEN
775 cln_debug_pub.Add('XMLG Message ID ----' || l_xmlg_message_id,1);
776 END IF;
777
778 l_xmlg_document_id := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_DOCUMENT_ID',TRUE);
779 IF (l_debug_Level <= 1) THEN
780 cln_debug_pub.Add('XMLG Document ID ----' || l_xmlg_document_id,1);
781 END IF;
782
783 -- This attribute when filled is used for add messages screen
784 l_wf_error_msg := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_ERROR_MSG',TRUE );
785 IF (l_debug_Level <= 1) THEN
786 cln_debug_pub.Add('WF Error Msg ----' || l_wf_error_msg,1);
787 END IF;
788
789 -- This attribute is used for puttg up prefix to generic msgs that can be used
790 -- in the collaboration event details screen
794 END IF;
791 l_fnd_msg_key := wf_engine.GetActivityAttrText(itemtype, itemkey, actid,'M4U_FND_MSG',TRUE);
792 IF (l_debug_Level <= 1) THEN
793 cln_debug_pub.Add('Message Prefix ----' || l_fnd_msg_key,1);
795
796 -- check if error type is set at Activity level
797 -- else check at item level, doing this because some of the WF activities involving the
798 -- java files do not set item_attribute M4U_ERROR_TYPE. This is specific to different Updates
799 l_wf_error_type := wf_engine.GetActivityAttrText(itemtype, itemkey,actid, 'M4U_ERROR_TYPE',TRUE);
800 if(l_wf_error_type IS NULL) THEN
801 l_wf_error_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_ERROR_TYPE',TRUE);
802 END IF;
803
804 IF (l_debug_Level <= 1) THEN
805 cln_debug_pub.Add('l_wf_error_type ----' || l_wf_error_type,1);
806 END IF;
807
808 IF (l_Debug_Level <= 1) THEN
809 cln_debug_pub.Add('------------------------------------------', 1);
810 END IF;
811
812 -- set CLN Disposition, CLN Coll Status, CLN Doc status, CLN Message Text
813 IF ( l_wf_error_type is NULL) THEN
814 l_disposition := 'PENDING' ;
815 l_fnd_msg_key := l_fnd_msg_key || '_SUCCESS';
816 l_coll_status := 'INITIATED';
817 l_doc_status := 'SUCCESS';
818 ELSE
819 l_disposition := 'REJECTED' ;
820 l_fnd_msg_key := l_fnd_msg_key || '_FAILURE';
821 l_coll_status := 'ERROR';
822 l_doc_status := 'ERROR';
823
824 wf_engine.setItemAttrtext(
825 itemtype => itemtype,
826 itemkey => itemkey,
827 aname => 'M4U_ERROR_FLAG',
828 avalue => 'Y');
829
830 IF (l_debug_Level <= 1) THEN
831 cln_debug_pub.Add('M4U_ERROR_FLAG - Y',1);
832 END IF;
833 END IF;
834
835 CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
836 x_return_status => l_return_status,
837 x_msg_data => l_msg_data,
838 p_coll_id => l_ecx_parameter1,
839 p_xmlg_transaction_type => l_xmlg_transaction_type,
840 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
841 p_xmlg_document_id => l_xmlg_document_id,
842 p_disposition => l_disposition,
843 p_coll_status => l_coll_status,
844 p_doc_type => l_doc_type,
845 p_doc_dir => 'OUT',
846 p_coll_pt => l_coll_pt,
847 p_doc_status => l_doc_status,
848 p_notification_id => NULL,
849 p_msg_text => l_fnd_msg_key,
850 p_xmlg_msg_id => l_xmlg_message_id,
851 p_unique1 => l_unique1,
852 p_unique2 => l_unique2,
853 p_unique3 => l_unique3,
854 p_unique4 => l_unique4,
855 p_unique5 => l_unique5,
856 p_tr_partner_type => m4u_ucc_utils.c_party_type,
857 p_tr_partner_id => m4u_ucc_utils.g_party_id,
858 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
859 p_rosettanet_check_required => FALSE,
860 x_dtl_coll_id => l_coll_dtl_id
861 );
862
863 IF (l_Debug_Level <= 1) THEN
864 cln_debug_pub.add('collaboration history updated for cln_id - ' || l_coll_id,1);
865 cln_debug_pub.add('Collab_detail_id - ' || l_coll_dtl_id,1);
866 cln_debug_pub.add('x_return_status - ' || l_return_status,1);
867 cln_debug_pub.add('x_msg_data - ' || l_msg_data,1);
868 END IF;
869
870
871 IF l_return_status <> 'S' THEN
872 resultout := wf_engine.eng_completed || ':FAIL';
873
874 FND_MESSAGE.SET_NAME('CLN','M4U_UPDATE_COLL_FAILURE');
875 /* 'Update Collaboration Failed. Details are
876 Collaboration type : COLLTYPE
877 Collaboration Id : COLLID
878 Document type : DOCTYPE
879 Event Key : EVTKEY
880 Item Type : ITMTYPE
881 Error Code : ERRCODE
882 Failure Reason : MSG
883 */
884 FND_MESSAGE.SET_TOKEN('COLL_TYPE',l_coll_type);
885 FND_MESSAGE.SET_TOKEN('COLL_ID',l_ecx_parameter1);
886 FND_MESSAGE.SET_TOKEN('DOC_TYPE',l_doc_type);
890
887 FND_MESSAGE.SET_TOKEN('ITEM_TYPE',itemtype);
888 FND_MESSAGE.SET_TOKEN('EVT_KEY',itemkey);
889 FND_MESSAGE.SET_TOKEN('FAILURE_REASON',l_msg_data);
891 l_fnd_msg := FND_MESSAGE.GET;
892
893 wf_engine.setItemAttrtext(
894 itemtype => itemtype,
895 itemkey => itemkey,
896 aname => 'M4U_ERROR_FLAG',
897 avalue => 'Y');
898
899 IF (l_debug_Level <= 1) THEN
900 cln_debug_pub.Add('M4U_ERROR_FLAG - Y',1);
901 END IF;
902
903 -- Notify the administrator
904 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg);
905
906 IF (l_debug_Level <= 1) THEN
907 cln_debug_pub.Add('return from call to Notify_Administrator',1);
908 END IF;
909
910 IF (l_doc_type = 'M4U_CIN' OR l_doc_type = 'M4U_RCIR' OR l_doc_type = 'M4U_RCIR_BATCH') THEN
911 ego_uccnet_events_pub.update_event_disposition(
912 p_api_version => 1.0,
913 p_cln_id => l_ecx_parameter1,
914 p_disposition_code => 'FAILED',
915 p_disposition_date => sysdate,
916 x_return_status => l_return_status,
917 x_msg_count => l_msg_count,
918 x_msg_data => l_msg_data );
919
920 IF (l_Debug_Level <= 1) THEN
921 cln_debug_pub.Add('ego_uccnet_events_pub.update_event_disposition - returns', 1);
922 cln_debug_pub.Add(' l_return_status - '|| l_return_status, 1);
923 cln_debug_pub.Add(' l_msg_data - '|| l_msg_data, 1);
924 cln_debug_pub.Add(' l_msg_count - '|| l_msg_count, 1);
925 END IF;
926
927 IF (l_Debug_Level <= 2) THEN
928 cln_debug_pub.Add('====== Exiting m4u_xml_genprocess_out.update_cln_collaborations - Normal : resultout-' || resultout,2);
929 END IF;
930 RETURN; -- Something wrong, simply notify admin and bailout, ...
931 END IF;
932 END IF;
933
934
935 IF l_wf_error_msg IS NOT NULL THEN
936 IF (l_Debug_Level <= 1) THEN
937 cln_debug_pub.add('adding error detail to dtl_coll_id - ' || l_coll_dtl_id,1);
938 cln_debug_pub.add('message - ' || l_wf_error_msg,1);
939 END IF;
940
941
942 IF (l_doc_type = 'M4U_CIN' OR l_doc_type = 'M4U_RCIR' OR l_doc_type = 'M4U_RCIR_BATCH') THEN
943 ego_uccnet_events_pub.update_event_disposition(
944 p_api_version => 1.0,
945 p_cln_id => l_ecx_parameter1,
946 p_disposition_code => 'FAILED',
947 p_disposition_date => sysdate,
948 x_return_status => l_return_status,
949 x_msg_count => l_msg_count,
950 x_msg_data => l_msg_data );
951
952 IF (l_Debug_Level <= 1) THEN
953 cln_debug_pub.Add('ego_uccnet_events_pub.update_event_disposition - returns', 1);
954 cln_debug_pub.Add(' l_return_status - '|| l_return_status, 1);
955 cln_debug_pub.Add(' l_msg_data - '|| l_msg_data, 1);
956 cln_debug_pub.Add(' l_msg_count - '|| l_msg_count, 1);
957 END IF;
958 END IF;
959
960
961 cln_ch_collaboration_pkg.add_collaboration_messages
962 (
963 x_return_status => l_return_status,
964 x_msg_data => l_msg_data,
965 p_dtl_coll_id => l_coll_dtl_id,
966 p_dtl_msg => l_wf_error_msg
967 );
968
969 IF (l_Debug_Level <= 1) THEN
970 cln_debug_pub.add('error detail added to dtl_coll_id - ' || l_coll_dtl_id,1);
971 cln_debug_pub.add('x_return_status - ' || l_return_status,1);
972 cln_debug_pub.add('x_msg_data - ' || l_msg_data,1);
973 END IF;
974
975 IF l_return_status <> 'S' THEN
976 FND_MESSAGE.SET_NAME('CLN','M4U_UPDATE_COLL_FAILURE');
977 /* 'Update Collaboration Failed. Details are
978 Collaboration type : COLLTYPE
979 Collaboration Id : COLLID
983 Error Code : ERRCODE
980 Document type : DOCTYPE
981 Event Key : EVTKEY
982 Item Type : ITMTYPE
984 Failure Reason : MSG
985 */
986
987 FND_MESSAGE.SET_TOKEN('COLL_TYPE',l_coll_type);
988 FND_MESSAGE.SET_TOKEN('COLL_ID',l_ecx_parameter1);
989 FND_MESSAGE.SET_TOKEN('DOC_TYPE',l_doc_type);
990 FND_MESSAGE.SET_TOKEN('ITEM_TYPE',itemtype);
991 FND_MESSAGE.SET_TOKEN('EVT_KEY',itemkey);
992 FND_MESSAGE.SET_TOKEN('FAILURE_REASON',l_msg_data);
993
994
995 l_fnd_msg := FND_MESSAGE.GET;
996
997 wf_engine.setItemAttrtext(
998 itemtype => itemtype,
999 itemkey => itemkey,
1000 aname => 'M4U_ERROR_FLAG',
1001 avalue => 'Y');
1002
1003 IF (l_debug_Level <= 1) THEN
1004 cln_debug_pub.Add('M4U_ERROR_FLAG - set to Y',1);
1005 END IF;
1006
1007 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg);
1008 IF (l_debug_Level <= 1) THEN
1009 cln_debug_pub.Add('returning from call to Notifiy_Administrator',1);
1010 END IF;
1011 END IF;
1012 END IF;
1013
1014 -- this nullfies the value stored in the message for update collaboration
1015 wf_engine.setItemAttrtext(
1016 itemtype => itemtype,
1017 itemkey => itemkey,
1018 aname => 'M4U_ERROR_TYPE',
1019 avalue => NULL);
1020
1021 IF (l_debug_Level <= 1) THEN
1022 cln_debug_pub.Add('M4U_ERROR_TYPE set to NULL',1);
1023 END IF;
1024
1025 -- this nullfies the value stored in the message for add messages table
1026 wf_engine.setItemAttrtext(
1027 itemtype => itemtype,
1028 itemkey => itemkey,
1029 aname => 'M4U_ERROR_MSG',
1030 avalue => NULL);
1031
1032 IF (l_debug_Level <= 1) THEN
1033 cln_debug_pub.Add('M4U_ERROR_MSG set to NULL',1);
1034 END IF;
1035
1036 resultout := wf_engine.eng_completed;
1037
1038 IF (l_Debug_Level <= 2) THEN
1039 cln_debug_pub.Add('====== Exiting m4u_xml_genprocess_out.update_collab_setattr - Normal : resultout-' || resultout,2);
1040 END IF;
1041
1042 RETURN;
1043 -- Exception Handling
1044 EXCEPTION
1045
1046 WHEN OTHERS THEN
1047 l_error_code :=SQLCODE;
1048 l_error_msg :='Workflow - ' || itemtype || '/' || itemkey || ' - ' || SQLERRM;
1049
1050 FND_MESSAGE.SET_NAME('CLN','M4U_UPDATE_COLL_FAILURE');
1051
1052 FND_MESSAGE.SET_TOKEN('COLL_TYPE',l_coll_type);
1053 FND_MESSAGE.SET_TOKEN('COLL_ID',l_ecx_parameter1);
1054 FND_MESSAGE.SET_TOKEN('DOC_TYPE',l_doc_type);
1055 FND_MESSAGE.SET_TOKEN('ITEM_TYPE',itemtype);
1056 FND_MESSAGE.SET_TOKEN('EVT_KEY',itemkey);
1057 FND_MESSAGE.SET_TOKEN('FAILURE_REASON',l_error_msg);
1058
1059 l_fnd_msg := FND_MESSAGE.GET;
1060
1061 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg);
1062
1063 IF (l_Debug_Level <= 5) THEN
1064 cln_debug_pub.Add('Unexpected Error -'||l_error_code||' : '||l_error_msg,5);
1065 END IF;
1066
1067 IF (l_Debug_Level <= 2) THEN
1068 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.update_collab_setattr - Exception =========== ',2);
1069 END IF;
1070
1071 wf_Core.context('m4u_xml_genprocess_out','update_collab_setattr',
1072 itemtype, itemkey, to_char(actid), funcmode);
1073 RAISE;
1074 END;
1075
1076
1077 -- Name
1078 -- create CLN collaborations in batch mode
1079 -- Purpose
1080 -- Creates multiple CLN collaborations for each item-event in the group.
1081 -- returns failure even if one of the collab is not created successfully.
1082 -- updates ego_uccnet_events, with the CLN id.
1083 -- this CLN id, is used as the command level unique-identifier.
1084 -- Arguments
1085 -- itemtype => WF item type
1086 -- itemkey => WF item key
1087 -- actid => WF act id
1088 -- funcmode => WF func mode
1089 -- resultout => result param
1090 -- Notes
1091 -- None.
1092
1093 PROCEDURE create_batchcollab_setattr(
1094 itemtype IN VARCHAR2,
1098 resultout IN OUT NOCOPY VARCHAR2
1095 itemkey IN VARCHAR2,
1096 actid IN NUMBER,
1097 funcmode IN VARCHAR2,
1099 )
1100 IS
1101 x_coll_id NUMBER;
1102 l_gtin_count NUMBER;
1103 l_ego_batch_id NUMBER;
1104 l_ego_subbatch_id NUMBER;
1105 l_org_id NUMBER;
1106
1107 x_return_status VARCHAR2(10);
1108
1109 l_tp_gln VARCHAR2(50);
1110 l_target_market VARCHAR2(50);
1111 l_error_code VARCHAR2(50);
1112 l_owner_role VARCHAR2(50);
1113
1114 x_msg_count VARCHAR2(100);
1115 l_xmlg_transaction_subtype VARCHAR2(100);
1116 l_xmlg_transaction_type VARCHAR2(100);
1117 l_xmlg_document_id VARCHAR2(100);
1118 l_ecx_msg_id VARCHAR2(100);
1119 l_coll_type VARCHAR2(100);
1120 l_doc_type VARCHAR2(100);
1121
1122 l_error_msg VARCHAR2(255);
1123 l_fnd_msg VARCHAR2(2000);
1124 x_msg_data VARCHAR2(4000);
1125
1126 l_event_type ego_uccnet_events.event_type%TYPE;
1127 l_event_action ego_uccnet_events.event_action%TYPE;
1128 l_gtin ego_uccnet_events.gtin%TYPE;
1129 l_top_gtin ego_uccnet_events.top_gtin%TYPE;
1130 l_doc_owner ego_uccnet_events.last_updated_by%TYPE;
1131 l_item_number mtl_system_items_kfv.concatenated_segments%TYPE;
1132
1133
1134 CURSOR c_gtinInBatch (p_batchid NUMBER, p_subbatch_id NUMBER, p_event_type VARCHAR2)
1135 IS
1136 SELECT e.event_action, e.gtin, e.top_gtin,
1137 e.last_updated_by,e.target_market, e.tp_gln,
1138 e.organization_id, f.user_name, mtlkfv.concatenated_segments
1139 FROM ego_uccnet_events e,
1140 fnd_user f,
1141 mtl_system_items_kfv mtlkfv
1142 WHERE e.batch_id = p_batchid
1143 AND e.subbatch_id = p_subbatch_id
1144 AND e.gtin = e.top_gtin
1145 AND e.event_type = p_event_type
1146 AND e.INVENTORY_ITEM_ID = mtlkfv.INVENTORY_ITEM_ID
1147 AND e.ORGANIZATION_ID = mtlkfv.ORGANIZATION_ID
1148 AND e.last_updated_by = f.user_id(+);
1149
1150 /*
1151 For publications, this will give the TOP level GTINs only.
1152 need to check with if this is true for REGISTRATION GTIN
1153 as well, that is GTIN=TOP_GTIN
1154 */
1155 BEGIN
1156 IF (l_debug_Level <= 2) THEN
1157 cln_debug_pub.Add('========= Entering create_batchcollab_setattr == ',2);
1158 END IF;
1159
1160 -- Parameters received
1161 IF (l_Debug_Level <= 1) THEN
1162 cln_debug_pub.Add('-- Parameters received from Workflow process -- ',1);
1163 cln_debug_pub.Add('itemtype - '||itemtype, 1);
1164 cln_debug_pub.Add('itemkey - '||itemkey, 1);
1165 cln_debug_pub.Add('actid - '||actid, 1);
1166 cln_debug_pub.Add('funcmode - '||funcmode, 1);
1167 cln_debug_pub.Add('---------------------------------------------',1);
1168 END IF;
1169
1170 -- if funcmode is not null then exit
1171 IF (funcmode <> wf_engine.eng_run) THEN
1172 resultout := wf_engine.eng_null;
1173 IF (l_Debug_Level <= 2) THEN
1174 cln_debug_pub.Add('M4U:====== Exiting create_batchcollab_setattr - Normal : resultout - ' || resultout,2);
1175 END IF;
1176 RETURN;
1177 END IF;
1178
1179 -- Pramaters obtained with the event from EGO
1180 l_xmlg_transaction_type := wf_engine.GetItemAttrText(itemtype, itemkey,'ECX_TRANSACTION_TYPE',TRUE);
1181 IF (l_Debug_Level <= 1) THEN
1182 cln_debug_pub.Add('ECX Transaction Type ----'||l_xmlg_transaction_type, 1);
1183 END IF;
1184
1185 l_xmlg_transaction_subtype := wf_engine.GetItemAttrText(itemtype, itemkey,'ECX_TRANSACTION_SUBTYPE',TRUE);
1186 IF (l_Debug_Level <= 1) THEN
1187 cln_debug_pub.Add('ECX Transaction Sub Type ----'||l_xmlg_transaction_subtype, 1);
1188 END IF;
1189
1190 l_ego_batch_id := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER2',TRUE);
1191 IF (l_Debug_Level <= 1) THEN
1192 cln_debug_pub.Add('EGO batch id ----'|| l_ego_batch_id , 1);
1193 END IF;
1194
1195 l_ego_subbatch_id := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER1',TRUE);
1196 IF (l_Debug_Level <= 1) THEN
1200 l_event_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER3',TRUE);
1197 cln_debug_pub.Add('EGO subbatch id ----'|| l_ego_subbatch_id , 1);
1198 END IF;
1199
1201 IF (l_Debug_Level <= 1) THEN
1202 cln_debug_pub.Add('Event Type ----'|| l_event_type , 1);
1203 END IF;
1204
1205 l_coll_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_CLN_COLL_TYPE',TRUE);
1206 IF (l_Debug_Level <= 1) THEN
1207 cln_debug_pub.Add('Collaboration Type ----'|| l_coll_type , 1);
1208 END IF;
1209
1210 l_doc_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_CLN_DOC_TYPE',TRUE);
1211 IF (l_Debug_Level <= 1) THEN
1212 cln_debug_pub.Add('DOCUMENT TYPE ----'||l_doc_type,1);
1213 END IF;
1214
1215
1216 l_xmlg_document_id := l_ego_batch_id || ':' || l_ego_subbatch_id;
1217
1218 IF (l_Debug_Level <= 1) THEN
1219 cln_debug_pub.Add('ECX document id ----'|| l_xmlg_document_id, 1);
1220 END IF;
1221
1222
1223 -- set item-attributes of WF
1224 IF (l_Debug_Level <= 1) THEN
1225 cln_debug_pub.Add('----- Set WF Item Attributes ----- ',1);
1226 END IF;
1227
1228 wf_engine.setItemAttrtext(
1229 itemtype => itemtype,
1230 itemkey => itemkey,
1231 aname => 'ECX_PARTY_SITE_ID',
1232 avalue => m4u_ucc_utils.g_party_site_id);
1233
1234 IF (l_Debug_Level <= 1) THEN
1235 cln_debug_pub.Add(' Item Attribute - ECX_PARTY_SITE_ID set',1);
1236 END IF;
1237
1238 wf_engine.setItemAttrtext(
1239 itemtype => itemtype,
1240 itemkey => itemkey,
1241 aname => 'ECX_PARTY_ID',
1242 avalue => m4u_ucc_utils.g_party_id);
1243
1244 IF (l_Debug_Level <= 1) THEN
1245 cln_debug_pub.Add(' Item Attribute - ECX_PARTY_ID set',1);
1246 END IF;
1247
1248 wf_engine.setItemAttrtext(
1249 itemtype => itemtype,
1250 itemkey => itemkey,
1251 aname => 'ECX_PARTY_TYPE',
1252 avalue => m4u_ucc_utils.c_party_type);
1253
1254 IF (l_Debug_Level <= 1) THEN
1255 cln_debug_pub.Add(' Item Attribute - ECX_PARTY_TYPE set',1);
1256 END IF;
1257
1258 wf_engine.setItemAttrtext(
1259 itemtype => itemtype,
1260 itemkey => itemkey,
1261 aname => 'M4U_XML_VALIDATION_REQUIRED',
1262 avalue => fnd_profile.value('M4U_XML_VALIDATION_REQUIRED'));
1263
1264 IF (l_Debug_Level <= 1) THEN
1265 cln_debug_pub.Add(' XML Validation Reqd - ' || fnd_profile.value('M4U_XML_VALIDATION_REQUIRED'),1);
1266 END IF;
1267
1268
1269
1270
1271
1272 /* look at the ego_uccnet_events table and set these values */
1273 /* get the values for the wf and cln */
1274 OPEN c_gtinInBatch(l_ego_batch_id,NVL(l_ego_subbatch_id,1),l_event_type );
1275
1276 LOOP
1277 FETCH c_gtinInBatch
1278 INTO l_event_action,l_gtin,l_top_gtin,l_doc_owner,
1279 l_target_market,l_tp_gln, l_org_id,l_owner_role, l_item_number;
1280
1281 EXIT WHEN c_gtinInBatch%NOTFOUND;
1282
1283 IF (l_Debug_Level <= 1) THEN
1284 cln_debug_pub.Add(' in the loop w/ l_gtin - '|| l_gtin , 1);
1285 cln_debug_pub.Add(' l_event_action - '|| l_event_action , 1);
1286 cln_debug_pub.Add(' l_gtin - '|| l_gtin , 1);
1287 cln_debug_pub.Add(' l_top_gtin - '|| l_top_gtin , 1);
1288 cln_debug_pub.Add(' l_doc_owner - '|| l_doc_owner , 1);
1289 cln_debug_pub.Add(' l_target_market - '|| l_target_market , 1);
1290 cln_debug_pub.Add(' l_tp_gln - '|| l_tp_gln , 1);
1291 cln_debug_pub.Add(' l_org_id - '|| l_org_id , 1);
1292 cln_debug_pub.Add(' l_item_number - '|| l_item_number , 1);
1293 cln_debug_pub.Add(' l_owner_role - '|| l_owner_role , 1);
1294 END IF;
1295
1296
1297
1298 /* Now set the values needed for CLN create collaboration event all the
1299 values are based on ECX transaction type need to derive values for CLN
1300 API and set WF attributes for XMLG Generate document routine.
1301 */
1302 -- here the doc type is hardcoded to M4U_RCIR which would need a change
1306 itemtype => itemtype,
1303 l_coll_type := 'M4U_RCIR'||'_'||l_event_action;
1304
1305 wf_engine.setItemAttrtext(
1307 itemkey => itemkey,
1308 aname => 'M4U_CLN_COLL_TYPE',
1309 avalue => l_coll_type);
1310
1311 IF (l_Debug_Level <= 1) THEN
1312 cln_debug_pub.Add('M4U_CLN_DOC_TYPE - ' || l_doc_type, 1);
1313 END IF;
1314
1315 wf_engine.setItemAttrtext(
1316 itemtype => itemtype,
1317 itemkey => itemkey,
1318 aname => 'ECX_DOCUMENT_ID',
1319 avalue => l_xmlg_document_id);
1320
1321 IF (l_Debug_Level <= 1) THEN
1322 cln_debug_pub.Add('ECX_DOCUMENT_ID - ' || l_xmlg_document_id, 1);
1323 END IF;
1324
1325
1326 /* create CLN collaboration and update the ego_uccnet_events with CLN-id*/
1327 /* this CLN-d will be used as command level identifier in the map */
1328 cln_ch_collaboration_pkg.create_collaboration(
1329 x_return_status => x_return_status,
1330 x_msg_data => x_msg_data,
1331 p_app_id => m4u_ucc_utils.c_resp_appl_id,
1332 p_ref_id => NULL,
1333 p_org_id => m4u_ucc_utils.g_org_id,
1334 p_rel_no => NULL,
1335 p_doc_no => l_gtin,
1336 p_doc_owner => l_doc_owner,
1337 p_xmlg_int_transaction_type => l_xmlg_transaction_type,
1338 p_xmlg_int_transaction_subtype=> l_xmlg_transaction_subtype,
1339 p_xmlg_transaction_type => l_xmlg_transaction_type,
1340 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
1341 p_xmlg_document_id => l_xmlg_document_id,
1342 p_coll_type => l_coll_type,
1343 p_tr_partner_type => m4u_ucc_utils.c_party_type,
1344 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
1345 p_doc_creation_date => sysdate,
1346 p_doc_revision_date => sysdate,
1347 p_init_date => sysdate,
1348 p_doc_type => l_doc_type,
1349 p_doc_dir => 'OUT',
1350 p_coll_pt => 'APPS',
1351 p_xmlg_msg_id => NULL,
1352 p_rosettanet_check_required => FALSE,
1353 x_coll_id => x_coll_id,
1354 p_msg_text => 'M4U_REGISTRATION_INITIATED',
1355 p_xml_event_key => itemkey,
1356 p_attribute1 => m4u_ucc_utils.g_host_gln,
1357 p_attribute2 => l_target_market,
1358 p_attribute3 => l_ego_batch_id,
1359 p_attribute4 => l_ego_subbatch_id,
1360 p_attribute5 => m4u_ucc_utils.g_supp_gln,
1361 p_attribute6 => l_tp_gln,
1362 p_attribute12 => l_xmlg_document_id,
1363 p_partner_doc_no => l_item_number,
1364 p_collaboration_standard => 'UCCNET',
1365 p_owner_role => l_owner_role);
1366
1367 IF (l_Debug_Level <= 1) THEN
1368 cln_debug_pub.Add('create collaboration returns - ', 1);
1369 cln_debug_pub.Add(' x_return_status - '|| x_return_status, 1);
1370 cln_debug_pub.Add(' x_msg_data - '|| x_msg_data, 1);
1371 cln_debug_pub.Add(' x_coll_id - '|| x_coll_id, 1);
1372 END IF;
1373
1374 IF (x_return_status ='S') THEN
1375
1376 ego_uccnet_events_pub.set_collaboration_id(
1377 p_api_version => 1.0,
1378 p_batch_id => l_ego_batch_id ,
1379 p_subbatch_id => l_ego_subbatch_id,
1380 p_top_gtin => l_gtin,
1381 p_cln_id => x_coll_id,
1382 x_return_status => x_return_status,
1383 x_msg_count => x_msg_count,
1384 x_msg_data => x_msg_data);
1385
1386 IF (l_Debug_Level <= 1) THEN
1387 cln_debug_pub.Add('ego_uccnet_events_pub.set_collaboration_id - ', 1);
1388 cln_debug_pub.Add(' x_return_status - '|| x_return_status, 1);
1392
1389 cln_debug_pub.Add(' x_msg_data - '|| x_msg_data, 1);
1390 cln_debug_pub.Add(' x_msg_count - '|| x_msg_count, 1);
1391 END IF;
1393 ELSE
1394 resultout := wf_engine.eng_completed || ':FAIL';
1395
1396 wf_engine.setItemAttrtext(
1397 itemtype => itemtype,
1398 itemkey => itemkey,
1399 aname => 'M4U_ERROR_FLAG',
1400 avalue => 'Y');
1401
1402 IF (l_Debug_Level <= 1) THEN
1403 cln_debug_pub.Add('M4U_ERROR_FLAG set to Y', 1);
1404 END IF;
1405
1406 FND_MESSAGE.SET_NAME('CLN','M4U_OUT_CREATE_COLL_FAILURE');
1407 l_fnd_msg := FND_MESSAGE.GET;
1408
1409 x_msg_data := 'Workflow - ' || itemtype || '/' || itemkey || ' - ' || x_msg_data;
1410
1411 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg
1412 || ', Error code - '
1413 || x_return_status
1414 || ', Error message - '
1415 || x_msg_data);
1416
1417 IF (l_Debug_Level <= 2) THEN
1418 cln_debug_pub.Add('EXITING m4u_xml_genprocess_out.create_cln_collaborations FAILURE',2);
1419 END IF;
1420 RETURN;
1421 END IF;
1422
1423 END LOOP;
1424
1425 IF (l_debug_Level <= 2) THEN
1426 cln_debug_pub.Add('========= Exiting create_batchcollab_setattr == ',2);
1427 END IF;
1428
1429 resultout := wf_engine.eng_completed||':SUCCESS';
1430
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 l_error_code :=SQLCODE;
1434 l_error_msg :=' Workflow - ' || itemtype || '/' || itemkey || ' - ' || SQLERRM;
1435
1436 FND_MESSAGE.SET_NAME('CLN','M4U_OUT_CREATE_COLL_FAILURE');
1437 x_msg_data := FND_MESSAGE.GET;
1438
1439 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data
1440 || ', Error code - '
1441 || l_error_code
1442 || ', Error message - '
1443 || l_error_msg);
1444
1445
1446 IF (l_Debug_Level <= 5) THEN
1447 cln_debug_pub.Add('Error : ' || SQLCODE || ':' || SQLERRM, 5);
1448 cln_debug_pub.Add('x_msg_data : ' || x_msg_data,5);
1449 END IF;
1450 IF (l_Debug_Level <= 2) THEN
1451 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.create_cln_collaborations - Exception =========== ',2);
1452 END IF;
1453
1454 Wf_Core.Context('m4u_xml_genprocess_out','create_cln_collaborations',
1455 itemtype, itemkey, to_char(actid), funcmode);
1456 RAISE;
1457 END;
1458
1459
1460 -- Name
1461 -- Update_cln_collaborations
1462 -- Purpose
1463 -- update the collaboration with
1464 -- i) ECXMSGID after message generation
1465 -- ii)Any error/ progress information
1466 -- Arguments
1467 -- itemtype => WF item type
1468 -- itemkey => WF item key
1469 -- actid => WF act id
1470 -- funcmode => WF func mode
1471 -- resultout => result param
1472 -- Notes
1473 -- None.
1474
1475
1476 PROCEDURE update_batchcollab_setattr(
1477 itemtype IN VARCHAR2,
1478 itemkey IN VARCHAR2,
1479 actid IN NUMBER,
1480 funcmode IN VARCHAR2,
1481 resultout IN OUT NOCOPY VARCHAR2
1482 )
1483 IS
1484 x_return_status VARCHAR2(10);
1485 x_msg_data VARCHAR2(4000);
1486 x_msg_count VARCHAR2(100);
1487 l_ego_batch_id NUMBER;
1488 l_ego_subbatch_id NUMBER;
1489 l_xmlg_transaction_subtype VARCHAR2(100);
1490 l_xmlg_transaction_type VARCHAR2(100);
1491 l_ecx_partysite_id VARCHAR2(30);
1492 l_ecx_party_type VARCHAR2(10);
1493 l_ecx_doc_id VARCHAR2(30);
1494 l_dtl_coll_id NUMBER;
1495 l_ecx_msgid VARCHAR2(100);
1496 l_wf_error_msg VARCHAR2(4000);
1497 l_wf_error_type VARCHAR2(100);
1498 l_wf_error_flag VARCHAR2(20);
1499 l_fnd_msg_key VARCHAR2(100);
1500 l_disposition VARCHAR2(100);
1501 l_coll_status VARCHAR2(100);
1502 l_doc_status VARCHAR2(100);
1503 l_error_code VARCHAR2(50);
1504 l_cln_doc_type VARCHAR2(50);
1505 l_cln_coll_type VARCHAR2(50);
1506 l_error_msg VARCHAR2(2000);
1507
1508 /*CURSOR c_clnidForBatch (p_batchid VARCHAR2,p_subbatch_id VARCHAR2, p_coll_doc_type VARCHAR2) IS
1509 SELECT distinct hdr.collaboration_id
1510 FROM cln_coll_hist_hdr hdr, cln_coll_hist_dtl dtl
1511 WHERE hdr.attribute3 = p_batchid
1512 AND hdr.attribute4 = p_subbatch_id
1513 AND COLLABORATION_DOCUMENT_TYPE = p_coll_doc_type
1514 AND hdr.collaboration_id = dtl.collaboration_id
1515 AND hdr.xmlg_transaction_type = 'M4U';*/
1516
1517 CURSOR c_clnidForBatch (p_batchid VARCHAR2,p_subbatch_id VARCHAR2, p_coll_type VARCHAR2) IS
1518 SELECT collaboration_id
1519 FROM cln_coll_hist_hdr
1520 WHERE attribute3 = p_batchid
1521 AND attribute4 = p_subbatch_id
1522 AND collaboration_type = p_coll_type
1523 AND xmlg_transaction_type = 'M4U';
1524 BEGIN
1525 IF (l_debug_Level <= 2) THEN
1526 cln_debug_pub.Add('========= Entering update_batchcollab_setattr == ',2);
1527 END IF;
1528
1529
1530 IF (l_Debug_Level <= 1) THEN
1531 cln_debug_pub.Add('Paramters received are' , 2);
1532 cln_debug_pub.Add('itemtype - ' || itemtype , 2);
1533 cln_debug_pub.Add('itemkey - ' || itemkey , 2);
1534 cln_debug_pub.Add('actid - ' || actid , 2);
1535 cln_debug_pub.Add('funcmode - ' || funcmode , 2);
1536 END IF;
1537
1538
1539 -- if funcmode is not null then exit
1540 IF (funcmode <> wf_engine.eng_run) THEN
1541 resultout := wf_engine.eng_null;
1542 IF (l_Debug_Level <= 2) THEN
1543 cln_debug_pub.Add('====== Exiting m4u_xml_genprocess_out.update_cln_collaborations - Normal : resultout-' || resultout,2);
1544 END IF;
1545 RETURN;
1546 END IF;
1547
1548 l_ego_batch_id := wf_engine.GetItemAttrText(itemtype,itemkey,'ECX_PARAMETER2',TRUE);
1549
1550 IF (l_Debug_Level <= 1) THEN
1551 cln_debug_pub.Add('WF Attributes retreived are', 1);
1552 cln_debug_pub.Add('l_ego_batch_id ----'|| l_ego_batch_id , 1);
1553 END IF;
1554
1555
1556 l_ego_subbatch_id := wf_engine.GetItemAttrText(itemtype,itemkey,'ECX_PARAMETER4',TRUE);
1557
1558 IF (l_Debug_Level <= 1) THEN
1559 cln_debug_pub.Add('l_ego_subbatch_id ----'|| l_ego_subbatch_id , 1);
1560 END IF;
1561
1562 l_xmlg_transaction_type := wf_engine.GetItemAttrText(itemtype,itemkey,'ECX_TRANSACTION_TYPE',TRUE);
1563
1564 IF (l_Debug_Level <= 1) THEN
1565 cln_debug_pub.Add('ECX l_xmlg_transaction_type ----'|| l_xmlg_transaction_type , 1);
1566 END IF;
1567
1568 l_xmlg_transaction_subtype := wf_engine.GetItemAttrText(itemtype,itemkey,'ECX_TRANSACTION_SUBTYPE',TRUE);
1569
1570 IF (l_Debug_Level <= 1) THEN
1571 cln_debug_pub.Add('ECX l_xmlg_transaction_subtype ----'|| l_xmlg_transaction_subtype , 1);
1572 END IF;
1573
1574 l_ecx_doc_id := wf_engine.GetItemAttrText(itemtype,itemkey,'ECX_DOCUMENT_ID', TRUE);
1575
1576 IF (l_Debug_Level <= 1) THEN
1577 cln_debug_pub.Add('ECX l_ecx_doc_id ----'|| l_ecx_doc_id , 1);
1578 END IF;
1579
1580 l_ecx_msgid := wf_engine.GetItemAttrText(itemtype,itemkey,'ECX_MSGID_ATTR', TRUE);
1581
1582 IF (l_Debug_Level <= 1) THEN
1583 cln_debug_pub.Add('ECX l_ecx_msgid ----'|| l_ecx_msgid, 1);
1584 END IF;
1585
1586 /* following values will be available only if there is schema validation or adapter issues */
1587 l_wf_error_msg := wf_engine.GetItemAttrText(itemtype,itemkey,'M4U_ERROR_MSG',TRUE);
1588
1589 IF (l_Debug_Level <= 1) THEN
1590 cln_debug_pub.Add('l_wf_error_msg ----'|| l_wf_error_msg, 1);
1591 END IF;
1592
1593 l_wf_error_flag := wf_engine.GetItemAttrText(itemtype,itemkey,'M4U_ERROR_FLAG',TRUE);
1594
1595 IF (l_Debug_Level <= 1) THEN
1596 cln_debug_pub.Add('l_wf_error_flag ----'|| l_wf_error_flag, 1);
1597 END IF;
1598
1599 l_fnd_msg_key := wf_engine.GetActivityAttrText(itemtype, itemkey,actid,'M4U_FND_MSG',TRUE);
1600
1601 IF (l_Debug_Level <= 1) THEN
1602 cln_debug_pub.Add('l_fnd_msg_key ----'|| l_fnd_msg_key , 1);
1603 END IF;
1604
1605 l_cln_doc_type := wf_engine.GetItemAttrText(itemtype,itemkey,'M4U_CLN_DOC_TYPE',TRUE);
1606
1607 IF (l_Debug_Level <= 1) THEN
1608 cln_debug_pub.Add('l_cln_doc_type ----'|| l_cln_doc_type , 1);
1609 END IF;
1610
1611 l_cln_coll_type := wf_engine.GetItemAttrText(itemtype,itemkey,'M4U_CLN_COLL_TYPE',TRUE);
1612
1613 IF (l_Debug_Level <= 1) THEN
1614 cln_debug_pub.Add('l_cln_coll_type ----'|| l_cln_coll_type , 1);
1615 END IF;
1616
1617 -- check if error type is set at Activity level
1618 -- else check at item level, doing this because some of the WF activities,
1619 -- do not set item_attribute M4U_ERROR_TYPE
1620 -- so setting Activity Attribute in the WF in those cases.
1621 l_wf_error_type := wf_engine.GetActivityAttrText(itemtype, itemkey,actid, 'M4U_ERROR_TYPE',TRUE);
1622
1623 if(l_wf_error_type IS NULL) THEN
1624 l_wf_error_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_ERROR_TYPE',TRUE);
1625 END IF;
1626
1627 IF (l_Debug_Level <= 1) THEN
1628 cln_debug_pub.Add('l_wf_error_type ----'|| l_wf_error_type , 1);
1629 END IF;
1630
1631
1632 -- set CLN Disposition, CLN Coll Status, CLN Doc status, CLN Message Text
1633 IF ( l_wf_error_type is NULL AND l_wf_error_flag <> 'Y' ) THEN
1634 l_disposition := 'PENDING' ;
1635 l_fnd_msg_key := l_fnd_msg_key || '_SUCCESS';
1636 l_coll_status := 'INITIATED';
1637 l_doc_status := 'SUCCESS';
1638 ELSE
1639 l_disposition := 'REJECTED' ;
1640 l_fnd_msg_key := l_fnd_msg_key || '_FAILURE';
1641 l_coll_status := 'ERROR';
1642 l_doc_status := 'ERROR';
1643
1644 wf_engine.setItemAttrtext(
1645 itemtype => itemtype,
1646 itemkey => itemkey,
1647 aname => 'M4U_ERROR_FLAG',
1648 avalue => 'Y');
1649
1650 IF (l_Debug_Level <= 1) THEN
1651 cln_debug_pub.Add('M4U_ERROR_FLAG set to Y', 1);
1652 END IF;
1653 END IF;
1654
1655 --for each collaboration in the batch,
1656 --update message and add any error to Error information
1657 FOR rec_cln_ids IN c_clnidForBatch(l_ego_batch_id,l_ego_subbatch_id,l_cln_coll_type)
1661 x_return_status => x_return_status,
1658 LOOP
1659
1660 cln_ch_collaboration_pkg.update_collaboration(
1662 x_msg_data => x_msg_data,
1663 p_coll_id => rec_cln_ids.collaboration_id,
1664 p_msg_text => l_fnd_msg_key,
1665 p_xmlg_msg_id => l_ecx_msgid,
1666 p_xmlg_transaction_type => l_xmlg_transaction_type,
1667 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype ,
1668 p_xmlg_int_transaction_type => l_xmlg_transaction_type,
1669 p_xmlg_int_transaction_subtype => l_xmlg_transaction_subtype ,
1670 p_doc_dir => 'OUT',
1671 p_doc_type => l_cln_doc_type,
1672 p_disposition => l_disposition,
1673 p_doc_status => l_doc_status,
1674 p_coll_status => l_coll_status,
1675 p_xmlg_document_id => l_ecx_doc_id,
1676 p_tr_partner_type => m4u_ucc_utils.c_party_type,
1677 p_tr_partner_id => m4u_ucc_utils.g_party_id,
1678 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
1679 p_rosettanet_check_required => false,
1680 x_dtl_coll_id => l_dtl_coll_id );
1681
1682 IF (l_Debug_Level <= 1) THEN
1683 cln_debug_pub.add('collaboration history updated for cln_id - ' || rec_cln_ids.collaboration_id,1);
1684 cln_debug_pub.add('collab_detail_id - ' || l_dtl_coll_id,1);
1685 cln_debug_pub.add('x_return_status - ' || x_return_status,1);
1686 cln_debug_pub.add('x_msg_data - ' || x_msg_data,1);
1687 END IF;
1688
1689 IF x_return_status <> 'S' THEN
1690 FND_MESSAGE.SET_NAME('CLN','M4U_OUT_UPDATE_COLL_FAILURE');
1691 l_fnd_msg_key := FND_MESSAGE.GET;
1692
1693 wf_engine.setItemAttrtext(
1694 itemtype => itemtype,
1695 itemkey => itemkey,
1696 aname => 'M4U_ERROR_FLAG',
1697 avalue => 'Y');
1698
1699 IF (l_Debug_Level <= 1) THEN
1700 cln_debug_pub.Add('M4U_ERROR_FLAG set to Y', 1);
1701 END IF;
1702
1703 resultout := wf_engine.eng_completed || ':FAIL';
1704 x_msg_data := 'Workflow - ' || itemtype || '/' || itemkey || ' - ' || x_msg_data;
1705
1706 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR
1707 ( l_fnd_msg_key
1708 || ', Error code - '
1709 || x_return_status
1710 || ', Error message - '
1711 || x_msg_data);
1712 IF (l_Debug_Level <= 1) THEN
1713 cln_debug_pub.Add('returned from call to NOTIFY_ADMINISTRATOR', 1);
1714 END IF;
1715
1716 ego_uccnet_events_pub.update_event_disposition(
1717 p_api_version => 1.0,
1718 p_cln_id => rec_cln_ids.collaboration_id,
1719 p_disposition_code => 'FAILED',
1720 p_disposition_date => sysdate,
1721 x_return_status => x_return_status,
1722 x_msg_count => x_msg_count,
1723 x_msg_data => x_msg_data );
1724
1725 IF (l_Debug_Level <= 1) THEN
1726 cln_debug_pub.Add('ego_uccnet_events_pub.update_event_disposition - returns', 1);
1727 cln_debug_pub.Add(' x_return_status - '|| x_return_status, 1);
1728 cln_debug_pub.Add(' x_msg_data - '|| x_msg_data, 1);
1729 cln_debug_pub.Add(' x_msg_count - '|| x_msg_count, 1);
1730 END IF;
1731
1732 IF (l_Debug_Level <= 2) THEN
1733 cln_debug_pub.Add('====== Exiting m4u_xml_genprocess_out.update_cln_collaborations - Normal : resultout-' || resultout,2);
1734 END IF;
1735 RETURN; -- Something wrong, simply notify admin and bailout, ...
1736 END IF;
1737
1738 IF l_wf_error_msg IS NOT NULL THEN
1739
1740 IF (l_Debug_Level <= 1) THEN
1741 cln_debug_pub.add('adding error detail to dtl_coll_id - ' || l_dtl_coll_id,1);
1742 cln_debug_pub.add('message - ' || l_wf_error_msg,1);
1743 END IF;
1744
1745 cln_ch_collaboration_pkg.add_collaboration_messages
1746 (
1747 x_return_status => x_return_status,
1748 x_msg_data => x_msg_data,
1749 p_dtl_coll_id => l_dtl_coll_id,
1750 p_dtl_msg => l_wf_error_type || ':' || l_wf_error_msg
1751 );
1752
1753 IF (l_Debug_Level <= 1) THEN
1754 cln_debug_pub.add('error detail added to dtl_coll_id - ' || l_dtl_coll_id,1);
1755 cln_debug_pub.add('x_return_status - ' || x_return_status,1);
1756 cln_debug_pub.add('x_msg_data - ' || x_msg_data,1);
1757 END IF;
1758
1759 ego_uccnet_events_pub.update_event_disposition(
1760 p_api_version => 1.0,
1761 p_cln_id => rec_cln_ids.collaboration_id,
1762 p_disposition_code => 'FAILED',
1763 p_disposition_date => sysdate,
1764 x_return_status => x_return_status,
1765 x_msg_count => x_msg_count,
1766 x_msg_data => x_msg_data );
1767
1768 IF (l_Debug_Level <= 1) THEN
1769 cln_debug_pub.Add('ego_uccnet_events_pub.update_event_disposition - returns', 1);
1770 cln_debug_pub.Add(' x_return_status - '|| x_return_status, 1);
1771 cln_debug_pub.Add(' x_msg_data - '|| x_msg_data, 1);
1772 cln_debug_pub.Add(' x_msg_count - '|| x_msg_count, 1);
1773 END IF;
1774
1775 END IF;
1776 END LOOP;
1777
1778 wf_engine.setItemAttrtext(
1779 itemtype => itemtype,
1780 itemkey => itemkey,
1781 aname => 'M4U_ERROR_TYPE',
1782 avalue => NULL);
1783
1784 IF (l_Debug_Level <= 1) THEN
1785 cln_debug_pub.Add('M4U_ERROR_TYPE set to NULL', 1);
1786 END IF;
1787
1788 wf_engine.setItemAttrtext(
1789 itemtype => itemtype,
1790 itemkey => itemkey,
1791 aname => 'M4U_ERROR_MSG',
1792 avalue => NULL);
1793
1794 IF (l_Debug_Level <= 1) THEN
1795 cln_debug_pub.Add('M4U_ERROR_MSG set to NULL', 1);
1796 END IF;
1797
1798 resultout := wf_engine.eng_completed || ':SUCCESS';
1799
1800 IF (l_Debug_Level <= 1) THEN
1801 cln_debug_pub.Add('==========Exiting update_batchcollab_setattr ======', 2);
1802 END IF;
1803
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 l_error_code :=SQLCODE;
1807 l_error_msg :=' Workflow - ' || itemtype || '/' || itemkey || ' - ' || SQLERRM;
1808
1809 FND_MESSAGE.SET_NAME('CLN','M4U_OUT_UPDATE_COLL_FAILURE');
1810 l_fnd_msg_key := FND_MESSAGE.GET;
1811
1812 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(
1813 l_fnd_msg_key
1814 || ', Error code - '
1815 || l_error_code
1816 || ', Error message - '
1817 || l_error_msg);
1818
1819 IF (l_Debug_Level <= 5) THEN
1823 IF (l_Debug_Level <= 2) THEN
1820 cln_debug_pub.Add('Error '|| l_error_code || ':' || l_error_msg, 5);
1821 END IF;
1822
1824 cln_debug_pub.Add('======EXITING m4u_xml_genprocess_out.update_cln_collaborations exception', 2);
1825 END IF;
1826
1827 wf_Core.context('m4u_wlq_generate','wlq_update_cln_collab',
1828 itemtype, itemkey, to_char(actid), funcmode);
1829 RAISE;
1830 END;
1831
1832 /* set the delivery method, AS2 or direct http or Error*/
1833 -- Name
1834 -- set_aq_correlation
1835 -- Purpose
1836 -- sets the PROTOCOL_TYPE event attribute in the ECX_EVENT_MESSAGE item attribute
1837 -- This is in-turn used to set AQ correlation-id by the queue-handler
1838 -- Arguments
1839 -- itemtype => WF item type
1840 -- itemkey => WF item key
1841 -- actid => WF act id
1842 -- funcmode => WF func mode
1843 -- resultout => result param
1844 -- Notes
1845 -- none
1846 PROCEDURE set_aq_correlation(
1847 itemtype IN VARCHAR2,
1848 itemkey IN VARCHAR2,
1849 actid IN NUMBER,
1850 funcmode IN VARCHAR2,
1851 resultout IN OUT NOCOPY VARCHAR2)
1852 IS
1853 l_param_list wf_parameter_list_t;
1854 l_param wf_parameter_t;
1855 l_event wf_event_t;
1856 l_http_correlation_id VARCHAR2(20);
1857 l_as2_correlation_id VARCHAR2(20);
1858 l_error_correlation_id VARCHAR2(20);
1859 l_correlation_id VARCHAR2(20);
1860 l_error_type VARCHAR2(50);
1861 l_protocol_profile_value VARCHAR2(1);
1862 l_error_code VARCHAR2(50);
1863 l_error_msg VARCHAR2(255);
1864 l_fnd_msg VARCHAR2(255);
1865 BEGIN
1866 l_http_correlation_id := 'UCC:HTTP';
1867 l_as2_correlation_id := 'UCC:AS2';
1868 l_error_correlation_id := 'UCC:ERROR';
1869
1870 IF (l_Debug_Level <= 2) THEN
1871 cln_debug_pub.Add('=========== Entering m4u_xml_genprocess_out.set_aq_correlation =========== ',2);
1872 END IF;
1873
1874 IF (l_Debug_Level <= 1) THEN
1875 cln_debug_pub.Add('Paramters received are' , 2);
1876 cln_debug_pub.Add('itemtype - ' || itemtype , 2);
1877 cln_debug_pub.Add('itemkey - ' || itemkey , 2);
1878 cln_debug_pub.Add('actid - ' || actid , 2);
1879 cln_debug_pub.Add('funcmode - ' || funcmode , 2);
1880 END IF;
1881
1882
1883
1884 -- if funcmode is not null then exit
1885 IF (funcmode <> wf_engine.eng_run) THEN
1886
1887 IF (l_Debug_Level <= 1) THEN
1888 cln_debug_pub.Add('l_error_type - ' || l_error_type,1);
1889 END IF;
1890
1891 resultout := wf_engine.eng_null;
1892 IF (l_Debug_Level <= 2) THEN
1893 cln_debug_pub.Add('====== Exiting m4u_xml_genprocess_out.set_aq_correlation - Normal : resultout-' || resultout,2);
1894 END IF;
1895 RETURN;
1896 END IF;
1897
1898
1899 l_error_type := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_ERROR_FLAG',TRUE);
1900
1901
1902 IF (l_Debug_Level <= 1) THEN
1903 cln_debug_pub.Add('l_error_type - ' || l_error_type,1);
1904 END IF;
1905
1906 IF (l_error_type = 'Y') THEN
1907 l_correlation_id := l_error_correlation_id;
1908 ELSE
1909 IF UPPER(fnd_profile.value('M4U_USE_HTTP_ADAPTER')) = 'Y' THEN
1910 l_correlation_id := l_http_correlation_id;
1911 ELSE
1912 l_correlation_id := l_as2_correlation_id;
1913 END IF;
1914 END IF;
1915
1916 IF (l_Debug_Level <= 1) THEN
1917 cln_debug_pub.Add('l_correlation_id - ' || l_correlation_id,1);
1918 END IF;
1919
1920 BEGIN
1921 l_event := wf_engine.GetItemAttrEvent(itemtype, itemkey, 'ECX_EVENT_MESSAGE');
1922 EXCEPTION
1923 WHEN others THEN
1924 NULL;
1925 END;
1926
1927 IF (l_event is null ) THEN
1928 IF (l_Debug_Level <= 1) THEN
1929 cln_debug_pub.Add('ECX_EVENT_MESSAGE is null',1);
1930 END IF;
1931 wf_core.token('ECX_EVENT_MESSAGE','NULL');
1932 wf_core.raise('WFSQL_ARGS');
1933 END IF;
1934
1935 l_param_list := l_event.getParameterList();
1936
1937 FOR p_count in 1..l_param_list.COUNT LOOP
1938 IF (l_param_list(p_count).getName() = 'PROTOCOL_TYPE') THEN
1939 l_param_list(p_count).setValue(l_correlation_id);
1943 EXIT;
1940 IF (l_Debug_Level <= 1) THEN
1941 cln_debug_pub.Add('setting ECX_EVENT_MESSAGE.PROTOCOL_TYPE - ' || l_correlation_id,1);
1942 END IF;
1944 END IF;
1945 END LOOP;
1946
1947 l_event.setParameterList(l_param_list);
1948 wf_engine.SetItemAttrEvent(itemtype, itemkey, 'ECX_EVENT_MESSAGE', l_event);
1949
1950 IF (l_Debug_Level <= 1) THEN
1951 cln_debug_pub.Add('ECX_EVENT_MESSAGE initialised',1);
1952 END IF;
1953
1954 wf_engine.SetItemAttrText(itemtype,itemkey, 'M4U_SEND_MODE',l_correlation_id);
1955
1956 IF (l_Debug_Level <= 1) THEN
1957 cln_debug_pub.Add('M4U_SEND_MODE - ' || l_correlation_id,1);
1958 END IF;
1959
1960
1961 /* Y or N value if Y use the UCCnet adapter */
1962 resultout := wf_engine.eng_completed;
1963
1964 IF (l_Debug_Level <= 2) THEN
1965 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.set_aq_correlation =========== ',2);
1966 END IF;
1967
1968
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 l_error_code :=SQLCODE;
1972 l_error_msg :=' Workflow - ' || itemtype || '/' || itemkey || ' - ' || SQLERRM;
1973
1974 FND_MESSAGE.SET_NAME('CLN','M4U_UNEXPECTED_ERROR');
1975 l_fnd_msg := FND_MESSAGE.GET;
1976
1977 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg
1978 || ' in m4u_xml_genprocess_out.Set_AQ_Correlation , Error code - '
1979 || l_error_code
1980 || ', Error message - '
1981 || l_error_msg);
1982
1983
1984 IF (l_Debug_Level <= 5) THEN
1985 cln_debug_pub.Add('Error : ' || l_error_code || ':' || l_error_msg, 5);
1986 END IF;
1987 IF (l_Debug_Level <= 2) THEN
1988 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.set_aq_correlation - Exception =========== ',2);
1989 END IF;
1990
1991 Wf_Core.Context('m4u_xml_genprocess_out','set_aq_correlation',
1992 itemtype, itemkey, to_char(actid), funcmode);
1993 RAISE;
1994 END;
1995
1996
1997 -- Name
1998 -- check_send_method
1999 -- Purpose
2000 -- returns send_method to be used
2001 -- Arguments
2002 -- itemtype => WF item type
2003 -- itemkey => WF item key
2004 -- actid => WF act id
2005 -- funcmode => WF func mode
2006 -- resultout => result param
2007 -- Notes
2008 -- None.
2009 PROCEDURE check_send_method(
2010 itemtype IN VARCHAR2,
2011 itemkey IN VARCHAR2,
2012 actid IN NUMBER,
2013 funcmode IN VARCHAR2,
2014 resultout IN OUT NOCOPY VARCHAR2)
2015 IS
2016 l_send_method VARCHAR2(50);
2017 l_error_code VARCHAR2(50);
2018 l_error_msg VARCHAR2(255);
2019 l_fnd_msg VARCHAR2(255);
2020 BEGIN
2021 IF (l_Debug_Level <= 2) THEN
2022 cln_debug_pub.Add('=========== Entering m4u_xml_genprocess_out.check_send_method =========== ',2);
2023 END IF;
2024
2025 IF (l_Debug_Level <= 1) THEN
2026 cln_debug_pub.Add('Paramters received are' , 2);
2027 cln_debug_pub.Add('itemtype - ' || itemtype , 2);
2028 cln_debug_pub.Add('itemkey - ' || itemkey , 2);
2029 cln_debug_pub.Add('actid - ' || actid , 2);
2030 cln_debug_pub.Add('funcmode - ' || funcmode , 2);
2031 END IF;
2032
2033 -- Do nothing in cancel or timeout mode
2034 IF (funcmode <> wf_engine.eng_run) THEN
2035 resultout := wf_engine.eng_null;
2036 IF (l_Debug_Level <= 2) THEN
2037 cln_debug_pub.Add('==========Exiting m4u_xml_genprocess_out.check_send_method ======', 2);
2038 END IF;
2039 RETURN;
2040 END IF;
2041
2042 /* Y or N depending on whether validation is required */
2043 l_send_method := wf_engine.GetItemAttrText(itemtype, itemkey, 'M4U_SEND_MODE',TRUE);
2044
2045 IF (l_Debug_Level <= 1) THEN
2046 cln_debug_pub.Add('l_send_method - ' || l_send_method, 2);
2047 END IF;
2048
2049 resultout := wf_engine.eng_completed||':' || l_send_method;
2050 EXCEPTION
2051 WHEN OTHERS THEN
2052 l_error_code :=SQLCODE;
2053 l_error_msg :=' Workflow - ' || itemtype || '/' || itemkey || ' - ' || SQLERRM;
2054
2055 FND_MESSAGE.SET_NAME('CLN','M4U_UNEXPECTED_ERROR');
2056 l_fnd_msg := FND_MESSAGE.GET;
2057
2058 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg
2062 || l_error_msg);
2059 || ' in m4u_xml_genprocess_out.check_send_method , Error code - '
2060 || l_error_code
2061 || ', Error message - '
2063
2064
2065 IF (l_Debug_Level <= 5) THEN
2066 cln_debug_pub.Add('Error :' || SQLCODE || ':' || SQLERRM, 5);
2067 END IF;
2068 IF (l_Debug_Level <= 2) THEN
2069 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.CHECK_SEND_METHOD =========== ',2);
2070 END IF;
2071
2072 Wf_Core.Context('m4u_xml_genprocess_out','check_send_method',
2073 itemtype, itemkey, to_char(actid), funcmode);
2074 RAISE;
2075 END;
2076
2077 -- Name
2078 -- dequeue_ucc_message
2079 -- Purpose
2080 -- dequeues payload from AQ when correlation is UCC:ERROR|UCC:HTTP
2081 -- Arguments
2082 -- itemtype => WF item type
2083 -- itemkey => WF item key
2084 -- actid => WF act id
2085 -- funcmode => WF func mode
2086 -- resultout => result param
2087 -- Notes
2088 -- Need to make a modification to set QueueName to be used in dequeue
2089 PROCEDURE dequeue_ucc_message(
2090 itemtype IN VARCHAR2,
2091 itemkey IN VARCHAR2,
2092 actid IN NUMBER,
2093 funcmode IN VARCHAR2,
2094 resultout IN OUT NOCOPY VARCHAR2)
2095 IS
2096 l_ecxmsg system.ecxmsg;
2097 l_queue_name varchar2(80);
2098 l_dequeue_options dbms_aq.dequeue_options_t;
2099 l_message_properties dbms_aq.message_properties_t;
2100 l_msgid RAW(16);
2101 l_wait_time NUMBER;
2102 l_group_identifier VARCHAR2(10);
2103 l_error_code VARCHAR2(50);
2104 l_error_msg VARCHAR2(255);
2105 l_fnd_msg VARCHAR2(255);
2106 l_agent wf_agent_t;
2107 l_event wf_event_t;
2108 BEGIN
2109 l_ecxmsg := null;
2110 l_queue_name := 'ecx_outbound';
2111 l_wait_time := DBMS_AQ.NO_WAIT;
2112
2113 l_group_identifier := wf_engine.GetItemAttrText(
2114 itemtype,
2115 itemkey,
2116 'M4U_SEND_MODE',
2117 TRUE);
2118
2119 IF (l_Debug_Level <= 2) THEN
2120 cln_debug_pub.Add('=========== Entering m4u_xml_genprocess_out.dequeue_ucc_message =========== ',2);
2121 END IF;
2122
2123 IF (l_Debug_Level <= 1) THEN
2124 cln_debug_pub.Add('Paramters received are' , 2);
2125 cln_debug_pub.Add('itemtype - ' || itemtype , 2);
2126 cln_debug_pub.Add('itemkey - ' || itemkey , 2);
2127 cln_debug_pub.Add('actid - ' || actid , 2);
2128 cln_debug_pub.Add('funcmode - ' || funcmode , 2);
2129 END IF;
2130
2131 IF (funcmode <> wf_engine.eng_run) THEN
2132 resultout := wf_engine.eng_null;
2133 IF (l_Debug_Level <= 2) THEN
2134 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.dequeue_ucc_message =========== ',2);
2135 END IF;
2136 RETURN;
2137 END IF;
2138
2139 BEGIN
2140 l_event := wf_engine.GetItemAttrEvent(itemtype, itemkey, 'ECX_EVENT_MESSAGE');
2141 EXCEPTION
2142 WHEN others THEN
2143 NULL;
2144 END;
2145
2146 IF l_event IS NULL THEN
2147 IF (l_Debug_Level <= 2) THEN
2148 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.dequeue_ucc_message, null event =========== ',2);
2149 END IF;
2150 END IF;
2151
2152 l_agent := l_event.getFromAgent;
2153
2154
2155 IF l_agent IS NULL THEN
2156 IF (l_Debug_Level <= 2) THEN
2157 cln_debug_pub.Add('=========== m4u_xml_genprocess_out.dequeue_ucc_message, null agent =========== ',2);
2158 END IF;
2159 ELSE
2160 l_queue_name := l_agent.getName;
2161 END IF;
2162
2163 l_dequeue_options.wait := l_wait_time;
2164 l_dequeue_options.correlation := l_group_identifier;
2165
2166 DBMS_AQ.DEQUEUE(queue_name => l_queue_name,
2167 dequeue_options => l_dequeue_options,
2168 message_properties => l_message_properties,
2169 payload => l_ecxmsg,
2170 msgid => l_msgid);
2171
2172 -- not raising an exception when message is NULL
2173 IF (l_Debug_Level <= 2) THEN
2177 resultout := wf_engine.eng_completed;
2174 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.dequeue_ucc_message normal =========== ',2);
2175 END IF;
2176
2178
2179 EXCEPTION
2180 WHEN OTHERS THEN
2181 l_error_code :=SQLCODE;
2182 l_error_msg :=' Workflow - ' || itemtype || '/' || itemkey || ' - ' || SQLERRM;
2183
2184 FND_MESSAGE.SET_NAME('CLN','M4U_UNEXPECTED_ERROR');
2185 l_fnd_msg := FND_MESSAGE.GET;
2186
2187 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg
2188 || ' in m4u_xml_genprocess_out.dequeue_ucc_message , Error code - '
2189 || l_error_code
2190 || ', Error message - '
2191 || l_error_msg);
2192
2193
2194 IF (l_Debug_Level <= 5) THEN
2195 cln_debug_pub.Add('Error :' || SQLCODE || ':' || SQLERRM, 5);
2196 END IF;
2197 IF (l_Debug_Level <= 2) THEN
2198 cln_debug_pub.Add('=========== Exiting m4u_xml_genprocess_out.dequeue_ucc_message exception =========== ',2);
2199 END IF;
2200
2201 resultout := wf_engine.eng_completed;
2202 Wf_Core.Context('m4u_xml_genprocess_out','dequeue_ucc_message ',
2203 itemtype, itemkey, to_char(actid), funcmode);
2204
2205 END;
2206
2207
2208 -- Name
2209 -- raise_payload_event
2210 -- Purpose
2211 -- raises event containing payload
2212 -- Arguments
2213 -- itemtype => WF item type
2214 -- itemkey => WF item key
2215 -- actid => WF act id
2216 -- funcmode => WF func mode
2217 -- resultout => result param
2218 -- Notes
2219 -- This needs to be replace by standard WF_ RAISE EVENT activity
2220 -- was developed as workaround when WF _RAISE EVENT was not working
2221 -- problem lies in GETTPXML parameters,
2222 PROCEDURE raise_payload_event(
2223 itemtype IN VARCHAR2,
2224 itemkey IN VARCHAR2,
2225 actid IN NUMBER,
2226 funcmode IN VARCHAR2,
2227 resultout IN OUT NOCOPY VARCHAR2)
2228 IS
2229 l_data_event wf_event_t;
2230 l_raise_event_name VARCHAR2(50);
2231 l_raise_event_key VARCHAR2(50);
2232 l_clob_xml_payload clob;
2233 l_event_params wf_parameter_list_t;
2234 l_error_code VARCHAR2(50);
2235 l_error_msg VARCHAR2(255);
2236 l_fnd_msg VARCHAR2(255);
2237 BEGIN
2238
2239 l_data_event := null;
2240 l_clob_xml_payload := null;
2241
2242 IF (l_debug_Level <= 2) THEN
2243 cln_debug_pub.Add('========= Entering m4u_xml_genprocess_out.raise_payload_event == ',2);
2244 END IF;
2245
2246 -- Parameters received
2247 IF (l_Debug_Level <= 1) THEN
2248 cln_debug_pub.Add('-- Parameters received from Workflow process -- ',1);
2249 cln_debug_pub.Add('itemtype - '||itemtype, 1);
2250 cln_debug_pub.Add('itemkey - '||itemkey, 1);
2251 cln_debug_pub.Add('actid - '||actid, 1);
2252 cln_debug_pub.Add('funcmode - '||funcmode, 1);
2253 cln_debug_pub.Add('---------------------------------------------',1);
2254 END IF;
2255
2256 -- if funcmode is not null then exit
2257 IF (funcmode <> wf_engine.eng_run) THEN
2258 resultout := wf_engine.eng_null;
2259 IF (l_Debug_Level <= 2) THEN
2260 cln_debug_pub.Add('====== Exiting raise_payload_event - Normal : resultout-' || resultout,2);
2261 END IF;
2262 RETURN;
2263 END IF;
2264
2265 l_data_event := wf_engine.GetActivityAttrEvent(
2266 itemtype => itemtype,
2267 itemkey => itemkey,
2268 actid => actid,
2269 name => 'EVENT_DATA');
2270 IF (l_Debug_Level <= 1) THEN
2271 cln_debug_pub.Add('EVENT_DATA obtained', 1);
2272 END IF;
2273
2274 l_raise_event_name := wf_engine.GetActivityAttrText(
2275 itemtype => itemtype,
2276 itemkey => itemkey,
2277 actid => actid,
2278 aname => 'EVENT_NAME',
2279 ignore_notfound => TRUE);
2280 IF (l_Debug_Level <= 1) THEN
2281 cln_debug_pub.Add('EVENT_NAME - ' || l_raise_event_name, 1);
2282 END IF;
2283
2284 l_raise_event_key := wf_engine.GetActivityAttrText(
2285 itemtype => itemtype,
2286 itemkey => itemkey,
2287 actid => actid,
2288 aname => 'EVENT_KEY',
2289 ignore_notfound => TRUE);
2290
2291 IF (l_Debug_Level <= 1) THEN
2292 cln_debug_pub.Add('EVENT_KEY - ' || l_raise_event_key, 1);
2293 END IF;
2294
2295 IF l_raise_event_key IS NULL THEN
2296 l_raise_event_key := itemkey;
2297 END IF;
2298
2299 l_clob_xml_payload := l_data_event.getEventData();
2300
2301 IF (l_Debug_Level <= 1) THEN
2302 cln_debug_pub.Add('Obtained XML payload as clob', 1);
2303 END IF;
2304
2305
2306 l_event_params := wf_parameter_list_t();
2307
2308 IF (l_Debug_Level <= 1) THEN
2309 cln_debug_pub.Add('Obtained Event Parameters', 1);
2310 END IF;
2311
2312 IF l_clob_xml_payload IS NULL
2313 THEN
2314 resultout := wf_engine.eng_completed || ':FAIL';
2315
2316 IF (l_debug_Level <= 2) THEN
2317 cln_debug_pub.Add('clob payload is null, exiting rasie_payload_event', 1);
2318 END IF;
2319
2320 wf_engine.setItemAttrtext(
2321 itemtype => itemtype,
2322 itemkey => itemkey,
2323 aname => 'M4U_ERROR_FLAG',
2324 avalue => 'Y');
2325
2326 IF (l_Debug_Level <= 1) THEN
2327 cln_debug_pub.Add('M4U_ERROR_FLAG set to Y', 1);
2328 END IF;
2329 RETURN;
2330 END IF;
2331
2332 IF (l_Debug_Level <= 1) THEN
2333 cln_debug_pub.Add('raising payload_event, with key -- ' || l_raise_event_key,2);
2334 END IF;
2335
2336
2337 WF_EVENT.raise(
2338 p_event_name => l_raise_event_name,
2339 p_event_key => l_raise_event_key,
2340 p_event_data => l_clob_xml_payload,
2341 p_parameters => l_event_params);
2342
2343 resultout := wf_engine.eng_completed || ':SUCCESS';
2344
2345 IF (l_Debug_Level <= 2) THEN
2346 cln_debug_pub.Add('====== exiting m4u_xml_genprocess_out.raise_payload_event - normal : resultout-' || resultout,2);
2347 END IF;
2348
2349 -- Exception Handling
2350 EXCEPTION
2351 WHEN OTHERS THEN
2352 l_error_code :=SQLCODE;
2353 l_error_msg :=' Workflow - ' || itemtype || '/' || itemkey || ' - ' || SQLERRM;
2354
2355 FND_MESSAGE.SET_NAME('CLN','M4U_UNEXPECTED_ERROR');
2356 l_fnd_msg := FND_MESSAGE.GET;
2357
2358 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_fnd_msg
2359 || ' in m4u_xml_genprocess_out.raise_payload_event , Error code - '
2360 || l_error_code
2361 || ', Error message - '
2362 || l_error_msg);
2363
2364
2365 IF (l_Debug_Level <= 5) THEN
2366 cln_debug_pub.Add('Error :' || SQLCODE || ':' || SQLERRM, 5);
2367 END IF;
2368 IF (l_Debug_Level <= 2) THEN
2369 cln_debug_pub.Add('=========== ERROR :Exiting m4u_xml_genprocess_out.raise_payload_event =========== ',2);
2370 END IF;
2371
2372 Wf_Core.Context('m4u_xml_genprocess_out','raise_payload_event',
2373 itemtype, itemkey, to_char(actid), funcmode);
2374 RAISE;
2375 END;
2376
2377
2378 BEGIN
2379 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
2380 END m4u_xml_genprocess_out;