[Home] [Help]
PACKAGE BODY: APPS.M4U_DMD_MESSAGE_PKG
Source
1 PACKAGE BODY m4u_dmd_message_pkg AS
2 /* $Header: M4UDMSGB.pls 120.5 2007/08/29 10:23:17 bsaratna noship $ */
3
4 PROCEDURE create_dom_from_clob_pvt
5 (
6 p_xml IN CLOB,
7 x_dom OUT NOCOPY xmldom.domdocument
8 )
9 IS
10 l_prsr xmlparser.parser;
11 BEGIN
12 m4u_dmd_utils.log('Entering m4u_dmd_messages.create_dom_from_clob_pvt',2);
13
14 l_prsr := xmlparser.newparser;
15 m4u_dmd_utils.log('Got XMLparser handle' ,2);
16
17 xmlparser.setValidationMode(l_prsr, FALSE);
18 xmlparser.setPreserveWhiteSpace(l_prsr, TRUE);
19 m4u_dmd_utils.log('Set parser properties' ,2);
20
21 xmlparser.parseCLOB (l_prsr, p_xml);
22 m4u_dmd_utils.log('Parse clob successfull' ,2);
23
24 x_dom := xmlparser.getdocument (l_prsr);
25 m4u_dmd_utils.log('XML dom obtained' ,2);
26
27 xmlparser.freeparser (l_prsr);
28 m4u_dmd_utils.log('Exiting m4u_dmd_messages.create_dom_from_clob_pvt',2);
29
30 END create_dom_from_clob_pvt;
31
32 PROCEDURE send_rfcin
33 (
34 p_user_gln IN VARCHAR2,
35 p_retailer_gln IN VARCHAR2,
36 p_datapool_gln IN VARCHAR2,
37
38 p_reload_flag IN VARCHAR2,
39 p_info_provider_gln IN VARCHAR2,
40 p_tgt_mkt_country IN VARCHAR2,
41 p_tgt_mkt_subdiv IN VARCHAR2,
42 p_gtin IN VARCHAR2,
43 p_cat_type IN VARCHAR2,
44 p_cat_code IN VARCHAR2,
45 x_msg_id OUT NOCOPY VARCHAR2,
46 x_ret_sts OUT NOCOPY VARCHAR2,
47 x_ret_msg OUT NOCOPY VARCHAR2
48 ) AS
49 l_msg_id VARCHAR2(30);
50 l_doc_id VARCHAR2(30);
51 l_ret_sts VARCHAR2(1);
52 l_ret_msg VARCHAR2(4000);
53 l_err_msg VARCHAR2(4000);
54 l_err_api VARCHAR2(100) := 'm4u_dmd_message_pkg.send_rfcin';
55 l_user_id VARCHAR2(100);
56 BEGIN
57
58
59 m4u_dmd_utils.log('Entering m4u_dmd_request.send_rfcin' ,2);
60
61
62 m4u_dmd_utils.log('=====================================' ,1);
63 m4u_dmd_utils.log('p_user_gln -|' || p_user_gln || '|',1);
64 m4u_dmd_utils.log('p_retailer_gln -|' || p_retailer_gln || '|',1);
65 m4u_dmd_utils.log('p_datapool_gln -|' || p_datapool_gln || '|',1);
66
67 m4u_dmd_utils.log('p_reload_flag -|' || p_reload_flag || '|',1);
68 m4u_dmd_utils.log('p_tgt_mkt_country-|' || p_tgt_mkt_country || '|',1);
69 m4u_dmd_utils.log('p_tgt_mkt_subdiv -|' || p_tgt_mkt_subdiv || '|',1);
70 m4u_dmd_utils.log('p_gtin -|' || p_gtin || '|',1);
71 m4u_dmd_utils.log('p_cat_type -|' || p_cat_type || '|',1);
72 m4u_dmd_utils.log('p_cat_code -|' || p_cat_code || '|',1);
73 m4u_dmd_utils.log('p_info_prv_gln -|' || p_info_provider_gln|| '|',1);
74
75
76 l_user_id := m4u_dmd_utils.get_gln_user(p_user_gln);
77
78 m4u_dmd_utils.log('l_user_id -|' || l_user_id || '|',1);
79 m4u_dmd_utils.log('=====================================' ,1);
80
81 m4u_dmd_utils.log('Call m4u_dmd_requests.create_request' ,1);
82 m4u_dmd_requests.create_request
83 (
84 p_type => m4u_dmd_utils.c_type_rfcin,
85 p_direction => m4u_dmd_utils.c_dir_out,
86 p_status => m4u_dmd_utils.c_sts_ready,
87 p_msg_timstamp => sysdate,
88 p_orig_msg_id => 'GENERATE',
89
90 p_sender_gln => p_user_gln,
91 p_receiver_gln => p_datapool_gln,
92 p_rep_party_gln => p_retailer_gln,
93 p_user_gln => p_user_gln,
94 p_user_id => l_user_id,
95
96 x_msg_id => l_msg_id,
97 x_ret_sts => l_ret_sts,
98 x_ret_msg => l_ret_msg
99 );
100
101 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
102 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
103 m4u_dmd_utils.log('l_msg_id - |' || l_msg_id || '|',1);
104
105 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
106 l_err_msg := l_ret_msg;
107 RAISE fnd_api.g_exc_error;
108 END IF;
109
110 m4u_dmd_utils.log('Call m4u_dmd_requests.create_document' ,1);
111
112 m4u_dmd_requests.create_document
113 (
114 p_msg_id => l_msg_id,
115 p_type => m4u_dmd_utils.c_type_rfcin,
116 p_action => m4u_dmd_utils.c_action_add,
117 p_doc_status => m4u_dmd_utils.c_sts_ready,
118 p_func_status => null,
119 p_processing_msg => null,
120 p_timestamp => sysdate,
121 p_orig_doc_id => 'GENERATE',
122
123 p_top_gtin => p_gtin,
124 p_info_provider_gln => p_info_provider_gln,
125 p_data_rcpt_gln => nvl(p_retailer_gln,p_user_gln),
126 p_tgt_mkt_ctry => p_tgt_mkt_country,
127 p_tgt_mkt_div => p_tgt_mkt_subdiv,
128
129 p_param1 => substr(p_reload_flag,1,50),
130 p_param2 => substr(p_cat_type,1,50),
131 p_param3 => substr(p_cat_code,1,50),
132
133 x_doc_id => l_doc_id,
134 x_ret_sts => l_ret_sts,
135 x_ret_msg => l_ret_msg
136 );
137
138 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
139 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
140 m4u_dmd_utils.log('l_doc_id - |' || l_doc_id || '|',1);
141
142 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
143 l_err_msg := l_ret_msg;
144 RAISE fnd_api.g_exc_error;
145 END IF;
146
147 x_ret_sts := fnd_api.g_ret_sts_success;
148 x_ret_msg := '';
149 x_msg_id := l_msg_id;
150
151 m4u_dmd_utils.log('Exiting m4u_dmd_request.send_rfcin - Success' ,2);
152 RETURN; --success
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
157 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
158 RETURN; --fail
159 END send_rfcin;
160
161 PROCEDURE send_cis
162 (
163 p_cis_name IN VARCHAR2,
164
165 p_user_gln IN VARCHAR2,
166 p_retailer_gln IN VARCHAR2,
167 p_datapool_gln IN VARCHAR2,
168
169 p_operation IN VARCHAR2,
170 p_info_provider_gln IN VARCHAR2,
171 p_tgt_mkt_country IN VARCHAR2,
172 p_tgt_mkt_subdiv IN VARCHAR2,
173 p_gtin IN VARCHAR2,
174 p_cat_type IN VARCHAR2,
175 p_cat_code IN VARCHAR2,
176 x_msg_id OUT NOCOPY VARCHAR2,
177 x_ret_sts OUT NOCOPY VARCHAR2,
178 x_ret_msg OUT NOCOPY VARCHAR2
179
180 ) AS
181 l_ret_sts VARCHAR2(1);
182 l_ret_msg VARCHAR2(4000);
183 l_msg_id VARCHAR2(30);
184 l_doc_id VARCHAR2(30);
185 l_err_msg VARCHAR2(4000);
186 l_err_api VARCHAR2(30) := 'm4u_dmd_message_pkg.send_cis';
187 l_user_id VARCHAR2(100);
188 BEGIN
189
190 m4u_dmd_utils.log('Entering m4u_dmd_request.send_cis', 2);
191 m4u_dmd_utils.log('=====================================', 1);
192 m4u_dmd_utils.log('p_cis_name -|' || p_cis_name || '|',1);
193 m4u_dmd_utils.log('p_user_gln -|' || p_user_gln || '|',1);
194 m4u_dmd_utils.log('p_retailer_gln -|' || p_retailer_gln || '|',1);
195 m4u_dmd_utils.log('p_datapool_gln -|' || p_datapool_gln || '|',1);
196 m4u_dmd_utils.log('p_operation -|' || p_operation || '|',1);
197 m4u_dmd_utils.log('p_tgt_mkt_country -|' || p_tgt_mkt_country || '|',1);
198 m4u_dmd_utils.log('p_tgt_mkt_subdiv -|' || p_tgt_mkt_subdiv || '|',1);
199 m4u_dmd_utils.log('p_gtin -|' || p_gtin || '|',1);
200 m4u_dmd_utils.log('p_cat_type -|' || p_cat_type || '|',1);
201 m4u_dmd_utils.log('p_cat_code -|' || p_cat_code || '|',1);
202 m4u_dmd_utils.log('p_info_prv_gln -|' || p_info_provider_gln|| '|',1);
203
204
205 l_user_id := m4u_dmd_utils.get_gln_user(p_user_gln);
206
207
208 m4u_dmd_utils.log('l_user_id -|' || l_user_id || '|',1);
209 m4u_dmd_utils.log('=====================================', 1);
210 m4u_dmd_utils.log('Call m4u_dmd_requests.create_request' , 1);
211
212 m4u_dmd_requests.create_request
213 (
214 p_type => m4u_dmd_utils.c_type_cis,
215 p_direction => m4u_dmd_utils.c_dir_out,
216 p_status => m4u_dmd_utils.c_sts_ready,
217 p_msg_timstamp => sysdate,
218 p_orig_msg_id => 'GENERATE',
219
220 p_sender_gln => p_user_gln,
221 p_receiver_gln => p_datapool_gln,
222 p_rep_party_gln => p_retailer_gln,
223 p_user_gln => p_user_gln,
224 p_user_id => l_user_id,
225
226 x_msg_id => l_msg_id,
227 x_ret_sts => l_ret_sts,
228 x_ret_msg => l_ret_msg
229 );
230
231 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
232 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
233 m4u_dmd_utils.log('l_msg_id - |' || l_msg_id || '|',1);
234
235 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
236 l_err_msg := l_ret_msg;
237 RAISE fnd_api.g_exc_error;
238 END IF;
239
240 m4u_dmd_utils.log('Call m4u_dmd_requests.create_document' ,1);
241 m4u_dmd_requests.create_document
242 (
243 p_msg_id => l_msg_id,
244 p_type => m4u_dmd_utils.c_type_cis,
245 p_action => p_operation,
246 p_doc_status => m4u_dmd_utils.c_sts_ready,
247 p_func_status => null,
248 p_processing_msg => null,
249 p_orig_doc_id => 'GENERATE',
250
251 p_top_gtin => p_gtin,
252 p_info_provider_gln => p_info_provider_gln,
253 p_data_rcpt_gln => nvl(p_retailer_gln,p_user_gln),
254 p_tgt_mkt_ctry => p_tgt_mkt_country,
255 p_tgt_mkt_div => p_tgt_mkt_subdiv,
256
257 p_param1 => null,
258 p_param2 => substr(p_cat_type,1,50),
259 p_param3 => substr(p_cat_code,1,50),
260 p_lparam1 => substr(p_cis_name,1,100),
261
262 x_doc_id => l_doc_id,
263 x_ret_sts => l_ret_sts,
264 x_ret_msg => l_ret_msg
265 );
266
267 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
268 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
269 m4u_dmd_utils.log('l_doc_id - |' || l_doc_id || '|',1);
270
271 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
272 l_err_msg := l_ret_msg;
273 RAISE fnd_api.g_exc_error;
274 END IF;
275
276 x_ret_sts := fnd_api.g_ret_sts_success;
277 x_ret_msg := '';
278 x_msg_id := l_msg_id;
279
280 m4u_dmd_utils.log('Exiting m4u_dmd_request.send_cis - Success' ,2);
281 RETURN; --success
282
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
287 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
288 RETURN; --fail
289 END send_cis;
290
291 PROCEDURE send_cin_ack
292 (
293 p_cin_msg_id IN VARCHAR2,
294 x_msg_id OUT NOCOPY VARCHAR2,
295 x_ret_sts OUT NOCOPY VARCHAR2,
296 x_ret_msg OUT NOCOPY VARCHAR2
297 ) AS
298 l_ret_sts VARCHAR2(1);
299 l_ret_msg VARCHAR2(4000);
300 l_msg_id VARCHAR2(30);
301 l_err_msg VARCHAR2(4000);
302 l_err_api VARCHAR2(50) := 'm4u_dmd_message_pkg.send_cin_ack';
303 l_payload_id VARCHAR2(30);
304 l_datapool_gln VARCHAR2(100);
305 l_retailer_gln VARCHAR2(100);
306 l_user_gln VARCHAR2(100);
307 l_user_id VARCHAR2(100);
308 BEGIN
309
310 m4u_dmd_utils.log('Entering m4u_dmd_message_pkg.send_cin_ack' ,2);
311 m4u_dmd_utils.log('p_msg_id - |' || p_cin_msg_id || '|',2);
312
313 SELECT payload_id, sender_gln, receiver_gln, rep_party_gln
314 INTO l_payload_id,l_datapool_gln, l_user_gln, l_retailer_gln
315 FROM m4u_dmd_messages
316 WHERE msg_id = p_cin_msg_id;
317
318 m4u_dmd_utils.log('l_payload_id - |' || l_payload_id || '|',1);
319 m4u_dmd_utils.log('l_datapool_gln - |' || l_datapool_gln || '|',1);
320 m4u_dmd_utils.log('l_user_gln - |' || l_user_gln || '|',1);
321 m4u_dmd_utils.log('l_retailer_gln - |' || l_retailer_gln || '|',1);
322
323 l_user_id := m4u_dmd_utils.get_gln_user(l_user_gln);
324
325 m4u_dmd_utils.log('Call m4u_dmd_requests.create_request' ,1);
326 m4u_dmd_requests.create_request
327 (
328 p_type => m4u_dmd_utils.c_type_cin_ack,
329 p_direction => m4u_dmd_utils.c_dir_out,
330 p_status => m4u_dmd_utils.c_sts_ready,
331 p_ref_msg_id => p_cin_msg_id,
332 p_orig_msg_id => 'GENERATE',
333 p_msg_timstamp => sysdate,
334 p_payload_id => l_payload_id,
335
336 p_sender_gln => l_user_gln,
337 p_receiver_gln => l_datapool_gln,
338 p_rep_party_gln => l_retailer_gln,
339 p_user_gln => l_user_gln,
340 p_user_id => l_user_id,
341
342 x_msg_id => l_msg_id,
343 x_ret_sts => l_ret_sts,
344 x_ret_msg => l_ret_msg
345 );
346 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
347 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
348 m4u_dmd_utils.log('l_msg_id - |' || l_msg_id || '|',1);
349
350
351 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
352 l_err_msg := l_ret_msg;
353 RAISE fnd_api.g_exc_error;
354 END IF;
355
356 x_ret_sts := fnd_api.g_ret_sts_success;
357 x_ret_msg := '';
358
359 m4u_dmd_utils.log('Exiting m4u_dmd_request.send_cin_ack - Success' ,2);
360 RETURN; --success
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
365 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
366 RETURN; --fail
367 END send_cin_ack;
368
369
370 PROCEDURE send_cic
371 (
372 p_payload IN CLOB,
376 ) AS
373 x_msg_id OUT NOCOPY VARCHAR2,
374 x_ret_sts OUT NOCOPY VARCHAR2,
375 x_ret_msg OUT NOCOPY VARCHAR2
377 PRAGMA AUTONOMOUS_TRANSACTION;
378 l_ret_sts VARCHAR2(1);
379 l_ret_msg VARCHAR2(4000);
380 l_err_msg VARCHAR2(4000);
381 l_err_api VARCHAR2(30) := 'm4u_dmd_message_pkg.send_cic';
382 l_msg_id VARCHAR2(30);
383 l_doc_id VARCHAR2(30);
384 l_payload_id VARCHAR2(30);
385 l_doc_rec m4u_dmd_documents%ROWTYPE;
386 l_dom xmldom.domdocument;
387
388 l_doc_node xmldom.domnode;
389 l_doc_count NUMBER;
390 l_doc_idx NUMBER;
391 l_doc_nodes xmldom.domnodelist;
392
393 l_msg_node xmldom.domnode;
394 l_msg_count NUMBER;
395 l_msg_idx NUMBER;
396 l_msg_nodes xmldom.domnodelist;
397
398 l_user_id VARCHAR2(100);
399 l_user_gln VARCHAR2(100);
400 l_datapool_gln VARCHAR2(100);
401 l_retailer_gln VARCHAR2(100);
402 l_cin_msg_id VARCHAR2(100);
403 BEGIN
404
405 m4u_dmd_utils.log('Entering m4u_dmd_request.send_cic' ,2);
406 m4u_dmd_utils.log('len(p_payload) - ' || length(p_payload) || '|',1);
407
408 -- Create payload
409 -- parsing will allow us to update cin doc records
410 -- right here, avoiding bpel callback
411 m4u_dmd_utils.log('Call m4u_dmd_requests.create_payload' ,1);
412 m4u_dmd_requests.create_payload
413 (
414 p_xml => p_payload,
415 p_type => m4u_dmd_utils.c_type_resp_ebm,
416 p_dir => m4u_dmd_utils.c_dir_out,
417 x_payload_id => l_payload_id,
418 x_ret_sts => l_ret_sts,
419 x_ret_msg => l_ret_msg
420 );
421
422 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
423 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
424 m4u_dmd_utils.log('l_payload_id - |' || l_payload_id|| '|',1);
425
426 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
427 l_err_msg := l_ret_msg;
428 RAISE fnd_api.g_exc_error;
429 END IF;
430
431 m4u_dmd_utils.log('Call create_dom_from_clob_pvt' ,1);
432 create_dom_from_clob_pvt
433 (
434 p_xml => p_payload,
435 x_dom => l_dom
436 );
437 m4u_dmd_utils.log('DOM object created' ,1);
438
439 l_msg_nodes := xslprocessor.selectnodes (xmldom.makenode (l_dom),
440 '/*[local-name()="SyncItemPublicationConfirmationEBM"]' ||
441 '/*[local-name()="DataArea"]' ||
442 '/*[local-name()="SyncItemPublicationConfirmation"]');
443
444 l_msg_count:= xmldom.getlength (l_msg_nodes);
445
446
447
448 m4u_dmd_utils.log('l_msg_count -|' ||l_msg_count || ' |',1);
449
450
451 FOR l_msg_idx IN 0..l_msg_count - 1
452 LOOP
453 m4u_dmd_utils.log('Processing l_msg_idx -|' ||l_msg_idx || ' |' ,1);
454 l_msg_node := xmldom.item(l_msg_nodes,l_msg_idx);
455
456 l_cin_msg_id := xslprocessor.valueof (l_msg_node,
457 './*[local-name()="ItemPublicationIdentification"]'
458 || '/*[local-name()="AlternateIdentification"]'
459 || '/*[local-name()="ID"]');
460
461 m4u_dmd_utils.log('CIN l_cin_msg_id -|' ||l_cin_msg_id || ' |',1);
462 m4u_dmd_utils.log('Query m4u_dmd_documents for CIN info' ,1);
463
464 SELECT receiver_gln, sender_gln, rep_party_gln
465 INTO l_user_gln, l_datapool_gln, l_retailer_gln
466 FROM m4u_dmd_messages
467 WHERE orig_msg_id = l_cin_msg_id;
468
469 m4u_dmd_utils.log('l_user_gln -|' ||l_user_gln || '|',1);
470 m4u_dmd_utils.log('l_datapool_gln -|' ||l_datapool_gln || '|',1);
471 m4u_dmd_utils.log('l_retailer_gln -|' ||l_retailer_gln || '|',1);
472
473 l_user_id := m4u_dmd_utils.get_gln_user(l_user_gln);
474
475 m4u_dmd_utils.log('l_user_id -|' ||l_user_id || '|',1);
476
477 m4u_dmd_utils.log('Call to m4u_dmd_requests.create_request' ,1);
478 m4u_dmd_requests.create_request
479 (
480 p_type => m4u_dmd_utils.c_type_cic,
481 p_direction => m4u_dmd_utils.c_dir_out,
482 p_status => m4u_dmd_utils.c_sts_ready,
483 p_msg_timstamp => sysdate,
484 p_payload_id => l_payload_id,
485 p_orig_msg_id => 'GENERATE',
486
487 p_sender_gln => l_user_gln,
488 p_receiver_gln => l_datapool_gln,
489 p_rep_party_gln => l_retailer_gln,
490 p_user_gln => l_user_gln,
491 p_user_id => l_user_id,
492
493 x_msg_id => l_msg_id,
494 x_ret_sts => l_ret_sts,
495 x_ret_msg => l_ret_msg
496 );
497
498 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
502
499 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
500 m4u_dmd_utils.log('l_msg_id - |' || l_msg_id || '|',1);
501
503 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
504 l_err_msg := l_ret_msg;
505 RAISE fnd_api.g_exc_error;
506 END IF;
507
508 l_doc_nodes := xslprocessor.selectnodes (l_msg_node,
509 './*[local-name()="ItemPublicationLineConfirmation"]');
510
511 l_doc_count:= xmldom.getlength (l_doc_nodes);
512 m4u_dmd_utils.log('l_doc_count -|' ||l_doc_count || ' |',1);
513
514
515 FOR l_doc_idx IN 0..l_doc_count - 1
516 LOOP
517 m4u_dmd_utils.log('l_doc_idx -|' ||l_doc_idx || ' |' ,1);
518
519 l_doc_node := xmldom.item(l_doc_nodes,l_doc_idx);
520
521 l_doc_rec.orig_doc_id := xslprocessor.valueof (l_doc_node,
522 './*[local-name()="ItemPublicationLineIdentification"]'
523 || '/*[local-name()="AlternateIdentification"]'
524 || '/*[local-name()="ID"]');
525 m4u_dmd_utils.log('CIN orig_doc_id -|' ||l_doc_rec.orig_doc_id || ' |',1);
526
527 l_doc_rec.parameter1 := xslprocessor.valueof (l_doc_node,
528 './*[local-name()="ProcessingStatus"]/*[local-name()="Code"]');
529
530 m4u_dmd_utils.log('Query m4u_dmd_documents for ref_doc_id',1);
531 SELECT top_gtin, action, info_provider_gln,
532 data_recepient_gln,target_market_country,
533 doc_id
534 INTO l_doc_rec.top_gtin, l_doc_rec.action, l_doc_rec.info_provider_gln,
535 l_doc_rec.data_recepient_gln, l_doc_rec.target_market_country,
536 l_doc_rec.ref_doc_id
537 FROM m4u_dmd_documents
538 WHERE orig_doc_id = l_doc_rec.orig_doc_id;
539
540 m4u_dmd_utils.log('top_gtin -|' ||l_doc_rec.top_gtin || '|',1);
541 m4u_dmd_utils.log('sub_type -|' ||l_doc_rec.action || '|',1);
542 m4u_dmd_utils.log('info_gln -|' ||l_doc_rec.info_provider_gln || '|',1);
543 m4u_dmd_utils.log('rcpt_gln -|' ||l_doc_rec.data_recepient_gln || '|',1);
544 m4u_dmd_utils.log('tgt_ctry -|' ||l_doc_rec.target_market_country || '|',1);
545
546
547 m4u_dmd_requests.create_document
548 (
549 p_msg_id => l_msg_id,
550 p_type => m4u_dmd_utils.c_type_cic,
551 p_action => l_doc_rec.action,
552 p_doc_status => m4u_dmd_utils.c_sts_ready,
553 p_ref_doc_id => l_doc_rec.ref_doc_id,
554 p_orig_doc_id => 'GENERATE',
555 p_func_status => null,
556 p_processing_msg => null,
557
558 p_top_gtin => l_doc_rec.top_gtin,
559 p_info_provider_gln => l_doc_rec.info_provider_gln,
560 p_data_rcpt_gln => nvl(l_doc_rec.data_recepient_gln,l_user_gln),
561 p_tgt_mkt_ctry => l_doc_rec.target_market_country,
562 p_param1 => l_doc_rec.parameter1,
563 p_lparam1 => l_doc_rec.orig_doc_id,
564
565 p_payload_id => l_payload_id,
566 p_payload_type => m4u_dmd_utils.c_type_resp_ebm,
567 p_payload_dir => m4u_dmd_utils.c_dir_out,
568
569 x_doc_id => l_doc_id,
570 x_ret_sts => l_ret_sts,
571 x_ret_msg => l_ret_msg
572 );
573
574 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
575 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
576 m4u_dmd_utils.log('l_doc_id - |' || l_doc_id || '|',1);
577
578
579 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
580 l_err_msg := l_ret_msg;
581 RAISE fnd_api.g_exc_error;
582 END IF;
583 END LOOP;
584
585 m4u_dmd_utils.log('Processed l_msg_idx -|' ||l_msg_idx || ' |' ,1);
586
587
588 END LOOP;
589
590
591 x_ret_sts := fnd_api.g_ret_sts_success;
592 x_ret_msg := '';
593
594 m4u_dmd_utils.log('Exiting m4u_dmd_request.send_cic - Success' ,2);
595 COMMIT;
596 RETURN; --success
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 ROLLBACK;
601 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
602 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
603 RETURN; --fail
604 END send_cic;
605
606
607
608
609 PROCEDURE receive_cin
610 (
611 p_payload IN CLOB,
615 ) AS
612 x_msg_id OUT NOCOPY VARCHAR2,
613 x_ret_sts OUT NOCOPY VARCHAR2,
614 x_ret_msg OUT NOCOPY VARCHAR2
616 PRAGMA AUTONOMOUS_TRANSACTION;
617 l_ret_sts VARCHAR2(1);
618 l_ret_msg VARCHAR2(4000);
619 l_err_msg VARCHAR2(4000);
620 l_err_api VARCHAR2(50) := 'm4u_dmd_message_pkg.receive_cin';
621 l_cin_msg_id VARCHAR2(30);
622 l_cin_ack_msg_id VARCHAR2(30);
623 l_doc_id VARCHAR2(30);
624 l_payload_id VARCHAR2(30);
625
626 l_msg_timestamp VARCHAR2(30);
627 l_orig_msg_id VARCHAR2(80);
628 l_sender_gln VARCHAR2(30);
629 l_receiver_gln VARCHAR2(30);
630 l_doc_timestamp VARCHAR2(30);
631 l_orig_doc_id VARCHAR2(80);
632 l_operation VARCHAR2(30);
633 l_top_gtin VARCHAR2(30);
634 l_info_prov_gln VARCHAR2(30);
635 l_tgt_mkt_ctry VARCHAR2(30);
636 l_cin_status VARCHAR2(30);
637 l_data_rcpt_gln VARCHAR2(30);
638
639 l_dom xmldom.domdocument;
640 l_node xmldom.domnode;
641 l_doc_nodes xmldom.domnodelist;
642 l_doc_count NUMBER;
643 l_doc_idx NUMBER;
644
645 BEGIN
646
647 m4u_dmd_utils.log('Entering m4u_dmd_request.receive_cin' ,2);
648 m4u_dmd_utils.log('len(p_payload) - ' || length(p_payload) || '|',1);
649
650 m4u_dmd_utils.log('Call m4u_dmd_requests.create_payload' ,1);
651
652 m4u_dmd_requests.create_payload
653 (
654 p_xml => p_payload,
655 p_type => m4u_dmd_utils.c_type_cin,
656 p_dir => m4u_dmd_utils.c_dir_in,
657 x_payload_id => l_payload_id,
658 x_ret_sts => l_ret_sts,
659 x_ret_msg => l_ret_msg
660 );
661
662
663 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
664 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
665 m4u_dmd_utils.log('l_payload_id - |' || l_payload_id|| '|',1);
666
667 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
668 l_err_msg := l_ret_msg;
669 RAISE fnd_api.g_exc_error;
670 END IF;
671
672 m4u_dmd_utils.log('Call create_dom_from_clob_pvt' ,1);
673 create_dom_from_clob_pvt
674 (
675 p_xml => p_payload,
676 x_dom => l_dom
677 );
678 m4u_dmd_utils.log('DOM object created' ,1);
679
680 l_node := xmldom.makenode (l_dom);
681 l_msg_timestamp := xslprocessor.valueof
682 (l_node,'//*[local-name()="header"]/*[local-name()="creationDateTime"]');
683 l_sender_gln := xslprocessor.valueof
684 (l_node,'//*[local-name()="header"]/*[local-name()="sender"]');
685 l_receiver_gln := xslprocessor.valueof
686 (l_node,'//*[local-name()="header"]/*[local-name()="receiver"]');
687 l_orig_msg_id := xslprocessor.valueof
688 (l_node,'//*[local-name()="header"]/*[local-name()="messageId"]');
689 l_data_rcpt_gln := xslprocessor.valueof
690 (l_node,'//*[local-name()="catalogueItemNotification"]' ||
691 '/*[local-name()="header"]/*[local-name()="dataRecipientGLN"]');
692
693
694 m4u_dmd_utils.log('l_msg_timestamp -|' ||l_msg_timestamp || '|',1);
695 m4u_dmd_utils.log('l_orig_msg_id -|' ||l_orig_msg_id || '|',1);
696 m4u_dmd_utils.log('l_sender_gln -|' ||l_sender_gln || '|',1);
697 m4u_dmd_utils.log('l_receiver_gln -|' ||l_receiver_gln || '|',1);
698 m4u_dmd_utils.log('l_data_rcpt_gln -|' ||l_data_rcpt_gln || '|',1);
699
700 m4u_dmd_utils.log('Call m4u_dmd_requests.create_request' ,1);
701
702
703 m4u_dmd_requests.create_request
704 (
705 p_type => m4u_dmd_utils.c_type_cin,
706 p_direction => m4u_dmd_utils.c_dir_in,
707 p_status => m4u_dmd_utils.c_sts_ready,
708 p_msg_timstamp => m4u_dmd_utils.date_xml_to_db(l_msg_timestamp),
709 p_orig_msg_id => l_orig_msg_id,
710 p_payload_id => l_payload_id,
711
712 p_sender_gln => l_sender_gln,
713 p_receiver_gln => l_receiver_gln,
714 p_rep_party_gln => l_data_rcpt_gln,
715
716 x_msg_id => l_cin_msg_id,
717 x_ret_sts => l_ret_sts,
718 x_ret_msg => l_ret_msg
719 );
720
721
722
723 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
724 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
725 m4u_dmd_utils.log('l_msg_id - |' || l_cin_msg_id|| '|',1);
726
727
728 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
729 l_err_msg := l_ret_msg;
730 RAISE fnd_api.g_exc_error;
731 END IF;
732
733
734 l_doc_nodes := xslprocessor.selectnodes (l_node,'/*[local-name()="envelope"]/*[local-name()="catalogueItemNotification"]/*[local-name()="document"]');
735 l_doc_count := xmldom.getlength (l_doc_nodes);
736 m4u_dmd_utils.log('l_doc_count -|' ||l_doc_count || ' |',1);
737
738 FOR l_doc_idx IN 0..l_doc_count - 1
739 LOOP
740 m4u_dmd_utils.log('l_doc_idx -|' ||l_doc_idx || ' |' ,1);
741
745 l_orig_doc_id := xslprocessor.valueof(l_node,'documentId');
742 l_node := xmldom.item(l_doc_nodes,l_doc_idx);
743
744
746 l_operation := xslprocessor.valueof(l_node,'hierarchyInformation/operation');
747 l_top_gtin := xslprocessor.valueof(l_node,'hierarchyInformation/publishedGTIN');
748 l_info_prov_gln := xslprocessor.valueof(l_node,'hierarchyInformation/informationProviderGLN');
749 l_tgt_mkt_ctry := xslprocessor.valueof(l_node,'hierarchyInformation/targetMarket');
750 l_cin_status := xslprocessor.valueof(l_node,'hierarchyInformation/documentStatus');
751 l_doc_timestamp := xslprocessor.valueof(l_node,'item[1]/publicationDate');
752
753 m4u_dmd_utils.log('l_operation -|' ||l_operation || ' |',1);
754 m4u_dmd_utils.log('l_top_gtin -|' ||l_top_gtin || ' |',1);
755 m4u_dmd_utils.log('info_gln -|' ||l_info_prov_gln || ' |',1);
756 m4u_dmd_utils.log('l_tg_mkt_gln -|' ||l_tgt_mkt_ctry || ' |',1);
757 m4u_dmd_utils.log('l_cin_status -|' ||l_cin_status || ' |',1);
758 m4u_dmd_utils.log('l_orig_doc_id-|' ||l_orig_doc_id || ' |',1);
759
760
761 m4u_dmd_requests.create_document
762 (
763 p_msg_id => l_cin_msg_id,
764 p_type => m4u_dmd_utils.c_type_cin,
765 p_action => l_operation,
766 p_doc_status => m4u_dmd_utils.c_sts_ready,
767 p_func_status => null,
768 p_processing_msg => null,
769 p_orig_doc_id => l_orig_doc_id,
770 p_timestamp => m4u_dmd_utils.date_xml_to_db(l_doc_timestamp),
771
772 p_top_gtin => l_top_gtin,
773 p_info_provider_gln => l_info_prov_gln,
774 p_data_rcpt_gln => l_data_rcpt_gln,
775 p_tgt_mkt_ctry => l_tgt_mkt_ctry,
776 p_tgt_mkt_div => null,
777
778 p_payload_id => l_payload_id,
779 p_payload_type => m4u_dmd_utils.c_type_cin,
780 p_payload_dir => m4u_dmd_utils.c_dir_in,
781
782 x_doc_id => l_doc_id,
783 x_ret_sts => l_ret_sts,
784 x_ret_msg => l_ret_msg
785 );
786
787 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
788 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
789 m4u_dmd_utils.log('l_doc_id - |' || l_doc_id || '|',1);
790
791
792 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
793 l_err_msg := l_ret_msg;
794 RAISE fnd_api.g_exc_error;
795 END IF;
796 END LOOP;
797
798
799 m4u_dmd_utils.log('Call m4u_dmd_requests.send_cin_ack' ,1);
800 send_cin_ack
801 (
802 p_cin_msg_id => l_cin_msg_id,
803 x_msg_id => l_cin_ack_msg_id,
804 x_ret_sts => l_ret_sts,
805 x_ret_msg => l_ret_msg
806 );
807
808
809 m4u_dmd_utils.log('l_ret_sts - |' || l_ret_sts || '|',1);
810 m4u_dmd_utils.log('l_ret_msg - |' || l_ret_msg || '|',1);
811 m4u_dmd_utils.log('l_cin_ack_msg_id - |' || l_cin_ack_msg_id|| '|',1);
812
813 IF l_ret_sts <> fnd_api.g_ret_sts_success THEN
814 l_err_msg := l_ret_msg;
815 RAISE fnd_api.g_exc_error;
816 END IF;
817
818 x_ret_sts := fnd_api.g_ret_sts_success;
819 x_ret_msg := '';
820 x_msg_id := l_cin_msg_id;
821
822 m4u_dmd_utils.log('Exiting m4u_dmd_request.send_cic - Success' ,2);
823 COMMIT;
824 RETURN; --success
825
826
827 EXCEPTION
828 WHEN OTHERS THEN
829 ROLLBACK;
830 m4u_dmd_utils.handle_error(l_err_api,l_err_msg,
831 SQLCODE,SQLERRM,x_ret_sts,x_ret_msg);
832
833 END receive_cin;
834
835
836 END m4u_dmd_message_pkg;