eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CLN_XMLG_EVENT_HANDLER_PKG

Source


1 PACKAGE BODY CLN_XMLG_EVENT_HANDLER_PKG AS
2 /* $Header: ECXXMLGB.pls 120.1 2005/08/26 07:19:28 nparihar noship $ */
3    l_debug_level        NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4 
5 -- Package
6 --   CLN_XMLG_EVENT_HANDLER_PKG
7 --
8 -- Purpose
9 --    Body of package CLN_XMLG_EVENT_HANDLER_PKG. This package captures the events raised
10 --    by XML Gateway and updates the collaboration history
11 --
12 -- History
13 --    May-20-2002       Viswanthan Umapathy         Created
14 
15 
16 -- Name
17 --    NOTIFIY_XMLG_IN_ERROR
18 -- Purpose
19 --   This procedure is called to update the collaboration history whenever
20 --   XMLG enconters an error in an inbound dcoument.
21 -- Arguments
22 --
23 -- Notes
24 --    No specific notes.
25 
26    PROCEDURE NOTIFIY_XMLG_IN_ERROR(
27       x_return_status   OUT NOCOPY VARCHAR2,
28       x_msg_data        OUT NOCOPY VARCHAR2,
29       p_msg_id          IN  RAW,
30       p_err_code        IN  VARCHAR2,
31       p_err_desc        IN  VARCHAR2)
32    IS
33       l_coll_id                     NUMBER;
34       l_party_id                    VARCHAR2(100);
35       l_party_type                  VARCHAR2(100);
36       l_np_tp_id                    VARCHAR2(100);
37       l_app_id                      VARCHAR2(100);
38       l_tp_location_code            VARCHAR2(255);
39       l_doc_dir                     VARCHAR2(10);
40       l_doc_no                      VARCHAR2(100);
41       l_xmlg_tran_type              VARCHAR2(100);
42       l_xmlg_tran_subtype           VARCHAR2(100);
43       l_xmlg_doc_id                 VARCHAR2(255);
44       l_app_ref_id                  VARCHAR2(1000);
45       l_coll_type                   VARCHAR2(100);
46       l_fnd_message                 VARCHAR2(1000);
47       l_sender_component            VARCHAR2(50);
48       l_email                       VARCHAR2(255);
49       l_notification_flow_key       NUMBER(20);
50       l_coll_exists_flag            BOOLEAN;
51       l_rosettanet_check_required   BOOLEAN;
52       l_int_con_no                  VARCHAR2(255);
53       l_debug_mode                  VARCHAR2(255);
54       l_error_code                  VARCHAR2(255);
55       l_error_msg                   VARCHAR2(1000);
56       l_message_standard            VARCHAR2(30);
57       l_doc_type                    VARCHAR2(30);
58 
59    BEGIN
60 
61       -- Sets the debug mode to be FILE
62       --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
63 
64       IF (l_Debug_Level <= 2) THEN
65               ecx_cln_debug_pub.Add('ENTERING NOTIFIY_XMLG_IN_ERROR', 2);
66       END IF;
67 
68       IF (l_Debug_Level <= 1) THEN
69               ecx_cln_debug_pub.Add('With the following parameters:', 1);
70               ecx_cln_debug_pub.Add('p_msg_id:' || p_msg_id, 1);
71               ecx_cln_debug_pub.Add('p_err_code:' || p_err_code, 1);
72               ecx_cln_debug_pub.Add('p_err_desc:' || p_err_desc, 1);
73       END IF;
74 
75 
76       --  Initialize API return status to success
77       x_return_status := FND_API.G_RET_STS_SUCCESS;
78       x_msg_data := 'Collaboration History is successfully updated with the error message';
79 
80       --  Getting trading partner details, transaction type, subtype, document id, etc..
81       --  from ecx tables using message ID
82 
83       BEGIN
84          --  Getting trading partner details, transaction type, subtype, document id, etc..
85          --  from ecx tables using message ID
86          SELECT header.tp_header_id , header.party_id, header.party_site_id,
87                 header.party_type, doclogs.internal_control_number, doclogs.attribute5,
88                 transaction.transaction_type, transaction.transaction_subtype,
89                 doclogs.document_number, doclogs.protocol_type, estd.standard_code
90          INTO   l_np_tp_id, l_party_id, l_tp_location_code,
91                 l_party_type, l_int_con_no, l_app_ref_id,
92                 l_xmlg_tran_type, l_xmlg_tran_subtype,
93                 l_xmlg_doc_id, l_sender_component, l_message_standard
94          FROM   ecx_doclogs doclogs, ecx_tp_details details, ecx_tp_headers header,
95                 ecx_ext_processes processes, ecx_transactions transaction, ecx_standards estd
96          	WHERE  doclogs.msgid = HEXTORAW(p_msg_id) and
97                 doclogs.party_site_id = source_tp_location_code and
98                 doclogs.transaction_type = ext_type and
99                 doclogs.transaction_subtype = ext_subtype and
100                 header.tp_header_id = details.tp_header_id and
101                 details.ext_process_id  = processes.ext_process_id and
102                 processes.transaction_id = transaction.transaction_id and
103                 estd.standard_id = processes.standard_id and
104                rownum < 2;
105          EXCEPTION
106             WHEN NO_DATA_FOUND THEN
107                IF (l_Debug_Level <= 1) THEN
108                        ecx_cln_debug_pub.Add('INVALID_DATA_FOUND EXCEPTION IN ECX_DOCLOGS FOR MESSSAGE ID:' || p_msg_id, 1);
109                END IF;
110 
111                SELECT internal_control_number, attribute5, transaction_type,
112                       transaction_subtype, document_number, protocol_type, message_standard
113                INTO   l_int_con_no, l_app_ref_id, l_xmlg_tran_type,
114                       l_xmlg_tran_subtype, l_xmlg_doc_id, l_sender_component, l_message_standard
115                FROM   ecx_doclogs
116                WHERE  msgid = HEXTORAW(p_msg_id);
117       END;
118 
119 
120       IF (l_Debug_Level <= 1) THEN
121               ecx_cln_debug_pub.Add('l_np_tp_id:' || l_np_tp_id, 1);
122               ecx_cln_debug_pub.Add('l_party_id:' || l_party_id, 1);
123               ecx_cln_debug_pub.Add('l_tp_location_code:' || l_tp_location_code, 1);
124               ecx_cln_debug_pub.Add('l_party_type:' || l_party_type, 1);
125               ecx_cln_debug_pub.Add('l_int_con_no:' || l_int_con_no, 1);
126               ecx_cln_debug_pub.Add('l_app_ref_id:' || l_app_ref_id, 1);
127 
128               ecx_cln_debug_pub.Add('Transaction Type:' || l_xmlg_tran_type, 1);
129               ecx_cln_debug_pub.Add('Transaction Subtype:' || l_xmlg_tran_subtype, 1);
130               ecx_cln_debug_pub.Add('Document ID:' || l_xmlg_doc_id, 1);
131               ecx_cln_debug_pub.Add('Application reference ID from attribute5:' || l_app_ref_id, 1);
132               ecx_cln_debug_pub.Add('Message Standard:' || l_message_standard, 1);
133       END IF;
134 
135 
136 
137       SELECT DECODE(NVL(l_xmlg_tran_type, 'NULL'), 'NULL', '', l_xmlg_tran_type || ':') ||
138              DECODE(NVL(l_xmlg_tran_subtype, 'NULL'), 'NULL', '', l_xmlg_tran_subtype || ':') ||
139              DECODE(NVL(l_xmlg_doc_id, 'NULL'), 'NULL', '', l_xmlg_doc_id)
140              INTO l_doc_no
141              FROM DUAL;
142 
143       IF (l_Debug_Level <= 1) THEN
144               ecx_cln_debug_pub.Add('l_doc_no:' || l_doc_no, 1);
145       END IF;
146 
147       CLN_CH_COLLABORATION_PKG.DEFAULT_XMLGTXN_MAPPING(
148          x_return_status                => x_return_status,
149          x_msg_data                     => x_msg_data,
150          p_xmlg_transaction_type        => l_xmlg_tran_type,
151          p_xmlg_transaction_subtype     => l_xmlg_tran_subtype,
152          p_doc_dir                      => l_doc_dir,
153          p_app_id                       => l_app_id,
154          p_coll_type                    => l_coll_type,
155          p_doc_type                     => l_doc_type);
156 
157       --  Getting Application Refernce ID by Calling GET_DATA_AREA_REFID
158       IF l_app_ref_id IS NULL THEN
159          CLN_CH_COLLABORATION_PKG.GET_DATA_AREA_REFID(p_msg_id, l_message_standard, l_app_ref_id, l_app_id, l_coll_type);
160          IF (l_Debug_Level <= 1) THEN
161                  ecx_cln_debug_pub.Add('Application reference ID obtained thru payload parsing:'|| l_app_ref_id, 1);
162          END IF;
163 
164       END IF;
165 
166       -- Collaboration exists ?
167       l_coll_exists_flag := true;
168 
169       -- Getting Collaboration ID for the Application Refernce ID
170       BEGIN
171          SELECT collaboration_id
172          INTO   l_coll_id
173          FROM   CLN_COLL_HIST_HDR
174          WHERE  APPLICATION_REFERENCE_ID = l_app_ref_id;
175          EXCEPTION
176             WHEN NO_DATA_FOUND THEN
177                l_coll_exists_flag := false;
178       END;
179 
180       IF NOT l_coll_exists_flag THEN
181          BEGIN
182             SELECT collaboration_id
183             INTO   l_coll_id
184             FROM   CLN_COLL_HIST_HDR
185             WHERE  xmlg_msg_id = p_msg_id;
186 
187             l_coll_exists_flag := true;
188 
189          EXCEPTION
190             WHEN NO_DATA_FOUND THEN
191                l_coll_exists_flag := false;
192          END;
193       END IF;
194 
195       l_doc_dir := 'IN';
196       l_rosettanet_check_required := FALSE;
197       IF l_sender_component IS NOT NULL THEN
198          l_rosettanet_check_required := TRUE;
199       END IF;
200 
201 
202       -- If collaboration doesn't exist
203       IF NOT l_coll_exists_flag THEN
204 
205          IF (l_Debug_Level <= 1) THEN
206                  ecx_cln_debug_pub.Add('Collaboration does not exist', 1);
207          END IF;
208 
209 
210          --  Getting Party ID
211          IF (l_Debug_Level <= 1) THEN
212                  ecx_cln_debug_pub.Add('Party ID :' || l_party_id, 1);
213          END IF;
214 
215 
216 
217          l_app_id := NVL(l_app_id,'701');
218          l_coll_type := NVL(l_coll_type,'XMLG_ERROR');
219 
220          -- SELECT COMPONENT INTO l_sender_component FROM ECX_OAG_CBOD_V WHERE MSGID = p_msg_id;
221 
222          -- Create collaboration
223          IF (l_Debug_Level <= 1) THEN
224                  ecx_cln_debug_pub.Add('CREATE_COLLABORATION: <Mandatory parameters>', 1);
225                  ecx_cln_debug_pub.Add('l_app_id:' || l_app_id, 1);
226                  ecx_cln_debug_pub.Add('l_coll_type:' || l_coll_type, 1);
227                  ecx_cln_debug_pub.Add('l_party_id:' || l_party_id, 1);
228                  ecx_cln_debug_pub.Add('l_doc_dir:' || l_doc_dir, 1);
229                  ecx_cln_debug_pub.Add('l_sender_component:' || l_sender_component, 1);
230          END IF;
231 
232          /*
233          CLN_CH_COLLABORATION_PKG.CREATE_COLLABORATION(
234             x_return_status                => x_return_status,
235             x_msg_data                     => x_msg_data,
236             p_ref_id                       => l_app_ref_id,
237             p_doc_no                       => l_doc_no,
238             p_resend_flag                  => 'N',
239             p_resend_count                 => 0,
240             p_doc_owner                    => FND_GLOBAL.USER_ID,
241             p_init_date                    => sysdate,
242             p_coll_pt                      => 'XML_GATEWAY',
243             p_xmlg_msg_id                  => p_msg_id,
244             p_sender_component             => l_sender_component,
245             p_rosettanet_check_required    => l_rosettanet_check_required,
246             x_coll_id                      => l_coll_id);
247          */
248 
249 
250          CLN_CH_COLLABORATION_PKG.CREATE_COLLABORATION(
251             x_return_status                => x_return_status,
252             x_msg_data                     => x_msg_data,
253             p_app_id                       => l_app_id,
254             p_ref_id                       => l_app_ref_id,
255             p_org_id                       => NULL,
256             p_rel_no                       => NULL,
257             p_doc_no                       => l_doc_no,
258             p_doc_rev_no                   => NULL,
259             p_xmlg_transaction_type        => l_xmlg_tran_type,
260             p_xmlg_transaction_subtype     => l_xmlg_tran_subtype,
261             p_xmlg_document_id             => l_xmlg_doc_id,
262             p_partner_doc_no               => NULL,
263             p_coll_type                    => l_coll_type,
264             p_tr_partner_type              => l_party_type,
265             p_tr_partner_id                => l_party_id,
266             p_tr_partner_site              => NULL,
267             p_resend_flag                  => 'N',
268             p_resend_count                 => 0,
269             p_doc_owner                    => FND_GLOBAL.USER_ID,
270             p_init_date                    => sysdate,
271             p_doc_creation_date            => NULL,
272             p_doc_revision_date            => NULL,
273             p_doc_type                     => l_doc_type,
274             p_doc_dir                      => l_doc_dir,
275             p_coll_pt                      => 'B2B_SERVER',
276             p_xmlg_msg_id                  => p_msg_id,
277             p_unique1                      => NULL,
278             p_unique2                      => NULL,
279             p_unique3                      => NULL,
280             p_unique4                      => NULL,
281             p_unique5                      => NULL,
282             p_sender_component             => l_sender_component,
283             p_rosettanet_check_required    => l_rosettanet_check_required,
284             x_coll_id                      => l_coll_id);
285          IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
286             x_msg_data := 'Create Collaboration Failed:' || x_msg_data;
287             IF (l_Debug_Level <= 1) THEN
288                     ecx_cln_debug_pub.Add('ERROR:' || x_msg_data, 1);
289             END IF;
290 
291             IF (l_Debug_Level <= 2) THEN
292                     ecx_cln_debug_pub.Add('EXITING NOTIFIY_XMLG_IN_ERROR', 2);
293             END IF;
294 
295             RETURN;
296          END IF;
297       ELSE
298             IF (l_Debug_Level <= 1) THEN
299                     ecx_cln_debug_pub.Add('Collaboration exists', 1);
300             END IF;
301 
302       END IF; -- IF NOT l_coll_exists_flag THEN
303 
304       FND_MESSAGE.SET_NAME('CLN', 'CLN_MSG_XMLG_ERROR'); -- 'XML Gateway Error'
305       l_fnd_message := FND_MESSAGE.GET;
306 
307       -- Update collaboration
308       CLN_NP_PROCESSOR_PKG.PROCESS_NOTIFICATION(x_return_status, x_msg_data, NULL, l_app_ref_id,
309                                                 '99', l_fnd_message, p_err_code, p_err_desc,
310                                                 l_int_con_no, 'XML_GATEWAY', l_doc_dir, l_coll_id,NULL);
311       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
312          x_msg_data := 'Process Notification Failed:' || x_msg_data;
313          IF (l_Debug_Level <= 4) THEN
314                  ecx_cln_debug_pub.Add('ERROR:' || x_msg_data, 4);
315          END IF;
316 
317          IF (l_Debug_Level <= 2) THEN
318                  ecx_cln_debug_pub.Add('EXITING NOTIFIY_XMLG_IN_ERROR', 2);
319          END IF;
320 
321          RETURN;
322       END IF;
323       EXCEPTION
324          WHEN OTHERS THEN
325             l_error_code := SQLCODE;
326             l_error_msg := SQLERRM;
327             x_msg_data :=  'ERROR: ' || l_error_code||' : '||l_error_msg;
328             x_return_status := FND_API.G_RET_STS_ERROR;
329             IF (l_Debug_Level <= 5) THEN
330                     ecx_cln_debug_pub.Add(x_msg_data, 6);
331                     ecx_cln_debug_pub.Add('EXITING NOTIFIY_XMLG_IN_ERROR', 2);
332             END IF;
333 
334    END NOTIFIY_XMLG_IN_ERROR;
335 
336    -- Name
337    --    CLN_XMLG_ERROR_SUBSCRIPTION_F
338    -- Purpose
339    --    This function subcribes to oracle.apps.ecx.processing.message.error which is generated
340    --    by XML Gateway when it encounters a parsing error for an inbound document
341    -- Arguments
342    --
343    -- Notes
344    --    No specific notes.
345 
346    FUNCTION CLN_XMLG_PROCESSING_ERROR_F(
347       p_subscription_guid  IN RAW,
348       p_event  IN OUT NOCOPY WF_EVENT_T
349       ) RETURN VARCHAR2
350    IS
351       l_msg_id      VARCHAR2(255);
352       l_error_code  VARCHAR2(255);
353       l_error_msg   VARCHAR2(1000);
354       l_return_code VARCHAR2(255);
355       l_return_msg  VARCHAR2(1000);
356       l_debug_mode  VARCHAR2(255);
357       i_cln_not_parameters   wf_parameter_list_t;
358    BEGIN
359       -- Sets the debug mode to be FILE
360       --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
361 
362       IF (l_Debug_Level <= 2) THEN
363               ecx_cln_debug_pub.Add('ENTERING CLN_XMLG_PROCESSING_ERROR_F', 2);
364       END IF;
365 
366 
367       i_cln_not_parameters := p_event.getParameterList();
368 
369       l_msg_id := WF_EVENT.getValueForParameter('ECX_MSGID',i_cln_not_parameters);
370       l_error_code := 'XMLG_IN01'; -- Error code not supplied by XMLGateway
371       l_error_msg := WF_EVENT.getValueForParameter('ECX_ERROR_MSG',i_cln_not_parameters);
372       NOTIFIY_XMLG_IN_ERROR(l_return_code, l_return_msg,
373                                                         l_msg_id, l_error_code, l_error_msg);
374       IF (l_Debug_Level <= 2) THEN
375               ecx_cln_debug_pub.Add('EXITING CLN_XMLG_PROCESSING_ERROR_F', 2);
376       END IF;
377 
378       RETURN 'SUCCESS';
379    EXCEPTION
380       WHEN OTHERS THEN
381          l_error_code := SQLCODE;
382          l_error_msg := SQLERRM;
383          IF (l_Debug_Level <= 5) THEN
384                  ecx_cln_debug_pub.Add('Error:' || l_error_code || ':' || l_error_msg, 3);
385                  ecx_cln_debug_pub.Add('EXITING CLN_XMLG_PROCESSING_ERROR_Fw', 2);
386          END IF;
387 
388          RETURN 'SUCCESS';
389    END;
390 
391    -- Name
392    --    CLN_XMLG_SETUP_ERROR_F
393    -- Purpose
394    --    This function subcribes to oracle.apps.ecx.inbound.message.receive which is generated
395    --    by XML Gateway when it encounters a setup error for an inbound document
396    -- Arguments
397    --
398    -- Notes
399    --    No specific notes.
400 
401    FUNCTION CLN_XMLG_SETUP_ERROR_F(
402       p_subscription_guid  IN RAW,
403       p_event  IN OUT NOCOPY WF_EVENT_T
404       ) RETURN VARCHAR2
405    IS
406       l_msg_id      VARCHAR2(255);
407       l_error_code  VARCHAR2(255);
408       l_error_msg   VARCHAR2(1000);
409       l_return_code VARCHAR2(255);
410       l_return_msg  VARCHAR2(1000);
411       l_debug_mode  VARCHAR2(255);
412       i_cln_not_parameters   wf_parameter_list_t;
413    BEGIN
414       -- Sets the debug mode to be FILE
415       --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
416 
417       IF (l_Debug_Level <= 2) THEN
418               ecx_cln_debug_pub.Add('ENTERING CLN_XMLG_SETUP_ERROR_F', 2);
419       END IF;
420 
421 
422       i_cln_not_parameters := p_event.getParameterList();
423 
424       l_msg_id := WF_EVENT.getValueForParameter('ECX_MSGID',i_cln_not_parameters);
425       l_error_code := 'XMLG_IN02'; -- Error code not supplied by XMLGateway
426       SELECT processing_message INTO l_error_msg FROM ecx_in_process_v
427       WHERE msgid = HEXTORAW(l_msg_id);
428       NOTIFIY_XMLG_IN_ERROR(l_return_code, l_return_msg,
429                                                         l_msg_id, l_error_code, l_error_msg);
430       IF (l_Debug_Level <= 2) THEN
431               ecx_cln_debug_pub.Add('EXITING CLN_XMLG_SETUP_ERROR_F', 2);
432       END IF;
433 
434       RETURN 'SUCCESS';
435    EXCEPTION
436       WHEN OTHERS THEN
437          l_error_code := SQLCODE;
438          l_error_msg := SQLERRM;
439          IF (l_Debug_Level <= 5) THEN
440                  ecx_cln_debug_pub.Add('Error:' || l_error_code || ':' || l_error_msg, 5);
441                  ecx_cln_debug_pub.Add('EXITING CLN_XMLG_SETUP_ERROR_F', 2);
442          END IF;
443 
444          RETURN 'SUCCESS';
445    END;
446 
447    -- Name
448    --    CLN_XMLG_EVENT_SUB_F
449    -- Purpose
450    --    This function subcribes to oracle.apps.ecx.inbound.message.receive
451    --    with source type 'EXTERNAL' and 'LOCAL', which is generated by XML Gateway
452    --    when it receives an inbound document
453    -- Arguments
454    --
455    -- Notes
456    --    No specific notes.
457 
458    FUNCTION CLN_XMLG_EVENT_SUB_F(
459       p_subscription_guid  IN RAW,
460       p_event  IN OUT NOCOPY WF_EVENT_T
461    ) RETURN VARCHAR2
462    IS
463       l_msg_id      VARCHAR2(255);
464       l_error_code  VARCHAR2(255);
465       l_error_msg   VARCHAR2(1000);
466       l_return_code VARCHAR2(255);
467       l_return_msg  VARCHAR2(1000);
468       l_debug_mode  VARCHAR2(255);
469       l_txn_type    VARCHAR2(100);
470       l_txn_subtype VARCHAR2(100);
471       l_direction   VARCHAR2(10);
472       l_app_id      VARCHAR2(10);
473       l_coll_type   VARCHAR2(100);
474       l_doc_type    VARCHAR2(100);
475       l_app_ref_id  VARCHAR2(255);
476       l_coll_id     NUMBER;
477       l_dtl_coll_id NUMBER;
478       l_msg         VARCHAR2(1000);
479       i_cln_not_parameters   wf_parameter_list_t;
480       l_message_standard VARCHAR2(30);
481    BEGIN
482       -- Sets the debug mode to be FILE
483       --l_debug_mode := ecx_cln_debug_pub.Set_Debug_Mode('FILE');
484 
485       IF (l_Debug_Level <= 2) THEN
486               ecx_cln_debug_pub.Add('ENTERING CLN_XMLG_EVENT_SUB_F', 2);
487       END IF;
488 
489 
490       i_cln_not_parameters := p_event.getParameterList();
491 
492       l_msg_id := WF_EVENT.getValueForParameter('ECX_MSGID',i_cln_not_parameters);
493       IF (l_Debug_Level <= 1) THEN
494               ecx_cln_debug_pub.Add('Message ID:' || l_msg_id, 1);
495       END IF;
496 
497 
498       SELECT TRANSACTION_TYPE, TRANSACTION_SUBTYPE, DIRECTION, ATTRIBUTE5, message_standard
499       INTO   l_txn_type, l_txn_subtype, l_direction, l_app_ref_id, l_message_standard
500       FROM   ecx_doclogs
501       WHERE  msgid = HEXTORAW(l_msg_id);
502 
503       IF (l_Debug_Level <= 1) THEN
504               ecx_cln_debug_pub.Add('Transaction Type:' || l_txn_type, 1);
505               ecx_cln_debug_pub.Add('Transaction Subtype:' || l_txn_subtype, 1);
506               ecx_cln_debug_pub.Add('Direction:' || l_direction, 1);
507               ecx_cln_debug_pub.Add('Application Reference ID:' || l_app_ref_id, 1);
508       END IF;
509 
510 
511       IF (l_txn_type = 'CLN' AND l_txn_subtype = 'NBOD') THEN
512          IF (l_Debug_Level <= 1) THEN
513                  ecx_cln_debug_pub.Add('This message (Confirm BOD) will be processed by Notification Processor',1);
514          END IF;
515 
516          IF (l_Debug_Level <= 2) THEN
517                  ecx_cln_debug_pub.Add('EXITING CLN_XMLG_EVENT_SUB_F', 2);
518          END IF;
519 
520          RETURN 'SUCCESS';
521       END IF;
522 
523       CLN_CH_COLLABORATION_PKG.DEFAULT_XMLGTXN_MAPPING(
524          x_return_status                => l_return_code,
525          x_msg_data                     => l_return_msg,
526          p_xmlg_transaction_type        => l_txn_type,
527          p_xmlg_transaction_subtype     => l_txn_subtype,
528          p_doc_dir                      => l_direction,
529          p_app_id                       => l_app_id,
530          p_coll_type                    => l_coll_type,
531          p_doc_type                     => l_doc_type);
532       -- IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
533       -- Procedure will return success even if there is NO_DATA_FOUND exception
534       IF ((l_app_id IS NULL) OR (l_coll_type IS NULL) OR (l_doc_type IS NULL)) THEN
535          IF (l_Debug_Level <= 1) THEN
536                  ecx_cln_debug_pub.Add('This message is not for oracle supply chain trading connector',1);
537          END IF;
538 
539          IF (l_Debug_Level <= 2) THEN
540                  ecx_cln_debug_pub.Add('EXITING CLN_XMLG_EVENT_SUB_F', 2);
541          END IF;
542 
543          RETURN 'SUCCESS';
544       END IF;
545 
546       IF l_app_ref_id IS NULL THEN
547          CLN_CH_COLLABORATION_PKG.GET_DATA_AREA_REFID(l_msg_id, l_message_standard, l_app_ref_id, l_app_id, l_coll_type);
548          IF (l_Debug_Level <= 1) THEN
549                  ecx_cln_debug_pub.Add('Application reference ID obtained thru payload parsing:'|| l_app_ref_id, 1);
550          END IF;
551 
552       END IF;
553 
554       BEGIN
555          SELECT collaboration_id
556          INTO   l_coll_id
557          FROM   CLN_COLL_HIST_HDR
558          WHERE  APPLICATION_REFERENCE_ID = l_app_ref_id;
559       EXCEPTION
560            WHEN NO_DATA_FOUND THEN
561               l_coll_id := NULL;
562       END;
563 
564       IF (l_Debug_Level <= 1) THEN
565               ecx_cln_debug_pub.Add('Collaboration ID:' || l_coll_id, 1);
566       END IF;
567 
568       FND_MESSAGE.SET_NAME('CLN','CLN_NP_XMLEH_SUCCESS');
569       l_msg := FND_MESSAGE.GET;
570       IF l_coll_id IS NULL THEN
571          CLN_CH_COLLABORATION_PKG.ADD_COLLABORATION(
572             x_return_status                => l_return_code,
573             x_msg_data                     => l_return_msg,
574             -- p_resend_flag                  => 'N',
575             -- p_resend_count                 => 0,
576             -- p_doc_owner                    => FND_GLOBAL.USER_ID,
577             -- p_init_date                    => sysdate,
578             p_msg_text                     => l_msg,
579             p_coll_pt                      => 'XML_GATEWAY',
580             p_xmlg_msg_id                  => l_msg_id,
581             x_dtl_coll_id                  => l_dtl_coll_id);
582          IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
583             l_return_msg := 'Create Collaboration Failed:' || l_return_msg;
584             IF (l_Debug_Level <= 4) THEN
585                     ecx_cln_debug_pub.Add('ERROR:' || l_return_msg, 4);
586             END IF;
587 
588             CLN_NP_PROCESSOR_PKG. NOTIFY_ADMINISTRATOR('While trying to create '
589                                     || 'new collaboration for the inbound message ID#'
590                                     || l_msg_id
591                                     || ', the following error is encountered:'
592                                     || l_return_msg);
593             IF (l_Debug_Level <= 2) THEN
594                     ecx_cln_debug_pub.Add('EXITING NOTIFIY_XMLG_IN_ERROR', 2);
595             END IF;
596 
597             RETURN 'SUCCESS';
598          END IF;
599       ELSE
600          CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
601             x_return_status                => l_return_code,
602             x_msg_data                     => l_return_msg,
603             p_msg_text                     => l_msg,
604             p_coll_pt                      => 'XML_GATEWAY',
605             p_doc_status                   => 'SUCCESS',
606             p_coll_id                      => l_coll_id,
607             -- p_xmlg_internal_control_number => NULL,
608             p_xmlg_msg_id                  => l_msg_id,
609             p_rosettanet_check_required    => FALSE,
610             x_dtl_coll_id                  => l_dtl_coll_id);
611       END IF;
612       IF (l_Debug_Level <= 2) THEN
613               ecx_cln_debug_pub.Add('EXITING CLN_XMLG_EVENT_SUB_F', 2);
614       END IF;
615 
616       RETURN 'SUCCESS';
617    EXCEPTION
618       WHEN OTHERS THEN
619          l_error_code := SQLCODE;
620          l_error_msg := SQLERRM;
621          IF (l_Debug_Level <= 5) THEN
622                  ecx_cln_debug_pub.Add('Error:' || l_error_code || ':' || l_error_msg, 3);
623                  ecx_cln_debug_pub.Add('EXITING CLN_XMLG_EVENT_SUB_F', 2);
624          END IF;
625 
626          RETURN 'SUCCESS';
627    END;
628 
629 END CLN_XMLG_EVENT_HANDLER_PKG;