1 PACKAGE BODY CLN_CH_COLLABORATION_PKG AS
2 /* $Header: ECXCHCHB.pls 120.4 2006/06/30 07:48:51 susaha noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4
5 -- Package
6 -- CLN_CH_COLLABORATION_PKG
7 --
8 -- Purpose
9 -- Spec of package CLN_CH_COLLABORATION_PKG. This package
10 -- is called by both inbound and outbound operations from the application
11 -- when ever a new collaboration has to be started.Also,the details of any
12 -- existing collaboration can also be retrieved/updated by calling this package.
13 --
14 -- History
15 -- Mar-26-2002 Rahul Krishan Created
16 -- Apr-12-2002 Rahul Krishan Updated
17 -- Aug-26-2002 Rahul Krishan Updated
18
19 -- Name
20 -- GET_CHILD_ELEMENT_VALUE
21 -- Purpose
22 -- This procedure is called to retrieve value of child element in an xml
23 -- Arguments
24 -- parent element and child element name
25 -- Notes
26 -- Only called by get_document_creation_date
27
28 FUNCTION GET_CHILD_ELEMENT_VALUE(
29 l_element IN xmldom.domElement,
30 l_child_name IN VARCHAR2) RETURN VARCHAR2
31 IS
32 l_child_value VARCHAR2(100);
33 BEGIN
34 l_child_value := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( Xmldom.getElementsByTagName(l_element, l_child_name), 0 )));
35 RETURN l_child_value;
36 EXCEPTION
37 WHEN OTHERS THEN
38 IF (l_Debug_Level <= 1) THEN
39 ecx_cln_debug_pub.Add('When others in get child element' , 1);
40 END IF;
41 RETURN NULL;
42 END;
43
44
45
46
47 -- Name
48 -- GET_DOCUMENT_CREATION_DATE
49 -- Purpose
50 -- This procedure is called to retrieve document creation date based on
51 -- XML message ID from control area of the payload
52 -- Arguments
53 -- XML Gateway Message ID
54 -- Notes
55 -- Uses a DOM Parser to parse the document and retrieve the doc creation date
56
57 PROCEDURE GET_DOCUMENT_CREATION_DATE(
58 p_msgId IN RAW,
59 x_doc_creation_date IN OUT NOCOPY DATE)
60 IS
61 l_xmlDoc CLOB;
62 l_parser xmlparser.parser := xmlparser.newParser;
63 l_domDoc xmldom.DOMDocument;
64 l_node xmldom.domNode;
65 l_element xmldom.domElement;
66 l_nodeList xmldom.domNodeList;
67 l_size number;
68 l_Nname varchar2(255);
69 l_Nvalue varchar2(255);
70 l_error_code VARCHAR2(255);
71 l_error_msg VARCHAR2(1000);
72 l_msg_data VARCHAR2(1000);
73 l_payload CLOB;
74 l_ini_pos NUMBER(38);
75 l_fin_pos NUMBER(38);
76 l_amount INTEGER;
77 l_year VARCHAR2(10);
78 l_month VARCHAR2(10);
79 l_day VARCHAR2(10);
80 l_hour VARCHAR2(10);
81 l_minute VARCHAR2(10);
82 l_second VARCHAR2(10);
83 l_timezone_string VARCHAR2(10);
84 l_timezone_num VARCHAR2(10);
85
86 BEGIN
87 IF (l_Debug_Level <= 2) THEN
88 ecx_cln_debug_pub.Add('-----------ENTERING GET_DOCUMENT_CREATION_DATE-----------', 2);
89 END IF;
90
91 IF (l_Debug_Level <= 2) THEN
92 ecx_cln_debug_pub.Add('WITH PARAMETERS', 1);
93 ecx_cln_debug_pub.Add('p_msgId:' || p_msgId, 1);
94 END IF;
95
96 x_doc_creation_date := NULL;
97
98 SELECT payload into l_xmlDoc FROM ecx_doclogs WHERE msgid = HEXTORAW(p_msgId);
99
100 l_ini_pos := -1;
101 l_ini_pos := dbms_lob.instr(l_xmlDoc, '!DOCTYPE ');
102 IF (l_Debug_Level <= 2) THEN
103 ecx_cln_debug_pub.Add('Init Position:' || l_ini_pos, 1);
104 END IF;
105
106 IF (l_ini_pos > 0) THEN
107 l_fin_pos := dbms_lob.instr(l_xmlDoc, '>', l_ini_pos);
108 l_fin_pos := l_fin_pos + 1;
109 l_amount := dbms_lob.getlength(l_xmlDoc);
110
111 IF (l_Debug_Level <= 2) THEN
112 ecx_cln_debug_pub.Add('Final Position:' || l_fin_pos, 1);
113 ecx_cln_debug_pub.Add('Length:' || l_amount, 1);
114 END IF;
115
116 DBMS_LOB.CREATETEMPORARY(l_payload, TRUE, DBMS_LOB.SESSION);
117
118 dbms_lob.copy(l_payload, l_xmlDoc, l_amount - l_fin_pos + 1, 1, l_fin_pos);
119
120 END IF;
121
122 l_parser := xmlparser.newparser;
123 xmlparser.setValidationMode(l_parser,FALSE);
124 xmlparser.showWarnings(l_parser,FALSE);
125
126 BEGIN
127
128 IF (l_ini_pos > 0) THEN
129 xmlparser.parseClob(l_parser,l_payload);
130 ELSE
131 xmlparser.parseClob(l_parser,l_xmlDoc);
132 END IF;
133
134 l_domDoc := xmlparser.getDocument(l_parser);
135 l_nodeList := Xmldom.getElementsByTagName(l_domDoc, 'CNTROLAREA');
136 l_element := xmldom.makeElement(xmldom.item( l_nodeList, 0 ));
137 l_nodeList := Xmldom.getElementsByTagName(l_element, 'DATETIME');
138 l_element := xmldom.makeElement(xmldom.item( l_nodeList, 0 ));
139
140 IF (l_Debug_Level <= 1) THEN
141 ecx_cln_debug_pub.Add('About to get values of child elements of DATETIME', 1);
142 END IF;
143 l_year := GET_CHILD_ELEMENT_VALUE(l_element,'YEAR');
144 IF (l_Debug_Level <= 1) THEN
145 ecx_cln_debug_pub.Add('l_year : ' || l_year , 1);
146 END IF;
147 l_month := GET_CHILD_ELEMENT_VALUE(l_element,'MONTH');
148 IF (l_Debug_Level <= 1) THEN
149 ecx_cln_debug_pub.Add('l_month : ' || l_month , 1);
150 END IF;
151 l_day := GET_CHILD_ELEMENT_VALUE(l_element,'DAY');
152 IF (l_Debug_Level <= 1) THEN
153 ecx_cln_debug_pub.Add('l_day : ' || l_day , 1);
154 END IF;
155 l_hour := GET_CHILD_ELEMENT_VALUE(l_element,'HOUR');
156 IF (l_Debug_Level <= 1) THEN
157 ecx_cln_debug_pub.Add('l_hour : ' || l_hour , 1);
158 END IF;
159 l_minute := GET_CHILD_ELEMENT_VALUE(l_element,'MINUTE');
160 IF (l_Debug_Level <= 1) THEN
161 ecx_cln_debug_pub.Add('l_minute : ' || l_minute , 1);
162 END IF;
163 l_second := GET_CHILD_ELEMENT_VALUE(l_element,'SECOND');
164 IF (l_Debug_Level <= 1) THEN
165 ecx_cln_debug_pub.Add('l_second : ' || l_second , 1);
166 END IF;
167 x_doc_creation_date := to_date(l_year ||'-'||
168 l_month ||'-'||
169 l_day ||'-'||
170 l_hour ||'-'||
171 l_minute ||'-'||
172 l_second,
173 'yyyy-mm-dd-hh24-mi-ss');
174 IF (l_Debug_Level <= 1) THEN
175 ecx_cln_debug_pub.Add('Date before conversion : ' || to_char(x_doc_creation_date,'yyyy-mm-dd-hh24-mi-ss'), 1);
176 END IF;
177 l_timezone_string := GET_CHILD_ELEMENT_VALUE(l_element,'TIMEZONE');
178 IF (l_Debug_Level <= 1) THEN
179 ecx_cln_debug_pub.Add('l_timezone_string : ' || l_timezone_string , 1);
180 END IF;
181 l_timezone_num := to_number(l_timezone_string);
182 x_doc_creation_date := x_doc_creation_date - trunc(l_timezone_num/100)/24 - mod(l_timezone_num,100)/1440;
183 IF (l_Debug_Level <= 1) THEN
184 ecx_cln_debug_pub.Add('Date after conversion : ' || to_char(x_doc_creation_date,'yyyy-mm-dd-hh24-mi-ss'), 1);
185 ecx_cln_debug_pub.Add('Server Time Zone : ' || FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE, 1);
186 END IF;
187
188 x_doc_creation_date := fnd_timezones_pvt.adjust_datetime(x_doc_creation_date,'GMT',FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
189 IF (l_Debug_Level <= 1) THEN
190 ecx_cln_debug_pub.Add('Date after converting to server Time Zone : ' || to_char(x_doc_creation_date,'yyyy-mm-dd-hh24-mi-ss'), 1);
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 x_doc_creation_date := NULL;
195 END;
196
197 IF (l_ini_pos > 0) THEN
198 DBMS_LOB.FREETEMPORARY(l_payload);
199 END IF;
200 xmlparser.freeparser(l_parser);
201
202 IF (l_Debug_Level <= 5) THEN
203 ecx_cln_debug_pub.Add('Document Creation Date:' || x_doc_creation_date,1);
204 END IF;
205 IF (l_Debug_Level <= 2) THEN
206 ecx_cln_debug_pub.Add('EXITING GET_DOCUMENT_CREATION_DATE', 2);
207 END IF;
208 EXCEPTION
209 WHEN OTHERS THEN
210 l_error_code := SQLCODE;
211 l_error_msg := SQLERRM;
212 l_msg_data := l_error_code||' : '||l_error_msg;
213 IF (l_Debug_Level <= 5) THEN
214 ecx_cln_debug_pub.Add(l_msg_data,6);
215 ecx_cln_debug_pub.Add('EXITING GET_APP_REFID', 1);
216 END IF;
217 x_doc_creation_date := NULL;
218 END GET_DOCUMENT_CREATION_DATE;
219
220
221
222
223 -- Name
224 -- GET_CONTROL_AREA_REFID
225 -- Purpose
226 -- This procedure is called to retrieve application reference ID based on
227 -- XML message ID from control area of the payload
228 -- Arguments
229 -- XML Gateway Message ID
230 -- Notes
231 -- Uses a DOM Parser to parse the document and retrieve the application reference ID
232
233 PROCEDURE GET_CONTROL_AREA_REFID(
234 p_msgId IN RAW,
235 p_collaboration_standard IN VARCHAR2,
236 x_app_ref_id IN OUT NOCOPY VARCHAR2,
237 p_app_id IN VARCHAR2,
238 p_coll_type IN VARCHAR2
239 )
240 IS
241 l_xmlDoc CLOB;
242 l_parser xmlparser.parser := xmlparser.newParser;
243 l_domDoc xmldom.DOMDocument;
244 l_node xmldom.domNode;
245 l_element xmldom.domElement;
246 l_nodeList xmldom.domNodeList;
247 l_size number;
248 l_Nname varchar2(255);
249 l_Nvalue varchar2(255);
250 l_error_code VARCHAR2(255);
251 l_error_msg VARCHAR2(1000);
252 l_msg_data VARCHAR2(1000);
253 l_payload CLOB;
254 l_ini_pos NUMBER(38);
255 l_fin_pos NUMBER(38);
256 l_amount INTEGER;
257 BEGIN
258 IF (l_Debug_Level <= 2) THEN
259 ecx_cln_debug_pub.Add('-----------ENTERING GET_APP_REFID-----------', 2);
260 END IF;
261 IF (l_Debug_Level <= 2) THEN
262 ecx_cln_debug_pub.Add('WITH PARAMETERS', 1);
263 ecx_cln_debug_pub.Add('p_msgId:' || p_msgId, 1);
264 ecx_cln_debug_pub.Add('p_collaboration_standard:' || p_collaboration_standard, 1);
265 ecx_cln_debug_pub.Add('p_app_id:' || p_app_id, 1);
266 ecx_cln_debug_pub.Add('p_coll_type:' || p_coll_type, 1);
267 END IF;
268
269 IF p_collaboration_standard is NULL THEN
270 IF (l_Debug_Level <= 2) THEN
271 ecx_cln_debug_pub.Add('-----------EXITING GET_APP_REFID as collaboration standard is null-----------', 1);
272 END IF;
273 RETURN;
274 END IF;
275 x_app_ref_id := NULL;
276
277 SELECT payload into l_xmlDoc FROM ecx_doclogs WHERE msgid = HEXTORAW(p_msgId);
278
279 l_ini_pos := -1;
280 l_ini_pos := dbms_lob.instr(l_xmlDoc, '!DOCTYPE ');
281 IF (l_Debug_Level <= 2) THEN
282 ecx_cln_debug_pub.Add('Init Position:' || l_ini_pos, 1);
283 END IF;
284
285 IF (l_ini_pos > 0) THEN
286 l_fin_pos := dbms_lob.instr(l_xmlDoc, '>', l_ini_pos);
287 l_fin_pos := l_fin_pos + 1;
288 l_amount := dbms_lob.getlength(l_xmlDoc);
289
290 IF (l_Debug_Level <= 2) THEN
291 ecx_cln_debug_pub.Add('Final Position:' || l_fin_pos, 1);
292 ecx_cln_debug_pub.Add('Length:' || l_amount, 1);
293 END IF;
294
295 DBMS_LOB.CREATETEMPORARY(l_payload, TRUE, DBMS_LOB.SESSION);
296
297 dbms_lob.copy(l_payload, l_xmlDoc, l_amount - l_fin_pos + 1, 1, l_fin_pos);
298
299 END IF;
300
301 l_parser := xmlparser.newparser;
302 xmlparser.setValidationMode(l_parser,FALSE);
303 xmlparser.showWarnings(l_parser,FALSE);
304
305 BEGIN
306
307 IF (l_ini_pos > 0) THEN
308 xmlparser.parseClob(l_parser,l_payload);
309 ELSE
310 xmlparser.parseClob(l_parser,l_xmlDoc);
311 END IF;
312
313 l_domDoc := xmlparser.getDocument(l_parser);
314 IF(p_collaboration_standard = 'OAG') THEN
315 l_nodeList := Xmldom.getElementsByTagName(l_domDoc, 'CNTROLAREA');
316 l_element := xmldom.makeElement(xmldom.item( l_nodeList, 0 ));
317 l_nodeList := Xmldom.getElementsByTagName(l_element, 'REFERENCEID');
318 ELSIF (p_collaboration_standard = 'ROSETTANET') THEN
319 l_nodeList := Xmldom.getElementsByTagName(l_domDoc, 'thisDocumentIdentifier');
320 l_element := xmldom.makeElement(xmldom.item( l_nodeList, 0 ));
321 l_nodeList := Xmldom.getElementsByTagName(l_element, 'ProprietaryDocumentIdentifier');
325 END IF;
322 ELSE
323 IF (l_Debug_Level <= 2) THEN
324 ecx_cln_debug_pub.Add('-----------EXITING GET_APP_REFID as the collaboration standard is not supported-----------', 1);
326 RETURN;
327 END IF;
328 l_node := xmldom.item( l_nodeList, 0 );
329 l_Nvalue := xmldom.getNodeName(l_node);
330 l_node := xmldom.getFirstChild(l_node);
331
332 IF NOT xmldom.IsNull(l_node) THEN
333 l_error_code := SQLCODE;
334 l_error_msg := SQLERRM;
335 l_msg_data := l_error_code||' : '||l_error_msg;
336 IF (l_Debug_Level <= 5) THEN
337 ecx_cln_debug_pub.Add(l_msg_data,6);
338 END IF;
339 x_app_ref_id := xmldom.getNodeValue(l_node);
340 IF(p_collaboration_standard = 'ROSETTANET' and p_app_id is not null and p_coll_type is not null and x_app_ref_id is not null) THEN
341 x_app_ref_id := p_app_id || p_coll_type || x_app_ref_id;
342 END IF;
343 END IF;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 x_app_ref_id := NULL;
348 END;
349
350 IF (l_ini_pos > 0) THEN
351 DBMS_LOB.FREETEMPORARY(l_payload);
352 END IF;
353 xmlparser.freeparser(l_parser);
354
355 IF (l_Debug_Level <= 5) THEN
356 ecx_cln_debug_pub.Add('Application Reference ID:' || x_app_ref_id,1);
357 END IF;
358 IF (l_Debug_Level <= 2) THEN
359 ecx_cln_debug_pub.Add('EXITING GET_APP_REFID', 2);
360 END IF;
361 EXCEPTION
362 WHEN OTHERS THEN
363 l_error_code := SQLCODE;
364 l_error_msg := SQLERRM;
365 l_msg_data := l_error_code||' : '||l_error_msg;
366 IF (l_Debug_Level <= 5) THEN
367 ecx_cln_debug_pub.Add(l_msg_data,6);
368 ecx_cln_debug_pub.Add('EXITING GET_APP_REFID', 1);
369 END IF;
370 x_app_ref_id := NULL;
371 END GET_CONTROL_AREA_REFID;
372
373
374
375
376 -- Name
377 -- GET_DATA_AREA_REFID
378 -- Purpose
379 -- This procedure is called to retrieve application reference ID based on
380 -- XML message ID from data area of the payload
381 -- Arguments
382 -- XML Gateway Message ID
383 -- Notes
384 -- Uses a DOM Parser to parse the document and retrieve the application reference ID
385
386 PROCEDURE GET_DATA_AREA_REFID(
387 p_msgId IN RAW,
388 p_collaboration_standard IN VARCHAR2,
389 x_app_ref_id IN OUT NOCOPY VARCHAR2,
390 p_app_id IN VARCHAR2,
391 p_coll_type IN VARCHAR2)
392 IS
393 l_xmlDoc CLOB;
394 l_parser xmlparser.parser := xmlparser.newParser;
395 l_domDoc xmldom.DOMDocument;
396 l_node xmldom.domNode;
397 l_element xmldom.domElement;
398 l_nodeList xmldom.domNodeList;
399 l_size number;
400 l_Nname varchar2(255);
401 l_Nvalue varchar2(255);
402 l_error_code VARCHAR2(255);
403 l_error_msg VARCHAR2(1000);
404 l_msg_data VARCHAR2(1000);
405 l_payload CLOB;
406 l_ini_pos NUMBER(38);
407 l_fin_pos NUMBER(38);
408 l_amount INTEGER;
409 BEGIN
410 IF (l_Debug_Level <= 2) THEN
411 ecx_cln_debug_pub.Add('ENTERING GET_DATA_AREA_REFID', 2);
412 END IF;
413 IF (l_Debug_Level <= 2) THEN
414 ecx_cln_debug_pub.Add('WITH PARAMETERS', 1);
415 ecx_cln_debug_pub.Add('p_msgId:' || p_msgId, 1);
416 ecx_cln_debug_pub.Add('p_collaboration_standard:' || p_collaboration_standard, 1);
417 ecx_cln_debug_pub.Add('p_app_id:' || p_app_id, 1);
418 ecx_cln_debug_pub.Add('p_coll_type:' || p_coll_type, 1);
419 END IF;
420
421 IF p_collaboration_standard is NULL THEN
422 IF (l_Debug_Level <= 2) THEN
423 ecx_cln_debug_pub.Add('-----------EXITING GET_APP_REFID as collaboration standard is null-----------', 1);
424 END IF;
425 RETURN;
426 END IF;
427
428 x_app_ref_id := NULL;
429
430 SELECT payload into l_xmlDoc FROM ecx_doclogs WHERE msgid = HEXTORAW(p_msgId);
431 IF (l_Debug_Level <= 1) THEN
432 ecx_cln_debug_pub.Add('payload obtained', 1);
433 END IF;
434
435 l_ini_pos := -1;
436 l_ini_pos := dbms_lob.instr(l_xmlDoc, '!DOCTYPE ');
437 IF (l_Debug_Level <= 1) THEN
438 ecx_cln_debug_pub.Add('Init Position:' || l_ini_pos, 1);
439 END IF;
440
441 IF (l_ini_pos > 0) THEN
442 l_fin_pos := dbms_lob.instr(l_xmlDoc, '>', l_ini_pos);
443 l_fin_pos := l_fin_pos + 1;
444 l_amount := dbms_lob.getlength(l_xmlDoc);
445
446 IF (l_Debug_Level <= 1) THEN
447 ecx_cln_debug_pub.Add('Final Position:' || l_fin_pos, 1);
448 ecx_cln_debug_pub.Add('Length:' || l_amount, 1);
449 END IF;
450
451 DBMS_LOB.CREATETEMPORARY(l_payload, TRUE, DBMS_LOB.SESSION);
452
453 dbms_lob.copy(l_payload, l_xmlDoc, l_amount - l_fin_pos + 1, 1, l_fin_pos);
454
455 END IF;
456 IF (l_Debug_Level <= 1) THEN
460 l_parser := xmlparser.newparser;
457 ecx_cln_debug_pub.Add('about to initialize parser', 1);
458 END IF;
459
461 xmlparser.setValidationMode(l_parser,FALSE);
462 xmlparser.showWarnings(l_parser,FALSE);
463 IF (l_Debug_Level <= 1) THEN
464 ecx_cln_debug_pub.Add('parser initialized', 1);
465 END IF;
466
467 BEGIN
468
469 IF (l_ini_pos > 0) THEN
470 xmlparser.parseClob(l_parser,l_payload);
471 ELSE
472 xmlparser.parseClob(l_parser,l_xmlDoc);
473 END IF;
474 IF (l_Debug_Level <= 1) THEN
475 ecx_cln_debug_pub.Add('xml doc parsed', 1);
476 END IF;
477
478 l_domDoc := xmlparser.getDocument(l_parser);
479 IF (l_Debug_Level <= 1) THEN
480 ecx_cln_debug_pub.Add('dom doc obtained', 1);
481 END IF;
482 IF(p_collaboration_standard = 'OAG') THEN
483 l_nodeList := Xmldom.getElementsByTagName(l_domDoc, 'DATAAREA');
484 IF (l_Debug_Level <= 1) THEN
485 ecx_cln_debug_pub.Add('IN OAG, data area found', 1);
486 END IF;
487 l_element := xmldom.makeElement(xmldom.item( l_nodeList, 0 ));
488 IF (l_Debug_Level <= 1) THEN
489 ecx_cln_debug_pub.Add('data area element obtained', 1);
490 END IF;
491 l_nodeList := Xmldom.getElementsByTagName(l_element, 'REFERENCEID');
492 IF (l_Debug_Level <= 1) THEN
493 ecx_cln_debug_pub.Add('Reference id element obtained', 1);
494 END IF;
495 ELSIF (p_collaboration_standard = 'ROSETTANET') THEN
496 l_nodeList := Xmldom.getElementsByTagName(l_domDoc, 'requestingDocumentIdentifier');
497 l_element := xmldom.makeElement(xmldom.item( l_nodeList, 0 ));
498 l_nodeList := Xmldom.getElementsByTagName(l_element, 'ProprietaryDocumentIdentifier');
499 ELSE
500 IF (l_Debug_Level <= 2) THEN
501 ecx_cln_debug_pub.Add('-----------EXITING GET_APP_REFID as the collaboration standard is not supported-----------', 1);
502 END IF;
503 RETURN;
504 END IF;
505
506 l_node := xmldom.item( l_nodeList, 0 );
507 l_Nvalue := xmldom.getNodeName(l_node);
508 l_node := xmldom.getFirstChild(l_node);
509
510 IF NOT xmldom.IsNull(l_node) THEN
511 IF (l_Debug_Level <= 1) THEN
512 ecx_cln_debug_pub.Add('about to obtain the value from the node, as node is not null', 1);
513 END IF;
514 x_app_ref_id := xmldom.getNodeValue(l_node);
515 IF (l_Debug_Level <= 1) THEN
516 ecx_cln_debug_pub.Add('x_app_ref_id:' || x_app_ref_id, 1);
517 END IF;
518 IF(p_collaboration_standard = 'ROSETTANET' and p_app_id is not null and p_coll_type is not null and x_app_ref_id is not null) THEN
519 x_app_ref_id := p_app_id || p_coll_type || x_app_ref_id;
520 END IF;
521 IF (l_Debug_Level <= 1) THEN
522 ecx_cln_debug_pub.Add('x_app_ref_id:' || x_app_ref_id, 1);
523 END IF;
524 END IF;
525
526 EXCEPTION
527 WHEN OTHERS THEN
528 IF (l_Debug_Level <= 1) THEN
529 ecx_cln_debug_pub.Add('In when others. so setting appref id to null', 1);
530 END IF;
531 x_app_ref_id := NULL;
532 END;
533
534 IF (l_ini_pos > 0) THEN
535 DBMS_LOB.FREETEMPORARY(l_payload);
536 END IF;
537 xmlparser.freeparser(l_parser);
538
539 IF (l_Debug_Level <= 1) THEN
540 ecx_cln_debug_pub.Add('Application Reference ID:' || x_app_ref_id,1);
541 END IF;
542 IF (l_Debug_Level <= 2) THEN
543 ecx_cln_debug_pub.Add('EXITING GET_DATA_AREA_REFID', 2);
544 END IF;
545 EXCEPTION
546 WHEN OTHERS THEN
547 l_error_code := SQLCODE;
548 l_error_msg := SQLERRM;
549 l_msg_data := l_error_code||' : '||l_error_msg;
550 IF (l_Debug_Level <= 5) THEN
551 ecx_cln_debug_pub.Add(l_msg_data,6);
552 ecx_cln_debug_pub.Add('EXITING GET_DATA_AREA_REFID', 1);
553 END IF;
554 x_app_ref_id := NULL;
555 END GET_DATA_AREA_REFID;
556
557
558
559 -- Name
560 -- VALIDATE_PARAMS
561 -- Purpose
562 -- This procedure is called for validation purposes.This checks for validity of all lookup
563 -- values passed.
564 -- Arguments
565 --
566 -- Notes
567 -- No specific notes.
568
569
570 PROCEDURE VALIDATE_PARAMS(
571 x_return_status OUT NOCOPY VARCHAR2,
572 x_msg_data OUT NOCOPY VARCHAR2,
573 p_app_id IN VARCHAR2,
574 p_doc_dir IN VARCHAR2,
575 p_doc_type IN VARCHAR2,
576 p_coll_status IN VARCHAR2,
577 p_coll_pt IN VARCHAR2,
578 p_coll_type IN VARCHAR2,
579 p_doc_status IN VARCHAR2,
580 p_disposition IN VARCHAR2)
581
582 IS
583 l_error_code NUMBER;
584 l_error_msg VARCHAR2(2000);
585 l_msg_data VARCHAR2(2000);
589 BEGIN
586 l_meaning VARCHAR2(255);
587 l_param VARCHAR2(255);
588
590
591 IF (l_Debug_Level <= 2) THEN
592 ecx_cln_debug_pub.Add('-----------Entering VALIDATE_PARAMS API---- ',2);
593 END IF;
594
595
596 -- Initialize API return status to success
597 x_return_status := FND_API.G_RET_STS_SUCCESS;
598
599 IF (l_Debug_Level <= 1) THEN
600 ecx_cln_debug_pub.Add('------------Parameters Received-------------',1);
601 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
602 ecx_cln_debug_pub.Add('DOC DIRECTION ----- >>>'||p_doc_dir,1);
603 ecx_cln_debug_pub.Add('DOC TYPE ----- >>>'||p_doc_type,1);
604 ecx_cln_debug_pub.Add('COLL STATUS ----- >>>'||p_coll_status,1);
605 ecx_cln_debug_pub.Add('COLL POINT ----- >>>'||p_coll_pt,1);
606 ecx_cln_debug_pub.Add('COLL TYPE ----- >>>'||p_coll_type,1);
607 ecx_cln_debug_pub.Add('DOC STATUS ----- >>>'||p_doc_status,1);
608 ecx_cln_debug_pub.Add('DISPOSITION ----- >>>'||p_disposition,1);
609 ecx_cln_debug_pub.Add('---------------------------------------------',1);
610
611
612
613 ecx_cln_debug_pub.Add('-------------Validating Parameters-----------',1);
614
615 ecx_cln_debug_pub.Add('>>>Validating Application ID >>>',1);
616 END IF;
617 IF (p_app_id IS NOT NULL) THEN
618 BEGIN
619 l_meaning := p_app_id;
620 SELECT meaning INTO l_meaning FROM fnd_lookups
621 WHERE lookup_code = p_app_id AND lookup_type = 'CLN_APPLICATION_ID';
622 IF (l_Debug_Level <= 1) THEN
623 ecx_cln_debug_pub.Add('...........Application ID found as - '||l_meaning,1);
624 END IF;
625 EXCEPTION
626 WHEN NO_DATA_FOUND THEN
627 l_param := 'Application ID';
628 RAISE FND_API.G_EXC_ERROR;
629 END;
630 END IF;
631
632
633 IF (l_Debug_Level <= 1) THEN
634 ecx_cln_debug_pub.Add('>>>Validating Document Direction >>>',1);
635 END IF;
636 IF (p_doc_dir IS NOT NULL) THEN
637 BEGIN
638 l_meaning := p_doc_dir;
639 SELECT meaning INTO l_meaning FROM fnd_lookups
640 WHERE lookup_code = p_doc_dir AND lookup_type = 'CLN_COLLABORATION_DOC_DIRECTN';
641 IF (l_Debug_Level <= 1) THEN
642 ecx_cln_debug_pub.Add('...........Document Direction found as - '||l_meaning,1);
643 END IF;
644 EXCEPTION
645 WHEN NO_DATA_FOUND THEN
646 l_param := 'Document Direction';
647 RAISE FND_API.G_EXC_ERROR;
648 END;
649 END IF;
650
651
652 IF (l_Debug_Level <= 1) THEN
653 ecx_cln_debug_pub.Add('>>>Validating Document Type >>>',1);
654 END IF;
655 IF (p_doc_type IS NOT NULL) THEN
656 BEGIN
657 l_meaning := p_doc_type;
658 SELECT meaning INTO l_meaning FROM fnd_lookups
659 WHERE lookup_code = p_doc_type AND lookup_type = 'CLN_COLLABORATION_DOC_TYPE';
660 IF (l_Debug_Level <= 1) THEN
661 ecx_cln_debug_pub.Add('...........Document Type found as - '||l_meaning,1);
662 END IF;
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 l_param := 'Document Type';
666 RAISE FND_API.G_EXC_ERROR;
667 END;
668 END IF;
669
670
671 IF (l_Debug_Level <= 1) THEN
672 ecx_cln_debug_pub.Add('>>>Validating Collaboration Status >>>',1);
673 END IF;
674 IF (p_coll_status IS NOT NULL) THEN
675 BEGIN
676 l_meaning := p_coll_status;
677 SELECT meaning INTO l_meaning FROM fnd_lookups
678 WHERE lookup_code = p_coll_status AND lookup_type = 'CLN_COLLABORATION_STATUS';
679 IF (l_Debug_Level <= 1) THEN
680 ecx_cln_debug_pub.Add('...........Collaboration Status found as - '||l_meaning,1);
681 END IF;
682 EXCEPTION
683 WHEN NO_DATA_FOUND THEN
684 l_param := 'Collaboration Status';
685 RAISE FND_API.G_EXC_ERROR;
686 END;
687 END IF;
688
689
690 IF (l_Debug_Level <= 1) THEN
691 ecx_cln_debug_pub.Add('>>>Validating Collaboration Point >>>',1);
692 END IF;
693 IF (p_coll_pt IS NOT NULL) THEN
694 BEGIN
695 l_meaning := p_coll_pt;
696 SELECT meaning INTO l_meaning FROM fnd_lookups
697 WHERE lookup_code = p_coll_pt AND lookup_type = 'CLN_COLLABORATION_POINT';
698 IF (l_Debug_Level <= 1) THEN
702 WHEN NO_DATA_FOUND THEN
699 ecx_cln_debug_pub.Add('...........Collaboration Point found as - '||l_meaning,1);
700 END IF;
701 EXCEPTION
703 l_param := 'Collaboration Point';
704 RAISE FND_API.G_EXC_ERROR;
705 END;
706 END IF;
707
708
709 IF (l_Debug_Level <= 1) THEN
710 ecx_cln_debug_pub.Add('>>>Validating Collaboration Type >>>',1);
711 END IF;
712 IF (p_coll_type IS NOT NULL) THEN
713 BEGIN
714 l_meaning := p_coll_type;
715 SELECT meaning INTO l_meaning FROM fnd_lookups
716 WHERE lookup_code = p_coll_type AND lookup_type = 'CLN_COLLABORATION_TYPE';
717 IF (l_Debug_Level <= 1) THEN
718 ecx_cln_debug_pub.Add('...........Collaboration Type found as - '||l_meaning,1);
719 END IF;
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 l_param := 'Collaboration Type';
723 RAISE FND_API.G_EXC_ERROR;
724 END;
725 END IF;
726
727
728 IF (l_Debug_Level <= 1) THEN
729 ecx_cln_debug_pub.Add('>>>Validating Document Status >>>',1);
730 END IF;
731 IF (p_doc_status IS NOT NULL) THEN
732 BEGIN
733 l_meaning := p_doc_status;
734 SELECT meaning INTO l_meaning FROM fnd_lookups
735 WHERE lookup_code = p_doc_status AND lookup_type = 'CLN_COLLABORATION_DOC_STATUS';
736 IF (l_Debug_Level <= 1) THEN
737 ecx_cln_debug_pub.Add('...........Collaboration Document Status found as - '||l_meaning,1);
738 END IF;
739 EXCEPTION
740 WHEN NO_DATA_FOUND THEN
741 l_param := 'Collaboration Document Status';
742 RAISE FND_API.G_EXC_ERROR;
743 END;
744 END IF;
745
746 IF (l_Debug_Level <= 1) THEN
747 ecx_cln_debug_pub.Add('>>>Validating Disposition >>>',1);
748 END IF;
749 IF (p_disposition IS NOT NULL) THEN
750 BEGIN
751 l_meaning := p_disposition;
752 SELECT meaning INTO l_meaning FROM fnd_lookups
753 WHERE lookup_code = p_disposition AND lookup_type = 'CLN_DISPOSITION';
754 IF (l_Debug_Level <= 1) THEN
755 ecx_cln_debug_pub.Add('...........Disposition found as - '||l_meaning,1);
756 END IF;
757 EXCEPTION
758 WHEN NO_DATA_FOUND THEN
759 l_param := 'Disposition';
760 RAISE FND_API.G_EXC_ERROR;
761 END;
762 END IF;
763
764
765 FND_MESSAGE.SET_NAME('CLN','CLN_CH_VALIDATION_SUCCESS');
766 x_msg_data := FND_MESSAGE.GET;
767
768 IF (l_Debug_Level <= 1) THEN
769 ecx_cln_debug_pub.Add('Successfully validated all parameters passed',1);
770 END IF;
771 IF (l_Debug_Level <= 2) THEN
772 ecx_cln_debug_pub.Add('-------- Exiting VALIDATE_PARAMS API ----- ',2);
773 END IF;
774
775 -- Exception Handling
776 EXCEPTION
777 WHEN FND_API.G_EXC_ERROR THEN
778 x_return_status := FND_API.G_RET_STS_ERROR ;
779 FND_MESSAGE.SET_NAME('CLN','CLN_CH_PARAM_VALIDATION');
780 FND_MESSAGE.SET_TOKEN('PARAM',l_param);
781 FND_MESSAGE.SET_TOKEN('VALUE',l_meaning);
782 x_msg_data := FND_MESSAGE.GET;
783 IF (l_Debug_Level <= 5) THEN
784 ecx_cln_debug_pub.Add(l_param||' is irrelevant -'||l_meaning,4);
785 ecx_cln_debug_pub.Add('-------- Exiting VALIDATE_PARAMS API ----- ',2);
786 END IF;
787
788
789 WHEN OTHERS THEN
790 l_error_code :=SQLCODE;
791 l_error_msg :=SQLERRM;
792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
793 x_msg_data :=l_error_code||' : '||l_error_msg;
794 IF (l_Debug_Level <= 5) THEN
795 ecx_cln_debug_pub.Add(x_msg_data,6);
796 ecx_cln_debug_pub.Add('-------- Exiting VALIDATE_PARAMS API ----- ',2);
797 END IF;
798
799
800 END VALIDATE_PARAMS;
801
802
803
804 -- Name
805 -- UPDATE_COLLABORATION_INIT
806 -- Purpose
807 -- This procedure is called internally by the public procedure CREATE_COLLABORATION
808 -- to add the initial details in the CLN_COLL_HIST_DTL table corresponding to newly started
809 -- collaboration.
810 -- Arguments
811 --
812 -- Notes
813 -- No specific notes.
814
815
816 PROCEDURE UPDATE_COLLABORATION_INIT(
817 x_return_status OUT NOCOPY VARCHAR2,
818 p_coll_id IN NUMBER,
819 p_doc_type IN VARCHAR2,
823 p_doc_status IN VARCHAR2,
820 p_doc_dir IN VARCHAR2,
821 p_coll_pt IN VARCHAR2,
822 p_org_ref IN VARCHAR2,
824 p_notification_id IN VARCHAR2,
825 p_msg_text IN VARCHAR2,
826 p_xmlg_transaction_type IN VARCHAR2,
827 p_xmlg_transaction_subtype IN VARCHAR2,
828 p_xmlg_document_id IN VARCHAR2,
829 p_xmlg_msg_id IN VARCHAR2,
830 p_xmlg_internal_control_number IN NUMBER,
831 p_resend_flag IN VARCHAR2,
832 p_xmlg_int_transaction_type IN VARCHAR2,
833 p_xmlg_int_transaction_subtype IN VARCHAR2,
834 p_xml_event_key IN VARCHAR2)
835
836 IS
837 l_dtl_coll_id NUMBER;
838 l_error_code NUMBER;
839 l_error_msg VARCHAR2(2000);
840 l_msg_data VARCHAR2(2000);
841
842 BEGIN
843
844
845 IF (l_Debug_Level <= 2) THEN
846 ecx_cln_debug_pub.Add('------- Entering UPDATE_COLLABORATION_INIT API -------- ',2);
847 END IF;
848
849
850 -- Initialize API return status to success
851 x_return_status := FND_API.G_RET_STS_SUCCESS;
852
853 IF (l_Debug_Level <= 1) THEN
854 ecx_cln_debug_pub.Add('------------Parameters Received-------------',1);
855 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
856 ecx_cln_debug_pub.Add('DOC TYPE ----- >>>'||p_doc_type,1);
857 ecx_cln_debug_pub.Add('DOC DIRECTION ----- >>>'||p_doc_dir,1);
858 ecx_cln_debug_pub.Add('COLL POINT ----- >>>'||p_coll_pt,1);
859 ecx_cln_debug_pub.Add('ORIGINATOR REFERNCE ----- >>>'||p_org_ref,1);
860 ecx_cln_debug_pub.Add('DOC STATUS ----- >>>'||p_doc_status,1);
861 ecx_cln_debug_pub.Add('NOTIFICATION ID ----- >>>'||p_notification_id,1);
862 ecx_cln_debug_pub.Add('MESSAGE TEXT ----- >>>'||p_msg_text,1);
863 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
864 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
865 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
866 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
867 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
868 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
869 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
870 ecx_cln_debug_pub.Add('RESEND FLAG ----- >>>'||p_resend_flag,1);
871 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
872 ecx_cln_debug_pub.Add('---------------------------------------------',1);
873 END IF;
874
875
876 -- Collaboration Detail ID is generated from a sequence.
877 SELECT cln_collaboration_dtl_id_s.nextval INTO l_dtl_coll_id FROM dual ;
878 IF (l_Debug_Level <= 1) THEN
879 ecx_cln_debug_pub.Add('Collaboration Detail ID generated : '||l_dtl_coll_id,1);
880
881
882 ecx_cln_debug_pub.Add('----- Before SQL Query : Adding Details in CLN_COLL_HIST_DTL -----',1);
883 END IF;
884
885 -- Initial Collaboration Details are added into CLN_COLL_HIST_DTL Table
886 --Bug 3655492 : Added nvl(p_doc_type,'UNKNOWN') for 11.5.10 performance enh, to make sure always Doc Type is not null
887 INSERT INTO CLN_COLL_HIST_DTL(
888 COLLABORATION_DTL_ID,COLLABORATION_ID,COLLABORATION_DOCUMENT_TYPE,
889 DOCUMENT_DIRECTION,COLLABORATION_POINT,
890 ORIGINATOR_REFERENCE,DOCUMENT_STATUS,NOTIFICATION_ID,MESSAGE_TEXT,CREATION_DATE,CREATED_BY,
891 LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN, XMLG_TRANSACTION_TYPE, XMLG_TRANSACTION_SUBTYPE,
892 XMLG_DOCUMENT_ID, XMLG_MSG_ID, XMLG_INTERNAL_CONTROL_NUMBER,
893 RESEND_FLAG, XMLG_INT_TRANSACTION_TYPE, XMLG_INT_TRANSACTION_SUBTYPE, XML_EVENT_KEY)
894 VALUES(l_dtl_coll_id,p_coll_id,nvl(p_doc_type,'UNKNOWN'),p_doc_dir,p_coll_pt,p_org_ref,p_doc_status,
895 p_notification_id,p_msg_text,SYSDATE,FND_GLOBAL.USER_ID,
896 SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID, p_xmlg_transaction_type,
897 p_xmlg_transaction_subtype,p_xmlg_document_id, p_xmlg_msg_id, p_xmlg_internal_control_number,
898 p_resend_flag, p_xmlg_int_transaction_type, p_xmlg_int_transaction_subtype, p_xml_event_key);
899
900 IF (l_Debug_Level <= 1) THEN
901 ecx_cln_debug_pub.Add('----- After SQL Query : Adding Details in CLN_COLL_HIST_DTL -----',1);
902 END IF;
903
904
905 -- Check whether the above SQL Query resulted in some updations or not.
906 IF SQL%FOUND THEN
907 IF (l_Debug_Level <= 1) THEN
911 l_msg_data := 'Failed to add Collaboration Details in CLN_COLL_HIST_DTL TABLE';
908 ecx_cln_debug_pub.Add('Collaboration Details successfully entered in CLN_COLL_HIST_DTL TABLE',1);
909 END IF;
910 ELSE
912 RAISE FND_API.G_EXC_ERROR;
913 END IF;
914
915 IF (l_Debug_Level <= 2) THEN
916 ecx_cln_debug_pub.Add('--------- Exiting UPDATE_COLLABORATION_INIT API -------- ',2);
917 END IF;
918
919 -- Exception Handling
920 EXCEPTION
921
922 WHEN FND_API.G_EXC_ERROR THEN
923 x_return_status := FND_API.G_RET_STS_ERROR ;
924 IF (l_Debug_Level <= 4) THEN
925 ecx_cln_debug_pub.Add(l_msg_data,4);
926 ecx_cln_debug_pub.Add('--------- Exiting UPDATE_COLLABORATION_INIT API -------- ',2);
927 END IF;
928
929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
930 l_error_code :=SQLCODE;
931 l_error_msg :=SQLERRM;
932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
933 l_msg_data :=l_error_code||' : '||l_error_msg;
934 IF (l_Debug_Level <= 5) THEN
935 ecx_cln_debug_pub.Add(l_msg_data,6);
936 ecx_cln_debug_pub.Add('--------- Exiting UPDATE_COLLABORATION_INIT API -------- ',2);
937 END IF;
938
939 WHEN OTHERS THEN
940 l_error_code :=SQLCODE;
941 l_error_msg :=SQLERRM;
942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
943 l_msg_data :=l_error_code||' : '||l_error_msg;
944 IF (l_Debug_Level <= 5) THEN
945 ecx_cln_debug_pub.Add(l_msg_data,6);
946 ecx_cln_debug_pub.Add('--------- Exiting UPDATE_COLLABORATION_INIT API -------- ',2);
947 END IF;
948
949
950 END UPDATE_COLLABORATION_INIT;
951
952
953
954 -- Name
955 -- CREATE_COLLABORATION
956 -- Purpose
957 -- This is the public procedure which starts a new Collaboration
958 -- and adds the initial details corresponding to it in both the CLN_COLL_HIST_HDR
959 -- and CLN_COLL_HIST_DTL Tables.
960 -- Arguments
961 --
962 -- Notes
963 -- No specific notes.
964
965
966 PROCEDURE CREATE_COLLABORATION(
967 x_return_status OUT NOCOPY VARCHAR2,
968 x_msg_data OUT NOCOPY VARCHAR2,
969 p_app_id IN VARCHAR2,
970 p_ref_id IN VARCHAR2,
971 p_org_id IN NUMBER,
972 p_rel_no IN VARCHAR2,
973 p_doc_no IN VARCHAR2,
974 p_doc_rev_no IN VARCHAR2,
975 p_xmlg_transaction_type IN VARCHAR2,
976 p_xmlg_transaction_subtype IN VARCHAR2,
977 p_xmlg_document_id IN VARCHAR2,
978 p_partner_doc_no IN VARCHAR2,
979 p_coll_type IN VARCHAR2,
980 p_tr_partner_type IN VARCHAR2,
981 p_tr_partner_id IN VARCHAR2,
982 p_tr_partner_site IN VARCHAR2,
983 p_resend_flag IN VARCHAR2,
984 p_resend_count IN NUMBER,
985 p_doc_owner IN VARCHAR2,
986 p_init_date IN DATE,
987 p_doc_creation_date IN DATE,
988 p_doc_revision_date IN DATE,
989 p_doc_type IN VARCHAR2,
990 p_doc_dir IN VARCHAR2,
991 p_coll_pt IN VARCHAR2,
992 p_xmlg_msg_id IN VARCHAR2,
993 p_unique1 IN VARCHAR2,
994 p_unique2 IN VARCHAR2,
995 p_unique3 IN VARCHAR2,
996 p_unique4 IN VARCHAR2,
997 p_unique5 IN VARCHAR2,
998 p_sender_component IN VARCHAR2,
999 p_rosettanet_check_required IN BOOLEAN,
1000 x_coll_id OUT NOCOPY NUMBER,
1001 p_xmlg_internal_control_number IN NUMBER,
1002 p_xmlg_int_transaction_type IN VARCHAR2,
1003 p_xmlg_int_transaction_subtype IN VARCHAR2,
1004 p_msg_text IN VARCHAR2,
1005 p_xml_event_key IN VARCHAR2,
1006 p_collaboration_standard IN VARCHAR2,
1007 p_attribute1 IN VARCHAR2,
1008 p_attribute2 IN VARCHAR2,
1009 p_attribute3 IN VARCHAR2,
1010 p_attribute4 IN VARCHAR2,
1011 p_attribute5 IN VARCHAR2,
1012 p_attribute6 IN VARCHAR2,
1013 p_attribute7 IN VARCHAR2,
1014 p_attribute8 IN VARCHAR2,
1018 p_attribute12 IN VARCHAR2,
1015 p_attribute9 IN VARCHAR2,
1016 p_attribute10 IN VARCHAR2,
1017 p_attribute11 IN VARCHAR2,
1019 p_attribute13 IN VARCHAR2,
1020 p_attribute14 IN VARCHAR2,
1021 p_attribute15 IN VARCHAR2,
1022 p_dattribute1 IN DATE,
1023 p_dattribute2 IN DATE,
1024 p_dattribute3 IN DATE,
1025 p_dattribute4 IN DATE,
1026 p_dattribute5 IN DATE,
1027 p_owner_role IN VARCHAR2 )
1028
1029 IS
1030
1031 l_return_status VARCHAR2(30);
1032 l_error_code NUMBER;
1033 l_error_msg VARCHAR2(2000);
1034 l_msg_data VARCHAR2(2000);
1035 l_msg_text VARCHAR2(2000);
1036 l_debug_mode VARCHAR2(255);
1037 l_fnd_profile VARCHAR2(100);
1038 l_protocol_type VARCHAR2(5);
1039 l_update_reqd BOOLEAN;
1040 l_xmlg_internal_control_number NUMBER;
1041 l_xmlg_msg_id VARCHAR2(100);
1042 l_xmlg_transaction_type VARCHAR2(100);
1043 l_xmlg_transaction_subtype VARCHAR2(100);
1044 l_xmlg_int_transaction_type VARCHAR2(100);
1045 l_xmlg_int_transaction_subtype VARCHAR2(100);
1046 l_xmlg_document_id VARCHAR2(256);
1047 l_doc_dir VARCHAR2(240);
1048 l_tr_partner_type VARCHAR2(30);
1049 l_tr_partner_id VARCHAR2(256);
1050 l_tr_partner_site VARCHAR2(256);
1051 l_sender_component VARCHAR2(500);
1052 l_app_id VARCHAR2(10);
1053 l_coll_type VARCHAR2(30);
1054 l_doc_type VARCHAR2(100);
1055 l_resend_flag VARCHAR2(1);
1056 l_doc_no VARCHAR2(255);
1057 l_coll_status VARCHAR2(10);
1058 l_ref_id VARCHAR2(100);
1059 l_doc_owner VARCHAR2(30);
1060 l_owner_role VARCHAR2(30);
1061 l_coll_pt VARCHAR2(20);
1062 l_xml_event_key VARCHAR2(240);
1063 l_rosettanet_check_required BOOLEAN;
1064 l_collaboration_standard VARCHAR2(30);
1065 l_doc_creation_date DATE;
1066
1067 BEGIN
1068
1069 -- Sets the debug mode to be FILE
1070 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
1071
1072
1073 IF (l_Debug_Level <= 2) THEN
1074 ecx_cln_debug_pub.Add('------ Entering CREATE_COLLABORATION API ------- ',2);
1075 END IF;
1076
1077
1078
1079 -- Standard Start of API savepoint
1080 SAVEPOINT CREATE_COLLABORATION_PUB;
1081
1082 -- Initialize API return status to success
1083 x_return_status := FND_API.G_RET_STS_SUCCESS;
1084 l_msg_data := 'Collaboration successfully created ';
1085
1086
1087 -- get the paramaters passed
1088 IF (l_Debug_Level <= 1) THEN
1089 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
1090 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
1091 ecx_cln_debug_pub.Add('REFERENCE ID ----- >>>'||p_ref_id,1);
1092 ecx_cln_debug_pub.Add('ORG ID ----- >>>'||p_org_id,1);
1093 ecx_cln_debug_pub.Add('RELEASE NUMBER ----- >>>'||p_rel_no,1);
1094 ecx_cln_debug_pub.Add('DOCUMENT NO ----- >>>'||p_doc_no,1);
1095 ecx_cln_debug_pub.Add('DOCUMENT REV. NO ----- >>>'||p_doc_rev_no,1);
1096 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
1097 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_type,1);
1098 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
1099 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
1100 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
1101 ecx_cln_debug_pub.Add('PARTNER DOCUMENT NO ----- >>>'||p_partner_doc_no,1);
1102 ecx_cln_debug_pub.Add('COLLABORATION TYPE ----- >>>'||p_coll_type,1);
1103 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||p_tr_partner_type,1);
1104 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||p_tr_partner_id,1);
1105 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||p_tr_partner_site,1);
1106 ecx_cln_debug_pub.Add('RESENG FLAG ----- >>>'||p_resend_flag,1);
1107 ecx_cln_debug_pub.Add('RESEND COUNT ----- >>>'||p_resend_count,1);
1111 ecx_cln_debug_pub.Add('DOCUMENT CREATION DATE ----- >>>'||p_doc_creation_date,1);
1108 ecx_cln_debug_pub.Add('DOCUMENT OWNER ----- >>>'||p_doc_owner,1);
1109 ecx_cln_debug_pub.Add('OWNER ROLE ----- >>>'||p_owner_role,1);
1110 ecx_cln_debug_pub.Add('INITIATION DATE ----- >>>'||p_init_date,1);
1112 ecx_cln_debug_pub.Add('DOCUMENT REVISION DATE ----- >>>'||p_doc_revision_date,1);
1113 ecx_cln_debug_pub.Add('DOCUMENT TYPE ----- >>>'||p_doc_type,1);
1114 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
1115 ecx_cln_debug_pub.Add('COLLABORATION POINT ----- >>>'||p_coll_pt,1);
1116 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
1117 ecx_cln_debug_pub.Add('UNIQUE 1 ----- >>>'||p_unique1,1);
1118 ecx_cln_debug_pub.Add('UNIQUE 2 ----- >>>'||p_unique2,1);
1119 ecx_cln_debug_pub.Add('UNIQUE 3 ----- >>>'||p_unique3,1);
1120 ecx_cln_debug_pub.Add('UNIQUE 4 ----- >>>'||p_unique4,1);
1121 ecx_cln_debug_pub.Add('UNIQUE 5 ----- >>>'||p_unique5,1);
1122 ecx_cln_debug_pub.Add('SENDER COMPONENT ----- >>>'||p_sender_component,1);
1123 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
1124 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
1125 ecx_cln_debug_pub.Add('MSG TXT ----- >>>'||p_msg_text,1);
1126 ecx_cln_debug_pub.Add('Collaboration Standard ----- >>>'||p_collaboration_standard,1);
1127 ecx_cln_debug_pub.Add('ATTRIBUTE1 ----- >>>'||p_attribute1,1);
1128 ecx_cln_debug_pub.Add('ATTRIBUTE2 ----- >>>'||p_attribute2,1);
1129 ecx_cln_debug_pub.Add('ATTRIBUTE3 ----- >>>'||p_attribute3,1);
1130 ecx_cln_debug_pub.Add('ATTRIBUTE4 ----- >>>'||p_attribute4,1);
1131 ecx_cln_debug_pub.Add('ATTRIBUTE5 ----- >>>'||p_attribute5,1);
1132 ecx_cln_debug_pub.Add('ATTRIBUTE6 ----- >>>'||p_attribute6,1);
1133 ecx_cln_debug_pub.Add('ATTRIBUTE7 ----- >>>'||p_attribute7,1);
1134 ecx_cln_debug_pub.Add('ATTRIBUTE8 ----- >>>'||p_attribute8,1);
1135 ecx_cln_debug_pub.Add('ATTRIBUTE9 ----- >>>'||p_attribute9,1);
1136 ecx_cln_debug_pub.Add('ATTRIBUTE10 ----- >>>'||p_attribute10,1);
1137 ecx_cln_debug_pub.Add('ATTRIBUTE11 ----- >>>'||p_attribute11,1);
1138 ecx_cln_debug_pub.Add('ATTRIBUTE12 ----- >>>'||p_attribute12,1);
1139 ecx_cln_debug_pub.Add('ATTRIBUTE13 ----- >>>'||p_attribute13,1);
1140 ecx_cln_debug_pub.Add('ATTRIBUTE14 ----- >>>'||p_attribute14,1);
1141 ecx_cln_debug_pub.Add('ATTRIBUTE15 ----- >>>'||p_attribute15,1);
1142 ecx_cln_debug_pub.Add('DATTRIBUTE1 ----- >>>'||p_dattribute1,1);
1143 ecx_cln_debug_pub.Add('DATTRIBUTE2 ----- >>>'||p_dattribute2,1);
1144 ecx_cln_debug_pub.Add('DATTRIBUTE3 ----- >>>'||p_dattribute3,1);
1145 ecx_cln_debug_pub.Add('DATTRIBUTE4 ----- >>>'||p_dattribute4,1);
1146 ecx_cln_debug_pub.Add('DATTRIBUTE5 ----- >>>'||p_dattribute5,1);
1147 ecx_cln_debug_pub.Add('===========================================',1);
1148 END IF;
1149
1150 -- Assigning parameter to local variables
1151 l_xmlg_internal_control_number := p_xmlg_internal_control_number;
1152 l_xmlg_msg_id := p_xmlg_msg_id;
1153 l_xmlg_transaction_type := p_xmlg_transaction_type;
1154 l_xmlg_transaction_subtype := p_xmlg_transaction_subtype;
1155 l_xmlg_int_transaction_type := p_xmlg_int_transaction_type;
1156 l_xmlg_int_transaction_subtype := p_xmlg_int_transaction_subtype;
1157 l_xmlg_document_id := p_xmlg_document_id;
1158 l_doc_dir := p_doc_dir;
1159 l_tr_partner_type := p_tr_partner_type;
1160 l_tr_partner_id := p_tr_partner_id;
1161 l_tr_partner_site := p_tr_partner_site;
1162 l_sender_component := p_sender_component;
1163 l_app_id := p_app_id;
1164 l_coll_type := p_coll_type;
1165 l_doc_type := p_doc_type;
1166 l_ref_id := p_ref_id;
1167 l_doc_no := p_doc_no;
1168 l_doc_owner := p_doc_owner;
1169 l_owner_role := p_owner_role;
1170 l_coll_pt := p_coll_pt;
1171 l_rosettanet_check_required := p_rosettanet_check_required;
1172 l_msg_text := p_msg_text;
1173 l_xml_event_key := p_xml_event_key;
1174 l_collaboration_standard := p_collaboration_standard;
1178 -- Set Default values.
1175 l_doc_creation_date := p_doc_creation_date;
1176
1177
1179
1180 -- Removed as per bug #2641981.
1181 -- Check if document number is passed and set it to default if passed as null
1182 IF (p_doc_no IS NULL) THEN
1183 IF (l_Debug_Level <= 1) THEN
1184 ecx_cln_debug_pub.Add('Document Number passed as NULL',1);
1185 END IF;
1186 FND_MESSAGE.SET_NAME('CLN','CLN_CH_DOC_NUMBER_NOT_GEN');
1187 l_doc_no := FND_MESSAGE.GET;
1188 IF (l_Debug_Level <= 1) THEN
1189 ecx_cln_debug_pub.Add('Setting Document Number :'||l_doc_no,1);
1190 END IF;
1191
1192
1193 END IF;
1194
1195
1196 -- Check for the resend flag value and default it.
1197 IF (p_resend_count > 0) THEN
1198 IF (l_Debug_Level <= 1) THEN
1199 ecx_cln_debug_pub.Add('Resend Flag is set to Y ',1);
1200 END IF;
1201 l_resend_flag := 'Y';
1202 END IF;
1203
1204
1205 -- If Document Owner passed is null
1206 IF ((l_doc_owner IS NULL) OR (l_doc_owner = '')) THEN
1207 IF (l_Debug_Level <= 1) THEN
1208 ecx_cln_debug_pub.Add('Document Owner passed as NULL',1);
1209 END IF;
1210 l_doc_owner := FND_GLOBAL.USER_ID;
1211 IF (l_Debug_Level <= 1) THEN
1212 ecx_cln_debug_pub.Add('Document Owner set as >> '||l_doc_owner,1);
1213 END IF;
1214 END IF;
1215
1216 -- If Collaboration Point passed is null
1217 IF ((l_coll_pt IS NULL) OR (l_coll_pt = '')) THEN
1218 IF (l_Debug_Level <= 1) THEN
1219 ecx_cln_debug_pub.Add('Collaboration Point passed as NULL',1);
1220 END IF;
1221 l_coll_pt := 'APPS';
1222 IF (l_Debug_Level <= 1) THEN
1223 ecx_cln_debug_pub.Add('Collaboration Point set as >> '||l_coll_pt,1);
1224 END IF;
1225 END IF;
1226
1227 -- If RosettaNet Check Reqd value is null
1228 IF (l_rosettanet_check_required IS NULL) THEN
1229 IF (l_Debug_Level <= 1) THEN
1230 ecx_cln_debug_pub.Add('Rosettanet Check Reqd value passed as NULL',1);
1231 END IF;
1232 l_rosettanet_check_required := TRUE;
1233 IF (l_Debug_Level <= 1) THEN
1234 ecx_cln_debug_pub.Add('Rosettanet Check Reqd value set to true',1);
1235 END IF;
1236 END IF;
1237
1238
1239 -- Call the API to get the trading partner set up details
1240 IF (l_Debug_Level <= 1) THEN
1241 ecx_cln_debug_pub.Add('==========Call to GET_TRADING_PARTNER_DETAILS API=============',1);
1242 END IF;
1243 GET_TRADING_PARTNER_DETAILS(
1244 x_return_status => x_return_status,
1245 x_msg_data => x_msg_data,
1246 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
1247 p_xmlg_msg_id => l_xmlg_msg_id,
1248 p_xmlg_transaction_type => l_xmlg_transaction_type,
1249 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
1250 p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
1251 p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
1252 p_xmlg_document_id => l_xmlg_document_id,
1253 p_doc_dir => l_doc_dir,
1254 p_tr_partner_type => l_tr_partner_type,
1255 p_tr_partner_id => l_tr_partner_id,
1256 p_tr_partner_site => l_tr_partner_site,
1257 p_sender_component => l_sender_component,
1258 p_xml_event_key => l_xml_event_key,
1259 p_collaboration_standard => l_collaboration_standard);
1260
1261 IF ( x_return_status <> 'S') THEN
1262 l_msg_data := 'Error in GET_TRADING_PARTNER_DETAILS ';
1263 -- x_msg_data is set to appropriate value by GET_TRADING_PARTNER_DETAILS
1264 RAISE FND_API.G_EXC_ERROR;
1265 END IF;
1266 IF (l_Debug_Level <= 1) THEN
1267 ecx_cln_debug_pub.Add('===========================================',1);
1268
1269
1270 -- call the API to get the default parameters through XMLG settings
1271 ecx_cln_debug_pub.Add('==========Call to DEFAULT_XMLGTXN_MAPPING API=============',1);
1272 END IF;
1273 DEFAULT_XMLGTXN_MAPPING(
1274 x_return_status => x_return_status,
1275 x_msg_data => x_msg_data,
1276 p_xmlg_transaction_type => l_xmlg_transaction_type,
1277 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
1278 p_doc_dir => l_doc_dir,
1279 p_app_id => l_app_id,
1280 p_coll_type => l_coll_type,
1284 l_msg_data := 'Error in DEFAULT_XMLGTXN_MAPPING';
1281 p_doc_type => l_doc_type );
1282
1283 IF ( x_return_status <> 'S') THEN
1285 -- x_msg_data is set to appropriate value by DEFAULT_XMLGTXN_MAPPING
1286 RAISE FND_API.G_EXC_ERROR;
1287 END IF;
1288 IF (l_Debug_Level <= 1) THEN
1289 ecx_cln_debug_pub.Add('===========================================',1);
1290
1291 END IF;
1292
1293 -- Check for required parameters
1294 IF((l_app_id IS NULL) OR (l_doc_dir IS NULL)) THEN
1295 FND_MESSAGE.SET_NAME('CLN','CLN_CH_REQD_PARAMS_MISSING');
1296 FND_MESSAGE.SET_TOKEN('ACTION','create');
1297 x_msg_data := FND_MESSAGE.GET;
1298 l_msg_data := 'Failed to create Collaboration as required parameters Application ID/Document_Direction not found';
1299 RAISE FND_API.G_EXC_ERROR;
1300 END IF;
1301
1302 -- GET THE COLLABORATION STATUS HERE IF NOT PASSED
1303 IF (l_Debug_Level <= 1) THEN
1304 IF (l_Debug_Level <= 1) THEN
1305 ecx_cln_debug_pub.Add('==========Call to DEFAULT_COLLABORATION_STATUS API=============',1);
1306 END IF;
1307
1308 END IF;
1309
1310 IF (l_coll_status IS NULL) THEN
1311 IF (l_Debug_Level <= 1) THEN
1312 ecx_cln_debug_pub.Add('Collaboration Status is NULL',1);
1313 END IF;
1314
1315 DEFAULT_COLLABORATION_STATUS(
1316 x_return_status => x_return_status,
1317 x_msg_data => x_msg_data,
1318 x_coll_status => l_coll_status,
1319 p_app_id => l_app_id,
1320 p_coll_type => l_coll_type,
1321 p_doc_status => 'SUCCESS',
1322 p_doc_type => l_doc_type,
1323 p_doc_dir => l_doc_dir,
1324 p_coll_id => null,
1325 p_coll_standard => l_collaboration_standard
1326 );
1327
1328 IF ( x_return_status <> 'S') THEN
1329 l_msg_data := 'Error in DEFAULT_XMLGTXN_MAPPING';
1330 -- x_msg_data is set to appropriate value by DEFAULT_XMLGTXN_MAPPING
1331 RAISE FND_API.G_EXC_ERROR;
1332 END IF;
1333 END IF;
1334
1335 ecx_cln_debug_pub.Add('===========================================',1);
1336
1337 -- Validation for few parameters by passing parameters to VALIDATE_PARAMS API
1338 VALIDATE_PARAMS(
1339 x_return_status,x_msg_data,l_app_id,l_doc_dir,
1340 l_doc_type,l_coll_status,l_coll_pt,
1341 l_coll_type,null,null);
1342
1343 IF ( x_return_status <> 'S') THEN
1344 l_msg_data := 'Validation of parameters failed';
1345 -- x_msg_data is set to appropriate value by VALIDATE_PARAMS
1346 RAISE FND_API.G_EXC_ERROR;
1347 END IF;
1348
1349
1350 -- RosettaNet Check Required or not.
1351 IF (l_rosettanet_check_required) THEN
1352 IF (l_Debug_Level <= 1) THEN
1353 ecx_cln_debug_pub.Add('RosettaNet Check is Required');
1354 END IF;
1355
1356 -- Check whether collaboration can be created/upadted based on Profile, Protocol value
1357 IS_UPDATE_REQUIRED(
1358 x_return_status, x_msg_data, l_doc_dir, l_xmlg_transaction_type,
1359 l_xmlg_transaction_subtype, l_tr_partner_type, l_tr_partner_id,
1360 l_tr_partner_site, l_sender_component, l_update_reqd );
1361
1362 IF (l_Debug_Level <= 1) THEN
1363 ecx_cln_debug_pub.Add('Status Code Returned By IS_UPDATE_REQUIRED :'||x_return_status,1);
1364 END IF;
1365
1366
1367 IF (x_return_status <> 'S') THEN
1368 FND_MESSAGE.SET_NAME('CLN','CLN_CH_REQD_CRITERIA_FAIL');
1369 x_msg_data := FND_MESSAGE.GET;
1370 l_msg_data :='Failed to verify the required criteria for updating/creating collaboration';
1371 RAISE FND_API.G_EXC_ERROR;
1372 END IF;
1373 IF (l_update_reqd <> TRUE) THEN
1374 IF (l_Debug_Level <= 1) THEN
1375 ecx_cln_debug_pub.Add('Update Reqd as Returned By IS_UPDATE_REQUIRED -FALSE',1);
1376 END IF;
1377
1378 -- x_msg_data is set to appropriate value by IS_UPDATE_REQUIRED
1379 RETURN;
1380 END IF;
1381 END IF;
1382
1383
1384 IF (l_collaboration_standard = 'OAG') and (l_doc_type <> 'CONFIRM_BOD' )
1385 and (l_doc_dir = 'OUT') and (l_ref_id is null)
1386 and (g_xmlg_oag_application_ref_id is not null) THEN
1387 l_ref_id := g_xmlg_oag_application_ref_id;
1388 g_xmlg_oag_application_ref_id := NULL;
1389 END IF;
1390
1391
1392 -- For OutBound Docs, If Ref id is null, we are obtaining it using xml payload parsing
1396 AND l_xmlg_msg_id IS NOT NULL) THEN
1393 -- As an exception, for confirm bod outbound we need not take the reference id
1394 IF(l_ref_id IS NULL AND l_doc_dir = 'OUT'
1395 AND l_doc_type <> 'CONFIRM_BOD'
1397 IF (l_Debug_Level <= 1) THEN
1398 ecx_cln_debug_pub.Add('Application Reference ID is Null',1);
1399 END IF;
1400
1401 GET_CONTROL_AREA_REFID(p_msgId => l_xmlg_msg_id,
1402 p_collaboration_standard => l_collaboration_standard,
1403 x_app_ref_id => l_ref_id,
1404 p_app_id => l_app_id,
1405 p_coll_type => l_coll_type);
1406 END IF;
1407
1408
1409 -- Collaboration ID is generated from a sequence.
1410 SELECT cln_collaboration_hdr_id_s.NEXTVAL INTO x_coll_id FROM dual;
1411 IF (l_Debug_Level <= 1) THEN
1412 ecx_cln_debug_pub.Add('Collaboration Id generated : '||x_coll_id,1);
1413
1414
1415 ecx_cln_debug_pub.Add('----- Before SQL Query : Adding Details in CLN_COLL_HIST_HDR -----',1);
1416 END IF;
1417
1418 IF (l_doc_creation_date is null and l_xmlg_msg_id is not null) THEN -- If collaboration history already doesnt have doc creation date and user hasnt passed it then we have to get it by parsing the payload
1419 Get_document_Creation_date(l_xmlg_msg_id,l_doc_creation_date);
1420 END IF;
1421
1422
1423 --Bug 3655492 : Added nvl(l_coll_type,'UNKNOWN') for 11.5.10 performance enh, to make sure always Collaboration Type is not null
1424 INSERT INTO CLN_COLL_HIST_HDR(
1425 COLLABORATION_ID,APPLICATION_ID,APPLICATION_REFERENCE_ID,ORG_ID,RELEASE_NO,DOCUMENT_NO,
1426 DOC_REVISION_NO,PARTNER_DOCUMENT_NO,COLLABORATION_TYPE,TRADING_PARTNER,RESEND_FLAG,
1427 RESEND_COUNT,DOCUMENT_OWNER,INITIATION_DATE,DOCUMENT_CREATION_DATE,DOCUMENT_REVISION_DATE,
1428 COLLABORATION_STATUS,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
1429 LAST_UPDATE_LOGIN,XMLG_MSG_ID,UNIQUE_ID1,UNIQUE_ID2,UNIQUE_ID3,UNIQUE_ID4,UNIQUE_ID5,
1430 XMLG_TRANSACTION_TYPE, XMLG_TRANSACTION_SUBTYPE,XMLG_DOCUMENT_ID, TRADING_PARTNER_TYPE, TRADING_PARTNER_SITE,
1431 XMLG_INTERNAL_CONTROL_NUMBER, DOCUMENT_DIRECTION, XMLG_INT_TRANSACTION_TYPE, XMLG_INT_TRANSACTION_SUBTYPE,
1432 XML_EVENT_KEY, COLLABORATION_STANDARD,OWNER_ROLE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,
1433 ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
1434 ATTRIBUTE14,ATTRIBUTE15,DATTRIBUTE1,DATTRIBUTE2,DATTRIBUTE3,DATTRIBUTE4,DATTRIBUTE5 )
1435 VALUES( x_coll_id,l_app_id,l_ref_id,p_org_id,p_rel_no,l_doc_no,p_doc_rev_no,
1436 p_partner_doc_no,nvl(l_coll_type,'UNKNOWN'),l_tr_partner_id,l_resend_flag,p_resend_count,
1437 l_doc_owner,nvl(p_init_date,SYSDATE),l_doc_creation_date,p_doc_revision_date,l_coll_status,
1438 SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID,l_xmlg_msg_id,
1439 p_unique1,p_unique2,p_unique3,p_unique4,p_unique5,
1440 l_xmlg_transaction_type, l_xmlg_transaction_subtype, l_xmlg_document_id,l_tr_partner_type, l_tr_partner_site,
1441 l_xmlg_internal_control_number, l_doc_dir, l_xmlg_int_transaction_type, l_xmlg_int_transaction_subtype,
1442 l_xml_event_key, l_collaboration_standard,l_owner_role,p_attribute1,p_attribute2,p_attribute3,p_attribute4,p_attribute5,
1443 p_attribute6,p_attribute7,p_attribute8,p_attribute9,p_attribute10,p_attribute11,p_attribute12,p_attribute13,
1444 p_attribute14,p_attribute15, p_dattribute1,p_dattribute2,p_dattribute3,p_dattribute4,p_dattribute5 );
1445
1446 IF (l_Debug_Level <= 1) THEN
1447 ecx_cln_debug_pub.Add('------ After SQL Query : Adding Details in CLN_COLL_HIST_HDR --------',1);
1448 END IF;
1449
1450
1451
1452 IF SQL%FOUND THEN
1453 IF (l_Debug_Level <= 1) THEN
1454 ecx_cln_debug_pub.Add('Collaboration Details successfully entered in CLN_COLL_HIST_HDR TABLE',1);
1455 END IF;
1456
1457 ELSE
1458 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ADD_DTLS_FAILED');
1459 FND_MESSAGE.SET_TOKEN('TABLE','CLN_COLL_HIST_HDR');
1460 x_msg_data := FND_MESSAGE.GET;
1461 l_msg_data := 'Failed to add Collaboration Details in CLN_COLL_HIST_HDR TABLE';
1462 RAISE FND_API.G_EXC_ERROR;
1463 END IF;
1464
1465 IF(l_msg_text IS NULL) THEN
1466 l_msg_text := 'CLN_CH_COLLABORATION_CREATED';
1467 END IF;
1468
1469
1470 --FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_CREATED');
1471 -- Calling UPDATE_COLLABORATION_INIT API
1472
1473 UPDATE_COLLABORATION_INIT(
1474 x_return_status,x_coll_id,l_doc_type,l_doc_dir,l_coll_pt,l_doc_no,'SUCCESS',null,
1475 l_msg_text, l_xmlg_transaction_type, l_xmlg_transaction_subtype, l_xmlg_document_id,
1476 l_xmlg_msg_id, l_xmlg_internal_control_number, l_resend_flag, l_xmlg_int_transaction_type,
1477 l_xmlg_int_transaction_subtype, l_xml_event_key );
1478
1479 IF (l_Debug_Level <= 1) THEN
1480 ecx_cln_debug_pub.Add('Status Code Returned By UPDATE_COLLABORATION_INIT :'||x_return_status,1);
1481 END IF;
1482
1483
1484 IF x_return_status <> 'S' THEN
1485 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ADD_DTLS_FAILED');
1489 RAISE FND_API.G_EXC_ERROR;
1486 FND_MESSAGE.SET_TOKEN('TABLE','CLN_COLL_HIST_DTL');
1487 x_msg_data := FND_MESSAGE.GET;
1488 l_msg_data := 'Failed to add Collaboration Details in CLN_COLL_HIST_DTL TABLE';
1490 END IF;
1491
1492 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_CREATED');
1493 x_msg_data := FND_MESSAGE.GET;
1494 IF (l_Debug_Level <= 1) THEN
1495 ecx_cln_debug_pub.Add(l_msg_data,1);
1496 END IF;
1497
1498 IF (l_Debug_Level <= 2) THEN
1499 ecx_cln_debug_pub.Add('------- Exiting CREATE_COLLABORATION API ---------- ',2);
1500 END IF;
1501
1502
1503 EXCEPTION
1504
1505 WHEN FND_API.G_EXC_ERROR THEN
1506 ROLLBACK TO CREATE_COLLABORATION_PUB;
1507 x_return_status :=FND_API.G_RET_STS_ERROR ;
1508 IF (l_Debug_Level <= 4) THEN
1509 ecx_cln_debug_pub.Add(l_msg_data,4);
1510 ecx_cln_debug_pub.Add('------- Exiting CREATE_COLLABORATION API ---------- ',2);
1511 END IF;
1512
1513
1514
1515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1516 ROLLBACK TO CREATE_COLLABORATION_PUB;
1517 l_error_code :=SQLCODE;
1518 l_error_msg :=SQLERRM;
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1520 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
1521 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
1522 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
1523 x_msg_data :=FND_MESSAGE.GET;
1524 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
1525 IF (l_Debug_Level <= 5) THEN
1526 ecx_cln_debug_pub.Add(l_msg_data,6);
1527 ecx_cln_debug_pub.Add('------- Exiting CREATE_COLLABORATION API ---------- ',2);
1528 END IF;
1529
1530
1531
1532 WHEN OTHERS THEN
1533 ROLLBACK TO CREATE_COLLABORATION_PUB;
1534 l_error_code :=SQLCODE;
1535 l_error_msg :=SQLERRM;
1536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1537 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
1538 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
1539 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
1540 x_msg_data :=FND_MESSAGE.GET;
1541 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
1542 IF (l_Debug_Level <= 5) THEN
1543 ecx_cln_debug_pub.Add(l_msg_data,4);
1544 ecx_cln_debug_pub.Add('------- Exiting CREATE_COLLABORATION API ---------- ',2);
1545 END IF;
1546
1547 END CREATE_COLLABORATION;
1548
1549
1550
1551 -- Name
1552 -- UPDATE_COLLABORATION
1553 -- Purpose
1554 -- This is the public procedure which is called at subsequent stages after creation,
1555 -- to update collaboration with the progress.It creates a new row in the CLN_COLL_HIST_DTL
1556 -- table and also modifies the CLN_COLL_HIST_HDR if the need may be.
1557 -- Arguments
1558 --
1559 -- Notes
1560 -- No specific notes.
1561
1562
1563 PROCEDURE UPDATE_COLLABORATION(
1564 x_return_status OUT NOCOPY VARCHAR2,
1565 x_msg_data OUT NOCOPY VARCHAR2,
1566 p_coll_id IN NUMBER,
1567 p_app_id IN VARCHAR2,
1568 p_ref_id IN VARCHAR2,
1569 p_rel_no IN VARCHAR2,
1570 p_doc_no IN VARCHAR2,
1571 p_doc_rev_no IN VARCHAR2,
1572 p_xmlg_transaction_type IN VARCHAR2,
1573 p_xmlg_transaction_subtype IN VARCHAR2,
1574 p_xmlg_document_id IN VARCHAR2,
1575 p_resend_flag IN VARCHAR2,
1576 p_resend_count IN NUMBER,
1577 p_disposition IN VARCHAR2,
1578 p_coll_status IN VARCHAR2,
1579 p_doc_type IN VARCHAR2,
1580 p_doc_dir IN VARCHAR2,
1581 p_coll_pt IN VARCHAR2,
1582 p_org_ref IN VARCHAR2,
1583 p_doc_status IN VARCHAR2,
1584 p_notification_id IN VARCHAR2,
1585 p_msg_text IN VARCHAR2,
1586 p_bsr_verb IN VARCHAR2,
1587 p_bsr_noun IN VARCHAR2,
1588 p_bsr_rev IN VARCHAR2,
1589 p_sdr_logical_id IN VARCHAR2,
1590 p_sdr_component IN VARCHAR2,
1591 p_sdr_task IN VARCHAR2,
1592 p_sdr_refid IN VARCHAR2,
1593 p_sdr_confirmation IN VARCHAR2,
1594 p_sdr_language IN VARCHAR2,
1595 p_sdr_codepage IN VARCHAR2,
1596 p_sdr_authid IN VARCHAR2,
1597 p_sdr_datetime_qualifier IN VARCHAR2,
1601 p_attr2 IN VARCHAR2,
1598 p_sdr_datetime IN VARCHAR2,
1599 p_sdr_timezone IN VARCHAR2,
1600 p_attr1 IN VARCHAR2,
1602 p_attr3 IN VARCHAR2,
1603 p_attr4 IN VARCHAR2,
1604 p_attr5 IN VARCHAR2,
1605 p_attr6 IN VARCHAR2,
1606 p_attr7 IN VARCHAR2,
1607 p_attr8 IN VARCHAR2,
1608 p_attr9 IN VARCHAR2,
1609 p_attr10 IN VARCHAR2,
1610 p_attr11 IN VARCHAR2,
1611 p_attr12 IN VARCHAR2,
1612 p_attr13 IN VARCHAR2,
1613 p_attr14 IN VARCHAR2,
1614 p_attr15 IN VARCHAR2,
1615 p_xmlg_msg_id IN VARCHAR2,
1616 p_unique1 IN VARCHAR2,
1617 p_unique2 IN VARCHAR2,
1618 p_unique3 IN VARCHAR2,
1619 p_unique4 IN VARCHAR2,
1620 p_unique5 IN VARCHAR2,
1621 p_tr_partner_type IN VARCHAR2,
1622 p_tr_partner_id IN VARCHAR2,
1623 p_tr_partner_site IN VARCHAR2,
1624 p_sender_component IN VARCHAR2,
1625 p_rosettanet_check_required IN BOOLEAN,
1626 x_dtl_coll_id OUT NOCOPY NUMBER,
1627 p_xmlg_internal_control_number IN NUMBER,
1628 p_partner_doc_no IN VARCHAR2,
1629 p_org_id IN NUMBER,
1630 p_doc_creation_date IN DATE,
1631 p_doc_revision_date IN DATE,
1632 p_doc_owner IN VARCHAR2,
1633 p_xmlg_int_transaction_type IN VARCHAR2,
1634 p_xmlg_int_transaction_subtype IN VARCHAR2,
1635 p_xml_event_key IN VARCHAR2,
1636 p_collaboration_standard IN VARCHAR2,
1637 p_attribute1 IN VARCHAR2,
1638 p_attribute2 IN VARCHAR2,
1639 p_attribute3 IN VARCHAR2,
1640 p_attribute4 IN VARCHAR2,
1641 p_attribute5 IN VARCHAR2,
1642 p_attribute6 IN VARCHAR2,
1643 p_attribute7 IN VARCHAR2,
1644 p_attribute8 IN VARCHAR2,
1645 p_attribute9 IN VARCHAR2,
1646 p_attribute10 IN VARCHAR2,
1647 p_attribute11 IN VARCHAR2,
1648 p_attribute12 IN VARCHAR2,
1649 p_attribute13 IN VARCHAR2,
1650 p_attribute14 IN VARCHAR2,
1651 p_attribute15 IN VARCHAR2,
1652 p_dattribute1 IN DATE,
1653 p_dattribute2 IN DATE,
1654 p_dattribute3 IN DATE,
1655 p_dattribute4 IN DATE,
1656 p_dattribute5 IN DATE,
1657 p_owner_role IN VARCHAR2 )
1658 IS
1659 l_dtl_coll_id NUMBER;
1660 l_coll_id NUMBER;
1661 l_error_code NUMBER;
1662 l_error_msg VARCHAR2(2000);
1663 l_msg_data VARCHAR2(2000);
1664 l_debug_mode VARCHAR2(255);
1665 l_update_reqd BOOLEAN;
1666 l_collaboration_found BOOLEAN;
1667 l_xmlg_internal_control_number NUMBER;
1668 l_xmlg_msg_id VARCHAR2(100);
1669 l_xmlg_transaction_type VARCHAR2(100);
1670 l_xmlg_transaction_subtype VARCHAR2(100);
1671 l_xmlg_int_transaction_type VARCHAR2(100);
1672 l_xmlg_int_transaction_subtype VARCHAR2(100);
1673 l_xmlg_document_id VARCHAR2(256);
1674 l_doc_dir VARCHAR2(240);
1675 l_tr_partner_type VARCHAR2(30);
1676 l_tr_partner_id VARCHAR2(256);
1677 l_tr_partner_site VARCHAR2(256);
1678 l_sender_component VARCHAR2(500);
1679 l_app_id VARCHAR2(10);
1680 l_coll_type VARCHAR2(30);
1681 l_doc_type VARCHAR2(100);
1682 l_resend_flag VARCHAR2(1);
1683 l_coll_status VARCHAR2(10);
1684 l_msg_text VARCHAR2(2000);
1685 l_ref_id VARCHAR2(100);
1686 l_doc_owner VARCHAR2(30);
1687 l_owner_role VARCHAR2(30);
1688 l_coll_pt VARCHAR2(20);
1692 l_collaboration_standard VARCHAR2(30);
1689 l_doc_status VARCHAR2(10);
1690 l_xml_event_key VARCHAR2(240);
1691 l_rosettanet_check_required BOOLEAN;
1693 l_doc_creation_date DATE;
1694
1695
1696 BEGIN
1697
1698 -- Sets the debug mode to be FILE
1699 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
1700
1701
1702 IF (l_Debug_Level <= 2) THEN
1703 ecx_cln_debug_pub.Add('--------- Entering UPDATE_COLLABORATION API ------------ ',2);
1704 END IF;
1705
1706
1707 -- Standard Start of API savepoint
1708 -- SAVEPOINT UPDATE_COLLABORATION_PUB;
1709
1710
1711 -- Initialize API return status to success
1712 x_return_status := FND_API.G_RET_STS_SUCCESS;
1713 l_msg_data := 'Collaboration successfully updated ';
1714
1715
1716 -- get the paramaters passed
1717 IF (l_Debug_Level <= 1) THEN
1718 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
1719 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
1720 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
1721 ecx_cln_debug_pub.Add('REFERENCE ID ----- >>>'||p_ref_id,1);
1722 ecx_cln_debug_pub.Add('RELEASE NUMBER ----- >>>'||p_rel_no,1);
1723 ecx_cln_debug_pub.Add('DOCUMENT NO ----- >>>'||p_doc_no,1);
1724 ecx_cln_debug_pub.Add('DOCUMENT REV. NO ----- >>>'||p_doc_rev_no,1);
1725 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
1726 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
1727 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
1728 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
1729 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
1730 ecx_cln_debug_pub.Add('RESENG FLAG ----- >>>'||p_resend_flag,1);
1731 ecx_cln_debug_pub.Add('RESEND COUNT ----- >>>'||p_resend_count,1);
1732 ecx_cln_debug_pub.Add('DISPOSITION ----- >>>'||p_disposition,1);
1733 ecx_cln_debug_pub.Add('COLLABORATION STATUS ----- >>>'||p_coll_status,1);
1734 ecx_cln_debug_pub.Add('DOCUMENT TYPE ----- >>>'||p_doc_type,1);
1735 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
1736 ecx_cln_debug_pub.Add('COLLABORATION POINT ----- >>>'||p_coll_pt,1);
1737 ecx_cln_debug_pub.Add('ORIGINATOR REFERENCE ----- >>>'||p_org_ref,1);
1738 ecx_cln_debug_pub.Add('DOCUMENT STATUS ----- >>>'||p_doc_status,1);
1739 ecx_cln_debug_pub.Add('NOTIFICATION ID ----- >>>'||p_notification_id,1);
1740 ecx_cln_debug_pub.Add('MESSAGE TEST ----- >>>'||p_msg_text,1);
1741 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
1742 ecx_cln_debug_pub.Add('UNIQUE 1 ----- >>>'||p_unique1,1);
1743 ecx_cln_debug_pub.Add('UNIQUE 2 ----- >>>'||p_unique2,1);
1744 ecx_cln_debug_pub.Add('UNIQUE 3 ----- >>>'||p_unique3,1);
1745 ecx_cln_debug_pub.Add('UNIQUE 4 ----- >>>'||p_unique4,1);
1746 ecx_cln_debug_pub.Add('UNIQUE 5 ----- >>>'||p_unique5,1);
1747 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||p_tr_partner_type,1);
1748 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||p_tr_partner_id,1);
1749 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||p_tr_partner_site,1);
1750 ecx_cln_debug_pub.Add('SENDER COMPONENT ----- >>>'||p_sender_component,1);
1751 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
1752 ecx_cln_debug_pub.Add('PARTNER DOCUMENT NO ----- >>>'||p_partner_doc_no,1);
1753 ecx_cln_debug_pub.Add('ORG ID ----- >>>'||p_org_id,1);
1754 ecx_cln_debug_pub.Add('DOCUMENT CREATION DATE ----- >>>'||p_doc_creation_date,1);
1755 ecx_cln_debug_pub.Add('DOCUMENT REVISION DATE ----- >>>'||p_doc_revision_date,1);
1756 ecx_cln_debug_pub.Add('DOCUMENT OWNER ----- >>>'||p_doc_owner,1);
1757 ecx_cln_debug_pub.Add('OWNER ROLE ----- >>>'||p_owner_role,1);
1758 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
1759 ecx_cln_debug_pub.Add('Collaboration Standard ----- >>>'||p_collaboration_standard,1);
1760 ecx_cln_debug_pub.Add('ATTRIBUTE1 ----- >>>'||p_attribute1,1);
1761 ecx_cln_debug_pub.Add('ATTRIBUTE2 ----- >>>'||p_attribute2,1);
1762 ecx_cln_debug_pub.Add('ATTRIBUTE3 ----- >>>'||p_attribute3,1);
1763 ecx_cln_debug_pub.Add('ATTRIBUTE4 ----- >>>'||p_attribute4,1);
1767 ecx_cln_debug_pub.Add('ATTRIBUTE8 ----- >>>'||p_attribute8,1);
1764 ecx_cln_debug_pub.Add('ATTRIBUTE5 ----- >>>'||p_attribute5,1);
1765 ecx_cln_debug_pub.Add('ATTRIBUTE6 ----- >>>'||p_attribute6,1);
1766 ecx_cln_debug_pub.Add('ATTRIBUTE7 ----- >>>'||p_attribute7,1);
1768 ecx_cln_debug_pub.Add('ATTRIBUTE9 ----- >>>'||p_attribute9,1);
1769 ecx_cln_debug_pub.Add('ATTRIBUTE10 ----- >>>'||p_attribute10,1);
1770 ecx_cln_debug_pub.Add('ATTRIBUTE11 ----- >>>'||p_attribute11,1);
1771 ecx_cln_debug_pub.Add('ATTRIBUTE12 ----- >>>'||p_attribute12,1);
1772 ecx_cln_debug_pub.Add('ATTRIBUTE13 ----- >>>'||p_attribute13,1);
1773 ecx_cln_debug_pub.Add('ATTRIBUTE14 ----- >>>'||p_attribute14,1);
1774 ecx_cln_debug_pub.Add('ATTRIBUTE15 ----- >>>'||p_attribute15,1);
1775 ecx_cln_debug_pub.Add('DATTRIBUTE1 ----- >>>'||p_dattribute1,1);
1776 ecx_cln_debug_pub.Add('DATTRIBUTE2 ----- >>>'||p_dattribute2,1);
1777 ecx_cln_debug_pub.Add('DATTRIBUTE3 ----- >>>'||p_dattribute3,1);
1778 ecx_cln_debug_pub.Add('DATTRIBUTE4 ----- >>>'||p_dattribute4,1);
1779 ecx_cln_debug_pub.Add('DATTRIBUTE5 ----- >>>'||p_dattribute5,1);
1780 ecx_cln_debug_pub.Add('=========================================================',1);
1781 END IF;
1782
1783
1784
1785 -- assigning parameter to local variables
1786 l_xmlg_internal_control_number := p_xmlg_internal_control_number;
1787 l_xmlg_msg_id := p_xmlg_msg_id;
1788 l_xmlg_transaction_type := p_xmlg_transaction_type;
1789 l_xmlg_transaction_subtype := p_xmlg_transaction_subtype;
1790 l_xmlg_int_transaction_type := p_xmlg_int_transaction_type;
1791 l_xmlg_int_transaction_subtype := p_xmlg_int_transaction_subtype;
1792 l_xmlg_document_id := p_xmlg_document_id;
1793 l_doc_dir := p_doc_dir;
1794 l_tr_partner_type := p_tr_partner_type;
1795 l_tr_partner_id := p_tr_partner_id;
1796 l_tr_partner_site := p_tr_partner_site;
1797 l_sender_component := p_sender_component;
1798 l_app_id := p_app_id;
1799 l_doc_type := p_doc_type;
1800 l_coll_status := p_coll_status;
1801 l_msg_text := ltrim(rtrim(p_msg_text));
1802 l_ref_id := p_ref_id;
1803 l_doc_owner := p_doc_owner;
1804 l_owner_role := p_owner_role;
1805 l_coll_pt := p_coll_pt;
1806 l_doc_status := p_doc_status;
1807 l_rosettanet_check_required := p_rosettanet_check_required;
1808 l_xml_event_key := p_xml_event_key;
1809 l_collaboration_standard := P_collaboration_standard;
1810 l_doc_creation_date := p_doc_creation_date;
1811
1812 -- enhancement done to support translation issues //15-Nov-2002
1813 l_msg_text := p_msg_text;
1814
1815 -- Set Default values.
1816 -- Check for the Resend Flag value and default it.
1817 IF (p_resend_count > 0) THEN
1818 l_resend_flag := 'Y';
1819 IF (l_Debug_Level <= 1) THEN
1820 ecx_cln_debug_pub.Add('Resend Flag is set to Y ',1);
1821 END IF;
1822
1823 END IF;
1824
1825 -- If Document Owner passed is null
1826 IF ((l_doc_owner IS NULL) OR (l_doc_owner = '')) THEN
1827 IF (l_Debug_Level <= 1) THEN
1828 ecx_cln_debug_pub.Add('Document Owner passed as NULL',1);
1829 END IF;
1830
1831 l_doc_owner := FND_GLOBAL.USER_ID;
1832 IF (l_Debug_Level <= 1) THEN
1833 ecx_cln_debug_pub.Add('Document Owner set as '||l_doc_owner,1);
1834 END IF;
1835
1836 END IF;
1837
1838 -- If Collaboration Point passed is null
1839 IF ((l_coll_pt IS NULL) OR (l_coll_pt = '')) THEN
1840 IF (l_Debug_Level <= 1) THEN
1841 ecx_cln_debug_pub.Add('Collaboration Point NULL',1);
1842 END IF;
1843
1844 l_coll_pt := 'APPS';
1845 IF (l_Debug_Level <= 1) THEN
1846 ecx_cln_debug_pub.Add('Collaboration Point is -- '||l_coll_pt,1);
1847 END IF;
1848
1849 END IF;
1850
1851 -- If Document Status passed is null
1852 IF ((l_doc_status IS NULL) OR (l_doc_status = '')) THEN
1853 IF (l_Debug_Level <= 1) THEN
1854 ecx_cln_debug_pub.Add('Document Status passed as NULL',1);
1855 END IF;
1856
1857 l_doc_status := 'SUCCESS';
1858 IF (l_Debug_Level <= 1) THEN
1859 ecx_cln_debug_pub.Add('Document Status set as '||l_doc_status,1);
1860 END IF;
1864 -- If RosettaNet Check Reqd value is null
1861
1862 END IF;
1863
1865 IF (l_rosettanet_check_required IS NULL) THEN
1866 IF (l_Debug_Level <= 1) THEN
1867 ecx_cln_debug_pub.Add('Rosettanet Check Reqd value passed as NULL',1);
1868 END IF;
1869
1870 l_rosettanet_check_required := TRUE;
1871 IF (l_Debug_Level <= 1) THEN
1872 ecx_cln_debug_pub.Add('Rosettanet Check Reqd value set to true',1);
1873 END IF;
1874
1875 END IF;
1876
1877 -- call the API to get the trading partner set up details
1878 IF (l_Debug_Level <= 1) THEN
1879 ecx_cln_debug_pub.Add('==========Call to GET_TRADING_PARTNER_DETAILS API=============',1);
1880 END IF;
1881
1882
1883 GET_TRADING_PARTNER_DETAILS(
1884 x_return_status => x_return_status,
1885 x_msg_data => x_msg_data,
1886 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
1887 p_xmlg_msg_id => l_xmlg_msg_id,
1888 p_xmlg_transaction_type => l_xmlg_transaction_type,
1889 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
1890 p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
1891 p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
1892 p_xmlg_document_id => l_xmlg_document_id,
1893 p_doc_dir => l_doc_dir,
1894 p_tr_partner_type => l_tr_partner_type,
1895 p_tr_partner_id => l_tr_partner_id,
1896 p_tr_partner_site => l_tr_partner_site,
1897 p_sender_component => l_sender_component,
1898 p_xml_event_key => l_xml_event_key,
1899 p_collaboration_standard => l_collaboration_standard);
1900
1901 IF ( x_return_status <> 'S') THEN
1902 l_msg_data := 'Error in GET_TRADING_PARTNER_DETAILS ';
1903 -- x_msg_data is set to appropriate value by GET_TRADING_PARTNER_DETAILS
1904 RAISE FND_API.G_EXC_ERROR;
1905 END IF;
1906 IF (l_Debug_Level <= 1) THEN
1907 ecx_cln_debug_pub.Add('===========================================',1);
1908 END IF;
1909
1910
1911
1912
1913 -- call the API to get the default parameters through XMLG settings
1914 IF (l_Debug_Level <= 1) THEN
1915 ecx_cln_debug_pub.Add('==========Call to DEFAULT_XMLGTXN_MAPPING API=============',1);
1916 END IF;
1917
1918 DEFAULT_XMLGTXN_MAPPING(
1919 x_return_status => x_return_status,
1920 x_msg_data => x_msg_data,
1921 p_xmlg_transaction_type => l_xmlg_transaction_type,
1922 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
1923 p_doc_dir => l_doc_dir,
1924 p_app_id => l_app_id,
1925 p_coll_type => l_coll_type,
1926 p_doc_type => l_doc_type );
1927
1928 IF ( x_return_status <> 'S') THEN
1929 l_msg_data := 'Error in DEFAULT_XMLGTXN_MAPPING';
1930 -- x_msg_data is set to appropriate value by DEFAULT_XMLGTXN_MAPPING
1931 RAISE FND_API.G_EXC_ERROR;
1932 END IF;
1933 IF (l_Debug_Level <= 1) THEN
1934 ecx_cln_debug_pub.Add('===========================================',1);
1935 END IF;
1936
1937
1938
1939 -- RosettaNet Check Required or not.
1940 IF (l_rosettanet_check_required ) THEN
1941 IF (l_Debug_Level <= 1) THEN
1942 ecx_cln_debug_pub.Add('RosettaNet Check is Required');
1943 END IF;
1944
1945
1946 -- Check whether collaboration can be created/upadted based on Profile, Protocol value
1947 IS_UPDATE_REQUIRED(
1948 x_return_status, x_msg_data, l_doc_dir, l_xmlg_transaction_type,
1949 l_xmlg_transaction_subtype, l_tr_partner_type, l_tr_partner_id,
1950 l_tr_partner_site, l_sender_component, l_update_reqd );
1951
1952 IF (l_Debug_Level <= 1) THEN
1953 ecx_cln_debug_pub.Add('Status Code Returned By IS_UPDATE_REQUIRED :'||x_return_status,1);
1954 END IF;
1955
1956
1957 IF (x_return_status <> 'S') THEN
1958 FND_MESSAGE.SET_NAME('CLN','CLN_CH_REQD_CRITERIA_FAIL');
1959 x_msg_data := FND_MESSAGE.GET;
1960 l_msg_data:='Failed to verify the required criteria for updating/creating collaboration';
1961 RAISE FND_API.G_EXC_ERROR;
1962 END IF;
1963
1964 IF (l_update_reqd <> TRUE) THEN
1965 IF (l_Debug_Level <= 1) THEN
1966 ecx_cln_debug_pub.Add('Update Reqd as Returned By IS_UPDATE_REQUIRED -FALSE',1);
1967 END IF;
1968
1972 END IF;
1969 -- x_msg_data is set to appropriate value by IS_UPDATE_REQUIRED
1970 RETURN;
1971 END IF;
1973
1974
1975 -- set the message text to default value if found null
1976 IF (l_msg_text IS NULL OR ltrim(rtrim(l_msg_text)) = '') THEN
1977 FND_MESSAGE.SET_NAME('CLN','CLN_CH_DEFAULT_MSG_TXT');
1978 l_msg_text := FND_MESSAGE.GET;
1979 IF (l_Debug_Level <= 1) THEN
1980 ecx_cln_debug_pub.Add('Message Text Value is NULL, Defaulting to : '||l_msg_text,1);
1981 END IF;
1982
1983 END IF;
1984
1985
1986 -- Remove the comma at the last of message if it is there.First trim the message for possible spaces.
1987 l_msg_text := ltrim(rtrim(l_msg_text));
1988 IF ( substr(l_msg_text,-1) )= ',' THEN
1989 l_msg_text:= substr( l_msg_text, 0, length(l_msg_text) - 1);
1990 END IF;
1991
1992
1993 IF (l_Debug_Level <= 1) THEN
1994 ecx_cln_debug_pub.Add('l_ref_id -- '||l_ref_id,1);
1995 ecx_cln_debug_pub.Add('l_doc_dir -- '||l_doc_dir,1);
1996 ecx_cln_debug_pub.Add('l_xmlg_msg_id -- '||l_xmlg_msg_id,1);
1997 ecx_cln_debug_pub.Add('l_doc_type -- '||l_doc_type,1);
1998 END IF;
1999
2000
2001 -- Defaulting Application Reference ID
2002
2003 IF (l_collaboration_standard = 'OAG') and (l_doc_type <> 'CONFIRM_BOD' )
2004 and (l_doc_dir = 'OUT') and (l_ref_id is null)
2005 and (g_xmlg_oag_application_ref_id is not null) THEN
2006 l_ref_id := g_xmlg_oag_application_ref_id;
2007 g_xmlg_oag_application_ref_id := NULL;
2008 END IF;
2009
2010
2011
2012 IF (l_Debug_Level <= 1) THEN
2013 ecx_cln_debug_pub.Add('---- Before the call to GET_REFERENCE_ID Modified-----',1);
2014 END IF;
2015
2016 IF(l_ref_id IS NULL AND l_doc_dir = 'OUT'
2017 AND l_doc_type <> 'CONFIRM_BOD'
2018 AND l_xmlg_msg_id IS NOT NULL) THEN
2019 GET_CONTROL_AREA_REFID(p_msgId => l_xmlg_msg_id,
2020 p_collaboration_standard => l_collaboration_standard,
2021 x_app_ref_id => l_ref_id,
2022 p_app_id => l_app_id,
2023 p_coll_type => l_coll_type);
2024 END IF;
2025 IF (l_Debug_Level <= 1) THEN
2026 ecx_cln_debug_pub.Add('Application reference id -- ' || l_ref_id, 1);
2027 END IF;
2028
2029
2030
2031 --Assign the value of collaboration id to a local variable
2032 l_coll_id :=p_coll_id;
2033
2034 -- Retrieving Collaboration ID incase the value supplied by user is null
2035 IF l_coll_id IS NULL THEN
2036 IF (l_Debug_Level <= 1) THEN
2037 ecx_cln_debug_pub.Add('Collaboration ID passed as null',1);
2038 ecx_cln_debug_pub.Add('==========Call to FIND_COLLABORATION_ID API=============',1);
2039 END IF;
2040
2041 FIND_COLLABORATION_ID(
2042 x_return_status => x_return_status,
2043 x_msg_data => x_msg_data,
2044 x_coll_id => l_coll_id,
2045 p_app_id => l_app_id,
2046 p_coll_type => l_coll_type,
2047 p_ref_id => l_ref_id,
2048 p_xmlg_transaction_type => l_xmlg_transaction_type,
2049 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
2050 p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
2051 p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
2052 p_tr_partner_type => l_tr_partner_type,
2053 p_tr_partner_id => l_tr_partner_id,
2054 p_tr_partner_site => l_tr_partner_site,
2055 p_xmlg_document_id => l_xmlg_document_id,
2056 p_doc_dir => l_doc_dir,
2057 p_xmlg_msg_id => l_xmlg_msg_id,
2058 p_unique1 => p_unique1,
2059 p_unique2 => p_unique2,
2060 p_unique3 => p_unique3,
2061 p_unique4 => p_unique4,
2062 p_unique5 => p_unique5,
2063 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
2064 p_xml_event_key => l_xml_event_key);
2065
2066 IF ( x_return_status <> 'S') THEN
2067 l_msg_data := 'Error in FIND_COLLABORATION_ID - ' || x_msg_data;
2068 -- x_msg_data is set to appropriate value by FIND_COLLABORATION_ID
2069 RAISE FND_API.G_EXC_ERROR;
2070 END IF;
2071
2072 END IF;
2073
2074 IF l_coll_id IS NULL THEN
2075 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_NOT_FOUND');
2076 x_msg_data := FND_MESSAGE.GET;
2080 -- If Application ID or Collaboration Type is null (this may be the case when the
2077 RAISE FND_API.G_EXC_ERROR;
2078 END IF;
2079
2081 -- document type is CONFIRM_BOD ), we need to get these values from the collaboration
2082 -- history for these are reqd to get the default status.
2083
2084
2085 IF (l_Debug_Level <= 1) THEN
2086 ecx_cln_debug_pub.Add('--- Before the query to find the Application ID/Collaboration Type----',1);
2087 END IF;
2088
2089 SELECT APPLICATION_ID, COLLABORATION_TYPE, DOCUMENT_CREATION_DATE
2090 INTO l_app_id, l_coll_type, l_doc_creation_date
2091 FROM CLN_COLL_HIST_HDR
2092 WHERE COLLABORATION_ID = l_coll_id;
2093
2094 IF (l_Debug_Level <= 1) THEN
2095 ecx_cln_debug_pub.Add('--- After the query to find the Application ID/Collaboration Type----',1);
2096 ecx_cln_debug_pub.Add('Application ID obtained as - '||l_app_id,1);
2097 ecx_cln_debug_pub.Add('Collaboration Type obtained as - '||l_coll_type,1);
2098 END IF;
2099
2100 IF( p_doc_creation_date is not null) THEN
2101 l_doc_creation_date := p_doc_creation_date;
2102 ELSIF (l_doc_creation_date is null and l_xmlg_msg_id is not null) THEN -- If collaboration history already doesnt have doc creation date and user hasnt passed it then we have to get it by parsing the payload
2103 Get_document_Creation_date(l_xmlg_msg_id,l_doc_creation_date);
2104 END IF;
2105
2106
2107
2108 -- GET THE COLLABORATION STATUS HERE IF NOT PASSED
2109 IF (l_Debug_Level <= 1) THEN
2110 ecx_cln_debug_pub.Add('==========Call to DEFAULT_COLLABORATION_STATUS API=============',1);
2111 END IF;
2112
2113
2114 IF (l_coll_status IS NULL) THEN
2115 IF (l_Debug_Level <= 1) THEN
2116 ecx_cln_debug_pub.Add('Collaboration Status is NULL',1);
2117 END IF;
2118
2119 DEFAULT_COLLABORATION_STATUS(
2120 x_return_status => x_return_status,
2121 x_msg_data => x_msg_data,
2122 x_coll_status => l_coll_status,
2123 p_app_id => l_app_id,
2124 p_coll_type => l_coll_type,
2125 p_doc_status => l_doc_status,
2126 p_doc_type => l_doc_type,
2127 p_doc_dir => l_doc_dir,
2128 p_coll_id => l_coll_id,
2129 p_coll_standard => l_collaboration_standard
2130 );
2131
2132 IF ( x_return_status <> 'S') THEN
2133 l_msg_data := 'Error in DEFAULT_XMLGTXN_MAPPING';
2134 -- x_msg_data is set to appropriate value by DEFAULT_XMLGTXN_MAPPING
2135 RAISE FND_API.G_EXC_ERROR;
2136 END IF;
2137 END IF;
2138
2139 IF (l_Debug_Level <= 1) THEN
2140 ecx_cln_debug_pub.Add('===========================================',1);
2141 END IF;
2142
2143
2144 -- Check for required parameters
2145 IF((l_coll_status IS NULL) OR (l_doc_dir IS NULL)) THEN
2146 FND_MESSAGE.SET_NAME('CLN','CLN_CH_REQD_PARAMS_MISSING');
2147 FND_MESSAGE.SET_TOKEN('ACTION','update');
2148 x_msg_data := FND_MESSAGE.GET;
2149 l_msg_data := 'Failed to update Collaboration as required parameters Collaboration Status / Document Direction not found';
2150 RAISE FND_API.G_EXC_ERROR;
2151 END IF;
2152
2153 -- Validation for few parameters by passing parameters to VALIDATE_PARAMS API
2154 VALIDATE_PARAMS(
2155 x_return_status,x_msg_data,l_app_id,l_doc_dir,
2156 l_doc_type,l_coll_status,l_coll_pt,
2157 null,null,p_disposition);
2158
2159 IF ( x_return_status <> 'S') THEN
2160 IF (l_Debug_Level <= 1) THEN
2161 ecx_cln_debug_pub.Add('Validation of parameters failed',1);
2162 END IF;
2163
2164 -- x_msg_data is set to appropriate value by VALIDATE_PARAMS
2165 RAISE FND_API.G_EXC_ERROR;
2166 END IF;
2167
2168
2169 IF (l_Debug_Level <= 1) THEN
2170 ecx_cln_debug_pub.Add('--- Before SQL Query : Updating CLN_COLL_HIST_HDR ---',1);
2171 END IF;
2172
2173
2174 UPDATE CLN_COLL_HIST_HDR
2175 SET APPLICATION_ID = NVL(l_app_id,APPLICATION_ID),
2176 APPLICATION_REFERENCE_ID = NVL(l_ref_id,APPLICATION_REFERENCE_ID),
2177 RELEASE_NO = NVL(p_rel_no,RELEASE_NO),
2178 DOC_REVISION_NO = NVL(p_doc_rev_no,DOC_REVISION_NO),
2179 RESEND_FLAG = NVL(l_resend_flag,RESEND_FLAG),
2180 RESEND_COUNT = NVL(p_resend_count,RESEND_COUNT),
2181 DISPOSITION = NVL(p_disposition,DISPOSITION),
2182 COLLABORATION_STATUS = NVL(l_coll_status,COLLABORATION_STATUS),
2183 DOCUMENT_NO = NVL(p_doc_no,DOCUMENT_NO),
2187 XMLG_INT_TRANSACTION_TYPE = NVL(l_xmlg_int_transaction_type,XMLG_INT_TRANSACTION_TYPE),
2184 XMLG_MSG_ID = NVL(l_xmlg_msg_id,XMLG_MSG_ID),
2185 XMLG_TRANSACTION_TYPE = NVL(l_xmlg_transaction_type,XMLG_TRANSACTION_TYPE),
2186 XMLG_TRANSACTION_SUBTYPE = NVL(l_xmlg_transaction_subtype,XMLG_TRANSACTION_SUBTYPE),
2188 XMLG_INT_TRANSACTION_SUBTYPE = NVL(l_xmlg_int_transaction_subtype,XMLG_INT_TRANSACTION_SUBTYPE),
2189 XMLG_DOCUMENT_ID = NVL(l_xmlg_document_id,XMLG_DOCUMENT_ID),
2190 UNIQUE_ID1 = NVL(p_unique1,UNIQUE_ID1),
2191 UNIQUE_ID2 = NVL(p_unique2,UNIQUE_ID2),
2192 UNIQUE_ID3 = NVL(p_unique3,UNIQUE_ID3),
2193 UNIQUE_ID4 = NVL(p_unique4,UNIQUE_ID4),
2194 UNIQUE_ID5 = NVL(p_unique5,UNIQUE_ID5),
2195 XMLG_INTERNAL_CONTROL_NUMBER = NVL(l_xmlg_internal_control_number,XMLG_INTERNAL_CONTROL_NUMBER),
2196 DOCUMENT_DIRECTION = NVL(l_doc_dir,DOCUMENT_DIRECTION),
2197 PARTNER_DOCUMENT_NO = NVL(p_partner_doc_no,PARTNER_DOCUMENT_NO),
2198 ORG_ID = NVL(p_org_id,ORG_ID),
2199 DOCUMENT_CREATION_DATE = NVL(DOCUMENT_CREATION_DATE,l_doc_creation_date),
2200 DOCUMENT_REVISION_DATE = NVL(p_doc_revision_date,DOCUMENT_REVISION_DATE),
2201 DOCUMENT_OWNER = NVL(l_doc_owner,DOCUMENT_OWNER),
2202 XML_EVENT_KEY = NVL(l_xml_event_key, XML_EVENT_KEY),
2203 COLLABORATION_STANDARD = NVL(p_collaboration_standard,NVL(COLLABORATION_STANDARD,l_collaboration_standard)),
2204 ATTRIBUTE1 = NVL(p_attribute1 ,ATTRIBUTE1),
2205 ATTRIBUTE2 = NVL(p_attribute2 ,ATTRIBUTE2),
2206 ATTRIBUTE3 = NVL(p_attribute3 ,ATTRIBUTE3),
2207 ATTRIBUTE4 = NVL(p_attribute4 ,ATTRIBUTE4),
2208 ATTRIBUTE5 = NVL(p_attribute5 ,ATTRIBUTE5),
2209 ATTRIBUTE6 = NVL(p_attribute6 ,ATTRIBUTE6),
2210 ATTRIBUTE7 = NVL(p_attribute7 ,ATTRIBUTE7),
2211 ATTRIBUTE8 = NVL(p_attribute8 ,ATTRIBUTE8),
2212 ATTRIBUTE9 = NVL(p_attribute9 ,ATTRIBUTE9),
2213 ATTRIBUTE10 = NVL(p_attribute10 ,ATTRIBUTE10),
2214 ATTRIBUTE11 = NVL(p_attribute11 ,ATTRIBUTE11),
2215 ATTRIBUTE12 = NVL(p_attribute12 ,ATTRIBUTE12),
2216 ATTRIBUTE13 = NVL(p_attribute13 ,ATTRIBUTE13),
2217 ATTRIBUTE14 = NVL(p_attribute14 ,ATTRIBUTE14),
2218 ATTRIBUTE15 = NVL(p_attribute15 ,ATTRIBUTE15),
2219 DATTRIBUTE1 = NVL(p_dattribute1 ,DATTRIBUTE1),
2220 DATTRIBUTE2 = NVL(p_dattribute2 ,DATTRIBUTE2),
2221 DATTRIBUTE3 = NVL(p_dattribute3 ,DATTRIBUTE3),
2222 DATTRIBUTE4 = NVL(p_dattribute4 ,DATTRIBUTE4),
2223 DATTRIBUTE5 = NVL(p_dattribute5 ,DATTRIBUTE5),
2224 OWNER_ROLE = NVL(p_owner_role,OWNER_ROLE)
2225 WHERE (COLLABORATION_ID = l_coll_id);
2226 /* Note on collaboraiton standard update in the above query
2227 ------------------------------------------------------------
2228 1. The criteria is, if caller passes collaboration standard to the API explicitely,
2229 then the column has to be updated with that value.
2230 2. If caller doesnt pass and if the column is already filled in,
2231 then the value of the column should be unchanged
2232 3. If caller doesnt pass and if the column is empty,
2233 then the value obtained by us from xml gateway tables needs to be filled in
2234 ===========================================================================*/
2235
2236 IF (l_Debug_Level <= 1) THEN
2237 ecx_cln_debug_pub.Add('--- After SQL Query : Updating CLN_COLL_HIST_HDR ---',1);
2238 END IF;
2239
2240
2241
2242 IF SQL%FOUND THEN
2243 IF (l_Debug_Level <= 1) THEN
2244 ecx_cln_debug_pub.Add('Collaboration Details successfully updated in CLN_COLL_HIST_HDR TABLE',1);
2245 END IF;
2246
2247 ELSE
2248 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_NOT_FOUND');
2249 x_msg_data := FND_MESSAGE.GET;
2250 l_msg_data := 'Unable to find the collaboration in Collaboration History';
2251 RAISE FND_API.G_EXC_ERROR;
2252 END IF;
2253
2254
2255 -- Collaboration Detail ID is generated from a sequence.
2256 SELECT cln_collaboration_dtl_id_s.nextval INTO l_dtl_coll_id FROM dual ;
2257 IF (l_Debug_Level <= 1) THEN
2261 x_dtl_coll_id := l_dtl_coll_id;
2258 ecx_cln_debug_pub.Add('Collaboration Detail ID generated : '||l_dtl_coll_id,1);
2259 END IF;
2260
2262
2263 IF (l_Debug_Level <= 1) THEN
2264 ecx_cln_debug_pub.Add('---- Before SQL Query : Adding Details in CLN_COLL_HIST_DTL ----',1);
2265 END IF;
2266
2267 --Bug 3655492 : Added nvl(l_doc_type,'UNKNOWN') for 11.5.10 performance enh, to make sure always Doc Type is not null
2268 INSERT INTO CLN_COLL_HIST_DTL(
2269 COLLABORATION_DTL_ID, COLLABORATION_ID,
2270 COLLABORATION_DOCUMENT_TYPE, DOCUMENT_DIRECTION, COLLABORATION_POINT,
2271 ORIGINATOR_REFERENCE, DOCUMENT_STATUS, NOTIFICATION_ID, MESSAGE_TEXT,
2272 BSR_VERB, BSR_NOUN, BSR_REVISION,SENDER_LOGICAL_ID,SENDER_COMPONENT,
2273 SENDER_TASK,SENDER_REFERENCEID,SENDER_CONFIRMATION,SENDER_LANGUAGE,
2274 SENDER_CODEPAGE, SENDER_AUTHID, SENDER_DATETIME_QUALIFIER,
2275 SENDER_DATETIME,SENDER_TIMEZONE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,
2276 ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9, ATTRIBUTE10,
2277 ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15, CREATION_DATE,CREATED_BY,
2278 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,XMLG_TRANSACTION_TYPE,
2279 XMLG_TRANSACTION_SUBTYPE, XMLG_DOCUMENT_ID, XMLG_MSG_ID, XMLG_INTERNAL_CONTROL_NUMBER,
2280 RESEND_FLAG, XMLG_INT_TRANSACTION_TYPE, XMLG_INT_TRANSACTION_SUBTYPE, XML_EVENT_KEY )
2281 VALUES( x_dtl_coll_id,l_coll_id,nvl(l_doc_type,'UNKNOWN'),l_doc_dir,
2282 l_coll_pt,p_org_ref,l_doc_status,
2283 p_notification_id,l_msg_text,p_bsr_verb,p_bsr_noun,p_bsr_rev,p_sdr_logical_id,
2284 p_sdr_component,p_sdr_task,p_sdr_refid,p_sdr_confirmation,p_sdr_language,p_sdr_codepage,
2285 p_sdr_authid,p_sdr_datetime_qualifier,p_sdr_datetime,p_sdr_timezone,p_attr1,p_attr2,p_attr3,
2286 p_attr4,p_attr5,p_attr6,p_attr7,p_attr8,p_attr9,p_attr10,
2287 p_attr11,p_attr12,p_attr13,p_attr14,p_attr15,SYSDATE,FND_GLOBAL.USER_ID,
2288 SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID, l_xmlg_transaction_type,
2289 l_xmlg_transaction_subtype, l_xmlg_document_id, l_xmlg_msg_id, l_xmlg_internal_control_number,
2290 l_resend_flag, l_xmlg_int_transaction_type, l_xmlg_int_transaction_subtype, l_xml_event_key);
2291
2292 IF (l_Debug_Level <= 1) THEN
2293 ecx_cln_debug_pub.Add('----- After SQL Query : Adding Details in CLN_COLL_HIST_DTL -----',1);
2294 END IF;
2295
2296
2297 IF SQL%FOUND THEN
2298 IF (l_Debug_Level <= 1) THEN
2299 ecx_cln_debug_pub.Add('Collaboration Details successfully entered in CLN_COLL_HIST_DTL TABLE',1);
2300 END IF;
2301
2302 ELSE
2303 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ADD_DTLS_FAILED');
2304 FND_MESSAGE.SET_TOKEN('TABLE','CLN_COLL_HIST_DTL');
2305 x_msg_data := FND_MESSAGE.GET;
2306 l_msg_data := 'Failed to add Collaboration Details in CLN_COLL_HIST_DTL TABLE';
2307 RAISE FND_API.G_EXC_ERROR;
2308 END IF;
2309
2310
2311 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_UPDATED');
2312 x_msg_data := FND_MESSAGE.GET;
2313 IF (l_Debug_Level <= 1) THEN
2314 ecx_cln_debug_pub.Add(l_msg_data,1);
2315 END IF;
2316
2317 IF (l_Debug_Level <= 2) THEN
2318 ecx_cln_debug_pub.Add('------ Exiting UPDATE_COLLABORATION API ------- ',2);
2319 END IF;
2320
2321
2322 EXCEPTION
2323
2324 WHEN FND_API.G_EXC_ERROR THEN
2325 --ROLLBACK TO UPDATE_COLLABORATION_PUB;
2326 x_return_status := FND_API.G_RET_STS_ERROR ;
2327 IF (l_Debug_Level <= 4) THEN
2328 IF (l_Debug_Level <= 4) THEN
2329 ecx_cln_debug_pub.Add(l_msg_data,4);
2330 ecx_cln_debug_pub.Add('------ Exiting UPDATE_COLLABORATION API ------- ',2);
2331 END IF;
2332
2333 END IF;
2334
2335
2336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2337 --ROLLBACK TO UPDATE_COLLABORATION_PUB;
2338 l_error_code :=SQLCODE;
2339 l_error_msg :=SQLERRM;
2340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2341 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
2342 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2343 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2344 x_msg_data :=FND_MESSAGE.GET;
2345 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
2346 IF (l_Debug_Level <= 5) THEN
2347 ecx_cln_debug_pub.Add(l_msg_data,6);
2348 ecx_cln_debug_pub.Add('------ Exiting UPDATE_COLLABORATION API ------- ',2);
2349 END IF;
2350
2351
2352
2353 WHEN OTHERS THEN
2354 --ROLLBACK TO UPDATE_COLLABORATION_PUB;
2355 l_error_code :=SQLCODE;
2356 l_error_msg :=SQLERRM;
2357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2358 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
2359 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2360 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2361 x_msg_data :=FND_MESSAGE.GET;
2365 ecx_cln_debug_pub.Add('------ Exiting UPDATE_COLLABORATION API ------- ',2);
2362 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
2363 IF (l_Debug_Level <= 5) THEN
2364 ecx_cln_debug_pub.Add(l_msg_data,4);
2366 END IF;
2367
2368
2369 END UPDATE_COLLABORATION;
2370
2371
2372
2373 -- Name
2374 -- FIND_COLLABORATION_STATUS
2375 -- Purpose
2376 -- This is the public procedure which may be called by the user to
2377 -- know the status of any Collaboration.
2378 -- Arguments
2379 --
2380 -- Notes
2381 -- No specific notes.
2382
2383
2384 PROCEDURE FIND_COLLABORATION_STATUS(
2385 x_return_status OUT NOCOPY VARCHAR2,
2386 x_msg_data OUT NOCOPY VARCHAR2,
2387 p_coll_id IN NUMBER,
2388 p_app_id IN VARCHAR2,
2389 p_ref_id IN VARCHAR2,
2390 p_rel_no IN VARCHAR2,
2391 p_doc_no IN VARCHAR2,
2392 p_doc_rev_no IN VARCHAR2,
2393 p_xmlg_transaction_type IN VARCHAR2,
2394 p_xmlg_transaction_subtype IN VARCHAR2,
2395 p_xmlg_document_id IN VARCHAR2,
2396 x_coll_status OUT NOCOPY VARCHAR2,
2397 p_unique1 IN VARCHAR2,
2398 p_unique2 IN VARCHAR2,
2399 p_unique3 IN VARCHAR2,
2400 p_unique4 IN VARCHAR2,
2401 p_unique5 IN VARCHAR2,
2402 p_doc_direction IN VARCHAR2,
2403 p_xmlg_msg_id IN VARCHAR2,
2404 p_xmlg_internal_control_number IN NUMBER )
2405
2406 IS
2407 l_error_code NUMBER;
2408 l_error_msg VARCHAR2(2000);
2409 l_msg_data VARCHAR2(2000);
2410 l_debug_mode VARCHAR2(255);
2411
2412 BEGIN
2413
2414 -- Sets the debug mode to be FILE
2415 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
2416
2417
2418 IF (l_Debug_Level <= 2) THEN
2419 ecx_cln_debug_pub.Add('----- Entering FIND_COLLABORATION_STATUS API -----',2);
2420 END IF;
2421
2422
2423 -- Initialize API return status to success
2424 x_return_status := FND_API.G_RET_STS_SUCCESS;
2425 l_msg_data := 'Collaboration status successfully found ';
2426
2427 -- get the paramaters passed
2428 IF (l_Debug_Level <= 1) THEN
2429 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
2430 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
2431 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
2432 ecx_cln_debug_pub.Add('REFERENCE ID ----- >>>'||p_ref_id,1);
2433 ecx_cln_debug_pub.Add('RELEASE NUMBER ----- >>>'||p_rel_no,1);
2434 ecx_cln_debug_pub.Add('DOCUMENT NO ----- >>>'||p_doc_no,1);
2435 ecx_cln_debug_pub.Add('DOCUMENT REV. NO ----- >>>'||p_doc_rev_no,1);
2436 ecx_cln_debug_pub.Add('XMLG TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
2437 ecx_cln_debug_pub.Add('XMLG TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
2438 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
2439 ecx_cln_debug_pub.Add('UNIQUE 1 ----- >>>'||p_unique1,1);
2440 ecx_cln_debug_pub.Add('UNIQUE 2 ----- >>>'||p_unique2,1);
2441 ecx_cln_debug_pub.Add('UNIQUE 3 ----- >>>'||p_unique3,1);
2442 ecx_cln_debug_pub.Add('UNIQUE 4 ----- >>>'||p_unique4,1);
2443 ecx_cln_debug_pub.Add('UNIQUE 5 ----- >>>'||p_unique5,1);
2444 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_direction,1);
2445 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
2446 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
2447 ecx_cln_debug_pub.Add('===========================================',1);
2448
2449 ecx_cln_debug_pub.Add('--- Before SQL Query : Retrieving Collaboration Status ---',1);
2450 END IF;
2451
2452
2453 SELECT COLLABORATION_STATUS INTO x_coll_status FROM CLN_COLL_HIST_HDR
2454 WHERE (COLLABORATION_ID = p_coll_id)
2455 OR (APPLICATION_REFERENCE_ID = p_ref_id)
2456 OR (APPLICATION_ID = p_app_id
2457 AND UNIQUE_ID1 = p_unique1)
2458 OR (APPLICATION_ID = p_app_id
2459 AND UNIQUE_ID2 = p_unique2)
2460 OR (APPLICATION_ID = p_app_id
2461 AND UNIQUE_ID3 = p_unique3)
2462 OR (APPLICATION_ID = p_app_id
2466 OR XMLG_MSG_ID = p_xmlg_msg_id
2463 AND UNIQUE_ID4 = p_unique4)
2464 OR (APPLICATION_ID = p_app_id
2465 AND UNIQUE_ID5 = p_unique5)
2467 OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number;
2468
2469 IF (l_Debug_Level <= 1) THEN
2470 ecx_cln_debug_pub.Add('--- After SQL Query : Retrieving Collaboration Status ----',1);
2471 END IF;
2472
2473
2474 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_STATUS_FOUND');
2475 x_msg_data := FND_MESSAGE.GET;
2476
2477 IF (l_Debug_Level <= 1) THEN
2478 ecx_cln_debug_pub.Add(l_msg_data,1);
2479 END IF;
2480
2481 IF (l_Debug_Level <= 2) THEN
2482 ecx_cln_debug_pub.Add('----- Exiting FIND_COLLABORATION_STATUS API -----',2);
2483 END IF;
2484
2485
2486
2487 EXCEPTION
2488
2489 WHEN FND_API.G_EXC_ERROR THEN
2490 l_error_code :=SQLCODE;
2491 l_error_msg :=SQLERRM;
2492 x_return_status := FND_API.G_RET_STS_ERROR ;
2493 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_STATUS_NOT_FOUND');
2494 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2495 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2496 x_msg_data := FND_MESSAGE.GET;
2497 l_msg_data :='Collaboration status could not be retrieved for the parameters passed '||l_error_code||' : '||l_error_msg;
2498 IF (l_Debug_Level <= 4) THEN
2499 ecx_cln_debug_pub.Add(l_msg_data,4);
2500 ecx_cln_debug_pub.Add('----- Exiting FIND_COLLABORATION_STATUS API -----',2);
2501 END IF;
2502
2503
2504
2505 WHEN NO_DATA_FOUND THEN
2506 IF (l_Debug_Level <= 5) THEN
2507 ecx_cln_debug_pub.Add('Unable to find the collaboration status in Collaboration History - Header Table',1);
2508 ecx_cln_debug_pub.Add('----- Finding Collaboration status using CLN_COLL_HIST_DTL table ----',1);
2509 END IF;
2510
2511 BEGIN
2512 SELECT COLLABORATION_STATUS INTO x_coll_status
2513 FROM CLN_COLL_HIST_HDR hdr, CLN_COLL_HIST_DTL dtl
2514 WHERE hdr.COLLABORATION_ID = dtl.COLLABORATION_ID
2515 AND ( dtl.XMLG_MSG_ID = p_xmlg_msg_id
2516 OR dtl.XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number
2517 )
2518 AND ROWNUM < 2;
2519
2520 EXCEPTION
2521 WHEN NO_DATA_FOUND THEN
2522 IF (l_Debug_Level <= 1) THEN
2523 ecx_cln_debug_pub.Add('Unable to find the collaboration status in Collaboration History - Detail Table',1);
2524 END IF;
2525
2526 l_error_code :=SQLCODE;
2527 l_error_msg :=SQLERRM;
2528 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR ;
2529 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_STATUS_NOT_FOUND');
2530 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2531 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2532 x_msg_data := FND_MESSAGE.GET;
2533 l_msg_data :='Collaboration status could not be retrieved for the parameters passed '||l_error_code||' : '||l_error_msg;
2534 END;
2535 IF (l_Debug_Level <= 1) THEN
2536 ecx_cln_debug_pub.Add(l_msg_data,1);
2537 END IF;
2538
2539 IF (l_Debug_Level <= 2) THEN
2540 ecx_cln_debug_pub.Add('----- Exiting FIND_COLLABORATION_STATUS API -----',2);
2541 END IF;
2542
2543
2544
2545 WHEN OTHERS THEN
2546 l_error_code :=SQLCODE;
2547 l_error_msg :=SQLERRM;
2548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2549 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_STATUS_NOT_FOUND');
2550 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2551 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2552 x_msg_data := FND_MESSAGE.GET;
2553 l_msg_data :='Collaboration status could not be retrieved for the parameters passed '||l_error_code||' : '||l_error_msg;
2554 IF (l_Debug_Level <= 5) THEN
2555 ecx_cln_debug_pub.Add(l_msg_data,6);
2556 ecx_cln_debug_pub.Add('----- Exiting FIND_COLLABORATION_STATUS API -----',2);
2557 END IF;
2558
2559
2560
2561 END FIND_COLLABORATION_STATUS;
2562
2563
2564
2565
2566 -- Name
2567 -- RETRIEVE_COLLABORATION_DETAILS
2568 -- Purpose
2569 -- This is the public procedure which may be called to retrieve the details of any
2570 -- collaboration.
2571 -- Arguments
2572 --
2573 -- Notes
2574 -- No specific notes.
2575
2576 PROCEDURE RETRIEVE_COLLABORATION_DETAILS(
2577 x_return_status OUT NOCOPY VARCHAR2,
2581 x_app_id IN OUT NOCOPY VARCHAR2,
2578 x_msg_data OUT NOCOPY VARCHAR2,
2579 p_dtl_coll_id IN NUMBER,
2580 p_coll_id IN NUMBER,
2582 x_ref_id IN OUT NOCOPY VARCHAR2,
2583 x_rel_no IN OUT NOCOPY VARCHAR2,
2584 x_doc_no IN OUT NOCOPY VARCHAR2,
2585 x_doc_rev_no IN OUT NOCOPY VARCHAR2,
2586 p_xmlg_transaction_type IN OUT NOCOPY VARCHAR2,
2587 p_xmlg_transaction_subtype IN OUT NOCOPY VARCHAR2,
2588 p_xmlg_document_id IN OUT NOCOPY VARCHAR2,
2589 x_resend_flag OUT NOCOPY VARCHAR2,
2590 x_resend_count OUT NOCOPY NUMBER,
2591 x_disposition OUT NOCOPY VARCHAR2,
2592 x_coll_status OUT NOCOPY VARCHAR2,
2593 x_org_id OUT NOCOPY NUMBER,
2594 x_tr_partner_id OUT NOCOPY VARCHAR2,
2595 x_doc_owner OUT NOCOPY VARCHAR2,
2596 x_init_date OUT NOCOPY DATE,
2597 x_doc_creation_date OUT NOCOPY DATE,
2598 x_doc_revision_date OUT NOCOPY DATE,
2599 x_doc_type IN OUT NOCOPY VARCHAR2,
2600 x_doc_dir IN OUT NOCOPY VARCHAR2,
2601 x_coll_pt IN OUT NOCOPY VARCHAR2,
2602 x_org_ref OUT NOCOPY VARCHAR2,
2603 x_doc_status OUT NOCOPY VARCHAR2,
2604 x_notification_id OUT NOCOPY VARCHAR2,
2605 x_msg_text OUT NOCOPY VARCHAR2,
2606 x_bsr_verb OUT NOCOPY VARCHAR2,
2607 x_bsr_noun OUT NOCOPY VARCHAR2,
2608 x_bsr_rev OUT NOCOPY VARCHAR2,
2609 x_sdr_logical_id OUT NOCOPY VARCHAR2,
2610 x_sdr_component OUT NOCOPY VARCHAR2,
2611 x_sdr_task OUT NOCOPY VARCHAR2,
2612 x_sdr_refid OUT NOCOPY VARCHAR2,
2613 x_sdr_confirmation OUT NOCOPY VARCHAR2,
2614 x_sdr_language OUT NOCOPY VARCHAR2,
2615 x_sdr_codepage OUT NOCOPY VARCHAR2,
2616 x_sdr_authid OUT NOCOPY VARCHAR2,
2617 x_sdr_datetime_qualifier OUT NOCOPY VARCHAR2,
2618 x_sdr_datetime OUT NOCOPY VARCHAR2,
2619 x_sdr_timezone OUT NOCOPY VARCHAR2,
2620 x_attr1 OUT NOCOPY VARCHAR2,
2621 x_attr2 OUT NOCOPY VARCHAR2,
2622 x_attr3 OUT NOCOPY VARCHAR2,
2623 x_attr4 OUT NOCOPY VARCHAR2,
2624 x_attr5 OUT NOCOPY VARCHAR2,
2625 x_attr6 OUT NOCOPY VARCHAR2,
2626 x_attr7 OUT NOCOPY VARCHAR2,
2627 x_attr8 OUT NOCOPY VARCHAR2,
2628 x_attr9 OUT NOCOPY VARCHAR2,
2629 x_attr10 OUT NOCOPY VARCHAR2,
2630 x_attr11 OUT NOCOPY VARCHAR2,
2631 x_attr12 OUT NOCOPY VARCHAR2,
2632 x_attr13 OUT NOCOPY VARCHAR2,
2633 x_attr14 OUT NOCOPY VARCHAR2,
2634 x_attr15 OUT NOCOPY VARCHAR2,
2635 x_xmlg_msg_id IN OUT NOCOPY VARCHAR2,
2636 p_unique1 IN VARCHAR2,
2637 p_unique2 IN VARCHAR2,
2638 p_unique3 IN VARCHAR2,
2639 p_unique4 IN VARCHAR2,
2640 p_unique5 IN VARCHAR2,
2641 p_xmlg_internal_control_number IN OUT NOCOPY NUMBER )
2642
2643 IS
2644
2645 l_error_code NUMBER;
2646 l_error_msg VARCHAR2(2000);
2647 l_msg_data VARCHAR2(2000);
2648 l_debug_file VARCHAR2(255);
2649
2650 BEGIN
2651
2652 -- Sets the debug mode to be FILE
2653 --l_debug_file :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
2654
2655
2656 IF (l_Debug_Level <= 2) THEN
2657 ecx_cln_debug_pub.Add('------ Entering RETRIEVE_COLLABORATION_DETAILS API -----',2);
2658 END IF;
2659
2660
2661
2662 -- Initialize API return status to success
2663 x_return_status := FND_API.G_RET_STS_SUCCESS;
2664 l_msg_data := 'Collaboration details successfully retrieved ';
2665
2666 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILS_RETRIEVED');
2667 x_msg_data := FND_MESSAGE.GET;
2668
2669 -- get the paramaters passed
2670 IF (l_Debug_Level <= 1) THEN
2671 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
2672 ecx_cln_debug_pub.Add('COLLABORATION DETAIL ID ----- >>>'||p_dtl_coll_id,1);
2673 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
2674 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||x_app_id,1);
2675 ecx_cln_debug_pub.Add('REFERENCE ID ----- >>>'||x_ref_id,1);
2676 ecx_cln_debug_pub.Add('RELEASE NUMBER ----- >>>'||x_rel_no,1);
2677 ecx_cln_debug_pub.Add('DOCUMENT NO ----- >>>'||x_doc_no,1);
2681 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
2678 ecx_cln_debug_pub.Add('DOCUMENT REV. NO ----- >>>'||x_doc_rev_no,1);
2679 ecx_cln_debug_pub.Add('XMLG TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
2680 ecx_cln_debug_pub.Add('XMLG TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
2682 ecx_cln_debug_pub.Add('DOCUMENT TYPE ----- >>>'||x_doc_type,1);
2683 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||x_doc_dir,1);
2684 ecx_cln_debug_pub.Add('COLL POINT ----- >>>'||x_coll_pt,1);
2685 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||x_xmlg_msg_id,1);
2686 ecx_cln_debug_pub.Add('UNIQUE 1 ----- >>>'||p_unique1,1);
2687 ecx_cln_debug_pub.Add('UNIQUE 2 ----- >>>'||p_unique2,1);
2688 ecx_cln_debug_pub.Add('UNIQUE 3 ----- >>>'||p_unique3,1);
2689 ecx_cln_debug_pub.Add('UNIQUE 4 ----- >>>'||p_unique4,1);
2690 ecx_cln_debug_pub.Add('UNIQUE 5 ----- >>>'||p_unique5,1);
2691 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
2692 ecx_cln_debug_pub.Add('===========================================',1);
2693
2694
2695 ecx_cln_debug_pub.Add('---- Before SQL Query : Retrieving Collaboration Details -----',1);
2696 END IF;
2697
2698 Select hdr.APPLICATION_ID,hdr.APPLICATION_REFERENCE_ID,
2699 hdr.RELEASE_NO,hdr.DOCUMENT_NO,hdr.DOC_REVISION_NO,
2700 hdr.RESEND_FLAG,hdr.RESEND_COUNT,hdr.DISPOSITION,
2701 hdr.COLLABORATION_STATUS,hdr.ORG_ID,hdr.TRADING_PARTNER,
2702 hdr.DOCUMENT_OWNER,hdr.INITIATION_DATE,hdr.DOCUMENT_CREATION_DATE,
2703 hdr.DOCUMENT_REVISION_DATE,hdr.XMLG_MSG_ID,dtl.COLLABORATION_DOCUMENT_TYPE,
2704 dtl.DOCUMENT_DIRECTION,dtl.COLLABORATION_POINT,
2705 dtl.ORIGINATOR_REFERENCE,dtl.DOCUMENT_STATUS,dtl.NOTIFICATION_ID,
2706 dtl.MESSAGE_TEXT,dtl.BSR_VERB,dtl.BSR_NOUN,dtl.BSR_REVISION,
2707 dtl.SENDER_LOGICAL_ID,dtl.SENDER_COMPONENT,dtl.SENDER_TASK,
2708 dtl.SENDER_REFERENCEID,dtl.SENDER_CONFIRMATION,
2709 dtl.SENDER_LANGUAGE,dtl.SENDER_CODEPAGE,dtl.SENDER_AUTHID,
2710 dtl.SENDER_DATETIME_QUALIFIER,dtl.SENDER_DATETIME,dtl.SENDER_TIMEZONE,
2711 dtl.ATTRIBUTE1,dtl.ATTRIBUTE2,dtl.ATTRIBUTE3,dtl.ATTRIBUTE4,dtl.ATTRIBUTE5,
2712 dtl.ATTRIBUTE6,dtl.ATTRIBUTE7,dtl.ATTRIBUTE8,dtl.ATTRIBUTE9,dtl.ATTRIBUTE10,
2713 dtl.ATTRIBUTE11,dtl.ATTRIBUTE12,dtl.ATTRIBUTE13,dtl.ATTRIBUTE14,dtl.ATTRIBUTE15,
2714 dtl.XMLG_TRANSACTION_TYPE, dtl.XMLG_TRANSACTION_SUBTYPE,dtl.XMLG_DOCUMENT_ID, dtl.XMLG_INTERNAL_CONTROL_NUMBER
2715 INTO x_app_id,x_ref_id,x_rel_no,x_doc_no,x_doc_rev_no,x_resend_flag,
2716 x_resend_count,x_disposition,x_coll_status,x_org_id,
2717 x_tr_partner_id,x_doc_owner,x_init_date,x_doc_creation_date,
2718 x_doc_revision_date,x_xmlg_msg_id,x_doc_type,
2719 x_doc_dir,x_coll_pt,x_org_ref,x_doc_status,
2720 x_notification_id,x_msg_text,x_bsr_verb,x_bsr_noun,x_bsr_rev,x_sdr_logical_id,
2721 x_sdr_component,x_sdr_task,x_sdr_refid,x_sdr_confirmation,x_sdr_language,x_sdr_codepage,
2722 x_sdr_authid,x_sdr_datetime_qualifier,x_sdr_datetime,x_sdr_timezone,x_attr1,x_attr2,x_attr3,
2723 x_attr4,x_attr5,x_attr6,x_attr7,x_attr8,x_attr9,x_attr10,
2724 x_attr11,x_attr12,x_attr13,x_attr14,x_attr15,
2725 p_xmlg_transaction_type, p_xmlg_transaction_subtype, p_xmlg_document_id, p_xmlg_internal_control_number
2726 FROM CLN_COLL_HIST_HDR hdr,CLN_COLL_HIST_DTL dtl
2727 WHERE hdr.COLLABORATION_ID = dtl.COLLABORATION_ID
2728 AND ( dtl.COLLABORATION_DTL_ID = p_dtl_coll_id
2729 OR (
2730 ((hdr.COLLABORATION_ID = p_coll_id)
2731 OR (APPLICATION_REFERENCE_ID = x_ref_id)
2732 OR (APPLICATION_ID = x_app_id
2733 AND UNIQUE_ID1 = p_unique1)
2734 OR (APPLICATION_ID = x_app_id
2735 AND UNIQUE_ID2 = p_unique2)
2736 OR (APPLICATION_ID = x_app_id
2737 AND UNIQUE_ID3 = p_unique3)
2738 OR (APPLICATION_ID = x_app_id
2739 AND UNIQUE_ID4 = p_unique4)
2740 OR (APPLICATION_ID = x_app_id
2741 AND UNIQUE_ID5 = p_unique5)
2742 )
2743 AND dtl.COLLABORATION_POINT = x_coll_pt
2744 AND dtl.DOCUMENT_DIRECTION = x_doc_dir
2745 )
2746 OR dtl.XMLG_MSG_ID = x_xmlg_msg_id
2747 OR dtl.XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number
2748 );
2749 IF (l_Debug_Level <= 1) THEN
2750 ecx_cln_debug_pub.Add('----- After SQL Query : Retrieving Collaboration Details -----',1);
2751 ecx_cln_debug_pub.Add(l_msg_data,1);
2755
2752 ecx_cln_debug_pub.Add('---- Exiting RETRIEVE_COLLABORATION_DETAILS API -----',2);
2753 END IF;
2754
2756
2757 EXCEPTION
2758
2759 WHEN FND_API.G_EXC_ERROR THEN
2760 l_error_code :=SQLCODE;
2761 l_error_msg :=SQLERRM;
2762 x_return_status := FND_API.G_RET_STS_ERROR ;
2763 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILS_NOT_FOUND');
2764 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2765 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2766 x_msg_data := FND_MESSAGE.GET;
2767 l_msg_data :='Collaboration details could not be retrieved for the parameters passed '||l_error_code||' : '||l_error_msg;
2768 IF (l_Debug_Level <= 4) THEN
2769 ecx_cln_debug_pub.Add(l_msg_data,4);
2770 ecx_cln_debug_pub.Add('---- Exiting RETRIEVE_COLLABORATION_DETAILS API -----',2);
2771 END IF;
2772
2773
2774
2775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2776 l_error_code :=SQLCODE;
2777 l_error_msg :=SQLERRM;
2778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2779 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILS_NOT_FOUND');
2780 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2781 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2782 x_msg_data := FND_MESSAGE.GET;
2783 l_msg_data :='Collaboration details could not be retrieved for the parameters passed '||l_error_code||' : '||l_error_msg;
2784 IF (l_Debug_Level <= 4) THEN
2785 ecx_cln_debug_pub.Add(l_msg_data,6);
2786 ecx_cln_debug_pub.Add('---- Exiting RETRIEVE_COLLABORATION_DETAILS API -----',2);
2787 END IF;
2788
2789
2790
2791 WHEN NO_DATA_FOUND THEN
2792 l_error_code :=SQLCODE;
2793 l_error_msg :=SQLERRM;
2794 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR ;
2795 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILS_NOT_FOUND');
2796 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2797 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2798 x_msg_data := FND_MESSAGE.GET;
2799 l_msg_data :='Collaboration details could not be retrieved for the parameters passed '||l_error_code||' : '||l_error_msg;
2800 IF (l_Debug_Level <= 5) THEN
2801 ecx_cln_debug_pub.Add(l_msg_data,4);
2802 ecx_cln_debug_pub.Add('---- Exiting RETRIEVE_COLLABORATION_DETAILS API -----',2);
2803 END IF;
2804
2805
2806
2807 WHEN OTHERS THEN
2808 l_error_code :=SQLCODE;
2809 l_error_msg :=SQLERRM;
2810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2811 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILS_NOT_FOUND');
2812 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
2813 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
2814 x_msg_data := FND_MESSAGE.GET;
2815 l_msg_data :='Collaboration details could not be retrieved for the parameters passed '||l_error_code||' : '||l_error_msg;
2816 IF (l_Debug_Level <= 5) THEN
2817 ecx_cln_debug_pub.Add(l_msg_data,6);
2818 ecx_cln_debug_pub.Add('---- Exiting RETRIEVE_COLLABORATION_DETAILS API -----',2);
2819 END IF;
2820
2821
2822
2823 END RETRIEVE_COLLABORATION_DETAILS;
2824
2825
2826
2827 -- Name
2828 -- ADD_COLLABORATION_MESSAGES
2829 -- Purpose
2830 -- This is the public procedure which may be called by user for adding
2831 -- detail messages related with any Collaboration.
2832 -- Arguments
2833 --
2834 -- Notes
2835 -- No specific notes.
2836
2837
2838 PROCEDURE ADD_COLLABORATION_MESSAGES(
2839 x_return_status OUT NOCOPY VARCHAR2,
2840 x_msg_data OUT NOCOPY VARCHAR2,
2841 p_dtl_coll_id IN NUMBER,
2842 p_ref1 IN VARCHAR2,
2843 p_ref2 IN VARCHAR2,
2844 p_ref3 IN VARCHAR2,
2845 p_ref4 IN VARCHAR2,
2846 p_ref5 IN VARCHAR2,
2847 p_dtl_msg IN VARCHAR2,
2848 p_coll_id IN NUMBER,
2849 p_xmlg_transaction_type IN VARCHAR2,
2850 p_xmlg_transaction_subtype IN VARCHAR2,
2851 p_xmlg_document_id IN VARCHAR2,
2852 p_doc_type IN VARCHAR2,
2853 p_doc_direction IN VARCHAR2,
2854 p_coll_point IN VARCHAR2,
2855 p_xmlg_internal_control_number IN NUMBER,
2856 p_xmlg_int_transaction_type IN VARCHAR2,
2857 p_xmlg_int_transaction_subtype IN VARCHAR2,
2858 p_xmlg_msg_id IN VARCHAR2,
2859 p_xml_event_key IN VARCHAR2,
2860 p_app_id IN VARCHAR2,
2861 p_ref_id IN VARCHAR2,
2862 p_unique1 IN VARCHAR2,
2866 p_unique5 IN VARCHAR2
2863 p_unique2 IN VARCHAR2,
2864 p_unique3 IN VARCHAR2,
2865 p_unique4 IN VARCHAR2,
2867 )
2868
2869 IS
2870
2871 l_coll_dtl_id NUMBER;
2872 l_error_code NUMBER;
2873 l_dtl_msg_id NUMBER;
2874 l_error_msg VARCHAR2(2000);
2875 l_msg_data VARCHAR2(2000);
2876 l_debug_file VARCHAR2(255);
2877 l_dtl_msg VARCHAR2(2000);
2878
2879 BEGIN
2880
2881 -- Sets the debug mode to be FILE
2882 --l_debug_file :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
2883
2884 IF (l_Debug_Level <= 2) THEN
2885 ecx_cln_debug_pub.Add('----- Entering ADD_COLLABORATION_MESSAGES API ------',2);
2886 END IF;
2887
2888
2889
2890 -- Initialize API return status to success
2891 x_return_status := FND_API.G_RET_STS_SUCCESS;
2892
2893 l_msg_data := 'Detail Messages for Collaboration successfully added ';
2894 l_coll_dtl_id := p_dtl_coll_id;
2895 l_dtl_msg := p_dtl_msg;
2896
2897 -- get the paramaters passed
2898 IF (l_Debug_Level <= 1) THEN
2899 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
2900 ecx_cln_debug_pub.Add('COLLABORATION DETAIL ID ----- >>>'||l_coll_dtl_id,1);
2901 ecx_cln_debug_pub.Add('REFERENCE 1 ----- >>>'||p_ref1,1);
2902 ecx_cln_debug_pub.Add('REFERENCE 2 ----- >>>'||p_ref2,1);
2903 ecx_cln_debug_pub.Add('REFERENCE 3 ----- >>>'||p_ref3,1);
2904 ecx_cln_debug_pub.Add('REFERENCE 4 ----- >>>'||p_ref4,1);
2905 ecx_cln_debug_pub.Add('REFERENCE 5 ----- >>>'||p_ref5,1);
2906 ecx_cln_debug_pub.Add('DETAIL MSG ----- >>>'||p_dtl_msg,1);
2907 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
2908 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
2909 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
2910 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
2911 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
2912 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
2913 ecx_cln_debug_pub.Add('DOCUMENT TYPE ----- >>>'||p_doc_type,1);
2914 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_direction,1);
2915 ecx_cln_debug_pub.Add('COLLABORATION POINT ----- >>>'||p_coll_point,1);
2916 ecx_cln_debug_pub.Add('XMLG INTERNAL CTRL NUMBER ----- >>>'||p_xmlg_internal_control_number,1);
2917 ecx_cln_debug_pub.Add('XML MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
2918 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
2919 ecx_cln_debug_pub.Add('APPLICATION ID ----- >>>'||P_app_id,1);
2920 ecx_cln_debug_pub.Add('REFERENCE ID ----- >>>'||P_ref_id,1);
2921 ecx_cln_debug_pub.Add('UNIQUE ID 1 ----- >>>'||p_unique1,1);
2922 ecx_cln_debug_pub.Add('UNIQUE ID 2 ----- >>>'||p_unique2,1);
2923 ecx_cln_debug_pub.Add('UNIQUE ID 3 ----- >>>'||p_unique3,1);
2924 ecx_cln_debug_pub.Add('UNIQUE ID 4 ----- >>>'||p_unique4,1);
2925 ecx_cln_debug_pub.Add('UNIQUE ID 5 ----- >>>'||p_unique5,1);
2926 ecx_cln_debug_pub.Add('=========================================',1);
2927 END IF;
2928
2929
2930
2931
2932 -- Remove the last comma from the message if it exists
2933 IF ( substr(p_dtl_msg,-1) )= ',' THEN
2934 l_dtl_msg:= substr( p_dtl_msg, 0, length(p_dtl_msg) - 1);
2935 IF (l_Debug_Level <= 1) THEN
2936 ecx_cln_debug_pub.Add('DETAIL MSG AFTER TRIMMING ----- >>>'||l_dtl_msg,1);
2937 END IF;
2938
2939 END IF;
2940
2941
2942 -- Collaboration Message Detail ID is generated from a sequence.
2943 SELECT cln_collaboration_msg_id_s.nextval INTO l_dtl_msg_id FROM dual ;
2944 IF (l_Debug_Level <= 1) THEN
2945 ecx_cln_debug_pub.Add('Message Detail ID generated : '||l_dtl_msg_id,1);
2946 END IF;
2947
2948
2949 -- Check for Collaboration Detail ID value
2950 BEGIN
2951 IF (l_coll_dtl_id IS NULL) THEN
2952 IF (l_Debug_Level <= 1) THEN
2953 ecx_cln_debug_pub.Add('COLLABORATION_DETAIL_ID passed as null',1);
2954 ecx_cln_debug_pub.Add('Before calling CLN_CH_COLLABORATION_PKG.FIND_COLLABORATION_DETAIL_ID API',1);
2955 END IF;
2956
2957
2958 FIND_COLLABORATION_DETAIL_ID(
2959 x_return_status => x_return_status,
2960 x_msg_data => x_msg_data,
2964 p_xmlg_document_id => p_xmlg_document_id,
2961 p_coll_id => p_coll_id,
2962 p_xmlg_transaction_type => p_xmlg_transaction_type,
2963 p_xmlg_transaction_subtype => p_xmlg_transaction_subtype,
2965 p_doc_type => p_doc_type,
2966 p_doc_direction => p_doc_direction,
2967 p_coll_point => p_coll_point,
2968 x_dtl_coll_id => l_coll_dtl_id,
2969 p_xmlg_msg_id => p_xmlg_msg_id,
2970 p_xmlg_internal_control_number => p_xmlg_internal_control_number,
2971 p_xmlg_int_transaction_type => p_xmlg_int_transaction_type,
2972 p_xmlg_int_transaction_subtype => p_xmlg_int_transaction_subtype,
2973 p_xml_event_key => p_xml_event_key,
2974 p_app_id => p_app_id,
2975 p_ref_id => p_ref_id,
2976 p_unique1 => p_unique1,
2977 p_unique2 => p_unique2,
2978 p_unique3 => p_unique3,
2979 p_unique4 => p_unique4,
2980 p_unique5 => p_unique5
2981 );
2982
2983 IF (l_Debug_Level <= 1) THEN
2984 ecx_cln_debug_pub.Add('Return Status from FIND_COLLABORATION_DETAIL_ID -'||x_return_status ,1);
2985 ecx_cln_debug_pub.Add('COLLABORATION_DETAIL_ID obtained as -'||l_coll_dtl_id ,1);
2986 END IF;
2987
2988
2989 IF (x_return_status <> 'S') THEN
2990 RAISE FND_API.G_EXC_ERROR;
2991 END IF;
2992 ELSE
2993 SELECT collaboration_dtl_id
2994 INTO l_coll_dtl_id
2995 FROM cln_coll_hist_dtl
2996 WHERE collaboration_dtl_id = p_dtl_coll_id;
2997 END IF;
2998 EXCEPTION
2999 WHEN NO_DATA_FOUND THEN
3000 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_NOT_FOUND');
3001 FND_MESSAGE.SET_TOKEN('PARAM','Collaboration Detail Id');
3002 FND_MESSAGE.SET_TOKEN('VALUE',p_dtl_coll_id);
3003 x_msg_data := FND_MESSAGE.GET;
3004 l_msg_data :='Collaboration not found for the particular Collaboration Detail Id :'||p_dtl_coll_id;
3005 RAISE FND_API.G_EXC_ERROR;
3006 END;
3007
3008 IF (l_Debug_Level <= 1) THEN
3009 ecx_cln_debug_pub.Add('Before SQL Query : Adding Collaboration Detail Messages',1);
3010 END IF;
3011
3012
3013 -- Message Details for a Collaboration are added into CLN_COLL_MESSAGES Table
3014 INSERT INTO CLN_COLL_MESSAGES(
3015 DTL_MESSAGE_ID,COLLABORATION_DTL_ID,REFERENCE1,REFERENCE2,REFERENCE3,REFERENCE4,REFERENCE5,
3016 DTL_MESSAGE_TEXT,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
3017 VALUES( l_dtl_msg_id,l_coll_dtl_id,p_ref1,p_ref2,p_ref3,p_ref4,p_ref5,l_dtl_msg,
3018 SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID);
3019
3020
3021 IF (l_Debug_Level <= 1) THEN
3022 ecx_cln_debug_pub.Add('After SQL Query : Adding Collaboration Detail Messages',1);
3023 END IF;
3024
3025
3026 IF SQL%FOUND THEN
3027 IF (l_Debug_Level <= 1) THEN
3028 ecx_cln_debug_pub.Add('Detail Messages for a collaboration Added',1);
3029 END IF;
3030
3031 ELSE
3032 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ADD_MSGS_FAILED');
3033 x_msg_data :=FND_MESSAGE.GET;
3034 l_msg_data :='Failed to add Message Details';
3035 RAISE FND_API.G_EXC_ERROR;
3036 END IF;
3037
3038 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_MSGS_ADDED');
3039 x_msg_data := FND_MESSAGE.GET;
3040 IF (l_Debug_Level <= 1) THEN
3041 ecx_cln_debug_pub.Add(l_msg_data,1);
3042 END IF;
3043
3044 IF (l_Debug_Level <= 2) THEN
3045 ecx_cln_debug_pub.Add('------ Exiting ADD_COLLABORATION_MESSAGES API ------',2);
3046 END IF;
3047
3048
3049 EXCEPTION
3050
3051 WHEN FND_API.G_EXC_ERROR THEN
3052 x_return_status :=FND_API.G_RET_STS_ERROR ;
3053 IF (l_Debug_Level <= 4) THEN
3054 ecx_cln_debug_pub.Add(l_msg_data,4);
3055 ecx_cln_debug_pub.Add('------ Exiting ADD_COLLABORATION_MESSAGES API ------',2);
3056 END IF;
3057
3058
3059
3060 WHEN NO_DATA_FOUND THEN
3061 l_error_code :=SQLCODE;
3062 l_error_msg :=SQLERRM;
3063 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR ;
3064 l_msg_data :='Collaboration not found for the particular Collaboration Detail Id :'||l_error_code||' : '||l_error_msg;
3068 x_msg_data := FND_MESSAGE.GET;
3065 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILID_NOT_FOUND');
3066 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
3067 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
3069 IF (l_Debug_Level <= 5) THEN
3070 ecx_cln_debug_pub.Add(l_msg_data,4);
3071 ecx_cln_debug_pub.Add('------ Exiting ADD_COLLABORATION_MESSAGES API ------',2);
3072 END IF;
3073
3074
3075
3076 WHEN OTHERS THEN
3077 l_error_code :=SQLCODE;
3078 l_error_msg :=SQLERRM;
3079 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
3080 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
3081 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
3082 x_msg_data :=FND_MESSAGE.GET;
3083 l_msg_data :=l_error_code||' : '||l_error_msg;
3084 IF (l_Debug_Level <= 5) THEN
3085 ecx_cln_debug_pub.Add(l_msg_data,6);
3086 ecx_cln_debug_pub.Add('------ Exiting ADD_COLLABORATION_MESSAGES API ------',2);
3087 END IF;
3088
3089
3090
3091 END ADD_COLLABORATION_MESSAGES;
3092
3093
3094
3095 -- Name
3096 -- IS_UPDATE_REQUIRED
3097 -- Purpose
3098 -- This is the public procedure which checks for the protocol used
3099 -- based on few parameters passed in and accordingly,collaboration is updated.
3100 -- Arguments
3101 --
3102 -- Notes
3103 -- No specific notes.
3104
3105 PROCEDURE IS_UPDATE_REQUIRED(
3106 x_return_status OUT NOCOPY VARCHAR2,
3107 x_msg_data OUT NOCOPY VARCHAR2,
3108 p_doc_dir IN VARCHAR2,
3109 p_xmlg_transaction_type IN VARCHAR2,
3110 p_xmlg_transaction_subtype IN VARCHAR2,
3111 p_tr_partner_type IN VARCHAR2,
3112 p_tr_partner_id IN VARCHAR2,
3113 p_tr_partner_site IN VARCHAR2,
3114 p_sender_component IN VARCHAR2,
3115 x_update_reqd OUT NOCOPY BOOLEAN)
3116
3117 IS
3118
3119 l_error_code NUMBER;
3120 l_msg_data VARCHAR2(2000);
3121 l_error_msg VARCHAR2(2000);
3122 l_debug_mode VARCHAR2(255);
3123 l_fnd_profile VARCHAR2(100);
3124 l_protocol_type VARCHAR2(50);
3125 l_hub_user_id VARCHAR2(50);
3126
3127 BEGIN
3128
3129 -- Sets the debug mode to be FILE
3130 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
3131
3132
3133 IF (l_Debug_Level <= 2) THEN
3134 ecx_cln_debug_pub.Add('------ Entering IS_UPDATE_REQUIRED API ------ ',2);
3135 END IF;
3136
3137
3138 -- Initialize API return status to success
3139 x_return_status := FND_API.G_RET_STS_SUCCESS;
3140
3141
3142 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ROSETTANET_STD');
3143 x_msg_data := FND_MESSAGE.GET;
3144 l_msg_data := 'Collaboration is on RosettaNet standards';
3145 x_update_reqd := FALSE;
3146 l_protocol_type := p_sender_component ;
3147
3148
3149 -- get the paramaters passed
3150 IF (l_Debug_Level <= 1) THEN
3151 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
3152 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
3153 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
3154 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
3155 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||p_tr_partner_type,1);
3156 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||p_tr_partner_id,1);
3157 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||p_tr_partner_site,1);
3158 ecx_cln_debug_pub.Add('SENDER COMPONENT ----- >>>'||l_protocol_type,1);
3159 ecx_cln_debug_pub.Add('===========================================',1);
3160 END IF;
3161
3162
3163
3164 -- Check for Profile value
3165 l_fnd_profile :=FND_PROFILE.VALUE('CLN_UPDATION');
3166
3167 IF(l_fnd_profile = 'NEVER') THEN
3168 IF (l_Debug_Level <= 1) THEN
3169 ecx_cln_debug_pub.Add('Profile Value - CLN_UPDATION found as NEVER',1);
3170 END IF;
3171
3172 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_NOT_REQD');
3173 x_msg_data := FND_MESSAGE.GET;
3174 x_return_status := FND_API.G_RET_STS_SUCCESS;
3175 x_update_reqd := FALSE;
3176 IF (l_Debug_Level <= 1) THEN
3177 ecx_cln_debug_pub.Add('Collaboration need not be created/updated',1);
3178 END IF;
3179
3180 Return;
3181 ELSIF(l_fnd_profile = 'ROSETTANET') THEN
3182 IF (l_Debug_Level <= 1) THEN
3183 ecx_cln_debug_pub.Add('Profile Value - CLN_UPDATION found as ROSETTANET',1);
3187 IF (p_doc_dir = 'OUT') THEN
3184 END IF;
3185
3186
3188 IF (l_Debug_Level <= 1) THEN
3189 ecx_cln_debug_pub.Add('Document direction is out',1);
3190 END IF;
3191
3192 IF (l_protocol_type IS NULL) THEN
3193 BEGIN
3194 SELECT protocol_type,hub_user_id
3195 INTO l_protocol_type, l_hub_user_id
3196 FROM ecx_tp_details etd, ecx_tp_headers eth,
3197 ecx_ext_processes eep
3198 WHERE (eth.party_id = p_tr_partner_id or p_tr_partner_id is null )
3199 AND eth.party_site_id = p_tr_partner_site
3200 AND eth.party_type = p_tr_partner_type
3201 AND eth.tp_header_id = etd.tp_header_id
3202 AND eep.ext_type = p_xmlg_transaction_type
3203 AND eep.ext_subtype = p_xmlg_transaction_subtype
3204 AND eep.ext_process_id = etd.ext_process_id
3205 AND eep.direction = 'OUT';
3206 EXCEPTION
3207 WHEN NO_DATA_FOUND THEN
3208 l_msg_data := 'Invalid Trading Partner';
3209 FND_MESSAGE.SET_NAME('CLN','CLN_CH_INVALID_PARAM');
3210 FND_MESSAGE.SET_TOKEN('PARAM','Trading Partner');
3211 x_msg_data := FND_MESSAGE.GET;
3212 RAISE FND_API.G_EXC_ERROR;
3213 END;
3214
3215 -- if hub value is also entered along with the protocol or
3216 -- only hub value is there , then also we an get protocol value
3217 IF l_hub_user_id IS NOT NULL THEN
3218 IF (l_Debug_Level <= 1) THEN
3219 ecx_cln_debug_pub.Add('Hub user id is not null',1);
3220 END IF;
3221
3222
3223 BEGIN
3224 SELECT protocol_type
3225 INTO l_protocol_type
3226 FROM ecx_hubs eh, ecx_hub_users ehu
3227 WHERE eh.hub_id = ehu.hub_id
3228 AND ehu.hub_user_id = l_hub_user_id;
3229 EXCEPTION
3230 WHEN NO_DATA_FOUND THEN
3231 l_msg_data := 'Invalid user id for Hub';
3232 FND_MESSAGE.SET_NAME('CLN','CLN_CH_INVALID_USERID_HUB');
3233 x_msg_data := FND_MESSAGE.GET;
3234 RAISE FND_API.G_EXC_ERROR;
3235 END;
3236 END IF;
3237 END IF;
3238
3239 IF (l_Debug_Level <= 1) THEN
3240 ecx_cln_debug_pub.Add('Protocol value found as :'||l_protocol_type,1);
3241 END IF;
3242
3243
3244 IF(l_protocol_type <> 'IAS') THEN
3245 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_NOT_REQD');
3246 x_msg_data := FND_MESSAGE.GET;
3247 ecx_cln_debug_pub.Add('Protocol type is not IAS',1);
3248 ecx_cln_debug_pub.Add('Collaboration need not be created/updated',1);
3249 x_update_reqd :=FALSE;
3250 x_return_status := FND_API.G_RET_STS_SUCCESS;
3251 Return;
3252 END IF;
3253 IF (l_Debug_Level <= 1) THEN
3254 ecx_cln_debug_pub.Add('Collaboration can be updated',1);
3255 END IF;
3256
3257 x_update_reqd := TRUE;
3258
3259 ELSIF (p_doc_dir = 'IN') THEN
3260 ecx_cln_debug_pub.Add('Document direction is IN',1);
3261 IF(l_protocol_type = 'IAS') THEN
3262 IF (l_Debug_Level <= 1) THEN
3263 ecx_cln_debug_pub.Add('SENDER/COMPONENT tag has the value as IAS',1);
3264 ecx_cln_debug_pub.Add('Collaboration can be updated',1);
3265 END IF;
3266
3267 x_update_reqd := TRUE;
3268 ELSE
3269 IF (l_Debug_Level <= 1) THEN
3270 ecx_cln_debug_pub.Add('SENDER/COMPONENT tag has the value as -'||l_protocol_type,1);
3271 ecx_cln_debug_pub.Add('Collaboration need not be created/updated',1);
3272 END IF;
3273
3274 x_update_reqd := FALSE;
3275 END IF;
3276 END IF;
3277 ELSE
3278 x_update_reqd := TRUE;
3279 END IF;
3283 END IF;
3280
3281 IF (l_Debug_Level <= 1) THEN
3282 ecx_cln_debug_pub.Add(l_msg_data,1);
3284
3285 IF (l_Debug_Level <= 2) THEN
3286 ecx_cln_debug_pub.Add('------ Exiting IS_UPDATE_REQUIRED ------- ',2);
3287 END IF;
3288
3289
3290 EXCEPTION
3291
3292 WHEN FND_API.G_EXC_ERROR THEN
3293 x_return_status :=FND_API.G_RET_STS_ERROR ;
3294 IF (l_Debug_Level <= 4) THEN
3295 ecx_cln_debug_pub.Add(l_msg_data,4);
3296 ecx_cln_debug_pub.Add('------ Exiting IS_UPDATE_REQUIRED ------- ',2);
3297 END IF;
3298
3299
3300
3301 WHEN OTHERS THEN
3302 l_error_code :=SQLCODE;
3303 l_error_msg :=SQLERRM;
3304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3305 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
3306 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
3307 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
3308 x_msg_data :=FND_MESSAGE.GET;
3309 l_msg_data := 'Unexpected Error : '||l_error_code||'-'||l_error_msg;
3310 IF (l_Debug_Level <= 5) THEN
3311 ecx_cln_debug_pub.Add(l_msg_data,6);
3312 ecx_cln_debug_pub.Add('------ Exiting IS_UPDATE_REQUIRED ------- ',2);
3313 END IF;
3314
3315
3316 END IS_UPDATE_REQUIRED;
3317
3318
3319
3320 -- Name
3321 -- FIND_COLLABORATION_DETAIL_ID
3322 -- Purpose
3323 -- This is the public procedure which may be used to get the latest collaboration detail id
3324 -- for a particular collaboration id or other paramaters.
3325 -- Arguments
3326 --
3327 -- Notes
3328 -- No specific notes.
3329
3330
3331 PROCEDURE FIND_COLLABORATION_DETAIL_ID(
3332 x_return_status OUT NOCOPY VARCHAR2,
3333 x_msg_data OUT NOCOPY VARCHAR2,
3334 p_coll_id IN NUMBER,
3335 p_app_id IN VARCHAR2,
3336 p_ref_id IN VARCHAR2,
3337 p_rel_no IN VARCHAR2,
3338 p_doc_no IN VARCHAR2,
3339 p_doc_rev_no IN VARCHAR2,
3340 p_xmlg_transaction_type IN VARCHAR2,
3341 p_xmlg_transaction_subtype IN VARCHAR2,
3342 p_xmlg_document_id IN VARCHAR2,
3343 p_unique1 IN VARCHAR2,
3344 p_unique2 IN VARCHAR2,
3345 p_unique3 IN VARCHAR2,
3346 p_unique4 IN VARCHAR2,
3347 p_unique5 IN VARCHAR2,
3348 p_doc_type IN VARCHAR2,
3349 p_doc_direction IN VARCHAR2,
3350 p_coll_point IN VARCHAR2,
3351 x_dtl_coll_id OUT NOCOPY NUMBER,
3352 p_xmlg_msg_id IN VARCHAR2,
3353 p_xmlg_internal_control_number IN NUMBER,
3354 p_xmlg_int_transaction_type IN VARCHAR2,
3355 p_xmlg_int_transaction_subtype IN VARCHAR2,
3356 p_xml_event_key IN VARCHAR2)
3357 IS
3358 l_error_code NUMBER;
3359 l_error_msg VARCHAR2(2000);
3360 l_msg_data VARCHAR2(2000);
3361 l_debug_mode VARCHAR2(255);
3362 l_coll_id VARCHAR2(255);
3363 BEGIN
3364
3365 -- Sets the debug mode to be FILE
3366 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
3367
3368
3369 IF (l_Debug_Level <= 2) THEN
3370 ecx_cln_debug_pub.Add('------ Entering FIND_COLLABORATION_DETAIL_ID API -----',2);
3371 END IF;
3372
3373
3374 -- Initialize API return status to success
3375 x_return_status := FND_API.G_RET_STS_SUCCESS;
3376 l_msg_data := 'Collaboration detail id successfully found'||x_dtl_coll_id;
3377
3378 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILID_FOUND');
3379 x_msg_data := FND_MESSAGE.GET;
3380
3381 -- get the paramaters passed
3382 IF (l_Debug_Level <= 1) THEN
3383 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
3384 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
3385 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
3386 ecx_cln_debug_pub.Add('REFERENCE ID ----- >>>'||p_ref_id,1);
3387 ecx_cln_debug_pub.Add('RELEASE NUMBER ----- >>>'||p_rel_no,1);
3388 ecx_cln_debug_pub.Add('DOCUMENT NO ----- >>>'||p_doc_no,1);
3389 ecx_cln_debug_pub.Add('DOCUMENT REV. NO ----- >>>'||p_doc_rev_no,1);
3390 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
3391 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
3392 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
3396 ecx_cln_debug_pub.Add('UNIQUE 2 ----- >>>'||p_unique2,1);
3393 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
3394 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
3395 ecx_cln_debug_pub.Add('UNIQUE 1 ----- >>>'||p_unique1,1);
3397 ecx_cln_debug_pub.Add('UNIQUE 3 ----- >>>'||p_unique3,1);
3398 ecx_cln_debug_pub.Add('UNIQUE 4 ----- >>>'||p_unique4,1);
3399 ecx_cln_debug_pub.Add('UNIQUE 5 ----- >>>'||p_unique5,1);
3400 ecx_cln_debug_pub.Add('DOCUMENT TYPE ----- >>>'||p_doc_type,1);
3401 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_direction,1);
3402 ecx_cln_debug_pub.Add('COLLABORATION POINT ----- >>>'||p_coll_point,1);
3403 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
3404 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
3405 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
3406 ecx_cln_debug_pub.Add('===========================================',1);
3407 END IF;
3408
3409
3410
3411
3412 BEGIN
3413 IF (l_Debug_Level <= 1) THEN
3414 ecx_cln_debug_pub.Add('--Before the query to CLN_COLL_HIST_DTL table ---',1);
3415 END IF;
3416
3417 SELECT MAX(collaboration_dtl_id) INTO x_dtl_coll_id
3418 FROM CLN_COLL_HIST_DTL
3419 WHERE COLLABORATION_POINT = nvl(p_coll_point,'APPS')
3420 AND ( XMLG_MSG_ID = p_xmlg_msg_id
3421 OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number
3422 );
3423 IF (l_Debug_Level <= 1) THEN
3424 ecx_cln_debug_pub.Add('-- After the query to CLN_COLL_HIST_DTL table ---',1);
3425 ecx_cln_debug_pub.Add('Collaboration Detail ID : '||x_dtl_coll_id,1);
3426 END IF;
3427
3428
3429 EXCEPTION
3430 WHEN NO_DATA_FOUND THEN
3431 IF (l_Debug_Level <= 1) THEN
3432 ecx_cln_debug_pub.Add('Unable to trace collaboration dtl ID using transaction details',1);
3433 END IF;
3434
3435 END;
3436
3437 IF (x_dtl_coll_id is NULL) THEN
3438 IF (l_Debug_Level <= 1) THEN
3439 ecx_cln_debug_pub.Add('Before SQL Query : Retrieving Collaboration dtl ID using xmlg_document_id',1);
3440 END IF;
3441
3442
3443 BEGIN
3444 SELECT MAX(collaboration_dtl_id) INTO x_dtl_coll_id
3445 FROM CLN_COLL_HIST_DTL
3446 WHERE COLLABORATION_POINT = nvl(p_coll_point,'APPS')
3447 AND ( (XMLG_TRANSACTION_TYPE = p_xmlg_transaction_type
3448 AND XMLG_TRANSACTION_SUBTYPE = p_xmlg_transaction_subtype
3449 AND XMLG_DOCUMENT_ID = p_xmlg_document_id
3450 AND DOCUMENT_DIRECTION = nvl( p_doc_direction, DOCUMENT_DIRECTION)
3451 AND (XML_EVENT_KEY is null or p_xml_event_key is null or XML_EVENT_KEY = p_xml_event_key)
3452 )
3453 OR
3454 (XMLG_INT_TRANSACTION_TYPE = p_xmlg_int_transaction_type
3455 AND XMLG_INT_TRANSACTION_SUBTYPE = p_xmlg_int_transaction_subtype
3456 AND XMLG_DOCUMENT_ID = p_xmlg_document_id
3457 AND DOCUMENT_DIRECTION = nvl( p_doc_direction, DOCUMENT_DIRECTION)
3458 AND (XML_EVENT_KEY is null or p_xml_event_key is null or XML_EVENT_KEY = p_xml_event_key)
3459 )
3460 );
3461 IF (l_Debug_Level <= 1) THEN
3462 ecx_cln_debug_pub.Add('After SQL Query : Retrieving Collaboration dtl ID using xmlg_document_id',1);
3463 ecx_cln_debug_pub.Add('Collaboration Detail ID : '||x_dtl_coll_id,1);
3464 END IF;
3465
3466
3467 EXCEPTION
3468 WHEN NO_DATA_FOUND THEN
3469 IF (l_Debug_Level <= 1) THEN
3470 ecx_cln_debug_pub.Add('Unable to trace Collaboration dtl ID using xmlg_document_id',1);
3471 END IF;
3472
3473 END;
3474 END IF;
3475
3476 IF (x_dtl_coll_id IS NOT NULL) THEN
3477 IF (l_Debug_Level <= 1) THEN
3478 ecx_cln_debug_pub.Add(l_msg_data,1);
3479 END IF;
3480
3481 IF (l_Debug_Level <= 2) THEN
3482 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_DETAIL_ID API ------',2);
3483 END IF;
3484
3485 RETURN;
3486 END IF;
3487
3488
3489
3490 IF (l_Debug_Level <= 1) THEN
3491 ecx_cln_debug_pub.Add('Collaboration ID passed as '||p_coll_id,1);
3492 END IF;
3493
3497 END IF;
3494 IF (p_coll_id is NULL) THEN
3495 IF (l_Debug_Level <= 1) THEN
3496 ecx_cln_debug_pub.Add('Before SQL Query 1 : Retrieving Collaboration ID using CLN_COLL_HIST_HDR',1);
3498
3499 BEGIN
3500 SELECT COLLABORATION_ID INTO l_coll_id
3501 FROM CLN_COLL_HIST_HDR
3502 WHERE APPLICATION_REFERENCE_ID = p_ref_id
3503 OR XMLG_MSG_ID = p_xmlg_msg_id
3504 OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number;
3505 EXCEPTION
3506 WHEN TOO_MANY_ROWS THEN
3507 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNIQUE_COLLABORATION_NF');
3508 x_msg_data := FND_MESSAGE.GET;
3509 l_msg_data := 'Unique Collaboration Not Found';
3510 RAISE FND_API.G_EXC_ERROR;
3511
3512 WHEN NO_DATA_FOUND THEN
3513 IF p_unique1 IS NOT NULL OR
3514 p_unique2 IS NOT NULL OR
3515 p_unique3 IS NOT NULL OR
3516 p_unique4 IS NOT NULL OR
3517 p_unique5 IS NOT NULL THEN
3518
3519 IF (l_Debug_Level <= 1) THEN
3520 ecx_cln_debug_pub.Add('Before SQL Query 2 : Retrieving Collaboration ID using CLN_COLL_HIST_HDR',1);
3521 END IF;
3522
3523 BEGIN
3524 SELECT COLLABORATION_ID INTO l_coll_id
3525 FROM CLN_COLL_HIST_HDR
3526 WHERE (APPLICATION_ID = p_app_id AND UNIQUE_ID1 = p_unique1)
3527 OR (APPLICATION_ID = p_app_id AND UNIQUE_ID2 = p_unique2)
3528 OR (APPLICATION_ID = p_app_id AND UNIQUE_ID3 = p_unique3)
3529 OR (APPLICATION_ID = p_app_id AND UNIQUE_ID4 = p_unique4)
3530 OR (APPLICATION_ID = p_app_id AND UNIQUE_ID5 = p_unique5);
3531
3532 EXCEPTION
3533 WHEN NO_DATA_FOUND THEN
3534 IF (l_Debug_Level <= 1) THEN
3535 ecx_cln_debug_pub.Add('Unable to find the collaboration in Collaboration History - Header Table',1);
3536 END IF;
3537
3538 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_NOT_FOUND');
3539 x_msg_data := FND_MESSAGE.GET;
3540 l_msg_data := 'Unable to find the collaboration in Collaboration History - Detail Table'; RAISE FND_API.G_EXC_ERROR;
3541 WHEN TOO_MANY_ROWS THEN
3542 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNIQUE_COLLABORATION_NF');
3543 x_msg_data := FND_MESSAGE.GET;
3544 l_msg_data := 'Unique Collaboration Not Found';
3545 RAISE FND_API.G_EXC_ERROR;
3546 END;
3547 ELSE
3548 IF (l_Debug_Level <= 1) THEN
3549 cln_debug_pub.Add('Unique IDs from 1 to 5 are NULL',1);
3550 END IF;
3551 END IF;
3552
3553 END;
3554 IF (l_Debug_Level <= 1) THEN
3555 IF (l_Debug_Level <= 1) THEN
3556 ecx_cln_debug_pub.Add('After SQL Query : Retrieving Collaboration ID',1);
3557 ecx_cln_debug_pub.Add('Retrieved Collaboration ID' || l_coll_id,1);
3558 END IF;
3559
3560 END IF;
3561
3562 ELSE
3563 l_coll_id := p_coll_id;
3564 END IF;
3565
3566 IF (l_Debug_Level <= 1) THEN
3567 ecx_cln_debug_pub.Add('--- Before SQL Query : Retrieving Collaboration Detail ID ----',1);
3568 END IF;
3569
3570 SELECT max(COLLABORATION_DTL_ID) INTO x_dtl_coll_id
3571 FROM CLN_COLL_HIST_DTL
3572 WHERE COLLABORATION_ID = l_coll_id
3573 AND COLLABORATION_DOCUMENT_TYPE = p_doc_type
3574 AND DOCUMENT_DIRECTION = p_doc_direction
3575 AND COLLABORATION_POINT = p_coll_point;
3576 IF (l_Debug_Level <= 1) THEN
3577 ecx_cln_debug_pub.Add(' ---- After SQL Query : Retrieving Collaboration Detail ID ----',1);
3578 ecx_cln_debug_pub.Add(l_msg_data,1);
3579 END IF;
3580
3581 IF (l_Debug_Level <= 2) THEN
3582 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_DETAIL_ID API ------',2);
3583 END IF;
3584
3585
3586
3587 EXCEPTION
3588
3589 WHEN NO_DATA_FOUND THEN
3590 l_error_code :=SQLCODE;
3591 l_error_msg :=SQLERRM;
3592 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR ;
3593 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILID_NOT_FOUND');
3594 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
3595 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
3596 x_msg_data := FND_MESSAGE.GET;
3600 ecx_cln_debug_pub.Add('Error in FIND_COLLABORATION_DETAIL_ID API',2);
3597 l_msg_data :='Collaboration detail id could not be found for the parameters passed :'||l_error_code||' : '||l_error_msg;
3598 IF (l_Debug_Level <= 4) THEN
3599 ecx_cln_debug_pub.Add(l_msg_data,4);
3601 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_DETAIL_ID API ------',2);
3602 END IF;
3603
3604
3605
3606 WHEN OTHERS THEN
3607 l_error_code :=SQLCODE;
3608 l_error_msg :=SQLERRM;
3609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3610 FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLL_DETAILID_NOT_FOUND');
3611 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
3612 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
3613 x_msg_data := FND_MESSAGE.GET;
3614 l_msg_data :='Collaboration detail id could not be found for the parameters passed :'||l_error_code||' : '||l_error_msg;
3615 IF (l_Debug_Level <= 5) THEN
3616 ecx_cln_debug_pub.Add(x_msg_data,6);
3617 ecx_cln_debug_pub.Add('Error in FIND_COLLABORATION_DETAIL_ID API',2);
3618 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_DETAIL_ID API ------',2);
3619 END IF;
3620
3621
3622 END FIND_COLLABORATION_DETAIL_ID;
3623
3624
3625
3626 -- Name
3627 -- GET_TRADING_PARTNER_DETAILS
3628 -- Purpose
3629 -- This is the public procedure which checks for the trading partner details from the
3630 -- xmlg tables based on the parameters passed.
3631 -- Arguments
3632 --
3633 -- Notes
3634 -- No specific notes.
3635
3636 PROCEDURE GET_TRADING_PARTNER_DETAILS(
3637 x_return_status OUT NOCOPY VARCHAR2,
3638 x_msg_data OUT NOCOPY VARCHAR2,
3639 p_xmlg_internal_control_number IN OUT NOCOPY NUMBER,
3640 p_xmlg_msg_id IN OUT NOCOPY VARCHAR2,
3641 p_xmlg_transaction_type IN OUT NOCOPY VARCHAR2,
3642 p_xmlg_transaction_subtype IN OUT NOCOPY VARCHAR2,
3643 p_xmlg_int_transaction_type IN OUT NOCOPY VARCHAR2,
3644 p_xmlg_int_transaction_subtype IN OUT NOCOPY VARCHAR2,
3645 p_xmlg_document_id IN OUT NOCOPY VARCHAR2,
3646 p_doc_dir IN OUT NOCOPY VARCHAR2,
3647 p_tr_partner_type IN OUT NOCOPY VARCHAR2,
3648 p_tr_partner_id IN OUT NOCOPY VARCHAR2,
3649 p_tr_partner_site IN OUT NOCOPY VARCHAR2,
3650 p_sender_component IN OUT NOCOPY VARCHAR2,
3651 p_xml_event_key IN OUT NOCOPY VARCHAR2,
3652 p_collaboration_standard IN OUT NOCOPY VARCHAR2)
3653
3654 IS
3655
3656 l_error_code NUMBER;
3657 l_msg_data VARCHAR2(2000);
3658 l_error_msg VARCHAR2(2000);
3659 l_debug_mode VARCHAR2(255);
3660 l_xmlg_internal_control_number NUMBER;
3661 l_xmlg_msg_id VARCHAR2(100);
3662 l_xmlg_transaction_type VARCHAR2(100);
3663 l_xmlg_transaction_subtype VARCHAR2(100);
3664 l_xmlg_int_transaction_type VARCHAR2(100);
3665 l_xmlg_int_transaction_subtype VARCHAR2(100);
3666 l_xmlg_document_id VARCHAR2(256);
3667 l_collaboration_standard VARCHAR2(30);
3668
3669 l_xml_event_key VARCHAR2(240);
3670
3671 l_doc_dir VARCHAR2(240);
3672 l_tr_partner_type VARCHAR2(30);
3673 l_tr_partner_id VARCHAR2(256);
3674 l_tr_partner_site VARCHAR2(256);
3675
3676 l_txn_partner_type VARCHAR2(30);
3677 l_txn_partner_id VARCHAR2(256);
3678 l_txn_partner_site VARCHAR2(256);
3679
3680 l_sender_component VARCHAR2(500);
3681 l_xmlg_msg_standard VARCHAR2(100);
3682 l_xmlg_msg_type VARCHAR2(100);
3683 l_enhanced_combination_key BOOLEAN DEFAULT FALSE;
3684
3685 BEGIN
3686
3687 -- Sets the debug mode to be FILE
3688 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
3689
3690 IF (l_Debug_Level <= 2) THEN
3691 ecx_cln_debug_pub.Add('------ Entering GET_TRADING_PARTNER_DETAILS API ------ ',2);
3692 END IF;
3693
3694
3695 -- Initialize API return status to success
3696 x_return_status := FND_API.G_RET_STS_SUCCESS;
3697
3698 -- get the paramaters passed
3699 IF (l_Debug_Level <= 1) THEN
3700 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
3701 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
3702 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
3706 ecx_cln_debug_pub.Add('XMLG INTERNAL TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
3703 ecx_cln_debug_pub.Add('XMLG EXTERNAL TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
3704 ecx_cln_debug_pub.Add('XMLG EXTERNAL TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
3705 ecx_cln_debug_pub.Add('XMLG INTERNAL TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
3707 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
3708 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
3709 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||p_tr_partner_type,1);
3710 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||p_tr_partner_id,1);
3711 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||p_tr_partner_site,1);
3712 ecx_cln_debug_pub.Add('SENDER COMPONENT ----- >>>'||p_sender_component,1);
3713 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
3714 ecx_cln_debug_pub.Add('Collab Standard ----- >>>'||p_collaboration_standard,1);
3715 ecx_cln_debug_pub.Add('===========================================',1);
3716 END IF;
3717
3718
3719
3720 -- assigning values to local variables for transaction tyoe/subtype
3721 l_xmlg_transaction_type := p_xmlg_transaction_type;
3722 l_xmlg_transaction_subtype := p_xmlg_transaction_subtype;
3723 l_xmlg_msg_standard := p_collaboration_standard;
3724
3725 -- Check if we need to call an API to get the trading partner set up details
3726 IF ( (p_xmlg_internal_control_number IS NULL)
3727 OR (p_xmlg_msg_id IS NULL)
3728 OR (p_xmlg_transaction_type IS NULL)
3729 OR (p_xmlg_transaction_subtype IS NULL)
3730 OR (p_xmlg_document_id IS NULL)
3731 OR (p_doc_dir IS NULL)
3732 OR (p_tr_partner_type IS NULL)
3733 OR (p_tr_partner_id IS NULL)
3734 OR (p_tr_partner_site IS NULL)
3735 OR (p_sender_component IS NULL)
3736 OR (p_xml_event_key IS NULL)
3737 OR (p_collaboration_standard IS NULL)
3738 ) THEN
3739
3740 BEGIN
3741 IF (l_Debug_Level <= 1) THEN
3742 ecx_cln_debug_pub.Add('-- Before SQL query to find the trading partner set up from ECX_DOCLOGS --',1);
3743 END IF;
3744
3745
3746 IF (p_xmlg_msg_id is not null ) THEN
3747 SELECT internal_control_number, msgid, transaction_type, transaction_subtype,
3748 document_number, party_type, partyid, party_site_id, direction, protocol_type,
3749 message_standard, message_type, event_key
3750 INTO l_xmlg_internal_control_number, l_xmlg_msg_id, l_xmlg_transaction_type, l_xmlg_transaction_subtype,
3751 l_xmlg_document_id, l_tr_partner_type, l_tr_partner_id, l_tr_partner_site, l_doc_dir, l_sender_component,
3752 l_xmlg_msg_standard, l_xmlg_msg_type, l_xml_event_key
3753 FROM ECX_DOCLOGS
3754 WHERE MSGID = HEXTORAW(p_xmlg_msg_id);
3755 ELSIF (p_xmlg_internal_control_number is not null ) THEN
3756 SELECT internal_control_number, msgid, transaction_type, transaction_subtype,
3757 document_number, party_type, partyid, party_site_id, direction, protocol_type,
3758 message_standard, message_type, event_key
3759 INTO l_xmlg_internal_control_number, l_xmlg_msg_id, l_xmlg_transaction_type,
3760 l_xmlg_transaction_subtype, l_xmlg_document_id, l_tr_partner_type, l_tr_partner_id,
3761 l_tr_partner_site, l_doc_dir, l_sender_component, l_xmlg_msg_standard, l_xmlg_msg_type, l_xml_event_key
3762 FROM ECX_DOCLOGS
3763 WHERE INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number;
3764 ELSIF (p_xmlg_transaction_type is not null and p_xmlg_transaction_subtype is not null and p_xmlg_document_id IS not NULL) THEN
3765 SELECT internal_control_number, msgid, transaction_type, transaction_subtype,
3766 document_number, party_type, partyid, party_site_id, direction, protocol_type,
3767 message_standard, message_type, event_key
3768 INTO l_xmlg_internal_control_number, l_xmlg_msg_id, l_xmlg_transaction_type,
3769 l_xmlg_transaction_subtype, l_xmlg_document_id, l_tr_partner_type, l_tr_partner_id,
3770 l_tr_partner_site, l_doc_dir, l_sender_component, l_xmlg_msg_standard, l_xmlg_msg_type, l_xml_event_key
3771 FROM ECX_DOCLOGS
3772 WHERE transaction_type = p_xmlg_transaction_type
3773 AND transaction_subtype = p_xmlg_transaction_subtype
3774 AND document_number = p_xmlg_document_id
3775 AND direction = NVL(p_doc_dir,direction)
3776 AND (event_key is null or p_xml_event_key is null or event_key = p_xml_event_key);
3777 ELSIF (p_xmlg_int_transaction_type is not null and p_xmlg_int_transaction_subtype is not null and p_xmlg_document_id IS not NULL) THEN
3781 INTO l_xmlg_internal_control_number, l_xmlg_msg_id, l_xmlg_transaction_type, l_xmlg_transaction_subtype,
3778 SELECT doclogs.internal_control_number, doclogs.msgid, doclogs.transaction_type, doclogs.transaction_subtype,
3779 doclogs.document_number, doclogs.party_type, doclogs.partyid, doclogs.party_site_id, doclogs.direction,
3780 doclogs.protocol_type, doclogs.message_standard, doclogs.message_type, doclogs.event_key
3782 l_xmlg_document_id, l_tr_partner_type, l_tr_partner_id,l_tr_partner_site, l_doc_dir,
3783 l_sender_component, l_xmlg_msg_standard, l_xmlg_msg_type, l_xml_event_key
3784 FROM ECX_TRANSACTIONS ecxtrans, ECX_EXT_PROCESSES ecxproc, ECX_DOCLOGS doclogs
3785 WHERE ecxtrans.TRANSACTION_TYPE = NVL(p_xmlg_int_transaction_type,l_xmlg_int_transaction_type)
3786 AND ecxtrans.TRANSACTION_SUBTYPE = NVL(p_xmlg_int_transaction_subtype,l_xmlg_int_transaction_subtype)
3787 AND ecxproc.TRANSACTION_ID = ecxtrans.TRANSACTION_ID
3788 AND ecxproc.DIRECTION = NVL(p_doc_dir,ecxproc.direction)
3789 AND ecxproc.DIRECTION = doclogs.DIRECTION
3790 AND (event_key is null or p_xml_event_key is null or event_key = p_xml_event_key)
3791 AND doclogs.transaction_type = ecxproc.EXT_TYPE
3792 AND doclogs.transaction_subtype = ecxproc.EXT_SUBTYPE
3793 AND doclogs.document_number = p_xmlg_document_id;
3794 END IF;
3795 IF (l_Debug_Level <= 1) THEN
3796 ecx_cln_debug_pub.Add('-- After SQL query to find the trading partner set up from ECX_DOCLOGS --',1);
3797 END IF;
3798
3799
3800 EXCEPTION
3801 WHEN NO_DATA_FOUND THEN
3802 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
3803 x_msg_data := FND_MESSAGE.GET;
3804 ecx_cln_debug_pub.Add('Unable to find the set up details for the trading partner',1);
3805 WHEN TOO_MANY_ROWS THEN
3806 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
3807 x_msg_data := FND_MESSAGE.GET;
3808 ecx_cln_debug_pub.Add('More then one row found for the same trading partner set up',1);
3809 END;
3810
3811 IF (l_Debug_Level <= 1) THEN
3812 ecx_cln_debug_pub.Add('==========Parameters Received From ECX_DOCLOG=============',1);
3813 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||l_xmlg_internal_control_number,1);
3814 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||l_xmlg_msg_id,1);
3815 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||l_xmlg_transaction_type,1);
3816 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||l_xmlg_transaction_subtype,1);
3817 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||l_xmlg_document_id,1);
3818 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||l_doc_dir,1);
3819 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||l_tr_partner_type,1);
3820 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||l_tr_partner_id,1);
3821 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||l_tr_partner_site,1);
3822 ecx_cln_debug_pub.Add('SENDER COMPONENT ----- >>>'||l_sender_component,1);
3823 ecx_cln_debug_pub.Add('XMLG MESSAGE STANDARD ID ----- >>>'||l_xmlg_msg_standard,1);
3824 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||l_xml_event_key,1);
3825 ecx_cln_debug_pub.Add('===========================================',1);
3826 END IF;
3827
3828
3829 -- Getting External Transaction type and Subtype associated with Internal transaction type
3830 -- and Internal transaction subtype
3831 IF ((l_xmlg_transaction_type IS NULL) OR (l_xmlg_transaction_subtype IS NULL) OR (l_xmlg_msg_standard IS NULL ) ) THEN
3832 IF (l_Debug_Level <= 1) THEN
3833 ecx_cln_debug_pub.Add('Getting values for External Transaction type and SubType and msg standard',1);
3834 END IF;
3835
3836 BEGIN
3837 SELECT ecxproc.EXT_TYPE,ecxproc.EXT_SUBTYPE, estd.standard_code
3838 INTO l_xmlg_transaction_type, l_xmlg_transaction_subtype, l_xmlg_msg_standard
3839 FROM ecx_tp_headers eth, ecx_tp_details etd, ECX_TRANSACTIONS ecxtrans, ECX_EXT_PROCESSES ecxproc, ecx_standards estd
3840 WHERE eth.party_id = p_tr_partner_id
3841 AND eth.party_site_id = p_tr_partner_site
3842 AND eth.party_type = nvl(l_tr_partner_type, eth.party_type)
3843 AND eth.tp_header_id = etd.tp_header_id
3844 AND etd.ext_process_id = ecxproc.ext_process_id
3845 AND ecxtrans.transaction_id = ecxproc.transaction_id
3846 AND ecxtrans.transaction_type = nvl(p_xmlg_int_transaction_type,l_xmlg_int_transaction_type)
3847 AND ecxtrans.transaction_subtype = nvl(p_xmlg_int_transaction_subtype,l_xmlg_int_transaction_subtype)
3851 IF (l_Debug_Level <= 1) THEN
3848 AND ecxproc.direction = nvl(p_doc_dir,ecxproc.direction)
3849 AND estd.standard_id = ecxproc.standard_id;
3850
3852 ecx_cln_debug_pub.Add('====Parameters Received From ECX_TRANSACTIONS/ECX_EXT_PROCESSES====',1);
3853 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||l_xmlg_transaction_type,1);
3854 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||l_xmlg_transaction_subtype,1);
3855 ecx_cln_debug_pub.Add('XMLG Message Standard ----- >>>'||l_xmlg_msg_standard,1);
3856 ecx_cln_debug_pub.Add('==================================================================',1);
3857 END IF;
3858
3859
3860
3861 EXCEPTION
3862 WHEN NO_DATA_FOUND THEN
3863 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TRANSACTION_NOT_FOUND');
3864 x_msg_data := FND_MESSAGE.GET;
3865 IF (l_Debug_Level <= 1) THEN
3866 ecx_cln_debug_pub.Add('Unable to find External Transaction Type/ Subtype',1);
3867 END IF;
3868
3869
3870 WHEN TOO_MANY_ROWS THEN
3871 FND_MESSAGE.SET_NAME('CLN','CLN_CH_EXCESS_TXN_FOUND');
3872 x_msg_data := SUBSTR(FND_MESSAGE.GET,10);
3873 IF (l_Debug_Level <= 1) THEN
3874 ecx_cln_debug_pub.Add('More then one row found for the same transaction detail',1);
3875 END IF;
3876
3877 END;
3878 END IF;
3879
3880 /* ***** NOT REQUIRED *******
3881 -- Getting Internal Transaction type and Subtype associated with External transaction type
3882 -- and External transaction subtype
3883 IF ((p_xmlg_int_transaction_type IS NULL) OR (p_xmlg_int_transaction_subtype IS NULL)) THEN
3884 ecx_cln_debug_pub.Add('Getting values for Internal Transaction type and SubType',1);
3885 BEGIN
3886 SELECT distinct ecxtrans.TRANSACTION_TYPE, ecxtrans.TRANSACTION_SUBTYPE
3887 INTO l_xmlg_int_transaction_type, l_xmlg_int_transaction_subtype
3888 FROM ECX_TRANSACTIONS ecxtrans, ECX_EXT_PROCESSES ecxproc
3889 WHERE ecxtrans.TRANSACTION_ID = ecxproc.TRANSACTION_ID
3890 AND ecxproc.EXT_TYPE = NVL(p_xmlg_transaction_type,l_xmlg_transaction_type)
3891 AND ecxproc.EXT_SUBTYPE = NVL(p_xmlg_transaction_subtype,l_xmlg_transaction_subtype)
3892 AND ecxproc.DIRECTION = NVL(p_doc_dir,l_doc_dir);
3893
3894 ecx_cln_debug_pub.Add('====Parameters Received From ECX_TRANSACTIONS/ECX_EXT_PROCESSES====',1);
3895 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||l_xmlg_int_transaction_type,1);
3896 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||l_xmlg_int_transaction_subtype,1);
3897 ecx_cln_debug_pub.Add('==================================================================',1);
3898
3899 EXCEPTION
3900 WHEN NO_DATA_FOUND THEN
3901 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TRANSACTION_NOT_FOUND');
3902 x_msg_data := FND_MESSAGE.GET;
3903 ecx_cln_debug_pub.Add('Unable to find Internal Transaction Type/ Subtype',1);
3904
3905 WHEN TOO_MANY_ROWS THEN
3906 FND_MESSAGE.SET_NAME('CLN','CLN_CH_EXCESS_TXN_FOUND');
3907 x_msg_data := SUBSTR(FND_MESSAGE.GET,10);
3908 ecx_cln_debug_pub.Add('More then one row found for the same transaction detail',1);
3909 END;
3910 END IF;
3911 */
3912
3913
3914
3915 -- Assingning non null values to the input parameters
3916 p_xmlg_internal_control_number := NVL(p_xmlg_internal_control_number,l_xmlg_internal_control_number);
3917 p_xmlg_msg_id := NVL(p_xmlg_msg_id,l_xmlg_msg_id);
3918 p_xmlg_transaction_type := NVL(p_xmlg_transaction_type,l_xmlg_transaction_type);
3919 p_xmlg_transaction_subtype := NVL(p_xmlg_transaction_subtype,l_xmlg_transaction_subtype);
3920 p_xmlg_int_transaction_type := NVL(p_xmlg_int_transaction_type,l_xmlg_int_transaction_type);
3921 p_xmlg_int_transaction_subtype := NVL(p_xmlg_int_transaction_subtype,l_xmlg_int_transaction_subtype);
3922 p_xmlg_document_id := NVL(p_xmlg_document_id,l_xmlg_document_id);
3923 p_doc_dir := NVL(p_doc_dir,l_doc_dir);
3924 p_sender_component := NVL(p_sender_component,l_sender_component);
3925 p_xml_event_key := NVL(p_xml_event_key, l_xml_event_key);
3926 p_collaboration_standard := NVL(p_collaboration_standard, l_xmlg_msg_standard);
3927
3928 IF (p_tr_partner_type IS NULL OR p_tr_partner_id IS NULL OR p_tr_partner_site IS NULL or l_xmlg_msg_standard is null )
3929 THEN -- based on the ecx doclogs values we have to get actual TP values
3930 BEGIN
3934
3931 IF (l_Debug_Level <= 1) THEN
3932 ecx_cln_debug_pub.Add('-- Before SQL query to find the trading partner set up from ecx_ext_processes /ecx_tp_details /ecx_tp_headers',1);
3933 END IF;
3935
3936 SELECT eth.party_id, eth.party_site_id, eth.party_type, estd.standard_code
3937 INTO l_txn_partner_id, l_txn_partner_site, l_txn_partner_type, l_xmlg_msg_standard
3938 FROM ecx_ext_processes eep, ecx_tp_details etd, ecx_tp_headers eth, ecx_standards estd
3939 WHERE eep.ext_type = l_xmlg_transaction_type
3940 AND eep.ext_subtype = l_xmlg_transaction_subtype
3941 AND eep.standard_id = estd.standard_id
3942 AND estd.standard_code = l_xmlg_msg_standard
3943 AND eep.ext_process_id = etd.ext_process_id
3944 AND etd.source_tp_location_code = l_tr_partner_site
3945 AND eep.direction = l_doc_dir
3946 AND eth.party_type = NVL(l_tr_partner_type,eth.party_type)
3947 AND eth.tp_header_id = etd.tp_header_id
3948 AND estd.standard_type = l_xmlg_msg_type;
3949
3950 IF (l_Debug_Level <= 1) THEN
3951 ecx_cln_debug_pub.Add('-- After SQL query ----',1);
3952 END IF;
3953
3954
3955 IF (l_Debug_Level <= 1) THEN
3956 ecx_cln_debug_pub.Add('====Trading Partner Parameters Changed To IDs Using ecx_ext_processes /ecx_tp_details /ecx_tp_headers =====',1);
3957 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||l_txn_partner_id,1);
3958 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||l_txn_partner_site,1);
3959 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||l_txn_partner_type,1);
3960 ecx_cln_debug_pub.Add('Message Standard ----- >>>'||l_xmlg_msg_standard,1);
3961 ecx_cln_debug_pub.Add('===========================================================================',1);
3962 END IF;
3963
3964
3965 EXCEPTION
3966 WHEN NO_DATA_FOUND THEN
3967 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
3968 x_msg_data := FND_MESSAGE.GET;
3969 IF (l_Debug_Level <= 1) THEN
3970 ecx_cln_debug_pub.Add('Unable to find the id details for the trading partner',1);
3971 ecx_cln_debug_pub.Add('Trying to find details without considering party type',1);
3972 END IF;
3973
3974 BEGIN
3975 SELECT eth.party_id, eth.party_site_id, eth.party_type, estd.standard_code
3976 INTO l_txn_partner_id, l_txn_partner_site, l_txn_partner_type, l_xmlg_msg_standard
3977 FROM ecx_ext_processes eep, ecx_tp_details etd, ecx_tp_headers eth, ecx_standards estd
3978 WHERE eep.ext_type = l_xmlg_transaction_type
3979 AND eep.ext_subtype = l_xmlg_transaction_subtype
3980 AND eep.standard_id = estd.standard_id
3981 AND estd.standard_code = l_xmlg_msg_standard
3982 AND eep.ext_process_id = etd.ext_process_id
3983 AND etd.source_tp_location_code = l_tr_partner_site
3984 AND eep.direction = l_doc_dir
3985 AND eth.tp_header_id = etd.tp_header_id
3986 AND estd.standard_type = l_xmlg_msg_type;
3987 EXCEPTION
3988 WHEN OTHERS THEN
3989 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
3990 x_msg_data := FND_MESSAGE.GET;
3991 IF (l_Debug_Level <= 1) THEN
3992 ecx_cln_debug_pub.Add('Event without TP, unable to find the id details for the trading partner',1);
3993 END IF;
3994 END;
3995
3996
3997 WHEN TOO_MANY_ROWS THEN
3998 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
3999 x_msg_data := FND_MESSAGE.GET;
4000 IF (l_Debug_Level <= 1) THEN
4001 ecx_cln_debug_pub.Add('More then one row found for the same trading partner set up',1);
4002 END IF;
4003
4004 END;
4005 p_tr_partner_type := NVL(p_tr_partner_type,l_txn_partner_type);
4006 p_tr_partner_id := NVL(p_tr_partner_id,l_txn_partner_id);
4007 p_tr_partner_site := NVL(p_tr_partner_site,l_txn_partner_site);
4008 p_collaboration_standard := NVL(p_collaboration_standard,l_xmlg_msg_standard);
4009 END IF;
4010 ELSE
4014 IF (l_Debug_Level <= 1) THEN
4011 x_return_status := FND_API.G_RET_STS_SUCCESS;
4012 FND_MESSAGE.SET_NAME('CLN','CLN_CH_API_CALL_NOT_REQD');
4013 x_msg_data := FND_MESSAGE.GET;
4015 ecx_cln_debug_pub.Add('API - GET_TRADING_PARTNER_DETAILS need not be called as all input parameters are having non-null values ',1);
4016 END IF;
4017
4018 RETURN;
4019 END IF;
4020
4021 -- values obtained after the query to ecx_doclogs table
4022 IF (l_Debug_Level <= 1) THEN
4023 ecx_cln_debug_pub.Add('====Parameters Just Before Returning To the Main Calling Procedure======',1);
4024 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
4025 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
4026 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
4027 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUB TYPE ----- >>>'||p_xmlg_transaction_subtype,1);
4028 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
4029 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUB TYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
4030 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
4031 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
4032 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||p_tr_partner_type,1);
4033 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||p_tr_partner_id,1);
4034 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||p_tr_partner_site,1);
4035 ecx_cln_debug_pub.Add('SENDER COMPONENT ----- >>>'||p_sender_component,1);
4036 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
4037 ecx_cln_debug_pub.Add('Message Standard ----- >>>'||p_collaboration_standard,1);
4038 ecx_cln_debug_pub.Add('=============================================================',1);
4039 END IF;
4040
4041
4042
4043 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS');
4044 x_msg_data := FND_MESSAGE.GET;
4045
4046 l_msg_data := 'Successfully retrieved values for the Trading partner';
4047 IF (l_Debug_Level <= 1) THEN
4048 ecx_cln_debug_pub.Add(l_msg_data,1);
4049 END IF;
4050
4051 IF (l_Debug_Level <= 2) THEN
4052 ecx_cln_debug_pub.Add('------ Exiting GET_TRADING_PARTNER_DETAILS ------- ',2);
4053 END IF;
4054
4055 EXCEPTION
4056
4057 WHEN OTHERS THEN
4058 l_error_code :=SQLCODE;
4059 l_error_msg :=SQLERRM;
4060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- sending back success for backward compatibility
4061 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
4062 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
4063 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
4064 x_msg_data :=FND_MESSAGE.GET;
4065 l_msg_data := 'Unexpected Error : '||l_error_code||'-'||l_error_msg;
4066 IF (l_Debug_Level <= 6) THEN
4067 ecx_cln_debug_pub.Add(l_msg_data,6);
4068 ecx_cln_debug_pub.Add('------ Exiting GET_TRADING_PARTNER_DETAILS ------- ',2);
4069 END IF;
4070
4071 END GET_TRADING_PARTNER_DETAILS;
4072
4073
4074 -- Name
4075 -- DEFAULT_XMLGTXN_MAPPING
4076 -- Purpose
4077 -- This is the public procedure which returns the application id for a given set of
4078 -- parameters passed while refering to teh CLN_CH_XMLGTXN_MAPPING.
4079 -- Arguments
4080 --
4081 -- Notes
4082 -- No specific notes.
4083
4084 PROCEDURE DEFAULT_XMLGTXN_MAPPING(
4085 x_return_status OUT NOCOPY VARCHAR2,
4086 x_msg_data OUT NOCOPY VARCHAR2,
4087 p_xmlg_transaction_type IN VARCHAR2,
4088 p_xmlg_transaction_subtype IN VARCHAR2,
4089 p_doc_dir IN VARCHAR2,
4090 p_app_id IN OUT NOCOPY VARCHAR2 ,
4091 p_coll_type IN OUT NOCOPY VARCHAR2,
4092 p_doc_type IN OUT NOCOPY VARCHAR2 )
4093
4094 IS
4095
4096 l_error_code NUMBER;
4097 l_msg_data VARCHAR2(2000);
4098 l_error_msg VARCHAR2(2000);
4099 l_debug_mode VARCHAR2(255);
4100 l_application_id NUMBER;
4101 l_collaboration_type VARCHAR2(30);
4102 l_document_type VARCHAR2(100);
4103
4104
4105 BEGIN
4106
4107 -- Sets the debug mode to be FILE
4108 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
4109
4110 IF (l_Debug_Level <= 2) THEN
4111 ecx_cln_debug_pub.Add('------ Entering DEFAULT_XMLGTXN_MAPPING API ------ ',2);
4112 END IF;
4113
4114
4115 -- Initialize API return status to success
4116 x_return_status := FND_API.G_RET_STS_SUCCESS;
4117
4118 -- get the paramaters passed
4119 IF (l_Debug_Level <= 1) THEN
4120 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
4124 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
4121 ecx_cln_debug_pub.Add('XMLG TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
4122 ecx_cln_debug_pub.Add('XMLG TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
4123 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
4125 ecx_cln_debug_pub.Add('COLL TYPE ----- >>>'||p_coll_type,1);
4126 ecx_cln_debug_pub.Add('DOCUMENT TYPE ----- >>>'||p_doc_type,1);
4127 ecx_cln_debug_pub.Add('==========================================',1);
4128 END IF;
4129
4130
4131
4132 --check for the reqd parameters for this API
4133 IF ( ( p_xmlg_transaction_type IS NULL)
4134 OR (p_xmlg_transaction_subtype IS NULL)
4135 OR (p_doc_dir IS NULL)
4136 ) THEN
4137 l_msg_data := 'Required parameters(p_xmlg_transaction_type/ p_xmlg_transaction_subtype / p_xmlg_document_direction) missing';
4138 IF (l_Debug_Level <= 1) THEN
4139 ecx_cln_debug_pub.Add(l_msg_data,1);
4140 END IF;
4141
4142 FND_MESSAGE.SET_NAME('CLN','CLN_CH_REQD_KEY_MISSING');
4143 FND_MESSAGE.SET_TOKEN('API','DEFAULT_XMLGTXN_MAPPING');
4144 x_msg_data := FND_MESSAGE.GET;
4145 x_return_status := FND_API.G_RET_STS_SUCCESS;
4146 RETURN;
4147 END IF;
4148
4149
4150 -- Get the trading partner details
4151 IF ((p_app_id IS NULL) OR (p_coll_type IS NULL) OR (p_doc_type IS NULL)) THEN
4152 BEGIN
4153 IF (l_Debug_Level <= 1) THEN
4154 ecx_cln_debug_pub.Add('-- Before sql query to CLN_CH_XMLGTXN_MAPPING --',1);
4155 END IF;
4156
4157 SELECT application_id, collaboration_type, document_type
4158 INTO l_application_id, l_collaboration_type, l_document_type
4159 FROM CLN_CH_XMLGTXN_MAPPING
4160 WHERE XMLG_TRANSACTION_TYPE = p_xmlg_transaction_type
4161 AND XMLG_TRANSACTION_SUBTYPE = p_xmlg_transaction_subtype
4162 AND DOCUMENT_DIRECTION = p_doc_dir ;
4163 IF (l_Debug_Level <= 1) THEN
4164 ecx_cln_debug_pub.Add('-- After sql query to CLN_CH_XMLGTXN_MAPPING --',1);
4165 END IF;
4166
4167
4168 EXCEPTION
4169 WHEN NO_DATA_FOUND THEN
4170 -- Here return status is passed as success just because the notification processing module
4171 -- has to create an error collaboration incase setup information is wrong or xmlg gives error
4172 -- while processing the xml document.
4173 l_msg_data := 'Unable to find the default xmlg mapping values for the application';
4174 IF (l_Debug_Level <= 1) THEN
4175 ecx_cln_debug_pub.Add(l_msg_data,1);
4176 END IF;
4177
4178 FND_MESSAGE.SET_NAME('CLN','CLN_CH_XMLGTXN_MAPPING_NF');
4179 x_msg_data := FND_MESSAGE.GET;
4180 x_return_status := FND_API.G_RET_STS_SUCCESS;
4181 RETURN;
4182 END;
4183 END IF;
4184
4185
4186 -- Assingning non null values to the input parameters
4187 p_app_id := NVL(p_app_id,l_application_id);
4188 p_coll_type := NVL(p_coll_type,l_collaboration_type);
4189 p_doc_type := NVL(p_doc_type,l_document_type);
4190
4191
4192 -- values obtained after the query to ecx_doclogs table
4193 IF (l_Debug_Level <= 1) THEN
4194 ecx_cln_debug_pub.Add('==========Parameters After Query To ECX_DOCLOGS=============',1);
4195 ecx_cln_debug_pub.Add('APPLICATION ID ----- >>>'||p_app_id,1);
4196 ecx_cln_debug_pub.Add('COLLABORATION TYPE ----- >>>'||p_coll_type,1);
4197 ecx_cln_debug_pub.Add('COLLABORATION DOCUMENT TYPE ----- >>>'||p_doc_type,1);
4198 ecx_cln_debug_pub.Add('=============================================================',1);
4199 END IF;
4200
4201
4202 l_msg_data := 'Successfully retrieved default values from the CLN_XMLGTXN_MAPPING';
4203 IF (l_Debug_Level <= 1) THEN
4204 ecx_cln_debug_pub.Add(l_msg_data,1);
4205 ecx_cln_debug_pub.Add('------ Exiting DEFAULT_XMLGTXN_MAPPING ------- ',2);
4206 END IF;
4207
4208
4209
4210 EXCEPTION
4211
4212 WHEN FND_API.G_EXC_ERROR THEN
4213 x_return_status := FND_API.G_RET_STS_SUCCESS ;-- sending back success for backward compatibility
4214 IF (l_Debug_Level <= 4) THEN
4215 ecx_cln_debug_pub.Add(l_msg_data,4);
4216 ecx_cln_debug_pub.Add('------ ERROR--------');
4217 ecx_cln_debug_pub.Add('------ Exiting DEFAULT_XMLGTXN_MAPPING ------- ',2);
4218 END IF;
4219
4220
4221
4222 WHEN OTHERS THEN
4223 l_error_code :=SQLCODE;
4224 l_error_msg :=SQLERRM;
4228 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
4225 x_return_status := FND_API.G_RET_STS_SUCCESS ; -- sending back success for backward compatibility
4226 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
4227 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
4229 x_msg_data :=FND_MESSAGE.GET;
4230 l_msg_data := 'Unexpected Error : '||l_error_code||'-'||l_error_msg;
4231 IF (l_Debug_Level <= 5) THEN
4232 ecx_cln_debug_pub.Add(l_msg_data,6);
4233 ecx_cln_debug_pub.Add('------ Exiting DEFAULT_XMLGTXN_MAPPING ------- ',2);
4234 END IF;
4235
4236
4237 END DEFAULT_XMLGTXN_MAPPING;
4238
4239
4240 -- Name
4241 -- DEFAULT_COLLABORATION_STATUS
4242 -- Purpose
4243 -- This procedure defaults collaboration status based on the rules defined in
4244 -- CLN_COLL_STATUS_MAPPING table.
4245 -- Arguments
4246 --
4247 -- Notes
4248 -- No specific notes.
4249
4250
4251 PROCEDURE DEFAULT_COLLABORATION_STATUS(
4252 x_return_status OUT NOCOPY VARCHAR2,
4253 x_msg_data OUT NOCOPY VARCHAR2,
4254 x_coll_status IN OUT NOCOPY VARCHAR2,
4255 p_app_id IN VARCHAR2,
4256 p_coll_type IN VARCHAR2,
4257 p_doc_status IN VARCHAR2,
4258 p_doc_type IN VARCHAR2,
4259 p_doc_dir IN VARCHAR2,
4260 p_coll_id IN NUMBER,
4261 p_coll_standard IN VARCHAR2
4262 )
4263
4264 IS
4265 l_error_code NUMBER;
4266 l_error_msg VARCHAR2(2000);
4267 l_msg_data VARCHAR2(2000);
4268 l_debug_mode VARCHAR2(255);
4269 l_message_count NUMBER;
4270 l_msg_count_in_hist NUMBER;
4271
4272 BEGIN
4273
4274 -- Sets the debug mode to be FILE
4275 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
4276
4277 IF (l_Debug_Level <= 2) THEN
4278 ecx_cln_debug_pub.Add('------Entering DEFAULT_COLLABORATION_STATUS API---- ',2);
4279 END IF;
4280
4281
4282 -- Initialize API return status to success
4283 x_return_status := FND_API.G_RET_STS_SUCCESS;
4284
4285 FND_MESSAGE.SET_NAME('CLN','CLN_COLL_STATUS_FOUND');
4286
4287 IF (l_Debug_Level <= 1) THEN
4288 ecx_cln_debug_pub.Add('------------Parameters Received-------------',1);
4289 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
4290 ecx_cln_debug_pub.Add('COLL TYPE ----- >>>'||p_coll_type,1);
4291 ecx_cln_debug_pub.Add('COLL STANDARD ----- >>>'||p_coll_standard,1);
4292 ecx_cln_debug_pub.Add('DOC STATUS ----- >>>'||p_doc_status,1);
4293 ecx_cln_debug_pub.Add('DOC TYPE ----- >>>'||p_doc_type,1);
4294 ecx_cln_debug_pub.Add('DOC DIRECTION ----- >>>'||p_doc_dir,1);
4295 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
4296 ecx_cln_debug_pub.Add('---------------------------------------------',1);
4297 END IF;
4298
4299
4300
4301 IF (p_doc_status = 'ERROR') THEN
4302 x_coll_status := 'ERROR';
4303 FND_MESSAGE.SET_TOKEN('STATUS','ERROR');
4304 x_msg_data := FND_MESSAGE.GET;
4305 IF (l_Debug_Level <= 1) THEN
4306 ecx_cln_debug_pub.Add('Collaboration Status ----- >>>'||x_coll_status ,1);
4307 END IF;
4308
4309 IF (l_Debug_Level <= 2) THEN
4310 ecx_cln_debug_pub.Add('-------- Exiting DEFAULT_COLLABORATION_STATUS API ----- ',2);
4311 END IF;
4312
4313 RETURN;
4314 END IF;
4315
4316 x_coll_status := 'STARTED';
4317 BEGIN
4318 IF (l_Debug_Level <= 1) THEN
4319 ecx_cln_debug_pub.Add('----- Before querying the CLN_COLL_COMPLETED_STATUS ----',1);
4320 END IF;
4321
4322
4323 SELECT message_count
4324 INTO l_message_count
4325 FROM CLN_COLL_COMPLETED_STATUS
4326 WHERE application_id = p_app_id
4327 AND collaboration_type = p_coll_type
4328 AND nvl(collaboration_standard,nvl(p_coll_standard,'~')) = nvl(p_coll_standard,'~')
4329 AND document_type = p_doc_type
4330 AND document_direction = p_doc_dir;
4331
4332 IF (l_Debug_Level <= 1) THEN
4333 ecx_cln_debug_pub.Add('----- After querying the CLN_COLL_COMPLETED_STATUS ----',1);
4334 ecx_cln_debug_pub.Add('----- Message count : '||l_message_count,1);
4335 END IF;
4336
4337
4338 IF (l_message_count > 1) THEN
4339 -- We have to query coll and find out for howmany messages
4340 -- are already there. If they are greater than or equal to
4341 -- what is specified then the status is completed normal.
4342 IF (l_Debug_Level <= 1) THEN
4346
4343 ecx_cln_debug_pub.Add('---- Before querying the collaboration history ---',1);
4344 END IF;
4345
4347 SELECT count('x')
4348 INTO l_msg_count_in_hist
4349 FROM CLN_COLL_HIST_HDR hdr, CLN_COLL_HIST_DTL dtl
4350 WHERE hdr.collaboration_id = p_coll_id
4351 AND hdr.collaboration_id = dtl.collaboration_id
4352 AND hdr.application_id = p_app_id
4353 AND hdr.collaboration_type = p_coll_type
4354 AND dtl.collaboration_document_type = p_doc_type
4355 AND dtl.document_direction = p_doc_dir;
4356
4357 IF (l_Debug_Level <= 1) THEN
4358 ecx_cln_debug_pub.Add('---- After querying the collaboration history ---, count : ' || l_msg_count_in_hist ,1);
4359 END IF;
4360
4361
4362 IF (l_msg_count_in_hist >= l_message_count-1) THEN
4363 x_coll_status := 'COMPLETED';
4364 FND_MESSAGE.SET_TOKEN('STATUS','COMPLETED');
4365 x_msg_data := FND_MESSAGE.GET;
4366 END IF;
4367 ELSE
4368 x_coll_status := 'COMPLETED';
4369 FND_MESSAGE.SET_TOKEN('STATUS','COMPLETED');
4370 x_msg_data := FND_MESSAGE.GET;
4371 END IF;
4372
4373 EXCEPTION
4374 WHEN NO_DATA_FOUND THEN
4375 x_coll_status := 'STARTED';
4376 FND_MESSAGE.SET_TOKEN('STATUS','STARTED');
4377 x_msg_data := FND_MESSAGE.GET;
4378 END;
4379
4380 IF (l_Debug_Level <= 1) THEN
4381 ecx_cln_debug_pub.Add('Collaboration Status ----- >>>'||x_coll_status ,1);
4382 END IF;
4383
4384 IF (l_Debug_Level <= 2) THEN
4385 ecx_cln_debug_pub.Add('-------- Exiting DEFAULT_COLLABORATION_STATUS API ----- ',2);
4386 END IF;
4387
4388
4389 -- Exception Handling
4390 EXCEPTION
4391 WHEN OTHERS THEN
4392 l_error_code := SQLCODE;
4393 l_error_msg := SQLERRM;
4394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4395 x_msg_data := l_error_code||' : '||l_error_msg;
4396 IF (l_Debug_Level <= 6) THEN
4397 ecx_cln_debug_pub.Add(x_msg_data,6);
4398 ecx_cln_debug_pub.Add('-------- Exiting DEFAULT_COLLABORATION_STATUS API WITH ERROR----- ',2);
4399 END IF;
4400
4401
4402 END DEFAULT_COLLABORATION_STATUS;
4403
4404
4405
4406 -- Name
4407 -- FIND_COLLABORATION_ID
4408 -- Purpose
4409 -- This is the public procedure which may be used to get the collaboration id
4410 -- for a particular transaction.
4411 -- Arguments
4412 --
4413 -- Notes
4414 -- No specific notes.
4415
4416
4417 PROCEDURE FIND_COLLABORATION_ID(
4418 x_return_status OUT NOCOPY VARCHAR2,
4419 x_msg_data OUT NOCOPY VARCHAR2,
4420 x_coll_id OUT NOCOPY NUMBER,
4421 p_app_id IN VARCHAR2,
4422 p_coll_type IN VARCHAR2,
4423 p_ref_id IN VARCHAR2,
4424 p_xmlg_transaction_type IN VARCHAR2,
4425 p_xmlg_transaction_subtype IN VARCHAR2,
4426 p_xmlg_int_transaction_type IN VARCHAR2,--NOT USED FOR THIS RELEASE
4427 p_xmlg_int_transaction_subtype IN VARCHAR2,--NOT USED FOR THIS RELEASE
4428 p_tr_partner_id IN VARCHAR2,
4429 p_tr_partner_site IN VARCHAR2,
4430 p_tr_partner_type IN VARCHAR2,
4431 p_xmlg_document_id IN VARCHAR2,
4432 p_doc_dir IN VARCHAR2,
4433 p_xmlg_msg_id IN VARCHAR2,
4434 p_unique1 IN VARCHAR2,
4435 p_unique2 IN VARCHAR2,
4436 p_unique3 IN VARCHAR2,
4437 p_unique4 IN VARCHAR2,
4438 p_unique5 IN VARCHAR2,
4439 p_xmlg_internal_control_number IN NUMBER,
4440 p_xml_event_key IN VARCHAR2,
4441 p_collaboration_standard IN VARCHAR2)
4442 IS
4443 l_error_code NUMBER;
4444 l_error_msg VARCHAR2(2000);
4445 l_msg_data VARCHAR2(2000);
4446 l_debug_mode VARCHAR2(255);
4447 l_data_area_refid VARCHAR2(255);
4448 l_corrspnd_internal_cntrl_num NUMBER;
4449 BEGIN
4450
4451 -- Sets the debug mode to be FILE
4452 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
4453
4454
4455 IF (l_Debug_Level <= 2) THEN
4456 ecx_cln_debug_pub.Add('--------- Entering FIND_COLLABORATION_ID API ------------ ',2);
4457 END IF;
4458
4459
4460 -- Initialize API return status to success
4461 x_return_status := FND_API.G_RET_STS_SUCCESS;
4462 l_msg_data := 'Collaboration ID successfully found ';
4463
4464
4465 -- get the paramaters passed
4466 IF (l_Debug_Level <= 1) THEN
4467 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
4468 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
4469 ecx_cln_debug_pub.Add('REFERENCE ID ----- >>>'||p_ref_id,1);
4470 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
4471 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
4472 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
4473 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
4474 ecx_cln_debug_pub.Add('XMLG TRADING PARTNER ID ----- >>>'||p_tr_partner_id,1);
4475 ecx_cln_debug_pub.Add('XMLG TRADING PARTNER SITE ----- >>>'||p_tr_partner_site,1);
4476 ecx_cln_debug_pub.Add('XMLG TRADING PARTNER TYPE ----- >>>'||p_tr_partner_type,1);
4477 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
4478 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
4479 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
4480 ecx_cln_debug_pub.Add('UNIQUE 1 ----- >>>'||p_unique1,1);
4481 ecx_cln_debug_pub.Add('UNIQUE 2 ----- >>>'||p_unique2,1);
4482 ecx_cln_debug_pub.Add('UNIQUE 3 ----- >>>'||p_unique3,1);
4483 ecx_cln_debug_pub.Add('UNIQUE 4 ----- >>>'||p_unique4,1);
4484 ecx_cln_debug_pub.Add('UNIQUE 5 ----- >>>'||p_unique5,1);
4485 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
4486 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
4487 ecx_cln_debug_pub.Add('===========================================',1);
4488 END IF;
4489
4490
4491
4492 BEGIN
4493 IF (l_Debug_Level <= 1) THEN
4494 ecx_cln_debug_pub.Add('----- Query 1: Finding Collaboration ID from CLN_COLL_HIST_HDR table ----',1);
4495 END IF;
4496
4497 SELECT COLLABORATION_ID INTO x_coll_id
4498 FROM CLN_COLL_HIST_HDR
4499 WHERE (APPLICATION_REFERENCE_ID=p_ref_id)
4500 OR XMLG_MSG_ID = p_xmlg_msg_id
4501 OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number;
4502
4503 IF (l_Debug_Level <= 1) THEN
4504 ecx_cln_debug_pub.Add('Collaboration ID found as - '||x_coll_id,1);
4505 END IF;
4506
4507
4508 EXCEPTION
4509 WHEN TOO_MANY_ROWS THEN
4510 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNIQUE_COLLABORATION_NF');
4511 x_msg_data := FND_MESSAGE.GET;
4512 l_msg_data := 'Unique Collaboration Not Found';
4513 RAISE FND_API.G_EXC_ERROR;
4514 WHEN NO_DATA_FOUND THEN
4515 IF (l_Debug_Level <= 1) THEN
4516 cln_debug_pub.Add('Unable to find the collaboration in Collaboration History - Header Table',1);
4517 END IF;
4518 IF p_unique1 IS NOT NULL OR
4519 p_unique2 IS NOT NULL OR
4520 p_unique3 IS NOT NULL OR
4521 p_unique4 IS NOT NULL OR
4522 p_unique5 IS NOT NULL THEN
4523 BEGIN
4524 IF (l_Debug_Level <= 1) THEN
4525 ecx_cln_debug_pub.Add('----- Query 2: Finding Collaboration ID from CLN_COLL_HIST_HDR table ----',1);
4526 END IF;
4527
4528 SELECT COLLABORATION_ID INTO x_coll_id
4529 FROM CLN_COLL_HIST_HDR
4530 WHERE (APPLICATION_ID = p_app_id AND UNIQUE_ID1 = p_unique1)
4531 OR (APPLICATION_ID = p_app_id AND UNIQUE_ID2 = p_unique2)
4532 OR (APPLICATION_ID = p_app_id AND UNIQUE_ID3 = p_unique3)
4533 OR (APPLICATION_ID = p_app_id AND UNIQUE_ID4 = p_unique4)
4534 OR (APPLICATION_ID = p_app_id AND UNIQUE_ID5 = p_unique5);
4535
4536 IF (l_Debug_Level <= 1) THEN
4537 ecx_cln_debug_pub.Add('Collaboration ID found as - '||x_coll_id,1);
4538 END IF;
4539 EXCEPTION
4540 WHEN NO_DATA_FOUND THEN
4541
4542 IF (l_Debug_Level <= 1) THEN
4543 ecx_cln_debug_pub.Add('Unable to find the collaboration in Collaboration History - Header Table',1);
4544 END IF;
4545
4546 WHEN TOO_MANY_ROWS THEN
4547 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNIQUE_COLLABORATION_NF');
4548 x_msg_data := FND_MESSAGE.GET;
4549 l_msg_data := 'Unique Collaboration Not Found';
4550 RAISE FND_API.G_EXC_ERROR;
4551
4552 END;
4553 ELSE
4554 IF (l_Debug_Level <= 1) THEN
4555 cln_debug_pub.Add('Unique_id from 1 to 5 are NULL',1);
4556 END IF;
4557 END IF;
4558 END;
4559
4560 IF x_coll_id IS NOT NULL THEN
4561 IF (l_Debug_Level <= 1) THEN
4562 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_ID API ------- ',2);
4563 END IF;
4564
4565 RETURN;
4566 END IF;
4567
4568
4569 IF (l_Debug_Level <= 1) THEN
4570 ecx_cln_debug_pub.Add('----- Finding Collaboration ID from CLN_COLL_HIST_DTL table ----',1);
4571 END IF;
4572
4573 BEGIN
4574 SELECT COLLABORATION_ID INTO x_coll_id
4575 FROM CLN_COLL_HIST_DTL
4576 WHERE (XMLG_MSG_ID = p_xmlg_msg_id
4577 OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number)
4578 AND ROWNUM < 2;
4579 IF (l_Debug_Level <= 1) THEN
4580 ecx_cln_debug_pub.Add('Collaboration ID found as - '||x_coll_id,1);
4581 END IF;
4582
4583 EXCEPTION
4584 WHEN NO_DATA_FOUND THEN
4585 IF (l_Debug_Level <= 1) THEN
4586 ecx_cln_debug_pub.Add('Unable to find the collaboration in Collaboration History - Detail Table',1);
4587 END IF;
4588
4589 WHEN TOO_MANY_ROWS THEN
4590 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNIQUE_COLLABORATION_NF');
4591 x_msg_data := FND_MESSAGE.GET;
4592 l_msg_data := 'Unique Collaboration Not Found';
4593 RAISE FND_API.G_EXC_ERROR;
4594 END;
4595
4596 IF x_coll_id IS NOT NULL THEN
4597 IF (l_Debug_Level <= 2) THEN
4598 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_ID API ------- ',2);
4599 END IF;
4600
4601 RETURN;
4602 END IF;
4603
4604 IF (l_Debug_Level <= 1) THEN
4605 ecx_cln_debug_pub.Add('----- Finding Collaboration ID from CLN_COLL_HIST_HDR table using Transaction Type/SubType',1);
4606 END IF;
4607
4608 BEGIN
4609 SELECT COLLABORATION_ID INTO x_coll_id
4610 FROM CLN_COLL_HIST_HDR
4611 WHERE XMLG_TRANSACTION_TYPE = p_xmlg_transaction_type
4612 AND XMLG_TRANSACTION_SUBTYPE = p_xmlg_transaction_subtype
4613 AND XMLG_DOCUMENT_ID = p_xmlg_document_id
4614 AND DOCUMENT_DIRECTION = nvl(p_doc_dir, DOCUMENT_DIRECTION)
4615 AND (XML_EVENT_KEY is null or p_xml_event_key is null or XML_EVENT_KEY = p_xml_event_key);
4616 IF (l_Debug_Level <= 1) THEN
4617 ecx_cln_debug_pub.Add('Collaboration ID found as - '||x_coll_id,1);
4618 END IF;
4619
4620 EXCEPTION
4621 WHEN NO_DATA_FOUND THEN
4622 IF (l_Debug_Level <= 1) THEN
4623 ecx_cln_debug_pub.Add('Unable to find the collaboration in Collaboration History - Header Table using Transaction Type/SubType',1);
4624 END IF;
4625
4626 WHEN TOO_MANY_ROWS THEN
4627 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNIQUE_COLLABORATION_NF');
4628 x_msg_data := FND_MESSAGE.GET;
4629 l_msg_data := 'Unique Collaboration Not Found';
4630 RAISE FND_API.G_EXC_ERROR;
4631 END;
4632
4633
4634 IF x_coll_id IS NOT NULL THEN
4635 IF (l_Debug_Level <= 2) THEN
4636 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_ID API ------- ',2);
4637 END IF;
4638
4639 RETURN;
4643 IF (l_Debug_Level <= 1) THEN
4640 END IF;
4641
4642 IF (p_doc_dir = 'OUT') and (p_xmlg_msg_id IS NOT NULL) and (p_collaboration_standard is not null) THEN
4644 ecx_cln_debug_pub.Add('----- Finding Collaboration ID using the reference id of the payload and then FIELD7 of doclogs',1);
4645 END IF;
4646
4647 GET_DATA_AREA_REFID(p_xmlg_msg_id,p_collaboration_standard,l_data_area_refid, p_app_id, p_coll_type);
4648
4649 IF (l_Debug_Level <= 1) THEN
4650 ecx_cln_debug_pub.Add('Data area reference id got as : ' || l_data_area_refid ,1);
4651 END IF;
4652
4653 IF l_data_area_refid IS NOT NULL THEN
4654 IF (l_Debug_Level <= 1) THEN
4655 ecx_cln_debug_pub.Add('Trying to find collaboration based on data area reference id through ecx_doclogs' ,1);
4656 END IF;
4657
4658 BEGIN
4659 IF (l_Debug_Level <= 1) THEN
4660 ecx_cln_debug_pub.Add('Trying to get internal control number ',1);
4661 END IF;
4662
4663 SELECT doclogs.internal_control_number
4664 INTO l_corrspnd_internal_cntrl_num
4665 FROM ecx_doclogs doclogs, ecx_ext_processes eep, ecx_tp_details etd, ecx_tp_headers eth, ecx_standards estd
4666 WHERE doclogs.direction = 'IN' and doclogs.field7 = l_data_area_refid
4667 AND eep.ext_type = doclogs.transaction_type
4668 AND eep.ext_subtype = doclogs.transaction_subtype
4669 AND eep.standard_id = estd.standard_id
4670 AND estd.standard_code = doclogs.message_standard
4671 AND eep.ext_process_id = etd.ext_process_id
4672 AND etd.source_tp_location_code = doclogs.party_site_id
4673 AND eep.direction = doclogs.direction
4674 --AND eth.party_type = NVL(doclogs.party_type,eth.party_type)
4675 AND eth.tp_header_id = etd.tp_header_id
4676 AND estd.standard_type = doclogs.message_type
4677 AND eth.party_id = p_tr_partner_id
4678 AND eth.party_site_id = p_tr_partner_site
4679 AND eth.party_type = p_tr_partner_type;
4680 IF (l_Debug_Level <= 1) THEN
4681 ecx_cln_debug_pub.Add('Internal control number got : ' || l_corrspnd_internal_cntrl_num,1);
4682 END IF;
4683
4684
4685 IF (l_Debug_Level <= 1) THEN
4686 ecx_cln_debug_pub.Add('Trying to get collaboration id ',1);
4687 END IF;
4688
4689
4690 SELECT distinct COLLABORATION_ID INTO x_coll_id
4691 FROM cln_coll_hist_dtl
4692 WHERE XMLG_INTERNAL_CONTROL_NUMBER = l_corrspnd_internal_cntrl_num;
4693
4694 EXCEPTION
4695 WHEN NO_DATA_FOUND THEN
4696 IF (l_Debug_Level <= 1) THEN
4697 ecx_cln_debug_pub.Add('Unable to find the collaboration in Collaboration History - Header Table using Transaction Type/SubType',1);
4698 END IF;
4699
4700 WHEN TOO_MANY_ROWS THEN
4701 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNIQUE_COLLABORATION_NF');
4702 x_msg_data := FND_MESSAGE.GET;
4703 l_msg_data := 'Unique Collaboration Not Found';
4704 RAISE FND_API.G_EXC_ERROR;
4705 END;
4706 END IF;
4707 END IF;
4708
4709
4710 IF (l_Debug_Level <= 1) THEN
4711 ecx_cln_debug_pub.Add('Collaboration ID found as : '||x_coll_id,1);
4712 END IF;
4713
4714 IF (l_Debug_Level <= 2) THEN
4715 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_ID API ------- ',2);
4716 END IF;
4717
4718
4719
4720 EXCEPTION
4721
4722 WHEN FND_API.G_EXC_ERROR THEN
4723 x_return_status := FND_API.G_RET_STS_ERROR ;
4724 IF (l_Debug_Level <= 4) THEN
4725 ecx_cln_debug_pub.Add(l_msg_data,4);
4726 END IF;
4727
4728 l_msg_data :=l_error_code||' : '||l_error_msg;
4729 IF (l_Debug_Level <= 4) THEN
4730 ecx_cln_debug_pub.Add(l_msg_data,4);
4731 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_ID API ------- ',2);
4732 END IF;
4733
4734
4735 WHEN OTHERS THEN
4736 l_error_code :=SQLCODE;
4737 l_error_msg :=SQLERRM;
4738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4739 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
4740 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
4741 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
4742 x_msg_data :=FND_MESSAGE.GET;
4743 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
4744 IF (l_Debug_Level <= 4) THEN
4745 ecx_cln_debug_pub.Add(l_msg_data,4);
4746 ecx_cln_debug_pub.Add('------ Exiting FIND_COLLABORATION_ID API ------- ',2);
4747 END IF;
4748
4749
4750 END FIND_COLLABORATION_ID;
4751
4752 -- Name
4753 -- ADD_COLLABORATION
4754 -- Purpose
4755 -- This is the public procedure which decides whether the collaboration nneds to be created
4756 -- or updated.
4757 -- Arguments
4758 --
4759 -- Notes
4760 -- No specific notes.
4761
4762
4763 PROCEDURE ADD_COLLABORATION(
4764 x_return_status OUT NOCOPY VARCHAR2,
4765 x_msg_data OUT NOCOPY VARCHAR2,
4766 p_coll_id IN NUMBER,
4767 p_app_id IN VARCHAR2,
4768 p_ref_id IN VARCHAR2,
4769 p_rel_no IN VARCHAR2,
4770 p_doc_no IN VARCHAR2,
4771 p_doc_rev_no IN VARCHAR2,
4772 p_xmlg_transaction_type IN VARCHAR2,
4773 p_xmlg_transaction_subtype IN VARCHAR2,
4774 p_xmlg_document_id IN VARCHAR2,
4775 p_resend_flag IN VARCHAR2,
4776 p_resend_count IN NUMBER,
4777 p_disposition IN VARCHAR2,
4778 p_coll_status IN VARCHAR2,
4779 p_coll_type IN VARCHAR2,
4780 p_coll_pt IN VARCHAR2,
4781 p_doc_type IN VARCHAR2,
4782 p_doc_dir IN VARCHAR2,
4783 p_org_ref IN VARCHAR2,
4784 p_doc_status IN VARCHAR2,
4785 p_notification_id IN VARCHAR2,
4786 p_msg_text IN VARCHAR2,
4787 p_attr1 IN VARCHAR2,
4788 p_attr2 IN VARCHAR2,
4789 p_attr3 IN VARCHAR2,
4790 p_attr4 IN VARCHAR2,
4791 p_attr5 IN VARCHAR2,
4792 p_attr6 IN VARCHAR2,
4793 p_attr7 IN VARCHAR2,
4794 p_attr8 IN VARCHAR2,
4795 p_attr9 IN VARCHAR2,
4796 p_attr10 IN VARCHAR2,
4797 p_attr11 IN VARCHAR2,
4798 p_attr12 IN VARCHAR2,
4799 p_attr13 IN VARCHAR2,
4800 p_attr14 IN VARCHAR2,
4801 p_attr15 IN VARCHAR2,
4802 p_xmlg_msg_id IN VARCHAR2,
4803 p_unique1 IN VARCHAR2,
4804 p_unique2 IN VARCHAR2,
4808 p_tr_partner_type IN VARCHAR2,
4805 p_unique3 IN VARCHAR2,
4806 p_unique4 IN VARCHAR2,
4807 p_unique5 IN VARCHAR2,
4809 p_tr_partner_id IN VARCHAR2,
4810 p_tr_partner_site IN VARCHAR2,
4811 p_sender_component IN VARCHAR2,
4812 p_rosettanet_check_required IN BOOLEAN,
4813 x_dtl_coll_id OUT NOCOPY NUMBER,
4814 p_xmlg_internal_control_number IN NUMBER,
4815 p_partner_doc_no IN VARCHAR2,
4816 p_org_id IN NUMBER,
4817 p_init_date IN DATE,
4818 p_doc_creation_date IN DATE,
4819 p_doc_revision_date IN DATE,
4820 p_doc_owner IN VARCHAR2,
4821 p_xmlg_int_transaction_type IN VARCHAR2,
4822 p_xmlg_int_transaction_subtype IN VARCHAR2,
4823 p_xml_event_key IN VARCHAR2,
4824 p_collaboration_standard IN VARCHAR2,
4825 p_attribute1 IN VARCHAR2,
4826 p_attribute2 IN VARCHAR2,
4827 p_attribute3 IN VARCHAR2,
4828 p_attribute4 IN VARCHAR2,
4829 p_attribute5 IN VARCHAR2,
4830 p_attribute6 IN VARCHAR2,
4831 p_attribute7 IN VARCHAR2,
4832 p_attribute8 IN VARCHAR2,
4833 p_attribute9 IN VARCHAR2,
4834 p_attribute10 IN VARCHAR2,
4835 p_attribute11 IN VARCHAR2,
4836 p_attribute12 IN VARCHAR2,
4837 p_attribute13 IN VARCHAR2,
4838 p_attribute14 IN VARCHAR2,
4839 p_attribute15 IN VARCHAR2,
4840 p_dattribute1 IN DATE,
4841 p_dattribute2 IN DATE,
4842 p_dattribute3 IN DATE,
4843 p_dattribute4 IN DATE,
4844 p_dattribute5 IN DATE,
4845 p_owner_role IN VARCHAR2 )
4846 IS
4847 l_dtl_coll_id NUMBER;
4848 l_coll_id NUMBER;
4849 l_error_code NUMBER;
4850 l_error_msg VARCHAR2(2000);
4851 l_msg_data VARCHAR2(2000);
4852 l_debug_mode VARCHAR2(255);
4853 l_xmlg_transaction_type VARCHAR2(100);
4854 l_xmlg_transaction_subtype VARCHAR2(100);
4855 l_xmlg_int_transaction_type VARCHAR2(100);
4856 l_xmlg_int_transaction_subtype VARCHAR2(100);
4857 l_doc_dir VARCHAR2(240);
4858 l_xmlg_internal_control_number NUMBER;
4859 l_xmlg_msg_id VARCHAR2(100);
4860 l_xml_event_key VARCHAR2(240);
4861 l_xmlg_document_id VARCHAR2(256);
4862 l_tr_partner_type VARCHAR2(30);
4863 l_tr_partner_id VARCHAR2(256);
4864 l_tr_partner_site VARCHAR2(256);
4865 l_sender_component VARCHAR2(500);
4866 l_collaboration_standard VARCHAR2(30);
4867 l_app_id VARCHAR2(10);
4868 l_coll_type VARCHAR2(30);
4869 l_doc_type VARCHAR2(100);
4870
4871 BEGIN
4872
4873 -- Sets the debug mode to be FILE
4874 --l_debug_mode :=ecx_cln_debug_pub.Set_Debug_Mode('FILE');
4875
4876
4877 IF (l_Debug_Level <= 2) THEN
4878 ecx_cln_debug_pub.Add('--------- Entering ADD_COLLABORATION API ------------ ',2);
4879 END IF;
4880
4881
4882 -- Initialize API return status to success
4883 x_return_status := FND_API.G_RET_STS_SUCCESS;
4884 l_msg_data := 'Collaboration successfully created/updated ';
4885
4886
4887
4888 -- get the paramaters passed
4889 IF (l_Debug_Level <= 1) THEN
4890 ecx_cln_debug_pub.Add('==========Parameters Received=============',1);
4891 ecx_cln_debug_pub.Add('COLLABORATION ID ----- >>>'||p_coll_id,1);
4892 ecx_cln_debug_pub.Add('APPLCATION ID ----- >>>'||p_app_id,1);
4893 ecx_cln_debug_pub.Add('REFERENCE ID ----- >>>'||p_ref_id,1);
4894 ecx_cln_debug_pub.Add('RELEASE NUMBER ----- >>>'||p_rel_no,1);
4895 ecx_cln_debug_pub.Add('DOCUMENT NO ----- >>>'||p_doc_no,1);
4896 ecx_cln_debug_pub.Add('DOCUMENT REV. NO ----- >>>'||p_doc_rev_no,1);
4897 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>>'||p_xmlg_transaction_type,1);
4901 ecx_cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>>'||p_xmlg_document_id,1);
4898 ecx_cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_transaction_subtype,1);
4899 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>>'||p_xmlg_int_transaction_type,1);
4900 ecx_cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>>'||p_xmlg_int_transaction_subtype,1);
4902 ecx_cln_debug_pub.Add('RESENG FLAG ----- >>>'||p_resend_flag,1);
4903 ecx_cln_debug_pub.Add('RESEND COUNT ----- >>>'||p_resend_count,1);
4904 ecx_cln_debug_pub.Add('DISPOSITION ----- >>>'||p_disposition,1);
4905 ecx_cln_debug_pub.Add('COLLABORATION STATUS ----- >>>'||p_coll_status,1);
4906 ecx_cln_debug_pub.Add('COLLABORATION TYPE ----- >>>'||p_coll_type,1);
4907 ecx_cln_debug_pub.Add('DOCUMENT TYPE ----- >>>'||p_doc_type,1);
4908 ecx_cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>>'||p_doc_dir,1);
4909 ecx_cln_debug_pub.Add('COLLABORATION POINT ----- >>>'||p_coll_pt,1);
4910 ecx_cln_debug_pub.Add('ORIGINATOR REFERENCE ----- >>>'||p_org_ref,1);
4911 ecx_cln_debug_pub.Add('DOCUMENT STATUS ----- >>>'||p_doc_status,1);
4912 ecx_cln_debug_pub.Add('NOTIFICATION ID ----- >>>'||p_notification_id,1);
4913 ecx_cln_debug_pub.Add('MESSAGE TEST ----- >>>'||p_msg_text,1);
4914 ecx_cln_debug_pub.Add('XMLG MESSAGE ID ----- >>>'||p_xmlg_msg_id,1);
4915 ecx_cln_debug_pub.Add('UNIQUE 1 ----- >>>'||p_unique1,1);
4916 ecx_cln_debug_pub.Add('UNIQUE 2 ----- >>>'||p_unique2,1);
4917 ecx_cln_debug_pub.Add('UNIQUE 3 ----- >>>'||p_unique3,1);
4918 ecx_cln_debug_pub.Add('UNIQUE 4 ----- >>>'||p_unique4,1);
4919 ecx_cln_debug_pub.Add('UNIQUE 5 ----- >>>'||p_unique5,1);
4920 ecx_cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>>'||p_tr_partner_type,1);
4921 ecx_cln_debug_pub.Add('TRADING PARTNER ID ----- >>>'||p_tr_partner_id,1);
4922 ecx_cln_debug_pub.Add('TRADING PARTNER SITE ----- >>>'||p_tr_partner_site,1);
4923 ecx_cln_debug_pub.Add('SENDER COMPONENT ----- >>>'||p_sender_component,1);
4924 ecx_cln_debug_pub.Add('XMLG INTERNAL CONTROL NO ----- >>>'||p_xmlg_internal_control_number,1);
4925 ecx_cln_debug_pub.Add('PARTNER DOCUMENT NO ----- >>>'||p_partner_doc_no,1);
4926 ecx_cln_debug_pub.Add('ORG ID ----- >>>'||p_org_id,1);
4927 ecx_cln_debug_pub.Add('DOCUMENT CREATION DATE ----- >>>'||p_doc_creation_date,1);
4928 ecx_cln_debug_pub.Add('DOCUMENT REVISION DATE ----- >>>'||p_doc_revision_date,1);
4929 ecx_cln_debug_pub.Add('INIT DATE ----- >>>'||p_init_date,1);
4930 ecx_cln_debug_pub.Add('DOCUMENT OWNER ----- >>>'||p_doc_owner,1);
4931 ecx_cln_debug_pub.Add('OWNER ROLE ----- >>>'||p_owner_role,1);
4932 ecx_cln_debug_pub.Add('XMLG EVENT KEY ----- >>>'||p_xml_event_key,1);
4933 ecx_cln_debug_pub.Add('Collaboration Standard ----- >>>'||p_collaboration_standard,1);
4934 ecx_cln_debug_pub.Add('ATTRIBUTE1 ----- >>>'||p_attribute1,1);
4935 ecx_cln_debug_pub.Add('ATTRIBUTE2 ----- >>>'||p_attribute2,1);
4936 ecx_cln_debug_pub.Add('ATTRIBUTE3 ----- >>>'||p_attribute3,1);
4937 ecx_cln_debug_pub.Add('ATTRIBUTE4 ----- >>>'||p_attribute4,1);
4938 ecx_cln_debug_pub.Add('ATTRIBUTE5 ----- >>>'||p_attribute5,1);
4939 ecx_cln_debug_pub.Add('ATTRIBUTE6 ----- >>>'||p_attribute6,1);
4940 ecx_cln_debug_pub.Add('ATTRIBUTE7 ----- >>>'||p_attribute7,1);
4941 ecx_cln_debug_pub.Add('ATTRIBUTE8 ----- >>>'||p_attribute8,1);
4942 ecx_cln_debug_pub.Add('ATTRIBUTE9 ----- >>>'||p_attribute9,1);
4943 ecx_cln_debug_pub.Add('ATTRIBUTE10 ----- >>>'||p_attribute10,1);
4944 ecx_cln_debug_pub.Add('ATTRIBUTE11 ----- >>>'||p_attribute11,1);
4945 ecx_cln_debug_pub.Add('ATTRIBUTE12 ----- >>>'||p_attribute12,1);
4946 ecx_cln_debug_pub.Add('ATTRIBUTE13 ----- >>>'||p_attribute13,1);
4947 ecx_cln_debug_pub.Add('ATTRIBUTE14 ----- >>>'||p_attribute14,1);
4948 ecx_cln_debug_pub.Add('ATTRIBUTE15 ----- >>>'||p_attribute15,1);
4949 ecx_cln_debug_pub.Add('DATTRIBUTE1 ----- >>>'||p_dattribute1,1);
4950 ecx_cln_debug_pub.Add('DATTRIBUTE2 ----- >>>'||p_dattribute2,1);
4951 ecx_cln_debug_pub.Add('DATTRIBUTE3 ----- >>>'||p_dattribute3,1);
4955 END IF;
4952 ecx_cln_debug_pub.Add('DATTRIBUTE4 ----- >>>'||p_dattribute4,1);
4953 ecx_cln_debug_pub.Add('DATTRIBUTE5 ----- >>>'||p_dattribute5,1);
4954 ecx_cln_debug_pub.Add('===========================================',1);
4956
4957
4958
4959 -- assigning values to local variables for transaction tyoe/subtype
4960 l_coll_id := p_coll_id;
4961 l_xmlg_transaction_type := p_xmlg_transaction_type;
4962 l_xmlg_transaction_subtype := p_xmlg_transaction_subtype;
4963 l_xmlg_int_transaction_type := p_xmlg_int_transaction_type;
4964 l_xmlg_int_transaction_subtype := p_xmlg_int_transaction_subtype;
4965 l_xmlg_internal_control_number := p_xmlg_internal_control_number;
4966 l_xmlg_msg_id := p_xmlg_msg_id;
4967 l_xmlg_document_id := p_xmlg_document_id;
4968 l_tr_partner_type := p_tr_partner_type;
4969 l_tr_partner_id := p_tr_partner_id;
4970 l_tr_partner_site := p_tr_partner_site;
4971 l_doc_dir := p_doc_dir;
4972 l_sender_component := p_sender_component;
4973 l_xml_event_key := p_xml_event_key;
4974 l_collaboration_standard := p_collaboration_standard;
4975
4976 -- call the API to get the trading partner set up details
4977 IF (l_Debug_Level <= 1) THEN
4978 ecx_cln_debug_pub.Add('==========Call to GET_TRADING_PARTNER_DETAILS API=============',1);
4979 END IF;
4980
4981
4982 GET_TRADING_PARTNER_DETAILS(
4983 x_return_status => x_return_status,
4984 x_msg_data => x_msg_data,
4985 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
4986 p_xmlg_msg_id => l_xmlg_msg_id,
4987 p_xmlg_transaction_type => l_xmlg_transaction_type,
4988 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
4989 p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
4990 p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
4991 p_xmlg_document_id => l_xmlg_document_id,
4992 p_doc_dir => l_doc_dir,
4993 p_tr_partner_type => l_tr_partner_type,
4994 p_tr_partner_id => l_tr_partner_id,
4995 p_tr_partner_site => l_tr_partner_site,
4996 p_sender_component => l_sender_component,
4997 p_xml_event_key => l_xml_event_key,
4998 p_collaboration_standard => l_collaboration_standard);
4999
5000 IF ( x_return_status <> 'S') THEN
5001 l_msg_data := 'Error in GET_TRADING_PARTNER_DETAILS ';
5002 -- x_msg_data is set to appropriate value by GET_TRADING_PARTNER_DETAILS
5003 RAISE FND_API.G_EXC_ERROR;
5004 END IF;
5005 IF (l_Debug_Level <= 1) THEN
5009 l_app_id := p_app_id;
5006 ecx_cln_debug_pub.Add('===========================================',1);
5007 END IF;
5008
5010 l_coll_type := p_coll_type;
5011 l_doc_type := p_doc_type;
5012
5013 IF (l_app_id is null or l_coll_type is null and l_doc_type is null) THEN
5014 DEFAULT_XMLGTXN_MAPPING(
5015 x_return_status => x_return_status,
5016 x_msg_data => x_msg_data,
5017 p_xmlg_transaction_type => l_xmlg_transaction_type,
5018 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
5019 p_doc_dir => l_doc_dir,
5020 p_app_id => l_app_id,
5021 p_coll_type => l_coll_type,
5022 p_doc_type => l_doc_type );
5023 END IF;
5024
5025
5026 IF l_coll_id IS NULL THEN
5027 IF (l_Debug_Level <= 1) THEN
5028 ecx_cln_debug_pub.Add('Collaboration ID passed as null',1);
5029 ecx_cln_debug_pub.Add('==========Call to FIND_COLLABORATION_ID API=============',1);
5030 END IF;
5031
5032 FIND_COLLABORATION_ID(
5033 x_return_status => x_return_status,
5034 x_msg_data => x_msg_data,
5035 x_coll_id => l_coll_id,
5036 p_app_id => l_app_id,
5037 p_coll_type => l_coll_type,
5038 p_ref_id => p_ref_id,
5039 p_xmlg_transaction_type => l_xmlg_transaction_type,
5040 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
5041 p_xmlg_document_id => l_xmlg_document_id,
5042 p_tr_partner_type => l_tr_partner_type,
5043 p_tr_partner_id => l_tr_partner_id,
5044 p_tr_partner_site => l_tr_partner_site,
5045 p_doc_dir => l_doc_dir,
5046 p_xmlg_msg_id => l_xmlg_msg_id,
5047 p_unique1 => p_unique1,
5048 p_unique2 => p_unique2,
5049 p_unique3 => p_unique3,
5050 p_unique4 => p_unique4,
5051 p_unique5 => p_unique5,
5052 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
5053 p_xml_event_key => l_xml_event_key,
5054 p_collaboration_standard => l_collaboration_standard);
5055
5056 IF ( x_return_status <> 'S') THEN
5057 l_msg_data := 'Error in FIND_COLLABORATION_ID';
5058 -- x_msg_data is set to appropriate value by FIND_COLLABORATION_ID
5059 RAISE FND_API.G_EXC_ERROR;
5060 END IF;
5061 IF (l_Debug_Level <= 1) THEN
5062 ecx_cln_debug_pub.Add('Collaboration ID Found as '||l_coll_id,1);
5063 END IF;
5064
5065
5066 END IF;
5067
5068 IF l_coll_id IS NULL THEN
5069 IF (l_Debug_Level <= 1) THEN
5070 ecx_cln_debug_pub.Add('.....Collaboration Does Not Exist...............',1);
5071 ecx_cln_debug_pub.Add('.....Call to Create Collaboration API...........',1);
5072 END IF;
5073
5074
5075 CREATE_COLLABORATION(
5076 x_return_status => x_return_status,
5077 x_msg_data => x_msg_data,
5078 p_app_id => l_app_id,
5079 p_ref_id => p_ref_id,
5080 p_org_id => p_org_id,
5081 p_rel_no => p_rel_no,
5082 p_doc_no => p_doc_no,
5083 p_doc_rev_no => p_doc_rev_no,
5084 p_xmlg_transaction_type => l_xmlg_transaction_type,
5085 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
5086 p_xmlg_document_id => l_xmlg_document_id,
5087 p_partner_doc_no => p_partner_doc_no,
5088 p_coll_type => l_coll_type,
5089 p_tr_partner_type => l_tr_partner_type,
5090 p_tr_partner_id => l_tr_partner_id,
5091 p_tr_partner_site => l_tr_partner_site,
5092 p_resend_flag => p_resend_flag,
5093 p_resend_count => p_resend_count,
5094 p_doc_owner => p_doc_owner,
5095 p_init_date => p_init_date,
5096 p_doc_creation_date => p_doc_creation_date,
5100 p_coll_pt => p_coll_pt,
5097 p_doc_revision_date => p_doc_revision_date,
5098 p_doc_type => l_doc_type,
5099 p_doc_dir => l_doc_dir,
5101 p_xmlg_msg_id => l_xmlg_msg_id,
5102 p_unique1 => p_unique1,
5103 p_unique2 => p_unique2,
5104 p_unique3 => p_unique3,
5105 p_unique4 => p_unique4,
5106 p_unique5 => p_unique5,
5107 p_sender_component => l_sender_component,
5108 p_rosettanet_check_required => p_rosettanet_check_required,
5109 x_coll_id => l_coll_id,
5110 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
5111 p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
5112 p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
5113 p_msg_text => p_msg_text,
5114 p_xml_event_key => l_xml_event_key,
5115 p_collaboration_standard => l_collaboration_standard,
5116 p_attribute1 => p_attribute1,
5117 p_attribute2 => p_attribute2,
5118 p_attribute3 => p_attribute3,
5119 p_attribute4 => p_attribute4,
5120 p_attribute5 => p_attribute5,
5121 p_attribute6 => p_attribute6,
5122 p_attribute7 => p_attribute7,
5123 p_attribute8 => p_attribute8,
5124 p_attribute9 => p_attribute9,
5125 p_attribute10 => p_attribute10,
5126 p_attribute11 => p_attribute11,
5127 p_attribute12 => p_attribute12,
5128 p_attribute13 => p_attribute13,
5129 p_attribute14 => p_attribute14,
5130 p_attribute15 => p_attribute15,
5131 p_dattribute1 => p_dattribute1,
5132 p_dattribute2 => p_dattribute2,
5133 p_dattribute3 => p_dattribute3,
5134 p_dattribute4 => p_dattribute4,
5135 p_dattribute5 => p_dattribute5,
5136 p_owner_role => p_owner_role );
5137
5138 IF ( x_return_status <> 'S') THEN
5139 l_msg_data := 'Error in CREATE_COLLABORATION';
5140 -- x_msg_data is set to appropriate value by CREATE_COLLABORATION
5141 RAISE FND_API.G_EXC_ERROR;
5142 END IF;
5143 l_coll_id := null; -- so that update collaboration is not called immediately after create collaboration.
5144 END IF;
5145
5146
5147 IF l_coll_id IS NOT NULL THEN
5148 IF (l_Debug_Level <= 1) THEN
5149 ecx_cln_debug_pub.Add('.....Collaboration Exists...........',1);
5150 ecx_cln_debug_pub.Add('.....Call to Update Collaboration API...........',1);
5151 END IF;
5152
5153
5154
5155 UPDATE_COLLABORATION(
5156 x_return_status => x_return_status,
5157 x_msg_data => x_msg_data,
5158 p_coll_id => l_coll_id,
5159 p_app_id => l_app_id,
5160 p_ref_id => p_ref_id,
5161 p_rel_no => p_rel_no,
5162 p_doc_no => p_doc_no,
5163 p_doc_rev_no => p_doc_rev_no,
5164 p_xmlg_transaction_type => l_xmlg_transaction_type,
5165 p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
5166 p_xmlg_document_id => l_xmlg_document_id,
5167 p_resend_flag => p_resend_flag,
5168 p_resend_count => p_resend_count,
5169 p_disposition => p_disposition,
5170 p_coll_status => p_coll_status,
5171 p_doc_type => l_doc_type,
5172 p_doc_dir => l_doc_dir,
5173 p_coll_pt => p_coll_pt,
5174 p_org_ref => p_org_ref,
5178 p_attr1 => p_attr1,
5175 p_doc_status => p_doc_status,
5176 p_notification_id => p_notification_id,
5177 p_msg_text => p_msg_text,
5179 p_attr2 => p_attr2,
5180 p_attr3 => p_attr3,
5181 p_attr4 => p_attr4,
5182 p_attr5 => p_attr5,
5183 p_attr6 => p_attr6,
5184 p_attr7 => p_attr7,
5185 p_attr8 => p_attr8,
5186 p_attr9 => p_attr9,
5187 p_attr10 => p_attr10,
5188 p_attr11 => p_attr11,
5189 p_attr12 => p_attr12,
5190 p_attr13 => p_attr13,
5191 p_attr14 => p_attr14,
5192 p_attr15 => p_attr15,
5193 p_xmlg_msg_id => l_xmlg_msg_id,
5194 p_unique1 => p_unique1,
5195 p_unique2 => p_unique2,
5196 p_unique3 => p_unique3,
5197 p_unique4 => p_unique4,
5198 p_unique5 => p_unique5,
5199 p_tr_partner_type => l_tr_partner_type,
5200 p_tr_partner_id => l_tr_partner_id,
5201 p_tr_partner_site => l_tr_partner_site,
5202 p_sender_component => l_sender_component,
5203 p_rosettanet_check_required => p_rosettanet_check_required,
5204 x_dtl_coll_id => l_dtl_coll_id,
5205 p_xmlg_internal_control_number => l_xmlg_internal_control_number,
5206 p_partner_doc_no => p_partner_doc_no,
5207 p_org_id => p_org_id,
5208 p_doc_creation_date => p_doc_creation_date,
5209 p_doc_revision_date => p_doc_revision_date,
5210 p_doc_owner => p_doc_owner,
5211 p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
5212 p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
5213 p_xml_event_key => l_xml_event_key,
5214 p_collaboration_standard => l_collaboration_standard,
5215 p_attribute1 => p_attribute1,
5216 p_attribute2 => p_attribute2,
5217 p_attribute3 => p_attribute3,
5218 p_attribute4 => p_attribute4,
5219 p_attribute5 => p_attribute5,
5220 p_attribute6 => p_attribute6,
5221 p_attribute7 => p_attribute7,
5222 p_attribute8 => p_attribute8,
5223 p_attribute9 => p_attribute9,
5224 p_attribute10 => p_attribute10,
5225 p_attribute11 => p_attribute11,
5226 p_attribute12 => p_attribute12,
5227 p_attribute13 => p_attribute13,
5231 p_dattribute2 => p_dattribute2,
5228 p_attribute14 => p_attribute14,
5229 p_attribute15 => p_attribute15,
5230 p_dattribute1 => p_dattribute1,
5232 p_dattribute3 => p_dattribute3,
5233 p_dattribute4 => p_dattribute4,
5234 p_dattribute5 => p_dattribute5,
5235 p_owner_role => p_owner_role );
5236
5237
5238 IF ( x_return_status <> 'S') THEN
5239 l_msg_data := 'Error in UPDATE_COLLABORATION';
5240 -- x_msg_data is set to appropriate value by UPDATE_COLLABORATION
5241 RAISE FND_API.G_EXC_ERROR;
5242 END IF;
5243
5244 END IF;
5245
5246
5247 IF (l_Debug_Level <= 1) THEN
5248 ecx_cln_debug_pub.Add(l_msg_data,1);
5249 END IF;
5250
5251 IF (l_Debug_Level <= 2) THEN
5252 ecx_cln_debug_pub.Add('------ Exiting ADD_COLLABORATION API ------- ',2);
5253 END IF;
5254
5255
5256 EXCEPTION
5257
5258 WHEN FND_API.G_EXC_ERROR THEN
5259 --ROLLBACK TO UPDATE_COLLABORATION_PUB;
5260 x_return_status := FND_API.G_RET_STS_ERROR ;
5261 IF (l_Debug_Level <= 4) THEN
5262 ecx_cln_debug_pub.Add(l_msg_data,4);
5263 ecx_cln_debug_pub.Add('------ Exiting ADD_COLLABORATION API ------- ',2);
5264 END IF;
5265
5266
5267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5268 --ROLLBACK TO UPDATE_COLLABORATION_PUB;
5269 l_error_code :=SQLCODE;
5270 l_error_msg :=SQLERRM;
5271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5272 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
5273 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
5274 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
5275 x_msg_data :=FND_MESSAGE.GET;
5276 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
5277 IF (l_Debug_Level <= 5) THEN
5278 ecx_cln_debug_pub.Add(l_msg_data,6);
5279 ecx_cln_debug_pub.Add('------ Exiting ADD_COLLABORATION API ------- ',2);
5280 END IF;
5281
5282
5283
5284 WHEN OTHERS THEN
5285 --ROLLBACK TO UPDATE_COLLABORATION_PUB;
5286 l_error_code :=SQLCODE;
5287 l_error_msg :=SQLERRM;
5288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5289 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
5290 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
5291 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
5292 x_msg_data :=FND_MESSAGE.GET;
5293 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
5294 IF (l_Debug_Level <= 4) THEN
5295 ecx_cln_debug_pub.Add(l_msg_data,4);
5296 ecx_cln_debug_pub.Add('------ Exiting ADD_COLLABORATION API ------- ',2);
5297 END IF;
5298
5299
5300 END ADD_COLLABORATION;
5301
5302 END CLN_CH_COLLABORATION_PKG;
[Home] [Help]