[Home] [Help]
PACKAGE BODY: APPS.M4U_PARTY_QUERY
Source
1 PACKAGE BODY m4u_party_query AS
2 /*$Header: M4UQRPTB.pls 120.2 2005/06/14 12:03:44 appldev $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'm4u_global_regqry';
5 l_debug_level NUMBER;
6
7
8 -- Name
9 -- parse_param_list
10 -- Purpose
11 -- This procedure is called from the map m4u_230_party_qry_out.xgm
12 -- The purpose of this procedure is to parse the parameter list
13 -- supplied as input to each individual parameter.
14 -- This is used becuase multiple optional parameters to the XGM are passed
15 -- as a single Delimitor Separated Value list, since the ECXSTD/GETTPXML
16 -- activity used in the workflow does allows us to specify only 5 paramters
17 -- to the XGM
18 -- Arguments
19 -- p_param_list - List of delimitor separated value
20 -- x_org_gln - Organization_GLN param filter to be used in Query Command
21 -- x_duns_code - Duns code parameter filter
22 -- x_post_code - Postal code parameter filter
23 -- x_city - City parameter filter
24 -- x_return_status - return status 'S' on success else 'F'
25 -- x_msg_data - Failure message to be sent back to sysadmin
26 -- Notes
27 -- None.
28 PROCEDURE parse_param_list(
29 p_param_list IN VARCHAR2,
30 x_org_gln OUT NOCOPY VARCHAR2,
31 x_duns_code OUT NOCOPY VARCHAR2,
32 x_org_name OUT NOCOPY VARCHAR2,
33 x_post_code OUT NOCOPY VARCHAR2,
34 x_city OUT NOCOPY VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_data OUT NOCOPY VARCHAR2 )
37 IS
38 l_pos NUMBER;
39 r_pos NUMBER;
40 BEGIN
41
42 IF (l_debug_Level <= 2) THEN
43 cln_debug_pub.Add('=========================================', 2);
44 cln_debug_pub.Add('Entering m4u_party_query.parse_param_list', 2);
45 END IF;
46
47 IF (l_debug_Level <= 1) THEN
48 cln_debug_pub.Add('p_param_list - ' || p_param_list, 1);
49 END IF;
50
51 l_pos := 1;
52 r_pos := INSTR(p_param_list,'$',1,1);
53 x_org_gln := SUBSTR(p_param_list,l_pos,r_pos-l_pos);
54 l_pos := r_pos+1;
55
56 IF x_org_gln IS NULL THEN
57 x_org_gln := '%';
58 END IF;
59
60 r_pos := INSTR(p_param_list,'$',1,2);
61 x_org_name := SUBSTR(p_param_list,l_pos,r_pos-l_pos);
62 l_pos := r_pos+1;
63 IF x_org_name IS NULL THEN
64 x_org_name := '%';
65 END IF;
66
67 r_pos := INSTR(p_param_list,'$',1,3);
68 x_duns_code := SUBSTR(p_param_list,l_pos,r_pos-l_pos);
69 l_pos := r_pos+1;
70 IF x_duns_code IS NULL THEN
71 x_duns_code := '%';
72 END IF;
73
74
75 r_pos := INSTR(p_param_list,'$',1,4);
76 x_post_code := SUBSTR(p_param_list,l_pos,r_pos-l_pos);
77 l_pos := r_pos+1;
78 IF x_post_code IS NULL THEN
79 x_post_code := '%';
80 END IF;
81
82 r_pos := INSTR(p_param_list,'$',1,5);
83 x_city := SUBSTR(p_param_list,l_pos,r_pos-l_pos);
84 l_pos := r_pos+1;
85 IF x_city IS NULL THEN
86 x_city := '%';
87 END IF;
88
89
90 x_return_status := FND_API.G_RET_STS_SUCCESS;
91 x_msg_data := '';
92
93 IF (l_debug_Level <= 1) THEN
94 cln_debug_pub.Add('x_org_gln - ' || x_org_gln, 1);
95 cln_debug_pub.Add('x_duns_code - ' || x_duns_code, 1);
96 cln_debug_pub.Add('x_org_name - ' || x_org_name, 1);
97 cln_debug_pub.Add('x_post_code - ' || x_post_code, 1);
98 cln_debug_pub.Add('x_city - ' || x_city, 1);
99 cln_debug_pub.Add('x_return_status - ' || x_return_status, 1);
100 cln_debug_pub.Add('x_msg_data - ' || x_msg_data, 1);
101 END IF;
102
103
104
105 IF (l_debug_Level <= 2) THEN
106 cln_debug_pub.Add('Exiting m4u_party_query.parse_param_list - normal', 2);
107 cln_debug_pub.Add('=================================================', 2);
108 END IF;
109
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 x_msg_data :=' - Unexpected error in m4u_party_query/parse_param_list -'|| SQLCODE ||' : '||SQLERRM;
115
116 END parse_param_list;
117
118
119 -- Name
120 -- raise_partyqry_event
121 -- Purpose
122 -- This procedure is called from a concurrent program.
123 -- The 'oracle.apps.m4u.partyqry.generate' is raised with the supplied event parameters
124 -- The sequence m4u_wlqid_sequence is used to obtain unique event key.
125 -- Arguments
126 -- Notes
127 --
128 PROCEDURE raise_partyqry_event(
129 x_errbuf OUT NOCOPY VARCHAR2,
130 x_retcode OUT NOCOPY NUMBER,
131 p_tp_gln IN VARCHAR2,
132 p_org_gln IN VARCHAR2,
133 p_org_name IN VARCHAR2,
134 p_duns IN VARCHAR2,
135 p_postal_code IN VARCHAR2,
136 p_city IN VARCHAR2,
137 p_org_status IN VARCHAR2,
138 p_msg_count IN NUMBER
139 )
140 IS
141 l_event_parameters wf_parameter_list_t;
142 l_org_status VARCHAR2(10);
143 l_msg_data VARCHAR2(50);
144 l_event_key VARCHAR2(50);
145 l_error_code VARCHAR2(50);
146 l_ret_status VARCHAR2(50);
147 l_ptyqry_arg VARCHAR2(200);
148 l_error_msg VARCHAR2(500);
149 l_msg_count NUMBER;
150 BEGIN
151
152 IF (l_debug_Level <= 2) THEN
153 cln_debug_pub.Add('M4U:----- Entering raise_partyqry_event ------- ',2);
154 END IF;
155
156 -- Parameters received
157 IF (l_Debug_Level <= 1) THEN
158 cln_debug_pub.Add('++++ PARAMETERS RECEIVED FROM CONCURRENT PROGRAM ++ ',1);
159 cln_debug_pub.Add('p_tp_gln - '||p_tp_gln ,1);
160 cln_debug_pub.Add('p_org_gln - '||p_org_gln,1);
161 cln_debug_pub.Add('p_org_name - '||p_org_name,1);
162 cln_debug_pub.Add('p_duns - '||p_duns ,1);
163 cln_debug_pub.Add('p_postal_code - '||p_postal_code,1);
164 cln_debug_pub.Add('p_city - '||p_city,1);
165 cln_debug_pub.Add('p_org_status - '||p_org_status,1);
166 cln_debug_pub.Add('p_msg_count - '||p_msg_count,1);
167 cln_debug_pub.Add('+++++++++++++++++++++++++++++++++++++++++++++++++++',1);
168 END IF;
169
170 l_msg_count := p_msg_count;
171 -- Default the count to 10 if invalid value is input
172 /*
173 IF ((p_msg_count IS NULL) OR (p_msg_Count < 1)) AND (p_org_status IS NULL) THEN
174 l_msg_count := 10;
175 END IF;
176 */
177
178 -- 1. Validation of the GLN
179 IF NOT m4u_ucc_utils.validate_uccnet_attr
180 (
181 x_return_status => l_ret_status,
182 x_msg_data => l_msg_data,
183 p_attr_type => 'GLN',
184 p_attr_value => p_tp_gln
185 )THEN
186 IF (l_Debug_Level <= 5) THEN
187 cln_debug_pub.Add('Trading Partner '||l_msg_data,4);
188 END IF;
189
190 RAISE FND_API.G_EXC_ERROR;
191 END IF;
192
193
194 -- Org gln should either contain a Wildcard/have valid GLN as parameter
195 IF NOT(INSTR(p_org_gln,'%')>0 OR p_org_gln IS NULL) THEN
196 IF NOT m4u_ucc_utils.validate_uccnet_attr
197 (
198 x_return_status => l_ret_status,
199 x_msg_data => l_msg_data,
200 p_attr_type => 'GLN',
201 p_attr_value => p_org_gln
202 )THEN
203 IF (l_Debug_Level <= 5) THEN
204 cln_debug_pub.Add('Organization '||l_msg_data,4);
205 END IF;
206
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209 END IF;
210
211 -- Set the value for the status
212 IF (p_org_status IS NOT NULL) THEN
213 IF (p_org_status = 'Active') THEN
214 l_org_status := 'AC';
215 ELSE
216 l_org_status := 'IN';
217 END IF;
218 END IF;
219
220 IF (l_Debug_Level <= 1) THEN
221 cln_debug_pub.Add('Final p_org_status value - '||l_org_status,1);
222 END IF;
223
224 -- obtain a unique event-id
225 SELECT m4u_wlqid_s.NEXTVAL
226 INTO l_event_key
227 FROM dual;
228
229 l_event_key := 'M4U_QRYPT_' || l_event_key;
230
231 -- pass Concurrent program parameters as event parameters,
232 -- to be used in outboud xml generation map .
233
234 -- passing all optional paramters in a single event attributes as a Delimiter Separated Value string
235 l_ptyqry_arg := p_org_gln||'$'||p_org_name||'$'||p_duns||'$'||p_postal_code||'$'||p_city||'$';
236
237 l_event_parameters := wf_parameter_list_t();
238
239 wf_event.AddParameterToList(
240 p_name => 'ECX_EVENT_KEY',
241 p_value => l_event_key,
242 p_parameterlist => l_event_parameters );
243 IF (l_Debug_Level <= 1) THEN
244 cln_debug_pub.Add('ECX_EVENT_KEY - '||l_event_key,1);
245 END IF;
246
247 wf_event.AddParameterToList(
251 IF (l_Debug_Level <= 1) THEN
248 p_name => 'ECX_PARAMETER2',
249 p_value => l_msg_count,
250 p_parameterlist => l_event_parameters );
252 cln_debug_pub.Add('ECX_PARAMETER2 - Message Count - '||p_msg_count,1);
253 END IF;
254
255
256 wf_event.AddParameterToList(
257 p_name => 'ECX_PARAMETER3',
258 p_value => p_tp_gln,
259 p_parameterlist => l_event_parameters );
260 IF (l_Debug_Level <= 1) THEN
261 cln_debug_pub.Add('ECX_PARAMETER3 - TP GLN - '||p_tp_gln,1);
262 END IF;
263
264 wf_event.AddParameterToList(
265 p_name => 'ECX_PARAMETER5',
266 p_value => l_ptyqry_arg,
267 p_parameterlist => l_event_parameters );
268 IF (l_Debug_Level <= 1) THEN
269 cln_debug_pub.Add('ECX_PARAMETER5 - Optional Parameters - '|| l_ptyqry_arg,1);
270 END IF;
271
272 wf_event.AddParameterToList(
273 p_name => 'ECX_TRANSACTION_TYPE',
274 p_value => 'M4U',
275 p_parameterlist => l_event_parameters );
276 IF (l_Debug_Level <= 1) THEN
277 cln_debug_pub.Add('ECX_TRANSACTION_TYPE - M4U',1);
278 END IF;
279
280 wf_event.AddParameterToList(
281 p_name => 'ECX_TRANSACTION_SUBTYPE',
282 p_value => 'PARTYQRY',
283 p_parameterlist => l_event_parameters );
284 IF (l_Debug_Level <= 1) THEN
285 cln_debug_pub.Add('ECX_TRANSACTION_SUBTYPE - PARTYQRY',1);
286 END IF;
287
288 wf_event.AddParameterToList(
289 p_name => 'ECX_DOCUMENT_ID',
290 p_value => l_event_key,
291 p_parameterlist => l_event_parameters );
292 IF (l_Debug_Level <= 1) THEN
293 cln_debug_pub.Add('ECX_DOCUMENT_ID - '||l_event_key,1);
294 END IF;
295
296
297 wf_event.AddParameterToList(
298 p_name => 'M4U_CLN_COLL_TYPE',
299 p_value => 'M4U_PARTY_QUERY',
300 p_parameterlist => l_event_parameters );
301 IF (l_Debug_Level <= 1) THEN
302 cln_debug_pub.Add('M4U_CLN_COLL_TYPE - M4U_PARTY_QUERY',1);
303 END IF;
304
305 wf_event.AddParameterToList(
306 p_name => 'M4U_CLN_DOC_TYPE',
307 p_value => 'M4U_PARTY_QUERY',
308 p_parameterlist => l_event_parameters );
309 IF (l_Debug_Level <= 1) THEN
310 cln_debug_pub.Add('M4U_CLN_DOC_TYPE - M4U_PARTY_QUERY',1);
311 END IF;
312
313 wf_event.AddParameterToList(
314 p_name => 'M4U_DOC_NO',
315 p_value => l_event_key,
316 p_parameterlist => l_event_parameters );
317 IF (l_Debug_Level <= 1) THEN
318 cln_debug_pub.Add('M4U_DOC_NO - '||l_event_key,1);
319 END IF;
320
321 wf_event.AddParameterToList(
322 p_name => 'ATTRIBUTE1',
323 p_value => m4u_ucc_utils.g_host_gln,
324 p_parameterlist => l_event_parameters );
325 IF (l_Debug_Level <= 1) THEN
326 cln_debug_pub.Add('ATTRIBUTE1 - '||m4u_ucc_utils.g_host_gln,1);
327 END IF;
328
329 wf_event.AddParameterToList(
330 p_name => 'ATTRIBUTE5',
331 p_value => m4u_ucc_utils.g_supp_gln,
332 p_parameterlist => l_event_parameters );
333 IF (l_Debug_Level <= 1) THEN
334 cln_debug_pub.Add('ATTRIBUTE5 - '||m4u_ucc_utils.g_supp_gln,1);
335 END IF;
336
337
338 wf_event.AddParameterToList(
339 p_name => 'ATTRIBUTE12',
340 p_value => l_event_key,
341 p_parameterlist => l_event_parameters );
342 IF (l_Debug_Level <= 1) THEN
343 cln_debug_pub.Add('ATTRIBUTE12 - '||l_event_key,1);
344 END IF;
345
346
347 wf_event.AddParameterToList(
348 p_name => 'ATTRIBUTE14',
349 p_value => l_org_status,
350 p_parameterlist => l_event_parameters );
351 IF (l_Debug_Level <= 1) THEN
355
352 cln_debug_pub.Add('ATTRIBUTE14 - '||l_org_status,1);
353 END IF;
354
356 -- set event parameters, end
357
358 IF (l_Debug_Level <= 1) THEN
359 cln_debug_pub.Add('-- Raising Event oracle.apps.m4u.outboundwf.generate -- ',1);
360 cln_debug_pub.Add('p_event_key - '||l_event_key ,1);
361 END IF;
362
363
364 -- raise event to trigger outbound wlq generation WF
365 wf_event.raise(
366 p_event_name => 'oracle.apps.m4u.outboundwf.generate',
367 p_event_key => l_event_key,
368 p_parameters => l_event_parameters
369 );
370
371 x_retcode := 0;
372 x_errbuf := 'Successful';
373
374
375 IF (l_Debug_Level <= 1) THEN
376 cln_debug_pub.Add('Event oracle.apps.m4u.outboundwf.generate raised',1);
377 END IF;
378
379 IF (l_Debug_Level <= 2) THEN
380 cln_debug_pub.Add('------- Exiting raise_partyqry_event API/normal --------- ',2);
381 END IF;
382
383
384 -- Exception Handling
385 EXCEPTION
386 WHEN FND_API.G_EXC_ERROR THEN
387 x_retcode := 2 ;
388 x_errbuf := l_msg_data;
389
390 IF (l_Debug_Level <= 5) THEN
391 cln_debug_pub.Add('Details: '||l_msg_data,4);
392 cln_debug_pub.Add('------- Exiting raise_partyqry_event API :Error--------- ',2);
393 END IF;
394
395 WHEN OTHERS THEN
396 x_retcode := 2 ;
397 x_errbuf := l_msg_data;
398
399 l_error_code :=SQLCODE;
400 l_error_msg :=SQLERRM;
401 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
402 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
403 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
404
405
406
407 IF (l_Debug_Level <= 5) THEN
408 cln_debug_pub.Add(x_errbuf, 5);
409 cln_debug_pub.Add('Details - '||FND_MESSAGE.GET , 5);
410 cln_debug_pub.Add('------- Exiting raise_partyqry_event API :Other Error --------- ',2);
411 END IF;
412
413 END raise_partyqry_event;
414
415 -- Name
416 -- process_resp_org_data
417 -- Purpose
418 -- This procedure is called from the XGM m4u_230_resp_partyqry
419 -- This procedure recieves the organization info parsed from
420 -- the UCCnet party-query command response
421 -- Arguments
422 -- p_org_gln - Party info received in response
423 -- p_org_name - Party info received in response
424 -- p_short_name - Party info received in response
425 -- p_org_type - Party info received in response
426 -- p_contact - Party info received in response
427 -- p_org_status - Party info received in response
428 -- p_role - Party info received in response
429 -- p_addr1 - Party info received in response
430 -- p_addr2 - Party info received in response
431 -- p_city - Party info received in response
432 -- p_state - Party info received in response
433 -- p_zip - Party info received in response
434 -- p_country - Party info received in response
435 -- p_phone - Party info received in response
436 -- p_fax - Party info received in response
437 -- p_email - Party info received in response
438 -- p_party_query_id - Id of party command
439 -- p_ucc_doc_unique_id - UCCnet generated unique doc-id
440 -- p_xmlg_internal_control_no - retrieved from map, for logging
441 -- x_collab_detail_id - returned from update collab call
442 -- x_return_status - flag indicating success/failure of api call
443 -- x_msg_data - exception messages if any
444 -- Notes
445 -- None.
446 PROCEDURE process_resp_org_data(
447 p_org_gln IN VARCHAR2,
448 p_org_name IN VARCHAR2,
449 p_short_name IN VARCHAR2,
450 p_org_type IN VARCHAR2,
451 p_contact IN VARCHAR2,
452 p_org_status IN VARCHAR2,
453 p_role IN VARCHAR2,
454 p_addr1 IN VARCHAR2,
455 p_addr2 IN VARCHAR2,
456 p_city IN VARCHAR2,
457 p_state IN VARCHAR2,
458 p_zip IN VARCHAR2,
459 p_country_code IN VARCHAR2,
460 p_phone IN VARCHAR2,
461 p_fax IN VARCHAR2,
465 p_ucc_doc_unique_id IN VARCHAR2,
462 p_email IN VARCHAR2,
463 p_party_links IN VARCHAR2,
464 p_party_query_id IN VARCHAR2,
466 p_xmlg_internal_control_no IN NUMBER,
467 p_collab_dtl_id IN VARCHAR2,
468 x_return_status OUT NOCOPY VARCHAR2,
469 x_msg_data OUT NOCOPY VARCHAR2 )
470 IS
471
472 l_org_status_db VARCHAR2(10);
473 l_error_code VARCHAR2(50);
474 l_event_name VARCHAR2(100);
475 l_event_key VARCHAR2(255);
476 l_error_msg VARCHAR2(1000);
477 l_dtl_msg VARCHAR2(2000);
478 l_event_parameters wf_parameter_list_t;
479
480 BEGIN
481 l_event_name := 'oracle.apps.cln.ch.collaboration.addmessage';
482 l_event_parameters := wf_parameter_list_t();
483
484 IF (l_Debug_Level <= 2) THEN
485 cln_debug_pub.Add('Entering m4u_party_query.process_resp_org_data' ,1);
486 END IF;
487
488 -- Parameters received
489 IF (l_Debug_Level <= 1) THEN
490 cln_debug_pub.Add('---- m4u_party_query.process_resp_org_data -- ' ,1);
491 cln_debug_pub.Add('p_org_gln - '||p_org_gln ,1);
492 cln_debug_pub.Add('p_org_name - '||p_org_name ,1);
493 cln_debug_pub.Add('p_short_name - '||p_short_name ,1);
494 cln_debug_pub.Add('p_org_type - '||p_org_type ,1);
495 cln_debug_pub.Add('p_contact - '||p_contact ,1);
496 cln_debug_pub.Add('p_org_status - '||p_org_status ,1);
497 cln_debug_pub.Add('p_role - '||p_role ,1);
498 cln_debug_pub.Add('p_addr1 - '||p_addr1 ,1);
499 cln_debug_pub.Add('p_addr2 - '||p_addr2 ,1);
500 cln_debug_pub.Add('p_city - '||p_city ,1);
501 cln_debug_pub.Add('p_state - '||p_state ,1);
502 cln_debug_pub.Add('p_zip - '||p_zip ,1);
503 cln_debug_pub.Add('p_country_code - '||p_country_code ,1);
504 cln_debug_pub.Add('p_phone - '||p_phone ,1);
505 cln_debug_pub.Add('p_fax - '||p_fax ,1);
506 cln_debug_pub.Add('p_email - '||p_email ,1);
507 cln_debug_pub.Add('p_party_links - '||p_party_links ,1);
508 cln_debug_pub.Add('p_party_query_id - '||p_party_query_id ,1);
509 cln_debug_pub.Add('p_ucc_doc_unique_id - '||p_ucc_doc_unique_id ,1);
510 cln_debug_pub.Add('p_xmlg_internal_control_no - '||p_xmlg_internal_control_no ,1);
511 cln_debug_pub.Add('p_collab_dtl_id - '||p_collab_dtl_id);
512 cln_debug_pub.Add('-------------------------------------------' ,1);
513 END IF;
514
515 -- Check for the collaboration ID based on the collaboration detail ID and get the
516 -- attribute 14 value. If the value is set and is AC, that means only active organizations
517 -- are desired in the display.
518
519 IF (l_Debug_Level <= 1) THEN
520 cln_debug_pub.Add('Checking for the value of attribute14 for org status' ,1);
521 END IF;
522
523 SELECT attribute14
524 INTO l_org_status_db
525 FROM CLN_COLL_HIST_HDR
526 WHERE collaboration_id = ( SELECT collaboration_id
527 FROM CLN_COLL_HIST_DTL
528 WHERE collaboration_dtl_id = p_collab_dtl_id);
529
530 IF (l_Debug_Level <= 1) THEN
531 cln_debug_pub.Add('Organization Status required '||l_org_status_db ,1);
532 END IF;
533
534 -- if the database value for status is AC and the incoming
535 -- payload carries organization details with status as IN, return
536 IF (l_org_status_db IS NOT NULL) AND (l_org_status_db <> p_org_status) THEN
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538 x_msg_data := 'SUCCESS';
539 RETURN;
540 END IF;
541
542 /* NO PROCESSING/UPDATE DEFINED */
543
544 --create unique key for each event using CLN sequence
545 SELECT cln_collaboration_msg_id_s.nextval INTO l_event_key FROM dual ;
546
547 l_dtl_msg := 'GLN - ' || p_org_gln || ', Name - ' || p_org_name || ', Type - ' || p_org_type;
548 l_dtl_msg := l_dtl_msg || ', Contact - ' || p_contact || ' , Status - ' || p_org_status || ', Role - ' || p_role;
549 l_dtl_msg := l_dtl_msg || ', Address - ' || p_addr1 || ', ' || p_addr2 || ', '|| p_city || ', ' || p_state || ', ' || p_zip || ', ' || p_country_code;
553
550 l_dtl_msg := l_dtl_msg || ', Phone - ' || p_phone || ', Fax - ' || p_fax ;
551
552 --dbms_output.put_line(l_dtl_msg);
554 -- set event parameters for CLN Add Message Event
555 wf_event.AddParameterToList(
556 p_name => 'COLLABORATION_DETAIL_ID',
557 p_value => p_collab_dtl_id,
558 p_parameterlist => l_event_parameters );
559
560 wf_event.AddParameterToList(
561 p_name => 'DETAIL_MESSAGE',
562 p_value => l_dtl_msg,
563 p_parameterlist => l_event_parameters );
564
565
566 wf_event.AddParameterToList(
567 p_name => 'REFERENCE_ID1',
568 p_value => 'GLN - ' || p_org_gln,
569 p_parameterlist => l_event_parameters );
570
571 -- add CLN event to add error messages as to collaboration history
572 wf_event.raise(
573 p_event_name => l_event_name,
574 p_event_key => l_event_key,
575 p_parameters => l_event_parameters
576 );
577
578 IF (l_Debug_Level <= 2) THEN
579 cln_debug_pub.Add('Raised - ' || l_event_name || ', key - ' || l_event_key,2);
580 END IF;
581
582 x_return_status := FND_API.G_RET_STS_SUCCESS;
583 x_msg_data := 'SUCCESS';
584
585 IF (l_Debug_Level <= 2) THEN
586 cln_debug_pub.Add('Exiting m4u_party_query.process_resp_org_data - Normal' ,1);
587 END IF;
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 l_error_code := SQLCODE;
592 l_error_msg := SQLERRM;
593 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
594
595 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
596 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
597 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
598
599 x_msg_data := ' ' || FND_MESSAGE.GET || ' , ';
600
601 IF (l_debug_level <= 5) THEN
602 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
603 END IF;
604
605 IF (l_debug_level <= 2) THEN
606 cln_debug_pub.add('Exiting m4u_party_query.process_resp_org_data - exception',2);
607 cln_debug_pub.add('=========================================================',2);
608 END IF;
609
610 END process_resp_org_data;
611
612
613 -- Name
614 -- process_party_links
615 -- Purpose
616 -- This procedure is called from the XGM m4u_230_resp_partyqry_in
617 -- This procedure recieves the organization-links info parsed from
618 -- the UCCnet party-query command response
619 -- Arguments
620 -- p_org_gln - Party info received in response
621 -- p_linked_gln - GLN of Org linked to p_org_gln
622 -- p_party_query_id - Id of party command
623 -- p_ucc_doc_unique_id - UCCnet generated unique doc-id
624 -- p_xmlg_internal_control_no - retrieved from map, for logging
625 -- x_collab_detail_id - returned from update collab call
626 -- x_return_status - flag indicating success/failure of api call
627 -- x_msg_data - exception messages if any
628 -- Notes
629 -- This is only a Dummy API, can be extended based on future requirements
630 PROCEDURE process_party_links(
631 p_org_gln IN VARCHAR2,
632 p_linked_gln IN VARCHAR2,
633 p_party_query_id IN VARCHAR2,
634 p_collab_id IN VARCHAR2,
635 p_ucc_doc_unique_id IN VARCHAR2,
636 p_xmlg_internal_control_no IN NUMBER,
637 x_collab_detail_id OUT NOCOPY VARCHAR2,
638 x_return_status OUT NOCOPY VARCHAR2,
639 x_msg_data OUT NOCOPY VARCHAR2 )
640 IS
641 l_error_code VARCHAR2(50);
642 l_error_msg VARCHAR2(1000);
643 BEGIN
644
645 IF (l_Debug_Level <= 2) THEN
646 cln_debug_pub.Add('Entering m4u_party_query.process_party_links' ,1);
647 END IF;
648
649
650
651 -- Parameters received
652 IF (l_Debug_Level <= 1) THEN
653 cln_debug_pub.Add('---- m4u_party_query.proces_party_links-- ' ,1);
654 cln_debug_pub.Add('p_org_gln - '||p_org_gln ,1);
655 cln_debug_pub.Add('p_linked_gln - '||p_linked_gln ,1);
656 cln_debug_pub.Add('p_party_query_id - '||p_party_query_id ,1);
657 cln_debug_pub.Add('p_collab_id - '||p_collab_id ,1);
658 cln_debug_pub.Add('p_ucc_doc_unique_id - '||p_ucc_doc_unique_id ,1);
662
659 cln_debug_pub.Add('p_xmlg_internal_control_no - '||p_xmlg_internal_control_no ,1);
660 cln_debug_pub.Add('-------------------------------------------' ,1);
661 END IF;
663 /* NO PROCESSING/UPDATE DEFINED */
664 /* IN-FUTURE THIS CAN BE EXTENDED */
665
666 x_return_status := FND_API.G_RET_STS_SUCCESS;
667 x_msg_data := 'SUCCESS';
668
669 IF (l_Debug_Level <= 2) THEN
670 cln_debug_pub.Add('Exiting m4u_party_query.process_party_links - Normal' ,1);
671 END IF;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 l_error_code := SQLCODE;
676 l_error_msg := SQLERRM;
677 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
678
679 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
680 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
681 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
682
683 x_msg_data := ' ' || FND_MESSAGE.GET || ' , ';
684
685
686 IF (l_debug_level <= 5) THEN
687 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
688 END IF;
689
690 IF (l_debug_level <= 2) THEN
691 cln_debug_pub.add('Exiting m4u_party_query.process_party_links - exception',2);
692 cln_debug_pub.add('=======================================================',2);
693 END IF;
694
695 END process_party_links;
696
697
698
699 -- Name
700 -- process_resp_doc
701 -- Purpose
702 -- This procedure is called from the XGM m4u_230_resp_partyqry_in
703 -- This procedure updateds the UCCnet Party Query collaboration
704 -- with details of the response document, and failre messages in case of error.
705 -- Arguments
706 -- p_party_query_id - Id of party query command
707 -- p_query_success_flag - Flag - if query command was sucess/failure
708 -- p_ucc_doc_unique_id - UCCnet generated unique doc-id
709 -- p_xmlg_internal_control_no - retrieved from map, for logging
710 -- x_collab_detail_id - returned from update collab call
711 -- x_return_status - flag indicating success/failure of api call
712 -- x_msg_data - exception messages if any
713 -- Notes
714 -- None.
715 PROCEDURE process_resp_doc(
716 p_party_query_id IN VARCHAR2,
717 p_query_success_flag IN VARCHAR2,
718 p_ucc_doc_unique_id IN VARCHAR2,
719 p_xmlg_internal_control_no IN NUMBER,
720 p_doc_status IN VARCHAR2,
721 x_collab_detail_id OUT NOCOPY VARCHAR2,
722 x_return_status OUT NOCOPY VARCHAR2,
723 x_msg_data OUT NOCOPY VARCHAR2 )
724 IS
725 l_error_code VARCHAR2(50);
726 l_error_msg VARCHAR2(1000);
727 l_disposition VARCHAR2(50);
728 l_coll_status VARCHAR2(50);
729 l_fnd_msg VARCHAR2(1000);
730 l_doc_status VARCHAR2(50);
731 BEGIN
732
733 -- Parameters received
734 IF (l_Debug_Level <= 1) THEN
735 cln_debug_pub.Add('---- m4u_party_query.proces_resp_doc ---- ' ,1);
736 cln_debug_pub.Add('p_party_query_id - '||p_party_query_id ,1);
737 cln_debug_pub.Add('p_query_success_flag - '||p_query_success_flag ,1);
738 cln_debug_pub.Add('p_ucc_doc_unique_id - '||p_ucc_doc_unique_id ,1);
739 cln_debug_pub.Add('p_xmlg_internal_control_no - '||p_xmlg_internal_control_no ,1);
740 cln_debug_pub.Add('p_doc_status - '||p_doc_status ,1);
741 cln_debug_pub.Add('-------------------------------------------' ,1);
742 END IF;
743
744
745 -- set CLN Disposition, CLN Coll Status, CLN Doc status, CLN Message Text
746 IF (UPPER(p_query_success_flag) = 'TRUE') THEN
747 l_disposition := 'ACCEPTED' ;
748 l_coll_status := 'COMPLETED';
749 l_doc_status := 'SUCCESS';
750 l_fnd_msg := 'M4U_PARTYQRY_SUCCESS';
751
752 IF (p_doc_status IS NULL)THEN
753 l_fnd_msg := 'M4U_PARTYQRY_WRONG_CHOICE';
754 END IF;
755 ELSE
756 l_disposition := 'FAILED' ;
757 l_coll_status := 'ERROR';
758 l_doc_status := 'ERROR';
759 l_fnd_msg := 'M4U_PARTYQRY_FAILURE';
760 END IF;
761
762
763
764 IF (l_Debug_Level <= 1) THEN
765 cln_debug_pub.Add('-- Update collaboration parameters set -',1);
766 cln_debug_pub.Add('Disposition - '|| l_disposition ,1);
767 cln_debug_pub.Add('Collaboration Status - '|| l_coll_status ,1);
768 cln_debug_pub.Add('Document Status - '|| l_doc_status ,1);
772
769 cln_debug_pub.Add('FND Message - '|| l_fnd_msg ,1);
770 cln_debug_pub.Add('---------------------------------------' ,1);
771 END IF;
773
774
775 cln_ch_collaboration_pkg.update_collaboration(
776 x_return_status => x_return_status,
777 x_msg_data => x_msg_data,
778 p_coll_id => p_party_query_id,
779 p_msg_text => l_fnd_msg,
780 p_xmlg_msg_id => NULL,
781 p_xmlg_transaction_type => 'M4U',
782 p_xmlg_transaction_subtype => 'RESP_PARTY',
783 p_xmlg_int_transaction_type => 'M4U',
784 p_xmlg_int_transaction_subtype => 'RESP_PARTY',
785 p_doc_dir => 'IN',
786 p_doc_type => 'M4U_RESP_PARTY',
787 p_disposition => l_disposition,
788 p_doc_status => l_doc_status,
789 p_coll_status => l_coll_status,
790 p_xmlg_document_id => NULL,
791 p_xmlg_internal_control_number => p_xmlg_internal_control_no,
792 p_tr_partner_type => m4u_ucc_utils.c_party_type,
793 p_tr_partner_id => m4u_ucc_utils.g_party_id,
794 p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
795 p_attribute8 => p_ucc_doc_unique_id,
796 p_attribute9 => null,
797 p_dattribute1 => sysdate,
798 p_rosettanet_check_required => false,
799 x_dtl_coll_id => x_collab_detail_id );
800
801
802 IF (l_Debug_Level <= 1) THEN
803 cln_debug_pub.Add('-- Update Collab API returns Values -' ,1);
804 cln_debug_pub.Add('Collab Detail Id - '|| x_collab_detail_id ,1);
805 cln_debug_pub.Add('CLN API Return Status - '|| x_return_status ,1);
806 cln_debug_pub.Add('CLN return msg data - '|| x_msg_data ,1);
807 END IF;
808
809
810 IF (l_Debug_Level <= 2) THEN
811 cln_debug_pub.Add('Exiting - m4u_party_query.process_resp_doc Normal',2);
812 END IF;
813
814
815 EXCEPTION
816 WHEN OTHERS THEN
817 l_error_code := SQLCODE;
818 l_error_msg := SQLERRM;
819 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
820
821 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
822 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
823 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
824
825 x_msg_data := ' ' || FND_MESSAGE.GET || ' , ';
826
827
828 IF (l_debug_level <= 5) THEN
829 cln_debug_pub.add(l_error_code || ':' || l_error_msg, 4);
830 END IF;
831
832 IF (l_debug_level <= 2) THEN
833 cln_debug_pub.add('Exiting m4u_party_query.process_resp_doc - exception',2);
834 cln_debug_pub.add('====================================================',2);
835 END IF;
836
837 END process_resp_doc;
838
839 BEGIN
840 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
841 END m4u_party_query;