[Home] [Help]
PACKAGE BODY: APPS.CLN_NP_UTILS_PKG
Source
1 PACKAGE BODY CLN_NP_UTILS_PKG AS
2 /* $Header: CLNNPUTLB.pls 115.0 2003/10/31 07:21:06 vumapath noship $ */
3 -- Package
4 -- CLN_NP_UTILS_PKG
5 --
6 -- Purpose
7 -- Specification of package body: CLN_NP_UTILS_PKG.
8 -- This package bundles all the utility functions of
9 -- notification Processing module for processing inbound messages
10 --
11 -- History
12 -- Oct-16-2003 Viswanthan Umapathy Created
13
14 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
15
16 -- Name
17 -- UPDATE_COLLABORATION
18 -- Purpose
19 -- This procedure raises collaboration update event to update a collaboration.
20 -- passing all the procedure parameters as event parameters
21 -- Arguments
22 --
23 -- Notes
24 -- No specific notes.
25
26 PROCEDURE UPDATE_COLLABORATION(
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_data OUT NOCOPY VARCHAR2,
29 p_ref_id IN VARCHAR2,
30 p_doc_no IN VARCHAR2,
31 p_part_doc_no IN VARCHAR2,
32 p_msg_text IN VARCHAR2,
33 p_status_code IN NUMBER,
34 p_int_ctl_num IN NUMBER,
35 p_tp_header_id IN NUMBER)
36 IS
37 l_cln_ch_parameters wf_parameter_list_t;
38 l_event_key NUMBER;
39 l_error_code NUMBER;
40 l_error_msg VARCHAR2(2000);
41 l_debug_mode VARCHAR2(255);
42 l_doc_status VARCHAR2(255);
43 BEGIN
44 -- Sets the debug mode to be FILE
45 l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
46
47 IF (l_Debug_Level <= 2) THEN
48 cln_debug_pub.Add('ENTERING UPDATE_COLLABORATION', 2);
49 END IF;
50
51 -- Initialize API return status to success
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 FND_MESSAGE.SET_NAME('CLN','CLN_CH_EVENT_RAISED');
55 FND_MESSAGE.SET_TOKEN('EVENT','Update');
56 x_msg_data := FND_MESSAGE.GET;
57
58 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
59
60 IF (l_Debug_Level <= 1) THEN
61 cln_debug_pub.Add('With the following parameters', 1);
62 cln_debug_pub.Add('p_ref_id' || p_ref_id, 1);
63 cln_debug_pub.Add('p_doc_no:' || p_doc_no, 1);
64 cln_debug_pub.Add('p_status_code:' || p_status_code, 1);
65 cln_debug_pub.Add('p_msg_text:' || p_msg_text, 1);
66 cln_debug_pub.Add('p_part_doc_no:' || p_part_doc_no, 1);
67 cln_debug_pub.Add('p_int_ctl_num:' || p_int_ctl_num, 1);
68 cln_debug_pub.Add('p_tp_header_id:' || p_tp_header_id, 1);
69 END IF;
70
71 IF p_status_code = 0 THEN
72 l_doc_status := 'SUCCESS';
73 -- ELSIF p_status_code = 1 THEN
74 -- l_doc_status := 'ERROR';
75 ELSE
76 l_doc_status := 'ERROR';
77 END IF;
78
79 IF (l_Debug_Level <= 1) THEN
80 cln_debug_pub.Add('l_doc_status:' || l_doc_status, 1);
81 END IF;
82
83 l_cln_ch_parameters := wf_parameter_list_t();
84
85 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER', p_int_ctl_num, l_cln_ch_parameters);
86 WF_EVENT.AddParameterToList('REFERENCE_ID', p_ref_id, l_cln_ch_parameters);
87
88 WF_EVENT.AddParameterToList('DOCUMENT_NO', p_doc_no, l_cln_ch_parameters);
89 WF_EVENT.AddParameterToList('PARTNER_DOCUMENT_NO', p_part_doc_no, l_cln_ch_parameters);
90 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', p_doc_no, l_cln_ch_parameters);
91
92 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
93 WF_EVENT.AddParameterToList('MESSAGE_TEXT', p_msg_text, l_cln_ch_parameters);
94
95 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',
96 l_event_key, NULL, l_cln_ch_parameters, NULL);
97
98 IF (l_Debug_Level <= 1) THEN
99 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update raised', 1);
100 END IF;
101
102 IF (l_Debug_Level <= 2) THEN
103 cln_debug_pub.Add('EXITING UPDATE_COLLABORATION', 2);
104 END IF;
105 EXCEPTION
106 WHEN OTHERS THEN
107 l_error_code := SQLCODE;
108 l_error_msg := SQLERRM;
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 x_msg_data := l_error_code || ':' || l_error_msg;
111 IF (l_Debug_Level <= 6) THEN
112 cln_debug_pub.Add(x_msg_data, 4);
113 END IF;
114 IF (l_Debug_Level <= 2) THEN
115 cln_debug_pub.Add('EXITING UPDATE_COLLABORATION', 2);
116 END IF;
117 END UPDATE_COLLABORATION;
118
119
120
121 -- Name
122 -- ADD_COLLABORATION_MESSAGE
123 -- Purpose
124 -- This procedure raise event to add messages into collaboration history
125 -- passing all the procedure parameters as event parameters
126 -- Arguments
127 -- Internal Control Number
128 -- Reference 1 to 5
129 -- Message Text
130 -- Notes
131 -- No specific notes.
132
133 PROCEDURE ADD_COLLABORATION_MESSAGE(
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_data OUT NOCOPY VARCHAR2,
136 p_ictrl_no IN NUMBER,
137 p_ref1 IN VARCHAR2,
138 p_ref2 IN VARCHAR2,
139 p_ref3 IN VARCHAR2,
140 p_ref4 IN VARCHAR2,
141 p_ref5 IN VARCHAR2,
142 p_dtl_msg IN VARCHAR2)
143 IS
144 l_cln_ch_parameters wf_parameter_list_t;
145 l_event_key NUMBER;
146 l_error_code NUMBER;
147 l_error_msg VARCHAR2(2000);
148 l_debug_mode VARCHAR2(255);
149 l_msg_data VARCHAR2(2000);
150 BEGIN
151 -- Sets the debug mode to be FILE
152 l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
153
154 IF (l_Debug_Level <= 2) THEN
155 cln_debug_pub.Add('ENTERING ADD_COLLABORATION_MESSAGE', 2);
156 END IF;
157
158 -- Parameters received
159 IF (l_Debug_Level <= 1) THEN
160 cln_debug_pub.Add('With the following parameters',1);
161 cln_debug_pub.Add('p_ictrl_no - ' || p_ictrl_no,1);
162 cln_debug_pub.Add('p_ref1 - ' || p_ref1,1);
163 cln_debug_pub.Add('p_ref2 - ' || p_ref2,1);
164 cln_debug_pub.Add('p_ref3 - ' || p_ref3,1);
165 cln_debug_pub.Add('p_ref4 - ' || p_ref4,1);
166 cln_debug_pub.Add('p_ref5 - ' || p_ref5,1);
167 cln_debug_pub.Add('p_dtl_msg - ' || p_dtl_msg,1);
168 END IF;
169
170 -- Initialize API return status to success
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172 FND_MESSAGE.SET_NAME('CLN', 'CLN_G_RET_MSG_SUCCESS');
173 x_msg_data := FND_MESSAGE.GET;
174
175
176 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
177
178 l_cln_ch_parameters := wf_parameter_list_t();
179
180 WF_EVENT.AddParameterToList('REFERENCE_ID1', p_ref1, l_cln_ch_parameters);
181 WF_EVENT.AddParameterToList('REFERENCE_ID2', p_ref2, l_cln_ch_parameters);
182 WF_EVENT.AddParameterToList('REFERENCE_ID3', p_ref3, l_cln_ch_parameters);
183 WF_EVENT.AddParameterToList('REFERENCE_ID4', p_ref4, l_cln_ch_parameters);
184 WF_EVENT.AddParameterToList('REFERENCE_ID5', p_ref5, l_cln_ch_parameters);
185 WF_EVENT.AddParameterToList('DETAIL_MESSAGE', p_dtl_msg, l_cln_ch_parameters);
186 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER', p_ictrl_no, l_cln_ch_parameters);
187 -- WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SALES_ORDER', l_cln_ch_parameters);
188 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
189 -- Not required since defaulted to APPS
190 -- WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
191
192 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',
193 l_event_key, NULL, l_cln_ch_parameters, NULL);
194
195 IF (l_Debug_Level <= 1) THEN
196 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage', 1);
197 END IF;
198
199 IF (l_Debug_Level <= 2) THEN
200 cln_debug_pub.Add('EXITING ADD_COLLABORATION_MESSAGE', 2);
201 END IF;
202 EXCEPTION
203 WHEN OTHERS THEN
204 l_error_code := SQLCODE;
205 l_error_msg := SQLERRM;
206 x_return_status := FND_API.G_RET_STS_ERROR;
207 x_msg_data := l_error_code || ':' || l_error_msg;
208 IF (l_Debug_Level <= 6) THEN
209 cln_debug_pub.Add(x_msg_data, 4);
210 END IF;
211 IF (l_Debug_Level <= 2) THEN
212 cln_debug_pub.Add('EXITING ADD_COLLABORATION_MESSAGE', 2);
213 END IF;
214 END ADD_COLLABORATION_MESSAGE;
215
216
217
218 -- Name
219 -- GET_FND_MESSSAGE
220 -- Purpose
221 -- Gets the FND message for the given message name
222 -- substituting the token values
223 -- Arguments
224 -- FND message name
225 -- Token Name
226 -- Token Value
227 -- Notes
228 -- No specific notes
229
230 PROCEDURE GET_FND_MESSSAGE(
231 p_fnd_message_name IN VARCHAR2,
232 p_token_name1 IN VARCHAR2,
233 p_token_value1 IN VARCHAR2,
234 p_token_name2 IN VARCHAR2,
235 p_token_value2 IN VARCHAR2,
236 p_message OUT NOCOPY VARCHAR2
237 )
238 IS
239 l_return_msg VARCHAR2(2000);
240 l_debug_mode VARCHAR2(300);
241 l_error_code NUMBER;
242 l_error_msg VARCHAR2(2000);
243 BEGIN
244
245 -- Sets the debug mode to be FILE
246 l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
247
248 IF (l_Debug_Level <= 2) THEN
249 cln_debug_pub.Add('ENTERING GET_FND_MESSSAGE', 2);
250 END IF;
251
252 IF (l_Debug_Level <= 1) THEN
253 cln_debug_pub.Add('With the following parameters:', 1);
254 cln_debug_pub.Add('p_fnd_message_name:' || p_fnd_message_name, 1);
255 cln_debug_pub.Add('p_token_name1:' || p_token_name1, 1);
256 cln_debug_pub.Add('p_token_value1:' || p_token_value1, 1);
257 cln_debug_pub.Add('p_token_name2:' || p_token_name2, 1);
258 cln_debug_pub.Add('p_token_value2:' || p_token_value2, 1);
259 END IF;
260
261 FND_MESSAGE.SET_NAME('CLN', p_fnd_message_name);
262 IF p_token_name1 IS NOT NULL THEN
263 FND_MESSAGE.SET_TOKEN(p_token_name1, p_token_value1);
264 END IF;
265 IF p_token_name2 IS NOT NULL THEN
266 FND_MESSAGE.SET_TOKEN(p_token_name2, p_token_value2);
267 END IF;
268 p_message := FND_MESSAGE.GET;
269
270 IF (l_Debug_Level <= 1) THEN
271 cln_debug_pub.Add('p_message:' || p_message, 1);
272 END IF;
273
274 IF (l_Debug_Level <= 2) THEN
275 cln_debug_pub.Add('EXITING GET_FND_MESSSAGE', 2);
276 END IF;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 l_error_code := SQLCODE;
281 l_error_msg := SQLERRM;
282 l_return_msg := l_error_code||' : '||l_error_msg;
283 cln_debug_pub.Add(l_return_msg, 3);
284 l_return_msg := 'While trying to get the FND message '
285 || ' for '
286 || p_fnd_message_name
287 || ', the following error is encountered:'
288 || l_return_msg;
289 IF (l_Debug_Level <= 6) THEN
290 cln_debug_pub.Add(l_return_msg, 3);
291 END IF;
292 IF (l_Debug_Level <= 2) THEN
293 cln_debug_pub.Add('EXITING GET_FND_MESSSAGE', 2);
294 END IF;
295 END GET_FND_MESSSAGE;
296
297
298
299
300 -- Name
301 -- CALL_TAKE_ACTIONS
302 -- Purpose
303 -- Invokes Notification Processor TAKE_ACTIONS according to the parameter.
304 -- Arguments
305 --
306 -- Notes
307 -- No specific notes.
308
309 PROCEDURE CALL_TAKE_ACTIONS(
310 p_itemtype IN VARCHAR2,
311 p_itemkey IN VARCHAR2,
312 p_actid IN NUMBER,
313 p_funcmode IN VARCHAR2,
314 x_resultout IN OUT NOCOPY VARCHAR2)
315 IS
316 l_doc_status VARCHAR2(100);
317 l_trp_id VARCHAR2(100);
318 l_app_ref_id VARCHAR2(255);
319 l_return_status VARCHAR2(1000);
320 l_return_msg VARCHAR2(2000);
321 l_error_code NUMBER;
322 l_error_msg VARCHAR2(2000);
323 l_msg_data VARCHAR2(1000);
324 l_not_msg VARCHAR2(1000);
325 l_debug_mode VARCHAR2(255);
326 l_tp_id NUMBER;
327 l_ret_status VARCHAR2(5);
328 l_cbod_statuslvl VARCHAR2(5);
329 l_int_ctl_num NUMBER;
330 l_not_code1 VARCHAR2(255);
331 l_not_code_desc1 VARCHAR2(2000);
332 l_tp_type VARCHAR2(255);
333 BEGIN
334
335 -- Sets the debug mode to be FILE
336 l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
337
338 x_resultout:='Yes';
339
340 IF (l_Debug_Level <= 2) THEN
341 cln_debug_pub.Add('ENTERING CALL_TAKE_ACTIONS API', 2);
342 END IF;
343
344 IF (l_Debug_Level <= 1) THEN
345 cln_debug_pub.Add('Parameters:', 1);
346 END IF;
347
348 -- Internal Control Number
349 l_int_ctl_num := to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER1', TRUE));
350 IF (l_Debug_Level <= 1) THEN
351 cln_debug_pub.Add('l_int_ctl_num:' || l_int_ctl_num, 1);
352 END IF;
353
354 -- Application Reference ID
355 l_app_ref_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
356 IF (l_Debug_Level <= 1) THEN
357 cln_debug_pub.Add('l_app_ref_id:' || l_app_ref_id, 1);
358 END IF;
359
360 -- Document Status
361 l_doc_status := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', TRUE);
362 IF (l_Debug_Level <= 1) THEN
363 cln_debug_pub.Add('l_doc_status:' || l_doc_status, 1);
364 END IF;
365
366 -- TP Header ID
367 l_trp_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE);
368 IF (l_Debug_Level <= 1) THEN
369 cln_debug_pub.Add('l_tp_id:' || l_trp_id, 1);
370 END IF;
371
372 -- Confirm BOD Status lvl
373 l_cbod_statuslvl := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', TRUE);
374 IF (l_Debug_Level <= 1) THEN
375 cln_debug_pub.Add('l_cbod_statuslvl:' || l_cbod_statuslvl, 1);
376 END IF;
377
378
379 -- If either statuslvl or document status is available, set the other one
380 IF l_doc_status IS NOT NULL THEN -- Set the value of statuslvl based on document status
381 IF l_cbod_statuslvl IS NULL THEN
382 IF (l_doc_status = 'SUCCESS') THEN
383 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', '00');
384 ELSE -- Assertion l_doc_status = 'ERROR'
385 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', '99');
386 END IF;
387 END IF;
388 ELSE -- Set the value of document status based on statuslvl
389 IF l_cbod_statuslvl IS NOT NULL THEN
390 IF (l_cbod_statuslvl = '00') THEN
391 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', 'SUCCESS');
392 ELSE -- Assertion l_cbod_statuslvl = '99'
393 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', 'ERROR');
394 END IF;
395 END IF;
396 END IF;
397
398 -- Notification Code1
399 l_not_code1 := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER6', TRUE);
400 IF (l_Debug_Level <= 1) THEN
401 cln_debug_pub.Add('l_not_code1:' || l_not_code1, 1);
402 END IF;
403
404 -- Notification Code Description1
405 l_not_code_desc1 := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER7', TRUE);
406 IF (l_Debug_Level <= 1) THEN
407 cln_debug_pub.Add('l_not_code_desc1:' || l_not_code_desc1, 1);
408 END IF;
409
410
411 -- Trading Partner Type
412 l_tp_type := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER8', TRUE);
413 IF (l_Debug_Level <= 1) THEN
414 cln_debug_pub.Add('l_tp_type:' || l_tp_type, 1);
415 END IF;
416
417 -- For futurte use
418 -- l_unused := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER9', TRUE);
419
420
421 CLN_UTILS.GET_TRADING_PARTNER(l_trp_id, l_tp_id);
422 IF (l_Debug_Level <= 1) THEN
423 cln_debug_pub.Add('Trading Partner ID:' || l_tp_id, 1);
424 END IF;
425
426
427 IF l_not_code1 IS NOT NULL AND LENGTH(TRIM(l_not_code1)) > 0 THEN
428 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
429 x_ret_code => l_return_status,
430 x_ret_desc => l_return_msg,
431 p_notification_code => l_not_code1,
432 p_notification_desc => l_not_code_desc1,
433 p_status => l_doc_status,
434 p_tp_id => to_char(l_tp_id),
435 p_reference => l_app_ref_id,
436 p_coll_point => 'APPS',
437 p_int_con_no => l_int_ctl_num);
438 IF l_return_status <> 'S' THEN
439 IF (l_Debug_Level <= 6) THEN
440 cln_debug_pub.Add('CALL_TAKE_ACTIONS CALL FAILED', 6);
441 END IF;
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444 END IF;
445
446 IF (l_Debug_Level <= 2) THEN
447 cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
448 END IF;
449
450 EXCEPTION
451 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452 IF (l_Debug_Level <= 6) THEN
453 cln_debug_pub.Add(l_return_msg, 6);
454 END IF;
455
456 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_return_msg);
457 IF (l_Debug_Level <= 2) THEN
458 cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
459 END IF;
460
461 WHEN OTHERS THEN
462 l_error_code := SQLCODE;
463 l_error_msg := SQLERRM;
464 l_not_msg := l_error_code || ':' || l_error_msg;
465 IF (l_Debug_Level <= 6) THEN
466 cln_debug_pub.Add(l_not_msg, 6);
467 END IF;
468
469 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_not_msg);
470 IF (l_Debug_Level <= 2) THEN
471 cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
472 END IF;
473 END CALL_TAKE_ACTIONS;
474
475
476
477 END CLN_NP_UTILS_PKG;