[Home] [Help]
PACKAGE BODY: APPS.CLN_NP_PROCESSOR_PKG
Source
1 PACKAGE BODY CLN_NP_PROCESSOR_PKG AS
2 /* $Header: ECXNPNPB.pls 120.0 2005/08/25 04:47:39 nparihar noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4
5 --
6 -- Package
7 -- CLN_NP_PROCESSOR_PKG_NEW
8 --
9 -- Purpose
10 -- Spec of package CLN_NP_PROCESSOR_PKG_NEW
11 -- Based on the latesy CH enhancements, notification processing has been modified.
12 -- History
13 -- Mar-22-2001 Kodanda Ram Created
14 --
15
16 -- This internal procedue fetches the next reason code in l_reason_code and updates the
17 -- l_position_code and l_all_reason_codes accordingly
18 PROCEDURE NEXT_PART(
19 l_position_code IN OUT NOCOPY NUMBER,
20 l_all_reason_codes IN OUT NOCOPY VARCHAR2,
21 l_reason_code IN OUT NOCOPY VARCHAR2)
22 IS
23 BEGIN
24 l_position_code := instr(l_all_reason_codes, fnd_global.local_chr(127));
25 IF l_position_code = 0 THEN
26 l_reason_code := l_all_reason_codes;
27 l_all_reason_codes := NULL;
28 ELSE
29 l_reason_code := substr(l_all_reason_codes, 0, l_position_code-1);
30 l_all_reason_codes := substr(l_all_reason_codes, l_position_code+1);
31 END IF;
32 END NEXT_PART;
33
34
35
36
37 -- This procedure sends E-mail thru the workflow - NOTIFIY_SOMEONE
38 PROCEDURE SEND_MAIL(
39 p_admin_or_tp IN VARCHAR2,
40 p_role IN VARCHAR2,
41 p_notification_code IN VARCHAR2,
42 p_notification_desc IN VARCHAR2,
43 p_notification_mesg IN VARCHAR2,
44 p_application_name IN VARCHAR2,
45 p_org_id IN VARCHAR2,
46 p_document_number IN VARCHAR2,
47 p_revision_number IN VARCHAR2,
48 p_release_number IN VARCHAR2,
49 p_collaboration_id IN VARCHAR2,
50 p_collaboration_type IN VARCHAR2)
51 IS
52 l_notification_flow_key NUMBER;
53 l_debug_mode VARCHAR2(255);
54 l_embedded_notif_screen VARCHAR2(5);
55 BEGIN
56 -- Sets the debug mode to FILE
57 --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
58 IF (l_Debug_Level <= 2) THEN
59 ecx_cln_debug_pub.Add('ENTERING CLN_NP_PROCESSOR_PKG.SEND_MAIL', 2);
60 END IF;
61
62 IF (l_Debug_Level <= 1) THEN
63 ecx_cln_debug_pub.Add('E-Mail to be sent to :' || p_role , 1);
64 ecx_cln_debug_pub.Add('With the following parameters :', 1);
65 ecx_cln_debug_pub.Add('TO_TP_OR_ADMIN :' || p_admin_or_tp, 1);
66 ecx_cln_debug_pub.Add('P_Role :' || p_role, 1);
67 ecx_cln_debug_pub.Add('NOTIFICATION_CODE :' || p_notification_code, 1);
68 ecx_cln_debug_pub.Add('NOTIFICATION_DESC :' || p_notification_desc, 1);
69 ecx_cln_debug_pub.Add('NOTIFICATION_MESSAGE :' || p_notification_mesg, 1);
70 ecx_cln_debug_pub.Add('APPLICATION_NAME :' || p_application_name, 1);
71 ecx_cln_debug_pub.Add('ORG_ID :' || p_org_id, 1);
72 ecx_cln_debug_pub.Add('DOCUMENT_NUMBER :' || p_document_number, 1);
73 ecx_cln_debug_pub.Add('REVISION_NUMBER :' || p_revision_number, 1);
74 ecx_cln_debug_pub.Add('RELEASE_NUMBER :' || p_revision_number, 1);
75 ecx_cln_debug_pub.Add('RELEASE_NUMBER :' || p_release_number, 1);
76 ecx_cln_debug_pub.Add('COLLABORATION_ID :' || p_collaboration_id, 1);
77 ecx_cln_debug_pub.Add('COLLABORATION_TYPE :' || p_collaboration_type, 1);
78 END IF;
79
80 l_embedded_notif_screen := FND_PROFILE.VALUE('CLN_EMBEDDED_NOT_SCREEN');
81 IF (l_Debug_Level <= 1) THEN
82 ecx_cln_debug_pub.Add('Embedded Notification reqd :' || l_embedded_notif_screen , 1);
83 END IF;
84
85 SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
86
87 IF(l_embedded_notif_screen = 'N') THEN
88 IF (l_Debug_Level <= 1) THEN
89 ecx_cln_debug_pub.Add('Calling CLN_NPNP/NOTIFY_SOMEONE', 1);
90 END IF;
91
92 WF_ENGINE.CreateProcess('CLN_NPNP', l_notification_flow_key, 'NOTIFY_SOMEONE');
93 ELSE
94 IF (l_Debug_Level <= 1) THEN
95 ecx_cln_debug_pub.Add('Calling CLN_NPNP/NOTIFY_SOMEONE_EMBEDDED_RGN', 1);
96 END IF;
97
98 WF_ENGINE.CreateProcess('CLN_NPNP', l_notification_flow_key, 'NOTIFY_SOMEONE_EMBEDDED_RGN');
99 END IF;
100
101 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'TO_TP_OR_ADMIN', p_admin_or_tp);
102 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'NOTIFICATION_CODE', p_notification_code);
103 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'NOTIFICATION_DESC', p_notification_desc);
104 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'NOTIFICATION_MESSAGE', p_notification_mesg);
105 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'APPLICATION_NAME', p_application_name);
106 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'ORG_ID', p_org_id);
107 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'DOCUMENT_NUMBER', p_document_number);
108 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'REVISION_NUMBER', p_revision_number);
109 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'RELEASE_NUMBER', p_release_number);
110 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'COLLABORATION_ID', p_collaboration_id);
111 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'COLLABORATION_TYPE', p_collaboration_type);
112 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'CLN_PERFORMER', p_role);
113 WF_ENGINE.StartProcess('CLN_NPNP', l_notification_flow_key);
114
115 -- check the profile option whether the embedded region shd be sent or not
116 -- if not then continue with the old code and if no, then we call different process
117
118
119 IF (l_Debug_Level <= 2) THEN
120 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.SEND_MAIL', 2);
121 END IF;
122
123 END SEND_MAIL;
124
125
126 -- Name
127 -- TAKE_ACTIONS_INTERNAL
128 -- Purpose
129 -- This procedure handles a notification by executing all the actions defined by the user
130 -- for this notification code. To make use of this API, teams should pass either p_coll_id,
131 -- or p_reference or p_int_con_no that will be used to identify the collaboration uniquely.
132 --
133 -- Arguments
134 --
135 -- Notes
136 -- No specific notes
137
138 PROCEDURE TAKE_ACTIONS_INTERNAL(
139 x_ret_code OUT NOCOPY VARCHAR2,
140 x_ret_desc OUT NOCOPY VARCHAR2,
141 p_notification_code IN VARCHAR2,
142 p_notification_desc IN VARCHAR2,
143 p_tp_id IN VARCHAR2,
144 p_reference IN VARCHAR2,
145 p_statuslvl IN VARCHAR2,
146 p_header_desc IN VARCHAR2,
147 p_update_collaboration_flag IN BOOLEAN,
148 p_update_coll_mess_flag IN BOOLEAN,
149 p_all_notification_codes IN VARCHAR2,
150 p_int_con_no IN VARCHAR2,
151 p_coll_point IN VARCHAR2,
152 p_doc_dir IN VARCHAR2,
153 p_coll_id IN NUMBER,
154 x_dtl_coll_id IN OUT NOCOPY VARCHAR2,
155 p_collaboration_standard IN VARCHAR2,
156 p_notification_event IN WF_EVENT_T,
157 p_application_id IN NUMBER )
158 IS
159 l_application_id NUMBER(10);
160 l_application_name VARCHAR2(100);
161 l_collaboration_type VARCHAR2(30);
162 l_document_owner VARCHAR2(30);
163 l_notification_flow_key NUMBER(20);
164 l_concurrent_request_sts NUMBER;
165 l_email VARCHAR2(255);
166 l_procedure_call_statement VARCHAR2(255);
167 l_cln_not_parameters wf_parameter_list_t;
168 l_org_id VARCHAR2(100);
169 l_document_number VARCHAR2(100);
170 l_revision_number VARCHAR2(100);
171 l_release_number VARCHAR2(100);
172 l_collaboration_id VARCHAR2(100);
173 l_doc_type VARCHAR2(100);
174 l_document_status VARCHAR2(100);
175 l_collaboration_status VARCHAR2(10);
176 l_all_notification_codes VARCHAR2(100);
177 l_notification_updation_code VARCHAR2(100);
178 l_delivery_confirmation_code VARCHAR2(100);
179 l_notify_default_admin_flag BOOLEAN;
180 l_tp_id VARCHAR2(100);
181 l_doc_dir VARCHAR2(5);
182 l_return_status VARCHAR2(1000);
183 l_msg_data VARCHAR2(1000);
184 l_debug_mode VARCHAR2(255);
185 l_msg_id VARCHAR2(255);
186 l_xmlg_transaction_type VARCHAR2(100);
187 l_xmlg_transaction_subtype VARCHAR2(100);
188 l_xmlg_document_id VARCHAR2(255);
189 l_ret_code NUMBER;
190 l_ret_msg VARCHAR2(1000);
191 l_error_code NUMBER;
192 l_error_msg VARCHAR2(1000);
193 l_admin_email VARCHAR2(1000);
194 l_role VARCHAR2(1000);
195 l_temp VARCHAR2(100);
196
197 -- Cursor to retrieve all the user defined actions
198 CURSOR Get_ACTIONS( p_notification_code VARCHAR2, p_coll_point VARCHAR2, p_application_id NUMBER, p_collaboration_type VARCHAR2) IS
199 SELECT codes.notification_message,details.action_dtl_id, details.action_code, details.attribute1,
200 details.attribute2, details.attribute3, details.attribute4, details.attribute5, details.attribute6,
201 details.attribute7, details.attribute8, details.attribute9, details.attribute10, details.attribute11,
202 details.attribute12, details.attribute13, details.attribute14, details.attribute15
203 FROM CLN_NOTIFICATION_CODES codes, CLN_NOTIFICATION_ACTION_HDR header, CLN_NOTIFICATION_ACTION_DTL details
204 WHERE codes.NOTIFICATION_CODE = p_notification_code and codes.collaboration_point = p_coll_point
205 and header.notification_id = codes.notification_id and header.application_id = p_application_id
206 and header.collaboration_type = p_collaboration_type and header.ACTION_HDR_ID = details.ACTION_HDR_ID
207 and details.active_flag = 'Y'
208 ORDER BY details.ACTION_DTL_ID;
209
210 BEGIN
211 -- Sets the debug mode to be FILE
212 --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
213 IF (l_Debug_Level <= 2) THEN
214 ecx_cln_debug_pub.Add('ENTERING CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS_INTERNAL', 2);
215 END IF;
216
217 IF (l_Debug_Level <= 1) THEN
218 ecx_cln_debug_pub.Add('With the following parameters:', 1);
219 ecx_cln_debug_pub.Add('p_notification_code:' || p_notification_code, 1);
220 ecx_cln_debug_pub.Add('p_notification_desc:' || p_notification_desc, 1);
221 ecx_cln_debug_pub.Add('p_tp_id:' || p_tp_id, 1);
222 ecx_cln_debug_pub.Add('p_reference:' || p_reference, 1);
223 ecx_cln_debug_pub.Add('p_statuslvl:' || p_statuslvl, 1);
224 ecx_cln_debug_pub.Add('p_header_desc:' || p_header_desc, 1);
225 ecx_cln_debug_pub.Add('p_collaboration_standard:' || p_collaboration_standard, 1);
226 END IF;
227
228
229 IF(p_update_collaboration_flag) THEN
230 IF (l_Debug_Level <= 1) THEN
231 ecx_cln_debug_pub.Add('p_update_collaboration_flag:TRUE', 1);
232 END IF;
233 ELSE
234 IF (l_Debug_Level <= 1) THEN
235 ecx_cln_debug_pub.Add('p_update_collaboration_flag:FALSE', 1);
236 END IF;
237 END IF;
238
239 IF(p_update_coll_mess_flag) THEN
240 IF (l_Debug_Level <= 1) THEN
241 ecx_cln_debug_pub.Add('p_update_coll_mess_flag:TRUE', 1);
242 END IF;
243 ELSE
244 IF (l_Debug_Level <= 1) THEN
245 ecx_cln_debug_pub.Add('p_update_coll_mess_flag:FALSE', 1);
246 END IF;
247 END IF;
248
249 IF (l_Debug_Level <= 1) THEN
250 ecx_cln_debug_pub.Add('p_all_notification_codes:' || p_all_notification_codes, 1);
251 ecx_cln_debug_pub.Add('p_int_con_no:' || p_int_con_no, 1);
252 ecx_cln_debug_pub.Add('p_coll_point:' || p_coll_point, 1);
253 ecx_cln_debug_pub.Add('p_doc_dir:' || p_doc_dir, 1);
254 ecx_cln_debug_pub.Add('Collaboration ID:' || p_coll_id, 1);
255 END IF;
256
257
258 x_ret_code := FND_API.G_RET_STS_SUCCESS;
259 FND_MESSAGE.SET_NAME('CLN', 'CLN_SUCCESS'); -- 'Success'
260 x_ret_desc := FND_MESSAGE.GET;
261
262
263 BEGIN
264 IF (l_Debug_Level <= 1) THEN
265 ecx_cln_debug_pub.Add('Take Actions :' || p_notification_code, 1);
266 END IF;
267
268 -- Query collaboration history header for information using the reference
269 IF ( (p_coll_id IS NOT NULL) AND (LENGTH(TRIM(p_coll_id)) <> 0) ) THEN
270 SELECT application_id, collaboration_type, org_id, document_no,
271 doc_revision_no, release_no, collaboration_id, document_owner
272 INTO l_application_id, l_collaboration_type, l_org_id, l_document_number,
273 l_revision_number, l_release_number, l_collaboration_id, l_document_owner
274 FROM CLN_COLL_HIST_HDR
275 WHERE collaboration_id = p_coll_id;
276 ELSIF ( (p_reference IS NOT NULL) AND (LENGTH(TRIM(p_reference)) <> 0) ) THEN
277 SELECT application_id, collaboration_type, org_id, document_no,
278 doc_revision_no, release_no, collaboration_id,document_owner
279 INTO l_application_id, l_collaboration_type, l_org_id, l_document_number,
280 l_revision_number, l_release_number, l_collaboration_id, l_document_owner
281 FROM CLN_COLL_HIST_HDR
282 WHERE APPLICATION_REFERENCE_ID = p_reference;
283 ELSIF (p_int_con_no IS NOT NULL) THEN -- added 28 June 2004.
284 SELECT application_id, collaboration_type, org_id, document_no,
285 doc_revision_no, release_no, collaboration_id, l_document_owner
286 INTO l_application_id, l_collaboration_type, l_org_id, l_document_number,
287 l_revision_number, l_release_number, l_collaboration_id, l_document_owner
288 FROM CLN_COLL_HIST_HDR
289 WHERE xmlg_internal_control_number = p_int_con_no;
290 END IF;
291
292 l_tp_id := p_tp_id;
293
294 IF p_tp_id is NULL THEN
295 GET_TRADING_PARTNER_DETAILS( l_return_status, l_ret_msg, p_int_con_no, l_tp_id);
296 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
297 -- x_ret_desc := l_ret_msg;
298 -- RAISE FND_API.G_EXC_ERROR;
299 l_tp_id := NULL;
300 END IF;
301 END IF;
302
303 IF (l_Debug_Level <= 1) THEN
304 ecx_cln_debug_pub.Add('l_tp_id:' || l_tp_id, 1);
305 ecx_cln_debug_pub.Add('Queried the following from Collaboration History', 1);
306 ecx_cln_debug_pub.Add('l_application_id:' || l_application_id, 1);
307 ecx_cln_debug_pub.Add('l_collaboration_type:' || l_collaboration_type, 1);
308 ecx_cln_debug_pub.Add('l_org_id:' || l_org_id, 1);
309 ecx_cln_debug_pub.Add('l_document_number:' || l_document_number, 1);
310 ecx_cln_debug_pub.Add('l_revision_number:' || l_revision_number, 1);
311 ecx_cln_debug_pub.Add('l_release_number:' || l_release_number, 1);
312 ecx_cln_debug_pub.Add('l_collaboration_id:' || l_collaboration_id, 1);
313 END IF;
314
315
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 -- INVALID REFERENCE
319 FND_MESSAGE.SET_NAME('CLN', 'CLN_INVALID_REFERENCE'); -- 'Invalid reference'
320 x_ret_desc := FND_MESSAGE.GET;
321
322 IF (l_Debug_Level <= 1) THEN
323 ecx_cln_debug_pub.Add('Collaboration does not exist or Invalid Collaboration', 1);
324 END IF;
325
326 -- removed 28June 2004.Now, we intend to support the case
327 -- when no collaboration is there
328 --RAISE FND_API.G_EXC_ERROR;
329 END;
330
331 -- Obtain the value of CLN_DELIVERY_CONFIRMATION_CODE profile option - Default B2B_02
332 l_delivery_confirmation_code := FND_PROFILE.VALUE('CLN_DELIVERY_CONFIRMATION_CODE');
333
334 IF l_delivery_confirmation_code IS NULL THEN
335
336 FND_MESSAGE.SET_NAME('CLN', 'CLN_PO_DEL_CONFIRM_NOT_FOUND');
337 -- 'Profile option - CLN_DELIVERY_CONFIRMATION_CODE - Not found'
338 x_ret_desc := FND_MESSAGE.GET;
339 RAISE FND_API.G_EXC_ERROR;
340
341 END IF;
342
343 IF (l_Debug_Level <= 1) THEN
344 ecx_cln_debug_pub.Add('Profile option - CLN_DELIVERY_CONFIRMATION_CODE:' || l_delivery_confirmation_code, 1);
345 END IF;
346
347
348 -- If the notification code is delivery confirmation code then call delivery confirmation API
349 IF p_notification_code = l_delivery_confirmation_code THEN
350
351 -- Get the txn type, txn subtype, xmlg doc id, xmlg msg id
352 -- of the last outbound message for this collaboration
353 SELECT xmlg_transaction_type, xmlg_transaction_subtype, xmlg_document_id, xmlg_msg_id
354 INTO l_xmlg_transaction_type, l_xmlg_transaction_subtype, l_xmlg_document_id, l_msg_id
355 FROM CLN_COLL_HIST_DTL where collaboration_dtl_id =
356 (SELECT MAX(collaboration_dtl_id) FROM CLN_COLL_HIST_DTL
357 WHERE document_direction = 'OUT' AND collaboration_id = l_collaboration_id);
358
359 IF (l_Debug_Level <= 1) THEN
360 ecx_cln_debug_pub.Add('l_xmlg_transaction_type:' || l_xmlg_transaction_type, 1);
361 ecx_cln_debug_pub.Add('l_xmlg_transaction_subtype:' || l_xmlg_transaction_subtype, 1);
362 ecx_cln_debug_pub.Add('l_xmlg_document_id:' || l_xmlg_document_id, 1);
363 END IF;
364
365
366 -- We are assuming that the first message after any out bound will be delviery confirmation
367 IF (l_msg_id IS NULL ) OR (TRIM(l_msg_id) = '') THEN
368 BEGIN
369 -- Query ECX_DOCLOGS for message ID using transaction type , transaction subtype and document id
370 SELECT msgid
371 INTO l_msg_id
372 FROM ECX_DOCLOGS
373 WHERE transaction_type = l_xmlg_transaction_type AND
374 transaction_subtype = l_xmlg_transaction_subtype AND document_number = l_xmlg_document_id
375 AND direction = 'OUT';
376 IF (l_Debug_Level <= 1) THEN
377 ecx_cln_debug_pub.Add('Quried ECX_DOCLOGS for l_msg_id:' || l_msg_id, 1);
378 END IF;
379
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 -- INVALID TRANSACTION TYPE,TRANSACTION SUBTYPE AND DOCUMENT ID
383 FND_MESSAGE.SET_NAME('CLN', 'CLN_INVALID_TRAN_DATA');
384 -- 'Unable to call delivery confirmation: Invalid transaction type, transaction subtype and document id'
385 x_ret_desc := FND_MESSAGE.GET;
386 RAISE FND_API.G_EXC_ERROR;
387 END;
388 END IF;
389
390 -- Call Delivery Confirmation API
391 ECX_ERRORLOG.external_system(l_msg_id, 0, 'Success', sysdate, l_ret_code, l_ret_msg);
392 IF l_ret_code <> 0 THEN
393 FND_MESSAGE.SET_NAME('CLN', 'CLN_ERROR_DELIVERY_CONFIRM_API');
394 -- 'Error while calling delivery confirmation API:' || l_ret_msg;
395 FND_MESSAGE.SET_TOKEN('ERRMESSAGE', l_ret_msg);
396 x_ret_desc := FND_MESSAGE.GET;
397 RAISE FND_API.G_EXC_ERROR;
398 END IF;
399 IF (l_Debug_Level <= 1) THEN
400 ecx_cln_debug_pub.Add('Executed delivery confirmation API:' || l_ret_code || ':' || l_ret_msg, 1);
401 END IF;
402
403 END IF; -- Delivery Comfirmation
404
405
406 IF p_update_collaboration_flag THEN
407 IF p_statuslvl = '00' THEN
408 l_document_status := 'SUCCESS';
409 l_collaboration_status := 'STARTED';
410 ELSIF p_statuslvl = '99' THEN
411 l_document_status := 'ERROR';
412 l_collaboration_status := 'ERROR';
413 END IF;
414
415 IF (l_Debug_Level <= 1) THEN
416 ecx_cln_debug_pub.Add('l_document_status:' || l_document_status, 1);
417 ecx_cln_debug_pub.Add('l_collaboration_status:' || l_collaboration_status, 1);
418 END IF;
419
420 l_all_notification_codes := rtrim(p_all_notification_codes);
421
422 IF ( substr(l_all_notification_codes,-1) )= ':' THEN
423 l_all_notification_codes := substr( l_all_notification_codes, 0, length(l_all_notification_codes) - 1);
424 END IF;
425
426 IF l_tp_id IS NULL THEN
427 IF p_coll_point = 'B2B_SERVER' THEN
428 l_doc_type := 'CONFIRM_BOD';
429 l_doc_dir := 'IN';
430 ELSE
431 l_doc_type := null;
432 l_doc_dir := p_doc_dir;
433 END IF;
434
435 IF (l_Debug_Level <= 2) THEN
436 ecx_cln_debug_pub.Add('INVOKING CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION', 2);
437 END IF;
438
439 CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
440 x_return_status => l_return_status,
441 x_msg_data => l_msg_data,
442 p_msg_text => p_header_desc,
443 p_coll_status => l_collaboration_status,
444 p_doc_type => l_doc_type,
445 p_doc_dir => l_doc_dir,
446 p_coll_pt => p_coll_point,
447 p_doc_status => l_document_status,
448 p_notification_id => l_all_notification_codes,
449 p_coll_id => l_collaboration_id,
450 p_xmlg_internal_control_number => p_int_con_no,
451 p_xmlg_msg_id => NULL,
452 p_rosettanet_check_required => FALSE,
453 x_dtl_coll_id => x_dtl_coll_id,
454 p_collaboration_standard => p_collaboration_standard);
455 ELSE
456 IF (l_Debug_Level <= 2) THEN
457 ecx_cln_debug_pub.Add('INVOKING CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION', 2);
458 END IF;
459
460 CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
461 x_return_status => l_return_status,
462 x_msg_data => l_msg_data,
463 p_msg_text => p_header_desc,
464 p_coll_pt => p_coll_point,
465 p_doc_status => l_document_status,
466 p_notification_id => l_all_notification_codes,
467 p_coll_id => l_collaboration_id,
468 p_xmlg_internal_control_number => p_int_con_no,
469 p_xmlg_msg_id => NULL,
470 p_rosettanet_check_required => FALSE,
471 x_dtl_coll_id => x_dtl_coll_id,
472 p_collaboration_standard => p_collaboration_standard);
473 END IF;
474
475 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
476 x_ret_desc := l_msg_data;
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479
480 IF (l_Debug_Level <= 2) THEN
481 ecx_cln_debug_pub.Add('COMPLETED CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION', 2);
482 END IF;
483
484 END IF;
485
486 -- Add messages
487 IF p_update_coll_mess_flag THEN
488 CLN_CH_COLLABORATION_PKG.ADD_COLLABORATION_MESSAGES(
489 x_return_status => l_return_status,
490 x_msg_data => l_msg_data,
491 p_dtl_coll_id => x_dtl_coll_id,
492 p_ref1 => p_notification_code,
493 p_dtl_msg => p_notification_desc);
494
495 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
496 x_ret_desc := l_msg_data;
497 RAISE FND_API.G_EXC_ERROR;
498 END IF;
499
500 IF (l_Debug_Level <= 2) THEN
501 ecx_cln_debug_pub.Add('COMPLETED CLN_CH_COLLABORATION_PKG.ADD_COLLABORATION_MESSAGES', 2);
502 END IF;
503
504 END IF;
505
506 -- Query fnd_application_vl for application name using application id
507 BEGIN
508 SELECT application_name INTO l_application_name
509 FROM fnd_application_vl
510 WHERE application_id = l_application_id;
511 IF (l_Debug_Level <= 1) THEN
512 ecx_cln_debug_pub.Add('Queried the following from fnd_application_vl using application id:'|| l_application_id, 1);
513 ecx_cln_debug_pub.Add('l_application_name:' || l_application_name, 1);
514 END IF;
515
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 -- INVALID APPLICATION ID
519 FND_MESSAGE.SET_NAME('CLN', 'CLN_INVALID_APPL_ID'); -- 'Invalid application id'
520 x_ret_desc := FND_MESSAGE.GET;
521 RAISE FND_API.G_EXC_ERROR;
522 END;
523
524
525 IF (l_Debug_Level <= 1) THEN
526 ecx_cln_debug_pub.Add('Action parameters:');
527 ecx_cln_debug_pub.Add('APPLICATION_ID:' || l_application_id);
528 ecx_cln_debug_pub.Add('COLLABORATION_ID:' || l_collaboration_id);
529 ecx_cln_debug_pub.Add('COLLABORATION_TYPE:' || l_collaboration_type);
530 ecx_cln_debug_pub.Add('REFERENCE_ID:' || p_reference);
531 ecx_cln_debug_pub.Add('TRADING_PARTNER_ID:' || l_tp_id);
532 ecx_cln_debug_pub.Add('HEADER_DESCRIPTION:' || p_header_desc);
533 ecx_cln_debug_pub.Add('NOTIFICATION_DESCRIPTION:' || p_notification_desc);
534 ecx_cln_debug_pub.Add('NOTIFICATION_CODE:' || p_notification_code);
535 ecx_cln_debug_pub.Add('STATUS:' || p_statuslvl);
536 END IF;
537
538 -- if notification code is not null then only do this step
539 -- else check if any of the three Send_mail_tp/ send_mail_admin/ send_mail_docowner
540 -- is set, we call SEND_MAIL API.
541 FOR c_actions IN Get_actions(p_notification_code, p_coll_point, l_application_id, l_collaboration_type) LOOP
542 BEGIN
543 SAVEPOINT ACTION;
544 IF (l_Debug_Level <= 1) THEN
545 ecx_cln_debug_pub.Add('Obtained cursor row for action code:' || c_actions.action_code, 1);
546 END IF;
547
548
549 IF c_actions.action_code = 'START_WORKFLOW' THEN
550 -- attribute1 => Item , attribute2 => Process
551 SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
552 IF (l_Debug_Level <= 2) THEN
553 ecx_cln_debug_pub.Add('INVOKING WF_ENGINE.CreateProcess:' || ':' || c_actions.attribute1 || l_notification_flow_key
554 || ':' || c_actions.attribute2, 2);
555 END IF;
556
557 WF_ENGINE.CreateProcess(c_actions.attribute1, l_notification_flow_key, c_actions.attribute2);
558 IF (l_Debug_Level <= 2) THEN
559 ecx_cln_debug_pub.Add('INVOKING WF_ENGINE.CreateProcess:' || ':' || c_actions.attribute1 || l_notification_flow_key || ':' || c_actions.attribute2, 2);
560 END IF;
561
562 -- Set attributes
563 -- pass on the object of type wf_event_t also here.
564 IF(p_notification_event IS NOT NULL) THEN
565 WF_ENGINE.SetItemAttrEvent(c_actions.attribute1,l_notification_flow_key, 'EVENT_OBJ', p_notification_event);
566 END IF;
567
568 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'APPLICATION_ID', l_application_id);
569 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'COLLABORATION_ID', l_collaboration_id);
570 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'COLLABORATION_TYPE', l_collaboration_type);
571 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'REFERENCE_ID', p_reference);
572 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'TRADING_PARTNER_ID', l_tp_id);
573 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'HEADER_DESCRIPTION', p_header_desc);
574 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'NOTIFICATION_DESCRIPTION', p_notification_desc);
575 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'NOTIFICATION_CODE', p_notification_code);
576 WF_ENGINE.SetItemAttrText(c_actions.attribute1,l_notification_flow_key, 'STATUS', p_statuslvl);
577 IF (l_Debug_Level <= 2) THEN
578 ecx_cln_debug_pub.Add('INVOKING WF_ENGINE.StartProcess:' || c_actions.attribute1 || ':' || l_notification_flow_key, 2);
579 END IF;
580
581 WF_ENGINE.StartProcess(c_actions.attribute1,l_notification_flow_key);
582 IF (l_Debug_Level <= 2) THEN
583 ecx_cln_debug_pub.Add('COMPLETED WF_ENGINE.StartProcess:' || c_actions.attribute1 || ':' || l_notification_flow_key, 2);
584 END IF;
585 ELSIF c_actions.action_code = 'NOTIFY_ADMINISTRATOR' THEN
586 l_notify_default_admin_flag := TRUE;
587 IF c_actions.attribute1 IS NOT NULL THEN
588 SEND_MAIL('Administrator', c_actions.attribute1, p_notification_code, c_actions.notification_message, p_notification_desc,
589 l_application_name, l_org_id, l_document_number, l_revision_number,
590 l_release_number, l_collaboration_id, l_collaboration_type);
591 l_notify_default_admin_flag := FALSE;
592 END IF;
593 IF c_actions.attribute2 IS NOT NULL THEN
594 SEND_MAIL('Administrator', c_actions.attribute2, p_notification_code, c_actions.notification_message, p_notification_desc,
595 l_application_name, l_org_id, l_document_number, l_revision_number,
596 l_release_number, l_collaboration_id, l_collaboration_type);
597 l_notify_default_admin_flag := FALSE;
598 END IF;
599 IF c_actions.attribute3 IS NOT NULL THEN
600 SEND_MAIL('Administrator', c_actions.attribute3, p_notification_code, c_actions.notification_message, p_notification_desc,
601 l_application_name, l_org_id, l_document_number, l_revision_number,
602 l_release_number, l_collaboration_id, l_collaboration_type);
603 l_notify_default_admin_flag := FALSE;
604 END IF;
605 IF c_actions.attribute4 IS NOT NULL THEN
606 SEND_MAIL('Administrator', c_actions.attribute4, p_notification_code, c_actions.notification_message, p_notification_desc,
607 l_application_name, l_org_id, l_document_number, l_revision_number,
608 l_release_number, l_collaboration_id, l_collaboration_type);
609 l_notify_default_admin_flag := FALSE;
610 END IF;
611 IF c_actions.attribute5 IS NOT NULL THEN
612 SEND_MAIL('Administrator', c_actions.attribute5, p_notification_code, c_actions.notification_message, p_notification_desc,
613 l_application_name, l_org_id, l_document_number, l_revision_number,
614 l_release_number, l_collaboration_id, l_collaboration_type);
615 l_notify_default_admin_flag := FALSE;
616 END IF;
617 IF c_actions.attribute6 IS NOT NULL THEN
618 SEND_MAIL('Administrator', c_actions.attribute6, p_notification_code, c_actions.notification_message, p_notification_desc,
619 l_application_name, l_org_id, l_document_number, l_revision_number,
620 l_release_number, l_collaboration_id, l_collaboration_type);
621 l_notify_default_admin_flag := FALSE;
622 END IF;
623 IF c_actions.attribute7 IS NOT NULL THEN
624 SEND_MAIL('Administrator', c_actions.attribute7, p_notification_code, c_actions.notification_message, p_notification_desc,
625 l_application_name, l_org_id, l_document_number, l_revision_number,
626 l_release_number, l_collaboration_id, l_collaboration_type);
627 l_notify_default_admin_flag := FALSE;
628 END IF;
629 IF c_actions.attribute8 IS NOT NULL THEN
630 SEND_MAIL('Administrator', c_actions.attribute8, p_notification_code, c_actions.notification_message, p_notification_desc,
631 l_application_name, l_org_id, l_document_number, l_revision_number,
632 l_release_number, l_collaboration_id, l_collaboration_type);
633 l_notify_default_admin_flag := FALSE;
634 END IF;
635 IF c_actions.attribute9 IS NOT NULL THEN
636 SEND_MAIL('Administrator', c_actions.attribute9, p_notification_code, c_actions.notification_message, p_notification_desc,
637 l_application_name, l_org_id, l_document_number, l_revision_number,
638 l_release_number, l_collaboration_id, l_collaboration_type);
639 l_notify_default_admin_flag := FALSE;
640 END IF;
641 IF c_actions.attribute10 IS NOT NULL THEN
642 SEND_MAIL('Administrator', c_actions.attribute10, p_notification_code, c_actions.notification_message, p_notification_desc,
643 l_application_name, l_org_id, l_document_number, l_revision_number,
644 l_release_number, l_collaboration_id, l_collaboration_type);
645 l_notify_default_admin_flag := FALSE;
646 END IF;
647 IF c_actions.attribute11 IS NOT NULL THEN
648 SEND_MAIL('Administrator', c_actions.attribute11, p_notification_code, c_actions.notification_message, p_notification_desc,
649 l_application_name, l_org_id, l_document_number, l_revision_number,
650 l_release_number, l_collaboration_id, l_collaboration_type);
651 l_notify_default_admin_flag := FALSE;
652 END IF;
653 IF c_actions.attribute12 IS NOT NULL THEN
654 SEND_MAIL('Administrator', c_actions.attribute12, p_notification_code, c_actions.notification_message, p_notification_desc,
655 l_application_name, l_org_id, l_document_number, l_revision_number,
656 l_release_number, l_collaboration_id, l_collaboration_type);
657 l_notify_default_admin_flag := FALSE;
658 END IF;
659 IF c_actions.attribute13 IS NOT NULL THEN
660 SEND_MAIL('Administrator', c_actions.attribute13, p_notification_code, c_actions.notification_message, p_notification_desc,
661 l_application_name, l_org_id, l_document_number, l_revision_number,
662 l_release_number, l_collaboration_id, l_collaboration_type);
663 l_notify_default_admin_flag := FALSE;
664 END IF;
665 IF c_actions.attribute14 IS NOT NULL THEN
666 SEND_MAIL('Administrator', c_actions.attribute14, p_notification_code, c_actions.notification_message, p_notification_desc,
667 l_application_name, l_org_id, l_document_number, l_revision_number,
668 l_release_number, l_collaboration_id, l_collaboration_type);
669 l_notify_default_admin_flag := FALSE;
670 END IF;
671 IF c_actions.attribute15 IS NOT NULL THEN
672 SEND_MAIL('Administrator', c_actions.attribute15, p_notification_code, c_actions.notification_message, p_notification_desc,
673 l_application_name, l_org_id, l_document_number, l_revision_number,
674 l_release_number, l_collaboration_id, l_collaboration_type);
675 l_notify_default_admin_flag := FALSE;
676 END IF;
677 IF l_notify_default_admin_flag = TRUE THEN
678 -- Get administrator e-mail from profile value
679 IF (l_Debug_Level <= 1) THEN
680 ecx_cln_debug_pub.Add('Administrator Roles not specified', 1);
681 END IF;
682
683 l_role := FND_PROFILE.VALUE('CLN_ADMINISTRATOR');
684 IF (l_Debug_Level <= 1) THEN
685 ecx_cln_debug_pub.Add('Administrator Profile Role or E-Mail:' || l_email, 1);
686 END IF;
687 BEGIN
688 SELECT 'x'
689 INTO l_temp
690 FROM WF_ROLES
691 WHERE NAME = l_role
692 AND rownum < 2;
693 EXCEPTION
694 WHEN NO_DATA_FOUND THEN
695 IF (l_Debug_Level <= 1) THEN
696 ecx_cln_debug_pub.Add('CLN Administrator Profile has email(not role)', 1);
697 END IF;
698 l_email := l_role;
699 l_role := 'CLN_ADMINISTRATOR';
700 SELECT email_address
701 INTO l_admin_email
702 FROM WF_USERS
703 WHERE NAME = 'CLN_ADMINISTRATOR';
704 IF (l_Debug_Level <= 1) THEN
705 ecx_cln_debug_pub.Add('Administrator Role E-Mail:' || l_admin_email, 1);
706 END IF;
707 IF( l_email <> l_admin_email or l_admin_email is null) THEN
708 WF_DIRECTORY.SetAdHocUserAttr (USER_NAME => l_role, EMAIL_ADDRESS => l_email);
709 END IF;
710
711 END;
712
713 SEND_MAIL('Administrator', l_role, p_notification_code, c_actions.notification_message, p_notification_desc,
714 l_application_name, l_org_id, l_document_number, l_revision_number,
715 l_release_number, l_collaboration_id, l_collaboration_type);
716 END IF;
717 ELSIF c_actions.action_code = 'NOTIFY_DOC_OWNER' THEN
718 BEGIN
719 IF (l_Debug_Level <= 1) THEN
720 ecx_cln_debug_pub.Add('Entering notify document Owner', 1);
721 END IF;
722
723 SELECT OWNER_ROLE
724 INTO l_role
725 FROM CLN_COLL_HIST_HDR
726 where COLLABORATION_ID = l_collaboration_id;
727
728 IF (l_Debug_Level <= 1) THEN
729 ecx_cln_debug_pub.Add('Notification Receiver '||l_role, 1);
730 END IF;
731
732 BEGIN
733 SELECT 'x'
734 INTO l_temp
735 FROM WF_ROLES
736 WHERE NAME = l_role
737 AND rownum < 2;
738 EXCEPTION
739 WHEN NO_DATA_FOUND THEN
740 IF (l_Debug_Level <= 1) THEN
741 ecx_cln_debug_pub.Add('CLN Administrator Profile has email(not role)', 1);
742 END IF;
743
744 l_email := l_role;
745 l_role := 'CLN_ADMINISTRATOR';
746
747 SELECT email_address
748 INTO l_admin_email
749 FROM WF_USERS
750 WHERE NAME = 'CLN_ADMINISTRATOR';
751
752 IF (l_Debug_Level <= 1) THEN
753 ecx_cln_debug_pub.Add('Administrator Role E-Mail:' || l_admin_email, 1);
754 END IF;
755
756 IF( l_email <> l_admin_email or l_admin_email is null) THEN
757 WF_DIRECTORY.SetAdHocUserAttr (USER_NAME => l_role, EMAIL_ADDRESS => l_email);
758 END IF;
759 END;
760
761 SEND_MAIL('Administrator', l_role, p_notification_code, c_actions.notification_message, p_notification_desc,
762 l_application_name, l_org_id, l_document_number, l_revision_number,
763 l_release_number, l_collaboration_id, l_collaboration_type);
764 EXCEPTION
765 WHEN NO_DATA_FOUND THEN
766 -- No Document Owner Found
767 IF (l_Debug_Level <= 4) THEN
768 ecx_cln_debug_pub.Add('No Document Owner Found', 3);
769 END IF;
770
771 END;
772 ELSIF c_actions.action_code = 'NOTIFY_TRADING_PARTNER' THEN
773 BEGIN
774 IF (l_Debug_Level <= 1) THEN
775 ecx_cln_debug_pub.Add('Entering notify trading partner', 1);
776 END IF;
777 SELECT company_admin_email
778 INTO l_email
779 FROM ecx_tp_headers
780 where tp_header_id = l_tp_id;
781 IF (l_Debug_Level <= 1) THEN
782 ecx_cln_debug_pub.Add('Queried the following from ecx_tp_headers using l_tp_id:' || l_tp_id, 1);
783 ecx_cln_debug_pub.Add('l_email:' || l_email, 1);
784 END IF;
785 l_role := null;
786 l_temp := null;
787 WF_DIRECTORY.CreateAdHocUser(name => l_role, display_name => l_temp, email_address => l_email, expiration_date => sysdate + 10);
788
789 IF (l_Debug_Level <= 1) THEN
790 ecx_cln_debug_pub.Add('Before callling send_mail to trading partner- trading partner role : ' || l_role, 1);
791 END IF;
792
793 SEND_MAIL('Trading Partner', l_role, p_notification_code, c_actions.notification_message, p_notification_desc,
794 l_application_name, l_org_id, l_document_number, l_revision_number,
795 l_release_number, l_collaboration_id, l_collaboration_type);
796 EXCEPTION
797 WHEN NO_DATA_FOUND THEN
798 -- Invalid trading partner
799 IF (l_Debug_Level <= 4) THEN
800 ecx_cln_debug_pub.Add('Invalid trading partner: Unable to notify trading partner:' || l_tp_id, 3);
801 END IF;
802
803 -- FND_MESSAGE.SET_NAME('CLN', 'CLN_INVALID_TRADING_PARTNER'); -- 'Invalid trading partner'
804 -- x_ret_desc := FND_MESSAGE.GET;
805 -- RAISE FND_API.G_EXC_ERROR;
806 END;
807 ELSIF c_actions.action_code = 'RAISE_EVENT' THEN
808 l_cln_not_parameters := wf_parameter_list_t();
809 WF_EVENT.AddParameterToList('ApplicationId', l_application_id, l_cln_not_parameters);
810 WF_EVENT.AddParameterToList('CollaborationId', l_collaboration_id, l_cln_not_parameters);
811 WF_EVENT.AddParameterToList('CollaborationType', l_collaboration_type, l_cln_not_parameters);
812 WF_EVENT.AddParameterToList('ReferenceId', p_reference, l_cln_not_parameters);
813 WF_EVENT.AddParameterToList('TradingPartnerID', l_tp_id, l_cln_not_parameters);
814 WF_EVENT.AddParameterToList('HeaderDescription', p_header_desc, l_cln_not_parameters);
815 WF_EVENT.AddParameterToList('NotificationDescription', p_notification_desc, l_cln_not_parameters);
816 WF_EVENT.AddParameterToList('NotificationCode', p_notification_code, l_cln_not_parameters);
817 WF_EVENT.AddParameterToList('Status', p_statuslvl, l_cln_not_parameters);
818 -- User defined parameters
819 IF c_actions.attribute2 IS NOT NULL THEN
820 WF_EVENT.AddParameterToList('Attribute1', c_actions.attribute2, l_cln_not_parameters);
821 END IF;
822 IF c_actions.attribute3 IS NOT NULL THEN
823 WF_EVENT.AddParameterToList('Attribute2', c_actions.attribute3, l_cln_not_parameters);
824 END IF;
825 IF c_actions.attribute4 IS NOT NULL THEN
826 WF_EVENT.AddParameterToList('Attribute3', c_actions.attribute4, l_cln_not_parameters);
827 END IF;
828 IF c_actions.attribute5 IS NOT NULL THEN
829 WF_EVENT.AddParameterToList('Attribute4', c_actions.attribute5, l_cln_not_parameters);
830 END IF;
831 IF c_actions.attribute6 IS NOT NULL THEN
832 WF_EVENT.AddParameterToList('Attribute5', c_actions.attribute6, l_cln_not_parameters);
833 END IF;
834 IF c_actions.attribute7 IS NOT NULL THEN
835 WF_EVENT.AddParameterToList('Attribute6', c_actions.attribute7, l_cln_not_parameters);
836 END IF;
837 IF c_actions.attribute8 IS NOT NULL THEN
838 WF_EVENT.AddParameterToList('Attribute7', c_actions.attribute8, l_cln_not_parameters);
839 END IF;
840 IF c_actions.attribute9 IS NOT NULL THEN
841 WF_EVENT.AddParameterToList('Attribute8', c_actions.attribute9, l_cln_not_parameters);
842 END IF;
843 IF c_actions.attribute10 IS NOT NULL THEN
844 WF_EVENT.AddParameterToList('Attribute9', c_actions.attribute10, l_cln_not_parameters);
845 END IF;
846 IF c_actions.attribute11 IS NOT NULL THEN
847 WF_EVENT.AddParameterToList('Attribute10', c_actions.attribute11, l_cln_not_parameters);
848 END IF;
849 IF c_actions.attribute12 IS NOT NULL THEN
850 WF_EVENT.AddParameterToList('Attribute11', c_actions.attribute12, l_cln_not_parameters);
851 END IF;
852 IF c_actions.attribute13 IS NOT NULL THEN
853 WF_EVENT.AddParameterToList('Attribute12', c_actions.attribute13, l_cln_not_parameters);
854 END IF;
855 IF c_actions.attribute14 IS NOT NULL THEN
856 WF_EVENT.AddParameterToList('Attribute13', c_actions.attribute14, l_cln_not_parameters);
857 END IF;
858 IF c_actions.attribute15 IS NOT NULL THEN
859 WF_EVENT.AddParameterToList('Attribute14', c_actions.attribute15, l_cln_not_parameters);
860 END IF;
861 SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
862 WF_EVENT.Raise(c_actions.attribute1, l_notification_flow_key, NULL, l_cln_not_parameters, NULL);
863 ELSIF c_actions.action_code = 'PROCEDURE_CALL' THEN
864 IF (l_Debug_Level <= 1) THEN
865 ecx_cln_debug_pub.Add('UserDefined PL/SQL API :' || c_actions.attribute1, 1);
866 ecx_cln_debug_pub.Add('UserDefined PL/SQL API Mode:' || c_actions.attribute3, 1);
867 END IF;
868
869 IF c_actions.attribute3 = 'CONCURRENT' THEN
870 SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
871 IF (l_Debug_Level <= 2) THEN
872 ecx_cln_debug_pub.Add('INVOKING WF_ENGINE.CreateProcess:' || ':CLN_NP' || l_notification_flow_key || ':CALL_API_CONC', 2);
873 END IF;
874
875 WF_ENGINE.CreateProcess('CLN_NPNP', l_notification_flow_key, 'CALL_API_CONC');
876 IF (l_Debug_Level <= 2) THEN
877 ecx_cln_debug_pub.Add('COMPLETED WF_ENGINE.CreateProcess:' || ':CLN_NP' || l_notification_flow_key || ':CALL_API_CONC', 2);
878 END IF;
879
880 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'PROCEDURE_NAME', c_actions.attribute1);
881 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'APPLICATION_ID', l_application_id);
882 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'COLLABORATION_ID', l_collaboration_id);
883 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'COLLABORATION_TYPE', l_collaboration_type);
884 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'REFERENCE_ID', p_reference);
885 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'TRADING_PARTNER_ID', l_tp_id);
886 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'HEADER_DESC', p_header_desc);
887 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'NOTIFICATION_DESC', p_notification_desc);
888 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'NOTIFICATION_CODE', p_notification_code);
889 WF_ENGINE.SetItemAttrText('CLN_NPNP',l_notification_flow_key, 'STATUS', p_statuslvl);
890 IF (l_Debug_Level <= 2) THEN
891 ecx_cln_debug_pub.Add('INVOKING WF_ENGINE.StartProcess:' || ':CLN_NP' || l_notification_flow_key || ':CALL_API_CONC', 2);
892 END IF;
893
894 WF_ENGINE.StartProcess('CLN_NPNP', l_notification_flow_key);
895 IF (l_Debug_Level <= 2) THEN
896 ecx_cln_debug_pub.Add('COMPLETED WF_ENGINE.StartProcess:' || ':CLN_NP' || l_notification_flow_key, 2);
897 END IF;
898
899 ELSE
900 l_cln_not_parameters := wf_parameter_list_t();
901 WF_EVENT.AddParameterToList('ApplicationId', l_application_id, l_cln_not_parameters);
902 WF_EVENT.AddParameterToList('CollaborationId', l_collaboration_id, l_cln_not_parameters);
903 WF_EVENT.AddParameterToList('CollaborationType', l_collaboration_type, l_cln_not_parameters);
904 WF_EVENT.AddParameterToList('ReferenceId', p_reference, l_cln_not_parameters);
905 WF_EVENT.AddParameterToList('TradingPartnerID', l_tp_id, l_cln_not_parameters);
906 WF_EVENT.AddParameterToList('HeaderDescription', p_header_desc, l_cln_not_parameters);
907 WF_EVENT.AddParameterToList('NotificationDescription', p_notification_desc, l_cln_not_parameters);
908 WF_EVENT.AddParameterToList('NotificationCode', p_notification_code, l_cln_not_parameters);
909 WF_EVENT.AddParameterToList('Status', p_statuslvl, l_cln_not_parameters);
910 l_procedure_call_statement := 'begin ' || c_actions.attribute1 || '(:l_cln_not_parameters); end;';
911 execute immediate l_procedure_call_statement using l_cln_not_parameters;
912 END IF;
913 ELSE
914 FND_MESSAGE.SET_NAME('CLN', 'CLN_INVALID_ACTION_DEFINED'); -- 'Invalid action defined'
915 x_ret_desc := FND_MESSAGE.GET;
916 RAISE FND_API.G_EXC_ERROR;
917 END IF;
918 EXCEPTION
919 WHEN FND_API.G_EXC_ERROR THEN
920 ROLLBACK TO ACTION;
921 IF (l_Debug_Level <= 5) THEN
922 ecx_cln_debug_pub.Add('Rolledback transaction', 6);
923 ecx_cln_debug_pub.Add(x_ret_desc, 6);
924 END IF;
925
926 NOTIFY_ADMINISTRATOR('While trying to execute user defined actions as part of '
927 || 'notification processing for inbound confirmbod for the '
928 || 'collaboration ID#'
929 || l_collaboration_id
930 || ', the following error is encountered:'
931 || x_ret_desc);
932 IF (l_Debug_Level <= 2) THEN
933 ecx_cln_debug_pub.Add('Proceeding with the next action', 6);
934 END IF;
935 WHEN OTHERS THEN
936
937 ROLLBACK TO ACTION;
938 IF (l_Debug_Level <= 5) THEN
939 ecx_cln_debug_pub.Add('Rolledback transaction', 6);
940 END IF;
941
942 l_error_code := SQLCODE;
943 l_error_msg := SQLERRM;
944 IF (l_Debug_Level <= 5) THEN
945 ecx_cln_debug_pub.Add(l_error_code || ':' || l_error_msg, 6);
946 END IF;
947
948 NOTIFY_ADMINISTRATOR('While trying to execute user defined actions as part of '
949 || 'notification processing for inbound confirmbod for the '
950 || 'collaboration ID#'
951 || l_collaboration_id
952 || ', the following error is encountered:'
953 || l_error_code || ':' || l_error_msg);
954 IF (l_Debug_Level <= 5) THEN
955 ecx_cln_debug_pub.Add('Proceeding with the next action', 6);
956 END IF;
957
958 END;
959 END Loop;
960 IF (l_Debug_Level <= 2) THEN
961 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS_INTERNAL', 2);
962 END IF;
963
964 EXCEPTION
965 WHEN FND_API.G_EXC_ERROR THEN
966 x_ret_code := FND_API.G_RET_STS_ERROR;
967 IF (l_Debug_Level <= 5) THEN
968 ecx_cln_debug_pub.Add(x_ret_desc, 6);
969 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS_INTERNAL', 2);
970 END IF;
971
972 WHEN OTHERS THEN
973 l_error_code := SQLCODE;
974 l_error_msg := SQLERRM;
975 x_ret_code := FND_API.G_RET_STS_UNEXP_ERROR ;
976 x_ret_desc := l_error_code || ':' || l_error_msg;
977 IF (l_Debug_Level <= 5) THEN
978 ecx_cln_debug_pub.Add(x_ret_desc,6);
979 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS_INTERNAL', 2);
980 END IF;
981
982 END TAKE_ACTIONS_INTERNAL;
983
984
985 -- Name
986 -- PROCESS_NOTIFICATION
987 -- Purpose
988 -- Spec of package CLN_NP_PROCESSOR_PKG
989 -- Based on the notification code, fetches the notification actions
990 -- and executes the actions that are defined by the user.
991 -- The actions can be one of the following : Raise Event, Start Workflow,
992 -- Notify Administartor, Notify Trading Pratner and Call user Procedure.
993 -- This package is triggered by Notification Message Map when a Notification BOD arraives,
994 -- XML Gateway Error Handling.
995 -- Arguments
996 -- p_tp_id Trading Partner ID
997 -- notification_code Notification Code Received
998 -- p_reference Application Reference ID
999 -- p_statuslvl '00' for Sucess and '99' for Error
1000 -- p_header_desc Header description
1001 -- p_reason_code Comma seperated list of notification code
1002 -- p_line_desc Line description
1003 -- p_int_con_no Internal Control Number
1004 -- p_coll_point Collaboration Point
1005 -- p_doc_dir Document Direction
1006 -- Notes
1007 -- No specific notes
1008
1009 PROCEDURE PROCESS_NOTIFICATION(
1010 x_ret_code OUT NOCOPY VARCHAR2,
1011 x_ret_desc OUT NOCOPY VARCHAR2,
1012 p_tp_id IN VARCHAR2,
1013 p_reference IN VARCHAR2,
1014 p_statuslvl IN VARCHAR2,
1015 p_header_desc IN VARCHAR2,
1016 p_reason_code IN VARCHAR2,
1017 p_line_desc IN VARCHAR2,
1018 p_int_con_no IN VARCHAR2,
1019 p_coll_point IN VARCHAR2,
1020 p_doc_dir IN VARCHAR2,
1021 p_coll_id IN NUMBER,
1022 p_collaboration_standard IN VARCHAR2)
1023 IS
1024 l_all_reason_codes VARCHAR2(255);
1025 l_reason_code VARCHAR2(100);
1026 l_position_code NUMBER;
1027 l_all_reason_desc VARCHAR2(2000);
1028 l_reason_desc VARCHAR2(1000);
1029 l_position_desc NUMBER;
1030 l_success BOOLEAN;
1031 l_update_collaboration_flag BOOLEAN; -- For updating collaboration history only once
1032 l_dtl_coll_id NUMBER(10);
1033 l_debug_mode VARCHAR2(255);
1034 l_return_code VARCHAR2(1000);
1035 l_return_desc VARCHAR2(1000);
1036 l_error_code NUMBER;
1037 l_error_msg VARCHAR2(1000);
1038 BEGIN
1039 SAVEPOINT PROCESS_NOTIFICATION;
1040 -- Sets the debug mode to be FILE
1041 --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
1042 IF (l_Debug_Level <= 2) THEN
1043 ecx_cln_debug_pub.Add('ENTERING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIFICATION', 2);
1044 END IF;
1045
1046 IF (l_Debug_Level <= 1) THEN
1047 ecx_cln_debug_pub.Add('With the following parameters:', 1);
1048 ecx_cln_debug_pub.Add('p_tp_id:' || p_tp_id, 1);
1049 ecx_cln_debug_pub.Add('p_reference:' || p_reference, 1);
1050 ecx_cln_debug_pub.Add('p_statuslvl:' || p_statuslvl, 1);
1051 ecx_cln_debug_pub.Add('p_header_desc:' || p_header_desc, 1);
1052 ecx_cln_debug_pub.Add('p_reason_code:' || p_reason_code, 1);
1053 ecx_cln_debug_pub.Add('p_line_desc:' || p_line_desc, 1);
1054 ecx_cln_debug_pub.Add('p_int_con_no:' || p_int_con_no, 1);
1055 ecx_cln_debug_pub.Add('p_coll_point:' || p_coll_point, 1);
1056 ecx_cln_debug_pub.Add('p_doc_dir:' || p_doc_dir, 1);
1057 ecx_cln_debug_pub.Add('p_coll_id:' || p_coll_id, 1);
1058 ecx_cln_debug_pub.Add('p_collaboration_standard:' || p_collaboration_standard, 1);
1059 END IF;
1060
1061
1062 x_ret_code := FND_API.G_RET_STS_SUCCESS;
1063 FND_MESSAGE.SET_NAME('CLN', 'CLN_SUCCESS'); -- 'Success'
1064 x_ret_desc := FND_MESSAGE.GET;
1065
1066 l_update_collaboration_flag := TRUE; -- IF TRUE collaboration is updated
1067 IF p_reason_code = ':' or p_reason_code IS NULL THEN
1068 l_all_reason_codes := NULL;
1069 TAKE_ACTIONS_INTERNAL(l_return_code, l_return_desc, p_statuslvl, l_reason_desc,
1070 p_tp_id, p_reference, p_statuslvl, p_header_desc,
1071 l_update_collaboration_flag, true, p_reason_code,
1072 p_int_con_no, p_coll_point, p_doc_dir,
1073 p_coll_id, l_dtl_coll_id,p_collaboration_standard, null,null);
1074 IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
1075 x_ret_desc := l_return_desc;
1076 RAISE FND_API.G_EXC_ERROR;
1077 END IF;
1078
1079 l_update_collaboration_flag := FALSE; -- no need to update second or third time
1080 ELSE
1081 SELECT REPLACE(p_reason_code, '::', ':') INTO l_all_reason_codes FROM DUAL;
1082 SELECT REPLACE(p_line_desc, '::', ':') INTO l_all_reason_desc FROM DUAL;
1083 l_position_code := 0;
1084 l_position_desc := 0;
1085 END IF;
1086
1087 WHILE l_all_reason_codes IS NOT NULL LOOP
1088 NEXT_PART(l_position_code,l_all_reason_codes,l_reason_code);
1089 NEXT_PART(l_position_desc,l_all_reason_desc,l_reason_desc);
1090 TAKE_ACTIONS_INTERNAL(l_return_code, l_return_desc, l_reason_code, l_reason_desc,
1091 p_tp_id, p_reference, p_statuslvl, p_header_desc,
1092 l_update_collaboration_flag, true, p_reason_code,
1093 p_int_con_no, p_coll_point, p_doc_dir,
1094 p_coll_id, l_dtl_coll_id,p_collaboration_standard,null,null);
1095 IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
1096 x_ret_desc := l_return_desc;
1097 RAISE FND_API.G_EXC_ERROR;
1098 END IF;
1099 l_update_collaboration_flag := FALSE; -- no need to update second or third time
1100 END LOOP;
1101 IF (l_Debug_Level <= 2) THEN
1102 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIFICATION', 2);
1103 END IF;
1104
1105 EXCEPTION
1106 WHEN FND_API.G_EXC_ERROR THEN
1107 ROLLBACK TO PROCESS_NOTIFICATION;
1108 IF (l_Debug_Level <= 5) THEN
1109 ecx_cln_debug_pub.Add('Rolledback transaction',5);
1110 END IF;
1111
1112 x_ret_code := FND_API.G_RET_STS_ERROR;
1113 IF (l_Debug_Level <= 5) THEN
1114 ecx_cln_debug_pub.Add(x_ret_desc, 6);
1115 END IF;
1116
1117 NOTIFY_ADMINISTRATOR('Notification processing for inbound confirmbod '
1118 || 'for the collaboration ID#'
1119 || p_coll_id
1120 || ', encountered the following error:'
1121 || x_ret_desc);
1122 IF (l_Debug_Level <= 5) THEN
1123 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIFICATION', 2);
1124 END IF;
1125
1126 WHEN OTHERS THEN
1127 ROLLBACK TO PROCESS_NOTIFICATION;
1128 IF (l_Debug_Level <= 5) THEN
1129 ecx_cln_debug_pub.Add('Rolledback transaction');
1130 END IF;
1131
1132 l_error_code := SQLCODE;
1133 l_error_msg := SQLERRM;
1134 x_ret_code := FND_API.G_RET_STS_UNEXP_ERROR;
1135 NOTIFY_ADMINISTRATOR('Notification processing for inbound confirmbod '
1136 || 'for the collaboration ID#'
1137 || p_coll_id
1138 || ', encountered the following error:'
1139 || l_error_code || ':' || l_error_msg);
1140 IF (l_Debug_Level <= 5) THEN
1141 ecx_cln_debug_pub.Add(x_ret_desc, 6);
1142 END IF;
1143
1144 NOTIFY_ADMINISTRATOR(x_ret_desc);
1145 IF (l_Debug_Level <= 5) THEN
1146 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIFICATION', 2);
1147 END IF;
1148
1149 END PROCESS_NOTIFICATION;
1150
1151
1152
1153 -- Name
1154 -- TAKE_ACTIONS
1155 -- Purpose
1156 -- This procedure performs all the user defined actions for the specified comma seperated list of notification codes
1157 -- Arguments
1158 -- p_notification_code Comma seperated list of notification code
1159 -- p_notification_desc Comma seperated list of notification description
1160 -- p_status SUCCESS/ERROR
1161 -- p_tp_id Trading Partner ID
1162 -- p_reference Application Reference ID
1163 -- p_coll_point Collaboration Point
1164 -- p_int_con_no Internal Control number
1165 -- Notes
1166 -- No specific notes
1167
1168
1169 PROCEDURE TAKE_ACTIONS(
1170 x_ret_code OUT NOCOPY VARCHAR2,
1171 x_ret_desc OUT NOCOPY VARCHAR2,
1172 p_notification_code IN VARCHAR2,
1173 p_notification_desc IN VARCHAR2,
1174 p_status IN VARCHAR2,
1175 p_tp_id IN VARCHAR2,
1176 p_reference IN VARCHAR2,
1177 p_coll_point IN VARCHAR2,
1178 p_int_con_no IN VARCHAR2)
1179 IS
1180 l_dtl_coll_id NUMBER(10);
1181 l_statuslvl VARCHAR2(10);
1182 l_return_code VARCHAR2(1000);
1183 l_return_msg VARCHAR2(1000);
1184 l_debug_mode VARCHAR2(255);
1185 BEGIN
1186 SAVEPOINT TAKE_ACTIONS;
1187 -- Sets the debug mode to be FILE
1188 --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
1189 IF (l_Debug_Level <= 2) THEN
1190 ecx_cln_debug_pub.Add('ENTERING CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS', 2);
1191 END IF;
1192
1193
1194 x_ret_code := FND_API.G_RET_STS_SUCCESS;
1195 FND_MESSAGE.SET_NAME('CLN', 'CLN_SUCCESS'); -- 'Success'
1196 x_ret_desc := FND_MESSAGE.GET;
1197
1198 IF p_status = 'SUCCESS' THEN
1199 l_statuslvl := '00';
1200 ELSE
1201 l_statuslvl := '99';
1202 END IF;
1203
1204 TAKE_ACTIONS_INTERNAL(l_return_code,
1205 l_return_msg,
1206 p_notification_code,
1207 p_notification_desc,
1208 p_tp_id,
1209 p_reference,
1210 l_statuslvl,
1211 null,
1212 false,
1213 false,
1214 null,
1215 p_int_con_no,
1216 p_coll_point,
1217 null,
1218 null,
1219 l_dtl_coll_id,
1220 null, null, null);
1221 IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
1222 x_ret_desc := l_return_msg;
1223 RAISE FND_API.G_EXC_ERROR;
1224 END IF;
1225 IF (l_Debug_Level <= 2) THEN
1226 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS', 2);
1227 END IF;
1228
1229 EXCEPTION
1230 WHEN FND_API.G_EXC_ERROR THEN
1231 ROLLBACK TO TAKE_ACTIONS;
1232 IF (l_Debug_Level <= 5) THEN
1233 ecx_cln_debug_pub.Add('Rolledback transaction');
1234 END IF;
1235
1236 x_ret_code := FND_API.G_RET_STS_ERROR;
1237 IF (l_Debug_Level <= 5) THEN
1238 ecx_cln_debug_pub.Add(x_ret_desc, 6);
1239 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS', 2);
1240 END IF;
1241
1242 WHEN OTHERS THEN
1243 ROLLBACK TO TAKE_ACTIONS;
1244 IF (l_Debug_Level <= 5) THEN
1245 ecx_cln_debug_pub.Add('Rolledback transaction');
1246 END IF;
1247
1248 l_return_code := SQLCODE;
1249 l_return_msg := SQLERRM;
1250 x_ret_code := FND_API.G_RET_STS_UNEXP_ERROR;
1251 x_ret_desc := l_return_code||' : '||l_return_msg;
1252 IF (l_Debug_Level <= 5) THEN
1253 ecx_cln_debug_pub.Add(x_ret_desc, 6);
1254 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS', 2);
1255 END IF;
1256
1257 END TAKE_ACTIONS;
1258
1259 -- Name
1260 -- GET_DELIMITER
1261 -- Purpose
1262 -- This function returns the delimiter character used to delimit a list of notification code/description
1263 -- Arguments
1264 --
1265 -- Notes
1266 -- No specific notes.
1267
1268 FUNCTION GET_DELIMITER RETURN VARCHAR2
1269 IS
1270 l_delimter_chr VARCHAR2(1);
1271 BEGIN
1272 l_delimter_chr := fnd_global.local_chr(127);
1273 RETURN l_delimter_chr;
1274 END;
1275
1276
1277 -- Name
1278 -- GET_TRADING_PARTNER_DETAILS
1279 -- Purpose
1280 -- This procedure gets the trading partner id based on the internal control number
1281 -- Arguments
1282 --
1283 -- Notes
1284 -- No specific notes.
1285
1286 PROCEDURE GET_TRADING_PARTNER_DETAILS(
1287 x_return_status OUT NOCOPY VARCHAR2,
1288 x_msg_data OUT NOCOPY VARCHAR2,
1289 p_xmlg_internal_control_number IN NUMBER,
1290 p_tr_partner_id IN OUT NOCOPY VARCHAR2)
1291 IS
1292 l_error_code NUMBER;
1293 l_msg_data VARCHAR2(2000);
1294 l_error_msg VARCHAR2(2000);
1295 l_return_code VARCHAR2(2000);
1296 l_return_msg VARCHAR2(2000);
1297 l_debug_mode VARCHAR2(255);
1298 BEGIN
1299
1300 -- Sets the debug mode to be FILE
1301 --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
1302 IF (l_Debug_Level <= 2) THEN
1303 ecx_cln_debug_pub.Add('ENTERING CLN_NP_PROCESSOR_PKG.GET_TRADING_PARTNER_DETAILS', 2);
1304 END IF;
1305
1306
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308 FND_MESSAGE.SET_NAME('CLN', 'CLN_SUCCESS'); -- 'Success'
1309 x_msg_data := FND_MESSAGE.GET;
1310
1311 BEGIN
1312 SELECT to_char(eth.tp_header_id)
1313 INTO p_tr_partner_id
1314 FROM ECX_DOCLOGS doclogs, ecx_ext_processes eep, ecx_tp_details etd, ecx_tp_headers eth, ecx_standards estd
1315 WHERE doclogs.internal_control_number = p_xmlg_internal_control_number
1316 AND eep.ext_type = doclogs.transaction_type
1317 AND eep.ext_subtype = doclogs.transaction_subtype
1318 AND eep.standard_id = estd.standard_id
1319 AND estd.standard_code = doclogs.message_standard
1320 AND eep.ext_process_id = etd.ext_process_id
1321 AND etd.source_tp_location_code = doclogs.party_site_id
1322 AND eep.direction = 'IN'
1323 AND eth.party_type = NVL(doclogs.party_type,eth.party_type);
1324 EXCEPTION
1325 WHEN NO_DATA_FOUND THEN
1326 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
1327 x_msg_data := FND_MESSAGE.GET;
1328 RAISE FND_API.G_EXC_ERROR;
1329 WHEN TOO_MANY_ROWS THEN
1330 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
1331 x_msg_data := FND_MESSAGE.GET;
1332 RAISE FND_API.G_EXC_ERROR;
1333 END;
1334 IF (l_Debug_Level <= 1) THEN
1335 ecx_cln_debug_pub.Add('p_tr_partner_id:' || p_tr_partner_id, 1);
1336 END IF;
1337
1338 IF (l_Debug_Level <= 2) THEN
1339 ecx_cln_debug_pub.Add('EXITING GET_TRADING_PARTNER_DETAILS', 2);
1340 END IF;
1341
1342 EXCEPTION
1343 WHEN FND_API.G_EXC_ERROR THEN
1344 x_return_status := FND_API.G_RET_STS_ERROR;
1345 IF (l_Debug_Level <= 5) THEN
1346 ecx_cln_debug_pub.Add(x_msg_data, 4);
1347 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.GET_TRADING_PARTNER_DETAILS', 2);
1348 END IF;
1349
1350 WHEN OTHERS THEN
1351 l_error_code := SQLCODE;
1352 l_error_msg := SQLERRM;
1353 x_msg_data := l_error_code || ':' || l_error_msg;
1354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1355 IF (l_Debug_Level <= 5) THEN
1356 ecx_cln_debug_pub.Add(x_msg_data, 6);
1357 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.GET_TRADING_PARTNER_DETAILS', 2);
1358 END IF;
1359
1360 END GET_TRADING_PARTNER_DETAILS;
1361
1362
1363
1364 -- Name
1365 -- NOTIFY_ADMINISTRATOR
1366 -- Purpose
1367 -- Sends a mail to the administrator
1368 -- Arguments
1369 -- Message to be send to the administrator
1370 -- Notes
1371 -- No specific notes.
1372
1373 PROCEDURE NOTIFY_ADMINISTRATOR(
1374 p_message IN VARCHAR2
1375 )
1376 IS
1377 l_notification_flow_key NUMBER(20);
1378 l_email VARCHAR2(100);
1379 l_debug_mode VARCHAR2(255);
1380 l_error_code NUMBER;
1381 l_error_msg VARCHAR2(2000);
1382 l_admin_email VARCHAR2(1000);
1383 l_role VARCHAR2(1000);
1384 l_temp VARCHAR2(100);
1385 BEGIN
1386
1387 -- Sets the debug mode to be FILE
1388 --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
1389
1390 IF (l_Debug_Level <= 2) THEN
1391 ecx_cln_debug_pub.Add('ENTERING CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR', 2);
1392 END IF;
1393
1394
1395 l_role := FND_PROFILE.VALUE('CLN_ADMINISTRATOR');
1396 IF (l_Debug_Level <= 1) THEN
1397 ecx_cln_debug_pub.Add('Administrator Profile Role or E-Mail:' || l_email, 1);
1398 END IF;
1399
1400 BEGIN
1401 SELECT 'x'
1402 INTO l_temp
1403 FROM WF_ROLES
1404 WHERE NAME = l_role
1405 AND rownum < 2;
1406 EXCEPTION
1407 WHEN NO_DATA_FOUND THEN
1408 IF (l_Debug_Level <= 1) THEN
1409 ecx_cln_debug_pub.Add('CLN Administrator Profile has email(not role)', 1);
1410 END IF;
1411 l_email := l_role;
1412 l_role := 'CLN_ADMINISTRATOR';
1413 SELECT email_address
1414 INTO l_admin_email
1415 FROM WF_USERS
1416 WHERE NAME = 'CLN_ADMINISTRATOR';
1417 IF (l_Debug_Level <= 1) THEN
1418 ecx_cln_debug_pub.Add('Administrator Role E-Mail:' || l_admin_email, 1);
1419 END IF;
1420 IF( l_email <> l_admin_email or l_admin_email is null) THEN
1421 WF_DIRECTORY.SetAdHocUserAttr (USER_NAME => l_role, EMAIL_ADDRESS => l_email);
1422 END IF;
1423
1424 END;
1425
1426
1427 IF (l_Debug_Level <= 1) THEN
1428 ecx_cln_debug_pub.Add('About to start workflow', 1);
1429 END IF;
1430
1431
1432 SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
1433 IF (l_Debug_Level <= 1) THEN
1434 ecx_cln_debug_pub.Add('Process Item Key to send a mail to administrator:' || l_notification_flow_key, 1);
1435 END IF;
1436 WF_ENGINE.CreateProcess('CLN_NPNP', l_notification_flow_key, 'NOTIFY');
1437 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'NOTIFICATION_CONTENT', p_message);
1438 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'CLN_PERFORMER', l_role );
1439 WF_ENGINE.StartProcess('CLN_NPNP', l_notification_flow_key);
1440
1441 IF (l_Debug_Level <= 2) THEN
1442 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR', 2);
1443 END IF;
1444
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447 l_error_code := SQLCODE;
1448 l_error_msg := SQLERRM;
1449 IF (l_Debug_Level <= 5) THEN
1450 ecx_cln_debug_pub.Add(l_error_code || ':' || l_error_msg, 6);
1451 ecx_cln_debug_pub.Add('Failed to send a mail to administrator', 3);
1452 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR', 2);
1453 END IF;
1454
1455 END NOTIFY_ADMINISTRATOR;
1456
1457
1458
1459
1460 -- Name
1461 -- PROCESS_NOTIF_ACTIONS_EVT
1462 -- Purpose
1463 -- This procedure handles a notification by executing all the actions defined by the user
1464 -- for a given notification code.
1465 --
1466 -- Arguments
1467 --
1468 -- Notes
1469 -- No specific notes
1470
1471 PROCEDURE PROCESS_NOTIF_ACTIONS_EVT(
1472 x_return_status OUT NOCOPY VARCHAR2,
1473 x_msg_data OUT NOCOPY VARCHAR2,
1474 p_coll_id IN NUMBER,
1475 p_xmlg_transaction_type IN VARCHAR2,
1476 p_xmlg_transaction_subtype IN VARCHAR2,
1477 p_xmlg_int_transaction_type IN VARCHAR2,
1478 p_xmlg_int_transaction_subtype IN VARCHAR2,
1479 p_xmlg_document_id IN VARCHAR2,
1480 p_doc_dir IN VARCHAR2,
1481 p_tr_partner_type IN VARCHAR2,
1482 p_tr_partner_id IN VARCHAR2,
1483 p_tr_partner_site IN VARCHAR2,
1484 p_xmlg_msg_id IN VARCHAR2,
1485 p_application_id IN VARCHAR2,
1486 p_unique1 IN VARCHAR2,
1487 p_unique2 IN VARCHAR2,
1488 p_unique3 IN VARCHAR2,
1489 p_unique4 IN VARCHAR2,
1490 p_unique5 IN VARCHAR2,
1491 p_xmlg_internal_control_number IN NUMBER,
1492 p_collaboration_pt IN VARCHAR2,
1493 p_notification_code IN VARCHAR2,
1494 p_notification_desc IN VARCHAR2,
1495 p_notification_status IN VARCHAR2,
1496 p_notification_event IN WF_EVENT_T )
1497
1498 IS
1499 l_coll_id NUMBER;
1500 l_error_code NUMBER;
1501
1502 l_application_name VARCHAR2(100);
1503
1504 l_error_msg VARCHAR2(2000);
1505 l_msg_data VARCHAR2(2000);
1506 l_xmlg_internal_control_number NUMBER;
1507 l_xmlg_msg_id VARCHAR2(100);
1508 l_xmlg_transaction_type VARCHAR2(100);
1509 l_xmlg_transaction_subtype VARCHAR2(100);
1510 l_xmlg_int_transaction_type VARCHAR2(100);
1511 l_xmlg_int_transaction_subtype VARCHAR2(100);
1512 l_xmlg_document_id VARCHAR2(256);
1513 l_doc_dir VARCHAR2(240);
1514 l_tr_partner_type VARCHAR2(30);
1515 l_tr_partner_id VARCHAR2(256);
1516 l_tr_partner_site VARCHAR2(256);
1517 l_application_id VARCHAR2(10);
1518 l_collaboration_pt VARCHAR2(20);
1519
1520 l_notification_code VARCHAR2(30);
1521 l_notification_desc VARCHAR2(1000);
1522 l_notification_status VARCHAR2(30);
1523
1524 l_unique1 VARCHAR2(30);
1525 l_unique2 VARCHAR2(30);
1526 l_unique3 VARCHAR2(30);
1527 l_unique4 VARCHAR2(30);
1528 l_unique5 VARCHAR2(30);
1529
1530 l_statuslvl VARCHAR2(10);
1531
1532 l_admin_email VARCHAR2(1000);
1533 l_role VARCHAR2(1000);
1534 l_temp VARCHAR2(100);
1535 l_email VARCHAR2(255);
1536 l_return_status VARCHAR2(1000);
1537 l_sender_component VARCHAR2(500);
1538 l_xml_event_key VARCHAR2(240);
1539 l_coll_type VARCHAR2(30);
1540 l_return_msg VARCHAR2(1000);
1541 l_return_code VARCHAR2(1000);
1542 l_org_id VARCHAR2(100);
1543 l_collaboration_standard VARCHAR2(30);
1544 l_doc_type VARCHAR2(100);
1545 l_document_number VARCHAR2(255);
1546
1547 l_dtl_coll_id NUMBER;
1548
1549
1550 BEGIN
1551 IF (l_Debug_Level <= 2) THEN
1552 ecx_cln_debug_pub.Add('ENTERING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIF_ACTIONS_EVT', 2);
1553 END IF;
1554
1555 -- Initialize API return status to success
1556 x_return_status := FND_API.G_RET_STS_SUCCESS;
1557 l_msg_data := 'Notifications Processing Successfully completed';
1558
1559 -- get the paramaters passed
1560 IF (l_Debug_Level <= 1) THEN
1561 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
1562 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
1563 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_application_id,1);
1564 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
1565 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
1566 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
1567 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
1568 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
1569 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
1570 ecx_cln_debug_pub.Add('COLLABORATION POINT ----- >>>'||p_collaboration_pt,1);
1571 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
1572 ecx_cln_debug_pub.Add('UNIQUE 1 ----- >>>'||p_unique1,1);
1573 ecx_cln_debug_pub.Add('UNIQUE 2 ----- >>>'||p_unique2,1);
1574 ecx_cln_debug_pub.Add('UNIQUE 3 ----- >>>'||p_unique3,1);
1575 ecx_cln_debug_pub.Add('UNIQUE 4 ----- >>>'||p_unique4,1);
1576 ecx_cln_debug_pub.Add('UNIQUE 5 ----- >>>'||p_unique5,1);
1577 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||p_tr_partner_type,1);
1578 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||p_tr_partner_id,1);
1579 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||p_tr_partner_site,1);
1580 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
1581 ecx_cln_debug_pub.Add('NOTIFICATION CODE ----- >>>'||p_notification_code,1);
1582 ecx_cln_debug_pub.Add('NOTIFICATION DESC ----- >>>'||p_notification_desc,1);
1583 ecx_cln_debug_pub.Add('NOTIFICATION STATUS ----- >>>'||p_notification_status,1);
1584 ecx_cln_debug_pub.Add('=========================================================',1);
1585 END IF;
1586
1587
1588
1589 -- assigning parameter to local variables
1590 l_xmlg_internal_control_number := p_xmlg_internal_control_number;
1591 l_xmlg_msg_id := p_xmlg_msg_id;
1592 l_xmlg_transaction_type := p_xmlg_transaction_type;
1593 l_xmlg_transaction_subtype := p_xmlg_transaction_subtype;
1594 l_xmlg_int_transaction_type := p_xmlg_int_transaction_type;
1595 l_xmlg_int_transaction_subtype := p_xmlg_int_transaction_subtype;
1596 l_xmlg_document_id := p_xmlg_document_id;
1597 l_doc_dir := p_doc_dir;
1598 l_tr_partner_type := p_tr_partner_type;
1599 l_tr_partner_id := p_tr_partner_id;
1600 l_tr_partner_site := p_tr_partner_site;
1601 l_application_id := p_application_id;
1602 l_coll_id := p_coll_id;
1603 l_unique1 := p_unique1;
1604 l_unique2 := p_unique2;
1605 l_unique3 := p_unique3;
1606 l_unique4 := p_unique4;
1607 l_unique5 := p_unique5;
1608 l_xmlg_internal_control_number := p_xmlg_internal_control_number;
1609 l_collaboration_pt := p_collaboration_pt;
1610 l_notification_code := p_notification_code;
1611 l_notification_desc := p_notification_desc;
1612 l_notification_status := p_notification_status;
1613
1614
1615 -- Getting External Transaction type and Subtype associated with Internal transaction type
1616 -- and Internal transaction subtype
1617 IF((l_xmlg_int_transaction_type IS NOT NULL) AND (l_xmlg_int_transaction_subtype IS NOT NULL) AND (l_tr_partner_id IS NOT NULL) AND (l_tr_partner_site IS NOT NULL)) THEN
1618 IF ((l_xmlg_transaction_type IS NULL) OR (l_xmlg_transaction_subtype IS NULL)) THEN
1619
1620 IF (l_Debug_Level <= 1) THEN
1621 ecx_cln_debug_pub.Add('Getting values for External Transaction type and SubType and msg standard',1);
1622 END IF;
1623
1624 BEGIN
1625 SELECT ecxproc.EXT_TYPE,ecxproc.EXT_SUBTYPE
1626 INTO l_xmlg_transaction_type, l_xmlg_transaction_subtype
1627 FROM ecx_tp_headers eth, ecx_tp_details etd, ECX_TRANSACTIONS ecxtrans, ECX_EXT_PROCESSES ecxproc, ecx_standards estd
1628 WHERE eth.party_id = l_tr_partner_id
1629 AND eth.party_site_id = l_tr_partner_site
1630 AND eth.party_type = nvl(l_tr_partner_type, eth.party_type)
1631 AND eth.tp_header_id = etd.tp_header_id
1632 AND etd.ext_process_id = ecxproc.ext_process_id
1633 AND ecxtrans.transaction_id = ecxproc.transaction_id
1634 AND ecxtrans.transaction_type = l_xmlg_int_transaction_type
1635 AND ecxtrans.transaction_subtype = l_xmlg_int_transaction_subtype
1636 AND ecxproc.direction = nvl(l_doc_dir,ecxproc.direction)
1637 AND estd.standard_id = ecxproc.standard_id;
1638
1639 IF (l_Debug_Level <= 1) THEN
1640 ecx_cln_debug_pub.Add('====Parameters Received From ECX_TRANSACTIONS/ECX_EXT_PROCESSES====',1);
1641 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||l_xmlg_transaction_type,1);
1642 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||l_xmlg_transaction_subtype,1);
1643 ecx_cln_debug_pub.Add('==================================================================',1);
1644 END IF;
1645
1646 EXCEPTION
1647 WHEN NO_DATA_FOUND THEN
1648 FND_MESSAGE.SET_NAME('CLN', 'CLN_CH_TRANSACTION_NOT_FOUND');
1649 x_msg_data := FND_MESSAGE.GET;
1650 l_msg_data := 'Unable to find External Transaction Type/ Subtype';
1651
1652 RAISE FND_API.G_EXC_ERROR;
1653
1654 WHEN TOO_MANY_ROWS THEN
1655 FND_MESSAGE.SET_NAME('CLN', 'CLN_CH_EXCESS_TXN_FOUND');
1656 x_msg_data := FND_MESSAGE.GET;
1657 l_msg_data := 'More then one row found for the same transaction detail';
1658 RAISE FND_API.G_EXC_ERROR;
1659 END;
1660 END IF;
1661 END IF;
1662
1663
1664 -- Retrieving Collaboration ID incase the the collaboration id supplied by user is null
1665 --check whether the collaboration is being recorded or not at first instance
1666 IF l_coll_id IS NULL THEN
1667
1668 IF (l_Debug_Level <= 1) THEN
1669 ecx_cln_debug_pub.Add('Collaboration ID passed as null',1);
1670 ecx_cln_debug_pub.Add('==========Call to FIND_COLLABORATION_ID API=============',1);
1671 END IF;
1672
1673 CLN_CH_COLLABORATION_PKG.FIND_COLLABORATION_ID(
1674 x_return_status => x_return_status,
1675 x_msg_data => x_msg_data,
1676 x_coll_id => l_coll_id,
1677 p_app_id => l_application_id,
1678 p_coll_type => null,
1679 p_ref_id => null,
1680 p_xmlg_transaction_type => l_xmlg_transaction_type,
1681 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
1682 p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
1683 p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
1684 p_tr_partner_type => l_tr_partner_type,
1685 p_tr_partner_id => l_tr_partner_id,
1686 p_tr_partner_site => l_tr_partner_site,
1687 p_xmlg_document_id => l_xmlg_document_id,
1688 p_doc_dir => l_doc_dir,
1689 p_xmlg_msg_id => l_xmlg_msg_id,
1690 p_unique1 => l_unique1,
1691 p_unique2 => l_unique2,
1692 p_unique3 => l_unique3,
1693 p_unique4 => l_unique4,
1694 p_unique5 => l_unique5,
1695 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
1696 p_xml_event_key => null);
1697
1698 IF ( x_return_status <> 'S') THEN
1699 l_msg_data := 'Error in FIND_COLLABORATION_ID - ' || x_msg_data;
1700 -- l_msg_data is set to appropriate value by FIND_COLLABORATION_ID
1701 -- RAISE FND_API.G_EXC_ERROR;
1702
1703 -- we are not throwing any error here so as to make this module
1704 -- work even when no collaboration exists
1705 END IF;
1706 END IF;
1707
1708 -- Call the API to get the trading partner set up details
1709 IF (l_Debug_Level <= 1) THEN
1710 ecx_cln_debug_pub.Add('==========Call to GET_TRADING_PARTNER_DETAILS API=============',1);
1711 END IF;
1712
1713 CLN_CH_COLLABORATION_PKG.GET_TRADING_PARTNER_DETAILS(
1714 x_return_status => x_return_status,
1715 x_msg_data => x_msg_data,
1716 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
1717 p_xmlg_msg_id => l_xmlg_msg_id,
1718 p_xmlg_transaction_type => l_xmlg_transaction_type,
1719 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
1720 p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
1721 p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
1722 p_xmlg_document_id => l_xmlg_document_id,
1723 p_doc_dir => l_doc_dir,
1724 p_tr_partner_type => l_tr_partner_type,
1725 p_tr_partner_id => l_tr_partner_id,
1726 p_tr_partner_site => l_tr_partner_site,
1727 p_sender_component => l_sender_component,
1728 p_xml_event_key => l_xml_event_key,
1729 p_collaboration_standard => l_collaboration_standard);
1730
1731 IF ( x_return_status <> 'S') THEN
1732 l_msg_data := 'Error in GET_TRADING_PARTNER_DETAILS ';
1733 -- x_msg_data is set to appropriate value by GET_TRADING_PARTNER_DETAILS
1734 RAISE FND_API.G_EXC_ERROR;
1735 END IF;
1736
1737 IF (l_Debug_Level <= 1) THEN
1738 -- call the API to get the default parameters through XMLG settings
1739 ecx_cln_debug_pub.Add('==========Call to DEFAULT_XMLGTXN_MAPPING API=============',1);
1740 END IF;
1741
1742 CLN_CH_COLLABORATION_PKG.DEFAULT_XMLGTXN_MAPPING(
1743 x_return_status => x_return_status,
1744 x_msg_data => x_msg_data,
1745 p_xmlg_transaction_type => l_xmlg_transaction_type,
1746 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
1747 p_doc_dir => l_doc_dir,
1748 p_app_id => l_application_id,
1749 p_coll_type => l_coll_type,
1750 p_doc_type => l_doc_type );
1751
1752 IF ( x_return_status <> 'S') THEN
1753 l_msg_data := 'Error in DEFAULT_XMLGTXN_MAPPING';
1754 -- x_msg_data is set to appropriate value by DEFAULT_XMLGTXN_MAPPING
1755 -- RAISE FND_API.G_EXC_ERROR;
1756 -- No need to set up defaulting data when no collaboration exists
1757 END IF;
1758
1759
1760 IF(l_notification_code IS NULL) THEN
1761 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1762 RETURN;
1763 END IF;
1764
1765 IF l_notification_status = 'SUCCESS' THEN
1766 l_statuslvl := '00';
1767 ELSE
1768 l_statuslvl := '99';
1769 END IF;
1770
1771 -- if send_mail is not null, directly call send_mail api.
1772 -- if notification_code is not null, then proceed further else return.
1773
1774 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS_INTERNAL(
1775 x_ret_code => x_return_status,
1776 x_ret_desc => x_msg_data,
1777 p_notification_code => l_notification_code,
1778 p_notification_desc => l_notification_desc,
1779 p_tp_id => l_tr_partner_id,
1780 p_reference => null,
1781 p_statuslvl => l_statuslvl,
1782 p_header_desc => null,
1783 p_update_collaboration_flag => false,
1784 p_update_coll_mess_flag => false,
1785 p_all_notification_codes => null,
1786 p_int_con_no => l_xmlg_internal_control_number,
1787 p_coll_point => l_collaboration_pt,
1788 p_doc_dir => l_doc_dir,
1789 p_coll_id => l_coll_id,
1790 x_dtl_coll_id => l_dtl_coll_id,
1791 p_collaboration_standard => null,
1792 p_notification_event => p_notification_event,
1793 p_application_id => l_application_id );
1794
1795
1796 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1797 l_msg_data := 'Error in TAKE_ACTIONS_INTERNAL API ';
1798 RAISE FND_API.G_EXC_ERROR;
1799 END IF;
1800
1801 IF (l_Debug_Level <= 2) THEN
1802 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIF_ACTIONS_EVT', 2);
1803 END IF;
1804
1805 EXCEPTION
1806 WHEN FND_API.G_EXC_ERROR THEN
1807 x_return_status := FND_API.G_RET_STS_ERROR;
1808 IF (l_Debug_Level <= 5) THEN
1809 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIF_ACTIONS_EVT', 2);
1810 END IF;
1811
1812 WHEN OTHERS THEN
1813 l_return_code := SQLCODE;
1814 l_return_msg := SQLERRM;
1815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1816 x_msg_data := l_return_code||' : '||x_msg_data;
1817
1818 IF (l_Debug_Level <= 5) THEN
1819 ecx_cln_debug_pub.Add(x_msg_data, 6);
1820 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIF_ACTIONS_EVT', 2);
1821 END IF;
1822
1823 END PROCESS_NOTIF_ACTIONS_EVT;
1824
1825
1826 -- Name
1827 -- PROCESS_NOTIF_BATCH_EVT
1828 -- Purpose
1829 -- This procedure handles a Batch notification request by executing all the actions
1830 -- defined by the user for a given notification code.
1831 --
1832 -- Arguments
1833 --
1834 -- Notes
1835 -- No specific notes
1836
1837 PROCEDURE PROCESS_NOTIF_BATCH_EVT(
1838 x_return_status OUT NOCOPY VARCHAR2,
1839 x_msg_data OUT NOCOPY VARCHAR2,
1840 p_attribute_name IN VARCHAR2,
1841 p_attribute_value IN VARCHAR2,
1842 p_notification_receiver IN VARCHAR2,
1843 p_application_id IN VARCHAR2,
1844 p_collaboration_std IN VARCHAR2,
1845 p_collaboration_type IN VARCHAR2,
1846 p_collaboration_point IN VARCHAR2,
1847 p_notification_code IN VARCHAR2,
1848 p_notification_msg IN VARCHAR2,
1849 p_notification_status IN VARCHAR2 )
1850 IS
1851
1852
1853 l_error_code NUMBER;
1854 l_attribute_name VARCHAR2(150);
1855 l_attribute_value VARCHAR2(150);
1856 l_attribute_col_value VARCHAR2(150);
1857 l_notif_receiver_role VARCHAR2(100);
1858 l_application_name VARCHAR2(100);
1859 l_notification_flow_key NUMBER;
1860
1861 l_error_msg VARCHAR2(2000);
1862 l_msg_data VARCHAR2(2000);
1863 l_application_id VARCHAR2(10);
1864 l_collaboration_pt VARCHAR2(20);
1865
1866 l_notification_code VARCHAR2(30);
1867 l_notification_desc VARCHAR2(1000);
1868 l_notification_msg VARCHAR2(1000);
1869 l_notification_dtls VARCHAR2(1000);
1870 l_notification_status VARCHAR2(30);
1871
1872 l_collaboration_std VARCHAR2(30);
1873 l_collaboration_type VARCHAR2(30);
1874
1875 l_admin_email VARCHAR2(1000);
1876 l_role VARCHAR2(1000);
1877 l_temp VARCHAR2(100);
1878 l_email VARCHAR2(255);
1879 l_return_status VARCHAR2(1000);
1880 l_return_msg VARCHAR2(1000);
1881 l_return_code VARCHAR2(1000);
1882 l_org_id VARCHAR2(100);
1883 l_collaboration_standard VARCHAR2(30);
1884
1885 BEGIN
1886 IF (l_Debug_Level <= 2) THEN
1887 ecx_cln_debug_pub.Add('ENTERING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIF_BATCH_EVT', 2);
1888 END IF;
1889
1890 -- Initialize API return status to success
1891 x_return_status := FND_API.G_RET_STS_SUCCESS;
1892 l_msg_data := 'Batch Notification Processing Successfully completed';
1893
1894 -- get the paramaters passed
1895 IF (l_Debug_Level <= 1) THEN
1896 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
1897 ecx_cln_debug_pub.Add('ATTRIBUTE NAME ----- >>>'||p_attribute_name,1);
1898 ecx_cln_debug_pub.Add('ATTRIBUTE VALUE ----- >>>'||p_attribute_value,1);
1899 ecx_cln_debug_pub.Add('APPLICATION ID ----- >>>'||p_application_id,1);
1900 ecx_cln_debug_pub.Add('COLLABORATION STD ----- >>>'||p_collaboration_std,1);
1901 ecx_cln_debug_pub.Add('COLLABORATION TYPE ----- >>>'||p_collaboration_type,1);
1902 ecx_cln_debug_pub.Add('COLLABORATION POINT ----- >>>'||p_collaboration_point,1);
1903 ecx_cln_debug_pub.Add('NOTIFICATION RECEIVER ----- >>>'||p_notification_receiver,1);
1904 ecx_cln_debug_pub.Add('NOTIFICATION CODE ----- >>>'||p_notification_code,1);
1905 ecx_cln_debug_pub.Add('NOTIFICATION DESC ----- >>>'||p_notification_msg,1);
1906 ecx_cln_debug_pub.Add('NOTIFICATION STATUS ----- >>>'||p_notification_status,1);
1907 ecx_cln_debug_pub.Add('=========================================================',1);
1908 END IF;
1909
1910 -- assigning parameter to local variables
1911 l_attribute_name := p_attribute_name;
1912 l_attribute_value := p_attribute_value;
1913 l_notif_receiver_role := p_notification_receiver;
1914 l_application_id := p_application_id;
1915 l_collaboration_std := p_collaboration_std;
1916
1917 IF (l_Debug_Level <= 4) THEN
1918 ecx_cln_debug_pub.Add('Getting the Application Name from the Application ID as '||l_application_id, 1);
1919 END IF;
1920
1921
1922 -- Query fnd_application_vl for application name using application id
1923 BEGIN
1924 SELECT application_name
1925 INTO l_application_name
1926 FROM fnd_application_vl
1927 WHERE application_id = l_application_id;
1928
1929 IF (l_Debug_Level <= 1) THEN
1930 ecx_cln_debug_pub.Add('Queried the following from fnd_application_vl using application id:'|| l_application_id, 1);
1931 ecx_cln_debug_pub.Add('APPLICATION NAME ----- >>>'||l_application_name, 1);
1932 END IF;
1933
1934 EXCEPTION
1935 WHEN NO_DATA_FOUND THEN
1936 -- INVALID APPLICATION ID
1937 FND_MESSAGE.SET_NAME('CLN', 'CLN_INVALID_APPL_ID'); -- 'Invalid application id'
1938 x_msg_data := FND_MESSAGE.GET;
1939 RAISE FND_API.G_EXC_ERROR;
1940 END;
1941
1942 IF(l_attribute_name IS NULL) OR (l_attribute_value IS NULL) THEN
1943 FND_MESSAGE.SET_NAME('CLN','CLN_CH_BATCH_PARAM_NULL');
1944 x_msg_data := FND_MESSAGE.GET;
1945 RAISE FND_API.G_EXC_ERROR;
1946 END IF;
1947
1948 IF (l_Debug_Level <= 4) THEN
1949 ecx_cln_debug_pub.Add('Queried the display setup using application id/collaboration standard/attribute name as :'|| l_application_id||'/'||l_collaboration_std||'/'||l_attribute_name, 1);
1950 END IF;
1951
1952
1953 -- GET THE ATTRIBUTE COLUMN NAME
1954 BEGIN
1955 SELECT tl.display_label
1956 INTO l_attribute_col_value
1957 FROM CLN_CH_DISPLAY_LABELS_DTL_tl tl, CLN_CH_DISPLAY_LABELS_DTL_VL vl, CLN_CH_DISPLAY_LABELS_hdr hdr
1958 WHERE tl.guid = vl.guid
1959 AND parent_guid = hdr.guid
1960 AND collaboration_standard = l_collaboration_std
1961 AND application_id = l_application_id
1962 AND cln_columns = l_attribute_name
1963 AND collaboration_type IS NULL
1964 AND LANGUAGE = USERENV('LANG');
1965
1966 IF (l_Debug_Level <= 1) THEN
1967 ecx_cln_debug_pub.Add('ATTRIBUTE COLUMN VALUE ----- >>>'||l_attribute_col_value, 1);
1968 END IF;
1969
1970 EXCEPTION
1971 WHEN NO_DATA_FOUND THEN
1972 -- DISPLAY SETUP NOT DONE
1973 FND_MESSAGE.SET_NAME('CLN', 'CLN_DISPLAY_SETUP_ERROR');
1974 FND_MESSAGE.SET_TOKEN('APPLID',l_application_id);
1975 FND_MESSAGE.SET_TOKEN('COLLSTD',l_collaboration_std);
1976
1977 IF (l_Debug_Level <= 4) THEN
1978 ecx_cln_debug_pub.Add('Display SetUp Not Found forapplication id/collaboration standard/attribute name as :'|| l_application_id||'/'||l_collaboration_std||'/'||l_attribute_name, 4);
1979 END IF;
1980
1981 x_msg_data := FND_MESSAGE.GET;
1982 RAISE FND_API.G_EXC_ERROR;
1983 END;
1984
1985 IF (l_Debug_Level <= 4) THEN
1986 ecx_cln_debug_pub.Add('Getting the Notification desc for Code ----- >>>'||p_notification_code, 1);
1987 END IF;
1988
1989 IF(p_collaboration_point IS NOT NULL AND p_notification_code IS NOT NULL) THEN
1990 BEGIN
1991 SELECT codestl.NOTIFICATION_MESSAGE
1992 INTO l_notification_desc
1993 FROM CLN_NOTIFICATION_CODES codes, CLN_NOTIFICATION_CODES_TL codestl
1994 WHERE codes.NOTIFICATION_ID = codestl.NOTIFICATION_ID
1995 AND NOTIFICATION_CODE = p_notification_code
1996 AND COLLABORATION_POINT = p_collaboration_point
1997 AND LANGUAGE = USERENV('LANG');
1998
1999 IF (l_Debug_Level <= 1) THEN
2000 ecx_cln_debug_pub.Add('Queried the Notification Codes Setup', 1);
2001 ecx_cln_debug_pub.Add('NOTIFICATION DESC ----- >>>'||l_notification_desc, 1);
2002 END IF;
2003
2004 EXCEPTION
2005 WHEN NO_DATA_FOUND THEN
2006 -- DISPLAY SETUP NOT DONE
2007 IF (l_Debug_Level <= 4) THEN
2008 ecx_cln_debug_pub.Add('No setup found for Notification Code ---- >>>'||p_notification_code, 4);
2009 END IF;
2010 l_notification_desc := 'xxxxxxxxxx';
2011 END;
2012 ELSE
2013 l_notification_desc := 'xxxxxxxxxx';
2014 END IF;
2015
2016
2017 IF(l_notif_receiver_role IS NULL) THEN
2018 IF (l_Debug_Level <= 1) THEN
2019 ecx_cln_debug_pub.Add('Notification Receiver Defaulted to CLN:Admimistrator', 1);
2020 END IF;
2021
2022 l_notif_receiver_role := FND_PROFILE.VALUE('CLN_ADMINISTRATOR');
2023
2024 IF (l_Debug_Level <= 1) THEN
2025 ecx_cln_debug_pub.Add('Notification Receiver ---- >>>'||l_notif_receiver_role, 1);
2026 END IF;
2027 END IF;
2028
2029 BEGIN
2030 SELECT 'x'
2031 INTO l_temp
2032 FROM WF_ROLES
2033 WHERE NAME = l_notif_receiver_role
2034 AND rownum < 2;
2035
2036 EXCEPTION
2037 WHEN NO_DATA_FOUND THEN
2038 IF (l_Debug_Level <= 1) THEN
2039 ecx_cln_debug_pub.Add('CLN Administrator Profile has email(not role)', 1);
2040 END IF;
2041
2042 l_email := l_notif_receiver_role;
2043 l_role := 'CLN_ADMINISTRATOR';
2044
2045 SELECT email_address
2046 INTO l_admin_email
2047 FROM WF_USERS
2048 WHERE NAME = 'CLN_ADMINISTRATOR';
2049
2050 IF (l_Debug_Level <= 1) THEN
2051 ecx_cln_debug_pub.Add('Administrator Role E-Mail:' || l_admin_email, 1);
2052 END IF;
2053
2054 IF( l_email <> l_admin_email or l_admin_email is null) THEN
2055 WF_DIRECTORY.SetAdHocUserAttr (USER_NAME => l_role, EMAIL_ADDRESS => l_email);
2056 END IF;
2057 END;
2058
2059
2060 SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
2061
2062
2063 ------------ get the notification details -----------
2064 FND_MESSAGE.SET_NAME('CLN', 'CLN_NOTIF_DTLS');
2065 FND_MESSAGE.SET_TOKEN('ATTRNAME',l_attribute_col_value);
2066 FND_MESSAGE.SET_TOKEN('ATTRVALUE',p_attribute_value);
2067 l_notification_dtls := FND_MESSAGE.GET;
2068
2069 IF (l_Debug_Level <= 4) THEN
2070 ecx_cln_debug_pub.Add('l_notification_dtls : '||l_notification_dtls , 4);
2071 END IF;
2072
2073 -------------
2074
2075 IF (l_Debug_Level <= 1) THEN
2076 ecx_cln_debug_pub.Add('Calling CLN_NPNP/NOTIFY_BATCH', 1);
2077 END IF;
2078
2079
2080 WF_ENGINE.CreateProcess('CLN_NPNP', l_notification_flow_key, 'NOTIFY_BATCH');
2081 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'APPLICATION_NAME', l_application_name);
2082 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'APPLICATION_ID', l_application_id);
2083 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'COLLABORATION_STD', p_collaboration_std);
2084 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'NOTIFICATION_CODE', p_notification_code);
2085 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'NOTIFICATION_MESSAGE', p_notification_msg);
2086 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'NOTIFICATION_DESC', l_notification_desc);
2087 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'NOTIFICATION_DTLS', l_notification_dtls);
2088 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'ATTRIBUTE_COL_NAME', p_attribute_name);
2089 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'ATTRIBUTE_COL_VALUE', l_attribute_col_value);
2090 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'ATTRIBUTE_VALUE', p_attribute_value);
2091 WF_ENGINE.SetItemAttrText('CLN_NPNP', l_notification_flow_key, 'CLN_PERFORMER', l_notif_receiver_role);
2092 WF_ENGINE.StartProcess('CLN_NPNP', l_notification_flow_key);
2093
2094 IF (l_Debug_Level <= 2) THEN
2095 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.SEND_MAIL', 2);
2096 END IF;
2097
2098
2099 IF (l_Debug_Level <= 2) THEN
2100 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIF_BATCH_EVT', 2);
2101 END IF;
2102
2103 EXCEPTION
2104 WHEN FND_API.G_EXC_ERROR THEN
2105 x_return_status := FND_API.G_RET_STS_ERROR;
2106 IF (l_Debug_Level <= 5) THEN
2107 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIF_BATCH_EVT', 2);
2108 END IF;
2109
2110 WHEN OTHERS THEN
2111 l_return_code := SQLCODE;
2112 l_return_msg := SQLERRM;
2113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2114 x_msg_data := l_return_code||' : '||x_msg_data;
2115
2116 IF (l_Debug_Level <= 5) THEN
2117 ecx_cln_debug_pub.Add(x_msg_data, 6);
2118 ecx_cln_debug_pub.Add('EXITING CLN_NP_PROCESSOR_PKG.PROCESS_NOTIF_BATCH_EVT', 2);
2119 END IF;
2120
2121 END PROCESS_NOTIF_BATCH_EVT;
2122
2123 END CLN_NP_PROCESSOR_PKG;