[Home] [Help]
PACKAGE BODY: APPS.M4U_RESP_PROCESS
Source
1 PACKAGE BODY m4u_resp_process AS
2 /* $Header: m4urespb.pls 120.2 2006/05/11 03:29:27 bsaratna noship $ */
3
4
5 g_debug_level NUMBER ;
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'm4u_resp_process';
7
8
9 -- Name
10 -- add_error_to_cln_hist
11 -- Purpose
12 -- This generic procedure is used to add error messages to CLN History
13 -- Arguments
14 -- p_error_code - UCCnet specific error code
15 -- p_error_message - UCCnet generated error description
16 -- p_error_type - 'ERROR',(haven't seen anything else)
17 -- p_collab_detail_id - collaboration-history detail id to be updated
18 -- p_ucc_doc_unique_id - uccnet generated document identifer for inbound payload
19 -- p_xmlg_internal_control_no - XMLG ICN for debug purpose only
20 -- x_return_status - return_status (S-Success/F-failure)
21 -- x_msg_data - return message (if any)
22 -- Notes
23 -- Adds error-information to collaboration history detail messages
24
25 PROCEDURE add_error_to_cln_hist(
26 p_error_code IN VARCHAR2,
27 p_error_type IN VARCHAR2,
28 p_error_message IN VARCHAR2,
29 p_collab_detail_id IN VARCHAR2,
30 p_ucc_doc_unique_id IN VARCHAR2,
31 p_xmlg_internal_control_no IN VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_data OUT NOCOPY VARCHAR2 )
34
35 IS
36 l_error_code NUMBER;
37 l_error_msg VARCHAR2(2000);
38 l_event_key VARCHAR2(255);
39 l_event_name VARCHAR2(100);
40 l_error_description VARCHAR2(2000);
41 l_event_parameters wf_parameter_list_t;
42 BEGIN
43 l_event_name := 'oracle.apps.cln.ch.collaboration.addmessage';
44
45 IF (g_debug_Level <= 2) THEN
46 cln_debug_pub.Add('===============================================', 2);
47 cln_debug_pub.Add('Entering m4u_resp_process.add_error_to_cln_hist', 2);
48 END IF;
49
50 IF (g_debug_Level <= 1) THEN
51 cln_debug_pub.Add('-- Received parameters -- ', 1);
52 cln_debug_pub.Add('p_error_code --' || p_error_code , 1);
53 cln_debug_pub.Add('p_error_type --' || p_error_type , 1);
54 cln_debug_pub.Add('p_error_message --' || p_error_message, 1);
55 cln_debug_pub.Add('p_collab_detail_id --' || p_collab_detail_id, 1);
56 cln_debug_pub.Add('p_ucc_doc_unique_id --' || p_ucc_doc_unique_id, 1);
57 cln_debug_pub.Add('p_xmlg_internal_control_no --' || p_xmlg_internal_control_no, 1);
58 END IF;
59
60 l_event_parameters := wf_parameter_list_t();
61
62 l_error_description := p_error_type || ', ' || p_error_code || ', ' || p_error_message;
63
64 --create unique key for each event using CLN sequence
65 SELECT cln_collaboration_msg_id_s.nextval INTO l_event_key FROM dual ;
66
67 IF (g_debug_Level <= 1) THEN
68 cln_debug_pub.Add('Raising Add Message Event ', 1);
69 cln_debug_pub.Add('p_event_key -- ' || l_event_key, 1);
70 cln_debug_pub.Add('COLLABORATION_DETAIL_ID -- ' || p_collab_detail_id, 1);
71 cln_debug_pub.Add('DETAIL_MESSAGE -- ' || l_error_description, 1);
72 cln_debug_pub.Add('p_event_name -- ' || l_event_name, 1);
73 END IF;
74
75 -- set event parameters for CLN Add Message Event
76 wf_event.AddParameterToList(
77 p_name => 'COLLABORATION_DETAIL_ID',
78 p_value => p_collab_detail_id,
79 p_parameterlist => l_event_parameters );
80
81 wf_event.AddParameterToList(
82 p_name => 'DETAIL_MESSAGE',
83 p_value => l_error_description,
84 p_parameterlist => l_event_parameters );
85
86
87 -- add CLN event to add error messages as to collaboration history
88 wf_event.raise(
89 p_event_name => l_event_name,
90 p_event_key => l_event_key,
91 p_parameters => l_event_parameters
92 );
93
94 -- return success
95 x_return_status := 'S';
96 x_msg_data := l_event_name || ' raised';
97
98
99 IF (g_debug_Level <= 2) THEN
100 cln_debug_pub.Add('Exiting m4u_resp_process.add_error_to_cln_hist - normal',2);
101 cln_debug_pub.Add('=======================================================',2);
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106
107 -- log exception and exit.
108 l_error_code := SQLCODE;
109 l_error_msg := SQLERRM;
110 x_return_status := 'F';
111 x_msg_data := ' - Unexpected error in m4u_resp_process.add_error_to_cln_hist - ' || l_error_code || ':' || l_error_msg;
112
113 IF (g_debug_Level <= 5) THEN
114 cln_debug_pub.Add(l_error_code || ':' || l_error_msg, 4);
115 END IF;
116
117 IF (g_debug_Level <= 2) THEN
118 cln_debug_pub.Add('Exiting m4u_resp_process.add_error_to_cln_hist - exception',2);
119 cln_debug_pub.Add('==========================================================',2);
120 END IF;
121
122 END add_error_to_cln_hist;
123
124
125 -- Name
126 -- process_cic_notification
127 -- Purpose
128 -- This process is called for each CIC notification received from a Retailer(in worklist query response)
129 -- The Worklist-Query of the type/topic CatalogueItemConfirmation/RoutedDocument
130 -- contains CIC notifications issued by retailers
131 -- The ego_uccnet_events table, and CLN Collaboration need to be updated
132 -- When the catalogueItemConfirmationState is "REJECTED|SYNCHRONIZED" an event needs to be
133 -- raised to move the item-publication WF which is blocked at this stage.
134 -- Arguments
135 -- p_gtin => GTIN of item notified
136 -- p_supp_gln => Supplier GLN
137 -- p_target_market => Code of target market
138 -- p_retailer_gln => GLN of ratiler to whom notification is sent
139 -- p_cic_unique_id => Unique creator ID of CIC document generated by reatiler
140 -- p_cic_state => State of CIC notification
141 -- p_cin_unique_id => Unique creator of CIN for which this CIC was generated
142 -- p_ucc_doc_unique_id => Unique document identifier of wLQ response generated by UCCnet
143 -- p_xmlg_internal_colntrol_no => XMLG internal control no. from map
144 -- x_return_status => Collaboration history detail-id, to be used for further processing
145 -- x_return_status => return_status (s-Success/F-failure)
146 -- x_msg_data => return message
147 -- Notes
148 -- (GTIN + Supplier GLN + Target Code) identify a unique item registered in the UCCnet registry.
149 -- p_cin_unique_id = CLN Id for collaboration of outbound document
150
151 PROCEDURE process_cic_notification(
152 p_gtin IN VARCHAR2,
153 p_supp_gln IN VARCHAR2,
154 p_target_market IN vARCHAR2,
155 p_retailer_gln IN VARCHAR2,
156 p_cic_unique_id IN VARCHAR2,
157 p_cic_state IN VARCHAR2,
158 p_cin_unique_id IN VARCHAR2,
159 p_ucc_doc_unique_id IN VARCHAR2,
160 p_xmlg_internal_control_no IN NUMBER,
161 x_collab_detail_id OUT NOCOPY NUMBER,
162 x_return_status OUT NOCOPY VARCHAR2,
163 x_msg_data OUT NOCOPY VARCHAR2 )
164 IS
165 l_error_code NUMBER;
166 l_error_msg VARCHAR2(2000);
167 l_disposition VARCHAR2(50);
168 l_fnd_msg VARCHAR2(50);
169 l_doc_status VARCHAR2(50);
170 l_return_status VARCHAR2(50);
171 l_coll_status VARCHAR2(50);
172 l_msg_data VARCHAR2(2000);
173 l_msg_count VARCHAR2(50);
174 l_notif_mesg VARCHAR2(400);
175 l_event_name VARCHAR2(100);
176 l_event_key VARCHAR2(30);
177 l_apc_disposition VARCHAR2(30);
178 l_event_params wf_parameter_list_t;
179 BEGIN
180 l_event_name := 'oracle.apps.cln.np.processnotification';
181 IF (g_debug_Level <= 2) THEN
182 cln_debug_pub.Add('-- Entering m4u_resp_process.process_cic_notification -- ', 2);
183 END IF;
184
185 IF (g_debug_Level <= 1) THEN
186 cln_debug_pub.Add('-- with paramters -- ', 1);
187 cln_debug_pub.Add('p_gtin -- ' || p_gtin, 1);
188 cln_debug_pub.Add('p_supp_gln -- ' || p_supp_gln, 1);
189 cln_debug_pub.Add('p_target_market -- ' || p_target_market, 1);
190 cln_debug_pub.Add('p_retialer_gln -- ' || p_retailer_gln, 1);
191 cln_debug_pub.Add('p_cic_unique_id -- ' || p_cic_unique_id, 1);
192 cln_debug_pub.Add('p_cic_state -- ' || p_cic_state, 1);
193 cln_debug_pub.Add('p_cin_unique_id -- ' || p_cin_unique_id, 1);
194 cln_debug_pub.Add('p_ucc_doc_unique_id -- ' || p_ucc_doc_unique_id, 1);
195 cln_debug_pub.Add('p_xmlg_internal_control_no -- ' || p_xmlg_internal_control_no, 1);
196 END IF;
197
198
199
200 -- set CLN Dispostion, CLN Coll Status based on CIC state
201 IF (UPPER(p_cic_state) = 'REVIEW') THEN
202 l_disposition := 'REVIEWED';
203 l_coll_status := 'INITIATED';
204 l_fnd_msg := 'M4U_CIC_INITIATED';
205 l_apc_disposition := 'REVIEW';
206 ELSIF (UPPER(p_cic_state) = 'SYNCHRONISED') THEN
207 l_disposition := 'SYNCH';
208 l_coll_status := 'COMPLETED';
209 l_fnd_msg := 'M4U_CIC_COMPLETED';
210 l_apc_disposition := 'SYNCHRONIZED';
211 ELSIF (UPPER(p_cic_state) = 'ACCEPTED') THEN
212 l_disposition := 'ACCEPTED';
213 l_coll_status := 'INITIATED';
214 l_fnd_msg := 'M4U_CIC_INITIATED';
215 l_apc_disposition := 'ACCEPTED';
216 ELSIF (UPPER(p_cic_state) = 'REJECTED') THEN
217 l_disposition := 'REJECTED';
218 l_coll_status := 'ERROR';
219 l_fnd_msg := 'M4U_CIC_REJECTED';
220 l_apc_disposition := 'REJECTED';
221 END IF;
222
223 l_doc_status := 'SUCCESS';
224
225
226 -- Make API call to update APC event disposition
227 IF (g_debug_Level <= 1) THEN
228 cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
229 cln_debug_pub.Add(' disposition_code -- '|| l_apc_disposition,1);
233
230 cln_debug_pub.Add(' disposition_date -- '|| sysdate,1);
231 cln_debug_pub.Add(' p_cln_id -- '|| p_cin_unique_id,1);
232 END IF;
234
235 EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
236 (
237 p_api_version => 1.0,
238 p_commit => FND_API.g_FALSE,
239 p_init_msg_list => FND_API.G_FALSE,
240 p_cln_id => p_cin_unique_id,
241 p_disposition_code => l_apc_disposition,
242 p_disposition_date => sysdate,
243 x_return_status => l_return_status,
244 x_msg_count => l_msg_count,
245 x_msg_data => l_msg_data
246 );
247
248 IF (g_debug_Level <= 1) THEN
249 cln_debug_pub.Add('-- UPDATE_EVENT_DISPOSITION returned --',1);
250 cln_debug_pub.Add(' x_return_status -- '|| l_return_status,1);
251 cln_debug_pub.Add(' x_msg_count -- '|| l_msg_count,1);
252 cln_debug_pub.Add(' x_msg_data -- '|| l_msg_data,1);
253 END IF;
254
255 -- Direct call needed, since CLN Detail Id, is required for further processing
256 -- of map., CLN will defualt the rest of the parameters.
257 IF (g_debug_Level <= 1) THEN
258 cln_debug_pub.add('updating collaboration history for cln_id - ' || p_cin_unique_id,1);
259 END IF;
260
261 cln_ch_collaboration_pkg.update_collaboration(
262 x_return_status => x_return_status,
263 x_msg_data => x_msg_data,
264 p_coll_id => p_cin_unique_id,
265 p_msg_text => l_fnd_msg,
266 p_xmlg_msg_id => NULL,
267 p_xmlg_transaction_type => 'M4U',
268 p_xmlg_transaction_subtype => 'RESP_CIC',
269 p_xmlg_int_transaction_type => 'M4U',
270 p_xmlg_int_transaction_subtype => 'RESP_CIC',
271 p_doc_dir => 'IN',
272 p_doc_type => 'M4U_RESP_CIC',
273 p_disposition => l_disposition,
274 p_doc_status => l_doc_status,
275 p_coll_status => l_coll_status,
276 p_xmlg_document_id => NULL,
277 p_xmlg_internal_control_number => p_xmlg_internal_control_no,
278 p_tr_partner_type => m4u_ucc_utils.c_party_type,
279 p_tr_partner_id => m4u_ucc_utils.g_party_id,
280 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
281 p_attribute2 => p_target_market,
282 p_attribute5 => p_supp_gln,
283 p_attribute6 => p_retailer_gln,
284 p_attribute8 => p_ucc_doc_unique_id,
285 p_attribute10 => p_cic_unique_id,
286 p_attribute11 => p_cic_state,
287 p_doc_no => p_gtin,
288 p_dattribute1 => sysdate,
289 p_rosettanet_check_required => false,
290 x_dtl_coll_id => x_collab_detail_id );
291
292 IF (g_debug_Level <= 1) THEN
293 cln_debug_pub.add('collaboration history updated for cln_id - ' || p_cin_unique_id,1);
294 cln_debug_pub.add('Collab_detail_id - ' || x_collab_detail_id,1);
295 cln_debug_pub.add('x_return_status - ' || x_return_status,1);
296 cln_debug_pub.add('x_msg_data - ' || x_msg_data,1);
297 END IF;
298 x_msg_data := x_msg_data || ' ; EGO API update - ' || l_msg_data ;
299
300 IF x_return_status <> 'S' THEN
301 x_msg_data := 'Failure occured while processing CIC message in Query Worklist response - ' || x_msg_data;
302 END IF;
303
304 l_notif_mesg := 'Catalogue Item Confirmation received with following details';
305
306 l_event_params := wf_parameter_list_t();
307
308 wf_event.AddParameterToList(
309 p_name => 'COLLABORATION_ID',
310 p_value => p_cin_unique_id,
311 p_parameterlist => l_event_params );
312
313 wf_event.AddParameterToList(
314 p_name => 'COLLABORATION_POINT',
315 p_value => 'APPS',
316 p_parameterlist => l_event_params );
317
318 wf_event.AddParameterToList(
319 p_name => 'APPLICATION_ID',
320 p_value => m4u_ucc_utils.c_resp_appl_id,
321 p_parameterlist => l_event_params );
322
323 wf_event.AddParameterToList(
324 p_name => 'NOTIFICATION_CODE',
325 p_value => 'M4UCIC',
329 p_name => 'NOTIFICATION_STATUS',
326 p_parameterlist => l_event_params );
327
328 wf_event.AddParameterToList(
330 p_value => l_coll_status,
331 p_parameterlist => l_event_params );
332
333 wf_event.AddParameterToList(
334 p_name => 'NOTIFICATION_DESC',
335 p_value => l_notif_mesg,
336 p_parameterlist => l_event_params );
337
338
339 l_event_key := 'RESP_CIC_' || p_cic_unique_id;
340
341 IF (g_debug_Level <= 2) THEN
342 cln_debug_pub.add('--- raising Business Event ---',1);
343 cln_debug_pub.add('Event-name - ' || l_event_name,1);
344 cln_debug_pub.add('event-value -' || l_event_key ,1);
345 END IF;
346
347 -- raise event to trigger CLN Notifcations
348 wf_event.raise(
349 p_event_name => l_event_name,
350 p_event_key => l_event_key,
351 p_parameters => l_event_params
352 );
353
354 IF (g_debug_Level <= 2) THEN
355 cln_debug_pub.add('Exiting m4u_resp_process.process_cic_notification - normal',2);
356 cln_debug_pub.add('==========================================================',2);
357 END IF;
358 EXCEPTION
359 WHEN OTHERS THEN
360 -- log exception and exit.
361 l_error_code := SQLCODE;
362 l_error_msg := SQLERRM;
363 x_return_status := 'F';
364 x_msg_data := ' - Unexpected error occred while processing CIC message from Query Worklist response - ' || l_error_code || ':' || l_error_msg;
365
366
367 IF (g_debug_Level <= 5) THEN
368 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 5);
369 END IF;
370
371 IF (g_debug_Level <= 2) THEN
372 cln_debug_pub.add('Exiting m4u_resp_process.process_cic_notification - exception',2);
373 cln_debug_pub.add('=============================================================',2);
374 END IF;
375
376 END process_cic_notification;
377
378
379 -- Name
380 -- process_cic_additional_info
381 -- Purpose
382 -- This process is called to cater for additional attributes obtained in
383 -- CIC notification received from a Retailer(in worklist query response)
384
385 PROCEDURE process_cic_additional_info(
386 p_cln_id IN NUMBER,
387 p_cic_code IN VARCHAR2,
388 p_cic_description IN VARCHAR2,
389 p_cic_action_needed IN VARCHAR2,
390 p_collab_detail_id IN NUMBER,
391 x_return_status OUT NOCOPY VARCHAR2,
392 x_msg_data OUT NOCOPY VARCHAR2 )
393 IS
394 l_error_code NUMBER;
395 l_msg_count VARCHAR2(50);
396 l_return_status VARCHAR2(50);
397
398 l_error_msg VARCHAR2(2000);
399 l_coll_dtl_msg VARCHAR2(2000);
400 l_msg_data VARCHAR2(2000);
401
402 --l_add_cic_info EGO_UCCNET_EVENTS_PVT.ADD_CIC_INFO_TBL_TYPE;
403 --l_add_cic_info EGO_UCCNET_EVENTS_PVT.ADD_CIC_INFO_TYPE;
404
405 BEGIN
406 IF (g_debug_Level <= 2) THEN
407 cln_debug_pub.Add('-- Entering m4u_resp_process.process_cic_additional_info -- ', 2);
408 END IF;
409
410 IF (g_debug_Level <= 1) THEN
411 cln_debug_pub.Add('-- with paramters -- ', 1);
412 cln_debug_pub.Add('p_cln_id -- ' || p_cln_id, 1);
413 cln_debug_pub.Add('p_cic_code -- ' || p_cic_code, 1);
414 cln_debug_pub.Add('p_cic_description -- ' || p_cic_description, 1);
415 cln_debug_pub.Add('p_cic_action_needed -- ' || p_cic_action_needed, 1);
416 cln_debug_pub.Add('p_collab_detail_id -- ' || p_collab_detail_id, 1);
417 END IF;
418
419 -- adding the values for the ADD_CIC_INFO_TBL_TYPE type
420 IF (g_debug_Level <= 1) THEN
421 cln_debug_pub.Add('Adding the values for the ADD_CIC_INFO_TBL_TYPE type', 1);
422 END IF;
423
424
425 IF (g_debug_Level <= 1) THEN
426 cln_debug_pub.Add('-- Calling EGO_UCCNET_EVENTS_PUB.ADD_ADDITIONAL_CIC_INFO-- ', 1);
427 END IF;
428
429 ego_uccnet_events_pub.ADD_ADDITIONAL_CIC_INFO (
430 p_api_version => 1.0
431 ,p_commit => FND_API.g_FALSE
432 ,p_init_msg_list => FND_API.g_FALSE
433 ,p_cln_id => p_cln_id
434 ,p_cic_code => p_cic_code
435 ,p_cic_description => p_cic_description
439 ,x_msg_data => l_msg_data
436 ,p_cic_action_needed => p_cic_action_needed
437 ,x_return_status => l_return_status
438 ,x_msg_count => l_msg_count
440 );
441
442 IF (g_debug_Level <= 1) THEN
443 cln_debug_pub.Add('-- ADD_ADDITIONAL_CIC_INFO returned --',1);
444 cln_debug_pub.Add(' x_return_status -- '|| l_return_status,1);
445 cln_debug_pub.Add(' x_msg_count -- '|| l_msg_count,1);
446 cln_debug_pub.Add(' x_msg_data -- '|| l_msg_data,1);
447 END IF;
448
449 -- defaulting the message for the add collaboration msg API
450 IF (g_debug_Level <= 1) THEN
451 cln_debug_pub.Add('-- Defaulting the value for add collaboration msg ', 1);
452 END IF;
453
454 l_coll_dtl_msg := 'Desc - '||p_cic_description||
455 ' '||
456 'Action - '||p_cic_action_needed;
457
458 IF (g_debug_Level <= 1) THEN
459 cln_debug_pub.Add('-- l_coll_dtl_msg '||l_coll_dtl_msg, 1);
460 END IF;
461
462
463 -- Direct call needed, since CLN Detail Id, is required for further processing
464 -- of map., CLN will defualt the rest of the parameters.
465 IF (g_debug_Level <= 1) THEN
466 cln_debug_pub.add('updating collaboration history messages for cln_dtl_id - ' || p_collab_detail_id,1);
467 END IF;
468
469
470 cln_ch_collaboration_pkg.ADD_COLLABORATION_MESSAGES(
471 x_return_status => x_return_status,
472 x_msg_data => x_msg_data,
473 p_dtl_coll_id => p_collab_detail_id,
474 p_ref1 => p_cic_code,
475 p_dtl_msg => l_coll_dtl_msg);
476
477 IF (g_debug_Level <= 1) THEN
478 cln_debug_pub.add('collaboration history messages updated for cln_dtl_id - ' || p_collab_detail_id,1);
479 cln_debug_pub.add('x_return_status - ' || x_return_status,1);
480 cln_debug_pub.add('x_msg_data - ' || x_msg_data,1);
481 END IF;
482
483 x_msg_data := x_msg_data || ' Collaboration Add Messages update - ' || l_msg_data ;
484
485 IF x_return_status <> 'S' THEN
486 x_msg_data := 'Failure occured while processing CIC message in Query Worklist response - ' || x_msg_data;
487 END IF;
488
489 IF (g_debug_Level <= 2) THEN
490 cln_debug_pub.add('Exiting m4u_resp_process.process_cic_additional_info - normal',2);
491 cln_debug_pub.add('==========================================================',2);
492 END IF;
493 EXCEPTION
494 WHEN OTHERS THEN
495 -- log exception and exit.
496 l_error_code := SQLCODE;
497 l_error_msg := SQLERRM;
498 x_return_status := 'F';
499 x_msg_data := ' - Unexpected error occred while processing CIC message from Query Worklist response - ' || l_error_code || ':' || l_error_msg;
500
501
502 IF (g_debug_Level <= 5) THEN
503 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 5);
504 END IF;
505
506 IF (g_debug_Level <= 2) THEN
507 cln_debug_pub.add('Exiting m4u_resp_process.process_cic_additional_info - exception',2);
508 cln_debug_pub.add('=============================================================',2);
509 END IF;
510
511 END process_cic_additional_info;
512
513
514 -- process_cic_trade_item
515 -- Purpose
516 -- This process is called if the optional trade-item information is sent along with the CIC document
517 -- The Worklist-Query of the type/topic CatalogueItemConfirmation/RoutedDocument
518 -- contains CIC notifications issued by retailers
519 -- No processing is defined for this, dummy procedure call
520 -- Arguments
521 -- p_gtin => GTIN of item received as part of CIC
522 -- p_supp_gln => Supplier GLN of item received as part of CIC
523 -- p_target_market => Target market code of item received as part of CIC
524 -- p_reailter_gln => GLN of retailer who generated the CIC
525 -- p_cic_unique_id => uniquecreatorID of CIC document
526 -- p_cic_state => State of CIC
527 -- p_cin_unique_id => uniqueCreatorId of CIN for which this CIC is generated
528 -- p_ucc_doc_unique_id => uniqueCreatorId of UCCnet generated WLQ response
529 -- p_xmlg_internal_colntrol_no => XMLG map ICN
530 -- x_collab_detail_id => Collaboration history detail-id, to be used for further processing
531 -- x_return_status => return_status (s-Success/F-failure)
532 -- x_msg_data => return message
533 -- Notes
534 -- (GTIN + Supplier GLN + Target Code) identify a unique item registered in the UCCnet registry.
535
536 PROCEDURE process_cic_trade_item(
537 p_gtin IN VARCHAR2,
541 p_cic_unique_id IN VARCHAR2,
538 p_supp_gln IN VARCHAR2,
539 p_target_market IN vARCHAR2,
540 p_retailer_gln IN VARCHAR2,
542 p_cic_state IN VARCHAR2,
543 p_cin_unique_id IN VARCHAR2,
544 p_ucc_doc_unique_id IN VARCHAR2,
545 p_xmlg_internal_control_no IN NUMBER,
546 x_collab_detail_id OUT NOCOPY NUMBER,
547 x_return_status OUT NOCOPY VARCHAR2,
548 x_msg_data OUT NOCOPY VARCHAR2 )
549 IS
550 l_error_code NUMBER;
551 l_error_msg VARCHAR2(2000);
552 BEGIN
553
554
555 IF (g_debug_Level <= 2) THEN
556 cln_debug_pub.Add('==================================================', 2);
557 cln_debug_pub.Add('Entering - m4u_resp_process.process_cic_trade_item', 2);
558 END IF;
559
560
561
562 IF (g_debug_Level <= 1) THEN
563 cln_debug_pub.Add('-- Received parameters -- ', 1);
564 cln_debug_pub.Add('p_gtin --' || p_gtin, 1);
565 cln_debug_pub.Add('p_supp_gln --' || p_supp_gln, 1);
566 cln_debug_pub.Add('p_target_market --' || p_target_market, 1);
567 cln_debug_pub.Add('p_retailer_gln --' || p_retailer_gln, 1);
568 cln_debug_pub.Add('p_cic_unique_id --' || p_cic_unique_id, 1);
569 cln_debug_pub.Add('p_cic_state --' || p_cic_state, 1);
570 cln_debug_pub.Add('p_cin_unique_id --' || p_cin_unique_id, 1);
571 cln_debug_pub.Add('p_ucc_doc_unique_id --' || p_ucc_doc_unique_id, 1);
572 cln_debug_pub.Add('p_xmlg_internal_control_no --' || p_xmlg_internal_control_no, 1);
573 END IF;
574
575 x_return_status := 'S';
576 x_msg_data := 'CIC trade_item succesfully processed';
577
578 IF (g_debug_Level <= 2) THEN
579 cln_debug_pub.add('Exiting process_cic_trade_item - normal',2);
580 cln_debug_pub.add('=======================================',2);
581 END IF;
582
583 EXCEPTION
584 WHEN OTHERS THEN
585
586 -- log exception and exit.
587 l_error_code := SQLCODE;
588 l_error_msg := SQLERRM;
589 x_return_status := 'F';
590 x_msg_data := ' - Unexpected error while Processing CIC trade-item information, in Query Worklist Response - ' || l_error_code || ':' || l_error_msg;
591
592 IF (g_debug_Level <= 5) THEN
593 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
594 END IF;
595
596 IF (g_debug_Level <= 2) THEN
597 cln_debug_pub.add('Exiting m4u_resp_process.process_cic_trade_item - exception',2);
598 cln_debug_pub.add('===========================================================',2);
599 END IF;
600
601 END process_cic_trade_item;
602
603
604 -- Name
605 -- process_CIC_fication
606 -- Purpose
607 -- This process is called for each RCIR command in a transaction batch.
608 -- The Worklist-query response contains status of each of the RCIR command
609 -- The ego_uccnet_events table, and CLN Collaboration need to be updated
610 -- The Collaboration Detail Id is an out parameter, to be used subsequently for updating error messsages.
611 -- Arguments
612 -- p_gtin => The GTIN of the Item published in the Batch
613 -- p_supp_gln => Unique GLN of Supplier
614 -- p_target_market => target market to which the item is to be published
615 -- p_rcir_batch_id => The Batch_Id of the Batch in which the RCIR command was sent
616 -- p_rcir_command_success => TRUE/FALSE flag, indicates if Item-Registration was successful
617 -- p_rcir_command_unique_id => Unique Creartor Id of RCIR command (command level)
618 -- p_rcir_command_validation_key => Unique validation key, received for successful registration.
619 -- p_ucc_doc_unique_id => Unique UCCnet generated identifier for the document
620 -- p_xmlg_internal_control_no => XMLG Generated Unique Id for Document
621 -- x_collab_detail_id => The collab_detail_id returned by CLN Update CH API call.
622 -- x_return_status => return_status (s-Success/F-failure)
623 -- x_msg_data => return message
624 -- Notes
625 -- (GTIN + Supplier GLN + Target Code) identify a unique item registered in the UCCnet registry.
626 PROCEDURE process_rcir_notification(
627 p_gtin IN VARCHAR2,
628 p_supp_gln IN VARCHAR2,
629 p_target_market IN VARCHAR2,
630 p_rcir_batch_id IN VARCHAR2,
631 p_rcir_command_unique_id IN VARCHAR2,
635 p_xmlg_internal_control_no IN NUMBER,
632 p_rcir_command_success IN VARCHAR2,
633 p_rcir_command_validation_key IN VARCHAR2,
634 p_ucc_doc_unique_id IN VARCHAR2,
636 x_collab_detail_id OUT NOCOPY NUMBER,
637 x_return_status OUT NOCOPY VARCHAR2,
638 x_msg_data OUT NOCOPY VARCHAR2 )
639 IS
640 l_error_code NUMBER;
641 l_error_msg VARCHAR2(2000);
642 l_return_status VARCHAR2(50);
643 l_disposition VARCHAR2(50);
644 l_msg_data VARCHAR2(2000);
645 l_msg_text VARCHAR2(50);
646 l_msg_count VARCHAR2(50);
647 l_coll_status VARCHAR2(50);
648 l_doc_status VARCHAR2(50);
649 l_fnd_msg VARCHAR2(50);
650 l_apc_disposition VARCHAR2(50);
651 BEGIN
652
653 IF (g_debug_Level <= 2) THEN
654 cln_debug_pub.Add('=====================================', 2);
655 cln_debug_pub.Add('Entering -- process_rcir_notification', 2);
656 END IF;
657
658 IF (g_debug_Level <= 1) THEN
659 cln_debug_pub.Add('-- Received parameters -- ', 1);
660 cln_debug_pub.Add('p_gtin --' || p_gtin, 1);
661 cln_debug_pub.Add('p_supp_gln --' || p_supp_gln, 1);
662 cln_debug_pub.Add('p_target_market --' || p_target_market, 1);
663 cln_debug_pub.Add('p_rcir_batch_id --' || p_rcir_batch_id, 1);
664 cln_debug_pub.Add('p_rcir_command_unique_id --' || p_rcir_command_unique_id, 1);
665 cln_debug_pub.Add('p_rcir_command_success --' || p_rcir_command_success, 1);
666 cln_debug_pub.Add('p_rcir_command_validation_key --' || p_rcir_command_validation_key, 1);
667 cln_debug_pub.Add('p_ucc_doc_unique_id --' || p_ucc_doc_unique_id, 1);
668 cln_debug_pub.Add('p_xmlg_internal_control_no --' || p_xmlg_internal_control_no, 1);
669 END IF;
670
671
672 -- set CLN Disposition, CLN Coll Status, CLN Doc status, CLN Message Text
673 IF (UPPER(p_rcir_command_success) = 'TRUE') THEN
674 l_disposition := 'ACCEPTED' ;
675 l_msg_text := 'COMPLETED';
676 l_coll_status := 'COMPLETED';
677 l_doc_status := 'SUCCESS';
678 l_fnd_msg := 'M4U_RCIR_SUCCESS';
679 l_apc_disposition := 'PROCESSED';
680 ELSE
681 l_disposition := 'FAILED' ;
682 l_msg_text := 'REJECTED' ;
683 l_coll_status := 'ERROR';
684 l_doc_status := 'ERROR';
685 l_fnd_msg := 'M4U_RCIR_FAILURE';
686 l_apc_disposition := 'FAILED';
687 END IF;
688
689
690
691 IF (g_debug_Level <= 1) THEN
692 cln_debug_pub.Add('--- updating following columns of ego_uccnet_events --',1);
693 cln_debug_pub.Add(' disposition_code -- '|| l_apc_disposition,1);
694 cln_debug_pub.Add(' disposition_date -- '|| sysdate,1);
695 cln_debug_pub.Add(' last_updated_by -- '|| 1,1);
696 cln_debug_pub.Add(' last_updated_date -- '|| sysdate ,1);
697 cln_debug_pub.Add('-- where - CLN Id -- '|| p_rcir_command_unique_id,1);
698 END IF;
699
700 -- Make API call to update APC event disposition
701 IF (g_debug_Level <= 1) THEN
702 cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
703 cln_debug_pub.Add(' disposition_code -- '|| l_disposition,1);
704 cln_debug_pub.Add(' disposition_date -- '|| sysdate,1);
705 cln_debug_pub.Add(' p_cln_id -- '|| p_rcir_command_unique_id,1);
706 END IF;
707
708 EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
709 (
710 p_api_version => 1.0,
711 p_commit => FND_API.g_FALSE,
712 p_init_msg_list => FND_API.G_FALSE,
713 p_cln_id => p_rcir_command_unique_id,
714 p_disposition_code => l_apc_disposition,
715 p_disposition_date => sysdate,
716 x_return_status => x_return_status,
717 x_msg_count => l_msg_count,
718 x_msg_data => l_msg_data
719 );
720
721
722 IF (g_debug_Level <= 1) THEN
723 cln_debug_pub.Add('-- UPDATE_EVENT_DISPOSITION returned --',1);
724 cln_debug_pub.Add(' x_return_status-- '|| x_return_status,1);
725 cln_debug_pub.Add(' x_msg_count -- '|| l_msg_count,1);
726 cln_debug_pub.Add(' x_msg_data -- '|| l_msg_data,1);
727 END IF;
728
729 -- Direct call needed, since CLN Detail Id, is required for further processing
733 END IF;
730 -- of map., CLN will defualt the rest of the parameters.
731 IF (g_debug_Level <= 1) THEN
732 cln_debug_pub.add('updating collaboration history for cln_id - ' || p_rcir_command_unique_id,1);
734
735 cln_ch_collaboration_pkg.update_collaboration(
736 x_return_status => x_return_status,
737 x_msg_data => x_msg_data,
738 p_coll_id => p_rcir_command_unique_id,
739 p_msg_text => l_fnd_msg,
740 p_xmlg_msg_id => NULL,
741 p_xmlg_transaction_type => 'M4U',
742 p_xmlg_transaction_subtype => 'RESP_BATCH',
743 p_xmlg_int_transaction_type => 'M4U',
744 p_xmlg_int_transaction_subtype => 'RESP_BATCH',
745 p_doc_dir => 'IN',
746 p_doc_type => 'M4U_RESP_BATCH',
747 p_disposition => l_disposition,
748 p_doc_status => l_doc_status,
749 p_coll_status => l_coll_status,
750 p_xmlg_document_id => NULL,
751 p_xmlg_internal_control_number => p_xmlg_internal_control_no,
752 p_tr_partner_type => m4u_ucc_utils.c_party_type,
753 p_tr_partner_id => m4u_ucc_utils.g_party_id,
754 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
755 p_attribute2 => p_target_market,
756 p_attribute5 => p_supp_gln,
757 p_attribute8 => p_ucc_doc_unique_id,
758 p_attribute9 => p_rcir_command_validation_key,
759 p_dattribute1 => sysdate,
760 p_attribute11 => p_rcir_command_success,
761 p_doc_no => p_gtin,
762 p_rosettanet_check_required => false,
763 x_dtl_coll_id => x_collab_detail_id );
764
765
766
767 IF (g_debug_Level <= 1) THEN
768 cln_debug_pub.Add('Update collaboration returned');
769 cln_debug_pub.Add('Collaboration detail id - ' || x_collab_detail_id , 1);
770 cln_debug_pub.Add('Return status - ' || x_return_status, 1);
771 cln_debug_pub.Add('Message data - ' || x_msg_data, 1);
772 END IF;
773
774 x_msg_data := x_msg_data || ' ; EGO API update - ' || l_msg_data ;
775
776 IF x_return_status <> 'S' THEN
777 x_msg_data := 'Failure occured while processing RCIR Batch Notification in Query Worklist Response - ' || x_msg_data;
778 END IF;
779
780 IF (g_debug_Level <= 2) THEN
781 cln_debug_pub.Add('Exiting m4u_resp_process.process_rcir_notification - normal',2);
782 cln_debug_pub.Add('===========================================================',2);
783 END IF;
784
785 EXCEPTION
786 WHEN OTHERS THEN
787 -- log exception and exit.
788 l_error_code := SQLCODE;
789 l_error_msg := SQLERRM;
790 x_return_status := 'F';
791 x_msg_data := ' - Unexpected error in Processing RCIR notification in Query Worklist Response - ' || l_error_code || ':' || l_error_msg;
792
793
794 IF (g_debug_Level <= 5) THEN
795 cln_debug_pub.Add(l_error_code || ':' || l_error_msg, 4);
796 END IF;
797
798 IF (g_debug_Level <= 2) THEN
799 cln_debug_pub.Add('Exiting m4u_resp_process.process_rcir_notification - exception',2);
800 cln_debug_pub.Add('==============================================================',2);
801 END IF;
802
803 END process_rcir_notification;
804
805
806 -- Name
807 -- process_rcir_batch_list
808 -- Purpose
809 -- process all RCIR-BATCHes received in a Worlist-query Batch Notification reponse
810 -- raise event, for each batch-id to move corresponding WF block
811 -- Arguments
812 -- p_rcir_batch_id_list => distinct RCIR batch_ids which have been processed
813 -- are concatenated into a string, delimited by ':' string.
814 -- p_rcir_batch_count => Count of number of Batch_ids in the above string
815 -- p_ucc_doc_unique_id => unique UCCnet generated document identifer
816 -- p_xmlg_internal_control_no => XML Gateway generated Internal Control Number for document
817 -- x_return_status => return_status (s-Success/F-failure)
818 -- x_msg_data => return message
819 -- Notes
820 -- Sample input to this procedure would be
821 -- p_rcir_batch_id_list - rcir1@rcir2@rcir3@
822 -- p_rcir_batch_count - 3
823
824 PROCEDURE process_rcir_batch_list(
825 p_rcir_batch_id_list IN VARCHAR2,
826 p_rcir_batch_count IN NUMBER,
830 x_msg_data OUT NOCOPY VARCHAR2 )
827 p_ucc_doc_unique_id IN VARCHAR2,
828 p_xmlg_internal_control_no IN vARCHAR2,
829 x_return_status OUT NOCOPY VARCHAR2,
831 IS
832 l_prev_pos NUMBER;
833 l_cur_pos NUMBER;
834 l_batch_id VARCHAR2(50);
835 l_error_code NUMBER;
836 l_error_msg VARCHAR2(2000);
837 l_notif_mesg VARCHAR2(400);
838 l_event_name VARCHAR2(100);
839 l_event_key VARCHAR2(50);
840 l_owner_role VARCHAR2(50);
841 l_event_params wf_parameter_list_t;
842
843 BEGIN
844 l_event_name := 'oracle.apps.cln.np.processnotification';
845
846 IF (g_debug_Level <= 2) THEN
847 cln_debug_pub.Add('=================================================', 2);
848 cln_debug_pub.Add('Entering m4u_resp_process.process_rcir_batch_list', 2);
849 END IF;
850
851 IF (g_debug_Level <= 1) THEN
852 cln_debug_pub.Add('-- with paramerters --', 1);
853 cln_debug_pub.Add('p_rcir_batch_id_list -- ' || p_rcir_batch_id_list, 1);
854 cln_debug_pub.Add('p_rcir_batch_count -- ' || p_rcir_batch_count, 1);
855 cln_debug_pub.Add('p_rcir_batch_count -- ' || p_ucc_doc_unique_id, 1);
856 cln_debug_pub.Add('p_rcir_batch_count -- ' || p_xmlg_internal_control_no, 1);
857 END IF;
858
859 l_event_params := wf_parameter_list_t();
860 l_prev_pos := 1;
861
862 -- for each RCIR Batch_Id present in the DSV string
863 FOR i in 1 .. p_rcir_batch_count LOOP
864 -- parsing logic begins
865 l_cur_pos := INSTR(p_rcir_batch_id_list,'@',1,i);
866 l_batch_id := SUBSTR(p_rcir_batch_id_list,l_prev_pos,l_cur_pos-l_prev_pos);
867 l_prev_pos := l_cur_pos+1;
868 -- parsing logic ends,
869
870 -- now variable l_batch_id, contains the next RCIR batch_id to process
871 IF (g_debug_Level <= 1) THEN
872 cln_debug_pub.add('processing batch_id - ' || l_batch_id, 1);
873 END IF;
874
875 BEGIN
876 SELECT owner_role
877 INTO l_owner_role
878 FROM cln_coll_hist_hdr
879 WHERE attribute12 = l_batch_id AND rownum < 2
880 AND xmlg_transaction_type = 'M4U';
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 l_owner_role := fnd_profile.value('CLN_ADMINISTRATOR');
884 END;
885
886 IF (g_debug_Level <= 1) THEN
887 cln_debug_pub.add('Batch owner role obtained as -- '|| l_owner_role, 1);
888 END IF;
889
890 l_notif_mesg := 'RCIR Batch status received';
891
892 l_event_params := wf_parameter_list_t();
893
894 wf_event.AddParameterToList(
895 p_name => 'ATTRIBUTE_NAME',
896 p_value => 'ATTRIBUTE12',
897 p_parameterlist => l_event_params );
898
899 wf_event.AddParameterToList(
900 p_name => 'ATTRIBUTE_VALUE',
901 p_value => l_batch_id,
902 p_parameterlist => l_event_params );
903
904 wf_event.AddParameterToList(
905 p_name => 'BATCH_MODE_REQD',
906 p_value => 'Y',
907 p_parameterlist => l_event_params );
908
909
910 wf_event.AddParameterToList(
911 p_name => 'COLLABORATION_POINT',
912 p_value => 'APPS',
913 p_parameterlist => l_event_params );
914
915 wf_event.AddParameterToList(
916 p_name => 'COLLABORATION_STANDARD',
917 p_value => 'UCCNET',
918 p_parameterlist => l_event_params );
919
920 wf_event.AddParameterToList(
921 p_name => 'APPLICATION_ID',
922 p_value => m4u_ucc_utils.c_resp_appl_id,
923 p_parameterlist => l_event_params );
924
925 wf_event.AddParameterToList(
926 p_name => 'NOTIFICATION_CODE',
927 p_value => 'M4UBATCH',
928 p_parameterlist => l_event_params );
929
930 wf_event.AddParameterToList(
931 p_name => 'NOTIFICATION_STATUS',
932 p_value => 'SUCCESS',
933 p_parameterlist => l_event_params );
934
938 p_parameterlist => l_event_params );
935 wf_event.AddParameterToList(
936 p_name => 'NOTIFICATION_DESC',
937 p_value => l_notif_mesg,
939
940 wf_event.AddParameterToList(
941 p_name => 'NOTIFICATION_RECEIVER_ROLE',
942 p_value => l_owner_role,
943 p_parameterlist => l_event_params );
944
945 l_event_key := 'RESP_BATCH_' || l_batch_id;
946
947
948 IF (g_debug_Level <= 2) THEN
949 cln_debug_pub.add('--- raising Business Event ---',1);
950 cln_debug_pub.add('Event-name - ' || l_event_name,1);
951 cln_debug_pub.add('event-value -' || l_event_key ,1);
952 END IF;
953
954 -- raise event to trigger CLN Notifcations
955 wf_event.raise(
956 p_event_name => l_event_name,
957 p_event_key => l_event_key,
958 p_parameters => l_event_params
959 );
960
961
962
963
964 IF (g_debug_Level <= 1) THEN
965 cln_debug_pub.Add('Raising event name - ' || l_event_name, 1);
966 cln_debug_pub.Add(' event key -- ' || l_batch_id, 1);
967 END IF;
968 END LOOP;
969
970
971
972 x_return_status := 'S';
973 x_msg_data := 'RCIR Batch-list succesfully processed';
974
975 IF (g_debug_Level <= 2) THEN
976 cln_debug_pub.Add('Exiting m4u_resp_process.process_rcir_batch_list - normal',2);
977 cln_debug_pub.Add('=========================================================',2);
978 END IF;
979
980 EXCEPTION
981 WHEN OTHERS THEN
982 -- log exception and exit.
983
984 l_error_code := SQLCODE;
985 l_error_msg := SQLERRM;
986 x_return_status := 'F';
987 x_msg_data := ' - Unexpected error while raising notification for RCIR batches - ' || l_error_code || ':' || l_error_msg;
988
989
990 IF (g_debug_Level <= 5) THEN
991 cln_debug_pub.Add(l_error_code || ':' || l_error_msg, 4);
992 END IF;
993
994 IF (g_debug_Level <= 2) THEN
995 cln_debug_pub.Add('Exiting m4u_resp_process.process_rcir_batch_list - exception',2);
996 cln_debug_pub.Add('============================================================',2);
997 END IF;
998
999 END process_rcir_batch_list;
1000
1001
1002 -- Name
1003 -- process_wlq_response
1004 -- Purpose
1005 -- This process is called once for a Worklist query response document processed
1006 -- This is done to update the cLN Collaboration Id corresponding to the WLQ send
1007 -- Arguments
1008 -- p_wlq_message_id => Used as CLN_Id for the WLQ Collaboration
1009 -- p_wlq_success_flag => TRUE/FALSE flag, correspond to WLQ, generally true.
1010 -- p_ucc_unique_id => Unique document identifer for payload, from UCC
1011 -- p_xmlg_internal_control_no => XMLG generated Unique Id for inbound document
1012 -- x_collab_detail_id => collaboration history detail id
1013 -- x_return_status => return_status (s-Success/F-failure)
1014 -- x_msg_data => return message
1015 -- Notes
1016 -- No specific notes.
1017
1018 PROCEDURE process_wlq_response(
1019 p_wlq_message_id VARCHAR2,
1020 p_wlq_success_flag VARCHAR2,
1021 p_wlq_response_type VARCHAR2,
1022 p_ucc_doc_unique_id VARCHAR2,
1023 p_xmlg_internal_control_no VARCHAR2,
1024 x_collab_detail_id OUT NOCOPY NUMBER,
1025 x_return_status OUT NOCOPY VARCHAR2,
1026 x_msg_data OUT NOCOPY VARCHAR2 )
1027 IS
1028 l_error_code NUMBER;
1029 l_error_msg VARCHAR2(2000);
1030 l_disposition VARCHAR2(50);
1031 l_msg_text VARCHAR2(50);
1032 l_doc_status VARCHAR2(50);
1033 l_coll_status VARCHAR2(50);
1034 l_return_status VARCHAR2(20);
1035 l_msg_data VARCHAR2(2000);
1036 l_collab_detail_id VARCHAR2(50);
1037 l_fnd_msg VARCHAR2(50);
1038 l_coll_type VARCHAR2(50);
1039 l_doc_type VARCHAR2(50);
1040 BEGIN
1041 IF (g_debug_Level <= 2) THEN
1042 cln_debug_pub.Add('=====================================================', 2);
1043 cln_debug_pub.Add('-- Entering m4u_resp_process.process_wlq_response -- ', 2);
1044 END IF;
1045
1046 IF (g_debug_Level <= 1) THEN
1050 cln_debug_pub.Add('p_wlq_response_type -- ' || p_wlq_response_type, 1);
1047 cln_debug_pub.Add('-- with paramters -- ', 1);
1048 cln_debug_pub.Add('p_wlq_message_id -- ' || p_wlq_message_id , 1);
1049 cln_debug_pub.Add('p_wlq_success_flag -- ' || p_wlq_success_flag, 1);
1051 cln_debug_pub.Add('p_ucc_doc_unique_id -- ' || p_ucc_doc_unique_id, 1);
1052 cln_debug_pub.Add('p_xmlg_internal_control_no -- ' || p_xmlg_internal_control_no, 1);
1053 END IF;
1054
1055
1056 l_doc_type := 'M4U_' || p_wlq_response_type;
1057
1058 SELECT collaboration_type
1059 INTO l_coll_type
1060 FROM cln_coll_hist_hdr
1061 WHERE collaboration_id = p_wlq_message_id;
1062
1063 -- set CLN Dispositon, CLN Coll Status, CLN Message Text, CLN Doc Status
1064
1065 IF (UPPER(p_wlq_success_flag) = 'TRUE') THEN
1066 l_disposition := 'ACCEPTED';
1067 l_msg_text := 'COMPLETED';
1068 l_doc_status := 'SUCCESS';
1069 l_coll_status := 'COMPLETED';
1070 l_fnd_msg := 'M4U_WLQ_COMPLETED';
1071
1072 IF l_coll_type = 'M4U_PARTY_QUERY' THEN
1073 l_doc_type := 'M4U_RESP_PARTY';
1074 l_fnd_msg := 'M4U_PARTYQRY_SUCCESS';
1075 END IF;
1076 ELSE
1077 l_disposition := 'FAILED';
1078 l_msg_text := 'REJECTED';
1079 l_doc_status := 'ERROR';
1080 l_coll_status := 'ERROR';
1081 l_fnd_msg := 'M4U_WLQ_FAILURE';
1082 END IF;
1083
1084 -- Direct call needed, since CLN Detail Id, is required for further processing
1085 -- of map., CLN will defualt the rest of the parameters.
1086 IF (g_debug_Level <= 1) THEN
1087 cln_debug_pub.add('updating collaboration history for cln_id - ' || p_wlq_message_id,1);
1088 END IF;
1089
1090 cln_ch_collaboration_pkg.update_collaboration(
1091 x_return_status => x_return_status,
1092 x_msg_data => x_msg_data,
1093 p_coll_id => p_wlq_message_id,
1094 p_msg_text => l_fnd_msg,
1095 p_xmlg_msg_id => NULL,
1096 p_xmlg_transaction_type => 'M4U',
1097 p_xmlg_transaction_subtype => p_wlq_response_type,
1098 p_xmlg_int_transaction_type => 'M4U',
1099 p_xmlg_int_transaction_subtype => p_wlq_response_type,
1100 p_doc_dir => 'IN',
1101 p_doc_type => l_doc_type,
1102 p_disposition => l_disposition,
1103 p_doc_status => l_doc_status,
1104 p_coll_status => l_coll_status,
1105 p_xmlg_document_id => NULL,
1106 p_xmlg_internal_control_number => p_xmlg_internal_control_no,
1107 p_tr_partner_type => m4u_ucc_utils.c_party_type,
1108 p_tr_partner_id => m4u_ucc_utils.g_party_id,
1109 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
1110 p_attribute8 => p_ucc_doc_unique_id,
1111 p_dattribute1 => sysdate,
1112 p_attribute11 => p_wlq_success_flag,
1113 p_rosettanet_check_required => false,
1114 x_dtl_coll_id => x_collab_detail_id );
1115
1116
1117
1118 IF (g_debug_Level <= 1) THEN
1119 cln_debug_pub.add('collaboration history updated for CLN_uniqueid - ' || p_wlq_message_id,1);
1120 cln_debug_pub.add('Collab_detail_id - ' || x_collab_detail_id,1);
1121 cln_debug_pub.add('x_return_status - ' || x_return_status,1);
1122 cln_debug_pub.add('x_msg_data - ' || x_msg_data,1);
1123 END IF;
1124
1125 IF x_return_status <> 'S' THEN
1126 x_msg_data := 'Failure occured while processing collaboration history on Query Response - ' || x_msg_data;
1127 END IF;
1128
1129 IF (g_debug_Level <= 2) THEN
1130 cln_debug_pub.add('Exiting m4u_resp_process.process_wlq_response - normal',2);
1131 cln_debug_pub.add('======================================================',2);
1132 END IF;
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135
1136 -- log exception and exit.
1137 l_error_code := SQLCODE;
1138 l_error_msg := SQLERRM;
1139 x_return_status := 'F';
1140 x_msg_data := ' - Unexpected error occured while processing Query-Worklist Response - ' || l_error_code || ':' || l_error_msg;
1141
1142
1143 IF (g_debug_Level <= 5) THEN
1144 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
1145 END IF;
1146
1147 IF (g_debug_Level <= 2) THEN
1151
1148 cln_debug_pub.add('Exiting m4u_resp_process.process_wlq_response - exception',2);
1149 cln_debug_pub.add('=========================================================',2);
1150 END IF;
1152 END process_wlq_response;
1153
1154
1155 -- Name
1156 -- process_rcir_ack
1157 -- Purpose
1158 -- This process is called processing the synchronous Ack received from UCCnet for RCIR messages
1159 -- *The CLN Collaboration is updated with the response, and event is raised to move WF block*
1160 -- There is no need for this now.
1161 -- Arguments
1162 -- p_ack_type => Used as CLN_Id for the WLQ Collaboration
1163 -- p_command_unique_id => Unique id of command for which response is received
1164 -- p_command_success => Success/failure flag of command for which response is received
1165 -- p_command_validation_key => validation key if command is succesfully processed
1166 -- p_xmlg_internal_control_no => XMLG generated Unique Id for inbound document
1167 -- p_ucc_doc_unique_id => UCCnet generated UniqueId for response
1168 -- x_collab_detail_id => collab deatil-id to be returned to map
1169 -- x_return_status => return_status (s-Success/F-failure)
1170 -- x_msg_data => return message
1171 -- Notes
1172 -- No specific notes.
1173 PROCEDURE process_rcir_ack(
1174 p_gtin IN VARCHAR2,
1175 p_supp_gln IN VARCHAR2,
1176 p_target_market IN VARCHAR2,
1177 p_command_unique_id IN VARCHAR2,
1178 p_command_success IN VARCHAR2,
1179 p_command_validation_key IN VARCHAR2,
1180 p_ucc_doc_unique_id IN VARCHAR2,
1181 p_xmlg_internal_control_no IN NUMBER,
1182 x_collab_detail_id OUT NOCOPY NUMBER,
1183 x_return_status OUT NOCOPY VARCHAR2,
1184 x_msg_data OUT NOCOPY VARCHAR2 )
1185 IS
1186 l_error_code NUMBER;
1187 l_error_msg VARCHAR2(2000);
1188 l_doc_status VARCHAR2(50);
1189 l_return_status VARCHAR2(50);
1190 l_disposition VARCHAR2(20);
1191 l_msg_count VARCHAR2(50);
1192 l_msg_data VARCHAR2(2000);
1193 l_coll_status VARCHAR2(50);
1194 l_notif_mesg VARCHAR2(100);
1195 l_event_name VARCHAR2(100);
1196 l_event_params wf_parameter_list_t;
1197 l_event_key VARCHAR2(50);
1198 l_fnd_msg VARCHAR2(50);
1199 l_apc_disposition VARCHAR2(50);
1200 l_coll_type VARCHAR2(50);
1201 BEGIN
1202 l_event_name := 'oracle.apps.cln.np.processnotification';
1203
1204 IF (g_debug_Level <= 2) THEN
1205 cln_debug_pub.Add('==================================================', 2);
1206 cln_debug_pub.Add('-- Entering m4u_resp_process.process_rcir_ack -- ', 2);
1207 END IF;
1208
1209 IF (g_debug_Level <= 1) THEN
1210 cln_debug_pub.Add('-- with paramters -- ', 1);
1211 cln_debug_pub.Add('p_gtin -- ' || p_gtin, 1);
1212 cln_debug_pub.Add('p_supp_gln -- ' || p_supp_gln, 1);
1213 cln_debug_pub.Add('p_target_market -- ' || p_target_market, 1);
1214 cln_debug_pub.Add('p_command_unique_id -- ' || p_command_unique_id, 1);
1215 cln_debug_pub.Add('p_command_success -- ' || p_command_success, 1);
1216 cln_debug_pub.Add('p_command_validation_key -- ' || p_command_validation_key, 1);
1217 cln_debug_pub.Add('p_ucc_doc_unique_id -- ' || p_ucc_doc_unique_id, 1);
1218 cln_debug_pub.Add('p_xmlg_internal_contrlo_no -- ' || p_xmlg_internal_control_no, 1);
1219 END IF;
1220
1221 -- since same procedure is being used to process RCIR Ack, GTIN Query Ack
1222 -- and diff processing requirement for each of these messages
1223 SELECT collaboration_type
1224 INTO l_coll_type
1225 FROM cln_coll_hist_hdr
1226 WHERE collaboration_id = p_command_unique_id;
1227
1228 IF (g_debug_Level <= 1) THEN
1229 cln_debug_pub.Add('CLN Collaboration Type retrieved as - ' || l_coll_type, 1);
1230 END IF;
1231
1232
1233 -- set CLN Message Text, CLN Disposition, CLN Doc status, CLN coll status
1234 IF (UPPER(p_command_success) = 'TRUE') THEN
1235 l_disposition := 'ACCEPTED' ;
1236 l_apc_disposition := 'PROCESSED';
1237 l_coll_status := 'COMPLETED';
1238 l_doc_status := 'SUCCESS';
1239 l_fnd_msg := 'M4U_RCIR_SUCCESS';
1240 ELSE
1241 l_disposition := 'FAILED' ;
1242 l_apc_disposition := 'FAILED';
1243 l_coll_status := 'ERROR';
1244 l_doc_status := 'ERROR';
1248 -- set a diff message than RCIR ack for Query message failure response
1245 l_fnd_msg := 'M4U_RCIR_FAILURE';
1246 END IF;
1247
1249 IF l_coll_type = 'M4U_REGISTRY_QUERY' THEN
1250 l_fnd_msg := 'M4U_GTINQRY_FAILURE';
1251 END IF;
1252
1253 -- Make API call to update APC event disposition only if it is a M4U_RCIR response
1254 IF l_coll_type <> 'M4U_REGISTRY_QUERY' THEN
1255
1256 IF (g_debug_Level <= 1) THEN
1257 cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
1258 cln_debug_pub.Add(' disposition_code -- '|| l_apc_disposition,1);
1259 cln_debug_pub.Add(' disposition_date -- '|| sysdate,1);
1260 cln_debug_pub.Add(' p_cln_id -- '|| p_command_unique_id,1);
1261 END IF;
1262
1263
1264 EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
1265 (
1266 p_api_version => 1.0,
1267 p_commit => FND_API.g_FALSE,
1268 p_init_msg_list => FND_API.G_FALSE,
1269 p_cln_id => p_command_unique_id,
1270 p_disposition_code => l_apc_disposition,
1271 p_disposition_date => sysdate,
1272 x_return_status => l_return_status,
1273 x_msg_count => l_msg_count,
1274 x_msg_data => l_msg_data
1275 );
1276
1277
1278 IF (g_debug_Level <= 1) THEN
1279 cln_debug_pub.add('ego_uccnet_events disposition updated');
1280 cln_debug_pub.add('p_cln_id - ' || p_command_unique_id,1);
1281 cln_debug_pub.add('p_disposition_code - ' || l_apc_disposition,1);
1282 cln_debug_pub.add('p_disposition_date - ' || sysdate,1);
1283 cln_debug_pub.add('-- return values --',1);
1284 cln_debug_pub.add('x_return_status - ' || l_return_status,1);
1285 cln_debug_pub.add('x_msg_count - ' || l_msg_count,1);
1286 cln_debug_pub.add('x_msg_data - ' || l_msg_data,1);
1287 END IF;
1288 END IF;
1289
1290
1291 -- Direct call needed, since CLN Detail Id, is required for further processing
1292 -- of map., CLN will defualt the rest of the parameters.
1293 IF (g_debug_Level <= 1) THEN
1294 cln_debug_pub.add('updating collaboration history for cln_id - ' || p_command_unique_id,1);
1295 END IF;
1296
1297 CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION
1298 (
1299 x_return_status => x_return_status,
1300 x_msg_data => x_msg_data,
1301 p_coll_id => p_command_unique_id,
1302 p_msg_text => l_fnd_msg,
1303 p_xmlg_msg_id => NULL,
1304 p_xmlg_int_transaction_type => 'M4U',
1305 p_xmlg_int_transaction_subtype => 'RESP_ACK',
1306 p_xmlg_transaction_type => 'M4U',
1307 p_xmlg_transaction_subtype => 'RESP_ACK',
1308 p_doc_dir => 'IN',
1309 p_doc_type => 'M4U_RESP_ACK',
1310 p_disposition => l_disposition,
1311 p_doc_status => l_doc_status,
1312 p_coll_status => l_coll_status,
1313 p_tr_partner_type => m4u_ucc_utils.c_party_type,
1314 p_tr_partner_id => m4u_ucc_utils.g_party_id,
1315 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
1316 p_rosettanet_check_required => FALSE,
1317 x_dtl_coll_id => x_collab_detail_id,
1318 p_xmlg_internal_control_number => p_xmlg_internal_control_no,
1319 p_doc_creation_date => sysdate,
1320 p_attribute2 => p_target_market,
1321 p_attribute5 => p_supp_gln,
1322 p_attribute8 => p_ucc_doc_unique_id,
1323 p_attribute9 => p_command_validation_key,
1324 p_attribute11 => p_command_success,
1325 p_dattribute1 => sysdate,
1326 p_doc_no => p_gtin
1327
1328 );
1329
1330 x_msg_data := x_msg_data || ' - ' || l_msg_data;
1331 IF (g_debug_Level <= 1) THEN
1332 cln_debug_pub.add('collaboration history updated for cln_id - ' || p_command_unique_id,1);
1333 cln_debug_pub.add('Collab_detail_id - ' || x_collab_detail_id,1);
1334 cln_debug_pub.add('x_return_status - ' || x_return_status,1);
1335 cln_debug_pub.add('x_msg_data - ' || x_msg_data,1);
1339 IF l_coll_type <> 'M4U_REGISTRY_QUERY' THEN
1336 END IF;
1337
1338 -- Raise CLN notification event only for a M4U_RCIR response
1340
1341 l_notif_mesg := 'Acknowledgement received for RCIR, from UCCnet with following details';
1342
1343 l_event_params := wf_parameter_list_t();
1344
1345 wf_event.AddParameterToList(
1346 p_name => 'COLLABORATION_ID',
1347 p_value => p_command_unique_id,
1348 p_parameterlist => l_event_params );
1349
1350 wf_event.AddParameterToList(
1351 p_name => 'COLLABORATION_POINT',
1352 p_value => 'APPS',
1353 p_parameterlist => l_event_params );
1354
1355 wf_event.AddParameterToList(
1356 p_name => 'APPLICATION_ID',
1357 p_value => m4u_ucc_utils.c_resp_appl_id,
1358 p_parameterlist => l_event_params );
1359
1360 wf_event.AddParameterToList(
1361 p_name => 'NOTIFICATION_CODE',
1362 p_value => 'M4URCIR',
1363 p_parameterlist => l_event_params );
1364
1365 wf_event.AddParameterToList(
1366 p_name => 'NOTIFICATION_STATUS',
1367 p_value => l_coll_status,
1368 p_parameterlist => l_event_params );
1369
1370 wf_event.AddParameterToList(
1371 p_name => 'NOTIFICATION_DESC',
1372 p_value => l_notif_mesg,
1373 p_parameterlist => l_event_params );
1374
1375 l_event_key := 'RESP_ACK_RCIR_' || p_command_unique_id;
1376
1377
1378 IF (g_debug_Level <= 1) THEN
1379 cln_debug_pub.add('--- raising Business Event ---',1);
1380 cln_debug_pub.add('Event-name - ' || l_event_name,1);
1381 cln_debug_pub.add('event-value -' || l_event_key ,1);
1382 END IF;
1383
1384 -- raise event for notification
1385 wf_event.raise(
1386 p_event_name => l_event_name,
1387 p_event_key => l_event_key,
1388 p_parameters => l_event_params
1389 );
1390
1391
1392
1393 END IF;
1394
1395 IF (x_return_status <> 'S') THEN
1396 x_msg_data := 'Failure occured which processing RCIR Acknowledgement from UCCnet, - ' || x_msg_data;
1397 END IF;
1398
1399
1400 IF (g_debug_Level <= 2) THEN
1401 cln_debug_pub.add('Exiting m4u_resp_process.process_rcir_ack - normal',2);
1402 cln_debug_pub.add('==================================================',2);
1403 END IF;
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406
1407 -- log exception and exit.
1408 l_error_code := SQLCODE;
1409 l_error_msg := SQLERRM;
1410 x_return_status := 'F';
1411 x_msg_data := ' - Unexpected error in while processing RCIR acknowledgement - ' || l_error_code || ':' || l_error_msg;
1412
1413
1414 IF (g_debug_Level <= 5) THEN
1415 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
1416 END IF;
1417
1418 IF (g_debug_Level <= 2) THEN
1419 cln_debug_pub.add('Exiting m4u_resp_process.process_rcir_ack - exception',2);
1420 cln_debug_pub.add('=====================================================',2);
1421 END IF;
1422
1423 END process_rcir_ack;
1424
1425
1426
1427
1428 -- Name
1429 -- process_rcir_batch_ack
1430 -- Purpose
1431 -- This is used to process the RCIR Batch message
1432 -- In case of success, the individual RCIR collaborations are updated (status set to initiated)
1433 -- **In case of failure, the business event is raised to move WF block**
1434 -- Removing this as this is not required.
1435 -- Arguments
1436 -- p_command_unique_id => Unique id of batch
1437 -- p_supp_gln => unique GLN of supplier
1438 -- p_command_success => Success/failure flag of command for which response is received
1439 -- p_ucc_doc_unique_id => UCCnet generated UniqueId for response
1440 -- p_xmlg_internal_control_no => XMLG generated Unique Id for inbound document
1441 -- x_collab_detail_id => collab deatil-id to be returned to map
1442 -- x_return_status => return_status (s-Success/F-failure)
1443 -- x_msg_data => return message
1444 -- Notes
1445 -- No specific notes.
1446 PROCEDURE process_rcir_batch_ack(
1447 p_command_unique_id IN VARCHAR2,
1448 p_supp_gln IN VARCHAR2,
1452 x_collab_detail_id OUT NOCOPY NUMBER,
1449 p_command_success IN VARCHAR2,
1450 p_ucc_doc_unique_id IN VARCHAR2,
1451 p_xmlg_internal_control_no IN NUMBER,
1453 x_return_status OUT NOCOPY VARCHAR2,
1454 x_msg_data OUT NOCOPY VARCHAR2 )
1455
1456 IS
1457 l_error_code NUMBER;
1458 l_error_msg VARCHAR2(2000);
1459 l_doc_status VARCHAR2(50);
1460 l_return_status VARCHAR2(50);
1461 l_disposition VARCHAR2(50);
1462 l_msg_data VARCHAR2(2000);
1463 l_msg_count VARCHAR2(50);
1464 l_coll_status VARCHAR2(50);
1465 l_error_flag VARCHAR2(50);
1466 l_notif_mesg VARCHAR2(100);
1467 l_event_name VARCHAR2(100);
1468 l_owner_role VARCHAR2(100);
1469 l_event_params wf_parameter_list_t;
1470 l_event_key VARCHAR2(50);
1471 l_fnd_msg VARCHAR2(50);
1472 l_apc_disposition VARCHAR2(50);
1473
1474 CURSOR c_clnid_for_batch (p_batch_id VARCHAR) IS
1475 SELECT collaboration_id
1476 FROM cln_coll_hist_hdr
1477 WHERE attribute12 = p_batch_id
1478 AND xmlg_transaction_type = 'M4U';
1479
1480 BEGIN
1481 l_event_name := 'oracle.apps.cln.np.processnotification';
1482
1483 IF (g_debug_Level <= 2) THEN
1484 cln_debug_pub.Add('=======================================================', 2);
1485 cln_debug_pub.Add('-- Entering m4u_resp_process.process_rcir_batch_ack -- ', 2);
1486 END IF;
1487
1488 IF (g_debug_Level <= 1) THEN
1489 cln_debug_pub.Add('-- with parameters -- ', 1);
1490 cln_debug_pub.Add('p_command_unique_id -- ' || p_command_unique_id, 1);
1491 cln_debug_pub.Add('p_supp_gln -- ' || p_supp_gln, 1);
1492 cln_debug_pub.Add('p_command_success -- ' || p_command_success, 1);
1493 cln_debug_pub.Add('p_ucc_doc_unique_id -- ' || p_ucc_doc_unique_id, 1);
1494 cln_debug_pub.Add('p_xmlg_internal_control_no -- ' || p_xmlg_internal_control_no, 1);
1495 END IF;
1496
1497 -- set CLN message, CLN Doc status, CLN disposition, CLN Coll Status
1498 IF (UPPER(p_command_success) = 'TRUE') THEN
1499 l_disposition := 'PENDING' ;
1500 l_coll_status := 'INITIATED';
1501 l_doc_status := 'SUCCESS';
1502 l_fnd_msg := 'M4U_RCIR_BATCH_ACCEPTED';
1503 l_apc_disposition := 'PROCESSED';
1504 ELSE
1505 l_disposition := 'FAILED' ;
1506 l_coll_status := 'ERROR';
1507 l_doc_status := 'ERROR';
1508 l_fnd_msg := 'M4U_RCIR_BATCH_REJECTED';
1509 l_apc_disposition := 'FAILED';
1510 END IF;
1511
1512 -- for every CLN-ID corresponding to individual rcir commands in the batch
1513 -- 1. update ego_uccnet_table disposition.
1514 -- 2. update CLN Collaboration.
1515 FOR rec_cln_id IN c_clnid_for_batch(p_command_unique_id) LOOP
1516
1517
1518 IF (UPPER(p_command_success) <> 'TRUE') THEN
1519 -- Make API call to update APC event disposition
1520 IF (g_debug_Level <= 1) THEN
1521 cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
1522 cln_debug_pub.Add(' disposition_code -- '|| l_apc_disposition,1);
1523 cln_debug_pub.Add(' disposition_date -- '|| sysdate,1);
1524 cln_debug_pub.Add(' p_cln_id -- '|| rec_cln_id.collaboration_id,1);
1525 END IF;
1526
1527 EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
1528 (
1529 p_api_version => 1.0,
1530 p_commit => FND_API.g_FALSE,
1531 p_init_msg_list => FND_API.G_FALSE,
1532 p_cln_id => rec_cln_id.collaboration_id,
1533 p_disposition_code => l_apc_disposition,
1534 p_disposition_date => sysdate,
1535 x_return_status => l_return_status,
1536 x_msg_count => l_msg_count,
1537 x_msg_data => l_msg_data
1538 );
1539
1540 IF (g_debug_Level <= 1) THEN
1541 cln_debug_pub.Add('-- UPDATE_EVENT_DISPOSITION returned --',1);
1542 cln_debug_pub.Add(' x_return_status -- ' || l_return_status,1);
1543 cln_debug_pub.Add(' x_msg_count -- ' || l_msg_count,1);
1544 cln_debug_pub.Add(' x_msg_data -- ' || l_msg_data,1);
1548
1545 END IF;
1546
1547 END IF;
1549 -- Direct call needed, since CLN Detail Id, is required for further processing
1550 -- of map., CLN will defualt the rest of the parameters.
1551 IF (g_debug_Level <= 1) THEN
1552 cln_debug_pub.add('updating collaboration history for cln_id - ' || rec_cln_id.collaboration_id,1);
1553 END IF;
1554
1555 CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION
1556 (
1557 x_return_status => x_return_status,
1558 x_msg_data => x_msg_data,
1559 p_coll_id => rec_cln_id.collaboration_id ,
1560 p_msg_text => l_fnd_msg,
1561 p_xmlg_msg_id => NULL,
1562 p_xmlg_int_transaction_type => 'M4U',
1563 p_xmlg_int_transaction_subtype => 'RESP_ACK',
1564 p_xmlg_transaction_type => 'M4U',
1565 p_xmlg_transaction_subtype => 'RESP_ACK',
1566 p_doc_dir => 'IN',
1567 p_doc_type => 'M4U_RESP_ACK',
1568 p_disposition => l_disposition,
1569 p_doc_status => l_doc_status,
1570 p_coll_status => l_coll_status,
1571 p_tr_partner_type => m4u_ucc_utils.c_party_type,
1572 p_tr_partner_id => m4u_ucc_utils.g_party_id,
1573 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
1574 p_rosettanet_check_required => FALSE,
1575 x_dtl_coll_id => x_collab_detail_id,
1576 p_xmlg_internal_control_number => p_xmlg_internal_control_no,
1577 p_doc_creation_date => sysdate,
1578 p_attribute8 => p_ucc_doc_unique_id,
1579 p_attribute10 => p_command_unique_id,
1580 p_attribute11 => 'FALSE'
1581 );
1582
1583
1584 IF (g_debug_Level <= 1) THEN
1585 cln_debug_pub.add('collaboration history updated for cln_id - ' || rec_cln_id.collaboration_id,1);
1586 cln_debug_pub.add('Collab_detail_id - ' || x_collab_detail_id,1);
1587 cln_debug_pub.add('x_return_status - ' || x_return_status,1);
1588 cln_debug_pub.add('x_msg_data - ' || x_msg_data,1);
1589 END IF;
1590
1591
1592 IF x_return_status <> 'S' THEN
1593 l_error_flag := 'TRUE';
1594 l_error_msg := 'Failure occured while updating collaboration history on RCIR Batch response - ' || x_msg_data;
1595 END IF;
1596
1597
1598
1599 END LOOP;
1600
1601 IF UPPER(p_command_success) <> 'TRUE' THEN
1602
1603
1604 l_notif_mesg := 'RCIR Batch Payload Rejected by UCCnet';
1605
1606 l_event_params := wf_parameter_list_t();
1607
1608
1609 wf_event.AddParameterToList(
1610 p_name => 'ATTRIBUTE_NAME',
1611 p_value => 'ATTRIBUTE12',
1612 p_parameterlist => l_event_params );
1613
1614
1615 wf_event.AddParameterToList(
1616 p_name => 'ATTRIBUTE_VALUE',
1617 p_value => p_command_unique_id,
1618 p_parameterlist => l_event_params );
1619
1620 wf_event.AddParameterToList(
1621 p_name => 'BATCH_MODE_REQD',
1622 p_value => 'Y',
1623 p_parameterlist => l_event_params );
1624
1625 wf_event.AddParameterToList(
1626 p_name => 'COLLABORATION_POINT',
1627 p_value => 'APPS',
1628 p_parameterlist => l_event_params );
1629
1630 wf_event.AddParameterToList(
1631 p_name => 'COLLABORATION_STANDARD',
1632 p_value => 'UCCNET',
1633 p_parameterlist => l_event_params );
1634
1635 wf_event.AddParameterToList(
1636 p_name => 'APPLICATION_ID',
1637 p_value => m4u_ucc_utils.c_resp_appl_id,
1638 p_parameterlist => l_event_params );
1639
1640 wf_event.AddParameterToList(
1641 p_name => 'NOTIFICATION_CODE',
1642 p_value => 'M4UBCHAK',
1646 p_name => 'NOTIFICATION_STATUS',
1643 p_parameterlist => l_event_params );
1644
1645 wf_event.AddParameterToList(
1647 p_value => l_coll_status,
1648 p_parameterlist => l_event_params );
1649
1650 wf_event.AddParameterToList(
1651 p_name => 'NOTIFICATION_DESC',
1652 p_value => l_notif_mesg,
1653 p_parameterlist => l_event_params );
1654
1655
1656 BEGIN
1657 SELECT owner_role
1658 INTO l_owner_role
1659 FROM cln_coll_hist_hdr
1660 WHERE attribute12 = p_command_unique_id AND rownum < 2
1661 AND xmlg_transaction_type = 'M4U';
1662 EXCEPTION
1663 WHEN NO_DATA_FOUND THEN
1664 l_owner_role := fnd_profile.value('CLN_ADMINISTRATOR');
1665 END;
1666
1667 wf_event.AddParameterToList(
1668 p_name => 'NOTIFICATION_RECEIVER_ROLE',
1669 p_value => l_owner_role,
1670 p_parameterlist => l_event_params );
1671
1672
1673 l_event_key := 'M4U_RCIR_BATCH_AK_' || p_command_unique_id;
1674
1675 IF (g_debug_Level <= 1) THEN
1676 cln_debug_pub.add('--- raising Business Event ---',1);
1677 cln_debug_pub.add('Event-name - ' || l_event_name,1);
1678 cln_debug_pub.add('event-value -' || l_event_key ,1);
1679 END IF;
1680
1681 -- raise event to trigger outbound wlq generation WF
1682 wf_event.raise(
1683 p_event_name => l_event_name,
1684 p_event_key => l_event_key,
1685 p_parameters => l_event_params
1686 );
1687 END IF;
1688
1689
1690 IF l_error_flag = 'TRUE' THEN
1691 x_return_status := 'F';
1692 x_msg_data := l_error_msg;
1693 ELSE
1694 x_return_status := 'S';
1695 x_msg_data := 'Successfully updated all RCIR-Batch collaborations on RCIR Batch response';
1696 END IF;
1697
1698 IF (g_debug_Level <= 2) THEN
1699 cln_debug_pub.add('Exiting m4u_resp_process.process_rcir_batch_ack - normal',2);
1700 cln_debug_pub.add('========================================================',2);
1701 END IF;
1702 EXCEPTION
1703 WHEN OTHERS THEN
1704 -- log exception and exit.
1705 l_error_code := SQLCODE;
1706 l_error_msg := SQLERRM;
1707 x_return_status := 'F';
1708 x_msg_data := ' - Unexpected error while processing RCIR Batch response - ' || l_error_code || ':' || l_error_msg;
1709
1710
1711 IF (g_debug_Level <= 5) THEN
1712 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
1713 END IF;
1714
1715 IF (g_debug_Level <= 2) THEN
1716 cln_debug_pub.add('Exiting m4u_resp_process.process_rcir_batch_ack - exception',2);
1717 cln_debug_pub.add('===========================================================',2);
1718 END IF;
1719
1720 END process_rcir_batch_ack;
1721
1722
1723 -- Name
1724 -- process_cin_ack
1725 -- Purpose
1726 -- This process is called for processing the synchronous Ack received from UCCnet for CIN messages
1727 -- The CLN Collaboration, ego_uccnet_events table are updated with the response
1728 -- A CLN notification event is raised.
1729 -- Arguments
1730 -- p_command_unique_id => Unique id of command for which response is received
1731 -- p_supp_gln => datasource/gln of document originator
1732 -- p_command_success => Success/failure flag of command for which response is received
1733 -- p_command_validation_key => validation key if command is succesfully processed
1734 -- p_ucc_doc_unique_id => UCCnet generated UniqueId for response document
1735 -- p_xmlg_internal_control_no => XMLG generated Unique Id for inbound document
1736 -- x_collab_detail_id => collab deatil-id to be returned to map
1737 -- x_return_status => return_status (S-Success/F-failure)
1738 -- x_msg_data => return message
1739 -- Notes
1740 -- No specific notes.
1741 PROCEDURE process_cin_ack(
1742 p_command_unique_id IN VARCHAR2,
1743 p_supp_gln IN VARCHAR2,
1744 p_command_success IN VARCHAR2,
1745 p_command_validation_key IN VARCHAR2,
1746 p_ucc_doc_unique_id IN VARCHAR2,
1747 p_xmlg_internal_control_no IN NUMBER,
1751 IS
1748 x_collab_detail_id OUT NOCOPY NUMBER,
1749 x_return_status OUT NOCOPY VARCHAR2,
1750 x_msg_data OUT NOCOPY VARCHAR2 )
1752 l_error_code NUMBER;
1753 l_error_msg VARCHAR2(2000);
1754 l_doc_status VARCHAR2(50);
1755 l_return_status VARCHAR2(50);
1756 l_disposition VARCHAR2(50);
1757 l_msg_data VARCHAR2(2000);
1758 l_coll_status VARCHAR2(50);
1759 l_msg_count VARCHAR2(50);
1760 l_notif_mesg VARCHAR2(100);
1761 l_event_name VARCHAR2(100);
1762 l_event_params wf_parameter_list_t;
1763 l_event_key VARCHAR2(50);
1764 l_fnd_msg VARCHAR2(50);
1765 l_apc_disposition VARCHAR2(50);
1766 BEGIN
1767 l_event_name := 'oracle.apps.cln.np.processnotification';
1768
1769 IF (g_debug_Level <= 2) THEN
1770 cln_debug_pub.Add('================================================', 2);
1771 cln_debug_pub.Add('-- Entering m4u_resp_process.process_cin_ack -- ', 2);
1772 END IF;
1773
1774 IF (g_debug_Level <= 1) THEN
1775 cln_debug_pub.Add('-- with paramters -- ', 1);
1776 cln_debug_pub.Add('p_command_unique_id -- ' || p_command_unique_id, 1);
1777 cln_debug_pub.Add('p_supp_gln -- ' || p_command_unique_id, 1);
1778 cln_debug_pub.Add('p_command_success -- ' || p_command_success, 1);
1779 cln_debug_pub.Add('p_command_validation_key -- ' || p_command_validation_key, 1);
1780 cln_debug_pub.Add('p_ucc_doc_unique_id -- ' || p_ucc_doc_unique_id, 1);
1781 cln_debug_pub.Add('p_xmlg_internal_contrlo_no -- ' || p_xmlg_internal_control_no, 1);
1782 END IF;
1783
1784 -- set CLN Disposition, CLN Document status, CLN status
1785
1786 -- set CLN Message Text based on command sucess/failure parameter
1787 IF (UPPER(p_command_success) = 'TRUE') THEN
1788 l_disposition := 'PENDING' ;
1789 l_coll_status := 'INITIATED';
1790 l_doc_status := 'SUCCESS';
1791 l_fnd_msg := 'M4U_CIN_AK_SUCCESS';
1792 l_apc_disposition := 'PROCESSED';
1793 ELSE
1794 l_disposition := 'FAILED' ;
1795 l_coll_status := 'ERROR';
1796 l_doc_status := 'ERROR';
1797 l_fnd_msg := 'M4U_CIN_AK_FAILURE';
1798 l_apc_disposition := 'FAILED';
1799 END IF;
1800
1801 -- Make API call to update APC event disposition
1802 IF (g_debug_Level <= 1) THEN
1803 cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
1804 cln_debug_pub.Add(' disposition_code -- '|| l_apc_disposition,1);
1805 cln_debug_pub.Add(' disposition_date -- '|| sysdate,1);
1806 cln_debug_pub.Add(' p_cln_id -- '|| p_command_unique_id,1);
1807 END IF;
1808
1809 EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
1810 (
1811 p_api_version => 1.0,
1812 p_commit => FND_API.g_FALSE,
1813 p_init_msg_list => FND_API.G_FALSE,
1814 p_cln_id => p_command_unique_id,
1815 p_disposition_code => l_apc_disposition,
1816 p_disposition_date => sysdate,
1817 x_return_status => l_return_status,
1818 x_msg_count => l_msg_count,
1819 x_msg_data => l_msg_data
1820 );
1821
1822 IF (g_debug_Level <= 1) THEN
1823 cln_debug_pub.Add('-- UPDATE_EVENT_DISPOSITION returned --',1);
1824 cln_debug_pub.Add(' x_return_status -- '|| l_return_status,1);
1825 cln_debug_pub.Add(' x_msg_count -- '|| l_msg_count,1);
1826 cln_debug_pub.Add(' x_msg_data -- '|| l_msg_data,1);
1827 END IF;
1828
1829
1830 -- Direct call needed, since CLN Detail Id, is required for further processing
1831 -- of map., CLN will defualt the rest of the parameters.
1832 IF (g_debug_Level <= 1) THEN
1833 cln_debug_pub.add('updating collaboration history for cln_id - ' || p_command_unique_id,1);
1834 END IF;
1835
1836 CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION
1837 (
1838 x_return_status => x_return_status,
1839 x_msg_data => x_msg_data,
1840 p_coll_id => p_command_unique_id ,
1841 p_msg_text => l_fnd_msg,
1842 p_xmlg_msg_id => NULL,
1843 p_xmlg_int_transaction_type => 'M4U',
1844 p_xmlg_int_transaction_subtype => 'RESP_ACK',
1845 p_xmlg_transaction_type => 'M4U',
1846 p_xmlg_transaction_subtype => 'RESP_ACK',
1850 p_doc_status => l_doc_status,
1847 p_doc_dir => 'IN',
1848 p_doc_type => 'M4U_RESP_ACK',
1849 p_disposition => l_disposition,
1851 p_coll_status => l_coll_status,
1852 p_tr_partner_type => m4u_ucc_utils.c_party_type,
1853 p_tr_partner_id => m4u_ucc_utils.g_party_id,
1854 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
1855 p_rosettanet_check_required => FALSE,
1856 x_dtl_coll_id => x_collab_detail_id,
1857 p_xmlg_internal_control_number => p_xmlg_internal_control_no,
1858 p_doc_creation_date => sysdate,
1859 p_attribute8 => p_ucc_doc_unique_id,
1860 p_attribute9 => p_command_validation_key,
1861 p_attribute11 => p_command_success
1862 );
1863
1864
1865 IF (g_debug_Level <= 1) THEN
1866 cln_debug_pub.add('collaboration history updated for cln_id - ' || p_command_unique_id,1);
1867 cln_debug_pub.add('Collab_detail_id - ' || x_collab_detail_id,1);
1868 cln_debug_pub.add('x_return_status - ' || x_return_status,1);
1869 cln_debug_pub.add('x_msg_data - ' || x_msg_data,1);
1870 END IF;
1871
1872 IF x_return_status <> 'S' THEN
1873 x_msg_data := 'Error occured while processing response from UCCnet for CIN message - ' || x_msg_data;
1874 END IF;
1875
1876 l_notif_mesg := 'Acknowledgement received from UCCnet for CIN message';
1877
1878 l_event_params := wf_parameter_list_t();
1879 wf_event.AddParameterToList(
1880 p_name => 'COLLABORATION_ID',
1881 p_value => p_command_unique_id,
1882 p_parameterlist => l_event_params );
1883
1884 wf_event.AddParameterToList(
1885 p_name => 'COLLABORATION_POINT',
1886 p_value => 'APPS',
1887 p_parameterlist => l_event_params );
1888
1889 wf_event.AddParameterToList(
1890 p_name => 'APPLICATION_ID',
1891 p_value => m4u_ucc_utils.c_resp_appl_id,
1892 p_parameterlist => l_event_params );
1893
1894 wf_event.AddParameterToList(
1895 p_name => 'NOTIFICATION_CODE',
1896 p_value => 'M4UCINAK',
1897 p_parameterlist => l_event_params );
1898
1899 wf_event.AddParameterToList(
1900 p_name => 'NOTIFICATION_STATUS',
1901 p_value => l_coll_status,
1902 p_parameterlist => l_event_params );
1903
1904 wf_event.AddParameterToList(
1905 p_name => 'NOTIFICATION_DESC',
1906 p_value => l_notif_mesg,
1907 p_parameterlist => l_event_params );
1908
1909 l_event_key := 'M4U_CINAK_' || p_command_unique_id;
1910
1911 IF (g_debug_Level <= 1) THEN
1912 cln_debug_pub.add('--- raising Business Event ---',1);
1913 cln_debug_pub.add('Event-name - ' || l_event_name,1);
1914 cln_debug_pub.add('event-value -' || l_event_key ,1);
1915 END IF;
1916
1917 -- raise event to trigger outbound wlq generation WF
1918 wf_event.raise(
1919 p_event_name => l_event_name,
1920 p_event_key => l_event_key,
1921 p_parameters => l_event_params
1922 );
1923
1924 IF (g_debug_Level <= 2) THEN
1925 cln_debug_pub.add('Exiting m4u_resp_process.process_cin_ack - normal',2);
1926 cln_debug_pub.add('=================================================',2);
1927 END IF;
1928 EXCEPTION
1929 WHEN OTHERS THEN
1930 -- log exception and exit.
1931 l_error_code := SQLCODE;
1932 l_error_msg := SQLERRM;
1933 x_return_status := 'F';
1934 x_msg_data := ' - Unexpected error while processing CIN response - ' || l_error_code || ':' || l_error_msg;
1935
1936
1937 IF (g_debug_Level <= 5) THEN
1938 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
1939 END IF;
1940
1941 IF (g_debug_Level <= 2) THEN
1942 cln_debug_pub.add('Exiting m4u_resp_process.process_cin_ack - exception',2);
1943 cln_debug_pub.add('====================================================',2);
1944 END IF;
1945 END process_cin_ack;
1946
1947
1948 -- Name
1949 -- process_rfcin_notification
1953 -- filter in the Query is name = "REQUEST_FOR_NOTIFICATION".
1950 -- Purpose
1951 -- This process is called for processing the "Request For Catalogue Item Notification" notifiacation
1952 -- from UCCnet. These notifications are recived as part of Worklistquery Responses where the
1954 -- A CLN collaboration is to be created for this messsage.
1955 -- A CLN Notification is to be raised on for each of these responses received.
1956 -- Arguments
1957 -- p_wlq_message_id => Unique identifier Worklist-query for which this notification is received as response
1958 -- p_target_market_country => Retailer wants notification for item(s) of this target market
1959 -- p_target_market_subdivision => Retailer wants notification for item(s) of this target market sub division
1960 -- p_gtin => Retailer wants notification for this specific GTIN
1961 -- p_data_recipient => GLN of (this) supplier
1962 -- p_data_source => Datasoure
1963 -- p_recipient_data_pool => Datapool of recipient
1964 -- p_classification_category => Retailer wants notification for items of this Category
1965 -- p_rfcin_unique_id => unique command level id of RFCIN command
1966 -- p_rfcin_owner_gln => GLN of retailer who issued this command
1967 -- p_reload_flag => flag specifies whether retailer wants new GTIN or reload exisiting data
1968 -- p_ucc_doc_unique_id => unique id of UCCnet message(worklist query response)
1969 -- p_xmlg_internal_control_no => XMLG generated internal control number
1970 -- x_return_status => out param. ret status from CLN API call
1971 -- x_msg_data => out param. ret message from CLN API call
1972 -- Notes
1973 -- No specific notes.
1974 PROCEDURE process_rfcin_notification(
1975 p_wlq_message_id IN VARCHAR2,
1976 p_target_market_country IN VARCHAR2,
1977 p_target_market_subdivision IN VARCHAR2,
1978 p_gtin IN VARCHAR2,
1979 p_data_recipient IN VARCHAR2,
1980 p_data_source IN VARCHAR2,
1981 p_recipient_data_pool IN VARCHAR2,
1982 p_classification_category IN VARCHAR2,
1983 p_rfcin_unique_id IN VARCHAR2,
1984 p_rfcin_owner_gln IN VARCHAR2,
1985 p_reload_flag IN VARCHAR2,
1986 p_rfcin_creation_date IN VARCHAR2,
1987 p_ucc_doc_unique_id IN VARCHAR2,
1988 p_xmlg_internal_control_no IN NUMBER,
1989 x_return_status OUT NOCOPY VARCHAR2,
1990 x_msg_data OUT NOCOPY VARCHAR2 )
1991
1992
1993 IS
1994 l_error_code VARCHAR2(50);
1995 l_error_msg VARCHAR2(255);
1996 l_return_status VARCHAR2(50);
1997 l_msg_data VARCHAR2(255);
1998 l_coll_id VARCHAR2(50);
1999 l_notif_mesg VARCHAR2(400);
2000 l_event_name VARCHAR2(100);
2001 l_event_key VARCHAR2(50);
2002 l_event_params wf_parameter_list_t;
2003 BEGIN
2004
2005 l_event_name := 'oracle.apps.cln.np.processnotification';
2006
2007 IF (g_debug_Level <= 2) THEN
2008 cln_debug_pub.Add('-- Entering m4u_resp_process.process_rfcin_notification -- ', 2);
2009 END IF;
2010
2011 IF (g_debug_Level <= 1) THEN
2012 cln_debug_pub.Add('-- with paramters -- ', 1);
2013 cln_debug_pub.Add('p_wlq_message_id -- ' || p_wlq_message_id, 1);
2014 cln_debug_pub.Add('p_target_market_country -- ' || p_target_market_country, 1);
2015 cln_debug_pub.Add('p_target_market_subdivision -- ' || p_target_market_subdivision, 1);
2016 cln_debug_pub.Add('p_gtin -- ' || p_gtin, 1);
2017 cln_debug_pub.Add('p_data_recipient -- ' || p_data_recipient, 1);
2018 cln_debug_pub.Add('p_data_source -- ' || p_data_source, 1);
2019 cln_debug_pub.Add('p_recipient_data_pool -- ' || p_recipient_data_pool, 1);
2020 cln_debug_pub.Add('p_classification_category -- ' || p_classification_category, 1);
2021 cln_debug_pub.Add('p_rfcin_unique_id -- ' || p_rfcin_unique_id, 1);
2022 cln_debug_pub.Add('p_rfcin_owner_gln -- ' || p_rfcin_owner_gln, 1);
2023 cln_debug_pub.Add('p_reload_flag -- ' || p_reload_flag, 1);
2024 cln_debug_pub.Add('p_rfcin_creation_date -- ' || p_rfcin_creation_date, 1);
2025 cln_debug_pub.Add('p_ucc_doc_unique_id -- ' || p_ucc_doc_unique_id, 1);
2026 cln_debug_pub.Add('p_xmlg_internal_control_no -- ' || p_xmlg_internal_control_no, 1);
2027 END IF;
2028
2029 IF (g_debug_Level <= 1) THEN
2033 cln_ch_collaboration_pkg.create_collaboration (
2030 cln_debug_pub.add('Calling Create Collaboration API',1);
2031 END IF;
2032
2034 x_return_status => l_return_status,
2035 x_msg_data => l_msg_data,
2036 p_app_id => m4u_ucc_utils.c_resp_appl_id,
2037 p_ref_id => NULL,
2038 p_org_id => m4u_ucc_utils.g_org_id,
2039 p_rel_no => NULL,
2040 p_doc_no => p_rfcin_unique_id,
2041 p_doc_owner => NULL,
2042 p_xmlg_int_transaction_type => 'M4U',
2043 p_xmlg_int_transaction_subtype=> 'RESP_RFCIN',
2044 p_xmlg_transaction_type => 'M4U',
2045 p_xmlg_transaction_subtype => 'RESP_RFCIN',
2046 p_xmlg_document_id => p_ucc_doc_unique_id,
2047 p_coll_type => 'M4U_RFCIN',
2048 p_tr_partner_type => m4u_ucc_utils.c_party_type,
2049 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
2050 p_doc_creation_date => sysdate,
2051 p_doc_revision_date => sysdate,
2052 p_init_date => sysdate,
2053 p_doc_type => 'M4U_RESP_RFCIN',
2054 p_doc_dir => 'IN',
2055 p_coll_pt => 'XML_GATEWAY',
2056 p_xmlg_msg_id => NULL,
2057 p_rosettanet_check_required => FALSE,
2058 x_coll_id => l_coll_id,
2059 p_msg_text => 'M4U_RFCIN_RECEIVED',
2060 p_xml_event_key => NULL,
2061 p_xmlg_internal_control_number=> p_xmlg_internal_control_no,
2062 p_attribute1 => p_recipient_data_pool,
2063 p_attribute2 => p_target_market_country,
2064 p_attribute5 => m4u_ucc_utils.g_supp_gln,
2065 p_attribute6 => p_rfcin_owner_gln,
2066 p_attribute8 => p_ucc_doc_unique_id,
2067 p_attribute9 => p_gtin,
2068 p_attribute10 => p_rfcin_unique_id,
2069 p_attribute12 => p_wlq_message_id,
2070 p_attribute13 => p_target_market_subdivision,
2071 p_attribute14 => p_reload_flag,
2072 p_attribute15 => p_classification_category,
2073 p_partner_doc_no => NULL,
2074 p_collaboration_standard => 'UCCNET'
2075 );
2076
2077 IF (g_debug_Level <= 1) THEN
2078 cln_debug_pub.add('create Collaboration returned ---',1);
2079 cln_debug_pub.add('Collab_detail_id - ' || l_coll_id,1);
2080 cln_debug_pub.add('x_return_status - ' || l_return_status,1);
2081 cln_debug_pub.add('x_msg_data - ' || l_msg_data,1);
2082 END IF;
2083
2084 x_return_status := l_return_status;
2085 x_msg_data := l_msg_data;
2086
2087 IF(l_return_status <> 'S') THEN
2088 RETURN;
2089 IF (g_debug_Level <= 2) THEN
2090 cln_debug_pub.add('Exiting process_rfcin_notification - ' || l_return_status,2);
2091 cln_debug_pub.add('===========================================',2);
2092 END IF;
2093 END IF;
2094
2095 l_notif_mesg := 'Request For CIN, received on with following details';
2096
2097 l_event_params := wf_parameter_list_t();
2098 wf_event.AddParameterToList(
2099 p_name => 'COLLABORATION_ID',
2100 p_value => l_coll_id,
2101 p_parameterlist => l_event_params );
2102
2103 wf_event.AddParameterToList(
2104 p_name => 'COLLABORATION_POINT',
2105 p_value => 'APPS',
2106 p_parameterlist => l_event_params );
2107
2108 wf_event.AddParameterToList(
2109 p_name => 'APPLICATION_ID',
2110 p_value => m4u_ucc_utils.c_resp_appl_id,
2111 p_parameterlist => l_event_params );
2112
2113 wf_event.AddParameterToList(
2117
2114 p_name => 'NOTIFICATION_CODE',
2115 p_value => 'M4URFCIN',
2116 p_parameterlist => l_event_params );
2118 wf_event.AddParameterToList(
2119 p_name => 'NOTIFICATION_STATUS',
2120 p_value => 'SUCCESS',
2121 p_parameterlist => l_event_params );
2122
2123 wf_event.AddParameterToList(
2124 p_name => 'NOTIFICATION_DESC',
2125 p_value => l_notif_mesg,
2126 p_parameterlist => l_event_params );
2127
2128
2129 l_event_key := 'RESP_RFCIN_' || l_coll_id;
2130
2131 IF (g_debug_Level <= 1) THEN
2132 cln_debug_pub.Add('-- CLN notification raised -- event_key ' || l_event_key, 1);
2133 END IF;
2134
2135 -- raise event to trigger outbound wlq generation WF
2136 wf_event.raise(
2137 p_event_name => l_event_name,
2138 p_event_key => l_event_key,
2139 p_parameters => l_event_params
2140 );
2141
2142 IF (g_debug_Level <= 2) THEN
2143 cln_debug_pub.Add('-- Exiting m4u_resp_process.process_rfcin_notification success-- ', 2);
2144 END IF;
2145
2146
2147
2148 RETURN;
2149 EXCEPTION
2150 WHEN OTHERS THEN
2151
2152 -- log exception and exit.
2153 l_error_code := SQLCODE;
2154 l_error_msg := SQLERRM;
2155 x_return_status := 'F';
2156 x_msg_data := ' - Unexpected error in m4u_resp_process.process_rfcin_notification' || l_error_code || ':' || l_error_msg;
2157
2158
2159 IF (g_debug_Level <= 5) THEN
2160 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
2161 END IF;
2162
2163 IF (g_debug_Level <= 2) THEN
2164 cln_debug_pub.add('Exiting process_rfcin_notification - exception',2);
2165 cln_debug_pub.add('==============================================',2);
2166 END IF;
2167 END;
2168
2169
2170 BEGIN
2171 g_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
2172 END m4u_resp_process;