[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;