[Home] [Help]
PACKAGE BODY: APPS.ITG_WF_UTILS
Source
1 PACKAGE BODY itg_wf_utils AS
2 /* ARCS: $Header: itgwfutb.pls 120.3 2006/07/11 07:49:28 pvaddana noship $
3 * CVS: itgwfutb.pls,v 1.19 2003/05/29 22:22:44 klai Exp
4 */
5 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
6
7 PROCEDURE create_outbound_collaboration(
8 itemtype IN VARCHAR2,
9 itemkey IN VARCHAR2,
10 actid IN NUMBER,
11 funcmode IN VARCHAR2,
12 resultout OUT NOCOPY VARCHAR2
13 ) IS
14 l_org_id NUMBER;
15 l_coll_id NUMBER;
16
17 l_return_status VARCHAR2(1);
18 l_doc_id VARCHAR2(20);
19 l_rel_num VARCHAR2(100);
20 l_doc_num VARCHAR2(100);
21 l_xact_type VARCHAR2(100);
22 l_xact_subtype VARCHAR2(100);
23 l_cln_type VARCHAR2(100);
24 l_doc_type VARCHAR2(100);
25 l_xml_event_key VARCHAR2(100);
26 l_buff VARCHAR2(2000);
27 BEGIN
28 IF (l_Debug_Level <= 2) THEN
29 itg_debug_pub.Add('--- Entering create_outbound_collaboration ---' ,2);
30 END IF;
31
32 IF funcmode = 'RUN' THEN
33 IF NOT itg_x_utils.g_initialized THEN
34 /* 4169685: REMOVE INSTALL DATA INSERTION FROM HR_LOCATIONS TABLE
35 * Missing Trading Partner setup and/or Connector uninitialized.
36 * Should never get here, since the workflow should not be activated.
37 */
38 resultout := 'ERROR';
39 RETURN;
40 END IF;
41
42 l_org_id := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_ORGANIZATION_ID');
43 l_rel_num := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_REL_NUM');
44 l_doc_num := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_DOC_NUM');
45 l_xact_type := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'ECX_TRANSACTION_TYPE');
46 l_xact_subtype := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'ECX_TRANSACTION_SUBTYPE');
47 l_doc_id := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'ECX_DOCUMENT_ID');
48 l_cln_type := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_TYPE');
49 l_doc_type := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_DOC_TYPE');
50 l_xml_event_key := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'XML_EVENT_KEY');
51
52 /* Refer to Bug no: 3896966, Collaborations need to be unique
53 * for Blanket Po Releases, Introduced XML_EVENT_KEY,
54 * XMLG_MESSAGE_ID in Workflow
55 */
56
57 /*To fix Enhancement bug 5348827 for BPO Releases not being displayed on Collab history*/
58 if l_rel_num is not null then
59 l_doc_num := l_doc_num||':'||l_rel_num;
60 end if;
61 /* See CLNPOWFB.pls */
62 CLN_CH_COLLABORATION_PKG.create_collaboration(
63 x_return_status => l_return_status,
64 x_msg_data => l_buff,
65 p_app_id => itg_x_utils.c_application_id,
66 p_ref_id => NULL,
67 p_org_id => l_org_id,
68 p_rel_no => l_rel_num,
69 p_doc_no => l_doc_num,
70 p_doc_rev_no => NULL, /* NOTE: ??? */
71 p_xmlg_transaction_type => l_xact_type,
72 p_xmlg_transaction_subtype => l_xact_subtype,
73 p_xmlg_document_id => l_doc_id,
74 p_partner_doc_no => NULL, /* NOTE: ??? */
75 p_coll_type => l_cln_type,
76 p_tr_partner_type => itg_x_utils.c_party_type,
77 p_tr_partner_id => itg_x_utils.g_party_id,
78 p_tr_partner_site => itg_x_utils.g_party_site_id,
79 p_resend_flag => 'N',
80 p_resend_count => 0,
81 p_doc_owner => FND_GLOBAL.USER_ID,
82 p_init_date => SYSDATE,
83 p_doc_creation_date => SYSDATE, /* NOTE: value from record? */
84 p_doc_revision_date => SYSDATE,
85 p_doc_type => l_doc_type,
86 p_doc_dir => 'OUT',
87 p_coll_pt => itg_x_utils.c_coll_pt,
88 p_xmlg_msg_id => NULL,
89 p_unique1 => null,
90 p_unique2 => NULL,
91 p_unique3 => NULL,
92 p_unique4 => NULL,
93 p_unique5 => NULL,
94 p_sender_component => NULL,
95 p_rosettanet_check_required => FALSE,
96 x_coll_id => l_coll_id,
97 p_xml_event_key => l_xml_event_key
98 );
99
100 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
101 resultout := 'ERROR';
102 ELSE
103 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'CLN_ID', l_coll_id);
104 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
105 END IF;
106
107 IF (l_Debug_Level <= 2) THEN
108 itg_debug_pub.Add('--- Exting create_outbound_collaboration ---' ,2);
109 END IF;
110 RETURN;
111 END IF;
112 EXCEPTION
113 WHEN OTHERS THEN
114 WF_CORE.CONTEXT('itg_wf_utils', 'create_outbound_collaboration',
115 itemtype, itemkey, to_char(actid), funcmode
116 );
117 IF (l_Debug_Level <= 6) THEN
118 itg_debug_pub.Add('--- Exting create_outbound_collaboration : ERROR---' ,6);
119 END IF;
120 RAISE;
121 END create_outbound_collaboration;
122
123
124 PROCEDURE update_outbound_collaboration(
125 itemtype IN VARCHAR2,
126 itemkey IN VARCHAR2,
127 actid IN NUMBER,
128 funcmode IN VARCHAR2,
129 resultout OUT NOCOPY VARCHAR2
130 ) IS
131 l_org_id NUMBER;
132 l_coll_id NUMBER;
133 l_collaboration_dtl_id NUMBER;
134
135 l_return_status VARCHAR2(1);
136 l_buff VARCHAR2(2000);
137 l_rel_num VARCHAR2(100);
138 l_doc_num VARCHAR2(100);
139 l_xact_type VARCHAR2(100);
140 l_xact_subtype VARCHAR2(100);
141 l_doc_id VARCHAR2(20);
142 l_cln_type VARCHAR2(100);
143 l_doc_type VARCHAR2(100);
144 l_ref_id VARCHAR2(100);
145 l_xml_event_key VARCHAR2(100);
146 l_xmlg_msg_id VARCHAR2(100);
147
148 BEGIN
149 IF (l_Debug_Level <= 2) THEN
150 itg_debug_pub.Add('--- Entering update_outbound_collaboration ---' ,2);
151 END IF;
152
153 IF funcmode = 'RUN' THEN
154 IF NOT itg_x_utils.g_initialized THEN
155 /* 4169685: REMOVE INSTALL DATA INSERTION FROM HR_LOCATIONS TABLE
156 * Missing Trading Partner setup and/or Connector uninitialized.
157 * Should never get here, since the workflow should not be activated.
158 */
159 resultout := 'ERROR';
160 RETURN;
161 END IF;
162
163 l_cln_type := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_TYPE');
164 l_doc_type := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_DOC_TYPE');
165 l_coll_id := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_ID');
166 -- using ECX_PARAMETER5 for referenceid.
167 l_ref_id := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'ECX_PARAMETER5');
168 l_xml_event_key := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'XML_EVENT_KEY');
169 l_xmlg_msg_id := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'XMLG_MESSAGE_ID');
170
171 /* Refer to Bug no: 3896966, Collaborations need to be unique
172 * for Blanket Po Releases, Introduced XML_EVENT_KEY,
173 * XMLG_MESSAGE_ID in Workflow
174 */
175
176 CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
177 x_return_status => l_return_status,
178 x_msg_data => l_buff,
179 p_coll_id => l_coll_id,
180 /* Returned from previous call */
181 p_app_id => itg_x_utils.c_application_id,
182 p_ref_id => l_ref_id,
183 /* The value of <CONTROLAREA>.<SENDER>.<REFERENCEID> ? */
184 p_rel_no => NULL,
185 p_doc_no => NULL,
186 p_doc_rev_no => NULL,
187 p_xmlg_transaction_type => NULL,
188 p_xmlg_transaction_subtype => NULL,
189 p_xmlg_document_id => NULL,
190 p_resend_flag => NULL,
191 p_resend_count => NULL,
192 p_disposition => NULL,
193 p_coll_status => 'COMPLETED',
194 p_doc_type => l_doc_type,
195 p_doc_dir => 'OUT',
196 p_coll_pt => itg_x_utils.c_xmlg_coll_pt,
197 p_org_ref => NULL,
198 p_doc_status => 'SUCCESS',
199 p_notification_id => NULL,
200 p_msg_text => 'Document successfully sent',
201 p_tr_partner_type => itg_x_utils.c_party_type,
202 p_tr_partner_id => itg_x_utils.g_party_id,
203 p_tr_partner_site => itg_x_utils.g_party_site_id,
204 p_sender_component => NULL,
205 p_rosettanet_check_required => FALSE,
206 p_xmlg_msg_id => l_xmlg_msg_id,
207 x_dtl_coll_id => l_collaboration_dtl_id,
208 p_xml_event_key => l_xml_event_key
209 );
210
211 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
212 resultout := 'ERROR';
213 ELSE
214 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
215 END IF;
216
217 IF (l_Debug_Level <= 2) THEN
218 itg_debug_pub.Add('--- Exting update_outbound_collaboration ---' ,2);
219 END IF;
220 RETURN;
221 END IF;
222 EXCEPTION
223 WHEN OTHERS THEN
224 WF_CORE.CONTEXT('itg_wf_utils', 'update_outbound_collaboration',
225 itemtype, itemkey, to_char(actid), funcmode);
226
227 IF (l_Debug_Level <= 6) THEN
228 itg_debug_pub.Add('--- Exting update_outbound_collaboration :ERROR---' ,6);
229 END IF;
230
231 RAISE;
232 END update_outbound_collaboration;
233
234
235
236
237 PROCEDURE update_outbound_collab_cbod(
238 itemtype IN VARCHAR2,
239 itemkey IN VARCHAR2,
240 actid IN NUMBER,
241 funcmode IN VARCHAR2,
242 resultout OUT NOCOPY VARCHAR2
243 ) IS
244 l_org_id NUMBER;
245 l_coll_id NUMBER;
246 l_collaboration_dtl_id NUMBER;
247
248 l_rel_num VARCHAR2(100);
249 l_doc_num VARCHAR2(100);
250 l_xact_type VARCHAR2(100);
251 l_xact_subtype VARCHAR2(100);
252 l_doc_id VARCHAR2(20);
253 l_cln_type VARCHAR2(100);
254 l_doc_type VARCHAR2(100);
255 l_xmlg_msg_id VARCHAR2(100);
256 l_statuslvl VARCHAR2(50);
257 l_msg_text VARCHAR2(50);
258 l_coll_status VARCHAR2(50);
259 l_lang VARCHAR2(50);
260 l_nls_lang VARCHAR2(50);
261 l_return_status VARCHAR2(1);
262 l_buff VARCHAR2(2000);
263 BEGIN
264 IF (l_Debug_Level <= 2) THEN
265 itg_debug_pub.Add('--- Entering update_outbound_collab_cbod ---' ,2);
266 END IF;
267
268 IF funcmode = 'RUN' THEN
269 l_lang := wf_engine.GetItemAttrText(itemtype,itemkey,'SESSION_LANGUAGE',TRUE);
270
271 IF l_lang IS NOT NULL THEN
272 BEGIN
273 select nls_language
274 into l_nls_lang
275 from fnd_languages
276 where language_code = l_lang;
277
278 IF (l_Debug_Level <= 1) THEN
279 itg_debug_pub.Add('NLS langauage '||l_nls_lang ,1);
280 END IF;
281
282 FND_GLOBAL.set_nls_context(p_nls_language => l_nls_lang);
283
284 IF (l_Debug_Level <= 1) THEN
285 itg_debug_pub.Add('NLS context is switched' ,1);
286 END IF;
287 EXCEPTION
288 WHEN OTHERS THEN
289 IF (l_Debug_Level <= 5) THEN
290 itg_debug_pub.Add('Error changing session language to '|| l_lang ,5);
291 itg_debug_pub.Add(SQLCODE || ' - ' || SQLERRM ,5);
292 END IF;
293 END;
294 END IF;
295
296 IF NOT itg_x_utils.g_initialized THEN
297 /* 4169685: REMOVE INSTALL DATA INSERTION FROM HR_LOCATIONS TABLE
298 * Missing Trading Partner setup and/or Connector uninitialized.
299 * Should never get here, since the workflow should not be activated.
300 */
301 resultout := 'ERROR';
302 RETURN;
303 END IF;
304
305 -- The CLN Collaboration history should complete/error when
306 -- there is a failure in the collaboration.
307 -- The success/failure of collaboration can be checked from
308 -- the STATUSLVL item-attribute
309
310 l_cln_type := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_TYPE');
311 l_doc_type := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'CLN_DOC_TYPE');
312 l_xmlg_msg_id := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'XMLG_MESSAGE_ID');
313
314 l_coll_id := wf_engine.GetItemAttrText(itemtype, itemkey, 'PARAMETER10');
315 l_statuslvl := wf_engine.GetItemAttrText(itemtype, itemkey, 'PARAMETER6');
316
317 IF l_statuslvl = '00' THEN
318 l_coll_status := 'COMPLETED';
319 ELSE
320 l_coll_status := 'ERROR';
321 END IF;
322
323 /* Refer to Bug no: 3902644, 'CBOD' Payload Collaborations not created.
324 * Using the XMLG_MESSAGE_ID introduced in ITGSTD Workflow.
325 */
326
327 CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
328 x_return_status => l_return_status,
329 x_msg_data => l_buff,
330 p_coll_id => l_coll_id,
331 /* Returned from previous call */
332 p_app_id => itg_x_utils.c_application_id,
333 p_ref_id => NULL,
334 p_rel_no => NULL,
335 p_doc_no => NULL,
336 p_doc_rev_no => NULL,
337 p_xmlg_transaction_type => NULL,
338 p_xmlg_transaction_subtype => NULL,
339 p_xmlg_document_id => NULL,
340 p_resend_flag => NULL,
341 p_resend_count => NULL,
342 p_disposition => NULL,
343 p_coll_status => l_coll_status, /*'COMPLETED',*/
344 p_doc_type => l_doc_type,
345 p_doc_dir => 'OUT',
346 p_coll_pt => itg_x_utils.c_xmlg_coll_pt,
347 p_org_ref => NULL,
348 p_doc_status => 'SUCCESS',
349 p_notification_id => NULL,
350 p_msg_text => 'Document successfully sent',
351 p_tr_partner_type => itg_x_utils.c_party_type,
352 p_tr_partner_id => itg_x_utils.g_party_id,
353 p_tr_partner_site => itg_x_utils.g_party_site_id,
354 p_sender_component => NULL,
355 p_rosettanet_check_required => FALSE,
356 p_xmlg_msg_id => l_xmlg_msg_id,
357 x_dtl_coll_id => l_collaboration_dtl_id
358 );
359
360 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
361 resultout := 'ERROR';
362 ELSE
363 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
364 END IF;
365
366 IF (l_Debug_Level <= 2) THEN
367 itg_debug_pub.Add('--- Exiting update_outbound_collab_cbod ---' ,2);
368 END IF;
369 RETURN;
370 END IF;
371 EXCEPTION
372 WHEN OTHERS THEN
373 WF_CORE.CONTEXT('itg_wf_utils', 'update_outbound_collaboration cbod',
374 itemtype, itemkey, to_char(actid), funcmode);
375 IF (l_Debug_Level <= 6) THEN
376 itg_debug_pub.Add('--- Exting update_outbound_collab_cbod :ERROR---' ,6);
377 END IF;
378 RAISE;
379 END update_outbound_collab_cbod;
380
381
382
383 --4335714
384 --This procedure converts the CBOD description description error code
385 --into a translated message in the language corresponding to the //language tag
386 --corresponding to the inbound xgm
387 --The langauage tag is passed in as parameter5 of the inbound xgm.
388 --Also the currrent nls session is set to langauge value, to allow CBOD XML generation
389 --to be done in the translated language.
390 PROCEDURE set_cbod_description(
391 itemtype in varchar2,
392 itemkey in varchar2,
393 actid in number,
394 funcmode in varchar2,
395 resultout out nocopy varchar2)
396 IS
397 l_nls_language varchar2(20);
398 l_lang varchar2(20);
399 l_error_code varchar2(100);
400 l_error_msg varchar2(100);
401 parameter7 varchar2(1000);
402 parameter5 varchar2(1000);
403 l_cbod_message varchar2(2000);
404 BEGIN
405 IF (l_Debug_Level <= 2) THEN
406 itg_debug_pub.Add('--- Entering update_set_cbod_description ---' ,2);
407 END IF;
408
409 parameter5 := wf_engine.GetItemAttrText(itemtype,itemkey,'PARAMETER5',TRUE);
410 parameter7 := wf_engine.GetItemAttrText(itemtype,itemkey,'PARAMETER7',TRUE);
411
412 IF (l_Debug_Level <= 1) THEN
413 itg_debug_pub.Add('Parameter5 - ' || parameter5 ,1);
414 itg_debug_pub.Add('Parameter7 - ' || parameter7 ,1);
415 END IF;
416
417 BEGIN
418 SELECT NLS_LANGUAGE
419 INTO l_nls_language
420 FROM FND_LANGUAGES
421 WHERE LANGUAGE_CODE = parameter5
422 AND installed_flag in ('B','I') ;
423
424 IF (l_Debug_Level <= 1) THEN
425 itg_debug_pub.Add('l_nls_language - ' || l_nls_language ,1);
426 END IF;
427
428 l_lang := FND_GLOBAL.CURRENT_LANGUAGE;
429
430 IF (l_Debug_Level <= 1) THEN
431 itg_debug_pub.Add('l_lang - ' || l_lang ,1);
432 END IF;
433
434 FND_GLOBAL.set_nls_context(p_nls_language => l_nls_language);
435
436 IF (l_Debug_Level <= 1) THEN
437 itg_debug_pub.Add('set_nls_context SET' ,1);
438 END IF;
439
440 wf_engine.SetItemAttrText(itemtype,itemkey,'SESSION_LANGUAGE',l_lang);
441
442 IF (l_Debug_Level <= 1) THEN
443 itg_debug_pub.Add('SetItemAttrText - SESSION_LANGUAGE SET ');
444 END IF;
445
446 EXCEPTION
447 WHEN OTHERS THEN
448 IF (l_Debug_Level <= 1) THEN
449 itg_debug_pub.Add('Exception lang - '|| parameter5 || SQLCODE || '-' ||SQLERRM, 1);
450 END IF;
451 null; -- if any error, continue to use current session langauge.
452 END;
453
454 l_cbod_message := itg_x_utils.translateCBODDescMsg(p_msg_list=> parameter7);
455
456 IF (l_Debug_Level <= 1) THEN
457 itg_debug_pub.Add('l_cbod_message' || l_cbod_message ,1);
458 END IF;
459
460 wf_engine.SetItemAttrText(itemtype,itemkey,'PARAMETER7',l_cbod_message);
461 resultout := 'COMPLETED';
462
463 IF (l_Debug_Level <= 2) THEN
464 itg_debug_pub.Add('--- Exiting update_set_cbod_description ---' ,2);
465 END IF;
466 EXCEPTION
467 WHEN OTHERS THEN
468 WF_CORE.CONTEXT('itg_wf_utils', 'set_cbod_description',
469 itemtype, itemkey, to_char(actid), funcmode);
470 IF (l_Debug_Level <= 6) THEN
471 itg_debug_pub.Add('--- Exting set_cbod_description :ERROR---' ,6);
472 END IF;
473
474 RAISE;
475 END;
476 END itg_wf_utils;