DBA Data[Home] [Help]

PACKAGE BODY: APPS.M4U_CLN_PKG

Source


1 PACKAGE BODY m4u_cln_pkg AS
2  /* $Header: M4UDCLNB.pls 120.2 2007/06/13 10:27:36 bsaratna noship $ */
3 
4    FUNCTION log_payload
5    (
6         p_subscription_guid     IN RAW,
7         p_event                 IN OUT NOCOPY wf_event_t
8    ) RETURN VARCHAR2
9    AS
10         l_params        wf_parameter_list_t;
11         l_clob          CLOB;
12         l_ecx_doc_num   VARCHAR2(100);
13         l_direction     VARCHAR2(10);
14         l_payload_type  VARCHAR2(30);
15         l_txn_styp      VARCHAR2(30);
16         l_payload_id    VARCHAR2(30);
17         l_event_key     VARCHAR2(30);
18         l_event_name    VARCHAR2(100);
19         l_ret_sts       VARCHAR2(30);
20         l_ret_msg       VARCHAR2(4000);
21    BEGIN
22 
23 
24         m4u_dmd_utils.log('Entering m4u_cln_pkg.log_payload',2);
25 
26         l_event_key             := p_event.getEventKey();
27         l_event_name            := p_event.getEventName();
28 
29         m4u_dmd_utils.log('l_event_key      -|' || l_event_key      || '|',6);
30         m4u_dmd_utils.log('l_event_name     -|' || l_event_name     || '|',6);
31 
32         -- reading parameters
33         l_params                := p_event.getParameterList();
34         l_payload_id            := wf_event.getValueForParameter('PAYLOAD_ID',  l_params);
35         l_payload_type          := wf_event.getValueForParameter('PAYLOAD_TYPE',l_params);
36         l_direction             := wf_event.getValueForParameter('PAYLOAD_DIR' ,l_params);
37 
38         l_ecx_doc_num           := l_payload_type || '.' || l_payload_id;
39         IF l_direction = m4u_dmd_utils.c_dir_out THEN
40                 l_txn_styp := m4u_cln_pkg.c_xmlg_styp_out;
41         ELSE
42                 l_txn_styp := m4u_cln_pkg.c_xmlg_styp_in;
43         END IF;
44 
45 
46         m4u_dmd_utils.log('l_ecx_doc_num    -|' || l_ecx_doc_num    || '|',6);
47         m4u_dmd_utils.log('l_payload_id     -|' || l_payload_id     || '|',1);
48         m4u_dmd_utils.log('l_payload_type   -|' || l_payload_type   || '|',1);
49         m4u_dmd_utils.log('l_direction      -|' || l_direction      || '|',6);
50         m4u_dmd_utils.log('l_txn_styp       -|' || l_txn_styp       || '|',1);
51 
52 
53         BEGIN
54                 SELECT  payload
55                 INTO    l_clob
56                 FROM    m4u_dmd_payloads
57                 WHERE   payload_id = l_payload_id;
58         EXCEPTION
59                 WHEN OTHERS THEN
60                         m4u_dmd_utils.log('Error retrieving payload',6);
61                         m4u_dmd_utils.log(SQLCODE || '-' || SQLERRM  ,6);
62                         RAISE;
63         END;
64 
65         IF l_clob IS NOT NULL THEN
66                 m4u_dmd_utils.log('length(l_clob)   -|' || length(l_clob)   || '|',6);
67         END IF;
68 
69         m4u_dmd_utils.log('Call ecx_errorlog.log_document',2);
70         ecx_errorlog.log_document
71         (
72                 o_retcode               =>    l_ret_sts,
73                 o_retmsg                =>    l_ret_msg,
74                 i_msgid                 =>     SYS_GUID,
75                 i_message_type          =>    'XML',
76                 i_message_standard      =>    m4u_cln_pkg.c_xmlg_std,
77                 i_transaction_type      =>    m4u_cln_pkg.c_xmlg_type,
78                 i_transaction_subtype   =>    l_txn_styp,
79                 i_document_number       =>    l_ecx_doc_num,
80                 i_partyid               =>    m4u_cln_pkg.g_party_id,
81                 i_party_site_id         =>    m4u_cln_pkg.g_party_site_id,
82                 i_party_type            =>    m4u_cln_pkg.c_party_type,
83                 i_protocol_type         =>    'HTTP',
84                 i_protocol_address      =>    null,
85                 i_username              =>    null,
86                 i_password              =>    null,
87                 i_attribute1            =>    null,
88                 i_attribute2            =>    null,
89                 i_attribute3            =>    null,
90                 i_attribute4            =>    null,
91                 i_attribute5            =>    null,
92                 i_payload               =>    l_clob,
93                 i_internal_control_num  =>    null,
94                 i_status                =>    null,
95                 i_direction             =>    l_direction,
96                 i_outmsgid              =>    null,
97                 i_logfile               =>    null,
98                 i_item_type             =>    null,
99                 i_item_key              =>    null,
100                 i_activity_id           =>    null,
101                 i_event_name            =>    l_event_name,
102                 i_event_key             =>    l_event_key,
103                 i_cb_event_name         =>    null,
104                 i_cb_event_key          =>    null,
105                 i_block_mode            =>    'N'
106         );
107 
108         m4u_dmd_utils.log('l_ret_sts    -|' || l_ret_sts   || '|',2);
109         m4u_dmd_utils.log('l_ret_msg    -|' || l_ret_msg   || '|',2);
110         m4u_dmd_utils.log('Exiting m4u_cln_pkg.log_payload - Success' ,2);
111 
112         RETURN 'SUCCESS';
113 
114    EXCEPTION
115         WHEN OTHERS THEN
116                 m4u_dmd_utils.log('m4u_cln_pkg.log_payload',6);
117                 m4u_dmd_utils.log('Unexpected error occured while processing',6);
118                 m4u_dmd_utils.log(SQLCODE || '-' || SQLERRM,6);
119                 RETURN 'SUCCESS';
120    END log_payload;
121 
122    FUNCTION update_collab
123    (
124         p_subscription_guid     IN RAW,
125         p_event                 IN OUT NOCOPY wf_event_t
126    ) RETURN VARCHAR2
127    AS
128         l_params         wf_parameter_list_t;
129         l_event_key      VARCHAR2(50);
130         l_event_name     VARCHAR2(50);
131 
132         l_ret_sts        VARCHAR2(5);
133         l_ret_msg        VARCHAR2(4000);
134 
135         l_cln_pt         VARCHAR2(30);
136         l_cln_sts        VARCHAR2(30);
137         l_cln_disp       VARCHAR2(30);
138         l_cln_type       VARCHAR2(30);
139         l_cln_doc_type   VARCHAR2(30);
140         l_cln_msg        VARCHAR2(400);
141         l_cln_doc_sts    VARCHAR2(30);
142         l_cln_dtl_msg    VARCHAR2(2000);
143         l_xmlg_doc_id    VARCHAR2(30);
144         l_xmlg_type      VARCHAR2(30);
145         l_xmlg_styp      VARCHAR2(30);
146 
147         l_cln_id         VARCHAR2(30);
148         l_cln_dtl_id     VARCHAR2(30);
149         l_cln_ref_id     VARCHAR2(30);
150         l_cln_doc_id     VARCHAR2(30);
151         l_create_flag    BOOLEAN := true;
152 
153         l_doc_id         VARCHAR2(30);
154         l_retry_count    NUMBER;
155         l_ref_doc_id     VARCHAR2(30);
156 
157         l_doc_type       VARCHAR2(30);
158         l_action         VARCHAR2(30);
159         l_doc_sts        VARCHAR2(30);
160         l_processing_msg VARCHAR2(4000);
161 
162 
163         l_payload_id     VARCHAR2(30);
164         l_payload_dir    VARCHAR2(30);
165         l_payload_type   VARCHAR2(30);
166 
167 
168         l_m4u_doc_rec   m4u_dmd_documents%ROWTYPE;
169         l_m4u_msg_rec   m4u_dmd_messages%ROWTYPE;
170    BEGIN
171 
172         m4u_dmd_utils.log('Entering m4u_cln_pkg.update_collab',6);
173         l_event_key             := p_event.getEventKey();
174         l_event_name            := p_event.getEventName();
175 
176         m4u_dmd_utils.log('l_event_key      -|' || l_event_key      || '|',1);
177         m4u_dmd_utils.log('l_event_name     -|' || l_event_name     || '|',1);
178 
179         -- reading parameters
180         l_params                := p_event.getParameterList();
181         l_retry_count           := wf_event.getValueForParameter('RETRY_COUNT'   ,l_params);
182         l_doc_id                := wf_event.getValueForParameter('DOC_ID'        ,l_params);
183         l_ref_doc_id            := wf_event.getValueForParameter('REF_DOC_ID'    ,l_params);
184 
185         l_doc_type              := wf_event.getValueForParameter('DOC_TYPE'      ,l_params);
186         l_action                := wf_event.getValueForParameter('ACTION'        ,l_params);
187         l_doc_sts               := wf_event.getValueForParameter('DOC_STATUS'    ,l_params);
188         l_processing_msg        := wf_event.getValueForParameter('PROCESSING_MSG',l_params);
189 
190         l_payload_id            := wf_event.getValueForParameter('PAYLOAD_ID'    ,l_params);
191         l_payload_dir           := wf_event.getValueForParameter('PAYLOAD_DIR'   ,l_params);
192         l_payload_type          := wf_event.getValueForParameter('PAYLOAD_TYPE'  ,l_params);
193 
194 
195         m4u_dmd_utils.log('l_ref_doc_id     -|' || l_ref_doc_id     || '|',1);
196         m4u_dmd_utils.log('l_doc_id         -|' || l_doc_id         || '|',1);
197         m4u_dmd_utils.log('l_doc_typ        -|' || l_doc_type       || '|',1);
198         m4u_dmd_utils.log('l_retry_count    -|' || l_retry_count    || '|',1);
199         m4u_dmd_utils.log('l_action         -|' || l_action         || '|',1);
200 
201         m4u_dmd_utils.log('l_doc_sts        -|' || l_doc_sts        || '|',1);
202         m4u_dmd_utils.log('l_processing_msg -|' || l_processing_msg || '|',1);
203 
204         m4u_dmd_utils.log('l_payload_id     -|' || l_payload_id     || '|',1);
205         m4u_dmd_utils.log('l_payload_dir    -|' || l_payload_dir    || '|',1);
206         m4u_dmd_utils.log('l_payload_type   -|' || l_payload_type   || '|',1);
207 
208         -- query m4u_dmd_documents
209         SELECT  *
210         INTO    l_m4u_doc_rec
211         FROM    m4u_dmd_documents
212         WHERE   doc_id = l_doc_id;
213         m4u_dmd_utils.log('m4u_doc_rec.doc_id -|' || l_m4u_doc_rec.doc_id || '|',1);
214 
215         -- query m4u_dmd_messages
216         SELECT  *
217         INTO    l_m4u_msg_rec
218         FROM    m4u_dmd_messages
219         WHERE   msg_id = l_m4u_doc_rec.msg_id;
220         m4u_dmd_utils.log('m4u_msg_rec.msg_id -|' || l_m4u_msg_rec.msg_id || '|',1);
221 
222         --xmlg payload reference
223         IF l_payload_id IS NOT NULL THEN
224                 l_xmlg_doc_id   := l_payload_type || '.' || l_payload_id;
225                 l_xmlg_type     := m4u_cln_pkg.c_xmlg_type;
226 
227                 IF l_payload_dir = m4u_dmd_utils.c_dir_out THEN
228                         l_xmlg_styp     := m4u_cln_pkg.c_xmlg_styp_out;
229                 ELSE
230                         l_xmlg_styp     := m4u_cln_pkg.c_xmlg_styp_in;
231                 END IF;
232         END IF;
233 
234 
235         --todo: fill this junk  BEGIN
236         IF l_doc_type = m4u_dmd_utils.c_type_rfcin THEN
237                 l_cln_doc_id    :=  l_doc_type || '.' ||l_doc_id;
238                 l_cln_ref_id    :=  l_doc_type || '.' ||l_doc_id;
239                 l_cln_type      :=  l_doc_type ;
240                 l_cln_doc_type  :=  l_doc_type;
241                 l_payload_dir   :=  nvl(l_payload_dir,m4u_dmd_utils.c_dir_out);
242                 l_cln_dtl_msg   :=  l_processing_msg;
243 
244                 IF      l_doc_sts = m4u_dmd_utils.c_sts_ready  AND  l_retry_count = 0 THEN
245                         l_create_flag   := true;
246                         l_cln_doc_sts   := 'SUCCESS';
247                         l_cln_pt        := 'APPS';
248                         l_cln_sts       := 'INITIATED';
249                         l_cln_disp      := 'PENDING';
250                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_INIT');
251                         fnd_message.set_token('TYPE','Request for Catalogue Item Notification');
252                         l_cln_msg       := fnd_message.get;
253 
254                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_ready  AND  l_retry_count > 0 THEN
255                         l_create_flag   := false;
256                         l_cln_doc_sts   := 'SUCCESS';
257                         l_cln_pt        := 'APPS';
258                         l_cln_sts       := 'INITIATED';
259                         l_cln_disp      := 'PENDING';
260                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_RETRY');
261                         fnd_message.set_token('TYPE','Request for Catalogue Item Notification');
262                         l_cln_msg       := fnd_message.get;
263 
264                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_sent              THEN
265                         l_create_flag   := false;
266                         l_cln_doc_sts   := 'SUCCESS';
267                         l_cln_pt        := 'APPS';
268                         l_cln_sts       := 'STARTED';
269                         l_cln_disp      := 'PENDING';
270                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_SENT');
271                         fnd_message.set_token('TYPE','Request for Catalogue Item Notification');
272                         l_cln_msg       := fnd_message.get;
273 
274                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_success THEN
275                         l_create_flag   := false;
276                         l_cln_doc_sts   := 'SUCCESS';
277                         l_cln_pt        := 'B2B_SERVER';
278                         l_cln_sts       := 'COMPLETED';
279                         l_cln_disp      := 'SYNCH';
280 
281                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_SUCCESS');
282                         fnd_message.set_token('TYPE','Request for Catalogue Item Notification');
283                         l_cln_msg       := fnd_message.get;
284 
285 
286                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_fail    THEN
287                         l_create_flag   := false;
288                         l_cln_doc_sts   := 'SUCCESS';
289                         l_cln_pt        := 'B2B_SERVER';
290                         l_cln_sts       := 'COMPLETED';
291                         l_cln_disp      := 'REJECTED';
292 
293                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_FAILURE');
294                         fnd_message.set_token('TYPE','Request for Catalogue Item Notification');
295                         l_cln_msg       := fnd_message.get;
296 
297 
298                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_error     THEN
299                         l_create_flag   := false;
300                         l_cln_doc_sts   := 'ERROR';
301                         l_cln_pt        := 'B2B_SERVER';
302                         l_cln_sts       := 'ERROR';
303                         l_cln_disp      := 'FAILED';
304                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_ERROR');
305                         fnd_message.set_token('TYPE','Request for Catalogue Item Notification');
306                         l_cln_msg       := fnd_message.get;
307 
308                 END IF;
309 
310         ELSIF l_doc_type =  m4u_dmd_utils.c_type_cis  THEN
311 
312                 l_cln_doc_id    :=  l_doc_type || '.' ||l_doc_id;
313                 l_cln_ref_id    :=  l_doc_type || '.' ||l_doc_id;
314                 l_cln_type      :=  l_doc_type ;
315                 l_cln_doc_type  :=  l_doc_type;
316                 l_payload_dir   :=  nvl(l_payload_dir,m4u_dmd_utils.c_dir_out);
317                 l_cln_dtl_msg   :=  l_processing_msg;
318 
319                 IF      l_doc_sts = m4u_dmd_utils.c_sts_ready  AND  l_retry_count = 0 THEN
320                         l_create_flag   := true;
321                         l_cln_pt        := 'APPS';
322                         l_cln_doc_sts   := 'SUCCESS';
323                         l_cln_sts       := 'INITIATED';
324                         l_cln_disp      := 'PENDING';
325                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_INIT');
326                         fnd_message.set_token('TYPE','Catalogue Item Subscription');
327                         l_cln_msg       := fnd_message.get;
328 
329                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_ready  AND  l_retry_count > 0 THEN
330                         l_create_flag   := false;
331                         l_cln_pt        := 'APPS';
332                         l_cln_doc_sts   := 'SUCCESS';
333                         l_cln_sts       := 'INITIATED';
334                         l_cln_disp      := 'PENDING';
335                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_RETRY');
336                         fnd_message.set_token('TYPE','Catalogue Item Subscription');
337                         l_cln_msg       := fnd_message.get;
338 
339 
340                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_sent              THEN
341                         l_create_flag   := false;
342                         l_cln_pt        := 'APPS';
343                         l_cln_sts       := 'STARTED';
344                         l_cln_doc_sts   := 'SUCCESS';
345                         l_cln_disp      := 'PENDING';
346                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_SENT');
347                         fnd_message.set_token('TYPE','Catalogue Item Subscription');
348                         l_cln_msg       := fnd_message.get;
349 
350 
351                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_success THEN
352                         l_create_flag   := false;
353                         l_cln_pt        := 'B2B_SERVER';
354                         l_cln_doc_sts   := 'SUCCESS';
355                         l_cln_sts       := 'COMPLETED';
356                         l_cln_disp      := 'SYNCH';
357                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_SUCCESS');
358                         fnd_message.set_token('TYPE','Catalogue Item Subscription');
359                         l_cln_msg       := fnd_message.get;
360 
361 
362                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_fail    THEN
363                         l_create_flag   := false;
364                         l_cln_pt        := 'B2B_SERVER';
365                         l_cln_doc_sts   := 'SUCCESS';
366                         l_cln_sts       := 'COMPLETED';
367                         l_cln_disp      := 'REJECTED';
368                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_FAILURE');
369                         fnd_message.set_token('TYPE','Catalogue Item Subscription');
370                         l_cln_msg       := fnd_message.get;
371 
372 
373                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_error     THEN
374                         l_create_flag   := false;
375                         l_cln_doc_sts   := 'ERROR';
376                         l_cln_pt        := 'B2B_SERVER';
377                         l_cln_sts       := 'ERROR';
378                         l_cln_disp      := 'FAILED';
379                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_ERROR');
380                         fnd_message.set_token('TYPE','Catalogue Item Subscription');
381                         l_cln_msg       := fnd_message.get;
382 
383 
384                 END IF;
385 
386         --TBD:cin ack case
387         ELSIF   l_doc_type = m4u_dmd_utils.c_type_cin    THEN
388 
389                 l_cln_doc_id    :=  l_doc_type || '.' ||l_doc_id;
390                 l_cln_ref_id    :=  l_doc_type || '.' ||l_doc_id;
391                 l_cln_type      :=  l_doc_type;
392                 l_cln_doc_type  :=  l_doc_type;
393                 l_cln_dtl_msg   :=  l_processing_msg;
394 
395 
396                 IF      l_doc_sts = m4u_dmd_utils.c_sts_ready  AND  l_retry_count = 0 THEN
397 
398                         l_create_flag   := true;
399                         l_cln_pt        := 'B2B_SERVER';
400                         l_cln_sts       := 'INITIATED';
401                         l_cln_doc_sts   := 'SUCCESS';
402                         l_cln_disp      := 'PENDING';
403                         l_payload_dir   :=  m4u_dmd_utils.c_dir_in;
404                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_INIT');
405                         fnd_message.set_token('TYPE','Catalogue Item Notification');
406                         l_cln_msg       := fnd_message.get;
407 
408                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_ready  AND  l_retry_count > 0 THEN
409                         l_create_flag   := false;
410                         l_cln_pt        := 'B2B_SERVER';
411                         l_cln_sts       := 'INITIATED';
412                         l_cln_doc_sts   := 'SUCCESS';
413                         l_cln_disp      := 'PENDING';
414                         l_payload_dir   :=  m4u_dmd_utils.c_dir_in;
415                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_RETRY');
416                         fnd_message.set_token('TYPE','Catalogue Item Notification');
417                         l_cln_msg       := fnd_message.get;
418 
419                 --CIN sent to plm
420                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_sent
421                 AND     l_payload_type <> m4u_dmd_utils.c_type_cin_ack THEN
422                         l_create_flag   := false;
423                         l_cln_pt        := 'APPS';
424                         l_cln_sts       := 'STARTED';
425                         l_cln_doc_sts   := 'SUCCESS';
426                         l_cln_disp      := 'PENDING';
427                         l_payload_dir   :=  m4u_dmd_utils.c_dir_in;
428                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_SENT');
429                         fnd_message.set_token('TYPE','Catalogue Item Notification');
430                         l_cln_msg       := fnd_message.get;
431 
432                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_fail
433                 AND     l_payload_type <> m4u_dmd_utils.c_type_cin_ack THEN
434                         l_create_flag   := false;
435                         l_cln_pt        := 'APPS';
436                         l_cln_sts       := 'ERROR';
437                         l_cln_doc_sts   := 'ERROR';
438                         l_cln_disp      := 'REJECTED';
439                         l_payload_dir   :=  m4u_dmd_utils.c_dir_in;
440 
441                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_FAILURE');
442                         fnd_message.set_token('TYPE','Catalogue Item Notification');
443                         l_cln_msg       := fnd_message.get;
444 
445 
446                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_error
447                 AND     l_payload_type <> m4u_dmd_utils.c_type_cin_ack THEN
448                         l_create_flag   := false;
449                         l_cln_doc_sts   := 'ERROR';
450                         l_cln_pt        := 'APPS';
451                         l_cln_sts       := 'ERROR';
452                         l_cln_disp      := 'FAILED';
453                         l_payload_dir   :=  m4u_dmd_utils.c_dir_in;
454                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_ERROR');
455                         fnd_message.set_token('TYPE','Catalogue Item Notification');
456                         l_cln_msg       := fnd_message.get;
457                 -- CIN response sent
458                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_sent
459                 AND     l_payload_type = m4u_dmd_utils.c_type_cin_ack THEN
460                         l_create_flag   := false;
461                         l_cln_pt        := 'B2B_SERVER';
462                         l_cln_doc_sts   := 'SUCCESS';
463                         l_cln_sts       := 'STARTED';
464                         l_cln_disp      := 'PENDING';
465                         l_payload_dir   :=  m4u_dmd_utils.c_dir_out;
466                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_SENT');
467                         fnd_message.set_token('TYPE','Catalogue Item Notification Response');
468                         l_cln_msg       := fnd_message.get;
469 
470                 -- CIN response sent
471                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_error
472                 AND     l_payload_type = m4u_dmd_utils.c_type_cin_ack THEN
473                         l_create_flag   := false;
474                         l_cln_doc_sts   := 'ERROR';
475                         l_cln_pt        := 'B2B_SERVER';
476                         l_cln_sts       := 'ERROR';
477                         l_cln_disp      := 'FAILED';
478                         l_payload_dir   :=  m4u_dmd_utils.c_dir_out;
479                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_ERROR');
480                         fnd_message.set_token('TYPE','Catalogue Item Notification Response');
481                         l_cln_msg       := fnd_message.get;
482 
483                 -- CIN response sent
484                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_fail
485                 AND     l_payload_type = m4u_dmd_utils.c_type_cin_ack THEN
486                         l_create_flag   := false;
487                         l_cln_doc_sts   := 'ERROR';
488                         l_cln_pt        := 'B2B_SERVER';
489                         l_cln_sts       := 'ERROR';
490                         l_cln_disp      := 'FAILED';
491                         l_payload_dir   :=  m4u_dmd_utils.c_dir_out;
492                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_ERROR');
493                         fnd_message.set_token('TYPE','Catalogue Item Notification Response');
494                         l_cln_msg       := fnd_message.get;
495 
496 
497                 END IF;
498 
499 
500         ELSIF   l_doc_type = m4u_dmd_utils.c_type_cic    THEN
501 
502                 l_create_flag   := false;
503                 l_cln_doc_id    :=  m4u_dmd_utils.c_type_cin || '.' || l_m4u_doc_rec.ref_doc_id;
504                 l_cln_type      :=  m4u_dmd_utils.c_type_cin;
505                 l_cln_ref_id    :=  m4u_dmd_utils.c_type_cin || '.' || l_m4u_doc_rec.ref_doc_id;
506 
507                 l_cln_doc_type  :=  l_doc_type;
508                 l_payload_dir   :=  nvl(l_payload_dir,m4u_dmd_utils.c_dir_out);
509                 l_cln_dtl_msg   :=  l_processing_msg;
510 
511                 IF l_m4u_doc_rec.parameter1 = 'ACCEPTED' THEN
512                         l_cln_disp := 'ACCEPTED' ;
513                 ELSIF l_m4u_doc_rec.parameter1 = 'REJECTED' THEN
514                         l_cln_disp := 'REJECTED' ;
515                 ELSIF l_m4u_doc_rec.parameter1 = 'SYNCHRONISED' THEN
516                         l_cln_disp := 'SYNCH'    ;
517                 ELSIF l_m4u_doc_rec.parameter1 = 'REVIEW' THEN
518                         l_cln_disp := 'CORRECTION' ;
519                 END IF;
520 
521                 IF      l_doc_sts = m4u_dmd_utils.c_sts_ready  AND  l_retry_count = 0 THEN
522 
523                         l_cln_pt        := 'APPS';
524                         l_cln_doc_sts   := 'SUCCESS';
525                         l_cln_sts       := 'STARTED';
526                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_INIT');
527                         fnd_message.set_token('TYPE','Catalogue Item Confirmation');
528                         l_cln_msg       := fnd_message.get;
529 
530                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_ready  AND  l_retry_count > 0 THEN
531 
532                         l_cln_pt        := 'B2B_SERVER';
533                         l_cln_doc_sts   := 'SUCCESS';
534                         l_cln_sts       := 'STARTED';
535                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_RETRY');
536                         fnd_message.set_token('TYPE','Catalogue Item Confirmation');
537                         l_cln_msg       := fnd_message.get;
538 
539 
540                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_sent              THEN
541 
542                         l_cln_pt        := 'B2B_SERVER';
543                         l_cln_doc_sts   := 'SUCCESS';
544                         l_cln_sts       := 'STARTED';
545                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_SENT');
546                         fnd_message.set_token('TYPE','Catalogue Item Confirmation');
547                         l_cln_msg       := fnd_message.get;
548 
549 
550                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_success THEN
551 
552                         l_cln_pt        := 'B2B_SERVER';
553                         l_cln_doc_sts   := 'SUCCESS';
554                         l_cln_sts       := 'COMPLETED';
555                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_SUCCESS');
556                         fnd_message.set_token('TYPE','Catalogue Item Confirmation');
557                         l_cln_msg       := fnd_message.get;
558 
559 
560                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_fail    THEN
561 
562                         l_cln_pt        := 'B2B_SERVER';
563                         l_cln_sts       := 'ERROR';
564                         l_cln_doc_sts   := 'ERROR';
565                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_FAILURE');
566                         fnd_message.set_token('TYPE','Catalogue Item Confirmation');
567                         l_cln_msg       := fnd_message.get;
568 
569 
570                 ELSIF   l_doc_sts = m4u_dmd_utils.c_sts_error     THEN
571 
572                         l_cln_doc_sts   := 'ERROR';
573                         l_cln_pt        := 'B2B_SERVER';
574                         l_cln_sts       := 'ERROR';
575                         fnd_message.set_name('CLN','M4U_DMD_REQUEST_ERROR');
576                         fnd_message.set_token('TYPE','Catalogue Item Confirmation');
577                         l_cln_msg       := fnd_message.get;
578 
579 
580                 END IF;
581 
582 
583         END IF;
584 
585 
586 
587 
588 
589         IF l_create_flag THEN
590 
591                 m4u_dmd_utils.log('Call cln.create_collaboration',2);
592                 cln_ch_collaboration_pkg.create_collaboration
593                 (
594                         x_return_status             => l_ret_sts,
595                         x_msg_data                  => l_ret_msg,
596                         p_app_id                    => m4u_dmd_utils.c_app_id,
597                         p_ref_id                    => l_cln_ref_id,
598                         p_org_id                    => fnd_global.org_id,
599                         p_rel_no                    => null,
600                         p_doc_no                    => l_cln_doc_id,
601                         p_doc_rev_no                => null,
602                         p_xmlg_transaction_type     => l_xmlg_type,
603                         p_xmlg_transaction_subtype  => l_xmlg_styp,
604                         p_xmlg_document_id          => l_xmlg_doc_id,
605                         p_partner_doc_no            => null,
606                         p_coll_type                 => l_cln_type,
607                         p_tr_partner_type           => m4u_cln_pkg.c_party_type,
608                         p_tr_partner_id             => m4u_cln_pkg.g_party_id,
609                         p_tr_partner_site           => m4u_cln_pkg.g_party_site_id,
610                         p_resend_flag               => 'N',
611                         p_resend_count              => 0,
612                         p_doc_owner                 => fnd_global.user_id,
613                         p_init_date                 => sysdate,
614                         p_doc_creation_date         => sysdate,
615                         p_doc_revision_date         => null,
616                         p_doc_type                  => l_cln_doc_type,
617                         p_doc_dir                   => l_payload_dir,
618                         p_coll_pt                   => l_cln_pt,
619                         p_xmlg_msg_id               => null,
620                         p_unique1                   => null,
621                         p_unique2                   => null,
622                         p_unique3                   => null,
623                         p_unique4                   => null,
624                         p_unique5                   => null,
625                         p_sender_component          => null,
626                         p_rosettanet_check_required => false,
627                         p_xmlg_internal_control_number => null,
628                         p_collaboration_standard    => c_xmlg_std,
629                         p_attribute1                => l_m4u_doc_rec.msg_id,
630                         p_attribute2                => l_m4u_doc_rec.doc_id,
631                         p_attribute3                => l_m4u_doc_rec.ref_doc_id,
632                         p_attribute4                => l_m4u_doc_rec.orig_doc_id,
633                         p_attribute5                => l_m4u_doc_rec.type,
634                         p_attribute6                => l_m4u_doc_rec.action,
635                         p_attribute7                => l_m4u_doc_rec.top_gtin,
636                         p_attribute8                => l_m4u_doc_rec.info_provider_gln,
637                         p_attribute9                => l_m4u_doc_rec.data_recepient_gln,
638                         p_attribute10               => l_m4u_doc_rec.target_market_country,
639                         p_attribute11               => l_m4u_doc_rec.target_market_sub_div,
640                         p_attribute12               => l_m4u_doc_rec.parameter1,
641                         p_attribute13               => l_m4u_doc_rec.parameter2,
642                         p_attribute14               => l_m4u_doc_rec.parameter3,
643                         p_attribute15               => l_m4u_doc_rec.lparameter1,
644                         p_dattribute1               => l_m4u_doc_rec.doc_timestamp,
645                         x_coll_id                   => l_cln_id
646                 );
647                 m4u_dmd_utils.log('l_ret_sts    -|' || l_ret_sts   || '|',6);
648                 m4u_dmd_utils.log('l_ret_msg    -|' || l_ret_msg   || '|',6);
649                 m4u_dmd_utils.log('l_cln_id     -|' || l_cln_id    || '|',6);
650         END IF;
651 
652         m4u_dmd_utils.log('Query collaboration_id',6);
653         SELECT  collaboration_id
654         INTO    l_cln_id
655         FROM    cln_coll_hist_hdr
656         WHERE       application_reference_id = l_cln_ref_id
657                 AND collaboration_type       = l_cln_type
658                 AND rownum < 2;
659         m4u_dmd_utils.log('l_cln_id    -|' || l_cln_id   || '|',6);
660 
661 
662         m4u_dmd_utils.log('Call cln.update_collaboration',6);
663         cln_ch_collaboration_pkg.update_collaboration
664         (
665                 x_return_status             => l_ret_sts,
666                 x_msg_data                  => l_ret_msg,
667                 p_coll_id                   => l_cln_id,
668                 p_app_id                    => m4u_dmd_utils.c_app_id,
669                 p_ref_id                    => l_cln_ref_id,
670                 p_rel_no                    => NULL,
671                 p_doc_no                    => l_cln_doc_id,
672                 p_doc_rev_no                => NULL,
673                 p_xmlg_transaction_type     => l_xmlg_type,
674                 p_xmlg_transaction_subtype  => l_xmlg_styp,
675                 p_xmlg_document_id          => l_xmlg_doc_id,
676                 p_resend_flag               => NULL,
677                 p_resend_count              => NULL,
678                 p_disposition               => l_cln_disp,
679                 p_coll_status               => l_cln_sts,
680                 p_doc_type                  => l_cln_doc_type,
681                 p_doc_dir                   => l_payload_dir,
682                 p_coll_pt                   => l_cln_pt,
683                 p_org_ref                   => NULL,
684                 p_doc_status                => l_cln_doc_sts,
685                 p_notification_id           => NULL,
686                 p_msg_text                  => l_cln_msg,
687                 p_tr_partner_type           => m4u_cln_pkg.c_party_type,
688                 p_tr_partner_id             => m4u_cln_pkg.g_party_id,
689                 p_tr_partner_site           => m4u_cln_pkg.g_party_site_id,
690                 p_sender_component          => NULL,
691                 p_rosettanet_check_required => false,
692                 p_xmlg_msg_id               => null,
693                 p_attribute1                => l_m4u_doc_rec.msg_id,
694                 p_attribute2                => l_m4u_doc_rec.doc_id,
695                 p_attribute3                => l_m4u_doc_rec.ref_doc_id,
696                 p_attribute4                => l_m4u_doc_rec.orig_doc_id,
697                 p_attribute5                => l_m4u_doc_rec.type,
698                 p_attribute6                => l_m4u_doc_rec.action,
699                 p_attribute7                => l_m4u_doc_rec.top_gtin,
700                 p_attribute8                => l_m4u_doc_rec.info_provider_gln,
701                 p_attribute9                => l_m4u_doc_rec.data_recepient_gln,
702                 p_attribute10               => l_m4u_doc_rec.target_market_country,
703                 p_attribute11               => l_m4u_doc_rec.target_market_sub_div,
704                 p_attribute12               => l_m4u_doc_rec.parameter1,
705                 p_attribute13               => l_m4u_doc_rec.parameter2,
706                 p_attribute14               => l_m4u_doc_rec.parameter3,
707                 p_attribute15               => l_m4u_doc_rec.lparameter1,
708                 p_dattribute1               => l_m4u_doc_rec.doc_timestamp,
709                 x_dtl_coll_id               => l_cln_dtl_id,
710                 p_xml_event_key             => null
711         );
712         m4u_dmd_utils.log('l_ret_sts    -|' || l_ret_sts   || '|',6);
713         m4u_dmd_utils.log('l_ret_msg    -|' || l_ret_msg   || '|',6);
714         m4u_dmd_utils.log('l_cln_dtl_id -|' || l_cln_dtl_id|| '|',6);
715 
716 
717         IF l_cln_dtl_msg IS NOT NULL THEN
718                 -- add coll message
719                 m4u_dmd_utils.log('Call cln.add_collaboration_messages',6);
720                 cln_ch_collaboration_pkg.add_collaboration_messages
721                 (
722                         x_return_status   => l_ret_sts,
723                         x_msg_data        => l_ret_msg,
724                         p_dtl_coll_id     => l_cln_dtl_id,
725                         p_ref1            => NULL,
726                         p_ref2            => NULL,
727                         p_ref3            => NULL,
728                         p_ref4            => NULL,
729                         p_ref5            => NULL,
730                         p_dtl_msg         => l_cln_dtl_msg
731                 );
732                 m4u_dmd_utils.log('l_ret_sts    -|' || l_ret_sts   || '|',6);
733                 m4u_dmd_utils.log('l_ret_msg    -|' || l_ret_msg   || '|',6);
734 
735         END IF;
736 
737         m4u_dmd_utils.log('l_ret_sts    -|' || l_ret_sts   || '|',6);
738         m4u_dmd_utils.log('l_ret_msg    -|' || l_ret_msg   || '|',6);
739         m4u_dmd_utils.log('Exiting m4u_cln_pkg.update_collab - Success' ,6);
740 
741         RETURN 'SUCCESS';
742 
743    EXCEPTION
744         WHEN OTHERS THEN
745                 m4u_dmd_utils.log('m4u_cln_pkg.update_collab',6);
746                 m4u_dmd_utils.log('Unexpected error occured while processing',6);
747                 m4u_dmd_utils.log(SQLCODE || '-' || SQLERRM,6);
748                 RETURN 'SUCCESS';
749    END update_collab;
750 
751 
752 ----------------------------------------------------------------------------------------
753         -- Name
754         --      update_tp_detail
755         -- Purpose
756         --      This procedure sets up the XMLGateway Trading Partner Setup detail
757         --      for a single transaction based on the params
758         --      If detail record is present it updates else inserts
759         PROCEDURE update_tp_detail
760         (
761                 p_tp_hdr_id             IN              NUMBER,
762                 p_direction             IN              VARCHAR2,
763                 x_ret_sts               OUT NOCOPY      VARCHAR2,
764                 x_ret_msg               OUT NOCOPY      VARCHAR2
765         )
766         IS
767                 l_ret_msg       VARCHAR2(4000);
768                 l_ret_sts       VARCHAR2(10);
769                 l_record_found  BOOLEAN;
770 
771                 l_party_type    VARCHAR2(30);
772                 l_standard_code VARCHAR2(30);
773 
774                 l_ext_type      VARCHAR2(30);
775                 l_txn_type      VARCHAR2(30);
776                 l_doc_conf      VARCHAR2(30);
777                 l_map           VARCHAR2(30);
778 
779                 l_ext_subtype   VARCHAR2(30);
780                 l_txn_subtype   VARCHAR2(30);
781                 l_conn_type     VARCHAR2(30);
782                 l_protocol      VARCHAR2(30);
783                 l_protocol_addr VARCHAR2(30);
784                 l_user          VARCHAR2(30);
785                 l_passwd        VARCHAR2(30);
786                 l_src_loc       VARCHAR2(30);
787                 l_progress      VARCHAR2(200);
788                 l_tp_dtl_id     NUMBER;
789                 l_ext_process_id NUMBER;
790         BEGIN
791 
792 
793                 m4u_dmd_utils.log('Entering m4u_cln_pkg.add_or_update_tp_detail',2);
794                 m4u_dmd_utils.log('p_tp_hdr_id -|' || p_tp_hdr_id || '|',1);
795                 m4u_dmd_utils.log('p_direction -|' || p_direction || '|',1);
796 
797 
798                 --------------------------------------------------------------------------
799                 --Set ECX api input params
800                 l_ext_type      := m4u_cln_pkg.c_xmlg_type;
801                 l_standard_code := m4u_cln_pkg.c_xmlg_std;
802                 l_txn_type      := m4u_cln_pkg.c_xmlg_type;
803                 l_party_type    := m4u_cln_pkg.c_party_type;
804                 l_doc_conf      := '2';
805                 l_map           := 'm4u_230_cin_out';
806 
807                 IF p_direction = m4u_dmd_utils.c_dir_out THEN
808                 --out specific values
809                         l_src_loc       := 7777;
810                         l_ext_subtype   := m4u_cln_pkg.c_xmlg_styp_out;
811                         l_txn_subtype   := m4u_cln_pkg.c_xmlg_styp_out;
812                         l_conn_type     := 'DIRECT';
813                         l_protocol      := 'HTTP';
814                         l_protocol_addr := 'http://none';
815                         l_user          := 'operations';
816                         l_passwd        := 'welcome';
817                 ELSE
818                 --in specific values
819                         l_src_loc       := 7777;
820                         l_ext_subtype   := m4u_cln_pkg.c_xmlg_styp_in;
821                         l_txn_subtype   := m4u_cln_pkg.c_xmlg_styp_in;
822                         l_conn_type     := null;
823                         l_protocol      := null;
824                         l_protocol_addr := null;
825                         l_user          := null;
826                         l_passwd        := null;
827                 END IF;
828                 --------------------------------------------------------------------------
829                 -- Query ecx_tp_detail_id
830                 m4u_dmd_utils.log('Query ecx_tp_detail_id',2);
831                 l_progress := 'Query ecx_tp_detail_id';
832 
833                 BEGIN
834                         SELECT tp_detail_id
835                         INTO   l_tp_dtl_id
836                         FROM
837                                 ecx_tp_details    tpd,
838                                 ecx_tp_headers    tph,
839                                 ecx_ext_processes extp,
840                                 ecx_transactions  txn,
841                                 ecx_standards     svl
842                         WHERE   1=1
843                                 AND tph.tp_header_id            = tpd.tp_header_id
844                                 AND tpd.ext_process_id          = extp.ext_process_id
845                                 AND extp.transaction_id         = txn.transaction_id
846                                 AND extp.standard_id            = svl.standard_id
847                                 AND svl.standard_code           = l_standard_code
848                                 AND extp.ext_type               = l_ext_type
849                                 AND extp.ext_subtype            = l_ext_subtype
850                                 AND extp.direction              = p_direction
851                                 AND txn.transaction_type        = l_txn_type
852                                 AND txn.transaction_subtype     = l_txn_subtype
853                                 AND tph.tp_header_id            = p_tp_hdr_id;
854 
855                         l_record_found   := TRUE;
856                         m4u_dmd_utils.log('l_tp_dtl_id -|' || l_tp_dtl_id || '|',1);
857 
858                 EXCEPTION
859                         WHEN NO_DATA_FOUND THEN
860                                 m4u_dmd_utils.log('No data found',1);
861                                 l_record_found   := FALSE;
862                 END;
863 
864                 -------------------------------------------------------------------------
865                 --Query extp.ext_process_id
866                 m4u_dmd_utils.log('Query extp.ext_process_id',1);
867                 l_progress := 'Query ecx_tp_detail_id';
868 
869 
870                 SELECT  extp.ext_process_id
871                 INTO    l_ext_process_id
872                 FROM    ecx_ext_processes extp,
873                         ecx_transactions  txn,
874                         ecx_standards     svl
875                 WHERE   1=1
876                         AND extp.transaction_id         = txn.transaction_id
877                         AND extp.standard_id            = svl.standard_id
878                         AND svl.standard_code           = l_standard_code
879                         AND extp.ext_type               = l_ext_type
880                         AND extp.ext_subtype            = l_ext_subtype
881                         AND extp.direction              = p_direction
882                         AND txn.party_type              = l_party_type
883                         AND txn.transaction_type        = l_txn_type
884                         AND txn.transaction_subtype     = l_txn_subtype;
885 
886                 m4u_dmd_utils.log('l_ext_process_id -|' || l_ext_process_id || '|',1);
887                 -------------------------------------------------------------------------
888                 -- Create or update ECX tp detail
889                 IF NOT l_record_found THEN
890                 --Create
891                         m4u_dmd_utils.log('Call ecx_tp_api.create_tp_detail',1);
892                         l_progress := 'Call ecx_tp_api.create_tp_detail';
893 
894                         ecx_tp_api.create_tp_detail
895                         (
896                                 x_return_status                 =>  l_ret_sts,
897                                 x_msg                           =>  l_ret_msg,
898                                 x_tp_detail_id                  =>  l_tp_dtl_id,
899                                 p_tp_header_id                  =>  p_tp_hdr_id,
900                                 p_ext_process_id                =>  l_ext_process_id,
901                                 p_map_code                      =>  l_map,
902                                 p_connection_type               =>  l_conn_type,
903                                 p_hub_user_id                   =>  null,
904                                 p_protocol_type                 =>  l_protocol,
905                                 p_protocol_address              =>  l_protocol_addr,
906                                 p_username                      =>  l_user,
907                                 p_password                      =>  l_passwd,
908                                 p_routing_id                    =>  null,
909                                 p_source_tp_location_code       =>  l_src_loc,
910                                 p_external_tp_location_code     =>  null,
911                                 p_confirmation                  =>  l_doc_conf
912                         );
913 
914                         m4u_dmd_utils.log('l_ret_sts -|' || l_ret_sts || '|',1);
915                         m4u_dmd_utils.log('l_ret_msg -|' || l_ret_msg || '|',1);
916 
917 
918 
919                         IF l_ret_sts <> '0' THEN
920                              x_ret_sts := fnd_api.g_ret_sts_error;
921                              x_ret_msg := l_ret_msg;
922                              m4u_dmd_utils.log('Exiting add_or_update_tp_detail - Failure',6);
923                              RETURN;
924                         END IF;
925                 ELSE
926                 --Update
927                         m4u_dmd_utils.log('Call ecx_tp_api.update_tp_detail',1);
928                         l_progress := 'Call ecx_tp_api.update_tp_detail';
929 
930                         ecx_tp_api.update_tp_detail
931                         (
932                                 x_return_status                 => l_ret_sts,
933                                 x_msg                           => l_ret_msg,
934                                 p_tp_detail_id                  => l_tp_dtl_id,
935                                 p_map_code                      => l_map,
936                                 p_ext_process_id                => l_ext_process_id,
937                                 p_connection_type               => l_conn_type,
938                                 p_hub_user_id                   => null,
939                                 p_protocol_type                 => l_protocol,
940                                 p_protocol_address              => l_protocol_addr,
941                                 p_username                      => l_user,
942                                 p_password                      => l_passwd,
943                                 p_routing_id                    => null,
944                                 p_source_tp_location_code       => l_src_loc,
945                                 p_external_tp_location_code     => null,
946                                 p_confirmation                  => l_doc_conf,
947                                 p_passupd_flag                  => 'Y'
948                         );
949 
950                         m4u_dmd_utils.log('l_ret_sts -|' || l_ret_sts || '|',1);
951                         m4u_dmd_utils.log('l_ret_msg -|' || l_ret_msg || '|',1);
952 
953                         IF l_ret_sts <> '0' THEN
954                              x_ret_sts := fnd_api.g_ret_sts_error;
955                              x_ret_msg := l_ret_msg;
956                              m4u_dmd_utils.log('Exiting add_or_update_tp_detail - Failure',6);
957                              RETURN;
958                         END IF;
959                 END IF;
960                 -------------------------------------------------------------------------
961                 x_ret_sts := fnd_api.g_ret_sts_success;
962                 x_ret_msg := '';
963 
964                 m4u_dmd_utils.log('Exiting add_or_update_tp_detail - Success',2);
965                 RETURN; /*success*/
966         EXCEPTION
967                 WHEN OTHERS THEN
968                         x_ret_sts := fnd_api.g_ret_sts_error;
969                         x_ret_msg := l_progress || '-' || SQLCODE || '-' || SQLERRM;
970         END update_tp_detail;
971 
972 ---------------------------------------------------------------------------------------------
973         -- Name
974         --      SETUP
975         -- Purpose
976         --      This procedure is called from a concurrent program(can be called from anywhere actually).
977         --      This procedure does the setup required for m4u
978         --              i)      Setup default TP location in HR_LOCATIONS
979         --              ii)     Setup XMLGateway trading partner definition
980         -- Arguments
981         --      x_err_buf                       => API out result param for concurrent program calls
982         --      x_retcode                       => API out result param for concurrent program calls
983         -- Notes
984         --      The concurrent program will be failed in case of any error
985         PROCEDURE setup_cln
986         (
987                 x_errbuf             OUT NOCOPY VARCHAR2,
988                 x_retcode            OUT NOCOPY NUMBER
989         )
990         IS
991                 l_location_code                 VARCHAR2(60);
992                 l_country                       VARCHAR2(60);
993                 l_style                         VARCHAR2(30);
994                 l_description                   VARCHAR2(240);
995                 l_addr_line_1                   VARCHAR2(240);
996 
997                 l_ret_msg                       VARCHAR2(4000);
998                 l_err_msg                       VARCHAR2(4000);
999                 l_ret_sts                       VARCHAR2(5);
1000                 l_progress                      VARCHAR2(100);
1001 
1002                 l_location_id                   NUMBER;
1003                 l_obj_ver_num                   NUMBER;
1004                 l_tp_hdr_id                     NUMBER;
1005 
1006                 l_record_found                  BOOLEAN;
1007 
1008         BEGIN
1009 
1010                 m4u_dmd_utils.log('Entering m4u_dmd_utils.setup_cln',2);
1011 
1012                 l_location_code := m4u_cln_pkg.c_party_site;
1013                 l_description   := 'Dummy seeded location for M4U transactions';
1014                 l_addr_line_1   := 'Princeton';
1015                 l_style         := 'US_GLB';
1016                 l_country       := 'US';
1017                 -----------------------------------------------------------------------------
1018                 -- Query HR Locations
1019                 -- Check if record exists. Create Locations if it does not else Update Location value
1020                 l_progress      := 'Query for hr_locations_all based on location_code';
1021                 BEGIN
1022                         m4u_dmd_utils.log('Query hr_locations_all',1);
1023                         SELECT location_id, object_version_number
1024                         INTO   l_location_id, l_obj_ver_num
1025                         FROM   hr_locations_all
1026                         WHERE  location_code = l_location_code
1027                                 AND ROWNUM < 2;
1028                         l_record_found   := TRUE;
1029 
1030                         m4u_dmd_utils.log('l_location_id  -|'||l_location_id || '|', 1);
1031                         m4u_dmd_utils.log('l_obj_ver_num  -|'||l_obj_ver_num || '|', 1);
1032                 EXCEPTION
1033                         WHEN NO_DATA_FOUND THEN
1034                                 m4u_dmd_utils.log('No data found', 1);
1035                                l_record_found   := FALSE;
1036                 END;
1037                 -----------------------------------------------------------------------------
1038                 --HR location
1039                 IF NOT l_record_found THEN
1040                 --Create
1041                         m4u_dmd_utils.log('Call HR_LOCATION_API.create_location', 2);
1042                         l_progress      := 'Call to HR_LOCATION_API.create_location';
1043 
1044                         HR_LOCATION_API.create_location
1045                         (
1046                                      p_effective_date          => sysdate,
1047                                      p_language_code           => userenv('LANG'),
1048                                      p_location_code           => l_location_code,
1049                                      p_description             => l_description,
1050                                      p_address_line_1          => l_addr_line_1,
1051                                      p_country                 => l_country,
1052                                      p_style                   => l_style,
1053                                      p_location_id             => l_location_id,
1054                                      p_object_version_number   => l_obj_ver_num
1055                         );
1056 
1057                         m4u_dmd_utils.log('l_location_id -|'||l_location_id || '|', 1);
1058                         m4u_dmd_utils.log('l_obj_ver_num -|'||l_obj_ver_num || '|', 1);
1059 
1060                 ELSE
1061                 --Update
1062                         m4u_dmd_utils.log('Call HR_LOCATION_API.update_location', 2);
1063                         l_progress      := 'Call HR_LOCATION_API.update_location';
1064 
1065                         HR_LOCATION_API.update_location
1066                         (
1067                                 p_effective_date          => sysdate,
1068                                 p_language_code           => userenv('LANG'),
1069                                 p_location_code           => l_location_code,
1070                                 p_description             => l_description,
1071                                 p_address_line_1          => l_addr_line_1,
1072                                 p_country                 => l_country,
1073                                 p_style                   => l_style,
1074                                 p_location_id             => l_location_id,
1075                                 p_object_version_number   => l_obj_ver_num
1076                         );
1077 
1078                         m4u_dmd_utils.log('l_location_id -|'||l_location_id || '|', 1);
1079                         m4u_dmd_utils.log('l_obj_ver_num -|'||l_obj_ver_num || '|', 1);
1080                 END IF;
1081 
1082                 -- reset the value for next phase
1083                 l_record_found   := FALSE;
1084                 -----------------------------------------------------------------------------
1085                 --Query TP Header Id
1086                 l_progress      := 'Query ecx_tp_headers : party_id - ' || l_location_id;
1087                 -- Check if record exists. Create TP Header if it does not else Update value
1088                 BEGIN
1089                         SELECT  tp_header_id
1090                         INTO    l_tp_hdr_id
1091                         FROM    ecx_tp_headers
1092                         WHERE       party_type          = 'I'
1093                                 AND party_id            = l_location_id
1094                                 AND party_site_id       = l_location_id;
1095 
1096                         l_record_found   := TRUE;
1097                         m4u_dmd_utils.log('l_tp_hdr_id    -'||l_tp_hdr_id || '|', 1);
1098                 EXCEPTION
1099                         WHEN NO_DATA_FOUND THEN
1100                                 m4u_dmd_utils.log('No data found', 1);
1101                                l_record_found   := FALSE;
1102                 END;
1103                 -----------------------------------------------------------------------------
1104                 --ECX TP Header
1105                 IF NOT l_record_found THEN
1106                 --create
1107                         m4u_dmd_utils.log('Call ecx_tp_api.create_trading_partner', 2);
1108                         l_progress      := 'Call ecx_tp_api.create_trading_partner';
1109 
1110                         ecx_tp_api.create_trading_partner
1111                         (
1112                                 x_return_status         => l_ret_sts,
1113                                 x_msg                   => l_ret_msg,
1114                                 x_tp_header_id          => l_tp_hdr_id,
1115                                 p_party_type            => m4u_cln_pkg.c_party_type,
1116                                 p_party_id              => l_location_id,
1117                                 p_party_site_id         => l_location_id,
1118                                 p_company_admin_email   => m4u_cln_pkg.c_tp_email
1119                         );
1120 
1121                         m4u_dmd_utils.log('l_ret_msg   -|'||l_ret_msg ||'|', 1);
1122                         m4u_dmd_utils.log('l_ret_sts   -|'||l_ret_sts ||'|', 1);
1123                         IF l_ret_sts <> '0' THEN
1124                              l_err_msg := l_ret_msg;
1125                              RAISE FND_API.G_EXC_ERROR;
1126                         END IF;
1127 
1128 
1129                 ELSE
1130                 --Update
1131                         m4u_dmd_utils.log('Call ecx_tp_api.update_trading_partner', 2);
1132                         l_progress      := 'Call ecx_tp_api.update_trading_partner';
1133 
1134                         ecx_tp_api.update_trading_partner
1135                         (
1136                                 x_return_status         => l_ret_sts,
1137                                 x_msg                   => l_ret_msg,
1138                                 p_tp_header_id          => l_tp_hdr_id,
1139                                 p_company_admin_email   => m4u_cln_pkg.c_tp_email
1140                         );
1141 
1142                         m4u_dmd_utils.log('l_ret_msg   -|'||l_ret_msg ||'|', 1);
1143                         m4u_dmd_utils.log('l_ret_sts   -|'||l_ret_sts ||'|', 1);
1144                         IF l_ret_sts <> '0' THEN
1145                              l_err_msg :=   l_ret_msg;
1146                              RAISE FND_API.G_EXC_ERROR;
1147                         END IF;
1148                 END IF;
1149                 -----------------------------------------------------------------------------
1150                 --TP details
1151 
1152                 --Out txn
1153                 l_progress      := 'Call update_tp_detail - OUT';
1154                 update_tp_detail(l_tp_hdr_id,m4u_dmd_utils.c_dir_out,l_ret_sts,l_ret_msg);
1155                 IF l_ret_sts <> fnd_api.g_ret_sts_success  THEN
1156                         l_err_msg := l_ret_msg;
1157                         RAISE FND_API.G_EXC_ERROR;
1158                 END IF;
1159                 --In txn
1160                 l_progress      := 'Call update_tp_detail - IN';
1161                 update_tp_detail(l_tp_hdr_id,m4u_dmd_utils.c_dir_in ,l_ret_sts,l_ret_msg);
1162                 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
1163                         l_err_msg := l_ret_msg;
1164                         RAISE FND_API.G_EXC_ERROR;
1165                 END IF;
1166                 -----------------------------------------------------------------------------
1167                 --Done, return success
1168                 x_retcode  := 0;
1169                 x_errbuf   := '';
1170                 m4u_dmd_utils.log('Exiting m4u_dmd_utils.setup_cln - SUCCESS',2);
1171                 RETURN; /*sucess*/
1172         EXCEPTION
1173                 WHEN OTHERS THEN
1174                         x_errbuf        := 'Unexpected error at: ' || l_progress || '-' || l_err_msg ;
1175                         IF l_err_msg IS NULL THEN
1176                                 x_errbuf := x_errbuf || '-' || SQLCODE || '-' || SQLERRM;
1177                         END IF;
1178                         x_retcode       :=  2;
1179         END setup_cln;
1180 
1181 ---------------------------------------------------------------------------------------
1182 
1183     BEGIN
1184         /* Package initialization. */
1185         SELECT to_char(e.party_id), to_char(e.party_site_id)
1186         INTO   g_party_id,          g_party_site_id
1187         FROM   hr_locations_all h,
1188                ecx_tp_headers   e
1189         WHERE  h.location_id   = e.party_id
1190         AND    e.party_type    = m4u_cln_pkg.c_party_type
1191         AND    h.location_code = m4u_cln_pkg.c_party_site;
1192 
1193         g_init_success := TRUE;
1194 
1195     EXCEPTION
1196         WHEN NO_DATA_FOUND THEN
1197                 --Indicate that package has NOT been properly initialized -
1198                 -- g_party_id and g_party_site_id are not valid for XMLG and CLN usage.
1199         g_init_success := FALSE;
1200 
1201 END m4u_cln_pkg;